linux中oracle的日常维护命令

1.检查Oracle的进程
$ps -ef|grep "ora_"|grep -v grep
oracle5998 10 11:15:59 ?0:01 ora_j000_PPRD10
oracle2968 10 21:16:57 ?0:00 ora_q000_PPRD10
oracle2927 10 21:16:33 ?0:00 ora_pmon_PPRD10
oracle2933 10 21:16:34 ?0:07 ora_dbw0_PPRD10
oracle2945 10 21:16:34 ?0:02 ora_mmon_PPRD10
oracle2931 10 21:16:33 ?0:00 ora_mman_PPRD10
oracle2949 10 21:16:34 ?0:00 ora_d000_PPRD10
oracle2970 10 21:16:57 ?0:00 ora_q001_PPRD10
oracle2935 10 21:16:34 ?0:05 ora_lgwr_PPRD10
oracle2951 10 21:16:34 ?0:00 ora_s000_PPRD10
oracle2939 10 21:16:34 ?0:06 ora_smon_PPRD10
oracle2957 10 21:16:47 ?0:00 ora_qmnc_PPRD10
oracle2943 10 21:16:34 ?0:05 ora_cjq0_PPRD10
oracle2947 10 21:16:34 ?0:00 ora_mmnl_PPRD10
oracle2937 10 21:16:34 ?0:18 ora_ckpt_PPRD10
oracle2941 10 21:16:34 ?0:00 ora_reco_PPRD10
oracle2929 10 21:16:33 ?0:00 ora_psp0_PPRD10

在检查Oracle的进程命令输出后，输出显示至少应包括以下一些进程：
? Oracle写数据文件的进程，输出显示为：“ora_dbw0_ORCL”
? Oracle写日志文件的进程，输出显示为：“ora_lgwr_ORCL”
? Oracle监听实例状态的进程，输出显示为：“ora_smon_ORCL”
? Oracle监听客户端连接进程状态的进程，输出显示为：“ora_pmon_ORCL”
? Oracle进行归档的进程，输出显示为：“ora_arc0_ORCL”
? Oracle进行检查点的进程，输出显示为：“ora_ckpt_ORCL”
? Oracle进行[url=javascript:;]恢复[/url]的进程，输出显示为：“ora_reco_ORCL”


2 .查看数据库的实例：
SQL> select instance_name,status,version,database_status from v$instance;

INSTANCE_NAMESTATUSVERSION DATABASE_STATUS
---------------------------- ----------------------------------
PPRD10 OPEN 10.2.0.4.0 ACTIVE

其中“STATUS”表示Oracle当前的实例状态，必须为“OPEN”；DATABASE_STATUS”表示Oracle当前数据库的状态，必须为“ACTIVE”。


3 .查看数据库的日志模式，打开模式
SQL> select name,log_mode,open_mode from v$database;

NAMELOG_MODE OPEN_MODE
-------------------------------
PPRD10NOARCHIVELOGREAD WRITE

其中“LOG_MODE”表示Oracle当前的归档方式。“ARCHIVELOG”表示数据库运行在归档模式下，“NOARCHIVELOG”表示数据库运行在非归档模式下。

4 .查看数据库alert_SID.log 中的ora-的错误信息：
$ more /u01/app/oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep -i ora-

WARNING: inbound connection timed out (ORA-3136)
WARNING: inbound connection timed out (ORA-3136)
ORA-12012: error on auto execute of job 78677
ORA-20820: ORA-20820:
ORA-06512: at "PS_SYSTEM.ROW_VALIDATOR_T", line 912
ORA-06512: at "PS_SYSTEM.ROW_VALIDATOR_T", line 924
ORA-06512: at "WORKORDER.INVENTORY_UTL", line 1260
ORA-12012: error on auto execute of job 78677


4.1查alert_SID.log中的err的信息：
$ more /u01/app/oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep -i err

Errors in file /u01/app/oracle/admin/pinnsoft/bdump/pinnsoft_j000_2666.trc:
ORA-12012: error on auto execute of job 78677
: 6000101: Error occurred when rolling Inventory date
Errors in file /u01/app/oracle/admin/pinnsoft/bdump/pinnsoft_j000_11886.trc:
ORA-12012: error on auto execute of job 78677
: 6000101: Error occurred when rolling Inventory date
Errors in file /u01/app/oracle/admin/pinnsoft/bdump/pinnsoft_j000_21375.trc:
ORA-12012: error on auto execute of job 78677
: 6000101: Error occurred when rolling Inventory date


4.2查alert_SID.log的fail的信息：
$ more /u01/app/oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep -i fail

PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump


5. 检查控制文件状态
SQL> select status,name from v$controlfile;
STATUSNAME
-------------------------------------------------------------------------------
/data/app/oracle/oradata/PPRD10/control01.ctl
/data/app/oracle/oradata/PPRD10/control02.ctl
/data/app/oracle/oradata/PPRD10/control03.ctl

