1. Despliegue de una topología de DR híbrida para una base de datos local
  2. Crear una instancia en espera mediante RMAN

Crear una instancia en espera mediante RMAN

Utilice Oracle Recovery Manager (RMAN) para crear una instancia en la nube en espera a partir de la base de datos primaria.

Crear Log de Archivado

Compruebe el modo de log del servidor Oracle local. Si está en modo NOARCHIVELOG, cámbielo al modo ARCHIVELOG para crear un archive log.

  1. Ver el modo de log del servidor local.
    SQL> select log_mode from v$database;
    La salida será similar a la siguiente cuando el modo de log esté en modo NOARCHIVELOG:
    LOG_MODE
------------
NOARCHIVELOG
  2. Cierre la instancia local.
    SQL> shutdown immediate;
    La salida será similar a la siguiente:
    Database closed.
Database dismounted.
ORACLE instance shut down.
  3. Monte la base de datos local.
    SQL> startup mount;
    La salida será similar a la siguiente:
    ORACLE instance started.

Total System Global Area 4966054520 bytes
Fixed Size		    9144952 bytes
Variable Size		  922746880 bytes
Database Buffers	 4026531840 bytes
Redo Buffers		    7630848 bytes
Database mounted.
  4. Modifique el archive log de la base de datos y, a continuación, abra la base de datos.
    SQL> alter database archivelog;
    SQL> alter database open;
  5. Cambie el modo de log a ARCHIVELOG.
    SQL> select log_mode from v$database;
    LOG_MODE
------------
ARCHIVELOG
  6. Visualice la lista de archive log.
    SQL> archive log list;
    La salida será similar a la siguiente:
    Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /opt/oracle/product/19c/dbhome_1/dbs/arch
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence	       12
  7. Archivar todos los logs.
    SQL> archive log all;
    ORA-00271:there are no logs that need archiving

Instancia de la Base de Datos en Espera en OCI

Utilice Oracle Recovery Manager (RMAN) para duplicar la base de datos primaria activa en Oracle Cloud Infrastructure (OCI). También puede duplicar desde una copia de seguridad de la base de datos primaria.

  1. Detenga la base de datos mediante la utilidad srvctl o cierre la base de datos mediante SQL*Plus.
    • $ srvctl stop database -d standby DB_UNIQUE_NAME -o immediate
    • Shutdown immediate;
  2. Conéctese a la base de datos.
    $ rman target /
RMAN> startup nomount;
  3. Restaurar el archivo de control en espera desde el servicio principal.
    En este ejemplo, ORCLCDB es la base de datos primaria local.
    RMAN> restore standby controlfile from service 'ORCLCDB';
  4. Si aparecen los siguientes mensajes de error, vuelva a conectarse a la base de datos.
    RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/24/2020 21:10:13
RMAN-06496: must use the TO clause when the database is mounted or open
    1. Cierre la base de datos
      RMAN> shutdown;
    2. Conéctese a la base de datos
      RMAN> startup nomount;
  5. Restaurar mediante el archivo de control de base de datos en lugar del catálogo de recuperación.
    RMAN> restore standby controlfile from service 'ORCLCDB';
    La salida será similar a la siguiente:
    Starting restore at DD-MM-YY
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=182 device type=DISK 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLCDB
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u02/app/oracle/oradata/ORCLCDB_iad1s3/control01.ctl
output file name=/u03/app/oracle/fast_recovery_area/ORCLCDB_IAD1S3/control02.ctl
Finished restore at DD-MM-YY
  6. Monte la base de datos.
    RMAN> alter database mount;
  7. Si aparece un mensaje de error similar al siguiente, vuelva a conectarse a la base de datos.
    Si no recibe un mensaje de error, vaya al paso 8.
    Released channel: ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 09/24/2020 21:13:41
RMAN-06189: current DBID 2822997440 does not match target mounted database (2823031995)
    1. Cierre la base de datos
      RMAN> shutdown immediate;
      La salida será similar a la siguiente:
      database dismounted
Oracle instance shutdown
    2. Conéctese a la base de datos
      RMAN> startup nomount;
      La salida será similar a la siguiente:
      connected to target database (not started)
