Blog do Bruno Murassaki

Olá Pessoal tudo bom?

Bom Hoje irei compartilhar um problema que levei uma semana para descobrir o erro, pois sabemos quando se trata de um Banco de Produção, não podemos sair fazendo Testes de qualquer jeito, pois pode afetar algo no Banco, então todo cuidado é pouco e sempre testar em um ambiente Teste antes de aplicar em Produção.
Um certo dia resolvi implantar um script de Backup Full com RMAN em um cliente, mau sabia o que estava por vir rs.
Homologuei o script do RMAN com um ambiente de Teste parecido com o produção, mas o unico detalhe que não estava igual era o ponto de Montagem do Sistema de Arquivos NFS, até ai tudo ok.
Inclusive este mesmo script tenho ele rodando em um outro cliente Oracle 9i 9.2.0.8.

==================================================
Bom para enterdermos melhor irei explicar o ambiente:
==================================================

- Sistema Operacional: Red Hat Enterprise Linux Advanced Server 4 Update4 32bits

- Versão do Oracle: Oracle 9i 9.2.0.4 32bits

- Estratégia de Backup: Criar Backup Full Database com RMAN utilizando Controlfile, onde a gravação é realizada em outro Servidor Linux através de um mapeamento NFS. Estratégia de Backup adotada devido a falta de espaço em disco local.

==================================================

Bom então vamos lá…. iniciei o script RMAN e…. “PAU”….

Então fui a caça dos erros começando a verificar o alertlog e nada de erros. Nenhum trace foi gerado.

Após isso executei o DEBUG do RMAN mas também não ajudou em nada.

O script do RMAN inicia a execução mas desconecta do nada com a mensagem de erro conforme abaixo:

==================================================

PROBLEMA:
Falha ao executar script RMAN
Recovery Manager: Release 9.2.0.4.0 - Production
RMAN>
Iniciando backup em 05/11/09
usar o arquivo de controle do banco de dados de destino em vez do catálogo de recuperação
canal alocado: ORA_DISK_1
canal ORA_DISK_1: sid=98 devtype=DISK
canal ORA_DISK_1: iniciando conjunto de backups do arquivo de dados full
canal ORA_DISK_1: especificando arquivo(s) de dados no conjunto de backups
fno=00002 name=/disk0/oracle/oradata/orcl/undotbs01.dbf do arquivo de dados de entrada
…………………………………………………………………………
…………………………………………………………………………
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
RMAN-10038: database session for channel ORA_DISK_1 terminated unexpectedly

==================================================

CAUSA:
Ponto de Montagem incorreto devido a falta de Parametros.

mount -t nfs xxx.xxx.xxx.xxx:/diretorio /mnt

==================================================

SOLUÇÃO:
Corrigir o Ponto de Montagem adicionando os parametros que faltam.

Adicionar esta Linha no FSTAB do Linux:

xxx.xxx.xxx.xxx:/diretorio  /mnt/diretorio  nfs rw,bg,hard,nointr, rsize=32768, wsize=32768, tcp, vers=3, timeo=600, actimeo=0 0 0

==================================================

Agora o RMAN roda sem problemas e como podemos ver não tinha nada have com o RMAN e sim o ponto de Montagem do Sistema de Arquivos NFS.

Espero ter ajudado.

Abraço

Bruno Murassaki

Olá Pessoal, hoje irei demonstrar uma solução muito interessante do RMAN que agiliza muito a recuperação de apenas um Usuario (schema) do Banco de Dados. Esta solução foi indicada por um grande amigo Rodrigo Almeida (alphamek). Gostaria ja de agradecer a contribuição do Rodrigo Almeida (alphamek) e do David (Drbs) por terem me ajudado com meus estudos para este Post. Bom, mas vamos lá, este artigo irá ajudar os DBAs que necessitam voltar apenas um Usuario(schema) do Backup RMAN sem necessitar voltar o Backup inteiro. Muitos confundem com recuperação incompleta, mas é um pouco diferente.

Vamos supor que você tenha uma Tablespace EXAMPLE que com tenha varios Usuarios (Schemas) nesta mesma Tablespace e você precise voltar o backup de apenas um único usuario (chamado HR) com apenas um Backup RMAN (Sem Backup Export). Este artigo irá lhe ajudar a desenvolver esta solução.

=================================================
Ambiente:
=================================================

-Objetivo: Recuperar um schema que foi deletado do servidor de produção através do RMAN com TSPITR.
-Versão do Oracle: Oracle 10g 10.2.0.4
-Sistema Operacional: Red Hat Enterprise Linux 5
-Backup: Backup Full Database com RMAN.
-Instância Auxiliar: Preparar uma Instancia Auxiliar para residir no mesmo Servidor que a Instancia Produção(target)

=================================================
Problema: Usuario(Schema) HR removido
=================================================
[oracle@db10g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 18 02:52:19 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> drop user HR cascade;
User dropped.
SQL>
=================================================
Solução: Utilizar RMAN TSPITR - Tablespace Point in Time Recovery
=================================================
Recuperar o schema(HR) que foi deletado do servidor de produção através do RMAN com TSPITR, pois existem outros Schemas como o SCOTT que não podem ser alterados pois residem na mesma Tablespace EXAMPLE.
=================================================
1 - Passo: Criar um arquivo de Senha (Password File) para a instancia auxiliar:
=================================================
orapwd file=$ORACLE_HOME/dbs/orapwaux1 PASSWORD=teste ENTRIES=5
=================================================
2- Passo: Criar um arquivo de inicialização(Parameter File):
=================================================
Criar o arquivo /oracle/app/oradata/aux1/initAux.ora
touch initAux.ora
DB_NAME=db10g
DB_UNIQUE_NAME=tspitr_db10g
CONTROL_FILES=/oracle/app/oradata/aux1/control01.ctl
DB_FILE_NAME_CONVERT=(’/oracle/app/oradata/db10g/’,'/oracle/app/oradata
/aux1/’)
LOG_FILE_NAME_CONVERT=(’/oracle/app/oradata/db10g/’,'/oracle/app/orada
ta/aux1/’)
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
COMPATIBLE =10.2.0.3.0
DB_BLOCK_SIZE=8192
processes=150
pga_aggregate_target=16777216
undo_management=AUTO
undo_tablespace=UNDOTBS1
sga_target=167772160processes=150
pga_aggregate_target=16777216
undo_management=AUTO
undo_tablespace=UNDOTBS1
sga_target=167772160
=================================================
3- Passo: Criar a conectividade do Oracle Net para a Instancia Auxiliar. Para isso utilize a ferramenta NETMGR.
=================================================
- Criar um LISTENER Auxiliar
——————————-
L1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db10g)(PORT = 1522))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db10g)
(ORACLE_HOME = /oracle/app/product/10.2.0/db10g)
(SID_NAME = db10g)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db10g)(PORT = 1521))
)
SID_LIST_L1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = aux1)
(ORACLE_HOME = /oracle/app/product/10.2.0/db10g)
(SID_NAME = aux1)
)
)
————————————————
- Criar um HOSTSTRING AUXILIAR - TNSNAMES.ORA
————————————————
DB10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.239)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db10g)
)
)
AUX1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db10g)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = aux1)
)
)
=================================================
4- Passo: Iniciar a Instancia Auxiliar em Modo NOMOUNT
=================================================
[oracle@db10g /]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 14 22:19:09 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> CONNECT SYS/teste@aux1 AS SYSDBA
Connected to an idle instance.
SQL> STARTUP NOMOUNT PFILE=’/oracle/app/oradata/aux1/initAux.ora’
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 62917928 bytes
Database Buffers 100663296 bytes
Redo Buffers 2924544 bytes
SQL>

=================================================
5- Passo: Conectar com RMAN na Instância Auxiliar:
=================================================
[oracle@db10g /]$ rman target / auxiliary sys/teste@aux1
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Aug 14 22:24:59 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DB10G (DBID=86932143)
connected to auxiliary database: DB10G (not mounted)
RMAN>
=================================================
6- Passo: Executar o restore e recover controlfile e montar o banco de dados clone
=================================================
RMAN>
RUN{
set until time “18-AUG-2009 00:55:00′,’DD-MON-YYYY HH24:MI:SS”;
restore clone controlfile;
sql clone ‘alter database mount clone database’;
sql ‘alter system archive log current’;
sql ‘begin dbms_backup_restore.AutoBackupFlag(FALSE); end;’;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
executing command: SET until clause
using target database control file instead of recovery catalog
Starting restore at 18-AUG-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece
/oracle/backupRMAN/backup_FULL_RMAN_180809/controlfile.ctlc-86932143-
20090818-02
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/backupRMAN/backup_FULL_RMAN_180809/controlfile.ctlc-
86932143-20090818-02 tag=TAG20090818T004430
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oracle/app/oradata/aux1/control01.ctl
Finished restore at 18-AUG-09
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
RMAN>
=================================================
7- Passo: Executar o restore e recover dos datafiles e abrir o banco de dados clone
=================================================
RMAN>
RUN{
set until time “18-AUG-2009 00:55:00′,’DD-MON-YYYY HH24:MI:SS”;
set newname for datafile 1 to “/oracle/app/oradata/aux1/system01.dbf”;
set newname for datafile 2 to “/oracle/app/oradata/aux1/undotbs01.dbf”;
set newname for tempfile 1 to “/oracle/app/oradata/aux1/temp01.dbf”;
set newname for datafile 3 to “/oracle/app/oradata/aux1/sysaux01.dbf”;
set newname for datafile 5 to “/oracle/app/oradata/aux1/example01.dbf”;
switch clone tempfile all;
restore clone datafile 1, 2, 3, 5;
switch clone datafile all;
sql clone “alter database datafile 1 online”;
sql clone “alter database datafile 2 online”;
sql clone “alter database datafile 3 online”;
sql clone “alter database datafile 5 online”;
recover clone database tablespace “EXAMPLE”, “SYSAUX”, “SYSTEM”,
“UNDOTBS1″ delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
sql clone “create tablespace aux_tspitr_tmp datafile
”/oracle/app/oradata/aux1/temp_aux.dbf” size 500K”;
}
7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20>
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
released channel: ORA_AUX_DISK_1
Starting restore at 18-AUG-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/app/oradata/aux1/system01.dbf
restoring datafile 00002 to /oracle/app/oradata/aux1/undotbs01.dbf
restoring datafile 00003 to /oracle/app/oradata/aux1/sysaux01.dbf
restoring datafile 00005 to /oracle/app/oradata/aux1/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece
/oracle/backupRMAN/backup_FULL_RMAN_180809/backupfullDB10G_20090818_0
gkmv3no_1_1.dbf
channel ORA_AUX_DISK_1: restored backup piece 1
piece
handle=/oracle/backupRMAN/backup_FULL_RMAN_180809/backupfullDB10G_200
90818_0gkmv3no_1_1.dbf tag=BKP_FULL
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 18-AUG-09
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 5 online
Starting recover at 18-AUG-09
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 25 is already on disk as file
/archivelog/db10g/1_25_694590577.arc
archive log thread 1 sequence 26 is already on disk as file
/archivelog/db10g/1_26_694590577.arc
archive log thread 1 sequence 27 is already on disk as file
/archivelog/db10g/1_27_694590577.arc
archive log filename=/archivelog/db10g/1_25_694590577.arc thread=1 sequence=25
archive log filename=/archivelog/db10g/1_26_694590577.arc thread=1 sequence=26
archive log filename=/archivelog/db10g/1_27_694590577.arc thread=1 sequence=27
media recovery complete, elapsed time: 00:00:03
Finished recover at 18-AUG-09
database opened
sql statement: create tablespace aux_tspitr_tmp datafile
”/oracle/app/oradata/aux1/temp_aux.dbf” size 500K
RMAN>
————————————————————-
PRONTO BANCO DE DADOS RECUPERADO COM SUCESSO.
————————————————————-
=================================================
8- Passo: Executar o Export apenas do usuario(schema)HR
=================================================
[oracle@db10g installoracle]$ exp userid =\”sys/teste@aux1 as sysdba\”
file=hr.dmp log=hr.log owner=hr
Export: Release 10.2.0.4.0 - Production on Tue Aug 18 03:08:20 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HR
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HR
About to export HR’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HR’s tables via Conventional Path …
. . exporting table COUNTRIES 25 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table DEPARTMENTS 27 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMPLOYEES 107 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table JOBS 19 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table JOB_HISTORY 10 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table LOCATIONS 23 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table REGIONS 4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@db10g installoracle]$
=================================================
9- Passo: Criar o Usuario HR no Banco de Dados Produção novamente
=================================================
[oracle@db10g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 18 03:11:20 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> create user HR identified by hr
default tablespace Example
temporary tablespace TEMP;
User created.
SQL> grant resource to HR;
Grant succeeded.
SQL> grant connect to HR;
Grant succeeded.
=================================================
10- Passo: Executar o Import do usuario(schema)HR
=================================================
[oracle@db10g installoracle]$ imp userid =\”sys/oracle@db10g as sysdba\”
file=hr.dmp log=hr.log FROMUSER=hr TOUSER=hr
Import: Release 10.2.0.4.0 - Production on Tue Aug 18 03:18:41 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing HR’s objects into HR
. . importing table “COUNTRIES” 25 rows imported
. . importing table “DEPARTMENTS” 27 rows imported
. . importing table “EMPLOYEES” 107 rows imported
. . importing table “JOBS” 19 rows imported
. . importing table “JOB_HISTORY” 10 rows imported
. . importing table “LOCATIONS” 23 rows imported
. . importing table “REGIONS” 4 rows imported
About to enable constraints…
Import terminated successfully without warnings.
=================================================
PRONTO USUARIO(SCHEMA)HR RECUPERADO COM SUCESSO !!
=================================================

Espero ter ajudado.

Abraço,

Bruno Murassaki.

Olá pessoal, hoje estarei postando um pouco sobre uma ferramenta que utilizo muito e que estou me especializando. Essa ferramenta é o RMAN, ferramenta de Backup recomendada pela Oracle. Este artigo vai em homenagem a um grande amigo Rodrigo Almeida, na minha opinião o Mestre dos Magos em RMAN, pois ele tem me ajudado muito com meus estudos e apredizados com RMAN. RMAN vale um destaque especial no meu blog, pois quando realmente precisei ele funcionou. Irei contar uma história…………………………..

Um mês atrás eu estava de férias e o DBA junior da empresa ficou como responsável durante minha ausência, com isso todas as atividades relacionadas a Banco de Dados ficou para sua responsabilidade. Na empresa em que trabalhamos possuímos 2 tipos de backup, 1 backup Lógico(Export),1 backup com RMAN FULL e um Standby Manual. Um certo dia um usuario rodou um Update sem a clausula Where. Pronto ai você ja viu o estrago.  Com isso o Usuário aciona o DBA Junior. O DBA Junior sem muita experiência se desespera e ja pensa em voltar o backup com Export, sendo que um FlashBack Table nesta situação resolveria em minutos. Com isso o DBA Junior limpa todo o Usuario(schema) no qual estava o problema, mas esqueceu de verificar o backup Export se tinha realmente executado com sucesso. Como vocês ja devem estar imaginando o backup Export não rodou por falta de espaço no Servidor e pra ajudar piorou a situação. Um certo dia nas minhas férias recebo uma ligação do DBA Junior desesperado porque tinha removido o Usuario(Schema) e não tinha backup. Bom, resumindo minha estratégia para este problema foi:

- Voltar o Backup RMAN em outra maquina e exportar apenas o schema dropado e importar para o Banco de Dados Produção. Tive que agir com esta estratégia porque o Usuario(schema) dropado estava na mesma Tablespace que varios outros Usuario de bases de clientes diferentes.

Devido esta situação neste post estarei demonstrando um EXEMPLO de como voltar de forma ágil e fácil um backup RMAN FULL considerando que ja temos o backup em mãos:

====================================================
CONSIDERANDO O SEGUINTE AMBIENTE:
====================================================

- Banco de Dados: Oracle 10g 10.2.0.1 - Enterprise Edition
- Sistema Operacional: Red Hat Advanced Server 4 Update 4
- Modo de arquivamento: Archivelog Ativo.
- Backup: Backup RMAN FULL (Online, Inconsistente, Hot).
- IMPORTANTE: Ter em mãos o DBID=1207426900

- Objetivo: voltar o Backup até o ultimo ponto de recuperação possível.

====================================================
1- PREPARAR UM NOVO SERVIDOR PARA RESTAURAR O BACKUP
====================================================

- Prepara um novo Servidor com Oracle instalado para restauração do Backup RMAN

- Instalar apenas o Software Oracle

====================================================
2- RESTAURAR O SPFILE
====================================================

RMAN TARGET /
RMAN>SET DBID=1207426900
RMAN>startup nomount
RMAN>restore spfile from ‘/oracle10g/backupRMAN/backup_FULL_RMAN_220609/controlfile.ctlc-1207426900-20090622-00′;
Starting restore at 22-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oracle10g/backupRMAN/backup_FULL_RMAN_220609/controlfile.ctlc-1207426900-20090622-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 22-JUN-09
RMAN> shutdown immediate
Oracle instance shut down

====================================================
3- RESTAURAR O CONTROLFILE
====================================================
RMAN>SET DBID=1207426900

RMAN> startup nomount

database is already started

RMAN> restore controlfile from ‘/oracle10g/backupRMAN/backup_FULL_RMAN_220609/controlfile.ctlc-1207426900-20090622-00′;

Starting restore at 22-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1091 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/oracle10g/oradata/BSM/control01.ctl
output filename=/oracle10g/oradata/BSM/control02.ctl
output filename=/oracle10g/oradata/BSM/control03.ctl
Finished restore at 22-JUN-09

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

====================================================
4- SINCRONIZAR O BACKUP
====================================================

RMAN> CROSSCHECK BACKUP;
RMAN> CROSSCHECK COPY;
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;
RMAN> DELETE EXPIRED BACKUP;
RMAN> delete obsolete device type disk;
RMAN> list backup;

====================================================
5- RESTAURAR O BANCO DE DADOS ORACLE
====================================================

RMAN> restore database;

Starting restore at 22-JUN-09
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /oracle10g/oradata/BSM/undotbs01.dbf
restoring datafile 00005 to /oracle10g/BSM01.dbf
restoring datafile 00006 to /oracle10g/BSM02.dbf
restoring datafile 00007 to /oracle10g/BSM03.dbf
restoring datafile 00008 to /oracle10g/BSM04.dbf
restoring datafile 00009 to /oracle10g/BSM05.dbf
restoring datafile 00020 to /oracle10g/OBM01.dbf
restoring datafile 00021 to /oracle10g/OBM02.dbf
restoring datafile 00024 to /oracle10g/HINT01.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_                                                                                                          RMAN_220609/backupfullBSM_20090622_qpki6ahi_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qpki6ahi_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:17:35
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /oracle10g/oradata/BSM/sysaux01.dbf
restoring datafile 00013 to /oracle10g/oradata/BSM/RMAN01.dbf
restoring datafile 00015 to /oracle10g/BSMLOG02.dbf
restoring datafile 00016 to /oracle10g/oradata/MGA01.dbf
restoring datafile 00017 to /oracle10g/oradata/MGA02.dbf
restoring datafile 00018 to /oracle10g/oradata/ELOP01.dbf
restoring datafile 00019 to /oracle10g/oradata/ELOP02.dbf
restoring datafile 00022 to /oracle10g/oradata/_NOVA01.dbf
restoring datafile 00023 to /oracle10g/oradata/_NOVA02..dbf
restoring datafile 00025 to /oracle10g/BSMLOB01.dbf
restoring datafile 00031 to /oracle10g/oradata/BSM301.dbf
restoring datafile 00038 to /oracle10g/oradata/BSM/GERAL01.dbf
restoring datafile 00054 to /oracle10g/oradata/UNTI.dbf
restoring datafile 00060 to /oracle10g/oradata/BSM/BSMREPLIC.dbf
restoring datafile 00061 to /oracle10g/oradata/_CNAB.dbf
restoring datafile 00068 to /oracle10g/IDX.dbf
restoring datafile 00071 to /oracle10g/oradata/FAT01.dbf
restoring datafile 00085 to /oracle10g/oradata/BSM/USR01.dbf
restoring datafile 00086 to /oracle10g/oradata/BSM/USR02.dbf
restoring datafile 00091 to /oracle10g/BSM19.dbf
restoring datafile 00093 to /oracle10g/oradata/BSM/BSMFINANC.dbf
restoring datafile 00095 to /oracle10g/oradata/BSM/INSTERT.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qqki6c7g_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qqki6c7g_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:09:46
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /oracle10g/oradata/OWERSDF01.dbf
restoring datafile 00026 to /oracle10g/BSM06.dbf
restoring datafile 00027 to /oracle10g/BSM07.dbf
restoring datafile 00028 to /oracle10g/BSM08.dbf
restoring datafile 00029 to /oracle10g/BSM09.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qrki6d2i_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qrki6d2i_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:12:26
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00030 to /oracle10g/BSM10.dbf
restoring datafile 00032 to /oracle10g/HIPTRE02.dbf
restoring datafile 00033 to /oracle10g/ACERT01.dbf
restoring datafile 00062 to /oracle10g/oradata/BSM/WERWER01.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qski6e8h_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qski6e8h_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:04:35
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle10g/oradata/BSM/system01.dbf
restoring datafile 00014 to /oracle10g/BSMLOG01.dbf
restoring datafile 00034 to /oracle10g/ACERT02.dbf
restoring datafile 00035 to /oracle10g/PERT01.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qtki6ek9_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qtki6ek9_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:02:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /oracle10g/oradata/BSM/users01.dbf
restoring datafile 00036 to /oracle10g/PERT02.dbf
restoring datafile 00037 to /oracle10g/BSM_DAD01.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_quki6eqc_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_quki6eqc_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00010 to /oracle10g/oradata/KBSM01.dbf
restoring datafile 00039 to /oracle10g/MWER01.dbf
restoring datafile 00040 to /oracle10g/MWER02.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qvki6etv_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_qvki6etv_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00012 to /oracle10g/oradata/OWER02.dbf
restoring datafile 00075 to /oracle10g/BSM11.dbf
restoring datafile 00076 to /oracle10g/BSM12.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r0ki6f18_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r0ki6f18_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:06:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00052 to /oracle10g/oradata/GWER01.dbf
restoring datafile 00081 to /oracle10g/BSM13.dbf
restoring datafile 00082 to /oracle10g/BSM14.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r1ki6fle_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r1ki6fle_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:05:06
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00053 to /oracle10g/oradata/GWER02.dbf
restoring datafile 00087 to /oracle10g/BSM15.dbf
restoring datafile 00088 to /oracle10g/BSM16.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r2ki6g57_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r2ki6g57_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:06:06
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00074 to /oracle10g/oradata/GWER03.dbf
restoring datafile 00089 to /oracle10g/BSM17.dbf
restoring datafile 00090 to /oracle10g/BSM18.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r3ki6go4_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r3ki6go4_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:06:06
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00092 to /oracle10g/BSM20.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r4ki6hb2_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r4ki6hb2_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:02:15
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00107 to /oracle10g/BSM21.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r5ki6hi3_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r5ki6hi3_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00108 to /oracle10g/BSM22.dbf
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r6ki6hjg_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/backupfullBSM_20090622_r6ki6hjg_1_1.dbf tag=BKP_FULL
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 22-JUN-09

====================================================
6- RECUPERAR O BANCO DE DADOS
====================================================

RMAN> recover database;

Starting recover at 22-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1088 devtype=DISK
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=17486
channel ORA_DISK_1: reading from backup piece /oracle10g/backupRMAN/backup_FULL_RMAN_220609/Archivelog_BSM_r8ki6hl4.arc
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle10g/backupRMAN/backup_FULL_RMAN_220609/Archivelog_BSM_r8ki6hl4.arc tag=TAG20090622T040139
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
archive log filename=/oracle9/1_17486_613563284.arc thread=1 sequence=17486
unable to find archive log
archive log thread=1 sequence=17487
RMAN-00571: =======================================
RMAN-00569: ======= ERROR MESSAGE STACK FOLLOWS ==========
RMAN-00571: =======================================
RMAN-03002: failure of recover command at 06/22/2009 14:07:33
RMAN-06054: media recovery requesting unknown log: thread 1 seq 17487 lowscn 6611212706

====================================================
7- ABRIR O BANCO DE DADOS COM RESETLOGS
====================================================

RMAN> alter database open resetlogs;

database opened

RMAN>

====================================================
PRONTO!! BANCO DE DADOS RECUPERADO COM SUCESSO!!
====================================================

Abraço,

Bruno Murassaki