Raúl Antonio Molina Alvarenga
18 de Abril de 2023
#OracleACEAssociate
#OracleTipsSV
Vamos a ver una guía para poder construir rápidamente un Oracle Data Guard en versión 19c.
Para lograrlo use 2 maquinas Compute Instance en una de mis cuentas Always Free Tier.
Host Origen: linux7
Host Destino: standbylinux7
Version Software Oracle Database: 19c (19.3)
Clusterware: No
Multitenant: No
Asumpciones:
- No es detallado en algunos elementos como Preparación de maquinas Virtuales
- Instalación de Software Oracle
- Explicación detallada de Oracle Data Guard
Rápidamente, use la guía de instalación de Oracle 19c con el formato RPM:
Installing Oracle Database RPM Manually
Esto debe realizarse en los N Nodos que formaran el Conjunto Data Guard, para mi ejemplo 2 Nodos.
- Log in as root.
- Download and install the Oracle Database Preinstallation RPM:
-
Descargar el rpm de https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
-
Instalar el software con el rpm # yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
-
La instalación del Software esta ahora completa.
Creación de la Base de datos
Puede hacerse la creación de la Base de Datos en el Servidor Origen, usando el tool del rpm de instalación o lanzar el dbca de forma normal, pongo los 2 ejemplos.
- Conectarse con root
- To configure a sample Oracle Database instance, run the following service configuration script:
# /etc/init.d/oracledb_ORCLCDB-19c configure
Usando el metodo silencioso tipico con dbca:
nohup dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -gdbname emcdb -sid emcdb -responseFile NO_VALUE \ -characterSet AL32UTF8 \ -sysPassword syspassword1 \ -systemPassword syspassword1 \ \ -databaseType MULTIPURPOSE \ -totalMemory 388 \ -storageType FS \ -datafileDestination /u01/app/oracle/oradata \ -redoLogFileSize 600 \ -emConfiguration NONE \ -ignorePreReqs &
-bash-4.2$ cat nohup.out
[WARNING] [DBT-11205] Specified shared pool size does not meet the recommended minimum size requirement. This might cause database creation to fail. ACTION: Specify at least (481 MB) for shared pool size. [WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards. CAUSE: a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. b.The password entered is a keyword that Oracle does not recommend to be used as password ACTION: Specify a strong password. If required refer Oracle documentation for guidelines. [WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards. CAUSE: a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. b.The password entered is a keyword that Oracle does not recommend to be used as password ACTION: Specify a strong password. If required refer Oracle documentation for guidelines. Prepare for db operation 10% complete Copying database files 40% complete Creating and starting Oracle instance 42% complete 46% complete 50% complete 54% complete 60% complete Completing Database Creation 66% complete 69% complete 70% complete Executing Post Configuration Actions 100% complete Database creation complete. For details check the logfiles at: /opt/oracle/cfgtoollogs/dbca/emcdb. Database Information: Global Database Name:emcdb System Identifier(SID):emcdb Look at the log file "/opt/oracle/cfgtoollogs/dbca/emcdb/emcdb.log" for further details. -bash-4.2$
A este punto tenemos correctamente configurada una Base de Datos 19c en el servidor Origen.
Asumiremos, que logramos
- Configuramos la maquina virtual correctamente
- Instalar en el servidor Destino o nodo2 el Software Oracle Database
- Tenemos reglas de transferencia y comunicación entre los nodos (firewall y access list en la VCN).
Ademas, debemos
- Trasladado el Password file de la BD creada , así como el init (mejor spfile) , el tnsnames.ora hacia el servidor destino.
- Con el spfile, iniciamos la instancia en el nodo2
Ahora vamos a Crear la replica, que método usaremos, bueno el ahora método preferido para mi, duplicación via el servicio:
Nota: Durante la clonación, me percate que no tenia en Modo archive la BD Origen.
-bash-4.2$ rman target sys/syspassword1 RMAN> restore standby controlfile from service emcdb; Starting restore at 18-APR-23 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=255 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service emcdb channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/EMCDB/control01.ctl output file name=/u01/app/oracle/oradata/EMCDB/control02.ctl Finished restore at 18-APR-23 RMAN> restore database from service emcdb; Starting restore at 18-APR-23 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=255 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service emcdb channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/EMCDB/system01.dbf RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 04/18/2023 21:12:45 ORA-19845: error in backupDataFile while communicating with remote database server ORA-17628: Oracle error 19602 returned by remote Oracle server ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode ORA-19660: some files in the backup set could not be verified ORA-19661: datafile 1 could not be verified ORA-19845: error in backupDataFile while communicating with remote database server ORA-17628: Oracle error 19602 returned by remote Oracle server ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode SQL> shutdown immediate startup mount; ORACLE instance started. Total System Global Area 524285856 bytes Fixed Size 8898464 bytes Variable Size 285212672 bytes Database Buffers 222298112 bytes Redo Buffers 7876608 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. restore database from service emcdb; Starting restore at 18-APR-23 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=256 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service emcdb channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/EMCDB/system01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service emcdb channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/EMCDB/sysaux01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:16 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service emcdb channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/EMCDB/undotbs01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service emcdb channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/EMCDB/users01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 18-APR-23 RMAN> recover database from service emcdb; Starting recover at 18-APR-23 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=255 device type=DISK skipping datafile 1; already restored to SCN 2065656 skipping datafile 3; already restored to SCN 2065666 skipping datafile 4; already restored to SCN 2065672 skipping datafile 7; already restored to SCN 2065675 starting media recovery RMAN-08187: warning: media recovery until SCN 2064663 complete Finished recover at 18-APR-23
Continuamos, ya tenemos la replica, ahora hay que configurar el paso del Broker, es demasiado util para dejar de usarlo:
dgmgrl / –> a veces es necesario para algunas tareas usar sys y su password.
Me di cuenta que me falto encender el parámetros de broker en ambos sitios
CREATE CONFIGURATION emcdbdrp as primary database is emcdb connect identifier is emcdb;
Error:
ORA-16525: The Oracle Data Guard broker is not yet available.
DGMGRL>
SQL> alter system set dg_broker_start=true;
System altered.
SQL>
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration; Configuration - emcdbdrp Protection Mode: MaxPerformance Members: emcdb - Primary database Warning: ORA-16789: standby redo logs configured incorrectly emcdbr - Physical standby database Warning: ORA-16809: multiple warnings detected for the member Fast-Start Failover: Disabled Configuration Status: WARNING (status updated 36 seconds ago) DGMGRL> show database emcdbr Database - emcdbr Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 1 minute 58 seconds (computed 46 seconds ago) Apply Lag: 1 minute 58 seconds (computed 46 seconds ago) Average Apply Rate: 18.95 MByte/s Real Time Query: OFF Instance(s): emcdb Database Warning(s): ORA-16853: apply lag has exceeded specified threshold ORA-16855: transport lag has exceeded specified threshold ORA-16857: member disconnected from redo source for longer than specified threshold ORA-16789: standby redo logs configured incorrectly Database Status: WARNING
Como PLUS, vamos a activar Active Data Guard (ADG) , en el sitio alterno, o nodo 2 , o sitio secundario:
SQL> alter database open; Database altered. DGMGRL> Database - emcdbr Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 8 minutes 1 second (computed 59 seconds ago) Apply Lag: 8 minutes 1 second (computed 59 seconds ago) Average Apply Rate: 18.95 MByte/s Real Time Query: ON Instance(s): emcdb Database Warning(s): ORA-16853: apply lag has exceeded specified threshold ORA-16855: transport lag has exceeded specified threshold ORA-16857: member disconnected from redo source for longer than specified threshold ORA-16789: standby redo logs configured incorrectly Database Status: WARNING DGMGRL> edit database emcdbr set state=apply-on; Succeeded. DGMGRL> show configuration; Configuration - emcdbdrp Protection Mode: MaxPerformance Members: emcdb - Primary database emcdbr - Physical standby database Warning: ORA-16809: multiple warnings detected for the member Fast-Start Failover: Disabled Configuration Status: WARNING (status updated 47 seconds ago) DGMGRL> show database emcdbr; Database - emcdbr Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 50 minutes 18 seconds (computed 17 seconds ago) Apply Lag: 50 minutes 18 seconds (computed 17 seconds ago) Average Apply Rate: 18.95 MByte/s Real Time Query: ON Instance(s): emcdb Database Warning(s): ORA-16853: apply lag has exceeded specified threshold ORA-16855: transport lag has exceeded specified threshold Database Status: WARNING DGMGRL> sql ' alter system switch logfile' Succeeded. DGMGRL> DGMGRL> show database emcdbr; Database - emcdbr Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Average Apply Rate: 553.00 KByte/s Real Time Query: ON Instance(s): emcdb Database Status: SUCCESS DGMGRL>
Voy a intentar la validación de configuración y sitios antes de intentar el switchover, previo a esto recordé que debo configurar el Flashback, Necesario para FSFO y otros beneficios.
SQL> alter database flashback on; alter database flashback on * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38707: Media recovery is not enabled. SQL> alter database archivelog; alter database archivelog * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in any instance SQL> archive log list; Database log mode No Archive Mode Automatic archival Enabled Archive destination /opt/oracle/product/19c/dbhome_1/dbs/arch Oldest online log sequence 1 Current log sequence 2 SQL> select status,open_mode from v$database,v$instance; STATUS OPEN_MODE ------------ -------------------- OPEN READ ONLY WITH APPLY SQL>
La validación me indica debo cubrir las entradas estáticas que el broker requiere , esto debido que no hay srvctl , o Oracle Restart.
Ademas tenemos otro punto a cubrir, los Standby RedoLogs o SRL.
Recordemos que tenemos dos tipos de Redologs , Online, y Standby.
DGMGRL>validate database emcdb; Database Role: Primary database Ready for Switchover: Yes Managed by Clusterware: emcdb: NO Validating static connect identifier for the primary database emcdb... Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux7.subnet.vcn.oraclevcn.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=emcdb_DGMGRL)(INSTANCE_NAME=emcdb)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE))) ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed. Warning: Ensure primary database's StaticConnectIdentifier property is configured properly so that the primary database can be restarted by DGMGRL after switchover DGMGRL> validate database emcdbr; Database Role: Physical standby database Primary Database: emcdb Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Managed by Clusterware: emcdb : NO emcdbr: NO Validating static connect identifier for the primary database emcdb... Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux7.subnet.vcn.oraclevcn.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=emcdb_DGMGRL)(INSTANCE_NAME=emcdb)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE))) ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed. Warning: Ensure primary database's StaticConnectIdentifier property is configured properly so that the primary database can be restarted by DGMGRL after switchover Log Files Cleared: emcdb Standby Redo Log Files: Cleared emcdbr Online Redo Log Files: Not Cleared emcdbr Standby Redo Log Files: Available Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (emcdb) (emcdbr) 1 3 2 Insufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (emcdbr) (emcdb) 1 3 0 Insufficient SRLs Warning: standby redo logs not configured for thread 1 on emcdb DGMGRL> exit sql-bash-4.2$ dgmgrl / DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 18 22:36:07 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "emcdb" Connected as SYSDG. DGMGRL> validate database emcdr; Object "emcdr" was not found DGMGRL> validate database emcdbr; Database Role: Physical standby database Primary Database: emcdb Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Managed by Clusterware: emcdb : NO emcdbr: NO Validating static connect identifier for the primary database emcdb... Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux7.subnet.vcn.oraclevcn.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=emcdb_DGMGRL)(INSTANCE_NAME=emcdb)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE))) ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed. Warning: Ensure primary database's StaticConnectIdentifier property is configured properly so that the primary database can be restarted by DGMGRL after switchover Log Files Cleared: emcdb Standby Redo Log Files: Cleared emcdbr Online Redo Log Files: Not Cleared emcdbr Standby Redo Log Files: Available Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (emcdb) (emcdbr) 1 3 2 Insufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (emcdbr) (emcdb) 1 3 0 Insufficient SRLs Warning: standby redo logs not configured for thread 1 on emcdb
Las entradas estáticas en el listener.ora quedo dela siguiente manera, recordemos que debemos reiniciar el listener para que ese cambio tome efecto. No usar reload. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linux7.subnet.vcn.oraclevcn.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=emcdb) (GLOBAL_DBNAME=emcdb_DGMGRL) (ORACLE_HOME=/opt/oracle/product/19c/dbhome_1) ) ) -bash-4.2$
Ajustaremos la entrada estatica mas simple en el broker: DGMGRL> edit database emcdb set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=10.0.0.3))(CONNECT_DATA=(SERVICE_NAME= emcdb_DGMGRL)(INSTANCE_NAME=emcdb)(SERVER=DEDICATED)))'; edit database emcdbr set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=10.0.0.4))(CONNECT_DATA=(SERVICE_NAME= emcdbr_DGMGRL)(INSTANCE_NAME=emcdb)(SERVER=DEDICATED)))';
Intentaremos nuevamente la validación: DGMGRL> validate database emcdb; Database Role: Primary database Ready for Switchover: Yes Managed by Clusterware: emcdb: NO Validating static connect identifier for the primary database emcdb... The static connect identifier allows for a connection to database "emcdb". DGMGRL> validate database emcdbr; Database Role: Physical standby database Primary Database: emcdb Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Managed by Clusterware: emcdb : NO emcdbr: NO Validating static connect identifier for the primary database emcdb... The static connect identifier allows for a connection to database "emcdb". Log Files Cleared: emcdb Standby Redo Log Files: Cleared emcdbr Online Redo Log Files: Not Cleared emcdbr Standby Redo Log Files: Available Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (emcdb) (emcdbr) 1 3 2 Insufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (emcdbr) (emcdb) 1 3 0 Insufficient SRLs Warning: standby redo logs not configured for thread 1 on emcdb
Vamos a corregir el tema de los standby redologs en ambos sitios, borramos los que están mal ( quedaron con thread 0), y los agregamos de forma correcta.
Por defecto el dbca crea 3 Online redologs, en este caso de 600mb, y para seguir la regla de la documentacion, debemos crear N+1 standby Redologs, donde N es la cantidad de Online Redologs x Thread, en el caso es un solo thread, porque es SingleInstance.
SQL> select thread#, group#, sequence#, status, bytes from v$standby_log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------- ---------- 0 4 0 UNASSIGNED 629145600 0 5 0 UNASSIGNED 629145600 0 6 0 UNASSIGNED 629145600 0 7 0 UNASSIGNED 629145600 alter database add standby logfile thread 1 size 600m;
Volvemos a intentar la validación:
-bash-4.2$ dgmgrl sys/syspassword1 DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 18 23:23:34 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "emcdb" Connected as SYSDBA. DGMGRL> sql ' alter system switch logfile'; Succeeded. DGMGRL> validate database emcdb; Database Role: Primary database Ready for Switchover: Yes Managed by Clusterware: emcdb: NO Validating static connect identifier for the primary database emcdb... The static connect identifier allows for a connection to database "emcdb". DGMGRL> validate database emcdbr; Database Role: Physical standby database Primary Database: emcdb Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Managed by Clusterware: emcdb : NO emcdbr: NO Validating static connect identifier for the primary database emcdb... The static connect identifier allows for a connection to database "emcdb". Log Files Cleared: emcdb Standby Redo Log Files: Cleared emcdbr Online Redo Log Files: Not Cleared emcdbr Standby Redo Log Files: Available DGMGRL>
Intentemos el Swicthover:
DGMGRL> switchover to emcdbr;
Performing switchover NOW, please wait...
Operation requires a connection to database "emcdbr"
Connecting ...
Connected to "emcdbr"
Connected as SYSDBA.
New primary database "emcdbr" is opening...
Operation requires start up of instance "emcdb" on database "emcdb"
Starting instance "emcdb"...
Connected to an idle instance.
ORACLE instance started.
Connected to "emcdb"
Database mounted.
Database opened.
Connected to "emcdb"
Switchover succeeded, new primary is "emcdbr"
DGMGRL>
Del log, en la secuencia de tiempo, podemos notar que demoro menos de 2 Minutos:
SWITCHOVER VERIFY COMPLETE 2023-04-18T23:27:09.914922+00:00 Additional information: 7 2023-04-18T23:27:14.836244+00:00 rfs (PID:8201): Selected LNO:5 for T-1.S-11 dbid 888512743 branch 1134441066 2023-04-18T23:27:15.108620+00:00 PR00 (PID:7203): Resetting standby activation ID 888530151 (0x34f5e4e7) 2023-04-18T23:27:15.126778+00:00 Media Recovery End-Of-Redo indicator encountered .... TMI: kcv_commit_to_so_to_primary Switchover from physical END 2023-04-18 23:28:06.692280 SWITCHOVER: completed request from primary database. 2023-04-18T23:28:12.732013+00:00 ALTER SYSTEM SET fal_server='' SCOPE=BOTH; 2023-04-18T23:28:12.735019+00:00 Starting background process NSV1 2023-04-18T23:28:12.749558+00:00 NSV1 started with pid=26, OS id=8428 2023-04-18T23:28:14.946580+00:00 ARC0 (PID:5163): Becoming the 'no SRL' ARCH 2023-04-18T23:28:16.040815+00:00 RSM0 (PID:5182): Using STANDBY_ARCHIVE_DEST parameter default value as /opt/oracle/product/19c/dbhome_1/dbs/arch [krsd.c:18141] 2023-04-18T23:28:16.049990+00:00 ALTER SYSTEM SET log_archive_dest_2='service="emcdb"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="emcdb" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH; 2023-04-18T23:28:16.109045+00:00 ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH; ALTER DATABASE OPEN
Validamos el cambio correcto:
DGMGRL> show database emcdb; Database - emcdb Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 1 second (computed 0 seconds ago) Average Apply Rate: 5.00 KByte/s Real Time Query: ON Instance(s): emcdb Database Status: SUCCESS DGMGRL> show database emcdbr; Database - emcdbr Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): emcdb Database Status: SUCCESS DGMGRL>
Con este tutorial, vamos a terminar de complementarlo, cuando se hace cambio de rol, debe considerarse si las Apps lograran conectarse a un nuevo HOST, IP, datos de otro sitio, otro servicio, etc, etc; Oracle provee una solución para garantizar la menor intervención de DBA, de SYSADMIN, de NetworkAdmin, etc: Client failover connectivity .
De La documentacion:
Pre 11.2 Configuration:
Recordemos que en este ejemplo no tenemos SIHA ni Clusterware, entonces debemos considerar que el usuario el servicio debe manejarse solo por funcionamiento de la DB, y eso lo lograremos con un trigger en la misma DB.
La idea es, intervenir lo menos posible en un evento de switchover o Failover, esto es requerido, porque el servicio default de BD, de PBD, no funciona correctamente.
Crear en la primaria los servicios para ser usados por el respectivo rol:
+ On the current primary, create 2 services, one to connect to the primary (prim_db) and another (stby_db) to connect to the read only standby:
SQL> exec dbms_service.create_service('prim_db','prim_db');
SQL> exec dbms_service.create_service('stby_db','stby_db');
Iniciar el servicio en la primaria:
+ On the current primary, start the service that is needed to connect to primary:
SQL> exec dbms_service.start_service('prim_db');
+ Now, on the current primary, create the trigger to manage these services accordingly:
CREATE OR REPLACE TRIGGER startDgServices after startup on database DECLARE db_role VARCHAR(30); db_open_mode VARCHAR(30); BEGIN SELECT DATABASE_ROLE, OPEN_MODE INTO db_role, db_open_mode FROM V$DATABASE; IF db_role = 'PRIMARY' THEN DBMS_SERVICE.START_SERVICE('prim_db'); END IF; IF db_role = 'PHYSICAL STANDBY' AND db_open_mode LIKE 'READ ONLY%' THEN DBMS_SERVICE.START_SERVICE('stby_db'); END IF; END; /
El tnsnames a usar:
PRIM_DB = (DESCRIPTION = (ADDRESS_LIST = (FAILOVER = ON) (LOAD_BALANCE = OFF) (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = prim_db) ) ) STBY_DB = (DESCRIPTION = (ADDRESS_LIST = (FAILOVER = ON) (LOAD_BALANCE = OFF) (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = stby_db) ) )
Complementaremos este tutorial, con un Video de las pruebas de switchover y un cliente conectandose para validar la cadena de conexión unificada sin intervención manual para ajustar dicha cadena.
Espero les sea de utilidad.
Saludos!!!