How to Multiplex Control Files in Oracle Database?

After this article, you will have the understanding of,

• How to add one or more control files in oracle database? 
• Why do we require control file multiplexing? 
• Why is it important to have multiple copies of control files in your oracle base?

Control files serve a very important role and are very crucial for database working because of the information stored inside them. You can't take the risk of losing your control file especially when the database is running. When database is properly shutdown, even if you lose your control file, you can recreate it without much trouble. But the general idea is to secure the control file but taking regular backups and also multiplexing the same. By multiplexing, we simply mean to keep multiple copies of the same file in different locations.

Password File Management in Oracle Database

What is the purpose of a password file in Oracle Database?

Password file stores the password for users having SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSKM and SYSDG privileges. By default, SYS is the only user to have SYSDBA+SYSOPER privileges. The password file is maintained on OS at locations, 

$ORACLE_HOME/database - for windows systems
$ORACLE_HOME/dbs - for linux/unix systems

Find SID from Process ID (PID) in Oracle

 How to find SID from PID in Oracle Database?

The below query helps in finding the Session ID using Process ID within Oracle Database,

SELECT s.SID,
       s.serial#,
       s.machine,
       s.osuser,
       s.terminal,
       s.username
  FROM v$process P LEFT OUTER JOIN v$session s ON P.addr = s.paddr
 WHERE P.spid = 12345;

Components of Oracle DB BUFFER CACHE

What are the components of DB BUFFER CACHE?

Below mentioned are the components of db buffer cache,

• Default pool (where blocks are normally buffered)
• Big table cache (optional part of the default pool; uses a temperature based algorithm instead of the LRU based one)
• Keep pool - data buffers for frequently running queries
• Recycle pool - data buffers for least running queries
• Non-default buffer pools (used for non-standard block sizes of 2, 4, 16 or 32 KB, when you have different tablespaces with different db block sizing)
• Database Smart Flash Cache (used in flash cache)
• Least Recently Used list (LRU)
• Checkpoint queue
• Flash Buffer Area

Cache Hit vs Cache Miss in Oracle Database

 Difference between Cache Hit and Cache Miss

Cache Hit: When running a SELECT statement within Oracle database, if the relevant data is already found in the db buffer cache, that signifies its a Cache Hit. 

Cache Miss: When any transaction is not able to find data in the db buffer cache and it has to go to datafile to fetch that data, it means cache miss. Which means it would take more time to process that transaction.

The more Cache hit ratio you have, the better performance you have within your database. It is always beneficial to keep data buffers available for frequently running queries within db buffer cache and hence database memory components should be tuned accordingly.

Important Parameters related to DB_BUFFER_CACHE

 Important Parameters related to DB BUFFER CACHE in Oracle Database

What are the parameters related to Oracle Database Buffer Cache? How to modify/tune db buffer cache within Oracle Database?

The below mentioned parameters are useful when you want to configure DB BUFFER CACHE as per your need,

Oracle Query to check OS PID from SID

How to find OS Process ID using Session ID in oracle?

Below query will help you extract the PID on OS using SID. Normally in OS, different processes takes care of different operations and the same goes for Oracle as well. In random scenarios, you could be interested in finding PID for some operation running within database for example, a background job for which you want to track the resource consumption. The below query will help in such scenarios.

Set lines 200
col sid format 99999
col username format a15
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;
/