Tuesday 18 November 2014

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

Tuesday 16 September 2014

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.

Tuesday 26 August 2014

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_SIZELOG_BUFFERSHARED_POOL_SIZELARGE_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