平顶山银行系统数据库部署运维手册

1、实施方案平顶山TEL 系统数据库部署运维手册(AIX 系统)2016 年 2 月 1 日目录1.系统环境32.安装前的准备.1)2)3)4)5)6)..系统补丁4系统参数5VMO5网络参数5磁盘属性5系统参数6换页空间6Hosts 文件6配置 NTP6创建用户和组7配置 SSH9安装前的检查93.安装 ORACLE GRID INFRASTRUCTURE10安装 ORACLE DATABASE214.5.创建 ASM 磁盘组28运行 DBCA 创建数据库296.7.数据库参数修改及优化367.1 修改数据库初始化参数367.2 修改系统自带的各表空间

2、大小368.系统环境378.1 数据库集群启停操作378.2 器操作398.3 警告日志检查398.4 ORACLE 安装目录磁盘使用情况检查408.5 ORACLE 归档目录磁盘使用情况检查408.6 表空间维护408.7 维护联机日志418.8 统计信息411. 系统环境硬件：2 台 IBM P750，SAN Switch 各 1 台，HDS一套；软件：Oracle 11gR2 grid，Oracle 11gR2 database3节点Node NameInstance NameDB nameProcessorRAMOSdteldb01Grid：+ASM1oracle：tel1TEL166

3、4GBAIX7.1dteldb02Grid：+ASM2oracle：tel21664GBAIX7.1网络配置NodenamePublic IPPrivate IPVIPSCANSCAN IPdteldb046scan8dteldb0257Oracle 软件组件软件组件UserPrimarygroup辅助组主目录目录Grid Infragridoinstalldba，oper， asmdba， asmadmin，asmoper/home/gridORACLE

4、_BASE=/u01/app/gridORACLE_HOME=/u01/app/11.2.0/gridOracle Databaseoracleoinstalldba， asmdba/home/oracleORACLE_BASE=/u01/app/oracleORACLE_HOME=/u01/app/oracle/prod uct/11.2.0/db_1组件组件文件系统卷大小ASM 卷组名ASM 冗余设备名CRSASM1G*5CRSHigh/dev/rhdisk7112. 安装前的准备2.1.系统补丁补丁列表如下：bos.adt.basebos.adt.libbos.adt.libmbos.p

5、ctoolsxlC.rte or latergpfs.base 1 or later (Only for RAC systems that will use GPFS cluster filesystems)需要的 APARs for AIX 7.1:IZ87216IZ87564IZ89165IZ97035检查补丁文件。注意：安装 oracle 11g grid类型选择 ASM，所以系统不需要安装 HACMP 相关软件，如果已经安装，需卸载 bos.ahafs，bos.clu

6、ster，hacmp 三个包。否则在安装 grid 执行 rootpre.sh 时会报错。4ctools rsct.basic.rte pat.clients.rte xlC.aix61.rtelslpp-lbos.adt.basebos.adt.libbos.adt.libmbos.perf.libperfstatbos.perf.perfstatFRAASM200G*2FRAExternal/dev/rhdisk56DATAASM200G*3DATAExternal/dev/rhdisk242.2.系统参数1)VMO网络参数2)磁盘属性3)5#for i in 2.

7、11>do>chdev l hdisk$i a pv=clear>chdev l hdisk$i a reserve_policy=no_reserve>chown grid:asmadmin /dev/rhdisk$i>chmod 660 /dev/rhdisk$i>done#lsattr -E -l sys0 -a pre520tunepre520tune disable Pre-520 tuning compatibility mode True #no -p -o udp_sendspace=65536#no -p -o udp_recvspace=

8、655360 #no -p -o tcp_sendspace=65536 #no -p -o tcp_recvspace=65536 #no -p -o rfc1323=1#no -p -o sb_max=4194304 #no -r -o ipqmaxlen=512#no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500#no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500vmo -p -o minperm%=3 vmo -p -o maxperm%=90 vmo

9、 -p -o maxclient%=90 vmo -p -o lru_file_repage=0 vmo -p -o strict_maxclient=1vmo -p -o strict_maxperm=04) 系统参数5) 换页空间 Smitty chps 增加换页空间6) Hosts 文件修改/etc/hosts2.3.配置 NTP1)配置NTP服务器端启动xntpd守护进程6#vi / etc/rc.tcpip修改 start /usr/sbin/xntpd "$src_running" 行为/usr/sbin/xntpd "$src_running&quo

10、t; "-x" #vi /etc/ntp.conf#broadcastclient server driftfile /etc/ntp.drifttracefile /etc/ntp.trace#Public ip4 dteldb05 dteldb02 #VIP6 dteldb01-vip7 dteldb02-vip #Private ip dteldb01-priv dteldb02-priv #Scan ip

11、8dteldb-scanchdev -l sys0 -a fullcore=truechdev -l sys0 -a ncargs=128 chdev -l sys0 -a maxuproc=163842)配置NTP客户机启动xntpd守护进程3）确定ntp是否工作, 通过运行ntpq -p命令检查确认2.4.创建用户和组1） 创建用户及相应的目录创建组创建用户修改 grid、oracle、root 的 capabilities 属性 chusercapabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE7gridchuserca

12、pabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oraclechusercapabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATErootmkuserid='1000'pgrp='oinstall'groups='dba,asmdba,asmadmin,asmoper,oper'home='/home/grid' gridmkuser id='1001' pgrp='oin

13、stall' groups='dba,asmdba' home='/home/oracle' oraclemkgroup -'A' id='1010' adms='root' oinstall mkgroup -'A' id='1020' adms='root' asmadmin mkgroup -'A' id='1021' adms='root' asmdba mkgroup -'A' id=&

14、#39;1022' adms='root' asmoper mkgroup -'A' id='1031' adms='root' dbamkgroup -'A' id='1032' adms='root' oper#/usr/bin/ntpq -p# startsrc -s xntpd -a "-x"#vi / etc/rc.tcpip修改 start /usr/sbin/xntpd "$src_running" 行为/usr/sbin

15、/xntpd "$src_running" "-x" #vi /etc/ntp.confserver dodsdb02 driftfile /etc/ntp.drifttracefile /etc/ntp.trace# startsrc -s xntpd -a "-x"修改两用户权限2）配置 GRID 用户环境变量注意 grid 的 ORACLE_BASE 目录不能在 ORACLE_HOME 目录下3）配置 Oracle 用户环境变量；4）创建相关目录并分配赋予权限8#mkdir p /u01/app/grid #mkdir p /u

16、01/app/11.2.0/grid#mkdir p /u01/app/oracle/product/11.2.0/db_1 #chown -R grid:oinstall /u01/app/11.2.0/grid #chown -R grid:oinstall /u01/app/grid#chown -R oracle:oinstall /u01/app/oracle#chmod -R 775 /u01/#su oracle$vi .profile （oracle 环境变量）export PS1="/usr/bin/hostname-> " export ORACL

17、E_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export ORACLE_SID=tel1 (另外一个节点为 tel2)export PATH=$PATH:$ORACLE_HOME/bin: /sbin:/usr/sbin:/bin:/usr/local/bin:.export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib$vi .profile（grid 用户环境变量）export PS1=&quo

18、t;/usr/bin/hostname-> "export ORACLE_SID=+ASM1 (另外一个节点为+ASM2) export ORACLE_BASE=/u01/app/gridexport ORACLE_HOME=/u01/app/11.2.0/gridexport PATH=$ORACLE_HOME/bin:$PATH:/usr/local/bin/:.#su gridvi /etc/security/limits default:fsize = -1core = 2097151cpu = -1data = -1rss = -1stack = -1nofiles

19、= -12.5.配置 SSH1)以 root 用户修改 ssh 的配置文件2)以下在两个节点分别以 grid 和 oracle 用户运行3)以下在一个节点分别以 grid 和 oracle 用户运行4)配置完毕分别在两个节点以 grid 和 oracle 用户验证 ssh 连通性，如果都不需要，得到时间后，就说明 ssh 对等性配置。在两个节点分别执行：2.6.安装前的检查到 Grid 的安装目录执行如下指令：9#./runcluvfy.sh stage -pre crsinst -n dteldb01, dteldb02 verbosessh dteldb01 date ssh dteldb

20、02 datessh dteldb01-priv datessh dteldb02-priv date$ ssh dteldb02 cat /.ssh/id_rsa.pub>>/.ssh/authorized_keys$ ssh dteldb02 cat /.ssh/id_dsa.pub>>/.ssh/authorized_keys$ ssh dteldb02 cat /.ssh/id_rsa.pub>>/.ssh/authorized_keys$ ssh dteldb02 cat /.ssh/id_dsa.pub>>/.ssh/authoriz

21、ed_keys$ scp /.ssh/authorized_keys dteldb02:/.ssh/authorized_keys# mkdir /.ssh# chmod 755 /.ssh# /usr/bin/ssh-keygen -t rsa # /usr/bin/ssh-keygen -t dsa# touch /.ssh/authorized_keys#vi /etc/ssh/sshd_configLoginGraceTime 03. 安装 Oracle Grid Infrastructure解压介质：mkdir /u01/mediachmod R 775 /u01/media/usr

