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

最新推荐文章于 2025-12-01 02:11:37 发布

最新推荐文章于 2025-12-01 02:11:37 发布 · 111 阅读

文章标签：

本文详细介绍了在Oracle Data Guard环境中遇到Failover连接失败导致ORA-1033错误时的排查与解决方案。通过调整Net Service Name配置、创建并启动服务、确保服务名在切换时始终指向主节点等步骤，有效解决了连接问题。

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

	Modified28-SEP-2010TypePROBLEMStatusPUBLISHED

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 indata guard environment fail withORA-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 balancebetweenthem.

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 startservice

SQL>execDBMS_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/SQLproceduresuccessfullycompleted.

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>CREATEORREPLACETRIGGERmanage_OCIservice
afterstartupondatabase
DECLARE
roleVARCHAR(30);
BEGIN
SELECTDATABASE_ROLEINTOroleFROMV$DATABASE;
IFrole='PRIMARY'THEN
DBMS_SERVICE.START_SERVICE('failover');
ELSE
DBMS_SERVICE.STOP_SERVICE('failover');
ENDIF;
END;

PL/SQLproceduresuccessfullycompleted.

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"has1instance(s).
Instance"tom1",statusREADY,has1handler(s)forthisservice...
Handler(s):
"DEDICATED"established:0refused:0state:ready
LOCALSERVER

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.

Moreinformation 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

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