MADHU ORACLE APPS & RAC DBA: Migrate a Database from Non-ASM to ASM in Virtual Box

Step by Step Document for Migrate a Database from Non-ASM to ASM in Virtual Box:

Requirements:

1. OEL 5.5

2. 11g Grid Release 2

3. A running stand-alone Database

1. Creating Standalone ASM instance in Oracle 11gR2 using "Oracle Grid Infrastructure"

[root@11g ~]# uname -r

2.6.18-194.el5

[root@11g ~]#

In my case it is 64bit OEL 5.5 , so I downloaded following rpms to install Oracle ASM Lib

oracleasm-support-2.1.7-1.el5.x86_64.rpm

oracleasm-2.6.18-194.el5-2.0.5-1.el5.x86_64.rpm

oracleasmlib-2.0.4-1.el5.x86_64.rpm

2. The rpm's should be applied in the same order, otherwise you will get a dependencies error

rpm -Uvh oracleasm-support-2.1.7-1.el5.x86_64.rpm
rpm -Uvh oracleasm-2.6.18-194.el5-2.0.5-1.el5.x86_64.rpm

rpm -Uvh oracleasmlib-2.0.4-1.el5.x86_64.rpm

rpm –ivh –force –nodeps oracleasm*

3. Configure Oracle ASM Library driver

[root@11g ~]# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library

driver. The following questions will determine whether the driver is

loaded on boot and what permissions it will have. The current values

will be shown in brackets ('[]'). Hitting <ENTER> without typing an

answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle

Default group to own the driver interface []: dba

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

Initializing the Oracle ASMLib driver: [ OK ]

Scanning the system for Oracle ASMLib disks: [ OK ]

4. You can check the Oracle ASM status

[root@11g ~]# oracleasm status

Checking if ASM is loaded: yes

Checking if /dev/oracleasm is mounted: yes

5. Before that we need to get the RAW disk for installing ASM,

First we need to create 1 folder in windows level,

c:\ kavirajan\virtualbox\sharedstorage

6. Go to the command prompt , In command prompt go to,

c:\ kavirajan\virtualbox\sharedstorage

7. Create ASM disk by the following commands,

VBoxManage createhd --filename skr.vdi --size 8000 --format VDI --variant Fixed

8. Connect those disks to virtual machine RAC1.

VBoxManage storageattach ASMfromNONASM --storagectl "SATA" --port 1 --device 0 --type hdd --medium skr.vdi --mtype shareable

VBoxManage modifyhd ocr1.vdi --type shareable

9. Power on the virtual machine with the user root. (putty)

10. Check the disks are created or not,

#cd /dev

#ls –ltr sd*

11. Creating a new Partition for ASM disk

[root@11g ~]# fdisk /dev/sdb

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel. Changes will remain in memory only,

until you decide to write them. After that, of course, the previous

content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): p

Disk /dev/sdb: 8388 MB, 8388608000 bytes

255 heads, 63 sectors/track, 1019 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

Command (m for help): n

Command action

e extended

p primary partition (1-4)

Partition number (1-4): 1

First cylinder (1-1019, default 1):

Using default value 1

Last cylinder or +size or +sizeM or +sizeK (1-1019, default 1019):

Using default value 1019

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

[root@11g ~]# fdisk -l

Disk /dev/sda: 107.3 GB, 107374182400 bytes

255 heads, 63 sectors/track, 13054 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sda1 * 1 13 104391 83 Linux

/dev/sda2 14 13054 104751832+ 8e Linux LVM

Disk /dev/sdb: 8388 MB, 8388608000 bytes

255 heads, 63 sectors/track, 1019 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdb1 1 1019 8185086

12. To create a ASM disk using Oracle ASMLib

[root@11g ~]# oracleasm createdisk DISK1 /dev/sdb1

Writing disk header: done

Instantiating disk: done

13. To List and check the Oracle ASM disks

[root@11g ~]# oracleasm listdisks

DISK1

[root@11g ~]# oracleasm querydisk DISK1

Disk "DISK1" is a valid ASM disk