22、/java5/bin/jar xvf 13390677_112040_AIX64-5L_1of7.zip/usr/java5/bin/jar xvf 13390677_112040_AIX64-5L_2of7.zip/usr/java5/bin/jar xvf 13390677_112040_AIX64-5L_3of7.zip以grid用户运行OUI安装A.软件更新：选择 Skip software updates，单击 Next10#su - grid$cd /u01/media/grid$./runInstallerB.安装选项：选择 install and configure grid

23、infrastructure for a cluster，单击 NextC.安装类型：选择 Advanced Installation，单击 Next11D.语言：添加 Simplified，单击 NextE.配置 Cluster 节点信息：12F.选择特定的网络接口：(按照/etc/hosts 中IP 对应设置，多余的都设置为 Do Not Use)G.方式：选择 ASM13H.创建 ASM磁盘组：Disk Group Name 选 CRSDG磁盘选择/dev/rhdisk12 /dev/rhdisk16I.指定 ASM用户：选择 Use same passwords for these a

24、ccounts14J.选择特定的管理组：asmdba，asmoper，asmadminK.选择安装位置：Oracle Base：/u01/app/gridSoftware Location：/u01/app/11.2.0/grid15L.选择特定的 OraInventory：/u01/app/oraInventoryM.执行预安装检查：16N.安装汇总：O.按提示执行：分别在两个节点以 root 用户的权限按顺序运行 orainstRoot.sh 及 root.sh执行节点 1 的 root.sh内容如下：17bash-3.00# /u01/app/11.2.0/grid/root.shRun

25、ning Oracle 11g root script.The following environment variables are set as:ORACLE_OWNER= gridORACLE_HOME=/u01/app/11.2.0/gridEnter the full pathname of the local bin directory: /usr/local/bin:The contents of "dbhome" have not changed. No need to overwrite.The contents of "oraenv"

26、 have not changed. No need to overwrite.The contents of "coraenv" have not changed. No need to overwrite.Creating /etc/oratab file.Entries will be added to the /etc/oratab file as needed byDatabase Configuration Assistant when a database is createdFinished running generic part of root scri

27、pt.Now product-specific root actions will be performed.Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_paramsCreating trace directoryUser grid has the required capabilities to run CSSD in realtime modeLOCAL ADD MODECreating OCR keys for user 'root', privgrp 

28、9;system'.Operation successful.OLR initialization - successfulroot walletroot wallet certroot cert exportpeer walletprofile reader walletpa walletpeer wallet keyspa wallet keyspeer cert request18pa cert requestpeer certpa certpeer root cert TPprofile reader root cert TPpa root cert TPpeer pa cer

29、t TPpa peer cert TPprofile reader pa cert TPprofile reader peer cert TPpeer user certpa user certAdding daemon to inittabACFS-9200: SupportedACFS-9300: ADVM/ACFS distribution files found.ACFS-9307: Installing requested ADVM/ACFS software.ACFS-9359: The AIX ODM entry for 'acfsctl' was success

30、fully added.ACFS-9359: The AIX ODM entry for 'advmctl' was successfully added.ACFS-9359: The AIX ODM entry for 'advmvol' was successfully added.ACFS-9308: Loading installed ADVM/ACFS drivers.ACFS-9327: Verifying ADVM/ACFS devices.ACFS-9309: ADVM/ACFS installation correctness verified

31、.CRS-2672: Attempting to start 'ora.mdnsd' on dteldb01CRS-2676: Start of 'ora.mdnsd' on dteldb01 succeededCRS-2672: Attempting to start 'ora.gpnpd' on dteldb01CRS-2676: Start of 'ora.gpnpd' on dteldb01 succeededCRS-2672: Attempting to start 'ora.cssdmonitor' o

32、n dteldb01CRS-2672: Attempting to start 'ora.gipcd' on dteldb01CRS-2676: Start of 'ora.cssdmonitor' on dteldb01 succeededCRS-2676: Start of 'ora.gipcd' on dteldb01 succeeded19CRS-2672: Attempting to start 'ora.cssd' on dteldb01CRS-2672: Attempting to start 'ora.di

33、skmon' on dteldb01CRS-2676: Start of 'ora.diskmon' on dteldb01 succeededCRS-2676: Start of 'ora.cssd' on dteldb01 succeededASM created and started successfully.Disk Group CRS created successfully.clscfg: -install mode specifiedSuccessfully accumulated necessary OCR keys.Creating

34、OCR keys for user 'root', privgrp 'system'.Operation successful.CRS-4256: Updating the profileSuccessful addition of voting disk 684b31bb1d9f4f2dbf31fbea02e5acd6.Successfully replaced voting disk group with +CRS.CRS-4256: Updating the profileCRS-4266: Voting file(s) successfully repl

