Oracle数据库常见维护问题手册

1、目录1数据库基础71.1数据库启动方式71.2数据库关闭方式81.3数据库常用的系统表81.4连接数据库的常用命令格式81.5数据库主要配置文件92数据库日常维护操作132.1查询表空间信息132.2查询数据库的连接情况152.3查询数据库数据文件情况152.4查询数据文件的io情况162.5查询redo log buffer的繁忙程度172.6查看等待事件182.7判断undo表空间的使用情况192.8查询数据库的字符集202.9查询sql执行计划202.10如何执行数据库脚本212.11如何drop offline数据文件212.12如何修改dbfiles213数据库表空间扩容及迁移233

2、.1向表空间增加一个不自动扩展的数据文件233.2创建一个表空间，增加一个自动扩展到最大值的数据文件233.3数据表空间文件迁移233.4redo日志迁移253.5修改redo日志的大小263.6临时表空间迁移273.7控制文件迁移283.8重建及切换undo表空间294数据库常用备份方法304.1逻辑备份导入导出的常用命令304.2数据泵导入导出备份常用命令314.3RMAN增量备份步骤325数据库常见问题排查345.1如何初步判断数据库有问题345.2如何获取排查数据库问题所需的日志355.3oracle无法启动355.4如何修改oracle系统参数375.5如何解决数据库用户被锁定问题3

3、7 1 数据库基础1.1 数据库启动方式方式含义startup启动实例、装载数据库、打开数据库startup nomount启动实例，不加载数据库startup mount启动实例，加载数据库但不打开数据库startup restrict启动过程中限制访问数据库startup force迫使数据库启动startup pfile=/oracle/app/oracle/product/10g/dbs/initminos.ora使用非缺省参数文件启动数据库，以特定文件中指定参数启动数据库,本例为”/oracle/app/oracle/product/10g/dbs/initminos.ora注：启动

4、前需要先使用sqlplus登录到数据库中 $ sqlplus sys/oracle as sysdbaSQL*Plus: Release .0 - Production on Mon May 28 03:39:35 2012Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to an idle instance.SQL startupORACLE instance started.Total System Global Area bytesFixed Size bytesVariable Size b

5、ytesDatabase Buffers bytesRedo Buffers bytesDatabase mounted.Database opened.SQL1.2 数据库关闭方式方式含义shutdown normal不允许新的连接；等待当前的连接释放shutdown immediately不允许新的连接；当前的事务回滚；不等待当前的连接释放shutdown abort不允许新的连接；立即中止当前事务中的SQL当前事务不回滚；下次启动时有实例的恢复过程shutdown transactional不允许新的连接；等待当前事务完成；事务完成后断开当前的连接 $ sqlplus sys/oracl

6、e as sysdbaSQL*Plus: Release .0 - Production on Mon May 28 03:39:35 2012Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected SQL shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL1.3 数据库常用的系统表1.4 连接数据库的常用命令格式a) sqlplus / as sysdba 这是典型的操作系统认证，不需要li

7、stener进程，数据库即使不可用也可以登录b) sqlplus username/password 不需要listener进程，登录本机数据库，数据库实例启动即可c) sqlplus username/passwordtnsname需要listener进程，最常见的远程登录模式，需要启动数据库实例和listener进程在多实例环境中，a)和b ) 两种连接方式默认连接的实例为当前环境变量中SID值定义的实例，如果需要连接另外的实例，请更改环境变量中SID值后进行连接$ env | grep SID ORACLE_SID=minos$ export ORACLE_SID=rct$ env |

8、grep SID ORACLE_SID=rct1.5 数据库主要配置文件 环境变量文件在aix系统中，环境变量文件一般存放在 /etc/profile ,/etc/environment, $HOME/.profile 这三个文件中三个环境变量的定义如下： /etc/environment -操作系统在登录时使用的第一个文件，/etc / environment文件中包含的变量指定的所有进程的基本环境。 /etc/profile 操作系统在登录时使用的第二文件，这个文件中包含了所有登录用户指定的基本环境 $HOME/.profile -操作系统在登录时最后使用的文件，这个文件中存在放在属主目录

9、中，定义了当前登录用户的工作环境系统级的环境变量一般在/etc/environment 文件中定义，与数据库相关的环境变量就定义在/etc/profile 文件中,如下所示 export ORACLE_BASE=/oracle/app/oracle export ORACLE_HOME=/oracle/app/oracle/product/10g/export ORACLE_SID=minos对于solaris系统而言，环境变量文件一般存放在/etc/profile , $HOME/.profile这两个文件中。故修改时只需修改/etc/profile 文件即可 /etc/oratab 文件/

10、etc/oratab 文件描述目前系统中创建的数据库实例 以及是否通过 dbstart 和 dbshut 来控制该实例的启动与关闭,如下所示: minos:/oracle/app/oracle/product/10g:Y其中 minos 为实例 ID/oracle/app/oracle/product/10g为 ORACLE_HOME目录Y表示允许使用 dbstart和 dbshut 启动和关闭该实例数据库,如果设置为N 表示不通过 dbstart 和 dbshut 启动和关闭实例数据库 数据库实例初始化文件 initSID.ora每个数据库实例都有一个初始化参数文件,其缺省存放的路径为$OR

11、ACLE_BASE/dbs ,其名称为 initSID.ora如 minos实例对应的参数文件为 initminos.ora,缺省存放路径为/oracle/app/oracle/product/10g/dbs/initminos.ora tnsnames.ora文件此文件类似于unix 中的hosts文件，提供tnsname到主机名或者ip的对应。几个重要参数:PROTOCOL：客户端与服务器端通讯的协议，一般为TCP，该内容一般不用改。HOST：数据库侦听所在的机器的机器名或IP地址PORT：数据库侦听正在侦听的端口，系统缺省值为1521。SERVICE_NAME：数据库实例的服务名对于外场

12、环境而言，tnsnames.ora文件一般存放在$ORACLE_HOME/network/admin/tnsnames.ora以下为一个完整的tnsnames.ora文件#tnsnames.ora Network Configuration File: /oracle/app/oracle/product/10g/network/admin/tnsnames.ora# Generated by Oracle configuration tools.MINOS_16 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mi

13、nos1)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = minos) ) )RCT_16 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = minos1)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rct) ) )RCT = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = minos1)(PO

14、RT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rct) ) )MINOS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = minos1)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = minos) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(H

15、OST = 16)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = PLSExtProc) ) ) listener.ora文件listener作为监听器进程的配置文件，接受远程对数据库的接入申请并转交给oracle的服务器进程。几个重要参数:SID_NAME:数据库实例名ORACLE_HOME:数据库目录PROTOCOL: 客户端与服务器端通讯的协议，一般为TCP，该内容一般不用改。HOST: 数据库侦听所在的机器的机器名或IP地址PORT：数据库侦听正在侦听的端口，系统缺省值为1521。对于外场环境而言，listener.

16、ora文件一般存放在$ORACLE_HOME/network/admin/ listener.ora以下为一个完整的listener.ora文件# listener.ora Network Configuration File: /oracle/app/oracle/product/10g/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /or

17、acle/app/oracle/product/10g/) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = minos) (ORACLE_HOME = /oracle/app/oracle/product/10g/) ) (SID_DESC = (SID_NAME = rct) (ORACLE_HOME = /oracle/app/oracle/product/10g) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = minos1)

18、(PORT = 1521) ) )2 数据库日常维护操作以下操作语句执行前需要使用sqlplus或者PLSQL Developer客户端登录数据库进行操作。 Sqlplus登录方式如下: PLSQL Developer登录方式如下:12.1 查询表空间信息 2.2 查询数据库的连接情况SELECT sid,serial#,username,program,machine,status FROM v$session;2.3 查询数据库数据文件情况SELECT file_name,status FROM dba_data_files;Available 代表数据库文件均为可用状态2.4 查询数据文

19、件的io情况查询表空间读写情况SELECT name,phyrds,phywrts FROM V$datafile df,V$filestat fsWHERE df.file# =fs.file#; 其中PHYRDS 代表已完成的物理读次数，PHYWRTS代表DBWR完成的物理写次数 数据库的IO负载系统的瓶颈在于磁盘IO，所以一般可以通过操作系统的一些命令来确认一个系统是否是存在IO负载问题，比如iostat，sar 等重点关注的几个指标就是idle值和iowait，但是idle值非常小(小于20%)或者iowait值非常大 (大于70%)时，说明IO出现问题。由于IO问题涉及操作系统，存储

20、系统，cp负荷及应用系统等一些因素，故当发现问题请及时联系硬件厂家进行分析处理2.5 查询redo log buffer的繁忙程度SELECT retries.value/entries.value Redo Log Buffer Retry RatioFROM V$sysstat retries, V$sysstat entriesWhere = redo buffer allocation retriesAnd = redo entries;redo buffer allocation retries事件越少越好，该语句查询结果应该接近0

