【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 数据库本身对应用必定有影响。