The nosqllog option in compare/repair suppresses the logging of the SQLs that need to be applied by compare or have been applied by repair, depending on the compare or repair command that is used. The file has an extension .sql and is created in the target /vardir/log directory regardless of the use of this option. This article explains the logging of SQL and delves on how that logging is affected by the option as well the pros and cons of using this option.
Here are the advantages of using the nosqllog option:
1. The confidential info is not logged in the SQL log file of the compare/repair:
By default the SQL log file gets created if the compare determines that the table is out of sync, or if repair has to fix the target table to bring it in sync with the source table. If no out of sync are applicable for either the compare or repair, the file has a header but no SQL in it. In case of out of sync the file will contain the SQL that need to be applied or have been applied. If, however, the nosqllog option is used and if out of sync exist, then the file will mention that the following SQL were applied (for repair) or will need to be applied (for compare) but there will not be any SQL present in that file except for the summary. In the following example the repair is able to fix the target table but due to nosqllog option used the SQLs are suppressed:
alvsupu07 # cat declt_6-1_ORA11GR2_10.1.23.74_p25894_01.sql
/*
* Compare Report
*
* Job ID : 6
* Schema : SHA86
* Table : TABLE1
* Repair : On
* Key Compare : Off
* Select Hint :
* Log File : declt_6-1_ORA11GR2_10.1.23.74_p25894
* Date : Mon Apr 17 10:34:04 2017
*
*/
/*
* Compare Results
*
* 5 source and 3 target rows compared successfully .
* 2 out-of-sync row(s) found and repaired.
* The table is now in sync.
* The SQL statements above were applied to bring this
* table back in sync
*
* Inserts : 2
* Updates : 0
* Deletes : 0
*
*/
alvsupu07 #
2. The speed of compare/repair will vastly improve if there are many out of sync involved as the SQL required for sync of target table are never logged though the compare/repair is still performed. Consequently the speed of replication is also improved as the compare/repair does not consume as much resources.
3. The disk space usage by the logs is greatly reduced.
Here is one of the most prominent disadvantage:
Since no SQL are logged, it is not possible to analyze the type of out of sync incurred. For compare, you can still run it again, this time without nosqllog option and get the SQL file. But if you had earlier run repair with nosqllog option, you will not get another opportunity to generate the SQL file as the target table is already repaired and out of sync do not exist.
