ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 在ORACLE11gR2上使用RMAN实现整库迁移

在ORACLE11gR2上使用RMAN实现整库迁移

原创 Linux操作系统 作者:mengxianyao 时间:2012-06-07 17:29:52 0 删除 编辑

ORACLE11gR2上使用RMAN实现整库迁移

背景:

数据库

IP

SID

端口

数据库版本

Target DB

192.168.1.133

orcl

1521

11.2.0.1.0

Auxiliary DB

192.168.1.133(可以是同一机器,也可以是不同机器)

V112

1522

11.2.0.1.0

 

1. Auxiliary 创建pfile 参数文件

 

[oracle@localhost dbs]$ pwd

/u01/oracle11gR2/product/11.2.0/db_1/dbs

[oracle@localhost dbs]$ more initv112.ora

*.DB_NAME=v112

*.control_files='/u01/oracle11gR2/oradata/v112/control01.ctl','/u01/oracle11gR2/flash_recovery_area

/v112/control02.ctl'

*.db_block_size=8192

*.compatible='11.2.0.1'

_compression_compatibility="11.2.0"   注:这个隐含参数可以绕过11.2.0.1bug

*.db_recovery_file_dest_size=4294967296

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

*.audit_file_dest='/u01/oracle11gR2/admin/v112/adump'

 

   

2. Auxiliary库上创建口令文件

[oracle@localhost dbs]$ pwd

/u01/oracle11gR2/product/11.2.0/db_1/dbs

[oracle@localhost dbs]$ orap

orapipe  orapki   orapwd   orapwdO 

[oracle@localhost dbs]$ orapwd file=orapwv112 password=Your password

   

3. Auxiliary库创建相关的目录结构

[oracle@localhost dbs]$ mkdir /u01/oracle11gR2/admin/v112/

[oracle@localhost dbs]$ mkdir /u01/oracle11gR2/oradata/v112/

 

注:在duplicate过程中,如果目录不存在,则会报错“ORA-27040: file create error, unable to create file

 

4. 启动Auxiliary nomout 状态

 

SQL> conn / as sysdba;

Connected to an idle instance.

SQL> startup nomount pfile=/u01/oracle11gR2/product/11.2.0/db_1/dbs/initv112.ora

ORACLE instance started.

Total System Global Area  146472960 bytes

Fixed Size                  1335080 bytes

Variable Size              92274904 bytes

Database Buffers           50331648 bytes

Redo Buffers                2531328 bytes

    

5. Target Auxiliary 都配置Oracle NetListener.ora and tnsnames.ora[可选]

 

 

 

[oracle@localhost admin]$ more tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/oracle11gR2/product/11.2.0/db_1/network/admin/tnsna

mes.ora

# Generated by Oracle configuration tools.

v112 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = v112)

    )

  )

LISTENER_v112 =

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

ORCL =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID_NAME = orcl

    )

  )

LISTENER_ORCL =

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

 

[oracle@localhost admin]$ more listener.ora

# listener.ora Network Configuration File: /u01/oracle11gR2/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = v112)

      (ORACLE_HOME = /u01/oracle11gR2/product/11.2.0/db_1)

      (SID_NAME = v112)

    )

  )

 

LISTENER2 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.133)(PORT = 1522))

    )

  )

 

SID_LIST_LISTENER2 =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /u01/oracle11gR2/product/11.2.0/db_1)

      (SID_NAME = orcl)

    )

  )

 

ADR_BASE_LISTENER1 = /u01/oracle11gR2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


6. 开始RMAN duplicate from active database

 

 

[oracle@localhost temp]$ rman target sys/mxy19880122@192.168.1.133:1522/orcl auxiliary sys/mxy19880122@192.168.1.133:1521/v112

 

:这里没有用tnsnames.ora文件中的配置。

 

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jun 3 17:31:11 2012

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORCL (DBID=1311170080)

connected to auxiliary database: V112 (not mounted)

 

RMAN> run{

set newname for datafile '/u01/oracle11gR2/oradata/orcl/system01.dbf' to

3>

'/u01/oracle11gR2/oradata/v112/system01.dbf';

 set newname for datafile '/u01/oracle11gR2/oradata/orcl/sysaux01.dbf' to

6>

'/u01/oracle11gR2/oradata/v112/sysaux01.dbf';

set newname for datafile '/u01/oracle11gR2/oradata/orcl/undotbs01.dbf' to

9>

'/u01/oracle11gR2/oradata/v112/undotbs01.dbf';

set newname for datafile '/u01/oracle11gR2/oradata/orcl/users01.dbf' to

11>

'/u01/oracle11gR2/oradata/v112/users01.dbf';

set newname for datafile

14> 15>

'/u01/oracle11gR2/oradata/orcl/ORCL/datafile/o1_mf_mxy_7v8tdctq_.dbf' to

17>

18> '/u01/oracle11gR2/oradata/v112/mxy.dbf';

19> duplicate target database to V112 from active database nofilenamecheck;}

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

