Oracle DBA

(1)

ORACLE DBA BEGINNER LEVEL

TRAINING

05/01/2012

TABLE OF CONTENTS 1. Oracle Architecture

Oracle database and instance Archive log mode in Oracle Oracle Storage structures 2. Parameter files (spfile and pfile)

3. Dictionary views

4. Other Important DBA Queries . 5. Shutdown options in Oracle. 6. Basic Unix Commands for DBA.

7.Oracle Shared Server Vs Dedicated Server Architecture. Domain : Life Sciences and Health Care.

Author : Alamuru Prasadu Reddy.

(2)

Title of the Document

ORACLE ARCHITECTURE

ORACLE DATABASE AND INSTANCE

The database is the set of files stored on disk where as Instance (SGA+ PGA+ Process) is the collection of oracle background processes and memory. An instance can mount and open one and only one database whereas database can be mounted and opened by one or more instances.

Multiple instances can run concurrently on the same computer, each accessing its own physical database. In large-scale cluster systems, Oracle Real Application Clusters enables multiple instances to mount a single database.

(3)

BACKGROUND PROCESSES :

Following are the 5 mandatory Background processes : Data Base Writer Process (DBWn):

Database Writer writes from database buffer cache to datafile. Database Writer writes the dirty buffer (modified) and cold buffer (which are not recently used) to the datafile.

Database writer writes contents to the datafile when,

• Server process is not able to find the free buffers after searching a threshold No. of dirty buffers.

• DBWn periodically writes buffer to advance the checkpoint, which is the position in the redo log from where the instance recovery begins.

Log Writer Process (LGWR):

It is responsible for writing the contents of Redo log buffer to a redo log file on the disk. Redo is nothing but the information of changes made to the database.

LGWR writes one contiguous portion of the buffer to disk. LGWR writes:

● A commit record when a user process commits a transaction. ● Redo log buffers

o Every three seconds.

o When the redo log buffer is one-third full.

(4)

Checkpoint Process (CKPT):

When a checkpoint occurs, Oracle must update the headers of all data files to record the details of the checkpoint .It also increments the SCN which acts as the starting point during instance recovery. SCN is the System Change Number that is assigned

(& incremented) every time someone commits

(i.e. transaction completes) and acts as the internal timestamp For Oracle. This is done by the CKPT process.

System Monitor Process (SMON):

The system monitor process (SMON) performs instance recovery, if necessary (when the system shutdowns due to power cut or due the abrupt shut down of system), at instance startup.

SMON is responsible for cleaning up of temporary segments that are no longer in use and coalescing contiguous free extents within dictionary managed table space.

If any terminated transactions are skipped during instance recovery it recovers them when the table space is brought back online.

Process Monitor Process (PMON):

The process monitor performs process recovery when a user process fails or terminates abnormally. PMON is responsible for cleaning up the database buffer cache for failed /abnormally

terminated process and freeing resources that the user process was using.

(5)

(6)

SGA: (SYSTEM GLOBAL AREA)

The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance” (an instance is your database programs and RAM).

SGA consists of database buffer cache, Redo buffer cache, shared pool, large pool, Stream pool, and Java pool.

Database buffer cache:

The database buffer cache is the portion of the SGA that holds Copies of Data blocks read from data files. The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list.

The write list holds dirty buffers, which contain data that has been Modified but has not yet been written to disk. The LRU list holds Free buffers, pinned buffers, and dirty buffers that have not yet Been moved to the write list. Free buffers do not contain any Useful data and are available for use. Pinned buffers are currently Being accessed.

Redo Buffer Cache:

The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database by INSERT, UPDATE, DELETE, CREATE, DROP or ALTER.

Shared Pool:

The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages and control structures.

(7)

Large Pool (optional):

It is used to provide large memory allocations for:

• Session memory for the shared server and the Oracle XA interface (used where transactions

Interact with more than one database). • I/O server processes.

• Oracle backup and restore operations. Java Pool (optional):

Java pool memory is used in server memory for all session-specific Java code and data within the JVM.

Streams Pool (optional):

In a single database, we can specify that Streams of memory be allocated from a pool in the SGA called the Streams pool.

PGA: (PROGRAM GLOBAL AREA)

A program global area (PGA) is a memory region that contains data and control information for a server process. It is a non shared memory created by Oracle when a server process is started.

The PGA memory can be classified as follows:

• Private SQL Area: Private SQL area contains data such as bind information and runtime memory structures.

• Cursors and SQL Areas.

• Session Memory: Session memory is the memory allocated to hold a session's variables and other information related to the session.

(8)

(9)

ARCHIVE LOG MODE IN ORACLE

Following are the steps to be followed to configure database in archive log mode.

• Set the Archive redo log destination directories. • Shut down the database.

• Startup the database in mount mode.

• Then query “ALTER DATABASE ARCHIVE LOG” • Open the database.

Significance:

When we enable this mode redo logs will be archived before overwriting it. Database backup can be taken even when database is in Open Mode when the database mode is in archive log mode. Database can be recovered till the time of failure if have all the archived log files.

We can find out whether the database is in ARCHIVE LOG mode or NO ARCHIVELOG mode using the command .

“select log_mode from v$database”.

ORACLE STORAGE STRUCTURES

Different types of tables paces are:

• Permanent Table space (Stores Table & Index data). • Temporary Table space (Stores sorting related temporary

data).

(10)

Database Block :

The smallest unit of allocation in an Oracle database. One or more database blocks compose a database extent.

Every database block in a table space has the same number of bytes.

The different table spaces within a database can have database blocks with different sizes.

Typically, one or more rows of a table will reside in a database block, although very long rows may span several database blocks. A database block can have a size of 2KB, 4KB, 8KB, 16KB, or

32KB. Once any table space, including the SYSTEM and SYSAUX table spaces, is created with a given block size, it cannot be changed.

Data Block :

The header contains general block information, such as the block address and the type of segment (for example, data or index).

Table directory portion of the data block contains information about the table having rows in this block.

Row Directory contains information about the actual rows in the block .The data block header, table directory, and row directory are referred to collectively as overhead.

(11)

Figure: Diagram showing the different parts of the Database Block THE TABLE HIGHER WATER MARK:

For each table Oracle records the number of blocks which have ever had rows allocated to them. This includes blocks which are empty but which have previously contained rows.

This is known as the high water mark of the table. In a full table scan all blocks up to the high water mark are read.

If a table has been the subject of multiple inserts followed by deletes there may be many blocks which have few or no rows.

(12)

Hierarchy from Table space to Data Block in Oracle:

Every database must consists of one or more table spaces.

Every table space must consist of one or more data files and every table space must belong to one and only database.

Every data file must consist of one or more operating system blocks. Each operating system block may belong to one and only data file.

A table space may contain one or more segments and every segment must exist in one and only table space.

A segment must consists of one or more extents and each extent must belong to one and only one segment Stores Undo data. Each extent must be located in one and only data file the space in

the data file may be allocated to one or more extents.

Every oracle block must consist of one or more OS block and every OS block may be a part of one and only one oracle block.

(13)

Figure : Segments, Extents, and Data Blocks Within a Table space

Logical Space Management:

 Oracle Database must use logical space management to track and allocate the extents in a table space.

(14)

When a database object requires an extent, the database must have a method of finding and providing it. Similarly, when an object no longer requires an extent, the database must have a method of making the free extent available.

Oracle Database manages space within a table space based on the type that you create. You can create either of the following types of table spaces:

1) Locally managed table spaces (default):

• Locally managed tables paces the record of extent allocation is made in tables pace header.

• Locally managed table space the free and used space is stored within the bitmap structure stored in the table space’s data files.

2) Dictionary-managed table spaces:

• In dictionary managed tables paces the record of extent allocation is made in dictionary.

• In dictionary managed table space free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table .

So, Overhead of managing the space in dictionary tables is reduced in case of locally managed table space. In oracle10g, table space created will be locally managed table space by default.

(15)

PARAMETER FILES

There are two types of parameter files in Oracle.

PFILE:

A PFILE is a static, client-side text file that must be updated with a standard text editor like "note pad"or "vi".

This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine.

SPFILE:

An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command .

This means you no longer need a local copy of the pfile to start the database from a remote machine.

Use the following Syntax for Creating spfile from pfile:

Create spfile=’path/spfile’ from pfile=’path/init.ora’ Default location is “$ORACLE_HOME/database” in windows and“$ORACLE_HOME/dbs” in UNIX.

Password File :

The Oracle Password File stores passwords for users with administrative privileges. One needs to create a password files before remote administrators (like OEM) will be allowed to connect.

Default location is “$ORCALE_HOME/ database” in windows and “$ORCALE_HOME/dbs” in UNIX.

Using password file give an extended level of authentication. Using password file we can add the users Whom we want to grant the login rights.

(16)

List of important parameters (10 parameters) from parameter file in oracle

Following are the few important parameters from parameter file:

Background_dump_dest: It specifies the location for the alert.log file. background_dump_dest. specifies the directory where trace files of background processes are being written.

