Friday, August 8, 2008

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......

Friday, August 1, 2008

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