Posted by Q : There is a database called “A” and transaction log file size is 200 MB and transactions are getting committed slowly to fill up the transaction log and assume one log per hour .
Now scenario, incremental backup is completed on day at 9 a.m., with the next transaction log generated and committed at 10 a.m., 11 a.m., and 12 a.m. By 12:30, the log size had filled up to 100MB, and the database was waiting to flush the transaction log to the archive site.
if DB requests a restoration until 12:30 a.m. Is it possible to restore until the latest transaction in the same “A” system? or in a different system “B”
Answer :
The answer for both cases “YES” .
Solution 1 : Issue below command to move the transaction log from active directory to archive directory manually
db2 archive log for database <<DBNAME>>
then rollforward the DB name using local time :
Pros and cons for db2 archive log for database <<DBNAME>>
This command can be used to collect a complete set of log data up to a known point. The log data can then be used to update a standby database.
If log data up to the time the ARCHIVE LOG command is issued is in the middle of a log file, this log file will be truncated and logging will continue on the next file.
This command can only be executed when the invoking application or shell does not have a database connection to the specified database. This prevents a user from executing the command with uncommitted transactions. As such, the ARCHIVE LOG command will not forcibly commit the user’s incomplete transactions. If the invoking application or shell already has a database connection to the specified database, the command will terminate and return an error. If another application has transactions in progress with the specified database when this command is executed, there will be a slight performance degradation since the command flushes the log buffer to disk. Any other transactions attempting to write log records to the buffer will have to wait until the flush is complete.
Solution 2 :
Reduce the size of the log file as much as feasible.
Solution 2 is, in my opinion, the finest practice.
Please feel free to send feedback or remarks.
DayDreamsDatabases 