I need to load a database dump into a target database, on Sybase ASE 16. In order to do that, the target database must be offline. Sybase ASE doesn't have a command to put a database offline, so my only possibility is to stop the DB server and restart it, taking care that the target database is not brought online during startup. This is my question: which command/option do I need to set so that a specific database is not brought online at server startup? I couldn't find it in the official documentation đ
How to Mark a Sybase ASE Database as Offline for Startup
sybasesybase-ase
Related Solutions
Ok ! Let me describe how this issue was solved:
1) There was definitely a corruption issue caused by a problem with vmware tools on solaris 10. When the network interface had high transfer/load operations (sample: copy of a 2 GB DB ....), it just stoped working, in the middle of the operation. To put the interface working again, I had to disconnect and connect the network interface again (in the vmware interface !). Basically, I had to unistall vmware tools on solaris virtual machine. There was one catch, the highest transfer rates that could be achieved was about 300 Kb. Basically I could take hours to perform a simple ftp transfer of a 2 GB database, but there wasn´t any corruption at all. How to prove/test that there is/inst´t any corruption. I just packed (on the source machine) the database dump into a tar file (yes, an extra 20kb), but after the download complete, on the target server, I was able to untar the file, and if the untar operation was sucessfull that proved that there wasn´t any corruption on the file that was just transfered.
2) After being sure that the dump was ok, I got a diferent error:
Apr 17 14:24:20 2012: Backup Server: 4.188.1.1: Database wfcv2: 158936 kilobytes (1%) LOADED.
Apr 17 14:24:47 2012: Backup Server: 4.188.1.1: Database wfcv2: 303212 kilobytes (2%) LOADED.
Apr 17 14:25:16 2012: Backup Server: 4.188.1.1: Database wfcv2: 447104 kilobytes (3%) LOADED.
Apr 17 14:25:39 2012: Backup Server: 4.124.2.1: Archive API error for device='compress::1::/data4/20120413_wfcv2_zdump::000': Vendor application name=Compress API, Library version=1, API routine=syb_read(), Message=syb_read: gzread() error=0, msg=Error 0
Apr 17 14:25:39 2012: Backup Server: 4.124.2.1: Archive API error for device='compress::1::/data4/20120413_wfcv2_zdump::000': Vendor application name=Compress API, Library version=1, API routine=syb_close(), Message=syb_close: gzclose() error=-3 msg=Input/output buffer is corrupt
Apr 17 14:25:39 2012: Backup Server: 6.32.2.3: compress::1::/data4/20120413_wfcv2_zdump::000: volume not valid or not requested (server: , session id: 20.)
Apr 17 14:25:39 2012: Backup Server: 1.14.2.4: Unrecoverable I/O or volume error. This DUMP or LOAD session must exit.
Ok ! Sybase configuration issues !
I had to configure some parameters that were related to load operations, such as:
number of large i/o buffers -> 32 max memory
And a Solaris issue !
I also had to adjust the Operating System Shared Memory to the sybase engine ... !
And I finally was able to load the DB (size > 2.1 GB) !
;-) Cheers !
Yes is a good idea, if the DB is spread across different devices. ( that ideally would be on different volumes ). The percentage of the volume of corruption would be less. However to restore you still have to restore the whole DB, which in separate devices and volumes can be faster. ( I/O is spread, less concurrency )
No. You have to recover the Db(s) that the corrupted device holds. So if you have a DB that has 1 corrupt device and you have those logical connections (good practice), you just need to change on OS, the connection to the new physical device. Then restore DB.
How is you replication ? Repserver or disk mirroring? For repserver, i do not think so, your source ASE would stop functioning, and you would have to stop replication to restore the db. Disk mirroring I don't know.
Response to updated question:
My point 2 was:
- You will need to recover the whole db, unless you know exactly which objects where present on that device (and segments of it) that got corrupted, if yes then you can rebuild them manually( tables, procs, views... ). If you don't know or the labour to do it manually is too great, complete db recover.
What I mentioned about the logical connections was this, for example:
You have DB called TEST1 on the the following devices:
data01
data04
log02
data03
When you create the device with disk init, you have to give the path. But the path is not directed to the device itself, it goes to a soft link. Your data01 for example:
disk init.... physname='..sybase/data01.dat" ...
On the OS that sybase/data01.dat will be pointing to /dev/data01.dat. This way if you need on the OS to replace a corrupted device, you won't have to rebuild the database, you just create a new raw file, point data01.dat to it, and on DB restore data (LOAD). Faster process, than drop old DB, create new, load.
Well I'm pretty sure your errorlog would have some information about that corrupted device and also about your dumps had issues. If your dumps were successfully dumped you should be able to use them on your restore (good devices). So I don't really get why you had to go back so many days. The thing is for instance if you have a corrupted device, it will manifest whenever some action tries to use it, (I/O). So your dumps would have failed i believe.
Related Question
- Sybase ASE 12 – Common Questions and Solutions
- Sybase ASE server request logs
- Sql-server – SQL query output to Sybase temp table
- Sybase ASE Replication – Problem Replicating IDENTITY Columns
- Sql-server – Numbered stored procedures: introduction and deprecation
- Sybase ASE – Log Backup Size Not Reducing for Past Three Days
- Sybase ASE – How to Restore One Table from Full Backup
Best Answer
Thanks to Learning_DBAdmin for correcting me: the database does not have to be offline, the load process was failing due to remaining connections of other users on this target database. After killing the offending connections and putting the database in single user mode, it was possible to load a dump into it.