Creating Oracle Standalone ASM instance on Oracle 11gR2 (11.2.0.3)

1. Once the Oracle ASM disk is created, We have to create the ASM instance using Oracle Grid Infrastructure

In Oracle 11gR2, the ASM instance is Installed on new "Grid Infrastructure home".

2. Download the Grid Infrastructure software from Oracle support site and install it using./runInstaller.

3. Select "Configure Oracle Grid Infrastructure for a Standalone Server"

4. As you can see the ASM Disk -DISK1, which we created earlier is visible here. Give the Disk Group Name (DATA) and Redundancy select External.

5. In Oracle 11gR2 we have a new "SYSASM" Privilege to manage the ASM instance. Provide the password skr for the same.

6. Once the all the prerequisite are met, you will receive the "install" screen

7. At the end execute "root.sh" as a root user and complete the installation.

Verify whether the ASM instance is created and

[root@11g 11.2.0]# ps -ef | grep pmon

oracle 8228 1 0 14:43 ? 00:00:00 asm_pmon_+ASM

oracle 8313 1 0 14:45 ? 00:00:00 ora_pmon_orcl

root 8434 2955 0 14:46 pts/1 00:00:00 grep pmon

[root@11g 11.2.0]# su - oracle

[oracle@11g ~]$ . oraenv

ORACLE_SID = [orcl] ? +ASM

The Oracle base for ORACLE_HOME=/opt/oracle/product/11.2.0/grid is /opt/oracle

[oracle@11g ~]$ asmcmd

ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 4096 1048576 7993 7934 0 7934 0 N DATA/

ASMCMD> exit

[oracle@11g ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 14 14:48:16 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Automatic Storage Management option

SQL> select INSTANCE_NAME,VERSION,STATUS from v$instance;

INSTANCE_NAME VERSION STATUS

---------------- ----------------- ------------

+ASM 11.2.0.1.0 STARTED

SQL> select NAME,STATE,TOTAL_MB,FREE_MB,COMPATIBILITY from V$ASM_DISKGROUP;

NAME STATE TOTAL_MB FREE_MB

------------------------------ ----------- ---------- ----------

COMPATIBILITY

------------------------------------------------------------

DATA MOUNTED 7993 7934

11.2.0.0.0

Now we are ready migrate this database,

1. Check the database version and status

SQL> select INSTANCE_NAME,VERSION,DATABASE_STATUS from v$instance;

INSTANCE_NAME VERSION DATABASE_STATUS

---------------- ----------------- -----------------

orcl 11.2.0.1.0 ACTIVE

We should be able to check the ASM disk available from the instance which we created earlie

SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;

NAME STATE TOTAL_MB PATH

----------------------------------------------------------------

DISK1 NORMAL 7993 ORCL:DISK1

Migrating the SPFILE from Non-asm to ASM

1) If you are using a spfile, take a backup of spfile using rman to restore it to ASM disk

RMAN>BACKUP AS BACKUPSET SPFILE;

2) Now shutdown the database and start it in Mount stage
SQL> Shutdown immediate

SQL> STARTUP MOUNT;

3) Now restore the spfile from the backup to ASM disk as below

  RMAN>RESTORE SPFILE TO '+DATA/spfileorcl.ora';

4) You can see, still the parameter "spfile" is pointing to file system instead of ASM disk

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/11.2.0/db_1
/dbs/spfileorcl.ora
5) Shutdowm the instance

SQL> shutdown immediate

6) Delete "spfile<sid>.ora and init<sid>.ora from the $ORACLE_HOME/dbs directory and create a new init<sid>.ora with the following line of content and start the instance,

  vi initorcl.ora
SPFILE='+DATA/spfileorcl.ora'

SQL> Startup ( first it will search for spfile<sid>.ora which we deleted and next it will look for init<sid>.ora which we have moified with the above content )

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfileorcl.ora

Migrating Control files from Non-ASM to ASM

1) These are the current control files in non-asm

SQL>show parameter contol_files
control_files string /opt/oracle/oradata/orcl/control01.ctl,
/opt/oracle/oradata/orcl/control02.ctl

