informix表级恢复的问题，谁能帮我下，急
命令文件：database npmdb;
create table rnc
(
int_id integer,
object_class integer
default 9200,
rncid integer,
mcc integer,
mnc integer,
maxcallcapability integer,
maxthroughput integer,
software_version varchar(255),
dev_mode varchar(255),
cover_area_info varchar(255),
maxmbmscontext integer,
spc varchar(60),
primary key (int_id,object_class)
) in rootdbs;
create table rnc_20110220
(
int_id integer,
object_class integer
default 9200,
rncid integer,
mcc integer,
mnc integer,
maxcallcapability integer,
maxthroughput integer,
software_version varchar(255),
dev_mode varchar(255),
cover_area_info varchar(255),
maxmbmscontext integer,
spc varchar(60),
primary key (int_id,object_class)
);
insert into rnc_20110220 select * from rnc;
restore to "2011-02-17 23:00:00";
恢复结果：
正常从备份软件读完数据，然后就莫名其妙的退出了，目标表建立成功，但是没有记录。
日志输出：
2011-02-20 00:30:11
-----------------------------------------
STATUS: IBM Informix Dynamic Server Version 11.50.FC5
Program Name: archecker
Version: 8.0
Released: 2009-07-15 14:43:23
CSDK: IBM Informix CSDK Version 3.50
ESQL: IBM Informix-ESQL Version 3.50.FC4
Compiled: 07/15/09 14:43on AIX 3 5
STATUS: Arguments [-bvs -f restore3.cmdf]
STATUS: AC_STORAGE /tmp
STATUS: AC_MSGPATH /tmp/ac_msg.log
STATUS: AC_VERBOSE on
STATUS: AC_TAPEBLOCK 60 KB
STATUS: AC_IXBAR /opt/informix/etc/ixbar.0
STATUS: AC_SCHEMA restore3.cmdf
TIME: All old validation files removed.
STATUS: Dropping old log control tables
STATUS: Target table already created
STATUS: SQL
STATUS: Extracting table npmdb:rnc into npmdb:rnc_20110220
STATUS: Determined Storage Manager to be nwbsa.
STATUS: starting to scan dbspace 1 created on 2011-02-15 04:11:16.
STATUS: Archive timestamp 0X85F10E05.
STATUS: Scan PASSED
STATUS: Control page checks PASSED
STATUS: Table checks PASSED
STATUS: Table extraction commands 1
STATUS: Tables found on archive 0
STATUS: Tables not located on archive 1
STATUS: LOADED: npmdb:rnc_20110220 produced 0 rows
TIME: Physical Extraction Completed
TIME: Unload Completed
STATUS: archecker completed Physical Restore pid = 164842 exit code: 0
请各位高手帮忙啊，很急 确信这个表原来是在那个dbspace上？确信是在rootdbs上吗？这个必须写在的原来所在的dbspace的名字。。不然找不到记录的。。
STATUS: Tables found on archive 0
STATUS: Tables not located on archive 1
STATUS: LOADED: npmdb:rnc_20110220 produced 0 rows
从恢复日志上看是没有找到记录。。 回复 2# liaosnet
这是建库脚本
xndb1-/opt/informix> dbschema -d npmdb -t rnc -ss
DBSCHEMA Schema Utility INFORMIX-SQL Version 11.50.FC5
{ TABLE "npmuser".rnc row size = 861 number of columns = 12 index size = 13 }
create table "npmuser".rnc
(
int_id integer,
object_class integer
default 9200,
rncid integer,
mcc integer,
mnc integer,
maxcallcapability integer,
maxthroughput integer,
software_version varchar(255),
dev_mode varchar(255),
cover_area_info varchar(255),
maxmbmscontext integer,
spc varchar(60),
primary key (int_id,object_class)
) extent size 16 next size 16 lock mode page;
revoke all on "npmuser".rnc from "public" as "npmuser";
看着好像database和dbspace都没错啊 npmdb 你这个库建在哪个dbspace上，表就应该在那个dbspace上。。 回复 4# liaosnet
请教一下，单表恢复支持分片的表吗？
我上面那个恢复这是因为需要恢复的表失败了而找的一个简单表进行测试，真正要恢复的表是分片的，而且建表脚本指定了dbspace，应该是正确的，但是还是报同样的错
database npmdb;
create table tcc_ne_snap_bak_xy
(
ne_id integer not null ,
ne_type integer not null ,
first_result datetime year to second not null ,
compress_date date not null ,
s_month smallint,
related_ne1 integer not null ,
related_type1 integer not null ,
related_ne2 integer,
related_type2 integer,
related_site integer,
related_bsc integer,
related_bss integer,
related_msc integer,
related_omc integer,
city_id integer,
admin_level integer,
region_id integer,
area_id integer,
parent_ne_id integer not null ,
parent_ne_type integer not null ,
pm_rdn varchar(255) not null ,
userlabel varchar(255),
zh_label varchar(128),
vendor_id smallint,
gsmdcsindicator integer,
locationname varchar(127),
version varchar(127),
adminstate smallint,
optstate smallint,
usagestate smallint,
unknownstatus smallint,
native_int_id integer,
upd_flag smallint,
ne_code varchar(127),
int_id integer,
object_class integer,
confirmed smallint,
province_id integer,
tmsc_cat integer,
carrier_id integer,
stp_cat integer,
area_code integer,
related_lac integer,
ci integer,
lpt_type varchar(20),
related_ne3 integer,
related_type3 integer,
foreign_obj_ind integer
default 0 not null ,
resource_status smallint
default 1 not null ,
is_locked smallint
default 0 not null ,
lock_time datetime year to second,
machroom_id integer,
new_rdn varchar(128),
import_label varchar(127),
d_province_id integer,
d_area_id integer,
d_admin_level integer,
d_region_id integer,
d_city_id integer,
d_related_omc integer,
d_related_msc integer,
d_related_bss integer,
d_related_bsc integer,
d_related_site integer,
d_tmsc_cat integer,
d_stp_cat integer,
d_carrier_id integer,
d_related_lac integer,
d_vendor_id integer,
related_stp integer,
related_linkset integer,
related_smp integer,
related_scp integer,
related_sdp integer,
related_sau integer,
related_ip integer,
d_related_stp integer,
d_related_linkset integer,
d_related_smp integer,
d_related_scp integer,
d_related_sdp integer,
d_related_sau integer,
d_related_ip integer,
cat1_sv_id integer,
cat2_sv_id integer,
cat3_sv_id integer,
cat4_sv_id integer,
cat5_sv_id integer,
cat6_sv_id integer,
cat7_sv_id integer,
cat8_sv_id integer,
cat9_sv_id integer,
cat10_sv_id integer,
cat11_sv_id integer,
cat12_sv_id integer,
cat13_sv_id integer,
cat14_sv_id integer,
cat15_sv_id integer,
cat16_sv_id integer,
cat17_sv_id integer,
cat18_sv_id integer,
cat19_sv_id integer,
d_related_smsrv integer,
divide_in_time datetime year to second,
divide_out_time datetime year to second,
related_smsrv integer,
time_putinto_use datetime year to second,
d_related_smsc integer,
related_smsc integer,
country_id integer,
d_country_id integer,
cat20_sv_id integer,
cat21_sv_id integer,
cat22_sv_id integer,
cat23_sv_id integer,
cat24_sv_id integer,
cat25_sv_id integer,
cat26_sv_id integer,
cat27_sv_id integer,
cat28_sv_id integer,
cat29_sv_id integer,
related_tmg integer,
d_related_tmg integer,
related_tserver integer,
d_related_tserver integer,
mgw_cat integer,
mss_cat integer,
related_managedelement varchar(255),
related_managedelement_id integer,
managementipaddress varchar(255),
patchinfo varchar(255),
related_rns integer,
related_rnc integer,
related_nodeb integer,
related_mscserver integer,
d_related_rns integer,
d_related_rnc integer,
d_related_nodeb integer,
d_related_mscserver integer,
network_type integer,
primary key (compress_date,ne_type,ne_id)
)fragment by round robin in apmdbs1 , apmdbs2 , apmdbs3 , apmdbs4 , apmdbs5
, apmdbs6 , apmdbs7 , apmdbs8 , apmdbs9 , apmdbs10 , apmdbs11
, apmdbs12;
create table tcc_ne_snap_bak_20110218
(
ne_id integer not null ,
ne_type integer not null ,
first_result datetime year to second not null ,
compress_date date not null ,
s_month smallint,
related_ne1 integer not null ,
related_type1 integer not null ,
related_ne2 integer,
related_type2 integer,
related_site integer,
related_bsc integer,
related_bss integer,
related_msc integer,
related_omc integer,
city_id integer,
admin_level integer,
region_id integer,
area_id integer,
parent_ne_id integer not null ,
parent_ne_type integer not null ,
pm_rdn varchar(255) not null ,
userlabel varchar(255),
zh_label varchar(128),
vendor_id smallint,
gsmdcsindicator integer,
locationname varchar(127),
version varchar(127),
adminstate smallint,
optstate smallint,
usagestate smallint,
unknownstatus smallint,
native_int_id integer,
upd_flag smallint,
ne_code varchar(127),
int_id integer,
object_class integer,
confirmed smallint,
province_id integer,
tmsc_cat integer,
carrier_id integer,
stp_cat integer,
area_code integer,
related_lac integer,
ci integer,
lpt_type varchar(20),
related_ne3 integer,
related_type3 integer,
foreign_obj_ind integer
default 0 not null ,
resource_status smallint
default 1 not null ,
is_locked smallint
default 0 not null ,
lock_time datetime year to second,
machroom_id integer,
new_rdn varchar(128),
import_label varchar(127),
d_province_id integer,
d_area_id integer,
d_admin_level integer,
d_region_id integer,
d_city_id integer,
d_related_omc integer,
d_related_msc integer,
d_related_bss integer,
d_related_bsc integer,
d_related_site integer,
d_tmsc_cat integer,
d_stp_cat integer,
d_carrier_id integer,
d_related_lac integer,
d_vendor_id integer,
related_stp integer,
related_linkset integer,
related_smp integer,
related_scp integer,
related_sdp integer,
related_sau integer,
related_ip integer,
d_related_stp integer,
d_related_linkset integer,
d_related_smp integer,
d_related_scp integer,
d_related_sdp integer,
d_related_sau integer,
d_related_ip integer,
cat1_sv_id integer,
cat2_sv_id integer,
cat3_sv_id integer,
cat4_sv_id integer,
cat5_sv_id integer,
cat6_sv_id integer,
cat7_sv_id integer,
cat8_sv_id integer,
cat9_sv_id integer,
cat10_sv_id integer,
cat11_sv_id integer,
cat12_sv_id integer,
cat13_sv_id integer,
cat14_sv_id integer,
cat15_sv_id integer,
cat16_sv_id integer,
cat17_sv_id integer,
cat18_sv_id integer,
cat19_sv_id integer,
d_related_smsrv integer,
divide_in_time datetime year to second,
divide_out_time datetime year to second,
related_smsrv integer,
time_putinto_use datetime year to second,
d_related_smsc integer,
related_smsc integer,
country_id integer,
d_country_id integer,
cat20_sv_id integer,
cat21_sv_id integer,
cat22_sv_id integer,
cat23_sv_id integer,
cat24_sv_id integer,
cat25_sv_id integer,
cat26_sv_id integer,
cat27_sv_id integer,
cat28_sv_id integer,
cat29_sv_id integer,
related_tmg integer,
d_related_tmg integer,
related_tserver integer,
d_related_tserver integer,
mgw_cat integer,
mss_cat integer,
related_managedelement varchar(255),
related_managedelement_id integer,
managementipaddress varchar(255),
patchinfo varchar(255),
related_rns integer,
related_rnc integer,
related_nodeb integer,
related_mscserver integer,
d_related_rns integer,
d_related_rnc integer,
d_related_nodeb integer,
d_related_mscserver integer,
network_type integer,
primary key (compress_date,ne_type,ne_id)
);
insert into tcc_ne_snap_bak_20110218 select * from tcc_ne_snap_bak_xy;
restore to "2011-02-04 12:00:00"; 回复 5# ff_jxj
从你的第一楼的日志信息上看，整个操作是正常的完成的，只是至你当前的逻辑前并没有该表的记录。。
分片表是否支持表级恢复，这个需要在测试下才能确定。。。理论上也是应该支持才对。。。 本帖最后由 liaosnet 于 2011-02-20 17:47 编辑
回复 5# ff_jxj
测试了下，分段表也是可以的～
以下是测试过程（使用的备份方式是ontape。若是使用onbar备份，在命令上有区别）：
1，新建三个dbs,分别是userdbs1,userdbs2,userdbs3。然后在testdb中创建表tt，并导入100行数据。
create table tt
(
id char(10),
name char(20),
primary key(id)
) fragment by round robin in userdbs1,userdbs2,userdbs3;
2，创建0级备份
ontape -s -L 0
3，删除表 --注：也可以不删除源表，在模式文件中应指定目标表为另外的名字！
drop table tt;
4，创建表恢复模式文件tt.sc
database testdb;
create table tt
(
id char(10),
name char(20),
primary key(id)
) fragment by round robin in userdbs1,userdbs2,userdbs3;
create table tt_new
(
id char(10),
name char(20),
primary key(id)
) fragment by round robin in userdbs1,userdbs2,userdbs3;
insert into tt_new select * from tt;
restore to current;
5，执行恢复表tt，以下是恢复的日志。
informix@suse10:/opt/informix/tmp/test> archecker -tvs -f tt.sc
IBM Informix Dynamic Server Version 11.50.UC6
Program Name: archecker
Version: 8.0
Released: 2009-12-15 21:59:36
CSDK: IBM Informix CSDK Version 3.50
ESQL: IBM Informix-ESQL Version 3.50.UC4
Compiled: 12/15/09 21:59on Linux 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:54:53 EST 2006
AC_STORAGE /tmp
AC_MSGPATH /tmp/ac_msg.log
AC_VERBOSE on
AC_TAPEDEV /opt/informix/backup/fullbak/
AC_TAPEBLOCK 1024 KB
AC_LTAPEDEV /opt/informix/backup/logbak/
AC_LTAPEBLOCK 32 KB
Dropping old log control tables
Extracting table testdb:tt into testdb:tt_new
Archive file /opt/informix/backup/fullbak/suse10_0_L0
Tape type: Archive Backup Tape
OnLine version: IBM Informix Dynamic Server Version 11.50.UC6
Archive date: Sun Feb 20 17:25:45 2011
Archive level:0
Tape blocksize:1048576
Tape size:2147483647
Tape number in series:1
Scan PASSED
Control page checks PASSED
Table checks PASSED
Table extraction commands 1
Tables found on archive 1
LOADED: testdb:tt_new produced 100 rows.
Creating log control tables
Log file /opt/informix/backup/logbak/suse10_0_Log0000000060
Log tape backup date: Sun Feb 20 17:28:40 2011
Tape blocksize: 32768
Tape size: 2147483647
Tape number in series: 2
Log file /opt/informix/backup/logbak/suse10_0_Log0000000061
Log tape backup date: Sun Feb 20 17:28:44 2011
Tape blocksize: 32768
Tape size: 2147483647
Tape number in series: 3
Switching to log 61
Logically recovered testdb:tt_new Inserted 0 Deleted 0 Updated 0
以下是ac_msg.log中的恢复信息
2011-02-20 17:31:26
-----------------------------------------
STATUS: IBM Informix Dynamic Server Version 11.50.UC6
Program Name: archecker
Version: 8.0
Released: 2009-12-15 21:59:36
CSDK: IBM Informix CSDK Version 3.50
ESQL: IBM Informix-ESQL Version 3.50.UC4
Compiled: 12/15/09 21:59on Linux 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:54:53 EST 2006
STATUS: Arguments [-tvs -f tt.sc]
STATUS: AC_STORAGE /tmp
STATUS: AC_MSGPATH /tmp/ac_msg.log
STATUS: AC_VERBOSE on
STATUS: AC_TAPEDEV /opt/informix/backup/fullbak/
STATUS: AC_TAPEBLOCK 1024 KB
STATUS: AC_LTAPEDEV /opt/informix/backup/logbak/
STATUS: AC_LTAPEBLOCK 32 KB
STATUS: AC_SCHEMA tt.sc
TIME: All old validation files removed.
STATUS: Dropping old log control tables
STATUS: SQL
STATUS: Extracting table testdb:tt into testdb:tt_new
STATUS: Archive file /opt/informix/backup/fullbak/suse10_0_L0
STATUS: Tape type: Archive Backup Tape
STATUS: OnLine version: IBM Informix Dynamic Server Version 11.50.UC6
STATUS: Archive date: Sun Feb 20 17:25:45 2011
STATUS: Archive level:0
STATUS: Tape blocksize:1048576
STATUS: Tape size:2147483647
STATUS: Tape number in series:1
TIME: Phys Tape 1 started
STATUS: starting to scan dbspace 1 created on 2011-02-20 17:25:45.
STATUS: Archive timestamp 0X001D4847.
STATUS: starting to scan dbspace 7 created on 2011-02-20 17:25:45.
STATUS: Archive timestamp 0X001D4847.
STATUS: starting to scan dbspace 8 created on 2011-02-20 17:25:45.
STATUS: Archive timestamp 0X001D4847.
STATUS: starting to scan dbspace 9 created on 2011-02-20 17:25:45.
STATUS: Archive timestamp 0X001D4847.
STATUS: starting to scan dbspace 3 created on 2011-02-20 17:25:45.
STATUS: Archive timestamp 0X001D4847.
STATUS: starting to scan dbspace 4 created on 2011-02-20 17:25:45.
STATUS: Archive timestamp 0X001D4847.
STATUS: starting to scan dbspace 5 created on 2011-02-20 17:25:45.
STATUS: Archive timestamp 0X001D4847.
STATUS: starting to scan dbspace 6 created on 2011-02-20 17:25:45.
STATUS: Archive timestamp 0X001D4847.
STATUS: starting to scan dbspace 10 created on 2011-02-20 17:25:45.
STATUS: Archive timestamp 0X001D4847.
TIME: Found Partition tt in space userdbs1 (0x00A00002).
STATUS: starting to scan dbspace 11 created on 2011-02-20 17:25:45.
STATUS: Archive timestamp 0X001D4847.
TIME: Found Partition tt in space userdbs2 (0x00B00002).
STATUS: starting to scan dbspace 12 created on 2011-02-20 17:25:45.
STATUS: Archive timestamp 0X001D4847.
TIME: Found Partition tt in space userdbs3 (0x00C00002).
TIME: Tape 1 completed
STATUS: Scan PASSED
STATUS: Control page checks PASSED
STATUS: Table checks PASSED
STATUS: Table extraction commands 1
STATUS: Tables found on archive 1
STATUS: LOADED: testdb:tt_new produced 100 rows.
TIME: Physical Extraction Completed
STATUS: Creating log control tables
TIME: Log Stager started (pid = 12174)
TIME: Log Applier started (pid = 12173)
STATUS: Setting up log stream 1
STATUS: Starting recovery at log 60.
STATUS: Common rollforward point 0060:0x0038E:0x0018.
STATUS: Log file /opt/informix/backup/logbak/suse10_0_Log0000000060
STATUS: Log tape backup date: Sun Feb 20 17:28:40 2011
STATUS: Tape blocksize: 32768
STATUS: Tape size: 2147483647
STATUS: Tape number in series: 2
STATUS: Log tape log block header logid=60
TIME: Log Tape 60_2 started
WARNING: Not a complete buffer
TIME: Tape 2 completed
STATUS: Log file /opt/informix/backup/logbak/suse10_0_Log0000000061
STATUS: Log tape backup date: Sun Feb 20 17:28:44 2011
STATUS: Tape blocksize: 32768
STATUS: Tape size: 2147483647
STATUS: Tape number in series: 3
STATUS: Log tape log block header logid=61
TIME: Log Tape 61_3 started
TIME: Switching to log 61
WARNING: Not a complete buffer
TIME: Tape 3 completed
TIME: Scanner (12174) is done scanning logs
STATUS: Log stager elapsed processing time 00 H 00 M 00.091 S
TIME: Unload Completed
STATUS: archecker completed staging pid = 12174 exit code: 2
STATUS: SQL
STATUS: Logically recovered testdb:tt_new Inserted 0 Deleted 0 Updated 0
STATUS: Log applier processing time 00 H 00 M 01.064 S
TIME: Unload Completed
STATUS: archecker completed applying pid = 12173 exit code: 0
给诸位添麻烦了
昨天没办法，只能全库恢复，做了44个小时总算做完了，在恢复后的库里看，用户要求恢复的表根本不存在，查了下表，发现用户给的表名都是错的，更别提那该死的对应DBSPACAE了
这个表级恢复，非DBA来做真是麻烦啊，遇到个混乱的DBA，害死人了.....
最后，谢谢下liaosnet，你真是热心人，衷心感谢你
页:
[1]