How to check if your installed oracle client is 32bit or 64bit

The easy way to check the installed oracle client version in windows operating system:

The first way to check  is checking from sqlplus binary.

1. Run sqlplus in command line, and leave it prompting for user name as shown below



2. Check oracle version from windows task manager:



If the process shows "sqlplus.exe*32" as in the picture then it's the 32bit version client. If it shows "sqlplus.exe" then it's the 62bit version client.

The second way is checking from installation log:

The path of the installation lies on [ORACLE BASE]\product\11.2.0\client_1\cfgtoollogs\oui\, and the file name starts with installActions with log as an extension; for example installActions2013-07-11_03-08-26PM.log.
Here is what you have to do to check the installed oracle version:

1. Open the oracle client installation log.

2. Search for the keyword oracle_install_architecture. If you see the number as 32 then it is 32bit version client and if it is 64 then it is 64bit client. The example below shows that the installed oracle is oracle client 32bit version..





 

 

Read More

Understanding Shared Pool Memory Structures: Tips on How to Optimize Usage and Avoid Errors

The Oracle shared pool provides critical services for sharing of complex objects among large numbers of users. Prior to 10g R1, DBAs often spent much time learning about the shared pool memory management to configure and tune shared pool usage; with the tight time constraints and ever-changing workloads, many found this task daunting. The Automatic Shared Memory Management (ASMM) features introduced in 10gR1 solved this problem by providing the DBA a simple, automatic self-tuning mechanism for configuring shared memory components of the SGA, including the buffer cache and shared pool. The Automatic Database Diagnostic Monitor (also introduced in 10gR1) further simplified shared pool related tuning efforts by providing automatic diagnosis and recommendations for application specific issues.

More information : http://download.oracle.com/oowsf2005/003wp.pdf

Below are the video to help you understand more on Oracle Database Architecture.

Read More

Migrate SUSE Linux to Oracle Linux

If you are thinking of migrating your SUSE to Oracle Linux, there are things to think of and things to be prepared before you do it.

The preinstallation process and pro installation process need to be prepared. Below is the link to get information on the migration. Please read it before you do it.

http://www.oracle.com/technetwork/articles/servers-storage-admin/dell-linux-1498654.html?ssSourceSiteId=ocomen

Read More

Save your foreign key constraint before dropping table

There might be the case that you find it difficult to recreate the table with another table having foreign keys dependent on it. When you need to recreate the table that mean you need to drop that table first. Apart form losing data and other objects related to that table you will also lost foreign key constraint on every tables which referring to it. There is a little tip top help you get all those foreign key constraints back with an ease, below are a few step to do it.

1. Before dropping the table run the below script which will prompt you to input the table you are about to drop and keep the output.

select 'alter table ' || cc.table_name || ' add constraint ' || c.constraint_name || ' foreign key(' ||
cc.column_name || ') references ' || cc_r.table_name || '( ' || cc_r.column_name || ');'
from user_constraints c, user_cons_columns cc, user_cons_columns cc_r
where c.constraint_type = 'R' and
cc.constraint_name = c.constraint_name and
cc.table_name = c.table_name and
cc_r.constraint_name = c.r_constraint_name and cc_r.TABLE_NAME = '&ref_tab';

2. drop the desired table
3. recreate that table
4. run the output scripts you get from number 1

Read More

Public Synonym

If you are the owner of a table or view and you create a public synonym for the table
or view, when you grant select on the synonym mane you are in effect granting select
on the table or view. The synonym eliminates the need to clarify the table or view
with the schema.table name.

And here is how yo create public synonym

CREATE public SYNONYM [table/view name] FOR [owner].[table/view name];

and then you can grant select/insert/update/delete on this object to the role which will apply to any user who was assigned that role. Without the role assigned to that specific user he/she would still not be able to access that object event it was create as public synonym

Read More

Oracle Import Utility

Import extracts data from an export file and puts it back into an Oracle database. Import is command line not SQL command.

Table objects are imported from the export file in the following order:

