數(shù)據(jù)庫日常維護培訓(xùn)20111122

1、數(shù)據(jù)庫日常維護廣州中軟信息技術(shù)有限公司2011-11-22主講人：何滿文公司6,8樓無線密碼 gzcssWLAN公司7樓無線密碼 gzcs172.16.11.6 用戶名root 密碼 root 用戶名 oracle 密碼 oracle課程安排Oracle 數(shù)據(jù)庫體系結(jié)構(gòu)探討Oracle數(shù)據(jù)庫日常維護及相關(guān)操作:INSTANCE 狀態(tài)管理Oracle當(dāng)前連接管理數(shù)據(jù)庫健康檢查數(shù)據(jù)庫安全整改數(shù)據(jù)庫權(quán)限管理數(shù)據(jù)庫邏輯備份（exp/imp）數(shù)據(jù)庫圖形管理界面（EM dbconsole）練習(xí)及問題解答Database StructuresStorage structuresMemory structur

2、esProcess structuresInstanceSystem Global Area (SGA)Background processesDatabase filesDB structures - Memory - Process - StorageOracle Instance ManagementSystemMonitor(SMON)DatabaseWriter(DBWn)LogWriter(LGWR)ProcessMonitor(PMON)Archiver(ARCn)SGAJava poolShared poolLarge poolStreams poolDatabasebuffe

3、r cacheRedo log bufferCheckpoint(CKPT)ControlfilesDatafilesRedo logfilesArchive log files一、確認所有的 INSTANCE 狀態(tài)正常1、登陸到數(shù)據(jù)庫主機, 檢測 ORACLE 后臺進程 :# su - oracle$ ps -ef |grep ora_ oracle 290952 1 0 Jul 14 - 1:50 ora_ckpt_zsds oracle 295080 1 0 Jul 14 - 0:00 ora_reco_zsds oracle 344132 1 0 Jul 14 - 0:36 ora_d

4、bw0_zsds oracle 446476 1 0 Jul 14 - 0:00 ora_d000_zsds oracle 450612 1 0 Jul 14 - 0:15 ora_mman_zsds oracle 459000 1 0 Jul 14 - 2:58 ora_cjq0_zsds oracle 471044 1 0 Jul 14 - 1:38 ora_mmon_zsds oracle 475310 1 0 Jul 14 - 1:20 ora_pmon_zsds oracle 479384 1 0 Jul 14 - 1:20 ora_psp0_zsds oracle 487442 1

5、 0 Jul 14 - 0:01 ora_s000_zsds oracle 491674 1 0 Jul 14 - 0:31 ora_lgwr_zsds oracle 503830 1 0 Jul 14 - 0:53 ora_smon_zsds oracle 508154 1 0 Jul 14 - 2:52 ora_mmnl_zsds oracle 520446 1 0 Jul 14 - 0:01 ora_arc0_zsds oracle 524288 1 0 Jul 14 - 0:08 ora_arc1_zsds oracle 528390 1 0 Jul 14 - 0:03 ora_qmn

6、c_zsds oracle 540682 1 0 Jul 14 - 0:08 ora_q000_zsds oracle 577562 1 0 Jul 14 - 0:02 ora_q001_zsds 一、確認所有的 INSTANCE 狀態(tài)正常1、登陸到數(shù)據(jù)庫主機, 檢測 ORACLE 后臺進程 :1. DBWR 數(shù)據(jù)庫寫入進程 : DBWR The Database Writer Process 進程負責(zé)將臟數(shù)據(jù)塊從數(shù)據(jù)緩存（database block buffer)寫回磁盤. 2. LGWR 日志寫入進程 LGWR (the log writer process)負責(zé)將日志緩沖區(qū)中的數(shù)據(jù)寫入

7、重做日志。 3. PMON 進程監(jiān)視進程 PMON(the Process Monitor Process)進程負責(zé)監(jiān)視數(shù)據(jù)庫的處理情況,并負責(zé)清除死掉的進程。 4. SMON 系統(tǒng)監(jiān)視進程 SMON(the System Monitor Process)進程負責(zé)在實例啟動時恢復(fù)實例. 5.CKPT 檢查點進程 CKPT(the Checkpoint Process)進程負責(zé)向DBWR進程發(fā)送信號，要求執(zhí)行一次檢查點. 一、確認所有的 INSTANCE 狀態(tài)正常1、登陸到數(shù)據(jù)庫主機, 檢測 ORACLE 后臺進程 :6. ARCHn 歸檔進程 ARCH(the Archiver Process

8、 )負責(zé)將再現(xiàn)重做日志復(fù)制到歸檔存儲器。 7. RECO 恢復(fù)進程 RECO (the Recovery Process)進程用于清除分布式數(shù)據(jù)庫中的未決(pending)事務(wù). 8. Dnnn 調(diào)度進程 如果選擇了Multithreaded 選項，則對使用的每個通信協(xié)議至少有一個調(diào)度進程. 9. Snnn 共享服務(wù)器進程 當(dāng)使用共享服務(wù)器配置或MTS時，會用到共享服務(wù)器進程(the Shared Servers Process ,Snnn). 10. QMNn 隊列管理進程 使用高級隊列選項時(advanced queueing option)，隊列管理進程 (Queue Manager P

9、rocess )用于管理作業(yè)隊列.一、確認所有的 INSTANCE 狀態(tài)正常2、登陸到數(shù)據(jù)庫檢查數(shù)據(jù)庫open mode$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 21 11:54:41 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductioWith

10、 the Partitioning, OLAP and Data Mining options SQL select open_mode from v$database; OPEN_MODE - READ WRITE 一、確認所有的 INSTANCE 狀態(tài)正常3、RAC數(shù)據(jù)庫$crsctl check crsCSS appears healthy CRS appears healthy EVM appears healthy $crs_stat -t Name Type Target State Host - ora.RV.lsnr application ONLINE ONLINE dgps

11、._srvora.srv.ons application ONLINE ONLINE dgps._srvora.srv.vip application ONLINE ONLINE dgps._srvora.RV.lsnr application ONLINE ONLINE dgps._srvora.srv.ons application ONLINE ONLINE dgps._srvora.srv.vip application ONLINE ONLINE dgps._srvora.dgyd.db application ONLINE ONLINE dgps._srvora.d1.inst a

12、pplication ONLINE ONLINE dgps._srvora.d2.inst application ONLINE ONLINE dgps._srv一、確認所有的 INSTANCE 狀態(tài)正常4、檢查日志文件$ sqlplus / as sysdbaSQL show parameter background_dump_dest NAME TYPE VALUE - - - background_dump_dest string /oracle/admin/zsds/bdump SQL exit Disconnected from Oracle Database 10g Enterpr

