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>

Adding a Datafile/Tempfile to Temp Tablespace

Connect with oracle database and check tempfile location & information :

[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 5 14:58:33 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set pages 999
set lines 400
col FILE_NAME format a50
col TABLESPACE_NAME format a10
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;SQL> SQL> SQL> SQL>   2    3    4    5

TABLESPACE FILE_NAME                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
---------- -------------------------------------------------- ---------- --- ---------- ---------------
TEMP       /u01/app/oracle/oradata/risdev/temp01.dbf                  32 YES 32767.9844            .625

SQL>

Add tempfile to temp tablepsace : 

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/risdev/temp02.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

Tablespace altered.

SQL>



Verify added tempfile : 



SQL> set pages 999
set lines 400
col FILE_NAME format a50
col TABLESPACE_NAME format a10
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;SQL> SQL> SQL> SQL>   2    3    4    5

TABLESPACE FILE_NAME                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
---------- -------------------------------------------------- ---------- --- ---------- ---------------
TEMP       /u01/app/oracle/oradata/risdev/temp01.dbf                  32 YES 32767.9844            .625
TEMP       /u01/app/oracle/oradata/risdev/temp02.dbf                5120 YES      32767            1024

SQL>

* Make sure enough space available at mount point .

Query:

set pages 999
set lines 400
col FILE_NAME format a50
col TABLESPACE_NAME format a10
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#

order by d.TABLESPACE_NAME, d.FILE_NAME;

Find Out Virtual Memory PAGESIZE on Linux

[root@localhost ~]# getconf PAGESIZE
4096
[root@localhost ~]# getconf PAGE_SIZE
4096
[root@localhost ~]#

[root@localhost ~]# getconf -a | grep -i pagesize
PAGESIZE                           4096
[root@localhost ~]#