Dataguard之Rac To Single（local file system）
dataguard是我们常用的oracle备份工具，因为其中的参数涉及较多，且相关资料说明也比较少，搭建起来如果没有相关文档说明的话比较费力。以下为rac到单节点的dataguard的搭建过程，由于rac到单节点这种方式一般只是用备库作为数据备份，因此一般也不考虑主备互切的情况，参数中无相关的主切换到备之后的参数配置。个人觉得这是一个比较纯净版的dataguard搭建方式，我们可以在这上边慢慢添加相应的参数设置，逐步的认识到我们常用的每个参数的用处。步子迈的小一点，路走的稳一点！
主库 ************************************************* 开启归档 ************************************************* SQL> alter system set log_archive_dest_1='LOCATION=+FRA' valid_for=(ALL_LOGFILES,ALL_ROLES)' scope=both; 设置归档路径 SQL> shutdown immediate --所有节点 SQL> startup mount --第一节点 SQL> alter database archivelog; SQL> alter database open; SQL> startup --其他节点 ************************************************* 开启强制记录日志 ************************************************* select log_mode,FORCE_LOGGING from v$database; alter database force logging; SQL> select log_mode,FORCE_LOGGING from v$database; LOG_MODE FOR ------------ --- ARCHIVELOG YES ************************************************* 主库参数设置 ************************************************* alter system set db_unique_name='rac' scope=spfile; alter system set log_archive_config='dg_config=(rac,rac_dg)' scope=both; --此处为db_unique_name alter system set log_archive_dest_1='location=+FRA valid_for=(all_logfiles,all_roles) db_unique_name=rac' scope=both; --valid_for 这里涉及内容较多 alter system set log_archive_dest_2='service=rac_sbdb lgwr async compression=enable valid_for=(online_logfiles,primary_role) db_unique_name=rac_dg' scope=both;
--service为tns中指定的名字。 alter system set log_archive_dest_state_1=enable scope=both; alter system set log_archive_dest_state_2=enable scope=both; alter system set standby_file_management=auto scope=both; ************************************************* 主库文件备份 ************************************************* $ rman target / run{ allocate channel c0 type disk; allocate channel c1 type disk; backup database filesperset 3 format '/rman/prod_full_%U'; sql 'alter system archive log current'; backup archivelog all format '/rman/prod_arch_%U'; release channel c0; release channel c1; } 生成备库控制文件： SQL> alter database create standby controlfile as '/rman/prod_for_standby.ctl'; 生成备库参数文件： SQL> create pfile='/rman/prod.ora' from spfile; 备份密码文件(10g/11g)： ls -ltr $ORACLE_HOME/dbs/orapw* 如果不存在密码文件则创建： orapwd file=$ORACLE_HOME/dbs/orapw< SID> password=<xxxx> entries=5 复制密码文件至其他所有节点，包括RAC环境下主库的其他节点，注意命名规则，在节点一上为orwpwrac1，节点二上为orwpwrac2，拷贝至备库之后，
命名应该为备库的orapwrac，因为备库的instance_name仍然沿用主库的。
[oracle@rac1 rman]$ scp ./* 192.168.8.220:/rman/
*************************************************
字符串配置
*************************************************
在主和备所有节点配置TNS连接串，使用SCAN-IP，配置UR=A选项
# su - oracle
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
rac_prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.201)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.203)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
)
)
rac_sbdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.220)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =rac_dg)
)
)
备库
*************************************************
备库参数修改
*************************************************
$ cp prod.ora sbdb.ora
编辑参数文件，保留以下参数
$ vi sbdb.ora
*.__db_cache_size=377487360
*.__java_pool_size=4194304
*.__large_pool_size=8388608
*.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.__pga_aggregate_target=209715200
*.__sga_target=624951296
*.__shared_io_pool_size=0
*.__shared_pool_size=222298112
*.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/rac/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata'
*.db_domain=''
*.db_name='rac'
*.db_unique_name='rac1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)'
*.log_archive_config='dg_config=(rac,rac_dg)'
*.log_archive_dest_1='location=/arch valid_for=(all_logfiles,all_roles) db_unique_name=rac_dg'
*.log_archive_dest_2='service=rac_prod lgwr async compression=enable valid_for=(online_logfiles,primary_role) db_unique_name=rac'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=207618048
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=624951296
*.standby_file_management='AUTO'
建立ADUMP
# mkdir -p /u01/app/oracle/admin/rac/adump
# chown oracle:oinstall adump
配置SPFILE文件
SQL> create spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfilerac.ora' from pfile='/rman/sbdb.ora';
*************************************************
备库取回控制文件
*************************************************
RMAN> restore controlfile from '/rman/prod_for_standby.ctl';
RMAN> alter database mount;
禁用BCT
为防止BCT文件重复覆盖，在临时环境执行下列脚本：
SQL> alter database disable block change tracking;
*************************************************
备库恢复数据文件
*************************************************
$ rman target /
RMAN> catalog start with '/rman'
使用RMAN恢复数据文件
文件系统示例：
run{
allocate channel c0 type disk;
allocate channel c1 type disk;
set newname for datafile 1 to '/oradata/system';
set newname for datafile 2 to '/oradata/sysaux';
set newname for datafile 3 to '/oradata/undotbs1';
set newname for datafile 4 to '/oradata/undotbs2';
set newname for datafile 5 to '/oradata/users';
set newname for tempfile 1 to '/oradata/temp';
restore database;
switch datafile all;
switch tempfile all;
release channel c0;
release channel c1;
}
exit;
*************************************************
字符串配置
*************************************************
在主和备所有节点配置TNS连接串，使用SCAN-IP，配置UR=A选项
# su - oracle
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
rac_prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.201)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.203)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
)
)
rac_sbdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.220)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =rac_dg)
)
)
在主备环境所有节点测试：
# su - oracle
$ sqlplus sys/oracle@orcl_prod as sysdba
$ sqlplus sys/oracle5@orcl_sbdb as sysdba
当以上连接测试通过的时候，基本上一个双节点到单节点的dataguard的就实现了。下一步就是去验证主库到备库的数据传输是没问题的：
验证
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
1 ONLINE /oradata/RAC_DG/onlinelog/o1_mf_1_fpggr8hh_.log NO
2 ONLINE /oradata/RAC_DG/onlinelog/o1_mf_2_fpggr919_.log NO
3 ONLINE /oradata/RAC_DG/onlinelog/o1_mf_3_fpggr9n3_.log NO
4 ONLINE /oradata/RAC_DG/onlinelog/o1_mf_4_fpggrb4l_.log NO
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
1 1 149 52428800 512 1 YES CURRENT 4551681 05-AUG-18 4551575 05-AUG-18
2 1 148 52428800 512 1 YES CLEARING 4551575 05-AUG-18 4551681 05-AUG-18
3 2 85 52428800 512 1 YES CURRENT 4551742 05-AUG-18 4550125 05-AUG-18
4 2 84 52428800 512 1 YES CLEARING 4550125 05-AUG-18 4551742 05-AUG-18
切换日志之后
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
1 1 149 52428800 512 1 YES CURRENT 4551681 05-AUG-18 4551575 05-AUG-18
2 1 148 52428800 512 1 YES CLEARING 4551575 05-AUG-18 4551681 05-AUG-18
3 2 85 52428800 512 1 YES CLEARING 4551742 05-AUG-18 4586661 05-AUG-18
4 2 86 52428800 512 1 YES CURRENT 4586661 05-AUG-18 4551742 05-AUG-18
SQL> select * from v$standby_log;
no rows selected
*****************
以下为在备库抓取的日志
*****************
Media Recovery Waiting for thread 1 sequence 163 (in transit)
Mon Aug 06 06:06:25 2018
Archived Log entry 37 added for thread 1 sequence 163 rlc 982413589 ID 0x99358fd5 dest 2:
RFS[1]: No standby redo logfiles created
RFS[1]: Opened log for thread 1 sequence 164 dbid -1724571691 branch 982413589
Mon Aug 06 06:06:28 2018
Media Recovery Log /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_163_982413589.dbf
Media Recovery Log /u01/app/oracle/product/11.2.0/db_1/dbs/arch2_97_982413589.dbf
Media Recovery Waiting for thread 2 sequence 98 (in transit)
Mon Aug 06 06:08:12 2018
Archived Log entry 38 added for thread 1 sequence 164 rlc 982413589 ID 0x99358fd5 dest 2:
RFS[1]: No standby redo logfiles created
RFS[1]: Opened log for thread 1 sequence 165 dbid -1724571691 branch 982413589
Mon Aug 06 06:09:15 2018
Archived Log entry 39 added for thread 1 sequence 165 rlc 982413589 ID 0x99358fd5 dest 2:
RFS[1]: No standby redo logfiles created
RFS[1]: Opened log for thread 1 sequence 166 dbid -1724571691 branch 982413589
Mon Aug 06 06:09:17 2018
Archived Log entry 40 added for thread 2 sequence 98 rlc 982413589 ID 0x99358fd5 dest 2:
RFS[3]: No standby redo logfiles created
RFS[3]: Opened log for thread 2 sequence 99 dbid -1724571691 branch 982413589
推测主库生成的归档文件通过rfs进程推送到备库指定的目录下，备库采用media recovery进行数据恢复，具体的过程还需要进一步验证。
在主库更新一个表，切换几次日志看下，在备库能否查询到数据库更新，若能更新就说明搭建的dataguard是可用的。但此时的dataguard和主库还是有差距的，主库的更新只有在切换几次日志之后备库才能应用到。那么如何打开real time apply呢？
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs
--这里说明，如果要开启备库的实时应用的话，备库必须创建相应的standby redo logs
alter database add standby logfile thread 1 group 5 ('/oradata/stby_redo05.log') size 100m;
alter database add standby logfile thread 1 group 6 ('/oradata/stby_redo06.log') size 100m;
alter database add standby logfile thread 1 group 7 ('/oradata/stby_redo07.log') size 100m;
alter database add standby logfile thread 2 group 9 ('/oradata/stby_redo09.log') size 100m;
alter database add standby logfile thread 2 group 10 ('/oradata/stby_redo10.log') size 100m;
alter database add standby logfile thread 2 group 11 ('/oradata/stby_redo11.log') size 100m;
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
查询相关日志如下：
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /oradata/RAC_DG/onlinelog/o1_mf_1_fpggr8hh_.log NO
2 ONLINE /oradata/RAC_DG/onlinelog/o1_mf_2_fpggr919_.log NO
3 ONLINE /oradata/RAC_DG/onlinelog/o1_mf_3_fpggr9n3_.log NO
4 ONLINE /oradata/RAC_DG/onlinelog/o1_mf_4_fpggrb4l_.log NO
5 STANDBY /oradata/stby_redo05.log NO
6 STANDBY /oradata/stby_redo06.log NO
7 STANDBY /oradata/stby_redo07.log NO
9 STANDBY /oradata/stby_redo09.log NO
10 STANDBY /oradata/stby_redo10.log NO
11 STANDBY /oradata/stby_redo11.log NO
10 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
1 1 171 52428800 512 1 YES CLEARING 4597081 05-AUG-18 4605630 05-AUG-18
2 1 172 52428800 512 1 YES CURRENT 4605630 05-AUG-18 2.8147E+14
3 2 99 52428800 512 1 YES CLEARING 4592512 05-AUG-18 4590991 05-AUG-18
4 2 100 52428800 512 1 YES CURRENT 4595584 05-AUG-18 4592512 05-AUG-18
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------ ------------ ------------------ ------------ ------------------
5 2570395605 1 172 104857600 512 2567168 YES ACTIVE 4605630 05-AUG-18 4606147 05-AUG-18
6 UNASSIGNED 1 0 104857600 512 0 YES UNASSIGNED
7 UNASSIGNED 1 0 104857600 512 0 YES UNASSIGNED
9 UNASSIGNED 2 0 104857600 512 0 YES UNASSIGNED
10 UNASSIGNED 2 0 104857600 512 0 YES UNASSIGNED
11 UNASSIGNED 2 0 104857600 512 0 YES UNASSIGNED
6 rows selected.
******************
备库应用日志如下
******************
Media Recovery Waiting for thread 1 sequence 171 (in transit)
Completed: alter database recover managed standby database using current logfile disconnect from session
Mon Aug 06 07:07:17 2018
Archived Log entry 48 added for thread 1 sequence 171 rlc 982413589 ID 0x99358fd5 dest 2:
RFS[2]: Selected log 5 for thread 1 sequence 172 dbid -1724571691 branch 982413589
Mon Aug 06 07:07:18 2018
Media Recovery Log /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_171_982413589.dbf
Media Recovery Waiting for thread 1 sequence 172 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 172 Reading mem 0
Mem# 0: /oradata/stby_redo05.log
在这种模式下，备库不等归档文件传输过来，就已经将事务同步过来。感觉这里的传输归档和备库的没啥太大关系，具体的应用过程也需要进一步测试。
