Welcome to Charles Blog

Oracle Application server to support multiple name domains on the same IP using APACHE server.

Changes to be made on the Oracle Application server to support multiple name domains on the same IP using APACHE server.

 1) Backup httpd.conf from the apache/conf folder.

 2) Listen: Allows you to bind Apache to specific IP addresses and/or ports, in addition to the default. Search and add to file : Listen 80

3) Comment out ServerName [charles1] using # as we will be shifting to virtual hosts configuration and there would not exist one server using this parameter ServerName

The ServerName directive sets the hostname and port that the server uses to identify itself. This is used when creating redirection URLs. For example, if the name of the machine hosting the web server is simple.example.com, but the machine also has the DNS alias www.example.com and you wish the web server to be so identified.

4) Add new parameter to file NameVirtualHost 192.168.63.100:80 from #NameVirtualHost *

With the NameVirtualHost directive you specify the IP address on which the server will receive requests for the name-based virtual hosts. This will usually be the address to which your name-based virtual host names resolve. In cases where a firewall or other proxy receives the requests and forwards them on a different IP address to the server, you must specify the IP address of the physical interface on the machine which will be servicing the requests. If you have multiple name-based hosts on multiple addresses, repeat the directive for each address.

5) Move all files from D:\product\10.1.3.1\OracleAS_2\Apache\Apache\htdocs to folder domain1 as D:\product\10.1.3.1\OracleAS_2\Apache\Apache\htdocs\domain1

6) Create folders domain2 , domain3 , at the same level as domain 1 i.e D:\product\10.1.3.1\OracleAS_2\Apache\Apache\htdocs

7) Update network DNS/host files with the new domain address and IP, so as to point the domain name to the correct server IP address, the domain names will be resolved up the application server (APACHE) level to point to different website roots. Use ping to check domain name/IP binding on network.

8 ) Add virtual hosts (uncomment first)

# domain 1

ServerAdmin webmaster@dummy-host.example.com
DocumentRoot “D:\product\10.1.3.1\OracleAS_2\Apache\Apache\htdocs\domain1″
ServerName www.charles1.com
ErrorLog logs/charles1.com-error_log
CustomLog logs/charles1.com-access_log common

# domain 2

ServerAdmin webmaster@dummy-host.example.com
DocumentRoot “D:\product\10.1.3.1\OracleAS_2\Apache\Apache\htdocs\domain2″
ServerName www.charles2.com
ErrorLog logs/charles2.com-error_log
CustomLog logs/charles2.com-access_log common

# domain 3

ServerAdmin webmaster@dummy-host.example.com
DocumentRoot “D:\product\10.1.3.1\OracleAS_2\Apache\Apache\htdocs\domain3″
ServerName www.charles3.com
ErrorLog logs/charles3.com-error_log
CustomLog logs/charles3.com-access_log common

** Set different path folder for logging, will help in maintenance of each site hosted individually.

9)Test new configuration of the httpd.conf by using command line argument C:/> apache –t.

10)Restart Application server using opmnctl stopall followed by opmnctl startall

11)Check for all domains in IE

Exp/Imp: Oracle Databases

Oracle’s export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database.

These utilities can be used to move data between different machines, databases or schema. However, as they use a proprietary binary file format, they can only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database.

Various parameters are available to control what objects are exported or imported. To get a list of available parameters, run the exp or imp utilities with the help=yes parameter.

The export/import utilities are commonly used to perform the following tasks:

* Backup and recovery (small databases only, say < +50GB, if bigger, use RMAN instead)
* Move data between Oracle databases on different platforms (for example from Solaris to Windows)
* Reorganization of data/ eliminate database fragmentation (export, drop and re-import tables)
* Upgrade databases from extremely old versions of Oracle (when in-place upgrades are not supported by the Database Upgrade Assistant anymore)
* Detect database corruption. Ensure that all the data can be read
Transporting tablespaces between databases

How does one use the import/export utilities?
Look for the “imp” and “exp” executables in your $ORACLE_HOME/bin directory. One can run them interactively, using command line parameters, or using parameter files. Look at the imp/exp parameters before starting. These parameters can be listed by executing the following commands: “exp help=yes” or “imp help=yes”.

The following examples demonstrate how the imp/exp utilities can be used:

exp usename/password file=backupfilename.dmp log=logfilename.log
exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)
imp scott/tiger file=emp.dmp full=yes
imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept
Using a parameter file:

exp userid=scott/tiger@orcl parfile=export.txt
… where export.txt contains:
BUFFER=100000
FILE=account.dmp
FULL=n
OWNER=scott
GRANTS=y
COMPRESS=y

How can one improve Import/ Export performance?

EXPORT:
* Set the BUFFER parameter to a high value (e.g. 2Mb — entered as an integer “2000000″)
* Set the RECORDLENGTH parameter to a high value (e.g. 64Kb — entered as an integer “64000″)
* Use DIRECT=yes (direct mode export)
* Stop unnecessary applications to free-up resources for your job.
* If you run multiple export sessions, ensure they write to different physical disks.
* DO NOT export to an NFS mounted filesystem. It will take forever.

IMPORT:
* Create an indexfile so that you can create indexes AFTER you have imported data. * Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
* Place the file to be imported on a separate physical disk from the oracle data files
* Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
* Set the LOG_BUFFER to a big value and restart oracle.
* Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
* Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
* Use COMMIT=N in the import parameter file if you can afford it
* Use STATISTICS=NONE in the import parameter file to avoid time consuming to import the statistics
* Remember to run the indexfile previously created

What are the common Import/ Export problems?
ORA-00001: Unique constraint (…) violated
You are importing duplicate rows. Use IGNORE=YES to skip tables that already exist (imp will give an error if the object is re-created).

ORA-01555: Snapshot too old
Ask your users to STOP working while you are exporting or try using parameter CONSISTENT=NO

ORA-01562: Failed to extend rollback segment
Create bigger rollback segments or set parameter COMMIT=Y while importing

IMP-00015: Statement failed … object already exists…
Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.

As DBA’s we need to understand that AN EXPORT IS NOT A BACKUP!!! get it? never has and never will be, it is a tool for moving data between databases, if you use export, you will lose data – any solution that means you lose data is by definition not a proper backup strategy!!!!

Oracle Apex upgrade from 3.0 to 3.2 in 11g

Here is what I did on 11g

Preliminary Steps

1. backup database consistently , specifically backup schemas FLOWS_030000,FLOWS_FILES,APEX_PUBLIC_USER.

using exp/expd in this case expd (How to configure expd click here)

C :\> expdp backup/***@ORCL directory=backup dumpfile=apex_bkp.dmp schemas=(‘FLOWS_030000’,’FLOWS_FILES’,’APEX_PUBLIC_USER’)

Step 1: Run the install script.

Note: You need to have downloaded Oracle APEX 3.2 already, to download go to ORACLE APEX

1. Copy the ‘apex_3.2’ directory to the ORACLE_BASE directory on the database server. (:\oracle\product\11.1.0). When completed the following two directories should exist in the ORACLE_BASE directory.

apex – The original version 3.0 directory.
apex_32 – The new directory 3.2.

2. At the database server console change the working directory to the ‘ORACLE_BASE\apex_3.2\apex’ directory. (:\oracle\product\11.1.0\apex_3.2\apex)

3. Start SQL*Plus and connect to the database where APEX is to be installed. Connect as SYS and specify the SYSDBA role.

C:\set ORACLE_SID =
C:\sqlplus / as sysdba
SQL>

4. Select the appropriate installation option :

Full development environment:
@apexins tablespace_apex tablespace_files tablespace_temp images

tablespace_apex is the name of the tablespace for the Oracle Application Express user.
tablespace_files is the name of the tablespace for the Oracle Application Express files user.
tablespace_temp is the name of the temporary tablespace
images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as “/i/”.

Enter the following to begin the installation: @apexins APEX APEX_FILES TEMP /i/

When Oracle Application Express v3.2 is installed it creates one new database account (schema):APEX_030200: The account that owns the Oracle Application Express schema and metadata.

It will take some time to upgrade and during that all you need to do is to sit back and watch (better start reading something). After it is completed it will create a new user as above and will update APEX metadata to 3.2. There a will be crystal look in interface of APEX which will distinguish it from previous release.This can be confirmed for the table dba_registry.

Step 2: Update the Images Directory When Upgrading from a Previous Release

If you are upgrading Oracle Application Express from a previous release, you must run the ‘apxldimg.sql’ script to update the images directory.

Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

Run ‘apxldimg.sql’ passing the file system path to the base directory where the Oracle Application Express software was unzipped as shown in the following example:

@apxldimg.sql D:\oracle\product\11.1.0\apex_3.2\apex

Step 3: Configure Oracle HTTP Server Distributed with Oracle Database 11g or Oracle Application Server 10g

Copy the Images directory

Note: Whether a new installation or upgrade is being performed, the ‘images’ directory from the top level of the ‘ORACLE_BASE\apex\images’ directory, needs to be copied to the location containing the Oracle home for Oracle HTTP Server. This directory contains all of the APEX necessary images, templates, cascading style sheets, java scripts, etc.

1. Copy the directory ‘apex_3.2\apex\images’ to the appropriate location:

xcopy /E /I ORACLE_BASE\apex_3.2\apex\images ORACLE_HTTPSERVER_HOME\Apache\Apache\images

Step 4: Log In to Oracle Application Express

To access APEX as the Application Express administrator use one of the following URLs:

http://charleshost/apex

Workspace: INTERNAL
Username: ADMIN
Password: ********

http://charleshost/apex/apex_admin

Username: ADMIN
Password: ********

Step 5: Drop Previous Version Schema

Backup the database.
Drop the ‘FLOWS_030100’ schema. ( DROP user FLOWS_030100 CASCASE; )

Do not panic, Remember that your old applications, workspaces and users etc will still remain intact after upgrade.So ask users to log in as before

Processing an SQL Statement

In the following we sketch how an SQL statement is processed by the Oracle server and which
processes and buffers are involved.

1. Assume a user (working with SQL*Plus) issues an update statement on the table TAB such
that more than one tuple is affected by the update. The statement is passed to the server
by the USER process. Then the server (or rather the query processor) checks whether
this statement is already contained in the library cache such that the corresponding
information (parse tree, execution plan) can be used. If the statement can not be found,
it is parsed and after verifying the statement (user privileges, affected tables and columns)
using data from the dictionary cache, a query execution plan is generated by the query
optimizer. Together with the parse tree, this plan is stored in the library cache.

2. For the objects affected by the statement (here the table TAB) it is checked, whether the
corresponding data blocks already exist in the database buffer. If not, the USER process
reads the data blocks into the database buffer. If there is not enough space in the buffer,
the least recently used blocks of other objects are written back to the disk by the DBWR
process.

3. The modifications of the tuples affected by the update occurs in the database buffer.
Before the data blocks are modified, the “before image” of the tuples is written to the
rollback segments by the DBWR process.

4. While the redo-log buffer is filled during the data block modifications, the LGWR process
writes entries from the redo-log buffer to the redo-log files.

5. After all tuples (or rather the corresponding data blocks) have been modified in the
database buffer, the modifications can be committed by the user using the commit
command.

6. As long as no commit has been issued by the user, modifications can be undone using
the rollback statement. In this case, the modified data blocks in the database buffer are
overwritten by the original blocks stored in the rollback segments.

7. If the user issues a commit, the space allocated for the blocks in the rollback segments is
deallocated and can be used by other transactions. Furthermore, the modified blocks in
the database buffer are unlocked such that other users now can read the modified blocks.
The end of the transaction (more precisely the commit) is recorded in the redo-log files.
The modified blocks are only written to the disk by the DBWR process if the space
allocated for the blocks is needed for other blocks.

Oracle Tuning Responsibilities

I will now will just list the major tuning steps and discuss in general.
The major thing to remember is that running is recursive, changes
to one area may affect others, an example would be the correction
of I/O waits resulting in a CPU bottleneck. Before beginning a
tuning effort always set goals, what do you want to accomplish?
Otherwise you won’t know when you are successful.

Step 1: Tune the Business Rules

In this step the goals should be to create a set of business rules
that truly model the business needs for the database. The business
rules should specify requirements not give solutions or methods.

Step 2: Tune the Data Design

In this step the goals should be to provide a data design that
optimizes the access of data. Usually the data structure will be
normalized according to the rules of normalization to at least a third
normal form design. Once a normal design is reached, specific
tables will be denormalized to optimize performance. At this step
indexing is laid out to optimize join paths.

Step 3: Tune the Application Design

In this step the DBA must have input to the developers and provide
them with tools and guidance in the proper methods to optimize the
SQL and PL/SQL used to access data. The goal of this step is to
provide a properly tuned and optimized application that utilizes the
data design to provide maximum performance.

Step 4: Tune the Logical Structure of the Database

This step may require changes to the data design and additional
creation of indexes. In this step the application is analyzed to set
such parameters as INITRANS and FREELISTS. Proper setup of
sequences is also accomplished in this step.

Step 5: Tune Database Operations

System designers and application developers must understand
Oracle’s query processing mechanism to write effective SQL
statements.
Before tuning the Oracle Server itself, be certain that your
application is taking full advantage of the SQL language and the
Oracle features designed to speed application processing. Use
features and techniques such as the following based on the needs
of your application:
* Array processing
* The Oracle optimizer
* The row-level lock manager
* PL/SQL

Step 6: Tune the Access Paths

Ensure that there is efficient access to data. Consider the use of
clusters, hash clusters, B*-tree indexes and bitmap indexes as well
as new features of Oracle8 such as index only tables, partitioned
views and indexes and using new object oriented features.
Ensuring efficient access may mean adding indexes, or adding
indexes for a particular application section (such as for specialized
reporting) and then dropping them again. It may mean revisiting
your design after you have built the database. You may want to do
more denormalization or create alternative indexes at this point.
Upon testing the application you may find that you’re still not
obtaining the required response time. Look for more ways to
improve the design.

Step 7: Tune Memory Allocation

Appropriate allocation of memory resources to Oracle memory
structures can have a large impact on performance.
Oracle shared memory is allocated dynamically to the following
structures, which are all part of the shared pool. Although you
explicitly set the total amount of memory available in the shared
pool, the system dynamically sets the size of each structure
contained within it:
* The data dictionary cache
* The library cache
* Context areas (if running a multithreaded server)

You can explicitly set memory allocation for the following structures:
* Buffer cache
* Log buffer
* Sequence caches
Proper allocation of memory resources can improve cache
performance, reduce parsing of SQL statements, and reduce
paging and swapping.

Process local areas include:
* Context areas (for systems not running a multithreaded server)
* Sort areas
* Hash areas

Be careful not to allocate to the system global area (SGA) to such a
large percentage of the machine’s physical memory that it causes
paging or swapping.

Step 8: Tune I/O and Physical Structure

Disk I/O tends to reduce the performance of many software
applications. Oracle Server, however, is designed so that its
performance need not be unduly limited by I/O. Tuning I/O and
physical structure involves these procedures:

1. Distributing data so that I/O is distributed, thus avoiding disk
contention
2. Storing data in data blocks for best access: setting the right
number of free lists, and proper values for PCTFREE and
PCTUSED
3. Creating extents large enough for your data so as to avoid
dynamic extension of tables, which would hurt high-volume
OLTP applications
4. Evaluating the use of raw devices

Step 9: Tune Resource Contention

Concurrent processing by multiple Oracle users may create
contention for Oracle resources. Contention may cause processes
to wait until resources are available. Take care to reduce the
following kinds of contention:
* Block contention
* Shared pool contention
* Lock contention
* Pinging (in a parallel server environment)
* Latch contention

Step 10: Tune the Underlying Platform(s)

See your platform-specific Oracle documentation to investigate
ways of tuning the underlying system. For example, on UNIX-based
systems you might want to tune the following:
* Size of the UNIX buffer cache
* UNIX memory segments
* Logical volume managers
* Memory and size for each process

Oracle Tuning Summary

Remember that without clear tuning goals you will never know if
you have been successful. Tuning is a recursive process and is
never finished.

Backup principles in Oracle Databases

It consists of

* Physical and Logical backups
* Whole database and partial database backups
* Online and offline backups
* RMAN and User-Managed backups
* Physical and Logical Backups:

Physical backups:

In  ‘physical backups’ are backups of physical database files: data files and control files. If you run the database in ARCHIVELOG mode, then the dataase also generates archived redo logs. You can back up the data files, control files, and archived redo logs.

Physical backups are divided into two categories: image copies and backups in a proprietary format. An image copy is  an exact duplicate of a data file,control file, or archived log. You can create image copies of physical files with operating system utilities or the RMAN COPY command, and you can restore them as-is without performing additional processing by using either operating system utilities or the RMAN RESTORE command.

Note: Unlike operating system copies, the RMAN copy command validates the blocks in the file and records the copy in the repository.

The RMAN BACKUP command generates a backup set, which is logical object containing one or more backup pieces. Each backup piece is a physical file in a proprietary, binary format. You must use RMAN to restore this backup set.

Logical Backups:

In contrast to physical,  ‘logical backups’ are exports of schema objects into a binary file. Import and Export are utilities used to move oracle data in and out of oracle schema. Export writes data from and oracle database to binary operating system files. These export files store information about schema objects, for example, tables and stored procedures. Import is a utility that reads export files and restores the corresponding data into an existing database.

Although import and export are designed for moving oracle data, you can also use them as a supplement method of protecting data in an oracle database. You should not use import and export as the sole method of backing up your data.

• WHOLE DATABASE AND PARTIAL DATABASE BACKUPS:

* Whole database backups
* Table space backups
* Data file backups
* Control file backups

Whole Database Backups:

A ‘Whole Database Backup’ includes backups of the control file along with all data files. Whole database backups are the most common type of backup.

Whole database backups do not require you to operate the database in a specific archiving mode. Before performing whole database backups, however, be aware of the implications of backing up in ARCHIVELOG and NOARCHIVELOG modes.

A whole database backup is either a ‘consistent backup’ or an ‘inconsistent backup.Whether or not a backup is consistent determines whether you need to apply redo logs after restoring the backup.You can make backups of the entire database with the following methods:

An operating system utility that makes a separate copy of each individual data file in the database as well as the current control file.

* The RMAN BACKUP DATABASE command
* The RMAN COPY DATAFILE command run against each data file in the database, and the COPY CURRENT CONTROLFILE command run against the control file.

Tablespace Backups:

A tablespace backup is a backup of the data files that constitute the tablespace. For example, if tablespace users contains data files 2,3, and 4, then a backup of tablespace users backs up these three data files.

Tablespace backups, whether online or offline, are valid only I f the database is operating in ARCHIVELOG mode. The reason is that redo is required to make  the restored tablespace consistent with the other tablespaces in the database.

The only time a tablespace backup is valid for a database in NOARCHIVELOG mode is when the tablespace is currently read-only or offline-normal. These cases are exceptions because no redo is required to recover them.

For example, take the scenario depicted

1. You take a tablespace offline normal at some time during day t
2. You make a backup of the tablespace at day t+5.
3. You restore the tablespace at day t+10 with the backup made at day t+5.
4. You make the tablespace read/write at day t+15.

Because there were no changes to the offline tablespaces between t+5 and t+10, no media recovery is needed. If you make the tablespace read/write at t+15 and then subsequently attempt to restore the t+5 backup, however, oracle requires media recovery for the changes after t+15. Hence, you can only open the database if all necessary redo is located in the online redo logs.

You can make backups of an individual tablespace with the following methods:

An operating system utility that makes a separate copy of each data file in the tablespace

The RMAN BACKUP TABLESPACE command syntax

Data file backups:

A data file backup is a backup of a single data file. Data file backups, which are not as common as table_ space backups, are valid in ARCHIVELOG databases.

The only time a data file backup is valid for a database in NOARCHIVELOG mode is if:

* Every data file in a tablespace is backed up. You cannot restore the database unless all data files are backed up.

* The datafiles are read-only or offline-normal.

You can make backups of an individual data-file using these methods:

* An operating system utility
* The RMAN BACKUP DATAFILE command
* The RMAN COPY DATAFILE command which produces a ‘data-file copy’.

Control file backups:

Backing up the control file is crucial aspect of backup and recovery.Without an accessible control file, you cannot mount or open the database.

If you use RMAN as your backup and recovery solution, and if you run the CONFIGURE CONTROLFILE AUTOBACKUP command, then RMAN automatically backs up the control file whenever you run backup and copy jobs. This backup is called a ‘control file autobackup’. Because the autobackup uses a default file-name. RMAN can restore this backup even if the RMAN repository is unavailable. Hence this feature is extremely useful in a disaster recovery scenario.

You can make mutual backups of the control file by using the following methods:

The RMAN BACKUP CURRENT CONTROLFILE creates an RMAN-specific backup of the control file, and the COPY CURRENT CONTROLFILE command creates an image copy of the control file.

The SQL statement ALTER DATABASE BACKUP CONTROLFILE makes a binary backup of the control file.

The SQL statement ALTER DATABASE BACKUP CONTROLFILE TO TRACE exports the control file contents to a SQL script file. You can use the script to create a new control file.

Trace file backups have one major disadvantage:

1) they contain no records of archived redo logs, offline ranges for data files, and RMAN backups and copies. For this reason, binary backups are preferable.

Archived Redo log backups:

Archived redo logs are essential for recovering an inconsistent backup. The only way to recover an inconsistent backup without archived logs is to use RMAN incremental backups. To be able to recover a backup through the most recent log, every log generated between these two points must be available. In other words, you cannot recover from log 100 to log 200 if log 173 is missing. If log 173 is missing, then you must halt recovery at log 172 and open the database with the RESETLOGS option. Because archived redo logs are essential to recovery, you should back them up regularly. If you use a media manager, then back them up regularly to tape.

You can make backups of archived logs by using the following methods:

* An operating system utility

* The RMAN BACKUP ARCHIVELOG command

* The RMAN BACKUP….PLUS ARCHIVELOG command

* The RMAN COPY ARCHIVELOG command

• CONSISTENT AND INCONSISTENT BACKUPS

A consistent backup of a database or part of a database is a backup in which all read/write data-files and control files are check-pointed with respect to the same ‘system change number (SCN). Oracle determines whether a restored backup is consistent by checking the data-file headers against the data-file header information contained in the control-file.

The only way to make a consistent whole database backup is to shut down the database with the NORMAL, IMMEDIATE, or TRANSACTIONAL options and make the backup while the database is closed. If a database is not shutdown cleanly, for example, and instance fails or you issue a SHUTDOWN ABORT statement, then the databases’s data-files are always inconsistent—unless the database is a ‘read-only database’ . Instance recovery will be required at open time.

Oracle makes the control files and data-files consistent to the same SCN during a database ‘checkpoint’. The only table-spaces in a consistent backup that are allowed to have older SCNs are read-only and offline normal table-spaces, which are still consistent with the other data-files in the backup because no changes have been made to them.

The important point is that you can open the database after restoring a consistent whole database backup ‘without applying redo’ because the data is already consistent: no action is required to make the data in the restored data-files correct. Hence, you can restore a year-old consistent backup of your database without performing media recovery and without oracle performing instance recovery.

A consistent whole database backup is the only valid backup option for databases operating in NOARCHIVELOG mode, because otherwise redo will need to be applied to create consistency. In NOARCHIVELOG mode, oracle does not archive the redo logs, and so the required redo logs may not exist on disk.

Inconsistent backup:

An inconsistent backup is a backup in which all read/write data-files and control-files have not been check-pointed with respect to the same SCN. For example, one read/write data-file header may contain an SCN of 100 while other read/write data-file headers contain an SCN of 95 or 90. Oracle cannot openthe database until all of these header SCNs are consistent, that is, until all changes recorded in the online redo logs have been applied to the data-files on disk.

If the database must be up and running 24 hours a day, 7 days a week, then you have no choice but to perform inconsistent backups of a whole database. For example, a backup of an offline table-space in an open database is inconsistent with other table-spaces because portions of the database are being modified and written to disk while the backup of the table-space is progressing. The data-file headers for the online and offline data-files may contain inconsistent SCNs. You must run your database in ARCHIVELOG mode to make online backups of online data-files.

If you run the database in ARCHIVELOG mode, then you can construct a whole database backup using backups of online data-files-taken at different times. For example, if your database contains seven table-spaces, and if you backup the control-files as well as different table-space each night, then in a week you will backup all tables-spaces in the database as well as the control-file. You can consider this staggered backup as a whole database backup.

Inconsistent Closed Backups:

You have the option of making inconsistent closed backups if a database is backed up after a system crash or SHUTDOWN ABORT. This type of backup is valid if the database is running in ARCHIVELOG mode, because both online and archived redo logs are available to make the backup consistent.

Caution: Oracle strongly recommends that you do not make inconsistent, closed database backups in NOARCHIVELOG mode.

If you run the database in NOARCHIVELOG mode, then only back it up when you have closed it cleanly with the IMMEDIATE, NORMAL, or TRANSACTIONAL options. Inconsistent whole database backups of databases running in NOARCHIVELOG mode are usable only if the redo logs containing the changes made prior to the backup are available when you restore it-an unlikely occurrence.

The reason that NOARCHIVELOG inconsistent backups are not recommended is that the data-file headers of the backed up files contain different SCNs (a normal shutdown guarantees the consistency of these SCNs), and because the database is in NOARCHIVELOG mode, no archived redo logs are available to apply the lost changes. For this reason, RMAN does not allow you to back up a database that has been running in NOARCHIVELOG mode and shutdown abnormally because the backup is not usable for recovery.

The basic guideline is: if you run your database in NOARCHIVELOG mode, always have a backup that is usable without performing any recovery. This aim is defeated if you need to apply redo from logs to recover a backup.

Archiving un-archived redo log files:

After an ‘online backup’ or inconsistent ‘closed backup’, always ensure that you have the redo necessary to recover the backup by archiving the un-archived redo logs. When the database is open, run the following SQL statement to force oracle to switch out of the current log and archive it as well as all other un-archived logs:

ALTER SYSTEM ARCHIVE LOG CURRENT;

When the database is mounted, open, or closed, you can run the following SQL statement to force oracle to archive all non-current redo logs.

ALTER SYSTEM ARCHIVE LOG ALL;

When the database is mounted, open, or closed, you can run the following SQL statement to archive a specific group, where ‘integer’ is the number of the group:

ALTER SYSTEM ARCHIVE LOG GROUP integer;

Backing up the Archived Logs and the control file:

After open or inconsistent closed backups, oracle recommends backing up all archived logs produced during the backup, and then backing up the control file after the backup, then you cannot recover the backup because you do not have all the redo records necessary to make it consistent. Having a control-file backup generated after the completion of the database backup is helpful when using RMAN because the control file contains a record of the backup.

Creating and Running PL/SQL

As is true for SQL, PL/SQL programs can be invoked from a variety of other languages. Unlike SQL, PL/SQL can actually run in two different places:

* Inside the Oracle database server, as stored code. If you call PL/SQL from SQL*Plus, Java, or any other language, this is typically where it’s going to run.
* In one of Oracle’s application environments such as Oracle Forms or Oracle Reports, as a program that executes on a client computer. Often, the runtime environment lives on a middle tier, operated across the network from a browser-only or “thin” client.

SQL*Plus

The granddaddy of Oracle front ends, Oracle’s SQL*Plus provides a command-line interpreter for both SQL and PL/SQL. That is, it accepts statements from the user, sends them off to the Oracle server, and displays the results.

Often maligned for its primitive user interface, SQL*Plus is one of my favorite Oracle tools. I actually like the lack of fancy gizmos and complicated menus. Ironically, when I started using Oracle (circa 1986), this product’s predecessor was boldly named UFIUser Friendly Interface. Two decades later, even the latest version of SQL*Plus is still unlikely to win any user friendliness awards, but at least it doesn’t crash very often.

Today, Oracle supports three different styles of executing SQL*Plus:

As a console program

This is a program that runs from a shell or command prompt[*] (an environment that is sometimes called a console).[*] Oracle calls this the “command-line interface” version of SQL*Plus, but I find that somewhat confusing, because two of the three styles provide a command-line interface.

As a pseudo-GUI program

This form of SQL*Plus is available only on Microsoft Windows. I call it a “pseudo-GUI” because it looks pretty much like the console program but with bitmapped fonts; few other features distinguish it from the console program. Beware: Oracle has been threatening to desupport this product for years, and it hasn’t really been updated since Oracle8i Database.

Via iSQL*Plus (in Oracle9i Database or later)

This program executes from a web browser connected to a middle-tier machine running Oracle’s HTTP server and iSQL*Plus server.

Performing Essential PL/SQL Tasks
Let’s turn to the highlights of creating , running, deleting, and otherwise managing PL/SQL programs, using SQL*Plus as the front end. Don’t expect to be overwhelmed with detail here; treat this section as a glimpse of topics that will be covered in much greater detail in the chapters ahead.
Creating a Stored Program
To build a new stored PL/SQL program, you use one of SQL’s CREATE statements. For example, if you want to create a stored function that counts words in a string, you can do so using a CREATE FUNCTION statement :
CREATE FUNCTION wordcount (str IN VARCHAR2)
RETURN PLS_INTEGER
AS
declare local variables here
BEGIN
implement algorithm here
END;
/
As with the simple BEGIN-END blocks shown earlier, running this statement from SQL*Plus requires a trailing slash on a line by itself.
Assuming that the Oracle DBA has granted you Oracle’s CREATE PROCEDURE privilege (which also gives you the privilege of creating functions), this statement causes Oracle to compile and store this stored function in your schema; if your code compiles, you’ll probably see a success message such as:
Function created.
If a table or stored program named wordcount already exists in your Oracle schema, CREATE FUNCTION will fail with the error message ORA-00955: name is already used by an existing object. That is one reason that Oracle provides the OR REPLACE option, which you will want to use probably 99% of the time.
CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)
RETURN PLS_INTEGER
AS
same as before
The OR REPLACE option avoids the side effects of dropping and recreating the program; in other words, it preserves any object privileges you have granted to other users or roles. Fortunately, it replaces only objects of the same type, and it won’t automatically drop a table named wordcount just because you decided to create a function by that name.
As with anonymous blocks used more than once, programmers generally store these statements in files in the operating system. I could create a file wordcount.fun for this function and use the SQL*Plus @ command to run it:
SQL> @wordcount.fun

Function created.

As mentioned earlier, SQL*Plus does not, by default, echo the contents of scripts. You can SET ECHO ON to see the source code scroll past the screen, including the line numbers that Oracle assigns; this setting can be helpful when troubleshooting. Let’s introduce an error into the program by commenting out a variable declaration.
SQL> /* File on web by Chares Menezes: wordcount.fun */
SQL> SET ECHO ON
SQL> @wordcount.fun
SQL> CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)
2     RETURN PLS_INTEGER
3  AS
4  /* words PLS_INTEGER := 0;  ***Commented out for intentional error*** */
5     len PLS_INTEGER := NVL(LENGTH(str),0);
6     inside_a_word BOOLEAN;
7  BEGIN
8     FOR i IN 1..len + 1
9     LOOP
10        IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len
11        THEN
12           IF inside_a_word
13           THEN
14              words := words + 1;
15              inside_a_word := FALSE;
16           END IF;
17        ELSE
18           inside_a_word := TRUE;
19        END IF;
20     END LOOP;
21     RETURN words;
22  END;
23  /

Warning: Function created with compilation errors.

SQL>
This message tells us that the function was created, but that there were compilation errors that render it inoperable. We’ve succeeded in storing the source code in the database; now we need to tease the details of the error out of the database. The quickest way to see the full text of the error message is to use SQL*Plus’ SHOW ERRORS command, abbreviated as SHOW ERR:
SQL> SHOW ERR
Errors for FUNCTION WORDCOUNT:

LINE/COL ERROR
——– ———————————————-
14/13    PLS-00201: identifier ‘WORDS’ must be declared
14/13    PL/SQL: Statement ignored
21/4     PL/SQL: Statement ignored
21/11    PLS-00201: identifier ‘WORDS’ must be declared

The compiler has detected both occurrences of the variable, reporting the exact line and column numbers. To see more detail about any server-based error, you can look it up by its identifierPLS-00201 in this casein Oracle’s Database Error Messages document.
Behind the scenes, SHOW ERRORS is really just querying Oracle’s USER_ERRORS view in the data dictionary. You can query that view yourself, but you generally don’t need to

Show Other Errors
Many Oracle programmers know only one form of the SQL*Plus command:
SQL> SHOW ERRORS
and they incorrectly believe that they must query the USER_ERRORS view directly to see anything but the error messages from the most recent compile. However, you can append to SHOW ERRORS an object category and a name, and it will display the latest errors for any object:
SQL> SHOW ERRORS category [schema. ]object
For example, to view the latest errors for the wordcount function, specify:
SQL> SHOW ERRORS FUNCTION wordcount
Use caution when interpreting the output:
No errors.
This message actually means one of three things: (1) the object did compile successfully; (2) you gave it the wrong category (for example, function instead of procedure); or (3) no object by that name exists.
The complete list of categories this command recognizes varies by version, but includes the following:
DIMENSION
FUNCTION
JAVA SOURCE
JAVA CLASS
PACKAGE
PACKAGE BODY
PROCEDURE
TRIGGER
TYPE
TYPE BODY
VIEW

It’s common practice to append a SHOW ERRORS command after every scripted CREATE statement that builds a stored PL/SQL program. So, a “good practices” template for building stored programs in SQL*Plus might begin with this form:
CREATE OR REPLACE program-type
AS
your code
END;
/

SHOW ERRORS
When your program contains an error that the compiler can detect, CREATE will still cause Oracle to store the program in the database, though in an invalid state. If, however, you mistype part of the CREATE syntax, Oracle won’t be able to figure out what you are trying to do and won’t store the code in the database.
Executing a Stored Program
For example, you can invoke a function such as wordcount in any location where you could use an integer expression. Here is a short illustration of how I might test the wordcount function with a strange input (CHR(9) is an ASCII “tab” character 
BEGIN
DBMS_OUTPUT.PUT_LINE(‘There are ‘ || wordcount(CHR(9)) || ‘ words in a tab’);
END;
/
I have embedded wordcount as part of an expression and supplied it as an argument to DBMS_OUTPUT.PUT_LINE. Here, PL/SQL automatically casts the integer to a string so it can concatenate it with two other literal expressions; the result is:
There are 0 words in a tab
Showing Stored Programs
Sooner or later you will want to get a list of the stored programs you own, and you may also need to view the most recent version of program source that Oracle has saved in its data dictionary. This is one task that you will find far easier if you use some kind of GUI-based navigation assistant (SqlDeveloper) but if you lack such a tool, it’s not too hard to write a few SQL statements that will pull the desired information out of the data dictionary.
For example, to see a complete list of your programs (and tables, indexes, etc.), query the USER_OBJECTS view, as in:
SELECT * FROM USER_OBJECTS;
This view shows name, type, creation time, latest compile times, status (valid or invalid), and other useful information.
If all you need is the summary of a PL/SQL program’s callable interface in SQL*Plus, the easiest command to use is DESCRIBE:
SQL> DESCRIBE wordcount
FUNCTION wordcount RETURNS BINARY_INTEGER
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
STR                            VARCHAR2                IN
DESCRIBE also works on tables, views, object types, procedures, and packages. To see the complete source code of your stored programs, query USER_SOURCE or TRIGGER_SOURCE.

Dropping a Stored Program
If you really, truly don’t need a particular stored program any more, you can drop it using SQL’s DROP statement :
DROP FUNCTION wordcount;
You can drop a package, which can be composed of up two elements (a specification and body), in its entirety:
DROP PACKAGE pkgname;
Or you can drop only the body without invalidating the corresponding specification:
DROP PACKAGE BODY pkgname;
Any time you drop a program that other programs call, the callers will be marked invalid.

Bigfile Tablespaces in Oracle

Starting with Oracle 10g, tablespaces can be created with the BIGFILE option, which specifies that the tablespace will consist of only a single datafile, which can be up to 8 exabytes (8 million terabytes) in size. Thus using a single large file can drastically simplify storage management in large databases.

But , Obviously, these extremely large files are only suitable for use on disk arrays. Generally, bigfile tablespaces must be locally managed and must use ASSM.

Note: Both undo tablespaces and temporary tablespaces can be created as bigfile tablespaces even though they can’t be configured with ASSM.

All normal tablespace CREATE and ALTER operations work with bigfile tablespaces, with the obvious exception of adding a datafile. Since they must consist only of a single datafile, attempting to add another file to a bigfile tablespace will result in an error. When specifying file or file extension sizes for bigfiles, you can specify sizes in gigabytes or terabytes by using the “G” and “T” abbreviations, respectively. For example:

CREATE BIGFILE TABLESPACE big_tbs
DATAFILE SIZE 2T AUTOEXTEND ON NEXT 500G MAXSIZE 200T
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50G
SEGMENT SPACE MANAGEMENT AUTO;

At database create time, you can indicate that the database should default to creating bigfile tablespaces rather than normal (smallfile) ones. To do this, include the SET DEFAULT BIGFILE TABLESPACE clause in the CREATE DATABASE statement as follows:

CREATE DATABASE bigdb
SET DEFAULT BIGFILE TABLESPACE
UNDO TABLESPACE undotbs
DEFAULT TEMPORARY TABLESPACE temp01;

If you set bigfile tablespaces as the default at database creation time, you can still create smallfile tablespaces by simply using the CREATE SMALLFILE TABLESPACE command.

You can also, at database create time, designate the undo and/or temporary tablespaces as bigfile tablespaces by adding the BIGFILE qualifier to the appropriate phrase of the CREATE DATABASE command; for instance:

CREATE DATABASE bigdb2
BIGFILE UNDO TABLESPACE undotbs DATAFILE SIZE 5G
BIGFILE DEFAULT TEMPORARY TABLESPACE temp01 TEMPFILE SIZE 500M;

IMPDP / EXPDP Data pumps in Oracle 11g

Oracle Data Pump is a new and unique feature of Oracle Database 11g. A new public interface package, DBMS_DATAPUMP, provides a server-side infrastructure for fast data and metadata movement between Oracle databases. It is ideal for large databases and data warehousing environments, where high-performance data movement offers significant time savings to database administrators.

Data Pump automatically manages multiple, parallel streams of unload and load for maximum throughput. The degree of parallelism can be adjusted on-the-fly. There are new and easy-to-use Export and Import utilities (expdp and impdp), as well as a web-based Enterprise Manager export/import interface. Because of this, dump files, log files, and sql files are accessed relative to the server-based directory paths. Data Pump requires that directory objects mapped a file system directory be specified in the invocation of the data pump import or export.

Configuration  (Windows/Unix)

No much work here, just execute the following 2 statement and you are done, what are we doing here is just creating a directory object for the data dumps to be dumped to since this is a server side application we definitely required a server location to put the dumps, also the user doing the dumps must have the DBA role or EXP_FULL_DATABASE Role . (During my  implememtation i did not have to grant role EXP_FULL_DATABASE the DBA role seem to be sufficient and working )

–
– Create a directory object name for implementing EXPDP/IMPDP
–
Create directory expdp_dir as  ‘C:\app\Oracle\backupdp’;

Create directory expdp_dir as  ‘ /u01/backup/exports’ ;      (Unix )

Grant read,write on directory expdp_dir to system,charles,user1,user2;

Using EXPDP :

Actually doing a EXPDP dump look below..

–Database Export Mode  (Example)
expdp system/manager DIRECTORY=expdp_dir DUMPFILE=expfull_DB.dmp FULL=y LOGFILE=expfull_DB.log

–Schema Export Mode (Example)
expdp charles/charles DIRECTORY=expdp_dir DUMPFILE=charles_exp.dmp SCHEMAS=scott,scott_history LOGFILE=charles_imp.log

C:\>expdp charles/charles DIRECTORY=expdp_dir DUMPFILE=charles_exp.dmp SCHEMAS=c
harles LOGFILE=charles_imp.log

;;;
Export: Release 11.1.0.6.0 – Production on Thursday, 02 July, 2009 18:19:59

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting “CHARLES”.”SYS_EXPORT_SCHEMA_01″:  charles/******** DIRECTORY=expdp_dir DUMPFILE=charles_exp.dmp SCHEMAS=charles LOGFILE=charles_imp.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported “CHARLES”.”DNN_DYNAMICFORMS_WIZARD”         7.937 KB       1 rows
. . exported “CHARLES”.”DNN_DYNAMICFORMS_BLACKLIST”          0 KB       0 rows
. . exported “CHARLES”.”DNN_DYNAMICFORMS_DYNEVTPAYS”         0 KB       0 rows
. . exported “CHARLES”.”DNN_DYNAMICFORMS_DYNQSTEVENT”        0 KB       0 rows
. . exported “CHARLES”.”DNN_DYNAMICFORMS_EVENTS”             0 KB       0 rows
. . exported “CHARLES”.”DNN_DYNAMICFORMS_FLOCALTION”         0 KB       0 rows
. . exported “CHARLES”.”DNN_DYNAMICFORMS_QLOCALTION”         0 KB       0 rows
. . exported “CHARLES”.”DNN_DYNAMICFORMS_QUESTEVTHF”         0 KB       0 rows
. . exported “CHARLES”.”DNN_DYNAMICFORMS_QUESTION”           0 KB       0 rows
. . exported “CHARLES”.”DNN_DYNAMICFORMS_QUESTIONOPT”        0 KB       0 rows
. . exported “CHARLES”.”DNN_DYNAMICFORMS_QUESTIONROLE”       0 KB       0 rows
. . exported “CHARLES”.”DNN_DYNAMICFORMS_QUESTIONRSP”        0 KB       0 rows
Master table “CHARLES”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for CHARLES.SYS_EXPORT_SCHEMA_01 is:
C:\APP\ORACLE\BACKUPDP\CHARLES_EXP.DMP
Job “CHARLES”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 18:20:51

Using  IMPDP :

Doing a IMPDP dump look below..

–Database Export Mode (Example)
impdp system/manager DIRECTORY=expdp_dir DUMPFILE=expfull_DB.dmp FULL=y LOGFILE=impfull_DB.log

–Schema Export Mode (Examples)
impdp charles/charles DIRECTORY=expdp_dir DUMPFILE=expfull.dmp SCHEMAS=hr,scott,scott_history LOGFILE=schema_imp.log

impdp charles/charles DIRECTORY=expdp_dir DUMPFILE=charles_exp.dmp
SCHEMAS=scott LOGFILE=charles_imp.log

;;;
Import: Release 11.1.0.6.0 – Production on Thursday, 02 July, 2009 18:27:49

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “CHARLES”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Starting “CHARLES”.”SYS_IMPORT_SCHEMA_01″:  charles/******** DIRECTORY=expdp_dir DUMPFILE=charles_exp.dmp SCHEMAS=charles LOGFILE=charles_imp.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “CHARLES”.”DNN_DYNAMICFORMS_WIZARD”         7.937 KB       1 rows
. . imported “CHARLES”.”DNN_DYNAMICFORMS_BLACKLIST”          0 KB       0 rows
. . imported “CHARLES”.”DNN_DYNAMICFORMS_DYNEVTPAYS”         0 KB       0 rows
. . imported “CHARLES”.”DNN_DYNAMICFORMS_DYNQSTEVENT”        0 KB       0 rows
. . imported “CHARLES”.”DNN_DYNAMICFORMS_EVENTS”             0 KB       0 rows
. . imported “CHARLES”.”DNN_DYNAMICFORMS_FLOCALTION”         0 KB       0 rows
. . imported “CHARLES”.”DNN_DYNAMICFORMS_QLOCALTION”         0 KB       0 rows
. . imported “CHARLES”.”DNN_DYNAMICFORMS_QUESTEVTHF”         0 KB       0 rows
. . imported “CHARLES”.”DNN_DYNAMICFORMS_QUESTION”           0 KB       0 rows
. . imported “CHARLES”.”DNN_DYNAMICFORMS_QUESTIONOPT”        0 KB       0 rows
. . imported “CHARLES”.”DNN_DYNAMICFORMS_QUESTIONROLE”       0 KB       0 rows
. . imported “CHARLES”.”DNN_DYNAMICFORMS_QUESTIONRSP”        0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job “CHARLES”.”SYS_IMPORT_SCHEMA_01″ completed with 3 error(s) at 18:28:09

Oracle Database 11g: SQL Query Result Set Caching

SQL Query Result Set Caches

In previous versions it was possible to manipulate the instance’s memory structures to improve the performance of user queries. For instance, a table or index could be “pinned” into memory via usage of the db_keep_cache. Alternatively, tables could be “cached,” prohibiting tables – accessed via a full scan – from being placed on the tail-end of the LRU.

In 11g Oracle introduces a new area of memory within the shared pool, in which it pins the blocks of appropriately marked user queries and/or deterministic PL/SQL programs. Once pinned, these blocks can be reused by future submissions of the query and/or PL/SQL calls in question; this significantly reduces I/O. This is referred to as server-side SQL Result Caching.

A SQL Query Result Set will be cached within a subsection of a database instance’s shared pool in the Shared Global Area (SGA) depending on the setting for several new initialization parameters.

RESULT_CACHE_MODE. This new parameter accepts one of three values, and it can be set at either the database (ALTER SYSTEM) or individual session (ALTER SESSION) level:

• When set to MANUAL (the default), a SQL query result set will only be considered for potential caching if the query itself specifies the +RESULT_CACHE optimizer hint.
• If this parameter is set to FORCE, however, a query’s result set will always be cached unless the query specifies the +NO_RESULT_CACHE optimizer hint.
• Finally, if this parameter is set to AUTO, Oracle 11g uses an unpublished internal algorithm to automatically determine whether a query’s result set should be cached based on how often the result set could benefit future statement execution. Only if the query specifies the +NO_RESULT_CACHE optimizer hint will it be ignored.

Controlling Result Set Cache Memory Utilization. Oracle 11g also provides several methods to limit precisely the amount of memory that may be allocated for SQL query result set caching:

RESULT_CACHE_MAX_SIZE. To reserve an appropriate amount of SGA memory for all local result caches, the DBA can specify a value for the RESULT_CACHE_MAX_SIZE initialization parameter. Oracle 11g automatically rounds the supplied value to the nearest 32K boundary.

If no value is supplied, then Oracle 11g uses the following algorithm to allocate memory for Result Caches:

• If a value has been specified for the new Oracle 11g MEMORY_TARGET parameter (i.e. the total memory allocated to both SGA and PGA for the database instance), then Oracle sets RESULT_CACHE_MAX_SIZE to 0.25% of MEMORY_TARGET.
• If no value for MEMORY_TARGET has been set, but a value for SGA_TARGET has been set, then Oracle 11g sets RESULT_CACHE_MAX_SIZE to 0.5% of SGA_TARGET.
• Finally, if neither a value for MEMORY_TARGET or SGA_TARGET has been set, then Oracle sets RESULT_CACHE_MAX_SIZE to 1.0% of the memory allocated to the Shared Pool based on the setting for SHARED_POOL_SIZE.

Regardless of which calculation method is used, note that Oracle 11g will never set RESULT_CACHE_MAX_SIZE to more than 75% of SHARED_POOL_SIZE. Moreover, note that if the DBA wants to deactivate SQL Result Caching features completely, she merely needs to set the size of this memory allocation area to zero (0) to tell Oracle 11g to reserve absolutely no memory for results caching.

RESULT_CACHE_MAX_RESULT. This parameter tells Oracle 11g how much of the result cache should be allowed for any individual query. Its default value of 5% of the entire result cache should usually be sufficient, but it can also be set between 0% and 100%.

RESULT_CACHE_REMOTE_EXPIRATION. If a query depends on a remote database, then this parameter determines the number of minutes for which a result set should be retained. The default value of zero (0) minutes serves as a reminder that any changes to a remote database table can’t be detected at the local database, and therefore stale result sets might remain for an unduly long period of time. This parameter can be set globally (ALTER SYSTEM) or on a per-session basis (ALTER SESSION).

PL/SQL Cursor For Loop

Sequence of steps to be  followed to define and use a cursor.

1.         Open the cursor.

2.         Start a loop.

3.         Fetch a row of data.

4.         Check for a new row, exit if not.

5.         Process the data.

6.         End the loop.

7.         Close the cursor.

Since these steps are almost always followed, Oracle provides a way to let PL/SQL perform most of the steps.  This is called the CURSOR FOR loop.  As in the name, it uses a FOR loop to process the cursor.

SQL> declare
2    cursor c8 is
3      select
4        initcap(author_last_name)  l_name,
5        initcap(author_first_name) f_name
6      from author;
7  begin
8    for r_c8 in c8 loop
9      dbms_output.put_line(r_c8.f_name||’ ‘||
10                           r_c8.l_name);
11    end loop;
12  end; /

Mark Jones
Alvis Hester
Erin Weaton
Pierre Jeckle
Lester Withers

The CURSOR FOR is a very efficient cursor.  Lines 2 through 6 define the cursor.  Line 8 handles opening the cursor, fetching the rows, testing for new rows, and exiting when a new row is not returned.  Notice that the record r_c8 is not defined in the declaration section.  The CURSOR FOR automatically defines r_c8 as a c8%rowtype.  The scope for r_c8 is only inside the CURSOR FOR loop.  Likewise, the cursor attributes for cursor c8 are defined only inside the CURSOR FOR loop.

If you define the record r_c8 in the declare section of the PL/SQL block, the variable you defined will go out of scope when the CURSOR FOR defines the record r_c8 and will return in scope when the loop ends.  If there is a requirement to access the cursor attributes from outside the loop, a variable must be defined and the cursor attribute copied into it before the loop ends.  The CURSOR FOR closes the cursor at the END LOOP clause.  Below is an example that calculates the average of total store sales by store, using the cursor %rowcount attribute.

SQL> declare
2    cursor c9 is
3      select sum(quantity) qty
4      from sales group by store_key;
5    sumer   number := 0;
6    counter number := 0;
7  begin
8    for r_c9 in c9 loop
9      sumer := sumer + r_c9.qty;
10      counter := c9%rowcount;
11    end loop;
12    dbms_output.put_line(‘Average is ‘||
13          sumer/counter);
14  exception
15    when others then
16      if c9%isopen then close c9;
17      end if;
18  end; /

Average is 11055

In order to count the number of items summed, the attribute c9%rowcount must be assigned inside the loop at line 10.  Also note that an exception happening inside the loop will jump to the exceptions handler which must ensure that the cursor is properly closed.

The CURSOR FOR can also be passed parameters.  It follows the same rules as the manual cursor in that if the cursor is defined with a parameter, it must be opened with a parameter, or the cursor will raise an exception.

SQL> declare
2    cursor c10(v_avg in number) is
3      select store_name, sum(quantity) qty
4      from store join sales using (store_key)
5      group by store_name
6      having sum(quantity) > v_avg;
7  begin
8    for r_c10 in c10 (11055) loop
9      dbms_output.put_line(initcap(
10          r_c10.store_name)||
‘         ‘||r_c10.qty);
11    end loop;
12  end; /

Borders                             21860
Books For Dummies            13000
Hot Wet And Sexy Books    24700
Wee Bee Books                 13700
Eaton Books                     12120

The variable v_avg is defined as an IN parameter.  This designation is optional as a variable can only be passed as mode IN, and the passed parameter can also be assigned a default value.  If a default value is assigned, then the cursor can be opened without passing a parameter and it will use the defined default value.

Notice in the example below that v_avg is assigned a default value in line 2, and the cursor is opened without passing a parameter in line 8.

SQL> declare
2    cursor c10(v_avg number:= 11055) is
3      select store_name, sum(quantity) qty
4      from store join sales using (store_key)
5      group by store_name
6      having sum(quantity) > v_avg;
7  begin
8    for r_c10 in c10 loop
9      dbms_output.put_line(initcap(
10         r_c10.store_name)||
‘         ‘||r_c10.qty);
11    end loop;
12  end;
13  /

Borders                   21860
Books For Dummies         13000
Hot Wet And Sexy Books    24700
Wee Bee Books             13700
Eaton Books               12120

Another nice feature of the CURSOR FOR is the ability to define the cursor inline.  Below is a cursor that is not declared in the declaration section of the executing block.  The cursor definition is included in the CURSOR FOR.

SQL> begin
2    for r_c11 in
(select author_last_name l_name,
3            author_first_name f_name
4                  from author) loop
5      dbms_output.put_line(initcap(
6        r_c11.l_name||
‘, ‘||r_c11.f_name));
7    end loop;
8  end;
9  /

Jones, Mark
Hester, Alvis
Weaton, Erin
Jeckle, Pierre
Withers, Lester

Defining a cursor inline has the advantage of self documenting the cursor in the code.  Instead of going back to the declare section to determine the cursor definition, the programmer maintaining the code can see the definition right there in the code that calls it.

However, if the cursor is used multiple times, it will be in multiple locations in the code, reducing maintainability.

Another problem with defining the cursor inline is that the cursor will have no defined name.  Therefore you will not be able to access the cursor attributes.  This can be a problem if an exception is raised inside the cursor that jumps out of the loop.

You can get around this problem by wrapping the loop inner statements in a separate block with an exception handler.

begin
for r_c11 in (select author_last_name l_name,
author_first_name f_name
from author) loop
begin
dbms_output.put_line(initcap(r_c11.l_name||
‘, ‘||r_c11.f_name));
– lots of other stuff to do.
exception
when others then
– handle all exceptions
end;
end loop;
end;

The exception handler must deal with all the exceptions without raising new ones unless it terminates the program.  Any exception raised in the exception handler will jump to the end of the loop and leave the cursor open.

Another powerful feature of the CURSOR FOR is the update feature.  This allows the programmer to efficiently update data returned by the cursor.

Oracle System Table Description

ALL_ARGUMENTS : Arguments in object accessible to the user
ALL_CATALOG :  All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS :  Comments on columns of accessible tables and views
ALL_CONSTRAINTS :  Constraint definitions on accessible tables
ALL_CONS_COLUMNS :  Information about accessible columns in constraint definitions
ALL_DB_LINKS  : Database links accessible to the user
ALL_ERRORS :  Current errors on stored objects that user is allowed to create
ALL_INDEXES :  Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS  COLUMNS : comprising INDEXes on accessible TABLES
ALL_LOBS :  Description of LOBs contained in tables accessible to the user
ALL_OBJECTS :  Objects accessible to the user
ALL_OBJECT_TABLES :  Description of all object tables accessible to the user
ALL_SEQUENCES :  Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS :  Snapshots the user can access
ALL_SOURCE :  Current source on stored objects that user is allowed to create
ALL_SYNONYMS :  All synonyms accessible to the user
ALL_TABLES :  Description of relational tables accessible to the user
ALL_TAB_COLUMNS :  Columns of user’s tables, views and clusters
ALL_TAB_COL_STATISTICS :  Columns of user’s tables, views and clusters
ALL_TAB_COMMENTS :  Comments on tables and views accessible to the user
ALL_TRIGGERS :  Triggers accessible to the current user
ALL_TRIGGER_COLS :  Column usage in user’s triggers or in triggers on user’s tables
ALL_TYPES :  Description of types accessible to the user
ALL_UPDATABLE_COLUMNS :  Description of all updatable columns
ALL_USERS :  Information about all users of the database
ALL_VIEWS :  Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL :  Database compatible parameter set via init.ora
DBA_DB_LINKS :  All database links in the database
DBA_ERRORS :  Current errors on all stored objects in the database
DBA_OBJECTS :  All objects in the database
DBA_ROLES :  All Roles which exist in the database
DBA_ROLE_PRIVS :  Roles granted to users and roles
DBA_SOURCE :  Source of all stored objects in the database
DBA_TABLESPACES :  Description of all tablespaces
DBA_TAB_PRIVS :  All grants on objects in the database
DBA_TRIGGERS :  All triggers in the database
DBA_TS_QUOTAS :  Tablespace quotas for all users
DBA_USERS :  Information about all users of the database
DBA_VIEWS :  Description of all views in the database
DICTIONARY :  Description of data dictionary tables and views
DICT_COLUMNS :  Description of columns in data dictionary tables and views
GLOBAL_NAME :  global database name
NLS_DATABASE_PARAMETERS :  Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS  NLS parameters of the instance
NLS_SESSION_PARAMETERS :  NLS parameters of the user session
PRODUCT_COMPONENT_VERSION :  version and status information for component products
ROLE_TAB_PRIVS :  Table privileges granted to roles
SESSION_PRIVS :  Privileges which the user currently has set
SESSION_ROLES :  Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP :  Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES :  Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP :  Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names

Oracle Overview

Physical and logical structuring in Oracle

An Oracle database system — identified by an alphanumeric system identifier or SID — comprises at least one instance of the application, along with data storage. An instance — identified persistently by an instantiation number (or activation id: SYS.V_$DATABASE.ACTIVATION#) — comprises a set of operating-system processes and memory-structures that interact with the storage. Typical processes include PMON (the process monitor) and SMON (the system monitor).

Users of Oracle databases refer to the server-side memory-structure as the SGA (System Global Area). The SGA typically holds cache information such as data-buffers, SQL commands and user information. In addition to storage, the database consists of online redo logs (which hold transactional history). Processes can in turn archive the online redo logs into archive logs (offline redo logs), which provide the basis (if necessary) for data recovery and for some forms of data replication.

The Oracle RDBMS stores data logically in the form of tablespaces and physically in the form of data files. Tablespaces can contain various types of memory segments; for example, Data Segments, Index Segments etc. Segments in turn comprise one or more extents. Extents comprise groups of contiguous data blocks. Data blocks form the basic units of data storage. At the physical level, data-files comprise one or more data blocks, where the block size can vary between data-files.

Oracle database management tracks its computer data storage with the help of information stored in the SYSTEM tablespace. The SYSTEM tablespace contains the data dictionary — and often (by default) indexes and clusters. (A data dictionary consists of a special collection of tables that contains information about all user-objects in the database). Since version 8i, the Oracle RDBMS also supports “locally managed” tablespaces which can store space management information in bitmaps in their own headers rather than in the SYSTEM tablespace (as happens with the default “dictionary-managed” tablespaces).

If the Oracle database administrator has instituted Oracle RAC (Real Application Clusters), then multiple instances, usually on different servers, attach to a central storage array. This scenario offers numerous advantages, most importantly performance, scalability and redundancy. However, support becomes more complex, and many sites do not use RAC. In version 10g, grid computing has introduced shared resources where an instance can use (for example) CPU resources from another node (computer) in the grid.

The Oracle DBMS can store and execute stored procedures and functions within itself. PL/SQL (Oracle Corporation’s proprietary procedural extension to SQL), or the object-oriented language Java can invoke such code objects and/or provide the programming structures for writing them.

Database schema

Oracle database conventions refer to defined groups of ownership (generally associated with a “username”) as schemas.

Most Oracle database installations traditionally come with a default schema called SCOTT. After the installation process has set up the sample tables, the user can log into the database with the username scott and the password tiger. The name of the SCOTT schema originated with Bruce Scott, one of the first employees at Oracle (then Software Development Laboratories), who had a cat named Tiger.

The SCOTT schema has seen less use as it uses few of the features of the more recent releases of Oracle. Most recent examples reference the default HR or OE schemas.
Other default schemas include:

* SYS (essential core database structures and utilities)
* SYSTEM (additional core database structures and utilities, and privileged account)
* OUTLN (utilized to store metadata for stored outlines for stable query-optimizer execution plans.
* BI, IX, HR, OE, PM, and SH (expanded sample schemas containing more data and structures than the older SCOTT schema)

Memory architecture : System Global Area

Each Oracle instance uses a System Global Area or SGA — a shared-memory area — to store its data and control-information.

Each Oracle instance allocates itself an SGA when it starts and de-allocates it at shut-down time. The information in the SGA consists of the following elements, each of which has a fixed size, established at instance startup:

• The database buffer cache: this stores the most recently-used data blocks. These blocks can contain modified data not yet written to disk (sometimes known as “dirty blocks”), unmodified blocks, or blocks written to disk since modification (sometimes known as clean blocks). Because the buffer cache keeps blocks based on a most-recently-used algorithm, the most active buffers stay in memory to reduce I/O and to improve performance.

• The redo log buffer: this stores redo entries — a log of changes made to the database. The instance writes redo log buffers to the redo log as quickly and efficiently as possible. The redo log aids in instance recovery in the event of a system failure.

• The shared pool: this area of the SGA stores shared-memory structures such as shared SQL areas in the library cache and internal information in the data dictionary. An insufficient amount of memory allocated to the shared pool can cause performance degradation.

Library cache:

The library cache stores shared SQL, caching the parse tree and the execution plan for every unique SQL statement.

If multiple applications issue the same SQL statement, each application can access the shared SQL area. This reduces the amount of memory needed and reduces the processing-time used for parsing and execution planning.

Data dictionary cache:

The data dictionary comprises a set of tables and views that map the structure of the database.

Oracle databases store information here about the logical and physical structure of the database. The data dictionary contains information such as:

• * user information, such as user privileges

• * integrity constraints defined for tables in the database

• * names and datatypes of all columns in database tables

• * information on space allocated and used for schema objects

The Oracle instance frequently accesses the data dictionary in order to parse SQL statements. The operation of Oracle depends on ready access to the data dictionary: performance bottlenecks in the data dictionary affect all Oracle users. Because of this, database administrators should make sure that the data dictionary cache has sufficient capacity to cache this data. Without enough memory for the data-dictionary cache, users see a severe performance degradation. Allocating sufficient memory to the shared pool where the data dictionary cache resides precludes these particular performance problems. But after the things u have to restart your database and after the restarted the data base. go the system

Program Global Area

The Program Global Area or PGA memory-area contains data and control-information for Oracle’s server-processes.

The size and content of the PGA depends on the Oracle-server options installed. This area consists of the following components:

• stack-space: the memory that holds the session’s variables, arrays, and so on.

• session-information: unless using the multithreaded server, the instance stores its session-information in the PGA. (In a multithreaded server, the session-information goes in the SGA.)

• private SQL-area: an area in the PGA which holds information such as bind-variables and runtime-buffers.

• sorting area: an area in the PGA which holds information on sorts, hash-joins, etc.

Process architecture : Oracle processes

The Oracle RDBMS typically relies on a group of processes running simultaneously in the background and interacting to further and monitor database operations. Such processes (and their standard abbreviations) can include:

• archiver processes (ARCn)

• checkpoint process (CKPT)

• coordinator-of-job-queues process (CJQn): dynamically spawns slave processes for job-queues

• * database writer processes (DBWn)

• dispatcher processes (Dnnn): multiplex server-processes on behalf of users

• memory-manager process (MMAN): used for internal database tasks such as Automatic Shared Memory Management

• log-writer process (LGWR)

• * log-write network-server (LNSn): transmits redo logs in Data Guard environments

• logical standby coordinator process (LSP0): controls Data Guard log-application

• media-recovery process (MRP): detached recovery-server process

• memory-monitor process (MMON)

• memory-monitor light process (MMNL): gathers and stores Automatic Workload Repository (AWR) data

• process-monitor process (PMON)

• process-spawner (PSP0): spawns Oracle processes

• queue-monitor processes (QMNn)

• recoverer process (RECO)

• remote file-server process (RFS)

• shared server processes (Snnn): serve client-requests

• system monitor process (SMON)

User processes, connections and sessions:

Oracle Database terminology distinguishes different computer-science terms in describing how end-users interact with the database:

• user processes involve the invocation of application software

• a connection refers to the pathway linking a user process to an Oracle instance

• sessions consist of specific connections to an Oracle instance. Each session within an instance has a session identifier or “SID”