QUESTION 6
Your multitenant container database, CDB1, is running in ARCHIVELOG mode and has two pluggable databases, HR_PDB and ACCOUNTS_PDB. An RMAN backup exists for the database.
You issue the command to open ACCOUNTS_PDB and find that the USERDATA.DBF data file for the default permanent tablespace USERDATA belonging to ACCOUNTS_PDB is corrupted.
What should you do before executing the commands to restore and recover the data file in ACCOUNTS_PDB?
A. Place CDB1 in the mount stage and then the USERDATA tablespace offline in ACCOUNTS_PDB.
B. Place CDB1 in the mount stage and issue the ALTER PLUGGABLE DATABASE accounts_pdb CLOSE IMMEDIATE command.
C. Issue the ALTER PLUGGABLE DATABASE accounts_pdb RESTRICTED command.
D. Take the USERDATA tablespace offline in ACCOUNTS_PDB.
【题目示意】
本题考察的是PDB中的表空间完全恢复，需要注意的是，根据题意分析，ACCOUNTS_PDB应该是已经open的PDB，发生数据文件损坏。
【解析】
在数据库已经open的情况下，某些非关键数据文件发生损坏。应该在保证数据库其他数据文件可用的状态下，对损坏的文件或损坏文件所在的表空间进行restore和recover操作。在restore之前，要求表空间处于offline状态。
使用rman连接12C的多容器数据库时，可以在ROOT container中，或者一个或多个PDB container中执行RMAN操作。
1.可以使用RMAN连到root container，来操作整个CDB
2.可以使用RMAN连到root container，来操作root container
3.可以使用RMAN连到root 或者PDB，来操作某一个container
- 如果连到root container，必须在rman命令中使用 PLUGGABLE DATABASE关键字。如：使用BACKUP PLUGGABLE DATABASE命令备份一个PDB
- 如果连到PDB，只需要使用BACKUP DATABASE来备份PDB
4.连到root container，可以使用一条命令操作多个PDB，如备份sales和hr这两个PDB，使用下面的命令BACKUP PLUGGABLE DATABASE sales, hr;
5.直接连到PDB有些操作是受限的：
- 备份归档日志
- 删除归档日志
- 删除归档日志备份
- 还原归档日志（在介质恢复期间rman不能还原归档日志）
- pitr基于时间点的恢复
- TSPITR基于表空间的时间点恢复
- 表恢复
- 复制数据库
- 闪回操作
- 运行Data Recovery Advisor
- report/delete obsolete
- 注册catalog数据库
- 导入catalog
- 重启数据库
- 配置rman环境 configure
- 当连到PDB，是不能连到catalog的
连接到root container的三种方法：
1.使用sys 用户来连接root container
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Aug 8 12:10:58 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: DBSTYLE (DBID=2767578829)
RMAN>
2.使用操作系统验证来连接root
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Aug 8 12:11:48 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBSTYLE (DBID=2767578829)
RMAN>
3.通过net service 使用具备sysbackup权限的common 用户来连接root
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Aug 8 12:15:33 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBSTYLE (DBID=2767578829)
RMAN>
连接到pdb的方法
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Aug 8 12:19:34 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBSTYLE (DBID=2767578829)
RMAN>
【实验】
1.启动PDB
SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 8 11:14:50 2014
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, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options
SYS@DBSTYLE> alter session set container=dbs;
Session altered.
SYS@DBSTYLE> startup
Pluggable Database opened.
2.删除数据文件，模拟损坏
3.此时使用丢失数据文件的表空间，出现报错
create table test tablespace users as select * from dba_objects
*
ERROR at line 1:
ORA-01110: data file 9: '/u01/app/oracle/oradata/DBSTYLE/DBS/DBS_users01.dbf'
ORA-01116: error in opening database file 9
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
4.此时丢失数据文件的表空间为online状态
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
5.如果直接进行恢复，会有报错，如下：
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Aug 8 10:41:05 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBSTYLE (DBID=2767578829)
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 762.73M DISK 00:00:03 07-AUG-14
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140807T232050
Piece Name: /u01/app/oracle/fast_recovery_area/DBSTYLE/FDD32A078F321802E0430A50A8C0F4FF/backupset/2014_08_07/o1_mf_nnndf_TAG20140807T232050_9y76cd33_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 1563093 07-AUG-14 /u01/app/oracle/oradata/DBSTYLE/DBS/system01.dbf
8 Full 1563093 07-AUG-14 /u01/app/oracle/oradata/DBSTYLE/DBS/sysaux01.dbf
9 Full 1563093 07-AUG-14 /u01/app/oracle/oradata/DBSTYLE/DBS/DBS_users01.dbf
RMAN> restore datafile 9;
Starting restore at 08-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=57 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 00009 to /u01/app/oracle/oradata/DBSTYLE/DBS/DBS_users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DBSTYLE/FDD32A078F321802E0430A50A8C0F4FF/backupset/2014_08_07/o1_mf_nnndf_TAG20140807T232050_9y76cd33_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/08/2014 10:41:13
ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/DBSTYLE/FDD32A078F321802E0430A50A8C0F4FF/backupset/2014_08_07/o1_mf_nnndf_TAG20140807T232050_9y76cd33_.bkp
ORA-19573: cannot obtain exclusive enqueue for datafile 9
6.首先将有问题的表空间offline：
Tablespace altered.
SYS@DBSTYLE> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS OFFLINE
7.恢复离线的表空间：
Starting restore at 08-AUG-14
using channel ORA_DISK_1
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 00009 to /u01/app/oracle/oradata/DBSTYLE/DBS/DBS_users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DBSTYLE/FDD32A078F321802E0430A50A8C0F4FF/backupset/2014_08_07/o1_mf_nnndf_TAG20140807T232050_9y76cd33_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DBSTYLE/FDD32A078F321802E0430A50A8C0F4FF/backupset/2014_08_07/o1_mf_nnndf_TAG20140807T232050_9y76cd33_.bkp tag=TAG20140807T232050
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-AUG-14
RMAN> recover datafile 9;
Starting recover at 08-AUG-14
using channel ORA_DISK_1
applied offline range to datafile 00009
offline range RECID=7 STAMP=855013520
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-AUG-14
8.恢复完成，使表空间上线：
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS OFFLINE
SYS@DBSTYLE> alter tablespace users online;
Tablespace altered.
SYS@DBSTYLE> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
SYS@DBSTYLE>
【小结】
对于已经打开的PDB，如果数据文件发生损坏，对数据库影响最小的处理方法就是，让丢失了数据文件的表空间离线，然后进行restore和recovery，进而使表空间恢复正常。而把CDB启动到mount模式等操作显然是对数据库影响很大，因此不应采用。
【答案】 D
相关参考
http://docs.oracle.com/database/121/BRADV/rcmcnctg.htm#BRADV671
http://docs.oracle.com/database/121/BRADV/rcmcomre.htm#BRADV89773
Speak Your Mind