Migration Of Non-ASM 11g Database To ASM

Posted: April 23, 2013 in ASM
Tags: , ,

I found it bit difficult to get all the steps for migration of Non-ASM 11g database to ASM 11g. All most all of the web links i found starts directly with RMAN commands to do the actual job. Since in 11g we need to install the grid infrastructure seperatly for ASM, so i thought of posting a complete steps of doing migration in a single post. In 10g, ASM creation is included in the same installer through which we install database, so that was bit easy and straight forward. But 11g has a different installer for ASM. Let’s start this post and following assumptions are made:

1) I have a database YYAPRD running on normal file system
2) I have created disks DG1, DG2, DG3 and DG4 which will be used during ASM instance creation

I can’t include the steps for creating disks as i don’t have much experties on that. I just followed few links to create these disks. The links, which i have followed can be found here. Follow this link to download the following files:

1) p10404530_112030_LINUX_1of7.zip
2) p10404530_112030_LINUX_2of7.zip
3) p10404530_112030_LINUX_3of7.zip

First two files are for 11g database installer and third file is for 11g grid infrastructure which needs to be run before using ASM or before setting up cluster services for RAC database.

11g grid infrastructure will be installed in a seperate ORACLE_HOME then that of database and with different OS user as recommended by oracle. So, we have to create OS user “grid” and following OS groups:

1) asmdba
2) asmoper
3) asmadmin

Make sure that OS user “grid” has primary group as “oinstall” and secondary groups as “dba”, “asmdba”, “asmoper” and “asmadmin”. We need to modify OS user “oracle” too a bit. we need to add “asmdba” as secondary group to OS user “oracle”. After all this our OS users “grid” and “oracle” should look like below:

[oracle@NODE1 ~]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(asmdba)

[grid@NODE1 ~]$ id
uid=54322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(asmdba),54324(asmoper),54325(asmadmin)

Now, we unzip the files to some location on server and then run the runInstaller from the grid folder which will be created after unzipping the above files. In my case location of installer is /u01/11g_soft/11203_soft/grid. Following are the screen shots for grid infrastructure installation:

A1

 

Start the installer from ./runInstaller command as in above screen shot.

A2

Select “skip software update” and click next>

A3

Select the “configure grid infrastructure for standalone server” as we not using ASM for RAC database.

A4

click next>

A5

On this screen, you can see the disks which i created earlier. I have keept the redundency “External” so that only one disk is required for a disk group. For redundancy “Normal” we need to have a two disks in a diskgroup and for “High” we need a minimum for three disks. Allocation unit size of 1MB is fine. Select the disk which has to be added to diskgroup named “+DATA”.

A6

Provide password for SYS and ASMSNMP accounts for the ASM instance.

A7

if you created all the groups mentioned earlier in this post then default selection should be fine.

A8

Choose the ORACLE_BASE and ORACLE_HOME location. Make sure that ORACLE_HOME location is different then from database home location. In my case locations are as:

ASM ORACLE_HOME=/u01/app/grid/product/11.2.0/grid

DB   ORACLE_HOME=/u01/app/oracle/product/11.2.0

A9

click next>

A10

wait for installation to prompt for root.sh

New

 

 

 

 

 

 

 

run the root.sh from location mentioned above.

A11

 

 

 

 

 

Once root.sh is run successfully as in above screen shot, installation is completed for grid infrastructure with a ASM instance +ASM running on server.

A12

alert_+ASM.log can be found at location /u01/app/grid/diag/asm/+asm/+ASM/trace and below services are running from os user “grid” on server.


