Can't start database in read only mode

[Former Member]

I have "borrowed" Breck Carter's concepts for backing up and validating a database that I found on his blog.  All seemed to be working well until this morning.

This is SA 12.0.1.3967 running on linux.

The failure is occurring when trying to start the validation database in read only mode.  This is after the transaction log has been applied to it.

I would simply get an error "Error:  Database cannot be started -- unable to start database...".  I'm sorry, but this error is just about useless because it gives no other details.

I tried several times to start it but always go the same error.

I saved off the backup that wouldn't start and re-ran my backup script.  That time everything worked just fine.

Anyway, I went back to the backup that wouldn't work and tried to start it again read only and still failed.  I then tried to start it normally and it started right up.  I then tried to start is read only and it started then too.

Does anyone have any clue as to why it would behave this way?

[lucjan_chmura · Accepted solution]

Bill ,

validation by default is done on read-only database file. In the case when ,a database requires recovery ( so needs to write to transaction log ) you need to start it in read-write.

Have a look at SQA Forum discussion Suggestions with dbvalid error - SQLA Forum

which talks about this.

Also, have a look at SQA documentation when (at the bottom of this page ) talks how to run validation in read-write mode.

DocCommentXchange

FYI: "

If the database you are validating requires recovery and you want to force it to start as read-write, you can either start the database before running dbvalid or specify a valid value for the DBS connection parameter. SeeDatabaseSwitches (DBS) connection parameter.

Both of the following commands allow dbvalid to run if the mycopy.db database needs to be

recovered:

dbvalid -c "UID=DBA;PWD=sql;DBF=mycopy.db;DBS=-n mycopy"

dbvalid -c "UID=DBA;PWD=sql;DBF=mycopy.db;DBS=-n mycopy"

Hope it helps.

Lucjan

[Former Member · Accepted solution]

Prior to starting the database in read only mode, I apply the transaction log to the database and that is successful.  I then try to start it again in read only mode so I can run dbvalid against it.  Starting the database in read only mode is what is failing.  Since that kept failing, I started it normally and that is successful.  I then was able to start it in read only mode.  It was weird.

Here's the link to the example on Breck Carter's blog 

http://sqlanywhere.blogspot.com/2011/04/demonstrating-backup-validation-and.html

I'm not using the example verbatim, but I am using the same type of logic flow as his example.

[lucjan_chmura · Accepted solution]

I think the key is that you have uncommitted transactions. Database will not start in read-only since it needs to recover , therefore write into the transaction log.

Your option is to start in read-write and then discard validated copy of the database.

In Breck's script , you would need to remove "-r" from this step:

050."%SQLANY12%\bin32\dbspawn.exe"^

051.  -f "%SQLANY12%\bin32\dbsrv12.exe"^

052.  -n readonlycopy^

053.  -o backup\dbsrv12_readonlycopy_log.txt^

054.  -r^

055.  backup\generation_temp\validate\ddd12.db

Lucjan

[Former Member · Accepted solution]

So even though I started the database once with the "-ad" option to apply the transaction log to it (and I thought do the recovery), I would still need to start is without the "-r" option after that?

[lucjan_chmura · Accepted solution]

If you use "-ad" transaction logs are applied but database is not started.

If you want to start it after logs are applied then use : "-ad <dir> -as" , e.g. :

"dbsrv16 "c:\mysample.db" -ad "c:\backup" -as"

however, you won't be able to apply anymore transaction logs after that.

[Former Member · Accepted solution]

I understand that the database stops when the "-ad" option is used after the transaction logs are applied.  What puzzles me is I thought this option recovers the database and applies all transaction logs.  I would have thought at this point, I could then restart the database with the read only option.

[lucjan_chmura · Accepted solution]

Server needs to rollback all uncommitted transaction after is started. This operation needs to be written to the current transaction log , hence server needs to have read-write access.

[Former Member · Accepted solution]

Ok.  I'll take a look at handling that.

Thanks

[Former Member · Accepted solution]

Since the database I'm trying to validate is a copy of the actual backup that I do not intend to keep and no other database connections will exist, is it safe to start the database in normal mode instead of read only and validate it?  The whole purpose is I'm trying to make sure my backup database is valid.

[lucjan_chmura · Accepted solution]

That's fine as long a you know what you are doing. The whole purpose of running validation in read-only mode is to make sure there is nothing else going on the database while validation is running , no update/deletes/events etc..

This change (running validation in read-only ) was actually added in version 11 to prevent common user mistakes that could lead to a backup copy being unusable.

On the other hand, if you really want in read-only you could, but you would need to start this db and let it recover first (rollback all transactions) and then stop it.

An easy way to do this is using "dbping -d -c <conn>" commmad.e.g.:

dbping -d -c "uid=dba;pwd=sql;dbf=demo12.db"

would start demo.db  (run through recovery) , then make a connection and shutdown.

[Former Member · Accepted solution]

Thanks for the info.

So based on the fact I am:

1. Copying the backup database files to a validation directory
2. Starting the validation database with "-ad" option to apply the transaction log
3. Starting the validation database to allow dbvalid to run
4. Running dbvalid against the validation database
5. Stopping the validation database
6. Deleting validation directory

I should be fine with starting the database in normal mode in step 3 above instead of read only.

[lucjan_chmura · Accepted solution]

Yes , you should be fine assuming like I said , there is nothing running on your database while you are running validation. For example, there is no database events that could start automatically on schedule.

Also, please have a look at this doc posted on our WIKI site:

Backup, Validation, and Recovery (Restore) - SQL Anywhere - SAP SQL Anywhere - SCN Wiki

[Former Member · Accepted solution]

Ugh, I did not think about database events that may change the database.  That means I definitely need a read only database.  I'm going to look at your dbping suggestion.

Thanks for all of the help.

[johnsmirnios · Accepted solution]

You should be able to guarantee that your database can be validated in read-only mode if you use the WAIT BEFORE START clause. See DocCommentXchange. Rolling back transactions requires writing to the database file itself, not the transaction log. So, if there is an uncommitted transaction at the time the backup starts, the backup database cannot be started read-only.

If you do a step of applying logs with -ad, you may not be able to start up the database in read-only mode unless you also use WAIT AFTER END (which can be dangerous since the backup will wait indefinitely for all transactions to complete). The problem here is that the logs you apply may have uncommitted transactions in them. So, although the backup copy of the database file itself has no uncommitted transactions due to the WAIT BEFORE START, applying logs without WAIT AFTER END could introduce some uncommitted transactions.

Validating in read-only mode was primarily introduced to avoid the need to make an extra copy of the database in order to do the validation because once you start a backup in read/write mode, you can't use it for your recovery strategy. If you are willing to make a copy, you don't need to worry about read-only databases.

Whether you are concerned about the possible effects of events when doing validation is up to you. If this is a known database with known schema, your validation script could disable any events that could cause problems: the database is going to be thrown away anyways.