There are various methods (RMAN, Online datafiles move 12c onwards, ASMCMD, DBMS_FILE_TRANSFER, etc) to achieve migrate databases from Non-ASM to ASM. Here we will use two types of RMAN mix methods to migrate local filesystems storage (Non-ASM) to ASM storage. Below are database details:
| COMPONENTS | SOURCE | TARGET |
| INSTANCE TYPE | HAS | HAS |
| DATABASE | LABDB05 | LABDB05 |
| STORAGE | /oradata/labdb05 | +DATA1 / +DATA1/oradata/labdb05 |
| VERSION | 12.2.0.1.0 | 12.2.0.1.0 |
| OS | Linux 7 | Linux 7 |
Method 1
1.1: Validate the database size.
1.2 Capture all physical files. Click here to get the SQL command for How to find all physicals files in Oracle.
1.3 Backup of SPFILE.
SQL> create pfile='/home/oracle/initlabdb05_before.ora' from spfile;
File created.
1.4: Validate available free size of target ASM DISK GROUP. Make sure you have enough free space in DG as require to migrate the database.
1.5: Blackout in OEM, disable jobs in DBMS_scheduler/cronjobs, stop dependent applications, etc as applicable if any.
1.6: Copy CONTROLFILES to new location DG +DATA1 using RMAN.
1.6.A: Shutdown the database and start in NOMOUNT state.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 1879048192 bytes
Fixed Size 8622000 bytes
Variable Size 1493172304 bytes
Database Buffers 369098752 bytes
Redo Buffers 8155136 bytes
1.6.B: Connect to RMAN session and restore CONTROLFILES to DG +DATA1 using any one existing CONTROLFILE.
RMAN> restore controlfile to '+DATA1' from '/oradata/labdb05/control01.ctl';Note: Repeat step to a multiplex of CONTROLFILE to other DG or same DG.
1.6.C: Identify the new name of CONTROLFILES either connect to ASM instance using ASMCMD or refer to new name output on RMAN session while restoring.
ASMCMD> find --type CONTROLFILE +DATA1 *
+DATA1/LABDB05/CONTROLFILE/Current.289.938012955
+DATA1/LABDB05/CONTROLFILE/Current.290.938012955
1.6.D: Modify the new name of CONTROLFILE either in PFILE or SPFILE and start the database in MOUNT state. Here we are performing in SPFILE.
SQL> alter system set control_files= '+DATA1/LABDB05/CONTROLFILE/Current.289.938012955', '+DATA1/LABDB05/CONTROLFILE/Current.290.938012955' scope=spfile;
System altered.
SQL> ALTER DATABASE MOUNT;
Database mounted.
SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;
NAME OPEN_MODE
--------- --------------------
LABDB05 MOUNTED
SQL> SHOW PARAMETER CONTROL_FILES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/LABDB05/CONTROLFILE/Current.289.938012955
+DATA1/LABDB05/CONTROLFILE/Current.290.938012955
1.7: Connect to RMAN session, copy datafiles from Non-ASM to ASM location.
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
RMAN>
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA1';
1.8: Rename datafiles connected to RMAN session.
RMAN> SWITCH DATABASE TO COPY;
1.9: Switch all tempfiles to ASM DG +DATA1.
RMAN> run
{
set newname for tempfile '/oradata/labdb05/temp_01.dbf' to '+DATA1';
set newname for tempfile '/oradata/labdb05/temp_02.dbf' to '+DATA1';
switch tempfile all;
}
1.10. Now OPEN database in READ/WRITE mode.
SQL> ALTER DATABASE OPEN;
Database altered.
1.11: Add new REDOLOG member to DG +DATA1 and drop old MEMBER.
SQL> SELECT A.GROUP#, B.MEMBER, A.STATUS FROM V$LOG A, V$LOGFILE B WHERE A.GROUP#=B.GROUP#;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA1' TO GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA1' TO GROUP 2;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA1' TO GROUP 3;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/oradata/labdb05/redo01.log';
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/oradata/labdb05/redo02.log';
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/oradata/labdb05/redo03.log';
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT A.GROUP#, B.MEMBER, A.STATUS FROM V$LOG A, V$LOGFILE B WHERE A.GROUP#=B.GROUP#;
Note: To drop redolog MEMBER/GROUP, status with INACTIVE only can be dropped and if required perform log switch to make it INACTIVE.
1.12: Register database in a server control, applicable only if it was not done earlier.
[oracle@DBsGuruN3 dbs]$ srvctl add database -d labdb05 -n labdb05 -o /u01/app/oracle/product/12.2.0.1/db_2 -p /u01/app/oracle/product/12.2.0.1/db_2/dbs/spfilelabdb05.ora
[oracle@DBsGuruN3 dbs]$ srvctl config database -d labdb05
Database unique name: labdb05
Database name: labdb05
Oracle home: /u01/app/oracle/product/12.2.0.1/db_2
Oracle user: oracle
Spfile: /u01/app/oracle/product/12.2.0.1/db_2/dbs/spfilelabdb05.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA1
Services:
OSDBA group: dba
OSOPER group: dba
Database instance: labdb05
[oracle@DBsGuruN3 dbs]$
1.13: Stop the database and start using SRVCTL.
SQL> SHU IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@DBsGuruN3 dbs]$ srvctl start database -d labdb05
[oracle@DBsGuruN3 dbs]$ srvctl status database -d labdb05
Database is running.
1.14: Validate all physical files after migration from Non-ASM to ASM storage.
1.15: Finally delete Blackout in OEM, enable jobs DBMS_scheduler/cronjobs, start dependent applications, etc as applicable if any.
Method 2
2.1: Validate the database size.
2.2 Capture all physical files.
2.3 Backup of SPFILE and CONTROLFILE (text format).
SQL> create pfile='/home/oracle/initlabdb05_before.ora' from spfile;
File created.
SQL> alter database backup controlfile to trace as '/home/oracle/labdb05_controlfile.sql';
Database altered.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> !ls -lrt /home/oracle/initlabdb05_before.ora /home/oracle/labdb05_controlfile.sql
-rw-r--r-- 1 oracle dba 1346 Jul 28 22:32 /home/oracle/initlabdb05_before.ora
-rw-r--r-- 1 oracle dba 6284 Jul 28 22:32 /home/oracle/labdb05_controlfile.sql
2.4: Create a directory.
[oracle@DBsGuruN3 ~]$ . setenv +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBsGuruN3 ~]$ asmcmd
ASMCMD> cd +DATA1/oradata/
ASMCMD> mkdir labdb05
ASMCMD> cd labdb05
ASMCMD> pwd
+DATA1/oradata/labdb05
Note: This step is completely optional step instead of it we can use DG name +DATA1 while copy datafiles in the RMAN session.
2.5: Blackout in OEM, disable jobs in DBMS_scheduler/cronjobs, stop dependent applications, etc as applicable if any.
2.6: Stop database and start in MOUNT state.
SQL> SHU IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 1879048192 bytes
Fixed Size 8622000 bytes
Variable Size 1493172304 bytes
Database Buffers 369098752 bytes
Redo Buffers 8155136 bytes
Database mounted.
SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;
NAME OPEN_MODE
--------- --------------------
LABDB05 MOUNTED
2.7: Connect to RMAN session, copy datafiles from Non-ASM to ASM location.
[oracle@DBsGuruN3 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Fri Jul 28 22:43:30 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: LABDB05 (DBID=4936628084, not open)
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> BACKUP AS COPY DB_FILE_NAME_CONVERT ('/oradata/labdb05','+DATA1/oradata/labdb05') database;
Starting backup at 28-JUL-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=820 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=983 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oradata/labdb05/sysaux01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00001 name=/oradata/labdb05/system01.dbf
output file name=+DATA1/oradata/labdb05/system01.dbf tag=TAG20210716T224736 RECID=1 STAMP=9380094894
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=/oradata/labdb05/undotbs01.dbf
output file name=+DATA1/oradata/labdb05/undotbs01.dbf tag=TAG20210716T224736 RECID=2 STAMP=9380094937
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_2: starting datafile copy
input datafile file number=00008 name=/oradata/labdb05/users01.dbf
output file name=+DATA1/oradata/labdb05/users01.dbf tag=TAG20210716T224736 RECID=3 STAMP=9380094955
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_2: starting datafile copy
input datafile file number=00005 name=/oradata/labdb05/tbls02.dbf
output file name=+DATA1/oradata/labdb05/sysaux01.dbf tag=TAG20210716T224736 RECID=4 STAMP=9380094964
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:48
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/oradata/labdb05/tbls04.dbf
output file name=+DATA1/oradata/labdb05/tbls04.dbf tag=TAG20210716T224736 RECID=6 STAMP=9380094971
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oradata/labdb05/tbls01.dbf
output file name=+DATA1/oradata/labdb05/tbls02.dbf tag=TAG20210716T224736 RECID=5 STAMP=9380094969
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:10
channel ORA_DISK_2: starting datafile copy
input datafile file number=00006 name=/oradata/labdb05/tbls03.dbf
output file name=+DATA1/oradata/labdb05/tbls01.dbf tag=TAG20210716T224736 RECID=7 STAMP=9380094975
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
output file name=+DATA1/oradata/labdb05/tbls03.dbf tag=TAG20210716T224736 RECID=8 STAMP=9380094975
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:07
Finished backup at 28-JUL-21
Starting Control File and SPFILE Autobackup at 28-JUL-21
piece handle=/u01/app/oracle/product/12.2.0.1/db_2/dbs/c-1755204861-20210728-00 comment=NONE
Finished Control File and SPFILE Autobackup at 28-JUL-21
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
released channel: ORA_DISK_2
2.8: Prepare PFILE & CONTROLFILE, edit the same file which we have taken in Step 2.3.
2.8.A: Edit PFILE: Replace parameter value of control_files to the new name, pointing to ASM location.
[oracle@DBsGuruN3 oracle]$ cat initlabdb05.ora | grep control
*.control_files='+DATA1/oradata/labdb05/control01.ctl','+DATA1/oradata/labdb05/control02.ctl'
2.8.B: Edit CONTROLFILE: To prepare to rename all physical of the database, we will edit CONTROLFILE (text format) where we will replace the new path of all files which is pointing to ASM DG +DATA1 as used in step 2.7 while copy datafiles to DG i.e replace from /oradata/labdb05 to +DATA1/oradata/labdb05 followed by below important points.
=> Remove all lines in trace file till up to line STARTUP NOMOUNT i.e remove lines that are comments (line started “–“).
=> Choose wisely options ARCHIVE/NOARCHIVE while creating CONTROLFILE with option RESETLOGS.
=> Replace path of redolog, datafile & tempfile to the new location where we copied files to ASM storage.
=> Make sure file extension should be .sql in case want to execute as a script on SQL prompt.
=> Below is a sample file for reference.
[oracle@DBsGuruN3 oracle]$ cat labdb05_controlfile.sql
SET ECHO ON;
SET TIMING ON;
CREATE CONTROLFILE REUSE DATABASE "LABDB05" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2921
LOGFILE
GROUP 1 '+DATA1/oradata/labdb05/redo01.log' SIZE 50M,
GROUP 2 '+DATA1/oradata/labdb05/redo02.log' SIZE 50M,
GROUP 3 '+DATA1/oradata/labdb05/redo03.log' SIZE 50M
DATAFILE
'+DATA1/oradata/labdb05/system01.dbf',
'+DATA1/oradata/labdb05/sysaux01.dbf',
'+DATA1/oradata/labdb05/undotbs01.dbf',
'+DATA1/oradata/labdb05/tbls01.dbf',
'+DATA1/oradata/labdb05/tbls02.dbf',
'+DATA1/oradata/labdb05/tbls03.dbf',
'+DATA1/oradata/labdb05/tbls04.dbf',
'+DATA1/oradata/labdb05/users01.dbf'
CHARACTER SET AL32UTF8
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP_NEW ADD TEMPFILE '+DATA1/oradata/labdb05/temp_01.dbf'
SIZE 62914560 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP_NEW ADD TEMPFILE '+DATA1/oradata/labdb05/temp_02.dbf'
SIZE 62914560 REUSE AUTOEXTEND OFF;
2.9: Stop database and start in NOMOUNT state using PFILE.
SQL> SHU IMMEDIATE
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> STARTUP NOMOUNT PFILE='/home/oracle/initlabdb05.ora'
ORACLE instance started.
Total System Global Area 1879048192 bytes
Fixed Size 8622000 bytes
Variable Size 1174405200 bytes
Database Buffers 687865856 bytes
Redo Buffers 8155136 bytes
SQL> SHOW PARAMETER CONTROL_FILES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/oradata/labdb05/con
trol01.ctl, +DATA1/oradata/
labdb05/control02.ctl
2.10: Execute create CONTROLFILE script which is the substitute of rename files connected to RMAN session where we followed in method 1 step 1.7, 1.8 and 1.10.
SQL> @/home/oracle/labdb05_controlfile.sql
SQL> SET TIMING ON;
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "LABDB05" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 2921
7 LOGFILE
8 GROUP 1 '+DATA1/oradata/labdb05/redo01.log' SIZE 50M,
9 GROUP 2 '+DATA1/oradata/labdb05/redo02.log' SIZE 50M,
10 GROUP 3 '+DATA1/oradata/labdb05/redo03.log' SIZE 50M
11 DATAFILE
12 '+DATA1/oradata/labdb05/system01.dbf',
13 '+DATA1/oradata/labdb05/sysaux01.dbf',
14 '+DATA1/oradata/labdb05/undotbs01.dbf',
15 '+DATA1/oradata/labdb05/tbls01.dbf',
16 '+DATA1/oradata/labdb05/tbls02.dbf',
17 '+DATA1/oradata/labdb05/tbls03.dbf',
18 '+DATA1/oradata/labdb05/tbls04.dbf',
19 '+DATA1/oradata/labdb05/users01.dbf'
20 CHARACTER SET AL32UTF8
21 ;
Control file created.
Elapsed: 00:00:00.71
SQL>
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
Elapsed: 00:00:09.57
SQL>
SQL> ALTER TABLESPACE TEMP_NEW ADD TEMPFILE '+DATA1/oradata/labdb05/temp_01.dbf'
2 SIZE 62914560 REUSE AUTOEXTEND OFF;
Tablespace altered.
Elapsed: 00:00:00.49
SQL> ALTER TABLESPACE TEMP_NEW ADD TEMPFILE '+DATA1/oradata/labdb05/temp_02.dbf'
2 SIZE 62914560 REUSE AUTOEXTEND OFF;
Tablespace altered.
Elapsed: 00:00:00.50
2.11: Validate all physical files after successful migration. Click here to get the SQL command for How to find all physicals files in Oracle.
2.12: Create SPFILE using PFILE and reboot the database, optionally also create PFILE in the default location after reboot.
SQL> CREATE SPFILE FROM PFILE='/home/oracle/initlabdb05.ora';
File created.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 1879048192 bytes
Fixed Size 8622000 bytes
Variable Size 1174405200 bytes
Database Buffers 687865856 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> SHOW PARAMETER SPFILE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/12.2.0.1/db_2/
dbs/spfilelabdb05.ora
SQL> CREATE PFILE FROM SPFILE;
File created.
2.13: Register database in a server control, applicable only if it was not done earlier.
[oracle@DBsGuruN3 dbs]$ srvctl add database -d labdb05 -n labdb05 -o /u01/app/oracle/product/12.2.0.1/db_2 -p /u01/app/oracle/product/12.2.0.1/db_2/dbs/spfilelabdb05.ora
[oracle@DBsGuruN3 dbs]$ srvctl config database -d labdb05
Database unique name: labdb05
Database name: labdb05
Oracle home: /u01/app/oracle/product/12.2.0.1/db_2
Oracle user: oracle
Spfile: /u01/app/oracle/product/12.2.0.1/db_2/dbs/spfilelabdb05.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA1
Services:
OSDBA group: dba
OSOPER group: dba
Database instance: labdb05
[oracle@DBsGuruN3 dbs]$
2.14: Stop the database and start using SRVCTL.
SQL> SHU IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@DBsGuruN3 dbs]$ srvctl start database -d labdb05
[oracle@DBsGuruN3 dbs]$ srvctl status database -d labdb05
Database is running.
2.15: Finally delete Blackout in OEM, enable jobs DBMS_scheduler/cronjobs, start dependent applications, etc as applicable if any.
This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
