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

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

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

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

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

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

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

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

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

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

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

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

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

Secrets for optimizing Oracle table inserts

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/

How to Create Oracle Database Manually

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 change your oracle user password?

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:

Ora Tip: How much did you assign your memory for oracle sga and pga?

If you want to know how much does your oracle database taking up memory for SGA and PGA just login to your oracle database with the following command on the command prompt

if you can access to the server physically or remotely
sqlplus "/ as sysdba"

otherwise use this command

sqlplus "sys@ora_service as sysdba"

SQL>show SGA
you would see something like the following lines but difference on number.

Total System Global Area 209235968 bytes
Fixed Size 1298920 bytes
Variable Size 109055512 bytes
Database Buffers 96468992 bytes
Redo Buffers 2412544 bytes

this is how your system Global Area taking your memory space every time your database is running.

to see what is your SGA size just run the following command

SQL>show parameter pga

and you would get the folloing result

pga_aggregate_target big integer 75M

this is how much your pga consume your memory.