RACRMAN

How to Restore 12c RAC Database by a Backup Set

For 10g RAC database restoration, you may check: How to Restore 10g RAC Database by a Backup Set.

Suppose we have restored 12c clusterware back. Now we can start to restore 12c cluster database. Our goal in this post is to use a backup set on one of RAC nodes to restore the database.

First of all, make sure the clusterware is online and healthy.

[grid@primary01 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[grid@primary01 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       primary01                STABLE
               ONLINE  ONLINE       primary02                STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       primary01                STABLE
               ONLINE  ONLINE       primary02                STABLE
ora.LISTENER2.lsnr
               ONLINE  ONLINE       primary01                STABLE
               ONLINE  ONLINE       primary02                STABLE
ora.asm
               ONLINE  ONLINE       primary01                Started,STABLE
               ONLINE  ONLINE       primary02                Started,STABLE
ora.net1.network
               ONLINE  ONLINE       primary01                STABLE
               ONLINE  ONLINE       primary02                STABLE
ora.net2.network
               ONLINE  ONLINE       primary01                STABLE
               ONLINE  ONLINE       primary02                STABLE
ora.ons
               ONLINE  ONLINE       primary01                STABLE
               ONLINE  ONLINE       primary02                STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER2_SCAN1_NET2.lsnr
      1        ONLINE  ONLINE       primary02                STABLE
ora.LISTENER2_SCAN2_NET2.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.LISTENER2_SCAN3_NET2.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.LISTENER2_SCAN4_NET2.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.LISTENER_SCAN4.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       primary01                169.254.135.24 192.1
                                                             68.24.11,STABLE
ora.compdb.db
      1        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.cvu
      1        ONLINE  ONLINE       primary01                STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       primary01                Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       primary01                STABLE
ora.primary01.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.primary01_2.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.primary02.vip
      1        ONLINE  ONLINE       primary02                STABLE
ora.primary02_2.vip
      1        ONLINE  ONLINE       primary02                STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.scan1_net2.vip
      1        ONLINE  ONLINE       primary02                STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.scan2_net2.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.scan3_net2.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.scan4.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.scan4_net2.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.smalldb.db
      1        ONLINE  ONLINE       primary01                Open,STABLE
--------------------------------------------------------------------------------

Restore Cluster Database

1. Make a Directory in Disk Group

The directory name should be the database name depending on your design. In this case, DB_NAME is COMPDB, DB_UNIQUE_NAME is PRIMDB.

[grid@primary01 ~]$ asmcmd mkdir +DATA/COMPDB
[grid@primary01 ~]$ asmcmd ls -l +DATA
Type      Redund  Striped  Time             Sys  Name
                                            Y    ASM/
                                            N    COMPDB/
                                            Y    _MGMTDB/
PASSWORD  UNPROT  COARSE   JUN 26 15:00:00  N    orapwasm => +DATA/ASM/PASSWORD/pwdasm.256.1011972205
                                            Y    primary-cluster/

Make sure that we have a backup set of the database.

[oracle@primary01 ~]$ ll /backup
total 1412984
-rw-r----- 1 oracle asmadmin 1427841024 Jun 26 10:01 COMPDB_968055037_07u52b5h_1_1
-rw-r----- 1 oracle asmadmin   19038208 Jun 26 10:01 COMPDB_968055037_08u52b7j_1_1
-rw-r----- 1 oracle asmadmin      11264 Jun 26 10:01 COMPDB_968055037_09u52b7r_1_1

2. Detach Database from Clusterware

Since we only use one node to do the entire recovery, so we have to detach the database from the clusterware in case we trigger automatic actions during maintenance time.

[oracle@primary01 ~]$ srvctl disable database -d compdb

3. Startup a Dummy Instance

We need a dummy instance to restore SPFILE from a backup piece.

[oracle@primary01 ~]$ rman target /
...
RMAN> startup nomount force;

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

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

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes
Variable Size                281018472 bytes
Database Buffers             784334848 bytes
Redo Buffers                   5455872 bytes

4. Restore SPFILE to a Local Directory

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/COMPDB_968055037_08u52b7j_1_1';

Starting restore at 27-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=177 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/COMPDB_968055037_08u52b7j_1_1
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 27-JUN-19

Then shutdown the instance.

RMAN> shutdown immediate;

Oracle instance shut down

4. Create a PFILE for Later Startups

We created PFILE from this SPFILE for later operations.

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

Statement processed

5. Startup Database to Nomount by PFILE

Then startup the database to nomount state by the PFILE.

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

connected to target database (not started)
Oracle instance started

Total System Global Area    1526726656 bytes

Fixed Size                     2924688 bytes
Variable Size               1056968560 bytes
Database Buffers             452984832 bytes
Redo Buffers                  13848576 bytes

6. Restore Controlfile

RMAN> restore controlfile from '/backup/COMPDB_968055037_08u52b7j_1_1';

Starting restore at 27-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 instance=primdb1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/COMPDB/CONTROLFILE/current.276.1012058301
Finished restore at 27-JUN-19

Then we shutdown the instance.

RMAN> shutdown immediate;

Oracle instance shut down

7. Modify PFILE

Since restored controlfile name was different from the original one under OMF’s instruction, we had to modify PFILE to align with the current controlfile name.

[oracle@primary01 ~]$ vi /tmp/initPRIMDB.ora
...
*.control_files='+DATA/COMPDB/CONTROLFILE/current.276.1012058301'

8. Startup Database to Mount by PFILE

For restoring SPFILE correctly, we have to mount the controlfile to let ASM know the database name of current instance.

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

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

Total System Global Area    1526726656 bytes

Fixed Size                     2924688 bytes
Variable Size               1056968560 bytes
Database Buffers             452984832 bytes
Redo Buffers                  13848576 bytes

9. Restore SPFILE to ASM Diskgroup

We created SPFILE from our modified PFILE. Of course, you can also restore SPFILE from the above backup piece, but subsequently, you have to change the location of control files by ALTER SYSTEM SET CONTROL_FILES under NOMOUNT.

RMAN> create spfile='+DATA/COMPDB/spfileprimdb.ora' from pfile='/tmp/initPRIMDB.ora';

Statement processed

Why should we do so many steps above before restoring SPFILE? Why don’t we just restore SPFILE in the first place when the dummy instance is up? This is because we don’t want SPFILE to be restore to +DATA/DB_UNKNOWN directory, even though it will be working find after instance restarted.

Check the physical location of SPFILE.

[grid@primary01 ~]$ asmcmd ls -l +DATA/COMPDB
Type           Redund  Striped  Time             Sys  Name
                                                 Y    CONTROLFILE/
                                                 Y    PARAMETERFILE/
PARAMETERFILE  UNPROT  COARSE   JUN 27 15:00:00  N    spfileprimdb.ora => +DATA/COMPDB/PARAMETERFILE/spfile.277.1012058435

Good, it’s in +DATA/COMPDB/PARAMETERFILE, not in+DATA/DB_UNKNOWN/PARAMETERFILE.

9. 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    1526726656 bytes

Fixed Size                     2924688 bytes
Variable Size               1073745776 bytes
Database Buffers             436207616 bytes
Redo Buffers                  13848576 bytes

Check SPFILE of current instance.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/COMPDB/spfileprimdb.ora

OK, they can work together.

11. 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/COMPDB_968055037_08u52b7j_1_1
File Name: /backup/COMPDB_968055037_09u52b7r_1_1
File Name: /backup/COMPDB_968055037_07u52b5h_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/COMPDB_968055037_08u52b7j_1_1
File Name: /backup/COMPDB_968055037_09u52b7r_1_1
File Name: /backup/COMPDB_968055037_07u52b5h_1_1

12. Restore Data Files

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

RMAN> restore database;

Starting restore at 27-JUN-19
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/COMPDB/DATAFILE/system.278.887730489
channel ORA_DISK_1: restoring datafile 00002 to +DATA/COMPDB/DATAFILE/undotbs2.286.887733345
channel ORA_DISK_1: restoring datafile 00003 to +DATA/COMPDB/DATAFILE/sysaux.277.887730079
channel ORA_DISK_1: restoring datafile 00004 to +DATA/COMPDB/DATAFILE/undotbs1.280.887730969
channel ORA_DISK_1: restoring datafile 00005 to +DATA/COMPDB/DATAFILE/example.285.887731271
channel ORA_DISK_1: restoring datafile 00006 to +DATA/COMPDB/DATAFILE/users.279.887730965
channel ORA_DISK_1: reading from backup piece /backup/COMPDB_968055037_07u52b5h_1_1
channel ORA_DISK_1: piece handle=/backup/COMPDB_968055037_07u52b5h_1_1 tag=TAG20190626T100017
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:25
Finished restore at 27-JUN-19

13. Recover Database

RMAN> recover database;

Starting recover at 27-JUN-19
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=39
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=31
channel ORA_DISK_1: reading from backup piece /backup/COMPDB_968055037_09u52b7r_1_1
channel ORA_DISK_1: piece handle=/backup/COMPDB_968055037_09u52b7r_1_1 tag=TAG20190626T100131
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+DATA/COMPDB/ARCHIVELOG/2019_06_27/thread_1_seq_39.284.1012058789 thread=1 sequence=39
archived log file name=+DATA/COMPDB/ARCHIVELOG/2019_06_27/thread_2_seq_31.285.1012058789 thread=2 sequence=31
channel default: deleting archived log(s)
archived log file name=+DATA/COMPDB/ARCHIVELOG/2019_06_27/thread_2_seq_31.285.1012058789 RECID=13 STAMP=1012058788
unable to find archived log
archived log thread=2 sequence=32
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/27/2019 15:26:30
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 32 and starting SCN of 5655908

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

14. Open Database

Since this is a point-in-time recovery and we don’t have any redo logs, so 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;

Statement processed

RMAN> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
COMPDB    READ WRITE

RMAN> select first_name from hr.employees where last_name = 'Chen';

FIRST_NAME
--------------------
Ed

Then we shutdown the instance.

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

15. Create a New Password File

We created a new password file for the cluster database.

[oracle@primary01 ~]$ orapwd file='+DATA' dbuniquename='compdb' password=welcome1

The configuration of the cluster database reflected the new password file.

[oracle@primary01 ~]$ srvctl config database -d compdb
Database unique name: compdb
Database name: compdb
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/COMPDB/spfileprimdb.ora
Password file: +DATA/COMPDB/PASSWORD/pwdcompdb.295.1012059079
Domain: example.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: primdb1,primdb2
Configured nodes: primary01,primary02
Database is administrator managed

Check the physical location of the new password file.

[grid@primary01 ~]$ asmcmd ls -l +DATA/COMPDB/PASSWORD
Type      Redund  Striped  Time             Sys  Name
PASSWORD  UNPROT  COARSE   JUN 27 15:00:00  Y    pwdcompdb.295.1012059079

16. Reattach Database to Clusterware

We reattach it to the clusterware to be a cluster-managed database.

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

17. 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 compdb
[oracle@primary01 ~]$ srvctl status database -d compdb
Instance primdb1 is running on node primary01
Instance primdb2 is running on node primary02

18. Check Resource Status

One last thing, we should check everything is fine by grid.

[grid@primary01 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       primary01                STABLE
               ONLINE  ONLINE       primary02                STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       primary01                STABLE
               ONLINE  ONLINE       primary02                STABLE
ora.LISTENER2.lsnr
               ONLINE  ONLINE       primary01                STABLE
               ONLINE  ONLINE       primary02                STABLE
ora.asm
               ONLINE  ONLINE       primary01                Started,STABLE
               ONLINE  ONLINE       primary02                Started,STABLE
ora.net1.network
               ONLINE  ONLINE       primary01                STABLE
               ONLINE  ONLINE       primary02                STABLE
ora.net2.network
               ONLINE  ONLINE       primary01                STABLE
               ONLINE  ONLINE       primary02                STABLE
ora.ons
               ONLINE  ONLINE       primary01                STABLE
               ONLINE  ONLINE       primary02                STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER2_SCAN1_NET2.lsnr
      1        ONLINE  ONLINE       primary02                STABLE
ora.LISTENER2_SCAN2_NET2.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.LISTENER2_SCAN3_NET2.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.LISTENER2_SCAN4_NET2.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.LISTENER_SCAN4.lsnr
      1        ONLINE  ONLINE       primary01                STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       primary01                169.254.135.24 192.1
                                                             68.24.11,STABLE
ora.compdb.db
      1        ONLINE  ONLINE       primary01                Open,STABLE
      2        ONLINE  ONLINE       primary02                Open,STABLE
ora.cvu
      1        ONLINE  ONLINE       primary01                STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       primary01                Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       primary01                STABLE
ora.primary01.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.primary01_2.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.primary02.vip
      1        ONLINE  ONLINE       primary02                STABLE
ora.primary02_2.vip
      1        ONLINE  ONLINE       primary02                STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.scan1_net2.vip
      1        ONLINE  ONLINE       primary02                STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.scan2_net2.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.scan3_net2.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.scan4.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.scan4_net2.vip
      1        ONLINE  ONLINE       primary01                STABLE
ora.smalldb.db
      1        ONLINE  ONLINE       primary01                Open,STABLE
--------------------------------------------------------------------------------

The database is back.

Leave a Reply

Your email address will not be published. Required fields are marked *