Sap Database Notes -2

Sap Database Notes 2:-

BR Tools (Used for entire backup administration)
• BR  tools is a package name which contain various tools.
• These tools are divided into various ways based on their performance.
Note: If you get an error message while calling BR tools then your version might be older. (Less than 4.7).
• These are two modes while calling the various options in BR Tools.
 -Main Menu Mode
 -Quick Mode

BRConnect: is must, be called in main menu mode.
• ‘BRSPACE’ and ‘BRRECOVER’ always make a ‘CONNECT/AS SYS DBA’, because their actions require SYSDBA privilege.
• Once you connect a SYSDBA, if you do not want to enter a user name, password, while calling ‘SQL* PLUS call the interactive program using the command ‘SQLPLUS/NO LOG’
• ‘SQLSTARPLUS by default connects to the db defined in enhancement oracle database.
• Changing the password for SAP user is done using ‘BR CONNECT’
Note: Passwords for DB user ‘SAP SCHEMA ID’ or ‘SAPR3’ should not be changed using oracle methods.

Database Transaction Codes:
1. DB13: Schedule backups and other administrative jobs.
Note: ‘DB13C’ : This is used to schedule backups and admin activities centrally for all SAP systems and database.
2. DB14: To check the status and logs of all database operations.
3. DB16: Overview of database system checks.
4. Db17: View and maintain check conditions for database system check.
5. DB20: Maintain Statistics.
6. DB21: Configuration of Statistics
7. DB26: Database parameter overview with history.
8. DB02: Table and index monitor
9. ST04: Database performance monitor
10. RZ20 – DB Alert Monitor (Optional)
11. DB13 is used as an interface to schedule back ground jobs starting with DBA*. These background jobs look into table ‘SDBAC’
12. SPfile.ora is server side initialization parameter file (oracle database server)
• Do not make parameter changes on oracle level, because if only changes parameter values in SPfile, hence always use BR* tools, because it monitors consistency by copying the contents in both files.
• The transaction code DB02 and ST04 still use ‘init<SID>.ora’
• SAP installation tool do not create SPfile. SPfile is created using SQL*plus ‘CREATE SPFILE’.
• SPfile is stored in ‘oracle_home’ directory same as ‘init<SID>_ora’.
• RZ20: Database alert monitor.

Start and Stop Commands
BRSPACE_C FORCE_F dbstand_S <State>
BRSPACE_C FORCE_F dbstand_S <State>

Starting of Database
1. No mount = reads parameter files, database instance started and allocated memory buffers.
2. Mount face: opens cofiles.
3. Open: opens all data files and online redo log files.
• Mount face is used for database recovery, for changing archive log mode, for removing and moving data file and also for adding, dropping, renaming online redo log files.
• Do not use ‘BRCONNECT’ to start and shutdown database, instead use ‘BRSPACE’ because it tried logfile actions.
• No mount space is used for creation of database and for recreation of lost cofiles.

Stopping of Database
1. Normal: Oracle waits till all users are disconnected from the database. All files are closed and database is dis mounted and instance is shutdown.
2. Transactional: Oracle waits till all open transactional to finish and then it disconnects users and shutdown database.
3. Immedaite: No new connections and transaction are allowed. PMON ends all user sessions and performance roll back of any open transactions then only shutdown database.
4. Abort: no new connection and transactional allowed. No roll back of open transactions. Users are disconnected and oracle processes are stopped.
Note: With all the above first three methods, database is shutdown in a consistent state and does not need recovery at next restart.
• Default mode for oracle shutdown is normal
• Oracle commands shutdown immediate and shutdown abort stage oracle instance even if work process still has connections of database.
• Oracle info messages, warnings and errors are logged in oracle dump files i.e. background, user trace which is located in ‘SAPDATA_NAME’ directory.
• Background directory store alert log file. Alert_<SID>.log. Whereas user directory store trace files written on behalf of shadow process.

