Wednesday, February 9, 2011

ORACLE BACKUP

Importance of Backup:

Before you create an Oracle database, decide how to protect the database against potential media failures. If you do not develop a backup strategy before creating your database, then you may not be able to perform recovery if a disk failure damages the datafiles, online redo log files, or control files. Hardware and software can always be replaced, but your data is irreplaceable, hence backup plays the most important role.

Database Backup: The following methods are valid for backing-up an Oracle database:

Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file.


Redundancy Set: It is a set of files needed to recover from any sort of database failure, we can create it (redundancy set) using the User managed/RMAN utility backups. It comprises of the following files:

· Backup of the control file and all the data files

· All archived redo logs generated after the last backup was taken

· A duplicate of the online redo log files generated by Oracle multiplexing

· A duplicate of the current control file generated by Oracle multiplexing

· Configuration files such as the server parameter file/parameter file (spfile/pfile), tnsnames.ora, and listener.ora

Golden Rule of Backup: It is recommended that you store the backup files (Redundancy set) separate from the database files, so that if the disk containing database files fails, we can restore the database using redundancy set (backup). Oracle also recommends to keep at least one copy of the entire redundancy set--including the most recent backup--on hard disk.

Cold or Off-line Backups (User Managed) - shut the database down and backup up ALL data, log, and control files.

Hot or On-line Backups (User Managed) - If the database is available and in ARCHIVELOG mode, set the table spaces into backup mode and backup their files. Also remember to backup the control files and archived redo log files.

RMAN Backups - While the database is off-line or on-line, use the "rman" utility to backup the database. (Oracle recommended).

Backup Frequency: It normally depends on the following factors:

· Rate of data change/ transaction rate

· Database availability/ Can you shutdown for cold backups?

· Criticality of the data/ Value of the data to the company

· Read-only table space needs backing up just once right after you make it read-only

· If you are running the database in archive log mode you can backup parts of a database over an extended cycle of days

· If archive logging is enabled one needs to backup archived log files timeously to prevent database freezes

Rman backup is the best option and oracle recommends it.

Advantage of RMAN :

· We no need to COPY data files manually to backup location

· RMAN is fast then User Managed backup

· We don’t put database in backup mode

· We can take backup in single RUN command

· RMAN also takes backup of archive log and delete all archive logs which are backed up.

· We don’t need to take the backup of spfile or controlfile separately; RMAN will take the backup of both.

Tuesday, September 21, 2010

Statspack Utility

STATSPACK: It is a diagnosis tool for instance-wide performance problems; also supports application tuning activities by providing data which identifies high-load SQL statements. STATSPACK can be used both proactively to monitor the changing load on a system, and also reactively to investigate a performance problem

When a snapshot is executed, the STATSPACK software will sample from the in-memory structures inside the SGA and transfer the values into the corresponding STATSPACK tables.

Installing and Configuring Statspack:

The STATSPACK utility requires a tablespace to store all of the objects and data gathered. It is suggested that the tablespace be called STATSPACK for the STATSPACK tables. Closely watch the STATSPACK data to ensure that the stats$sql_summary table is not taking an inordinate amount of space.

SQL> CREATE TABLESPACE statspack
DATAFILE '/u01/ora10/mydb1 /statspack.dbf' SIZE 200M AUTOEXTED ON NEXT 10M MAXSIZE 500M;

Once the tablespace is created run the following script to generate the statspack user schema and the utility tables.

> sqlplus "/ as sysdba"
SQL> @?/rdbms/admin/spcreate

Once the above script is completed, we can set the snapshot data collection level as per our requirement, default level is 5.

SQL> exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');

Snapshot Level Details:

Level 0 - GENERAL PERFORMANCE

This level can be used to gather general performance information about the database.

Level 5 - GENERAL PERFORMANCE + SQL STATEMENTS (DEFAULT)

This snapshot level will gather all the information from the previous levels, plus it will collect

performance data on high resource SQL statements. This is also the default snapshot level when

Statspack is installed.

Level 6 - GENERAL PERFORMANCE + SQL STATEMENTS + SQL PLANS AND SQL PLAN USAGE

This level is new in Oracle9i and it will include all the information collected from the previous

snapshot levels, plus execution path and plan usage information as they relate to high resource SQL

statements. This type of information can prove critical when determining if the execution path or plan

has changed for high resource SQL statements. Oracle recommends using this level for when one of the

following situations has occurred:

- A plan has possibly changed after large volumes of data have been added.

- Obtaining new optimizer setting information.

Level 10 - GENERAL PERFORMANCE + SQL STATEMENTS + SQL PLANS AND SQL PLAN USAGE + PARENT AND CHILD LATCHES

This level will include all the information collected from previous snapshot levels, plus the addition of

parent and child latch information. This level will take even longer to complete since the parent and

child latch information are added to the duration of the previous 2 levels, which are already information

gathering intensive. First, because the information gathered is based on the shared_pool_size and

secondly the volume of information gathered based on SQL statement information, plus the parent and

child latch information. Snapshots taken from this level will take even longer and it is Oracle's recommendation to only use this level when requested by Oracle technical support personnel.

It is recommended to set the timed_statistics to true BEFORE the first snapshot because it will help to establish a better baseline, otherwise another baseline will be needed AFTER it is turned on. This can be done with the Alter SYSTEM command and/or setting it in the init.ora file.

Snapshot creation:

Login to the perfstat user and execute the following command:

Manual Mode:

SQL> Connect perfstat/******
SQL> exec statspack.snap;

Automated Mode:

Automating and scheduling when to take snapshots allows for the collection of database performance information that would be beneficial for troubleshooting performance problems that occurred earlier.

The following are two ways that snapshots can be automated:

Ø Oracle's DBMS_JOB utility to schedule snapshots. This utility will be discussed in greater detail.

Ø An operating specific job scheduler. For example on Unix, shell scripts can be written and then scheduled through the CRON scheduler.

To view the snapshot details:

SQL> select name, snap_id, to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')
"Date/Time" from stats$snapshot,v$database;


NAME SNAP_ID Date/Time
--------- ---------- -------------------
MYDB1 4 14.09.2010:10:56:01
MYDB1 1 13.09.2004:08:48:47
MYDB1 2 13.09.2010:09:00:01
MYDB1 3 13.09.2010:09:01:48

Creating report:

sqlplus perfstat/******
SQL> @?/rdbms/admin/spreport.sql

Information on Sections of the Statspack Report:

Section(s)

What You Can Use the Section(s) for

Wait Events

Look for excessive waits and wait times; drill down to specific problems

SQL Ordered by Buffer Gets, Physical Reads, and Rows
Processed

Figure out which SQL statements to tune

Instance Activity Statistics

Compare with baseline report; compute additional statistics

Tablespace and File I/O

Investigate I/O bottlenecks, identify files and tablespaces
with heavy I/O

Buffer Pool

Identify specific buffer pools with high contention or I/O

Buffer Wait Statistics

Identify types of buffers with large number of buffer waits

Enqueue Activity

Investigate specific lock types that are causing the most
waits

Rollback Segment Statistics and Storage

Investigate waits for rollback segment headers

Latch Activity, Latch Sleep Breakdown, Latch Miss
Sources

Identify latching bottlenecks; diagnose and related problems

Library Cache

Diagnose problems with shared pool

Non-default init.ora

Look for unnecessary or problematic parameter definitions

Wait Problems and their potential fixes:

Wait Problem

Potential Fix

DB File
Scattered Read

Wait for Multi-block read of a table or index (full scan): tune the code and/or
cache small tables.

DB File
Sequential Read

Wait for single block read of a table or index. Indicates many index reads: tune the
code (especially joins).

DB File parallel
read

Used when Oracle performs in parallel reads from multiple datafiles to noncontiguous
buffers in memory (PGA or Buffer Cache). Similar to db file sequential
read

Free Buffer

Increase the DB_CACHE_SIZE; shorten the checkpoint; tune the code.

Buffer Busy

Segment header: add freelists or freelist groups.

Buffer Busy

Data block: separate "hot" data; use reverse key indexes and/or smaller blocks.

Buffer Busy

Data block: increase initrans and/or maxtrans.

Buffer Busy

Undo header: add rollback segments or areas.

Buffer Busy

Undo block: commit more often; use larger rollback segments or areas.

Latch Free

Investigate the latch detail.

Enqueue-ST

Use LMTs or preallocate large extents.

Enqueue-HW

Preallocate extents above high-water mark.

Enqueue-TX4

Enqueue-TX4 Increase initrans and/or maxtrans on the table or index.

Enqueue-TM

Index foreign keys; check application locking of tables.

Log Buffer Space

Increase the log buffer; use faster disks for the redo logs.

Log File Switch

Archive destination slow or full; add more or larger redo logs.

Log File Sync

Commit more records at a time; use faster redo log disks or raw devices.

Direct Path Read

Used by Oracle when reading directly into PGA (sort or hash)

Direct Path Write

Used by Oracle when writing directly into PGA (sort or hash)

Idle Event

Ignore it.

Common Idle Events:

Event

Idle Event Type

Dispatcher timer

Shared server

Lock manager wait for remote message

Oracle9i Real Application Clusters

Pipe get

User process

pmon timer

Background process

PX Idle wait

Parallel query

PX Deq Credit: need buffer

Parallel query

PX Deq Credit: send blkd

Parallel query

rdbms ipc message

Background process

smon timer

Background process

SQL*Net message from client

User process

virtual Circuit status

User process

To purge the snapshots:

SQL> @?/rdbms/admin/sppurge;
Enter the Lower and Upper Snapshot ID

Removing STATSPACK from the Database:

sqlplus "/ as sysdba"
SQL> @?/rdbms/admin/spdrop.sql
SQL> drop tablespace statspack including contents and datafiles;

Thursday, June 17, 2010

History of oracle

HISTORY OF ORACLE

More than three decades ago Larry Ellison saw an opportunity other companies missed: a description of a working prototype for a relational database. No company had committed to commercializing the technology, but Ellison and co-founders Bob Miner and Ed Oates realized the tremendous business potential of the relational database model.

l Software Development Laboratories – the company's name before it was called Oracle was founded by the Trio Larry Ellison, Bob Miner, and Ed Oates.

l Throughout its history Oracle has proved it can build technology for the future on the foundation of its innovations and, its intimate knowledge of customer challenges and successes analyzed by the best technical and business minds in the world.

l Thirty years later, Oracle is the gold standard for database technology and applications in enterprises throughout the world, from the largest multinational corporations to the corner coffee shop

1978: Oracle version 1

l Ran on PDP-11 under RSX, in 128 KB memory

l Written in assembly language

l Separated Oracle code (OPI) and user code (UPI)

1979: Oracle version 2

l • Written in PDP-11 assembly language

l • Ran on VAX/VMS in compatibility mode

1980: Oracle version 3

l Written in C: portable source code

l Retained split architecture

l Introduced the concept of atomic SQL execution

and transactions (commit, rollback)

1984: Oracle version 4

l Introduced read consistency

l Ported to many platforms

l Interoperability between PC and server

1986: Oracle version 5

l True client-server (distributed processing)

l VAX-cluster support

l Version 5.1: Distributed queries

1989: Oracle version 6 (major kernel rewrite)

l OLTP performance enhancements, savepoints

l Online backup and recovery

l Row-level locking, PL/SQL in the database

l Parallel Server (VAX clusters, nCube)

1993: Oracle7

l Declarative referential integrity

l Stored procedures and triggers

l Shared SQL, parallel execution

l Advanced replication

1997: Oracle8

l Object-relational extensions in the database

l From client/server to three-tier architecture

l Partitioning option

1999: Oracle8i

l Java in the database (JVM and SQLJ)

l Partitioning enhancements

l Data warehousing enhancements

l XML support

l Summary management

l Oracle Internet Directory (LDAP)

l Ported to Linux

2001: Oracle9i

l Real Application Clusters, with cache fusion

l Scalability on inexpensive clustered hardware

l Automatic segment-space management

l Internet security enhancements

l Integrated business intelligence functionality

l Data Guard (standby databases)

l Oracle managed files

l Globalization support (Unicode, time zones, locales)

2003: Oracle 10g

l Primary goal: Build a self-managing database that requires minimal human intervention.

l Reduction in administration cost without compromising high availability, scalability, and security.

l Minimal performance impact

l Effective for all configurations and workloads