select * from v$dbfile;      FILE# NAME..."/>
溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

修改Oracle數(shù)據(jù)文件名及數(shù)據(jù)文件存放路徑

發(fā)布時間：2020-08-11 15:07:02 來源：ITPUB博客 閱讀：225 作者：Davis_itpub 欄目：建站服務(wù)器
更改Oracle數(shù)據(jù)文件名及數(shù)據(jù)文件存放路徑
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

一、關(guān)閉數(shù)據(jù)庫進行修改
[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:數(shù)據(jù)文件脫機 （方法2必須在數(shù)據(jù)庫歸檔模式下進行)
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
 
向AI問一下細節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手