Skip to content

1z0-060 Q1

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.


lets check

ANSWER A,E

Create PDB for test:

 (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;

Check tempfile name:

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

we need to close database to delete tempfile

18:13:47 (1)[CDB$ROOT]sys@orcl> alter pluggable database q1 close force;

remove file:

[grid@oel7-2 ~]$ asmcmd rm +DATA/ORCL/219A48C9456728A4E053480A0A0AE1A5/TEMPFILE/temp.312.892552367

open database and check tempfile:

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

as i do not know how to currupt asm file, i will create another one on filesystem and drop old one:

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

remove file from OS:

[root@oel7-2 ~]# rm /ora01/app/oracle/temp.ora
rm: remove regular file ‘/ora01/app/oracle/temp.ora’? y

it is not deleted as beeing accessed by :

[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)

lets empty this file

[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

And reproduce error:

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

^_^ yahoo

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;

check that temp is used by session:

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.

B,C is wrong, because temp files are not backed up and recreate on startup ( since 11g )

[свернуть]

Primary Sidebar

Яндекс.Метрика