How to move a SYSTEM datafile from filesystem to the ASM diskgroup using ASMCMD on RAC

on
How to move a SYSTEM datafile from filesystem to the ASM diskgroup using ASMCMD on RAC

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.

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

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
[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>
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.
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
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.
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.
5. Open the DB:
SQL> alter database open;
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.
[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

======================================================================================

How to move a datafile from a file system to ASM (Doc ID 390274.1)

GOAL

This note will guide a DBA in moving a datafile currently located on file system to an ASM diskgroup.

SOLUTION

How to move a datafile from a file system to ASM



Moving a datafile from the file system can be achived in two ways.


i. While the database is shutdown (in mount stage).

ii. While the database is running (with the selected tablespace offline).

-------------------------------------------------------------------------------------------------------------------------------

i.  While the database is shutdown (in mount stage).


Moving oracle datafile while the database is in mount stage is performed in the following way:


1. Shutdown and mount the database.
[oracle@linux] sqlplus '/as sysdba'

SQL> shutdown immediate;

SQL> startup mount;

2. Ensure you have enough space in the ASM diskgroup to copy the datafile.

     First identify the size of the datafile you wish to move.
SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;

FILE#   NAME                         FILE_SIZE_MB
-----   ---------------------------- --------------
...
4       /oradata/PROD/users01.dbf    2500
...

* In this example we will be moving users01.dbf


[oracle@linux] export ORACLE_SID=+ASM

SQL> select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;

NAME                           STATE       TOTAL_MB   FREE_MB
------------------------------ ----------- ---------- ----------
DGROUP1                        MOUNTED     100        3
DGROUP2                        MOUNTED     4882       4830

3. Connect to RMAN and copy the datafile from the filesystem to the select ASM diskgroup.
[oracle@linux] rman target=/

RMAN> copy datafile 4 to '+DGROUP2';

Starting backup at 2006/09/05 12:14:23
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=31 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oradata/PROD/users01.dbf
output filename=+DGROUP2/PROD/datafile/users01.258.600351265 tag=TAG20060905T121424 recid=10 stamp=600351264
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:01
Finished backup at 2006/09/05 12:19:24

4.  Update the controlfile with the new location of the datafile.
[oracle@linux] rman target /


RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy "+DGROUP2/PROD/datafile/users01.258.600351265".
5. The file is now if the new location.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
..
+DGROUP2/PROD/datafile/users01.258.600351265
..
6. The database may now be opened.


While the database is running (with the select tablespace offline).


In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.

1. Identify the tablespace which contains the datafile and offline the tablespace.
SQL> select tablespace_name, file_name from dba_data_files where file_id=4;

TABLESPACE_NAME    FILE_NAME
------------------ ------------------------------
USERS              /oradata/RMAN/users01.dbf


SQL> alter tablespace USERS offline;

* * * * * Continue with Steps 2 - 5 above. * * * * *


6. After you have successfully completed the above steps (2 -5) place the tablespace online;
SQL> alter tablespace USERS online;

     The datafile has now been successfully moved to the ASM diskgroup.

REFERENCES

NOTE:390416.1 - How to move a datafile from ASM to the file system
NOTE:944831.1 - How to Copy Archivelog Files From ASM to Filesystem and vice versa
NOTE:345180.1 - How to duplicate a controlfile when ASM is involved
=====================================================================

 How to move a datafile from ASM to the file system (Doc ID 390416.1)

GOAL

This note will guide a DBA in moving a datafile currently located in a ASM diskgroup to a file system.

SOLUTION

How to move a datafile from ASM to a file system.


Moving a datafile from ASM to the file system can be performed in two ways:

i. While the database is shutdown (in mount stage). This is the only option if datafiles to be
 moved are from system or undo tablespaces.

ii. While the database is running (with the selected tablespace offline).

-------------------------------------------------------------------------------------------------------------------------------

i. While the database is shutdown (in mount stage).

Moving oracle datafile while the database is in mount stage is performed in the following way:

1. Shutdown and mount the database.
[oracle@linux] sqlplus '/as sysdba'

SQL> shutdown immediate;

SQL> startup mount;
2. Ensure you have enough space on the file system to copy the datafile.

First identify the size of the datafile you wish to move.
SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;

FILE#  NAME                                           FILE_SIZE_MB
-----  ---------------------------------------------  ------------
...
4      +DGROUP2/PROD/datafile/users01.258.600351265   2500
...

* In this example we will be moving the users01 datafile.


[oracle@linux] df -k

Filesystem                  1K-blocks      Used      Available   Use% Mounted on
/dev/vg01/root              10321208       3716884   6080036     38%  /
/dev/sda1                   147766         15521     124616      12%  /boot
none                        1022976        0         1022976     0%   /dev/shm
/dev/vg01/tmp               2064208        58380     1900972     3%   /tmp
/dev/vg01/u01               20158332       18557600  576732      97%  /u01
/dev/vg01/backups           6092608        5266016   517140      92%  /backups
/dev/vg01/oradata           15487600       11876492  2824676     81%  /oradata
/dev/vg01/recovery_area     15487600       11465932  3235236     78%  /recovery_area

3. Connect to RMAN and copy the datafile from the ASM diskgroup to the filesystem.
[oracle@linux] rman target=/

RMAN> copy datafile 4 to '/oradata/PROD/users01.dbf';

Starting backup at 2006/09/06 15:46:13
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DGROUP3/rman/datafile/users.258.600450259
output filename=/oradata/PROD/users01.dbf tag=TAG20060906T154614 recid=22 stamp=600450375
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2006/09/06 15:46:15

4. Update the controlfile with the new location of the datafile. one can also use  "RMAN> SWITCH DATAFILE 4 TO COPY;" command to rename the datafile.
[oracle@linux] sqlplus '/as sysdba'

SQL> alter database rename file '+DGROUP3/PROD/datafile/users.258.600450259' to '/oradata/PROD/users01.dbf';

Database altered.

5. The file is now in the new location.
SQL> select file#, name from v$datafile;

FILE#    NAME
------   ------------------------------------------------------------------
..
4        /oradata/PROD/users01.dbf
..
6. The database may now be opened.

ii. While the database is running (with the select tablespace offline).

In order to move a datafile on a running active database the datafile must be placed offline.

1. Identify the tablespace which contains the datafile and offline the datafile.
RMAN> report schema;

Report of database schema for database with db_unique_name PROD

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** +DATA/prod/datafile/system.260.752096007
2 719 SYSAUX *** +DATA/prod/datafile/sysaux.261.752096045
3 100 UNDOTBS1 *** +DATA/prod/datafile/undotbs1.262.762299241
4 211 USERS *** +DATA/prod/datafile/users.264.752096117


SQL> alter database datafile 4 offline;


* * * * * Continue with Steps 2 - 5 above. * * * * *


6. After you have successfully completed the above steps (2 -5) place the datafile online ;
Note:

 IF TABLESPACE is OFFLINED, all related datafiles for this Tablespace are checkpointed and offlined
 No recovery needed when setting tablespace / datafiles Online

 If OFFLINE a 'datafile' (not the tablespace) when database is 'open', the datafile is not checkpointed, so it
 will be required to do recovery for the datafile before it can successful set to ONLINE.


6.1 Steps if TABLESPACE was OFFLINED
   
        SQL> alter database datafile 4 online;


6.2 Steps if DATAFILE is OFFLINED - and not the tablespace

  SQL> recover automatic datafile 4 ; 
  SQL> alter database datafile 4 ONLINE ;


# or use RMAN, as rman will implicit restore archive logs if not available on disk, and if needed for recovery
  RMAN> recover datafile 4 ;
  RMAN> sql "alter database datafile 4 ONLINE" ;

The datafile has now been successfully moved to the file system .
Finally check as well that the related Tablespace is ONLINE as well.
.



REFERENCES

NOTE:390274.1 - How to move a datafile from a file system to ASM
NOTE:345180.1 - How to duplicate a controlfile when ASM is involved

 =====================================================================

 Note---> This informationmation taken from oracle metalink. all copy rights oracle only.

Comments

Post a Comment

Oracle DBA Information