Oracle DBA Tips and Techniques

By Arjun Raja

RESTORE AND RECOVER SYSTEM TABLESPACE IN PLUGGABLE DATABASE

Posted by Arjun Raja on June 11, 2014

SYSTEM TABLESPACE RESTORE AND RECOVER IN PLUGGABLE DATABASE

From CDB root backup all databases – CDB and PDB’s.

cdbd1:/u01/oracle> rcat

Recovery Manager: Release 12.1.0.1.0 – Production on Mon May 12 08:45:15 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

connected to target database: CDBD1 (DBID=2264962255)
connected to recovery catalog database

RMAN> backup database plus archivelog;

Next backup only pluggable database.

RMAN> backup pluggable database pdb1d1;

Starting backup at 12-05-2014 08:55:51
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:55
Finished backup at 12-05-2014 08:56:47

Starting Control File and SPFILE Autobackup at 12-05-2014 08:56:47
piece handle=/u03/oradata/fast_recovery_area/CDBD1/autobackup/2014_05_12/o1_mf_s_847357009_9q072kv9_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-05-2014 08:56:50

RMAN>

Now remove system datafile from PDB.

cd /u02/oradata/cdbd1/pdb1d1

cdbd1:/u02/oradata/cdbd1/pdb1d1> rm system01.dbf

cdbd1:/u02/oradata/cdbd1/pdb1d1> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 12 09:21:02 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 and Real Application Testing options

SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 3
ORA-01116: error in opening database file 10
ORA-01110: data file 10: ‘/u02/oradata/cdbd1/pdb1d1/system01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Additional information: 4
Additional information: 4194304

Now try to restore and recover SYSTEM tablespace of PDB with CDB still up and running….

RMAN> run {
2> RESTORE TABLESPACE PDB1D1:SYSTEM;
3> }

Starting restore at 12-05-2014 09:22:25
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=138 device type=DISK

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 00010 to /u02/oradata/cdbd1/pdb1d1/system01.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rmy_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/12/2014 09:22:31
ORA-19870: error while restoring backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rmy_.bkp
ORA-19573: cannot obtain exclusive enqueue for datafile 10

RMAN>

So you can’t restore the system tablespace datafile of a PDB if the CDB is still up and running.

In CDB.

Shutdown abort .

If you try shutdown immediate – the following error will appear :

SQL> shut immediate;
startuORA-01116: error in opening database file 10
ORA-01110: data file 10: ‘/u02/oradata/cdbd1/pdb1d1/system01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Additional information: 4
Additional information: 4194304

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size 2360696 bytes
Variable Size 973081224 bytes
Database Buffers 620756992 bytes
Redo Buffers 7213056 bytes
Database mounted.
SQL>

Now run RMAN recovery of SYSTEM tablespace in Pluggable database.

run
{ RESTORE TABLESPACE PDB1D1:SYSTEM ;
}

Starting restore at 12-05-2014 09:10:15
using channel ORA_DISK_1
using channel ORA_DISK_2

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 00010 to /u02/oradata/cdbd1/pdb1d1/system01.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rmy_.bkp
channel ORA_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rmy_.bkp tag=TAG20140512T085552
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 12-05-2014 09:10:41

RUN {
RECOVER TABLESPACE PDB1D1:SYSTEM;
}

Starting recover at 12-05-2014 09:11:13
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 12-05-2014 09:11:14

RMAN>

Now to continue first open the CDB and then open the PDB.

If you try to open the PDB with the CDB in mount state –

RMAN> ALTER PLUGGABLE DATABASE PDB1D1 OPEN READ WRITE;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 05/12/2014 09:25:23
ORA-01109: database not open

RMAN> ALTER DATABASE OPEN;

Statement processed
starting full resync of recovery catalog
full resync complete

RMAN>

Finally open PDB

RMAN> ALTER PLUGGABLE DATABASE PDB1D1 OPEN READ WRITE;

Statement processed
starting full resync of recovery catalog
full resync complete

RMAN>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: