How to move a SYSTEM datafile from filesystem to the ASM diskgroup using ASMCMD on RAC.
(Doc ID 1607292.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.1 [Release 10.2]
Information in this document applies to any platform.
SYMPTOMS
Customer by mistake added a datafile to the SYSTEM tablespace without a + sign, command went through and datafile shows only on one node under $ORACLE_HOME/dbs default location. Since datafile is not showing on the other node, we get messages ORA-01157: cannot identify/lock data file
Need to move SYSTEM datafile from filesystem to the ASM diskgroup.
Need to move SYSTEM datafile from filesystem to the ASM diskgroup.
CAUSE
Datafile is created on the filesystem instead of an asm diskgroup.
[oracle@mbrac2 ~]$ echo $ORACLE_SID
ORCL2
File exists on one node but not on the other:
[oracle@mbrac2 dbs]$ ls -ltr
total 524848
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 1536 Nov 28 16:12 orapwORCL2
-rw-r-----. 1 oracle oinstall 35 Nov 28 16:13 initORCL2.ora
-rw-rw----. 1 oracle dba 1544 Dec 3 15:23 hc_ORCL2.dat
-rw-r-----. 1 oracle dba 536879104 Dec 10 17:56 DATA ==============<<<
[oracle@mbrac1 dbs]$ ls -ltr
total 32
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 1536 Nov 28 16:12 orapwORCL1
-rw-r-----. 1 oracle oinstall 35 Nov 28 16:13 initORCL1.ora
-rw-rw----. 1 oracle dba 1544 Dec 3 15:23 hc_ORCL1.dat
ORCL2
File exists on one node but not on the other:
[oracle@mbrac2 dbs]$ ls -ltr
total 524848
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 1536 Nov 28 16:12 orapwORCL2
-rw-r-----. 1 oracle oinstall 35 Nov 28 16:13 initORCL2.ora
-rw-rw----. 1 oracle dba 1544 Dec 3 15:23 hc_ORCL2.dat
-rw-r-----. 1 oracle dba 536879104 Dec 10 17:56 DATA ==============<<<
[oracle@mbrac1 dbs]$ ls -ltr
total 32
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 1536 Nov 28 16:12 orapwORCL1
-rw-r-----. 1 oracle oinstall 35 Nov 28 16:13 initORCL1.ora
-rw-rw----. 1 oracle dba 1544 Dec 3 15:23 hc_ORCL1.dat
SOLUTION
1. Stop DB.
2. Move the datafile using asmcmd.
3. Mount the DB.
4. Rename the datafile.
5. Open the DB.
6. On other RAC nodes you still need to bounce the database because it is SYSTEM tablespace, otherwise you will keep getting errors ORA-01516 or original error ORA-01157: cannot identify/lock data file.
==================
1. Stop DB.
With System tablespace you have to stop DB first otherwise will get errors ORA-01541: system tablespace cannot be brought offline; shut down if necessary
2. Move the datafile using asmcmd.
3. Mount the DB.
4. Rename the datafile.
5. Open the DB.
6. On other RAC nodes you still need to bounce the database because it is SYSTEM tablespace, otherwise you will keep getting errors ORA-01516 or original error ORA-01157: cannot identify/lock data file.
==================
1. Stop DB.
With System tablespace you have to stop DB first otherwise will get errors ORA-01541: system tablespace cannot be brought offline; shut down if necessary
[oracle@mbrac1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
SQL> select TABLESPACE_NAME, file_name, file_id, online_status from dba_data_files where TABLESPACE_NAME='SYSTEM';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID ONLINE_
---------- -------
SYSTEM
+DATA/orcl/datafile/system.256.832695063
1 SYSTEM
SYSTEM
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA
6 SYSTEM
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
SQL> select TABLESPACE_NAME, file_name, file_id, online_status from dba_data_files where TABLESPACE_NAME='SYSTEM';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID ONLINE_
---------- -------
SYSTEM
+DATA/orcl/datafile/system.256.832695063
1 SYSTEM
SYSTEM
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA
6 SYSTEM
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
No errors in alert.log
2. Move the datafile using ASCMD cp command. Notice an alias link which is normal since it is not system assigned alias.
2. Move the datafile using ASCMD cp command. Notice an alias link which is normal since it is not system assigned alias.
ASMCMD> cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA +DATA/ORCL/DATAFILE/SYSTEM2
copying /u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA -> +DATA/ORCL/DATAFILE/SYSTEM2
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y USERS.259.832695063
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y UNDOTBS2.264.832695177
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y UNDOTBS1.258.832695063
N SYSTEM2 => +DATA/ASM/DATAFILE/SYSTEM2.268.833825159
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y SYSTEM.256.832695063
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y SYSAUX.257.832695063
copying /u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA -> +DATA/ORCL/DATAFILE/SYSTEM2
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y USERS.259.832695063
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y UNDOTBS2.264.832695177
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y UNDOTBS1.258.832695063
N SYSTEM2 => +DATA/ASM/DATAFILE/SYSTEM2.268.833825159
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y SYSTEM.256.832695063
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y SYSAUX.257.832695063
3. Mount the database:
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 805310440 bytes
Database Buffers 436207616 bytes
Redo Buffers 8892416 bytes
Database mounted.
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 805310440 bytes
Database Buffers 436207616 bytes
Redo Buffers 8892416 bytes
Database mounted.
4. Rename the datafile:
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA' to '+DATA/ORCL/DATAFILE/SYSTEM2';
Database altered.
Database altered.
5. Open the DB:
SQL> alter database open;
Database altered.
Database altered.
No errors in alert.log.
6. On the other RAC node you still need to bounce the database otherwise you will keep getting errors ORA-01516 or original error ORA-01157: cannot identify/lock data file.
Compare dba_data_files and v$instance to make sure entries are correct for system tablespace datafiles on all RAC nodes.
6. On the other RAC node you still need to bounce the database otherwise you will keep getting errors ORA-01516 or original error ORA-01157: cannot identify/lock data file.
Compare dba_data_files and v$instance to make sure entries are correct for system tablespace datafiles on all RAC nodes.
[oracle@mbrac1 dbs]$ echo $ORACLE_SID
ORCL1
[oracle@mbrac1 dbs]$
[oracle@mbrac1 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 10 19:00:47 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 872419304 bytes
Database Buffers 369098752 bytes
Redo Buffers 8892416 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select TABLESPACE_NAME, file_name, file_id from dba_data_files where TABLESPACE_NAME='SYSTEM';
TABLESPACE_NAME FILE_NAME FILE_ID
--------------------------------------------------------------------------------
SYSTEM
+DATA/orcl/datafile/system.256.832695063
1
SYSTEM
+DATA/orcl/datafile/system2
6
SQL> select INSTANCE_NUMBER, INSTANCE_NAME from v$instance ;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
1 ORCL1
Then on the second node:
SQL> select TABLESPACE_NAME, file_name, file_id, online_status from dba_data_files where TABLESPACE_NAME='SYSTEM';
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_
--------------------------------------------------------------------------------
SYSTEM +DATA/orcl/datafile/system.256.832695063
1 SYSTEM
SYSTEM +DATA/orcl/datafile/system2
6 SYSTEM
SQL> select INSTANCE_NUMBER, INSTANCE_NAME from v$instance ;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
2 ORCL2
ORCL1
[oracle@mbrac1 dbs]$
[oracle@mbrac1 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 10 19:00:47 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 872419304 bytes
Database Buffers 369098752 bytes
Redo Buffers 8892416 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select TABLESPACE_NAME, file_name, file_id from dba_data_files where TABLESPACE_NAME='SYSTEM';
TABLESPACE_NAME FILE_NAME FILE_ID
--------------------------------------------------------------------------------
SYSTEM
+DATA/orcl/datafile/system.256.832695063
1
SYSTEM
+DATA/orcl/datafile/system2
6
SQL> select INSTANCE_NUMBER, INSTANCE_NAME from v$instance ;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
1 ORCL1
Then on the second node:
SQL> select TABLESPACE_NAME, file_name, file_id, online_status from dba_data_files where TABLESPACE_NAME='SYSTEM';
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_
--------------------------------------------------------------------------------
SYSTEM +DATA/orcl/datafile/system.256.832695063
1 SYSTEM
SYSTEM +DATA/orcl/datafile/system2
6 SYSTEM
SQL> select INSTANCE_NUMBER, INSTANCE_NAME from v$instance ;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
2 ORCL2
=============================================================================================
RAC: How to move a datafile that was added by mistake on local storage to shared location (Doc ID 1678747.1)
RAC: How to move a datafile that was added by mistake on local storage to shared location (Doc ID 1678747.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 and laterInformation in this document applies to any platform.
GOAL
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/11.2.0/dbhome_1/dbs/DATA'
ORA-01110: data file 10: '/u01/app/oracle/product/11.2.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 = '';
FILE_ID FILE_NAME BYTES ONLINE_STATUS
------- -------------------------------------------------- ---------- -------------
8 +DATA/V11R2/datafile/data.258.832695063 10485760 ONLINE
10 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA 10485760 ONLINE <<--- nbsp="" p="">
------- -------------------------------------------------- ---------- -------------
8 +DATA/V11R2/datafile/data.258.832695063 10485760 ONLINE
10 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA 10485760 ONLINE <<--- nbsp="" p="">
2. Put the datafile offline
SQL> alter 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/11.2.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 = '';
FILE_ID FILE_NAME BYTES ONLINE_STATUS
-------- ------------------------------------------------- --------- -------------
8 +DATA/V11R2/datafile/data.258.832695063 10485760 ONLINE
10 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA 10485760 ONLINE <--- p="">
-------- ------------------------------------------------- --------- -------------
8 +DATA/V11R2/datafile/data.258.832695063 10485760 ONLINE
10 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA 10485760 ONLINE <--- p="">
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@V11R2i1> startup mount
ORACLE instance started.
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@V11R2i1> startup mount
ORACLE instance started.
2. Move the datafile to the shared storage
2.1 using ASMCMD cp command (11g and above)
# asmcmd
ASMCMD> cd//DATAFILE
example: DATA/V11R2/DATAFILE
ASMCMD> cp /u01/app/oracle/product/11.2.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 <<--- br="">DATAFILE UNPROT COARSE JUN 03 10:00:00 Y SYSAUX.261.818315029
...
ASMCMD> cd
example: DATA/V11R2/DATAFILE
ASMCMD> cp /u01/app/oracle/product/11.2.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 <<--- br="">DATAFILE UNPROT COARSE JUN 03 10:00:00 Y SYSAUX.261.818315029
...
--OR--
2.2 Using RMAN:
RMAN> copy datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA' to '+DATA';
Starting backup at 06-JAN-14
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 06-JAN-14
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 06-JAN-14
3. Mount the database:
SQL> startup mount;
ORACLE instance started.
...
Database mounted.
...
Database mounted.
4. Rename the datafile:
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA' to '+DATA/V11R2/DATAFILE/';
5. Open the DB:
SQL> alter database open;
Database altered.
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
=======================================================================
Comments
Post a Comment
Oracle DBA Information