Take DB2 offline backup when it’s part of DB2 HADR

DB2 SQL -1035 Error (SQL1035N) database is currently in use

Submitted by Vikram Khatri on Fri, 2007-08-31 01:36.

Did you get this DB2 SQL1035N error if you were trying to take offline backup? The error is obvious as someone is connected to the database and you cannot take offline database backup.

There are several solution that you can take to do offline backup when you get SQL -1035 error. They are described as below:

Method – Brute Force

C:>db2_kill
C:>db2start
C:>db2 backup database sample to C:db2backup

The above method is a brute force method and is not recommended. You may get unexpected results at the expense of even corrupting the database. It is not guaranteed that above will work always. You may still get SQL1035N error even after db2_kill. You try to now kill every process with “kill -9” command and still it is not guaranteed to work. It is not at all recommended to use this brute force method.

Method – Mild Force

C:>db2 force applications all
C:>db2stop force
C:>db2start
C:>db2 backup database sample to C:db2backup

The above method is less brute force but you may not be able to use this if there are other databases in your instance. Stopping instance to just take a database backup is not acceptable if there are other databases in the instance.

Method – Correct way

Scenario – Application server has retry logic

Let us say, you want to take offline backup of your database and there is an application server which is designed to connect to the database as soon as it becomes available. The application server has retry logic and it will keep on trying making a connection to a database but your intent is to take an offline database backup. You are stuck as you have to ask your application server administrator to shut down the application server so that you can take up the backup. This may not be sometime possible to get your application server down. The above brute force methods also do not work in this circumstance. The following is the correct way to take offline backup of the database under this scenerio.

db2 CONNECT TO SAMPLE
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
db2 TERMINATE
db2 DEACTIVATE DB SAMPLE
db2 BACKUP DATABASE SAMPLE TO "C:DB2Backup" WITHOUT PROMPTING
db2 CONNECT TO SAMPLE
db2 UNQUIESCE DATABASE
db2 TERMINATE

Let us examine what we did in above example:

1. We did not stop instance.
2. We did not kill db2
3. We quiesced the database and forced connections out if we had to kill the sessions connected to SAMPLE database.
4. We terminated the connection so that db2bp process goes away.
5. We deactivated the database
6. We took the backup.
7. Since we quiesced the database, the application server’s retry logic will not be able to make connections to the database.
8. After successful database backup, do not forget to unquiesce the database.

Scenario – I just need to be able to take database backup

db2 CONNECT TO SAMPLE
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
db2 UNQUIESCE DATABASE
db2 TERMINATE
db2 DEACTIVATE DB SAMPLE
db2 BACKUP DATABASE SAMPLE TO "C:DB2Backup" WITHOUT PROMPTING

The above method is a graceful method to take offline backup on a system that gets used heavily. I have seen DB2 DBAs using brute force methods as they get frustrated due to following:

1. There is no connection to the database
2. No one is trying to connect
3. Why am I still getting SQL1035N error?

The answer to above question is – If you continue to get SQL1035N error even if there are no connections, the ONLY way you can take offline backup is to deactivate the database and take the backup. For offline backup, the only condition that you need to meet is : The instance should be up and running and still you can take offline backup if you are the only one connected.

For example:

C:>db2start
C:>db2 connect to sample
C:>db2 backup db sample TO C:DB2Backup

If there is no one connected to the database except you and still you were able to take offline backup. Open up another db2 window and make a connection to sample database and try to take a offline backup, you now get SQL1035N error.

Hope this solves your issue of SQL1035N error in your db2 database.

Reference:

http://www.db2ude.com/?q=node/43

http://www.db2ude.com/?q=blog/1

Advertisements

About Ishtiaque

I am IBM Certified Infrastructure Systems Architect, Linux Foundation Certified System Administrator, Oracle Certified Programmer in Java and Web Component Developer, and TOGAF 9 certified with over 10 years of support and development experience in IBM middleware software and Java. Additionally, have a sound grip in databases and OpenStack administration. I hold the following certifications: IBM Certified Infrastructure Systems Architect Linux Foundation Certified System Administrator (LFCS) TOGAF 9 Certified Oracle Certified Expert, Java EE6 Web Component Developer Oracle Certified Professional – Java 6 Programmer ITIL v3 Foundation Certified IBM Certified Solution Architect – Cloud Computing Infrastructure V1 IBM Certified System Administrator – WebSphere Portal V8, V7, V6.1, V6 IBM Certified System Administrator – WebSphere Application Server V7, V6.1 IBM Certified System Administrator – AIX V7 IBM Certified System Administrator – WebSphere MQ V7 IBM Certified Deployment Professional – Business Process Manager Advanced V7.5 IBM Certified Solution Advisor – Cloud Computing Architecture V3 IBM Certified Solution Developer – WebSphere Portal V5.1
This entry was posted in DB2. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s