21、或少于1%，否则要增加redo buffer2.6 查看等待事件Select .username,gram,s.status,se.event,se.total_waits,se.total_timeouts,se.time_waited,se.average_wait from v$session s,v$session_event seWhere s.sid=se.sid And se.event not like SQl*Net% And s.status = ACTIVE And s.username is not null;select * from (select eve

22、nt wait event,time_waited time waited, round(time_waited /(select sum(time_waited) from v$system_event),4) %time waited, total_waits waits, round(total_waits /(select sum(total_waits) from v$system_event),4) %waited from v$system_event where wait_class!=Idle order by 2 desc) where rownum select sum(

23、bytes) ,status from dba_undo_extents group by status;一般来说外场环境都是创建一个undotbs,如果有多个undotbs的情况，请使用以下语句查询SQL select sum(bytes) ,status,tablespace_name from dba_undo_extents group by status,tablespace_name;每个undo extent可以有三种状态:active:有活动事务在此extent上expired:已结束的事务，undo 信息超过undo_retention时间限制unexpired:已经结束的事

24、务，undo 信息未达到undo_retention时间限制当一个事务开始它将会去寻找可用的undo block来存放undo信息，它将按照以下顺序请求undo space.先去搜索拥有非active extent的undo segment，如果没有发现，那么会去创建新的undo segment,如果空间不够不能创建，将返回错误。所以如果undo虽然表空间满，但是有很多非active的空间的话，应该不会出现事务失败的问题2.8 查询数据库的字符集使用system用户登录数据库，执行下面的sql语句查询数据库服务器的字符集：SQL select userenv(language) from du

25、al;AMERICAN_AMERICA.ZHS16GBK在客户端查询字符集$ echo $NLS_LANGAMERICAN_AMERICA.ZHS16GBK2.9 查询sql执行计划explain plan for select * from tab;select * from table(dbms_xplan.display)2.10 如何执行数据库脚本oracle用户登录服务器，将所需要执行的脚本放到服务器上例如: 执行/home/omc 目录下的test.sql 脚本$cd /home/omc$sqlplus /nologSQLconn system/oracleminos SQLtes

26、t.sql 2.11 如何drop offline数据文件先关闭数据库实例$sqlplus /nologSQLconn / as sysdbaSQLshutdown immediate启动数据库实例至mount状态 进行offline 操作SQLstartup mountSQLalter database datafile /datafile/u01/UEP_CAF_FM05.dbf OFFLINE DROP;SQLalter database open2.12 如何修改dbfiles连接数据库sqlplus /nologSQL conn sys/oracle as sysdba;修改参数 S

27、QL show parameter db_files; NAME TYPE VALUE - - - db_files integer 200 SQL alter system set db_files=1000 scope=spfile; System altered. 修改后关闭数据库SQL shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 修改完成后启动数据库SQL startup; ORACLE instance started. Total System Global

28、 Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes Database mounted. Database opened. SQLshow parameter db_files; NAME TYPE VALUE - - - db_files integer 1000 SQL exit 至此db_files参数修改工作完成 3 数据库表空间扩容及迁移23.1 向表空间增加一个不自动扩展的数据文件以sys用户登录数据库SQL conn sys/oracleminos as

29、 sysdba在一个表空间增加一个数据文件，文件路径和大小需要自己修改成适合的位置和大小。特别注意:路径和文件名的正确性,严格区分字母大小写SQL ALTER TABLESPACE UEP_CAF_FM ADD DATAFILE /datafile/u01/UEP_CAF_FM01.DBF SIZE 4096M REUSE AUTOEXTEND OFF;3.2 创建一个表空间，增加一个自动扩展到最大值的数据文件以sys用户登录数据库SQL conn sys/oracleminos as sysdba创建一个表空间SQL CREATE TABLESPACE TEST LOGGING DATAFI

30、LE /datafile/u01/TEST.dbf SIZE 5M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 300M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO ;增加一个数据文件，文件路径和大小需要自己修改成适合的位置和大小特别注意:路径和文件名的正确性,严格区分字母大小写SQL ALTER TABLESPACE TEST ADD DATAFILE /datafile/u01/test1.dbf SIZE 5M REUSE AUTOEXTENDON NEXT 5M

31、MAXSIZE 300M3.3 数据表空间文件迁移查询当前数据表空间文件位置并关闭数据库实例$ sqlplus /nologSQL connect sys/oracleminos as sysdbaSQL select file_name from dba_data_Files order by file_name;SQL shutdown immediate;SQL exit;移动数据库表空间文件的物理位置mv /datafile/u01/system01.dbf /datafile/u02/system01.dbf；mv /datafile/u01/undotbs01.dbf /dataf

32、ile/u02/undotbs01.dbf；mv /datafile/u01/sysaux01.dbf /datafile/u02/sysaux01.dbf；mv /datafile/u01/users01.dbf /datafile/u02/users01.dbf；mv /datafile/u01/uep.dbf /datafile/u02/uep.dbf；mv /datafile/u01/MINOS_RNS_PM_ORI03.dbf /datafile/u02/MINOS_RNS_PM_ORI03.dbf；& 提示1:system,undo,sysaus,user等系统表空间文件也可以作为

33、数据文件进行迁移启动数据库至mount状态SQL connect sys/oracle as sysdbaSQL startup mount;修改数据表空间文件在数据库中路径SQL alter database rename file /datafile/u01/system01.dbf to /datafile/u02/system01.dbf;SQL alter database rename file /datafile/u01/undotbs01.dbf to /datafile/u02/undotbs01.dbf;SQL alter database rename file /dat

34、afile/u01/sysaux01.dbf to /datafile/u02/sysaux01.dbf;SQL alter database rename file /datafile/u01/users01.dbf to /datafile/u02/users01.dbf;SQL alter database rename file /datafile/u01/uep.dbf to /datafile/u02/uep.dbf;SQL alter database rename file /datafile/u01/MINOS_RNS_PM_ORI03.dbf to /datafile/u0

35、2/MINOS_RNS_PM_ORI03.dbf;启动实例，如果实例正常打开无报错且使用语句查询表空间路径已修改则表示迁移成功SQL alter database open;SQL select file_name from dba_data_Files order by file_name;SQL commit;SQL quit;3.4 redo日志迁移查询当前数据表空间文件位置并关闭数据库实例$ sqlplus /nologSQL connect sys/oracleminos as sysdbaSQL select g.member, v.status from v$log v, v$l

36、ogfile g where v.GROUP#=g.GROUP#;SQL alter system switch logfile;SQL shutdown immediate;SQL exit;移动redo日志的物理位置mv /datafile/u01/redo01.log /datafile/u02/mv /datafile/u01/redo02.log /datafile/u02/mv /datafile/u01/redo03.log /datafile/u02/修改数据表空间文件在数据库中位置SQL connect sys/oracle as sysdbaSQL startup moun

37、t;SQL alter database rename file /datafile/u01/redo03.log to /datafile/u02/redo03.log;SQL alter database rename file /datafile/u01/redo02.log to /datafile/u02/redo02.log;SQL alter database rename file /datafile/u01/redo01.log to /datafile/u02/redo01.log;打开实例并切换日志SQL alter database open;SQL alter sys

38、tem switch logfile;验证迁移结果SQL select g.member, v.status from v$log v, v$logfile g where v.GROUP#=g.GROUP#;SQL commit;SQL quit;3.5 修改redo日志的大小确认redo文件名和路径$sqlplus /nologSQLconn system/oracleminosSQL select member from v$logfile;MEMBER-/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO03.LOG/ORACLE/PRODUCT/10.2.

39、0/ORADATA/ORCL/REDO01.LOG/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG添加新的日志文件组SQL alter database add logfile group 4 /ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO04.LOG size 1024m;Database altered. SQL alter database add logfile group 5 /ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO05.LOG size 1024m;Database

40、 altered. SQL alter database add logfile group 6 /ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO06.LOG size 1024m;Database altered. 删除先前日志过小的日志组1、2、3SQL alter database drop logfile group 1;Database altered. SQL alter database drop logfile group 2;Database altered. SQL alter database drop logfile group 3;Da

41、tabase altered. 删除日志组出现的错误，比如删除group 3时出现如下错误alter database drop logfile group 3*ERROR at line 1:ORA-01623: log 3 is current log for instance minos (thread 1) - cannot dropORA-00312: online log 3 thread 1: /datafile/u01/minos/minos/redo03.log对于这种情况，请执行切换日志命令，命令如下：SQL alter system switch logfile; Sys

