Database -- How to correct an unused corrupt block.

Normally, corruptions on unsued blocks can be ignored, as Oracle will create a new block image should the block need to be used.. On the other hand; if you use rman to backup the database; rman backups can fail with ORA-19566 error..
So, if you want to format this block anyways; here is the procedure..

The logic behind this procedure is simple --> identify the block, create a table, fill it with data till you reach the corrupted block..

I suppose that you already identified the corrupted block ; so I will not write about that part..

Create a table which will be used to reformat the corrupt unused block.

create table s (
n number,
c varchar2(4000)
) nologging tablespace <tablespace name having the corrupt block> ;

Create a trigger to report/warn when the corrupt block will be reformatted.

CREATE OR REPLACE TRIGGER corrupt_trigger 

AFTER INSERT ON scott.s
REFERENCING OLD AS p_old NEW AS new_p
FOR EACH ROW
DECLARE
corrupt EXCEPTION;
BEGIN
IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
RAISE corrupt;
END IF;
EXCEPTION
WHEN corrupt THEN
RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');
END;
/

Use following loop to extent the table, we just created.. You should run this loop till the unused block will belong to the scott.s table..

BEGIN

for i in 1..1000000 loop
EXECUTE IMMEDIATE 'alter table scott.s allocate extent (DATAFILE '||'''the full path and dbf name which the corrupt block belongs to''' ||'SIZE 8K) ';
end loop;
end ;
/

You can check, whether the corrupt block belongs to our table with the following  query;

select segment_name, segment_type, owner
from dba_extents
where file_id = <Absolute file number>
and <corrupt block number> between block_id
and block_id + blocks -1 ;

After the block will belong to scott.sh table; insert data into it with the floowing loop.. 

This operation will actually reformat the block..

BEGIN
FOR i IN 1..1000000000 LOOP
INSERT /*+ APPEND */ INTO scott.s select i, lpad('REFORMAT',3092, 'R') from dual;
commit ;
END LOOP;
END;

Once the block will be refomatted , check the block corruption again...

For Db version <=10gr2
Rman> Backup validate check logical datafile <fileno>,<fileno> ;
For Db version >= 11gr1
Rman> Backup validate check logical datafile <fileno> ;
Or
Rman> validate datafile <fileno> block <blockno reported corrupt>, <blockno reported corrupt> ;
-->
SQL>Select * from v$database_block_corruption ;

Oracle E-Business Suite Starting/Stopping/Controlling Notification Mailer with PLSQL

It is possible to stop/start/control Workflow Notification Mailer using sql/plsql.. Using this method to stop/start/control Workflow Mailer, Application Database Administrators save time..

In addition to that; by using this method, it gets easy to automatize the stop/start operations of the Workflow Noficiation Mailer..

To stop/start/control Workflow Notification Mailer using sql, you need to login to the database as APPS schema owner.

Click here for the scripts..

Database 12c: -- A clear article about Oracle Multitenant Option

1. Why ?
2. What is it ?
3. How ?
-- >            https://blogs.oracle.com/ou_trainers/entry/oracle_database_12c_oracle_multitenant

Database -- Index Block Corruption/ index recreation or index rebuild

It 's better to use drop and create an index to correct an index block corrution..

As Oracle Documentation says:

ALTER INDEX ... REBUILD is faster than dropping and re-creating an index, because this statement uses the fast full scan feature. It reads all the index blocks using multiblock I/O, then discards the branch blocks. A further advantage of this approach is that the old index is still available for queries while the rebuild is in progress.

The rebuild operation does a fast full index scan, which reads the index blocks.. (consider one of the index block is corrupt..) I didnt test it, but it probably will encounter an error if an index block is corrupt.

So drop/create index seems the only solution for now.

But there is another option ALTER INDEX ... REBUILD ONLINE;

As Oracle Support Doc (When Does Offline Index Rebuild Refer To Base Table? (Doc ID 278600.1) explains, with this method accesses the table directly instead of the old index

So rebuild online becomes another solution..

Linux -- Process State Transition

Linux -- Process states and descriptions

TASK_RUNNING: The process is either running on CPU or waiting in a run queue to get scheduled.
TASK_INTERRUPTIBLE: The process is sleeping, waiting for some event to occur. The process is open to be interrupted by signals. Once signalled or awoken by explicit wake-up call, the process transitions to TASK_RUNNING.
TASK_UNINTERRUPTIBLE: The process state is similar to TASK_INTERRUPTIBLE except that in this state it does not process signals. It may not be desirable even to interrupt the process while in this state since it may be in the middle of completing some important task. When the event occurs that it is waiting for, the process is awaken by the explicit wake-up call.
TASK_STOPPED: The process execution is stopped, it's not running, and not able to run. On receipt of signals likeSIGSTOP, SIGTSTP, and so on, the process arrives at this state. The process would be runnable again on receipt of signal SIGCONT.
TASK_TRACED: A process arrives at this state while it is being monitored by other processes such as debuggers.
EXIT_ZOMBIE: The process has terminated. It is lingering around just for its parent to collect some statistical information about it.
EXIT_DEAD: The final state (just like it sounds). The process reaches this state when it is being removed from the system since its parent has just collected all statistical information by issuing the wait4() or waitpid() system call.

Linux -- D state processes & TASK_KILLABLE state

The following figure show the process state transitions:

D state is a special sleep mode..
In D state , the code can not be interrupted..
When the process in D state, actually It seems blocked from our perspective, but actually nothing is blocked inside the kernel.
For example, when a process issues an I/O operation , the kernel is triggered to run the relevant system call..
This code goes from filename to filesystem, from filesystem to block device and device driver, and then device driver sends the command to the hardware to fetch a block on disk.
The process, on the other hand ; is put in sleeping state (D). When the data is fetched, the process is put in runnable state again. After this point, the process will run(continue its work) when the scheduler allow it to.

D state processes,  can not be killed with kill signals..

The exact name for the D state is TASK_UNINTERRUPTABLE, and as mentioned in its name, the processes in this state can not be interrupted..
In the most cases, this unkillable processes appear when NFS shares are used for the IO. This is probably because the error detection in local disks are very fast when you compare it with the TCP timeout (300 seconds approx.)

The new task_killable state introduced in Linux 2.6.25 is a solution to kill these kind of unkillable processes.
Task_killable works just like the Task_interruptable + it can respond to fatal signals..
(http://www.ibm.com/developerworks/linux/library/l-task-killable/)

The functions for the TASK_KILLABLE state is below;
Reference: Ibm

• int wait_event_killable(wait_queue_t queue, condition); 
  This function is defined in include/linux/wait.h; it puts the calling process to sleep killably in queue until the conditionevaluates to true.
• long schedule_timeout_killable(signed long timeout); 
  This is defined in kernel/timer.c; this routine basically sets the current task's state to TASK_KILLABLE and callsschedule_timeout(), which makes the calling task sleep for timeout number of jiffies. (In UNIX systems, a jiffy is basically the time between two consecutive clock ticks.)
• int wait_for_completion_killable(struct completion *comp); 
  Defined in kernel/sched.c, this routine is used to wait killably for the completion of an event. This function callsschedule_timeout() for MAX_SCHEDULE_TIMEOUT (defined to be equal to LONG_MAX) jiffies if there are no fatal signals pending.
• int mutex_lock_killable(struct mutex *lock); 
  Defined in kernel/mutex.c, this routine is used to acquire mutex lock. However, if the lock is not available and the task is waiting to get the lock, and in the meantime it gets a fatal signal, the task would be removed from the list of waiters waiting for the mutex lock to process the signal.
• int down_killable(struct semaphore *sem); 
  Defined in kernel/semaphore.c, it is used to acquire the semaphore sem. If the semaphore is not available, it's put to sleep; if a fatal signal is delivered to it, it would be removed from the waiters' list and would have to respond to the signal. The other two methods of acquiring a semaphore are by using the routines down() or down_interruptible(). The function down() is deprecated now; you should use either down_killable() or down_interruptible().

Linux -- Share and Mount NFS Linux to Linux

Click here to read the document

Database -- Finding Current Buffer Cache Size of the Database Objects

http://ermanarslan.blogspot.com/p/core-dba-scripts.html

Database -- Why we need the serial# to kill a session

SID ->Session identifier

SERIAL# -> Session serial number. Used to identify uniquely a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID

To kill a session in Oracle , we use alter system kill session 'serial#,sid'

One question comes to our minds.. Why not using sid alone , as we know sid is a unique identifier..

I will write a short example scenario to explain this..

Suppose you want to kill sid 250.
And you prepared kill statement, as alter system kill session 250 (this is not a correct syntax, but suppose it s a correct synax and it is possible)
--> Here is the important part.
Just before, you execute your kill statement, suppose the the session with sid 250 closed itself. ( For example, it was a toad session, and the user closed its Toad program.).. and just after 1 second, another session is connected to the database and assigned the sid 250.
So when, you execute your kill command you will kill sid 250 , but actually you will do something wrong as sid 250 will belong to another session at that time..

So this is the need for Serial#  in kill command...