Database Migration from Non-ASM to ASM
We are going to convert our oracle Database to be migrated from Non ASM to ASM storage using RMAN commands
Check the ASM instance is up and running
If not start the ASM instance and begin the migration.
- SELECT GROUP_NUMBER,NAME,STATE,TYPE FROM V$ASM_DISKGROUP WHERE NAME=’PROD’;
- SET LINES 1000
- COL PATH FOR A40
- SELECT NAME,STATE,TOTAL_MB,PATH FROM V$ASM_DISK;
Check the database file locations
- SELECT NAME,OPEN_MODE FROM V$DATABASE;
- SELECT NAME FROM V$DATAFILE;
- SELECT MEMBER FROM V$LOGFILE;
- SELECT NAME FROM V$TEMPFILE;
Check the spfile an control file location.
We are going to restore the control file to ASM by using this controlfile.
- sho parameter spfile
- sho parameter control_file
Change the location of database files to ASM file system in spfile.
- alter system set control_files=’+PROD’ scope=spfile;
- alter system set db_create_file_dest=’+PROD’ scope=spfile;
- alter system set db_recovery_file_dest=’+PROD’ scope=spfile;
Create pfile and check the parameter changes in pfile
- create pfile from spfile;
Restore the spfile to ASM diskgroup. After startup our database will use the ASM diskgroup parameter file
- rman target /
- RESTORE SPFILE TO ‘+PROD’;
Edit the parameter file to reflect the spfile location in ASM diskgroup
- vi initnewdb.ora
- spfile=’+PROD/NEWDB/AUTOBACKUP/2021_02_24/s_1065378100.303.1065378101′
Shutdown and start the database in nomount state with spfile.
- shut immediate
- startup nomount
We are restoring the control file from existing control file and mount the database.
The control file will be automatically restored to ASM filesystem because we have already changed the parameter of control_files=’+PROD’
- rman target /
- restore controlfile from ‘/u01/app/oracle/oradata/newdb/control01.ctl’;
- alter database mount;
We are using the backup as copy database command to copy the database files from the normal filesystem to ASM diskgroup.
- backup as copy database format ‘+PROD’;
Use the switch database to copy command to restore datafiles.
- switch database to copy;
Open the database
- alter database open;
Check the database files are created under ASM diskgroup.
- select name,open_mode from v$database;
- select name from v$datafile;
Tempfiles and logfiles are not created under ASM filesystem
We have to change the location to ASM diskgroup.
- select name from v$tempfile;
- select member from v$logfile;
- Create temporary tablespace.
- Make it default tablespace
- Drop the existing temporary tablespace
- create temporary tablespace temp1 tempfile ‘+prod’ size 50m autoextend on next 10m maxsize 1g;
- alter database default temporary tablespace temp1;
Drop the existing temporary tablespace and check the tablespace created under ASM disk group.
- ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/newdb/temp01.dbf’ DROP INCLUDING DATAFILES;
- select name from v$tempfile;
Add the new logfile group in ASM diskgroup and drop the existing logfile.
- alter database add logfile group 4 ‘+PROD’ size 50M;
- alter database add logfile group 5 ‘+PROD’ size 50M;
- alter database add logfile group 6 ‘+PROD’ size 50M;
- If status is clearing or current then we cannot drop online redo log group.The status need to be cleared, unused or inactive
- We can drop the group 2 and 3 because it is in unused state.
- We cannot drop the group 1 because it is in current state.
- alter database drop logfile group 2;
- alter database drop logfile group 3;
- set lines 1000
- select * from v$log;
- We have to switch the logfile to change the status of redo log group 1 to inactive.
- alter system switch logfile;
Now the status has changed and drop the old redolog group.
- select * from v$log;
alter database drop logfile group 1
The redo log files are created under ASM disk group.
- select member from v$logfile;
Add a database configuration to Oracle Clusterware
- srvctl add database -d newdg -o /u01/app/oracle/product/12.2.0.1/dbhome_1
- srvctl modify database -d newdg -spfile +PROD/NEWDB/AUTOBACKUP/2021_02_24/s_1065378100.303.1065378101
- srvctl config database -d newdg
