Web Analytics

Oracle Database 11.2 simple disaster recovery scenario

Svetoslav Gyurov

• 10 min read

This is simple scenario in which I will simulate full database loss and then I’ll do an incomplete database recovery. The server is installed with Oracle Enterprise Linux 5.6, Oracle Grid Infrastructure 11.2 and Oracle Database 11.2. First I’ll create a backup, then shutdown the database and delete all corresponding data files and then I’ll restore from the backup I already created.

The database is running in archivelog:

SQL> archive log list;
Database log mode                  Archive Mode
Automatic archival                 Enabled
Archive destination                USE_DB_RECOVERY_FILE_DEST

Backup the database

Login as Oracle and start rman to create backup:

[oracle@oelvm4 ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jul 28 15:21:03 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1284876027)

RMAN>

Review the current parameters, only non-default parameter is controlfile 	autobackup:

RMAN>show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # 	default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/oracle/product/11.2.0	/dbhome_1/dbs/snapcf_orcl.f'; # default

Make sure that CONTROLFILE AUTOBACKUP is ON.This will make RMAN to create auto backup of the server parameter file (spfile) and control file after each structural change.

Start full database backup plus archivelogs:

RMAN> backup database plus archivelog delete input;

Starting backup at 28-JUL-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=757100104
channel ORA_DISK_1: starting piece 1 at 28-JUL-11
channel ORA_DISK_1: finished piece 1 at 28-JUL-11
piece handle=+FRA/orcl/backupset/2011_07_28/annnf0_tag20110728t152150_0.266.757696911 tag=TAG20110728T152150 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+FRA/orcl/archivelog/2011_07_21/thread_1_seq_1.262.757096029 RECID=1 STAMP=757100104
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=5 STAMP=757100609
input archived log thread=1 sequence=2 RECID=6 STAMP=757100809
channel ORA_DISK_1: starting piece 1 at 28-JUL-11
channel ORA_DISK_1: finished piece 1 at 28-JUL-11
piece handle=+FRA/orcl/backupset/2011_07_28/annnf0_tag20110728t152150_0.262.757696917 tag=TAG20110728T152150 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+FRA/orcl/archivelog/2011_07_21/thread_1_seq_1.264.757100609 RECID=5 STAMP=757100609
archived log file name=+FRA/orcl/archivelog/2011_07_21/thread_1_seq_2.260.757100809 RECID=6 STAMP=757100809
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=10 STAMP=757696910
channel ORA_DISK_1: starting piece 1 at 28-JUL-11
channel ORA_DISK_1: finished piece 1 at 28-JUL-11
piece handle=+FRA/orcl/backupset/2011_07_28/annnf0_tag20110728t152150_0.260.757696919 tag=TAG20110728T152150 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+FRA/orcl/archivelog/2011_07_28/thread_1_seq_1.268.757696911 RECID=10 STAMP=757696910
Finished backup at 28-JUL-11

Starting backup at 28-JUL-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/orcl/datafile/system.258.757101411	
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.259.757101411	
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.260.757101411
input datafile file number=00004 name=+DATA/orcl/datafile/users.261.757101411
channel ORA_DISK_1: starting piece 1 at 28-JUL-11
channel ORA_DISK_1: finished piece 1 at 28-JUL-11
piece handle=+FRA/orcl/backupset/2011_07_28/nnndf0_tag20110728t152200_0.268.757696921 tag=TAG20110728T152200 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 28-JUL-11

Starting backup at 28-JUL-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=11 STAMP=757696976
channel ORA_DISK_1: starting piece 1 at 28-JUL-11
channel ORA_DISK_1: finished piece 1 at 28-JUL-11
piece handle=+FRA/orcl/backupset/2011_07_28/annnf0_tag20110728t152256_0.265.757696977 tag=TAG20110728T152256 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+FRA/orcl/archivelog/2011_07_28/thread_1_seq_2.264.757696975 RECID=11 STAMP=757696976
Finished backup at 28-JUL-11

Starting Control File and SPFILE Autobackup at 28-JUL-11
piece handle=+FRA/orcl/autobackup/2011_07_28/s_757696977.264.757696979 comment=NONE
Finished Control File and SPFILE Autobackup at 28-JUL-11

Simulate disaster

Now, to simulate the disaster I will shutdown the database and erase the ORCLÂ directory from DATAÂ diskgroup and controlfile and onlinelogs from FRAÂ diskgroup. Additionaly the spfile will be deleted. This will force me to recover the lost database from the backup I already created.

Shutdown the instance and delete the database spfile:

RMAN> shutdown abort

Oracle instance shut down

RMAN> quit

Recovery Manager complete.
[oracle@oelvm4 ~]$ cd /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@oelvm4 dbs]$ rm spfileorcl.ora
[oracle@oelvm4 dbs]$ rm initorcl.ora

In my case the database spfile was not placed on the first diskgroup, but on the file system because ASM was installed later, after database was created.

Delete the ORCL directory from asm DATA diskgroup, delete controfile and onlinelogs from FRA diskgroup:

[oracle@oelvm4 ~]$ asmcmd
ASMCMD> ls -l
State    Type    Rebal  Name
MOUNTED  EXTERN  N      DATA/
MOUNTED  EXTERN  N      FRA/
ASMCMD> cd data
ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        Y    ASM/
                                        Y    ORCL/
ASMCMD> rm -r orcl
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD> cd ../fra/orcl
ASMCMD> rm -r CONTROLFILE/ ONLINELOG/
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD> exit
[oracle@oelvm4 ~]$

Restore the database

To restore the database IÂ assume that:

Server is installed and appropriate parameters are set.
Oracle Grid Infrastructure software is installed and ASMÂ instance is created. Both diskgroup DATAÂ and FRA are mounted.
Oracle Database software is installed.
You know the database DBID

From Oracle docs, DBID is an internal, uniquely generated number that differentiates databases. Oracle creates this number automatically when you create the database.

Start the database in nomount state and set DBID:

[oracle@oelvm4 dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jul 28 16:09:31 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> set dbid=1284876027;

executing command: SET DBID

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2224584 bytes
Variable Size                 88084024 bytes
Database Buffers              62914560 bytes
Redo Buffers                   5439488 bytes

RMAN>

We start the database with dummy spfile and it says that the server parameter could not be found. The next step is to restore spfile to appropriate location from autobackup.

Restore spfile from autobackup:

RMAN> restore spfile to '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora' from autobackup db_recovery_file_dest='+FRA' db_name='orcl';

Starting restore at 28-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 device type=DISK

recovery area destination: +FRA
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP +fra/ORCL/AUTOBACKUP/2011_07_28	/s_757696977.264.757696979 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20110728
channel ORA_DISK_1: restoring spfile from AUTOBACKUP +fra/ORCL/AUTOBACKUP/2011_07_28/s_757696977.264.757696979
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 28-JUL-11

This is very cool feature, we say to RMAN, please search for an autobackup at +FRA asm diskgroup for database name orcl. Because RMAN knows how to search for an autobackup he is able to find it and restore the spfile. Now we can start the database with our original spfile and continue with restoring the controfile.

Restart the instance with restored spfile:

Before we do that, make sure that initorcl.ora file point to the correct location of the spfile. In my case this is in the file system and not in the diskgroup as it is by default. This is because IÂ installed the database first and then created ASMÂ instance. Either way, make sure $ORACLE_HOME/dbs/initorcl.ora point to correct location of the restored spfile.

RMAN> shutdown immediate

Oracle instance shut down

RMAN> quit

Recovery Manager complete.
[oracle@oelvm4 dbs]$ cd $ORACLE_HOME/dbs
[oracle@oelvm4 dbs]$ cat > initorcl.ora
SPFILE='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora'
[oracle@oelvm4 dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jul 28 16:17:26 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area    1653518336 bytes

Fixed Size                     2227032 bytes
Variable Size                989856936 bytes
Database Buffers             654311424 bytes
Redo Buffers                   7122944 bytes

Restore the control files and mount database:

Now as we have a running instance with our database spfile we can proceed with restoring the control file. Once again we set dbid and restore the controfile from autobackup. Now we don’t have to specify FRA location or/and SID because all the information needed is already read from spfile. The parameter control_files from spfile shows where the control files should be restored to.

RMAN> set dbid=1284876027;

executing command: SET DBID

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Starting restore at 28-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 device type=DISK

recovery area destination: +FRA
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP +fra/ORCL/AUTOBACKUP/2011_07_28/s_757696977.264.757696979 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20110728
channel ORA_DISK_1: restoring control file from AUTOBACKUP +fra/ORCL/AUTOBACKUP	/2011_07_28/s_757696977.264.757696979
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATA/orcl/controlfile/current.265.757700503
output file name=+FRA/orcl/controlfile/current.259.757700503
Finished restore at 28-JUL-11

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Restore and recover the database:

The last step before opening the database is to restore the database files and recover the database up to date where the disaster happen. This is achieved by applying all the archive logs from the backup. If the archive logs are already db_recovery_file_dest (FRA) or there are newer they will be applied.

[oracle@oelvm4 dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jul 28 16:29:27 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1284876027, not open)

RMAN> restore database;

Starting restore at 28-JUL-11
Starting implicit crosscheck backup at 28-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 28-JUL-11

Starting implicit crosscheck copy at 28-JUL-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 28-JUL-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +fra/ORCL/AUTOBACKUP/2011_07_28/s_757696977.264.757696979
File Name: +fra/ORCL/AUTOBACKUP/2011_07_21/s_757100818.267.757100819

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/orcl/datafile/system.258.757101411
channel ORA_DISK_1: restoring datafile 00002 to +DATA/orcl/datafile/sysaux.259.757101411
channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/datafile/undotbs1.260.757101411
channel ORA_DISK_1: restoring datafile 00004 to +DATA/orcl/datafile/users.261.757101411
channel ORA_DISK_1: reading from backup piece +FRA/orcl/backupset/2011_07_28/nnndf0_tag20110728t152200_0.268.757696921
channel ORA_DISK_1: piece handle=+FRA/orcl/backupset/2011_07_28/nnndf0_tag20110728t152200_0.268.757696921 tag=TAG20110728T152200
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 28-JUL-11

RMAN> restore database;

Starting restore at 28-JUL-11
using channel ORA_DISK_1

skipping datafile 1; already restored to file +DATA/orcl/datafile/system.264.757700981
skipping datafile 2; already restored to file +DATA/orcl/datafile/sysaux.263.757700981
skipping datafile 3; already restored to file +DATA/orcl/datafile/undotbs1.262.757700981
skipping datafile 4; already restored to file +DATA/orcl/datafile/users.261.757700981
restore not done; all files read only, offline, or already restored
Finished restore at 28-JUL-11

RMAN> recover database;

Starting recover at 28-JUL-11
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2
channel ORA_DISK_1: reading from backup piece +FRA/orcl/backupset/2011_07_28/annnf0_tag20110728t152256_0.265.757696977
channel ORA_DISK_1: piece handle=+FRA/orcl/backupset/2011_07_28/annnf0_tag20110728t152256_0.265.757696977 tag=TAG20110728T152256
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/orcl/archivelog/2011_07_28/thread_1_seq_2.258.757701187 thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=+FRA/orcl/archivelog/2011_07_28/thread_1_seq_2.258.757701187 RECID=12 STAMP=757701186
	unable to find archived log
archived log thread=1 sequence=3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/28/2011 16:33:07
RMAN-06054: media recovery requesting unknown archived log for thread 1 with 	sequence 3 and starting SCN of 1002978

The error at the end indicates that RMANÂ is requesting the online redo logs which are not present, because of that the currect restore is a kind of incomplete recovery. The other option is to used this query once the database is mounted:

SELECT archivelog_change# FROM v$database;

and restore the database to the previous archivelog_change# with set until clause in RMANÂ restore script.

Either way would required the database to be opened with resetlogs which is the next and last step to complete database recovery.

Open the database:

The last step is to open the database. Because we’ve lost all the database files. including the online logs database has to be opened with option resetlogs.

[oracle@oelvm4 dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jul 28 16:56:51 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1284876027, not open)

RMAN> alter database open resetlogs; 

using target database control file instead of recovery catalog
database opened

RMAN>

The following could be see at alert log:

2011-07-28 17:00:02.986000 +03:00
alter database open resetlogs
RESETLOGS after incomplete recovery UNTIL CHANGE 1002978

...
Clearing online redo logfile 1 complete

...
Clearing online redo logfile 2 complete

...
Clearing online redo logfile 3 complete

...
Resetting resetlogs activation ID 1284910294 (0x4c962cd6)
Online log +DATA/orcl/onlinelog/group_1.260.757702803: Thread 1 Group 1 was previously cleared
Online log +FRA/orcl/onlinelog/group_1.258.757702807: Thread 1 Group 1 was 	previously cleared
Online log +DATA/orcl/onlinelog/group_2.259.757702813: Thread 1 Group 2 was previously cleared
Online log +FRA/orcl/onlinelog/group_2.257.757702817: Thread 1 Group 2 was previously cleared
Online log +DATA/orcl/onlinelog/group_3.258.757702821: Thread 1 Group 3 was previously cleared
Online log +FRA/orcl/onlinelog/group_3.256.757702825: Thread 1 Group 3 was previously cleared
Setting recovery target incarnation to 5
Assigning activation ID 1285452027 (0x4c9e70fb)
...

Re-creating tempfile +DATA/orcl/tempfile/temp.265.757101627 as +DATA/orcl/tempfile/temp.257.757702831

Conclusion

This is only to show how to perform disaster recovery in case of complete database loss or/and server loss.

For more information refer to the Oracle documentation, which is a priceless source of knowledge:
Oracle® Database Backup and Recovery Users Guide 11g Release 2 (11.2)

Sign up for more like this.

Enter your email
Subscribe