RMAN – Command LIST and examples – Empowering the Humanity with technology

Wireless Information System for Emergency Resp... — Image via Wikipedia

Cuando tenemos un catalogo o realizamos solo copias locales, hay un comando de RMAN que nos permite listar Backup sets (Copias de Seguridad) , Imagenes y otras copias como spfile, archivelogs o control file.

El comando LIST nos ayuda también a poder ver con que contamos en nuestro catalogo, y de esa manera ver que cosas son obsoletas y poder aplicar o un DELETE, para hacer una depuración que no esta implicada en la deletion policy.

También lo podemos aplicar para trabajar en conjunto con el comando CROSSCHECK que compara lo existente en el CATALOG con lo existente fisico en DISCO o TAPE.

Otras utildades en las que podemos emplearlo:

Conocer cuales son las copias de seguridad que tenemos o no disponibles, en el caso de querer realizar un restore a futuro , o en lo immediato ante la perdida de por ejemplo, un DATFILE.
Tener un registro a modo de inventario como DATAFILE, BACKUP SET, CONTROLFILE, SPFILE.
Copias de SEGURIDAD diarias , distinguidas por la manera en que las etiquetamos, Ej. BACKUP_HOT_DIARIO_%d_%h , BKP_DIARIO_DATAFILE_%d_%h
Scripts almacenados en el catalogo.
Incarnations de alguna base especifica.

Una vez que comentamos que utilidades podemos aplicar , (Es obvio que ustedes podran “sacarle mucho más provecho”) mostraremos unos ejemplos.

Comencemos.

LIST BACKUP: Nos lista en un modo verbose todos los backups que poseemos.

RMAN>  LIST BACKUP; 
  List of Archived Logs in backup set 550
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2766    642841579  14-JUN-10 642958941  14-JUN-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
551     6.72M      DISK        00:00:03     14-JUN-10
        BP Key: 547   Status: AVAILABLE  Compressed: YES  Tag: TAG20100614T155444
        Piece Name: /u03/rman_database_backup/th_1_seq_2767_610_1.logs

  List of Archived Logs in backup set 551
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2767    642958941  14-JUN-10 642986704  14-JUN-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
552     43.62M     DISK        00:00:08     15-JUN-10
        BP Key: 548   Status: EXPIRED  Compressed: YES  Tag: TAG20100615T122149
        Piece Name: /u03/rman_database_backup/th_1_seq_2766_611_1.logs

  List of Archived Logs in backup set 552
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2766    642841579  14-JUN-10 642958941  14-JUN-10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
553     Full    5.02G      DISK        00:13:50     15-JUN-10
        BP Key: 554   Status: AVAILABLE  Compressed: YES  Tag: BACKUP_DIARIO_DBF
        Piece Name: /u03/rman_database_backup/20100615_PROAPP_t721745491_s612_p1_dbf
  List of Datafiles in backup set 553
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/system.273.721748867
  2       Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/undotbs1.270.721748865
  3       Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/sysaux.268.721748867
  4       Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/users.283.721748985
  5       Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/undotbs2.267.721748865
  6       Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/undotbs3.266.721748867
  7       Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/SUC_dat.297.721748951
  8       Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/SUC_idx.272.721748889
  9       Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/AB_dat.260.721748857
  10      Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/AB_idx.269.721748863
  11      Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/undotbs4.263.721748865
  12      Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/TO_dat.265.721748981
  13      Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/TO_idx.282.721748983
  14      Full 643422534  15-JUN-10 +PROAPP_DG1/PROAPP/datafile/monitor_dat.271.721748967

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
554     Full    1.72M      DISK        00:00:03     15-JUN-10
        BP Key: 555   Status: AVAILABLE  Compressed: YES  Tag: BACKUP_DIARIO_DBF
        Piece Name: /u03/rman_database_backup/20100615_PROAPP_t721746326_s613_p1_dbf
  Control File Included: Ckp SCN: 643427088    Ckp time: 15-JUN-10
  SPFILE Included: Modification time: 15-JUN-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
