Lock & Latch
Control file function Control file is the heart of database which contain:
The name of database
Date and time of the creation of database
All the meta data
When a script (. oraenv, prod) is executed first thing is done it create a
control file because it to store the metadata of all the datafiles that you
are creating in the database
when your script is executed we will find that our database is already up
and running, >
so first thing we started our database in nomount, >
then first thing our scrip does it create a control file and it transfer our
database into mount state >
then it creates all the datafiles and after creating all the datafile opens the
database
Metadata metadata refers to the data that describes the structure and properties
of the actual data within the database. It provides information about the
organization, storage, and manipulation of the data. In an Oracle
database, metadata includes various types of information
Table Definitions
View Definitions
Index Information
Stored Procedures and Functions
Trigger Definitions
User and Role Information
Database Objects and Relationships
Data Dictionary Views
System and Session Information
User Preferences
Database Parameters and Initialization Files
System Statistics
Data File Information
Redo Log Information
Control File Metadata
Dictionary Cache
Error in database There are certain tool like log file called alert log (If there is any error
occurs in database it will start writing into the file)
How will I monitor
database as DBA Cd /u01/app/oracle/rdbms/dev/dev/trace/ >>> ls -l
In the above directory there are .trc files are there. This file contain all
the information of errors in details
Location of Alart log Desc v$diag_info
Select name,value from v$diag_info;
Physical location : cd /u01/app/oracle/ >>> ls -l >>> cd
diag/rdbms/dev/dev/trace/ >>> tail -15f alert_dev.log
It will find all the background processes are running here
Interview Question Suppose alert log file has been deleted , what is its affect database
It will not hamper your database but if there is any new error in database
it will create a new alert file
Lock situation in "lock situation" refers to a scenario where one session (or transaction)
oracle holds a lock on a resource (such as a table or a row) and another session
is trying to access or modify that same resource.
To check lock there is Desc v$lock
a view
Now finding out which session is block and which one is waiting
Select a.sid “Blocking session”,b.sid “Blocked session”
From v$lock a, v$lock b
Where a.sid !=b.sid and a.id1=b.id1 and a.id2=b.id2 and
b.request >0 and a.block=1;
AND
To know the exact location of blocked session
select s1.username || '@' || s1.machine || ' (SID=' ||
s1.sid || ') as blocking '
|| s2.username || '@' || s2.machine
|| ' (SID=' || s2.sid || ')' as blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
Blocking
Blocking
Row level Table level
Row-level Locks: These locks are placed on individual rows in a table. They
can be shared (multiple sessions can read the row simultaneously) or
exclusive (only one session can write to the row at a time)
Table-level Locks: These locks are placed on an entire table. They can also
be shared or exclusive. A shared table lock allows multiple sessions to
read from the table simultaneously, but only one session can write to the
table at a time with an exclusive table lock.
Finding out the 1.26
query due to which
session Blocking
happened
Dead lock situation A deadlock in Oracle occurs when two or more transactions are unable to
proceed because each is waiting for the other to release a lock on a
resource. This creates a circular dependency where none of the
transactions can proceed.
For example, consider two transactions:
1. Transaction A holds a lock on Resource X and needs a lock on
Resource Y.
2. Transaction B holds a lock on Resource Y and needs a lock on
Resource X.
In this situation, Transaction A cannot proceed because it's waiting for
Resource Y, which is locked by Transaction B. Similarly, Transaction B
cannot proceed because it's waiting for Resource X, which is locked by
Transaction A. This creates a deadlock.
Interview question As a DBA we can’t do anything
Action taken by oracle he will release one of the trasaction
As a DBA what we or
can do in case of In this situation we can ask the user to complete the transaction either
Dead lock situation ‘rollback or committed
Kill a session Alter system kill session ‘<SID>,<serial no> immediate;
From where did I get Select sid,serial#, username from v$session;
SID and serial
number
"listener" refers to a process or service that listens for incoming client
connection requests and manages the traffic to the database.
It acts as an intermediary between the client application and the database
instance. The listener process uses a protocol to establish communication
between the client and the database.
Client sends a connection request: When a client application
wants to connect to an Oracle database, it sends a connection
request to the Oracle listener.
Listener processes the request: The listener receives the
connection request and checks if it matches any of the services it is
configured to listen for.
Establishing a connection: If a match is found, the listener
establishes a connection between the client and the database
instance.
Routing traffic: The listener is responsible for routing subsequent
communication between the client and the database. It also
manages connection pooling and failover.
Monitoring connections: The listener continuously monitors the
connections and, if necessary, drops inactive or expired
connections.
note that the listener itself doesn't store any data. Its primary function is
to manage the connections between clients and the database instances.
Listener creation Listener should be created in $ prompt but environment must be created
To create listener there are two tools
Netmgr (network manager)
Netca (network consideration application)
Listener location cd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin
ls-l
Check name of my hostname
server
To check running ps -ef | grep tns
status of listener
To start listener Lsnrctl start <listner name>
To stop listener Lsnrctl stop <listner name>
Create a clone of
your server
