File system to asm migration

File system to asm migration
############################


Check existing details
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.4/db/orcl/system01.dbf
/u01/app/oracle/product/11.2.0.4/db/orcl/sysaux01.dbf
/u01/app/oracle/product/11.2.0.4/db/orcl/undotbs01.dbf
/u01/app/oracle/product/11.2.0.4/db/orcl/users01.dbf
/u01/app/oracle/product/11.2.0.4/db/orcl/example01.dbf

 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8

SQL> show parameter db_recov

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 3882M

SQL> select NAME from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.4/db/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl


SQL> select * from v$Log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          7   52428800        512          1 YES
INACTIVE               1011076 23-JAN-17      1011080 23-JAN-17

         2          1          8   52428800        512          1 NO
CURRENT                1011080 23-JAN-17   2.8147E+14

         3          1          6   52428800        512          1 YES
INACTIVE               1011073 23-JAN-17      1011076 23-JAN-17


[oracle@localhost ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576      5740     5514                0            2757              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576      1928     1874                0            1874              0             N  FRA/
ASMCMD>



#######################################
Actual migration:
#######################################
1)Change the controlfile parameter
ALTER SYSTEM SET control_files='+DATA','+FRA' scope=spfile;

2)change db create parameter and create pfile
ALTER SYSTEM SET db_create_file_dest='+DATA' SCOPE=spfile;
create pfile='/tmp/pfile_orcl.ora' from spfile;

3)Connect to RMAN and fire

RMAN> backup as copy database format '+DATA';

Starting backup at 23-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/product/11.2.0.4/db/orcl/system01.dbf
output file name=+DATA/orcl/datafile/system.257.934043207 tag=TAG20170123T162643 RECID=2 STAMP=934043339
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:17
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/product/11.2.0.4/db/orcl/sysaux01.dbf
output file name=+DATA/orcl/datafile/sysaux.258.934043341 tag=TAG20170123T162643 RECID=3 STAMP=934043449
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:49
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/product/11.2.0.4/db/orcl/example01.dbf
output file name=+DATA/orcl/datafile/example.259.934043451 tag=TAG20170123T162643 RECID=4 STAMP=934043468
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/product/11.2.0.4/db/orcl/undotbs01.dbf
output file name=+DATA/orcl/datafile/undotbs1.260.934043477 tag=TAG20170123T162643 RECID=5 STAMP=934043489
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/orcl/controlfile/backup.261.934043493 tag=TAG20170123T162643 RECID=6 STAMP=934043497
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/product/11.2.0.4/db/orcl/users01.dbf
output file name=+DATA/orcl/datafile/users.262.934043501 tag=TAG20170123T162643 RECID=7 STAMP=934043501
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 23-JAN-17
channel ORA_DISK_1: finished piece 1 at 23-JAN-17
piece handle=+DATA/orcl/backupset/2017_01_23/nnsnf0_tag20170123t162643_0.263.934043501 tag=TAG20170123T162643 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JAN-17

RMAN>

4)restart db and go to no mount state
connect to RMAN

RMAN> restore controlfile from '/u01/app/oracle/product/11.2.0.4/db/orcl/control01.ctl';

Starting restore at 23-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.264.934044723
output file name=+FRA/orcl/controlfile/current.256.934044729
Finished restore at 23-JAN-17

--Controlfile restored to new location by using modified parameters

5)to change path in controlfile

RMAN> switch database  to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.257.934043207"
datafile 2 switched to datafile copy "+DATA/orcl/datafile/sysaux.258.934043341"
datafile 3 switched to datafile copy "+DATA/orcl/datafile/undotbs1.260.934043477"
datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.262.934043501"
datafile 5 switched to datafile copy "+DATA/orcl/datafile/example.259.934043451"

6)Recover database

RMAN> recover database;

Starting recover at 23-JAN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:10

Finished recover at 23-JAN-17

7)Now only redolog file migration is pending
a)change parameter
alter system set db_create_online_log_dest_1='+FRA';

b)add logfile group
alter database add logfile group 4;
alter database add logfile group 5;
alter database add logfile group 6;

c)it will be created on ASM location now drop existing one

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

group 3 will not get drop because file showing status as current
SQL>  select GROUP#,THREAD#,MEMBERS,STATUS from v$log;

    GROUP#    THREAD#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         3          1          1 CURRENT
         4          1          1 UNUSED
         5          1          1 UNUSED
         6          1          1 UNUSED

For that
SQL> alter system switch logfile;

System altered.

still it will show status as active

SQL> alter system checkpoint;

System altered.


and drop the group 3 redolog
alter database drop logfile group 3;




****************************Migration Complete to verify*****************************

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/system.257.934043207
+DATA/orcl/datafile/sysaux.258.934043341
+DATA/orcl/datafile/undotbs1.260.934043477
+DATA/orcl/datafile/users.262.934043501
+DATA/orcl/datafile/example.259.934043451


 SQL> select NAME from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/controlfile/current.264.934044723
+FRA/orcl/controlfile/current.256.934044729


SQL> select GROUP#,MEMBER from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         4 +FRA/orcl/onlinelog/group_4.257.934045745
         5 +FRA/orcl/onlinelog/group_5.258.934045773
         6 +FRA/orcl/onlinelog/group_6.259.934045781


****************************************************END*************************************************