【Oracle】How to Rename or Move Datafiles and Logfiles 之一

楊奇龍發表於2011-10-11
Oracle
在做搭建第二備庫的時候用到了資料檔案的遷移，當時選擇了 在mount狀態下，offine 資料檔案然後在進行rename 的過程，這裡直接進行了，當然可以參考MOS 文件 How to Rename or Move Datafiles and Logfiles [ID 115424.1]
一在資料庫shutdown 模式 遷移資料檔案和日誌檔案
1 檢視檔案位置
SYS@yangdb-rac3> select file_name from dba_data_files
  2  union
  3  select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/yangdb/example01.dbf
/opt/oracle/oradata/yangdb/sysaux01.dbf
/opt/oracle/oradata/yangdb/system01.dbf
/opt/oracle/oradata/yangdb/temp01.dbf
/opt/oracle/oradata/yangdb/undotbs01.dbf
/opt/oracle/oradata/yangdb/users01.dbf
6 rows selected.
SYS@yangdb-rac3> select member from v$logfile;
MEMBER
-------------------------------------------------------------------------
/opt/oracle/oradata/yangdb/redo03.log
/opt/oracle/oradata/yangdb/redo02.log
/opt/oracle/oradata/yangdb/redo01.log

SYS@yangdb-rac3> col name for a50
SYS@yangdb-rac3> select name ,status ,enabled from v$datafile;
NAME                                               STATUS  ENABLED
-------------------------------------------------- ------- ----------
/opt/oracle/oradata/yangdb/system01.dbf            SYSTEM  READ WRITE
/opt/oracle/oradata/yangdb/sysaux01.dbf            ONLINE  READ WRITE
/opt/oracle/oradata/yangdb/undotbs01.dbf           ONLINE  READ WRITE
/opt/oracle/oradata/yangdb/users01.dbf             ONLINE  READ WRITE
/opt/oracle/oradata/yangdb/example01.dbf           ONLINE  READ WRITE

2 因為要移動整個資料庫的檔案，所以要修改引數檔案中的controfile的引數 
SYS@yangdb-rac3> create pfile='/tmp/inityangdb.ora' from spfile;
File created.
SYS@yangdb-rac3> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
3 移動所有的資料庫檔案到新的位置
oracle@rac3:/opt/oracle/oradata/yangdb>ls
control01.ctl  example01.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
oracle@rac3:/opt/oracle/oradata/yangdb>mv * ../yangdb_test/
oracle@rac3:/opt/oracle/oradata/yangdb>ls
oracle@rac3:/opt/oracle/oradata/yangdb>cd ../yangdb_test/
oracle@rac3:/opt/oracle/oradata/yangdb_test>ls
control01.ctl  example01.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
4 修改引數檔案pfile
*.control_files='/opt/oracle/oradata/yangdb_test/control01.ctl','/opt/oracle/oradata/yangdb_test/control02.ctl','/opt/oracle/oradata/yangdb_test/control03.ctl'
5 把老的spfile檔案mv到別的地方或者刪除，建立新的spfile
SYS@yangdb-rac3> create spfile from pfile='/tmp/inityangdb.ora';
File created.
SYS@yangdb-rac3> startup mount 
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size                  2213896 bytes
Variable Size             956303352 bytes
Database Buffers          687865856 bytes
Redo Buffers                7135232 bytes
Database mounted.

確認一下檔案位置：(可以省略)
SYS@yangdb-rac3> col member for a50
SYS@yangdb-rac3> select member from v$logfile;
MEMBER
--------------------------------------------------
/opt/oracle/oradata/yangdb/redo03.log
/opt/oracle/oradata/yangdb/redo02.log
/opt/oracle/oradata/yangdb/redo01.log
SYS@yangdb-rac3> select name ,status ,enabled from v$datafile;
NAME                                               STATUS  ENABLED
-------------------------------------------------- ------- ----------
/opt/oracle/oradata/yangdb/system01.dbf            SYSTEM  READ WRITE
/opt/oracle/oradata/yangdb/sysaux01.dbf            ONLINE  READ WRITE
/opt/oracle/oradata/yangdb/undotbs01.dbf           ONLINE  READ WRITE
/opt/oracle/oradata/yangdb/users01.dbf             ONLINE  READ WRITE
/opt/oracle/oradata/yangdb/example01.dbf           ONLINE  READ WRITE

SYS@yangdb-rac3>select name from v$controlfile; 
NAME
--------------------------------------------------
/opt/oracle/oradata/yangdb_test/control01.ctl
/opt/oracle/oradata/yangdb_test/control02.ctl
/opt/oracle/oradata/yangdb_test/control03.ctl
6 修改資料檔案和日誌檔案的位置：
SYS@yangdb-rac3> alter database rename file '/opt/oracle/oradata/yangdb/example01.dbf' to  '/opt/oracle/oradata/yangdb_test/example01.dbf';
Database altered.
SYS@yangdb-rac3> alter database rename file '/opt/oracle/oradata/yangdb/sysaux01.dbf'  to  '/opt/oracle/oradata/yangdb_test/sysaux01.dbf';
Database altered.
SYS@yangdb-rac3> alter database rename file '/opt/oracle/oradata/yangdb/system01.dbf'  to  '/opt/oracle/oradata/yangdb_test/system01.dbf';
Database altered.
SYS@yangdb-rac3> alter database rename file '/opt/oracle/oradata/yangdb/temp01.dbf'    to  '/opt/oracle/oradata/yangdb_test/temp01.dbf';
Database altered.
SYS@yangdb-rac3> alter database rename file '/opt/oracle/oradata/yangdb/undotbs01.dbf' to  '/opt/oracle/oradata/yangdb_test/undotbs01.dbf';
Database altered.
SYS@yangdb-rac3> alter database rename file '/opt/oracle/oradata/yangdb/users01.dbf'   to  '/opt/oracle/oradata/yangdb_test/users01.dbf';
Database altered.
SYS@yangdb-rac3> col member for a50
SYS@yangdb-rac3> select name ,status ,enabled from v$datafile;
NAME                                               STATUS  ENABLED
-------------------------------------------------- ------- ----------
/opt/oracle/oradata/yangdb_test/system01.dbf       SYSTEM  READ WRITE
/opt/oracle/oradata/yangdb_test/sysaux01.dbf       ONLINE  READ WRITE
/opt/oracle/oradata/yangdb_test/undotbs01.dbf      ONLINE  READ WRITE
/opt/oracle/oradata/yangdb_test/users01.dbf        ONLINE  READ WRITE
/opt/oracle/oradata/yangdb_test/example01.dbf      ONLINE  READ WRITE
7 修改redo 日誌檔案的位置！
SYS@yangdb-rac3> 
SYS@yangdb-rac3> alter database rename file '/opt/oracle/oradata/yangdb/redo03.log' to '/opt/oracle/oradata/yangdb_test/redo03.log';
Database altered.
SYS@yangdb-rac3> alter database rename file '/opt/oracle/oradata/yangdb/redo02.log' to '/opt/oracle/oradata/yangdb_test/redo02.log';
Database altered.
SYS@yangdb-rac3> alter database rename file '/opt/oracle/oradata/yangdb/redo01.log' to '/opt/oracle/oradata/yangdb_test/redo01.log';
Database altered.

SYS@yangdb-rac3> alter database open;
Database altered.
開啟資料庫進行驗證：
SYS@yangdb-rac3> col member for a50
SYS@yangdb-rac3> select member from v$logfile;
MEMBER
--------------------------------------------------
/opt/oracle/oradata/yangdb_test/redo03.log
/opt/oracle/oradata/yangdb_test/redo02.log
/opt/oracle/oradata/yangdb_test/redo01.log
SYS@yangdb-rac3> select name ,status ,enabled from v$datafile;
NAME                                               STATUS  ENABLED
-------------------------------------------------- ------- ----------
/opt/oracle/oradata/yangdb_test/system01.dbf       SYSTEM  READ WRITE
/opt/oracle/oradata/yangdb_test/sysaux01.dbf       ONLINE  READ WRITE
/opt/oracle/oradata/yangdb_test/undotbs01.dbf      ONLINE  READ WRITE
/opt/oracle/oradata/yangdb_test/users01.dbf        ONLINE  READ WRITE
/opt/oracle/oradata/yangdb_test/example01.dbf      ONLINE  READ WRITE
SYS@yangdb-rac3> select name from v$controlfile;
NAME
--------------------------------------------------
/opt/oracle/oradata/yangdb_test/control01.ctl
/opt/oracle/oradata/yangdb_test/control02.ctl
/opt/oracle/oradata/yangdb_test/control03.ctl
至此 成功遷移，僅僅是做測試用的，對於生產環境而言，shutdown 資料庫本身對應用必定有影響。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-708991/，如需轉載，請註明出處，否則將追究法律責任。

相關文章