16 Feb 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?
Pingback:RMAN 12cR1 : Dile adios a tu respaldo cuando haces un DROP de tu PDB - Parte II
Posted at 08:22h, 21 April[…] última entrada en mi blog de Pythian (aquí en español) antes de unirme a Oracle, hable de lo que ocurre si intentas recuperar una BASE DE DATOS […]