Montando seus datafiles em NFS
junho 20, 2012

Há uma maneira correta de montar a NFS em dispositivos NAS para os arquivos de dados Oracle? A resposta é Sim! E existem muitos NFS por aí montados para ambientes Oracle de maneira errada, não de acordo com as exigências da Oracle.

De acordo com o Oracle, as opções necessárias para montar uma NFS para um datafile são as seguintes:

rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp,actimeo=0*

Isso também se faz necessários para NFS onde backups RMAN são executados, evitando assim o erro:

ORA-19504: failed to create file "/backup/DBTST_2_5_U9idjai.bkp"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Restore com RMAN-06023
fevereiro 23, 2012

Oracle sempre surpreendendo, dessa vez com um restore em RMAN. Essa eu peguei sexta-feira passada em um cliente o ambiente era um Oracle 10g 64b em um Red Hat Linux 5.

O erro ocorria assim: Sempre quando tentava realizar um restore da base a seguinte mensagem era mostrada pelo RMAN.

RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore

Já pensei comigo, “poxa não tenho backups dos datafiles 4 e 5″ mais o problema estava ai, eu tinha o backup dos datafiles 4 e 5. Veja abaixo que quando peço para listar os backups do banco ele sinaliza o backup para os datafiles 4 e 5:

RMAN> list backup of database;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
9965    Incr 0  50.13G     DISK        00:49:12     22.02.2012 14:15:11
        BP Key: 8201   Status: AVAILABLE  Compressed: YES  Tag: FULL_DB
        Piece Name: /oracle/backup/FULL_DB_1.bkp
  List of Datafiles in backup set 9965
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 92563519151 22.02.2012 14:15:11 /oradata/system01.dbf
  2    0  Incr 92563519151 22.02.2012 14:15:11 /oradata/df_tst1.dbf
  3    0  Incr 92563519151 22.02.2012 14:15:11 /oradata/sysaux01.dbf
  4    0  Incr 92563519151 22.02.2012 14:15:11 /oradata/datafiledb1.dbf
  5    0  Incr 92563519151 22.02.2012 14:15:11 /oradata/datafiledb2.dbf
  6    0  Incr 92563519151 22.02.2012 14:15:11 /oradata/users01.dbf
  7    0  Incr 92563519151 17.02.2012 14:15:11 /oradata/undotbs01.dbf

Mesmo apresentando no backup que os datafiles 4 e 5 estavam presentes não adiantava, o erro RMAN-06023 aparecia:

RMAN> RESTORE DATABASE;

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=321 devtype=DISK

Starting restore at 22.02.2012 15:15:21

released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 22/02/2012 15:15:22
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore

Realmente o erro era inesperado, pois eu tinha o backup:

RMAN> list backup of datafile 4;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
9965    Incr 0  50.13G     DISK        00:49:12     22.02.2012 14:15:11
        BP Key: 8201   Status: AVAILABLE  Compressed: YES  Tag: FULL_DB
        Piece Name: /oracle/backup/FULL_DB_1.bkp
  List of Datafiles in backup set 9965
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  4    0  Incr 92563519151 22.02.2012 14:15:11 /oradata/datafiledb1.dbf

RMAN> list backup of datafile 5;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
9965    Incr 0  50.13G     DISK        00:49:12     22.02.2012 14:15:11
        BP Key: 8201   Status: AVAILABLE  Compressed: YES  Tag: FULL_DB
        Piece Name: /oracle/backup/FULL_DB_1.bkp
  List of Datafiles in backup set 9965
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  5    0  Incr 92563519151 22.02.2012 14:15:11 /oradata/datafiledb2.dbf

Solução encontrada? Desabilitar a Flash Recovery Area. Isso mesmo, após desabilitar a flash recovery area o problema não aconteceu mais.

Mais porque? Bom …

Quando iniciamos um restore de uma base através de um backup de controlfile em que a flash recovery area está habilita o RMAN executa um implícito crosscheck e cataloga todos os objetos na flash recovery area. Com isso o RMAN vai catalagar QUALQUER objeto que estiver na flash recovery area e se qualquer um desses arquivos pertencer a uma encarnação diferente da ATUAL encarnação do controlfile então muda a atual encarnação para do arquivo que está sendo catalogado.

Isso de acordo com a Oracle evita ao banco de dados restaurar backups que pertencem a uma velha encarnação. Para mais detalhes veja a nota 965122.1 do suporte Oracle.

Após desabilitado a flash recovery area, meu restore funcionou perfeitamente :)

Migrando para ASM utilizando apenas o RMAN
janeiro 16, 2012

O ASM é sem dúvida um dos maiores avanços de gerenciamento de disco e muitas coisas relacionadas a I/O (performance, gerenciamento, etc..). Em um outro post estarei mostrando as vantagens de um ambiente utilizando ASM e como ele é benéfico para o ambiente.

Apesar de todas essas vantagens, ainda hoje existem muitos ambientes em que não é utilizado o ASM. Pensando nisso hoje estaremos vendo como utilizar o RMAN (apenas o RMAN) para realizar uma migração de um filesystem comum como ext3 para ASM.

No nosso caso, estaremos movendo todos os datafiles e tempfiles do banco dbtst para o diskgroup +DGROUP1.

[oracle@oracle11gR3 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 4 13:56:46 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DBTST (DBID=2987787466)

-- Habilitando o Oracle Managed File
RMAN> sql "alter system set db_create_file_dest=''+DGROUP1'' scope=spfile";
using target database control file instead of recovery catalog
sql statement: alter system set db_create_file_dest=''+DGROUP1'' scope=spfile

-- Mudando a localização do controlfile
RMAN> sql "alter system set control_files=''+DGROUP1'' scope=spfile";
using target database control file instead of recovery catalog
sql statement: alter system set control_files=''+DGROUP1'' scope=spfile

RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down

-- Subimos o banco em modo NOMOUNT
RMAN> startup nomount
connected to target database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2233000 bytes
Variable Size                511708504 bytes
Database Buffers             318767104 bytes
Redo Buffers                   6574080 bytes

Nesse momento vamos restaurar o nosso control file atual (‘/u01/app/oracle/oradata/dbtst/control01.ctl’ ou ‘/u01/app/oracle/oradata/dbtst/control02.ctl’) para o diskgroup DGROUP1. O grande lance aqui é que como mudamos o parâmetro control_files para o +DGROUP1 e habilitamos o Oracle Managed Files e temos uma cópia consistência do control files pois baixamos o banco de maneira adequada, simplesmente restaurando o control file atual ele irá gerar no caminho do diskgroup DGROUP1.

Observe a saída do comando:

RMAN> restore controlfile from '/u01/app/oracle/oradata/dbtst/control01.ctl';

Starting restore at 04-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DGROUP1/dbtst/controlfile/current.256.771688895
Finished restore at 04-JAN-12

Como já temos nosso control file restaurado para o DGROUP1, podemos inicializar o banco em mount.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Agora vamos realizar um backup do tipo copy formatando para o diskgroup DGROUP1.

RMAN> BACKUP AS COPY DATABASE DATABASE FORMAT '+DGROUP1';

Starting backup at 04-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/dbtst/system01.dbf
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/data_D-DBTST_I-2987787466_TS-SYSTEM_FNO-1_01mvu2gt tag=TAG20120104T140308 RECID=2 STAMP=771689052
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy

...

input datafile file number=00004 name=/u01/app/oracle/oradata/dbtst/users01.dbf
output file name=+DGROUP1/dbtst/datafile/users.262.771689571 tag=TAG20120104T140308 RECID=13 STAMP=771689575
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 04-JAN-12
channel ORA_DISK_1: finished piece 1 at 04-JAN-12
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/0dmvu33a_1_1 tag=TAG20120104T140308 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 04-JAN-12
channel ORA_DISK_1: finished piece 1 at 04-JAN-12
piece handle=+DGROUP1/dbtst/backupset/2012_01_04/nnsnf0_tag20120104t140308_0.263.771689581 tag=TAG20120104T140308 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 04-JAN-12

Com a image copy do banco de dados feito, o vamos migrar nossos datafiles para esses arquivos de copias de backup. Isso tudo é feito através do comando SWITCH DATABASE TO COPY, que sempre quando utilizado deve-se realizar um recover do banco de dados.

RMAN> SWITCH DATABASE TO COPY; 

RMAN> RECOVER DATABASE;

Agora abrimos o banco e adicionamos um novo tempfile para o tablespace TEMP e removemos o tempfile ‘/u01/app/oracle/oradata/dbtst/temp01.dbf’ antigo. Como nosso OMF está apontando para o diskgroup DGROUP1 não precisamos especificar o caminho do tempfile na sua criação.

RMAN> SQL "ALTER DATABASE OPEN";
RMAN> SQL "ALTER TABLESPACE TEMP ADD TEMPFILE";
RMAN> SQL "ALTER DATABASE TEMPFILE ''/u01/app/oracle/oradata/dbtst/temp01.dbf'' DROP";

Após esses passos seus datafiles e tempfiles estarão todos no ASM.

Restore Datafile de um Image Copy
outubro 3, 2011

Um backup Image copy é mais rápido para o restore e para o DBA tempo é tudo.

Quando um datafile é restaurado de um image copy a estrutura do datafile já existe, é o famoso backup/restore bit-a-bit. O Oracle tem o dever de pegar o bit do backup e colocar no disco, diferente de um backupset que ele tem que montar a estrutura física no disco.

Aqui eu mostro o processo na prática de um backup e restore, de um datafile. Não preciso nem dizer para não realizar esse teste em um ambiente de produção né :)

