Example of a complete Oracle RMAN backup and recovery

The target database version is oracle8.1.7, the directory database version is oracle9.2.0, and all data files (including control files and On-line log files) are lost. Examples of using RMAN for backup and recovery are as follows:

Tutorial steps:

1. Create a tablespace in the directory database to restore the directory:

SQL> Create tablespace rmants datafile '/opt/Oracle/DB02/oradata/orcl/rmants. dbf' size 100 m autoextend on next 1 m maxsize unlimited;

Tablespace created.

2. Create and authorize the RMAN user in the directory database:

SQL> create user RMAN identified by RMAN default tablespace rmants temporary tablespace temp quota unlimited on rmants;

User Created.

SQL> grant recovery_catalog_owner to RMAN;

Grant succeeded.

3. Create a recovery directory in the directory database:

$ RMAN catalog RMAN/RMAN

Recovery MANAGER: Release 9.2.0.4.0-64bit Production

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

Connected to recovery Catalog Database
Recovery catalog Is Not Installed

RMAN> Create catalog tablespace rmants;
Recovery catalog created

4. register the target database to the recovery directory
The service name used by my target database is rcat.
$ RMAN target/(execute this command on the target database and connect to the target database with RMAN first)

Recovery MANAGER: Release 8.1.7.3.0-Production

RMAN-06005: connected to target database: orcl (dbid = 1000277484)

RMAN> connect catrman RMAN/RMAN @ rcat (connect to the catalog directory database)

RMAN-06008: connected to recovery Catalog Database

RMAN> Register database; (register the target database on the catalog directory database)

RMAN-03022: compiling command: Register
RMAN-03023: Executing command: Register
The RMAN-08006: Database registered in recovery catalog
RMAN-03023: Executing command: Full Resync
RMAN-08002: Starting full Resync of recovery catalog
RMAN-08004: Full Resync complete

The preparation is complete. Now we use RMAN for backup and recovery.

5. Complete RMAN backup:
RMAN> RUN
{
Allocate channel C1 type disk;
Backup full tag 'dbfull' format'/opt/Oracle/full % u _ % P _ % C' database include current controlfile;
SQL 'alter system archive log current ';
Backup filesperset 3 archivelog all Delete input;
Release Channel C1;
}

RMAN-03022: compiling command: Allocate
RMAN-03023: Executing command: Allocate
RMAN-08030: allocated channel: C1
RMAN-08500: Channel C1: SID = 15 devtype = Disk

RMAN-03022: compiling command: Backup
RMAN-03023: Executing command: Backup
RMAN-08008: Channel C1: Starting full datafile backupset
RMAN-08502: set_count = 3 set_stamp = 537970127 creation_time = 27-sep-04
RMAN-08010: Channel C1: specifying datafile (s) in backupset
RMAN-08522: Input datafile fno = 00001 name =/opt/Oracle/DB02/oradata/orcl/system01.dbf
RMAN-08011: including current controlfile in backupset
RMAN-08522: Input datafile fno = 00004 name =/opt/Oracle/DB02/oradata/orcl/temp01.dbf
RMAN-08522: Input datafile fno = 00019 name =/opt/Oracle/DB02/oradata/orcl/rbs02.dbf
RMAN-08522: Input datafile fno = 00003 name =/opt/Oracle/DB02/oradata/orcl/rbs03.dbf
RMAN-08522: Input datafile fno = 00002 name =/opt/Oracle/DB02/oradata/orcl/tools01.dbf
RMAN-08522: Input datafile fno = 00006 name =/opt/Oracle/DB02/oradata/orcl/indx01.dbf
RMAN-08522: Input datafile fno = 00005 name =/opt/Oracle/DB02/oradata/orcl/users01.dbf
RMAN-08522: Input datafile fno = 00007 name =/opt/Oracle/DB02/oradata/orcl/drsys01.dbf
RMAN-08522: Input datafile fno = 00010 name =/opt/Oracle/DB02/oradata/orcl/test. DBF

