Tag Archives: disaster

Oracle, Oracle 10g, oracle 9i, RMAN

Oracle RMAN – Recovery of entire Oracle database

Leave a comment

Oracle’s Recovery Manger (RMAN), originally released in Oracle 8, is a proprietary utility used for the backup and recovery of Oracle databases. Given a good Oracle RMAN backup, A DBA can recover from any disaster which may occur to their database, to include: loss of controlfiles, loss of datafiles, corruption of datafiles, loss of the entire database and even provides the ability to clone a database. In this article we give detailed instructions on the recovery of an entire Oracle database in Oracle 9i, however these instructions will work in versions 10g and 11g.

1. Logon to the Oracle server as the Oracle software owner and set the ORACLE_SID.

[oracle@testdb02 ORCL]$ su – oracle
Password:
[oracle@testdb02 ~]$ export ORACLE_SID=ORCL
[oracle@testdb02 ~]$

2. Logon to SQL*PLUS and obtain location of all datafiles from dba_data_files and location of all control files by the show parameter control_file command.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:45:27 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
/u01/oradata/ORCL/system01.dbf
/u04/oradata/ORCL/undo01.dbf
/u05/oradata/ORCL/users01.dbf
/u05/oradata/ORCL/tools01.dbf

SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————————————————–
/u05/oradata/ORCL/temp02.dbf

SQL> show parameter control_files

NAME TYPE VALUE
———————————— ———– ——————————
control_files string /u01/oradata/ORCL/control01.c
tl, /u03/oradata/ORCL/control
02.ctl, /u06/oradata/ORCL/con
trol03.ctl
SQL>

3. Navigate to each of the files listed and add the extension of _bak to the files.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ cd /u01/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
archive control01.ctl redolog1a.dbf redolog2b.dbf system01.dbf
[oracle@testdb02 ORCL]$ mv control01.ctl control01.ctl_bak
[oracle@testdb02 ORCL]$ mv system01.dbf system01.dbf_bak
[oracle@testdb02 ORCL]$ cd /u03/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
archive control02.ctl redolog1b.dbf
[oracle@testdb02 ORCL]$ mv control02.ctl control02.ctl_bak
[oracle@testdb02 ORCL]$ cd /u04/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
undo01.dbf
[oracle@testdb02 ORCL]$ mv undo01.dbf undo01.dbf_bak
[oracle@testdb02 ORCL]$ cd /u05/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
sqlnet.log temp02.dbf tools01.dbf users01.dbf
[oracle@testdb02 ORCL]$ mv temp02.dbf temp02.dbf_bak
[oracle@testdb02 ORCL]$ mv tools01.dbf tools01.dbf_bak
[oracle@testdb02 ORCL]$ mv users01.dbf users01.dbf_bak
[oracle@testdb02 ORCL]$ cd /u06/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
archive control03.ctl redolog2a.dbf
[oracle@testdb02 ORCL]$ mv control03.ctl control03.ctl_bak
[oracle@testdb02 ORCL]$ ls
archive control03.ctl_bak redolog2a.dbf
[oracle@testdb02 ORCL]$

4. Stop the Oracle database by killing the active SMON process of the ORCL instance.

[oracle@testdb02 ORCL]$ ps -ef|grep smon
oracle 14206 1 0 21:37 ? 00:00:00 ora_smon_ORCL
oracle 15121 13009 0 21:56 pts/4 00:00:00 grep smon
[oracle@testdb02 ORCL]$ kill -9 14206
[oracle@testdb02 ORCL]$ ps -ef|grep ORCL
oracle 12023 11973 0 20:51 pts/6 00:00:00 vim ORCL_RMAN.sh
oracle 15139 13009 0 21:56 pts/4 00:00:00 grep ORCL
[oracle@testdb02 ORCL]$

5. Enter SQL*PLUS and startup the database in nomount state. You have to startup the database with nomount in order to recovery the controlfiles.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:58:01 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 144670496 bytes
Fixed Size 741152 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 1323008 bytes
SQL>

6. Exit SQL*PLUS, enter RMAN and connect to the target database and recovery catalog.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ RMAN

Recovery Manager: Release 9.2.0.8.0 – 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/h1g0alz

connected to target database: ORCL (not mounted)

RMAN> connect catalog RMAN/RMAN@RMANDB

connected to recovery catalog database

7. Issue the restore database command, which replaces all database objects with the backup set copies.

RMAN> restore database;

Starting restore at 12-JUN-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/ORCL/system01.dbf
restoring datafile 00002 to /u04/oradata/ORCL/undo01.dbf
restoring datafile 00003 to /u05/oradata/ORCL/users01.dbf
restoring datafile 00004 to /u05/oradata/ORCL/tools01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220129_1_26_1_0qjiooh1.dbf tag=BACKUP_FULL_ORCL params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 12-JUN-08

8. Issue the restore controlfile command, which replaces all controlfiles with the backup set copies.

RMAN> restore controlfile;

Starting restore at 12-JUN-08

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring controlfile
output filename=/u01/oradata/ORCL/control01.ctl
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220219_1_27_1_0rjioojr.ctl tag=TAG20080612T171019 params=NULL
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/u01/oradata/ORCL/control01.ctl
output filename=/u03/oradata/ORCL/control02.ctl
output filename=/u06/oradata/ORCL/control03.ctl
Finished restore at 12-JUN-08

RMAN>

9. Exit RMAN and enter SQL*PLUS, alter the database to a mount state. The mount state will allow you to restore the database using the archive redo logs.

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 22:06:33 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database mount;

10. Exit SQL*PLUS and enter RMAN utility, connect to the target database and catalog.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ RMAN

Recovery Manager: Release 9.2.0.8.0 – 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/h1g0alz

connected to target database: ORCL (DBID=1373796530)

RMAN> connect catalog RMAN/RMAN@RMANDB

connected to recovery catalog database

11. Issue the recovery database command. This will apply all archive redo logs to the datafiles and controlfiles.

RMAN> recover database;

Starting recover at 12-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK

starting media recovery

archive log thread 1 sequence 28 is already on disk as file /u01/oradata/ORCL/archive/ORCL_ARCH28.arc
archive log thread 1 sequence 29 is already on disk as file /u06/oradata/ORCL/archive/ORCL_ARCH29.arc
archive log thread 1 sequence 30 is already on disk as file /u06/oradata/ORCL/redolog2a.dbf
archive log thread 1 sequence 31 is already on disk as file /u03/oradata/ORCL/redolog1b.dbf
archive log filename=/u01/oradata/ORCL/archive/ORCL_ARCH28.arc thread=1 sequence=28
archive log filename=/u06/oradata/ORCL/archive/ORCL_ARCH29.arc thread=1 sequence=29
archive log filename=/u06/oradata/ORCL/redolog2a.dbf thread=1 sequence=30
archive log filename=/u03/oradata/ORCL/redolog1b.dbf thread=1 sequence=31
media recovery complete
Finished recover at 12-JUN-08

RMAN>

12. Exit RMAN, enter SQL*PLUS and alter the database open with reset logs command. NOTE: you must perform new full database RMAN backup after you reset the logs. ANY backup taken prior will now be useless.

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 22:10:06 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database open resetlogs;

Database altered.

SQL>

This completes the recovery of full database.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com