control_files: This parameter specifies the location of control files. db_domain: This parameter specifies the domain name of the database. db_files: This parameter indicates the maximum number of database files that can be opened for a database.

db_name: This parameter must have the same value as the database name.

log_archive_dest: This parameter specifies location of archive log files. remote_login_passwordfile: remote_login_passwordfile specifies if Oracle checks for a password file and if this password file is shared among databases.

undo_retention: Specifies for how many seconds undo information is kept.

undo_tablespace: Specifies the undo tables paces when using automatic undo management.

db_block_size: Determines the size of a database block in bytes.

sga_target: It specifies the total amount of SGA memory available to an instance

(17)

DICTIONARY VIEWS

List of some important Data Dictionary Views in Oracle.

DBA_DATA_FILES: DBA_DATA_FILES view gives the information about data file details.

DBA_TABLESPACES: This View gives information about all Table spaces in the database.

DBA_FREE_SPACE: This View gives information about the free extents in all table spaces in the database. DBA_USERS: This View gives information about all users of the database.

DBA_OBJECTS: This View gives information about all objects in the database.

DBA_SEGMENTS: This View gives information about the storage allocated for all segments in the database.

DICT: DICT is the synonym for Dictionary; it contains the descriptions of data dictionary tables and views.

DBA_INDEXES: DBA_INDEXES describes all indexes in the database. DBA_IND_COLUMNS: DBA_IND_COLUMNS describes the columns of all the indexes on all tables and clusters in the database.

ALL_SOURCE: ALL_SOURCE describes the text source of the stored objects accessible to the current user.

(18)

V$session: This view lists session information for each current session Session is identified uniquely using Sid, serial#

V$locked_Objects: This view lists locks acquired by every transaction on the system.

V$sqlarea: V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

V$sysstat: This view lists system statistics. V$sessstat: This view lists user session statistics.

DYNAMIC PERFORMANCE VIEWS:

Dynamic performance views are the views which are continuously updated while a database is open and in use . Dynamic performance views are useful in identifying instance

level performance problems.

Lifetime of the dynamic performance is same as that of instance lifetime. Dynamic performance views are also called as

(19)

Important DBA Queries which are helpful for Beginners

¾ Query to get the schema name along with the number of objects owned by each schema.

select owner, count (object_type) from dba_objects group by owner;

¾ Query to get list of all control file, datafiles, redo log file in single query.

select name from v$datafile union all select name from

v$controlfile union all select member from v$logfile ; ¾ Query to find the list of primary keys of the particular table

in Oracle.

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINT S C ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME

WHERE C.TABLE_NAME = ‘tablename’ AND C.CONSTRAINT_TYPE = ‘P’ ;

¾ Query to find the list of foreign keys of the particular table in Oracle.

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINT S C ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME

WHERE C.TABLE_NAME = ‘tablename’ AND C.CONSTRAINT_TYPE = ‘R’ ;

¾ Query to find the Duplicate records in the table.

SELECT COUNT(*) , COLUMN1, COLUMN2 FROM TABLE_NAME GROUP BY COLUMN1, COLUMN2 HAVING COUNT(*) > 1;

(20)

¾ Query to delete the Duplicate records in the table. DELETE FROM table_name A WHERE a.rowid > ANY ( SELECT B.rowid FROM table_name B WHERE A.col1 = B.col1 AND A.col2 = B.col2 );

¾ Query to find the nth Highest salary of a Employee of a Table.

Select * from epm A where &(n-1) = ( select count(*) from emp B where A.sal < B.sal) ;

¾ Query to find out the free table space in Oracle .

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb from (select tablespace_name, round(sum(bytes)/1024/1024 ,2 ) as free_space

from dba_free_space

group by tablespace_name) a,

(select tablespace_name, sum(bytes)/1024/1024 as tbs_size from dba_data_files

group by tablespace_name) b

where a.tablespace_name(+)=b.tablespace_name; ¾ Query to find out the Invalid objects in Oracle .

SELECT owner, object_type, object_name, status

FROM dba_objects WHERE status = 'INVALID'ORDER BY owner, object_type, object_name;

¾ Query to find out the logmode of the database in Oracle . Select log_mode from the V$database ;

¾ Query to find out the which version of the database you are working in Oracle .

(21)

¾ Query to find out the ACL list in Oracle 11g . SELECT host, lower_port, upper_port, acl FROM dba_network_acls;

¾ Query to find out the usage of the Temorery table space in Oracle .

SELECT * from dba_temp_free_space ;

¾ Query to find the all the Database links in database of the oracle.

SELECT owner, db_link, username, host

FROM dba_db_links ORDER BY owner, db_link;

¾ Query to find the list of the Parameter values in Oracle .

SELECT p.name, p.type, p.value, p.isses_modifiable, p.issys_modifiable, p.isinstance_modifiable

FROM v$parameter p ORDER BY p.name;

¾ Query to find out general information about the database . SELECT * FROM v$database;

SELECT * FROM v$instance; SELECT * FROM v$version;

¾ Query to find out all the jobs & which jobs are currently running in the database .

select job, log_user, next_date, next_sec, interval, what from dba_jobs;

(22)

Shutdown options in Oracle database

Following three methods are available to shutdown the oracle database: 1. Normal Shutdown

2. Shutdown Immediate 3. Shutdown Abort 1. Normal Shutdown

During normal shutdown, before the oracle database is shut down, oracle will wait for all active users to disconnect their sessions. As the parameter name (normal) suggest, use this option to shutdown the database under normal conditions.

SQL> shutdown Database closed. Database dismounted.

ORACLE instance shut down. SQL>

2. Shutdown Immediate

During immediate shutdown, before the oracle database is shut down, oracle will rollback active transaction and disconnect all active users. Use this option when there is a problem with your database and you don’t have enough time to request users to log-off.

SQL> shutdown immediate; Database closed.

Database dismounted.

(23)

3. Shutdown Abort

During shutdown abort, before the oracle database is shutdown, all user sessions will be terminated immediately. Un committed transactions will not be rolled back. Use this option only during emergency situations when the “shutdown” and “shutdown immediate” doesn’t work. $ sqlplus '/ as sysdba'

Connected to an idle instance.

SQL> shutdown abort

(24)

Basic UNIX Commands for the DBA

A collection of commonly used UNIX commands by the Oracle Database Administrator.

File Manipulation

Action Command Example

list file contents cat cat myfile

append to a file cat cat myfile >>newfile

combine two files cat cat myfile1 myfile2 >newfile

create a file cat cat >newfile

copy a file cp cp myfile newfile

create a file touch touch newfile

move a file mv mv myfile /usr/home/myfile

remove a file rm rm myfile

Directory Navigation & Manipulation

change directory cd cd

$ORACLE_HOME

change directory -up 1 level cd cd ..

change directory -home cd cd

create directory mkdir mkdir mydir

remove directory rmdir rmdir mydir

current path pwd pwd

directory listing ls ls

directory listing -long ls ls -l

directory listing -all/long ls ls -al

direcoty listing -all/last created ls ls-lrt

(25)

Miscellaneous

change password passwd passwd

list processes -owner ps ps

list processes -everyone ps ps -e

list processes -username ps ps -fu username

show process for sid ps ps -ef|grep [sid]

show smon processes ps ps -ef|grep smon

show full process path /usr/ucb/ps

-augxwv

change access privileges chmod chmod XXX

read/write -owner,group chmod chmod 660 myfile

read/write/execute- owner,group chmod chmod 770 myfile

read -owner,group,world chmod chmod 444 myfile

execute -owner,group chmod chmod 110 myfile

change ownership chown see access modes

default security for new files umask umask 023

change group newgrp newgrp dba

who else is logged on who who

who owns this session who who am i

what is my session and group id id

(26)

Change Access Modes The postion of X effects:

X00 – owner privileges | 0X0 – group privileges | 00X -world or other privilegesThe value of X can be:

0 -no privileges 1 -execute 2 -write 3 -write execute 4 -read 5 -read/execute 6 -read/write

7 -read/write/executeUsing the above numeric file modes you can set access using chmod.

chmod XXX myfile or chmod XXX /usr/home/mydirectory

Oracle Shared vs. Dedicated Server Architecture

 Shared Server:

In a Shared Server architecture MANY sessions (user connections) have ONE server process associated.

 Dedicated Server:

In a Dedicated Server architecture ONLY ONE sessions (user connections) have ONLY ONE server process

(27)

• To request a dedicated server connection when Oracle Database is configured for shared server, users must connect using a net service name that is configured to use a dedicated server. Specifically, the net service name value should include the SERVER=DEDICATED clause in the connect descriptor.

• Because a Shared Server architecture use a multi-thread architecture (one server process has more threads), a Shared Server is named MTS (Multi-Thread Server) as well.

We can know wether if the database using a Shared Server architecture or Dedicated architecture

¾ The following select give us the response: SELECT sid, username, server FROM v$session WHERE type='USER';

(28)

Figure: Dedicated Server Architecture in Oracle 11g