Ramana Ora

Basic Unix
Commands
Posted by Narashim Reddy .R on September 23, 2009
Knowing basic Unix commands should allow you to navigate your Unix system, confirm current
system status and manage files or directories.
Getting help
 man – view manual pages for Unix commands
Unix users
 id – print user identity
Unix file operations
 ls - list files and directories

 cat – concatenate files and show contents to the standard output
 cp – copy
 rm – remove the file
 mv – move the file
Unix directory management commands
 cd – change directory
 pwd – confirm current directory
 ln – make links and symlinks to files and directories
 mkdir – make new directory
 rmdir – remove empty directories
Unix system status commands
 uptime – find out how long the system has been up

 who - find out who is logged into the system
 uname - print Unix system information
Process management
 ps – process information
 top - show tasks and system status
Backup Strategies and commands form Metalink

Posted by Narashim Reddy .R on October 30, 2009
This is a very handy material. You may be doing recovery but have you done
mistakes in doing so.. say forget commands or sequence of commands to be
written.. Here is a material worth for collection.
Your feedback is most welcome..
BACKUP PROCEDURES :
It is a very important aspect of any database which should be planned carefully as

recovery depends upon the back up strategy which are being followed. Backup
strategy depends upon the mode of database. Different methods are adopted for
the database running in archivelog mode or database running in no-archivelog
mode .
Criteria :
[1] When database is running in archive-log mode.
Cold backup :
In init.ora, search for parameter control_files to find the name of control

file for that database. Query the v$datafiles and v$logfiles views to find the
names
of datafiles and redo logfiles associated with the database. Use the operating
system
command to take the backup of these files. *
In init.ora, search for parameter log_archive_dest to find the location of archived

files.
Use the operating system command to take the backup of these files.
Frequency: This backup has to be taken weekly.

Only Oracle database related file should be backedup. It will solve two problems
[a] Downtime of database will be less.
[b] Retrieval from cartridge will take less time.
[c] Less numbers of cartridges will be required.
File System Backup : Generally its frequency should be low. It will act as a backup
for all the files (Oracle+O.S.+Other). It will be needed if all the disks crash. If you
are creating important files on server then its frequency should be increased as
decided by the site incharge.
Hot backup:
In init.ora, search for parameter control_files to find the name of control file for
that database. Query the v$datafiles views to find the names of datafiles
associated with the database.
Use the operating system command to take the backup of these files.*
In init.ora, search for parameter log_archive_dest to find the location of archived

files.
Use the operating system command to take the backup of these files. *
Frequency: This backup has to be taken daily.
Logical Backup:
[1] Ideally Complete database export should be taken daily.

It is also called base backup.
[2] Take incremental export daily except on weekends.
On weekends, cumulative database export should be taken. When cumulative

export is taken, one should remove incremental export to save space on disk.
On month end, take complete database export and remove previously stored
cumulative export logical backups.
[3] Take important user level export daily.

Either of the above option can be implemented at the site but order of prefrence
should be first try [1], if not then use [2] else last option should be [3]
Cartridges Strategy :
If you are taking complete database export then use three different sets of
cartridges ( Grand father , Father and Son concepts .) on three different days .
And rotate these cartridges again .
For Incremental Backups use six different sets of cartridges on six different days
. And rotate these cartridges again after successfully completion of Cummulative
database export backup .
For Cummulative Backups use different cartridges in every week and rotate those
cartridges in the next month after successfully completion of Complete database
export backup.
Recovery :
In day to day operation the most common type of failure is table drop or partial
data loss in any table or instance failure. Using export backed up dump file
(expdat.dmp) one can recover first two type of problems.
For instance failure, simply restart the database, oracle will automatically recover
the database (Instance recovery).
For more complicated type of problems like media crash (data file loss etc.), please
refer to annexure-1.
[2] When database is running in no archive-log mode.
Cold backup :
In init.ora, search for parameter control_files to find the name of control
file for that database. Query the v$datafiles and v$logfiles views to find the
names
of datafiles and redo logfiles associated with the database. Use the operating
system
command to take the backup of these files. Ideally this backup should be taken
daily.
Logical Backup:
[1] Ideally Complete database export should be taken daily.

It is also called base backup.
[2] Take incremental export daily except on weekends.
On weekends, cumulative database export should be taken. When cumulative

export is taken, one should remove incremental export to save space on disk.
On month end, take complete database export and remove previously stored
cumulative export logical backups.
[3] Take important user level export daily.
Either of the above option can be implemented at the site but order of prefrence
should be first try [1], if not then use [2] else last option should be [3]
Cartridges Strategy :
If you are taking complete database export then use three different sets of
cartridges ( Grand father , Father and Son concepts .) on three different days .
And rotate these cartridges again .
For Incremental Backups use six different sets of cartridges on six different days
. And rotate these cartridges again after successfully completion of Cummulative
database export backup .
For Cummulative Backups use different cartridges in every week and rotate those
cartridges in the next month after successfully completion of Complete database
export backup.
Recovery :
It is a very important process and it should be done very carefully. In day to day
operation the most common type of failure is table drop or partial data loss in any
table or instance failure. Using export backed up dump file (expdat.dmp) one can
recover first two type of problems. For instance failure, simply restart the
database, oracle will automatically recover the database (Instance recovery). For
more complicated type of problems like media crash (data file loss etc.), please
refer to annexure-1.
* Commands to be used in copying file(s) to backup device :
In Unix :
[a] cpio -ocBv < [name of file] > [/dev/rmt0.1|/dev/rmt0]

or
find / -name [pattern] -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0]
[b] tar -cvf [name of file] > [/dev/rmt0.1|/dev/rmt0]
or
tar -cvf /
Complete file system backup :
In Unix :
[a] find / -name -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0]
or
[b] To copy all files of unix to backup device
tar -cvf /
In Window NT :
Use backup utility to copy the necessary files.
Commands to be used in restoring file(s) from backup device :
In Unix :
[a] cpio -icBv < [/dev/rmt0.1|/dev/rmt0]
or
cpio -icBv “[pattern]” < [/dev/rmt0.1|/dev/rmt0]
[b] tar -xvf < [/dev/rmt0.1|/dev/rmt0]
In Window NT :
Use restore utility to copy the necessary files from backup device.
Annexure – 1
1. LOSS OF NON-ESSENTIAL DATAFILE WHEN DATABASE IS DOWN

( DATABASE CAN BE IN ARCHIVELOG MODE OR NO ARCHIVELOG
MODE )
SCENARIO
[1] Database startup fails with errors :
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.
REQUIREMENT
[1] The script which will recreate the objects in the datafile like script which will
create indexex.
TIME TAKEN IN RECOVERY

DATABASE WILL BE READY FOR USE IN (5 MIN+ TIME TAKEN TO CREATE
INDEXES)
NON-ESSENTIAL DATAFILES
DATAFILE OF INDEX TABLESPACE, TEMPORARY TABLESPACE.
SOLUTION
Shutdown the database.(shutdown immediate).
Take complete backup of current database.
Startup mount
Query the v$recover_file view along with v$datafile with a join on file# and note
down the name of file
say it is /prodebs/test/ind.dbf.
Alter database datafile ‘/prodebs/test/ind.dbf’ offline;
(if database is in noarchivwlog mode command will be
Alter database datafile ‘/prodebs/test/ind.dbf’ offline drop; )
Alter database open;
Drop tablespace user_index including contents;
Create tablespace user_index
datafile ‘/prodebs/test/ind.dbf’ size 1M;
Run the script which will built indexes*.
Shutdown the database and take backup if necessary.
Startup.
* NB : For temporary tablespace skip this step.

———————————————————————————————–
2. MISSING MIRRORED ONLINE REDO LOG FILES (DATABASE IS UP/DOWN)
SCENARIO
Database opens neatly but in alert log two error messages are logged with errors :
(error from lgwr. Error is also written in lgwr trace file)
ora 313 open failed for members ..
ora 312 name of redo log memeber missing
ora 7360 OS error
ora 321 Can not update logfile header

DATABASE WILL BE READY FOR USE IN 5 MIN.
SOLUTION
Shutdown the database.(shutdown).

Startup mount
Query v$logfile view and find which member has become invalid.
Query v$log view and find which group is current and size of group members (say it
is b).
If the member of current log group (say it is 1) is corrupted issue the following
commands :
Alter system switch logfile
If you can add one more member to corrupted log group ie maximum log member is
not reached add one more
member to that group
Alter database add logfile member ‘filespec’ to group 1;
Shutdown the database
Startup the database
If you can not add one more member to corrupted log group
create one more log group with equal members and size of non corrupted log group.
Alter database group 3 (‘filespec’,'filespec’) size b;
Drop corrupted log group.
alter database drop logfile group 1;
Manually remove other members of this corrupted log group (ie rm in unix)
Shut down the database
Startup the database
———————————————————————————————–
3. RECOVER A LOST DATAFILE WITH NO BACKUP AND ALL ARCHIVED
LOG FILES
SCENARIO
Database startup fails with errors :
REQUIREMENT
For full recovery, database should be in archivelog mode.

DATABASE WILL BE READY FOR USE IN MIN 10 mins.
SOLUTION

Startup mount;
Query v$recover_file
Query v$datafile and find the name of datafile which is missing.(say it is
‘/prodebs/test/user_odc.dbf’)
Now issue the following commands in the given order :
alter database datafile ‘/prodebs/test/user_odc.dbf’ offline;
alter database create datafile ‘/prodebs/test/user_odc.dbf’ as
‘/prodebs/test/user_odc1.dbf’;
(removed file) (new file)
alter database datafile ‘/prodebs/test/user_odc1.dbf’ online;
alter database recover datafile ‘/prodebs/test/user_odc1.dbf’; or recover
database
It will generally recover the database if you have all the archived file with you
alter database open;
Shutdown the database and take necessary backup if required.
Start the database.
———————————————————————————————–
4. RECOVER A LOST DATAFILE WITH BACKUP AND ALL ARCHIVED
LOG FILES
SCENARIO
REQUIREMENT
For full recovery, database should be in archivelog mode.

DATABASE WILL BE READY FOR USE IN 5 mins.
SOLUTION

Startup mount;
Query v$recover_file
Query v$datafile and find the name of datafile which is missing.(say it is
‘/prodebs/test/user_odc.dbf’)
Copy the archived datafile (old one that is in backup) and give following commands :
recover database;
Start the database.
———————————————————————————————–
5. RECOVER A LOST DATAFILE WITH BACKUP AND MISSING ARCHIVED
LOG FILES.
SCENARIO
REQUIREMENT
For recovery, database should be in archivelog mode.
CONDITION
Recovery will be incomplete.

SOLUTION

Copy all your datafiles from backup except control file.
Startup mount;
Copy the archived datafile (old one that is in backup) and give following commands :
recover database until cancel
alter database open resetlogs;
Start the database.
———————————————————————————————–
6. LOSS OF DATAFILE WHEN THE DATABASE IS IN NOARCHIVELOG MODE
WITH NO LOGICAL BACKUP AND RECOVERY.
SCENARIO
Database startup fails with errors : (a) on monday morning (b) on thursday
ora 1110 give the name of datafile which is missing and found to be user data file.
There is no export and import backup (Logical backup).
Cold backup is taken once in every week . Here backup is taken on every sunday and
this is the last activity
on sunday.
Problem has occured on (a) on monday morning (b) on thursday.
Here data file (s) associated with user tablespace is (are) lost.
SOLUTION when Problem has occured on (a) on monday morning

Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the backup.
Start the database. There is no data loss.
SOLUTION when Problem has occured on (a) on thursday morning. Here data loss
will occur.

Start the database. There is data loss for monday, tuesday and wednessday.
Ask user to reenter the data.
7. LOSS OF DATAFILE WHEN THE DATABASE IS IN NOARCHIVELOG MODE

WITH LOGICAL BACKUP AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
There is export and import backup. (Logical backup).
Strategy : So along with coldback up a complete database backup is also taken.
(i) After this on everyday, an incremental backup is also taken.
(ii) After this on everyday, complete database backup is also taken.
on sunday.

[i] SOLUTION when Problem has occured on thursday morning and incremental
backup is taken.

Start the database.
Apply incremental export using import file starting from monday to
wednesday.There is no data loss.
[ii] SOLUTION when Problem has occured on thursday morning and daily complete
database logical
backup is taken.

Start the database.
Apply wednesday complete export backup. There is no data loss.
8. LOSS OF SYSTEM DATAFILE WHEN THE DATABASE IS IN ARCHIVE

LOG MODE AND RECOVERY.
SCENARIO
on sunday.
Here data file (s) associated with system tablespace is (are) lost.

SOLUTION
Copy the missing system database file(s).
Startup mount exclusive;
recover database;
Database is ready for use.
9. LOSS OF NON SYSTEM DATAFILE WITHOUT ROLLBACK SEGMENTS

WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.
SCENARIO
on sunday.

SOLUTION -1 (DATABASE RECOVERY)

Copy the missing database file(s).
recover database;
SOLUTION -2 (DATAFILE RECOVERY)*

Alter database datafile ” offline;
recover datafile ”
Alter database datafile ” online;
* If multiple datafiles are lost use parallel recovery method (from muliple terminal
use the same method for different files).
SOLUTION -3 (TABLESPACE RECOVERY)

Alter database datafile ” offline;
Alter tablespace offline temporary;
recover tablespace ;
Alter tablespace online ;
10. LOSS OF NON SYSTEM DATAFILE WITH ROLLBACK SEGMENTS WHEN

THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.
SCENARIO
on sunday.
Datafile(s) associated with rollback segment tablespace is (are) lost.

SOLUTION –

Comment the ROLLBACK_SEGMENT parameter or assign
ROLLBACK_SEGMENT=(SYSTEM)
in init.ora file before startup.
Copy the missing rollback segment database file(s).
alter database datafile ” offline;
Alter tablespace offline temporary;
Alter tablespace online;
Query dba_rollback_segs (column name segment_name, status) and note down the
name of segment
name having status recovery (say they are r01, r02,r03).
alter rollback segment r01 online;
shutdown the database.
Remove comment from rollback_segment parameter or remove system value and
give the name of rollback
segments which you want to be online when database starts.
Start the database.
11. LOSS OF UNARCHIVED ONLINE LOG FILES WHEN THEY ARE NOT
MIRRORED WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND
RECOVERY.
SCENARIO
on sunday.
All the online redo log files are lost.
All the data files and current control files are intact.

SOLUTION –

Copy the all the database files from latest offline or online backup.
recover database until cancel;
Take cold backup. It is strongely advised.
Start the database.
12. DATABASE CRASH DURING HOT BACKUP WHEN THE DATABASE IS IN

ARCHIVE LOG MODE AND RECOVERY.
SCENARIO
While taking hot backup, database crashes.
(a) When Oracle Version is 7.2 or more.
(b) When Oracle Version is 7.1.

[a] DATABASE WILL BE READY FOR USE IN MIN 2 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION – (Oracle Version is 7.2 or more)

Query the view v$backup and get file # having status active. Now from v$datafile
get the name of
file which is active in v$backup
alter database datafile ” end backup;
alter database open ;
Start the database.
SOLUTION – (Oracle Version is 7.1 )

recover database ;
( This may take significant amount of time if a large number of archived logs are to
be applied)
Start the database.
13. LOSS OF CONTROL FILE AND WHEN THE DATABASE IS IN ARCHIVE

LOG MODE AND RECOVERY.
SCENARIO
[a] Loss of control file when it is mirrored.
[b] Loss of control file when there is a backup and it is not mirrored but not
before last reset log option.
[c] Loss of control file when there is no backup and it is not mirrored (total loss).
NB : Loss of control file when there is a backup and it is not mirrored but it is
(suppose database is open on day x with alter database startup resetlogs.So your
control file should
be before xth day )
startup/recover database using backup controlfile

Oracle error no is ora 1190 :control file or data file 1 is from before the last
RESETLOGS
ora 1110 :name of system datafile.
[b] Loss of control file when there is a backup and it is not mirrored but not
(suppose database is open on day x with alter database startup resetlogs.So your
control file should
be after xth day )
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/prodebs/test/sys_odc.dbf’
ORA-01207: file is more recent than control file – old control file

[c] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION – (Loss of control file when it is mirrored)

Copy the second control file to this disk.
Rename this control file to the lost one.
Start the database.
SOLUTION – (Loss of all the control file(s) )

run backup of controlfile which you might have taken using the following command :
alter database backup controlfile to trace;
It creates a script. After editing that script it looks like this :
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “” NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
‘/prodebs/test/redo_odc11.dbf’,
‘/prodebs/test/redo_odc12.dbf’
) SIZE 50K,
GROUP 2 (
‘/prodebs/test/redo_odc21.dbf’,
‘/prodebs/test/redo_odc22.dbf’
) SIZE 50K
DATAFILE
‘/prodebs/test/sys_odc.dbf’,
‘/prodebs/test/sys_odc1.dbf’,
‘/prodebs/test/user_odc.dbf’,
‘/prodebs/test/temp_odc.dbf’,
‘/prodebs/test/rbs_odc.dbf’,
‘/prodebs/test/ind.dbf’
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
Start the database.
SOLUTION – (Loss of control file when there is a backup and it is not mirrored)

Copy the old control file to this disk.
startup mount exclusive;
If you have any tablespace which is read only, take all the datafile offiline related
to this tablespace.
recover database using backup controlfile;
Offline datafile should bring to online status. (alter database datafile ‘< name of
datafile>’ online;)
(for read only tablespace)
Shutdown the database.
Take cold backup. It is strongely advised.
Start the database.
14. DATABASE SPACE MANAGEMENT WHEN THE DATABASE IS IN
ARCHIVE LOG MODE AND RECOVERY (RESIZING DATAFILE).
SCENARIO
Space management when :
(a) Oracle Version is 7.2 or more.
(b) Oracle Version is 7.1.
Oracle Error is : ora 00376 file # can not be read at this time.
ora 01110 name of datafile.

SOLUTION – (Oracle Version is 7.2 or more)

Startup open;
Query the view v$datafile get the name of file which you want to resize.
alter database datafile ” resize [m/k];
Take backup if necessary.
Start the database.
SOLUTION – (Oracle Version is 7.1 )
[a] Restore the datafile and apply recovery. Resizing is not possible.

Copy the deleted datafile
recover database ;
be applied)
Start the database.
[b] If deleted datafile is not available in backup .

alter database add datafile ” as ”;
recover database ;
be applied)
Start the database.
[c] Rebuild tablespace

Requirement : Logical backup is there.

alter tablespace offline;
drop tablespace ;
create tablespace datafile ” size [m/k];
alter tablespace online;
use export / import method to recover the loss data.
Take backup if necessary.
Start the database.
15. RECOVERY THROUGH RESETLOGS .

CONDITIONS :
[A] When online redo logs files are deleted.

[B] Loss of all control files.
[C] When recovery is done through old control files.
Events
[1]
a- Cold backup is taken.
b- Loss of redo log file.and media recovery. At this moment, a backup is taken.
c- Loss of data file
[2]
a- Cold backup is taken.
b- Loss of redo log file.and media recovery. At this moment, a backup is not taken.
c- Loss of data file .
SOLUTION – [1]

Copy the most recent cold backup of datafiles.
Recover database;
Take a cold backup.
Start the database.
Advantage : All the data will be recovered.
SOLUTION – [2]-i

Export all the data from the tablespace (all objects in missing file will be
inaccessible),
Drop and recreate the tablespace.
Import all the data taken from tablespace.
recover datafile ” ;
Take a cold backup.
Start the database.
Disadvantage : All the data will be lost that was entered in datafile 5;
SOLUTION – [2]-ii

Copy the most recent cold backup of datafile and control file only. Do not copy
redo log file.
Recover database;
Take a cold backup.
Start the database.
Disadvantage : All the data will be lost that after event b.
16. LOSS OF DATA FILE WHEN THE DATABASE IS IN ARCHIVE LOG MODE
AND RECOVERY.
SCENARIO
on sunday.
Datafile(s) associated with user tablespace is (are) permanently lost.

SOLUTION –

alter database create datafile ” ;
recover datafile ” ;
Start the database.
17. SYSTEM CLOCK CHANGE AND POINT-IN-TIME RECOVERY.
SCENARIO
on sunday.

SOLUTION –

recover database until time ” ;
Take a cold backup of database;
Start the database.
18. OFFLINE TABLESPACES AND RECOVERY.
SCENARIO
on sunday.

SOLUTION – 1

recover database ;
alter tablespace online;
Shutdown the database.
Start the database.
SOLUTION – 2 (better)

Query the view v$datafile and note which datafile is offline say it is .
alter database datafile <’a'> online;
recover database;
shutdown the database
Start the database.
Internals of Shared Pool
Internals of Shared Pool
Purpose and Scope:
This article is intended to provide a inside view and working of Shared Pool and its
solution since Oracle 7 version thru Oracle 10g Release 2.
Introduction to Shared Pool
It is fundamentally to serve the metadata cache. Shared pool helps execute SQL
and PLSQL.
Efficient metadata caching required to support creation of objects and to be a

repository of many sharable components for efficient running of RDBMS engine
and PL/SQL engine
Contents of Shared Pool
SQL and PLSQL Objects
SQL Area
V$SQLAREA table contains all the data of this sql area and also provides a list of
statistics on shared SQL area and contains one row per SQL string. It provides
statistics on SQL statements that are in memory, parsed, and ready for execution.
PL/SQL MPCODE
The “compiled” form a PL/SQL is called PCODE, which stands for pseudo code, and
is interpreted at runtime by the PL/SQL engine. That is, it is not really compiled; it
is just pre-compiled. There is also a machine dependent form of pseudo code used
in some cases called MPCODE. (Source Steve Adams – 1999 – Questions and
Answers)
PL/SQL DIANA
Source: Steve Adams – 1999 Q & A
IDL stands for Interface Definition Language. It is an intermediate language in

which the structure of database tables and the logic of PL/SQL program units can
be consistently represented as attributed trees. Oracle uses the DIANA IDL,
which comes from compilers for the Ada programming language. DIANA stands for
Descriptive Intermediate Attributed Notation for Ada. Anyway, this is one of four
tables in the data dictionary used to store the DIANA for PL/SQL program units,
and the database objects that they reference.
To convert from a human-readable source language (such as PL/SQL) to a machine-

readable language (such as m-code) that is efficiently executed. In Oracle,
compiling converts PL/SQL into two internal forms, m-code and DIANA, to assist
with both efficient execution and dependency management.
sql area:PLSQL
sql area:KOKA related to Pseudo code cursor – No document defines or talks about
this piece of sql area.
Library cache:
It contains the objects, grants on those objects, dependencies, sequences and

synonyms. It also contains the complex metadata that helps to compile the shared
SQL and PLSQL
Please query v$librarycache and under namespace column all the following are
listed.
BODY
CLUSTER
INDEX
JAVA DATA
JAVA RESOURCE
JAVA SOURCE
OBJECT
PIPE
SQL AREA
TABLE/PROCEDURE
TRIGGER
Row cache:
Please query ‘parameter’ from v$rowcache and the following is listed.
dc_awr_control
dc_constraints
dc_database_links
dc_files
dc_free_extents
dc_global_oids
dc_hintsets
dc_histogram_data
dc_histogram_defs
dc_object_grants
dc_object_ids
dc_objects
dc_outlines
dc_partition_scns
dc_profiles
dc_qmc_cache_entries
dc_qmc_ldap_cache_entries
dc_rollback_segments
dc_segments
dc_sequences
dc_table_scns
dc_tablespace_quotas
dc_tablespaces
dc_used_extents
dc_usernames
dc_users
global database name
kqlsubheap_object
outstanding_alerts
qmtmrcin_cache_entries
qmtmrcip_cache_entries
qmtmrciq_cache_entries
qmtmrctn_cache_entries
qmtmrctp_cache_entries
qmtmrctq_cache_entries
rule_fast_operators
rule_info
rule_or_piece
Active Session History (ASH) buffers
There are new background processes introduced by Oracle in Oracle 10g in R1 and
R2. They are
The Memory Monitor Light (MMNL) process is a new process in 10g which works
with the Automatic Workload Repository new feature (AWR) to write out full
statistics buffers to disk as needed.
The memory monitor (MMON) process was introduced in 10g and is associated with
the Automatic Workload Repository new features used for automatic problem
detection and self-tuning. MMON writes out the required statistics for AWR on a
scheduled basis.
MMON background slave (m000) processes.
MMAN – SGA Background Process: The Automatic Shared Memory Management

feature uses a new background process named Memory Manager (MMAN). MMAN
serves as the SGA Memory Broker and coordinates the sizing of the memory
components. The SGA Memory Broker keeps track of the sizes of the components
and pending resize operations
ASH is a circular buffer and is an integral part of shared pool. Oracle Metalink Doc
ID Note: 243132.1
Size of ASH Circular Buffer = Max [Min [ #CPUs * 2 MB, 5% of Shared Pool Size,
30MB ], 1MB ]
An ACTIVE session means
01. Present inside a user call
02. Not a recursive session
03. Not waiting for the ‘IDLE’ wait-event
04. If it is a background process, not waiting for its usual timer-event
05. If it is a parallel slave, not waiting for the PX_IDLE wait event.
So will not see any info if a process is waitting for “SQL*Net message from client”.
Get that SQL with this SQL
select SESSION_ID,
NAME,
P1,
P2,
P3,
WAIT_TIME,
CURRENT_OBJ#,
CURRENT_FILE#,
CURRENT_BLOCK#
from v$active_session_history ash,
v$event_name enm
where ash.event#=enm.event#
and SESSION_ID=&SID and SAMPLE_TIME>=(sysdate-&minute/(24*60));
Oracle has provide a utility under $ORACLE_HOME/rdbms/demo (Location may

change), by which you can upload the ASH trace dump to a database table and do
the analysis.
And many more stuff is stored in the Shared Pool of every Oracle Instance.
Memory Allocation and Release
The SHARED_POOL has at the highest level has 2 kinds of structures. The first
structure is PERMANENT structure which is not alterable as has been spawned by
Oracle Instance Startup and its stay in the SHARED_POOL is not negotiable.
The second structure is re-creatable and hence is negotiable. The negotiating

mechanism is Least Recently Used (LRU) and thus the contents can be aged out and
re-loadable. But every re-load has its price to pay.
01. When new objects are referenced they need to be brought into memory and
they need memory allocation
02. So re-creatable objects are aged out and pushed out of memory
03. Objects are made up of chunks of memory and when they are created the
process checks for contiguous required space
04. The chunks of memory are in 1 and 4 K
05. If the available free memory is not sufficient to create a contiguous required
chunk, Oracle throws up error ORA-04031
SQL and PL/SQL objects and cursor usage and parsing
There are two kinds of cursors.
01. One is explicit cursor – created by PL/SQL explicitly in the declarative section
and then managed and closed in the executable/run section of the PL/SQL object.
For queries that return more than one row, one can explicitly declare a cursor to
process the rows individually.
02. PL/SQL implicitly declares a cursor for all SQL data manipulation statements,
including queries that return only one row.
03. All the SELECT statements issued are managed by the implicit cursors.
When a cursor is closed, the cursor information is moved into session’s closed
cursor cache in User Global Area (UGA). The Shared pool maintains the handles in
hashed chains. SESSION_CACHED_CURSORS is the parameter that controls the
number. The default value for this in Oracle 10.2.0.2.0 is 0 (zero) and in the later
versions, it was said that the same is set to 50.
How it works?
When a cursor is opened, the session process hashes pthe SQL statement and
performs a hash lookup in the closed cursor cache in the session memory (UGA) and
if found the same is moved to open cursors and then no parsing is required.
If the cursor is not found in the session, the hash value is used to search the hash
chains in the shared pool for the cursor handle. This search is registered as hard
parse.
If the cursor handle is found and the cursor has not aged out, the cursor is
executed. This is a soft parse.
If some part of the cursor has aged out of the shared pool
Or
If the cursor does not exist in the shared pool, then the cursor is reconstructed.
This is called hard parse. The cursor reconstruction requires a lookup of the
metadata for the dependent objects such as tables, indexes, extents and
sequences.
If the metadata for these objects does not already cached in the shared pool,
recursive SQL is generated to fetch the information from the data dictionary.
How the cache in the shared pool is protected from being over written?
The following listing is to NAME

identify the latches listed
from v$latchname
LATCH#
213 shared pool
214 library cache
215 library cache lock
216 library cache pin
217 library cache pin allocation
218 library cache lock allocation
219 library cache load lock
220 library cache hash chains
240 shared pool simulator
241 shared pool sim alloc
Oracle Recovery Manager satisfies the most pressing

demands of performance, manageable backup and recovery,
for all Oracle data formats.
A complete high availability and disaster recovery strategy requires

dependable data backup, restore, and recovery procedures. Oracle Recovery
Manager (RMAN), a command-line and Enterprise Manager-based tool, is the
Oracle-preferred method for efficiently backing up and recovering your Oracle
database. RMAN is designed to work intimately with the server, providing
block-level corruption detection during backup and restore. RMAN optimizes
performance and space consumption during backup with file multiplexing and
compression, and integrates with leading tape and storage media products via
the supplied Media Management Library (MML) API.
RMAN takes care of all underlying database procedures before and after backup
or restore, freeing dependency on OS and SQL*Plus scripts. It provides a common
interface for backup tasks across different host operating systems, and offers
features not available through user-managed methods, such as binary file
compression, parallelization of backup/recovery data streams, backup files
retention policy, and detailed history of all backups.
First introduced in the Oracle8 Database, RMAN has since been updated with
numerous enhancements in Oracle9i, including block media recovery, autobackup of
control files, and resumable backup/recovery in case of failure. With the latest
release of RMAN in Oracle Database 10g, a host of new features allow the DBA to:
· Better organize backups with flash recovery area
· Optimize incremental backup performance via block change tracking
· Reduce the number of full backups to perform with incrementally updated
backups
· Administer backup sets and image copies with Enterprise Manager
· Easily convert tablespaces across platforms
Overview of RMAN Functional Components
The RMAN environment consists of the utilities and databases that play a role in
backing up your data. At a minimum, the environment for RMAN must include the
following:
· The target database to be backed up
· The RMAN client, which interprets backup and recovery commands, directs
server sessions to execute those commands, and records your backup and recovery
activity in the target database control file.
Some environments will also use these optional components:

· A flash recovery area, a disk location in which the database can store and manage
files related to backup and recovery;
· Media management software, required for RMAN to interface with backup
devices such as tape drives;
· A recovery catalog database, a separate database schema used to record RMAN
activity against one or more target databases.
The following diagram illustrates these components:
Target Database
The target database is the database that you are backing up, restoring, or
recovering with RMAN.
RMAN Client
RMAN is a command-line-oriented database client, much like SQL*Plus, with its

own command syntax. From the RMAN client you can issue RMAN commands and
SQL statements to perform and report on backup and recovery operations.
RMAN can take interactive input or read input from plain text files (called
command files). RMAN then communicates with one or more server processes on
the target database server which actually perform the work. You can also access
RMAN through the Enterprise Manager; for details see Oracle Enterprise Manager
Administrator’s Guide.
The RMAN executable is typically installed in the same directory as the other
database executables. On Unix systems, for example, the RMAN executable is
located in $ORACLE_HOME/bin.
RMAN Repository
RMAN maintains metadata about the target database and its backup and recovery
operations in the RMAN repository. Among other things, RMAN stores information
about its own configuration settings, the target database schema, archived redo
logs, and all backup files on disk or tape. RMAN’s LIST, REPORT, and SHOW
commands display RMAN repository information.
RMAN repository data is always stored in the control file of the target database.
The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter controls how
long backup records are kept in the control file before those records are re-used
to hold information about more recent backups. The repository can also be kept in
a recovery catalog, a separate database that keeps historical data on backup
activities much longer than the control file and preserves backup information if
the control file is lost.
Flash Recovery Area
The Automatic Disk-Based Backup and Recovery feature simplifies managing disk
space and files related to backup and recovery, by managing all backup and
recovery related files in a flash recovery area. You set the flash recovery area
size and location, using the DB_RECOVERY_FILE_DEST and
DB_RECOVERY_FILE_DEST_SIZE initialization parameters. You also specify a
retention policy that dictates when backups may be discarded.
RMAN then manages your backup storage, deleting obsolete backups and backups
already copied to tape when space is needed, but keeping as many backups on disk
as space permits. This minimizes restores from tape during data recovery
operations to shorten restore and recovery times.
Recovery Catalog
In addition to RMAN repository records, the recovery catalog can also hold RMAN
stored scripts, sequences of RMAN commands for common backup tasks.
Centralized storage of scripts in the recovery catalog can be more convenient than
working with command files.
Except for stored scripts, all of RMAN’s features work equally well with or without
a recovery catalog. For more information on the recovery catalog see Oracle
Database Backup and Recovery Advanced User’s Guide.
Media Managers
To access sequential media devices like tape libraries, RMAN uses third-party
media management software. A media manager controls these devices during
backup and recovery, managing the loading, labeling and unloading of media, among
other functions. Oracle Corporation’s Backup Solutions Program (BSP) works with
vendors to help them produce media management software for their devices. For
enterprises that already use media management software in their enterprise, many
of those software products can be directly integrated with RMAN. Contact your
media management software vendor for details about whether they participate in
the BSP and have an RMAN-compatible media management layer.
RMAN Features & Benefits
1. Guaranteed, accurate backup and recovery

New features for RMAN 10g:
· The Flash Recovery Area, a single directory on disk or ASM disk group,
consolidates all recovery-related files, including control, data, archive log, RMAN
backup set files; everything needed by RMAN upon recovery can be retrieved from
this directory. With a rapidly dropping price, disk has become a more attractive
option for primary backup storage, in addition to faster read/write performance
than tape. In addition, Flash Recovery Area can automatically warn administrators
of disk capacity issues and obsolete outdated backup sets to reclaim space.
· With the automatic channel failover feature, when an error occurs during a disk
backup or restore, RMAN will attempt to complete as much of the job as possible,
rather than aborting the job. RMAN accomplishes this by utilizing multiple
channels, as specified by the CONFIGURE command. This offers a higher level of
resiliency when streaming problems arise and better utilization of system
resources, versus restarting jobs manually.
· During a restore, when RMAN finds corruption in a backup, or finds that a backup
cannot be accessed, RMAN will try to restore the file from a different backup.
RMAN will try to restore the desired file from all possible backups before
returning an error. This is done automatically whenever RMAN restores file(s)
during the RESTORE or RECOVER commands, relieving the need to search for valid
backups and performing a manual RESTORE or RECOVER.
These features are in addition to the Oracle9i RMAN resumeable backup/restore.
In case of a failed backup job or restore, restarting the job will automatically
pickup the operation from the last successfully backed up file.
2. Automatic block corruption detection and repair

Block Media Recovery allows RMAN to fix a corrupted block (detected on backup)
while the data file remains online, and non-affected data continues to be available
for selecting and updating. This increases data availability and reduces mean time
to recover by selectively restoring and recovering the damaged blocks. Minimal
I/O is needed because redo is only applied to damaged blocks.
3. Comprehensive reporting
Using special V$ views, users can retrieve information on all currently executing
and completed RMAN backup jobs, as well as details on all backed up files and
obsolete backup sets. This output can also be easily viewed in Enterprise Manager,
under Backup Set Management.
4. Performance-optimized, space-saving operations

RMAN takes advantage of intimate knowledge of Oracle block structures to
provide high backup and restore data streaming performance and efficient file
compression. By default, when creating backup sets, RMAN backs up only used
blocks and saves disk space by merging blocks into as few backup pieces as
necessary.
New features for RMAN 10g:

· Binary compression technique reduces backup space usage by 50-75%.
· Block change tracking for incremental backups allows the server to record and
read only the changed blocks, rather than performing full data file scans on every
operation, thus dramatically shortening completion time.
· With the new DURATION option for the RMAN BACKUP command, DBAs can
weigh backup performance against system service level requirements. By specifying
a duration, RMAN will automatically calculate the appropriate backup rate; in
addition, DBAs can optionally specify whether backups should minimize time or
system load.
· For recovery, completion time is reduced through the use of the incrementally
updated backup feature; this allows incremental backup information to be
continually rolled into data file image copies, thus eliminating the need to apply
incrementals on recovery.
5. Intelligent disk consumption monitoring

New for RMAN 10g, Flash Recovery Area allows administrators to setup
notifications on disk space usage and automate obsolescence of expired backup
sets, via RMAN client command-line or EM interfaces.
6. Simple and centralized management

Administrators can take advantage of the RMAN client command-line interface,
whose commands are written in an OS-independent scripting language, or by using
the Enterprise Manager Backup and Recovery Management console. With EM,
performing backup job scheduling and recovery operations is completely automated
via step-by-step wizards.
In addition, EM includes:
· Backup optimization settings
· Retention policy parameters
· Mean-time-to-recovery monitoring
· Archived log multiplexing
· New features for EM 10g:
o Backup set and image copies management
o Flash recovery area settings
o Catalog synchronization
o Catalog crosscheck validation
7. Fine-granular data operations

RMAN provides fine granular, on and offline backups at the database, tablespace,
archive log, datafile, control file, and block level. Also, both up-to-the-present and
point-in-time recoveries can be performed, based on a date or SCN number, by
rolling forward archived logs. RMAN 10g eliminates the need for a DBA to manually
create an auxiliary instance for tablespace point-in-time recovery (TSPITR).
RMAN takes care of instance creation on the same server as the target database,
and removal upon completion of the tablespace recovery.
8. Extensible to third party media managers

Through one standard Media Management Layer API (MML), third-party media
management vendors can leverage the functionality of RMAN to provide robust
backup solutions for Oracle databases.
COMMON USED DICTIONARY VIEWS-ORACLE DBA-

APPSDBA
Here I list the Dictionary Views that can one oracle dba & appsdba use most of the
times.
Data Dictionary
Which users are in the database password file:
V$PWFILE_USERS
Where values set in the init.ora file can be viewed – all parameters:
V$PARAMETER
Script used to create the objects that comprise the data dictionary:
catalog.sql
To grant a special role to users so they can look at DBA views:
SELECT_CATALOG_ROLE
Information about all database objects in the database:
DBA_OBJECTS
Information about all tables in the database:
DBA_TABLES
Information about all indexes in the database:
DBA_INDEXES
Information about all views (including dictionary views) in the database:
DBA_VIEWS
Information about all sequences in the database:
DBA_SEQUENCES
Information about all users in the database:
DBA_USERS
Information about all constraints in the database:

DBA_CONSTRAINTS
Information about all table columns that have constraints on them:
DBA_CONS_COLUMNS
Information about all columns that have indexes on them in the database:
DBA_IND_COLUMNS
Information about all columns in all the tables in the database:
DBA_TAB_COLUMNS
Information about all the roles in the database:
DBA_ROLES
Information about all object privileges in the database:
DBA_TAB_PRIVS
Information about all system privileges granted to all users in the database:
DBA_SYS_PRIVS
Displays all PL/SQL source code in the database:
DBA_SOURCE
Information about all triggers in the database:
DBA_TRIGGERS
Information about object privileges granted to roles
ROLE_TAB_PRIVS
Information about system privileges granted to roles
ROLE_SYS_PRIVS
Information about roles granted to roles
ROLE_ROLE_PRIVS
Information about all tablespaces in the database:
DBA_TABLESPACES
Information about all profiles in the database:
DBA_PROFILES
For all parameters?
V$PARAMETER
General information about the database mounted to your instance:
V$DATABASE
Most information about the performance of the database is kept here:
V$SYSSTAT
Most information about the performance for individual user sessions is stored
here:
V$SESSION , V$SESSTAT
Information about online redo logs (2)
V$LOG, V$LOGFILE
Information about datafiles
V$DATAFILE
Basic information about control files, and the two columns it has:
V$CONTROLFILE. STATUS / NAME

An object you can query to obtain a listing of all data dictionary objects (4)
CATALOG, CAT, DICTIONARY, DICT.
When the control file was created, Sequence Number, most recent SCN:
V$DATABASE
Information stored in different sections of the control file, Sequence Number:
V$CONTROLFILE_RECORD_SECTION
To see the names and locations of all control files in the db? (2)
V$PARAMETER. V$CONTROLFILE
Tablespace and Datafiles
Temporary Segments:
Name, tablespace location, and owner of temporary segments:
DBA_SEGMENTS
Size of temporary tablespaces, current number of extents allocated to sort
segments, and sort segment high-water mark information. Space usage allocation
for temporary segments:
V$SORT_SEGMENT
Types of sorts that are happening currently on the database
V$SORT_USAGE
To see the username corresponding with the session:
V$SESSION
Information about every datafile in the database associated with a temporary

tablespace:
DBA_TEMP_FILES
Similar to DBA_TEMP_FILES, this performance view gives Information about

every
datafile in the database associated with a temporary tablespace:
V$TEMPFILE
Storage Structures
A summary view, contains all types of segments and their storage parameters,
space utilization settings:
DBA_SEGMENTS
Tablespace quotas assigned to users:
DBA_TS_QUOTAS
Segment name, type, owner, total bytes of extent, name of tablespace storing the
extent:
DBA_EXTENTS
The location and amount of free space by tablespace name:
DBA_FREE_SPACE
The location of free space in the tablespace that has been coalesced:
DBA_FREE_SPACE_COALESCED
Information about datafiles for every tablespace
DBA_DATAFILES
Performance view for information for datafiles for every tablespace
V$DATAFILE
To see the total amount of space allocated to a table?
DBA_EXTENTS
Table creation timestamp, information about the object ID:
DBA_OBJECTS
High water mark, all storage settings for a table, and statistics collected as part
of the analyze (for row migration) operation on that table
DBA_TABLES
Information about every column in every table:
DBA_TAB_COLUMNS
To determine how many columns are marked unused for later removal?
DBA_UNUSED_COL_TABS
To find the number of deleted index entries ?
INDEX_STATS
To determine the columns on a table that have been indexed:
DBA_ID_COLUMNS
The dynamic view to show whether the index is being used in a meaningful way?
V$OBJECT_USAGE
To see whether a constraint exists on a particular column?
DBA_CONS_COLUMNS
To see the constraints associated with a particular table:
DBA_CONSTRAINTS
To find the username, ID number, (encrypted) password, default and temporary
tablespace information, user profile of a user, password expiry date:
DBA_USERS
To all objects, which objects belong to which users, how many objects a user has
created?
DBA_OBJECTS
Resource-usage parameters for a particular profile:
DBA_PROFILES
Identifies all resources in the database and their corresponding cost:
RESOURCE_COST
Identifies system resource limits for individual users:
USER_RESOURCE_LIMITS
Shows all system privileges:
DBA_SYS_PRIVS
Show all object privileges:
DBA_TAB_PRIVS
Shows all privileges in this session available to you as the current user:
SESSION_PRIVS
Views for audits currently taking place are created by this script:
cataudit.sql
a list of audit entries generated by the exists option of the audit command:
DBA_AUDIT_EXISTS
A list of audit entries generated for object audits:
DBA_AUDIT_OBJECT
A list of audit entries generated by session connects and disconnects:
DBA_AUDIT_SESSION
A list of audit entries generated by statement options of the audit command:
DBA_AUDIT_STATEMENT
A list of all entries in the AUD$ table collected by the audit command:
DBA_AUDIT_TRAIL
To determine the roles available in the database, the names of all the roles on the
database and if a password is required to use each role:
DBA_ROLES
Names of all users and the roles granted to them:
DBA_ROLE_PRIVS
All the roles and the roles that are granted to them:
ROLE_ROLE_PRIVS
Which system privileges have been granted to a role:
DBA_SYS_PRIVS
All the system privileges granted only to roles:

