HOW ORACLE WORKS?

An instance is currently running on the computer that is executing Oracle called

database server.

A computer is running an application (local machine) runs the application in a user

process.

The client application attempts to establish a connection to the server using the
proper Net8 driver.

When the oracle server detects the connection request from the client it’s check
client authentication, if authentication pass the oracle server creates a (dedicated)
server process on behalf of the user process. When the user executes a SQL
statement and commits the transaction. For example, the user changes a name in a
row of a table. The server process receives the statement and checks the shared
pool for any shared SQL area that contains an identical SQL statement. If a shared
SQL area is found, the server process checks the user's access privileges to the
requested data and the previously existing shared SQL area is used to process the
statement; if not, a new shared SQL area is allocated for the statement so that it can
be parsed and processed. The server process retrieves any necessary data values
from the actual datafile or those stored in the system global area. The server process
modifies data block in the system global area. The DBWn process writes modified
blocks permanently to disk when doing so is efficient. Because the transaction
committed, the LGWR process immediately records the transaction in the online redo
log file. If the transaction is successful, the server process sends a message across
the network to the application. If it is not successful, an appropriate error message is
transmitted. Throughout this entire procedure, the other background processes run,
watching for conditions that require intervention.

The Basics of Oracle Architecture

As an Oracle DBA, you must be understand the concepts of Oracle architecture

clearly. It is a basic step or main point that you need before you go to manage your
database. By this article, I will try to share my knowledge about it. Hope it can be
useful for you.

What is An Oracle Database?

Basically, there are two main components of Oracle database –– instance and
database itself. An instance consists of some memory structures (SGA) and the
background processes.

1
 Figure 1. Two main components of Oracle database

Instance

As we cover above, instance is consist of the memory structures and background

processes. The memory structure itself consists of System Global Area (SGA),
Program Global Area (PGA). In the other hand, the mandatory background processes
are Database Writer (DBWn), Log Writer (LGWR), Checkpoint (CKPT), System
Monitor (SMON), and Process Monitor (PMON). And another optional background
processes are Archiver (ARCn), Recoverer (RECO), etc.

Figure 2 will illustrate the relationship for those components on an instance.

2
 Figure 2. The instance components

System Global Area

SGA is the primary memory structures. This area is broken into a few of part
memory –– Buffer Cache, Shared Pool, Redo Log Buffer, Large Pool, and Java Pool.

Buffer Cache

Buffer cache is used to stores the copies of data block that retrieved from datafiles.
That is, when user retrieves data from database, the data will be stored in buffer
cache. Its size can be manipulated via DB_CACHE_SIZE parameter in [Link]
initialization parameter file.

Shared Pool

Shared pool is broken into two small part memories –– Library Cache and Dictionary
Cache. The library cache is used to stores information about the commonly used SQL
and PL/SQL statements; and is managed by a Least Recently Used (LRU) algorithm.
It is also enables the sharing those statements among users. In the other hand,
dictionary cache is used to stores information about object definitions in the
database, such as columns, tables, indexes, users, privileges, etc.

The shared pool size can be set via SHARED_POOL_SIZE parameter in [Link]
initialization parameter file.

Redo Log Buffer

Each DML statement (insert, update, and delete) executed by users will generates
the redo entry. What is a redo entry? It is an information about all data changes
made by users. That redo entry is stored in redo log buffer before it is written into
the redo log files. To manipulate the size of redo log buffer, you can use the
LOG_BUFFER parameter in [Link] initialization parameter file.

Large Pool

Large pool is an optional area of memory in the SGA. It is used to relieves the
burden place on the shared pool. It is also used for I/O processes. The large pool
size can be set by LARGE_POOL_SIZE parameter in [Link] initialization parameter
file.

Java Pool

As its name, Java pool is used to services parsing of the Java commands. Its size can
be set by JAVA_POOL_SIZE parameter in [Link] initialization parameter file.

Oracle Background Processes

Oracle background processes is the processes behind the scene that work together
with the memories.

DBWn

3
Database writer (DBWn) process is used to write data from buffer cache into the
datafiles. Historically, the database writer is named DBWR. But since some of Oracle
version allows us to have more than one database writer, the name is changed to
DBWn, where n value is a number 0 to 9.

LGWR

Log writer (LGWR) process is similar to DBWn. It writes the redo entries from redo
log buffer into the redo log files.

CKPT

Checkpoint (CKPT) is a process to give a signal to DBWn to writes data in the buffer
cache into datafiles. It will also updates datafiles and control files header when log
file switch occurs.

SMON

System Monitor (SMON) process is used to recover the system crach or instance
failure by applying the entries in the redo log files to the datafiles.

PMON

Process Monitor (PMON) process is used to clean up work after failed processes by
rolling back the transactions and releasing other resources.

Database

We can broken up database into two main structures –– Logical structures and
Physical structures.

Logical Structures

The logical units are tablespace, segment, extent, and data block.

Figure 3 will illustrate the relationships between those units.

4
 Figure 3. The relationships between the Oracle logical structures

Tablespace

A Tablespace is a grouping logical database objects. A database must have one or

more tablespaces. In the Figure 3, we have three tablespaces –– SYSTEM
tablespace, Tablespace 1, and Tablespace 2. Tablespace is composed by one or more
datafiles.

Segment

A Tablespace is further broken into segments. A segment is used to stores same type
of objects. That is, every table in the database will store into a specific segment
(named Data Segment) and every index in the database will also store in its own
segment (named Index Segment). The other segment types are Temporary Segment
and Rollback Segment.

Extent

A segment is further broken into extents. An extent consists of one or more data
block. When the database object is enlarged, an extent will be allocated. Unlike a

5
tablespace or a segment, an extent cannot be named.

Data Block

A data block is the smallest unit of storage in the Oracle database. The data block
size is a specific number of bytes within tablespace and it has the same number of
bytes.

Physical Structures

The physical structures are structures of an Oracle database (in this case the disk
files) that are not directly manipulated by users. The physical structure consists of
datafiles, redo log files, and control files.

Datafiles

A datafile is a file that correspondens with a tablespace. One datafile can be used by
one tablespace, but one tablespace can has more than one datafiles.

Redo Log Files

Redo log files are the files that store the redo entries generated by DML statements.
It can be used for recovery processes.

Control Files

Control files are used to store information about physical structure of database, such
as datafiles size and location, redo log files location, etc.

Starting up a database

This article explains the procedures involved in starting an Oracle instance and
database.

First Stage: Oracle engine start an Oracle Instance

When Oracle starts an instance, it reads the initialization parameter file to determine
the values of initialization parameters. Then, it allocates an SGA, which is a shared
area of memory used for database information, and creates background processes.
At this point, no database is associated with these memory structures and processes.

Second Stage: Mount the Database

To mount the database, the instance finds the database control files and opens
them. Control files are specified in the CONTROL_FILES initialization parameter in
the parameter file used to start the instance. Oracle then reads the control files to
get the names of the database's datafiles and redo log files.
At this point, the database is still closed and is accessible only to the database
administrator. The database administrator can keep the database closed while
completing specific maintenance operations. However, the database is not yet
available for normal operations.

6
Final Stage: Database open for normal operation

Opening a mounted database makes it available for normal database operations.

Usually, a database administrator opens the database to make it available for
general use.
When you open the database, Oracle opens the online datafiles and online redo log
files. If a tablespace was offline when the database was previously shut down, the
tablespace and its corresponding datafiles will still be offline when you reopen the
database.
If any of the datafiles or redo log files are not present when you attempt to open the
database, then Oracle returns an error. You must perform recovery on a backup of
any damaged or missing files before you can open the database.

Open a Database in Read-Only Mode

You can open any database in read-only mode to prevent its data from being
modified by user transactions. Read-only mode restricts database access to read-
only transactions, which cannot write to the datafiles or to the redo log files.
Disk writes to other files, such as control files, operating system audit trails, trace
files, and alert files, can continue in read-only mode. Temporary tablespaces for sort
operations are not affected by the database being open in read-only mode. However,
you cannot take permanent tablespaces offline while a database is open in read-only
mode. Also, job queues are not available in read-only mode.
Read-only mode does not restrict database recovery or operations that change the
database's state without generating redo data. For example, in read-only mode:
 Datafiles can be taken offline and online
 Offline datafiles and tablespaces can be recovered
 The control file remains available for updates about the state of the database

One useful application of read-only mode is that standby databases can function as
temporary reporting databases.

Database Shutdown

The three steps to shutting down a database and its associated instance are:
 Close the database.
 Unmount the database.
 Shut down the instance.

Close a Database

When you close a database, Oracle writes all database data and recovery data in the
SGA to the datafiles and redo log files, respectively. Next, Oracle closes all online
datafiles and online redo log files. At this point, the database is closed and
inaccessible for normal operations. The control files remain open after a database is
closed but still mounted.

Close the Database by Terminating the Instance

7
In rare emergency situations, you can terminate the instance of an open database to
close and completely shut down the database instantaneously. This process is fast,
because the operation of writing all data in the buffers of the SGA to the datafiles
and redo log files is skipped. The subsequent reopening of the database requires
recovery, which Oracle performs automatically.

Un mount a Database

After the database is closed, Oracle un mounts the database to disassociate it from
the instance. At this point, the instance remains in the memory of your computer.
After a database is un mounted, Oracle closes the control files of the database.

Shut Down an Instance

The final step in database shutdown is shutting down the instance. When you shut
down an instance, the SGA is removed from memory and the background processes
are terminated.

Abnormal Instance Shutdown

In unusual circumstances, shutdown of an instance might not occur cleanly; all

memory structures might not be removed from memory or one of the background
processes might not be terminated. When remnants of a previous instance exist, a
subsequent instance startup most likely will fail. In such situations, the database
administrator can force the new instance to start up by first removing the remnants
of the previous instance and then starting a new instance, or by issuing a
SHUTDOWN ABORT statement in Enterprise Manager.

Oracle Background Processes

An Oracle instance runs two types of processes –

Server Process
Background Process

Before work user must connect to an Instance. When user LOG on Oracle Server
Oracle Engine create a process called Server processes. Server process
communicate with oracle instance on the behalf of user process.

Each background process is useful for a specific purpose and its role is well defined.

Background processes are invoked automatically when the instance is started.

Database Writer (DBW’r)

Process Name: DBW0 through DBW9 and DBWa through DBWj

Max Processes: 20

8
This process writes the dirty buffers for the database buffer cache to data files. One
database writer process is sufficient for most systems; more can be configured if
essential. The initialisation parameter, DB_WRITER_PROCESSES, specifies the
number of database writer processes to start.

The DBWn process writes dirty buffer to disk under the following conditions:
When a checkpoint is issued.
When a server process cannot find a clean reusable buffer after
scanning a threshold number of buffers.
Every 3 seconds.
When we place a normal or temporary table space offline and read only
mode
When we drop and truncate table.
When we put a table space in backup mode;

Log Writer(LGWR)

Process Name: LGWR

Max Processes: 1

The log writer process writes data from the redo log buffers to the redo log files on
disk.

The writer is activated under the following conditions:

 When a transaction is committed, a System Change Number (SCN) is
generated and tagged to it. Log writer puts a commit record in the redo log
buffer and writes it to disk immediately along with the transaction's redo
entries.
 Every 3 seconds.
 When the redo log buffer is 1/3 full.
 When DBWn signals the writing of redo records to disk. All redo records
associated with changes in the block buffers must be written to disk first (The
write-ahead protocol). While writing dirty buffers, if the DBWn process finds
that some redo information has not been written, it signals the LGWR to write
the information and waits until the control is returned.

