How To Move The Database To Different Diskgroup Migrating from External Redundancy to Normal Redundancy

Solution

Create a new diskgroup with desired redundancy and move the existing data to newly created diskgroup.

1) If we have extra disk space available,then we can create a new diskgroup and move the files from old diskgroup to it.

-- Initially I have 3 diskgroup with external redundancy as:

This blog will move the database in all the diskgroups to one diskgroup


export ORACLE_HOME=/opt/oracle/product/10.2.0.2/asm1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=+ASM1

SQL> select state,name from v$asm_diskgroup;

STATE NAME

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

MOUNTED DATA1

MOUNTED RMAN1

MOUNTED SYS1

SQL> !ls -ltr /dev/rhdisk*

crw------- 2 root system 14, 1 Feb 5 15:58 /dev/rhdisk1

crw------- 1 root system 14, 0 May 14 13:41 /dev/rhdisk0

crw-r----- 1 root oinstall 14, 2 May 14 17:13 /dev/rhdisk2

crw-r----- 1 root oinstall 14, 7 Jul 11 09:26 /dev/rhdisk7

crw------- 1 oracle dba 14, 9 Jul 11 09:26 /dev/rhdisk9

crw-r--r-- 1 oracle oinstall 14, 8 Jul 11 09:26 /dev/rhdisk8

crw------- 1 oracle dba 14, 14 Jul 11 09:26 /dev/rhdisk14

crw-r--r-- 1 oracle oinstall 14, 13 Jul 11 09:26 /dev/rhdisk13

crw-r----- 1 root oinstall 14, 12 Jul 11 09:26 /dev/rhdisk12

crw------- 1 oracle dba 14, 11 Jul 11 09:26 /dev/rhdisk11

crw------- 1 oracle dba 14, 10 Jul 11 09:26 /dev/rhdisk10

crw------- 1 oracle dba 14, 16 Jul 11 09:26 /dev/rhdisk16

crw------- 1 oracle dba 14, 15 Jul 11 09:26 /dev/rhdisk15

crw------- 1 oracle dba 14, 6 Jul 11 12:05 /dev/rhdisk6

crw------- 1 oracle dba 14, 5 Jul 11 12:06 /dev/rhdisk5

crw------- 1 oracle dba 14, 4 Jul 11 12:06 /dev/rhdisk4

crw-r--r-- 1 oracle oinstall 14, 3 Jul 11 12:06 /dev/rhdisk3

SQL> show PARAMETER ASM_DISKSTRING

NAME TYPE VALUE

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

asm_diskstring string

SQL> !ssh node2 ls -l /dev/rhdisk*

crw------- 2 root system 15, 0 Feb 5 10:23 /dev/rhdisk0

crw------- 1 root system 15, 1 Feb 5 10:47 /dev/rhdisk1

crw------- 1 oracle dba 15, 11 Jul 11 09:33 /dev/rhdisk10

crw------- 1 oracle dba 15, 10 Jul 11 09:35 /dev/rhdisk11

crw-r----- 1 root oinstall 15, 12 Jul 11 09:36 /dev/rhdisk12

crw-r--r-- 1 oracle oinstall 15, 13 Jul 11 09:37 /dev/rhdisk13

crw------- 1 oracle dba 15, 14 Jul 11 09:38 /dev/rhdisk14

crw------- 1 oracle dba 15, 15 Jul 11 09:40 /dev/rhdisk15

crw------- 1 oracle dba 15, 16 Jul 11 09:42 /dev/rhdisk16

crw-r----- 1 root oinstall 15, 2 Jul 11 12:07 /dev/rhdisk2

crw-r--r-- 1 oracle oinstall 15, 3 Jul 11 12:07 /dev/rhdisk3

crw------- 1 oracle dba 15, 4 Jul 11 12:07 /dev/rhdisk4

crw------- 1 oracle dba 15, 5 Jul 11 12:07 /dev/rhdisk5

crw------- 1 oracle dba 15, 6 Jul 11 12:07 /dev/rhdisk6

crw-r----- 1 root oinstall 15, 7 Jul 11 09:30 /dev/rhdisk7

crw-r--r-- 1 oracle oinstall 15, 8 Jul 11 09:31 /dev/rhdisk8

crw------- 1 oracle dba 15, 9 Jul 11 09:32 /dev/rhdisk9
2) Create a new diskgroup with normal redundancy as :


SQL> create diskgroup DATA2 normal redundancy failgroup datafg01 disk '/dev/rhdisk9' failgroup datafg02 disk '/dev/rhdisk14';

Diskgroup created.

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

STATE NAME TYPE

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

MOUNTED DATA1 EXTERN

MOUNTED RMAN1 EXTERN

MOUNTED SYS1 EXTERN

MOUNTED DATA2 NORMAL

export ORACLE_HOME=/opt/oracle/product/10.2.0.2/db1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=db1
3)Backup the current database as follows:


SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string db

SQL> show parameter control

NAME TYPE VALUE

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

control_file_record_keep_time integer 7

control_files string +SYS1/db/controlfile/current.257.623341273

SQL> alter database backup controlfile to '+DATA2';

Database altered.

SQL> alter system set control_files='+DATA2\db\CONTROLFILE\Backup.256.627662879' SCOPE=SPFILE;

System altered.

-- Connect to rman

home/oracle > rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Jul 11 14:53:59 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: DB (DBID=732666905)

RMAN> shutdown immediate;=== if its RAC shutdown from node 2 also

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)

Oracle instance started

Total System Global Area 2063597568 bytes

Fixed Size 2072480 bytes

Variable Size 469762144 bytes

Database Buffers 1577058304 bytes

Redo Buffers 14704640 bytes

RMAN> restore controlfile from '+SYS1\db\CONTROLFILE\Current.257.623341273';

Starting restore at 11-JUL-07

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=145 instance=db1 devtype=DISK

channel ORA_DISK_1: copied control file copy

output filename=+DATA2/db/controlfile/backup.256.627662879

Finished restore at 11-JUL-07

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> backup as copy database format '+DATA2';
4)Switch the database to copy. At this moment we are switching to the new Diskgroup

== A SWITCH is equivalent to using the PL/SQL "alter database rename file" statement.


RMAN> switch database to copy;

RMAN > alter database open;==wil not work
this is a bug in 10.2.0.2

exit out and


sqlplus “/ as sysdba”

alter database open

shutdown immediate;

startup

alter system set db_create_file_dest ='+DATA2';
5)Add new tempfile to newly created database.


SQL> create bigfile temporary tablespace temp03 tempfile size 100M;

Tablespace created.

SQL> alter database default temporary tablespace temp03;

Database altered.

Drop any existing tempfile on the old diskgroup

SQL> drop tablespace temp;

Tablespace dropped.
6)Find out how many members we have have in redolog groups, make sure that we have only one member in each log group.(drop other members).

SQL> select * from v$log;

Suppose we have 4 log groups, then add one member to each log group as following:


SQL> alter database add logfile member '+data2' to group 1;

Database altered.

SQL> alter database add logfile member '+data2' to group 2;

Database altered.

SQL> alter database add logfile member '+data2' to group 3;

Database altered.

SQL> alter database add logfile member '+data2' to group 4;

Database altered.

SQL>
Then we can drop the old logfile member from earlier diskgroups as:

Issue the below command till all the logfiles are in valid state


alter system switch logfile;

SQL> alter database drop logfile member '+SYS1/db/onlinelog/group_2.260.623341307';

Database altered.

SQL> select * from v$logfile;
Logfiles from group 3 and 4 cannot be dropped at this point from node 1 as node 2 is still down

start the database on node 2 and delete the old logfiles in the data1/sys1 diskgroup

7)Use the following query to verify that all the files are moved to new diskgroup with desired redundancy:


SQL> select name from v$controlfile

union

select name from v$datafile

union

select name from v$tempfile

union

select member from v$logfile

union

select filename from v$block_change_tracking
union
select name from v$flashback_database_logfile;