Oracle:RM

RM Ensure how resources are accessed at mixed load like DSS and OLTP
Component of RM:
Resource consumer group: Group of resource with similar need
Resource Plan: Actual Plan How the resource will be divided
Resource directive: Link between Resource Consumer and Resource plan. Make sure how Resource consumer uses resource plans

What all resources can be controlled?
1. CPU utilization
2. Degree of parallelization
3. Session polling with queing: Control active sessions per resource consumer group
One queue per resource consumer group and FIFO is used for resource manager queue management with timeout

queuing_p1: Time after a session will timeout from the queue
active_session_pool_p1: How many concurrent session can be on a queue?
4. Execution time
5. Idle Time

Automatic Consumer Group Switch

Switch_time: after being idle for some time switch to the specified switch_group
switch_group: Group to switch when certain criteria meet.
switch_time_in_call : Mainly used for 3 tire application it is almost same as switch time but after switch session will switch back to the top call program.
switch_estimate: If set estimate is calculated and if database estimation is longer then the time specified in switch_time it does not even start the operation
and switch happens
You can not specify Switch_time and switch_time_in_call both the parameter for same resource group

We can also use automatic consumer group switch in switching in case of long running query and killing session
"cancel_sql" and "kill_session"

You can not set switch_group to either "cancel_sql" and "kill_session" through EM
You can not set switch_time_in_call from EM console
Adaptive Consumer group mapping:
We can use resource manger to configure session to map to the specific consumer group based on the module/application or session attribute.
Two type of session attribute:
Login attribute: meaning full at session login time
Run Time Attribute: Meaningful during run time based on the session attribute it can change/switch consumer group.

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING.use pending area for these procedure.
Oracle provides one default manager resource plan called system_plan and is basically used for system sessions. It contains directive for following
1. sys_group : Primary consumer group for sys and system users
2. other_goup : used for all sessions who belongs to consumer group not part of any active resource plan
3. low_group :No user associated with this consumer group. Low priority then sys_group and other_group

You can create a new Resource plan with the help of
1.EM
2.DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN
3.DBMS_RESOURCE_MANAGER procedure to create complex plan. steps to do that....
It provides the greatest level of customization and flexibility in terms of configuration....

Create Pending area prior to using RM command pending area need to be created for the work with the help of DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA procedure
Create resource plan
Create resource consumer group
Create resource directive
Validate the pending area

To configure the resource manager consumer group and plans you need to have a DBA role or
ADMINISTER_RESOURCE_MANAGER SYSTEM Privilege

Certification-Exam Notes Part-1

• · SMON performs the roll forward process by applying changes recorded in the online redo log files from the last checkpoint
  · Rolling forward recovers data which including the contents of rollback segments
  · Rollback can occur while the database is open, because either SMON or a server process can perform the rollback operation.
  · RMAN uses the large pool for backup and restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O.
  
  · When the database is set to Archive log mode, the LGWR process waits for the online redo log files to be archived(either manually or through the ARCn process) before they can be reused.
  
  · An Oracle database cannot be opened unless all datafiles redo logs, and control files are synchronized.
  
  · During Recovery Archived and online redo log files recover committed transactions and roll back
  Uncommitted transactions to synchronize the database files.
  
  · Archived and online redo log files are automatically requested by the oracle server during the recovery phase. Make sure logs exist in the requested location.
  Roll forward (redo)
  Roll back (undo)
  
  · Undo segments are populated during the roll forward phase.
  · You can restore a backup copy of the damaged files and use archived log files to bring the data files up-to-date while the database is online or offline.
  · You can recover the database to a specific point in time.
  · You can recover the database to the end of a specified archived log file.
  · You can recover the database to a specific system change number(SCN).
  · Setting the database in Archivelog mode does not enable the Arciver(ARCn) processes.
  · The dynamic parameter LOG_ARCHIVE_MAX_PROCESSES controls the number of archive processes started at instance startup.
  · When LOG_ARCHIVE_START is set to TRUE, an Oracle instance starts up with as many archiver processes as defined by LOG_ARCHIVE_MAX_PROCESSES.
  
  ALTER SYSTEM ARCHIVE LOG LOG_ARCHIVE_DEST_n
  · Only one archive destination per remote database can be specified.
  · Although it is not mandatory to create a recovery catalog to use RMAN, it is beneficial to use a recovery catalog. The recovery catalog should be located in a database different from the target database.
  
  TSPITR(tablespace point-in-time recovery)
  
  · The following commands change the database configuration and result in changes to control file.
  
  alter database [adddrop] logfile
  alter database [adddrop] logfile member
  alter database [adddrop] logfile group
  alter database [noarchivelogarchivelog]
  alter database rename file
  create tablespace
  alter tablespace [addrename] datafile
  alter tablespace [read writeread only]
  drop tablespace
  · If you are performing a closed backup, the target database must not be open. If you are using a recovery catalog,the recovery catalog database must be open.
  SBT_tape (System Backup to Tape)
  
  · select * from v$recover_file The CHANGE# column returns the SCN (system change number) from where recovery must start.
  · Recovery manager uses the recovery catalog or target database control file to determine which full or incremental backups or image copies it will use during restoration.
  
  · To restore to a previous point in time, you may have to use the backup of an older control file and use the restore control file option. The database should be in nomount state to restore the control file.
  
  · Incomplete recovery is typically used when a complete recovery operation fails.
  
  
  Time-based recovery
  Cancel-based recovery
  Change-based recovery
  
  · A retention policy specifies when RMAN should consider the backups and copies it creates as obsolete.
  · The recovery catalog is a schema that is created in a separate database. it contains the RMAN metadata obtained from the target database control file. RMAN propagates information about the database structure, archived redo logs, backup sets, and datafile copies into the recovery catalog from the control file of the target database.
  · You should use a catalog when you have multiple target databases to manage. Recovery Catalog contains information on: datafile and archived redo log file backup sets and backup pieces. datafile copies. Archived redo log files. The physical structure of the target database. Persistent RMAN configuration settings. Stored job scripts.
  · These objects share the same namespace:
  Tables,
  Views,
  Sequences, Private synonyms,
  Procedures,
  Functions, packages, materialized views, user defined types.
  · The database has separate namespaces for each of the following:
  Indexes, contraints, clusters, database triggers, private database
  Links, dimensions, roles, public synonyms, public database
  Links, tablespaces, profiles, PFILES.
  · A shared server process executes each request and places the completed request in the dispatcher’s response queue.
  · An Oracle 10g database supports four levels (statement, privilege, object, and fine-grained access) of auditing.
  · The SYS and SYSTEM accounts are the data dictionary owner and an administrative account, respectively SYSMAN and DBSNMP are used by EM.
  · Oracle offers three authentication methods for your user accounts: password authentication (the most common) external authentication, and global authentication.
  · Automatic shared memory management : Use MMAN(Memory Manager) Process
  · In the shared serve configuration user session data and cursor state are stored in large pool (inside SGA) and stack space are stored outside SGA ( PGA)
  · In the dedicated server configuration PGA contains user session, cursor state and stack space.
  · locale manage & dictionary manage
  · NLS_LANG=_.
  · alter session set nls_language=english
  · Parameter files(PFILEs) and server parameter files(SPFILEs)
  · private synonym , public synonym
  · tablespace, segment, extent, block
  · Schema Objects: tables, triggers, constraints, indexes, views, sequences, stored
  Program units, synonyms, user-defined data types, database links
  · SBT_tape (System Backup to Tape)

I will Keep on adding to the series like Part 1 part 2 and son on of the Exam Notes series......

How To OCP?

I wanted to write last week only but i could not do that....I have a news to share FINALLY I am OCP Certified. I took the exam last week and passed it with 98% marks. I have something rather interesting to share about exam. Guys the exam format has been changed and now it is not a Multiple choice only. Now oracle has adopted Simulation and Multiple choice both for OCP as well. It was tough and moreover surprising. Thank god I used Lab (Personal at Home) during my preparation for exam.
Tips:
1.Set up your Lab
Install VMware
Install Linux ( I used RHEL4)
Install Oracle Database 10g ( R2)
2.Visit Oracle site and have the clear understanding of the Exam Objective for Both the papers
3.Give Sufficient Time to study and Lab (Like in my case i took around 7 month of study and practical)
4.Keep the Oracle Documentation handy. Apart from that i followed Books/materials
1. Oracle database administration 10g workshop -1 ( PDF )
2. Oracle database administration 10g workshop -2 ( PDF )
3. vct- Oracle 10g (Video/CBT)
don’t ask me I got it from where they are copyrighted materials so ...............
5.Try to prepare by doing it on the Lab as much as possible. Simulate all the scenario for recovery (at least what is possible in Lab based environment and under Exam objective)
Tip: Keep a clone of the VMW configuration file so that you can do R & D without any fear of losing installation/configuration. Because with the help of clone/or copy of your VMW configuration file you can build the setup again in few minutes
6. OCP Exam Format has been changed. And now it is Simulation and multiple choice based. I was the one who got tested first on the new format and passed.
7. OCA Format is still the same ( Only Multiple choice ) but they plan to increase the requirement of OCA (going forward it will be 2 paper) also by DEC-2008 so save money get certified before DEC-2008 else you have to spend more money to become OCA.

Thanks i hope the information will help someone ...i will keep on adding things related to certification going forward...
For More Information on oracle certification:
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=143

Oracle Licensing

Good One.
Processor/CPU licensing You pay per processor (CPU) you run the Oracle software on. Intel Hyperthreading technology that makes one core look like two counts as 2 processors for this purpose (see this document)
Multi-core processors are priced as (number of cores)*(multi-core factor) processors, where the multi-core factor is:
0.25 for SUN's UltraSparc T1 processors 0.50 for Intel and AMD processors 0.75 for all other multi-core processors 1.00 for single-core processors .....................................read more below
http://www.orafaq.com/wiki/Oracle_Licensing#Processor.2FCPU_licensing
http://www.itmanagementresource.com/how-oracle-licensing-differs

Oracle:ASM

ASM is self managing capability within oracle kernel
ASM has a special purpose instance called ASM Instance.
An ASM instance never actually opens a database; instead, it is responsible for storing and processing the metadata that is required to make available the files stored within the ASM storage system to non-ASM Oracle databases.
It is used to manage disk group activity.
Take 64 MB from SGA
ASM Instance creates & manage Extant map
ASM Instance must be started before database instance can access data files in disk group
All ASM management command are handled by ASM instance and not by database instance
Apart from other background process like ckpt,pmon,smon,dbwr etc. Each ASM Instance should have two new background process
RBAL----Rebalncer Process----Coordinate rebalance activity
ARBn----Actual Rebalancer Process---actually does the rebalance actiivty at extent level
ASM is only specific to oracle files
Redundancy: External, Normal and High
Since ASM handles the mirroring of data, there is no need to purchase a third-party Logical Volume Manager (LVM). Mirroring is applied on a file-by-file basis, rather than on a per-volume basis, so the same disk group may contain a combination of files protected by mirroring (or perhaps not even mirrored at all, if mirroring is not required for some files).
ASM is responsible for providing equal distribution of I/O loads across all available disk storage resources, so there is a measurable performance improvement. For example, ASM will split a data file into its component extents and then spread those extents evenly across all defined disks that ASM is managing; those extents are then tracked via an indexing technique.
when ASM storage capacity changes, ASM doesn't need to re-stripe all data - it just moves enough data in proportion to the amount of added (or reduced!) storage, thus redistributing the data file’s extents evenly and keeping a balanced data load across all disks. Moreover, since ASM can accomplish the rebalancing act while the database is active, there is virtually no impact on database availability. I can also instruct ASM to increase the speed of a space rebalancing operation if I know that sufficient system resources are available, or I can tell ASM to reduce the speed of the rebalancing operation to limit the impact on the ASM I/O subsystem.

ASM Power takes parameter from 1-11 where 1 is being default and has less effect on I/O while rebalancing activity is performed.
Every database instance that uses ASM for file storage will also need two new processes. The Rebalancer background process (RBAL) handles global opens of all ASM disks in the ASM Disk Groups, while the ASM Bridge process (ASMB) connects as a foreground process into the ASM instance when the regular database instance starts. ASMB facilitates communication between the ASM instance and the regular database; including handling physical file changes like data file creation and deletion.
ASMB exchanges messages between both servers for statistics update and instance health validation
ALTER SYSTEM ENABLE RESTRICTED SESSION; command to prevent database instances from accessing ASM disk groups while maintenance is being performed against the ASM instance or any ASM disk groups.
Issuing the ALTER SYSTEM DISABLE RESTRICTED SESSION; command re-enables access to the ASM instance.
ASM use a storage Hierarchy:
A smallest unit is AU ( Allocation unit) Each allocation unit is 1 MB in size. So all ASM disk is partiaitoned in 1MB block and a block never spans a AU.
ASM Files: Next in the hierarchy are the actual ASM files themselves. With the exception of trace files and operating system files, ASM can store every type of Oracle database file, including control files, server parameter files (SPFILEs), data files, temp files, online redo logs, archived redo logs, flashback logs, DataPump dump sets. In addition, ASM can store all types of files managed by Recovery Manager (RMAN), including backup sets, archived redo log image copies, datafile image copies, and auto backups.
ASM Disk Group

References and Additional Reading
B10739-01 Oracle Database Administrator's Guide, Chapter 12
B10743-01 Oracle Database Concepts, Chapter 14
B10755-01 Oracle Database Reference
B10759-01 Oracle Database SQL Reference

Oracle Certification

Its been long time ..actually i was busy with my OCP exam preparation. finally today i have completed first part of my oracle certification. I PASSED and became OCA today.It was really hard and challenging. Now i am willing to take up another Oracle 10g Administration II to finally become OCP DBA.

Today i was so nervous as i really got conscious that i am going to face exam and my knowledge will be tested. i must say it was really a good set of question that came for me and it kept me busy and i was crossing my finger before clicking END Button to finish my exam as i was not very sure about few of my answers any way "Everything looks god when end is good" and that is me very happy with my self i really worked hard. Now i want to contribute whatever i can to OCP aspirants.

My Plans:
I will share my study notes on this blog ( It can be used to refresh almost all important concept before getting in to the exam)
As everybody knows we have to accept some oracle terms and condition before taking the exam " No disclosure" kind of . so i will stick to that and will share things keeping in mind rule and regulation.
I plan to share Different recovery Scenario series in my blogs that will be tested and simulated on my personal test lab.

OK guys good night ..let me enjoy the evening with mug of beer.

Good Night

Basics: ORACLE vs. SQL Server

An Oracle instance:
Is a means to access an Oracle database Always opens one and only one database
Consists of:
Oracle database processes and buffers (instance).
SYSTEM tablespace containing one centralized system catalog, which is made up of one or more datafiles.
Other tablespaces as defined by the DBA (optional), each made up of one or more datafiles.
Two or more online Redo Logs.
Archived Redo Logs (optional).
Miscellaneous other files (control file, Init.ora, config.ora, etc.).
An SQL Server Instance:
Is a Means to Acess an SQL Server database
A SQL Server installation (an instance) can support multiple databases.
There can be multiple instances of SQL Server on a single computer.
Each instance of SQL Server can have multiple databases.
SQL Server also installs the following databases by default:
• The model database is a template for all newly created user databases.
• The tempdb database is similar to an Oracle temporary tablespace in that it is used for temporary working storage and sort operations. Unlike the Oracle temporary tablespace, SQL Server users can create temporary tables that are automatically dropped when the user logs off. • The msdb database supports the SQL Server Agent and its scheduled jobs, alerts, and replication information.
• The pubs and Northwind databases are provided as sample databases for training.

Each Oracle database runs on one centralized system catalog, or data dictionary, which resides in the SYSTEM tablespace.
Each SQL Server 2000 database maintains its own system catalog, which contains information about:
• Database objects (tables, indexes, stored procedures, views, triggers, and so on).
• Constraints.
• Users and permissions.
• User-defined data types.
• Replication definitions.
• Files used by the database.
SQL Server also contains a centralized system catalog in the master database, which contains system catalogs as well as some information about the individual databases:
• Database names and the primary file location for each database.
• SQL Server login accounts.
• System messages.
• Database configuration values.
• Remote and/or linked servers.
• Current activity information.
• System stored procedures.
Similar to the SYSTEM tablespace in Oracle, the SQL Server master database must be available to access any other database. It is important to protect against failures by backing up the master database after any significant changes are made in the database. Database administrators can also mirror the files that make up the master database.

The Oracle RDBMS is comprised of tablespaces.which in turn are comprised of datafiles. Tablespace datafiles are formatted into internal units termed blocks. The block size is set by the DBA when the Oracle database is first created.When an object is created in an Oracle tablespace, the user can specify its space in units called extents (initial extent, next extent, min extents, and max extents). If an extent size is not defined explicitly, a default extent is created. An Oracle extent varies in size and must contain a chain of at least five contiguous blocks.

SQL Server RDBMS uses filegroup at database level to contains table and indexes.Filegroup is logical in nature and contains number of datafiles
SQL Server formats files into logical units called pages. The page size is fixed at 8192 bytes (8 KB). Pages are organized into extents that are fixed in size at 8 contiguous pages (64 KB). When a table or index is created in a SQL Server database, it is automatically allocated one page within an extent.
SQL Server RDBMS perform automatic recovery each time it starts. It first checks to see all the databases entry in master databases and creatd a threads for each of the databases for recovery.Every database has a transaction log file used during recovery.Each database has its own transaction log that records all changes to the database and is shared by all users of that database.The T-log files are looked during recovery to see the uncommited transaction to rollback and comitted ransaction that was not written to disk are rollforward.
SQL Server transaction log = Oracle rollback segment + Oracle online redo log.
SQL Server has a automatic checkpoint mechanism that ensure any committed transaction called dirty pages since the last checkpoint are written to the disk.This mechanism ensure faster recovery of the database in case of system failure like power outages.
Oracle RDBMS Perform autometic recovery each time it starts. It looks for if tablespace data are same as online redolog data if not it recover it from online redo logfiles ( Roll forward), and then removes any uncommitted transactions that are found in the rollback segments (roll back).
If Oracle cannot obtain the information it requires from the online redo log files, it consults the archived redo log files.
Oracle uses TNS protocol ( Transparent Network Substrate (TNS) data stream )
SQL Server uses TDS protocol ( Tabuler data Stream )

I will keep the series going and will write/share more on this. I love different RDBMS like Oracle/Mysql etc apart from being a SQL Server DBA. My Blog will mainly contain information revolving around these 3 islands in the ocean of RDBMS.