Log writer will write synchronously to the redo log groups in a circular fashion. If any
damage is identified with a redo log file, the log writer will log an error in the LGWR
trace file and the system Alert Log. Sometimes, when additional redo log buffer
space is required, the LGWR will even write uncommitted redo log entries to release
the held buffers. LGWR can also use group commits (multiple committed
transaction's redo entries taken together) to write to redo logs when a database is
undergoing heavy write operations.

The log writer must always be running for an instance.

System Monitor

Process Name: SMON

Max Processes: 1

9
This process is responsible for instance recovery, if necessary, at instance startup.
SMON also cleans up temporary segments that are no longer in use. SMON wakes up
about every 5 minutes to perform housekeeping activities. SMON must always be
running for an instance.

Process Monitor

Process Name: PMON

Max Processes: 1

This process is responsible for performing recovery if a user process fails. It will
rollback uncommitted transactions. PMON is also responsible for cleaning up the
database buffer cache and freeing resources that were allocated to a process. PMON
also registers information about the instance and dispatcher processes with network
listener.
PMON wakes up every 3 seconds to perform housekeeping activities. PMON must
always be running for an instance.

Checkpoint Process

Process Name: CKPT

Max processes: 1

Checkpoint process signals the synchronization of all database files with the
checkpoint information. It ensures data consistency and faster database recovery in
case of a crash.
CKPT ensures that all database changes present in the buffer cache at that point are
written to the data files, the actual writing is done by the Database Writer process.
The datafile headers and the control files are updated with the latest SCN (when the
checkpoint occurred), this is done by the log writer process.
The CKPT process is invoked under the following conditions:

 When a log switch is done.

 When the time specified by the initialization parameter
LOG_CHECKPOINT_TIMEOUT exists between the incremental checkpoint and
the tail of the log; this is in seconds.
 When the number of blocks specified by the initialization parameter
LOG_CHECKPOINT_INTERVAL exists between the incremental checkpoint and
the tail of the log; these are OS blocks.
 The number of buffers specified by the initialization parameter
FAST_START_IO_TARGET required to perform roll-forward is reached.
 Oracle 9i onwards, the time specified by the initialization parameter
FAST_START_MTTR_TARGET is reached; this is in seconds and specifies the
time required for a crash recovery. The parameter
FAST_START_MTTR_TARGET replaces LOG_CHECKPOINT_INTERVAL and
FAST_START_IO_TARGET, but these parameters can still be used.
 When the ALTER SYSTEM SWITCH LOGFILE command is issued.
 When the ALTER SYSTEM CHECKPOINT command is issued.

10
Incremental Checkpoints initiate the writing of recovery information to datafile
headers and controlfiles. Database writer is not signaled to perform buffer cache
flushing activity here.

Archiver

Process Name: ARC0 through ARC9

Max Processes: 10

The ARCn process is responsible for writing the online redo log files to the mentioned
archive log destination after a log switch has occurred. ARCn is present only if the
database is running in archivelog mode and automatic archiving is enabled. The log
writer process is responsible for starting multiple ARCn processes when the workload
increases. Unless ARCn completes the copying of a redo log file, it is not released to
log writer for overwriting.
The number of Archiver processes that can be invoked initially is specified by the
initialization parameter LOG_ARCHIVE_MAX_PROCESSES. The actual number of
Archiver processes in use may vary based on the workload.

Lock Monitor

Process Name: LMON

processes: 1

Meant for Parallel server setups, Lock Monitor manages global locks and resources. It
handles the redistribution of instance locks whenever instances are started or
shutdown. Lock Monitor also recovers instance lock information prior to the instance
recovery process. Lock Monitor co-ordinates with the Process Monitor to recover
dead processes that hold instance locks.

Lock processes

Process Name: LCK0 through LCK9

Max Processes: 10

Meant for Parallel server setups, the instance locks that are used to share resources
between instances are held by the lock processes.

Block Server Process

Process Name: BSP0 through BSP9

Max processes: 10

Meant for Parallel server setups, Block server Processes have to do with providing a
consistent read image of a buffer that is requested by a process of another instance,
in certain circumstances.

Queue Monitor

Process Name: QMN0 through QMN9

Max Processes: 10

11
This is the advanced Queuing Time manager process. QMNn monitors the message
queues. Failure of QMNn process will not cause the instance to fail.

Event Monitor

Process Name: EMN0/EMON

Max Processes: 1

This process is also related to Advanced Queuing, and is meant for allowing a
publish/subscribe style of messaging between applications.

Recoverer

Process Name: RECO

Max processes: 1

Intended for distributed recovery. All in-doubt transactions are recovered by this
process in the distributed database setup. RECO will connect to the remote database
to resolve pending transactions.

Job Queue Processes

Process Name: J000 through J999 (Originally called SNPn processes)

Max Processes: 1000

Job queue processes carry out batch processing. All scheduled jobs are executed by
these processes. The initialization parameter JOB_QUEUE_PROCESSES specifies the
maximum job processes that can be run concurrently. If a job fails with some Oracle
error, it is recorded in the alert file and a process trace file is generated. Failure of
the Job queue process will not cause the instance to fail.

Dispatcher

Process Name: Dnnn

Max Processes: -

Intended for Shared server setups (MTS). Dispatcher processes listen to and receive
requests from connected sessions and places them in the request queue for further
processing. Dispatcher processes also pickup outgoing responses from the result
queue and transmit them back to the clients. Dnnn are mediators between the client
processes and the shared server processes. The maximum number of Dispatcher
process can be specified using the initialization parameter MAX_DISPATCHERS.

Shared Server Processes

Process Name: Snnn

Max Processes: -

Intended for Shared server setups (MTS). These processes pickup requests from the
call request queue, process them and then return the results to a result queue. The

12
number of shared server processes to be created at instance startup can be specified
using the initialization parameter SHARED_SERVERS.

Parallel Execution Slaves

Process Name: Pnnn

Max Processes: -

These processes are used for parallel processing. It can be used for parallel
execution of SQL statements or recovery. The Maximum number of parallel
processes that can be invoked is specified by the initialization parameter
PARALLEL_MAX_SERVERS.

Trace Writer

Process Name: TRWR

Max Processes: 1

Trace writer writes trace files from an Oracle internal tracing facility.

Input/Output Slaves

Process Name: Innn

Max Processes: -

These processes are used to simulate asynchronous I/O on platforms that do not
support it. The initialization parameter DBWR_IO_SLAVES is set for this purpose.

Wakeup Monitor Process

Process Name: WMON

Max Processes: -

This process was available in older versions of Oracle to alarm other processes that
are suspended while waiting for an event to occur. This process is obsolete and has
been removed.

Conclusion

With every release of Oracle, new background processes have been added and some
existing ones modified. These processes are the key to the proper working of the
database. Any issues related to background processes should be monitored and
analyzed from the trace files generated and the alert log.

Managing Tablespace

A tablespace is a logical storage unit. Why we are say logical because a tablespace is
not visible in the file system. Oracle store data physically is datafiles. A tablespace
consist of one or more datafile.

13
Type of tablespace:

System Tablespace:

 Created with the database

 Required in all database
 Contain the data dictionary

Non System Tablespace:

 Separate undo, temporary, application data and application index segments

Control the amount of space allocation to the user’s objects
 Enable more flexibility in database administration

How to Create Tablespace?

CREATE TABLESPACE "tablespace name"

DATAFILE clause SIZE ……. REUSE
MINIMUM EXTENT (This ensure that every used extent size in the tablespace is a
multiple of the integer)
BLOCKSIZE
LOGGING | NOLOGGING (Logging: By default tablespace have all changes written to
redo, Nologging : tablespace do not have all changes written to redo)
ONLINE | OFFLINE (OFFLINE: tablespace unavailable immediately after creation)
PERMANENT | TEMPORARY (Permanent: tablespace can used to hold permanent
object, temporary: tablespace can used to hold temp object)
EXTENT MANAGEMENT clause

Example:

CREATE TABLESPACE "USER1"

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10m REUSE
BLOCKSIZE 8192
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL

How to manage space in Tablespace?

Tablespace allocate space in extent.

Locally managed tablespace:

The extents are managed with in the tablespace via bitmaps. In locally managed
tablespace, all tablespace information store in datafile header and don’t use data
dictionary table for store information. Advantage of locally managed tablespace is
that no DML generate and reduce contention on data dictionary tables and no undo
generated when space allocation or deallocation occurs.

14
Extent Management [Local | Dictionary]

The storage parameters NEXT, PCTINCREASE, MINEXTENTS,

MAXEXTENTS, and DEFAULT STORAGE are not valid for segments
stored in locally managed tablespaces.

To create a locally managed tablespace, you specify LOCAL in the extent

management clause of the CREATE TABLESPACE statement. You then have
two options. You can have Oracle manage extents for you automatically with
the AUTOALLOCATE option, or you can specify that the tablespace is managed
with uniform extents of a specific size (UNIFORM SIZE).

If the tablespace is expected to contain objects of varying sizes requiring

different extent sizes and having many extents, then AUTOALLOCATE is the
best choice.

If you do not specify either AUTOALLOCATE or UNIFORM with the LOCAL

parameter, then AUTOALLOCATE is the default.

Dictionary Managed tablespace

When we declaring a tablespace as a Dictionary Managed, the data dictionary

manages the extents. The Oracle server updates the appropriate tables ([Link]$ and
[Link]$) in the data dictionary whenever an extent is allocated or deallocated.

How to Create a Locally Managed Tablespace?

The following statement creates a locally managed tablespace named USERS, where
AUTOALLOCATE causes Oracle to automatically manage extent size.

CREATE TABLESPACE users

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Alternatively, this tablespace could be created specifying the UNIFORM clause. In this
example, a 512K extent size is specified. Each 512K extent (which is equivalent to
64 Oracle blocks of 8K) is represented by a bit in the bitmap for this file.

CREATE TABLESPACE users

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

How to Create a Dictionary Managed Tablespace?

The following is an example of creating a DICTIONARY managed tablespace in

Oracle9i:

CREATE TABLESPACE users

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE (

15
INITIAL 64K
NEXT 64K
MINEXTENTS 2
MAXEXTENTS 121
PCTINCREASE 0);

What is Segment Space Management Options?

Two choices for segment-space management, one is manual (the default)

and another auto.

Manual: This is default option. This option use free lists for managing free
space within segments. What are free lists: Free lists are lists of data blocks
that have space available for inserting new rows.

Auto: This option use bitmaps for managing free space within segments.
This is typically called automatic segment-space management

Example:

CREATE TABLESPACE users

DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT
ONLINE;

How to Convert between LMT and DMT Tablespace?

The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert

between LMT and DMT mode. Look at these examples:

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');

PL/SQL procedure successfully completed.

Important Query related to Tablespace

How to retrieve tablespace default storage Parameters?

SELECT TABLESPACE_NAME "TABLESPACE",

INITIAL_EXTENT "INITIAL_EXT",
NEXT_EXTENT "NEXT_EXT",
MIN_EXTENTS "MIN_EXT",
MAX_EXTENTS "MAX_EXT",
PCT_INCREASE
FROM DBA_TABLESPACES;

16
TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE
---------- ----------- -------- ------- ------- ------------
RBS 1048576 1048576 2 40 0
SYSTEM 106496 106496 1 99 1
TEMP 106496 106496 1 99 0
TESTTBS 57344 16384 2 10 1
USERS 57344 57344 1 99 1

How to retrieve information tablesapce and associated datafile?

SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME

FROM DBA_DATA_FILES;

FILE_NAME BLOCKS TABLESPACE_NAME

------------ ---------- -------------------
/U02/ORACLE/IDDB3/[Link] 1536 RBS
/U02/ORACLE/IDDB3/[Link] 6586 SYSTEM
/U02/ORACLE/IDDB3/[Link] 6400 TEMP
/U02/ORACLE/IDDB3/[Link] 6400 TESTTBS
/U02/ORACLE/IDDB3/[Link] 384 USERS

How to retrive Statistics for Free Space (Extents) of Each Tablespace?

SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,

COUNT(*) "PIECES",
MAX(blocks) "MAXIMUM",
MIN(blocks) "MINIMUM",
AVG(blocks) "AVERAGE",
SUM(blocks) "TOTAL"
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;

TABLESPACE FILE_ID PIECES MAXIMUM MINIMUM AVERAGE TOTAL

---------- ------- ------ ------- ------- ------- ------
RBS 2 1 955 955 955 955
SYSTEM 1 1 119 119 119 119
TEMP 4 1 6399 6399 6399 6399
TESTTBS 5 5 6364 3 1278 6390
USERS 3 1 363 363 363 363
PIECES shows the number of free space extents in the tablespace file, MAXIMUM and
MINIMUM show the largest and smallest contiguous area of space in database blocks,
AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows
the amount of free space in each tablespace file in blocks. This query is useful when
you are going to create a new object or you know that a segment is about to extend,
and you want to make sure that there is enough space in the containing tablespace.

Managing Datafiles

17
What is datafile?

Datafiles are physical files of the OS that store the data of all logical structures in the
database. Datafile must be created for each tablespace.

How to determine the number of datafiles?

At least one datafile is required for the SYSTEM tablespace. We can create separate
datafile for other tablespace. When we create DATABASE , MAXDATAFILES may be
or not specify in create database statement clause. Oracle assassin db_files default
value to 200. We can also specify the number of datafiles in init file.

When we start the oracle instance , the DB_FILES initialization parameter reserve
for datafile information and the maximum number of datafile in SGA. We can change
the value of DB_FILES (by changing the initialization parameter setting), but the new
value does not take effect until you shut down and restart the instance.

Important:

 If the value of DB_FILES is too low, you cannot add datafiles beyond the
DB_FILES limit. Example : if init parameter db_files set to 2 then you can not
add more then 2 in your database.
 If the value of DB_FILES is too high, memory is unnecessarily consumed.
 When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the
MAXDATAFILES parameter specifies an initial size. However, if you attempt to
add a new file whose number is greater than MAXDATAFILES, but less than or
equal to DB_FILES, the control file will expand automatically so that the
datafiles section can accommodate more files.

Note:

If you add new datafiles to a tablespace and do not fully specify the filenames, the
database creates the datafiles in the default database directory . Oracle recommends
you always specify a fully qualified name for a datafile. Unless you want to reuse
existing files, make sure the new filenames do not conflict with other files. Old files
that have been previously dropped will be overwritten.

How to add datafile in execting tablespace?

alter tablespace <Tablespace_Name> add datafile ‘/............../......./[Link]’ size

10m autoextend on;

How to resize the datafile?

alter database datafile '/............../......./[Link]' resize 100M;

How to bring datafile online and offline?

alter database datafile '/............../......./[Link]' online;

alter database datafile '/............../......./[Link]' offline;

18
How to rename the datafile in a single tablespace?

Step:1 Take the tablespace that contains the datafiles offline. The database must be
open.

alter tablespace <Tablespace_Name> offline normal;

Step:2 Rename the datafiles using the operating system.

Step:3 Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to
change the filenames within the database.

alter tablespace <Tablespace_Name> rename datafile '/...../..../..../[Link]' to

'/..../..../.../[Link]';

Step 4: Back up the database. After making any structural changes to a database,
always perform an immediate and complete backup.

How to relocate datafile in a single tablespace?

Step:1 Use following query to know the specifiec file name or size.

select file_name,bytes from dba_data_files where

tablespace_name='<tablespace_name>';

Step:2 Take the tablespace containing the datafiles offline:

alter tablespace <Tablespace_Name> offline normal;

Step:3 Copy the datafiles to their new locations and rename them using the
operating system.

Step:4 Rename the datafiles within the database.

ALTER TABLESPACE <Tablespace_Name> RENAME DATAFILE

'/u02/oracle/rbdb1/[Link]', '/u02/oracle/rbdb1/[Link]'
TO '/u03/oracle/rbdb1/[Link]','/u04/oracle/rbdb1/[Link]';

Step:5 Back up the database. After making any structural changes to a database,
always perform an immediate and complete backup.

How to Renaming and Relocating Datafiles in Multiple Tablespaces?

Step:1 Ensure that the database is mounted but closed.

Step:2 Copy the datafiles to be renamed to their new locations and new names,
using the operating system.

Step:3 Use ALTER DATABASE to rename the file pointers in the database control file.

19
ALTER DATABASE RENAME FILE
'/u02/oracle/rbdb1/[Link]','/u02/oracle/rbdb1/[Link]'
TO '/u02/oracle/rbdb1/[Link]','/u02/oracle/rbdb1/[Link];

Step:4 Back up the database. After making any structural changes to a database,
always perform an immediate and complete backup.

How to Drop Datafile?

How to drop a datafile from a tablespace

Important : Oracle does not provide an interface for dropping datafiles in the same
way you would drop a schema object such as a table or a user.

Reasons why you want to remove a datafile from a tablespace:

 You may have mistakenly added a file to a tablespace.

 You may have made the file much larger than intended and now want to
remove it.
 You may be involved in a recovery scenario and the database won't start
because a datafile is missing.

Important : Once the DBA creates a datafile for a tablespace, the datafile cannot be
removed. If you want to do any critical operation like dropping datafiles, ensure you
have a full backup of the database.

Step: 1 Determining how many datafiles make up a tablespace

To determine how many and which datafiles make up a tablespace, you can use the
following query:

SELECT file_name, tablespace_name FROM dba_data_files WHERE tablespace_name

='<name of tablespace>';

Case 1:

If you have only one datafile in the tablespace and you want to remove it. You can
simply drop the entire tablespace using the following:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

The above command will remove the tablespace, the datafile, and the tablespace's
contents from the data dictionary.

Important : Oracle will not drop the physical datafile after the DROP TABLESPACE
command. This action needs to be performed at the operating system.

Case 2:

20
If you have more than one datafile in the tablespace, and you want to remove all
datafiles and also no need the information contained in that tablespace, then use the
same command as above:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

Case 3:

If you have more than one datafile in the tablespace and you want to remove only
one or two ( not all) datafile in the tablesapce or you want to keep the objects that
reside in the other datafile(s) which are part of this tablespace, then you must export
all the objects inside the tablespace.

Step: 1 Gather information on the current datafiles within the tablespace by running
the following query in SQL*Plus:

SELECT file_name, tablespace_name FROM dba_data_files WHERE tablespace_name

='<name of tablespace>';

Step: 2 You now need to identify which objects are inside the tablespace for the
purpose of running an export. To do this, run the following query:

SELECT owner, segment_name, segment_type FROM dba_segments WHERE

tablespace_name='<name of tablespace>'

Step : 3 Now, export all the objects that you wish to keep.

Step : 4 Once the export is done, issue the

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS.

Step : 5 Delete the datafiles belonging to this tablespace using the operating
system.

Step : 6 Recreate the tablespace with the datafile(s) desired, then import the
objects into that tablespace.

Case : 4

If you do not want to follow any of these procedures, there are other things that can
be done besides dropping the tablespace.

 If the reason you wanted to drop the file is because you mistakenly created
the file of the wrong size, then consider using the RESIZE command.
 If you really added the datafile by mistake, and Oracle has not yet allocated
any space within this datafile, then you can use ALTER DATABASE DATAFILE
<filename> RESIZE; command to make the file smaller than 5 Oracle blocks.
If the datafile is resized to smaller than 5 oracle blocks, then it will
never be considered for extent allocation. At some later date, the
tablespace can be rebuilt to exclude the incorrect datafile.

21
Important : The ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP
command is not meant to allow you to remove a datafile. What the command really
means is that you are offlining the datafile with the intention of dropping the
tablespace.

Important : If you are running in archivelog mode, you can also use: ALTER
DATABASE DATAFILE <datafile name> OFFLINE; instead of OFFLINE DROP. Once
the datafile is offline, Oracle no longer attempts to access it, but it is still considered
part of that tablespace. This datafile is marked only as offline in the controlfile and
there is no SCN comparison done between the controlfile and the datafile during
startup (This also allows you to startup a database with a non-critical datafile
missing). The entry for that datafile is not deleted from the controlfile to give us the
opportunity to recover that datafile.

22
23
24
 UNDO TABLESPACE

Before commit, Oracle Database keeps records of actions of transaction because

Oracle needs this information to rollback or Undo the Changes.

What is the main [Link] Parameters for Automatic Undo Management?

UNDO_MANAGEMENT:

The default value for this parameter is MANUAL. If you want to set the database in
an automated mode, set this value to AUTO. (UNDO_MANAGEMENT = AUTO)

UNDO_TABLESPACE:

25
UNDO_TABLESPACE defines the tablespaces that are to be used as Undo
Tablespaces. If no value is specified, Oracle will use the system rollback segment to
startup. This value is dynamic and can be changed online (UNDO_TABLESPACE =
<Tablespace_Name>)

UNDO_RETENTION:

The default value for this parameter is 900 Secs. This value specifies the amount of
time, Undo is kept in the tablespace. This applies to both committed and
uncommitted transactions since the introduction of FlashBack Query feature in Oracle
needs this information to create a read consistent copy of the data in the past.

UNDO_SUPRESS_ERRORS:

Default values is FALSE. Set this to true to suppress the errors generated when
manual management SQL operations are issued in an automated management
mode.

How to Creating UNDO Tablespaces?

UNDO tablespaces can be created during the database creation time or can be added
to an existing database using the create UNDO Tablespace command

Scripts at the time of Database creation:

CREATE DATABASE <DB_NAME>

MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
DATAFILE '<DISK>:\Directory\<FILE_NAME>.DBF' SIZE 204800K REUSE
AUTOEXTEND ON NEXT 20480K MAXSIZE 32767M
UNDO TABLESPACE "<UNDO_TABLESPACE_NAME>"
DATAFILE '<DISK>:\DIRECTORY\<FILE_NAME>.DBF’ SIZE 1178624K REUSE
AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 (<DISK>:\DIRECTORY\<FILE_NAME>.LOG') SIZE 5024K,
GROUP 2 ('<DISK>:\DIRECTORY\<FILE_NAME>.LOG') SIZE 5024K,
GROUP 3 (<DISK>:\DIRECTORY\<FILE_NAME>.LOG') SIZE 5024K;

Scripts after creating Database:

CREATE UNDO TABLESPACE "<UNDO_TABLESPACE_NAME"

DATAFILE '<DISK>:\DIRECTORY\<FILE_NAME>.DBF' SIZE 1178624K REUSE
AUTOEXTEND ON;

How to Dropping an Undo Tablespace?

26
You cannot drop Active undo tablespace. Means, undo tablespace can only be
dropped if it is not currently used by any instance. Use the DROP TABLESPACE
statement to drop an undo tablespace and all contents of the undo tablespace are
removed.

Example:

DROP TABLESPACE <UNDO_TABLESPACE_NAME> including contents;

How to Switching Undo Tablespaces?

We can switch form one undo tablespace to another undo tablespace. Because the
UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER
SYSTEM SET statement can be used to assign a new undo tablespace.

Step 1: Create another UNDO TABLESPACE

CREATE UNDO TABLESPACE "<ANOTHER_UNDO_TABLESPACE>"

DATAFILE '<DISK>:\Directory\<FILE_NAME>.DBF' SIZE 1178624K REUSE
AUTOEXTEND ON;

Step 2: Switches to a new undo tablespace:

alter system set UNDO_TABLESPACE=<UNDO_TABLESPACE>;

Step 3: Drop old UNDO TABLESPACE

drop tablespace <UNDO_TABLESPACE> including contents;

IMPORTANT:

The database is online while the switch operation is performed, and user transactions
can be executed while this command is being executed. When the switch operation
completes successfully, all transactions started after the switch operation began are
assigned to transaction tables in the new undo tablespace.

The switch operation does not wait for transactions in the old undo tablespace to
commit. If there is any pending transactions in the old undo tablespace, the old undo
tablespace enters into a PENDING OFFLINE mode (status). In this mode, existing
transactions can continue to execute, but undo records for new user transactions
cannot be stored in this undo tablespace.

An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch
operation completes successfully. A PENDING OFFLINE undo tablespace cannot used
by another instance, nor can it be dropped. Eventually, after all active transactions
have committed, the undo tablespace automatically goes from the PENDING OFFLINE
mode to the OFFLINE mode. From then on, the undo tablespace is available for other
instances (in an Oracle Real Application Cluster environment).

If the parameter value for UNDO TABLESPACE is set to '' (two single quotes), the
current undo tablespace will be switched out without switching in any other undo

27
tablespace. This can be used, for example, to unassign an undo tablespace in the
event that you want to revert to manual undo management mode.

The following example unassigns the current undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = '';

How to Monitoring Undo Space?

The V$UNDOSTAT view is useful for monitoring the effects of transaction execution
on undo space in the current instance. Statistics are available for undo space
consumption, transaction concurrency, and length of queries in the instance.

The following example shows the results of a query on the V$UNDOSTAT view.

SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT,

MAXCONCURRENCY AS "MAXCON" FROM V$UNDOSTAT;

Managing Control Files

A control file is a small binary file that records the physical structure of the database
with database name, Names and locations of associated datafiles, online redo log
files, timestamp of the database creation, current log sequence number and
Checkpoint information.
Note:
 Without the control file, the database cannot be mounted.
 You should create two or more copies of the control file during database
creation.
Role of Control File:
When Database instance mount, Oracle recognized all listed file in Control file and
open it. Oracle writes and maintains all listed control files during database operation.
Important:
 If you do not specify files for CONTROL_FILES before database creation, and
you are not using the Oracle Managed Files feature, Oracle creates a control
file in <DISK>:\ORACLE_HOME\DTATBASE\ location and uses a default
filename. The default name is operating system specific.
 Every Oracle database should have at least two control files, each stored on a
different disk. If a control file is damaged due to a disk failure, the associated
instance must be shut down.
 Oracle writes to all filenames listed for the initialization parameter
CONTROL_FILES in the database's initialization parameter file.
 The first file listed in the CONTROL_FILES parameter is the only file read by
the Oracle database server during database operation.
 If any of the control files become unavailable during database operation, the
instance becomes inoperable and should be aborted.
How to Create Control file at the time of database creation:
The initial control files of an Oracle database are created when you issue the CREATE

28
DATABASE statement. The names of the control files are specified by the
CONTROL_FILES parameter in the initialization parameter file used during database
creation.
How to Create Additional Copies, Renaming, and Relocating Control Files
Step:1 Shut down the database.
Step:2 Copy an existing control file to a different location, using operating system
commands.
Step:3 Edit the CONTROL_FILES parameter in the database's initialization parameter
file to add the new control file's name, or to change the existing control filename.
Step:4 Restart the database.
When you Create New Control Files?
 All control files for the database have been permanently damaged and you do
not have a control file backup.
 You want to change one of the permanent database parameter settings
originally specified in the CREATE DATABASE statement. These settings
include the database's name and the following parameters: MAXLOGFILES,
MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.
Steps for Creating New Control Files
Step:1 Make a list of all datafiles and online redo log files of the database.
SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE;
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'CONTROL_FILES';

Step:2 Shut down the database.

Step:3 Back up all datafiles and online redo log files of the database.
Step:4 Start up a new instance, but do not mount or open the database:
STARTUP NOMOUNT
Step:5 Create a new control file for the database using the CREATE CONTROLFILE
statement.
Example:
CREATE CONTROLFILE REUSE DATABASE "<DB_NAME" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '<DISK>:\Directory\[Link]' SIZE 5024K,
GROUP 2 '<DISK>:\Directory\[Link]' SIZE 5024K,
GROUP 3 '<DISK>:\Directory\[Link]' SIZE 5024K
# STANDBY LOGFILE
DATAFILE
'<DISK>:\Directory\[Link]',
'<DISK>:\Directory\[Link]'

29
CHARACTER SET WE8MSWIN1252
;
Step:6 Open the database using one of the following methods:
 If you specify NORESETLOGS when creation the control file, use following
commands: ALTER DATABASE OPEN;
 If you specified RESETLOGS when creating the control file, use the ALTER
DATABASE statement, indicating RESETLOGS.

ALTER DATABASE OPEN RESETLOGS;

TIPS:
When creating a new control file, select the RESETLOGS option if you have lost any
online redo log groups in addition to control files. In this case, you will need to
recover from the loss of the redo logs . You must also specify the RESETLOGS option
if you have renamed the database. Otherwise, select the NORESETLOGS option.
Backing Up Control Files
Method 1:
Back up the control file to a binary file (duplicate of existing control file) using the
following statement:

ALTER DATABASE BACKUP CONTROLFILE TO '<DISK>:\Directory\[Link]';

Method 2:
Produce SQL statements that can later be used to re-create your control file:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

How to retrieve information related to Control File:
V$DATABASE:
Displays database information from the control file
V$CONTROLFILE:
Lists the names of control files
V$CONTROLFILE_RECORD_SECTION:
Displays information about control file record sections

Managing Redo Log Files

Redo logs consists of two or more pre allocated files that store all changes made to
the database. Every instance of an Oracle database has an associated online redo log
to protect the database in case of an instance failure.

Main points to consider before creating redo log files?

 Members of the same group should be stores in separate disk so that no

single disk failure can cause LGWR and database instance to fail.

30
  Set the archive destination to separate disk other than redo log members to
avoid contention between LGWR and Arch.
 With mirrored groups of online redo logs , all members of the same group
must be the same size.

What are the parameters related to Redo log files?

Parameters related to redo log files are

 MAXLOGFILES
 MAXLOGMEMEBERS

MAXLOGFILES and MAXLOGMEMEBERS parameters are defined while creation of

database. You can increase these parameters by recreating the control file.

How do you create online Redo log group?

Alter database add logfile group <group Number>

(‘<DISK>:\Directory\<LOG_FILE_NAME>.log’,’
(‘<DISK>:\Directory\<LOG_FILE_NAME>.log’) size 500K;

How to check the status of added redo log group?

Select * from v$log;

Interpretation:

Here you will observe that status is UNUSED means that this redo log file is not
being used by oracle as yet. ARC is the archived column in v$log , it is by default
YES when you create a redo log file. It will returns to NO if the system is not in
archive log mode and this file is used by oracle. Sequence# 0 also indicate that it is
not being used as yet.

How to create online redo log member ?

alter database add logfile member

'<DISK>:\Directory\<LOG_FILE_NAME>.log’,'<DISK>:\Directory\<LOG_FILE_NAME
>.log’' to group <GROUP NUMBER>;

How to rename and relocate online redo log members ?

Important: Take the backup before renaming and relocating.

Step:1 Shutdown the database .

Step:2 Startup the database as startup mount.

Step:3 Copy the desired redo log files to new location . You can change the name of
redo log file in the new location.

31
Step:4 Alter database rename file ‘<DISK>:\Directory\<LOG_FILE_NAME>.log’ to
‘<new path><DISK>:\Directory\<LOG_FILE_NAME>.log’,

Step:5 Alter database open;

Step: 6 Shutdown the database normal and take the backup.

How to drop online redo log group?

Important:

 You must have at- least two online groups.

 You can not drop a active online redo log group. If it active switch it by alter
system switch logfile before dropping.
 Also make sure that online redo log group is archived ( if archiving is
enabled).

Syntax:

If you want to drop log group:

Alter database drop logfile group <GROUP_NUMBER>;

If you want to drop a logfile member:

Alter database drop logfile member ’ <DISK>:\Directory\<LOG_FILE_NAME>.log’;

How to Viewing Online Redo Log Information?

SELECT * FROM V$LOG;

GROUP# THREAD# SEQ BYTES MEMBERS ARC STATUS FIRST_CHANGE#

FIRST_TIM
------ ------- ----- ------- ------- --- --------- ------------- ---------
1 1 10605 1048576 1 YES ACTIVE 11515628 16-APR-00
2 1 10606 1048576 1 NO CURRENT 11517595 16-APR-00
3 1 10603 1048576 1 YES INACTIVE 11511666 16-APR-00
4 1 10604 1048576 1 YES INACTIVE 11513647 16-APR-00

SELECT * FROM V$LOGFILE;

GROUP# STATUS MEMBER

------ ------- ----------------------------------
1 D:\ORANT\ORADATA\IDDB2\[Link]
2 D:\ORANT\ORADATA\IDDB2\[Link]
3 D:\ORANT\ORADATA\IDDB2\[Link]
4 D:\ORANT\ORADATA\IDDB2\[Link]
If STATUS is blank for a member, then the file is in use.

32
 User-Managed Backup Terminology

(Operating system command are used to make backups when database is closed or
open in this terminology)

Whole database backup refer to a backup of all data file, control file and log file of
the database. whole database backup can be perform when database open or closed.

The backup takes when database is closed called consistent backup. (Because
database file header are consistent with the control file and when restore completely
the database can be opened without any recovery.)

The backup takes when database is opened and operational called inconsistent
backup. (Because database file header are not consistent with the control file.)

Physical Backup Method

Database Operation Mode Recovery Senerio

Archive log mode recover to the point of failure
No Archive log mode recover to the point of the last backup

Querying View to obtain Database file Information

V$database( use for obtaining data file information)

v$controlfile( user for obtaining control file information)
v$logfile ( user for obtaining log file information)

Use the v$tablespace and v$datafile data dictonery view to obtain a list of all
datafiles and there respective tablespace.

SQL> SELECT [Link] TABLESPACE,[Link] DATAFILE

FROM V$TABLESPACE T, V$DATAFILE F
WHERE [Link]# = [Link]#
ORADER BY [Link];

Making a consistent whole Database Backup

 Shutdown the database.

 Backup all data file, control file and log file by using an operating system
command. we can also include password file and parameter file.
 Restart the oracle database/Instance.

Making a inconsistent whole database backup

Requirement for inconstant database backup:

 The database is set to ARCHIVELOG mode.

 You ensure that the online redo logs are archived, either by enabling the
Oracle automatic archiving (ARCn) process.

33
 Making a Backup of an Online teblespace or Data file

 Set the datafile or tablespace is backup mode by issuing following command:

SQL> ALTER TABLESPACE <TABLESPACE_NAME> BEGIN BACKUP;

(Note:This prevent the sequence number in the datafile header from changing.)

 Use an operating system backup utility to copy all database in the tablespace
to backup storage.

Copy c:\datafile_path e:\datafilepath

 After the datafile of the tablespace have been backed up, set them into mode
by issuing the following command:

SQL> ALTER TABLESPACE <TABLESPACE_NAME> END BACKUP;

 Archive the unarchive redo logs;

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Repeat these steps for all tablespaces.

Mechanism of Open database backup

When a datafile is placed in backup mode, more redo log entries may be generated
because the log writer writes block image of changes block of the datafile in backup
mode to the redo log instead of just the row information

Backup Status Information( When performing open database)

select * from v$backup; (view to determine which file are in backup mode, when
alter tablespace begin backup command is issued the status change to ACTIVE.)

Manual Control File Backups

 Creating a binary image:

ALTER DATABASE BACKUP CONTROLFILE TO '[Link]';

 Creating a taxt trace file:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Backing Up the Initilization Parameter File

CREATE PFILE FROM SPFILE;( For Default location)

34
CREATE PFILE ='C:\BACKUP\[Link]' FRoM SPFILE;

Backup Verification (Command line Interface)

Use to ensure that a backup database or datafile is valid before a restore.

>dbv file='path of file location' start=1 logfile='enter path for log file generation'

Backup Issue with Logging and nologging Option

Tablespace, table, index may be use to set to NOLOGGING mode for Faster load of
data when using direct load operation like SQL LOADER.( Because the redo logs do
not contain the values that were inserted when the table was in NOLOGGING mode.

User Managed Database Recovery

Scenario: (When you take cold backup means consistent backup)

If your database is running in archive log mode and you take cold backup means
consistent backup (all data file, control file and redo log file) in every Sunday and
Monday to Saturday you take only backup of archive log file.

Recovery from missing or corrupted datafile(s):

Case 1:

You have Sunday cold backup and you have also Monday to Wednesday archive log
file backup. Suppose any data file corrupt or missed on Thursday, how will you
recover database up to Wednesday.

 When u start database by using startup command system show following

error:

SQL> startup
ORACLE instance started.

Total System Global Area 122755896 bytes

Fixed Size 453432 bytes
Variable Size 67108864 bytes
Database Buffers 54525952 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'C:\O\ORADATA\[Link]'

 Read DBWR trace file or alert log file and find details of missing data files.
Restore missing files from backup storage area by using OS Copy command
and try to open database by using alter database open command.

SQL> alter database open;

35
 alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\O\ORADATA\[Link]'
(Error means data file 1 needs media recovery)

 Recover database by using following syntax:

SQL> recover datafile 1;

ORA-00279: change 222132 generated at 06/02/2006 [Link] needed
for thread 1
ORA-00289: suggestion : C:\O\ADMIN\ARCH\ARC00100052
ORA-00280: change 222132 for thread 1 is in sequence #52

Note : Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

Log applied.

Media recovery complete.

 Open databae:

SQL> alter database open;

Your database recovered up to Wednesday.

Recovery from missing or corrupted redo log group:

Case 1: A multiplexed copy of the missing log is available.

if a redo log is missing, it should be restored from a multiplexed copy, if possible.

Here's an example, where I attempt to startup from SQLPlus when a redo log is
missing:

SQL> startup

ORACLE instance started.

Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.

36
 ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'D:\ORACLE_DATA\LOGS\ORCL\[Link]'

SQL>

To fix this we simply copy [Link] from its multiplexed location on E: to the
above location on D:.

SQL> alter database open;

Database altered.

SQL>

That's it - the database is open for use.

Case 2: Only A redo log file backup copy available

If a redo log is missing, it should be restored from a Cold backup (if redo log backup
available in Sunday Cold Backup) if possible. Here's an example, where I attempt to
startup from SQLPlus when a redo log is missing:

SQL> startup
ORACLE instance started.

Total System Global Area 122755896 bytes

Fixed Size 453432 bytes
Variable Size 67108864 bytes
Database Buffers 54525952 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\O\ORADATA\[Link]'

SQL>

To fix this we simply copy [Link] from Cold Backup.

SQL> alter database clear unarchived logfile group 1;

SQL> alter database open;

Database altered.
SQL>

That's it - the database is open for use.

37
 Case 3: All redo log file or any one redo log file missing and we have no
cbackup copy of redo log file or no multiplexing redo log file.

If all or some redo log is missing. Here's an example, where I attempt to startup
from SQLPlus when a redo log is missing:

SQL> startup
ORACLE instance started.

Total System Global Area 122755896 bytes

Fixed Size 453432 bytes
Variable Size 67108864 bytes
Database Buffers 54525952 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\O\ORADATA\[Link]'

SQL> recover database until cancel;

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

That's it - the database is open for use.

How to perform a disaster recovery of an Oracle server using Manual

Backup Method

[Link]-requisites:
The following are the pre-requisites to fully recover an Oracle database server in the
event a disaster occurs:

A FULL Oracle database backup (all data file, control file and redo log file) using copy
command. When making this backup, make sure the Oracle database is shut down.
This backup set will contain a FULL CLOSED Oracle Database backup. A FULL Oracle
database backup should be performed every time any changes are made to the
physical and/or logical structure of the Oracle database and forms the base for
recovering the database server to a fully working order.

Archive log file backup up to the time of the server failure.

Control file up to the time of the server failures.

Scenario

38
if your database is running in archive log mode and every Sunday you take full/cold
backup of database (all data file , control file and redolog file ) and every day
Monday to Saturday you take only archive log file backup. If a situation in which
your database server has been destroyed at Saturday , how will u recover data up to
Saturday

Steps

1. Build the server

You need a server to host the database, so the first step is to acquire or build the
new machine. This is not strictly a DBA task, so we won't delve into details here. The
main point to keep in mind is that the replacement server should, as far as possible,
be identical to the old one. In particular, pay attention to the following areas:

Disk layout and capacity: Ideally the server should have the same number of disks
as the original. This avoids messy renaming of files during recovery. Obviously, the
new disks should also have enough space to hold all software and data that was on
the original server.

Operating system, service pack and patches: The operating system environment
should be the same as the original, right up to service pack and patch level.

Memory: The new server must have enough memory to cater to Oracle and
operating system / other software requirements. Oracle memory structures (Shared
pool, db buffer caches etc) will be sized identically to the original database instance.
Use of the backup server parameter file will ensure this.

4. Install Oracle Software

Now we get to the meat of the database recovery process. The next step is to install
Oracle software on the machine. The following points should be kept in mind when
installing the software:

Install the same version of Oracle as was on the destroyed server. The version
number should match right down to the patch level, so this may be a multi-step
process involving installation followed by the application of one or more patchsets
and patches.

Do not create a new database at this stage.

Create a listener using the Network Configuration Assistant. Ensure that it has the
same name and listening ports as the original listener. Relevant listener
configuration information can be found in the backed up [Link] file.

4. Create directory structure for database files

After software installation is completed, create all directories required for datafiles,
(online and archived) logs, control files and backups. All directory paths should
match those on the original server. This, though not mandatory, saves additional
steps associated with renaming files during recovery.

39
Don't worry if you do not know where the database files should be located. You can
obtain the required information from the backup spfile and control file at a later
stage. Continue reading - we'll come back to this later.

5. Create Oracle service

An Oracle service must be exist before a database is created. The service is created
using the oradim utility, which must be run from the command line. The following
commands show how to create and modify a service (comments in italics, typed
commands in bold):

--create a new service with auto startup

C:\>oradim -new -sid ORCL -intpwd ORCL -startmode a

Unfortunately oradim does not give any feedback, but you can check that the service
exists via the Services administrative panel. The service has been configured to start
automatically when the computer is powered up. Note that oradim offers options to
delete, startup and shutdown a service. See the documentation for details.

3. Restore backup from tape

The next step is to get your backup from tape on to disk.

6. Restore and recover database

If an Oracle database server experienced a disaster such as a hard disk failure, use
this procedure to recover the server and the Oracle databases:

Shutdown database

SQL> SHUTDOWN IMMEDIATE

Restore all data file, log file from cold backup and restore all archive log file from
Cold backup to disaster.

Restore current control file at the time of Disaster recovery.

When the restore operation completes, move to the Oracle database server

Start server manager, connect as Internal and start the database but only mount it
by typing:

SQL> STARTUP MOUNT

When the database is mounted, type:

RECOVER DATABASE USING BACKUP CONTROLFILE

40
 Note :Oracle will respond to this command by returning the following message,
suggesting a log sequence to apply.

ORA-00279: Change 36579 generated at <time/date> needed for

thread 1

ORA-00289: Suggestion :
\Oracle_Home\Oradata\<SID>\%SID%[Link]

ORA-00280: {<RET>=Suggested | filename | AUTO | FROM logsource

| CANCEL}

At the prompt, type:

AUTO

Then press <Enter>

This will automatically apply all archived log sequences required to recover the
database (assuming all archived redo logs are available in the location specified
in the [Link] parameter and that the format corresponds to the format
specified).

It is possible that a final non-archived log sequence is requested to complete the

recovery. This will only hold one System Change Number (SCN) and no
transactions relating to the database, up to, and including the time of the FULL
ONLINE Oracle backup. If this is the case, the following message will be returned
by Oracle:

ORA-00308: cannot open archived log

'E:\ORACLE\ORADATA\KIMSTAD\ARCHIVE\[Link]
'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

To finish the recovery, stay in server manager with the database mounted, and type:

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

Then press <Enter>

9. When Oracle requests this final sequence again, type:

41
 CANCEL

Then press <Enter>

10. Oracle will return the following message:

Media recovery canceled

The media recovery of the database is complete.

11. To open the database and to synchronize the log sequence, type:

ALTER DATABASE OPEN RESETLOGS

Then press <Enter>

The Oracle database server is now restored to full working order up to the time
of the latest full online Oracle backup.

Backup Recovery Example

1. Backup all data file and Control file by using following commands on
every Sunday.

(BACKUP SCRIPTS)

host ocopy C:\Oracle\RDBMS\*.001 e:\temp

host del C:\Oracle\RDBMS\*.001 /q
set echo on
alter tablespace system begin backup;
host ocopy E:\LOCAL\ORADATA\[Link] e:\bu
alter tablespace system end backup;
alter tablespace undotbs begin backup;
host ocopy E:\LOCAL\ORADATA\[Link] e:\bu
alter tablespace undotbs end backup;
alter tablespace data begin backup;
host ocopy E:\LOCAL\ORADATA\[Link] e:\bu
alter tablespace data end backup;
alter tablespace "INDEX" begin backup;
host ocopy E:\LOCAL\ORADATA\FLEX_INDEX.DBF e:\bu
alter tablespace "INDEX" end backup;
alter database backup controlfile to 'e:\bu\[Link]' reuse ;
host ocopy C:\Oracle\RDBMS\*.001 e:\bu /q

2. Backup all Archivelog files by using following commands on Daily Basic.

host ocopy C:\Oracle\RDBMS\*.001 e:\local\bu /q

3. Suppose You have Sunday hot backup and you have also Monday to

42
Wednesday archive log file backup. Suppose any data file corrupt or missed
on Thursday, how will you recover database up to Wednesday.

4. When you triying to open database following error accore:

SQL> startup
ORACLE instance started.

Total System Global Area 118255568 bytes

Fixed Size 282576 bytes
Variable Size 62914560 bytes
Database Buffers 54525952 bytes
Redo Buffers 532480 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'E:\LOCAL\ORADATA\[Link]'

5. Check backup status and messing file (Required recovery) and current log
sicquence of all data file.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 FILE NOT FOUND 0
2 NOT ACTIVE 314014 13-OCT-06
3 NOT ACTIVE 314021 13-OCT-06
4 NOT ACTIVE 314027 13-OCT-06

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

---------- ------- ------- ----------------------------------------------------------------- ---
------- ---------
1 ONLINE ONLINE FILE NOT FOUND 0

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#

FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------
---
1 1 304 52428800 1 NO CURRENT 335069 14-OCT-06
2 1 302 52428800 1 YES INACTIVE 335067 14-OCT-06
3 1 303 52428800 1 YES INACTIVE 335068 14-OCT-06

6. Restore datafile from backup location.

7. Try to open database by using following command:

43
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\LOCAL\ORADATA\[Link]'

8. SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 ACTIVE 334936 14-OCT-06
2 NOT ACTIVE 334942 14-OCT-06
3 ACTIVE 334949 14-OCT-06
4 NOT ACTIVE 334977 14-OCT-06

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

---------- ------- ------- ----------------------------------------------------------------- ---
------- ---------
1 ONLINE ONLINE 334936 14-OCT-06
3 ONLINE ONLINE 334949 14-OCT-06

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#

FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------
---
1 1 304 52428800 1 NO CURRENT 335069 14-OCT-06
2 1 302 52428800 1 YES INACTIVE 335067 14-OCT-06
3 1 303 52428800 1 YES INACTIVE 335068 14-OCT-06

SQL> select * from v$log_history;

RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#

---------- ---------- ---------- ---------- ------------- --------- ------------
1 603754350 1 1 313930 13-OCT-06 314115
2 603754360 1 2 314115 13-OCT-06 314116
3 603754369 1 3 314116 13-OCT-06 314118
4 603754369 1 4 314118 13-OCT-06 314119
5 603754374 1 5 314119 13-OCT-06 314120
6 603754374 1 6 314120 13-OCT-06 314121
7 603754379 1 7 314121 13-OCT-06 314122
8 603754379 1 8 314122 13-OCT-06 314123
9 603754385 1 9 314123 13-OCT-06 314124
10 603754385 1 10 314124 13-OCT-06 314125
11 603754390 1 11 314125 13-OCT-06 314126

303 603822241 1 303 335068 14-OCT-06 335069

44
SQL> recover datafile 1;
ORA-00279: change 334936 generated at 10/14/2006 [Link] needed for thread 1
ORA-00289: suggestion : C:\ORACLE\RDBMS\ARC00208.001
ORA-00280: change 334936 for thread 1 is in sequence #208

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

SQL*Plus: Release [Link].1 - Production on Fri Oct 13 [Link] 2006

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SP2-0306: Invalid option.

Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_string>] | /

Connected to:
Oracle9i Enterprise Edition Release [Link].1 - Production
With the Partitioning option
JServer Release [Link].1 - Production

SQL> set linesize 7777

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#

FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------
---
1 1 1 52428800 1 NO CURRENT 313930 13-OCT-06
2 1 0 52428800 1 YES UNUSED 0
3 1 0 52428800 1 YES UNUSED 0

SQL> select * from v$recover_file;

no rows selected

SQL> select * from v$bachup;

select * from v$bachup
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 NOT ACTIVE 251373 12-OCT-06
2 NOT ACTIVE 251381 12-OCT-06

45
3 NOT ACTIVE 251388 12-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 NOT ACTIVE 314007 13-OCT-06
2 ACTIVE 314014 13-OCT-06
3 NOT ACTIVE 251388 12-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> /

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 NOT ACTIVE 314007 13-OCT-06
2 ACTIVE 314014 13-OCT-06
3 NOT ACTIVE 251388 12-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> /

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 NOT ACTIVE 314007 13-OCT-06
2 ACTIVE 314014 13-OCT-06
3 NOT ACTIVE 251388 12-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> /

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 NOT ACTIVE 314007 13-OCT-06
2 NOT ACTIVE 314014 13-OCT-06
3 ACTIVE 314021 13-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> /

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 NOT ACTIVE 314007 13-OCT-06
2 NOT ACTIVE 314014 13-OCT-06
3 ACTIVE 314021 13-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> /

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 NOT ACTIVE 314007 13-OCT-06

46
2 NOT ACTIVE 314014 13-OCT-06
3 ACTIVE 314021 13-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> /

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 NOT ACTIVE 314007 13-OCT-06
2 NOT ACTIVE 314014 13-OCT-06
3 ACTIVE 314021 13-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> /

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 NOT ACTIVE 314007 13-OCT-06
2 NOT ACTIVE 314014 13-OCT-06
3 ACTIVE 314021 13-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> /

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 NOT ACTIVE 314007 13-OCT-06
2 NOT ACTIVE 314014 13-OCT-06
3 ACTIVE 314021 13-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> /

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 NOT ACTIVE 314007 13-OCT-06
2 NOT ACTIVE 314014 13-OCT-06
3 NOT ACTIVE 314021 13-OCT-06
4 ACTIVE 314027 13-OCT-06

SQL> /

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 NOT ACTIVE 314007 13-OCT-06
2 NOT ACTIVE 314014 13-OCT-06
3 NOT ACTIVE 314021 13-OCT-06
4 NOT ACTIVE 314027 13-OCT-06

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

47
SQL> startup
ORACLE instance started.

Total System Global Area 118255568 bytes

Fixed Size 282576 bytes
Variable Size 62914560 bytes
Database Buffers 54525952 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

48
SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

49
SQL> /

System altered.

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 118255568 bytes

Fixed Size 282576 bytes
Variable Size 62914560 bytes
Database Buffers 54525952 bytes
Redo Buffers 532480 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'E:\LOCAL\ORADATA\[Link]'

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#

FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------
---
1 1 25 52428800 1 YES INACTIVE 314139 13-OCT-06
2 1 26 52428800 1 NO CURRENT 314140 13-OCT-06
3 1 24 52428800 1 YES INACTIVE 314138 13-OCT-06

SQL> select * from V$log_history;

RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#

---------- ---------- ---------- ---------- ------------- --------- ------------
1 603754350 1 1 313930 13-OCT-06 314115
2 603754360 1 2 314115 13-OCT-06 314116
3 603754369 1 3 314116 13-OCT-06 314118
4 603754369 1 4 314118 13-OCT-06 314119
5 603754374 1 5 314119 13-OCT-06 314120
6 603754374 1 6 314120 13-OCT-06 314121
7 603754379 1 7 314121 13-OCT-06 314122
8 603754379 1 8 314122 13-OCT-06 314123
9 603754385 1 9 314123 13-OCT-06 314124
10 603754385 1 10 314124 13-OCT-06 314125
11 603754390 1 11 314125 13-OCT-06 314126

RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#

---------- ---------- ---------- ---------- ------------- --------- ------------
12 603754390 1 12 314126 13-OCT-06 314127
13 603754395 1 13 314127 13-OCT-06 314128
14 603754395 1 14 314128 13-OCT-06 314129
15 603754401 1 15 314129 13-OCT-06 314130

50
16 603754401 1 16 314130 13-OCT-06 314131
17 603754406 1 17 314131 13-OCT-06 314132
18 603754406 1 18 314132 13-OCT-06 314133
19 603754411 1 19 314133 13-OCT-06 314134
20 603754412 1 20 314134 13-OCT-06 314135
21 603754417 1 21 314135 13-OCT-06 314136
22 603754417 1 22 314136 13-OCT-06 314137

RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#

---------- ---------- ---------- ---------- ------------- --------- ------------
23 603754422 1 23 314137 13-OCT-06 314138
24 603754422 1 24 314138 13-OCT-06 314139
25 603754445 1 25 314139 13-OCT-06 314140

25 rows selected.

SQL> alter database open;

alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'E:\LOCAL\ORADATA\[Link]'

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------
1 ACTIVE 314007 13-OCT-06
2 NOT ACTIVE 314014 13-OCT-06
3 NOT ACTIVE 314021 13-OCT-06
4 NOT ACTIVE 314027 13-OCT-06

SQL> alter * from v$recover_file;

alter * from v$recover_file
*
ERROR at line 1:
ORA-00940: invalid ALTER command

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

---------- ------- ------- ----------------------------------------------------------------- ---
------- ---------
1 ONLINE ONLINE 314007 13-OCT-06

SQL> select * from v$log_history;

RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#

---------- ---------- ---------- ---------- ------------- --------- ------------
1 603754350 1 1 313930 13-OCT-06 314115
2 603754360 1 2 314115 13-OCT-06 314116

51
3 603754369 1 3 314116 13-OCT-06 314118
4 603754369 1 4 314118 13-OCT-06 314119
5 603754374 1 5 314119 13-OCT-06 314120
6 603754374 1 6 314120 13-OCT-06 314121
7 603754379 1 7 314121 13-OCT-06 314122
8 603754379 1 8 314122 13-OCT-06 314123
9 603754385 1 9 314123 13-OCT-06 314124
10 603754385 1 10 314124 13-OCT-06 314125
11 603754390 1 11 314125 13-OCT-06 314126

RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#

---------- ---------- ---------- ---------- ------------- --------- ------------
12 603754390 1 12 314126 13-OCT-06 314127
13 603754395 1 13 314127 13-OCT-06 314128
14 603754395 1 14 314128 13-OCT-06 314129
15 603754401 1 15 314129 13-OCT-06 314130
16 603754401 1 16 314130 13-OCT-06 314131
17 603754406 1 17 314131 13-OCT-06 314132
18 603754406 1 18 314132 13-OCT-06 314133
19 603754411 1 19 314133 13-OCT-06 314134
20 603754412 1 20 314134 13-OCT-06 314135
21 603754417 1 21 314135 13-OCT-06 314136
22 603754417 1 22 314136 13-OCT-06 314137

RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#

---------- ---------- ---------- ---------- ------------- --------- ------------
23 603754422 1 23 314137 13-OCT-06 314138
24 603754422 1 24 314138 13-OCT-06 314139
25 603754445 1 25 314139 13-OCT-06 314140

25 rows selected.

SQL> select 8 from v$datafile;

8
----------
8
8
8
8

SQL> select * from v$datafile;

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED

CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNRECOVER
LAST_CHANGE# LAST_TIME O
---------- ---------------- --------- ---------- ---------- ------- ---------- -----------------
- --------- --------------------- --------- ------------ --------- -
1 4 06-OCT-06 0 1 SYSTEM READ WRITE 314150 13-OCT-06 0 314150 13-OCT-06
2 5827 06-OCT-06 1 2 ONLINE READ WRITE 314150 13-OCT-06 0 314150 13-OCT-
06
3 6752 06-OCT-06 2 3 ONLINE READ WRITE 314150 13-OCT-06 0 314150 13-OCT-
06

52
4 6774 06-OCT-06 3 4 ONLINE READ WRITE 314150 13-OCT-06 0 314150 13-OCT-
06

SQL> select namr from V%archivelogs;

select namr from V%archivelogs
*
ERROR at line 1:
ORA-00911: invalid character

SQL> select * from V$archive_logs;

select * from V$archive_logs
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> select * from V$archive_log;

select * from V$archive_log
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> ed
Wrote file [Link]

1* select * from V$archived_log

SQL> /

RECID STAMP NAME

---------- ---------- -------------------------------------------------------------------------
---------------------------------------------------------------------
1 603465266 C:\ORACLE\RDBMS\ARC00002.001
2 603465267 C:\ORACLE\RDBMS\ARC00003.001
3 603465268 C:\ORACLE\RDBMS\ARC00004.001
4 603491991 C:\ORACLE\RDBMS\ARC00005.001
5 603540234 C:\ORACLE\RDBMS\ARC00006.001
6 603668585 C:\ORACLE\RDBMS\ARC00007.001
7 603668586 C:\ORACLE\RDBMS\ARC00008.001
8 603668592 C:\ORACLE\RDBMS\ARC00009.001
9 603669141
10 603670713 C:\ORACLE\RDBMS\ARC00001.001
11 603670715 C:\ORACLE\RDBMS\ARC00002.001

RECID STAMP NAME

---------- ---------- -------------------------------------------------------------------------
---------------------------------------------------------------------
12 603670719 C:\ORACLE\RDBMS\ARC00003.001
13 603670720 C:\ORACLE\RDBMS\ARC00004.001
14 603670726 C:\ORACLE\RDBMS\ARC00005.001
15 603670728 C:\ORACLE\RDBMS\ARC00006.001
16 603670729 C:\ORACLE\RDBMS\ARC00007.001

53
17 603670733 C:\ORACLE\RDBMS\ARC00008.001
18 603670733 C:\ORACLE\RDBMS\ARC00009.001
19 603670737 C:\ORACLE\RDBMS\ARC00010.001
20 603670737 C:\ORACLE\RDBMS\ARC00011.001
21 603670742 C:\ORACLE\RDBMS\ARC00012.001
22 603670743 C:\ORACLE\RDBMS\ARC00013.001

RECID STAMP NAME

---------- ---------- -------------------------------------------------------------------------
---------------------------------------------------------------------
23 603670748 C:\ORACLE\RDBMS\ARC00014.001
24 603670748 C:\ORACLE\RDBMS\ARC00015.001
25 603670753 C:\ORACLE\RDBMS\ARC00016.001
26 603670753 C:\ORACLE\RDBMS\ARC00017.001
27 603670759 C:\ORACLE\RDBMS\ARC00018.001
28 603670759 C:\ORACLE\RDBMS\ARC00019.001
29 603670759 C:\ORACLE\RDBMS\ARC00020.001
30 603670764 C:\ORACLE\RDBMS\ARC00021.001
31 603670764 C:\ORACLE\RDBMS\ARC00022.001
32 603670769 C:\ORACLE\RDBMS\ARC00023.001
33 603670769 C:\ORACLE\RDBMS\ARC00024.001

RECID STAMP NAME

---------- ---------- -------------------------------------------------------------------------
---------------------------------------------------------------------
34 603671995 C:\ORACLE\RDBMS\ARC00025.001
35 603672005 C:\ORACLE\RDBMS\ARC00026.001
36 603672006 C:\ORACLE\RDBMS\ARC00027.001
37 603672011 C:\ORACLE\RDBMS\ARC00028.001
38 603672011 C:\ORACLE\RDBMS\ARC00029.001
39 603672016 C:\ORACLE\RDBMS\ARC00030.001
40 603672017 C:\ORACLE\RDBMS\ARC00031.001
41 603673030 C:\ORACLE\RDBMS\ARC00032.001
42 603721691 C:\ORACLE\RDBMS\ARC00033.001
43 603722884 C:\ORACLE\RDBMS\ARC00034.001
44 603722886 C:\ORACLE\RDBMS\ARC00035.001

RECID STAMP NAME

---------- ---------- -------------------------------------------------------------------------
---------------------------------------------------------------------
45 603722890 C:\ORACLE\RDBMS\ARC00036.001
46 603722890 C:\ORACLE\RDBMS\ARC00037.001
47 603726744 C:\ORACLE\RDBMS\ARC00038.001
48 603726745 C:\ORACLE\RDBMS\ARC00039.001
49 603726747 C:\ORACLE\RDBMS\ARC00040.001
50 603728810 C:\ORACLE\RDBMS\ARC00041.001
51 603737242 C:\ORACLE\RDBMS\ARC00042.001
52 603737242 C:\ORACLE\RDBMS\ARC00043.001
53 603737242 C:\ORACLE\RDBMS\ARC00044.001
54 603737243 C:\ORACLE\RDBMS\ARC00045.001
55 603737244 C:\ORACLE\RDBMS\ARC00046.001

RECID STAMP NAME

54
---------- ---------- -------------------------------------------------------------------------
---------------------------------------------------------------------
56 603737244 C:\ORACLE\RDBMS\ARC00047.001
57 603737246 C:\ORACLE\RDBMS\ARC00048.001
58 603737251 C:\ORACLE\RDBMS\ARC00049.001
59 603737252 C:\ORACLE\RDBMS\ARC00050.001
60 603737253 C:\ORACLE\RDBMS\ARC00051.001
61 603737255 C:\ORACLE\RDBMS\ARC00052.001
62 603738258 C:\ORACLE\RDBMS\ARC00053.001
63 603738298 C:\ORACLE\RDBMS\ARC00054.001
64 603738303 C:\ORACLE\RDBMS\ARC00055.001
65 603738303 C:\ORACLE\RDBMS\ARC00056.001
66 603738303 C:\ORACLE\RDBMS\ARC00057.001

RECID STAMP NAME

---------- ---------- -------------------------------------------------------------------------
---------------------------------------------------------------------
67 603738308 C:\ORACLE\RDBMS\ARC00058.001
68 603738309 C:\ORACLE\RDBMS\ARC00059.001
69 603738314 C:\ORACLE\RDBMS\ARC00060.001
70 603738314 C:\ORACLE\RDBMS\ARC00061.001
71 603738314 C:\ORACLE\RDBMS\ARC00062.001
72 603738319 C:\ORACLE\RDBMS\ARC00063.001
73 603738325 C:\ORACLE\RDBMS\ARC00064.001
74 603739549 C:\ORACLE\RDBMS\ARC00065.001
75 603739882 C:\ORACLE\RDBMS\ARC00001.001
76 603739883 C:\ORACLE\RDBMS\ARC00002.001
77 603739883 C:\ORACLE\RDBMS\ARC00003.001

RECID STAMP NAME

---------- ---------- -------------------------------------------------------------------------
---------------------------------------------------------------------
78 603739884 C:\ORACLE\RDBMS\ARC00004.001
79 603739884 C:\ORACLE\RDBMS\ARC00005.001
80 603739890 C:\ORACLE\RDBMS\ARC00006.001
81 603739890 C:\ORACLE\RDBMS\ARC00007.001
82 603739896 C:\ORACLE\RDBMS\ARC00008.001
83 603749449 C:\ORACLE\RDBMS\ARC00009.001
84 603749779 C:\ORACLE\RDBMS\ARC00010.001
85 603749780 C:\ORACLE\RDBMS\ARC00011.001
86 603749784 C:\ORACLE\RDBMS\ARC00012.001
87 603749785 C:\ORACLE\RDBMS\ARC00013.001
88 603749791 C:\ORACLE\RDBMS\ARC00014.001

RECID STAMP NAME

---------- ---------- -------------------------------------------------------------------------
---------------------------------------------------------------------
89 603749795 C:\ORACLE\RDBMS\ARC00015.001
90 603749796 C:\ORACLE\RDBMS\ARC00016.001
91 603749796 C:\ORACLE\RDBMS\ARC00017.001
92 603749797 C:\ORACLE\RDBMS\ARC00018.001
93 603749803 C:\ORACLE\RDBMS\ARC00019.001
94 603749804 C:\ORACLE\RDBMS\ARC00020.001

55
95 603749809 C:\ORACLE\RDBMS\ARC00021.001
96 603749810 C:\ORACLE\RDBMS\ARC00022.001
97 603751702 C:\ORACLE\RDBMS\ARC00023.001
98 603751703 C:\ORACLE\RDBMS\ARC00024.001
99 603751707 C:\ORACLE\RDBMS\ARC00025.001

RECID STAMP NAME

---------- ---------- -------------------------------------------------------------------------
---------------------------------------------------------------------
100 603751707 C:\ORACLE\RDBMS\ARC00026.001
101 603751712 C:\ORACLE\RDBMS\ARC00027.001
102 603751712 C:\ORACLE\RDBMS\ARC00028.001
103 603751718 C:\ORACLE\RDBMS\ARC00029.001
104 603751718 C:\ORACLE\RDBMS\ARC00030.001
105 603751723 C:\ORACLE\RDBMS\ARC00031.001
106 603751723 C:\ORACLE\RDBMS\ARC00032.001
107 603751728 C:\ORACLE\RDBMS\ARC00033.001
108 603751728 C:\ORACLE\RDBMS\ARC00034.001
109 603751733 C:\ORACLE\RDBMS\ARC00035.001
110 603751733 C:\ORACLE\RDBMS\ARC00036.001

RECID STAMP NAME

---------- ---------- -------------------------------------------------------------------------
---------------------------------------------------------------------
111 603751739 C:\ORACLE\RDBMS\ARC00037.001
112 603751753 C:\ORACLE\RDBMS\ARC00038.001
113 603752151
114 603754350 C:\ORACLE\RDBMS\ARC00001.001
115 603754360 C:\ORACLE\RDBMS\ARC00002.001
116 603754369 C:\ORACLE\RDBMS\ARC00003.001
117 603754369 C:\ORACLE\RDBMS\ARC00004.001
118 603754374 C:\ORACLE\RDBMS\ARC00005.001
119 603754374 C:\ORACLE\RDBMS\ARC00006.001
120 603754379 C:\ORACLE\RDBMS\ARC00007.001
121 603754379 C:\ORACLE\RDBMS\ARC00008.001

RECID STAMP NAME

---------- ---------- -------------------------------------------------------------------------
---------------------------------------------------------------------
122 603754385 C:\ORACLE\RDBMS\ARC00009.001
123 603754385 C:\ORACLE\RDBMS\ARC00010.001
124 603754390 C:\ORACLE\RDBMS\ARC00011.001
125 603754390 C:\ORACLE\RDBMS\ARC00012.001
126 603754395 C:\ORACLE\RDBMS\ARC00013.001
127 603754395 C:\ORACLE\RDBMS\ARC00014.001
128 603754401 C:\ORACLE\RDBMS\ARC00015.001
129 603754401 C:\ORACLE\RDBMS\ARC00016.001
130 603754406 C:\ORACLE\RDBMS\ARC00017.001
131 603754406 C:\ORACLE\RDBMS\ARC00018.001
132 603754412 C:\ORACLE\RDBMS\ARC00019.001

RECID STAMP NAME

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

56
---------------------------------------------------------------------
133 603754412 C:\ORACLE\RDBMS\ARC00020.001
134 603754417 C:\ORACLE\RDBMS\ARC00021.001
135 603754417 C:\ORACLE\RDBMS\ARC00022.001
136 603754422 C:\ORACLE\RDBMS\ARC00023.001
137 603754422 C:\ORACLE\RDBMS\ARC00024.001
138 603754445 C:\ORACLE\RDBMS\ARC00025.001

138 rows selected.

SQL> show parameter log_Archive_form

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
log_archive_format string ARC%S.%T

Basic RMAN Tutorial

Oracle provide a tool for Database backup and restore operation is called RMAN.

Recovery Manager is a client/server application that uses database server sessions to

perform backup and recovery. It stores metadata about its operations in the control
file of the target database and, optionally, in a recovery catalog schema in an Oracle
database.

Difference between RMAN and Traditional backup methods

RMAN is Oracle's backup and recovery utility. With RMAN, backups become as easy
as:

BACKUP DATABASE;

RMAN reduces the complexity of backup and recovery. RMAN can determine what
needs to be backed up or restored.

Why Should we use RMAN

Ability to perform incremental backups.

Ability to recover one block of a datafile.
Ability to perform the backup and restore with parallelization.
Ability to automatically delete archived redo logs after they are backed up.
Ability to automatically backup the control file and the SPFILE.
Ability to restart a failed backup without having to start from the beginning.
Ability to verify the integrity of the backup.
Ability to test the restore process without having to actually perform the restore.
Comparison of RMAN Automated and User-Managed Procedures

By using operating system commands for User-Managed Backup and Recovery , a

DBA manually keeps track of all database files and backups. But RMAN performs
these same tasks automatically.

57
Understanding the RMAN Architecture

An oracle RMAN comprises of RMAN EXECUTABLE This could be present and fired
even through client side, TARGET DATABASE (This is the database which needs to be
backed up) and RECOVERY CATALOG (Recovery catalog is optional otherwise backup
details are stored in target database controlfile .)

About the RMAN Repository

The RMAN repository is a set of metadata that RMAN uses to store information about
the target database and its backup and recovery operations. RMAN stores
information about:

Backup sets and pieces

Image copies (including archived redo logs)
Proxy copies
The target database schema
Persistent configuration settings

If you start RMAN without specifying either CATALOG or NOCATALOG on the

command line, then RMAN makes no connection to a repository. If you run a
command that requires the repository, and if no CONNECT CATALOG command has
been issued yet, then RMAN automatically connects in the default NOCATALOG
mode. After that point, the CONNECT CATALOG command is not valid in the session.

Types of Database Connections

You can connect to the following types of databases.

Target database
RMAN connects you to the target database with the SYSDBA privilege. If you do not
have this privilege, then the connection fails.
Recovery catalog database
This database is optional: you can also use RMAN with the default NOCATALOG
option.
Auxiliary database
You can connect to a standby database, duplicate database, or auxiliary instance
(standby instance or tablespace point-in-time recovery instance

Note: That a SYSDBA privilege is not required when connecting to the recovery
catalog. The only requirement is that the RECOVERY_CATALOG_OWNER role be
granted to the schema owner.

Using Basic RMAN Commands

After you have learned how to connect to a target database, you can immediately
begin performing backup and recovery operations. Use the examples in this section
to go through a basic backup and restore scenario using a test database. These
examples assume the following:

The test database is in ARCHIVELOG mode.

You are running in the default NOCATALOG mode.
The RMAN executable is running on the same host as the test database.

58
Connecting to the Target Database
rman TARGET /

If the database is already mounted or open, then RMAN displays output similar to
the following:
Recovery Manager: Release [Link].0

connected to target database: RMAN (DBID=1237603294)

Reporting the Current Schema of the Target Database

In this example, you generate a report describing the target datafiles. Run the report
schema command as follows:

RMAN> REPORT SCHEMA; (RMAN displays the datafiles currently in the target
database.)

Backing Up the Database

In this task, you back up the database to the default disk location. Because you do
not specify the format parameter in this example, RMAN assigns the backup a unique
filename.

You can make two basic types of backups: full and incremental.

Making a Full Backup

Run the backup command at the RMAN prompt as follows to make a full backup of
the datafiles, control file, and current server parameter file (if the instance is started
with a server parameter file) to the default device type:
RMAN> BACKUP DATABASE;

Making an Incremental Backup

Incremental backups are a convenient way to conserve storage space because they
back up only database blocks that have changed. RMAN compares the current
datafiles to a base backup, also called a level 0 backup, to determine which blocks to
back up.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backing Up Archived Logs

Typically, database administrators back up archived logs on disk to a third-party

storage medium such as tape. You can also back up archived logs to disk. In either
case, you can delete the input logs automatically after the backup [Link]
back up all archived logs and delete the input logs (from the primary archiving
destination only), run the backup command at the RMAN prompt as follows:

RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

Listing Backups and Copies

59
To list the backup sets and image copies that you have created, run the list
command as follows:

RMAN> LIST BACKUP;

To list image copies, run the following command:

RMAN> LIST COPY;

Validating the Restore of a Backup

Check that you are able to restore the backups that you created without actually
restoring them. Run the RESTORE ... VALIDATE command as follows:

RMAN> RESTORE DATABASE VALIDATE;

Type of RMAN Backup Tutorial

Full Backups

A full backup reads the entire file and copies all blocks into the backup set, only
skipping datafile blocks that have never been used.

About Incremental Backups

Rman create backup only changed block since a previous backup. You can use RMAN
to create incremental backups of datafiles, tablespaces, or the whole database.

How Incremental Backups Work

Each data block in a datafile contains a system change number (SCN), which is the
SCN at which the most recent change was made to the block. During an incremental
backup, RMAN reads the SCN of each data block in the input file and compares it to
the checkpoint SCN of the parent incremental backup. RMAN reads the entire file
every time whether or not the blocks have been used.

The parent backup is the backup that RMAN uses for comparing the SCNs. If the
current incremental is a differential backup at level n, then the parent is the most
recent incremental of level n or less. If the current incremental is a cumulative
backup at level n, then the parent is the most recent incremental of level n-1 or less.
If the SCN in the input data block is greater than or equal to the checkpoint SCN of
the parent, then RMAN copies the block.

Multilevel Incremental Backups

RMAN can create multilevel incremental backups. Each incremental level is denoted
by an integer, for example, 0, 1, 2, and so forth. A level 0 incremental backup, which
is the base for subsequent incremental backups, copies all blocks containing data.

60
The only difference between a level 0 backup and a full backup is that a full
backup is never included in an incremental strategy.

If no level 0 backup exists when you run a level 1 or higher backup, RMAN makes a
level 0 backup automatically to serve as the base.

The benefit of performing multilevel incremental backups is that RMAN does not back
up all blocks all of the time.

Differential Incremental Backups

In a differential level n incremental backup, RMAN backs up all blocks that have
changed since the most recent backup at level n or lower.

For example, in a differential level 2 backups, RMAN determines which level 2 or

level 1 backup occurred most recently and backs up all blocks modified after that
backup. If no level 1 is available, RMAN copies all blocks changed since the base
level 0 backup. If no level 0 backup is available, RMAN makes a new base level 0
backup for this file.

Case 1: if you want to implement incremental backup strategy as a DBA in your

organization:

Use Command for incremental Level Backup

RMAN> backup incremental level 0 database tag="SUNDAY";

RMAN> backup incremental level 3 database tag="MONDAY";
RMAN> backup incremental level 3 database tag="TUESDAY";
RMAN> backup incremental level 3 database tag="WEDNESDAY";
RMAN> backup incremental level 2 database tag="THURSDAY";
RMAN> backup incremental level 3 database tag="FRIDAY";
RMAN> backup incremental level 3 database tag="SATURDAY";

Backup Example ( You can view your incremental Backup Details by using
following Query)

61
select incremental_level, incremental_change#, checkpoint_change#, blocks from
v$backup_datafile;

Result of above Query:

INC_LEVEL INC_CHANGE# CHECKPOINT_CHANGE# BLOCKS

0 0 271365 59595

3 271365 271369 2

3 271369 271371 1

3 271371 271374 2

2 271365 271378 2

3 271378 271380 1

3 271380 271383 2

Cumulative Incremental Backups

RMAN provides an option to make cumulative incremental backups at level 1 or

greater. In a cumulative level n backup, RMAN backs up all the blocks used since the
most recent backup at level n-1 or lower.

For example, in cumulative level 2 backups, RMAN determines which level 1 backup
occurred most recently and copies all blocks changed since that backup. If no level 1
backups is available, RMAN copies all blocks changed since the base level 0 backup.

Cumulative incremental backups reduce the work needed for a restore by ensuring
that you only need one incremental backup from any particular level. Cumulative
backups require more space and time than differential backups, however, because
they duplicate the work done by previous backups at the same level.

Case 1: if you want to implement Cumulative backup strategy as a DBA in your

organization:

62
Use Command for Cumulative Level Backup

backup incremental level=0 database tag='base';

backup incremental level=2 cumulative database tag='monday';
backup incremental level=2 cumulative database tag='tuesday';
backup incremental level=2 cumulative database tag='wednesday';
backup incremental level=2 cumulative database tag='thursday';
backup incremental level=2 cumulative database tag='friday';
backup incremental level=2 cumulative database tag='saturday';
backup incremental level=1 cumulative database tag='weekly';

Incremental backup implementation

RMAN will determine the incremental SCN for each datafile

Find the backup with highest checkpoint scn that

 belongs to the incarnation of datafile

 matches the given file#
 is an incremental backup/copy at level N or less if noncumulative or
 is an incremental backup/copy at level N-1 or less if cumulative
 belongs to an available backup set if backup

Incremental Backup Strategy

You can implement a three-level backup scheme so that a full or level 0 backup is
taken monthly, a cumulative level 1 backup is taken weekly, and a cumulative level
2 is taken daily. In this scheme, you never have to apply more than a day's worth of
redo for complete recovery. When deciding how often to take full or level 0 backups,
a good rule of thumb is to take a new level 0 whenever 50% or more of the data has
changed. If the rate of change to your database is predictable, then you can observe
the size of your incremental backups to determine when a new level 0 is appropriate.
The following query displays the number of blocks written to a backup set for each
datafile with at least 50% of its blocks backed up:

SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME, BLOCKS,

DATAFILE_BLOCKS
FROM V$BACKUP_DATAFILE
WHERE INCREMENTAL_LEVEL > 0 AND BLOCKS / DATAFILE_BLOCKS > .5

63
ORDER BY COMPLETION_TIME;

Compare the number of blocks in differential or cumulative backups to a base level 0

backup. For example, if you only create level 1 cumulative backups, then when the
most recent level 1 backup is about half of the size of the base level 0 backup, take a
new level 0.

RMAN: RESTORE Concept

Use the RMAN RESTORE command to restore the following types of files from
copies on disk or backups on other media:

· Database (all datafiles)

· Tablespaces
· Control files
· Archived redo logs
· Server parameter files

Process of Restore Operations

RMAN automates the procedure for restoring files. When you issue a RESTORE
command, RMAN restore the correct backups and copies to either:

· The default location, overwriting the old files with the same name

· A new location, which you can specify with the SET NEWNAME command

For example:

If you restore datafile 'C:_DATA.DBF’ to its default location, then RMAN restores the
file C:_DTAA.DBF’ and overwrites any file that it finds with the same filename.

if you run a SET NEWNAME command before you restore a file, then RMAN creates
a datafile copy with the name that you specify. For example, assume that you run
the following commands:

Run

SET NEWNAME FOR DATAFILE 'C:_DATA.DBF’ TO C:_DATA.DBF’;

RESTORE DATAFILE 'C:_DTAA.DBF’;

SWITCH DATAFILE 'C:_DATA.DBF' TO DATAFILECOPY 'C:_DATA.DBF’;

In this case, RMAN creates a datafile copy of 'C:_DATA.DBF’ named

'C:_DATA.DBF’ and records it in the repository. To change the name for datafile

64
'C:_DATA.DBF’ to 'C:_DATA.DBF’ in the control file, run a SWITCH command so
that RMAN considers the restored file as the current database file.

RMAN Recovery: Basic Steps

If possible, make the recovery catalog available to perform the media recovery. If it
is not available, then RMAN uses metadata from the target database control file.
Assuming that you have backups of the datafiles and at least one autobackup of the
control file.

The generic steps for media recovery using RMAN are as follows:

· Place the database in the appropriate state: mounted or open. For example, mount
the database when performing whole database recovery, or open the database when
performing online tablespace recovery.

· Restore the necessary files using the RESTORE command.

· Recover the datafiles using the RECOVER command.

· Place the database in its normal state.

Mechanism of Restore and Recovery operation:

The DBA runs the following commands:

RESTORE DATABASE;

RECOVER DATABASE;

The RMAN recovery catalog obtains its metadata from the target database control
file. RMAN decides which backup sets to restore, and which incremental backups and
archived logs to use for recovery. A server session on the target database instance
performs the actual work of restore and recovery.

Mechanics of Recovery: Incremental Backups and Redo Logs

RMAN does not need to apply incremental backups to a restored level 0 incremental
backup: it can also apply archived logs. RMAN simply restores the datafiles that it
needs from available backups and copies, and then applies incremental backups to
the datafiles if it can and if not applies logs.

How RMAN Searches for Archived Redo Logs During Recovery

If RMAN cannot find an incremental backup, then it looks in the repository for the
names of archived redo logs to use for recovery. Oracle records an archived log in
the control file whenever one of the following occurs:

· The archiver process archives a redo log

65
· RMAN restores an archived log

· The RMAN COPY command copies a log

· The RMAN CATALOG command catalogs a user-managed backup of an archived log

RMAN propagates archived log data into the recovery catalog during
resynchronization, classifying archived logs as image copies. You can view the log
information through:

· The LIST command

· The V$ARCHIVED_LOG control file view

· The RC_ARCHIVED_LOG recovery catalog view

During recovery, RMAN looks for the needed logs using the filenames specified in the
V$ARCHIVED_LOG view. If the logs were created in multiple destinations or were
generated by the COPY, CATALOG, or RESTORE commands, then multiple, identical
copies of each log sequence number exist on disk.

If the RMAN repository indicates that a log has been deleted or uncataloged, then
RMAN ceases to consider it as available for recovery. For example, assume that the
database archives log 100 to directories /dest1 and /dest2. The RMAN repository
indicates that /dest1/[Link] and /dest2/[Link] exist. If you delete
/dest1/[Link] with the DELETE command, then the repository indicates that only
/dest2/[Link] is available for recovery.

If the RMAN repository indicates that no copies of a needed log sequence number
exist on disk, then RMAN looks in backups and restores archived redo logs as needed
to perform the media recovery. By default, RMAN restores the archived redo logs to
the first local archiving destination specified in the initialization parameter file. You
can run the SET ARCHIVELOG DESTINATION command to specify a different restore
location. If you specify the DELETE ARCHIVELOG option on RECOVER, then RMAN
deletes the archived logs after restoring and applying them. If you also specify
MAXSIZE integer on the RECOVER command, then RMAN staggers the restores so
that they consume no more than integer amount of disk space at a time.

Incomplete Recovery

RMAN can perform either complete or incomplete recovery. You can specify a time,
SCN, or log sequence number as a limit for incomplete recovery with the SET UNTIL
command or with an UNTIL clause specified directory on the RESTORE and RECOVER
commands. After performing incomplete recovery, you must open the database with
the RESETLOGS option.

Disaster Recovery with a Control File Autobackup

Assume that you lose both the target database and the recovery catalog. All that you
have remaining is a tape with RMAN backups of the target database and archived
redo logs. Can you still recover the database? Yes, assuming that you enabled the

66
control file autobackup feature. In a disaster recovery situation, RMAN can determine
the name of a control file autobackup even without a repository available. You can
then restore this control file, mount the database, and perform media recovery.

About Block Media Recovery

You can also use the RMAN BLOCKRECOVER command to perform block media
recovery. Block media recovery recovers an individual corrupt datablock or set of
datablocks within a datafile. In cases when a small number of blocks require media
recovery, you can selectively restore and recover damaged blocks rather than whole
datafiles.

Note: Restrictions of block media recovery:

 You can only perform block media recovery with Recovery Manager. No
SQL*Plus recovery interface is available.
 You can only perform complete recovery of individual blocks. In other words,
you cannot stop recovery before all redo has been applied to the block.
 You can only recover blocks marked media corrupt. The
V$DATABASE_BLOCK_CORRUPTION view indicates which blocks in a file were
marked corrupt since the most recent BACKUP, BACKUP ... VALIDATE, or
COPY command was run against the file.
 You must have a full RMAN backup. Incremental backups are not allowed.
 Blocks that are marked media corrupt are not accessible to users until
recovery is complete. Any attempt to use a block undergoing media recovery
results in an error message indicating that the block is media corrupt.

When Block Media Recovery Should Be Used

For example, you may discover the following messages in a user trace file:

ORA-01578: ORACLE data block corrupted (file # 7, block # 3)

ORA-01110: data file 7: '/oracle/oradata/trgt/[Link]'
ORA-01578: ORACLE data block corrupted (file # 2, block # 235)
ORA-01110: data file 2: '/oracle/oradata/trgt/[Link]'

You can then specify the corrupt blocks in the BLOCKRECOVER command as follows:

BLOCKRECOVER DATAFILE 7 BLOCK 3 DATAFILE 2 BLOCK 235;

Block Media Recovery When Redo Is Missing

Like datafile media recovery, block media recovery cannot survive a missing or
inaccessible archived log. Where is datafile recovery requires an unbroken series of
redo changes from the beginning of recovery to the end, block media recovery only
requires an unbroken set of redo changes for the blocks being recovered.

When RMAN first detects missing or corrupt redo records during block media
recovery, it does not immediately signal an error because the block undergoing
recovery may become a newed block later in the redo stream. When a block is
newed all previous redo for that block becomes irrelevant because the redo applies

67
to an old incarnation of the block. For example, Oracle can new a block when users
delete all the rows recorded in the block or drop a table.

RMAN Other Tutorial

Deciding Whether to Use RMAN with a Recovery Catalog

By default, RMAN connects to the target database in NOCATALOG mode, meaning

that it uses the control file in the target database as the sole repository of RMAN
metadata. Perhaps the most important decision you make when using RMAN is
whether to create a recovery catalog as the RMAN repository for normal production
operations. A recovery catalog is a schema created in a separate database that
contains metadata obtained from the target control file.

Benefits of Using the Recovery Catalog as the RMAN Repository

When you use a recovery catalog, RMAN can perform a wider variety of automated
backup and recovery functions than when you use the control file in the target
database as the sole repository of metadata.

The following features are available only with a catalog:

 You can store metadata about multiple target databases in a single catalog.
 You can store metadata about multiple incarnations of a single target
database in the catalog. Hence, you can restore backups from any
incarnation.
 Resynchronizing the recovery catalog at intervals less than the
CONTROL_FILE_RECORD_KEEP_TIME setting, you can keep historical
metadata.
 You can report the target database schema at a noncurrent time.
 You can store RMAN scripts in the recovery catalog.
 When restoring and recovering to a time when the database files that exist in
the database are different from the files recorded in the mounted control file,
the recovery catalog specifies which files that are needed. Without a catalog,
you must first restore a control file backup that lists the correct set of
database files.
 If the control file is lost and must be restored from backup, and if persistent
configurations have been made to automate the tape channel allocation,
these configurations are still available when the database is not mounted.

Costs of Using the Recovery Catalog as the RMAN Repository

The main cost of using a catalog is the maintenance overhead required for this
additional database.

For example, you have to:Find a database other than the target database to store
the recovery catalog (otherwise, the benefits of maintaining the catalog are lost), or
create a new database Create enough space on the database for the RMAN
metadata.

 Back up the recovery catalog metadata

68
  Upgrade the recovery catalog when necessary

Types of Files That RMAN Can Back Up

The BACKUP command can back up the following types of files:

Database, which includes all datafiles as well as the current control file and current
server parameter
file:

 Tablespaces (except for locally-managed temporary tablespaces)

 Current datafiles
 Current control file
 Archived redo logs
 Current server parameter file
 Backup sets

RMAN does not back up the following:

 Online redo logs

 Transported tablespaces before they have been made read/write
 Client-side initialization parameter files or noncurrent server parameter files

How to Configure RMAN

RMAN can invoked from the command line on the database host machine like so:

C:\>rman target sys/sys_password

Recovery Manager: Release [Link].0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

Connected to target database: ORCL (DBID=1036216947)

RMAN> show all;

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'e:\backup\ctl_sp_bak_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'e:\backup\%[Link]'
MAXPIECESIZE 4G;

69
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'e:\backup\%[Link]'
MAXPIECESIZE 4G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'C:\ORACLE\ORA92\DATABASE\[Link]'; #
default

RMAN>

Retention Policy:

This instructs RMAN on the backups that are eligible for deletion.
For example: A retention policy with redundancy 2 would mean that two backups -
the latest and the one prior to that - should be retained. All other backups are
candidates for deletion.

Default Device Type:

This can be "disk" or "sbt" (system backup to tape). We will backup to disk and then
have our OS backup utility copy the completed backup, and other supporting files, to
tape.

Controlfile Autobackup:

This can be set to "on" or "off". When set to "on", RMAN takes a backup of the
controlfile AND server parameter file each time a backup is performed. Note that
"off" is the default.

Controlfile Autobackup Format:

This tells RMAN where the controlfile backup is to be stored. The "%F" in the file
name instructs RMAN to append the database identifier and backup timestamp to the
backup filename. The database identifier, or DBID, is a unique integer identifier for
the database.

Parallelism:

This tells RMAN how many server processes you want dedicated to performing the
backups.

Device Type Format:

This specifies the location and name of the backup files. We need to specify the
format for each channel. The "%U" ensures that Oracle appends a unique identifier
to the backup file name. The MAXPIECESIZE attribute sets a maximum file size for
each file in the backup set.

Any of the above parameters can be changed using the commands displayed by the
"show all" command.

70
For example, one can turn off controlfile autobackups by issuing:

RMAN> configure controlfile autobackup off;

using target database controlfile instead of recovery catalog

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored

RMAN>

Complete Steps for Using RMAN through Catalog

Recovery manager is a platform independent utility for coordinating your backup and
restoration procedures across multiple servers.

Create Recovery Catalog

First create a user to hold the recovery catalog:

CONNECT sys/password@w2k1 AS SYSDBA

-- Create tablepsace to hold repository

CREATE TABLESPACE "RMAN"

DATAFILE 'C:\ORACLE\ORADATA\W2K1\[Link]' SIZE 6208K REUSE

AUTOEXTEND ON NEXT 64K MAXSIZE 32767M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

-- Create rman schema owner

CREATE USER rman IDENTIFIED BY rman

TEMPORARY TABLESPACE temp

DEFAULT TABLESPACE rman

QUOTA UNLIMITED ON rman;

GRANT connect, resource, recovery_catalog_owner TO rman;

71
C:>rman catalog=rman/rman@w2k1

Recovery Manager: Release [Link].0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

Connected to recovery catalog database

Recovery catalog is not installed

RMAN> create catalog tablespace "RMAN";

Recovery catalog created

RMAN> exit

Recovery Manager complete.

C:>

Register Database

C:>rman catalog=rman/rman@w2k1 target=sys/password@w2k2\

<[Link]

Recovery Manager: Release [Link].0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: W2K2 (DBID=1371963417)

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN>

Full Backup

72
First we configure several persistent parameters for this instance:

RMAN> configure retention policy to recovery window of 7 days;

RMAN> configure default device type to disk;

RMAN> configure controlfile autobackup on;

RMAN> configure channel device type disk format

'C:\Oracle\Admin\W2K2\Backup%d_DB_%u_%s_%p';

Next we perform a complete database backup using a single command:

RMAN> run

{backup database plus archivelog;

delete noprompt obsolete;

The recovery catalog should be resyncronized on a regular basis so that changes to

the database structure and presence of new archive logs is recorded. Some
commands perform partial and full resyncs implicitly, but if you are in doubt you can
perform a full resync using the follwoing command:

RMAN> resync catalog;

Recovery from missing or corrupted redo log group

Case 1: A multiplexed copy of the missing log is available.
If a redo log is missing, it should be restored from a multiplexed copy, if possible.
Here's an example, where I attempt to startup from SQLPLUS when a redo log is
missing:
SQL> startup
ORACLE instance started.

Total System Global Area 131555128 bytes

Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'D:\ORACLE_DATA\LOGS\ORCL\[Link]'

SQL>
To fix this we simply copy [Link] from its multiplexed location on E: to the
above location on D:.

73
SQL> alter database open;

Database altered.

SQL>
That's it - the database is open for use.
Case 2: All members of a log group lost.
In this case an incomplete recovery is the best we can do. We will lose all
transactions from the missing log and all subsequent logs. We illustrate using the
same example as above. The error message indicates that members of log group 3
are missing. We don't have a copy of this file, so we know that an incomplete
recovery is required. The first step is to determine how much can be recovered. In
order to do this, we query the V$LOG view (when in the mount state) to find the
system change number (SCN) that we can recover to (Reminder: the SCN is a
monotonically increasing number that is incremented whenever a commit is issued)
--The database should be in the mount state for v$log access

SQL> select first_change# from v$log whnhi….ere group#=3 ;

FIRST_CHANGE#
-------------
370255

SQL>
The FIRST_CHANGE# is the first SCN stamped in the missing log. This implies that
the last SCN stamped in the previous log is 370254 (FIRST_CHANGE#-1). This is the
highest SCN that we can recover to. In order to do the recovery we must first restore
ALL datafiles to this SCN, followed by recovery (also up to this SCN). This is an
incomplete recovery, so we must open the database resetlogs after we're done.
Here's a transcript of the recovery session (typed commands in bold, comments in
italics, all other lines are RMAN feedback):
C:\>rman target /

Recovery Manager: Release [Link].0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--Restore ENTIRE database to determined SCN

RMAN> restore database until scn 370254;

Starting restore at 26/JAN/05

using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]

74
restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
channel ORA_DISK_2: restored backup piece 1
piece handle=E:\BACKUP\13GB14IB_1_1.BAK tag=TAG20050124T171139
params=NUL
channel ORA_DISK_2: restore complete
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\14GB14IB_1_1.BAK tag=TAG20050124T171139
params=NUL
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05

--Recover database

RMAN> recover database until scn 370254;

Starting recover at 26/JAN/05

using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

archive log thread 1 sequence 9 is already on disk as file

E:\ORACLE_ARCHIVE\ORCL\1_9.ARC
archive log thread 1 sequence 10 is already on disk as file
E:\ORACLE_ARCHIVE\ORCL\1_10.ARC
archive log thread 1 sequence 11 is already on disk as file
E:\ORACLE_ARCHIVE\ORCL\1_11.ARC
archive log thread 1 sequence 12 is already on disk as file
E:\ORACLE_ARCHIVE\ORCL\1_12.ARC
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_9.ARC thread=1 sequence=9
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_10.ARC thread=1 sequence=10
media recovery complete
Finished recover at 26/JAN/05

--open database with RESETLOGS (see comments below)

RMAN> alter database open resetlogs;

database opened
RMAN>
The following points should be noted:
1. The entire database must be restored to the SCN that has been determined by
querying v$log.
2. All changes beyond that SCN are lost. This method of recovery should be used
only if you are sure that you cannot do better. Be sure to multiplex your redo logs,
and (space permitting) your archived logs!
3. The database must be opened with RESETLOGS, as a required log has not been
applied. This resets the log sequence to zero, thereby rendering all prior backups
worthless. Therefore, the first step after opening a database RESETLOGS is to take
a fresh backup. Note that the RESETLOGS option must be used for any incomplete
recovery.

75
 Recovery from missing or corrupted control file

Case 1: A multiplexed copy of the control file is available.

On startup Oracle must read the control file in order to find out where the datafiles
and online logs are located. Oracle expects to find control files at locations specified
in the CONTROL_FILE initialisation parameter. The instance will fail to mount the
database if any one of the control files are missing or corrupt. Here's an example:

SQL> startup

ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

SQL>

On checking the alert log, as suggested, we find the following:

ORA-00202: controlfile: 'e:\oracle_dup_dest\controlfile\ORCL\[Link]'

ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 5447783)

The above corruption was introduced by manually editing the control file when the
database was closed.

The solution is simple, provided you have at least one uncorrupted control file -
replace the corrupted control file with a copy using operating system commands.
Remember to rename the copied file. The database should now start up without any
problems.

Case 2: All control files lost

What if you lose all your control files? In that case you have no option but to use a
backup control file. The recovery needs to be performed from within RMAN, and
requires that all logs (archived and current online logs) since the last backup are
available. The logs are required because all datafiles must also be restored from
backup. The database will then have to be recovered up to the time the control files
went missing. This can only be done if all intervening logs are available. Here's an
annotated transcript of a recovery session (as usual, lines in bold are commands to
be typed, lines in italics are explanatory comments, other lines are RMAN feedback):

-- Connect to RMAN

C:\rman

76
Recovery Manager: Release [Link].1 - Production
(c) Copyright 2001 Oracle Corporation. All rights reserved.

RMAN> set dbid 4102753520

executing command: SET DBID

set DBID - get this from the name of the controlfile autobackup. For example, if
autobackup name is
CTL_SP_BAK_C-1507972899-20050124-00 the the DBID is 1507972899. This step
will not be required if the instance is

RMAN> connect target sys/change_on_install

connected to target database: (not mounted)

Recovery Manager: Release [Link].0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 26/JAN/05

using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:\ORACLE_DATA\CONTROLFILE\ORCL\[Link]
output filename=E:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\[Link]
output filename=C:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\[Link]
Finished restore at 26/JAN/05

-- Now that control files have been restored, the instance can mount the

-- database.

RMAN> mount database;

database mounted
-- All datafiles must be restored, since the controlfile is older than the current
-- datafiles. Datafile restore must be followed by recovery up to the current log.

RMAN> restore database;

Starting restore at 26/JAN/05

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
channel ORA_DISK_1: restored backup piece 1

77
piece handle=E:\BACKUP\0DGB0I79_1_1.BAK tag=TAG20050124T115832
params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\0CGB0I78_1_1.BAK tag=TAG20050124T115832
params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05

--Database must be recovered because all datafiles have been restored from

-- backup

RMAN> recover database;

Starting recover at 26/JAN/05

using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file
E:\ORACLE_ARCHIVE\ORCL\1_2.ARC
archive log thread 1 sequence 4 is already on disk as file
D:\ORACLE_DATA\LOGS\ORCL\[Link]
archive log thread 1 sequence 5 is already on disk as file
D:\ORACLE_DATA\LOGS\ORCL\[Link]
archive log thread 1 sequence 6 is already on disk as file
D:\ORACLE_DATA\LOGS\ORCL\[Link]
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_2.ARC thread=1 sequence=2
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_3.ARC thread=1 sequence=3
archive log filename=E:\ORACLE_DATA\LOGS\ORCL\[Link] thread=1
sequence=4
archive log filename=E:\ORACLE_DATA\LOGS\ORCL\[Link] thread=1
sequence=5
archive log filename=E:\ORACLE_DATA\LOGS\ORCL\[Link] thread=1
sequence=6
media recovery complete
Finished recover at 26/JAN/05

-- Recovery completed. The database must be opened with RESETLOGS

-- because a backup control file was used. Can also use

-- "alter database open resetlogs" instead.

RMAN> open resetlogs database;

database opened

78
Several points are worth emphasizing.

1. Recovery using a backup controlfile should be done only if a current control file is
unavailable.

2. All datafiles must be restored from backup. This means the database will need to
be recovered using archived and online redo logs. These MUST be available for
recovery until the time of failure.

3. As with any database recovery involving RESETLOGS, take a fresh backup

immediately.

4. Technically the above is an example of complete recovery - since all committed

transactions were recovered. However, some references consider this to be
incomplete recovery because the database log sequence had to be reset.

After recovery using a backup controlfile, all temporary files associated with locally-
managed tablespaces are no longer available. You can check that this is so by
querying the view V$TEMPFILE - no rows will be returned. Therefore tempfiles must
be added (or recreated) before the database is made available for general use. In
the case at hand, the tempfile already exists so we merely add it to the temporary
tablespace. This can be done using SQLPlus or any tool of your choice:

SQL> alter tablespace temp add tempfile

'D:\oracle_data\datafiles\ORCL\[Link]';
Tablespace altered.

SQL>

Check that the file is available by querying v$TEMPFILE.

Recovery from missing or corrupted datafile(s):

Case 1: Recovery from corrupted or missing datafile

This scenario deals with a situation where a datafile has gone missing, or is
corrupted beyond repair. For concreteness, we look at a case where a datafile is
missing. Below is a transcript of an SQL Plus session that attempts to open a
database with a missing datafile (typed commands in bold, lines in italics are my
comments, all other lines are feedback from SQL Plus):

--open SQL Plus from the command line without

--logging on to database

C:\>sqlplus /nolog

SQL*Plus: Release [Link].0 - Production on Tue Jan 25 [Link] 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

79
--Connect to the idle Oracle process as a privileged user and start up instance

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\[Link]'

SQL>

The error message tells us that file# 4 is missing. Note that although the startup
command has failed, the database is in the mount state. Thus, the database control
file, which is also the RMAN repository can be accessed by the instance and by
RMAN. We now recover the missing file using RMAN. The transcript of the recovery
session is reproduced below (bold lines are typed commands, comments in italics,
the rest is feedback from RMAN):

--logon to RMAN

C:\>rman target /

Recovery Manager: Release [Link].0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)

--restore missing datafile

RMAN> restore datafile 4;

Starting restore at 26/JAN/05

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=15 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\0QGB0UEC_1_1.BAK tag=TAG20050124T152708
params=NULL

80
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05

--recover restored datafile - RMAN applies all logs automatically

RMAN> recover datafile 4;

Starting recover at 26/JAN/05 using channel ORA_DISK_1

using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 4 is already on disk as file
E:\ORACLE_ARCHIVE\ORCL\1_4.ARC
archive log thread 1 sequence 5 is already on disk as file
C:\ORACLE_ARCHIVE\ORCL\1_5.ARC
archive log thread 1 sequence 6 is already on disk as file
E:\ORACLE_ARCHIVE\ORCL\1_6.ARC
archive log thread 1 sequence 7 is already on disk as file
E:\ORACLE_ARCHIVE\ORCL\1_7.ARC
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_4.ARC thread=1 sequence=4
archive log filename=C:\ORACLE_ARCHIVE\ORCL\1_5.ARC thread=1 sequence=5
media recovery complete
Finished recover at 26/JAN/05

--open database for general use

RMAN> alter database open;

database opened

RMAN>

In the above scenario, the database is already in the mount state before the RMAN
session is initiated. If the database is not mounted, you should issue a "startup
mount" command before attempting to restore the missing datafile. The database
must be mounted before any datafile recovery can be done.

If the database is already open when datafile corruption is detected, you can recover
the datafile without shutting down the database. The only additional step is to take
the relevant tablespace offline before starting recovery. In this case you would
perform recovery at the tablespace level. The commands are:

C:\>rman target /

Recovery Manager: Release [Link].0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)

--offline affected tablespace

RMAN> sql 'alter tablespace USERS offline immediate';

81
using target database controlfile instead of recovery catalog
sql statement: alter tablespace USERS offline immediate

--recover offlined tablespace

RMAN> recover tablespace USERS;

Starting recover at 26/JAN/05

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=12 devtype=DISK
starting media recovery
media recovery complete
Finished recover at 26/JAN/05

--online recovered tablespace

RMAN> sql 'alter tablespace USERS online';

sql statement: alter tablespace USERS online

RMAN>

Here we have used the SQL command, which allows us to execute arbitrary SQL
from within RMAN.

Case 2: Recovery from block corruption

It is possible to recover corrupted blocks using RMAN backups. This is a somewhat

exotic scenario, but it can be useful in certain circumstances, as illustrated by the
following example. Here's the situation: a user connected to SQLPlus gets a data
block corruption error when she queries a table. Here's a part of the session
transcript:

SQL> connect testuser/testpassword

Connected.

SQL> select count(*) from test_table;

select count(*) from test_table

*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 2015)
ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\[Link]'

Since we know the file and block number, we can perform block level recovery using
RMAN. This is best illustrated by example:

82
C:\>rman target /

Recovery Manager: Release [Link].0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)

--restore AND recover specific block

RMAN> blockrecover datafile 4 block 2015;

Starting blockrecover at 26/JAN/05

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=19 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=20 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=E:\BACKUP\0QGB0UEC_1_1.BAK tag=TAG20050124T152708
params=NULL
channel ORA_DISK_1: block restore complete
starting media recovery
media recovery complete
Finished blockrecover at 26/JAN/05

RMAN>

Now our user should be able to query the table from her SQLPlus session. Here's her
session transcript after block recovery.

SQL> select count(*) from test_table;

COUNT(*)
----------
217001
SQL>

A couple of important points regarding block recovery:

1. Block recovery can only be done using RMAN.

2. The entire database can be open while performing block recovery.

3. Check all database files for corruption. This is important - there could be other
corrupted blocks. Verification of database files can be done using RMAN or the
dbverify utility. To verify using RMAN simply do a complete database backup with
default settings. If RMAN detects block corruption, it will exit with an error message
pointing out the guilty file/block.

83
 Disaster Recovery

Introduction:
- i.e. a situation in which your database server has been destroyed and has taken all
your database files (control files, logs and data files) with it. Obviously, recovery
from a disaster of this nature is dependent on what you have in terms of backups
and hardware resources. We assume you have the following available after the
disaster:

 A server with the same disk layout as the original.

 The last full hot backup on tape.

With the above items at hand, it is possible to recover all data up to the last full
backup. One can do better if subsequent archive logs (after the last backup) are
available. In our case these aren't available, since our only archive destination was
on the destroyed server ). Oracle provides methods to achieve better data
protection. We will discuss some of these towards the end of the article.

Now on with the task at hand. The high-level steps involved in disaster recovery are:

 Build replacement server.

 Restore backup from tape.
 Install database software.
 Create Oracle service.
 Restore and recover database.

Step:1 Build the server

You need a server to host the database, so the first step is to acquire or build the
new machine. This is not strictly a DBA task, so we won't delve into details here. The
main point to keep in mind is that the replacement server should, as far as possible,
be identical to the old one. In particular, pay attention to the following areas:

 Ideally the server should have the same number of disks as the original. The
new disks should also have enough space to hold all software and data that
was on the original server.
 The operating system environment should be the same as the original, right
up to service pack and patch level.
 The new server must have enough memory to cater to Oracle and operating
system / other software requirements. Oracle memory structures (Shared
pool, db buffer caches etc) will be sized identically to the original database
instance. Use of the backup server parameter file will ensure this.

Step:2 Restore backup from tape

The next step is to get your backup from tape on to disk.

84
Step:3 Install Oracle Software

The next step is to install Oracle software on the machine. The following points
should be kept in mind when installing the software:

 Install the same version of Oracle as was on the destroyed server. The
version number should match right down to the patch level, so this may be a
multi-step process involving installation followed by the application of one or
more patch sets and patches.
 Do not create a new database at this stage.
 Create a listener using the Network Configuration Assistant. Ensure that it has
the same name and listening ports as the original listener. Relevant listener
configuration information can be found in the backed up [Link] file.

Step:4 Create directory structure for database files

After software installation is completed, create all directories required for datafiles,
(online and archived) logs, control files and backups. All directory paths should
match those on the original server.

Don't worry if you do not know where the database files should be located. You can
obtain the required information from the backup spfile and control file at a later
stage. Continue reading - we'll come back to this later.

Step: 5 Create Oracle service

An Oracle service must be exist before a database is created. The service is created
using the oradim utility, which must be run from the command line. The following
commands show how to create and modify a service (comments in italics, typed
commands in bold):

--create a new service with auto startup

C:\>oradim -new -sid ORCL -intpwd ORCL -startmode a

Unfortunately oradim does not give any feedback, but you can check that the
service exists via the Services administrative panel. The service has been configured
to start automatically when the computer is powered up.

Step: 6 Restore and recover database

Now it is time to get down to the nuts and bolts of database recovery. There are
several steps, so we'll list them in order:

 Copy PASSWORD and TNSNAMES file from backup: The backed up

password file and [Link] files should be copied from the backup
directory to the proper locations. Default location for password and tnsnames

85
 files are ORACLE_HOME\database ORACLE_HOME\network\admin
respectively.
 Set ORACLE_SID environment variable: ORACLE_SID should be set to the
proper SID name (ORCL in our case). This can be set either in the registry
(registry key: HKLM\Software\Oracle\HOME<X>\ORACLE_SID) or from the
system applet in the control panel.
 Invoke RMAN and set the DBID: We invoke rman and connect to the target
database as usual. No login credentials are required since we connect from an
OS account belonging to ORA_DBA. Note that RMAN accepts a connection to
the database although the database is yet to be recovered. RMAN doesn't as
yet "know" which database we intend to connect to. We therefore need to
identify the (to be restored) database to RMAN. This is done through the
database identifier (DBID). The DBID can be figured out from the name of the
controlfile backup. Example: if you use the controlfile backup format , your
controlfile backup name will be something like "CTL_SP_BAK_C-1507972899-
20050228-00". In this case the DBID is 1507972899. Here's a transcript
illustrating the process of setting the DBID:

C:\>rman

Recovery Manager: Release [Link].0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> set dbid 1507972899

executing command: SET DBID

RMAN>connect target /

connected to target database (not started)

RMAN>
Restore spfile from backup: To restore the spfile, you first need to startup the
database in the nomount state. This starts up the database using a dummy
parameter file. After that you can restore the spfile from the backup (which has been
restored from tape ). Finally you restart the database in nomount state. Here is an
example RMAN transcript for the foregoing procedure. Note the difference in SGA
size and components between the two startups:

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file
'C:\ORACLE\ORA92\DATABASE\[Link]'
trying to start the Oracle instance without parameter files ...
Oracle instance started

Total System Global Area 97590928 bytes

Fixed Size 454288 bytes

Variable Size 46137344 bytes
Database Buffers 50331648 bytes

86
Redo Buffers 667648 bytes

RMAN> restore spfile from 'e:\backup\CTL_SP_BAK_C-1507972899-

20050228-00';

Starting restore at 01/MAR/05

using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: autobackup found: e:\backup\CTL_SP_BAK_C-1507972899-
20050228-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 01/MAR/05

RMAN> startup force nomount

Oracle instance started

Total System Global Area 1520937712 bytes

Fixed Size 457456 bytes

Variable Size 763363328 bytes
Database Buffers 754974720 bytes
Redo Buffers 2142208 bytes

RMAN>

The instance is now started up with the correct initialization parameters.

We are now in a position to determine the locations of control file and archive
destination, as this information sits in the spfile. This is done via SQL Plus as follows:

C:\>sqlplus /nolog

SQL>connect / as sysdba
Connected.
SQL> show parameter control_file

SQL> show parameter log_archive_dest

The directories listed in the CONTROL_FILES and LOG_ARCHIVE_DEST_N

parameters should be created at this stage if they haven't been created earlier.
Restore control file from backup: The instance now "knows" where the control
files should be restored, as this is listed in the CONTROL_FILES initialization
parameter. Therefore, the next step is to restore these files from backup. Once the
control files are restored, the instance should be restarted in mount mode. A restart
is required because the instance must read the initialization parameter file in order to
determine the control file locations. At the end of this step RMAN also has its proper
configuration parameters, as these are stored in the control file.

Here is a RMAN session transcript showing the steps detailed here:

87
RMAN> restore controlfile from 'e:\backup\CTL_SP_BAK_C-1507972899-
20050228-00';

Starting restore at 01/MAR/05

allocated channel: ORA_DISK_1

hannel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:\ORACLE_DATA\CONTROLFILE\ORCL\[Link]
output filename=E:\ORACLE_DATA\CONTROLFILE\ORCL\[Link]
output filename=C:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\[Link]
Finished restore at 01/MAR/05

RMAN> shutdown

Oracle instance shut down

RMAN> exit

Recovery Manager complete.

C:\>rman target /

Recovery Manager: Release [Link].0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)

