How to Migrate a Oracle Database From Non-ASM to ASM

By Posted on

Step by Step Migrating a Oracle Database From Non-ASM to ASM

Description:-

  • In this article we are going to see How to migrate the database from normal Filesystem to Asm Storage.
  • There are several methods is there to move database from filesystem to Asm diskgroup like RMAN,DBMS_FILE_TRANSFER,ASMCMD and 12c online Move datafile
  • Here we are going to convert database from local filesystem to ASM storage using Rman Method

Example for 12c:-

12c Migrate Database from non-ASM to ASM using online datafile move

Move to ASM:
ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/orcl/datafile/test.dbf’ TO ‘+DATA/data/datafile/test.dbf’;

  • ASM Instance is already Created and Ready to Use see the below link.

Step by Step configuration ASM for a Standalone Database Here 

Let’s Start the Demo:-
Pre-Steps

Check the Asm instance is Running

select INSTANCE_NAME,VERSION,DATABASE_STATUS from v$instance;

select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;

Login to ascmd check using lsdg command

We are going to migrate the below database “ORCL”  and Disk Group Name is  “+DATA”

SQL> select name from v$database;
NAME
———
ORCL

SQL> select name from v$datafile;
NAME
——————————————–
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf

SQL> select name from v$controlfile;
NAME
—————————————–
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl

SQL> select member from v$logfile;
MEMBER
—————————————
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log

Migration steps:

Step1:-Login in to Orcl Database change the below parameters

SQL> ALTER SYSTEM SET control_files=’+DATA’ scope=spfile;
System altered.

SQL> ALTER SYSTEM SET db_create_file_dest=’+DATA’ scope=spfile;
System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest=’+DATA’ scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step2:-Startup the orcl database in NOMOUNT mode

SQL> startup nomount
ORACLE instance started.
Total System Global Area 1607008256 bytes
Fixed Size 1345240 bytes
Variable Size 486541608 bytes
Database Buffers 1107296256 bytes
Redo Buffers 11825152 bytes

Step3:-Connect to Rman Session to copy the controlfile from local filesystem to ASM Diskgroup “+DATA”

[oracle@oracleasm ~]$ export ORACLE_SID=orcl
[oracle@oracleasm ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 – Production on Mon Jun 11 10:27:31 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)

RMAN> RESTORE CONTROLFILE FROM ‘/u01/app/oracle/oradata/orcl/control01.ctl’;
Starting restore at 11-JUN-18
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
output file name=+DATA/orcl/controlfile/current.280.978517795
Finished restore at 11-JUN-18

Step4:-Connect to SQL*Plus and mount the database.

[oracle@oracleasm ~]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 11 10:31:51 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter database mount;
Database altered.

SQL> select name from v$controlfile;
NAME
———————————————–
+DATA/orcl/controlfile/current.280.978517795

Step5:-Again connect to RMAN session to copy the database files from the local filesystem to ASM Diskgroup “+DATA”

[oracle@oracleasm ~]$ export ORACLE_SID=orcl
[oracle@oracleasm ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 – Production on Mon Jun 11 10:35:58 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1506003460, not open)

RMAN> BACKUP AS COPY DATABASE FORMAT ‘+DATA’;
Starting backup at 11-JUN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=+DATA/orcl/datafile/system.281.978518177 tag=TAG20180611T103616 RECID=2 STAMP=978518232
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:57
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output file name=+DATA/orcl/datafile/sysaux.282.978518235 tag=TAG20180611T103616 RECID=3 STAMP=978518275
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output file name=+DATA/orcl/datafile/example.283.978518283 tag=TAG20180611T103616 RECID=4 STAMP=978518299
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output file name=+DATA/orcl/datafile/undotbs1.284.978518307 tag=TAG20180611T103616 RECID=5 STAMP=978518310
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:05
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/orcl/controlfile/backup.285.978518313 tag=TAG20180611T103616 RECID=6 STAMP=978518313
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=+DATA/orcl/datafile/users.286.978518315 tag=TAG20180611T103616 RECID=7 STAMP=978518314
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 11-JUN-18
channel ORA_DISK_1: finished piece 1 at 11-JUN-18
piece handle=+DATA/orcl/backupset/2018_06_11/nnsnf0_tag20180611t103616_0.287.978518315 tag=TAG20180611T103616 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 11-JUN-18

Step6:-update the control file and data dictionary for the database files point to the ASM Diskgroup “+DATA”

RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy “+DATA/orcl/datafile/system.281.978518177”
datafile 2 switched to datafile copy “+DATA/orcl/datafile/sysaux.282.978518235”
datafile 3 switched to datafile copy “+DATA/orcl/datafile/undotbs1.284.978518307”
datafile 4 switched to datafile copy “+DATA/orcl/datafile/users.286.978518315”
datafile 5 switched to datafile copy “+DATA/orcl/datafile/example.283.978518283”

SQL> select name from v$datafile; (Check database level)
NAME
———————————————
+DATA/orcl/datafile/system.281.978518177
+DATA/orcl/datafile/sysaux.282.978518235
+DATA/orcl/datafile/undotbs1.284.978518307
+DATA/orcl/datafile/users.286.978518315
+DATA/orcl/datafile/example.283.978518283

Step6:-Tempfile relocating to ASM diskgroup 

SQL> select name from v$tempfile;

NAME
—————————————–
/u01/app/oracle/oradata/orcl/temp01.dbf

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@oracleasm ~]$ export ORACLE_SID=orcl
[oracle@oracleasm ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 – Production on Mon Jun 11 10:47:05 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1506003460, not open)

RMAN> run
{
set newname for tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ to ‘+DATA’;
switch tempfile all;
}2> 3> 4> 5>

executing command: SET NEWNAME
using target database control file instead of recovery catalog
renamed tempfile 1 to +DATA in control file

SQL> select name from v$tempfile;(Check database level)

NAME
—————————————-
+DATA/orcl/tempfile/temp.288.978519169

Step7:-Connect to SQL*Plus and open the database. 

[oracle@oracleasm ~]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 11 10:52:41 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter database open;
Database altered.

Step8:-One by one drop and re-create the online redo logfiles to ASM Diskgroup “+DATA”

SQL> select group#, status from v$log;
GROUP# STATUS
———- —————-
1 INACTIVE
2 CURRENT
3 INACTIVE           (Note:-inactive and unused group only we need to drop)  

SQL> select member from V$logfile;
MEMBER
—————————————–
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log

SQL> alter database add logfile group 1 size 50m;
Database altered.

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database add logfile group 3 size 50m;
Database altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system checkpoint;
System altered.

SQL> alter database drop logfile group 2;
Database altered.

SQL> alter database add logfile group 2 size 50m;
Database altered.

SQL> select member from V$logfile;
MEMBER
——————————————————————————–
+DATA/orcl/onlinelog/group_3.291.978519961
+DATA/orcl/onlinelog/group_2.293.978519999
+DATA/orcl/onlinelog/group_1.289.978519919
+DATA/orcl/onlinelog/group_1.290.978519921
+DATA/orcl/onlinelog/group_3.292.978519961
+DATA/orcl/onlinelog/group_2.294.978520001

Successfully completed the Migrating a Oracle Database From Non-ASM to ASM

Reference:-

https://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_rman.htm#OSTMG120

Related posts:

Leave a Reply

Your email address will not be published. Required fields are marked *