Oracle DBA Tip and Trick

Wednesday, May 28, 2008

Secrets for optimizing Oracle table inserts

10:16 PM Niramit Soonthawong No comments

When loading large-volumes of data, you have several choices:

1 - Tune INSERTS - Parallelized programs doing concurrent INSERT statements

2 - Tune SQL*Loader - Using sqlldr Direct Load, and adjusting parameters improves INSERT performance.

3 - Tune imports - Use Oracle Data Pump (Formally Oracle import utility) - Here are tips for hypercharging Oracle import.

Optimizing Oracle INSERT performance

When using standard SQL statements to load Oracle data tables, there are several tuning approaches:

a - Manage segment header contention for parallel inserts - Make sure to define multiple freelist (or freelist groups) to remove contention for the table header. Multiple freelists add additional segment header blocks, removing the bottleneck. You can also use Automatic Segment Space Management (bitmap freelists) to support parallel DML, but ASSM has some limitations.

Read more about secrets for optimizing Oracle table inserts here:
http://oracle-tips.c.topica.com/maalfseabGRCBcilq9pb/

Oracle Business Intelligence (BI) Software Benefits and Challenges

1:11 PM Niramit Soonthawong No comments

Oracle BI Suite EE Plus represents significant opportunities and challenges for Oracle E-Business Suite (EBS) customers. Using automated conversion tools and services, there is now a cost-effective method of migrating that minimizes downtime and maximizes the benefits your organization receives from Oracle's next generation BI platform.

Attend this Webcast to learn about successful strategies for using Oracle BI Suite EE Plus with the E-Business Suite to provide real-time access to transaction data for operational reporting. Explore specific topics, including:

* Benefits of Oracle BI Suite EE Plus.
* Challenges of integrating Oracle BI Suite EE Plus with Oracle EBS.
* Provide an instant self-service reporting environment.
* How to reduce the time, cost, and risks during migration.

Get this white paper at: http://searchoracle.bitpipe.com/data/document.do?res_id=1204913038_975

Utility Computing Made Simple: Automated Provisioning and Patching Solutions for Oracle Grid Computing

12:25 PM Niramit Soonthawong No comments

Utility computing is all about enabling system resources, including the grid, server farm, and clustering, to be acquired for different types of applications.

Many enterprises are adopting grid and utility computing technologies because of their benefits. This document discusses the growth of enterprise computing.

Oracle Enterprise Manager provides these benefits:

* Centralized control for software lifecycle, from deploying to uninstalling the software
* Out-of-the-box best practices for patching and rolling out software
* Reduction in human error because of minimal human involvement in the patching and provisioning processes
* Incorporation of all the best practices in patching and provisioning procedures from Oracle's experience in the field
* Accelerated service delivery time

Download here: Utility Computing Made Simple: Automated Provisioning and Patching Solutions for Oracle Grid Computing

Master Data Management

12:22 PM Niramit Soonthawong No comments

If Master data management (MDM) is defined as the processes and tools which centrally and persistently define non-transactional entities of an organization, then its execution requires consideration of many moving parts. There is the data itself, data governance, business rules, data storage and implementation. This white paper provides independent insight and guidance as to which approach might work best for your organization.

Download here: Master Data Managementw

Monitor Oracle Application Health and Performance with Middleware

12:15 PM Niramit Soonthawong No comments

Organizations are finding limitations in orchestrating processes across ERP, SCM and CRM applications. Business and IT leaders deploy middleware with a sharp eye on the health and performance of key business applications.

Learn how you can narrow your short list significantly with a more strategic approach to IT portfolio management and application synergies. By considering the potential business benefits and teaming middleware with familiar applications, you can move to new levels of performance and response without making huge investments.

Download here: Monitor Oracle Application Health and Performance with Middleware

Transactional Data Management for Eliminating Database Downtime When Upgrading or Migrating from Oracle 8i or 9i to Oracle 10g

12:34 PM Niramit Soonthawong No comments

Oracle 8i and 9i database customers are facing more urgency to upgrade to Oracle 10g or higher, particularly where more business-critical applications are supported. In many cases, cross-platform migrations may also need to occur along with the database upgrade. Despite this necessary IT project, users expect uninterrupted availability and/or SLAs (service level agreements) dictate uptime requirements. Therefore any outage, even if scheduled or "planned", has a negative impact on the business.

Learn about a practical, proven solution that tackles the challenge of upgrading or migrating to Oracle database version 10g without taking database downtime. Learn how you can successfully upgrade or migrate to Oracle 10g as well as:

