lunes, 3 de septiembre de 2012

RMAN : Como Verificar que tengo un respaldo consistente

El otro día me hicieron una pregunta de que como podía verificar que el respaldo que había tomado . Para esto RMAN tiene unos comandos que nos ayudan a verificar que respaldos tenemos y la integridad de ellos

Todos lo comandos que utilice no van a restaurar los respaldos, solamente lee y los valida, lo que si te debo de decir es que tengas cuidado con los comandos que vas a lanzar, ya que en varios , si la palabra VALIDATE no se encuentra presente, en lugar de validar el respaldo, empieza a restaurar el respaldo.

Aquí esta un resumen de los comandos que vamos a usar y estos son validos para un canal SBT o DISK

  • RESTORE DATABASE PREVIEW ;
  • RESTORE DATABASE VALIDATE;
  • RESTORE ARCHIVELOG FROM sequence xx UNTIL SEQUENCE yy THREAD nn VALIDATE;
  • RESTORE CONTROLFILE VALIDATE;
  • RESTORE SPFILE VALIDATE;
Lo primero que tenemos que hacer es definir el tiempo al que queremos restaurar nuestro respaldo. Una vez definido este tiempo, el primer comando que vamos a usar es RESTORE... PREVIEW, este comando identifica el respaldo necesario para llevar a cabo la operación de restaurar así como los Archived Redo logs necesarios.


RMAN> RUN
2> {
3> set until time "to_date('03-SEP-201222:20:00','dd-mm-yyyyhh24:mi:ss')";
4> allocate channel ch1 device type disk ;
5> RESTORE DATABASE PREVIEW ;
6> }   

executing command: SET until clause

allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK

Starting restore at 03-SEP-2012 22:54:18


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
6       Full    75.34M     DISK        00:00:19     03-SEP-2012 22:01:04
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: TESTDB_HOT_0904_2100
        Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/full/TESTDB_HOT_09032012_1_6_793058445
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1       Full 224235     03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/system01.dbf
  2       Full 224235     03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/sysaux01.dbf
  3       Full 224235     03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/undotbs1_01.dbf
  4       Full 224235     03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/undotbs2_01.dbf
  5       Full 224235     03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/users_01.dbf


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
9       482.00K    DISK        00:00:00     03-SEP-2012 22:01:39
        BP Key: 9   Status: AVAILABLE  Compressed: YES  Tag: TESTDB_ARCH_0904_2100
        Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499

  List of Archived Logs in backup set 9
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    8       222449     03-SEP-2012 21:57:53 224442     03-SEP-2012 22:01:33
  2    4       222452     03-SEP-2012 21:59:38 224448     03-SEP-2012 22:03:17
  1    9       224442     03-SEP-2012 22:01:33 224456     03-SEP-2012 22:01:36
  2    5       224448     03-SEP-2012 22:03:17 224459     03-SEP-2012 22:03:21

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
12      389.50K    DISK        00:00:01     03-SEP-2012 22:21:50
        BP Key: 12   Status: AVAILABLE  Compressed: YES  Tag: TESTDB_ARCH_0904_22_20
        Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/full/0bnka8bt_1_1

  List of Archived Logs in backup set 12
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    10      224456     03-SEP-2012 22:01:36 225574     03-SEP-2012 22:21:01

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
11      25.00K     DISK        00:00:00     03-SEP-2012 22:21:50
        BP Key: 11   Status: AVAILABLE  Compressed: YES  Tag: TESTDB_ARCH_0904_22_20
        Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/full/0cnka8bu_1_1

  List of Archived Logs in backup set 11
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  2    6       224459     03-SEP-2012 22:03:21 225577     03-SEP-2012 22:22:45
Media recovery start SCN is 224235
Recovery must be done beyond SCN 224235 to clear datafile fuzziness
Finished restore at 03-SEP-2012 22:55:15
released channel: ch1

El siguiente paso, que es RESTORE DATABASE VALIDATE, va a leer las piezas del respaldo y si llega a encontrar algún error , va a reportar este error


RMAN> RUN
2> {
3> set until time "to_date('03-SEP-201222:20:00','dd-mm-yyyyhh24:mi:ss')";
4> allocate channel ch1 device type disk ;
5> restore database validate;
6> }

executing command: SET until clause

allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK

Starting restore at 03-SEP-2012 22:51:44

channel ch1: starting validation of datafile backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/full/TESTDB_HOT_09032012_1_6_793058445
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/full/TESTDB_HOT_09032012_1_6_793058445 tag=TESTDB_HOT_0904_2100
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:25
Finished restore at 03-SEP-2012 22:52:38
released channel: ch1

Pero si te fijaste, esto solamente leyó el backupset del respaldo completo, así que hace falta ver si los backupsets de los archivelogs estan integros para hacer en dado caso de ser necesario restaurar tu base de datos, para esto, lo que te recomiendo yo es que del resultado del primer comando, de allí sacar los valores de la secuencias de archive logs que requieres y de una vez que tienes estos valores ,correr el comando RESTORE ARCHIVELOG. . . VALIDATE

RMAN> RUN
2> {
3> allocate channel ch1 type disk;
4> restore archivelog from sequence 8 until sequence 10 thread 1 validate;
5> restore archivelog from sequence 4 until sequence 6 thread 2 validate;
6> }

allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK

Starting restore at 03-SEP-2012 23:15:11

channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499 tag=TESTDB_ARCH_0904_2100
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/full/0bnka8bt_1_1
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/full/0bnka8bt_1_1 tag=TESTDB_ARCH_0904_22_20
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:15:15

Starting restore at 03-SEP-2012 23:15:17

channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499 tag=TESTDB_ARCH_0904_2100
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/full/0cnka8bu_1_1
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/full/0cnka8bu_1_1 tag=TESTDB_ARCH_0904_22_20
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:15:21
released channel: ch1

Ya por ultimo vamos a validar que podemos restaurar el controlfile y nuestro archivo de parámetros binario


RMAN> RUN
2> {
3> allocate channel ch1 type disk;
4> set until time "to_date('03-SEP-201222:20:00','dd-mm-yyyyhh24:mi:ss')";
5> restore controlfile validate;
6> restore spfile validate;
7> }

allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK

executing command: SET until clause

Starting restore at 03-SEP-2012 23:23:14

channel ch1: starting validation of datafile backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-03
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-03 tag=TAG20120903T220143
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:23:15

Starting restore at 03-SEP-2012 23:23:16

channel ch1: starting validation of datafile backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-04
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-04 tag=TAG20120903T222152
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:23:18
released channel: ch1

Conclusión

Estos comandos aunque sencillos, son muy útiles para validar tus respaldos, mi recomendación es que los corras una vez a la semana, ya que no te quieres enfrentar a una situación en donde tengas que restaurar tus respaldo y saber que tenias algún error y no poder hacerlo