Linux Shell Scripting For Automatic Oracle Statspack report generation

 

This is the Linux Shell Scripting For Oracle Staspack report generation

statspack report  is  the picture of database between two points of time.thats why this is divided into two steps

 

These Steps Are:

 

1.            Create a shell script named

      first_step.sh at location from whereever you want it to run

 

      conn

sqlplus Perfstat/perfstat@db_name

 

                   exec statspack.snap;

           

                   and assign it in cronjob at time when you want to start taking the picture of of your database

 

2.      Create another shell script named

second_step.sh  at location from whereever you want it to run

                       

                        set head off

                        set timing off

                        sqlplus Perfstat/perfstat@db_name  <<EOF

                        exec statspack.snap;

                        spool /address/first_snap.lst;

                        select max(snap_id) from stats\$snapshot where snap_id not in (select max(snap_id) from        stats\$snapshot);

                        spool off;

                        spool /address/Second_snap.lst;

                         select max(snap_id) from stats\$snapshot;

                        spool off;

                        exit;

                        !

                        EOF

                        START_SNAP=$(cat /address/first_snap.lst  | tail +5 | head -1)

                        END_SNAP=$(cat /address/Second_snap.lst  | tail +5 | head -1)

                        REP_NAME=/address/sp'date'_ac

                        sqlplus Perfstat/perfstat@db_name  <<EOF

                        define start_snap=$ START_SNAP

                        define end_snap=$ END_SNAP

                        define rep_name=$ REP_NAME

                        @?/rdbms/admin/spreport.sql;

                        EOF

                        and also assign it in cronjob at time when you want to end the picture of of your database.

           

                        This two shell scripts assign in cronjob at two point of time whenever you want to view the                         picture of your DB .

Oracle Optimal Passes (i.e. memory gain in PGA auto Target)

 

Oracle Optimal Passes (i.e. memory gain in PGA auto Target)

 

OPTIMAL PASS :-

In Oracle if sort,hash join or group by suddenly gain memory in pga auto target it is said optimal pass.

 

ONE PASS:-

If the memory acquisition requires a single pass through pga_aggregate_target, then memory allocation is marked

as one pass.

 

MULTI PASS:-

 If all memory is in use, Oracle may have to make multiple passes through pga_aggregate_target

to acquire the memory. Multipass executions indicate a memory

shortage, and you should always allocate enough PGA memory to ensure that at least 90-95 percent of

connected tasks can acquire their memory optimally.

 

WHEN TO INCREASE  PGA_AGGREGATE_TARGET:-

Whenever the value of the v$sysstat statistic estimated PGA memory for one-pass

exceeds pga_aggregate_target, then you'll want to increase pga_aggregate_target.

Whenever the value of the v$sysstat statistic workarea executions-multipass is

 greater than 1 percent, the database may benefit from additional PGA memory.

 

To evaluate  this:-

 consider the following query:

 

select name c1,count c2,decode(total, 0, 0, round(count*100/total)) c3

from

(

select name,value count,(sum(value) over ()) total

from

v$sysstat

where

name like 'workarea ex%'

);

 

 

Oracle Server Tuning

 

ORACLE SERVER TUNING

 

Server Tuning:- server tuning refers that the server on which database resides is proper tuned i.e.

 

Here I am using a example of how a server is tuned properly and how input/output response from server should be at best performance rate.

 

Example:- let us suppose a database named TESTDB and there are 7 tablespaces are associated with that database.

 

These 7 Tablespaces has 7 data files there  roles in the database are:-

 

1.      testtbl_1.dbf  for previous year data( for read )

2.      testtbl_2.dbf  for current year data (read and write both)

3.      testtbl_3.dbf for index data(for read)

4.      system.dbf for database information.

5.      Testtemp_1.dbf for temporary data

6.      testundo_1.dbf for undo tablespace

7.      testtbl_7.dbf for history data more then one year

 

here some tablespace are for previous year data which is now using as history tablespaces and they are only for read purpose some tablespaces are for indexes and some for writable purpose and some for read and write both.

 

 

Just remember these main points while creating tablespaces

 

1.      tablespaces for read purpose are always on different disk

 

2.      tablespace for write purpose are on different disk

 

3.      indexes tablespaces are on different disk you can also create them with read only tablespace disk.

 

4.      Always trying to maintain the input/output management

because if read and write tablespace are on same disk I.e if input and output are performing on same disk in a database then it will degrade the system performance because input output are running on same disk if they are on different disks then you will notice the big improvement in speed.

 

5.      Always create objects on their respectively tablespace not on SYSTEM tablespace if system tablespace contains users objects like tables,triggers etc. then it create lack of performance.

Oracle Database Paramater file

 

Oracle Database Paramater file

 

 

Paramter file :- Parameter file is the file which contains the intialiazation parameters and value associated with each parameter

 

Oracle has  two types of parameter files:

 

Initialization Parameter Files:-this parameter file is in text format which contains the initialization parameters of database.

 

Which contains entries like

                        sessions

                        processes

                        user_dump_dest location

background_dump_dest location

core_dump_dest location

db_cache_size

java_pool_size

control_files  (which contains the control file location)

etc.

 

Server Parameter File :- this is also a parametr file but this is in binary format which is unreadable .the main advantage of using this file for database startup is

                                    when you change any parameter of database then that paramter new value directly applicable to server parameter file while database running.

 

Note:

1. if you want to know which type of parameter file is currently in used for startup then you issue

 

SQL> Show  parameter spfile;

 

If value is present under VALUE column that means spfile (server parameter file is used in starting the database.if VALUE cloumn show no value then initalization parameter file is used.

 

2.      if spfile is currently in used and you want to create the initparameter file then issue

 

SQL> Create pfile from spfile;

OR

SQL> Create pfile=ADDRESS_OF_FILE From spfile;

 

Oracle Database Startup Stages

Oracle Database Startup Stages

 

1.      Startup(Nomount)

2.      Mount

3.      Open

 

when startup command is issue to start the database then database startup goes from these three stages:-

 

1.      Startup(Nomount) :-  this is the first stage of database startup at this stage database entered in nomount stage where parameter file is read (i.e. init.ora). In which all the database parameter are read which includes all the memory areas and parameter like

 

 

user_dump_dest location

background_dump_dest location

core_dump_dest location

db_cache_size

java_pool_size

etc.

if any of the parameter is not correct or any of the file is not available at the location mentioned in parameter file then database doesn't start .

 

2.      Mount :-  this is the second stage of database startup at this stage database control file is read.in which all the datafiles address and database information is read if any file is not at the address mentioned in control file then database doesn't opened and shows error of missing file.

 

3.      Open :-  the last stage of startup is open at this stage all the datafiles are accessed

                        after accessing datafiles ,oracle checks that all the files are consistent or not.

 

 

 

Error::ORA-12154: TNS:could not resolve service name

If error comes as follows:
　


ORA-12154: TNS:could not resolve service name
　



STEPS TO SOLVE IT ARE:-

　

1.Check the entries in the TNSNAMES.ORA file located at

({ORACLE_HOME/DBS/TNSNAMES.ORA}) is proper.

　

2.Check it is according to the SQLNET.ORA file located at

({ORACLE_HOME/DBS/SQLNET.ORA})

　

#NAMES.DEFAULT_DOMAIN = VALUE

　

Note: here NAMES stand for instance_name in TNSNAMES.ORA


IF
NAMES.DEFAULT_DOMAIN = VALUE (IS UNCOMMENTED)

(like NAMES.DEFAULT_DOMAIN = VALUE)


THEN
instance_name look like NAMES.VALUE in TNSNAMES.ORA


IF
NAMES.DEFAULT_DOMAIN = VALUE (IS COMMENTED)

(like #NAMES.DEFAULT_DOMAIN = VALUE)


THEN
instance_name look like NAMES in TNSNAMES.ORA

　

EXAMPLE


1.SQLNET.ORA file entries


　
# SQLNET.ORA Network Configuration File: /u01/app/oracle/product/9.2.0/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

　

#NAMES.DEFAULT_DOMAIN = test.in

　

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

　

Note:( here test.in is DEFAULT_DOMAIN) WHICH IS COMMENTED HERE .

　

　

TNSNAMES.ORA file entries


testdb=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = db.test.in)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test)

)

)

　

Note: because of

#NAMES.DEFAULT_DOMAIN = test.in

is commented thats why in TNSNAMES.ORA INSTANCE_NAME IS test

　



2 SQLNET.ORA file entries

　

# SQLNET.ORA Network Configuration File: /u01/app/oracle/product/9.2.0/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

　

NAMES.DEFAULT_DOMAIN = test.in

　

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

　

Note:( here test.in is DEFAULT_DOMAIN) WHICH IS UNCOMMENTED HERE .




TNSNAMES.ORA file entries

　

testdb.test.in=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = db.test.in)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test)

)

)

　

Note: because of

#NAMES.DEFAULT_DOMAIN = test.in

is uncommented thats why in TNSNAMES.ORA INSTANCE_NAME IS test.test.in.

ORACLE DB:MIGRATING DATABASE FROM WINDOW OS TO LINUX OS

MIGRATING DATABASE FROM WINDOW OS TO LINUX RED HAT ENTERPRISE LINUX OS

1. INSTALL RHEL RED HAT ENTERPRISE LINUX    (here we are using RHEL4 EDITION).
2. INSTALL ORACLE ON LINUX OPERATING SYSTEM (here we using ORACLE 9i (9.2.0.4.0))
   (Note :
   some times during installation of oracle on linux when setup start creating DBCA it
   Dissappears and DBCA not fully installed and when you trying to create database through DBCA
   it shows some error related to segmentation fault then use COMMAND
   
   
   export THREADS_FLAG =native
   echo $THREADS_FLAG
   O/P: native)
   
   
   This error can be presolved by mentioning in BASH_PROFILE file when you are creating other parameters in BASH_PROFILE file.
3. CREATE ORACLE DATABASE
        There are two ways to create database on oracle
   (i) MANUALLY DATABASE CREATION.
   See article (Oracle DB: Manually Database Creation in Linux )
   
   
   OR
   
   
   (ii) WITH THE HELP OF DBCA (DATABASE CONFIGURATION ASSISTANT)
   FOR THIS
   (a) TYPE DBCA ON COMMAND LINE AND IT WILL OPEN THE DBCA CONFIGURATION PROMPT AND FOLLOW THE INSTRUCTIONS ACCORDING TO YOUR REQUIREMENT OF DATABASE.
   (b) IF YOU WANT TO CHANGE LOCATION OF YOUR DATAFILES THEN GO FOR THE OPTION NEW DATABASE IN DBCA.
   AND CREATE THE DATABASE.

            IMPORTANT NOTE ABOUT DATABASE CREATION

1. IF YOU WANT TO TAKE BETTER PERFORMANCE FROM DATABASE THEN ALLOCATE YOUR MAJOR DATABASE USERS ON DIFFERENT TABLESPACES
2. CREATE DIFFERENT TABLESPACES FOR INDEXES (FOR MAJOR USERS OF DATABASE).
3. PLACE DATA TABLESPACE SAND INDEX TABLESPACES ON DIFFERENT HARD DISKS.

STEP RELATED TO IMPORTING DATABASE

         4.   AFTER CREATION OF DATABASE IMPORT DATABASE ON NEWLY CREATED DATABASE  WITH THE COMMAND

                                       

                                          IMP 'USERNAME/PASSWORD@DATABASE_NAME'

                                          LOG=IMPORT_LOG.TXT(ANY VALID NAME) FILE=(EXPORT FILE ADDRESS)

                                         OR ANY OTHER IMPORT UTILITY COMMANDS COMBINATION

         5.   AFTER IMPORTING DUMP INTO NEWLY CREATED DATABASE CREATE NEW USERS THEIR PASSWORDS CHANGE ANY EXISTING

                     PARAMETERS WHICH YOU FORGET AT CREATION TIME OR WANT TO ADD NEW PARAMETERS.

                    (Note:

                               when we import our database some time it invalidate the

                               indexes so validating it use SQL query.

                              (ALTER INDEX INDEX_NAME REBUILD)

                              OR

                               validate it with the help of TOAD(right click on index and click rebuild )

           6. MIGRATION OF ORACLE DATABASE FROM WINDOWS OS TO LINUX OS

                   IS COMPLETE.

How To Create Statspack Report

STATSPACK RUNNING PROCEDURE

Conn As Perfstat User
As
exec statspack.snap

Again Execute Statspack’s Snap Prodedure After Some Time (e.g 1 hour or 2 hours Whenever There Is So Much Transactions Are RunningASexec statspack.snap
　

NOTE: Here We Execute Statspack Procedure Two Times Which Will Create Two Point Of Time Images of Database .In Which We Are Going To Run Statspack Report For Finding out The Database Picture .

　
Now Run Statspack ReportAs@?/rdbms/admin/spreport
and then press ENTER
This Will Create SPREPORT

Dataguard Configuration Steps (Disaster Recovery)

DATAGUARD STEPS TESTING
Configuring the Primary Database
Enable archiving and define a local archiving destination
1. PRIMARY DATABASE SHOULD BE IN ARCHIVE MODE.
IF NOT
THEN
sql>ALTER SYSTEM ARCHIVE LOG START.

Enable forced logging

2. PRIMARY DATABASE SHOULD BE IN FORCE LOGGING MODE.
IF NOT
THEN
sql>ALTER DATABASE FORCE LOGGING

Configuring the Oracle networking files

3. ENTER THE TNS ENTRIES IN TNS FILES.FOR PRIMARY AND STANDBY DATABASE.

Set initialization parameters on the primary database

4. SET PARAMETERS FILE PARAMETERS CAREFULLY
FAL_CLIENT (IF YOU ARE ON PRIMARY THEN
PRIMARY DATABASE NAME HERE OTHERWISE STANDBY)
FAL_SERVER SAME AS PREVIOUS
LOG_ARCHIVE_DEST_2 THIS PARAMETER SHOULD BE SET ENABLE ON
PRIMARY AND DEFER ON STANDBY
LOG_ARCHIVE_DEST_STATE_2 ‘service=STANDBY DATABASE NAME
optional reopen=15′
Take cold backup of the primary database

5.TAKE COLD BACKUP AND TRANSFER THESE FILES TO STANDBY DATABASE.
Create a controlfile for the standby database

6.OPEN PRIMARY AFTER COLD BACKUP AND CREATE STANDBY DATABASE
CONTROL FILE AND PARAMETER FILE.

alter database create standby controlfile as ‘/u01/app/oracle/control01.ctl’;
create pfile=’/u01/app/oracle/initTESTDB.ora’ from spfile;
Copy all files from the primary host to the standby host

7. COPY STANDBY CONTROL FILE AND PARAMETER FILE ALSO AT STANDBY

Configuring the Standby Database
Configuring the Oracle networking files

1.ENTER THE TNS ENTRIES IN TNS FILES.FOR PRIMARY AND STANDBY DATABASE.
Create a server parameter file for the standby database
IN PARAMETER FILE AT STANDBY
FAL_CLIENT ((IF YOU ARE ON PRIMARY THEN PRIMARY
DATABASE NAME HERE OTHERWISE STANDBY))
FAL_SERVER(SAME)
LOG_ARCHIVE_DEST_
LOG_ARCHIVE_DEST_STATE_2

Copy the standby controlfile to the appropriate location

2. COPY CONTROL FILES COME FROM PRIMARY DATABASE
ACCORDING TO PARAMETER FILE LOCATION.

Create Oracle Password File

3. CREATE PASSWORD FILE.
ORAPWD FILE=PATH PASSWORD=PASSWORD
Start The Physical Standby Database

4. START STANDBY ON NOMOUNT

Mount The Standby Database

5. AND MOUNT IT WITH
ALTER DATABASE MOUNT STANDBY DATABASE

Initiate log apply services

6. INITIATE REDO APPLY BY
alter database recover managed standby database disconnect from
session;
Start Remote Archiving

1. ON PRIMARY issue

alter system archive log current

Verifying the Physical Standby Database

1. use v$archived_log table (archived,applied,sequence# columns).

2. SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

High Water Mark

1.     High water mark is the point upto which data is stored in table as block

2      IT Increases upward when you insert data

3.     It decreases downward when you truncate or drop the table not in the case of delete.

Example
A table containing 225 rows with total of 9 blocks in which 6 blocks are empty.Analyse table test1 compute statistics.
Select blocks, empty_blocks, num_rows
From user_tables
Where table_name = ‘test1′;
After deleting some data it is still using same number of blocks.
Alter table test1 move.
Analyse table test1 compute statistics.
Now it frees the blocks in which data is deleted and hwm is decreases downward to this when full table scan is performed then it reads blocks up to hwm which is less this time and it will decreases execution time.

Database Cloning From Cold Backup

1. SHUTDOWN SOURCE DATABASE CLEANLY BYSHUTDOWN IMMEDIATE;
 
2. COPY ALL SOURCE DATABASE DATAFILES TO NEW LOCATION OF DUPLICATE DATABASE EXCEPT CONTROL FILES.
 
3. CREATE BDUMP,UDUMP,CDUMP FOLDERS IN WINDOWS
ORACLE_BASE/ADMIN/DATABASENAME_FOLDER/ {CDUMP,UDUMP,BDUMP}
IN LINUX$ORACLE_BASE/ADMIN/DATABASENAME_FOLDER/ {CDUMP,UDUMP,BDUMP}
 
4. STARTUP SOURCE DATABASE.
 
5. CREATE PFILE WITH NEW SID LIKE create pfile=’location’ from spfile.
 
6. MODIFY PFILE PARAMETERS OF DUPLICAT E DATABASE ACCORDING TO DUPLICATE DATABASE NAME AND THEIR FILES LOCATIONS.
 
7. CREATE CONTROL FILE USINGALTER DATABASE BACKUP CONTROLFILE TO TRACE AS "LOCATION"
 
8. CHANGE CONTROL FILE PARAMETERS i.e. REUSE BY SET
NORESETLOGS BY RESETLOGS.
SOURCE DATABASE DATAFILES LOCATIONS WITH DUPLICATE
DATABASE DATAFILES LOCATIONS.
　 
NOTE : IF YOU WANT TO USE FEW TABLESPACES OF SOURCE DATABASE
THEN DONT USE ALL DATAFILES JUST USE DATAFILES RELATED TO THOSE TABLESPACES.
　 
9. CREATE NEW SID USING ORADIM -NEW -SID STANDBYON COMMAND PROMPT(AT ORACLE_HOME/DATABASE/INITDATABASE_NAME.ORA)
CREATE PASSWORD FILE FOR DUPLICATE DATABASE BY ORAPWD FILE=’LOCATION’ PASSWORD={PASSWORD} (SAME LOCATION AS PARAMETER FILE )
 
10. (AT ORACLE_HOME/DATABASE/ORAPWDATABASE_NAME.ORA)
　 
11. GIVE ENTRY IN TNSNAMES.ORA AND LISTENER.ORA FILES OF DUPLICATE DATABASE.
　 
12. CONNECT TO INSTANCE OF DUPLICATE DATABASE.
LIKE: SQLPLUS "SYS AS SYSDBA"
　 
13. STARTUP NOMOUNT USING PARAMETER FILE LOCATIONBECAUSE AT NOMOUNT PFILE READED.LIKE : STARTUP NOMOUNT;
　 
14. CREATE CONTROL FILE BY SCRIPT WHERE NEW CONTOL FILE LOCATED.LIKE : CONTROL FILE IS AT C:\DB\CONTROL.CTL
THEN ON SQL PROMPT @C:\DB\CONTROL.CTL;
　 
15. ALTER DATABASE OPEN RESETLOGS
 
• CLONED DATABASE IS READY TO USE.

Database Cloning Through RMAN(Recovery Manager)

Clone an Oracle database using a RMAN backup

1.  
1.TAKE RMAN BACKUP OF MAIN DATABASE (BACKUP DATABASE PLUS   ARCHIVELOG)
 
2.CREATE FOLDERS OF NEW (CLONE DATABASE) AT APPROPRIATE LOCATIONS
 
3.CREATE PFILE FROM NEW DATABASE I.e. MAIN DATABASE
 
4.ADD THESE TWO PARAMETERS TO NEW DATABASE (CLONED DATABASE) PFILE.db_file_name_convert=(<source_db_path>,<target_db_path>)
 
4.log_file_name_convert=(<source_db_path>,<target_db_path>)
AND IN NEW DATABASE PFILE PARAMETER LOCATIONS SHOULD BE ACCORDING TO NEW DATABASE FILE LOCATIONS NOT ACCORDING TO MAIN DATABASE.
 
5.AT CLONING TIME NEW DATABASE SHOULD BE AT NOMOUNT AND MAIN DATABASE SHOULD BE AT MOUNT OR OPEN.
 
6.AT COMMAND PROMPTRMAN TARGET SYS/MANAGER@MAIN AUXILIARY SYS/MANAGER@CLONENOTE:–
TARGET DATABASE IS MAIN
 
7.AUXILIARY DATABASE IS CLONE DATABASE
 
8.AT RMAN PROMPT ISSUE
DUPLICATE TARGET DATABASE TO "CLONE"
MONITOR THE CLONED PROCESS.
 
CLONING OF DATABASE THROUGH RMAN IS DONE.

Change The Database Into Archive Log Mode

Change The Database Into Archive Log Mode

STEPS ARE:–

1.CHECK THE DATABASE ARCHIVE LOG MODE BY

ARCHIVE LOG LIST

2.FOR ENABLING ARCHIVE MODE.

ALTER DATABASE ARCHIVE LOG

3. FOR ENABLING ARCHIVAL OF ARCHIVE LOG AUTOMATICALLY.

ALTER SYSTEM ARCHIVE LOG START.

4.FOR DISABLING ARCHIVAL OF ARCHIVE LOG AUTOMATICALLY.

ALTER DATABASE ARCHIVE LOG STOP.

5.AND CHECK THE ARCHIVE PARAMTERS BY

ARCHIVE LOG LIST

Oracle Database::Manually Database Creation In Linux

ORACLE DATABASE::MANUALLY DATABASE CREATION IN LINUX

THROUGH VI EDITOR

1.FIRST CREATE THE PARAMETER FILE (PFILE) IN $ORACLE_HOME/DBS/INIT{SID}.ORA .
Which Defines

(i) core dump dest location $ORACLE_BASE/ADMIN/{SID NAME FOLDER}/CDUMP

(ii) user dump dest location $ORACLE_BASE/ADMIN/{SID NAME FOLDER}/UDUMP

(iii) background dump dest location $ORACLE_BASE/ADMIN/{SID NAME FOLDER}/BDUMP

(iv) db_name

(v) control_file(s) location $ORACLE_BASE/ORADATA/{SID NAMEFOLDER}/.CTL(FILE(S))

(vi) undo_management parameter

(vii) compatibility parameter


2. CREATE THE BDUMP,CDUMP,UDUMP FOLDERS IN $ORACLE_BASE/ADMIN/ .

3. EXPORT ORACLE SID
Like

export oracle_sid={SID NAME}


4. SET THE TNSNAMES AND LISTENER FILE PROPERLY.
THROUGH SQLPLUS


5. STARTUP NOMOUNT WITH PFILE LOCATED AT $ORACLE_HOME/DBS/INIT{SID}.ORA .

6. CREATE DATABASE SCRIPT
Which includes

(i) datafile address and size $ORACLE_BASE/ORADATA/{SID NAME FOLDER}/

(ii) redo log groups(2) at $ORACLE_BASE/ORADATA/{SID NAME FOLDER}/

(iii) undo tablespace address and size $ORACLE_BASE/ORADATA/{SID NAME FOLDER}/

(iv) temporary tablespace file address and size $ORACLE_BASE/ORADATA/{SID NAME FOLDER}/


7.RUN THE CATPROC AND CATALOG PATCHES FROM $ORACLE_HOME/RDBMS/ADMIN FOLDER
Like @?/rdbms/admin/catproc.sql

And @?/rdbms/admin/catalog.sql


8.MOUNT THE DATABASE
alter database mount


9.OPEN THE DATABASE
alter database open


10.DATABASE IS READY FOR WORK
Done !!