How to setup Table level replication in SAP Sybase ASE Step by Step |Table Level Replication Sybase ASE 15.7

How to setup Table level replication in SAP Sybase ASE Step by Step |Table Level Replication Sybase ASE 15.7 and common troubleshoot issues

Primary Server Name  :- PROD_ASE01

Primary Database Name:-Prod_DB

Table Name           :-Prod_Table

Replication Server Name:- REP_REP157_01

Replicated (Target)Server Name  :-DEV_ASE01

Database Name:-DEV_DB

Table Name   :-DEV_Table

 

 

I)Create connection for Prod_DB database in (PROD_ASE01) using rs_init

 

cd /opt/sybase/rep

. SYBASE.sh

cd REP-*

cd install

./rs_init —>utility

 

Configure a Server product

1.Replication Server

Configure Replication Server

1.Install a new replication Server

2.Add a database to replication system

3.Upgrade an existing replication server

4.Downgrade RSSD for an existing replication server

5.Upgrade an existing database in the replication server

6.Enable password encryption for replication server

7.Alter a Replication Server Configuration file password

 

choose2

1.Replication Server Information

2.Database Information

 

choose 1-Replication Server Information

Replication Server :-REP_REP157_01

Password :- Sybase123

 

Choose 2 Database Information

then we have

1.SQL Server name:PROD_ASE01

2.SA User :sa

3.SA password :Sybase123

4.Database name: PROD_DB

5.will database be replicate Yes

6.Maintenance User:proddb_maint

7.Maintenance Password:Sybase123

8.Is this a Physical Connection for existing Logical Connection :No

 

Replication agent information

RS User: REP157_01_ra

RS Password: Sybase123

 

Execute  Replication Server task to activate the Replication

 

 

II)Create connection for DEV_DB database in (DEV_ASE02) using rs_init

 

cd $SYABSE/REP-15_5/install

./rs_init

 

will get below options

=>RS_INIT

1.Release directory: /opt/sybaserep/157

2.Configure a Server product

 

pick option2

 

=>Configure a Server product

1.Replication Server

Configure Replication Server

  1. Install a new replication Server
  2. Add a database to replication system
  3. Upgrade an existing replication server
  4. Downgrade RSSD for an existing replication server
  5. Upgrade an existing database in the replication server
  6. Enable password encryption for replication server
  7. Alter a Replication Server Configuration file password

 

choose2

  1. Replication Server Information
  2. Database Information

Choose 1-Replication Server Information

Replication Server :-REP_REP157_01

Password :- Sybase123

 

choose 2 Database Information

then we have

  1. SQL Sever name :DEV_ASE01
  2. SA User :sa
  3. SA passord:Sybase123

4.Database name:pubs2

  1. Will database be replicate
  2. Maintenance User:devdb_maint

7.Maintenance Password:devdb_maint_ps

8.Is this a Physical Connection for existing Logical Connection :No

 

Execute the Replication Server task

 

III)Need to create the replication definition for PROD_ASE01 Replication subscription for DEV_ASE01 i.e replicated server. Check below command.

Login to the Replication server :-PROD_REP157_01

//Replication Definition for primary server  PROD_ASE01

create replication definition PROD_ASE01_PROD_Table_R

with primary at PROD_ASE01.PROD_TABLE

with replication table named ‘DEV_table’

(id int,fullname varchar(20)) primary key(id)

go

 

//Replication Subscription for DEV_ASE01

 

Subscription create subscription DEV_ASE01_DEV_Table_S

for PROD_ASE01_PROD_Table_R

with replication at DEV_ASE01_DEV_Table

without materialization

 

/* without materialization means either table are sync or they are freshly created */

 

//To validate the replication definition and replication subscription are correct please check

 

check subscription DEV_ASE01_DEV_Table_S

for PROD_ASE01_PROD_Table_R

with replication at DEV_ASE01.DEV_Table

go

 

  1. IV) Marking primary table for Replication using sp_setrepetable.

Login to REP_REP157_01

sp_setrepetable PROD_Table,true

go

 

 

  1. V) Login to DEV_ASE01 add grant permission to maintenance user devdb_maint_user

 

Use DEV_Db

Go

Sp_helpuser

Go

grant all on DEV_Table to devdb_maint_user

go

 

VI)How to Validate table level replication .?

 

Login to PROD_ASE01

 

use PROD_DB

go

