Como restaurar um Banco de Dados – Guia passo a passo

Como restaurar um Banco de Dados

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 Jul 12 22:33:15 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

— Dropando o usuário préviamente criado e suas dependências

SQL> drop user teste cascade;

User dropped.

— Confirmando que o usuário teste não existe mais

SQL> select username from dba_users where username = 'TESTE';

no rows selected

— Confirmando que a tabela teste_rman não existe mais

SQL> select table_name from dba_tables where owner = 'TESTE';

no rows selected

— Verificando o caminho dos controlfiles

SQL> set linesize 999;

SQL> set pagesize 999;

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
control_files                        string      /u01/app/oracle/oradata/orcl/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 fast_recovery_area/orcl/contro
                                                 l02.ctl

Obs: Guardar estas informações, elas serão utilizadas posteriormente

— Verificando o caminho dos datafiles

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf

Obs:Guardar estas informações, elas serão utilizadas posteriormente

— Verificando o caminho dos online redo logfiles

SQL> select MEMBER  from v$logfile;

MEMBER
-------------------------------------------------------------

/u01/app/oracle/oradata/orcl/redo03.log

/u01/app/oracle/oradata/orcl/redo02.log

/u01/app/oracle/oradata/orcl/redo01.log

Obs:Guardar estas informações, elas serão utilizadas posteriormente

— Realizando o shutdown do Banco de Dados

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

— Desconectando do Banco de Dados 

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

— Listando os datafiles, online redo logfiles e um controlfile

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

total 1895312

-rw-r----- 1 oracle oinstall  10043392 Jul 12 22:50 control01.ctl

-rw-r----- 1 oracle oinstall  52429312 Jul 12 22:38 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Jul 12 22:38 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Jul 12 22:50 redo03.log

-rw-r----- 1 oracle oinstall 775954432 Jul 12 22:50 sysaux01.dbf

-rw-r----- 1 oracle oinstall 838868992 Jul 12 22:50 system01.dbf

-rw-r----- 1 oracle oinstall  62922752 Jul 12 22:40 temp01.dbf

-rw-r----- 1 oracle oinstall 141565952 Jul 12 22:50 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Jul 12 22:50 users01.dbf

— Deletando os datafiles, online redo logfiles e um controlfile

$ rm -rf /u01/app/oracle/oradata/orcl/*

— Constatando que todos os arquivos foram apagados

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

total 0

— Listando o segundo  controlfile

$ ls -l  /u01/app/oracle/fast_recovery_area/orcl/

total 9808

-rw-r----- 1 oracle oinstall 10043392 Jul 12 22:56 control02.ctl

— Deletando o segundo controlfile

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

— Constatando que o segundo controlfile foi apagado

$ ls -l  /u01/app/oracle/fast_recovery_area/orcl/

total 0

— Conectando ao RMAN sem utilizar um catalogo

$ rman target /  

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jul 12 22:59:24 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)

— Tentando restaurar o controlfile

RMAN> restore controlfile;  

Starting restore at 12-JUL-15

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/12/2015 22:59:43

RMAN-12010: automatic channel allocation initialization failed

RMAN-06403: could not obtain a fully authorized session

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3640

Additional information: 644371931

Obs: O RMAN detectou que não havia estrutura de memória do Banco de Dados  para realizar o restore.

— 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

— Tentando restaurar o controlfile

RMAN> restore controlfile;

Starting restore at 12-JUL-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=23 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/12/2015 23:00:12

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

Obs: O erro acima ocorreu porque o parâmetro RMAN CONTROLFILE AUTOBACKUP está configurado como OFF, neste caso, o controlfile e também o spfile são salvos juntamente como os datafiles, caso contrário, o controlfile seria salvo separadamente

Para restaurar o controlfiles, devemos encontrar dentro do backup, o arquivo gerado pelo RMAN em que o controlfile foi salvo, para isso, vamos até o display da saída do backup localizar o arquivo.

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 12-JUL-15

channel ORA_DISK_1: finished piece 1 at 12-JUL-15

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_07_12/o1_mf_ncsnf_TAG20150712T222106_bt64rrgf_.bkp tag=TAG20150712T222106 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Aí está, agora, o comando de restore será executado utilizando o arquivo onde o controlfile foi salvo.

— Restaurando o controlfile

RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_07_12/o1_mf_ncsnf_TAG20150712T222106_bt64rrgf_.bkp';

Starting restore at 12-JUL-15

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

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 12-JUL-15

— Tentando restaurar o Banco de Dados

RMAN> restore database;

Starting restore at 12-JUL-15

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/12/2015 23:02:53

ORA-01507: database not mounted

Obs: Para a realização do restore, o Banco de Dados precisa das informações do controlfile, para isso, ele deve estar no modo MOUNT.

— Alterando o modo do Banco de Dados para MOUNT

RMAN> alter database mount;

Statement processed

released channel: ORA_DISK_1

— Realizando o restore

RMAN> restore database;

Starting restore at 12-JUL-15

Starting implicit crosscheck backup at 12-JUL-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=23 device type=DISK

Crosschecked 1 objects

Finished implicit crosscheck backup at 12-JUL-15

Starting implicit crosscheck copy at 12-JUL-15

using channel ORA_DISK_1

Finished implicit crosscheck copy at 12-JUL-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/backupset/2015_07_12/o1_mf_ncsnf_TAG20150712T222106_bt64rrgf_.bkp
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 /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T222106_bt64n3nm_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T222106_bt64n3nm_.bkp tag=TAG20150712T222106

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:16

Finished restore at 12-JUL-15

— Realizando o Recover

Obs: Como o backup foi realizado de forma consistente, não haveria a necesidade do recover, pois não há archive logs para serem aplicados, este comando foi executado para fins de conhecimento.

RMAN> recover database;

Starting recover at 12-JUL-15

using channel ORA_DISK_1

starting media recovery


RMAN-08187: WARNING: media recovery until SCN 2138806 complete

Finished recover at 12-JUL-15

— Abrindo o Banco de Dados com a opção RESETLOGS

RMAN> alter database open resetlogs;
Statement processed

-- Desconectando do RMAN

RMAN> exit

Recovery Manager complete.

Para validar o restore, será verificado se a tabela teste_rman foi restaurada.

— Conectar ao Banco de Dados

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 12 23:17:32 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

SQL> set linesize 999;
SQL> set pagesize 999;

— Confirmar se a tabela foi restaurada juntamente com o Banco de Dados

SQL> select TABLE_NAME from dba_tables where owner = 'TESTE';

TABLE_NAME
-------------------------------------------------------------
TESTE_RMAN

SQL> select * from teste.TESTE_RMAN;

COL1       COL2
---------- ----------
reg_1a     reg_1b
reg_2a     reg_2b
reg_3a     reg_3b

— Desconectar do Banco de Dados

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

$

-- Constatando que os datafiles, online redo log files e controlfiles foram restaurados

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

total 1895312

-rw-r----- 1 oracle oinstall  10043392 Jul 01 35:50 control01.ctl

-rw-r----- 1 oracle oinstall  52429312 Jul 01 35:38 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Jul 01 35:38 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Jul 01 35:50 redo03.log

-rw-r----- 1 oracle oinstall 775954432 Jul 01 35:50 sysaux01.dbf

-rw-r----- 1 oracle oinstall 838868992 Jul 01 35:50 system01.dbf

-rw-r----- 1 oracle oinstall  62922752 Jul 12 22:40 temp01.dbf

-rw-r----- 1 oracle oinstall 141565952 Jul 12 22:50 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Jul 12 22:50 users01.dbf

No próximo artigo, será realizado um backup inconsistente e também serão alterados alguns parâmetros do RMAN para facilitar a execução do backup e do restore.