远程Sybase数据库技术支持,联系手机:13811580958,QQ:289965371!

 

随着Sybase被完全整合到SAP下,Sybase原来的支持网站被SAP Support Portal取代。
只有购买了SAP服务的用户才能使用账号登录SAP Support Portal进行介质下载、补丁升级、报Incident等。
目前,原Sybase所有产品(包括:Adaptive Server Enterprise、Sybase IQ、Replication Server、PowerDesigner等)的官方手册仍然可以从http://infocenter.sybase.com/help/index.jsp进行浏览或下载。暂不清楚该网站http://infocenter.sybase.com/help/index.jsp何时会被完全迁移到SAP Support上!
Sybase官方手册英文版有html和pdf两种格式,而中文版手册只有pdf一种格式。为了国内Sybase用户更方便、快捷地搜索Sybase常见产品的官方手册内容,特将中文版Sybase官方手册转为html格式!
Sybase产品官方手册中文版的html格式所有内容的版权归SAP公司所有!本博客站长是Sybase数据库的铁杆粉丝!

如有Sybase数据库技术问题需要咨询,请联系我!

  QQ :289965371 点击这里给我发消息
  Email:

以下官方手册为ASE 15.7 ESD#2中文版:

  1. 新增功能公告 适用于 Windows、Linux 和 UNIX 的 Open Server 15.7 和 SDK 15.7
  2. 新增功能摘要
  3. 新增功能指南
  4. ASE 15.7 发行公告
  5. 配置指南(windows)
  6. 安装指南(windows)
  7. 参考手册:构件块
  8. 参考手册:命令
  9. 参考手册:过程
  10. 参考手册:表
  11. Transact-SQL® 用户指南
  12. 系统管理指南,卷 1
  13. 系统管理指南,卷 2
  14. 性能和调优系列:基础知识
  15. 性能和调优系列:锁定和并发控制
  16. 性能和调优系列:监控表
  17. 性能和调优系列:物理数据库调优
  18. 性能和调优系列:查询处理和抽象计划
  19. 性能和调优系列:使用 sp_sysmon 监控 Adaptive Server
  20. 性能和调优系列:利用统计分析改进性能
  21. 程序员参考 jConnect for JDBC 7.0.7
  22. Adaptive Server Enterprise 中的 Java
  23. 组件集成服务用户指南
  24. Ribo 用户指南
  25. 内存数据库用户指南
  26. Sybase Control Center for Adaptive Server® Enterprise
  27. 安全性管理指南
  28. 实用程序指南

 


< 上一个 | 内容 | 下一步 >

缩减日志空间


alter database 命令包括 log off 参数,它用于删除数据库日志的不必要 部分,能让您缩减日志空间并释放存储空间而无需重新创建数据库。

语法为:

alter database database_name [log off database_device

[= size | [from logical_page_number] [to logical_page_number]] [, database_device

[= size | [from logical_page_number] [to logical_page_number]]

该参数可能在运行数据库操作的完全记录选项(如 select intoalter table reorg rebuild)后,数据库产生不再需要的额外分配空间时特 别有用。请参见《参考手册:命令》中的 dump transaction 命令。


缩减日志空间时使用 dump load database

执行 dump load database 时:

要装载的数据库拥有的物理空间至少要与转储时拥有的物理空间 相等。

要装载的数据库中的物理空间根据转储数据库中的物理片段指 定。这意味着要装载的数据库中有“空洞”(先前执行 alter database log off 命令后没有物理存储与之关联的分配单位),装 载后无需保留空洞。

要装载的数据库中余留空间的指定方式与没有空洞的情况下执 行 dump load database 时相同。

您可以通过运行 load database with headeronly 命令来确定转储数 据库中的物理空间量、它是否有空洞以及有关这些空洞的大小 和位置的信息。

转储和装载数据库之前缩减日志

以下方案缩减即将转储的数据库的日志。使用转储装载之前,可使 用 load database with headeronly 命令和 sp_helpdb 系统过程确定目标 数据库的大小。完整的命令序列位于 134 页的“使用 dump load database 的序列示例”中。

1 根据需要使用任意数目的日志设备创建数据库。该示例创建两 个日志设备。

2 (可选)运行 select *,确认数据库创建情况并显示组成数据库 的设备片段。


3 使用 alter database log off 命令,在不中断数据库转储序列的情况 下从数据库中删除不需要的日志部分。如果数据库的转储序列 已中断,则 alter database log off 会自动从数据库结尾删除所有缩 减的空间。如果删除的空间不在数据库结尾处,则会形成空洞。

在该示例中,位于数据库中间位置的缩减空间已形成空洞。

4 sysusages 的输出显示了空洞的位置和大小(在该示例中, segmap = 0location = 4)。sp_helpdb 的输出摘要显示除去空洞 之外的数据库大小(在该示例中为 9MB)和数据库中空洞的总 大小(在该示例中,仅日志不可用空间为 3072KB,即 3MB):

5 要装载的数据库的总大小为 12MB,但其中,数据库中实际的 物理空间为 9MB,因为存在 3MB 的空洞。dump database with headeronly 命令验证数据库是否包含 12MB 的逻辑页和 9MB 的物 理页。要装载此数据库,必须创建大小至少为 9MB 的新数据库。

6 装载数据库并使它联机。

在新装载数据库的 sysusages 行中,已重新安排 9MB 的物理空 间,以与转储数据库的物理空间相匹配,这样数据库现在的总 大小为 12MB,其中只有 9MB 的物理页,另外还有 3MB 的空。


使用 dump load database 的序列示例

下面的示例显示按 133 页的“转储和装载数据库之前缩减日志” 中所述,使用 dump load database 时要执行的完整序列:

1> create database sales_db on sales_db_dev=3 log on sales_db_log1=3, sales_db_log2=3, sales_db_log1=3, sales_db_log2=3

2> go

00:00:00000:00015:2011/01/21 09:38:28.29 server Timestamp for database 'sales_db' is (0x0000,

CREATE DATABASE:allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_dev' (1536 logical pages requested).

CREATE DATABASE:allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log1' (1536 logical pages requested).

CREATE DATABASE:allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log2' (1536 logical pages requested).

CREATE DATABASE:allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log1' (1536 logical pages requested).

CREATE DATABASE:allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log2' (1536 logical pages requested).

Warning:The database 'sales_db' is using an unsafe virtual device 'sales_db_dev'. The recovery of this database can not be guaranteed.

Database 'sales_db' is now online.

1> select * from sysusages where dbid=4 2> go


dbid segmap lstart size vstart location unreservedpgs crdate vdevno

4

4

1536 1536

0

0

1530

Jan 21 2011 9:38AM

2

4

4

3072 1536

0

0

1530

Jan 21 2011 9:38AM

3

4

4

4608 1536

1536

0

1530

Jan 21 2011 9:38AM

2

4

4

6144 1536

1536

0

1530

Jan 21 2011 9:38AM

3

4

4

1536 1536

0

0

1530

Jan 21 2011 9:38AM

2

4

4

3072 1536

0

0

1530

Jan 21 2011 9:38AM

3

4

4

4608 1536

1536

0

1530

Jan 21 2011 9:38AM

2

4

4

6144 1536

1536

0

1530

Jan 21 2011 9:38AM

3

---- ------ ------ ---- ------ -------- ------------- ------ ------ 4 3 0 1536 0 0 670 Jan 21 2011 9:38AM 1


(5 rows affected)

1> alter database sales_db log off sales_db_log2 2> select * from sysusages where dbid=4

3> go

dbid segmap lstart size vstart location unreservedpgs crdate vdevno

---- ------ ------ ---- ------ -------- ------------- ------ ------ 4 3 0 1536 0 0 670 Jan 21 2011 9:38AM 1

4 4 1536 1536 0 0 1530 Jan 21 2011 9:38AM 2

4 0 3072 1536 3072 4 1530 Jan 21 2011 9:38AM -4

4 4 4608 1536 1536 0 1530 Jan 21 2011 9:38AM 2


(4 rows affected)

1> sp_helpdb sales_db

2> go

name db_size owner dbid created durability status

-------- ------------- ----- ---- ------------ ---------- --------------

sales_db 9.0 MB sa 4 Jan 21, 2011 full no options set


(1 row affected)

device_fragments size usage created free kbytes

------------------ ------ ---------- ------------------ --------------

sales_db_dev 3.0 MB data only Jan 21 2011 9:38AM 1340

sales_db_log1 3.0 MB log only Jan 21 2011 9:38AM not applicable sales_db_log1 3.0 MB log only Jan 21 2011 9:38AM not applicable


--------------------------------------------------------------

log only free kbytes = 6082, log only unavailable kbytes = 3072 (return status = 0)

1> dump database sales_db to "c:/temp/sales_db.dmp" 2> go

Backup Server session id is:45. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from

the Backup Server.

Backup Server4.41.1.1: Creating new disk file c:/temp/sales_db.dmp.

Backup Server6.28.1.1: Dumpfile name 'sales_db11021087C7 ' section number 1

mounted on disk file 'c:/temp/sales_db.dmp'

Backup Server4.188.1.1: Database sales_db:848 kilobytes (67%) DUMPED. Backup Server4.188.1.1: Database sales_db:862 kilobytes (100%) DUMPED. Backup Server3.43.1.1: Dump phase number 1 completed.

Backup Server:3.43.1.1: Dump phase number 2 completed.


Backup Server:3.43.1.1: Dump phase number 3 completed.

Backup Server4.188.1.1: Database sales_db:870 kilobytes (100%) DUMPED. Backup Server3.42.1.1: DUMP is complete (database sales_db).

1> load database sales_db from "c:/temp/sales_db.dmp" with headeronly 2> go

Backup Server session id is:48. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.

Backup Server6.28.1.1: Dumpfile name 'sales_db11021087C7 ' section number 1 mounted

on disk file 'c:/temp/sales_db.dmp'

This is a database dump of database ID 4, name 'sales_db', from Jan 21 2011 9:39AM.ASE version:lite_642236-1/Adaptive Server Enterprise/15.7/EBF 18567 SMP Drop#2/B/X64/Windows Server/aseasap/ENG/.Backup Server version: Backup Server/15.7/B/X64/Windows Server/aseasap/ENG/64-bit/DEBUG/Thu Jan 20 11:12:51 2011.

Database page size is 2048.

Database contains 6144 pages; checkpoint RID=(Rid pageid = 0x604; row num = 0x12); next object ID=560001995; sort order ID=50, status=0; charset ID=2.

Database log version=7; database upgrade version=35; database durability=UNDEFINED. segmap:0x00000003 lstart=0 vstart=[vpgdevno=1 vpvpn=0] lsize=1536 unrsvd=670 segmap:0x00000004 lstart=1536 vstart=[vpgdevno=2 vpvpn=0] lsize=1536 unrsvd=1530 Unavailable disk fragment: .lstart=3072 lsize=1536

segmap:0x00000004 lstart=4608 vstart=[vpgdevno=2 vpvpn=1536] lsize=1536 unrsvd=1530 The database contains 6144 logical pages (12 MB) and 4608 physical pages (9 MB).

1> create database sales_db2 on sales_db_dev=3 log on sales_db_log1=6 2> go

CREATE DATABASE:allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_dev' (1536 logical pages requested).

CREATE DATABASE:allocating 3072 logical pages (6.0 megabytes) on disk 'sales_db_log1' (3072 logical pages requested).

Warning:The database 'sales_db2' is using an unsafe virtual device 'sales_db_dev'. The recovery of this database can not be guaranteed.

Database 'sales_db2' is now online.

1> select * from sysusages where dbid=db_id("sales_db2") 2> go

dbid segmap lstart size vstart location unreservedpgs crdate vdevno

---- ------ ------ ---- ------ -------- ------------- ------ ------ 5 3 0 1536 1536 0 670 Jan 26 2011 1:22AM 1

5 4 1536 3072 3072 0 3060 Jan 26 2011 1:22AM 2


1> load database sales_db2 from "/tmp/sales_db.dmp" 2> go

Backup Server session id is:10. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.

Backup Server6.28.1.1: Dumpfile name 'sales_db1102602564 ' section number 1 mounted

on disk file '/tmp/sales_db.dmp'

Backup Server4.188.1.1: Database sales_db2:6148 kilobytes (33%) LOADED. Backup Server4.188.1.1: Database sales_db2:9222 kilobytes (50%) LOADED. Backup Server4.188.1.1: Database sales_db2:9230 kilobytes (100%) LOADED. Backup Server3.42.1.1: LOAD is complete (database sales_db2).


Started estimating recovery log boundaries for database 'sales_db2'.

Database 'sales_db2', checkpoint=(1544, 22), first=(1544, 22), last=(1544, 22). Completed estimating recovery log boundaries for database 'sales_db2'.

Started ANALYSIS pass for database 'sales_db2'. Completed ANALYSIS pass for database 'sales_db2'.

00:00:00000:00011:2011/01/26 05:12:15.86 server Log contains all committed transactions until 2011/01/26 01:55:15.71 for database sales_db2.

Started REDO pass for database 'sales_db2'.The total number of log records to process is 1.

Completed REDO pass for database 'sales_db2'.

00:00:00000:00011:2011/01/26 05:12:15.88 server Timestamp for database 'sales_db2' is (0x0000, 0x00001612).

Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.

1> online database sales_db2 2> go

Started estimating recovery log boundaries for database 'sales_db2'.

Database 'sales_db2', checkpoint=(1544, 22), first=(1544, 22), last=(1544, 22). Completed estimating recovery log boundaries for database 'sales_db2'.

Started ANALYSIS pass for database 'sales_db2'. Completed ANALYSIS pass for database 'sales_db2'.

00:00:00000:00011:2011/01/26 05:12:22.49 server Log contains all committed transactions until 2011/01/26 01:55:15.71 for database sales_db2.

Recovery of database 'sales_db2' will undo incomplete nested top actions. Database 'sales_db2' is now online.

1> select * from sysusages where dbid=db_id("sales_db2") 2> go

dbid segmap lstart size vstart location unreservedpgs crdate vdevno

---- ------ ------ ---- ------ -------- ------------- ------ ------

5

3

0 1536

1536

0

670 Jan 26 2011 5:12AM

1

5

4

1536 1536

3072

0

1530 Jan 26 2011 5:12AM

2

5

0

3072 1536

3072

4

1530 Jan 26 2011 5:12AM

-5

5

4

4608 1536

4608

0

1530 Jan 26 2011 5:12AM

2


缩减日志空间时使用 dump load transaction

如果在某一转储序列中首先转储数据库,然后再定期执行事务日志 转储,则在该转储序列期间日志的大小可能会发生变化。例如,如 果增加日志段来纳由完全记录的 select into 执行的日志记录量,然后 在完成该命令后缩减日志使其恢复为以前的大小,则更是如此。装 载此类转储序列时请遵循以下准则:

在转储序列期间采用最大大小创建要从转储装载的数据库。可通 过对要装载的上一事务转储执行 dump tran with headeronly 来确定 该值。


仅在事务日志序列完成并且使数据库联机后,才将日志缩减到所 需的大小。

装载其日志已得到缩减的数据库的转储序列

显示这些命令和输出的示例按 139 页的“使用 dump load transaction 的序列示例”中所述的编号步骤执行。

1 创建数据库。该示例创建 sales_db

2 使用 sp_dboption 系统过程的 'full logging for all' 数据库选项打开数 据库的完全日志记录模式。

3 转储数据库。

4 使用 alter database log on 增加日志段的大小,准备执行完全记录 的 select into 命令。

5 运行完全记录的 select into 命令,该命令使用增加的日志段。

6 转储事务日志以截断日志,准备缩减日志段。

7 使用 alter database log off 缩减数据库日志,以删除前面的步骤中 增加的日志空间。

8 使用缩减的日志段转储数据库的事务日志。

9 装载转储序列之前,首先根据装载序列中的上一文件获取数据 库的逻辑大小。在该示例中,大小为 16MB


image

注释 根据装载序列中的上一转储确定的数据库逻辑大小保证至 少与整个转储序列中数据库的最大物理大小相等。这样可以方便 地确定,要装载序列中的所转储,目标数据库应采用的大小。

image


使用 load transaction with headeronly 命令确定,为了容纳序列中的 所有转储,目标数据库必须采用的大小。

10 根据需要使用任意数目的日志设备创建新数据库。该示例使用 两个日志设备创建了 sales_db1 数据库,它的大小为 16MB

11 装载此数据库。

12 从第一个和第二个事务日志转储将事务日志装载到数据库中。

13 使数据库联机。

14 通过从其日志段中删除空间来减小数据库的大小。在该示例中, 日志段的大小减小了 10MB

15 运行 select * from sysusages 以确认从数据库结尾删除了空间。删 除的空间在数据库中形成空洞。


16 使用 dump database with shrink_log 选项删除数据库结尾的 空洞。

17 再次运行 select * from sysusages,以确认 Adaptive Server 成功从 数据库结尾删除了空洞。


使用 dump load transaction 的序列示例

下面的示例显示按 138 页的“装载其日志已得到缩减的数据库的 转储序列”中所述,使用 dump load transaction 时要执行的完整 序列:

1> create database sales_db on sales_db_dev=3 log on sales_db_log1=3 2> go

00:00:00000:00018:2011/05/05 12:45:06.36 server Timestamp for database 'sales_db' is (0x0000, 0x00002aa9).

CREATE DATABASE:allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_dev' (1536 logical pages requested).CREATE DATABASE:allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log1' (1536 logical pages requested).

Warning:The database 'sales_db' is using an unsafe virtual device 'sales_db_dev'.The recovery of this database can not be guaranteed.

Database 'sales_db' is now online.

1> sp_dboption sales_db,'full logging for all',true 2> go

Database option 'full logging for all' turned ON for database 'sales_db'.

Running CHECKPOINT on database 'sales_db' for option 'full logging for all' to take effect.

(return status = 0)

1> use master

2> go

1> dump database sales_db to "/tmp/sales_db.dmp" 2> go

Backup Server session id is:120. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.

Backup Server4.41.1.1: Creating new disk file /tmp/sales_db.dmp.

Backup Server6.28.1.1: Dumpfile name 'ales_db11137014BC' section number 1 mounted on

disk file '/tmp/sales_db.dmp'

Backup Server4.188.1.1: Database sales_db:852 kilobytes (100%) DUMPED. Backup Server3.43.1.1: Dump phase number 1 completed.

Backup Server3.43.1.1: Dump phase number 2 completed.

Backup Server4.188.1.1: Database sales_db:856 kilobytes (100%) DUMPED. Backup Server3.43.1.1: Dump phase number 3 completed.

Backup Server4.188.1.1: Database sales_db:860 kilobytes (100%) DUMPED. Backup Server3.42.1.1: DUMP is complete (database sales_db).

1> alter database sales_db log on sales_db_log2=10 2> go

Extending database by 5120 pages (10.0 megabytes) on disk sales_db_log2


Warning:The database 'sales_db' is using an unsafe virtual device 'sales_db_dev'.The recovery of this database can not be guaranteed.

Warning:Using ALTER DATABASE to extend the log segment will cause user thresholds on the log segment within 128 pages of the last chance threshold to be disabled.

use sales_db go

select * into bigtab2 from bigtab go

(20000 rows affected)

1> dump tran sales_db to "/tmp/sales_db.trn1" 2> go

Backup Server session id is:9. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.

Backup Server4.41.1.1: Creating new disk file /tmp/sales_db.trn1.

Backup Server6.28.1.1: Dumpfile name 'ales_db1113903D37' section number 1 mounted on

disk file '/tmp/sales_db.trn1'

Backup Server4.58.1.1: Database sales_db:250 kilobytes DUMPED. Backup Server4.58.1.1: Database sales_db:254 kilobytes DUMPED. Backup Server3.43.1.1: Dump phase number 3 completed.

Backup Server4.58.1.1: Database sales_db:258 kilobytes DUMPED. Backup Server3.42.1.1: DUMP is complete (database sales_db).

1> use master

2> go

1> alter database sales_db log off sales_db_log2 2> go

Removing 5120 pages (10.0 MB) from disk 'sales_db_log2' in database 'sales_db'.

1> dump tran sales_db to "/tmp/sales_db.trn2" 2> go

Backup Server session id is:11. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.

Backup Server4.41.1.1: Creating new disk file /tmp/sales_db.trn2.

Backup Server6.28.1.1: Dumpfile name 'ales_db1113903D87' section number 1 mounted on

disk file '/tmp/sales_db.trn2'

Backup Server4.58.1.1: Database sales_db:6 kilobytes DUMPED. Backup Server3.43.1.1: Dump phase number 3 completed.

Backup Server4.58.1.1: Database sales_db:10 kilobytes DUMPED. Backup Server3.42.1.1: DUMP is complete (database sales_db).

1> load tran sales_db from "/tmp/sales_db.trn2" with headeronly 2> go

Backup Server session id is:13. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.

Backup Server6.28.1.1: Dumpfile name 'ales_db1113903D87' section number 1 mounted on

disk file '/tmp/sales_db.trn2'

This is a log dump of database ID 5, name 'sales_db', from May 19 2011 4:22AM. ASE version: lite_670673-1/Adaptive Server Enterprise/15.7.0/EBF 19186 SMP GA FS3b/B/x86_64/Enterprise Linux/asea.Backup Server version: Backup Server/15.7/EBF 19186 Drop#3B Prelim/B/Linux AMD Opteron/Enterprise


Linux/aseasap/3556/64-bi.Database page size is 2048.

Log begins on page 1986; checkpoint RID=Rid pageid = 0x7c2; row num = 0x14; previous BEGIN XACT RID=(Rid pageid = 0x7c2; row num = 0x4); sequence dates: (old=May 19 2011 4:21:11:356AM, new=May 19 2011 4:22:31:043AM); truncation page=1986; 123 pages deallocated; requires database with 8192 pages.

Database log version=7; database upgrade version=35; database durability=UNDEFINED.

segmap:0x00000003 lstart=0 vstart=[vpgdevno=1 vpvpn=0] lsize=1536 unrsvd=192 segmap:0x00000004 lstart=1536 vstart=[vpgdevno=2 vpvpn=0] lsize=1536 unrsvd=1530

Unavailable disk fragment: lstart=3072 lsize=5120

The database contains 8192 logical pages (16 MB) and 3072 physical pages (6MB).

1> create database sales_db1 on sales_db_dev=3 log on sales_db_log1=3, sales_db_log2=10

2> go

CREATE DATABASE:allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_dev' (1536 logical pages requested).

CREATE DATABASE:allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log1' (1536 logical pages requested).

CREATE DATABASE:allocating 5120 logical pages (10.0 megabytes) on disk 'sales_db_log2' (5120 logical pages requested).

Warning:The database 'sales_db1' is using an unsafe virtual device 'sales_db_dev'.The recovery of this database can not be guaranteed.

Database 'sales_db1' is now online.

1> load database sales_db1 from "/tmp/sales_db.dmp" 2> go

Backup Backup Server session id is:15. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server. Backup Server6.28.1.1: Dumpfile name 'ales_db111390340B' section number 1 mounted on disk file '/tmp/sales_db.dmp'

Backup Server4.188.1.1: Database sales_db1:6148 kilobytes (37%) LOADED. Backup Server4.188.1.1: Database sales_db1:6160 kilobytes (100%) LOADED. Backup Server3.42.1.1: LOAD is complete (database sales_db1).

All dumped pages have been loaded. ASE is now clearing pages above page 3072, which were not present in the database just loaded.

ASE has finished clearing database pages.

Started estimating recovery log boundaries for database 'sales_db1'.

Database 'sales_db1', checkpoint=(1863, 13), first=(1863, 13), last=(1865, 7). Completed estimating recovery log boundaries for database 'sales_db1'.

Started ANALYSIS pass for database 'sales_db1'. Completed ANALYSIS pass for database 'sales_db1'.

Started REDO pass for database 'sales_db1'.The total number of log records to process is 22.

Redo pass of recovery has processed 2 committed and 0 aborted transactions. Completed REDO pass for database 'sales_db1'.

Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.

1> load tran sales_db1 from "/tmp/sales_db.trn1" 2> go


Backup Server session id is:17. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.

Backup Server6.28.1.1: Dumpfile name 'ales_db1113903D37' section number 1 mounted on

disk file '/tmp/sales_db.trn1'

Backup Server4.58.1.1: Database sales_db1:250 kilobytes LOADED. Backup Server4.58.1.1: Database sales_db1:258 kilobytes LOADED. Backup Server3.42.1.1: LOAD is complete (database sales_db1).

Started estimating recovery log boundaries for database 'sales_db1'.

Database 'sales_db1', checkpoint=(1863, 13), first=(1863, 13), last=(1986, 3). Completed estimating recovery log boundaries for database 'sales_db1'.

Started ANALYSIS pass for database 'sales_db1'. Completed ANALYSIS pass for database 'sales_db1'.

Started REDO pass for database 'sales_db1'.The total number of log records to process is 365.

Redo pass of recovery has processed 8 committed and 0 aborted transactions. Completed REDO pass for database 'sales_db1'.

Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.

1> load tran sales_db1 from "/tmp/sales_db.trn2" 2> go

Backup Server session id is:19. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.

Backup Server6.28.1.1: Dumpfile name 'ales_db1113903D87' section number 1 mounted on

disk file '/tmp/sales_db.trn2'

Backup Server4.58.1.1: Database sales_db1:10 kilobytes LOADED. Backup Server3.42.1.1: LOAD is complete (database sales_db1). Started estimating recovery log boundaries for database 'sales_db1'.

Database 'sales_db1', checkpoint=(1986, 3), first=(1986, 3), last=(1986, 20). Completed estimating recovery log boundaries for database 'sales_db1'.

Started ANALYSIS pass for database 'sales_db1'. Completed ANALYSIS pass for database 'sales_db1'.

Started REDO pass for database 'sales_db1'.The total number of log records to process is 16.

Redo pass of recovery has processed 2 committed and 0 aborted transactions. Completed REDO pass for database 'sales_db1'.

Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.

1> online database sales_db1 2> go

Started estimating recovery log boundaries for database 'sales_db1'.

Database 'sales_db1', checkpoint=(1986, 20), first=(1986, 19), last=(1986, 20). Completed estimating recovery log boundaries for database 'sales_db1'.

Started ANALYSIS pass for database 'sales_db1'. Completed ANALYSIS pass for database 'sales_db1'.

Recovery of database 'sales_db1' will undo incomplete nested top actions.

Started UNDO pass for database 'sales_db1'.The total number of log records to process is 2.

Undo pass of recovery has processed 1 incomplete transactions. Completed UNDO pass for database 'sales_db1'.


Database 'sales_db1' is now online.

1> alter database sales_db1 log off sales_db_log2 2> go

Removing 5120 pages (10.0 MB) from disk 'sales_db_log2' in database 'sales_db1'.

dbid

segmap

lstart

size

vstart location unreservedpgs crdate vdevno

----

------

-------

----

------ -------- -------------- ------------------ ------

6

3

0

1536

1536

0

192

May 19 2011 4:25AM

1

6

4

1536

1536

1536

0

1536

May 19 2011 4:25AM

2

6

0

3072

5120

3072

4

5100

May 19 2011 4:25AM

-6

dbid

segmap

lstart

size

vstart location unreservedpgs crdate vdevno

----

------

-------

----

------ -------- -------------- ------------------ ------

6

3

0

1536

1536

0

192

May 19 2011 4:25AM

1

6

4

1536

1536

1536

0

1536

May 19 2011 4:25AM

2

6

0

3072

5120

3072

4

5100

May 19 2011 4:25AM

-6

1> select * from sysusages where dbid=db_id("sales_db1") 2> go


(3 rows affected)

1> dump database sales_db1 to "/tmp/sales_db1.dmp" with shrink_log 2> go

Backup Server session id is:22. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.

Backup Server4.41.1.1: Creating new disk file /tmp/sales_db1.dmp.

Backup Server6.28.1.1: Dumpfile name 'sales_db11113903EC3' section number 1 mounted

on disk file '/tmp/sales_db1.dmp'

Backup Server4.188.1.1: Database sales_db1:3100 kilobytes (100%) DUMPED. Backup Server3.43.1.1: Dump phase number 1 completed.

Backup Server:3.43.1.1: Dump phase number 2 completed. Backup Server:3.43.1.1: Dump phase number 3 completed.

Backup Server 4.188.1.1: Database sales_db1:3108 kilobytes (100%) DUMPED. Backup Server3.42.1.1: DUMP is complete (database sales_db1).

dbid

segmap

lstart

size

vstart location unreservedpgs crdate vdevno

----

------

-------

----

------ -------- -------------- ------------------- ------

6

3

0

1536

1536 0 192 May 19 2011 4:25AM 3

6

4

1536

1536

1536 0 1530 May 19 2011 4:25AM 4

dbid

segmap

lstart

size

vstart location unreservedpgs crdate vdevno

----

------

-------

----

------ -------- -------------- ------------------- ------

6

3

0

1536

1536 0 192 May 19 2011 4:25AM 3

6

4

1536

1536

1536 0 1530 May 19 2011 4:25AM 4

1> select * from sysusages where dbid=db_id("sales_db1") 2> go


(2 rows affected)




--------------------------------------华丽的分割线-------------------------------------------------------------------------
之前就已经研发成功了能够从Sybase SQL Anywhere的DB文件中恢复数据的工具:ReadASADB。
此工具支持ASA v5.0,v6.0,v7.0,v8.0,v9.0,v10.0,v11.0,v12.0等版本。
恢复Sybase SQL Anywhere的工具在国内应该算首创。

ReadASADB功能
能够从损坏的SQL Anywhere数据文件(.db)和UltraLite数据文件(.udb)上提取数据的非常规恢复工具

  1. 适用于所有的SQL Anywhere版本    包括:5.x,6.x,7.x,8.x,9.x,10.x,11.x,12.x
  2. 适用于所有的UltraLite版本
  3. 能够恢复出来表结构和数据
  4. 能够恢复自定义数据类型
  5. 能够恢复存储过程等对象的语法
  6. 能够导出到目标数据库
  7. 能够导出到SQL文件并生成导入脚本
  8. 支持多种字符集  包括:cp850、cp936、gb18030、utf8等
  9. 能够恢复未加密或者简单加密类型的数据
  10. 简单易用
  11. 限制:不支持AES加密的数据文件
请参考:研发成功了从Sybase SQL Anywhere的DB文件上恢复数据的工具
            SQL Anywhere数据库非常规恢复工具ReadASADB使用介绍

ReadASADB适用场景

各种误操作:

  1. 误截断表(truncate table)
  2. 误删除表(drop table)
  3. 错误的where条件误删数据
  4. 误删除db或log文件
  5. 误删除表中的字段

本工具的应用场景:

1.因为物理磁盘故障、操作系统、系统软件方面或者掉电等等原因导致的Sybase SQL Anywhere数据库无法打开的情况;
2.误操作,包括truncate table,drop table,不正确的where条件导致的误删除等;
Sybase SQL Anywhere无法打开时,比较常见的错误是:Assertion failed。
如:
1、Internal database error *** ERROR *** Assertion failed:201819 (8.0.1.2600) Checkpoint log: invalid bitmap page -- transaction rolled back
2、Internal database error *** ERROR *** Assertion failed:201819 (8.0.1.2600) Page number on page does not match page requested -- transaction rolled back
3、Internal database error *** ERROR *** Assertion failed:200502 (9.0.2.2451) Checksum failure on page 23 -- transaction rolled back
4、File is shorter than expected
5、Internal database error *** ERROR *** Assertion failed: 201116 Invalid free list index page found while processing checkpoint log -- transaction rolled back
6、*** ERROR *** Assertion failed: 51901 Page for requested record not a table page or record not present on page等等。
+-------------------------------------华丽的分割线-------------------------------------------------------------------------