ORACLE 12C 冷備份方式的資料移轉（rename方式）

標籤：oracle資料庫遷移   冷備份   12c   

之前寫了一篇通過修改控制檔案來修改路徑不一的問題，現在使用方法二通過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的資訊。

源庫
[[email protected] 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; 

至此，完成資料的遷移。

本文出自 “Sysdba” 部落格，請務必保留此出處http://sysdba.blog.51cto.com/10492366/1702452

ORACLE 12C 冷備份方式的資料移轉（rename方式）