Blog Flávio Soares
Oracle, Database, Data Guard, RAC, Performance, Exadata, ASM, Goldengate, Troubleshooting
June 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
February 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 
January 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.
October 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
May 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.
May 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.
May 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;
May 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
