Tuesday, July 5, 2011

Migrate database to ASM configuration.

Migration to ASM is a very simple task.Please follow the below steps one by one to so: -
Step:1

1. First need to set below parameter for controlfile,datafile or FRA.
NOTE: I have disk group  "DAT1"
SQL> alter system set control_files='+DAT1' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DAT1' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='+DAT1' scope=spfile;
System altered.

Step: 2 Restart DATABASE server to take above parameter value.
SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.
Total System Global Area 263639040 bytes

Fixed Size 1332552 bytes

Variable Size 218106552 bytes

Database Buffers 37748736 bytes

Redo Buffers 6451200 bytes


Step:3 Connect with RMAN session & restore controlfile on ASM system.


C:\>RMAN target=sys
Recovery Manager: Release 11.1.0.6.0 - Production on Thu Apr 17 10:12:53 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:connected to target database: TEST (not mounted)


Restore Controlfile.

RMAN> restore controlfile from 'C:\app\vishwanath\oradata\test\CONTROL01.ctl';
Starting restore at 17-APR-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISK
channel ORA_DISK_1: copied control file copyoutput file name=+DAT1/test/controlfile/current.256.652270419Finished restore at 17-APR-08


Step:4 Mount Oracle Database and take backup of database.


RMAN> alter database mount;
database mountedreleased channel: ORA_DISK_1

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


Starting backup at 17-APR-08allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=C:\APP\VISHWANATH\ORADATA\TEST\SYSTEM01.dbf output file name=+DAT1/test/datafile/system.257.652270565 tag=TAG20080417T101550 RECID=1 STAMP=652270748channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:06channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=C:\APP\VISHWANATH\ORADATA\TEST\SYSAUX01.dbf output file name=+DAT1/test/datafile/sysaux.258.652270761 tag=TAG20080417T101550 RECID=2 STAMP=652270908channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:35channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=C:\APP\VISHWANATH\ORADATA\TEST\UNDOTBS01.dbf output file name=+DAT1/test/datafile/undotbs1.259.652270927 tag=TAG20080417T101550 RECID=3 STAMP=652270952channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35channel ORA_DISK_1: starting datafile copycopying current control fileoutput file name=+DAT1/test/controlfile/backup.260.652270971 tag=TAG20080417T101550 RECID=4 STAMP=652270975channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=C:\APP\VISHWANATH\ORADATA\TEST\USERS01.dbf output file name=+DAT1/test/datafile/users.261.652270989 tag=TAG20080417T101550 RECID=5 STAMP=652270991channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 17-APR-08channel ORA_DISK_1: finished piece 1 at 17-APR-08piece handle=+DAT1/test/backupset/2008_04_17/nnsnf0_tag20080417t101550_0.262.652271003 tag=TAG20080417T101550 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 17-APR-08


RMAN> switch database to copy;


datafile 1 switched to datafile copy "+DAT1/test/datafile/system.257.652270565"
datafile 2 switched to datafile copy "+DAT1/test/datafile/sysaux.258.652270761"
datafile 3 switched to datafile copy "+DAT1/test/datafile/undotbs1.259.652270927"
datafile 4 switched to datafile copy "+DAT1/test/datafile/users.261.652270989"


Step:5 Again connect to sqlplus session and perform incomplete recovery


C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 17 10:26:24 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> recover database using backup controlfile until cancel;

ORA-00279: change 1071679 generated at 04/17/2008 10:11:10 needed for thread 1ORA-00289: suggestion : +DAT1ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'TEST'ORA-00280: change 1071679 for thread 1 is in sequence #14
Specify log: {=suggested filename AUTO CANCEL}

CANCEL

Media recovery cancelled.


Step:6 OPEN database with RESETLOGS option.


SQL> alter database open resetlogs;
Database altered.


Step:7 Drop old tempfile and create new tempfile in existing temp tablespace


SQL> alter database tempfile 'c:\app\vishwanath\oradata\test\temp01.dbf'  drop including datafiles;
Database altered.


SQL> alter tablespace temp add tempfile size 512m 2 autoextend on next 250m maxsize unlimited;
Tablespace altered.


SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES
------------------------------ ------------------------------ ----------
TEMP +DAT1/test/tempfile/temp.266. 536870912 652271571


Step:8 Recreate All redolog group on ASM diskgroup


SQL> select a.group#, a.member, b.bytes

2 from v$logfile a, v$log b where a.group# = b.group#;


GROUP# MEMBER BYTES

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

3 C:\APP\VISHWANATH\ORADATA\TEST\REDO03.LOG 52428800
2 C:\APP\VISHWANATH\ORADATA\TEST\REDO02.LOG 52428800
1 C:\APP\VISHWANATH\ORADATA\TEST\REDO01.LOG 52428800


SQL> select group#,status from v$log;
GROUP# STATUS

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

1 CURRENT

2 UNUSED

3 UNUSED


SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.


SQL> select group#,status from v$log;
GROUP# STATUS

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

1 ACTIVE

2 ACTIVE

3 CURRENT


SQL> alter database drop logfile group 1;

alter database drop logfile group 1*ERROR at line 1:ORA-01624: log 1 needed for crash recovery of instance test (thread 1)ORA-00312: online log 1 thread 1: 'C:\APP\VISHWANATH\ORADATA\TEST\REDO01.LOG'


When you get above error message then set checkpoint with below command.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 10m;
Database altered.


SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 10m;
Database altered.


SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 10m;
Database altered.


SQL> column member format a30

SQL> select a.group#, a.member, b.bytes

2 from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES

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

3 +DAT1/test/onlinelog/group_3. 10485760 277.652273117
2 +DAT1/test/onlinelog/group_2. 10485760 274.652273019
1 +DAT1/test/onlinelog/group_1. 10485760 271.652272977
1 +DAT1/test/onlinelog/group_1. 10485760 272.652272979
2 +DAT1/test/onlinelog/group_2. 10485760 275.652273021
3 +DAT1/test/onlinelog/group_3. 10485760 278.652273119
6 rows selected.


Step:9 Recreate SPFILE on ASM diskgroup


SQL> create pfile='c:\initTEST.ora' from spfile;
File created.
SQL> create spfile='+DAT1/spfileTEST.ora' from pfile='c:\initTEST.ora';
File created.

ENJOY

5 comments:

Zheng junxai5 said...

zhengjx20160428
kd 8
jordan retro 8
kate spade outlet
coach factory outlet online
coach outlet store online clearances
toms outlet
nfl jerseys
hollister clothing store
michael kors outlet clearance
true religion outlet
ray bans
air jordan homme
nike outlet store
ray ban outlet
burberry handbags
hollister clothing store
asics running shoes
montblanc pen
oakley canada
cheap nfl jerseys
coach outlet
michael kors outlet online
nike store outlet
coach factory outlet
nike air max 90
coach factory outlet
adidas stan smith
cheap jordan shoes
abercrombie and fitch
polo ralph lauren outlet
louis vuitton outlet stores
louis vuitton bags
louis vuitton outlet
polo ralph lauren outlet
oakley vault
cheap jerseys
gucci outlet online
coach outlet
lebron james shoes 13

oakleyses said...

cheap oakley sunglasses, nike air max, jordan shoes, polo outlet, longchamp outlet, louis vuitton, ray ban sunglasses, tory burch outlet, longchamp pas cher, oakley sunglasses, replica watches, longchamp outlet, nike free run, louis vuitton outlet, nike air max, polo ralph lauren, christian louboutin outlet, nike outlet, oakley sunglasses, burberry pas cher, oakley sunglasses, tiffany jewelry, louis vuitton, prada outlet, replica watches, ray ban sunglasses, jordan pas cher, sac longchamp pas cher, chanel handbags, tiffany and co, ugg boots, oakley sunglasses wholesale, longchamp outlet, uggs on sale, polo ralph lauren outlet online, christian louboutin uk, michael kors pas cher, gucci handbags, christian louboutin, christian louboutin shoes, prada handbags, nike roshe, nike free, air max, kate spade outlet, ray ban sunglasses, ugg boots, louboutin pas cher

oakleyses said...

michael kors outlet, hollister pas cher, ralph lauren uk, michael kors, north face uk, coach outlet store online, true religion outlet, mulberry uk, abercrombie and fitch uk, guess pas cher, true religion outlet, lululemon canada, ray ban pas cher, michael kors, uggs outlet, vans pas cher, nike free uk, michael kors outlet, michael kors outlet online, replica handbags, nike tn, michael kors outlet, oakley pas cher, burberry outlet, polo lacoste, nike air max uk, nike blazer pas cher, hollister uk, michael kors outlet online, sac hermes, uggs outlet, michael kors outlet online, burberry handbags, new balance, coach purses, true religion jeans, north face, kate spade, ray ban uk, michael kors outlet online, true religion outlet, nike air max, converse pas cher, nike air force, nike air max uk, nike roshe run uk, sac vanessa bruno

oakleyses said...

ghd hair, hollister clothing, ray ban, iphone cases, celine handbags, oakley, insanity workout, converse, abercrombie and fitch, instyler, gucci, lululemon, ferragamo shoes, baseball bats, nike huaraches, beats by dre, babyliss, louboutin, new balance shoes, mac cosmetics, hollister, herve leger, hermes belt, giuseppe zanotti outlet, timberland boots, vans, valentino shoes, nike trainers uk, asics running shoes, p90x workout, nfl jerseys, nike air max, vans outlet, reebok outlet, soccer shoes, north face outlet, soccer jerseys, converse outlet, longchamp uk, nike roshe run, wedding dresses, ralph lauren, hollister, bottega veneta, north face outlet, mont blanc pens, nike air max, jimmy choo outlet, mcm handbags, chi flat iron

oakleyses said...

canada goose outlet, canada goose, toms shoes, links of london, karen millen uk, doudoune moncler, pandora jewelry, louis vuitton, marc jacobs, montre pas cher, canada goose outlet, moncler uk, moncler, pandora charms, juicy couture outlet, louis vuitton, thomas sabo, replica watches, canada goose jackets, ugg uk, ugg, canada goose, ugg,uggs,uggs canada, louis vuitton, wedding dresses, juicy couture outlet, canada goose, swarovski, moncler, canada goose outlet, supra shoes, moncler outlet, lancel, coach outlet, canada goose uk, louis vuitton, moncler outlet, moncler, louis vuitton, swarovski crystal, ugg pas cher, barbour, pandora uk, barbour uk, ugg,ugg australia,ugg italia, moncler, hollister, pandora jewelry