返回首页
当前位置: 主页 > 网络编程 > Jsp实例教程 >

linux中oracle的日常维护命令大全

时间:2011-04-26 20:50来源:知行网www.zhixing123.cn 编辑:麦田守望者

1. 检查Oracle的进程
$ ps -ef|grep "ora_"|grep -v grep
oracle 5998 1 0 11:15:59 ? 0:01 ora_j000_PPRD10
oracle 2968 1 0 21:16:57 ? 0:00 ora_q000_PPRD10
oracle 2927 1 0 21:16:33 ? 0:00 ora_pmon_PPRD10
oracle 2933 1 0 21:16:34 ? 0:07 ora_dbw0_PPRD10
oracle 2945 1 0 21:16:34 ? 0:02 ora_mmon_PPRD10
oracle 2931 1 0 21:16:33 ? 0:00 ora_mman_PPRD10
oracle 2949 1 0 21:16:34 ? 0:00 ora_d000_PPRD10
oracle 2970 1 0 21:16:57 ? 0:00 ora_q001_PPRD10
oracle 2935 1 0 21:16:34 ? 0:05 ora_lgwr_PPRD10
oracle 2951 1 0 21:16:34 ? 0:00 ora_s000_PPRD10
oracle 2939 1 0 21:16:34 ? 0:06 ora_smon_PPRD10
oracle 2957 1 0 21:16:47 ? 0:00 ora_qmnc_PPRD10
oracle 2943 1 0 21:16:34 ? 0:05 ora_cjq0_PPRD10
oracle 2947 1 0 21:16:34 ? 0:00 ora_mmnl_PPRD10
oracle 2937 1 0 21:16:34 ? 0:18 ora_ckpt_PPRD10
oracle 2941 1 0 21:16:34 ? 0:00 ora_reco_PPRD10
oracle 2929 1 0 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_NAME STATUS VERSION 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;

NAME LOG_MODE OPEN_MODE
--------- ------------ ----------
PPRD10 NOARCHIVELOG READ 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;
STATUS NAME
------- ------------------------------------------------------------------------
/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# STATUS MEMBER
---------- ------- -------------------------------------------------------------
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
---------- ------- --------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
2 ONLINE /u01/app/oracle/oradata/orcl/undotbs01.dbf
3 ONLINE /u01/app/oracle/oradata/orcl/sysaux01.dbf
4 ONLINE /u01/app/oracle/oradata/orcl/users01.dbf
5 ONLINE /u01/app/oracle/oradata/orcl/example01.dbf
6 ONLINE /u01/app/oracle/oradata/orcl/perfstat.dbf
7 ONLINE /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
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
PERFSTAT ONLINE
RISENET ONLINE

“ONLINE”表示正常在线状态

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

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME STATUS
------------------------------ ----------------
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_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LI
------------------------------ ------------------- --------------- ---------- --
processes 26 31 150
sessions 30 37 170
enqueue_locks 23 31 2300
enqueue_resources 23 49 968 U
ges_procs 0 0 0
ges_ress 0 0 0 U
ges_locks 0 0 0 U
ges_cache_ress 0 0 0 U
ges_reg_msgs 0 0 0 U
ges_big_msgs 0 0 0 U
ges_rsv_msgs 0 0 0

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LI
------------------------------ ------------------- --------------- ---------- --
gcs_resources 0 0 0
gcs_shadows 0 0 0
dml_locks 0 68 748 U
temporary_table_locks 0 3 UNLIMITED U
transactions 2 11 187 U
branches 0 0 187 U
cmtcallbk 0 2 187 U
sort_segment_locks 0 3 UNLIMITED U
max_rollback_segments 11 11 187
max_shared_servers 1 1 UNLIMITED U
parallel_max_servers 0 0 40

22 rows selected.


11. 检查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;

顶一下
(0)
0%
踩一下
(0)
0%
标签(Tag):数据库 oralce
------分隔线----------------------------
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码:点击我更换图片