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

articles: 

Oracle RDBMS Server - Enterprise Edition - Version: 10.2.0.2
OS: Applies to any OS, But this is tested on AIX5.3L

Goal:

Moving Data between Disk groups

ASM is a Storage Manager built for Oracle Database file’s, One can store Data files, Temp files, Redologs, Archive logs, Flashback recovery, backupsets and also spfile.

This Notes helps if the Disk groups are created with some redundancy and one want to change the redundancy (e.g. External to Normal, Normal to High) or Migrating data from one Diskgroup to another.

Redundancy of the Disk cannot be changed on the fly, Better way is to move the Data from Disk groups.

The purpose of the Document is to help move the Data from external Redundancy Diskgroup to Normal Redundancy Diskgroup

Note: One need level 0 Backup (or Cold Backup) of the Database before going further

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;

Comments

Oracle RDBMS Server - Enterprise Edition - Version: 10.2.0.3.
OS: Applies to any OS with available ksh shell while this example is tested on Solaris 5.10.
For info on supporting scripts called by this script: email michael dot shank at mpiresearch.com

Not sure how this will format... first posting on orafaq. If the posting is not clean enough, let me know and I will send you this script with supporting scripts. This script supports the move of $ORACLE_HOME/admin as well as files in ASM.

If you have a better way of automating the drop of redo members, please send along your comments.

Rem
Rem $Header: move_oracle_files_ASM.sql 30-may-2007 mjshank Exp $
Rem
Rem NAME
Rem move_oracle_files_ASM.sql - Script to move a specific database’s files.
Rem
Rem USAGE: sqlplus "/ as sysdba" @move_oracle_files_ASM.sql $sourcedg $destdg
Rem
Rem DESCRIPTION
Rem -- Whole database move script ---
Rem
Rem NOTES
Rem Must be run as sys and sysdba. May require a lengthy outage depending on if renaming
Rem or moving the database files. Multiple shutdowns are included as a trap for any failed
Rem dbf moves. RMAN is used to copy all required database files. The original files must
Rem be manually deleted after the database files are copied to their new destination.
Rem
Rem

-- Define database related variables.
undefine db host mydate sourcedg destdg
col db new_value db
col host new_value host
col mydate new_value mydate
col sourcedg new_value sourcedg
col sourcedg format a10
col destdg new_value destdg

-- Set database relate variables including SOURCE and DESTINATION DISKGROUP.
-- &1 and &2 are OS variables sourcedg and destdg.
set termout off
select '+&1' sourcedg from dual;
select '+&2' destdg from dual;
select lower(b.instance_name) db, lower(b.host_name) host from v$instance b;
select to_char(sysdate, 'YYYYMM_HHMISS') mydate from dual;
set termout on

prompt
prompt !!! WARNING !!! The database &&db. will be moved from from &&sourcedg. to &&destdg..
prompt Take a backup!
prompt
prompt !! You will need to remove or comment out the exit command
prompt (next statement in this script) to execute this script. !!!
prompt
prompt
exit

spool move_oracle_files_&&db._&&host._&&mydate..log

-- Confirm the database and that there are no users.
@@uname
@@who

-- Use the following query to verify current list of database files.
@@datafile_review.sql
-- The database is moving to one of the available (MOUNTED) diskgroups as the DESTINATION diskgroup.
set verify on echo on

-- Move the spfile and control files.
alter database backup controlfile to trace;
create pfile='/var/tmp/init__&&db._&&mydate..ora' from spfile;
show parameter spfile
create spfile='&&destdg./&&db./spfile&&db..ora' from pfile='/var/tmp/init__&&db._&&mydate..ora';
-- elsewhere... ASMCMD [+] > ls -al destdg/SID/ --- confirm new spfile

-- Point the instance to the spfile created in the destination diskgroup.
!cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
!cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora|sed -e "s/$sourcedg/$destdg/" > $ORACLE_HOME/dbs/init$ORACLE_SID.new.ora
!cat $ORACLE_HOME/dbs/init$ORACLE_SID.new.ora
!cp -p $ORACLE_HOME/dbs/init$ORACLE_SID.ora $ORACLE_HOME/dbs/init$ORACLE_SID.ora.`date '+%y_%m_%d_%H%M'`
!mv $ORACLE_HOME/dbs/init$ORACLE_SID.new.ora $ORACLE_HOME/dbs/init$ORACLE_SID.ora
!cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
shutdown immediate
startup nomount