RMAN>startup mount;

Oracle instance started

database mounted

Total System Global Area 1520937712 bytes

Fixed Size 457456 bytes

Variable Size 763363328 bytes
Database Buffers 754974720 bytes
Redo Buffers 2142208 bytes

RMAN> show all;

using target database controlfile instead of recovery catalog

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'e:\backup\ctl_sp_bak_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

88
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'e:\backup\%[Link]'
MAXPIECESIZE 4G;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'e:\backup\%[Link]'
MAXPIECESIZE 4G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'C:\ORACLE\ORA92\DATABASE\[Link]'; # default

RMAN>

At this stage we can determine the locations of data files and redo logs if we don't
know where they should go. This is done from SQL Plus as follows:

C:\>sqlplus /nolog

SQL>connect / as sysdba
Connected.
SQL>select name from v$datafile;

SQL>select member from v$logfile;

SQL>

The directories shown in the output should be created manually if this hasn't been
done earlier.
Restore all datafiles: This is easy. Simply issue a "restore database" command
from RMAN, and it will do all the rest for you:

RMAN> restore database;

Starting restore at 01/MAR/05

using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=8 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
restoring datafile 00005 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
restoring datafile 00006 to D:\ORACLE_DATA\DATAFILES\ORCL\[Link]
channel ORA_DISK_2: restored backup piece 1
piece handle=E:\BACKUP\80G6E1TT_1_1.BAK tag=TAG20041130T222501
params=NULL
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\81G6E1TU_1_1.BAK tag=TAG20041130T222501