2) Start the instance in nomount state

SQL> startup nomount

3) You can move these control files to ASM using RMAN

[oracle@coltdb04 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:04:39 2012

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

connected to target database: LEARNDB (not mounted)

  RMAN>restore controlfile to '+DATA' from '/opt/oracle/oradata/orcl/control01.ctl';

4) You can check whether the control file are created

ASMCMD> find -t CONTROLFILE +DATA *
WARNING:option 't' is deprecated for 'find'
please use 'type'

   +DATA/ORCL/CONTROLFILE/current.256.787200593

5) Update the spfile with new control file location

SQL> alter system set control_files='+DATA/ORCL/CONTROLFILE/current.256.787200593' scope=spfile;

6) Now shutdown the instance and startup in mount stage and check for the control file location

SQL> shutdown immediate

SQL> startup mount

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/orcl/controlfile/curr
ent.256.787200593

Migrating the Datafiles from Non-ASM to ASM

1) Keep the database is in mount stage

2) Use RMAN "BACKUP AS COPY" method to migrate the datafiles from filesystem to ASM

[oracle@coltdb04 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jun 14 16:35:39 2013

connected to target database: ORCL (DBID=1345860456)

   RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

3) Switch the database to the copy created using the following RMAN command

   RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.258.787201633"
datafile 2 switched to datafile copy "+DATA/orcl/datafile/sysaux.257.787201553"
datafile 3 switched to datafile copy "+DATA/orcl/datafile/undotbs1.259.787201713"
datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.261.787201725"

4) Migrate the tempfile to ASM using RMAN

run

{

set newname for tempfile '/opt/oracle/oradata/orcl/temp01.dbf' to '+DATA';

switch tempfile all;

}

executing command: SET NEWNAME

using target database control file instead of recovery catalog

renamed tempfile 1 to +DATA in control file

5) Now open the Database

RMAN> ALTER DATABASE OPEN;

database opened

6) You can now check the datafiles created in ASM

SQL> select FILE_NAME from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/users.261.787201725
+DATA/orcl/datafile/undotbs1.259.787201713
+DATA/orcl/datafile/sysaux.257.787201553
+DATA/orcl/datafile/system.258.787201633

Migrating the Redo log files from Non-ASM to ASM

1) Identify the currently available redo log files using the following command

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

GROUP# MEMBER STATUS
---------- --------- -----------

3 /opt/oracle/oradata/orcl/redo03.log INACTIVE

2 /opt/oracle/oradata/orcl/redo02.log CURRENT

1 /opt/oracle/oradata/orcl/ INACTIVE

2) Add the new logfiles to ASM using following command

ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 3;

3) Now drop the old logfiles, A redo log member can only be dropped after being archived and being in INACTIVE mode. If needed, switch logfile multiple times until the logfile is ready for dropping.

ALTER DATABASE DROP LOGFILE MEMBER '/opt/oracle/oradata/orcl/redo01.log';
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE MEMBER '/opt/oracle/oradata/orcl/redo02.log';
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE MEMBER '/opt/oracle/oradata/orcl/redo03.log';

4) You can check the logfiles created now in ASM

GROUP# MEMBER STATUS
---------- --------- -----------
1 +DATA/orcl/onlinelog/group_1.264.787205393 CURRENT

2 +DATA/orcl/onlinelog/group_2.265.787205405 INACTIVE

3 +DATA/orcl/onlinelog/group_3.266.787205417 INACTIVE

Now your database is migrated to ASM

SQL> select file_name from dba_data_files;

FILE_NAME

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

+DATA/orcl/datafile/users.263.818092087

+DATA/orcl/datafile/undotbs1.260.818092069

+DATA/orcl/datafile/sysaux.259.818092043

+DATA/orcl/datafile/system.258.818092009

+DATA/orcl/datafile/example.261.818092077

Migrate a Database from Non-ASM to ASM in Virtual Box

Step by Step Document for Migrate a Database from Non-ASM to ASM in Virtual Box:

Requirements:

1. OEL 5.5

2. 11g Grid Release 2

3. A running stand-alone Database

No comments: