Moneycontrol Brokerage Recos

Wednesday, June 17, 2015

Migrate Non-ASM Oracle Database to ASM Storage:

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 : &current_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.