ITPub博客

首页 > 数据库 > Oracle > 主库不停用DUPLICATE做物理dg

主库不停用DUPLICATE做物理dg

原创 Oracle 作者:bogengwang 时间:2015-11-30 18:09:04 0 删除 编辑
在官方文档中,在rman的部分,有关于用DUPLICATE命令做dg的方法。
部分文档,摘取出来了,如下:

Purpose of Database Duplication

A duplicate database is useful for a variety of purposes, most of which involve testing. You can perform the following tasks in a duplicate database:

  • Test backup and recovery procedures

  • Test an upgrade to a new release of Oracle Database

  • Test the effect of applications on database performance

  • Create a standby database

  • Generate reports

For example, you can duplicate the production database on host1 to host2, and then use the duplicate database on host2 to practice restoring and recovering this database while the production database on host1 operates as usual.

自己在虚拟机上试了一下。

环境介绍

操作系统:

点击(此处)折叠或打开

  1. [root@wbg2 ~]# lsb_release -a
  2. LSB Version: :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
  3. Distributor ID: RedHatEnterpriseServer
  4. Description: Red Hat Enterprise Linux Server release 6.6 (Santiago)
  5. Release: 6.6
  6. Codename: Santiago
hosts文件

点击(此处)折叠或打开

  1. [root@wbg2 ~]# cat /etc/hosts
  2. 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
  3. ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
  4. 1.1.1.1 wbg1
  5. 1.1.1.2 wbg2


操作过程

在主备库,要写好tnsnames.ora

点击(此处)折叠或打开

  1. primary=
  2. (DESCRIPTION=
  3.  (ADDRESS=(PROTOCOL=tcp)(HOST=wbg1)(PORT=1521))
  4.  (CONNECT_DATA=(SERVICE_NAME=wbg)))
  5. standby=
  6. (DESCRIPTION=
  7.  (ADDRESS=(PROTOCOL=tcp)(HOST=wbg2)(PORT=1521))
  8.  (CONNECT_DATA=(SERVICE_NAME=standby)))
备库要配置一个静态监听并启动,因为备库在搭建过程中,要重启几次
备库的口令文件,要从主库拷贝
备库要写一个pfile,只要指定一个db_name就可以了

