Oracle Database 19c : Demo Construcción de un Data Guard Fácil .

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.

  1. Log in as root. 
  2. Download and install the Oracle Database Preinstallation RPM:
    1. 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
    2. 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.

  1. Conectarse con root
  2. 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!!!