Database refresh in Sybase

Database refresh is routine task for database administrators. Following are the steps needs to be performed in database refresh activity.

Scenario:

Assume that, We have two instances one is on production server and another is development server. When we are loading data from production to development server, development server database size should be greater than or equal to the production server.If the development server database is less than the production server, then we will receive error.

Production server instance name and database name: K2PRD1 and k2_update
Development server instance name and database name: K2DEV1 and k2_update

Steps Involved in Database refresh:
Dump Database:
SCP
BCP (out)
LOAD Database
Online Database
Delete bcp out tables
BCP (in)

Dump Database: Dump database is used to make backup copy of the entire database.

1> dump database k2_update to ‘/opt/sap/dump/k2_update/k2_update09122015.dmp’
2> go

Backup Server: 4.171.1.1: The current value of ‘reserved pages threshold’ is 85%.
Backup Server: 4.171.1.2: The current value of ‘allocated pages threshold’ is 40%.
Backup Server: 4.171.1.5: The current value of ‘parallel scan’ is 2.
Backup Server session id is: 5. Use this value when executing the ‘sp_volchanged’ system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /opt/sap/dump/k2_update/k2_update09122015.dmp.
Backup Server: 6.28.1.1: Dumpfile name ‘k2_update1534310AE0 ‘ section number 1 mounted on disk file ‘/opt/sap/dump/bank/k2_update09122015.dmp’
Backup Server: 4.188.1.1: Database k2_update: 780 kilobytes (1%) DUMPED.
Backup Server: 4.188.1.1: Database k2_update: 1258 kilobytes (13%) DUMPED.
Backup Server: 4.188.1.1: Database k2_update: 1596 kilobytes (28%) DUMPED.
Backup Server: 4.188.1.1: Database k2_update: 1934 kilobytes (43%) DUMPED.
Backup Server: 4.188.1.1: Database k2_update: 2272 kilobytes (59%) DUMPED.
Backup Server: 4.188.1.1: Database k2_update: 2610 kilobytes (74%) DUMPED.
Backup Server: 4.188.1.1: Database k2_update: 2948 kilobytes (89%) DUMPED.
Backup Server: 4.188.1.1: Database k2_update: 3198 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 bank: 3212 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database k2_update).
1>

SCP: The scp command used to copy files or directories between a local and a remote system or between two remote systems. We can use this command from a remote system or from the local system. The scp command uses ssh for data transfer.

scp -p ‘/opt/sap/dump/k2_update/k2_update09122015.dmp’ user@K2DEV1:’/opt/sap/dump/k2_update/’

BCP (out): This is OS level command.

[root@localhost sap]# bcp k2_update..sysusers out ‘/opt/sap/sysusers.out’ -Usa -Shp -c -n
Warning: ‘n’ overrides ‘c’.
Password:
Starting copy…
27 rows copied.

[root@localhost sap]# bcp k2_update..sysalternates out ‘/opt/sap/sysalternates .out’ -Usa -Shp -C -n
Password:
Starting copy…
5 rows copied.

[root@localhost sap]# bcp k2_update..sysprotects out ‘/opt/sap/sysprotects.out’ -Usa -Shp -C -n
Password:
Starting copy…
67 rows copied.

LOAD Database
1>use master
2>go
1> load database k2_update from “/opt/sap/bank09122015.dmp”
2> go

Backup Server session id is: 5. 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 ‘bank1534310AE0 ‘ section number 1 mounted on disk file ‘/opt/sap/bank09122015.dmp’
Backup Server: 4.188.1.1: Database k2_update: 69638 kilobytes (8%) LOADED.
Backup Server: 4.188.1.1: Database k2_update: 155656 kilobytes (19%) LOADED.
Backup Server: 4.188.1.1: Database k2_update: 241674 kilobytes (29%) LOADED.
Backup Server: 4.188.1.1: Database k2_update: 327692 kilobytes (40%) LOADED.
Backup Server: 4.188.1.1: Database k2_update: 413710 kilobytes (50%) LOADED.
Backup Server: 4.188.1.1: Database k2_update: 499728 kilobytes (61%) LOADED.
Backup Server: 4.188.1.1: Database k2_update: 563218 kilobytes (68%) LOADED.
Backup Server: 4.188.1.1: Database k2_update: 563232 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database k2_update).
All dumped pages have been loaded. ASE is now clearing pages above page 140800, which were not present in the database just loaded.
ASE has finished clearing database pages.
Started estimating recovery log boundaries for database ‘k2_update’.
Database ‘k2_update’, checkpoint=(128010, 17), first=(128010, 17), last=(128011, 3).
Completed estimating recovery log boundaries for database ‘k2_update’.
Started ANALYSIS pass for database ‘k2_update’.
Completed ANALYSIS pass for database ‘k2_update’.
Started REDO pass for database ‘k2_update’. The total number of log records to process is 16.
Redo pass of recovery has processed 1 committed and 0 aborted transactions.
Completed REDO pass for database ‘k2_update’.
Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.

Online Database

1>online database k2_update
2>go

Delete bcp out tables

1>use k2_update
2>go
1>delete from k2_update..sysusers
2>go
1>delete from k2_update..sysprotects
2>go
1>delete from k2_update..sysalternates
2>go

BCP (in)

[root@localhost sap]# bcp k2_update..sysusers in ‘/opt/sap/sysusers.out’ -Usa -k2s -c -n
Warning: ‘n’ overrides ‘c’.
Password:
Starting copy…
27 rows copied.

[root@localhost sap]# bcp k2_update..sysalternates in ‘/opt/sap/sysalternates .out’ -Usa -Sk2s -C -n
Password:
Starting copy…
5 rows copied.

[root@localhost sap]# bcp k2_update..sysprotects in ‘/opt/sap/sysprotects.out’ -Usa -Sk2s -C -n
Password:
Starting copy…
67 rows copied.

How to check db refresh completed successfully or not(backup log)

CheapSexCams
You can leave a response, or trackback from your own site.

Leave a Reply

Powered by k2schools
%d bloggers like this: