STANDBY DB ISSUES

These are some of the issues which i have faced while managing the physical standby database:

1. ORA-00368: Checksum error
2. ORA-01119: Error in creating database file


1. ORA-00368: Checksum error
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 10241 change 2700 time 11/22/2007
ORA-00334: archived log: '/archivelog/oradb1_arc_10444_596803436.arc'

Reason for the above error:
If checksum of the archive file at the production and standby
database is different. Compare the checksum using md5sum.

Solution:
a. If archive log file is present at the production box.
Then move the file to standby box.
b. Else Restore the archive log file from RMAN backup.
RMAN> restore archivelog logseq=42603 thread=1;
Transferred the file to standby box.
c. Make sure the checksum of the file ar prod and standbby box is same.
Use md5sum command.
d. Register the logfile:
SQL> alter database register or replace logfile '/archivelog/oradb1_arc_42603_596803436.arc';

2. ORA-01119: Error in creating database file
ORA-01119: error in creating database file '/opt/oraprod/tbs2.dbf'
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Linux-x86_64 Error: 13: Permission denied
Some recovered datafiles maybe left media fuzzy

Reason for the above error:
File system of production and standby database were different. So when New datafile '/opt/oraprod/tbs2.dbf'
was added to production db at the standby it could not create the same file as of production.

Solution:
At the standby database:
sql> recover managed standby database cancel;
sql> select file#,name from v$datafile where name like '%UNNAMED%';
sql> alter database create datafile '/opt/oracle/product/10.2.0/DB/dbs/UNNAMED00049' as '/opt/orastd/tbs2.dbf';
sql> recover managed standby database disconnect from session;

To avoid the above error just execute this statement:
sql> alter system set STANDBY_FILE_MANAGEMENT=auto scope=both;

RMAN Resoter and Recover

1. The whole database
RMAN> startup force mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

2. Tablespace
RMAN> sql 'alter tablespace tbs1 offline';
RMAN> restore tablespace tbs1;
RMAN> recover tablespace tbs1;
RMAN> sql 'alter tablespace tbs1 online';

3. Datafile
RMAN> sql 'alter database datafile 5 offline';
RMAN> restore datafile 5;
RMAN> recover datafile 5;
RMAN> sql 'alter database datafile 5 online';

4. Block of a Datafile
RMAN> blockrecover datafile 5 block 126;

SQL stetment of a USER

Below SQL query is used to find the SQL statement of a USER:

select sess.sid, sqltext.piece, sqltext.sql_text, sqltext.command_type
from v$sqltext sqltext, v$session sess
where sqltext.address = sess.sql_address
and sqltext.hash_value = sess.sql_hash_value
and sess.username = 'NAIKH'
order by 2;

Bloking Locks

SQL query to identify blocking locks:

1.
select s.username "Blocking Username",l1.sid "Blocking SID", l2.sid "Blocked SID",s2.username "Blocked Username"
from v$lock l1, v$lock l2,v$session s,v$session s2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2
and l1.sid=s.sid
and l2.sid=s2.sid;

2.
select l.sid SID,
decode(l.type,'TM','DML','TX',
'Trans','UL','User',l.type) Lock_Type,
decode(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',5,'S/Row-X',6,'Exclusive', l.lmode) Lock_Held_In,
decode(l.request,0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',5,'S/Row-X',6,'Exclusive',l.request) Lock_Req_In, l.ctime Duration_Seconds,
decode(l.block,0,'NO',1,'YES') Blocking
from v$lock l
where l.request != 0 or l.block != 0
order by l.id1, l.lmode desc, l.ctime desc;


Kill The Blocking Session

Find the 'serial' number of bloking session:
select
s.sid sid, s.serial# serial, s.osuser osuser, s.username username, s.module module,p.spid spid, s.process process, s.machine machine, last_call_et active_length, to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss') logontime, s.status status
from v$process p, v$session s
where s.paddr = p.addr (+) and s.sid = '&sid';

Use alter system command to kill the session:
Alter system kill session '115,10366' immediate;

Links Part 1

Some useful links:

Oracle
Oracle 9i funda1,funda2 and PT PPTs

Shell Scripting
1. Bash Guide for Beginners
2. Easy Shell Scripting
3. Advanced Bash-Scripting Guide

One Liners
1. AWK
2. AWK2
3. SED
4. Perl
5. Solaris

Linux
1. Linux in a Nutshell
2. Red Hat Linux Unleashed
3. Linux Documentation Collection

Solaris
1. System Administration Commands
2. Solaris 10 Documentation collection
3. Solaris Tips & Tricks

Unix
UNIX Unleashed