8 Replies Latest reply: Mar 27, 2013 2:52 PM by user588212 RSS

    ORA-01033: ORACLE initialization or shutdown in progress / TAF

    hqt200475
      Hi experts,

      I have the following environment:
      + 2 Linux Redhat  5.7 0n X86/64 named dwh and stb and Oracle 11.2.0.2,
      + Database on dwh is primary and on stb is standby
      the listener on dwh:
      ADR_BASE_LISTENER=/u00/app/oracle
      
      LISTENER =
          (ADDRESS_LIST =
              # for external procedure calls, create a separate listener
              # See basenv_user_guide.pdf for details (chapter of listener.ksh)
              (ADDRESS =
                  (PROTOCOL = TCP)
                  (Host = dwh )
                  (Port = 1521)
              )
          )
      
      SID_LIST_LISTENER =
          (SID_LIST =
              (SID_DESC =
                  # Next line is necessary for dataguard >= 10g
                  (GLOBAL_DBNAME = strm_site1_DGMGRL)
                  (SID_NAME      = STRM )
                  (ORACLE_HOME   = /u01/app/oracle/product/11.2.0/db_1 )
              )
          )
      the service_names parameter on dwh:
      SQL> show parameter service
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      service_names                        string      STRM
      
      [oracle@dwh admin]$ lsnrctl status
      
      
      [oracle@dwh admin]$ lsnrctl status
      
      LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 14-SEP-2011 17:32:43
      
      Copyright (c) 1991, 2010, Oracle.  All rights reserved.
      
      Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=dwh)(Port=1521))
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENER
      Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
      Start Date                14-SEP-2011 12:11:15
      Uptime                    0 days 5 hr. 21 min. 28 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
      Listener Log File         /u01/app/oracle/product/11.2.0/db_1/network/log/listener.log
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dwh)(PORT=1521)))
      Services Summary...
      Service "STRMXDB" has 1 instance(s).
        Instance "STRM", status READY, has 1 handler(s) for this service...
      Service "strm" has 1 instance(s).
        Instance "STRM", status READY, has 1 handler(s) for this service...
      Service "strm_site1" has 1 instance(s).
        Instance "STRM", status READY, has 1 handler(s) for this service...
      Service "strm_site1_DGB" has 1 instance(s).
        Instance "STRM", status READY, has 1 handler(s) for this service...
      Service "strm_site1_DGMGRL" has 1 instance(s).
        Instance "STRM", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully
      the listener on stb:
      [oracle@stb admin]$ cat listener.ora
      ADR_BASE_LISTENER=/u00/app/oracle
      
      LISTENER =
          (ADDRESS_LIST =
              # for external procedure calls, create a separate listener
              # See basenv_user_guide.pdf for details (chapter of listener.ksh)
              (ADDRESS = (PROTOCOL = TCP) (Host = stb ) (Port = 1521)
              )
          )
      
      SID_LIST_LISTENER =
              (SID_DESC =
                  # Next line is necessary for dataguard >= 10g
                  (GLOBAL_DBNAME = strm_site2_DGMGRL)
                  (SID_NAME      = STRM )
                  (ORACLE_HOME   = /u01/app/oracle/product/11.2.0/db_1 )
              )
          )
      the service_names parameter on stb:
      SQL> show parameter service
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      service_names                        string      STRM
      
      [oracle@stb admin]$ lsnrctl status
      
      LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 14-SEP-2011 17:37:23
      
      Copyright (c) 1991, 2010, Oracle.  All rights reserved.
      
      Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=stb)(Port=1521))
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENER
      Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
      Start Date                14-SEP-2011 12:12:39
      Uptime                    0 days 5 hr. 24 min. 44 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
      Listener Log File         /u01/app/oracle/product/11.2.0/db_1/network/log/listener.log
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stb)(PORT=1521)))
      Services Summary...
      Service "strm" has 1 instance(s).
        Instance "STRM", status READY, has 1 handler(s) for this service...
      Service "strm_site2" has 1 instance(s).
        Instance "STRM", status READY, has 1 handler(s) for this service...
      Service "strm_site2_DGB" has 1 instance(s).
        Instance "STRM", status READY, has 1 handler(s) for this service...
      Service "strm_site2_DGMGRL" has 1 instance(s).
        Instance "STRM", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully
      My tnsnames.ora
      # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.2/db_1/network/admin/tnsnames.ora
      # Generated by Oracle configuration tools.
      
      STRM=
       (DESCRIPTION= 
             (LOAD_BALANCE=on) 
             (FAILOVER=on) 
             (ADDRESS=(PROTOCOL=tcp)(HOST=dwh)(PORT=1521)) 
             (ADDRESS=(PROTOCOL=tcp)(HOST=stb)(PORT=1521)) 
                  (CONNECT_DATA=
                      (SERVICE_NAME=strm)
                      (FAILOVER_MODE=(TYPE=select)(METHOD=basic))
                   )
        )
      
      STRM_SITE1 =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = dwh)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = STRM_SITE1)
          )
        )
      
      STRM_SITE2 =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = stb)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = STRM_SITE2)
          )
        )
      My Problem:
      C:\Documents and Settings\thai>sqlplus scott/scott@STRM
      
      SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 14 17:49:51 2011
      
      Copyright (c) 1982, 2010, Oracle.  All rights reserved.
      
      ERROR:
      ORA-01033: ORACLE initialization or shutdown in progress
      Process ID: 0
      Session ID: 0 Serial number: 0
      Login with
      sqlplus sys/****@STRM as sysdba
      raises no problem!

      What I have done wrong? Please, help!

      regards

      hqt200475

      Edited by: hqt200475 on Sep 14, 2011 9:04 AM
        • 1. Re: ORA-01033: ORACLE initialization or shutdown in progress / TAF
          mseberg
          Nothing is wrong.

          Your standby should show this

          Mine :
          SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 14 11:22:26 2011
          
          Copyright (c) 1982, 2009, Oracle.  All rights reserved.
          
          Enter user-name: bigshow/<password>
          ERROR:
          ORA-01033: ORACLE initialization or shutdown in progress
          Process ID: 0
          Session ID: 0 Serial number: 0
          
          
          Enter user-name: 
          Since your standby is in "recovery - mount" this is normal.

          If you want a "Reader" standby you have to use Active Data Guard if you have a license.

          The SYS user works as expected.
          /home/oracle:STANDBY >sqlplus "/ as sysdba"
          
          SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 14 11:30:18 2011
          
          Copyright (c) 1982, 2009, Oracle.  All rights reserved.
          
          
          Connected to:
          Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options
          
          SQL> 
          Non-sysdba users can not connect on Standby server.

          For additional details see :

          ORA-1033 Testing Monitoring User On Standby Databases [ID 1298416.1]

          Best Regards

          mseberg
          • 2. Re: ORA-01033: ORACLE initialization or shutdown in progress / TAF
            hqt200475
            Hi Mseberg,

            Thank for the fast answer!

            Does it mean?

            In the normal fall (dwh/strm_site1 als primary): user scott have to enter:
            sqlplus scott/****@strm_site1
            and in the case of failover to stb/strm_site2 als primary: user scott have to enter:

            sqlplus scott/****@strm_site2
            or do I have a better alternative, to give user scott a unique net_service_name: strm in both cases (without active Data Guard) ?

            regards

            hqt200475
            • 3. Re: ORA-01033: ORACLE initialization or shutdown in progress / TAF
              mseberg
              If I understand the second part of the question correctly you can use DBMS_SERVICE to create an alias on both servers.

              Then depending upon the ROLE they are in you can just connect.


              First an entry need to be added to the client tnsnames.ora that uses a SERVICE_NAME instead of a SID.


              ernie =
               (DESCRIPTION =
                  (ADDRESS_LIST =
                     (ADDRESS = (PROTOCOL = TCP)(HOST = primary.host)(PORT = 1521))
                     (ADDRESS = (PROTOCOL = TCP)(HOST = standby.host)(PORT = 1521))
                     )
                     (CONNECT_DATA =
                     (SERVICE_NAME = ernie)
                  )
               )
              Next the service 'ernie' needs to be created manually on the primary database.
               
              BEGIN
               
                 DBMS_SERVICE.CREATE_SERVICE('ernie','ernie');
              
              END;
              /
              After creating the service needs to be manually started.
               BEGIN
               
                 DBMS_SERVICE.START_SERVICE('ernie');
               
               END;
              Several of the default parameters can now be set for 'ernie'.
              BEGIN
                 DBMS_SERVICE.MODIFY_SERVICE
                 ('ernie',
                 FAILOVER_METHOD => 'BASIC',
                 FAILOVER_TYPE => 'SELECT',
                 FAILOVER_RETRIES => 200,
                 FAILOVER_DELAY => 1);
              END;
              /
              Finally a database STARTUP trigger should be created to ensures that this service is only offered if the database is primary.
              CREATE TRIGGER CHECK_ERNIE_START AFTER STARTUP ON DATABASE
              DECLARE
               V_ROLE VARCHAR(30);
              BEGIN
               SELECT DATABASE_ROLE INTO V_ROLE FROM V$DATABASE;
               IF V_ROLE = 'PRIMARY' THEN
               DBMS_SERVICE.START_SERVICE('ernie');
               ELSE
               DBMS_SERVICE.STOP_SERVICE('ernie');
               END IF;
              END;
              /
              Check the status using lsnrctl status

              /home/oracle: >lsnrctl status

              Service "ernie" has 1 instance(s).


              erine is from the Oracle example, you can add whatever you want and then if you do a switchover or failover it is transparent to your users.



              See this for more details


              http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_serv.htm

              Best Regards

              mseberg
              • 4. Re: ORA-01033: ORACLE initialization or shutdown in progress / TAF
                hqt200475
                Hi MSeberg,

                Your solution works fine! Thank you!

                I have 3 more Questions:

                Question 1:

                >
                I defined the service and Trigger on Primary Database,
                It means changes take place in Data Dictionary of the Primary Database! Are they also logged in redo logs?
                If yes, the definition of Trigger must be transfered to standby database, isn't it?
                Is my line of thought correct?
                Question 2:

                >
                If I understand active data guard correctly! It's only a read-only standby database since version 11
                [url //www.databasejournal.com/features/oracle/article.php/3834931/Using-Oracle-11gs-Active-Data-Guard-and-Snapshot-Standby-Features.htm]Using Oracle 11g's Active Data Guard and Snapshot Standby Features
                So, technically I need only open read-only the standby database, is it correct?
                Question 3:

                I don't have Grid Infrastructure!It means: I cannot use srvctl to configure Oracle restart like [url  //download.oracle.com/docs/cd/E11882_01/server.112/e17120/restart002.htm#BABHHABA] Configuring Oracle Restart  , e.g:
                [oracle@dwh ~]$ srvctl status database -d strm
                ****Unable to retrieve Oracle Clusterware home.
                Start Oracle Clusterware stack and try again.
                What is the optimal way in this situation to configure the Oracle Restart for both databases?
                >

                regards

                hqt200475
                • 5. Re: ORA-01033: ORACLE initialization or shutdown in progress / TAF
                  mseberg
                  Question 1:

                  The Trigger will move to the Standby if Data Guard is working. The tnsnames.ora on the standby will need
                  an entry like the primary side. Otherwise you won't connect using the new alias.


                  Question 2:

                  Yes Oracle 11.

                  To determine whether a standby database is using Active Data Guard :
                  SELECT database_role, open_mode FROM v$database;
                  If you start a database using the STARTUP command and then invoke managed recovery, the Active Data Guard will be enabled.

                  Ex.
                  SQL> STARTUP
                  
                  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE WITH SESSION SHUTDOWN;
                  If the database is started using the STARTUP MOUNT command and then managed recovery is invoked, Active Data Guard will not be enabled.



                  Question 3:

                  Not sure how to answer. I like to check everything before I restart, so I don't do this.

                  Best Regards

                  mseberg
                  • 6. Re: ORA-01033: ORACLE initialization or shutdown in progress / TAF
                    hqt200475
                    Hi MSeberg,

                    You wrote:

                    >

                    Not sure how to answer. I like to check everything before I restart, so I don't do this.

                    >

                    My questions:
                    >

                    What do you have to check?

                    >

                    and

                    >

                    After every Operating System reboot?
                    >

                    regards

                    hqt200475
                    • 7. Re: ORA-01033: ORACLE initialization or shutdown in progress / TAF
                      mseberg
                      After the OS reboot or any Database shutdown.

                      I check alert logs, sequence numbers, space in FRA and generally do a log switch on the primary to confirm DG is working.

                      For Example :
                      PROMPT
                      PROMPT Checking Size and usage in GB of Flash Recovery Area
                      PROMPT
                      
                      SELECT 
                        ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 
                        ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 
                        ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
                        SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
                      FROM 
                        V$RECOVERY_FILE_DEST A,
                        V$FLASH_RECOVERY_AREA_USAGE B
                      GROUP BY
                        SPACE_LIMIT, 
                        SPACE_USED , 
                        SPACE_RECLAIMABLE ;
                      and then
                      column FILE_TYPE format a20
                       
                      select * from v$flash_recovery_area_usage;
                      Best Regards

                      mseberg
                      • 8. Re: ORA-01033: ORACLE initialization or shutdown in progress / TAF
                        user588212
                        Below works when primary host is the first on the list of address, but when standby host is the first, we have ORA-01033: ORACLE initialization or shutdown in progress ... how can we make sure that we use the same service name and connect string even when standby host or SCAN name is the first on the address list?


                        Below Works when primary node is dwh
                        STRM=
                        (DESCRIPTION=
                             (LOAD_BALANCE=on)
                             (FAILOVER=on)
                             (ADDRESS=(PROTOCOL=tcp)(HOST=dwh)(PORT=1521))
                             (ADDRESS=(PROTOCOL=tcp)(HOST=stb)(PORT=1521))
                                  (CONNECT_DATA=
                                       (SERVICE_NAME=strm)
                                       (FAILOVER_MODE=(TYPE=select)(METHOD=basic))
                                  )
                        )


                        But below does not work (ORA-01033: ORACLE initialization or shutdown in progress ) when primary is still dwh but order in connectstring is stb (standby host) before dwh(primary)
                        STRM=
                        (DESCRIPTION=
                             (LOAD_BALANCE=on)
                             (FAILOVER=on)
                             (ADDRESS=(PROTOCOL=tcp)(HOST=stb)(PORT=1521))
                             (ADDRESS=(PROTOCOL=tcp)(HOST=dwh)(PORT=1521))
                                  (CONNECT_DATA=
                                       (SERVICE_NAME=strm)
                                       (FAILOVER_MODE=(TYPE=select)(METHOD=basic))
                                  )
                        )