Migrating Databases From NON-ASM to ASM

1. First need to set below parameter for controlfile,datafile or FRA.

NOTE: I have two disk group here i am using "DGRP2" disk group.

SQL> alter system set control_files='+DGRP2' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DGRP2' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='+DGRP2' scope=spfile;
System altered.

2. Create New controlfile , datafile or spfile in ASM diskgroup.

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

Connect with RMAN session.

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\m.taj\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=+DGRP2/test/controlfile/current.256.652270419Finished restore at 17-APR-08

RMAN> alter database mount;
database mountedreleased channel: ORA_DISK_1

Take backup of database which we use for ASM diskgroup.

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

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\M.TAJ\ORADATA\TEST\SYSTEM01.DBFoutput file name=+DGRP2/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\M.TAJ\ORADATA\TEST\SYSAUX01.DBFoutput file name=+DGRP2/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\M.TAJ\ORADATA\TEST\UNDOTBS01.DBFoutput file name=+DGRP2/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=+DGRP2/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\M.TAJ\ORADATA\TEST\USERS01.DBFoutput file name=+DGRP2/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=+DGRP2/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 "+DGRP2/test/datafile/system.257.652270565"
datafile 2 switched to datafile copy "+DGRP2/test/datafile/sysaux.258.652270761"
datafile 3 switched to datafile copy "+DGRP2/test/datafile/undotbs1.259.652270927"
datafile 4 switched to datafile copy "+DGRP2/test/datafile/users.261.652270989"

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 : +DGRP2ORA-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.

OPEN database with RESETLOGS option.

SQL> alter database open resetlogs;
Database altered.

4. Drop old tempfile and create new tempfile in existing temp tablespace

SQL> alter database tempfile 'c:\app\m.taj\oradata\test\temp01.dbf' 2 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 +DGRP2/test/tempfile/temp.266. 536870912 652271571

5. 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\M.TAJ\ORADATA\TEST\REDO03.LOG 52428800
2 C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG 52428800
1 C:\APP\M.TAJ\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\M.TAJ\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 +DGRP2/test/onlinelog/group_3. 10485760 277.652273117
2 +DGRP2/test/onlinelog/group_2. 10485760 274.652273019
1 +DGRP2/test/onlinelog/group_1. 10485760 271.652272977
1 +DGRP2/test/onlinelog/group_1. 10485760 272.652272979
2 +DGRP2/test/onlinelog/group_2. 10485760 275.652273021
3 +DGRP2/test/onlinelog/group_3. 10485760 278.652273119
6 rows selected.

6. Recreate SPFILE on ASM diskgroup

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

7. Detele all backup copy from RMAN

RMAN> delete noprompt force copy;

Find more details

http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_33.shtml#Overview

Managing Diskgroups

Creating Diskgroup
1. Assign Unqiue name for each diskgroup
2. Specify redundancy level of disk group
There is three type of redundancy level
1. NORMAL redundancy with 2 way mirroring
2. HIGH redundancy with 3 way mirroring
3. EXTERNAL redundancy with NO mirroring.

3. Specify disks as belonging to specific failgroup.
If we manually not define any failgroup for disk then Oracle automatically create failgroup for each diskgroup.

4. Specify asm disks (v$asm_disk) for diskgroup.

5. Optionally specify disk group attributes such as compatibility or allocation unit size.

Find more http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#i1009309

SQL> conn / as sysasm
Connected to an idle instance.
SQL> startup nomount
ASM instance started
Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes

Create DISKGROUP with NORMAL REDUNDANCY
SQL> CREATE DISKGROUP dgrp1 NORMAL REDUNDANCY
2 FAILGROUP controller1 DISK
3 'e:\asmdisks\disk1' NAME diska1,
4 'e:\asmdisks\disk2' NAME diska2
5 FAILGROUP controller2 DISK
6 'e:\asmdisks\disk3' NAME diska3;
Diskgroup created.

SQL> select group_number,
2 name,
3 type,
4 total_mb,
5 free_mb
6 from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ------ ---------- ------
1 DGRP1 NORMAL 1536 1432

ADD NEW DISK in exiting DISKGROUP dgrp1
SQL> alter diskgroup dgrp1 add disk 'e:\asmdisks\disk4';
Diskgroup altered.

SQL> select group_number,
2 name,
3 type,
4 total_mb,
5 free_mb
6 from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ------ ---------- ------
1 DGRP1 NORMAL 2048 1940

Managing Capacity in Disk Groups
When ASM provides redundancy, such as when you create a disk group with NORMAL or HIGH redundancy, you must have sufficient capacity in each disk group to manage a re-creation of data that is lost after a failure of one or two failure groups. After one or more disks fail, the process of restoring redundancy for all data requires space from the surviving disks in the disk group. If not enough space remains, then some files might end up with reduced redundancy.

Find more http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#CHDGGGAD

Negative Values of USABLE_FILE_MB

Due to the relationship between FREE_MB, REQUIRED_MIRROR_FREE_MB, and USABLE_FILE_MB, USABLE_FILE_MB can become negative. Although this is not necessarily a critical situation, it does mean that:

• Depending on the value of FREE_MB, you may not be able to create new files.

• The next failure might result in files with reduced redundancy.

If USABLE_FILE_MB becomes negative, it is strongly recommended that you add more space to the disk group as soon as possible.

When Should External, Normal, or High Redundancy Be Used?

ASM mirroring runs on the database server and Oracle recommends to off load this processing to the storage hardware RAID controller by using external redundancy. You can use normal redundancy in the following scenarios:

• Storage system does not have RAID controller

• Mirroring across storage arrays

• Extended cluster configurations

In general, ASM mirroring is the Oracle alternative to third party logical volume managers. ASM mirroring eliminates the need to deploy additional layers of software complexity in your Oracle database environment.

Find more http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#CHDHDGDI

Next Part >>> http://dbataj.blogspot.com/2008/04/migrating-databases-from-non-asm-to-asm.html

ASM Instance Creation

ASM Instance Creation on Windows

1. Create INIT.ORA file for asm instance

DIAGNOSTIC_DEST=C:\app\m.taj\diag
_asm_allow_only_raw_disks= FALSE
instance_type=asm
remote_login_passwordfile=exclusive
memory_target=52m

Note: Minimum required parameters.

C:\>oradim -new -asmsid +ASM -startmode manual
Instance created.

C:\>oradim -edit -asmsid +ASM -startmode a

C:\>oradim -edit -asmsid +ASM -syspwd change_on_install

C:\>set ORACLE_SID=+ASM

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 8 11:22:47 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup pfile ='c:\init.ora';
ASM instance started

Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted


SQL> create spfile from pfile='c:\init.ora';

File created.

SQL> shutdown
ORA-15100: invalid or missing diskgroup name


ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

SQL> SELECT group_number, disk_number, mount_status, header_status, state, path FROM v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH

----------- ----------- ------- ------------ -------- -------------------- 0 1 CLOSED CANDIDATE NORMAL E:\ASMDISKS\DISK2 0 0 CLOSED CANDIDATE NORMAL E:\ASMDISKS\DISK1

Administratering ASM instance

http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asminst.htm#CHDGDJDF

http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_10.shtml

Next Part >>> http://dbataj.blogspot.com/2008/04/managing-diskgroups.html

Preparing disk for ASM instance

Preparing disk for ASM instance on Windows

We can use DISKPART.EXE to create primary,logical partitions and using ASMTOOL (commandline) & ASMTOOLG (GUI)stamp(map) for ASM instance.

Preparing Disks for ASM

http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmprepare.htm#BHCDCFIH

Diskpart.exe

http://download.oracle.com/docs/cd/B28359_01/install.111/b28250/racstorage.htm#BABFIDGI

ASMTOOL & ASMTOOLG

http://download.oracle.com/docs/cd/B28359_01/install.111/b28250/racstorage.htm#sthref212

Demostration

E:\>asmtool -list
NTFS \Device\Harddisk0\Partition1 37997M
NTFS \Device\Harddisk0\Partition2 38319M

E:\>mkdir asmdisks
E:\>cd e:\asmdisks
E:\asmdisks>asmtool -create e:\asmdisks\DISK1 512
E:\asmdisks>asmtool -create e:\asmdisks\DISK2 512
E:\asmdisks>cd c:\app\m.taj\product\11.1.0

More Reference on disks preparing for ASM

http://www.databasejournal.com/features/oracle/article.php/3571371

http://www.databasejournal.com/img/jsc_ASM_Listing2.html

Next Part >>> ASM Instance Creation on Windows

http://dbataj.blogspot.com/2008/04/asm-instance-creation.html

ASM - Introduction

ASM - Automatic storage management

Introduce with Oracle 10gr1.

About ASM

http://www.oracle.com/database/demos/asm.html

Benefits of Automatic Storage Management

http://download.oracle.com/docs/cd/B14117_01/server.101/b10743/mgmt_db.htm#sthref2270

ASM provides many of the same benefits as storage technologies such as RAID or logical volume managers (LVMs). Like these technologies, ASM enables you to create a single disk group from a collection of individual disk devices. It balances I/O to the disk group across all of the devices in the disk group. It also implements striping and mirroring to improve I/O performance and data reliability.
However, unlike RAID or LVMs, ASM implements striping and mirroring at the file level. This implementation enables you to specify different storage attributes for individual files in the same disk group.

Next Part >>>Preparing Disks for ASM instance

http://dbataj.blogspot.com/2008/04/preparing-disk-for-asm-instance.html