* Achieve rolling upgrade or migration.
* Keep transactions in synch across the databases.
* Manage partial or phased migrations or upgrades.
* Conduct data verification post-upgrade or migration.
* Implement easy and reliable failover strategy.

Get this white paper at: http://searchoracle.bitpipe.com/data/document.do?res_id=1139491378_804

How to Create Oracle Database Manually

8:15 PM Niramit Soonthawong No comments

CREATING THE DATABASE MANUALLY


This method involves typing the create database statement within SQL*DBA.
Using this method allows for more flexibility such as specifying
the MAXDATAFILES parameter or specifying multiple SYSTEM tablespace
database files. However by doing this manually there is also a greater
possibility of syntax errors. In addition there is no logfile automatically
created to record the options which have been specified.

Steps for Method I:

1. Set unix environment (SEE SETTING UNIX EVIRONMENT SECTION ABOVE).

2. Create a new init.ora for your new database by copying the default
   one provided by Oracle:

   % cp $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/initNEW_NAME.ora

3. Change db_name parameter in the new initNEW_NAME.ora from DEFAULT to
   the new database name.

4. Startup sql*dba in line mode:

      sqldba lmode=y

5. Connect to the instance, and startup in a 'NOMOUNT' state:

      SQLDBA> connect internal
      Connected.
      SQLDBA> startup nomount
      ORACLE instance started.
      SQLDBA>

6. Refer to the SQL Language Reference Guide for the 'CREATE DATABASE'
   statement syntax - page 4-148.

   Here is a sample create database statement:

      SQLDBA> create database NEW_NAME
           2> logfile group 1 ('oracle_home/dbs/log1NEW_NAME.dbf') size 500K,
           3>         group 2 ('oracle_home/dbs/log2NEW_NAME.dbf') size 500K
           4> datafile 'oracle_home/dbs/dbsNEW_NAME.dbf' size 20M
           5> maxdatafiles 50;

7. Once completed run catalog.sql located in the oracle_home/rdbms/admin
   directory. This script must be run under the 'SYS' user or connected
   'internal'.

   NOTE: catproc.sql must also be run if you have the procedural option
         installed.

      SQLDBA>@oracle_home/rdbms/admin/catalog.sql
      SQLDBA>@oracle_home/rdbms/admin/catproc.sql

8. After the database has been created, the SYSTEM tablespace and SYSTEM
   rollback segment will exist. However, a second rollback segment in the
   SYSTEM tablespace must be created and activated before any other
   tablespaces can be created in the database (Refer to SQL Lanuguage
   Reference Manual for full syntax).

      Creating the rollback segment:

        SYNTAX: CREATE ROLLBACK SEGMENT system2
                TABLESPACE SYSTEM
                STORAGE (...);

      Activating the rollback segment:

        SYNTAX: ALTER ROLLBACK SEGMENT system2 ONLINE;

9. Modify the /etc/oratab file by adding the new database name.  This
   is used by dbstart to startup all databases with a 'Y' entry in this
   file.  (See page 4-17 of Oracle for Unix technical Reference Guide).

Source:http://www.fors.com/orasupp/unix/13481_1.HTM

How to Create Oracle Database Manually

8:11 PM Niramit Soonthawong No comments

CREATING THE DATABASE MANUALLY

This method involves typing the create database statement within SQL*DBA.
Using this method allows for more flexibility such as specifying
the MAXDATAFILES parameter or specifying multiple SYSTEM tablespace
database files. However by doing this manually there is also a greater
possibility of syntax errors. In addition there is no logfile automatically
created to record the options which have been specified.

Steps for Method I:

1. Set unix environment (SEE SETTING UNIX EVIRONMENT SECTION ABOVE)].

http://sabdarsyed.blogspot.com/2008/09/setting-oracle-environment-variable.html

2. Create a new init.ora for your new database by copying the default
   one provided by Oracle:

   % cp $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/initNEW_NAME.ora

3. Change db_name parameter in the new initNEW_NAME.ora from DEFAULT to
   the new database name.

4. Startup sql*dba in line mode:

      sqldba lmode=y

5. Connect to the instance, and startup in a 'NOMOUNT' state:

      SQLDBA> connect internal
      Connected.
      SQLDBA> startup nomount
      ORACLE instance started.
      SQLDBA>

