Get the client IP address from a session in oracle

Get the client IP address from a session in oracle .  There are two way to get IP

•  SYS_CONTEXT('USERENV','IP_ADDRESS')
•  UTL_INADDR.GET_HOST_ADDRESS

Query1 : using :  SYS_CONTEXT('USERENV','IP_ADDRESS')
=============================================================

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP,
v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
ORDER BY logon_time ASC;


Query2: using  UTL_INADDR.GET_HOST_ADDRESS (SUBSTR(machine,INSTR(machine,'')+1)) IP
=========================================================================================

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE,
UTL_INADDR.GET_HOST_ADDRESS (SUBSTR(machine,INSTR(machine,'')+1)) IP,
v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
ORDER BY logon_time ASC;

Extract Index DDL from database export dump

PAR file for index ddl extract from dump using traditional import utility
===================================================================

userid= '/ as sysdba '
statistics=none
buffer=9999999
log=/oracle/export/IND/dump/imp_index_full_10122019.log
constraints=n
grants=n
rows=n
FROMUSER=HR
indexfile=/oracle/export/IND/dump/index3.sql
Tables=(EMPLOYEES)
file=(/oracle/export/IND//exp_full_IND.dmp)


To execute the command in background 
===========================================

nohup imp parfile=file_name.par &

Enable Trace in oracle for SID

Get SID :
================

column SID for a20
SELECT sys_context('USERENV', 'SID') SID FROM DUAL;


Enable Trace for SID  in oracle 
==================================


SQL> select username,sid,serial# from v$session where sid=22;

USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
SYS                                    22       2895

SQL>




alter session set tracefile_identifier='anurag_sql_trace';

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>22, serial#=>2895, sql_trace=>TRUE)

---To disable

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

--- Get the tracefile name:

SELECT p.tracefile FROM   v$session s  JOIN v$process p ON s.paddr = p.addr WHERE  s.sid = 22;

TRACEFILE
------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9699.trc

Manually Corrupting Oracle Data Block & Recover using RMAN

What is data block corruption?

A database block is corrupted when its content has changed from what Oracle Database expects to find. If not prevented or repaired, block corruption can bring down the database and possibly result in the loss of key business data.

Prerequisite :

• Audiance of this document should be experienced DBA
• Database should be in archive log mode and have good backup

Procedure of work :

• Create tablespace  using sysdba
• create user  using sysdba
• create table  using test user 
• insert records  using test user
• check the header of table segment 
• manually corrupt the block on terminal 
• flush the buffer cahce  as sysdba
• Disconnect and re-connect test user and query the table you will get block corruption error 

To get information about datafiles exists in database .

select file_name from dba_data_files;

create tablespace useing below command :

create tablespace app_corrupt datafile '/u01/app/oracle/oradata/risdev/app_corrupt01.dbf' size 5m;

create user as follow below command

create user test identified by test123
default tablespace app_corrupt
quota unlimited on app_corrupt;

grant create session, resource to test;


Login using test user account :

create emp table :

create table emp(eno number(8)) tablespace app_corrupt;

Insert records in emp table :

begin
for i in 1..1000000
loop
insert into emp values(i);
end loop;
end;


select count(*) from emp;


SQL> SELECT header_block FROM dba_segments WHERE segment_name='EMP';

HEADER_BLOCK
------------
         130

SQL>

Take tablespace backup using RMAN:

backup tablespace app_corrupt;

Mannualy Corrupt the datafile block using below command:

dd of=/u01/app/oracle/oradata/testdb/corrupt_block_test.dbf bs=8192 conv=notrunc seek=130  EOF
testing corruption
EOF
0+1 records in
0+1 records out
19 bytes (19 B) copied, 0.000133601 s, 142 kB/s


[oracle@localhost ~]$

SQL> select count(*) from emp;
select count(*) from emp
       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 130)
ORA-01110: data file 5: '/u01/app/oracle/oradata/risdev/app_corrupt01.dbf'


SQL>

connect with RMAN and validate tablespace :

RMAN> validate tablespace APP_CORRUPT;

Starting validate at 05-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/u01/app/oracle/oradata/risdev/app_corrupt01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1            640             1669173
  File Name: /u01/app/oracle/oradata/risdev/app_corrupt01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              496
  Index      0              0
  Other      1              143

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/risdev/risdev/trace/risdev_ora_24069.trc for details
Finished validate at 05-SEP-19

RMAN>


SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
         5        130          1                  0 CORRUPT            0

SQL>


Recover corrupt block using RMAN:

RMAN> blockrecover datafile 5 block 130;

Starting recover at 05-SEP-19
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/db/dbs/02ub15f9_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/db/dbs/02ub15f9_1_1 tag=TAG20190905T185433
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 05-SEP-19

RMAN>



SQL>
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

no rows selected

SQL>