"12C Exam Solution" OCP 1z0-060 QUESTION 8:recovery of a tablespace in the CDB

QUESTION 8

Your multitenant Container (CDB) containing three pluggable databases (PDBs) is running in ARCHIVELOG mode. You find the Sysaux tablespace are corrupted in the root container. The steps to recover the tablespace is as follows:
1. Mount the CDB.
2. Close all the PDBs.
3. Open the database.
4. Apply the archive redo logs.
5. Restore the data file.
6. Take the Sysaux tablespace offline.
7. Place the Sysaux tablespace online.
8. Open all the PDBs with Resetlogs.
9. Open the database with Resetlogs.
Execute the command SHUTDOWN ABORT.
which option identifies the correct sequence to recover the Sysaux tablespace?

A. 6,5,4,7
B. 10,1,2,5,8
C. 10,1,2,5,4,9,8
D. 10,1,5,8,10

"The topic is indicative"
The Sysaux table space in CDB is fully restored.

"parsing"
Some non-critical data files are corrupted when the database is already open. You only need to restore and recover the table space where the corrupted file or corrupted file resides, in a state where the other data files of the database are guaranteed to be available. You do not need to close the database and then restore it. Before restore, the tablespace is required to be in the offline state.

"Experiment"
1. Modify the database to archive mode

[[email protected] ~]$ sqlplus/as sysdbasql*plus:release 12.1.0.1.0 Production on Mon 14:58:41 2014Copyrigh  T (c) 1982, Oracle. All rights reserved. Connected to an idle instance. [email protected]> Startuporacle instance started. Total System Global area 2121183232 bytesfixed size 2290360 bytesvariable size 1308626248 bytesdatabase Bu Ffers 805306368 Bytesredo buffers 4960256 bytesdatabase mounted. Database opened. [email protected]> Shu Immediatedatabase closed. Database dismounted. ORACLE instance shut down. [email protected]> Startup Mountoracle instance started. Total System Global area 2121183232 bytesfixed size 2290360 bytesvariable size 1308626248 bytesdata Base buffers 805306368 Bytesredo buffers 4960256 bytesdatabase mounted. [email protected]> ALTER DATABASE Archivelog;database altered. [email protected]> ALTER DATABASE Open;database altered. [EMAIL&NBsp;protected]> alter system switch logfile; System altered.

2. Backing up the database using Rman

[email protected]>!rmanrecovery manager:release 12.1.0.1.0-production on Mon 15:00:13 2014Copyright (c)  1982, Oracle and/or its affiliates. All rights reserved. Rman> Connect target/connected to target Database:dbstyle (dbid=2767578829) rman> backup database; Starting backup at 11-aug-14using target database control file instead of recovery catalogallocated Channel:ora_disk_1cha Nnel ora_disk_1:sid=50 device Type=diskchannel ora_disk_1:starting full datafile backup Setchannel ora_disk_1:specifyin G DataFile (s) in Backup setinput datafile file number=00005 name=/u01/app/oracle/oradata/dbstyle/undotbs01.dbfinput datafile file number=00001 name=/u01/app/oracle/oradata/dbstyle/system01.dbfinput datafile file number=00003 name=/ U01/app/oracle/oradata/dbstyle/sysaux01.dbfinput datafile file number=00006 name=/u01/app/oracle/oradata/dbstyle/ Users01.dbfchannel ora_disk_1:starting piece 1 at 11-aug-14channel ora_disk_1:finished piece 1 at 11-aug-14piece handle=/u01/app/oracle/fast_recovery_area/dbstyle/backupset/2014_08_11/o1_mf_nnndf_tag20140811t150021_9yjtj5z5_.bkp tag=tag20140811t150021 Comment=nonechannel ora_disk_1:backup set complete, elapsed Time:00:00:26channel ORA_DISK_1: Starting full datafile backup Setchannel ora_disk_1:specifying datafile (s) in Backup setinput datafile file number=00008 Name=/u01/app/oracle/oradata/dbstyle/dbs/sysaux01.dbfinput datafile file number=00007 name=/u01/app/oracle/oradata /dbstyle/dbs/system01.dbfinput datafile file number=00009 name=/u01/app/oracle/oradata/dbstyle/dbs/dbs_ Users01.dbfchannel ora_disk_1:starting piece 1 at 11-aug-14channel ora_disk_1:finished piece 1 at 11-aug-14piece handle= /u01/app/oracle/fast_recovery_area/dbstyle/fdd32a078f321802e0430a50a8c0f4ff/backupset/2014_08_11/o1_mf_nnndf_ TAG20140811T150021_9YJTJZ65_.BKP tag=tag20140811t150021 Comment=nonechannel ora_disk_1:backup set complete, elapsed Time:00:00:07channel ora_disk_1:starting full datafile backup Setchannel ora_disk_1: Specifying DataFile (s) in Backup setinput datafile file number=00004 name=/u01/app/oracle/oradata/dbstyle/pdbseed/ Sysaux01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/dbstyle/pdbseed/system01.dbfchannel ORA_ Disk_1:starting piece 1 at 11-aug-14channel ora_disk_1:finished piece 1 at 11-aug-14piece handle=/u01/app/oracle/fast_re Covery_area/dbstyle/fdd22bf463bc0f53e0430a50a8c0edd2/backupset/2014_08_11/o1_mf_nnndf_tag20140811t150021_ 9YJTK68H_.BKP tag=tag20140811t150021 Comment=nonechannel ora_disk_1:backup set complete, elapsed time: 00:00:07finished backup at 11-aug-14starting Control File and SPFILE autobackup at 11-aug-14piece handle=/u01/app/oracle/ FAST_RECOVERY_AREA/DBSTYLE/AUTOBACKUP/2014_08_11/O1_MF_S_855327661_9YJTKFF8_.BKP comment=NONEFinished Control File and SPFILE autobackup at 11-aug-14rman> list backup; List of Backup sets===================bs Key type LV Size Device Type Elapsed Time completion time-------------- --------- ----------- ---------------------------1 full 2.09G DISK 00:00:16 11-aug-14 BP key:1 Status:ava ilable compressed:no tag:tag20140811t150021 Piece Name:/u01/app/oracle/fast_recovery_area/dbstyle/backupset/20   14_08_11/O1_MF_NNNDF_TAG20140811T150021_9YJTJ5Z5_.BKP List of Datafiles in Backup set 1 File LV Type Ckp SCN Ckp time Name---------------------------------1 full 1564193 11-AUG-14/U01/APP/ORACLE/ORADATA/DBSTYLE/SYSTEM01 . dbf 3 Full 1564193 11-aug-14/u01/app/oracle/oradata/dbstyle/sysaux01.dbf 5 full 1564193 11-aug-14/u 01/APP/ORACLE/ORADATA/DBSTYLE/UNDOTBS01.DBF 6 Full 1564193 11-aug-14/u01/app/oracle/oradata/dbstyle/users01.dbf BS Key type LV Size Device type Elapsed time completion time------------------------------------------------- ------------2 full 762.73M DISK 00:00:04 11-aug-14 BP key:2 status:available Comp Ressed:no tag:tag20140811t150021 Piece Name:/u01/app/oracle/fast_recovery_area/dbstyle/fdd32a078f321802e0430a50a8c0f4ff/backupset/   2014_08_11/O1_MF_NNNDF_TAG20140811T150021_9YJTJZ65_.BKP List of Datafiles in Backup set 2 Container id:3, PDB Name:dbs File LV Type Ckp SCN Ckp time Name---------------------------------7 full 1563088 11-aug-14/u01/a PP/ORACLE/ORADATA/DBSTYLE/DBS/SYSTEM01.DBF 8 Full 1563088 11-aug-14/u01/app/oracle/oradata/dbstyle/dbs/sysaux01 . dbf 9 Full 1563088 11-aug-14/u01/app/oracle/oradata/dbstyle/dbs/dbs_users01.dbfbs Key Type LV Size Devi Ce Type Elapsed Time completion time-------------------------------------------------------------3 Full 761 .49M DISK 00:00:04 11-aug-14 BP key:3 status:available compressed:no tag:tag20140811t15 0021 Piece Name:/u01/app/oracle/fast_recovery_area/dbstyle/fdd22bf463bc0f53e0430a50a8c0edd2/backupset/2014_08_11 /o1_mf_nnndf_tag20140811t150021_9YJTK68H_.BKP List of Datafiles in Backup set 3 Container id:2, PDB name:pdb$seed File LV Type Ckp SCN Ckp time Name---------------------------------2 full 1539785 10-jul-14/u01/app/oracle/oradata/dbstyle/pdbseed/sy       STEM01.DBF 4 full 1539785 10-jul-14/u01/app/oracle/oradata/dbstyle/pdbseed/sysaux01.dbfbs Key Type LV Size     Device Type Elapsed Time completion time-------------------------------------------------------------4 full 17.20M DISK 00:00:00 11-aug-14 BP key:4 status:available compressed:no tag:tag2014 0811t150101 Piece Name:/u01/app/oracle/fast_recovery_area/dbstyle/autobackup/2014_08_11/o1_mf_s_855327661_ 9YJTKFF8_.BKP SPFILE included:modification time:11-aug-14 SPFILE db_unique_name:dbstyle Control File included:ckp S cn:1565204 CKP time:11-aug-14

3. Delete sysaux01.dbf data file, simulate Sysaux table space corruption

[Email protected] dbstyle]$ rm-f sysaux01.dbf

4. Use Rman for restore operations, where the restore is not possible because the tablespace is not offline

rman> restore tablespace sysaux; Starting restore at 11-aug-14using Channel Ora_disk_1channel ora_disk_1:starting datafile backup set Restorechannel ORA_D Isk_1:specifying DataFile (s) to restore from backup Setchannel ora_disk_1:restoring datafile 00003 to/u01/app/oracle/or Adata/dbstyle/sysaux01.dbfchannel ora_disk_1:reading from backup piece/u01/app/oracle/fast_recovery_area/dbstyle/ backupset/2014_08_11/o1_mf_nnndf_tag20140811t150021_9yjtj5z5_.bkprman-00571: ================================== =========================rman-00569: =============== ERROR MESSAGE STACK follows ===============rman-00571: ========= ==================================================rman-03002:failure of Restore command at 08/11/2014 15:18:45ora-19870:error while restoring backup piece/u01/app/oracle/fast_recovery_area/dbstyle/backupset/2014_08_ 11/o1_mf_nnndf_tag20140811t150021_9yjtj5z5_.bkpora-19573:cannot obtain exclusive enqueue for DataFile 3 

5. Take the Sysaux table space offline, you need to use the offline immediate command

[[email protected] ~]$ sqlplus/as sysdbasql*plus:release 12.1.0.1.0 Production on Mon 15:01:27 2014Copyrigh  T (c) 1982, Oracle. All rights reserved. Connected to:oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bit productionwith The partitioning, Oracle Lab El Security, OLAP, advanced Analyticsand Real application testing options[email protected]> alter Tablespace SYSA  UX Offline;alter tablespace sysaux offline*error at line 1:ora-01116:error in opening database file 3ora-01110:data file 3: '/u01/app/oracle/oradata/dbstyle/sysaux01.dbf ' ora-27041:unable to open filelinux-x86_64 error:2: No such file or dir Ectoryadditional information:3[email protected]> alter tablespace sysaux offline immediate; Tablespace altered. [email protected]> select Tablespace_name,status from Dba_tablespaces;                 Tablespace_name STATUS---------------------------------------SYSTEM onlinesysaux       OFFLINEUNDOTBS1        Onlinetemp onlineusers ONLINE 

6. Use Rman again to recover tablespace

rman> restore Tablespace sysaux; Starting restore at 11-aug-14using Channel Ora_disk_1channel ora_disk_1:starting datafile backup set Restorechannel ORA_D Isk_1:specifying DataFile (s) to restore from backup Setchannel ora_disk_1:restoring datafile 00003 to/u01/app/oracle/or Adata/dbstyle/sysaux01.dbfchannel ora_disk_1:reading from backup piece/u01/app/oracle/fast_recovery_area/dbstyle/ Backupset/2014_08_11/o1_mf_nnndf_tag20140811t150021_9yjtj5z5_.bkpchannel ora_disk_1:piece handle=/u01/app/oracle /FAST_RECOVERY_AREA/DBSTYLE/BACKUPSET/2014_08_11/O1_MF_NNNDF_TAG20140811T150021_9YJTJ5Z5_.BKP tag= Tag20140811t150021channel ora_disk_1:restored backup piece 1channel ora_disk_1:restore complete, elapsed time:00:00:07 Finished restore at 11-aug-14rman> recover tablespace sysaux; Starting recover at 11-aug-14using channel ora_disk_1starting Media recoverymedia recovery complete, elapsed time:00:00:0 0Finished recover at 11-aug-14rman> alter tablespace Sysaux online; Statement ProcesseDrman> 

7.SYSAUX table space is back to normal

[Email protected]> select tablespace_name,status from Dba_tablespaces; Tablespace_name            STATUS---------------------------------------SYSTEM                 onlinesysaux                 ONLINEUNDOTBS1               onlinetemp                   Onlineusers                  Online[email protected]>

"Summary"
Regardless of the CDB or the PDB, if the non-critical table space is damaged, the least affected database processing method is to take the corrupted tablespace offline, then restore and recovery, so that the table space is restored to normal. So the A option is the most reasonable.

"Answer" A

Related references
http://docs.oracle.com/database/121/BRADV/rcmcomre.htm#BRADV89773

for more exciting articles, please visit the author's personal blog:www.dbstyle.net

This article is from the "into the ORACLE" blog, be sure to keep this source http://dbstyle.blog.51cto.com/8619508/1539114