12c: Recover lost system tablespace of one PDB

While evaluating the 12c beta3 I was not able to do the recover while testing “all pdb files lost”.

Cannot close the pdb as the system datafile was missing…
So only option to recover was:

Shutdown cdb
startup mount;
restore pluggable database
recover pluggable databsae
alter database open;
allter pluggagle database name open;

Oracle support says: You should be able to close the pdb and restore/recover the system tablespace of PDB.

Full steps:

I have created two PDBs in my CDB sales and allraces

After that, I have backed up using rman and run the following:

backup incremental level 0 database;
backup incremental level 0 pluggable database sales;
backup incremental level 0 pluggable database allraces;

Then I have removed all the files related to PDB sales:
sales_o1_mf_sysaux_8omrmswj_.dbf
sales_o1_mf_system_8omrmswo_.dbf
sales_users01.dbf
temp01.dbf

Then I try to connect back to PDB and i got an error
oracle@srv12c:~/ [CDBPRD] sqlplus admin@sales

SQL*Plus: Release 12.1.0.1.0 Production on Thu Apr 4 16:02:34 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 23
ORA-01110: data file 23:
‘/home/app/oradata/CDBPRD/datafile/sales/sales_o1_mf_system_8omrmswo_.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 23
ORA-01110: data file 23:
‘/home/app/oradata/CDBPRD/datafile/sales/sales_o1_mf_system_8omrmswo_.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 23
ORA-01110: data file 23:
‘/home/app/oradata/CDBPRD/datafile/sales/sales_o1_mf_system_8omrmswo_.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Then I try to close the PDB to do my full recover of sales PDB and I am getting this:
SQL> alter pluggable database sales close immediate;
alter pluggable database sales close immediate
*
ERROR at line 1:
ORA-01116: error in opening database file 25
ORA-01110: data file 25:
‘/home/app/oradata/CDBPRD/datafile/sales/sales_users01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

No way to close the PDB.

Cannot restore as cannot get exclusive access to system tablespace…

RMAN> restore pluggable database sales;

Starting restore at 04-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 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 00023 to /home/app/oradata/CDBPRD/datafile/sales/sales_o1_mf_system_8omrmswo_.dbf
channel ORA_DISK_1: restoring datafile 00024 to /home/app/oradata/CDBPRD/datafile/sales/sales_o1_mf_sysaux_8omrmswj_.dbf
channel ORA_DISK_1: restoring datafile 00025 to /home/app/oradata/CDBPRD/datafile/sales/sales_users01.dbf
channel ORA_DISK_1: reading from backup piece /mnt/hgfs/Shared/FRA/CDBPRD/backupset/2013_04_04/o1_mf_nnnd0_TAG20130404T155907_8ov1scyz_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/04/2013 16:07:55
ORA-19870: error while restoring backup piece /mnt/hgfs/Shared/FRA/CDBPRD/backupset/2013_04_04/o1_mf_nnnd0_TAG20130404T155907_8ov1scyz_.bkp
ORA-19573: cannot obtain exclusive enqueue for datafile 24

So I decided to shutdown the CDB but got an error

RMAN> shutdown immediate;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 04/04/2013 16:09:28
ORA-01116: error in opening database file 23
ORA-01110: data file 23: ‘/home/app/oradata/CDBPRD/datafile/sales/sales_o1_mf_system_8omrmswo_.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

At that point, sometime the database crash and sometimes I was able to do shutdown abort.

THe I mount the CDB and I was able to do my recover.

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 1286066176 bytes

Fixed Size 2287336 bytes
Variable Size 838863128 bytes
Database Buffers 436207616 bytes
Redo Buffers 8708096 bytes

RMAN> restore pluggable database sales;

Starting restore at 04-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 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 00023 to /home/app/oradata/CDBPRD/datafile/sales/sales_o1_mf_system_8omrmswo_.dbf
channel ORA_DISK_1: restoring datafile 00024 to /home/app/oradata/CDBPRD/datafile/sales/sales_o1_mf_sysaux_8omrmswj_.dbf
channel ORA_DISK_1: restoring datafile 00025 to /home/app/oradata/CDBPRD/datafile/sales/sales_users01.dbf
channel ORA_DISK_1: reading from backup piece /mnt/hgfs/Shared/FRA/CDBPRD/backupset/2013_04_04/o1_mf_nnnd0_TAG20130404T155907_8ov1scyz_.bkp
channel ORA_DISK_1: piece handle=/mnt/hgfs/Shared/FRA/CDBPRD/backupset/2013_04_04/o1_mf_nnnd0_TAG20130404T155907_8ov1scyz_.bkp tag=TAG20130404T155907
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 04-APR-13

RMAN> recover database;

Starting recover at 04-APR-13
using channel ORA_DISK_1

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

Finished recover at 04-APR-13

RMAN> alter database open;

Statement processed

RMAN> exit

Recovery Manager complete.
oracle@srv12c:~/ [CDBPRD] sq

SQL*Plus: Release 12.1.0.1.0 Production on Thu Apr 4 16:11:40 2013

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> alter pluggable database sales open;

Pluggable database altered.

Conclusion, we cannot recover a PDB with open CDB if system or sysaux tablespaces are lost.

We cannot close a PDB if ONE datafile is missing…

 

Answer from Oracle support:CDB must be restarted if PDB system datafile is lost. For non-SYSTEM datafiles, you can offline, restore, recover, and online them..all while CDB is online. We plan to address the lost system datafile scenario in a future release.

Seems to be the same with the final release:-)

The problem is solved since the 12.1.0.2 version!

Thanks:-)

jko

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

2 thoughts on “12c: Recover lost system tablespace of one PDB

  1. Chandresh

    Hi JKO,

    excellent, just preparing for my exam and found this extremely helpful.

    helps gain valuable insight.

    Regards,
    Chandresh.

    Reply
  2. SUCHIKA

    Hi Jacques,

    Thanks a lot for sharing this valuable post. Really helped me prepare document for my team on various Recovery and Restore scenarios for PDBs.

    Regards
    Suchika

    Reply

Leave a Reply

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