【Q&A】OCP 1z0-060 QUESTION 1: About Managing Temporary Tablespaces in a CDB

2014 年 8 月 5 日 by Leave a Comment



    • QUESTION 1

    Your multitenant container (CDB) contains two pluggable databases (PDB), HR_PDB and ACCOUNTS_PDB, both of which use the CDB tablespace. The temp file is called temp01.tmp. A user issues a query on a table on one of the PDBs and receives the following error:

    ERROR at line 1:
    ORA-01565: error in identifying file ‘/u01/app/oracle/oradata/CDB1/temp01.tmp’
    ORA-27037: unable to obtain file status
    Identify two ways to rectify the error.

    A. Add a new temp file to the temporary tablespace and drop the temp file that that produced the error.
    B. Shut down the database instance,restore the temp01.tmp file from the backup,and then restart the database.
    C. Take the temporary tablespace offline, recover the missing temp file by applying redo logs, and then bring the temporary tablespace online.
    D. Shutdown the database instance, restore and recover the temp file from the backup, and then open the database with RESETLOGS.
    E. Shut down the database instance and then restart the CDB and PDBs.

    【题目示意】

    考察了多租户环境下的临时表空间数据文件丢失的处理方法

    【解析】

    在CDB中有一个默认的临时表空间或表空间组。要修改这个临时表空间，当前的数据库必须是root容器数据库。在root容器数据库中，可以创建额外的临时表空间，可以把这些临时表空间分配给指定的用户。

    可以为PDB创建默认的临时表空间，也可以单独为PDB创建额外的临时表空间，同时可以把这个临时表空间分配给PDB中的指定用户。当一个PDB从CDB中拔去时，这些临时表空间也被拔去。

    图中对用户使用那个临时表空间进行了描述： 

    q1[2014080401]

    从11g开始，当数据库open的时候，记录在控制文件中的临时表空间发生丢失，则会根据临时表空间之前的size，autoextend和maxsize属性重新创建已经丢失的临时表空间，如果数据库是OMF的管理方法，临时表空间中的临时文件会创建在DB_CREATE_FILE_DEST参数所指定的路径，如果非OMF管理，则会创建在原来的路径下。此种丢失的情况，在alert中，可以看到如下的报错：

    ORA-01565: error in identifying file ‘/u01/app/oracle/oradata/CDB1/temp01.tmp’
    ORA-27037: unable to obtain file status
    Linux-x86_64 Error: 2: No such file or directory

    当数据库关闭并再次打开时，在alert中会看到临时表空间重建的日志。

    Re-creating tempfile /u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf

    仅仅当临时表空间中的文件丢失的情况下会重新创建，如果临时文件任然存在在RMAN repository所记录的路径中，只是文件头损坏，则RMAN不会重建创建临时文件，如果RMAN无法创建临时文件，数据库依旧可以打开。此过程在alert中表现如下：

    Errors in file /u01/app/oracle/diag/rdbms/dbstyle/DBSTYLE/trace/DBSTYLE_dbw0_3112.trc:
    ORA-01186: file 203 failed verification tests
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    File 203 not verified due to error ORA-01157
    Tue Aug 05 00:35:27 2014
    Cannot re-create tempfile /u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf, the same name file exists

    【实验】

    观察临时表空间中的文件丢失的重建

    1.连到数据库，检查container情况：

    [oracle@dbstyle ~]$ sqlplus / as sysdba

    SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 5 11:04:09 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> COLUMN NAME FORMAT A8
    SYS@DBSTYLE> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;


    NAME         CON_ID       DBID    CON_UID GUID
    -------- ---------- ---------- ---------- --------------------------------
    CDB$ROOT          1 2767578829          1 FDD22BF463BD0F53E0430A50A8C0EDD2
    PDB$SEED          2 4095226510 4095226510 FDD22BF463BC0F53E0430A50A8C0EDD2
    DBS               3 1831021044 1831021044 FDD32A078F321802E0430A50A8C0F4FF

    SYS@DBSTYLE> show con_name

    CON_NAME
    ------------------------------
    CDB$ROOT

    2.切换PDB为DBS，查看DBS的临时表空间文件：

    SYS@DBSTYLE> alter session set container=DBS;

    Session altered.

    SYS@DBSTYLE> show con_name

    CON_NAME
    ------------------------------
    DBS

    SYS@DBSTYLE> select FILE_NAME,TABLESPACE_NAME,CON_ID from cdb_temp_files;

    FILE_NAME                                         TABLESPACE_NAME      CON_ID
    ------------------------------------------------- ------------------- -------
    /u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf    TEMP                      3

    3.删除临时文件：

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

    4.进行一个大的排序操作，发现报错，此报错和题目中的报错一致：

    SYS@DBSTYLE> select * from tests order by 1 desc;
    select * from tests order by 1 desc
    *
    ERROR at line 1:
    ORA-01565: error in identifying file
    '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    ORA-27037: unable to obtain file status
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3

    5.重启PDB，发现临时文件已经重建完成：

    SYS@DBSTYLE> startup force
    Pluggable Database opened.
    SYS@DBSTYLE> !ls /u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf
    /u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf

    6.alert日志如下：

    ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE
    Tue Aug 05 11:15:16 2014
    ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
    Pluggable database DBS closed
    Completed: ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE
    ALTER PLUGGABLE DATABASE OPEN
    Tue Aug 05 11:15:16 2014
    Errors in file /u01/app/oracle/diag/rdbms/dbstyle/DBSTYLE/trace/DBSTYLE_dbw0_3112.trc:
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    ORA-27037: unable to obtain file status
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3
    Tue Aug 05 11:15:16 2014
    Errors in file /u01/app/oracle/diag/rdbms/dbstyle/DBSTYLE/trace/DBSTYLE_dbw0_3112.trc:
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    ORA-27037: unable to obtain file status
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3
    Tue Aug 05 11:15:16 2014
    Errors in file /u01/app/oracle/diag/rdbms/dbstyle/DBSTYLE/trace/DBSTYLE_dbw0_3112.trc:
    ORA-01186: file 203 failed verification tests
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    Tue Aug 05 11:15:16 2014
    File 203 not verified due to error ORA-01157
    Tue Aug 05 11:15:16 2014
    Errors in file /u01/app/oracle/diag/rdbms/dbstyle/DBSTYLE/trace/DBSTYLE_dbw0_3112.trc:
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    ORA-27037: unable to obtain file status
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3
    Tue Aug 05 11:15:16 2014
    Errors in file /u01/app/oracle/diag/rdbms/dbstyle/DBSTYLE/trace/DBSTYLE_dbw0_3112.trc:
    ORA-01186: file 203 failed verification tests
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    File 203 not verified due to error ORA-01157
    Tue Aug 05 11:15:16 2014
    Re-creating tempfile /u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf
    Opening pdb DBS (3) with no Resource Manager plan active
    Pluggable database DBS opened read write
    Completed: ALTER PLUGGABLE DATABASE OPEN

    观察临时表空间中的文件损坏的重建

    1.使用dd命令，破坏临时文件：

    SYS@DBSTYLE> !dd if=/dev/zero of=/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf bs=102400 count=10
    10+0 records in
    10+0 records out
    1024000 bytes (1.0 MB) copied, 0.00178821 s, 573 MB/s

    2.PDB启动正常：

    SYS@DBSTYLE> startup force
    Pluggable Database opened.
    SYS@DBSTYLE>

    3.此时做个比较大的排序操作，就会报错：

    SYS@DBSTYLE> select * from tests order by 1 desc;
    select * from tests order by 1 desc
    *
    ERROR at line 1:
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'

    4.alert日志如下：

    ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE
    Tue Aug 05 11:20:32 2014
    ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
    Pluggable database DBS closed
    Completed: ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE
    ALTER PLUGGABLE DATABASE OPEN
    Tue Aug 05 11:20:32 2014
    Errors in file /u01/app/oracle/diag/rdbms/dbstyle/DBSTYLE/trace/DBSTYLE_dbw0_3112.trc:
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    ORA-27048: skgfifi: file header information is invalid
    Additional information: 19
    Tue Aug 05 11:20:32 2014
    Errors in file /u01/app/oracle/diag/rdbms/dbstyle/DBSTYLE/trace/DBSTYLE_dbw0_3112.trc:
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    ORA-27048: skgfifi: file header information is invalid
    Additional information: 19
    Tue Aug 05 11:20:32 2014
    Errors in file /u01/app/oracle/diag/rdbms/dbstyle/DBSTYLE/trace/DBSTYLE_dbw0_3112.trc:
    ORA-01186: file 203 failed verification tests
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    Tue Aug 05 11:20:32 2014
    File 203 not verified due to error ORA-01157
    Tue Aug 05 11:20:32 2014
    Errors in file /u01/app/oracle/diag/rdbms/dbstyle/DBSTYLE/trace/DBSTYLE_dbw0_3112.trc:
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    ORA-27048: skgfifi: file header information is invalid
    Additional information: 19
    Tue Aug 05 11:20:32 2014
    Errors in file /u01/app/oracle/diag/rdbms/dbstyle/DBSTYLE/trace/DBSTYLE_dbw0_3112.trc:
    ORA-01186: file 203 failed verification tests
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    File 203 not verified due to error ORA-01157
    Tue Aug 05 11:20:32 2014
    Cannot re-create tempfile /u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf, the same name file exists
    Tue Aug 05 11:20:32 2014
    Errors in file /u01/app/oracle/diag/rdbms/dbstyle/DBSTYLE/trace/DBSTYLE_dbw0_3112.trc:
    ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
    ORA-01110: data file 203: '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf'
    ORA-27048: skgfifi: file header information is invalid
    Additional information: 19
    Opening pdb DBS (3) with no Resource Manager plan active
    Pluggable database DBS opened read write
    Completed: ALTER PLUGGABLE DATABASE OPEN

    5.删除被破坏的临时文件，重启PDB故障解决：

    SYS@DBSTYLE> !rm -rf /u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf
    SYS@DBSTYLE> startup force
    Pluggable Database opened.

    【小结】

    A. 临时表空间中的数据文件丢失，可以采用向临时表空间中添加新的数据文件，并把丢失的文件drop掉的方法处理，所以A正确。
    B. 临时文件中存在的数据都是临时数据，而非永久数据，因此无需进行还原和恢复，并且RMAN也不对临时文件进行备份。
    C.临时文件可以进行offline操作，alter database tempfile '/u01/app/oracle/oradata/DBSTYLE/DBS/temp01.dbf' offline; 但临时文件不能recover。
    D. 临时文件中存在的数据都是临时数据，而非永久数据，因此无需进行还原和恢复。
    E. 如果临时表空间中的文件丢失，重启数据库实例时，会自动创建丢失的临时文件，所以E正确。

    【答案】 A，E 

    相关参考

    http://docs.oracle.com/cd/E16655_01/backup.121/e17630/rcmcomre.htm#BRADV89772

    http://docs.oracle.com/database/121/ADMIN/cdb_admin.htm#ADMIN13674 

    Filed Under: 1z0-060, OCP, Oracle

    Speak Your Mind

    *

    京ICP备14059771号-2