Oracle help at your desk....:-): 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

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

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.

What is ASM? How can we configure it?

ENJOY

Oracle help at your desk....:-)

Tuesday, July 5, 2011

Migrate database to ASM configuration.

1 comment: