Oracle For SAP

Shared SQL area: It is used to store the frequently accessed tables, element and SQL

statements

It consists of DD cache and SQL cache

DB Buffer: It is used to store the conetent i.e fetched from the database, it has its own

size

-DB buffer size should large enough to hold the user request data

-Each and every request should get response from DB buffer

Redolog Buffer:

It is used for fast commit processing; it has its own size i.e., 1mb up to oracle 9i, from

oracle 10g it has 4 MB

Fast commit –it ensures that user transaction is updated into database

-user transactions are committed only when they are written to redolog files

-user gets updated the response immediately when it is updated in the redolog groups (A

& B) on the disk

Control files

These are used to update the information dynamically about the database structure

changes

-It consists of the paths, log sequence no, system changes number, and it is essential to

restore and recover the database.

-Control files needs to be placed in 3 different physical locations, which can be extended

up to 6.

-This is to ensure that atleast one control file is available to restore and recover the db

Cntrl<SID>.dbf located in cntrl directory

Datafiles

Datafile are the physical files at the OS Level

Oracle has its own background process

Log writer:

It is used to write the content from redolog buffer (memory) to online redolog buffer

(disk)

It writes when

1/3rd of the log buffer is full

If the user commits size more that 1MB

Before DB writes, writes to the db it triggers

When a user commits a transaction it triggers

Database writer (DBWR):

It is used to write the contents which are marked as dirty buffers to data buffers

It is triggered

For every 15 seconds

When the checkpoint occurred again it writes

When user request (db process) does not find space database buffers to host the fetched

content it trigger

Check point:

It is used to maintain the consistence of the consistency of the system by triggering log

writer and db writer

It is triggered for every 60 seconds which can be increased or decreased

SMON:

It is used to bring the system into a consistency stage when the system is terminated or

uploaded during power failure.

SMON does the following tasks during startup the database

Before the db is opened, SMON reads the online redolog files and redo the changes in the

database as per commit

SMON reads roll back or undo segments to roll back or undo changes

SMON releases the user requests and locks

SMON brings the system in consistent mode

PMON:

It is used to monitor the process and ensure that all the process are released, before

openings the database

Archive Process:

It is used to write the contents from OriLog A & B to oraarch directory

When orilog A is full log switch occur and log files are written to OriLog B and A and

the sequence number is increased

Sap data 1….Sap data 4: These are the physical files

SAPTRACE: It is used to write the log files of all the database operation

SAPCHECK: It is written with log files when a command to check the database is issued

SAPBACKUP: It writes the log files of the backup, when the backup is triggered to tape

This is also a directory for ac backup for the disk backup is scheduled

SAPARCH: It is used to host the log files that are generated during the log backup

SAPREORG: It is used to host the data import and export during re-organization

-Orilog A and Orilog B: These are online redolog files with atleast one member in each

group

SAP recommends having 2 groups that should be placed in 2 different disks

The size of the log files varies from company to company, 25-50 MB

ARCHIVE: on production systems the archive log mode should be set to yes, if it is set to

yes, org process writes from LOG A and LOG B to ora arch directory

But on training and demo systems log mode can be set to no, so that archive process

never gets triggered

When OriLog A is filled it will overwrite the content of OriLog B

During abnormal termination we cannot restore the system to the point of consistency

Oracle startup and shutdown process

When db starts or startdb command is initialized the parameter are read from

init<SID>.ora which resides in d:\oracle\ora92\

It consists of

-SGA (Shared Global Area)

database buffer: db_block_buffer=26080

-Log-buffer

-shared_pool_size

-control file (3 locations are specified, 6 location can be specified)

-db_block_size=8192

-archive destination

There are 3 types of parameters:

Tuning Parameters

-This parameter is used to tune the performance of the system

Changeable Parameters

-These parameters can be changes based on the requirements like process, change the

archive log mode, control file location etc

Static Parameters

-Most of the parameters are not modified like database_block_siz, but the location and

the patches can be changed

-Oracle database can be started as startup service or use command strdbs.cmd or startdb

(UNIX)

-Oracle startup can be performed using SAPDBA or BR tools

Oracle is started in 3 cases:

*Database is operational when it is started in the open mode

Startups mount:

-It is used to build control files, the system is not usable, and it is started only for

administrative purpose

Startup nomount:

-SGA is built, db views are allowed to access and it is used for recovery purpose

-data files are not opened, db is not usable

Startup Open:

-Database is opened in the open mode and it is accessibility to all users

Oracle is shutdown in 4 cases

Shutdown abort:

-It is used to shutdown the database abruptly, but it is suicidal.

-It should be only used under critical situations

-System is not consistent, so SMON performs redo and undo when the system is restarted

and tries to bring the system to consistent state

-Sometime we may need to restore the system from tapes and recover from logs

-All the opened transactions are abrupted and there is no roll forward or rolled back takes

before shutdown

Shutdown Immediate:

-All opened transactions are rolled back and the system shut downs gracefully, It is

consistent and no restore/ recovery is required

Shutdown transactional:

-All the opened transaction are allowed to complete and the system gracefully, it is

consistent and not restore /recovery is required

Shutdown Normal:

-All the logged users are allowed to work, no new connections are allowed in all the

above cases and the system shutdown gracefully, it is consistent and no restore/recovery

is required

R/3 connection mechanism

Each R/3 work process while starting tries to establish connection to the db

The connectivity as follows

-It loads the dboraslib.dll from run directory and assigns shared memory and get the path

of the oracle home and tries to login to the database as OPS$ users

-In order to connect to database using OPS$ user the 2 parameters needs to be set up

i) remote_os_authnet=true

ii) Os_authnet_prefix=ops$

First, parameter allows, it is used to authenticate os users remotely

Second, when the user is authenticated as admin at os, corresponding user can login to

the database w/o any password

-R/3 workprocess login to the database as OPS$ user, OPS$ user owns only one table i.e.,

sapuers – table consist of SAPSID and its password

-R/3 workprocess get the password of SAPSID user and disconnects from the oracle &

reconnects with user SAP <SID> and known password.

-It is documented in dev_wn log files

* SAP<SID> password resides in 2 tables

SAPUSERS

DBA_USER

-When the password is changes for SAPSID, needs to be checked in 2 locations i.e., the

reason it should be changes in 2 locations and it should be changes using BRtools

>brtools

-4

Lsnrctl

Database is started w/o any error, but sap system could not connect to the database and

results and error ora-1250 (listener is also started)

Each user request is listened on the database on the ports 1521 and 1529

The listener service is running on different ports, other than the ports accessed by r/3 WP

Go to listener.ora and check the listener service is configured for the right port, host

name, sid.

If the port is different, reconfigure the listener service and restart the service

Storage Management

Oracle data is stored interms of data blocks

Data blocks are defined by the parameterd b _ b l o c k _ s i z e

By default it is 8 kb

Oracle data is stored physically on the file structure using data file with extension. d b f,

but they will address logically for easy maintenance and administration, but the data files

with extension. d a t a

SQL.mdf (main data file)

SQL.ldf (log data file)

SQL.ndf (next data file)

Extents

- they are nothing but group of blocks

- they are assigned to a table or index

Extent is a logical container which is assigned to segments (table or index)

While allocating extent to a table the following parameters are used

Initial extent

Next extent

Mini extent

Max extent

Tables

TGORA, IGORA documents the above extents

If a block is opened, it should be written otherwise it is closed and doesn’t open again

Data should not be small

If a block is empty, there is a problem

If data doesn’t fit in 8kb, increase data block to 16kb, otherwise database increases

SAP has 14,000 tables but oracle can hold9 9 9 9 tables , small tables combined to a form

a big tables and the pooled tables and cluster comes into picture

8k is enough for most of the companies

Extent Filling

The tables are populated with the initial extents if the size is known, if not mini extents

are allocated to a table

If mini extent is completed then it will try to grown at the rate of next extent

If the extents are used completely ora 16131 and ora 1632 occurs with a message max

extent reached

If order to adopt next extent schedule SAPDBA –next, BRCONNECT –Fnext

Tables /Indices /Segments

Extents are assigned to segments

A segments is a table or index or a group of a tables

Table space

It is a logical container which consists of tables and index

Each tablespace is referred with a data files on the disk

There are various tablespaces which has their own functionality

There were 27 table spaces up to 4.6c, from 4.7EE there are 4

Prefix

Abbreviation

Ext

PSAP

<TS_name >

D (

R/3 Naming Conventi

1) System table space –It contains the information about the database

2) PSAPTEMP – It is a temporary table space which is used to sort the tables for selected

operations

3) Dictionary Managed Tablespace –this table space managed by the dictionary and it has

its own disadvantage

While allocating, extents contiguous free space required

It cannot coalesce the free space

More disk fragmentation occurs there by increasing the search criteria

Frequently re-organization is required

SAPDBA –next or BRCONNECT –next to be adapted then next extent

4) Locally Managed Table Space –This feature is available since 9.20

This table space has the following features

While allocating next extent, it looks for the free space, and the extents are assigned less

fragmentation occur and frequently and reorganization not required

System defines the next extents SAPDBA –next or BRCONNECT –f next are not

required

BRSPACE provides an option to convert dmts to lmts

5) PSAPROLE-It is a table space which is used to store the data before an update

Datafiles

-These are the physical data files which resides at os level

-Each data file is associated with a tablespace

-A tablespace can have more that 1 datafile, but each datafile can be associated with 1

table space

-Depending on the company category each data file may vary in between 1GB to 8GB

Datafile filling:

Logically user fills the data i.e update the data or inserts the data into tables, which

automatically file the datafile

If the datafile is filled and does not have enough space to insert the data ora 1653, ora

1654 occurs, can be occurred in 2 situations

-When the Datafiles are completely filled up

-unable to extent because there is no contagious space available in the datafile

-The datafile can be resized, > BRCONNECT, space management

Orarchive directory

When ORAARCH directory is filled ORA255 or ORA272 occurs and the systems strucks

and no updates be made

Update gets deactivated, and it should be activated after resolving.

*Resolution: Schedule Archive backup to tape on disk immediately.

Reorganization

Re-organization is required when the db response time are increasing and the database is

growing out of space

The fragmented space is displayed in db02

Process

-Schedule the downtime

-Ensure the SAP reorg die rectory has enough space to hold the size of the db

-Go to SAPDBA or BRTOOLS use the option export

* Ensure that valid backup is available

-Export the database

-Delete the database

-Import the database

BRSPACE provides an option to perform the reorganization online

House-keeping Jobs

DB01 –It is used to monitor the lock waits on the system similar to sm12 on R/3

It helps the lock before a record is updated on the database

DB02 – It is used to identify the space utilization of the tablespace

It is also used to identify the critical objects which are going out of space

It is also used to identify the missing index

Conclusion:

Capacity planning of the db on daily growth

Identify the space critical objects, resize the datafile.

It the tablespace exceeds 90% of the utilization it is consider as critical

Missing indexes:

Missing are displayed using the option missing indexes

Missing indices are displayed in yellow colour select the index and choose the option

create in db or SE14 to create index

BRCONNECT

BRCONNECT –Fcheck: it is used to check the database check condition

BRCONNECT –Fnext: it is used to adapt next extent

BRCONNECT –Fcleanup: it is used the clean the log files

BRCONNECT –Fstats: it is used to update the statistics of all the transparent tables

whose statistics are outdated; it should run weekly atleast once & it is one of the most

important step in installation ,system copy …etc

-identifies the tables whose statistics is outdated and write into the tabled b s t a t c

Update the tables which are in dbstatc

All the above jobs can be performed by db13

DB13

-Double click on date, specify the start time

-Schedule the job

-Execute check and update optimizer statistics

-Adapt next extents

-Check the database

-Verify the database

-Cleanup the logs

ORACLE FOR SAP

BR

BR

Scheduling Databas









Offline backup

Which is used for the environment of 16/5, during the offline backup the database will be

shutdown and table spaces are in the backupmode, it is referred as cold backup

It is consistent to restore

It is scheduled using DB13, BRTOOLs or any third party tools

Offline backup can also include logfiles backups, also referred as complete backup

Online backup

It is used for high availability systems which supports 24x7x365 day-environment

The database should be in archive mode

The users continue to work during the backup operations

It is also referred as hot backup

It is scheduled using db13, sapdba, Brtools

It will be consistent only with the logfiles

Process-

During the backup the tablespace goes into begin backup mode, after the backup the

tablespace goes to end backup mode, during this period the data is written into the log file

Partial Database backup

It is used to backup the database at table space level

Incremental Backup

This is used for the companies where high availability is required and cannot spare much

time for database backup

So the whole database backup is taken wither on Saturday or Sunday, when the whole

backup is taken catalogue is written to the database

From the modified data can be backed up using incremental backup

*Online redolog files:

Orilog A, Mirrorlog A, MirrorLogB B, Orilog B

*Offline redolog files:

The files which are copied from ORAARCH directory to the other files

Redolog Backup

The log files which resides in ORAARCH directory needs to be moved from TAPE

periodically, if they are not moved Orarchive directory fills and archive struck occurs

As the redolog files are critical to restore the database to consistent state, the needs a

backup periodically

If the Redologfiles are lost, the database cannot be stored

Init<sid>.sap:

It is the initialization for Brtools and SAPDBA; it consists of the following parameters

and resides in the database directory oracle\oraa\102\database

BACKUP MODE=all

RESTORE MODE=all

BACKUP TYPE=offline, online

BACKUP_DEV_TYPE=tape or disk

BACKUP_ROOT_DIR=sap directory (backup)

BRARCHIVE_FUNCTION=save

ARCHIVE_FUNCTION=save

ARCHIVE_COPY_DIR=sapbackup\<SID>\Backup

TAPE_COPY_CMD=cpio

TAPE_SIZE

TAPE_ADDRESS=/dev/mnt

VOLUME_ARCHIVE=<specify the name of the tape>

VOLUME_BACKUP=<SID>01

EXPIRE_PERIOD=30 days

TAPE_USE_OUT=<time>

-BRTOOLS takes the parameter from this file by default; however this parameter can be

overwritten by using at run time

BRBACKUP

It is used to backup the database

It can be initialized through SM49

>BRTOOLS – 4

BRARCHIVE

It is used to backup the log files

It can be initialized using SM49

>BRTOOLs -4

BRRESTORE

It is used to restore the system from a database backup

>BRTOOLS -5

BRRECOVER

It is used to recover the database from the log files

BRCONNECT

It is used to establish connection to the database

-It is also used to change the password of SAPSID

Backup database

When a db is backedup its backup all the datafile, control files and log files (tablespace

logically)

BRSPACE

It is the new feature provides by BR tools to perform:

-create, drop, extent, alter a tablespace

-it is also used to alter the database (move)

-BRSPACE can be used interactively to Dictionary managed table space to locally

managed table space