Month: November 2013

3 Comments Posted on Oracle, Rac

Moving Your 11.2 Database to a new Diskgroup in ASM

Introduction:

I have been assigned with a new challenge with 11.2 Oracle On RedHat Linux with Grid Infrastructure and ASM.  Lets start with some background information. When I started building Real Application Clusters with ASM it was a standard to have merely one Disk group for data (often +DATA01) and one Disk group for the flash recovery area (+FRA01).  Over time that was altered as a standard by still having that one Data Disk group but using at least a dedicated disk group for the flash recovery area. When adding more databases to the same data disk group that might cause bottlenecks when one database grows fast claiming all space. Now a day standard would require a dedicated diskgroup both for data and fra. For one of the production databases I have been asked to deal with a scenario where there will be a new dedicated data disk group (the database already had a dedicated FRA disk group.

Below you will find the steps I performed in a sandbox as a scenario for upcoming change:

Details:

Working with the Spfile:

## First we start with finding location of the spfile that is being used.

SQL> show parameter spfile
NAME                                                      TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile                                                string       +DATA01/ASMdummy/spfileASMdummy.ora

## Next step is creating a pfile from this current spfile which will be used to recreate the spfile in the new disk group:

SQL> create pfile='/opt/oracle/ASMDUMMY/admin/pfile/initASMDUMMY.ora' from spfile ;
File created.

## Then create an spfile in the new disk group:

create spfile='+DATA02' from pfile='/opt/oracle/ASMDUMMY/admin/pfile/initASMDUMMY.ora';

## note alertlog shows:

Tue Nov 12 15:43:51 2013
SUCCESS: disk group DATA02 was mounted
Tue Nov 12 15:43:51 2013

NOTE: dependency between database ASMDUMMY and disk group resource ora.DATA02.dg is established

## Shutdown the database:

SQL> shutdown immediate;

## In a second screen make sure your environment is pointing to the ASM environment and go to ASMcmd  and go to the new disk group

asmcmd -p

## it shows:

ASMCMD [+DATA02/ASMDUMMY/PARAMETERFILE] > ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   NOV 12 15:00:00  Y    spfile.256.831311031

## Copy the file spfile.256.831311031 to the new disk group which will work as a more human readable file name:

cp +DATA02/ASMDUMMY/PARAMETERFILE/spfile.256.831311031 +DATA02/ASMDUMMY/spfileASMdummy.ora

### After doing that on Linux level alter the location of the spfile in the init.ora in the $ORACLE_HOME/dbs

cd /opt/oracle/product/11203_ee_64_a/db/dbs [ASMDUMMY]# ls -ltr  initASMDUMMY.ora
-rw-r----- 1 oracle dba       45 Nov 12 14:07 initASMDUMMY.ora
### current content: initASMDUMMY.ora
SPFILE='+DATA01/ASMDUMMY/spfileASMDUMMY.ora'

## After changing the disk group my new init.ora looks like this:

SPFILE='+DATA02/ASMDUMMY/spfileASMDUMMY.ora'

## Start the  database to find out that you did good thing :

SQL> startup

Working with the control files and Perform Backup:

SQL> show parameter control_files
NAME                                                      TYPE VALUE
---------------------------- ----------- ------------------------------
control_files                  string  +DATA01/ASMdummy/control01.ctl,+FRA01/ASMdummy/control02.ctl

## Set new location of controlfile in SPFILE:

SQL> alter system set control_files='+DATA02/ASMdummy/control01.ctl' scope=spfile sid='*';

## if you work with one control file , at the end you have to do extra step again to have a two control files again ( I have documented that step ) , but when re-reading this note I think would have been better to :

## Set new location of controlfile in SPFILE:

SQL> alter system set control_files='+DATA02/ASMdummy/control01.ctl', '+FRA01/ASMdummy/control02.ctl' scope=spfile sid='*';

## Shutdown your database

SQL> shutdown;

## Open ASMcmd again with the environment pointing to +ASM instance:

ASMCMD> cp +DATA01/ASMdummy/control01.ctl +DATA02/ASMdummy/control01.ctl

## check it:

ls -l  +DATA02/ASMdummy/control01.ctl
ASMCMD [+] > ls -l  +DATA02/ASMdummy/control01.ctl
Type         Redund  Striped  Time             Sys  Name
N    control01.ctl => +DATA02/ASM/CONTROLFILE/control01.ctl.258.831312753

##  Start your database with startup nomount

SQL> startup nomount;

## Start an rman session : Open “rman target /” and restore from old control and mount + open database:

rman target /
restore controlfile to '+DATA02/ASMdummy/control01.ctl' from '+DATA01/ASMdummy/control01.ctl';

##This will show

Starting restore at 12.11.2013 16:15:54
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 12.11.2013 16:15:55

##In Rman you should mount the database:

RMAN> sql 'alter database mount';

## this shows :

sql statement: alter database mount
released channel: ORA_DISK_1
## and in the alert:
Tue Nov 12 16:16:51 2013
SUCCESS: disk group DATA02 was mounted
Tue Nov 12 16:16:51 2013
NOTE: dependency between database ASMDUMMY and disk group resource ora.DATA02.dg is established
Tue Nov 12 16:16:55 2013
Successful mount of redo thread 1, with mount id 2267329971
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount

## Now it is time to make a backup of the database into the new Disk group (Data02). If you are in a rac environment make sure all other instances are down. Issue the following command in rman because this will create a one to one copy of the database in the new Disk group:

backup as copy database format '+DATA02';
##This will show:
Starting backup at 12.11.2013 16:18:54
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA01/ASMdummy/datafile/system.256.831304997
output file name=+DATA02/ASMdummy/datafile/system.259.831313135 tag=TAG20131112T161854 RECID=2 STAMP=831313144
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA01/ASMdummy/datafile/sysaux.257.831304997
output file name=+DATA02/ASMdummy/datafile/sysaux.260.831313151 tag=TAG20131112T161854 RECID=3 STAMP=831313157
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA01/ASMdummy/datafile/example.262.831305069
output file name=+DATA02/ASMdummy/datafile/example.261.831313165 tag=TAG20131112T161854 RECID=4 STAMP=831313170
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA01/ASMdummy/datafile/undotbs1.258.831304997
output file name=+DATA02/ASMdummy/datafile/undotbs1.262.831313173 tag=TAG20131112T161854 RECID=5 STAMP=831313173
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA02/ASMdummy/controlfile/backup.263.831313173 tag=TAG20131112T161854 RECID=6 STAMP=831313175
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA01/ASMdummy/datafile/users.259.831304997
output file name=+DATA02/ASMdummy/datafile/users.264.831313177 tag=TAG20131112T161854 RECID=7 STAMP=831313177
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 12.11.2013 16:19:38
channel ORA_DISK_1: finished piece 1 at 12.11.2013 16:19:39
piece handle=+DATA02/ASMdummy/backupset/2013_11_12/nnsnf0_tag20131112t161854_0.265.831313179 tag=TAG20131112T161854 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12.11.2013 16:19:39

## Once that has finished issue the following command in Rman (note this switch to command performs the  … set newname for you .. :

switch database to copy;
##This will show you:
datafile 1 switched to datafile copy "+DATA02/ASMdummy/datafile/system.259.831313135"
datafile 2 switched to datafile copy "+DATA02/ASMdummy/datafile/sysaux.260.831313151"
datafile 3 switched to datafile copy "+DATA02/ASMdummy/datafile/undotbs1.262.831313173"
datafile 4 switched to datafile copy "+DATA02/ASMdummy/datafile/users.264.831313177"
datafile 5 switched to datafile copy "+DATA02/ASMdummy/datafile/example.261.831313165"

##When that is finished issue following command in rman :

alter database open;

##Your alert file has been updated with following information:

Tue Nov 12 16:21:17 2013
Switch of datafile 1 complete to datafile copy
checkpoint is 1040434
Switch of datafile 2 complete to datafile copy
checkpoint is 1040434
Switch of datafile 3 complete to datafile copy
checkpoint is 1040434
Switch of datafile 4 complete to datafile copy
checkpoint is 1040434
Switch of datafile 5 complete to datafile copy
checkpoint is 1040434
Tue Nov 12 16:22:00 2013
alter database open
Tue Nov 12 16:22:00 2013
Thread 1 opened at log sequence 10
Current log# 1 seq# 10 mem# 0: +FRA01/ASMdummy/onlinelog/group_1.257.831305063
Current log# 1 seq# 10 mem# 1: +DATA01/ASMdummy/onlinelog/group_1.264.831305765
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Nov 12 16:22:00 2013
SMON: enabling cache recovery
[29312] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:3669618038 end:3669618518 diff:480 (4 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Tue Nov 12 16:22:03 2013
QMNC started with pid=33, OS id=8677
Tue Nov 12 16:22:06 2013
db_recovery_file_dest_size of 4122 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM disk group.
Completed: alter database open
Tue Nov 12 16:22:06 2013
Starting background process CJQ0
Tue Nov 12 16:22:06 2013
CJQ0 started with pid=34, OS id=8763

## At that moment it feels is if we are almost there ..

Create Destination(s)

## Open a Sqlplus session and check for the %create% parameter:

show parameter create
##This shows:
NAME                                                      TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size                   integer            8388608
create_stored_outlines                       string
db_create_file_dest                             string               +DATA01
db_create_online_log_dest_1           string               +FRA01
alter system set db_create_file_dest='DATA02' sid='*';
alter system set db_create_file_dest='DATA02' sid='*'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01261: Parameter db_create_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux-x86_64 Error: 2: No such file or directory
## pff typo  of course you should add the + in front of the disk group name
SQL> alter system set db_create_file_dest='+DATA02' sid='*';

Working with the temp files:

##Now it is time to work with the temp files. You will have to create a New temp tablespace in the new disk group, make that the default one and drop the old one:

SQL> select FILE_NAME from dba_temp_files;

## This shows:

FILE_NAME
--------------------------------------------------------------------------------
+DATA01/ASMdummy/tempfile/temp.261.831305069

##This will create a new temp tablespace , in the new disk group , make it the default tablespace and drop old

create temporary tablespace TEMP02 tempfile size 100m;
alter database default temporary tablespace TEMP02;
drop tablespace TEMP including contents;

##Check it again:

SQL> select FILE_NAME from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA02/ASMdummy/tempfile/temp02.266.831313635

Working with the redo logs:

In this step first we have to add new members to each group (to each thread (in a rac)). After that and after switching the log files you can delete the members in the old disk group

##First check the environment:

Set lines 2000
select * from v$log;
SQL>
GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE        MEMBERS ARC STATUS              FIRST_CHANGE# FIRST_TIME    NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1         1        10   52428800    512       2 YES INACTIVE                  1035793 12.11.2013 14:17:13      1067305 13.11.2013 06:00:48
2         1        11   52428800    512       2 NO  CURRENT            1067305 13.11.2013 06:00:48   2.8147E+14
3         1         9   52428800    512       2 YES INACTIVE                  1035790 12.11.2013 14:17:11      1035793 12.11.2013 14:17:13
## and
SQL> select GROUP#,MEMBER from v$logfile order by 1;
GROUP# MEMBER
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 +FRA01/ASMdummy/onlinelog/group_1.257.831305063
1 +DATA01/ASMdummy/onlinelog/group_1.264.831305765
2 +DATA01/ASMdummy/onlinelog/group_2.265.831305777
2 +FRA01/ASMdummy/onlinelog/group_2.258.831305065
3 +DATA01/ASMdummy/onlinelog/group_3.266.831305783
3 +FRA01/ASMdummy/onlinelog/group_3.259.831305065

##First we add new members to the correct , new disk group:

alter database add logfile member '+DATA02' to group 1;
alter database add logfile member '+DATA02' to group 2;
alter database add logfile member '+DATA02' to group 3;

###Check again:

select GROUP#,MEMBER from v$logfile order by 1;

## Perform several  switches to make sure the new members have been in use

SQL> alter system switch logfile;

##Alert showed:

Wed Nov 13 07:54:28 2013
Thread 1 advanced to log sequence 12 (LGWR switch)
Current log# 3 seq# 12 mem# 0: +FRA01/ASMdummy/onlinelog/group_3.259.831305065
Current log# 3 seq# 12 mem# 1: +DATA01/ASMdummy/onlinelog/group_3.266.831305783
Current log# 3 seq# 12 mem# 2: +DATA02/ASMdummy/onlinelog/group_3.269.831369169
Wed Nov 13 07:54:28 2013
Errors in file /opt/oracle/diag/rdbms/ASMdummy/ASMDUMMY/trace/ASMDUMMY_arc0_2197.trc:
ORA-00313: open failed for members of log group 2 of thread 1
Errors in file /opt/oracle/diag/rdbms/ASMdummy/ASMDUMMY/trace/ASMDUMMY_arc0_2197.trc:
ORA-00313: open failed for members of log group 2 of thread 1
Wed Nov 13 07:54:29 2013
Errors in file /opt/oracle/diag/rdbms/ASMdummy/ASMDUMMY/trace/ASMDUMMY_m000_22231.trc:
Errors in file /opt/oracle/diag/rdbms/ASMdummy/ASMDUMMY/trace/ASMDUMMY_m000_22231.trc:
Archived Log entry 8 added for thread 1 sequence 11 ID 0x8724b1a2 dest 1:
Wed Nov 13 07:55:38 2013
Thread 1 advanced to log sequence 13 (LGWR switch)
Current log# 1 seq# 13 mem# 0: +FRA01/ASMdummy/onlinelog/group_1.257.831305063
Current log# 1 seq# 13 mem# 1: +DATA01/ASMdummy/onlinelog/group_1.264.831305765
Current log# 1 seq# 13 mem# 2: +DATA02/ASMdummy/onlinelog/group_1.267.831369167
Wed Nov 13 07:55:38 2013
Archived Log entry 9 added for thread 1 sequence 12 ID 0x8724b1a2 dest 1:
Thread 1 cannot allocate new log, sequence 14
Checkpoint not complete
Current log# 1 seq# 13 mem# 0: +FRA01/ASMdummy/onlinelog/group_1.257.831305063
Current log# 1 seq# 13 mem# 1: +DATA01/ASMdummy/onlinelog/group_1.264.831305765
Current log# 1 seq# 13 mem# 2: +DATA02/ASMdummy/onlinelog/group_1.267.831369167
Thread 1 advanced to log sequence 14 (LGWR switch)
Current log# 2 seq# 14 mem# 0: +FRA01/ASMdummy/onlinelog/group_2.258.831305065
Current log# 2 seq# 14 mem# 1: +DATA01/ASMdummy/onlinelog/group_2.265.831305777
Current log# 2 seq# 14 mem# 2: +DATA02/ASMdummy/onlinelog/group_2.268.831369169
Wed Nov 13 07:55:45 2013
Archived Log entry 10 added for thread 1 sequence 13 ID 0x8724b1a2 dest 1:
Wed Nov 13 07:55:59 2013
Thread 1 advanced to log sequence 15 (LGWR switch)
Current log# 3 seq# 15 mem# 0: +FRA01/ASMdummy/onlinelog/group_3.259.831305065
Current log# 3 seq# 15 mem# 1: +DATA01/ASMdummy/onlinelog/group_3.266.831305783
Current log# 3 seq# 15 mem# 2: +DATA02/ASMdummy/onlinelog/group_3.269.831369169
Wed Nov 13 07:55:59 2013
Archived Log entry 11 added for thread 1 sequence 14 ID 0x8724b1a2 dest 1:

##It is time to drop the members from the old ( data01 )

select GROUP#,MEMBER from v$logfile order by 1;
GROUP# MEMBER
---------- -------------------------------------------------------------------------------- -------
1 +DATA01/ASMdummy/onlinelog/group_1.264.831305765
1 +DATA02/ASMdummy/onlinelog/group_1.267.831369167
1 +FRA01/ASMdummy/onlinelog/group_1.257.831305063
2 +DATA02/ASMdummy/onlinelog/group_2.268.831369169
2 +DATA01/ASMdummy/onlinelog/group_2.265.831305777
2 +FRA01/ASMdummy/onlinelog/group_2.258.831305065
3 +DATA02/ASMdummy/onlinelog/group_3.269.831369169
3 +DATA01/ASMdummy/onlinelog/group_3.266.831305783
3 +FRA01/ASMdummy/onlinelog/group_3.259.831305065
9 rows selected.

##So we have to drop the redo members that point to the old ( DATA01 ) Disk group:

alter database drop logfile member '+DATA01/ASMdummy/onlinelog/group_1.264.831305765';
alter database drop logfile member '+DATA01/ASMdummy/onlinelog/group_2.265.831305777';
alter database drop logfile member  '+DATA01/ASMdummy/onlinelog/group_3.266.831305783';
##When I did  this following error occurred:
SQL> alter database drop logfile member  '+DATA01/ASMdummy/onlinelog/group_3.266.831305783'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: '+FRA01/ASMdummy/onlinelog/group_3.259.831305065'
ORA-00312: online log 3 thread 1: '+DATA01/ASMdummy/onlinelog/group_3.266.831305783'
ORA-00312: online log 3 thread 1: '+DATA02/ASMdummy/onlinelog/group_3.269.831369169'
##So I need another switch logfile since group 3 was current.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile member  '+DATA01/ASMdummy/onlinelog/group_3.266.831305783';
Database altered.

##Checked again

select GROUP#,MEMBER from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
1 +FRA01/ASMdummy/onlinelog/group_1.257.831305063
1 +DATA02/ASMdummy/onlinelog/group_1.267.831369167
2 +DATA02/ASMdummy/onlinelog/group_2.268.831369169
2 +FRA01/ASMdummy/onlinelog/group_2.258.831305065
3 +DATA02/ASMdummy/onlinelog/group_3.269.831369169
3 +FRA01/ASMdummy/onlinelog/group_3.259.831305065
6 rows selected.

Working in the clusterware:

After these activities I tried Stopping and starting via srvctl ( this is an oracle restart environment crashed ) ..   Alertlog was having error messages and the start failed …   I did notice that the environment was using the old SPFILE in +Data01 again … I checked the spfile which was wrong again..

cd :/opt/oracle/product/11203_ee_64_a/db/dbs [ASMDUMMY]# cat initASMDUMMY.ora
SPFILE='+DATA01/ASMDUMMY/spfileASMDUMMY.ora'                        # line added by Agent

## so the clusteragent had altered my changes

##altered init.ora again and started that worked ..

##In sqlplus:
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;
## This shows:
NAME
--------------------------------------------------------------------------------
+DATA02/ASMdummy/control01.ctl
+DATA02/ASMdummy/datafile/example.261.831313165
+DATA02/ASMdummy/datafile/sysaux.260.831313151
+DATA02/ASMdummy/datafile/system.259.831313135
+DATA02/ASMdummy/datafile/undotbs1.262.831313173
+DATA02/ASMdummy/datafile/users.264.831313177
+DATA02/ASMdummy/onlinelog/group_1.267.831369167
+DATA02/ASMdummy/onlinelog/group_2.268.831369169
+DATA02/ASMdummy/onlinelog/group_3.269.831369169
+DATA02/ASMdummy/tempfile/temp02.266.831313635
+FRA01/ASMdummy/onlinelog/group_1.257.831305063
+FRA01/ASMdummy/onlinelog/group_2.258.831305065
+FRA01/ASMdummy/onlinelog/group_3.259.831305065
14 rows selected.

## In an Oracle restart or Rac Environment you need to check the Clusterware setup now since it has knowledge about spfile, disk groups being used etc.

##First check the configuration in the Clusterware for the database:

srvctl config database -d ASMDUMMY

##This shows:

Database unique name: ASMDUMMY
Database name: ASMDUMMY
Oracle home: /opt/oracle/product/11203_ee_64_a/db
Oracle user: oracle
Spfile: +DATA01/ASMDUMMY/spfileASMDUMMY.ora <<- OLD DISK GROUP
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: ASMDUMMY
Disk Groups: DATA01,FRA01,DATA02 <<- OLD DISK GROUP
Services:

##So we have to perform two action points:

  • Make the spfile point to the correct disk group ( our new +data02)
  • Disk groups attribute still knows about that data01 disk group ( and it should not)

##First modification will be to inform the Clusterware which spfile to use:

srvctl modify database -d ASMdummy -p ' +DATA02/ASMDUMMY/spfileASMDUMMY.ora'

##After that similar action for the disk groups:

srvctl modify database -d ASMdummy -a 'DATA02,FRA01'

## don’t believe it check it
srvctl config database -d ASMdummy
Database unique name: ASMDUMMY
Database name: ASMDUMMY
Oracle home: /opt/oracle/product/11203_ee_64_a/db
Oracle user: oracle
Spfile: +DATA02/ASMDUMMY/spfileASMDUMMY.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: ASMDUMMY
Disk Groups: DATA02,FRA01

##This looks better so now let’s do a stop & start with srvctl

srvctl stop database -d ASMdummy
srvctl start database -d ASMdummy

##That worked !  happy dba

##Since I started the scenario with one control file I needed to go back to two control files:

##Set new location of both ctlfile in SPFILE:

alter system set control_files='+DATA02/ASMdummy/control01.ctl','+FRA01/ASMdummy/control02.ctl' scope=spfile sid='*';

##Perform a shutdown

SQL> shutdown;

## Open ASMcmd (make sure you point to the ASM instance) and copy the controlfile to the Fra01 Disk group to get them in sync again:

ASMCMD> cp +DATA02/ASMdummy/control01.ctl +FRA01/ASMdummy/control02.ctl

##Check it:

ls -l  +FRA01/ASMdummy/control02.ctl

##In Sqlplus startup nomount

SQL> startup nomount;

## Start an  “rman target /” and restore from old control and mount & open database:

RMAN> restore controlfile to '+FRA01/ASMdummy/control02.ctl' from '+DATA02/ASMdummy/control01.ctl';
sql 'alter database mount';
sql 'alter database open';

##In an Sqlplus session final check :

SQL> show parameter control
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA02/ASMdummy/control01.ctl, +FRA01/ASMdummy/control02.ctl

As always Happy reading,

Mathijs.