This post is also available in:
English
O Fast-Start Failover Observer é um componente do Oracle Data Guard Broker que permite ao DBA automatizar a tarefa de failover e ter noites mais tranquilas de sono. O que poucos DBAs sabem, no entanto, é que ele pode trabalhar em conjunto com o Oracle Wallet, de forma que você possa remover a senha de login dos seus scripts.
Artigo Publicado na OTN: http://www.oracle.com/technetwork/pt/articles/database-performance/oracle-data-guard-failover-e-wallet-2407989-ptb.html
Vamos considerar o cenário de um Data Guard com 2 hosts separados por cidades:
- Unique Name: aaasne / IP: 10.2.200.2
- Unique Name: aaarjo / IP: 10.1.100.1
Verificando as configurações de conexão:
DGMGRL> show database verbose aaasne; Database - aaasne ... Properties: ... DGConnectIdentifier = 'aaasne' ObserverConnectIdentifier = '' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaasne_DGMGRL)(INSTANCE_NAME=aaasne)(SERVER=DEDICATED)))' ... Database Status: SUCCESS DGMGRL> show database verbose aaarjo; Database - aaarjo ... Properties: ... DGConnectIdentifier = 'aaarjo' ObserverConnectIdentifier = '' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.100.1)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaarjo_DGMGRL)(INSTANCE_NAME=aaarjo)(SERVER=DEDICATED)))' ... Database Status: SUCCESS
O Observer vai tentar se conectar nas bases usando o ObserverConnectIdentifier.
Se este for nulo, ele vai usar o DGConnectIdentifier. Portanto, a nossa primeira tarefa é incluir essas duas entradas no tnsnames.ora e no Oracle Wallet do Observer.
Neste cenário, o cliente não permite que o usuário SYS seja usado e nem que as senhas fiquem salvas no script. Eu criei então o usuário dgobserver, senha abcd1234 como sysdba e repliquei o pwfile nos dois ambientes para refletir este privilégio.
Incluindo este usuário no Wallet:
[oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -create Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter password: Enter password again: [oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -createCredential aaarjo dgobserver abcd1234 Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string1 [oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -createCredential aaasne dgobserver abcd1234 Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string2
Vamos adicionar no sqlnet.ora o caminho para o Wallet:
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /home/oracle/wallet_dir) ) ) SQLNET.WALLET_OVERRIDE = TRUE
Após esses dois valores inclusos, já é possível iniciar o Observer sem receber erro:
DGMGRL> connect /@aaarjo Connected. DGMGRL> start observer; Observer started
Vamos simular um erro executando um "shutdown abort" na instância principal do momento (aaasne):
[oracle@aaasneserver ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 2 16:51:21 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> shutdown abort; ORACLE instance shut down. SQL>
Acompanhando o log do Observer:
DGMGRL> start observer; Observer started 16:52:06.96 Tuesday, December 02, 2014 Initiating Fast-Start Failover to database "aaarjo"... Performing failover NOW, please wait... Failover succeeded, new primary is "aaarjo" 16:52:18.96 Tuesday, December 02, 2014
O Observer conseguiu fazer a transição do aaasne, tornando a aaarjo como primária.
Iniciando o banco de dados novamente em outra sessão:
[oracle@aaasneserver ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 2 16:55:24 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to an idle instance. SQL> startup; ORACLE instance started. Total System Global Area 1069252608 bytes Fixed Size 2188408 bytes Variable Size 700455816 bytes Database Buffers 356515840 bytes Redo Buffers 10092544 bytes Database mounted. ORA-16649: possible failover to another database prevents this database from being opened SQL>
No entanto, pelo log do Observer percebemos que ele falha ao tentar fazer automaticamente o REINSTATE:
16:56:00.37 Tuesday, December 02, 2014
Initiating reinstatement for database "aaasne"...
Reinstating database "aaasne", please wait...
Operation requires shutdown of instance "aaasne" on database "aaasne"
Shutting down instance "aaasne"...
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Please complete the following steps and reissue the REINSTATE command:
shut down instance "aaasne" of database "aaasne"
start up and mount instance "aaasne" of database "aaasne"
16:56:22.21 Tuesday, December 02, 2014
O problema é que durante a fase de REINSTATE, o Oracle utiliza como conexão o StaticConnectIdentifier. Isso pode ser percebido se simularmos novamente a execução automática do reinstate com o Observer em modo de debug ("dgmgrl -debug"):
[W000 12/02 17:22:24.33] Ping the primary database.
[W000 12/02 17:22:24.33] Sending command PING to thread P004
[P004 12/02 17:22:24.33] Executing PING command.
[P004 12/02 17:22:24.33] {BEGIN dbms_drs.Ping(25640, 248, 0, 194, 0, :version, :flags, :focond, :status); END;}
[P004 12/02 17:22:24.34] Ping returned REINSTATING
[W000 12/02 17:22:24.34] Command PING to thread P004 returned status=0
[W000 12/02 17:22:27.34] Ping the primary database.
[W000 12/02 17:22:27.34] Sending command PING to thread P004
[P004 12/02 17:22:27.34] Executing PING command.
[P004 12/02 17:22:27.34] {BEGIN dbms_drs.Ping(25640, 248, 0, 194, 0, :version, :flags, :focond, :status); END;}
Operation requires shutdown of instance "aaasne" on database "aaasne"
Shutting down instance "aaasne"...
[S005 12/02 17:22:27.34] Connecting to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaasne_DGMGRL)(INSTANCE_NAME=aaasne)(SERVER=DEDICATED))).
[P004 12/02 17:22:27.36] Ping returned AFO_SUCCESS
[W000 12/02 17:22:27.36] Command PING to thread P004 returned status=0
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Please complete the following steps and reissue the REINSTATE command:
shut down instance "aaasne" of database "aaasne"
start up and mount instance "aaasne" of database "aaasne"
17:22:28.44 Tuesday, December 02, 2014
[W000 12/02 17:22:29.36] Command REINSTATE to thread S005 returned status=16661
[W000 12/02 17:22:29.36] Failed to reinstate database aaasne. Will retry later
Neste caso, o Observer tentou conectar utilizando o serviço:
- '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaasne_DGMGRL)(INSTANCE_NAME=aaasne)(SERVER=DEDICATED)))'.
Para o reinstate passar a funcionar, vai ser preciso adicionarmos ao nosso Wallet as strings de conexão representadas pelo StaticConnectIdentifier de ambas as bases:
[oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -createCredential '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.100.1)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaarjo_dgmgrl)(INSTANCE_NAME=aaarjo)(SERVER=DEDICATED)))' dgobserver abcd1234 Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string3 [oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -createCredential '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaasne_dgmgrl)(INSTANCE_NAME=aaasne)(SERVER=DEDICATED)))' dgobserver abcd1234 Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string4 [oracle@fsfoserver ~]$
Testando mais uma vez o processo automático de REINSTATE:
DGMGRL> start observer; Observer started 17:36:42.59 Tuesday, December 02, 2014 Initiating Fast-Start Failover to database "aaarjo"... Performing failover NOW, please wait... Failover succeeded, new primary is "aaarjo" 17:36:54.36 Tuesday, December 02, 2014 17:37:29.72 Tuesday, December 02, 2014 Initiating reinstatement for database "aaasne"... Reinstating database "aaasne", please wait... Operation requires shutdown of instance "aaasne" on database "aaasne" Shutting down instance "aaasne"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "aaasne" on database "aaasne" Starting instance "aaasne"... ORACLE instance started. Database mounted. Continuing to reinstate database "aaasne" ... Reinstatement of database "aaasne" succeeded 17:38:50.59 Tuesday, December 02, 2014
Pronto, não teremos mais problemas quanto ao FSFO Observer. Até aqui, já conseguimos que esse processo trabalhe corretamente em modo automático em conjunto com o Oracle Wallet.
No entanto, e se tentarmos efetuar manualmente um switchover entre esses 2 banco de dados?
[oracle@fsfoserver ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /@aaarjo
Connected.
DGMGRL> show configuration;
Configuration - timdbubb
Protection Mode: MaxAvailability
Databases:
aaarjo - Primary database
aaasne - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to aaasne;
Performing switchover NOW, please wait...
Operation requires a connection to instance "aaasne" on database "aaasne"
Connecting to instance "aaasne"...
Unable to connect to database
ORA-12545: Connect failed because target host or object does not exist
Failed.
Warning: You are no longer connected to ORACLE.
connect to instance "aaasne" of database "aaasne"
DGMGRL>
Note que mesmo criando anteriormente as 2 entradas que utilizam o listener estático, tivemos um novo problema. Vamos analisar então em modo debug:
[oracle@fsfoserver ~]$ dgmgrl -debug
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /@aaarjo
[W000 12/02 18:27:29.12] Connecting to database using aaarjo.
[W000 12/02 18:27:29.49] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 12/02 18:27:29.50] Broker version is '11.2.0.4.0'
Connected.
DGMGRL> switchover to aaasne;
Performing switchover NOW, please wait...
Operation requires a connection to instance "aaasne" on database "aaasne"
Connecting to instance "aaasne"...
[W000 12/02 18:27:35.86] Connecting to database using (DESCRIPTION=(SDU=32767)(SEND_BUF_SIZE=160000)(RECV_BUF_SIZE=160000)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378)))(CONNECT_DATA=(SERVICE_NAME=aaasne_DGB)(INSTANCE_NAME=aaasne)(SERVER=dedicated))).
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
connect to instance "aaasne" of database "aaasne"
DGMGRL>
Pelos logs, podemos ver que para o processo de switchover, o Broker tenta usar outro serviço:
- '(DESCRIPTION=(SDU=32767)(SEND_BUF_SIZE=160000)(RECV_BUF_SIZE=160000)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378)))(CONNECT_DATA=(SERVICE_NAME=aaasne_DGB)(INSTANCE_NAME=aaasne)(SERVER=dedicated)))'.
Esse serviço é derivado do configurado no DGConnectIdentifier, concatenando "_DGB" ao SERVICE_NAME. Portanto, será necessário também incluirmos essa string de ambos os servidores no Wallet:
[oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -createCredential '(DESCRIPTION=(SDU=32767)(SEND_BUF_SIZE=160000)(RECV_BUF_SIZE=160000)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.100.1)(PORT=4378)))(CONNECT_DATA=(SERVICE_NAME=aaarjo_dgb)(INSTANCE_NAME=aaarjo)(SERVER=dedicated)))' dgobserver abcd1234 Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string5 [oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -createCredential '(DESCRIPTION=(SDU=32767)(SEND_BUF_SIZE=160000)(RECV_BUF_SIZE=160000)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378)))(CONNECT_DATA=(SERVICE_NAME=aaasne_dgb)(INSTANCE_NAME=aaasne)(SERVER=dedicated)))' dgobserver abcd1234 Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string6 [oracle@fsfoserver ~]$
E testando novamente:
DGMGRL> switchover to aaasne; Performing switchover NOW, please wait... Operation requires a connection to instance "aaasne" on database "aaasne" Connecting to instance "aaasne"... Connected. New primary database "aaasne" is opening... Operation requires startup of instance "aaarjo" on database "aaarjo" Starting instance "aaarjo"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "aaasne" DGMGRL>
Agora o Observer está livre para executar as todas as tarefas utilizando o Oracle Wallet, além de ser possível efetuar operações manuais.
Por fim, o Wallet ficou da seguinte forma, com 3 entradas para cada host:
[oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -listCredential Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 6: (DESCRIPTION=(SDU=32767)(SEND_BUF_SIZE=160000)(RECV_BUF_SIZE=160000)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378)))(CONNECT_DATA=(SERVICE_NAME=aaasne_dgb)(INSTANCE_NAME=aaasne)(SERVER=dedicated))) dgobserver 5: (DESCRIPTION=(SDU=32767)(SEND_BUF_SIZE=160000)(RECV_BUF_SIZE=160000)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.100.1)(PORT=4378)))(CONNECT_DATA=(SERVICE_NAME=aaarjo_dgb)(INSTANCE_NAME=aaarjo)(SERVER=dedicated))) dgobserver 4: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaasne_dgmgrl)(INSTANCE_NAME=aaasne)(SERVER=DEDICATED))) dgobserver 3: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.100.1)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaarjo_dgmgrl)(INSTANCE_NAME=aaarjo)(SERVER=DEDICATED))) dgobserver 2: aaasne dgobserver 1: aaarjo dgobserver [oracle@fsfoserver ~]$Gostou? Não deixe de comentar ou deixar um 👍!