输出结果应该有3条以上（包含3条）的记录，“STATUS”应该为空。状态为空表示控制文件状态正常。

6.查询日志状态
SQL> select group#,status,member from v$logfile;

GROUP#STATUSMEMBER
------------------------------------------------------------------------------
1 /data/app/oracle/oradata/PPRD10/redo01.log
2 /data/app/oracle/oradata/PPRD10/redo02.log
3 /data/app/oracle/oradata/PPRD10/redo03.log

输出结果应该有3条以上(包含 3 条)记录,”STATUS”应该为非”INVALID”，非“DELETED”。

7 .查询数据状态：
SQL> select file#,status,name from v$datafile;

FILE#STATUS NAME
-------------------------------------------------------------------
1SYSTEM/u01/app/oracle/oradata/orcl/system01.dbf
2ONLINE/u01/app/oracle/oradata/orcl/undotbs01.dbf
3ONLINE/u01/app/oracle/oradata/orcl/sysaux01.dbf
4ONLINE/u01/app/oracle/oradata/orcl/users01.dbf
5ONLINE/u01/app/oracle/oradata/orcl/example01.dbf
6ONLINE/u01/app/oracle/oradata/orcl/perfstat.dbf
7ONLINE/u01/app/oracle/oradata/orcl/risenet.dbf

“ONLINE"表示正常在线状态

8 .查询表空间状态
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMPONLINE
USERSONLINE
EXAMPLE ONLINE
PERFSTATONLINE
RISENETONLINE

“ONLINE”表示正常在线状态

9.检查Oracle所有回滚段的状态

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAMESTATUS
----------------------------------------------
SYSTEM ONLINE
_SYSSMU10$ONLINE
_SYSSMU9$ONLINE
_SYSSMU8$ONLINE
_SYSSMU7$ONLINE
_SYSSMU6$ONLINE
_SYSSMU5$ONLINE
_SYSSMU4$ONLINE
_SYSSMU3$ONLINE
_SYSSMU2$ONLINE
_SYSSMU1$ONLINE

11 rows selected.

10 .检查Oracle初始化文件中相关的参数值

SQL>select resource_name,current_utilization,max_utilization,initial_allocation,limit_value from v$resource_limit;

RESOURCE_NAMECURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LI
------------------------------ ------------------- --------------- ---------- --
processes 2631150
sessions3037170
enqueue_locks 2331 2300
enqueue_resources 2349968U
ges_procs0 00
ges_ress 0 00U
ges_locks0 00U
ges_cache_ress 0 00U
ges_reg_msgs 0 00U
ges_big_msgs 0 00U
ges_rsv_msgs 0 00

RESOURCE_NAMECURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LI
------------------------------ ------------------- --------------- ---------- --
gcs_resources0 00
gcs_shadows0 00
dml_locks068748U
temporary_table_locks0 3UNLIMITEDU
transactions 211187U
branches 0 0187U
cmtcallbk0 2187U
sort_segment_locks 0 3UNLIMITEDU
max_rollback_segments 1111187
max_shared_servers 1 1UNLIMITEDU
parallel_max_servers 0 0 40

22 rows selected.


11.检查Oracle各个表空间的增长情况

SelectA.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;

SQL> SelectA.tablespace_name,(1-(A.total)/B.total)*100 used_percent
2from (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;
3

TABLESPACE_NAMEUSED_PERCENT
------------------------------ ------------
UNDOTBS133.25
SYSAUX 99.0364583
RISENET.0125
USERS 62.5
SYSTEM 98.6067708
EXAMPLE 68.25
PERFSTAT 28.7625

7 rows selected.

12 .检查一些扩展异常的对象
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;

SQL> select segment_name,segment_type,tablespace_name,
2(extents/max_extents)*100 Percent
3from sys.DBA_segments
4where max_extents!=0 and (extents/max_extents)*100>=95
5order by percent;

no rows selected

如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要修改它的存储结构参数.

13.检查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';
SQL> select distinct(owner) from dba_tables
2where tablespace_name='SYSTEM' and
3owner!='SYS' and owner!='SYSTEM'
4union
5select distinct(owner) from dba_indexes
6where tablespace_name='SYSTEM' and
7owner!='SYS' and owner!='SYSTEM';

OWNER
------------------------------
MDSYS
OLAPSYS
OUTLN

如果有记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值，

14.检查对象的下一扩展与表空间的最大扩展值
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;

SQL> select a.table_name,a.next_extent,a.tablespace_name
2from all_tables a,
3(select tablespace_name,max(bytes) as big_chunk
4from dba_free_space group by tablespace_name) f
5where f.tablespace_name=a.tablespace_name
6and a.next_extent>f.big_chunk
7union
8select a.index_name,a.next_extent,a.tablespace_name
9from all_indexes a,
10(select tablespace_name,max(bytes) as big_chunk
11from dba_free_space
12group by tablespace_name) f
13where f.tablespace_name=a.tablespace_name
14and a.next_extent>f.big_chunk;

no rows selected

如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需调整相应表空间的存储参数