This discussion is archived
8 Replies Latest reply: Mar 27, 2013 12:52 PM by user588212 RSS

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

hqt200475 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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))
              )
    )

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points