Modern Oracle DBA

  If a media failure has affected the online redo logs of a database, then the appropriate recovery procedure depends on the following: The configuration of the online redo log: mirrored or non-mirrored the type of media failure: temporary or permanent the types of online redo log files affected by the media failure: CURRENT, ACTIVE, UNARCHIVED, or INACTIVE The database was shutdown normally before loss of archivelog file Scenario 1: Loss of INACTIVE Online Redo Log Group Step 1: Shutdown the database Step 2: Mount the database. SQL> STARTUP MOUNT Step 3: Check the V$LOG view to determine if the file has been archived. SQL> select GROUP#,MEMBERS,STATUS, ARCHIVED from v$log; Step 4: If file is archived execute the following command. SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2; Clearing an inactive, online redo log group that has been archived: Step 1: Start Dtabase is mount Step 2: Check the V$LOG view to check the file status archived/Not SQL>

  RMAN Recovery catalog is a schema that is created in separate DB. It contained RMAN metadata obtained from the target database control file. RMAN stores, uses, and maintains the information in the recovery catalog. The recovery catalog is maintained by RMAN when you do following Register the target database in catalog Resynchronize the catalog with control file of target DB Change the information about the backups or files. Perform backup,restore,recovery operations   RMAN Catalog Contents Datafile and archived redo log file backup sets and backup pieces. Datafile copies Archive and redo log files The physical structure of the target database.   Why use a recovery catalog? It makes restore if all the target database is lost, including the controlfile. As that controlfile contains all backup information, the catalog would then be used to identify a controlfile backup If an RMAN catalog is not used, the controlfile autobackup should be configured to

  RMAN validate and crosscheck commands are using for maintenance activity RMAN Validate RMAN Validate is the 11g New feature. It’s used to find the corrupted blocks and missing files This command is check for validity of data files and update the v$database_block_corruption view in case it finds any corruption Validate command can also use to check Database, Tablespace, Datafile, Spfile, Archivelog, Control file   RMAN VALIDATE COMMANDS Validate whole Database RMAN> backup validate database; Validate Tablespace RMAN> backup validate tablespace system; Validate a specific datafile RMAN> backup validate datafile 8; Validate SPFILE RMAN> backup validate spfile; Validate archived redo log files RMAN> backup validate archivelog all; Validate current control file RMAN> backup validate current control file;   Following commands, we will see backup is valid and available for restoration process.   RMAN> restore database valid

Result cache introduce in 11g It’s area of memory either in SGA/ client application memory It’s allows results of query to be cached in SGA Possible to reduce amount of physical I/O, logical I/O, number of sorts, amount of CPU   How Result cache works When a query executes, the database searches the cache memory to determine whether the result exists in the result cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache. When users execute queries and functions repeatedly, the database retrieves rows from the cache, decreasing response time. Cached results become invalid when data in dependent database objects is modified.   Result cache Type There are two types of result cache there Server Result cache Used shared memory Available to all session Client Result cache

Oracle Application R12.2.3 Instance Health Check - PROD   1.Database Server :<Hostanme> 2.Oracle RDBMS Version: 11.2.0.3.0 3: Operating System : Red Hat Enterprise Linux Server release 6.6 (Santiago) 4.Disk Utilization Volume Total Disk Space Size in GB Available Disk Space Size in GB / 100G 64G /Oracle 984G 244G /Backup 606G 201G   5 Background services Background Services Status SMON UP POMN UP CKPT UP LGWR UP DBWR UP Listner status UP     6.Alert Log check for critical ORA Errors No errors 7.Instance Efficiency Parameter Recommended Current status Remark Buffer Hit Ratio 98 93   L