Oracle instance startedTotal System Global Area 14495511760 bytesFixed Size 9151696 bytes
Variable Size 1912602624 bytes
Database Buffers 12549357568 bytes
Redo Buffers 24399872 bytes
    3. Defina el identificador de la base de datos.
      RMAN> set DBID=2823031995;
      La salida será similar a la siguiente:
      executing command: SET DBID
  8. Como raíz en la instancia de OCI, cambie los permisos del directorio Oracle a abrir (chmod 777).
    [root@drdb opc]# sudo su
[root@drdb opc]# cd /opt/
[root@drdb opt]# ls -lrt
total 8
drwx------ 7 root root 4096 Sep 12 00:39 zookeeper
drwxr-xr-x 6 root root 4096 Sep 12 00:51 oracle
[root@drdb opt]# chmod 777 oracle/
[root@drdb opt]# ls -lrt
total 8
drwx------ 7 root root 4096 Sep 12 00:39 zookeeper
drwxrwxrwx 6 root root 4096 Sep 12 00:51 oracle
  9. Restaurar la base de datos desde la base de datos primaria (ORCLCDB).
    RMAN> restore database from service 'ORCLCDB' section size 5G;
    Después de completar la copia de seguridad de comprobación cruzada y la copia de comprobación cruzada, aparece una lista de archivos catalogados en la salida, similar a lo siguiente:
    Starting restore at 01-SEP-20
Starting implicit crosscheck backup at 01-SEP-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=184 device type=DISK
Finished implicit crosscheck backup at 01-SEP-20

Starting implicit crosscheck copy at 01-SEP-20
using channel ORA_DISK_1
Finished implicit crosscheck copy at 01-SEP-20

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u03/app/oracle/fast_recovery_area/ORCLCDB_IAD1S3/archivelog/2020_08_27/o1_mf_1_3_hngo1gl6_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCLCDB_IAD1S3/archivelog/2020_08_27/o1_mf_1_1_hngnvtgb_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCLCDB_IAD1S3/archivelog/2020_08_27/o1_mf_1_2_hngnwdfd_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/ORCLCDB/system01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/ORCLCDB/sysaux01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/ORCLCDB/undotbs01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
channel ORA_DISK_1: restoring section 1 of 1
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 ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
channel ORA_DISK_1: restoring section 1 of 1
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 ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 01-SEP-20
  10. Cierre la base de datos.
    RMAN> Shutdown immediate;
  11. Reinicie la base de datos en espera mediante la utilidad Server Control (srvctl) o SQL*Plus.
    • Utilice la utilidad srvctl para iniciar la base de datos: 
      srvctl start instance -d standby DB_UNIQUE_NAME -i standby instance name -o mount
    • Utilice SQL*Plus para iniciar la base de datos:
      $ ./sqlplus / as sysdba
  12. Monte la base de datos.
    SQL> startup mount
    La salida será similar a la siguiente:
    ORACLE instance started.

Total System Global Area 1.4496E+10 bytes
Fixed Size		    9151696 bytes
Variable Size		 1912602624 bytes
Database Buffers	 1.2549E+10 bytes
Redo Buffers		   24399872 bytes
Database mounted.
  13. Modificar base de datos.
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
    Database altered.
  14. Ver el rol, el número de thread, el número de secuencia y la acción de la base de datos.
    SQL> SELECT ROLE, THREAD#, SEQUENCE#, ACTION FROM V$DATAGUARD_PROCESS;
    La salida será similar a la siguiente:
    ROLE			    THREAD#  SEQUENCE# ACTION
------------------------ ---------- ---------- ------------
post role transition		  0	     0 IDLE
recovery apply slave		  0	     0 IDLE
recovery apply slave		  0	     0 IDLE
recovery apply slave		  0	     0 IDLE
recovery apply slave		  0	     0 IDLE
recovery logmerger		  1	     7 WAIT_FOR_LOG
managed recovery		  0	     0 IDLE
archive redo			  0	     0 IDLE
archive local			  0	     0 IDLE
redo transport timer		  0	     0 IDLE
gap manager			  0	     0 IDLE
archive redo			  0	     0 IDLE
archive redo			  0	     0 IDLE
redo transport monitor	0	     0 IDLE
log writer			  0	     0 IDLE

