Oracle Live

02/04/2016

Montar Dataguard con Dataguard Broker

Filed under: Dataguard — mogukiller @ 1:20 am
Tags: ,

Paso a paso del proceso de creacion y configuración de una base de datos standby gestionada con dataguard broker.

-- Validaciones en la primaria
-- Credenciales utilizadas para SYS
-- Entradas tnsnames para el duplicate.
-- Creamos los directorios necesarios
-- Configuramos el pfile
-- Configuramos el ora password.
-- No mount de la standby
-- Lanzamos el duplicate 
-- Configuramos el spfile
-- Registramos la base de datos en el cluster
-- Creamos los servicios de la base de datos
-- Configuramos los standby redo log en los dos niveles
-- Configuramos el Broker
--- Configuramos el servicio _DGMGRL en el LISTENER_DGMGRL
--- Configuramos las entradas del tnsnames.ora
--- Parametros de inicio para el broker
--- Creamos la configuracion del broker
--- Pruebas de switchover


-- Validaciones en la primaria
show parameter standby_file_management
SELECT FORCE_LOGGING FROM V$DATABASE;

alter system set standby_file_management='AUTO' scope=spfile sid='*';
alter database force logging;

-- Credenciales utilizadas para SYS
alter user sys identified by W3lc0m31;

-- Entradas tnsnames para el duplicate.

'nota: Añadimos la entrada estatica al listener listener_prueba_tq'
SID_LIST_listener_prueba_tq=
    (SID_DESC=
      (ORACLE_HOME=/u01/oradb/rdbms1120/112040)
      (GLOBAL_DBNAME = HRDB0STB)
      (SID_NAME=HRDB01)
    )
	
-- tnsnames del N2
HRDB01_STB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mogube051-vip)(PORT = 50063))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HRDB0STB)
      (UR = A)
    )
  )
  
HRDB01 =
  (DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = mogube041-vip)(PORT = 50061))
      )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HRDB0)(UR=A)  
    )
  )
  
 -- tnsnames del N1
 HRDB01_STB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mogube051-vip)(PORT = 50063))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HRDB0STB)
      (UR = A)
    )
  )
  
 -- Creamos los directorios necesarios
mkdir -p /apps/oradb/audit/HRDB0/adump
 
 cat >/tmp/initHRDB01.ora
 -- Configuramos el pfile
 /*
*.audit_file_dest='/apps/oradb/audit/HRDB0/adump'
*.audit_sys_operations=true;
*.audit_trail='DB'
*.cluster_database_instances=2
*.cluster_database=FALSE				# <<<Necesario para el duplicate
HRDB01.cluster_interconnects='192.168.21.1:192.168.21.2'
HRDB02.cluster_interconnects='192.168.21.3:192.168.21.4'
*.compatible='11.2.0.4.0'
*.control_files='+DG_RECO','+DG_DATOS'	# <<< Necesario para el duplicate
*.control_management_pack_access='DIAGNOSTIC'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_create_file_dest='+DG_DATOS'
*.db_create_online_log_dest_1='+DG_RECO'
*.db_name='HRDB0'
*.db_unique_name='HRDB0STB'
*.diagnostic_dest='/apps/oradb/trc'
HRDB01.instance_name='HRDB01'
HRDB02.instance_name='HRDB02'
HRDB01.instance_number=1
HRDB02.instance_number=2
HRDB01.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = mogube051-vip)(PORT = 50061))','(ADDRESS=(PROTOCOL=TCP)(HOST= mogube051-vip )(PORT=50060))'
HRDB02.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = mogube052-vip)(PORT = 50061))','(ADDRESS=(PROTOCOL=TCP)(HOST= mogube052-vip )(PORT=50060))'
*.log_archive_dest_1='location="+DG_RECO" MANDATORY valid_for=(ALL_LOGFILES,ALL_ROLES)'
*.nls_language='SPANISH'
*.nls_territory='SPAIN'
*.open_cursors=1500
*.optimizer_mode='FIRST_ROWS_10'
*.pga_aggregate_target=6G
*.processes=4200
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.sga_max_size=12G
*.sga_target=12G
*.shared_pool_reserved_size=295279001
*.standby_file_management='AUTO'
HRDB01.thread=1
HRDB02.thread=2
*.undo_management='AUTO'
*.undo_retention=3600
HRDB01.undo_tablespace='UNDO_COLX01'
HRDB02.undo_tablespace='UNDO_COLX02'
 */

-- Configuramos el ora password.
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=W3lc0m31 force=y
SELECT USERNAME FROM V$PWFILE_USERS;
/*
SYS
*/
scp $ORACLE_HOME/dbs/orapwHRDB01 mogube042:$ORACLE_HOME/dbs/orapwHRDB02
scp $ORACLE_HOME/dbs/orapwHRDB01 mogube051:$ORACLE_HOME/dbs/orapwHRDB01
scp $ORACLE_HOME/dbs/orapwHRDB01 mogube052:$ORACLE_HOME/dbs/orapwHRDB02

