Oracle 11gR2 使用RMAN Duplicate复制数据库 

作者:吴伟龙 PrudentWoo

   前言:

    上周刚做完一个项目,用户要求RAC的数据库可以自己主动备份到另外一个单节点上,单节点可以正常拿起来就能用。当时跟他们讨论的是用ADG来做,但通过描写叙述后。用户认为假设要人工干预的话太麻烦。它不想做不论什么的人工干预,实现数据自己主动到这台单机上来。那仅仅是一台备用的数据库。没事的时候可以登录上去看看历史的数据情况。

       这个时候我提出了11g的RMAN Duplicate方案,它可以在线实现异机的在线备份。且无需人工干预,通过网络将传输数据过来后可以自己主动open备机.

如图:

Oracle 11gR2 使用RMAN Duplicate复制数据库_hive


     RMAN Duplicate 是从Oracle 10g就已经開始有的一个功能;可是在11g的时候进行了非常多增强,免去了10g版本号下须要人工拷贝到备机上过来进行恢复的尴尬境界。通过11g的Duplicate可以创建一个全然同样但DBID不同的备机数据库。在11g的RMAN Duplicate中可以通过Active database duplicate和Backup-based duplicate两种方法实现。我们在用户现场採用的是Active Database Duplicate;由于它不用先把目标数据库进行RMAN备份。仅仅须要目标数据库处于归档模式下就可以通过网络对数据库进行恢复。且恢复完毕后可以自己主动Open Duplicate Database。这样就正好满足了用户不用人工干预的需求。尤其是对于大数据库特别是TB级别的数据库其长处时非常明显,恢复前不须要进行单独的备份。降低了备份及拷贝备份文件的时间,同一时候还节省了备份空间。以下我们来进行完整的RAC to Single Instance的Active Database Duplicate操作。

一、环境介绍

Oracle 11gR2 使用RMAN Duplicate复制数据库_oracle_02


HostName

DBName

public ip


11gR2

RAC


db01

woo1

192.168.7.51

db02

woo2

192.168.7.52

Duplicate

standby

woo


192.168.7.55




二、開始配置Duplicate Database。过程例如以下:
2.1 构建辅助数据库文件夹结构,配置辅助数据库相关系统參数。

2.2 安装软件并创建数据库。

2.3 开启归档
2.3 创建pfile,并改动 2.4 创建辅助数据库实例口令文件
2.5 配置静态监听
2.6 用pfile文件,将辅助数据库启动到nomount状态
2.7 运行RMAN duplicate命令复制数据库
2.8 创建spfile文件


xxxxxxxxxx
 
1
三、构建辅助数据库文件夹结构,配置辅助数据库相关參数。(简)/usr/sbin/groupadd -g 502 dba
2
/usr/sbin/groupadd -g 501 oinstall
3
/usr/sbin/useradd -u 502 -g oinstall -G dba oracle
4
mkdir -p /DBSoft/oraInventory
5
mkdir -p /DBSoft/oracle/product/11.2.4/dbhome_1
6
chown -R oracle:oinstall /DBSoft
7
chmod -R 775 /DBSoft
8
echo "oracle"|passwd --stdin oracle
9
yum install -y setarch binutils compat-libstdc++-33 compat-libcap1 compat-db compat-libstdc++ compat-gcc elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc glibc-common glibc
10
-devel libaio libaio-devel libgcc libstdc++ libstdc++-devel make ksh numactl sysstat libXp unixODBC unixODBC-devel udev
11
cat >> /etc/sysctl.conf <<EOF
12
kernel.shmall = 4294967296
13
kernel.sem = 510 65280 510 128
14
kernel.shmmni = 4096
15
kernel.shmmax = 137438953472
16
net.ipv4.ip_local_port_range = 9000 65500
17
net.core.rmem_default = 1048576
18
net.core.rmem_max = 4194304
19
net.core.wmem_default = 262144
20
net.core.wmem_max = 1048576
21
fs.file-max = 6815744
22
fs.aio-max-nr = 1048576
23
vm.swappiness = 0
24
vm.dirty_background_ratio = 3
25
vm.dirty_ratio = 80
26
vm.dirty_expire_centisecs = 500
27
vm.dirty_writeback_centisecs = 100
28
net.ipv4.tcp_sack = 0
29
net.ipv4.tcp_timestamps = 0
30
net.ipv4.conf.default.rp_filter = 0
31
net.ipv4.tcp_wmem = 262144
32
net.ipv4.tcp_rmem = 4194304
33
EOF
34
35
36
/sbin/sysctl –p
37
cat >> /etc/security/limits.conf <<EOF
38
oracle          soft    nproc           2047
39
oracle          hard    nproc           16384
40
oracle          soft    nofile          65536
41
oracle          hard    nofile          65536
42
oracle          soft    memlock         3145728
43
oracle          hard    memlock         3145728
44
45
46
#signaturelevin
47
EOF
48
cat >> /etc/profile <<EOF
49
50
51
if [ \$USER = "oracle" ]; then
52
    if [ \$SHELL = "/bin/ksh" ]; then
