A friend, Vishnu Sai asked me if I have any document to migrate a Non-ASM Database to ASM storage so I thought to create a quick note for him today. I have created a ASM diskgroup named '+DATA' and will be using the same to store all database files in there.
Step 1 - List the database files need to be migrated to ASM storage:
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Data File Report (all physical files) |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN tablespace FORMAT a30 HEADING 'Tablespace Name / File Class'
COLUMN filename FORMAT a75 HEADING 'Filename'
COLUMN filesize FORMAT 9,999,999,999,999 HEADING 'File Size'
COLUMN autoextensible FORMAT a4 HEADING 'Auto'
COLUMN increment_by FORMAT 999,999,999,999 HEADING 'Next'
COLUMN maxbytes FORMAT 999,999,999,999 HEADING 'Max'
BREAK ON report
COMPUTE sum OF filesize ON report
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_data_files d
, v$datafile v
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
WHERE
(d.file_name = v.name)
UNION
SELECT
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_temp_files d
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
UNION
SELECT
'[ ONLINE REDO LOG ]'
, a.member
, b.bytes
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$logfile a
, v$log b
WHERE
a.group# = b.group#
UNION
SELECT
'[ CONTROL FILE ]'
, a.name
, TO_NUMBER(null)
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$controlfile a
ORDER BY 1,2
/
=====================
Tablespace Name / File Class Filename File Size Auto Next Max
------------------------------ -------------------------------------------------- ------------------ ---- ---------------- ----------------
SYSAUX /u01/app/oracle/oradata/testdb/sysaux01.dbf 450,887,680 YES 10,485,760 34,359,721,984
SYSTEM /u01/app/oracle/oradata/testdb/system01.dbf 702,545,920 YES 10,485,760 34,359,721,984
TEMP /u01/app/oracle/oradata/testdb/temp01.dbf 20,971,520 YES 655,360 34,359,721,984
UNDOTBS1 /u01/app/oracle/oradata/testdb/undotbs01.dbf 31,457,280 YES 5,242,880 34,359,721,984
USERS /u01/app/oracle/oradata/testdb/users01.dbf 5,242,880 YES 1,310,720 34,359,721,984
[ CONTROL FILE ] /u01/app/oracle/flash_recovery_area/testdb/control
02.ctl
[ CONTROL FILE ] /u01/app/oracle/oradata/testdb/control01.ctl
[ ONLINE REDO LOG ] /u01/app/oracle/oradata/testdb/redo01.log 52,428,800
[ ONLINE REDO LOG ] /u01/app/oracle/oradata/testdb/redo02.log 52,428,800
[ ONLINE REDO LOG ] /u01/app/oracle/oradata/testdb/redo03.log 52,428,800
------------------
sum 1,368,391,680
10 rows selected.
Above is the list of all database files these we will be migrating to ASM storage from filesystem.
Step 2 - If your database is running using pfile then just edit the following parameters pointing to appropriate ASM diskgroups in the pfile and shutdown the database normal and start in nomount state.
control_files='=+DATA'
db_create_file_dest='+DATA'
db_recovery_file_dest='+DATA'
and if your database is running using spfile then simply alter the parameters accordingly.
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.
Step 3 - Shutdown the DB normally and start it up in nomount state.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 841162752 bytes
Fixed Size 1339768 bytes
Variable Size 494931592 bytes
Database Buffers 339738624 bytes
Redo Buffers 5152768 bytes
Step 4 - Restore the control file to ASM diskgroup '+DATA' from its original filesystem location.
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 17 15:57:43 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/testdb/control02.ctl';
Starting restore at 17-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/testdb/controlfile/current.264.882633485
Finished restore at 17-JUN-15
RMAN>
Step 5 - As of now, we have migrated the database controlfile to ASM storage now so lets start the database in MOUNT state.
SQL> alter database mount;
Database altered.
Step 6 - Backup the database as copy to the ASM storage.
RMAN> backup as copy database format '+DATA';
Starting backup at 17-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 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=+DATA/testdb/datafile/system.263.882633749 tag=TAG20150617T160228 RECID=1 STAMP=882633766
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/testdb/sysaux01.dbf
output file name=+DATA/testdb/datafile/sysaux.262.882633775 tag=TAG20150617T160228 RECID=2 STAMP=882633789
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/testdb/undotbs01.dbf
output file name=+DATA/testdb/datafile/undotbs1.269.882633801 tag=TAG20150617T160228 RECID=3 STAMP=882633800
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/testdb/controlfile/backup.259.882633801 tag=TAG20150617T160228 RECID=4 STAMP=882633802
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/testdb/users01.dbf
output file name=+DATA/testdb/datafile/users.258.882633803 tag=TAG20150617T160228 RECID=5 STAMP=882633803
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
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 17-JUN-15
channel ORA_DISK_1: finished piece 1 at 17-JUN-15
piece handle=+DATA/testdb/backupset/2015_06_17/nnsnf0_tag20150617t160228_0.257.882633807 tag=TAG20150617T160228 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 17-JUN-15
Step 7 - So now we have a copy of our current database at ASM storage now we can switch the database to copy.
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/testdb/datafile/system.263.882633749"
datafile 2 switched to datafile copy "+DATA/testdb/datafile/sysaux.262.882633775"
datafile 3 switched to datafile copy "+DATA/testdb/datafile/undotbs1.269.882633801"
datafile 4 switched to datafile copy "+DATA/testdb/datafile/users.258.882633803"
RMAN>
Step 8 - Now open the database.
SQL> alter database open;
Database altered.
SQL>
Step 9 - Now create the new temp file and drop the old one so that it is also stored on ASM.
SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES
------------------------------ -------------------------------------------------- ----------
TEMP /u01/app/oracle/oradata/testdb/temp01.dbf 20971520
SQL> alter tablespace temp add tempfile size 50m autoextend on maxsize 2048m;
Tablespace altered.
SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES
------------------------------ -------------------------------------------------- ----------
TEMP /u01/app/oracle/oradata/testdb/temp01.dbf 20971520
TEMP +DATA/testdb/tempfile/temp.256.882634443 52428800
Now drop the old temp file which is stored on Filesystem.
SQL> alter database tempfile '/u01/app/oracle/oradata/testdb/temp01.dbf' drop including datafiles;
Database altered.
SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES
------------------------------ -------------------------------------------------- ----------
TEMP +DATA/testdb/tempfile/temp.256.882634443 52428800
Step 10 - Let's migrate the database redo log files to ASM storage now.
SQL> select a.group#, a.member, b.bytes, b.status from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES STATUS
---------- -------------------------------------------------- ---------- ----------------
3 /u01/app/oracle/oradata/testdb/redo03.log 52428800 CURRENT
2 /u01/app/oracle/oradata/testdb/redo02.log 52428800 ACTIVE
1 /u01/app/oracle/oradata/testdb/redo01.log 52428800 INACTIVE
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 50m;
Database altered.
SQL> select a.group#, a.member, b.bytes, b.status from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES STATUS
---------- -------------------------------------------------- ---------- ----------------
3 /u01/app/oracle/oradata/testdb/redo03.log 52428800 INACTIVE
2 /u01/app/oracle/oradata/testdb/redo02.log 52428800 CURRENT
1 +DATA/testdb/onlinelog/group_1.287.882635039 52428800 INACTIVE
1 +DATA/testdb/onlinelog/group_1.286.882635039 52428800 INACTIVE
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 50m;
Database altered.
SQL> select a.group#, a.member, b.bytes, b.status from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES STATUS
---------- -------------------------------------------------- ---------- ----------------
3 +DATA/testdb/onlinelog/group_3.282.882635099 52428800 INACTIVE
2 /u01/app/oracle/oradata/testdb/redo02.log 52428800 INACTIVE
1 +DATA/testdb/onlinelog/group_1.287.882635039 52428800 CURRENT
1 +DATA/testdb/onlinelog/group_1.286.882635039 52428800 CURRENT
3 +DATA/testdb/onlinelog/group_3.280.882635099 52428800 INACTIVE
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 50m;
Database altered.
Now we are all migrated to ASM storage.
SQL> select a.group#, a.member, b.bytes, b.status from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES STATUS
---------- -------------------------------------------------- ---------- ----------------
3 +DATA/testdb/onlinelog/group_3.282.882635099 52428800 INACTIVE
2 +DATA/testdb/onlinelog/group_2.289.882635185 52428800 UNUSED
1 +DATA/testdb/onlinelog/group_1.287.882635039 52428800 CURRENT
1 +DATA/testdb/onlinelog/group_1.286.882635039 52428800 CURRENT
3 +DATA/testdb/onlinelog/group_3.280.882635099 52428800 INACTIVE
2 +DATA/testdb/onlinelog/group_2.290.882635187 52428800 UNUSED
Finally let's verify if all database files are migrated to ASM storage now:
Tablespace Name / File Class Filename File Size Auto Next Max
------------------------------ --------------------------------------------------------------------------- ------------------ ---- ---------------- ----------------
SYSAUX +DATA/testdb/datafile/sysaux.262.882633775 450,887,680 YES 10,485,760 34,359,721,984
SYSTEM +DATA/testdb/datafile/system.263.882633749 702,545,920 YES 10,485,760 34,359,721,984
TEMP +DATA/testdb/tempfile/temp.256.882634443 52,428,800 YES 52,428,800 2,147,483,648
UNDOTBS1 +DATA/testdb/datafile/undotbs1.269.882633801 31,457,280 YES 5,242,880 34,359,721,984
USERS +DATA/testdb/datafile/users.258.882633803 5,242,880 YES 1,310,720 34,359,721,984
[ CONTROL FILE ] +DATA/testdb/controlfile/current.264.882633485
[ ONLINE REDO LOG ] +DATA/testdb/onlinelog/group_1.286.882635039 52,428,800
[ ONLINE REDO LOG ] +DATA/testdb/onlinelog/group_1.287.882635039 52,428,800
[ ONLINE REDO LOG ] +DATA/testdb/onlinelog/group_2.289.882635185 52,428,800
[ ONLINE REDO LOG ] +DATA/testdb/onlinelog/group_2.290.882635187 52,428,800
[ ONLINE REDO LOG ] +DATA/testdb/onlinelog/group_3.280.882635099 52,428,800
[ ONLINE REDO LOG ] +DATA/testdb/onlinelog/group_3.282.882635099 52,428,800
------------------
sum 1,557,135,360
12 rows selected.
Conclusion : Finally we are migrated from Non-ASM storage to ASM now.
Step 1 - List the database files need to be migrated to ASM storage:
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Data File Report (all physical files) |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN tablespace FORMAT a30 HEADING 'Tablespace Name / File Class'
COLUMN filename FORMAT a75 HEADING 'Filename'
COLUMN filesize FORMAT 9,999,999,999,999 HEADING 'File Size'
COLUMN autoextensible FORMAT a4 HEADING 'Auto'
COLUMN increment_by FORMAT 999,999,999,999 HEADING 'Next'
COLUMN maxbytes FORMAT 999,999,999,999 HEADING 'Max'
BREAK ON report
COMPUTE sum OF filesize ON report
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_data_files d
, v$datafile v
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
WHERE
(d.file_name = v.name)
UNION
SELECT
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_temp_files d
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
UNION
SELECT
'[ ONLINE REDO LOG ]'
, a.member
, b.bytes
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$logfile a
, v$log b
WHERE
a.group# = b.group#
UNION
SELECT
'[ CONTROL FILE ]'
, a.name
, TO_NUMBER(null)
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$controlfile a
ORDER BY 1,2
/
=====================
Tablespace Name / File Class Filename File Size Auto Next Max
------------------------------ -------------------------------------------------- ------------------ ---- ---------------- ----------------
SYSAUX /u01/app/oracle/oradata/testdb/sysaux01.dbf 450,887,680 YES 10,485,760 34,359,721,984
SYSTEM /u01/app/oracle/oradata/testdb/system01.dbf 702,545,920 YES 10,485,760 34,359,721,984
TEMP /u01/app/oracle/oradata/testdb/temp01.dbf 20,971,520 YES 655,360 34,359,721,984
UNDOTBS1 /u01/app/oracle/oradata/testdb/undotbs01.dbf 31,457,280 YES 5,242,880 34,359,721,984
USERS /u01/app/oracle/oradata/testdb/users01.dbf 5,242,880 YES 1,310,720 34,359,721,984
[ CONTROL FILE ] /u01/app/oracle/flash_recovery_area/testdb/control
02.ctl
[ CONTROL FILE ] /u01/app/oracle/oradata/testdb/control01.ctl
[ ONLINE REDO LOG ] /u01/app/oracle/oradata/testdb/redo01.log 52,428,800
[ ONLINE REDO LOG ] /u01/app/oracle/oradata/testdb/redo02.log 52,428,800
[ ONLINE REDO LOG ] /u01/app/oracle/oradata/testdb/redo03.log 52,428,800
------------------
sum 1,368,391,680
10 rows selected.
Above is the list of all database files these we will be migrating to ASM storage from filesystem.
Step 2 - If your database is running using pfile then just edit the following parameters pointing to appropriate ASM diskgroups in the pfile and shutdown the database normal and start in nomount state.
control_files='=+DATA'
db_create_file_dest='+DATA'
db_recovery_file_dest='+DATA'
and if your database is running using spfile then simply alter the parameters accordingly.
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.
Step 3 - Shutdown the DB normally and start it up in nomount state.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 841162752 bytes
Fixed Size 1339768 bytes
Variable Size 494931592 bytes
Database Buffers 339738624 bytes
Redo Buffers 5152768 bytes
Step 4 - Restore the control file to ASM diskgroup '+DATA' from its original filesystem location.
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 17 15:57:43 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/testdb/control02.ctl';
Starting restore at 17-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/testdb/controlfile/current.264.882633485
Finished restore at 17-JUN-15
RMAN>
Step 5 - As of now, we have migrated the database controlfile to ASM storage now so lets start the database in MOUNT state.
SQL> alter database mount;
Database altered.
Step 6 - Backup the database as copy to the ASM storage.
RMAN> backup as copy database format '+DATA';
Starting backup at 17-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 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=+DATA/testdb/datafile/system.263.882633749 tag=TAG20150617T160228 RECID=1 STAMP=882633766
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/testdb/sysaux01.dbf
output file name=+DATA/testdb/datafile/sysaux.262.882633775 tag=TAG20150617T160228 RECID=2 STAMP=882633789
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/testdb/undotbs01.dbf
output file name=+DATA/testdb/datafile/undotbs1.269.882633801 tag=TAG20150617T160228 RECID=3 STAMP=882633800
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/testdb/controlfile/backup.259.882633801 tag=TAG20150617T160228 RECID=4 STAMP=882633802
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/testdb/users01.dbf
output file name=+DATA/testdb/datafile/users.258.882633803 tag=TAG20150617T160228 RECID=5 STAMP=882633803
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
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 17-JUN-15
channel ORA_DISK_1: finished piece 1 at 17-JUN-15
piece handle=+DATA/testdb/backupset/2015_06_17/nnsnf0_tag20150617t160228_0.257.882633807 tag=TAG20150617T160228 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 17-JUN-15
Step 7 - So now we have a copy of our current database at ASM storage now we can switch the database to copy.
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/testdb/datafile/system.263.882633749"
datafile 2 switched to datafile copy "+DATA/testdb/datafile/sysaux.262.882633775"
datafile 3 switched to datafile copy "+DATA/testdb/datafile/undotbs1.269.882633801"
datafile 4 switched to datafile copy "+DATA/testdb/datafile/users.258.882633803"
RMAN>
Step 8 - Now open the database.
SQL> alter database open;
Database altered.
SQL>
Step 9 - Now create the new temp file and drop the old one so that it is also stored on ASM.
SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES
------------------------------ -------------------------------------------------- ----------
TEMP /u01/app/oracle/oradata/testdb/temp01.dbf 20971520
SQL> alter tablespace temp add tempfile size 50m autoextend on maxsize 2048m;
Tablespace altered.
SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES
------------------------------ -------------------------------------------------- ----------
TEMP /u01/app/oracle/oradata/testdb/temp01.dbf 20971520
TEMP +DATA/testdb/tempfile/temp.256.882634443 52428800
Now drop the old temp file which is stored on Filesystem.
SQL> alter database tempfile '/u01/app/oracle/oradata/testdb/temp01.dbf' drop including datafiles;
Database altered.
SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES
------------------------------ -------------------------------------------------- ----------
TEMP +DATA/testdb/tempfile/temp.256.882634443 52428800
Step 10 - Let's migrate the database redo log files to ASM storage now.
SQL> select a.group#, a.member, b.bytes, b.status from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES STATUS
---------- -------------------------------------------------- ---------- ----------------
3 /u01/app/oracle/oradata/testdb/redo03.log 52428800 CURRENT
2 /u01/app/oracle/oradata/testdb/redo02.log 52428800 ACTIVE
1 /u01/app/oracle/oradata/testdb/redo01.log 52428800 INACTIVE
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 50m;
Database altered.
SQL> select a.group#, a.member, b.bytes, b.status from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES STATUS
---------- -------------------------------------------------- ---------- ----------------
3 /u01/app/oracle/oradata/testdb/redo03.log 52428800 INACTIVE
2 /u01/app/oracle/oradata/testdb/redo02.log 52428800 CURRENT
1 +DATA/testdb/onlinelog/group_1.287.882635039 52428800 INACTIVE
1 +DATA/testdb/onlinelog/group_1.286.882635039 52428800 INACTIVE
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 50m;
Database altered.
SQL> select a.group#, a.member, b.bytes, b.status from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES STATUS
---------- -------------------------------------------------- ---------- ----------------
3 +DATA/testdb/onlinelog/group_3.282.882635099 52428800 INACTIVE
2 /u01/app/oracle/oradata/testdb/redo02.log 52428800 INACTIVE
1 +DATA/testdb/onlinelog/group_1.287.882635039 52428800 CURRENT
1 +DATA/testdb/onlinelog/group_1.286.882635039 52428800 CURRENT
3 +DATA/testdb/onlinelog/group_3.280.882635099 52428800 INACTIVE
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 50m;
Database altered.
Now we are all migrated to ASM storage.
SQL> select a.group#, a.member, b.bytes, b.status from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES STATUS
---------- -------------------------------------------------- ---------- ----------------
3 +DATA/testdb/onlinelog/group_3.282.882635099 52428800 INACTIVE
2 +DATA/testdb/onlinelog/group_2.289.882635185 52428800 UNUSED
1 +DATA/testdb/onlinelog/group_1.287.882635039 52428800 CURRENT
1 +DATA/testdb/onlinelog/group_1.286.882635039 52428800 CURRENT
3 +DATA/testdb/onlinelog/group_3.280.882635099 52428800 INACTIVE
2 +DATA/testdb/onlinelog/group_2.290.882635187 52428800 UNUSED
Finally let's verify if all database files are migrated to ASM storage now:
Tablespace Name / File Class Filename File Size Auto Next Max
------------------------------ --------------------------------------------------------------------------- ------------------ ---- ---------------- ----------------
SYSAUX +DATA/testdb/datafile/sysaux.262.882633775 450,887,680 YES 10,485,760 34,359,721,984
SYSTEM +DATA/testdb/datafile/system.263.882633749 702,545,920 YES 10,485,760 34,359,721,984
TEMP +DATA/testdb/tempfile/temp.256.882634443 52,428,800 YES 52,428,800 2,147,483,648
UNDOTBS1 +DATA/testdb/datafile/undotbs1.269.882633801 31,457,280 YES 5,242,880 34,359,721,984
USERS +DATA/testdb/datafile/users.258.882633803 5,242,880 YES 1,310,720 34,359,721,984
[ CONTROL FILE ] +DATA/testdb/controlfile/current.264.882633485
[ ONLINE REDO LOG ] +DATA/testdb/onlinelog/group_1.286.882635039 52,428,800
[ ONLINE REDO LOG ] +DATA/testdb/onlinelog/group_1.287.882635039 52,428,800
[ ONLINE REDO LOG ] +DATA/testdb/onlinelog/group_2.289.882635185 52,428,800
[ ONLINE REDO LOG ] +DATA/testdb/onlinelog/group_2.290.882635187 52,428,800
[ ONLINE REDO LOG ] +DATA/testdb/onlinelog/group_3.280.882635099 52,428,800
[ ONLINE REDO LOG ] +DATA/testdb/onlinelog/group_3.282.882635099 52,428,800
------------------
sum 1,557,135,360
12 rows selected.
Conclusion : Finally we are migrated from Non-ASM storage to ASM now.
