Restoring a database using rman

In the last post I demonstrated how to take a cold backup of a database using nocatalog mode. In this post I would be demonstrating:

a) How to take a backup of a database using recovery catalog
b) Simulate a hardware failure where all files would be lost (Control files + Redo logs + Data Files).
c) Then restore the database from the backup taken.


1) Set the appropriate environment, startup the database and verify the database file location.
[oracle@lxhyd01 ~]$ . oraenv
ORACLE_SID = [oracle] ? ebsdev
[oracle@lxhyd01 ~]$ sqlplus '/as sysdba'
SQL> startup
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 2083528 bytes
Variable Size 318768440 bytes
Database Buffers 872415232 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data01/oradata/ebsdev/system01.dbf
/data01/oradata/ebsdev/undotbs01.dbf
/data01/oradata/ebsdev/sysaux01.dbf
/data01/oradata/ebsdev/users01.dbf
/data01/oradata/ebsdev/example01.dbf

SQL>

2) Since the database is not in archive log mode, we have to take a cold backup of the database.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 1
Current log sequence 2
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lxhyd01 ~]$

3) Review the script to take the cold backup.

[oracle@lxhyd01 ~]$ cat level0_ebsdev.cmd
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
backup
incremental level=0
tag ebsdevLevel0
format '/data01/backup/ebsdev%U'
database include current controlfile;

release channel d1;
release channel d2;
}
[oracle@lxhyd01 ~]$

4) Connect to rman.

[oracle@lxhyd01 ~]$ rman target / catalog rcat/rcat@rman

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 23 18:57:26 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN>
RMAN> startup mount

Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 01/23/2010 18:57:50
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

5) This database was not registered in the catalog. So, we register it.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> exit

Recovery Manager complete.

6) Now execute the backup

[oracle@lxhyd01 ~]$ rman target / catalog rcat/rcat@rman cmdfile=/home/oracle/level0_ebsdev.cmd

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 23 19:21:01 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: EBSDEV (DBID=2521112594, not open)
connected to recovery catalog database

RMAN> run {
2> allocate channel d1 type disk;
3> allocate channel d2 type disk;
4> backup
5> incremental level=0
6> tag ebsdevLevel0
7> format '/data01/backup/ebsdev%U'
8> database include current controlfile;
9>
10> release channel d1;
11> release channel d2;
12> }
13>
allocated channel: d1
channel d1: sid=159 devtype=DISK

allocated channel: d2
channel d2: sid=155 devtype=DISK

Starting backup at 23-JAN-10
channel d1: starting incremental level 0 datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00001 name=/data01/oradata/ebsdev/system01.dbf
input datafile fno=00004 name=/data01/oradata/ebsdev/users01.dbf
channel d1: starting piece 1 at 23-JAN-10
channel d2: starting incremental level 0 datafile backupset
channel d2: specifying datafile(s) in backupset
input datafile fno=00003 name=/data01/oradata/ebsdev/sysaux01.dbf
input datafile fno=00005 name=/data01/oradata/ebsdev/example01.dbf
input datafile fno=00002 name=/data01/oradata/ebsdev/undotbs01.dbf
channel d2: starting piece 1 at 23-JAN-10
channel d2: finished piece 1 at 23-JAN-10
piece handle=/data01/backup/ebsdev03l4718v_1_1 tag=EBSDEVLEVEL0 comment=NONE
channel d2: backup set complete, elapsed time: 00:04:26
channel d2: starting incremental level 0 datafile backupset
channel d2: specifying datafile(s) in backupset
including current control file in backupset
channel d2: starting piece 1 at 23-JAN-10
channel d2: finished piece 1 at 23-JAN-10
piece handle=/data01/backup/ebsdev04l471ha_1_1 tag=EBSDEVLEVEL0 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:13
channel d2: starting incremental level 0 datafile backupset
channel d2: specifying datafile(s) in backupset
including current SPFILE in backupset
channel d2: starting piece 1 at 23-JAN-10
channel d2: finished piece 1 at 23-JAN-10
piece handle=/data01/backup/ebsdev05l471hp_1_1 tag=EBSDEVLEVEL0 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:03
channel d1: finished piece 1 at 23-JAN-10
piece handle=/data01/backup/ebsdev02l4718v_1_1 tag=EBSDEVLEVEL0 comment=NONE
channel d1: backup set complete, elapsed time: 00:06:30
Finished backup at 23-JAN-10

released channel: d1

released channel: d2

Recovery Manager complete.
[oracle@lxhyd01 ~]$

7) Validate the backup taken.

[oracle@lxhyd01 ~]$ rman target / catalog rcat/rcat@rman

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 23 19:56:33 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: EBSDEV (DBID=2521112594, not open)
connected to recovery catalog database

RMAN> list backup ;


List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
37 Incr 0 219.48M DISK 00:04:22 23-JAN-10
BP Key: 41 Status: AVAILABLE Compressed: NO Tag: EBSDEVLEVEL0
Piece Name: /data01/backup/ebsdev03l4718v_1_1
List of Datafiles in backup set 37
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 688114 23-JAN-10 /data01/oradata/ebsdev/undotbs01.dbf
3 0 Incr 688114 23-JAN-10 /data01/oradata/ebsdev/sysaux01.dbf
5 0 Incr 688114 23-JAN-10 /data01/oradata/ebsdev/example01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
38 Incr 0 7.08M DISK 00:00:10 23-JAN-10
BP Key: 42 Status: AVAILABLE Compressed: NO Tag: EBSDEVLEVEL0
Piece Name: /data01/backup/ebsdev04l471ha_1_1
Control File Included: Ckp SCN: 688114 Ckp time: 23-JAN-10

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
39 Incr 0 80.00K DISK 00:00:01 23-JAN-10
BP Key: 43 Status: AVAILABLE Compressed: NO Tag: EBSDEVLEVEL0
Piece Name: /data01/backup/ebsdev05l471hp_1_1
SPFILE Included: Modification time: 23-JAN-10

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40 Incr 0 366.66M DISK 00:06:29 23-JAN-10
BP Key: 44 Status: AVAILABLE Compressed: NO Tag: EBSDEVLEVEL0
Piece Name: /data01/backup/ebsdev02l4718v_1_1
List of Datafiles in backup set 40
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 688114 23-JAN-10 /data01/oradata/ebsdev/system01.dbf
4 0 Incr 688114 23-JAN-10 /data01/oradata/ebsdev/users01.dbf

RMAN>
RMAN> RESTORE VALIDATE DATABASE;

Starting restore at 23-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /data01/backup/ebsdev03l4718v_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/data01/backup/ebsdev03l4718v_1_1 tag=EBSDEVLEVEL0
channel ORA_DISK_1: validation complete, elapsed time: 00:00:57
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /data01/backup/ebsdev02l4718v_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/data01/backup/ebsdev02l4718v_1_1 tag=EBSDEVLEVEL0
channel ORA_DISK_1: validation complete, elapsed time: 00:00:55
Finished restore at 23-JAN-10

RMAN>
RMAN> exit


Recovery Manager complete.

8) Simulate a failure, Remove all the database files at OS level.

[oracle@lxhyd01 ~]$ rm -f /data01/oradata/ebsdev/*
[oracle@lxhyd01 ~]$ ls -ltr /data01/oradata/ebsdev/*
ls: /data01/oradata/ebsdev/*: No such file or directory
[oracle@lxhyd01 ~]$

9) Restore the database from the backup taken.

[oracle@lxhyd01 ~]$ rman target / catalog rcat/rcat@rman

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 23 20:15:51 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1207959552 bytes

Fixed Size 2083528 bytes
Variable Size 318768440 bytes
Database Buffers 872415232 bytes
Redo Buffers 14692352 bytes

10) Since the controlfile is missing as well, We should restore the controlfile first.

RMAN> restore controlfile;

Starting restore at 23-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /data01/backup/ebsdev04l471ha_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/data01/backup/ebsdev04l471ha_1_1 tag=EBSDEVLEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
output filename=/data01/oradata/ebsdev/control01.ctl
output filename=/data01/oradata/ebsdev/control02.ctl
output filename=/data01/oradata/ebsdev/control03.ctl
Finished restore at 23-JAN-10

RMAN>
RMAN> restore database;

Starting restore at 23-JAN-10
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /data01/oradata/ebsdev/undotbs01.dbf
restoring datafile 00003 to /data01/oradata/ebsdev/sysaux01.dbf
restoring datafile 00005 to /data01/oradata/ebsdev/example01.dbf
channel ORA_DISK_1: reading from backup piece /data01/backup/ebsdev03l4718v_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/data01/backup/ebsdev03l4718v_1_1 tag=EBSDEVLEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:01:46
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /data01/oradata/ebsdev/system01.dbf
restoring datafile 00004 to /data01/oradata/ebsdev/users01.dbf
channel ORA_DISK_1: reading from backup piece /data01/backup/ebsdev02l4718v_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/data01/backup/ebsdev02l4718v_1_1 tag=EBSDEVLEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 23-JAN-10

RMAN>

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


RMAN> alter database open resetlogs;

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

RMAN>

11) Verify the status of the database from v$instance.

[oracle@lxhyd01 ~]$ sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jan 23 20:27:47 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 ebsdev
lxhyd01
10.2.0.4.0 23-JAN-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL>