Oracle 11g 使用duplicate from active database 创建物理ADG的操作详细教程

程序员文章站 2022-08-05 20:16:17

1、概述：之前使用冷备的方式搭建了一个物理adg环境，但是由于冷备需要停库。因此在一些大型库上使用这种技术就不怎么实用了，而使用duplicatefrom active database则必免了这...

1、概述：

之前使用冷备的方式搭建了一个物理adg环境，但是由于冷备需要停库。因此在一些大型库上使用这种技术就不怎么实用了，而使用duplicatefrom active database则必免了这种情况，它只需要短暂的重启库让主库参数文件生效，就能完成adg的搭建。

2、环境说明

oracle：11.2.0.1.0

os：redhat 5.7

primary ip:192.168.2.111/24

hostname:dba1.test.com

db_name=ora11g

standby ip:192.168.2.112/24

hostname:dba2.test.com

db_name=ora11g

3、操作步骤：

3.1. primary端操作：

3.1.1.设置归档模式

sql> archive log list;

sql> shutdown immediate

sql> startup mount

sql> alter database archivelog;

sql> archive log list;

3.1.2. primary设置force logging

sql> alterdatabase force logging;

sql> selectforce_logging from v$database;

force_log

---------

yes

3.1.3.配置oracle net（主、从上修改）

注意：在primary库和standby都需要修改，可以将primary的listener.ora\tnsname.ora拷贝到standby上在做相应修改，如ip地址。修改完后重启listener。

主库：

listener.ora

[oracle@dba1admin]$ cat listener.ora

# listener.oranetwork configuration file:/u01/oracle/product/11.2.0/network/admin/listener.ora

# generated byoracle configuration tools.

--配置静态注册

sid_list_listener=

