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

在做搭建第二备库的时候用到了数据文件的迁移，当时选择了 在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 数据库本身对应用必定有影响。