自律自由

yaogang

0%

oracle 11.2.0.4.0 使用 duplicate 配置 data guard

发表于 更新于 分类于 
1
2
3
4
5
data guard分为物理和逻辑备库:

a:  物理standby是redo应用，可以mount和open read only，也可以通过停止应用，保存快照作为还原点，然后打开为读写模式，standby和primary的版本需要一致，redo应用又可以使用lgwr和arch进程使用2种，sync和async2种，affirm和noaffirm 2种，保护模式共有3种：最大性能，最大可用，最大保护

b:  逻辑standby是sql应用，所以standby一定是open的，standby和primary库版本可能不需要强一致性
1
注意：主库和备库的 sid, db_name 一样, db_unique_name 不一样, 一般情况下 oracle_sid=dn_name=db_unique_name
1
2
3
4
5
6
7
8
环境信息：

db_version=Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
os_version=CentOS Linux release 7.6.1810 (Core)
primary_db_name=SRM
standby_db_name=SRM
primary_db_unique_name=SRMDG
standby_db_unique_name=SRMDG

###主库操作

1. 启动强制日志，强制nologging的操作写redo

1
alter database force logging;

2. 增加主库standby redo log，目的主要是switchover后，主库作为备库时接收接收主库的redo

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- standby logfile一般主要由rfs(remote file server 前台进程)接收主库的redo，然后进行应用，当主库归档redo后，备库就相应的归档standby logfile
-- 所以standby logfile要大于主库redo file，oracle推荐一样大，数量的话也是至少一样多
-- 要注意rac主库的话要结合 v$log 和 v$thread 来，单主库rac添加节点实例或者redo文件时，每个standby库都需要相应添加standby logfile，否则可能会备库不同步
-- 当备库的standby不可用或者有redo gap时，备库收到的redo可能直接写到备库的LOCATION位置

--单实例库
set line 200
col member for a100
select * from v$logfile;
select group#, bytes/1024/1024 M from v$log;
alter database add standby logfile '/u01/app/oracle/oradata/SRM/standby_redo01.log' size 100M;
alter database add standby logfile '/u01/app/oracle/oradata/SRM/standby_redo02.log' size 100M;
alter database add standby logfile '/u01/app/oracle/oradata/SRM/standby_redo03.log' size 100M;
alter database add standby logfile '/u01/app/oracle/oradata/SRM/standby_redo04.log' size 100M;

--如果是rac主库的话需要结合 v$log和 v$thread 来：
--rac 库
--alter database add standby logfile thread 1 SIZE 500M;
--alter database add standby logfile thread 1 SIZE 500M;
--alter database add standby logfile thread 1 SIZE 500M;
--alter database add standby logfile thread 2 SIZE 500M;
--alter database add standby logfile thread 2 SIZE 500M;
--alter database add standby logfile thread 2 SIZE 500M;

3. 配置主库到备库的redo传输

1
2
--配置log_archive_config和log_archive_dest_n，11g最多30个standby吧好像**
alter system set log_archive_config='dg_config=(srm,srmdg)';

4. 设置备库redo应用位置

1
2
3
4
5
6
--本机使用fra 注意租后db_unique_name指定tns名，这里可以设置redo实时应用或者应用归档
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=srm';
--归档应用型
alter system set log_archive_dest_2='SERVICE=srmdg arch async affirm valid_for=(online_logfiles,primary_role) db_unique_name=srmdg';
--日志应用型
--alter system set log_archive_dest_2='SERVICE=srmdg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=srmdg';

5. 启用redo应用1 2 ，默认已经启动

1
2
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;

6. 设置standby file自动管理，默认为manual

1
alter system set standby_file_management='auto';

7. 设置sys远程登录

1
2
3
--检查EXCLUSIVE，一般默认为exclusive ，为none表明不允许sys远程登录，如果值不是EXCLUSIVE，则：
show parameter REMOTE_LOGIN_PASSWORDFILE;
alter system set remote_login_passwordfile=exclusive scope=spfile;

8. 设置故障转移

1
2
3
4
5
alter system set fal_server='srmdg';

--设置日志和数据文件名称转换，用于主和备库路径不一致情况(注意： 主库在前，备库在后，但是为standby才生效，所以一般是primary配置备库库在前，standby配置主库在前)
alter system set db_file_name_convert='/u01/app/oracle/oradata2/SRMDG/','/u01/app/oracle/oradata/SRM' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata2/SRMDG/','/u01/app/oracle/oradata/SRM' scope=spfile;

9. 启动主库归档

1
2
3
4
5
6
--重启生效静态参数,启动归档(如果已经启用就不用了)
shu immediate
startup mount;
alter database archivelog;
--alter database open;
--archive log list;

10. 配置主库tns

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--注意主备库都要配置2个，不然主库发送redo找不到备库位置
---------------------------------------------------------------------------------------
SRM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.203)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SRM)
    )
  )
SRMDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.204)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SRMDG)
    )
  )
---------------------------------------------------------------------------------------

11. 建测试表，插入一条测试数据

1
2
3
create table yg(id int);
insert into yg values(1);
commit;

###备库操作

1. 配置静态监听

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
---千万注意静态监听一定要注意环境变量ORACLE_SID和监听文件中配置的大小写要一致
[oracle@localhost admin]$ lsnrctl stop
[oracle@localhost admin]$ vim listener.ora
---------------------------------------------------------------------------------------
LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.204)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=SRMDG)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=SRMDG))
  )
---------------------------------------------------------------------------------------

-- 重启静态监听**
[oracle@localhost admin]$ lsnrctl start

2. 添加备库tns配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--注意主备都加，不然switchover测试时备库发送redo根据tns找不到原来主库的位置
[oracle@localhost admin]$ vim tnsnames.ora
---------------------------------------------------------------------------------------
SRM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.203)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SRM)
    )
  )
SRMDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.204)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SRMDG)
    )
  )
---------------------------------------------------------------------------------------

3. 配置密码文件

1
2
--复制主库password文件到备库，复制和新建都行，密码应该可以不一样，因为duplicate都会帮你再覆盖一次，如果rman恢复的话要一样
[oracle@localhost dbs]$ scp primary:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSRM orapwSRMDG

4. 新建standby参数文件

1
2
3
4
5
-- 指定一个和备库的db_name就行了，也许可以不用，自己有兴趣可以验证
[oracle@localhost ~]$ cd
cat >> temp_pfile.ora <<EOF
*.db_name='SRM'
EOF

5. 修改 standby SID

1
2
3
[oracle@localhost ~]$ export ORACLE_SID=SRMDG
[oracle@localhost ~]$ echo $ORACLE_SID
SRMDG

6. 启动standby实例

1
2
[oracle@localhost ~]$ sqlplus / as sysdba
SQL>  startup nomount pfile='/home/oracle/temp_pfile.ora';

7. 测试standby远程sys登录, 可以正常登录，不能为idle状态，否则检查密码文件和SID和监听SID大小写问题

1
[oracle@localhost ~]$ sqlplus sys/oracle@srmdg as sysdba

8. 创建standby相应目录

1
2
mkdir -p /u01/app/oracle/oradata2/SRMDG/
mkdir -p /u01/app/oracle/fast_recovery_area/SRMDG

9. 开始duplicate standby库

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
--在主库或者备库操作都行，都以sys登录rma，一定要登录target库，准备开始搭建standby库，channel配置使用target库
[oracle@localhost ~]$ rman target sys/oracle@srm

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 19 07:53:36 2019

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

connected to target database: SRM (DBID=2040369693)

RMAN> connect auxiliary sys/oracle@srmdg

connected to auxiliary database: SRM (not mounted)    # 注意这里状态

