What is difference between oracle SID and
Oracle service name?
Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID.
What are the steps to install oracle on Linux system? List two kernel parameter that effect oracle installation?
Initially set up disks and kernel parameters, then create oracle user and DBA group, and finally run installer to start the installation process. The SHMMAX & SHMMNI two kernel parameter required to set before installation process.
What are bind variables?
With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.
What is the difference between data block/extent/segment?
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
What is the difference between PGA and UGA?
When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).
What is SGA? Define structure of shared pool component of SGA?
The system global area is a group of shared memory area that is dedicated to oracle instance. All oracle process uses the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. You can control the SGA memory by setting the parameter db_cache_size, shared_pool_size and log_buffer.
Shared pool portion contain three major area:
Library cache (parse SQL statement, cursor information and execution plan),
data dictionary cache (contain cache, user account information, privilege user information, segments and extent information,
data buffer cache for parallel execution message and control structure.
What is the difference between SMON and PMON processes?
SMON (System Monitor) performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default.
PMON (Process Monitor) failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default.
What is a system change number (SCN)?SCN is a value that is incremented whenever a dirty read occurs.
SCN is incremented whenever a deadlock occurs.
SCN is a value that keeps track of explicit locks.
SCN is a value that is incremented whenever database changes are made.
What is the main purpose of ‘CHECKPOINT’ in oracle database? How do you automatically force the oracle to perform a checkpoint?
A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and enable faster database Recovery.
The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.
LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hour
LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.
What happens when we fire SQL statement in Oracle?
First it will check the syntax and semantics in library cache, after that it will create execution plan.
If already data is in buffer cache it will directly return to the client.
If not it will fetch the data from datafiles and write to the database buffer cache after that it will send server and finally server send to the client.
What is the use of large pool, which case you need to set the large pool?
You need to set large pool if you are using: MTS (Multi thread server) and RMAN Backups. Large pool prevents RMAN & MTS from competing with other sub system for the same memory. RMAN uses the large pool for backup & restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled, then Oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool.
What does database do during the mounting process?
While mounting the database oracle reads the data from controlfile which is used for verifying physical database files during sanity check. Background processes are started before mounting the database only.
What are logfile states?
“CURRENT” state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current.
If a redo group containing redo’s of a dirty buffer that redo group is said to be ‘ACTIVE’ state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).
And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten.
One more state ‘UNUSED’ initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.
What is log switch?
The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch. Sometimes you can force the log switch.
ALTER SYSTEM SWITCH LOGFILE;
How to check Oracle database version?
SQL> Select * from v$version;
Explain Oracle Architecture?
Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID.
What are the steps to install oracle on Linux system? List two kernel parameter that effect oracle installation?
Initially set up disks and kernel parameters, then create oracle user and DBA group, and finally run installer to start the installation process. The SHMMAX & SHMMNI two kernel parameter required to set before installation process.
What are bind variables?
With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.
What is the difference between data block/extent/segment?
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
What is the difference between PGA and UGA?
When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).
What is SGA? Define structure of shared pool component of SGA?
The system global area is a group of shared memory area that is dedicated to oracle instance. All oracle process uses the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. You can control the SGA memory by setting the parameter db_cache_size, shared_pool_size and log_buffer.
Shared pool portion contain three major area:
Library cache (parse SQL statement, cursor information and execution plan),
data dictionary cache (contain cache, user account information, privilege user information, segments and extent information,
data buffer cache for parallel execution message and control structure.
What is the difference between SMON and PMON processes?
SMON (System Monitor) performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default.
PMON (Process Monitor) failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default.
What is a system change number (SCN)?SCN is a value that is incremented whenever a dirty read occurs.
SCN is incremented whenever a deadlock occurs.
SCN is a value that keeps track of explicit locks.
SCN is a value that is incremented whenever database changes are made.
What is the main purpose of ‘CHECKPOINT’ in oracle database? How do you automatically force the oracle to perform a checkpoint?
A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and enable faster database Recovery.
The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.
LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hour
LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.
What happens when we fire SQL statement in Oracle?
First it will check the syntax and semantics in library cache, after that it will create execution plan.
If already data is in buffer cache it will directly return to the client.
If not it will fetch the data from datafiles and write to the database buffer cache after that it will send server and finally server send to the client.
What is the use of large pool, which case you need to set the large pool?
You need to set large pool if you are using: MTS (Multi thread server) and RMAN Backups. Large pool prevents RMAN & MTS from competing with other sub system for the same memory. RMAN uses the large pool for backup & restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled, then Oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool.
What does database do during the mounting process?
While mounting the database oracle reads the data from controlfile which is used for verifying physical database files during sanity check. Background processes are started before mounting the database only.
What are logfile states?
“CURRENT” state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current.
If a redo group containing redo’s of a dirty buffer that redo group is said to be ‘ACTIVE’ state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).
And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten.
One more state ‘UNUSED’ initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.
What is log switch?
The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch. Sometimes you can force the log switch.
ALTER SYSTEM SWITCH LOGFILE;
How to check Oracle database version?
SQL> Select * from v$version;
Explain Oracle Architecture?
Oracle Instance:
a means to access an
Oracle database,always opens one and only one database and consists of memory
structures and background process.
Oracle server:
a DBMS that provides
an open, comprehensive, integrated approach to information management,Consists
of an Instance and a database.
Oracle database:
a collection of data
that is treated as a unit,Consists of Datafiles, Control files, Redo log files.
(optional param file, passwd file, archived log)
Instance memory Structures:
System Global Area
(SGA):
Allocated at instance
startup, and is a fundamental component of an Oracle Instance.
SGA Memory structures:
Includes Shared Pool,
Database Buffer Cache, Redo Log Buffer among others.
Shared Pool :
Consists of two key
performance-related memory structures Library Cache and Data Dictionary
Cache.
Library Cache:
Stores information
about the most recently used SQL and PL/SQL statements and enables the sharing
of commonly used statements.
Data Dictionary Cache
:
Stores collection of
the most recently used definitions in the database Includes db files, tables,
indexes, columns etc. Improves perf. During the parse phase, the server process
looks at the data dictionary for information to resolve object names and validate
access.
Database Buffer Cache:
Stores copies of data
blocks that have been retrieved from the datafiles. Everything done here.
Redo Log Buffer :
Records all changes
made to the database data blocks, Primary purpose is recovery. Redo entries
contain information to reconstruct or redo changes.
User process:
Started at the time a
database User requests connection to the Oracle server. requests interaction
with the Oracle server, does not interact directly with the Oracle server.
Server process:
Connects to the Oracle
Instance and is Started when a user establishes a session.
fulfills calls
generated and returns results.
Each server process
has its own nonshared PGA when the process is started.
Server Process Parses
and run SQL statements issued through the application, Reads necessary data
blocks from datafiles on disk into the shared database buffers of the SGA, if
the blocks are not already present in the SGA and Return results in such a way
that the application can process the information.
In some situations
when the application and Oracle Database operate on the same computer, it is
possible to combine the user process and corresponding server process into a
single process to reduce system overhead.
Program Global Area
(PGA):
Memory area used by a
single Oracle server process.
Allocated when the
server process is started, deallocated when the process is terminated and used
by only one process.
Used to process SQL
statements and to hold logon and other session information.
Background
processes:
Started when an Oracle
Instance is started.
Background Processes
Maintains and enforces relationships between physical and memory structures
There are two types of
database processes:
1. Mandatory background processes
2. Optional background processes
Mandatory background
processes:
– DBWn, PMON, CKPT,
LGWR, SMON
Optional background
processes:
– ARCn, LMDn, RECO,
CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn
DBWn writes when:
• Checkpoint occurs
• Dirty buffers reach
threshold
• There are no free
buffers
• Timeout occurs
• RAC ping request is
made
• Tablespace OFFLINE
• Tablespace READ ONLY
• Table DROP or
TRUNCATE
• Tablespace BEGIN
BACKUP
Log Writer (LGWR)
writes:
• At commit
• When 1/3rd full
• When there is 1 MB
of redo
• Every 3 seconds
• Before DBWn writes
System Monitor (SMON)
Responsibilities:
• Instance recovery
– Rolls forward
changes in redo logs
– Opens database for
user access
– Rolls back
uncommitted transactions
• Coalesces free space
• Deallocates
temporary segments.
Process Monitor (PMON)
Cleans up after failed processes by:
• Rolling back the
transaction
• Releasing locks
• Releasing other
resources
• Restarting dead
dispatchers
Checkpoint (CKPT)
Responsible for:
• Signaling DBWn at
checkpoints
• Updating datafile headers
with checkpoint information
• Updating control
files with checkpoint information
Archiver (ARCn)
• Optional background
process
• Automatically
archives online redo logs when ARCHIVELOG mode is set
• Preserves the record
of all changes made to the database
Why do you run
orainstRoot and ROOT.SH once you finalize the Installation?
orainstRoot.sh needs
to be run to change the Permissions and groupname to 770 and to dba.
Root.sh (ORACLE_HOME)
location needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab
in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin.
orainstRoot.sh
[root@oracle11g ~]#
/u01/app/oraInventory/orainstRoot.sh
Changing permissions
of /u01/app/oraInventory to 770.
Changing groupname of
/u01/app/oraInventory to dba.
The execution of the
script is complete
root.sh
[root@oracle11g ~]#
/u01/app/oracle/product/11.1.0/db_1/root.sh
Running Oracle 11g
root.sh script...
The following
environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1
Enter the full
pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to
/usr/local/bin ...
Copying oraenv to
/usr/local/bin ...
Copying coraenv to
/usr/local/bin ...
Creating /etc/oratab
file...
Entries will be added
to the /etc/oratab file as needed by
Database Configuration
Assistant when a database is created
Finished running
generic part of root.sh script.
Now product-specific
root actions will be performed.
Finished
product-specific root actions.
For Oracle
installation on unix/linux, we will be prompted to run a script 'root.sh' from
the oracle inventory directory.this script needs to run the first time only
when any oracle product is installed on the server.
It creates the
additional directories and sets appropriate ownership and permissions on files
for root user.
|
File type
|
Extension
|
Default location (when
created with OMF)
|
|
Pfile :
|
ORA
|
C:\oracle\product\10.2.0\admin\orcl\pfile
|
|
Spfile:
|
ORA
|
C:\oracle\product\10.2.0\db_1\database
|
|
Control file:
|
CTL
|
C:\oracle\product\10.2.0\oradata\orcl
|
|
Redo log file:
|
LOG
|
C:\oracle\product\10.2.0\oradata\orcl
|
|
Archive log file:
|
LOG
|
C:\oracle\product\10.2.0\flash_recovery_area\ORCL\ARCHIVELOG
|
|
Data file:
|
DBF
|
C:\oracle\product\10.2.0\oradata\orcl
|
|
Alert log files:
|
LOG
|
C:\oracle\product\10.2.0\admin\orcl\adump
|
|
Trace log files:
|
TRC
|
C:\oracle\product\10.2.0\admin\orcl\udump|bdump|cdump
|
|
Password file:
|
ORA
|
C:\oracle\product\10.2.0\db_1\database
|
Oracle Database 11g
New Feature for DBAs?
1) Automatic
Diagnostic Repository [ADR]
2) Database Replay
3) Automatic Memory
Tuning
4) Case sensitive
password
5) Virtual columns and
indexes
6) Interval Partition
and System Partition
7) The Result Cache
8) ADDM RAC
Enhancements
9) SQL Plan Management
and SQL Plan Baselines
10) SQL Access Advisor
& Partition Advisor
11) SQL Query Repair
Advisor
12) SQL Performance
Analyzer (SPA) New
13) DBMS_STATS
Enhancements
14) The Result Cache
15) Total Recall
(Flashback Data Archive)
Note: The above are
only top new features, there are other features as well introduced in 11g which
will be included subsequently
What is the Difference
Between Local Inventory and Global Inventory?
What is oraInventory ?
oraInventory is
repository (directory) which store/records oracle software products & their
oracle_homes location on a machine. This Inventory now a days in XML format and
called as XML Inventory where as in past it used to be in binary format &
called as binary Inventory.
There are basically
two kind of inventories,
One is Local Inventory
(also called as Oracle Home Inventory) and other is Global Inventory
(also called as Central Inventory).
What is Global
Inventory ?
Global Inventory holds
information about Oracle Products on a Machine. These products can be various
oracle components like database, oracle application server, collaboration
suite, soa suite, forms & reports or discoverer server . This global
Inventory location will be determined by file oraInst.loc in /etc (on Linux) or
/var/opt/oracle (solaris). If you want to see list of oracle products on
machine check for file inventory.xml under ContentsXML in oraInventory Please
note if you have multiple global Inventory on machine check all oraInventory
directories)
You will see entry
like
HOME
NAME=”ORA10g_HOME” LOC=”/u01/oracle/10.2.0/db” TYPE=”O” IDX=”1?/
What is Local
Inventory ?
Inventory inside each
Oracle Home is called as local Inventory or oracle_home Inventory. This
Inventory holds information to that oracle_home only.
What is Oracle Home
Inventory?
Oracle home inventory
or local inventory is present inside each Oracle home. It only contains
information relevant to a particular Oracle home. This file is located in the
following location:
$ORACLE_HOME/inventory
It contains the
following files and folders:
·
Components File
·
Home Properties File
·
Other Folders
Can I have multiple
Global Inventory on a machine ?
Quite common questions
is that can you have multiple global Inventory and answer is YES you can have
multiple global Inventory but if your upgrading or applying patch then change
Inventory Pointer oraInst.loc to respective location. If you are following
single global Inventory and if you wish to uninstall any software then remove
it from Global Inventory as well.
What to do if my Global Inventory is corrupted ?
No need to worry if
your global Inventory is corrupted, you can recreate global Inventory on
machine using Universal Installer and attach already Installed oracle home by
option
-attachHome
./runInstaller -silent
-attachHome -invPtrLoc $location_to_oraInst.loc
ORACLE_HOME=”Oracle_Home_Location”
ORACLE_HOME_NAME=”Oracle_Home_Name”
CLUSTER_NODES=”{}”
What is RESULT Cache?
11G Backgroung Processes?
The following process
are added in 11g as new background processes.
1 dbrm DB resource
manager
2 dia0 Diagnosability
process
3 fbda Flashback data
archiver process
4 vktm Virtual
Timekeeper
5 w000 Space
Management Co-ordination process
6 smc0 Space Manager
process
NOTE : The above six
are mandatory processes.
But 11g has 56 new
processes added which can be queried using
If any one of these 6
mandatory background processes is killed/not running, the instance will be
aborted ?
Background processes
are started automatically when the instance is started.
Mandatory background
processes are DBWn, LGWR, CKPT, SMON, PMON, and RECO. All other processes are
optional, will be invoked if that particular feature is activated.
If any one of these 6
mandatory background processes is killed/not running, the instance will be
aborted.
Any issues related to backgroud processes
should be monitored and analyzed from the trace files generated and the alert
log.
Literal Vs. Bind Variables?
Select * from emp where dept=10; what level should i configure for better performance Where Clause .... Literal or Bind Variables? Expalin?
What is a Baseline?
Incarnation? Explain in detail? Where the incarnation information will be stored?
Hard Parse Vs. Soft Parse?
What is semaphores, semaphores?
What is latch?
What is Enqueue?
Literal Vs. Bind Variables?
Select * from emp where dept=10; what level should i configure for better performance Where Clause .... Literal or Bind Variables? Expalin?
What is a Baseline?
Incarnation? Explain in detail? Where the incarnation information will be stored?
Hard Parse Vs. Soft Parse?
What is semaphores, semaphores?
What is latch?
What is Enqueue?
What is SGA_TARGET and
SGA_MAX_SIZE ?
SGA_MAX_SIZE is the
largest amount of memory that will be available for the SGA in the instance and
it will be allocated from memory. You do not have to use it all, but it will be
potentially wasted if you set it too high and don't use it. It is not a dynamic
parameter. Basically it gives you room for the Oracle instance to grow.
SGA_TARGET is actual
memory in use by the current SGA. This parameter is dynamic and can be
increased up to the value of SGA_MAX_SIZE.
SGA_MAX_SIZE and
SGA_TARGET both are the parameter are used to change the SGA SIZE.
SGA_MAX_SIZE sets the
maximum value for sga_target.
SGA_TAGET is 10G
feature used to change the sga size dynamically .it specifies the total amount
of SGA memory available to an instance.
this feature is called
Automatic Shared Memory Management. With ASMM, the parameters java_pool_size,
shared_pool_size, large_pool_size and db_cache_size are affected.
SGA_MAX_SIZE &
SGA_TARGET
SGA_MAX_SIZE sets the
overall amount of memory the SGA can consume but is not dynamic.
The SGA_MAX_SIZE
parameter is the max allowable size to resize the SGA Memory area parameters.
If the SGA_TARGET is set to some value then the Automatic Shared Memory
Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the
SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value.
I.e. If
SGA_MAX_SIZE=4GB and SGA_TARGET=2GB, later period of time, if you want you can
resize your SGA_TARGET parameter to the value of SGA_MAX_SIZE i.e. 4GB, you
can’t resize the SGA_TARGET value to more than 4GB.
It is significant that
SGA_TARGET includes the entire memory for the SGA, in contrast to earlier
releases in which memory for the internal and fixed SGA was added to the sum of
the configured SGA memory parameters. Thus, SGA_TARGET gives you precise
control over the size of the shared memory region allocated by the database. If
SGA_TARGET is set to a value greater than SGA_MAX_SIZE at startup, then the
latter is bumped up to accommodate SGA_TARGET
Do not dynamically set
or unset the SGA_TARGET parameter. This should be set only at startup.
SGA_TARGET is a
database initialization parameter (introduced in Oracle 10g) that can be used
for automatic SGA memory sizing.
SGA_TARGET provides
the following:
§ Single
parameter for total SGA size
§ Automatically
sizes SGA components
§ Memory is
transferred to where most needed
§ Uses workload
information
§ Uses internal
advisory predictions
§
STATISTICS_LEVEL must be set to TYPICAL
§ SGA_TARGET is
dynamic
§ Can be
increased till SGA_MAX_SIZE
§ Can be reduced
till some component reaches minimum size
§ Change in
value of SGA_TARGET affects only automatically sized components
If I keep SGA_TARGET
=0 then what will happen ?
Disable automatic SGA
tuning by setting sga_target=0
Disable ASMM by setting
SGA_TARGET=0
SGA_TARGET is a
database initialization parameter (introduced in Oracle 10g) that can be used
for automatic SGA memory sizing.
Default value
0 (SGA auto tuning is disabled)
What happens when you
run ALTER DATABASE OPEN RESETLOGS ?
The current online
redo logs are archived, the log sequence number is reset to 1, new database
incarnation is created, and the online redo logs are given a new time stamp and
SCN.
The reason to do the
open the database with the resetlogs is that after doing an incomplete recovery
, the data files and control files still don't come to the same point of the
redo log files. And as long as the database is not consistent within all the
three file-data, redo and control, you can't open the database. The resetlogs
clause would reset the log sequence numbers within the log files and would
start them from 0 thus enabling you to open the database but on the cost of
losing all what was there in the redo log files.
In what scenarios open
resetlogs required ?
An ALTER DATABASE OPEN
RESETLOGS statement is required,
1.after incomplete
recovery (Point in Time Recovery) or
2.recovery with a
backup control file.
3. recovery with a
control file recreated with the reset logs option.
Whenever you perform
incomplete recovery or recovery with a backup control file, you must reset the
online logs when you open the database. The new version of the reset database
is called a new incarnation..
Difference between
RESETLOGS and NORESETLOGS ?
After recover database
operation, open the database with: ALTER DATABASE OPEN [NO]RESETLOGS
NORESETLOGS:
The NORESETLOGS option
does not clear the redo log files during startup and the online redo logs to be
used for recovery. Only used in scenario where MANUAL RECOVERY is started,
CANCEL is used, and then RECOVER DATABASE is started.
RESETLOGS:
CAUTION: Never use
RESETLOGS unless necessary.
Once RESETLOGS is used
then the redo log files cannot be used and any completed transactions in those
redo logs are lost!!
Before using the
RESETLOGS option take an offline backup of the database.
What is SCN (System Change Number) ?
The system change
number (SCN) is an ever-increasing value that uniquely identifies a committed
version of the database at a point in time. Every time a user commits a
transaction Oracle records a new SCN in redo logs.
Oracle uses SCNs in
control files datafile headers and redo records. Every redo log file has both a
log sequence number and low and high SCN. The low SCN records the lowest SCN
recorded in the log file while the high SCN records the highest SCN in the log
file.
What is Database Incarnation ?
Database incarnation
is effectively a new “version” of the database that happens when you reset the
online redo logs using “alter database open resetlogs;”.
Database incarnation
falls into following category Current, Parent, Ancestor and Sibling
i) Current Incarnation
: The database incarnation in which the database is currently generating redo.
ii) Parent Incarnation
: The database incarnation from which the current incarnation branched
following an OPEN RESETLOGS operation.
iii) Ancestor
Incarnation : The parent of the parent incarnation is an ancestor incarnation.
Any parent of an ancestor incarnation is also an ancestor incarnation.
iv) Sibling
Incarnation : Two incarnations that share a common ancestor are sibling
incarnations if neither one is an ancestor of the other.
How to view Database Incarnation history of Database ?
Using SQL> select *
from v$database_incarnation;
Using RMAN>LIST
INCARNATION;
However, you can use
the RESET DATABASE TO INCARNATION command to specify that SCNs are to be
interpreted in the frame of reference of another incarnation.
•For example my
current database INCARNATION is 3 and now I have used
FLASHBACK DATABASE TO
SCN 3000;then SCN 3000 will be search in current incarnation which is 3.
However if I want to get back to SCN 3000 of INCARNATION 2 then I have to use,
RMAN> RESET DATABASE
TO INCARNATION 2;
RMAN> RECOVER DATABASE TO SCN 3000;
No comments:
Post a Comment