53
                ulimit -p 16384
54
                ulimit -n 65536
55
        else
56
                ulimit -u 16384 -n 65536
57
        fi
58
fi
59
EOF
60
61
62
cat >> /home/oracle/.bash_profile <<EOF
63
export TMP=/tmp
64
export TMPDIR=\$TMP
65
export EDIT=vi
66
alias  vi=vim
67
export ORACLE_HOSTNAME=$hostname
68
export ORACLE_UNQNAME=$db_name
69
export ORACLE_BASE=/DBSoft/oracle
70
export ORACLE_HOME=\$ORACLE_BASE/product/11.2.4/dbhome_1
71
export ORACLE_SID=$instance_name
72
export ORACLE_TERM=xterm
73
export PATH=/usr/sbin:\$PATH
74
export PATH=\$ORACLE_HOME/bin:\$PATH
75
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
76
export CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib
77
export NLS_LANG=AMERICAN_AMERICA AL32UTF16
78
umask 022
79
EOF
80
81
四、安装软件并创建数据库./runInstaller -silent -debug -force -responseFile /home/oracle/database/response/db_install.rsp
82
83
dbca -silent -responsefile /home/oracle/database/response/dbca.rsp
84
85
五、开启归档SQL> alter system set log_archive_dest_1=\'location=+FRA001/archive\' scope=spfile sid=\'woo1\';
86
87
System altered.
88
SQL> alter database archivelog;
89
90
Database altered.
91
SQL> alter database open;
92
93
Database altered.
94
95
SQL> archive log list;
96
Database log mode Archive Mode
97
Automatic archival Enabled
98
Archive destination +FRA001/archive
99
Oldest online log sequence 978
100
Next log sequence to archive 979
101
Current log sequence 979
102
SQL> alter system switch logfile;
103
alter system switch logfile;
104
105
System altered.
106
107
SQL> 
108
109
System altered.
110
SQL> archive log list;
111
Database log mode Archive Mode
112
Automatic archival Enabled
113
Archive destination +FRA001/archive
114
Oldest online log sequence 980
115
Next log sequence to archive 981
116
Current log sequence 981
117
118
六、配置静态监听   RAC节点监听信息例如以下:$ cd $ORACLE_HOME/network/admin
119
LISTENER =
120
  (DESCRIPTION_LIST =
121
    (DESCRIPTION =
122
      (ADDRESS = (PROTOCOL = TCP)(HOST = scan.woo.com)(PORT = 1521))
123
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
124
    )
125
  )
126
 
127
SID_LIST_LISTENER =
128
  (SID_LIST =
129
     (SID_DESC =
130
        (GLOBAL_DBNAME = woo.com)
131
        (ORACLE_HOME = /DBSoft/oracle/product/11.2.0/db_1)
132
        (SID_NAME = woo)
133
      )
134
  )
135
ADR_BASE_LISTENER = /DBSoft/oracle
136
137
单节点监听信息例如以下:LISTENER =
138
  (DESCRIPTION_LIST =
139
    (DESCRIPTION =
140
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.55)(PORT = 1521))
141
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
142
    )
143
  )
144
 
145
SID_LIST_LISTENER =
146
  (SID_LIST =
147
     (SID_DESC =
148
        (GLOBAL_DBNAME = woo.com)
149
        (ORACLE_HOME = /DBSoft/oracle/product/11.2.0/db_1)
150
        (SID_NAME = woo)
151
      )
152
  )
153
ADR_BASE_LISTENER = /DBSoft/oracle
154
155
配置全部节点tnsname信息例如以下:WOO =
156
  (DESCRIPTION =
157
    (ADDRESS_LIST =
158
      (ADDRESS = (PROTOCOL = TCP)(HOST = scan.woo.com)(PORT = 1521))
159
    )
160
    (CONNECT_DATA =
161
      (SERVICE_NAME = woo)
162
    )
163
  )
164
 
165
DUPLICATE =
166
  (DESCRIPTION =
167
    (ADDRESS_LIST =
168
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.55)(PORT = 1521))
169
    )
