dg架构备库应用日志慢 oracle dg备库应用日志

转载

柳随风 2024-01-31 20:35:16

文章标签 dg架构备库应用日志慢 Oracle延时DG oracle SQL hive 文章分类 架构后端开发

本帖最后由 panda_rabbit 于 2013-8-22 14:07 编辑

DG备库上做备库，并延迟应用日志

有时候备库不需要查询实时数据，如OLAP报表分析，又比如防止主库有误操作后找回原来的数据，故意让备库延迟几小时应用主库日志。

如何让备库延迟应用主库日志呢？

方法1: 在备库应用主库日志的语句中指定delay属性

如alter database recover managed standby database delay 120 disconnect from session; --备库延迟120分钟应用主库日志

方法2: log_ archive_dest_n参数中指定了delay属性

alter system set log_archive_dest_3='service=db3 lgwr async delay=120 valid_for=(all_logfiles,all_roles) db_unique_name=db3';

注意：delay属性并不是说延迟发送主库日志到备库，而是指日志到备库后，延迟多长时间应用主库日志。

但是，如果在备库应用主库日志的语句中指定了实时应用，也就是使用了using current logfile，如alter database recover managed standby database using current logfile disconnect from session;

那么，即使在log_ archive_dest_n参数中指定了delay属性，备库也会忽略delay属性。

另外，备库还可以在启动redo应用时，通过附加nodelay子句的方式，取消延迟应用主库日志，如alter database recover managed standby database disconnect from session nodelay;

下面我们通过方法1，做一个复杂点的DG实验，如下图，主库的备库是实时应用主库日志，备库的备库延迟应用日志。

1.png (1.36 KB, 下载次数: 67)

2013-8-21 20:03 上传

本实验我分了两个步骤，第一步建立主库的备库，第二步建立备库的备库。

一．建立主库的备库

主库ip及主机名：192.168.1.68 db

主库的备库ip及主机名：192.168.1.69 db2

1.建立主备库的归档目录

[root@db ~]# su - oracle

[oracle@db ~]$ cd /u01/

[oracle@db ~]$ mkdir arclog

2.主库改为强制日志模式:

[oracle@db ~]$ sqlplus / as sysdba

SQL> alter database force logging;

3.主库建立备库的文本参数文件

SQL> create pfile from spfile;

4.rman备份主库

[oracle@db ~]$ cd /u01

[oracle@db u01]$ mkdir rman

[oracle@db ~]$ rman target /

RMAN> backup database format '/u01/rman/db_%d_%s_%p_%u_%T.dbf';

RMAN> sql 'alter system archive log current';

RMAN> backup archivelog all format '/u01/rman/arc_%d_%s_%p_%u_%T.arc'; --不要加delete all input，危险！;

RMAN> exit

5.主库建立备库的控制文件

[oracle@db ~]$ sqlplus / as sysdba

SQL> alter database create standby controlfile as '/u01/standby.ctl';

SQL> exit

6.把主库的rman备份、主库密码文件、文本参数文件、standby.ctl复制到备库相应位置

①主库的rman备份