(Q) Why do I need ‘SPFILE<SID>.ora’ even though I have ‘init<SID>.ora ?
(A) From Oracle 9.i ‘init<SID>.ora’ is replaced by ‘SPfile<SID>.ora or ‘SPfile.ora.

(Q) If a file is missing from the chain of offline Redo log files, then what we’ll do ?
(A) We have to perform a restore and recovery of Database. Recovery is performed using the method “Point In Time” by which all the Offline Redo log files older than the last one is used for recovery.

(Q) What are the causes for logical errors related to Database ?
(A) (i) Manually deleting parts of Database objects such as Rows in a table.
    (ii) Manually dropping Database Objects.
    (iii) Manually dropping Application Objects.

(Q) Is Point in Time Recovery a standard Solution for logical errors in production system ?
(A) NO

(Q) Where do we use the Point IN Time Recovery ?
(A) Point in Time is very critical in a system landscape with Data Dependencies between Systems.

(Q) How do we verify Consistency of Oracle Database ?
(A) By performing by a logical data check.

(Q) Why do we need to perform a logical check ?
(A) In order to verify corrupted Data blocks (Ora – 1578)

(Q) Why do we need to perform a physical Data check ?
(A) To verify the tapes used for Database backup.

(Q) How often we perform Online Backup and Offline Backups ?
(A) Online Backup = Daily
    Offline Backup = Once in a Week

(Q) How do we perform Backup of Offline Redo log files ?
 (A) (i) Backup of every Offline Redo log files is taken TWICE on separate tapes before the    files are  deleted from Archive Directory.
   (ii) Perform additional Backups after each system upgrade and also if Database structure is Modified.

(Q) What are the tools used by Oracle Admin in an SAP System for Backups ?
(A) Database Backups = BRBACKUP
    Offline Redo log files = BRARCHIVE

(Q) What are the occasions in which changes to Tile Structure of Database is made ?
(A) 1) When a Data file is added
    2) When a Data file is moved to a Different Location.
    3) When a Table Space and its Data files are reorganized.

(Q) What are the various Backup types?
(A) There are 5 Backup types
    1) Online Backup
    2) Offline Backup
    3) Complete Backup
    4) Incremental Backup
    5) Partial Backup

Complete Backup:
All the Data in the Database is backed up. Complete Backup is again divided into 2 Types
1) Full Backup:- After data backup an additional information , i.e. Catalog is Written into Cofile by Recovery Manager.
2) Whole Backup:- It creates a Backup of all the data without the Catalog.

Incremental Backup:
i) This Backup Is used for taking needed Data blocks that have changed since the time of Full Backup.
ii) During Incremental Backup the amount of data to be backed up to get shorten and not for The Backup time.
iii) During Incremental Backup is only based on previous Full Backup.

(Q) If the Corresponding Full Backup is already overwritten and can I use Incremental Backup ?
(A) NO, Incremental Backup is useless.

(Q) Can I perform a Backup of Individual data files using Incremental Backups ?
(A) NO

Partial Backup:
The backup of Database in smaller parts is called as Partial Backup.
NOTE:- Sum of individual partial Backups form an Entire Complete Backup.
NOTE:- Recovery Backup using partial Backup data is very much time consuming, because it needs all oldest Backup Offline and Online recovery Processes.

(Q) What are the various Backup strategies used in SAP ?
(A) There are 3 Backup strategies in SAP
     i) Complete Backup:- Restore missing Database files from complete Backup, Restore Offline Redo Log files writte during and after this Backup.
     ii) Incremental Backup:- Restore missing Data files from last Full Backup, update them with restore from last Incremental Backup.
     iii)Partial Backup:- Replace complete backup with partial Backups , we need a longer time to perform a recovery from media crash.

TOOLS:
(1) BRBACKUP: Backup of Oracle Data files , Cofiles, Db Redolog files, Oracle Software Directories and SAP System directories.
(2) BRARCHIVE: Backup of Redo log files.
(3) BRRESTORE: Restore all Db files and Offline Redo log files
(4) BRRECOVER: Checks for Database for missing files , it calls BRRESTORE for restoration of missing Data and Offline redo log files.

NOTE:
(1) Both BRBACKUP and BRARCHIVE records their actions in log files, BRRESTORE uses above logs for restoration of missing files.
(2) Both BRBACKUP and BRARCHIVE supports Backup to Tapes, Disks as well as Backups with Third party Tools.
Important Parameters for Configuration of BRBACKUP and BRARCHIVE(Init<SID>.SAP)
(A) Backup_mode =   All(Whole)
            Full(full backup)
            Incremental Backup
            Partial(Table space name, Dir path, File id.s)
(B) Backup_type = Online and Offline Backup
(C) Backup_dev_type = Tape or Disk or External Interface
(D) Util_file = BACKINT(External Backup program through Interface BACKINT)
(E) TAPE_COPY_CMD = CPIO or DD or RMAN(Copying files from Disk to Tapes)
NOTE:
 DD = Raw devices are copied with this option
 CPIO = Directories are copied with this option
The Profiles init<SID>.ora and init<SID..sap and Summary and detail logs are copied with this CPIO.
(F) DISK_COPY_CMD = cp, copy (Copying files to disks)
    Cp is used in UNIX
    Copy is used in WINDOWS
(G) Expire_period = (1)We have to specify the expiry period of a tape
        (2)Tape_use_count = Max number of times, volumes can be written   to tapes.
(H) Volume_Backup: Names of volumes used for backups(BRBACKUP)
    Volume_Archive: Names of volume used for backups of Offline redo log files(BRARCHIVE)
(I)Tape_Address = Identifies device address of tapes.
(J) DD_Flags and DD_IN_FLAGS= Specify block ( Size of at least 64kb)

Integration of Oracle Recovery Manager (RMAN) into SAP Tools:
(1) RMAN is Default Oracle Backup and Restore Program
(2) RMAN executables run in Client process and connection to Database
(3) Backup with RMAN is done in 2 ways
(i) RMAN classifies complete backup level 0 Backup
(ii) Level 0 serves as basis for Level 1 (Incremental)
(4) Backups performed without RMAN call CPIO or DD to save Database files to tape
NOTE: RMAN always writes the information in a separate file recovery catalog

(Q) Can RMAN recover the Database automatically without Recovery catalog ?
(A) NO

(5) RMAN performs Backups directly to Disks and not to Tapes
(6) RMAN uses Oracle shadow process to check for data block corruptions and filters those blocks and then writes used blocks to backup media.
(7) The Parameter to set the controls of copying data to Backup media to RMAN is TAPE_COPY_CMD or DISK_COPY_CMD= RMAN_DISK (RMAN Value)
(8) Advantages of using RMAN:
I) All blocks are checked for block corruption to ensure the consistency state.
II) Only used blocks are copied to Backup media
III) Empty blocks used before are always backed up

(Q) Is whole Backup can be consider as level 0 Backup ?
(A) Whole backup is not level 0 Backup and can’t be used as basis for Incremental Backup.

(9) RMAN writes Header, tailer and blocks of atleast one Database or one raw disk file to a file called SAVESETS
(10) Using SAVESETS speeds up Backup Process.

PREPARATORY RUN:
    Preparatory run is used to determine the optimal SAVESET distribution of data files we want to backup.
(Q) Why do we need to perform a preparatory run ?
(A) If Backup with RMAN is supposed to form sets then we need to run Preparatory run.
     Preparatory run can be run from DB13 prepare for RMAN Backup.
     No Backup is created during preparation run, only estimates Compression rate of BRTOOLS to compress the files and to determine compressed and decompressed file sizes.
     It is recommended to perform preparatory run per one Backup cycle.


Will update soon... Check next post...