Converting ASM database to non-ASM environment

Following procedure need to be followed for migrating of a ASM database to non-ASM.

Create directories (locations) where you want to put your datafiles,controlfile,and redologfiles.
Now shutdown the ASM database and change the parameters control_files,db_create_file_dest,db_create_online_log_dest_1{2/3/4…}

C:\>set oracle_sid=CATDB
C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 13:09:07 2010
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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Go to init file or spfile , then modify the parameters mentioned above.
C:\oracle\product\10.2.0\db_1>
C:\oracle\product\10.2.0\db_1\database>notepad initcatdb.ora
Eg: control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL01.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL01.CTL’,’C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL01.CTL'

Accordingly change the other paths ASM DISK GROUP to normal file system.
C:\oracle\product\10.2.0\db_1\database>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 13:24:33 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.

SQL> STARTUP NOMOUNT PFILE='C:\oracle\product\10.2.0\db_1\database\initCATDB.ora
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 192940932 bytes
Database Buffers 411041792 bytes
Redo Buffers 7135232 bytes

Open a new session and connect to RMAN and then copy the controlfile from older location to non-ASM location using the following command:
C:\Documents and Settings\Quest>set oracle_sid=catdb
C:\Documents and Settings\Quest>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Feb 18 13:27:40 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: catdb (not mounted)

RMAN> restore controlfile from '+data\control01.ctl';
Starting restore at 18-FEB-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL03.CTL
Finished restore at 18-FEB-10

Now mount the database from existing RMAN session
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

Now take a backup copy into a file system location, this copied file system Database backup will be used as the actual non-ASM database files .
RMAN> backup as copy database format 'c:\oracle\product\10.2.0\oradata\catdb\%U.dbf';
Starting backup at 18-FEB-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DATA/catdb/datafile/system.259.711293871
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-SYSTEM_FNO-1_08L6C22K.DBF tag=TAG20100218
T233755 recid=11 stamp=711329913
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+DATA/catdb/datafile/sysaux.260.711293917
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-SYSAUX_FNO-3_09L6C242.DBF tag=TAG20100218
T233755 recid=12 stamp=711329942
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+DATA/catdb/datafile/undotbs1.261.711293941
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-UNDOTBS1_FNO-2_0AL6C24R.DBF tag=TAG201002
18T233755 recid=13 stamp=711329949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DATA/catdb/datafile/users.262.711293945
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-USERS_FNO-4_0BL6C24U.DBF tag=TAG20100218T
233755 recid=14 stamp=711329952
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CF_D-CATDB_ID-2256105880_0CL6C252.DBF tag=TAG20100218T233755 recid=15
stamp=711329955
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-FEB-10

We switch the actual ASM database to non-ASM using the below simple but powerful command.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-SYSTEM_FNO-1_08L6C2
2K.DBF"
datafile 2 switched to datafile copy "C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-UNDOTBS1_FNO-2_0AL6
C24R.DBF"
datafile 3 switched to datafile copy "C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-SYSAUX_FNO-3_09L6C2
42.DBF"
datafile 4 switched to datafile copy "C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-USERS_FNO-4_0BL6C24
U.DBF"

Switch the tempfiles from non-ASM to ASM ,
RMAN> run{
2> set newname for tempfile 1 to 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB/tempfile';
3> switch tempfile all;
4> }
executing command: SET NEWNAME
renamed tempfile 1 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB/tempfile in control file


Now our database is switched from ASM to non- ASM
Now you can open the database
RMAN> ALTER DATABASE OPEN (RESETLOGS);
database opened

Now, and create new in the non-ASM and drop the existing ASM ONLINE LOG FILES:
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 +DATA/catdb/onlinelog/group_3.265.711296273
2 +DATA/catdb/onlinelog/group_2.266.711296283
1 +DATA/catdb/onlinelog/group_1.265.711296273

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO04.LOG');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO05.LOG');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO06.LOG');
Database altered.

SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
6 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO06.LOG
5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO05.LOG
4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO04.LOG
1 +DATA/catdb/onlinelog/group_1.265.711296273
2 +DATA/catdb/onlinelog/group_2.266.711296283
3 +DATA/catdb/onlinelog/group_3.267.711296293
6 rows selected.

Make sure that the ASM online redo logfiles are not in current/active state.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ---------------------------------------------
6 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO06.LOG
5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO05.LOG
4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO04.LOG




********************************************************************************

Migration of Database from non-ASM to ASM (Part - 02)

Following procedure need to be followed for migrating a non-ASM database to ASM.

Now shutdown the non-ASM database and change the parameters control_files,db_create_file_dest,db_create_online_log_dest_1{2/3/4…}
C:\>set oracle_sid=CATDB
C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 13:09:07 2010
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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Go to init file or spfile , then modify the parameters mentioned above.
C:\oracle\product\10.2.0\db_1\database>notepad initcatdb.ora
Eg: control_files='+DATA\control01.ctl','+DATA\control02.ctl','+DATA\control03.ctl'
Accordingly change the other paths to ASM DISK GROUP.
C:\oracle\product\10.2.0\db_1\database>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 13:24:33 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP NOMOUNT PFILE='C:\oracle\product\10.2.0\db_1\database\initCATDB.oraORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 192940932 bytes
Database Buffers 411041792 bytes
Redo Buffers 7135232 bytes

Open a new session and connect to RMAN and then copy the controlfile from older location to ASM location using the following command:
C:\Documents and Settings\Quest>set oracle_sid=catdb
C:\Documents and Settings\Quest>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Feb 18 13:27:40 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: catdb (not mounted)
RMAN> restore controlfile from 'C:\oracle\product\10.2.0\oradata\catdb\control01.ctl';
Starting restore at 18-FEB-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/control01.ctl
output filename=+DATA/control02.ctl
output filename=+DATA/control03.ctl
Finished restore at 18-FEB-10

Now mount the database from existing RMAN session
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

Now take a backup copy into ASM disk group, this copied ASM Database backup will be used as the actual ASM database files .
RMAN> backup as copy database format '+DATA';
Starting backup at 18-FEB-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\SYSTEM01.DBF
output filename=+DATA/catdb/datafile/system.259.711293871 tag=TAG20100218T133750 recid=1 stamp=711293907
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\SYSAUX01.DBF
output filename=+DATA/catdb/datafile/sysaux.260.711293917 tag=TAG20100218T133750 recid=2 stamp=711293936
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\UNDOTBS01.DBF
output filename=+DATA/catdb/datafile/undotbs1.261.711293941 tag=TAG20100218T133750 recid=3 stamp=711293943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\USERS01.DBF
output filename=+DATA/catdb/datafile/users.262.711293945 tag=TAG20100218T133750 recid=4 stamp=711293946
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA/catdb/controlfile/backup.263.711293949 tag=TAG20100218T133750 recid=5 stamp=711293950
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-FEB-10
Now we are gonna see the powerful feature of RMAN, we switch the actual non-ASM database to ASM using the below simple but powerful command.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/catdb/datafile/system.259.711293871"
datafile 2 switched to datafile copy "+DATA/catdb/datafile/undotbs1.261.711293941"
datafile 3 switched to datafile copy "+DATA/catdb/datafile/sysaux.260.711293917"
datafile 4 switched to datafile copy "+DATA/catdb/datafile/users.262.711293945"

Switch the tempfiles from non-ASM to ASM ,

RMAN>
run{
2> set newname for tempfile 1 to '+DATA/catdb/tempfile';
3> switch tempfile all;
4> }
executing command: SET NEWNAME
renamed tempfile 1 to +DATA/catdb/tempfile in control file

Now our database is switched from non-ASM to ASM
Now you can can open the database
RMAN> ALTER DATABASE OPEN (RESETLOGS);
database opened.
Now, and create new online logfiles in the ASM and drop the existing non-ASM ONLINE LOG FILES:
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO03.LOG
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO02.LOG
1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO01.LOG

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('+data');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('+data');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('+data');
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO03.LOG
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO02.LOG
1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO01.LOG
4 +DATA/catdb/onlinelog/group_4.265.711296273
5 +DATA/catdb/onlinelog/group_5.266.711296283
6 +DATA/catdb/onlinelog/group_6.267.711296293

6 rows selected.
Make sure that the non-ASM online redo logfiles are not in current/active state.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ---------------------------------------------
4 +DATA/catdb/onlinelog/group_4.265.711296273
5 +DATA/catdb/onlinelog/group_5.266.711296283
6 +DATA/catdb/onlinelog/group_6.267.711296293

So, at last please comment on this post and critics are most welcome!!

Migration(Convertion) of Database from non-ASM to ASM (Part - 01)

Dear friends

I am posting the process of migration(conversion) of Oracle non-ASM database to ASM environment.The OS platform is Windows using Oracle Utility Recovery Manager (RMAN) for testing purpose. Before conversion I would like to describe few steps that deal with ASM Instance Creation, then we will go for Conversion!

ASM DISK CREATION IN WINDOWS USING ASMTOOL UTILITY…

Before converting database from non-ASM to ASM, we need to create few ASM disks to allocate them to ASM Instance and then later these ASM disks will be used for Database storage. You can create a mimicked ASM files in windows using this ASMTOOL utility.

Create a directory called ‘asmdir’ and In ‘asmdir’ directory create ASM disks using ‘ASMTOOL’. The below command creates an ASM disk of size 512MB

D:\asmdir>asmtool -create disk01 512
D:\asmdir>dir
Volume in drive D has no label.
Volume Serial Number is D80A-F55D
Directory of D:\asmdir
02/18/2010 11:00 AM

.
02/18/2010 11:00 AM

..
02/18/2010 11:02 AM 536,870,912 disk01
1 File(s) 536,870,912 bytes
2 Dir(s) 15,103,508,480 bytes free

Also we can create multiple ASM disks at once!

D:\asmdir>asmtool -create disk02 512 disk03 512 disk04 512
D:\asmdir>dir
Volume in drive D has no label.
Volume Serial Number is D80A-F55D
Directory of D:\asmdir
02/18/2010 11:07 AM

.
02/18/2010 11:07 AM

..
02/18/2010 11:02 AM 536,870,912 disk01
02/18/2010 11:06 AM 536,870,912 disk02
02/18/2010 11:07 AM 536,870,912 disk03
02/18/2010 11:09 AM 536,870,912 disk04
4 File(s) 2,147,483,648 bytes
2 Dir(s) 13,492,891,648 bytes free

ASM Instance Creation using DBCA:

We can create ASM Instance in Two ways , using Oracle tool DBCA (Database Configuration Assistant) or using manual method. For ease I have chosen DBCA.
The steps are as follow!

1.Run autoconfig: To create OCR key and CSS Service:
C:\oracle\product\10.2.0\db_1\BIN>localconfig add
Step 1: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'quest', privgrp ''..
Operation successful.
Step 2: creating new CSS service
successfully created local CSS service
successfully added CSS to home

After OCR Repository and CSS service creation, call Oracle DBCA tool to create ASM Instance.
2. Creating ASM Instance using DBCA tool:

You can call DBCA tool from command prompt also like C:\>dbca

Click NEXT , to go further, there will be three major steps to complete the ASM instance Creation
In below shown option, choose Configure Automatic Storage Management



Here you will get a new window to create ASM Instance, where you have to set ASM related parameters and other settings .



Choose ASM Parameters option.



Select Show Advanced Parameters option here you have to check all the ASM Instance Parameters and set them accordingly similarly shown below…



Here you have to set ASM DISKGROUPS, ASM_DISKSTRING ,ASM_POWER_LIMIT,INSTANCE_TYPE, and other similar parameters as in normal Database Instance. You can check the description of each and every parameter and its usage by clicking on SHOW DESCRIPTION,




Like the parameter ASM_DISKSTRING description is something like below and you have to mention the Disks which are created using ASMTOOL include their complete path.. Observe the Description given above about ASM_DISKSTRING parameter .

The ASM Instance should have its own SYS user to manage remotely. So give a different password for ASM Instance SYS user



Click NEXT to go further.. Where DBCA comes to action for ASM instance creation and make it up , then you can use these ASM disk groups for Your Database storage.



Click OK to start the action, Below you can see the window of ASM creation.



Now, Your ASM Instance is created with the specified ASM DISKS, now assign these ASM disks to one or more ASM DISK GROUPS



Here you have to create new ASM Disk Groups , click on Create New, option to create a new ASM Disk Group and Assign the Disks which are created and mentioned in previous steps..



Here mention the name of the Disk Group (eg: DATA) , You need to set the Redundancy type , there are three types of Redundancies , HIGH,NORMAL and EXTERNAL, Though there is a lot to explain about Redundancy I would like to give you some brief information about them which is something important to know..

REDUNDANCY Clause:
The REDUNDANCY clause lets you specify the redundancy level of the disk group.
NORMAL REDUNDANCY requires the existence of at least two failure groups . Automatic Storage Management provides redundancy for all files in the disk group according to the attributes specified in the disk group templates.
HIGH REDUNDANCY requires the existence of at least three failure groups. Automatic Storage Management fixes mirroring at 3-way mirroring, with each extent getting two mirrored copies. HIGH REDUNDANCY disk groups can tolerate the loss of two failure groups.
EXTERNAL REDUNDANCY indicates that Automatic Storage Management does not provide any redundancy for the disk group. The disks within the disk group must provide redundancy (for example, using a storage array), or you must be willing to tolerate loss of the disk group if a disk fails (for example, in a test environment). You cannot specify the FAILGROUP clause if you specify EXTERNAL REDUNDANCY.

Now, SELECTING DISK MEMEBERS , Which may belong to the below four status, those are
Former - Disk was formerly part of disk group.
Candidate - Available to be added to a disk group.
Member - Indicate part of a disk group.
Provisioned - Similar to candidate, made available using ASMLIB.

So , choose SHOW CANDIDATES, to select the available addable ASM disks,

Wait, Wait, something went wrong , though we select Show Candidate option We are unable to see the ASM DISKS, So there is something has to be done more , that is , Actually there is a hidden Parameter called _ASM_ALLOW_ONLY_RAW_DISKS , Which value has to be set to FALSE to cope up our mimicked so called ASM DISKS. Now cancel only CREATE DISK GROUP window, we shall reopen it after the below settings.

So connect to the ASM Instance from command prompt like below
C:\Documents and Settings\Quest>SET ORACLE_SID=+ASM
C:\Documents and Settings\Quest>SQLPLUS / AS SYSDBA
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 12:24:50 2010
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> ALTER SYSTEM SET "_ASM_ALLOW_ONLY_RAW_DISKS"=FALSE SCOPE=SPFILE;
System altered.

Now restart the ASM Instance to above changed parameter come into action,
SQL> SHUTDOWN IMMEDIATE
ORA-15100: invalid or missing diskgroup name
ASM instance shutdown
SQL> STARTUP
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1247420 bytes
Variable Size 57472836 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

Ignore the above error because we have not attached the ASM DISKS yes and not mounted.
Now choose CREATE NEW option again and You can see the CANDIDATE ASM DISKS .



Here You can choose the number of CANDIDATE disks to attach them to the Disk Group(Eg: DATA)



Here I have choosen all available CANDIDATE ASM DISKS and Redundency EXTERNAL is taken.
Now click OK



Now , DISK GROUP CREATION will be processed,



Yes ! a new DISK GROUP called DATA is created and mounted successfully, So this Disk Group can be now used for our DATABASE. Though there is a lot to explain about the ASM options, due to the fear that this might lead us to deviation from the actual concept I am forcibly reverting to our topic.

Click FINISH to wash your hands now.



Click YES to farewell the DBCA tool.

Now you are successfully created the ASM Instance using DBCA and Mounted the DISK GROUPS, You can check them below using command prompt.
C:\Documents and Settings\Quest>net start oracleasmservice+asm
The OracleASMService+ASM service is starting.....
The OracleASMService+ASM service was started successfully.
C:\Documents and Settings\Quest>set oracle_sid=+asm
C:\Documents and Settings\Quest>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 12:51:33 2010
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>
( Here all the DISK GROUPS are mounted so we are not getting the error that blinked last time.)

Check some important parameters here,
SQL> show parameter asm
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
_asm_allow_only_raw_disks boolean FALSE
asm_diskgroups string DATA
asm_diskstring string D:\ASMDIR\DIS*
asm_power_limit integer 1

Yes, it’s the time to move on to our actual concept, Migration of DATABASE from FILE SYSTEM to ASM environment using RMAN…. (End of part :1)

Optimize I/O and disk utilization in Oracle Databases - Space Manager with LiveReorg.

Managing exploding growth of Oracle databases requires a strategy to optimize I/O and available disk space while still meeting SLAs for application performance.

This is especially useful for business critical ERP applications such as SAP, Siebel, Oracle e-Business and Cerner. Such environments have high transaction volumes which require large database storage capacity and routine archiving of data. Inefficient storage can lead to poor I/O performance.
How do you fix this issue with no downtime to the applications or the database?

Space Manager with LiveReorg offers a simple yet comprehensive solution for space reclamation and reorganization, partitioning and capacity management for Oracle databases. DBAs can visualize and plan space use, detect and resolve space fragmentation, reclaim wasted space and project database growth for future planning.

•Perform online reorganizations to minimize downtime to applications and the database
•Support for Oracle database environments running ERP applications such as SAP, Oracle E-Business, PeopleSoft, Siebel and Cerner Millenium
•Resolve database performance issues due to fragmentation from archiving Oracle data
•Predict disk space growth needs to plan for hardware expenditures
•Eliminate unnecessary disk space purchases by reclaiming wasted space

Know more about Quest's Space Manager with LiveReorg,

http://www.quest.com/space_manager_with_livereorg/

Regards,

Shafiulla Syed

Advanced Performance Management for Critical Oracle Databases - Performance Analysis for Oracle

Most Oracle databases today are managed by Oracle's Enterprise Manager (OEM). This is essentially a basic solution for performance management, but what about those problems that you just can't seem to resolve using Oracle's native technology? This can introduce considerable risk when managing critical databases.

Foglight Performance Analysis, extends OEM's basic management to the next level providing advanced performance management for your busiest, most critical databases. Unleash the performance potential of your Oracle databases with:

•Intelligent alerting -- detect unusual behavior with adaptive baselines to uncover deviations to normal activity for immediate attention.

•Problem centric workflow -- prioritize problems by severity of impact and provide next steps workflow to determine root cause and resolve the problem.

•Common problem recognition -- leverage weeks, months of historical data providing real-time advisories to accelerate resolution

•Exposing degrading performance trends --compare the workload from different periods to identify the relative health of overall performance

•Eliminating change impacts -- quickly pinpoint elusive changes (planned or unplanned) and realign to maximize performance


StealthCollect® – High-frequency in-memory collection provides deep session-level collection at a fraction of the overhead imposed by tracing or SQL-based collection methods.
Performance Advisories – Built-in analytics and business intelligence is like having an in-house Oracle expert to review performance data and provide customized advice to guide DBAs toward maximizing performance.
IntelliProfile® – Sophisticated baselining algorithm continually tracks database behavior and identifies activity that is outside normal deviation.
Statement-level Wait Event Analysis – Provides wait event data down to the statement level, greatly decreasing the time required to resolve resource-related performance problems.

QuickCompare – the compare utility provides DBAs with a shortcut to identifying the root cause of performance deviations in application environments. By allowing customized comparisons of any aspect of an instance or application workload with full access to baseline data, identifying the cause of performance deviations is simple, and can be automated with scheduled reporting.
ChangeTracker – Automated change tracking enables DBAs to receive notifications of changes to server, instance, database and schema environments as well as application SQL degradations and a facility to define custom change alerts. Integrated alerting allows DBAs to use the analysis history view to correlate performance slowdowns with changes to quickly understand where to focus tuning and troubleshooting initiatives.
Integrated Reporting– Built-in wizard-driven reports allow DBAs to easily define, customize, and schedule reports on system health, performance, trends, changes, and a variety of other data points. Plus, by leveraging the long-term performance repository, detailed custom reports are always just a query away. WebView – Enterprise-level visibility, administrative shortcuts, and robust alerting are available from one easy-to-use web interface. WebView provides DBAs with a centralized, configurable set of dashboards to expose performance anomalies across any monitored Oracle and SQL Server instances.
Long-term Performance Repository – Unlimited performance and environmental data storage and dimensional analysis are provided in a data warehouse. DBAs can feel confident that application trends and the custom reports created on their application workload will be available and accessible using standard SQL query techniques. Dimensional Analysis – Traditional methods of analyzing application and workload trends require DBAs to trace activity and sift through huge amounts of data. Foglight provides a dimensional drill-down that exposes resource bottlenecks from poorly written code, inefficient database accesses or rogue applications or users more efficiently.

You can download a trial version of Quest's Performance Analysis for Oracle, the below link drops you there....

http://www.quest.com/Foglight-Performance-Analysis-for-Oracle/

Regards,

Shafiulla Syed

The FASTEST way to find and fix performance bottlenecks- SPOTLIGHT on ORACLE

Your hardware budget might not be growing, but your data volume sure is. And today's economic conditions put IT cost savings front and center. What can a DBA do? Save time finding and fixing performance bottlenecks to reduce downtime and IT costs.

Spotlight on Oracle provides real-time diagnostics so you can eliminate bottlenecks in Oracle and Oracle RAC environments. Show your boss the improvements you can make and the corresponding ROI with Spotlight on Oracle.
Key Benefits
Reduce time to resolution
Prevent or minimize unplanned downtime
Improve level of service to users and stakeholders
Increase knowledge of Oracle performance
Increase visibility into database processes—including RAC (node, cluster, interconnect)

Spotlight on Oracle includes a comprehensive home page that displays all critical database components at a glance for easy detection of bottlenecks and quick diagnosis.

Spotlight on Oracle with RAC diagnostics graphically represents your RAC cluster displaying the essential components including: individual nodes, the interconnect, the IO subsystem and the flows between these components.


The below link drops you at Quest's Spotlight on Oracle....

http://www.quest.com/spotlight-on-oracle/

Regards,

Shafiulla Syed

Oracle 11g Installation on Red Hat Linux 5

Dear Friends,

Yesterday, I installed & configured the Oracle 11gR1 (11.1.0) and created the database on Linux (Red Hat Enterprise Linux – 5.0) successfully. I would like to share the steps with my blog viewers what I have followed to install it.

Operating System Details: Linux – (Red Had Enterprise Linux – 5.0)

Database Version Details: Oracle 11g Release 1 (11.1.0)

Download Software: (linux_11gR1_database.zip)http://www.oracle.com/technology/software/products/database/index.html

Pre-Installation Tasks:

* Prepare the stage area for Oracle Software on the Serve

----- Choose the mount point where software needs to be staged.

/u01

----- Create the stage directories and change the ownership and permissions.

Su – rootPassword: xxxxx ( Enter your root password Here)

# mkdir –p /u01/11gStage

# chown –R oracle:dba /u01/11gStage

# chmod –R 755 /u01/11gStage

----- Copy the software in the stage areaftp the downloaded Oracle 11g software in this stage area and change the ownership and permissions.

$ cd /u01/11gStage

$ chown oracle:dba linux_11gR1_database.zip

$ chmod 755 linux_11gR1_database.zip

* Performing the pre-installation tasks

----- Check the size of physical RAM

$ grep MemTotal /proc/meminfo

Note: During the installation it will say failed when it checks the available physical RAM is less than 900 MB , no problem even if you have less than 900MB (if it’s for testing purpose), you can continue with the installation.

----- Check the size of SWAP space

$ grep SwapTotal /proc/meminfo

----- Check the free disk space in “/tmp” directory

$ df -k /tmp

----- Check the free space available on the system

$ df –k

* Checking the Software Requirements

----- The version of Linux is installed

$ uname –a

----- Check the following required packages are installed.

binutils-2.15.92.0.2-18

compat-libstdc++-33.2.3-47.3

elfutils-libelf-0.97-5

elfutils-libelf-devel-0.97-5

glibc-2.3.9.4-2.19

glibc-common-2.3.9.4-2.19

glibc-devel-2.3.9.4-2.19

gcc-3.4.5-2gcc-c++-3.4.5-2

libaio-devel-0.3.105-2

libaio-0.3.105-2

libgcc-3.4.5

libstdc++-3.4.5-2

libstdc++-devel-3.4.5-2

make-3.80-5

sysstat-5.0.5

unixODBC-2.2.11

unixODBC-devel-2.2.11

----- To check RPMS are installed or not.Login as ROOT user

# rpm –q libaio-devel-0.3.105-2

# rpm –q unixODBC-devel-2.2.11

…………………………………………………………Like this check for all the above RPMS.

----- Install the missing packages or rpms.