42、tem altered. 执行该命令后，可以用以下命令查看当前日志组状态。SQL select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS- - - - - - -FIRST_CHANGE# FIRST_TIME- - 3 1 12 1 NO INACTIVE 31-OCT-11 4 1 12 1 NO CURRENT 31-OCT-11 5 1 11 1 NO ACTIVE 26-OCT-11 6 1 11 1 NO ACTIVE 26-OCT-11使用日志查询命令查出的结果中，必定有一个日志组为CURRENT

43、状态重复执行日志切换命令，可以使新增加的日志组状态由unused变为active如果删除日志时报错,报错的日志组会转入active状态，该状态不能删除，至少等待5分钟，当该日志组自动从active状态转为inactive后，该日志文件才能被删除。本步骤的删除、切换、查看日志命令可以反复执行，直到将group1,gourp2,gourp3日志都删除后，方可进入下一步。查询所有的日志的状态,确认redo日志大小和状态SQL select group#,bytes,members status from v$log; GROUP# BYTES STATUS- - - 4 1 5 1 6 13.6 临

44、时表空间迁移查询当前临时表空间大小并关闭数据库实例$ sqlplus /nologSQL conn sys/oracleminos as sysdbaSQL select file_name,tablespace_name,bytes,maxbytes,autoextensible from dba_temp_files;-需要先查看原有临时表空间大小是多少SQL shutdown immediate启动实例至mount状态后drop掉旧的临时表空间SQL connect sys/oracle as sysdbaSQL startup mountSQL alter database tempf

45、ile /datafile/u01/temp01.dbf drop including datafiles;打开数据库实例，新建临时表空间SQL alter database open;SQL ALTER TABLESPACE TEMP ADD TEMPFILE /datafile/u01/temp.dbf SIZE 3253M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED; -标红值为临时表空间的大小，建议设置成与旧临时表空间一样的大小SQL select file_name,tablespace_name,bytes,maxbytes from db

46、a_temp_files;SQL commit;SQL quit;3.7 控制文件迁移登录数据库查询控制文件位置$sqlplus /nolog$conn sys/oracleminos as sysdbaSQL select name from v$controlfile;NAME-/datafile/u02/minos/control01.ctl/datafile/u02/minos/control02.ctl/datafile/u02/minos/control03.ctl修改数据库系统中控制文件路径SQLalter system set control_files=/datafile/u

47、02/control01.ctl,/datafile/u02/control02.ctl,/datafile/u02/control03.ctl scope=spfile; SQLshutdown immediate 修改控制文件物理路径$cp /datafile/u01/control* /datafile/u02/ 连接数据库测试实例启动$sqlplus /nologSQLconn sys/oracle as sysdbaSQLstartup实例启动成功后，删除旧的控制文件$rm /datafile/u01/control* 3.8 重建及切换undo表空间用SYS用户以SYSDBA身份登

48、陆到数据库。$sqlplus /nologSQLconn sys/oracle as sysdba创建新的UNDO表空间SQLCREATE UNDO TABLESPACE UNDOTBS2 DATAFILE /datafile/u02/minos/undotbs02.dbf SIZE 16384M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE 32767M 切换UNDO表空间为新的UNDO表空间SQLalter system set undo_tablespace=undotbs2 scope=both;等待原UNDO表空间所有UNDO SEGMENT OFFL

49、INE SQLselect usn,xacts,status from v$rollstat;看STATUS字段的值是否有PENDING OFFLINE值，如果有就等待一会再执行上面的语句观察，知道没有，再执行下一步操作删除原UNDO表空间SQLdrop tablespace undotbs1 including contents and datafiles;4 数据库常用备份方法4.1 逻辑备份导入导出的常用命令 按用户导出，导入$exp uep4x/U_tywg_2008minos file=/export/home/test.dmp log=/tmp/exp_test.log$imp u

50、ep4x/U_tywg_2008minos full=y file=/export/home/test.dmp log=/tmp/exp_test.log 多个用户导出，导入exp system/oracleminos owner=(uep4x,caffm4x) file=/export/home/test.dmp log=/tmp/exp_test.logimp system/oracleminos full=y file=/export/home/test.dmp full=y log=/tmp/exp_test.log 按表导出，导入exp minos_pm/u_tywg_2008 parfile=test.dat其中PARFILE代表传递给导出的参数文件的名字,这个文件将包含所有参数的入口项。file=/oracle/pmo