Tales From A Lazy Fat DBA

Fan of Oracle DB & Performance, PostgreSQL & Cassandra … \,,/

  • Likes

    • 236,072
  • Archives

  • Categories

  • Subscribe

Posts Tagged ‘recovery’

11g Data Recovery Advisor: Diagnosing and Repairing Failures

Posted by FatDBA on December 9, 2012

Oracle’s 11g New Feature ‘Data Recovery Advisor’ is one of the most important tool introduced by The Red Giants with the release of r2. I will of course consider this functionality as one of the most impressive and best among others added in 11g r2 package. The Repair Advisor can take away lot of the stress associated with peforming backup and recovery by diagnosing what is wrong as well as presenting us with the syntax as well to execute the commands to restore and recover as the case may be. Under pressure, everyone can make mistakes and it is comforting to know that there is a tool which can really he;p the DBA.

The Data Recovery Advisor can be used via OEM Database or Grid Control or via the RMAN command line interface.


Let me Explain it using a real time issue: (Missed one of the control file).
Here I’ve intentionally deleted Control File 2 (Below provided was past status)

SQL> select name from v$controlfile;



Once the file is removed or corrupted it will start throwing below provided error message with code to check error log (Alert Log)

ORA-00205: error in identifying control file, check alert log for more info

Launch RMAN console and connect to the database (NO MOUNT Mode – Pretty Obvious!!) and check failures using ‘list failure’ command and will show you problem detected by DB engine.


RMAN> list failure;

List of Database Failures

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
322        CRITICAL OPEN      09-DEC-12     Control file /u01/app/oracle/flash_recovery_area/orcl/control02.ctl is missi                                                                     ng

RMAN> advise failure;

List of Database Failures

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
322        CRITICAL OPEN      09-DEC-12     Control file /u01/app/oracle/flash_recovery_area/orcl/control02.ctl is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
no manual actions available

Optional Manual Actions
no manual actions available

Automated Repair Options
Option Repair Description
—— ——————
1      Use a multiplexed copy to restore control file /u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2219809221.hm


RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2219809221.hm

contents of repair script:
# restore control file using multiplexed copy
restore controlfile from ‘/u01/app/oracle/oradata/orcl/control01.ctl’;
sql ‘alter database mount’;

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 09-DEC-12
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 09-DEC-12

sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened

It’s back!!!!

SQL> select name from v$controlfile;


Posted in Advanced | Tagged: , | Leave a Comment »

RMAN-06056: RMAN Engine failed to access any datafile (How to Skip)

Posted by FatDBA on December 9, 2012

Today when trying to backup full database, I’ve  encountered error which says RMAN failed to access one of the datafile. I found that datafile is not of any use, hence  I’ve  dropped the datafile offline but still RMAN not allowed me to backup database. Here is one of the resolution to fix such issue when you are sure that datafile is not of any use.

RMAN> backup database;

Starting backup at 09-DEC-12
using channel ORA_DISK_1
could not read file header for datafile 9 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/09/2012 14:28:13
RMAN-06056: could not access datafile 9

RMAN> backup database skip inaccessible;

Starting backup at 09-DEC-12
using channel ORA_DISK_1
could not access datafile 9
skipping inaccessible file 9
RMAN-06060: WARNING: skipping datafile compromises tablespace RMAN recoverability
RMAN-06060: WARNING: skipping datafile compromises tablespace RMAN recoverability
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00006 name=/u01/app/oracle/aliflaila
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00008 name=/u01/app/oracle/oradata/orcl/undotbs007.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/orcl/users02.dbf
channel ORA_DISK_1: starting piece 1 at 09-DEC-12

Posted in Advanced | Tagged: , | Leave a Comment »

Scenario: Recovering a dropped Table.

Posted by FatDBA on December 7, 2012

One not-uncommon error is the accidental dropping of a table from your database. In general, the fastest and simplest solution is to use the flashback drop feature, to reverse the dropping of the table. However, if for some reason, such as flashback drop being disabled or the table having been dropped with the PURGE option, you cannot use flashback table.

To recover a table that has been accidentally dropped, use the following procedure:

1. If possible, keep the database that experienced the user error online and available for use. Back up all datafiles of the existing database in case an error is made during the remaining steps of this procedure.

2. Restore a database backup to an alternate location, then perform incomplete recovery of this backup using a restored backup control file, to the point just before the table was dropped.

3. Export the lost data from the temporary, restored version of the database using an Oracle export utility. In this case, export the accidentally dropped table.

4. Use an Oracle import utility to import the data back into the production database.

5. Delete the files of the temporary copy of the database to conserve space.

Posted in Advanced | Tagged: , | Leave a Comment »

Recovery: Types ?

Posted by FatDBA on November 5, 2012

What Are the Types of Recovery?

There are three basic types of recovery: instance recovery, crash recovery, and media recovery. Oracle performs the first two types of recovery automatically at instance startup; only media recovery requires you to issue commands.

Instance Recovery
Instance recovery, which is only possible in an OPS (Oracle Parallel Server) configuration, occurs in an open database when one instance discovers that another instance has crashed. A surviving instance automatically uses the redo log to recover the committed data in the database buffers that was lost when the instance failed. Further, Oracle undoes any transactions that were in progress on the failed instance when it crashed and then clears any locks held by the crashed instance after recovery is complete.
Crash Recovery
Crash recovery occurs when either a single-instance database crashes or all instances of a multi-instance database crash. In crash recovery, an instance must first open the database and then execute recovery operations. In general, the first instance to open the database after a crash or SHUTDOWN ABORT automatically performs crash recovery.
Media Recovery
Unlike crash and instance recovery, media recovery is executed on your command. In media recovery, you use online and archived redo logs and (if using RMAN) incremental backups to make a restored backup current or to update it to a specific time. It is called media recovery because you usually perform it in response to media failure. Media recovery is a process of restoring the physical backups and making all the restored datafiles consistent with each other by having same SCN’s in their header’s.

If you do not use Recovery Manager (RMAN) to perform recovery, then you should use the SQL*Plus RECOVER command. It is also possible to use the SQL statement ALTER DATABASE RECOVER, but it is highly recommended that you use the SQL*Plus RECOVER command instead.

When using SQL*Plus to perform media recovery, the easiest strategy is to perform automatic recovery. Automatic recovery initiates recovery without manually prompting SQL*Plus to apply each individual archived log.

When using SQL*Plus, you have two options for automating the application of the default filenames of archived redo logs needed during recovery:

  • Specifying the AUTOMATIC keyword as an option of the RECOVER command   – RECOVER AUTOMATIC DATABASE


what are the types of media recovery in oracle database?

  1. Complete recovery
  2. Incomplete recovery

Though it is complete recovery or incomplete recovery, the recovery types classified based on what type of failure we are going to recover.

  1. Full database recovery
  2. Tablespace recovery
  3. Datafile recovery
Types of Media recovery
Complete Recovery Incomplete Recovery
Complete Recovery involves recovery of database with out any data loss i.e full recovery. Making all datafiles consistent with each other i.e having latest SCN and opening database normally with out resetlogs option. Incomplete Recovery means recovering database to a specified time, SCN no, log sequence no or using cancel. In this type of recovery the database can open only with RESETLOG option and database can not open normally.

What is complete media recovery?

Complete media recovery is process of restoring full database, a tablespace or a datafile from backup based upon media failure and applying the redo log files to the most current time with of loosing any data’s.

To determine which files to recover:

SQL> SELECT * FROM v$recover_file;

FILE#      ONLINE  ERROR              CHANGE#    TIME     
---------- ------- ------------------ ---------- ---------
        14 ONLINE                              0          
        15 ONLINE  FILE NOT FOUND              0          
        21 OFFLINE OFFLINE NORMAL              0

When you perform complete recovery, you can either recover the whole database at once or recover individual tablespaces or datafiles. Because you do not have to open the database with the RESETLOGS option after complete recovery as you do after incomplete recovery, you have the option of recovering some datafiles at one time and the remaining datafiles later. Types of recoveries comes under Complete Media Recovery:

  • Performing Closed Database Recovery
  • Performing Open Database Recovery

How complete full database media recovery are made (Performing CLOSED DATABASE Recovery)?

Following way of complete full database recovery is used in both operating system command along with sqlplus recovery command or RMAN utility.

    • Shutdown your database using SHUTDOWN ABORT option
    • Mount the Database using STARTUP MOUNT
    • Restore all the datafiles from the backup and make all datafiles online.
    • Apply online redo log files or archived redo log files or both.
    • open the database with below command.

sql>alter database open;

How complete tablespace or datafile recovery made (Performing  OPEN DATABASE recovery) ?

Following methods applies to both operating system commands along with Sqlplus RECOVERY command or RMAN utility method to recover datafile or tablespace.

    • During database open take tablespace or datafile which need recovery offline.
    • Restore the backup tablespace or datafile.
    • Apply the online redo log files or archive redo log files or both.
    • Open the database with below command.

sql>alter database open;

How incomplete recovery done in oracle database?

Incomplete recovery in oracle database are done using following option when applying archived redo log files and online redo log files.

  1. Time based recovery
  2. Cancel based recovery
  3. Change based recovery
  4. Log sequence recovery

The recover database until  command supports three clauses that will abort the recovery process at a specified point:

  • recover database until time  ‘YYYY-MM-DD:HH24:MI:SS’ recovers the database to a specified point in time.
  • recover database until cancel recovers the database applying one log file at a time until the recovery process is manually canceled
  • recover database until change <scn>recovers the database to a specified system change number ( SCN)

After using any one option open the database with following commands,

sql>alter database open resetlogs;

The above statement reset the log sequence to 0 and this command is used when we need incomplete recovery.

What are the methods of media recovery available in oracle database?

  1. Recovery using operating system.
  2. RMAN Recovery Manager.
Methods of Media recovery
Using Operating system commands and sqlplus RECOVERY command RMAN (Recovery Manager)
This old traditional method of physical backup and recovery available in oracle. From oracle 9i onwards oracle recommends to use RMAN which overcomes the disadvantages in backup and recovery using operating system commands. Since backup taken with OS commands can be used with RMAN utility. But backup taken with RMAN cannot used with OS commands for media recovery directly. RMAN means recovery manager to define it in simple words like logical utility (import,impdp) this is physical backup and recovery utility comes along with oracle database software. This makes oracle database server itself to take backup and recovery it during failure. This backup information are stored in target(backup) database control file and catalog database scheme(optional one if it is used).

Posted in Advanced | Tagged: | Leave a Comment »


Posted by FatDBA on September 24, 2012

What is and why there is a need to open database in RESETLOGS mode ?

Whenever you perform incomplete recovery or recovery with a backup control file, you must reset the online logs when you open the database.

– Archives the current online redo logs (if they are accessible) and then erases the contents of   the online redo logs and resets the log sequence number to 1.

– Reinitializes the control file metadata about online redo logs and redo threads.

– The RESETLOGS prevents you from corrupting datafiles with archived logs that are not from   direct parent incarnations of the current incarnation.

To reset the log sequence number when opening a database after recovery and thereby create a new incarnation of the database, execute the following statement:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1

Posted in Advanced | Tagged: | Leave a Comment »

%d bloggers like this: