Oracle 数据库状态查询

启动状态	SQL语句	结果
nomount	select status from v$instance;	STARTED
	select open_mode from v$database;	ERROR at line 1: ORA-01507: database not mounted
mount	select status from v$instance;	MOUNTED
	select open_mode from v$database;	MOUNTED
open	select status from v$instance;	OPEN
	select open_mode from v$database;	READ WRITE 或者 READ ONLY

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 125829120 bytes
Fixed Size 1247684 bytes
Variable Size 92276284 bytes
Database Buffers           25165824 bytes
Redo Buffers                7139328 bytes

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> alter database mount;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

Alert 日志

11g以前oracle的alter文件是在bdump目录下呢。 oracle 11g一改常态以XML与传统的文本两种格式提供 Alert 日志。 oracle 11g新的日志的位置由Automatic Diagnostic Respository(ADR)决定。 可以通过新的初始化参数DIAGNOSTIC_DEST控制ADR BASE的位置。
显示DIAGNOSTIC_DEST的位置

SQL> show parameter DIAGNOSTIC_DEST;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string /u01/app/oracle

查询两种日志的位置 查询xml文件的目录

SQL> select value from v$diag_info where name='Diag Alert';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/test/test/alert

查询文本文件的目录 

SQL> select value from v$diag_info where name='Diag Trace';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/test/test/trace

也可以通过查询background_dump_dest来查看oracle的文本日志

SQL> show parameter background_dump_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string /u01/app/oracle/diag/rdbms/tes
                                                 t/test/trace

OR

SQL> select value from v$parameter where name='background_dump_dest';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/test/test/trace

参考：http://blog.csdn.net/huang_xw/article/details/6173876