35、aced#STATEFile Universal IdFile Name Disk group1. ONLINE684b31bb1d9f4f2dbf31fbea02e5acd6 (/dev/rhdisk7) CRSLocated 1 voting disk(s).CRS-2672: Attempting to start 'ora.asm' on dteldb01CRS-2676: Start of 'ora.asm' on dteldb01 succeededCRS-2672: Attempting to start 'ora.CRS.dg'

36、on dteldb01CRS-2676: Start of 'ora.CRS.dg' on dteldb01 succeededACFS-9200: SupportedACFS-9200: SupportedCRS-2672: Attempting to start 'ora.registry.acfs' on dteldb01CRS-2676: Start of 'ora.registry.acfs' on dteldb01 succeededConfigure Oracle Grid Infrastructure for a Cluster

37、. succeeded204. 安装 Oracle Database以oracle用户运行OUI安装A.配置安全更新：所有的选项都不用选21#su - oracle$cd /u01/media/database$./runInstallerB.安装选项：选择 install database software only，单击 NextC.Grid 安装选项：选择 Real Application Clusters databaseinstallation，可以看到两个节 nodename，的 SSH Connectivity 按钮，设置 oracle 用户，点击 Setup，点击 Test22

38、D.语言：添加 Simplified，单击 Next23E.安装企业版F.选择安装位置：Oracle Base：/u01/app/oracle24Software Location：/u01/app/oracle/product/11.2.0/db_1G.选择特定管理组：dba，operH.执行预安装检查：25I.安装汇总：J.按提示执行：分别在两个节点以 root 用户的权限按顺序运行 root.sh26#/u01/app/oracle/product/11.2.0/db_1/root.shRunning Oracle 11g root script.The following enviro

39、nment variables are set as:ORACLE_OWNER= oracleORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1Enter the full pathname of the local bin directory: /usr/local/bin:The contents of "dbhome" have not changed. No need to overwrite.The contents of "oraenv" have not changed. No need to o

40、verwrite.The contents of "coraenv" have not changed. No need to overwrite.Entries will be added to the /etc/oratab file as needed byDatabase Configuration Assistant when a database is createdFinished running generic part of root script.Now product-specific root actions will be performed.Fi

41、nished product-specific root actions.275. 创建 ASM 磁盘组以grid用户运行OUI安装创建 DATA，FRA 两个磁盘组，其中 DATA 给数据库文件使用，FRA 给归档日志文件使用28#su - grid$./asmca6. 运行 DBCA 创建数据库以oracle用户运行OUI安装A.欢迎页面选择 Oracle Real Application Clusters (RAC) databaseB.选择Create a Database，单击 Next29# su - oracle$ dbcaC.选择General Purpose or Trans

42、action Processing，单击 NextD.配置类型为 Admin-Managed，全局数据库名及 SID Prefix 都是 TEL (Oracle 用户的.bash_profile中有设置)，节点部分 Select All30E.企业管理器部分不选F.所有用户设置一个31G.设置类型：ASM，选择"Use Oracle-Managed Files"Database Files Location：+DATAH.不选择 Specify Flash Recovery Area32I.数据库组件默认即可（全都不选）J. Memory 部分SGA Size 填写 196

43、60 M BytesPGA Size 填写6553M Bytes33点击 Sizing 选项卡，Processes 一栏填写 1500字符集部分Database Character sets 选择：zhs16gbkNational Character Set 选择：al16utf16连接模式采用 Dedicated Server34L.创建数据库数据库版本是 ，已经将 GRID和数据库升级到最新的 PSU：.8357. 数据库参数修改及优化7.1 修改数据库初始化参数使用 oracle 用户在 sqlplus 中执行（在任意一台服务器上执行即可）alter sy

44、stem set sga_max_size=80g scope=spfile;alter system set sga_target=80g scope=spfile;alter system set pga_aggregate_target=16g scope=spfile;alter system set db_recovery_file_dest='+FRA' scope=spfile;alter system set db_recovery_file_dest_size=10322025M scope=spfile;alter system set db_files =

