Wednesday, March 1, 2017

How to move ASM database files from one diskgroup to another

Below are the steps to move ASM datafiles from one DF to another :-
1) Identify the data file to be moved.
2) Identify the diskgroup on to which the file has to be moved.
3) Take the file offline.
4) Copy the file to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.
5) Rename the file to point to new location.
6) Recover the file.
7) Bring the file online.
8) Verify the new file locations.
9) Delete the file from its original location.
Note :-
1. The steps provided below assume that the database is open and in Archivelog mode.
2. Make sure you run RMAN crosscheck copy and delete expired copy to update the controlfile and catalog for your backups to run without issues.
1) Identify the data file to be moved.
—————————————-
In database instance
poc_db> select name from v$datafile;
NAME
——————————————————————————–
+DATA/poc_db/datafile/system.261.925641905
+DATA/poc_db/datafile/sysaux.260.925641905
+DATA/poc_db/datafile/undo_t01.259.925641907
+DATA/poc_db/datafile/users.264.925642655 >>Move this to +DATA1
2) Identify the diskgroup on to which the file has to be moved.
————————————————————–
In ASM instance
SQL> select group_number, name,compatibility, database_compatibility from v$asm_diskgroup;
GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY
———— —————————— —————————— —————————————-
1 DATA1 12.1.0.2.0 10.1.0.0.0
2 DATA 12.1.0.2.0 10.1.0.0.0
3) Take the file offline.
————————–
poc_db> ALTER DATABASE DATAFILE ‘+DATA/poc_db/datafile/users.264.925642655’ offline;
Database altered.
4)Now Copy the file from Source diskgroup DATA to target Diskgroup DATA1
poc_db$ rman target /
Recovery Manager: Release 11.2.0.4.0 – Production on Mon Oct 24 12:10:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: poc_db (DBID=1207787839)
RMAN> COPY DATAFILE ‘+DATA/poc_db/datafile/users.264.925642655’ to ‘+DATA1’;
Starting backup at 24-OCT-2016
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=294 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/poc_db/datafile/users.264.925642655
output file name=+DATA1/poc_db/datafile/users.259.926079061 tag=TAG20161024T121101 RECID=1 STAMP=926079062
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 24-OCT-2016
5) Rename the file to point to new location. 
——————————————-
RMAN> run
{
set newname for datafile ‘+DATA/poc_db/datafile/users.264.925642655’ to ‘+DATA1/poc_db/datafile/users.259.926079061’ ;
switch datafile all;
}
executing command: SET NEWNAME
datafile 4 switched to datafile copy
input datafile copy RECID=1 STAMP=926079062 file name=+DATA1/poc_db/datafile/users.259.926079061
6) Recover the file.
——————-
poc_db> RECOVER DATAFILE ‘+DATA1/poc_db/datafile/users.259.926079061’;
Media recovery complete.
7) Bring the file online.
———————–
poc_db> alter database datafile ‘+DATA1/poc_db/datafile/users.259.926079061’ online;
Database altered.
8) Verify the new file location.
———————————
poc_db> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
——————————————————————————–
+DATA/poc_db/datafile/system.261.925641905
+DATA/poc_db/datafile/sysaux.260.925641905
+DATA/poc_db/datafile/undo_t01.259.925641907
+DATA1/poc_db/datafile/users.259.926079061
9) Delete the file from its original location either per SQLPLUS or per ASMCMD:
——————————————————————-
Automatic Storage Management files do not need to be manually deleted because, as Oracle managed files, they are removed automatically when they are no longer needed.
However, if you need to drop an Oracle Managed File (OMF) manually you should use the fully qualified filename if you reference the file. Otherwise you will get an error (e.g. ORA-15177).
poc_db> ALTER DISKGROUP DATA DROP FILE ‘+DATA/poc_db/datafile/users.264.925642655’;
Diskgroup altered.
For System and Sysaux an approach similar to the one given below can be used:
1. Create a Copy of datafile in target Diskgroup: 
—————————————
RMAN> backup as copy tablespace system format ‘+DATA1’;
Starting backup at 24-OCT-2016
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=146 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/poc_db/datafile/system.261.925641905
output file name=+DATA1/poc_db/datafile/system.261.926080035 tag=TAG20161024T122713 RECID=5 STAMP=926080035
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA1/poc_db/controlfile/backup.262.926080037 tag=TAG20161024T122713 RECID=6 STAMP=926080037
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24-OCT-2016
channel ORA_DISK_1: finished piece 1 at 24-OCT-2016
piece handle=+DATA1/poc_db/backupset/2016_10_24/nnsnf0_tag20161024t122713_0.263.926080039 tag=TAG20161024T122713 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-OCT-2016
RMAN> backup as copy tablespace sysaux format ‘+DATA1’;
Starting backup at 24-OCT-2016
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/poc_db/datafile/sysaux.260.925641905
output file name=+DATA1/poc_db/datafile/sysaux.260.926079645 tag=TAG20161024T122045 RECID=3 STAMP=926079646
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-OCT-2016
2. Then shutdown the database and restart to a mounted state 
—————————————————————
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 335547472 bytes
Database Buffers 490733568 bytes
Redo Buffers 6565888 bytes
3. switch the datafiles to the copy 
————————————
RMAN> switch tablespace sysaux to copy;
datafile 2 switched to datafile copy “+DATA1/poc_db/datafile/sysaux.260.926079645”
RMAN> switch tablespace system to copy;
datafile 1 switched to datafile copy “+DATA1/poc_db/datafile/system.261.926080035”
4. Recover the changes made to these tablespaces; 
—————————————————
RMAN> recover database;
Starting recover at 24-OCT-2016
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 24-OCT-2016
5. check datafile location
—————————————————
RMAN> report schema;
Report of database schema for database with db_unique_name poc_db
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 300 SYSTEM *** +DATA1/poc_db/datafile/system.261.926080035
2 200 SYSAUX *** +DATA1/poc_db/datafile/sysaux.260.926079645
3 500 UNDO_T01 *** +DATA/poc_db/datafile/undo_t01.259.925641907
4 500 USERS *** +DATA1/poc_db/datafile/users.259.926079061
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 500 TEMP_T01 500 +DATA/poc_db/tempfile/temp_t01.262.925641909
2 500 TEMP 500 +DATA/poc_db/tempfile/temp.263.925642565
6. Open your database
—————————————————
RMAN> alter database open;
database opened
Happy Learning !!
Hope you guys will like this.

No comments:

Post a Comment