oracle数据库日常维护（1） - 好文

1. 检查数据库基本状况

主要对数据库的基本状况进行检查，其中包含：检查Oracle实例状态，检查Oracle服务进程，检查Oracle监听进程，共三个部分。

SQL> select instance_name,host_name,startup_time,status,database_status from
v$instance;

INSTANCE_NAME  HOST_NAME  STARTUP_TIME STATUS    DATABASE_STATUS

----------------      -------------------    --------------------   ----------    ------------
----

CKDB             AS14          2009-5-7 9:3      OPEN        ACTIVE

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

SQL> select name,log_mode,open_mode from v$database;

NAME      LOG_MODE     OPEN_MODE

---------      ------------        -----------------

CKDB      ARCHIVELOG   READ WRITE

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

1.1. 检查Oracle服务进程

$ps -ef|grep ora_|grep -v grep&&ps -ef|grep ora_|grep -v grep|wc –l

oracle    2960     1 0 May07 ?        00:01:02 ora_pmon_CKDB

oracle    2962     1 0 May07 ?        00:00:22 ora_psp0_CKDB

oracle    2964     1 0 May07 ?        00:00:00 ora_mman_CKDB

oracle    2966     1 0 May07 ?        00:03:20 ora_dbw0_CKDB

oracle    2968     1 0 May07 ?        00:04:29 ora_lgwr_CKDB

oracle    2970     1 0 May07 ?        00:10:31 ora_ckpt_CKDB

oracle    2972     1 0 May07 ?        00:03:45 ora_smon_CKDB

oracle    2974     1 0 May07 ?        00:00:00 ora_reco_CKDB

oracle    2976     1 0 May07 ?        00:01:24 ora_cjq0_CKDB

oracle    2978     1 0 May07 ?        00:06:17 ora_mmon_CKDB

oracle    2980     1 0 May07 ?        00:07:26 ora_mmnl_CKDB

oracle    2982     1 0 May07 ?        00:00:00 ora_d000_CKDB

oracle    2984     1 0 May07 ?        00:00:00 ora_s000_CKDB

oracle    2994     1 0 May07 ?        00:00:28 ora_arc0_CKDB

oracle    2996     1 0 May07 ?        00:00:29 ora_arc1_CKDB

oracle    3000     1 0 May07 ?        00:00:00 ora_qmnc_CKDB

oracle    3625     1 0 May07 ?        00:01:40 ora_q000_CKDB

oracle   31594     1 0 Jul20 ?        00:00:00 ora_q003_CKDB

oracle   23802     1 0 05:09 ?        00:00:33 ora_j000_CKDB

19

在检查Oracle的进程命令输出后，输出显示至少应包括以下一些进程：
. Oracle写数据文件的进程，输出显示为：“ora_dbw0_CKDB”
. Oracle写日志文件的进程，输出显示为：“ora_lgwr_ CKDB”
. Oracle监听实例状态的进程，输出显示为：“ora_smon_ CKDB”
. Oracle监听客户端连接进程状态的进程，输出显示为：“ora_pmon_ CKDB”
. Oracle进行归档的进程，输出显示为：“ora_arc0_ CKDB”
. Oracle进行检查点的进程，输出显示为：“ora_ckpt_ CKDB”
. Oracle进行恢复的进程，输出显示为：“ora_reco_ CKDB”

1.2. 检查Oracle监听状态

/home/oracle>lsnrctl status

LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 23-JUL-2009 14:11:53

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.2.0 - Production

Start Date                07-MAY-2009 09:35:52

Uptime                    77 days 4 hr. 36 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File
  /data/oracle/product/10.2.0/network/admin/listener.ora

Listener Log File         /data/oracle/product/10.2.0/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AS14)(PORT=1521)))

Services Summary...

Service "CKDB" has 1 instance(s).

  Instance "CKDB", status READY, has 1 handler(s) for this service...

Service "CKDBXDB" has 1 instance(s).

  Instance "CKDB", status READY, has 1 handler(s) for this service...

Service "CKDB_XPT" has 1 instance(s).

  Instance "CKDB", status READY, has 1 handler(s) for this service...

The command completed successfully

“Services Summary”项表示Oracle的监听进程正在监听哪些数据库实例，输出显示中至少应该有“CKDB”这一项。

检查监听进程是否存在：

[oracle@AS14 ~]$ ps -ef|grep lsn|grep -v grep

oracle    2954     1 0 May07 ?        00:01:17
/data/oracle/product/10.2.0/bin/tnslsnr LISTENER –inherit

热门工具换一换