Failover Connections for Data Guard Error with ORA-1033 [ID 461874.1]

来源：互联网发布：省市区数据库sql 编辑：程序博客网时间：2024/05/22 05:33

Failover Connections for Data Guard Error with ORA-1033 [ID 461874.1]

Modified 28-SEP-2010 Type PROBLEM Status PUBLISHED

In this Document
  Symptoms
  Cause
  Solution

Applies to:

Oracle Net Services - Version: 9.2.0.1.0 to 10.2.0.1.0 - Release: 9.2 to 10.2
Information in this document applies to any platform.

Symptoms

Connections in data guard environment fail with ORA-01033: ORACLE initialization or shutdown in progress.

Oracle Net client trace shows after successful connection handshake

Net service name has LOAD_BALANCE set in description section

  (DESCRIPTION =
    (LOAD_BALANCE = yes)
    (ADDRESS = (PROTOCOL = TCP)(HOST = NodeA)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = NodeB)(PORT = 1521))

Or has two description sections

  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(Host = NodeA)(Port = 1521))
      (CONNECT_DATA =
        (SERVICE_NAME = ORCL1)
      )
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(Host = NodeB)(Port = 1521))
      (CONNECT_DATA =
        (SERVICE_NAME = ORCL2)

Cause

Error ORA-1033 is expected for connections when they attempt to connect to standby instance, because it is mounted and not open.

When LOAD_BALANCE is set, connections can load balance between the addresses in the net service name.

When there are two description sections in a net service name, Oracle Net will load balance between them.

Solution

Example is for service called failover

1. Setup the net service name, ensuring LOAD_BALANCE is not used and one has one description section.

DGtest =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = NodeA)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = NodeB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = failover)
      (SERVER = DEDICATED)
        (FAILOVER_MODE =
          (TYPE = session)
          (METHOD = BASIC)
          (RETRIES = 180)
          (DELAY = 5)
        )
      )
    )
  )

2. Create and start service

SQL> exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'failover',network_name => 'failover',aq_ha_notifications => true,failover_method => 'BASIC', failover_type => 'SELECT',failover_retries => 180,failover_delay => 1);

PL/SQL procedure successfully completed.

SQL>exec dbms_service.start_service('failover');

PL/SQL procedure successfully completed.

3.Create a database trigger to ensure the service name is always the same, no matter which node's instance is the primary.

sqlplus / as sysdba

SQL> CREATE OR REPLACE TRIGGER manage_OCIservice
after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('failover');
ELSE
DBMS_SERVICE.STOP_SERVICE('failover');
END IF;
END;

PL/SQL procedure successfully completed.

4. Ensure the change is made on the standby with command.

SQL> Alter system archive log current ;

5. Listener service command lsnrctl services on primary will then show

Service "failover" has 1 instance(s).
  Instance "tom1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

This method ensures the service failover is only available on the primary node.The net service name DGtest works through the addresses in the address_list section, in order. If NodeA is the standby, then Node B will have the service failover and the connection will attempt to connect.

More information on Dataguard setup can be found in following articles:
Note 316740.1How to configure to let TAF work after Data Guard Switchover or Failover

http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm
http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gR2_ClientFailoverBestPractices.pdf

------------------------------------------------------------------------------