New Background Process in 11G

This articles will describe the new background processes that has been introduced in oracle 11GR1.The background process in oracle 11.1g  are

ora_pmon_11G
ora_vktm_11G
ora_diag_11G
ora_dbrm_11G
ora_psp0_11G
ora_dia0_11G
ora_mman_11G
ora_dbw0_11G
ora_lgwr_11G
ora_ckpt_11G
ora_smon_11G
ora_reco_11G
ora_mmon_11G
ora_mmnl_11G
ora_d000_11G
ora_s000_11G
ora_smco_11G
ora_fbda_11G
ora_qmnc_11G
ora_q000_11G
ora_q001_11G
ora_cjq0_11G
ora_w000_11G
ora_j000_11G

I compared the 11g processes above with a 10g instance and found that 6 new background processes had been introduced in 11g which are:

ora_dbrm_11G         DB resource manager
ora_dia0_11G          Diagnosability process 0
ora_fbda_11G          Flashback data archiver process
ora_vktm_11G         Virtual Timekeeper
ora_w000_11G        Space Management Co-ordination process
ora_smc0_11G         Space Manager process

However while googling the net, I found that (212-157+1=) 56 new processes were added. I am pasting those here for reference, just in case the original link disappears . In Oracle 10.2 there were 157 background parameters; in Oracle 11.1 there are 212 background processes . The following table shows the 56 background processess that were added in Oracle 11.1

Name          Description
---------    -----------------
ACMS Atomic Controlfile to Memory Server
DBRM Resource Manager Process
DIA0 Diagnosibility Process 0
DIA1 Diagnosibility Process 1
DIA2 Diagnosibility Process 2
DIA3 Diagnosibility Process 3
DIA4 Diagnosibility Process 4
DIA5 Diagnosibility Process 5
DIA6 Diagnosibility Process 6
DIA7 Diagnosibility Process 7
DIA8 Diagnosibility Process 8
DIA9 Diagnosibility Process 9
DSKM slave DiSKMon process
EMNC EMON Coordinator
FBDA Flashback Data Archiver Process
FSFP Data Guard Broker FSF0 Pinger
GTX0 Global Transaction Process 0
GTX1 Global Transaction Process 1
GTX2 Global Transaction Process 2
GTX3 Global Transaction Process 3
GTX4 Global Transaction Process 4
GTX5 Global Transaction Process 5
GTX6 Global Transaction Process 6
GTX7 Global Transaction Process 7
GTX8 Global Transaction Process 8
GTX9 Global Transaction Process 9
GTXa Global Transaction Process 10
GTXb Global Transaction Process 11
GTXc Global Transaction Process 12
GTXd Global Transaction Process 13
GTXe Global Transaction Process 14
GTXf Global Transaction Process 15
GTXg Global Transaction Process 16
GTXh Global Transaction Process 17
GTXi Global Transaction Process 18
GTXj Global Transaction Process 19
KATE Konductor of ASM Temporary Errands
MARK mark AU for resysc koordinator
OFSC OFS CSS
PING interconnect latency measurement
RCBG Result Cache: Background
RMS0 rac management server
RSMN Remote Slave Monitor
SMC0 Space Manager Process
VBG0 Volume BG 0
VBG1 Volume BG 1
VBG2 Volume BG 2
VBG3 Volume BG 3
VBG4 Volume BG 4
VBG5 Volume BG 5
VBG6 Volume BG 6
VBG7 Volume BG 7
VBG8 Volume BG 8
VBG9 Volume BG 9
VDBG Volume Driver BG
VKTM Virtual Keeper of TiMe process.

The following table shows the one background process that was removed  in Oracle 11.1G

Name  Description
--------     -----------------
EMN0 Event Monitor Process 0



Enjoy   J J J

Database Monitoring and Checklist

What is monitoring ?

The Monitering of predefined events that generates a message or warning when a certain  threshold has been exceeded.This is done in an effort to ensure that an issue doesn't become a problem.The database monitering is required for the following reason : 

• Supporting production !!!
• Keeping an eye on development, i.e. disabled PKs | FKs.
• Database performance
• In Support of an SLA (service level agreement)

Here are steps which are required to moniter. 

1.)  Daily Procedures

A.)  Verify all instances are up :  Make sure the database is available. Log into each instance and run daily reports or test scripts. Use the below queries to check where database is up or not.
SQL> select name,open_mode from v$database ;

B.)  Look for any new alert log entries : The alert log file is a best DBA's friend and could be a true lifesaver. Go to the background dump destination or diag (in oracle 11g) and check alert log file . If any ORA- errors have appeared since the previous time we looked note them investigate it and take the steps to resolve the errors .

C.) Verify DBSNMP is running : Log on to each managed machine to check for the 'dbsnmp' process.

For Unix: at the command line, type ps –ef | grep dbsnmp. There should be two dbsnmp processes running. If not, restart DBSNMP. (Some sites have this disabled on purpose; if this is the case, remove this item from our list, or change it to "verify that DBSNMP is NOT running".)

D.) Verify success of database backup : Check the physical location of the database backup and ensure that the database backup is successful .

E.) Verify enough resources for acceptable performance : Check the space status of the tablespace i.e, free spaces and database size.click the below monitoring link to check the tablespaces spaces .

F.) Check the instance status of the database : Check the memory component ie, buffer cache ratio, library hits , shared pool and physical reads and logical reads . To check all run the below monitoring scripts .

Monitoring scripts 

G.) Processes to review contention for CPU, memory, network or disk resources :  To check CPU utilization, go to x:\web\phase2\default.htm =>system metrics=>CPU utilization page. 400 is the maximum CPU utilization because there are 4 CPUs on phxdev and phxprd machine. We need to investigate if CPU utilization keeps above 350 for a while.

II. Nightly Procedures

Most production databases (and many development and test databases) will benefit from having certain nightly batch processes run.

A.)  Collect volumetric data : This example collects table row counts. This can easily be extended to other objects such as indexes, and other data such as average row sizes . Analyze schemas and collect data as in case of oracle 11g the optimizer collect the analyse the whole database and collect the fresh statistics .In case of Oracle 9i , collect fresh statistics by running the below command :
SQL> exec dbms_stats.gather_schemas_stats ;
SQL> exec dbms_stats.gather_schemas_stats('SCOTT') ;

III. Weekly Procedures

A.) Check the invalids objects :  Check the invalid objects and remove them from the database .The the utlrp.sql script to remove the invalid objects . This scripts is present in  "$ORACLE_HOME\rdbms\admin" folder .

B.) Check the Growth of the tablespace in the database : Check the growth of the each tablespace and in the database. Run the scripts to check  the  growth of tablespace . Tablespace Growth Scripts

IV. Monthly Procedures

A.)  Look for Harmful Growth Rates : Review changes in segment growth when compared to previous reports to identify segments with a harmful growth rate.

B.)  Review Tuning Opportunities : Review common Oracle tuning points such as cache hit ratio, latch contention, and other points dealing with memory management. Compare with past reports to identify harmful trends or determine impact of recent tuning adjustments.

C.) Look for I/O Contention : Review database file activity. Compare to past output to identify trends that could lead to possible contention.

D.) Review Fragmentation : Investigate fragmentation (e.g. row chaining, etc.).

E.) Perform Tuning and Maintenance : Make the adjustments necessary to avoid contention for system resources. This may include scheduled down time or request for additional resources.

Enjoy    J J J

What is Data Dictionary?

The  Data Dictionary   is  a  repository   of  database  metadata (data  about  data), about  all the  information  inside  the database. This  repository  is owned  by  "sys",  and  is  stored principally  in  the "system"  tablespace, though  some  components  are stored in the "sysaux"  tablespace (in Oracle 10g and 11g). The  Oracle  user  "SYS"  stores  all base tables and user-accessible views of the data dictionary. No Oracle user should ever alter (update,delete or insert)  any  rows  or  schema  objects  contained  in  the  SYS  schema, because such activity can compromise data integrity. 

A data dictionary contains the following contents  :

• The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
• How much space has been allocated for, and is currently used by, the schema objects
• Default values for columns
• Integrity constraint information
• The names of Oracle users
• Privileges and roles each user has been granted
• Auditing information, such as who has accessed or updated various schema objects
• Other general database information.

The data dictionary consists of the following :

1.) Base Table : The  underlying  tables  that  store  information about the associated database. Only 

Oracle  should write to and read these  tables. Users  rarely access them directly because they  are  normalized, and most of the data is stored in a cryptic format.

2.) User-Accessible Views :  The  views that summarize and  display  the  information  stored  in  the base tables  of  the  data dictionary. These  views  decode  the  base table data into  useful information, such as user or table names, using joins and where clauses to simplify the information. Most users are given access to the views rather than the base tables.

The  user-accessible views  come in two primary forms :

1.) The static performance views : The static views are dba_xx , all_xx  , user_xx  views  (eg. dba_users, user_tables, all_tables). These views are used to manage database structures.

2.) The v$dynamic performance views : The dynamics views are v$xx views (eg v$process).These views are used to monitor real time database statistics.

Some  of  these tables  are inside  of  the Oracle  kernel, so  we  would  never  work  directly  with them unless  we  are  working  for Oracle support  or  performing  a  disaster  recovery scenario . But instead we can  access  to  the  views  in  order  to know  the “information about the information”.  For example,  a possible  usage  of  this  data  dictionary  would  be  to  know all  the tables  owned  by  a  single user, or  the list  or  relationships between  all  the  tables  of  the  database.

The  main view of the data dictionary is the view DICT (or DICTIONARY):  
SQL> desc dict
Name                           Null?                Type
---------------               --------         -----------------
TABLE_NAME                                   VARCHAR2(30)
COMMENTS                                      VARCHAR2(4000)

Through  the  DICT view, we can access  to all the data dictionary views that could provide us the information that  we  need. For  example, if  we are looking  for  information  related  to  link , but we  don’t know  where  to  look  for, then query the DICT view:

SQL> select  table_name  from  dict  where  table_name  like   '%DATAPUMP%'  ; 
TABLE_NAME
----------------------------
DBA_DATAPUMP_JOBS

DBA_DATAPUMP_SESSIONS
USER_DATAPUMP_JOBS
GV$DATAPUMP_JOB
GV$DATAPUMP_SESSION
V$DATAPUMP_JOB
V$DATAPUMP_SESSION
7 rows selected.


Now, we  have just to query  one of  these  views  to  find  the  data  we are  looking for  .GV$ views  are very  useful  when we  are working  with  RAC,  and V$  views  are instance  related. Remember  that  the data  dictionary  provides  critical  information  of  the database, and  it should  be  restricted  to users. However,  if  a  user  really  needs  to query  the  data dictionary,  we  can  grant  the  following  privileges .

SQL> grant select_catalog_role to username ;

As  a DBA, we  can see why the data dictionary is so important. Since we can’t possibly remember everything  about our database (like the names of all the tables and columns) Oracle remembers this for us. All  we need is to learn how to find that information .

Enjoy     :-) 

Database Objects

Here are some scripts related to Database Objects :

DATABASE CLUSTER NOTES:

  Owner - Owner of the table/cluster

  Tablespace - Name of the tablespace containing the cluster

  Cluster Name - Name of the cluster

  Table Name - Clustered table name

  Table Column - Key column in the table

  Cluster Column - Key column in the cluster

SQL> select a.OWNER,TABLESPACE_NAME, a.CLUSTER_NAME,

           TABLE_NAME,TAB_COLUMN_NAME,CLU_COLUMN_NAME

           from    dba_clusters a, dba_clu_columns b

           where   a.CLUSTER_NAME = b.CLUSTER_NAME

           order   by a.OWNER,TABLESPACE_NAME,a.CLUSTER_NAME,TABLE_NAME ;

DATABASE JOB NOTES :

  Log User - USER who was logged in when the job was submitted.

  Schema - Default schema used to parse the job. For example, if the SCHEMA_USER is SCOTT and you submit the procedure HIRE_EMP as a job, Oracle looks for SCOTT.HIRE_EMP.

  Job# - Identifier of job. Neither import/export nor repeated executions change it.

  Interval - A date function, evaluated at the start of execution, becomes next NEXT_DATE.

  Next Execution - Date/time that this job will next be executed.

  Broken - If Y, no attempt is made to run this job. See DBMS_JOBQ.BROKEN (JOB).

  What - Body of the anonymous PL/SQL block that this job executes.

SQL> select LOG_USER, SCHEMA_USER schema,JOB job#, INTERVAL,

           to_char(NEXT_DATE,'MM/DD/YYYY HH24:MI:SS') next_execution,

           BROKEN, substr(WHAT,1,100) what

            from    dba_jobs order   by LOG_USER

DATABASE LINK NOTES:

  Owner - Owner of the database link

  DBLink - Name of the database link

  Username - Name of user to log in as

  Host - SQL*Net string for connect

  Created - Creation time of the database link

SQL> select OWNER,DB_LINK,USERNAME, HOST,

           to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created

           from    dba_db_links  order   by OWNER,DB_LINK ; 

DATABASE PROCEDURE/PACKAGE NOTES : 

  Owner - Owner of the object

  Name - Name of the object

  Type - Type of object

SQL> select  OWNER, NAME, TYPE from  dba_source

           group   by OWNER,NAME,TYPE

           order   by OWNER,NAME,TYPE ; 

DATABASE SEQUENCE NOTES : 

  Owner - Owner of the sequence

  Name - Name of the sequence

  MinValue - Minimum value of the sequence

  MaxValue - Maximum value of the sequence

  Increment - Value by which sequence is incremented

  Cycle - Does sequence wrap around on reaching limit?

  Order - Are sequence numbers generated in order?

  Cache Size - Number of sequence numbers to cache

  Last Number - Last sequence number written to disk

SQL> select SEQUENCE_OWNER,SEQUENCE_NAME, MIN_VALUE,

        MAX_VALUE,INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG,

        CACHE_SIZE, LAST_NUMBER  from dba_sequences

        where   SEQUENCE_OWNER not in ('SYS','SYSTEM')

        order   by SEQUENCE_OWNER,SEQUENCE_NAME ;  

DATABASE SNAPSHOT NOTES : 

  Owner - Owner of the snapshot

  Name - The view used by users and applications for viewing the snapshot

  Table Name - Table the snapshot is stored in, has an extra column for the master rowid

  Master View - View of the master table, owned by the snapshot owner, used for refreshes

  Master Owner - Owner of the master table

  Master - Name of the master table of which this snapshot is a copy

  Master Link - Database link name to the master site

  Can Use Log - If NO, this snapshot is complex and will never use a log

  Updatable - If NO, the snapshot is read only

  Last Refresh - SYSDATE from the master site at the time of the last refresh

  Error - The number of failed automatic refreshes since last successful refresh

  Type - The type of refresh (complete, fast, force) for all automatic refreshes

  Next Refresh - The date function used to compute next refresh dates

  Refresh Group - GROUP All snapshots in a given refresh group get refreshed in the same transaction

SQL> select  OWNER,NAME, TABLE_NAME, MASTER_VIEW, MASTER_OWNER,

        MASTER, MASTER_LINK, CAN_USE_LOG, UPDATABLE, LAST_REFRESH,

        ERROR,  TYPE, NEXT, REFRESH_GROUP  from    dba_snapshots

          order   by OWNER,NAME ; 

DATABASE SYNONYM NOTES :  

  Owner - Owner of the synonym

  Name - Name of the synonym

  Table Owner - Owner of the table

  Table Name - Name of the table

  DB Link - Name of the database link

SQL> select  OWNER, SYNONYM_NAME, TABLE_OWNER,

            TABLE_NAME,DB_LINK from    dba_synonyms

            where   owner not in ('SYS','SYSTEM','PUBLIC','DBSNMP')

           order   by OWNER,SYNONYM_NAME ;    

DATABASE TRIGGER NOTES  :

  Table Owner - Owner of the table

  Table Name - Name of the table

  Trigger Name - Name of the trigger

  Trigger Name - When the trigger fires (BEFORE EACH ROW, AFTER EACH ROW, BEFORE STATEMENT, AFTER STATEMENT)

  Triggering Event - Statement that fires the trigger (INSERT, UPDATE, DELETE)

  Status - Whether the trigger is enabled (ENABLED or DISABLED)

SQL> select TABLE_OWNER, TABLE_NAME, TRIGGER_NAME, TRIGGER_TYPE,

           TRIGGERING_EVENT, STATUS from    dba_triggers

          order   by TABLE_NAME, TRIGGER_NAME; 

DATABASE VIEW NOTES :

  Owner - Owner of the view

  View Name - Name of the view

  Created - Date/time view was created

  Status - Status of the view

SQL> select OWNER, OBJECT_NAME,to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created,

        status from    dba_objects  where   OWNER not in ('SYS','SYSTEM') and              OBJECT_TYPE='VIEW'  order   by OWNER,OBJECT_NAME ; 

Enjoy   :-) 

Remote_login_passwordfile Parameter and it's use

Remote_login_passwordfile   specifies whether Oracle checks for a password file . When we grant "sysdba" or  "sysoper" privileges to a user, that  user's name  and  privilege  information  are  added to the password file. A user's name remains in the password file only as long as that user has at least one of these two privileges. If we revoke both of these privileges, Oracle Database removes the user from the password file .

We have two options for Super User "SYS" password authentication .
1.) Operating System authentication

2.) Password file authentication

Password file authentication : When we use password file authentication then we have to create password file for  "SYS"  user or  the  user  having  "sysdba"  privileges . We can create the password file as below : 

C:\>orapwd  file=$ORACLE_HOME\database\orapwd.ora  password=sys_password  entries=n force=y

Note:

=> Entries= n  specifies the "maximum number of distinct DBAs and OPERs (opt)" can login into database .

=> Force=y    specifies if the password file exists then it overwrites the existing password file .

 There are three value for remote_login_passwordfile parameters : 

1.) EXCLUSIVE:  This is default value. The password file can be used by only one database and the password file can contain names other than sys.We can use password file for authentication and connect to remote machine with sys user.Only an EXCLUSIVE file can contain the names of users other than SYS. Using an "exclusive" password file lets us allow to grant sysdba and sysoper system privileges to individual users and have them connect as themselves.

2.) NONE: If this parameter set "NONE" means passwordfile doesn't exists. That is, no privileged connections are allowed over non-secure connections. If remote_login_password is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting  remote_login_password  to NONE.

3.) SHARED: A SHARED password file can be used by multiple databases running on the same server. However, only user recognized by a SHARED password file is sys .We cannot add users to a SHARED password file. All users needing  sysdba  or sysoper system privileges must connect using the same name, SYS, and password. This option is useful if we have a single DBA administering multiple databases.A shared password file cannot be modified. This means that we cannot add users to a shared password file. Any attempt to do so or to change the password of SYS or other users with the sysdba or sysoper privileges generates an error.

All users needing  sysdba or sysoper  system privileges must be added to the password file when remote_login_passwordfile is set to  exclusive . After all users are added, we can change remote_login_passwordfile  to shared, and then share the file.This option is useful if we are administering multiple databases or a RAC database. If  remote_login_passwordfile is set to  exclusive  or shared and the password file is missing, this is equivalent to setting  remote_login_passwordfile  to None .

If we ever receive the file full error i.e, ORA-1996  while granting  "sysdba"  or  "sysoper" system privileges to a user, we must create a larger password file and regrant the privileges to the users. The below steps will replace the current passwordfile with new larger password file .

1.)  Identify the users who have sysdba or  sysoper  privileges by querying the  v$pwfile_users views .
2.)  Delete the existing password file.
3.)  Creating a new password file using the ORAPWD utility and ensure that the ENTRIES parameter is set to a number larger then the previous password file .
4.) Connect with "sys" and grant the priveleges to user as quired from v$pwfile_users view .

Enjoy     :-)