Restrict A User From Being Dropped

Sometimes  we have to  put  the  restriction  on  those  user which  is  having DBA role even though they can't drop a particular schemas . In such scenario's we have to create the triggers to restrict the user   before  the drop command . Below script  will restrict the DBA's  to drop the particular schemas . Here is the triggers.

SQL> Create Or Replace Trigger  TrgDropUserRestrict
Before Drop On Database
Declare
        Begin If Ora_Dict_Obj_Name In ('SCOTT','OUTLN')    Then 
 Raise_Application_Error(-20001,'Cannot Drop User '||ora_dict_obj_name||' Contact Your Database Administrator For Dropping This User !');  
        End If;
End;
/

Enjoy        :-)

Script to Query all table row counts

Once I have to calculate the no. of rows of  all tables in a schema. It seems quite tedious to count rows of all the tables one by one  as  :
SQL>select count(*) from table_name;


The second method is to export the schemas virtually by using the parameter estimate of Datapump. Using this method,we can check the logfile for tables row counts . Even this method is not so efficient because if the schemas size is large then we will take long time. We can export the schemas as
C:\> expdp system/xxxx@noida directory=dpump  schemas=hr logfile=hrlog11.log dumpfile=hr.dmp ESTIMATE=BLOCK

Another option to find table row counts is to use the pl/sql scripts .I found this script from http://www.dba-village.com  and is quite efficient and useful. Here is the below script to check the no. of rows in tables. 

create or replace function table_count (i_table_name varchar2)
return number
as 
 t_cnt number default 0;
begin
 execute immediate 'select count(*) from '||i_table_name into t_cnt;
 return t_cnt;
end;
/
show errors
select table_name, table_count(table_name) from user_tables;
drop function table_count;
Hence, this script allows for counting rows in tables without the need for sqlplus or temporary script files.

If we want to check the empty tables i.e; table not having in any rows can be found from the below scripts :

set termout off

col sql for a120

spool 'C:\checkempty_temp.sql'

set pages 0 feed off echo off

select 

'select '''||owner ||''' owner,'''||table_name||''' table_name  '||chr(10)||

'FROM '||owner||'.'||table_name||' where rownum<2 having count(*)=0; ' sql

from all_tables

where owner not in ('SYS','SYSTEM','OUTLN','WMSYS') and substr(owner,1,4)<>'OPS$'

--and (blocks>0 or last_analyzed is null)

--and owner='XXXX'

--and blocks=0

order by 1;

spool off

set termout on pages 100

@"c:\checkempty_temp.sql"

Enjoy   :-)

WISH YOU ALL A VERY VERY HAPPY DIWALI

May the light that we celebrate at Diwali show us the way and lead us together on the path of peace and social harmony.

           "WISH YOU ALL A VERY HAPPY DIWALI" 

May millions of lamps illuminate your  life with endless joy,prosperity,health & wealth forever.

  

HAVE FUN AND ENJOY DIWALI    :-)

Switchover and Failover in Standby Oracle 11g

Data Guard uses two terms when cutting over the standby server, switch-over which is a planned and failover which a unplanned event .

1.)   Switchover  :   Switchover is a planned event, it is ideal when we might want to upgrade the primary database or change the storage/hardware configuration (add memory, cpu networking), we may even want to upgrade the configuration to Oracle RAC .

What happens during a switchover is the following :


1.) Notifies the primary database that a switchover is about to occur
2.) Disconnect all users from the primary database
3.) Generate a special redo record that signals the End of Redo (EOR)
4.) Converts the primary database into a standby database

5.) Once the standby database applies the final EOR record, guaranteeing that no data loss has been lost, converts the standby database into the primary database.

The new standby database (old primary) starts to receive the redo records and continues process until we switch back again. It is important to remember that both databases receive the EOR record so both databases know the next redo that will be received. Although we can have users still connecting to the primary database while the switchover occurs (which generally takes about 60 seconds) I personal have a small outage just to be on the safe side and just in case things don't go as smoothly as I hoped.

We can even switch over form a linux database to a windows database from a 64 bit to a 32 bit database which is great if we want to migrate to a different O/S of 32/64 bit architecture, also our rollback option is very easy simply switchback if it did not work.

2.)   Failover :   Failover is a unplanned event, this is where the EOR was never written by the primary database, the standby database process what redo it has then waits, data loss now depends on the protection mode in affect .

• Maximum Performance - possible chance of data loss
• Maximum Availability - possible chance of data loss
• Maximum Protection - no data loss

we have the option to manually failover or make the whole process automatic, manual gives  the DBA maximum control over the whole process obliviously the the length time of the outage depends on getting the DBA out of bed and failing over. Otherwise Oracle Data Guard Fast-Start Failover feature can automatically detect a problem and failover automatically for us. The failover process should take between 15 to 25 seconds.

Which Role Transition Operation Should I Use  ?

When faced with the decision on which role transition is best for the given situation, we need to always choose one that best reduces downtime and has the least potential for data loss. Also to consider is how the change will affect any other standby database in the configuration. We should consider the following when making the decision on which operation to use:

• What is the current state of the primary database just before the transition? Is it available?
• What is the state of the selected standby database to be used in the role transition at the time of transition?
• Is the standby database configured as a physical or logical standby database?

The following decision tree can be used to assist when making this critical decision as to which operation to perform:

One key point to consider is that if it would be faster to repair the primary database (from failure or a simple planned hardware/software upgrade), the most efficient method would be to perform the tasks and then to bring up the primary database as quickly as possible and not perform any type of role transition. This method can impose less risk to the system and does not require any client software to be re-configured.

Another consideration involves a Data Guard configuration which includes a logical standby database. A switchover operation can be performed using either a physical or logical standby database. Take note, however, of the following issues you may run in to regarding physical and logical standby configurations. If the configuration includes a primary, a physical standby, and a logical standby, and a switchover is performed on the logical standby, the physical standby will no longer be a part of the configuration and must be rebuilt. In the same scenario, if a switchover operation is performed on the physical standby, the logical standby remains in the Data Guard configuration and does not need to be rebuilt. Obviously, a physical standby is a better option to be a switchover candidate than a logical standby when multiple standby types exist in a given configuration.

Hence finally we come to conclusion that the order to setup Data Guard is the following : 

• The primary database is up and running
• Create a standby database
• Setup the redo transport rules
• Create the SRL files
• Execute one of the following

SQL> alter database set standby to maximum performance;      //(default) 
SQL> alter database set standby to maximum availability;
SQL> alter database set standby to maximum protection;


Reference : http://www.datadisk.co.uk
                     http://www.idevelopment.info




Enjoy        :-)

Data Protection Mode In Data Guard

Data Guard protection modes are simply a set of rules that the primary database must adhere to when running in a Data Guard configuration. A protection mode is only set on the primary database and defines the way Oracle Data Guard will maximize a Data Guard configuration for performance, availability, or protection in order to achieve the maximum amount of allowed data loss that can occur when the primary database or site fails

A Data Guard configuration will always run in one of the three protection modes listed above. Each of the three modes provide a high degree of data protection; however they differ with regards to data availability and performance of the primary database. When selecting a protection mode, always consider the one that best meets the needs of your business. Carefully take into account the need to protect the data against any loss vs. availability and performance expectations of the primary database

Data Guard can support multiple standby databases in a single configuration, they may or may not have the same protection mode settings depending on our requirements. The protection modes are 

1.) Maximum Performance  
2.) Maximum Availability    
3.) Maximum Protection      

1.)  Maximum Performance    : This is the default mode, we get the highest performance but the lowest protection. This mode requires ASYNC redo transport so that the LGWR process never waits for acknowledgment from  the standby database for maximum performance.How much data we lose depends on the redo rate and how well our network can handle the amount of redo also known as transport lag. Even if we have a zero lag time we still will lose some data at fail-over time .

We can have up to 9 physical standby database in oracle 10g and 30 in oracle 11g and we will use the Asynchronous transport (ASYNC) with no affirmation of the standby I/O (NOAFFIRM). We can use this anywhere in the world but bear in mind the network latency and making sure it can support our redo rate .While it is not mandatory to have standby redo logs (SRL) in this mode, it is advise to do so. The SRL files need to be the same size as the online redo log files (ORL) . 

The following table describes the attributes that should be defined for the LOG_ARCHIVE_DEST_n initialization parameter for the standby database destination to participate in Maximum Performance mode. 

For example :   log_archive_dest_2='service=res ARCH  NOAFFIRM'        or
                       log_archive_dest_2='service=red LGWR ASYNC NOAFFIRM'

2.)  Maximum Availability   : Its first priority is to be available and  its second priority is zero loss protection, thus it requires the SYNC redo transport. This is the middle middle of the range, it offers maximum protection but not at the expense of causing problems with the primary database. However we must remember that it is possible to lose data, if our network was out for a period of time and the standby has not had a chance to re-synchronize and the primary went down then there will be data loss.

Again we can have up to  9 physical standby database in oracle 10g and 30 in oracle 11g  and we will use Synchronous transport (SYNC) with affirmation of the standby I/O (AFFIRM) and SRL files. In the event that the standby server is unavailable the primary will wait the specified time in the NET_TIMEOUT parameter before giving up on the standby server and allowing the primary to continue to process. Once the connection has been re-established the primary will automatically resynchronize the standby database.

When the NET_TIMEOUT expires the LGWR process disconnects from the LNS process, acknowledges the commit and proceeds without the standby, processing continues until the current ORL is complete and the LGWR cycles into a new ORL, a new LNS process is started and an attempt to connect to the standby server is made, if it succeeds the new ORL is sent as normal, if not then LGWR disconnects again until the next log switch, the whole process keeps repeating at every log switch, hopefully the standby database will become available at some point in time. Also in the background if we remember if any archive logs have been created during this time the ARCH process will continually ping the standby database waiting until it come online.

We might have noticed there is a potential loss of data if the primary goes down and the standby database has also been down for a period of time and here has been no resynchronization, this is similar to Maximum Performance but we do give the standby server a chance to respond using the timeout. The minimum requirements are described in the following table : 

For example  :   log_archive_dest_2='services=red LGWR SYNC AFFIRM

3.) Maximum Protection   : This offers the maximum protection even at the expense of the primary database, there is no data loss.  This mode uses the SYNC redo transport and the primary will not issue a commit acknowledgment to the application unless it receives an acknowledgment from at least one standby database, basically the primary will stall and eventually abort preventing any unprotected commits from occurring. This guarantees complete data protection, in this setup it is advised to have two separate standby databases at different locations with no Single Point Of Failures (SPOF's), they should not use the same network infrastructure as this would be a SPOF.

The minimum requirements are described in the following following table

For Example :   log_archive_dest_2='service=red LWGR SYNC AFFIRM'

Finally the protection mode will be changed from its default of Maximum Performance to Maximum Protection.The protection modes run in the order from highest (most data protection) to the lowest (least data protection)

Each of the Data Guard data protection modes require that at least one standby database in the configuration meet the minimum set of requirements listed in the table below.

Reference  ::  http://www.datadisk.co.uk
                       http://www.idevelopment.info

For more detail   Click Here


Enjoy    :-) 

Data Guard Architecture Oracle 11g Part-III

The redo data transmitted from the primary database is written to the standby redo log on the standby database. Apply services automatically apply the redo data on the standby database to maintain consistency with the primary database. It also allows read-only access to the data.The main difference between physical and logical standby databases is the manner in which apply services apply the archived redo data.

There are two methods in which to apply redo  i.e,
1.)  Redo Apply (physical standby)     and
2.) SQL Apply   (logical standby).

They both have the same common features:

• Both synchronize the primary database
• Both can prevent modifications to the data
• Both provide a high degree of isolation between the primary and the standby database
• Both can quick transition the standby database into the primary database
• Both offer a productive use of the standby database which will have no impact on the primary database

1.) Redo Apply (Physical Standby) :  Redo apply is basically a block-by-block physical replica of the primary database, redo apply uses media recovery to read records from the SRL into memory and apply change vectors directly to the standby database. Media recovery does parallel recovery for very high performance, it comprises a media recovery coordinator (MRP0) and multiple parallel apply rocesses(PR0?). The coordinator manages the recovery session, merges the redo by SCN from multiple instances (if in a RAC environment) and parses redo into change mappings partitioned by the apply process. The apply processes read data blocks, assemble redo changes from mappings and then apply redo changes to the data blocks.

This method allows us to be able to use the standby database in a read-only fashion, Active Data Guard solves the read consistency problem in previous releases by the use of a "query" SCN. The media recovery process on the standby database advances the query SCN after all dependant changes in a transaction have been fully applied. The query SCN is exposed to the user via the current_scn column of the v$databaseview  Read-only use will only be able to see data up to the query SCN and thus the standby database can be open in read-only mode while the media recovery is active, which make this an ideal reporting database.

We can use SYNC or ASYNC and is isolated from I/O physical corruptions, corruption-detections checks occur at the following key interfaces:

On the primary during redo transport - LGWR, LNS, ARCH use the DB_UTRA_SAFE parameter

On the standby during redo apply       - RFS, ARCH, MRP, DBWR use the DB_BLOCK_CHECKSUM and DB_LOST_WRITE_PROTECT parameters .

If Data Guard detects any corruption it will automatically fetch new copies of the data from the primary using gap resolution process in the hope of that the original data is free of corruption.The key features of this solution are

• Complete application and data transparency - no data type or other restrictions
• Very high performance, least managed complexity and fewest moving parts
• End-to-end validation before applying, including corruptions due to lost writes
• Able to be utilized for up-to-date read-only queries and reporting while providing DR
• Able to execute rolling database upgrades beginning with Oracle Database 11g 

2.) SQL Apply (Logical Standby)  : SQL apply uses the logical standby process (LSP) to coordinate the apply of changes to the standby database. SQL apply requires more processing than redo apply, the processes that make up SQL apply, read the SRL and "mine" the redo by converting it to logical change records and then building SQL transactions and applying SQL to the standby database and because there are more moving parts it requires more CPU, memory and I/O then redo apply .

SQL apply does not support all data types, such as XML in object relational format and Oracle supplied types such as Oracle spatial, Oracle inter-media and Oracle text .

The benefits to SQL apply is that the database is open to read-write while apply is active, while we can not make any changes to the replica data we can insert, modify and delete data from local tables and schemas that have been added to the database, we can even create materialized views and local indexes. This makes it ideal for reporting tools, etc to be used.

The key features of this solution are :

• A standby database that is opened for read-write while SQL apply is active
• A guard setting that prevents the modification of data that is being maintained by the SQL apply
• Able to execute rolling database upgrades beginning with Oracle Database 11g using the KEEP IDENTITY clause

Click Here for Data Guard Architecture Oracle 11g Part-IV




Enjoy    :-)

Data Guard Architecture Oracle 11g Part-II

LNS (log-write network-server) and ARCH (archiver) processes running on the primary database select archived redo logs and send them to the standby database, where the RFS (remote file server) background process within the Oracle instance performs the task of receiving archived redo-logs originating from the primary database .

The LNS process support two modes  as
1.) Synchronous    and
2.) Asynchronous.

1.) Synchronous Mode :  Synchronous transport (SYNC) is also referred to as "zero data loss" method because the LGWR is not allowed to acknowledge a commit has succeeded until the LNS can confirm that the redo needed to recover the transaction has been written at the standby site. In the below diagram, the phases of a transaction are : 

The user commits a transaction creating a redo record in the SGA, the LGWR reads the redo record from the log buffer and writes it to the online redo log file and waits for confirmation from the LNS. The LNS reads the same redo record from the buffer and transmits it to the standby database using Oracle Net Services, the RFS receives the redo at the standby database and writes it to the SRL. When the RFS receives a write complete from the disk, it transmits an acknowledgment back to the LNS process on the primary database which in turns notifies the LGWR that the transmission is complete, the LGWR then sends a commit acknowledgment to the user.

This setup really does depend on network performance and can have a dramatic impact on the primary databases, low latency on the network will have a big impact on response times. The impact can be seen in the wait event "LNS wait on SENDREQ" found in the v$system_event dynamic performance view.

2.) Asynchronous  Mode : Asynchronous transport (ASYNC) is different from SYNC in that it eliminates the requirement that the LGWR waits for a acknowledgment from the LNS, creating a "near zero" performance on the primary database regardless of distance between the primary and the standby locations. The LGWR will continue to acknowledge commit success even if the bandwidth prevents the redo of previous transaction from being sent to the standby database immediately. If the LNS is unable to keep pace and the log buffer is recycled before the redo is sent to the standby, the LNS automatically transitions to reading and sending from the log file instead of the log buffer in the SGA. Once the LNS has caught up it then switches back to reading directly from the buffer in the SGA .

The log buffer ratio is tracked via the view X$LOGBUF_READHIST a low hit ratio indicates that the LNS is reading from the log file instead of the log buffer, if this happens try increasing the log buffer size.

The drawback with ASYNC is the increased potential for data loss, if a failure destroys the primary database before the transport lag is reduced to zero, any committed transactions that are part of the transport lag are lost. So again make sure that the network bandwidth is adequate and that get the lowest latency possible.

A log file gap occurs whenever a primary database continues to commit transactions while the LNS process has ceased transmitting redo to the standby database (network issues). The primary database continues writing to the current log file, fills it, and then switches to a new log file, then archiving kicks in and archives the file, before we know it there are a number of archive and log files that need to be processed by the the LNS basically creating a large log file gap.

Data Guard uses an ARCH process on the primary database to continuously ping the standby database during the outage, when the standby database eventually comes back, the ARCH process queries the standby control file (via the RFS process) to determine the last complete log file that the standby received from the primary. The ARCH process will then transmit the missing files to the standby database using additional ARCH processes, at the very next log switch the LNS will attempt and succeed in making a connection to the standby database and will begin transmitting the current redo while the ACH processes resolve the gap in the background. Once the standby apply process is able to catch up to the current redo logs the apply process automatically transitions out of reading the archive redo logs and into reading the current SRL. The whole process can be seen in the diagram below  :

Click Here for Data Guard Architecture Oracle 11g Part-III


Enjoy      :-)

Data Guard Architecture Oracle 11g Part-I

I have decided to post the Architecture of the Standby Database, although there are lots of  stuff on the Internet but most of them are lengthy and are not so juicy . I have read a good notes on Standby Database  Architecture and further decided to post it . Though, I have modified few topics to make it more clear , juicy and interesting .Hope you all find helpful  and enjoy this after reading. 

Oracle Data Guard is the most effective and comprehensive data availability, data protection and disaster recovery solution for enterprise databases. It provides a method for customers to actively utilize their disaster recovery configuration for read-only queries and reports while it is in standby role. Additionally, a standby database can be used to offload backups from production databases or for Quality Assurance and other test activities that require read-write access to an exact replica of production. These capabilities are unique to Oracle .

Oracle   Data  Guard is  the  management,  monitoring,  and  automation  software  infrastructure that creates,maintains, and monitors one or more standby databases  to  protect  enterprise  data  from  failures, disasters, errors, and corruptions.Data Guard is basically a ship redo and then apply redo, as we know redo is the information needed to recover a database transaction. A production database referred to as a primary database transmits redo to one or more independent replicas referred to as standby databases. Standby databases are in a continuous state of recovery, validating and applying redo to maintain synchronization with the primary database. A  standby database will also automatically re-synchronize if it becomes temporary disconnected to the primary due to power outages, network problems, etc.

The diagram below shows the overview of Data Guard, firstly the redo transport services transmits redo data from the primary to the standby as it is generated, secondly services apply the redo data and update the standby database files, thirdly independently of Data Guard the database writer process updates the primary database files and lastly Data Guard will automatically re-synchronize the standby database following power or network outages using redo data that has been archived at the primary.

Redo records contain all the information needed to reconstruct changes made to a database. During  recovery the database will read the  change vectors in the redo  records and apply  the changes  to  the relevant blocks.Redo  records are  buffered  in a circular fashion in  the redo log  buffer  of the SGA, the  log  writer  process (LGWR) is  the  background process  that handles redo log buffer  management. The LGWR at specific times writes redo log entries into a sequential file (online redo log file) to free space in the buffer, the LGWR writes the following.

1.) A commit record :   When ever a transaction is committed the LGWR writes the transaction redo records from the buffer to the log file and assigns a system change number (SCN), only when this process is complete is  the transaction said to be committed.

2.) Redo log buffers :  If the redo log becomes a third full or if 3 seconds have passed sine the last time the LGWR wrote to the log file, all redo entries in the buffer will be written to the log file. This means  that redo records can be written to the log file before the transaction has been committed and if  necessary media recovery will rollback these changes using undo that is also part of the redo entry.

Remember that the LGWR can write to the log file using "group" commits, basically entire list of redo entries of waiting transactions (not yet committed) can be written to disk in one operation, thus reducing I/O. Even through the data buffer cache has not been written to disk, Oracle guarantees that no transaction will be lost due to the redo log having successfully saved any changes.

Data Guard Redo Transport Services  coordinate the  transmission of redo from  the primary  database to the standby database, at the same time the LGWR  is  processing redo, a separate Data Guard process called the Log Network Server (LNS) is reading from the redo buffer in the SGA and passes redo to Oracle Net Services from transmission to a standby database, it is possible to direct the redo data to nine standby databases, we can also use Oracle RAC and they don't all need to be a RAC setup. The process Remote File Server  (RFS) receives the redo from LNS and writes it to a sequential file called a standby redo log file (SRL).

Click Here for Data Guard Architecture Oracle 11g Part-II


Enjoy   :-)

ORA-01000: Maximum Open Cursors Exceeded

Once our client report that they are facing error  “ORA-01000: maximum open cursors exceeded” while  running a application . As it seems from error that the error is related to cursors limits i.e, open cursors are exceeding from it's defaults values. To solving this issue ,let's have a look on the open_cursor i.e, what is open_cursor and how it impact into  database.

Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

To solve this issue we can either increase the no. of open_cursors or kill the inactive session which has open the large number of cursors. Now we connect to the database and check the open_cursors limits :

C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 18:05:30 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> sho parameter open_cursors
NAME                          TYPE          VALUE
-----------------        ---------      -----------
open_cursors              integer            300

Since the no. of open_cursors is 300. So we list the top 10 sessions which are currently opening most cursors

SQL> select * from ( select ss.value, sn.name, ss.sid
 from v$sesstat ss, v$statname sn
 where ss.statistic# = sn.statistic#
 and sn.name like '%opened cursors current%'
 order by value desc) where rownum < 11 ;

VALUE           NAME                              SID
-----            -----------------                ----------
300         opened cursors current          131
300        opened cursors current          125
300        opened cursors current           143
300        opened cursors current          149
300        opened cursors current           17
300        opened cursors current           132
300        opened cursors current           23
300        opened cursors current           1
300        opened cursors current           9
300        opened cursors current          10
10 rows selected.

Now we check what make session 131 open to many cursors?

SQL>  select sid, status, event, seconds_in_wait state "wait(s)" , blocking_session "blk_sesn", prev_sql_id  "SQL_ID"  from v$session where sid=131;

SID  STATUS      EVENT                  WAIT(s)  STATE    BLK_SESN    SQL_ID
---   ----------   ------------------ --------     ---------   ---------    ---------------
131 INACTIVE rdbms ipc message   8745     WAITING                 6mqvntr9ytnga


Since the status of the cursor is INACTIVE so we can we kill the session by using the below command :

SQL> alter system kill session 'sid,serial#' immediate;

The other alternatives is to increase the no. of the open_cursors parameter as :

SQL> alter system set open_cursors=1500 scope=spfile;

In my case i have increased the values of the open_cursors and issue got solved.


Enjoy     :-) 

Open Standby in Read-write Mode When Primary is Lost

There may be scenario  where Primary database is lost and we are only left with the standby database . In this scenario's we have to open the standby database in read-write mode. Below are the steps to convert standby database to Primary database.

1.)  Open standby database in mount state : 
SQL> select name,open_mode from v$database;
NAME       OPEN_MODE
------     -------------
NOIDA      READ ONLY

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
 
Total System Global Area      263639040  bytes
Fixed Size                             1373964      bytes
Variable Size                         230689012  bytes
Database Buffers                  25165824     bytes
Redo Buffers                        6410240       bytes
Database mounted.

SQL> select open_mode ,protection_mode , database_role from v$database ;
OPEN_MODE     PROTECTION_MODE           DATABASE_ROLE
---------        ----------------------       ----------------
MOUNTED       MAXIMUM PERFORMANCE    PHYSICAL STANDBY

2.) Recover if there is any archive logs:

SQL>recover standby database;
ORA-01153: an incompatible media recovery is active

To solve this issue, we cancel the media recovery by using the below command .

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

SQL> recover standby database
ORA-00279: change 2698969 generated at 10/05/2011 16:46:58 needed for thread
ORA-00289: suggestion : D:\ARCHIVE\ARC0000000133_0761068614.0001
ORA-00280: change 2698969 for thread 1 is in sequence #133

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

3.) Finish the Recovery process :  

The below command will perform the role transition as quickly as possible with little or no data loss and without rendering other standby databases unusable and to open the database in read-write mode we fire the below command : 

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

4.) Activate the Standby Database : 

SQL> alter database activate physical standby database ;
Database altered.

5.) Check the new status

SQL> select open_mode ,protection_mode , database_role from v$database ;

OPEN_MODE     PROTECTION_MODE           DATABASE_ROLE
---------         ----------------------      ---------------------
MOUNTED       MAXIMUM PERFORMANCE    PHYSICAL STANDBY

6.) Open the Database
SQL> alter database open ;
Database altered.

SQL> select open_mode ,protection_mode , database_role from v$database ;

OPEN_MODE       PROTECTION_MODE             DATABASE_ROLE
---------           ----------------------        --------------------
READ WRITE       MAXIMUM PERFORMANCE      PHYSICAL STANDBY

Enjoy   :-) 

Journeyman On OTN

Hello  All

It is a good day for me. Today i become the Journeyman on OTN site . It's great pleasure while being the journeyman . Working with Oracle is  exciting, challenging and great fun.

I am Passionate about Oracle.Though  i am working with oracle for few years and find that oracle is a great Database . whenever I ask a question to myself how much i know about oracle , the answer is  "NOTHING" . Now my aim is know "something" about oracle . There are lots to be learn and the journey is too long.

The only advise I will like to give others and try to follow myself is that always  work or choose the job that you are enthusiastic or  passionate about .If you are motivated or enthusiastic ,you will be able to learn more and enjoy while working . I beleive that knowledge is not only about learning but about sharing to others.

Enjoy    :-)

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      :-)