|
| Wednesday, May 27, 2009 |
| Migrating Oracle10g DB to ASM |
Step By Step Instructions on Migrating Oracle10g Database to Automatic Storage management (ASM)
Disable Block change tracking:
SQL> select * from v$block_change_tracking;
STATUS
----------
FILENAME
--------------------------------------------------------------------------------
BYTES
----------
DISABLED
If not disabled then, disble using this command.
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Database altered.
SQL>
Shutdown Database Cleanly:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Create pfile and add/modify the below parameters:
[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 12:17:50 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create pfile from spfile;
File created.
Modify pfile with these parameters:
I have already created 2 ASM diskgroups DATA and FLASH.
*.control_files=(+DATA, +FLASH)
*.db_recovery_file_dest=+FLASH
*.db_recovery_file_dest_size=2147483648
*.db_create_file_dest=+DATA
*.db_create_online_log_dest_1=+FLASH
*.db_create_online_log_dest_2=+DATA -- optional if you want another online redo logs dest.
Create spfile back from modified pfile:
PS: take a copy of original spfile before you overwrite spfile using below command.
SQL> create spfile from pfile;
File created.
SQL> exit
Disconnected
Copy Database to ASM diskgroups using rman:
(1) start the instance on NOMOUNT state
(2) copy the controlfile from old location to ASM usin "resrore" rman command
(3) mount the database
(4) copy the datafiles to ASM disk group using rman "BACKUP AS COPY DATABASE" command
(5) Switch database to COPY and open the database.
[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 10:03:10 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
RMAN> restore controlfile from '/home/oracle/oradata/db10g/control01.ctl';
Starting restore at 21-JUL-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/db10g/controlfile/backup.256.596369129
output filename=+FLASH/db10g/controlfile/backup.256.596369131
Finished restore at 21-JUL-06
RMAN> startup mount
database is already started
database mounted
released channel: ORA_DISK_1
RMAN> configure device type disk parallelism 4;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
Starting backup at 21-JUL-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=151 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=150 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=149 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/home/oracle/oradata/db10g/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile fno=00003 name=/home/oracle/oradata/db10g/sysaux01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile fno=00002 name=/home/oracle/oradata/db10g/undotbs01.dbf
channel ORA_DISK_4: starting datafile copy
input datafile fno=00004 name=/home/oracle/oradata/db10g/users01.dbf
output filename=+DATA/db10g/datafile/undotbs1.259.596369341 tag=TAG20060721T100858 recid=2 stamp=596369352
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_3: starting datafile copy
copying current control file
output filename=+DATA/db10g/datafile/users.260.596369341 tag=TAG20060721T100858 recid=1 stamp=596369350
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:20
channel ORA_DISK_4: starting full datafile backupset
channel ORA_DISK_4: specifying datafile(s) in backupset
output filename=+DATA/db10g/controlfile/backup.261.596369361 tag=TAG20060721T100858 recid=3 stamp=596369364
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:06
including current SPFILE in backupset
channel ORA_DISK_4: starting piece 1 at 21-JUL-06
channel ORA_DISK_4: finished piece 1 at 21-JUL-06
piece handle=+DATA/db10g/backupset/2006_07_21/nnsnf0_tag20060721t100858_0.262.596369369 tag=TAG20060721T100858 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:10
output filename=+DATA/db10g/datafile/sysaux.258.596369341 tag=TAG20060721T100858 recid=4 stamp=596369390
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:05
output filename=+DATA/db10g/datafile/system.257.596369339 tag=TAG20060721T100858 recid=5 stamp=596369414
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:21
Finished backup at 21-JUL-06
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/db10g/datafile/system.257.596369339"
datafile 2 switched to datafile copy "+DATA/db10g/datafile/undotbs1.259.596369341"
datafile 3 switched to datafile copy "+DATA/db10g/datafile/sysaux.258.596369341"
datafile 4 switched to datafile copy "+DATA/db10g/datafile/users.260.596369341"
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
Migrate tempfile to ASM:
RMAN does not migrate the tempfile as part of the BACKUP AS COPY and SWITCH command becuase the tempfile is not listed in controlfile.
The tempfile has to be manually migrated to ASM.
[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 10:12:42 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name, bytes from v$tempfile;
NAME
--------------------------------------------------------------------------------
BYTES
----------
/home/oracle/oradata/db10g/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/db10g/tempfile/temp.264.596370217
Migrate and drop the old Online Redo Logs to ASM:
Run the below procedure to migrate the redo logs to ASM. This program is taken from OTN/metalink.
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;
/
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
PL/SQL procedure successfully completed.
SQL> SQL>
SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+FLASH/db10g/onlinelog/group_3.259.596373299
+FLASH/db10g/onlinelog/group_2.258.596373295
/home/oracle/oradata/db10g/redo01.log
+FLASH/db10g/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;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
PL/SQL procedure successfully completed.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+FLASH/db10g/onlinelog/group_3.259.596373619
+FLASH/db10g/onlinelog/group_2.258.596373615
+FLASH/db10g/onlinelog/group_1.261.596373613
+FLASH/db10g/onlinelog/group_4.257.596373293
+FLASH/db10g/onlinelog/group_5.260.596373609
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
DELETE THE OLD DATAFILES USING RMAN.
This way, it will also clear out the datafiles entry from controlfile.
[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 11:22:33 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target
connected to target database: DB10G (DBID=4283639931)
RMAN> run {
2> DELETE COPY OF DATABASE;
3> }
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=151 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=153 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=138 devtype=DISK
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
6 1 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/system01.dbf
7 2 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/undotbs01.dbf
8 3 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/sysaux01.dbf
9 4 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/users01.dbf
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy filename=/home/oracle/oradata/db10g/system01.dbf recid=6 stamp=596369439
deleted datafile copy
datafile copy filename=/home/oracle/oradata/db10g/undotbs01.dbf recid=7 stamp=596369439
deleted datafile copy
datafile copy filename=/home/oracle/oradata/db10g/sysaux01.dbf recid=8 stamp=596369440
deleted datafile copy
datafile copy filename=/home/oracle/oradata/db10g/users01.dbf recid=9 stamp=596369440
Deleted 4 objects
RMAN> exit
Recovery Manager complete.
REMOVE THE OLD ONLINE REDO LOGS FILES PHYSICALLY:
[oracle@node1-pub oracle]$ rm /home/oracle/oradata/db10g/redo*.log
[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 11:29:56 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Enable the block change tracking:
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Database altered.
SQL>Labels: Migrating Oracle10g DB to ASM |
posted by Srinivasan .R @ 1:17 AM  |
|
| 5 Comments: |
-
Thank you taking time to provide us with your valuable post. We strive provide our students with excellent care & we taking your comment to heart.As alway, we appreciate your confidences and trusted.
iOS Training in Chennai
-
-
-
Hello, First and foremost thing I would like to tell is Thankyou so much. I was in search of the topic on Migrating Oracle10g Database to Automatic Storage Management. oracle fusion financials training
-
|
| |
| << Home |
| |
|
|
|
|
Thank you taking time to provide us with your valuable post. We strive provide our students with excellent care & we taking your comment to heart.As alway, we appreciate your confidences and trusted.
iOS Training in Chennai