How to recreate DUAL table in Oracle after deleting it ?

We should never drop a dual table, it will have serious impact on the database functionality .If we have dropped the dual table from sys user and startup the database next time it will be not open. To open the database we have follow the below steps .Therefore it is highly recommended not to delete the dual table .

SQL> conn /as sysdba
SQL> drop table dual;
 Table dropped.

SQL> desc dba_objects

Name                                 Null?                Type

---------------------        --------          ---------------

OWNER                                                 VARCHAR2(30)

OBJECT_NAME                                        VARCHAR2(128)

SUBOBJECT_NAME                                   VARCHAR2(30)

OBJECT_ID                                             NUMBER

DATA_OBJECT_ID                                    NUMBER

OBJECT_TYPE                                         VARCHAR2(19)

CREATED                                                DATE

LAST_DDL_TIME                                      DATE

TIMESTAMP                                            VARCHAR2(19)

STATUS                                                 VARCHAR2(7)

TEMPORARY                                            VARCHAR2(1)

GENERATED                                            VARCHAR2(1)

SECONDARY                                            VARCHAR2(1)

NAMESPACE                                            NUMBER

EDITION_NAME                                        VARCHAR2(30)

SQL> select sysdate from dual ;
select sysdate from dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms

Check the alertlog file

Errors in file d:\oracle\diag\rdbms\god\god\trace\god_j000_26455.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms
ORA-12012: error on auto execute of job 21
ORA-01775: looping chain of synonyms
ORA-4063  :encountered when generating server alert SMG-3503 

SQL> shut immediate;                               
SQL> startup;

ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 113249576 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

Checking the alert logfile

 Errors in file d:\oracle\diag\rdbms\god\god\trace\god_ora_26538.trc:
ORA-01775: looping chain of synonyms
Error 1775 happened during db open, shutting down database
USER: terminating instance due to error 1775
Instance terminated by USER, pid = 26538

ORA-1092 signalled during: ALTER DATABASE OPEN...

Now we will create pfile  and add the below parameter and startup database.Create a pfile from spfile and add the below line in the pfile .

replication_dependency_tracking= false 

Now start the database using pfile;
 SQL> startup pfile='c:\initgod.ora'; 

Now database is open but it throws the error while creating the dummt table .

SQL>create table "SYS"."DUAL" ("DUMMY"  VARCHAR2(1)) ;
 CREATE TABLE "SYS"."DUAL" ( "DUMMY" VARCHAR2(1))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms

Again Check the Alertlog file

 Errors in file d:\oracle\diag\rdbms\god\god\trace\god_j000_26583.trc:
ORA-12012: error on auto execute of job 21
ORA-01775: looping chain of synonyms

 SQL> shut immediate
 Now open the database using upgrade mode

SQL> startup upgrade pfile='c:\initgod.ora';
 ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 113249576 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.

SQL> create table  "SYS"."DUAL" ("DUMMY"    VARCHAR2(1)) ;
Table created.

SQL> Insert Into Dual Values ('X') ;
 1 row created.

SQL> commit;
 Commit complete.

SQL> Grant Select On Dual To Public;
 Grant succeeded.

SQL> column OBJECT_NAME format a20;
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED from dba_objects where object_name='DUAL';

OWNER         OBJECT_NAME     OBJECT_TYPE       CREATED     

---------    ---------------    ---------------    ------------  

SYS                 DUAL               TABLE                02-APR-10

PUBLIC              DUAL               SYNONYM          02-APR-10

Hence, Dual table is recreated.

Enjoy          J J J

What is Dual Table ?

Dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X.  The reason to use the DUAL table isn't just portability, but optimization.  The owner of dual is SYS but dual can be accessed by every user.Dual is useful because it always exists, and has a single row, which is handy for select statements with constant expressions.
Example:


SQL> select 1+1  from dual ;
     1+1
----------
       2
SQL> select sysdate from dual;

SYSDATE
---------
3-MAR-11


DUAL was originally a table and the database engine would perform disk IO on the table when selecting from DUAL. This disk IO was usually logical IO (not involving physical disk access) as the disk blocks were usually already cached in memory. This resulted in a large amount of logical IO against the DUAL table.Later versions of the Oracle database have been optimized and the database no longer performs physical or logical IO on the DUAL table even though the DUAL table still actually exists.

If we drop a dual table then it will have serious impact on the database functionality. so we should never drop dual table. TOM KYTE explained about this in more details .Click the below link 

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388

Also Check this link for more about dual table :
http://radiofreetooting.blogspot.com/2006/12/why-dual.html

Enjoy          J J J

Displaying Oracle background processes on Windows

We knows how to use the "ps -ef" command in UNIX to see Oracle background processes but we are not able to see the background processes in Windows ? When we view Oracle processes on Windows, all we see is one background process called oracle.exe.?

This is because in Windows, the "thread" model is used, and Oracle dispatches his own background tasks within the domain of the single process, oracle.exe. Hence, we cannot see any background processes from the Windows OS (but we can see listener process and parallel query slaves).To see details about the background processes in Windows,we need to run a dictionary query against the v$bgprocess view to see what the background processes are doing in Window .

The below sql statement is useful to view the background process in windows .

SQL> select a.sid,a.serial#, a.program, p.pid, p.spid, a.osuser, b.name, b.DESCRIPTION, p.PGA_USED_MEM   from v$session a,v$process p, v$bgprocess b where a.paddr=b.paddr 
      and    a.paddr=p.addr and p.background=1;
Output  : 

Enjoy          J J J

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