使用Oracle 11g新特性 Active Database Duplication 搭建Dataguard环境

2022-08-01 22:54:10

Duplication Database 介绍

Duplicate database可以按照用途分为2种:

    duplicate database(复制出一个数据库)

    duplicate standby database(复制出一个dataguard standby数据库)

Duplicate Database又可以按照复制数据的来源不同分为2种方式：

    Active Database Duplication(从正在运行的数据库上复制数据)

    Backup-based duplication(基于备份集的数据复制)

Active Database Duplication 介绍

Active database duplication功能是从11g开始引入的一个新功能，它是对比以前版本中的基于备份集(Backup-based duplication)的复制数据库功能。    这种复制数据库要求源数据库是open状态或者mount状态，复制的过程一定要连接到源数据库，RMAN直接从源库复制数据库到Duplication服务器，这种方式不需要提前备份源库。

Duplicate Database 特点

1. 复制的Database会自动分配一个新的DBID，与源数据库的DBID不同，这样Duplicate数据库和源库可以注册到同一个catalog 数据库。

如果使用操作系统的命令来做异机复制恢复的话，新创建的数据库和源库是相同的DBID。

2. 复制的数据库可以是源库的一个完全镜像，也可以是源库的一个子集。

3. 复制的数据库和源库必须是相同的操作系统平台，我们认为同平台下的32-bit 和 64-bit是同一个平台，例如Linux IA (32-bit) 和Linux IA (64-bit)，认为是相同的平台，可以实施duplicate 功能，但是最后一定要运行下面的脚本来转换PL/SQL：

ORACLE_HOME/rdbms/admin/utlirp.sql

Active Database Duplication 和 Backup-based duplication对比

Backup-based duplication ：需要提前备份数据库，磁盘空间大小能够满足备份的需要。

Active Database Duplication 不需要提前备份，节省了磁盘空间,减少了传输备份的时间,但在复制的过程中，对源库有一定的压力，需要一定的网络带宽

Active Database Duplication 原理

1. 手动创建一个临时的pfile文件，pfile文件至少包括一个参数DB_NAME，然后启动到nomount状态。

2. RMAN从源库拷贝spfile文件到复制数据库上，并且修改spfile的名字。

3. RMAN从源库拷贝最新的control file到复制数据库，并且mount 复制数据库。

4. RMAN从源库拷贝datafile和必要的归档日志到复制数据库。

5. RMAN执行不完全的恢复。

6. RMAN创建新的control file，并且设置新的DBID。

7. 以RESETLOGS方式打开复制的database。

以上理论知识参考自下列网址：

11g 新特性—— Active Database Duplication

使用Active Database Duplication搭建步骤

一、要点

--主库

主库开启归档

主库开启force logging

主库修改dg需要的参数

主库生成duplicate需要的控制文件

主库tns中加入备库服务名

--备库

备库生成一个包含db_name的pfile

拷贝主库的密码文件放置到备库指定位置并改名

设置监听为静态监听

拷贝主库上的tns文件到备库指定位置

使用tnsping测试主库备库之间的连通性

使用pfile启动备库到nomount状态

二、具体操作、

系统说明：

主库：

IP:172.25.21.111

Linux:RHEL6.4

DB:Oracle 11.2.0.4

SID:siebuat

备库：

IP:172.25.21.222

Linux:RHEL6.4

DB:Oracle 11.2.0.4

SID:uatldg

-----------------------------------------------主库配置-----------------------------------------------

1、查看主库是否开启归档

SYS@siebuat> archive log list;

Database log mode           Archive Mode

Automatic archival           Enabled

Archive destination           /oracle/archivelog

Oldest online log sequence     21

Next log sequence to archive   23

Current log sequence           23

2、查看主库是否开启FORCE_LOGGING

SYS@siebuat> r

  1* SELECT NAME,OPEN_MODE,DATABASE_ROLE,LOG_MODE,FORCE_LOGGING FROM V$DATABASE

NAME           OPEN_MODE  DATABASE_ROLE  LOG_MODE              FORCE_

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

SIEBUAT        READ WRITE PRIMARY         ARCHIVELOG           YES

如果未开启FORCE_LOGGING，则执行：

SYS@siebuat> ALTER DATABASE FORCE LOGGING;

3、主库修改dg需要的参数

--修改主库spfile初始化参数