170
    (CONNECT_DATA =
171
      (SERVER = DEDICATED)
172
      (SERVICE_NAME = woo)
173
    )
174
  )
175
176
六、启动数据库到nomount状态
177
SQL> startup nomount
178
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
179
ORACLE instance started.
180
181
Total System Global Area 954155008 bytes
182
Fixed Size 1368632 bytes
183
Variable Size 306187720 bytes
184
Database Buffers 641728512 bytes
185
Redo Buffers 4870144 bytes
186
SQL>exit                           ---------必须退出
187
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
188
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
189
Oracle Database Vault and Real Application Testing options
190
191
七、 在RAC端随意节点通过RMAN连接到目标实例和辅助实例。运行duplicate命令复制数据库:[oracle@db01 ~]$ rman target sys/oracle@woo auxiliary sys/oracle@standby
192
193
194
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 3 19:39:00 2015
195
196
197
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
198
199
200
connected to target database: WOO (DBID=4218366793)
201
connected to auxiliary database: WOO (not mounted)
202
203
204
RMAN>
205
RMAN> duplicate target database to woo from active database nofilenamecheck;
206
207
208
Starting Duplicate Db at 03-JUN-15
209
using target database control file instead of recovery catalog
210
allocated channel: ORA_AUX_DISK_1
211
channel ORA_AUX_DISK_1: SID=19 device type=DISK
212
213
214
contents of Memory Script:
215
{
216
   sql clone "alter system set  db_name = 
217
 ''WOO'' comment=
218
 ''Modified by RMAN duplicate'' scope=spfile";
219
   sql clone "alter system set  db_unique_name = 
220
 ''WOO'' comment=
221
 ''Modified by RMAN duplicate'' scope=spfile";
222
   shutdown clone immediate;
223
   startup clone force nomount
224
   backup as copy current controlfile auxiliary format  '/DBSoft/oracle/oradata/woo/control01.ctl';
225
   restore clone controlfile to  '/DBSoft/oracle/fast_recovery_area/woo/control02.ctl' from 
226
 '/DBSoft/oracle/oradata/woo/control01.ctl';
227
   alter clone database mount;
228
}
229
executing Memory Script
230
sql statement: alter system set  db_name =  ''WOO'' comment= ''Modified by RMAN duplicate'' scope=spfile
231
232
233
sql statement: alter system set  db_unique_name =  ''WOO'' comment= ''Modified by RMAN duplicate'' scope=spfile
234
235
236
Oracle instance shut down
237
238
239
Oracle instance started
240
241
242
Total System Global Area     954155008 bytes
243
244
245
Fixed Size                     1368632 bytes
246
Variable Size                306187720 bytes
247
Database Buffers             641728512 bytes
248
Redo Buffers                   4870144 bytes
249
250
251
Starting backup at 03-JUN-15
252
allocated channel: ORA_DISK_1
253
channel ORA_DISK_1: SID=43 device type=DISK
254
channel ORA_DISK_1: starting datafile copy
255
copying current control file
256
output file name=/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/snapcf_woo.f tag=TAG20150603T194000 RECID=3 STAMP=881437202
257
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
258
Finished backup at 03-JUN-15
259
260
261
Starting restore at 03-JUN-15
262
allocated channel: ORA_AUX_DISK_1
263
channel ORA_AUX_DISK_1: SID=19 device type=DISK
264
265
266
channel ORA_AUX_DISK_1: copied control file copy
267
Finished restore at 03-JUN-15
268
269
270
database mounted
271
272
273
contents of Memory Script:
274
{
275
   sql clone 'alter database flashback off';
276
   set newname for datafile  1 to 
277
 "/DBSoft/oracle/oradata/woo/system01.dbf";
278
   set newname for datafile  2 to 
279
 "/DBSoft/oracle/oradata/woo/sysaux01.dbf";
280
   set newname for datafile  3 to 
281
 "/DBSoft/oracle/oradata/woo/undotbs01.dbf";
282
   set newname for datafile  4 to 
283
 "/DBSoft/oracle/oradata/woo/users01.dbf";
284
   backup as copy reuse
285
   datafile  1 auxiliary format 
286
 "/DBSoft/oracle/oradata/woo/system01.dbf"   datafile 
287
 2 auxiliary format 
288
 "/DBSoft/oracle/oradata/woo/sysaux01.dbf"   datafile 
289
 3 auxiliary format 
290
 "/DBSoft/oracle/oradata/woo/undotbs01.dbf"   datafile 
291
 4 auxiliary format 
292
 "/DBSoft/oracle/oradata/woo/users01.dbf"   ;
293
   sql 'alter system archive log current';
294
}
295
executing Memory Script
296
297
298
sql statement: alter database flashback off
299
300
301
executing command: SET NEWNAME
302
303
304
executing command: SET NEWNAME
305
306
307
executing command: SET NEWNAME
308
309
310
executing command: SET NEWNAME
311
Starting backup at 03-JUN-15
312
using channel ORA_DISK_1
313
channel ORA_DISK_1: starting datafile copy
314
input datafile file number=00001 name=/DBSoft/oracle/oradata/woo/system01.dbf
315
output file name=/DBSoft/oracle/oradata/woo/system01.dbf tag=TAG20150603T194019
316
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:47
317
channel ORA_DISK_1: starting datafile copy
318
input datafile file number=00002 name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
319
output file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf tag=TAG20150603T194019
320
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:07
321
channel ORA_DISK_1: starting datafile copy
322
input datafile file number=00003 name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
323
output file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf tag=TAG20150603T194019
324
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
325
channel ORA_DISK_1: starting datafile copy
326
input datafile file number=00004 name=/DBSoft/oracle/oradata/woo/users01.dbf
327
output file name=/DBSoft/oracle/oradata/woo/users01.dbf tag=TAG20150603T194019
328
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
329
Finished backup at 03-JUN-15
330
331
332
sql statement: alter system archive log current
333
334
335
contents of Memory Script:
336
{
337
   backup as copy reuse
338
   archivelog like  "/DBArchive/archive/1_63_878860684.dbf" auxiliary format 
339
 "/DBArchive2/archive/1_63_878860684.dbf"   ;
340
   catalog clone archivelog  "/DBArchive2/archive/1_63_878860684.dbf";
341
   switch clone datafile all;
342
}
343
executing Memory Script
344
345
346
Starting backup at 03-JUN-15
347
using channel ORA_DISK_1
348
channel ORA_DISK_1: starting archived log copy
349
input archived log thread=1 sequence=63 RECID=38 STAMP=881437711
350
output file name=/DBArchive2/archive/1_63_878860684.dbf RECID=0 STAMP=0
351
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
352
Finished backup at 03-JUN-15
353
354
355
cataloged archived log
356
archived log file name=/DBArchive2/archive/1_63_878860684.dbf RECID=38 STAMP=881437696
357
358
359
datafile 1 switched to datafile copy
360
input datafile copy RECID=3 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/system01.dbf
361
datafile 2 switched to datafile copy
362
input datafile copy RECID=4 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
363
datafile 3 switched to datafile copy
364
input datafile copy RECID=5 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
365
datafile 4 switched to datafile copy
366
input datafile copy RECID=6 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/users01.dbf
367
368
369
contents of Memory Script:
370
{
371
   set until scn  1338740;
372
   recover
373
   clone database
374
    delete archivelog
375
   ;
376
}
377
executing Memory Script
378
379
380
executing command: SET until clause
381
382
383
Starting recover at 03-JUN-15
384
using channel ORA_AUX_DISK_1
385
386
387
starting media recovery
388
389
390
archived log for thread 1 with sequence 63 is already on disk as file /DBArchive2/archive/1_63_878860684.dbf
391
archived log file name=/DBArchive2/archive/1_63_878860684.dbf thread=1 sequence=63
392
media recovery complete, elapsed time: 00:00:00
393
Finished recover at 03-JUN-15
394
Oracle instance started
395
396
397
Total System Global Area     954155008 bytes
398
399
400
Fixed Size                     1368632 bytes
401
Variable Size                306187720 bytes
402
Database Buffers             641728512 bytes
403
Redo Buffers                   4870144 bytes
404
405
406
contents of Memory Script:
407
{
408
   sql clone "alter system set  db_name = 
409
 ''WOO'' comment=
410
 ''Reset to original value by RMAN'' scope=spfile";
411
   sql clone "alter system reset  db_unique_name scope=spfile";
412
   shutdown clone immediate;
413
   startup clone nomount;
414
}
415
executing Memory Script
416
417
418
sql statement: alter system set  db_name =  ''WOO'' comment= ''Reset to original value by RMAN'' scope=spfile
419
420
421
sql statement: alter system reset  db_unique_name scope=spfile
422
423
424
Oracle instance shut down
425
426
427
connected to auxiliary database (not started)
428
Oracle instance started
429
430
431
Total System Global Area     954155008 bytes
432
433
434
Fixed Size                     1368632 bytes
435
Variable Size                306187720 bytes
436
Database Buffers             641728512 bytes
437
Redo Buffers                   4870144 bytes
438
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "WOO" RESETLOGS ARCHIVELOG 
439
  MAXLOGFILES     16
