Neste artigo vamos explorar algumas formas de se recuperar de uma perda de datafile de sistema no PDB. Antes disso, vamos realizar um backup do ambiente:
[oracle@quiasma ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Mon May 17 03:56:47 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: ASWAN (DBID=1340416544)
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Starting backup at 17-MAY-21
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=106 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=1 STAMP=1072501770
input archived log thread=1 sequence=7 RECID=2 STAMP=1072501809
input archived log thread=1 sequence=8 RECID=3 STAMP=1072503370
input archived log thread=1 sequence=9 RECID=4 STAMP=1072503403
input archived log thread=1 sequence=10 RECID=5 STAMP=1072586228
input archived log thread=1 sequence=11 RECID=6 STAMP=1072586257
input archived log thread=1 sequence=12 RECID=7 STAMP=1072586635
input archived log thread=1 sequence=13 RECID=8 STAMP=1072586846
input archived log thread=1 sequence=14 RECID=9 STAMP=1072586861
input archived log thread=1 sequence=15 RECID=10 STAMP=1072587344
input archived log thread=1 sequence=16 RECID=11 STAMP=1072756615
channel ORA_DISK_1: starting piece 1 at 17-MAY-21
channel ORA_DISK_1: finished piece 1 at 17-MAY-21
piece handle=/oracle/fra/ASWAN/backupset/2021_05_17/o1_mf_annnn_TAG20210517T035655_jb44xr1j_.bkp tag=TAG20210517T035655 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 17-MAY-21
Starting backup at 17-MAY-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/dados/ASWAN/datafile/o1_mf_system_j9yzl519_.dbf
input datafile file number=00003 name=/oracle/dados/ASWAN/datafile/o1_mf_sysaux_j9wdsb2f_.dbf
input datafile file number=00004 name=/oracle/dados/ASWAN/datafile/o1_mf_undotbs1_j9wc1bk9_.dbf
input datafile file number=00007 name=/oracle/dados/ASWAN/datafile/o1_mf_users_j9wc1clt_.dbf
channel ORA_DISK_1: starting piece 1 at 17-MAY-21
channel ORA_DISK_1: finished piece 1 at 17-MAY-21
piece handle=/oracle/fra/ASWAN/backupset/2021_05_17/o1_mf_nnndf_TAG20210517T035659_jb44xv9n_.bkp tag=TAG20210517T035659 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_j9wcyoh3_.dbf
input datafile file number=00010 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_sysaux_j9wcyoh7_.dbf
input datafile file number=00011 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_undotbs1_j9wcyoh8_.dbf
input datafile file number=00012 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_users_j9yz06wp_.dbf
channel ORA_DISK_1: starting piece 1 at 17-MAY-21
channel ORA_DISK_1: finished piece 1 at 17-MAY-21
piece handle=/oracle/fra/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/backupset/2021_05_17/o1_mf_nnndf_TAG20210517T035659_jb44y2gv_.bkp tag=TAG20210517T035659 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oracle/dados/ASWAN/datafile/o1_mf_system_j9wc9px6_.dbf
input datafile file number=00006 name=/oracle/dados/ASWAN/datafile/o1_mf_sysaux_j9wc9px1_.dbf
input datafile file number=00008 name=/oracle/dados/ASWAN/datafile/o1_mf_undotbs1_j9wc9px7_.dbf
channel ORA_DISK_1: starting piece 1 at 17-MAY-21
channel ORA_DISK_1: finished piece 1 at 17-MAY-21
piece handle=/oracle/fra/ASWAN/C2469670772D160AE0536A00A8C00FFA/backupset/2021_05_17/o1_mf_nnndf_TAG20210517T035659_jb44y5gt_.bkp tag=TAG20210517T035659 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 17-MAY-21
Starting backup at 17-MAY-21
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=12 STAMP=1072756632
channel ORA_DISK_1: starting piece 1 at 17-MAY-21
channel ORA_DISK_1: finished piece 1 at 17-MAY-21
piece handle=/oracle/fra/ASWAN/backupset/2021_05_17/o1_mf_annnn_TAG20210517T035712_jb44y8oz_.bkp tag=TAG20210517T035712 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-MAY-21
Starting Control File and SPFILE Autobackup at 17-MAY-21
piece handle=/oracle/fra/ASWAN/autobackup/2021_05_17/o1_mf_s_1072756633_jb44y9vw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-MAY-21
Agora vamos coletar o nome de um datafile da tablespace SYSTEM do PDB HIPOFISE1:
[oracle@quiasma ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Mon May 17 03:58:06 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;
NAME OPEN_MODE CDB
--------- -------------------- ---
ASWAN READ WRITE YES
SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY
HIPOFISE1
READ WRITE
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
Session altered.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_j9wcy
oh3_.dbf
Removendo o datafile:
SQL> ! rm /oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_j9wcyoh3_.dbf
Neste ponto, ao rodar o comando abaixo, a instância já reporta a falta do datafile:
SQL> DESC DBA_DATA_FILES
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 9
ORA-01110: data file 9:
'/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_j9wc
yoh3_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Assim, como o problema é em um PDB específico, loguei no CDB$ROOT e o baixei:
SQL> conn / as sysdba
Connected.
SQL> ALTER PLUGGABLE DATABASE HIPOFISE1 CLOSE;
Pluggable database altered.
No RMAN, ao realizar o restore/recover da tablespace SYSTEM, podemos colocar na frente o nome do PDB em questão, conforme exemplo abaixo:
RMAN> RESTORE TABLESPACE HIPOFISE1:SYSTEM;
Starting restore at 17-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=107 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 /oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_j9wcyoh3_.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/backupset/2021_05_17/o1_mf_nnndf_TAG20210517T035659_jb44y2gv_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/backupset/2021_05_17/o1_mf_nnndf_TAG20210517T035659_jb44y2gv_.bkp tag=TAG20210517T035659
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-MAY-21
RMAN> RECOVER TABLESPACE HIPOFISE1:SYSTEM;
Starting recover at 17-MAY-21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-MAY-21
Abrindo o PDB e validando:
SQL> select name,open_mode,cdb from v$database;
NAME OPEN_MODE CDB
--------- -------------------- ---
ASWAN READ WRITE YES
SQL> select name,open_mode from v$pdbs;
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY
HIPOFISE1
MOUNTED
SQL> ALTER PLUGGABLE DATABASE HIPOFISE1 OPEN;
Pluggable database altered.
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
Session altered.
SQL> DESC DBA_DATA_FILES;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
LOST_WRITE_PROTECT VARCHAR2(7)
Nessa situação, em vez de realizar o restore/recover da tablespace em questão, poderíamos também realizar o processo no PDB como um todo. Para simular, fiz um novo backup do ambiente e removi novamente um datafile do PDB:
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
Session altered.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_jb456
qo6_.dbf
SQL> ! rm /oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_jb456qo6_.dbf
Alert do ambiente já gritando o problema:
2021-05-17 04:13:44.933000 -03:00
Errors in file /oracle/18.0.0/base/diag/rdbms/aswan/ASWAN/trace/ASWAN_j000_3445.trc:
ORA-12012: error on auto execute of job "SYS"."CLEANUP_ONLINE_IND_BUILD"
ORA-01116: error in opening database file
Errors in file /oracle/18.0.0/base/diag/rdbms/aswan/ASWAN/trace/ASWAN_mz00_3449.trc:
ORA-01110: data file 9: '/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_jb456qo6_.dbf'
ORA-01565: error in identifying file '/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_jb456qo6_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 1 new persistent data failures
Baixando o PDB:
SQL> ALTER PLUGGABLE DATABASE HIPOFISE1 CLOSE;
Pluggable database altered.
Fazendo o seu restore e recover:
RMAN> RESTORE PLUGGABLE DATABASE HIPOFISE1;
Starting restore at 17-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 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 /oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_jb456qo6_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_sysaux_j9wcyoh7_.dbf
channel ORA_DISK_1: restoring datafile 00011 to /oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_undotbs1_j9wcyoh8_.dbf
channel ORA_DISK_1: restoring datafile 00012 to /oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_users_j9yz06wp_.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/backupset/2021_05_17/o1_mf_nnndf_TAG20210517T041115_jb45rtt5_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/backupset/2021_05_17/o1_mf_nnndf_TAG20210517T041115_jb45rtt5_.bkp tag=TAG20210517T041115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 17-MAY-21
RMAN> RECOVER PLUGGABLE DATABASE HIPOFISE1;
Starting recover at 17-MAY-21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-MAY-21
Abrindo o PDB:
SQL> ALTER PLUGGABLE DATABASE HIPOFISE1 OPEN;
Pluggable database altered.
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
Session altered.
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
SQL> DESC DBA_DATA_FILES;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
LOST_WRITE_PROTECT VARCHAR2(7)
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish