暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片
首页 / dg & dg Broker 搭建流程

dg & dg Broker 搭建流程

原创 搭春绿 恩墨学院 2022-02-22
1684

dg Broker 搭建流程

- ip 主机名
主: 172.16.19.54 orcl1
备: 172.16.19.55 orcl2

注: 主备都安装了数据库软件, 并且主库已有数据库实例，备库暂不需要实例
\$ shell执行
sql\> sqlplus执行
rman\> rman执行
dgmgrl\> dgmgrl执行

dg搭建

开启归档(主库)

--设置db_unique_name(这里设置是因为要重启后生效)
sql> alter system set db_unique_name=orcl1 scope=spfile;

--关库并启动mount状态
sql> SHUTDOWN IMMEDIATE;
sql> STARTUP MOUNT;

--开启归档并open数据库
sql> ALTER DATABASE ARCHIVELOG;
sql> ALTER DATABASE OPEN;

force logging(主库)

--开启强制归档/闪回/备库自动文件管理
sql> alter database force logging;
sql> alter database flashback on;
sql> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

开始搭建dg

相关目录:(主库)

数据文件目录:
/u01/app/oracle/oradata/orcl/

日志文件目录:
/u01/app/oracle/oradata/orcl/

添加备库standby日志文件(主库)

sql> alter database add standby logfile group 11 ('/u01/app/oracle/oradata/orcl/stanbylog11.log')size 50m;
sql> alter database add standby logfile group 12 ('/u01/app/oracle/oradata/orcl/stanbylog12.log')size 50m;
sql> alter database add standby logfile group 13 ('/u01/app/oracle/oradata/orcl/stanbylog13.log')size 50m;
sql> alter database add standby logfile group 14 ('/u01/app/oracle/oradata/orcl/stanbylog14.log')size 50m;

确认日志文件(主库)

SQL> select * from v$logfile;
	      GROUP# STATUS  TYPE    MEMBER						IS_
-------------------- ------- ------- -------------------------------------------------- ---
		   3	     ONLINE  /u01/app/oracle/oradata/orcl/redo03.log		NO
		   2	     ONLINE  /u01/app/oracle/oradata/orcl/redo02.log		NO
		   1	     ONLINE  /u01/app/oracle/oradata/orcl/redo01.log		NO
		  11	     STANDBY /u01/app/oracle/oradata/orcl/stanbylog11.log	NO
		  12	     STANDBY /u01/app/oracle/oradata/orcl/stanbylog12.log	NO
		  13	     STANDBY /u01/app/oracle/oradata/orcl/stanbylog13.log	NO
		  14	     STANDBY /u01/app/oracle/oradata/orcl/stanbylog14.log	NO
7 rows selected.

编辑tnsnames(主备库)

# 添加主备的tnsnames
$ cat  >> $ORACLE_HOME/network/admin/tnsnames.ora << EOF 
orcl1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.19.54)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
orcl2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.19.55)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
EOF

编辑静态监听(主备库)

# 添加主备库的静态监听
$ cat  >> $ORACLE_HOME/network/admin/listener.ora << EOF 
SID_LIST_LISTENER=
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME = orcl)
    )
  )
EOF

# orcl1/orcl2都要互ping
$ tnsping orcl1
$ tnsping orcl2

编辑归档配置文件(主库)

-- 设置dg_config后面带的是db_unique_name
SQL> alter system set log_archive_config='dg_config=(orcl1,orcl2)';

-- 设置主库的归档路径为log_archive_dest_1,放在闪回区
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl1';

-- 设置传输到备库的路径,service后为tnsnames
SQL> alter system set log_archive_dest_2='service=orcl2 LGWR SYNC AFFIRM delay=0 optional max_failure=0 max_connections=1 reopen=2 db_unique_name=orcl2 net_timeout=2 valid_for=(online_logfile,primary_role)';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

传密码文件(从主库传到备库)

$ ssh-kegen
$ ssh-copy-id oracle@172.16.19.54
$ scp $ORACLE_HOME/dbs/orapworcl oracle@172.16.19.55:/u01/app/oracle/product/11.2.0.4/db_1/dbs/

生成pfile并编辑(主库生成,备库编辑后应用)

SQL> create pfile='/tmp/pfile.ora' from spfile;

-- 编辑后pfile如下,主要改db_unique_name､路径要匹配备库本地环境
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='ORCL2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.open_cursors=300
*.pga_aggregate_target=155189248
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=466616320
*.undo_tablespace='UNDOTBS1


# 临时写入备库/tmp/pfile.ora
$ cat >> /tmp/pfile.ora << EOF
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='ORCL2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.open_cursors=300
*.pga_aggregate_target=155189248
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=466616320
*.undo_tablespace='UNDOTBS1'
EOF

备库创建必要文件夹(备库)

$ mkdir -p /u01/app/oracle/admin/orcl/adump
$ mkdir -p /u01/app/oracle/oradata/orcl
$ mkdir -p /u01/app/oracle/fast_recovery_area

启备库到nomount状态(备库)

SQL> create spfile from pfile='/tmp/pfile.ora'
SQL> startup nomount

rman 连接主备库(主库)

$ rman TARGET sys/password@orcl1 AUXILIARY sys/password@orcl2

开始同步(主库)

情况1

主备库数据文件､日志文件的路径一致,不需要路径转换的话

RMAN> DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  NOFILENAMECHECK;

情况2

主备库数据文件､日志文件的路径不一致,需要路径转换,可以使用spfile子句
比如windows到linux路径不一样､RAC到单实例路径不一样等待