sqlplus sys/W3lc0m31@HRDB01 as sysdba

-- No mount de la standby
sqlplus / as sysdba 
startup nomount pfile='/tmp/initHRDB01.ora'
sqlplus sys/W3lc0m31@HRDB01_STB as sysdba

-- Lanzamos el duplicate 

rman target sys/W3lc0m31@HRDB01 auxiliary sys/W3lc0m31@HRDB01_STB
/*
connected to target database: HRDB0 (DBID=1635084397)
connected to auxiliary database: HRDB0 (not mounted)
*/

run
{
   allocate channel pri1 type disk;
   allocate channel pri2 type disk;
   allocate channel pri3 type disk;
   allocate channel pri4 type disk;
   allocate auxiliary channel stby1 type disk;
   allocate auxiliary channel stby2 type disk;
   allocate auxiliary channel stby3 type disk;
   allocate auxiliary channel stby4 type disk;
     DUPLICATE TARGET DATABASE 
         FOR STANDBY 
     FROM ACTIVE DATABASE
     DORECOVER nofilenamecheck;
}
/*
Starting Duplicate Db at 2016/03/07 11:53:46
Finished Duplicate Db at 2016/03/07 12:06:51
*/

-- Configuramos el spfile
SQL>create pfile='/tmp/initHRDB01.new.ora' from spfile;
SQL>shutdown abort
grep control_file /tmp/initHRDB01.new.ora
*.cluster_database=TRUE
*.control_files='+DG_RECO/HRDB0stb/controlfile/current.8106.905862459','+DG_DATOS/HRDB0stb/controlfile/current.1801.905862459'
startup mount pfile='/tmp/initHRDB01.ora'
create spfile='+DG_DATOS/HRDB0STB/spfileHRDB0.ora' from pfile='/tmp/initHRDB01.ora';
cd $ORACLE_HOME/dbs
echo "spfile='+DG_DATOS/HRDB0STB/spfileHRDB0.ora'" >initHRDB01.ora
scp initHRDB01.ora mogube052:$PWD/initHRDB02.ora

-- Registramos la base de datos en el cluster

srvctl add database -d HRDB0stb -o /u01/oradb/rdbms1120/112040 -p '+DG_DATOS/HRDB0stb/spfileHRDB0.ora' -r  physical_standby -s MOUNT -a DG_DATOS,DG_RECO
srvctl add instance -d HRDB0stb -i HRDB01 -n mogube051
srvctl add instance -d HRDB0stb -i HRDB02 -n mogube052
srvctl start database -d HRDB0stb

-- Creamos los servicios de la base de datos
ora.HRDB0.HRDB0_jdbc_colx.svc   
ora.HRDB0.HRDB0_online_colx.svc 

srvctl add service -d HRDB0stb  -s HRDB0_jdbc_colx 	-r HRDB01,HRDB02 -l primary
srvctl add service -d HRDB0stb  -s HRDB0_online_colx 	-r HRDB01,HRDB02 -l primary


/*
Resource Name                            Resource Type       Target       State        Node            State Details
---------------------------------------- ------------------- ------------ ------------ --------------- ---------------
ora.HRDB0stb.db                         database            ONLINE       INTERMEDIATE mogube051        Mounted (Closed)
ora.HRDB0stb.db                         database            ONLINE       INTERMEDIATE mogube052        Mounted (Closed)
*/


-- Configuramos los standby redo log en los dos niveles

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 31 ('+DG_DATOS','+DG_RECO') SIZE 2048M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 32 ('+DG_DATOS','+DG_RECO') SIZE 2048M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 33 ('+DG_DATOS','+DG_RECO') SIZE 2048M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 34 ('+DG_DATOS','+DG_RECO') SIZE 2048M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 35 ('+DG_DATOS','+DG_RECO') SIZE 2048M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 36 ('+DG_DATOS','+DG_RECO') SIZE 2048M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 41 ('+DG_DATOS','+DG_RECO') SIZE 2048M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 42 ('+DG_DATOS','+DG_RECO') SIZE 2048M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 43 ('+DG_DATOS','+DG_RECO') SIZE 2048M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 44 ('+DG_DATOS','+DG_RECO') SIZE 2048M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 45 ('+DG_DATOS','+DG_RECO') SIZE 2048M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 46 ('+DG_DATOS','+DG_RECO') SIZE 2048M;

-- Configuramos el Broker

-- Configuramos el servicio _DGMGRL en el LISTENER_DGMGRL

