linux中oracle的日常维护命令(转载,方便平时使用时随时来查看)

0.  检查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”


1.Oracle的开启和关闭
    1)连接sqlplus
sqlplus /nolog
    2)登陆数据库
conn /as sysdba
connect sys/123456 as sysdba;
    3)启动数据库
startup
  startup命令它有三种情况:
  第一种:不带参数,启动数据库实例并打开数据库,以便用户使用数据库,在多数情况下,使用这种方式!
  第二种:带nomount参数,只启动数据库实例,但不打开数据库,在你希望创建一个新的数据库时使用,或者      在你需要这样的时候使用!
  第三种:带mount参数,在进行数据库更名的时候采用。这个时候数据库就打开并可以使用了!
    4)关闭数据库
shutdown immediate
  shutdown有四个参数:
  normal --关闭,等待每个用户退出系统戓被取消后退出关闭数据库。
  transactional --事务性关闭,等待每个用户提交戓回退当前的事务,然后oracle取消对话,在所有用户退出    系统后执行关闭。
  immediate --直接关闭,取消所有用户对话(促使回退),执行正常的关闭程序。
  abort --终止关闭,关闭数据库时没有自动检查点戓日志开关。
    5)查看当前用户
show user
    6)退出sqlplus
quit
    7)启动监听器
lsnrctl start
    8)停止监听器
lsnrctl stop
    9)查看监听器状态
lsnrctl status
  
  
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;
  
SQL> Select  A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
  2  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;
3
  
TABLESPACE_NAME                USED_PERCENT
------------------------------         ------------
UNDOTBS1                              33.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
  3      from sys.DBA_segments
  4      where max_extents!=0 and (extents/max_extents)*100>=95
  5  order 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
  2  where tablespace_name=‘SYSTEM‘ and
  3  owner!=‘SYS‘ and owner!=‘SYSTEM‘
  4  union
  5  select distinct(owner) from dba_indexes
  6  where tablespace_name=‘SYSTEM‘ and
  7  owner!=‘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
  2      from all_tables a,
  3      (select tablespace_name,max(bytes) as big_chunk
  4      from dba_free_space group by tablespace_name) f
  5      where f.tablespace_name=a.tablespace_name
  6      and a.next_extent>f.big_chunk
  7      union
  8      select a.index_name,a.next_extent,a.tablespace_name
  9      from all_indexes a,
10      (select tablespace_name,max(bytes) as big_chunk
11      from dba_free_space
12      group by tablespace_name) f
13      where f.tablespace_name=a.tablespace_name
14      and a.next_extent>f.big_chunk;
  
no rows selected
  
  如果有记录返回,则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值,需调整相应表空间的存储参数

  

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。