rman recovery – Recovery Enviroment with RMAN

Wireless Information System for Emergency Resp...

Image via Wikipedia

Recuperando Ambiente con RMAN

Hay momentos en los que tenemos que realizar algún tipo de recovery de ambientes productivos , o de testing sobre ambientes de desarrollo y las reglas de firewall existentes o las policy no nos permiten loguearnos de un host a otro para poder recuperar con el feature de RMAN clonate.

En nuestro caso vamos a plantear un ambientproductivo o de testing que se encuentra en ASM y con diskgroups distintos a los que tenemos en nuestros ambientes de desarrollo.

Entonces es donde recurrimos a otra forma de recovery mediante un backup hot o cold.

CREAMOS UN ARCHIVO DE VARIABLES DE AMBIENTES

$ touch testclona.sh
$ vi testclona.sh

PONEMOS EL CONTENIDO DE NUESTRO HOME Y SID DE LA BASE A RESTOREAR, DESPÚES DE ELLO GUARDAMOS LOS CAMBIOS.

export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_test
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export EDITOR=vi

CREAMOS UN ARCHIVO DE PARAMETROS PARA RESTOREAR NUESTRA BASE

DEBEMOS DE TENER EN CUENTA QUE:

DB_NAME =         BASE A RESTOREAR
DB_UNIQUE_NAME =     NUEVA INSTANCIA CON NUEVO PATH.
CONTROL_FILES =        HAY QUE PONER SOLO EL NOMBRE DEL DISCO DE ASM DONDE SE VA RESTOREAR EG. +DATA
DB_CREATE_FILE_DEST =    HAY QUE PONER SOLO EL NOMBRE DEL DISCO DE ASM DONDE SE VAN A CREAR LOS ARCHIVOS EG. +DATA
DB_FILE_NAME_CONVERT =     HAY QUE PONER EL PATH DE ORIGEN CONTRA EL PATH DE DESTINO EG. +DATA/TEST,+DATA/TESTCLONA
DB_RECOVERY_FILE_DEST =    HAY QUE PONER SOLO EL NOMBRE DEL DISCO DE ASM DONDE SE VAN A RESTOREAR LOS ARCHIVOS EG. +DATA
LOG_FILE_NAME_CONVERT =    HAY QUE PONER EL PATH DE ORIGEN CONTRA EL PATH DE DESTINO EG. +DATA/TEST,+DATA/TESTCLONA

AUDIT_FILE_DEST =    PATH DE DESTINO EN EL SO
BACKGROUND_DUMP_DEST =    PATH DE DESTINO EN EL SO
CORE_DUMP_DEST =     PATH DE DESTINO EN EL SO
USER_DUMP_DEST =     PATH DE DESTINO EN EL SO

# SEGÚN EL CASO HABRÍA QUE COMENTAR EL REMOTE LISTENER

*.audit_file_dest='/u01/app/oracle/admin/TESTCLONA/adump'
*.background_dump_dest='/u01/app/oracle/admin/TESTCLONA/bdump'
*.control_files='+DATA'#Oracle managed file
*.core_dump_dest='/u01/app/oracle/admin/TESTCLONA/cdump'
*.db_create_file_dest='+DATA'
*.db_file_name_convert='+DATA/test','+DATA/testclona'
*.db_name='TEST'
*.db_unique_name='TESTCLONA'
*.db_recovery_file_dest='+DATA'
*.log_file_name_convert='+DATA/test','+DATA/testclona','+DATA/test','+DATA/testclona'
*.user_dump_dest='/u01/app/oracle/admin/TESTCLONA/udump'

LEVANTAMOS LA INSTANCIA

startup nomount from pfile='$ORACLE_HOME/dbs/initTESTCLONA.ora';
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size            2085288 bytes
Variable Size          247467608 bytes
Database Buffers      281018368 bytes
Redo Buffers            6299648 bytes

CREAMOS EL SPFILE

CREATE SPFILE='+DATA/TESTCLONA/initTESTCLONA.ora'
   FROM PFILE='$ORACLE_HOME/initTESTCLONA.ora';

BAJAMOS LA BASE

SHUTDOWN IMMEDIATE

EN EL ARCHIVO DEL HOST CREAMOS UN PUNTERO AL SPFILE QUE SE ENCUENTRA EN EL ASM

$ cd $ORACLE_HOME/dbs
$ echo SPFILE='+DATA/testclona/initTESTCLONA.ora' initTESTCLONA.ora
$ more initTESTCLONA.ora
SPFILE='+DATA/testclona/initTESTCLONA.ora'

SUBIMOS LA INSTANCIA

SQL> startup nomount
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size            2085288 bytes
Variable Size          247467608 bytes
Database Buffers      281018368 bytes
Redo Buffers            6299648 bytes

RELEVAMOS LOS PARAMETROS DB_NAME, DB_UNIQUE_NAME, CONTROLFILES

SQL> sho parameter db_name

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_name                  string     TEST
SQL> sho parameter db_unique_name

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                 string     TESTCLONA

SQL> sho parameter control

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time         integer     7
control_files                 string     +DATA

EN EL CASO DE TENER QUE CAMBIAR UN PARAMETRO POR EJEMPLO EL DB_UNIQUE_NAME

SQL> alter system set db_unique_name=TESTCLONA scope=spfile sid='*';

System altered.

PASAMOS POR SCP EL BACKUP AL /u03/rman_database_backup DE NUESTRO HOST

oracle@sdat2102lx:~/bin $ ls -l /u03/rman_database_backup/ | grep TEST
-rw-r-----  1 oracle oinstall  500457472 Jan 28 11:13 20090128_TEST_t677329873_s58_p1_dbf
-rw-r-----  1 oracle oinstall    1130496 Jan 28 11:13 20090128_TEST_t677329988_s59_p1_dbf
-rw-r-----  1 oracle oinstall   23558144 Jan 28 11:13 20090128_TEST_t677329997_s60_p1_arc
-rw-r-----  1 oracle oinstall    1130496 Jan 28 11:13 20090128_TEST_t677330006_s61_p1_ctl

NOS LOGUEAMOS AL RMAN SIN CATALOGO

$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jan 29 12:29:36 2009

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

connected to target database: TEST (not mounted)

RMAN> restore controlfile from '/u03/rman_database_backup/20090128_TEST_t677330006_s61_p1_ctl';

Starting restore at 29-JAN-09
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output filename=+DATA/testclona/controlfile/current.288.677421725
Finished restore at 29-JAN-09

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

DESPUES DE MONTAR LA BASE LEER MAS ABAJO EN IMPORTANTE

RMAN> restore database;

Starting restore at 29-JAN-09
Starting implicit crosscheck backup at 29-JAN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
Crosschecked 55 objects
Finished implicit crosscheck backup at 29-JAN-09

Starting implicit crosscheck copy at 29-JAN-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 29-JAN-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +data/TESTCLONA/CONTROLFILE/current.312.677421073

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/test/datafile/system.924.671123615
restoring datafile 00002 to +DATA/test/datafile/undotbs1.1085.671123615
restoring datafile 00003 to +DATA/test/datafile/sysaux.1193.671123615
restoring datafile 00004 to +DATA/test/datafile/users.1087.671123615
restoring datafile 00005 to +DATA/test/datafile/example.388.671123673
restoring datafile 00006 to +DATA/test/datafile/undotbs2.554.671123761
restoring datafile 00007 to +DATA/test/datafile/t24dr704_idx.618.671200431
restoring datafile 00008 to +DATA/test/datafile/t24dr704_dat.916.671200443
channel ORA_DISK_1: reading from backup piece /u03/rman_database_backup/20090128_TEST_t677329873_s58_p1_dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u03/rman_database_backup/20090128_TEST_t677329873_s58_p1_dbf tag=BACKUP_DIARIO_DBF
channel ORA_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 29-JAN-09

RMAN> recover database;