(sid_list =

(sid_desc =

(global_dbname = ora11g)

(oracle_home =/u01/oracle/product/11.2.0)

(sid_name = ora11g)

)

listener =

(description_list =

(description =

(address = (protocol = tcp)(host =192.168.2.111)(port = 1521))

)

adr_base_listener= /u01/oracle

tnsname.ora

[oracle@dba1admin]$ cat tnsnames.ora

# tnsnames.oranetwork configuration file:/u01/oracle/product/11.2.0/network/admin/tnsnames.ora

# generated byoracle configuration tools.

ora11g_dba1 =

(description =

(address = (protocol = tcp)(host = 192.168.2.111)(port = 1521))

(connect_data =

(server = dedicated)

(service_name = ora11g)

)

ora11g_dba2 =

(description =

(address = (protocol = tcp)(host =192.168.2.112)(port = 1521))

(connect_data =

(server = dedicated)

(service_name = ora11g)

)

[oracle@dba2admin]$ tnsping ora11g_dba2

[oracle@dba2admin]$ tnsping ora11g_dba1

3.1.4.添加data guard 参数

创建pfile

[oracle@dba1~]$ sqlplus / as sysdba

sys@ora11g>create pfile from spfile;

[oracle@dba1~]$ cd $oracle_home/dbs

修改参数文件

[oracle@dba1dbs]$ vim initora11g.ora

ora11g.__db_cache_size=75497472

ora11g.__java_pool_size=4194304

ora11g.__large_pool_size=4194304

ora11g.__oracle_base='/u01/oracle'#oracle_baseset from environment

ora11g.__db_cache_size=75497472

ora11g.__java_pool_size=4194304

ora11g.__large_pool_size=4194304

ora11g.__oracle_base='/u01/oracle'#oracle_baseset from environment

ora11g.__pga_aggregate_target=146800640

ora11g.__sga_target=222298112

ora11g.__shared_io_pool_size=0

ora11g.__shared_pool_size=125829120

ora11g.__streams_pool_size=4194304

*.audit_file_dest='/u01/oracle/admin/ora11g/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/oradata/ora11g/control01.ctl','/u01/oracle/flash_recovery_area/ora11g/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_recovery_file_dest='/u01/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4039114752

*.diagnostic_dest='/u01/oracle'

*.dispatchers='(protocol=tcp)(service=ora11gxdb)'

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

*.memory_target=367001600

*.nls_language='simplifiedchinese'

*.nls_territory='china'

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='exclusive'

*.sessions=170

*.undo_tablespace='undotbs1'

--添加如下参数，上面如有重复的参数删除

*.db_name='ora11g'

*.db_unique_name='primary'

*.log_archive_config='dg_config=(primary,standby)'

*.log_archive_dest_1='location=/orachivelogvalid_for=(all_logfiles,all_roles) db_unique_name=primary'

--注意：ora11g_db1/ora11g_db2为tns文件中的network service name

*.log_archive_dest_2='service=ora11g_dba2reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

--注意：如果将这个文件拷贝到standby库，要改成fal_server=’ora11g_dba1’,fal_client=’ora11g_dba2’，这两个值写错了将导致不能传输日志。

*.fal_server='ora11g_dba2'

*.fal_client='ora11g_dba1'

注意：

在oracle 11g的data guard中，standby_archive_dest参数已经被取消了。

standby库归档文件的存放位置规则如下：

（1）当log_archive_dest_n设置了valid_for=(all_logfiles,all_roles)，那么在不定义standby_archive_dest参数时，oracle就会选择log_archive_dest_n参数作为归档目标。

（2）如果在第一步设置的同时，又独立设置log_archive_dest_n参数为valid_for=(standby_logfile,*) 属性，那么当compatible参数大于10.0的时候，会自动的选择任意一个log_archive_dest_n的值。

（3）如果log_archive_dest_n没有设置的话，默认位置是：

$oracle_home/dbs.

不过valid_for参数的默认值就是all_logfiles和all_roles.所以只要设置了本地的归档位置，远程的归档文件也会放到这个目录下面。

3.1.5.用新pfile重启主库

sys@ora11g>shut immediate;

database closed.

databasedismounted.

oracle instanceshut down.

sys@ora11g>create spfile from pfile='/dbs/initora11g.ora';

3.2. standby端设置：

3.2.1.创建相关目录结构

--这里创建的目录和primary库相同，如不同要在参数文件里转换一下。

--如果没有创建以下目录，在使用sqlplus / as sysdba时会报错：

[oracle@dba2bin]$ sqlplus / as sysdba

sql*plus:release 11.2.0.1.0 production on fri feb 22 13:12:17 2013

error:

ora-09925:

linuxerror: 2: no such file or directory

additionalinformation: 9925

ora-01075:

[oracle@dba2admin]$ pwd

/u01/oracle/admin

[oracle@dba2admin]$ mkdir ora11g/adump ora11g/dpdump ora11g/pfile -p

[root@dba2 /]$mkdir /oradata/ora11g -p

[root@dba2 /]$chown oracle.oinstall /oradata -r

[oracle@dba2flash_recovery_area]$ pwd

/u01/oracle/flash_recovery_area

[oracle@dba2flash_recovery_area]$ mkdir ora11g

3.2.1.创建standby的口令文件

注意：主从库的sys口令相同

[oracle@dba2 /]$orapwd file=/dbs/orapwora11g password=oracle entries=10 force=y ignorecase=y

3.2.3.创建standby的初始化参数：

从主库上拷贝参数文件到备库

[oracle@dba1 dbs]$scp initora11g.ora 192.168.2.112:$oracle_home/dbs/

在备库上进行修改：

[oracle@dba2 dbs]$vim initora11g.ora

ora11g.__db_cache_size=75497472

ora11g.__java_pool_size=4194304

ora11g.__large_pool_size=4194304

ora11g.__oracle_base='/u01/oracle'#oracle_baseset from environment

ora11g.__pga_aggregate_target=146800640

ora11g.__sga_target=222298112

ora11g.__streams_pool_size=4194304

*.audit_file_dest='/u01/oracle/admin/ora11g/adump'

--将audit_trail的值由db改成none,不然在alter日志中会报“audit_trail initializationparameter is changed to os, as db is not compatible for database opened with read-onlyaccess”

*.audit_trail='none'

*.compatible='11.2.0.0.0'

*.control_files='/oradata/ora11g/control01.ctl','/u01/oracle/flash_recovery_area/ora11g/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_recovery_file_dest='/u01/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4039114752

*.diagnostic_dest='/u01/oracle'

*.dispatchers='(protocol=tcp)(service=ora11gxdb)'

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

*.memory_target=367001600

*.nls_language='simplifiedchinese'

*.nls_territory='china'

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='exclusive'

*.sessions=170

*.undo_tablespace='undotbs1'

--添加如下参数，ora11g_dba1为tnsname.ora中的网络名，上面如有重复的参数删除

*.db_name='ora11g'

*.db_unique_name='standby'

*.log_archive_config='dg_config=(primary,standby)'

*.log_archive_dest_1='location=/orachivelogvalid_for=(all_logfiles,all_roles) db_unique_name=standby'

*.log_archive_dest_2='service=ora11g_dba1reopen=120 lgwr async valid_for=(online_logfiles,primary_role)

*.db_unique_name=primary'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

--备库上的fal_server、fal_client的参数千万不要写错，它的值是tnsname.ora文件中的网络名，不然备库不能接收日志，而且在启动备库到open时会报：ora-01152: file 1 was not restoredfrom a sufficiently old backup

*.fal_server='ora11g_dba1'

*.fal_client='ora11g_dba2'

注意：如果主从数据目录结构不同需要加以下参数

*.log_file_name_convert=’primary路径’,’standby路径’

*.db_file_name_convert=’primary路径’,’standby路径’

3.2.4.用pfile 将standby 启动到nomount状态：

[oracle@dba2dbs]$ sqlplus / as sysdba

idle> startupnomount pfile='/dbs/initora11g.ora';

3.2.5.开始duplicate

[oracle@dba2dbs]$rman target sys/oracle@ora11g_dba1 auxiliarysys/oracle@ora11g_dba2

recovery manager:release 11.2.0.1.0 - production on fri feb 22 13:38:00 2013

connected totarget database: ora11g (dbid=4173278619)

connected toauxiliary database: ora11g (not mounted)

--执行duplicate的时候，如果源库和目标库目录相同，那么在duplicate时，需要加上nofilenamecheck，不同则不需要加nofilenamecheck,否则会报：rman-05501: aborting duplication of targetdatabase

rman>duplicate target database for standby from active databasenofilenamecheck;

startingduplicate db at 22-feb-13

using targetdatabase control file instead of recovery catalog

allocatedchannel: ora_aux_disk_1

channelora_aux_disk_1: sid=20 device type=disk

contents ofmemory script:

{

backup as copy reuse

--用duplicate创建standby会复制口令文件

targetfile'/u01/oracle/product/11.2.0/dbs/orapwora11g' auxiliary format

'/u01/oracle/product/11.2.0/dbs/orapwora11g' ;

}

executing memoryscript

starting backupat 22-feb-13

allocatedchannel: ora_disk_1

channel ora_disk_1:sid=41 device type=disk

finished backupat 22-feb-13

--创建控制文件

contents ofmemory script:

{

backup as copy current controlfile forstandby auxiliary format'/oradata/ora11g/control01.ctl';

restore clone controlfile to '/u01/oracle/flash_recovery_area/ora11g/control02.ctl'from

'/oradata/ora11g/control01.ctl';

}

executing memoryscript

starting backupat 22-feb-13

using channelora_disk_1

channelora_disk_1: starting datafile copy

copying standbycontrol file

output filename=/u01/oracle/product/11.2.0/dbs/snapcf_ora11g.f tag=tag20130222t133953recid=1 stamp=808061996

channelora_disk_1: datafile copy complete, elapsed time: 00:00:07

finished backup at22-feb-13

starting restoreat 22-feb-13

using channelora_aux_disk_1

channelora_aux_disk_1: copied control file copy

finished restoreat 22-feb-13

--将备库启动到mount standby standby database

contents ofmemory script:

{

sql clone 'alter database mount standbydatabase';

}

executing memoryscript

sql statement:alter database mount standby database

--转换数据文件目录

contents ofmemory script:

{

set newname for tempfile 1 to

"/oradata/ora11g/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/oradata/ora11g/system01.dbf";

set newname for datafile 2 to

"/oradata/ora11g/sysaux01.dbf";

set newname for datafile 3 to

"/oradata/ora11g/undotbs01.dbf";

set newname for datafile 4 to

"/oradata/ora11g/users01.dbf";

backup as copy reuse

datafile1 auxiliary format

"/oradata/ora11g/system01.dbf" datafile

2 auxiliary format

"/oradata/ora11g/sysaux01.dbf" datafile

3 auxiliary format

"/oradata/ora11g/undotbs01.dbf" datafile

4 auxiliary format

"/oradata/ora11g/users01.dbf" ;

sql 'alter system archive log current';

}

executing memoryscript

executingcommand: set newname

renamed tempfile1 to /oradata/ora11g/temp01.dbf in control file

executingcommand: set newname

starting backupat 22-feb-13

using channelora_disk_1

--开始copy datafile，如果数据文件比较大，这里会比较慢

channelora_disk_1: starting datafile copy

input datafilefile number=00001 name=/oradata/ora11g/system01.dbf

output filename=/oradata/ora11g/system01.dbf tag=tag20130222t134011

channelora_disk_1: datafile copy complete, elapsed time: 00:02:10

channelora_disk_1: starting datafile copy

input datafilefile number=00002 name=/oradata/ora11g/sysaux01.dbf

output filename=/oradata/ora11g/sysaux01.dbf tag=tag20130222t134011

channelora_disk_1: datafile copy complete, elapsed time: 00:01:38

channelora_disk_1: starting datafile copy

input datafilefile number=00003 name=/oradata/ora11g/undotbs01.dbf

output filename=/oradata/ora11g/undotbs01.dbf tag=tag20130222t134011

channel ora_disk_1:datafile copy complete, elapsed time: 00:00:27

channelora_disk_1: starting datafile copy

input datafilefile number=00004 name=/oradata/ora11g/users01.dbf

output filename=/oradata/ora11g/users01.dbf tag=tag20130222t134011

channelora_disk_1: datafile copy complete, elapsed time: 00:00:03

finished backupat 22-feb-13

sql statement:alter system archive log current

contents ofmemory script:

{

switch clone datafile all;

}

executing memoryscript

datafile 1switched to datafile copy

input datafilecopy recid=1 stamp=808062273 file name=/oradata/ora11g/system01.dbf

datafile 2switched to datafile copy

input datafilecopy recid=2 stamp=808062273 file name=/oradata/ora11g/sysaux01.dbf

datafile 3switched to datafile copy

input datafilecopy recid=3 stamp=808062273 file name=/oradata/ora11g/undotbs01.dbf

datafile 4switched to datafile copy

input datafilecopy recid=4 stamp=808062273 file name=/oradata/ora11g/users01.dbf

--到这里完成

finishedduplicate db at 22-feb-13

rman>

注意：

duplicate不会将primary上的temp表空间复制到standby库上,但是在将standby库启动到open时会自动创建，standby库上的redo log oracle也将会重新创建。

sys@ora11g>host ls -l /oradata/ora11g/temp01.dbf

ls:/oradata/ora11g/temp01.dbf:没有那个文件或目录

sys@ora11g>select * from v$logfile;

group#status type member is_rec

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

3 online/u01/oracle/flash_recovery_area/standby/onlinyes

elog/o1_mf_3_8lg1g8sw_.log

2 online/u01/oracle/flash_recovery_area/standby/onlinyes

elog/o1_mf_2_8lg1g676_.log

1 online/u01/oracle/flash_recovery_area/standby/onlinyes

elog/o1_mf_1_8lg1g2ok_.log

3.2.6注意事项

如果使用的是非catalog，在rman 连接时，加上nocatalog关键字，如：

[oracle@dba2 dbs]$ rman targetsys/oracle@ora11g_dba1 auxiliary sys/oracle@ora11g_dba2 nocatalog

connectedto target database: dg(dbid=1679060044)

usingtarget databasecontrol file instead of recovery catalog

connectedto auxiliary database: dg (notmounted)

不然会报如下错误：

rman-05501:aborting duplication of targetdatabase

3.3.建立完adg后续工作

3.3.1.创建备库spfile,并用spfile启库：

sys@ora11g> create spfile from pfile;

sys@ora11g> shut immediate;

sys@ora11g> startup nomount;

sys@ora11g> alter database mount standby database;

注：除了adg功能需要的相关参数，其它参数和主库参数文件最好一致。

3.3.2.启用mrp进程

复制结束后的standby 只启动到mount standby 的状态。并没有启动mrp的应用归档程序。所以这个时候查询主备库，归档是不同步的。需要手动的启动mrp进程。

sql> alterdatabase recover managed standby database disconnect from session;

3.3.4.备库standby redo log 问题：

在duplicate结束后，备库没有添加standbyredo log file。但是主库采用的是：lgwrasync传送的日志。当备库的rfs 进程接收到日志后，发现备库没有standbyredo log的时候，备库会自动用arch将其写入归档文件。

--没有standby redo log ,启用时实应用日志会报以下错

sys@ora11g>alter database recover managed standby database using current logfiledisconnect from session;

alter databaserecover managed standby database using current logfile disconnect from session

error at line 1:

ora-38500: usingcurrent logfile option not available without standby redo logs

3.3.5如何计算standby redo log数量

standby redo log日志文件组的个数依照下面的原则进行计算

standby redo log组数公式>=(每个instance日志组个数+1)*instance个数

例如在环境中，只有2个节点，这个节点有三组redo，所以

standby redo log组数公式>=(3+1)*2 = 8

所以需要创建8组standbyredo log

3.3.6.在备库添加standby redo log：

sys@ora11g>alter database add standby logfile

2group 4 ('/oradata/ora11g/dglog04.log') size 50m,

3group 5 ('/oradata/ora11g/dglog05.log') size 50m,

4group 6 ('/oradata/ora11g/dglog06.log') size 50m,

5group 7 ('/oradata/ora11g/dglog07.log') size 50m,

6group 8 ('/oradata/ora11g/dglog08.log') size 50m,

7group 9 ('/oradata/ora11g/dglog09.log') size 50m,

8group 10 ('/oradata/ora11g/dglog10.log') size 50m,

9group 11 ('/oradata/ora11g/dglog11.log') size 50m;

alter databaseadd standby logfile

error at line 1:

ora-01156:recovery or flashback in progress may need access to files*

--在备库添加standby redo log需要先停mrp

alterdatabase add standby logfile

group 4('/oradata/ora11g/dglog04.log') size 50m,

group 5('/oradata/ora11g/dglog05.log') size 50m,

group 6('/oradata/ora11g/dglog06.log') size 50m,

group 7('/oradata/ora11g/dglog07.log') size 50m,

group 8('/oradata/ora11g/dglog08.log') size 50m,

group 9('/oradata/ora11g/dglog09.log') size 50m,

group 10('/oradata/ora11g/dglog10.log') size 50m,

group 11('/oradata/ora11g/dglog11.log') size 50m;

3.3.7.在主库也添加一下standby redo log

alterdatabase add standby logfile

group 4('/oradata/ora11g/dglog04.log') size 50m,

group 5('/oradata/ora11g/dglog05.log') size 50m,

group 6('/oradata/ora11g/dglog06.log') size 50m,

group 7('/oradata/ora11g/dglog07.log') size 50m,

group 8('/oradata/ora11g/dglog08.log') size 50m,

group 9('/oradata/ora11g/dglog09.log') size 50m,

group 10('/oradata/ora11g/dglog10.log') size 50m,

group 11('/oradata/ora11g/dglog11.log') size 50m;

3.3.8.启用real-time apply，从而实现real-timequery：

sql> alterdatabase recover managed standby database cancel;

sys@ora11g> alterdatabase open;

sys@ora11g>alter database recover managed standby databaseusing current logfile disconnect from session;

3.3.9.验证real-time apply 和real-timequery：

primary：

sql> createtable fengg(id number,name varchar2(30));

sql> insertinto fengg values(1,'fengg');

sql> commit;

standby：

sql> selectopen_mode from v$database;

open_mode

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

read only withapply

sys@ora11g> select * from fengg;

id name

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

1 fengg

primary:

sys@ora11g> select sequence#,applied from v$archived_log;

14no --在线redo日志是否被应用

14yes --备库上的standbyredo log是否被应用

15no

15yes

4、小结：

通过几天反复的实验，终于完成了整个实验，也遇到一些问题在这里总结一下：

1），vmware的快照功能为了省了不少时间，不然搭建实验环境很浪费时间。

2），网络连接文件listener.ora/tnsname.ora非常的敏感，因此最好少改动这个文件，并做好备份，在需要时直接复制备份文件。

3），adg的参数文件中的参数一定要严格按照官方文档操作并了解相关参数的用途。

4），备库上的fal_server、fal_client的参数千万不要写错，否则备库不能接收日志。

5），主从库的sys口令必须完全相同。

6），使用tnsping命令测试主从库网络连通性。

7），切记，经常观察alter 报警日志的相关操作信息。

上一篇：预防食物中毒应该怎么做

下一篇： oracle初学操作讲解

Oracle 11g 使用duplicate from active database 创建物理ADG的操作详细教程

1、概述：

2、环境说明

3、操作步骤：

3.1. primary端操作：

3.1.1.设置归档模式

3.1.2. primary设置force logging

3.1.3.配置oracle net（主、从上修改）

3.1.4.添加data guard 参数

3.1.5.用新pfile重启主库

3.2. standby端设置：

3.2.1.创建相关目录结构

3.2.1.创建standby的口令文件

3.2.3.创建standby的初始化参数：

3.2.4.用pfile 将standby 启动到nomount状态：

3.2.5.开始duplicate

3.2.6注意事项

3.3.建立完adg后续 工作

3.3.1.创建备库spfile,并用spfile启库：

3.3.2.启用mrp进程

3.3.4.备库standby redo log 问题：

3.3.5如何计算standby redo log数量

3.3.6.在备库添加standby redo log：

3.3.7.在主库也添加一下standby redo log

3.3.8.启用real-time apply，从而实现real-timequery：

3.3.9.验证real-time apply 和real-timequery：

4、小结：