Pre Reqs:
1. Make Sure Grid Infra Home is installed and the ASM instance is running on the node
2. Take backup of your database
RMAN\> backup database plus archivelog;
Starting backup at 09-NOV-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=1 STAMP=756911083
input archived log thread=1 sequence=5 RECID=2 STAMP=756911271
input archived log thread=1 sequence=6 RECID=3 STAMP=756911547
input archived log thread=1 sequence=7 RECID=4 STAMP=756911661
input archived log thread=1 sequence=8 RECID=5 STAMP=756911790
input archived log thread=1 sequence=9 RECID=6 STAMP=756911976
input archived log thread=1 sequence=10 RECID=7 STAMP=756912183
…………..
………………………….
…………………………………..
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2011_11_09/o1_mf_annnn_TAG20111109T202834_7cormmow_.bkp tag=TAG20111109T202834 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 09-NOV-11
Starting Control File and SPFILE Autobackup at 09-NOV-11
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2011_11_09/o1_mf_s_766787318_7cormq6v_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-NOV-11
3. Detemine the names of Current Datafiles and the Controlfiles:
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/redo03.log
/u01/app/oracle/oradata/prim/redo02.log
/u01/app/oracle/oradata/prim/redo01.log
SQL> sho parameter CONTROL
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/prim/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/prim/contr
ol02.ctl
4. Disk Based Migration
To perform the migration, carry out the following steps:
A. Back up your database files as copies to the ASM disk group.
BACKUP DATABASE
FORMAT '+DATA01' TAG 'ORA_ASM_MIGRATION';
You can perform this backup with multiple channels to improve performance, depending upon your hardware configuration. For example:
run {
Allocate channel prm01 type disk;
Allocate channel prm02 type disk;
Allocate channel prm03 type disk;
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE FORMAT '+DATA' TAG 'ORACLE_TO_ASM_MIGRATION';
}
[oracle@dba clust]$ rman target /
Connected to target database: PRIM (DBID=4049196380)
RMAN> run {
Allocate channel prm01 type disk;
Allocate channel prm02 type disk;
Allocate channel prm03 type disk;
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE FORMAT '+DATA' TAG 'ORACLE_TO_ASM_MIGRATION';
2> 3> 4> 5> 6> }
using target database control file instead of recovery catalog
allocated channel: prm01
channel prm01: SID=34 device type=DISK
allocated channel: prm02
channel prm02: SID=36 device type=DISK
allocated channel: prm03
channel prm03: SID=30 device type=DISK
Starting backup at 10-DEC-11
channel prm01: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/prim/system01.dbf
channel prm02: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/prim/sysaux01.dbf
channel prm03: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/prim/undotbs01.dbf
output file name=+DATA/prim/datafile/undotbs1.258.769546347 tag=ORACLE_TO_ASM_MIGRATION RECID=14 STAMP=769546368
channel prm03: datafile copy complete, elapsed time: 00:00:45
channel prm03: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
channel prm03: datafile copy complete, elapsed time: 00:00:10
channel prm03: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
output file name=+DATA/prim/datafile/users.260.769546397 tag=ORACLE_TO_ASM_MIGRATION RECID=16 STAMP=769546399
channel prm03: datafile copy complete, elapsed time: 00:00:03
hannel prm03: datafile copy complete, elapsed time: 00:00:45
channel prm03: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
output file name=+DATA/prim/datafile/example.259.769546387 tag=ORACLE_TO_ASM_MIGRATION RECID=15 STAMP=769546393
channel prm03: datafile copy complete, elapsed time: 00:00:10
channel prm03: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
output file name=+DATA/prim/datafile/users.260.769546397 tag=ORACLE_TO_ASM_MIGRATION RECID=16 STAMP=769546399
channel prm03: datafile copy complete, elapsed time: 00:00:03
output file name=+DATA/prim/datafile/sysaux.257.769546345 tag=ORACLE_TO_ASM_MIGRATION RECID=17 STAMP=769546406
channel prm02: datafile copy complete, elapsed time: 00:01:10
output file name=+DATA/prim/datafile/system.256.769546345 tag=ORACLE_TO_ASM_MIGRATION RECID=18 STAMP=769546413
channel prm01: datafile copy complete, elapsed time: 00:01:20
Finished backup at 10-DEC-11
Starting Control File and SPFILE Autobackup at 10-DEC-11
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2011_12_10/o1_mf_s_769546416_7g7bokb6_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 10-DEC-11
Finished Control File and SPFILE Autobackup at 10-DEC-11
released channel: prm01
released channel: prm02
released channel: prm03
B. Take backup of SPFILE:
run {
BACKUP AS BACKUPSET SPFILE;
RESTORE SPFILE TO "+DATA/spfile";
}
RMAN> run {
BACKUP AS BACKUPSET SPFILE;
RESTORE SPFILE TO "+DATA/spfile";
}2> 3> 4>
Starting backup at 10-DEC-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
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 10-DEC-11
channel ORA_DISK_1: finished piece 1 at 10-DEC-11
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2011_12_10/o1_mf_nnsnf_TAG20111210T190101_7g7c3gdf_.bkp tag=TAG20111210T190101 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-DEC-11
Starting Control File and SPFILE Autobackup at 10-DEC-11
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2011_12_10/o1_mf_s_769546864_7g7c3jvv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 10-DEC-11
arting restore at 10-DEC-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/spfile
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PRIM/autobackup/2011_12_10/o1_mf_s_769546864_7g7c3jvv_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2011_12_10/o1_mf_s_769546864_7g7c3jvv_.bkp tag=TAG20111210T190104
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 10-DEC-11
C. Create an init.ora specifying the location of the new SPFILE, and start the instance with it. For example, create ORACLE_HOME/dbs/initprim.ora with the following contents:
SPFILE=+DATA/spfile
D. Shutdown and startup to make sure CONTROL_FILES has taken the latest value.
Before Restart:
SQL> sho parameter CONTROL_FILES;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/prim/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/prim/contr
ol02.ctl
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initprim.ora'
ORACLE instance started.
Total System Global Area 401743872 bytes
Fixed Size 1336820 bytes
Variable Size 343935500 bytes
Database Buffers 50331648 bytes
Redo Buffers 6139904 bytes
SQL> alter system set control_files='+DATA/control01.ctl','+DATA/control02.ctl' scope=spfile sid='*';
System altered.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initprim.ora'
ORACLE instance started.
otal System Global Area 401743872 bytes
Fixed Size 1336820 bytes
Variable Size 343935500 bytes
Database Buffers 50331648 bytes
Redo Buffers 6139904 bytes
E. Create controlfile(On ASM DISK) using RMAN using your current controlfile(On Unix File System):
[oracle@dba dbs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Dec 10 19:41:35 2011
connected to target database: PRIM (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/oradata/prim/control01.ctl'
2> ;
Starting restore at 10-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/control01.ctl
output file name=+DATA/control02.ctl
Finished restore at 10-DEC-11
F. Mount the database and switch the database to copy:
RMAN> restore controlfile from '/u01/app/oracle/oradata/prim/control01.ctl'
2> ;
Starting restore at 10-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/control01.ctl
output file name=+DATA/control02.ctl
Finished restore at 10-DEC-11
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> switch database to copy;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATA/prim/datafile/system.256.769546345"
datafile 2 switched to datafile copy "+DATA/prim/datafile/sysaux.257.769546345"
datafile 3 switched to datafile copy "+DATA/prim/datafile/undotbs1.258.769546347"
datafile 4 switched to datafile copy "+DATA/prim/datafile/users.260.769546397"
datafile 5 switched to datafile copy "+DATA/prim/datafile/example.259.769546387"
G. Recover the Database
RMAN> recover database;
Starting recover at 10-DEC-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 10-DEC-11
H. The change tracking file cannot be migrated. You can only disable change tracking, then re-enable it, specifying an ASM disk location for the change tracking file:
SQL> alter database disable block change tracking;
alter database disable block change tracking
*
ERROR at line 1:
ORA-19759: block change tracking is not enabled
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DATA';
Database altered.
Info: Block change tracking causes the changed database blocks to be flagged in a file. As the data blocks gets dirty (changed), the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory. When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer. During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the change-tracking file.To achieve this we need to enable the block change tracking in our database
Confirmation:
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/prim/datafile/users.260.769546397
+DATA/prim/datafile/undotbs1.258.769546347
+DATA/prim/datafile/sysaux.257.769546345
+DATA/prim/datafile/system.256.769546345
+DATA/prim/datafile/example.259.769546387
I. Migrate Online Redologs of Primary database to ASM
declare
cursor rlc is
select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
from v$log
union
select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
from v$standby_log
order by 1;
stmt varchar2(2048);
swtstmt varchar2(1024) := 'alter system switch logfile';
ckpstmt varchar2(1024) := 'alter system checkpoint global';
begin
for rlcRec in rlc loop
if (rlcRec.srl = 'YES') then
stmt := 'alter database add standby logfile thread '
rlcRec.thr
' ''+DATA'' size '
rlcRec.bytes_k
'K';
execute immediate stmt;
stmt := 'alter database drop standby logfile group '
rlcRec.grp;
execute immediate stmt;
else
stmt := 'alter database add logfile thread '
rlcRec.thr
' ''+DATA'' size '
rlcRec.bytes_k
'K';
execute immediate stmt;
begin
stmt := 'alter database drop logfile group '
rlcRec.grp;
dbms_output.put_line(stmt);
execute immediate stmt;
exception
when others then
execute immediate swtstmt;
execute immediate ckpstmt;
execute immediate stmt;
end;
end if;
end loop;
end;
PL/SQL procedure successfully completed.
L> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 STANDBY
+DATA/prim/onlinelog/group_3.268.769550357
NO
2 ONLINE
+DATA/prim/onlinelog/group_2.267.769550355
NO
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
1 ONLINE
+DATA/prim/onlinelog/group_1.266.769550347
NO
4 STANDBY
+DATA/prim/onlinelog/group_4.269.769550359
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
NO
5 STANDBY
+DATA/prim/onlinelog/group_5.270.769550361
NO
ONLINE
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
+DATA/prim/onlinelog/group_7.265.769550345
NO
1. Make Sure Grid Infra Home is installed and the ASM instance is running on the node
2. Take backup of your database
RMAN\> backup database plus archivelog;
Starting backup at 09-NOV-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=1 STAMP=756911083
input archived log thread=1 sequence=5 RECID=2 STAMP=756911271
input archived log thread=1 sequence=6 RECID=3 STAMP=756911547
input archived log thread=1 sequence=7 RECID=4 STAMP=756911661
input archived log thread=1 sequence=8 RECID=5 STAMP=756911790
input archived log thread=1 sequence=9 RECID=6 STAMP=756911976
input archived log thread=1 sequence=10 RECID=7 STAMP=756912183
…………..
………………………….
…………………………………..
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2011_11_09/o1_mf_annnn_TAG20111109T202834_7cormmow_.bkp tag=TAG20111109T202834 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 09-NOV-11
Starting Control File and SPFILE Autobackup at 09-NOV-11
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2011_11_09/o1_mf_s_766787318_7cormq6v_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-NOV-11
3. Detemine the names of Current Datafiles and the Controlfiles:
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/redo03.log
/u01/app/oracle/oradata/prim/redo02.log
/u01/app/oracle/oradata/prim/redo01.log
SQL> sho parameter CONTROL
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/prim/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/prim/contr
ol02.ctl
4. Disk Based Migration
To perform the migration, carry out the following steps:
A. Back up your database files as copies to the ASM disk group.
BACKUP DATABASE
FORMAT '+DATA01' TAG 'ORA_ASM_MIGRATION';
You can perform this backup with multiple channels to improve performance, depending upon your hardware configuration. For example:
run {
Allocate channel prm01 type disk;
Allocate channel prm02 type disk;
Allocate channel prm03 type disk;
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE FORMAT '+DATA' TAG 'ORACLE_TO_ASM_MIGRATION';
}
[oracle@dba clust]$ rman target /
Connected to target database: PRIM (DBID=4049196380)
RMAN> run {
Allocate channel prm01 type disk;
Allocate channel prm02 type disk;
Allocate channel prm03 type disk;
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE FORMAT '+DATA' TAG 'ORACLE_TO_ASM_MIGRATION';
2> 3> 4> 5> 6> }
using target database control file instead of recovery catalog
allocated channel: prm01
channel prm01: SID=34 device type=DISK
allocated channel: prm02
channel prm02: SID=36 device type=DISK
allocated channel: prm03
channel prm03: SID=30 device type=DISK
Starting backup at 10-DEC-11
channel prm01: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/prim/system01.dbf
channel prm02: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/prim/sysaux01.dbf
channel prm03: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/prim/undotbs01.dbf
output file name=+DATA/prim/datafile/undotbs1.258.769546347 tag=ORACLE_TO_ASM_MIGRATION RECID=14 STAMP=769546368
channel prm03: datafile copy complete, elapsed time: 00:00:45
channel prm03: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
channel prm03: datafile copy complete, elapsed time: 00:00:10
channel prm03: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
output file name=+DATA/prim/datafile/users.260.769546397 tag=ORACLE_TO_ASM_MIGRATION RECID=16 STAMP=769546399
channel prm03: datafile copy complete, elapsed time: 00:00:03
hannel prm03: datafile copy complete, elapsed time: 00:00:45
channel prm03: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
output file name=+DATA/prim/datafile/example.259.769546387 tag=ORACLE_TO_ASM_MIGRATION RECID=15 STAMP=769546393
channel prm03: datafile copy complete, elapsed time: 00:00:10
channel prm03: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
output file name=+DATA/prim/datafile/users.260.769546397 tag=ORACLE_TO_ASM_MIGRATION RECID=16 STAMP=769546399
channel prm03: datafile copy complete, elapsed time: 00:00:03
output file name=+DATA/prim/datafile/sysaux.257.769546345 tag=ORACLE_TO_ASM_MIGRATION RECID=17 STAMP=769546406
channel prm02: datafile copy complete, elapsed time: 00:01:10
output file name=+DATA/prim/datafile/system.256.769546345 tag=ORACLE_TO_ASM_MIGRATION RECID=18 STAMP=769546413
channel prm01: datafile copy complete, elapsed time: 00:01:20
Finished backup at 10-DEC-11
Starting Control File and SPFILE Autobackup at 10-DEC-11
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2011_12_10/o1_mf_s_769546416_7g7bokb6_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 10-DEC-11
Finished Control File and SPFILE Autobackup at 10-DEC-11
released channel: prm01
released channel: prm02
released channel: prm03
B. Take backup of SPFILE:
run {
BACKUP AS BACKUPSET SPFILE;
RESTORE SPFILE TO "+DATA/spfile";
}
RMAN> run {
BACKUP AS BACKUPSET SPFILE;
RESTORE SPFILE TO "+DATA/spfile";
}2> 3> 4>
Starting backup at 10-DEC-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
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 10-DEC-11
channel ORA_DISK_1: finished piece 1 at 10-DEC-11
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2011_12_10/o1_mf_nnsnf_TAG20111210T190101_7g7c3gdf_.bkp tag=TAG20111210T190101 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-DEC-11
Starting Control File and SPFILE Autobackup at 10-DEC-11
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2011_12_10/o1_mf_s_769546864_7g7c3jvv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 10-DEC-11
arting restore at 10-DEC-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/spfile
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PRIM/autobackup/2011_12_10/o1_mf_s_769546864_7g7c3jvv_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2011_12_10/o1_mf_s_769546864_7g7c3jvv_.bkp tag=TAG20111210T190104
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 10-DEC-11
C. Create an init.ora specifying the location of the new SPFILE, and start the instance with it. For example, create ORACLE_HOME/dbs/initprim.ora with the following contents:
SPFILE=+DATA/spfile
D. Shutdown and startup to make sure CONTROL_FILES has taken the latest value.
Before Restart:
SQL> sho parameter CONTROL_FILES;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/prim/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/prim/contr
ol02.ctl
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initprim.ora'
ORACLE instance started.
Total System Global Area 401743872 bytes
Fixed Size 1336820 bytes
Variable Size 343935500 bytes
Database Buffers 50331648 bytes
Redo Buffers 6139904 bytes
SQL> alter system set control_files='+DATA/control01.ctl','+DATA/control02.ctl' scope=spfile sid='*';
System altered.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initprim.ora'
ORACLE instance started.
otal System Global Area 401743872 bytes
Fixed Size 1336820 bytes
Variable Size 343935500 bytes
Database Buffers 50331648 bytes
Redo Buffers 6139904 bytes
E. Create controlfile(On ASM DISK) using RMAN using your current controlfile(On Unix File System):
[oracle@dba dbs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Dec 10 19:41:35 2011
connected to target database: PRIM (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/oradata/prim/control01.ctl'
2> ;
Starting restore at 10-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/control01.ctl
output file name=+DATA/control02.ctl
Finished restore at 10-DEC-11
F. Mount the database and switch the database to copy:
RMAN> restore controlfile from '/u01/app/oracle/oradata/prim/control01.ctl'
2> ;
Starting restore at 10-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/control01.ctl
output file name=+DATA/control02.ctl
Finished restore at 10-DEC-11
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> switch database to copy;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATA/prim/datafile/system.256.769546345"
datafile 2 switched to datafile copy "+DATA/prim/datafile/sysaux.257.769546345"
datafile 3 switched to datafile copy "+DATA/prim/datafile/undotbs1.258.769546347"
datafile 4 switched to datafile copy "+DATA/prim/datafile/users.260.769546397"
datafile 5 switched to datafile copy "+DATA/prim/datafile/example.259.769546387"
G. Recover the Database
RMAN> recover database;
Starting recover at 10-DEC-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 10-DEC-11
H. The change tracking file cannot be migrated. You can only disable change tracking, then re-enable it, specifying an ASM disk location for the change tracking file:
SQL> alter database disable block change tracking;
alter database disable block change tracking
*
ERROR at line 1:
ORA-19759: block change tracking is not enabled
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DATA';
Database altered.
Info: Block change tracking causes the changed database blocks to be flagged in a file. As the data blocks gets dirty (changed), the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory. When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer. During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the change-tracking file.To achieve this we need to enable the block change tracking in our database
Confirmation:
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/prim/datafile/users.260.769546397
+DATA/prim/datafile/undotbs1.258.769546347
+DATA/prim/datafile/sysaux.257.769546345
+DATA/prim/datafile/system.256.769546345
+DATA/prim/datafile/example.259.769546387
I. Migrate Online Redologs of Primary database to ASM
declare
cursor rlc is
select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
from v$log
union
select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
from v$standby_log
order by 1;
stmt varchar2(2048);
swtstmt varchar2(1024) := 'alter system switch logfile';
ckpstmt varchar2(1024) := 'alter system checkpoint global';
begin
for rlcRec in rlc loop
if (rlcRec.srl = 'YES') then
stmt := 'alter database add standby logfile thread '
rlcRec.thr
' ''+DATA'' size '
rlcRec.bytes_k
'K';
execute immediate stmt;
stmt := 'alter database drop standby logfile group '
rlcRec.grp;
execute immediate stmt;
else
stmt := 'alter database add logfile thread '
rlcRec.thr
' ''+DATA'' size '
rlcRec.bytes_k
'K';
execute immediate stmt;
begin
stmt := 'alter database drop logfile group '
rlcRec.grp;
dbms_output.put_line(stmt);
execute immediate stmt;
exception
when others then
execute immediate swtstmt;
execute immediate ckpstmt;
execute immediate stmt;
end;
end if;
end loop;
end;
PL/SQL procedure successfully completed.
L> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 STANDBY
+DATA/prim/onlinelog/group_3.268.769550357
NO
2 ONLINE
+DATA/prim/onlinelog/group_2.267.769550355
NO
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
1 ONLINE
+DATA/prim/onlinelog/group_1.266.769550347
NO
4 STANDBY
+DATA/prim/onlinelog/group_4.269.769550359
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
NO
5 STANDBY
+DATA/prim/onlinelog/group_5.270.769550361
NO
ONLINE
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
+DATA/prim/onlinelog/group_7.265.769550345
NO
