Monday, June 14, 2010

Foglight for ORACLE : An Advanced Performance Monitoring and Management for Oracle Databases

Database health is critical to ensuring business continuity. To maintain peak database performance, database administrators (DBAs) need unattended, 24x7 data collection and automated alerting. Unfortunately, native tools do not offer these capabilities. And even without the capabilities, these tools still impose excessive overhead onto systems that might already be experiencing performance problems.


             

    However, with Quest’s Foglight® for Oracle, DBAs can easily detect, diagnose, resolve and report on performance issues wherever, whenever and however they occur.

     Foglight’s intuitive interface allows DBAs to assess enterprise-level performance at a glance. Through the interface, DBAs are instantly alerted to performance bottlenecks and can play back historical data to diagnose issues. To identify the root cause of performance problems and speed resolution, the solution offers built-in expert knowledge and workflows. And Foglight provides all of these capabilities with a minimal footprint, imposing no agent overhead on the monitored hosts.The solution can function as either a standalone application or as a component of a larger enterprise monitoring solution
     
    Key Features

    • Global View – Determine your most critical instance alarms and immediately take action to resolve performance issues, both for Oracle and across platforms.
    • Built-in Intelligence – Understand the health and activity of your Oracle enterprise with a variety of integrated management functionality and performance views; this includes pop-up advice with correlated workflows.
    • Adaptive Baselines – Track performance using automatic detection and calculation of normal ranges for all metrics, and receive alerts about deviations from normal activity.
    • RAC Monitoring – Receive global alerts for issues detected at the cluster and the instance levels, plus notification on cluster overhead and interconnect problems; also, track cluster balance based on a variety of workload measures.
    • Dataguard and ASM Monitoring – Monitor failover replication and manage storage efficiently
    • Historical Data – Navigate through performance diagnostics and alarm data to resolve recent performance issues from any drill-down screen.
    • Wait-State Analysis – Diagnose wait statistics to easily determine where waits are occurring and what is causing the event.
    • Comprehensive Alarm Workflow – Manage and annotate alarms, set up blackouts and search for past solutions.
    • Drag-and-Drop Reporting – Create custom views and reports quickly and easily.
    • Wizard-driven Installation – Leverage a powerful wizard to easily install and configure Foglight as a standalone solution or to add Oracle monitoring to existing infrastructure.
    • Simple Integration – Integrate Foglight seamlessly with other end-to-end enterprise monitors.
    • Automatic Instance Discovery – Deploy Foglight capabilities rapidly and painlessly with automatic instance discovery or specific hosts (you can also manually add instances).

    Wednesday, February 24, 2010

    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




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

    Monday, February 22, 2010

    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)

    Monday, January 25, 2010

    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

    Sunday, January 24, 2010

    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 RAC node unavailable with error: Server unexpectedly closed network connection6]clsc_connect: (0x251c670) no listener at (ADDRESS=(PROTOCOL=ipc)(KEY=OCSSD_LL_node2_))

     Early midnight I received a call from the monitoring team that one of the critical production database node is not available. As I am aware...