Migrating databases from NON-ASM to ASM in oracle 12c
Step 1.
Login to the database and write down the path of all the datafiles, tempfiles and redo log files.
SQL> select status,version,instance_name from v$instance;
STATUS VERSION INSTANCE_NAME
------------ ----------------- ----------------
OPEN 12.1.0.2.0 orclnew
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------------------
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_users_dcldb9g3_.dbf USERS
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_undotbs1_dcldbbm9_.dbf UNDOTBS1
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_system_dcld7ty2_.dbf SYSTEM
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_sysaux_dcld611r_.dbf SYSAUX
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/ORCLNE
W/controlfile/o1_mf_dcldcd09_.
ctl
Step 2. Lets find out what are the disks available in ask disk group along with the free space.
SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;
NAME STATE TOTAL_MB PATH
------------------------------ -------- ---------- -------------------------
VOL1 NORMAL 9209 ORCL:VOL1
VOL2 NORMAL 9209 ORCL:VOL2
VOL3 NORMAL 9209 ORCL:VOL3
SQL> select NAME,GROUP_NUMBER,FREE_MB,TOTAL_MB from v$asm_diskgroup;
NAME GROUP_NUMBER FREE_MB TOTAL_MB
------------------------------ ------------ ---------- ----------
DATA 1 23719 27627
Now set the control file to point the ASM disk group DATA.
SQL> alter system set control_files='+DATA' scope=spfile;
System altered.
Step 3. Since our database in running on archivelog mode, lets change the db_recovery_file_dest location to ASM diskgroup DATA.
SQL> alter system set db_recovery_file_dest='+DATA' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest_SIZE=200M scope=spfile;
System altered.
Step 4. Shutdown and start the database in nomount stage.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 440401920 bytes
Fixed Size 2925360 bytes
Variable Size 360713424 bytes
Database Buffers 71303168 bytes
Redo Buffers 5459968 bytes
SQL>
Step 5.
Connect to RMAN and restore the controlfile from the file system to the disk group DATA.
[oracle@server3 ~]$ rman target sys/redhat
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Mar 3 15:12:56 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLNEW (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/oradata/ORCLNEW/controlfile/o1_mf_dcldcd09_.ctl';
Starting restore at 03-MAR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/ORCLNEW/CONTROLFILE/current.271.937667637
Finished restore at 03-MAR-17
Step 6. After restoring the controlfile mount the database from rman.
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
Step 7. Now take the backup of the database in the ASM disk group DATA.
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
Starting backup at 03-MAR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_system_dcld7ty2_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/system.272.937667983 tag=TAG20170303T151941 RECID=1 STAMP=937668020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_sysaux_dcld611r_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/sysaux.273.937668029 tag=TAG20170303T151941 RECID=2 STAMP=937668059
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_undotbs1_dcldbbm9_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/undotbs1.274.937668065 tag=TAG20170303T151941 RECID=3 STAMP=937668068
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/ORCLNEW/CONTROLFILE/backup.275.937668073 tag=TAG20170303T151941 RECID=4 STAMP=937668074
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_users_dcldb9g3_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/users.276.937668075 tag=TAG20170303T151941 RECID=5 STAMP=937668075
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 03-MAR-17
channel ORA_DISK_1: finished piece 1 at 03-MAR-17
piece handle=+DATA/ORCLNEW/BACKUPSET/2017_03_03/nnsnf0_tag20170303t151941_0.277.937668077 tag=TAG20170303T151941 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-MAR-17
Step 8. Now change the NON ASM database to ASM by running the below command.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/system.272.937667983"
datafile 3 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/sysaux.273.937668029"
datafile 4 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/undotbs1.274.937668065"
datafile 6 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/users.276.937668075"
RMAN>
Step 9. Now once the datafiles have been moved into ASM diskgroup we can open the database.
RMAN> alter database open;
Statement processed
Step 10. Once database has been opened determine the datafiles location
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/ORCLNEW/DATAFILE/users.276.937668075 USERS
+DATA/ORCLNEW/DATAFILE/undotbs1.274.937668065 UNDOTBS1
+DATA/ORCLNEW/DATAFILE/system.272.937667983 SYSTEM
+DATA/ORCLNEW/DATAFILE/sysaux.273.937668029 SYSAUX
SQL>
Step 11.After moving the datafiles into ASM diskgroup , we still need to move the temp tablespace and redolog files into ASM diskgroup.
SQL> select file_name,tablespace_name from dba_Temp_Files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_temp_dcldd5h8_.tmp TEMP
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------------------------------------
3 /u01/app/oracle/oradata/ORCLNEW/onlinelog/o1_mf_3_dcldchys_.log
2 /u01/app/oracle/oradata/ORCLNEW/onlinelog/o1_mf_2_dcldcgk9_.log
1 /u01/app/oracle/oradata/ORCLNEW/onlinelog/o1_mf_1_dcldcfg7_.log
Step 11. To move the temp tablespace we need to drop the existing temp files first then recreate it into ASM diskgroup
SQL> alter database tempfile '/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_temp_dcldd5h8_.tmp' drop including datafiles;
Database altered.
SQL> alter tablespace TEMP add tempfile '+DATA' size 150M autoextend on;
Tablespace altered.
SQL> select file_name,tablespace_name from dba_Temp_Files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/ORCLNEW/TEMPFILE/temp.278.937669453 TEMP
SQL>
Step 12. Now move the redolog group into ASM diskgroup
For this first we will drop the inactive redo log group and then recreate it.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
Here log group 2 and log group 3 are INACTIVE. Hence, we can drop them and recreate the log groups 2 and 3.
Change default location of online redo log files as +DATA disk group.
SQL> alter system set db_create_online_log_dest_1='+DATA' SCOPE=BOTH;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2;
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3;
SQL>alter system switch logfile;
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------------------------------------- --- ----------
3 ONLINE +DATA/ORCLNEW/ONLINELOG/group_3.280.937672657 NO 0
2 ONLINE +DATA/ORCLNEW/ONLINELOG/group_2.279.937672551 NO 0
1 ONLINE +DATA/ORCLNEW/ONLINELOG/group_1.285.937672783 NO 0
Now finally all physical datafiles/logfiles have been moved from NON ASM TO ASM.
Step 1.
Login to the database and write down the path of all the datafiles, tempfiles and redo log files.
SQL> select status,version,instance_name from v$instance;
STATUS VERSION INSTANCE_NAME
------------ ----------------- ----------------
OPEN 12.1.0.2.0 orclnew
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------------------
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_users_dcldb9g3_.dbf USERS
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_undotbs1_dcldbbm9_.dbf UNDOTBS1
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_system_dcld7ty2_.dbf SYSTEM
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_sysaux_dcld611r_.dbf SYSAUX
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/ORCLNE
W/controlfile/o1_mf_dcldcd09_.
ctl
Step 2. Lets find out what are the disks available in ask disk group along with the free space.
SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;
NAME STATE TOTAL_MB PATH
------------------------------ -------- ---------- -------------------------
VOL1 NORMAL 9209 ORCL:VOL1
VOL2 NORMAL 9209 ORCL:VOL2
VOL3 NORMAL 9209 ORCL:VOL3
SQL> select NAME,GROUP_NUMBER,FREE_MB,TOTAL_MB from v$asm_diskgroup;
NAME GROUP_NUMBER FREE_MB TOTAL_MB
------------------------------ ------------ ---------- ----------
DATA 1 23719 27627
Now set the control file to point the ASM disk group DATA.
SQL> alter system set control_files='+DATA' scope=spfile;
System altered.
Step 3. Since our database in running on archivelog mode, lets change the db_recovery_file_dest location to ASM diskgroup DATA.
SQL> alter system set db_recovery_file_dest='+DATA' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest_SIZE=200M scope=spfile;
System altered.
Step 4. Shutdown and start the database in nomount stage.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 440401920 bytes
Fixed Size 2925360 bytes
Variable Size 360713424 bytes
Database Buffers 71303168 bytes
Redo Buffers 5459968 bytes
SQL>
Step 5.
Connect to RMAN and restore the controlfile from the file system to the disk group DATA.
[oracle@server3 ~]$ rman target sys/redhat
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Mar 3 15:12:56 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLNEW (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/oradata/ORCLNEW/controlfile/o1_mf_dcldcd09_.ctl';
Starting restore at 03-MAR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/ORCLNEW/CONTROLFILE/current.271.937667637
Finished restore at 03-MAR-17
Step 6. After restoring the controlfile mount the database from rman.
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
Step 7. Now take the backup of the database in the ASM disk group DATA.
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
Starting backup at 03-MAR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_system_dcld7ty2_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/system.272.937667983 tag=TAG20170303T151941 RECID=1 STAMP=937668020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_sysaux_dcld611r_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/sysaux.273.937668029 tag=TAG20170303T151941 RECID=2 STAMP=937668059
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_undotbs1_dcldbbm9_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/undotbs1.274.937668065 tag=TAG20170303T151941 RECID=3 STAMP=937668068
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/ORCLNEW/CONTROLFILE/backup.275.937668073 tag=TAG20170303T151941 RECID=4 STAMP=937668074
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_users_dcldb9g3_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/users.276.937668075 tag=TAG20170303T151941 RECID=5 STAMP=937668075
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 03-MAR-17
channel ORA_DISK_1: finished piece 1 at 03-MAR-17
piece handle=+DATA/ORCLNEW/BACKUPSET/2017_03_03/nnsnf0_tag20170303t151941_0.277.937668077 tag=TAG20170303T151941 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-MAR-17
Step 8. Now change the NON ASM database to ASM by running the below command.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/system.272.937667983"
datafile 3 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/sysaux.273.937668029"
datafile 4 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/undotbs1.274.937668065"
datafile 6 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/users.276.937668075"
RMAN>
Step 9. Now once the datafiles have been moved into ASM diskgroup we can open the database.
RMAN> alter database open;
Statement processed
Step 10. Once database has been opened determine the datafiles location
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/ORCLNEW/DATAFILE/users.276.937668075 USERS
+DATA/ORCLNEW/DATAFILE/undotbs1.274.937668065 UNDOTBS1
+DATA/ORCLNEW/DATAFILE/system.272.937667983 SYSTEM
+DATA/ORCLNEW/DATAFILE/sysaux.273.937668029 SYSAUX
SQL>
Step 11.After moving the datafiles into ASM diskgroup , we still need to move the temp tablespace and redolog files into ASM diskgroup.
SQL> select file_name,tablespace_name from dba_Temp_Files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_temp_dcldd5h8_.tmp TEMP
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------------------------------------
3 /u01/app/oracle/oradata/ORCLNEW/onlinelog/o1_mf_3_dcldchys_.log
2 /u01/app/oracle/oradata/ORCLNEW/onlinelog/o1_mf_2_dcldcgk9_.log
1 /u01/app/oracle/oradata/ORCLNEW/onlinelog/o1_mf_1_dcldcfg7_.log
Step 11. To move the temp tablespace we need to drop the existing temp files first then recreate it into ASM diskgroup
SQL> alter database tempfile '/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_temp_dcldd5h8_.tmp' drop including datafiles;
Database altered.
SQL> alter tablespace TEMP add tempfile '+DATA' size 150M autoextend on;
Tablespace altered.
SQL> select file_name,tablespace_name from dba_Temp_Files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/ORCLNEW/TEMPFILE/temp.278.937669453 TEMP
SQL>
Step 12. Now move the redolog group into ASM diskgroup
For this first we will drop the inactive redo log group and then recreate it.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
Here log group 2 and log group 3 are INACTIVE. Hence, we can drop them and recreate the log groups 2 and 3.
Change default location of online redo log files as +DATA disk group.
SQL> alter system set db_create_online_log_dest_1='+DATA' SCOPE=BOTH;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2;
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3;
SQL>alter system switch logfile;
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------------------------------------- --- ----------
3 ONLINE +DATA/ORCLNEW/ONLINELOG/group_3.280.937672657 NO 0
2 ONLINE +DATA/ORCLNEW/ONLINELOG/group_2.279.937672551 NO 0
1 ONLINE +DATA/ORCLNEW/ONLINELOG/group_1.285.937672783 NO 0
Now finally all physical datafiles/logfiles have been moved from NON ASM TO ASM.
