Oracle12c RAC ASM数据库资源池实施手册

Oracle12c+RAC+ASM数据库资源池实施手册福建移动多租户数据库资源池AIX7.1+Oracle12C+RAC+ASM实施手册福建新大陆软件工程有限公司目录1主机规划(3)1.1硬件环境(3)1.2软件环境(3)1.3网络规划(3)1.3.1拓扑连接(3)1.3.2IP地址(3)1.4安装目录规划(4)1.5存储规划(4)1.5.1grid、ASM安装目录(4)1.5.2OCR、VOTE存储规划(4)1.5.3数据库数据文件存储规划(4)1.6创建用户、组(4)1.6.1创建命令（注意集群用户与组的ID的一致）(4)1.7用户环境变量设置(5)1.7.1grid用户(5)1.7.2oracle用户(5)1.8SSH信任关系设置(5)1.9配置NTP服务(5)2安装Grid(5)2.1Grid安装前检查(5)2.2执行rootpre.sh(6)2.3安装grid(7)2.4Clusterware安装校验(16)3安装Database(18)4创建ASM磁盘组(25)5DBCA创建数据库(27)6启动、关闭及测试(33)6.1RAC集群系统维护命令(33)6.2启动和关闭集群系统命令(34)6.2.1启动集群件和集群数据库(34)6.2.2关闭RAC集群数据库和集群件(34)6.3数据库测试(35)6.3.1客户端配置(35)6.3.2查看监听(36)6.3.3登陆CDP&PDB(37)6.3.4创建 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 空间：(38)6.3.5查看表空间：(38)6.3.6删除表空间(38)6.3.7测试IO(39)1主机规划1.1硬件环境服务器：三台IBMP77016C(Power7CPU)/256GB；存储：一台富士通DX8700存储1.2软件环境数据库：aix.ppc64_12102_database_1of2.zipaix.ppc64_12102_database_2of2.zip、集群件（clusterware）：aix.ppc64_12102_grid_1of2.zipaix.ppc64_12102_grid_2of2.zip操作系统：AIX7.1(7100-03)1.3网络规划1.3.1拓扑连接ent12：业务10.48.187.243-251ent13：心跳192.168.2.1-3业务和心跳网络primary为2个GE端口绑定，backup为1个GE端口。1.3.2IP地址●每个节点一个publicIP●每个节点一个publicVIP●每个节点一个privateIP●一个用于集群的SCAN名称和一和publicIP同网段的IP（SCANIP）●publicIP,VIP和privateIP写入/etc/hosts文件配置好固定IP（public），ORACLE的私有IP（Private）和VIP，尤其要注意的是三台机器需配置对应关系一致的IP与网卡。10.48.187.243yzdb-rac110.48.187.244yzdb-rac210.48.187.245yzdb-rac310.48.187.246yzdb-rac1-vip10.48.187.247yzdb-rac2-vip10.48.187.248yzdb-rac3-vip10.48.187.249yzdb192.168.2.1yzdb-rac1-pri192.168.2.2yzdb-rac2-pri192.168.2.3yzdb-rac3-pri1.4安装目录规划安装目录规划原则：建立/oracle文件系统用来安装grid、datbase程序。程序都安装在/oracle/app下面，对于grid与database分别建立不同的目录，分配不同的权限。其中grid的ORACLE_BASE和ORACLE_HOME建议安装在不同的目录下，具体规划如下：新建60GB的lv：oralv，新建文件系统，挂载点：/oraclegridbase目录：/oracle/app/grid#grid用户的ORACLE_BASEgridasm安装目录：/oracle/app/12.1.0.2/grid#grid用户的ORACLE_HOME，也即是安装时的softwarelocationOraclebase目录：/oracle/app/oracle#oracle用户的ORACLE_BASE1.5存储规划1.5.1grid、ASM安装目录在Oracle12C中grid和ASM是安装在同一目录下的，将grid和ASM安装在/oracle/app/12.1.0.2/grid目录下，为JFS2文件系统，大小建立为60GB。1.5.2OCR、VOTE存储规划OCR&VOTE,hdisk28、48、49，每个10GB，共30GB1.5.3数据库数据文件存储规划ORA_DATA，使用hdisk7~47（除28之外）共40个，每个400GB，约16TB用来存放数据库数据文件文件，在ASM中做成external磁盘组。1.6创建用户、组1.6.1创建命令（注意集群用户与组的ID的一致）1、创建组mkgroup-'A'id='1000'adms='root'oinstallmkgroup-'A'id='1001'adms='root'dbamkgroup-'A'id='1002'adms='root'opermkgroup-'A'id='1003'adms='root'asmadminmkgroup-'A'id='1004'adms='root'asmopermkgroup-'A'id='1005'adms='root'asmdba2、创建用户mkuserid='1000'pgrp='oinstall'groups='dba,asmdba,oper,asmadmin,asmoper'gridmkuserid='1001'pgrp='oinstall'groups='dba,oper,asmadmin,asmdba,asmoper'oracle1.7用户环境变量设置1.7.1grid用户在三台服务器的的grid用户的.profile最后，增加如下内容：umask022exportORACLE_BASE=/oracle/app/grid#需手工创建exportORACLE_HOME=/oracle/app/12.1.0.2/grid#需手工创建exportPS1='hostname':'$PWD'"$1.7.2oracle用户在三台服务器的的oracle用户的.profile，增加如下内容：umask022exportPS1=$(hostname):'${PWD}'"$"exportORACLE_BASE=/oracle/app/oracle#需手工创建exportORACLE_HOME=/oracle/app/oracle/product/12.1.0.2exportORACLE_SID=yzcdb1#节点2是yzcdb2，节点3是yzcdb31.8SSH信任关系设置Grid用户和oracle用户都需要配置SSH信任关系。1.9配置NTP服务yzdb-rac1:/$ntpq-premoterefidsttwhenpollreachdelayoffsetdisp*10.46.4.15610.46.58.12u63643770.560.5760.06+10.46.4.15710.46.58.172u39643770.60-0.4390.062安装Grid2.1Grid安装前检查$./runcluvfy.shstage-precrsinst-nyzdb-rac1,yzdb-rac2,yzdb-rac3-fixup–verboseG19-POOL-YZ:/oracle/install/grid$./runcluvfy.shstage-precrsinst-nG19-POOL-YZ,G20-POOL-YZ,G21-POOL-YZ-fixup-verbosePerformingpre-checksforclusterservicessetupCheckingnodereachability...Check:Nodereachabilityfromnode"g19-pool-yz"DestinationNodeReachable?------------------------------------------------------------g19-pool-yzyesg20-pool-yzyesg21-pool-yzyesResult:Nodereachabilitycheckpassedfromnode"g19-pool-yz"Checkinguserequivalence...Check:Userequivalenceforuser"grid"NodeNameStatus------------------------------------------------------------g19-pool-yzpassedg21-pool-yzpassedg20-pool-yzpassedResult:Userequivalencecheckpassedforuser"grid"…….如果存在失败项，需进行相应的调整。2.2执行rootpre.sh1、以root身份登录在所有节点执行：slibclean2、以root身份登录在所有节点执行grid软件下的：rootpre.sh2.3安装grid2.4Clusterware安装校验1、root用户登录，使用crsctlcheckcluster命令在每个节点检查安装和运行情况；注意不能删除/tmp/.oracle或者/var/tmp/.oracle目录及其内部的文件，否则会出现CRS通信失败（CRS-0184）。yzdb-rac1:/oracle/app/grid$/oracle/app/12.1.0.2/asm_test/bin/crsctlcheckclusterCRS-4537:ClusterReadyServicesisonlineCRS-4529:ClusterSynchronizationServicesisonlineCRS-4533:EventManagerisonline2、grid用户登录，使用svrctlstatusasm命令检查ASM运行情况。3、查看集群启动了哪些服务，以grid用户执行crsctlstatusres-t命令：yzdb-rac1:/oracle/app/12.1.0.2/asm_test/bin$./crsctlstatusres-t--------------------------------------------------------------------------------NameTargetStateServerStatedetails--------------------------------------------------------------------------------LocalResources--------------------------------------------------------------------------------ora.LISTENER.lsnrONLINEONLINEyzdb-rac1STABLEONLINEONLINEyzdb-rac2STABLEONLINEONLINEyzdb-rac3STABLEora.OCVO.dgONLINEONLINEyzdb-rac1STABLEONLINEONLINEyzdb-rac2STABLEONLINEONLINEyzdb-rac3STABLEora.asmONLINEONLINEyzdb-rac1Started,STABLEONLINEONLINEyzdb-rac2Started,STABLEONLINEONLINEyzdb-rac3STABLEora.net1.networkONLINEONLINEyzdb-rac1STABLEONLINEONLINEyzdb-rac2STABLEONLINEONLINEyzdb-rac3STABLEora.onsONLINEONLINEyzdb-rac1STABLEONLINEONLINEyzdb-rac2STABLEONLINEONLINEyzdb-rac3STABLE--------------------------------------------------------------------------------ClusterResources--------------------------------------------------------------------------------ora.LISTENER_SCAN1.lsnr1ONLINEONLINEyzdb-rac3STABLEora.MGMTLSNR1ONLINEONLINEyzdb-rac3169.254.196.82192.168.2.3,STABLEora.cvu1ONLINEONLINEyzdb-rac3STABLEora.mgmtdb1ONLINEONLINEyzdb-rac3Open,STABLEora.oc4j1ONLINEONLINEyzdb-rac3STABLEora.scan1.vip1ONLINEONLINEyzdb-rac3STABLEora.yzdb-rac1.vip1ONLINEONLINEyzdb-rac1STABLEora.yzdb-rac2.vip1ONLINEONLINEyzdb-rac2STABLEora.yzdb-rac3.vip1ONLINEONLINEyzdb-rac3STABLEyzdb-rac3:/oracle/app/grid$exportORACLE_SID=+ASM3yzdb-rac3:/oracle/app/grid$sqlplus/nologSQL*Plus:Release12.1.0.2.0ProductiononFriOct2321:46:422015Copyright(c)1982,2014,Oracle.Allrightsreserved.SQL>conngrid/gridgridassysdbaConnected.SQL>3安装Database先只安装数据库软件，之后再用dbca命令建立集群数据库。Oracle用户登录SJ-1F-G20-POOL-YZ:/oracle/app/oracle/product/12.1.0.2/sqlplus/bin$sqlplus/nologSQL*Plus:Release12.1.0.2.0ProductiononSatSep2621:34:472015Copyright(c)1982,2014,Oracle.Allrightsreserved.SQL>4创建ASM磁盘组在建库前需要先创建存储数据文件的ASM磁盘组。选择rhdisk7～rhdisk47（除28之外）组成ORA_DATA磁盘组。其中OCRVOTE磁盘组是安装grid时建立的给OCR和VOTEDISK使用的，ORA_DATA是准备用来安装数据库的，三个节点都必须处于mounted（3of3）状态。5DBCA创建数据库使用oracle用户，通过xmanager登录运行dbca来建立RAC集群数据库。Sizing中选择16384（16k块大小）6启动、关闭及测试6.1RAC集群系统维护命令$srvctladd–h#给集群添加对象，比较重要的如：service,srvpool等等$srvctlremove–h#删除集群的对象$srvctlmodify–h#修改集群的对象$srvctlconfig–h#查看集群内对象的配置信息$srvctlstatus–h#查看集群内对象的状态信息$srvctlrelocate–h#把集群的对象重定位到其它实例节点，如oc4j，gns只在一个实例节点上有效，使用该命令可以把对象转移到其它实例节点$srvctlenable–h#把集群内已经存在的对象置于可用状态$srvctldisable–h#把集群内已经存在的对象置于不可用状态$srvctlstart–h#启动集群内已经存在的对象$srvctlstop–h#停止集群内已经存在的对象$srvctlconfigdatabase#查看全部安装集群数据库名称$srvctlconfigdatabase–dyzcdb#查看指定的集群数据库的配置信息，以环境变量中为准$srvctlconfignodeapps–a–g–s–e#显示节点应用程序的配置6.2启动和关闭集群系统命令6.2.1启动集群件和集群数据库集群系统默认是自动启动的，root手工启动命令如下：#cd/oracle/app/12.1.0.2/grid/bin#视安装路径而定，可查看grid环境变量#./crsctlstartcluster也可以以grid用户执行以下命令$su-grid$crs_start–allyzdb-rac3:/oracle/app/12.1.0.2/grid/bin$su-rootroot'sPassword:yzdb-rac3:/$cd/oracle/app/12.1.0.2/grid/binyzdb-rac3:/oracle/app/12.1.0.2/grid/bin$./crsctlstartclusterCRS-2672:Attemptingtostart'ora.cssd'on'yzdb-rac3'CRS-2672:Attemptingtostart'ora.diskmon'on'yzdb-rac3'CRS-2676:Startof'ora.diskmon'on'yzdb-rac3'succeededCRS-2676:Startof'ora.cssd'on'yzdb-rac3'succeededCRS-2672:Attemptingtostart'ora.ctssd'on'yzdb-rac3'CRS-2672:Attemptingtostart'ora.cluster_interconnect.haip'on'yzdb-rac3'CRS-2676:Startof'ora.ctssd'on'yzdb-rac3'succeededCRS-2676:Startof'ora.cluster_interconnect.haip'on'yzdb-rac3'succeededCRS-2672:Attemptingtostart'ora.asm'on'yzdb-rac3'CRS-2676:Startof'ora.asm'on'yzdb-rac3'succeededCRS-2672:Attemptingtostart'ora.storage'on'yzdb-rac3'CRS-2676:Startof'ora.storage'on'yzdb-rac3'succeededCRS-2672:Attemptingtostart'ora.crsd'on'yzdb-rac3'CRS-2676:Startof'ora.crsd'on'yzdb-rac3'succeededyzdb-rac3:/oracle/app/12.1.0.2/grid/bin$su-grid查看集群启动了哪些服务，以grid用户执行crsctlstatusres-t命令启动集群数据库$su–oracle$srvctlstartdatabase–dyzcdb#启动全部实例$srvctlstartinstance–dyzcdb–nyz-rac1#节点1上启动数据库实例$srvctlstartinstance–dyzcdb–nyz-rac2#节点2上启动数据库实例6.2.2关闭RAC集群数据库和集群件先关闭集群数据库$su–oracle$srvctlstopdatabase–dyzcdb再关闭集群件（root用户）#cd/oracle/app/12.1.02/grid/bin#视安装路径而定，可查看grid环境变量#./crsctlstopcluster6.3数据库测试6.3.1客户端配置yzcdb=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.48.187.246)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.48.187.247)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.48.187.248)(PORT=1521))(LOAD_BALANCE=no)(FAILOVER=ON)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=yzcdb)(FAILOVER_MODE=(TYPE=SESSION)(METHOD=BASIC))))yzpdb_test=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.48.187.246)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.48.187.247)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.48.187.248)(PORT=1521))(LOAD_BALANCE=no)(FAILOVER=ON)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=yzpdb_test)(FAILOVER_MODE=(TYPE=SESSION)(METHOD=BASIC))))6.3.2查看监听yzdb-rac1:/oracle/app/oracle$lsnrctlstatusLSNRCTLforIBM/AIXRISCSystem/6000:Version12.1.0.2.0-Productionon23-OCT-201508:48:50Copyright(c)1991,2014,Oracle.Allrightsreserved.Connectingto(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUSoftheLISTENER------------------------AliasLISTENERVersionTNSLSNRforIBM/AIXRISCSystem/6000:Version12.1.0.2.0-ProductionStartDate22-OCT-201518:16:25Uptime0days14hr.32min.25secTraceLeveloffSecurityON:LocalOSAuthenticationSNMPOFFListenerParameterFile/oracle/app/12.1.0.2/grid/network/admin/listener.oraListenerLogFile/oracle/app/grid/diag/tnslsnr/yzdb-rac1/listener/alert/log.xmlListeningEndpointsSummary...(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.48.187.243)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.48.187.246)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=yzdb-rac1)(PORT=5500))(Security=(my_wallet_directory=/oracle/app/oracle/product/12.1.0.2/admin/yzcdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))ServicesSummary...Service"+ASM"has1instance(s).Instance"+ASM1",statusREADY,has1handler(s)forthisservice...Service"-MGMTDBXDB"has1instance(s).Instance"-MGMTDB",statusREADY,has1handler(s)forthisservice...Service"_mgmtdb"has1instance(s).Instance"-MGMTDB",statusREADY,has1handler(s)forthisservice...Service"yzcdb"has1instance(s).Instance"yzcdb1",statusREADY,has1handler(s)forthisservice...Service"yzcdbXDB"has1instance(s).Instance"yzcdb1",statusREADY,has1handler(s)forthisservice...Service"yzdb_cluster"has1instance(s).Instance"-MGMTDB",statusREADY,has1handler(s)forthisservice...Service"yzpdb_test"has1instance(s).Instance"yzcdb1",statusREADY,has1handler(s)forthisservice...Thecommandcompletedsuccessfully6.3.3登陆CDP&PDByzdb-rac1:/oracle/app/grid/$su-oracleoracle'sPassword:yzdb-rac1:/oracle/app/oracle$exportORACLE_SID=yzcdb1yzdb-rac1:/oracle/app/oracle$sqlplus/nologSQL*Plus:Release12.1.0.2.0ProductiononFriOct2308:49:222015Copyright(c)1982,2014,Oracle.Allrightsreserved.SQL>connsys/Pass#w0rdassysdbaConnected.SQL>showcon_nameCON_NAME------------------------------CDB$ROOTSQL>selectcon_id,dbid,name,open_modefromv$pdbs;CON_IDDBID--------------------NAME------------------------------------------------------------OPEN_MODE--------------------22242427422PDB$SEEDREADONLY31968005246YZPDB_TESTREADWRITECON_IDDBID--------------------NAME------------------------------------------------------------OPEN_MODE--------------------SQL>selectdbid,namefromv$pdbs;DBIDNAME----------------------------------------------------------------------2242427422PDB$SEED1968005246YZPDB_TESTSQL>altersessionsetcontainer=YZPDB_TEST;Sessionaltered.SQL>showcon_nameCON_NAME------------------------------YZPDB_TESTSQL>showparameterservice;NAMETYPE----------------------------------------------------------VALUE------------------------------service_namesstringyzcdbSQL>6.3.4创建表空间：SQL>createtablespacetest5datafile'+ORA_DATA/YZCDB/DATAFILE/test5'size30000m;Tablespacecreated.SQL>createbigfiletablespacetestdatafile'+ORA_DATA/YZCDB/DATAFILE/test'size5T;6.3.5查看表空间：SQL>selectfile_name,BYTES/1000000000fromdba_data_files;FILE_NAMEBYTES/1000000000+ORA_DATA/YZCDB/DATAFILE/test320.97152+ORA_DATA/YZCDB/DATAFILE/test431.45728+ORA_DATA/YZCDB/DATAFILE/test531.457286.3.6删除表空间SQL>droptablespacetest5includingcontentsanddatafiles；6.3.7测试IO