关于oracle下的DG搭建
ドラゴンボールのLong Lv4
  2021-01-06 20:10:12 2021-01-06 20:10      4.1k 字  20 分钟  

oracle11g高可用,oracle11g 搭建DG环境总结

一、前言：

网络上关于dataguard的配置文章很多，但是很多打着oracle11g的文章实际都是只能在9 10 上运行，比如FAL_CLIENT在11g中已经废弃，但是现在网络上的文章都是没有标注这一点。而且对于具体含义语焉不详对于新手只能知其然而不知其所以然。这篇文章我就想让像我这样的人对于dataguard配置不仅仅知道怎么配置，还要知道为什么需要这样配置，这样的效果才是最好的。
这篇文章不仅仅是记录如何配置，还介绍了为什么是这样，以及注意要点，我希望这个文章可以作为进行dataguard配置的一个参考手册。

二、前提

1.主库是归档模式：
如果我们不清楚为什么是归档模式，那我们就应该也不会清楚dataguard是用来做什么的。透过很多修饰的官方语言，我们需要明确DG（dataguard简称，后同）实际上的作用就是用来高可用。而实现原理就是从主库获取数据到从库，在主库发生异常的时候，从库接管主库，完成身份的变化。可以一个主库，最多9个从库。同时分为逻辑standby和物理standby这里我们讨论的是物理standby.
一旦创建并配置成 standby 后，dg 负责传输 primary数据库 redo data 到 standby 数据库，standby 数据库通过应用接收到的 redo data 保持与 primary 数据库的事务一致。
这下清楚了吧，需要保证主从库一致，需要传输archive log和redo log到从库，如果不是归档模式无法保证主从库的数据一致。

2.从库只需要安装数据库软件，数据从主库传输后完成。

3.很多人说11g有了active dataguard(ADG)，逻辑standby 实际上已经没什么用处了。

主从库硬件最好一致。oracle数据库版本需要一致。
内存检查项：

1
grep MemTotal /proc/meminfo

交换分区检查项：如果内存在1-2G,swap是1.5倍；2-16G,1倍；超过16G，设置为16G即可。

1
grep SwapTotal /proc/meminfo

查看共享内存大小：

1
df -h /dev/shm

查看系统处理器架构，与oracle安装包一致

1
uname -m

空间空间 /tmp必须大于1G

1
df -h /tmp

配置环境数据库用户必须有sysdba权限
后面的环境：

主库 192.168.65.129 数据库实例名：orcl db_unique_name:orcl
从库 192.168.65.130 数据库实例名：std db_unique_name:std

测试环境为：两台服务器配置一样：Oracle Linux Server release 6.5 X86_64, DB 11.2.0.4.0

前提环境：一台已经安装好数据库软件并创建数据库实例的服务器

另一台也就是备库，只安装了数据库软件，空实例。

安装环境与创建数据库这里就不用说了，很简单的。

整个环境为成4个步骤：

1，主库(orcl)修改

2，备库(std)修改

3，主备duplicate数据库

4，测试DG是否成功

判断DG是否已经安装：

1
select * from v$option where parameter = 'Oracle Data Guard';

如果是true表示已经安装可以配置，否则需要安装相应组件。

教程开始

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
su - oracle

#启动库
lsnrctl start 

sqlplus / as sysdba

#启动监听
startup

#查看强制记录日志状态
SQL> select name,force_logging from v$database;

NAME	  FOR
--------- ---
ORCL	  NO

#修改强制记录日志
alter database force logging

#查看强制记录日志状态
SQL> select name,force_logging from v$database;

NAME	  FOR
--------- ---
ORCL	  YES

exit

cd $ORACLE_HOME/dbs

#传输密码文件至备库,记得修改下备库的orapw文件名称（此步骤只在主库上操作）
scp orapworcl ora11g-2:$ORACLE_HOME/dbs/orapwstd

创建standby日志组，创建完成后查询是否成功

一般而言， standbyredo 日志文件组数要比 primary 数据库的 online redo 日志文件组数至少多一个。
推荐 standbyredo 日志组数量基于 primary 数据库的线程数(这里的线程数可以理解为 rac 结构中的 rac节点数)。

有一个推荐的公式可以做参考：(每线程的日志组数+1)*最大线程数
假设现在节点是1个，则=(3+1)*1=4
如果是双节点 则=(3+1)*2=8
这里我们创建4个standby logfile:
另：不建议组号group#紧挨着redo，因为后续redo有可能调整，这里我们从建立从11到14的standby logfile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
cd $ORACLE_BASE/oradata
#创建强制日志路径
mkdir standbylog

alter database add standby logfile group  11 '/u01/oracle/app/oradata/standbylog/standby11.log' size 50m;

alter database add standby logfile group  12 '/u01/oracle/app/oradata/standbylog/standby12.log' size 50m;

alter database add standby logfile group  13 '/u01/oracle/app/oradata/standbylog/standby13.log' size 50m;

alter database add standby logfile group  14 '/u01/oracle/app/oradata/standbylog/standby14.log' size 50m;


#查看standby日志组
SQL> select group#,sequence#,status,bytes/1024/1024 from v$standby_log;
SQL> select group#,status,type,member from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
	 1	   ONLINE
/u01/oracle/app/oradata/orcl/redo01.log

	 2	   ONLINE
/u01/oracle/app/oradata/orcl/redo02.log

	 3	   ONLINE
/u01/oracle/app/oradata/orcl/redo03.log

	11	   STANDBY
/u01/oracle/app/oradata/standbylog/standby11.log

	12	   STANDBY
/u01/oracle/app/oradata/standbylog/standby12.log

	13	   STANDBY
/u01/oracle/app/oradata/standbylog/standby13.log

	14	   STANDBY
/u01/oracle/app/oradata/standbylog/standby14.log

7 rows selected.


修改主库的pfile参数文件（只在主库上做）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
用spfile创建一个pfile，用于修改
SQL> create pfile from spfile;


SQL> show parameter spfile;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 /u01/oracle/app/oracle/product
						 /11.2.0/dbhome_1/dbs/spfileorc
						 l.ora



cd $ORACLE_HOME/dbs
vi initorcl.ora

---------------------------------------
orcl.__db_cache_size=1895825408
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/u01/oracle/app'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=838860800
orcl.__sga_target=2483027968
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=503316480
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/app/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/oracle/app/oradata/orcl/control01.ctl','/u01/oracle/app/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/oracle/app/fast_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.diagnostic_dest='/u01/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.open_cursors=300
*.pga_aggregate_target=824180736
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2474639360
*.undo_tablespace='UNDOTBS1'


#以下内容是需要新增的
*.db_unique_name='orcl'
*.log_archive_config='dg_config=(orcl,std)'
*.log_archive_dest_1='LOCATION=/u01/oracle/app/arch valid_for=(all_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_2='SERVICE=std valid_for=(online_logfile,primary_role) db_unique_name=std'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=4
*.fal_server='std'
*.fal_client='orcl'
*.db_file_name_convert='/u01/oracle/app/oradata/std','/u01/oracle/app/oradata/orcl'
*.log_file_name_convert='/u01/oracle/app/oradata/std','/u01/oracle/app/oradata/orcl'
*.standby_file_management='auto'

手工创建/u01/oracle/app/arch,此目录是上面配置文件里面后加的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
mkdir -p /u01/oracle/app/arch

sqlplus / as sysdba


#用修改过的pfile重新创建一个spfile，用于重启数据库（此步骤只在主库上做）
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> create spfile from pfile;
File created.

#数据库启动到mount状态：
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2471931904 bytes
Fixed Size		    2255752 bytes
Variable Size		  603980920 bytes
Database Buffers	 1845493760 bytes
Redo Buffers		   20201472 bytes
Database mounted.

#启动归档模式：
SQL> alter database archivelog;
Database altered.

#open数据库：
SQL> alter database open;
Database altered.

#查看是否启用归档模式
SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /u01/oracle/app/arch
Oldest online log sequence     71
Next log sequence to archive   73
Current log sequence	       73