555     230.91M    DISK        00:00:35     15-JUN-10
        BP Key: 556   Status: AVAILABLE  Compressed: YES  Tag: BACKUP_DIARIO_ARC
        Piece Name: /u03/rman_database_backup/20100615_PROAPP_t721746345_s614_p1_arc

  List of Archived Logs in backup set 555
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2766    642841579  14-JUN-10 642958941  14-JUN-10
  1    2767    642958941  14-JUN-10 642986704  14-JUN-10
  1    2768    642986704  14-JUN-10 642987249  14-JUN-10
  1    2769    642987249  14-JUN-10 642987293  14-JUN-10
  1    2770    642987293  14-JUN-10 642987630  14-JUN-10
  1    2771    642987630  14-JUN-10 642988426  14-JUN-10
  1    2772    642988426  14-JUN-10 643000051  14-JUN-10
  1    2773    643000051  14-JUN-10 643000054  14-JUN-10
  1    2774    643000054  14-JUN-10 643000062  14-JUN-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
556     236.84M    DISK        00:00:38     15-JUN-10
        BP Key: 557   Status: AVAILABLE  Compressed: YES  Tag: BACKUP_DIARIO_ARC
        Piece Name: /u03/rman_database_backup/20100615_PROAPP_t721746382_s615_p1_arc

  List of Archived Logs in backup set 556
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2789    643095940  14-JUN-10 643319894  15-JUN-10
  1    2790    643319894  15-JUN-10 643322466  15-JUN-10
  1    2791    643322466  15-JUN-10 643401752  15-JUN-10
  1    2792    643401752  15-JUN-10 643402211  15-JUN-10
  2    2135    642841956  14-JUN-10 642958937  14-JUN-10
  2    2136    642958937  14-JUN-10 642986693  14-JUN-10
  2    2137    642986693  14-JUN-10 642987254  14-JUN-10
  2    2138    642987254  14-JUN-10 642987283  14-JUN-10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
557     Full    1.72M      DISK        00:00:01     15-JUN-10
        BP Key: 553   Status: AVAILABLE  Compressed: YES  Tag: BACKUP_DIARIO_CTL
        Piece Name: /u03/rman_database_backup/20100615_PROAPP_t721746430_s616_p1_ctl
  Control File Included: Ckp SCN: 643427505    Ckp time: 15-JUN-10

RMAN>

LIST BACKUP SUMMARY: Nos muestra un sumario de todos los backup. Despues podemos ahondar en detalle usando el numero que nos da el PC KEY y ver el contenido especifico de un backup.

RMAN> LIST BACKUP SUMMARY; 
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
222     B  F  X DISK        01-AUG-08       1       1       YES        BACKUP_DIARIO_DBF
224     B  F  X DISK        01-AUG-08       1       1       YES        BACKUP_DIARIO_CTL
226     B  F  X DISK        09-AUG-08       1       1       YES        BACKUP_DIARIO_DBF
230     B  F  X DISK        11-AUG-08       1       1       YES        BACKUP_DIARIO_DBF
232     B  F  X DISK        11-AUG-08       1       1       YES        BACKUP_DIARIO_CTL
233     B  F  X DISK        11-AUG-08       1       1       YES        TAG20080811T095053
234     B  F  X DISK        15-AUG-08       1       1       YES        BACKUP_DIARIO_DBF
235     B  F  X DISK        15-AUG-08       1       1       YES        BACKUP_DIARIO_DBF
237     B  F  X DISK        15-AUG-08       1       1       YES        BACKUP_DIARIO_CTL
238     B  F  X DISK        15-AUG-08       1       1       YES        TAG20080815T111824
455     B  A  X DISK        28-MAR-10       1       1       YES        APP_DIARIO_20100328.0401
456     B  A  X DISK        28-MAR-10       1       1       YES        APP_DIARIO_20100328.0401
457     B  A  X DISK        28-MAR-10       1       1       YES        APP_DIARIO_20100328.0401
458     B  F  X DISK        04-APR-10       1       1       YES        APP_DIARIO_20100404.0401
459     B  F  X DISK        04-APR-10       1       1       YES        APP_DIARIO_20100404.0401
460     B  F  X DISK        04-APR-10       1       1       YES        APP_DIARIO_20100404.0401
461     B  F  X DISK        04-APR-10       1       1       YES        APP_DIARIO_20100404.0401
[...]

COMANDO LIST BACKUP BY FILE : Podemos ver una lista de todas la piezas de backup que tenemos disponibles, por datafile, spfile, controlfile , etc.

RMAN> LIST BACKUP BY FILE; 
List of Datafile Backups
========================

File Key     TY LV S Ckp SCN    Ckp Time  #Pieces #Copies Compressed Tag
---- ------- -  -- - ---------- --------- ------- ------- ---------- ---
1    553     B  F  A 643422534  15-JUN-10 1       1       YES        BACKUP_DIARIO_DBF
     540     B  F  X 642626992  13-JUN-10 1       1       YES        APP_DIARIO_20100613.0401
     532     B  F  X 639214866  06-JUN-10 1       1       YES        APP_DIARIO_20100606.0401
     524     B  F  X 636427949  30-MAY-10 1       1       YES        APP_DIARIO_20100530.0401
     516     B  F  X 633306898  23-MAY-10 1       1       YES        APP_DIARIO_20100523.0401
     508     B  F  X 629300620  16-MAY-10 1       1       YES        APP_DIARIO_20100516.0401
     500     B  F  X 626545802  09-MAY-10 1       1       YES        APP_DIARIO_20100509.0401
[...]

List of SPFILE Backups
======================

Modification Time BS Key  S #Pieces #Copies Compressed Tag
----------------- ------- - ------- ------- ---------- ---
15-JUN-10         554     A 1       1       YES        BACKUP_DIARIO_DBF
12-JUN-10         542     X 1       1       YES        APP_DIARIO_20100613.0401
05-JUN-10         534     X 1       1       YES        APP_DIARIO_20100606.0401
29-MAY-10         526     X 1       1       YES        APP_DIARIO_20100530.0401
22-MAY-10         518     X 1       1       YES        APP_DIARIO_20100523.0401
[...]

List of Control File Backups
============================

CF Ckp SCN Ckp Time  BS Key  S #Pieces #Copies Compressed Tag
---------- --------- ------- - ------- ------- ---------- ---
643427505  15-JUN-10 557     A 1       1       YES        BACKUP_DIARIO_CTL
643427088  15-JUN-10 554     A 1       1       YES        BACKUP_DIARIO_DBF
642628357  13-JUN-10 544     X 1       1       YES        APP_DIARIO_20100613.0401
642627478  13-JUN-10 541     X 1       1       YES        APP_DIARIO_20100613.0401
639216475  06-JUN-10 536     X 1       1       YES        APP_DIARIO_20100606.0401
639215406  06-JUN-10 533     X 1       1       YES        APP_DIARIO_20100606.0401
636429386  30-MAY-10 528     X 1       1       YES        APP_DIARIO_20100530.0401
636428468  30-MAY-10 525     X 1       1       YES        APP_DIARIO_20100530.0401
633308135  23-MAY-10 520     X 1       1       YES        APP_DIARIO_20100523.0401
633307405  23-MAY-10 517     X 1       1       YES        APP_DIARIO_20100523.0401
629301697  16-MAY-10 512     X 1       1       YES        APP_DIARIO_20100516.0401
[...]

List of Archived Log Backups
============================

Thrd Seq     Low SCN    Low Time  BS Key  S #Pieces #Copies Compressed Tag
---- ------- ---------- --------- ------- - ------- ------- ---------- ---
1    797     265947768  04-MAR-09 303     X 1       1       YES        TAG20090309T130629
1    798     265949464  04-MAR-09 303     X 1       1       YES        TAG20090309T130629
1    799     265950896  04-MAR-09 303     X 1       1       YES        TAG20090309T130629
1    800     265952365  04-MAR-09 303     X 1       1       YES        TAG20090309T130629
1    801     265953789  04-MAR-09 303     X 1       1       YES        TAG20090309T130629
1    802     265955195  04-MAR-09 303     X 1       1       YES        TAG20090309T130629
1    804     265959590  04-MAR-09 303     X 1       1       YES        TAG20090309T130629
1    805     265960623  04-MAR-09 303     X 1       1       YES        TAG20090309T130629
1    809     270075431  05-MAR-09 303     X 1       1       YES        TAG20090309T130629
[...]

List of Control File Backups
============================

CF Ckp SCN Ckp Time  BS Key  S #Pieces #Copies Compressed Tag
---------- --------- ------- - ------- ------- ---------- ---
274892642  09-MAR-09 308     X 1       1       YES        TAG20090309T164219
238576222  29-NOV-08 286     X 1       1       YES        TAG20081129T183326
181135793  15-AUG-08 238     X 1       1       YES        TAG20080815T111824
177539881  11-AUG-08 233     X 1       1       YES        TAG20080811T095053

RMAN>

LIST COPY DATABASE ARCHIVELOG ALL: Nos mostrara todas las copias disponibles de LOGS disponibles.

RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;
List of Archived Log Copies

Key     Thrd Seq     S Low Time  Name

------- ---- ------- - --------- ----

11      1    2715    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2715.3300.721342853
12      1    2716    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2716.1824.721345739
13      1    2717    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2717.2476.721345763
15      1    2718    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2718.3271.721345801
16      1    2719    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2719.861.721345839
17      1    2720    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2720.3605.721347465
19      1    2721    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2721.2686.721349891
20      1    2722    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2722.562.721349903
21      1    2723    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2723.3207.721349915
23      1    2724    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2724.2510.721349929
24      1    2725    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2725.3686.721349941
25      1    2726    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2726.3718.721349963
27      1    2727    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2727.2792.721349975
28      1    2728    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2728.1687.721349989
29      1    2729    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2729.919.721350003
31      1    2730    A 10-JUN-10 +PROAPP_DG2/PROAPP/archivelog/2010_06_10/thread_1_seq_2730.1341.721350019

[...]

COMANDO LIST BACKUP OF DATAFILE SUMMARY: Podemos utilzar para que nos liste un suamario de todo lo disponible para el datafile 3 por ejemplo.

RMAN> LIST BACKUP OF DATAFILE 3 SUMMARY; 
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
234     B  F  X DISK        15-AUG-08       1       1       YES        BACKUP_DIARIO_DBF
254     B  F  X DISK        02-SEP-08       1       1       YES        BACKUP_DIARIO_DBF
258     B  F  X DISK        12-SEP-08       1       1       YES        BACKUP_DIARIO_DBF
262     B  F  X DISK        19-SEP-08       1       1       YES        BACKUP_DIARIO_DBF
267     B  F  X DISK        10-OCT-08       1       1       YES        BACKUP_DIARIO_DBF
271     B  F  X DISK        26-NOV-08       1       1       YES        BACKUP_DIARIO_DBF
276     B  F  X DISK        29-NOV-08       1       1       YES        BACKUP_DIARIO_DBF
280     B  F  X DISK        29-NOV-08       1       1       YES        BACKUP_DIARIO_DBF
287     B  F  X DISK        29-JAN-09       1       1       YES        BACKUP_DIARIO_DBF
291     B  F  X DISK        24-FEB-09       1       1       YES        BACKUP_DIARIO_DBF
295     B  F  X DISK        09-MAR-09       1       1       YES        BACKUP_DIARIO_DBF
304     B  F  X DISK        09-MAR-09       1       1       YES        BACKUP_DIARIO_DBF
309     B  F  X DISK        01-MAY-09       1       1       YES        BACKUP_DIARIO_DBF
313     B  F  X DISK        16-MAY-09       1       1       YES        BACKUP_DIARIO_DBF
317     B  F  X DISK        07-JUL-09       1       1       YES        BACKUP_DIARIO_DBF
[...]
524     B  F  X DISK        30-MAY-10       1       1       YES        APP_DIARIO_20100530.0401
532     B  F  X DISK        06-JUN-10       1       1       YES        APP_DIARIO_20100606.0401
540     B  F  X DISK        13-JUN-10       1       1       YES        APP_DIARIO_20100613.0401
553     B  F  A DISK        15-JUN-10       1       1       YES        BACKUP_DIARIO_DBF

RMAN>

LIST INCARNATION: Nos muestra todas las copias incarnation que posemos para una o varias bases.

RMAN> LIST INCARNATION; 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROAPP   3921744109       PARENT  1          19-DEC-06
2       2       PROAPP   3921744109       PARENT  564488     09-NOV-07
3       3       PROAPP   3921744109       PARENT  106837923  25-APR-08
4       4       PROAPP   3921744109       PARENT  152371008  20-JUN-08
5       5       PROAPP   3921744109       PARENT  177194975  09-AUG-08
6       6       PROAPP   3921744109       CURRENT 328850878  15-MAY-09

RMAN>

LIST ALL SCRIPT NAMES: Todos los scripts que tenemos almacenados en el catalogo y que nos sirven para poder aplicarlo a todas las bases que se encuentren registradas.

RMAN> LIST ALL SCRIPT NAMES; 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06002: command not allowed when not connected to a recovery catalog

RMAN> quit

$ rman target / catalog=rman@catrman

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jul 15 13:03:27 2010

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

connected to target database: PROAPP (DBID=xxxxxxxxxx, not open)
recovery catalog database Password:
connected to recovery catalog database

RMAN> LIST ALL SCRIPT NAMES;

List of Stored Scripts in Recovery Catalog

    Global Scripts

       Script Name
       Description
       -----------------------------------------------------------------------
       full_cold_backup

       full_hot_backup

       incremental_copy

    Scripts of Target Database OT2T1N

       Script Name
       Description
       -----------------------------------------------------------------------
       full_hot_backupRMAN>

LIST COPY OF DATABASE SKIP TABLESPACE TEMP: Por último un listado de los tablespace temporales.

RMAN> LIST COPY OF DATABASE SKIP TABLESPACE temp;using target database control file instead of recovery catalogRMAN>

7 thoughts on “RMAN – Command LIST and examples”

Pingback: DbRunas – Noticias y Recursos sobre Bases de Datos » RMAN – Command LIST and examples
W@lter says:

July 21, 2010 at 2:14 am

Kcs culiao! no entiendo absolutamente nada de lo que publicas!!! =)
mira esto -> http://barrapunto.com/articles/10/07/19/0920244.shtml
M$ con su Azure se va meter la nube en el toor, jajaja
un Abrazo y Feliz Dia!

W@lly

LikeLike
1. Juan Andres says:
  
  July 21, 2010 at 11:02 am
  
  Gracias ! Igualmente para vos, tenemos que juntarnos para armar the pulse bomb !
  Otro Abrazo para vos !
  
  1u4n !
  
  LikeLike
W@lter says:

July 21, 2010 at 11:18 am

la semana que viene tengo curso en IT college de cordoba y suipacha, podemos arreglar al mediodia (?)
Abrazo

w4IIy

LikeLike
1. Juan Andres says:
  
  July 21, 2010 at 11:40 am
  
  Confirmado ! podemos transformar en energia mucha masa alimencia 😀
  @Br4z0!
  1001010 1110101 1100001 1101110
  (De ASCII a Binario se pueden hacer cosas maravillosas !)
  
  LikeLike
W@lter says:

July 21, 2010 at 11:45 am

afirmativo!
escribime al mai para coordinar =)

LikeLike
Pingback: RMAN – add missing archivelog | Obtained's Blog

Comments are closed.

Share this:

Like this:

Related

Published by Juan Andres Mercado

7 thoughts on “RMAN – Command LIST and examples”