ROLE_SYS_PRIVS
All the object privileges granted only to roles:
ROLE_TAB_PRIVS
All the roles available in the current session:
SESSION_ROLES
Which object privilege has been granted to a role:
DBA_TAB_PRIVS
To display the value of the NLS_CHARACTERSET parameter:
NLS_DATABASE_PARAMETERS
« RAC FAQ
Cost Based Optimizer (CBO) and Database Statistics »
SQL Quick Reference
Posted by Narashim Reddy .R on September 10, 2009

1 Votes
SQL Syntax
Statement
AND / OR SELECT column_name(s) FROM table_name
WHERE condition AND|OR condition
ALTER ALTER TABLE table_name ADD column_name
TABLE datatype or ALTER TABLE table_name DROP
COLUMN column_name
AS (alias) SELECT column_name AS column_alias FROM

table_name or SELECT column_name FROM
table_name AS table_alias
BETWEEN SELECT column_name(s) FROM table_name

WHERE column_name BETWEEN value1 AND
value2
CREATE CREATE DATABASE database_name

DATABAS
E
CREATE CREATE TABLE table_name ( column_name1
TABLE data_type, column_name2 data_type,
column_name2 data_type, … )
CREATE CREATE INDEX index_name ON table_name

INDEX (column_name) or CREATE UNIQUE INDEX
index_name ON table_name (column_name)
CREATE CREATE VIEW view_name AS SELECT

VIEW column_name(s) FROM table_name WHERE
condition
DELETE DELETE FROM table_name WHERE
some_column=some_value or DELETE FROM
table_name (Note: Deletes the entire table!!)
DELETE * FROM table_name (Note: Deletes the
entire table!!)
DROP DROP DATABASE database_name

DATABAS
E
DROP DROP INDEX table_name.index_name (SQL
INDEX Server) DROP INDEX index_name ON
table_name (MS Access) DROP INDEX
index_name (DB2/Oracle) ALTER TABLE
table_name DROP INDEX index_name (MySQL)
DROP DROP TABLE table_name

TABLE
GROUP BY SELECT column_name,
aggregate_function(column_name) FROM
table_name WHERE column_name operator
value GROUP BY column_name
HAVING SELECT column_name,

aggregate_function(column_name) FROM
table_name WHERE column_name operator
value GROUP BY column_name HAVING
aggregate_function(column_name) operator
value
IN SELECT column_name(s) FROM table_name

WHERE column_name IN (value1,value2,..)
INSERT INSERT INTO table_name VALUES (value1,
INTO value2, value3,….) or INSERT INTO table_name
(column1, column2, column3,…) VALUES (value1,
value2, value3,….)
INNER SELECT column_name(s) FROM table_name1

JOIN INNER JOIN table_name2 ON
table_name1.column_name=table_name2.column
_name
LEFT SELECT column_name(s) FROM table_name1

JOIN LEFT JOIN table_name2 ON
_name
RIGHT SELECT column_name(s) FROM table_name1
JOIN RIGHT JOIN table_name2 ON
_name
FULL SELECT column_name(s) FROM table_name1

JOIN FULL JOIN table_name2 ON
_name
LIKE SELECT column_name(s) FROM table_name

WHERE column_name LIKE pattern
ORDER BY SELECT column_name(s) FROM table_name

ORDER BY column_name [ASC|DESC]
SELECT SELECT column_name(s) FROM table_name
SELECT * SELECT * FROM table_name

SELECT SELECT DISTINCT column_name(s) FROM
DISTINC table_name
T
SELECT SELECT * INTO new_table_name [IN

INTO externaldatabase] FROM old_table_name or
SELECT column_name(s) INTO new_table_name
[IN externaldatabase] FROM old_table_name
SELECT SELECT TOP number|percent column_name(s)

TOP FROM table_name
TRUNCAT TRUNCATE TABLE table_name

E TABLE
UNION SELECT column_name(s) FROM table_name1
UNION SELECT column_name(s) FROM
table_name2
UNION SELECT column_name(s) FROM table_name1
ALL UNION ALL SELECT column_name(s) FROM
table_name2
UPDATE UPDATE table_name SET column1=value,

column2=value,… WHERE
some_column=some_value
WHERE SELECT column_name(s) FROM table_name

WHERE column_name operator value
Installing Oracle Database 11g on Linux
Purpose
In this tutorial, you learn how to install Oracle Database 11g on Linux.
Topics
This tutorial covers the following topics:
Overview
Testing Your Installation
Summary
Note: Alternatively, you can place the cursor over an individual icon in the following steps to
load and viewonly the screenshot associated with that step. You can hide an individual
screenshot by clicking it.
Overview
Using the Oracle Universal Installer, you install the Oracle Database 11g software and create a
database.
To install the Oracle software, you must use the Oracle Universal installer.
1. For this installation, you need either the DVDs or a downloaded version of the DVDs. In this tutorial, you
install from the downloaded version. From the directory where the DVD files were unzipped, open a
terminal window and enter the following:
./runInstaller.sh
2. The product you want to install is Oracle Database 11g. Make sure the product is selected and click
Next.
2. You will perform a basic installation with a starter database. Enter orcl for the Global Database Name
and oracle for Database Password and Confirm Password. Then, click Next.
3. You need to specify your Inventory directory. The location should be set to
/u01/app/oracle/oraInventory. Accept the default Operating System group name, oinstall. Then, click
Next.
4. The installer now verifies that the system meets all the minimum requirements for installing and
configuring the chosen product. Please correct any reported errors before continuing. When the check
successfully completes, click Next.
5. Oracle Configuration Manager allows you to associate your configuration information with your Metalink
account. You can choose to enable it on this window. Then, click Next.
5.
Review the Summary window to verify what is to be installed. Then, click Install.
6.
The progress window appears.
7.
The Configuration Assistants window appears.
8.
Your database is now being created.
9.
When the database has been created, you can unlock the users you want to use. Click OK.
10.
You need to execute orainstRoot.sh and root.sh as the root user.
11. Open a terminal window and enter the following commands. Follow the prompts as instructed.
su -
<rootpassword>
cd /u01/app/oracle/oraInventory
./orainstRoot.sh
cd ../product/11.1.0/db_1
./root.sh
exit
exit
12.
Switch back to the Universal Installer and click OK.
13.
Click Exit. Click Yes to confirm exit.
Testing Your Installation
To test that your installation completed successfully, perform the following steps:
1. Open a browser and enter the following URL:https://<hostname>:1158/emwhere <hostname> should

be changed to your machine name, IP address, or localhost.
Because Enterprise Manager Database Control is a secure site, you need a certificate. Select the
Accept this certificate permanently option, and then click OK.
2.
Enter system as the User Name and oracle as the Password, and then click Login.
3.
The Database Control Home Page appears. Your installation was successful.
Summary
In this tutorial, you learned how to install and test the Oracle Database 11g installation on Linux.
Archived Redo Log Files Management

Posted by Narashim Reddy .R on August 16, 2009

Rate This
Checking Database Mode:
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/oracle/tarun4/archive
Oldest online log sequence 0
Current log sequence 1
Configuring Database to archive:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.
SQL> !
[oracle@reddy ~]$ cd $ORACLE_HOME/dbs
[oracle@reddy dbs]$ vi inittarun4.ora
——————
——————
*.log_archive_dest=’/home/oracle/tarun4/archive’
*.log_archive_format=’arch_%t_%r_%s.arc’
*.log_archive_start=true
——————-
——————-
:wq!
[oracle@reddy dbs]$ cd
[oracle@reddy ~]$ sqlplus sys as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 18 17:55:03 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup mount;

ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 348127232 bytes
Fixed Size 1219328 bytes
Variable Size 289408256 bytes
Database Buffers 50331648 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
Database log mode Archive Mode
Automatic archival Enabled
Next log sequence to archive 1
SQL> select archiver from v$instance;
ARCHIVE
——-
STARTED
Configuring Database to noarchive:
Database closed.
SQL> startup mount;

Database mounted.
SQL> alter database noarchivelog;
Database altered.
Database altered.
Database log mode No Archive Mode
Automatic archival Disabled
SQL> select archiver from v$instance;
ARCHIVE
——-
STOPPED
« Table Space Management
Archived Redo Log Files Management »
DataFile Management

Rate This
Steps to Make datafile off line and online:
SQL> select file#,ts#,status,bytes,name from v$datafile where file#=10;
FILE# TS# STATUS BYTES
———- ———- ——- ———-
NAME
——————————————————————————–
10 5 ONLINE 10485760
/home/oracle/tarun4/datafiles/users01.dbf
SQL> alter database datafile 10 offline;
Database altered.
———- ———- ——- ———-
NAME
——————————————————————————–
10 5 RECOVER 10485760
SQL> recover datafile 10;
Media recovery complete.

———- ———- ——- ———-
NAME
——————————————————————————–
10 5 OFFLINE 10485760
SQL> alter database datafile 10 online;
Database altered.
———- ———- ——- ———-
NAME
——————————————————————————–
10 5 ONLINE 10485760
 Steps for Resizing the Datafile:
SQL> select file_name from dba_data_files where tablespace_name=’USERS’;
FILE_NAME
/home/oracle/tarun4/datafiles/users.dbf
SQL> select tablespace_name,bytes/1024/1024 from dba_data_files where

tablespace_name=’USERS’;
TABLESPACE_NAME BYTES/1024/1024
—————————— —————
USERS 100
SQL> alter database datafile’/home/oracle/tarun4/datafiles/users.dbf’ resize

50m;
Database altered.

—————————— —————
USERS 50
 Steps for adding a Datafile to Tablespace:
FILE_NAME
————————————————————————————————————————————
————————————————
SQL> alter tablespace users add

datafile’/home/oracle/tarun4/datafiles/users1.dbf’ size 10m;
Tablespace altered.
FILE_NAME
————————————————————————————————————————————
————————————————
 Steps for Rename the Datafile:
FILE_NAME
————————————————————————————————————————————
————————————————
SQL> alter tablespace users offline;
Tablespace altered.
SQL> !
[oracle@reddy ~]$ cd tarun4/datafiles/
[oracle@reddy datafiles]$ mv users1.dbf users01.dbf
SQL> alter tablespace users rename

datafile’/home/oracle/tarun4/datafiles/users1.dbf’ to
‘/home/oracle/tarun4/datafiles/users01.dbf’;
Tablespace altered.
SQL> alter tablespace users online;
Tablespace altered.

FILE_NAME
————————————————————————————————————————————
————————————————
« RMAN
DataFile Management »
Table Space Management

Rate This
Syntax for Creating Tablespace:
SQL> create tablespace users datafile’/home/oracle/tarun4/datafiles/users.dbf’

size 50m;
Tablespace created.
SQL> create tablespace test2 datafile’/home/oracle/tarun4/datafiles/test02.dbf’

size 50m
2 extent management local
3 segment space management auto;
Tablespace created.
SQL> create tablespace test3 datafile’/home/oracle/tarun4/datafiles/test03.dbf’

size 50m
2 extent management local

3 uniform size 2m;
Tablespace created.
SQL> select initial_extent from dba_tablespaces where tablespace_name=’TEST2′;
INITIAL_EXTENT
————–
65536
SQL> select initial_extent from dba_tablespaces where tablespace_name=’TEST3′;
INITIAL_EXTENT
————–
2097152
Note:Default is extent management local
SQL> create table emp_test2(c1 number) tablespace test2;
Table created.
SQL> select segment_name,extent_id,bytes,blocks from dba_extents where

tablespace_name=’TEST2′;
SEGMENT_NAME
——————————————————————————–
EXTENT_ID BYTES BLOCKS
———- ———- ———-
EMP_TEST2
0 65536 16
SQL> create table emp_test3(c1 number) tablespace test3;
Table created.

SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST3
0 2097152 512
SQL> create table emp_test4(c1 number) storage(initial 1m next 1m) tablespace

test2;
Table created.

SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST2
0 65536 16
EMP_TEST4
0 65536 16
EMP_TEST4
1 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
2 65536 16
EMP_TEST4
3 65536 16
EMP_TEST4
4 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
5 65536 16
EMP_TEST4
6 65536 16
EMP_TEST4
7 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
8 65536 16
EMP_TEST4
9 65536 16
EMP_TEST4
10 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
11 65536 16
EMP_TEST4
12 65536 16
EMP_TEST4
13 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
14 65536 16
EMP_TEST4
15 65536 16
17 rows selected.

test3;
Table created.

SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST3
0 2097152 512
EMP_TEST5
0 2097152 512
test2;
Table created.

SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST2
0 65536 16
EMP_TEST4
0 65536 16
EMP_TEST4
1 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
2 65536 16
EMP_TEST4
3 65536 16
EMP_TEST4
4 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
5 65536 16
EMP_TEST4
6 65536 16
EMP_TEST4
7 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
8 65536 16
EMP_TEST4
9 65536 16
EMP_TEST4
10 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
11 65536 16
EMP_TEST4
12 65536 16
EMP_TEST4
13 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
14 65536 16
EMP_TEST4
15 65536 16
EMP_TEST6
0 1048576 256
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST6
1 1048576 256
19 rows selected.

test3;
Table created.

SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST3
0 2097152 512
EMP_TEST7
0 2097152 512
EMP_TEST5
0 2097152 512

test3;
Table created.

SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST3
0 2097152 512
EMP_TEST7
0 2097152 512
EMP_TEST5
0 2097152 512

test2;
Table created.

SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST2
0 65536 16
EMP_TEST4
0 65536 16
EMP_TEST4
1 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
2 65536 16
EMP_TEST4
3 65536 16
EMP_TEST4
4 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
5 65536 16
EMP_TEST4
6 65536 16
EMP_TEST4
7 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
8 65536 16
EMP_TEST4
9 65536 16
EMP_TEST4
10 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
11 65536 16
EMP_TEST4
12 65536 16
EMP_TEST4
13 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
14 65536 16
EMP_TEST4
15 65536 16
EMP_TEST8
0 1048576 256
SEGMENT_NAME
——————————————————————————–

