REFRESCAR PHYSICAL STANDBY A PARTIR DE BACKUP INCREMENTAL FROM SCN – Refresh Standby from backup incremental from SCN

¿Cuantas veces hemos perdido una secuencia de archivelog, o nos damos cuenta que nuestra BD standby esta desincronizada y al ver los archivelogs de la BD primaria con la intención de aplicarlos estos ya fueron borrados por la política de retención?.

El siguiente procedimiento describe el paso a paso para re-sincronizar nuestra base de datos standby, sin necesidad de volver a restaurar toda la BD, basta con aplicar las secuencias faltantes a partir del ultimo SCN disponible en que se haya quedado nuestra BD standby.

using_physical_standby_as_a_testing_and_reporting_database

Este método nos puede ahorrar mucho tiempo en la re-sincronización de nuestro standby, sobretodo en bases de datos de gran tamaño.

PASO A PASO:

1.-IDENTIFICAR EL ACTUAL SCN EN QUE SE QUEDO DESINCRONIZADO NUESTRO STANDBY DATABASE
SQL> select current_scn from v$database;

CURRENT_SCN
———————-
1719913113

2.-EN PRODUCCION GENERAR  BACKUP CONTROLFILE PARA STANDBY:
SQL> alter database create standby controlfile as ‘/tmp/backup/standby_control.ctl’;
Database altered.

 

3.-EN PRODUCCION GENERAR BACKUP INCREMENTAL FROM SCN, TOMANDO COMO INPUT EL SCN DEL PASO PREVIO(PASO 1).
[oracle@PR ~]$ rman target /
RMAN> backup device type disk incremental from scn 1719913113 database format ‘/tmp/backup/Inc_backup_%U’;
Starting backup at 13-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
backup will be obsolete on date 20-MAY-16
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/RTS/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/RTS/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/RTS/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/RTS/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-MAY-16
……..

.

4.-TRANSFERIR LOS BACKUPIECES AL SERVIDOR STANDBY
cd /tmp/backup
-bash-4.2$ ls -ltr
total 3123304
-rw-r—– 1 oracle oinstall 12828672 May 18 12:11 standby_control.ctl
-rw-r—– 1 oracle oinstall 478183424 May 18 12:13 Inc_backup_r8t36sr4_1_1
-rw-r—– 1 oracle oinstall 1019928576 May 18 12:13 Inc_backup_r9t36srj_1_1
-rw-r—– 1 oracle oinstall 515129344 May 18 12:13 Inc_backup_rat36ss2_1_1
-rw-r—– 1 oracle oinstall 899063808 May 18 12:14 Inc_backup_rbt36ssa_1_1
-rw-r—– 1 oracle oinstall 260235264 May 18 12:14 Inc_backup_rct36ssp_1_1
-rw-r—– 1 oracle oinstall 12877824 May 18 12:14 Inc_backup_rdt36st0_1_1

-bash-4.2$ scp * oracle@fcrac05:/home/oracle/reco_sby_scn
oracle@fcrac05’s password:
Inc_backup_r8t36sr4_1_1 100% 456MB 152.0MB/s 00:03
Inc_backup_r9t36srj_1_1 100% 973MB 139.0MB/s 00:07
Inc_backup_rat36ss2_1_1 100% 491MB 98.3MB/s 00:05
Inc_backup_rbt36ssa_1_1 100% 857MB 171.5MB/s 00:05
Inc_backup_rct36ssp_1_1 100% 248MB 248.2MB/s 00:01
Inc_backup_rdt36st0_1_1 100% 12MB 12.3MB/s 00:00
standby_control.ctl 100% 12MB 12.2MB/s 00:01

5.-RESTAURAR INCREMENTAL BACKUP EN EL SERVIDOR STANDBY:

5.1.- Poner la BD en modo nomount

shutdown abort;
startup nomount;

5.2.- Restaurar el standby controlfile

[oracle@DR ~]$ rman target /
connected to target database: IRIS (not mounted)
RMAN> restore controlfile from ‘/home/oracle/reco_sby_scn/standby_control.ctl’;

Starting restore at 18-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3421 instance=IRIS5 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DG_DATA11G_01/IRISSBY/CONTROLFILE/current.2206.972939175
Finished restore at 18-MAY-18

5.3.- Montar la base de datos
SQL> alter database mount;
Database altered.

5.4.- catalogar los backuppieces
[oracle@DR ~]$ rman target /
RMAN> catalog start with ‘/home/oracle/reco_sby_scn/’;

using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/reco_sby_scn/

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/reco_sby_scn/Inc_backup_r9t36srj_1_1
File Name: /home/oracle/reco_sby_scn/Inc_backup_r8t36sr4_1_1
File Name: /home/oracle/reco_sby_scn/standby_control.ctl
File Name: /home/oracle/reco_sby_scn/Inc_backup_rat36ss2_1_1
File Name: /home/oracle/reco_sby_scn/Inc_backup_rbt36ssa_1_1
File Name: /home/oracle/reco_sby_scn/Inc_backup_rct36ssp_1_1
File Name: /home/oracle/reco_sby_scn/Inc_backup_rdt36st0_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/reco_sby_scn/Inc_backup_r9t36srj_1_1
File Name: /home/oracle/reco_sby_scn/Inc_backup_r8t36sr4_1_1
File Name: /home/oracle/reco_sby_scn/standby_control.ctl
File Name: /home/oracle/reco_sby_scn/Inc_backup_rat36ss2_1_1
File Name: /home/oracle/reco_sby_scn/Inc_backup_rbt36ssa_1_1
File Name: /home/oracle/reco_sby_scn/Inc_backup_rct36ssp_1_1
File Name: /home/oracle/reco_sby_scn/Inc_backup_rdt36st0_1_1

5.5.-Recover a partir del backup incremental from SCN.

RMAN> recover database;

Starting recover at 18-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3901 instance=IRIS5 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/18/2018 12:25:00
RMAN-06094: datafile 1 must be restored <—————ERROR

 

NOTA:Recordar que estamos volviendo a restaurar un nuevo standby controlfile que viene desde el origen donde no necesariamente los datafiles tienen la misma ruta que en el destino.En este caso los datafiles en el origen tienen diferente ruta que en el destino,
por lo tanto el recover fallará debido a que el controlfile no los conoce. Para decirle a controlfile que los reconozca, se tiene que catalogar el diskgroup o la ruta donde estos residen en el standby:
RMAN-06094: datafile 1 must be restored

Catalogamos la ruta de nuestros datafiles:

RMAN> catalog start with ‘+DG_DATA11G_01/IRISSBY/datafile’;

using target database control file instead of recovery catalog
searching for all files that match the pattern +DG_DATA11G_01/IRISSBY/datafile

List of Files Unknown to the Database
=====================================
File Name: +DG_DATA11G_01/IRISSBY/datafile/system.2119.972937765
File Name: +DG_DATA11G_01/IRISSBY/datafile/system.2259.972937789
File Name: +DG_DATA11G_01/IRISSBY/datafile/sysaux.2273.972937819
File Name: +DG_DATA11G_01/IRISSBY/datafile/sysaux.2204.972937847
File Name: +DG_DATA11G_01/IRISSBY/datafile/undotbs1.2270.972937873
File Name: +DG_DATA11G_01/IRISSBY/datafile/users.2205.972937923
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data1.2255.972937939
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data1.2240.972938015
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data1.2239.972938077
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data1.2254.972938139
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data2.2165.972938205
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data2.2114.972938265
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data2.2160.972938421
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data2.2269.972938503

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: +DG_DATA11G_01/IRISSBY/datafile/system.2119.972937765
File Name: +DG_DATA11G_01/IRISSBY/datafile/system.2259.972937789
File Name: +DG_DATA11G_01/IRISSBY/datafile/sysaux.2273.972937819
File Name: +DG_DATA11G_01/IRISSBY/datafile/sysaux.2204.972937847
File Name: +DG_DATA11G_01/IRISSBY/datafile/undotbs1.2270.972937873
File Name: +DG_DATA11G_01/IRISSBY/datafile/users.2205.972937923
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data1.2255.972937939
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data1.2240.972938015
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data1.2239.972938077
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data1.2254.972938139
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data2.2165.972938205
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data2.2114.972938265
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data2.2160.972938421
File Name: +DG_DATA11G_01/IRISSBY/datafile/iris_data2.2269.972938503

 

