Generating Statspack, AWR and ADDM reports

STATSPACK

The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters

Continue reading

Advertisements

Things to do during a Database Health Check

HEALTH CHECK :

The Monitoring of predefined events that generates a message or warning when a certain threshold has been exceeded. The database health check is required for the following reason:

  1. Smooth running of production
  2. Database performance
  3. In Support of the SLA (service level agreement)

Continue reading

Data Pump Export Parameter and Values

 

Data Pump Export (hereinafter referred to as Export for ease of reading) is a utility for unloading data and metadata into a set of operating system files called a dump file set. The dump file set can be imported only by the Data Pump Import utility. The dump file set can be imported on the same system or it can be moved to another system and loaded there.

The Data Pump export utility provides a mechanism for transferring data objects between Oracle databases. The utility is invoked with the following command:

 

Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Export runs by entering the ‘expdp’ command followed by various parameters.

To specify parameters, you use keywords:

Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,…,valueN)Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scottor TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.

————————————————————————————————————–

Continue reading

NOARCHIVELOG DATABASE RECOVERY VIA RMAN

Your database is running in No Archive log mode and you have daily/weekly RMAN backup configured.

  •  One fine day, your server crashed and you lose one of the disks or all the disks.
  • You have no other option but to restore the whole database backup to point of last valid backup.

 

rman target / catalog rman10/rman10@rman10s

Recovery Manager: Release 10.1.0.3.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.


Connected to target database (not started)

Connected to recovery catalog database


RMAN>

RMAN>

RUN 

{
STARTUP NOMOUNT FORCE;
ALLOCATE CHANNEL CH1 DEVICE TYPE 'SBT_TAPE' 
PARMS  'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.orcld.opt)';
RESTORE SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RESTORE DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

 

Now you will face the below error message :

Startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file ‘/freespace/ORACLE/OCM/prod10gr1/dbs/initorcl.ora’

 

But Oracle tries to boot up:

Trying to start the Oracle instance without parameter files...

Oracle instance started

Total System Global Area     142606336 bytes

Fixed Size                      778072 bytes

Variable Size                 70525096 bytes

Database Buffers              67108864 bytes

Redo Buffers                   4194304 bytes



Allocated channel: CH1

Channel CH1: Sid=28 devtype=DISK

Starting restore at 11-JAN-17

Channel CH1: starting datafile backupset restore

Channel CH1: restoring SPFILE

Output filename=/freespace/ORACLE/OCM/prod10gr1/dbs/spfileorcl.ora

Channel CH1: restored backup piece 1

Piece handle=/freespace/ORACLE/OCM/orcl/ORCL.20070211.8.1.1.614285217 tag=TAG20070211T184641

Channel CH1: restore complete

Finished restore at 11-JAN-17


Oracle instance shut down

connected to target database (not started)

Oracle instance started


Total System Global Area    1241513984 bytes




Fixed Size                      778896 bytes

Variable Size                347348336 bytes

Database Buffers             889192448 bytes

Redo Buffers                   4194304 bytes




Starting restore at 11-JAN-17

allocated channel: ORA_DISK_1

Channel ORA_DISK_1: Sid=159 devtype=DISK




Channel ORA_DISK_1: starting datafile backupset restore

Channel ORA_DISK_1: restoring controlfile

Channel ORA_DISK_1: restored backup piece 1

Piece handle=/freespace/ORACLE/OCM/orcl/ORCL.20070211.8.1.1.614285217 tag=TAG20070211T184641

Channel ORA_DISK_1: restore complete

Output filename=/freespace/ORACLE/OCM/orcl/control01.ctl

Output filename=/freespace/ORACLE/OCM/orcl/control02.ctl

Output filename=/freespace/ORACLE/OCM/orcl/control03.ctl

Finished restore at 11-JAN-17




Database mounted

Released channel: ORA_DISK_1




Starting restore at 11-JAN-17

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: Sid=159 devtype=DISK




Channel ORA_DISK_1: starting datafile backupset restore

Channel ORA_DISK_1: specifying datafile(s) to restore from backup set

Restoring datafile 00001 to /freespace/ORACLE/OCM/orcl/system01.dbf

Restoring datafile 00002 to /freespace/ORACLE/OCM/orcl/undotbs01.dbf

Restoring datafile 00003 to /freespace/ORACLE/OCM/orcl/sysaux01.dbf

Restoring datafile 00004 to /freespace/ORACLE/OCM/orcl/users01.dbf

Channel ORA_DISK_1: restored backup piece 1

Piece handle=/freespace/ORACLE/OCM/orcl/ORCL.20070211.7.1.1.614285202 tag=TAG20070211T184641

Channel ORA_DISK_1: restore complete

Finished restore at 11-JAN-17




Database opened

New incarnation of database registered in recovery catalog

Starting full resync of recovery catalog

Full resync complete

 

This is how you can restore the database to the point where last backup was taken.

Major differences between STANDARD and ENTERPRISE Edition (Oracle Database 11g)

Lets get to know about various editions of ORACLE and also the major difference between Standard and Enterprise edition.

Oracle Enterprise Edition:

Enterprise Edition is the full (top of the range) version or the Oracle Database Server. Options like RAC, Partitioning, Spatial, etc. can be purchased separately to enhance the functionality of the database.

Oracle Standard Edition:

Standard Edition is designed for smaller businesses and enterprises. It offers a subset of the features/ functionality implemented in Enterprise Edition. Database options like Data Guard, Partitioning, Spatial, etc. is not available with Standard Edition (from 10g one can use RAC with Standard Edition). Standard Edition can only be licensed on servers with a maximum capacity of four processors. Continue reading

ORACLE INSTALLATION ON NON-WINDOWS PLATFORM

Greeting to all of my readers,

So Let’s get started with installation of Oracle 11g on Linux/Unix operating system.

 

After logging onto UNIX system (using any user), the following is done:

# su – root

(Switches User to root; it will ask for root’s password)

# id

(It will give the uid and other details of the root user; uid of root (super user) is always 0)

Continue reading