jueves, 25 de octubre de 2012

RMAN : Crear un respaldo consistente y un RESTORE POINT todo a la vez

Una característica que tiene RMAN en 11g es que tienes la posibilidad de crear un punto de restauración en el mismo respaldo, este método es muy útil, sobre todo cuando vas a hacer una actualización importante en tu Base de Datos y tienes que crear un respaldo antes, este es el mejor método desde mi punto de vista.

Lo primero , es que verifico que no hay un punto de restauración para mi base de datos

TESTDB> set linesize 121
TESTDB> col name format a15
TESTDB> col time format a32

TESTDB> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size
  2  FROM gv$restore_point;

no rows selected

Ahora lo que hago es que corro un respaldo con la opción de KEEP UNTIL y RESTORE POINT

RMAN> RUN
2>  {
3> BACKUP AS COMPRESSED BACKUPSET
4>  INCREMENTAL LEVEL = 0
5>  DATABASE
6>   FORMAT '/mount/copy01/TESTDB/oracle/TESTDB/incr/%d_HOT_%M%D%Y_%p_%s' 
7>   TAG TESTDB
8>   KEEP UNTIL TIME 'SYSDATE + 5'
9>   RESTORE POINT BEFORE_UPGRADE;
10> }
Starting backup at Oct 25 2012 08:41:49
current log archived

using channel ORA_DISK_1
backup will be obsolete on date Oct 30 2012 08:41:50
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/mount/u01/oracle/TESTDB/data/system01.dbf
input datafile file number=00003 name=/mount/u01/oracle/TESTDB/data/undotbs1_01.dbf
input datafile file number=00004 name=/mount/u01/oracle/TESTDB/data/users01.dbf
input datafile file number=00002 name=/mount/u01/oracle/TESTDB/data/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at Oct 25 2012 08:41:50
channel ORA_DISK_1: finished piece 1 at Oct 25 2012 08:41:57
piece handle=/mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_10252012_1_24 tag=TESTDB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

using channel ORA_DISK_1
backup will be obsolete on date Oct 30 2012 08:41:57
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed 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 Oct 25 2012 08:41:57
channel ORA_DISK_1: finished piece 1 at Oct 25 2012 08:41:58
piece handle=/mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_10252012_1_25 tag=TESTDB 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 Oct 30 2012 08:41:58
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=19 STAMP=797589718
channel ORA_DISK_1: starting piece 1 at Oct 25 2012 08:41:58
channel ORA_DISK_1: finished piece 1 at Oct 25 2012 08:41:59
piece handle=/mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_10252012_1_26 tag=TESTDB 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 Oct 30 2012 08:41:59
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed 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 Oct 25 2012 08:42:00
channel ORA_DISK_1: finished piece 1 at Oct 25 2012 08:42:01
piece handle=/mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_10252012_1_27 tag=TESTDB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at Oct 25 2012 08:42:01

Para verificar que se creo el punto de restauración corro los siguientes comandos en RMAN

RMAN> list restore point all;

SCN              RSP Time             Type       Time                 Name
---------------- -------------------- ---------- -------------------- ----
234073                                           Oct 25 2012 08:41:58 BEFORE_UPGRADE

y en Sqlplus

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size
  2  FROM gv$restore_point;

NAME         SCN TIME        DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------- ---------- -------------------------------- --------------------- --- ------------
BEFORE_UPGRADE     234073 25-OCT-12 08.41.58.000000000 AM   1 NO   0

Si te fijas, el SCN es el mismo , 234073,  que es lo que andamos buscando. En dado caso de que tengas que hacer un rollback, a este SCN seria al que regresarias.

Este respaldo de igual manera lo podemos utilizar para clonar una base de datos hasta el punto de restauración que creaste con la opción TO RESTORE POINT, no puse toda la salida, pero si te fijas abajo, esta recuperando hasta el punto de restauración que creamos en la Base de Datos TESTDB

oracle@servidor1.oracleenespanol.blogspot.com [TESTDB] /mount/dba01/oracle/TESTCOPY/scripts
oracle $ rman target / auxiliary sys@TESTCOPY


RMAN> RUN
2> {  
3>   ALLOCATE AUXILIARY CHANNEL newdb DEVICE TYPE disk; 
4>   DUPLICATE TARGET DATABASE TO testcopy
5>     TO RESTORE POINT BEFORE_UPGRADE
6>      DB_FILE_NAME_CONVERT ('/mount/u01/oracle/TESTDB/','/mount/u01/oracle/TESTCOPY/','/mount/u02/oracle/TESTDB/','/mount/u02/oracle/TESTCOPY/')
7> LOGFILE
8>       GROUP 1 ('/mount/u01/oracle/TESTCOPY/log/redo01_1.f',
9>                '/mount/u02/oracle/TESTCOPY/log/redo01_2.f') SIZE 4M,
10>       GROUP 2 ('/mount/u01/oracle/TESTCOPY/log/redo02_1.f',
11>                '/mount/u02/oracle/TESTCOPY/log/redo02_2.f') SIZE 4M,
12>       GROUP 3 ('/mount/u01/oracle/TESTCOPY/log/redo03_1.f',
13>                '/mount/u02/oracle/TESTCOPY/log/redo03_2.f') SIZE 4M REUSE;
14> }
using target database control file instead of recovery catalog
allocated channel: newdb
channel newdb: SID=19 device type=DISK

Starting Duplicate Db at Oct 25 2012 08:50:53

contents of Memory Script:
{
   set to restore point  'BEFORE_UPGRADE';
   sql clone "alter system set  db_name = 
 ''TESTDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''TESTCOPY'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
.
.
.
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at Oct 25 2012 08:51:57
released channel: newdb


Espero que este método te sirva en tus próximas actualizaciones.