- Distribuire una topologia DR ibrida per un Oracle Exadata in locale
- Crea un'istanza in standby
Crea un'istanza in standby
Creare un'istanza cloud in standby dal database primario.
Crea un log di archivio
Controllare la modalità di log di Oracle Exadata Database Machine in locale. Se è in modalità NOARCHIVELOG, modificarla in modalità ARCHIVELOG per creare un log di archivio.
- Visualizzare la modalità di log per il server in locale.
SQL> select log_mode from v$database;L'output sarà simile a quello riportato di seguito quando la modalità log è in modalitàNOARCHIVELOG.LOG_MODE ------------ NOARCHIVELOG - Chiudere l'istanza in locale.
SQL> shutdown immediate;L'output sarà simile a quanto segue:Database closed. Database dismounted. ORACLE instance shut down. - Installare il database in locale.
SQL> startup mount;L'output sarà simile a quanto segue: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. - Modificare il log dell'archivio del database, quindi aprire il database.
SQL> alter database archivelog;SQL> alter database open; - Modificare la modalità di log in
ARCHIVELOG.SQL> select log_mode from v$database;LOG_MODE ------------ ARCHIVELOG - Visualizza la lista dei log di archivio.
SQL> archive log list;L'output sarà simile a quanto segue:Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 4 Next log sequence to archive 7 Current log sequence 7 - Archivia tutti i log.
SQL> archive log all;ORA-00271:there are no logs that need archiving
Creazione di un'istanza del database in standby su OCI
Creazione di un'istanza del database Oracle Database Exadata Cloud Service in standby.
- Arrestare o chiudere il database Oracle Database Exadata Cloud Service utilizzando la utility
srvctl.-
$ srvctl stop database -d standby DB_UNIQUE_NAME -o immediate -
$ srvctl stop database -d DB1_phx3g7 -o immediate
-
- connettersi al database;
SQL> startup nomount; - In Oracle Exadata Database Machine primario in locale, eseguire la seguente serie di comandi per forzare il log e definire il file di log.
SQL> alter database force logging;SQL> set linesize 500 pages 0 SQL> col value for a90 SQL> col name for a50 SQL> select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_de st_state_2', 'remote_login_passwordfile', 'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert' ,'log_file_name_convert', 'standby_file_management');L'output sarà simile a quanto segue:db_file_name_convert log_file_name_convert log_archive_dest_1 location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) MAX_FAILURE=1 REOPEN =5 DB_UNIQUE_NAME=db1 ALTERNATE=LOG_ARCHIVE_DEST_2 log_archive_dest_2 location=+DATAC1 valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db1 ALTERNATE=LOG_ARCHI VE_DEST_1 log_archive_dest_state_1 ENABLE log_archive_dest_state_2 ALTERNATE fal_server log_archive_config log_archive_format %t_%s_%r.dbf log_archive_max_processes 4 standby_file_management MANUAL remote_login_passwordfile EXCLUSIVE db_name db1 db_unique_name db1 14 rows selected. - Impostare la gestione dei file in standby.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*'; - In Oracle Exadata Database Machine primario in locale definire i parametri riportati di seguito per l'archivio di log e la gestione in standby.
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD1,STAND1)' LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD1' LOG_ARCHIVE_DEST_2='SERVICE=STAND1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAND1' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=8 FAL_SERVER=STAND1 DB_FILE_NAME_CONVERT='STAND1',' PROD1' LOG_FILE_NAME_CONVERT= 'STAND1',' PROD1' STANDBY_FILE_MANAGEMENT=AUTOIl seguente esempio include i valori dei parametri:ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB1, DB1_phx3g7)'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DB1'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DB1_phx3g7 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB1_phx3g7’; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER; ALTER SYSTEM SET FAL_SERVER=DB1_phx3g7; ALTER SYSTEM SET FAL_CLIENT=DB1; ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB1_phx3g7','DB1' scope=spfile; ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB1_phx3g7','DB1' scope=spfile; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; - In Oracle Exadata Database Machine primario in locale, eseguire il login come
sysdba, quindi eseguire la seguente serie di comandi per forzare il log.$ sqlplus / as sysdbaSQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB1, DB1_phx3g7)'; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DB1'; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DB1_phx3g7 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB1_phx3g7'; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER; System altered. SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE;ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SQL> ALTER SYSTEM SET FAL_SERVER=DB1_phx3g7; System altered. SQL> ALTER SYSTEM SET FAL_CLIENT=DB1; System altered. SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB1_phx3g7','DB1' scope=spfile; System altered. SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT= 'DB1_phx3g7','DB1' scope=spfile; System altered. SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; System altered.Non crearepfilesu Oracle Exadata Database Machine primario in locale. Utilizzare invecePfilepredefinito creato da Oracle Database Exadata Cloud Service. - In Oracle Database Exadata Cloud Service in standby eseguire il login come
sysdba, quindi avviare l'istanza senza eseguire il MOUNT del database.$ sqlplus / as sysdbaSQL> startup nomount; - In Oracle Database Exadata Cloud Service in standby eseguire la seguente serie di comandi per definire il server, il log e la gestione dei file.
alter system set fal_server='DB1' scope =spfile; alter system set fal_client='DB1_PHX3G7' scope =spfile; alter system set log_archive_config='DG_CONFIG=(DB1, DB1_phx3g7)' scope =spfile; alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DB1_PHX3G7' scope =spfile; alter system set log_archive_dest_2='SERVICE=DB1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB1' scope =spfile; alter system set log_archive_dest_state_1='ENABLE' scope =spfile; alter system set log_archive_dest_state_2='ENABLE' scope =spfile; alter system set log_archive_format='%t_%s_%r.dbf' scope =spfile; alter system set log_file_name_convert='DB1','DB1_phx3g7' scope =spfile; alter system set db_file_name_convert='DB1','DB1_phx3g7' scope =spfile; alter system set remote_login_passwordfile='exclusive' scope =spfile; alter system set standby_file_management='AUTO' scope =spfile; - In Oracle Database Exadata Cloud Service in standby utilizzare Oracle Recovery Manager (RMAN) per definire una destinazione di test e un database in standby nel cloud, quindi eseguire RMAN per creare il database in standby.
rman target sys/Test_Dr_123@DB1 auxiliary sys/Test_Dr_123@DB1_Phx3g7L'output sarà simile a quanto segue:connected to target database: DB1 (DBID=1657313530) connected to auxiliary database: DB1 (not mounted)RMAN> run - In Oracle Database Exadata Cloud Service in standby eseguire il login come
sysdba, quindi disconnettersi dalla sessione.$ sqlplus / as sysdbaSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; - In Oracle Database Exadata Cloud Service in standby, visualizzare i log per verificare che il recupero del supporto sia in transito.
[oracle@exacs82-vm3sv1 DB11]$ pwd /u02/app/oracle/diag/rdbms/db1_phx3g7/DB11[oracle@exacs82-vm3sv1 trace]$ tail -f alert_DB11.logL'output includerà una linea simile a quella riportata di seguito.PR00 (PID:253670): Media Recovery Waiting for T-1.S-125 (in transit) - Nella Oracle Exadata Database Machine locale primaria eseguire il login a sqlplus come
sysdba, quindi eseguire la seguente serie di comandi SQL per cambiare il file di log.$ sqlplus / as sysdbaSQL> alter system switch logfile; SQL> / SQL> / SQL> / - In Oracle Database Exadata Cloud Service in standby eseguire il login come
sysdba, quindi eseguire la seguente serie di comandi.SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD#,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 124 124 0 2 26 26 0SQL> /Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 124 124 0 2 26 26 0SQL> /Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 130 128 2 2 27 27 0SQL> /Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 130 128 2 2 27 27 0SQL> /Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 130 130 0 2 28 28 0 - In Oracle Database Exadata Cloud Service in standby eseguire il login come
sysdba, quindi eseguire la seguente serie di comandi.SQL> alter database recover managed standby database cancel;SQL> show parameter clusterNAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cdb_cluster boolean FALSE cdb_cluster_name string cluster_database boolean FALSE cluster_database_instances integer 1 cluster_interconnects string 192.0.2.22:192.0.2.23 SQL> alter system set cluster_database=TRUE scope=spfile; System altered.SQL> alter system set cluster_database=TRUE scope=spfile;SQL> show parameter cluster;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cdb_cluster boolean FALSE cdb_cluster_name string cluster_database boolean FALSE cluster_database_instances integer 1 cluster_interconnects string 192.0.2.22:192.0.2.23 SQL> / System altered.SQL> show parameter pluggable;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_pluggable_database boolean FALSE SQL> shutdown; ORA-01109: database not open Database dismounted. ORACLE instance shut down. - In Oracle Database Exadata Cloud Service in standby, visualizzare lo stato delle risorse e confermare che sono tutte in linea.
[grid@exacs82-vm3sv1 ~]$ crsctl stat resL'output includerà il tipo, lo stato di destinazione e lo stato per ogni risorsa. Ad esempio:NAME=ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup) TYPE=ora.asm_listener.type TARGET=ONLINE , ONLINE STATE=ONLINE on exacs82-vm3sv1, ONLINE on exacs82-vm3sv2 NAME=ora.DATAC1.ACFSVOL01.advm TYPE=ora.volume.type TARGET=ONLINE , ONLINE STATE=ONLINE on exacs82-vm3sv1, ONLINE on exacs82-vm3sv2 NAME=ora.DATAC1.dg(ora.asmgroup) TYPE=ora.diskgroup.type TARGET=ONLINE , ONLINE STATE=ONLINE on exacs82-vm3sv1, ONLINE on exacs82-vm3sv2 NAME=ora.LISTENER.lsnr TYPE=ora.listener.type TARGET=ONLINE , ONLINE STATE=ONLINE on exacs82-vm3sv1, ONLINE on exacs82-vm3sv2 . . . - In Oracle Database Exadata Cloud Service in standby avviare e montare il database.
[oracle@exacs82-vm3sv1 ~]$ source DB1.env [oracle@exacs82-vm3sv1 ~]$ srvctl start database -db db1_phx3g7 -startoption mount - Sul nodo 1 di Oracle Database Exadata Cloud Service in standby, eseguire il login come
sysdba, eseguire il MOUNT del database inopen_mode, quindi uscire.oracle@exacs82-vm3sv1SQL> select open_mode from v$database;L'output sarà simile a quanto segue:OPEN_MODE -------------------- MOUNTEDSQL> exit - Sul nodo 2 di Oracle Database Exadata Cloud Service in standby, eseguire il login come
sysdba, eseguire il MOUNT del database inopen_mode, quindi uscire.oracle@exacs82-vm3sv2SQL> select open_mode from v$database;L'output sarà simile a quanto segue:OPEN_MODE -------------------- MOUNTEDSQL> exit - Disconnettersi dalla sessione.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;L'output sarà simile a quanto riportato di seguito. - In Oracle Exadata Database Machine primario in locale, eseguire la seguente serie di comandi SQL per il file di log.
SQL> alter database force logging;SQL> /SQL> / - In Oracle Database Exadata Cloud Service in standby eseguire il login come
sysdba, quindi eseguire la serie di comandi riportata di seguito.SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD#,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 161 161 0 2 41 41 0SQL> /Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 161 161 0 2 41 41 0SQL> /Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 161 161 0 2 41 41 0SQL> /Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 163 161 2 2 41 41 0SQL> /Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 163 161 2 2 41 41 0SQL> /Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 163 163 0 2 42 42 0 - In Oracle Exadata Database Machine primario in locale, verificare che il file
tnsnames.orasia configurato correttamente per quanto riportato di seguito.
VerificareDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = exa11-scan1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1.us.osc.oracle.com) (INSTANCE_NAME = db12) ) ) DB1_phx3g7 = (DESCRIPTION = (SDU = 65536) (RECV_BUF_SIZE = 134217728) (SEND_BUF_SIZE = 134217728) (ADDRESS_LIST = (CONNECT_TIMEOUT = 3) (RETRY_COUNT = 3) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.255.195.136)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB1_phx3g7.datasubnetactiv.oscphevcn.oraclevcn.com) (UR = A) ) )tnsnamesper il database in locale (db1):[oracle@exa11db01 admin]$ tnsping db1
VerificareUsed parameter files: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = exa11-scan1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1.us.osc.oracle.com) (INSTANCE_NAME = db11))) OK (0 msec)tnsnamesper l'istanza di Oracle Database Exadata Cloud Service (DB1_phx3g7):[oracle@exa11db01 admin]$ tnsping DB1_phx3g7TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 05-FEB-2021 16:19:15 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (SDU = 65536) (RECV_BUF_SIZE = 134217728) (SEND_BUF_SIZE = 134217728) (ADDRESS_LIST = (CONNECT_TIMEOUT = 3) (RETRY_COUNT = 3) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.255.195.136)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB1_phx3g7.datasubnetactiv.oscphevcn.oraclevcn.com) (UR = A))) OK (40 msec) - In Oracle Database Exadata Cloud Service in standby verificare che il file
tnsnames.orasia configurato correttamente per quanto riportato di seguito.
Verificaredb1 = (DESCRIPTION = (SDU=65536) (RECV_BUF_SIZE=134217728) (SEND_BUF_SIZE=134217728) (ADDRESS_LIST = (CONNECT_TIMEOUT=3)(RETRY_COUNT=3) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.136.74.18)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1.us.osc.oracle.com) (UR=A) ) ) DB1_phx3g7 = (DESCRIPTION = (SDU = 65536) (RECV_BUF_SIZE = 134217728) (SEND_BUF_SIZE = 134217728) (ADDRESS_LIST = (CONNECT_TIMEOUT = 3) (RETRY_COUNT = 3) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.255.195.136)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB1_phx3g7.datasubnetactiv.oscphevcn.oraclevcn.com) (INSTANCE_NAME = DB12) (UR = A) ) )tnsnamesper il database in locale (db1):[oracle@exacs82-vm3sv2 DB1]$ tnsping db1
VerificareTNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 05-FEB-2021 16:34:59 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u02/app/oracle/product/19.0.0.0/dbhome_5/network/admin/DB1/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (SDU=65536) (RECV_BUF_SIZE=134217728) (SEND_BUF_SIZE=134217728) (ADDRESS_LIST = (CONNECT_TIMEOUT=3)(RETRY_COUNT=3) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.136.74.18)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1.us.osc.oracle.com) (UR=A))) OK (40 msec)tnsnamesper l'istanza di Oracle Database Exadata Cloud Service (DB1_phx3g7):[oracle@exacs82-vm3sv2 DB1]$ tnsping DB1_phx3g7TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 05-FEB-2021 16:35:15 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u02/app/oracle/product/19.0.0.0/dbhome_5/network/admin/DB1/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (SDU = 65536) (RECV_BUF_SIZE = 134217728) (SEND_BUF_SIZE = 134217728) (ADDRESS_LIST = (CONNECT_TIMEOUT = 3) (RETRY_COUNT = 3) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.255.195.136)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB1_phx3g7.datasubnetactiv.oscphevcn.oraclevcn.com) (INSTANCE_NAME = DB12) (UR = A))) OK (0 msec)