What can prevent a database from being opened in read-only mode that won't stop it from being opened normally?
The situation here is that an online backup is being done weekly, then the backed-up log is being applied to the backed-up database file successfully - and reporting "Recovery complete" afterwards. We are then applying periodic log backups to that database during the course of each day. Each night we start the (backed-up) database in read-only mode and validate it. Then we carry on applying logs etc.
We are finding that occasionally (one in ten times perhaps) we cannot start the database in read-only mode (whether directly, dbsrv16 ..., or indirectly dbvalid ...). In each case the error we get is:
SQL error (-82) -- Unable to start specified database: unable to start database "f:\databases\iqx\pears.db"
No other information is given as to why the database cannot start, either in the server log or in the Windows Event log. We tried adding the -z parameter in case that gave more information, but it doesn't.
One can then find that after further logs are applied the database can then be started read-only (and validated) without a problem.
The help docs say:
Databases that require recovery cannot be started in read-only mode. For example, database files created using an online backup cannot be started in read-only mode if there were any open transactions when the backup was started, since these transactions would require recovery when the backup copy is started
In this case though, the last thing that happened to the database (ie applying logs) before trying to start it in read-only mode concludes with "Recovery complete" - so presumably the database can't "require recovery".
In answers to my previous question there was the suggestion that some environmental change could be responsible, but we have tried to eliminate that possibility. First by always specifying -x none to avoid TCP/IP issues and secondly we were able to run a test starting the database in normal (ie not read_only) mode successfully. When we did that all other parameters were the same and no additional information relating to recovery appeared in the log (we couldn't then of course continue to use the database to apply logs to as the offset had changed).
Any suggestions welcome!
asked 08 Feb '16, 09:57
It's likley because of transactions that span multiple transaction logs.
You connect to the database, make a modification to the database, and leave the transaction open. dbbackup -r -t -n then runs, and in your backup directory you end up with a transaction log "160208AC.LOG" that does not have a COMMIT in it for a transaction that was started in the log. After the backup, you commit the transaction, and then run dbbackup -r -t -n again, generating "160208AD.LOG" which includes the COMMIT for the transaction started in "160208AC.LOG".
During recovery, you do the following :
dbsrv16 -a 160208AA.LOG recovery.db dbsrv16 -a 160208AB.LOG recovery.db dbsrv16 -a 160208AC.LOG recovery.db
Recovery showed no errors in any of the steps, because nothing bad happened. However, in order to be able to start the database it will require recovery, because the current version of the recovered database after applying 160208AC.LOG has a ROLLBACK log associated with it. If you attempt to start the database in read-only mode, you will get error -82, since recovery is needed. If you were then to apply the next transaction log (which contains the COMMIT of the transaction in 160208AC.LOG), you could then start the database in read-only mode, since there would no longer be a ROLLBACK log in the current version of the recovered database.
If this is a big issue for you, conisder using the -wa switch on the dbbackup command to ensure there are no open transactions in the database when the log is renamed.
answered 08 Feb '16, 12:10