oracle冷迁移,ORACLE 12C 冷备份方式的数据迁移（rename方式）

之前写了一篇通过修改控制文件来修改路径不一的问题，现在使用方法二通过rename来修改路径不一的问题

实验对象：两台linux单机的oracle 12C 数据库(大版本和小版本都一致)，数据迁移采取冷备份的方式。

迁移步骤

首先关闭监听，和kill掉连接的应用

ps -ef | grep LOCAL=NO | awk '{print ($2)}' | xargs kill -9

创建pfile

create pfile='/tmp/inittest.ora1012' from spfile;

查看数据文件，日志文件的路径，日志文件路径

SQL> set line 200

SQL> col FILE_NAME for a80

SQL>  select TABLESPACE_NAME,file_name from  dba_data_files;

TABLESPACE_NAME                FILE_NAME

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

SYSTEM                         /u01/app/oracle/oradata/test/system01.dbf

SYSAUX                         /u01/app/oracle/oradata/test/sysaux01.dbf

UNDOTBS1                       /u01/app/oracle/oradata/test/undotbs01.dbf

USERS                          /u01/app/oracle/oradata/test/users01.dbf

QWERTY                         /u01/app/oracle/oradata/test/qwerty.dbf

TESTBIG                        /u01/app/oracle/oradata/test/testbig.dbf

DATA                           /u01/app/oracle/oradata/test/data_01.dbf

SQL> select TABLESPACE_NAME,file_name from  dba_temp_files;

TABLESPACE_NAME                FILE_NAME

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

TEMP                           /u01/app/oracle/oradata/test/temp01.dbf

TEMP_ASYNC                     /u01/app/oracle/oradata/test/temp_async_01.dbf

SQL> set line 200

SQL>  col MEMBER for a80

SQL> select GROUP#,MEMBER from v$logfile;

GROUP# MEMBER

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

1 /u01/app/oracle/oradata/test/redo01.log

2 /u01/app/oracle/oradata/test/redo02.log

3 /u01/app/oracle/oradata/test/redo03.log

SQL> show parameter control_files

NAME                                 TYPE        VALUE

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

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

ontrol01.ctl, /u01/app/oracle/

fast_recovery_area/test/contro

l02.ctl

关闭数据库后，拷贝参数文件，数据文件，日志文件，控制文件。

shutdown immediate

将所有需要的文件拷到本地，以便更好的传输。

cp ......

拷贝文件到目标服务器上。

scp ...

在目标服务器上修改pfile的信息。

源库

[oracle@test dbs]$ vi inittest.ora

test.__data_transfer_cache_size=0

test.__db_cache_size=339738624

test.__java_pool_size=4194304

test.__large_pool_size=8388608

test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

test.__pga_aggregate_target=293601280

test.__sga_target=545259520

test.__shared_io_pool_size=16777216

test.__shared_pool_size=167772160

test.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/test/adump'

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='/u01/app/oracle/oradata/test/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='test'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4815m

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=800m

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

以下操作在目标数据库上操作

修改了控制文件的路径

*.control_files='/u01/app/oracle/oradata/test/controlfile/control01.ctl'

修改数据文件的路径

alter database rename file '/u01/app/oracle/oradata/test/system01.dbf'  to '/u01/app/oracle/oradata/test/datafile/system01.dbf';

alter database rename file '/u01/app/oracle/oradata/test/sysaux01.dbf'  to '/u01/app/oracle/oradata/test/datafile/sysaux01.dbf';

alter database rename file '/u01/app/oracle/oradata/test/undotbs01.dbf' to '/u01/app/oracle/oradata/test/datafile/undotbs01.dbf';

alter database rename file '/u01/app/oracle/oradata/test/users01.dbf'   to '/u01/app/oracle/oradata/test/datafile/users01.dbf';

alter database rename file '/u01/app/oracle/oradata/test/qwerty.dbf'    to '/u01/app/oracle/oradata/test/datafile/qwerty.dbf';

alter database rename file '/u01/app/oracle/oradata/test/testbig.dbf'   to '/u01/app/oracle/oradata/test/datafile/testbig.dbf';

alter database rename file '/u01/app/oracle/oradata/test/data_01.dbf'   to '/u01/app/oracle/oradata/test/datafile/data_01.dbf';

alter database rename file '/u01/app/oracle/oradata/test/temp_async_01.dbf' to  '/u01/app/oracle/oradata/test/datafile/temp_async_01.dbf';

alter database rename file '/u01/app/oracle/oradata/test/temp01.dbf' to  '/u01/app/oracle/oradata/test/datafile/temp01.dbf';

alter database rename file '/u01/app/oracle/oradata/test/temp02.dbf' to  '/u01/app/oracle/oradata/test/datafile/temp02.dbf';

修改日志文件的路径

alter database rename file '/u01/app/oracle/oradata/test/redo01.log' to '/u01/app/oracle/oradata/test/onlinelog/redo01.log';

alter database rename file '/u01/app/oracle/oradata/test/redo02.log' to '/u01/app/oracle/oradata/test/onlinelog/redo02.log';

alter database rename file '/u01/app/oracle/oradata/test/redo03.log' to '/u01/app/oracle/oradata/test/onlinelog/redo03.log';

alter database open;

至此，完成数据的迁移。