Replication Server Thread Flow

Sybase Dump verification

TAKEN DUMP
1> load database pm from '/OracleDumps/opctlsy2/pm.verific.dmp' with headeronly
2>
Backup Server session id is: 14. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'pm100050E6BD ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/pm.verific.dmp'
This is a database dump of database ID 4, name 'pm', from Jan 5 2010 4:24PM. ASE version: Adaptive Server Enterprise/15.0.2/EBF 14328/P/Sun_svr4/OS 5.8/ase1502/2486/64-bit/FBO/Thu May 24 12:. Backup Server version: Backup Server/15.0.2/Sun_svr4/OS 5.8/ase1502/2902/32-bit/OPT/Thu May 24 08:04:12 2007. Database page size is 2048.
Database contains 15360 pages; checkpoint RID=(Rid pageid = 0x7e0; row num = 0x2); next object ID=1801054421; sort order ID=50, status=0; charset ID=1.
Database log version=7; database upgrade version=35.
segmap: 0x00000007 lstart=0 vstart=[vpgdevno=8 vpvpn=0] lsize=5120 unrsvd=3546
segmap: 0x00000007 lstart=5120 vstart=[vpgdevno=23 vpvpn=0] lsize=10240 unrsvd=10193
1> load database pm from '/OracleDumps/opctlsy2/pm.verific.dmp' with listonly
2>
Backup Server session id is: 16. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.34.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Volume name:' '
Backup Server: 4.36.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
File name: 'pm100050E6BD '
Create date & time: Tuesday, Jan 5, 2010, 16:24:29
Expiration date & time: Tuesday, Jan 5, 2010, 00:00:00
Database name: 'pm '
Backup Server: 6.30.1.3: Device /OracleDumps/opctlsy2/pm.verific.dmp: Volume cataloguing complete.
1> load database pm from '/OracleDumps/opctlsy2/pm.verific.dmp' with listonly=full
2>
Backup Server session id is: 18. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.35.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Label name: 'VOL1'
Volume id: ' '
Access code: ' '
Reserved: ' '
Compression level: '0'
Owner id: ' '
Reserved: ' '
Labeling version: 7
Backup Server: 4.37.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Label id: 'HDR1'
File name: 'pm100050E6BD '
Stripe count: 1
Device typecount: 1
Archive volume number: 1
Stripe position: 0
Generation number: 0001
Generation version: 00
Backup Server: 4.148.1.1:
Create date & time: Tuesday, Jan 5, 2010, 16:24:29
Expiration date & time: Tuesday, Jan 5, 2010, 00:00:00
Access code: ' '
File block count: 0
Sybase id string: 'Sybase '
Reserved: ' '
Backup Server: 4.174.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Label id: 'HDR2'
Record format: 'F'
Max. bytes/block: 65536
Record length: 02048
Backup format version: 03
Reserved: ' B'
Database name: 'pm '
Buffer offset length: 00
Async. database I/Os: '000000002'
Max. database I/O size: '0000128'
Number of I/O Buffers: '03'
database page size in KiloBytes: '0000002'
Reserved: ' '
Backup Server: 4.37.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Label id: 'EOF1'
File name: 'pm100050E6BD '
Stripe count: 1
Device typecount: 1
Archive volume number: 1
Stripe position: 0
Generation number: 0001
Generation version: 00
Backup Server: 4.148.1.1:
Create date & time: Tuesday, Jan 5, 2010, 16:24:29
Expiration date & time: Tuesday, Jan 5, 2010, 00:00:00
Access code: ' '
File block count: 1483
Sybase id string: 'Sybase '
Reserved: ' '
Backup Server: 4.174.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Label id: 'EOF2'
Record format: 'F'
Max. bytes/block: 65536
Record length: 02048
Backup format version: 03
Reserved: ' B'
Database name: 'pm '
Buffer offset length: 00
Async. database I/Os: '000000002'
Max. database I/O size: '0000128'
Number of I/O Buffers: '03'
database page size in KiloBytes: '0000002'
Reserved: ' '
Backup Server: 4.154.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Label id: 'EOF3'
Total data stored on the volume: 2966 KiloBytes
Backup Server: 6.30.1.3: Device /OracleDumps/opctlsy2/pm.verific.dmp: Volume cataloguing complete.
CORRUPTED THE DUMP ;)
1> load database pm from '/OracleDumps/opctlsy2/pm.verific.dmp' with headeronly
2>
Backup Server session id is: 21. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.12.2.1: Label validation error: HDR1 label not found.
Backup Server: 6.31.2.4: Volume rejected.
Backup Server: 1.14.2.2: Unrecoverable I/O or volume error. This DUMP or LOAD session must exit.
Backup Server: 6.32.2.3: /OracleDumps/opctlsy2/pm.verific.dmp: volume not valid or not requested (server: , session id: 21.)
Backup Server: 1.14.2.4: Unrecoverable I/O or volume error. This DUMP or LOAD session must exit.
Msg 8009, Level 16, State 1:
Server 'GALLARDO', Line 1:
Error encountered by Backup Server. Please refer to Backup Server messages for details.
1> load database pm from '/OracleDumps/opctlsy2/pm.verific.dmp' with listonly
2>
Backup Server session id is: 23. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.12.2.1: Label validation error: HDR1 label not found.
Backup Server: 6.31.2.4: Volume rejected.
Backup Server: 6.32.2.3: /OracleDumps/opctlsy2/pm.verific.dmp: volume not valid or not requested (server: , session id: 23.)
Backup Server: 1.14.2.4: Unrecoverable I/O or volume error. This DUMP or LOAD session must exit.
1> load database pm from '/OracleDumps/opctlsy2/pm.verific.dmp' with listonly = full
2>
Backup Server session id is: 25. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.12.2.1: Label validation error: HDR1 label not found.
Backup Server: 6.31.2.4: Volume rejected.
Backup Server: 6.32.2.3: /OracleDumps/opctlsy2/pm.verific.dmp: volume not valid or not requested (server: , session id: 25.)
Backup Server: 1.14.2.4: Unrecoverable I/O or volume error. This DUMP or LOAD session must exit.

