Dump trace files for files between SCN range and entire redo log with specified thread#,sequence#

Dump  the trace files for 84 archived log files between SCN 6725691  and 6761907

1. I got the following 84 archived log files from v$archived_log  between SCN 6725691  and 6761907 at PART ONE.

2. And on source CDB name: cdb dump the trace files for 84 archived log files between SCN 6725691  and 6761907 at PART TWO

The trace file listed in attached text file named: list4trace.txt on rws00abc

PART ONE: query on v$archived_log

NAME                                                                                                            FIRST_CHANGE#    NEXT_CHANGE#   FIRST_TIM   NEXT_TIME                                  

--------------------------------------------------------------------------------  ------------ ------------ --------- --------- 

+DATA/CDB/ARCHIVELOG/2020_02_13/thread_1_seq_898.2043.1032243497      6760204      6766743 13-FEB-20 13-FEB-20

...

+DATA/CDB/ARCHIVELOG/2020_02_13/thread_2_seq_860.2185.1032243005    6746651      6747452 13-FEB-20 13-FEB-20   +DATA/CDB/ARCHIVELOG/2020_02_13/thread_1_seq_875.2187.1032243005    6746643      6747445 13-FEB-20 13-FEB-20    +DATA/CDB/ARCHIVELOG/2020_02_13/thread_2_seq_859.2188.1032242999     6745759      6746651 13-FEB-20 13-FEB-20 +DATA/CDB/ARCHIVELOG/2020_02_13/thread_1_seq_874.2189.1032242999    6745753      6746643 13-FEB-20 13-FEB-20   +DATA/CDB/ARCHIVELOG/2020_02_13/thread_1_seq_873.2199.1032242945    6743980      6745753 13-FEB-20 13-FEB-20   +DATA/CDB/ARCHIVELOG/2020_02_13/thread_2_seq_858.2198.1032242945     6743974      6745759 13-FEB-20 13-FEB-20 

...

+DATA/CDB/ARCHIVELOG/2020_02_13/thread_1_seq_857.2292.1032242595     6720878      6729619 13-FEB-20 13-FEB-20  +DATA/CDB/ARCHIVELOG/2020_02_13/thread_2_seq_842.2298.1032242595     6720869      6728551 13-FEB-20 13-FEB-20           

PART TWO:

abc.txt stored the name of the 84 archived logs listed as above:

List4trace.txt is the list dumped trace file list with the archived log name.

And the script to get dump file:

dump.sh

=============== Script dump.sh BEGIN ============

#!/bin/sh

export dateSuffix=1

FILENAME=$1

list4trace=$2

i=0;

while read Line

do

i=$(($i+1))

echo " "

echo "Start to dump archive log ${Line} to :"

echo "____________________________________"

echo " "

sqlplus / as sysdba <<EOF

set echo on;

set feedback on;

spool /tmp//logforLine_${i}_dump.log_${dateSuffix}

alter system dump logfile '${Line}' scn min 6725691 scn max 6761907;

oradebug setmypid;

oradebug tracefile_name;

exit;

EOF

cat /tmp/test/logforLine_${i}_dump.log_${dateSuffix}

echo "Dump archive log ${Line} :" >> ${list4trace}

sed -n 8p /tmp/test/logforLine_${i}_dump.log_${dateSuffix} >> ${list4trace}

echo "____________________________________________" >>  ${list4trace}

echo " " >> ${list4trace}

echo "ENDEEENDEEEEENDEEEEEENDEND"

echo " "

done < $FILENAME

#cat ${list4trace}

echo " "

echo "List of dump trace at ${list4trace}"

================= Script dump.sh END ===============

Usage example:

abc.txt:

+DATA/CDB/ARCHIVELOG/2020_02_13/thread_2_seq_860.2185.1032243005    6746651      6747452 13-FEB-20 13-FEB-20   +DATA/CDB/ARCHIVELOG/2020_02_13/thread_1_seq_875.2187.1032243005    6746643      6747445 13-FEB-20 13-FEB-20 
...

+DATA/CDB/ARCHIVELOG/2020_02_13/thread_2_seq_842.2298.1032242595     6720869      6728551 13-FEB-20 13-FEB-20   

touch  /tmp/list4trace.txt

Bash dump.sh /tmp/abc.txt  /tmp/list4trace.txt

Will get list4trace.txt:

Dump archive log +DATA/CDB/ARCHIVELOG/2020_02_13/thread_1_seq_898.2043.1032243497 to :
/scratch/oracle/base/diag/rdbms/cdb/cdb1/trace/cdb1_ora_15053.trc
___________________________________________________________________________________
 
Dump archive log +DATA/CDB/ARCHIVELOG/2020_02_13/thread_2_seq_883.2042.1032243499 to :
/scratch/oracle/base/diag/rdbms/cdb/cdb1/trace/cdb1_ora_15071.trc
___________________________________________________________________________________
 
Dump archive log +DATA/CDB/ARCHIVELOG/2020_02_13/thread_2_seq_882.2056.1032243467 to :
/scratch/oracle/base/diag/rdbms/cdb/cdb1/trace/cdb1_ora_15158.trc

 

Dump redo for archive log for specified thread and sequence

Please dump redo for archive logs in the following range:

- thread 1, sequence 857 to 898.

- thread 2, sequences 842 to 883.

Please do NOT specify min scn ... max scn ... in the dump command.

Here is the list for thread 1 sequence number between 857 and 898, total 42 archived logs: rws00abc:/tmp/threadOne.log

And dumpped trace file name with archive log name list at :/tmp/Thread/ThreadOneTrace.txt

 

The list for  thread 2 sequence number between 842 and 883, total 42 archived logs: rws00abc:/tmp/threadTwo.log

And dumpped trace file name with archive log name list at :/tmp/Thread/ThreadTwoTrace.txt

 

Script to get the dump trace:

 

==============dumpThread.sh begin=================

 

#!/bin/sh

export dateSuffix=`date "+%Y%m%d%H%M%S"`

td=$1

FILENAME=$2

list4trace=$3

 

i=0;

 

while read Line

do

i=$(($i+1))

echo " "

echo "Start to dump archive log ${Line} for Thread ${td}"

echo "_________________________________________________"

echo " "

sqlplus / as sysdba <<EOF

set echo on;

set feedback on;

spool /tmp/test/Thread_${td}_logforLine_${i}_dump.log_${dateSuffix}

alter system dump logfile '${Line}';

oradebug setmypid;

oradebug tracefile_name;

exit;

EOF

cat /tmp/test/Thread_${td}_logforLine_${i}_dump.log_${dateSuffix}

echo "Dump archive log ${Line} :" >> ${list4trace}

sed -n 8p /tmp/test/Thread_${td}_logforLine_${i}_dump.log_${dateSuffix} >> ${list4trace}

echo "____________________________________________________" >>  ${list4trace}

echo " " >> ${list4trace}

echo "EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE"

echo " "

done < $FILENAME

 

#cat ${list4trace}

echo " "

echo "List of dump trace at ${list4trace}"

 

================dumpThread.sh end==================

 

Usage to run the script:

Touch /tmp/Thread/ThreadOneTrace.txt

Touch /tmp/Thread/ThreadTwoTrace.txt

bash dumpThread.sh 1 /tmp/threadOne.log /tmp/Thread/ThreadOneTrace.txt

bash dumpThread.sh 2 /tmp/threadTwo.log  /tmp/Thread/ThreadTwoTrace.txt