November 21, 2024

A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Migrate Non-ASM to ASM instance in 12c

6 min read
asrblogger

Check 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

More Articles

text on computer monitor 1 min read

How to check FRA ( Fast Recovery area) Utilization in Oracle ?

asrblogger
text on computer monitor 6 min read

How to check Tablespace Utilization in Oracle ?

asrblogger
wooden world map on wall 4 min read

How to Rename ASM Diskgroup ??

asrblogger

You may have missed

text on computer monitor 1 min read

How to check FRA ( Fast Recovery area) Utilization in Oracle ?

asrblogger
text on computer monitor 6 min read

How to check Tablespace Utilization in Oracle ?

asrblogger
15 min read

Install and Configure OpenSearch & OpenSearch Dashboard

Avnish Singh
wooden world map on wall 4 min read

How to Rename ASM Diskgroup ??

asrblogger