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

最新推荐文章于 2021-04-09 23:44:42 发布

最新推荐文章于 2021-04-09 23:44:42 发布 · 86 阅读

文章标签：

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

	Modified 28-SEP-2010Type PROBLEMStatus 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 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

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

Blog： http://blog.youkuaiyun.com/tianlesoftware

网上资源： http://tianlesoftware.download.youkuaiyun.com

相关视频：http://blog.youkuaiyun.com/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1 群：62697716(满); DBA2 群：62697977(满)

DBA3 群：62697850 DBA 超级群：63306533;

聊天群：40132017