mysql可以使用dataguard_逻辑DataGuard的配置

最新推荐文章于 2021-03-18 22:27:33 发布

weixin_39622399

最新推荐文章于 2021-03-18 22:27:33 发布

阅读量161

点赞数

文章标签： mysql可以使用dataguard

本文链接：https://blog.csdn.net/weixin_39622399/article/details/113393619

版权

1.Primary数据库生成LogMiner字典信息(生成之前，确保待转换的物理Standby停止REDO应用) ORCLPRI_LG execute dbms_logstdby.build; PL/SQL procedure successfully completed. 2.逻辑Standby更名 ORCLSTD_LG show parameter db_name NAME TYPE VALUE -----

1.Primary数据库生成LogMiner字典信息(生成之前，确保待转换的物理Standby停止REDO应用)

ORCLPRI_LG >execute dbms_logstdby.build;

PL/SQL procedure successfully completed.

2.逻辑Standby更名

ORCLSTD_LG >show parameter db_name

NAME TYPE VALUE

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

db_name string orcl

ORCLSTD_LG >alter database recover to logical standby ORCLLDG;

alter database recover to logical standby ORCLLDG

ERROR at line 1:

ORA-19953: database should not be open

ORCLSTD_LG >shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

ORCLSTD_LG >alter database recover to logical standby ORCLLDG;

alter database recover to logical standby ORCLLDG

ERROR at line 1:

ORA-01034: ORACLE not available

ORCLSTD_LG >startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 62916852 bytes

Database Buffers 100663296 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORCLSTD_LG >alter database recover to logical standby ORCLLDG;

Database altered.

ORCLSTD_LG >show parameter db_name

NAME TYPE VALUE

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

db_name string orcl

ORCLSTD_LG >shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

ORCLSTD_LG >startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 62916852 bytes

Database Buffers 100663296 bytes

Redo Buffers 2973696 bytes

Database mounted.

3.重启生效

ORCLSTD_LG >show parameter db_name

NAME TYPE VALUE

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

db_name string ORCLLDG

ORCLSTD_LG >select database_role from v$database;

DATABASE_ROLE

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

LOGICAL STANDBY

4.创建本地和远程归档路径

修改Primary spfile：

orcl.__db_cache_size=92274688

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=58720256

orcl.__streams_pool_size=4194304

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'#Restore Controlfile

*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd','/u01/rec_catalog','/u01/app/oracle/oradata/orclstd'

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.db_unique_name='orclpre'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fal_client='orcl_192.168.1.222'

*.fal_server='orcls_192.168.1.223'

*.job_queue_processes=10

*.log_archive_config='dg_config=(orclpre,orclldg)'

*.log_archive_dest_1='location=/u01/arch_orcl'

*.log_archive_dest_2='service=orcls_192.168.1.223

lgwr async valid_for=(online_logfiles, primary_role) db_unique_name=orclldg'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='archive_%t_%s_%r.arclog'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd','/u01/rec_catalog','/u01/app/oracle/oradata/orclstd'

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.standby_file_management='auto'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

lgwr async:保证Primary到逻辑Standby异步传输

修改Standby spfile：

orcl.__db_cache_size=96468992

orcls.__db_cache_size=100663296

orclstd.__db_cache_size=50331648

orcl.__java_pool_size=4194304

orcls.__java_pool_size=4194304

orclstd.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcls.__large_pool_size=4194304

orclstd.__large_pool_size=4194304

orcl.__shared_pool_size=54525952

orcls.__shared_pool_size=54525952

orclstd.__shared_pool_size=104857600

orcl.__streams_pool_size=4194304

orcls.__streams_pool_size=0

orclstd.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orclstd/adump'

*.background_dump_dest='/u01/app/oracle/admin/orclstd/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/orclstd/orclstd01.ctl','/u01/app/oracle/oradata/orclstd/orclstd02.ctl','/u01/app/oracle/oradata/orclstd/orclstd03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/orclstd/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orcl','/u01/rec_catalog','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/rec_catalog'

*.db_name='ORCLLDG'#db_name

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.db_unique_name='orclldg'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fal_client='orcl_192.168.1.223'

*.fal_server='orcls_192.168.1.222'

*.job_queue_processes=10

*.log_archive_config='dg_config=(orclpre,orclldg)'

*.log_archive_dest_1='location=/u01/arch_ldg valid_for=(online_logfiles,all_roles)

db_unique_name=orclldg'

*.log_archive_dest_2='service=orcl_192.168.1.222

arch valid_for=(online_logfiles, primary_role)

db_unique_name=orclpre'

*.log_archive_dest_3='location=/u01/arch_std valid_for=(standby_logfiles,standby_role)

db_unique_name=orclldg'

*.log_archive_dest_state_2='enable'

*.log_archive_format='archive_%t_%s_%r.arclog'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orcl','/u01/rec_catalog','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/rec_catalog'

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.standby_file_management='auto'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/orclstd/udump'

ORCLSTD_LG >create spfile from pfile='/u01/pfile';

File created.

ORCLSTD_LG > startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 62916852 bytes

Database Buffers 100663296 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORCLSTD_LG > alterd database open;

SP2-0734: unknown command beginning "alterd dat..." - rest of line ignored.

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

ORCLSTD_LG > alter database open resetlogs;

Database altered.

开始应用REDO数据

ORCLSTD_LG >alter database start logical standby apply;

Database altered.

ORCLSTD_LG >alter database stop logical standby apply;

Database altered.

ORCLSTD_LG >select * from v$logfile;