--arch型
RMAN>duplicate target database 
    for standby 
    from active database 
    dorecover 
    spfile 
    set "db_unique_name"="SRMDG" 
    set control_files="/u01/app/oracle/oradata2/SRMDG/control01.ctl","/u01/app/oracle/oradata2/SRMDG/control02.ctl" 
    set log_archive_dest_1="LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=srmdg" 
    set log_archive_dest_2="SERVICE=srm arch async affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=srm" 
    set db_file_name_convert="/u01/app/oracle/oradata/SRM/","/u01/app/oracle/oradata2/SRMDG/" 
    set log_file_name_convert="/u01/app/oracle/oradata/SRM/","/u01/app/oracle/oradata2/SRMDG/" 
    set fal_server="srm" comment "is primary" 
    nofilenamecheck;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
--lgwr型
--duplicate target database 
--    for standby 
--    from active database 
--    dorecover 
--    spfile 
--    set "db_unique_name"="SRMDG" 
--    set control_files="/u01/app/oracle/oradata2/SRMDG/control01.ctl","/u01/app/oracle/oradata2/SRMDG/control02.ctl" 
--    set log_archive_dest_1="LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=srmdg" 
--    set log_archive_dest_2="SERVICE=srm lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=srm" 
--    set db_file_name_convert="/u01/app/oracle/oradata/SRM/","/u01/app/oracle/oradata2/SRMDG/" 
--    set log_file_name_convert="/u01/app/oracle/oradata/SRM/","/u01/app/oracle/oradata2/SRMDG/" 
--    set fal_server="srm" comment "is primary" 
--    nofilenamecheck;

10. 查看备库相关参数

1
2
3
4
5
6
7
8
9
10
set line 200
col OPEN_MODE for a15
col PROTECTION_MODE for a25
col PROTECTION_MODE for a25
col DATABASE_ROLE for a20
col PROTECTION_LEVEL for a25
col LOG_MODE for a15
col name for a15
col SWITCHOVER_STATUS for a20
select DBID,NAME,LOG_MODE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

11. 启动备库，开始归档日志应用

1
2
alter database open;
alter database recover managed standby database disconnect from session;

12. 在备库查询

1
2
3
4
5
6
7
8
9
10
11
12
13
select client_process, process, thread#, sequence#, status from 
v$managed_standby where client_process='LGWR' or process='MRP0';

--如下所示 注意这是使用arch进程，可能监控不到应用状态
CLIENT_PROCESS           PROCESS                        THREAD#  SEQUENCE# STATUS
------------------------ --------------------------- ---------- ---------- ------------------------------------
N/A                      MRP0                                 1         63 WAIT_FOR_LOG

--如果是使用lgwr进程，查询得到如下接口则证明此次dg搭建应该没问题
CLIENT_PROCESS PROCESS   THREAD#    SEQUENCE#  STATUS
-------------- --------- ---------- ---------- ------------
N/A            MRP0      1          80         APPLYING_LOG
LGWR           RFS       1          80         IDLE

13. 数据校验

1
--手动插入主库数据，归档，备库查寻，数据已经插入并归档到到备库

主库查询

1
2
3
4
5
SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

      DBID NAME            LOG_MODE        OPEN_MODE       DATABASE_ROLE        SWITCHOVER_STATUS    PROTECTION_MODE           PROTECTION_LEVEL
---------- --------------- --------------- --------------- -------------------- -------------------- ------------------------- -------------------------
2040369693 SRM             ARCHIVELOG      READ WRITE      PRIMARY              TO STANDBY           MAXIMUM PERFORMANCE       MAXIMUM PERFORMANCE

备库查询

1
2
3
4
5
SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

      DBID NAME            LOG_MODE        OPEN_MODE       DATABASE_ROLE        SWITCHOVER_STATUS    PROTECTION_MODE           PROTECTION_LEVEL
---------- --------------- --------------- --------------- -------------------- -------------------- ------------------------- -------------------------
2040369693 SRM             ARCHIVELOG      READ ONLY WITH  PHYSICAL STANDBY     NOT ALLOWED          MAXIMUM PERFORMANCE       MAXIMUM PERFORMANCE

本地 duplicate data guard 至此搭建完毕!

因水平有限，如果有误，欢迎指正，不喜勿喷，喜欢能帮到大家 ^ · ^