Migrate Non-ASM to ASM instance in 12c
6 min readCheck the status of ASM instance .
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.
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
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.
[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
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.
[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.
First it will search for spfile<sid>.ora which we deleted and next it will look for init<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.
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
[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.
[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.
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.
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
[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)
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 .
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
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
RMAN> ALTER DATABASE OPEN;
Statement processed
Identify the current available redo log files using the following command:
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:
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.
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
SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;
NAME OPEN_MODE
--------- --------------------
SHA READ WRITE
