Question : A datafile was wrongly added on file system instead of ASM diskgroup. How can you move it to the ASM disk group?
As root:
[root@L160 ~]# mkdir -p /u02/oradata/PDEV
chown -R oracle:oinstall /u02/oradata
As oracle:
sqlplus system/sys@PDEV
drop tablespace spot including contents and datafiles;
create tablespace spot
datafile ‘/u02/oradata/PDEV/spot.dbf’ size 2m reuse
autoextend on next 10m
extent management local autoallocate
segment space management auto;
set linesize 180
col FILE_NAME for a80
select file_name, file_id from dba_data_files;
FILE_NAME FILE_ID
——————————————————————————– ———-
+DATA/CDB1/B1C2EBF73AE762C4E053A001A8C00E1D/DATAFILE/system.325.1053907731 13
+DATA/CDB1/B1C2EBF73AE762C4E053A001A8C00E1D/DATAFILE/sysaux.326.1053907731 14
+DATA/CDB1/B1C2EBF73AE762C4E053A001A8C00E1D/DATAFILE/undotbs1.324.1053907731 15
+DATA/CDB1/B1C2EBF73AE762C4E053A001A8C00E1D/DATAFILE/berean_data.328.1053909203 17
+DATA/CDB1/B1C2EBF73AE762C4E053A001A8C00E1D/DATAFILE/berean_index.329.1053909341 18
/u02/oradata/PDEV/spot.dbf 20
oops -WRONG LOCATION of datafile.
How to move datafile #20 to ASM?
Answer :
1) Make sure the datafile to be moved is OFFLINE before proceeding :
SQL> alter system switch logfile;
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> alter database datafile 20 offline;
Database altered.
SQL> select file_name, file_id, online_status from dba_data_files where file_id=20;
FILE_NAME FILE_ID ONLINE_
——————————————————————————– ———- ——-
/u02/oradata/PDEV/spot.dbf 20 RECOVER
2. Use ASMCMD to copy the file from filesystem to the diskgroup, ONLY as the owner of the grid software (grid)
oracle@L160.bitech.local(CDB1): su – grid
ASMCMD> cp /u02/oradata/PDEV/spot.dbf +DATA/CDB1/B1C2EBF73AE762C4E053A001A8C00E1D/DATAFILE/spot.dbf
copying /u02/oradata/PDEV/spot.dbf -> +DATA/CDB1/B1C2EBF73AE762C4E053A001A8C00E1D/DATAFILE/spot.dbf
Verify the movement :
ASMCMD> cd +DATA/ASM/DATAFILE
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE OCT 23 00:00:00 N spot.dbf => +DATA/ASM/DATAFILE/spot.dbf.320.1054514311
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’;
alter database rename file ‘/u02/oradata/PDEV/spot.dbf’ to ‘+DATA/CDB1/B1C2EBF73AE762C4E053A001A8C00E1D/DATAFILE/spot.dbf’;
Database altered.
4. Recover the datafile and bring it ONLINE.
SQL> alter database recover datafile 20;
Database altered.
SQL> alter database datafile 20 online;
Database altered.
5. Confirm correct name and location:
SQL> select file_name, file_id, online_status from dba_data_files where file_id=20;
FILE_NAME FILE_ID ONLINE_STATUS
———- ——- ————-
+DATA/london/datafile/test.dbf 6 ONLINE
Millions of Free Traffic with AI Tools – https://ext-opp.com/AIVault