89
params=NULL
channel ORA_DISK_2: restored backup piece 2
piece handle=E:\BACKUP\80G6E1TT_2_1.BAK tag=TAG20041130T222501
params=NULL
channel ORA_DISK_1: restored backup piece 2
piece handle=E:\BACKUP\81G6E1TU_2_1.BAK tag=TAG20041130T222501
params=NULL
channel ORA_DISK_1: restored backup piece 3
piece handle=E:\BACKUP\81G6E1TU_3_1.BAK tag=TAG20041130T222501
params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_2: restored backup piece 3
piece handle=E:\BACKUP\80G6E1TT_3_1.BAK tag=TAG20041130T222501
params=NULL
channel ORA_DISK_2: restore complete
Finished restore at 01/MAR/05

RMAN>

Recover database: The final step is to recover the database. Obviously recovery is
dependent on the available archived (and online) redo logs. Since we have lost our
database server and have no remote archive destination, we can recover only up to
the time of the backup. Further, since this is an incomplete recovery, we will have to
open the database with resetlogs. Here's a sample RMAN session illustrating this:

RMAN> recover database;

Starting recover at 01/MAR/05

using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

unable to find archive log archive log thread=1 sequence=1388

RMAN-00571: ==============================
RMAN-00569: =ERROR MESSAGE STACK FOLLOWS =
RMAN-00571: ===============================
RMAN-03002: failure of recover command at 04/01/2005 [Link]
RMAN-06054: media recovery requesting unknown log: thread 1 scn 32230460

RMAN>alter database open resetlogs;

database opened

RMAN>

Note that RMAN automatically applies all available archive logs. It first applies the
backed up log and then searches for subsequent logs in the archive destination. This
opens the door for further recovery if the necessary logs are available. In our case,
however, we have no more redo so we open the database with resetlogs. The error
message above simply indicates that RMAN has searched, unsuccessfully, for
subsequent logs.

90
That's it. The database has been recovered, from scratch, to the last available
backup. Now having done this, it is worth spending some time in discussing how one
can do better - i.e. recover up to a point beyond the backup. We do this in the next
section.

Restoring an RMAN Backup to Another Node

In certain circumstances, it may be desirable to restore a database from an RMAN

backup onto a machine other than the original host. For example, to recover data at
a given point in time, or to duplicate a production instance.

The example assumes:

The target database is on host A

The database is to be restored onto host B
The directory structure of host B is different to host A
The ORACLE_SID will not change for the restored database
A recovery catalog is being used
The backups were carried out to disk (for illustrative purposes, and to disassociate
from any media manager specific issues)

The following steps are required:

Backup the target on host A

List the datafile locations on host A
Make the backup available to host B
Make a copy of the [Link] available to host B
Edit the [Link] to reflect directory structure changes
Configure SQL*Net connectivity from host to the recovery catalog and duplicated
database
Set up a password file for the duplicated database
Startup nomount the duplicated database
RMAN restore the controlfile(s)
Mount the database
Restore and rename the datafiles
Recover and open the database