Prmeiro vamos ao backup do datafile example01.dbf

$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Oct 3 20:43:20 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBTST (DBID=2973952248)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DBTST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               ***     /u01/app/oracle/oradata/dbtst/system01.dbf
2    600      SYSAUX               ***     /u01/app/oracle/oradata/dbtst/sysaux01.dbf
3    200      UNDOTBS1             ***     /u01/app/oracle/oradata/dbtst/undotbs01.dbf
4    2203     USERS                ***     /u01/app/oracle/oradata/dbtst/users01.dbf
5    1024     EXAMPLE              ***     /u01/app/oracle/oradata/dbtst/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    34       TEMP                 32767       /u01/app/oracle/oradata/dbtst/temp01.dbf

RMAN> backup as copy datafile 5 format '/u01/app/oracle/backup/dbtst/example01.bkp';

Starting backup at 03-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/dbtst/example01.dbf
output file name=/u01/app/oracle/backup/dbtst/example01.bkp tag=TAG20111003T204407 RECID=1 STAMP=763591516
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
Finished backup at 03-OCT-11

Vamos agora remover o datafile example01.dbf do disco:

$ rm /u01/app/oracle/oradata/dbtst/example01.dbf

Novamente ao RMAN, vamos aproveitar o image copy e realizar o switch para ele:

RMAN> list copy of datafile 5;

using target database control file instead of recovery catalog
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
1       5    A 03-OCT-11       348805     03-OCT-11      
        Name: /u01/app/oracle/backup/dbtst/example01.bkp
        Tag: TAG20111003T204407

RMAN> sql 'alter database datafile 5 offline';

sql statement: alter database datafile 5 offline

RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy "/u01/app/oracle/backup/dbtst/example01.bkp"

RMAN>  recover datafile 5;

Starting recover at 03-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 03-OCT-11

RMAN>  sql 'alter database datafile 5 online';

sql statement: alter database datafile 5 online
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 3 20:52:48 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$dbfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbtst/system01.dbf
/u01/app/oracle/oradata/dbtst/sysaux01.dbf
/u01/app/oracle/oradata/dbtst/undotbs01.dbf
/u01/app/oracle/oradata/dbtst/users01.dbf
/u01/app/oracle/backup/dbtst/example01.bkp
Otimizando operações RMAN
maio 23, 2011

Todo operação seja ela restore ou um recover do banco de dados por mais simples que seja, o tempo é fundamental. Cada minuto se torna precioso para o DBA.

Uma forma de simples de otimizar, seja ela um restore ou um recover é sincronizar o catalogo do RMAN com os backups existentes em disco.

Veja abaixo nesse ambiente que estive a pouco tempo a quantidade de backups inexistente em disco (EXPIRED) que é mostrado quando executamos o crosscheck.

Backup´s expired são ineficientes ao banco visto que eles não estão mais presentes no disco. Se o seu catalogo não está sincronizado, o rman vai tentar ler e não vai encontrar. Ele vai ter que procurar em todo seu catalog novamente por um piece disponível.

RMAN> crosscheck backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=864 devtype=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/quarta/DB_c-control--20110406-00.bkp recid=15077 stamp=747747400
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/quarta/DB_26420_1.bkp recid=15078 stamp=747747629
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/quarta/DB_26421_1.bkp recid=15079 stamp=747748097
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/quarta/DB_26422_1.bkp recid=15080 stamp=747748464
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/quarta/DB_26423_1.bkp recid=15081 stamp=747748771
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/quarta/DB_26424_1.bkp recid=15082 stamp=747748988
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/quarta/DB_26425_1.bkp recid=15083 stamp=747749316
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/quarta/DB_26426_1.bkp recid=15084 stamp=747749633
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/quarta/DB_26427_1.bkp recid=15085 stamp=747749950
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/quarta/DB_26428_1.bkp recid=15086 stamp=747750258
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/quarta/DB_26429_1.bkp recid=15087 stamp=747750726
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/quarta/DB_26430_1.bkp recid=15088 stamp=747751423
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26619_1.bkp recid=15487 stamp=748632681
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26620_1.bkp recid=15488 stamp=748632779
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-07.bkp recid=15489 stamp=748632894
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26622_1.bkp recid=15490 stamp=748637136
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26623_1.bkp recid=15491 stamp=748637425
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26624_1.bkp recid=15492 stamp=748637664
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-08.bkp recid=15493 stamp=748637675
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26626_1.bkp recid=15494 stamp=748639019
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26627_1.bkp recid=15495 stamp=748639223
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26628_1.bkp recid=15496 stamp=748639560
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26629_1.bkp recid=15497 stamp=748639606
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26631_1.bkp recid=15498 stamp=748639796
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-09.bkp recid=15499 stamp=748639800
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26633_1.bkp recid=15500 stamp=748640417
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26634_1.bkp recid=15501 stamp=748640585
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-0a.bkp recid=15502 stamp=748640767
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26636_1.bkp recid=15503 stamp=748641617
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-0b.bkp recid=15504 stamp=748641689
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26638_1.bkp recid=15505 stamp=748642817
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-0c.bkp recid=15506 stamp=748642826
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26640_1.bkp recid=15507 stamp=748644016
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-0d.bkp recid=15508 stamp=748644026
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26642_1.bkp recid=15509 stamp=748645216
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-0e.bkp recid=15510 stamp=748645238
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26644_1.bkp recid=15511 stamp=748646416
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-0f.bkp recid=15512 stamp=748646458
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26646_1.bkp recid=15513 stamp=748647617
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-10.bkp recid=15514 stamp=748647660
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26648_1.bkp recid=15515 stamp=748648818
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-11.bkp recid=15516 stamp=748648851
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26650_1.bkp recid=15517 stamp=748650017
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-12.bkp recid=15518 stamp=748650070
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26652_1.bkp recid=15519 stamp=748651223
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-13.bkp recid=15520 stamp=748651278
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26654_1.bkp recid=15521 stamp=748652424
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-14.bkp recid=15522 stamp=748652478
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26656_1.bkp recid=15523 stamp=748653622
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110416-15.bkp recid=15524 stamp=748653646
backup piece handle=/backup/rman/sabado/DB_c-control--20110417-03.bkp recid=15534 stamp=748659641
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26668_1.bkp recid=15535 stamp=748660821
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110417-04.bkp recid=15536 stamp=748660846
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26670_1.bkp recid=15537 stamp=748662025
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_c-control--20110417-05.bkp recid=15538 stamp=748662048
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/sabado/DB_26672_1.bkp recid=15539 stamp=748663225

...

crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/domingo/DB_27321_1.bkp recid=17039 stamp=751790542
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/domingo/DB_27322_1.bkp recid=17040 stamp=751790950
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27326_1.bkp recid=17043 stamp=751874607
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27326_2.bkp recid=17044 stamp=751875392
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27326_3.bkp recid=17045 stamp=751876167
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27326_4.bkp recid=17046 stamp=751876962
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27327_1.bkp recid=17047 stamp=751877167
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27327_2.bkp recid=17048 stamp=751877912
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27331_2.bkp recid=17062 stamp=751886845
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27331_3.bkp recid=17063 stamp=751887670
Crosschecked 1619 objects

Para removermos esses backup que não estão mais disponível no disco (EXPIRED).

Usamos o delete expired backup

RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
15077   10343   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_c-control--20110406-00.bkp
15078   10344   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26420_1.bkp
15079   10345   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26421_1.bkp
15080   10346   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26422_1.bkp
15081   10347   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26423_1.bkp
15082   10348   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26424_1.bkp
15083   10349   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26425_1.bkp
15084   10350   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26426_1.bkp
15085   10351   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26427_1.bkp
15086   10352   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26428_1.bkp
15087   10353   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26429_1.bkp
15088   10354   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26430_1.bkp
15089   10355   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26431_1.bkp
15090   10356   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_26432_1.bkp
15091   10357   1   1   EXPIRED     DISK        /backup/rman/quarta/DB_c-control--20110406-01.bkp
15124   10369   1   1   EXPIRED     DISK        /backup/rman/quinta/DB_c-control--20110407-00.bkp
15125   10370   1   1   EXPIRED     DISK        /backup/rman/quinta/DB_26446_1.bkp
15126   10371   1   1   EXPIRED     DISK        /backup/rman/quinta/DB_26447_1.bkp
15127   10372   1   1   EXPIRED     DISK        /backup/rman/quinta/DB_26448_1.bkp
15128   10373   1   1   EXPIRED     DISK        /backup/rman/quinta/DB_26449_1.bkp
15129   10374   1   1   EXPIRED     DISK        /backup/rman/quinta/DB_26450_1.bkp
15130   10375   1   1   EXPIRED     DISK        /backup/rman/quinta/DB_c-control--20110407-01.bkp
15163   10387   1   1   EXPIRED     DISK        /backup/rman/sexta/DB_c-control--20110408-00.bkp
15164   10388   1   1   EXPIRED     DISK        /backup/rman/sexta/DB_26464_1.bkp
15165   10389   1   1   EXPIRED     DISK        /backup/rman/sexta/DB_26465_1.bkp
15166   10390   1   1   EXPIRED     DISK        /backup/rman/sexta/DB_26466_1.bkp
15167   10391   1   1   EXPIRED     DISK        /backup/rman/sexta/DB_26467_1.bkp
15168   10392   1   1   EXPIRED     DISK        /backup/rman/sexta/DB_26468_1.bkp
15169   10393   1   1   EXPIRED     DISK        /backup/rman/sexta/DB_c-control--20110408-01.bkp
15202   10405   1   1   EXPIRED     DISK        /backup/rman/sabado/DB_c-control--20110409-00.bkp
15203   10406   1   1   EXPIRED     DISK        /backup/rman/sabado/DB_26482_1.bkp
15204   10407   1   1   EXPIRED     DISK        /backup/rman/sabado/DB_26483_1.bkp
15205   10408   1   1   EXPIRED     DISK        /backup/rman/sabado/DB_26484_1.bkp
15206   10409   1   1   EXPIRED     DISK        /backup/rman/sabado/DB_26485_1.bkp
15207   10410   1   1   EXPIRED     DISK        /backup/rman/sabado/DB_26486_1.bkp
15208   10411   1   1   EXPIRED     DISK        /backup/rman/sabado/DB_c-control--20110409-01.bkp

backup piece handle=/backup/rman/quarta/DB_27236_3.bkp recid=16839 stamp=751469698
deleted backup piece
backup piece handle=/backup/rman/quarta/DB_27236_4.bkp recid=16840 stamp=751470503
deleted backup piece
backup piece handle=/backup/rman/control.bkp recid=17042 stamp=751799073

Deleted 1358 EXPIRED objects

RMAN> crosscheck backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=871 devtype=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/terca/DB_27208_1.bkp recid=16760 stamp=751356257
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/terca/DB_27208_2.bkp recid=16761 stamp=751357040
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/terca/DB_27208_3.bkp recid=16762 stamp=751357795
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/terca/DB_27208_4.bkp recid=16763 stamp=751358590
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/terca/DB_27209_1.bkp recid=16764 stamp=751358626
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/terca/DB_27209_2.bkp recid=16765 stamp=751359351
crosschecked backup piece: found to be 'AVAILABLE'
...
backup piece handle=/backup/rman/segunda/DB_27329_1.bkp recid=17055 stamp=751882042
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27329_2.bkp recid=17056 stamp=751882824
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27329_3.bkp recid=17057 stamp=751883639
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27330_1.bkp recid=17058 stamp=751884137
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27330_2.bkp recid=17059 stamp=751884950
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27330_3.bkp recid=17060 stamp=751885825
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27331_1.bkp recid=17061 stamp=751886141
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27331_2.bkp recid=17062 stamp=751886845
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/segunda/DB_27331_3.bkp recid=17063 stamp=751887670
Crosschecked 261 objects

De 1619 arquivos backup somente 261 está disponível em disco.

Desconsiderando a retenção do RMAN
maio 10, 2011

Em algumas situações, há a necessidade de guardar determinado backup além da retenção definida pelo RMAN.

Hoje em dia, existem leis que exigem que determinado backup seja armazenado por um tempo definido. É nesse momento que a desconsiderar a retenção do RMAN faz toda diferença.

No exemplo abaixo, estou demonstrado que o backup do dataflie 4 será armazenado em minha retenção do RMAN como 30 dias ignorando minha retenção padrão, o backup do datafile 4 só será considerada obsolete depois de 30 dias.

RMAN> backup datafile 4 keep until time "sysdate+30"; 

Starting backup at 07-APR-11
current log archived

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
backup will be obsolete on date 07-MAY-11
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-APR-11
channel ORA_DISK_1: finished piece 1 at 07-APR-11
piece handle=/oracle/backup/ORCL_1_4.bkp tag=TAG20110407T222922 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
backup will be obsolete on date 07-MAY-11
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-APR-11
channel ORA_DISK_1: finished piece 1 at 07-APR-11
piece handle=/oracle/backup/ORCL_1_5.bkp tag=TAG20110407T222922 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

current log archived
using channel ORA_DISK_1
backup will be obsolete on date 07-MAY-11
archived logs required to recover from this backup will be backed up
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=7 RECID=5 STAMP=747872969
channel ORA_DISK_1: starting piece 1 at 07-APR-11
channel ORA_DISK_1: finished piece 1 at 07-APR-11
piece handle=/oracle/backup/ORCL_1_6.bkp tag=TAG20110407T222922 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
backup will be obsolete on date 07-MAY-11
archived logs required to recover from this backup will be backed up
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
channel ORA_DISK_1: starting piece 1 at 07-APR-11
channel ORA_DISK_1: finished piece 1 at 07-APR-11
piece handle=/oracle/backup/ORCL_1_7.bkp tag=TAG20110407T222922 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-APR-11

 

Veja que até os archives são guardados na retenção e também não são removidos caso seja requisitado a execução os archives obsoletos.
Agora, se você deseja deixar guardado somente o BACKUP ignorando os archives basta executar usar a palavra chave nologs, ou seja ele permite no entando, que o RMAN remova os archives logs que seriam necessário para recuperar o backup.

Validando seus backups
maio 10, 2011

De nada adianta ter um backup, se na hora que você mais precisa ele não vai funcionar.

A principal função do RMAN validation, é procurar por possíveis blocos corrompidos no seu backup (ou no próprio banco de dados) ou até mesmo simular se o seu backup pode ser restaurado.

Você pode usar o VALIDATE comando no RMAN para manualmente checar por físico e lógico blocos corrompidos no seu database. Se o RMAN encontrar qualquer problema ele colocará as informações em V$DATABASE_BLOCK_CORRUPTION, onde ficará descrito os dados do bloco com problema.

Aqui, você pode validar todos os arquivos do seu database, procurando por físicos problemas lógicos.

RMAN> backup validate database;
Starting backup at 26-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 26-FEB-11

Para checar por corrupções lógicas, além da física, execute:

RMAN> backup validate check logical database;
Starting backup at 26-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 26-FEB-11

Alternativamente, você pode validar somente um backupset. No exemplo abaixo estarei fazendo um backup na forma de backupset do datafile 4, depois estarei validando esse backup.

