Prayer

在一般中寻求卓越
posts - 1256, comments - 190, trackbacks - 0, articles - 0
  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

多分区数据库脱机备份时遇到 SQL1035N 错误

Posted on 2010-03-22 13:53 Prayer 阅读(1973) 评论(0)  编辑 收藏 引用 所属分类: DB2
于多分区数据库,脱机备份数据库时需要注意先备份 CATALOG 节点,再备份其它节点,否则下面命令也会遇到 SQL1035N 错误(假设数据库名为 SAMPLE)。

db2_all "; db2 backup database sample to ..."

......

SQL1035N The database is currently in use. SQLSTATE=57019

......


在单分区数据库下,如果脱机备份时遇到 SQL1035N 错误,说明数据库因为处于活动( ACTIVATE )状态而无法备份。可以使用 “db2 list active databases” 查询一下实例下活动的数据库,也可以用 “db2pd -db 数据库名” 检查数据库是否处于活动状态。

但多于多分区数据库,脱机备份数据库时需要注意先备份 CATALOG 节点,再备份其它节点,否则下面命令也会遇到 SQL1035N 错误(假设数据库名为 SAMPLE)。

db2_all "; db2 backup database sample to ..."

......

SQL1035N The database is currently in use. SQLSTATE=57019

......

假设 0 号节点就是 CATALOG 节点,下面的命令可以成功地备份数据库:

db2_all "<<+0<; db2 backup database sample to ..."
db2_all "<<-0<; db2 backup database sample to ...."

<<+0< 代表只包括 0 号节点,<<-0< 代表不包括 0 号节点。

下面引用两个技术文档的原文:

1) http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=problem+determination&q2=backup+%3c%3c%2b0%3c&q3=SQL1035N&uid=swg21224032&loc=en_US&cs=utf-8&lang=en

SQL1035N during execution of an offline backup

Problem
This document provides troubleshooting methods for when you attempt to take an offline backup of a database and encounter the following error: SQL1035N The database is currently in use. SQLSTATE=57019

Cause
One cause of this error is that you are attempting to take an offline backup of an activated database. This fails and returns SQL1035N.

For example, if you're using DB2? Universal Database? (DB2 UDB) Version 8.2, taking a backup of the database fails as follows:

db2 backup db sample to /home/db2inst1
SQL1035N The database is currently in use. SQLSTATE=57019

The following message is also logged in the db2diag.log file:

2005-11-18-11.29.17.849377-360 I9627A336 LEVEL: Error
PID : 34980 TID : 1 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
FUNCTION: DB2 UDB, database utilities, sqlubConnectDatabase, probe:1259
DATA #1 : Hexdump, 4 bytes
0x00000001102842E0 : FFFF FBF5
....
In the above db2diag.log message, the value "FFFF FBF5" translates to -1035 (SQL1035).

Solution
You will not be able to determine whether the database is activated via the db2 list applications command, since it only tells you whether or not any applications are active on the database. To determine whether the database has been activated, you can use the following db2pd command:
db2pd -db sample -app
...
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:19:42

Applications:
Address AppHandl [nod-index] NumAgents CoorPid Status Appid
...

The fact that the database has been activated is indicated by the phrase "-- Active --".

Alternatively, you can issue the command db2 list active databases.

Once the database has been deactivated (for example, via the deactivate database command), the offline backup will succeed.

2) From "DB2 Problem Determination Tutorial Series"

Version recovery - SQL1035 (database in use) when taking offline backups of all partitions in parallel The version recovery method requires loading a backup copy of the database. The database will be restored to exactly the same state that it was in when it was backed up. Using the version recovery method, you must schedule and perform full backups of the database on a regular basis.

You may have the following experience:
All database partitions are inactive at the moment. However, offline backup of all partitions in parallel fails with SQL1035N (The database is currently in use.) on most of the partitions.
You try to take offline backup of all partitions in parallel as follows:
% db2 force applications all
% db2_all ";db2 backup database sample to /database/backup"
af01n002: SQL1035N The database is currently in use. SQLSTATE=57019
af01n002: db2 backup database ... completed rc=4
af01n003: SQL1035N The database is currently in use. SQLSTATE=57019
af01n003: db2 backup database ... completed rc=4
af01n003: SQL1035N The database is currently in use. SQLSTATE=57019
af01n003: db2 backup database ... completed rc=4
af01n002:
af01n002: Backup successful. The timestamp for this backup image is :
20021029190857
af01n002:
af01n002: db2 backup database ... completed ok
You can confirm the error in the db2diag.log file:
2002-10-29-19.08.58.628733 Instance:db2inst1 Node:001
PID:41494(db2agent (SAMPLE) 1) Appid:*N1.db2inst1.021030000858
relation_data_serv sqlrrain Probe:70 Database:SAMPLE
DIA9999E An internal error occurred. Report the following error code :
"0xFFFF876D".
Data Title:SQLCA PID:41494 Node:001
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1035 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLESRSU
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
......

This occurs because the database backup utility requires exclusive access to the catalog partition.
To properly backup the database in parallel, you may use the following commands:
% db2_all "<<+0<; db2 backup database sample to /database/backup"
% db2_all "<<-0<; db2 backup database sample to /database/backup"
This assumes that CATALOG PARTITION is partition 0. It is also a good illustration of why the CATALOG PARTITION should contain catalog data ONLY.


只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理