Where are my NULL records

I got a query from one of our developer few days back regarding a problem that he was facing with the result sets of the SQL. Column in the WHERE clause was having some NULL values. While generating the output, records related to NULL values were not getting generated. So, thought of testing this out and looking for the reasons.

Created a table with three records -

SQL> select * from NULL_TEST;

ID NAME
-- ------------------------------
1 Apple
2 Orange
Watermelon

If I select all the records other than ID=2 then I get the following resutset -

SQL> select * from NULL_TEST where ID!=2;

ID NAME
-- ------------------------------
1 Apple

So, why it is not showing the record that has ID=NULL?

Because, we not use equality ("=") or inequality ("!=") when dealing with NULL; only "IS NULL" or "IS NOT NULL".

Our SQL should be like this -

SQL> select * from NULL_TEST where ID!=2 or ID is NULL;

ID NAME
-- ------------------------------
1 Apple
Watermelon

Thats Better!

Temp Tablespace Usage

Script to give an idea about the usage of temp tablespace -

SELECT sql_text,
sum(onepass_executions) onepass_cnt,
sum(multipasses_executions) mpass_cnt
FROM V$SQL s, V$SQL_WORKAREA wa
WHERE s.address = wa.address
GROUP BY sql_text
HAVING sum(onepass_executions+multipasses_executions)>0;

Script to show allocation on the temp tablespace -

SELECT TO_NUMBER(DECODE(sid, 65535, NULL, sid)) sid,
operation_type OPERATION,
TRUNC(expected_size/1024) ESIZE,
TRUNC(actual_mem_used/1024) MEM,
TRUNC(max_mem_used/1024) MAXMEM,
number_passes PASS,
TRUNC(tempseg_size/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

Following script you can monitor the actual space used in a temporary tablespace and see the HWM of the temporary tablespace. The script is designed to run when there is only one temporary tablespace in the database.

select sum( u.blocks * blk.block_size)/1024/1024 "Mb. in sort segments",
(hwm.max * blk.block_size)/1024/1024 "Mb. High Water Mark"
from v$sort_usage u,
(select block_sizefrom dba_tablespaceswhere contents = 'TEMPORARY') blk,
(select segblk#+blocks maxfrom v$sort_usagewhere segblk# = (select max(segblk#)
from v$sort_usage) ) hwm
group by hwm.max * blk.block_size/1024/1024;

Disaster Recovery using RMAN

This scenario assumes the following:

• All the filesystems including oracle binaries and TSM Client of the Database Server will be available in the DR Server.
• Database TESTDB uses a server parameter file (not a client-side initialization parameter file)
• We are using Control File for the purpose of this DR and not the Recovery Catalog
• You have a record of the DBID for TESTDB (1527325274)
• A media manager (TSM Server) is accessible

Steps followed were as follows -

Step 1. Connect RMAN without catalog

rman TARGET / NOCATALOG

Step 2. Start the instance without mounting it.

RMAN> SET DBID 1527325274; --> DBID of the Source Database (TESTDB)

RMAN> STARTUP NOMOUNT;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file -----
starting Oracle instance without parameter file for retrival of spfileOracle instance started

Step 3. Restore and edit the server parameter file.

RUN
{
allocate channel t1 device type 'sbt_tape' PARMS="ENV=(TDPO_OPTFILE=/oracle/tdpo/TESTDB/tdpo.opt)";
RESTORE SPFILE TO PFILE '/oracle/product/10.2/dbs/initTESTDB.ora' FROM AUTOBACKUP;
SHUTDOWN ABORT;
}

Step 4. Start the database at NOMOUNT state.

STARTUP FORCE NOMOUNT PFILE='/oracle/product/10.2/dbs/initTESTDB.ora';

Step 5. Restore the Control File from the Autobackup..

RUN
{
ALLOCATE CHANNEL t1 DEVICE TYPE 'sbt_tape' PARMS="ENV=(TDPO_OPTFILE=/oracle/tdpo/TESTDB/tdpo.opt)";
RESTORE CONTROLFILE FROM AUTOBACKUP;
release channel t1;
}

Step 6. Restore and Recover the database

run
{
allocate channel t1 device type 'sbt_tape' PARMS="ENV=(TDPO_OPTFILE=/oracle/tdpo/TESTDB/tdpo.opt)";
set until time "to_date('05/10/2010 4:00','MM/DD/YYYY HH24:MI')";
restore database;
recover database;
release channel t1;
}

Step 7. Open the databse and start Listener

ALTER DATABASE OPEN RESETLOGS;

lsnrctl start

Check if everything is running on the database server -

ps –ef grep smon
ps –ef grep tns