EXP-00008: ORACLE error 942 encountered , ORA-00942

Once while visiting OTN , i found a user is facing the below error while performing exporting  

C:\> exp hr/hr tables=batch_job
 Export: Release 10.2.0.3.0 - Production on Mon Oct 10 14:34:35 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)
 About to export specified tables via Conventional Path ...
. . exporting table                 BATCH_JOB
EXP-00008 : ORACLE error 942 encountered
ORA-00942: table or view does not exist
Export terminated successfully with warnings.

While connecting connecting with "HR" user

C:\Documents and Settings\admin>sqlplus hr/hr
 SQL*Plus: Release 10.2.0.3.0 - Production on Mon Oct 10 14:34:58 2011
 Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
 SQL>  select count(*) from batch_job;
COUNT(*)
----------
37311

Solution :  There may various possible solution .The following are

1.) One of the possible solution is that the view "SYS.EXU9TYP" which is used by EXP is missing. So to solve this issue follow the below steps : 

I.) Ensure no application user connect to database.

II.) Run the following script
SQL>@?/rdbms/admin/catexp.sql
SQL>@?/rdbms/admin/catpatch.sql

III.) Re-try EXP.

2.) Secondly,it also seems that the problem is clearly with the version of exp i.e;
Exporting  with  version : 10.2.0.3
Connecting  to  database : 10.2.0.1

3.) Export proper home and perform export, even with exp still we can solve our problem but always recommended to use expdp/impdp instead of exp/imp in oracle 10g and higher version . 

4.) Finally to find out exactly what's wrong, check for a trace file in CORE_DUMP_DEST directory.


Enjoy    :-) 

How to Register Listener in the Database ?

The  listener is a separate process that runs on the database server computer. It  receives incoming client connection requests and manages the traffic of these requests to the database server. There are two methods by which a listener comes to know of a database instance. In Oracle terminology, this is referred  as “Registering with the Listener” .  The two methods are 

1.) Static Instance Registration
2.) Dynamic Instance Registration

First we will discuss about the Static Instance Listener  :

This is the very basic method to register listener .We can either add the entries in $ORACLE_HOME\NETWORK\ADMIN\listener.ora file or by using the GUI i.e, through Net Manager. The configuration inside the listener.ora file looks like : 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = noida)
      (ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
      (SID_NAME = noida)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = hyd)
      (ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
      (SID_NAME = hyd)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tech-199)(PORT = 1521))
  )

and when we check the registration , it shows the status of UNKNOWN :

C:\>lsnrctl
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2011 15:26:27
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                28-SEP-2011 15:03:39
Uptime                    7 days 0 hr. 22 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\neerajs\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         c:\app\neerajs\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))
Services Summary...
Service "hyd" has 1 instance(s).
  Instance "hyd", status UNKNOWN, has 1 handler(s) for this service...
Service "noida" has 1 instance(s).
  Instance "noida", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

The status is unknown because there is no mechanism to guarantee that the specified status even exists.Here the listener assumes that instance will be there whenever there will be any request. It donot have inforamtion about the status of the Current Instance. 

Now, we will check the Dynamic Instance Listener :

Dynamic Instance Registration :  This dynamic registration feature is called service registration. The registration is performed by the PMON process  an instance background process   of each database instance that has the necessary configuration in the database initialization parameter file. Dynamic service registration does not require any configuration in the listener.ora file.

Service registration offers the following benefits :

1.) Simplified configuration  :  Service registration reduces the need for the SID_LIST_listener_name parameter setting, which specifies information about the databases served by the listener, in the listener.ora file.

Note :  The SID_LIST_listener_name parameter is still required if we are using Oracle Enterprise Manager to manage the database.

2.) Connect-time failover  : Because the listener always knows the state of the instances, service registration facilitates automatic failover of the client connect request to a different instance if one instance is down.

In a static configuration model, a listener would start a dedicated server upon receiving a client request. The server would later find out that the instance is not up, causing an "Oracle not available" error message.

3.) Connection load balancing : Service registration enables the listener to forward client connect requests to the least loaded instance and dispatcher or dedicated server. Service registration balances the load across the service handlers and nodes.  To ensure service registration works properly, the initialization parameter file should contain the following parameters:

SERVICE_NAMES for the database service name
INSTANCE_NAME for the instance name
For example:
SERVICE_NAMES=noida.TECH-199
INSTANCE_NAME=noida

Let's have a Demo of Dynamic Listener.

The listener is quite capable of running without a listner.ora file at all. It will simply start and run with all default values.Here i have rename the listener.ora file and stop and start the listener and find that listener supports no services.Check the below: 

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))
The command completed successfully.

Now start the listener

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Log messages written to c:\app\neerajs\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                05-OCT-2011 16:21:30
Uptime                    0 days 0 hr. 0 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         c:\app\neerajs\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))
The listener supports no services
The command completed successfully

Here, we find that listener donot support any services.Since it doesnot found the listener.ora file ,and  if we try to connect to the Instance then it will  throws the error i.e, ORA-12514 :

C:\> tnsping noida
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2011 16:23:03
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\app\neerajs\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.100.0.112)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = noida)))
OK (40 msec)

Now, we try to connect with Instance "NOIDA"

C:\> sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 5 16:23:45 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Since the tnsping proves that our tnsnames.ora resolution is correct, but it throws the error while connecting to database because the listener doesnot knows anything about the services "NOIDA" . Let's start the instance and check again : 

C:\> set ORACLE_SID=noida
SQL> startup 
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size                  1373964 bytes
Variable Size             222300404 bytes
Database Buffers           33554432 bytes
Redo Buffers                6410240 bytes
Database mounted.
Database opened.

Now check the listener status again :

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                05-OCT-2011 16:21:30
Uptime                    0 days 0 hr. 19 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         c:\app\neerajs\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))
Services Summary...
Service "noida.TECH-199" has 1 instance(s).
  Instance "noida", status READY, has 1 handler(s) for this service...
Service "noidaXDB.TECH-199" has 1 instance(s).
  Instance "noida", status READY, has 1 handler(s) for this service...
Service "noida_DGB.TECH-199" has 1 instance(s).
  Instance "noida", status READY, has 1 handler(s) for this service...
The command completed successfully

Here we observe that once the instance is started , when we re-check the listener now knows of service “NOIDA”, with a status of READY . This obviously did not come from listener.ora as the file is renamed. Notice also that, unlike the static registration, this time the status is READY. The listener knows the instance is ready because the instance itself told the listener it was ready. 

Now agian connecting to the Instance :

C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 4 18:14:28 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Here by default, the PMON process registers service information with its local listener on the default local address of TCP/IP, port 1521. As long as the listener configuration is synchronized with the database configuration, PMON can register service information with a nondefault local listener or a remote listener on another node. During service registration PMON provides listener with the following information: 

- Name of the associated instance
- Current load and maximum load on instance
- Names of DB services provided by database.

- Information about dedicated servers and dispatchers (depends on database server mode i.e dedicated/shared server mode) .

PMON process wakes up at every 60 seconds and provide information to the listener. If any problem arises and PMON process fails then it's not possible to register information to listener periodically. In this case we can do 'Manual service registration' using command: 

SQL> ALTER SYSTEM REGISTER; 

Reference ::  Click Here


Enjoy      :-) 

DataGuard Broker And its Benefits

The Oracle Data Guard broker  is a distributed  management  framework that automates and centralizes  the creation, maintenance, and   monitoring  of  Data Guard configurations. The  following  describes  some of the operations the broker automates and simplifies 

I.) Adding  additional  new  or  existing (physical, snapshot, logical, RAC or non-RAC)  standby databases to  an existing Data Guard configuration, for a total of one primary database, and from 1 to 30 standby databases(in Oracle 11g) in the same configuration.

II.) Managing  an entire Data Guard configuration, including  all databases, redo transport services, and log apply services, through a client connection to any database in the configuration.

III.) Managing the protection mode for the broker configuration.

IV.) Invoking switchover or failover with a single command to initiate and control complex role changes across all databases in the configuration.

V.) Configuring failover to occur automatically upon loss of the primary database, increasing availability without manual intervention.

VI.) Monitoring the status of the entire configuration, capturing diagnostic information, reporting statistics such as the redo apply rate and the redo generation rate, and detecting problems quickly with centralized monitoring, testing, and performance tools.

Oracle Data Guard Broker Diagram :  The below diagram will help us to understand Data Guard Broker.

We can perform all management operations locally or remotely through the broker's easy-to-use interfaces: the Data Guard management pages in Oracle Enterprise Manager, which is the broker's graphical user interface (GUI), and the Data Guard command-line interface called DGMGRL.

Benefits of  Data Guard Broker : 

The broker's interfaces improve usability and centralize management and monitoring of the Data Guard configuration.The following benefits are : 

1.) Disaster protection :  By  automating  many of the manual tasks required to configure and monitor  a Data  Guard configuration, the broker enhances  the  high  availability,  data  protection, and  disaster protection capabilities that are inherent in Oracle Data Guard. Access is possible through a client to any system  in the  Data Guard  configuration, eliminating  any  single  point  o  failure. If  the  primary  database fails, the  broker automates  the  process  for  any  one of the standby databases to replace the primary database  and  take  over  production  processing. The  database  availability  that  Data Guard provides makes it easier to protect our data.

2.) Higher availability and scalability :  While  Oracle  Data Guard broker  enhances  disaster  protection by  maintaining  transitionally  consistent  copies  of  the  primary database,  Data Guard,  configured  with Oracle  high  availability solutions such as Oracle Real Application Clusters (RAC) databases.

3.) Automated creation of a Data Guard configuration :  The broker  helps us  to  logically  define  and create  a Data  Guard  configuration  consisting  of  a  primary  database  and  (physical  or  logical, snapshot, R AC or non-RAC)  standby  databases.  The  broker  automatically  communicates  between  the databases  in  a Data Guard configuration using Oracle Net Services. The databases can be local or remote, connected by a LAN or geographically dispersed over a WAN.

4.) Easy configuration of additional standby databases :  After  we  create  a  Data Guard  configuration consisting  of  a  primary  and  a  standby  database,  we can  add  up  to  eight  new  or  existing,  physical, snapshot, or logical standby databases to each Data Guard configuration. Oracle Enterprise Manager provides an Add Standby Database wizard to guide us through the process of adding more databases. 

5.) Simplified, centralized, and extended management : We can issue commands to manage many aspects of the broker configuration. These include:

I.> Simplify the management of all components of the configuration, including the primary and standby databases, redo transport services, and log apply services.

II.>  Coordinate  database  state transitions  and  update  database  properties  dynamically  with  the broker recording the changes in a broker  configuration  file  that  includes  profiles  of  all  the  databases  in  the configuration. The broker  propagates  the  changes  to  all  databases  in  the configuration and their server parameter files.

6.) Simplified switchover and failover operations : The  broker  simplifies  switchovers  and  failovers  by allowing  us  to  invoke  them  using  a  single  key  click  in  Oracle  Enterprise  Manager  or  a  single command  at  the  DGMGRL  command-line interface. Fast-start  failover  can  be configured  to occur  with no data loss or with a configurable amount of data loss.

7.) Built-in monitoring and alert and control mechanisms :  The  broker  provides  built-in  validation that  monitors  the  health  of  all  of  the  databases  in  the configuration. From  any  system  in  the configuration connected to  any   database, we  can capture  diagnostic  information  and detect obvious and subtle  problems  quickly  with  centralized  monitoring,  testing,  and  performance  tools. 

8.) Transparent to application :  Use  of  the broker  is  possible  for  any  database  because  the  broker works  transparently  with  applications;  no  application  code  changes  are  required  to  accommodate a configuration that we manage with the broker.

Relationship of Objects Managed by the Data Guard Broker :

Reference : http://download.oracle.com/docs/cd/B13789_01/server.101/b10822/concepts.htm




Enjoy      :-) 

ORA-16789: Standby Redo Logs Not Configured

This is very common error which occur during the switchover the standby database in Dataguard Broker. In my case when i switchover to standby the error occur as below : 

C:\>dgmgrl
DGMGRL for 32-bit Windows: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/xxxx@noida
Connected.

DGMGRL>  switchover to 'red';
Performing switchover NOW, please wait...
Error: ORA-16789: standby redo logs not configured
Failed.
Unable to switchover, primary database is still "noida"

This error generally occur because the Standby redo logs were not configured for the database.

Therefore,Standby redo logs are required when the redo transport mode is set to SYNC or ASYNC . Hence to solve this we have add standby redolog files on primary database . Below the command to this issue. 

On Primary Database: 

SQL> ALTER DATABASE add standby  LOGFILE GROUP 6  'C:\APP\NEERAJS\ORADATA\NOIDA\REDO06.LOG' size 50m ;
Database altered.

Now it's is not giving any further error .


Enjoy         :-) 

How to Drop/Rename Standby Redolog file in Oracle 11g

While performing the dataguard Broker, we need to drop the standby database while switchover the standby . As it seems an easy task but it is bit tricky . Below are the steps to drop the redolog file from standby database :

On Standby Database : 
SQL> select member,type from v$logfile;
MEMBER                                                                     TYPE                      
----------------------------------                                         -----------
D:\APP\STANDBY\ORADATA\REDO03.LOG      ONLINE
D:\APP\STANDBY\ORADATA\REDO02.LOG      ONLINE
D:\APP\STANDBY\ORADATA\REDO01.LOG      ONLINE
D:\APP\STANDBY\ORADATA\REDO04.LOG      STANDBY    
D:\APP\STANDBY\ORADATA\REDO05.LOG      STANDBY

Here,we have to drop the two standby redolog file .

SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

Now to solve this issue we have cancel the managed recovery session and set  "standby_file_management"  to manual and drop the standby redolog file  as 

SQL> alter database recover managed standby database cancel ;
Database altered.

SQL> alter system set standby_file_management='MANUAL' ;
System altered.

SQL>alter database drop standby logfile group 4;
Database altered.

SQL>alter database drop standby logfile group 5;
Database altered.

If the status of standby redolog show the "clearing_current" then we cannot drop "clearing_current" status logs,and for that we have to sync with Primary and clear the log first before dropping as
SQL> alter database clear logfile group n;

Once the standby redologs are dropped then again back to recover the standby.

SQL>alter system set standby_file_management='AUTO' ;
System altered.

SQL> alter database recover managed standby database disconnect from session ;
Database altered.

More detail click here


Enjoy    :-)