15 rows selected.
  15. (Opcional) Ver el nombre y el rol de la base de datos.
    SQL> select name, database_role from v$database;
    La salida será similar a la siguiente:
    NAME	  DATABASE_ROLE
--------- ----------------
ORCLCDB   PHYSICAL STANDBY
  16. Como sysdba, borre todos los logs redo en espera en línea
    $ sqlplus “/ as sysdba” 
SQL> alter system set db_create_online_log_dest_1=<DATA Disk group>; 
SQL> set pagesize 0 feedback off linesize 120 trimspool on 
SQL> spool /tmp/clearlogs.sql 
SQL> select distinct 'alter database clear logfile group '||group#||';' from v$logfile; 
SQL> spool off 
SQL> @/tmp/clearlogs.sql 
SQL> select member from v$logfile;

Agregar Instancias RAC a Archivos tnsnames.ora

Para Oracle Real Application Clusters (Oracle RAC), agregue las instancias de Oracle RAC a tnsnames.ora y reinicie los listeners. El nombre de exploración no se puede resolver en ninguna dirección en una configuración híbrida, lo que hace necesario configurar la lista de direcciones para proporcionar alta disponibilidad.

  1. Edite el archivo TNSNAMES.ORA en el host de la base de datos local.
    Primary db_unique_name = (DESCRIPTION = 
(SDU=65536) (RECV_BUF_SIZE=134217728) 
(SEND_BUF_SIZE=134217728) 
(ADDRESS_LIST = 
		   (FAILOVER=on)
(CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
(ADDRESS = (PROTOCOL = TCP)(HOST = PrimaryDB Node 1IP address)(PORT = {1521|port#})) 
(ADDRESS = (PROTOCOL = TCP)(HOST = StandbyDB Node 2IP address)(PORT = {1521|port#})) 
) 
(CONNECT_DATA = 
(SERVER = DEDICATED) 
(SERVICE_NAME = Primary_db_SID) 
(UR=A) 
)) 

Standby db_unique_name = (DESCRIPTION = 
(SDU=65536) (RECV_BUF_SIZE=134217728) 
(SEND_BUF_SIZE=134217728) 
(ADDRESS_LIST = 
		   (FAILOVER=on)
(CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
(ADDRESS = (PROTOCOL = TCP)(HOST = StandbyDB Node 1IP address)(PORT = {1521|port#})) 
(ADDRESS = (PROTOCOL = TCP)(HOST = StandbyDB Node 2IP address)(PORT = {1521|port#})) 
) 
(CONNECT_DATA = 
(SERVER = DEDICATED) 
(SERVICE_NAME = Primary_db_SID) 
(UR=A) 
))
  2. Edite el archivo TNSNAMES.ORA en el host de la base de datos en espera en Oracle Cloud Infrastructure (OCI).
    Standby db_unique_name = (DESCRIPTION = 
(SDU=65536) (RECV_BUF_SIZE=134217728) 
(SEND_BUF_SIZE=134217728) 
(ADDRESS_LIST = 
		   (FAILOVER=on)
(CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
(ADDRESS = (PROTOCOL = TCP)(HOST = StandbyDB Node 1IP address)(PORT = {1521|port#})) 
(ADDRESS = (PROTOCOL = TCP)(HOST = StandbyDB Node 2IP address)(PORT = {1521|port#})) 
) 
(CONNECT_DATA = 
(SERVER = DEDICATED) 
(SERVICE_NAME = Primary_db_SID) 
(UR=A) 
)) 

<Primary db_unique_name> = (DESCRIPTION = 
(SDU=65536) (RECV_BUF_SIZE=134217728) 
(SEND_BUF_SIZE=134217728) 
(ADDRESS_LIST = 
		   (FAILOVER=on)
(CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
(ADDRESS = (PROTOCOL = TCP)(HOST = PrimaryDB Node 1IP address)(PORT = {1521|port#})) 
(ADDRESS = (PROTOCOL = TCP)(HOST = StandbyDB Node 2IP address)(PORT = {1521|port#})) 
) 
(CONNECT_DATA = 
(SERVER = DEDICATED) 
(SERVICE_NAME = Primary_db_SID) 
(UR=A) 
))