1.查看Oracle的进程
[oracle@asm01 ~]$ ps -ef | grep ora_ | grep -v grep
oracle 16633 1 0 17:04 ? 00:00:02 ora_pmon_orcl
oracle 16635 1 0 17:04 ? 00:00:00 ora_clmn_orcl
oracle 16637 1 0 17:04 ? 00:00:05 ora_psp0_orcl
oracle 16640 1 2 17:05 ? 00:05:11 ora_vktm_orcl
oracle 16644 1 0 17:05 ? 00:00:01 ora_gen0_orcl
oracle 16646 1 0 17:05 ? 00:00:00 ora_mman_orcl
oracle 16650 1 0 17:05 ? 00:00:12 ora_gen1_orcl
oracle 16653 1 0 17:05 ? 00:00:01 ora_diag_orcl
oracle 16655 1 0 17:05 ? 00:00:00 ora_ofsd_orcl
oracle 16658 1 0 17:05 ? 00:00:15 ora_dbrm_orcl
oracle 16660 1 0 17:05 ? 00:00:00 ora_vkrm_orcl
oracle 16662 1 0 17:05 ? 00:00:01 ora_svcb_orcl
oracle 16664 1 0 17:05 ? 00:00:02 ora_pman_orcl
oracle 16666 1 0 17:05 ? 00:00:44 ora_dia0_orcl
oracle 16668 1 0 17:05 ? 00:00:01 ora_dbw0_orcl
oracle 16670 1 0 17:05 ? 00:00:00 ora_lgwr_orcl
oracle 16672 1 0 17:05 ? 00:00:07 ora_ckpt_orcl
oracle 16674 1 0 17:05 ? 00:00:00 ora_lg00_orcl
oracle 16676 1 0 17:05 ? 00:00:00 ora_smon_orcl
oracle 16678 1 0 17:05 ? 00:00:00 ora_lg01_orcl
oracle 16680 1 0 17:05 ? 00:00:02 ora_smco_orcl
oracle 16682 1 0 17:05 ? 00:00:00 ora_reco_orcl
oracle 16684 1 0 17:05 ? 00:00:00 ora_w000_orcl
oracle 16686 1 0 17:05 ? 00:00:01 ora_lreg_orcl
oracle 16688 1 0 17:05 ? 00:00:00 ora_w001_orcl
oracle 16690 1 0 17:05 ? 00:00:00 ora_pxmn_orcl
oracle 16692 1 0 17:05 ? 00:00:21 ora_fenc_orcl
oracle 16694 1 0 17:05 ? 00:00:09 ora_mmon_orcl
oracle 16696 1 0 17:05 ? 00:00:19 ora_mmnl_orcl
oracle 16699 1 0 17:05 ? 00:00:00 ora_d000_orcl
oracle 16701 1 0 17:05 ? 00:00:00 ora_s000_orcl
oracle 16703 1 0 17:05 ? 00:00:00 ora_tmon_orcl
oracle 16713 1 0 17:05 ? 00:00:27 ora_m000_orcl
oracle 16725 1 0 17:05 ? 00:00:00 ora_tt00_orcl
oracle 16727 1 0 17:05 ? 00:00:00 ora_tt01_orcl
oracle 16729 1 0 17:05 ? 00:00:01 ora_tt02_orcl
oracle 16733 1 0 17:05 ? 00:00:00 ora_w002_orcl
oracle 16736 1 0 17:05 ? 00:00:00 ora_aqpc_orcl
oracle 16738 1 0 17:05 ? 00:00:00 ora_qm02_orcl
oracle 16742 1 0 17:05 ? 00:00:00 ora_q002_orcl
oracle 16748 1 0 17:05 ? 00:00:01 ora_p000_orcl
oracle 16750 1 0 17:05 ? 00:00:01 ora_p001_orcl
oracle 16752 1 0 17:05 ? 00:00:01 ora_p002_orcl
oracle 16754 1 0 17:05 ? 00:00:01 ora_p003_orcl
oracle 16756 1 0 17:05 ? 00:00:01 ora_p004_orcl
oracle 16758 1 0 17:05 ? 00:00:01 ora_p005_orcl
oracle 16760 1 0 17:05 ? 00:00:01 ora_p006_orcl
oracle 16762 1 0 17:05 ? 00:00:01 ora_p007_orcl
oracle 16764 1 0 17:05 ? 00:00:01 ora_p008_orcl
oracle 16766 1 0 17:05 ? 00:00:01 ora_p009_orcl
oracle 16768 1 0 17:05 ? 00:00:01 ora_p00a_orcl
oracle 16770 1 0 17:05 ? 00:00:01 ora_p00b_orcl
oracle 16772 1 0 17:05 ? 00:00:01 ora_p00c_orcl
oracle 16774 1 0 17:05 ? 00:00:01 ora_p00d_orcl
oracle 16776 1 0 17:05 ? 00:00:01 ora_p00e_orcl
oracle 16778 1 0 17:05 ? 00:00:01 ora_p00f_orcl
oracle 16781 1 0 17:05 ? 00:00:01 ora_p00g_orcl
oracle 16783 1 0 17:05 ? 00:00:01 ora_p00h_orcl
oracle 16785 1 0 17:05 ? 00:00:01 ora_p00i_orcl
oracle 16787 1 0 17:05 ? 00:00:01 ora_p00j_orcl
oracle 16791 1 0 17:05 ? 00:00:22 ora_cjq0_orcl
oracle 16969 1 0 17:05 ? 00:00:00 ora_w003_orcl
oracle 16987 1 0 17:05 ? 00:00:30 ora_m001_orcl
oracle 16989 1 0 17:05 ? 00:00:30 ora_m002_orcl
oracle 17021 1 0 17:05 ? 00:00:00 ora_w004_orcl
oracle 17068 1 0 17:05 ? 00:00:00 ora_q003_orcl
oracle 17833 1 0 17:15 ? 00:00:30 ora_m004_orcl
oracle 17838 1 0 17:15 ? 00:00:00 ora_w005_orcl
oracle 17843 1 0 17:15 ? 00:00:00 ora_w006_orcl
oracle 17850 1 0 17:15 ? 00:00:00 ora_w007_orcl
oracle 17883 1 0 17:15 ? 00:00:00 ora_w008_orcl
oracle 17888 1 0 17:15 ? 00:00:00 ora_w009_orcl
oracle 17892 1 0 17:15 ? 00:00:00 ora_w00a_orcl
oracle 17896 1 0 17:15 ? 00:00:00 ora_w00b_orcl
oracle 17903 1 0 17:15 ? 00:00:00 ora_w00c_orcl
oracle 17908 1 0 17:15 ? 00:00:00 ora_w00d_orcl
oracle 17912 1 0 17:15 ? 00:00:00 ora_w00e_orcl
oracle 17917 1 0 17:15 ? 00:00:00 ora_w00f_orcl
oracle 17923 1 0 17:15 ? 00:00:00 ora_w00g_orcl
oracle 17928 1 0 17:15 ? 00:00:00 ora_w00h_orcl
oracle 17932 1 0 17:15 ? 00:00:00 ora_w00i_orcl
oracle 17938 1 0 17:16 ? 00:00:00 ora_w00j_orcl
oracle 18673 1 0 17:25 ? 00:00:25 ora_m005_orcl
oracle 26139 1 0 18:55 ? 00:00:13 ora_m003_orcl
oracle 30323 1 0 19:42 ? 00:00:00 ora_asmb_orcl
oracle 30327 1 0 19:42 ? 00:00:00 ora_rbal_orcl
oracle 30330 1 0 19:42 ? 00:00:00 ora_mark_orcl
在检查Oracle的进程命令输出后，输出显示至少应包括以下一些进程：
Oracle 写数据文件的进程，输出显示为：”ora_dbw0_orcl”
Oracle 写日志文件的进程，输出显示为：”ora_lgwr_orcl”
Oracle 监听实例状态的进程，输出显示为：”ora_smon_orcl”
Oracle 监听客户端连接进程状态的进程，输出显示为：”ora_pmon_orcl”
2.查看数据库的实例状态
SQL> set linesize 200
SQL> set pagesize 200
SQL> col instance_name for a20
SQL> col status for a20
SQL> col database_status for a20
SQL> select instance_name,status,version,database_status from v$instance;
INSTANCE_NAME STATUS VERSION DATABASE_STATUS
-------------------- -------------------- ---------------------------------- --------------------
orcl OPEN 19.0.0.0.0 ACTIVE
3.查看数据库的日志模式
SQL> select name,log_mode,open_mode from v$database;
NAME LOG_MODE OPEN_MODE
------------------ ------------------------ ----------------------------------------
ORCL NOARCHIVELOG READ WRITE
其中LOG_MODE表示Oracle当前的归档方式。ARCHIVELOG表示数据库运行在归档模式下，NOARCHIVELOG表示数据库运行在非归档模式下。
4.查看数据库alert_SID.log 中的ora-的错误信息
[oracle@asm01 trace]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@asm01 trace]$ cat alert_orcl.log | grep -i ora-
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ORCL/redo01.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ORCL/redo01.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORCL/redo03.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORCL/redo03.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ORCL/redo01.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ORCL/redo01.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORCL/redo03.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORCL/redo03.log'
ORA-27037: unable to obtain file status
ORA-65221 signalled during: alter pluggable database application APP$CDB$SYSTEM begin install '1.0'...
5.查alert_SID.log中的err的信息
[oracle@asm01 trace]$ cat alert_orcl.log | grep -i err-
6.查alert_SID.log的fail的信息
[oracle@asm01 trace]$ cat alert_orcl.log | grep -i failed
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00313: open failed for members of log group 3 of thread 1
7.查看控制文件状态
SQL> col name for a50
SQL> select status,name from v$controlfile;
STATUS NAME
-------------------- --------------------------------------------------
/u01/app/oracle/oradata/ORCL/control01.ctl
/u01/app/oracle/oradata/ORCL/control02.ctl
STATUS状态为空表示控制文件状态正常。
8.查询日志状态
SQL> col member for a50
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- -------------------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORCL/redo03.log
2 /u01/app/oracle/oradata/ORCL/redo02.log
1 /u01/app/oracle/oradata/ORCL/redo01.log
9.查询数据状态
SQL> select file#,status,name from v$datafile;
FILE# STATUS NAME
---------- -------------------- --------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/ORCL/system01.dbf
3 ONLINE /u01/app/oracle/oradata/ORCL/sysaux01.dbf
4 ONLINE /u01/app/oracle/oradata/ORCL/undotbs01.dbf
5 SYSTEM /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
6 ONLINE /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
7 ONLINE /u01/app/oracle/oradata/ORCL/users01.dbf
8 ONLINE /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
9 SYSTEM /u01/app/oracle/oradata/ORCL/pdb/system01.dbf
10 ONLINE /u01/app/oracle/oradata/ORCL/pdb/sysaux01.dbf
11 ONLINE /u01/app/oracle/oradata/ORCL/pdb/undotbs01.dbf
12 ONLINE /u01/app/oracle/oradata/ORCL/pdb/users01.dbf
13 ONLINE +DATA/ORCL/DATAFILE/tbs01.257.1086205379
12 rows selected.
10.查询表空间状态
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------------------------------------ --------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TBS01 ONLINE
TEMP_TBS01 ONLINE
7 rows selected.
11.检查Oracle所有回滚段的状态
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------------------------------------ --------------------
SYSTEM ONLINE
_SYSSMU1_1261223759$ ONLINE
_SYSSMU2_27624015$ ONLINE
_SYSSMU3_2421748942$ ONLINE
_SYSSMU4_625702278$ ONLINE
_SYSSMU5_2101348960$ ONLINE
_SYSSMU6_813816332$ ONLINE
_SYSSMU7_2329891355$ ONLINE
_SYSSMU8_399776867$ ONLINE
_SYSSMU9_1692468413$ ONLINE
_SYSSMU10_930580995$ ONLINE
11 rows selected.
12.查看Oracle初始化文件中相关的参数值
SQL> col RESOURCE_NAME for a30
SQL> select resource_name,current_utilization,max_utilization,initial_allocation ,limit_value from v$resource_limit;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION
------------------------------ ------------------- --------------- --------------------------------------------------------------------------------
LIMIT_VALUE
--------------------------------------------------------------------------------
processes 92 97 2000
2000
sessions 107 112 3030
3030
enqueue_locks 33 59 34920
34920
enqueue_resources 24 45 13552
UNLIMITED
ges_procs 0 0 0
0
ges_ress 0 0 0
UNLIMITED
ges_locks 0 0 0
UNLIMITED
ges_cache_ress 0 0 0
UNLIMITED
ges_reg_msgs 0 0 0
UNLIMITED
ges_big_msgs 0 0 0
UNLIMITED
ges_rsv_msgs 0 0 0
0
gcs_resources 0 0 0
UNLIMITED
gcs_shadows 0 0 0
UNLIMITED
smartio_overhead_memory 0 0 0
UNLIMITED
smartio_buffer_memory 0 0 0
UNLIMITED
smartio_metadata_memory 0 0 0
UNLIMITED
smartio_sessions 0 0 0
UNLIMITED
dml_locks 0 19 13332
UNLIMITED
temporary_table_locks 0 0 UNLIMITED
UNLIMITED
transactions 0 1 3333
UNLIMITED
branches 0 0 3333
UNLIMITED
cmtcallbk 0 2 3333
UNLIMITED
max_rollback_segments 11 11 3333
65535
sort_segment_locks 2 4 UNLIMITED
UNLIMITED
k2q_locks 0 0 6060
UNLIMITED
max_shared_servers 1 2 UNLIMITED
UNLIMITED
parallel_max_servers 20 20 200
32767
13.检查Oracle各个表空间的增长情况
Select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
from (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A, (select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B
where A.tablespace_name=B.tablespace_name;
TABLESPACE_NAME USED_PERCENT
------------------------------------------------------------ ------------
SYSTEM 99.9438202
TBS01 .2
SYSAUX 94.7916667
UNDOTBS1 68.2366071
USERS 53.75
14.检查一些扩展异常的对象
select segment_name,segment_type,tablespace_name,
(extents/max_extents)*100 Percent from sys.DBA_segments
where max_extents!=0 and (extents/max_extents)*100>=95
order by percent;
no rows selected
15.检查system表空间内的内容
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and owner!='SYS'
and owner!='SYSTEM'
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM'
and owner!='SYS' and owner!='SYSTEM';
OWNER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GSMADMIN_INTERNAL
LBACSYS
OJVMSYS
OUTLN
如果有记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与应用相关.
16.检查对象的下一扩展与表空间的最大扩展值
select a.table_name,a.next_extent,a.tablespace_name
from all_tables a,
(select tablespace_name,max(bytes) as big_chunk
from dba_free_space group by tablespace_name) f
where f.tablespace_name=a.tablespace_name
and a.next_extent>f.big_chunk
union
select a.index_name,a.next_extent,a.tablespace_name
from all_indexes a,
(select tablespace_name,max(bytes) as big_chunk
from dba_free_space
group by tablespace_name) f
where f.tablespace_name=a.tablespace_name
and a.next_extent>f.big_chunk;
如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需调整相应表空间的存储参数
版权声明：本文为博主原创文章，未经博主允许不得转载。
Linux,oracle
