DBA Scripts

db_info

SET PAGESIZE 1000
SET LINESIZE 100
SET FEEDBACK OFF

SELECT *
FROM   v$database;

SELECT *
FROM   v$instance;

SELECT *
FROM   v$version;

SELECT a.name,
       a.value
FROM   v$sga a;

SELECT Substr(c.name,1,60) "Controlfile",
       NVL(c.status,'UNKNOWN') "Status"
FROM   v$controlfile c
ORDER BY 1;

SELECT Substr(d.name,1,60) "Datafile",
       NVL(d.status,'UNKNOWN') "Status",
       d.enabled "Enabled",
       LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM   v$datafile d
ORDER BY 1;

SELECT l.group# "Group",
       Substr(l.member,1,60) "Logfile",
       NVL(l.status,'UNKNOWN') "Status"
FROM   v$logfile l
ORDER BY 1,2;

PROMPT
SET PAGESIZE 14
SET FEEDBACK ON




sessions


SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN spid FORMAT A10
COLUMN service_name FORMAT A15
COLUMN module FORMAT A35
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.service_name,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s,
       v$process p
WHERE  s.paddr = p.addr
ORDER BY s.username, s.osuser;

SET PAGESIZE 14

tablespace_full

SET PAGESIZE 100

PROMPT Tablespaces nearing 0% free
PROMPT ***************************
SELECT tablespace_name,
       size_mb,
       free_mb,
       max_size_mb,
       max_free_mb,
       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct
FROM   (
        SELECT a.tablespace_name,
               b.size_mb,
               a.free_mb,
               b.max_size_mb,
               a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
        FROM   (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS free_mb
                FROM   dba_free_space
                GROUP BY tablespace_name) a,
               (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS size_mb,
                       TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
                FROM   dba_data_files
                GROUP BY tablespace_name) b
        WHERE  a.tablespace_name = b.tablespace_name
       )
WHERE  ROUND((max_free_mb/max_size_mb) * 100,2) < 10;

SET PAGESIZE 14

max_extents


SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

PROMPT
PROMPT Tables and Indexes nearing MAX_EXTENTS
PROMPT **************************************
SELECT e.owner,
       e.segment_type,
       Substr(e.segment_name, 1, 30) segment_name,
       Trunc(s.initial_extent/1024) "INITIAL K",
       Trunc(s.next_extent/1024) "NEXT K",
       s.max_extents,
       Count(*) as extents
FROM   dba_extents e,
       dba_segments s
WHERE  e.owner        = s.owner
AND    e.segment_name = s.segment_name
AND    e.owner        NOT IN ('SYS', 'SYSTEM')
GROUP BY e.owner, e.segment_type, e.segment_name, s.initial_extent, s.next_extent, s.max_extents
HAVING Count(*) > s.max_extents - 10
ORDER BY e.owner, e.segment_type, Count(*) DESC;






object_status

SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF
SET VERIFY OFF

SELECT Substr(object_name,1,30) object_name,
       object_type,
       status
FROM   all_objects
WHERE  owner = Upper('&&1');

PROMPT
SET FEEDBACK ON
SET PAGESIZE 18

min_datafile_size

COLUMN block_size NEW_VALUE v_block_size

SELECT TO_NUMBER(value) AS block_size
FROM   v$parameter
WHERE  name = 'db_block_size';

COLUMN tablespace_name FORMAT A20
COLUMN file_name FORMAT A50
COLUMN current_bytes FORMAT 999999999999999
COLUMN shrink_by_bytes FORMAT 999999999999999
COLUMN resize_to_bytes FORMAT 999999999999999
SET VERIFY OFF
SET LINESIZE 200

SELECT a.tablespace_name,
       a.file_name,
       a.bytes AS current_bytes,
       a.bytes - b.resize_to AS shrink_by_bytes,
       b.resize_to AS resize_to_bytes
FROM   dba_data_files a,
       (SELECT file_id, MAX((block_id+blocks-1)*&v_block_size) AS resize_to
        FROM   dba_extents
        GROUP by file_id) b
WHERE  a.file_id = b.file_id
ORDER BY a.tablespace_name, a.file_name;

monitor_memory

SET LINESIZE 200

COLUMN username FORMAT A20
COLUMN module FORMAT A20

SELECT NVL(a.username,'(oracle)') AS username,
       a.module,
       a.program,
       Trunc(b.value/1024) AS memory_kb
FROM   v$session a,
       v$sesstat b,
       v$statname c
WHERE  a.sid = b.sid
AND    b.statistic# = c.statistic#
AND    c.name = 'session pga memory'
AND    a.program IS NOT NULL
ORDER BY b.value DESC;

spfile_parameters

SET LINESIZE 500

COLUMN name  FORMAT A30
COLUMN value FORMAT A60
COLUMN displayvalue FORMAT A60

SELECT sp.sid,
       sp.name,
       sp.value,
       sp.display_value
FROM   v$spparameter sp
ORDER BY sp.name, sp.sid;

temp_io

SET PAGESIZE 1000

SELECT SUBSTR(t.name,1,50) AS file_name,
       f.phyblkrd AS blocks_read,
       f.phyblkwrt AS blocks_written,
       f.phyblkrd + f.phyblkwrt AS total_io
FROM   v$tempstat f,
       v$tempfile t
WHERE  t.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;

SET PAGESIZE 18

http://oracle-base.com/dba/scripts.php

RMAN Backup types in DIAGRAM

Here i attached a clear diagram of Oracle RMAN backup types; Hopes it will clear you doubts.

the digram can help you see how Rman can perform backups and the types of backup that can be done using Rman tool.

Oracle Database Architecture

Oracle Architecture :-

 

Oracle Architectural Components:

Objectives:

 

This is introduces the Oracle server architecture by examining the physical, memory,process, and logical structures involved in establishing a database connection, creating a session, and executing SQL commands.

Oracle is a Relational Database Management System (RDBMS), which uses Relational Data Model to store its database and SQL (commonly abbreviated as Structured Query Language) to process the stored data. The architecture of Oracle system can be best explained in terms of client/server paradigm. Thus, we will explain the architecture of Oracle server using the structure called instance..

An oracle instance is a complex set of memory structures and operating system processes. It is the Oracle instance, which manages all database activities, such as transaction processing, database recovery, form generation, and so on. The instance structure is loosely styled after UNIX's implementation of multitasking operating system. Discrete processes perform specialized tasks within the RDBMS that work together to accomplish the goals of the instance. Each process has a separate memory block that it uses to store local and private variables, address stacks and other runtime information. The processes use a common shared memory area for processing data concurrently. This memory block is called the System Global Area (SGA). Figure 1 illustrates the architecture of an instance.

 

 

System Global Area (SGA)

The SGA is the primary memory component of the instance. It provides memory structure necessary for data manipulation, SQL statement parsing, and redo caching. The SGA is shared, which means that the multiple processes can access and modify the data contained in it in a synchronized manner. The SGA consists of the following components:

• Shared Pool
• Database buffer cache
• Redo Log Buffer

Multithread server(MTS) structures.

• 
  
  
  
  

The SGA is a read/write memory area that stores information shared by all database processes and by all users of the database (sometimes it is called the Shared Global Area). 

o   This information includes both organizational data and control information used by the Oracle Server. 

o   The SGA is allocated in memory and virtual memory. 

o   The size of the SGA can be established by a DBA by assigning a value to the parameter SGA_MAX_SIZE in the parameter file—this is an optional parameter. 

 

The SGA is allocated when an Oracle instance (database) is started up based on values specified in the initialization parameter file (either PFILE or SPFILE). 

 

The SGA has the following mandatory memory structures:

·        Database Buffer Cache

·        Redo Log Buffer

·        Java Pool

·        Streams Pool

·        Shared Pool – includes two components:

o   Library Cache

o   Data Dictionary Cache

·        Other structures (for example, lock and latch management, statistical data)

 

Additional optional memory structures in the SGA include:

·        Large Pool

The SHOW SGA SQL command will show you the SGA memory allocations. 

·        This is a recent clip of the SGA for the DBORCL database at SIUE. 

·        In order to execute SHOW SGA you must be connected with the special privilege SYSDBA (which is only available to user accounts that are members of the DBA Linux group).

SQL> connect / as sysdba

Connected.

SQL> show sga

 

Total System Global Area 1610612736 bytes

Fixed Size                  2084296 bytes

Variable Size            1006633528 bytes

Database Buffers          587202560 bytes

Redo Buffers               14692352 bytes

 

Early versions of Oracle used a Static SGA.  This meant that if modifications to memory management were required, the database had to be shutdown, modifications were made to the init.ora parameter file, and then the database had to be restarted.  

 

Oracle 11g uses a Dynamic SGA.   Memory configurations for the system global area can be made without shutting down the database instance.  The DBA can resize the Database Buffer Cache and Shared Pool dynamically. 

 

Several initialization parameters are set that affect the amount of random access memory dedicated to the SGA of an Oracle Instance.  These are:

 

·        SGA_MAX_SIZE:  This optional parameter is used to set a limit on the amount of virtual memory allocated to the SGA – a typical setting might be 1 GB; however, if the value for SGA_MAX_SIZE in the initialization parameter file or server parameter file is less than the sum the memory allocated for all components, either explicitly in the parameter file or by default, at the time the instance is initialized, then the database ignores the setting for SGA_MAX_SIZE.  For optimal performance, the entire SGA should fit in real memory to eliminate paging to/from disk by the operating system.

·        DB_CACHE_SIZE:  This optional parameter is used to tune the amount memory allocated to the Database Buffer Cache in standard database blocks.  Block sizes vary among operating systems.  The DBORCL database uses 8 KB blocks.  The total blocks in the cache defaults to 48 MB on LINUX/UNIX and 52 MB on Windows operating systems.

·        LOG_BUFFER:   This optional parameter specifies the number of bytes allocated for the Redo Log Buffer. 

·        SHARED_POOL_SIZE:  This optional parameter specifies the number of bytes of memory allocated to shared SQL and PL/SQL.  The default is 16 MB.  If the operating system is based on a 64 bit configuration, then the default size is 64 MB.

·        LARGE_POOL_SIZE:  This is an optional memory object – the size of the Large Pool defaults to zero.  If the init.ora parameter PARALLEL_AUTOMATIC_TUNING is set to TRUE, then the default size is automatically calculated.

·        JAVA_POOL_SIZE:   This is another optional memory object.  The default is 24 MB of memory.

 

The size of the SGA cannot exceed the parameter SGA_MAX_SIZE minus the combination of the size of the additional parameters, DB_CACHE_SIZE, LOG_BUFFER, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE.

 

Memory is allocated to the SGA as contiguous virtual memory in units termed granules.  Granule size depends on the estimated total size of the SGA, which as was noted above, depends on the SGA_MAX_SIZE parameter.  Granules are sized as follows:

·        If the SGA is less than 1 GB in total, each granule is 4 MB.

·        If the SGA is greater than 1 GB in total, each granule is 16 MB.

 

Granules are assigned to the Database Buffer Cache, Shared Pool, Java Pool, and other memory structures, and these memory components can dynamically grow and shrink.  Using contiguous memory improves system performance.  The actual number of granules assigned to one of these memory components can be determined by querying the database view named V$BUFFER_POOL. 

 

Granules are allocated when the Oracle server starts a database instance in order to provide memory addressing space to meet the SGA_MAX_SIZE parameter.  The minimum is 3 granules:  one each for the fixed SGA, Database Buffer Cache, and Shared Pool.  In practice, you'll find the SGA is allocated much more memory than this.  The SELECT statement shown below shows a current_size of 1,152 granules.

 

SELECT name, block_size,current_size,prev_size,prev_buffers

FROM v$buffer_pool;

NAME     BLOCK_SIZE CURRENT_SIZE  PREV_SIZE PREV_BUFFERS

-------- ---------- ------------ ---------- ------------

DEFAULT  8192       560           576       71244 

Shared pool

The Oracle shared pool contains Oracle's library cache, which is responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go against the Oracle database. Hence, the shared pool is a key component, so it's necessary for the Oracle database administrator to check for shared pool contention.

The shared pool is like a buffer for SQL statements.  Oracle's parsing algorithm ensures that identical SQL statements do not have to be parsed each time they're executed.  The shared pool  is used to store SQL statements, and it includes the following components:

Since it is not possible to dedicate separate regions of memory for the shared pool components, the shared pool is usually the second-largest SGA memory area (depending on the size of the db_cache_size parameter). The shared pool contains RAM memory regions that serve the following purposes:

 

• Library cache – The library cache contains the current SQL execution plan information. It also holds stored procedures and trigger code.

• Dictionary cache - The dictionary cache stores environmental information, which includes referential integrity, table definitions, indexing information, and other metadata stored within Oracle's internal tables.

• Session information – Systems that use SQL*Net version 2 with a multi-threaded server need this area to store session information. Beginning with Oracle, the v$session view contains information related to Oracle*Net users.

The following table lists the different areas stored in the shared pool and their purpose:

* Shared SQL Area - The shared SQL area stores each SQL statement executed in the database. This area allows SQL execution plans to be reused by many users.

* Private SQL Area - Private SQL areas are non-shared memory areas assigned to unique user sessions.

* PL/SQL Area - Used to hold parsed and compiled PL/SQL program units, allowing the execution plans to be shared by many users.

* Control Structures - Common control structure information, for example, lock information.

Database Buffer Cache

 

The database buffer cache holds copies of data blocks read from the data files. The term data block is used to describe a block containing table data, index data, clustered data, and so on. Basically, it is a block that contains data. All user processes concurrently connected to the instance share access to the database buffer cache. The database buffer cache is logically segmented into multiple sets. This reduces contention on multiprocessor systems.

This area of the SGA contains only the buffers themselves and not their control structures. For each buffer, there is a corresponding buffer header in the variable area of the SGA.

 

Program Global Area (PGA)

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 region created by Oracle when a server process is started. Access to the PGA is exclusive to that server process and it is read and written only by Oracle code acting on its behalf. It contains a private SQL area and a session memory area.

A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Session memory is the memory allocated to hold a session’s variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.

 

Buffer Cache Management

 

The database buffer cache is 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 buffers that are currently being accessed.

When an Oracle process requires data, it searches the buffer cache, finds the data blocks, and then uses the data. This is known as a cache hit. If it cannot find the data, then it must be obtained from the data file. In this case, it finds a free buffer to accommodate the data block by scanning the LRU list, starting at the least-recently-used from the end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.

When the user process is performing a full table scan, it reads the data blocks into buffers and places them on the LRU end instead of the MRU end of the LRU list. This is because a fully scanned table is usually needed only briefly and the blocks should be moved out quickly.

What Is a Dirty Block?

Whenever a server process changes or modifies a data block, it becomes a dirty block. Once a server process makes changes to the data block, the user may commit transactions, or transactions may not be committed for quite some time. In either case, the dirty block is not immediately written back to disk.

Writing dirty blocks to disk takes place under the following two conditions:

* When a server process cannot find a clean, reusable buffer after scanning a threshold number of buffers, then the database writer process writes the dirty blocks to disk.

* When the checkpoint takes place, the database writer process writes the dirty blocks to disk.

Large Pool 

Oracle Large Pool is an optional memory component of the oracle database SGA. This area is used for providing large memory allocations in many situations that arise during the operations of an oracle database instance.

1. Session memory for the a shared server and the Oracle XA Interface when distributed transactions are involved

2. I/O Server Processes

3. Parallel Query Buffers

4. Oracle Backup and Restore Operations using RMAN

Large Pool plays an important role in Oracle Database Tuning since the allocation of the memory for the above components otherwise is done from the shared pool. Also due to the large memory requirements for I/O and Rman operations, the large pool is better able to satisfy the requirements instead of depending on the Shared Pool Area.

Usage of a Large Pool Area allows the shared pool to primarily cache SQL and avoid the overhead casued by shrinking the SQL Cache Area

 

JAVA Pool

The JAVA Pool holds the JAVA execution code in a similar manner to the PL/SQL cache in the shared pool. The JAVA pool is used by many internal routines, such as import and export, and should be sized at approximately 60 megabytes if no other JAVA will be utilized in the user applications

The Redo Log Buffer

 

The redo log buffer is a RAM area (defined by the initialization parameter log_buffer) that works to save changes to data, in case something fails and Oracle has to put it back into its original state (a “rollback”).  When Oracle SQL updates a table (a process called Data Manipulation Language, or DML), redo images are created and stored in the redo log buffer. Since RAM is faster than disk, this makes the storage of redo very fast.

 

The Oracle redo log buffer provides the following functions within the Oracle SGA:

• Serves for assistance with database recovery tasks
• Records all changes made to database blocks
• Places changes recorded to redo entries for redo logs

The database initialization parameter log_buffer defines the default size of the redo log buffer within Oracle 11g.

 

Redo log files:

Redo logs are transaction journals. Each transaction is recorded in the redo logs. Redo logs are used in a serial fashion with each transaction queuing up in the redo log buffers and being written one at a time into the redo logs. Redo logs as a general rule should switch about every thirty minutes. However, you may need to adjust the time up or down depending on the importance of your data. The rule of thumb is to size the redo logs such that you only loose the amount of data you can stand to loose should for some reason the online redo log become corrupt. With modern Oracle redo log mirroring and with disk array mirroring and various forms of online disk repair and replacement the occurrence of redo log corruptions has dropped to practically zero, so size based on the number of archive logs you want to apply should the database fail just before your next backup.

The LOG_BUFFER_SIZE and LOG_BUFFERS parameters control the redo log buffers. The LOG_BUFFER_SIZE should be set to reduce the number of writes required per redo log but not be so large that it results in an excessive IO wait time. Some studies have shown that sizing bigger than one megabyte rarely results in performance gains. Generally I size the LOG_BUFFER_SIZE such that it is equal to or results in an even divisor of the redo log size.

Monitor redo logs using the alert log, V$LOGHIST, V$LOGFILE, V$RECOVERY_LOG and V$LOG DPTs.

 

Control files

 

Control files are created when the database is created. Control file size is determined by a number of initialization parameters and really shouldn’t concern you. However, if you are using raw devices for control files make sure the raw partition is sized to allow the control file to grow in Oracle8 and Oracle8i databases as backup information is added. Each database instance must have at least one control file of it will not start. Loss of all control files will cause the database to crash. Have at least two copies on physically separate disk arrays or devices.

I suggest maintaining a script to rebuild your control files. The control file rebuild script should be recreated after each physical change to the database. The command CREATE CONTROLFILE is used to manually create a controlfile. However, the ALTER DATABASE BACKUP CONTROL FILE TO TRACE command is the preferred method to generate a script that can be used to rebuild the control file if it becomes damaged.

 

Initialization File

 

Although not normally discussed as a database file a database will not startup without a valid initialization parameter file. The file is only read at startup and contains the information required to set up the SGA, control file locations, trace and dump file locations and setup parameters for multi-threaded server, parallel query and Oracle Parallel Server. I suggest keeping a copy in a secure place incase yours ever becomes corrupted, deleted or damaged. The Oracle Reference, which is included in the Oracle8 and Oracle8i documentation set, provides descriptions of all initialization parameters. There are also undocumented initialization parameters that allow specialized behaviors to be configured. Both the documented and undocumented initialization parameters are covered in my book “ORACLE8i Administration and Management” from Wiley and Sons.

It should be obvious that the most important file regarding database setup and operation is probably the INIT.ORA, or initialization file. This file contains the assignments for the database initialization parameters.

 

