Load database from Solaris (Sybase 15.0.3) to VMWare Solaris (Sybase 15.7.0)

dumpsybase

I need to perform a load of a Sybase (15.0.3) Database from a Sun Solaris 5.10 environment (on sparc architecture) to Sybase (15.7.0) on Solaris 10 (on i86pc architecture) running on my vmware virtual machine.

  • Does anyone have a recipe (correct steps) ?

Best Answer

Recently I had a similar problem …

Here goes all the required steps with comments … !

[On Source]

============================================================================

1) Checks on Source

a. Run (on solaris prompt) On source machine

Execute: 

uname -a

In my case the output was:

SunOS clusterz1 5.10 Generic_142900-03 sun4u sparc SUNW,SPARC-Enterprise*

b. On source Sybase

Execute: 

select @@version
go

In my case the output was:

Adaptive Server Enterprise/15.0.3/EBF 16736 ESD#2/P/Sun_svr4/OS 5.8
/ase1503/2707/64-bit/FBO/Sun Jul 26 10:29:50 2009

2) Checks on Target

a. Run (on solaris prompt) On target machine

Execute:

uname –a

In my case the output was:

SunOS s_wcli0070 5.10 Generic_142910-17 i86pc i386 i86pc

b. On Target Sybase

Execute: 

select @@version
go

In my case the output was:

Adaptive Server Enterprise/15.7.0/EBF 19499 SMP /P/Solaris AMD64/OS 5.10
/ase157/2820/64-bit/FBO/Fri Sep 16 01:47:15 2011

3) Operations on Source

a. Set requested Database to “single” user mode

Execute:

sp_dboption Audit, "single", true go

You should get something similar to:

Database option 'single user' turned ON for database 'Audit'. 
Running CHECKPOINT on database 'Audit' for option 'single user' to take effect.
Execution time: 0,437 seconds

b. Position yourself on the requested database

Execute:

use Audit
go

You should get something similar to:

Execution time: 0,016 seconds

c. Flush Statistics

Execute:

sp_flushstats
go

You should get something similar to:

DBCC execution completed. If DBCC printed error messages, contact a
user with System Administrator (SA) role. DBCC execution completed. If
DBCC printed error messages, contact a user with System Administrator
(SA) role. Execution time: 0,094 seconds

For more details check the following URL:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs109.htm

d. Lets write all dirty pages …

Execute:

checkpoint
go

You should get something similar to:

Execution time: 0,031 seconds

For more details check the following URL:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands10.htm

e. Lets suspend updates on the requested database

quiesce database qaudit hold Audit
go

You should get something similar to:

Execution time: 0,016 seconds

f. Lets resume updates on the requested database

Execute:

quiesce database qaudit release
go

You should get something similar to:

Execution time: 0,016 seconds

For more details check the following URL:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands73.htm

Note: If you don't do the commands in the right sequence you can get the following error when trying to load the database dump:

load database Audit from "compress::9::/dumps/PRODUCTION.Audit_zdump"
go

Adaptive Server cannot load this database because the database that
was dumped was not quiescent when the dump was performed. Run
sp_flushstats before DUMP DATABASE and ensure that the database is not
updated during the dump.

Sybase error code=3151 Severity Level=16, State=2, Transaction State=0
Line 1"

g. Lets perform the compressed dump of the requested database

Execute:

dump database Audit to
"compress::9::/DATADUMPS/daily/PRODUCTION.Audit_zdump"
go

You should get something similar to:

