【Q&A】12C OCP 1z0-060 QUESTION 6: Performing Complete Recovery of a Tablespace in the PDB




  • 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

    8.恢复完成,使表空间上线:

    SYS@DBSTYLE> select TABLESPACE_NAME,STATUS from dba_tablespaces;

    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

    *

    京ICP备14059771号-2