Oracle Database Release

Oracle Database Release • 10.2.0.4.0 – 10: Major database release number – 2: Database maintenance release number – 0: Application Server Release Number – 4: Component-Specific Release Number – 0: Platform-Specific Release Number

Oracle Installation • • • • • •

Download from MetaLink. Unzip. Install Using Oracle Universal Installer. Use of DBCA / DBUA. SGA/PGA/Redo Log Settings. Other Parameter Settings.

Creating an Oracle Database • Use Database Configuration Assistance (DBCA).

Database Architecture • • • • • •

Control files Data files Online redo log files Parameter file Password file Archive log files

Starting Up and Shutting Down • Startup – Startup nomount: • Load spfile • Create oracle instance.

– Why ? • To edit control file (restore/recreate). • To use DBCA (to create database).

– Startup mount: • Load spfile. • Create oracle instance. • Opens control file.

– Why ? • For recovery. • To change database options.

Starting Up and Shutting Down • Startup – Startup Open • • • •

Load spfile Create oracle instance. Opens control file. Based on control file make all datafile and redo log file to Online.

– Why? • To make database available to database user.

– Can be open as nomount  mount  open using alter database but not like open  mount  nomount. – Here startup; = startup open;

Starting Up and Shutting Down • Shutdown – shutdown normal/immediate/transactional/abort. – Normal: • • • •

No new connection will be allowed. All existing user should logged out. Check pointing occurs. No data recovery required in next startup.

– Immediate: • • • • •

No new connection will be allowed. Forcefully disconnects all existing users. Rollback all users transactions. Checkpoint occurs. Dirty buffer writes into datafile.

Starting Up and Shutting Down • Shutdown – Transactional: • No new connection will be allowed. • No new transaction will allow. • Waits all running transaction and disconnects the users. • Checkpoint occurs.

– Abort: • Instance close abnormally. • Crash recovery occurs in next startup.

– Startup force: • Shutdown abort. • Startup open.

Oracle Database Processes Server Process

Server Process

Server Process

System Global Area

System Monitor SMON

Process Monitor PMON

Database Writer DBWn

Check Point CKPT

Background Processes

Log Writer LGWR

Archiver ARCn

Oracle Server Structure (Instance/Database) • Oracle Instance – Oracle Memory Structure • SGA: System/Shared Global Area(Shared by all DB users, server and background process). • PGA: Private/Process/Program Global Area(private to each server and background process, one PGA for each process).

– Oracle Background Process • DBWn: writes modified blocks from the database buffer cache to the files on disk. • LGWR: writes redo log entries to disk. • PMON: performs process cleanup when a user process fails. • SMON: performs crash recovery when the instance restart after failure. • CKPT: Signals DBWn at checkpoints and updates all of the data files and control files of the database to indicate the most recent checkpoint.

Oracle Instance • DBWn(Database Writer) – Upto 10 DBWn can run like DBW0, DBW1, DBW2………DBW9. – DBW writes dirty buffers of DB buffer cache into data file after every check pointing.

• LGWR(Log Writer) – Log buffer writes into redo log when (which ever occurs first): • • • • •

At every commit. At every 3 seconds. Every 1MB full of log buffer. When 1/3 full of log buffer size. Every check point process occur before DBWn writes data into data file.

Oracle Instance • SMON(System Monitor) – Does instance crash recovery: automatically starts crash recovery next time when database starts. – During crash recovery: • Roll forward – from the last check pointing all data writes into data file both committed and uncommitted. • Roll Backward: uncommitted transactions rolls back.

– SCN(system change number) assigns for Committed transaction.

• PMON(Process Monitor) – Handle and Monitor all server processes when abnormal user termination occurs all user transaction recovery will be handled by PMON.

alter system set db_file_multiblock_read_count=128 scope=both; alter system set sga_max_size=60% of RAM scope=spfile; alter system set sga_target=60% of RAM scope=spfile; alter system set pga_aggregate_target=30% scope=spfile; alter system scope=both;

set

"_gby_hash_aggregation_enabled"=false

alter system set optimizer_features_enable="10.1.0" scope=both;