alter system set log_archive_config='dg_config=(siebuat,uatldg )';

--log_archive_dest_1已经设置在本地，不需要再设置,只需要设置log_archive_dest_2

alter system set log_archive_dest_2='service=uatldg LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=uatldg';

--延迟开启dest_2,等备库配置好后再enable

alter system set log_archive_dest_state_2='defer';

--设置standby文件自动管理

alter system set standby_file_management=auto;

alter system set fal_server='uatldg';

alter system set fal_client='siebuat';

4、主库生成duplicate控制文件需要的参数模板

SYS@siebuat> create pfile='/home/oracle/to_standby/duplicate.cmd' from spfile;

File created.

5、主库TNSNAMES.ORA中加入备库服务名

# Generated by Oracle configuration tools.

SIEBUAT =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.111)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = siebuat)

    )

  )

UATLDG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.222)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = uatldg)

    )

  )

6、主库修改duplicate参数文件

[oracle@primary ~]$ cat duplicate.cmd

##在这里指定主库与备库存在不同的参数，可以直接通过主库生成的pfile来修改

##标红的参数行都需要存在，其他的如果主库和备库一样，可以不添加(推荐添加上)，

##如audit_trail、compatiable、db_block_size等通常都要保持一样，不需要更改

##但如果备库的服务器配置没主库高，如主库内存100G，备库只有60G，

##则必须添加sga_target,pga_aggregate_target等参数限制备库共享内存的大小，否则会报内存不足

duplicate target database for standby from active database dorecover
spfile

set audit_file_dest='/oracle/admin/uatldg/adump'

set audit_trail='db'

set compatible='11.2.0.4.0'

set control_files='/oracle/oradata/uatldg/control01.ctl','/oracle/oradata/uatldg/control02.ctl'

set db_block_size=''

set diagnostic_dest='/oracle'

set dispatchers='(PROTOCOL=TCP) (SERVICE=siebuatXDB)'

set fal_client='uatldg'

set fal_server='siebuat'

set log_archive_config='dg_config=(siebuat,uatldg )'

set log_archive_dest_1='LOCATION=/oracle/archivelog'

set log_archive_dest_2='service=siebuat LGWR valid_for=(online_logfiles,primary_role) db_unique_name=siebuat'

set log_archive_dest_state_2='enable'

set log_archive_format='%t_%s_%r.dbf'

set open_cursors=''

set pga_aggregate_target='30M'

set processes=''

set remote_login_passwordfile='EXCLUSIVE'

set sga_target='300M'

set standby_file_management='AUTO'

set undo_tablespace='UNDOTBS1'

##下列参数为新增的在主库中没有的，对数据文件、日志文件的路径进行转换，
##尤其在主库和备库在同一台机器上时，

##如果不指定，容易造成duplicate时把主库的在线日志文件冲掉，造成主库crash。

set db_file_name_convert='/oracle/oradata/siebuat/','/oracle/oradata/uatldg/'

set log_file_name_convert='/oracle/oradata/siebuat/','/oracle/oradata/uatldg/'

set db_unique_name='uatldg';

-----------------------------------------------备库配置-----------------------------------------------

1、拷贝主库的密码文件、tnsnames.ora文件

[oracle@primary to_standby]$ scp $ORACLE_HOME/dbs/orapwsiebuat oracle@172.25.21.222:$ORACLE_HOME/dbs/orapwuatldg 
oracle@172.25.21.222's password:

orapwsiebuat 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　100% 1536 1.5KB/s 00:00

[oracle@primary to_standby]$ scp $ORACLE_HOME/network/admin/tnsnames.ora oracle@172.25.21.222:$ORACLE_HOME/network/admin/
oracle@172.25.21.222's password:
tnsnames.ora 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　100% 511 0.5KB/s 00:00

2、设置备库监听为静态监听

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uatldg)

      (ORACLE_HOME = /oracle/software/11.2.0.4/db_1)

      (SID_NAME = uatldg)

    )

  )

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.222)(PORT = 1521))

  )

ADR_BASE_LISTENER = /oracle

[oracle@standby admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-MAR-2016 13:24:32

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.25.21.222)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                09-MAR-2016 21:25:24

Uptime                    0 days 15 hr. 59 min. 8 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/software/11.2.0.4/db_1/network/admin/listener.ora

Listener Log File         /oracle/diag/tnslsnr/standby/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.21.222)(PORT=1521)))

Services Summary...

Service "uatldg" has 1 instance(s).

  Instance "uatldg", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

3、使用tnsping测试主库备库之间的连通性

####备库#########################################################################
[oracle@standby admin]$ hostname

standby

[oracle@standby admin]$ tnsping siebuat

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 10-MAR-2016 13:29:07

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.111)(PORT = )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = siebuat)))

OK ( msec)

[oracle@standby admin]$ tnsping uatldg

TNS Ping Utility for Linux: Version 11.2.0.4. - Production on -MAR- ::

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.222)(PORT = )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = uatldg)))

OK ( msec)

[oracle@standby admin]$ 
####主库#######################################################################################

[oracle@primary ~]$ hostname
primary
[oracle@primary ~]$ tnsping siebuat

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 10-MAR-2016 13:30:48

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.111)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = siebuat)))
OK (0 msec)
[oracle@primary ~]$ tnsping uatldg

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 10-MAR-2016 13:30:57

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.222)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = uatldg)))
OK (0 msec)

4、备库创建一个pfile,加入db_name即可，并启动到nomount

db_name='uatldg'

5、启动备库到nomount模式

[oracle@standby dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 10 13:38:54 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@uatldg> startup nomount

ORACLE instance started.

Total System Global Area  521936896 bytes

Fixed Size            2254824 bytes

Variable Size          209717272 bytes

Database Buffers      306184192 bytes

Redo Buffers            3780608 bytes

SYS@uatldg>

duplicate操作(在主库上执行)

[oracle@primary ~]$ rman target / auxiliary sys/passoword@uatldg cmdfile=duplicate.cmd log=standby_create.log

 Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 10 13:41:38 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights 
reserved. connected to target database: SIEBUAT (DBID=443676106)
connected to auxiliary database: UATLDG (not mounted)

RMAN>

Starting Duplicate Db at 10-MAR-16
using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=1 device type=DISK

--复制主库密码文件

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/oracle/software/11.2.0.4/db_1/dbs/orapwsiebuat' auxiliary format

 '/oracle/software/11.2.0.4/db_1/dbs/orapwuatldg'   ;

}

executing Memory Script

Starting backup at 10-MAR-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=53 device type=DISK

Finished backup at 10-MAR-16

--复制主库的控制文件
contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  '/oracle/oradata/uatldg/control01.ctl';

   restore clone controlfile to  '/oracle/oradata/uatldg/control02.ctl' from

 '/oracle/oradata/uatldg/control01.ctl';

}

executing Memory Script

Starting backup at 10-MAR-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/oracle/software/11.2.0.4/db_1/dbs/snapcf_siebuat.f tag=TAG20160310T134417 RECID=3 STAMP=906126258

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 10-MAR-16

--还原控制文件到备库

Starting restore at 10-MAR-16

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 10-MAR-16

--使用还原的控制文件启动备库到mount状态

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

--转换主库的数据文件、临时文件、undo文件等到备库路径
contents of Memory Script:

{

   set newname for tempfile  1 to

 "/oracle/oradata/uatldg/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/oracle/oradata/uatldg/system01.dbf";

   set newname for datafile  2 to

 "/oracle/oradata/uatldg/sysaux01.dbf";

   set newname for datafile  3 to

 "/oracle/oradata/uatldg/undotbs01.dbf";

   set newname for datafile  4 to

 "/oracle/oradata/uatldg/users01.dbf";

   set newname for datafile  5 to

 "/oracle/oradata/uatldg/example01.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

 "/oracle/oradata/uatldg/system01.dbf"   datafile

 2 auxiliary format

 "/oracle/oradata/uatldg/sysaux01.dbf"   datafile

 3 auxiliary format

 "/oracle/oradata/uatldg/undotbs01.dbf"   datafile

 4 auxiliary format

 "/oracle/oradata/uatldg/users01.dbf"   datafile

 5 auxiliary format

 "/oracle/oradata/uatldg/example01.dbf"   ;

   sql 'alter system archive log current';--切换归档

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oracle/oradata/uatldg/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

--进行数据文件备份并拷贝到备库

Starting backup at 10-MAR-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/oracle/oradata/siebuat/system01.dbf

output file name=/oracle/oradata/uatldg/system01.dbf tag=TAG20160310T134428

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/oracle/oradata/siebuat/sysaux01.dbf

output file name=/oracle/oradata/uatldg/sysaux01.dbf tag=TAG20160310T134428

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/oracle/oradata/siebuat/example01.dbf

output file name=/oracle/oradata/uatldg/example01.dbf tag=TAG20160310T134428

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/oracle/oradata/siebuat/undotbs01.dbf

output file name=/oracle/oradata/uatldg/undotbs01.dbf tag=TAG20160310T134428

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/oracle/oradata/siebuat/users01.dbf

output file name=/oracle/oradata/uatldg/users01.dbf tag=TAG20160310T134428

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 10-MAR-16

sql statement: alter system archive log current

contents of Memory Script:

{
switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=3 STAMP=906126342 file name=/oracle/oradata/uatldg/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=4 STAMP=906126342 file name=/oracle/oradata/uatldg/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=906126342 file name=/oracle/oradata/uatldg/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=6 STAMP=906126342 file name=/oracle/oradata/uatldg/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=7 STAMP=906126342 file name=/oracle/oradata/uatldg/example01.dbf
Finished Duplicate Db at 10-MAR-16 --duplicate standby database完成

RMAN>

Dataguard操作

查看主备库信息

--主库
SYS@siebuat> l

  1* select switchover_status,DATABASE_ROLE,open_mode,PROTECTION_MODE,PROTECTION_LEVEL from v$database

SYS@siebuat> /

SWITCHOVER_STATUS    DATABASE_ROLE   OPEN_MODE    PROTECTION_MODE       PROTECTION_LEVEL

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

TO STANDBY         PRIMARY         READ WRITE MAXIMUM PERFORMANCE      MAXIMUM PERFORMANCE

--备库
SYS@uatldg> l

  1* select switchover_status,DATABASE_ROLE,open_mode,PROTECTION_MODE,PROTECTION_LEVEL from v$database

SYS@uatldg> /

SWITCHOVER_STATUS    DATABASE_ROLE      OPEN_MODE  PROTECTION_MODE           PROTECTION_LEVEL

-------------------- -------------------- ---------- ------------------------- -------------------------
RECOVERY NEEDED      PHYSICAL STANDBY      MOUNTED    MAXIMUM PERFORMANCE       MAXIMUM PERFORMANCE

--开启备库日志应用

SYS@uatldg> alter database recover managed standby database disconnect from session;

Database altered.

SYS@uatldg> select switchover_status,DATABASE_ROLE,open_mode,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

SWITCHOVER_STATUS    DATABASE_ROLE      OPEN_MODE  PROTECTION_MODE           PROTECTION_LEVEL

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

NOT ALLOWED         PHYSICAL STANDBY      MOUNTED    MAXIMUM PERFORMANCE       MAXIMUM PERFORMANCE

　--查看是否存在gap

SYS@uatldg> select thread#,low_sequence#,high_sequence# from v$archive_gap;

no rows selected

--开启备库到ADG模式

SYS@uatldg> alter database recover managed standby database cancel;

Database altered.

SYS@uatldg> alter database open read only;

Database altered.

SYS@uatldg> alter database recover managed standby database disconnect from session;

Database altered.

SYS@uatldg> select switchover_status,DATABASE_ROLE,open_mode,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
-------------------- -------------------- ---------- ------------------------- -------------------------
NOT ALLOWED PHYSICAL STANDBY READ ONLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
WITH APPLY

SYS@uatldg>

码农公寓

Duplication Database 介绍

Active Database Duplication 介绍

Duplicate Database 特点

Active Database Duplication 和 Backup-based duplication对比

Active Database Duplication 原理

以上理论知识参考自下列网址：

11g 新特性—— Active Database Duplication

使用Active Database Duplication搭建步骤

一、要点

二、具体操作、

系统说明：

1、查看主库是否开启归档

2、查看主库是否开启FORCE_LOGGING

3、主库修改dg需要的参数

4、主库生成duplicate控制文件需要的参数模板

5、主库TNSNAMES.ORA中加入备库服务名

6、主库修改duplicate参数文件

1、拷贝主库的密码文件、tnsnames.ora文件

2、设置备库监听为静态监听

3、使用tnsping测试主库备库之间的连通性

4、备库创建一个pfile,加入db_name即可，并启动到nomount

5、启动备库到nomount模式

duplicate操作(在主库上执行)

Dataguard操作

相关文章