使用rman copy方式迁移数据文件

本文所有的操作都是在linux环境下,非asm环境.

需求,原来的数据库服务器所挂的存储空间使用满了或者性能不够,新购买了存储,并且已经挂载到服务器上,需要将原来的数据文件迁移到新的存储中.

下面使用rman copy的方式进行迁移,停机时间降到最低

原来数据文件存放目录为/data/oradata/bre1  现在新的数据文件目录为/data/oradata/bre1_test2

一.迁移redo日志文件(不需要停机)

redo文件是最方便迁移的.使用的办法是为原来的redo group中添加新的member,然后在原来的member删除:

1.新增日志成员:

 

select 'alter database add logfile member ''/data/oradata/bre1_test2/'||substr(member,length(member)-INSTR(reverse(member),'/')+1,INSTR(reverse(member),'/'))||''' to group '||group#||';' from v$logfile where type='ONLINE';

'ALTERDATABASEADDLOGFILEMEMBER''/DATA/ORADATA/BRE1_TEST2/'||SUBSTR(MEMBER,LENGTH(MEMBER)-INSTR(REVERSE(MEMBER),'/')+1,INSTR(REVERSE(MEMBER),'/'))||'''TOGROUP'||GROUP#||';'

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

SQL>alter database add logfile member '/data/oradata/bre1_test2/redo01.log' to group 1;

SQL>alter database add logfile member '/data/oradata/bre1_test2/redo02.log' to group 2;

SQL>alter database add logfile member '/data/oradata/bre1_test2/redo03.log' to group 3;

2.删除旧的日志成员:

 

select 'alter database drop logfile member '''||member||''';' from v$logfile where member not like '%test2%' and type='ONLINE';

SQL>alter database drop logfile member '/data/oradata/bre1/redo01.log';

SQL>alter database drop logfile member '/data/oradata/bre1/redo02.log';

SQL>alter database drop logfile member '/data/oradata/bre1/redo03.log';

如果当中出现报错,则切换几次归档即可.如果还存在standby redo日志,则直接删除,后面如果需要手工再创建

select 'alter database drop logfile group '||group#||';' from v$logfile where type='STANDBY';

 

二.切换默认临时表空间(不需要停机)

1.创建新的临时表空间,临时文件指定新的磁盘组

SQL> create temporary tablespace temp01 tempfile '/data/oradata/bre1_test2/temp02.dbf' size 100m autoextend on;

Tablespace created.

2.将数据库的默认临时表空间设置到新的临时表空间

SQL> alter database default temporary tablespace temp01;

Database altered.

3.删除老的临时表空间

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

 

三.修改其它临时表空间文件(不需要停机)

上面是修改的数据库默认临时表空间.如果数据库内还有其它的临时表空间,则采用和日志文件一样的方法

先给临时表空间添加临时文件,再删除原来的临时文件:

 

alter tablespace temp2 add tempfile '/data/oradata/bre1_test2/temp2_01.dbf' size 100m autoextend on;

alter database tempfile '/data/oradata/bre1/temp02.dbf' drop;

做完上面几步后,检查一下临时文件和redo日志是否都已经到了新的目录中:

 

SQL> select member from v$logfile;

MEMBER

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

/data/oradata/bre1_test2/redo01.log

/data/oradata/bre1_test2/redo02.log

/data/oradata/bre1_test2/redo03.log

SQL> select file_name from dba_temp_files;

FILE_NAME

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

/data/oradata/bre1_test2/temp02.dbf

/data/oradata/bre1_test2/temp2_01.dbf

 

四.使用rman copy进行数据文件copy(不需要停机)

建议此步骤在业务空闲期间做,这样在copy期间产生的归档日志较少,最后切换数据文件的时候需要应用的日志较少,停机时间较短

使用rman进行copy,可以开多个通道加快copy速度.

 

run{

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

allocate channel ch3 device type disk;

allocate channel ch4 device type disk;

backup as copy database format '/data/oradata/bre1_test2/%b';

release channel ch1;

release channel ch2;

release channel ch3;

release channel ch4;

}

这一步会有报错,报错的意思是备份控制文件的时候有问题,这里我们忽略,我们采用手工拷贝控制文件的方式,不采用rman备份控制文件

 

五.停止数据库,修改参数文件,拷贝控制文件,恢复数据库(需要停机)

 

1.修改参数文件

SQL> alter system set control_files='/data/oradata/bre1_test2/control01.ctl' scope=spfile;

2.停止数据库,拷贝控制文件

注意这里拷贝的文件名需要和上面参数文件中指定的一致

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ora-bre1 bre1_test2]$ cp /data/oradata/bre1/control01.ctl /data/oradata/bre1_test2/control01.ctl

3.使用rman恢复数据库

rman target /

startup mount;

switch database to copy;

recover database;

alter database open;

 

[oracle@ora-bre1 bre1_test2]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 2 10:25:49 2019

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

connected to target database (not started)

RMAN>startup mount;

RMAN>switch database to copy;

RMAN>recover database;

RMAN>alter database open;

Oracle instance started

database mounted

Total System Global Area    1043886080 bytes

Fixed Size                     2259840 bytes

Variable Size                599786624 bytes

Database Buffers             436207616 bytes

Redo Buffers                   5632000 bytes

RMAN> 

using target database control file instead of recovery catalog

datafile 1 switched to datafile copy "/data/oradata/bre1_test2/SYSTEM1.dbf"

datafile 2 switched to datafile copy "/data/oradata/bre1_test2/SYSAUX2.dbf"

datafile 3 switched to datafile copy "/data/oradata/bre1_test2/UNDOTBS13.dbf"

datafile 4 switched to datafile copy "/data/oradata/bre1_test2/USERS4.dbf"

RMAN> 

Starting recover at 02-APR-19

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

starting media recovery

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

Finished recover at 02-APR-19

RMAN> 

database opened

到此,数据库迁移完成.检查所有的文件目录

 

SQL> select file_name from dba_data_files;

FILE_NAME

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

/data/oradata/bre1_test2/SYSTEM1.dbf

/data/oradata/bre1_test2/SYSAUX2.dbf

/data/oradata/bre1_test2/UNDOTBS13.dbf

/data/oradata/bre1_test2/USERS4.dbf

SQL> select file_name from dba_temp_files;

FILE_NAME

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

/data/oradata/bre1_test2/temp02.dbf

/data/oradata/bre1_test2/temp2_01.dbf

SQL> select member from v$logfile;

MEMBER

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

/data/oradata/bre1_test2/redo01.log

/data/oradata/bre1_test2/redo02.log

/data/oradata/bre1_test2/redo03.log

SQL> show parameter control

NAME                                  TYPE       VALUE

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

control_file_record_keep_time        integer     30

control_files                        string      /data/oradata/bre1_test2/control01.ctl

control_management_pack_access       string      DIAGNOSTIC+TUNING