How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command. (文档 ID 1610615.1)

最新推荐文章于 2024-09-06 14:14:41 发布

jx_jy

最新推荐文章于 2024-09-06 14:14:41 发布

阅读量198

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

73 篇文章

订阅专栏

本文详细介绍如何使用Oracle的ASM命令和SQL语句，将在线数据文件离线并更改其位置，具体步骤包括：切换日志文件，离线数据文件，使用ASMCMD复制文件，重命名数据文件，恢复并上线数据文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

NOTE: This article does not cover ALTER DATABASE MOVE DATAFILE SQL statement to rename or relocate online data files.

1) Make sure the datafile to be moved is OFFLINE

SQL> alter system switch logfile;
System altered.
SQL> select file_name, file_id from dba_data_files;
FILE_NAME FILE_ID
---------------------
/u01/oracle/oradata/test1.dbf 6

SQL> alter database datafile 6 offline;
Database altered.
SQL> select file_name, file_id, online_status from dba_data_files where file_id=6;
FILE_NAME FILE_ID ONLINE_STATUS
---------- -------
/u01/oracle/oradata/test1.dbf 6 RECOVER

2. Use ASMCMD to copy the file from filesystem to the diskgroup

ASMCMD> cp /u01/oracle/oradata/test1.dbf +DATA/LONDON/DATAFILE/test.dbf
copying /u01/oracle/oradata/test1.dbf -> +DATA/LONDON/DATAFILE/test.dbf

ASMCMD> ls -lt
Type Redund Striped Time Sys Name
N test.dbf => +DATA/ASM/DATAFILE/test.dbf.286.833718815
ASMCMD> pwd
+DATA/ASM/DATAFILE

3. Once the file is copied, rename the datafile

SQL> alter database rename file '/u01/oracle/oradata/test1.dbf' to '+DATA/LONDON/DATAFILE/test.dbf';
Database altered.

4. Recover the datafile and bring it ONLINE.

SQL> alter database recover datafile 6;
Database altered.

SQL> alter database datafile 6 online;
Database altered.

5. Confirm correct name and location:

SQL> select file_name, file_id, online_status from dba_data_files where file_id=6;
FILE_NAME FILE_ID ONLINE_STATUS
---------- -------
+DATA/london/datafile/test.dbf 6 ONLINE

NOTE: To copy datafiles from primary to standby databases on ASM it is recommended to use RMAN.

How to Copy ASM datafile From Primary Database to Standby Database on ASM using RMAN(Doc ID 605234.1)
Using RMAN Effectively In A Dataguard Environment.(Doc ID 848716.1)

References

NOTE:848716.1 - Using RMAN Effectively In A Dataguard Environment.
NOTE:605234.1 - How to Copy ASM datafile From Primary Database to Standby Database on ASM using RMAN