440
  MAXLOGMEMBERS      3
441
  MAXDATAFILES      100
442
  MAXINSTANCES     8
443
  MAXLOGHISTORY      292
444
 LOGFILE
445
  GROUP   1 ( '/DBSoft/oracle/oradata/woo/redo01.log' ) SIZE 50 M  REUSE,
446
  GROUP   2 ( '/DBSoft/oracle/oradata/woo/redo02.log' ) SIZE 50 M  REUSE,
447
  GROUP   3 ( '/DBSoft/oracle/oradata/woo/redo03.log' ) SIZE 50 M  REUSE
448
 DATAFILE
449
  '/DBSoft/oracle/oradata/woo/system01.dbf'
450
 CHARACTER SET WE8MSWIN1252
451
452
453
454
455
contents of Memory Script:
456
{
457
   set newname for tempfile  1 to 
458
 "/DBSoft/oracle/oradata/woo/temp01.dbf";
459
   switch clone tempfile all;
460
   catalog clone datafilecopy  "/DBSoft/oracle/oradata/woo/sysaux01.dbf", 
461
 "/DBSoft/oracle/oradata/woo/undotbs01.dbf", 
462
 "/DBSoft/oracle/oradata/woo/users01.dbf";
463
   switch clone datafile all;
464
}
465
executing Memory Script
466
467
468
executing command: SET NEWNAME
469
470
471
renamed tempfile 1 to /DBSoft/oracle/oradata/woo/temp01.dbf in control file
472
473
474
cataloged datafile copy
475
datafile copy file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf RECID=1 STAMP=881437718
476
cataloged datafile copy
477
datafile copy file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf RECID=2 STAMP=881437718
478
cataloged datafile copy
479
datafile copy file name=/DBSoft/oracle/oradata/woo/users01.dbf RECID=3 STAMP=881437718
480
481
482
datafile 2 switched to datafile copy
483
input datafile copy RECID=1 STAMP=881437718 file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
484
datafile 3 switched to datafile copy
485
input datafile copy RECID=2 STAMP=881437718 file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
486
datafile 4 switched to datafile copy
487
input datafile copy RECID=3 STAMP=881437718 file name=/DBSoft/oracle/oradata/woo/users01.dbf
488
489
490
contents of Memory Script:
491
{
492
   Alter clone database open resetlogs;
493
}
494
executing Memory Script
495
496
497
database opened
498
Reenabling controlfile options for auxiliary database
499
Executing: alter database flashback on
500
Finished Duplicate Db at 03-JUN-15
501
502
503
RMAN>
504
505
至此,duplicate已经完毕,standby数据库已经起来了。
506
507
[oracle@db02 ~]$ sqlplus / as sysdba
508
509
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 3 19:53:02 2015
510
511
Copyright (c) 1982, 2013, Oracle. All rights reserved.
512
513
514
Connected to:
515
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
516
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
517
Oracle Database Vault and Real Application Testing options
518
519
SQL> col host_name format a15
520
SQL> col file_name format a40
521
SQL> set line 150
522
SQL> select host_name,instance_name,status from gv$instance;
523
524
HOST_NAME INSTANCE_NAME STATUS
525
--------------- ---------------- ------------
526
db02 woo OPEN
527
528
SQL> select tablespace_name,file_name from dba_data_files;
529
530
TABLESPACE_NAME FILE_NAME
531
------------------------------ ----------------------------------------
532
SYSTEM /DBSoft/oracle/oradata/woo/system01.dbf
533
SYSAUX /DBSoft/oracle/oradata/woo/sysaux01.dbf
534
UNDOTBS1 /DBSoft/oracle/oradata/woo/undotbs01.dbf
535
USERS /DBSoft/oracle/oradata/woo/users01.dbf
536
537
注意:这里的nofilenamecheck參数须要解释下:       假设在复制时,位置不同一时候,我们会用參数db_file_name_convert 对文件位置进行换。 可是在这个复制演示样例中我们用的是同样的位置。 所以这里必须加上nofilenamecheck參数。 该參数通知复制操作不必在运行还原操作前确认文件名称是不同的。假设没有指定nofilenamecheck參数,rman会给出例如以下错误:RMAN-05001: auxiliary filename  /DBSoft/oracle/oradata/woo/users01.dbf conflicts with a file used by the target database
538
539
540
541
   
542
543