How to Recover Drop/Truncate table from database

If by mistakenly droped any table from database and our database size in TB then how to recover that droped table then we can use given below steps.

Step 1
We need valid backup of droped/Truncated table on Source.

Step 2.
Backup Transfer to another Test/Dev/UAT server.

Step 3. We make the Fresh new instance for recovery purpose and doing some entry in below file.
ORACLE_USER$vi /etc/oratab (Make the Entry in oratab file dummy Entry).

Step 4.
ORACLE_USER$. oraenv (then provide the dummy SID name that made the enty).

Step 5.
ORACLE_USER$rman target /

Step 6.
RMAN>startup nomount force ----command on rman.

Step 7.
RMAN>restore spfile from 'Backup Path'.

Step 8.
SQL>create pfile from spfile; ---command.

Step 9.
ORACLE_USER$ vi $ORACLE_HOME/dbs/init_SID(changes the some parameter values.i.e controlefile,datafile,redolog etc.)

Step 10.
SQL>create spfile from pfile.

Step 11.
ORACLE_USER$ kill -9 (pmon)

Step 12.
ORACLE_USER$. oraenv
ORACLE_USER$rman target /

Step 13.
RMAN>startup nomount.

Step 14.
restore controlfile from 'Backup Path'.

Step 15.
RMAN>alter database mount.

RMAN>run
{
set newname for datafile 1 to '/u01/app/oracle/oradata/prod/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/prod/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/prod/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/prod/users01.dbf';
restore tablespace system, sysaux, undotbs1, users;
switch datafile all;
sql "alter database datafile 1,2,3,4 online";
recover database skip forever tablespace TEMP,APEX_1680608425593888,APEX,example;
}

--------if directory same then no need to rename of redo otherwise  rename the redo log---------------
sql "alter database rename file ''/u01/app/oracle/oradata/prod/REDO01.LOG'' to ''/u01/app/oracle/oradata/REDO01.LOG''";
sql "alter database rename file ''/u01/app/oracle/oradata/prod/REDO02.LOG'' to ''/u01/app/oracle/oradata/REDO02.LOG''";

Step 16.
SQL> alter database open resetlogs.

Step 17.
use expdp/impdp

Corrupt block relative dba message in the Alert Log - Definition and Solution

What is the meaning of message "Corrupt block relative dba..." in the alert log?

message describing the block corruption:

Bad header found during buffer read
Data in bad block:
type: 2 format: 2 rdba: 0x21008dd0
last change scn: 0x0006.12114f8e seq: 0x37 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x4f8e0237
check value in block header: 0x6e5b
computed block checksum: 0x0
Reread of rdba: 0x3d808dd0 (file 246, block 36304) found same corrupted data
Errors in file /testsite/testdb/db/tech_st/11.1.0/admin/TEST_erptestappldb/diag/rdbms/test/TEST/trace/TEST_s000_11075614.trc
(incident=31537):
ORA-01578: ORACLE data block corrupted (file # 246, block # 36304)
ORA-01110: data file 246: '/testsite/testdb/db/apps_st/data/a_txn_data47.dbf'
Incident details in: /testsite/testdb/db/tech_st/11.1.0/admin/TEST_erptestappldb/diag/rdbms/test/TEST/incident/incdir_31537/T
EST_s000_11075614_i31537.trc
Wed Jun 28 11:42:12 2017
Corrupt Block Found
TSN = 10, TSNAME = APPS_TS_TX_DATA
RFN = 246, BLK = 36304, RDBA = 1031835088
OBJN = 87982, OBJD = 87982, OBJECT = OE_ORDER_HEADERS_ALL, SUBOBJECT =
SEGMENT OWNER = ONT, SEGMENT TYPE = Table Segment
Wed Jun 28 11:42:25 2017
Non critical error ORA-48913 caught while writing to trace file "/testsite/testdb/db/tech_st/11.1.0/admin/TEST_erptestappldb/
diag/rdbms/test/TEST/incident/incdir_31537/TEST_s000_11075614_i31537.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached
Writing to the above trace file is disabled for now on... 

Solution:

The corruption you are finding is misplaced block, which is a physical corruption introduced by the underlying system.

Corrupt block relative dba: 0x3d808dd0 (file 246, block 36304)
Bad header found during buffer read
Data in bad block:
type: 2 format: 2 rdba: 0x21008dd0 rdba: 0x21008dd0 != dba: 0x3d808dd0
last change scn: 0x0006.12114f8e seq: 0x37 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x4f8e0237
check value in block header: 0x6e5b
computed block checksum: 0x0
Reread of rdba: 0x3d808dd0 (file 246, block 36304) found same corrupted data 

This is fully solvable by blockrecovery operations. 

If RMAN backup is available and all archivelogs, than please run:

RMAN>recover corruption list;

Otherwise, the data in the affected blocks is lost and segments will need to be recreated.

To evaluate the overall database status, you can use:

RMAN> backup validate check logical database;

NOTE:
=====
This command will succeed if DB is running in ARCHIVELOG mode.
. If DB in NOARCHIVELOG mode, then this RMAN command *Must* be run against the database in MOUNT state.
. If you try to this RMAN command against an open DB running NOARCHIVELOG mode the following error will occur:

-> RMAN-03009: failure of backup command on d1 channel at 12/07/2009 18:55:25
-> ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

After RMAN validate finishes, the view v$database_block_corruption will be populated with all the affected blocks.

To identify the affected objects, below queries can be used:

set pagesize 2000
set linesize 280
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, corruption_type description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, corruption_type||' Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;