RMAN> DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='orcl2' COMMENT 'Is standby'
    SET db_file_name_convert='/original/directory/path1/','/new/directory/path1/','/original/directory/path2/','/new/directory/path2/'
    SET log_file_name_convert='/original/directory/path1/','/new/directory/path1/','/original/directory/path2/','/new/directory/path2/'
    SET job_queue_processes='0'
  NOFILENAMECHECK;

参数解释

  • FOR STANDBY: DUPLICATE命令将用于备库搭建,且不会强制进行DBID更改｡
  • FROM ACTIVE DATABASE: DUPLICATE将直接从源数据文件创建,而不需要额外的备份
  • DORECOVER: DUPLICATE将包括恢复步骤,将备用服务器拉到当前时间点｡
  • SPFILE: 允许在spfile从源服务器复制时重置它的值｡
  • NOFILENAMECHECK: 不检查目标文件位置｡

duplicate日志

Starting Duplicate Db at 21-FEB-22
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapworcl' auxiliary format 
 '/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapworcl'   ;
}
executing Memory Script

Starting backup at 21-FEB-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
Finished backup at 21-FEB-22

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/orcl/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' from 
 '/u01/app/oracle/oradata/orcl/control01.ctl';
}
executing Memory Script

Starting backup at 21-FEB-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_orcl.f tag=TAG20220221T160318 RECID=1 STAMP=1097251398
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-FEB-22

Starting restore at 21-FEB-22
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 21-FEB-22

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/orcl/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/orcl/system01.dbf";
   set newname for datafile  2 to 
 "/u01/app/oracle/oradata/orcl/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/orcl/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/orcl/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/app/oracle/oradata/orcl/system01.dbf"   datafile 
 2 auxiliary format 
 "/u01/app/oracle/oradata/orcl/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/u01/app/oracle/oradata/orcl/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/u01/app/oracle/oradata/orcl/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/orcl/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 21-FEB-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u01/app/oracle/oradata/orcl/system01.dbf tag=TAG20220221T160324
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf tag=TAG20220221T160324
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf tag=TAG20220221T160324
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=/u01/app/oracle/oradata/orcl/users01.dbf tag=TAG20220221T160324
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-FEB-22

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2022_02_21/o1_mf_1_5_k16kvf9o_.arc" auxiliary format 
 "/u01/app/oracle/fast_recovery_area/ORCL2/archivelog/2022_02_21/o1_mf_1_5_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 21-FEB-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=5 RECID=2 STAMP=1097251437
output file name=/u01/app/oracle/fast_recovery_area/ORCL2/archivelog/2022_02_21/o1_mf_1_5_060mdejd_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 21-FEB-22

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/ORCL2/archivelog/2022_02_21/o1_mf_1_5_060mdejd_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORCL2/archivelog/2022_02_21/o1_mf_1_5_060mdejd_.arc

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1097251438 file name=/u01/app/oracle/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1097251438 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1097251438 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1097251438 file name=/u01/app/oracle/oradata/orcl/users01.dbf

contents of Memory Script:
{
   set until scn  973019;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 21-FEB-22
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL2/archivelog/2022_02_21/o1_mf_1_5_060mdejd_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL2/archivelog/2022_02_21/o1_mf_1_5_060mdejd_.arc thread=1 sequence=5
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-FEB-22
Finished Duplicate Db at 21-FEB-22

duplicate完毕(备库)

-- 开启同步
SQL> alter database recover managed standby database using current logfile disconnect from session;
-- 暂停同步
SQL> alter database recover managed standby database cancel;

开启dg_broker(主备库)

SQL> ALTER SYSTEM SET dg_broker_start=true;

dgmgrl配置

$ dgmgrl sys/password@orcl1
Welcome to DGMGRL, type "help" for information.
Connected.

DGMGRL> CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS orcl1 CONNECT IDENTIFIER IS orcl1;
Configuration "my_dg_config" created with primary database "orcl1"
DGMGRL> ADD DATABASE orcl2 AS CONNECT IDENTIFIER IS orcl2 MAINTAINED AS PHYSICAL;
Database "orcl2" added

DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Databases:
    orcl1 - Primary database
    orcl2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

dg_broker 切换

DGMGRL> switchover to orcl2
Performing switchover NOW, please wait...
Operation requires a connection to instance "orcl" on database "orcl2"
Connecting to instance "orcl"...
Connected.
New primary database "orcl2" is opening...
Operation requires startup of instance "orcl" on database "orcl1"
Starting instance "orcl"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
	start up and mount instance "orcl" of database "orcl1"

-- 切换有报错,连不上数据库
  • 解决方案
DGMGRL> show database  orcl2 staticconnectidentifier

  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL2_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'

DGMGRL> show database  orcl1 staticconnectidentifier

  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL1_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'

-- 编辑orcl1和orcl2的链接
DGMGRL> edit database orcl1 set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.19.54)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))';

Property "staticconnectidentifier" updated

DGMGRL> edit database orcl2 set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.19.55)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))';

Property "staticconnectidentifier" updated
  • 再次切换就没有报错了
DGMGRL> switchover to orcl2
Performing switchover NOW, please wait...
Operation requires a connection to instance "orcl" on database "orcl2"
Connecting to instance "orcl"...
Connected.
New primary database "orcl2" is opening...
Operation requires startup of instance "orcl" on database "orcl1"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "orcl2"
DGMGRL>
墨力计划oracle dgdataguar broker
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论