RMAN> switch database to copy;
datafile 1 switched to datafile copy “+DATA/ANARDBdr/datafile/system.274.903008331”
datafile 2 switched to datafile copy “+DATA/ANARDBdr/datafile/sysaux.272.903008327”
datafile 3 switched to datafile copy “+DATA/ANARDBdr/datafile/undotbs1.276.903008333”
datafile 4 switched to datafile copy “+DATA/ANARDBdr/datafile/users.273.903008327”
datafile 5 switched to datafile copy “+DATA/ANARDBdr/datafile/undotbs2.277.903008333”
datafile 6 switched to datafile copy “+DATA/ANARDBdr/datafile/data.2871.903008317”
datafile 7 switched to datafile copy “+DATA/ANARDBdr/datafile/data.3554.903008317”
datafile 8 switched to datafile copy “+DATA/ANARDBdr/datafile/data.256.903008315”
datafile 9 switched to datafile copy “+DATA/ANARDBdr/datafile/data.3751.903008327”
datafile 10 switched to datafile copy “+DATA/ANARDBdr/datafile/data.257.903008315”
datafile 11 switched to datafile copy “+DATA/ANARDBdr/datafile/indx.2630.903008327”
datafile 12 switched to datafile copy “+DATA/ANARDBdr/datafile/indx.2629.903008327”
datafile 13 switched to datafile copy “+DATA/ANARDBdr/datafile/data.275.903008333”

 

FINALMENTE:

RMAN> recover database;

Starting recover at 18-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3481 instance=IRIS5 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: +DG_DATA11G_01/IRISSBY/datafile/iris_data1.2254.972938139
destination for restore of datafile 00010: +DG_DATA11G_01/IRISSBY/datafile/iris_data1.2255.972937939
channel ORA_DISK_1: reading from backup piece /home/oracle/reco_sby_scn/Inc_backup_r9t36srj_1_1
channel ORA_DISK_1: piece handle=/home/oracle/reco_sby_scn/Inc_backup_r9t36srj_1_1 tag=TAG20180518T121324
…..
..
.
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00009: +DG_DATA11G_01/IRISSBY/datafile/iris_data1.2240.972938015
destination for restore of datafile 00012: +DG_DATA11G_01/IRISSBY/datafile/iris_data2.2114.972938265
channel ORA_DISK_1: reading from backup piece /home/oracle/reco_sby_scn/Inc_backup_rat36ss2_1_1
channel ORA_DISK_1: piece handle=/home/oracle/reco_sby_scn/Inc_backup_rat36ss2_1_1 tag=TAG20180518T121324
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

starting media recovery

archived log for thread 1 with sequence 6200 is already on disk as file +DG_DATA11G_01/IRISSBY/ARCHIVELOG/2018_05_18/thread_1_seq_6200.1684.976450999
archived log for thread 1 with sequence 6201 is already on disk as file +DG_DATA11G_01/IRISSBY/ARCHIVELOG/2018_05_18/thread_1_seq_6201.2211.976451359
archived log for thread 1 with sequence 6202 is already on disk as file +DG_DATA11G_01/IRISSBY/ARCHIVELOG/2018_05_18/thread_1_seq_6202.944.976451721
archived log for thread 1 with sequence 6203 is already on disk as file

…..
….
.
archived log file name=+DG_DATA11G_01/IRISSBY/ARCHIVELOG/2018_05_18/thread_1_seq_6205.2169.976452801 thread=1 sequence=6205
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-MAY-18

6.-ACTIVAR EL STANDBY DATABASE

[oracle@fcrac05 reco_sby_scn]$ srvctl stop database -d IRISSBY
[oracle@fcrac05 reco_sby_scn]$ srvctl start database -d IRISSBY -o nomount
[oracle@fcrac05 reco_sby_scn]$ !sqlp
sqlplus ‘/as sysdba’

SQL*Plus: Release 12.1.0.2.0 Production on Fri May 18 13:01:01 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> alter database mount standby database;
Database altered.

SQL> alter database open read only;
Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 8 USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.

7.-VALIDAR SINCRONIZACIÓN DE STANDBY
SQL> alter session set nls_date_format=’dd/mm/yyyy hh24:mi:ss’;
SELECT sequence#, first_time, next_time, applied, thread# FROM v$archived_log ORDER BY 1,2;
Session altered.

sincronizacion_standby

Finalmente tenemos nuestro standby database sincronizado nuevamente !!

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s