DATAGUARD

Link Inmportantes

http://basesdedatosues.blogspot.com/2010/07/creando-manualmente-un-data-guard-en.html

CONFIGURACION DATAGUARD PARA DOS INSTANCIAS STANDBY 11G WINDOWS

http://www.youtube.com/watch?v=5keahpkzLtM

CONFIGURACION DATAGUARD SNAPSHOT

http://www.youtube.com/watch?v=Ns2TabgoZew


ACTIVE DATAGUARD 11G

http://www.youtube.com/watch?v=zULeOXDOobM

(select sequence#,applied from v$archived_log)

select NAME, THREAD#, SEQUENCE#, APPLIED, registrar from v$archived_log where APPLIED='NO' order by sequence#;

Consultando la v$archived_log y el alert de la standby.

Para poner la standby a sincronizar, estando montada corres lo siguiente:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Si resulta ser que ya estaba en modo de recover y no aplicaba los logs, fijate en la vista v$dataguard_status.


Como el EM me avisa que estoy teniendo logs sin aplicar, y que estan faltando otros, me logueo por consola y compruebo lo dicho, estan faltando archives y tenemos un GAP.

SQL> SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
----------- ------------- --------------
2 1785 1785


Como el EM me avisa que estoy teniendo logs sin aplicar, y que estan faltando otros, me logueo por consola y compruebo lo dicho, estan faltando archives y tenemos un GAP.

SQL> SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
----------- ------------- --------------
2 1785 1785

Teniendo el numero de la secuencia, en este caso la SEQ 1785 THREAD 2, consulto si existe este archivo en el storage del dataguard.

SQL> set line 150
SQL> col NAME format a80
SQL> select NAME, THREAD#, SEQUENCE#, APPLIED, registrar from v$archived_log where SEQUENCE#='1785' and THREAD#='2' order by sequence#; 2

NAME THREAD# SEQUENCE# APP REGISTR
---------------------------------- ---------- ---------- --- -------
2 1785 NO SRMN

Busco en el sitio primario, y efectivamente la pieza faltante se encuentra donde deberia estar.

SQL> select NAME, THREAD#, SEQUENCE#, APPLIED, registrar from v$archived_log where SEQUENCE#='1785' and THREAD#='2' order by sequence#; 2 3

NAME THR# SEQ# APP REGISTR
---------------------------------------------------- ------- ------- --- -------
+JP_DG2/JP/archivelog/2010_04_08
/thread_2_seq_1785.2588.715777831 2 1785 YES SRMN

Restoreamos el archive que necesitamos.

RMAN> RESTORE ARCHIVELOG FROM SEQUENCE 1785 THREAD 2;

Starting restore at 08-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=307 instance=JPDG1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=287 instance=JPDG1 devtype=DISK

archive log thread 2 sequence 1786 is already on disk as file
+JPDG_DG2/JP/archivelog/2010_04_06/thread_2_seq_1786.3764.715610751
archive log thread 2 sequence 1787 is already on disk as file
+JPDG_DG2/JP/archivelog/2010_04_06/thread_2_seq_1787.2909.715610787
archive log thread 2 sequence 1788 is already on disk as file
+JPDG_DG2/JP/archivelog/2010_04_06/thread_2_seq_1788.2125.715610813
archive log thread 2 sequence 1789 is already on disk as file
+JPDG_DG2/JP/archivelog/2010_04_06/thread_2_seq_1789.2620.715610849
archive log thread 2 sequence 1790 is already on disk as file
+JPDG_DG2/JP/archivelog/2010_04_06/thread_2_seq_1790.636.715643317
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=1785
channel ORA_DISK_1: reading from backup piece
/u03/rman_database_backup/thread_2_seq1785_525_1.logs
channel ORA_DISK_1: restored backup piece 1
piece handle=/u03/rman_database_backup/thread_2_seq1785_525_1.logs tag=TAG20100408T105951
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 08-APR-10

RMAN>Debemos recordar que al buscar la pieza dentro del ASM el rman la restorea al dia de la fecha y si no ponemos la sentencia de destino, la incorporara al path del dia de hoy.

Lo vemos con mas detalle ingresando al ASM y verificando que el log exista en el filesystem.

ASMCMD> cd 2010_04_08/
ASMCMD> ls
thread_1_seq_1864.791.715755691
thread_1_seq_1865.3747.715772623
thread_1_seq_1866.3007.715774079
thread_1_seq_1867.3722.715774109
thread_1_seq_1868.2712.715774259
thread_1_seq_1869.3680.715774289
thread_1_seq_1870.645.715774325
thread_1_seq_1871.3511.715774353
thread_1_seq_1872.974.715774407
thread_1_seq_1873.3343.715774433
thread_1_seq_1874.2724.715774565
thread_1_seq_1875.628.715774643
thread_2_seq_1785.2588.715777831
thread_2_seq_1813.2260.715772625Al encontrarse el archive restoreado donde corresponde, procedemos a detener el recovery y a registrar la pieza.

ALTER DATABASE REGISTER LOGFILE 'PATH/FILE.logs';Es importante saber que si tenemos el configurado dataguard broker, este trabajo lo hace de manera automatica, y que al intentar registrar el REDO faltante no arrojara el error ORA-16089:

SQL> ALTER DATABASE REGISTER LOGFILE '+JPDG_DG2/JP/ARCHIVELOG/2010_04_08/thread_2_seq_1785.2588.715777831'; 2ALTER DATABASE REGISTER LOGFILE '+JPDG_DG2/JP/ARCHIVELOG/2010_04_08/thread_2_seq_1785.2588.715777831'
*
ERROR at line 1:
ORA-16089: archive log has already been registeredUna vez que comienza el aplicado de logs podemos verlo el alert.log de la base.

Tiramos la consulta para ver si comenzo el aplicación, esto decrementaria la cantidad de logs encolados.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE


También verificamos que el GAP desaparecio.


SQL> select * from v$archive_gap;no rows selected

Review v$ views for errors

–Log Transport from Primary

v$archive_dest_status–status of each destination
v$archived_log –review which archived logs have not been shipped to the standby destination

–Log Application on Standby

v$managed_standby –status of log transport and log apply
v$archive_dest_status–archived_seq# vsapplied_seq#
v$archived_log –list of arch logs received from primary
v$dataguard_status–Data Gurardrelated alert messages
dba_logstdby_progress–progress of SQL apply
v$archive_gap –any gaps in archive logs


RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT UNTIL TIME 'SYSDATE - 2';


SQL> select * from v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
-------- -------------- --------------
1 24 28

From the output above, the physical standby database is currently missing logs from sequence 24 to sequence 28 for thread 1. Note that this view only returns the next gap that is currently blocking managed recovery from continuing.

After resolving the identified gap and starting managed recovery, the DBA should query the V$ARCHIVE_GAP view again on the physical standby database to determine the next (if any) gap sequence. This process should be repeated until there are no more gaps.

After identifying a gap (as shown above), the DBA will need to query the primary database to locate the archived redo logs on the primary database. The following query assumes the local archive destination on the primary database is LOG_ARCHIVE_DEST_1:


SELECT name
FROM v$archived_log
WHERE thread# = 1
AND dest_id = 1
AND sequence# BETWEEN 24 and 28;

NAME
--------------------------------------
/u02/oraarchive/TESTDB/arch_t1_s24.dbf
/u02/oraarchive/TESTDB/arch_t1_s25.dbf
/u02/oraarchive/TESTDB/arch_t1_s26.dbf
/u02/oraarchive/TESTDB/arch_t1_s27.dbf
/u02/oraarchive/TESTDB/arch_t1_s28.dbf

Next, we can copy the above redo log files to the physical standby database and register them using the ALTER DATABASE REGISTER LOGFILE ... SQL statement on the physical standby database. For example:

SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s24.dbf';
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s25.dbf';
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s26.dbf';
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s27.dbf';
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s28.dbf';

After the redo logs have been registered on the physical standby database, the DBA can restart the managed recovery operations. For example, to put the physical standby database into automatic recovery managed mode:

SQL> alter database recover managed standby database disconnect from session;


¿Archive log perdido? No reconstruya su Standby


1.Detener la sincronización de la base de datos standby
STDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2.Obtener el SCN al cual se ha llegado en la base de datos standby
STDB> SELECT CURRENT_SCN FROM V$DATABASE;
3.Obtener un backup incremental de la base de datos primaria con RMAN, a partir del SCN obtenido en el paso previo
RMAN> BACKUP INCREMENTAL FROM SCN
DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
4.Catalogar el backup del paso previo en la base de datos standby
RMAN> CATALOG START WITH '/tmp/ForStandby';
5.Recuperar la base de datos standby con el backup ya catalogado
RMAN> RECOVER DATABASE NOREDO;
6.En la base de datos primaria crear un nuevo standby controlfile
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
7.Detener la base de datos standby y levantarla con nomount
RMAN> SHUTDOWN;
RMAN> STARTUP NOMOUNT;
8.Restaurar el standby controlfile obtenido en el paso (6) en la base de datos standby
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
9.Detener la base de datos standby levantarla con mount
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
10.Limpiar los standby redo logs en la base de datos standby
STDB> ALTER DATABASE CLEAR LOGFILE GROUP 1;
STDB> ALTER DATABASE CLEAR LOGFILE GROUP 2;
STDB> ALTER DATABASE CLEAR LOGFILE GROUP 3;
11.Si estaba activo Flashback Database, reiniciarlo
STDB> ALTER DATABASE FLASHBACK OFF;
STDB> ALTER DATABASE FLASHBACK ON;
12.Reiniciar el recovery de la base de datos standby
STDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;


RECUPERAR ARCHIVELOGS PARA APLICAR LUEGO EN STANDBY

Restore Archive Logs to New Location using RMAN
Oracle up to 10g

When attempting to restore archive logs that are already located on disk using RMAN you’ll receive an error if the archivelogs are stored within ASM because until Oracle 11g shipped you could not use the OS copy or move command to retrieve them.

The solution is to use RMAN to copy them from ASM to an OS filesystem:

RMAN> copy archivelog ‘+psdisk1/ORCLSID/archivelog/2008_10_30/thread_1_seq_2.245.88866622′ to ‘/tmp/oraclearchive’;

You would have to repeat this command for each archive log to be copied or try the following solution:

RMAN> change archivelog from logseq=60 until logseq=70 uncatalog;

Once the archivelos have been successfully uncataloged you are now able to successfully restore the archive logs to a new location.

RMAN> run {
set archivelog destination to ‘/tmp’;
restore archivelog from logseq=60 until logseq=70;
}

Following the successful restore of the archivelogs you can then continue to recatalog the archivelogs back into the ASM diskgroup.

Example:

RMAN> catalog archivelog ‘+psdisk1/ORCLSID/archivelog/2008_10_30/thread_1_seq.60.245.888666222′;


COPIAR ARCHIVELOG ASM A DISCO

RMAN> copy archivelog '+FRA/CCPROD/ARCHIVELOG/2011_01_05/thread_1_seq_127538.455.739629505' to '/orahome10/archive';



-- Back up all archived logs from sequence # 250 to sequence # 301 and deletes the archived redo logs after the backup is complete.

run {
allocate channel ch1 type disk format '/tmp/arc_%U';
backup
archivelog from logseq 250 until logseq 301 thread 1
}

In FORMAT clause, %U is replaced with unique file names when creating archivelog backups.

The above RMAN statements create a file in the following format in the destination directory (/etc).

-rw-r----- 1 oracle dba2 46623232 Sep 28 13:14 arc_03kqcj6g_1_1

Then, one can restore archive logs that are already located on a filesystem using restore command:

run {
set archivelog destination to '/tmp';
restore archivelog all;
}