Restoring a container database after loss of all of its files.
The restore will be done using a multisection image copy
backup.
Oracle database 12c has a multisection image copy backup
feature - which allows multiple sections of a huge datafile to be backed up in
parallel but still producing a single output file per datafile.
In 11g, the multisection backup feature existed, but it created
a multipart backupset and not an image copy.Image copies are much faster to restore and recover than
backupsets – they are duplicates of the data files ( though they are larger).In this demonstration, we will use a multisection image copy
backup and a controlfile autobackup to restore a fully destroyed multitenant container
database.
Scenario:
The platform is Red Hat Linux 5 64 bit, running Oracle 12c release 1.
A CDB existed called CDB1, it had these tenant databases. It needs to be restored.
PDB$SEED – the seed database.
SALESPDB
SALESTEST
MARKETING
EXNONCDB
The CDB was operating in noarchivelog mode, and a backup had been taken in mount mode.
The CDB had been backed up using the multisection image copy backup feature, control file and spfile are also backed up due to the controlfile autobackup feature.
The spfile, password file, and the directory tree containing the CDB files and folders have all been deleted. There are no archived logs or online logs to apply and there is no media recovery to do.
This exercise will demonstrate recovery of the cdb after complete loss of all the datafiles, controlfiles and redo log files.
1. Create a temp init.ora file containing these settings
DB_NAME=CDB1
CONTROL_FILES='/u01/app/oracle/oradata/cdb1/control1.ctl','/u01/app/oracle/oradata/cdb1/control2.ctl';
ENABLE_PLUGGABLE_DATABASE=TRUE;
2. Create a password file using orapwd, if you also lost the password file.
3. Retrieve the original database's dbid
A CDB existed called CDB1, it had these tenant databases. It needs to be restored.
PDB$SEED – the seed database.
SALESPDB
SALESTEST
MARKETING
EXNONCDB
The CDB was operating in noarchivelog mode, and a backup had been taken in mount mode.
The CDB had been backed up using the multisection image copy backup feature, control file and spfile are also backed up due to the controlfile autobackup feature.
The spfile, password file, and the directory tree containing the CDB files and folders have all been deleted. There are no archived logs or online logs to apply and there is no media recovery to do.
This exercise will demonstrate recovery of the cdb after complete loss of all the datafiles, controlfiles and redo log files.
1. Create a temp init.ora file containing these settings
DB_NAME=CDB1
CONTROL_FILES='/u01/app/oracle/oradata/cdb1/control1.ctl','/u01/app/oracle/oradata/cdb1/control2.ctl';
ENABLE_PLUGGABLE_DATABASE=TRUE;
2. Create a password file using orapwd, if you also lost the password file.
3. Retrieve the original database's dbid
If not using an fra, its part of the controlfile autobackup file name --> 802949696
/u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
4. Start up rman
[oracle@laboms cdb1]$ export ORACLE_SID=CDB1
[oracle@laboms cdb1]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jan 8 08:16:32 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
5. Startup the instance in nomount mode using the pfile created in step 1.
RMAN> startup nomount pfile='init.ora';
Oracle instance started
Total System Global Area 229683200 bytes
Fixed Size 2286800 bytes
Variable Size 171969328 bytes
Database Buffers 50331648 bytes
Redo Buffers 5095424 bytes
6. Set the DBID, restore the spfile from the controlfile autobackup.
RMAN> SET DBID 802949696;
executing command: SET DBID
RMAN> set controlfile autobackup format for device type disk to '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
RMAN> restore spfile from autobackup;
Starting restore at 08-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140108
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140107
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140105
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-JAN-14
7. Start up the database using the restored spfile.
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 1570009088 bytes
Fixed Size 2288776 bytes
Variable Size 1342178168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7438336 bytes
8. Restore the controlfile from the backup;
RMAN> restore controlfile from autobackup;
Starting restore at 08-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140108
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140107
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140105
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/cdb1/control1.ctl
output file name=/u01/app/oracle/oradata/cdb1/control2.ctl
Finished restore at 08-JAN-14
9. Mount the database.
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
10. Examine the controlfile metadata, to make sure restore destination folders exist (optional).
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name CDB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /u01/app/oracle/oradata/cdb1/system.ora
2 0 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/cdb1/seed/system.ora
3 0 SYSAUX *** /u01/app/oracle/oradata/cdb1/sysaux.ora
4 0 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/cdb1/seed/sysaux.ora
5 0 UNDOTBS *** /u01/app/oracle/oradata/cdb1/undo.ora
6 0 USERS *** /u01/app/oracle/oradata/cdb1/users.ora
7 0 PDB$SEED:USERS *** /u01/app/oracle/oradata/cdb1/seed/users.ora
23 0 SALESPDB:SYSTEM *** /u01/app/oracle/oradata/cdb1/salespdb/system.ora
24 0 SALESPDB:SYSAUX *** /u01/app/oracle/oradata/cdb1/salespdb/sysaux.ora
25 0 SALESPDB:USERS *** /u01/app/oracle/oradata/cdb1/salespdb/users.ora
26 0 SALESPDB:SALES *** /u01/app/oracle/oradata/cdb1/salespdb/sales.ora
32 0 SALESTEST:SYSTEM *** /u01/app/oracle/oradata/cdb1/salestest/system.ora
33 0 SALESTEST:SYSAUX *** /u01/app/oracle/oradata/cdb1/salestest/sysaux.ora
34 0 SALESTEST:USERS *** /u01/app/oracle/oradata/cdb1/salestest/users.ora
35 0 SALESTEST:TS_SALESTEST *** /u01/app/oracle/oradata/cdb1/salestest/ts_salestest.ora
36 0 MARKETING:SYSTEM *** /u01/app/oracle/oradata/cdb1/marketing/system.ora
37 0 MARKETING:SYSAUX *** /u01/app/oracle/oradata/cdb1/marketing/sysaux.ora
38 0 MARKETING:USERS *** /u01/app/oracle/oradata/cdb1/marketing/users.ora
39 0 MARKETING:TS_SALESTEST *** /u01/app/oracle/oradata/cdb1/marketing/ts_marketing.ora
50 0 EXNONCDB:SYSTEM *** /u01/app/oracle/oradata/cdb1/exnoncdb/system.ora
51 0 EXNONCDB:SYSAUX *** /u01/app/oracle/oradata/cdb1/exnoncdb/sysaux.ora
52 0 EXNONCDB:USERS *** /u01/app/oracle/oradata/cdb1/exnoncdb/users.ora
53 0 EXNONCDB:ILMTBS *** /u01/app/oracle/oradata/cdb1/exnoncdb/ilmts.ora
54 0 EXNONCDB:LOW_COST_STORE *** /u01/app/oracle/oradata/cdb1/exnoncdb/lowcostts.ora
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/cdb1/temp.ora
2 20 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/cdb1/seed/temp.ora
3 20 SALESPDB:TEMP 32767 /u01/app/oracle/oradata/cdb1/salespdb/temp.ora
4 20 SALESTEST:TEMP 32767 /u01/app/oracle/oradata/cdb1/salestest/temp.ora
5 15 SALESPDB:TEMPSALES 15 /u01/app/oracle/oradata/cdb1/salespdb/tempsales.ora
6 20 MARKETING:TEMP 32767 /u01/app/oracle/oradata/cdb1/marketing/temp.ora
7 20 EXNONCDB:TEMP 32767 /u01/app/oracle/oradata/cdb1/exnoncdb/temp.ora
11. Create any missing destinations
In the case of the example these folders were created under /u01/app/oracle/oradata/cdb1
seed
salespdb
salestest
marketing
exnoncdb
audit
12. Restore the database (note below that the rman is using input datafilecopy - indicates that an image copy is being used for restore).
RMAN> restore database;
Starting restore at 08-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=2 STAMP=836068929 file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/data_D-CDB1_I-802949696_TS-SYSTEM_FNO-1_05otapi1
destination for restore of datafile 00001: /u01/app/oracle/oradata/cdb1/system.ora
...
...
...
channel ORA_DISK_1: restoring datafile 00054
input datafile copy RECID=22 STAMP=836069342 file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/data_D-CDB1_I-802949696_TS-LOW_COST_STORE_FNO-54_1cotaput
destination for restore of datafile 00054: /u01/app/oracle/oradata/cdb1/exnoncdb/lowcostts.ora
channel ORA_DISK_1: copied datafile copy of datafile 00054
output file name=/u01/app/oracle/oradata/cdb1/exnoncdb/lowcostts.ora RECID=0 STAMP=0
Finished restore at 08-JAN-14
13. Open the database with resetlogs
RMAN> alter database open resetlogs;
Statement processed
14. Exit rman and examine the db in SqlPlus
RMAN> exit
Recovery Manager complete.
[oracle@laboms cdb1]$ sqlplus sys/oracle@CDB1 as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 8 09:45:17 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> select pdb_name from cdb_pdbs;
PDB_NAME
--------------------------------------------------------------------------------
PDB$SEED
MARKETING
SALESTEST
SALESPDB
EXNONCDB
4. Start up rman
[oracle@laboms cdb1]$ export ORACLE_SID=CDB1
[oracle@laboms cdb1]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jan 8 08:16:32 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
5. Startup the instance in nomount mode using the pfile created in step 1.
RMAN> startup nomount pfile='init.ora';
Oracle instance started
Total System Global Area 229683200 bytes
Fixed Size 2286800 bytes
Variable Size 171969328 bytes
Database Buffers 50331648 bytes
Redo Buffers 5095424 bytes
6. Set the DBID, restore the spfile from the controlfile autobackup.
RMAN> SET DBID 802949696;
executing command: SET DBID
RMAN> set controlfile autobackup format for device type disk to '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
RMAN> restore spfile from autobackup;
Starting restore at 08-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140108
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140107
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140105
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-JAN-14
7. Start up the database using the restored spfile.
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 1570009088 bytes
Fixed Size 2288776 bytes
Variable Size 1342178168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7438336 bytes
8. Restore the controlfile from the backup;
RMAN> restore controlfile from autobackup;
Starting restore at 08-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140108
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140107
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140105
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/cdb1/control1.ctl
output file name=/u01/app/oracle/oradata/cdb1/control2.ctl
Finished restore at 08-JAN-14
9. Mount the database.
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
10. Examine the controlfile metadata, to make sure restore destination folders exist (optional).
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name CDB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /u01/app/oracle/oradata/cdb1/system.ora
2 0 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/cdb1/seed/system.ora
3 0 SYSAUX *** /u01/app/oracle/oradata/cdb1/sysaux.ora
4 0 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/cdb1/seed/sysaux.ora
5 0 UNDOTBS *** /u01/app/oracle/oradata/cdb1/undo.ora
6 0 USERS *** /u01/app/oracle/oradata/cdb1/users.ora
7 0 PDB$SEED:USERS *** /u01/app/oracle/oradata/cdb1/seed/users.ora
23 0 SALESPDB:SYSTEM *** /u01/app/oracle/oradata/cdb1/salespdb/system.ora
24 0 SALESPDB:SYSAUX *** /u01/app/oracle/oradata/cdb1/salespdb/sysaux.ora
25 0 SALESPDB:USERS *** /u01/app/oracle/oradata/cdb1/salespdb/users.ora
26 0 SALESPDB:SALES *** /u01/app/oracle/oradata/cdb1/salespdb/sales.ora
32 0 SALESTEST:SYSTEM *** /u01/app/oracle/oradata/cdb1/salestest/system.ora
33 0 SALESTEST:SYSAUX *** /u01/app/oracle/oradata/cdb1/salestest/sysaux.ora
34 0 SALESTEST:USERS *** /u01/app/oracle/oradata/cdb1/salestest/users.ora
35 0 SALESTEST:TS_SALESTEST *** /u01/app/oracle/oradata/cdb1/salestest/ts_salestest.ora
36 0 MARKETING:SYSTEM *** /u01/app/oracle/oradata/cdb1/marketing/system.ora
37 0 MARKETING:SYSAUX *** /u01/app/oracle/oradata/cdb1/marketing/sysaux.ora
38 0 MARKETING:USERS *** /u01/app/oracle/oradata/cdb1/marketing/users.ora
39 0 MARKETING:TS_SALESTEST *** /u01/app/oracle/oradata/cdb1/marketing/ts_marketing.ora
50 0 EXNONCDB:SYSTEM *** /u01/app/oracle/oradata/cdb1/exnoncdb/system.ora
51 0 EXNONCDB:SYSAUX *** /u01/app/oracle/oradata/cdb1/exnoncdb/sysaux.ora
52 0 EXNONCDB:USERS *** /u01/app/oracle/oradata/cdb1/exnoncdb/users.ora
53 0 EXNONCDB:ILMTBS *** /u01/app/oracle/oradata/cdb1/exnoncdb/ilmts.ora
54 0 EXNONCDB:LOW_COST_STORE *** /u01/app/oracle/oradata/cdb1/exnoncdb/lowcostts.ora
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/cdb1/temp.ora
2 20 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/cdb1/seed/temp.ora
3 20 SALESPDB:TEMP 32767 /u01/app/oracle/oradata/cdb1/salespdb/temp.ora
4 20 SALESTEST:TEMP 32767 /u01/app/oracle/oradata/cdb1/salestest/temp.ora
5 15 SALESPDB:TEMPSALES 15 /u01/app/oracle/oradata/cdb1/salespdb/tempsales.ora
6 20 MARKETING:TEMP 32767 /u01/app/oracle/oradata/cdb1/marketing/temp.ora
7 20 EXNONCDB:TEMP 32767 /u01/app/oracle/oradata/cdb1/exnoncdb/temp.ora
11. Create any missing destinations
In the case of the example these folders were created under /u01/app/oracle/oradata/cdb1
seed
salespdb
salestest
marketing
exnoncdb
audit
12. Restore the database (note below that the rman is using input datafilecopy - indicates that an image copy is being used for restore).
RMAN> restore database;
Starting restore at 08-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=2 STAMP=836068929 file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/data_D-CDB1_I-802949696_TS-SYSTEM_FNO-1_05otapi1
destination for restore of datafile 00001: /u01/app/oracle/oradata/cdb1/system.ora
...
...
...
channel ORA_DISK_1: restoring datafile 00054
input datafile copy RECID=22 STAMP=836069342 file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/data_D-CDB1_I-802949696_TS-LOW_COST_STORE_FNO-54_1cotaput
destination for restore of datafile 00054: /u01/app/oracle/oradata/cdb1/exnoncdb/lowcostts.ora
channel ORA_DISK_1: copied datafile copy of datafile 00054
output file name=/u01/app/oracle/oradata/cdb1/exnoncdb/lowcostts.ora RECID=0 STAMP=0
Finished restore at 08-JAN-14
13. Open the database with resetlogs
RMAN> alter database open resetlogs;
Statement processed
14. Exit rman and examine the db in SqlPlus
RMAN> exit
Recovery Manager complete.
[oracle@laboms cdb1]$ sqlplus sys/oracle@CDB1 as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 8 09:45:17 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> select pdb_name from cdb_pdbs;
PDB_NAME
--------------------------------------------------------------------------------
PDB$SEED
MARKETING
SALESTEST
SALESPDB
EXNONCDB
No comments:
Post a Comment