1. Table definitions
2. Table data
3. Table indexes
4. Integrity constraints and triggers

Here are the list of import process order
1. New tables are created
2. Data is imported.
3. Indexes are built.
4. Triggers are imported,
5. Integrity constraints are enabled.

Note: Tablespace need to be create from the first place(if not exist), otherwise the object on the specific tablespace would not be able to be created.

To list exp parameter enter "exp HELP=y" .

More Information on Oracle Export at
- http://www.orafaq.com/wiki/Import
- http://www.orafaq.com/wiki/Import_Export_FAQ

Read More

Recompile oracle invalid object from script

Login to oracle using sqlplus command line

prompt>sqlplus login@ora_service

enter password then copy the following text after the ---------- line to sqlplus prompt

---------script start ------------------------
set heading off;
set feedback off;
set echo off;
Set lines 999;

Spool compile_ora_invalid_obj.sql

select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from dba_objects
where status = 'INVALID' and
object_type in ('PACKAGE','FUNCTION','PROCEDURE', 'TRIGGER', 'VIEW');

spool off;

set heading on;
set feedback on;
set echo on;

@compile_ora_invalid_obj.sql
--- script end-------

Note: You may need to run @compile_ora_invalid_obj.sql at sqlplus prompt again in case of some of oracle invalid objects caused by the other.

Read More

Mount CD Rom/DVD Rom for installing Oracle Database

Most of the linux distros these days automount their CDs with the noexec switch set. In other word no-one apart from root is allowed to execute programs from the mounted media.

To work around this login as root and unmount cdrom using this command

umount /dev/cdrom

then remount it with this command

mount -o loop /dev/cdrom /media/cdrom

Read More

DB block size VS File system block size

In oracle tuning there is one turning concept were discussed about, the topic was about oracle DB Clock Size, how much should it be.
As I read through I came up with the conclusion that

DB block size = File system block size

and the db block size should be 8K on windows.

How do you find what file system block size you system is using on windows.
On dos prompt enter
1. chkdsk partition (ie. c:, d:)
the result may be something similar to this

20482874 KB total disk space.
8898184 KB in 12301 files.
5652 KB in 461 indexes.
0 KB in bad sectors.
116126 KB in use by the system.
65536 KB occupied by the log file.
11462912 KB available on disk.

4096 bytes in each allocation unit.
5120718 total allocation units on disk.
2865728 allocation units available on disk.

the 4096 bytes in each allocation unit, third last row is the one telling what file system block size is.

or you could use

2. fsutil fsinfo ntfsinfo partition (ie. c:, d:)
the result may be something similar to

NTFS Volume Serial Number : 0x8aec1303ec12e8e9
Version : 3.1
Number Sectors : 0x0000000002afd736
Total Clusters : 0x000000000055fae6
Free Clusters : 0x000000000013f71b
Total Reserved : 0x0000000000000040
Bytes Per Sector : 512
Bytes Per Cluster : 4096
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x00000000102cc000
Mft Start Lcn : 0x00000000000c0000
Mft2 Start Lcn : 0x00000000002afd73
Mft Zone Start : 0x0000000000253980
Mft Zone End : 0x0000000000253a00


Bytes Per Cluster is the one telling what file system block size your system is using.

To find out what db_block_size your oracle uses use the following command.
log in as sysdba and enter

show parameter db_block_size.



Resource:

• 
  buffered_block_size.htm


• http://asktom.oracle.com


• db_block_size.html


• block-size-does-matter-just-not-in-the-way-you-think


• multiple_blocksizes_summary.htm

Read More

Oracle Licensing

Oracle database software can be very expensive or very cheap (even free), depending on what you buy/use:

* Oracle Enterprise Edition is very expensive - and it should be: It is the most powerful database management system on the planet! [Note: The neutrality of this statement is disputed!]

* Oracle Standard Edition costs about the same as Microsoft SQL Server. It can only be licensed on servers, or server clusters, that have a maximum capacity of 4 processor sockets.

* Oracle Standard Editon One is less expensive than SQL Server. It may only be licensed on servers that have a maximum capacity of 2 processor sockets.

* Oracle PE is very inexpensive, full-featured version of Oracle DB, but is restricted to one user per database (server, not instance, most likely). The license however allows a developer to use all of the features of the EE (with the exception of the Options and Management Packs).

* Oracle XE is provided free of charge.

* Oracle Enterprise Manager (OEM) Database Control and Grid control is provided free of charge, however, the management packs needs to be licensed for the number of users or CPU's that you "monitor" with it.

* Oracle Application Server (iAS) Standard Edition One may only be licensed on servers that have a maximum capacity of 2 processor cores.

A great comparison document showing what is included and what isn't for all of the editions of the Database is available at Oracle.com. See page 10 for the beginning of the comparison table.

More information on oracle licensing at:
http://www.orafaq.com/wiki/Oracle_Licensing

Read More

New Storage Strategies: How to Completely Secure Your Data‏

As the rate of information security breaches has skyrocketed, the top
priority for enterprises is the enforcement of better controls on
information security and better security management. Storage systems
provide a distinct opportunity to address multiple security
challenges consistently, through the integration of security directly
in information management systems.

Read this white paper to learn more about why storage management
systems are now the centerpiece of enterprise risk management
strategies and see why the storage and security worlds are
converging.

Download this new white paper now:
http://go.techtarget.com/r/4123328/6416364

Resource: Email

Read More

Oracle Monitoring Best Practices

All DBA's are required to set-up an Oracle monitoring infrastructure and the architecture of the monitoring system is directly dependent on the demands of management. Oracle system cost the end-user community millions of dollars, and the end-user often demand service-level agreements (SLA's) that impose strict monitoring tasks for the Oracle professional.

- In many shops, the end-user community is demanding Oracle performance statistics at a detailed level.

- A financial services company demands that 95% of their Oracle transactions complete within one wallclock second. The DBA most develop a monitoring strategy to ensure compliance. (Note: there are special techniques that can be employed to get end-to-end response time within Oracle).

- A stock brokerage demands that the DBA write a sophisticated real-time proactive monitor. This monitor will quickly diagnose and pinpoint Oracle bottlenecks, but getting the data is a problem. It requires real-time access to all internal Oracle control structures. Running the monitor imposes a measurable burden on overall system load.

- A manufacturing plant requires measuring response time by hour-of-the-day to ensure fast throughput for all shifts.
These are just a few examples of the business demands that drive the Oracle professional to create sophisticated monitoring infrastructures. Let's example some of the most important issues when making these decisions.

Read the entire article on Oracle monitoring tips here:
http://oracle-tips.c.topica.com/maal51fabI6i4cilq9pb/

Source: Email

Read More

Sharing Memory - Automatically

It used to be a challenge to size the various memory pools that comprise the Oracle System Global Area (SGA) for optimal performance. But that was before Oracle Database 10g. First introduced in Oracle Database 10g and further enhanced in Oracle Database 10g Release 2, Automatic Shared Memory Management (ASMM) automatically sizes many of the memory pools while the database is running, allocating and de-allocating memory as needed. As the workload composition changes, Oracle Database 10g enlarges the appropriate pools and reduces the sizes of other automatically sized pools accordingly. In short, ASMM can save a lot of trouble - and improve overall performance as well.

More information : http://www.oracle.com/technology/oramag/oracle/05-sep/o55tuning.html

Read More

Understanding Shared Pool Memory Structures: Tips on How to Optimize Usage and Avoid Errors

The Oracle shared pool provides critical services for sharing of complex objects among large numbers of users. Prior to 10g R1, DBAs often spent much time learning about the shared pool memory management to configure and tune shared pool usage; with the tight time constraints and ever-changing workloads, many found this task daunting. The Automatic Shared Memory Management (ASMM) features introduced in 10gR1 solved this problem by providing the DBA a simple, automatic self-tuning mechanism for configuring shared memory components of the SGA, including the buffer cache and shared pool. The Automatic Database Diagnostic Monitor (also introduced in 10gR1) further simplified shared pool related tuning efforts by providing automatic diagnosis and recommendations for application specific issues.

More information : http://download.oracle.com/oowsf2005/003wp.pdf

Read More

Tuning Oracle on Windows for Maximum Performance on PowerEdge Servers

Tuning is the art and science of modifying and reconfiguring your system in order to achieve better performance. Tuning and sizing are closely related, in that tuning hardware might require the addition of more hardware. Tuning is done on a live system that is in use or in test, whereas sizing is a theoretical exercise that is done without actually modifying the system. This paper covers some of the general Oracle tuning practices as well as specific tuning for the Windows platform.

More Information : http://www.dell.com/downloads/global/solutions/Oracle%20on%20Windows%20Tuning.pdf

Read More

Get More from Your Oracle Database: Best Practice Performance Management for Real Results

DBAs are constantly challenged to increase database performance while keeping costs down. This short paper discusses Resource Mapping Methodology (RMM) which defines a systematic process for performing Wait-Event analysis to optimize database performance. It includes a brief overview of Ignite for Oracle and the business benefits that Ignite users have demonstrated.

More information : http://www.confio.com/English/Downloads/Articles/GetMoreOracleDatabase_WP.pdf

Read More

An Oracle Technology Brief: The Oracle Database and Storage

Given the intimate relationship between the Oracle Database and the storage it resides on it is important to ensure the interface between the two is efficient and robust. This is why Oracle and the storage vendors have invested so much in the interface and interrelationship of database and storage. There is a stack of storage management software, programs, and practices available for the Oracle database. All of the components in the stack are highly tuned and optimized for use with the Oracle database. They are the most performant, scalable, reliable, easy to use, and inexpensive way of using storage with the Oracle database.

See more information http://www.oracle.com/technology/deploy/availability/pdf/1121_Weiss_Grancher_WP.pdf

Read More

Oracle in a Nutshell: Performance

Achieving optimal performance from the Oracle database is an art, not a science. Using the appropriate data structures, ensuring that there are adequate resources available, and leveraging the features of the Oracle database can help to avoid bottlenecks that reduce the performance of the system. This paper provides an overview of some of the tools within an Oracle database that will help one understand how Oracle optimizes performance for its operations.

More information: http://oreilly.com/catalog/oracleian/chapter/ch17.pdf

Read More

Self-Tuning Oracle9i Database: Oracle SGA

As DBAs become more sophisticated in their self-tuning endeavors, many Oracle metrics may become self-tuning. In Oracle Database 10g, more self-tuning capability will be seen than ever before. For example, the dynamic memory allocation features of Oracle Database 10g make it possible to create a self-tuning Oracle SGA. By means of demonstration, in this paper the author will explain how to examine the Oracle instance in Oracle 9i Database and adjust the memory regions for sort_area_size or pga_aggregate_target, large_pool_size, sga_max_size and db_cache_size according to the processing demands on the server and within the database. The techniques discussed are based on the use of Statspack to monitor memory regions over time and develop signatures of system resource usage.

More information on http://www.dba-oracle.com/art_otn_auto_tuning_10g.htm

Read More

Understanding Optimization

Improvements in the Oracle Database 10g Optimizer make it even more valuable for tuning.

In Oracle Database 10g, the cost-based optimizer (CBO) has two modes—the normal mode and a tuning mode that is invoked by the SQL Tuning Advisor (and many other Oracle Database 10g advisors, such as the SQL Access Advisor). The SQL Tuning Advisor is a great new tool for DBAs in Oracle Database 10g. Specifically, in previous releases of Oracle, if you weren't happy with the plan created by the optimizer, you could hint your code to influence the optimizer's decision, but figuring that out is time-consuming at best. And even if you had the time to do all the analysis necessary to figure out the best execution plan, you can't touch the SQL generated by packaged applications—or any other application for which you didn't have access to the source code.

More information : http://www.oracle.com/technology/oramag/oracle/05-jan/o15tech_tuning.html

Read More