[oracle@db ~]$ scp -r /u01/rman/* 192.168.1.69:/u01/rman/

②主库密码和文本参数文件

[oracle@db ~]$ cd /u01/oracle/orcl/dbs/

[oracle@db ~]$ scp orapworcl initorcl.ora 192.168.1.69:/u01/oracle/orcl/dbs/

③主库上建立的备库控制文件standby.ctl

[oracle@db ~]$ scp /u01/standby.ctl 192.168.1.69:/u01/oracle/oradata/orcl/control01.ctl

[oracle@db ~]$ scp /u01/standby.ctl 192.168.1.69:/u01/oracle/flash_recovery_area/orcl/control02.ctl

7.备库文本参数文件修改

[oracle@db2 orcl]$ cd $ORACLE_HOME/dbs

[oracle@db2 dbs]$ vi initorcl.ora

添加下面内容:

db_unique_name=db2

log_archive_config='dg_config=(orcl,db2)'

log_archive_dest_1='location=/u01/arclog/ valid_for=(all_logfiles,all_roles) db_unique_name=db2'

log_archive_dest_2='service=orcl lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'

log_archive_max_processes=6

fal_server='orcl'

fal_client='db2'

standby_file_management=auto

原归档路径注释掉

8.备库建立新的spfile

[oracle@db2 dbs]$ cd $ORACLE_HOME/dbs

[oracle@db2 dbs]$ rm -rf spfileorcl.ora

[oracle@db2 dbs]$ sqlplus / as sysdba

SQL> create spfile from pfile;

9.主备库配置监听、tnsnames文件

注意：tnsnames中也要设置访问自己的配置，否则broker的failover时不成功。

非RAC下，建议配置监听的静态注册。

主备库tnsnames.ora配置如下：

vi $ORACLE_HOME/network/admin/tnsnames.ora
DB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.68)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

10.分别在主备库上重启或启动监听

lsnrctl reload 或lsnrctl start

11.分别在主备库上测试tns

tnsping orcl

tnsping db2

12.使用rman恢复备库

rman target /

RMAN> startup mount;

RMAN> list backup;

RMAN> restore database;

RMAN> recover database;

RMAN> exit

13.备库启动建立standby redolog

检查是否有standby redolog的记录，有则删除

SQL> select 'alter database drop standby logfile group '||group#||';' from v$standby_log;

备库建立standby logfile，接收主库传送过来的redo条目。

SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo01.log' size 50m;

SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo02.log' size 50m;

SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo03.log' size 50m;

SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo04.log' size 50m;

查看备库的standby logfile：

SQL> select group#,thread#,sequence#,status from v$standby_log;

14.主库参数修改

sqlplus / as sysdba

alter system set log_archive_config='dg_config=(orcl,db2)';

alter system set log_archive_dest_1='location=/u01/arclog/ valid_for=(all_logfiles,all_roles) db_unique_name=orcl';

alter system set log_archive_dest_2='service=db2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=db2';

alter system set log_archive_max_processes=6;

alter system set fal_server='db2';

alter system set fal_client='orcl';

alter system set standby_file_management=auto;

15.主备库中查看存档参数是否正常

主库：

col dest_name for a30

col error for a20

select dest_name,status,error,target,process from v$archive_dest where rownum<=2;

DEST_NAME STATUS ERROR TARGET PROCESS

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

LOG_ARCHIVE_DEST_1 VALID PRIMARY ARCH

LOG_ARCHIVE_DEST_2 VALID STANDBY LGWR

备库:

col dest_name for a30

col error for a20

select dest_name,status,error,target,process from v$archive_dest where rownum<=2;

DEST_NAME STATUS ERROR TARGET PROCESS

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

LOG_ARCHIVE_DEST_1 VALID LOCAL ARCH

LOG_ARCHIVE_DEST_2 VALID REMOTE LGWR

16. 查看主备库保护模式

查看主库保护模式：

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE

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

MAXIMUM PERFORMANCE PRIMARY MAXIMUM PERFORMANCE READ WRITE

查看备库保护模式：

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE

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

MAXIMUM PERFORMANCE PHYSICAL STANDBY MAXIMUM PERFORMANCE MOUNTED

17.开启物理备库的实时redo应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

18.验证备库应用了日志

主库：

SQL> alter system switch logfile;

备库：

SQL> select sequence#,applied from v$archived_log; --最后一行APPLIED为YES则表示应经应用

19.备库停止日志应用，启动到open

SQL> alter database recover managed standby database cancel;

备库启动到open

SQL> alter database open;

20.物理备库继续日志应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

21.测试物理备库是否数据更新：

主库上进行增删改操作，查看备库是否实时更新。

22.主库建立standby redolog

为了备库切换成主库时，原主库也可以接受新主库发来的redolog，所以在主库上添加standby logfile

SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo01.log' size 50m;

SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo02.log' size 50m;

SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo03.log' size 50m;

SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo04.log' size 50m;

查看主库的standby logfile：

SQL> select group#,thread#,sequence#,status from v$standby_log;

二、建立备库的备库

备库的备库ip及主机名：192.168.1.70 db3

1.建立备库的备库db3的归档目录

[root@db3 ~]# su - oracle

[oracle@db3 ~]$ cd /u01/

[oracle@db3 ~]$ mkdir arclog

4.rman备份备库(备份主库当然更好)

[oracle@db2 ~]$ rman target /

RMAN> backup database format '/u01/rman/db_%d_%s_%p_%u_%T.dbf';

RMAN> exit

[oracle@db2 ~]$ sqlplus system/oracle@orcl

SQL> alter system switch logfile;

SQL> exit

[oracle@db2 ~]$ rman target /

RMAN> backup archivelog all format '/u01/rman/arc_%d_%s_%p_%u_%T.arc';

RMAN> exit

5.主库建立备库的控制文件：

SQL> alter database create standby controlfile as '/u01/standby.ctl' reuse;

6.把备库的rman备份、密码文件、文本参数文件、主库standby.ctl复制到db3的相应位置

①备库的rman备份

[oracle@db2 ~]$ scp -r /u01/rman/* 192.168.1.70:/u01/rman/

②备库库密码和文本参数文件

[oracle@db2 ~]$ cd /u01/oracle/orcl/dbs/

[oracle@db2 ~]$ scp orapworcl initorcl.ora 192.168.1.70:/u01/oracle/orcl/dbs/

③主库上建立的备库控制文件standby.ctl

[oracle@db ~]$ scp /u01/standby.ctl 192.168.1.70:/u01/oracle/oradata/orcl/control01.ctl

[oracle@db ~]$ scp /u01/standby.ctl 192.168.1.70:/u01/oracle/flash_recovery_area/orcl/control02.ctl

7.备库的备库文本参数修改

[oracle@db3 orcl]$ cd $ORACLE_HOME/dbs

[oracle@db3 dbs]$ vi initorcl.ora

修改下面内容:

db_unique_name=db3

log_archive_config='dg_config=(db2,db3)'

log_archive_dest_1='location=/u01/arclog/ valid_for=(all_logfiles,all_roles) db_unique_name=db3'

#log_archive_dest_2='service=db2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=db2'

log_archive_max_processes=6

fal_server='db2'

fal_client='db3'

standby_file_management=auto

8.主备库配置监听、tnsnames文件

注意：tnsnames中也要设置访问自己的配置，否则broker的failover时不成功。

非RAC下，建议配置监听的静态注册。

备库db2和db3的tnsnames.ora配置如下：

vi /u01/oracle/orcl/network/admin/tnsnames.ora
DB3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.70)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
DB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.68)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

9.分别在主备库上重启监听

lsnrctl reload

或lsnrctl start

10.分别在备库db2和备库的备库db3上测试tns

tnsping db2

tnsping db3

11.备库db3建立新的spfile

cd /u01/oracle/orcl/dbs

rm -rf spfileorcl.ora

sqlplus / as sysdba

SQL> create spfile from pfile;

检查spfile

12.rman恢复备库

rman target /

RMAN> startup mount;

RMAN> catalog start with '/u01/rman/';

RMAN> crosscheck backup;

RMAN> delete noprompt obsolete;

RMAN> delete noprompt expired backup;

RMAN> list backup;

RMAN> restore database;

RMAN> recover database;

RMAN> exit

13.重建standby redolog

SQL> select 'alter database drop standby logfile group '||group#||';' from v$standby_log;

备库建立standby logfile，接收主库传送过来的redo条目。

SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo01.log' size 50m;

SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo02.log' size 50m;

SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo03.log' size 50m;

SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo04.log' size 50m;

注：如果使用ARCH方式传输日志，这一步不用执行。

14.备库db2参数修改

sqlplus / as sysdba

alter system set log_archive_config='dg_config=(orcl,db2,db3)';

alter system set log_archive_dest_3='service=db3 lgwr async valid_for=(all_logfiles,all_roles) db_unique_name=db3';

注：虽然这里是lgwr，但测试发现，只有主库归档后，db3才接收到归档，开始应用日志。也就是这里使用lgwr和arch没有区别。

15.主备库中查看存档参数是否正常

主库：

col dest_name for a30

col error for a20

select dest_name,status,error,target,process from v$archive_dest where rownum<=2;

DEST_NAME STATUS ERROR TARGET PROCESS

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

LOG_ARCHIVE_DEST_1 VALID PRIMARY ARCH

LOG_ARCHIVE_DEST_2 VALID STANDBY LGWR

备库db2:

col dest_name for a30

col error for a20

select dest_name,status,error,target,process from v$archive_dest where rownum<=3;

DEST_NAME STATUS ERROR TARGET PROCESS

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

LOG_ARCHIVE_DEST_1 VALID LOCAL ARCH

LOG_ARCHIVE_DEST_2 VALID REMOTE LGWR

LOG_ARCHIVE_DEST_3 VALID REMOTE LGWR

备库db3:

col dest_name for a30

col error for a20

select dest_name,status,error,target,process from v$archive_dest where rownum=1;

DEST_NAME STATUS ERROR TARGET PROCESS

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

LOG_ARCHIVE_DEST_1 VALID LOCAL ARCH

16. 查看主备库保护模式

查看主库保护模式：

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE

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

MAXIMUM PERFORMANCE PRIMARY MAXIMUM PERFORMANCE READ WRITE

查看备库db2保护模式：

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE

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

MAXIMUM PERFORMANCE PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY

查看备库db3保护模式：

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE

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

MAXIMUM PERFORMANCE PHYSICAL STANDBY MAXIMUM PERFORMANCE MOUNTED

17.开启备库db3的日志实时应用:

SQL> alter database recover managed standby database using current logfile disconnect from session;

18.验证备库应用了日志:

主库：SQL> alter system switch logfile;

备库：SQL> select sequence#,applied from v$archived_log; --最后一行APPLIED为YES则表示应经应用

19.备库启动到open

SQL> alter database recover managed standby database cancel;

SQL> alter database open;

20.备库db3的日志实时应用:

SQL> alter database recover managed standby database using current logfile disconnect from session;

测试发现，只有主库归档后，db3才接收到归档，开始应用日志，所以这里有无using current logfile并没有区别。

orcl主库切换日志后，db2和db3的alert日志为：

db2：

Tue Aug 20 21:37:03 2013

RFS[1]: Selected log 4 for thread 1 sequence 53 dbid 1309755207 branch 781974475

Recovery of Online Redo Log: Thread 1 Group 4 Seq 53 Reading mem 0 --db2是从redo中recover

Mem# 0: /u01/oracle/oradata/orcl/std_redo01.log

db3：

Tue Aug 20 21:37:03 2013

Media Recovery Log /u01/arclog/1_52_781974475.dbf --db3是从归档文件中recover

Media Recovery Waiting for thread 1 sequence 53

三、设置备库的备库db3延迟应用日志

[oracle@db3 ~]$ sqlplus / as sysdba

SQL> alter database recover managed standby database cancel;

SQL> alter database recover managed standby database delay 120 disconnect from session;

这样主库DML或DDL操作完成120分钟后，备库的备库db3才开始应用日志。

四、尝试db2到db3的归档参数改为ARCH模式

上面db2到db3的log_archive_dest参数用的是lgwr，但效果感觉跟arch没有区别，干脆改为ARCH试试。

db2:

alter system set log_archive_dest_3='service=db3 arch async valid_for=(all_logfiles,all_roles) db_unique_name=db3';

db3 依然是：

SQL> alter database recover managed standby database delay 120 disconnect from session;

经测试db2到db3的log_archive_dest参数用的是arch也是完全一样的效果，即可以做到延迟120分应用主库日志。

主库切换日志后，备库日志：

db2：

Wed Aug 21 07:49:21 2013

ARC4: Standby redo logfile selected for thread 1 sequence 56 for destination LOG_ARCHIVE_DEST_3

Archived Log entry 35 added for thread 1 sequence 56 ID 0x4e110c47 dest 1:

Recovery of Online Redo Log: Thread 1 Group 4 Seq 57 Reading mem 0

Mem# 0: /u01/oracle/oradata/orcl/std_redo01.log

db3：

Wed Aug 21 07:49:20 2013

RFS[9]: Assigned to RFS process 2026

RFS[9]: Identified database type as 'physical standby': Client is ARCH pid 15778

RFS[9]: Selected log 4 for thread 1 sequence 56 dbid 1309755207 branch 781974475

Wed Aug 21 07:49:21 2013

Archived Log entry 17 added for thread 1 sequence 56 ID 0x4e110c47 dest 1:

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：支持龙芯处理器的mysql 龙芯架构支持手机吗

下一篇：架构师和主程序员架构师地位

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

dg架构备库应用日志慢 oracle dg备库应用日志

dg架构备库应用日志慢 oracle dg备库应用日志

51CTO博客