I did some research on SQL Server clustering a while ago to find out what high-availability and fail-over options does SQL Server provide. Below are my findings. In short SQL Server has fail-over but no high-availability as compared to Oracle RAC.
1. SQL Server 2008 Peer-to-Peer Replication
Multiple nodes running their own instances
Each node has its own copy of data
Every node is a publisher and subscriber at the same time
Not scalable because of complex architecture
Complex to modify schema
Conflicts may arise if two nodes update the same row
In case of conflict, the topology remains inconsistent until the conflict is resolved
We usually face this situation where an Oracle session is dead and an UPDATE command freezes forever. Usually it is the fault of the programmer who messes up with his session. In such case of a deadlock, the problem session needs to be killed.
Following is a query (I got from some other blog I don’t remember) we use to find objects which are locked. This is used to make sure that the object which we are trying to update is actually locked by another session:
select c.owner, c.object_name, c.object_type b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;
And this query tells which are the problem sessions:
select l1.sid, ‘ IS BLOCKING ‘, l2.sid from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
So now we have the SID of the problem session and we only need the serial number to kill it.
Oracle introduced a new command in 10g to drop a database. Before that there was a set of procedures to follow in order to get rid of a database which involved deleting all data files from the file system, deleting control files, the parameter files etc. Now after this command everything is deleted by the RDBMS and there is no chance of forgetting any file to delete.
SQL> startup mount exclusive restrict ORACLE instance started.
rman target /
The database has to be started in exclusive restrict mode.
If you are running a RAC you will have to close down all instances and mount the database exclusively on one node only. The parameter file will need to be changed and the parameter “cluster_database” should be set to false. My suggestion is to create another parameter file from spfile and mount the database using that file.
CREATE PFILE=’/home/oracle/anotherpfile.ora’ from SPFILE;
The file can be modified and all RAC/node specific parameters can be removed.
Oracle has two type of parameter files for initial parameter configurations: PFILE and SPFILE. The parameter file and the shared parameter file. They are used to specify parameter values at the database startup. The SPFILE is used in a RAC environment and PFILE is used in a stand alone database environment.
The other day I had a problem starting up my RAC and later on it was discovered that the problem was because of these startup files. The recommended way of using an SPFILE in a RAC environment is to specify the (shared) location of your SPFILE in the PFILE. All nodes have their own PFILEs which point to the same SPFILE which is located on a shared storage. I had set it up all right but it was not working. And this was because Oracle searches the configuration files in the specified directory in the following order:
So I had my initSID.ora setup but was not being used because I already had one spfileSID.ora in the same location which always got preference.
Generating a series of numbers is a very common practice in SQL environments. The objectives are but not limited to generating testing data and constants to use in SQL statements. Oracle provides a number of ways to accomplish this but the most simple to me is using the CONNECT BY clause.
1: SELECT ROWNUM N FROM dual 2: CONNECT BY LEVEL <= 20
The statement generates integers starting from 1 to 20. It can be altered in many ways according to the acquirements e.g. adding a WHERE clause to offset the series or applying a mathematical equation at N to generate different types of series. For example:
1: SELECT (ROWNUM * 0.02 + 3) "N" FROM dual 2: WHERE LEVEL > 10 3: CONNECT BY LEVEL <= 20
Another objective of series is the requirement of constants in the SQL statements. For example I used constants of week days (1-7) and number of hours in a day (1-24) to generate some statistics by hour by day of week.
1: SELECT DECODE (DN, 2: 2, ‘MON ‘, 3: 3, ‘TUE ‘, 4: 4, ‘WED ‘, 5: 5, ‘THU ‘, 6: 6, ‘FRI ‘, 7: 7, ‘SAT ‘, 8: 1, ‘SUN ‘) "Day of Week", 9: Hour, 10: (SELECT COUNT(*) FROM tblsupport 11: WHERE tktopendate > SYSDATE – 90 12: AND TO_CHAR(tktopendate, ‘D’) = DN 13: AND TO_CHAR(tktopendate, ‘HH24’) = H) "Ticket Count" 14: FROM 15: (SELECT ROWNUM DN FROM dual 16: CONNECT BY LEVEL <= 7) WeekDays, 17: (SELECT ROWNUM Hour FROM dual 18: CONNECT BY LEVEL <= 24) Hours
This statement generates statistics data about number of tickets received during the last 3 months grouped by week day and hour.
As a standard DBMS practice Oracle provides all sort of row level and table level locking mechanism for data integrity. This built-in locking mechanism is strongly coupled with database transactions e.g. a lock acquired through the LOCK command is released automatically at the end of the transaction. Sometimes you need explicit locking with full control and independent of the transactions. This feature is provided by DBMS_LOCK.
The other day I was stuck in a legacy system where I had to provide a locking mechanism on call to a stored procedure at the database level. The procedure is called from a web farm consisting of a number of web servers and I wanted to make sure that only one web servers accesses the procedure at any given time. Although I hated such a procedure in my database but that looked like the only solution to the problem without changing some fundamentals. The DBMS_LOCK package came for my rescue and here is what I learnt from it.
This package is available to the SYS user only (may be because of its significance and impact). So if you try to access it from another user you will get an error “IDENTIFIER MUST BE DECLARED”. The solution is to write functions on top of the DBMS_LOCK package and grant access to those functions. The DBA can thus regulate which operations are allowed to the user and which are forbidden.
CREATE OR REPLACE PROCEDURE xmode_lock_procedure (lockhandle varchar2, v_result OUT number) IS v_lock varchar2(200); begin dbms_lock.allocate_unique(lockhandle, v_lock); v_result := dbms_lock.request(v_lock, dbms_lock.x_mode); end;
Grant access on this procedure to the user:
GRANT ACCESS ON xmode_lock_procedure TO user1;
Granting access to public is never recommended due to sensitivity of this procedure.
Similarly a procedure which releases this lock can be written as:
CREATE OR REPLACE PROCEDURE lock_release (lockhandle varchar2, v_result OUT number) IS v_lock varchar2(200); begin dbms_lock.allocate_unique(lockhandle, v_lock); v_result := dbms_lock.release (v_lock); end;
This will release the lock and in the meantime if another users requests a lock he will be blocked until this users releases the same lock. Note that the lock uniqueness is decided by the LOCKHANDLE parameter passed to the procedures.
Today I was writing a query trying to eliminate NULL values and empty strings from a column. I found a strange behavior in Oracle that it considers NULL and empty strings ( ” ) equal which is not ANSI. Other database systems which are more compliant to ANSI like PostgreSQL differentiate between NULL and ( ” ) correctly.
Check this out:
SQL> create table tes (a varchar2(2));
SQL> insert into tes values (''); -- inserted an empty string
1 row created.
SQL> select * from tes where a is not null;
no rows selected
Gives me no rows although it has a string with zero length in it.
SQL> select length(a) from tes;
Even the length function shows a NULL it should have been saying “0”.
This brings another interesting scenario. What if you want to find out empty strings in your column?
SQL> select * from tes where a = '';
no rows selected
This does not bring in any rows because a NULL is not equal to NULL. The correct query would be:
SQL> select * from tes where trim(a) is null;
Now it brings in one row because the TRIM function returns null if no characters are left in the given string after trimming. A more ANSI compliant database would have returned an empty string ( ” ) though.