修改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
- CentOS修改Mariadb資料庫檔案儲存路徑CentOS資料庫
- 更改資料檔案路徑的方法
- oracle升級後資料檔案路徑變為大寫Oracle
- oracle11g修改控制檔案路徑Oracle
- Oracle 資料檔案回收Oracle
- oracle 資料庫lsnrctl監聽的日誌路徑和trace檔案Oracle資料庫
- Python科研武器庫 - 檔案/路徑操作 - 判斷檔案或資料夾Python
- [重慶思莊每日技術分享]-ORACLE DG物理備庫使用別名資料檔案改變路徑到OMF路徑Oracle
- 利用Python快速批量修改資料集檔名Python
- 網站檔案修改資料庫，安全高效地修改網站資料庫中的檔案資訊網站資料庫
- Oracle資料檔案和臨時檔案的管理Oracle
- 桌面檔案在c盤哪個資料夾 c盤桌面檔案路徑
- nodejs遞迴資料夾獲取所有檔案路徑NodeJS遞迴
- oracle資料庫的配置檔案Oracle資料庫
- oracle 線上rename資料檔案Oracle
- win10 onenote如何修改檔案路徑_win10 onenote怎麼修改檔案路徑Win10
- 【kingsql分享】使用BBED修改Oracle資料檔案頭推進SCNSQLOracle
- 修改檔案、資料夾許可權
- 使用NID修改Oracle資料庫名Oracle資料庫
- Oracle 之利用BBED修改資料塊SCN----沒有備份資料檔案的資料恢復Oracle資料恢復
- PbootCMS後臺檔案修改路徑位置boot
- oracle徹底刪除資料檔案Oracle
- oracle uncatalog資料庫備份檔案Oracle資料庫
- mysql之 表資料存放路徑非datadir目錄MySql
- BBED 修改oracle 資料檔案的 SCN 號來做資料庫不完全恢復。Oracle資料庫
- 織夢資料庫配置檔案修改資料庫配置方法資料庫
- python實現圖書管理系統——通過excel檔案或者TXT檔案存放資料PythonExcel
- oracle dg庫資料檔案空間不足Oracle
- Oracle10g刪除資料檔案Oracle
- 修改資料檔案的位置的兩種方法
- dedecms資料庫配置檔案所在路徑為:/data/common.inc.php資料庫PHP
- css檔案與資源路徑相關CSS
- 檔案與資料
- win10系統設定索引檔案存放路徑的方法Win10索引