First download the missing RPMS from the relevant Linux websites, or if you have the CDs with you, used during the Linux Installation, you can use them to find the missing rpms from that.

----- To install or upgrade RPMS are installed or not.Login as the ROOT user

# rpm –ivh unixODBC-devel-2.2.11.3-1.i386.rpm

Or

# rpm –Uvh unixODBC-devel-2.2.11.3-1.i386.rpm

* Creating Required Operating System Groups and Users

----- Check and create DBA groupSu – rootPassword: xxxxx ( Enter your root password Here)

# cat /etc/groupgrep dba# /usr/sbin/groupadd dba

----- Check and create ORACLE user and assign dba group.

# cat /etc/passwdgrep oracle

# /usr/sbin/useradd –d “/home/oracle” –m –g dba –c “Oracle 11g Owner” oracle

----- Set the password of the ORACLE user

# passwd oracle

* Create Required Directories

----- The Oracle Base Directory and Oracle Home Directories

Su – rootPassword: xxxxxx (Enter the password for root here)

# mkdir –p /u01/app/oracle/product/11.0.1

# mkdir –p /u02/oradata

----- Chang the owner and group of the directories

# chown –R oracle:dba /u01/app/oracle/product/11.0.1

# chown –R oracle:dba /u02/oradata

----- Change the permissions of the directories

# chmod –R 755 /u01/app/oracle/product/11.0.1

# chmod –R 755 /u02/oradata

* Configuring Kernel Parameters

----- Check the current parameters and values in /etc/sysctl.conf

# cat /etc/sysctl.conf

Note: Take a back up of this file before changing the values and parameters.

# cp –p /etc/sysctl.conf /etc/sysctl.conf.old

----- Check and add the below parameters in /etc/sysctl.conf

fs.file-max = 76800

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 4194304

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 262144

----- To change the current values of the kernel parameters

# /sbin/sysctl -p

----- Make sure that the parameters and values in /etc/system file are set properly

# cat /etc/sysctl.conf

----- Check and set the following lines in the /etc/security/limits.conf file.

Note: Take a back up of this file before changing the values and parameters.

# cp –p /etc/security/limits.conf /etc/security/limits.conf.old

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

----- Add or edit the following line in the /etc/pam.d/login file, if it does not already exist

Note: Take a back up of this file before changing the values and parameters.

# cp –p /etc/pam.d/login /etc/pam.d/login .old

session required /lib/security/pam_limits.so

session required pam_limits.so

* Configuring the Oracle User’s Environment

----- Check and change the contents of .profile or .lgoin files.

$ vi .bash_profile

----- Remove the oracle environment variables set already.

----- Check the DISPLAY variable is set, if not, then set the variable.

$ echo $DISPLAY$ DISPLAY=:0.0

$ export DISPLAY

Su – rootPassword: xxxx ( Enter the root password here)

# xhost +

----- Make sure that xclock command is running

$ xlcok

*Installation of Oracle Software:

------- Login as oracle on the server

Su – oraclePassword: xxxxx (Oracle user password)

------- Go the directory where Oracle software is staged.

$ cd /u01/11gStage

------------ Unzip the software file

$ unzip linux_11gR1_database.zip

------------- Run/Launch the runinstaller

$ cd /u02/11gStage/database

$./.runInstaller

* Answer to the subsequent wizards or screens options with the proper information

*At the end of Installation you will get two scripts , run this scripts as 'root' user "/u01/app/oracle/product/11.1.0/db_1/root.sh" and

"/u01/app/oraInventory/orainstRoot.sh"

Conclusion:

I have followed the above series of steps for installing on one of Linux boxes. I would request and suggest them to go through the referenced links below, who are willing to do this with other options during the installation and as per their requirement.
References:
Oracle Database Installation Guide 11g Release 1 (11.1) for Linux
http://download.oracle.com/docs/cd/B28359_01/install.111/b32002/pre_install.htm#CHDHFGBJ
Installing Oracle Database 11g on Linux
http://www.oracle.com/technology/obe/11gr1_db/install/dbinst/dbinst.htm
Oracle 11g Documentations
http://www.oracle.com/pls/db111/homepage
Others:
http://www.datasoftech.com/library.html
****** Your suggestions and comments are welcomed. ******