Migrate Non-ASM to ASM instance in 12c

by

Check the status of ASM instance .

1
2
3
4
5
SQL> SELECT INSTANCE_NAME,VERSION,STATUS FROM V$INSTANCE;
 
INSTANCE_NAME    VERSION         STATUS
-------------    --------  ----------------
+ASM           12.1.0.2.0         STARTED

Check the database version and its status.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;
 
NAME      OPEN_MODE
--------- --------------------
SHA      READ WRITE
 
SQL> SELECT * FROM V$VERSION;
 
BANNER
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0  Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Check ASM disk name available from the instance which we created earlier

1
2
3
4
5
SQL> SELECT NAME,STATE,TOTAL_MB,FREE_MB,PATH FROM V$ASM_DISK;
 
NAME     STATE      TOTAL_MB        FREE_MB        PATH
------  ----------  -------------- ----------- -----------
DATA     NORMAL       5114           3169         ORCL:DATA

If you are using a spfile, take a backup of spfile using rman to restore it to ASM disk.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[oracle@sha-infotech ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jan 25 13:18:36 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SHA (DBID=2167081579)
 
RMAN> BACKUP AS BACKUPSET SPFILE;
 
Starting backup at 25-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 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 25-JAN-19
channel ORA_DISK_1: finished piece 1 at 25-JAN-19
piece handle=/u01/app/oracle/fast_recovery_area/SHA/backupset/2019_01_25/o1_mf_nnsnf_TAG20190125T131849_g4ohv1tj_.bkp tag=TAG20190125T131849 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-JAN-19

Now restore the spfile from the backup to ASM disk as below

1
2
3
4
5
6
7
8
9
10
11
12
RMAN> RESTORE SPFILE TO '+DATA/SHA/spfilesha.ora';
 
Starting restore at 25-JAN-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/SHA/spfilesha.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/SHA/backupset/2019_01_25/o1_mf_nnsnf_TAG20190125T131849_g4ohv1tj_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SHA/backupset/2019_01_25/o1_mf_nnsnf_TAG20190125T131849_g4ohv1tj_.bkp tag=TAG20190125T131849
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 25-JAN-19

Delete spfile<sid>.ora and init<sid>.ora from the $ORACLE_HOME/dbs directory and create a new init<sid>.ora and point out spfile asm location in the new pfile.

1
2
3
4
5
[oracle@sha-infotech ~]$ cd $ORACLE_HOME/dbs
[oracle@sha-infotech dbs]$ mv spfilesha.ora spfilesha.ora_old
[oracle@sha-infotech dbs]$ vi initsha.ora
[oracle@sha-infotech dbs]$ cat initsha.ora
 SPFILE='+DATA/SHA/spfilesha.ora'

Bounce the database and check whether spfile got restored in asm disk.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
First it will search for spfile&lt;sid>.ora which we deleted and next it will look for init&lt;sid>.ora which we have moified with the above content.
 
[oracle@sha-infotech dbs]$  sqlplus / as sysdba
 
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 25 13:27:34 2019
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
SQL> shu immediate
 
SQL> startup
 
SQL> show parameter spfile
 
NAME        TYPE                     VALUE
------     -----------          ------------------------------
spfile      string               +DATA/SHA/spfilesha.ora

Check control file, datafile and redo logfile locations in database instance.

1
2
3
4
5
SQL> select name from v$controlfile;
 
SQL>select name from v$datafile;
 
SQL>select member from v$logfile;

 Startup the database in nomount stage & try to move control files to ASM using RMAN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[oracle@sha-infotech ~]$ rman target /
 
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jan 25 13:30:08 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SHA (not mounted)
 
RMAN> restore controlfile to '+DATA' from '/u01/app/oracle/sha/control01.ctl';
 
Starting restore at 25-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 25-JAN-19

Check whether the control file is created.

1
2
3
4
5
6
7
8
9
10
11
[oracle@sha-infotech ~]$ . oraenv
ORACLE_SID = [sha] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
 
[oracle@sha-infotech ~]$  asmcmd
 
ASMCMD> find -t CONTROLFILE +DATA *
WARNING:option 't' is deprecated for 'find'
please use 'type'
 
+DATA/SHA/CONTROLFILE/current.272.998488595

Update new control file location in spfile.

1
2
SQL> alter system set control_files='+DATA/sha/CONTROLFILE/current.272.998488595' scope=spfile;
System altered.

 Now shutdown the instance and startup in mount stage and check for the control file location.

1
2
3
4
5
6
7
8
9
10
SQL> shutdown immediate
 
 SQL> startup mount
 
 SQL>show parameter control
 
NAME                                TYPE     VALUE
----------------                 -------- ------------------------------
control_file_record_keep_time        integer     7
control_files                         string     +DATA/sha/CONTROLFILE/current.272.998488595

After that we need to migrate the datafiles from file system to ASM disk using RMAN “BACKUP AS COPY” method

1
2
3
4
5
[oracle@sha-infotech ~]$  rman target /
 
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jan 25 14:04:24 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SHA (DBID=2167081579, not open)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
 
Starting backup at 25-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/sha/example01.dbf
output file name=+DATA/SHA/DATAFILE/example.273.998489077 tag=TAG20190125T140436 RECID=1 STAMP=998489197
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/sha/system01.dbf
output file name=+DATA/SHA/DATAFILE/system.274.998489203 tag=TAG20190125T140436 RECID=2 STAMP=998489278
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/sha/sysaux01.dbf
output file name=+DATA/SHA/DATAFILE/sysaux.275.998489287 tag=TAG20190125T140436 RECID=3 STAMP=998489350
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/sha/undotbs01.dbf
output file name=+DATA/SHA/DATAFILE/undotbs1.276.998489353 tag=TAG20190125T140436 RECID=4 STAMP=998489367
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/SHA/CONTROLFILE/backup.277.998489369 tag=TAG20190125T140436 RECID=5 STAMP=998489370
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/sha/users01.dbf
output file name=+DATA/SHA/DATAFILE/users.278.998489371 tag=TAG20190125T140436 RECID=6 STAMP=998489372
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 25-JAN-19
channel ORA_DISK_1: finished piece 1 at 25-JAN-19
piece handle=+DATA/SHA/BACKUPSET/2019_01_25/nnsnf0_tag20190125t140436_0.279.998489373 tag=TAG20190125T140436 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-JAN-19

Switch the database to copy .

1
2
3
4
5
6
7
RMAN> SWITCH DATABASE TO COPY;
 
datafile 1 switched to datafile copy "+DATA/SHA/DATAFILE/system.274.998489203"
datafile 3 switched to datafile copy "+DATA/SHA/DATAFILE/sysaux.275.998489287"
datafile 4 switched to datafile copy "+DATA/SHA/DATAFILE/undotbs1.276.998489353"
datafile 5 switched to datafile copy "+DATA/SHA/DATAFILE/example.273.998489077"
datafile 6 switched to datafile copy "+DATA/SHA/DATAFILE/users.278.998489371"

Migrate the tempfile to ASM using RMAN

1
2
3
4
5
6
RMAN> run {
2> set newname for tempfile '/u01/app/oracle/sha/temp01.dbf' to '+DATA';
3> switch tempfile all;
4> }
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file

Open the Database

1
2
RMAN> ALTER DATABASE OPEN;
Statement processed

Identify the current available redo log files using the following command:

1
2
3
4
5
6
7
SQL> SELECT A.GROUP#, B.MEMBER, A.STATUS FROM V$LOG A, V$LOGFILE B WHERE       A.GROUP#=B.GROUP#;
 
 GROUP#                    MEMBER                          STATUS
---------     --------------------------------         ----------------
     3         /u01/app/oracle/sha/redo03.log               CURRENT
     2        /u01/app/oracle/sha/redo02.log                INACTIVE
     1        /u01/app/oracle/sha/redo01.log                INACTIVE

Add New logfiles to ASM using following command:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
 
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 '+DATA' SIZE 50M;
Database altered.
 
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
 
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 '+DATA' SIZE 50M;
Database altered.
 
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
 
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
 
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
 
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 '+DATA' SIZE 50M;
Database altered.
1
2
3
4
5
6
7
SQL> SELECT A.GROUP#, B.MEMBER, A.STATUS FROM V$LOG A, V$LOGFILE B WHERE A.GROUP#=B.GROUP# ORDER BY 1;
 
    GROUP#            MEMB                                  STATUS
--------------    ------------------------                ----------------
     1 +DATA/SHA/ONLINELOG/group_1.281.998491979            INACTIVE
     2 +DATA/SHA/ONLINELOG/group_2.267.998492071            CURRENT
     3 +DATA/SHA/ONLINELOG/group_3.262.998492215            UNUSED
1
2
3
4
5
SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;
 
NAME      OPEN_MODE
--------- --------------------
SHA   READ WRITE