环境：


sys@ORCL> select * from v$version;


BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod


sys@ORCL> !uname

Linux



⑴ 修改控制文件的位置


sys@ORCL> show parameter control_files


NAME TYPE VALUE

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

control_files string /u01/app/oracle/oradata/ORCL/c

ontrolfile/o1_mf_8050hgfp_.ctl

, /u01/app/oracle/flash_recove

ry_area/ORCL/controlfile/o1_mf

_8050hgqh_.ctl



sys@ORCL> alter database backup controlfile to '/home/oracle/ctlasm.ctl';



sys@ORCL> alter system set control_files='+WATER' scope=spfile;



sys@ORCL> shutdown immediate;




⑵ 数据文件迁移

RMAN> startup nomount


RMAN> restore controlfile from '/home/oracle/ctlasm.ctl';


RMAN> alter database mount;


RMAN> backup as copy database format '+WATER';


RMAN> recover database;


RMAN> switch database to copy;




⑶ 日志文件迁移

idle> conn / as sysdba


idle> alter database open resetlogs;


idle> alter database add logfile group 4 '+WATER/redo04.log' size 50m;



idle> alter database add logfile group 5 '+WATER/redo05.log' size 50m;



idle> alter database add logfile group 6 '+WATER/redo06.log' size 50m;


idle> select group#,status from v$log;


GROUP# STATUS

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

1 UNUSED

2 CURRENT

3 UNUSED

4 UNUSED

5 UNUSED

6 UNUSED


idle> alter system switch logfile;



idle> alter system switch logfile;



idle> alter system switch logfile;


idle> select group#,status from v$log;


GROUP# STATUS

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

1 ACTIVE

2 ACTIVE

3 ACTIVE

4 ACTIVE

5 ACTIVE

6 CURRENT


idle> alter system checkpoint;


idle> alter database drop logfile group 1;



idle> alter database drop logfile group 2;



idle> alter database drop logfile group 3;



idle> select member from v$logfile;


MEMBER

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

+WATER/redo04.log

+WATER/redo05.log

+WATER/redo06.log




⑷ 临时文件迁移

sys@ORCL> select file_name,tablespace_name from dba_temp_files;


FILE_NAME TABLESPACE_NAME

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

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_8050j34j_.tmp TEMP


sys@ORCL> alter tablespace temp add tempfile '+WATER';


sys@ORCL> select file_name,tablespace_name from dba_temp_files;


FILE_NAME TABLESPACE_NAME

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

+WATER/orcl/tempfile/temp.265.798479421 TEMP

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_8050j34j_.tmp TEMP


sys@ORCL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_8050j34j_.tmp';




⑸ 修改db_create_file_dest，db_recovery_file_dest，db_recovery_file_dest_size 的值

sys@ORCL> alter system set db_create_file_dest='+WATER/ORCL/ORADATA' scope=spfile;



sys@ORCL> alter system set db_recovery_file_dest='+WATER' scope=spfile;



sys@ORCL> alter system set db_recovery_file_dest_size=1 G scope=spfile;



sys@ORCL> alter database backup controlfile to '+WATER'; --迁移时，只有一个控制文件



sys@ORCL> show parameter control


NAME TYPE VALUE

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

control_file_record_keep_time integer 7

control_files string +WATER/orcl/controlfile/curren

t.256.798477831




⑹ 修改参数文件并迁移spfile

sys@ORCL> alter system set control_files='+WATER/ORCL/ORADATA/CONTROL01.CTL','+WATER/ORCL/ORADATA/CONTROL02.CTL' scope=spfile;


sys@ORCL> create spfile='+WATER/ORCL/spfileorcl.ora' from pfile;


[oracle@localhost dbs]$ rm -i spfileorcl.ora


[oracle@localhost dbs]$ vim initorcl.ora


[oracle@localhost dbs]$ tail -f initorcl.ora

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=139460608

*.processes=150

*.remote_login_passwordfile='NONE'

*.sga_target=418381824

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

SPFILE='+WATER/ORCL/spfileorcl.ora'




⑺ 检查ASM实例和数据库实例连接情况

ASMCMD> lsct

DB_Name Status Software_Version Compatible_version Instance_Name

orcl CONNECTED 10.2.0.1.0 10.2.0.1.0 orcl




小结：

第一次迁移时失败，原因是仅作学习之用，磁盘组空间太小，大家如果空间富裕，不妨慷慨些。


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html