#查看当前数据库是否使用spfile启动：
SQL> show parameter spfile;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 /u01/oracle/app/oracle/product
						 /11.2.0/dbhome_1/dbs/spfileorc
						 l.ora

SQL> select name,log_mode,force_logging from v$database;

NAME	  LOG_MODE     FOR
--------- ------------ ---
ORCL	  ARCHIVELOG   YES

添加静态监听（主库，备库都要做）

主库监听配置

nano $ORACLE_HOME/network/admin/listener.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
ORCL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.65.137)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_ORCL = /u01/oracle/app

SID_LIST_LISTENER=

(SID_LIST =

  (SID_DESC =

      (GLOBAL_DBNAME = orcl_DGMGRL)

      (ORACLE_HOME = /u01/oracle/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = orcl)

  )
)

nano $ORACLE_HOME/network/admin/tnsnames.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.65.137)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

std =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.65.138)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = std)
    )
  )
  
orcl_DGMGR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.65.137)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl_DGMGRL)
    )
  )

std_DGMGR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.65.138)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = std_DGMGRL)
    )
  )

#重启监听
lsnrctl reload

备库监听配置

nano $ORACLE_HOME/network/admin/listener.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.65.138)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_ORCL = /u01/oracle/app

SID_LIST_LISTENER=

(SID_LIST =

  (SID_DESC =

      (GLOBAL_DBNAME = std_DGMGRL)

      (ORACLE_HOME = /u01/oracle/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = std)

  )
)

nano $ORACLE_HOME/network/admin/tnsnames.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.65.137)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

std =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.65.138)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = std)
    )
  )
  
orcl_DGMGR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.65.137)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl_DGMGRL)
    )
  )

std_DGMGR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.65.138)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = std_DGMGRL)
    )
  )

启动监听，看看是否有报错

1
2
3
4
#重启监听
lsnrctl reload
#或者
lsnrctl start

主库端把pfile拷贝给备库端的$ORACLE_HOME/dbs目录下,并重命名

1
2
cd $ORACLE_HOME/dbs
scp initorcl.ora ora11g-2:$ORACLE_HOME/dbs/initstd.ora

备库端进行修改（注意目录结构）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
cd $ORACLE_HOME/dbs

nano initstd.ora

------------------------------
orcl.__db_cache_size=1895825408
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/u01/oracle/app'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=838860800
orcl.__sga_target=2483027968
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=503316480
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/app/admin/std/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/oracle/app/oradata/std/control01.ctl','/u01/oracle/app/fast_recovery_area/std/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/oracle/app/fast_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.diagnostic_dest='/u01/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdXDB)'
*.open_cursors=300
*.pga_aggregate_target=824180736
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2474639360
*.undo_tablespace='UNDOTBS1'

*.db_unique_name='std'
*.log_archive_config='dg_config=(orcl,std)'
*.log_archive_dest_1='LOCATION=/u01/oracle/app/arch valid_for=(all_logfiles,primary_role) db_unique_name=std'
*.log_archive_dest_2='SERVICE=orcl valid_for=(online_logfile,primary_role) db_unique_name=orcl'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=4
*.fal_server='orcl'
*.fal_client='std'
*.db_file_name_convert='/u01/oracle/app/oradata/orcl','/u01/oracle/app/oradata/std'
*.log_file_name_convert='/u01/oracle/app/oradata/orcl','/u01/oracle/app/oradata/std'
*.standby_file_management='auto'

创建缺少的文件目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mkdir -pv /u01/oracle/app/admin/std/adump
mkdir -pv /u01/oracle/app/oradata/std
mkdir -pv /u01/oracle/app/fast_recovery_area
mkdir -pv /u01/oracle/app/fast_recovery_area/std
mkdir -pv /u01/oracle/app/arch
mkdir -pv /u01/oracle/app/diag/rdbms/std/std/trace
mkdir -pv /u01/oracle/app/oradata/standbylog

[oracle@ora11g-2 ~]$ cd ~
[oracle@ora11g-2 ~]$ nano .bash_profile

export ORACLE_SID=std 

exit

su - oracle

用修改后的pfile创建一个spfile,用于启动数据库（备库端做）:

1
2
3
4
5
6
SQL> sqlplus / as sysdba

SQL> create spfile from pfile;
File created.

SQL> startup nomount;

利用RMAN在备库上恢复主库（备库做）:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
[oracle@ora11g-2 ~]$ rman target sys/oracle@orcl_DGMGR auxiliary sys/oracle@std_DGMGR

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 6 17:32:26 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1637071856)
connected to auxiliary database: STD (not mounted)


RMAN> duplicate target database for standby from active database nofilenamecheck;
RMAN> exit

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

SQL> sqlplus / as sysdba
SQL> alter database recover managed standby database disconnect from session;
SQL> select sequence#,applied from v$archived_log order by 1;

#如果有发现NO的，也是正常的，说明该日志在主库上还没有归档，可以在主库上运行以下命令来进行日志切换，再到备库查看日志应用情况。

#主库运行操作
SQL> alter system switch logfile;

SQL> archive log list;

-----------------------
##如果发现不能同步，在备库端进行以下操作：
SQL> alter database recover managed standby database cancel;
SQL> shu immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect from session;
------------------------

#主库操作
#启动broken
SQL> alter system set dg_broker_start=true;
System altered.

#开启闪回

SQL> alter database flashback on;
System altered.

SQL> select open_mode,database_role,log_mode,flashback_on from v$database;

OPEN_MODE	     DATABASE_ROLE    LOG_MODE	   FLASHBACK_ON
-------------------- ---------------- ------------ ------------------
READ WRITE	     PRIMARY	      ARCHIVELOG   YES

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

#备库操作
SQL> alter system set dg_broker_start=true;
System altered.

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database flashback on;
Database altered.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

SQL> select open_mode,database_role,log_mode,flashback_on from v$database;

#备库执行实施同步语句
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

创建DGMGRL配置文件

主库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
[oracle@ora11g-1 dbs]$ dgmgrl sys/oracle@orcl_dgmgr

DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
----------------------

DGMGRL> create configuration dg as primary database is orcl connect identifier is orcl_dgmgr;
Configuration "dg" created with primary database "orcl"

----------------------
DGMGRL> show configuration;

Configuration - dg

  Protection Mode: MaxPerformance
  Databases:
    orcl - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

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

DGMGRL> add database std as connect identifier is std_dgmgr maintained as physical;
Database "std" added
----------------------

DGMGRL> show configuration;

Configuration - dg

  Protection Mode: MaxPerformance
  Databases:
    orcl - Primary database
    std  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

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

DGMGRL> enable configuration;
Enabled.
----------------------

DGMGRL> show configuration;

Configuration - dg

  Protection Mode: MaxPerformance
  Databases:
    orcl - Primary database
    std  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

主或者备库操作

1
2
3
4
5
6
7
8
9
10
11
12
[oracle@ora11g-2 dbs]$ dgmgrl sys/oracle "start observer" &
[1] 38210
[oracle@ora11g-2 dbs]$ DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
Observer started



主库操作

1
2
3
4
5
6
7
8
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
----------------------
DGMGRL> show configuration;

DGMGRL> show database verbose 'orcl';
DGMGRL> show database verbose 'std';

手动切换到备库测试：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DGMGRL> switchover to 'std'
Performing switchover NOW, please wait...
Operation requires a connection to instance "std" on database "std"
Connecting to instance "std"...
Connected.
New primary database "std" is opening...
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "std"


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

DGMGRL> switchover to 'orcl'
Performing switchover NOW, please wait...
Operation requires a connection to instance "orcl" on database "orcl"
Connecting to instance "orcl"...
Connected.
New primary database "orcl" is opening...
Operation requires startup of instance "std" on database "std"
Starting instance "std"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "orcl"

自动切换功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
begin
    DBMS_SERVICE.CREATE_SERVICE (
    service_name => 'HAHA',
    network_name => 'HAHA',
    aq_ha_notifications => TRUE,
    failover_method => 'BASIC',
    failover_type => 'SELECT',
    failover_retries => 30,
    failover_delay => 5);
