-
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.
-
select database_status from v$instance
select * from v$database
Hope this helps
KN
-
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';
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|