For 10g RAC database restoration, you may check: How to Restore 10g RAC Database from 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
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
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 restart during maintenance time.
[oracle@primary01 ~]$ srvctl disable database -d compdb
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
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
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
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
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
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'
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
Restore SPFILE to ASM Diskgroup
We have to know the location of SPFILE before we create it.
[oracle@primary01 ~]$ cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
SPFILE='+DATA/compdb/spfilecompdb.ora'
This is a pointer of SPFILE, the technique is adopted by RAC.
Now we can create SPFILE from the modified PFILE.
RMAN> create spfile='+DATA/COMPDB/spfileprimdb.ora' from pfile='/tmp/initPRIMDB.ora';
Statement processed
We have 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.
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.
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.
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
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
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.
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 = 'Rowe';
FIRST_NAME
--------------------
Scott
Then we shutdown the instance.
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
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
Reattach Database to Clusterware
We reattach it to the clusterware to be a cluster-managed database.
[oracle@primary01 ~]$ srvctl enable database -d compdb
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
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.
I tried this in Oracle19c and couldn’t restore until i changed cluster_database parameter to false because there was error RMAN-12009 and RMAN-12017. With cluster_database=false i could restore then recover without any other problem.
Thanks for sharing. I’ll try it some time.