修改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
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.
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.
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.
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
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
方法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
Database altered
SQL> alter tablespace gg online;
Tablespace altered
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
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
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
[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.
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
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/';
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
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'
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
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31520497/viewspace-2156844/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 修改資料檔案路徑
- oracle11g修改資料檔案路徑Oracle
- oracle 修改資料檔案路徑(四種方式)Oracle
- oracle 修改資料檔案位置（路徑）（移動）Oracle
- 【Oracle】如何修改資料檔案和日誌檔案的路徑Oracle
- 用重建控制檔案的方法修改oracle資料檔案路徑Oracle
- 非歸檔模式下的資料檔案路徑修改模式
- postgresql資料庫安裝以及修改資料檔案路徑SQL資料庫
- 修改資料庫檔名字和路徑資料庫
- CentOS修改Mariadb資料庫檔案儲存路徑CentOS資料庫
- oracle資料庫更改歸檔路徑Oracle資料庫
- 更改資料檔案路徑的方法
- 改變資料檔案的路徑
- MySQL資料庫資料檔案路徑遷移步驟MySql資料庫
- 改變mysql資料存放路徑MySql
- 修改資料檔案地址
- oracle升級後資料檔案路徑變為大寫Oracle
- oracle遷移,資料檔案路徑改變win-to-winOracle
- 更改MySQL資料檔案存放目錄位置MySql
- 關於Vue修改預設的build檔案存放的dist路徑VueUI
- 修改資料庫路徑資料庫
- oracle之 資料泵dump檔案存放nfs報ORA-27054OracleNFS
- oracle11g修改控制檔案路徑Oracle
- android 統計資料夾大小及刪除資料夾下所有檔案和路徑Android
- linux下批量修改檔案及資料夾所Linux
- oracle 資料庫lsnrctl監聽的日誌路徑和trace檔案Oracle資料庫
- oracle資料庫移動資料檔案、日誌檔案和控制檔案Oracle資料庫
- Oracle 資料檔案回收Oracle
- find 按檔案修改時間查詢檔案及find空資料夾
- C#實現把指定資料夾下的所有檔案複製到指定路徑下以及修改指定檔案的字尾名C#
- 使用RMAN複製活動資料庫（檔案路徑相同）資料庫
- 使用RMAN複製活動資料庫（檔案路徑不同）資料庫
- 物理Standby資料庫的檔案路徑轉換(1)資料庫
- 物理Standby資料庫的檔案路徑轉換(2)資料庫
- 物理Standby資料庫的檔案路徑轉換(3)資料庫
- 移動資料檔名中含有特殊字元的資料檔案方法字元
- 桌面檔案在c盤哪個資料夾 c盤桌面檔案路徑
- 修改表空間檔案路徑