Step:1 Backup the Target on Host A

The target database needs to be backed up using RMAN.

The following is one example of RMAN doing an online database backup. In this
example, the backup sets are written to disk.

run {
allocate channel t1 type disk;
backup

tag whole_database_open
format '/oracle/backups/BFS/df_%u'
database;

91
# switch out of the current logfile
sql 'alter system archive log current';

#backup the archived logs

backup archivelog all
format '/oracle/backups/BFS/al_%u';

# backup a copy of the controlfile that contains records for the

# other backups just made
backup current controlfile tag = cf1 format '/oracle/backups/BFS/cf_%u';

Step: 2 List Datafile Locations on Host A

The datafile numbers and location on host A are required. These datafile locations
will change on host B

SQL> select file#, name from v$datafile;

file# name
----- ------------------------------
1 /oracle/OFA_base/u01/oradata/V805X/[Link]
2 /oracle/OFA_base/u01/oradata/V805X/[Link]
3 /oracle/OFA_base/u01/oradata/V805X/[Link]
4 /oracle/OFA_base/u01/oradata/V805X/[Link]
5 /oracle/OFA_base/u01/oradata/V805X/[Link]
6 /oracle/OFA_base/u01/oradata/V805X/[Link]
7 /oracle/OFA_base/u01/oradata/V805X/[Link]
8 /oracle/OFA_base/u01/oradata/V805X/[Link]

The log file names should also be recorded.

SQL> select group#, member from v$logfile;

group# member
----- ------------------------------
1 /oracle/OFA_base/u01/oradata/V805X/[Link]
2 /oracle/OFA_base/u01/oradata/V805X/[Link]
3 /oracle/OFA_base/u01/oradata/V805X/[Link]

Step: 3 Make the Backups Available to Host B

[Link] - Pay Per Click Advertising, Pay Per Click Affiliate Program, Free
Traffic Counters. More information about indiandba

Disk Backups

During restore, RMAN will expect the backup sets to be located in the same
directory as written to during the backup.

92
Tape Backups

The media management software must be configured such that host B is a media
manager client, and can read the backup sets. The media management vendor
should be consulted for support on this issue.

Step: 4 [Link] on host B

[Link] - Pay Per Click Advertising, Pay Per Click Affiliate Program, Free
Traffic Counters. More information about indiandba

The "[Link]" needs to be made available on host B. Any location specific parameters
must be amended. For example, ifile, *_dump_dest, log_archive_dest*, control_files

Step: 5 SQL*Net configuration

[Link] - Pay Per Click Advertising, Pay Per Click Affiliate Program, Free
Traffic Counters. More information about indiandba

If running RMAN from host A:

A. Connectivity to the catalog remains unchanged

B. Configure [Link] on host A to connect to duplicated db on host B
C. Configure [Link] on host B to accept connections for duplicated database

If running RMAN from host B:

A Configure [Link] on host B to connect to catalog [Link] on catalog

host remains unchanged
B. Configure [Link] on host B to connect to duplicated db on host B configure
[Link] on host B to accept connections for duplicated database

If running RMAN from host C (ie, neither host A or host B):

A. Connectivity to the catalog remains unchanged

B. Configure [Link] on host C to connect to duplicated db on host B configure
[Link] on host B to accept connections for duplicated database

Step: 6 Setup Password File

[Link] - Pay Per Click Advertising, Pay Per Click Affiliate Program, Free
Traffic Counters. More information about indiandba

In order to allow RMAN remote connections, a password file must be setup for the
duplicated database. For example,

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=kernel

Step: 7 Recover Duplicated Database

93
[Link] - Pay Per Click Advertising, Pay Per Click Affiliate Program, Free
Traffic Counters. More information about indiandba

Startup nomount the database

SQL> startup nomount pfile=<location of [Link]>

Restore the controlfile(s)

For example,
run{
allocate channel c1 type disk;
restore controlfile;
}

Mount the database

SQL> alter database mount;

Rename and restore the datafiles, and perform database recovery

RMAN can be used to change the location of the datafiles from the location on host
A to the new location on host B.

For example,
run {

allocate channel c1 type disk;

allocate channel c2 type disk;
allocate channel c3 type disk;

set newname for datafile 1 to '/oracle/datafiles/[Link]';

set newname for datafile 2 to '/oracle/datafiles/[Link]';
set newname for datafile 3 to '/oracle/datafiles/[Link]';
set newname for datafile 4 to '/oracle/datafiles/[Link]';
set newname for datafile 5 to '/oracle/datafiles/[Link]';
set newname for datafile 6 to '/oracle/datafiles/[Link]';
set newname for datafile 7 to '/oracle/datafiles/[Link]';
set newname for datafile 8 to '/oracle/datafiles/[Link]';

restore database;

switch datafile all;

}

Recover and open the database

HOW TO PERFORM BLOCK MEDIA RECOVERY WHEN BACKUPS ARE NOT

TAKEN BY RMAN.

To Perform Block Media Recovery when there are OS-backups available, but NO
RMAN backups.

94
Consider that a block corruption is reported in the database and the corruption is not
wide spread.

Since RMAN is not used in the database the initial solution would be to restore the
file from backup and then recover. But this method will require the file should be
offline during the entire process. On the other hand if BMR can be used, the file does
not need to be taken offline.

Step 1 : Identify the backup of the datafilefile which has the corrupted
block(s) and catalog it within RMAN.

Example: Backup is /u01/backup/[Link]

$ export ORACLE_SID=orcl
$ RMAN target=/
RMAN> catalog datafilecopy ‘/u01/backup/[Link]’;

Step 2 : Check the archived redologs.

RMAN is using the archived redologs which are known in V$ARCHIVED_LOG. If the
archivelog files are not reported in V$ARCHIVED_LOG because they are moved to
another location, or aged out of the controlfile, then they need to be cataloged as
well.

First verify the existing archived redologs :

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;

Then catalog the unknown archived redologs :

RMAN> catalog archivelog ‘/u01/backup/archivelog/Arch_ocl_1_30.dbf’

Step 3 : Preform the Block Media Recovery

Having datafile(s) and all required archivelogs cataloged, we can run BMR as follows.

Example for a block recovery of blocks 99, 100, 101 in datafile 5

RMAN> blockrecover datafile 5 block 99,100,101;

Monitoring Recovery Manager Jobs

Sometimes it is useful to identify what a server session performing a backup or copy

operation is doing. You have access to several views that can assist in monitoring the
progress of or obtaining information about RMAN jobs:

View

V$PROCESS
Identifies currently active processes.

V$SESSION

95
Identifies currently active sessions. Use this view to determine which
Oracle database server sessions correspond to which RMAN allocated channels.

V$SESSION_LONGOPS
Provides progress reports on long-running operations.

V$SESSION_WAIT
Lists the events or resources for which sessions are waiting.

Correlating Server Sessions with Channels

To identify which server sessions correspond to which RMAN channels, use the set
command with the command id parameter. The command id parameter enters the
specified string into the CLIENT_INFO column of the V$SESSION dynamic
performance view. Join V$SESSION with V$PROCESS to correlate the server session
with the channel.

To correlate a process with a channel during a backup:

Step:1 Start RMAN and connect to the target database .

Step:2 Set the command id parameter after allocating the channels and then back
up the
desired object.

run {
allocate channel t1 type disk;
allocate channel t2 type disk;
set command id to 'rman';
backup
incremental level 0
filesperset 5
tablespace 'SYSTEM';
# optionally, issue a host command to access the operating system prompt
host;
sql 'ALTER SYSTEM ARCHIVE LOG ALL';
}

Step:3 Start a SQL*Plus session and then query the joined V$SESSION and
V$PROCESS
views while the RMAN job is executing.

SELECT sid, spid, client_info

FROM v$process p, v$session s
WHERE [Link] = [Link]
AND client_info LIKE '%id=rman%';

SID SPID CLIENT_INFO

96
8 21973 id=rman
16 22057 id=rman
17 22068 id=rman,ch=t1
18 22070 id=rman,ch=t2

Monitoring Job Progress

Each server session performing a backup, restore, or copy reports its progress
compared to the total amount of work required for that particular part of the restore.

For example, if you perform a restore using two channels, and each channel has two
backup sets to restore (a total of 4 sets), then each server session reports its
progress through a single set. When that set is completely restored, RMAN starts
reporting progress on the next set to restore.

Step:1 Start RMAN and connect to the target database and, optionally, the recovery
catalog
database.

Step:2 Start an RMAN job.:

run {
allocate channel t1 type disk;
backup database;
}

Step:3 While the job is running, execute a script containing the following SQL
statement:

SELECT sid, serial#, context, sofar, totalwork,

round(sofar/totalwork*100,2) "% Complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork
/

If you repeat the query while the backup progresses, then you see output such as
the following:

SQL>
SID SERIAL# CONTEXT SOFAR TOTALWORK %
Complete
8 19 1 10377 36617
28.34

SQL> /
SID SERIAL# CONTEXT SOFAR TOTALWORK %
Complete
8 19 1 21513 36617
58.75

97
SQL> /

SQL> /
no rows selected

NOTE: If you run the script at intervals of two minutes or more and the % Complete
column does not increase, then RMAN is encountering a problem.

SELECT sid, seconds_in_wait AS sec_wait, event FROM v$session_wait WHERE

wait_time = 0
ORDER BY sid;

SID SEC_WAIT EVENT

1 368383335 pmon timer
2 1097 rdbms ipc message
3 387928 rdbms ipc message
4 0 rdbms ipc message
5 1408 smon timer
6 386114 rdbms ipc message
7 387626 rdbms ipc message
8 1060 SQL*Net message from client
9 1060 SQL*Net message from client
12 1060 SQL*Net message from client
13 2366 SQL*Net message from client
14 2757 SQL*Net message from client
12 rows selected.

Note: The V$SESSION_WAIT view shows only Oracle events, not media
manager events.

Another Query:

COLUMN EVENT FORMAT a10

COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30

SELECT [Link], EVENT, SECONDS_IN_WAIT AS SEC_WAIT,

[Link], CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE [Link] LIKE '%disk%'
AND [Link]=[Link]
AND [Link]=[Link]
;

Monitoring and Tuning the Database Buffer Cache

The database buffer cache is the area of the SGA that store database data in
memory block for access by user process.

98
The database buffer cache hit ratio represent the percentage of block-read request
that satisfied by a logical read to the database buffer cache.

A high ratio means that more reads form memory took place and a low ration means
that more physical reads are required. Because logical reads involve reads to
memory and physical reads involve reading from disk. It stands to reason that one
would prefer that logical reads occur rather than physical one.

Calculating the Hit ratio for the Buffer Pool

SELECT ([Link] + [Link] - [Link]) / ([Link] + [Link]) * 100 "Buffer Pool

Hit Ratio"
FROM v$sysstat P1, v$sysstat P2, v$sysstat P3
WHERE [Link] = 'db block gets'
AND [Link] = 'consistent gets'
AND [Link] = 'physical reads'

Note: Hit ratio should be more then 90%, if hit ratio less then 90% then increase
database buffer cache size.

Tuning the Database Buffer Cache Hit Ration

The Hit Ratio can be up or down. If database buffer cache ration less then 90% . You
can increase DB_CACHE_SIZE in init file.

Monitoring and Tuning the Data Dictionary Cache

A component of the shared pool is the data dictionary cache, which is used to store
data dictionary rows in the SGA. Much like the database buffer cache, the shared
pool has various hit ratios that can be calculated to determine its performance.

Calculating the Data Dictionary Cache Hit Ratio

select (( 1 - (sum(getmisses) /
(sum(gets)+ sum(getmisses)))) * 100 ) " Data Dictionary Hit Ratio"
from v$rowcache
where
gets + getmisses <> 0

Note: If the hit ratio is less then 95% you probably need to add memory to the
Shared Pool. To be so, increase the SHARED_POOL_SIZE in init file.

Calculating the Dictionary Cache Miss Ratio

This tells you how often the system went into the dictionary cache and found what it
was looking for.

Select (sum(getmisses)/sum(gets))*100 " Dictonery Cache Miss Ratio" from

v$rowcache

Note: If ratio is more then 10% increase the Shared pool size.

99
 Monitoring and Tuning the Library Cache

Library cache miss can occure in either the parse or execute phase of a SQL
operation. If a parse call is made by an application and the parsed statement is not
already in the shared pool, oracle will need to parse the statement and a library
cache miss will result. If an application makes an execute call for SQL statement, it is
possible that the parsed representation of that statement will have been aged out of
the library cache by another operation.

In this case , oracle will re parse the statement before executing it.

The first solution to both of these problems is to make sure that you have sufficient
memory allocation to the shared pool. This is done using the shared_pool_size.

To monitor the library cache, you are interested in three statistics.

Cache Hit Ratio

Cache Reload Ratio

Library Cache Pin Hit Ratio

Cache Hit Ratio

Library cache hit ratio is a measure of how often the system finds the library cache
already loaded in the cache and how times the system needs to get then off the disk.

select sum(pins)/(sum(pins)+sum(reloads))*100 " LIBRARY CACHE HIT RATIO" from

v$librarycache

Note: greater then 94% is fine.

Cache Reload Ratio

The Library cache reload ratio measures the overall hit ratio of the library cache. The
library cache reload ratio differs from the hit ratio in that it measures the reuse of
SQL objects that have already been in the library cache , aged out and then reload.

The library cache reload ratio should be as near to 0 (zero).

A low cache reload ratio indicate that oracle did not need to reload any previously
loaded library cache items. If the library cache reload ration is high (.5% to 1%) you
should check for SQL statement reusability.

A high cache reload ratio indicate that the database needs to reload library cache
items once they have already been loaded.

Select sum(reloads)/(sum(pins)*100) " Library Cache reload Ratio" from

v$librarycache

100
Library cache Pin Hit Ratio

The library cache pin hit ratio is a measure of how often oracle executed an object
that was already in the library cache and was valied. This rate should be as close to
100% as possible.

select sum(pinhits)/(sum(pins))*100 " Library Cache pin hit Ratio" from

v$librarycache

Monitoring System File I/O

The fastest way that Oracle can read or modify a block is when the information is
already loaded in memory in the buffer cache. When this do not occur, Oracle will
need to read the information directly from the datafile incurring I/O operations,
which usually are much more expensive.

When data is retrieved from the physical disk you must takes steps to reduce disk
contention. Disk contention occurs when multiple processes try to access the same
disk simultaneously. Disk contention can be reduced, thereby increasing
performance, by distributing the disk I/O more evenly over the available disks.
Decreasing disk I/O can also reduce disk contention.

Monitoring disk I/O Contention

One can also determine file I/O problems by running the following query:

select name,phyrds "PRD",phywrts "PWR",readtim "RTM", writetim "WTM",avgiotim

"ATM"
from v$filestat a ,v$dbfile b
where [Link]#=[Link]#
order by avgiotim

NOTE: A large difference in the number of physical writes and reads among disks will
show which disk is being burdened the most.

Redo Log Buffer Tuning

How to Analyze Redo Log Buffer Efficiency

Execute Following Query:

select sid,event,seconds_in_wait,state from v$session_wait where event='log buffer

space%';

Out Put of Above Query:

SID EVENT SECONDS_IN_WAIT STATE

15 log buffer space 110 WAITING

101
Conclusion:

SECOND_IN_WAIT value of the Log Buffer Space event indicate the time spent
waiting for space in the redo log buffer because the log switch does not occure. This
is an indication that the buffer are filled up faster than LGER is writing.

Action:
If SECOND_IN_WAIT is high the increase size of LOG_BUFFER parameter in init file.

FAQ- Statspack

What is StatsPack and how does STATSPACK work?

Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection,
automation, storage and viewing of performance data. A user is automatically
created by the installation script - this user, PERFSTAT, owns all objects needed by
this package. This user is granted limited query-only
privileges on the V$views required for performance tuning.

How do I install StatsPack?

Execute [Link] which is located in ORACLE_HOME/rdbms/admin directory this

will create user PERFSTAT in database.

How do I take a snapshot of current database statistics?

Connect to PERFSTAT user and execute [Link] procedure.

How do I generate a performance report?

Run [Link] which is store in ORACLE_HOME/admin directory.

How to Automate Statistic Collection?

Use Job schedular in the oracle RDBMS_JOB package to set up a recurring job that
execute to [Link] sql secipts.

How do remove statspack?

Use [Link] scrips whic in located in ORACLE_HOME/rdbms/admin directory.

How To Resize the Online Redo Logfiles

Step:1 First see the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 1048576 INACTIVE

102
2 1048576 CURRENT
3 1048576 INACTIVE

In this query , Logs are 1MB . and we increase size them to 10MB.

Step:2 Retrieve all the log member names for the groups:

SQL> select group#, member from v$logfile;

GROUP# MEMBER
--------------- ----------------------------------------
1 /usr/oracle/dbs/[Link]
2 /usr/oracle/dbs/[Link]
3 /usr/oracle/dbs/[Link]

Step:3 Let's create 3 new log groups and name them groups 4, 5, and 6, each 10MB
in size:

SQL> alter database add logfile group 4 '/usr/oracle/dbs/[Link]'

size 10M;
SQL> alter database add logfile group 5 '/usr/oracle/dbs/[Link]'
size 10M;
SQL> alter database add logfile group 6 '/usr/oracle/dbs/[Link]'
size 10M;

Step:4 Now run a query to view the v$log status:

SQL> select group#, status from v$log;

GROUP# STATUS
--------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED

From the above we can see log group 2 is current, and this is one of the smaller
groups we must drop. Therefore let's switch out of this group into one of the newly
created log groups.

Step:5 Switch until we are into log group 4, so we can drop log groups 1, 2, and 3:

SQL> alter system switch logfile;

** repeat as necessary until group 4 is CURRENT **

Step:6 Run the query again to verify the current log group is group 4:

SQL> select group#, status from v$log;

GROUP# STATUS
--------- ----------------

103
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
5 UNUSED
6 UNUSED

Step:7 Now drop redo log groups 1, 2, and 3:

SQL> alter database drop logfile group 1;

SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

Verify the groups were dropped, and the new groups' sizes are correct.

SQL> select group#, bytes, status from v$log;

GROUP# BYTES STATUS

--------- --------- ----------------
4 10485760 CURRENT
5 10485760 UNUSED
6 10485760 UNUSED

Step:8 At this point, you consider taking a backup of the database.

Step:9 You can now go out to the operating system and delete the files associated
with redo log groups 1, 2, and 3 in step 2 above as they are no longer needed:

rm /usr/oracle/dbs/[Link]
rm /usr/oracle/dbs/[Link]
rm /usr/oracle/dbs/[Link]

Monitor the [Link] for the times of redo log switches.

How to calculate growth of Database (Consolidated)

Step : 1 Calculate total Size of tablespace

select sum(bytes)/1024/1024 "TOTAL SIZE (MB)" from dba_Data_files

Step : 2 Calculate Free Space in Tablespace

select sum(bytes)/1024/1024 "FREE SPACE (MB)" from dba_free_space

Step : 3 Calculate total size , free space and used space in tablespace

select [Link] "TOTAL SIZE",[Link] "FREE SPACE",([Link]/[Link])*100 "FREE (%)"

,([Link]/[Link])*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 , (select
sum(bytes)/1024/1024 total from dba_Data_files) t2

104
Step : 4 Create table which is store all free/use space related information of
tablespace

create table db_growth

as select *
from (
select sysdate,[Link] "TOTAL_SIZE",[Link] "FREE_SPACE",([Link]/[Link])*100
"FREE% "
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 5 Insert free space information in DB_GROWTH table (if you want to
populate data Manually)

insert into db_growth

select *
from (
select sysdate,[Link] "TOTAL_SIZE",[Link] "FREE_SPACE",([Link]/[Link])*100
"FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if

you want to populate data automatically)

create view v_db_growth

as select *
from
(
select sysdate,[Link] "TOTAL_SIZE",[Link] "FREE_SPACE",([Link]/[Link])*100
"FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view

insert into db_growth select *

from v_db_growth

Step : 8 Check everything goes fine.

105
select * from db_growth;

Check Result

Step : 9 Execute following SQL for more time stamp information

alter session set nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;

Session altered.

Step : 10 Create a DBMS jobs which execute after 24 hours

declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into db_growth select * from v_db_growth;commit;end;',
sysdate, 'SYSDATE+ 24', TRUE);
commit;
end;

PL/SQL procedure successfully completed.

Step: 11 View your dbms jobs and it's other information

select * from user_jobs;

TIPS: If you want to execute dbms jobs manually execute following

command other wise jobs is executing automatically

exec dbms_job.run(ENTER_JOB_NUMBER)

PL/SQL procedure successfully completed.

Step: 13 Finally all data populated in db_growth table

select * from db_growth

How to Estimate the size of UNDO tablespace?

Use following formula for calculating Sizing an UNDO tablespace:

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

 (UR) UNDO_RETENTION in seconds

 (UPS) Number of undo data blocks generated per second
 (DBS) Overhead varies based on extent and file size (db_block_size)

How to calculate UPS:

106
SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM
v$undostat;

The result of the query returns the number of undo blocks per second. This value
needs to be multiplied by the size of an undo block, which is the same size as the
database block defined in DB_BLOCK_SIZE.

Note: Column END_TIME and BEGIN_TIME are DATE data types. When DATE data
types are subtracted, the result is in days. To convert days to seconds, you multiply
by 86400, the number of seconds in a day.

Complete Query Calculate the needed the number of bytes

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"

FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM
v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=

(select value from v$parameter where name = 'undo_tablespace'));

Monitoring the System using Solaris Command

Monitoring CPU Usage

Method:1

We can use SAR command to monitor CPU usage. The SAR commands gives you a
quick snapshot of haw heavily the CPU is bogged down.

# sar -u 5 5

SunOS flex-prod 5.10 Generic_118833-23 sun4u 09/12/2007

[Link] %usr %sys %wio %idle

[Link] 33 2 0 66
[Link] 33 1 0 66
[Link] 36 2 0 63
[Link] 55 4 0 40
[Link] 59 7 0 34

Average 43 3 0 54

This is tell you:

%usr Percentage of CPU running in user mode

%sys Percentage of CPU running in system mode
%wio Percentage of CPU running in with a process waiting for block I/O
%idle Percentage of CPU that is IDLE

107
TIPS: Use the sar -u command to see how heavily the CPU is bogged down. The
Low CPU idle time can be an I/O issue and not a CPU issue.

Method:2

# vmstat 5 5

procs memory page disk faults cpu

r b w avm fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id

000 0 1088 0 2 2 0 1 0 0 0 0 0 0 26 72 24 0 1 98

Note: The CPU is spending most of its time in IDLE mode (id). That means that the
CPU is not being heavily used at all. There are no processes that are waiting to be
run (r), blocked (b), or waiting for IO (w) in the RUN QUEUE.

Method:3

# sar -qu 5 5

Note: The CPU is spending most (94%) of its time in idle mode. This CPU is not
being heavily used at all. if CPU is using heavily, two solutions to this are:

1. Obtain a faster processor

2. Use more CPU's.

Monitor I/O problem

Method :1

Use following command for Monitoring I/O

# sar -d 5 2

Note: This Command lists the % busy, avgue (average queue length), r+w/s, blks/s
( Number of block transfered), avwait and avserv.

Tips: A high % busy and high avque indicate a disk I/O bottleneck. if this condition
persist, an analysis of disk should lead to a reorganised of information from heavy
load to a less used disk.

Method: 2

# iostat -d 5 5

Note: iostat will display the number of kilobytes transferred per second, the number
of transfers per second, and the milliseconds per average seek.

108
Tips: KPS rates over 30 indicate heavy usage of a particular disk. If only one disk
shows heavy usage, consider moving some of your datafiles off it or striping your
data across several disks.

Method: 3

# sar -b 5 5

[Link] bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s

[Link] 0 2 90 1 2 38 0 0

Note: The "-b" option indicates the overall health of the IO subsystem.

Tips: The %rcache should be greater than 90% and %wcache should be greater
than 60%. If this is not the case, your system may be bound by disk IO. The sum
of bread, bwrit, pread, and pwrit gives a good indicator of how well your file
subsystem is doing. The sum should not be greater than 40 for 2 drives and 60 for
4-8 drives. If you exceed these values, your system may be IO bound.

When analyzing disk IO, make sure that you have balanced the load on your system.
Here is a tips of steps for designing a disk layout for Oracle:

 Make sure that your logfiles and archived logfiles are NOT on the same disk
as your datafiles. This is a basic safety precaution against disk failure.
 Allocate one disk for the User Data Tablespace.
 Place Rollback, Index, and System Tablespaces on separate disks.

Monitor Process which is using the most CPU

Use following command:

# ps -e -o pcpu,pid,user,args | sort -k 0,0 -r

This command list the %CPU used , PID, USER and Command that was executed. if
the top user was Oracle User, you must to know the information on the process form
oracle.

use following query for that:

SQL> select [Link],[Link],[Link], spid,sid,[Link]# from

v$session a,v$process b where [Link]=[Link] and spid='&pid' ;

Enter value for pid: 17929

old 3: and spid='&pid'
new 3: and spid='17929'

USERNAME OSUSER PROGRAM SPID SID SERIAL#

APPS applprod 17929 17 28394

APPS applprod 17929 192 20763

109
APPS applprod 17929 108 17788

SQL> select [Link],a.sql_text from v$sql a,v$session b where

b.sql_address = [Link] and b.sql_hash_value = a.hash_value and [Link] =
'&SID' and [Link]# = '&PROCESS'

Tips: Enter SID and Serial# because the values retrieved in the first query.

SQL>select [Link],a.sql_text from v$open_cursor a, v$session b

where b.sql_sddress = [Link] and b.sql_hash_value=a.hash_value and
[Link]= '&SID' and [Link]# = '&PROCESS'

Tips: If you had a ad-hoc query user problem and problem queries showed
up in this result regularly.

Identify CPU Bottlenecks

Use following Command:

# mpstat 10 5

The mpstat command is tool that report per-processor statistics in tabular form.
Each row of the table represent the activity of one processor.

Pay attention on smtx measurement, it measure the number of times the CPU faild
to obtain a mutual exclusion lock.

TIPS: If the smtx column for the mpstat output is greater than 200, you are heading
toward CPU bottleneck problems.

Monitoring Paging/Swaping

One of the most common problems when running large numbers of concurrent users
on UNIX machines is lack of memory. In this case, a quick review memory
management is useful to see what effect lack of RAM can have on performance.

When analyzing your machine, make sure that the machine is not swapping at all
and at worst paging lightly. This indicates a system with a healthy amount of
memory available. To analyze paging and swapping, use the following commands.

$ vmstat 5 5

procs memory page disk faults cpu

r b w avm fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id

000 0 1088 0 2 2 0 1 0 0 0 0 0 0 26 72 24 0 1 98

110
Note: There are NO pageouts (po) occurring on this system. There are also 1088 *
4k pages of free RAM available (4 Meg). It is OK and normal to have page out (po)
activity. You should get worried when the number of page ins (pi) starts rising.
This indicates that you system is starting to page.

$ sar -wpg 5 5

[Link] swpin/s pswin/s swpot/s pswot/s pswch/s

atch/s pgin/s ppgin/s pflt/s vflt/s slock/s

pgout/s ppgout/s pgfree/s pgscan/s %s5ipf

[Link] 0.00 0.0 0.00 0.0 12

0.00 0.22 0.22 0.65 3.90 0.87

0.00 0.00 0.00 0.00 0.00

Note: There is absolutely no swapping or paging going on.

(swpin,swpot,ppgin,ppgout).

$ sar -r 5 5

[Link] freemem freeswp

[Link] 790 5862

This will give you a good indication of how much free swap and RAM you have on
your machine. There are 790 pages of memory available and 5862 disk blocks of
SWAP available.

Oracle Statspack

Installing Statspack in the Database

To install Statspack, you run an installation scripts called [Link]. You can find
this scripts in the $Oracle_home/rdbms/admin directory.

SQL> conn sys/sys as sysdba

SQL> @?:\rdbms\admin\[Link]

This scripts will create PERFSTAT user in database.

Taking Database Snapshot

Now that Statspack has been installed, you can start collecting database data.

SQL> conn PERFSTAT/PERFSTAT;

111
SQL> execute [Link];

The SNAP procedure take a snapshot of your database and store the information
collected in that snapshot in the statspack repository. Leter , you can generat
statspack report based on the difference in snapshot, which occurred over a givin
period of time.

Viewing the Statspack Report

SQL> @?\rdbms\admin\[Link]

Oracle provide a single report that contains the collected information. This report is
run by using the scripts [Link]. When you run [Link] , it will provide you
with a list of snapshot currently store in the [Link] will then be prompted to
select the begining and ending snapshot to report on and to specify the output report
name.

Automating Statistics Collection

To collect statistics on a regular, frequent basic, you will want to automate the
process. One way to schedule statistics is to use oracle job scheduler in the oracle
DBMS_JOB package to set up a recurring job that executes the [Link] sql
scripts.

Example: Issue the following command to have statistics collected on your system
once every 6 hours:

declare
jobno number;
begin
dbms_job.submit(jobno, '[Link];', trunc(sysdate+1/6,'HH'),
'trunc(SYSDATE+1/6,''HH'')');
commit;
end;

Interpreting STATSPACK : Header Information

DB Name DB Id Instance Inst Num Release Cluster Host

------------ ----------- ------------ -------- ----------- ------- ------------
PROD 34493871 PROD 1 [Link].0 NO flex-prod

Snap Id Snap Time Sessions Curs/Sess Comment

------- ------------------ -------- --------- -------------------
Begin Snap: 61 12-Sep-07 [Link] 156 91.3 Created with OAM on
End Snap: 62 12-Sep-07 [Link] 155 92.9 Created with OAM on

Elapsed: 0.82 (mins)

Cache Sizes (end)

112
~~~~~~~~~~~~~~~~~
Buffer Cache: 160M Std Block Size: 8K
Shared Pool Size: 288M Log Buffer: 10,240K

The first section of the report include information about the database like db name ,
release number, db id and host. The Cache Size section show the values of the
Buffer Cache (DB_CACHE_SIZE), Shared Pool Size(SHARED_POOL_SIZE) standard
block size (DB_BLOCK_SIZE) and Log Buffer (LOG_BUFFER).

Interpreting STATSPACK : Load Profile

Load profile section provides per-second and per-transaction statistics. We can

monitor load variation on our system. When we see larger numbers for per second
on our system that means load on your system is increasing .if we receive lower
number for the per transaction statistic that means our system running with
maximum efficiency.

Load Profile
~~~~~~~~~~~ Per Second Per Transaction
--------------- --------------
Redo size: 7,674.80 7,230.16
Logical reads: 5,770.91 5,436.57
Block changes: 65.26 61.47
Physical reads: 372.73 351.14
Physical writes: 0.92 0.87
User calls: 41.13 38.74
Parses: 27.12 25.55
Hard parses: 0.04 0.04
Sorts: 12.87 12.13
Logons: 0.21 0.19
Executes: 74.27 69.97
Transactions: 1.06

% Blocks changed per Read: 1.13 Recursive Call %: 76.65

Rollback per transaction %: 0.16 Rows per Sort: 5.03

Note: an increase in redo size, block changes and % Block changed per Read
indicate increased DML(insert/update/delete) activity.
Hard Parse occurs when a SQL statments is executed and is not currently in the
shared pool. Hard Parses should not be grater than 100/second. if it is grater than
100/second means bind variables are not being used effectively. In this situation,
there is a shared pool sizing problems.
Soft Parses occures when a SQL statment is executed and it is currently in the
shared pool. Soft Parses should not be grater than 300/second. if it is grater than
300/second means programme inefficiencies where statements are being parsed
over and over again instead of the program efficiently parsing the statement only
once per session.

Interpreting STATSPACK : Instance Efficiency

113
The Instance efficiency section shows information for many of the common hit ratios.
If you regular run STATSPACK, comparing hit ratios from one day to another day can
be great barometer.

Note: Good hit ratio don't show the whole picture. There are always cases where the
database is working very efficiently but this report shows only database operations,
not the application operation , server action or network issues that also impact the
performance of the application.

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.99
Buffer Hit %: 93.54 In-memory Sort %: 100.00
Library Hit %: 99.95 Soft Parse %: 99.84
Execute to Parse %: 63.48 Latch Hit %: 99.92
Parse CPU to Parse Elapsd %: 71.30 % Non-Parse CPU: 95.59

Buffer Nowait % should not less then 99%

The Buffer Nowait Ratio is the percentage of requests a server process makes for a
specific buffer where the buffer was available immediately. all buffer types are
included in this statistic. If the ratio is low, determine which type of block is being
contended for by examining the Buffer Wait Statistics section.

Buffer Hit % should not less then 95%

This means that when a request for a buffer took place, the buffer was available in
memory and physical disk I/O did not need to take place.

Library Hit % should not less then 95%

The Library Hit Ratio is also known as the library cache hit ratio. The ratio indicates
the number of pin requests which result in pin hits. A pin hit occurs when the SQL or
PL/SQL code you wish to execute is already in the library cache and is valid to
execute.

A low library cache hit percentage could mean SQL is prematurely aging out of the
shared pool as the shared pool may be small, or that unsharable SQL is being used.
Also compare with the soft parse ratio; if they are both low, then investigate whether
there is a parsing issue.

Parse CPU to Parse Elapsd %

The Parse CPU is the amount of CPU time spent parsing SQL statements. The Parse
CPU filter represents the percentage of CPU time spent parsing SQL statements.

Parse time CPU can be a strong indication that an application has not been well
tuned. High parse time CPU usually indicates that the application may be spending
too much time opening and closing cursors or is not using bind variables.

If this is low, you may see "latch free" as one of your top wait events.

114
Redo NoWait %

Frequent, or slow log switches may be contributing to waits for redo log space. If you
are switching logs frequently (e.g. more than once every 15 minutes) this may be
improved by increasing the size of the online redo logs.

If the log switches are not frequent, check the disks the redo logs reside on to see if
log switches are taking a long time due to a slow IO system. If the IO system is
overloaded, either move the redo logs to disks with less activity, place the logs on
dedicated disks or faster devices.

In-memory Sort % should not less then 95%

The In Memory Sort Ratio is the percentage of sorts (from ORDER BY clauses or
index building) that are done to disk vs. in-memory. Disk sorts are done in the TEMP
tablespace, which is hundreds of times slower than a RAM sort. The in-memory sorts
are controlled by sort_area_size or by pga_aggregate_target.

At the time a session is established with Oracle, a private sort area is allocated in
RAM memory for use by the session for sorting. If the connection is via a dedicated
connection a Program Global Area (PGA) is allocated according to the sort_area_size
[Link] parameter.

For connections via the multithreaded server, sort space is allocated in the
large_pool. Unfortunately, the amount of memory used in sorting must be the same
for all sessions, and it is not possible to add additional sort areas for tasks that
require large sort operations.

Soft Parse % should not less then 95%

This is an important one at least for OLTP systems. This means that your SQL is
being reused. If this is low (not between 95% and 100%) then make sure that SQL
is not being reused and need to be investigated.

Latch Hit % should not less then 99%.

If it's not then check out what your top wait events are to try to fix the problem

The Latch Hit Ratio is the ratio of the total number of latch misses to the number of
latch gets for all latches. A low value for this ratio indicates a latching problem,
whereas a high value is generally good

Interpreting STATSPACK : Shared Pool Statistics

Shared Pool Statistics Begin End

------ ------
Memory Usage %: 28.38 29.46
% SQL with executions>1: 44.31 44.46
% Memory for SQL w/exec>1: 57.55 61.26
Memory Usage % : Percentage of shared pool in use.
As a rule of thumb expected to be between 70-85% for effective systems.

115
If the value is low then the memory is wasted. If its high the shared pool is used
heavily which means that you are aging certain components out from there.
SQL with executions > 1: This is indicate, How many SQL statements were found
in the shared pool executed more than once.
Memory for SQL w/exec>1: Measure of memory consumed by the SQL's used
frequently compared to sql's not used frequently.
Standby Oracle Database

This document contains a guide on setting up standby databases for maximum

protection, using command line mode, and avoiding using the GUI. To do this
Oracle9i has a feature called Data Guard and the following sections describe the
tasks undertaken to set-up primary and standby servers and a couple of
WINDOWS servers.

Database PROD is replicated from production Server to Standby Server via

DataGuard

Data Guard Operational Prerequisites:

 Same Oracle software release must be used for both primary and
standby databases. The operating system running on primary and
standby locations must be same, but operating system release may not
need to be same.
 The Primary Database must run in ARCHIVELOG mode.
 The hardware and Operating system architecture on primary and standby
location must be same.
 Each primary and standby database must have its own control file.
Architecture:

The Oracle9i Data Guard architecture incorporates the following items:

Primary Database - A production database that is used to create standby

databases. The archive logs from the primary database are transferred and
applied to standby databases. Each standby can only be associated with a single
primary database, but a single primary database can be associated with multiple
standby databases.

Standby Database - A replica of the primary database.

Log Transport Services - Control the automatic transfer of archive redo log
files from the primary database to one or more standby destinations.

Network Configuration - The primary database is connected to one or more

standby databases using Oracle Net.

Log Apply Services - Apply the archived redo logs to the standby database.
The Managed Recovery Process (MRP) actually does the work of maintaining and
applying the archived redo logs.

Role Management Services - Control the changing of database roles from

primary to standby. The services include switchover, switchback and fail over.

116
The services required on the primary database are:

Log Writer Process (LGWR) - Collects redo information and updates the
online redo logs. It can also create local archived redo logs and transmit online
redo to standby databases.

Archiver Process (ARCn) - One or more archiver processes make copies of

online redo logs either locally or remotely for standby databases.

Fetch Archive Log (FAL) Server - Services requests for archive redo logs
from FAL clients running on multiple standby databases. Multiple FAL servers
can be run on a primary database, one for each FAL request.

The services required on the standby database are:

Fetch Archive Log (FAL) Client - Pulls archived redo log files from the
primary site. Initiates transfer of archived redo logs when it detects a gap
sequence.

Remote File Server (RFS) - Receives archived and/or standby redo logs from
the primary database.
Archiver (ARCn) Processes - Archives the standby redo logs applied by the
managed recovery process (MRP).

Managed Recovery Process (MRP) - Applies archive redo log information to

the standby database.

Step-by-Step Stand by Database Configuration:

Step1: Configure Listener in Production Server and Standby Server.

 TIPS: You should try to Create Listener (Standby) by using Net
Configuration Assistant on Standby Server.
 TIPS: assume Listener already configure with PROD name on Primary
Node. If Listener not configured on Primery Node , You Should Create
Listener by using Net Configuration Assistant on Primary Server.
Step2: Configure [Link] in Production Server and Standby Server.
following [Link] entry on Production Database and Standby Database
# Connection string for Primary Instance.
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Production IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)
# Connecting string for Standby Instance
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY IP)(PORT = 1521))
)

117
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)

Step3: Put your production database in Archive Log mode if your database not
running in Archive log mode add following entries in [Link] file in Production
Server.
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_DEST_1='LOCATION=C:\oracle\database\archive MANDATORY
REOPEN=30'
LOG_ARCHIVE_DEST_2='SERVICE=STANDBY REOPEN=300'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
LOG_ARCHIVE_FORMAT=ARC%[Link]
REMOTE_ARCHIVE_ENABLE=true
STANDBY_FILE_MANAGEMENT=AUTO
STANDBY_ARCHIVE_DEST = 'C:\standby\archive '
# db_file_name_convert: do not need; same directory structure
# log_file_name_convert: do not need; same directory structure

Step4 : After add above syntax in [Link] file, copy [Link] file from production
server to Standby server in Oracle_Home\Database\ folder.

Step5 : On the both system, the same directory structure was set-up
Step6 : Place production database in FORCE LOGGING mode by using following
statement:
SQL> alter database force logging;

Database altered.

Step7 : Identify the primary database Data files:

SQL> select name from v$datafile;

Step8 : Make a copy of Production data files and redo flog file by performing
following steps:
Shutdown the Primary Database

SQL> shutdown immediate and put your primary database in Archive

log mode after archive log enable shutdown the database the database.

Copy the Datafiles and redo log files to standby location by using OS Command

