How To Move The Database To Different Diskgroup (Change Diskgroup Redundancy) (文档 ID 438580.1)

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]
Oracle Database - Standard Edition - Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

Goal

Automatic Storage Management (ASM) is an integrated file system and volume manager expressly built for Oracle database files.

This note is applicable :

1. If you wish to move to different ASM storage / Hardware.
2. If you wish to change the redundancy of the diskgroup

When the diskgroups are created with some redundancy say External,Normal or High then its redundancy cannot be changed. Need to change redundancy can arise if :

- DBA's want to switch from Normal/High Redundancy to External Redundancy due to disk space constraints or due to plans of using External methods of maintaining redundancy (like RAID 10 , etc) .

- Switch to ASM based Redundancy i.e converting from External redundancy to Normal/High Redundancy

This note discusses the steps to change the redundancy of existing diskgroups in ASM.

Note : - Please note that this steps have been tried and tested internally. But we would suggest users to test the steps in a Test Environment before executing them in Production.

Also having a Full Cold Backup for the database is recommended.

Solution

To collect the list of files in ASM with their full path, use the Note 888943.1 named "How to collect the full path name of the files in ASM diskgroups"

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.

CASE 1: 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 we have two diskgroup with external redundancy as:

SQL> select state,name from v$asm_diskgroup;

STATE NAME
----------- --------------------
MOUNTED DG2
MOUNTED DG3

2) Create a new diskgroup with normal redundancy as :

SQL > create diskgroup DG1 normal redundancy failgroup disk 'disk1_name' failgroup disk 'disk2_name';

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

STATE NAME TYPE
----------- ------------------- ------
MOUNTED DG2 EXTERN
MOUNTED DG3 EXTERN
MOUNTED DG1 NORMAL

3)Backup the current database as follows:

SQL> show parameter db_name

NAME TYPE VALUE
---------------- ----------- ----------------------------
db_name string orcl10g

SQL> create pfile='d:\initsid.ora' from spfile;

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

SQL> alter system set control_files='+DG1\ORCL10G\CONTROLFILE\' SCOPE=SPFILE;

-- Connect to rman
$ rman target /
RMAN > shutdown immediate;
RMAN > startup nomount;
RMAN> restore controlfile to '' from '+DG2\ORCL10G\CONTROLFILE\mycontrol.ctl'; (specify the original (old) location of controlfile here)
(Check MOS Doc ID 345180.1 to multiplex the controlfile, when the move is done)
Mount the database and validate the controlfiles from v$controlfile

RMAN > alter database mount;

RMAN> backup as copy database format '+DG1';

With "BACKUP AS COPY", RMAN copies the files as image copies, bit-for-bit copies of database files created on disk.These are identical to copies of the same files that you can create with operating system commands like cp on Unix or COPY on Windows.However, using BACKUP AS COPY will be recorded in the RMAN repository and RMAN can use them in restore operations.

4)Switch the database to copy. At this moment we are switching to the new Diskgroup

RMAN> switch database to copy;

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

RMAN> recover database ;

This will recover the backup controlfile taken and restored before to be in sync with database/datafiles

RMAN> alter database open resetlogs;

5)Add new tempfile to newly created database.

SQL> alter tablespace TEMP add tempfile '+DG1' SIZE 10M;

Drop any existing tempfile on the old diskgroup

SQL> alter database tempfile '+DG2/orcl10g/tempfile/temp.265.626631119' drop;

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

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

SQL> alter database add logfile member '+DG1' to group 1;
SQL> alter database add logfile member '+DG1' to group 2;
SQL> alter database add logfile member '+DG1' to group 3;

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

SQL> alter database drop logfile member 'complete_name';

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

8) Enable block change tracking using ALTER DATABASE command.

SQL> alter database enable block change tracking using file ‘’;

Case 2:Drop the existing diskgroup after database backup and create a new diskgroup with desired redundancy.

1.Shutdown(immediate) the database and then startup mount. Take a valid RMAN backup of existing database as:

RMAN> backup device type disk format 'd:\backup\%U' database ;

RMAN> backup device type disk format 'd:\backup\%U'archivelog all;

2. Make copy of spfile to accessible location:

SQL> create pfile='d:\initsid.ora' from spfile;

SQL> alter database backup controlfile to 'd:\control.ctl';

3. Shutdown the RDBMS instance

SQL> shutdown immediate

4. Connect to ASM Instance and Drop the existing Diskgroups

SQL> drop diskgroup DG1 including contents;

5. Shutdown ASM Instance;

6.Startup the ASM instance in nomount state and Create the new ASM diskgroup

SQL>startup nomount
SQL> create diskgroup dg1 external redundancy disk'disk_name';

The new diskgroups name should be same as of previous diskgroup, it will facilitate the RMAN restore process.

7. Connect to the RDBMS instance and startup in nomount state using pfile

startup nomount pfile='d:\initsid.ora'
SQL> create spfile from pfile='d:\initsid.ora'

8. Now restore the controlfile and backup's using RMAN

RMAN > restore controlfile from 'd:\control.ctl';
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;

unable to find archive log
archive log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/05/2007 18:24:32
RMAN-06054: media recovery requesting unknown log: thread 1 seq 4 lowscn
570820

While recovery it will give an error for archive log missing, this is expected we need to open the database with resetlogs as:

RMAN> alter database open resetlogs;

-We also need to change Flash Recovery Area to newly created diskgroup location.

SQL> alter system set db_recovery_file_dest='+DG1' scope=both;

-We must now disable and re-enable Flashback Database so that the flashback log files are recreated in the +DG1 disk group and this can be done in mount state only.

SQL> alter database flashback off ;
SQL> alter database flashback on ;

- In case you want to use new name for diskgroup,in step 8 after mounting the database , you can use :

RMAN> run{
set newname for datafile 1 to '+DG2';
set newname for datafile 2 to '+DG2';
set newname for datafile 3 to '+DG2';
set newname for datafile 4 to '+DG2';
set newname for datafile 5 to '+DG2';
restore database;
switch datafile all;
recover database;
}

(assuming that we have 5 datafiles in our database)

Additional Resources

Community Discussions: Storage Management

Still have questions? Use the above community to search for similar discussions or start a new discussion on this subject.

References

NOTE:345180.1 - How to duplicate a controlfile when ASM is involved

How To Move DB To Different Diskgroup/Change Diskgroup Redundancy

How To Move The Database To Different Diskgroup (Change Diskgroup Redundancy) (文档 ID 438580.1)

Applies to:

Goal

Solution

References