Backup Server session id is:  39.  Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume
change request from the Backup Server. Backup Server: 4.132.1.1:
Attempting to open byte stream device:
'compress::9::/DATADUMPS/daily/PRODUCTION.Audit_zdump::00' Backup
Server: 6.28.1.1: Dumpfile name 'Audit12139079A9  ' section number 1
mounted on byte stream
'compress::9::/DATADUMPS/daily/PRODUCTION.Audit_zdump::00' Backup
Server: 4.188.1.1: Database Audit: 3456 kilobytes (2%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 8918 kilobytes (9%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9090 kilobytes (18%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9348 kilobytes (26%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9520 kilobytes (34%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9690 kilobytes (43%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9862 kilobytes (51%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 10032 kilobytes (60%) DUMPED.
Backup Server: 4.188.1.1: Database Audit: 10204 kilobytes (68%)
DUMPED. Backup Server: 4.188.1.1: Database Audit: 13422 kilobytes
(76%) DUMPED. Backup Server: 4.188.1.1: Database Audit: 13594
kilobytes (85%) DUMPED. Backup Server: 4.188.1.1: Database Audit:
13764 kilobytes (93%) DUMPED. Backup Server: 4.188.1.1: Database
Audit: 13916 kilobytes (100%) DUMPED. Backup Server: 3.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 Audit: 13932 kilobytes (100%)
DUMPED. Backup Server: 3.42.1.1: DUMP is complete (database Audit).
Execution time: 12,078 seconds

For more details check the following URL:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands54.htm

h. Lets take out the single user mode on the requested database …

Execute:

use master
go

sp_dboption Audit, "single", false
go

You should get something similar to:

Database option 'single user' turned OFF for database 'Audit'. Running
CHECKPOINT on database 'Audit' for option 'single user' to take
effect. Execution time: 0,015 seconds Execution time: 0,047 seconds

[On Target]

4) Operations on target

a. Load compressed dump

Execute (on Sybase):

load database Audit from "compress::9::/dumps/PRODUCTION.Audit_zdump"
go

You should get something similar to:

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 Server: 4.132.1.1:
Attempting to open byte stream device:
'compress::9::/dumps/PRODUCTION.Audit_zdump::000' Backup Server:
6.28.1.1: Dumpfile name 'Audit12139081CC  ' section number 1 mounted on byte stream 'compress::9::/dumps/PRODUCTION.Audit_zdump::000'
Backup Server: 4.188.1.1: Database Audit: 8836 kilobytes (1%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 44038 kilobytes (8%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 87050 kilobytes (17%)LOADED.
Backup Server: 4.188.1.1: Database Audit: 130058 kilobytes (25%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 173070 kilobytes (33%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 216590 kilobytes (42%) LOADED. 
Backup Server: 4.188.1.1: Database Audit: 259602 kilobytes (50%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 303122 kilobytes (59%) LOADED. 
Backup Server: 4.188.1.1: Database Audit: 346134 kilobytes (67%) LOADED. 
Backup Server: 4.188.1.1: Database Audit: 386582 kilobytes (75%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 429594 kilobytes (83%) LOADED. 
Backup Server: 4.188.1.1: Database Audit: 473114 kilobytes (92%) LOADED. 
Backup Server: 4.188.1.1: Database Audit: 512028 kilobytes (100%) LOADED. 
Backup Server: 4.188.1.1: Database Audit: 512036 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database Audit). 
Started cross-platform conversion for database Audit. 
Started cross-platform conversion for system objects.
Cross-platform conversion for database Audit: 1136 pages completed.
Completed cross-platform conversion for system objects. 
Started cross-platform conversion for user objects. 
Cross-platform conversion for database Audit: 10049 pages completed. 
Cross-platform conversion for database Audit: 20249 pages completed. 
Cross-platform conversion for database Audit: 30449 pages completed. 
Cross-platform conversion for database Audit: 40649 pages completed.
Cross-platform conversion for database Audit: 50849 pages completed. 
Cross-platform conversion for database Audit: 61049 pages completed. 
Cross-platform conversion for database Audit: 71249 pages completed.
Cross-platform conversion for database Audit: 81449 pages completed. 
Cross-platform conversion for database Audit: 91649 pages completed. 
Cross-platform conversion for database Audit: 101849 pages completed. 
Cross-platform conversion for database Audit: 112049 pages completed. 
Cross-platform conversion for database Audit: 122249 pages completed. 
Cross-platform conversion for database Audit: 132449 pages completed. 
Cross-platform conversion for database Audit: 142649 pages completed. 
Cross-platform conversion for database Audit: 152849 pages completed. 
Cross-platform conversion for database Audit: 163049 pages completed. 
Cross-platform conversion for database Audit: 173249 pages completed. 
Cross-platform conversion for database Audit: 183449 pages completed. 
Cross-platform conversion for database Audit: 193649 pages completed. 
Cross-platform conversion for database Audit: 203849 pages completed. 
Cross-platform conversion for database Audit: 214049 pages completed. 
Cross-platform conversion for database Audit: 224249 pages completed. 
Cross-platform conversion for database Audit: 234449 pages completed. 
Cross-platform conversion for database Audit: 244649 pages completed. 
Cross-platform conversion for database Audit: 254849 pages completed. 
Completed cross-platform  conversion for user objects. 
Started cross-platform conversion for log records. 
Cross-platform conversion for database Audit: 35 pages completed. 
Completed cross-platform conversion for log records.
Completed cross-platform conversion for database Audit. 
Started estimating recovery log boundaries for database 'Audit'. 
Database 'Audit', checkpoint=(179295, 3), first=(179295, 3), last=(179295, 6).
Completed estimating recovery log boundaries for database 'Audit'.
Started ANALYSIS pass for database 'Audit'. 
Completed ANALYSIS pass for database 'Audit'. 
Started REDO pass for database 'Audit'.  
The total number of log records to process is 4. 
Redo pass of recovery has processed 1 committed and 0 aborted transactions. 
Completed REDO pass for database 'Audit'.  
Use the ONLINE DATABASE command to bring this database online; 
ASE will not bring it online automatically. 
Execution time: 57,64 seconds

b. Just put the requested database online

Execute (on Sybase):

online database Audit go

You should get something similar to:

Started estimating recovery log boundaries for database 'Audit'.
Database 'Audit', checkpoint=(179295, 3), first=(179295, 3), last=(179295, 6).
Completed estimating recovery log boundaries for database 'Audit'.
Started ANALYSIS pass for database 'Audit'.
Completed ANALYSIS pass for database 'Audit'.
Recovery of database 'Audit' will undo incomplete nested top actions.
Database 'Audit' appears to be at an older version '15030' than the present installation at version '15700'; ASE will assess it, and upgrade it as required.
Database 'Audit': beginning upgrade step [ID  1564]: executing SQL statement ("declare @dbid int select @dbid = db...")
Database 'Audit': beginning upgrade step [ID     2]: validate basic system type data 
Database 'Audit': beginning upgrade step [ID     6]: alter table (table syspartitions)
Database 'Audit': beginning upgrade step [ID     9]: executing SQL statement ("if exists (select 1 from syscolumns...")
Database 'Audit': beginning upgrade step [ID    10]: alter table (table sysobjects)
Database 'Audit': beginning upgrade step [ID    11]: alter table (table syscolumns)
Database 'Audit': beginning upgrade step [ID    69]: executing SQL statement ("if 2 = (select count(name) from sys...")
Database 'Audit': beginning upgrade step [ID    27]: alter table (table syscomments)
Database 'Audit': beginning upgrade step [ID    80]: alter table (table sysattributes)
Database 'Audit': beginning upgrade step [ID    91]: executing SQL statement ("update systypes set length=@@maxpag...")
Database 'Audit': beginning upgrade step [ID   245]: Update system roles in system tables. 
Database 'Audit': beginning upgrade step [ID   246]: clear unused columns (table sysindexes)
Database 'Audit': beginning upgrade step [ID   248]: update system catalog protection data 
Database 'Audit': beginning upgrade step [ID  1105]: noting the present database upgrade level 
Database 'Audit': beginning upgrade step [ID  1132]: noting the present database upgrade level 
Database 'Audit': beginning upgrade step [ID  1299]: noting the present database upgrade level 
Database 'Audit': beginning upgrade step [ID  1400]: noting the present database upgrade level 
Database 'Audit': beginning upgrade step [ID  1502]: executing SQL statement ("if exists (select 1 from sysobjects...")
Database 'Audit': beginning upgrade step [ID  1503]: executing SQL statement ("create view sysquerymetrics (uid, g...")
Database 'Audit': beginning upgrade step [ID    73]: executing SQL statement ("update sysobjects set sysstat = sys...")
Database 'Audit': beginning upgrade step [ID  1550]: noting the present database upgrade level 
Database 'Audit': beginning upgrade step [ID  1702]: noting the present database upgrade level 
Database 'Audit': beginning upgrade step [ID  1719]: executing SQL statement ("update sysattributes set object_inf...")
Database 'Audit': beginning upgrade step [ID  1720]: noting the present database upgrade level 
Upgrade has finished in database 'Audit'. ASE is now updating table level statistics for its system tables. Please be patient.
Checking table 'sysstatistics' (object ID 24): Logical page size is 2048 bytes.

Checking partition 'sysstatistics_24' (partition ID 24) of table 'sysstatistics'. The logical page size of this table is 2048 bytes.

The indexes for 'sysstatistics' are already correct.  They will not be rebuilt.
Checking table 'sysobjects' (object ID 1): Logical page size is 2048 bytes.

Checking partition 'sysobjects_1' (partition ID 1) of table 'sysobjects'. The logical page size of this table is 2048 bytes.

The indexes for 'sysobjects' are already correct.  They will not be rebuilt.
Checking table 'sysindexes' (object ID 2): Logical page size is 2048 bytes.

Checking partition 'sysindexes_2' (partition ID 2) of table 'sysindexes'. The logical page size of this table is 2048 bytes.

The indexes for 'sysindexes' are already correct.  They will not be rebuilt.
Checking table 'syscolumns' (object ID 3): Logical page size is 2048 bytes.

Checking partition 'syscolumns_3' (partition ID 3) of table 'syscolumns'. The logical page size of this table is 2048 bytes.

The indexes for 'syscolumns' are already correct.  They will not be rebuilt.
Checking table 'systypes' (object ID 4): Logical page size is 2048 bytes.

Checking partition 'systypes_4' (partition ID 4) of table 'systypes'. The logical page size of this table is 2048 bytes.

The indexes for 'systypes' are already correct.  They will not be rebuilt.
Checking table 'sysprocedures' (object ID 5): Logical page size is 2048 bytes.

Checking partition 'sysprocedures_5' (partition ID 5) of table 'sysprocedures'. The logical page size of this table is 2048 bytes.

The indexes for 'sysprocedures' are already correct.  They will not be rebuilt.
Checking table 'syscomments' (object ID 6): Logical page size is 2048 bytes.

Checking partition 'syscomments_6' (partition ID 6) of table 'syscomments'. The logical page size of this table is 2048 bytes.

The indexes for 'syscomments' are already correct.  They will not be rebuilt.
Checking table 'syssegments' (object ID 7): Logical page size is 2048 bytes.

Checking partition 'syssegments_7' (partition ID 7) of table 'syssegments'. The logical page size of this table is 2048 bytes.

The table 'syssegments' has no indexes.
Checking table 'syslogs' (object ID 8): Logical page size is 2048 bytes.

Checking partition 'syslogs_8' (partition ID 8) of table 'syslogs'. The logical page size of this table is 2048 bytes.

*** NOTICE: Space used on the log segment is 401 pages (0.78 MB), 0.39%.
*** NOTICE: Space reserved on the log segment is 0 pages (0.00 MB), 0.00%.
*** NOTICE: Space free on the log segment is 101999 pages (199.22 MB), 99.61%.
The table 'syslogs' has no indexes.
Checking table 'sysprotects' (object ID 9): Logical page size is 2048 bytes.

Checking partition 'sysprotects_9' (partition ID 9) of table 'sysprotects'. The logical page size of this table is 2048 bytes.

The indexes for 'sysprotects' are already correct.  They will not be rebuilt.
Checking table 'sysusers' (object ID 10): Logical page size is 2048 bytes.

Checking partition 'sysusers_10' (partition ID 10) of table 'sysusers'. The logical page size of this table is 2048 bytes.

The indexes for 'sysusers' are already correct.  They will not be rebuilt.
Checking table 'sysalternates' (object ID 11): Logical page size is 2048 bytes.

Checking partition 'sysalternates_11' (partition ID 11) of table 'sysalternates'. The logical page size of this table is 2048 bytes.

The indexes for 'sysalternates' are already correct.  They will not be rebuilt.
Checking table 'sysdepends' (object ID 12): Logical page size is 2048 bytes.

Checking partition 'sysdepends_12' (partition ID 12) of table 'sysdepends'. The logical page size of this table is 2048 bytes.

The indexes for 'sysdepends' are already correct.  They will not be rebuilt.
Checking table 'syskeys' (object ID 13): Logical page size is 2048 bytes.

Checking partition 'syskeys_13' (partition ID 13) of table 'syskeys'. The logical page size of this table is 2048 bytes.

The indexes for 'syskeys' are already correct.  They will not be rebuilt.
Checking table 'sysgams' (object ID 14): Logical page size is 2048 bytes.
The total number of data pages in this table is 8.
The indexes for 'sysgams' are already correct.  They will not be rebuilt.
Checking table 'sysusermessages' (object ID 15): Logical page size is 2048 bytes.

Checking partition 'csysusermessages_15' (partition ID 15) of table 'sysusermessages'. The logical page size of this table is 2048 bytes.

The indexes for 'sysusermessages' are already correct.  They will not be rebuilt.
Checking table 'sysreferences' (object ID 16): Logical page size is 2048 bytes.

Checking partition 'sysreferences_16' (partition ID 16) of table 'sysreferences'. The logical page size of this table is 2048 bytes.

The indexes for 'sysreferences' are already correct.  They will not be rebuilt.
Checking table 'sysconstraints' (object ID 17): Logical page size is 2048 bytes.

Checking partition 'sysconstraints_17' (partition ID 17) of table 'sysconstraints'. The logical page size of this table is 2048 bytes.

The indexes for 'sysconstraints' are already correct.  They will not be rebuilt.
Checking table 'systhresholds' (object ID 18): Logical page size is 2048 bytes.

Checking partition 'systhresholds_18' (partition ID 18) of table 'systhresholds'. The logical page size of this table is 2048 bytes.

The table 'systhresholds' has no indexes.
Checking table 'sysroles' (object ID 19): Logical page size is 2048 bytes.

Checking partition 'sysroles_19' (partition ID 19) of table 'sysroles'. The logical page size of this table is 2048 bytes.

The indexes for 'sysroles' are already correct.  They will not be rebuilt.
Checking table 'sysattributes' (object ID 21): Logical page size is 2048 bytes.

Checking partition 'sysattributes_21' (partition ID 21) of table 'sysattributes'. The logical page size of this table is 2048 bytes.

The indexes for 'sysattributes' are already correct.  They will not be rebuilt.
Checking table 'sysslices' (object ID 22): Logical page size is 2048 bytes.

Checking partition 'csysslices_22' (partition ID 22) of table 'sysslices'. The logical page size of this table is 2048 bytes.

The indexes for 'sysslices' are already correct.  They will not be rebuilt.
Checking table 'systabstats' (object ID 23): Logical page size is 2048 bytes.

Checking partition 'systabstats_23' (partition ID 23) of table 'systabstats'. The logical page size of this table is 2048 bytes.

The indexes for 'systabstats' are already correct.  They will not be rebuilt.
Checking table 'sysstatistics' (object ID 24): Logical page size is 2048 bytes.

Checking partition 'sysstatistics_24' (partition ID 24) of table 'sysstatistics'. The logical page size of this table is 2048 bytes.

The indexes for 'sysstatistics' are already correct.  They will not be rebuilt.
Checking table 'sysxtypes' (object ID 25): Logical page size is 2048 bytes.

Checking partition 'sysxtypes_25' (partition ID 25) of table 'sysxtypes'. The logical page size of this table is 2048 bytes.

The indexes for 'sysxtypes' are already correct.  They will not be rebuilt.
Checking table 'sysjars' (object ID 26): Logical page size is 2048 bytes.

Checking partition 'sysjars_26' (partition ID 26) of table 'sysjars'. The logical page size of this table is 2048 bytes.

The indexes for 'sysjars' are already correct.  They will not be rebuilt.
Checking table 'sysqueryplans' (object ID 27): Logical page size is 2048 bytes.

Checking partition 'sysqueryplans_27' (partition ID 27) of table 'sysqueryplans'. The logical page size of this table is 2048 bytes.

The indexes for 'sysqueryplans' are already correct.  They will not be rebuilt.
Checking table 'syspartitions' (object ID 28): Logical page size is 2048 bytes.

Checking partition 'syspartitions_28' (partition ID 28) of table 'syspartitions'. The logical page size of this table is 2048 bytes.

The indexes for 'syspartitions' are already correct.  They will not be rebuilt.
Checking table 'syspartitionkeys' (object ID 29): Logical page size is 2048 bytes.

Checking partition 'syspartitionkeys_29' (partition ID 29) of table 'syspartitionkeys'. The logical page size of this table is 2048 bytes.

The indexes for 'syspartitionkeys' are already correct.  They will not be rebuilt.
Checking table 'sysencryptkeys' (object ID 98): Logical page size is 2048 bytes.

Checking partition 'sysencryptkeys_98' (partition ID 98) of table 'sysencryptkeys'. The logical page size of this table is 2048 bytes.

The indexes for 'sysencryptkeys' are already correct.  They will not be rebuilt.
WARNING: One or more indexes on user tables may have been marked as 'suspect' making these indexes unusable. Use the sp_post_xpload stored procedure to check and rebuild these indexes.
Database 'Audit' is now online.
Execution time: 6,516 seconds

*/

Related Question