———- ———- ———-
EMP_TEST8
1 1048576 256
EMP_TEST8
2 1048576 256
EMP_TEST6
0 1048576 256
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST6
1 1048576 256
22 rows selected.

test3;
Table created.

SEGMENT_NAME
——————————————————————————–

———- ———- ———-
EMP_TEST3
0 2097152 512
EMP_TEST7
0 2097152 512
EMP_TEST9
0 2097152 512
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST9
1 2097152 512
EMP_TEST5
0 2097152 512

test2;
Table created.

SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST2
0 65536 16
EMP_TEST4
0 65536 16
EMP_TEST4
1 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
2 65536 16
EMP_TEST4
3 65536 16
EMP_TEST4
4 65536 16
SEGMENT_NAME
——————————————————————————–

———- ———- ———-
EMP_TEST4
5 65536 16
EMP_TEST4
6 65536 16
EMP_TEST4
7 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
8 65536 16
EMP_TEST4
9 65536 16
EMP_TEST4
10 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-

EMP_TEST4
11 65536 16
EMP_TEST4
12 65536 16
EMP_TEST4
13 65536 16
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST4
14 65536 16
EMP_TEST4
15 65536 16
EMP_TEST1
0 1048576 256
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST1
1 1048576 256
EMP_TEST1
2 1048576 256
EMP_TEST1
3 1048576 256
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST8
0 1048576 256
EMP_TEST8
1 1048576 256
EMP_TEST8
2 1048576 256
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST6
0 1048576 256
EMP_TEST6
1 1048576 256
26 rows selected.

test3;
Table created.

SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST3
0 2097152 512
EMP_TEST10
0 2097152 512
EMP_TEST10
1 2097152 512
SEGMENT_NAME
——————————————————————————–
———- ———- ———-

EMP_TEST7
0 2097152 512
EMP_TEST9
0 2097152 512
EMP_TEST9
1 2097152 512
SEGMENT_NAME
——————————————————————————–
———- ———- ———-
EMP_TEST5
0 2097152 512
7 rows selected.
Syntax for Creating Temporary Tablespace:
SQL> create temporary tablespace temp1

tempfile’/home/oracle/tarun4/datafiles/temp02.dbf’ size 50m;
Tablespace created.
Syntax for Creating Undo Tablespace:
SQL> create undo tablespace undo07

datafile’/home/oracle/tarun4/datafiles/undo07.dbf’ size 50m autoextend on;
Tablespace created.
Important Views in Tablespace Management:

SQL> select tablespace_name,initial_extent,status,contents from
dba_tablespaces;
TABLESPACE_NAME INITIAL_EXTENT STATUS CONTENTS
SYSTEM 65536 ONLINE PERMANENT
UNDOTBS1 65536 ONLINE UNDO
SYSAUX 65536 ONLINE PERMANENT
TEMP 1048576 ONLINE TEMPORARY
RMAN_TBS 65536 ONLINE PERMANENT
USERS 65536 ONLINE PERMANENT
TEST2 65536 ONLINE PERMANENT
TEST3 2097152 ONLINE PERMANENT
TEMP1 1048576 ONLINE TEMPORARY
UNDO07 65536 ONLINE UNDO
10 rows selected.
SQL> select file_name,file_id,tablespace_name,bytes,blocks,status,online_status

from dba_data_files;
FILE_NAME
————————————————————————————————————————————
——————
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ONLINE_
/home/oracle/tarun4/datafiles/rman01.dbf
4 RMAN_TBS 20971520 5120 AVAILABLE ONLINE

/home/oracle/tarun4/datafiles/sysaux01.dbf
3 SYSAUX 524288000 128000 AVAILABLE ONLINE
/home/oracle/tarun4/datafiles/undotbs1.dbf
2 UNDOTBS1 524288000 128000 AVAILABLE ONLINE
FILE_NAME
————————————————————————————————————————————
——————
———- —————————— ———- ———- ——— ——-
/home/oracle/tarun4/datafiles/system01.dbf
1 SYSTEM 524288000 128000 AVAILABLE SYSTEM
5 USERS 52428800 12800 AVAILABLE ONLINE
/home/oracle/tarun4/datafiles/test02.dbf
6 TEST2 52428800 12800 AVAILABLE ONLINE
FILE_NAME
————————————————————————————————————————————
——————
———- —————————— ———- ———- ——— ——-
7 TEST3 52428800 12800 AVAILABLE ONLINE

/home/oracle/tarun4/datafiles/undo07.dbf
8 UNDO07 52428800 12800 AVAILABLE ONLINE
8 rows selected.
SQL> select * from database_properties where

PROPERTY_NAME=’DEFAULT_TEMP_TABLESPACE’;
PROPERTY_NAME
——————————
PROPERTY_VALUE
————————————————————————————————————————————
——————
DESCRIPTION
————————————————————————————————————————————
——————
DEFAULT_TEMP_TABLESPACE
TEMP
Name of default temporary tablespace
SQL> select * from database_properties where

PROPERTY_NAME=’DEFAULT_PERMANENT_TABLESPACE’;
PROPERTY_NAME
——————————
PROPERTY_VALUE
————————————————————————————————————————————
——————
DESCRIPTION
————————————————————————————————————————————
——————
DEFAULT_PERMANENT_TABLESPACE
SYSTEM
Name of default permanent tablespace
SQL> select file_name,file_id,tablespace_name,bytes,status from dba_temp_files;
FILE_NAME
————————————————————————————————————————————
————————————————
FILE_ID TABLESPACE_NAME BYTES STATUS
———- —————————— ———- ———
/home/oracle/tarun4/datafiles/temp02.dbf
1 TEMP1 52428800 AVAILABLE
2 TEMP 52428800 AVAILABLE
SQL> select * from dba_free_space where tablespace_name=’SYSTEM’;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
—————————— ———- ———- ———- ———- ————
SYSTEM 1 56977 524288 128 1
SYSTEM 1 57361 289341440 70640 1

SQL> select tablespace_name,sum(bytes) from dba_free_space group by
tablespace_name;
TABLESPACE_NAME SUM(BYTES)
—————————— ———-
UNDOTBS1 487129088
SYSAUX 307298304
RMAN_TBS 12255232
reddy 104726528
TEST3 35651584
USERS 62652416
UNDO07 51052544
SYSTEM 289865728
TEST2 41877504
9 rows selected.
SQL> select tablespace_name,sum(bytes) from dba_free_space where

tablespace_name=’USERS’ group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)
—————————— ———-
USERS 62652416
SQL> select owner,table_name,tablespace_name,status,initial_extent from

dba_tables where tablespace_name=’TEST2′;
OWNER TABLE_NAME TABLESPACE_NAME STATUS INITIAL_EXTENT

—————————— —————————— —————————— ——– ————–
SYS EMP_TEST2 TEST2 VALID 65536
SQL> select owner,segment_name,partition_name,segment_type, tablespace_name,

bytes, initial_extent from dba_segments where tablespace_name=’TEST2′;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
—————————— ——————————————————————————— ——————

———— ——————
TABLESPACE_NAME BYTES INITIAL_EXTENT
—————————— ———- ————–
SYS EMP_TEST2 TABLE
TEST2 65536 65536
SYS EMP_TEST4 TABLE
TEST2 1048576 1048576
SYS EMP_TEST6 TABLE
TEST2 2097152 2097152
—————————— —————————— ——————
TABLESPACE_NAME BYTES INITIAL_EXTENT

—————————— ———- ————–
SYS EMP_TEST8 TABLE
TEST2 3145728 3145728
SYS EMP_TEST1 TABLE
TEST2 4194304 4194304
SQL> select file#,ts#,status,enabled,bytes,block_size,name from v$tempfile;
FILE# TS# STATUS ENABLED BYTES BLOCK_SIZE
———- ———- ——- ———- ———- ———-
NAME
————————————————————————————————————————————
————————————————
1 8 ONLINE READ WRITE 52428800 4096
SQL> select file#,ts#,status,enabled,bytes,block_size,name from v$datafile;
———- ———- ——- ———- ———- ———-
NAME
————————————————————————————————————————————
————————————————
1 0 SYSTEM READ WRITE 524288000 4096

———- ———- ——- ———- ———- ———-
NAME
————————————————————————————————————————————
————————————————
———- ———- ——- ———- ———- ———-
NAME
————————————————————————————————————————————
————————————————

8 rows selected.
SQL> create bigfile tablespace reddy

datafile’/home/oracle/tarun4/datafiles/bigfile.dbf’ size 100m;
Tablespace created.
SQL> select ts#,name,included_in_database_backup,bigfile from v$tablespace;
TS# NAME INC BIG
———- —————————— — —
0 SYSTEM YES NO
1 UNDOTBS1 YES NO
2 SYSAUX YES NO
4 RMAN_TBS YES NO
3 TEMP NO NO
5 USERS YES NO
6 TEST2 YES NO
7 TEST3 YES NO
8 TEMP1 NO NO
9 UNDO07 YES NO
11 reddy YES YES

11 rows selected.
SQL> alter tablespace reddy add

datafile’/home/oracle/tarun4/datafiles/bigfile1.dbf’ size 100m;alter tablespace
reddy add datafile’/home/oracle/tarun4/datafiles/bigfile1.dbf’ size 100m
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
SQL> select OWNER, SEGMENT_NAME, PARTITION_NAME,SEGMENT_TYPE

from dba_extents where tablespace_name=’USERS’;
—————————— ——————————————————————————— ——————

———— ——————
SYS TEST TABLE
Note:When you have connected as user use
user_segments
user_tables
user_extents
Steps for Resizing the Datafile:
FILE_NAME

—————————— —————
USERS 100
SQL> alter database datafile’/home/oracle/tarun4/datafiles/users.dbf’ resize

50m;
Database altered.

—————————— —————
USERS 50
Steps for adding a Datafile to Tablespace:
FILE_NAME
————————————————————————————————————————————
————————————————
SQL> alter tablespace users add

datafile’/home/oracle/tarun4/datafiles/users1.dbf’ size 10m;
Tablespace altered.
FILE_NAME
————————————————————————————————————————————
————————————————
Steps for Rename the Datafile:
FILE_NAME
————————————————————————————————————————————
————————————————
Tablespace altered.
SQL> !
[oracle@reddy datafiles]$ mv users1.dbf users01.dbf
SQL> alter tablespace users rename

datafile’/home/oracle/tarun4/datafiles/users1.dbf’ to
‘/home/oracle/tarun4/datafiles/users01.dbf’;
Tablespace altered.
Tablespace altered.

FILE_NAME
————————————————————————————————————————————
————————————————
Steps to Move a Table from one Tablespace to another Tablespace:
SQL> select table_name from dba_tables where tablespace_name=’USERS’;
TABLE_NAME
——————————
TEST
SQL> select table_name from dba_tables where tablespace_name=’TEST2′;
TABLE_NAME
——————————
EMP_TEST2
EMP_TEST4
EMP_TEST1
EMP_TEST8
EMP_TEST6
SQL> alter table emp_test2 move tablespace users;
Table altered.
SQL> select table_name from dba_tables where tablespace_name=’USERS’;

TABLE_NAME
——————————
TEST
EMP_TEST2
SQL> select table_name from dba_tables where tablespace_name=’TEST2′;
TABLE_NAME
——————————
EMP_TEST4
EMP_TEST1
EMP_TEST8
EMP_TEST6
SQL> alter tablespace users coalesce;
Tablespace altered.
Tablespace altered.
SQL> insert into test values(&c1);
Enter value for c1: 1
old 1: insert into test values(&c1)
new 1: insert into test values(1)
insert into test values(1)
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: ‘/home/oracle/tarun4/datafiles/users.dbf’
Tablespace altered.
1 row created.
SQL> alter tablespace users read only;
Tablespace altered.
insert into test values(2)
ERROR at line 1:
ORA-00372: file 5 cannot be modified at this time
ORA-01110: data file 5: ‘/home/oracle/tarun4/datafiles/users.dbf’

SQL> alter tablespace users read write;
Tablespace altered.
1 row created.
Steps to Make datafile off line and online:
SQL> select file_name,file_id,status, online_status from dba_data_files where

FILE_NAME
————————————————————————————————————————————
————————————————
FILE_ID STATUS ONLINE_
———- ——— ——-
5 AVAILABLE ONLINE
10 AVAILABLE ONLINE
SQL> alter database datafile 5 offline;
Database altered.
FILE_NAME
————————————————————————————————————————————
————————————————
———- ——— ——-
5 AVAILABLE RECOVER
10 AVAILABLE ONLINE
SQL> recover datafile 5;
Media recovery complete.

FILE_NAME
————————————————————————————————————————————
————————————————
———- ——— ——-
5 AVAILABLE OFFLINE
10 AVAILABLE ONLINE
SQL> alter database datafile 5 online;
Database altered.
Syntax for Tablespace Rename:
SQL> alter tablespace test3 rename to test1;
Tablespace altered.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
TEMP
RMAN_TBS
USERS
TEST2
TEST1
TEMP1
UNDO07
reddy
11 rows selected.
Follow the Rules when Renaming a Tablespace:
SQL> alter tablespace users nologging;
Tablespace altered.
Nologging DDL statement won’t be records in the redo logfiles but DML statement
records in the redo logfiles.
SQL> alter tablespace users logging;
Tablespace altered.
Logging records the changes to the object into redo logfiles.
Dropping Tablespace:
TABLESPACE_NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
TEMP
RMAN_TBS
USERS
TEST2
TEST1
TEMP1
UNDO07
reddy
11 rows selected.
SQL> drop tablespace test1;
Removes the entry of tablespace from controlfile, physical files are present at OS
Level.
Tablespace dropped.
SQL> drop tablespace test1 including contents and datafiles;
Tablespace dropped.
Removes tablespace name from controlfile and also physical files.
SQL> drop tablespace test2 including contents and datafiles cascade constraints;
Tablespace dropped.
TABLESPACE_NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
TEMP
RMAN_TBS
USERS
TEMP1
UNDO07
reddy
9 rows selected.
SQL> select name from v$datafile;
NAME
————————————————————————————————————————————
————————————————
/home/oracle/tarun4/datafiles/bigfile.dbf
8 rows selected.
SQL> !
[oracle@reddy datafiles]$ ls
bigfile.dbf redo03.log rman01.dbf temp01.dbf undotbs1.dbf