end;

create or replace procedure cmc_taf_service_proc
is 
     v_role VARCHAR(30);
begin
     select DATABASE_ROLE into v_role from V$DATABASE;
     if v_role = 'PRIMARY' then
         DBMS_SERVICE.START_SERVICE('HAHA');
     else
         DBMS_SERVICE.STOP_SERVICE('HAHA');
     end if; end;

create or replace TRIGGER cmc_taf_service_trg_startup
after startup on database
begin
    cmc_taf_service_proc;
end;

create or replace TRIGGER cmc_taf_service_trg_rolechange
after db_role_change on database
begin
    cmc_taf_service_proc;
end;


SQL> exec cmc_taf_service_proc;
SQL> alter system archive log current;

遇到的问题（ORA-16829: fast-start failover configuration is lagging）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
解决方式：

1.关闭fast_start failover

DGMGRL> DISABLE FAST_START FAILOVER;
Disabled.



2.备库重启

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

3.备库执行实施同步语句

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

Database altered.

4.过一段时间再检查备库状态，发现恢复正常

DGMGRL> show database orcl

Database - orcl

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    orcl
但是接着又出现apply lag

DGMGRL> show database orcl

Database - orcl

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       2 minutes 37 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    orcl

5.将DelayMins设置0

DGMGRL> edit database orcl set property DelayMins=0;
Property "delaymins" updated

DGMGRL> edit database std set property DelayMins=0;
Property "delaymins" updated

继续不断检查dg的状态

DGMGRL> show database orcl

Database - orcl

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    orcl

Database Status:
SUCCESS


6.重新开启fast_start failover

DGMGRL> enable fast_start failover
Enabled.

DGMGRL> show configuration

Configuration - orcl

  Protection Mode: MaxPerformance
  Databases:
    orcl - Primary database
      Warning: ORA-16819: fast-start failover observer not started

    dg   - (*) Physical standby database
      Warning: ORA-16819: fast-start failover observer not started

Fast-Start Failover: ENABLED

Configuration Status:
WARNING

这时发现ORA-16829错误已经消失

6.备库执行取消同步语句，全部使用DGMGRL工具管理

SQL> alter database recover managed standby database cancel;

Database altered.

取消后发现错误再次出现

DGMGRL> show database dg;

Database - dg

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    dg

  Database Error(s):
    ORA-16766: Redo Apply is stopped

  Database Warning(s):
    ORA-16829: fast-start failover configuration is lagging
    ORA-16819: fast-start failover observer not started

Database Status:
ERROR

7.对备库使用DGMGRL重启APLLY

DGMGRL> edit database orcl set state="APPLY-OFF";
Succeeded.
DGMGRL> edit database orcl set state="APPLY-ON";
Succeeded.

最后发现一切正常

DGMGRL> show database orcl;

Database - orcl

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    orcl

  Database Warning(s):
    ORA-16819: fast-start failover observer not started

Database Status:
WARNING

错误总结：在使用了DGMGRL来管理Dataguard时，就千万不要再使用SQLPLUS命令行来管理了，所有的操作必须都要在DGMGRL下进行修改配置，否则会造成参数冲突，导致问题排查困难，造成不必要的劳动

DG启停维护

启动顺序：先启动备库，后启动主库
关闭顺序：先关闭主库，后关闭备库

1.正确关闭备库和主库

1
2
3
4
5
#主库
SQL>SHUTDOWN IMMEDIATE;
#备库:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>SHUTDOWN IMMEDIATE;

2.正确打开备库和主库

1
2
3
4
5
6
7
8
#备库:
--先启备库
sql>startup nomount
sql>alter database mount standby database;
sql>alter database open;
sql>alter database recover managed standby database using current logfile disconnect from session;
#主库:
SQL> STARTUP;

快速关库

1
shutdown abort
  • 本文标题：关于oracle下的DG搭建
  • 本文作者：ドラゴンボールのLong
  • 创建时间：2021-01-06 20:10:12
  • 本文链接：https://zhongshenglong.xyz/2021/01/06/oracle—DG搭建/
  • 版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
 评论