Oracle Data Guard Fast-Start Failover e Oracle Wallet

Por Rodrigo Jorge
Postado em Janeiro 2015

Revisado por Marcelo Pivovar - Solution Architect

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 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.

Vamos considerar o cenário de um Data Guard com 2 hosts separados por cidades:

1.         Unique Name: aaasne / IP: 10.2.200.2
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 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 ~]$
 

Rodrigo de Araujo Jorge is a Senior Oracle Professional (8i, 9i, 10g, 11g, 10gR2 RAC, 11gR2 RAC, 12c) with more than 8 years of industrial experience in Oracle high end technologies, starting with Oracle RDBMS version 8i up to 11g. He has been involved with multi platform High Availability Solutions as well as complex multiple node RAC 10g and 11g implementations on AIX, Linux, Solaris and HPUX. He has experience in deploying disaster recovery solutions compliant to Oracle MAA, by building RAC and Standby applications linked. As a former employee of IBM and Capgemini, providing services to Bradesco bank group and TIM companies, acted in several projects for enabling high availability RAC systems to support ERP, CRM, HRMS e MRP, end to end performance tuning, architectured backup and disaster recovery solutions to name a few. Presently he is working as a Senior Oracle Consultant in Amdocs.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.