【Oracle】How to Rename or Move Datafiles and Logfiles 之二
在資料庫open狀態下進行重新命名或者遷移資料檔案:
注意:遷移system 表空間裡的資料檔案或者redo log 必須在mount狀態下進行操作。
在資料庫open 模式進行資料檔案的移動時,資料檔案所在的表空間必須是隻讀的。使用者只可以進行select 查詢操作而不能進行DML 修改。此期間持續的時間由遷移的資料檔案大小和copy到新的目錄的時間決定!
具體步驟如下:
1 確定有多少資料檔案要進行遷移, 確保所有檔案的status 為 AVAILABLE
SYS@yangdb-rac3> col file_name for a50
SYS@yangdb-rac3> SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';
FILE_NAME STATUS
-------------------------------------------------- ---------
/opt/oracle/oradata/yangdb_test/users01.dbf AVAILABLE
2設定表空間為只讀,確保表空間已經在資料庫字典裡面設定為只讀
SYS@yangdb-rac3> alter tablespace users read only;
Tablespace altered.
SYS@yangdb-rac3> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';
TABLESPACE_NAME STATUS
------------------------------ ---------
USERS READ ONLY
3 移動資料檔案到新的位置
oracle@rac3:/opt/oracle/oradata/yangdb_test>ls
control01.ctl control02.ctl control03.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_test>cp users01.dbf ../yangdb/users01.dbf
oracle@rac3:/opt/oracle/oradata/yangdb_test>cd ../yangdb
oracle@rac3:/opt/oracle/oradata/yangdb>ls
users01.dbf
4 當資料檔案已經被遷移到新的位置,將表空間offline
SYS@yangdb-rac3> ALTER TABLESPACE USERS OFFLINE;
Tablespace altered.
查詢表空間的status 資訊
SYS@yangdb-rac3> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';
TABLESPACE_NAME STATUS
------------------------------ ---------
USERS READ ONLY
資料檔案的status
SYS@yangdb-rac3> SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';
FILE_NAME STATUS
-------------------------------------------------- ---------
/opt/oracle/oradata/yangdb_test/users01.dbf AVAILABLE
SYS@yangdb-rac3> select name,status from v$datafile;
NAME STATUS
-------------------------------------------------- -------
/opt/oracle/oradata/yangdb_test/system01.dbf SYSTEM
/opt/oracle/oradata/yangdb_test/sysaux01.dbf ONLINE
/opt/oracle/oradata/yangdb_test/undotbs01.dbf ONLINE
/opt/oracle/oradata/yangdb_test/users01.dbf OFFLINE
/opt/oracle/oradata/yangdb_test/example01.dbf ONLINE
5 重新命名資料檔案,此步驟將修改控制檔案裡的相關資訊
SYS@yangdb-rac3> alter database rename file '/opt/oracle/oradata/yangdb_test/users01.dbf' to '/opt/oracle/oradata/yangdb/users01.dbf';
Database altered.
6 將資料檔案重新命名後,將表空間設定為online,並且將其設定為read write
SYS@yangdb-rac3> ALTER TABLESPACE USERS ONLINE;
Tablespace altered.
SYS@yangdb-rac3> select name,status from v$datafile;
NAME STATUS
-------------------------------------------------- -------
/opt/oracle/oradata/yangdb_test/system01.dbf SYSTEM
/opt/oracle/oradata/yangdb_test/sysaux01.dbf ONLINE
/opt/oracle/oradata/yangdb_test/undotbs01.dbf ONLINE
/opt/oracle/oradata/yangdb/users01.dbf ONLINE
/opt/oracle/oradata/yangdb_test/example01.dbf ONLINE
SYS@yangdb-rac3> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';
TABLESPACE_NAME STATUS
------------------------------ ---------
USERS READ ONLY
SYS@yangdb-rac3> ALTER TABLESPACE USERS READ WRITE;
Tablespace altered.
SYS@yangdb-rac3> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';
TABLESPACE_NAME STATUS
------------------------------ ---------
USERS ONLINE
SYS@yangdb-rac3> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';
TABLESPACE_NAME STATUS
------------------------------ ---------
USERS ONLINE
7 測試結果,查詢users 表空間中的資料:
SYS@yangdb-rac3> SELECT COUNT(*) FROM SCOTT.EMP;
COUNT(*)
----------
14
成功!
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22664653/viewspace-708992/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【Oracle】How to Rename or Move Datafiles and Logfiles 之一Oracle
- Move_or_Rename_the_Tempfile_in_OracleOracle
- How To Move Datafiles On AIX Using Raw Logical Volumes To A New Location?AI
- How to rename an Oracle stored procedureOracle
- How to Rename Tablespace In Oracle10gOracle
- How does one rename a database?Database
- How to Rename a Server That Is Not a Data Store ServerServer
- How to move progress database to different OSDatabase
- How To Move The DB Audit Trails To A New TablespaceAI
- How to Move or Copy a Tablespace to Another Database (61)Database
- how to move a MediaWiki wiki from one server to anotherServer
- Rename or Move a datafile In Oracle 19c RAC-20220117Oracle
- How to move ASM database files from one diskgroup to anotherASMDatabase
- How to move Oracle Spatial objects from SYSAUX tablespace to a user defined tablespace [ID 1119758.1OracleObjectUX
- How to Rename Database/Change DB_NAME or ORACLE_SID/Instance Name-15390.1DatabaseOracle
- How to Move/Recreate GI Management Repository to Different Shared Storage
- How to move ASM spfile to a different disk group [ID 1082943.1]ASM
- Oracle的move操作Oracle
- move oracle 10 directoryOracle
- Rename db_name for OracleOracle
- OMF (Oracle Managed Files) Usage for Datafiles [ID 137482.1]Oracle
- ORACLE MOVE表空間Oracle
- Overview of Datafiles (62)View
- How to Move a Database Using Transportable Tablespaces (文件 ID 1493809.1)Database
- oracle 線上修改online redo logfiles size 大小Oracle
- oracle 線上rename資料檔案Oracle
- oracle rename 的連帶問題Oracle
- Rename Tablespace in Oracle database 10gOracleDatabase
- How to Move Tablespaces Across Platforms Using Transportable Tablespaces With RMAN [ID 371556.1]ROSPlatform
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- How Oracle Works!Oracle
- How to Study OracleOracle
- [轉移]ORACLE MOVE 表空間Oracle
- [Oracle] Shrink space & Table move比較Oracle
- alter system check datafiles 命令
- How to enable trace in OracleOracle
- In Oracle,How to use dumpOracle
- An Example of How Oracle WorksOracle