6. Refer to the SQL Language Reference Guide for the 'CREATE DATABASE'
   statement syntax - page 4-148.

   Here is a sample create database statement:

      SQLDBA> create database NEW_NAME
           2> logfile group 1 ('oracle_home/dbs/log1NEW_NAME.dbf') size 500K,
           3>         group 2 ('oracle_home/dbs/log2NEW_NAME.dbf') size 500K
           4> datafile 'oracle_home/dbs/dbsNEW_NAME.dbf' size 20M
           5> maxdatafiles 50;

7. Once completed run catalog.sql located in the oracle_home/rdbms/admin
   directory. This script must be run under the 'SYS' user or connected
   'internal'.

   NOTE: catproc.sql must also be run if you have the procedural option
         installed.

      SQLDBA>@oracle_home/rdbms/admin/catalog.sql
      SQLDBA>@oracle_home/rdbms/admin/catproc.sql

8. After the database has been created, the SYSTEM tablespace and SYSTEM
   rollback segment will exist. However, a second rollback segment in the
   SYSTEM tablespace must be created and activated before any other
   tablespaces can be created in the database (Refer to SQL Lanuguage
   Reference Manual for full syntax).

      Creating the rollback segment:

        SYNTAX: CREATE ROLLBACK SEGMENT system2
                TABLESPACE SYSTEM
                STORAGE (...);

      Activating the rollback segment:

        SYNTAX: ALTER ROLLBACK SEGMENT system2 ONLINE;

9. Modify the /etc/oratab file by adding the new database name.  This
   is used by dbstart to startup all databases with a 'Y' entry in this
   file.  (See page 4-17 of Oracle for Unix technical Reference Guide).

Source:http://www.fors.com/orasupp/unix/13481_1.HTM

Simple Storage for Oracle Database 10g and 11g

12:42 PM Niramit Soonthawong No comments

Oracle's launch of its Database 11g marks a major step forward in the relationship between Oracle databases and storage infrastructure. With 11g, Oracle has built Direct NFS (network file services) Client into Oracle, offering users the advantages of NAS for greater simplicity in deploying storage, easier storage management, lower cost, and superior scalability.

Attend this mediacast/podcast with Jeff Day of HP and Stan Gibson of SearchStorage.com to learn how to ease your management burdens while reducing storage expenditures. This webcast/podcast will explore how HP's Scalable NAS solution provides the following benefits for Oracle databases:

* Reduced cost and improved performance for your database
* Simple provisioning of storage for Oracle and Oracle RAC
* Superior value over traditional single-filer NAS and SAN products
* Tiered block and file storage in a single, easy-to-manage storage solution
* Integrated client-transparent NFS failover for robust high availability

Get this white paper at: payerhttp://searchoracle.bitpipe.com/data/document.do?res_id=1197907613_863

How to change your oracle user password?

10:24 AM Niramit Soonthawong No comments

There are two SQL command syntaxes that can be used to change Oracle user password:

first thing first log in to oracle database
sqlplus "/ as sysdba" or sqlplus [user_name]@[service_name]

1. Use SQL command "alter user" to change your oracle user password (for every of oracle version).

SQL>ALTER USER [user_name] IDENTIFIED BY [password];

2. Use SQL command "PASSWORD" to change your oracle user password (for Oracle8 and above)

SQL> password;
or
SQL> password [user_name]; (In case of changing other oracle user's password. You would need to have the privileges to change other Oracle user's password).

After pressing Enter, you will be prompted to input the old password and new password which would be something similar to:

SQL> password or password [user_name]
Changing password for [user_name]
Old password:
New password:
Retype new password:

EXP-00056: ORACLE error 12541 encountered

2:48 AM Niramit Soonthawong No comments

EXP-00056: ORACLE error 12541 encountered
ORA-12541: TNS:no listener
EXP-00000: Export terminated unsuccessfully

check your tnsname.ora or your server listener

Best Practices in Data Management

1:08 PM Niramit Soonthawong No comments

Financial institutions are forced to collect more data and dig deeper into their databases in order to refine their analyses due to constant performance pressures and changing regulatory demands. A holistic, unified approach to data management, one that ensures a smooth flow of information throughout the organization, enables decision makers to see a complete picture of enterprise risk.

This white paper discusses best practices for data management and various components required for successful enterprise risk management (ERM) and their benefits. Learn how your ERM solution can:

* Provide analytic and data quality capabilities.
* Produce reports for a variety of internal and external constituents.
* Group data from disparate sources to achieve a single view.
* Establish a flexible, unified data model.

Get this white paper at: http://searchoracle.bitpipe.com/data/document.do?res_id=1204657871_301

Simple Storage for Oracle Database 10g and 11g

12:51 PM Niramit Soonthawong No comments

Oracle's launch of its Database 11g marks a major step forward in the relationship between Oracle databases and storage infrastructure. With 11g, Oracle has built Direct NFS (network file services) Client into Oracle, offering users the advantages of NAS for greater simplicity in deploying storage, easier storage management, lower cost, and superior scalability.

Attend this podcast with Jeff Day of HP and Stan Gibson of SearchStorage.com to learn how to ease your management burdens while reducing storage expenditures. This podcast will explore how HP's Scalable NAS solution provides the following benefits for Oracle databases:

* Reduced cost and improved performance for your database
* Simple provisioning of storage for Oracle and Oracle RAC
* Superior value over traditional single-filer NAS and SAN products
* Tiered block and file storage in a single, easy-to-manage storage solution
* Integrated client-transparent NFS failover for robust high availability

Get this white paper at: http://searchoracle.bitpipe.com/data/document.do?res_id=1198017504_81

How to configure Windows 32 bit to let Oracle use more than 2 GB of memory

3:37 AM Niramit Soonthawong No comments

The 32-bit operating systems such as Windows 2000 and Windows Server
2003 provide access to 4-gigabyte (GB) of virtual address space. The lower 2 GB of virtual memory is private per process and available for application use. The upper 2 GB is reserved for operating system use.

All operating system editions, starting with Microsoft Windows XP Professional and later, including Windows Server 2003, include a boot.ini switch that can provide applications with access to 3 GB of virtual memory, limiting the operating system to 1 GB.

If you are running oracle on windows 32 bit OS then you are facing the 2GB limit of memory due to the information above there are 3 ways of dealing with that limitation:

Use 64-Bit windows

Apply the /3GB switch in boot.ini to allow 3GB to be made available to
oracle, with the kernel taking 1GB

Apply the /PAE switch in boot.ini to allow the use of upper memory
(anything above 4GB). Oracle needs to be configured to use this
memory, and this memory can only be used for the buffer cache. Shared
Pool, PGA, all the rest needs to sit in 'base' memory no matter what.

The /3GB and /PAE switches can be used together, but your
OS would recognize no more than 16GB of memory

The best way I suggest is finding yourself a 64-bit OS to maximize your memory usage. But if that is not the case the you can do here is how you do the modification on you boot.ini.

Configure your boot.ini

To be able to edit your window’s boot.ini you need to go to Control Panel » System » Advanced » Startup and Recovery » Settings » System startup » Edit .
The file should look something similar to the following:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(1)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(1)partition(2)\WINNT="Microsoft Windows 2003 Server" /fastdetect

As the solution above you need to apply /3GB or /PAE or both in boot.ini file after ”multi(0)disk(0)rdisk(1)partition(2)\WINNT="Microsoft Windows 2003 Server" /fastdetect” line. So after appling it your boot.ini would look like this:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(1)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(1)partition(2)\WINNT="Microsoft Windows 2003 Server" /fastdetect /3GB /PAE

reboot you windows once before configuring your oracle instance.

Real-Time Performance Boost with New Oracle Database Option

11:42 AM Niramit Soonthawong No comments

Oracle In-Memory Database Cache now offers Oracle Database customers lightning-fast transaction response times by caching frequently used data in the application tier.

Based on the Oracle TimesTen In-Memory Database, the new option delivers a real-time, dynamic, updatable cache for frequently accessed data in the Oracle Database. By caching performance-critical subsets of an Oracle Database in main memory in the application tier, the software dramatically reduces application response times and increases throughput by relying on memory-optimized algorithms and avoiding communication delays between computers and processes.

"This new option is well integrated with the Oracle Database,” says William Hardie, vice president of Database product marketing, Oracle.” It will help Oracle Database customers meet their extreme application performance needs as well as reduce development time."

For performance-critical applications in industries such as communications, financial services, and defense, the Oracle In-Memory Database Cache option delivers application response times in the microseconds by bringing the frequently accessed data closer to the application and by executing SQL requests in the TimesTen In-Memory Database.

The new option is compatible and integrated with Oracle Database Enterprise Edition, including Oracle Real Application Clusters, and also supports Oracle Enterprise Manager, Oracle SQL Developer, and components of Oracle Fusion Middleware such as Oracle Application Server, Oracle Jdeveloper, and Oracle TopLink.

“With the use of the Oracle In-Memory Database Cache, Oracle Database customers can expect to benefit from the increased performance and throughput of real-time application," says Hardie.
>>more information