RMAN> backup as backupset datafile 4;
Starting backup at 26-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-FEB-11
channel ORA_DISK_1: finished piece 1 at 26-FEB-11
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backup/ORCL_744133459_8_1. bkp tag=TAG20110226T154419 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-FEB-11
Starting Control File and SPFILE Autobackup at 26-FEB-11
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backup/control_c-1269263368-20110226-00.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-FEB-11
RMAN> list backup of datafile 4;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full    1.89M      DISK        00:00:00    26-FEB-11
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20110226T154419
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backup/ORCL_744133459_8_1.bkp
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 1941885 26-FEB-11 /u01/app/oracle/oradata/orcl/users01.dbf
RMAN> validate backupset 7;using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backup/ORCL_744133459_8_1.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backup/ORCL_744133459_8_1.bkp tag=TAG20110226T154419
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 

Assim como é possível paralelizar operações de backup, habilitando mais de um channel por vez, também é possível habilitar essa opção com o validate.

RMAN> run {
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> backup as backupset database;
5> }
released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=141 devtype=DISK
allocated channel: c2
channel c2: sid=138 devtype=DISK
Starting backup at 26-FEB-11
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
channel c1: starting piece 1 at 26-FEB-11
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel c2: starting piece 1 at 26-FEB-11
channel c2: finished piece 1 at 26-FEB-11
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/0bm5l52e_1_1 tag=TAG20110226T154830 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:41
channel c1: finished piece 1 at 26-FEB-11
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/0am5l52e_1_1 tag=TAG20110226T154830 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:17
Finished backup at 26-FEB-11
Starting Control File and SPFILE Autobackup at 26-FEB-11
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backup/control_c-1269263368-20110226-01.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-FEB-11
released channel: c1
released channel: c2
RMAN> list backup of database;

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Full 601.15M DISK 00:01:13 26-FEB-11
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20110226T154830
Piece Name: /u01/app/oracle/product/10.2.0/db_1/dbs/0am5l52e_1_1
List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1942049 26-FEB-11 /u01/app/oracle/oradata/orcl/system01.dbf
3 Full 1942049 26-FEB-11 /u01/app/oracle/oradata/orcl/sysaux01.dbf
RMAN> run {
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> validate backupset 10;
5> }
allocated channel: c1
channel c1: sid=141 devtype=DISK
allocated channel: c2
channel c2: sid=138 devtype=DISK
channel c1: starting validation of datafile backupset
channel c1: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/dbs/0am5l52e_1_1
channel c1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/0am5l52e_1_1 tag=TAG20110226T154830
channel c1: validation complete, elapsed time: 00:00:15
released channel: c1
released channel: c2

É possível também simular uma situação de RESTORE no banco, se o oracle não apresentar nenhuma mensagem de erro, quer dizer que você pode ficar tranquilo que no momento de fazer o RESTORE do seu banco de dados, o oracle irá conseguir operar sem problema algum.

RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;
Quanto tempo vai demorar o backup?
maio 10, 2011

Uma das formas para descobrir quanto tempo ainda falta para terminar o backup realizado com RMAN, é consultando a viewv$session_longops.

Essa view mostra as vários operações que estão executando por mais de 6 segundos no banco de dados Oracle.

Veja os passos abaixos:

Primeiro vamos relacionar o processo servidor com o channel do RMAN, através do comando SET COMMAND ID

RMAN> run {
2> allocate channel t1 type disk;
3> set command id to ‘rman’;
4> backup datafile 1;
5> release channel t1;
6> }

Agora, basta executar a query, vendo o resultado.

SYS@orcl> SELECT sid, serial#, sofar, totalwork,
2      round(sofar/totalwork*100,2) "% Complete"
3 FROM v$session_longops
4 WHERE opname LIKE 'RMAN:%'
5 AND opname NOT LIKE 'RMAN: aggregate%'
6 AND totalwork != 0;

SID        SERIAL#     SOFAR      TOTALWORK   % Complete
---------- ---------- ---------- ---------- ----------
139          17        13951       62720       22.24

SYS@orcl> /

SID         SERIAL#    SOFAR     TOTALWORK  % Complete
---------- ---------- ---------- ---------- ----------
139          17        24831       62720       39.59

SYS@orcl> /

SID         SERIAL#    SOFAR     TOTALWORK  % Complete
---------- ---------- ---------- ---------- ----------
139          17        62591       62720        99.79