It was relatively easy. The technique I used was an RMAN backup as copy, then a switch of the files. This assumes you already have ASM installed and a disk group available.
Here we go.
Make a note of the existing controlfile location:
select name from v$controlfile;
NAME
-----------------------------------
/oracle/eu_data_1/data/EMSBY/EMSBY_ctrl_1.ctl
Being a standby, the database is in mount mode:
sys.EMSBY> select
open_mode from v$database;
OPEN_MODE
----------
MOUNTED
1 row selected.
These are some of the files:
sys.EMSBY> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/oracle/eu_data_1/data/EMSBY/system_01.dbf
/oracle/eu_data_1/data/EMSBY/cis_idx03_67.dbf
/oracle/eu_data_1/data/EMSBY/undo_rbs_02.dbf
I need to stop the apply from the primary, so checked to see if it was using dg broker or not
sys.EMSBY> show parameter broker_s
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE
Nope.
Turn off the apply:
sys.EMSBY> alter DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
Connect to RMAN and issue the backup as copy command, point the destination to the ASM disk.
rman
RMAN> connect target /
connected to target database: EMSBY (DBID=2614687418, not open)
RMAN> RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
BACKUP AS COPY
INCREMENTAL LEVEL 0
DATABASE
FORMAT '+EMSBY_DATA01'
TAG 'ORA_ASM_MIGRATION_EMSBY';
}
allocated channel: dev1
channel dev1: sid=114 devtype=DISK
allocated channel: dev2
channel dev2: sid=249 devtype=DISK
allocated channel: dev3
channel dev3: sid=120 devtype=DISK
allocated channel: dev4
channel dev4: sid=116 devtype=DISK
Starting backup at 05-DEC-13
channel dev1: starting datafile copy
input datafile fno=00036 name=/oracle/eu_data_1/data/EMSBY/cis_idx03_80.dbf
channel dev2: starting datafile copy
input datafile fno=00285 name=/oracle/eu_data_1/data/EMSBY/rpt_tab02_04.dbf
channel dev3: starting datafile copy
input datafile fno=00248 name=/oracle/eu_data_1/data/EMSBY/cis_idx03_70.dbf
channel dev4: starting datafile copy
input datafile fno=00035 name=/oracle/eu_data_1/data/EMSBY/cis_tab03_43.dbf
.
.
.
output filename=+EMSBY_DATA01/EMSBY_base/datafile/rpt_idx01.538.833376955 tag=ORA_ASM_MIGRATION_EMSBY recid=300 stamp=833376957
channel dev3: datafile copy complete, elapsed time: 00:00:04
output filename=+EMSBY_DATA01/EMSBY_base/controlfile/backup.539.833376955 tag=ORA_ASM_MIGRATION_EMSBY recid=298 stamp=833376956
channel dev4: datafile copy complete, elapsed time: 00:00:04
including current SPFILE in backupset
channel dev1: starting piece 1 at 05-DEC-13
channel dev1: finished piece 1 at 05-DEC-13
piece handle=+EMSBY_DATA01/EMSBY_base/backupset/2013_12_05/nnsnn0_ora_asm_migration_EMSBY_0.540.833376961 tag=ORA_ASM_MIGRATION_EMSBY comment=NONE
channel dev1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-DEC-13
For record type DATAFILE COPY recids from 17 to 18 are re-used before resync
released channel: dev1
released channel: dev2
released channel: dev3
released channel: dev4
Just to be on the safe side, I Copied spfile, init.ora and control file to /apps/oracle/work/akh/EMSBY
Now logon to the database and do some fiddling:
Restart it in nomount mode:
/apps/oracle> sqlplus "/ as sysdba"
sys.EMSBY> startup force nomount;
ORACLE instance started.
Change the file creation destinations to the ASM disk group:
sys.EMSBY> ALTER SYSTEM SET DB_CREATE_FILE_DEST='+EMSBY_DATA01' sid='*';
System altered.
sys.EMSBY> ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1 = '+EMSBY_DATA01' sid='*';
System altered.
sys.EMSBY> ALTER SYSTEM SET CONTROL_FILES='+EMSBY_DATA01' scope=spfile sid='*';
System altered.
Exit, then connect to RMAN
/apps/oracle> rman target /
connected to target database: EMSBY (not mounted)
Restore the controlfile, using the location noted at the top:
RMAN> RESTORE CONTROLFILE FROM '/oracle/eu_data_1/data/EMSBY/EMSBY_ctrl_1.ctl';
This will put it into the ASM disk group:
Starting restore at 05-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=123 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+EMSBY_DATA01/EMSBY_base/controlfile/current.541.833378337
Finished restore at 05-DEC-13
Now mount the database (can be done within RMAN), and run the switch command. This will cause the database to start to use the ASM files:
RMAN> ALTER DATABASE MOUNT;
database mounted
released channel: ORA_DISK_1
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+EMSBY_DATA01/EMSBY_base/datafile/system.476.833376833"
datafile 2 switched to datafile copy "+EMSBY_DATA01/EMSBY_base/datafile/cis_idx03.263.833372371"
datafile 3 switched to datafile copy "+EMSBY_DATA01/EMSBY_base/datafile/undo_rbs.488.833376881"
.
.
.
datafile 284 switched to datafile copy "+EMSBY_DATA01/EMSBY_base/datafile/rpt_idx02.261.833372145"
datafile 285 switched to datafile copy "+EMSBY_DATA01/EMSBY_base/datafile/rpt_tab02.256.833371465"
datafile 286 switched to datafile copy "+EMSBY_DATA01/EMSBY_base/datafile/rpt_tab02.483.833376865"
Now all the files are there, you need to RECOVER the database to apply all the logs that have been generated during the backup:
RMAN> run
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
RECOVER DATABASE;
}
allocated channel: dev1
channel dev1: sid=123 devtype=DISK
allocated channel: dev2
channel dev2: sid=254 devtype=DISK
allocated channel: dev3
channel dev3: sid=120 devtype=DISK
allocated channel: dev4
channel dev4: sid=116 devtype=DISK
Starting recover at 05-DEC-13
.
archive log thread 1 sequence 427121 is already on disk as file /apps/oracle/admin/EMSBY/arch/EMSBY_1_479644347_0000427121.arc
archive log thread 1 sequence 427122 is already on disk as file /apps/oracle/admin/EMSBY/arch/EMSBY_1_479644347_0000427122.arc
archive log thread 1 sequence 427123 is already on disk as file /apps/oracle/admin/EMSBY/arch/EMSBY_1_479644347_0000427123.arc
archive log thread 1 sequence 427124 is already on disk as file /apps/oracle/admin/EMSBY/arch/EMSBY_1_479644347_0000427124.arc
archive log filename=/apps/oracle/admin/EMSBY/arch/EMSBY_1_479644347_0000427014.arc thread=1 sequence=427014
.
.
.
archive log filename=/apps/oracle/admin/EMSBY/arch/EMSBY_1_479644347_0000427128.arc thread=1 sequence=427128
archive log filename=/apps/oracle/admin/EMSBY/arch/EMSBY_1_479644347_0000427129.arc thread=1 sequence=427129
unable to find archive log
archive log thread=1 sequence=427130
released channel: dev1
released channel: dev2
released channel: dev3
released channel: dev4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/05/2013 15:20:54
RMAN-06054: media recovery requesting unknown log: thread 1 seq 427130 lowscn 7619059397730
Note that you'll always get this error as it tries to find the latest log. If you check the alert log you'll see it's OK:
Media Recovery Log /apps/oracle/admin/EMSBY/arch/EMSBY_1_479644347_0000427129.arc
ORA-279 signalled during: alter database recover logfile '/apps/oracle/admin/EMSBY/arch/EMSBY_1_479644347_0000427129.arc'...
Thu Dec 5 15:20:54 2013
alter database recover cancel
Thu Dec 5 15:20:58 2013
Media Recovery Canceled
Completed: alter database recover cancel
Log back into the database and check the file locations:
select name from v$datafile;
NAME
------------------------------------------------------------
+EMSBY_DATA01/EMSBY_base/datafile/cis_idx03.403.833375941
+EMSBY_DATA01/EMSBY_base/datafile/cis_idx02.459.833376693
+EMSBY_DATA01/EMSBY_base/datafile/cis_tab03.457.833376693
+EMSBY_DATA01/EMSBY_base/datafile/cis_tab02.333.833374681
sys.EMSBY> select name from v$controlfile;
NAME
------------------------------------------------------------
+EMSBY_DATA01/EMSBY_base/controlfile/current.541.833378337
1 row selected.
sys.EMSBY> select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------
/oracle/ora_redo/data/EMSBY/redo_04a.log
/oracle/ora_redo/data/EMSBY/redo_03a.log
/oracle/ora_redo/data/EMSBY/redo_02a.log
/oracle/ora_redo/data/EMSBY/redo_01a.log
4 rows selected.
Hmm, the redo logs are still on UFS. I need to move those as well.
sys.EMSBY> Select group#,status from V$LOG;
GROUP# STATUS
---------- ----------------
1 CLEARING
4 CLEARING
3 CLEARING_CURRENT
2 CLEARING
sys.EMSBY> alter database clear logfile group 1;
Database altered.
sys.EMSBY> alter database clear logfile group 4;
Database altered.
sys.EMSBY> alter database clear logfile group 3;
Database altered.
sys.EMSBY> alter database clear logfile group 2;
Database altered.
sys.EMSBY> Select group#,status from V$LOG;
GROUP# STATUS
---------- ----------------
1 UNUSED
4 UNUSED
3 CLEARING_CURRENT
2 UNUSED
4 rows selected.
sys.EMSBY> alter database drop logfile group 1;
Database altered
sys.EMSBY> alter database drop logfile group 2;
Database altered
sys.EMSBY> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance EMSBY (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/oracle/ora_redo/data/EMSBY/redo_03a.log'
I'll come back to this
sys.EMSBY> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01567: dropping log 4 would leave less than 2 log files for instance EMSBY (thread 1)
ORA-00312: online log 4 thread 1: '/oracle/ora_redo/data/EMSBY/redo_04a.log'
OK, need to create the new ones first.
sys.EMSBY> alter database add logfile group 1 size 500m;
Database altered.
sys.EMSBY> alter database add logfile group 2 size 500m;
Database altered.
Back to the log group 3 that was in use:
sys.EMSBY> Select group#,status from V$LOG;
GROUP# STATUS
---------- ----------------
1 UNUSED
4 UNUSED
3 CLEARING
2 CLEARING_CURRENT
4 rows selected.
sys.EMSBY> alter database clear logfile group 3;
Database altered.
sys.EMSBY> Select group#,status from V$LOG;
GROUP# STATUS
---------- ----------------
1 UNUSED
4 UNUSED
3 UNUSED
2 CLEARING_CURRENT
4 rows selected.
Now it's UNUSED, it can be dropped and recreated:
sys.EMSBY> alter database drop logfile group 3;
Database altered.
sys.EMSBY> alter database add logfile group 3 size 500m;
Database altered.
Check the files:
sys.EMSBY> select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------
+EMSBY_DATA01/EMSBY_base/onlinelog/group_4.544.833384439
+EMSBY_DATA01/EMSBY_base/onlinelog/group_3.545.833384617
+EMSBY_DATA01/EMSBY_base/onlinelog/group_1.542.833384411
+EMSBY_DATA01/EMSBY_base/onlinelog/group_2.543.833384419
4 rows selected.
Looks OK, now to turn the log apply back on:
sys.EMSBY> alter system set standby_file_management=auto;
System altered.
sys.EMSBY> alter database recover managed standby database disconnect from session;
Database altered.
Go to PROD and switch some logs, and check the alert log on the standby:
sys.EMSBY> alter system switch logfile;
System altered.
sys.EMSBY> /
System altered.
sys.EMSBY> /
System altered.
Standby alert Log:
RFS[1]: Archived Log: '/apps/oracle/admin/EMSBY/arch/EMSBY_1_479644347_0000427132.arc'
Thu Dec 5 15:27:31 2013
RFS[1]: Archive log thread 1 sequence 427132 available in 1439 minute(s)
Thu Dec 5 15:27:32 2013
Media Recovery Delayed for 1439 minute(s) (thread 1 sequence 427130)
Thu Dec 5 15:27:32 2013
RFS[1]: Archived Log: '/apps/oracle/admin/EMSBY/arch/EMSBY_1_479644347_0000427131.arc'
Thu Dec 5 15:27:32 2013
Looks OK, all done!