点击(此处)折叠或打开

  1. LISTENER=
  2. (DESCRIPTION=
  3. (ADDRESS_LIST=
  4. (ADDRESS=(PROTOCOL=tcp)(HOST=wbg2)(PORT=1521))
  5. (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
  6. SID_LIST_LISTENER=
  7. (SID_LIST=
  8. (SID_DESC=
  9. (GLOBAL_DBNAME=standby)
  10. (ORACLE_HOME=/u01/oracle)
  11. (SID_NAME=standby))
  12. )
主库执行如下命令,修改相关配置:

点击(此处)折叠或打开

  1. alter system set log_archive_config='DG_CONFIG=(wbg,standby)';
  2. alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch/wbg/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wbg';
  3. alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
  4. alter system set FAL_SERVER=standby;
  5. alter system set STANDBY_FILE_MANAGEMENT=AUTO;
  6. alter system set DB_FILE_NAME_CONVERT='/u01/data/standby/','/u01/data/wbg/' scope=spfile;
  7. alter system set LOG_FILE_NAME_CONVERT='/u01/data/standby/','/u01/data/wbg/' scope=spfile;
  8. ALTER DATABASE ADD STANDBY LOGFILE '/u01/data/wbg/std01.log' size 50m;
  9. ALTER DATABASE ADD STANDBY LOGFILE '/u01/data/wbg/std02.log' size 50m;
  10. ALTER DATABASE ADD STANDBY LOGFILE '/u01/data/wbg/std03.log' size 50m;
  11. ALTER DATABASE ADD STANDBY LOGFILE '/u01/data/wbg/std04.log' size 50m;
主库登录自己的rman,同时连接备库(辅助数据库)

点击(此处)折叠或打开

  1. rman target / auxiliary sys/oracle@1.1.1.2:1521/standby
主库在rman中,执行DUPLICATE语句

点击(此处)折叠或打开

  1. DUPLICATE TARGET DATABASE
  2. FOR STANDBY
  3. FROM ACTIVE DATABASE
  4. DORECOVER
  5. SPFILE
  6. SET db_unique_name 'standby'
  7. SET control_files '/u01/data/standby/ctl1.ctl'
  8. SET db_file_name_convert '/u01/data/wbg/','/u01/data/standby/'
  9. SET fal_server 'primary'
  10. SET log_archive_config 'DG_CONFIG=(wbg,standby)'
  11. SET log_archive_dest_1 'LOCATION=/u01/arch/standby/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
  12. SET log_archive_dest_2 'SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
  13. SET log_file_name_convert '/u01/data/wbg/','/u01/data/standby/'
  14. SET remote_login_passwordfile 'EXCLUSIVE'
  15. SET standby_file_management 'AUTO'
  16. ;
至此搭建完毕。在主库切换日志,可以看见备库收到了日志

点击(此处)折叠或打开

  1. Archived Log entry 5 added for thread 1 sequence 14 ID 0xcbe1a451 dest 1:
  2. RFS[1]: Selected log 5 for thread 1 sequence 15 dbid -874449071 branch 887556119
  3. Mon Nov 30 17:10:00 2015
  4. Archived Log entry 6 added for thread 1 sequence 15 ID 0xcbe1a451 dest 1:
  5. RFS[1]: Selected log 4 for thread 1 sequence 16 dbid -874449071 branch 887556119
  6. Mon Nov 30 17:10:02 2015
  7. Archived Log entry 7 added for thread 1 sequence 16 ID 0xcbe1a451 dest 1:
  8. RFS[1]: Selected log 4 for thread 1 sequence 18 dbid -874449071 branch 887556119
  9. Mon Nov 30 17:10:03 2015
  10. RFS[3]: Assigned to RFS process 3638
  11. RFS[3]: Selected log 5 for thread 1 sequence 17 dbid -874449071 branch 887556119
  12. Archived Log entry 8 added for thread 1 sequence 17 ID 0xcbe1a451 dest 1:
  13. Archived Log entry 9 added for thread 1 sequence 18 ID 0xcbe1a451 dest 1:
  14. RFS[1]: Selected log 4 for thread 1 sequence 19 dbid -874449071 branch 887556119
  15. Archived Log entry 10 added for thread 1 sequence 19 ID 0xcbe1a451 dest 1:
  16. RFS[1]: Selected log 4 for thread 1 sequence 20 dbid -874449071 branch 887556119
  17. Archived Log entry 11 added for thread 1 sequence 20 ID 0xcbe1a451 dest 1:
  18. RFS[1]: Selected log 4 for thread 1 sequence 21 dbid -874449071 branch 887556119
  19. Archived Log entry 12 added for thread 1 sequence 21 ID 0xcbe1a451 dest 1:
  20. RFS[1]: Selected log 4 for thread 1 sequence 22 dbid -874449071 branch 887556119
  21. Archived Log entry 13 added for thread 1 sequence 22 ID 0xcbe1a451 dest 1:
  22. RFS[1]: Selected log 4 for thread 1 sequence 23 dbid -874449071 branch 887556119
  23. Archived Log entry 14 added for thread 1 sequence 23 ID 0xcbe1a451 dest 1:
  24. RFS[1]: Selected log 4 for thread 1 sequence 24 dbid -874449071 branch 887556119
  25. Archived Log entry 15 added for thread 1 sequence 24 ID 0xcbe1a451 dest 1:
  26. RFS[1]: Selected log 4 for thread 1 sequence 25 dbid -874449071 branch 887556119

DUPLICATE的过程

看看命令执行过程中,数据库的反馈,可以了解一下,这个命令背后都干了什么

点击(此处)折叠或打开

  1. [oracle@wbg1 admin]$ rman target / auxiliary sys/oracle@1.1.1.2:1521/standby
  2. Recovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 30 16:46:41 2015
  3. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
  4. connected to target database: WBG (DBID=3420518225)
  5. connected to auxiliary database: WBG (not mounted)
  6. RMAN> DUPLICATE TARGET DATABASE
  7. 2> FOR STANDBY
  8. 3> FROM ACTIVE DATABASE
  9. 4> DORECOVER
  10. 5> SPFILE
  11. 6> SET db_unique_name 'standby'
  12. 7> SET control_files '/u01/data/standby/ctl1.ctl'
  13. 8> SET db_file_name_convert '/u01/data/wbg/','/u01/data/standby/'
  14. 9> SET fal_server 'primary'
  15. 10> SET log_archive_config 'DG_CONFIG=(wbg,standby)'
  16. 11> SET log_archive_dest_1 'LOCATION=/u01/arch/standby/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
  17. 12> SET log_archive_dest_2 'SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
  18. 13> SET log_file_name_convert '/u01/data/wbg/','/u01/data/standby/'
  19. 14> SET remote_login_passwordfile 'EXCLUSIVE'
  20. 15> SET standby_file_management 'AUTO'
  21. 16> ;
  22. Starting Duplicate Db at 30-NOV-15
  23. using target database control file instead of recovery catalog
  24. allocated channel: ORA_AUX_DISK_1
  25. channel ORA_AUX_DISK_1: SID=20 device type=DISK
  26. contents of Memory Script:
  27. {
  28. backup as copy reuse
  29. targetfile '/u01/oracle/dbs/orapwwbg' auxiliary format
  30. '/u01/oracle/dbs/orapwstandby' targetfile
  31. '/u01/oracle/dbs/spfilewbg.ora' auxiliary format
  32. '/u01/oracle/dbs/spfilestandby.ora' ;
  33. sql clone "alter system set spfile= ''/u01/oracle/dbs/spfilestandby.ora''";
  34. }
  35. executing Memory Script
  36. Starting backup at 30-NOV-15
  37. allocated channel: ORA_DISK_1
  38. channel ORA_DISK_1: SID=40 device type=DISK
  39. Finished backup at 30-NOV-15
  40. sql statement: alter system set spfile= ''/u01/oracle/dbs/spfilestandby.ora''
  41. contents of Memory Script:
  42. {
  43. sql clone "alter system set db_unique_name =
  44. ''standby'' comment=
  45. '''' scope=spfile";
  46. sql clone "alter system set control_files =
  47. ''/u01/data/standby/ctl1.ctl'' comment=
  48. '''' scope=spfile";
  49. sql clone "alter system set db_file_name_convert =
  50. ''/u01/data/wbg/'', ''/u01/data/standby/'' comment=
  51. '''' scope=spfile";
  52. sql clone "alter system set fal_server =
  53. ''primary'' comment=
  54. '''' scope=spfile";
  55. sql clone "alter system set log_archive_config =
  56. ''DG_CONFIG=(wbg,standby)'' comment=
  57. '''' scope=spfile";
  58. sql clone "alter system set log_archive_dest_1 =
  59. ''LOCATION=/u01/arch/standby/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'' comment=
  60. '''' scope=spfile";
  61. sql clone "alter system set log_archive_dest_2 =
  62. ''SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'' comment=
  63. '''' scope=spfile";
  64. sql clone "alter system set log_file_name_convert =
  65. ''/u01/data/wbg/'', ''/u01/data/standby/'' comment=
  66. '''' scope=spfile";
  67. sql clone "alter system set remote_login_passwordfile =
  68. ''EXCLUSIVE'' comment=
  69. '''' scope=spfile";
  70. sql clone "alter system set standby_file_management =
  71. ''AUTO'' comment=
  72. '''' scope=spfile";
  73. shutdown clone immediate;
  74. startup clone nomount;
  75. }
  76. executing Memory Script
  77. sql statement: alter system set db_unique_name = ''standby'' comment= '''' scope=spfile
  78. sql statement: alter system set control_files = ''/u01/data/standby/ctl1.ctl'' comment= '''' scope=spfile
  79. sql statement: alter system set db_file_name_convert = ''/u01/data/wbg/'', ''/u01/data/standby/'' comment= '''' scope=spfile
  80. sql statement: alter system set fal_server = ''primary'' comment= '''' scope=spfile
  81. sql statement: alter system set log_archive_config = ''DG_CONFIG=(wbg,standby)'' comment= '''' scope=spfile
  82. sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u01/arch/standby/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'' comment= '''' scope=spfile
  83. sql statement: alter system set log_archive_dest_2 = ''SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'' comment= '''' scope=spfile
  84. sql statement: alter system set log_file_name_convert = ''/u01/data/wbg/'', ''/u01/data/standby/'' comment= '''' scope=spfile
  85. sql statement: alter system set remote_login_passwordfile = ''EXCLUSIVE'' comment= '''' scope=spfile
  86. sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
  87. Oracle instance shut down
  88. connected to auxiliary database (not started)
  89. Oracle instance started
  90. Total System Global Area 626327552 bytes
  91. Fixed Size 2255832 bytes
  92. Variable Size 423625768 bytes
  93. Database Buffers 197132288 bytes
  94. Redo Buffers 3313664 bytes
  95. contents of Memory Script:
  96. {
  97. backup as copy current controlfile for standby auxiliary format '/u01/data/standby/ctl1.ctl';
  98. }
  99. executing Memory Script
  100. Starting backup at 30-NOV-15
  101. using channel ORA_DISK_1
  102. channel ORA_DISK_1: starting datafile copy
  103. copying standby control file
  104. output file name=/u01/oracle/dbs/snapcf_wbg.f tag=TAG20151130T164651 RECID=2 STAMP=897151611
  105. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
  106. Finished backup at 30-NOV-15
  107. contents of Memory Script:
  108. {
  109. sql clone 'alter database mount standby database';
  110. }
  111. executing Memory Script
  112. sql statement: alter database mount standby database
  113. contents of Memory Script:
  114. {
  115. set newname for tempfile 1 to
  116. "/u01/data/standby/temp01.dbf";
  117. switch clone tempfile all;
  118. set newname for datafile 1 to
  119. "/u01/data/standby/system01.dbf";
  120. set newname for datafile 2 to
  121. "/u01/data/standby/sysaux01.dbf";
  122. set newname for datafile 3 to
  123. "/u01/data/standby/undotbs01.dbf";
  124. set newname for datafile 4 to
  125. "/u01/data/standby/users01.dbf";
  126. set newname for datafile 5 to
  127. "/u01/data/standby/example01.dbf";
  128. backup as copy reuse
  129. datafile 1 auxiliary format
  130. "/u01/data/standby/system01.dbf" datafile
  131. 2 auxiliary format
  132. "/u01/data/standby/sysaux01.dbf" datafile
  133. 3 auxiliary format
  134. "/u01/data/standby/undotbs01.dbf" datafile
  135. 4 auxiliary format
  136. "/u01/data/standby/users01.dbf" datafile
  137. 5 auxiliary format
  138. "/u01/data/standby/example01.dbf" ;
  139. sql 'alter system archive log current';
  140. }
  141. executing Memory Script
  142. executing command: SET NEWNAME
  143. renamed tempfile 1 to /u01/data/standby/temp01.dbf in control file
  144. executing command: SET NEWNAME
  145. executing command: SET NEWNAME
  146. executing command: SET NEWNAME
  147. executing command: SET NEWNAME
  148. executing command: SET NEWNAME
  149. Starting backup at 30-NOV-15
  150. using channel ORA_DISK_1
  151. channel ORA_DISK_1: starting datafile copy
  152. input datafile file number=00001 name=/u01/data/wbg/system01.dbf
  153. output file name=/u01/data/standby/system01.dbf tag=TAG20151130T164658
  154. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
  155. channel ORA_DISK_1: starting datafile copy
  156. input datafile file number=00002 name=/u01/data/wbg/sysaux01.dbf
  157. output file name=/u01/data/standby/sysaux01.dbf tag=TAG20151130T164658
  158. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
  159. channel ORA_DISK_1: starting datafile copy
  160. input datafile file number=00005 name=/u01/data/wbg/example01.dbf
  161. output file name=/u01/data/standby/example01.dbf tag=TAG20151130T164658
  162. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
  163. channel ORA_DISK_1: starting datafile copy
  164. input datafile file number=00003 name=/u01/data/wbg/undotbs01.dbf
  165. output file name=/u01/data/standby/undotbs01.dbf tag=TAG20151130T164658
  166. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
  167. channel ORA_DISK_1: starting datafile copy
  168. input datafile file number=00004 name=/u01/data/wbg/users01.dbf
  169. output file name=/u01/data/standby/users01.dbf tag=TAG20151130T164658
  170. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
  171. Finished backup at 30-NOV-15
  172. sql statement: alter system archive log current
  173. contents of Memory Script:
  174. {
  175. backup as copy reuse
  176. archivelog like "/u01/arch/wbg/1_10_887556119.dbf" auxiliary format
  177. "/u01/arch/standby/1_10_887556119.dbf" archivelog like
  178. "/u01/arch/wbg/1_11_887556119.dbf" auxiliary format
  179. "/u01/arch/standby/1_11_887556119.dbf" ;
  180. catalog clone archivelog "/u01/arch/standby/1_10_887556119.dbf";
  181. catalog clone archivelog "/u01/arch/standby/1_11_887556119.dbf";
  182. switch clone datafile all;
  183. }
  184. executing Memory Script
  185. Starting backup at 30-NOV-15
  186. using channel ORA_DISK_1
  187. channel ORA_DISK_1: starting archived log copy
  188. input archived log thread=1 sequence=10 RECID=5 STAMP=897151623
  189. output file name=/u01/arch/standby/1_10_887556119.dbf RECID=0 STAMP=0
  190. channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
  191. channel ORA_DISK_1: starting archived log copy
  192. input archived log thread=1 sequence=11 RECID=6 STAMP=897151688
  193. output file name=/u01/arch/standby/1_11_887556119.dbf RECID=0 STAMP=0
  194. channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
  195. Finished backup at 30-NOV-15
  196. cataloged archived log
  197. archived log file name=/u01/arch/standby/1_10_887556119.dbf RECID=1 STAMP=897151692
  198. cataloged archived log
  199. archived log file name=/u01/arch/standby/1_11_887556119.dbf RECID=2 STAMP=897151692
  200. datafile 1 switched to datafile copy
  201. input datafile copy RECID=2 STAMP=897151692 file name=/u01/data/standby/system01.dbf
  202. datafile 2 switched to datafile copy
  203. input datafile copy RECID=3 STAMP=897151692 file name=/u01/data/standby/sysaux01.dbf
  204. datafile 3 switched to datafile copy
  205. input datafile copy RECID=4 STAMP=897151692 file name=/u01/data/standby/undotbs01.dbf
  206. datafile 4 switched to datafile copy
  207. input datafile copy RECID=5 STAMP=897151692 file name=/u01/data/standby/users01.dbf
  208. datafile 5 switched to datafile copy
  209. input datafile copy RECID=6 STAMP=897151692 file name=/u01/data/standby/example01.dbf
  210. contents of Memory Script:
  211. {
  212. set until scn 1021784;
  213. recover
  214. standby
  215. clone database
  216. delete archivelog
  217. ;
  218. }
  219. executing Memory Script
  220. executing command: SET until clause
  221. Starting recover at 30-NOV-15
  222. allocated channel: ORA_AUX_DISK_1
  223. channel ORA_AUX_DISK_1: SID=18 device type=DISK
  224. starting media recovery
  225. archived log for thread 1 with sequence 10 is already on disk as file /u01/arch/standby/1_10_887556119.dbf
  226. archived log for thread 1 with sequence 11 is already on disk as file /u01/arch/standby/1_11_887556119.dbf
  227. archived log file name=/u01/arch/standby/1_10_887556119.dbf thread=1 sequence=10
  228. archived log file name=/u01/arch/standby/1_11_887556119.dbf thread=1 sequence=11
  229. media recovery complete, elapsed time: 00:00:00
  230. Finished recover at 30-NOV-15
  231. Finished Duplicate Db at 30-NOV-15
  232. RMAN>

这种方法,创建过程比较炫酷,但是中间不能有错误,要一气呵成。
娱乐一下还可以,少用。

额。。。赓赓。。。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30202921/viewspace-1849928/,如需转载,请注明出处,否则将追究法律责任。

下一篇: kdb的prosync配置
请登录后发表评论 登录
全部评论

注册时间:2015-04-11

  • 博文量
    21
  • 访问量
    58505