Oracle11gRAC巡检手册

Oracle 11g RAC 手册（win2003 Server RAW+ASM）目录1 部署条件说明 (3)1.1 本手册适用范围 (3)1.2 软件环境 (3)1.3 硬件环境 (3)2 系统环境准备 (4)3 安装系统群集准备环境 (11)3.1 准备安装包 (12)3.2 安装clusterware (12)4 安装数据库软件 ........................................... 错误！未定义书签。

4.1 安装Oracle 10g DB Soft................................. 错误！未定义书签。

5 VIP 网络配置 ............................................ 错误！未定义书签。

6 执行netca建立Listener................................... 错误！未定义书签。

7 创建集群数据库 ........................................... 错误！未定义书签。

8 常用日常管理命令 ......................................... 错误！未定义书签。

1条件说明1.1 本手册适用范围本手册适用于win2003的oracle双机rac安装1.2 软件环境部署时如果规划好，一般操作都要在同一节点上面处理，防止不同节点处理出现监听以及数据库连接和主机名不一致，颠倒的顺序。

本部署实施手册适用于windows2003 Server平台下，安装Oracle版本为11g的2节点RAC；使用ASM文件系统存储数据文件所需软件包括:win32_11gR1_clusterwarewin32_11gR1_database_10131.3 硬件环境（测试环境）●服务器双网卡●1G以上可用内存●10G以上本地存储空间●共享磁盘阵列10GB2 系统环境准备1.系统采用统一的系统管理员用户名密码登陆如administrator/123456；2.配置系统环境变量：检查TEMP、TMP环境变量所在的磁盘有>1G的足够空间；3.配置相似的主机名，以便操作(例如node1，node2)；4.通过ipconfig /all命令察看网卡的排列顺序，修改排列在第一位置的网卡网络连接名称为“public”，第二位置的网卡网络名称为“private”；5.分别配置public和private网络连接的ip地址，保证两台机器的ip地址在同一网段，并分别使用相同的子网掩码；服务器AOS：win 2003 entprise sp2 ServerName：node1public：192.168.0.10/24 gateway：192.168.0.1private：10.0.0.1/24服务器BOS：win 2003 entprise sp2 ServerName：node2public：192.168.0.11/24 gateway：192.168.0.1private：10.0.0.2/246.编辑%systemroot%\system32\drivers\etc\hosts文件，添加192.168.0.10 node1192.168.0.11 node210.0.0.1 node1-priv10.0.0.2 node2-priv192.168.0.100 node1-vip192.168.0.101 node2-vip7.禁用 Windows Media Sensing for TCP/IP·打开注册表编辑器·HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parame ters·新建DWORD 名_DisableDHCPMediaSense 值为 1当网卡与网络设备没有正常连通时，“媒体感知”功能不会禁用所有的网络协议，如NetBEUI和IPX/SPX协议就不受影响，本机的环路测试也可正常进行。

oracle rac巡检过程一 RAC环境RAC架构，2节点信息节点1SQL> show parameter instanceNAME TYPE VALUE------------------------------------ ----------- -----------------------------------------------active_instance_count integercluster_database_instances integer 2 instance_groups stringinstance_name string RACDB1 instance_number Integer 1 instance_type string RDBMS open_links_per_instance integer 4 parallel_instance_group stringparallel_server_instances integer 2节点2SQL> show parameter instanceNAME TYPE VALUE------------------------------------ ----------- ------------------------------------------active_instance_count integercluster_database_instances integer 2 instance_groups stringinstance_name string RACDB2instance_number integer 2instance_type string RDBMSopen_links_per_instance integer 4parallel_instance_group stringparallel_server_instances integer 2数据库版本SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production操作系统信息节点1[oracle@rac1 ~]$ uname -aLinux rac1 2.6.18-53.el5 #1 SMP Wed Oct 10 16:34:02 EDT 2007 i686 i686 i386 GNU/Linux 节点2[oracle@rac2 ~]$ uname -aLinux rac2 2.6.18-53.el5 #1 SMP Wed Oct 10 16:34:02 EDT 2007 i686 i686 i386 GNU/LinuxRAC所有资源信息[oracle@rac2 ~]$ crs_stat -tName Type Target State Host----------------------------------------------------------------------------------------------ora....B1.inst application ONLINE ONLINE rac1ora....B2.inst application ONLINE ONLINE rac2ora....DB1.srv application ONLINE ONLINE rac2ora.....TAF.cs application ONLINE ONLINE rac2ora.RACDB.db application ONLINE ONLINE rac2ora....SM1.asm application ONLINE ONLINE rac1ora....C1.lsnr application ONLINE ONLINE rac1ora.rac1.gsd application ONLINE ONLINE rac1ora.rac1.ons application ONLINE ONLINE rac1ora.rac1.vip application ONLINE ONLINE rac1ora....SM2.asm application ONLINE ONLINE rac2ora....C2.lsnr application ONLINE ONLINE rac2ora.rac2.gsd application ONLINE ONLINE rac2ora.rac2.ons application ONLINE ONLINE rac2ora.rac2.vip application ONLINE ONLINE rac2二模拟两个节点内联网不通，观察RAC会出现什么现象？给出故障定位的整个过程本小题会模拟RAC的私有网络不通现象，然后定位故障原因，最后排除故障。

