How to Relocate Standby Database ASM Datafiles

TASK

This task is to relocate all the datafiles of diskgroup DATA1 to another diskgroup DATA2 for a standby database.

SOULUTION

1)Check the diskgroup space availability.

SQL> set pagesize 120
SQL> set linesize 180
SQL> select NAME,TOTAL_MB/1024,FREE_MB/1024,USABLE_FILE_MB/1024 
       from v$asm_diskgroup;

NAME T    TOTAL_MB/1024 FREE_MB/1024 USABLE_FILE_MB/1024
--------- ------------- ------------ -------------------
DATA1     1024.00977      15.6894531   15.6894531
OCR_VOTE    19.9931641    15.4257813   15.4257813
FRA        500.069336    210.331055   210.331055
DATA2     4000.55469    1816.02344   1816.02344

2)Leave managed standby database recovery still going. Create a RMAN script by using below query:

SQL> select 'backup as copy datafile '||file#||' format ''+DATA2'';' 
       from v$datafile 
      where name like '+DATA1%' order by 1;

'BACKUPASCOPYDATAFILE'||FILE#||'FORMAT''+DATA2'';'
---------------------------------------------------
backup as copy datafile 124 format '+DATA2';
backup as copy datafile 125 format '+DATA2';
backup as copy datafile 126 format '+DATA2';
backup as copy datafile 127 format '+DATA2';
.....
....
...
..
.

The final RMAN script content:

$cat move_DATA1_to_DATA2.rman

run {
allocate channel stdbych1 type disk;
allocate channel stdbych2 type disk;
allocate channel stdbych3 type disk;
allocate channel stdbych4 type disk;
backup as copy datafile 124 format '+DATA2';
backup as copy datafile 125 format '+DATA2';
backup as copy datafile 126 format '+DATA2';
backup as copy datafile 127 format '+DATA2';
....
...
..
.
}

3) Run the RMAN script to make a copy of all datafiles on diskgroup DATA1 onto diskgroup DATA2.

$nohup rman target / cmdfile=/u01/app/oracle/admin/scripts/move_DATA1_to_DATA2.rman  log=/u01/app/oracle/admin/scripts/move_DATA1_To_DATA2.log &

4) Stop the managed standby database recovery.

SQL> alter database recover managed standby database cancel;

Database altered.

5) Switch the datafiles.

RMAN>switch datafile 124 to copy;
RMAN>switch datafile 125 to copy;
RMAN>switch datafile 126 to copy;
RMAN>switch datafile 127 to copy;
....
...
..
.

6) Start the recovery.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

5) Archive log switch, check applied from alert.log, etc.

SQL>alter system archive log current ;
System altered.

6) Unmount diskgroup DATA1 on all the nodes, except the current node.

SQL> alter diskgroup data1 dismount;
Diskgroup altered.

7) Drop the diskgroup DATA1.

SQL> drop diskgroup data1;
drop diskgroup data1
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup "DATA1" contains existing files

SQL> drop diskgroup data1 including contents;
Diskgroup dropped.

8) Scan disks and confirm the ASM Disks of DATA1 have been removed from ASM disks.

$su - 

# oracleasm listdisks
ASM_DISK01
ASM_DISK02
ASM_DISK03
ASM_DISK04
ASM_DISK05
ASM_DISK06
ASM_DISK07
ASM_DISK08
ASM_FRA01
ASM_SSD1  <----- DATA1 diskgroup
OCR_VOTE01

# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Cleaning disk "ASM_SSD1"
Scanning system for ASM disks...

# oracleasm listdisks
ASM_DISK01
ASM_DISK02
ASM_DISK03
ASM_DISK04
ASM_DISK05
ASM_DISK06
ASM_DISK07
ASM_DISK08
ASM_FRA01
OCR_VOTE01