RMAN – Como restaurar o Banco de Dados e aplicar archivelogs

Neste manual, será mostrado como realizar o restore do backup previamente realizado conforme artigo anteriormente postado.

Obs: Para esta demonstração, não apagaremos o spfile, ele será mantido intacto.

– Conectando com o usuário do Banco de Dados

# su - oracle

– Criando e exportando a variável ORACLE_SID

$ export ORACLE_SID=orcl

– Conectando ao Banco de Dados

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 9 16:33:24 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

– Confirmando que a estrutura de memória do banco de dados está reservada

SQL> show sga;

Total System Global Area 339738624 bytes
Fixed Size 2924112 bytes

-- Realizando o shutdown do Banco de Dados

SQL> shutdown immediate;
 

Database closed.
Database dismounted.
ORACLE instance shut down.

Database Buffers 58720256 bytes
Redo Buffers 5464064 bytes

SQL> shutdown abort;

ORACLE instance shut down

– Saíndo do SQLPLUS

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 – Deletando os datafiles e controlfiles

$ ls -l /u01/app/oracle/oradata/orcl/ 
total 1921220

-rw-r----- 1 oracle oinstall  10371072 Aug 9 16:34 control01.ctl 
-rw-r----- 1 oracle oinstall  52429312 Aug 9 16:32 redo01.log 
-rw-r----- 1 oracle oinstall  52429312 Aug 9 16:34 redo02.log 
-rw-r----- 1 oracle oinstall  52429312 Aug 9 16:32 redo03.log 
-rw-r----- 1 oracle oinstall 796925952 Aug 9 16:33 sysaux01.dbf 
-rw-r----- 1 oracle oinstall 849354752 Aug 9 16:33 system01.dbf 
-rw-r----- 1 oracle oinstall  62922752 Aug 9 00:55 temp01.dbf 
-rw-r----- 1 oracle oinstall 141565952 Aug 9 16:33 undotbs01.dbf 
-rw-r----- 1 oracle oinstall   5251072 Aug 9 16:32 users01.dbf  

$ rm -rf /u01/app/oracle/oradata/orcl/* 
$ ls -l /u01/app/oracle/oradata/orcl/ 
total 0

– Deletando o controlfile

$ ls -l 
/u01/app/oracle/fast_recovery_area/ orcl/control02.ctl   
-rw-r----- 1 oracle oinstall 10371072 Aug  9 16:34  
/u01/app/oracle/fast_recovery_area/ orcl/control02.ctl 

$ rm -rf /u01/app/oracle/fast_recovery_area/ orcl/control02.ctl 
$ ls -l /u01/app/oracle/fast_recovery_area/ orcl/control02.ctl   

ls: cannot access /u01/app/oracle/fast_recovery_area/ orcl/control02.ctl: No such file or directory

 – Conectando-se ao RMAN

$ rman target / 

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Aug 9 16:42:26 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. connected to target database (not started)

— Inicializando o Banco de Dados em  modo nomount

RMAN> startup nomount;

Oracle instance started

Total System Global Area 339738624 bytes
Fixed Size 2924112 bytes
Variable Size 260047280 bytes
Database Buffers 71303168 bytes
Redo Buffers 5464064 bytes

– Restaurando o Controlfie

RMAN> restore controlfile; 
Starting restore at 09-AUG-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 device type=DISK 
RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: =========================================================== 
RMAN-03002: failure of restore command at 08/09/2015 16:43:19 
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

Veja que os parâmetros do RMAN estão como default novamente, isso ocorre porque como não estamos utilizando um catalogo, os parâmetros alterados estão guardados no antigo controlfile, como não o restauramos até o momento, precisamos informar o caminho completo do autobackup para restaurar o controlfile.

– Restaurando o Controlfile

RMAN> restore controlfile from '/backup/autobackup/c-1411380092-20150808-00';

Starting restore at 09-AUG-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl

Finished restore at 09-AUG-15

– Alterando o modo do Banco de Dados para mount

RMAN> alter database mount;

Statement processed

released channel: ORA_DISK_1

– Verificando os parâmetros do RMAN

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/autobackup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backup/repositorio/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/ 12.1.0.2/db_1/dbs/snapcf_orcl.f'; # default

Agora, podemos notar que as alterações feitas anteriormente retornaram.

– Realizando o Restore do Banco de Dados

RMAN> restore database;

Starting restore at 09-AUG-15
Starting implicit crosscheck backup at 09-AUG-15

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
Crosschecked 3 objects

Finished implicit crosscheck backup at 09-AUG-15

Starting implicit crosscheck copy at 09-AUG-15
using channel ORA_DISK_1
Finished implicit crosscheck copy at 09-AUG-15
searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/ 2015_08_09/o1_mf_1_4_bwhbp23p_.arc

using channel ORA_DISK_1

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 /u01/app/oracle/ oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/ oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/ oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/ oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /backup/ repositorio/05qe4730_1_1
channel ORA_DISK_1: piece handle=/backup/repositorio/ 05qe4730_1_1 tag=TAG20150808T214711
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:06

Finished restore at 09-AUG-15

– Realizando o Recover

RMAN> recover database; 

Starting recover at 09-AUG-15

using channel ORA_DISK_1
starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/ 2015_08_08/o1_mf_1_3_bwf8vsvl_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/ 2015_08_09/o1_mf_1_4_bwhbp23p_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/ 2015_08_08/o1_mf_1_3_bwf8vsvl_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/ 2015_08_09/o1_mf_1_4_bwhbp23p_.arc thread=1 sequence=4
unable to find archived log
archived log thread=1 sequence=5

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/09/2015 16:47:18
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 5 and starting SCN of 3025701

RMAN> list backup of archivelog sequence=5;

specification does not match any backup in the repository

O erro acima, refere-se a impossibilidade do RMAN de restaurar a sequencia 5 do archivelog, porém, se tentarmos listar através do RMAN o archivelog que contém a sequência 5, não encontraremos, isso significa que o recover foi realizado até o fim, garantindo a integridade dos dados e já podemos abrir o Banco de Dados.

– Abrindo o Banco de Dados com a opção resetlogs.

RMAN> alter database open resetlogs;

Statement processed

-- Saindo do RMAN

RMAN> exit
Recovery Manager complete.
$

Restore realizado com sucesso!!!

No próximo artigo, será mostrado como criar um catalogo, como conectar-se a ele através do RMAN.