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.

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;

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