GROUP# STATUS TYPE

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

MEMBER

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

IS_

---

3 ONLINE

/u01/app/oracle/oradata/orclstd/redo03.log

2 ONLINE

/u01/app/oracle/oradata/orclstd/redo02.log

GROUP# STATUS TYPE

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

MEMBER

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

IS_

---

1 ONLINE

/u01/app/oracle/oradata/orclstd/redo01.log

ORCLSTD_LG >alter database add standby logfile group 4 '/u01/app/oracle/oradata/orclstd/stdredo01.log' size 50m;

Database altered.

ORCLSTD_LG >alter database add standby logfile group 5 '/u01/app/oracle/oradata/orclstd/stdredo02.log' size 50m;

Database altered.

ORCLSTD_LG >alter database add standby logfile group 6 '/u01/app/oracle/oradata/orclstd/stdredo03.log' size 50m;

Database altered.

重新执行：

ORCLSTD_LG >alter database start logical standby apply immediate;

Database altered.

测试：

Primary：

SQL> select * from scott.test;

no rows selected

SQL> insert into scott.test values(1);

1 row created.

SQL> insert into scott.test values(2);

1 row created.

SQL> insert into scott.test values(3);

1 row created.

SQL> commit;

Commit complete.

Standby：

SQL> select * from scott.test;

----------

逻辑Standby创建(转换)成功……

总结：

一、Physical Standby，Logical Standby (物理Standby及逻辑Standby)

Physical standby直接从主库接受archived log，然后直接做基于block的物理恢复(更新或调整变化的block)，所以physical standby在物理文件一级完全都等同于主库。physical standby恢复只是底层的block apply, OS层面的工作,数据库SCHEMA，包括索引都是一样的。它是直接应用REDO或归档实现同步的。不会涉及temp ,undo等。物理STANDBY可能的模式：只读模式(OPEN

READONLY)和恢复模式(MANANGED RECOVERY)。

在逻辑STANDBY中，逻辑信息是相同的，但物理组织和数据结构可以不同，它和主库保持同步的方法是将接收的REDO转换成SQL语句，然后在STANDBY上执行SQL语句(SQL

Apply)。逻辑STANDBY除灾难恢复外还有其它用途，比如用于用户进行查询和报表。

在9i R2之前，data guard的服务器只能运行在read only或者recover模式，一个physical standby database在物理上完全等同主库，当physical standby database正在做恢复的时候，不能打开用作其他用途。而logical standby database只是在logical上等同需要恢复的schema，所以在恢复的时候，可以同时打开做report(做查询动作)，也可以用户和主库不一样的

数据对象等等，极大了提高了备用库的利用率。

二、Dataguard

都是Standby。在Oracle 9i之前称为Standby，9i或之后的Standby被改名为Data guard。不过功能上也有了很多的改进和区别。

三、Standby下LGWR / ARCH传输

查看数据库保护模式：

SQL> select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

1.最大性能(maximize performance):这是data guard默认的保护模式。primay上的事务commit前不需要从standby上收到反馈信息(主数据库的提交操作不等待STANDBY)，该模式在primary故障时可能丢失数据，但standby对primary的性能影响最小。可以使用LGWR ASYNC或者ARCH两种传输模式。

ARCH传输模式：Primary DB上的online redo log写满或其他条件引起redo log写归档的时候，redo log生成的archived log file写到本地归档目录的同时，写入了Standby归档目录。只是Primary db上的online redo log切换不必等Standby上的写归档动作结束。

2.最大可用(maximize availability):在正常情况下，最大可用模式和最大保护模式一样；在standby不可用时，最大可用模式会自动降低成最大性能模式，所以standby故障不会导致primay不可用。在问题纠正之后，Standby和主数据库进行再同步，至少有一个standby可用的情况下，即使primary down机，也能保证不丢失数据。(不过当问题修复，再同步之前有必要FAILOVER,那么有些数据可能会丢失)。最大可用性模式Standby必须配置Standby

Redo log，Oracle推荐最大可用模式使用LGWR ASYNC(异步)模式传输。

采用最大可用的data guard模式，主库往备库传递在线日志(online redo log)信息，在线日志信息写入备用库的standby redo log,这些standby redo log归档后，备用库应用归档日志。

LGWR还分为LGWR ASYNC(异步)和LGWR SYNC(同步)两种。

最大保护

最大可用

最大性能

进程

LGWR

LGWR或ARCH

网络传输模式

SYNC

LGWR时设置ASYNC

磁盘写操作

AFFIRM

NOAFFIRM

备用日志

YES

物理备用需要

LGWR和物理备用时需要

备用库类型

物理Standby

物理或逻辑

最大保护(maximize protection):最高级别的保护模式。primay上的事务在commit前必须确认redo已经传递到至少一个standby上，如果所有standby不可用，则primary会挂起。该模式能保证零数据丢失。对于最大保护和最高可用性模式，Standby数据库必须配置standby redo log，并且oracle推荐所有数据库都使用LGWR ASYNC模式传输。

本文原创发布php中文网，转载请注明出处，感谢您的尊重！

weixin_39622399

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql可以使用dataguard_逻辑DataGuard的配置

1.Primary数据库生成LogMiner字典信息(生成之前，确保待转换的物理Standby停止REDO应用) ORCLPRI_LG execute dbms_logstdby.build; PL/SQL procedure successfully completed. 2.逻辑Standby更名 ORCLSTD_LG show parameter db_name NAME TYPE VALU...
复制链接

扫一扫