45、4096 scope=spfile;alter system set open_cursors =600 scope=spfile;重启数据库，以便使修改生效srvctl stop database -telsrvctl start database -tel7.2 修改系统自带的各表空间大小使用如下语句确定数据文件路径：selectfile_name,tablespace_name from dba_data_files where tablespace_name in ('SYSTEM','SYSAUX','UNDOTBS1','UN

46、DOTBS2');修改表空间大小alter database datafile '+DATA/TEL/datafile/sysaux.257.85056628' resize 2g;alter database datafile '+DATA/TEL/datafile/system.256.850566279' resize 2g;alter database datafile '+DATA/TEL/datafile/undotbs1.258.85056628' resize 4g;alter database datafile '

47、;+DATA/TEL/datafile/undotbs2.264.850566455' resize 4g;使用如下语句确定临时文件路径：selectfile_name from dba_temp_files;修改临时表空间大小alter database tempfile '+DATA/TEL/tempfile/temp.263.850566383' resize 20g;368. 系统环境8.1 数据库集群启停操作1）数据库集群停止检查 CRS 各状态：如果数据库状态为 online 的，先停数据库，数据库名是 tel：以操作系统 root 用户停止 crs：（两个

48、节点分别操作）37su - root#$CRS_HOME/bin/crsctl stop cluster# su oracle$ srvctl stop database d tel# su grid$ crs_stat -tNameTypeTargetStateHostora.DATA.dgora.up.type ONLINEONLINEdteldb01 ora.FRA.dgora.up.type ONLINEONLINEdteldb01 ora.ER.lsnr ora.er.type ONLINEONLINEdteldb01 ora.N1.lsnr ora.er.type ONLINEON

49、LINEdteldb02 ora.OCR.dgora.up.type ONLINEONLINEdteldb01 ora.asmora.asm.type ONLINEONLINEdteldb01 ora.teldb.db ora.se.type ONLINEONLINEdteldb01 ora.cvuora.cvu.type ONLINEONLINEdteldb02 ora.SM1.asm applicationONLINEONLINEdteldb01 ora.01.lsnr applicationONLINEONLINEdteldb01 ora.b01.gsd applicationOFFLI

50、NE OFFLINE ora.b01.ons applicationONLINEONLINEdteldb01 ora.b01.vip ora.t1.type ONLINEONLINE dteldb01 ora.SM2.asm application ONLINE ONLINE dteldb02 ora.02.lsnr application ONLINE ONLINE dteldb02 ora.b02.gsd application OFFLINE OFFLINE ora.b02.ons application ONLINE ONLINE dteldb02 ora.b02.vip ora.t1

51、.type ONLINE ONLINE dteldb02 ora.gsd ora.gsd.type OFFLINE OFFLINE work ora.rk.type ONLINE ONLINE dteldb01 ora.oc4j ora.oc4j.type ONLINE ONLINE dteldb02 ora.ons ora.ons.type ONLINE ONLINE dteldb01 ora.ry.acfs ora.fs.type ONLINE ONLINE dteldb01 ora.scan1.vip ora.ip.type ONLINE ONLINE dteldb022）数据库集群停止

52、以操作系统 root 用户启动 crs：（两个节点分别操作）检查 CRS组状态需要启动的数据库组是 offline 状态，其他状态是 online 的，启动数据库检查数据库状态38# su grid# su oracle$ srvctl start database d telsu oracle$CRS_HOME/bin/crs_stat tNameTypeTargetStateHostora.DATA.dgora.up.type ONLINEONLINEdteldb01 ora.FRA.dgora.up.type ONLINEONLINEdteldb01 ora.ER.lsnr ora.er

53、.type ONLINEONLINEdteldb01 ora.N1.lsnr ora.er.type ONLINEONLINEdteldb02 ora.OCR.dgora.up.type ONLINEONLINEdteldb01 ora.asmora.asm.type ONLINEONLINEdteldb01 ora.teldb.db ora.se.type OFFLINE OFFLINEora.cvuora.cvu.type ONLINEONLINEdteldb02 ora.SM1.asm applicationONLINEONLINEdteldb01 ora.01.lsnr applica

54、tionONLINEONLINEdteldb01 ora.b01.gsd applicationOFFLINE OFFLINE ora.b01.ons applicationONLINEONLINEdteldb01 ora.b01.vip ora.t1.type ONLINEONLINEdteldb01 ora.SM2.asm applicationONLINEONLINEdteldb02 ora.02.lsnr applicationONLINEONLINEdteldb02 ora.b02.gsd applicationOFFLINE OFFLINE ora.b02.ons applicat

55、ionONLINEONLINEdteldb02 ora.b02.vip ora.t1.type ONLINEONLINEdteldb02 ora.gsdora.gsd.type OFFLINE OFFLINE work ora.rk.type ONLINEONLINEdteldb01 ora.oc4jora.oc4j.type ONLINEONLINE dteldb02 ora.ons ora.ons.type ONLINE ONLINE dteldb01 ora.ry.acfs ora.fs.type ONLINE ONLINE dteldb01 ora.scan1.vip ora.ip.type O