oracle10g rac搭建dg,RAC上配置DG

这段时间闲来无事，打算把这段时间在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

oraclesoftnproc2047

oraclehardnproc16384

oraclesoftnofile1024

oraclehardnofile65536

修改安全限制

[root@dg ~]# vi /etc/pam.d/login

增加如下内容

# Oracle Settings

sessionrequired/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.ctlcontrol02.ctlcontrol03.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=30VALID_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 NAMESWITCHOVER_STATUSDB_UNIQUE_NAMEDATABASE_ROLEOPEN_MODE

---------- --------- -------------------- ------------------------------ ---------------- ----------

773961569 RACDBSESSIONS ACTIVEpridbPHYSICAL 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>altersystem set fal_client='racdb1' sid='*';

System altered.

SQL>altersystem set fal_client='racdb1' sid='racdb1';

System altered.

SQL> altersystem 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=spfilesid='*';

创建主库的standby logfile

SQL> alter database add standby logfilethread 1 group 5 ('+LOGVG') size 50m,group 6 ('+LOGVG') size 50m,group 7 ('+LOGVG') size 50m;

Database altered.

SQL> alter database add standby logfilethread 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;

[i]在此学习过程中感谢楚天楚大人指点，本人将陆续跟新一些OGG，DATAGUARD在RAC上的配置经验，和调优经验 欢迎大家指点错误。QQ(231816133)