Migrate Oracle Database from ASM to Non-ASM adminkahaniya ASM April 18, 2023 +++Environment+++ DB Version: 19.15.0.0.0 DB Type: Oracle Restart Standalone GI Restart Version: asmcmd version 19.18.0.0.0 ASM Disk Group : TESTDBDATA +++Create directories in File System to hold datafiles/redo/control file+++ su – oracle mkdir -p /u01/app/oracle/product/oradata mkdir -p /u01/app/oracle/product/fra +++Change the control file location in SPFILE+++ SQL> select name from v$controlfile; NAME ——————————————————————————– +TESTDBDATA/TESTDB/CONTROLFILE/current.294.1131722387 +TESTDBDATA/TESTDB/CONTROLFILE/current.297.1131722387 SQL> SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 3087004336 bytes Fixed Size 8901296 bytes Variable Size 620756992 bytes Database Buffers 2449473536 bytes Redo Buffers 7872512 bytes SQL> SQL> alter system set control_files=’/u01/app/oracle/product/oradata/ control1.ctl’,’/u01/app/oracle/product/fra/control2.ctl’ scope=spfile; System altered. SQL> ALTER SYSTEM SET db_create_file_dest=’/u01/app/oracle/product/oradata’ scope=spfile; System altered. SQL> ALTER SYSTEM SET db_recovery_file_dest=’/u01/app/oracle/product/fra’ scope=spfile; System altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> +++Migrate the control file from ASM to file system+++ SQL> startup nomount ORACLE instance started. Total System Global Area 3087004336 bytes Fixed Size 8901296 bytes Variable Size 620756992 bytes Database Buffers 2449473536 bytes Redo Buffers 7872512 bytes SQL> [oracle@testdb dbs]$ rman target / Recovery Manager: Release 19.0.0.0.0 – Production on Fri Mar 17 18:17:05 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTDB (not mounted) RMAN> RMAN> restore controlfile from ‘+TESTDBDATA/TESTDB/CONTROLFILE/current.294.1131722387’; Starting restore at 17-MAR-2023 18:18:00 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=40 device type=DISK channel ORA_DISK_1: copied control file copy output file name=/u01/app/oracle/product/oradata/control1.ctl output file name=/u01/app/oracle/product/fra/control2.ctl Finished restore at 17-MAR-2023 18:18:06 RMAN> SQL> shut immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> +++Copy the datafiles from ASM to filesystem using RMAN+++ ===Query the datafiles and tempfiles SQL> startup mount ORACLE instance started. Total System Global Area 3087004336 bytes Fixed Size 8901296 bytes Variable Size 620756992 bytes Database Buffers 2449473536 bytes Redo Buffers 7872512 bytes Database mounted. SQL> SQL> set linesize 300 col name for a86 select file#, name from v$datafile; FILE# NAME ———- ————————————————————————————– 1 +TESTDBDATA/TESTDB/DATAFILE/system.293.1131722271 3 +TESTDBDATA/TESTDB/DATAFILE/sysaux.275.1131722315 4 +TESTDBDATA/TESTDB/DATAFILE/undotbs1.298.1131722341 7 +TESTDBDATA/TESTDB/DATAFILE/users.295.1131722343 SQL> rman target / RMAN> backup as copy database format ‘/u01/app/oracle/product/oradata/%U.dbf’; Starting backup at 17-MAR-2023 18:21:51 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=48 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=+TESTDBDATA/TESTDB/DATAFILE/system.293.1131722271 output file name=/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-SYSTEM_FNO-1_011n9oa3.dbf tag=TAG20230317T182155 RECID=1 STAMP=1131733320 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=+TESTDBDATA/TESTDB/DATAFILE/sysaux.275.1131722315 output file name=/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-SYSAUX_FNO-3_021n9oab.dbf tag=TAG20230317T182155 RECID=2 STAMP=1131733341 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+TESTDBDATA/TESTDB/DATAFILE/undotbs1.298.1131722341 output file name=/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-UNDOTBS1_FNO-4_031n9ob4.dbf tag=TAG20230317T182155 RECID=3 STAMP=1131733353 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=+TESTDBDATA/TESTDB/DATAFILE/users.295.1131722343 output file name=/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-USERS_FNO-7_041n9obb.dbf tag=TAG20230317T182155 RECID=4 STAMP=1131733355 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 17-MAR-2023 18:22:36 Starting Control File and SPFILE Autobackup at 17-MAR-2023 18:22:36 piece handle=/u01/app/oracle/product/fra/TESTDB/autobackup/2023_03_17/ o1_mf_s_1131732720_l18rnnz4_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 17-MAR-2023 18:22:37 RMAN> RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ——- —- — ———- ———– ———— ——————– 1 Full 10.20M DISK 00:00:01 17-MAR-2023 18:22:37 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20230317T182236 Piece Name: /u01/app/oracle/product/fra/TESTDB/autobackup/ 2023_03_17/o1_mf_s_1131732720_l18rnnz4_.bkp SPFILE Included: Modification time: 17-MAR-2023 18:20:02 SPFILE db_unique_name: TESTDB Control File Included: Ckp SCN: 2288681 Ckp time: 17-MAR-2023 18:12:00 RMAN> RMAN> switch database to copy; datafile 1 switched to datafile copy “/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-SYSTEM_FNO-1_011n9oa3.dbf” datafile 3 switched to datafile copy “/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-SYSAUX_FNO-3_021n9oab.dbf” datafile 4 switched to datafile copy “/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-UNDOTBS1_FNO-4_031n9ob4.dbf” datafile 7 switched to datafile copy “/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-USERS_FNO-7_041n9obb.dbf” RMAN> ===Check now whether datafiles moved to file sysstem or not SQL> set linesize 300 col name for a90 select file#, name from v$datafile; FILE# NAME ———- —————————————————————————————— 1 /u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-SYSTEM_FNO-1_011n9oa3.dbf 3 /u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-SYSAUX_FNO-3_021n9oab.dbf 4 /u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-UNDOTBS1_FNO-4_031n9ob4.dbf 7 /u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-USERS_FNO-7_041n9obb.dbf SQL> +++Recreate the temp files+++ SQL> set linesize 300 col name for a60 select file#, name from v$tempfile; FILE# NAME ———- ———————————————————— 1 +TESTDBDATA/TESTDB/TEMPFILE/temp.291.1131722399 SQL> rman target / RMAN> run { set newname for tempfile 1 to ‘/u01/app/oracle/product/oradata/%U.dbf’; switch tempfile all; } executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/product/oradata/data_D-TESTDB_TS-TEMP_FNO-1.dbf in control file RMAN> +++Check now whether temp files moved to file system or not+++ SQL> set linesize 300 col name for a70 select file#, name from v$tempfile; FILE# NAME ———- ———————————————————————- 1 /u01/app/oracle/product/oradata/data_D-TESTDB_TS-TEMP_FNO-1.dbf SQL> SQL> set linesize 300 SQL> col name for a15 SQL> select name,open_mode from v$database; NAME OPEN_MODE ————— ——————– TESTDB MOUNTED SQL> SQL> alter database open; Database altered. SQL> +++Drop the redo log files in ASM and recreate the online redo log files to filesystem+++ Note:—inactive and unused group only we need to drop ===== SQL> set linesize 300 col member for a60 col status for a10 select a.group#,b.member,a.status from v$log a, v$logfile b where a.group#=b.group#; GROUP# MEMBER STATUS ———- ———————————————————— ———- 3 +TESTDBDATA/TESTDB/ONLINELOG/group_3.289.1131722393 CURRENT 3 +TESTDBDATA/TESTDB/ONLINELOG/group_3.290.1131722393 CURRENT 2 +TESTDBDATA/TESTDB/ONLINELOG/group_2.300.1131722389 INACTIVE 2 +TESTDBDATA/TESTDB/ONLINELOG/group_2.262.1131722391 INACTIVE 1 +TESTDBDATA/TESTDB/ONLINELOG/group_1.299.1131722389 INACTIVE 1 +TESTDBDATA/TESTDB/ONLINELOG/group_1.301.1131722391 INACTIVE 6 rows selected. SQL> SQL> alter database drop logfile group 1; Database altered. SQL> alter database add logfile group 1 size 50m; Database
Migrate Oracle database from Non-ASM to ASM
Migrate Oracle database from Non-ASM to ASM adminkahaniya ASM April 12, 2023 +++Environment+++ DB Version: 19.15.0.0.0 DB Type: Oracle Restart Standalone GI Restart Version: asmcmd version 19.18.0.0.0 ASM Disk Group : TESTDBDATA +++In SPFILE, change the control file location from file system to ASM+++ –Verify the Controlfile location SQL> select name from v$controlfile; NAME ———————————————————- /u01/app/oracle/oradata/TESTDB/control01.ctl /u01/app/oracle/fast_recovery_area/TESTDB/control02.ctl –Shutdown the DB, startup DB in mount state and change the controlfile location to ASM. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup nomount ORACLE instance started. Total System Global Area 3187667920 bytes Fixed Size 8901584 bytes Variable Size 637534208 bytes Database Buffers 2533359616 bytes Redo Buffers 7872512 bytes SQL> SQL> alter system set control_files=’+TESTDBDATA’ scope=spfile; System altered. SQL> SQL> alter system set control_files=’+TESTDBDATA’ scope=spfile; System altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> +++Restore the control file from File System to ASM+++ –startup DB in nomount state and restore control file using RMAN SQL> startup nomount ORACLE instance started. Total System Global Area 3187667920 bytes Fixed Size 8901584 bytes Variable Size 637534208 bytes Database Buffers 2533359616 bytes Redo Buffers 7872512 bytes SQL> SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.15.0.0.0 [oracle@testdb ~]$ [oracle@testdb ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 – Production on Thu Mar 16 20:14:41 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTDB (not mounted) RMAN> RMAN> restore controlfile from ‘/u01/app/oracle/oradata/TESTDB/control01.ctl’; Starting restore at 16-MAR-2023 20:16:51 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=44 device type=DISK channel ORA_DISK_1: copied control file copy output file name=+TESTDBDATA/TESTDB/CONTROLFILE/current.275.1131653813 Finished restore at 16-MAR-2023 20:16:53 RMAN> RMAN> sql ‘alter database mount’; using target database control file instead of recovery catalog sql statement: alter database mount RMAN> [grid@testdb ~]$ su – oracle Password: Last login: Thu Mar 16 19:34:34 IST 2023 from 192.168.56.1 on pts/3 [oracle@testdb ~]$ [oracle@testdb ~]$ [oracle@testdb ~]$ su – grid Password: Last login: Thu Mar 16 20:21:03 IST 2023 on pts/2 [grid@testdb ~]$ asmcmd ASMCMD> find –type CONTROLFILE +TESTDBDATA * +TESTDBDATA/TESTDB/CONTROLFILE/current.275.1131653813 ASMCMD> +++Copy the datafiles from ASM to filesystem using RMAN+++ –Check the DB state, it should be in mount state SQL> select name,open_mode from v$database; NAME OPEN_MODE ——— ——————– TESTDB MOUNTED SQL> ===Query the datafiles locations before moving to asm. SQL> set linesize 300 col name for a86 select file#, name from v$datafile; FILE# NAME ———- ————————————————————————————– 1 /u01/app/oracle/oradata/TESTDB/system01.dbf 3 /u01/app/oracle/oradata/TESTDB/sysaux01.dbf 4 /u01/app/oracle/oradata/TESTDB/undotbs01.dbf 7 /u01/app/oracle/oradata/TESTDB/users01.dbf SQL> [oracle@testdb ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 – Production on Thu Mar 16 20:30:19 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTDB (DBID=2914977916, not open) RMAN> RMAN> backup as copy database format ‘+TESTDBDATA’; Starting backup at 16-MAR-2023 20:31:03 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=46 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/TESTDB/system01.dbf output file name=+TESTDBDATA/TESTDB/DATAFILE/system.262.1131654665 tag=TAG20230316T203104 RECID=1 STAMP=1131654668 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/TESTDB/sysaux01.dbf output file name=+TESTDBDATA/TESTDB/DATAFILE/sysaux.301.1131654671 tag=TAG20230316T203104 RECID=2 STAMP=1131654688 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/TESTDB/undotbs01.dbf output file name=+TESTDBDATA/TESTDB/DATAFILE/undotbs1.300.1131654697 tag=TAG20230316T203104 RECID=3 STAMP=1131654699 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/u01/app/oracle/oradata/TESTDB/users01.dbf output file name=+TESTDBDATA/TESTDB/DATAFILE/users.299.1131654703 tag=TAG20230316T203104 RECID=4 STAMP=1131654704 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 16-MAR-2023 20:31:44 Starting Control File and SPFILE Autobackup at 16-MAR-2023 20:31:44 piece handle=/u01/app/oracle/fast_recovery_area/TESTDB/autobackup/ 2023_03_16/o1_mf_s_1131653176_l16cts7h_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 16-MAR-2023 20:31:46 RMAN> RMAN> switch database to copy; datafile 1 switched to datafile copy “+TESTDBDATA/TESTDB/DATAFILE/system.262.1131654665” datafile 3 switched to datafile copy “+TESTDBDATA/TESTDB/DATAFILE/sysaux.301.1131654671” datafile 4 switched to datafile copy “+TESTDBDATA/TESTDB/DATAFILE/undotbs1.300.1131654697” datafile 7 switched to datafile copy “+TESTDBDATA/TESTDB/DATAFILE/users.299.1131654703” RMAN> ===Check now whether datafiles moved to ASM or not set linesize 300 col name for a86 select file#, name from v$datafile; FILE# NAME ———- ———————————————————————————— 1 +TESTDBDATA/TESTDB/DATAFILE/system.262.1131654665 3 +TESTDBDATA/TESTDB/DATAFILE/sysaux.301.1131654671 4 +TESTDBDATA/TESTDB/DATAFILE/undotbs1.300.1131654697 7 +TESTDBDATA/TESTDB/DATAFILE/users.299.1131654703 +++Switch the temp files to ASM and open the database from mount state+++ ===Query the TEMP files locations and then switch the temp file location to ASM. SQL> set linesize 300 col name for a60 select file#, name from v$tempfile; FILE# NAME ———- ———————————————————— 1 /u01/app/oracle/oradata/TESTDB/temp01.dbf SQL> [oracle@testdb ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 – Production on Thu Mar 16 20:38:49 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTDB (DBID=2914977916, not open) RMAN> run 2> { 3> set newname for tempfile 1 to ‘+TESTDBDATA’; 4> switch tempfile 1; 5> } executing command: SET NEWNAME using target database control file instead of recovery catalog renamed tempfile 1 to +TESTDBDATA in control file RMAN> ===Check now whether temp switched to ASM or not sqlplus / as sysdba SQL> set linesize 300 col name for a60 select file#, name from v$tempfile; FILE# NAME ———- ———————————————————— 1 +TESTDBDATA SQL> RMAN> sql ‘alter database open’; sql statement: alter database open RMAN> +++Drop the redo log files in file system and recreate the online redo log files in ASM+++ Note:—We need to drop inactive and unused group only. ===== SQL> set linesize 300 col member for a60 col status for a10 select a.group#,b.member,a.status from v$log a, v$logfile b where a.group#=b.group#; GROUP# MEMBER