NAME	VALUE	DESCRIPTION
O7_DICTIONARY_ACCESSIBILITY	TRUE	V7 Dictionary accessibility  Support (Migration only)
ALWAYS_ANTI_JOIN	NESTED_LOOPS	Always use this anti-join when possible
ALWAYS_SEMI_JOIN	standard	Always use this semi-join when possible
AQ_TM_PROCESSES	0	Number of AQ Time Managers to start
AUDIT_FILE_DEST	(PD)	Destination for audit files
AUDIT_TRAIL	NONE	Enable system auditing
BACKGROUND_CORE_DUMP	PARTIAL	Sets whether SGA is dumped with core file dump,   PARTIAL means don't dump SGA.
BACKGROUND_DUMP_DEST	(PD)	Detached process dump directory
BACKUP_TAPE_IO_SLAVES	FALSE	BACKUP Tape I/O slaves
BITMAP_MERGE_AREA_SIZE	1048576	Maximum memory allow for BITMAP MERGE
BLANK_TRIMMING	FALSE	Blank trimming semantics parameter
BUFFER_POOL_KEEP	0	Number of database blocks/latches in KEEP buffer pool
BUFFER_POOL_RECYCLE	0	Number of database blocks/latches in recycle buffer pool
COMMIT_POINT_STRENGTH	1	Bias this node has toward not preparing in a two-phase commit
COMPATIBLE	8.1.0	Database will be compatible with this software version
CONTROL_FILE_RECORD_KEEP_TIME	7	Control file record keep time in days
CONTROL_FILES	(PD)	Control file names list
CORE_DUMP_DEST	(PD)	Destination for core dump files.
CPU_COUNT	(PD)	Number of cpu's for this instance
CREATE_BITMAP_AREA_SIZE	8388608	Size of create bitmap buffer for bitmap index
CURSOR_SPACE_FOR_TIME	FALSE	Use more memory in order to get faster execution
DB_BLOCK_BUFFERS	8000	Number of database blocks  cached in memory
DB_BLOCK_CHECKING	TRUE	Data and index block checking overrides events 10210 and 10211
DB_BLOCK_CHECKSUM	FALSE	Store checksum in db blocks and check during reads
DB_BLOCK_LRU_LATCHES	1|CPU_COUNT/2	Number of lru latches
DB_BLOCK_MAX_DIRTY_TARGET	DB_BLOCK_BUFFERS	Upper bound on modified buffers/recovery reads
DB_BLOCK_SIZE	(PD)	Size of database block in bytes
DB_DOMAIN	WORLD	Directory part of global database name stored with CREATE DATABASE
DB_FILE_DIRECT_IO_COUNT	64	Sequential I/O block count
DB_FILE_MULTIBLOCK_READ_COUNT	8	Db blocks read for each IO
DB_FILE_NAME_CONVERT	NULL	Datafile name convert pattern and string for standby/clone database
DB_FILES	MAXDATAFILES	Max allowable # db files
DB_NAME	(PD)	Database name specified in CREATE DATABASE
DB_WRITER_PROCESSES	1	Number of background database writer processes to start
DBLINK_ENCRYPT_LOGIN	FALSE	Enforce password for distributed login always be encrypted
DBWR_IO_SLAVES	0	Number of DBWR I/O slaves
DELAYED_LOGGING_BLOCK_CLEANOUTS	TRUE	Turns delayed block cleanout on or off
DISK_ASYNCH_IO	TRUE	Use asynch I/O for random access devices
DISTRIBUTED_TRANSACTIONS	(PD)	Max. number of concurrent distributed transactions
DML_LOCKS	4*Trans.	Dml locks - one for each table modified in a transaction
ENQUEUE_RESOURCES	Derived	Resources for enqueues
EVENT	NULL	Debug event control
FIXED_DATE	NULL	Fixed SYSDATE value
FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY	FALSE	Freeze database during instance recovery (OPS)
GC_DEFER_TIME	10	How long to defer down converts for hot buffers (DFS)(OPS)
GC_FILES_TO_LOCKS	NULL	Mapping between file numbers and lock buckets  (DFS)(OPS)
GC_RELEASABLE_LOCKS	0	Number of releasable locks (DFS)(OPS)
GC_ROLLBACK_LOCKS	20	Locks for the rollback segments (DFS)(OPS)
GLOBAL_NAMES	TRUE	Enforce that database links have same name as remote database
HASH_AREA_SIZE	2*SORT	Size of in-memory hash work area
HASH_JOIN_ENABLED	TRUE	Enable/disable hash join
HASH_MULTIBLOCK_IO_COUNT	1	Number of blocks hash join will read/write at once
HI_SHARED_MEMORY_ADDRESS	0	SGA starting address (high order 32-bits on 64-bit platforms)
HS_AUTOREGISTER	TRUE	Enable automatic server DD updates in HS agent self-registration
IFILE	NULL	Include file in init.ora
INSTANCE_GROUPS	NULL	List of instance group names
INSTANCE_NAME	NULL	Instance name supported by the instance
INSTANCE_NUMBER	0	Instance number
JAVA_POOL_SIZE	10000K	Size in bytes of the Java pool
JOB_QUEUE_INTERVAL	60	Wakeup interval in seconds for job queue processes
JOB_QUEUE_KEEP_CONNECTIONS	FALSE	Keep network connections between execution of jobs
JOB_QUEUE_PROCESSES	0	Number of job queue  processes to start
LARGE_POOL_SIZE	0	Size in bytes of the large allocation pool (auto set at 600k)
LICENSE_MAX_SESSIONS	0	Maximum number of non-system user sessions allowed
LICENSE_MAX_USERS	0	Maximum number of named users that can be created in the database
LICENSE_SESSIONS_WARNING	0	Warning level for number of non-system user sessions
LM_LOCKS	12000	Number of locks configured for the lock manager (OPS)
LM_PROCS	64	Number of client processes configured for the lock manager (OPS)
LM_RESS	6000	Number of resources configured for the lock manager (OPS)
LOCAL_LISTENER	NULL	Local listener
LOCK_NAME_SPACE	NULL	Lock name space used for generating lock names for standby/clone database
LOCK_SGA	FALSE	Lock entire SGA in physical memory