Oracle巡检手册第一部分数据库状态监控首先检查oracle的log，在sqlplus中：show parameter background_dump_dest;select * from v$diag_info;可以得到日志路径1:检查oracle 监听lsnrtcl statusPs –ef|grep ora2:检查oracle初始化参数Select * from v$parameter;3:检查oracle实例状态Select instance_name,version,status,database_status from v$instance;select inst_id,instance_name,host_name,VERSION,TO_CHAR(startup_time,'yyyy-mm-dd hh24:mi:ss')startup_time,status,archiver,database_status FROM gv$instance;4:检查后台进程状态：select name,Description From v$BGPROCESS Where Paddr<>'00'5:查看系统全局区SGA信息select * from v$sga;6: 查看SGA各部分占用内存情况：select * from v$sgastat;select request_misses,request_failures from v$shared_pool_reserved;比较好的状态：REQUEST_MISSES REQUEST_FAILURES为0或者接近0REQUEST_MISSES REQUEST_FAILURES-------------- ----------------007：查看系统SCN号select (select dbms_flashback.get_system_change_number from dual)scn,current_scn,scn_to_timestamp(current_scn)from v$database;8：检查数据库状态：select name,log_mode,open_mode,platform_name from v$database;select inst_id,dbid,name,to_char(created,'yyyy-mm-dd hh24:mi:ss')created,log_mode,to_char(version_time,'yyyy-mm-ddhh24:mi:ss')version_time,open_mode from gv$database;第二部分：数据库空间监控检查表空间使用率select A.tablespace_name, (1 - (A.total) / B.total) * 100 used_percentfrom (select tablespace_name, sum(bytes) totalfrom dba_free_spacegroup by tablespace_name) A,(select tablespace_name, sum(bytes) totalfrom dba_data_filesgroup by tablespace_name) Bwhere A.tablespace_name = B.tablespace_name;检查system表空间内的内容select distinct (owner)from dba_tableswhere tablespace_name = 'SYSTEM'and owner != 'SYS'and owner != 'SYSTEM'unionselect distinct (owner)from dba_indexeswhere tablespace_name = 'SYSTEM'and owner != 'SYS'and owner != 'SYSTEM';输出：no rows selected分析：如果有记录返回，则表明system表空间内存在一些非system和sys用户的对象。

oracle 11g R2 RAC健康检查1.检查集群状态[grid@rac1 ~]$ iduid=501(grid) gid=501(oinstall) groups=501(oinstall),504(asmadmin),506(asmdba),507(asmoper) [grid@rac1 ~]$ crsctl check clusterCRS-4537: Cluster Ready Services is onlineCRS-4529: Cluster Synchronization Services is onlineCRS-4533: Event Manager is online[grid@rac1 ~]$2.检查rac下实例的状态[grid@rac1 ~]$ srvctl config database1.检查集群状态[grid@rac1 ~]$ iduid=501(grid) gid=501(oinstall) groups=501(oinstall),504(asmadmin),506(asmdba),507(asmoper) [grid@rac1 ~]$ crsctl check clusterCRS-4537: Cluster Ready Services is onlineCRS-4529: Cluster Synchronization Services is onlineCRS-4533: Event Manager is online[grid@rac1 ~]$2.检查rac下实例的状态[grid@rac1 ~]$ srvctl config databaseorcl[grid@rac1 ~]$ srvctl status database -d orclInstance orcl1 is running on node rac1Instance orcl2 is running on node rac2[grid@rac1 ~]$3.检查rac下某个实例状态[grid@rac1 ~]$ srvctl status instance -d orcl -i orcl1Instance orcl1 is running on node rac1[grid@rac1 ~]$ srvctl status instance -d orcl -i orcl2Instance orcl2 is running on node rac2[grid@rac1 ~]$4.检查rac各个节点应用程序状态（vip,network,gsd,ons,eons）[grid@rac1 ~]$ srvctl status nodeappsVIP rac1-vip is enabledVIP rac1-vip is running on node: rac1VIP rac2-vip is enabledVIP rac2-vip is running on node: rac2Network is enabledNetwork is running on node: rac1Network is running on node: rac2GSD is enabledGSD is running on node: rac1GSD is running on node: rac2ONS is enabledONS daemon is running on node: rac1ONS daemon is running on node: rac2eONS is enabledeONS daemon is running on node: rac1eONS daemon is running on node: rac2[grid@rac1 ~]$5.检查rac下的数据库配置[grid@rac1 ~]$ srvctl config database -d orcl -a Database unique name: orclDatabase name: orclOracle home: /u01/app/oracle/product/11.2.0/db_1 Oracle user: oracleSpfile: +DATA/orcl/spfileorcl.oraDomain:Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICServer pools: orclDatabase instances: orcl1,orcl2Disk Groups: DATA,BACKServices:Database is enabledDatabase is administrator managed[grid@rac1 ~]$6.检查rac下的ASM状态以及ASM配置[grid@rac1 ~]$ srvctl status asmASM is running on rac1,rac2[grid@rac1 ~]$ srvctl status asm -aASM is running on rac1,rac2ASM is enabled.[grid@rac1 ~]$ srvctl config asm -aASM home: /u01/app/grid/11.2.0/gridASM listener: LISTENERASM is enabled.[grid@rac1 ~]$7.检查rac下的TNS监听器状态以及配置[grid@rac1 ~]$ srvctl status listenerListener LISTENER is enabledListener LISTENER is running on node(s): rac1,rac2 [grid@rac1 ~]$ srvctl config listener -aName: LISTENERNetwork: 1, Owner: gridHome: <CRS home>/u01/app/grid/11.2.0/grid on node(s) rac2,rac1End points: TCP:1521[grid@rac1 ~]$8.检查rac下的SCAN状态以及配置[grid@rac1 ~]$ srvctl status scanSCAN VIP scan1 is enabledSCAN VIP scan1 is running on node rac1[grid@rac1 ~]$ srvctl config scanSCAN name: rac-cluster-scan, Network: 1/192.9.100.0/255.255.255.0/eth0 SCAN VIP name: scan1, IP: /rac-cluster-scan/192.9.100.36[grid@rac1 ~]$#SCAN192.9.100.36 rac-cluster-scan9.检查rac下的VIP各个节点的状态以及配置[grid@rac1 ~]$ srvctl status vip -n rac1VIP rac1-vip is enabledVIP rac1-vip is running on node: rac1[grid@rac1 ~]$ srvctl status vip -n rac2VIP rac2-vip is enabledVIP rac2-vip is running on node: rac2[grid@rac1 ~]$ srvctl config vip -n rac1VIP exists.:rac1VIP exists.: /rac1-vip/192.9.100.33/255.255.255.0/eth0[grid@rac1 ~]$ srvctl config vip -n rac2VIP exists.:rac2VIP exists.: /rac2-vip/192.9.100.35/255.255.255.0/eth0[grid@rac1 ~]$#VIP192.9.100.33 rac1-vip192.9.100.35 rac2-vip10.检查rac下各个节点应用程序配置情况 (VIP、GSD、ONS、监听器）[grid@rac1 ~]$ srvctl config nodeapps -a -g -s -l-l option has been deprecated and will be ignored.VIP exists.:rac1VIP exists.: /rac1-vip/192.9.100.33/255.255.255.0/eth0VIP exists.:rac2VIP exists.: /rac2-vip/192.9.100.35/255.255.255.0/eth0GSD exists.ONS daemon exists. Local port 6100, remote port 6200Name: LISTENERNetwork: 1, Owner: gridHome: <CRS home>/u01/app/grid/11.2.0/grid on node(s) rac2,rac1End points: TCP:152111.检查rac下的各个节点间的时钟同步情况[grid@rac1 ~]$ cluvfy comp clocksync -verboseVerifying Clock Synchronization across the cluster nodesChecking if Clusterware is installed on all nodes...Check of Clusterware install passedChecking if CTSS Resource is running on all nodes...Check: CTSS Resource running on all nodesNode N ame Status------------------------------------ ------------------------rac1 passedResult: CTSS resource check passedQuerying CTSS for time offset on all nodes...Result: Query of CTSS for time offset passedCheck CTSS state started...Check: CTSS stateNode N ame State------------------------------------ ------------------------rac1 ObserverCTSS is in Observer state. Switching over to clock synchronization checks using NTP Starting Clock synchronization checks using Network Time Protocol(NTP)...NTP Configuration file check started...The NTP configuration file "/etc/ntp.conf" is available on all nodesNTP Configuration file check passedChecking daemon liveness...Check: Liveness for "ntpd"Node N ame Running?------------------------------------ ------------------------rac1 yesResult: Liveness check passed for "ntpd"Checking NTP daemon command line for slewing option "-x"Check: NTP daemon command lineNode N ame Slewing O ption S et?------------------------------------ ------------------------rac1 yesResult:NTP daemon slewing option check passedChecking NTP daemon's boot time configuration, in file "/etc/sysconfig/ntpd", for slewing option "-x"Check: NTP daemon's boot time configurationNode N ame Slewing O ption S et?------------------------------------ ------------------------rac1 yesResult:NTP daemon's boot time configuration check for slewing option passedNTP common Time Server Check started...PRVF-5410 : Check of common NTP Time Server failedPRVF-5416 : Query of NTP daemon failed on all nodesResult: Clock synchronization check using Network Time Protocol(NTP) passedOracle Cluster Time Synchronization Services check passedVerification of Clock Synchronization across the cluster nodes was successful.[grid@rac1 ~]$12.检查rac下的所有正在运行的实例情况col status for a15;col database_status for a15;col instance_name for a15;col host_name for a15;col active_state for a15;SQL> set linesize 200;SQL> select instance_number, instance_name, parallel , status , database_status, active_state, host_name from gv$instance order by instance_number;INSTANCE_NUMBER INSTANCE_NAME PARALLEL STATUS DATABASE_STATUS ACTIVE_STATE HOST_NAME--------------- --------------- --------- --------------- --------------- --------------- ---------------1o rcl1YES OPEN ACTIVE NORMAL rac12o rcl2YES OPEN ACTIVE NORMAL rac2SQL> select * from v$active_instances;INST_NUMBER INST_NAME----------- --------------------------------------------------------------------1r ac1:orcl12r ac2:orcl213.检查数据文件及它们所在的 ASM磁盘组select name from v$datafileunionselect member from v$logfileunionselect name from v$controlfileunionselect name from v$tempfile;SQL> select name from v$datafile2 union3 select member from v$logfile4 union5 select name from v$controlfile6 union7 select name from v$tempfile;NAME--------------------------------------------------------------------------------+BACK/orcl/controlfile/current.299.768402009+BACK/orcl/onlinelog/group_1.298.768402013+BACK/orcl/onlinelog/group_2.297.768402015+BACK/orcl/onlinelog/group_3.296.768402261+BACK/orcl/onlinelog/group_4.295.768402263+DATA/orcl/controlfile/current.280.768402009+DATA/orcl/datafile/example.385.768402021........+DATA/orcl/tempfile/sms_db_temp.400.768403227+DATA/orcl/tempfile/temp.386.76840201914.检查ASM磁盘情况QL> select path from v$asm_disk;PATH--------------------------------------------------------------------------------/dev/oracleasm/disks/BACK_VOL1/dev/oracleasm/disks/DATA_VOL1/dev/oracleasm/disks/OCR_VOL3/dev/oracleasm/disks/OCR_VOL2/dev/oracleasm/disks/OCR_VOL1SQL> col path for a50;SQL> select path,disk_number,name from v$asm_disk;PATH DISK_NUMBER N AME-------------------------------------------------- ----------- -----------------/dev/oracleasm/disks/BACK_VOL10B ACK_0000/dev/oracleasm/disks/DATA_VOL10D ATA_0000/dev/oracleasm/disks/OCR_VOL32O CR_0002/dev/oracleasm/disks/OCR_VOL21O CR_0001/dev/oracleasm/disks/OCR_VOL10O CR_0000SQL> select group_number,name,offline_disks from v$asm_diskgroup;GROUP_NUMBER N AME OFFLINE_DISKS------------ ------------------------------ -------------1O CR02D ATA03B ACK0SQL> select group_number,name,total_mb,free_mb from v$asm_diskgroup;GROUP_NUMBER N AME TOTAL_MB FREE_MB------------ ------------------------------ ---------- ----------1O CR2859 19332D ATA568739 5568103B ACK571600 508953SQL>SQL> SELECT AS diskgroup, , t.stripe, t.redundancy, t.primary_region, t.mirror_region2 FROM V$ASM_DISKGROUP dg, V$ASM_TEMPLATE t3 WHERE dg.group_number = t.group_number ORDER BY ;rows will be truncatedDISKGROUP NAME STRIPE R EDUND PRIM------------------------------ ------------------------------ ------ ------ ----OCR ARCHIVELOG COARSE M IRROR COLDBACK ARCHIVELOG COARSE U NPROT COLDDATA ARCHIVELOG COARSE U NPROT COLDBACK ASMPARAMETERBAKFILE COARSE U NPROT COLDOCR ASMPARAMETERBAKFILE COARSE M IRROR COLDDATA ASMPARAMETERBAKFILE COARSE U NPROT COLDOCR ASMPARAMETERFILE COARSE M IRROR COLDBACK ASMPARAMETERFILE COARSE U NPROT COLDDATA ASMPARAMETERFILE COARSE U NPROT COLDOCR ASM_STALE COARSE H IGH COLDBACK ASM_STALE COARSE U NPROT COLDDISKGROUP NAME STRIPE R EDUND PRIM------------------------------ ------------------------------ ------ ------ ----DATA ASM_STALE COARSE U NPROT COLDOCR AUTOBACKUP COARSE M IRROR COLDDATA AUTOBACKUP COARSE U NPROT COLDBACK AUTOBACKUP COARSE U NPROT COLDBACK BACKUPSET COARSE U NPROT COLDOCR BACKUPSET COARSE M IRROR COLDDATA BACKUPSET COARSE U NPROT COLDOCR CHANGETRACKING COARSE M IRROR COLDBACK CHANGETRACKING COARSE U NPROT COLDDATA CHANGETRACKING COARSE U NPROT COLDBACK CONTROLFILE FINE UNPROT COLDDISKGROUP NAME STRIPE R EDUND PRIM------------------------------ ------------------------------ ------ ------ ----OCR CONTROLFILE FINE HIGH COLDDATA CONTROLFILE FINE UNPROT COLDBACK DATAFILE COARSE U NPROT COLDDATA DATAFILE COARSE U NPROT COLDOCR DATAFILE COARSE M IRROR COLDBACK DATAGUARDCONFIG COARSE U NPROT COLDOCR DATAGUARDCONFIG COARSE M IRROR COLDDATA DATAGUARDCONFIG COARSE U NPROT COLDOCR DUMPSET COARSE M IRROR COLDBACK DUMPSET COARSE U NPROT COLDDATA DUMPSET COARSE U NPROT COLDDISKGROUP NAME STRIPE R EDUND PRIM------------------------------ ------------------------------ ------ ------ ----OCR FLASHBACK COARSE M IRROR COLDDATA FLASHBACK COARSE U NPROT COLDBACK FLASHBACK COARSE U NPROT COLDOCR FLASHFILE COARSE M IRROR COLDDATA FLASHFILE COARSE U NPROT COLDBACK FLASHFILE COARSE U NPROT COLDOCR OCRBACKUP COARSE M IRROR COLDBACK OCRBACKUP COARSE U NPROT COLDDATA OCRBACKUP COARSE U NPROT COLDBACK OCRFILE COARSE U NPROT COLDOCR OCRFILE COARSE M IRROR COLDDISKGROUP NAME STRIPE R EDUND PRIM------------------------------ ------------------------------ ------ ------ ----DATA OCRFILE COARSE U NPROT COLDOCR ONLINELOG COARSE M IRROR COLDBACK ONLINELOG COARSE U NPROT COLDDATA ONLINELOG COARSE U NPROT COLDBACK PARAMETERFILE COARSE U NPROT COLDOCR PARAMETERFILE COARSE M IRROR COLDDATA PARAMETERFILE COARSE U NPROT COLDOCR TEMPFILE COARSE M IRROR COLDDATA TEMPFILE COARSE U NPROT COLDBACK TEMPFILE COARSE U NPROT COLDOCR XTRANSPORT COARSE M IRROR COLDDISKGROUP NAME STRIPE R EDUND PRIM------------------------------ ------------------------------ ------ ------ ----DATA XTRANSPORT COARSE U NPROT COLDBACK XTRANSPORT COARSE U NPROT COLD57 rows selected.SQL> set linesize 300;SQL> select name, path, mode_status, state, disk_number from v$asm_disk;NAME PATH MODE_ST S TATE DISK_NUMBER------------------------------ -------------------------------------------------- ------- -------- -----------BACK_0000 /dev/oracleasm/disks/BACK_VOL1 ONLINE NORMAL 0DATA_0000 /dev/oracleasm/disks/DATA_VOL1 ONLINE NORMAL 0OCR_0002 /dev/oracleasm/disks/OCR_VOL3 ONLINE NORMAL 2OCR_0001 /dev/oracleasm/disks/OCR_VOL2 ONLINE NORMAL 1OCR_0000 /dev/oracleasm/disks/OCR_VOL1 ONLINE NORMAL 0SQL> select name, state from v$asm_diskgroup;NAME STATE------------------------------ -----------OCR MOUNTEDDATA MOUNTEDBACK MOUNTED15.检查rac状态[grid@rac1 ~]$ crs_stat -t -vName Type R/RA F/FT Target State Host----------------------------------------------------------------------ora.BACK.dg ora....up.type0/5 0/ ONLINE ONLINE rac1ora.DATA.dg ora....up.type0/5 0/ ONLINE ONLINE rac1ora....ER.lsnr o ra....er.type0/5 0/ONLINE ONLINE rac1ora....N1.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac1ora.OCR.dg ora....up.type0/5 0/ ONLINE ONLINE rac1ora.asm ora.asm.type 0/5 0/ ONLINE ONLINE rac1ora.eons ora.eons.type 0/3 0/ ONLINE ONLINE rac1ora.gsd ora.gsd.type 0/5 0/ ONLINE ONLINE rac1work o ra....rk.type0/5 0/ONLINE ONLINE rac1ora.oc4j ora.oc4j.type 0/5 0/0 ONLINE ONLINE rac2ora.ons ora.ons.type 0/3 0/ ONLINE ONLINE rac1 ora.orcl.db ora....se.type0/2 0/1 ONLINE ONLINE rac1 ora....SM1.asm a pplication 0/5 0/0 ONLINE ONLINE rac1 ora....C1.lsnr a pplication 0/5 0/0 ONLINE ONLINE rac1 ora.rac1.gsd application 0/5 0/0 ONLINE ONLINE rac1 ora.rac1.ons application 0/3 0/0 ONLINE ONLINE rac1 ora.rac1.vip ora....t1.type0/0 0/0 ONLINE ONLINE rac1 ora....SM2.asm a pplication 0/5 0/0 ONLINE ONLINE rac2 ora....C2.lsnr a pplication 0/5 0/0 ONLINE ONLINE rac2 ora.rac2.gsd application 0/5 0/0 ONLINE ONLINE rac2 ora.rac2.ons application 0/3 0/0 ONLINE ONLINE rac2 ora.rac2.vip ora....t1.type0/0 1/0 ONLINE ONLINE rac2 ora....ry.acfs o ra....fs.type0/5 0/ONLINE ONLINE rac1 ora.scan1.vip ora....ip.type0/0 0/0 ONLINE ONLINE rac1展开全文。

