修改Oracle数据文件名及数据文件存放路径

更改Oracle数据文件名及数据文件存放路径

SQL> select * from v$dbfile;
 
     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 /db2/oracle/oradata/db2/system01.dbf
         2 /db2/oracle/oradata/db2/sysaux01.dbf
         3 /db2/oracle/oradata/db2/undotbs01.dbf
         4 /db2/oracle/oradata/db2/users01.dbf
         5 /db2/oracle/product/11.2.0/db_1/dbs/D:ORACLE11GADMINORADATAADMINgg01.dbf
         6 /db2/oracle/oradata/db2/db201.dbf

一、关闭数据库进行修改
[oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 17:43:38 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@db1 ~]$ mv /db2/oracle/product/11.2.0/db_1/dbs/D\:ORACLE11GADMINORADATAADMINgg01.dbf  /db2/oracle/oradata/db2/gg01.dbf

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 17:48:31 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size      2214936 bytes
Variable Size    163578856 bytes
Database Buffers   352321536 bytes
Redo Buffers      3821568 bytes
Database mounted.

SQL> alter database rename file '/db2/oracle/product/11.2.0/db_1/dbs/D\:ORACLE11GADMINORADATAADMINgg01.dbf' to '/db2/oracle/oradata/db2/gg01.dbf';

Database altered.

SQL> select * from v$dbfile;
 
     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 /db2/oracle/oradata/db2/system01.dbf
         2 /db2/oracle/oradata/db2/sysaux01.dbf
         3 /db2/oracle/oradata/db2/undotbs01.dbf
         4 /db2/oracle/oradata/db2/users01.dbf
         5 /db2/oracle/oradata/db2/gg01.dbf
         6 /db2/oracle/oradata/db2/db201.dbf
 
6 rows selected

二、在线修改
方法1:表空间offline
SQL> select  *  from dba_data_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
/db2/oracle/oradata/db2/system01.dbf                                                      1 SYSTEM                          734003200      89600 AVAILABLE            1 YES            3435972198    4194302         1280  732954624       89472 SYSTEM
/db2/oracle/oradata/db2/sysaux01.dbf                                                      2 SYSAUX                          629145600      76800 AVAILABLE            2 YES            3435972198    4194302         1280  628097024       76672 ONLINE
/db2/oracle/oradata/db2/undotbs01.dbf                                                     3 UNDOTBS1                        618659840      75520 AVAILABLE            3 YES            3435972198    4194302          640  617611264       75392 ONLINE
/db2/oracle/oradata/db2/users01.dbf                                                       4 USERS                             5242880        640 AVAILABLE            4 YES            3435972198    4194302          160    4194304         512 ONLINE
/db2/oracle/oradata/db2/gg01.dbf                                                          5 GG                              104857600      12800 AVAILABLE            5 NO                      0          0            0  103809024       12672 ONLINE
/db2/oracle/oradata/db2/db201.dbf                                                         6 DB2                             104857600      12800 AVAILABLE            6 NO                      0          0            0  103809024       12672 ONLINE
 
SQL> alter tablespace gg offline;
 
Tablespace altered

SQL> host mv /db2/oracle/oradata/db2/gg01.dbf /db2/oracle/oradata/db2/admin.dbf

SQL> alter database rename file '/db2/oracle/oradata/db2/gg01.dbf' to '/db2/oracle/oradata/db2/admin.dbf';
 
Database altered

SQL> alter tablespace gg online;
 
Tablespace altered

SQL> select  *  from dba_data_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
/db2/oracle/oradata/db2/system01.dbf                                                      1 SYSTEM                          734003200      89600 AVAILABLE            1 YES            3435972198    4194302         1280  732954624       89472 SYSTEM
/db2/oracle/oradata/db2/sysaux01.dbf                                                      2 SYSAUX                          629145600      76800 AVAILABLE            2 YES            3435972198    4194302         1280  628097024       76672 ONLINE
/db2/oracle/oradata/db2/undotbs01.dbf                                                     3 UNDOTBS1                        618659840      75520 AVAILABLE            3 YES            3435972198    4194302          640  617611264       75392 ONLINE
/db2/oracle/oradata/db2/users01.dbf                                                       4 USERS                             5242880        640 AVAILABLE            4 YES            3435972198    4194302          160    4194304         512 ONLINE
/db2/oracle/oradata/db2/admin.dbf                                                         5 GG                              104857600      12800 AVAILABLE            5 NO                      0          0            0  103809024       12672 ONLINE
/db2/oracle/oradata/db2/db201.dbf                                                         6 DB2                             104857600      12800 AVAILABLE            6 NO                      0          0            0  103809024       12672 ONLINE
 
方法2:数据文件脱机 （方法2必须在数据库归档模式下进行)
SQL> alter database datafile 5 offline;
alter database datafile 5 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

SQL> archive log list;
Database log mode        No Archive Mode
Automatic archival        Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     69
Current log sequence        71

开归档
[oracle@db1 ~]$ mkdir /db2/oracle/archivelog
[oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 18:15:52 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size      2214936 bytes
Variable Size    163578856 bytes
Database Buffers   352321536 bytes
Redo Buffers      3821568 bytes
Database mounted.
SQL> alter system set log_archive_dest_1='location=/db2/oracle/archivelog/';

System altered.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /db2/oracle/archivelog/
Oldest online log sequence     69
Next log sequence to archive   71
Current log sequence        71

SQL> alter database datafile 5 offline;

Database altered.

SQL> host mv /db2/oracle/oradata/db2/admin.dbf /db2/oracle/oradata/db2/gg01.dbf

SQL> alter database rename file '/db2/oracle/oradata/db2/admin.dbf' to '/db2/oracle/oradata/db2/gg01.dbf';

Database altered.

SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/db2/oracle/oradata/db2/gg01.dbf'

SQL> recover datafile 5;
Media recovery complete.

SQL> alter database datafile 5 online;

Database altered.

SQL> select file_id,file_name,tablespace_name,online_status from dba_data_files;
 
   FILE_ID FILE_NAME                                                                        TABLESPACE_NAME                ONLINE_STATUS
---------- -------------------------------------------------------------------------------- ------------------------------ -------------
         1 /db2/oracle/oradata/db2/system01.dbf                                             SYSTEM                         SYSTEM
         2 /db2/oracle/oradata/db2/sysaux01.dbf                                             SYSAUX                         ONLINE
         3 /db2/oracle/oradata/db2/undotbs01.dbf                                            UNDOTBS1                       ONLINE
         4 /db2/oracle/oradata/db2/users01.dbf                                              USERS                          ONLINE
         5 /db2/oracle/oradata/db2/gg01.dbf                                                 GG                             ONLINE
         6 /db2/oracle/oradata/db2/db201.dbf                                                DB2                            ONLINE