绝密邮件-oracle 11g 安装文档
***************************************************************
1.确认系统信息
1)系统版本最低要求
AIX 5L V5.3 TL 09 SP1 ("5300-09-01"), 64 bit kernel
AIX 6.1 TL 02 SP1 ("6100-02-01), 64-bit kernel
AIX 7.1 TL 0 SP1 ("7100-00-01"), 64-bit kernel
#######2017-03-02#########
--操作系统版本检查
oslevel -s
若存在以下信息，则要求系统回退AIX补丁7.1.4.2的SP03，6.1.9.7的SP08
7100-04-03-1642
7100-04-03-1643
6100-09-08-1642
6100-09-08-1643
2) 主机名设置
主机名，IP,由前期系统人员配置,
服务器/etc/hosts文件的编写,数据库中，要求主机名对应的IP地址写入/etc/hosts文件
vi /etc/hosts
# *** Oracle Scan IP ***
110.121.1.245 zhqzdb-scan
# *** Oracle Public IP***
110.121.1.143 zhqzdb1
110.121.1.144 zhqzdb2
# *** Oracle Virtual IP ***
110.121.1.143 zhqzdb1-vip
110.121.1.244 zhqzdb2-vip
# *** Oracle Private IP ***
192.121.201.143 zhqzdb1-priv
192.121.201.144 zhqzdb2-priv
# *** Management IP ***
110.200.2.17 zhqzdb1_mgr
110.200.2.18 zhqzdb2_mgr
3) 内存检查：至少4G的物理内存。
lsdev -C | grep mem
lsattr -El mem0
4) Swap空间
设置规则
4GB<RAM<8 GB swap=2*RAM
8GB<RAM<32 GB swap=1.5*RAM
RAM>32 GB swap=32GB
/usr/sbin/lsps -a
5)所需软件包安装
bos.adt.base
bos.adt.lib
bos.adt.libm
bos.perf.libperfstat
bos.perf.perfstat
bos.perf.proctools
xlC.aix61.rte 10.1.0.0 (or later) 或xlC.aix50.rte 10.1.0.0 (or later)。
lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.libperfstat bos.perf.perfstat bos.perf.proctools xlC.aix61.rte
6)空间检查
df -g
/tmp 剩余空间不少于2G
/home 建议剩余空间不少于2G
/var 建议剩余空间不少于2G
/usr 建议剩余空间不少于2G
/opt 建议剩余空间不少于2G
2.建立用户和组
mkgroup -'A' id='300' adms='root' dba
mkuser id=300 pgrp=dba oracle
mkuser id=301 pgrp=dba grid
mkgroup -'A' id='600' users='root,oracle,grid' hagsuser
3,赋用户权限
chuser capabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE grid
chuser capabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle
3.空间准备
(1)建立软件存放文件目录(/pkg)
建议剩余空间不少于10G；
mkdir -p /pkg/oracle/11203(版本号)
chown -R oracle:dba /pkg/oracle
(2)建立$ORACLE_BASE文件系统
要求大小为50G
/usr/sbin/mklv -y'lv_oracle' -t'jfs2' -a'c' bakvg 100
/usr/sbin/crfs -v jfs2 -d 'lv_oracle' -m '/oracle' -A yes -p rw
mount /oracle
chown -R oracle.dba /oracle
mkdir -p /oracle/grid
mkdir -p /oracle/asm
mkdir -p /oracle/db
chown -R grid:dba /oracle
chown -R grid:dba /oracle/asm
chown -R grid:dba /oracle/grid
chown -R oracle:dba /oracle/db
chmod -R 755 /oracle
(4)准备数据文件
命名规范及初始安装大小
system = 10000M
sysaux = 10000M
user = 5000M
ctl = 500M
spfile = 250M
undo = 20000M
temp = 20000M
redo = 500M
********************************************************************************
UNDO,TEMP设置规范
此设置为初始安装时设置。数据库大小为ASM磁盘ASMVG1大小。
当数据库大小为：0-300G UNDO=30G TEMP=30G
当数据库大小为：300G-1000G UNDO=60G TEMP=60G
当数据库大小为：1T-1.5T UNDO=90G TEMP=90G
当数据库大小为: 1.5T以上 UNDO=120G TEMP=120G
********************************************************************************
4.参数配置
1)系统参数
vi /etc/security/limits
root:
fsize = -1
cpu = -1
data = -1
stack = -1
core = -1
rss = -1
nofiles = -1
fsize_hard = -1
cpu_hard = -1
data_hard = -1
stack_hard = -1
rss_hard = -1
nofiles_hard = -1
oracle:
fsize = -1
cpu = -1
data = -1
stack = -1
core = -1
rss = -1
nofiles = -1
fsize_hard = -1
cpu_hard = -1
data_hard = -1
stack_hard = -1
rss_hard = -1
nofiles_hard = -1
grid:
fsize = -1
cpu = -1
data = -1
stack = -1
core = -1
rss = -1
nofiles = -1
fsize_hard = -1
cpu_hard = -1
data_hard = -1
stack_hard = -1
rss_hard = -1
nofiles_hard = -1
2)修改AIO及系统参数
AIX-6.1
ioo -o aio_maxreqs=65536
ioo -o aio_minservers=40
ioo -o aio_maxservers=80
AIX-5.3
smit aio
3)修改进程参数
chdev -l sys0 -a maxuproc=16384
chdev -l sys0 -a ncargs=256
chdev -l sys0 -a fullcore=true
4)，调整虚拟内存参数
vmo -p -o minperm%=3
一条一条执行
vmo -p -o maxperm%=90
vmo -p -o maxclient%=90
vmo -p -o lru_file_repage=0
vmo -p -o strict_maxclient=1
vmo -p -o strict_maxperm=0
5)，调整网络参数
lsattr -El sys0 -a pre520tune
输出如：
pre520tune disable Pre-520 tuning compatibility mode True
说明是不兼容方式，这样就可以用下面的方式调整：
/usr/sbin/no -p -o sb_max=21053440
/usr/sbin/no -p -o udp_sendspace=1052672
/usr/sbin/no -p -o udp_recvspace=10526720
以上三个参数的计算方式:
udp_sendspace=(DB_BLOCK_SIZE*DB_MULTIBLOCK_READ_COUNT)+4k=8192*128+4*1024=1052672
udp_recvspace=10*udp_sendspace=10526720
sb_max=2*udp_recvspace=21053440
/usr/sbin/no -p -o tcp_sendspace=65536
/usr/sbin/no -p -o tcp_recvspace=65536
/usr/sbin/no -p -o rfc1323=1
/usr/sbin/no -r -o ipqmaxlen=512
/usr/sbin/no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500
/usr/sbin/no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500
6),ssh信任关系设置
ln -s /usr/bin/ksh /bin/bash
mkdir -p /usr/local/bin
ln -s /usr/bin/ssh-keygen /usr/local/bin/ssh-keygen
注意：11203的ssh配置有BUG，解决方法是：先用11201的软件配置好ssh，在安装11203软件。
ln -s /etc/ssh /usr/local/etc
ln -s /usr/bin /usr/local/bin
startsrc -s sshd
vi /etc/ssh/sshd_config.
#LoginGraceTime 2m
LoginGraceTime 0
四,环境变量
grid
export ORACLE_BASE=/oracle/grid
export ORACLE_HOME=/oracle/asm
export ORACLE_SID=+ASM1
export NLS_LANG=AMERICAN_AMERICA.UTF8
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export PATH=.:${PATH}:$ORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin
export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
set -o vi
echo ORACLE_BASE=$ORACLE_BASE
echo ORACLE_HOME=$ORACLE_HOME
echo ORACLE_SID=$ORACLE_SID
oracle
export ORACLE_BASE=/oracle/db
export ORACLE_HOME=$ORACLE_BASE/product/11.2
export ORACLE_SID=otpsc2
export NLS_LANG=AMERICAN_AMERICA.zhs16gbk
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:$ORACLE_BASE/common/oracle/bin
export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
export ORACLE_BDUMP=
set -o vi
echo ORACLE_BASE=$ORACLE_BASE
echo ORACLE_HOME=$ORACLE_HOME
echo ORACLE_SID=$ORACLE_SID
五,设置磁盘信息
*****************************************************************
powermt display dev=all |grep Pseudo 查看共享盘 EMC
mpio_get_config -Av 查看共享盘IBM DS4000/DS5000
需要确认共享盘是否正常
1.lspv
共享盘不能有PVid
2.查看磁盘内容：lquerypv -h /dev/rhdisk*
必须为空
*****************************************************************
查看磁盘属性
lsattr -El hdiskpower0 | grep reserve_
lsattr -El hdiskpower1 | grep reserve_
lsattr -El hdisk3 | grep reserve_
lsattr -El hdisk4 | grep reserve_
lsattr -El hdisk5 | grep reserve_
lsattr -El hdisk6 | grep reserve_
lsattr -El hdisk7 | grep reserve_
如果上面查看的结果是reserve_policy
chdev -l hdisk3 -a reserve_policy=no_reserve
chdev -l hdisk4 -a reserve_policy=no_reserve
chdev -l hdisk5 -a reserve_policy=no_reserve
chdev -l hdisk6 -a reserve_policy=no_reserve
chdev -l hdisk7 -a reserve_policy=no_reserve
如果上面查看的结果是reserve_lock
chdev -l hdiskpower0 -a reserve_lock=no
chdev -l hdiskpower1 -a reserve_lock=no
chdev -l hdiskpower2 -a reserve_lock=no
chdev -l hdiskpower3 -a reserve_lock=no
chdev -l hdiskpower4 -a reserve_lock=no
chdev -l hdiskpower5 -a reserve_lock=no
chdev -l hdiskpower6 -a reserve_lock=no
2)，设置pv属主
chown grid:dba /dev/rhdiskpower0
chown grid:dba /dev/rhdiskpower1
chown grid:dba /dev/rhdiskpower2
chown grid:dba /dev/rhdiskpower3
chown grid:dba /dev/rhdiskpower4
chown grid:dba /dev/rhdiskpower5
chown grid:dba /dev/rhdiskpower6
chown grid:dba /dev/rhdiskpower7
3)，设置pv权限
chmod 660 /dev/rhdiskpower0
chmod 660 /dev/rhdiskpower1
chmod 660 /dev/rhdiskpower2
chmod 660 /dev/rhdiskpower3
chmod 660 /dev/rhdiskpower4
chmod 660 /dev/rhdiskpower5
chmod 660 /dev/rhdiskpower6
5.检查
lsattr -El sys0 -a fullcore
/usr/sbin/no -a | fgrep ephemeral
lsuser -a capabilities grid
lsuser -a capabilities oracle
prtconf |grep Memory
lsps -a
df -g /tmp
oslevel -r
getconf HARDWARE_BITMODE
id oracle
id grid
id nobody
ioo -a
no -a |grep udp
no -a |grep tcp
lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.libperfstat bos.perf.perfstat bos.perf.proctools rsct.basic.rte rsct.compat.clients.rte xlC.aix61.rte gpfs.base
确定HACMP卸载
./runcluvfy.sh stage -pre crsinst -n node1,node2 -fixup -verbose
6.oracle 软件准备
6.1安装grid
(1)ftp上传/pkg/oracle/11203（版本号）
#chmod 777 /pkg/oracle/11203
(2)授权，并用grid用户解开软件
#chown grid:dba aix.ppc64_11gR2_grid.zip
$./unzip_aix aix.ppc64_11gR2_grid.zip
(3)开始安装
# /usr/sbin/slibclean
#./rootpre.sh
注意以下命名规则：
cluster name: hostname-cluster
scan name:hostname-scan
***********************************************
磁盘组名字为：ASMCRS
磁盘冗余类型选择:外部冗余
安装完成GRID软件后创建数据库ASM磁盘组
磁盘组名字为：ASMVG1
磁盘冗余类型选择:外部冗余
***********************************************
6.2安装oracle
(1)ftp上传/pkg/oracle/11203（版本号）
(2)授权，并用oracle用户解开软件
#chown oacle:dba aix.ppc64_11gR2_database_1of2.zip
#chown oacle:dba aix.ppc64_11gR2_database_2of2.zip
$./unzip_aix aix.ppc64_11gR2_database_1of2.zip
$./unzip_aix aix.ppc64_11gR2_database_2of2.zip
安装过程时养成日志跟踪的习惯，不但可以更好的了解安装过程，而且在有错误时可以进行准确的分析。
1.tail -f /tmp/oraInstallYYYY-MM-DD-hh-mm-AM/PM
2.tail -f /oracle/oraInventory/logs/InstallActionsYYYY-MM-DD-hh-mm-AM/PM.log
root用户运行./rootpre.sh
oracle用户运行./runInstaller
执行“runInstaller”调出安装界面
1）Configure Security Updates这一步骤，要求提供一个邮箱地址，这里我们选择不提供，点击“Next”。点击“Yes”确认不提供邮箱地址；
2）Installation Option这一步骤，保持默认选项“Create and configure a database”不变，点击“Next”；
3）System Class这一步骤，细节发现：每一个选项前面都有一个带问号的黄色小灯泡，鼠标悬停在上面可以得到简略的提示信息，如果想得到详细的提示信息还可以点击 “more”。我们这里选择“Sever Class”，可以通过此选项得到更多的可配置项，另外一个选项“Desktop Class”是供快速简易部署使用的。点击“Next”；
4）Grid Options这一步骤，选择默认的“Single instance database installation”,点击“Next”；
5）Install Type这一步骤，默认是“Typical install”，这里选择了“Advanced Install”，点击“Next”；
6）Product Languages这一步骤，可以添加Chinese语言，也可以使用默认设置“English”，点击“Next”；
7）Database Edition这一步骤，有三个选项，我们选择默认的“Enterprise Edition”选项，点击“Next”；
8）Installation Location这一步骤，保持默认读取到的环境变量不变即可，点击“Next”；
9）Create Inventory这一步骤，保持默认，点击“Next”；
10）Prerequisite Checks这一步骤，Oracle将全面检查之前的配置是否满足安装需求；
11）Summary这一步骤，能够看到这一步，基本上开始安装就没有什么问题了。点击“Finish”进入正式安装阶段；
12）Install Product 安装数据库，然后就会看到我们非常熟悉的提示手工执行两个脚本，在root用户下分别执行一下
13）在root用户下执行脚本
/oracle/oraInventory/orainstRoot.sh
/oracle/product/11.2.0/root.sh
14)返回安装界面，点击OK，弹出finish界面，完成安装.
8.建库
oracle用户运行dbca
建库的时候日志会记录在这里
tail -f /oracle/diag/rdbms/ORACLE_SID/ORACLE_SID/trace/alter_ORACLE_SID.log
在安装不同的组件的时候日志会记录到这里，不同组件对应不同的名字。
tail -f /oracle/Cfgtoollogs/dbca/<db_name>
界面 操作
-----------------------------------------------------------------------------------------------------------------------------------------------------
select operation 选择"create a Database"
database Templates 选择"Custon Database"
Specify Database Identifiers 输入数据库db name和sid
Specify Management Options 关闭OEM
Database Credentials 设置数据库系统用户的密码
Specify Database Storage Options 数据文件是文件系统或者裸设备安装都选择 file system方式
Specify Recovery Options 暂不选择开启flashback功能
Database components 建议都去掉，除非有具体需求
Initialization Parameters
memory
1. 建议设置memory_targe为0，而采用SGA，PGA自动管理的方式。在联机事务处理系统中，若数据库服务器物理内存大小在8G（含）以下，则给数据库分配的内存SGA+PGA值为服务器物理内存的50%。对于内存在8至32G（含）的服务器来说，给数据库分配的SGA+PGA值为服务器物理内存的50%。对于物理内存在32G以上的数据库服务器，给数据库分配内存SGA+PGA为数据库物理内存的60%。
在应用无特殊要求的情况下，内存分配默认如下：
8G物理内存： SGA=3G PGA=1G
16G物理内存：SGA=6G PGA=2G
32G物理内存：SGA=16G PGA=8G(如果物理内存等于32G，sga=16g,pga=4g)
64G物理内存：SGA=36G PGA=12G
128G物理内存：SGA=72G PGA=24G
256G物理内存：SGA=144G PGA=48G
2. 自动分配大小的SGA组件配置
若应用系统无特殊要求，数据库安装时将共享池shared_pool_size、大型池large_pool_size、java_pool_size、db_cache_size参数值设为0，让oracle自身进行自动管理。
3. 进程数设置，一般为2500
4. 生产数据库字符集须依据应用系统特性来定义，推荐的数据库字符集为ZHS16GBK。国家语言字符集为AL16UTF16。
5. 连接方式为独占模式。
6. 数据块默认大小8K。
8.配置监听
tnsnames.ora
ora11 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 110.200.1.222)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora11)
(SERVER = DEDICATED)
)
)
9.数据库配置修改
--(1)exp 空表bug
alter system set deferred_segment_creation = FALSE;
--(2)profile限制
alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;
alter profile default limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
alter profile default limit PASSWORD_LOCK_TIME UNLIMITED;
alter profile default limit PASSWORD_GRACE_TIME UNLIMITED;
--(3)关闭审计功能
alter system set audit_trail=none scope=spfile;
--(4)关闭自动扩展
select 'alter database datafile '''|| name ||''' autoextend off;' from v$datafile;
select 'alter database tempfile '''|| name ||''' autoextend off;' from v$tempfile;
--(5)修改数据库参数
alter system set open_cursors=2500 sid='*' scope=both;
alter system set db_files=2000 sid='*' scope=spfile;
alter system set "_gc_read_mostly_locking"=false sid='*' scope=spfile; --(规避GC通信BUG，BUG现象GC通信失败导致一个节点重启);
alter system set "_serial_direct_read"='NEVER' sid='*'; --(关闭直接路径加载)
alter system set event ='28401 TRACE NAME CONTEXT FOREVER,LEVEL 1:60025 TRACE NAME CONTEXT FOREVER:10949 TRACE NAME CONTEXT FOREVER' scope=spfile; --(设置事件28401-版本11G)
alter system set parallel_force_local=true scope=spfile sid='*'; --(关闭跨实例并行)
alter system set max_dump_file_size='100M' scope=spfile sid='*'; --(设置dump文件大小上限)
alter system set "_partition_large_extents"=false scope=spfile sid='*'; --(调整默认分区大小)
alter system set control_file_record_keep_time=31 scope=spfile sid='*'; --(设置控制文件的保留时间)
alter system set result_cache_max_size=0 scope=spfile sid='*'; --(关闭result_cache)
alter system set sec_case_sensitive_logon=false sid='*' scope=spfile ; --(不区分密码大小写)
alter system set parallel_max_servers=40 sid='*' scope=spfile;
alter system set parallel_min_servers=0 sid='*' scope=spfile;
alter system set archive_lag_target=1200 sid='*' scope=spfile; --(强制日志切换)
alter system set "_highthreshold_undoretention"=70000 scope=both;
alter system set "_optim_peek_user_binds"=FALSE scope=both;
------------------------------关闭自适应游标共享------------------------------------------
alter system set "_optimizer_adaptive_cursor_sharing"=false sid='*' scope=spfile;
alter system set "_optimizer_extended_cursor_sharing"=none sid='*' scope=spfile;
alter system set "_optimizer_extended_cursor_sharing_rel"=none sid='*' scope=spfile;
------------------------------------------------------------------------------------------
alter system set "_optimizer_use_feedback"=false sid ='*' scope=spfile; --(关闭Cardinality feedback)
alter system set "_use_adaptive_log_file_sync"=false sid='*' scope=spfile; --(恢复LGWR的post/wait通知方式)
------------------------------关闭DRM新特性-----------------------------------------------
alter system set "_gc_undo_affinity"=false sid='*' scope=spfile;
alter system set "_gc_policy_time"=0 sid='*' scope=spfile;
------------------------------------------------------------------------------------------
alter system set "_optimizer_join_factorization"=false sid='*' scope=spfile;
alter system set "_undo_autotune"=false sid='*' scope=spfile; --(关闭UNDO自动调整)
alter system set "_use_adaptive_log_file_sync"=false scope=both sid='*';
alter system set "_long_log_write_warning_threshold"=550 scope=both sid='*';
alter system set streams_pool_size=150M scope=spfile sid='*';
设置ASM memory_target参数
*****************************************************************************
如果物理内存为16G时，memory_max_target=1024m，memory_target=1024m
如果物理内存为32G时，memory_max_target=1536m，memory_target=1536m
如果物理内存为64G时，memory_max_target=2048m，memory_target=1536m
如果物理内存为128G时，memory_max_target=4096m，memory_target=1536m
如果物理内存为256G或大于256G时，memory_max_target=4096m，memory_target=1536m
*****************************************************************************
例子：
su - grid
sqlplus / as sysasm
alter system set memory_max_target=1536m scope=spfile;
alter system set memory_target=1536m scope=spfile;
--(6)关闭OCM功能
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');
select JOB_NAME,ENABLED from dba_scheduler_jobs where job_name in ('MGMT_CONFIG_JOB','MGMT_STATS_CONFIG_JOB');
--(7)添加用户及表空间方法
create tablespace tablespace_name datafile ''/dev/rDBNAME_data01'' size 20000m,'/dev/rDBNAME_data02' size 20000m;
create user username identified by username;
alter user username default tablespace tablespace_name;
grant connect to username;
grant resource to username;
--(8)开机自动开启crs（默认）
./crsctl enable crs（开启自动开启CRS命令）
--(9)关闭EM
emctl status dbconsole
emctl stop dbconsole
--(10)启动GSD:
srvctl enable nodeapps -g
srvctl start nodeapps
--(11)RAC集群的话，注意RMAN CONFIGURE SNAPSHOT CONTROLFILE 改到ASM上 ##注意为当前数据库实例##
例：把以下snapcf_racdb1.f文件路径改为放置ASM磁盘上,并且删除实例名后的节点号改为snapcf_racdb.f
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/db/product/11.2/dbs/snapcf_racdb1.f'; # default
以上语句改为
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+ASMVG1/snapcf_racdb.f';
--(12)新上线系统创建QUEST_FG、HXB,用户，表空间使用用户应用表空间并赋权操作
create user hxb identified by hxb;
grant connect to hxb;
grant select any table to hxb;
grant select any dictionary to hxb;
--脚本位置55上C:\ccs勿删除\ORACLE11gforaix安装\授权脚本
create user tivoli identified by qwer1234 default tablespace sysaux;
grant SELECT ANY DICTIONARY,connect to tivoli;
grant CREATE PROCEDURE TO tivoli;
grant advisor to tivoli;
grant execute on sys.dbms_workload_repository to tivoli;
grant execute on sys.dbms_sqltune to tivoli;
grant execute on sys.dbms_advisor to tivoli;
grant select on sys.obj$ to tivoli;
grant select on sys.ind$ to tivoli;
grant select on sys.icol$ to tivoli;
grant select on sys.indpart$ to tivoli;
grant select on sys.indcompart$ to tivoli;
grant select on sys.indsubpart$ to tivoli;
grant select on sys.ts$ to tivoli;
grant select on sys.hist_head$ to tivoli;
grant select on sys.user$ to tivoli;
@/home/oracle/scripts/tivoli_gp.sql
create user quest_fg identified by foglight default tablespace sysaux;
grant select any table to quest_fg;
grant select any dictionary to quest_fg;
grant connect to quest_fg;
@/home/oracle/scripts/quest_gp.sql
--(13)创建sjwh用户，赋予权限
create user sjwh identified by sjwh default tablespace users;
grant
alter database link,
drop public database link,
create any sequence,
alter any sequence,
drop any sequence,
select any sequence,
create any synonym,
drop any synonym,
create any trigger,
alter any trigger,
drop any trigger,
create any view,
DROP ANY VIEW,
connect ,
resource,
CREATE TABLE,
CREATE ANY TABLE,
ALTER ANY TABLE,
BACKUP ANY TABLE,
DELETE ANY TABLE,
DROP ANY TABLE,
INSERT ANY TABLE,
LOCK ANY TABLE,
SELECT ANY TABLE,
FLASHBACK ANY TABLE,
UPDATE ANY TABLE,
CREATE ANY INDEX,
ALTER ANY INDEX,
DROP ANY INDEX
to sjwh;
grant
CREATE PROCEDURE ,
CREATE ANY PROCEDURE,
ALTER ANY PROCEDURE,
DROP ANY PROCEDURE,
EXECUTE ANY PROCEDURE,
CREATE LIBRARY,
CREATE ANY LIBRARY,
ALTER ANY LIBRARY,
EXECUTE ANY LIBRARY
to sjwh;
grant select any table to sjwh;
grant select on "SYS"."V_$SESSION" to sjwh;
grant select on dba_segments to sjwh;
grant select_catalog_role to sjwh;
grant analyze any to sjwh;
grant select on dict to sjwh;
grant select on SYS.DBA_SEGMENTS to SJWH;
grant select on SYS.V_$SESSION to SJWH;
grant connect to SJWH;
grant resource to SJWH;
grant select_catalog_role to SJWH;
grant alter any index to SJWH;
grant alter any library to SJWH;
grant alter any procedure to SJWH;
grant alter any sequence to SJWH;
grant alter any table to SJWH;
grant alter any trigger to SJWH;
grant alter database link to SJWH;
grant analyze any to SJWH;
grant create any index to SJWH;
grant create any library to SJWH;
grant create any procedure to SJWH;
grant create any sequence to SJWH;
grant create any synonym to SJWH;
grant create any table to SJWH;
grant create any trigger to SJWH;
grant create any view to SJWH;
grant create library to SJWH;
grant create procedure to SJWH;
grant delete any table to SJWH;
grant drop any index to SJWH;
grant drop any procedure to SJWH;
grant drop any sequence to SJWH;
grant drop any synonym to SJWH;
grant drop any table to SJWH;
grant drop any trigger to SJWH;
grant drop any view to SJWH;
grant drop public database link to SJWH;
grant execute any library to SJWH;
grant execute any procedure to SJWH;
grant insert any table to SJWH;
grant select any sequence to SJWH;
grant unlimited tablespace to SJWH;
grant update any table to SJWH;
grant comment any table to SJWH;
grant select any dictionary to sjwh;
10.添加HA脚本
su - oracle -c "srvctl stop instance -d dzxj -i dzxj1"
su - root -c "/oracle/asm/bin/crsctl stop crs"
su - oracle -c "srvctl stop instance -d dzxj -i dzxj2"
su - root -c "/oracle/asm/bin/crsctl stop crs"
su - root -c "/oracle/asm/bin/crsctl start crs"
sleep 120
su - oracle -c "srvctl start instance -d dzxj -i dzxj1"
su - root -c "/oracle/asm/bin/crsctl start crs"
sleep 120
su - oracle -c "srvctl start instance -d dzxj -i dzxj2"
11.归档
create pfile='/home/oracle/topsdbdb1.ora' from spfile;
create pfile='/home/oracle/topsdbdb2.ora' from spfile;
alter system set log_archive_dest_1='location=/archivelog' scope=spfile sid='oradb1';
alter system set log_archive_dest_1='location=/archivelog' scope=spfile sid='oradb2';
shutdown immeidate;
startup mount;
alter database archivelog;
su - oracle
sqlplus / as sysdba
ALTER DATABASE FORCE LOGGING;
查看是否开启强制日志.
select force_logging from gv$database;
12.RMAN备份
RMAN备份统一脚本
脚本名 /home/oracle/scripts/rman_full_db.sh
脚本内容(10G) （当系统为Linux时候，需要改为./home/oracle/.bash_profile)
#!/bin/ksh
. /home/oracle/.profile
export ORACLE_SID=XXX
echo "******Begin backup*****";date
$ORACLE_HOME/bin/rman target / <<EOF
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rmanbak/%F';
run {
allocate channel c1 device type disk maxpiecesize=5g connect sys/oracle@ins1;
allocate channel c2 device type disk maxpiecesize=5g connect sys/oracle@ins1;
backup full database filesperset 3 format '/rmanbak/full_%U_%d_%T';
release channel c1;
release channel c2;
allocate channel c1 device type disk maxpiecesize=5g connect sys/oracle@ins1;
allocate channel c2 device type disk maxpiecesize=5g connect sys/oracle@ins1;
allocate channel c3 device type disk maxpiecesize=5g connect sys/oracle@ins2;
allocate channel c4 device type disk maxpiecesize=5g connect sys/oracle@ins2;
sql 'alter system archive log current';
backup archivelog all filesperset 5 format '/rmanbak/arch_%U_%d_%T'delete input;
backup current controlfile format '/rmanbak/controlfile_%u_%d_%T';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
allocate channel for maintenance device type disk connect sys/oracle@ins1;
allocate channel for maintenance device type disk connect sys/oracle@ins2;
crosscheck backup;
list expired backup;
delete noprompt obsolete;
release channel;
exit;
EOF
echo "**********end backup*************"
date
crontab 内容
10 1 * * * /home/oracle/scripts/rman_full_db.sh >> /home/oracle/scripts/rman_full_db.log 2>&1
chmod u+x rman_full_db.sh
部署oswbb部署
部署文档和介质在\\103.2.4.45\d\数据库文档（勿删除）\lw\ORACLE11gforaix安装\oswbb部署
更改AWR保存时间为14天
exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>20160);
set line 150 pagesize 150
col snap_interval for a30
col retention for a30
select * from dba_hist_wr_control;
增加JAVAOUT
若报错，未安装java组件，执行下面两个脚本：
cd $ORACLE_HOME
SQL>@?/javavm/install/initjvm.sql
SQL>@?/rdbms/admin/catjava.sql
*********************************************用SYS用户执行*******************************************************
exec dbms_Java.grant_Permission('TIVOLI','SYS:java.io.FilePermission', '<<ALL FILES>>', 'read , execute');
exec dbms_java.grant_permission('TIVOLI','java.lang.RuntimePermission','*','writeFileDescriptor' );
*****************************************************************************************************************
*************************************************************tivoli用户执行*******************************************************
drop java source runcmdoutput;
create and compile java source named runcmdoutput as
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.sql.CLOB;
public class RunCmdOutPut{
private static StringBuffer copyStream(InputStream inputStream, OutputStream outStream, String type) throws IOException {
byte[] bytes = new byte[1024];
int len = 0;
StringBuffer strbuf = new StringBuffer();
while ((len = inputStream.read(bytes)) != -1) {
strbuf.append((new String(bytes,0,len)));
}
return strbuf;
}
public static CLOB exec(String cmd) {
try {
String[] cmds = {"/bin/sh","-c",cmd};
String[] envp = {"~=/home/oracle"};
Process pc = Runtime.getRuntime().exec(cmds,envp);
StringBuffer strbuf = copyStream(pc.getInputStream(), System.out, "===OutputStream===");
StringBuffer strbuf2 = copyStream(pc.getErrorStream(), System.out, "===ErrorStream===");
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
CLOB clob = oracle.sql.CLOB.createTemporary((Connection) conn, true, 1);
clob.putString(1, strbuf.toString());
return clob;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
};
/
drop function f_getres;
create FUNCTION f_getres(cmd string) return clob as
language java name 'RunCmdOutPut.exec(java.lang.String) return oracle.sql.CLOB';
/
*************************************************************************************************************************************
查看bug 18235390是否需要打补丁
查询视图dba_tablespace_usage_metrics,v$asm_diskgroup_stat,x$kttets（例：查询均为select * from dba_tablespace_usage_metrics）
如果查询时后台日志报ORA-1000或ORA-600错误时候。
需要打PATCH 18235390补丁
修改自动维护任务时间窗口脚本在\\103.2.4.45\d\数据库文档（勿删除）\lw\ORACLE11gforaix安装\自动维护任务.txt --按实际需求修改
关闭11G自动计划任务auto space advisor,sql tuning advisor
查看自动维护任务的详细信息
select client_name,status,attributes,window_group,service_name from dba_autotask_client;
begin
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
begin
dbms_auto_task_admin.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/
禁用ora.crf
禁用ora.crf，避免osyslogd进程大量吃系统资源（如CPU）
crsctl stat res -t -init查看是否禁用
以root用户执行以下语句：
/oracle/asm/bin/crsctl stop res ora.crf -init
/oracle/asm/bin/crsctl modify res ora.crf -attr "AUTO_START=never" -init
部署（路径要求为/oracle/asm/rdbms/audit一致）
增加crontab 内容 针对grid用户/oracle/asm/rdbms/audit下产生过度*.aud文件
su - grid
crontab -e
0 2 * * * /usr/bin/find /oracle/asm/rdbms/audit -name '*.aud' -mtime +7 | xargs rm -rf
(每日晚上2点，删除/oracle/asm/rdbms/audit下*.aud 7天前的审计文件）
增加ITM对tnsnames.ora文件和alert日志文件的o+r读权限
增加对alert日志的读权限，##两节点##分别运行
su - oracle
sqlplus / as sysdba
select 'chmod o+rx '||substr(value,'0',instr(value ,'/',-1,1) )||'' value1 from v$parameter where name like 'user_dump_dest'
union
select 'chmod o+rx '||substr(value,'0',instr(value ,'/',-1,2) )||'' value1 from v$parameter where name like 'user_dump_dest'
union
select 'chmod o+rx '||value||'' value4 from v$parameter where name like 'user_dump_dest'
union
select 'chmod o+rx '||value||'/alert*' value5 from v$parameter where name like 'user_dump_dest' ;
exit
cd $ORACLE_HOME/network/admin
chmod o+rx tnsnames.ora
############
#log_buffer#
############
查看内存大小，如果系统内存大于100G 调整log_buffer=32M 大小
11.2.0.4以上版本数据库需打patch 20675347
patch存放位置： \\103.2.4.45\d\oracle-soft 勿删\20675347
打patch文档位置： \\103.2.4.45\d\数据库文档（勿删除）\oracle_psu.txt
####################
##记录HAIP功能操作##
####################
############
#安装完毕后#
############
需要记录安装信息到 管理助手 安装日期，安装人员等信息.
crs卸载
crs卸载
--用ROOT用户在集群的第一个节点执行
/oracle/asm/crs/install/rootcrs.pl -verbose -deconfig -force
--在ROOT用户集群的最后一个节点执行
/oracle/asm/crs/install/rootcrs.pl -verbose -deconfig -force -lastnode
--用GRID用户在任意节点执行
/oracle/asm/deinstall/deinstall
--用ORACLE用户在任意节点执行
/oracle/db/product/11.2/deinstall/deinstall
rm -rf /var/tmp/.oracle
rm -rf /usr/tmp/.oracle
rm -rf /tmp/.oracle
rm -rf /etc/ora*
rm -rf /var/tmp/.oracle
rm -rf /oracle/*
rm -rf /etc/init.cssd
rm -rf /etc/init.crs
rm -rf /etc/init.crsd
rm -rf /etc/init.evmd
rm -rf /etc/rc.d/rc2.d/K96init.crs
rm -rf /etc/rc.d/rc2.d/S96init.crs
rm -rf /etc/oracle/scls_scr
rm -rf /etc/oracle/oprocd
rm -rf /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
rm -rf /opt/ORCL*
rm -rf /usr/local/bin/oraenv
rm -rf /usr/local/bin/dbhome
rm -rf /usr/local/bin/dbhome
dd if=/dev/zero of=/dev/rhdiskpower0 bs=1024 count=2560
dd if=/dev/zero of=/dev/rhdiskpower1 bs=1024 count=2560
dd if=/dev/zero of=/dev/rhdiskpower2 bs=1024 count=2560
dd if=/dev/zero of=/dev/rhdiskpower3 bs=1024 count=2560
dd if=/dev/zero of=/dev/rhdiskpower4 bs=1024 count=2560
dd if=/dev/zero of=/dev/rhdiskpower5 bs=1024 count=2560
dd if=/dev/zero of=/dev/rhdiskpower6 bs=1024 count=2560
查看磁盘内容：lquerypv -h /dev/rhdisk2
查看PV大小：bootinfo -s hdisk2
prtconf |grep disk 查看共享盘
powermt display dev=all |grep Pseudo 查看共享盘 EMC
mpio_get_config -Av 查看共享盘IBM DS4000/DS5000
杀掉正在使用目录的用户
fuser -kxuc /oracle
###################################数据库软件重新安装，重新注册ASM上的数据库#################
步骤1：MOUNT ASM磁盘
su - grid
sqlplus / as sysasm
alter system mount group asmvg1;
步骤2：注册服务
srvctl add database -d orcl -o <$ORACLE_HOME>
srvctl add instance -d orcl -i orcl1 -n <主机名>
srvctl add instance -d orcl -i orcl2 -n <主机名>
