Tell me about yourself? Your role as a DBA? Your Day to Day
activities?
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 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.
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.
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 ?
http://oracleappstechnology.blogspot.in/2008/05/difference-between-resetlogs-and.html
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;
ORACLE – BACKUP AND RECOVERY
How would you decide your backup strategy and timing for backup?
In fact backup strategy is purely depends upon your organization
business need.
If no downtime then database must be run on archivelog mode and
you have to take frequently or daily backup.
If sufficient downtime is there and loss of data would not
affect your business then you can run your database in noarchivelog mode and
backup can be taken in-frequently or weekly or monthly.
In most of the case in an organization when no downtime then
frequent inconsistent backup needed (daily backup), multiplex online redo log
files (multiple copies), different location for redo log files, database must
run in archivelog mode and dataguard can be implemented for extra bit of
protection.
What is difference between Restoring and Recovery of database?
Restoring means copying the database object from the backup
media to the destination where actually it is required where as recovery means
to apply the database object copied earlier (roll forward) in order to bring
the database into consistent state.
What is the difference between complete and incomplete recovery?
An incomplete database recovery is a recovery that it does not
reach to the point of failure. The recovery can be either point of time or
particular SCN or Particular archive log specially incase of missing archive
log or redolog failure where as a complete recovery recovers to the point of
failure possibly when having all archive log backup.
What is the benefit of running the DB in archivelog mode over no
archivelog mode?
When a database is in no archivelog mode whenever log switch
happens there will be a loss of some redoes log information in order to avoid
this, redo logs must be archived. This can be achieved by configuring the
database in archivelog mode.
If an oracle database is crashed?
How would you recover that transaction which is not in backup?
If the database is in archivelog we can recover that transaction
otherwise we cannot recover that transaction which is not in backup.
What is the difference between HOTBACKUP and RMAN backup?
For hotbackup we have to put database in begin backup mode, then
take backup where as RMAN would not put database in begin backup mode. RMAN is
faster can perform incremental (changes only) backup, and does not place
tablespace in hotbackup mode.
Can we use Same target database as Catalog database?
No, the recovery catalog should not reside in the target
database (database to be backed up) because the database can not be recovered
in the mounted state.
Incremental backup levels:
Level 0 – full backup that can be used for subsequent
incrementals
RMAN> backup incremental level 0 database;
Differential Level 1–only the blocks that have changed since the
last backup (whether it is level 0 or level 1)
RMAN> backup incremental level 1 differential database;
Cumulative Level 1 – all changes since the last level 0
incremental backup
RMAN> backup incremental level 1 cumulative database;
A full backup cannot be used for a cumulative level 1 backup.
A cumulative level 1 backup must be done on top of an
incremental level 0 backup.
Why RMAN
incremental backup fails even though full backup exists?
If you have taken the RMAN full backup using the command ‘Backup
database’, where as a level 0 backup is physically identical to a full backup.
The only difference is that the level 0 backup is recorded as an incremental
backup in the RMAN repository so it can be used as the parent for a level 1
backup. Simply the ‘full backup without level 0’ can not be considered as a
parent backup from which you can take level 1 backup.
Can we perform RMAN level 1 backup without level 0?
If no level 0 is available, then the behavior depends upon the
compatibility mode setting (oracle version).
If the compatibility mode less than 10.0.0, RMAN generates a
level 0 backup of files contents at the time of backup.
If the compatibility is greater than 10.0.0, RMAN copies all
block changes since the file was created, and stores the results as level 1
backup.
How to put Manual/User managed backup in RMAN?
In case of recovery catalog, you can put by using catalog
command:
RMAN> CATALOG START WITH ‘/oracle/backup.ctl’;
How to check RMAN version in oracle?
If you want to check RMAN catalog version then use the below
query from SQL*plus
SQL> Select * from rcver;
What happens actually in case of instance Recovery?
While Oracle instance fails, Oracle performs an Instance
Recovery when the associated database is being re-started. Instance recovery
occurs in 2 steps:
Cache recovery: Changes being made to a database are recorded in
the database buffer cache as well as redo log files simultaneously. When there are
enough data in the database buffer cache, they are written to data files. If an
Oracle instance fails before these data are written to data files, Oracle uses
online redo log files to recover the lost data when the associated database is
re-started. This process is called cache recovery.
Transaction recovery: When a transaction modifies data in a
database (the before image of the modified data is stored in an undo segment
which is used to restore the original values in case the transaction is rolled
back). At the time of an instance failure, the database may have uncommitted
transactions. It is possible that changes made by these uncommitted
transactions have gotten saved in data files. To maintain read consistency,
Oracle rolls back all uncommitted transactions when the associated database is
re-started. Oracle uses the undo data stored in undo segments to accomplish
this. This process is called transaction recovery.
Do you like this post? Please share this article.
HTML Link Code:
2 comments
Please post the scenarios about Patches and ASM............
ReplyNice blog Bro....
Hi Bro,
ReplyKeep visiting my blog..I will update very soon all topics from beginner levels to expert.
Post a Comment