RMAN-08013: Channel C1: piece 1 created
RMAN-08503: piece handle =/opt/Oracle/full01_11hef_1_1 comment = none
RMAN-08525: Backup set complete, elapsed time: 00:02:55
RMAN-03023: Executing command: Partial Resync
RMAN-08003: Starting partial Resync of recovery catalog
RMAN-08005: Partial Resync complete

RMAN-03022: compiling command: SQL
RMAN-06162: SQL statement: Alter system archive log current
RMAN-03023: Executing command: SQL

RMAN-03022: compiling command: Backup
RMAN-03025: discovery Ming implicit partial Resync of recovery catalog
RMAN-03023: Executing command: Partial Resync
RMAN-08003: Starting partial Resync of recovery catalog
RMAN-08005: Partial Resync complete
RMAN-03023: Executing command: Backup
RMAN-08009: Channel C1: Starting archivelog backupset
RMAN-08502: set_count = 4 set_stamp = 537970305 creation_time = 27-sep-04
RMAN-08014: Channel C1: specifying archivelog (s) in backup set
RMAN-08504: Input archivelog thread = 1 sequence = 1 recid = 7 stamp = 537958625
RMAN-08013: Channel C1: piece 1 created
RMAN-08503: piece handle =/opt/Oracle/db01/APP/Oracle/product/8.1.7/dbs/04g11hk1_1_1 comment = none
RMAN-08525: Backup set complete, elapsed time: 00:00:02
RMAN-08071: Channel C1: deleting archivelog (s)
RMAN-08514: archivelog filename =/opt/Oracle/ARCH/orcl/arch_1_1.arc recid = 7 stamp = 537958625
RMAN-08009: Channel C1: Starting archivelog backupset
RMAN-08502: set_count = 5 set_stamp = 537970307 creation_time = 27-sep-04
RMAN-08014: Channel C1: specifying archivelog (s) in backup set
RMAN-08504: Input archivelog thread = 1 sequence = 2 recid = 8 stamp = 537958661
RMAN-08504: Input archivelog thread = 1 sequence = 3 recid = 9 stamp = 537959443
RMAN-08504: Input archivelog thread = 1 sequence = 4 recid = 10 stamp = 537970304
RMAN-08013: Channel C1: piece 1 created
RMAN-08503: piece handle =/opt/Oracle/db01/APP/Oracle/products/8.1.7/dbs/05g11hk3_1_1 comment = none
RMAN-08525: Backup set complete, elapsed time: 00:00:02
RMAN-08071: Channel C1: deleting archivelog (s)
RMAN-08514: archivelog filename =/opt/Oracle/ARCH/orcl/arch_1_2.arc recid = 8 stamp = 537958661
RMAN-08514: archivelog filename =/opt/Oracle/ARCH/orcl/arch_1_3.arc recid = 9 stamp = 537959443
RMAN-08514: archivelog filename =/opt/Oracle/ARCH/orcl/arch_rj4.arc recid = 10 stamp = 537970304
RMAN-03023: Executing command: Partial Resync
RMAN-08003: Starting partial Resync of recovery catalog
RMAN-08005: Partial Resync complete

RMAN-03022: compiling command: Release
RMAN-03023: Executing command: Release
RMAN-08031: released channel: C1

RMAN> exit
Recovery manager complete.

6. view the database backup information:

$ RMAN target/catalog rman/RMAN @ rcat

Recovery MANAGER: Release 8.1.7.3.0-Production

RMAN-06005: connected to target database: orcl (dbid = 1000277484)
RMAN-06008: connected to recovery Catalog Database

RMAN> List backup;
RMAN-03022: compiling command: List

List of backup Sets
Key recid stamp LV set Stamp Set count completion time
-----------------------------------------------------------------------
22 2 537970293 0 537970127 3 27-sep-04

List of backup pieces
Key PC # cp # status completion time piece name
----------------------------------------------------------------------
23 1 1 available 27-sep-04/opt/Oracle/full01_11hef_1_1

