Performing Complete Recovery of the Whole Database to a New Location

Backup & Recovery / 15/07/2022

Hoje vamos simular uma situação onde perdemos os datafiles do nosso banco de dados, e que o local de origem onde os datafiles eram hospedados não pode ser mais utilizado, e por isso, a sua restauração e recuperação deverá ocorrer em outro diretório. Neste cenário vamos assumir que o banco está rodando em archivelog mode. Vamos tomar conhecimento do laboratório:

[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Jul 26 20:19:38 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,LOG_MODE FROM V$DATABASE;
 
NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
RMANDB    READ WRITE           ARCHIVELOG
 
SQL> SHO PARAMETER db_create_file_dest
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /oracle/dados

[oracle@oel8 ~]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Mon Jul 26 20:21:01 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> REPORT SCHEMA;
 
using target database control file instead of recovery catalog
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_jhq1ybl0_.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_jhq1yblw_.dbf
4    305      UNDOTBS1             YES     /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_jhq1ybmg_.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_jhq1ybos_.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 do banco de dados:

RMAN> ALTER SYSTEM SWITCH LOGFILE;
 
Statement processed
 
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
 
 
Starting backup at 2021-07-26:20:24:24
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=26 RECID=185 STAMP=1078950184
input archived log thread=1 sequence=27 RECID=186 STAMP=1078950266
channel ORA_DISK_1: starting piece 1 at 2021-07-26:20:24:26
channel ORA_DISK_1: finished piece 1 at 2021-07-26:20:24:27
piece handle=/oracle/fra/RMANDB/backupset/2021_07_26/o1_mf_annnn_TAG20210726T202426_jhyk1bpw_.bkp tag=TAG20210726T202426 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-07-26:20:24:27
 
Starting backup at 2021-07-26:20:24:27
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/RMANDB/datafile/o1_mf_system_jhq1ybl0_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_jhq1yblw_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_jhq1ybmg_.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_jhq1ybos_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-07-26:20:24:27
channel ORA_DISK_1: finished piece 1 at 2021-07-26:20:25:52
piece handle=/oracle/fra/RMANDB/backupset/2021_07_26/o1_mf_nnndf_TAG20210726T202427_jhyk1d8j_.bkp tag=TAG20210726T202427 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 2021-07-26:20:25:52
 
Starting backup at 2021-07-26:20:25:53
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=28 RECID=187 STAMP=1078950353
channel ORA_DISK_1: starting piece 1 at 2021-07-26:20:25:53
channel ORA_DISK_1: finished piece 1 at 2021-07-26:20:25:54
piece handle=/oracle/fra/RMANDB/backupset/2021_07_26/o1_mf_annnn_TAG20210726T202553_jhyk41bh_.bkp tag=TAG20210726T202553 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-07-26:20:25:54
 
Starting Control File and SPFILE Autobackup at 2021-07-26:20:25:54
piece handle=/oracle/fra/RMANDB/autobackup/2021_07_26/o1_mf_s_1078950354_jhyk42sf_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-07-26:20:25:55

Removendo os datafiles do banco de forma proposital:

RMAN> host 'rm /oracle/dados/RMANDB/datafile/o1_mf_system_jhq1ybl0_.dbf /oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf /oracle/dados/RMANDB/datafile/o1_mf_sysaux_jhq1yblw_.dbf /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_jhq1ybmg_.dbf /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf /oracle/dados/RMANDB/datafile/o1_mf_users_jhq1ybos_.dbf';
 
host command complete
 
RMAN> VALIDATE DATABASE;
 
Starting validate at 2021-07-26:20:27:03
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 07/26/2021 20:27:03
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/oracle/dados/RMANDB/datafile/o1_mf_system_jhq1ybl0_.dbf'
ORA-01565: error in identifying file '/oracle/dados/RMANDB/datafile/o1_mf_system_jhq1ybl0_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

Podemos constatar que apenas um tempfile foi preservado:

[oracle@oel8 datafile]$ ll
total 4276
-rw-r-----. 1 oracle oinstall 72359936 Jul 26 20:18 o1_mf_temp_h8nyvt1f_.tmp
[oracle@oel8 datafile]$ pwd
/oracle/dados/RMANDB/datafile

Criando um diretório diferente que abrigará os novos datafiles:

[oracle@oel8 oracle]$ pwd
/oracle
[oracle@oel8 oracle]$ mkdir RESTORE
[oracle@oel8 oracle]$ cd RESTORE/
[oracle@oel8 RESTORE]$ pwd
/oracle/RESTORE

Montando o banco:

[oracle@oel8 RESTORE]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Jul 26 20:33:22 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 1
ORA-01110: data file 1: '/oracle/dados/RMANDB/datafile/o1_mf_system_jhq1ybl0_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> SHU ABORT;
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
 
Total System Global Area 1610612016 bytes
Fixed Size                  8658224 bytes
Variable Size             520093696 bytes
Database Buffers         1073741824 bytes
Redo Buffers                8118272 bytes
Database mounted.

Com o comando abaixo, temos a definição do nosso diretório onde os datafiles serão hospedados (através do SET NEWNAME), além do comando SWITCH DATAFILE que altera o control file com os novos diretórios e nomes de datafiles:

run {
    SET NEWNAME FOR DATAFILE 1 TO '/oracle/RESTORE/system.dbf';
    SET NEWNAME FOR DATAFILE 2 TO '/oracle/RESTORE/ts_hipo_catalog.dbf';
    SET NEWNAME FOR DATAFILE 3 TO '/oracle/RESTORE/sysaux.dbf';
    SET NEWNAME FOR DATAFILE 4 TO '/oracle/RESTORE/undo.tbs';
    SET NEWNAME FOR DATAFILE 5 TO '/oracle/RESTORE/ts_cortex_catalog.dbf';
    SET NEWNAME FOR DATAFILE 7 TO '/oracle/RESTORE/users.dbf';
    RESTORE DATABASE;
    SWITCH DATAFILE ALL;
    RECOVER DATABASE;
}

Log da execução:

[oracle@oel8 RESTORE]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Mon Jul 26 20:35:13 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, not open)
 
