by Jonas Mason, Senior Consultant

This blog covers how to use the command line to convert Oracle trace files generated on a source instance into Tool Command Language (TCL) files for replay of SQL on a destination instance. Converting Oracle trace files to TCL files using the HammerDB GUI takes time and is a bottleneck, as I blogged about in my prior post on HammerDB’s capture and replay feature. Steve Shaw, HammerDB’s developer, provided this command line alternative to the GUI for conversion purposes. The solution was used to convert over 300 trace files to TCL files in about 10 minutes; using the GUI for this same task would have taken over 20 hours.

Converting Oracle Trace Files to Tool Command Language

This section assumes you have installed HammerDB on a Linux server per HammerDB’s documentation.

I am converting 300 trace files previously generated from all sessions in a production Oracle 10g environment during a one-hour period. These trace files were generated by enabling a trigger provided in the HammerDB documentation at the start of the capture period. The trigger was disabled at the end of the capture period. Keep in mind that it is important to monitor storage consumed by trace files written during this capture period.

CONVERT.TCL
I used the convert.tcl file, provided by Steve Shaw, to parse an Oracle trace file and return a TCL file format containing SQL for replay purposes. This convert.tcl file is placed in the HammerDB home. Prior to converting any trace files, you will need to update the string highlighted in bold with the username and password you require for the destination instance.

Download convert.tcl file

foreach line $filelist {
if {[string match {*Instance name*} $line]} {
regexp {(:)\ (.*)} $line a b c
if { $donehead == 0 } {
append ora "#!/usr/local/bin/tclsh8.6\n"
append ora "package require Oratcl\n"
append ora "####UPDATE THE CONNECT STRING BELOW###\n"
append ora "#set connect user/password@$c\n"
append ora "set lda \[oralogon \$connect\]\n"
set donehead 1
}
}

Update $c with a hard coded value if this destination replay instance will differ in name from the capture instance. Otherwise, leave it, or insure an identical service is setup on the destination environment that resolves to the destination SID:

append ora "set connect hob_jmason/abc123@$c\n"

Copy the trace files to be converted into the HammerDB directory home from their source.

Change directory to your HammerDB home:

#cd /usr/local/HammerDB-2.23/

Test the conversion of a single trace file with the command line entry in bold below:

# ./bin/tclsh8.6 convert.tcl prod_ora_19628.trc
#!/usr/local/bin/tclsh8.6
package require Oratcl
####UPDATE THE CONNECT STRING BELOW###
set connect hob_jmason/abc123@PROD
set lda [oralogon $connect]
set curn3 [oraopen $lda ]
set sql3 "SELECT PARAMETER, VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER IN (:\"SYS_B_0\", :\"SYS_B_1\") "
orasql $curn3 $sql3 -parseonly
orabindexec $curn3 :SYS_B_1 {NLS_NCHAR_CHARACTERSET} :SYS_B_0 {NLS_CHARACTERSET}
set row [orafetch $curn3 -datavariable output ]
while { [ oramsg $curn3 ] == 0 } {
puts $output
set row [orafetch $curn3 -datavariable output ]
}

You should see TCL output if all is successful.

Note: Troubleshooting efforts I encountered included LD_LIBRARY_PATH and package version issues. I used a local CentOS vm to get past this and other issues that I didn’t want to spend time on.

Repeat the step above, but direct the output to a file instead:

# ./bin/tclsh8.6 convert.tcl prod_ora_19628.trc > prod_ora_19628.tcl

Take the tcl file generated, and move this to the destination replay server. Launch your HammerDB GUI and select the tcl you just created. Generate a User and test the replay for troubleshooting purposes to validate success.

Now that you have confirmed the successful execution of a single TCL file, create a script to loop through all of the Oracle trace files generated in the capture phase in order to output results to unique tcl filenames. You should have one unique tcl file for each trace file generated.

Conclusion

If you have 30 or more trace files to convert to HammerDB TCL scripts for replay purposes, the command line approach is a significant time saver over the HammerDB GUI. If the capture and replay process needs to be repeated often, and without significant manual labor, this command line approach can be adopted and further refined.

In next week’s blog I will cover how to execute these 300 TCL files on the destination server using a HammerDB command line approach. This was also a much more efficient approach compared to using the GUI and should be considered when over 30 TCL files need to be replayed.

Please note: this blog contains code examples provided for your reference. All sample code is provided for illustrative purposes only. Use of information appearing in this blog is solely at your own risk. Please read our full disclaimer for details.