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

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;

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).