Oracle Database Structure:

• A database is collection of data files plus programs that manipulate those data files.

• Two types of information are stored in an Oracle database

• User data, relevant to a particular application and

• System data, that the database needs to manage itself

Components:

• Database files – contain all database data

• Control files – contain info to access and manipulate the data

• Redo Logs – record all the transactions on the database

• Tablespaces – logical divisions of a database

• Segments – set of database blocks

• Extents – allocation of contiguous space on disk exp in bytes.

 

Database files:

• An Oracle database consists of one or more database files.

• These files contain data that can be accessed with the help of SQL.

• A file can be associated with one and only one tablespace.

• The database when created initially, has only one physical file.

• The maximum number of files that can be opened at a time are 32 by default.

• One or more physical files form a logical unit called Tablespace.

Control files:

• A database’s overall physical architecture is maintained by its control files.

• These record control information about all the files within the database.

• A control file is associated with a single database

• Control files are automatically modified by Oracle; a user cannot edit them.

• They are used to maintain internal consistency and guide recovery operations.

Redo Log files:

• A Redo Log contains all the transactions that have occurred against the database.

• No activity in the database can take place without being recorded in Redo Logs.

• The Redo Logs are necessary to protect against data loss.

• Redo Logs are also called transaction logs.

• Every database requires a minimum of two Redo Logs.

 

• These are used in recovery operations to restore lost or damaged files.

Oracle Processes:

 

The front-line support for Oracle backup and recovery is the Oracle architecture. One part

of this architecture is the processes related to the Oracle database. Although the Oracle

database has a number of processes, only a few really matter with respect to backup and

recovery and will be mentioned in this text. These processes are as follows:

 

SMON

PMON

CKPT

LGWR

DBWR

ARCH

User processes

 

Let’s discuss each of these processes next so you can better understand how they impact

database recovery.

 

SMON (System Monitor):

 

SMON (System MONitor) is an Oracle background process created when you start a database instance. The SMON process performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents.

When you startup your Oracle database, Oracle will perform several checks to see if the database is healthy. SMON manages your database for you!

If Oracle finds a problem at startup time, the System Monitor process (SMON) will perform recovery activities. SMON also performs certain types of database maintenance. These activities occur in the background and have no real impact on normal database operations.

 

Performs multiple maintenance tasks, including the following:

• Creates and manages the temporary tablespace metadata

• Reclaims space used by orphaned temp segments

• Maintains the undo tablespace by on-lining, off-lining, and shrinking undo segments based on undo space usage statistics

• Cleans up the data dictionary when in a transient and inconsistent state

• Maintains the SCN to time mapping table used to support Flashback

 

 PMON (Process Monitor):

Process Monitor process recovers failed process resources. If MTS (also called Shared Server Architecture) is being utilized, PMON monitors and restarts any failed dispatcher or server processes. In RAC, PMON’s role as service registration agent is particularly important.

• The Process Monitor is the janitor of the database, cleaning-up trash left-over from aborted user sessions (for instance, if a client abnormally disconnects from the database). If this should happen, it is the job of the Process Monitor (PMON) process to cleanup after that failure. PMON will cleanup memory areas and other database resources that were in use by that user process. PMON constantly checks the status of user and database processes. In some cases, failed database processes can be restarted by PMON.

LGWR Process

The log writer process (LGWR) is responsible for keeping the online redo logs up-to-date. The

job of the LGWR process is to move redo from the volatile (nonpersistent) redo log buffer in

the System (sometimes called Shared) Global Area (SGA) to the persistence of the online redo

logs. A number of different things cause LGWR to wake up and write the redo data, among

them commits and when the redo log buffer fills to a certain point.

DBWR Process

The database writer process DBWn is responsible for writing to the database datafiles.

This writing occurs during events called checkpoints. A database checkpoint may, in reality,

happen at just about any time while the database is running. DBWR has very little

to do with recovery of the database (other than to support it by writing to the datafiles)

because database datafile writes are often delayed and the blocks within the datafiles themselves

are not consistent with the current state of the data inside of the SGA.

ARCH Process

The archiver process ARCn is responsible for the creation of archived redo logs. In a later

section in this chapter on redo logs, we will discuss how redo logs are filled with redo. Once

the redo log file fills, a log switch occurs and Oracle will begin to write to the next online

redo log. If the database is in ARCHIVELOG mode (see the section “NOARCHIVELOG

and ARCHIVELOG Modes”), the ARCH process will be responsible for taking that filled

archived redo log and copying it to one or more backup locations.

In Oracle Database 11g, the ARCH process starts automatically. Oracle can also start

more than one ARCH process if multiple redo logs need to be archived. For ARCH to work

properly, you will need to configure the appropriate archiving locations (see “Configuring the

Database for Backup and Recovery” later in this chapter for more). The ARCH process is so

vital to backup and recovery of the database that if it cannot copy the archived redo logs to the

mandatory archived log destinations, the database will eventually stall until the problem

is corrected.

User Processes

At first glance, it might seem that the user processes are not all that important to backup and

recovery. As you will see, user processes are actually an integral part of backup and recovery

since you have to be able to connect to the database instance to actually do a database backup

or recovery.

 

Tablespaces:

• A database is divided into logical divisions called tablespaces

• A database may have one or more tablespaces

• Each logical tablespace corresponds to one or more physical database files.

•The typical tablespace present in an Oracle database are (11g) :

SYSTEM Tablespace:

 

• Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open.

To take advantage of the benefits of locally managed tablespaces, you can create a locally managed SYSTEM tablespace, or you can migrate an existing dictionary managed SYSTEM tablespace to a locally managed format.

 

In a database with a locally managed SYSTEM tablespace, dictionary tablespaces cannot be created. It is possible to plug in a dictionary managed tablespace using the transportable feature, but it cannot be made writable.

 

• The SYSTEM tablespace always contains the data dictionary tables for the entire database. The data dictionary tables are stored in datafile 1.

 

Note: Once a tablespace is locally managed, it cannot be reverted back to being dictionary managed.

 

SYSAUX Tablespace:

• The SYSAUX tablespace is always created at database creation. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle Database features and products that previously required their own tablespaces, it reduces the number of tablespaces required by the database. It also reduces the load on the SYSTEM tablespace.

You can specify only datafile attributes for the SYSAUX tablespace, using the SYSAUX DATAFILE clause in the CREATE DATABASE statement. Mandatory attributes of the SYSAUX tablespace are set by Oracle Database and include:

• PERMANENT
• READ WRITE
• EXTENT MANAGMENT LOCAL
• SEGMENT SPACE MANAGMENT AUTO

You cannot alter these attributes with an ALTER TABLESPACE statement, and any attempt to do so will result in an error. You cannot drop or rename the SYSAUX tablespace.

Undo Tablespaces:

• Undo tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. Each database contains zero or more undo tablespaces. In automatic undo management mode, each Oracle instance is assigned one (and only one) undo tablespace. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by Oracle.

 

When the first DML operation is run within a transaction, the transaction is bound (assigned) to an undo segment (and therefore to a transaction table) in the current undo tablespace. In rare circumstances, if the instance does not have a designated undo tablespace, the transaction binds to the system undo segment.

 

• Each undo tablespace is composed of a set of undo files and is locally managed. Like other types of tablespaces, undo blocks are grouped in extents and the status of each extent is represented in the bitmap. At any point in time, an extent is either allocated to (and used by) a transaction table, or it is free.

Temporary Tablespace:

 

• When the SYSTEM tablespace is locally managed, you must define a default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.

 

• If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporary storage. However, you will receive a warning inALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.

 

USER Tablespace:  

 

• information about users is stored