Oracle Data Guard Fast-Start Failover e Oracle Wallet

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:

  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 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 👍!

Deixe um comentário

Seu e-mail não será publicado.