Datafiles are mistakenly built into the local file system for processing in the RAC environment
The RAC environment has been opened for archiving
Move the local datafile by created to the shared Disk Group
1) user add datafile for RAC database in the local node.
Node1:vastdata3 SQL> create tablespace asd datafile'/home/oracle/asd01.dbf' size 10M autoextend on; Tablespace created. SQL> col TABLESPACE_NAME for a20 SQL> col FILE_NAME for a50 SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files; FILE_NAME FILE_ID ONLINE_STATUS TABLESPACE_NAME ---------------------- ------ ---- --------- -------------------- +DATA/prod/datafile/undotbs1.258.1001122667 3 ONLINE UNDOTBS1 +DATA/prod/datafile/users.259.1001073567 4 ONLINE USERS +DATA/prod/datafile/sysaux.257.1001073565 2 ONLINE SYSAUX +DATA/prod/datafile/system.256.1001073565 1 SYSTEM SYSTEM +DATA/prod/datafile/example.264.1001073679 5 ONLINE EXAMPLE +DATA/prod/datafile/undotbs2.265.1001073909 6 ONLINE UNDOTBS2 /home/oracle/asd01.dbf 7 ONLINE ASD 7 rows selected.
2) Node 2 finds an error while viewing the DBA_DATA_FILES data file.
Node2:vastdata4 SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files; ERROR: ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: '/home/oracle/asd01.dbf' no rows selected
3) (1)Use RMAN to move the created datafile to the shared Disk Group;
Node1:vastdata3 SQL> alter database datafile 7 offline; Database altered. RMAN> copy datafile '/home/oracle/asd01.dbf' to '+DATA'; Starting backup at 2019-02-28 03:13:57 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=51 instance=PROD1 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/home/oracle/asd01.dbf output file name=+DATA/prod/datafile/asd.269.1001387639 tag=TAG20190228T031358 RECID=2 STAMP=1001387638 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 2019-02-28 03:13:59 RMAN> switch datafile '/home/oracle/asd01.dbf' to copy; datafile 7 switched to datafile copy "+DATA/prod/datafile/asd.269.1001387639"
(2)Move the created datafile in ASMCMD mode by copying.
4) Recover the datafile 7 And let it online.
Node1:vastdata3 SQL> alter database datafile 7 online; alter database datafile 7 online * ERROR at line 1: ORA-01113: file 7 needs media recovery ORA-01110: data file 7: '+DATA/prod/datafile/asd.269.1001387639' SQL> recover datafile 7; Media recovery complete. SQL> alter database datafile 7 online; Database altered. Node1:vastdata3 SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files; FILE_NAME FILE_ID ONLINE_STATUS TABLESPACE_NAME ---------------------- ------ ---- --------- -------------------- +DATA/prod/datafile/undotbs1.258.1001122667 3 ONLINE UNDOTBS1 +DATA/prod/datafile/users.259.1001073567 4 ONLINE USERS +DATA/prod/datafile/sysaux.257.1001073565 2 ONLINE SYSAUX +DATA/prod/datafile/system.256.1001073565 1 SYSTEM SYSTEM +DATA/prod/datafile/example.264.1001073679 5 ONLINE EXAMPLE +DATA/prod/datafile/undotbs2.265.1001073909 6 ONLINE UNDOTBS2 +DATA/prod/datafile/asd.269.1001387639 7 ONLINE ASD 7 rows selected. [oracle@vastdata3 ~]$ rm asd01.dbf Node2:vastdata4 SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files; FILE_NAME FILE_ID ONLINE_STATUS TABLESPACE_NAME ---------------------- ------ ---- --------- -------------------- +DATA/prod/datafile/undotbs1.258.1001122667 3 ONLINE UNDOTBS1 +DATA/prod/datafile/users.259.1001073567 4 ONLINE USERS +DATA/prod/datafile/sysaux.257.1001073565 2 ONLINE SYSAUX +DATA/prod/datafile/system.256.1001073565 1 SYSTEM SYSTEM +DATA/prod/datafile/example.264.1001073679 5 ONLINE EXAMPLE +DATA/prod/datafile/undotbs2.265.1001073909 6 ONLINE UNDOTBS2 +DATA/prod/datafile/asd.269.1001387639 7 ONLINE ASD 7 rows selected.
2) Move the datafile of shared Disk Group to the local file system.
Node1:vastdata3
1) user add datafile for RAC database in the shared disk group.
SQL> create tablespace asd datafile'+DATA' size 10M autoextend on; Tablespace created. SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files; FILE_NAME FILE_ID ONLINE_STATUS TABLESPACE_NAME ------------------------------------- ---------- -------------- --- +DATA/prod/datafile/undotbs1.258.1001122667 3 ONLINE UNDOTBS1 +DATA/prod/datafile/users.259.1001073567 4 ONLINE USERS +DATA/prod/datafile/sysaux.257.1001073565 2 ONLINE SYSAUX +DATA/prod/datafile/system.256.1001073565 1 SYSTEM SYSTEM +DATA/prod/datafile/example.264.1001073679 5 ONLINE EXAMPLE +DATA/prod/datafile/undotbs2.265.1001073909 6 ONLINE UNDOTBS2 +DATA/prod/datafile/asd. 269.1001415229 7 ONLINE ASD +DATA/prod/datafile/zxc 8 SYSTEM SYSTEM
2) Copy the datafile to the local file system in ASMCMD mode
SQL> alter database datafile 7 offline; Database altered. ASMCMD> pwd +DATA/PROD/DATAFILE ASMCMD> ls ASD.269.1001415229 ASMCMD> cp ASD.269.1001415229 /tmp/asd01.dbf copying +DATA/PROD/DATAFILE/ASD.269.1001415229 -> /tmp/asd01.dbf Destinations in cp allow at most one level of directory structure
3) Change the datafile path And Recovery the datafile
SQL> alter database rename file '+DATA/PROD/DATAFILE/ASD.269.1001415229' to '/tmp/asd01.dbf'; Database altered. SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files; FILE_NAME FILE_ID ONLINE_STATUS TABLESPACE_NAME -------------------------------------------------- ---------- -------------- -------------------- +DATA/prod/datafile/undotbs1.258.1001122667 3 ONLINE UNDOTBS1 +DATA/prod/datafile/users.259.1001073567 4 ONLINE USERS +DATA/prod/datafile/sysaux.257.1001073565 2 ONLINE SYSAUX +DATA/prod/datafile/system.256.1001073565 1 SYSTEM SYSTEM +DATA/prod/datafile/example.264.1001073679 5 ONLINE EXAMPLE +DATA/prod/datafile/undotbs2.265.1001073909 6 ONLINE UNDOTBS2 /tmp/asd01.dbf 7 RECOVER ASD +DATA/prod/datafile/zxc 8 SYSTEM SYSTEM 8 rows selected. SQL> recover datafile 7; ORA-00283: recovery session canceled due to errors ORA-01114: IO error writing block to file 7 (block # 1) ORA-01110: data file 7: '/tmp/asd01.dbf' ORA-27091: unable to queue I/O ORA-27041: unable to open file Linux-x86_64 Error: 13: Permission denied Additional information: 3 [root@vastdata3 ~]# ll /tmp/asd01.dbf -rw-r----- 1 grid oinstall 10493952 Feb 28 11:05 /tmp/asd01.dbf [root@vastdata3 ~]# chown oracle:oinstall /tmp/asd01.dbf SQL> recover datafile 7; Media recovery complete. SQL> alter database datafile 7 online; Database altered.
