Friday, October 28, 2011

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

Tuesday, October 25, 2011

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


Monday, October 17, 2011

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