Category: IT service availability management

Puzzle for Production Database Synchronisation…Any Idea?

~ Soumen ~ 2 Comments

The new puzzle is as below…..

Let us assume a business with its application running on an Oracle Database. Before running into the deep detail of the puzzle, let us name it as D1 and also consider it has a very much customized physical database structure (different file system, different data files (names and sizes) and we can name it as DF1. This database has three application schemas namely D1S1, D1S2 and D1S3. Transactions are happening 24X7. No downtime.

Now another Oracle database on a different hardware is installed, with same database SID, but using OFA (Let us name this physical structure as DF2 with much bigger database files) and consider this database is D2. Now I am allowed to create the application schemas. Remember the schemas in the new database having different tablespace (names). And you are also allowed to copy the data.

Let us consider you took 8 hours for creating the second database D2 starting from 00:00:01 hrs. So the data is consistent till 00:00:00 hrs. Now till 08:00:00 hrs there is enough transactions happened in the first database D1.

The puzzle is how to reflect the data in the D1 to D2 So that the users can be shifted to database D2 for the application?

Whether any native oracle tools oracle technology (comes free with if Oracle Enterprise version is purchased) available to do such job?

Or there are a need of special tools to used? Who are the vendors?

  • The basic rule is minimum downtime. Downtime is only permitted to stop user pointing to D1 and start users to point to D2.
  • The other point is you should have an evidence which proves the data in D1 and D2 are same same at the point of stoppage. This is a compliance requirement…..No data should miss and the the evidence it is not missed is must….

So enjoy solving the puzzle……

DB statistics and performance of DB at disaster recovery site after failed over.

~ Soumen ~ 7 Comments

This is a sudden thought that starts biting me…….

Let us consider a scenario, where we have a big application with thousands of user, using Oracle Database, and the application do not support the logical standby database. So for a DR setup, we are using a combination of primary database at main Data location and a physical standby database at the Disaster Recovery location using Oracle Dataguard technology.

And let us also consider the case, where the hardware used in secondary standby database is of entirely different hardware, I mean much lower in capacity (probably the ideal case – people may not want to spend as much money as it is for main data center and keep it idle – usage is much less unless there is a disaster or drill).

Now at the normal condition, any changes happening at Primary database will be applied using the Archived Logs in the standby database, and the statistics of the database objects in the primary database. Now when primary fails over to secondary, secondary database will have statistics which is actually of no use, because it is the statistics generated relevant to the hardware at primary site.

So expected is the slow response (At least the queries and reports) which will be much slower, when DBA starts generating the statistics…..

oinstall or dba – the myth and the experience

~ Soumen ~ 5 Comments

This is quite confusing, and hence I thought to better disclose it here. Actually I am current in an experimental setup of Business Objects, which finally led me to land up in the question “’oinstall’ or ‘dba’?” — that is years old and I don’t think this is yet answered properly or I understood it properly.

Oh..I forgot to mention that I am installing Business Objects with Oracle Database on a Linux (SuSE Linux Enterprise Server 10 SP2). Before installation I have created both the group ‘oinstall’ and ‘dba’ as it says in the installation manual. I had basically three things in mind.

  1. I should have an oracle directory/inventory owner / manager….He/she should not be able to database administration.
  2. I should have a database administrator who should have the full access to database admin but must not be able to do or change the directories/binaries.
  3. I should have a database user, who should not be able to database admin but will be able to do login into one particular schema he is assigned to. Let say it is ‘scott’.

Earlier I never used to give it (the Question — “’oinstall’ or ‘dba’?”) much of an importance, but this is now required and last three days I am searching internet for a very concise answer, on the possibility of meeting the 3 above conditions.

So what I did? I have installed Oracle – mentioning ‘dba’ as Unix DBA group and inventory and ‘oinstall’ as the operating system group who has the write permission to oracle inventory directory. I did the installation from a user ‘oratst’ whose primary group is ‘oinstall’ and other groups are ‘dba’ and ‘oper’. Hence ‘oratst’ is the inventory owner.

What I observed is a user who is not a member of ‘oinstall’ group (at least one of the secondary group of the user is not ‘oinstall’ can not traverse to ‘ORACLE_HOME’, ‘ORACLE_HOME/bin’, ORACLE_HOME/lib etc and hence will not be able to execute ‘sqlplus’ or any other binary.

If I create one user whose primary group is DBA and secondary is ‘oinstall’ , this user can do ‘sqlplus / as sydba’ i.e. do not need a password to be supplied to login as ‘SYSDBA’.

Similarly if I create a user whose primary group is ‘oper’ and secondary group is ‘oinstall’ and this is user is not a member of group ‘dba’, then this user will not be able to login as ‘sqlplus / as sydba’. But it will be able to do login in any of the user using database user name and password.

Apparently the above 3 goal is achieved…No not, it is achieved partially. I have checked if I remove the secondary group’ dba’ from user apparently it is a no harm, in fact I have removed the group membership of  ‘oper’ too. Although I didn’t checked yet whether it is going to create a problem for me when I am applying patch.  Till this time I have not yet started to apply the patch.

Secondly, any user who is able to connect database supplying a username and password, will be able to login as ‘sysdba’ provided he/she crack/steal the password of database user ‘sys’. At a first step I did created a long and strong password, but it would have been if I could have restricted him from the operating system two. i.e. except the ‘dba’ group member nobody should be able to login as ‘sysdba’ even if he/she guess the password. But complete security will come into picture, if and only if we also check and devise a method by which a user can be restricted to modify his group membership. Only then the above three goal can be achieved properly……….

A dream of yesterday is a fact today..

~ Soumen ~ Leave a comment

Almost two and half years ago, I tried to explain my thought on oracle Dataguard with my colleagues and seniors, ultimately denied about the viability. Finally I just blogged….

But today I am really relishing with the new though that I am not entirely confused but have brilliant ideas. We achieved very low Effective Downtime of Business Application [EDBA ;)] when we upgraded SAP. The EDBA was about 4 hours instead of 18/20 hours standard as seen by SAP. Please note that they (SAP) are correct as actually they do not consider the scenario of using a standby application ( We were using Oracle Dataguard.)

So I thought to write a universal schematic about my thought which do not only applies for SAP but for any other business application running Oracle database in the backend.

I was reading a powerpoint presentation Oracle Hot Standby Database by Chris Lawson  at DBA Specialist and it is an eye opener for me. I wonder, for any oracle application, database administrator/ system administrator must look for an option of creating a standby….Oracle Dataguard is an brilliant option. Consider the case for a major changes in the architecture, a hardware maintenance for a longer (of hours) period will always benefit out of it … Read More

via Diary of a Confused DBA…..