Home / Repstance User Guide / DATABASE CONFIGURATION COMMANDS

DATABASE CONFIGURATION COMMANDS

Repstance keeps a few objects of its own in the databases it works with: in a Source Database the objects that the Capture Process needs (for SQL Server also CDC), in a Target Database the checkpoints of the Apply Processes. The commands of this section create these objects (prepare), check that they are there (validate) and remove them (remove). Both databases have to be prepared before the processes that use them are created and run (see PROCESS CONFIGURATION COMMANDS).

The commands

Command	What it does	Page
prepare	Creates the Repstance objects in a Source Database or a Target Database. With generatesql=1 it creates nothing and returns the SQL script, so that an administrator can run it.	Prepare Source Database, Prepare Target Database
validate	Checks that the objects are there and that the database is ready for a Capture Process or an Apply Process.	Validate Database
remove	Removes the objects again.	Remove Repstance Objects from Database

The parameters depend on the database: SQL Server as Source and Oracle as Source for a Source Database, SQL Server as Target, Oracle as Target, PostgreSQL as Target, MySQL as Target, Snowflake as Target, Redshift as Target and AWS S3 as Target for a Target Database. The login that is given in user needs the privileges that are described in Database Privileges.

Preparing the databases

Create the login that Repstance connects with and give it the privileges (see Database Privileges). Repstance creates no login and grants no privileges.
Prepare the Source Database with prepare database=source (see Prepare Source Database). Only SQL Server and Oracle can be a Source Database.
Prepare the Target Database with prepare database=target (see Prepare Target Database).
Check both databases with validate database=source and validate database=target (see Validate Database).
Create the Capture Process and the Apply Process (see Prepare Capture Process and Prepare Apply Process), check them (see Validate Process) and run them (see Run any Capture or Apply Processes).

If the login of the processes may not create the objects, an administrator runs the script that prepare returns with generatesql=1. The Web UI leads through the same steps in the dialog “Database Settings”, see Setup First Replication.

What the commands create

Database	The objects are in	What prepare creates
SQL Server Source	the schema repi	CDC for the database, the tables and procedures of the Capture Process, a database trigger for DDL statements and, on a server with SQL Server Agent, the jobs of the heartbeat and of the CDC cleanup
Oracle Source	the user REPI	the supplemental logging, the Oracle directories (only with directlogmode=1), the tables REPI.ValidateTables and (with directlogmode=0) REPI.DML_CAPTURE_OBJECTS, and a sequence
SQL Server Target	the schema repi	the tables apply_ckpt, apply_ckpt_history, hb_stat and ValidateTables and four procedures; no CDC, no jobs
Oracle Target	the user REPI	the same tables, a sequence and four procedures
PostgreSQL and Redshift Target	the schema repi	the same tables and four functions (PostgreSQL) or procedures (Redshift)
MySQL Target	the database repi	the same tables and four procedures
Snowflake Target	the schema REPI	the tables APPLY_CKPT, HB_STAT and ValidateTables and a file format for the bulk load
AWS S3 Target	none	nothing

The user REPI is a locked user, it is named C##REPI on Oracle 11g and in the root of a container database. The steps of every command and the privileges they need are in Database Privileges. The replicated tables are never changed by these commands.

Rules of the commands

database (source or target) and dbtype are required. A Source Database is mssql or oracle; a Target Database is oracle, mssql, postgresql, mysql, snowflake, redshift or s3.
The names of the parameters and the keyword values are not case sensitive. A parameter can be given once in a command, and a parameter that does not belong to the database is refused: the answer lists the parameters that can be used.
prepare can be run again: it creates what is missing and replaces the procedures. Run it again after Repstance has been upgraded, a new version can have new objects.
generatesql=1 only changes prepare: remove removes the objects whatever its value.
AWS S3 has no objects: only validate does something for it.
The commands do not know the processes. remove does not look for a process that uses the database, and a process that uses it fails afterwards.

Messages

repcli prints the answer of a command as [Completed]: message, [Valid]: message, [Invalid]: message, [Failed]: message or [In Process]: message. The messages:

Message	Cause
Database [db-src.example.com:1433,testDB] has been prepared.	prepare has finished. For Oracle the message is Database has been prepared, for Snowflake it names the database only.
Capture objects have been removed [db-src.example.com:1433,testDB]	remove has finished: Capture objects for a Source Database, Apply objects for a Target Database.
Database TESTDB is valid	validate has found the objects. The name is written in upper case. Warnings can follow, and for a SQL Server Source Database lines that start with RESET LSN (see Validate Database).
Database is not prepared for capture: [CDC is not enabled: 1] and Database is not prepared for apply: [repi schema does not exist: 1]	The database is not prepared. The text in brackets names the first object that is missing and a number that identifies it (see Database Privileges).
The error of the driver or of the database, for example lookup db-none.example.com on 10.0.0.5:53: no such host	The database cannot be reached, or it refuses the login or a statement.
Missed port parameter and a line that describes the parameter	A required parameter is missing. The answer names one missing parameter at a time.
An invalid parameter [foo] has been supplied. and the list of the valid parameters	A parameter that does not exist for this database.
An invalid value [mysql] has been supplied for parameter [dbtype] with database=source. and Valid values: oracle \| mssql	A value that the parameter does not accept.
Missed dbtype parameter or Missed database parameter and the valid values	The command has no dbtype or no database.
Command [PREPARE] has been sent. Use [validate] command to validate the state with the status In Process	prepare or remove for dbtype=s3. Nothing is done.

REST API

The commands are POST requests to /configure/database. The body is JSON, the header X-Token carries the token of the Repstance server (see Configure repcli for Remote Access and REST API). The element command is prepare, validate or remove and parameters is a list of [name, value] pairs, the same names as in repcli. The answer has the fields Status and Message; the answer of validate for a SQL Server Source Database also has parameters. The bodies and the answers are on the pages of the commands. The HTTP codes:

HTTP code	Meaning
200	The request is valid and the command has been carried out. The body has the status Completed or Failed (prepare and remove) or Valid or Invalid (validate), and the message. A command that cannot do its work, for example because the database cannot be reached, is also answered with 200 and the status Failed or Invalid: always read the status.
401	The token is invalid.
422	The request cannot be carried out: the JSON, the command or a parameter is wrong. The body has the status Failed and the message, for example Missed port parameter.

Web UI

The dialog “Database Settings” of a Capture Process or an Apply Process does the same. “Test connection” is validate, “Prepare for Replication” is prepare (a confirmation window comes first), “Show Prepare SQL” is prepare with generatesql=1 and “Cleanup Repstance Objects” is remove (without a confirmation). “Copy Settings” takes the connection data of a process that exists already. The steps are shown in Setup First Replication, and the pages of the commands show the dialog for each of them.

Database Sync from Backup

Prepare Source Database