物理DataGuard RAC to Instance,异步配双standby
1. 规划:
参数 | 源端 | 目标端1 | 目标端2 | |
Hostname | Rac1 | Rac2 | dgdb | Dgdb2 |
db_name db_unique_name instance_name service_names | e7rac e7rac e7rac1 e7rac | e7rac e7rac e7rac2 e7rac | e7rac e7dg1 e7dg1 e7rac1 | e7rac e7dg2 e7dg1 e7rac1 |
ip | 192.168.33.172 | 192.168.33.173 | 192.168.33.181 | 192.168.33.184 |
VIP | 192.168.33.176 | 192.168.33.180 |
|
|
先做dgdb,完了再增加一个dgdb2
1. 第一台配置
1. 查看是否安装了DataGuard
SQL> select * from V$option where parameter = 'Oracle Data Guard';
2. 修改Hosts
源端rac1、rac2:
增加:
192.168.33.181 dgdb
目标端:dgdb:
增加:
192.168.33.176 rac1-vip
192.168.33.180 rac2-vip
3. 修改tnsnames.ora
源端rac1、rac2:
E7dg1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgdb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = e7dg1)
)
)
目标端:dgdb:
E7RAC=
(DESCRIPTION=
(LOAD_BALANCE=on)
(FAILOVER=on)
(ADDRESS_LIST=
(SOURCE_ROUTE=yes)
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.33.176)(PORT=1521))
)
(ADDRESS_LIST=
(SOURCE_ROUTE=yes)
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.33.180)(PORT=1521))
)
(CONNECT_DATA=(SERVICE_NAME=E7RAC)))
4. 源库强制归档
SQL> alter database force logging;
5. 参数修改
源库:e7rac
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(e7rac,e7dg1)' scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_1=
'LOCATION=+FRA/E7RAC/ARCHIVELOG/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=e7rac' scope=both sid='*';
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=e7dg1 LGWR AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=e7dg1' scope=both;
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> alter system set log_archive_max_processes=4 scope=both sid='*';
SQL> alter system set standby_file_management=AUTO scope=both;
下面参数在官方文档的参数介绍时,主库无要求:
SQL> alter system set DB_FILE_NAME_CONVERT=
'+DATA/E7RAC/DATAFILE/','/oradata/e7dg1/datafile/' scope=spfile;
SQL> alter system set LOG_FILE_NAME_CONVERT=
'+DATA/E7RAC/ONLINELOG/','/oradata/e7dg1/onlinelog' scope=spfile;
重启源库
rac1->srvctl stop database -d e7rac -o immediate;
rac1->srvctl start database -d e7rac
6. 源库建立standby redolog
查看每个实例的最大日志数据
SQL> select inst_id,count(1),max(bytes) from gv$log group by inst_id;
INST_ID COUNT(1) MAX(BYTES)
---------- ---------- ----------
1 4 52428800
2 4 52428800
每个实例有4组log最大50M,每个实例需要建立count+1个strandby日志大小为最大日志大小也就是10组50M大小的standby日志
ASMCMD> pwd
+DATA/E7RAC
ASMCMD> mkdir STANDBYLOG
SQL> alter system set standby_file_management=manual scope=both sid='*';
SQL> alter database add standby logfile thread 1 group 9
'+DATA/E7RAC/STANDBYLOG/standby01.log' size 52428800;
SQL> alter database add standby logfile thread 1 group 10
'+DATA/E7RAC/STANDBYLOG/standby02.log' size 52428800;
SQL> alter database add standby logfile thread 1 group 11
'+DATA/E7RAC/STANDBYLOG/standby03.log' size 52428800;
SQL> alter database add standby logfile thread 1 group 12
'+DATA/E7RAC/STANDBYLOG/standby04.log' size 52428800;
SQL> alter database add standby logfile thread 1 group 13
'+DATA/E7RAC/STANDBYLOG/standby05.log' size 52428800;
SQL> alter database add standby logfile thread 1 group 14
'+DATA/E7RAC/STANDBYLOG/standby06.log' size 52428800;
SQL> alter database add standby logfile thread 1 group 15
'+DATA/E7RAC/STANDBYLOG/standby07.log' size 52428800;
SQL> alter database add standby logfile thread 1 group 16
'+DATA/E7RAC/STANDBYLOG/standby08.log' size 52428800;
SQL> alter database add standby logfile thread 1 group 17
'+DATA/E7RAC/STANDBYLOG/standby09.log' size 52428800;
SQL> alter database add standby logfile thread 1 group 18
'+DATA/E7RAC/STANDBYLOG/standby10.log' size 52428800;
7. 源库rman备份整个库:
rac1->rman target /
run
{
sql "alter system switch logfile";
allocate channel ch1 type disk format
'/backup/dg_back_%U';
backup incremental level 0 database ;
backup current controlfile for standby;
sql "alter system archive log current";
}
拷贝备份到到目标库相同目录下。
8. 创建standby的pfile文件
源库:e7rac
SQL> create pfile='/backup/inite7dg1.ora' from spfile;进行修改,做为standby的参数文件
进行修改,如下:
有关路径的参数,一定要注意大小写:
*.sga_max_size=804m
*.audit_file_dest='/oradata/e7dg1/admin/adump/'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/e7dg1/controlfile/current1.ctl','/oradata/e7dg1/controlfile/current2.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata/e7dg1/datafile/'
*.db_domain=''
*.db_file_name_convert='+DATA/e7drac/DATAFILE/','/oradata/e7dg1/datafile/'
*.db_name='e7rac'
*.DB_UNIQUE_NAME=e7dg1
*.fal_server='e7rac'
*.diagnostic_dest='/apps/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=e7dg1XDB)'
*.log_archive_config='DG_CONFIG=(e7rac,e7dg1)'
*.log_archive_dest_1='LOCATION=/backup/e7dg1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=e7dg1'
*.log_archive_dest_2='SERVICE=e7rac LGWR AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=e7rac'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_file_name_convert='+DATA/e7rac/ONLINELOG/','/oradata/e7dg1/onlinelog/'
#*.memory_target=842006528
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
undo_tablespace='UNDOTBS1'
拷贝该pfile到standby
9. 创建standby数据库
方法一:
源端库:
rac1->rman target / auxiliary sys/tanqingru@e7dg1
RMAN> duplicate target database for standby nofilenamecheck;
上面方法第一次失败,未及时找出原因,后来发现是参数大小写错误,本次采用下面方法恢复,下面的命令可以用SQL命令生成,减少工作量:
方法二:
目录库:
Sql下恢复redo
alter database rename file '+DATA/e7rac/onlinelog/group_2.262.804695039' to '/oradata/e7dg1/onlinelog/group_2.262.804695039';
alter database rename file '+FRA/e7rac/onlinelog/group_2.258.804695045' to '/oradata/e7dg1/onlinelog/group_2.258.804695045';
alter database rename file '+DATA/e7rac/onlinelog/group_1.261.804695021' to '/oradata/e7dg1/onlinelog/group_1.261.804695021';
alter database rename file '+FRA/e7rac/onlinelog/group_1.257.804695029' to '/oradata/e7dg1/onlinelog/group_1.257.804695029';
alter database rename file '+DATA/e7rac/onlinelog/group_3.265.804697845' to '/oradata/e7dg1/onlinelog/group_3.265.804697845';
alter database rename file '+FRA/e7rac/onlinelog/group_3.259.804697851' to '/oradata/e7dg1/onlinelog/group_3.259.804697851';
alter database rename file '+DATA/e7rac/onlinelog/group_4.266.804697855' to '/oradata/e7dg1/onlinelog/group_4.266.804697855';
alter database rename file '+FRA/e7rac/onlinelog/group_4.260.804697861' to '/oradata/e7dg1/onlinelog/group_4.260.804697861';
alter database rename file '+DATA/e7rac/standbylog/standby01.log' to '/oradata/e7dg1/onlinelog/standby01.log';
alter database rename file '+DATA/e7rac/standbylog/standby02.log' to '/oradata/e7dg1/onlinelog/standby02.log';
alter database rename file '+DATA/e7rac/standbylog/standby03.log' to '/oradata/e7dg1/onlinelog/standby03.log';
alter database rename file '+DATA/e7rac/standbylog/standby04.log' to '/oradata/e7dg1/onlinelog/standby04.log';
alter database rename file '+DATA/e7rac/standbylog/standby05.log' to '/oradata/e7dg1/onlinelog/standby05.log';
alter database rename file '+DATA/e7rac/standbylog/standby06.log' to '/oradata/e7dg1/onlinelog/standby06.log';
alter database rename file '+DATA/e7rac/standbylog/standby07.log' to '/oradata/e7dg1/onlinelog/standby07.log';
alter database rename file '+DATA/e7rac/standbylog/standby08.log' to '/oradata/e7dg1/onlinelog/standby08.log';
alter database rename file '+DATA/e7rac/standbylog/standby09.log' to '/oradata/e7dg1/onlinelog/standby09.log';
alter database rename file '+DATA/e7rac/standbylog/standby10.log' to '/oradata/e7dg1/onlinelog/standby10.log';
rman恢复data files和 archive logs
rman target /
run {
set newname for datafile '+DATA/e7rac/datafile/users.259.804692315' to '/oradata/e7dg1/datafile/users.259.804692315';
set newname for datafile '+DATA/e7rac/datafile/undotbs1.258.804692313' to '/oradata/e7dg1/datafile/undotbs1.258.804692313';
set newname for datafile '+DATA/e7rac/datafile/sysaux.257.804692309' to '/oradata/e7dg1/datafile/sysaux.257.804692309';
set newname for datafile '+DATA/e7rac/datafile/system.256.804692297' to '/oradata/e7dg1/datafile/system.256.804692297';
set newname for datafile '+DATA/e7rac/datafile/undotbs2.264.804696469' to '/oradata/e7dg1/datafile/undotbs2.264.804696469';
restore database;
SWITCH DATAFILE ALL;
recover database;
}
run {
SET ARCHIVELOG DESTINATION TO '/backup/e7dg1/';
RESTORE ARCHIVELOG ALL;
}
10.源库设置最大可用
SQL> alter database set standby database to maximize availability;
11.启动日志应用
目标库
SQL> alter database recover managed standby database disconnect from session;
Database altered.
12.测试
SQL> create table testan as select * from dba_objects;
SQL> select count(*) from testan;
COUNT(*)
----------
74604
SQL> alter system switch logfile;
System altered.
SQL> select sequence#,first_time,next_time
2 from V$archived_log order by sequence#;
……
34 16-JAN-13 16-JAN-13
34 16-JAN-13 16-JAN-13
35 16-JAN-13 16-JAN-13
35 16-JAN-13 16-JAN-13
Standby库:
[root@dgdb e7dg1]# pwd
/backup/e7dg1
[root@dgdb e7dg1]# ll
total 56776
……
-rw-r----- 1 oracle oinstall 9985536 Jan 16 16:06 2_22_804695018.arc
-rw-r----- 1 oracle oinstall 218624 Jan 16 16:12 2_23_804695018.arc
SQL> select sequence#,first_time,next_time
2 from V$archived_log order by sequence#;
……
33 16-JAN-13 16-JAN-13
34 16-JAN-13 16-JAN-13
35 16-JAN-13 16-JAN-13
主库做alter system switch logfile;,备库再看更新。
查看归档是否都被应用:
SQL> select sequence#,applied from V$archived_log
2 order by sequence#;
……
30 YES
31 YES
32 YES
33 YES
34 NO
35 NO
36 NO
可以看到有3个还没应用。等待一会后发现都已经应用
35 YES
36 NO
。。。
SQL> shutdown immediate
SQL> startup mount;
SQL> alter database open read only;
SQL> select count(*) from testan;
COUNT(*)
----------
74604
2. 增加一个standby配置
1. 参数修改
源库:e7rac
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(e7rac,e7dg1,e7dg2)' scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_3='SERVICE=e7dg2 LGWR AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=e7dg2' scope=both;
SQL> alter system set standby_file_management=AUTO scope=both;
Standby库:dgdb2
下面参数中有关路径一定要注意大小写,正确路径大小写源自数据字典,如:select file_name from dba_data_files;
*.sga_max_size=804m
*.audit_file_dest='/oradata/e7dg2/admin/adump/'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/e7dg2/controlfile/current1.ctl','/oradata/e7dg2/controlfile/current2.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata/e7dg2/datafile/'
*.db_domain=''
*.db_file_name_convert='+DATA/e7rac/datafile/','/oradata/e7dg2/datafile/'
*.db_file_name_convert='+DATA/e7rac/tempfile/','/oradata/e7dg2/datafile/'
*.db_name='e7rac'
*.DB_UNIQUE_NAME=e7dg2
*.fal_server='e7rac'
*.diagnostic_dest='/apps/oracle/'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=e7dg2XDB)'
*.log_archive_config='DG_CONFIG=(e7rac,e7dg1,e7dg2)'
*.log_archive_dest_1='LOCATION=/backup/e7dg2/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=e7dg2'
*.log_archive_dest_2='SERVICE=e7rac LGWR AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=e7rac'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_file_name_convert='+DATA/e7rac/ONLINELOG/','/oradata/e7dg2/onlinelog/'
*.log_file_name_convert='+FRA/e7rac/onlinelog/','/oradata/e7dg2/onlinelog/'
*.log_file_name_convert='+DATA/e7rac/standbylog/','/oradata/e7dg2/onlinelog/'
#*.memory_target=842006528
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
2. 修改hosts
源端rac1、rac2:
增加:
192.168.33.184 dgdb2
目标端:dgdb2:
增加:
192.168.33.176 rac1-vip
192.168.33.180 rac2-vip
3. 修改tnsnames.ora
源端rac1、rac2:
e7dg2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgdb2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = e7dg2)
)
)
目标端:dgdb2:
E7RAC=
(DESCRIPTION=
(LOAD_BALANCE=on)
(FAILOVER=on)
(ADDRESS_LIST=
(SOURCE_ROUTE=yes)
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.33.176)(PORT=1521))
)
(ADDRESS_LIST=
(SOURCE_ROUTE=yes)
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.33.180)(PORT=1521))
)
(CONNECT_DATA=(SERVICE_NAME=E7RAC)))
4. 创建standby2数据库
将1上的备份拷贝到dgdb2上相同的位置,
将e7dg2启动到nomount,并且生成spfile文件
SQL> create spfile from pfile;
在源端rac1上执行:
rac1->rman target / auxiliary sys/tanqingru@e7dg2
RMAN> duplicate target database for standby nofilenamecheck;
5. 启动日志应用
目标库
SQL> alter database recover managed standby database disconnect from session;
Database altered.
6. 测试
测试类似于第一台的配置。
检查接收redo是否被应用:
SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
38 YES
39 IN-MEMORY
40 NO
41 NO
SQL>/
-------- ---------
47 YES
48 NO