Hoje é dia de destruir e reconstruir. Vamos simular a perda de um datafile “non-system” (ou seja, que não abriga os metadados do database), em um ambiente em noarchivelog mode. Reconhecendo o ambiente:
[oracle@oel8 ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Jul 23 20:37:44 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: RMANDB (DBID=3825250984)
RMAN> SELECT NAME,LOG_MODE,OPEN_MODE FROM V$DATABASE;
using target database control file instead of recovery catalog
NAME LOG_MODE OPEN_MODE
--------- ------------ --------------------
RMANDB NOARCHIVELOG READ WRITE
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name RMANDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 880 SYSTEM YES /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
2 120 TS_HIPO_CATALOG NO /oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
3 730 SYSAUX NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 305 UNDOTBS1 YES /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 200 TS_CORTEX_CATALOG NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 16 USERS NO /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 69 TEMP 32767 /oracle/dados/RMANDB/datafile/o1_mf_temp_h8nyvt1f_.tmp
Realizando um backup full do banco (como o mesmo está em noarchivelog mode, é necessário montá-lo para realizar o processo):
RMAN> SHUTDOWN IMMEDIATE;
database closed
database dismounted
Oracle instance shut down
RMAN> STARTUP MOUNT;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1610612016 bytes
Fixed Size 8658224 bytes
Variable Size 520093696 bytes
Database Buffers 1073741824 bytes
Redo Buffers 8118272 bytes
RMAN> BACKUP DATABASE;
Starting backup at 2021-07-23:20:39:14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
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/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00002 name=/oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-07-23:20:39:15
channel ORA_DISK_1: finished piece 1 at 2021-07-23:20:40:40
piece handle=/oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T203914_jhpns3d7_.bkp tag=TAG20210723T203914 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 2021-07-23:20:40:40
Starting Control File and SPFILE Autobackup at 2021-07-23:20:40:40
piece handle=/oracle/fra/RMANDB/autobackup/2021_07_23/o1_mf_s_1078691912_jhpnvs16_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-07-23:20:40:43
Para explorarmos algumas situações que ocorrerão, após o backup Full vamos criar uma tabela e populá-la com 1 registro:
RMAN> ALTER DATABASE OPEN;
Statement processed
RMAN> SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='BSS';
USERNAME
--------------------------------------------------------------------------------
DEFAULT_TABLESPACE
------------------------------
BSS
USERS
RMAN> CREATE TABLE BSS.ASWAN (DESCRICAO VARCHAR2(50));
Statement processed
RMAN> INSERT INTO BSS.ASWAN (DESCRICAO) VALUES ('AAAAAAAAA');
Statement processed
RMAN> COMMIT;
Statement processed
RMAN> SELECT * FROM BSS.ASWAN;
DESCRICAO
--------------------------------------------------
AAAAAAAAA
Vamos remover o datafile da tablespace USERS:
RMAN> host "rm /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf";
host command complete
Observando o alert.log do nosso banco, é possível notar que ainda não foi identificado a anomalia:
2021-07-23T20:41:05.705506-03:00
db_recovery_file_dest_size of 5120 MB is 29.37% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2021-07-23T20:41:53.461859-03:00
TABLE AUDSYS.AUD$UNIFIED: ADDED INTERVAL PARTITION SYS_P686 (85) VALUES LESS THAN (TIMESTAMP' 2021-08-01 00:00:00')
Podemos executar o comando VALIDATE DATABASE no RMAN para que o Oracle reconheça o problema:
RMAN> VALIDATE DATABASE;
Starting validate at 2021-07-23:20:43:21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=103 device type=DISK
RMAN-06169: could not read file header for datafile 7 error reason 5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 07/23/2021 20:43:22
RMAN-06056: could not access datafile 7
Alert:
2021-07-23T20:43:22.166904-03:00
Errors in file /oracle/18.0.0/base/diag/rdbms/rmandb/RMANDB/trace/RMANDB_mz00_3727.trc:
ORA-01110: data file 7: '/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf'
ORA-01565: error in identifying file '/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.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
Como os outros datafiles estão preservados, pensamos na possibilidade de restaurar e recuperar apenas o datafile 7. Como os vetores de alteração ainda estão contidos nos Online Redo Logs do ambiente, é possível recuperar este datafile de modo com que o banco abra sem necessidade de resetlogs. Este cenário depende muito mais do ambiente do que você. Caso esses vetores não estivessem mais no ORL, o resultado seria diferente. É como a reação de um paciente quando recebe transfusão de sangue do doador. Depende mais do seu organismo do que do médico.
[oracle@oel8 trace]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jul 23 20:44:41 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> SHU IMMEDIATE;
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RMAN> SHUTDOWN ABORT;
using target database control file instead of recovery catalog
Oracle instance shut down
RMAN> STARTUP MOUNT;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1610612016 bytes
Fixed Size 8658224 bytes
Variable Size 520093696 bytes
Database Buffers 1073741824 bytes
Redo Buffers 8118272 bytes
RMAN> RESTORE DATAFILE 7;
Starting restore at 2021-07-23:20:45:28
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 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 00007 to /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T203914_jhpns3d7_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T203914_jhpns3d7_.bkp tag=TAG20210723T203914
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2021-07-23:20:45:29
RMAN> RECOVER DATAFILE 7;
Starting recover at 2021-07-23:20:45:34
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2021-07-23:20:45:35
RMAN> ALTER DATABASE OPEN;
Statement processed
Realizando o VALIDATE DATABASE:
RMAN> VALIDATE DATABASE;
Starting validate at 2021-07-23:20:46:02
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00002 name=/oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_jhpo4rgv_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 17832 112643 3284859
File Name: /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 74841
Index 0 13649
Other 0 6318
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 14609 15360 2733896
File Name: /oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 150
Index 0 98
Other 0 503
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 22386 93444 3284301
File Name: /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 14138
Index 0 13992
Other 0 42924
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 513 39040 3284861
File Name: /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 38527
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 24697 25600 2790924
File Name: /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 175
Index 0 135
Other 0 593
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 120 2101 3083633
File Name: /oracle/dados/RMANDB/datafile/o1_mf_users_jhpo4rgv_.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1420
Index 0 19
Other 0 521
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 722
Finished validate at 2021-07-23:20:46:18
Nossa tabela de teste após o backup foi recuperada:
RMAN> SELECT * FROM BSS.ASWAN;
DESCRICAO
--------------------------------------------------
AAAAAAAAA
Agora vamos simular uma situação interessante que testei 2 vezes, para ter certeza que era o comportamento do Oracle. Vamos dropar nossa tabela de teste, remover os backups existentes, e fazer o processo completo:
RMAN> DROP TABLE BSS.ASWAN;
Statement processed
RMAN> DELETE BACKUPSET;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
376 347 1 1 AVAILABLE DISK /oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T203914_jhpns3d7_.bkp
377 348 1 1 AVAILABLE DISK /oracle/fra/RMANDB/autobackup/2021_07_23/o1_mf_s_1078691912_jhpnvs16_.bkp
Do you really want to delete the above objects (enter YES or NO)? Y
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T203914_jhpns3d7_.bkp RECID=376 STAMP=1078691955
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_07_23/o1_mf_s_1078691912_jhpnvs16_.bkp RECID=377 STAMP=1078692040
Deleted 2 objects
Gerando novo backup FULL:
RMAN> SHUTDOWN IMMEDIATE;
database closed
database dismounted
Oracle instance shut down
RMAN> STARTUP MOUNT;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1610612016 bytes
Fixed Size 8658224 bytes
Variable Size 520093696 bytes
Database Buffers 1073741824 bytes
Redo Buffers 8118272 bytes
RMAN> BACKUP DATABASE;
Starting backup at 2021-07-23:20:49:19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
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/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00002 name=/oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_jhpo4rgv_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-07-23:20:49:19
channel ORA_DISK_1: finished piece 1 at 2021-07-23:20:49:44
piece handle=/oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T204919_jhpoczkd_.bkp tag=TAG20210723T204919 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2021-07-23:20:49:44
Starting Control File and SPFILE Autobackup at 2021-07-23:20:49:44
piece handle=/oracle/fra/RMANDB/autobackup/2021_07_23/o1_mf_s_1078692524_jhpodrt9_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-07-23:20:49:45
Criando tabela após o backup:
RMAN> ALTER DATABASE OPEN;
Statement processed
RMAN> CREATE TABLE BSS.ASWAN (DESCRICAO VARCHAR2(50));
Statement processed
RMAN> INSERT INTO BSS.ASWAN (DESCRICAO) VALUES ('AAAAAAAAA');
Statement processed
RMAN> COMMIT;
Statement processed
RMAN> SELECT * FROM BSS.ASWAN;
DESCRICAO
--------------------------------------------------
AAAAAAAAA
Deletando novamente o datafile da tablespace USERS:
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name RMANDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 880 SYSTEM YES /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
2 120 TS_HIPO_CATALOG NO /oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
3 730 SYSAUX NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 305 UNDOTBS1 YES /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 200 TS_CORTEX_CATALOG NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 16 USERS NO /oracle/dados/RMANDB/datafile/o1_mf_users_jhpo4rgv_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 69 TEMP 32767 /oracle/dados/RMANDB/datafile/o1_mf_temp_h8nyvt1f_.tmp
RMAN> host 'rm /oracle/dados/RMANDB/datafile/o1_mf_users_jhpo4rgv_.dbf';
host command complete
Identificando o problema:
RMAN> VALIDATE DATABASE;
Starting validate at 2021-07-23:20:51:18
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00002 name=/oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 07/23/2021 20:51:18
ORA-01122: database file 7 failed verification check
ORA-01110: data file 7: '/oracle/dados/RMANDB/datafile/o1_mf_users_jhpo4rgv_.dbf'
ORA-01565: error in identifying file '/oracle/dados/RMANDB/datafile/o1_mf_users_jhpo4rgv_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Agora vamos executar o comando de SWITCH LOGFILE algumas vezes, de modo que esse roteamento mande nossos vetores de alteração (que poderiam ser usados no recover) para o espaço. Como temos 3 grupos de ORL, ao tentar executar o último SWITCH, a instância do Oracle cái. E isso permite a recuperação do datafile:
RMAN> SELECT DISTINCT(GROUP#) FROM V$LOG;
GROUP#
----------
1
2
3
RMAN> ALTER SYSTEM SWITCH LOGFILE;
Statement processed
RMAN> ALTER SYSTEM SWITCH LOGFILE;
Statement processed
RMAN> ALTER SYSTEM SWITCH LOGFILE;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
ORA-01092: ORACLE instance terminated. Disconnection forced
RMAN-03002: failure of sql statement command at 07/23/2021 20:52:22
ORA-03113: end-of-file on communication channel
Process ID: 5171
Session ID: 1 Serial number: 18594
Tentando realizar a recuperação apenas do datafile 7:
[oracle@oel8 trace]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Jul 23 20:52:51 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> STARTUP MOUNT;
Oracle instance started
database mounted
Total System Global Area 1610612016 bytes
Fixed Size 8658224 bytes
Variable Size 520093696 bytes
Database Buffers 1073741824 bytes
Redo Buffers 8118272 bytes
RMAN> RESTORE DATAFILE 7;
Starting restore at 2021-07-23:20:53:13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 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 00007 to /oracle/dados/RMANDB/datafile/o1_mf_users_jhpo4rgv_.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T204919_jhpoczkd_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T204919_jhpoczkd_.bkp tag=TAG20210723T204919
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2021-07-23:20:53:15
RMAN> RECOVER DATAFILE 7;
Starting recover at 2021-07-23:20:53:21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2021-07-23:20:53:22
RMAN> ALTER DATABASE OPEN;
Statement processed
Nossa tabela está salva:
RMAN> SELECT * FROM BSS.ASWAN;
DESCRICAO
--------------------------------------------------
AAAAAAAAA
Sendo assim, vou fazer o mesmo processo (dropar tabela, remover backup, criar tabela, criar backup), mas o Switch vou executar antes de remover o datafile. Assim garantimos que não teremos os vetores de alteração disponíveis para o nosso paciente.
RMAN> DROP TABLE BSS.ASWAN;
Statement processed
RMAN> DELETE BACKUPSET;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
378 349 1 1 AVAILABLE DISK /oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T204919_jhpoczkd_.bkp
379 350 1 1 AVAILABLE DISK /oracle/fra/RMANDB/autobackup/2021_07_23/o1_mf_s_1078692524_jhpodrt9_.bkp
Do you really want to delete the above objects (enter YES or NO)? Y
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T204919_jhpoczkd_.bkp RECID=378 STAMP=1078692559
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_07_23/o1_mf_s_1078692524_jhpodrt9_.bkp RECID=379 STAMP=1078692584
Deleted 2 objects
RMAN> BACKUP DATABASE;
Starting backup at 2021-07-23:20:54:42
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 07/23/2021 20:54:42
RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode
RMAN> SHUTDOWN IMMEDIATE;
database closed
database dismounted
Oracle instance shut down
RMAN> STARTUP MOUNT;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1610612016 bytes
Fixed Size 8658224 bytes
Variable Size 520093696 bytes
Database Buffers 1073741824 bytes
Redo Buffers 8118272 bytes
RMAN> BACKUP DATABASE;
Starting backup at 2021-07-23:20:55:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
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/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00002 name=/oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_jhpombgk_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-07-23:20:55:36
channel ORA_DISK_1: finished piece 1 at 2021-07-23:20:56:01
piece handle=/oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T205535_jhpoqr62_.bkp tag=TAG20210723T205535 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2021-07-23:20:56:01
Starting Control File and SPFILE Autobackup at 2021-07-23:20:56:01
piece handle=/oracle/fra/RMANDB/autobackup/2021_07_23/o1_mf_s_1078692900_jhporkmj_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-07-23:20:56:02
RMAN> CREATE TABLE BSS.ASWAN (DESCRICAO VARCHAR2(50));
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 07/23/2021 20:56:05
ORA-01109: database not open
RMAN> ALTER DATABASE OPEN;
Statement processed
RMAN> CREATE TABLE BSS.ASWAN (DESCRICAO VARCHAR2(50));
Statement processed
RMAN> INSERT INTO BSS.ASWAN (DESCRICAO) VALUES ('AAAAAAAAA');
Statement processed
RMAN> COMMIT;
Statement processed
RMAN> SELECT * FROM BSS.ASWAN;
DESCRICAO
--------------------------------------------------
AAAAAAAAA
RMAN> ALTER SYSTEM SWITCH LOGFILE;
Statement processed
RMAN> ALTER SYSTEM SWITCH LOGFILE;
Statement processed
RMAN> ALTER SYSTEM SWITCH LOGFILE;
Statement processed
RMAN> ALTER SYSTEM SWITCH LOGFILE;
Statement processed
Deletando datafile:
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name RMANDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 880 SYSTEM YES /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
2 120 TS_HIPO_CATALOG NO /oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
3 730 SYSAUX NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 305 UNDOTBS1 YES /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 200 TS_CORTEX_CATALOG NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 16 USERS NO /oracle/dados/RMANDB/datafile/o1_mf_users_jhpombgk_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 69 TEMP 32767 /oracle/dados/RMANDB/datafile/o1_mf_temp_h8nyvt1f_.tmp
RMAN> host 'rm /oracle/dados/RMANDB/datafile/o1_mf_users_jhpombgk_.dbf';
host command complete
RMAN> VALIDATE DATABASE;
Starting validate at 2021-07-23:20:57:32
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00002 name=/oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 07/23/2021 20:57:33
ORA-01122: database file 7 failed verification check
ORA-01110: data file 7: '/oracle/dados/RMANDB/datafile/o1_mf_users_jhpombgk_.dbf'
ORA-01565: error in identifying file '/oracle/dados/RMANDB/datafile/o1_mf_users_jhpombgk_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Tentando recuperar apenas o datafile 7, vimos que o mesmo precisaria de mais vetores de alteração para concluir o recover. Assim a abertura do banco não é possível.
RMAN> SHUTDOWN IMMEDIATE;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 07/23/2021 20:58:12
ORA-03113: end-of-file on communication channel
RMAN> exit
Recovery Manager complete.
[oracle@oel8 trace]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Jul 23 20:58:15 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> STARTUP MOUNT;
Oracle instance started
database mounted
Total System Global Area 1610612016 bytes
Fixed Size 8658224 bytes
Variable Size 520093696 bytes
Database Buffers 1073741824 bytes
Redo Buffers 8118272 bytes
RMAN> RESTORE DATAFILE 7;
Starting restore at 2021-07-23:20:59:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 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 00007 to /oracle/dados/RMANDB/datafile/o1_mf_users_jhpombgk_.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T205535_jhpoqr62_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T205535_jhpoqr62_.bkp tag=TAG20210723T205535
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2021-07-23:20:59:06
RMAN> RECOVER DATAFILE 7;
Starting recover at 2021-07-23:20:59:13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 123 is already on disk as file /oracle/dados/RMANDB/onlinelog/o1_mf_3_h8nyv1ok_.log
archived log for thread 1 with sequence 124 is already on disk as file /oracle/dados/RMANDB/onlinelog/o1_mf_1_h8nytyh4_.log
archived log for thread 1 with sequence 125 is already on disk as file /oracle/dados/RMANDB/onlinelog/o1_mf_2_h8nytykj_.log
RMAN-08187: warning: media recovery until SCN 3489796 complete
Finished recover at 2021-07-23:20:59:14
RMAN> ALTER DATABASE OPEN;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 07/23/2021 20:59:33
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/oracle/dados/RMANDB/datafile/o1_mf_users_jhpoy9bs_.dbf'
Nessas circunstâncias, como não há archived logs (pois o banco está em NOARCHIVELOG mode) e como não há mais os vetores nos Online Redo Logs (pois a quantidade de Switches foi maior que a quantidade de grupos do ambientes), a única saída é restaurar o banco de dados TODO, utilizando o backup FULL realizado, e abrí-lo com o Resetlogs:
RMAN> RESTORE DATABASE;
Starting restore at 2021-07-23:21:03:01
using channel ORA_DISK_1
skipping datafile 7; already restored to file /oracle/dados/RMANDB/datafile/o1_mf_users_jhpoy9bs_.dbf
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 00001 to /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T205535_jhpoqr62_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/RMANDB/backupset/2021_07_23/o1_mf_nnndf_TAG20210723T205535_jhpoqr62_.bkp tag=TAG20210723T205535
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 2021-07-23:21:04:26
RMAN> RECOVER DATABASE;
Starting recover at 2021-07-23:21:04:34
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 123 is already on disk as file /oracle/dados/RMANDB/onlinelog/o1_mf_3_h8nyv1ok_.log
archived log for thread 1 with sequence 124 is already on disk as file /oracle/dados/RMANDB/onlinelog/o1_mf_1_h8nytyh4_.log
archived log for thread 1 with sequence 125 is already on disk as file /oracle/dados/RMANDB/onlinelog/o1_mf_2_h8nytykj_.log
RMAN-08187: warning: media recovery until SCN 3489796 complete
Finished recover at 2021-07-23:21:04:34
RMAN> ALTER DATABASE OPEN RESETLOGS;
Statement processed
Já que conseguimos salvar nosso banco, mesmo com o resetlogs, o mesmo “ganha” um novo incarnation:
RMAN> SELECT INCARNATION#, RESETLOGS_CHANGE#, PRIOR_RESETLOGS_CHANGE#, STATUS FROM V$DATABASE_INCARNATION ORDER BY 1;
INCARNATION# RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE# STATUS
------------ ----------------- ----------------------- -------
1 1 0 PARENT
2 1477662 1 PARENT
3 3489797 1477662 CURRENT
Porém, nossa tabela não foi recuperada pois a mesma foi criada após o backup FULL usado:
RMAN> SELECT * FROM BSS.ASWAN;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 07/23/2021 21:09:01
ORA-00942: table or view does not exist
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