cesardbaLeave a Comment on Dicas para Troubleshooting Oracle Data Guard

Dicas para Troubleshooting Oracle Data Guard

1- Testes de conexão em todos o nodes produtivos e do standby via TNSPING.

[oracle@exacc01db01 ~]$ tnsping DB_DG
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB_DG_DGMGRL.teste.corp)))
OK (0 msec)
   
   
[oracle@exacc01db02 ~]$ tnsping DB_DG
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB_DG_DGMGRL.teste.corp)))
OK (0 msec)
   
   
[oracle@exacc02db01 admin]$ tnsping DB
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB.teste.corp)))
OK (0 msec)
  
[oracle@exacc02db02 admin]$ tnsping DB
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB.teste.corp)))
OK (0 msec)

2 – Testes de conexão em todos o nodes produtivos e do standby via SQLPLUS.

[oracle@exacc01db01 ~]$ sqlplus sys@DB_DG as sysdba
[oracle@exacc01db02 ~]$ sqlplus sys@DB_DG as sysdba
   
[oracle@exacc02db01 ~]$ sqlplus sys@DB as sysdba
[oracle@exacc02db02 ~]$ sqlplus sys@DB as sysdba

3 – Verifique em todos o nodes produtivos e do standby se a variável TNS_ADMIN possui o caminho correto do tnsnames usado:

[oracle@exacc01db01 ~]$ srvctl getenv database -d DB -t "TNS_ADMIN"
DB:
TNS_ADMIN=/u02/app/oracle/product/19.0.0.0/dbhome_1/network/admin/DB1
  
[oracle@exacc01db02 ~]$ srvctl getenv database -d DB -t "TNS_ADMIN"
DB:
TNS_ADMIN=/u02/app/oracle/product/19.0.0.0/dbhome_1/network/admin/DB2
  
  
[oracle@exacc02db01 ~]$ srvctl getenv database -d DB_DG -t "TNS_ADMIN"
DB_DG:
TNS_ADMIN=/u02/app/oracle/product/19.0.0.0/dbhome_1/network/admin/DB1
   
[oracle@exacc02db02 ~]$ srvctl getenv database -d DB_DG -t "TNS_ADMIN"
DB_DG:
TNS_ADMIN=/u02/app/oracle/product/12.1.0/dbhome_1/network/admin/DB2

4 – Verifique se todas as entradas do tnsnames em todos os nodes produtivos e do standby estão iguais.

##### PRODUCTION X9 ######
DB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB1.lab.corp)
    )
  )
     
DB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB2.lab.corp)
    )
  )
     
   
##### DATAGUARD X8 ######
DB_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB1_DG_DGMGRL.lab.corp)
    )
  )
     
DB_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB2_DG_DGMGRL.lab.corp)
    )
  )

5 – Verifique se a TDE e wallet em todos os nodes produtivos e do standby estão corretos.

[oracle@exacc01db01 ~]$ cat sqlnet.ora
[oracle@exacc01db02 ~]$ cat sqlnet.ora
  
[oracle@exacc02db01 ~]$ cat sqlnet.ora
[oracle@exacc02db02 ~]$ cat sqlnet.ora
  
  
ENCRYPTION_WALLET_LOCATION=
    (SOURCE=
      (METHOD=FILE)
      (METHOD_DATA=(DIRECTORY=/var/opt/oracle/dbaas_acfs/$ORACLE_UNQNAME/tde_wallet/))) <==================
HTTPS_SSL_VERSION=1.2
SQLNET.CRYPTO_CHECKSUM_CLIENT=ACCEPTED
SQLNET.CRYPTO_CHECKSUM_SERVER=ACCEPTED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256,SHA384,SHA512,SHA1)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256,SHA384,SHA512)
SQLNET.ENCRYPTION_CLIENT=REQUESTED
SQLNET.ENCRYPTION_SERVER=REQUESTED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256,AES192,AES128)
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256,AES192,AES128)
SQLNET.EXPIRE_TIME=10
SQLNET.IGNORE_ANO_ENCRYPTION_FOR_TCPS=TRUE
SQLNET.WALLET_OVERRIDE=FALSE
SSL_CIPHER_SUITES=(SSL_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256,SSL_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384,SSL_ECDHE_RSA_WITH_AES_128_GCM_SHA256,SSL_ECDHE_RSA_WITH_AES_256_GCM_SHA384)
SSL_CLIENT_AUTHENTICATION=FALSE
SSL_VERSION=1.2
WALLET_LOCATION=
    (SOURCE=
      (METHOD=FILE)
      (METHOD_DATA=(DIRECTORY=/var/opt/oracle/dbaas_acfs/$ORACLE_UNQNAME/db_wallet))) <==================
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

6 – Verifique o checksum após a copia dos arquivos de senha, tde e wallet, em nodes produtivos e do standby.

[oracle@exacc01db01 tde_wallet]$ md5sum *
d8d7fd69019c96e8e2f2e9106dcdbc61  cdb_ocids.json
25c501d45f632bd971b2b70099d72890  cwallet.sso
940abbf6becc952755ba0dfdffd05f8f  ewallet_2022072314092872.p12
4167705f20fc6bcfb33b7aad459a8b93  ewallet_2022072314130785.p12
aa36a5ce31323d2254ac187d4ba42234  ewallet.p12
 
[oracle@exacc01db02 tde_wallet]$ md5sum *
d8d7fd69019c96e8e2f2e9106dcdbc61  cdb_ocids.json
25c501d45f632bd971b2b70099d72890  cwallet.sso
940abbf6becc952755ba0dfdffd05f8f  ewallet_2022072314092872.p12
4167705f20fc6bcfb33b7aad459a8b93  ewallet_2022072314130785.p12
aa36a5ce31323d2254ac187d4ba42234  ewallet.p12
 
[oracle@exacc02db01 tde_wallet]$ md5sum *
d8d7fd69019c96e8e2f2e9106dcdbc61  cdb_ocids.json
25c501d45f632bd971b2b70099d72890  cwallet.sso
940abbf6becc952755ba0dfdffd05f8f  ewallet_2022072314092872.p12
4167705f20fc6bcfb33b7aad459a8b93  ewallet_2022072314130785.p12
aa36a5ce31323d2254ac187d4ba42234  ewallet.p12
 
[oracle@exacc02db02 tde_wallet]$ md5sum *
d8d7fd69019c96e8e2f2e9106dcdbc61  cdb_ocids.json
25c501d45f632bd971b2b70099d72890  cwallet.sso
940abbf6becc952755ba0dfdffd05f8f  ewallet_2022072314092872.p12
4167705f20fc6bcfb33b7aad459a8b93  ewallet_2022072314130785.p12
aa36a5ce31323d2254ac187d4ba42234  ewallet.p12

7 – Teste o acesso do broker em nodes produtivos e do standby.

[oracle@exacc01db01 ~]$ dgmgrl SYS@DB_DG as sysdba
[oracle@exacc01db02 ~]$ dgmgrl SYS@DB_DG as sysdba
  
[oracle@exacc02db01 ~]$ dgmgrl SYS@DB as sysdba
[oracle@exacc02db02 ~]$ dgmgrl SYS@DB as sysdba

8 – Verifique e acompanhe todos os logs do broker em nodes produtivos e do standby.

[oracle@exacc01db01 ~]$ tail -f /u02/app/oracle/diag/rdbms/DB/DB1/trace/drcDB1.log
[oracle@exacc01db02 ~]$ tail -f /u02/app/oracle/diag/rdbms/DB/DB2/trace/drcDB2.log
  
[oracle@exacc02db01 ~]$ tail -f /u02/app/oracle/diag/rdbms/DB_DG/DB1/trace/drcDB1.log
[oracle@exacc02db02 ~]$ tail -f /u02/app/oracle/diag/rdbms/DB_DG/DB2/trace/drcDB2.log

9 – Habilite tracing do broker para melhor detalhes dos erros ocorridos:

-- RAC
[oracle@exacc01db01 ~]$ dgmgrl /
DGMGRL> show configuration;
DGMGRL> show database verbose db_dg;
-- >=18C
DGMGRL> validate network configuration for all;
DGMGRL> edit configuration set property tracelevel=support;
DGMGRL> EDIT INSTANCE * ON DATABASE 'db_dg' SET PROPERTY LogArchiveTrace=8191;
DGMGRL> DISABLE DATABASE db_dg;
DGMGRL> ENABLE DATABASE db_dg;
  
  
[oracle@exacc01db01 ~]$ tail -f /u02/app/oracle/diag/rdbms/DB/DB1/trace/drcDB1.log
[oracle@exacc01db02 ~]$ tail -f /u02/app/oracle/diag/rdbms/DB/DB2/trace/drcDB2.log
  
-- SINGLE INSTANCE
dgmgrl /
DGMGRL> edit configuration set property tracelevel=support;
DGMGRL> edit database db_dg set property LogArchiveTrace=8191;
DGMGRL> DISABLE DATABASE db_dg;
DGMGRL> ENABLE DATABASE db_dg;
  
[oracle@exacc01db01 ~]$ tail -f /u02/app/oracle/diag/rdbms/DB/DB/trace/drcDB.log
  
-- Disable broker tracing.
DGMGRL> edit configuration reset property tracelevel ;
DGMGRL> edit database db_dg reset property logarchivetrace;

10 – Antes de realizar qualquer operação de switch no Data Guard, realize a validação do banco.

dgmgrl /
dgmgrl> validate database verbose db_dg

Leave a Reply

Your email address will not be published. Required fields are marked *

search previous next tag category expand menu location phone mail time cart zoom edit close