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
