(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
(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. TAPE MANAGEMENT:
(1) Each and every tape used for Backup, i.e. BRBACKUP and BRARCHIVE needs to be initialized.
(2)
During tape Initializing SAP specific label is written on lable as First file (Tape.hdro) containing the tape name.(3) BRTOOLS-> Backup-> Dbcopy-> Additional Functions-> Init of BRBACKUP tape Volume or Init of BRARCHIVE tape volumes.
The command to start the initialization is BRBACKUP or BRARCHIVE or – I/Initialize.
(Q) What are the contents of tape lable after a tape is Initialized? (A) (i) Tape Name
(ii)Name of the Database
(iii) Time stamp of last backup recorded on the tape (iv) Number of Backups performed with the tape
Before writing data to tape if the lable is Red to check the following (i) Tape Name
(ii) Tape Locked or Expired(Expire_period)
(iii) No. of times the tape already been read(Tape_use_count)
If Expiration_period = 0 days, the Volume is not locked at all and can be over written
(Q) What are the methods used by BRBACKUP and BRARCHIVE to check tape locks?
(A) There are 2 types of locks
(i) Physical lock check: Physical lock check is done by checking tape label parameter Expir_period. If the number of days passed since the tape was last used is less than value of parameter Expir_period, then the tape is physically locked.
(ii) Logical lock check: This value is derived from the time stamp written to tables SDBAH, SDBAD
(Q) What are the various tape selection processes? (A) (i) Auto tape selection BRBACKUP and BRARCH (ii) Manual selection by the Operator
(iii)By external tool
(Q) What is the option to select the tapes automatically by BRBACKUP and BRARCH?
(A) Set the parameter Volume_Backup and Volume_archive to TAPE
(Q) What is the command to check which tape will be automatically selected? (A) BR Backup | BRARCHIVE –Q | Query { check }
(Q) How do we switch off automatic tape Management?
(A) By setting up the parameter(Volume Backup and Volume Archive) to the value “SCRATCH”
(Q) How do I turnoff the tape management performed by SAP tools? (A) Configure the parameter Backup_dev_type= UTIL_FILE
OR
UTIL_FILE_ONLINE and also configure BACKINT interface in init<SID>.sap NOTE: BackINT Interface program is only supported for external Backup. (Q) How do we verify Backups?
(A) Verification of backups is of 2 types
(i) Tape Verification: The files are restored file by file and compared with original files to verify if the backup is redable.
(ii) DB Block consistency: This checks the Database block by block using Oracle tool “DBVERIFY” to identify and restore from bad blocks.
PATH: BRTOOLSBackup & DBcopyVerification of DB Backup, Verification of Archive log Backup
(iv) The option USE_DBV(DBVERIFY=NO), only tape is verified (If yes Tape verification + DB Block Consistancy Check)
STATUS OF OFFLINE REDO LOG FILES: (1) During Backup to tape= ARCHIVE (2) First Status= SAVED
SECOND STATUS=COPIED AFTER DELETION = DELETED During BACKUP TO Disk = DISK
NOTE: All the above status are recorded in ARCH<SID>.log ANALYZING Database PROBLEMS:
(1) Check Database alert log and trace files belonging to Bgprocess (SAP Trace/Background)
(i) Check for status of Database = Available or NOT Available (ii) Check for Error = Media or User error
(iii) Check for corrupted files and file types = Data, Cofile, Online Redo log Files
(iv) Check if Software or Hardware Mirroring = Available or Not (2) Safest method is to perform a complete Offline Backup before the files are
copied back in restore place using BR Backup or any Backup Tools.
(3) The above step is Very Important for Point In Time Recovery or for Database rest because these stratagies always involve Data loss.
(4) Save Offline Redo Log Files in ORARCH Directory using BRArchive only. (5) To check the reliability of Backup strategy , run regularly restoration report
in SAP using DB12
(6) The above report is used to find out which backup to use for recovery as well as it displays information about last successful Backup.
(7) If the list of RedoLog files after the last Database Backup is too long, then perform a complete Database Backup.
BR Tools:
Login to ORA<SID> using putty Type BRTOOLS
There are totally 9 option in BR tools
a.
Select Instant management, it is option 1b.
In Database instance management select option 2 to shutdown the database.c. Type ‘C’ and click enter to continue
d.
In Database instance shutdown main menu select option 1 shutdown DB.e.
Under options for shutting down the DB instance we have to choose option 1, that is close mode(Default mode is immediate)f. Select option 1 and enter string value for ‘mode’ (Immediate| normal|transcations|abort).
Note: if the users are logged in to the SAP system then I cannot use immediate, normal, transactional modes, using abort mode will forcefully shutdown and will result to data loss hence never use this option so to be on the safest side always shutdown using normal mode.
Alter DB Instance (Switching off archive mode): Shut down SAP Stop SAP [SID<adm>]
Log on to ORA<SID> user and start BR tools
In BR tools Select option 1 (Instance Management) Start up database Select option 1
Alter DB instance Option 3 Enter ‘c’ to continue
Enter ‘c’ to continue
Select option 4 for set non archive mode
Enter ‘c ‘to continue and select option 5 to show instance status
Note: while switching to archive mode and non-archive mode, it will shutdown the DB instance first and then starts the DB instance. In each of these cases the time stamp is recorded that is data and time. Once the DB is up and running always check the status before performing any action.
(Q) If SAP started and I am trying to switch to non-archive mode what will happen. (A) It will show an error showing that SAP instance is running. Please showdown first or use force option.
(Q) If SAP is running and I try to shutdown the DB using BR tools what will happen. (A) It through an error saying that SAP is running please shutdown the SAP first or force option and then continue.
Table space administration:
1. Oracle stores data in table spaces, each table space consists of one or more data files.
2. Data files are plain files stored on local system 3. Oracle has 4 segment types
a.
Data This segment contains table data in rowsb.
Index Each table has one primary index and ‘n’ number of secondary indexes (optional). This index is used for faster access to table data and to enforce unique constrains.c.
Temp Segment This segment is used for sorts and to create indexes.d.
Roll back/undo segment this segment is used to provide read consistency that is ability to roll back changed to tables for recovery.4. To meet the demand of large DB, DB designers creates partition tables and indexes.
5. An index segment in oracle DB used in SAP holds either all data for take that is not partitioned or all data for a partition of partitioned table.
Common table spaces:
1.
System Oracle data dictionary2.
PSAP ROLL Roll back segmentNote: From WAS 6.1 version we have SAP undo as roll back segment.
3.
PSAP TEMP Temporary segment.(Q) If table space is full then what are the possibility to extend the table spaces? (A) Option 1: Add another data file to table space
2: Existing data file can be manually resized
3: Properties of existing data file can be changed to auto extendable (Q) What id the formula to increase the data files size?
(A) Data file size = Expected DB/100
(Q) How many number of data files will be there by default? (A) Default there are 100 data files
(Q) Expected DB size and Data file size
Expected DB Size Data File Size
Up to 200Gb 2Gb
200 to 400Gb 4Gb
400 to 800Gb 8Gb
Greater than 800Gb 60Gb (Q) What is the error related with table flow? (A) For table ORA1653, ORA1654 for indexes. (Q) What will happen if max extents are reached?
(A) ORA1533 is the error forms extent reached. If max extent is reaching it limits, then increase next extent. When extents are dripped they are marked as free and their blocks can be used by new extents, but adjacent blocks are not combined. The DBA must use “COALEXE” free extent into one large extent. There are two options for “COALEXE” extent.
1. BRCONNECT –f check COALEXE free extent automatically
2. BRSPACE –f check COALEXE free extent use locally managed table spaces.
To solve above problem with extent we must use locally managed table spaces. Segment Sizes Next segment Size Max.no.of Extent
Less than 1Mb Less than 64Mb 16
1 to 64Mb 1Mb 63
64Mb to 1Gb 8Mb 126
Greater than 1Gb 64Mb Unlimited
Advantage of LMTS (locally managed table spaces) is “ORA1533” error eill no longer occur. The only disadvantage of LMTS is, always it checks for used and free space.
Increase the Table space:
1. Log on to ORA<SID> and enter into BR tools. 2. Space management (option 2)
3. Extent table space (option 1) 4. Enter ‘c’ to continue
5. Enter ‘c’ to continue
It will give “Table space extension main menu”
Note: First use option 2 to show the table spaces and percentage full and make a note of a table space which is 80% and above fill and then add a data file as per the specification using the option 1 that is “extent table space”.
6. Extend table space (option 1)
7. This will list all table spaces and percentage used Example Table: “PSAPR3700”
8. Select the table space that is ‘pos’ position 9. Enter 2 to select above example table Note: options for extension of table space
a. Last added file name b. Last added file size in MB c. New file to be added d. Raw disk/link target e. Size of the new file in MB f. File auto extend mode = YES g. Max file size in MB = [10000] h. File increment size in MB = [20]
i. SQL Command = [alter table space name]
Note: the last added data file name and new file to be added will show the exact location where the data file is residing that is Oracle/<sid>/sapdata 1 to n/
10. Enter ‘c’ to continue
11. Enter option 5 to change the size of new file in MB 12. Press ‘c’ to continue
13. Select ‘NO’ to continue with the current data file addition.
14. Select ‘YES’ to add a new data file to the current table or add new data file to a new table.
Note: this action will update the time stamp in co-file that is, it created a copy of co-file in the location /oracle/<SID>/SAPREORA|[CNTRL<SID>.old]
Once co-file is created, extending of table space is done, one successfully completed it switches to next online redo log file for database instance and finally creates a copy of co-file with new time stamp that is CMTRL<SID>.news
Top 10 Oracle errors: