Chapter 2 : MANAGING AN ORACLE INSTANCE

Chapter 2 : MANAGING AN ORACLE INSTANCE

CHAPTER NO # 2

MANAGING AN ORACLE INSTANCE

INITIALIZATION PARAMETER FILES :

In order to start an instance and open the database, you must connect as SYSDBA and enter the startup command.

The oracle server will then read the initialization parameter file and prepare the instance according to the initialization parameter contained within.

For this you must have SYSDBA privileges.

Two types of parameters :

EXLICIT : Having an entry in the file (default)

IMPLICIT : Having no entry in the file, but assuming the oracle default values

Multiple initialization parameter files can exist.

Changes of entries in the file based on the type on initialization parameter file used because there are two parameter files

1- STATIC PARAMETER FILE (Text Form)

   PFILE commonly referred to as initsid.ora

2- PERSISTANT SERVER PARAMETER FILE (Binary Form)

   SPFILE commonly referred to as spfilesid.ora

INITIALIZATION PARAMETER FILE CONTENTS :

- A list of instance parameter

- The name of database the instance is associated with

- Allocation for memory structure of SGA

- What to do filled online redo log file

- The names and locations of control files

- Information about undo segments

 

PFILE :

- Text File

- Modified with an operating system editor

- Modification made manually

- The pfile is read only during instance startup. (Changes take effect on next startup)

- Default location C:\oracle\admin\orcl\pfile\initsid.ora

SPFILE :

- Binary File

- Maintained by the oracle server

- Always resides on the server side

- Ability to make changes persistent across SHUTDOWN and STARTUP

- Can self-tune parameter values

- Can have RMAN support for backup the initialization parameter

STARTUP COMMAND BEHAVIOUR

CREATE SPFILE FROM PFILE

PRACTICAL #

Create SPFILE = 'd:\oracle\ora92\database\spfileorcl.ora'

from pfile = 'd:\oracle\admn\orcl\pfile\initorcl.ora';

THis work also be done without defining address and shutdown stage

Create spfile from pfile ;

PRACTICAL #

Modification can take place in SPFILE through

Alter system command

Alter system set undo_tablespace = undo2;

or

Alter system set undo_tablespace = undo2

scope = both;

show parameter undo_suppress_errors;

alter system set undo_suppress_errors = true

scope = both;

show parameter undo_suppress_errors;

ORDER OF PRECEDENCE :

• When the command startup is used, the spfileSID.ora on the server side is used to start up the instance.

            If

• The spfileSID.ora is not found the default SPFILE on the server side is used to start up the instance.

            If

• The default SPFILE is not found, the initSID.ora on the server side will be used to start up the instance.

A specified PFILE can override the use of the default SPFILE to start the instance.

A PFILE can optionally contain a definition to indicate use of an SPFILE.

This is the only way to start the instance with an SPFILE in a nondefault location.

STARTING UP THE DATABASE :

There are 4 stages to startup and shutdown the database

When startup database

Shutdown --> nomount --> mount --> open

AT NOMOUNT STAGE :

An instance would be started in the nomount stage only during

Database creation or the

Re-creation of control files.

- Reading initialization parameter file

          - First spfileSID.ora

          - if not found then, spfile.ora

          - If not found then, initsid.ora

     specifying the PFILE parameter with STARTUP overrides the default behavior.

- Allocating SGA

- Starting the background Process

- opening the alertSID.log files and trace files

AT MOUNT STAGE :

- Renaming data files

- Performing full Database Recovery

- Associating a database with instance start at nomount stage.

- Reading the control file to obtain the name and destination of

  DATA FILES AND ONLINE REDO LOG FILES

AT OPEN STAGE :

- Open online data files

- Open online redo log files

STARTUP COMMAND --> use to start database

Alter database command use to move database from nomount to mount or mount to open

PRACTICAL #

For start instance at normal or open stage

Use command :   SQL >  startup ;

To start the instance with pfile

SQL > startup pfile ='d:\oracle\admin\oracl\pfile\initorcl.ora';

to move the database form NOMOUNT TO MOUNT or from

                                           NOMOUNT TO OPEN  use

ALTER DATABASE COMMAND 

SQL > Alter database mount

SQL > Alter database open

OPENING A DATABASE IN RESTRICTRED MODE :

A restricted session is useful when you perform structure maintenance or a database import and export.

Then database can be started in restricted mode so that it is available only to users having restricted session privileges.

This can be done in two ways

1- Startup restrict;

or

2- After database open

   Alter system enable restricted session;

After placing an instance in restricted mode, you may want to kill all current users before performing administrative tasks

This can be done by the following

ALTER SYSTEM KILL SESSION 'integer1,integer2'

where

integer1: value of the SID column in the V$SESSION view

integer2: value of the SERIAL# column in the V$SESSION view

PRACTICAL #

STARTUP RESTRICT;

OR

After database open

ALTER SYSTEM ENABLE RESTRICTED SESSION ;  THEN  ALTER SYSTEM DISABLE RESTRICTED SESSION;

TERMINATE SESSION

ALTER SYSTEM KILL SESSION 'integer1,integer2'

STEPS :

Create user rr identified by rr

grant connect, resource to rr;

create user abc identified by abc

grant connect, resource to abc;

open rr , abc, and scott session

than sys/dba session

select username, sid, serial# from v$session;

then

alter system kill session '12,34';

Effects of terminating session :

Alter system kill session command causes the backgrounf process PMON to perform the following steps

1- Rollback the user current transactions

2- Release all currently held table or row lock

3- Free all resources currently reserved by the user

OPENING A DATABASE IN READ ONLY MODE

To prevent data from being modified by user transactions database can be open at read only mode.

Alter database open read only;

In this mode of database no DDL, DML operation perform

READ ONLY session can be used :

- Execute query

- Execute disk sort using locally manage tablespaces

- take data files offline and online , but not tablespaces

- perform recovery of offline data files and tablespaces.

SHUTDOWN THE DATABASE :

There are 4 ways to shutdown database

1- SHUTDOWN NORMAL :

   - No new connection can be made

   - Oracle server waits all user to disconnect

   - Database and redo buffer written to the disk

   - Background process terminated and SGA remove from memory

   - Oracle closes and dismount database before shutdown

   - Next startup doesn’t require recovery

2- SHUTDOWN TRANSACTIONAL :

   - No new connection can be made

   - User automatically disconnect after completing the transaction in progress

   - When all transaction finished shutdown occur immediately

   - Next startup does not require recovery

3- SHUTDOWM IMMEDIATE :

   - Current SQL statement being processed is not completed

   - oracle server does not wait for the user who are currently connected to the database

   - Oracle closes and dismount database before shutdown.

   - next startup does not require recovery

4- SHUTDOWN ABORT :

   - Oracle does not wait for user currently connected to the database

   - Database and redo buffers are not written to the disks

   - The instance terminated without closing the files

   - The database is not close or dismounts

   - Next startup require instance recovery.

MONITORING AN INSTANCE USING DIGNOSTIC FILES :

DIGNOSTIC FILES :

- Contain information about significant events encountered

- used to resolve problem

- used to better manage the database on day-to-day basis.

Several Diagnostic files are exist:

1- alertSID.log file

2- Background trace file

3- User trace files

ALERT LOG FILES :

Each oracle instance has an alert log file. if not already created, it is created during instance startup.

The file must manage by DBA

It continuous grow while the database continuous to work

Alert log file should be the first place you look when diagnostic day-to-day operations or errors.

The alert file keeps a record of the following information 

 - When the database was started and shutdown

 - A list of non default initialization parameters

 - The startup background process

 - The thread used by the instance

 - The log sequence number LGWR is writing to

 - Information regarding to log switch

 - Creation of tablespace and undo segments

 - Alter statement that have been issued

 - Information regarding error massages such as ORA-600 and extent errors.

The alertSID.log location is defined by the BACKGROUND_DUMP_DESTinitialization parameter.

BACKGROUND TRACE FILES :

Background trace files are used to log errors that have been encountered by a background process, such as

PMON, SMON, DBWN and other background process

We use these files to diagnose and troubleshoot problems.

Its location is defined by the BACKGROUND_DUMP_DEST initialization parameter.

USER TRACE FILES :

Produced by the user process

Can be generated by server process

Contain statistics for traced SQL statements

Contain user error massages

Location is defined by USER_DUMP_DEST

ENABLING OR DISABLING USER TRACING

-- SESSION LEVEL

Using the alter session command

ALTER SESSION SET SQL_TRACE = TRUE   OR

EXECUTE DBMS PROCEDURE

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION

-- INSTANCE LEVEL

Setting the initialization parameter

SQL_TRACE = TRUE

--------------------------------  END OF CHAPTER 02  -----------------------------