Mount Unmount databases in Sybase

1> unmount database mydb to '/OracleDumps/opctlsy2/sybase/mydb.mnfst'
2>
00:00000:00011:2008/07/29 17:02:43.76 kernel Deactivating virtual device 1, '/OracleDumps/opctlsy2/sybase/data/mdev.dat'.
00:00000:00011:2008/07/29 17:02:43.76 kernel Deactivating virtual device 3, '/OracleDumps/opctlsy2/sybase/data/mdev1.dat'.
1> 1>
1>
Password:
Msg 2401, Level 11, State 2:
Character set conversion is not available between client character set 'iso_1' and server character set 'ascii_8'.
No conversions will be done.

1> sp_helpdb
2>
name db_size owner dbid created status
-------------- ------------- ----- ----- ------------ --------------------------------------------------------------------
master 99.5 MB sa 1 Jan 01, 1900 mixed log and data
model 3.0 MB sa 3 Jan 01, 1900 mixed log and data
sybsystemdb 3.0 MB sa 31513 Jul 16, 2008 mixed log and data
sybsystemprocs 170.0 MB sa 31514 Feb 19, 2008 trunc log on chkpt, mixed log and data
tempdb 4.0 MB sa 2 Jul 29, 2008 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
(1 row affected)
(return status = 0)
1> mount database all from '/OracleDumps/opctlsy2/sybase/mydb.mnfst'
2>
00:00000:00012:2008/07/29 17:04:17.36 kernel Initializing virtual device 1, '/OracleDumps/opctlsy2/sybase/data/mdev.dat' with dsync 'on'.
00:00000:00012:2008/07/29 17:04:17.36 kernel Virtual device 1 started using asynchronous i/o.
00:00000:00012:2008/07/29 17:04:17.36 kernel Initializing virtual device 3, '/OracleDumps/opctlsy2/sybase/data/mdev1.dat' with dsync 'on'.
00:00000:00012:2008/07/29 17:04:17.36 kernel Virtual device 3 started using asynchronous i/o.
00:00000:00012:2008/07/29 17:04:17.37 server Log contains all committed transactions until 2008/07/29 16:55:49.67 for database mydb.
Started estimating recovery log boundaries for database 'mydb'.
Database 'mydb', checkpoint=(2567, 13), first=(2567, 13), last=(2567, 13).
Completed estimating recovery log boundaries for database 'mydb'.
Started ANALYSIS pass for database 'mydb'.
Completed ANALYSIS pass for database 'mydb'.
Started REDO pass for database 'mydb'. The total number of log records to process is 1.
Completed REDO pass for database 'mydb'.
Recovery of database 'mydb' will undo incomplete nested top actions.
Started recovery checkpoint for database 'mydb'.
Completed recovery checkpoint for database 'mydb'.
Started filling free space info for database 'mydb'.
Completed filling free space info for database 'mydb'.
Started cleaning up the default data cache for database 'mydb'.
Completed cleaning up the default data cache for database 'mydb'.
MOUNT DATABASE: Completed recovery of mounted database 'mydb'.
1> sp_helpdb
2>
name db_size owner dbid created status
-------------- ------------- ----- ----- ------------ --------------------------------------------------------------------
master 99.5 MB sa 1 Jan 01, 1900 mixed log and data
model 3.0 MB sa 3 Jan 01, 1900 mixed log and data
mydb 10.0 MB sa 4 Jul 29, 2008 offline
sybsystemdb 3.0 MB sa 31513 Jul 16, 2008 mixed log and data
sybsystemprocs 170.0 MB sa 31514 Feb 19, 2008 trunc log on chkpt, mixed log and data
tempdb 4.0 MB sa 2 Jul 29, 2008 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
(1 row affected)
(return status = 0)
1>

Fixing Corrupt index on system table

1> sp_logiosize 'default'
2>
Msg 644, Level 21, State 5:
Procedure 'sp_logiosize', Line 313:
Index row entry for data row id (817, 15) is missing from index page 1009 of index id 2 of table 'sysattributes' in database 'sag'. Xactid is (10215,24). Drop and re-create the index.
1> sp_dboption sag, 'single', true
2>
Database option 'single user' turned ON for database 'sag'.
Running CHECKPOINT on database 'sag' for option 'single user' to take effect.
(return status = 0)
1>
1> use sag
2>
1> checkpoint
1> use sag
2>
1> sp_fixindex sag,sysattributes,2
2>
There are 22.000000 rows in 1 pages for object 'sysattributes'.
Non-clustered index successfully restored for object 'sysattributes' in 'sag' database.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
(return status = 0)

1> sp_logiosize 'default'
2>
The transaction log for database 'sag' will use I/O size of 4 Kbytes.
(return status = 0)
1>

Dropping a Corrupt Table from Sybase

1> declare @obj int
2> select @obj = id from sysobjects where name = 'dddd'
3> delete syscolumns where id = @obj
4> delete sysindexes where id = @obj
5> delete sysobjects where id in (select constrid from sysconstraints where tableid = @obj)
6> delete sysdepends where depid = @obj
7> delete syskeys where id = @obj
8> delete syskeys where depid = @obj
9> delete sysprotects where id = @obj
10> delete sysconstraints where tableid = @obj
11> delete sysreferences where tableid = @obj
12> delete sysattributes where object = @obj
delete syspartitions where id = @obj
13> 14> delete sysstatistics where id = @obj
15> delete systabstats where id = @obj
16> delete syscomments where id in (select id from sysobjects where deltrig = @obj)
17> delete syscomments where id in (select id from sysobjects where instrig = @obj)
18> delete syscomments where id in (select id from sysobjects where updtrig = @obj)
19> delete sysprocedures where id in (select id from sysobjects where deltrig = @obj)
20> delete sysprocedures where id in (select id from sysobjects where instrig = @obj)
21> delete sysobjects where deltrig = @obj
22> delete sysobjects where instrig = @obj
23> delete sysobjects where updtrig = @obj
24> delete sysobjects where id = @obj
25> delete sysstatistics where id = @obj
26> delete systabstats where id = @obj
delete syspartitionkeys where id = @obj
27>
28>
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(12 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
1> commit tran
2>

Finallly dont forget to destroy the object descriptor from the metadata cache :

1> select object_id('dddd')
2>
-----------
828526954
(1 row affected)
1> sp_monitorconfig 'open objects'
2>
Usage information at date and time: Apr 21 2009 9:54AM.
Name Num_free Num_active Pct_act Max_Used Reuse_cnt
------------------------- ----------- ----------- ------- ----------- -----------
number of open objects 948 52 5.20 52 0
(1 row affected)
(return status = 0)
1> dbcc cacheremove(5,828526954)
2>
1> sp_monitorconfig 'open objects'
2>
Usage information at date and time: Apr 21 2009 9:54AM.
Name Num_free Num_active Pct_act Max_Used Reuse_cnt
------------------------- ----------- ----------- ------- ----------- -----------
number of open objects 949 51 5.10 52 0
(1 row affected)
(return status = 0)
1> select object_id('dddd')
2>
-----------
NULL
(1 row affected)
1>

Delete Trigger example in Sybase

1> sp_helptext empage_delete_restrict
2>
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create trigger empage_delete_restrict on empage
for delete
as
begin
print 'Delete of records not allowed in this table'
rollback transaction
end

(1 row affected)
(return status = 0)
1> select * from empage
2>
empid empage
----------- -----------
654 23
76 45
6 43
100 29
65 60
(5 rows affected)
1> delete from empage where empid = 100
2>
Delete of records not allowed in this table
1> select * from empage
2>
empid empage
----------- -----------
654 23
76 45
6 43
100 29
65 60
(5 rows affected)
1>

Update Trigger Example Sybase

create trigger empdata_to_upd_empage on empdata
for update
as
if update (empid)
begin
update empage set empage.empid = inserted.empid
from
inserted,empage,deleted
where deleted.empid=empage.empid
end

(1 row affected)
(return status = 0)
1> select * from empdata
2>
empname empid
-------------------- -----------
k 654
m 76
o 6
t 98
w 65
(5 rows affected)
1> select * from empage
2>
empid empage
----------- -----------
654 23
76 45
6 43
98 29
65 60
(5 rows affected)

1> update empdata set empid=100 where empname='t'
2>
(1 row affected)
1> select * from empdata
2>
empname empid
-------------------- -----------
k 654
m 76
o 6
t 100
w 65
(5 rows affected)
1> select * from empage
2>
empid empage
----------- -----------
654 23
76 45
6 43
100 29
65 60
(5 rows affected)
1>