-- Copy one of the SOURCE control files to all DESTINATION control files.
!echo "restore controlfile to '+$destdg/$ORACLE_SID/control01.ctl' from '+$sourcedg/$ORACLE_SID/control01.ctl';" > restore_control_file_$ORACLE_SID.rman
-- control02.ctl will be skipped – typically multiplexed into +FRA when using company standard template.
!echo "restore controlfile to '+$destdg/$ORACLE_SID/control03.ctl' from '+$sourcedg/$ORACLE_SID/control01.ctl';" >> restore_control_file_$ORACLE_SID.rman
!echo "exit" >> restore_control_file_$ORACLE_SID.rman
!cat restore_control_file_$ORACLE_SID.rman
!rman target / @restore_control_file_$ORACLE_SID.rman ## LOG restore_control_file_$ORACLE_SID.log
-- !cat restore_control_file_$ORACLE_SID.log

-- Now set the instance configuration to use the new control file copies.
show parameter control
alter system set control_files='&&destdg./&&db./control01.ctl','+FRA/&&db./control02.ctl','&&destdg./&&db./control03.ctl' scope=spfile;

-- Set the archive log to the DESTINATION diskgroup.
show parameter log_archive_dest_1
alter system set log_archive_dest_1='LOCATION=&&destdg./&&db./' scope=both;

-- Restart the instance using the new spfile, control files and archive log destination.
show parameter spfile
show parameter control
show parameter log_archive_dest_1
shutdown immediate
startup mount
show parameter spfile
show parameter control
show parameter log_archive_dest_1

-- Copy all source database files to the DESTINATION diskgroup
!echo "backup as copy database format '+$destdg';\nswitch database to copy;\nalter database open;\nlist copy of database;\nexit" > copy_database_$ORACLE_SID.rman
!cat copy_database_$ORACLE_SID.rman
!rman target / @copy_database_$ORACLE_SID.rman ## LOG copy_database_$ORACLE_SID.log

-- Replace the source temp file with a new destination temp file.
undefine source_temp
col source_temp new_value source_temp
col source_temp format a75
alter tablespace TEMP add tempfile '&&destdg.' SIZE 10M;
select name source_temp from v$tempfile where name like '&&sourcedg./%' and rownum < 2;
alter database tempfile '&&source_temp.' drop;
-- repeat above two for each temp file in sourcedg.
@@datafile_review.sql

-- Replace the source redo log files with new destination redo log files. A for loop would be handy here...
select member from v$logfile;
set linesize 110
select * from v$log;

-- Add one member in the destination diskgroup for each log group.
alter database add logfile member '&&destdg.' to group 1;
alter database add logfile member '&&destdg.' to group 2;
alter database add logfile member '&&destdg.' to group 3;
select member from v$logfile;

prompt This is a brute force ejection of the source diskgroup redo log members.
prompt Numerious errors are expected during the attempted drops for the redo members.
undefine source_redo_member
col source_redo_member new_value source_redo_member
col source_redo_member format a75
prompt Try to drop all three members but one or all will have failure messages. This is ok.
alter system checkpoint;
select member source_redo_member from v$logfile where member like '&&sourcedg./%' and rownum < 2;
alter database drop logfile member '&&source_redo_member.';
select member source_redo_member from v$logfile where member like '&&sourcedg./%' and rownum < 2;
alter database drop logfile member '&&source_redo_member.';
select member source_redo_member from v$logfile where member like '&&sourcedg./%' and rownum < 2;
alter database drop logfile member '&&source_redo_member.';
alter system switch logfile;
alter system checkpoint;
select member source_redo_member from v$logfile where member like '&&sourcedg./%' and rownum < 2;
alter database drop logfile member '&&source_redo_member.';
select member source_redo_member from v$logfile where member like '&&sourcedg./%' and rownum < 2;
alter database drop logfile member '&&source_redo_member.';
select member source_redo_member from v$logfile where member like '&&sourcedg./%' and rownum < 2;
alter database drop logfile member '&&source_redo_member.';
alter system switch logfile;
alter system checkpoint;
select member source_redo_member from v$logfile where member like '&&sourcedg./%' and rownum < 2;
alter database drop logfile member '&&source_redo_member.';
select member source_redo_member from v$logfile where member like '&&sourcedg./%' and rownum < 2;
alter database drop logfile member '&&source_redo_member.';
alter system switch logfile;
alter system checkpoint;
select member source_redo_member from v$logfile where member like '&&sourcedg./%' and rownum < 2;
alter database drop logfile member '&&source_redo_member.';
select member source_redo_member from v$logfile where member like '&&sourcedg./%' and rownum < 2;
alter database drop logfile member '&&source_redo_member.';

select * from v$log;
select member from v$logfile;

-- Delete all source database files in the SOURCE diskgroup.
-- The sourcedg is now the copy, so "delete copy" is actually deleting the source database!
!echo "list copy of database;\ncrosscheck copy;\ncrosscheck archivelog all;\ndelete copy of database;\nlist copy of database;" > copy_delete_$ORACLE_SID.rman
!echo "allocate channel for maintenance type disk;\ndelete noprompt obsolete device type disk;\nrelease channel;\nexit" >> copy_delete_$ORACLE_SID.rman
!cat copy_delete_$ORACLE_SID.rman
!rman target / @copy_delete_$ORACLE_SID.rman ## LOG copy_delete_$ORACLE_SID.log

-- Clean up temporary configuration files and source database control files.
!ls -alrt /var/tmp |tail -5
!rm /var/tmp/init__*$ORACL_SID* ; ls -alrt /var/tmp |tail -5
!ls -alrt $ORACLE_HOME/dbs/init*
!rm $ORACLE_HOME/dbs/init$ORACLE_SID.ora.* ; ls -alrt $ORACLE_HOME/dbs/init*
!ls -aldrt *$ORACLE_SID*
!rm restore_control_file_$ORACLE_SID.*
!rm copy_database_$ORACLE_SID.*
!rm copy_delete_$ORACLE_SID.* ; ls -aldrt *$ORACLE_SID*
-- elsewhere in asm...
-- rm sourcedg/SID/spfilep1mjsmv.ora
-- rm sourcedg/SID/CONTROLFILE/*

-- Final review after all changes.
show parameter spfile
show parameter control
show parameter log_archive_dest_1
@@datafile_review.sql

-- Drop out of sqlplus
prompt
prompt The database &&db. is now moved from &&sourcedg. to &&destdg..
prompt !!! Take a backup NOW! !!!
prompt
spool off
exit
exit

--- DO NOT COMPLETE THE STEPS BELOW UNLESS YOU ARE ALSO MOVING THE INSTANCE $ORACLE_BASE/ADMIN FILES.
exit

-- Review the Admin (dump) files:
show parameter audit_file_dest
show parameter background_dump_dest
show parameter core_dump_dest
show parameter user_dump_dest
show parameter dg_broker_config_file1
show parameter dg_broker_config_file2
show parameter log_archive_dest -- review for archive logs
show parameter pfile

alter system set audit_file_dest='/u01/app/oracle/admin/d1ersdw/adump' scope=spfile;
alter system set background_dump_dest='/u01/app/oracle/admin/d1ersdw/bdump' scope=spfile;
alter system set core_dump_dest='/u01/app/oracle/admin/d1ersdw/cdump' scope=spfile;
alter system set user_dump_dest='/u01/app/oracle/admin/d1ersdw/udump' scope=spfile;
-- alter system set dg_broker_config_file1
-- alter system set dg_broker_config_file2

shutdown immediate

! mkdir -p /u01/app/oracle/admin/dbs
! cp -pR /opt/oracle/admin/d1ersdw /u01/app/oracle/admin/.
! ls -alR /u01/app/oracle/admin/[sid]

startup

show parameter audit_file_dest
show parameter background_dump_dest
show parameter core_dump_dest
show parameter user_dump_dest
show parameter dg_broker_config_file1
show parameter dg_broker_config_file2
show parameter log_archive_dest -- review for archive logs

-- Consider making an spfile with one line: SPFILE='/u01/app/oracle/admin/dbs/spfiled1ersdw.ora'
show parameter pfile
create pfile='/u01/app/oracle/admin/d1ersdw/pfile/init.ora' from spfile;
alter database backup controlfile to trace;
shutdown immediate
-- startup pfile='/u01/app/oracle/admin/d1ersdw/pfile/init.ora'
-- create spfile='/u01/app/oracle/admin/dbs/spfiled1ersdw.ora' from pfile='/u01/app/oracle/admin/d1ersdw/pfile/init.ora';
-- ! cp -p /opt/oracle/product/10.2.0.2/db/dbs/spfiled1ersdw.ora /opt/oracle/product/10.2.0.2/db/dbs/spfiled1ersdw.ora.prev.mjs
-- ! echo "SPFILE='/u01/app/oracle/admin/dbs/spfiled1ersdw.ora'" >> /u01/app/oracle/admin/d1ersdw/pfile/init_redirect.ora
-- ! cat /u01/app/oracle/admin/d1ersdw/pfile/init_redirect.ora
-- create spfile='/opt/oracle/product/10.2.0.2/db/dbs/spfiled1ersdw.ora' from pfile='/u01/app/oracle/admin/d1ersdw/pfile/init_redirect.ora';
-- shutdown immediate;
-- startup

spool off
-- End

Our Oracle application is already on 11.5.10.2. When I checked for patch 3480000 it is not available in ad_bugs table. Is it required to apply it or not? We are planning to apply the WMS RUP16 patch and for this 3480000 is one of the prerequisites.