redo01.log redo04.log sysaux01.dbf temp02.dbf users01.dbf
redo02.log redo07.log system01.dbf undo07.dbf users.dbf
How To Shrink Undo Tablespace

If you want to shrink the Undo Tablespace ,you will have to shrink the
Datafile to free disk space and this is the best and fast solution.
1-you will need to create another Tablespace with a smaller Datafile size
SQL> create undo tablespace UNDO_TBS1

datafile ‘d:\oracle\oradata\orcl\undorbs1.dbf’ size 100m;
2-Let the new tablespace to be the default Undo Tablespace
SQL> alter system set undo_tablespace=UNDO_TBS1;
3-Check that there’s no active Undo Segments in your Database using this
query
SQL> SELECT a.name,b.status

FROM v$rollname a, v$rollstat b
WHERE a.name IN ( SELECT segment_name
FROM dba_segments
WHERE tablespace_name = ‘UNDOTBS0′)
AND a.usn = b.usn;
if the query returns no rows then there’s no active Undo segments
4- Drop the old Tablespace
SQL> drop tablespace UNDO_TBS0 including contents.
REDO LOGFILE MANAGEMENT
REDO LOGFILE MANAGEMENT
Views in Redologs:
v$log->Status,Groups
v$logfile->Filename and Paths
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#

FIRST_TIM
———- ———- ———- ———- ———- — —————- ————- ———
1 1 1 10485760 1 YES INACTIVE 642254 02-DEC-08
2 1 2 10485760 1 NO CURRENT 664106 03-DEC-08
SQL> select * from v$logfile;
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
———————————————————————————————————————
———————————
IS_
2 ONLINE
/home/oracle/tarun4/datafiles/redo02.log
NO
1 ONLINE
NO
Adding Online Redo Logfile Groups:
SQL> alter database add logfile group 3′/home/oracle/tarun4/datafiles/redo03.log’ size 10m;

Database altered.
If we want two members in the group:
SQL> alter database add logfile group 4(‘/home/oracle/tarun4/datafiles/redo04.log’,

‘/home/oracle/tarun4/datafiles/redo05.log’) size 10m;
Database altered.
Adding Online Redo Logfile Members:
SQL> alter database add logfile member ‘/home/oracle/tarun4/datafiles/redo06.log’ to group 1,
2 ‘/home/oracle/tarun4/datafiles/redo07.log’ to group 2;
Database altered.
Dropping Online Redo Logfile Groups:
SQL> alter database drop logfile group 4;
Database altered.
Note:
*You can’t drop a group if it is current.
*You can’t drop a group if it is current.
*On dropping group it just updates the controlfile, the physical files are not removed and it as to
be removed using OS commands.
Dropping Online Redo Logfile Members:
SQL> alter database drop logfile member ‘/home/oracle/tarun4/datafiles/redo06.log’;
Database altered.
Note:
*You can’t drop member if group as only 1 member.
*You must force a logfile switch before you can drop the member.
*On dropping group it just updates the controlfile, the physical files are not removed and it as to
be removed using OS commands.
Clearing Online Redo Logfiles:
Alter database clear logfile command can used to reinitialize an online redo logfile.
SQL> alter database clear logfile group 1;
Database altered.
Note:
*You can’t clear a group if it is current. Current Redo Logfile needed for crash recovery of
instance tarun4 (thread 1).
Use the unarchived Keyword to avoid archiving the corrupted online redo logfile.
SQL> alter database clear unarchived logfile group 1;
Database altered.
Note:
*You can’t clear a group if it is current. Current Redo Logfile needed for crash recovery of
instance tarun4 (thread 1).
Steps for Relocating or Renaming Online redo log members:
Database closed.
SQL> !
[oracle@reddy datafiles]$ ls
redo01.log redo07.log sysaux01.dbf temp01.dbf users01.dbf
redo02.log rman01.dbf system01.dbf undotbs1.dbf
[oracle@reddy datafiles]$ mv redo07.log /home/oracle/

[oracle@reddy datafiles]$ cd
[oracle@reddy ~]$ exit
exit
SQL> startup mount;
Database mounted.
SQL> alter database rename file ‘/home/oracle/tarun4/datafiles/redo07.log’ to

‘/home/oracle/redo07.log’;
Database altered.
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—
2 ONLINE
NO
1 ONLINE
NO
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
2 ONLINE
/home/oracle/redo07.log
NO
v$log view:
The following items are the most common values for the status column:
.Unused:Indicates that the online redo logfile group has never been written to.
This is the state of an online redo logfile that was just added.
.Current:Indicates the current online redo logfile group.This implies that the online redo logfile
group is active.
.Active:Indicates that the online redo logfile group is active but is not the current online redo
logfile group.It is needed for crash/instance recovery.
.Inactive:Indicates that the online redo logfile group is no longer needed for instance recovery.
v$logfile view:
The value of the status column could be one of the following:
.Invalid:Indicates that the file is inaccessible.
.Stale:Indicates that contents of the file is incomplete.
.Deleted:Indicates that the file is no longer used.
.Blank:Indicates that the file is in use.
Oracle architecture

1 Votes
ORACLE ARCHITECTURE
The Structural and Functional representation of Oracle DataBase Server.
 Oracle Server:
 Is a database management system.

 It provides an open, comprehensive integrated approach to information management.
 It consists of Oracle Instance and Oracle Database.
 It reliably manage a Large Amount of Data in a MultiUser Environment so that many
Users can Concurrently Access the Same Data.
 Oracle Database:
 The general purpose of a database is to Store and Retrieve Related Information.

 Data:Collection of Related Information.
 Base:Is a Place where Data is Stored.
 It consists of Physical Structure And Logical Structure.
 The Physical Structure of the Database is the Set of Operating System Files(DataBase
Files).
 The DataBase Files provide the Actual Physical Storage for DataBase Information.
 Important View in Database:

SQL> select DBID,NAME,LOG_MODE,CHECKPOINT_CHANGE#,
CONTROLFILE_CREATED,CONTROLFILE_TIME,OPEN_RESETLOGS,VERSION_TIME,
OPEN_MODE
2 from v$database;
DBID NAME LOG_MODE CHECKPOINT_CHANGE# CONTROLFI CONTROLFI

OPEN_RESETL VERSION_T OPEN_MODE
———- ——— ———— —————— ——— ——— ———– ——— ———-
2356734140 RUBRICS ARCHIVELOG 1400417 19-NOV-08 14-DEC-08 NOT ALLOWED

19-NOV-08 READ WRITE
SQL> select PROTECTION_MODE,DATABASE_ROLE,CURRENT_SCN,
FLASHBACK_ON,DB_UNIQUE_NAME,PLATFORM_ID ,
FS_FAILOVER_STATUS from v$database;
PROTECTION_MODE DATABASE_ROLE CURRENT_SCN FLASHBACK_ON

DB_UNIQUE_NAME PLATFORM_ID FS_FAILOVER_STATUS
——————– —————- ———– —————— —————————— ———–

———————
MAXIMUM PERFORMANCE PRIMARY 1401592
NO RUBRICS 10 DISABLED
 Oracle Instance:
 Is a means to Access an Oracle Database.

 An Instance is Identified by using methods specific to each Operating System.
 The Instance can open and use only one Database at a Time.
 An Oracle Instance is the Combination of the Background Process and Memory
Structure.
 Every time an Instance is Started, a System Global Area(SGA) is allocated and Oracle
Background Processes are Started.
 Important View in Instance:
SQL> select INSTANCE_NUMBER, INSTANCE_NAME,HOST_NAME,VERSION,STATUS

from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STATUS

————— —————- —————————————————————-
—————– ————
1 tarun4 rubrics 10.2.0.1.0 OPEN
SQL> select THREAD#, ARCHIVER, DATABASE_STATUS,ACTIVE_STATE from

v$instance;
THREAD# ARCHIVE DATABASE_STATUS ACTIVE_ST
———- ——- —————– ———
1 STARTED ACTIVE NORMAL
 User Process:
 A Database User who needs to request information from the Database must first
make a connection with the Oracle Server.
 The connection is requested using a Database interface tool, such as SQL*PLUS, and
beginning the User Process.
 Server Process:
 Once a User has established a connection, a Server Process is Started to Handle the User
Process request.
 A Server Process can be either Dedicated Server Process or Shared Server Process.
 In a Dedicated Server environment, the Server Process Handles the request of a Single
User Process.
 In a Shared Server environment, the Server Process Handles the request of Several User
Process.
Physical Structure
Database Files: NonDataBase Files:
 DataFiles Password File

 ControlFiles Parameter File
 RedologFiles Archived RedoLogFiles
 DataFiles:
 Containing the Actual Data in the Database.

 The data is Stored in the Oracle Database in the Datafiles in the Oracle Propriatory
Format.
 The User can Access the Data Through the Application(SQLPLUS or Any other front
end).
 Min One Datafile is necessary to create the Database i.e. System Datafile(Oracle 9i).
 Min Two Datafiles is necessary to create the Database i.e. System Datafile and Sysaux
Datafile ( Oracle 10g).
 Extension for datafile is (.dbf).
 The Entire Data can be Stored in the System Datafile but for ease of Admin the DBA has
to create separate Datafiles(Non System Datafile) to Store the User Data.
 System Data has the information about the Data called Data Dictionary or Meta Data.It
consists of privileges,objects,location ,etc.
 System Datafile is gets created when we create Database.
 Datafile classification based on type of Data stored Permanent and Temporary.
 The Data Stored in a Permanent Datafile is available across the Startup and Shutdown.
 The Data Stored in a Temporary Datafile is not available across the Startup and
Shutdown.
 The Temporary files are used whenever Sorting, Joining Operations performed by the
user on Database objects.
 The Space Management with in the Datafile can be of two types Dictionary Managed and
Locally Managed
 In a Dictionary Managed for Allocation and Deallocation of a space for a Object the
System Datafile is Update.This generates Redo and also decreases Performance.
 In a Locally Managed the Allocation and Deallocation of a space for a Object is
Maintained with in the same Datafile Header in the Form of Bit Maps or Bit Map
blocks.For example allocation 0 or 1.
 Important Views in Datafiles.
———- ———- ——- ———-
NAME
——————————————————————————–
10 5 ONLINE 10485760
SQL> select file_name,file_id,tablespace_name,bytes,status,online_status from dba_data_files

where tablespace_name=’RUBRICS’;
FILE_NAME
——————————————————————————–
FILE_ID TABLESPACE_NAME BYTES STATUS ONLINE_
———- —————————— ———- ——— ——-
/home/oracle/tarun4/datafiles/bigfile.dbf
9 RUBRICS 104857600 AVAILABLE ONLINE
 ControlFiles:
 Containing information necessary to Maintain and Verify Database integrity.

 It is a Binary file it contains the Structure and Contents of the Database i.e. the Physical
Location of the file along with the path.
 It also contains the information about the Time and Date the Database Creation,
Database Name,Backup and Recovery information,Online,Offline,Redo only etc.
 Min 1 Controlfile is needed for the Database to Startup.

 Max allowed is 8.
 A controlfile has two control sections Reusable and NonReusable.
 The Reusable section is used by RMAN(Recovery Manager) for Backup and Recovery
information.
 Important View in ControlFiles:
SQL> select NAME,IS_RECOVERY_DEST_FILE,BLOCK_SIZE ,FILE_SIZE_BLKS from

v$controlfile;
/home/oracle/tarun4/control/c1.ctl
NO 16384 440
/home/oracle/tarun4/control/c2.ctl
NO 16384 440
 RedologFiles:
 Containing a Record of changes made to the database(In Memory) to enable recovery of

the data in case of failures.
 Min of two groups are necessary for the database functioning.
 Max allowed is 255.
 Each group will have 1 to 5 Members.
 Min needed is 1 Member.
 While Recording the changes in redolog groups all the Members in a group will have the
same contents.
 Oracle Server Brights to all the Members of the groups simultaneously.
 Once the group is filled it’s switches to another group this switching is called log switch.
 For every log switch Oracle Server generates a number called log sequence no.
 After the completion of all the available group and control is transferred to the first group
and starts overwriting.
 Important Views in Redologfiles:
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#

FIRST_TIM
———- ———- ———- ———- ———- — —————- ————- ———
1 1 131 10485760 1 NO CURRENT 1400416 14-DEC-08
2 1 130 10485760 2 YES INACTIVE 1377952 14-DEC-08
3 1 128 20971520 1 YES INACTIVE 1353960 13-DEC-08
4 1 129 20971520 1 YES INACTIVE 1354009 13-DEC-
SQL> select GROUP#,STATUS, TYPE,MEMBER,
IS_RECOVERY_DEST_FILE from v$logfile;
2 STALE ONLINE /home/oracle/tarun4/datafiles/redo02.log
NO
1 ONLINE /home/oracle/tarun4/datafiles/redo01.log
NO
3 ONLINE /home/oracle/tarun4/datafiles/redo03.log
NO

NO
NO
 Password File:
 Authenticates users privileged to Startup and Shutdown an Oracle instance.

 To make a Normal users in a database as a Super user Password file is used.
 It is an Optional file and resides on the Oracle Server machine in the default path
$ORACLE_HOME/dbs.
 To create passwd file Oracle provides a utility $orapwd that should be envoked from the
OS command prompt.
 Important View in Password File.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
—————————— —– —–
SYS TRUE TRUE
A1 TRUE FALSE
 Creating Passwordfile:
[oracle@rubrics ~]$ orapwd file=$ORACLE_HOME/dbs/orapwtarun4(sid_name)
password=—- entries=5
 Parameter File:
 Defines the Characteristics of an Oracle instance.

 In a database instance functionality is set through parameter file, it is used to set the
parameter and the value to the parameters this is the first file that is read by Oracle Server
while starting the database.
 From Oracle 9i parameter file of two types pfile(text file) ans spfile(server parameter file)
(binary file).
 If both the parameter files are present than spfile is given higher priority than pfile.
 Once the instance is Started the parameter file not needed until the next Start of the
instance.
 The advantage of spfile is that the changes made to parameter when the database is up it
can update for the current instance.
 The default path for parameter file is $ORACLE_HOME/dbs where Oracle Home is the
home directory for Oracle Software installed directory.
 Important View in Parameter File:
SQL> show parameter pfile;
NAME TYPE
———————————— ———–
VALUE
——————————
spfile string
/home/oracle/oracle/product/10
.2.0/db_1/dbs/spfiletarun4.ora
SQL> select NAME,TYPE,VALUE,HASH from v$parameter ;
NAME TYPE VALUE HASH
————————————————————————————-
————————————————————————————-
max_dump_file_size 2 /home/oracle/tarun4/udump 2332088509
————————————————————————————–
————————————————————————————–
[oracle@rubrics ~]$ vi /home/oracle/oracle/product/10.2.0/db_1/dbs/inittarun4.ora
*.background_dump_dest=’/home/oracle/tarun4/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/home/oracle/tarun4/control/c1.ctl’
*.db_block_size=4096
*.db_cache_size=62914560
*.db_name=’RUBRICS’
*.log_archive_dest=’/home/oracle/tarun4/archive’
*.remote_login_passwordfile=’exclusive’
*.shared_pool_size=232m
*.undo_management=’auto’
*.user_dump_dest=’/home/oracle/tarun4/udump’
 Archived RedoLogFiles:
 Are offline copies of the online RedologFiles that may be necessary to recover from
media failures.
 It is an Optional file which will be created only if the database is in Archivelog mode.
 By default it will be No Archivelog mode.
 If Archivelog feature is enabled then every log switch in the redologfile generates a
Archivelog file and the generated Archivelog file will be placed in Archive destination.
 Each switch in redolog will have one archive file.The switch may be either
Automatic-After filling the group.
Manual-Applied by DBA SQL>alter system switch logfile;
 What ever the contents present in the redologfile at the time of logswitch will be size of
Archivelog file.
 Important Views in Archived RedoLogFiles:
SQL> select NAME,THREAD#,SEQUENCE#,RESETLOGS_CHANGE#,
BLOCKS,BLOCK_SIZE, STANDBY_DEST,ARCHIVED,APPLIED, STATUS

,IS_RECOVERY_DEST_FILE from v$archived_log ;
/home/oracle/tarun4/archive/arch_1_672425039_01.arc
1 15 642254 2788 512 NO YES NO A NO
/home/oracle/tarun4/archive/arch_1_672425039_02.arc
1 16 642254 24071 512 NO YES NO A NO

2 rows selected.
LOGICAL STRUCTURE
 TABLESPACE
 SEGMENT
 EXTENT
 BLOCK
 LOGICAL STRUCTURE:
 Dictates how the Physical space of a database is used.
 A Logical Structure hierarchy exists as follows:
 A tablespace contains one or more segments.
 An extent is made up of Logical blocks.
 A block is the smallest unit for read and write operations.
 The Oracle Server enables fine-grained control of disk space use through tablespace and
Logical Storage Structures, including segments,extents, and data blocks.
 TABLESPACE:
 An Oracle database can be logically grouped into smaller logical areas of space known as
tablespaces.
 A tablespace can belong to only one database at a time.

 The database is split into one or more tablespaces.
 Whenever the database is created one tablespace created by Oracle itself that is called as
system tablespace.
 The System tablespace contains the object related information which stores the meta data,
objects that are related to the database.
 The user data also stores in the system tablespace by default.
 It is highly recommended to create a separate non-system tablespace to store userdata.
 To create a tablespace at least one data file is necessary.
 Each tablespace in an Oracle database consists of one or more operating system files
called datafiles.
 A datafile can belong to only one tablespace.
Tablespace may logically contain a single type of segment or different type of segments.
 Space management in tablespaces can be classified into following Dictionary

management and Locally management tablespaces.
 Dictionary management tablespaces:Whenever object is created or dropped
i.e.,whenever space is allocated or de-allocated, Oracle uses data dictionary information
and updated appropriate table in dictionary managed tablespaces.This uses system
tablespace to store data.
 Locally management tablespaces:Space allocation and de-allocation is maintained in
the form of bit-map blocks present in the tablespace itself in Locally managed
tablespaces.This type of space allocation or de-allocation is faster than that of dictionary
managed.
 Tablespace can be classified into following Permanent,Temporary and Undo.
 Permanent Tablespace:It is used to store Permanent data. Permanent tablespace can be
created as either dictionary managed or locally managed.
 Temporary Tablespace:It is used to store only temporary object and data, usually used
for sorting operations. It can be created as either dictionary managed or locally managed.
 Undo Tablespace:It is used to store undo or rollback data.Whenever a change is made to
database, this undo data is used to rollback the transaction. Only one undo tablespace can
be active in database.
 Default Temporary Tablespace:When a user is created a default temporary tablespace
is assigned as temporary tablespace.This is used to store sorted data .
 SEGMENT:
 A Segment is the space allocated for a specific Logical storage structure within a
Tablespace.
 A tablespace may consists of one or more Segments.
A Segment cannot span tablespaces; however, a Segment can span multiple data
files that belong to the same tablespace.
 Each Segment is made up of one or more extents.
 EXTENT:
 Space is allocated to a Segment by extents.

 An extent is a set of Contiguous Oracle Blocks.
 An extent cannot span data files and therefore,it must exists in one datafile.
 One or more extents make up a segment
-When Segment is Created, it consists of at least one extent.
-As the Segment grows, extents are added to the Segment.
-The DBA can manually add extents to a Segment
 BLOCK:
 The Oracle Server manages the storage space in the data files in units called Oracle
blocks or data blocks.
 Oracle data block are the smallest units of storage that the Oracle Server can allocate,read
or write.
 The Standard data block size for an Oracle database is specified by the
DB_BLOCK_SIZE initialization parameter when the database is created.
 The Maximum data block size is dependent on the operating system.
 Oracle blocks are made up of multiple block sizes.
 Oracle supports 5 block sizes 2k,4k,8k,16k,32k.
 The data block size should be a multiple of the operating system block size to avoid
unnecessary I/O.
Tablespace associated with one or more datafiles
 Logical Structure Physical Files
 Tablespace(Space or Area Created Data files
for the Storage of data)
 Segments( Table,Index etc Storage Unit)

 Extents
 Blocks OS Blocks
CONTROL FILE MANAGEMENT
Posted by Narashim Reddy .R on July 29, 2009

Rate This
Note:
Before proceeding take Full Backup of Control and Datafiles in All the cases.
Multiplexing(P file):
[oracle@reddy ~]$ export ORACLE_SID=rub
Enter password:
SQL> startup;
Database mounted.
Database opened.
SQL> select name from v$controlfile;
NAME
——————————————————————————
/home/oracle/rub/control/c1.ctl

Database closed.
SQL> !
[oracle@reddy ~]$ cd rub/control/
[oracle@reddy control]$ ls
c1.ctl
[oracle@reddy control]$ cp c1.ctl c2.ctl
c1.ctl c2.ctl
[oracle@reddy control]$ cd $ORACLE_HOME/dbs
[oracle@reddy dbs]$ vi initrub.ora
*.background_dump_dest=’/home/oracle/rub/bdump’
*.control_files=’/home/oracle/rub/control/c1.ctl’,'/home/oracle/rub/control/c2.ctl’
*.db_name=’rub7′
*.log_archive_dest=’/home/oracle/rub/archive’
*.user_dump_dest=’/home/oracle/rub/udump’
timed_statistics=true
:wq!
[oracle@reddy dbs]$ cd
Enter password:
File created.
SQL> startup;
Database mounted.
Database opened.
NAME
——————————————————————————-

Multiplexing(SP file):
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
NAME
——————————————————————————–
SQL> alter system set

control_files=’/home/oracle/rub/control/c1.ctl’,'/home/oracle/rub/control/c2.ctl’
scope=spfile;
System altered.
Database closed.
SQL> create pfile from spfile;
File created.
SQL> !
c1.ctl
[oracle@reddy control]$ cp c1.ctl c2.ctl
c1.ctl c2.ctl
[oracle@reddy control]$ cd
S QL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 18 11:07:48 2008
Enter password:
SQL> startup;

Database mounted.
Database opened.
NAME
——————————————————————————–
Recreating Control file
When existing Control file is lost or corrupted:
Enter password:
SQL> startup;

Database mounted.
Database opened.
SQL> alter database backup controlfile to trace;(Creates trace of controlfile in

user_dump_dest)
Database altered.
Database closed.
SQL> !
[oracle@reddy ~]$ cd rub/udump/
[oracle@reddy udump]$ ls -ltr (look for the last trace of controlfile in

user_dump_dest)
-rw-r—– 1 oracle dba 781 Nov 18 11:05 rub_ora_5139.trc
[oracle@reddy udump]$ cp rub_ora_5196.trc reddy.sql
Remove the unwanted comments
[oracle@reddy udump]$ vi reddy.sql
To remove the unwanted lines use the commands (esc :set nu, (n.no) dd)
1 STARTUP NOMOUNT
2 CREATE CONTROLFILE REUSE DATABASE “RUB7″ NORESETLOGS
ARCHIVELOG
3 MAXLOGFILES 4
4 MAXLOGMEMBERS 4
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 292
8 LOGFILE
9 GROUP 1 ‘/home/oracle/rub/datafiles/redo01.log’ SIZE 10M,
10 GROUP 2 ‘/home/oracle/rub/datafiles/redo02.log’ SIZE 10M
11 — STANDBY LOGFILE
12 DATAFILE
13 ‘/home/oracle/rub/datafiles/system01.dbf’,
14 ‘/home/oracle/rub/datafiles/undotbs1.dbf’,
15 ‘/home/oracle/rub/datafiles/sysaux01.dbf’,
16 ‘/home/oracle/rub/datafiles/users01.dbf’
17 CHARACTER SET US7ASCII
18 ;
[oracle@reddy udump]$ cd /home/oracle/rub/control/
[oracle@reddy control]$ rm -rf *.ctl (Remove the earlier controlfile)
[oracle@reddy control]$
[oracle@reddy control]$ sqlplus sys as sysdba
Enter password:
SQL> @/home/oracle/rub/udump/reddy.sql
Control file created.
Database altered.
When you want to change MAX Parameter of database:
Enter password:
Connected to:

SQL> alter database backup controlfile to trace;
Database altered.
Database closed.
SQL> !
[oracle@reddy udump]$ ls -ltr
-rw-r—– 1 oracle dba 541 Nov 18 11:55 reddy.sql
[oracle@reddy udump]$ cp rub_ora_5902.trc reddy1.sql
[oracle@reddy udump]$ vi reddy1.sql
1 STARTUP NOMOUNT
2 CREATE CONTROLFILE REUSE DATABASE “RUB7″ NORESETLOGS
ARCHIVELOG
3 MAXLOGFILES 8
4 MAXLOGMEMBERS 4
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 292
8 LOGFILE
12 DATAFILE
18 ;
[oracle@reddy udump]$ cd
c1.ctl c2.ctl
[oracle@reddy control]$ rm -rf *.ctl
Enter password:
SQL> @/home/oracle/rub/udump/reddy1.sql
Database altered.
When you want to change database name:
Enter password:
SQL> startup;

Database mounted.
Database opened.
SQL> select name from v$database;
NAME
———
RUB7
SQL> alter database backup controlfile to trace;
Database altered.
Database closed.
SQL> !
[oracle@reddy udump]$ ls -ltr
-rw-r—– 1 oracle dba 541 Nov 18 11:55 reddy.sql
-rw-r—– 1 oracle dba 541 Nov 18 12:45 reddy1.sql

[oracle@reddy udump]$ cp rub_ora_6272.trc reddy2.sql
[oracle@reddy udump]$ vi reddy2.sql
1 STARTUP NOMOUNT
2 CREATE CONTROLFILE set DATABASE “RUB” RESETLOGS
ARCHIVELOG
3 MAXLOGFILES 8
4 MAXLOGMEMBERS 4
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 292
8 LOGFILE
12 DATAFILE
18;
: x
[oracle@reddy udump]$ cd $ORACLE_HOME/dbs
*.control_files=’/home/oracle/rub/control/c1.ctl’
*.db_name=’rub’
timed_statistics=true
[oracle@reddy dbs]$ cd /home/oracle/rub/control/
c1.ctl c2.ctl
[oracle@reddy control]$ rm -rf *.ctl
Enter password:
File created.
SQL> @/home/oracle/rub/udump/reddy2.sql
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$database;
NAME
———
RUB
PASSWORD FILE MANAGEMENT

Rate This
To make a normal user as super user password file is required.User needs SYSDBA privilege.
*Set the Parameters in the Parameter file.
[oracle@reddy~]$ vi /home/oracle/oracle/product/10.2.0/db_1/dbs/inittarun4.ora
remote_login_passwordfile=’exclusive’ ->Single instance db
‘shared‘ ->Multiple instance db
’none’ ->Default
remote_os_roles=true
:wq!
Creating Passwordfile:
[oracle@reddy~]$ orapwd file=$ORACLE_HOME/dbs/orapwtarun4 password=—-

entries=5
[oracle@reddy~]$ sqlplus sys as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Dec 9 15:35:17 2008
Enter password:

File created.
SQL> startup;
Database mounted.
Database opened.
SQL> create user a1 identified by a1;
User created.
SQL> grant sysdba to a1;
Grant succeeded.
SQL> connect a1 as sysdba;
Enter password:
Connected.
SQL> show user;
USER is “SYS”
View to know the password file users:
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
—————————— —– —–
SYS TRUE TRUE
A1 TRUE FALSE
Installation steps

Rate This
Installation steps:
1.Pre-Installation-(Tune(adjust) the OS Parameter for Oracle to function properly)
2.Installation-(Installs Oracle Software)
3.Post-Installation-(Sort the OS user environment to access Oracle Software)
Pre-Installation:
(a)Set the Kernel Parameter
[oracle@reddy ~]# vi /etc/sysctl.conf
– - – - – - – -
– - – - – - – -
kernel.shmmax=536870912
kernel.semmsl=250
kernel.semopm=100
kernel.fs.file-max=44278
:wq!
(b)Create a Group
[oracle@reddy ~]$ groupadd dba
(c)Create user to install Oracle Software
[oracle@reddy ~]$ useradd -g dba -md /home/oracle/ oracle
[oracle@reddy ~]$ passwd oracle (Set the password for oracle)
(d)Check the available free space in disk
[oracle@reddy ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda10 13G 7.1G 5.2G 58% /
/dev/sda7 99M 12M 83M 12% /boot
none 248M 0 248M 0% /dev/shm
/dev/sda8 13G 5.9G 6.1G 50% /home
(e)To make the kernel parameter effective
[oracle@reddy ~]$ init 6 (Reboot the system)
(f)Unzip the Oracle 10g zipfile
[root@eddy Desktop]# unzip 10201_database_linux32.zip
(g) Give full permission for unzip file
[root@reddy Desktop]# chmod -R 777 database/
[root@reddy Desktop]# mv database/ /tmp
Installation:
Login in as Oracle user
[oracle@reddy ~]$ cd /tmp/database/
[oracle@reddy database]$ ls
doc install response runInstaller stage welcome.html
[oracle@reddy database]$ ./runInstaller
Post-Installation:
[oracle@reddy ~]$ vi .bash_profile
# .bash_profile
Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH
:wq!
To make this Profile effective
[oracle@reddy ~]$ . .bash_profile (To Run the bash_profile)
(or)
Logout and Login as a oracle User

[oracle@reddy ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 – Production on Wed Nov 5 12:04:55 2008
Enter user-name: sys as sysdba
Enter password:
SQL>
LISTENER CONFIGURATION STEPS

Rate This
LISTENER CONFIGURATION STEPS:
SQL> select * from global_name;
GLOBAL_NAME
RUB7
[oracle@reddy ~]$ cd
/home/oracle/oracle/product/10.2.0/db_1/network/admin/
[oracle@reddy admin]$ ls
listener.ora tnsnames.ora
[oracle@reddy admin]$ netmgr
+ -ORACLE NET CONFIGURATION

-LOCAL
-PROFILE
-SERVICE NAMING
-LISTENERS(Select)
LISTENER NAME:Listener
Select Add Address:
Host:(Local IP address)
Port:1521
Select Database Services and Select Add database
:rub7
Oracle Home directory:/home/oracle/oracle/product/10.2.0/db_1
SID:rub
File
save in the path

/home/oracle/oracle/product/10.2.0/db_1/network/admin
exit
[oracle@reddy admin]$ vi listener.ora
# listener.ora Network Configuration File:

/home/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rub7)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = rub)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.9)(PORT = 1521))
)
:wq!
[oracle@reddy admin]$ cd
[oracle@reddy ~]$ lsnrctl
LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 06-NOV-2008

12:08:57
Welcome to LSNRCTL, type “help” for information.
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:
start stop status
services version reload
save_config trace spawn
change_password quit exit

set* show*
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=192.168.1.9)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
LSNRCTL> start
Starting /home/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr: please

wait…
TNSLSNR for Linux: Version 10.2.0.1.0 – Production
System parameter file is

Log messages written to

/home/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)

(HOST=192.168.1.9)(PORT=1521)))

(HOST=192.168.1.9)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 – Production
Start Date 06-NOV-2008 12:11:02
Uptime 0 days 0 hr. 0 min. 3 sec

Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File

Listener Log File

/home/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.9)
(PORT=1521)))
Services Summary…
Service “rub7″ has 1 instance(s).
Instance “rub”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL> stop
(HOST=192.168.1.9)(PORT=1521)))
The command completed successfully
LSNRCTL> exit
[oracle@reddy ~]$
DATABASE CREATION

Rate This
STEPS:
1.[oracle@reddy ~]$ vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH
:wq!
[oracle@reddy ~]$ . .bash_profile (To Run the bash_profile)
2.[oracle@reddy ~]$ export ORACLE_SID=rub (Exporting The SID Name)
3.[oracle@reddy ~]$ cd $ORACLE_HOME/dbs
*.control_files=’/home/oracle/rub/control/c1.ctl’
*.db_name=’rub7′
:wq!
4.[oracle@reddy ~]$ mkdir rub
[oracle@reddy rub]$ mkdir udump bdump datafiles control archive
[oracle@reddy ~]$ cd rub/
[oracle@reddy rub]$ touch dbc.sql
[oracle@reddy rub]$ ls
archive bdump control datafiles dbc.sql udump
[oracle@reddy rub]$ vi dbc.sql
create database rub
user sys identified by rub
user system identified by rub
logfile group 1(‘/home/oracle/rub/datafiles/redo01.log’)size 10m,

group 2(‘/home/oracle/rub/datafiles/redo02.log’)size 10m
maxlogfiles 4
maxlogmembers 4
maxloghistory 10
maxdatafiles 100
maxinstances 1
character set US7ASCII
national character set AL16UTF16
datafile ‘/home/oracle/rub/datafiles/system01.dbf’
size 500m reuse extent management local
sysaux datafile ‘/home/oracle/rub/datafiles/sysaux01.dbf’
size 500m reuse
default temporary tablespace temp
tempfile ‘/home/oracle/rub/datafiles/temp01.dbf’
size 200m reuse
undo tablespace undotbs1
datafile ‘/home/oracle/rub/datafiles/undotbs1.dbf’
size 500m reuse autoextend on maxsize unlimited ;
:wq!
[oracle@reddy rub]$ cd

Enter password:
Connected to:
File created.
SQL> startup nomount;
SQL> @/home/oracle/rub/dbc.sql
Database created
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
DATABASE CREATION »
Cloning an Oracle Database

1 Votes
Table of Contents
1. Purpose of the document
2. Document Usage
3. Overview of the Procedure
3.1 Introduction
3.2 Environments
3.3 Assumption
3.4 Cloning Process
3.4.1 Steps to be followed in Primary database:
3.4.2 Steps to be followed in the Secondary database server:
5. Conclusion
1. This document illustrates a process to clone an Oracle database using hot backup. The
following sections are covered in this document
Overview
Environments
Assumptions
Cloning process
2. Document Usage
This document can be useful for any Oracle DBA for cloning an Oracle database. Generally
cloning databases with the help of source database hot backup is a standard process to effectively
and efficiently create a replica of the source database. The process illustrated in this document
can be used by any DBA who wants to create a replica of an Oracle database with the help of hot
backup and the associated archive logs.
3. Overview of the Procedure

3.1 Introduction
Cloning database is one of the routine DBA activities. Any DBA who administers various
environments and is supporting the application development team for database activities has to
periodically synchronize the acceptance/testing/development with the Production data.
This frequency of synchronization depends upon the business and application development
teams requirements. Generally this process can be done with the below mentioned procedures
1)EXPORT/IMPORT utilities
2)DATA PUMP
3)Cold Backup Restoration
4)Hot Backup Recovery
Each of the above mentioned process has got its own pros and cons. To state a few vital reasons
why Hot backup recovery process is chosen widely
a)You can recover to the most recent time period.

b)Doesn’t require outage of the source database
c)Requires less time compared to the other process
d)Best method for large databases
3.2 Environments
1. Primary or the Source database system

2. Secondary or the Target database system
3.3 Assumption
Primary database operated in ARCHIVELOG mode.

Secondary database to be created by cloning in a different server
Primary and Secondary environment Operating system are same.
3.4 Cloning Process

3.4.1 Steps to be followed in Primary database:
1)Perform HOTBACKUP of all the data files
2)LSN number
Login as sys as sysdba
Sql> Alter system archive log current;

Sql> Archive log list
Note down the Current Log Sequence
3)Control file trace
In Sql> prompt issue the statement
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
You can find the trace file in the udump directory.
3.4.2 Steps to be followed in the Secondary database server:
1)If the secondary database is to be created in a different server

FTP the following to a temporary location in the secondary database server.
a)hot backup of all the primary database data files
b)all the archive log files generated in the primary database after you start hot backup
c)control file trace of primary database
d)pfile of the primary database
2)Edit the pfile as it would be for secondary database server locations
a)Control_files- new locations

b)instance_name – new name
c)background_dump_dest – new location
d)core_dump_dest- new location
e)user_dump_dest – new location
f)log_archive_dest- new location
Save the pfile as init_.ora in
$ORACLE_HOME/dbs directory if UNIX

$ORACLE_HOME\database if WINDOWS
Where SID is the instance name given above in the edited pfile
3) In the control file trace Use the “SET #2. RESTLOGS case” CREATE CONTROLFILE
section (Since we don’t use the primary database redolog files, we use this option). In that
section Copy from “CREATE CONTROLFILE” to “CHARACTER SET ” to another file.
Save the file as cr_control.sql
Edit the following section in cr_control.sql
a)Use SET instead of REUSE
Old: CREATE CONTROLFILE REUSE DATABASE <”PRIMARY_DBNAME”

>RESETLOGS
NEW: CREATE CONTROLFILE SET DATABASE <”SECONDARY_DBNAME”>

RESETLOGS
b)Edit the LOGFILE paths as per the secondary database environment

c)Edit the DATAFILE paths as per the secondary database environment
d)Save the cr_control.sql file
4) Move the ftp ed Hot backup DATAFILE from temporary location of specific location as
specified in the control file cr_control.sql
5) Move the archived logs from temporary location to the location specified in log_archive_dest
in pfile
6) If Windows,
Create an oracle service using ORADIM utility
ORADIM -NEW -SID sid | -SRVC service [-INTPWD password] [-MAXUSERS number] [-
STARTMODE a|m] [-PFILE file] [-TIMEOUT secs]
In the CMD prompt
SET ORACLE_SID=
Sqlplus “/ as sysdba”
You will get a Connected to an idle instance message.
7) If UNIX,
In the $ prompt give
EXPORT ORACLE_SID=
Sqlplus “/ as sysdba”
You will get a Connected to an idle instance message.
8)In the SQL> Prompt give
STARTUP NOMOUNT
9) Run the cr_control.sql file in the SQL> prompt
If Windows
@/cr_control.sql
If Unix
@\cr_control.sql if in Unix
You should get a message like Control File created
9) Recover the database using backup control file. In the SQL> prompt give
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Note: this can also be done with UNTIL TIME or UNTIL SCN provided you know the time or
the SCN until which you want to perform the recovery
10)The recovery process will keep prompting for archive logs. Give return for each prompt of
archive log.
Since the required archive logs from Primary database are restored in archive_log_dest of
secondary, it will get applied for every return you give in the prompt.
Once the recovery process prompts for the archive log with the LSN number noted in the
Primary database,
in step 2) of Steps to be done in Primary database section
Give CANCEL in the recovery sql prompt
11)Now you must get a message like
Media recovery cancelled.
12) Now in the SQL prompt give.
ALTER DATABASE OPEN RESETLOGS;
You should get a message like.
Database opened.
12)Bounce the database
SHUTDOWN IMMEDIATE;
STARTUP;
13)Check for errors in bdump.
14)Check log switch.
15)Check redo log archival
16)Compare the users, objects, size with the Primary database
17)Take a complete cold back up and maintain that as generation 0 backup.
You have cloned the database.

5. Conclusion
Hot backup recovery is one of the best methods to clone database because there is no outage or
down time required in the primary database and data is replicated in secondary database to the
most recent time period.
TNS CONFIGURATION STEPS

Rate This
N[oracle@reddy ~]$ cd $ORACLE_HOME
[oracle@reddy db_1]$ cd network/
[oracle@reddy network]$ cd admin/
[oracle@reddy admin]$ ls
listener.ora sqlnet.ora tnsnames.ora
[oracle@reddy admin]$ netmgr
+ -ORACLE NET CONFIGURATION
-LOCAL
-PROFILE
-SERVICE NAMING(Select)
-LISTENERS
Net service name (service_name)
next
TCP/IP Internet Protocol(Select)
next
Host Name (Remote IP Address)
Port Number 1521

next
Service Name (Global Database Name of remote machine)
next
finish
File
save in the path /home/oracle/oracle/product/10.2.0/db_1/network/admin
exit
[oracle@reddy admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File:

/home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RUBRICS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.9)(PORT =

1521))
)
(CONNECT_DATA =
(SERVICE_NAME = rub7)
)
:wq!
Check the data link from Local system to Remote system:
[oracle@reddy ~]$ sqlplus sys@rubrics(Net service name) as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Nov 6 14:28:50 2008
Enter password:
SQL>

Ramana Ora

Uploaded by

Copyright:

Available Formats

Ramana Ora

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Ramana Ora

Uploaded by

Copyright:

Available Formats

Basic Unix

 man – view manual pages for Unix commands

 id – print user identity

Unix file operations

 ls - list files and directories

Unix directory management commands

Unix system status commands

 uptime – find out how long the system has been up

Backup Strategies and commands form Metalink

It is a very important aspect of any database which should be planned carefully as

[1] When database is running in archive-log mode.

In init.ora, search for parameter control_files to find the name of control

In init.ora, search for parameter log_archive_dest to find the location of archived

Frequency: This backup has to be taken weekly.

In init.ora, search for parameter log_archive_dest to find the location of archived

Frequency: This backup has to be taken daily.

[1] Ideally Complete database export should be taken daily.

[2] Take incremental export daily except on weekends.

On weekends, cumulative database export should be taken. When cumulative

[3] Take important user level export daily.

[2] When database is running in no archive-log mode.

[1] Ideally Complete database export should be taken daily.

[2] Take incremental export daily except on weekends.

On weekends, cumulative database export should be taken. When cumulative

[3] Take important user level export daily.

* Commands to be used in copying file(s) to backup device :

[a] cpio -ocBv < [name of file] > [/dev/rmt0.1|/dev/rmt0]

[b] tar -cvf [name of file] > [/dev/rmt0.1|/dev/rmt0]

Complete file system backup :

[a] find / -name -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0]

[b] To copy all files of unix to backup device

Use backup utility to copy the necessary files.

Commands to be used in restoring file(s) from backup device :

1. LOSS OF NON-ESSENTIAL DATAFILE WHEN DATABASE IS DOWN

TIME TAKEN IN RECOVERY

* NB : For temporary tablespace skip this step.

TIME TAKEN IN RECOVERY

Shutdown the database.(shutdown).

TIME TAKEN IN RECOVERY

Shutdown the database.(shutdown).

TIME TAKEN IN RECOVERY

Shutdown the database.(shutdown).

TIME TAKEN IN RECOVERY

Shutdown the database.(shutdown).

SOLUTION when Problem has occured on (a) on monday morning

Shutdown the database.(shutdown).

Shutdown the database.(shutdown).

7. LOSS OF DATAFILE WHEN THE DATABASE IS IN NOARCHIVELOG MODE

TIME TAKEN IN RECOVERY

Shutdown the database.(shutdown).

Shutdown the database.(shutdown).

8. LOSS OF SYSTEM DATAFILE WHEN THE DATABASE IS IN ARCHIVE

TIME TAKEN IN RECOVERY

9. LOSS OF NON SYSTEM DATAFILE WITHOUT ROLLBACK SEGMENTS

TIME TAKEN IN RECOVERY

SOLUTION -1 (DATABASE RECOVERY)

Shutdown the database.(shutdown).

SOLUTION -2 (DATAFILE RECOVERY)*