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.
ANSWER A,E
Create PDB for test:
[code language=”sql”]
(1)[CDB$ROOT]sys@orcl> create pluggable database q1 admin user q1 identified by q1;
(1)[CDB$ROOT]sys@orcl> alter pluggable database q1 open read write;
[/code]
Check tempfile name:
[code language=”sql”]
18:14:54 (1)[Q1]c##bushmelev_aa@orcl> select con_id,name from v$tempfile;
CON_ID | NAME
———- | —————————————————————————————————-
4 | +DATA/ORCL/219A48C9456728A4E053480A0A0AE1A5/TEMPFILE/temp.312.892552367
[/code]
we need to close database to delete tempfile
[code language=”sql”]
18:13:47 (1)[CDB$ROOT]sys@orcl> alter pluggable database q1 close force;
[/code]
remove file:
[code language=”bash”]
[grid@oel7-2 ~]$ asmcmd rm +DATA/ORCL/219A48C9456728A4E053480A0A0AE1A5/TEMPFILE/temp.312.892552367
[/code]
open database and check tempfile:
[code language=”sql”]
18:24:13 (1)[CDB$ROOT]sys@orcl> alter pluggable database q1 open read write;
18:24:47 (1)[Q1]c##bushmelev_aa@orcl> select con_id,name from v$tempfile;
CON_ID | NAME
———- | —————————————————————————————————-
4 | +DATA/ORCL/219A48C9456728A4E053480A0A0AE1A5/TEMPFILE/temp.312.892553061
[/code]
as i do not know how to currupt asm file, i will create another one on filesystem and drop old one:
[code language=”sql”]
18:29:11 (1)[Q1]c##bushmelev_aa@orcl> alter tablespace temp add tempfile ‘/ora01/app/oracle/temp.ora’ size 1G;
18:29:40 (1)[Q1]c##bushmelev_aa@orcl> alter tablespace temp drop tempfile ‘+DATA/ORCL/219A48C9456728A4E053480A0A0AE1A5/TEMPFILE/temp.312.892553061’;
Tablespace altered.
Elapsed: 00:00:00.14
18:30:55 (1)[Q1]c##bushmelev_aa@orcl> select con_id,name from v$tempfile;
CON_ID | NAME
———- | —————————————————————————————————-
4 | /ora01/app/oracle/temp.ora
[/code]
remove file from OS:
[code language=”bash”]
[root@oel7-2 ~]# rm /ora01/app/oracle/temp.ora
rm: remove regular file ‘/ora01/app/oracle/temp.ora’? y
[/code]
it is not deleted as beeing accessed by :
[code language=”bash”]
[root@oel7-2 ~]# lsof | grep deleted
ora_dbw0_ 6424 oracle 257uW REG 252,0 1073750016 143742085 /ora01/app/oracle/temp.ora (deleted)
oracle_10 10913 oracle 258u REG 252,0 1073750016 143742085 /ora01/app/oracle/temp.ora (deleted)
[/code]
lets empty this file
[code]
[root@oel7-2 ~]# lsof | grep "(deleted)$" | sed -re ‘s/^\S+\s+(\S+)\s+\S+\s+([0-9]+).*/\1\/fd\/\2/’ | while read file; do sudo bash -c ": > /proc/$file"; done
[/code]
And reproduce error:
[code language=”sql”]
18:35:11 (1)[Q1]c##bushmelev_aa@orcl> alter session set workarea_size_policy=manual;
18:35:24 (1)[Q1]c##bushmelev_aa@orcl> alter session set sort_area_size=1;
18:35:37 (1)[Q1]c##bushmelev_aa@orcl> alter session set hash_area_size=1;
18:53:23 (1)[Q1]c##bushmelev_aa@orcl> select * from all_objects a,all_objects b order by 1 desc;
select * from all_objects a,all_objects b order by 1 desc
*
ERROR at line 1:
ORA-01116: error in opening database file 205
ORA-01110: data file 205: ‘/ora01/app/oracle/temp.ora’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
[/code]
^_^ yahoo
[code language=”sql”]
18:54:18 (1)[Q1]c##bushmelev_aa@orcl> alter tablespace temp add tempfile ‘/ora01/app/oracle/temp2.ora’ size 1G;
18:54:46 (1)[Q1]c##bushmelev_aa@orcl> alter tablespace temp drop tempfile ‘/ora01/app/oracle/temp.ora’;
18:55:08 (1)[Q1]c##bushmelev_aa@orcl> select * from all_objects a,all_objects b order by 1 desc;
[/code]
check that temp is used by session:
[code language=”sql”]
18:55:35 (1)[CDB$ROOT]sys@orcl> @snapper all 5 1 251
Sampling SID 251 with interval 5 seconds, taking 1 snapshots…
— Session Snapper v4.22 – by Tanel Poder ( http://blog.tanelpoder.com/snapper ) – Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
—————————————————————————————————————————————————————————————————————
SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES
—————————————————————————————————————————————————————————————————————
251, C##BUSHMEL, STAT, recursive calls , 5, .98, , , , , 0 recursive CPU per recursive call
251, C##BUSHMEL, STAT, session logical reads , 169, 33.01, , , , , 486 total buffer visits
251, C##BUSHMEL, STAT, user I/O wait time , 486, 94.92, , , , , ~ per execution
251, C##BUSHMEL, STAT, non-idle wait time , 486, 94.92, , , , , ~ per execution
251, C##BUSHMEL, STAT, non-idle wait count , 659, 128.71, , , , , ~ per execution
251, C##BUSHMEL, STAT, messages sent , 15, 2.93, , , , , ~ per execution
251, C##BUSHMEL, STAT, enqueue requests , 5, .98, , , , , ~ per execution
251, C##BUSHMEL, STAT, enqueue releases , 5, .98, , , , , ~ per execution
251, C##BUSHMEL, STAT, physical write total IO requests , 614, 119.92, , , , , ~ per execution
251, C##BUSHMEL, STAT, physical write total bytes , 5029888, 982.4k, , , , , ~ per execution
251, C##BUSHMEL, STAT, cell physical IO interconnect bytes , 5029888, 982.4k, , , , , ~ per execution
251, C##BUSHMEL, STAT, db block gets , 164, 32.03, , , , , ~ per execution
251, C##BUSHMEL, STAT, db block gets from cache , 164, 32.03, , , , , ~ per execution
251, C##BUSHMEL, STAT, consistent gets , 5, .98, , , , , ~ per execution
251, C##BUSHMEL, STAT, consistent gets from cache , 5, .98, , , , , ~ per execution
251, C##BUSHMEL, STAT, consistent gets pin , 5, .98, , , , , ~ per execution
251, C##BUSHMEL, STAT, consistent gets pin (fastpath) , 5, .98, , , , , ~ per execution
251, C##BUSHMEL, STAT, logical read bytes from cache , 1384448, 270.4k, , , , , ~ per execution
251, C##BUSHMEL, STAT, db block changes , 15, 2.93, , , , , ~ per execution
251, C##BUSHMEL, STAT, consistent changes , 15, 2.93, , , , , ~ per execution
251, C##BUSHMEL, STAT, physical writes , 614, 119.92, , , , , ~ per execution
251, C##BUSHMEL, STAT, physical writes direct , 614, 119.92, , , , , ~ per execution
251, C##BUSHMEL, STAT, physical write IO requests , 614, 119.92, , , , , 8.19k bytes per request
251, C##BUSHMEL, STAT, physical writes direct temporary tablespace , 614, 119.92, , , , , ~ per execution
251, C##BUSHMEL, STAT, physical write bytes , 5029888, 982.4k, , , , , ~ per execution
251, C##BUSHMEL, STAT, physical writes non checkpoint , 614, 119.92, , , , , ~ per execution
251, C##BUSHMEL, STAT, file io service time , 6140, 1.2k, , , , , 10us bad guess of IO service time per IO request
251, C##BUSHMEL, STAT, file io wait time , 4793456, 936.22k, , , , , 7.81ms bad guess of IO wait time per IO request
251, C##BUSHMEL, STAT, temp space allocated (bytes) , 5242880, 1.02M, , , , , ~ per execution
251, C##BUSHMEL, STAT, no work – consistent read gets , 5, .98, , , , , ~ per execution
251, C##BUSHMEL, STAT, table scan rows gotten , 332, 64.84, , , , , ~ per execution
251, C##BUSHMEL, STAT, table scan disk non-IMC rows gotten , 332, 64.84, , , , , ~ per execution
251, C##BUSHMEL, STAT, table scan blocks gotten , 5, .98, , , , , ~ per execution
251, C##BUSHMEL, STAT, buffer is pinned count , 317, 61.91, , , , , 65.23 % buffer gets avoided thanks to buffer pin caching
251, C##BUSHMEL, TIME, DB CPU , 146000, 28.52ms, 2.9%, [@ ], , ,
251, C##BUSHMEL, TIME, sql execute elapsed time , 6067709, 1.19s, 118.5%, [##########], , ,
251, C##BUSHMEL, TIME, DB time , 6067709, 1.19s, 118.5%, [##########], , , -18.51 % unaccounted time
251, C##BUSHMEL, WAIT, local write wait , 49120, 9.59ms, 1.0%, [W ], 12, 2.34, 4.09ms average wait
251, C##BUSHMEL, WAIT, direct path write temp , 4926337, 962.18ms, 96.2%, [WWWWWWWWWW], 609, 118.95, 8.09ms average wait
— End of Stats snap 1, end=2015-10-08 11:55:43, seconds=5.1
—————————————————————————————————————
ActSes %Thread | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS
—————————————————————————————————————
1.00 (100%) | 1 | 64tn8dyyrhyhn | 1 | direct path write temp | User I/O
— End of ASH snap 1, end=2015-10-08 11:55:43, seconds=5, samples_taken=49, AAS=1
PL/SQL procedure successfully completed.
[/code]
B,C is wrong, because temp files are not backed up and recreate on startup ( since 11g )