Sap Database Notes 1:-
Database :-
Oracle database: is a collection of data stored in one or more data files on disks.
Oracle manages database data in logical units called table spaces.
Table space: One or more data files.
Instance: Set of oracle background process and memory buffers form an instance.
Q) What happen when oracle instance is stored ?
Shared global are allocated (SAG is allocated)
Oracle background processes are started.
* In unix we can identify oracle process as individual system process
* In windows these processes run as threads with one common oracle OS process i.e. ‘Oracle.exe’.
* When an oracle instance starts a special process called listener, process opens and establish communication between net weaver and oracle.
* Listener process is not part of oracle instance; it is rather part of network process that works with oracle.
* In SAP dedicated server configuration is used. i.e. for each work process we have dedicated server processor called as shadow processes.
* The ratio of work process to shadow process is 1:1
* To handle database request for SAP uses a work process communicate with its core shadow process.
* Database data is permanently stored in datafiles or disks.
* To accelerate read and write access data it is cached in database buffer cache in SGA
* Shared pool divided into executable SQL statements which are stored in shared SQL area of the shadow pool.
* Oracle data dictionary is stored in row cache of shared pool.
* Data processing never takes place directly on disk, it is first copied by associated shadow process from disk to the database buffer cache in SGA.
* Oracle keeps most recently used data blocks in the database buffer cache.
* Sometimes oracle writes the least recently used data blocks in buffer cache.
* Modified data blocks are call as Dirty blocks.
* Shadow process never copies modified data into disk.
* Coping data into disk is done by a special background process called as ‘DBWO’ (DW writer).
Q) What are the situations in which DBWO writes dirty blocks to disks ?
if the number of scanned buffers reaches a certain thresh hold.
At a specific time that is when check point occurs.
* Scanning of the buffers is done by shadow process.
* Changes are done in two ways:
Roll forward changes.
Roll backward changes.
* Redo events are stored in redo.log files and performs roll forward recovery.
* Undo entries stored in undo table space performs rollback.
* Redo changes = committed changes = new value = after images.
* Undo changes = un committed changes = old value = before image.
* Oracle shadow process records redo changes and stores in redo log buffer of SGA temporarily.
* Oracle background process “log writer – LGWR” writes data in redo log buffer to online redo log files which are stored physically on disk.
* Redo log buffers is also called as circular buffer.
* Circular buffers records all committed and un-committed changes made to the database.
Q: What are the conditions in which log writer writes redo log buffer data to online redo log files ?
Ans: There 4 conditions:
When transaction is committed.
For every three seconds.
When redo log is 1/3rd of full.
When DBWR is about to write modified buffers to disk and some of the corresponding redo records have not at been written to online redo log i.e. write ahead logging.
* Each committed transaction will have a system change number (SCN) stored in redo log file.
* Size of Oracle redo log file is 40MB (fixed number). These are four predefined collections of online redo log files.
* At every log switch oracle will increase the log sequence number.
* Current online redo log file, ‘LGWR’ is writing into is call active online redo logo file.
Control files
This file is used to start and operate database.
Q) What are the entries in co files ?.
Physical structure of database
State of database
Table space information
Names and location of data files and redo log files.
Current log sequence number
* if physical structure of database is occurred then co.files get updated automatically.
* SAP stores co.files in three locations during installation of SAP. It is recommended to store the files in three physically separated hard disk.
* If database = open then co.file available for writing.
* Normally caches are small and don’t grow.
* ‘RMAN’ for backups, “cofiles may grow by factor 10”, because they contain information about RMAN backup.
Check point Functions:
* Checkpoint wakes up the database writer to copy all buffers that are dirty to the disk.
* It also updates header of all data files to record details of the check point.
* If writers information about the check point position in online redo log files into the cofile. This information is used during database recovery.
* Less frequently the checkpoint occurs the longer is the time the instance need for recovery.
* Checkpoints occurs at log switch.
Database Recovery:
* Online redo log files used for database recovery (instance recovery). After restart, the system performs automatic recovery.
* If online redo log files are lost during a crash, a complete recovery is not possible. Hence online redo log files must be mirrored i.e. two or more copies needs to be maintained.
* Oracle it self mirrors online redo log files by default.
* Online redo log fines are limited in size, and cannot grow automatically.
* Automatic instance recovery of online redo log files is possible.
* To manually restore and recover data files which are missing, we need both a database backup and all redo log information written after the backup.
* Archiving must be exclusively activated by tuning on archived log mode i.e. “LOG_ARCHIVE_START” is true.
* Archiving is take care by an oracle background process called as “ARCO” (archive)
* Oracle cannot mirror offline redo log files, hence we must use RAID.
* Offline redo log files and data files should be on different disk.
SMON (System Monitor)
* SMON performs recovery at instance startup
* It writers alert log information if any instance process fails.
* If cleans up temporary segments that are no longer in use.
PMON (Process Monitoring)
* This monitors shadow process.
* PMON roll backs, its uncommitted data, stops shadow process and frees resources incase of a client process crash.
Oracle Directory Structure in SAP
In Unix all directories are present under one single tree, where as in windows all directories are present under separate drive letters. They have 3 files inside the directories
/database (Windows) init<SID>.ora
/database (Unix) init<SID>.sap
Spfile<SID>.ora (only from oracle 9i)
• Online redo log file = original log and mirror log.
• Define redo log files: original arch, SAP arch.
Note: All previous versions till oracle 8i has saparch directory.
• SAP trace = Alert <SID> log = SAP trace/background/user trace
• Data files = SAP data1
...
...
...
SAP data <n>
There are 3 environment variables on database server
1. Oracle_SID = system ID for DB instance
2. Oracle_HOME = the directory for BR* tools.
3. SAP DATA_HOME = the data file directory.
• The home directory for oracle is ORACLE_HOME
• The location for cofiles and offline redo logs is configured in the oracle profile init<SID>.ora.
• The location for data files and online redolog files is stored in database.
• The oracle tool to ping is ‘TNSPING’
Oracle System Privileges
• SYS DBA and SYSOPR are oracle system privileges.
• Control at this privileges is outside the database.
• The privileges allow accesses to database instance even when database is not open.
Operating System Users and Groups (Start->programs->Admin tools-> Configure Management -> users, groups)
Users:
<SAP SID> Admin and ORAdb<SID> are the two users which are created in unix system,
where as <SAPSID> admin, <SAP service.SAP<SID> created in windows system.
Groups:
1. ‘ora_dba’ = Member of this groups can connect to oracle database as dba without a password.
2. ‘ora_<SID>_dba’ = admin group
3. ‘ora_<SID>_OPER = db operate group
Extra Groups:
SAP_<SID>_Global Admin = SAP Global Admin Group.
SAP_<SID>_Local Admin = SAP Local Admin Group
SAP_Local Admin = SAP local Admin Group
• Operating System group DBA will have administrative privileges, where as OS group OPER will have restricted privileges.
Note: Always assign database rates to users.
• Data base rolls have privileges.
Rolls:
DBA, SAPDBA are the two rolls.
DBA rolls is created by oracle
SAP DBA rolls is created by SAP.
• The Roll DBA has all admin privileges except the ‘SYS DBA’ and ‘SYS OPER’ system privileges
Note: The privileges ‘SAPDAB’ provides accesses for administrating certain tables.
• SYSOPER has all SYSDBA privileges except create DB and without ability to look at user data.
Database Users:
1. ‘SYS’ and ‘SYSTEM’ are created by oracle.
2. SAP <SID> are SAP <SCHEMA_id> is created by SAP.
3. Default user used by SAP to connect to database is system.
4. During installation oracle database, you will be promoted to enter the password for the user SYS, System, and SAP <SCHEMA_ID>
Note: OPS$ is an user which is created by SAP and doesnot need a password.
• SAP workprocess at OS level connect oracle with the user name ‘SAP<SCHEMA_ID>.
• The password for this user is stored in oracle system table ‘SAPUSER’
• Workprocess first connect to ‘OPS$ user and get the password for ‘SAP SCHEMA_ID’ from the table ‘SAP USER’.
• Never change the password for ‘SAP SCHEMA_ID’, always use ‘BR*’ tools, ie. ‘BRCONNECT’ to change the password.
• OS files stored in ‘ORACLE_HOME’ directory.
• ‘Listener_ora’ = contains all oracle system ID and protocol address.
• ‘TNSNAME.ORA’ = Contains all the list of server names for all the databases that can be accessed in the network.
• ‘SQL NET.ORA’ = Contains client side information.
• Oracle has one listener i.e. ‘LSNRCTL’
Options:
OS level : lnsnrctl_help
OS level : lnsnrctl_status = oracle.
Location of parameters and listener log files.
Note: ‘Listener_Ora = Listener tracing files.
Options:
1. Off = Offered
2. User = Limited Trace
3. Admin = Detail Trace
Will update soon... Check next post...
