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
Upgrading to Oracle Database 11g
Direct upgrades to 11g are possible from existing databases with versions 9.2.0.4+, 10.1.0.2+ or 10.2.0.1+. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.
1.Manual upgrade which involves the following steps:
Backup the database.
In UNIX/Linux environments, set the $ORACLE_HOME and $PATH variables to point to the new 11g Oracle home.
Analyze the existing instance using the "$ORACLE_HOME/rdbms/admin/utlu111i.sql" script, explained below.
Start the original database using the STARTUP UPGRADE command and proceed with the upgrade by running the "$ORACLE_HOME/rdbms/admin/catupgrd.sql" script.
Recompile invalid objects.
Restart the database.
Run the "$ORACLE_HOME/rdbms/admin/utlu111s.sql" script and check the result of the upgrade.
Troubleshoot any issues or abort the upgrade.
Backup the database
-------------------
rman "target / nocatalog"
RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';
}
The easiest way to abandon the upgrade is to restore that backup.
rman "target / nocatalog"
STARTUP NOMOUNT
RUN
{
REPLICATE CONTROLFILE FROM 'save_controlfile_location';
ALTER DATABASE MOUNT;
RESTORE DATABASE FROM TAG before_upgrade
ALTER DATABASE OPEN RESETLOGS;
}
In UNIX/Linux environments, set the $ORACLE_HOME and $PATH variables to point to the new 11g Oracle home.
Creating the SYSAUX tablespace which is required for 11g.
SQL> CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
RMAN Cloning using active database feature - Oracle 11g feature
Normally we well knew about RMAN cloning.
Oracle9i/10g using duplicate cloning command, we do the RMAN cloning.
We need a RMAN full backup for cloning the database in oracle9i/10g.
In oracle 11g provides a new feature, without RMAN database backup we can clone the database.
Environment Details:
Operating system: Windows XP service pack 2
Database Version: 11.1.0.7 (32 bit)
Source database name: troy
Clone database name: clonedb
How its work?
In an active duplication process, target database online image copies and archived redo log files were copied through the auxiliary instance service name. So we no need the target database backup.
Target database must be in archive log mode.
Database duplication process RMAN does the following things
1.Generate the unique DBID for auxiliary database.
2.Copy the data files & archived log files from target database to auxiliary database.
3.Recreate the new control files for auxiliary database.
4.Recreates the online redo log files.
5.Restart the auxiliary instance.
6.Open the database with RESETLOGS.
Real Application Clusters
Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide a highly scalable and available database solution for all your business applications. Oracle RAC provides the foundation for enterprise grid computing.
Oracle’s Real Application Clusters (RAC) option supports the transparent deployment of a single database across a cluster of servers, providing fault tolerance from hardware failures or planned outages. Oracle RAC running on clusters provides Oracle’s highest level of capability in terms of availability, scalability, and low-cost computing.
One DB opened by multipe instances so the the db ll be Highly Available if an instance crashes.
Cluster Software. Oracles Clusterware or products like Veritas Volume Manager are required to provide the cluster support and allow each node to know which nodes belong to the cluster and are available and with Oracle Cluterware to know which nodes have failed and to eject then from the cluster, so that errors on that node can be cleared.
Oracle Clusterware has two key components Cluster Registry OCR and Voting Disk.
The cluster registry holds all information about nodes, instances, services and ASM storage if used, it also contains state information ie they are available and up or similar.
The voting disk is used to determine if a node has failed, i.e. become separated from the majority. If a node is deemed to no longer belong to the majority then it is forcibly rebooted and will after the reboot add itself again the the surviving cluster nodes.
Data Guard
Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as copies of the production database. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
Data Guard Configurations:
A Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other.
Primary Database:
A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications.
Standby Databases:
A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database.
The types of standby databases are as follows:
Physical standby database:
Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, through Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.
Logical standby database:
Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database through SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database.
Snapshot Standby Database (UPDATEABLE SNAPSHOT FOR TESTING)
A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database.
Like a physical or logical standby database, a snapshot standby database receives and archives redo data from a primary database. Unlike a physical or logical standby database, a snapshot standby database does not apply the redo data that it receives. The redo data received by a snapshot standby database is not applied until the snapshot standby is converted back into a physical standby database, after first discarding any local updates made to the snapshot standby database.
Row Chaning and Migration
Row Migration:
A row migrates when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently). A migration means that the entire row will move and we just leave behind the «forwarding address». So, the original block just has the rowid of the new block and the entire row is moved.
Row Chaining:
A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces.
Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with LONG and LONG RAW columns are prone to having chained rows. Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces.
So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.
Top 10 Things to Look for in AWR Report and
STATSPACK Output
Many DBAs already know how to use STATSPACK but are not always sure what to check regularly.
Remember to separate OLTP and Batch activity when you run STATSPACK, since they usually
generate different types of waits. The SQL script “spauto.sql” can be used to run STATSPACK
every hour on the hour. See the script in $ORACLE_HOME/rdbms/admin/spauto.sql for more
information (note that JOB_QUEUE_PROCESSES must be set > 0). Since every system is different,
this is only a general list of things you should regularly check in your STATSPACK output:
¦ Top 5 wait events (timed events)
¦ Load profile
¦ Instance efficiency hit ratios
¦ Wait events
P:\010Comp\OracleT&T\305-9\ch14.vp
Color profile: Generic CMYK printer profile
Composite Default screen
¦ Latch waits
¦ Top SQL
¦ Instance activity
¦ File I/O and segment statistics
¦ Memory allocation
¦ Buffer waits
Oracle Architecture
An Oracle server: a DBMS that provides an open, comprehensive, integrated approach to information management, Consists of an Instance and a database.
An Oracle Instance: a means to access an Oracle database, always opens one and only one database and consists of memory structures and background process.
An 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)
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.
What would be the first thing you would do if an end user complains that performance is poor?
I always used to come up with this question on forums and well as from the Application support, and a time with my first interview in India.
As for the answer to the question, one should touch upon all these points:
- Check alert log, for any errors and UN even entries
- Check for archive log destination/file system full
- Check for database locks # Click here for a notes on locks
- Check server performance (CPU/IO)
- Check network performance
- Check if statistics is up to date
- If new SQL, then tune SQL (maybe take trace)
- Check if any parameters have been modified recently
- Check if any new updates as been done from the Application team.
Oracle Database 11g Top 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
How to Download Patchset or Opatch from metalink
Update:
If you are using latest support.oracle.com then after login to metalink Dashboard
- Click on Patches and Updates tab
- On the left sidebar under "Oracle Server/Tools" click on "Latest Patchsets".
- A new window will appear.
- Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page.
- Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that.
- You will go the download page. From the download page you can also change your platform and patchset version.
http://docs.oracle.com/cd/E11857_01/em.111/e12255/e_oui_appendix.htm
Oracle® Universal Installer and OPatch User's Guide
11g Release 2 (11.2) for Windows and UNIX
Part Number E12255-11
How to apply a patch ?
1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.
2. Make sure you have a good backup of database.
3. Make a note of all Invalid objects in the database prior to the patch.
4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
5. You MUST Backup your oracle Home and Inventory
tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz
6. Unzip the patch in $ORACLE_HOME/patches
7. cd to the patch direcory and do opatch -apply to apply the patch.
8. Read the output/log file to make sure there were no errors.
Patching Oracle Software with OPatch
Example 7-1
opatch napply <patch_location> -skip_subset -skip_duplicate
Example 7-2 applies patches 1, 2, and 3 that are under the <patch_location> directory. OPatch skips duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the Oracle home).
Table 7-1 OPatch OUI-based Commands
Command
Description
apply
Installs an interim patch. See "Apply Command for OUI-based Oracle Homes" for more information.
napply
Applies Oracle Clusterware patches. See the "Auto Command for OUI-based Oracle Homes" for more information.
auto
Applies Oracle Clusterware patches. See the "Auto Command for OUI-based Oracle Homes" for more information.
lsinventory
Lists what is currently installed on the system. See "Lsinventory Command for OUI-based Oracle Homes" for more information.
query
Queries a given patch for specific details. See "Query Command for OUI-based Oracle Homes" for more information.
rollback
Removes an interim patch. See "Rollback Command for OUI-based Oracle Homes" for more information.
nrollback
Removes n number of patches (hence nrollback). See "Nrollback Command for OUI-based Oracle Homes" for more information.
version
Prints the current version of the patch tool. See "Version Command for OUI-based Oracle Homes" for more information.
Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch
http://avdeo.com/2008/08/19/opatch-utility-oracle-rdbms-patching/
OPatch also maintains an index of the commands executed with OPatch and the log files associated with it in the history.txt file located in the <ORACLE_HOME>/cfgtoollogs/opatch directory.
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.
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=”{}”
Do I need to worry about oraInventory during oracle Apps 11i cloning ?
No, Rapid Clone will update both Global & Local Inventory with required information , you don’t have to worry about Inventory during Oracle Apps 11i cloning.
Central Inventory
The Central Inventory contains the information relating to all Oracle products installed on a host. It contains the following files and folders:
· Inventory File
· Logs Directory
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
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.
To Find and Delete bigger size and older files in Linux
--To find out files size more than 5MB
find . -size +5000 -exec ls -ltr {} \;
-- To **Remove** files size more than 5MB
find . -size +5000k -exec rm -rf {} \;
--To find out files older than 30days
find . -mtime +30 -exec ls -ltr {} \;
--To find **Remove** files older than 30days
find . -mtime +30 -exec rm -rf {} \;
No comments:
Post a Comment