为了账号安全，请及时绑定邮箱和手机立即绑定
首页 手记 Oracle...

Oracle dataGuard专题：Rman通过duplicate创建standby

标签：
Oracle


rman提供了更便捷的方式来创建standby，方法就是通过duplicate命令，下面做一个详细介绍：

1. 试验环境

[oracle@gaojf orcl]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 6 08:54:45 2006

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL>

2. 确认主库处于归档模式

SQL> archive log list

Database log mode            Archive Mode

Automatic archival             Enabled

Archive destination            /free/oracle/orabak

Oldest online log sequence     275

Next log sequence to archive   277

Current log sequence           277

SQL>

 

3：创建备库instance

windows平台利用oradim工具创建一个新的instance，unix/linux平台设置新的ORACLE_SID即可，我的是linux平台，设置ORACLE_SID即可。

4：准备好备库的参数文件

本次试验在两个系统上建主备库，另外如果主备库都在一起的话，db_name设置为主库名，lock_name_space设置为备库名，同时要注意设置好DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT参数。

备库主要的几个参数如下：

*.log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)' 

*.LOG_ARCHIVE_DEST_1='LOCATION=/free/oracle/orabak VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'

*.LOG_ARCHIVE_DEST_2='SERVICE=primary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY LGWR SYNC AFFIRM'

*.db_unique_name='STANDBY'

*.FAL_CLIENT='STANDBY'

*.FAL_SERVER='PRIMARY'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.STANDBY_ARCHIVE_DEST='/free/oracle/orabak'

*.STANDBY_FILE_MANAGEMENT='AUTO'

主库几个主要参数如下：

*.log_archive_config='DG_CONFIG=(PRIMAYR,STANDBY)'

*.DB_UNIQUE_NAME='PRIMARY'

*.LOG_ARCHIVE_DEST_1= 'LOCATION=/free/oracle/orabak'

#*.LOG_ARCHIVE_DEST_2='SERVICE=STANDBYVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY LGWR SYNC AFFIRM' ＃LGWR方式

*.LOG_ARCHIVE_DEST_2='SERVICE=STANDBY DB_UNIQUE_NAME=STANDBY ARCH SYNC REOPEN=10' ＃arch方式

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.LOG_ARCHIVE_FORMAT='arc%s%t%r.arc'

*.FAL_SERVER='STANDBY'

*.FAL_CLIENT='PRIMARY'

*.STANDBY_FILE_MANAGEMENT='AUTO'

*.standby_archive_dest='/free/oracle/orabak'

5.在备库生成password file

[oracle@gaojfdbs]$Orapwd file=/free/oracle/product/10.2.0/db_1/orapworcl.ora password=orcl entries=5

或者可以直接复制主库密码文件到备库相应的位置也可。

6.配置网络

配置主备库的listener.ora,tnsnames.ora。修改完lisner.ora后注意重启监听。

主库Listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /free/oracle/product/10.2.0/db_1)

      (PROGRAM = extproc)

    )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

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

    )

  )

备库Listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /free/oracle/product/10.2.0/db_1)

      (PROGRAM = extproc)

    )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

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

    )

  )

tnsnames.ora（主备库配置一样）

PRIMARY =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = primary)

    )

  )

STANDBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = standby)

    )

  )

 

7.利用rman备份主库

注意同时备库控制文件

[oracle@gaojf ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Dec 7 10:10:02 2006

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

connected to target database: ORCL (DBID=1132959697)

RMAN> backup full format='/free/oracle/FULL_%d_%T_%s' database  include current controlfile for standby

2> plus archivelog format='/free/oracle/arch_%d_%T_%s';

Starting backup at 07-DEC-06

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=159 devtype=DISK

skipping archive log file /free/oracle/orabak/arc2951605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc2961605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc2971605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc2981605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc2991605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3001605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3011605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3021605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3031605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3041605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3051605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3061605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3071605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3081605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3091605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3101605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3111605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3121605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3131605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3141605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3151605985194.arc; already backed up 1 time(s)

skipping archive log file /free/oracle/orabak/arc3161605985194.arc; already backed up 1 time(s)

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=317 recid=436 stamp=608584285

input archive log thread=1 sequence=318 recid=439 stamp=608585430

input archive log thread=1 sequence=319 recid=441 stamp=608585703

channel ORA_DISK_1: starting piece 1 at 07-DEC-06

channel ORA_DISK_1: finished piece 1 at 07-DEC-06

piece handle=/free/oracle/arch_ORCL_20061207_56 tag=TAG20061207T193504 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 07-DEC-06

Starting backup at 07-DEC-06

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/free/oracle/oradata/orcl/system01.dbf

input datafile fno=00003 name=/free/oracle/oradata/orcl/sysaux01.dbf

input datafile fno=00006 name=/free/oracle/oradata/orcl/gaojf1db.dbf

input datafile fno=00007 name=/free/oracle/oradata/orcl/gaojf2db.dbf

input datafile fno=00008 name=/free/oracle/oradata/orcl/gaojf3db.dbf

input datafile fno=00010 name=/free/oracle/oradata/orcl/cicrodb1.dbf

input datafile fno=00011 name=/free/oracle/oradata/orcl/cicrodb11.dbf

input datafile fno=00009 name=/free/oracle/oradata/orcl/gaojf4db.dbf

input datafile fno=00002 name=/free/oracle/oradata/orcl/undotbs01.dbf

input datafile fno=00005 name=/free/oracle/oradata/orcl/gaojfdb.dbf

input datafile fno=00004 name=/free/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: starting piece 1 at 07-DEC-06                                                                                                                                                                                                                                                                                        channel ORA_DISK_1: finished piece 1 at 07-DEC-06

piece handle=/free/oracle/FULL_ORCL_20061207_57 tag=TAG20061207T193507 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:03:55

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including standby control file in backupset

channel ORA_DISK_1: starting piece 1 at 07-DEC-06

channel ORA_DISK_1: finished piece 1 at 07-DEC-06

piece handle=/free/oracle/FULL_ORCL_20061207_58 tag=TAG20061207T193507 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 07-DEC-06

Starting backup at 07-DEC-06

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=320 recid=443 stamp=608585946

channel ORA_DISK_1: starting piece 1 at 07-DEC-06

channel ORA_DISK_1: finished piece 1 at 07-DEC-06

piece handle=/free/oracle/arch_ORCL_20061207_59 tag=TAG20061207T193906 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 07-DEC-06

Starting Control File Autobackup at 07-DEC-06

piece handle=/free/oracle/orabak/c-1132959697-20061207-02 comment=NONE

Finished Control File Autobackup at 07-DEC-06

RMAN>

 

8：复制 rman全备份到备库

利用操作系统命令scp，或者ftp bin方式复制rman全备份到备库。

由于我rman主库备份时设置备份文件位置为/free/oracle，因此必须把此备份文件也放到备库同样的位置。

同时scp 控制文件standby.ctl到备库相应的位置。

 

9.启动备库到nomount

SQL> startup nomount

ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

 

10.利用rman恢复备库

oracle@linux:/free/oracle/oradata/orcl>

rman target sys/cicro@primary  auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on 星期五 12月 8 12:09:23 2006

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

connected to target database: ORCL (DBID=1132959697)

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby dorecover nofilenamecheck;

Starting Duplicate Db at 08-12月-06

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:

{

   set until scn  1607552;

   restore clone standby controlfile;

   sql clone 'alter database mount standby database';

}

executing Memory Script

executing command: SET until clause

Starting restore at 08-12月-06

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /free/oracle/FULL_ORCL_20061207_58

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/free/oracle/FULL_ORCL_20061207_58 tag=TAG20061207T193507

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

output filename=/free/oracle/oradata/orcl/standby.ctl

Finished restore at 08-12月-06

sql statement: alter database mount standby database

                  released channel: ORA_AUX_DISK_1

contents of Memory Script:

{

   set until scn  1607552;

   set newname for tempfile  1 to 

 "/free/oracle/oradata/orcl/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to 

 "/free/oracle/oradata/orcl/system01.dbf";

   set newname for datafile  2 to 

 "/free/oracle/oradata/orcl/undotbs01.dbf";

   set newname for datafile  3 to 

 "/free/oracle/oradata/orcl/sysaux01.dbf";

   set newname for datafile  4 to 

 "/free/oracle/oradata/orcl/users01.dbf";

   set newname for datafile  5 to 

 "/free/oracle/oradata/orcl/gaojfdb.dbf";

   set newname for datafile  6 to 

 "/free/oracle/oradata/orcl/gaojf1db.dbf";

   set newname for datafile  7 to 

 "/free/oracle/oradata/orcl/gaojf2db.dbf";

   set newname for datafile  8 to 

 "/free/oracle/oradata/orcl/gaojf3db.dbf";

   set newname for datafile  9 to 

 "/free/oracle/oradata/orcl/gaojf4db.dbf";

   set newname for datafile  10 to 

 "/free/oracle/oradata/orcl/cicrodb1.dbf";

   set newname for datafile  11 to 

 "/free/oracle/oradata/orcl/cicrodb11.dbf";

   restore

   check readonly

   clone database

   ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to /free/oracle/oradata/orcl/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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 08-12月-06

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /free/oracle/oradata/orcl/system01.dbf

restoring datafile 00002 to /free/oracle/oradata/orcl/undotbs01.dbf

restoring datafile 00003 to /free/oracle/oradata/orcl/sysaux01.dbf

restoring datafile 00004 to /free/oracle/oradata/orcl/users01.dbf

restoring datafile 00005 to /free/oracle/oradata/orcl/gaojfdb.dbf

restoring datafile 00006 to /free/oracle/oradata/orcl/gaojf1db.dbf

restoring datafile 00007 to /free/oracle/oradata/orcl/gaojf2db.dbf

restoring datafile 00008 to /free/oracle/oradata/orcl/gaojf3db.dbf

restoring datafile 00009 to /free/oracle/oradata/orcl/gaojf4db.dbf

restoring datafile 00010 to /free/oracle/oradata/orcl/cicrodb1.dbf

restoring datafile 00011 to /free/oracle/oradata/orcl/cicrodb11.dbf

channel ORA_AUX_DISK_1: reading from backup piece /free/oracle/FULL_ORCL_20061207_57

                                                                                                                                                                                                                                                                             channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/free/oracle/FULL_ORCL_20061207_57 tag=TAG20061207T193507

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:07

Finished restore at 08-12月-06

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=17 stamp=608645655 filename=/free/oracle/oradata/orcl/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=18 stamp=608645655 filename=/free/oracle/oradata/orcl/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=19 stamp=608645655 filename=/free/oracle/oradata/orcl/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=20 stamp=608645655 filename=/free/oracle/oradata/orcl/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=21 stamp=608645655 filename=/free/oracle/oradata/orcl/gaojfdb.dbf

datafile 6 switched to datafile copy

input datafile copy recid=22 stamp=608645655 filename=/free/oracle/oradata/orcl/gaojf1db.dbf

datafile 7 switched to datafile copy

input datafile copy recid=23 stamp=608645655 filename=/free/oracle/oradata/orcl/gaojf2db.dbf

datafile 8 switched to datafile copy

input datafile copy recid=24 stamp=608645655 filename=/free/oracle/oradata/orcl/gaojf3db.dbf

datafile 9 switched to datafile copy

input datafile copy recid=25 stamp=608645655 filename=/free/oracle/oradata/orcl/gaojf4db.dbf

datafile 10 switched to datafile copy

input datafile copy recid=26 stamp=608645656 filename=/free/oracle/oradata/orcl/cicrodb1.dbf

datafile 11 switched to datafile copy

input datafile copy recid=27 stamp=608645656 filename=/free/oracle/oradata/orcl/cicrodb11.dbf

contents of Memory Script:

{

   set until scn  1607552;

   recover

   standby

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 08-12月-06

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=320

channel ORA_AUX_DISK_1: reading from backup piece /free/oracle/arch_ORCL_20061207_59

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/free/oracle/arch_ORCL_20061207_59 tag=TAG20061207T193906

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02

archive log filename=/free/oracle/orabak/1_320_605985194.dbf thread=1 sequence=320

channel clone_default: deleting archive log(s)

archive log filename=/free/oracle/orabak/1_320_605985194.dbf recid=1 stamp=608645661

media recovery complete, elapsed time: 00:00:03

Finished recover at 08-12月-06

 

11.将备库置于自动恢复状态

SQL> alter database recover managed standby database disconnect from session;

Database altered.

12.在主库上执行检查

SQL> conn /as sysdba

Connected.

SQL> select dest_name,status,error from v$archive_dest;

DEST_NAME                      STATUS    ERROR

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

LOG_ARCHIVE_DEST_1             VALID

LOG_ARCHIVE_DEST_2             VALID

LOG_ARCHIVE_DEST_3             INACTIVE

LOG_ARCHIVE_DEST_4             INACTIVE

LOG_ARCHIVE_DEST_5             INACTIVE

LOG_ARCHIVE_DEST_6             INACTIVE

LOG_ARCHIVE_DEST_7             INACTIVE

LOG_ARCHIVE_DEST_8             INACTIVE

LOG_ARCHIVE_DEST_9             INACTIVE

LOG_ARCHIVE_DEST_10            INACTIVE

10 rows selected.

SQL> select * from v$archive_gap;

no rows selected

SQL> select sequence# from v$log_history;

SEQUENCE#

----------

1

2

...

321

13.在备库上执行检查

SQL> conn / as sysdba

Connected.

SQL> select * from v$archive_gap;

no rows selected

SQL> select sequence# from v$log_history;

SEQUENCE#

----------

1

2

...

321

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIE

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

       320 YES

       321 YES

SQL> select process,status from v$managed_standby;

PROCESS            STATUS

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

ARCH               CLOSING

ARCH               CLOSING

RFS                IDLE

MRP0               WAIT_FOR_LOG

至此，备库创建成功。

可以看到，使用rman的duplicate来创建备库，过程简洁了不少，无需在手动创建备库控制文件。

本专题相关链接：

Oracle dataGuard专题：利用冷备创建standby：http://ixdba.blog.51cto.com/2895551/641184

Oracle dataGuard专题：利用rman创建standby：http://ixdba.blog.51cto.com/2895551/642906

©著作权归作者所有：来自51CTO博客作者南非蚂蚁的原创作品，如需转载，请注明出处，否则将追究法律责任

oracle职场休闲数据库技术


点击查看更多内容
发表于 2019.02.17 15:01， 共 2484 人浏览

本文原创发布于慕课网 ，转载请注明出处，谢谢合作

举报
为 TA 点赞

若觉得本文不错，就分享一下吧！

评论

作者其他优质文章

正在加载中

PIPIONE

手记
篇
粉丝
147
获赞与收藏
702

关注作者，订阅最新文章

阅读免费教程

  • 推荐
  • 评论
  • 收藏
  • 共同学习，写下你的评论
感谢您的支持，我会继续努力的～
扫码打赏，你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫，即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消