Migrating databases from NON-ASM to ASM in oracle 12c

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.

No comments:

Post a Comment