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

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;