insert into PROD_Table(101,sam)

go

insert into PROD_Table(102,mac)

go

select * from PROD_Table

go

 

 

Now login to DEV_ASE01 replicated data server and check if the data is replicated

Use DEV_DB

Go

Select * from DEV_Table

Go

 

**Result should be same as PROD_ASE01 have

 

VII) Table level replication and Troubleshoot

  • Replication Agent:- sp_help_rep_agent at Adaptive Server to display status information for RepAgent thread
  • Invalid Login
  • Missing Replication Server Name in Interfaces
  • Permission Issue
  • Misconfigured Replication Agent
  • Invalid Truncation Page
  • Syslogs Corruption
  • Primary Database Transaction Log Full
  • Standby Database Transaction Log Full
  • Replication Server Errors:-WARNING #32020
  • Stable Queues
  • The Origin Queue ID
  • Data Not Being Replicated
  • Duplicates Being Ignored
  • Corruption in the Queue
  • Data Latency

How to Configure MSA type Database Level Sybase ASE Replication Server Step by Step

How to Create a SAP Sybase ASE Warm Standby Environment Using rs_init step by step|How to use rs_init utility to create a SAP Sybase ASE Warm Standby Environment

 

 

 

 

Advertisements

How to Create a SAP Sybase ASE Warm Standby Environment Using rs_init step by step|How to use rs_init utility to create a SAP Sybase ASE Warm Standby Environment

We have request to setup the Sybase ASE warm standby Replication setup

Sybase ASE primary Server Name:-ActiveASE01
Sybase ASE replicated Server Name:-StandbyASE02
Sybase replication server name:-Rep157_01

Sybase ASE version 15.7 and
Sybase replication server 15.7
Using the Pubs2 database

Step-1)Login to ActiveASE01 and enable the Rep_Aganet

sp_configure ‘enable rep agent’,1
go

Step-2)Login to StandbyASE02 and enable the Rep_Aganet

Similary for StandbyASE02
sp_configure ‘enable rep agent’,1
go

Step-3) Now connect the replication server RS157_01 and create the logical connection

create logical connection to LASE.pubs2
go

//Please make note LogicalServerName.DatabaseName(LASE.pubs2)
Step-4) We need to create the physical Active connection using RS_INIT for Active server(ActiveASE01)

Now begain the rs_init utility

cd $SYABSE/REP-15_5/install
./rs_init

It prompts below options

=>RS_INIT
1.Release directory: /opt/sybaserep/157
2.Configure a Server product

Pick option2

=>Configure a Server product
1.Replication Server
Configure Replication Server
1.Install a new replication Server
2.Add a database to replication system
3.Upgrade an existing replication server
4.Downgrade RSSD for an existing replication server
5.Upgrade an existing database in the replication server
6.Enable password encryption for replication server
7.Alter a Replicaiton Serevr Confgireation file password

choose2
1.Replication Server Information
2.Database Information

choose 1-Replication Server Information
replication Server :-RS157_01
Password :- Sybase123

choose 2 Database Information
then we have
1.SQL Sevre name:ActiveASE01
2.SA User :sa
3.SA passord :Sybase123
4.Database name:pubs2
5.will database be replicate Yes
6.Maintenance User:pubs2_maint
7.Maintenance Password:Sybase123
8.Is this a Physical Connection for existing Logical Connection :Yes
9.Logical DB setup

Choose 9
Logiacl Connection Infrmaton
1.IS this an active Connection or standby connection active //Active Connection
2.Locical DS Name:-LASE
3.Logical DB Name:-Pubs2

Replication agent informatio
RS User: REP157_01_ra
RS Password: Sybase123

Executre Replication Server task to activate the Replication

Step-5)Now login to ActiveASE01
use pubs2
go
sp_repstandby ‘pubs2’,all //replicate data schema changes
go
Step-6) Now sync login

bcp master..syslogins out ‘/var/opt/sybase/DUMP/ActiveASE01/syslogins.out’ -Usa -SASE1 -PSybase123 -c -n

Login to StandbyASE02

sp_configure ‘allow updates’,1 //turned on sp_configure system update
go

performed bcp in StandbyASE02

sp_configure ‘allow updates’,0 //turned off sp_configure system update
go
Add minatence user to StandbyASE02
use pubs2
go
sp_adduser pubs2_maint
go
Step-7)Now we are working on standby connection StandbyASE02

Now need to create physical connection using rs_init utility

cd $SYABSE/REP-15_5/install
./rs_init

will get below options
=>RS_INIT
1.Release directory: /opt/sybaserep/157
2.Configure a Server product

pick option2

=>Configure a Server product
1.Replication Server
Configure Replication Server
1.Install a new replication Server
2.Add a database to replication system
3.Upgrade an existing replication server
4.Downgrade RSSD for an existing replication server
5.Upgrade an existing database in the replication server
6.Enable password encryption for replication server
7.Alter a Replicaiton Serevr Confgireation file password

choose2
1.Replication Server Information
2.Database Information
choose 1-Replication Server Information
replication Server :-RS157_01
Password :- Sybase123

choose 2 Database Information
then we have
1.SQL Sevre name :StandbyASE02
2.SA User :sa
3.SA passord:Sybase123
4.Database name:pubs2
5.will database be replicate
6.Maintenance User:pubs2_maint
7.Maintenance Password:Sybase123
8.Is this a Physical Connection for existing Logical Connection :Yes
9.Logical DB setup

Choose 9
Logiacl Connection Infrmaton
1.IS this an active Connection or standby connection active
2.Locical DS Name:-LASE
3.Logical DB Name:-Pubs2
4.Active DS name:-ActiveASE01
5.Active DB Name:-pubs2
6.Active DB sa User: sa
7.Active DB sa Password:Sybase123
8.Initialise sandtby using dump and load :Yes
9.Use Dump marker to start replication to standby :-Yes
Replication agent informatio
RS User ASE156_01_ra
RS Password: Sybase123

Execture the Replication Server task
Step-8) Login active server ActiveASE01

dump database Pubs2 to ‘/var/opt/sybase/dump/ActiveASE01/pubs2.01022017.dmp’
go
Step-9) Login standby server StandbyASE02

Load database Pubs2 from “/var/opt/sybase/dump/StandbyASE02/pubs2.01022017.dmp
go
online database pubs2
go

Step-10) Now add maitenance user to dbo

use pubs2
go
sp_dropuser pubs2_maint
go

sp_addaliase pubs2_maint,dbo
go
Step-11) Login to rep server RS157_01
admin logical_status
go

resume connection to ActiveASE01.pubs2
go
resume connection to StandbyASE02.pubs2
go
admin logical_status
go
admin who_is_down
go
Step-12)
// Now we will test the Active standby ASE enviournment ,Login ActiveASE01:-create the table t1 in pubs2 ,Validate the replication server
create table t1 (int a,int b)
go

insert t1 vaues (1,2)
go

Login to StandbyASE02
use pubs2
go
select * from t1
go

You will find all data replicated in StandbyASE02 from ActiveASE01

 

 

Also you can find

How to Configure MSA type Database Level Sybase ASE Replication Server Step by Step|How to setup Database level Replication in Sybase ASE

How to Configure MSA type Database Level Sybase ASE Replication Server Step by Step|How to setup Database level Replication in Sybase ASE

Primary:- ASE01
Stand by:-ASE02
Stand by:-ASE03
Replication Server :-RS157_01

Steps1:- login to REP_Sevre

isql -Usa -PSybase123 -SRS157_01

create connection to ASE03.pubs2
set error class to rs_sqlserver_function_class
set username to pubs_maint
set password to Sybase123
with log transfer on
go
Step2:-To configure/sync login from ASE01 to ASE03

bcp master..syslogins out login.txt -Usa -PSybase123

Step3:- Login to ASE03 for bcp in

isql -Usa -PSybase -SASE03

sp_configure ‘allow updates’,1
go

bcp master..syslogin in login.txt -SASE03 -Usa -PSybase123 -c -b1

Step4:-Then assign maintenance replication_role to pubs2_maint on ASE03
grant all replication_role to pubs2_maint
go

Step5:-create the replication defination on RS157_01 for ASE01

eg.
create database replication definition “ASE01_pubs2_d”
with primary at ASE01.pubs2
replicate ddl
go

create subscription ASE01_ASE03_pubs2_s
for ASE01_pubs2_d
with replicate at ASE03.pubs2 without materialization
subscribe to truncate table
go

check subscription ASE01_ASE03_pubs2_s
for ASE01_pubs2_d
with replicate at ASE03.pubs2

Step6:-To sync the data dump ASE01 pub2 and load at ASE03 pub2
perform refresh from ASE01 to ASE03

Steps7:-
resume connection to ASE03.pubs2
go

Steps8:-Test the replication ,login to ASE01

use pubs2
go

//create the table test at ASE01.pub2

create table test (a int,b int)
go
insert test values(1,2)
go

select * from test
go

Step9:- Now check if the data replicated or not at ASE03
and login ASE03 check

use pubs2
go
select * from test
go

Outpue will be same as we have seen in ASE01

How to performed database refresh step by step in SYABSE ASE |Steps invloved in database dump and load |step by step database refresh in SYABSE ASE

Consider the following scenario,In this case we are going to performed database  refresh from prod to dev check the below steps.

Production server instance name :-ASEPPROD01
Database name:-Prodb
Development server instance name:-ASEDEV02
Database name:-Devdb

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

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

dump database Prodb to ‘/var/opt/sybase/dump/ASEPPROD01/Prodb01022017.dmp’
go
2)SCP: The scp command used to copy files dump files from source server ASEPPROD01 to target Server ASEDEV02.The scp command uses ssh for data transfer.

scp -p ‘Prod0001:/var/opt/sybase/dump/ASEPPROD01/Prodb01022017.dmp’ ‘Dev0001:/var/opt/sybase/dump/ASEDEV02/’
3)This is OS level command used take copy of below system tabele sysusers,sysalternates and sysprotects in order to sync the user alias and permission etc

bcp Devdb..sysusers out ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysusers.out’ -Usa -SASEDEV02 -PSybase123 -c -n

bcp Devdb..sysalternates out ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysalternates.out’ -Usa -SASEDEV02 -PSybase123 -c -n

bcp Devdb..sysprotects out ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysprotects.out’ -Usa -SASEDEV02 -PSybase123 -c -n

4)LOAD Database

Now login to server isql -Usa -SASEDEV02 -PSybase123 -w2000

use master
go
load database Devdb from “/var/opt/sybase/dump/ASEDEV02/Prodb01022017.dmp”
go
use Devdb
go
select @@servername,getdate()
go

Online Database Devdb
go

5)Once database is online then delete entries from below table so that we can performed bcp in operation

use Devdb
go
delete from Devdb..sysusers
go
delete from Devdb..sysprotects
go
delete from Devdb..sysalternates
go

6)Performed bcp in

bcp Devdb..sysusers in ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysusers.out’ -Usa -SASEDEV02 -PSybase123 -c -n

bcp Devdb..sysalternates in ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysalternates.out’ -Usa -SASEDEV02 -PSybase123 -c -n

bcp Devdb..sysprotects in ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysprotects.out’ -Usa -SASEDEV02 -PSybase123 -c -n
7)How to check db refresh completed successfully.

i)MDA table master..monOpenDatabases

Select DBID,BackupInProgress,LastBackupFailed,TransactionLogFull,DBName,BackupStartTime,LastCheckpointTime,LastTranLogDumpTime from master..monOpenDatabases

ii)check backup errorlog

cat errorlog_ASEDEV02_BACKUP | grep -i ‘LOAD is complete’

How to performed database refresh step by step in SYABSE ASE |Steps invloved in database dump and load |step by step database refresh in SYABSE ASE

Production server instance name :-ASEPPROD01
Database name:-Prodb
Development server instance name:-ASEDEV02
Database name:-Devdb

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

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

dump database Prodb to ‘/var/opt/sybase/dump/ASEPPROD01/Prodb01022017.dmp’
go
2)SCP: The scp command used to copy files dump files from source server ASEPPROD01 to target Server ASEDEV02
The scp command uses ssh for data transfer.

scp -p ‘Prod0001:/var/opt/sybase/dump/ASEPPROD01/Prodb01022017.dmp’ ‘Dev0001:/var/opt/sybase/dump/ASEDEV02/’
3)This is OS level command used take copy of below system tabele sysusers,sysalternates and sysprotects in order to sync the user alias and permission etc

bcp Devdb..sysusers out ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysusers.out’ -Usa -SASEDEV02 -PSybase123 -c -t ‘|’

/* -c -t ‘|’ this delimitor  is needed inorder avoide common library error */

bcp Devdb..sysalternates out ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysalternates.out’ -Usa -SASEDEV02 -PSybase123 -c -t ‘|’

bcp Devdb..sysprotects out ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysprotects.out’ -Usa -SASEDEV02 -PSybase123 -c -t ‘|’

