Oracle系统管理.docx

1、Oracle系统管理Oracle系统管理 (1)常见的数据库维护过程常见的数据库维护过程一数据备份1， 备份与恢复前的准备（1） 设定字符集 set NLS_LANG=.ZHS16CGB231280 export NLS_LANG（2） 检查空间大小 df -k（3） 资源开销 联机备份资源开销较大，一般在2：30分左右进行。2， 数据备份以下是文件exp.file的内容：exp userid=cinms3/cinms3 full=Y inctype=complete constraints=Y file=cinms3_200804_1.dmp log = cinms3_200804_1.lo

2、g注意备份生成的数据文件的名称要有时间标识，留下备份成功与否的log,备份的工作放在后台去执行，如下所示$ nohup /u03/oradata/exp.file &3， 数据恢复（1） 恢复前的检查（a） 磁盘空间的大小df -k（b） 数据表空间的大小 以下script sql 检查数据库中所有的表空间及数据文件的资源分配及目前的开销，剩余空间的大小，以便分析、决定是否需要扩展数据文件。 select a.file_id FileNo,a.tablespace_name Tablespace_name,a.file_name DataFile_name,a.bytes Bytes,a.by

3、tes-sum(nvl(b.bytes,0) Used,sum(nvl(b.bytes,0) Free,sum(nvl(b.bytes,0)/a.bytes*100 %freefrom dba_data_files a, dba_free_space bwhere a.file_id=b.file_id(+) group by a.tablespace_name ,a.file_name,a.file_id,a.bytes order by a.tablespace_name（c） 回滚段 检查目前所用的回滚段，及其开销。select n.name,s.extents,s.rssize,s.s

4、tatus from v$rollname n,v$rollstat s where n.usn=s.usn;由于在恢复数据时，事务交易是大事务集中处理，回滚段开销较大，应考虑扩展回滚表空间与回滚段，使某个回滚段的大小在260M以上，交易处理在此回滚段上进行。CREATE ROLLBACK SEGMENT r15 TABLESPACE rbs STORAGE ( INITIAL 1m NEXT 1m MINEXTENTS 10 MAXEXTENTS 280m OPTIMAL 20m); 使回滚段在线： 当回滚段创建后，回滚段是离线的，不能被数据库使用， 为了使回滚段被事务利用，必须将回滚段在线

5、。可以用以下命令使回滚段在线： ALTER ROLLBACK SEGMENT r15 ONLINE; 其他回滚段离线 ALTER ROLLBACK SEGMENT rollback_segment OFFLINE; ALTER ROLLBACK SEGMENT r01 OFFLINE; ALTER ROLLBACK SEGMENT r02 OFFLINE; ALTER ROLLBACK SEGMENT r03 OFFLINE; ALTER ROLLBACK SEGMENT r04 OFFLINE; ALTER ROLLBACK SEGMENT r05 OFFLINE; ALTER ROLLBA

6、CK SEGMENT r06 OFFLINE; ALTER ROLLBACK SEGMENT r07 OFFLINE; ALTER ROLLBACK SEGMENT r08 OFFLINE; ALTER ROLLBACK SEGMENT r09 OFFLINE; ALTER ROLLBACK SEGMENT r10 OFFLINE; ALTER ROLLBACK SEGMENT r11 OFFLINE; ALTER ROLLBACK SEGMENT r12 OFFLINE; 此时，在import恢复数据时，交易将只在r15上进行。 （d） 扩展数据表空间 重新调整数据文件的大小，要均匀地分布在

7、不同的磁盘上。alter tablespace CINMS_USER add datafile /u02/oradata/unicom/cinms_user000.dbf size 500m；（e） 扩展系统表空间 扩展系统表空间的大小 alter tablespace SYSTEM add datafile /u02/oradata/unicom/system02.dbf size 300m；（f） 共享缓冲池 清除共享池数据：alter system flush shared_pool；功能：清除shared_pool中的碎片，解决连续的空间(db_block_size * db_bloc

8、k_buffers决定了share_pool的大小)（g） 字符集 再次检查字符集 set NLS_LANG=.ZHS16CGB231280 export NLS_LANG（h） 清除回滚段数据: alter rollback segment testroll shrink; 功能：把回滚段已提交地事务清空，使占有地资源释放出来。（i） 删除不需要的会话 查看后台进程, 删除不需要的会话 select spid,name from v$process,v$bgprocess where addr = paddr alter system kill session session_id,proc

9、ess_id;（j） （2） 恢复过程 恢复时，执行imp.file，并提交到后台执行。Imp.file文件内容如下： imp userid=cinms3/cinms3 fromuser=cinms3 ignore=y commit=y f ile= unicom_cinms3.dmp log=cinms3_200108021_1.log 恢复工作放在后台去执行，如下所示 $ nohup /u03/oradata/imp.file &（3） 恢复后的检查 (a), 检查数据库对象的个数 检查数据库中表地个数，Trigger,Procedure,Package,Funcation地个数，与原库中

10、相应地数据库对 象进行比较，如相等，说明对象及数据已恢复。 select count(*) from user_source where type =FUNCTION; select count(*) from user_source where type =PROCEDURE; select count(*) from user_source where name like RADIUS%; select count(*) from user_source where type =PACKAGE; select count(*) from user_tables; （b） 使回滚段在线 可以

11、用以下命令使回滚段在线： ALTER ROLLBACK SEGMENT r15 ONLINE; ALTER ROLLBACK SEGMENT rollback_segment OFFLINE; ALTER ROLLBACK SEGMENT r01 ONLINE; ALTER ROLLBACK SEGMENT r02 ONLINE; ALTER ROLLBACK SEGMENT r03 ONLINE; ALTER ROLLBACK SEGMENT r04 ONLINE; ALTER ROLLBACK SEGMENT r05 ONLINE; ALTER ROLLBACK SEGMENT r06 O

12、NLINE; ALTER ROLLBACK SEGMENT r07 ONLINE; 清除回滚段数据: alter rollback segmentr15 shrink;（c） 重新编译数据库对象TRIGGER /* 编译所有的触发器 */set pages 0 feed off echo offspool trgcomp.sqlprompt spool trgcom.lstprompt set echo on feed onselect alter trigger | trigger_name | compile; from user_triggers;prompt spool offspoo

13、l off（d） 重新编译数据库对象PROCEDURE,FUNCATION,PACKAGE /* 编译所有的函数，过程，包 */set pages 0 feed off echo offspool ppfcomp.sqlprompt spool ppfcompprompt set echo on feed onselect distinct alter | type | |name | compile; from user_source where type PACKAGE BODY;prompt spool offspool off（e） 快速编译所有视图当在把数据库倒入到新的服务器上后(数

14、据库重建)，需要将视图重新编译一遍，因为该表空间视图到其它表空间的表的连接会出现问题，可以利用PL/SQL的语言特性，快速编译。SQL SPOOL comp_view.sqlSQL SELECT ALTER VIEW |TNAME| COMPILE; FROM USER_TABLES;SQL SPOOL OFF然后执行ON.sql即可。SQL comp_view.SQL当然，授权和创建同义词也可以快速进行，如：SQL SELECT GRANT SELECT ON |TNAME| TO USERNAME; FROM USER_TABLES;SQL SELECT CREATE SYNONYM |T

15、NAME| FOR USERNAME.|TNAME|; FROM TABLES;（f） 数据库重建应注意的问题在利用import进行数据库重建过程中,有些视图可能会带来问题,因为结构输入的顺序可能造成视图的输入先于它低层次表的输入,这样建立视图就会失败.要解决这一问题,可采取分两步走的方法:首先输入结构,然后输入数据.命令举例如下 (uesrname:cinms3,password:cinms3,host sting:unicom,数据文件:expdata.dmp): impcinms3/cinms3unicom file=empdata.dmp rows=Nimpcinms3/cinms3u

16、nicom file=empdata.dmp full=Y buffer=64000 commit=Y ignore=Y第一条命令输入所有数据库结构,但无记录.第二次输入结构和数据,64000字节提交一次,ignore=Y选项保证第二次输入既使对象存在的情况下也能成功.（g） 4， 阿若误认为二 空间管理 1 检查目前表空间的大中小 数据文件的创建应以在磁盘上均匀分布为原则，创建时，在所有磁盘上要同时创建，使数据均匀地分布在各个磁盘上。col FileNo format a3col Tablespace_name format a22col DataFile_name format a30co

17、l Bytes format a15col Used format a15col Free format a15col %free format a15 select a.file_id FileNo,a.tablespace_name Tablespace_name,a.file_name DataFile_name,a.bytes Bytes,a.bytes-sum(nvl(b.bytes,0) Used,sum(nvl(b.bytes,0) Free,sum(nvl(b.bytes,0)/a.bytes*100 %freefrom dba_data_files a, dba_free_s

18、pace bwhere a.file_id=b.file_id(+) group by a.tablespace_name ,a.file_name,a.file_id,a.bytes order by a.tablespace_name2 检查某个数据文件上含有的数据库表 在数据文件/export/home/app/oracle/datafile/cinms3_user02.dbf上有那些数据库表，将segment_type 改为TRIGGER,PROCEDURE,FUNCATION,PACKAGE等可以查看其他数据库对象在数据文件上地分布情况。col segment_name format

19、 a20col tablespace_name format a12col file_name format a45select a.segment_name,a.tablespace_name,b.file_name from user_segments a, dba_data_files b where a.segment_type=TABLE and a.tablespace_name=b.tablespace_name and b.file_name=/export/home/app/oracle/datafile/cinms3_user02.dbf ;3 将数据文件从一个磁盘移到另一

20、个 (1) 使用SERVER MANAGER关闭实例SVRMGR connect internal;SVRMGR shutdown;SVRMGR exit; (2) 移动数据库文件位置 使用操作系统命令来移动数据库文件位置(假设这里操作系统为SOLARIS 2.7). 在UNIX中用 mv命令可以把文件移动到新的位置，mv /export/home/oracle/u02/cinms_user81.dbf /export/home/oracle/oradata/cinmsmv /u02/oradata/unicom/cinms_user04.dbf /u03/oradata/unicom (3)

21、 装载数据库 装载数据库并用alter database命令来改变数据库中的文件名SVRMGR connect internal;SVRMGR startup mount SVRMGR alter database rename file / ora13/orarun/document.dbf to/ ora12/orarun/document.dbf;或者alter database rename file /export/home/oracle/u02/cinms_user81.dbf to /export/home/oracle/oradata/cinms/cinms_user81.db

22、f;或者alter database rename file /u02/oradata/unicom/cinms_user04.dbf to /u03/oradata/unicom/cinms_user04.dbf; (4). 启动实例. SVRMGR alter database open;4 删掉某个数据文件 简单的方法是offline并删除该数据文件svrmgrlstartup mount svrmgrlalter database datafile /u02/oradata/unicom/cinms_user04.dbf offline drop;svrmgrlalter databa

23、se datafile /u02/oradata/unicom/cinms_user00.dbf offline drop;svrmgrlalter database datafile /u02/oradata/unicom/cinms_user01.dbf offline drop;svrmgrlalter database datafile /u02/oradata/unicom/cinms_user02.dbf offline drop;svrmgrlalter database datafile /u02/oradata/unicom/cinms_user03.dbf offline

24、drop;svrmgrlalter database datafile /u02/oradata/unicom/cinms_user05.dbf offline drop;svrmgrlalter database open; 此时，可将数据文件u02/oradata/unicom/cinms_user04.dbf从磁盘上物理删除。5 删除某个表空间及其含有的所有数据文件(a）alter tablespace CINMS_USER offline;(b) drop tablespace CINMS_USER including contents;(c) drop tablespace CINM

25、S_USER;随着表空间地删除，其上地数据文件随之删除。6 检查回滚段地状态 对于电信业务而言，基本上联机事务处理OLTP,回滚段不要太大，个数要适当多，究竟多少要根据具体情况而定，但有一点，一定要联机ONLINE,检查Script sql如下：select n.name,s.extents,s.rssize,s.optsize,s.hwmsize,s.xacts,s.status from v$rollname n,v$rollstat s where n.usn=s.usn;SQL /NAME EXTENTS RSSIZE OPTSIZE STATUS- - - - -SYSTEM 8 4

26、07552 ONLINER05 10 11589632 ONLINER01 545 72542208 ONLINER02 103 34168832 ONLINER03 37 10606592 ONLINER04 171 22759424 ONLINER06 5 6316032 ONLINER07 3 4206592 ONLINER08 4 5261312 ONLINER09 64 68544512 ONLINER10 5 6316032 ONLINE从上可以看到，R01占用了较多地区域，应作shrink处理。同时系统在长期运行后，可能会发生rollback segment offline现象，

27、此时，要做如下处理：清除回滚段数据: alter rollback segment testroll shrink;ALTER ROLLBACK SEGMENT r15 ONLINE;7 表空间的创建和扩建 （1） 创建临时表空间 create tablespace temp_user datafile /u01/oradata/cinms/Billing_curcomdedial_1.dbf size 200m, temporary； （2） 创建用户数据表空间 （a）方法1 CREATE TABLESPACE CINMS_USER DATAFILE /u02/oradata/unicom/

28、cinms_user000.dbf SIZE 500m REUSE, /u02/oradata/unicom/cinms_user001.dbf SIZE 500m REUSE DEFAULT STORAGE ( INITIAL 40K NEXT 40K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 )； （b）方法2 CREATE TABLESPACE CINMS_USER33 DATAFILE /export/home/app/oracle/datafile/cinms_user000.dbf SIZE 1m DEFAULT STORAG

29、E ( INITIAL 40K NEXT 40K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 )； （4） 创建索引表空间 CREATE TABLESPACE CINMS_INDEXES DATAFILE /u03/oradata/unicom/cinms_index03.dbf SIZE 300M DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 )；（5） 在给定的表空间上增加数据文件（扩展表空间）alter table

30、space CINMS_USER add datafile /u02/oradata/unicom/cinms_user002.dbf size 500malter tablespace CINMS_USER add datafile /u02/oradata/unicom/cinms_user003.dbf size 500m（6） 重建表空间及所有对象 删除某个用户，同时将此用户所具有的数据库对象全部删除，包括，table,index,procedure,funcation,package等，然后再创建表空间及其数据文件。以system/manager用户登录数据库，执行如下script sql:drop user cinms3 cascade;create user cinms3 identified by cinms3 default tablespace cinms_user;grant resource to cinms3;grant dba to cinms3;