sábado, 15 de febrero de 2014

RMAN 12c : Dile adios a tu respaldo cuando haces un DROP de tu PDB

Estaba trabajando en mis presentaciones para IOUG Collaborate, y me tope con este extraño comportamiento en RMAN 12c (12.1.0.1.0), que para mí, no debería suceder. Parece que cuando haces un DROP PLUGGABLE DATABASE, es el equivalente a DROP DATABASE INCLUDING BACKUPS. Esto significa que si necesitas  restaurar tu PDB más adelante, no tendrás este respaldo registrado - así que ten cuidado cuando tires tu PDB.

Aquí vamos: Tomé un respaldo de mi CDB y todas sus PDBs, ahora mantente atento en este tag TAG20140212T191237 (He quitado un par de líneas para facilitar la lectura del blog).

oracle@oracleenespanol2.localdomain [cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin
 
oracle $ rman target sys/oracle@cdb1
 
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 12 19:12:06 2014
 
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CDB1 (DBID=808250731)
 
RMAN> backup database plus archivelog ;
 
Starting backup at 12/02/2014 19:12:31
 
current log archived
 
...
 
Starting backup at 12/02/2014 19:12:37
 
using channel ORA_DISK_1
 
channel ORA_DISK_1: starting full datafile backup set
 
channel ORA_DISK_1: specifying datafile(s) in backup set
 
input datafile file number=00003 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c520w6w_.dbf
 
input datafile file number=00004 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_9c524cnr_.dbf
 
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c522mbz_.dbf
 
input datafile file number=00006 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_9c524bjm_.dbf
 
channel ORA_DISK_1: starting piece 1 at 12/02/2014 19:12:38
 
channel ORA_DISK_1: finished piece 1 at 12/02/2014 19:15:23
 
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbjrqd_.bkp tag=TAG20140212T191237 comment=NONE
 
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:45
 
channel ORA_DISK_1: starting full datafile backup set
 
channel ORA_DISK_1: specifying datafile(s) in backup set
 
input datafile file number=00009 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf
 
input datafile file number=00008 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf
 
input datafile file number=00010 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf
 
channel ORA_DISK_1: starting piece 1 at 12/02/2014 19:15:23
 
channel ORA_DISK_1: finished piece 1 at 12/02/2014 19:16:08
 
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbovmw_.bkp tag=TAG20140212T191237 comment=NONE
 
..
 
Finished backup at 12/02/2014 19:16:37
 
Starting Control File and SPFILE Autobackup at 12/02/2014 19:16:37
 
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2014_02_12/o1_mf_s_839358997_9hrbr5vr_.bkp comment=NONE
 
Finished Control File and SPFILE Autobackup at 12/02/2014 19:16:38

Así que primero quiero mostarte que si soy capaz de recuperar mi PDB en caso de que perdiera mis archivos con el respaldo que tengo, así que primero voy a eliminarlos manualmente.

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin
 
oracle $ sqlplus / as sysdba
 
Connected to:
 
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
SQL> set pages 999
 
SQL> COLUMN PDB_ID FORMAT 999
 
COLUMN PDB_NAME FORMAT A8
 
COLUMN FILE_ID FORMAT 9999
 
COLUMN TABLESPACE_NAME FORMAT A10
 
COLUMN FILE_NAME FORMAT A45
 
SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
 
FROM DBA_PDBS p, CDB_DATA_FILES d
 
WHERE p.PDB_ID = d.CON_ID
 
ORDER BY p.PDB_ID;
 
PDB_ID PDB_NAME FILE_ID TABLESPACE FILE_NAME
 
------ -------- ------- ---------- ---------------------------------------------
 
2 PDB$SEED       5 SYSTEM   /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c5257ms_.dbf
 
2 PDB$SEED       7 SYSAUX   /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c5257mj_.dbf
 
3 PDB1      9 SYSAUX   /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf
 
3 PDB1      8 SYSTEM   /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf
 
3 PDB1     10 USERS   /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf
 
SQL> alter pluggable database pdb1 close;
 
Pluggable database altered.
 
SQL> exit
 
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin
 
oracle $ rm /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf
 
oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin
 
oracle $ rm /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf
 
oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin
 
oracle $ rm /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf

Ahora que he borrado los datafiles de mi PDB, voy a proceder a restaurar y recuperarla, y no pierdas de vista la etiqueta TAG20140212T191237 que es utilizada para la restauración.

oracle@oracleenespanol2.localdomain [cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin
oracle $ rman target /
 
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 12 19:19:46 2014
 
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
 
connected to target database: CDB1 (DBID=808250731)
 
RMAN> RESTORE PLUGGABLE DATABASE PDB1;
 
Starting restore at 12/02/2014 19:20:12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=77 device type=DISK
 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbovmw_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbovmw_.bkp tag=TAG20140212T191237
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 12/02/2014 19:20:48
 
RMAN> RECOVER PLUGGABLE DATABASE PDB1;
 
Starting recover at 12/02/2014 19:21:06
using channel ORA_DISK_1
 
starting media recovery
media recovery complete, elapsed time: 00:00:00
 
Finished recover at 12/02/2014 19:21:07
 
RMAN> alter pluggable database pdb1 open;
 
Statement processed

Como puedes ver, pude restaurar y recuperar mi PDB sin ningún problema. ¿Pero qué sucede si decido tirar mi PDB, y más tarde me doy cuenta de que la PDB se necesitaba? Así que traté recuperar mis PDB con mi respaldo, ya no esta, y de igual manera no se reporta en la etiqueta de respaldo TAG20140212T191237.

RMAN> alter pluggable database pdb1 close;
 
Statement processed
 
RMAN> drop pluggable database PDB1;
 
Statement processed
 
RMAN> RESTORE PLUGGABLE DATABASE PDB1;
 
Starting restore at 13/02/2014 11:18:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/13/2014 11:18:27
RMAN-06813: could not translate pluggable database PDB1
 
RMAN> list backup tag TAG20140212T191237;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
67 Full 1.88G DISK 00:02:41 12/02/2014 19:15:19
 BP Key: 67 Status: AVAILABLE Compressed: NO Tag: TAG20140212T191237
 Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbjrqd_.bkp
 List of Datafiles in backup set 67
 File LV Type Ckp SCN Ckp Time Name
 ---- -- ---- ---------- ------------------- ----
 1 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c522mbz_.dbf
 3 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c520w6w_.dbf
 4 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_9c524cnr_.dbf
 6 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_9c524bjm_.dbf
 
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
69 Full 680.13M DISK 00:00:24 12/02/2014 19:16:32
 BP Key: 69 Status: AVAILABLE Compressed: NO Tag: TAG20140212T191237
 Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/EDDDB886A1191F07E043344EB2C0BE27/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbq8lm_.bkp
 List of Datafiles in backup set 69
 Container ID: 2, PDB Name: PDB$SEED
 File LV Type Ckp SCN Ckp Time Name
 ---- -- ---- ---------- ------------------- ----
 5 Full 1732663 18/12/2013 22:52:25 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c5257ms_.dbf
 7 Full 1732663 18/12/2013 22:52:25 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c5257mj_.dbf

Como puedes ver, el respaldo de mi PDB ya no está registrado. Yo todavía no sé si esto es un comportamiento normal para los respaldos de una PDB o un error - pero por ahora sólo ten cuidado al tirar una PDB ya que el respaldo puede que no será confiable. Si esto no te asusta, la verdad no se que lo hará ¿o no?