Oracle Architecture Part 3

No Comments

[embedplusvideo height=”356″ width=”584″ standard=”http://www.youtube.com/v/L_2TfSHotsY?fs=1″ vars=”ytid=L_2TfSHotsY&width=584&height=356&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep6822″ /]

Watch the rest of this series:

Part 1

Part 2

Part 4

Notes:

Initilisation params for the Buffer cache:

  • db_cache_size
  • db_block_size
  • It is very helpfull if the buffer block size is a multiple of your operating system block size.

The Buffer cache is controled with a Least Recently Used (LRU) algorithm which determines what stays in and what gets removed from the SGA.

The goal being to have the data that is accesses most frequently kept in the SGA so that it can be accessed fast.

Shared pool.

The purpose of the Shared pool is to reduce parsing

The Shared pool consists of 2 parts:

  • Library Cache
    • text of sql statements
    • meta code – compiled version of above text
    • execution plan that the optimiser will use
    • Views:
      • v$sqlarea
      • v$sql
  • Dictionary Cache
    • names of objects that have been touched recently
    • privalages and roles – access information

The Shared Pool also usesa  LRU algorithm.

Initialization parameter – shared_pool_size controls size of shared pool.

 

Java Pool

  • Initilization parameter – java_pool_size
  • large pool – optional
  • if using RMAN configure a large large pool
  • shared server uses this

 

Watch Part 4

Oracle Architecture Part 4

No Comments

[embedplusvideo height=”328″ width=”584″ standard=”http://www.youtube.com/v/nNGLyWIPPTQ?fs=1″ vars=”ytid=nNGLyWIPPTQ&width=584&height=328&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep8443″ /]

Watch the earlier parts first:

Part 1

Part 2

Part 3

 

Notes:

Oracle Processes

 

Dbwr

Database Writer

  • Writes from the SGA to the data files
  • Does deffered writes
  • like a hotel room clerk
  • kicks blocks out to disk when new data needs free blocks

 

Lgwr

Log Writer

  • writes info to the current group of redo logs

Smon

System monitor

  • very important process
  • performs instance recovery
  • cleans up after sorts

 

Pmon

Process monitor

  • Monitors Processes
  • detects – deadlock detected for example

 

Ckpt

Checkpoint process

one big reason for checkpoints is that commonly accessed
data may never get written out to disk. A checkpoint forces
this to get written.

Oracle Instance

– is the SGA and background processes

 

Oracle database and the Oracle instance combined is called the Oracle Server.

You can have multiple instances associated with the same database, this is called RAC.

Dedicated server process connects through the PGA.

Categories: Oracle Basics, Video Tags: Tags: , , , , , ,

Oracle Architecture Part 2

No Comments

[embedplusvideo height=”328″ width=”584″ standard=”http://www.youtube.com/v/wmc9uxNW99E?fs=1″ vars=”ytid=wmc9uxNW99E&width=584&height=328&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep5503″ /]

Check out part 1 first.

Notes:

Online Redo Logs

  • Contains Redo and Undo info
  • DML
  • Inserts/Deletes
  • DDL – Create alter drop
  • Commits
  • Need at least 2 files/groups
  • Should be multiplexed each on different devices and controlers
  • When each file fils, next one is started and old one is archived (if in Archivelogmode)

An Oracle database is comprised of datafiles, control files
and redo logs.

The second part is the memory – the SGA (system global area
or shared global memory)

Database Buffer Cache

Comprised of Oracle data blocks

There are 4 main types of data blocks, which are the same as the 4 types of data mentioned earlier:

  • Data
  • Index
  • Rollback
  • Temporary

Part 3…

Categories: Oracle Basics, Video Tags: Tags: , , , , , ,

Oracle Architecture Part 1

No Comments

[embedplusvideo height=”328″ width=”584″ standard=”http://www.youtube.com/v/addFo5FMzkg?fs=1″ vars=”ytid=addFo5FMzkg&width=584&height=328&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep3446″ /]

There are 3 essential elements of any database system

  • Datafiles – storage
  • Memory – in oracle its the SGA
  • Processes – To operate on the data files and memory

Storage

  • Datafiles
  • Contains data
  • Tables
  • Indexes
  • Undo segments (used to be rollback segments)
  • Temporary files
  • Oracle is easy as DIRT – Data, Index, Rolback, Temp
  • In Oracle you can now have around 65000 datafiles

Important views for the DBA

  • dba_data_files
  • v$dbfile
  • v$datafile

Control files.

Should have 3 identical control files on different disks and controllers

They contain information about the database

  • db name
  • created date
  • path to data files
  • check point info

Important Views

  • v$controlfile

Watch Part 2…

Resizing the Temp tablespace in Oracle

No Comments

In much the same way as with the Undo tablespace, you may want to reduce the size of the Temp tablespace in Oracle to reclaim space.

 

11g

—-
ALTER TABLESPACE [tbsp_name] SHRINK SPACE KEEP 30M;

10g and earlier

——————

Unfortunatly, if the temp tablespace is full (as will be the case if it has been expanding) you will not be able to reduce he size directly. Also, restarting the database will not clear out the data.

The process or doing this is similar to that of reducing the size of the undo tablespace.

——

1. First, find the location of the temp tablespace datafile:

select * from dba_temp_files;

If there is more than one, you can check which one is the default with the following query:

select * from database_properties
where property_name like ‘%TABLESPACE’;

2. Next, generate a script to create the existing temp tablespace:

SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’, ‘TEMP’) from dual;

i.e.
CREATE TEMPORARY TABLESPACE “TEMP” TEMPFILE
‘/TESTDB/oradata/TESTDB/temp01.dbf’ SIZE 104857600
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

save this.

3. Use that script to create a new temporary tablespace. Alter the tablespace anme, file name and size as appropriate.

You only need to create a small file which you will be deleting.

CREATE TEMPORARY TABLESPACE “TEMP2” TEMPFILE ‘/TESTDB/oradata/TESTDB/temp02.dbf’ SIZE 50M AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;

4. Make the new temporary tablespace the default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE “[Tablespace Name]”;

5. Make sure that the new tablespace is now the default:

select * from database_properties
where property_name like ‘%TABLESPACE’;

*** If Database version is < 9i see below ***

6. Drop the old tablespace:

DROP tablespace [Tablespace Name] including contents and datafiles;

7. Re-create the old tablespace using the script from step 2 – changing the size as appropriate.

8. Set the re created tablespace to be the default

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE “[Tablespace Name]”;

9. Make sure that the correct tablespace is now the default:

select * from database_properties
where property_name like ‘%TABLESPACE’;

10. Drop the other tablespace:

DROP tablespace [Tablespace Name] including contents and datafiles;

If you are using a database version below 9i then you will need to do this step before step 6:

Use the below query to identify any residual dead connections still alloctaed to the first TEMP tablespace. Dropping the TEMP tablespace (Step 7) will not complete until these dead session dissapear or are killed. The dead connections can be killed using the ALTER SYSTEM KILL command with the sid and Serial# returned from the query.

SELECT b.tablespace
, b.segfile#
, b.segblk#
, b.blocks
, a.sid
, a.serial#
, a.username
, a.osuser
, a.status
FROM v$session a
, v$sort_usage b
WHERE a.saddr = b.session_addr

Categories: Oracle Tasks Tags: Tags: ,