4)LOAD Database

Now login to server isql -Usa -SASEDEV02 -PSybase123 -w2000

use master
go
load database Devdb from “/var/opt/sybase/dump/ASEDEV02/Prodb01022017.dmp”
go
use Devdb
go
select @@servername,getdate()
go

Online Database Devdb
go

5)Once database is online then delete entries from below table so that we can performed bcp in operation

use master

go

sp_configure “allow updates”,1

go

use Devdb
go
delete from Devdb..sysusers
go
delete from Devdb..sysprotects
go
delete from Devdb..sysalternates
go

6)Performed bcp in

bcp Devdb..sysusers in ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysusers.out’ -Usa -SASEDEV02 -PSybase123 -c -t ‘|’

bcp Devdb..sysalternates in ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysalternates.out’ -Usa -SASEDEV02 -PSybase123 -c -t ‘|’

bcp Devdb..sysprotects in ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysprotects.out’ -Usa -SASEDEV02 -PSybase123 -c -t ‘|’
7)How to check db refresh completed successfully.

i)MDA table master..monOpenDatabases

Select DBID,BackupInProgress,LastBackupFailed,TransactionLogFull,DBName,BackupStartTime,LastCheckpointTime,LastTranLogDumpTime from master..monOpenDatabases

ii)check backup errorlog

cat errorlog_ASEDEV02_BACKUP | grep -i ‘LOAD is complete’

How to become DBA

Whenever I visit the colleges I got this question from many students How to become a DBA.

 

Here I am writing this blog, at end of this blog you will have at least concrete the idea about DBA and its role. To understand this role in broader way let me put one example, this role is nothing but to protect your Vault (Data is main assets for any organization ) from being stolen and to manage/limiting the access to vault.

 

DBA are the security guard to protect data, manage the users with appropriate the privileges.

The skill set required for this job Profile

1)Knowledge of database types of database specially RDBMS.

2) Knowledge on Different operating system like UNIX, Linux, Windows etc

3)Troubleshooting skills.

Which are the database product in the market which is among mostly used like Oracle, MS- SQL Server, SYABSE ASE, MYSQL etc and many more.

 

Those do not have interest in programming can walk with this field. Any computer science graduate can be DBA. There are lots of offline and online classes in market though which you can have DBA skills set. If you are certified in any of Database Product whether you are fresher or lateral(experienced) this will help to get shortlisted for DBA technical round in various companies.

Most of the IT companies do have DBA job profile you can apply through various job portal sites

DBA’s in market have high demand and they are often get very good paycheck.

 

I hope this information helps.

How to check db refresh completed successfully sybase ASE.LINUX Shell commands cat and grep

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

There are several ways,few of them listed below

1)cat errorlog_Syb_BACKUP | grep -i ‘LOAD is complete’

2)MDA tables:-monOpenDatabases

Select DBID,BackupInProgress,LastBackupFailed,TransactionLogFull,DBName,BackupStartTime,LastCheckpointTime,LastTranLogDumpTime from master..monOpenDatabases

2)dbcc traceon(3604)

go

dbcc dbtable(<mydatabase>|<mydbid>)

go

–look for “dbt_verstimestamp”

 

 

How to become DBA

Whenever I visit the colleges I got this question from many students How to become a DBA.

 

Here I am writing this blog, at end of this blog you will have at least concrete the idea about DBA and its role. To understand this role in broader way let me put one example, this role is nothing but to protect your Vault (Data is main assets for any organization ) from being stolen and to manage/limiting the access to vault.

 

DBA are the security guard to protect data, manage the users with appropriate the privileges.

The skill set required for this job Profile

1)Knowledge of database types of database specially RDBMS.

2) Knowledge on Different operating system like UNIX, Linux, Windows etc

3)Troubleshooting skills.

Which are the database product in the market which is among mostly used like Oracle, MS- SQL Server, SYABSE ASE, MYSQL etc and many more.

 

Those do not have interest in programming can walk with this field. Any computer science graduate can be DBA. There are lots of offline and online classes in market though which you can have DBA skills set. If you are certified in any of Database Product whether you are fresher or lateral(experienced) this will help to get shortlisted for DBA technical round in various companies.

Most of the IT companies do have DBA job profile you can apply through various job portal sites

DBA’s in market have high demand and they are often get very good paycheck.

 

I hope this information helps.