Find out the state of database from sql plus

[ronnie]

Hi,

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

Thanks

[Knarayan]

select database_status from v$instance
select * from v$database

Hope this helps

[Shestakov]

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';

[pando]

well v$instance should be enough

>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

[Shestakov]

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]