Find out the state of database from sql plus
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Find out the state of database from sql plus

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,

    How do i find out the state of the statebase (mounted,open ) from sqlplus.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    select database_status from v$instance
    select * from v$database

    Hope this helps
    KN

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    U should use ONLY x$ tables for this query,
    because if instance not in OPEN mode and oracle can't read from SYSTEM tablespace yet.
    Oracle can't use v$fixed_view_definition for works with v$instance fixed view.

    select ks.inst_id,decode(ksuxssts,0,'STARTED',1,'MOUNTED',2,'OPEN','UNKNOWN'),
    decode(ksuxsarc,0,'STOPPED',1,'STARTED','FAILED'),
    decode(ksuxslsw,0,NULL,2,'ARCHIVE LOG',3,'CLEAR LOG',4,'CHECKPOINT'),
    decode(kvitval,0,'ACTIVE',2147483647,'SUSPENDED','INSTANCE RECOVERY'),
    decode(ksuxsrol,1,'PRIMARY_INSTANCE',2,'SECONDARY_INSTANCE','UNKNOWN')
    from
    x$ksuxsinst ks, x$kvit kv
    where kvittag = 'kcbwst';

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well v$instance should be enough

    Code:
    >startup nomount
    ORACLE instance started.
    
    Total System Global Area   41764892 bytes
    Fixed Size                    75804 bytes
    Variable Size              33419264 bytes
    Database Buffers            8192000 bytes
    Redo Buffers                  77824 bytes
    >select * from v$instance;
    
    INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                                        VERSION
    --------------- ---------------- ---------------------------------------------------------------- -----------------
    STARTUP_T STATUS  PAR    THREAD# ARCHIVE LOG_SWITCH_ LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE
    --------- ------- --- ---------- ------- ----------- ---------- --- ----------------- ------------------
                  1 dev817           PANDO                                                            8.1.7.0.0
    2002-MAY- STARTED              0 STOPPED             ALLOWED    NO  ACTIVE            UNKNOWN
    
    
    >alter database mount;
    
    Database altered.
    
    >select * from v$instance;
    
    INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                                        VERSION
    --------------- ---------------- ---------------------------------------------------------------- -----------------
    STARTUP_TIME         STATUS  PAR    THREAD# ARCHIVE LOG_SWITCH_ LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE
    -------------------- ------- --- ---------- ------- ----------- ---------- --- ----------------- ------------------
                  1 dev817           PANDO                                                            8.1.7.0.0
    2002-MAY-06 22:32:46 MOUNTED NO           1 STOPPED             ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE
    
    
    >alter database open;
    
    Database altered.
    
    >
    >select * from v$instance;
    
    INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                                        VERSION
    --------------- ---------------- ---------------------------------------------------------------- -----------------
    STARTUP_TIME         STATUS  PAR    THREAD# ARCHIVE LOG_SWITCH_ LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE
    -------------------- ------- --- ---------- ------- ----------- ---------- --- ----------------- ------------------
                  1 dev817           PANDO                                                            8.1.7.0.0
    2002-MAY-06 22:32:46 OPEN    NO           1 STOPPED             ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE
    look status column and see the different values

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Thanks Pando.
    In this case oracle keep some of definitions of V$ tables as part of software code and view V$FIXED_VIEW_DEFINITION is nothing.
    Oracle instance can use another structure of V$ tables, as part of hard code.


    oldev:sys> startup nomount;
    ORACLE instance started.

    Total System Global Area 220325872 bytes
    Fixed Size 69616 bytes
    Variable Size 85860352 bytes
    Database Buffers 134215680 bytes
    Redo Buffers 180224 bytes
    oldev:sys> select * from v$mystat;

    SID STATISTIC# VALUE
    ---------- ---------- ----------
    7 0 1
    7 1 1
    7 2 3
    7 3 1
    7 4 0
    ...

    oldev:sys> select * from V$DATAFILE_HEADER;
    select * from V$DATAFILE_HEADER
    *
    ERROR at line 1:
    ORA-01507: database not mounted
    ...
    oldev:sys> desc V$SQL_CURSOR
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    CURNO NUMBER
    FLAG NUMBER
    STATUS VARCHAR2(9)
    PARENT_HANDLE RAW(4)
    PARENT_LOCK RAW(4)
    CHILD_LOCK RAW(4)
    CHILD_PIN RAW(4)
    PERS_HEAP_MEM NUMBER
    WORK_HEAP_MEM NUMBER
    BIND_VARS NUMBER
    DEFINE_VARS NUMBER
    BIND_MEM_LOC VARCHAR2(64)
    INST_FLAG VARCHAR2(64)
    INST_FLAG2 VARCHAR2(64)


    --------------------------------------------
    NOTE : V$FIXED_VIEW_DEFINITION - part of hard code too....


    [Edited by Shestakov on 05-06-2002 at 05:09 PM]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width