注:由于RMAN需要将文件恢复到新的位置,因此需要对文件进行重新映射。

 

Starting Duplicate Db at 03-JUN-12

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

 

contents of Memory Script.:

{

   sql clone "create spfile from memory";

}

executing Memory Script

 

sql statement: create spfile from memory

 

contents of Memory Script.:

{

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

 

注:恢复spfile

 

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area     217157632 bytes

 

Fixed Size                     2211928 bytes

Variable Size                159387560 bytes

Database Buffers              50331648 bytes

Redo Buffers                   5226496 bytes

 

contents of Memory Script.:

{

   sql clone "alter system set  db_name =

 ''ORCL'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''V112'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   shutdown clone immediate;

   startup clone force nomount

   backup as copy current controlfile auxiliary format  '/u01/oracle11gR2/oradata/v112/control01.ctl';

   restore clone controlfile to  '/u01/oracle11gR2/flash_recovery_area/v112/control02.ctl' from

 '/u01/oracle11gR2/oradata/v112/control01.ctl';

   alter clone database mount;

}

executing Memory Script

 

注:恢复控制文件

 

sql statement: alter system set  db_name =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

 

sql statement: alter system set  db_unique_name =  ''V112'' comment= ''Modified by RMAN duplicate'' scope=spfile

 

注:重新设置db_name

 

Oracle instance shut down

 

Oracle instance started

 

Total System Global Area     217157632 bytes

 

Fixed Size                     2211928 bytes

Variable Size                159387560 bytes

Database Buffers              50331648 bytes

Redo Buffers                   5226496 bytes

 

Starting backup at 03-JUN-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=34 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/u01/oracle11gR2/product/11.2.0/db_1/dbs/snapcf_orcl.f tag=TAG20120603T173213 RECID=1 STAMP=785007137

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

Finished backup at 03-JUN-12

 

Starting restore at 03-JUN-12

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

 

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 03-JUN-12

 

database mounted

 

contents of Memory Script.:

{

   set newname for datafile  1 to

 "/u01/oracle11gR2/oradata/v112/system01.dbf";

   set newname for datafile  2 to

 "/u01/oracle11gR2/oradata/v112/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/oracle11gR2/oradata/v112/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/oracle11gR2/oradata/v112/users01.dbf";

   set newname for datafile  5 to

 "/u01/oracle11gR2/oradata/v112/mxy.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

 "/u01/oracle11gR2/oradata/v112/system01.dbf"   datafile

 2 auxiliary format

 "/u01/oracle11gR2/oradata/v112/sysaux01.dbf"   datafile

 3 auxiliary format

 "/u01/oracle11gR2/oradata/v112/undotbs01.dbf"   datafile

 4 auxiliary format

 "/u01/oracle11gR2/oradata/v112/users01.dbf"   datafile

 5 auxiliary format

 "/u01/oracle11gR2/oradata/v112/mxy.dbf"   ;

   sql 'alter system archive log current';

}

executing Memory Script

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

注:开始恢复数据文件(耐心等待)

Starting backup at 03-JUN-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/oracle11gR2/oradata/orcl/system01.dbf

output file name=/u01/oracle11gR2/oradata/v112/system01.dbf tag=TAG20120603T173229

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/oracle11gR2/oradata/orcl/sysaux01.dbf

output file name=/u01/oracle11gR2/oradata/v112/sysaux01.dbf tag=TAG20120603T173229

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:06

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/oracle11gR2/oradata/orcl/undotbs01.dbf

output file name=/u01/oracle11gR2/oradata/v112/undotbs01.dbf tag=TAG20120603T173229

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/oracle11gR2/oradata/orcl/users01.dbf

output file name=/u01/oracle11gR2/oradata/v112/users01.dbf tag=TAG20120603T173229

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/oracle11gR2/oradata/orcl/ORCL/datafile/o1_mf_mxy_7v8tdctq_.dbf

output file name=/u01/oracle11gR2/oradata/v112/mxy.dbf tag=TAG20120603T173229

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

Finished backup at 03-JUN-12

 

sql statement: alter system archive log current

 

contents of Memory Script.:

{

   backup as copy reuse

   archivelog like  "/u01/oracle11gR2/flash_recovery_area/ORCL/archivelog/2012_06_03/o1_mf_1_291_7wpd7h5x_.arc" auxiliary format

 "/u01/oracle11gR2/flash_recovery_area/V112/archivelog/2012_06_03/o1_mf_1_291_%u_.arc"   ;

   catalog clone recovery area;

   switch clone datafile all;

}

executing Memory Script

 

Starting backup at 03-JUN-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=291 RECID=176 STAMP=785007599

output file name=/u01/oracle11gR2/flash_recovery_area/V112/archivelog/2012_06_03/o1_mf_1_291_0hnckgvg_.arc RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 03-JUN-12

 

searching for all files in the recovery area

 

List of Files Unknown to the Database

=====================================

File Name: /u01/oracle11gR2/flash_recovery_area/V112/archivelog/2012_06_03/o1_mf_1_291_0hnckgvg_.arc

cataloging files...

cataloging done

 

List of Cataloged Files

=======================

File Name: /u01/oracle11gR2/flash_recovery_area/V112/archivelog/2012_06_03/o1_mf_1_291_0hnckgvg_.arc

 

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=785007602 file name=/u01/oracle11gR2/oradata/v112/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=785007602 file name=/u01/oracle11gR2/oradata/v112/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=785007602 file name=/u01/oracle11gR2/oradata/v112/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=785007602 file name=/u01/oracle11gR2/oradata/v112/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=5 STAMP=785007602 file name=/u01/oracle11gR2/oradata/v112/mxy.dbf

 

contents of Memory Script.:

{

   set until scn  6603687;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

 

executing command: SET until clause

 

Starting recover at 03-JUN-12

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 291 is already on disk as file /u01/oracle11gR2/flash_recovery_area/V112/archivelog/2012_06_03/o1_mf_1_291_0hnckgvg_.arc

archived log file name=/u01/oracle11gR2/flash_recovery_area/V112/archivelog/2012_06_03/o1_mf_1_291_0hnckgvg_.arc thread=1 sequence=291

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

Finished recover at 03-JUN-12

 

contents of Memory Script.:

{

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  db_name =

 ''V112'' comment=

 ''Reset to original value by RMAN'' scope=spfile";

   sql clone "alter system reset  db_unique_name scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

 

database dismounted

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area     217157632 bytes

 

Fixed Size                     2211928 bytes

Variable Size                159387560 bytes

Database Buffers              50331648 bytes

Redo Buffers                   5226496 bytes

 

sql statement: alter system set  db_name =  ''V112'' comment= ''Reset to original value by RMAN'' scope=spfile

 

sql statement: alter system reset  db_unique_name scope=spfile

 

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area     217157632 bytes

 

Fixed Size                     2211928 bytes

Variable Size                159387560 bytes

Database Buffers              50331648 bytes

Redo Buffers                   5226496 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "V112" RESETLOGS ARCHIVELOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1  SIZE 50 M ,

  GROUP  2  SIZE 50 M ,

  GROUP  3  SIZE 50 M

 DATAFILE

  '/u01/oracle11gR2/oradata/v112/system01.dbf'

 CHARACTER SET AL32UTF8

 

 

contents of Memory Script.:

{

   set newname for tempfile  1 to

 "/u01/oracle11gR2/oradata/orcl/temp01.dbf";

   set newname for tempfile  2 to

 "/u01/oracle11gR2/oradata/orcl/ORCL/datafile/o1_mf_temp1_7vb4dcv5_.tmp";

   set newname for tempfile  3 to

 "/u01/oracle11gR2/oradata/orcl/ORCL/datafile/o1_mf_temp2_7vb4djq8_.tmp";

   switch clone tempfile all;

   catalog clone datafilecopy  "/u01/oracle11gR2/oradata/v112/sysaux01.dbf",

 "/u01/oracle11gR2/oradata/v112/undotbs01.dbf",

 "/u01/oracle11gR2/oradata/v112/users01.dbf",

 "/u01/oracle11gR2/oradata/v112/mxy.dbf";

   switch clone datafile all;

}

executing Memory Script

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /u01/oracle11gR2/oradata/orcl/temp01.dbf in control file

renamed tempfile 2 to /u01/oracle11gR2/oradata/orcl/ORCL/datafile/o1_mf_temp1_7vb4dcv5_.tmp in control file

renamed tempfile 3 to /u01/oracle11gR2/oradata/orcl/ORCL/datafile/o1_mf_temp2_7vb4djq8_.tmp in control file

 

cataloged datafile copy

datafile copy file name=/u01/oracle11gR2/oradata/v112/sysaux01.dbf RECID=1 STAMP=785007628

cataloged datafile copy

datafile copy file name=/u01/oracle11gR2/oradata/v112/undotbs01.dbf RECID=2 STAMP=785007628

cataloged datafile copy

datafile copy file name=/u01/oracle11gR2/oradata/v112/users01.dbf RECID=3 STAMP=785007628

cataloged datafile copy

datafile copy file name=/u01/oracle11gR2/oradata/v112/mxy.dbf RECID=4 STAMP=785007628

 

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=785007628 file name=/u01/oracle11gR2/oradata/v112/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=785007628 file name=/u01/oracle11gR2/oradata/v112/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=785007628 file name=/u01/oracle11gR2/oradata/v112/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=785007628 file name=/u01/oracle11gR2/oradata/v112/mxy.dbf

 

contents of Memory Script.:

{

   Alter clone database open resetlogs;

}

executing Memory Script

 

database opened

Finished Duplicate Db at 03-JUN-12

 

RMAN> exit

 

7. 复制结束

 

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-06-07

  • 博文量
    4
  • 访问量
    9510