RAC对单机的DATAGUARD
最近帮朋友搭了一个rac对单机的dg，做了些记录，大家一起来学习吧
RAC配置信息
IP | ROLE | INSTANCE NAME | SCANIP | VIP | STORAGE |
172.28.7.201 | RAC1 | DEVDB1 | 172.28.7.221 | 172.28.7.202 | ASM |
172.28.7.211 | RAC2 | DEVDB2 | 172.28.7.212 |
单库配置信息
IP | INSTANCE NAME | STORAGE |
172.26.5.16 | SDB | 文件系统 |
操作步骤：
1，开启归档模式
RAC:执行
shutdown immediate;
alter database archivelog ;
alter database open ;
2，配置归档位置
RAC:执行
这里可以将位置配置到闪回区，也可以将其配置到一个目录下，这里是将其配置到了闪回
开启闪回功能
alter system set db_recovery_file_dest_size=80g scope=spfile;
alter system set db_recovery_file_dest='+DATA' scope=spfile;
alter system set db_flashback_retention_target=1440 scope=spfile; --这里是配置闪回时间1天是1440min
shutdown immediate;
startup nomount;
alter database flashback on ;
alter database open;
select flashback_on from v$database;
这里归档默认是在闪回区目录下所以不用配置；
如果是普通的fs，就用下面这种
mkdir -p home/oracle/arch
alter system set log_archive_dest_1='location=/home/oracle/arch' node='devdb1';
alter system set log_archive_dest_1='location=/home/oracle/arch' node='devdb2';
3，创建日志组
RAC执行
一般来说，standby redo日志文件组要比primary数据库的online redo 日志文件组要多一个，这样设计会降低primary数据库LGWR进程锁住的可能性。
select * from v$logfiles; 查看有几组日志组，创建的Standby logfile group要比online 的多一个；
alter database add standby logfile group 5 '+DATA' size 50M;
alter database add standby logfile group 6 '+DATA' size 50M;
alter database add standby logfile group 7 '+DATA' size 50M;
alter database add standby logfile group 8 '+DATA' size 50M;
alter database add standby logfile group 9 '+DATA' size 50M;
4，开启强日志级别
RAC执行
SQL>select force_logging from v$database;
FOR
no
SQL>alter database force logging ;
5，修改primary的参数文件
RAC执行
alter system set log_archive_config='dg_config=(devdb,SDB)' scope=spfile sid='*';
alter system set log_archive_dest_1='location=+DATA valid_for=(all_logfiles,all_roles) db_unique_name=devdb' scope=spfile;
alter system set log_archive_dest_2='service=SDB async valid_for=(online_logfiles,primary_role) db_unique_name=SDB' scope=spfile;
alter system set log_archive_dest_state_1=enable scope=spfile sid='*';
alter system set log_archive_dest_state_2=enable scope=spfile sid='*';
alter system set standby_file_management='auto' scope=spfile sid='*';
alter system set fal_server='SDB' scope=spfile sid='*';
alter system set db_file_name_convert='/u01/app/oracle/oradata/SDB/','+data/devdb/datafile/' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/SDB/','+data/devdb/onlinelog' scope=spfile;
alter system set fal_server=sdb scope=spfile;
alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile;
6，standby数据库关闭监听
su - oracle
lsnrctl stop
7，将primary的参数文件和密码文件传到standby
create pfile='/home/oracle/rac.pfile' from spfile;
scp rac.pfile 172.26.5.16:/home/oracle/
orapwd file=orapwDEVDB password=oracle enrtries=30;
scp orapwDEVDB 172.26.5.16:/u01/app/oracle/product/11.2.0/dbhome_1/
8，修改standby的参数文件
将参数文件中重复的参数删掉
删掉*.cluster_database=true
删掉*.remote_listener='scan-cluster:1521'
修改*.audit_file_dest='/u01/app/oracle/admin/devdb/adump'的位置
修改 log_archive_dest_1='location=+DATA valid_for=(all_logfiles,all_roles) db_unique_name=devdb' 成log_archive_dest_1='location=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch valid_for=(all_logfiles,all_roles) db_unique_name=sdb' --这里注意，standby数据库需要开启归档，如果没有指定归档路径，默认创建在/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
修改log_archive_dest_2='service=sdb valid_for=(online_logfiles,primary_role) db_unique_name=sdb'成log_archive_dest_2='service=devdb valid_for=(online_logfiles,primary_role) db_unique_name=devdb'
修改*.db_create_file_dest='+DATA'成 *.db_create_file_dest='/u01/app/oracle/oradata/sdb/' --该参数指定tempfile,logfile,tracking file ,datafile ,controlfile等文件的路径
修改.control_files='/u01/app/oracle/oradata/sdb/ora_control.ctl','/u01/app/oracle/oradata/sdb/ora_control1.ctl'
修改 *.db_name='SDB' 成*.db_name='devdb'
删掉*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
修改*.db_name='devdb' 成 *.db_name='sdb'
替换参数名称
:%s#devdb1#SDB#g
修改db_file_name_convert='/u01/app/oracle/oradata/SDB/','+data/devdb/datafile/' 成db_file_name_convert='+data/devdb/datafile/'，'/u01/app/oracle/oradata/SDB/'
log_file_name_convert='/u01/app/oracle/oradata/SDB/','+data/devdb/onlinelog'成 log_file_name_convert='+data/devdb/onlinelog'，'/u01/app/oracle/oradata/SDB/'
最后文件
vi rac.pfile
sdb.__db_cache_size=1862270976
sdb.__java_pool_size=16777216
sdb.__large_pool_size=33554432
sdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
sdb.__pga_aggregate_target=838860800
sdb.__sga_target=2516582400
sdb.__shared_io_pool_size=0
sdb.__shared_pool_size=570425344
sdb.__streams_pool_size=0
audit_file_dest='/u01/app/oracle/admin/sdb/adump'
audit_trail='db'
compatible='11.2.0.4.0'
control_files='/u01/app/oracle/oradata/sdb/control01.ctl','/u01/app/oracle/oradata/sdb/control02.ctl'
db_block_size=8192
db_domain=''
db_file_name_convert='+DATA/devdb/datafile/','/u01/app/oracle/oradata/sdb/','+DATA/devdb/tempfile','/u01/app/oracle/oradata/sdb/tempfile' #这里包括tempfile
db_unique_name=SDB
db_name='DEVDB'
#*.db_recovery_file_dest='+DATA'
#*.db_recovery_file_dest_size=4621074432
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=devdbXDB)'
fal_server='devdb'
log_archive_config='dg_config=(sdb,devdb)'
log_archive_dest_1='location=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=sdb'
log_archive_dest_2='service=devdb async valid_for=(online_logfiles,primary_role) db_unique_name=devdb'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_file_name_convert='+DATA/devdb/onlinelog','/u01/app/oracle/oradata/sdb/'
LOG_ARCHIVE_MAX_PROCESSES=30
fal_server=devdb
open_cursors=300
pga_aggregate_target=838860800
processes=150
#*.remote_listener='scan-cluster:1521'
remote_login_passwordfile='exclusive'
sga_target=2516582400
standby_file_management='auto'
undo_tablespace='UNDOTBS1'
standby 启动到nomount：
export ORACLE_SID=SDB
startup pfile=/home/oracle/rac.pfile nomount;
创建spfile
create spfile from pfile;
--如果这里参数文件报错（无法从$ORACLE_HOME/dbs/initSDB.ora 创建spfile 之类的问题），将/home/oracle/rac.pfile cp到$ORACLE_HOME/dbs/下，命名为initSDB.ora
然后关闭数据库，使用spfile启动
shutdown immediate;
startup nomount
9，创建监听（location: $ORACLE_HOME/network/admin/listener.ora）
rac 节点1：
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3 = ON
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2 = ON
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
LISTENER_SCAN3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN3))
)
LISTENER_SCAN2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN2))
)
ADR_BASE_LISTENER_SCAN3 = /u01/app/grid
LISTENER_SCAN1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
)
ADR_BASE_LISTENER_SCAN2 = /u01/app/grid
ADR_BASE_LISTENER_SCAN1 = /u01/app/grid
~
rac 节点2：
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
LISTENER_SCAN1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
)
ADR_BASE_LISTENER_SCAN1 = /u01/app/grid
单机监听：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle_adg)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sdb)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=sdb)))
#SID_LIST_LISTENER部分标识全局数据库名称、标识监听程序正在服务的每个实例的Oracle软件主目录以及实例或SID。这里一定要配置SID_LIST_LISTENER 否则监听数据库的状态将会是block状态，这样primary使用rman 连接的时候会出现问题。
10.配置tnsname(location: $ORACLE_HOME/network/admin/tnsname.ora)
rac1和rac2配置相同
devdb=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =devdb)
)
)
SDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.26.5.16)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SDB)
)
)
单机tnsname
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.7.221)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.7.222)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.7.223)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = devdb)
)
)
sdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle_adg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sdb)
)
)
单机端需要配置sqlnet.ora(location: $ORACLE_HOME/network/admin/sqlnet.ora)
在最后加
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
sqlnet.authentication_services=all
#这里是为了单库可以支持远程连接
10，验证连接
rac:
sqlplus sys/oracle@sdb as sysdba
sqlplus sys/oracle@devdb as sysdba
单机:
sqlplus sys/oracle@sdb as sysdba
sqlplus sys/oracle@devdb as sysdba
11，备库duplicate
rac端操作：
rman target / auxiliary sys/oracle@sdb
执行命令将RAC数据推到SDB:
RMAN> duplicate target database for standby from active database;
注意：如果在duplicate的时候出现了password error 的问题，登陆rman请用rman target sys/oracle@devdb auxiliary sys/oracle@sdb
12，完成后对比两端的归档状态
rac:
archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 232
Next log sequence to archive 233
Current log sequence 233
sdb:
archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 232
Next log sequence to archive 0
Current log sequence 233
13，查询状态
RAC:
SELECT PROTECTION_MODE, PROTECTION_LEVEL,DATABASE_ROLE ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
PROTECTION_MODE PROTECTION_LEVEL ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY SESSIONS ACTIVE
SDB:
SELECT PROTECTION_MODE, PROTECTION_LEVEL,DATABASE_ROLE ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
PROTECTION_MODE PROTECTION_LEVEL ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY RECOVERY NEEDED
14:同步数据:
SDB:
recover managed standby database using current logfile disconnect from session; #开启日志实时应用
recover managed standby database cancel;
alter database open;
recover managed standby database using current logfile disconnect from session;
经验：RAC对单机的DG其实和单对单一样，可以在配置的时候将RAC的一个节点关掉，然后单独配置监听即可。
THAT'S ALL
BY CUI PEACE!!!
本文分享自微信公众号 - 最帅dba工作笔记，如有侵权，请联系 service001@enmotech.com 删除。
