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:
https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/running-rpm-packages-to-install-oracle-database.html#GUID-BB7C11E3-D385-4A2F-9EAF-75F4F0AACF02
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!!!