13、ise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options $ ls -l /oracle/admin/zsds/bdump/*.log -rwxrwxrwx 1 oracle dba 128426 Jul 20 22:00 /oracle/admin/zsds/bdump/alert_zsds.log $ tail -100 /oracle/admin/zsds/bdump/alert_zsds.log Tue Jul 19 14:00:57 201

14、1 Thread 1 advanced to log sequence 148 Current log# 3 seq# 148 mem# 0: /oracle/oradata/zsds/redo03.logWed Jul 20 02:14:23 2011 Thread 1 advanced to log sequence 149 一、確認所有的 INSTANCE 狀態(tài)正常 5、數(shù)據(jù)庫的啟動和關(guān)閉單實例單實例$ sqlplus / as sysdba SQL shutdown immediate Database closed. Database dismounted. ORACLE insta

15、nce shut down. SQL startup ORACLE instance started. Total System Global Area 1258291200 bytes Fixed Size 2020552 bytes Variable Size 318769976 bytes Database Buffers 922746880 bytes Redo Buffers 14753792 bytes Database mounted. Database opened. 一、確認所有的 INSTANCE 狀態(tài)正常 5、數(shù)據(jù)庫的啟動和關(guān)閉RAC實例實例停止RAC數(shù)據(jù)庫所有的實例 $

16、 srvctl stop database -d 數(shù)據(jù)庫名數(shù)據(jù)庫名例如：關(guān)閉營配gisdb數(shù)據(jù)庫所有實例 $ srvctl stop database d gisdb 停止指定實例$srvctl stop instance d 數(shù)據(jù)庫名數(shù)據(jù)庫名 i 實例名實例名例子：關(guān)閉營配數(shù)據(jù)庫gisdb1實例$srvctl stop instance -d gisdb i gisdb1 啟動RAC數(shù)據(jù)庫所有實例$srvctl start database d 數(shù)據(jù)庫名數(shù)據(jù)庫名例如：啟動營配gisdb數(shù)據(jù)庫所有實例 $ srvctl start database d gisdb 一、確認所有的 INSTAN

17、CE 狀態(tài)正常 5、數(shù)據(jù)庫的啟動和關(guān)閉RAC實例實例啟動RAC數(shù)據(jù)庫指定實例$ srvctl start instance -d 數(shù)據(jù)庫名數(shù)據(jù)庫名 -i 實例名實例名例子：啟動營配數(shù)據(jù)庫gisdb1實例 $ srvctl start instance -d gisdb i gisdb1 查看RAC數(shù)據(jù)庫CRS服務(wù)#crsctl check crs 停止RAC數(shù)據(jù)庫CRS服務(wù)(root用戶)#crsctl stop crs 啟動RAC數(shù)據(jù)庫CRS服務(wù)(root用戶)#crsctl start crs 二、查看oracle當(dāng)前連接1、查看當(dāng)前的連接數(shù)$ sqlplus / as sysdbaSQL

18、 select count(*) from v$session; COUNT(*) - 182、查看不同用戶的連接數(shù) SQL select username,count(username) from v$session where username is not null group by username;USERNAME COUNT(USERNAME) - - SYS 13、查看活動的連接SQL Select count(*) from v$session where status=ACTIVE; COUNT(*) - 18二、查看oracle當(dāng)前連接4、列出當(dāng)前數(shù)據(jù)庫建立的會話情況:SQ

19、Lselect sid,serial#,username,program,machine,status from v$session; SID SERIAL# USERNAME PROGRAM MACHINE STATUS- - - - - - 142 9808 oraclegzcssaix2 (J000) gzcssaix2 ACTIVE 143 2 oraclegzcssaix2 (q001) gzcssaix2 ACTIVE 146 18609 SYS sqlplusgzcssaix2 (TNS V1-V3) gzcssaix2 ACTIVE 147 1 oraclegzcssaix2

20、(q000) gzcssaix2 ACTIVE 149 1 oraclegzcssaix2 (QMNC) gzcssaix2 ACTIVE 155 3 oraclegzcssaix2 (ARC0) gzcssaix2 ACTIVE 156 3 oraclegzcssaix2 (ARC1) gzcssaix2 ACTIVE 160 1 oraclegzcssaix2 (MMNL) gzcssaix2 ACTIVE 161 1 oraclegzcssaix2 (MMON) gzcssaix2 ACTIVE 162 1 oraclegzcssaix2 (CJQ0) gzcssaix2 ACTIVE

21、163 1 oraclegzcssaix2 (RECO) gzcssaix2 ACTIVE二、查看oracle當(dāng)前連接5、查看數(shù)據(jù)庫最大連接配置SQL show parameter processes NAME TYPE VALUE- - -aq_tm_processes integer 0 db_writer_processes integer 1 gcs_server_processes integer 0 job_queue_processes integer 10 log_archive_max_processes integer 2 processes integer 150 6、修

22、改數(shù)據(jù)庫最大連接配置SQL alter system set processes=200 scope=spfile;System altered.RAC數(shù)據(jù)庫：SQL alter system set processes=200 scope=spfile sid=*;System altered.二、查看oracle當(dāng)前連接7、終止會話SQLselect sid,serial#,username,program,machine,statusfrom v$session; SID SERIAL# USERN PROGRAM MACHINE STATUS- - - - - - 142 10212

23、oraclegzcssaix2 (J000) gzcssaix2 ACTIVE 143 2 oraclegzcssaix2 (q001) gzcssaix2 ACTIVE 146 18611 SYS sqlplusgzcssaix2 (TNS V1-V3) gzcssaix2 ACTIVESID 會話(session)的ID號；SERIAL# 會話的序列號，和SID一起用來唯一標識一個會話；USERNAME 建立該會話的用戶名；PROGRAM 這個會話是用什么工具連接到數(shù)據(jù)庫的；STATUS 當(dāng)前這個會話的狀態(tài)，ACTIVE表示會話正在執(zhí)行某些任務(wù)， INACTIVE表示當(dāng)前會話沒有執(zhí)行任何操

24、作； 如果DBA要手工斷開某個會話，則執(zhí)行：SQLalter system kill session SID,SERIAL#;SQLalter system kill session 146,18611;三、數(shù)據(jù)庫健康檢查1、檢查數(shù)據(jù)庫版本SQL select * from v$version; BANNER -Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - Production CORE10.2.0.4.0Production TNS for IBM/AIX

25、 RISC System/6000: Version 10.2.0.4.0 - ProductioNLSRTL Version 10.2.0.4.0 - Production 三、數(shù)據(jù)庫健康檢查2、檢查控制文件位置和數(shù)量SQL Select * from v$controlfile; STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS- - - - - /dev/ryd_control01 NO 16384 984 /dev/ryd_control02 NO 16384 984 /dev/ryd_control03 NO 16384 984三、數(shù)據(jù)庫健康檢查3、

26、檢查redo log文件組和成員的數(shù)量、大小、狀態(tài)select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARCHIVED,STATUS from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS - - - - - - - 1 1 95781 524288000 1 NO CURRENT 2 1 95777 524288000 1 YES INACTIVE 3 1 95778 524288000 1 YES INACTIVE 4 1 95779 524288000 1 YES INACTIVE

27、 5 1 95780 524288000 1 YES ACTIVE 6 2 54416 524288000 1 YES ACTIVE 7 2 54417 524288000 1 YES ACTIVE 8 2 54418 524288000 1 NO CURRENT 9 2 54415 524288000 1 YES INACTIVE 10 2 54414 524288000 1 YES INACTIVE 10 rows selected. 三、數(shù)據(jù)庫健康檢查4、檢查redo log文件組切換時間select group#,sequence#,to_char(first_time,yyyy-mm

28、-dd hh24:mi:ss) from v$log; GROUP# SEQUENCE# TO_CHAR(FIRST_TIME, - - - 1 95781 2011-06-08 11:57:33 2 95777 2011-06-08 11:07:27 3 95778 2011-06-08 11:17:30 4 95779 2011-06-08 11:30:14 5 95780 2011-06-08 11:42:57 6 54416 2011-06-08 11:55:57 7 54417 2011-06-08 11:57:51 8 54418 2011-06-08 12:01:05 9 544

29、15 2011-06-08 11:47:47 10 54414 2011-06-08 11:43:12 10 rows selected. 三、數(shù)據(jù)庫健康檢查5、檢查redo log文件select group#,status,member from v$logfile； GROUP# STATUS MEMBER - - - 1 /dev/ryd_redo11 2 /dev/ryd_redo12 3 /dev/ryd_redo13 4 /dev/ryd_redo14 5 /dev/ryd_redo15 6 /dev/ryd_redo21 7 /dev/ryd_redo22 8 /dev/ryd

30、_redo23 9 /dev/ryd_redo24 10 /dev/ryd_redo25 10 rows selected. 三、數(shù)據(jù)庫健康檢查6、數(shù)據(jù)庫是否處于歸檔模式及歸檔進程 SQL archive log listDatabase log mode Archive ModeAutomatic archival Enabled Archive destination /ydarch1 Oldest online log sequence 95777 Next log sequence to archive 95781 Current log sequence 95781 7、檢查歸檔文件

31、占用空間大小oracledgps_yddb2_srv$ df -g Filesystem GB blocks Free %Used Iused %Iused Mounted on /dev/hd4 0.50 0.40 21% 3297 4% / /dev/hd1 1.00 0.95 5% 520 1% /home /proc - - - - - /proc /dev/hd10opt 0.50 0.21 58% 1134 3% /opt /dev/ora_soft 20.00 2.50 88% 137535 19% /oracle /dev/fslv01 348.00 319.82 9% 385

32、 1% /ydarch1三、數(shù)據(jù)庫健康檢查8、檢查表空間使用情況SELECT D.TABLESPACE_NAME,SPACE SUM_SPACE(M),SPACE-NVL(FREE_SPACE,0) USED_SPACE(M), FREE_SPACE FREE_SPACE(M),ROUND(1-NVL(FREE_SPACE,0)/SPACE)*100) USED_RATE(%) FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP

33、BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY USED_RATE(%) TABLESPACE_NAME SUM_SPACE(M) USED_SPACE(M) FREE_SPACE(M) USED_RATE(%) - - - - - LROB 250 0 250 0

34、 DGITJK 2040 0 2040 0 USERS 100 20 80 20 YD_DATA 393120 172478 220642 44 PART_DATA 319410 165219 154191 52 INDX 188370 115528 72842 61 SYSTEM 2000 1379 621 69 PERFSTAT 500 386 114 77 SYSAUX 3000 2393 607 80 UNDOTBS1 24570 23628 942 90 UNDOTBS2 28570 28548 22 90 11 rows selected. 三、數(shù)據(jù)庫健康檢查9、檢查數(shù)據(jù)文件是否自

35、動擴展SQL select file_name,autoextensible from dba_data_files;FILE_NAME AUT- -/oracle/oradata/zsds/users01.dbf YES/oracle/oradata/zsds/sysaux01.dbf YES/oracle/oradata/zsds/undotbs01.dbf YES/oracle/oradata/zsds/system01.dbf YES/oracle/oradata/zsds_data.dbf YESSQLselect file_name,autoextensible from dba_

36、data_files where autoextensible=YES；FILE_NAME AUT - - /dev/rlv_scmis_system YES /dev/rlv_scmis_undo01 YES /dev/rlv_scmis_sysaux YES /dev/rlv_scmis_undo02 YES /dev/rlv_scmis_user01 YES 三、數(shù)據(jù)庫健康檢查9、檢查數(shù)據(jù)文件是否自動擴展建表空間語法：CREATE TABLESPACE 表空間名 DATAFILE 數(shù)據(jù)文件 SIZE 1MAUTOEXTEND ONEXTENT MANAGEMENT LOCALSEGMEN

37、T SPACE MANAGEMENT AUTO;撤銷數(shù)據(jù)文件自動擴展：Alter database datafile 數(shù)據(jù)庫文件 AUTOEXTEND OFF;三、數(shù)據(jù)庫健康檢查10、檢查用戶默認的表空間和臨時表空間select username,default_tablespace,temporary_tablespace,account_status,profile from dba_users； USERNAME DEFAULT_TABLESPACE TEMPORARY_ ACCOUNT_STATUS PROFILE - - - - -YXKF1 YD_DATA TEMP OPEN DE

38、FAULTMUSER YD_DATA TEMP OPEN DEFAULTGDYD YD_DATA TEMP OPEN DEFAULTYPJK YD_DATA TEMP OPEN DEFAULTJSUSER YD_DATA TEMP OPEN DEFAULTCIMDG YD_DATA TEMP OPEN DEFAULTCIMUP YD_DATA TEMP OPEN DEFAULTDGYD_READER YD_DATA TEMP EXPIRED(GRACE) DEFAULTZHUSER YD_DATA TEMP EXPIRED(GRACE) DEFAULTDGFK YD_DATA TEMP EXP

39、IRED(GRACE) DEFAULTDGJC YD_DATA TEMP EXPIRED(GRACE) DEFAULTMFUSER YD_DATA TEMP EXPIRED(GRACE) DEFAULTDGDL YD_DATA TEMP LOCKED(TIMED) DEFAULTYDEXPGZ YD_DATA TEMP LOCKED DEFAULTYXKF YD_DATA TEMP LOCKED DEFAULT三、數(shù)據(jù)庫健康檢查11、檢查用戶權(quán)限 select grantee,privilege from DBA_SYS_PRIVS where grantee in (select usern

40、ame from dba_users where account_status=OPEN) and grantee not in (SYS,SYSTEM,SYSMAN,DBSNMP)order by 1 ;GRANTEE PRIVILEGE - -CALL CREATE ANY SYNONYM CALL DEBUG ANY PROCEDURE CIMDG UNLIMITED TABLESPACE CIMUP CREATE ANY TABLE CIMUP CREATE ANY TRIGGER CIMUP SELECT ANY TABLE CIMUP UNDER ANY TABLE CIMUP U

41、NLIMITED TABLESPACE CIMUP UPDATE ANY TABLE DGYDETL CREATE ANY SYNONYM DGYDETL SELECT ANY TABLE DGYS ALTER SESSION DGYS ALTER SYSTEM DGYS ANALYZE ANY 三、數(shù)據(jù)庫健康檢查11、檢查用戶權(quán)限 創(chuàng)建用戶CREATE USER 用戶名 IDENTIFIED BY 密碼 DEFAULT TABLESPACE example QUOTA 10M ON example TEMPORARY TABLESPACE temp QUOTA 5M ON system PR

42、OFILE app_user PASSWORD EXPIRE; 給用戶授權(quán)Grant 權(quán)限 to 用戶名;三、數(shù)據(jù)庫健康檢查12、檢查有無運行失敗的JOB select job,this_date,this_sec,next_date,next_sec,failures,what from dba_jobs where failures !=0； JOB THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC FAILURES - - - - - - WHAT - 444 01-JAN-00 00:00:00 16 KF_P_SMS_SEND_RESULT_UPDATE;

43、三、數(shù)據(jù)庫健康檢查13、檢查有無非法的數(shù)據(jù)庫對象select object_name, object_type, owner,status from dba_objects where status !=VALID and owner not in (SYS,SYSTEM)and object_type in (TRIGGER,VIEW,PROCEDURE,FUNCTION)；OBJECT_NAME OBJECT_TYPE OWNER STATUS - - - -ALL_WM_LOCKED_TABLES VIEW WMSYS INVALID USER_WM_LOCKED_TABLES VIEW

44、 WMSYS INVALID P_AUTO_CB_WRITE PROCEDURE DGYS INVALID DF_F_ALL_CB_WRITE_INITIAL0 FUNCTION DGYS INVALID GENE_WRITE_INIT_FILES FUNCTION DGYS INVALID NO_VM_DROP_PROC PROCEDURE WMSYS INVALID EXPFIL_ALTEREXPTAB_MAINT TRIGGER EXFSYS INVALID JL_P_SEAL_AUTO_RECEIVE PROCEDURE DGDL INVALID JL_P_SEAL_TERMINAL_

45、TREAT PROCEDURE DGDL INVALID GDPG_DW_YX_KH_MX_CUSTOMER_EQ PROCEDURE YXKF1 INVALID GDPG_DW_YX_KH_MX_QFTD PROCEDURE YXKF1 INVALID 三、數(shù)據(jù)庫健康檢查14、文件系統(tǒng)的空間使用情況oracledgps_yddb2_srv$ df -g Filesystem GB blocks Free %Used Iused %Iused Mounted on/dev/hd4 0.50 0.40 21% 3297 4% / /dev/hd2 4.00 0.75 82% 44217 20%

46、/usr /dev/hd9var 2.00 0.88 56% 18457 9% /var /dev/hd3 4.00 2.09 48% 2352 1% /tmp /dev/hd1 1.00 0.95 5% 520 1% /home /proc - - - - - /proc /dev/hd10opt 0.50 0.21 58% 1134 3% /opt /dev/ora_soft 20.00 2.50 88% 137535 19% /oracle /dev/fslv01 348.00 319.82 9% 385 1% /ydarch2 三、數(shù)據(jù)庫健康檢查14、文件系統(tǒng)的空間使用情況必要時對Or

47、acle的警告日志及TRC文件進行清理:SQL show parameter dump NAME TYPE VALUE - - -background_dump_dest string /oracle/admin/zsds/bdump core_dump_dest string /oracle/admin/zsds/cdump max_dump_file_size string UNLIMITED user_dump_dest string /oracle/admin/zsds/udump $ cd /oracle/admin/zsds/ $ ls adump bdump cdump dpdu

48、mp pfile udump$ du -sm * 0.62 adump 1.66 bdump 0.00 cdump 0.00 dpdump 0.00 pfile 0.39 udump 三、數(shù)據(jù)庫健康檢查15、檢查操作系統(tǒng)的資源使用情況是否正常$ topas三、數(shù)據(jù)庫健康檢查15、檢查操作系統(tǒng)的資源使用情況是否正常$ vmstat 1三、數(shù)據(jù)庫健康檢查16、檢查消耗資源較多的oracle進程-進程相關(guān)的連接信息SQLselect sid,serial#,username,program,machine,status from v$session where paddr in(select add

49、r from v$process where spid=&1)-該進程在數(shù)據(jù)庫上運行的sql語句SQL select sql_text from v$sql where address in(select sql_address from v$session where paddr in(select addr from v$process where spid=&1);三、數(shù)據(jù)庫健康檢查17、檢查數(shù)據(jù)庫當(dāng)日備份的有效性。對 RMAN 備份方式 :檢查第三方備份工具的備份日志以確定備份是否成功對 EXPORT 備份方式 :檢查 exp 日志文件以確定備份是否成功對其他備份方式

50、:檢查相應(yīng)的日志文件三、數(shù)據(jù)庫健康檢查18、檢查數(shù)據(jù)庫性能，生成性能報告$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 16 16:03:32 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP,

51、 Data Mining and Real Application Testing options SQL exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(flush_level=ALL);（在需要的時間執(zhí)行一次快照，系統(tǒng)會在這個時間點創(chuàng)建一個快照）PL/SQL procedure successfully completed. SQL exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(flush_level=ALL);（系統(tǒng)運行一段時間后在執(zhí)行一次快照）PL/SQL procedure successful

52、ly completed. 三、數(shù)據(jù)庫健康檢查18、檢查數(shù)據(jù)庫性能，生成性能報告S QL?/rdbms/admin/awrrpt.sql運行生成性能報告的腳本SQL ?/rdbms/admin/awrrpt.sql Current Instance DB Id DB Name Inst Num Instance- - - - 1195270118 SOC 1 SOC Specify the Report TypeWould you like an HTML report, or a plain text report?Enter html for an HTML report, or text

53、 for plain textDefaults to htmlEnter value for report_type:（默認為html）Type Specified: html 三、數(shù)據(jù)庫健康檢查18、檢查數(shù)據(jù)庫性能，生成性能報告Instances in this Workload Repository schema DB Id Inst Num DB Name Instance Host- - - - -* 1195270118 1 SOC SOC gddwbhm- Using 1195270118 for database IdUsing 1 for instance number Spe

54、cify the number of days of snapshots to choose fromEntering the number of days (n) will result in the most recent(n) days of snapshots being listed. Pressing withoutspecifying a number lists all completed snapshots. Enter value for num_days:1（1為當(dāng)天的快照，2為2天內(nèi)的，系統(tǒng)保留7天內(nèi)的快照）三、數(shù)據(jù)庫健康檢查18、檢查數(shù)據(jù)庫性能，生成性能報告Listi

55、ng the last days Completed Snapshots SnapInstance DB Name Snap Id Snap Started Level- - - - -SOC SOC 6674 16 Dec 2010 00:00 1 6686 16 Dec 2010 12:00 1 6687 16 Dec 2010 13:00 1 6688 16 Dec 2010 14:00 1 6689 16 Dec 2010 14:06 2 6690 16 Dec 2010 15:00 1 6691 16 Dec 2010 15:01 2 6692 16 Dec 2010 15:32 2

56、 6693 16 Dec 2010 16:03 2Specify the Begin and End Snapshot IdsEnter value for begin_snap: 6691Begin Snapshot Id specified: 6691 三、數(shù)據(jù)庫健康檢查18、檢查數(shù)據(jù)庫性能，生成性能報告Enter value for end_snap: 6693End Snapshot Id specified: 6693（根據(jù)需要輸入自己說要的快照點） Specify the Report NameThe default report file name is awrrpt_1_669

57、1_6693.html. To use this name,press to continue, otherwise enter an alternative. Enter value for report_name:（默認名為awrrpt_1_6691_6693.html） Using the report name awrrpt_1_6691_6693.html SQLexit$ls-rw-r-r- 1 oracle oinstall 420777 Dec 16 16:13 awrrpt_1_6691_6693.html報告生成在當(dāng)前目錄下四、數(shù)據(jù)庫安全整改1、 Oracle存在多個高危的

58、安全漏洞 查看當(dāng)前版本：SQL select * from v$version; BANNER -Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - Production CORE10.2.0.4.0Production TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - ProductioNLSRTL Version 10.2.0.4.0 - Production 必要時做數(shù)據(jù)庫版本升級整改建議：主要業(yè)務(wù)

59、數(shù)據(jù)庫不建議進行版本升級。四、數(shù)據(jù)庫安全整改2、啟用密碼復(fù)雜性驗證功能SQL select profile, resource_name, limit from dba_profiles where resource_type=PASSWORD order by 1；PROFILE RESOURCE_NAME LIMIT- - -DEFAULT PASSWORD_REUSE_TIME 1800DEFAULT PASSWORD_LOCK_TIME .0006DEFAULT FAILED_LOGIN_ATTEMPTS 3DEFAULT PASSWORD_VERIFY_FUNCTION VERIFY

60、_FUNCTIONDEFAULT PASSWORD_LIFE_TIME 60DEFAULT PASSWORD_REUSE_MAX UNLIMITEDDEFAULT PASSWORD_GRACE_TIME 10?/rdbms/admin/utlpwdmg.sql (修改后面設(shè)置)啟用PASSWORD_VERIFY_FUNCTION:alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION verify_function;啟用PASSWORD_REUSE_MAX：alter profile DEFAULT limit PASSWORD_REUSE_MAX 3；四、數(shù)