Friday, November 25, 2016

restore table space in PDB


As said earlier, I am currently doing a POC on multitenant 12.1.0.2 for my customer.
Unfortunately 12.1 and not 12.2 which seems to have really nice features especially with regards to FLASHBACK, limiting resources in the containers ....


One of the tasks I am investigating is backup and recovery. here some findings so far.

Situation :

CDB called CDB

container called ORA1


I have used OMF this time on a regular fs just to be able to easily remove datafiles while the db is running ....




SQL>!rm /oradata/CDB/2223196C0BE77A23E053144EA8C066EC/datafile/o1_mf_users_d3k2nnz6_.dbf'



SQL> alter session set container = ora1

  2  /



Session altered.



SQL>  select * from phil.test;



no rows selected



SQL>

SQL> create table phil.test2 as select * from dba_objects;

create table phil.test2 as select * from dba_objects

                                                   *

ERROR at line 1:

ORA-01116: error in opening database file 10

ORA-01110: data file 10:

'/oradata/CDB/2223196C0BE77A23E053144EA8C066EC/datafile/o1_mf_users_d3k2nnz6_.db

f'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3






ok the file is gone ! yippee

next step connect to RMAN

ok connected in the CDB :




$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Nov 25 15:18:00 2016

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

connected to target database: CDB (DBID=2009742972)

RMAN> restore tablespace ora1:users;

Starting restore at 25-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 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 /oradata/CDB/2223196C0BE77A23E053144EA8C066EC/datafile/o1_mf_users_d3k2nnz6_.dbf
channel ORA_DISK_1: reading from backup piece /oraarch/bck/CDB_0grlqcro_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/25/2016 15:18:11
ORA-19870: error while restoring backup piece /oraarch/bck/CDB_0grlqcro_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 10

RMAN>

RMAN>

RMAN> alter database datafile 10 offline;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 11/25/2016 15:19:35
ORA-01516: nonexistent log file, data file, or temporary file "10"



RMAN> exit



ok the CDB is not aware of datafile 10 ....

connect to SQLPLUS



 sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 25 15:21:54 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container = ora1;

Session altered.

SQL>  alter database datafile 10 offline;

Database altered.
ok back to RMAN



rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Nov 25 15:22:44 2016

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

connected to target database: CDB (DBID=2009742972)

RMAN> restore tablespace ora1:users;

Starting restore at 25-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=267 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 /oradata/CDB/2223196C0BE77A23E053144EA8C066EC/datafile/o1_mf_users_d3k2nnz6_.dbf
channel ORA_DISK_1: reading from backup piece /oraarch/bck/CDB_0grlqcro_1_1
channel ORA_DISK_1: piece handle=/oraarch/bck/CDB_0grlqcro_1_1 tag=TAG20161125T151456
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-NOV-16

RMAN> recover tablespace ora1:users;

Starting recover at 25-NOV-16
using channel ORA_DISK_1

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

Finished recover at 25-NOV-16

RMAN> exit


ok table space restored and recovered .


where are the datafiles now ?




SQL> alter session set container=ora1;

Session altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/CDB/datafile/o1_mf_undotbs1_c1yz8bk7_.dbf
/oradata/CDB/2223196C0BE77A23E053144EA8C066EC/datafile/o1_mf_system_c1yzs31m_.db
f

/oradata/CDB/2223196C0BE77A23E053144EA8C066EC/datafile/o1_mf_sysaux_c1yzs31t_.db
f

/oradata/CDB/datafile/o1_mf_users_d3k7f3tk_.dbf
ok so the users tablespace was restored in the CDB root directory ....
instead of here :




/oradata/CDB/2223196C0BE77A23E053144EA8C066EC/datafile/o1_mf_users_d3k2nnz6_.dbf


hmm lets check if we see the same when connecting to PDB instead of CDB when recovering :

so same scenario remove the datafile ...


[oracle@12cR1 datafile]$ rman target sys@ora1

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Nov 25 15:42:42 2016

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

target database Password:
connected to target database: CDB (DBID=2009742972)

RMAN> restore tablespace users;

Starting restore at 25-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 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 /oradata/CDB/datafile/o1_mf_users_d3k7f3tk_.dbf
channel ORA_DISK_1: reading from backup piece /oraarch/bck/CDB_0grlqcro_1_1
channel ORA_DISK_1: piece handle=/oraarch/bck/CDB_0grlqcro_1_1 tag=TAG20161125T151456
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-NOV-16

RMAN> recover tablespace users;

Starting recover at 25-NOV-16
using channel ORA_DISK_1

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

Finished recover at 25-NOV-16

RMAN> alter tablespace users online;

Statement processed

okay restored where they were originally stored
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/CDB/datafile/o1_mf_undotbs1_c1yz8bk7_.dbf
/oradata/CDB/2223196C0BE77A23E053144EA8C066EC/datafile/o1_mf_system_c1yzs31m_.dbf


/oradata/CDB/2223196C0BE77A23E053144EA8C066EC/datafile/o1_mf_sysaux_c1yzs31t_.dbf

/oradata/CDB/2223196C0BE77A23E053144EA8C066EC/datafile/o1_mf_users_d3k8lbts_.dbf

for me while making sense I see lots of potential issues with this ;) so I hope it was worthwhile to note this down