Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

we must set our database from CIRCULAR to ARCHIVE mode in order to enable ONLINE backups. Backups will be done every 1 day. Database is now almost 30GB. Database grows approximately 1GB per month. For online backup we will use Veritas NetBackup so we use its library file in the LOGARCHMETH1 parameter for the VENDOR option. Time to restore is not so important parameter, it is important to everything restore from previous day when everything was OK.

Can you please assist me to set those three parameters on appropriate value? This is production database with important data.

LOGPRIMARY and LOGSECOND are set to values 13 and 15 by default but I think it is not enough? Is it to large if I set them to 50 for example? What can be negative effect?

Current values are: 13, 15, 4096. I run Configuration Advisor and it gives me those values: 27, 7, 1024. But for many wizard questions I used default values because I do not know exact answers (for example Transactions per minute,or Average number of connected remote applications..) Why it put the lower value for LOGFILSIZ?

Can someone additionally suggest me are those good values or not ? Thank you!!!!

share|improve this question
    
I voted for Ian's answer. It is pretty thorough. –  Chris Aldrich Feb 26 at 16:07
add comment

1 Answer 1

LOGPRIMARY, LOGSECOND and LOGFILSIZ combine to define the maximum size of the transaction log on your server using the following formula:

Total Size = (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4096 bytes

In your case, this results in (13 + 15) * 4096 * 4096 = 469762048 bytes.

The maximum transaction log size limits 2 things:

1) the absolute size of a single transaction 2) the "timespan" between the oldest active transaction and the newest transaction.

You need to make it large enough to handle both scenarios, although if you're running into problems relating to #2 then you really have a problem with a poorly-behaved app that isn't committing.

Choosing an appropriate value for LOGFILSIZ should be based on how regularly you want to archive log files, as this will have an effect on your recoverability in the event of a problem. If your database archives log files only once every hour (or less), you will potentially lose much more data in the event of a serious problem than if your database archives a log file every 2-3 minutes (because every time a log file is archived it should be copied to NetBackup).

share|improve this answer
    
thank you for useful answer. I read this useful info: "The configured size of each log file has a direct bearing on archiving. If log file is very large, a large amount of data can be lost. If you configure your database to use small log files the log manager will archive the logs more frequently. However, if you are moving the data to a slower device such as tape, you might want to have larger log files to prevent the queue from building up. Using larger log files is also recommended if archiving each file requires substantial overhead, such as establishing a connection to the archive media." –  Dejan Feb 18 at 7:18
    
You wrote that Transaction log size limits the absoulute size of single transaction: 469762048 bytes. Is that mean that problem for #1 only can happen if size of single transaction is over 469 MB? We for sure do not have such large transactions if I understand you correctly what you mean. Does problem #1 mean that transaction over 469 will be wrote in database at once? I do not think it is possible in our case (as I said whole database is 30 GB) –  Dejan Feb 18 at 7:23
    
Regarding the #2.. Do you think this "timespan" is regarding the time difference when last and new transaction is performed? We do not have such big difference between transactions. for example more than 100 rows are updated /created every minute... can you do some estimation based on that? –  Dejan Feb 18 at 8:44
    
@Dejan For simplicity lets say that your transaction log can hold 500 transactions total. Lets say a transaction starts, but never commits. Meanwhile, 499 other transactions take place and are committed. Even if that first transaction is sitting idle, the fact that it hasn't committed means that it's still "active". –  Ian Bjorhovde Feb 18 at 17:17
    
@Dejan In a well behaved application neither scenario is likely to occur. However, if a developer executes something like delete from table to empty a table it may be easy to fill the log with a single transaction. –  Ian Bjorhovde Feb 18 at 17:20
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.