Migration Database from Non ASM to ASM

Migration Database from Non ASM to ASM

To take advantage of Automatic Storage Management with an existing database you must migrate that database into ASM. This migration is performed using Recovery Manager (RMAN) even if you are not using RMAN for your primary backup and recovery strategy.

A database can be moved from non-ASM disk storage directly into ASM, assuming you have enough disk space to hold the entire database both in non-ASM storage and in ASM storage.

Note:

· During the migration process all flashback logs are discarded. As a result, any guaranteed restore points in the database become unusable. You should drop all guaranteed restore points before performing the migration.

· Flashback logs cannot be migrated. All data in the flashback logs is lost.

· Because the actual flashback logs cannot be migrated, the only step required to move the location of flashback logs to the new ASM flash recovery area is to disable and then enable flashback logging. After a clean shutdown, mount the database and run the following commands in SQL*Plus

· Future flashback logs will be created in the new flash recovery area. The old flashback logs are automatically deleted from non-ASM storage.

SQL> ALTER DATABASE FLASHBACK OFF;

SQL> ALTER DATABASE FLASHBACK ON;

· 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 but here we are going to migrate the database from normal Filesystem to ASM

Validate the ASM instance running on server

[oracle@vm217 ~]$ crsctl stat res -t

--------------------------------------------------------------------------------

Name Target State Server State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.CRS_DISK.dg

ONLINE ONLINE vm217 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE vm217 STABLE

ora.asm

ONLINE ONLINE vm217 Started,STABLE

ora.ons

OFFLINE OFFLINE vm217 STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

1 ONLINE ONLINE vm217 STABLE

ora.diskmon

1 OFFLINE OFFLINE STABLE

ora.evmd

1 ONLINE ONLINE vm217 STABLE

--------------------------------------------------------------------------------

SQL> @open

DB_NAME INSTANCE_NAME VERSION HOST_NAME OPEN_MODE

---------- ---------------- ----------- -------------- -----------

PROD prod 19.0.0.0.0 vm217.ora.com READ WRITE

PDBS CHECKING

no rows selected

Validate the parameter, datafile,logfile before migration

SQL> show parameter pfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/app/oracle/product/19.3/D

B_1/dbs/spfileprod.ora

SQL> show parameter control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/PROD/c

ontrol01.ctl, /u01/app/oracle/

oradata/PROD/control02.ctl

control_management_pack_access string DIAGNOSTIC+TUNING

SQL>

SQL> sho parameter db_create

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest string /u01/app/oracle/oradata

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

SQL> col path for a80

SQL> col name for a25

SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk order by 1 ;

NAME STATE TOTAL_MB PATH

--------------- -------- ---------- ------------------------------

CRS_DISK_0000 NORMAL 10239 /dev/oracleasm/disks/ASMDISK01

CRS_DISK_0001 NORMAL 10239 /dev/oracleasm/disks/ASMDISK02

DATA_0000 NORMAL 10239 /dev/oracleasm/disks/ASMDISK03

DATA_0001 NORMAL 10239 /dev/oracleasm/disks/ASMDISK04

DATA_0002 NORMAL 10239 /dev/oracleasm/disks/ASMDISK05

DATA_0003 NORMAL 10239 /dev/oracleasm/disks/ASMDISK06

DATA_0004 NORMAL 10239 /dev/oracleasm/disks/ASMDISK07

7 rows selected.

SQL> select member from v$logfile;

MEMBER

---------------------------------------

/u01/app/oracle/oradata/PROD/redo03.log

/u01/app/oracle/oradata/PROD/redo02.log

/u01/app/oracle/oradata/PROD/redo01.log

Setting the location for Control file , datafile and shutdown the database.

SQL> alter system set DB_CREATE_FILE_DEST='+DATA' ;

System altered.

SQL> ALTER SYSTEM SET control_files='+DATA' scope=SPFILE ;

System altered.

SQL> shut immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount ;

ORACLE instance started.

Total System Global Area 1191181696 bytes

Fixed Size 8895872 bytes

Variable Size 436207616 bytes

Database Buffers 738197504 bytes

Redo Buffers 7880704 bytes

Mount the database and restore current structure to ASM diskgorup

[oracle@vm217 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Oct 21 12:42:47 2020

Version 19.3.0.0.0

resconnected to target database: PROD (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/oradata/PROD/control01.ctl' ;

Starting restore at 21-OCT-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=173 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+DATA/PROD/CONTROLFILE/current.256.1054384981

Finished restore at 21-OCT-20

RMAN> sql "alter database mount ";

sql statement: alter database mount

released channel: ORA_DISK_1

RMAN> backup as copy database format '+DATA' ;

Starting backup at 21-OCT-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=173 device type=DISK