Starting recover at 29-JAN-09
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=68
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=26
channel ORA_DISK_1: reading from backup piece /u03/rman_database_backup/20090128_TEST_t677329997_s60_p1_arc
channel ORA_DISK_1: restored backup piece 1
piece handle=/u03/rman_database_backup/20090128_TEST_t677329997_s60_p1_arc tag=BACKUP_DIARIO_ARC
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
archive log filename=+DATA/testclona/archivelog/2009_01_29/thread_1_seq_68.277.677422161 thread=1 sequence=68
archive log filename=+DATA/testclona/archivelog/2009_01_29/thread_2_seq_26.278.677422161 thread=2 sequence=26
channel default: deleting archive log(s)
archive log filename=+DATA/testclona/archivelog/2009_01_29/thread_2_seq_26.278.677422161 recid=106 stamp=677422160
unable to find archive log
archive log thread=2 sequence=27
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/29/2009 12:49:24
RMAN-06054: media recovery requesting unknown log: thread 2 seq 27 lowscn 5981371

RMAN>

SALIMOS DEL RMAN Y NOS LOGUEAMOS A LA BASE

SQL> alter database open resetlogs;

Database altered.

HABRIA QUE HACER EL CAMBIO DE NOMBRE

DEJAR LA BASE EN MODO NOARCHIVELOG

IMPORTANTE: DESPÚES DE MONTAR LA BASE CON EL RMAN PODEMOS ABRIR UNA NUEVA TERMINAL Y LOGUEARNOS AL SQLPLUS

Y VERIFICAMOS QUE EL PATH DONDE SE ENCUENTRAN NUESTROS DATAFILES

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/test/datafile/system.924.671123615
+DATA/test/datafile/undotbs1.1085.671123615
+DATA/test/datafile/sysaux.1193.671123615
+DATA/test/datafile/users.1087.671123615
+DATA/test/datafile/example.388.671123673
+DATA/test/datafile/undotbs2.554.671123761
+DATA/test/datafile/t24dr704_idx.618.671200431
+DATA/test/datafile/t24dr704_dat.916.671200443

8 rows selected.

UNA VEZ FINALIZADO EL RESTORE Y RECOVER PODEMOS QUE CAMBIARON SOLOS LOS PATH DE TEST A TESTCLONA.

SQL> r
1* select name from v$datafile

NAME
--------------------------------------------------------------------------------
+DATA/testclona/datafile/system.339.677421979
+DATA/testclona/datafile/undotbs1.367.677421979
+DATA/testclona/datafile/sysaux.314.677421979
+DATA/testclona/datafile/users.293.677421979
+DATA/testclona/datafile/example.365.677421979
+DATA/testclona/datafile/undotbs2.359.677421979
+DATA/testclona/datafile/t24dr704_idx.276.677421979
+DATA/testclona/datafile/t24dr704_dat.337.677421979

8 rows selected.

PODEMOS VER TAMBIÉN LA CREACIÓN DE LOS DATAFILES EN EL ASM

oracle@sdat2102lx:~/bin $ . ./asm.sh
oracle@sdat2102lx:~/bin $ asmcmd -p

ASMCMD [+] > cd DATA/TESTCLONA/DATAFILE

ASMCMD [+DATA/TESTCLONA/DATAFILE] > ls
EXAMPLE.365.677421979
UNDOTBS1.367.677421979
UNDOTBS2.359.677421979

ASMCMD [+DATA/TESTCLONA/DATAFILE] > ls
EXAMPLE.365.677421979
UNDOTBS1.367.677421979
UNDOTBS2.359.677421979
USERS.293.677421979ASMCMD [+DATA/TESTCLONA/DATAFILE] > ls
EXAMPLE.365.677421979
SYSAUX.314.677421979
SYSTEM.339.677421979
T24DR704_IDX.276.677421979
UNDOTBS1.367.677421979
UNDOTBS2.359.677421979
USERS.293.677421979

About Juan Andres
Consultant | Oracle DBA & IT Specialist | LinuxUnix Administrator | Father | Musician | Farmer | Environmentalist | Writer | Builder | Buenos Aires · burzaco.wordpress.com

2 Responses to rman recovery – Recovery Enviroment with RMAN

  1. Pingback: RMAN : Monitoring Backup Jobs « Tecnología Informática Buenos Aires

  2. hello!,I love your writing very a lot! share we communicate extra approximately your post on AOL? I need an expert in this area to resolve my problem. May be that’s you! Having a look ahead to peer you.

%d bloggers like this: