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、 检查控制文件冗余查看控制文件数量和位置，是否处于多份冗余状态。

Oracle 10G RAC巡检脚本===========================SRVCTL============================1.列出配置的所有数据库srvctl config database2.显示指定集群数据库的所有服务srvctl config service -d GDTV3.查看所有实例和服务的状态srvctl status database -d GDTV4.查看单个实例的状态srvctl status service -d GDTV -s <service_name>5.特定节点上节点应用程序的状态srvctl status nodeapps -n DBSERVER1srvctl status nodeapps -n DBSERVER26.列出RAC数据库的配置srvctl config database -d GDTV7.显示节点应用程序的配置—（VIP、GSD、ONS、监听器）srvctl config nodeapps -n DBSERVER1 -a -g -s -lsrvctl config nodeapps -n DBSERVER2 -a -g -s -l=========================================进程检查================== 8. Oracle进程检查Ps –ef |grep ora_9. CRS进程检查ps -ef | grep oracm$ps –df |grep d.bin应有：crsd.bin ocssd.bin evmd.bincrsctl check crscrs_stat –tcrs_stat –ls=================alert.log====================================8.查看各个instance的alert.log=====================instance配置参数======================== 9.查看spfile.ora查看profile====================参看监听状态============================== 10.$hostname$export ORACLE_SID=GDTV1$lsnrctl status$hostname$export ORACLE_SID=GDTV2$lsnrctl statuslistener日志检查/u01/app/oracle/product/9.2.0/network/log/listener.log/u01/app/oracle/product/9.2.0/network/log/listener.log================oracm状态检查===================================11.1c rs日志检查ocssd.log</u01/product/Oracle/oracm/log>$tail -20 cm.log======================SQL下检查============================== $hostname$export ORACLE_SID=GDTV1sqlplus "/as sysdba"or sqlplus "/as sysdba"@GDTV1--run on db server as sysdba!!!--collect by lyf 200609set pagesize 1000set linesize 120set echo onCOLUMN name_col_plus_show_param FORMAT a36 HEADING NAMECOLUMN value_col_plus_show_param FORMAT a30 HEADING VALUEcol tablespace_name format a15host hostname1.集群中所有正在运行的实例SELECTinst_id, instance_number inst_no, instance_name inst_name, parallel, status, database_status db_status, active_state state, host_name hostFROM gv$instanceORDER BY inst_id;INST_ID INST_NO INST_NAME PAR STATUS DB_STATUS STATE HOST-------- -------- ---------- --- ------- ------------ --------- -------1 1 orcl1 YES OPEN ACTIVE NORMAL rac12 2 orcl2 YES OPEN ACTIVE NORMAL rac2SELECT 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;2.检查参数show paramter3.检查SGA和PGAshow sgaselect name ,value/1024/1024/1024 from gv$sysstat where name like '%pga%';select name ,value/1024/1024/1024 from v$sysstat where name like '%pga%';4.检查查询服务器的运行模式和数据库安装选项set linesize 200select * from v$option;5.用户检查col temporary_tablespace for a21select username,account_status,default_tablespace,temporary_tablespace,created from dba_users;select ername , a.temporary_tablespace "Temporary Tablespace" , b.contentsfrom dba_users a , dba_tablespaces bwhere a.temporary_tablespace=b.tablespace_nameand b.contents <> 'TEMPORARY';6、控制文件检查col name for a60select * from v$controlfile;7、无效对象检查col OBJECT_NAME for a24SELECT owner , object_name, object_type,status ,LAST_DDL_TIME FROM dba_objects WHERE status like 'INVALID';8、表空间和数据文件检查1）数据文件col file_name for a56set linesize 300select file_id,file_name,tablespace_name,autoextensible from dba_data_files;select count(*) from v$datafile;show parameter db_filesselect name from v$datafileunionselect member from v$logfileunionselect name from v$controlfileunionselect name from v$tempfile；SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$datafile UNION ALL SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$tempfile;2）表空间set linesize 300col tablespace_name for a16SELECT upper(f.tablespace_name) "tablespace_name",d.Tot_grootte_Mb"tablespace(M)",d.Tot_grootte_Mb-f.total_bytes"used(M)",round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) "use%",f.total_bytes"free_space(M)",round(f.total_bytes/d.Tot_grootte_Mb*100,2)"free%",f.max_bytes"max_block(M)"FROM(SELECT tablespace_name,round(SUM(bytes)/(1024*1024),2)t otal_bytes,round(MAX(bytes)/(1024*1024),2)m ax_bytesFROM s ys.dba_free_spaceGROUP B Y t ablespace_name)f,(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_MbFROM sys.dba_data_files d dGROUP BY dd.tablespace_name) dWHERE d.tablespace_name = f.tablespace_nameORDER BY 4 DESC;表空间的空间使用情况SELECT df.tablespace_name, COUNT (*) datafile_count,ROUND (SUM (df.BYTES) / 1048576) size_mb,ROUND (SUM (free.BYTES) / 1048576, 2) free_mb,ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2 ) used_mb,ROUND (MAX (free.maxbytes) / 1048576, 2) maxfree,100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used,ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_freeFROM dba_data_files df,(SELECT tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_name, file_id) freeWHERE df.tablespace_name = free.tablespace_name(+) AND df.file_id = free.file_id(+) GROUP BY df.tablespace_name ORDER BY；表空间可用性检查select tablespace_name,status from dba_tablespaces;临时表空间使用情况和性能检查SELECT tablespace_name, extent_management, block_size, initial_extent, next_extent, min_extents, max_extents, pct_increase FROM dba_tablespaces WHERE CONTENTS = 'TEMPORARY';SELECT username, default_tablespace, temporary_tablespace FROM dba_users;selecttablespace_name,initial_extent,next_extent,max_extents,pct_increase,extent_management,stat us from dba_tablespaces order by extent_management;select tablespace_name,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;表：1、监控表的增长select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='FOUNDER' ORDER BY bytes/1024/1024 desc；2、表和索引分析信息SELECT'table', COUNT (*) FROM dba_tables WHERE last_analyzed IS NOT NULL GROUP BY 'table'UNION ALLSELECT 'index', COUNT (*) FROM dba_indexes WHERE last_analyzed IS NOT NULL GROUP BY 'index';3.未建索引的表SELECT/*+r ule*/owner,s egment_name,s egment_type,t ablespace_name,TRUNC(BYTES/1024/1024,1)s ize_mbFROM d ba_segments tWHERE N OT E XISTS(SELECT'x'FROM d ba_indexes iWHERE t.owner=i.table_ownerAND t.segment_name=i.table_name)AND t.segment_type I N('TABLE','TABLE P ARTITION')AND t.owner N OT I N('SYS','SYSTEM')ORDER B Y5D ESC;升序用ASC9.sort_segment检查select tablespace_name,extent_size db_blocks_per_extent,total_extents,used_extents,free_extents from v$sort_segment;10.数据库总大小select round(sum(space)) all_space_M from(select sum(bytes)/1024/1024 space from dba_data_filesunion allselect nvl(sum(bytes)/1024/1024,0) space from dba_temp_filesunion allselect sum(bytes)/1024/1024 space from v$log);11.检测连接数情况（1）select SW.Sid,ername,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAIT from v$session S,v$session_wait SW where ername is not null and SW.Sid=S.Sidand SW.event not like '%SQL*Net%' order by SW.Wait_Time Desc;（2）select count(*) from v$session;（3）select sid,serial#,username,program,machine,status from v$session;11.回滚段信息1）信息1col segment_name format a20col tablespace_name format a20select segment_name,owner,tablespace_name,dba_rollback_segs.statusfrom dba_rollback_segs,v$Datafile where file_id=file#;2）信息2select segment_name,initial_extent,next_extent,min_extents, owner,dba_rollback_segs.status status,optsizefrom dba_rollback_segs,v$rollstatwhere dba_rollback_segs.segment_id=v$n;3）信息3col Rollback_Name for a16select substr(V$,1,20) "Rollback_Name",substr(V$rollstat.EXTENTS,1,6)"EXTENT",v$rollstat.RSSIZE, v$rollstat.WRITES,substr(v$rollstat.XACTS,1,6)"XACTS",v$rollstat.GETS,substr(v$rollstat.WAITS,1,6)"WAITS",v$rollstat.HWMSIZE, v$rollstat.SHRINKS,substr(v$rollstat.WRAPS,1,6)"WRAPS",substr(v$rollstat.EXTENDS,1,6)"EXTEND",v$rollstat.AVESHRINK,v$rollstat.AVEACTIVEfrom v$rollname, v$rollstatwhere v$N = v$Norder by v$N;4）信息4select Rollback_Name,p.pid O racle_PID,p.spid O S_PID,nvl(ername,'NO T RANSACTION')T ransaction,p.terminal T erminalfrom v$lock l, v$process p, v$rollname rwhere l.addr = p.addr(+)and t runc(l.id1(+)/65536)=nand l.type(+)='TX'and l.lmode(+)=6order by ;5）回滚段的争用情况select name ,waits ,gets ,waits/gets "Ratio" from v$rollstat a ,v$rollname b where n=n; 6）rollback信息select substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#",substr(sys.dba_segments.OWNER,1,8)"Owner",substr(sys.dba_segments.TABLESPACE_NAME,1,17)"Tablespace N ame",substr(sys.dba_segments.SEGMENT_NAME,1,12)"Rollback N ame",substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent",substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts",substr(sys.dba_segments.MIN_EXTENTS,1,5)"MinEx",substr(sys.dba_segments.MAX_EXTENTS,1,5)"MaxEx",substr(sys.dba_segments.PCT_INCREASE,1,5)"%Incr",substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)",substr(sys.dba_segments.EXTENTS,1,6)"Extent#",substr(sys.dba_rollback_segs.STATUS,1,10) "Status"from sys.dba_segments, sys.dba_rollback_segswhere sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and sys.dba_segments.segment_type = 'ROLLBACK'order by sys.dba_rollback_segs.segment_id;12.Redo log信息检查1）Redo Log文件状态col member for a56select f.member "member",f.group#"group",l.bytes/1024/1024 "size",l.statusfrom v$logfile f, v$log lwhere f.group#=l.group#order by f.group#,f.member;2）LogGroup信息SELECT group#, sequence#, bytes, members, status from v$log;3）关于log_bufferselect name,value from v$sysstat where name in('redo entries','redo buffer allocation retries');4）查询LOG大小及频率set linesize 300set pages 100column d1 form a20 heading "Date"column sw_cnt form 99999 heading 'Number|of|Switches'column Mb form 999,999 heading "Redo Size"column redoMbytes form 999,999,9999 heading "Redo Log File Size (Mb)" break on reportcompute sum of sw_cnt on reportcompute sum of Mb on reportvar redoMbytes number;beginselect max(bytes)/1024/1024 into :redoMbytes from v$log;end;/print redoMbytesselect trunc(first_time) d1,c ount(*)s w_cnt,c ount(*)*:redoMbytes M bfrom v$log_historygroup by trunc(first_time)/13.IO情况检查col file_name for a46select file_name,fs.phyrds reads,fs.phywrts writes,(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) readtime,(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) writetimefromv$datafile df,v$filestat fswhere df.file#=fs.file#order by ;select count(*) from v$session;14.命中率相关检查1）Shared Pool Size命中率select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %"from v$librarycache where namespacein ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');2）数据字典命中率select round((1-sum(getmisses)/sum(gets))*100,1) "data dictionary hit ratio %"from v$rowcache;3）锁竞争select substr(,1,25) Name,l.gets,l.misses,100*(l.misses/l.gets)"%R atio(STAY U NDER1%)"from v$latch l, v$latchname lnwhere in ('cache buffers lru chain')and tch# = tch#;4）排序命中率select a.value "Sort(Disk)", b.value "Sort(Memory)",round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)"from v$sysstat a, v$sysstat bwhere = 'sorts (disk)'and = 'sorts (memory)';5）数据缓冲区命中率select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratiofrom v$sysstat phy,v$sysstat cur,v$sysstat conwhere ='physical reads' and ='db block gets' and ='consistent gets'; 6）Miss LRU Hit命中率column "Miss LRU Hit%" format 99.9999999;col name format a40select name, (sleeps/gets) "Miss LRU Hit%"from v$latch where name ='cache buffers lru chain';7）检查内存排序性能select , to_char(value)from v$statname a, v$sysstatwhere a.statistic# = v$sysstat.statistic#and in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)');8）redo log buffer retry ratioselect to_char(r.value/e.value) "redo log buffer retry ratio"from v$sysstat r,v$sysstat ewhere ='redo buffer allocation retries'and ='redo entries';9）wait等待检查select count(*) total_in_wait from v$session_waitwhere event='log buffer space';select event,total_waits,time_waited,average_waitfrom v$system_eventwhere event like '%undo%';select sid,seq#,event,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%'and event not like 'rdbms%';15、查询lock锁SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, typeFROM V$LOCKWHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)ORDER BY id1, request;linux。

Oracle 11g日常操作与维护手册拟制：数据库TMG 日期：20011-2-14 审核：日期：批准：日期：目录第1章文档说明 (5)第2章CRS的管理 (5)2.1 RAC状态检查 (5)2.1.1检查守护进程状态 (5)2.1.2检查资源状态 (5)2.2 手工启动与关闭RAC (6)2.3 OCR的管理 (6)2.4 VOTING DISK的管理 (8)2.5 CSS管理 (9)2.6 管理工具SRVCTL (9)2.6.1管理实例 (9)2.6.2管理监听程序 (9)2.6.3管理ASM (10)2.6.4管理service (10)2.7 修改RAC的IP及VIP (11)2.7.1修改外网IP及心跳IP (11)2.7.2修改VIP (11)2.7.3查看与删除IP (12)第3章ASM的管理 (13)3.1 管理DG (13)3.1.1建立与扩充disk group (13)3.1.2 mount与unmount的命令 (14)3.1.3删除disk group (14)3.1.4增加DISK的total_mb (14)3.1.5 DG的属性－AU大小 (14)3.1.6 DG的属性－离线删除时间 (15)3.1.7 DG的属性－兼容版本 (15)3.1.8向ASM中添加disk的完整步骤 (16)3.2 ASMCMD (17)3.2.1 ASMCMD常用命令 (17)3.2.2复制ASM文件 (18)3.2.3命令lsdg (18)3.2.4元数据备份与恢复 (18)3.3 ASM磁盘头信息备份与恢复 (19)3.4 ASM常用视图 (20)3.4.1视图V$ASM_DISKGROUP (20)3.4.2视图V$ASM_DISK (21)3.5 常用方法 (22)3.5.1如何确定ASM实例的编号 (22)3.5.2查询DG－RAW－磁盘的对应关系 (22)第4章数据库管理 (24)4.1 参数文件管理 (24)4.2 表空间管理 (25)4.2.1表空间自动扩张 (25)4.2.2表空间更名 (26)4.2.3表空间的数据文件更名 (26)4.2.4缺省表空间 (26)4.2.5表空间删除 (27)4.2.6 UNDO表空间 (27)4.2.7 TEMP表空间 (27)4.3 重做日志文件管理 (27)4.3.1增加REDO日志组 (27)4.3.2删除日志组 (28)4.3.3日志切换 (28)4.3.4日志清理 (28)4.3.5重做日志切换次数查询 (28)4.4 归档模式 (29)4.4.1单实例数据库修改为归档模式的方法 (29)4.4.2 RAC数据库修改为归档模式的方法 (29)4.4.3归档路径 (30)4.5 重建控制文件 (31)4.6 内存参数管理 (32)4.6.1 Oracle内存管理发展阶段 (32)4.6.2自动内存管理AMM (32)4.6.3自动共享内存管理ASMM (33)4.6.4自动PGA管理 (33)4.7 其他管理内容 (33)4.7.1数据库版本查看 (33)4.7.2字符集 (34)4.7.3创建密码文件 (34)4.7.4关闭审计功能 (34)4.7.5帐号管理 (34)4.7.6 profile管理 (35)第1章文档说明本文档描述了Oracle11g中常见的维护和管理方法，包括CRS、ASM、数据库等。

Oracle运维手册恒生电子客服总部系统服务部本文所述内容（包括文字和图片），恒生电子股份有限公司（以下简称“恒生”或“恒生公司”）拥有完全独立的唯一版权。

未经恒生公司书面同意或授权，任何单位和个人都不得将其复制、影印或引用。

目录第一章通过SQL*Plus管理Oracle系统 (3)**. 登录oracle系统 (3)**. 退出SQL*Plus (3)**. 在sqlplus下得到帮助信息 (3)第二章Oracle11gR2 RAC日常管理 4**. 监控oracle (4)**. 监控Oracle日志 (4)**. 监控oracle状态 (5)**. 监控表空间使用情况 (6)**. 监控主机 (8)**. 监控CPU (8)**. 使用vmstat 命令监控cpu使用情况 (8)**. 监控主机日志 (9)**. 监控OS文件系统使用情况 (9)**. 启动、关闭数据库 (9)**. 启动数据库 (9)**. 关闭数据库 (11)**. 监听器启动、关闭 (13)**. 表统计信息维护 (14)**. 修改主机系统时间 (15)**. 时间管理原则 (15)**. 时间修改方法 (15)**. 数据库日志收集 (15)**. OS日志 (16)**. DB日志 (16)**. grid日志 (16)**. AWR和ASH报告生成 (16)**. AWR报告生成（同10g） (16)**. ASH报告生成（同10g） (19)**. Recyclebin管理 (23)**. 归档日志的清理 (23)**. 查看回收站中的对象 (23)**. 指定删除某些特定对象 (23)**. 指定删除某表空间的所有回收站对象 (23)**. 清除某用户下的回收站对象 (23)**. 清除所有回收站对象 (23)**. 数据库性能监控 (24)**. 查看系统当前等待事件 (24)**. 通过sid查看sql语句24第一章通过SQL*Plus管理Oracle 系统登录oracle系统以oracle用户登录主机1)以sys用户登录oracle系统[oracle@rac1 ~]$ sqlplus / as sysdba2)其他用户登录oracle系统[oracle@rac1 ~]$ sqlplus hs_user/handsome@jsswdb1退出SQL*PlusSQL>exit在sqlplus下得到帮助信息列出全部SQL命令和SQL*Plus命令SQL> help index列出某个特定的命令的信息SQL>help 命令名或者SQL>? 命令名第二章Oracle11gR2 RAC日常管理监控oracle2.1.1.监控Oracle日志2.1.1.1. DB日志以oracle用户登录该日志文件在每个节点主机的$ORACLE_BASE/diag/rdbms/<dbname>/<sid>/trace目录下，文件名为：alert_XXXX.log （节点1）例如：/u01/app/oracle/diag/rdbms/hsdb/hsdb1/trace/alert_hsdb1.log在出现oracle故障前或故障时，数据库会将一些警告、错误信息写入该文件，交易时应随时监控该文件，及时发现警告、错误信息。

问题简介11g rac 巡检注意crfclust.bdb文件巨大问题环境介绍硬件平台：两台HP580 ，11g rac 数据库，具体版本：11.2.0.4.5问题描述东航期货刘工说他们发现一个奇怪的问题：11g rac 操作系统/u01 目录挂载点，里面除了安装oracle相关软件外，没有放或者安装其它东西，过一段时间莫名其妙的满了/u01 40g 的空间都用完了事件分析我进入/u01 目录发现：/u01/app/11.2.0/grid/crf/db/rac1/crfclust.bdb单个文件大小19G，然后让用户观察一天，用户发现此文件一直在增长，后来我在metalink 上查了一下发现原来是一个bug:下面当时参考文档：Oracle Cluster Health Monitor (CHM) using large amount of space (more than default) (Doc ID 1343105.1)To BottomIn this DocumentSymptomsCauseSolutionReferencesAPPLIES TO:Oracle Server - Enterprise Edition - Version 11.2.0.2 and laterInformation in this document applies to any platform. SYMPTOMSCluster Health Monitor (CHM) files in$GI_HOME/crf/db/<node name? directory are filling up disk space in GI_HOME.The bdb files in $GRID_HOME/crf/db/<node name> are larger than 1GB (default size) and filling up the GI_HOME file system.CAUSEOracle Cluster Health Monitor (CHM) using large amount of space when it is collecting the OS statistics.Check Cluster Health Monitor berkerleydatabase files in $GI_HOME/crf/db/<node name> directorySOLUTIONRemove those large Berkeley database files to free up space by doing the following as root:$GI_HOME/bin/crsctl stop res ora.crf -initcd $GI_HOME/crf/db/<nodename>rm *.bdb$GI_HOME/bin/crsctl start res ora.crf -initPlease note that bdb files get regenerated when CHM (ora.crf) resource is restarted. The files are owned by root, so only root can delete the bdb files. Other than losing the OS statistics that CHM has gathered, deleting bdb files does not have other impact. CHM will start collecting the OS statistics again.Having very large bdb files (greater than 2GB) is likely due to a bug since the default size limits the bdb to 1GB unless the CHM data retention time is increased. One such bug is 10165314.Also, please note that the local bdb file (<hostname>.ldb) may need to be deleted as well. REFERENCES@ BUG:10165314 - CHM/CRF/IPDOS REPOSITORY EXCEEDS 1GB AFTER ADD/REMOVE NODE OR FRESH INSTALLNOTE:1328466.1 - Cluster Health Monitor (CHM) FA小飞 2015/4/15 20:10:39APPLIES TO:Oracle Database - Enterprise Edition - Version11.2.0.1 to 11.2.0.4 [Release 11.2]Information in this document applies to any platform. SYMPTOMS11gR2 GI **mand fails:$ oclumon manage -get reppathCRS-9011-Error manage: Failed to initialize connection to the Cluster Logger ServiceBut "status & target" of resource ora.crf is online on all the node :crsctl stat res ora.crf -initNAME=ora.crfTYPE=ora.crf.typeTARGET=ONLINESTATE=ONLINE on dibarac01The following error is reported in the<GI_HOME>/log/<node>/crflogd/crflogd.log file[ clsdmc][1818209888]Fail to connect(ADDRESS=(PROTOCOL=ipc)(KEY=dibarac01DBG_LOGD)) with status 9[ clsdmt][1826756928]Listening to(ADDRESS=(PROTOCOL=ipc)(KEY=dibarac01DBG_LOGD)) 2013-07-09 16:14:14.404: [ clsdmt][1826756928]PID for the Process [19534], connkey 62013-07-09 16:14:14.404:[ clsdmt][1826756928]Creating PID [19534] file for home /oracle/grid-11.2.0.3 host dibarac01 bin ologgerd to /oracle/grid-11.2.0.3/ologgerd/init/ 2013-07-09 16:14:14.404:[ clsdmt][1826756928]Writing PID [19534] to the file [/oracle/grid-11.2.0.3/ologgerd/init/dibarac01.pid] 2013-07-09 16:14:14.478: [CRFLDREP][1818209888]BDB space utilization will be 2199257282013-07-09 16:14:14.522:[CRFLDREP][1835854144]db_delete: BDB grown beyond user desired limits disabling loggerd2013-07-09 16:14:14.522:[ CRFM][1818209888]crfm_listeninit: couldn't create endp(ret:20), conaddrtcp://192.168.139.1:61021.2013-07-09 16:14:14.522:[ CRFLOGD][1843370304]Thread ldbwrite running[ CLWAL][1818209888]clsw_Initialize: OLR initlevel [70000]2013-07-09 16:14:29.177: [ CRFLOGD][2482564704]**ing up...2013-07-09 16:14:29.381: [ COMMCRS][2491111744]clsc_connect: (0x6f2bca0) no listener at (ADDRESS=(PROTOCOL=ipc)(KEY=dibarac01DBG_LOGD))CAUSEThe issue was investigated in multiple bugs:BUG 17238613 - LNX64-11204-CHM:OLOGGERD WAS DISABLED BECAUSE BDB GROWN BEYOND DESIRED LIMITSBUG 20439706 - DB_KEYEXIST: KEY/DATA PAIR ALREADY EXISTS ERROR IN CRFLOGD.LOGBUG 18447164 - CRFCLUST.BDB GROW HUGE SIZEBUG 19692024 - EXADATA: CRFCLUST.BDB IS GROWING TO 40 GBBUG 20127477 - CRFCLUST.BDB HAS GROWN UNEXPECTEDLY BUG 20127477 - CRFCLUST.BDB HAS GROWN UNEXPECTEDLY BUG 20316849 - HUGE REPSIZE RESULTING IN GI HOME DIRECTORY FILLING UPBUG 20351845 - RETENTION FOR CHM DATA IS SET TO 34YRS.All of those are closed as duplicate of the following:BUG 20186278 - TAG OCR: GET ID FAILED AND CHM DB SIZE 24 GBSOLUTIONRequest/apply the patch or use the following workaround:1. On all nodes, issue "crsctl stop res ora.crf -init" as root user2. On all nodes, remove the value of BDBSIZE tag entry (set it to blank) in<GI_HOME>/crf/admin/crf<node>.ora, do not delete the BDBSIZE tag3. On all nodes, issue "crsctl start res ora.crf -init" as root userREFERENCESNOTE:1328466.1 - Cluster Health Monitor (CHM) FAQ处理建议QQ给用户发了上面文章后，给出解决方案：1. On all nodes, issue "crsctl stop res ora.crf -init" as root user2. On all nodes, remove the value of BDBSIZE tag entry (set it to blank) in <GI_HOME>/crf/admin/crf<node>.ora, do not delete the BDBSIZE tag3. On all nodes, issue "crsctl start res ora.crf -init" as root user第二天老大也发出相关类似邮件，证明问题的确是由于此引起的问题心得这个问题本身并不复杂，metalink上一搜一就找到答案了,但是同时两个体会：1.出现问题需要网络搜索时尽量到oracle官网上去找相关内容2.前天晚上已经把方案给客户了，但是貌似客户没有自己处理好，结果升级到老大那里处理了，以后可以后续跟踪下，找用户确认一下问题是否解决。

