【12C考題精解】OCP 1z0-060 QUESTION 8: Recovery of a Tablespace in the CDB

標籤：databases   running   online   file   

QUESTION 8

Your multitenant container (CDB) containing three pluggable databases (PDBs) is running in ARCHIVELOG mode. You find that the SYSAUX tablespace is corrupted in the root container. The steps to recover the tablespace are 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.
   10. 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

【題目示意】
本題考察的是CDB中的SYSAUX資料表空間完全恢複。

【解析】
在資料庫已經open的情況下，某些非關鍵資料檔案發生損壞。只需要在保證資料庫其他資料檔案可用的狀態下，對損壞的檔案或損壞檔案所在的資料表空間進行restore和recover操作。不需要關閉資料庫，再進行恢複。在restore之前，要求資料表空間處於offline狀態。

【實驗】
1.修改資料庫為歸檔模式

[[email protected] ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 11 14:58:41 2014Copyright (c) 1982, 2013, 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 Buffers      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 bytesDatabase Buffers             805306368 bytesRedo Buffers            4960256 bytesDatabase mounted.[email protected]> alter database archivelog;Database altered.[email protected]> alter database open;Database altered.[email protected]> alter system switch logfile;System altered.

2.使用RMAN備份資料庫

[email protected]> !rmanRecovery Manager: Release 12.1.0.1.0 - Production on Mon Aug 11 15:00:13 2014Copyright (c) 1982, 2013, 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_1channel ORA_DISK_1: SID=50 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying 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_recovery_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: AVAILABLE  Compressed: NO  Tag: TAG20140811T150021        Piece Name: /u01/app/oracle/fast_recovery_area/DBSTYLE/backupset/2014_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 /u01/app/oracle/oradata/DBSTYLE/undotbs01.dbf  6       Full 1564193    11-AUG-14 /u01/app/oracle/oradata/DBSTYLE/users01.dbfBS 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  Compressed: 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/app/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       Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------3       Full    761.49M    DISK        00:00:04     11-AUG-14              BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20140811T150021        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/system01.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: TAG20140811T150101        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 SCN: 1565204      Ckp time: 11-AUG-14

3.刪除sysaux01.dbf資料檔案，類比sysaux資料表空間損壞

[[email protected] DBSTYLE]$ rm -f sysaux01.dbf

4.使用RMAN進行restore操作，此時由於資料表空間沒有離線，因此無法進行restore

RMAN> restore tablespace sysaux;Starting restore at 11-AUG-14using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/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.將SYSAUX資料表空間離線，此時需要使用offline immediate命令

[[email protected] ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 11 15:01:27 2014Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Advanced Analyticsand Real Application Testing options[email protected]> alter tablespace sysaux 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 directoryAdditional 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.再次使用RMAN恢複資料表空間

RMAN> restore tablespace sysaux;Starting restore at 11-AUG-14using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/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:07Finished 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:00Finished recover at 11-AUG-14RMAN> alter tablespace sysaux online;Statement processedRMAN>

7.SYSAUX資料表空間恢複正常

[email protected]> select tablespace_name,status from dba_tablespaces;TABLESPACE_NAME            STATUS------------------------------ ---------SYSTEM                 ONLINESYSAUX                 ONLINEUNDOTBS1               ONLINETEMP                   ONLINEUSERS                  ONLINE[email protected]>

【小結】
無論CDB或者PDB，如果是非關鍵資料表空間發生損壞，對資料庫影響最小的處理方法就是，讓損壞的資料表空間離線，然後進行restore和recovery，進而使資料表空間恢複正常。因此A選項最合理。

【答案】 A

相關參考
http://docs.oracle.com/database/121/BRADV/rcmcomre.htm#BRADV89773

 更多精彩文章，請訪問作者個人部落格：www.dbstyle.net

本文出自 “INTO THE ORACLE” 部落格，請務必保留此出處http://dbstyle.blog.51cto.com/8619508/1539114