★Oracle数据库巡检操作内涵Oracle数据库巡检操作内涵吴汉耿资历荣誉履历曾任医保办公室主任、副主任、信息科科长、副科长、后勤科副科长等。

现为医保办公室副主任，具备高级岗位胜任力，取得中国医院协会医院医疗保险管理专业委员会合中国国家人事人才培训网联合签发的《从业人员培训合格证书》。

荣誉汕头市无偿献血先进个人，广东省无偿献血奉献奖铜奖获得者，国家无偿献血奉献奖铜奖获得者；广东省卫生信息化卓越青年工程师；广东省医学信息优秀管理者。

学术情况论文发表论文16篇，其中获得二等奖3篇，三等奖2篇，成果奖1篇，汕头科协三等奖1篇学者库编号PCNI:0000001 00834855X著作《思考的魅力》、《儒衢缵绪》、《中西结合·方剂条例》和《刺腧条辨》著作权《中西结合·方剂条例》登记号：国作登字-2020-A-00009537，《刺腧条辨》登记号：国作登字-2022-A-10116949,获中国版权保护中心认证，受《中华人民共和国著作权法》保护。

学术兼职汕头市汕头市计算机学会理事广东省医院协会信息专委会委员并潮汕分委会常务委员广东省广东省医院协会医院品管圈管理专业委员会委员广东省医院协会医院医疗保险管理专业委员会委员广东省卫生经济学会信息专委会常务委员广东省中医药学会会员、广东省中西医结合学会会员国家级中国中西医结合学会会员中国医药新闻信息协会智慧医院与后勤安全信息分会理事《中华现代医院管理杂志》常务编委一、Oracle数据库巡检概述目录二、Oracle数据库体系架构介绍三、数据库性能优化概述四、案例经验分享数据库系统的重要性数据是血液，数据库系统是心脏应用程序是大脑网络是血管、神经硬件是肌肉和骨骼数据库巡检概述启动、关闭数据库查看告警日志文件查看OS日志OS文件系统表空间管理检查备份与容灾性能监控启动数据库Linux/AIX: #su–oracle$exportORACLE_SID=ora11g$lsnrctlstart$sqlplus‘/assysdba’SQL>star tupWindows:-控制面板\系统和安全\管理工具?服务?OracleOraDb11g_home1TNSListener?启动OracleServiceORA11G?启动启动数据库（续）Windows 注册表：HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraDb11g_home1注意各个参数以及数值的名称格式之间的关系启动数据库（续）如果ORA_ORA11G_AUTOSTART=falsecmd.exe:sqlplus/assysdbaSQL> startup关闭数据库Linux/AIX: #su–oracle$exportORACLE_SID=ora11g$lsnrctlstop$sqlplus‘/assysdba’SQL>shutdownimmediate Windows:-控制面板\系统和安全\管理工具?服务?OracleOraDb11g_home1TNSListener?停止OracleServiceORA11G?停止关闭数据库（续）cmd.exe:sqlplus/assysdbaSQL>shutdownimmediate如果不停止后台服务，内存是不会释放的。

一系统网络资源1.1网络分配：主机名公网(public) 专网(private) 虚拟网(vip)Rac1 IP IP IPRac2 IP IP IP1.2 硬件资源Cpu ：Intel(R) Xeon(TM) MP CPU 3.16GHz*2内存：1G硬盘：20G共享磁盘：6G1.3 操作系统安装1.3.1 操作系统版本：Red Hat Enterprise Linux AS release 4 (Nahant Update 4) 1.3.2 系统分区分区信息分区类型分区大小/boot ext3 100M/ ext3 10240MSwap swap 2048/oracle ext3 10240M/opt ext3 5120M/tmp ext3 1024M1.3.3 系统语言简体中文，ENGLISH（USA）1.3.4 安装模式完全安装二数据安装资源2.1 数据库安装包2.1.1 ORACLE 11G软件包：下载地址：Oracle Database 11g Release 1 (11.1.0.6.0) for Linux x86Oracle Database 11g Release 1 Client (11.1.0.6.0) for Linux x86Oracle Database 11g Examples (formerly Companion)Oracle Clusterware Release 1 (11.1.0.6.0) for Linux x86Oracle Database Gateways 11g Release 1 (11.1.0.6.0) for Linux x862.1.2 ORACLE 11G-ASMlib工具包：下载地址：/technology/software/tech/linux/asmlib/rhel4.htmloracleasm-2.6.9-42.ELsmp-2.0.3-1.i686.rpmoracleasm-support-2.0.3-1.i386.rpmoracleasmlib-2.0.2-1.i386.rpm2.1.3 系统补丁的下载下载地址：/projects/compat-oracle/files/RedHat/compat-libcwait-2.1-1.i386.rpmcompat-oracle-rhel4-1.0-5.i386.rpm三系统安装配置3.1 系统参数配置3.1.1 关闭没必要的服务chkconfig --level 35 autofs offchkconfig --level 35 acpid offchkconfig –level 35 sendmail offchkconfig –level 35 cups-config-daemon offchkconfig –level 35 cpus offchkconfig –level 35 xfs offchkconfig –level 35 lm_sensors offchkconfig –level gpm offchkconfig –level openibd offchkconfig –level iiim offchkconfig –level pcmcia offchkconfig –level cpuspeed offchkconfig –level nfslock offchkconfig –level ip6tables offchkconfig –level rpcidmapd offchkconfig –level apmd offchkconfig –level sendmail offchkconfig –level arptables_jf offchkconifg –level microcode_ctl offchkconfig –level rpcgssd off3.1.2设置主机表vi /etc/hosts127.0.0.1 localhost.localdomain localhost IP rac1IP racp1IP racv1IP rac2IP racp2IP racv23.1.3 修改内核参数vi /etc/sysctl.confk ernel.shmmax = 536870912kernel.shmmni = 4096kernel.shmall = 2097152kernel.sem = 250 32000 100 128fs.file-max = 65536net.ipv4.ip_local_port_range = 1024 65000net.core.rmem_default = 4194304net.core.rmem_max = 4194304net.core.wmem_default = 4194304net.core.wmem_max = 4194304kernel.panic = 603.1.4设置oracle登录使用资源数vi /etc/security/limits.conforacle soft nofile 65536oracle hard nofile 65536oracle soft nproc 16384oracle hard nproc 16384vi /etc/pam.d/login –末行加入以下session required pam_limits.so multiple open3.1.5建立hangcheck模块加载检测是否安装了hangcheck模块[root@rac1]# find /lib/modules -name "hangcheck-timer.ko"/lib/modules/2.6.9-42.ELsmp/kernel/drivers/char/hangcheck-timer.ko设置模块为自动加载su –echo "options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180" >> /etc/modprobe.conf 或者echo " /sbin/modprobe hangcheck-timer hangcheck_tick=30 hangcheck_margin=180" >> /etc/rc.local 检测模块是否运行su -modprobe hangcheck-timergrep Hangcheck /var/log/messages | tail -23.1.6 禁用selinuxgetseboolgetsebool: SELinux is disabledvi /etc/selinux/configSELINUX=disabled3.1.7 添加oracle用户和用户组mkdir –p /oracle/oraclegroupadd –g 502 dbagroupadd –g 501 oinstalluseradd –g 501 –G 502 –d /oracle/oracle oraclepasswd oraclechown -R oracle.oinstall /oracle/oracle3.1.8 修改主机名vi /etc/sysconfig/networkHOSTNAME=rac1# hostname rac1Rac 2 上同样执行3.2 设置oracle用户vi .bash_profileumask 022export ORACLE_BASE=/oracle/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/dbexport ORA_CRS_HOME=$ORACLE_BASE/product/10.2.0/crsexport ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin #export ORACLE_SID=rac2export ORACLE_SID=rac1export NLS_LANG=AMERICAN_AMERICA.zhs16gbkexport NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/binexport PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/binexport PATH=${PATH}:$ORACLE_BASE/common/oracle/binexport ORACLE_TERM=xtermexport TNS_ADMIN=$ORACLE_HOME/network/adminexport ORA_NLS10=$ORACLE_HOME/nls/dataexport LD_LIBRARY_PATH=$ORACLE_HOME/libexport LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/libexport LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/libexport LD_ASSUME_KERNEL=2.4.19 # for RHEL AS 4export CLASSPATH=$ORACLE_HOME/JREexport CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlibexport CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlibexport CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlibexport THREADS_FLAG=nativeexport TEMP=/tmpexport TMPDIR=/tmpif [ $USER = "oracle" ]; thenif [ $SHELL = "/bin/ksh" ]; thenulimit -p 16384ulimit -n 65536elseulimit -u 16384 -n 65536fifi3.3 配置ASM服务3.3.1安装asm包rpm -Uvh oracleasm-2.6.9-42.ELsmp-2.0.3-1.i686.rpm oracleasmlib-2.0.2-1.i386.rpm oracleasm-support-2.0.3-1.i386.rpm安装asm包一定要和系统的内核相对应3.3.2配置asm服务cd /etc/init.d/[root@localhost init.d]# ./oracleasm configureConfiguring the Oracle ASM library driver.This will configure the on-boot properties of the Oracle ASM librarydriver. The following questions will determine whether the driver isloaded on boot and what permissions it will have. The current valueswill be shown in brackets ('[]'). Hitting <ENTER> without typing ananswer will keep that current value. Ctrl-C will abort.Default user to own the driver interface []: oracleDefault group to own the driver interface []: dbaStart Oracle ASM library driver on boot (y/n) [n]: yFix permissions of Oracle ASM disks on boot (y/n) [y]: yWriting Oracle ASM library driver configuration: [ OK ]Creating /dev/oracleasm mount point: [ OK ]Loading module "oracleasm": [ OK ]Mounting ASMlib driver filesystem: [ OK ]Scanning system for ASM disks: [ OK ]3.4 建立ssh等效性（在oracle用户操作）3.4.1 建立相关目录生成密钥和公钥su - oraclemkdir -p ~/.sshchmod 755 ~/.ssh[oracle@rac1 ~]$ /usr/bin/ssh-keygen -t rsaGenerating public/private rsa key pair.Enter file in which to save the key (/oracle/oracle//.ssh/id_rsa):Enter passphrase (empty for no passphrase):Enter same passphrase again:Your identification has been saved in /oracle/oracle//.ssh/id_rsa.Your public key has been saved in /oracle/oracle//.ssh/id_rsa.pub.The key fingerprint is:16:2b:45:27:24:ee:97:46:5e:72:43:f1:9f:df:4b:dd oracle@rac1[oracle@rac1 ~]$ /usr/bin/ssh-keygen -t dsaGenerating public/private dsa key pair.Enter file in which to save the key (/oracle/oracle//.ssh/id_dsa):Enter passphrase (empty for no passphrase):Enter same passphrase again:Your identification has been saved in /oracle/oracle//.ssh/id_dsa.Your public key has been saved in /oracle/oracle//.ssh/id_dsa.pub.The key fingerprint is:0a:d5:e7:6a:a1:11:de:e4:72:74:fc:5f:b2:63:5b:6b oracle@rac1同等在RAC2上建立公钥和私钥3.4.2 建立等效密钥在第一个主机上，以oracle 用户身份登录cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keyscat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keyscp ~/.ssh/authorized_keys ~/.ssh/rac1.authorized_keys第二台主机上，以oracle用户登录cat ~/.ssh/id_ >> ~/.ssh/authorized_keyscat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keyscp ~/.ssh/authorized_keys ~/.ssh/rac2.authorized_keys相互交换公钥scp ~/.ssh/rac1.authorized_keys oracle@IP:scp ~/.ssh/rac2.authorized_keys oracle@IP:RAC2 上执行：cat rac1.authorized_keys >> ~/.ssh/authorized_keysRAC1上执行：cat rac2.authorized_keys >> ~/.ssh/authorized_keys建立等效性（每台机器都执行）ssh rac1ssh rac2ssh racp1ssh racp2exec /usr/bin/ssh-agent $SHELL/usr/bin/ssh-add四配置共享磁盘4.1 建立ISCSI的共享磁盘4.1.1 编译iscsi-target的源包mkdir –p /usr/src/iscsi-targetwget /iscsi/RPMS/iscsitarget-0.4.12-6.src.rpmrpmbuild --rebuild iscsitarget-0.4.12-6.src.rpmrpm -Uvh iscsitarget-kernel-smp-0.4.12-6_2.6.9_42.EL.i386.rpm iscsitarget-0.4.12-6.i386.rpm 4.1.2 建立分区fdisk /dev/sda生成：/dev/sda8fdisk /dev/sdb生成：/dev/sdb14.1.3 配置共享盘I SCSI服务端配置：vi /etc/ietd.conf注释掉所有的其他Target .digicola:storage.lun1IncomingUser gfs secretsecretOutgoingUserLun 0 Path=/dev/sdb1,Type=fileioAlias iDISK0#MaxConnections 6Target .digicola:storage.lun2IncomingUser gfs secretsecretOutgoingUserLun 0 Path=/dev/sda8,Type=fileioAlias iDISK1#MaxConnections 6启动服务：service iscsi-target restart加入开机启动：chkconfig –level 2345 iscsi-target onIscsi客户端配置：vi /etc/iscsi.confDiscoveryAddress=192.168.1.156OutgoingUserName=gfsOutgoingPassword=secretsecretLoginTimeout=15DiscoveryAddress=192.168.1.15启动服务：service iscis restart加入开机启动：chkconfig –level 2345 iscsi on检查：iscsi共享磁盘：[root@rac1 ~]# iscsi-ls*******************************************************************************SFNet iSCSI Driver Version ...4:0.1.11-3(02-May-2006)******************************************************************************* TARGET NAME : .digicola:storage.lun2TARGET ALIAS :HOST ID : 1BUS ID : 0TARGET ID : 0TARGET ADDRESS : 192.168.1.156:3260,1SESSION STATUS : ESTABLISHED AT Thu Oct 25 23:14:52 CST 2007SESSION ID : ISID 00023d000001 TSIH 400注：在从启系统时要注意iscsi-target服务和iscsi服务的启动顺序。

Oracle数据库巡检命令手册点击上方卡片关注前言如果给你一个全新的O ra c le单机数据库环境，作为D B A，您需要关注哪些点？本文仅讨论L inux主机~注意：首先申明本文所述并非标准答案，只是个人的一些见解，欢迎 大家补充完善~一、⭐ 主机层面⭐1、 主机版本和O racle版本「主机版本：」c a t /e tc/s y s te m-re le a s e c a t /e tc/red ha t-re le a s e「O ra c le版本和补丁版本：」s q lp lus-v e rs io no p a tc h ls p a tc he s2、 主机硬件资源包括C P U负载，物理内存和磁盘使用。

「C P U负载和内存：」to p f re e-m⚠ 需要注意主机的C P U负载和物理内存使用是否异常，S w a p是否被过多使用。

「磁盘使用情况：」ls b lkf d is k -ld f-T h⚠ 显而易见，需要关注磁盘使用情况，是否存在使用率过高。

3、 计划任务 cron t ab一般计划任务会布置一些备份策略或者归档删除的策略，我们可以通过c ro nta b来查看：c ro nta b-l4、 检查H ost s 文件和网络配置c a t /e tc/ho s ts ip ad d rnmc li c o nne c tio n s ho w5、 检查系统参数文件c a t /e tc/s y s c tl.c o nf⚠ 需注意是否有设置非常规参数。

6、 检查 rc.local 文件rc.lo c a l文件用于配置开机自启动脚本，一般会设置关闭透明大页或者O ra c le数据库开机自启。

c a t /e tc/rc.lo c a l7、 环境变量配置查看环境变量配置，进一步熟悉环境。

c a t ~/.b a s h_p ro f ile c a t /ho me/o ra c le/.b a s h_p ro f ile8、 检查系统服务s y s te mc tl s ta tus f ire w a lld.s e rv ic e g e te nf o rc e c a t /p ro c/c md line c a t /e tc/s y s c o nfig/ne tw o rk二、 数据库层面1、 查看数据库实例和监听p s-e f|g re p s mo ns u -o ra c le ls nrc tl s ta tus2、 数据库表空间使用s q lp lus/ a s s y s d b a c o l TA B L E S P A C E_N A M E f o r a20s e le c t tb s_us e d_inf o.ta ble s p a c e_na me,tb s_us e d_inf o.a llo c_mb,tb s_us e d_inf e d_mb,tb s_us e d_inf o.ma x_mb,tb s_us e d_inf o.f re e_o f_ma x_mb,tb s_us e d_inf e d_o f_ma x|| '%'us e d_o f_ma x_p c t f ro m (s e le c t a.ta b le s p a c e_na me,ro und(a.b y te s_a llo c/ 1024/ 1024) a llo c_mb,ro und((a.b y te s_a llo c-nv l(b.b y te s_f re e,0)) / 1024/ 1024) us e d_mb,ro und((a.b y te s_a llo c-nv l(b.b y te s_f re e,0)) *100/ a.ma x b y te s) us e d_o f_ma x,ro und((a.ma x b y te s-a.b y te s_a llo c+nv l(b.b y te s_f re e,0)) / 1048576) f re e_o f_ma x_mb,ro und(a.ma x b y te s/ 1048576) ma x_mb f ro m (s e le c t f.ta b le s p a c e_na me,s um(f.b y te s) b y te s_a llo c,s um(d e c o d e(f.a uto e x te ns ib le,'Y E S',f.ma x b y te s,'N O', f.b y te s)) ma x b y te sf ro m d b a_d a ta_f ile s fg ro up b y ta b le s p a c e_na me) a,(s e le c t f.ta b le s p a c e_na me,s um(f.b y te s) b y te s_f re e f ro m d b a_f re e_s p a c efg ro up b y ta b le s p a c e_na me) b w he re a.ta b le s p a c e_na me=b.ta b le s p a c e_na me(+)) tb s_us e d_inf o o rd e r b y tb s_us e d_inf e d_o f_ma x de s c;3、 检查RMA N备份情况rma n ta rg e t /lis t b a c kup;s q lp lus/ a s s y s d b a c o l s ta tus f o r a10c o l inp ut_ty p e f o r a20c o l I N P U T_B Y T E S_D I S P L AY f o r a10c o l O U T P U T_B Y T E S_D I S P L AYf o r a10c o l T I M E_TA K E N_D I S P L AY f o r a10s e le c t inp ut_ty p e,s ta tus,to_c ha r(s ta rt_time,'y y y y-mm-d d hh24:mi:s s'),to_c ha r(e nd_time,'y y y y-mm-d d hh24:mi:s s'),inp ut_b y te s_d is p la y,o utp ut_b y te s_d is p la y,time_ta ke n_d is p la y,C O M P R E S S I O N_R AT I Of ro m v$rma n_b a c kup_jo b_d e ta ils w he re s ta rt_time>d a te'2021-07-01'o rd e r b y3d e s c;4、 检查控制文件冗余查看控制文件数量和位置，是否处于多份冗余状态。