How to switch local datafile to ASM storage on Oracle RAC

APPLIES TO

Oracle Database RAC

SYMPTOMS

The node02 can not query t1 table.

SQL> select * from t1;
select * from t1
              *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/db_1/dbs/tbs_demo.dbf'

CAUSE

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 +DATA/coco/system01.dbf
         2 +DATA/coco/sysaux01.dbf
         3 +DATA/coco/undotbs01.dbf
         4 +DATA/coco/users01.dbf
         5 +DATA/coco/undotbs02.dbf
         6 /u01/app/oracle/product/11.2.0/db_1/dbs/tbs_demo.dbf

6 rows selected.

The datafile was wrongly created on the local server.

SOLUTION

  1. Offline the incorrect datafile.
SQL> alter database datafile 6 offline;
alter database datafile 6 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            +DATA
Oldest online log sequence     41
Current log sequence           42
  1. Enable archive log mode.
~]$ srvctl stop database -d coco
~]$ srvctl start instance -d coco -i coco1 -o mount

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
COCO      MOUNTED

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

~]$ srvctl start instance -d coco -i coco2

SQL> alter system archive log current;

System altered.
  1. Offline the datafile again.
SQL> alter database datafile 6 offline;

Database altered.
  1. Copy the datafile to ASM.
~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 17 10:47:58 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: COCO (DBID=3033787638)

RMAN> backup as copy datafile 6 format '+DATA/coco/tbs_demo.dbf';

Starting backup at 17-APR-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 instance=coco1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/product/11.2.0/db_1/dbs/tbs_demo.dbf
output file name=+DATA/coco/tbs_demo.dbf tag=TAG20250417T104921 RECID=1 STAMP=1198666162
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 17-APR-25
  1. Rename and online the datafile.
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/tbs_demo.dbf' to '+DATA/coco/tbs_demo.dbf';

SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '+DATA/coco/tbs_demo.dbf'

SQL> alter database recover datafile 6;
alter database recover datafile 6
*
ERROR at line 1:
ORA-00279: change 2412732 generated at 04/17/2025 09:56:52 needed for thread 2
ORA-00289: suggestion :
+DATA/coco/archivelog/2025_04_17/thread_2_seq_34.271.1198665721
ORA-00280: change 2412732 for thread 2 is in sequence #34

SQL> recover datafile 6;
ORA-00275: media recovery has already been started

SQL> alter database recover cancel;

Database altered.

SQL> recover datafile 6;
ORA-00279: change 2412732 generated at 04/17/2025 09:56:52 needed for thread 2
ORA-00289: suggestion :
+DATA/coco/archivelog/2025_04_17/thread_2_seq_34.271.1198665721
ORA-00280: change 2412732 for thread 2 is in sequence #34


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/coco/archivelog/2025_04_17/thread_2_seq_34.271.1198665721
ORA-00288: to continue recovery type ALTER DATABASE RECOVER CONTINUE
ORA-00278: log file
'+DATA/coco/archivelog/2025_04_17/thread_2_seq_34.271.1198665721' no longer
needed for this recovery


ORA-00279: change 2413275 generated at 04/17/2025 10:44:21 needed for thread 2
ORA-00289: suggestion :
+DATA/coco/archivelog/2025_04_17/thread_2_seq_35.272.1198665893
ORA-00280: change 2413275 for thread 2 is in sequence #35


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/coco/archivelog/2025_04_17/thread_2_seq_35.272.1198665893
Log applied.
Media recovery complete.

SQL> alter database datafile 6 online;

Database altered.

SQL> select file#, name,status from v$datafile;

     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 +DATA/coco/system01.dbf                  SYSTEM
         2 +DATA/coco/sysaux01.dbf                  ONLINE
         3 +DATA/coco/undotbs01.dbf                 ONLINE
         4 +DATA/coco/users01.dbf                   ONLINE
         5 +DATA/coco/undotbs02.dbf                 ONLINE
         6 +DATA/coco/tbs_demo.dbf                  ONLINE

6 rows selected.

SQL> !rm -f /u01/app/oracle/product/11.2.0/db_1/dbs/tbs_demo.dbf