OS:HPUX
Oracle version: 11.1.0.7
why TNS service name give preference to connect instance which is in a mount mode and dsiaply 'ORA-01033' as mentioned below.
I wanted to connect only instance which are OPEN(not MOUNT) using connect string and want to know status of all instances using SQL query "select instance_name,status from gv$instance". How can I achive this?
I have four node rac cluster and four instance, one instance on each node and status of instances are as shown below
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
dtrac1 OPEN
dtrac4 STARTED
dtrac3 OPEN
dtrac2 MOUNTED
I have tried to connect using "dtrac" service name ( details of tnsnames gives below) from any node it gives priority to instance which is mount and shows following error messages
SQL> conn system/vcs@dtrac
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress Process ID: 0 Session ID: 834 Serial number: 139
Warning: You are no longer connected to ORACLE.
SQL> conn system/vcs@dtrac
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress Process ID: 0 Session ID: 834 Serial number: 139
SQL> conn system/vcs@dtrac
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress Process ID: 0 Session ID: 834 Serial number: 139
SQL>
when I changes dtrac2 instance to either NOMOUNT or OPEN state then connection properly goes to instances which are OPEN state.
tnsname.ora file:
==================
# tnsnames.ora Network Configuration File: /oracle/db11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DTRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal05-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal06-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal07-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal08-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dtrac)
)
)
DTRAC4 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal08-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dtrac)
(INSTANCE_NAME = dtrac4)
)
)
DTRAC3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal07-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dtrac)
(INSTANCE_NAME = dtrac3)
)
)
DTRAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal06-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dtrac)
(INSTANCE_NAME = dtrac2)
)
)
DTRAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal05-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dtrac)
(INSTANCE_NAME = dtrac1)
)
)
LISTENERS_DTRAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal05-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal06-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal07-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal08-vip)(PORT = 1521))
)
LISTENER_DTRAC4 =
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal08-vip)(PORT = 1521))
LISTENER_DTRAC3 =
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal07-vip)(PORT = 1521))
LISTENER_DTRAC2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal06-vip)(PORT = 1521))
LISTENER_DTRAC1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal05-vip)(PORT = 1521))
-----answer------------------
Actually, it's not quite that easy. FAILOVER=YES is the default, no real need to explicitly set it.
I have seen the OP's problem, too. It happens when the instance is still (or already) registered with the listeners which (i guess) can happen when PMON decides to register the instance before it is open or during a long shutdown operation. In that case, new connections will most propably be directed to that instance because it will have the least connections and connection attempts will fail with the error mentioned above.
I don't really see this as a huge problem since I don't know when or why I would want to just mount a single instance in RAC (while others are open) but I also don't know a solution.
---sqlplus srvctl 没区别
I have started instance in mount mode using sqlplus. But it doesn't make diffrence if I stated using srvctl since service get ready as we started instance either OPEN/MOUNT mode using srvctl or sqlplus.
As you are saying diffrent way of configuration of service by mentioning preferred and available attributes for instances but its not useful to know instances which are open. Its useful for segregate work to specific nodes by creating diffrent services for diffrent application.
'crs_stat -t' shows resources online even instance is either nomount/mount/open state. I want to exact state of instance so its not useful for me.
Below example showing service get READY status even I start instanc with mount option using SRVCTL
(oracle@slpal06)[~/db11g/network/admin] srvctl stop instance -d dtrac -i dtrac2
(oracle@slpal06)[~/db11g/network/admin] lsnrctl stat
LSNRCTL for HPUX: Version 11.1.0.7.0 - Production on 17-JUN-2009 18:10:03
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_SLPAL06
Version TNSLSNR for HPUX: Version 11.1.0.7.0 - Production
Start Date 03-JUN-2009 20:38:19
Uptime 13 days 21 hr. 31 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/db11g/network/admin/listener.ora
Listener Log File /oracle/db11g/diag/tnslsnr/slpal06/listener_slpal06/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.209.138.26)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.209.137.145)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "dtrac" has 3 instance(s).
Instance "dtrac1", status READY, has 1 handler(s) for this service...
Instance "dtrac3", status READY, has 1 handler(s) for this service...
Instance "dtrac4", status READY, has 1 handler(s) for this service...
Service "dtracXDB" has 3 instance(s).
Instance "dtrac1", status READY, has 1 handler(s) for this service...
Instance "dtrac3", status READY, has 1 handler(s) for this service...
Instance "dtrac4", status READY, has 1 handler(s) for this service...
Service "dtrac_XPT" has 3 instance(s).
Instance "dtrac1", status READY, has 1 handler(s) for this service...
Instance "dtrac3", status READY, has 1 handler(s) for this service...
Instance "dtrac4", status READY, has 1 handler(s) for this service...
The command completed successfully
(oracle@slpal06)[~/db11g/network/admin] srvctl start instance -d dtrac -i dtrac2 -o mount (oracle@slpal06)[~/db11g/network/admin] lsnrctl stat
LSNRCTL for HPUX: Version 11.1.0.7.0 - Production on 17-JUN-2009 18:11:19
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_SLPAL06
Version TNSLSNR for HPUX: Version 11.1.0.7.0 - Production
Start Date 03-JUN-2009 20:38:19
Uptime 13 days 21 hr. 33 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/db11g/network/admin/listener.ora
Listener Log File /oracle/db11g/diag/tnslsnr/slpal06/listener_slpal06/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.209.138.26)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.209.137.145)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "dtrac" has 4 instance(s).
Instance "dtrac1", status READY, has 1 handler(s) for this service...
Instance "dtrac2", status READY, has 2 handler(s) for this service...
Instance "dtrac3", status READY, has 1 handler(s) for this service...
Instance "dtrac4", status READY, has 1 handler(s) for this service...
Service "dtracXDB" has 3 instance(s).
Instance "dtrac1", status READY, has 1 handler(s) for this service...
Instance "dtrac3", status READY, has 1 handler(s) for this service...
Instance "dtrac4", status READY, has 1 handler(s) for this service...
Service "dtrac_XPT" has 4 instance(s).
Instance "dtrac1", status READY, has 1 handler(s) for this service...
Instance "dtrac2", status READY, has 2 handler(s) for this service...
Instance "dtrac3", status READY, has 1 handler(s) for this service...
Instance "dtrac4", status READY, has 1 handler(s) for this service...
The command completed successfully
(oracle@slpal06)[~/db11g/network/admin]
I searched a bit on metalink and found note 69546.1 which states that PMON will register with local and remote listeners on instance startup, not even mounting it is required. Which of course is sort of stupid because of the error message you get when trying to connect.
From Oracle 8i onwards, database instances can register themselves with the Listener when started. Prior to this release, information about the instance was required to be manually configured in the "listener.ora" file in the form of a STATIC SID_LIST. Database instance registration is comprised of two elements: - Service registration provides the Listener with instance information, such as database service names and instance names. - MTS (Shared Server) dispatcher registration provides dispatcher information to the Listener. By default, an instance background process registers instance information to a Listener on the local machine. If a Listener is started after the instance, there may be a delay before the instance and dispatchers are registered (normally 30 to 35 seconds). As such, if a Listener receives an incoming connection request before an instance is registered, the Listener may reject the request with an ORA-12514 error. The instance will attempt to register its services to the Listener periodically. Prerequisites ~~~~~~~~~~~~~ 1) Static parameters "INSTANCE_NAME" and "SERVICE_NAMES" should be set in "init.ora". An instance can have only one "INSTANCE_NAME," but more than one "SERVICE_NAMES". E.g.: instance_name = v815 service_names = ( marketing, support, education ) 2) It is not necessary to have a "LISTENER.ORA" unless one wants to listen on another PORT (other than 1521). 3) "TNSNAMES.ORA" is required unless ONAMES or LDAP is used. Just that "SID_NAME" is now "SERVICE_NAME". A typical entry ... V815 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle8i)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = support) ) ) NOTE: It is "SERVICE_NAME" and not "SERVICE_NAMES" as defined in "init.ora". 4) If using MTS, mts_... parameters needs to be set in "init.ora". For 9i and later, this is Shared Server and the parameter is DISPATCHERS. 5) The "LISTENER.ORA" file does not require the "SID_LIST_" parameter that specifies information on the databases served by the Listener. This parameter is still required only if the management tool being used still requires it. Example(s) ~~~~~~~~~~ For simplicity sake, there is no "LISTENER.ora" in the case study. "init.ora" has the following values: INSTANCE_NAME = v815 SERVICE_NAMES = support Case I. Database started after the Listener ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Step 1 ------ Starting Listener ( no "LISTENER.ora" exists ). Note that the Listener has no service handler. $ lsnrctl start LSNRCTL for Solaris: Version 8.1.5.0.0 - Production on 15-APR-99 05:23:44 (c) Copyright 1998 Oracle Corporation. All rights reserved. Starting /bigdisk/oracle8i/bin/tnslsnr: please wait... TNSLSNR for Solaris: Version 8.1.5.0.0 - Production Log messages written to /bigdisk/oracle8i/network/log/listener.log Listening on: (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)) Listening on: (ADDRESS=(PROTOCOL=tcp)(PORT=1521)) Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Solaris: Version 8.1.5.0.0 - Production Start Date 15-APR-99 05:23:44 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security OFF SNMP OFF Listener Log File /bigdisk/oracle8i/network/log/listener.log The listener supports no services The command completed successfully $ Step (2) -------- Start the database. Step (3) -------- Check Listener status. It shows one service handler; the service handler name is the "INSTANCE_NAME". $ lsnrctl status LSNRCTL for Solaris: Version 8.1.5.0.0 - Production on 15-APR-99 05:27:14 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Solaris: Version 8.1.5.0.0 - Production Start Date 15-APR-99 05:26:21 Uptime 0 days 0 hr. 0 min. 53 sec Trace Level off Security OFF SNMP OFF Listener Log File /bigdisk/oracle8i/network/log/listener.log Services Summary... v815 has 1 service handler(s) The command completed successfully $ Step (4) -------- Shutdown the instance. Step (5) -------- Check the Listener once again. The database has been unregistered. $ lsnrctl status LSNRCTL for Solaris: Version 8.1.5.0.0 - Production on 15-APR-99 05:34:27 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Solaris: Version 8.1.5.0.0 - Production Start Date 15-APR-99 05:33:02 Uptime 0 days 0 hr. 1 min. 25 sec Trace Level off Security OFF SNMP OFF Listener Log File /bigdisk/oracle8i/network/log/listener.log The listener supports no services The command completed successfully $ Case II. Database started before the Listener ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Step 1 ------ Start the database. Step 2 ------ Starting Listener ( no "LISTENER.ora" exists ). Note that the Listener has no service handler. $ lsnrctl start LSNRCTL for Solaris: Version 8.1.5.0.0 - Production on 15-APR-99 05:42:36 (c) Copyright 1998 Oracle Corporation. All rights reserved. Starting /bigdisk/oracle8i/bin/tnslsnr: please wait... TNSLSNR for Solaris: Version 8.1.5.0.0 - Production Log messages written to /bigdisk/oracle8i/network/log/listener.log Listening on: (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)) Listening on: (ADDRESS=(PROTOCOL=tcp)(PORT=1521)) Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Solaris: Version 8.1.5.0.0 - Production Start Date 15-APR-99 05:42:36 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security OFF SNMP OFF Listener Log File /bigdisk/oracle8i/network/log/listener.log The listener supports no services The command completed successfully $ Step 3 ------ Check Listener status after some time. Say, 1 minute. $ lsnrctl status LSNRCTL for Solaris: Version 8.1.5.0.0 - Production on 15-APR-99 05:43:35 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Solaris: Version 8.1.5.0.0 - Production Start Date 15-APR-99 05:42:36 Uptime 0 days 0 hr. 0 min. 58 sec Trace Level off Security OFF SNMP OFF Listener Log File /bigdisk/oracle8i/network/log/listener.log Services Summary... v815 has 1 service handler(s) The command completed successfully $ Step 4 ------ Check "$ORACLE_HOME/network/log/listener.log" after some time. One can find a "service_update" entry apart from the "service_register" entry. 15-APR-99 05:13:27 * service_register * v815 * 0 15-APR-99 05:23:27 * service_update * v815 * 0 15-APR-99 05:33:28 * service_update * v815 * 0 15-APR-99 05:43:28 * service_update * v815 * 0 15-APR-99 06:48:24 * service_quit * v815 * 0 In the above screen shot, PMON is registering the instance every 10 minutes. However, this varies greatly, depending on whether PMON is busy or not (registration is not a high priority task for PMON). Frequently Asked Question (FAQ) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Q1. At what frequency does the database try to register with the Listener? Can this be changed/configured? A1. Normally less than a minute. No. Q2. Does the database update the Listener? If yes, at what frequency? Can the frequency be changed/configured? A2. Yes, the database updates the Listener every 10 minutes apart from registering/unregistering during startup/shutdown. No, the frequency is not tunable but does depend on the state of PMON. Q3. What does "service_update" do? Why is it required? A3. In a Shared Server (old MTS) environment, the database updates the Listener with dispatcher and server information as they are dynamic in nature. One can change the number of dispatchers/servers dynamically. The Listener needs to know that. Also, the LOCAL_LISTENER (Database) parameter allows the Database to locate the correct listener address for registration and service updates. Q4. What happens when the database is still not registered and a client tries to connect? Will the database then register with the Listener? A4. The client will not connect [ORA-12514 ]. No, the database will wake up at its own interval and register. Q5. Can two databases have the same "service_name"? A5. Yes, used in a replicated environment. One connects by a different tnsnames alias, but both have the same service name. For example: primary_db = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = oracle8i) (PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = support)) ) replicated_db = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = backup) (PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = support)) ) Note 1 : Net8 8.1 wants a service name rather than a SID to identify a database. It is possible to provide multiple services names so that different usages of a single database can be identified separately. Note 2 : If two databases on the same machine have the same "service_name", then the database registered last has precedence. One is likely to get connected to the later one. Ideally, one should avoid having the same "service_name" for databases residing on the same host. Q6. At what stage does the database register with the Listener? A6. When the instance starts. The database need not be mounted. Q7. What if i want to trace the Listener but I don't have a "listener.ora"? A7. Either --> Create a "listener.ora" with only three entries: trace_level_listener=16 trace_file_listener=listener trace_directory_listener=/bigdisk/oracle8i/network/log Or --> Start dynamic tracing using "lsnrctl set Trace_level 16"
----------------------------------------
Note 797713.1 describes your exact problem and a solution. I would love to quote it but I am unsure if this is allowed or not. So instead I'll just sum up what I have learned: The default service you create when first installing RAC will behave just like in a single instance. It will register with the listener(s) as soon as the instance starts. This is actually wanted so you can perform administration. When you create additional services through srvctl or crs, they will somehow be managed by crs and only get registered once the instance is 'up' (I assume they mean open).
So the solution would be to create another service through srvctl and use that for your connections.
SYMPTOMS
When one of cluster nodes got evicted and is rejoining the cluster or an instance in the RAC cluster is starting up, the connections to the RAC DB are resulting in error ORA-01033: ORACLE initialization or shutdown in progress.
During this time the other nodes/instances in the cluster are up and running fine.
CAUSE
This is an expected behavior when a service not managed by CRS is used for the connection.
SOLUTION
An Oracle instance registers itself with a listener at the time of startup. The listener, at this moment will publish the instance over the network even though it is still not completely up i.e when the instance startup is still in progress. This is to ensure that the system / instance is available for administration during this time.
In RAC environment, the default service created with the installation ( the service name same as RAC database) is not managed by CRS and behaves the same as in a normal standalone DB. This means that this service will advertise an instance over the network early. On the other hand, a service created by srvctl command or via dbca which is managed by CRS will not publish the instance unless it is completely up.
So, when using a default database service, the error ORA-1033 is expected at the time of instance startup. The workaround would be to use a service created by DBCA or srvctl or handle the error at the application level.
REFERENCES
BUG:5348103 - ORA-01033: ORACLE INITIALIZATION OR SHUTDOWN IN PROGRESS IN 10G RAC
------------------------------------------------
"When you create additional services through srvctl or crs, they will somehow be managed by crs and only get registered once the instance is 'up' (I assume they mean open)."
Its true but with new added service its still tries to connect instance which is mount mode (assuming lowest load on mount instance). I think its oracle bug, why they considering instance for load balancing which are not open.
Steps I done
1. added new service
-----------------------------------
srvctl add service -d dtrac -s dbsrv -r dtrac1,dtrac2,dtrac3,dtrac4
2. added entry in tnsnames.ora
------------------------------------------------
dbsrv =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal05-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal06-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal07-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal08-vip)(PORT = 1521))
(LOAD_BALANCE = no)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dtrac)
)
)
3. started service
------------------------
srvctl start service -d dtrac -s dbsrv
4. current status of instances
----------------------------------------------
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
dtrac1 OPEN
dtrac4 OPEN
dtrac3 OPEN
dtrac2 MOUNTED
5. Tried to connect using newly added service and result is same
--------------------------------------------------------------------------------------------
SQL> conn system/vcs@dbsrv
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress Process ID: 0 Session ID: 828 Serial number: 7936
Warning: You are no longer connected to ORACLE.
SQL> conn system/vcs@dbsrv
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress Process ID: 0 Session ID: 828 Serial number: 7936
Thanks a lot Bjoern for agreat help. You suugetion to create new service worked for me.
Sorry, It was my mistake earlier I have not modified tnsname.ora properly. Forgot to modify SERVICE_NAME to dbsrv.
dbsrv =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal05-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal06-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal07-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = slpal08-vip)(PORT = 1521))
(LOAD_BALANCE = no)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbsrv)
)
)