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中拔去时，这些临时表空间也被拔去。
图中对用户使用那个临时表空间进行了描述：
从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=1010+0 records in10+0 records out1024000 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
本文出自 “INTO THE ORACLE” 博客，请务必保留此出处http://dbstyle.blog.51cto.com/8619508/1535932
【Q&A】12C OCP 1z0-060 QUESTION 1: About Managing Temporary Tablespaces in a CDB,布布扣,bubuko.com
【Q&A】12C OCP 1z0-060 QUESTION 1: About Managing Temporary Tablespaces in a CDB
原文：http://dbstyle.blog.51cto.com/8619508/1535932