How to move a datafile that was added by mistake on local storage to shared location
In Real Application Cluster (RAC) environments the datafiles need to be on the shared storage. It is possible that a datafile gets added to a tablespace on the local filesystem instead of the shared storage subsystem by mistake.
When another instance tries to contact the local file it will error out with:
ORA-01157: cannot identify/lock data file 10
ORA-01110: data file 10: '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/DATA'
Typically this happens when the datafile needed to be added to ASM but the '+'-sign is omited when specifying the diskgroup. In this case the datafile will be created in the default directory specified by the 'db_create_file_dest' parameter which defaults to $ORACLE_HOME/dbs.
This article explains how you can resolve this issue when the database is in archivelog mode and when the database is running in noarchivelog mode.
SOLUTION
A. When the database is running in archivelog mode
Note: You need to have all the archive files since the creation of the datafile (when it was added to the tablespace)
1. Find out the exact file name, file location, size and file number:
SQL> select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name = '<tablespace_name>';
FILE_ID FILE_NAME BYTES ONLINE_STATUS
------- -------------------------------------------------- ---------- -------------
8 +DATA/V11R2/datafile/data.258.832695063 10485760 ONLINE
10 /u01/app/oracle/product/12.1.0/dbhome_1/dbs/DATA 10485760 ONLINE <<---
2. Put the datafile offline
SQL> alter database datafile 10 offline;
3. Recreate the datafile on the shared storage, please note that you need to do this on the node where the physical file resides and you need to specify the size retrieved in step 1
SQL> alter database create datafile '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/DATA' as '+DATA' size 10485760;
4. Recover the datafile
SQL> recover datafile 10;
5. Place the datafile back online
SQL> alter database datafile 10 online;
Note: -
In some specific or particular case the end-user might copy the physical file by mistake again to another physical location on the same or another node. When perfoming the solution there is no risk in data lost between the different versions of the physical files because the 'alter database create datafile ', always recovers starting from the online redolog that was current at the moment the datafile was created originally, so it doesn't matter what happend with the datafile in between as long as we have all the archive logs since the datafile creation.
B. When the database is running in NOarchivelog mode
1. Find out the exact file name, file location, size and file number and stop the instance on the node where you added the file.
SQL> select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name = '<tablespace_name>';
FILE_ID FILE_NAME BYTES ONLINE_STATUS
-------- ------------------------------------------------- --------- -------------
8 +DATA/V11R2/datafile/data.258.832695063 10485760 ONLINE
10 /u01/app/oracle/product/12.1.0/dbhome_1/dbs/DATA 10485760 ONLINE <---
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2. Move the datafile to the shared storage
2.1 using ASMCMD cp command (11g and above from Grid Infrastructure home user environment setting ORACLE_SID to ASM instance etc)
# asmcmd
ASMCMD> cd <diskgroup>/<dbname>/DATAFILE
example: DATA/V11R2/DATAFILE
ASMCMD> cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/DATA +DATA/V11R2/DATAFILE/test02.dbf
ASMCMD> ls -l
Type Redund Striped Time Sys Name
N test02.dbf => +DATA/ASM/DATAFILE/DATA.340.849266075 <<---
DATAFILE UNPROT COARSE 10-NOV-21 10:00:00 Y SYSAUX.261.818315029
--OR--
2.2 Using RMAN:
RMAN> copy datafile '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/DATA' to '+DATA';
Starting backup at 10-NOV-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 instance=V11R2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA
output file name=+DATA/V11R2/DATAFILE/DATA.340.849266075 tag=TAG20140106T132548 RECID=1 STAMP=836141162
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 10-NOV-21
3. Mount the database:
SQL> startup mount;
ORACLE instance started.
...
Database mounted.
4. Rename the datafile:
SQL> alter database rename file '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/DATA' to '+DATA/V11R2/DATAFILE/test02.dbf';
5. Open the DB:
SQL> alter database open;
Database altered.
6. You still need to restart the other instances as well otherwise you will still get errors when the instance is trying to access the non-existing file
------
ACCIDENTALLY data file is added without “+” sign
ORA-01157: cannot identify/lock data file 3129 - see DBWR trace file
ORA-01110: data file 3129: '/home/oracle/product/10.2.0/db/dbs/DATA'
ORA-27037: unable to obtain file status
ORA-01186: file 3129 failed verification tests
ORA-01157: cannot identify/lock data file 3129 - see DBWR trace file
ORA-01110: data file 3129: '/home/oracle/product/10.2.0/db/dbs/DATA'
File 3129 not verified due to error ORA-01157Solution
1. Find out the data file number( It is available in the alert log)
2. use the rman to copy the data to ASM disk group.
3. offline the data file
4. copy the data file to the ASM disk group
5. switch the data file from old name to new COPY file
6. Recover the new data file
7. make data file on line.
- -bash-3.00$ rman target /
- RMAN> sql 'alter database datafile 3129 offline';
- RMAN> copy datafile '/home/oracle/product/10.2.0/db/dbs/DATA' to '+DATA';
- RMAN> switch datafile '/home/oracle/product/10.2.0/db/dbs/DATA' TO COPY;
- RMAN> recover datafile '+DATA/datafile/ymedia_cookie_tdata_200907.4732.705240801';
- RMAN> sql 'alter database datafile 3129 online';
No comments:
Post a Comment