How to Restore 10g RAC Database from a Backup Set

For 12c RAC database restoration, you may check: How to Restore 12c RAC Database from a Backup Set.

Suppose we have recovered 10g CRS and ASM from a fatal crash, but the database is still waiting for our saving. Luckily, we have a backup set of the database, which is a full and consistent database backup.

In this post, I will show you how to restore a 10g cluster database starting from restoring SPFILE. Some steps might seem to be nonsense, but they're necessary to restore the cluster database correctly.

Before we start to restore the database, let's make sure the clusterware is healthy.

[oracle@primary01 ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
[oracle@primary01 ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....B1.inst application    ONLINE    OFFLINE
ora....B2.inst application    ONLINE    OFFLINE
ora.PRIMDB.db  application    ONLINE    OFFLINE
ora....SM1.asm application    ONLINE    ONLINE    primary01
ora....01.lsnr application    ONLINE    ONLINE    primary01
ora....y01.gsd application    ONLINE    ONLINE    primary01
ora....y01.ons application    ONLINE    ONLINE    primary01
ora....y01.vip application    ONLINE    ONLINE    primary01
ora....SM2.asm application    ONLINE    ONLINE    primary02
ora....02.lsnr application    ONLINE    ONLINE    primary02
ora....y02.gsd application    ONLINE    ONLINE    primary02
ora....y02.ons application    ONLINE    ONLINE    primary02
ora....y02.vip application    ONLINE    ONLINE    primary02

And make sure the backup set is on the server, node 1.

[oracle@primary01 ~]$ ll /backup
total 647324
-rw-r----- 1 oracle oinstall 646799360 Jun 15 13:51 PRIMDB_685307556_0fu45h84_1_1
-rw-r----- 1 oracle oinstall  15368192 Jun 15 13:51 PRIMDB_685307556_0gu45h8j_1_1
-rw-r----- 1 oracle oinstall     32256 Jun 15 13:51 PRIMDB_685307556_0hu45h8q_1_1

Restore Cluster Database

Detach Database from Clusterware

For maintenance, we have to detach the database from the clusterware in case of any accidents.

[oracle@primary01 ~]$ export ORACLE_SID=PRIMDB1
[oracle@primary01 ~]$ srvctl disable database -d PRIMDB

Startup a Dummy Instance

We startup a dummy instance for restoring an initial SPFILE from the backup set.

[oracle@primary01 ~]$ rman target /
...
connected to target database (not started)

RMAN> startup nomount force;

startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+ORA_DATA/PRIMDB/spfilePRIMDB.ora'
ORA-17503: ksfdopn:2 Failed to open file +ORA_DATA/PRIMDB/spfilePRIMDB.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +ORA_DATA/primdb/spfileprimdb.ora
ORA-17503: ksfdopn:2 Failed to open file +ORA_DATA/primdb/spfileprimdb.ora
ORA-15173: entry 'primdb' does not exist in directory '/'
ORA-06512: at line 4

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

Total System Global Area     159383552 bytes

Fixed Size                     1218268 bytes
Variable Size                 58722596 bytes
Database Buffers              96468992 bytes
Redo Buffers                   2973696 bytes

Restore SPFILE to a Local Directory

In this step, we restore SPFILE from a backup piece essentially, but we did not restore SPFILE directly to the disk group. Instead, we restore SPFILE to a local directory, say /tmp. We will use the restored SPFILE to create a PFILE. This is for solving SPFILE restored in DB_UNKNOWN problem.

RMAN> restore spfile to '/tmp/spfilePRIMDB.ora' from '/backup/PRIMDB_685307556_0gu45h8j_1_1';

Starting restore at 15-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=33 devtype=DISK

channel ORA_DISK_1: autobackup found: /backup/PRIMDB_685307556_0gu45h8j_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 15-JUN-19

Then shutdown the instance.

RMAN> shutdown immediate;

Oracle instance shut down

Create a PFILE for Later Startups

SQL> create pfile='/tmp/initPRIMDB.ora' from spfile='/tmp/spfilePRIMDB.ora';

File created.

Startup Database to Nomount by PFILE

Then startup the database to nomount state by the PFILE.

SQL> startup nomount pfile='/tmp/initPRIMDB.ora';
ORACLE instance started.

Total System Global Area  922746880 bytes
Fixed Size                  1222648 bytes
Variable Size             260048904 bytes
Database Buffers          658505728 bytes
Redo Buffers                2969600 bytes

Restore Controlfile

This step will restore controlfiles to the original location which is used to be in +ORA_DATA/PRIMDB/ in the shared storage. RMAN> restore controlfile from '/backup/PRIMDB_685307556_0gu45h8j_1_1';

Starting restore at 15-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=535 instance=PRIMDB1 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:17
output filename=+ORA_DATA/primdb/control01.ctl
output filename=+ORA_DATA/primdb/control02.ctl
output filename=+ORA_DATA/primdb/control03.ctl
Finished restore at 15-JUN-19

Then we shutdown the instance.

RMAN> shutdown immediate;

Oracle instance shut down

Startup Database to Mount by PFILE

We have controlfiles restored in their original location, now we can mount the database normally.

RMAN> startup mount pfile='/tmp/initPRIMDB.ora';

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     922746880 bytes

Fixed Size                     1222648 bytes
Variable Size                260048904 bytes
Database Buffers             658505728 bytes
Redo Buffers                   2969600 bytes

If you saw ORA-00205 like this:

ORA-00205: error in identifying control file, check alert log for more info

This is because RMAN restored control file(s) with different names in Step 6. You have to shutdown the instance and do Step 8 to 10 in How to Restore 12c RAC Database from a Backup Set instead of Step 7 (this step) and 8 in this post.

Restore SPFILE to ASM Diskgroup

This step will restore SPFILE to its original location which is +ORA_DATA/PRIMDB/ in the shared storage.

RMAN> restore spfile from '/backup/PRIMDB_685307556_0gu45h8j_1_1';

Starting restore at 15-JUN-19
Starting implicit crosscheck backup at 15-JUN-19
allocated channel: ORA_DISK_1
Crosschecked 9 objects
Finished implicit crosscheck backup at 15-JUN-19

Starting implicit crosscheck copy at 15-JUN-19
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-JUN-19

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: autobackup found: /backup/PRIMDB_685307556_0gu45h8j_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 15-JUN-19

Restart Database to Mount

We have both SPFILE and controlfiles restored, let's see whether they can work together as usual.

RMAN> shutdown immediate;

database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     922746880 bytes

Fixed Size                     1222648 bytes
Variable Size                260048904 bytes
Database Buffers             658505728 bytes
Redo Buffers                   2969600 bytes

OK, they can work together.

Catalog the Backup Set

Before we can use the backup set to restore the database, controlfiles need to know their existence.

RMAN> catalog start with '/backup/';

searching for all files that match the pattern /backup/

List of Files Unknown to the Database
=====================================
File Name: /backup/PRIMDB_685307556_0hu45h8q_1_1
File Name: /backup/PRIMDB_685307556_0gu45h8j_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /backup/PRIMDB_685307556_0hu45h8q_1_1
File Name: /backup/PRIMDB_685307556_0gu45h8j_1_1

Restore Data Files

Use the cataloged backup set to restore the database, specifically, data files.

RMAN> restore database;

Starting restore at 15-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=536 instance=PRIMDB1 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 +ORA_DATA/primdb/system01.dbf
restoring datafile 00002 to +ORA_DATA/primdb/undotbs01.dbf
restoring datafile 00003 to +ORA_DATA/primdb/sysaux01.dbf
restoring datafile 00004 to +ORA_DATA/primdb/users01.dbf
restoring datafile 00005 to +ORA_DATA/primdb/example01.dbf
restoring datafile 00006 to +ORA_DATA/primdb/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /backup/PRIMDB_685307556_0fu45h84_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/backup/PRIMDB_685307556_0fu45h84_1_1 tag=TAG20190615T114612
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 15-JUN-19

Recover Database

We have archived logs in the backup set, so we can recover the data files to a consistent state.

RMAN> recover database;

Starting recover at 15-JUN-19
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=12
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=12
channel ORA_DISK_1: reading from backup piece /backup/PRIMDB_685307556_0hu45h8q_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/backup/PRIMDB_685307556_0hu45h8q_1_1 tag=TAG20190615T114634
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=+ORA_DATA/primdb/1_12_1010751018.dbf thread=1 sequence=12
archive log filename=+ORA_DATA/primdb/2_12_1010751018.dbf thread=2 sequence=12
unable to find archive log
archive log thread=2 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/15/2019 14:54:51
RMAN-06054: media recovery requesting unknown log: thread 2 seq 13 lowscn 567325

The error messages are just some notifications, you can ignore them.

Open Database

Since this is a point-in-time recovery, we have to open the database with reset redo logs. In this step, the log sequence will be reset and redo logs are created.

RMAN> alter database open resetlogs;

database opened

Then we shutdown the instance.

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

Reattach Database to Clusterware

[oracle@primary01 ~]$ srvctl enable database -d PRIMDB

Startup Database by Clusterware

We have already restore the database back, let's startup the cluster database by the clusterware.

[oracle@primary01 ~]$ srvctl start database -d primdb
[oracle@primary01 ~]$ srvctl status database -d primdb
Instance PRIMDB1 is running on node primary01
Instance PRIMDB2 is running on node primary02
[oracle@primary01 ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....B1.inst application    ONLINE    ONLINE    primary01
ora....B2.inst application    ONLINE    ONLINE    primary02
ora.PRIMDB.db  application    ONLINE    ONLINE    primary01
ora....SM1.asm application    ONLINE    ONLINE    primary01
ora....01.lsnr application    ONLINE    ONLINE    primary01
ora....y01.gsd application    ONLINE    ONLINE    primary01
ora....y01.ons application    ONLINE    ONLINE    primary01
ora....y01.vip application    ONLINE    ONLINE    primary01
ora....SM2.asm application    ONLINE    ONLINE    primary02
ora....02.lsnr application    ONLINE    ONLINE    primary02
ora....y02.gsd application    ONLINE    ONLINE    primary02
ora....y02.ons application    ONLINE    ONLINE    primary02
ora....y02.vip application    ONLINE    ONLINE    primary02

Reboot All Nodes

We reboot all nodes to verify the result.

[root@primary01 ~]# init 6 [root@primary02 ~]# init 6 [oracle@primary01 ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....B1.inst application    ONLINE    ONLINE    primary01
ora....B2.inst application    ONLINE    ONLINE    primary02
ora.PRIMDB.db  application    ONLINE    ONLINE    primary01
ora....SM1.asm application    ONLINE    ONLINE    primary01
ora....01.lsnr application    ONLINE    ONLINE    primary01
ora....y01.gsd application    ONLINE    ONLINE    primary01
ora....y01.ons application    ONLINE    ONLINE    primary01
ora....y01.vip application    ONLINE    ONLINE    primary01
ora....SM2.asm application    ONLINE    ONLINE    primary02
ora....02.lsnr application    ONLINE    ONLINE    primary02
ora....y02.gsd application    ONLINE    ONLINE    primary02
ora....y02.ons application    ONLINE    ONLINE    primary02
ora....y02.vip application    ONLINE    ONLINE    primary02

We check the data before crashing.

[oracle@primary01 ~]$ sqlplus / as sysdba

...
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
PRIMDB    READ WRITE


SQL> select first_name from hr.employees where last_name = 'Rowe';

FIRST_NAME
--------------------
Scott

It does really exist. The database is back.