List of datafiles encoded DED
File Name LV type ckp scn ckp time
----------------------------------------------------------------------
1/opt/Oracle/DB02/oradata/orcl/system01.dbf 0 full 5226242 27-sep-04
2/opt/Oracle/DB02/oradata/orcl/tools01.dbf 0 full 5226242 27-sep-04
3/opt/Oracle/DB02/oradata/orcl/rbs03.dbf 0 full 5226242 27-sep-04
4/opt/Oracle/DB02/oradata/orcl/temp01.dbf 0 full 5226242 27-sep-04
5/opt/Oracle/DB02/oradata/orcl/users01.dbf 0 full 5226242 27-sep-04
6/opt/Oracle/DB02/oradata/orcl/indx01.dbf 0 full 5226242 27-sep-04
7/opt/Oracle/DB02/oradata/orcl/drsys01.dbf 0 full 5226242 27-sep-04
10/opt/Oracle/DB02/oradata/orcl/test. DBF 0 full 5226242 27-sep-04
19/opt/Oracle/DB02/oradata/orcl/rbs02.dbf 0 full 5226242 27-sep-04

7. Shut down the database, simulate the loss of all data files, and manually delete all data files, control files, and online log files:
$ LSNRCTL stop

LSNRCTL for Solaris: Version 8.1.7.3.0-production on 27-sep-2004 12:30:39

(C) copyright 1998 Oracle Corporation. All rights reserved.

Connecting to (description = (address = (Protocol = TCP) (host = ipasdb) (Port = 1521 )))
The command completed successfully

$ Sqlplus internal/Oracle

SQL * Plus: Release 8.1.7.0.0-production on Mon Sep 27 12:29:12 2004

(C) Copyright 2000 Oracle Corporation. All rights reserved.

Connected:
Oracle8i Enterprise Edition Release 8.1.7.3.0-64bit Production
With the partitioning Option
Jserver release 8.1.7.3.0-64bit Production

SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.

$ RM/opt/Oracle/DB02/oradata/orcl /*
$ RM/opt/Oracle/db03/oradata/orcl /*
$ RM/opt/Oracle/DB04/oradata/orcl /*

8. Use RMAN for full recovery:
(1) first, start the database to the nomount status:
$ Sqlplus internal/Oracle

SQL * Plus: Release 8.1.7.0.0-production on Mon Sep 27 12:32:37 2004

(C) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
Oracle instance started.

Total system global area 1134141116 bytes
Fixed size 102076 bytes
Variable Size 311750656 bytes
Database buffers 819200000 bytes
Redo buffers 3088384 bytes

(2). Restore the control file Before restoring the entire database:
$ RMAN target/catalog rman/RMAN @ rcat

Recovery MANAGER: Release 8.1.7.3.0-Production

RMAN-06006: connected to target database: orcl (not mounted)
RMAN-06008: connected to recovery Catalog Database

RMAN> run {
Allocate channel C1 type disk;
Restore controlfile;
Release Channel C1;
}

RMAN-03022: compiling command: Allocate
RMAN-03023: Executing command: Allocate
RMAN-08030: allocated channel: C1
RMAN-08500: Channel C1: SID = 14 devtype = Disk

RMAN-03022: compiling command: Restore

RMAN-03022: compiling command: irestore
RMAN-03023: Executing command: irestore
RMAN-08016: Channel C1: Starting datafile backupset restore
RMAN-08502: set_count = 3 set_stamp = 537970127 creation_time = 27-sep-04
RMAN-08021: Channel C1: Restoring controlfile
The RMAN-08505: output filename =/opt/Oracle/DB02/oradata/orcl/control01.ctl
RMAN-08023: Channel C1: restored backup piece 1
RMAN-08511: piece handle =/opt/Oracle/full01_11hef_1_1 tag = dbfull Params = NULL
RMAN-08024: Channel C1: restore complete
RMAN-08058: replicating controlfile
RMAN-08506: Input filename =/opt/Oracle/DB02/oradata/orcl/control01.ctl
RMAN-08505: output filename =/opt/Oracle/db03/oradata/orcl/control02.ctl
RMAN-08505: output filename =/opt/Oracle/DB04/oradata/orcl/control03.ctl

RMAN-03022: compiling command: Release
RMAN-03023: Executing command: Release
RMAN-08031: released channel: C1

(3). restore archived log files:
RMAN> run {
Set archivelog destination to '/opt/Oracle/ARCH/orcl ';
Allocate channel C1 type disk;
Restore archivelog all;
Release Channel C1;
}

RMAN-03022: compiling command: Set

RMAN-03022: compiling command: Allocate
RMAN-03023: Executing command: Allocate
RMAN-08030: allocated channel: C1
RMAN-08500: Channel C1: SID = 14 devtype = Disk

RMAN-03022: compiling command: Restore

RMAN-03022: compiling command: irestore
RMAN-03023: Executing command: irestore
RMAN-08018: Channel C1: Starting archivelog restore to user-specified destination
RMAN-08508: archivelog Destination =/opt/Oracle/ARCH/orcl
RMAN-08022: Channel C1: Restoring archivelog
RMAN-08510: archivelog thread = 1 sequence = 1
RMAN-08023: Channel C1: restored backup piece 1
RMAN-08511: piece handle =/opt/Oracle/db01/APP/Oracle/product/8.1.7/dbs/04g11hk1_1_1 tag = NULL Params = NULL
RMAN-08024: Channel C1: restore complete
RMAN-08018: Channel C1: Starting archivelog restore to user-specified destination
RMAN-08508: archivelog Destination =/opt/Oracle/ARCH/orcl
RMAN-08022: Channel C1: Restoring archivelog
RMAN-08510: archivelog thread = 1 sequence = 2
RMAN-08022: Channel C1: Restoring archivelog
RMAN-08510: archivelog thread = 1 sequence = 3
RMAN-08022: Channel C1: Restoring archivelog
RMAN-08510: archivelog thread = 1 sequence = 4
RMAN-08023: Channel C1: restored backup piece 1
RMAN-08511: piece handle =/opt/Oracle/db01/APP/Oracle/product/8.1.7/dbs/05g11hk3_1_1 tag = NULL Params = NULL
RMAN-08024: Channel C1: restore complete

RMAN-03022: compiling command: Release
RMAN-03023: Executing command: Release
RMAN-08031: released channel: C1

(4) Restore all data files and open the database in resetlogs mode:
RMAN> run {
Allocate channel C1 type disk;
SQL 'alter database mount ';
Restore database;
Recover database;
SQL 'alter database open resetlogs ';
}

RMAN-03022: compiling command: Allocate
RMAN-03023: Executing command: Allocate
RMAN-08030: allocated channel: C1
RMAN-08500: Channel C1: SID = 12 devtype = Disk

RMAN-03022: compiling command: SQL
RMAN-06162: SQL statement: Alter database Mount
RMAN-03023: Executing command: SQL

RMAN-03022: compiling command: Restore
RMAN-03025: discovery Ming implicit partial Resync of recovery catalog
RMAN-03023: Executing command: Partial Resync
RMAN-08003: Starting partial Resync of recovery catalog
RMAN-08005: Partial Resync complete

RMAN-03022: compiling command: irestore
RMAN-03023: Executing command: irestore
RMAN-08016: Channel C1: Starting datafile backupset restore
RMAN-08502: set_count = 3 set_stamp = 537970127 creation_time = 27-sep-04
RMAN-08089: Channel C1: specifying datafile (s) to restore from backup set
RMAN-08523: Restoring datafile 00001 to/opt/Oracle/DB02/oradata/orcl/system01.dbf
RMAN-08523: Restoring datafile 00002 to/opt/Oracle/DB02/oradata/orcl/tools01.dbf
RMAN-08523: Restoring datafile 00003 to/opt/Oracle/DB02/oradata/orcl/rbs03.dbf
RMAN-08523: Restoring datafile 00004 to/opt/Oracle/DB02/oradata/orcl/temp01.dbf
RMAN-08523: Restoring datafile 00005 to/opt/Oracle/DB02/oradata/orcl/users01.dbf
RMAN-08523: Restoring datafile 00006 to/opt/Oracle/DB02/oradata/orcl/indx01.dbf
RMAN-08523: Restoring datafile 00007 to/opt/Oracle/DB02/oradata/orcl/drsys01.dbf
RMAN-08523: Restoring datafile 00010 to/opt/Oracle/DB02/oradata/orcl/test. DBF
RMAN-08523: Restoring datafile 00019 to/opt/Oracle/DB02/oradata/orcl/rbs02.dbf

RMAN-08023: Channel C1: restored backup piece 1
RMAN-08511: piece handle =/opt/Oracle/full01_11hef_1_1 tag = dbfull Params = NULL
RMAN-08024: Channel C1: restore complete
RMAN-03023: Executing command: Partial Resync
RMAN-08003: Starting partial Resync of recovery catalog
RMAN-08005: Partial Resync complete

RMAN-03022: compiling command: recover

RMAN-03022: compiling command: recover (1)
RMAN-03023: Executing command: Partial Resync

RMAN-03022: compiling command: recover (2)

RMAN-03022: compiling command: recover (3)
RMAN-03023: Executing command: recover (3)
RMAN-08054: starting media recovery

RMAN-03022: compiling command: recover (4)
RMAN-03023: Executing command: recover (4)
RMAN-08017: Channel C1: Starting archivelog restore to default destination
RMAN-08022: Channel C1: Restoring archivelog
RMAN-08510: archivelog thread = 1 sequence = 4
RMAN-08023: Channel C1: restored backup piece 1
RMAN-08511: piece handle =/opt/Oracle/db01/APP/Oracle/product/8.1.7/dbs/05g11hk3_1_1 tag = NULL Params = NULL
RMAN-08024: Channel C1: restore complete
RMAN-08515: archivelog filename =/opt/Oracle/ARCH/orcl/arch_00004.arc thread = 1 sequence = 4
RMAN-08060: unable to find archivelog
RMAN-08510: archivelog thread = 1 sequence = 5
RMAN-03026: Error Recovery releasing channel resources
RMAN-08031: released channel: C1
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-03002: failure during compilation of command
RMAN-03013: Command type: recover
RMAN-03006: Non-retryable error occurred during execution of command: recover (4)
RMAN-07004: unhandled exception during command execution on channel default
RMAN-20000: Abnormal Termination of job step
RMAN-06054: Media recovery requesting unknown log: thread 1 SCN 5226245

RMAN> exit
Recovery manager complete.

Some error messages are found. The cause of analysis is mainly because arch_1_5.arc archiving logs are not found. At this time, you can only manually execute alter database open resetlogs to open the database:
SQL> alter database open resetlogs;

Database altered.

Note: When the database is opened, some data may be lost, mainly the data in the current redolog. Therefore, use the current redolog to restore the last log file in the previous step.

View the database status:
SQL> select status from V $ instance;

Status
-------
Open

(5). Restore the RMAN Database Synchronization:
$ RMAN target/catalog rman/RMAN @ rcat

Recovery MANAGER: Release 8.1.7.3.0-Production

RMAN-06005: connected to target database: orcl (dbid = 1000277484)
RMAN-06008: connected to recovery Catalog Database
RMAN> Reset database;

RMAN-03022: compiling command: reset
RMAN-03023: Executing command: reset
The RMAN-08006: Database registered in recovery catalog
RMAN-03023: Executing command: Full Resync
RMAN-08002: Starting full Resync of recovery catalog
RMAN-08004: Full Resync complete
RMAN> exit
Recovery manager complete.