[grid@NODE1 ~]# ps -ef|grep grid
grid      3890     1  0 06:27 ?        00:00:02 /u01/app/grid/product/11.2.0/grid/bin/ohasd.bin reboot
grid      4444     1  0 06:28 ?        00:00:05 /u01/app/grid/product/11.2.0/grid/bin/oraagent.bin
grid      4457     1  0 06:28 ?        00:00:00 /u01/app/grid/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit
grid      4469     1  0 06:28 ?        00:00:00 /u01/app/grid/product/11.2.0/grid/bin/evmd.bin
grid      4476     1  0 06:28 ?        00:00:00 /u01/app/grid/product/11.2.0/grid/bin/cssdagent
grid      4501     1  0 06:28 ?        00:00:00 /u01/app/grid/product/11.2.0/grid/bin/ocssd.bin
grid      4523  4469  0 06:28 ?        00:00:00 /u01/app/grid/product/11.2.0/grid/bin/evmlogger.bin -o /u01/app/grid/product/11.2.0/grid/evm/log/evmlogger.info -l /u01/app/grid/product/11.2.0/grid/evm/log/evmlogger.log
grid      4586     1  0 06:29 ?        00:00:00 asm_pmon_+ASM
grid      4588     1  0 06:29 ?        00:00:00 asm_psp0_+ASM
grid      4590     1  0 06:29 ?        00:00:09 asm_vktm_+ASM
grid      4594     1  0 06:29 ?        00:00:00 asm_gen0_+ASM
grid      4596     1  0 06:29 ?        00:00:00 asm_diag_+ASM
grid      4598     1  0 06:29 ?        00:00:01 asm_dia0_+ASM
grid      4600     1  0 06:29 ?        00:00:00 asm_mman_+ASM
grid      4602     1  0 06:29 ?        00:00:00 asm_dbw0_+ASM
grid      4604     1  0 06:29 ?        00:00:00 asm_lgwr_+ASM
grid      4606     1  0 06:29 ?        00:00:00 asm_ckpt_+ASM
grid      4608     1  0 06:29 ?        00:00:00 asm_smon_+ASM
grid      4610     1  0 06:29 ?        00:00:00 asm_rbal_+ASM
grid      4612     1  0 06:29 ?        00:00:00 asm_gmon_+ASM
grid      4614     1  0 06:29 ?        00:00:00 asm_mmon_+ASM
grid      4616     1  0 06:29 ?        00:00:00 asm_mmnl_+ASM
grid      4618     1  0 06:29 ?        00:00:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root      4851  4810  0 07:05 pts/2    00:00:00 grep grid

Now, after our ASM instance is ready, we are all set for moving our database to ASM disks. Below are steps to move non-asm database to ASM. First of all get the details of all the file which needs to be moce to ASM.

SQL> select name from v$database;

NAME
---------
YYAPRD

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------
/u04/oradata/YYAPRD/system01.dbf
/u04/oradata/YYAPRD/sysaux01.dbf
/u04/oradata/YYAPRD/undotbs01.dbf
/u04/oradata/YYAPRD/users01.dbf

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------
/u04/oradata/YYAPRD/temp01.dbf

SQL> col member form a50
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
/u02/oradata/YYAPRD/redo01a.log
/u03/oradata/YYAPRD/redo01b.log
/u02/oradata/YYAPRD/redo02a.log
/u03/oradata/YYAPRD/redo02b.log
/u02/oradata/YYAPRD/redo03a.log
/u03/oradata/YYAPRD/redo03b.log

6 rows selected.

SQL> show parameter spfile

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
spfile                               string                           /u01/app/oracle/product/11.2.0
                                                                      .3/dbs/spfileYYAPRD.ora
SQL> show parameter control_file

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
control_file_record_keep_time        integer                          7
control_files                        string                           /u01/oradata/YYAPRD/control01.
                                                                      ctl, /u02/oradata/YYAPRD/contr
                                                                      ol02.ctl, /u03/oradata/YYAPRD/
                                                                      control03.ctl

Now, changed the location for control_file parameter to newly created ASM disks and set parameter DB_CREATE_FILE_DEST to ASM disks which will be used for datafiles. This parameter will serve as default location for all the datafiles which will be created in database. I am using multiplexing for controlfiles, so using two disk groups “+DATA” and “+DATA1”. Once parameters are set, restart the database in nomount state for RMAN to start movement.

SQL> alter system set control_files='+DATA','+DATA1' scope=spfile;

System altered.

SQL> alter system set db_create_file_dest='+DATA' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup nomount
ORACLE instance started.

Total System Global Area 2042241024 bytes
Fixed Size                  1345968 bytes
Variable Size             838862416 bytes
Database Buffers         1191182336 bytes
Redo Buffers               10850304 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now, connect with rman and restore control file first from the existing one. This is create a new control file in ASM disk “+DATA” and “+DATA1” and then mount the database and backup the database as copy in ASM disk “+DATA” and as last step switch database to copy. Following is the logs from this activity.

[oracle@NODE1 11.2.0.3]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Apr 8 23:24:36 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: YYAPRD (not mounted)

RMAN> restore controlfile from '/u01/oradata/YYAPRD/control01.ctl';

Starting restore at 08-APR-13
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=+DATA/yyaprd/controlfile/current.256.812244559
output file name=+DATA1/yyaprd/controlfile/current.256.812244561
Finished restore at 08-APR-13

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

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

Starting backup at 08-APR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u04/oradata/YYAPRD/system01.dbf
output file name=+DATA/yyaprd/datafile/system.257.812244625 tag=TAG20130408T233025 RECID=1 STAMP=812244720
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u04/oradata/YYAPRD/users01.dbf
output file name=+DATA/yyaprd/datafile/users.258.812244721 tag=TAG20130408T233025 RECID=2 STAMP=812244816
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u04/oradata/YYAPRD/sysaux01.dbf
output file name=+DATA/yyaprd/datafile/sysaux.259.812244827 tag=TAG20130408T233025 RECID=3 STAMP=812244873
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u04/oradata/YYAPRD/undotbs01.dbf
output file name=+DATA/yyaprd/datafile/undotbs.260.812244881 tag=TAG20130408T233025 RECID=4 STAMP=812244927
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/yyaprd/controlfile/backup.261.812244937 tag=TAG20130408T233025 RECID=5 STAMP=812244940
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
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 08-APR-13
channel ORA_DISK_1: finished piece 1 at 08-APR-13
piece handle=+DATA/yyaprd/backupset/2013_04_08/nnsnf0_tag20130408t233025_0.262.812244945 tag=TAG20130408T233025 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-APR-13

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/yyaprd/datafile/system.257.812244625"
datafile 2 switched to datafile copy "+DATA/yyaprd/datafile/sysaux.259.812244827"
datafile 3 switched to datafile copy "+DATA/yyaprd/datafile/undotbs.260.812244881"
datafile 4 switched to datafile copy "+DATA/yyaprd/datafile/users.258.812244721"

RMAN> alter database open;

database opened

RMAN> exit

Recovery Manager complete.

Now, we need to move the temporary tablespace files and online redo logfiles to ASM. Best way is to create new ones in ASM and drop the old ones. Following the steps to do that.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------
+DATA/yyaprd/datafile/system.257.812244625
+DATA/yyaprd/datafile/sysaux.259.812244827
+DATA/yyaprd/datafile/undotbs.260.812244881
+DATA/yyaprd/datafile/users.258.812244721

SQL> alter tablespace temp add tempfile size 500M;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------
/u04/oradata/YYAPRD/temp01.dbf
+DATA/yyaprd/tempfile/temp.263.812245559

SQL> alter database tempfile '/u04/oradata/YYAPRD/temp01.dbf' drop including datafiles;

Database altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------
+DATA/yyaprd/tempfile/temp.263.812245559

SQL> alter database add logfile group 4 ('+DATA','+DATA1') size 10M;

Database altered.

SQL> alter database add logfile group 5 ('+DATA','+DATA1') size 10M;

Database altered.

SQL> alter database add logfile group 6 ('+DATA','+DATA1') size 10M;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------- ------------ -----------
         1          1        115   10485760        512          2 NO  INACTIVE                244292 08-APR-2013       268271 08-APR-2013
         2          1        116   10485760        512          2 NO  INACTIVE                268271 08-APR-2013       271180 08-APR-2013
         3          1        117   10485760        512          2 NO  CURRENT                 271180 08-APR-2013   2.8147E+14
         4          1          0   10485760        512          2 YES UNUSED                       0                        0
         5          1          0   10485760        512          2 YES UNUSED                       0                        0
         6          1          0   10485760        512          2 YES UNUSED                       0                        0

		 SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------- ------------ -----------
         3          1        117   10485760        512          2 NO  INACTIVE                271180 08-APR-2013       274462 08-APR-2013
         4          1        118   10485760        512          2 NO  INACTIVE                274462 08-APR-2013       274465 08-APR-2013
         5          1        119   10485760        512          2 NO  CURRENT                 274465 08-APR-2013   2.8147E+14
         6          1          0   10485760        512          2 YES UNUSED                       0                        0

SQL> alter database drop logfile group 3;

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
+DATA/yyaprd/onlinelog/group_4.264.812246035
+DATA1/yyaprd/onlinelog/group_4.257.812246037
+DATA/yyaprd/onlinelog/group_5.265.812246095
+DATA1/yyaprd/onlinelog/group_5.258.812246097
+DATA/yyaprd/onlinelog/group_6.266.812246115
+DATA1/yyaprd/onlinelog/group_6.259.812246117

6 rows selected.

Now, the DB is migrated to ASM. Other things like FRA, archivlogs can be moved to ASM also by setting the respective parameters for these features.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s