Migration of Oracle 10g Database to ASM (Automatic Storage
Management) on Virtual Machine
Step 1: Open Virtual Machine (Vmware_workstation 7.0)
Step 2: Add Hard Drive (Here i am using 3 Hard
Drives).
a. Click on the "Edit virtual machine
settings".
b. Click on "Add..." Button.
c. Select "Hard Disk," press
"Next" button.
d. Select "Create a new virtual disk", press
"Next" button.
e. Select "SCSI" and "Independent
Persistent", press "next" button.
f. Specify the required Hard Drive space in
"Maximum disk size (GB)", press "Next" and
"Finish".
NOTE: Repeat the "Step 2:" as many disk you
want to add.
Step 3: Start Virtual Machine
Step 4: Now create the RAW Device
a. Partition the disk driver.
1. Login as root user
# fdisk -l
# cd /dev/
# ls sd*
sda sdb sdc
sdd sde
# fdisk
/dev/sdb (sdc, sdd ......)
command (m for help): n (Choose 'n' command)
command action
e extend
p primary
partition (1-4)
p (Choose 'p'
command)
Partition
Number (1-4): 1 (Choose '1')
First Cylinder
(1-1115, default 1): (Press Enter)
Last cylinder
or +size or +sizeM or +sizeK (1-1305,
default 1305): (Press Enter)
. . . .
. . . .
Command (m for
help): w (Choose 'w' command)
The partition
table has been altered! Calling
ioctl() to re-read partition table.
Syncing disks.
NOTE: Repeat the above same steps for remaining Disk.
b. Binding raw device with partition on new SCSI disk.
a. Login as root.
# raw
/dev/raw/raw1 /dev/sdb1
# raw
/dev/raw/raw2 /dev/sdc1
# raw
/dev/raw/raw3 /dev/sdd1
c. Change the ownership and peof the raw device.
a. Login as
root
# cd
/dev/raw
# chown
oracle:dba raw[1-3]
#chmod 600
/dev/raw/raw1
#chmod 600
/dev/raw/raw2
# chmod
600 /dev/raw/raw3
Step 4: ASM Creation using raw devices
a. To configure as ASM instance, start the Database
Configuration Assistant by issuing the "dbca" command as the oracle user.
b. On the "Welcome" screen, click the
"Next" button.
c. Select the "Configure Automatic Storage
Management" option, then click "Next" button.
d. If the Oracle Cluster Synchronization Service (CSS)
is not running currently, a warning screen will be displayed. Follow the
instruction and click "OK". Once the service started click
"Next" button.
NOTE: Script gives the o/p.
#
/u01/app/oracle/product/10.2.0/db_1/bin/localconfig add
/etc/oracle
does not exist. Creating it now.
Successfully
accumulated necessary OCR keys.
Creating OCR
keys for user 'root', privgrp 'root'..
Operation
successful.
Configuration
for local CSS has been initialized
Adding to
inittab
Startup will
be queued to init within 90 seconds.
Checking the
status of new Oracle init process...
Expecting the
CRS daemons to be up within 600 seconds.
CSS is active
on these nodes.
centos2
CSS is active
on all nodes.
Oracle CSS
service is installed and running under init(1M)
e. Enter the password for the ASM instance, then click
"Next" button.
f. On the conformation screen, click "Ok"
button.
g. Wait while the asm instance is cleared.
h. Once the asm creation is done, you are presented
with the "ASM Disk Group" screen. Click "Create New"
button.
i. On the "Create Disk Group" screen, enter
Disk Group Name of "DATA" and select the required level of redundancy.
- External:
ASM does not mirror the files. This option should only
be used if your disk are already
protected by some form of redundancy, like RAID
- Normal:
ASM performs
two-way mirroring of all files.
- High:
ASM performs
three-way mirroring of all files.
j. If you don't see disks which you believes should be
available, then press on "Change Disk Discovery path..."
NOTE: Add raw devices path like this:
/dev/raw/raw1,/dev/raw/raw2,/dev/raw/raw3
And then press "Ok" button.
h. On the "ASM Disk Groups " screen, Click
the "Finish" button.
i. Click "Yes" button to perform another
operation. (Now you are ready to create a database instance using ASM)
Step 5: Migration of Oracle 10g Database to ASM
a. Login as oracle user
b. Set the environment
c. start the listener
$lsnrctl start
d. Disable Block change tracking:
$ sqlplus "/as sysdba"
SQL> select
* from v$block_change_tracking;
STATUS
FILENAME BYTES
-----------------------------------------------
DISABLED
NOTE: If not disable then, disable using below
command:
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Database altered
e. Shutdown the database
SQL>shutdown immediate
SQL> exit
f. Create pfile and add/modify the following
parameters:
SQL> create pfile from spfile;
File created.
Parameters:
------------------
*.control_files=+DATA
*.db_recovery_file_dest=+DATA
*.db_recovery_file_dest_size=2147483648
*.db_create_file_dest=+DATA
*.db_create_online_log_dest_1=+DATA
g. Create spfile back from modified pfile:
SQL> create spfile from pfile;
File created
SQL> exit
h. Copy Database to ASM diskgroups using RMAN
- start the
instance on nomount
- Copy the control file from old location to
ASM using
- Mount the database
- copy the datafiles to ASM disk group using
rman
- Switch
database to copy and open the database
$ rman
RMAN> connect target
Connected to target database (not started)
RMAN> startup nomount
RMAN> restore controlfile from
'/u01/app/oracle/oradata/prod/contorl01.ctl';
RMAN> startup mount
RMAN> configure device type disk
parallelism 4;
RMAN> backup as copy database format
'+DATA';
RMAN> switch database to copy;
RMAN> alter database open;
RMAN> exit
i. Migration tempfile to ASM:
- tempfile
can't be migrate using RMAN, it has to be migrate manually to ASM.
$ sqlplus
"/as sysdba"
SQL> select
name, bytes from v$tempfile;
NAME BYTES
------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/temp01.dbf 20971520
SQL> create temporary tablespace temp1 tempfile
SIZE 100M extent management local
uniform size 1M;
Tablespace
created.
SQL> alter
database default temporary tablespace temp1;
Database
altered.
SQL> drop
tablespace temp including contents;
Tablespace
dropped.
SQL> create
temporary tablespace temp tempfile SIZE 100M extent management local uniform size 1M;
Tablespace
created.
SQL> alter
database default temporary tablespace temp;
Database
altered.
SQL> drop tablespace temp1 including contents;
Tablespace
dropped.
SQL> select
name from v$tempfile;
NAME
------------------------------------------------------------------------------
+DATA/prod/tempfile/temp.264.596370217
j. Migrate and drop the old Online Redo Logs to ASM
- Run the
following procedure to migrate the redo logs to ASM.
SQL> declare cursor orlc is select lf.member, l.bytes from v$log l,
v$logfile lf where
l.group# = lf.group# and lf.type = 'ONLINE' order by l.thread#,
l.sequence#;
type numTab_t is table of number index by binary_integer; type
charTab_t is table of varchar2(1024) index by binary_integer;
byteslist numTab_t; namelist charTab_t; procedure migrateorlfile(name IN varchar2,
bytes IN number) is
retry number;
stmt varchar2(1024); als
varchar2(1024) := 'alter system switch logfile';
begin select
count(*) into retry from v$logfile;
stmt := 'alter database add logfile size ' || bytes;
execute immediate stmt; stmt := 'alter database drop
logfile ''' || name || ''''; for i in 1..retry loop
begin execute
immediate stmt; exit; exception when
others then if i > retry then raise;
end if; execute immediate als;
end;
end loop;
end;
begin open
orlc; fetch orlc bulk collect into namelist, byteslist;
close orlc; for i in 1..namelist.count loop
migrateorlfile(namelist(i), byteslist(i));
end
loop;
end;
/
|
SQL> select
member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/prod/onlinelog/group_3.259.596373299
+DATA/prod/onlinelog/group_2.258.596373295
/u01/app/oracle/oradata/prod/redo01.log
+DATA/prod/onlinelog/group_4.257.596373293
SQL>
alter system switch logfile;
System
altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
- Re-Execute the same script again in order to
migrate the remaining ones.
SQL> declare
cursor orlc is select lf.member,
l.bytes from v$log l, v$logfile lf
where l.group# = lf.group# and lf.type = 'ONLINE'
order by l.thread#, l.sequence#;
type numTab_t
is table of number index by binary_integer; type charTab_t is table of
varchar2(1024) index by binary_integer;
byteslist
numTab_t; namelist charTab_t; procedure migrateorlfile(name IN varchar2,
bytes IN number) is retry number;
stmt
varchar2(1024); als varchar2(1024) := 'alter system switch logfile';
begin select
count(*) into retry from v$logfile; stmt := 'alter database add logfile size
' || bytes;
execute immediate stmt; stmt := 'alter database drop
logfile ''' || name || ''''; for i in 1..retry loop
begin execute immediate stmt; exit;
exception
when others then if i >
retry then raise; end if; execute
immediate als;
end;
end loop;
end;
begin open orlc;
fetch orlc bulk collect into namelist, byteslist; close orlc;
for i in 1..namelist.count loop
migrateorlfile(namelist(i), byteslist(i));
end loop;
end;
/
|
SQL> select
member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/prod/onlinelog/group_3.259.596373619
+DATA/prod/onlinelog/group_2.258.596373615
+DATA/prod/onlinelog/group_1.261.596373613
+DATA/prod/onlinelog/group_4.257.596373293
+DATA/prod/onlinelog/group_5.260.596373609
SQL> exit
k. Delete The old Datafiles using RMAN.
$ rman target /
RMAN> run {
2> delete
copy of database
3> }
RMAN> exit
l. Enable the block change tracking.
SQL> alter
database enable block change tracing;
Database
altered.
m. Now your database is ready to use using ASM.
