on 2023 Jun 23 10:30 AM
Is it possible to start a database read-only when it needs recovery?
I have a DB which stopped with "max file size reached", so DB says it needs recovery when I try to start it. So my wish would be to start the DB read-only to do an unload. Any advice?
Hitting the max file size means that you are either on an old FAT filesystem or your dbspace file is very large. On NTFS with a 4K database page size you should be able have dbspace files up to 1TB in size. If you are not using a filesystem that supports large files, you could copy everything to a filesystem that does.
Does the database fail to recover if you just start it in normal mode?
I don't think you can start a database in read-only mode if it needs recovery. You could probably use in-memory mode though with '-im nw' which shouldn't touch the file and modifications will be kept in memory. I vaguely recall that "nw" mode requires a different licence. There is also "-im v" mode (validation mode) which doesn't require a separate licence and allows writes by recovery but then is read-only. That said, we still "logically" allocate pages when using in-memory mode so you might still hit the same error.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Okay. You can try the in-memory modes but I suspect they won't help you. If they don't work we might have other options. First, I would recommend that you have a saved copy of the existing database files. Then you might proceed as follows:
Rename the transaction log to anything different and then start the server with "-f" on the command line. The server will then not replay transactions that were in the log after the last checkpoint. IIRC, the server will then shutdown automatically and you should then start it again but without -f. It might fail again with the same error at this point as it will now attempt to apply the undo logs. You can use dbtran to find the transactions that were committed after the last checkpoint.
If the above fails, start the server with -O (capital "o") on the command line. This will tell recovery not to apply the undo logs. Do not do this lightly: ideally do it on a copy of the database. If undo logs are not applied then your database might be inconsistent since partial transactions will effectively be committed.
Approach 1 worked, the database can be started now and we will do the unload, thanks for your help!!
According to that question and that one, I'd say "no" - but I would try to start it in read-only mode (probably with a copy of that database) anyway...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Well, in generally in RDBMS it is generally not possible to start a database in a read only mode when it needs recovery. Whenever database required recovery it means that there is the problem with the consistancy of database which you can further perform by taking appropriate steps that you can find over Google. Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
69 | |
9 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.