检查RAC运行状况[grid@rac1 ~]$ crsctl check clusterCRS-4537: Cluster Ready Services is onlineCRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online检查Database实例例状态[oracle@rac1 ~]$ srvctl status database -d prod Instance rac1 is running on node rac1Instance rac2 is running on node rac2检查节点应用状态及配置[oracle@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 disabledGSD is not running on node: rac1GSD is not 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[oracle@rac1 ~]$ srvctl config nodeapps -a -g -s -l-l option has been deprecated and will be ignored. VIP exists.:rac1VIP exists.: /rac1-vip/182.168.8.53/255.255.255.0/eth0 VIP exists.:rac2VIP exists.: /rac2-vip/182.168.8.54/255.255.255.0/eth0 GSD exists.ONS daemon exists. Local port 6100, remote port 6200 Name: LISTENER Network: 1, Owner: gridHome: <CRS home>/u01/app/11.2.0/grid on node(s) rac2,rac1End points: TCP:1521查看数据库配置[oracle@rac1 ~]$ srvctl config database -d prod -aDatabase unique name: prodDatabase name: prodOracle home: /u01/app/oracle/product/11.2.0/db_1 Oracle user: oracleSpfile: +DATA/prod/spfileprod.oraDomain:Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICServer pools: prodDatabase instances: prod1,prod2Disk Groups: DATA,FRAServices:Database is enabledDatabase is administrator managed检查 ASM状态及配置[oracle@rac1 ~]$ srvctl status asmASM is running on rac1,rac2[oracle@rac1 ~]$ srvctl status asmASM is running on rac1,rac2[oracle@rac1 ~]$ srvctl config asm -aASM home: /u01/app/11.2.0/gridASM listener: LISTENERASM is enabled.检查 TNS的状态及配置[oracle@rac1 ~]$ srvctl status listenerListener LISTENER is enabledListener LISTENER is running on node(s): rac1,rac2 [oracle@rac1 ~]$ srvctl status listenerListener LISTENER is enabledListener LISTENER is running on node(s): rac1,rac2 [oracle@rac1 ~]$ srvctl config listener -a Name: LISTENERNetwork: 1, Owner: gridHome: <CRS home>/u01/app/11.2.0/grid on node(s) rac2,rac1End points: TCP:1521检查 SCAN的状态及配置[oracle@rac1 ~]$ srvctl status scanSCAN VIP scan1 is enabledSCAN VIP scan1 is running on node rac1[oracle@rac1 ~]$ srvctl config scanSCAN name: rac-scan, Network: 1/182.168.8.0/255.255.255.0/eth0 SCAN VIP name: scan1, IP: //182.168.8.55检查 VIP的状态及配置[oracle@rac1 ~]$ srvctl status vip -n rac1VIP rac1-vip is enabledVIP rac1-vip is running on node: rac1[oracle@rac1 ~]$ srvctl status vip -n rac2VIP rac2-vip is enabledVIP rac2-vip is running on node: rac2[oracle@rac1 ~]$ srvctl config vip -n rac1VIP exists.:rac1VIP exists.: /rac1-vip/182.168.8.53/255.255.255.0/eth0 [oracle@rac1 ~]$ srvctl config vip -n rac2VIP exists.:rac2VIP exists.: /rac2-vip/182.168.8.54/255.255.255.0/eth0。

oracle11gr2rac 安装配置手册一：系统检查1，cpu信息查看物理cpu数cat /proc/cpuinfo |grep physical\ id|sort|uniq查看单个cpu corescat /proc/cpuinfo |grep cpu\ cores|uniq查看单个是否超线程cat /proc/cpuinfo |grep sibling|uniq查看cpu总corescat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c2，确认内存大小物理内存504G3，检查文件系统/var和/tmp空间至少2G，建议10G/dev/shm共享空间应当为物理内存/2二：配置系统1，主机正名1节点2节点<记得重新登陆>2，本地hosts解析vi /etc/hosts#public address172.18.62.21 ghintdb01172.18.62.23 ghintdb02#public vip address172.18.62.22 ghintdb01-vip172.18.62.24 ghintdb02-vip172.18.62.25 ghintdb-cluster-scan#private group172.21.44.21 ghintdb01-priv1172.21.44.22 ghintdb02-priv13，修改内核参数shell>vi /etc/sysctl.conf#ORACLE SETTINGfs.aio-max-nr = 1048576fs.file-max = 6815744#{shmall}单位pages,命令"echo $((物理内存/$(getconf PAGESIZE)))" kernel.shmall = 132299730#{shmmax}，单位bytes,为物理内存一半,echo $((504*1024*1024*1024/2)) kernel.shmmax = 270582939648kernel.shmmni = 4096kernel.sem = 250 32000 100 128net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048586net.ipv4.ipfrag_high_thresh=524288net.ipv4.ipfrag_low_thresh=393216net.ipv4.tcp_rmem=4096 524288 16777216net.ipv4.tcp_wmem=4096 524288 16777216net.ipv4.tcp_timestamps=0net.ipv4.tcp_sack=0net.ipv4.tcp_window_scaling=1net.core.optmem_max=524287dev_max_backlog=2500 net.ipv4.tcp_mem=16384 16384 16384生效shell>modprobe sunrpcshell>sysctl -p4，关闭防火墙服务chkconfig --level 2345 iptables off chkconfig --level 2345 ip6tables off service iptables stopservice ip6tables stop关闭selinuxset enforce 0vi /etc/selinux/configSELINUX=disabled5，配置ntp同步<主机实施人员步骤,略>增加ntpd启动选项-xvi /etc/sysconfig/ntpdSYNC_HWCLOCK=yesOPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"重启ntpdchkconfig --level 2345 ntpd onservice ntpd start6，创建用户groupadd -g 10501 oinstallgroupadd -g 10502 asmadmingroupadd -g 10503 asmdbagroupadd -g 10504 asmopergroupadd -g 10505 dbagroupadd -g 10506 operuseradd -m -u 10501 -g oinstall -G asmadmin,asmdba,asmoper,dba -d /home/grid -s /bin/bash -c "Grid Infrastructure Owner" griduseradd -m -u 10502 -g oinstall -G dba,oper,asmdba -d /home/oracle -s /bin/bash -c "Oracle Software Owner" oracle7，设置用户进程运行资源上限shell>vi /etc/security/limits.conf#ORACLE SETTINGroot soft nproc 2047root hard nproc 16384root soft nofile 1024root hard nofile 65536root soft stack 10240root hard stack 32768grid soft nproc 2047grid hard nproc 16384grid soft nofile 1024grid hard nofile 65536grid soft stack 10240grid hard stack 32768oracle soft nproc 2047oracle hard nproc 16384oracle soft nofile 1024oracle hard nofile 65536oracle soft stack 10240oracle hard stack 32768三：配置存储1，要求：同一个lun/vdisk在每个节点上设备名一致通过scsi_id确认每一个 lun/vdisk是根据方案从存储上划分正确的共享存储在每个节点能同时读写设备名的属主grid,组主asmadmin，权限660多路径软件配置<主机层实施,略>2，udev绑定参考本文以scsi磁盘为例查看系统识别的scsi盘(sd)fdisk -l|grep Disk\ /dev/sd根据系统人员告知,sda盘是本地硬盘，我们不去动它,剩下的是共享磁盘查看scsi_id命令:scsi_id -gvu /dev/sdN3，编辑udev的rule文件vi /etc/udev/rules.d/99-oracle.rulesKERNEL=="dm-*", PROGRAM=="/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="36a08cf81005732a1e7c0cacf000000a1", NAME="asm-data01", OWNER="grid", GROUP="oinstall", MODE="0660"KERNEL=="dm-*", PROGRAM=="/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="36a08cf81005732a1e7c0caeb000000a2", NAME="asm-data02", OWNER="grid", GROUP="oinstall", MODE="0660"KERNEL=="dm-*", PROGRAM=="/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="36a08cf81005732a1e7c0cb07000000a3", NAME="asm-data03", OWNER="grid", GROUP="oinstall", MODE="0660"KERNEL=="dm-*", PROGRAM=="/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="36a08cf81005732a1e7c16f91000000a4", NAME="asm-fra01", OWNER="grid", GROUP="oinstall", MODE="0660"KERNEL=="dm-*", PROGRAM=="/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="36a08cf81005732a1e7c16fab000000a5", NAME="asm-fra02", OWNER="grid", GROUP="oinstall", MODE="0660"KERNEL=="dm-*", PROGRAM=="/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="36a08cf81005732a10f5b0dc3000000dc", NAME="asm-crs01", OWNER="grid", GROUP="oinstall", MODE="0660"KERNEL=="dm-*", PROGRAM=="/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="36a08cf81005732a10f5b35c5000000dd", NAME="asm-crs02", OWNER="grid", GROUP="oinstall", MODE="0660"KERNEL=="dm-*", PROGRAM=="/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="36a08cf81005732a10f5b461a000000de", NAME="asm-crs03", OWNER="grid", GROUP="oinstall", MODE="0660"启动udevstart_udev我们发现原来的磁盘设备名没有了，变成了需要的名字，可以用”fdisk -l /dev/设备名“验证大小，接下来的安装将直接使用该设备名，不需要做fdisk分区4，配置SSH通信：./sshUserSetup.sh -user grid -hosts "ghintdb01 ghintdb02" -advanced -noPromptPassphrase ./sshUserSetup.sh -user oracle -hosts "ghintdb01 ghintdb02" -advanced -noPromptPassphrase5，准备目录和权限（所有节点）mkdir -p /u01/media/oraclechown -R grid:oinstall /u01chmod -R g+w /u016三：上传安装介质使用grid账户登陆，解压前面3个包即可unzip p1*******_112040_Linux-x86-64_1of7.zipunzip p1*******_112040_Linux-x86-64_2of7.zipunzip p1*******_112040_Linux-x86-64_3of7.zip运行安装程序runInstaller（在1节点操作）cd grid./runInstaller提示：为了把x-windows程序的界面投射到终端上，需要执行2个步骤1.启动本地X11Forward程序，推荐x-manager中的xpassive(端口默认6000,注意firewall)2.在服务器上设定shell环境变量：export DISPLAY=YourIP:0.03.执行xclock测试本方案只有一个scan IP，不需要配置GNS，也不需要DNS服务器这3个，可以忽略可以忽略继续了以root身份在所有节点运行指定的脚本一个节点运行完后，再运行其他节点，不可同时运行点击“ok”，最后点“close”关闭编辑~grid/.bash_profile文件，设置grid用户环境变量vi ~grid/.bash_profileexport ORACLE_BASE=/u01/app/gridexport ORACLE_HOME=/u01/app/11.2.0/gridexport PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatchexport NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'安装数据库软件（在1节点操作）设置/u01/app目录的group组可写chmod g+w /u01/app使用oracle登陆，运行runInstallercd /u01/media/oracle/linux_x64/database export DISPLAY=YourIP:0.0./runInstaller不配置NTP同步，当节点之间的时间有差异时，会影响时间类型数据，RAC也会不稳定确认可以忽略后，选择”next”,然后点击”install“安装啦按照提示，使用root用户执行脚本运行结束后，点击”ok”,然后点击”close”完成安装增加oracle用户的软件环境变量vi ~oracle/.bash_profileexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/lib export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'注：sid为实例名，rac中为数据库名加节点序列号(重新登陆后生效）创建数据库创建asm diskgroup使用grid用户登陆，执行asmcaexport DISPLAY=yourIP:0.0asmca创建闪回磁盘组（用于存放归档，闪回日志）创建数据库磁盘组，存放数据库文件登陆oracle账户，执行dbca export DISPLAY=YourIP:0.0dbca除非使用者要求，请关闭OEM根据应用要求选择组件oracle text：大文本存取功能oracle olap:联机分析处理oracle spatial:空间数据库，如地图展示数据oracle label security:数据标签安全sample schemas:开发样本enterprise manager repository：oracle企业管理器OEM的资料库oracle warehouse builder：数据仓库综合工具oracle database vault：敏感数据门禁oracle jvm:oracle java虚拟机，在数据库运行javaoracle xml db:支持xml功能oracle mutimedia:图像多媒体等类型数据oracle application express:快速web应用程序开发工具重要！根据应用要求选择字符集,中文一般性是ZHS16GBK缺省区域可以用united states,本例是china，影响时间、数字、币种等本地化表示法默认只有4组日志，每个实例（thread #)2组，每组大小50MB ，会影响性能，需要修改大小和create 更多的日志组,建议每个实例至少5组,每组256MB 或512MB 方案： 1-5组日志分配给thread 1 6-10组日志分配给thread 2每组大小512MB四：应用累计补丁更新程序PSU升级补丁工具opatch在所有节点，使用grid用户登陆，解压opatch升级包p6880880_112000_Linux-x86-64.zip,并整体覆盖$ORACLE_HOME/OPatch目录在所有节点，使用opatch升级包p6880880_112000_Linux-x86-64.zip 并整体覆盖$ORACLE_HOME/OPatch目录补丁1节点软件解压psu补丁包有些补丁需要一些先决条件，oracle一直在努力简化补丁过程，打补丁前务必参考README.html文件创建ocm相应文件(opatchauto模式）运行升级程序root登陆加载grid环境变量(. ~grid/.bash_profile)运行opatch工具。

Oracle11g 通用巡检项SSIA27A 检查数据库版本以oracle用户分别登录主备节点。

执行如下命令：sqlplus / as sysdbaSQL> select * from v$version;Oracle11G的版本为及以上。

显示类似如下信息：BANNER--------------------------------------------------------------------------------Oracle Database 11g EnterpriseEdition Release .1.0 - 64bitProductionPL/SQL Release .1.0 -ProductionCORE .1.0 ProductionTNS for HPUX: Version .1.0 -ProductionNLSRTL Version .1.0 -Production如果与指定规划版本不符合则联系外购商升级到指定规划版本。

SSIA28A 检查安装路径以oracle用户分别登录到两个节点上。

执行如下命令：% echo $ORACLE_BASE% echo $ORACLE_HOME 对于ATAE路径为：ORACLE_BASE为/opt/app/oracleORACLE_HOME为/opt/app/oracle/product/如果安装路径与不一致，则向安装人员确认。

SSIA32A 检查l(非RAC)以oracle用户分别登录到两个节点上。

执行如下命令：显示类似如下信息：如果不一致，请根据实际情况修改。

SSIA34A (非RAC)以oracle用户分别登录到两个节点上。

执行如下命令：% cat $ORACLE_HOME HOST的取值为浮动IP地址或浮动IP地址对应的逻辑主机名;显示类似如下信息：如果不一致，请根据实际情况修改SSIA35A 检查数据库监以oracle用户分别登录到两个节显示的信息如果包含：The。