Cómo configurar base de datos Standby en +ASM y Data Guard Broker (Oracle 12c)

Requisitos:

• 2 Servidores con Oracle 12c R2 y grid instalado.

• Crear base de datos en servidor primario.

Nombres de servidores e instancias a utilizar:

Servidor para BD primaria: ora12prim (IP: 10.0.0.95)

Servidor para BD standby: ora12stby (IP: 10.0.0.96)

Database Unique Name primaria: chicago

Database Unique Name standby: boston

Instance Name primaria: chicago

Instance Name standby: boston

Database Name primaria: chicago

Database Name standby: chicago

Diskgroups primaria: +DATA, +FRA
Diskgroups standby: +DATA_STBY, +FRA_STBY

Cómo podemos ver, los parametros db_unique_name e instance_name deberan ser diferentes en cada instancia, pero los db_name deberán ser iguales. 

Configuración de parámetros en instancia primaria:

sqlplus / as sysdba

SQL>alter database force logging;

SQL>show parameter db_name;

SQL>alter system set log_archive_config='DG_CONFIG=(chicago,boston)' SCOPE=BOTH SID='*';

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=boston NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston' SCOPE=BOTH SID='*';

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH SID='*';

SQL>ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE SID='*';

SQL>ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 SCOPE=BOTH SID='*';

SQL>ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=BOTH SID='*';

SQL>ALTER SYSTEM SET FAL_SERVER=BOSTON SOCPE=BOTH SID='*';

SQL>ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA_STBY','+DATA' SCOPE=BOTH SID='*';

SQL>ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+FRA_STBY','+FRA' SCOPE=BOTH SID='*';

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH SID='*';

SQL>ALTER SYSTEM SET ARCHIVE_LAG_TARGET=1800 SCOPE=BOTH SID='*';


Agregar standby redo log files (la cantidad recomendada es la cantidad de redo logs normales + 1):

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('+DATA/BOSTON/ONLINELOG/redostby4a.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('+DATA/BOSTON/ONLINELOG/redostby5a.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('+DATA/BOSTON/ONLINELOG/redostby6a.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('+DATA/BOSTON/ONLINELOG/redostby7a.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 8 ('+DATA/BOSTON/ONLINELOG/redostby8a.log') SIZE 200M;

Agregar entradas de TNS en ambos servidores en $ORACLE_HOME/network/admin/tnsnames.ora

BOSTON =
(DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.96)(PORT = 1521)
  )
 (CONNECT_DATA =
  (SERVICE_NAME= boston)
  )
 )

CHICAGO =
(DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.95)(PORT = 1521)
  )
 (CONNECT_DATA =
  (SERVICE_NAME= chicago)
  )
 )

Crear archivo init para la BD standby:

CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;

Crear controlfile para la BD Standby:

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston_stby.ctl';

Copiar controlfile al servidor ora12stby:
scp /tmp/boston_stby.ctl oracle@ora12stby.ctl:/tmp/

Copiar init al servidor ora12stby: 
scp /tmp/initboston.ora oracle@ora12stby.ctl:$ORACLE_HOME/dbs

Hacer backup de la base de datos primaria:

Entrar a RMAN: rman target /

Correr el siguiente script:

run {
backup
format '/tmp/backup/data_%d_%U.bck'
database
plus archivelog
format '/tmp/backup/arc_%d_%U.bck';
}

Copiar archivos al servidor ora12stby: 

scp /tmp/backup/* oracle@ora12stby:/tmp/backup/

Editar el init en el servidor ora12stby y cambiar los siguientes parametros:

*.db_unique_name='boston'

*.fal_server='chicago'

*.log_archive_dest_2='SERVICE=chicago ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago'

*Y todos los parámetros que hacen referencia al diskgroup +DATA por +DATA_STBY / +FRA por +FRA_STBY

Subir instancia boston en ora12stby en estado nomount:

sqlplus / as sysdba

startup nomount;

Entrar en RMAN y ejecutar los siguientes comandos:

rman target /

RMAN>set DBID 1624387693;

RMAN>restore standby controlfile from '/tmp/boston_stby.ctl';

RMAN> sql 'alter database mount';

RMAN>catalog start with '/tmp/backup/';

RMAN>

run

{

set newname for database to '+DATA_STBY';

set archivelog destination to '+FRA_STBY';

restore database;

switch datafile all;

switch tempfile all;

recover database delete archivelog;

}

exit;

shutdown immediate;

}

exit;

}

Entrar en sqlplus y subir la instancia en estado mount:

sqlplus / as sysdba

SQL> startup mount;

Iniciar replica de archive:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Querys para validar secuencias aplicadas:

PRIMARY:

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time

FROM   v$archived_log

ORDER BY sequence#;

STANDBY:

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied

FROM   v$archived_log

ORDER BY sequence#;

Con esto ya tenemos configurada una base de datos standby :)

Configurando el Data Guard Broker:

PRIMARY:

alter system set dg_broker_config_file1='+DATA/chicago/dr1.dat' scope=both sid='*';

alter system set dg_broker_config_file2='+FRA/chicago/dr2.dat' scope=both sid='*';

alter system set dg_broker_start=true scope=both sid='*';

STANDBY:

alter system set dg_broker_config_file1='+DATA_STBY/boston/dr1.dat' scope=both sid='*';

alter system set dg_broker_config_file2='+FRA_STBY/boston/dr2.dat' scope=both sid='*';

alter system set dg_broker_start=true scope=both sid='*';

PRIMARY:

alter system set LOG_ARCHIVE_DEST_2='' scope=both;

STANDBY:

alter system set LOG_ARCHIVE_DEST_2='' scope=both;

Configurar listener en primaria:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME =chicago_DGMGRL)

(SID_NAME =chicago)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)))

Configurar listener en standby:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME =boston_DGMGRL)

(SID_NAME =boston)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)))

listener stop

listener start

alter system set LOG_ARCHIVE_DEST_2='' scope=both;

dgmgrl sys/password

DGMGRL>create configuration 'dg_config' as  primary database is 'chicago' connect identifier is chicago;

DGMGRL>add database 'boston' as connect identifier is boston;

DGMGRL>enable configuration;

DGMGRL>show configuration;