User Process - A user process connects with a server process toestablish a session.
Server Process : serves the requests of the user process.
Oracle can be said to be composed of 2 major components :
1. Instance =SGA ( System Global Area) + Background Processes
2. Database - All the Files ( C - D - R) - This is the physical Database
C = Control File , D= Datafile , R= Redo log file
SGA (System Global Area)
------------------------------------------------------------
Major Components :
SGA = Database Buffer Cache + Shared Pool + Redo Buffer
Shared Pool = Library Cache + Data Dictionary Cache
Shared Pool
Library Cache
The compiled version of the statement (Parse Tree)The Execution Plan - tells how to run the statement, determined by theoptimizer.
Data dictionary cache: Used during the Parse phase to resolve objectnames and validate access privileges.
Table and column definitionsUser names, Passwords and Privileges
-Database Buffer cache: Holds the most recently used data.The Least Recently Used (LRU) algorithm is used to age out blocks ofdata in the Buffer Cache. Each buffer in the buffer cache is equal tothe size of the specified data block size.
DB_BLOCK_BUFFERS -parameter sets the number of buffers available.
DB_BLOCKSIZE - parameter sets the size of the data block
-Redo Log Buffer: Registers changes made to the database via theinstance. Size is defined by LOG_BUFFER in the parameter file.
Stores records of changes: Block that changed, the new value, andlocation of the change.The buffer is reused after the redo entries are recorded in the redo logfiles.
PGA (Program Global Area)Used by one Process only and contains the following
Session Information Sort AreaCursor StateStack SpaceDBWR (Database Writer)Writes changed data to the database file when one of the followingconditions are met:Dirty Buffers reach a threshold valueNo free blocks are availableA timeout occurs.
A DBWR checkpoint takes placeLGWR (Log Writer)Writes changes registered in the redo log buffer to the database filewhen one of the following conditions are met:
When the redo log buffer is 1/3 full
A timeout occurs (about every 3 seconds)Before DBWR writes modified blocks to the data files
A transaction COMMITS
SMON (System Monitor)Checks for consistency and initiates recovery.
PMON (Process Monitor)Cleans up resources after a process fails. Watches after the serverprocess
CKPT (Checkpoint Process)Updates the database status information after changes are permanentlywritten to the database file from the buffer cache.
Database Files
-Data files
-Redo log files
-Control files
-Parameter file
-Password file
-Archived redo log files
Query Processing
Parse: checks syntax
Execute:
Fetch: returns data to userDML
StatementsRequires Parse and Execute
Server process reads the data and rollback blocks from the data files.
Blocks that are read then are placed in the Buffer Cache.
Server process locks the data and objects.Server Process records the changes to the rollback and data (new) in theredo log buffer.The Server Process then records the rollback block "old" and updates thedata block "new" in the database buffer cache. Both are marked "dirtybuffers."
Friday, October 26, 2007
Oracle Architecture
Friday, October 19, 2007
What Happens when you login to Oracle Database:
Ans:How the Oracle Database processes operate—by decoupling the user process from the server process. If you don't remember it completely,When a user connects to an Oracle database, say with SQL*Plus, Oracle creates a new process to service this user's program.
This new process is called the Oracle server process, which differs from the user's process (sqlplus, sqlplus.exe, TOAD.exe, or whatever it else it may be). This server process interacts with the memory structures such as the System Global Area (SGA) and reads from the data files; if the data is not found in the data block buffers in the SGA, and so on. Under no circumstances is the user's process (sqlplus) allowed to directly interact with the Oracle database. As there are two processes (the user process and the server process) working in tandem to get the work done, this is sometimes known as two-task architecture.If a user process does something potentially disruptive, such as violating the memory management in the host machine, the Oracle database itself is not affected and the damage is limited to the user's process.
Note: that the above applies to Oracle connections in a dedicated server environment. In a multi-threaded server environment, this model is a little different in the sense that a single server process can service more than one user process. It's still two-task, but instead of a 1:1 relation between the server and user processes, it's 1:many.
What are Host based Connections and What are Network-based Connections?
Ans:Host Based Connection:
Suppose the user logs into the database using SQL*Plus.
oracle@myserver::/scripts [practicedb] $ sqlplus bnfdba
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 19 17:44:56 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Enter password:
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options
USER is "BNFDBA"
Instance
----------
PracticeDB
PracticeDB > select sid from v$mystat where rownum=1;
SID
----------
3917
PracticeDB > select spid from v$session s, v$process p
2 where s.sid = (select sid from v$mystat where rownum <2) addr =" s.paddr;"> !ps -eafgrep 10442
bnfdba 17151 17149 1 18:32:58 pts/7 0:00 grep 10442
bnfdba 10442 10212 0 18:27:07 ? 0:00 oraclepracticedb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
This, of course, assumes that no other SQL*Plus sessions have been running on the server.
The first one you've seen before (the process for the SQL*Plus session). The second one—process ID 10442—is the server process that Oracle creates for the user. Note the Parent Process ID of the process; it's 10212, which is the process ID of the SQL*Plus session.
The process name is "oraclepracticedb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))", which tells you several things. First, the presence of the clause
LOCAL=YES indicates that this process started due to another process that is running locally, on the same server as the database itself. It also shows PROTOCOL=beq, which means that the connection was made through a bequeath connection. You can also find the information about the server process from the dynamic views.
select spid
from v$session s,v$process p
where s.sid = (select sid from v$mystat where rownum <2) addr =" s.paddr;" practicedb =" (DESCRIPTION" address_list =" (ADDRESS" protocol =" TCP)(HOST" port =" 1521))" connect_data =" (SERVER" service_name =" PRACTICEDB)" onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:oracle@myserver::/scripts" target="_blank">oracle@myserver::/scripts [practicedb] $ sqlplusbnfdba@practicedb.bnfd.com
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 19 17:44:56 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Enter password:
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options
USER is "BNFDBA"
Instance
----------
PracticeDB
PracticeDB > select sid from v$mystat where rownum=1;
SID
----------
3939
After this, if you search for this process:
It shows:
PracticeDB > @vusersid
Enter value for sid: 3939
Press return to continue
Fri Oct 19 page 1
User Session Statistics
CLIENT SERVER OS ORACLE
PID PID SID SERIAL USERNAME USERNAME MODULE
---------- ------- ----------- ----- --------- -------- ----- - --------------------------------26917 27292 3939 34941 oracle BNFDBA SQL*Plus
This, of course, assumes that no other SQL*Plus sessions have been running on the server.
Note the process id (26917). Now if you search that process ID Searching for this on the server:
PracticeDB > !ps -eafgrep 26917
oracle 383 26917 1 17:47:22 pts/7 0:00 grep 26917
oracle 26917 29489 1 17:44:55 pts/7 0:00 sqlplus bnfdba@practicedb.bnfd.com <---This is the Client PID 29489 PracticeDB > !ps -eafgrep 27292
oracle 27292 1 0 17:45:03 ? 0:00 oraclepracticedb (LOCAL=NO) <-----This is the Server PID 27292 oracle 286 26917 1 17:47:11 pts/7 0:00 grep 27292 The parent process is "1". But why isn't it 29489?
Ans:To understand the answer, you have to understand how different Oracle server processes are created. In the first case, when the user did not use a TNS connect string, theconnection was routed directly to the database without going to the listener first. The database created a user process and then handed the control of the process to the
process owner, a process known as bequeathing—hence the term bequeath process, which showed up in the process name. In the second case, when the user was still on the same server but connected through the listener, the listener created the process for the user—which is known as forking. Similarly, if the user process were running on a different machine (such as a laptop), the connection would have to be made to the listener and the listener would have
created the process. The process was created by a remote server, hence the process name contains the clause LOCAL=NO. Even if the SQL*Plus session was running on the same server, the fact that it was a non-bequeath connection made it a non-LOCAL one.
Note: Depending on the OS, you may not see the parent ID of the server process the same way you see the SQL*Plus session in bequeath connections. In some cases, even through the connection is bequeath, the parent ID will show as "1". Therefore, don't rely on the parent ID to determine what type of server process it is; use the process name instead.
Thursday, October 18, 2007
DBA Responsibilities:
As a DBA, my responsibilities are :
We have production systems and non-production (Development, Testing, QA ) systems.
1. Health Check - of database servers. I use monitoring scripts for database health checkup.
2.Code Migration - With time the enhanced facilities and technical upgradation of modules are done through code migrations from development instances to Production systems.
3.Address daily/Weekly Space requests.-Monitor and add spaces as when required.
4. Manage Redo - Monitor Archive log files and troubleshoot if programs eror out due to it.
5.Manage Printers
* Review and design database structures to support business requirements including logical and physical database modeling;
* Install, configure and maintain database instances, creates various database objects, creates users with appropriate roles and levels of security.
* Recommend and implements database security policies that are consistent with security guidelines;
* Recommends and implements database backup and recovery strategies that meet recovery and availability requirements;
* Mentor DBA team members and developers, and delegates appropriately;
* Provide DBA support and technical expertise for multiple applications in development, test, QA and production environments;
* Troubleshoots and documents database problems and resolutions;
* Lead database projects and manages resources required for successful completion of the project and
* Support all database platforms.
* Strong experience in all aspects of database administration is required.
