【12C考题精解】OCP 1z0-060 QUESTION 6: Recovery of a Tablespace in the PDB-CSDN博客

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

[oracle@dbstyle ~]$ rman target sys
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

[oracle@dbstyle ~]$ rman target /
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

[oracle@dbstyle ~]$ rman target c##dbstyle/oracle@DBSTYLE
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的方法

[oracle@dbstyle ~]$ rman target hr/hr@DBS
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

[oracle@dbstyle ~]$ sqlplus / as sysdba
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.删除数据文件，模拟损坏

SYS@DBSTYLE>!rm -rf /u01/app/oracle/oradata/DBSTYLE/DBS/DBS_users01.dbf

复制代码

3.此时使用丢失数据文件的表空间，出现报错

SYS@DBSTYLE> create table test tablespace users as select * from dba_objects;
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状态

SYS@DBSTYLE> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE

复制代码

5.如果直接进行恢复，会有报错，如下：

[oracle@dbstyle DBS]$ rman target sys/oracle@DBS
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：

SYS@DBSTYLE> alter tablespace users offline immediate;
Tablespace altered.
SYS@DBSTYLE> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS OFFLINE

复制代码

7.恢复离线的表空间：

RMAN> restore datafile 9;
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