RMAN> run {
        SET NEWNAME FOR DATAFILE 1 TO '/oracle/RESTORE/system.dbf';
        SET NEWNAME FOR DATAFILE 2 TO '/oracle/RESTORE/ts_hipo_catalog.dbf';
        SET NEWNAME FOR DATAFILE 3 TO '/oracle/RESTORE/sysaux.dbf';
        SET NEWNAME FOR DATAFILE 4 TO '/oracle/RESTORE/undo.tbs';
        SET NEWNAME FOR DATAFILE 5 TO '/oracle/RESTORE/ts_cortex_catalog.dbf';
        SET NEWNAME FOR DATAFILE 7 TO '/oracle/RESTORE/users.dbf';
        RESTORE DATABASE;
        SWITCH DATAFILE ALL;
        RECOVER DATABASE;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
Starting restore at 2021-07-26:20:35:39
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 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 00001 to /oracle/RESTORE/system.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/RESTORE/ts_hipo_catalog.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/RESTORE/sysaux.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/RESTORE/undo.tbs
channel ORA_DISK_1: restoring datafile 00005 to /oracle/RESTORE/ts_cortex_catalog.dbf
channel ORA_DISK_1: restoring datafile 00007 to /oracle/RESTORE/users.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/RMANDB/backupset/2021_07_26/o1_mf_nnndf_TAG20210726T202427_jhyk1d8j_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/RMANDB/backupset/2021_07_26/o1_mf_nnndf_TAG20210726T202427_jhyk1d8j_.bkp tag=TAG20210726T202427
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2021-07-26:20:36:15
 
datafile 1 switched to datafile copy
input datafile copy RECID=52 STAMP=1078950975 file name=/oracle/RESTORE/system.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=53 STAMP=1078950975 file name=/oracle/RESTORE/ts_hipo_catalog.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=54 STAMP=1078950975 file name=/oracle/RESTORE/sysaux.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=55 STAMP=1078950975 file name=/oracle/RESTORE/undo.tbs
datafile 5 switched to datafile copy
input datafile copy RECID=56 STAMP=1078950975 file name=/oracle/RESTORE/ts_cortex_catalog.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=57 STAMP=1078950975 file name=/oracle/RESTORE/users.dbf
 
Starting recover at 2021-07-26:20:36:15
using channel ORA_DISK_1
 
starting media recovery
media recovery complete, elapsed time: 00:00:02
 
Finished recover at 2021-07-26:20:36:17

Abrindo o banco de dados:

RMAN> ALTER DATABASE OPEN;
 
Statement processed
 
RMAN> SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;
 
NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
RMANDB    READ WRITE           ARCHIVELOG

É importante notarmos que apesar do banco ter sido recuperado em um diretório diferente do original, o parâmetro “db_create_file_dest” não é alterado com o novo valor. Ou seja, novos datafiles serão criados no diretório antigo, caso o mesmo esteja disponível:

SQL> SHOW PARAMETER db_create_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /oracle/dados

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.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish