环境:
版本 11.2.0.1
1 primary :
name: rac
ip: 10.250.7.226
scan ip:10.250.7.200
使用ASM存储数据
2 standby :
name : yangdb
ip :10.250.7.241
使用filesystem 存储数据
在11gr2 版本中,oracle 提供了duplicate from active database 的功能,也就是可以不用备份源数据库,就可以直接创建standby 或者复制源库!相对于单实例使用duplicate创建物理备库,在创建RAC的standby时 遇到很多错误!
比较多的是:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server
安装完成后遇到:
ORA-00313: open failed for members of log group 3 of thread 2
ORA-00312: online log 3 thread 2: '/opt/oracle/oradata/yangdb/group_3.259.761070477'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
这里先给出整个安装的步骤:
1 在目标库上安装oracle 数据库软件,仅仅安装软件不创建数据库。将rac 节点的密码文件拷贝到目标库,并命重新命名为orapw,保持主库和备库的密码一致
2 创建数据库的对应文件目录:oracle 对应的跟踪文件目录,数据文件存放的目录,闪回恢复区,归档目录
audit_file_dest='/opt/oracle/admin/yangdb/adump'
diagnostic_dest='/opt/oracle'
db_create_file_dest='/opt/oracle/oradata/yangdb'
db_recovery_file_dest='/opt/oracle/flash_recovery_area'
log_archive_dest_1='LOCATION=/opt/oracle/arch'
否则,在重启stanby库的时候会报错说没有对应文件或目录!
3 配置主库和备库的tnsnames.ora文件
# tnsnames.ora Network Configuration File: /opt/oracle/11.2.0/yangdb/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
yangdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.241)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = yangdb)
)
)
rac =
(DESCRIPTION =
(ADDRESS_LIST =
# (ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.226)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = rac)
)
)
在主库中添加备库的tns连接
#scan 对应的IP为:10.250.7.200
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
)
)
yangdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.241)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = yangdb)
)
)
主库和备库配置好以后,要使用tnsping 命令查看是否能够ping通(在我的实践中,tnsping能通过,但是却报错ORA-17629: Cannot connect to the remote database server),最后重启监听!生产环境中可以使用lsnrctl 的reload命令。
我在搭建的过程中,实际在oracle的TNS_HOME/admin 下的tnsnams.ora 和grid 的NET_HOME/admin 下的tnsnames.ora 分别都添加了备库的tns 连接!!
4 创建备库的参数文件,非常简单只需要一个db_name参数就可以了
oracle@rac3:/opt/oracle/11.2.0/yangdb/dbs>cat inityangdb.ora
*.db_name=rac
使用duplicate方式创建备库或者复制数据库的时候,oracle会自动从源库中拷贝spfile到目标库,其中需要修改的参数则通过指定的spfile参数下使用set 命令重新命名!
5 修改主库参数文件:
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,yangdb)' scope=both sid='*';
alter system set log_archive_dest_2='SERVICE=yangdb ARCH SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=yangdb' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
记得一定要both,自己当时在主库没有设置
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,yangdb)' scope=both sid='*';
结果提示:
ORA-16057: server not in Data Guard configuration
PING[ARC2]: Heartbeat failed to connect to standby 'yangdb'. Error is 16057.
6 启动备库到nomount状态
oracle@rac3:/opt/oracle/11.2.0/yangdb/dbs>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 8 15:12:36 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='/opt/oracle/11.2.0/yangdb/dbs/inityangdb.ora';
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
SQL>
SQL>
SQL> exit
记得:一定要退出!!否则rman 会hang在执行一个脚本的地方!
7 在主库或者备库执行如下命令:
rman>rman target sys/yang402@rac auxiliary sys/yang402@yangdb
rman>run {
allocate channel c1 type disk connect 'sys/yang402@rac';
allocate channel c2 type disk connect 'sys/yang402@rac';
allocate auxiliary channel s1 type disk ;
allocate auxiliary channel s2 type disk ;
sql channel c1 "alter system archive log current";
duplicate target database for standby from active database
spfile
set db_name='rac'
set db_unique_name='yangdb'
set cluster_database='FALSE'
set audit_file_dest='/opt/oracle/admin/yangdb/adump'
set DIAGNOSTIC_DEST='/opt/oracle'
set db_create_file_dest='/opt/oracle/oradata/yangdb'
set db_recovery_file_dest='/opt/oracle/flash_recovery_area'
set control_files='/opt/oracle/oradata/yangdb/control01.ctl','/opt/oracle/oradata/yangdb/control02.ctl','/opt/oracle/oradata/yangdb/control03.ctl'
set undo_tablespace='UNDOTBS1'
set service_names='yangdb'
set standby_file_management='AUTO'
set log_archive_config='DG_CONFIG=(rac,yangdb)'
set log_archive_dest_1='LOCATION=/opt/oracle/arch'
set log_archive_dest_2='service=rac ARCH ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=rac'
set log_archive_dest_state_1='enable'
set log_archive_dest_state_2='enable'
set log_archive_format='yangdb%t_%s_%r.log'
set remote_login_passwordfile='exclusive'
set LOG_FILE_NAME_CONVERT='+DATA1/rac/onlinelog','/opt/oracle/oradata/yangdb','+DATA2/rac/onlinelog','/opt/oracle/oradata/yangdb'
set DB_FILE_NAME_CONVERT='+DATA1/rac/datafile','/opt/oracle/oradata/yangdb'
set remote_listener=''
set fal_client='yangdb'
set fal_server='rac'
NOFILENAMECHECK;
sql channel c1 "alter system archive log current";
}
这里需要说明的地方是:
由于duplicate 会从源库的spfile复制新的备库的参数文件,源库的参数文件中的参数设置很可能不符合备库的设置,比如:第一次新建立的spfile文件内容
oracle@rac3:/home/oracle>cat /tmp/init.ora
rac1.__db_cache_size=553648128
rac2.__db_cache_size=570425344
rac1.__java_pool_size=16777216
rac2.__java_pool_size=16777216
rac1.__large_pool_size=16777216
rac2.__large_pool_size=16777216
rac1.__oracle_base='/opt/rac/grid'#ORACLE_BASE set from environment
rac2.__oracle_base='/opt/rac/grid'#ORACLE_BASE set from environment
rac1.__pga_aggregate_target=671088640
rac2.__pga_aggregate_target=671088640
rac1.__sga_target=989855744
rac2.__sga_target=989855744
rac1.__shared_io_pool_size=0
rac2.__shared_io_pool_size=0
rac1.__shared_pool_size=385875968
rac2.__shared_pool_size=369098752
rac1.__streams_pool_size=0
rac2.__streams_pool_size=0
*.audit_file_dest='/opt/rac/oracle/admin/rac/adump'
*.audit_trail='db'
*.cluster_database=TRUE ##不用,要设置为false
*.compatible='11.2.0.0.0'
*.control_files='+DATA1/rac/controlfile/current.260.761070189','+DATA2/rac/controlfile/current.256.761070191'#要修改
*.db_block_size=8192
*.db_create_file_dest='+DATA1' --对应的值也要修改!
*.db_domain=''
*.db_name='rac'
*.db_recovery_file_dest='+DATA2'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/opt/rac/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)'
rac1.instance_number=1
rac2.instance_number=2
*.log_archive_config='DG_CONFIG=(rac,yangdb)'
*.log_archive_dest_1='LOCATION=/opt/rac/oracle/arch'
*.log_archive_dest_2='SERVICE=yangdb ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=yangdb'
*.log_archive_dest_state_2='ENABLE'
*.memory_target=1655701504
*.open_cursors=300
*.processes=150
*.remote_listener='scan:1521' --这个参数,对于备库是无用的
*.remote_login_passwordfile='exclusive'
rac2.thread=2
rac1.thread=1
rac2.undo_tablespace='UNDOTBS2'
rac1.undo_tablespace='UNDOTBS1'
oracle@rac3:/home/oracle>
所以要在duplicate同时修改一份和备库相适应的参数。
测试:
主库中:
SQL>
alter tablespace example add datafile '+DATA1/rac/datafile/example02.dbf' size 500m;
Tablespace altered.
备库的日志:
Successfully added datafile 7 to media recovery
Datafile #7: '
/opt/oracle/oradata/alifpre/YANGDB/datafile/o1_mf_example_76kp3yqn_.dbf'
Media Recovery Log /opt/oracle/arch/yangdb1_66_761070192.log
Thu Sep 08 23:00:40 2011
Media Recovery Waiting for thread 1 sequence 67
需要注意的是:本来的
DB_FILE_NAME_CONVERT='+DATA1/rac/datafile','/opt/oracle/oradata/yangdb'
到standby 库应用归档日志时文件却为:
/opt/oracle/oradata/yangdb/YANDDB/datafile/o1_mf_example_76kp3yqn_.dbf
不方便管理。