Creare un'istanza in standby mediante RMAN

Utilizzare Oracle Recovery Manager (RMAN) per creare un'istanza cloud in standby dal database primario.

Crea un log di archivio

Controllare la modalità di log del server Oracle in locale. Se è in modalità NOARCHIVELOG, modificarla in modalità ARCHIVELOG per creare un log di archivio.

  1. Visualizzare la modalità di log per il server locale.
    SQL> select log_mode from v$database;
    L'output sarà simile al seguente quando la modalità di log è in modalità NOARCHIVELOG:
    LOG_MODE
    ------------
    NOARCHIVELOG
  2. Chiudere l'istanza in locale.
    SQL> shutdown immediate;
    L'output sarà simile al seguente:
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
  3. Installare il database in locale.
    SQL> startup mount;
    L'output sarà simile al seguente:
    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. Modificare il log di archivio del database, quindi aprire il database.
    SQL> alter database archivelog;
    SQL> alter database open;
  5. Modificare la modalità di log in ARCHIVELOG.
    SQL> select log_mode from v$database;
    LOG_MODE
    ------------
    ARCHIVELOG
  6. Visualizza la lista di log di archivio.
    SQL> archive log list;
    L'output sarà simile al seguente:
    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. Archivia tutti i log.
    SQL> archive log all;
    ORA-00271:there are no logs that need archiving

Crea un'istanza del database in standby su OCI

Utilizzare Oracle Recovery Manager (RMAN) per duplicare il database primario attivo su Oracle Cloud Infrastructure (OCI). In alternativa, è possibile duplicare da un backup del database primario.

  1. Arrestare il database utilizzando la utility srvctl o chiudere il database utilizzando SQL*Plus.
    • $ srvctl stop database -d standby DB_UNIQUE_NAME -o immediate
    • Shutdown immediate;
  2. connettersi al database;
    $ rman target /
    RMAN> startup nomount;
  3. Ripristinare il control file in standby dal servizio primario.
    In questo esempio ORCLCDB è il database primario in locale.
    RMAN> restore standby controlfile from service 'ORCLCDB';
  4. Se vengono visualizzati i seguenti messaggi di errore, riconnettersi al database.
    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. Chiudi il database
      RMAN> shutdown;
    2. connettersi al database;
      RMAN> startup nomount;
  5. Ripristinare utilizzando il control file del database invece del Recovery Catalog.
    RMAN> restore standby controlfile from service 'ORCLCDB';
    L'output sarà simile al seguente:
    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. Eseguire il MOUNT del database.
    RMAN> alter database mount;
  7. Se viene visualizzato un messaggio di errore simile al seguente, riconnettersi al database.
    Se non viene visualizzato un messaggio di errore, passare al Passo 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. Chiudi il database
      RMAN> shutdown immediate;
      L'output sarà simile al seguente:
      database dismounted
      Oracle instance shutdown
    2. connettersi al database;
      RMAN> startup nomount;
      L'output sarà simile al seguente:
      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. Impostare l'identificativo del database.
      RMAN> set DBID=2823031995;
      L'output sarà simile al seguente:
      executing command: SET DBID
  8. Come radice nell'istanza OCI, modificare le autorizzazioni della directory Oracle da aprire (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. Ripristinare il database dal database primario (ORCLCDB).
    RMAN> restore database from service 'ORCLCDB' section size 5G;
    Al termine del backup e della copia del controllo incrociato, nell'output viene visualizzata una lista di file catalogati, simile a quanto riportato di seguito.
    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. Chiudere il database.
    RMAN> Shutdown immediate;
  11. Riavviare il database in standby utilizzando la utility Server Control (srvctl) o SQL*Plus.
    • Utilizzare la utility srvctl per avviare il database:
      srvctl start instance -d standby DB_UNIQUE_NAME -i standby instance name -o mount
    • Utilizzare SQL*Plus per avviare il database:
      $ ./sqlplus / as sysdba
  12. Eseguire il MOUNT del database.
    SQL> startup mount
    L'output sarà simile al seguente:
    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. Modifica database.
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
    Database altered.
  14. Visualizzare il ruolo, il numero di thread, il numero di sequenza e l'azione per il database.
    SQL> SELECT ROLE, THREAD#, SEQUENCE#, ACTION FROM V$DATAGUARD_PROCESS;
    L'output sarà simile al seguente:
    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. (Facoltativo) Visualizzare il nome e il ruolo del database.
    SQL> select name, database_role from v$database;
    L'output sarà simile al seguente:
    NAME	  DATABASE_ROLE
    --------- ----------------
    ORCLCDB   PHYSICAL STANDBY
  16. Come sysdba, cancellare tutti i log redo in standby in linea
    $ 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;

Aggiungi istanze RAC ai file tnsnames.ora

Per Oracle Real Application Clusters (Oracle RAC), aggiungere le istanze di Oracle RAC a tnsnames.ora e riavviare i listener. Il nome di scansione non può essere risolto in entrambe le direzioni in una configurazione ibrida, il che rende necessario configurare la lista di indirizzi per fornire alta disponibilità.

  1. Modificare il file TNSNAMES.ORA sull'host del database in locale.
    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. Modificare il file TNSNAMES.ORA sull'host del database in standby in 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) 
    ))