Oracle运维手册

20070718李玲斌

【修订稿】

版本:V1.00S.200718




















Linux 环境下Oracle运维手册(一)_Linux 环境下Oracle运维手册(

恒生电子证券事业部

○○七年七月




本文所述内容(包括文字和图片),恒生电子股份有限公司(以下简称“恒生”或“恒生公司”)拥有完全独立的唯一版权。未经恒生公司书面同意或授权,任何单位和个人都不得将其复制、影印或引用。

文档修改记录

版本号

日期

说明

编写者

审核者

V0.9

20070718

文档创建

李玲斌


V1.0

20070719

文档规范内容细化

李军





























目录


文档修改记录... 2

1.前言:... 5

2.简单命令使用... 5

2.1进入SQL*Plus. 5

2.2退出SQL*Plus. 5

2.3sqlplus下得到帮助信息... 6

2.4显示表结构命令DESCRIBE. 6

2.5SQL*Plus中的编辑命令... 6

2.6调用外部系统编辑器... 6

2.7运行命令文件... 7

2.8关于侦听... 7

3.ORACLE的启动和关闭... 8

3.1在单机环境下... 8

3.2在双机环境下... 9

4.数据库管理员日常工作... 9

4.1检查alterSID.log. 9

4.2环境确认... 10

5.数据库日常操作SQL. 11

5.1查看表空间物理文件的名称及大小... 11

5.2查询表空间使用情况... 11

5.3查询表空间的碎片程度... 11

5.4碎片程度... 12

5.5查看回滚段名称及大小... 12

5.6查看控制文件... 13

5.7查看日志文件... 13

5.8查看表空间的使用情况... 13

5.9查看数据库对象... 13

5.10查看数据库的版本... 14

5.11查看Oracle字符集... 14

5.12在某个用户下找所有的索引... 14

5.13表、索引的存储情况检查... 14

5.14查看数据库的创建日期和归档方式... 15

5.15显示所有数据库对象的类别和大小... 15

5.16设置RAC为归档模式?... 15

6.AWR报告... 16

7.Troubleshooting. 16

7.1监控事务的等待... 16

7.2查看一些等待信息:... 16

7.3查看等待(wait)情况... 17

7.4回滚段查看... 17

7.5回滚段的争用情况... 18

7.6监控表空间的I/O 比例... 18

7.7监控文件系统的I/O 比例... 18

7.8监控 SGA 的命中率... 18

7.9监控 SGA 中字典缓冲区的命中率... 19

7.10监控 SGA 中共享缓存区的命中率,应该小于1%. 19

7.11临控 SGA 中重做日志缓存区的命中率,应该小于1%. 19

7.12监控内存和硬盘的排序比率,最好使它小于 0.10,增加 sort_area_size. 20

7.13监控当前数据库谁在运行什么SQL语句... 20

7.14监控字典缓冲区... 20

7.15查看Lock. 20

7.16捕捉运行很久的SQL. 22

7.17查看数据表的参数信息... 22

7.18查看还没提交的事务... 23

7.19查找object为哪些进程所用... 23

7.20查看catchedobject. 23

7.21查看V$SQLAREA. 24

7.22有关connection的相关信息... 24

8.备份... 26

8.1数据逻辑备份... 26

8.1.1exp. 26

8.1.2imp. 27

8.2控制文件备份... 28

8.3初始参数备份... 28

8.4其它... 28

9.常见问题解决... 28

9.1安装后常用参数设置... 28

9.2杀死僵死连接... 29

9.3AIX内存溢出... 30

9.4某一功能特别慢... 32

9.5统计信息失效导致执行计划走全表扫瞄... 32

9.6中银... 32

9.7日志文件太小引起的切换过于频繁... 35

9.8Oracle连接中断问题... 37

9.9查询委托返回记录不对... 38

9.10Linux + Oracle 10g RAC的平台上,发生节点重启故障... 39

9.11联机日志损坏... 41

9.12控制文件损坏... 41

9.13ORA-01555错误... 41

9.14HP Proliant DL585G2 机器安装... 42



















1.前言:

有一定Linux/Unix操作系统、Oracle数据库基础的工程人员和维护人员,证券公司信息技术人员等。本手册可作为工具,Oralce运维提供帮助。

2.简单命令使用

2.1进入SQL*Plus

$sqlplus 用户名/密码

2.2退出SQL*Plus

SQL>exit


2.3sqlplus下得到帮助信息

列出全部SQL命令和SQL*Plus命令

SQL>help

列出某个特定的命令的信息

SQL>help 命令名


2.4显示表结构命令DESCRIBE

SQL>DESC 表名


2.5SQL*Plus中的编辑命令

显示SQL缓冲区命令

SQL>L

修改SQL命令

首先要将待改正行变为当前行

SQL>n

CHANGE命令修改内容

SQL>c//

重新确认是否已正确

SQL>L

使用INPUT命令可以在SQL缓冲区中增加一行或多行

SQL>i

SQL>输入内容


2.6调用外部系统编辑器

SQL>edit 文件名

可以使用DEFINE命令设置系统变量EDITOR来改变文本编辑器的类型,在login.sql文件中定义如下一行

DEFINE_EDITOR=vi


2.7运行命令文件

SQL>START test

SQL>@test

2.8关于侦听

1、新建/修改/删除侦听

以oracle用户登录,运行netca,会跳出图形配置界面。



2、打开侦听

    lsnrctl start

3、查看侦听

    Lsnrctl status

4、关闭侦听

    lsnrctl stop


3.ORACLE的启动和关闭

3.1在单机环境下

要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下

su - oracle


启动oracle 数据库命令:

$sqlplus/nolog


SQL*Plus: Release 10.2.0.1.0- Production on 星期一 7 16 16:09:40 2007


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


SQL>conn / as sysdba

已连接到空闲例程

SQL>startup

ORACLE 例程已经启动


Total System Global Area  369098752 bytes

Fixed Size                  1249080 bytes

Variable Size             201326792 bytes

Database Buffers          159383552 bytes

Redo Buffers                7139328 bytes

数据库装载完毕。

数据库已经打开。


关闭 oracle 数据库命令:

$ sqlplus/nolog


SQL*Plus: Release 10.2.0.1.0- Production on 星期一 7 16 16:08:10 2007

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

SQL>conn / as sysdba

已连接。

SQL>shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL>


3.2在双机环境下

要想启动或关闭crs服务必须首先切换到root用户,如下

su - root

启动crs 服务:

启动CRS

#$CRS_HOME/crs/bin/crsctlstart crs

查看CRS状态

#$CRS_HOME/crs/bin/crsctlcheck crs

关闭CRS

#$CRS_HOME/crs/bin/crsctlstop crs

查看CRS内部各资源状态

#$CRS_HOME/crs/bin/crs_stat–t


启动数据库服务

# srvctlstart database -d tdb             #tdb为数据库名

4.数据库管理员日常工作

4.1检查alterSID.log

这个日志文件位于参数BACKGROUND_DUMP_DEST指定的目录,可能通过以下命令来查看。

SQL> SHOW PARAMETER background_dump_dest

在出现大故障前,数据库有可能会报一些警告或错误信息,应该充分重视这些信息,未雨绸缪,避免更大错误的发生。

检查alterSID.log 的什么内容。

检查数据库是否出现过宕机(可能在晚间重启而维护人员不知道)

Oracle 出错信息,通过$grep ORA- alterSID.log查找

产品有关的问题:ORA-00600/ORA-07445等错误

相应的TRACE文件

4.2环境确认

数据库实例是否正常工作

SQL >select status from v$instance;

数据库监听器是否正常工作

-$ lsnrctl status

是否存在故障表空间

-SQL> select tablespace_name,status fromdba_tablespace;

控制文件、日志文件是否正常

SQL>select * from v$controlfile;

SQL>select * from v$log;

SQL>select * from v$logfile;

性能监测

每天按业务峰值情况,对数据库性能数据进行定时采集

每天检查数据库的主要性能指标

每天检查最消耗资源的SQL语句变化情况。

每天检查是否有足够的资源

检查所有表空间的剩余情况

识别出一些异常的增长

检查CPU、内存、网络等是否异常

5.数据库日常操作SQL

5.1查看表空间物理文件的名称及大小

select tablespace_name,
      file_id,
      file_name,
      round(bytes / (
1024 * 1024), 0) filesize
from dba_data_files
orderby tablespace_name;

5.2查询表空间使用情况

select a.tablespace_name "表空间名称",
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc)* 100, 2) "占用率(%)",
      round(a.bytes_alloc /
1024 / 1024, 2) "容量(M)",
      round(nvl(b.bytes_free,
0) / 1024 / 1024, 2) "空闲(M)",
      round((a.bytes_alloc -nvl(b.bytes_free,
0)) / 1024 / 1024, 2) "使用(M)",
      to_char(sysdate,
'yyyy-mm-ddhh24:mi:ss')"采样时间"
from (selectf.tablespace_name,
sum(f.bytes)bytes_alloc,
sum(decode(f.autoextensible,
'YES', f.maxbytes, 'NO', f.bytes))maxbytes
from dba_data_files f
groupbytablespace_name) a,
      (select f.tablespace_name, sum(f.bytes)bytes_free
from dba_free_space f
groupbytablespace_name) b
where a.tablespace_name =b.tablespace_name
orderby
2desc;

5.3查询表空间的碎片程度

select tablespace_name, count(tablespace_name)
from dba_free_space
groupby tablespace_name
havingcount(tablespace_name) >
10;

altertablespace HS_USER_DATA coalesce;
altertablenamedeallocateunused;

5.4碎片程度

select tablespace_name, count(tablespace_name)
from dba_free_space
groupby tablespace_name
havingcount(tablespace_name) >
10;
altertablespacenamecoalesce;
altertablenamedeallocateunused;
createorreplaceview ts_blocks_v as
select tablespace_name,block_id, bytes, blocks, segment_name
from dba_free_space
unionall
select tablespace_name,block_id, bytes, blocks, segment_name
from dba_extents;
select * from ts_blocks_v;
select tablespace_name, sum(bytes), max(bytes), count(block_id)
from dba_free_space
groupby tablespace_name;

查看碎片程度高的表

SELECT segment_name table_name, COUNT(*) extents
FROM dba_segments
WHERE owner NOTIN(
'SYS', 'SYSTEM')
GROUPBY segment_name
HAVINGCOUNT(*) = (SELECTMAX(COUNT(*))
FROMdba_segments
GROUPBY segment_name);


5.5查看回滚段名称及大小

select segment_name,
      tablespace_name,
      r.status,
      (initial_extent /
1024) InitialExtent,
      (next_extent /
1024) NextExtent,
      max_extents,
      v.curext CurExtent
From dba_rollback_segs r,v$rollstat v
Where r.segment_id = v.usn(+)
orderby segment_name;


5.6查看控制文件

selectnamefromv$controlfile;

5.7查看日志文件

selectmemberfromv$logfile;

5.8查看表空间的使用情况

selectsum(bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
groupby tablespace_name;

SELECT A.TABLESPACE_NAME,
      A.BYTES TOTAL,
      B.BYTES USED,
      C.BYTES FREE,
      (B.BYTES *
100) / A.BYTES "% USED",
      (C.BYTES *
100) / A.BYTES "% FREE"
FROMSYS.SM$TS_AVAIL A, SYS.SM$TS_USEDB, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME =B.TABLESPACE_NAME
AND A.TABLESPACE_NAME =C.TABLESPACE_NAME;


5.9查看数据库对象

select owner, object_type, status, count(*)count#
from all_objects
groupby owner,object_type, status;

5.10查看数据库的版本

Select version
FROM Product_component_version
Where SUBSTR(PRODUCT,
1, 6) = 'Oracle';

5.11查看Oracle字符集

select * fromsys.props$ wherename= 'NLS_CHARACTERSET';

5.12在某个用户下找所有的索引

select user_indexes.table_name,
      user_indexes.index_name,
      uniqueness,
      column_name
from user_ind_columns,user_indexes
where user_ind_columns.index_name= user_indexes.index_name
and user_ind_columns.table_name= user_indexes.table_name
orderbyuser_indexes.table_type,
         user_indexes.table_name,
         user_indexes.index_name,
         column_position;

5.13表、索引的存储情况检查

select segment_name, sum(bytes), count(*)ext_quan
from dba_extents
where tablespace_name =
'&tablespace_name'
and segment_type =
'TABLE'
groupby tablespace_name,segment_name;
select segment_name, count(*)
from dba_extents
where segment_type =
'INDEX'
and owner =
'&owner'
groupby segment_name;


5.14查看数据库的创建日期和归档方式

Select Created, Log_Mode, Log_Mode FromV$Database;


5.15显示所有数据库对象的类别和大小

selecttype,
count(name)num_instances,
sum(source_size)source_size,
sum(parsed_size)parsed_size,
sum(code_size) code_size,
sum(error_size) error_size,
sum(source_size) + sum(parsed_size)+ sum(code_size) +
sum(error_size)size_required
from dba_object_size
groupbytype
orderby
1;


5.16设置RAC为归档模式?

步骤:

1.SYSDBA身份登陆2个节点,执行

alter system set cluster_database=false scope =spfile sid=’*’;

设置归档路径

alter system set log_archive_start=true scope=spfile;

2.2个节点

shutdown immediate

3.在一个节点上执行

startup mount

alter database archivelog;

shutdown immediate;

alter database open;

alter system set cluster_database=true scope =spfile sid=’*’;

shutdown immediate

4、分别启动2个节点,修改完毕


6.AWR报告

9i 中的statspack相似,awr报告也需要两个快照,才能生成这两个时间点之间的性能报告。

$sqlplus/ as sysdba

生成快照一(10g中自动会每个整点都会生成一个快照)

SQL>exec dbms_workload_repository.create_snapshot();

(间隔一段时间)生成快照二

SQL>exec dbms_workload_repository.create_snapshot();

生成报告

SQL>@?/rdbms/admin/awrrpt.sql


7.Troubleshooting

常用性能相关SQL,监控数据库性能的SQL语句。

7.1监控事务的等待

select event,
sum(decode(wait_Time,
0, 0, 1)) "Prev",
sum(decode(wait_Time,
0, 1, 0)) "Curr",
count(*) "Totol"
from v$session_Wait
groupby event
orderby
4;

7.2查看一些等待信息:

select sid,event
from v$session_wait
where event notlike
'SQL%'
and event notlike
'%ipc%';


查看是否存在下面等常见的等待事件

buffer busy waits,

free buffer waits,

db file sequentialread,

db file scatteredread,

enqueue,latch free,

log file parallelwrite,

log file sync

7.3查看等待(wait)情况

SELECT v$waitstat.class,
      v$waitstat.countcount,
SUM(v$sysstat.value)sum_value
FROM v$waitstat, v$sysstat
WHERE v$sysstat.nameIN(
'dbblock gets', 'consistent gets')
groupby v$waitstat.class,v$waitstat.count;

7.4回滚段查看

selectrownum,
sys.dba_rollback_segs.segment_nameName,
      v$rollstat.extentsExtents,
      v$rollstat.rssize Size_in_Bytes,
      v$rollstat.xacts XActs,
      v$rollstat.gets Gets,
      v$rollstat.waits Waits,
      v$rollstat.writes Writes,
sys.dba_rollback_segs.statusstatus
from v$rollstat, sys.dba_rollback_segs,v$rollname
where v$rollname.name(+) =sys.dba_rollback_segs.segment_name
and v$rollstat.usn(+) =v$rollname.usn
orderbyrownum;

7.5回滚段的争用情况

selectname, waits, gets, waits / gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;

7.6监控表空间的I/O 比例

select df.tablespace_name name,
      df.file_name       "file",
      f.phyrds           pyr,
      f.phyblkrd         pbr,
      f.phywrts          pyw,
      f.phyblkwrt        pbw
from v$filestat f,dba_data_files df
where f.file# = df.file_id
orderbydf.tablespace_name;

7.7监控文件系统的I/O 比例

select substr(a.file#, 1, 2) "#",
      substr(a.name,
1, 30) "Name",
      a.status,
      a.bytes,
      b.phyrds,
      b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;

7.8监控 SGA 的命中率

select a.value + b.value"logical_reads",
      c.value"phys_reads",
      round(
100 * ((a.value + b.value) - c.value) /(a.value + b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b,v$sysstat c
where a.statistic# =
38 --physical read total multi block requests
and b.statistic# =
39 --physical read total bytes
and c.statistic# =
40; --physical write total IO requests

7.9监控 SGA 中字典缓冲区的命中率

select parameter,
      gets,
      Getmisses,
      getmisses / (gets + getmisses) *
100 "miss ratio",
      (
1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) *100 "Hit ratio"
from v$rowcache
where gets + getmisses <>
0
groupby parameter, gets,getmisses;

7.10监控 SGA 中共享缓存区的命中率,应该小于1%

selectsum(pins) "Total Pins",
sum(reloads) "TotalReloads",
sum(reloads) / sum(pins)libcache
from v$librarycache;
selectsum(pinhits - reloads) / sum(pins) * 100 "hitradio",
sum(reloads) / sum(pins)"reload percent"
from v$librarycache;

7.11临控 SGA 中重做日志缓存区的命中率,应该小于1%

SELECTname,
      gets,
      misses,
      immediate_gets,
      immediate_misses,
      Decode(gets,
0, 0, misses / gets * 100) ratio1,
      Decode(immediate_gets +immediate_misses,
0,
0,
             immediate_misses /(immediate_gets + immediate_misses) *
100) ratio2
FROM v$latch
WHEREnameIN (
'redo allocation', 'redo copy');

7.12监控内存和硬盘的排序比率,最好使它小于 0.10,增加 sort_area_size

SELECTname, value
FROM v$sysstat
WHEREnameIN (
'sorts (memory)', 'sorts (disk)');

7.13监控当前数据库谁在运行什么SQL语句

SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address = b.address
orderby address, piece;

7.14监控字典缓冲区

SELECT SUM(PINS) "EXECUTIONS",
      SUM(RELOADS) "CACHE MISSESWHILE EXECUTING",
      (SUM(PINS - RELOADS)) / SUM(PINS)"LIB CACHE"
 FROM V$LIBRARYCACHE;

SELECT SUM(GETS) "DICTIONARY GETS",
      SUM(GETMISSES) "DICTIONARYCACHE GET MISSES",
      (SUM(GETS - GETMISSES - USAGE -FIXED)) / SUM(GETS) "ROW CACHE"
 FROM V$ROWCACHE;

LIB CACHE“与“ROW CACHE”越接近1.00超好,不要低于0.90。否则需要调大SGA的空间。

7.15查看Lock

select s.osuser,
      l.sid,
      s.serial#,
      s.username,
      s.terminal,
      decode(l.type,
             'TM',
             'TM - DML Enqueue',
             'TX',

             'TX - Trans Enqueue',
             'UL',
             'UL - User',
             l.type || ' - Other Type')LOCKTYPE,
      substr(t.name, 1, 10) OBJECT,
      u.name owner,
      l.id1,
      l.id2,
      decode(l.lmode,
             1,
             'No Lock',
             2,
             'Row Share',
             3,

             'Row Exclusive',
             4,
             'Share',
             5,
             'Shr Row Excl',
             6,
             'Exclusive',
             null) lmode,
      decode(l.request,
             1,
             'No Lock',
             2,
             'Row Share',

             3,
             'Row Excl',
             4,
             'Share',
             5,
             'Shr Row Excl',
             6,
             'Exclusive',
             null) request
 from v$lock l, v$session s, sys.user$u, sys.obj$ t
where l.sid = s.sid
  and s.type != 'BACKGROUND'
  and t.obj# = l.id1
  and u.user# = t.owner#;

7.16捕捉运行很久的SQL

select username,
      sid,
      opname,
      round(sofar *
100 / totalwork, 0) || '%'asprogress,
      time_remaining,
      sql_text
from v$session_longops, v$sql
where time_remaining <>
0
and sql_address = address
and sql_hash_value =hash_value;

7.17查看数据表的参数信息

SELECT partition_name,
      table_name,
      high_value,
      high_value_length,
      tablespace_name,
      pct_free,
      pct_used,
      ini_trans,
      max_trans,
      initial_extent,
      next_extent,
      min_extent,
      max_extent,
      pct_increase,
FREELISTS,
      freelist_groups,
LOGGING,
BUFFER_POOL,
      num_rows,
      blocks,
      empty_blocks,
      avg_space,
      chain_cnt,
      avg_row_len,
      sample_size,
      last_analyzed
FROMsys.dba_tab_partitions
WHERE table_owner =
'HS_HIS'ANDtable_name = 'HISBANKTRADE'
--WHEREtable_name = :tname AND table_owner = :towner
ORDERBYpartition_position,partition_name;

7.18查看还没提交的事务

select * from v$locked_object;
select * from v$transaction;

7.19查找object为哪些进程所用

select p.spid,
      s.sid,
      s.serial# serial_num,
      s.username user_name,
      a.type object_type,
      s.osuser os_user_name,
      a.owner,
      a.object object_name,
      decode(sign(
48 - command),
1,
             to_char(command),
'Action Code #' ||to_char(command)) action,
      p.program oracle_process,
      s.terminal terminal,
      s.program program,
      s.status session_status
from v$session s, v$access a,v$process p
where s.paddr = p.addr
and s.type =
'USER'
and a.sid = s.sid
and a.object =
'FUNDREAL'
orderby s.username,s.osuser;

7.20查看catched object

SELECT owner,
name,
      db_link,
      namespace,
      type,
      sharable_mem,
      loads,
      executions,
      locks,
      pins,
      kept
FROM v$db_object_cache whereowner LIKE
'HS_%';

7.21查看V$SQLAREA

SELECT SQL_TEXT,
      SHARABLE_MEM,
      PERSISTENT_MEM,
      RUNTIME_MEM,
      SORTS,
      VERSION_COUNT,
      LOADED_VERSIONS,
      OPEN_VERSIONS,
      USERS_OPENING,
      EXECUTIONS,
      USERS_EXECUTING,
      LOADS,
      FIRST_LOAD_TIME,
      INVALIDATIONS,
      PARSE_CALLS,
      DISK_READS,
      BUFFER_GETS,
      ROWS_PROCESSED
FROM V$SQLAREA
;


7.22有关connection的相关信息

l查看有哪些用户连接

select s.sid,
      s.serial# serial_num,
      s.osuser os_user_name,
      decode(sign(
48 - command),
1,
             to_char(command),
'Action Code #' ||to_char(command)) action,
      p.program oracle_process,
      status session_status,
      s.terminal terminal,
      s.program program,
      s.username user_name,
      s.fixed_table_sequenceactivity_meter
  from v$session s, v$process p
where s.paddr = p.addr
and s.type =
'USER'orderbys.username, s.osuser;


l2)根据v.sid查看对应连接的资源占用等情况

select n.name, v.value, n.class,n.statistic#
from v$statname n, v$sesstat v
where v.sid =
&sid
and v.statistic# = n.statistic#
orderby n.class,n.statistic#;


l3)根据sid查看对应连接正在运行的sql

select/*+ PUSH_SUBQ */
command_type,
sql_text,
sharable_mem,
persistent_mem,
runtime_mem,
sorts,
version_count,
loaded_versions,
open_versions,
users_opening,
executions,
users_executing,
loads,
first_load_time,
invalidations,
parse_calls,
disk_reads,
buffer_gets,
rows_processed,
sysdate start_time,
sysdate finish_time,
'>' || address sql_address,
'N' status
from v$sqlarea
where address = (selectsql_address from v$session where sid = &sid)
;