这段时间闲来无事,打算把这段时间在RAC的一些应用配置整理一下。包括OGG,dataguard这些 今天先发一个 日后会继续更新的。关于这篇文章也就是搭个实验环境,真正的生产库还需要根据实际环境修改参数。
ORACLE RAC + DATA GUARD 配置
一.配置信息
RAC
hostname | RAC1 | RAC2 |
Public ip | 172.16.30.11 | 172.16.30.12 |
Private ip | 192.168.122.11 | 192.168.122.12 |
vip | 172.16.30.101 | 172.16.30.102 |
Sid | racdb1 | racdb2 |
软件安装目录 | /u01/oracle/product/10.2.0/db_1 | /u01/oracle/product/10.2.0/db_1 |
Strong | ASM | ASM |
DG
hostname | dg |
Ip | 172.16.30.93 |
Sid | pridb |
软件安装目录 | /u01/oracle/product/10.2.0/db_1 |
Strong | /u01/oradata |
二.安装步骤
Step 1 在DG上配置oracle用户
1. 调整/etc/sysctl.conf内核参数
[root@rac1 ~]# vi /etc/sysctl.conf
增加和修改如下内容
# Oracle Settings
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=262144
注意: 1)
fs.file-max = 65536该参数表示在linux系统上可以打开的文件数量
kernel.sem = 250 32000 100 128四个参数一次表示是每个用户拥有信号量最大数,系统信号 量最大数,每次semopm系统调用操作数,系统信号量集数最大数。此四参数 为固定数,不需要修改。
kernel.shmall=2097152 该参数表示系统一次可以使用的共享内存总量(以页为单位)。缺省 量为2097152,通常不需要修改,安这个计算2097152*4k(页大小) /1024/1024=8g
kernel.shmmax = 2147483648该参数定义了共享内存段的最大尺寸(以字节为单位),缺省为 32M,对于oracle来说太小,这里设置为2G
kernel.shmmni = 4096该参数定义了共享内存段的最小尺寸,通常不需要修改。
使相关参数生效
[root@dg ~]# /sbin/sysctl -p
2. 提高oracle用户的Shell限制
设置oracle使用的文件数权限
[root@dg ~]# vi /etc/security/limits.conf
增加如下内容
# Oracle Settings
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
修改安全限制
[root@dg ~]# vi /etc/pam.d/login
增加如下内容
# Oracle Settings
session required /lib/security/pam_limits.so
3. 创建oracle用户及相应组oinstall, dba
[root@dg ~]# /usr/sbin/groupadd oinstall
[root@dg ~]# /usr/sbin/groupadd dba
[root@dg ~]# useradd -d /home/oracle -g oinstall -G dba oracle
[root@dg ~]# passwd oracle
[root@dg ~]# id oracle
[root@dg ~]# id nobody
4. 创建Oracle基本目录及其权限
[root@dg ~]# mkdir -p /u01/oracle
[root@dg ~]# mkdir -p /u01/oradata
[root@dg ~]# chown -R oracle.oinstall /u01
[root@dg ~]# chmod -R 775 /u01
5. 修改oracle用户的初始参数文件
[root@rac1 ~]# vi /home/oracle/.bash_profile
增加如下内容
# Oracle RAC Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
umask 022
ulimit -u 16384 -n 65536
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=pridb; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
#LD_ASSUME_KERNEL=2.4.19; export LD_ASSUME_KERNEL
使此文件参数生效
[root@rac1 ~]# source /home/oracle/.bash_profile
Step 2 安装ORACLE 10G。
安装oracle10G软件,并升级到与RAC相同的版本,运行netca创建监听。
在dg上创建所需目录
[oracle@dg install]$ mkdir -p $ORACLE_BASE/oradata/pridb
[oracle@dg install]$ mkdir -p $ORACLE_BASE/admin/pridb/adump
[oracle@dg install]$ mkdir -p $ORACLE_BASE/admin/pridb/bdump
[oracle@dg install]$ mkdir -p $ORACLE_BASE/admin/pridb/cdump
[oracle@dg install]$ mkdir -p $ORACLE_BASE/admin/pridb/dpdump
[oracle@dg install]$ mkdir -p $ORACLE_BASE/admin/pridb/hdump
[oracle@dg install]$ mkdir -p $ORACLE_BASE/admin/pridb/udump
[oracle@dg install]$ mkdir -p $ORACLE_BASE/admin/pridb/pfile
修改主库成为force logging模式
SQL> alter database force logging;
修改备库的tnsnames.ora文件,使得两边能互相访问。
添加下段到rac1 rac2的tnsnames.ora文件下 在将整个文件scp到dg下。
PRIDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.30.93)(PORT = 1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SID=pridb)
)
)
请确保备库的tnsname.ora文件上有
RACDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb1)
)
)
修改备库的listener.ora 使其让主库可以监听它,添加红色字段。
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME=pridb)
(ORACLE_HOME= /u01/oracle/product/10.2.0/db_1)
(SID_NAME=pridb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
Step 3 生成物理库所需的配置文件
1.生成密码文件
orapwd file=orapwpridb password=sys
scp orapwpridb dg:/u01/oracle/product/10.2.0/db_1/dbs
2.生成standby控制文件
SQL> alter database create standby controlfile as '/u01/control01.ctl';
[oracle@rac1 dbs]$ scp /u01/control01.ctl dg:/u01/oracle/oradata/pridb
[oracle@dg pridb]$ cp control01.ctl control02.ctl
[oracle@dg pridb]$ cp control01.ctl control03.ctl
[oracle@dg pridb]$ ls
control01.ctl control02.ctl control03.ctl
3.生成所需的pfile。
在rac1上执行
SQL> create pfile='/u01/initpridb.ora' from spfile;
修改参数文件
[oracle@rac1 u01]$ more initpridb.ora
racdb1.__db_cache_size=260046848
racdb2.__db_cache_size=281018368
racdb1.__java_pool_size=4194304
racdb2.__java_pool_size=4194304
racdb1.__large_pool_size=4194304
racdb2.__large_pool_size=4194304
racdb1.__shared_pool_size=163577856
racdb2.__shared_pool_size=150994944
racdb1.__streams_pool_size=8388608
racdb2.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/racdb/adump'
*.background_dump_dest='/u01/oracle/admin/racdb/bdump'
*.cluster_database_instances=2
racdb1.cluster_database_instances=2
racdb2.cluster_database_instances=2
*.cluster_database=TRUE
*.compatible='10.2.0.1.0'
*.control_files='+SYSTEMVG/racdb/controlfile/current.260.776344801'
*.core_dump_dest='/u01/oracle/admin/racdb/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATAVG1'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='racdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
racdb1.instance_number=1
racdb2.instance_number=2
*.job_queue_processes=10
racdb1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.30.101)(PORT=1521))'
racdb2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.30.102)(PORT=1521))'
racdb1.log_archive_dest_1='LOCATION=+LOGVG'
racdb2.log_archive_dest_1='LOCATION=+LOGVG'
*.open_cursors=300
*.pga_aggregate_target=147849216
*.processes=150
*.remote_listener='LISTENERS_RACDB'
*.remote_login_passwordfile='exclusive'
*.sga_target=443547648
racdb2.thread=2
racdb1.thread=1
*.undo_management='AUTO'
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/u01/oracle/admin/racdb/udump'
修改完成后
*.audit_file_dest='/u01/oracle/admin/pridb/adump'
*.background_dump_dest='/u01/oracle/admin/pridb/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/oradata/pridb/control01.ctl','/u01/oracle/oradata/pridb/control02.ctl','/u01/oracle/oradata/pridb/control03.ctl'
*.core_dump_dest='/u01/oracle/admin/pridb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='racdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u01/oracle/arch'
*.open_cursors=300
*.pga_aggregate_target=147849216
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=443547648
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/pridb/udump'
以下是增加的,应用时请去掉该提示语句。
*.db_unique_name=pridb
*.log_archive_config='dg_config=(racdb,pridb)'
*.log_archive_dest_2='service=racdb1 lgwr async reopen=30 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=racdb';
*.db_file_name_convert='+DATAVG1/RACDB/DATAFILE','/u01/oracle/oradata/pridb','+SYSTEMVG/RACDB/DATAFILE','/u01/oracle/oradata/pridb','+SYSTEMVG/RACDB/TEMPFILE','/u01/oracle/oradata/pridb','+UNDOVG/RACDB/DATAFILE','/u01/oracle/oradata/pridb'
*.log_file_name_convert='+SYSTEMVG/RACDB/ONLINELOG','/u01/oracle/oradata/pridb','+LOGVG/RACDB/ONLINELOG','/u01/oracle/oradata/pridb'
*.standby_file_management=auto
*.standby_archive_dest='/u01/oracle/arch'
*.fal_server='racdb1','racdb2'
*.fal_client='pridb'
Step 4 通过rman创建备份standby库
在主库上RAC1节点创建备份(ASM,用本地磁盘的请自行更改)
[oracle@rac1 u01]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Mar 5 15:39:13 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: RACDB (DBID=773961569)
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> sql 'alter system archive log current';
5> backup current controlfile for standby format='/u01/datadump/control_%U';
6> backup format '/u01/datadump/datafile_%U' skip inaccessible filesperset 5 database;
7> sql 'alter system archive log current';
8> backup format '/u01/datadump/arch_%U' skip inaccessible filesperset 5 archivelog all delete input;
9> release channel c2;
10> release channel c1;
11> }
将备份拷贝到备份库的相同目录下
[oracle@rac1 datadump]$ scp * dg:/u01/datadump
还原备份
[oracle@rac1 datadump]$ rman target / auxiliary sys/sys@PRIDB
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Mar 5 15:45:29 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: RACDB (DBID=773961569)
connected to auxiliary database: RACDB (not mounted)
RMAN> duplicate target database for standby;
通过v$database视图查看是否成功
SQL> select DBID,NAME,SWITCHOVER_STATUS,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE from v$database;
DBID NAME SWITCHOVER_STATUS DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
---------- --------- -------------------- ------------------------------ ---------------- ----------
773961569 RACDB SESSIONS ACTIVE pridb PHYSICAL STANDBY MOUNTED
Step 5 配置主库(RACDB)
添加传输archivelog日志传输路径
SQL> alter system set log_archive_dest_2='service=pridb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=pridb' sid='*';
配置主库dg_config参数
SQL> alter system set log_archive_config='dg_config=(racdb,pridb)' sid='*';
配置fal参数
SQL> alter system set fal_server='pridb' sid='*';
System altered.
SQL> alter system set fal_client='racdb1' sid='*';
System altered.
SQL> alter system set fal_client='racdb1' sid='racdb1';
System altered.
SQL> alter system set fal_client='racdb2' sid='racdb2';
System altered.
配置standby_file_management参数
alter system set standby_file_management=auto sid='*';
配置db_file_name_convert参数
SQL> alter system set db_file_name_convert='/u01/oracle/oradata/pridb','+DATAVG1/RACDB/DATAFILE' scope=spfile sid='*';
配置log_file_name_convert参数
SQL> alter system set log_file_name_convert='/u01/oracle/oradata/pridb','+LOGVG/RACDB/ONLINELOG' scope=spfile sid='*';
创建主库的standby logfile
SQL> alter database add standby logfile thread 1 group 5 ('+LOGVG') size 50m,group 6 ('+LOGVG') size 50m,group 7 ('+LOGVG') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 8 ('+LOGVG') size 50m,group 9 ('+LOGVG') size 50m,group 10 ('+LOGVG') size 50m;
Database altered.
Step 6 配置备库(PRIDB)
创建备库的standby logfile
PS:添加前注意停止复制进程:
alter database recover managed standby database cancel;
alter database add standby logfile
group 5 ('/u01/oracle/oradata/pridb/group5_1') size 50m,
group 6 ('/u01/oracle/oradata/pridb/group6_1') size 50m,
group 7 ('/u01/oracle/oradata/pridb/group7_1') size 50m,
group 8 ('/u01/oracle/oradata/pridb/group8_1') size 50m,
group 9 ('/u01/oracle/oradata/pridb/group9_1') size 50m;
注意要使standby logfile生效必须是async或sync模式的。
Step 7 常用命令
启动复制
Alter database recover managed standby database disconnect from session;
关闭复制
Alter database recover managed standby database cancel;
切换成物理备库
alter database commit to switchover to physical standby with session shutdown;
切换成主备库
alter database commit to switchover to primary with session shutdown;
转载于:https://blog.51cto.com/johnnyfox/798436