-- mogube051
    (SID_DESC=
      (ORACLE_HOME=/u01/oradb/rdbms1120/112040)
      (GLOBAL_DBNAME = HRDB0STB_DGMGRL)
      (SID_NAME=HRDB01))
-- mogube052
    (SID_DESC=
      (ORACLE_HOME=/u01/oradb/rdbms1120/112040)
      (GLOBAL_DBNAME = HRDB0STB_DGMGRL)
      (SID_NAME=HRDB02))

lsnrctl stop LISTENER_DGMGRL
lsnrctl start LISTENER_DGMGRL

-- mogube041
    (SID_DESC=
      (ORACLE_HOME=/u01/oradb/rdbms1120/112040)
      (GLOBAL_DBNAME = HRDB0_DGMGRL)
      (SID_NAME=HRDB01))
-- mogube042
    (SID_DESC=
      (ORACLE_HOME=/u01/oradb/rdbms1120/112040)
      (GLOBAL_DBNAME = HRDB0_DGMGRL)
      (SID_NAME=HRDB02))
	  
lsnrctl stop LISTENER_DGMGRL
lsnrctl start LISTENER_DGMGRL

-- Configuramos las entradas del tnsnames.ora
-- mogube041/mogube051
HRDB0_JC =
  (DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = mogube041-vip)(PORT = 50061))
         (ADDRESS = (PROTOCOL = TCP)(HOST = mogube042-vip)(PORT = 50061))
      )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HRDB0)(UR=A)  
    )
  )

HRDB0_TA =
  (DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = mogube051-vip)(PORT = 50061))
          (ADDRESS = (PROTOCOL = TCP)(HOST = mogube052-vip)(PORT = 50061))
       )
  (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = HRDB0STB)(UR=A)
  )
)

-- mogube042/mogube052
HRDB0_JC =
  (DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = mogube042-vip)(PORT = 50061))
         (ADDRESS = (PROTOCOL = TCP)(HOST = mogube041-vip)(PORT = 50061))
      )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HRDB0)(UR=A)  
    )
  )

HRDB0_TA =
  (DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = mogube052-vip)(PORT = 50061))
          (ADDRESS = (PROTOCOL = TCP)(HOST = mogube051-vip)(PORT = 50061))
       )
  (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = HRDB0STB)(UR=A)
  )
)
-- Parametros de inicio para el broker

-- HRDB0
ASMCMD> cd +DG_DATOS/HRDB0
ASMCMD> mkdir BROKER

SQL> alter system set dg_broker_config_file1='+DG_DATOS/HRDB0/BROKER/dr1HRDB01.dat' scope=both sid='*';
SQL> alter system set dg_broker_config_file2='+DG_DATOS/HRDB0/BROKER/dr2HRDB01.dat' scope=both sid='*';

SQL> alter system set dg_broker_start=TRUE scope=both sid='*';

-- HRDB0STB
ASMCMD> cd +DG_DATOS/HRDB0STB
ASMCMD> mkdir BROKER

SQL> alter system set dg_broker_config_file1='+DG_DATOS/HRDB0STB/BROKER/dr1HRDB0STB.dat' scope=both sid='*';
SQL> alter system set dg_broker_config_file2='+DG_DATOS/HRDB0STB/BROKER/dr2HRDB0STB.dat' scope=both sid='*';

SQL> alter system set dg_broker_start=TRUE scope=both sid='*';

-- Creamos la configuracion del broker
sqlplus sys/W3lc0m31@HRDB0_JC
dgmgrl sys/W3lc0m31@HRDB0_JC
CREATE CONFIGURATION "DG_HRDB0" AS PRIMARY DATABASE IS "HRDB0" CONNECT IDENTIFIER IS "HRDB0_JC";
ADD DATABASE "HRDB0STB" AS CONNECT IDENTIFIER IS "HRDB0_TA";
ENABLE CONFIGURATION;

DGMGRL> show configuration verbose
/*
Configuration - DG_HRDB0

  Protection Mode: MaxPerformance
  Databases:
    HRDB0    - Primary database
    HRDB0STB - Physical standby database

Configuration Status:
SUCCESS	
*/

DGMGRL> show database verbose "HRDB0STB"
/*
Database - HRDB0STB

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    HRDB01 (apply instance)
    HRDB02
*/

-- Pruebas de switchover
'nota: conectamos siempre a la standby'
dgmgrl sys/W3lc0m31@HRDB0_TA

DGMGRL> switchover to "HRDB0STB"
Performing switchover NOW, please wait...
New primary database "HRDB0STB" is opening...
Operation requires startup of instance "HRDB01" on database "HRDB0"
Starting instance "HRDB01"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "HRDB0STB"

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

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 )

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 )

Google+ photo

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

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: