Remember your dreams

Remember your dreams
Remember your dreams

Friday, July 30, 2010

Oracle Disaster Recovery Test.

I will intentionally destroy my DEV instance in order to provide an example of restoring and recovering the database using Oracle RMAN an and RMAN Catalog.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production

Create a new pfile and backup the spfile just for kicks
SQL> create pfile from spfile;
mv spfiledev.ora spfiledev.old.ora

cd /u01/oradata/dev
# ls
control01.ctl
sysaux01.dbf
system01.dbf

#pwd
/u01/oradata/dev
# rm *

#cd /u02/oradata/dev/
# ls
control02.ctl
redo01a.rdo
redo02a.rdo
redo03a.rdo

#pwd
/u02/oradata/dev
# rm *

#cd /u03/oradata/dev/
# ls
control03.ctl
redo01b.rdo
redo02b.rdo
redo03b.rdo

#pwd
/u03/oradata/dev
# rm *

# cd /u04/oradata/dev/
# ls
undotbs01.dbf

#pwd
/u04/oradata/dev
# rm *

-- Login to sqlplus
SQL> select * from tab;
select * from tab
*
ERROR at line 1:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/oracle/u01/oradata/dev/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3













SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/u01/oradata/dev/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort;
ORACLE instance shut down.

# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 29 09:45:59 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.
ORA-00205: error in identifying control file, check alert log for more info

SQL> shutdown abort;
ORACLE instance shut down.

# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 29 09:58:42 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

-- login to rman and the rman catalog
#rman target / rcvcat_login/rcvcat_passwd@rcvcat
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jul 29 10:00:20 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEV (not mounted)
connected to recovery catalog database

RMAN> list incarnation of database dev;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
198582917 198582918 DEV 3800958841 CURRENT 1 25-JUN-10

RMAN> set dbid 3800958841
executing command: SET DBID
database name is "DEV" and DBID is 3800958841

RMAN> RESTORE SPFILE FROM AUTOBACKUP;

Starting restore at 29-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=114 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=130 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=146 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 6.5 (2010011113)

channel ORA_SBT_TAPE_1: looking for AUTOBACKUP on day: 20100729
channel ORA_SBT_TAPE_1: AUTOBACKUP found: c-3800958841-20100729-01
channel ORA_SBT_TAPE_2: looking for AUTOBACKUP on day: 20100729
channel ORA_SBT_TAPE_2: skipped, AUTOBACKUP already found
recovery area destination: /app/oracle/flash_recovery_area
database name (or database unique name) used for search: DEV
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20100729
channel ORA_DISK_1: skipped, AUTOBACKUP already found
recovery area destination: /app/oracle/flash_recovery_area
database name (or database unique name) used for search: DEV
channel ORA_DISK_2: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_2: looking for AUTOBACKUP on day: 20100729
channel ORA_DISK_2: skipped, AUTOBACKUP already found
channel ORA_SBT_TAPE_1: restoring spfile from AUTOBACKUP c-3800958841-20100729-01
channel ORA_SBT_TAPE_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 29-JUL-10

RMAN>
RMAN> SHUTDOWN IMMEDIATE;
Oracle instance shut down

RMAN> STARTUP FORCE NOMOUNT;
Oracle instance started

RMAN> run {
2> RESTORE CONTROLFILE FROM AUTOBACKUP;
3> ALTER DATABASE MOUNT;
4> }

Starting restore at 29-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=114 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=130 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=146 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 6.5 (2010011113)

channel ORA_SBT_TAPE_1: looking for AUTOBACKUP on day: 20100729
channel ORA_SBT_TAPE_2: skipped, AUTOBACKUP already found
recovery area destination: /app/oracle/flash_recovery_area
database name (or database unique name) used for search: DEV
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20100729
channel ORA_DISK_1: skipped, AUTOBACKUP already found
recovery area destination: /app/oracle/flash_recovery_area
database name (or database unique name) used for search: DEV
channel ORA_DISK_2: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_2: looking for AUTOBACKUP on day: 20100729
channel ORA_DISK_2: skipped, AUTOBACKUP already found
channel ORA_SBT_TAPE_1: restoring control file from AUTOBACKUP c-3800958841-20100729-01
channel ORA_SBT_TAPE_1: control file restore from AUTOBACKUP complete
output file name=/u01/oradata/dev/control01.ctl
output file name=/u02/oradata/dev/control02.ctl
output file name=/u03/oradata/dev/control03.ctl
Finished restore at 29-JUL-10

database mounted
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_SBT_TAPE_1
released channel: ORA_SBT_TAPE_2

-- Restore to the last archive log file backed up.
RMAN> list backup of archivelog from scn=1;
List of Backup Sets
===================
List of Archived Logs in backup set 208615940
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 40 5138860 27-JUL-10 5214805 28-JUL-10

RMAN> RUN {
2> SET UNTIL SEQUENCE 40 THREAD 1;
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> ALTER DATABASE OPEN RESETLOGS;
6> }

executing command: SET until clause

Starting restore at 30-JUL-10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2

channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00001 to /u01/oradata/dev/system01.dbf
channel ORA_SBT_TAPE_1: restoring datafile 00004 to /u06/oradata/dev/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece bk_62_1_725106021
channel ORA_SBT_TAPE_2: starting datafile backup set restore
channel ORA_SBT_TAPE_2: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_2: restoring datafile 00002 to /u01/oradata/dev/sysaux01.dbf
channel ORA_SBT_TAPE_2: restoring datafile 00003 to /u04/oradata/dev/undotbs01.dbf
channel ORA_SBT_TAPE_2: reading from backup piece bk_61_1_725106021
channel ORA_SBT_TAPE_1: piece handle=bk_62_1_725106021 tag=HOT_DB_BK_LEVEL0
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:55
channel ORA_SBT_TAPE_2: piece handle=bk_61_1_725106021 tag=HOT_DB_BK_LEVEL0
channel ORA_SBT_TAPE_2: restored backup piece 1
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:02:05
Finished restore at 30-JUL-10

Starting recover at 30-JUL-10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2

starting media recovery

channel ORA_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=36
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=37
channel ORA_SBT_TAPE_1: reading from backup piece al_69_1_725533318
channel ORA_SBT_TAPE_2: starting archived log restore to default destination
channel ORA_SBT_TAPE_2: restoring archived log
archived log thread=1 sequence=38
channel ORA_SBT_TAPE_2: restoring archived log
archived log thread=1 sequence=39
channel ORA_SBT_TAPE_2: reading from backup piece al_70_1_725533318
channel ORA_SBT_TAPE_2: piece handle=al_70_1_725533318 tag=TAG20100728T090157
channel ORA_SBT_TAPE_2: restored backup piece 1
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:00:45
channel ORA_SBT_TAPE_1: piece handle=al_69_1_725533318 tag=TAG20100728T090157
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:15
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_36_655w8sr0_.arc thread=1 sequence=36
channel default: deleting archived log(s)
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_36_655w8sr0_.arc RECID=36 STAMP=725704495
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_37_655w8sqg_.arc thread=1 sequence=37
channel default: deleting archived log(s)
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_37_655w8sqg_.arc RECID=37 STAMP=725704496
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_38_655w7xmk_.arc thread=1 sequence=38
channel default: deleting archived log(s)
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_38_655w7xmk_.arc RECID=35 STAMP=725704470
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_39_655w7xm0_.arc thread=1 sequence=39
channel default: deleting archived log(s)
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_39_655w7xm0_.arc RECID=34 STAMP=725704469
media recovery complete, elapsed time: 00:00:14
Finished recover at 30-JUL-10

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>exit

-- log into sqlplus and check the database status.
SQL> select status from v$instance;
STATUS
------------
OPEN

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
By -
Labels: , , ,

3 comments:

  1. MubeenAugust 27, 2011 at 12:49 AM

    good steps. nice example

    ReplyDelete
  2. architectural plan templateJuly 17, 2012 at 11:06 AM

    really good post and helped me very much.

    ReplyDelete
    Replies
    1. Oracle HackJuly 18, 2012 at 8:32 AM

      Thanks for the comments. It's good to know that some of these post can help others.

      Delete

Subscribe to: Post Comments (Atom)

Proactive Oracle DBA

This is a series of posts. I am working this to share some of the many scripts I schedule to automatically run to alert me of any current o...