Note: Primary Database must be shutdown while coping the files.

Step9 : Restart the Production Database

SQL> startup;

Step10 : Create Control file for Standby Database Issue the following command
on production database to create control file for the standby database.
SQL> Alter database create standby controlfile as
'c:\controlfile_standby.ctl';

118
Database altered.

Note: The filename for newly created standby control file must be different of
current control file of the production database. Also control file for standby
database must be created after the last timestamp for the backup Datafiles.

Step11 : Create [Link] file for standby database.

Copy [Link] file from Production Server to Stand by Server in Database folder
in oracle home directory and add following entries:

LOG_ARCHIVE_START = TRUE
LOG_ARCHIVE_DEST_1 = 'LOCATION=c:\oracle\database\archive MANDATORY'
LOG_ARCHIVE_FORMAT = arch%[Link]
REMOTE_ARCHIVE_ENABLE = true
STANDBY_FILE_MANAGEMENT = AUTO
LOG_ARCHIVE_MIN_SUCCEED_DEST=1
STANDBY_ARCHIVE_DEST = 'C:\standby\archive '
fal_server = FAL
fal_client = STANDBY
# db_file_name_convert: do not need; same directory structure
# log_file_name_convert: do not need; same directory structure

Note: Although most of the initialization parameter settings in the text

initialization parameter file that you copied from the primary system are also
appropriate for the physical standby database, some modifications need to be
made.

Edit created pfile from primary database.

control_files - Specify the path name and filename for the standby control file.

standby_archive_dest - Specify the location of the archived redo logs that will
be received from the primary database.

db_file_name_convert - Specify the location of the primary database datafiles

followed by the standby location of the datafiles. This parameter will convert the
filename of the primary database datafiles to the filename of the standby
datafile filenames. If the standby database is on the same system as the
primary database or if the directory structure where the datafiles are located on
the standby site is different from the primary site then this parameter is
required.

log_file_name_convert - Specify the location of the primary database logs

followed by the standby location of the logs. This parameter will convert the
filename of the primary database log to the filenames of the standby log. If the
standby database is on the same system as the primary database or if the
directory structure where the logs are located on the standby site is different
from the primary site then this parameter is required.

log_archive_dest_1 - Specify the location where the redo logs are to be

archived on the standby system. (If a switchover occurs and this instance
becomes the primary database, then this parameter will specify the location
where the online redo logs will be archived.)

119
standby_file_management - Set to AUTO.

remote_archive_enable - Set to TRUE.

instance_name - If this parameter is defined, specify a different value for the

standby database than the primary database when the primary and standby
databases reside on the same host.

lock_name_space - Specify the standby database instance name. Use this

parameter when you create the physical standby database on the same system
as the primary database. Change the INSTANCE_NAME parameter to a value
other than its primary database value, and set this LOCK_NAME_SPACE
initialization parameter to the same value that you specified for the standby
database INSTANCE_NAME initialization parameter.

Also change the values of the parameters background_dump_dest,

core_dump_dest and user_dump_dest to specify location of the standby
database.

Step12 : Create a Window service in Standby Server

If standby database is running on windows system, then oradim utility is used to
create windows service. Issue following command from the command prompt
window

C:\>oradim -new -sid PROD -intpwd PROD -startmode a

Step: 13 Start Physical standby database

Start up the stand by database using following commands

C:\>set oracle_sid=PROD
C:\>sqlplus /nolog
SQL> conn sys/prod as sysdba

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL> alter database mount standby database;

Database altered.

Step: 14 Initiate Log apply services The example includes the DISCONNECT
FROM SESSION option so that log apply services run in a background session.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT FROM SESSION;

120
Database altered.

Step: 15 Now go to production database prompt

SQL> alter system switch logfile;

Database altered.

Step: 16 Verifying the Standby Database On standby database query the

V$ARCHIVED_LOG view to verify that redo log received.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM
V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Result:

SEQUENCE# FIRST_TIME NEXT_TIME

14 25-APR-05 [Link] 25-APR-02 [Link]

15 25-APR-05 [Link] 25-APR-02 [Link]

16 25-APR-05 [Link] 25-APR-02 [Link]

Archive the current log on the primary database using following statement.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

On standby database query the V$ARCHIVED_LOG view

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM

V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Result:

SEQUENCE# FIRST_TIME NEXT_TIME

14 25-APR-05 [Link] 25-APR-02 [Link]

15 25-APR-05 [Link] 25-APR-02 [Link]

16 25-APR-05 [Link] 25-APR-02 [Link]

17 25-APR-05 [Link] 25-APR-02 [Link]

TIPS: Now connect system/manager on production database and create table or

insert row in any table.

Now connect as sys on production database and execute following SQL

statement

SQL> alter system switch logfile;

On standby database execute following SQL statements

121
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT FROM SESSION;

Database altered.

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> alter database open read only;

Database altered. And check whether the changes applied on the standby
database or not.

Step: 17 Query V$MANAGED_STANDBY

Query the physical standby database to monitor log apply and log transport
services activity at the standby site.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#,

BLOCKS FROM V$MANAGED_STANDBY;

Result:

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

ARCH CONNECTED 0 0 0 0

ARCH CONNECTED 0 0 0 0

MRP0 WAIT_FOR_LOG 1 4205 0 0

RFS RECEIVING 0 0 0 0

RFS RECEIVING 1 3524 2445 2445

RFS WRITING 1 4205 14947 20480

If we do the same query on the production database

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#,

BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

ARCH CLOSING 1 4203 2049 124

ARCH CLOSING 1 4204 1 1551

LGWR WRITING 1 4205 14947 1

From the query on the primary database, we see the current sequence being
written to in the redo log area is 4205, and on the standby database we also see

122
the current archive log being applied is for sequence 4205. In the directory that
receives archive files on the standby database, the file
DWH0P01_0000004205.arc will exist and will be the same size as the redo log
on the primary database. However the primary database will not have
DWH0P01_0000004205.arc as a file in the archive area, as a log switch will not
have occurred yet, but both databases are synchronized at the same sequence
and block number, 14947.

Step: 18 Log files to check on both systems

On production database in the bdump directory, the alert log and files generated
by lgwr and lnsx can be checked for any problems On standby database in the
bdump directory, the alert log and files generated by mrpx can be checked for
any problems.

Standby Oracle Database by using RMAN

You can use the Recovery Manager DUPLICATE TARGET DATABASE FOR
STANDBY command to create a standby database.

RMAN automates the following steps of the creation procedure:

 Restores the standby control file.

 Restores the primary datafile backups and copies.
 Optionally, RMAN recovers the standby database (after the control file
has been mounted) up to the specified time or to the latest archived
redo log generated.
 RMAN leaves the database mounted so that the user can activate it,
place it in manual or managed recovery mode, or open it in read-only
mode.

After the standby database is created, RMAN can back up the standby
database and archived redo logs as part of your backup strategy. These
standby backups are fully interchangeable with primary backups. In other
words, you can restore a backup of a standby datafile to the primary
database, and you can restore a backup of a primary datafile to the standby
database.

Step-by-Step Stand by Database Configuration:

Step1: Configure Listener in Production Server and Standby Server.

 TIPS: You should try to Create Listener (Standby) by using Net Configuration
Assistant on Standby Server.
 TIPS: assume Listener already configure with PROD name on Primary Node.
If Listener not configured on Primery Node , You Should Create Listener by
using Net Configuration Assistant on Primary Server.
Step2: Configure [Link] in Production Server and Standby Server.
following [Link] entry on Production Database and Standby Database

# Connection string for Primary Instance.

PROD =

123
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Production IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)
# Connecting string for Standby Instance
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)

Step3: Put your production database in Archive Log mode if your database not
running in Archive log mode add following entries in [Link] file in Production
Server.
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_DEST_1='LOCATION=C:\oracle\database\archive MANDATORY
REOPEN=30'
LOG_ARCHIVE_DEST_2='SERVICE=STANDBY REOPEN=300'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
LOG_ARCHIVE_FORMAT=ARC%[Link]
REMOTE_ARCHIVE_ENABLE=true
STANDBY_FILE_MANAGEMENT=AUTO
STANDBY_ARCHIVE_DEST = 'C:\standby\archive '
# db_file_name_convert: do not need; same directory structure
# log_file_name_convert: do not need; same directory structure

Step4 : Configure RMAN in Production Instance if not configured earlier.

Step5 : Take a full valied backup of Production instance

RMAN> backup database plus archivelog;

Step6 : go to Standby machine and Create Service for standby instance

Step7 : create a standby controlfile on production Machine

RMAN> backup current controlfile for standby

format='c:\rman_backup\stby_cfile.%U';

Step8 : Record last log sequance

SQL> alter system switch logfile;

SQL> alter system switch logfile;

124
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
100

Step8 : Backup new archive logs

RMAN>backup archivelog all;

Step9 : Make the RMAN Backups Available to Standby Server

Step10 : On the both system, the same directory structure was set-up

Step11 : Create [Link] file for standby database.

Copy [Link] file from Production Server to Stand by Server in Database folder in
oracle home directory and add following entries:

LOG_ARCHIVE_START = TRUE
LOG_ARCHIVE_DEST_1 = 'LOCATION=c:\oracle\database\archive MANDATORY'
LOG_ARCHIVE_FORMAT = arch%[Link]
REMOTE_ARCHIVE_ENABLE = true
STANDBY_FILE_MANAGEMENT = AUTO
LOG_ARCHIVE_MIN_SUCCEED_DEST=1
STANDBY_ARCHIVE_DEST = 'C:\standby\archive '
fal_server = FAL
fal_client = STANDBY
# db_file_name_convert: do not need; same directory structure
# log_file_name_convert: do not need; same directory structure

Note: Although most of the initialization parameter settings in the text initialization
parameter file that you copied from the primary system are also appropriate for the
physical standby database, some modifications need to be made.

Step: 12 Start Physical standby database

Start up the stand by database using following commands

C:\>set oracle_sid=PROD
C:\>sqlplus /nolog
SQL> conn sys/prod as sysdba

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

125
Step13 : Go to the Standby server and connect RMAN

Run the following:

CMD> rman target sys/change_on_install@prod_conn_string

RMAN > connect auxiliary sys/change_on_install

Step14 : The following RUN block can be used to fully duplicate the target
database from the latest full backup. This will create the standby database:

run {
# Set the last log sequence number
set until sequence = 100 thread = 1;
# Allocate the channel for the duplicate work
allocate auxiliary channel ch1 type disk;
# Duplicate the database to ORA920
duplicate target database for standby dorecover nofilenamecheck ;
}

RMAN> exit

Step15 : Put the Standby in Managed recovery Mode

On the standby database, run the following:

SQL> sqlplus "/ as sysdba"

SQL> recover standby database;

SQL> alter database recover managed standby database disconnect;

Database altered.

Standby Database Maintenance

Cancel/Stop Managed Standby Recovery

While connected to the standby database follow following steps:

 ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;

 RECOVER MANAGED STANDBY DATABASE CANCEL;
 ALTER DATABASE OPEN READ ONLY;

The database can subsequently be switched back to recovery mode as follows:

Start-up managed recovery on standby database

 CONNECT / AS SYSDBA

126
  SHUTDOWN IMMEDIATE
 STARTUP NOMOUNT
 ALTER DATABASE MOUNT STANDBY DATABASE;
 RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM
SESSION;

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby).

These roles can be altered at runtime without loss of data or resetting of redo logs.
This process is known as a Switchover and can be performed using the following
statements:

While connected to the primary database, issue the following commands:-

 CONNECT / AS SYSDBA
 ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
 SHUTDOWN IMMEDIATE;
 STARTUP NOMOUNT
 ALTER DATABASE MOUNT STANDBY DATABASE;
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT FROM SESSION;

Now the original Primary database is in Standby mode and waiting for the new
Primary database to activate, which is done while connected to the standby database
(not the original primary)

 CONNECT / AS SYSDBA
 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
 SHUTDOWN IMMEDIATE;
 STARTUP

This process has no affect on alternative standby locations. The process of

converting the instances back to their original roles is known as a Switchback. The
switchback is accomplished by performing another switchover.

Database Fail Over

Graceful Database Fail over occurs when database fail over causes a standby
database to be converted to a primary database:

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

 ALTER DATABASE ACTIVATE STANDBY DATABASE;

This process will recovery all or some of the application data using the standby redo
logs, therefore avoiding reinstantiation of other standby databases. If completed
successfully, only the primary database will need to be reinstatiated as a standby
database.

Standby Diagnosis Query for Primary Node

127
Query 1: protection_level should match the protection_mode after the next log
switch

select name,database_role role,log_mode,

protection_mode,protection_level from v$database;

NAME ROLE LOG_MODE PROTECTION_MODE

PROTECTION_LEVEL

TEST PRIMARY ARCHIVELOG MAXIMUM PERFORMANCE MAXIMUM

PERFORMANCE

1 row selected.

Query 2: ARCHIVER can be (STOPPED | STARTED | FAILED). FAILED means that the
archiver failed to archive a log last time, but will try again within 5 minutes.
LOG_SWITCH_WAIT The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching
is waiting for. Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is
room in the current online redo log, then value is NULL

select instance_name,host_name,version,archiver,log_switch_wait from

v$instance;

INSTANCE_NAME HOST_NAME VERSION ARCHIVE

LOG_SWITCH_

TEST flex-suntdb [Link].0 STARTED

1 row selected.

Query 3: Query give us information about catpatch.

select version, modified, status from dba_registry where comp_id =

'CATPROC';

VERSION MODIFIED STATUS

[Link].0 19-NOV-2004 [Link] VALID

1 row selected.

Query 4: Force logging is not mandatory but is recommended. Supplemental logging

must be enabled if thestandby associated with this primary is a logical standby.
During normal operations it is acceptable for SWITCHOVER_STATUS to be SESSIONS
ACTIVE or TO STANDBY.

select
force_logging,remote_archive,supplemental_log_data_pk,supplemental_log
_data_ui, switchover_status,dataguard_broker from v$database;

128
FORCE_LOGGING REMOTE_ARCHIVE SUP SUP SWITCHOVER_STATUS
DATAGUARD_BROKER

NO ENABLED NO NO SESSIONS
ACTIVE DISABLED

1 row selected.

Query 5: This query produces a list of all archive destinations. It shows if they are
enabled, what process is servicing that destination, if the destination is local or
remote, and if remote what the current mount ID is.

select dest_id "ID",destination,status,target,schedule,process,mountid mid

from v$archive_dest order by dest_id;

ID DESTINATION STATUS TARGET SCHEDULE

PROCESS MID

1 /applprod/archprod VALID PRIMARY ACTIVE

ARCH 0

2 STANDBY VALID STANDBY ACTIVE

ARCH 0

........

........

10 rows selected.

Query 6: This select will give further detail on the destinations as to what options
have been set. Register indicates whether or not the archived redo log is registered
in the remote destination control file.

select dest_id "ID",archiver,transmit_mode,affirm,async_blocks async,

net_timeout net_time,delay_mins delay,reopen_secs reopen,
register,binding from v$archive_dest order by dest_id;

ID ARCHIVER TRANSMIT_MOD AFF ASYNC NET_TIME DELAY REOPEN

REG BINDING

1 ARCH SYNCHRONOUS NO 0 0
0 300 YES MANDATORY

2 ARCH SYNCHRONOUS NO 0 0
0 300 YES OPTIONAL

...

...

129
10 rows selected.

Query 7: The following select will show any errors that occured the last time an
attempt to archive to the destination was attempted. If ERROR is blank and status is
VALID then the archive completed correctly.

select dest_id,status,error from v$archive_dest;

DEST_ID STATUS ERROR

1 VALID

2 VALID

3 INACTIVE

10 rows selected.

Query 8: The query below will determine if any error conditions have been reached
by querying the v$dataguard_status view (view only available in 9.2.0 and above):

select message, timestamp from v$dataguard_status where severity in

('Error','Fatal') order by timestamp;

no rows selected

Query 9: The following query will determine the current sequence number and the
last sequence archived. If you are remotely archiving using the LGWR process then
the archived sequence should be one higher than the current sequence. If remotely
archiving using the ARCH process then the archived sequence should be equal to the
current sequence. The applied sequence information is updated at log switch time.

select ads.dest_id,max(sequence#) "Current Sequence",

max(log_sequence) "Last Archived" from v$archived_log al, v$archive_dest
ad, v$archive_dest_status ads where ad.dest_id=al.dest_id and
al.dest_id=ads.dest_id group by ads.dest_id;

DEST_ID Current Sequence Last Archived

1 233 233

2 233 233

2 rows selected.

Query 10: The following select will attempt to gather as much information as
possible from the standby. SRLs are not supported with Logical Standby until Version
10.1.

130
select dest_id id,database_mode db_mode,recovery_mode,
protection_mode,standby_logfile_count "SRLs", standby_logfile_active
ACTIVE, archived_seq# from v$archive_dest_status;

ID DB_MODE RECOVER PROTECTION_MODE SRLs ACTIVE

ARCHIVED_SEQ#

1 OPEN IDLE MAXIMUM PERFORMANCE 0

0 233

2 MOUNTED-STANDBY IDLE MAXIMUM PERFORMANCE 0 0 2

33

...

...

10 rows selected.

Query 11: Query v$managed_standby to see the status of processes involved in the
shipping redo on this system. Does not include processes needed to apply redo.

select process,status,client_process,sequence# from v$managed_standby;

PROCESS STATUS CLIENT_P SEQUENCE#

ARCH CLOSING ARCH 233

ARCH CLOSING ARCH 232

2 rows selected.

Query 12: The following query is run on the primary to see if SRL's have been
created in preparation for switchover.

select group#,sequence#,bytes from v$standby_log;

no rows selected

Query 13: The above SRL's should match in number and in size with the ORL's
returned below:

select group#,thread#,sequence#,bytes,archived,status from v$log;

Standby Diagnosis Query for Standby Node

Query 1: ARCHIVER can be (STOPPED | STARTED | FAILED) FAILED means that the
archiver failed to archive a log last time, but will try again within 5 minutes.
LOG_SWITCH_WAIT The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching

131
is waiting for. Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is
room in the current online redo log, then value is NULL

select instance_name,host_name,version,archiver,log_switch_wait from

v$instance;

INSTANCE_NAME HOST_NAME VERSION ARCHIVE LOG_SWITCH_

TEST flex-sprod [Link].0 STARTED

1 row selected.

Query 2: The following select will give us the generic information about how this
standby is setup. The database_role should be standby as that is what this script is
intended to be ran on. If protection_level is different than protection_mode then for
some reason the mode listed in protection_mode experienced a need to downgrade.
Once the error condition has been corrected the protection_level should match the
protection_mode after the next log switch.

Selectname,database_role,log_mode,controlfile_type,protection_mode,prot
ection_level from v$database;

Query 3: Force logging is not mandatory but is recommended. Supplemental logging

should be enabled on the standby if a logical standby is in the configuration. During
normal operations it is acceptable for SWITCHOVER_STATUS to be SESSIONS
ACTIVE or NOT ALLOWED.

select
force_logging,remote_archive,supplemental_log_data_pk,supplemental_log
_data_ui, switchover_status,dataguard_broker from v$database;

FORCE_LOGGING REMOTE_ARCHIVE SUP SUP

SWITCHOVER_STATUS DATAGUARD_BROKER

NO ENABLED NO NO SESSIONS
ACTIVE DISABLED

1 row selected.

Query 4: This query produces a list of all archive destinations and shows if they are
enabled, what process is servicing that destination, if the destination is local or
remote, and if remote what the current mount ID is. For a physical standby we
should have at least one remote destination that points the primary set but it should
be deferred.

select dest_id "ID",destination,status,target,

archiver,schedule,process,mountid from v$archive_dest;

Query 5: If the protection mode of the standby is set to anything higher than max
performance then we need to make sure the remote destination that points to the
primary is set with the correct options else we will have issues during switchover.

132
select
dest_id,process,transmit_mode,async_blocks,net_timeout,delay_mins,reop
en_secs,register,binding from v$archive_dest;

Query 6: The following select will show any errors that occured the last time an
attempt to archive to the destination was attempted. If ERROR is blank and status is
VALID then the archive completed correctly.

select dest_id,status,error from v$archive_dest;

Query 7: Determine if any error conditions have been reached by querying

thev$dataguard_status view (view only available in 9.2.0 and above):

select message, timestamp from v$dataguard_status where severity in

('Error','Fatal') order by timestamp;

Query 8: The following query is ran to get the status of the SRL's on the standby. If
the primary is archiving with the LGWR process and SRL's are present (in the correct
number and size) then we should see a group# active.

select group#,sequence#,bytes,used,archived,status from v$standby_log;

Query 9: The above SRL's should match in number and in size with the ORL's
returned below:

select group#,thread#,sequence#,bytes,archived,status from v$log;

Query 10: Query v$managed_standby to see the status of processes involved in the
configuration.

selectprocess,status,client_process,sequence#,block#,active_agents,known
_agents from v$managed_standby;

Query 11: Verify that the last sequence# received and the last sequence# applied
to standby database.

select max([Link]#) "Last Seq Recieved", max([Link]#) "Last

Seq Applied" from v$archived_log al, v$log_history lh;

Query 12: The V$ARCHIVE_GAP fixed view on a physical standby database only
returns the next gap that is currently blocking redo apply from continuing. After
resolving the identified gap and starting redo apply, query the V$ARCHIVE_GAP fixed
view again on the physical standby database to determine the next gap sequence, if
there is one. select * from v$archive_gap;

133