逻辑DataGuard的配置

2020-11-09 来源：乌哈旅游

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.

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
NO

2 ONLINE
/u01/app/oracle/oradata/orclstd/redo02.log
NO

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---

1 ONLINE
/u01/app/oracle/oradata/orclstd/redo01.log
NO

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;

ID
----------
1
2
3

逻辑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	LGWR或ARCH
网络传输模式	SYNC	SYNC	LGWR时设置ASYNC
磁盘写操作	AFFIRM	AFFIRM	NOAFFIRM
备用日志	YES	物理备用需要	LGWR和物理备用时需要
备用库类型	物理Standby	物理或逻辑	物理或逻辑

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

显示全文

全部栏目

逻辑DataGuard的配置