3 - 4 minutes readMove / Relocate Database to Different Disk Group

Source Setup Details:
Host Name: SUN1
IP: 192.168.126.2
Oracle Home: /export/home/oracle/oracle/product/db_1
DB Name: INDIAN
PORT: 1521
ASM DISK GROUP: DB_DATA01

Task:

We move or relocate database from DB_DATA01 disk group to New Desk Group DB_DATA02

Retrieve bellow mention information from Database:

A. Check Datafile, redologfile, controlfile and temp file location:

SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;
SQL> select name from v$tempfile;

B. Know your instance is currently using spfile or pfile and mote down location of spfile

SQL> show parameter spfile

NAME TYPE VALUE
——————————- ———– ——————————
spfile string +DB_DATA01/indian/spfileindian.ora

C. Know current controlfile location

SQL> show parameter control_files

NAME TYPE VALUE
—————— ———– ——————————
control_files string +DB_DATA01/indian/controlfile/current.284.746241239

There are two ways to perform this:

1. Create a new Diskgroup with desired redundancy and move the existing data to newly created Diskgroup.
2. Drop the existing Diskgroup after backing up data and create a new Diskgroup with desired redundancy.

First Way:

Step 1 Check Current DISK Group and status

SQL> select state,name from v$asm_diskgroup;

STATE NAME
———– ——————————
CONNECTED DB_DATA01

Step 2 Create a new diskgroup with Desire redundancy

Step 3 Check DISK Group and status

SQL> select state,name,type from v$asm_diskgroup;

STATE NAME TYPE
———– —————————— ——
CONNECTED DB_DATA01 EXTERN
MOUNTED DB_DATA02 NORMAL

Step 4 Create pfile from spfile, if required

SQL> create pfile=’/export/home/oracle/initINDIAN.ora’ from spfile;

Step 5 Backup controlfile to Target Disk Group

SQL> alter database backup controlfile to ‘+DB_DATA02’;
Database altered.

Step 6 Update parameter is spfile

SQL>alter system set control_files=’+DB_DATA02/indian/controlfile/Backup.269.746242015′ scope=spfile;

Step 7 Connect to rman, restore Controlfile and copy database files to New Disk Group.

$ rman target /
RMAN > shutdown immediate;
RMAN > startup nomount;
RMAN> restore controlfile from ‘+DB_DATA01/indian/controlfile/current.284.746241239’;

Note: Mount the database and validate the controlfiles from v$controlfile

RMAN> alter database mount;
RMAN> backup as copy database format ‘+DB_DATA02’;

Note: With “BACKUP AS COPY”, RMAN copies the files as image copies, bit-for-bit copies of database files created on disk

Step 8 Switch the database to copy. At this moment we are switching to the new Diskgroup

RMAN> switch database to copy;

Note: A SWITCH is equivalent to using the PL/SQL “alter database rename file” statement.

###########Add new tempfile to newly created Disk Group############

SQL> select name from v$tempfile;

NAME
——————————————————————————–
+DB_DATA01/indian/tempfile/temp.278.746241263

SQL> alter tablespace TEMP add tempfile ‘+DB_DATA02’;
Tablespace altered.

SQL> alter database tempfile ‘+DB_DATA01/indian/tempfile/temp.278.746241263’ drop;
Database altered.

#########Find out how many members we have in redo log groups#######

SQL> select group#,member from v$logfile;

GROUP# MEMBER
——————————————————————————–
3 +DB_DATA01/indian/onlinelog/group_3.269.746241245
2 +DB_DATA01/indian/onlinelog/group_2.276.746241243
1 +DB_DATA01/indian/onlinelog/group_1.277.746241241

Add logfile member

SQL> alter database add logfile member ‘+DB_DATA02’ to group 1;
Database altered.

SQL> alter database add logfile member ‘+DB_DATA02’ to group 2;
Database altered.

SQL> alter database add logfile member ‘+DB_DATA02’ to group 3;
Database altered.

Drop old logfile from earlier diskgroup

SQL> alter database drop logfile member ‘complete_name’;

##########verify that all the files are moved to new diskgroup##########

SQL>
select name from v$controlfile
union
select name from v$datafile
union
select name from v$tempfile
union
select member from v$logfile

Second Way:

Step 1 Shutdown, mount the database and take a valid RMAN Backup.

RMAN> backup device type disk format ‘/export/home/oracle/rman/%U’ database;
RMAN> backup device type disk format ‘/export/home/oracle/rman/%U’ archivelog all;

Step 2 Backup the spfile.

SQL> create pfile=’/export/home/oracle/initINDIAN.ora’ from spfile;

Step 3 Backup the Controlfile.

SQL> alter database backup controlfile to ‘/export/home/oracle/control.ctl’;

Step 4 Connect to RDBMS and shutdown the database

SQL> shutdown immediate

Step 5 Connect to ASM Instance and Drop the Existing Diskgroup

SQL> drop diskgroup DB_DATA01 including contents;
SQL> shutdown immediate
SQL> startup nomount
SQL> create diskgroup DB_DATA02 external redundancy disk ‘/dev/rdsk/c0dqs1’;

Step 6 connect to the RDBMS and startup nomount using pfile

SQL> startup nomount pfile=’/export/home/oracle/initINDIAN’
SQL> create spfile from pfile=’/export/home/oracle/initINDIAN’

Step 7 Now restore the controlfile and backup

RMAN> restore controlfile from ‘/export/home/oracle/controlf.ctl’;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;