Download Database Synchronizer Administration Manual

J-CORES
Database Synchronizer
Administration Manual
2013-08
NTT DATA CCS CORPORATION
Database Synchronizer Administration Manual
Revision Information
version
date
writer
revised point
1.3
2008-03-31
IIZUKA Yukiko
first revision
1.5
2009-03-19
Adds
about
Tatsuo TANABE
misc_material_halves
and
db_overview_counts
for
jp.co.ccs.science.od21.compositelog.LaboratorySampleIndexColumn to Appendix A.
1.6
2011-04-28
Tatsuo TANABE
Adds about known bug.
1.8
2012-03-30
Tatsuo TANABE
Adds about user-defined parameter. Section piece is also added.
1.9
2013-08-30
Tatsuo TANABE
Update execution environment to Java 7.
2
Database Synchronizer Administration Manual
―Contents―
1. Introduction ..................................................................................................................... 5
2. Basic behavior ................................................................................................................. 6
2.1. Rules to update data ................................................................................................. 6
2.2. Operation mode, dry-run/go-live .............................................................................. 9
2.3. Behaviors on errors................................................................................................... 9
2.4. Access restriction .................................................................................................... 10
2.5. Treatment of Oracle user integration .................................................................... 10
3. J-CORES operational constraints ................................................................................ 11
4. Operational Example .................................................................................................... 12
5. How to use ..................................................................................................................... 13
5.1. Requirement ........................................................................................................... 13
5.2. Installing Database Synchronizer ......................................................................... 13
5.3. Usage of the merge command and the split command ......................................... 14
5.3.1. Synopses ........................................................................................................... 14
5.3.2. Options.............................................................................................................. 15
5.3.3. Return codes ..................................................................................................... 19
5.3.4. Update statistical information on the database ............................................. 20
5.3.5. Execution samples............................................................................................ 20
5.4. Checking messages of the merge/split command .................................................. 21
5.5. Usages of the related commands............................................................................ 24
5.5.1. The cleanup command ..................................................................................... 24
5.5.2. A password generator ....................................................................................... 25
6. Properties ...................................................................................................................... 27
7. Known Bug .................................................................................................................... 28
―Diagrams―
Tables
Table 2-1 Data categories............................................................................................. 6
Table 2-2 Behaviors on errors ...................................................................................... 9
Table 5-1 Return codes of the merge / split command .............................................. 19
Table 5-2 Labels of data categories ........................................................................... 22
Table 5-3 Return codes of the cleanup command...................................................... 25
3
Database Synchronizer Administration Manual
―Appendixes―
Appendix A Correspondences of database tables to data categories of Database
Synchronizer
Appendix B Updating rules of selectees, their statuses and data categories
Appendix C Database Synchronizer operational example
Appendix D The list of errors for the merge / split command
Appendix E Updating Rules for User-Defined Parameters
4
Database Synchronizer Administration Manual
1. Introduction
This document describes settings and operations of J-CORES Database Synchronizer.
Database Synchronizer has been designed for merging/splitting data stored in J-CORES
databases. Database Synchronizer is based on the following postulate:
a. “Merging” is that a part (or all) of data in a slave database are integrated into the
master database of J-CORES.
b. “Splitting” is that a part (or all) of data in the master database are duplicated to a
slave database.
c. The master database is the only database, into which all of J-CORES data are
integrated.
d. Some slave databases are deployed onshore, and some are offshore (or on vessel).
e. “Merging” and “splitting” are executed only between the master database and a
slave database, and are not executed between slave databases.
User interfaces of Database Synchronizer are as follows:
-
jcores-dbsync-merge2master
The command to merge
-
jcores-dbsync-split2slave
The command to split
-
jcores-dbsync-cleanup
The command to cleanup user lock statuses
and sessions on databases. This command
may be used when merging (or splitting)
processes failed.
-
jcores-dbsync-pgen.sh
A command to generate a password for an
Oracle user
5
Database Synchronizer Administration Manual
2. Basic behavior
2.1. Rules to update data
The rules to update data are depending on data categories, as follows:
Table 2-1 Data categories
split
A2. expedition, site, hole, core data and
curatorial material data
A. expedition data
A1. expedition data, edited only on single DB
updating rule
when conflicting
updating rule
when conflicting
A1a. expedition
data, expedition
indistinguishable
union
act on the parameter,
"--masterdb-overwrit
es-conflict" or
"--slavedb-overwrites
-conflict" or
"--error-on-conflict"
A1b. expedition
data, expedition
distinguishable(*
1)
overwrite(*2)
overwrite
overwrite(*2)
overwrite
overwrite(*2) ,
adjusting the sort
key order (local
selectees follow basic
selectees, and the
orders among local
selectees should not
be changed.)
overwrite(*2),
adjusting the sort
key order (local
selectees follow basic
selectees, and the
orders among local
selectees should not
be changed.)
overwrite(*2),
adjusting the sort
key order (local
selectees follow basic
selectees, and the
orders among local
selectees should not
be changed.)
overwrite(*2),
adjusting the sort
key order (local
selectees follow basic
selectees, and the
orders among local
selectees should not
be changed.)
union
act on the parameter,
"--masterdb-overwrit
es-expsiteholecore-ofexpeditions" or
"--slavedb-overwrites
-expsiteholecore-of-e
xpeditions" or
"--error-on-expsitehol
ecore-conflict-of-expe
ditions"
union
act on the parameter,
"--masterdb-overwrit
es-expsiteholecore-ofexpeditions" or
"--slavedb-overwrites
-expsiteholecore-of-e
xpeditions" or
"--error-on-expsitehol
ecore-conflict-of-expe
ditions"
union
act on the parameter,
"--masterdb-repositor
ies" or "--slavedb
-repositories" (*4,*5)
union
act on the parameter,
"--masterdb-repositor
ies" or "--slavedb
-repositories" (*4,*5)
overwrite
overwrite
-
error
union
act on the parameter,
"--masterdb-overwrit
es-conflict" or
"--slavedb-overwrites
-conflict" or
"--error-on-conflict"
union
act on the parameter,
"--masterdb-overwrit
es-conflict" or
"--slavedb-overwrites
-conflict" or
"--error-on-conflict"
overwrite only
records having the
master database
location(*6)
overwrite only
records having the
master database
location(*6)
overwrite only
records having the
slave database
location(*6)
overwrite only
records having the
slave database
location(*6)
A1b1.
expedition
data, VCD
selectee
excluding
basic and
defunct on
master DB(*3)
A2a. expedition,
site, hole and
core data
A2b. curatorial
material data
B1. master data, editable
only on master DB(*3)
B2. master data, editable
on slave DB(*1)
B. master data
merge
B2a. master
data,
sequence
current value
6
union
act on the parameter,
"--masterdb-overwrit
es-conflict" or
"--slavedb-overwrites
-conflict" or
"--error-on-conflict"
Database Synchronizer Administration Manual
split
updating rule
-
C. temporary data
merge
when conflicting
-
updating rule
-
when conflicting
-
*1 : Roles’ categories differ by whether they belong to an expedition as below:
The roles belonging to an expedition
A1b
The roles not belonging to an expedition
B2
The roles not belonging to an expedition are:
-
developer
-
user administrator
-
VCD selectee administrator
The other roles belong to an expedition.
*2 : When not specifying expeditions to process, data in this category will not be
merged (or not to be split).
*3 : Selectees' categories differ by their statuses. Refer to Appendix B for details.
*4 : If each conflicting data has different repository, a conflicting error occurs.
The overwriting rules for conflicting section data are depended on repository values of
section halves belonging to the section. Details are as below.
(1) When repository values of archive halves and working halves are same, the section
data stored in the database which has a control of the repository overwrites the
other. (Example 1)
(2) A case that repository values of archive halves and working halves are different.
(a) A case that combinations of SECTION_HALVES.REPOSITORY for the section halves
are same between the master database and the slave database. (Example 2)
-
About records of the following tables; a case of conflicting, or a case that a
record stored in only one database.
SECTION
SECTION_COMMENT
SUBSECTION
SECT_EXPAND_CONTRACT
(i)
When one database has controls of both repositories of archive halves
and working halves, the record stored in the database which has
controls overwrites the other. (Example 3)
(ii) When different databases have each control of repositories of archive
halves and working halves, a conflicting error occurs. (Example 4)
-
About records of the following tables; a case of conflicting, or a case that a
record stored in only one database.
7
Database Synchronizer Administration Manual
SECT_HALF_COMMENT
SECT_HALF_EXPAND_CONTRACT
The record stored in the database which has a control of its section half
repository overwrites the other.
(b) When combinations of SECTION_HALVES.REPOSITORY for the section halves are
different between the master database and the slave database, a conflicting
error occurs. (Example 5)
Example 1;
Master database
Slave database
Archive half repository
CKY
CKY
Working half repository
CKY
CKY
Master database
Slave database
Archive half repository
KCC
KCC
Working half repository
CKY
CKY
Example 2;
Example 3; on the example 2, the master database has both controls of KCC and CKY.
Example 4; on example 2, the master database has a control of KCC, and the slave
database has a control of CKY.
Example 5;
Master database
Slave database
Archive half repository
KCC
CKY
Working half repository
CKY
CKY
*5 : Kinds of sample (core sample or miscellaneous material sample) is also obeyed this
option, even if their attributes are managed in different tables (CORE_SAMPLE or
LOGGING_SAMPLE).
*6 : The rules for the tables, SEQ_MANAGE and SEQ_NUMBER are as below:
-
Records of these tables shall have one to one relations each other.
-
Records
of
these
tables
are
categorized
as
B1,
excluding
SEQ_NUMBER.SEQ_CURRVAL categorized as C.
-
The value of SEQ_CURRVAL will be the greater value comparing records with
same SEQ_ID. The same SEQ_ID means the records' LOCATION_CODE,
TABLE_NAME, and COLUMN_NAME in SEQ_MANAGE are same. To make this
decision, the value of LOCATION_CODE is not considered.
For the lists of database tables, columns, or records belonging to each category, refer to
Appendix A.
8
Database Synchronizer Administration Manual
Some data consist of one parent record and some children records. In this case, the
updating rule of the children records accords with their parent record updating. For
example, when A record and B record are conflicting and A is overwritten by B, children
records of A would be overwritten by children records of B. By way of exception,
updating rules of roles differ by their categories as below:
Roles categorized A1b:
overwrite each scientist
Roles categorized B2:
union each scientist record
2.2. Operation mode, dry-run/go-live
Database Synchronizer employs two operation modes, called “dry-run”, which does not
update J-CORES database, and “go-live”, which updates J-CORES database. User can
check the results of merging or splitting by “dry-run” mode without updating the
database.
To check errors and results, executing in the dry-run mode before synchronization in the
go-live mode is strongly recommended.
2.3. Behaviors on errors
There are three types of errors: parameter error, runtime error, logical error.
The behavior on an error is depended on the current operational mode as blow:
Table 2-2 Behaviors on errors
dry-run mode
Parameter error
Logical error
go-live mode
Stop processing. No updating has done.
Continue processing to detect of
Stop
processing.
all logical errors.
updating
is
And
all
canceled,
i.e.
database is not updated.
Stop processing, excepting some errors. And when stopping process
Runtime error
in the go-live mode, all updating is cancelled. Refer to Appendix D
for behavior on each error.
For the list of errors and their details, refer to Appendix D
9
Database Synchronizer Administration Manual
2.4. Access restriction
Database Synchronizer connects to the databases as the user “jcores.” To avoid parallel
execution of synchronization, when another session of jcores existed on the databases,
Database Synchronizer would stop processing.
And no editing shall be done to neither the databases during merging (or splitting.) For
safety, Database Synchronizer locks the other users, and closes existent sessions
forcibly.
At the end of merge/split processes, Database Synchronizer unlocks users. But in some
cases of unexpected runtime errors, users might remain in locked statuses. And also
jcores session used by Database Synchronizer might remain. Although Database
Synchronizer would try to reconnect to unlock users on the database, if a database
session had closed, locked status and the database session might still remain. In this
case, by executing the cleanup command, the users would be unlocked and the remained
jcores session would be closed.
When an error message prompting to execute the cleanup command is displayed,
execute the command.
2.5. Treatment of Oracle user integration
Although Oracle user may be needed to shift to another database with J-CORES user
data shifts, there are two problems.
One is that Database Synchronizer can’t access passwords of Oracle users. To resolve
this problem, Database Synchronizer employs a function to get password of each user
from specified external program. The external program should be specified as an option
value. A possible external program is jcores-dbsync-pgen.sh.
Another one is that Database Synchronizer can’t recognize Oracle user name suffix for
J-CORES users (e.g. A J-CORES user named “xxx” has the name “xxx#2Z” as an Oracle
user.) To resolve this problem, Database Synchronizer requires specifying the Oracle
user name suffix as an option value. And at the end of processing, Database
Synchronizer deletes Oracle users whose names end with the specified suffix and who
do not exist in the SCIENTIST table.
10
Database Synchronizer Administration Manual
3. J-CORES operational constraints
Database Synchronizer performs on the premised J-CORES operational constraints,
which are described in the following documents.
-
Operational Manual, 4.9. Synchronizing databases
-
The user manual of VCD Selectee Administrator, 2. Operational Restrictions
-
The user manual of Operation, 2. Operational Restrictions
-
The user manual of Curation, 2. Operational Restrictions
-
The user manual of User, 2. Operational Restrictions
11
Database Synchronizer Administration Manual
4. Operational Example
Some operational examples are described in Appendix C. Those examples are based on a
premised scenario.
Merging can be executed after finishing an expedition and during the expedition
repeatedly. And splitting can be executed before starting an expedition and during the
expedition repeatedly.
12
Database Synchronizer Administration Manual
5. How to use
5.1. Requirement
Environmental requirements for Database Synchronizer are as follows:
-
Database Synchronizer runs on a UNIX machine with JRE 7 and Bash.
-
Database Synchronizer requires direct network connections to the master
database and to the slave database to merge or to split. And the speeds of the
network connections shall be as fast as a connection inside LAN.
-
The slave database shall be a J-CORES database instance and have all object
needed (table spaces, roles, tables, indexes, triggers, synonyms, “jcores” user,
and master data.)
5.2. Installing Database Synchronizer
Installing steps are as follows:
1.
Make one directory to install. And deploy program files and a library file in the
directory as below:
install_directory/
jcores-dbsync-merge2master
jcores-dbsync-split2slave
jcores-dbsync-cleanup
classes/
jcores-dbsync.jar
ojdbc6.jar (*)
properties/
dbsync.properties
* The library, ojdbc6.jar can be downloaded from the site of Oracle.
(http://www.oracle.com/technology/index.html)
2.
Make sure that command shell files have execute permissions.
3.
Deploy the file, jcores-dbsync-pgen.sh in any directory you want.
4.
Each command can be executed from any directories. If necessary, add the directory
where command files are deployed in to your path.
13
Database Synchronizer Administration Manual
5.3. Usage of the merge command and the split command
Variables are underlined.
5.3.1. Synopses
(1) Synopsis of the merge command
To run merge processes:
jcores-dbsync-merge2master [--expeditions-to-merge expedition[,..]]
[--masterdb-repositories repository[,..] ]--slavedb
-repositories-all-the-others | --masterdb-repositories-all-the-others[ -slavedb-repositories repository[,..]]
[--masterdb-overwrites-conflict | --slavedb-overwrites-conflict |
--error-on-conflict]
[[--masterdb-overwrites-expsiteholecore-of-expeditions expedition[,..]]
[--slavedb-overwrites-expsiteholecore-of-expeditions expedition[,..]]
[--error-on-expsiteholecore-conflict-of-expeditions-all-the-others]] |
[[--masterdb-overwrites-expsiteholecore-of-expeditions expedition[,..]]
[--slavedb-overwrites-expsiteholecore-of-expeditions-all-the-others]
[--error-on-expsiteholecore-conflict-of-expeditions expedition[,..]]] |
[[--masterdb-overwrites-expsiteholecore-of-expeditions-all-the-others]
[--slavedb-overwrites-expsiteholecore-of-expeditions expedition[,..]]
[--error-on-expsiteholecore-conflict-of-expeditions expedition[,..]]]
--masterdb-connection
password-for-user-jcores@hostname-or-ip-address:port:instance
--slavedb-connection
password-for-user-jcores@hostname-or-ip-address:port:instance
[--go-live | --dry-run]
--masterdb-loginid-suffix suffix
--password-generator passwd-generator-command-with-option
[--java java-command]
[--option-file file-name]
To see the synopsis:
jcores-dbsync-merge2master --help
14
Database Synchronizer Administration Manual
(2) Synopsis of the split command
To run split processes:
jcores-dbsync-split2slave
[--expeditions-to-split expedition[,..]]
[--masterdb-repositories repository[,..] ]--slavedb
-repositories-all-the-others | --masterdb-repositories-all-the-others[ -slavedb-repositories repository[,..]]
[--masterdb-overwrites-conflict | --slavedb-overwrites-conflict |
--error-on-conflict]
[[--masterdb-overwrites-expsiteholecore-of-expeditions expedition[,..]]
[--slavedb-overwrites-expsiteholecore-of-expeditions expedition[,..]]
[--error-on-expsiteholecore-conflict-of-expeditions-all-the-others]] |
[[--masterdb-overwrites-expsiteholecore-of-expeditions expedition[,..]]
[--slavedb-overwrites-expsiteholecore-of-expeditions-all-the-others]
[--error-on-expsiteholecore-conflict-of-expeditions expedition[,..]]] |
[[--masterdb-overwrites-expsiteholecore-of-expeditions-all-the-others]
[--slavedb-overwrites-expsiteholecore-of-expeditions expedition[,..]]
[--error-on-expsiteholecore-conflict-of-expeditions expedition[,..]]]
--masterdb-connection
password-for-user-jcores@hostname-or-ip-address:port:instance
--slavedb-connection
password-for-user-jcores@hostname-or-ip-address:port:instance
[--go-live | --dry-run]
-- slavedb-loginid-suffix suffix
--password-generator passwd-generator-command-with-option
[--java java-command]
[--option-file file-name]
To see the synopsis:
jcores-dbsync-split2slave --help
5.3.2. Options
(1) Note
The notes when specifying an option are as follows.
a. The options, --java, --help, and --option-file can be specified only on
command line. The other options can be specified both on command line and in an
15
Database Synchronizer Administration Manual
option file. There is no difference arising from a specification way.
b. The options can be specified in random order.
c. Specifying wrong options makes a parameter error. (e.g. same options specified
more than once, undefined option, arguments for an option.)
d. When specifying more than one variable, they must be listed separating by a
comma without a space.
e. The option suffix, “-all-the-others”, means “all of the values excluding the
values which are specified as arguments for the other options of the same
category.”
For
example,
“--masterdb-repositories-all-the-others
--slavedb-repositories CKY” means the repositories which are controlled by the
master database are all of the repositories stored in databases excluding CKY.
f.
When specifying options and their arguments on command line, you have to quote
a string to pass to the command correctly via your login-shell, if necessary. For
example, Bash considers ‘#’ is the beginning character of a comment. Therefore
when you would like to specify a string with ‘#’, you need to quote the word. Refer
to your login-shell manual for details of its quote rules. Some examples for Bash
are below.
Example 1: Specifying “#z2” as an argument for “--masterdb-loginid-suffix”
option
--masterdb-loginid-suffix "#z2"
Example 2: Specifying the password generator command and its option as an
argument for “--password-generator” option
--password-generator "./jcores-dbsync-pgen.sh --suffix \"abc\""
g. When specifying options and their arguments in an option file, you should accord
to the rules below:
-
The character ‘#’ is not considered as a comment, i.e. a quotation for the
character, ‘#’ is not needed and has no influence.
-
Specifying a string with blanks, you have to quote it.
-
Specifying a quoted string within quotation marks, you have to use another
quotation mark or to escape them using ‘\.’ The character ‘\’ is always
considered as the escape character.
Example 1: Specifying “#z2” as an argument for “--masterdb-loginid-suffix”
option
--masterdb-loginid-suffix #z2
Example 2: Specifying the password generator command and its option as an
argument for “--password-generator” option
16
Database Synchronizer Administration Manual
--password-generator "./jcores-dbsync-pgen.sh --suffix \"abc\""
(2) Option List for the merge command
[--expeditions-to-merge expedition[,..]]
Specify expeditions to process. In the A1b data, only the records belonging to specified
expeditions will be processed. By default, Database Synchronizer will merge no
expedition data categorized A1b.
[--masterdb-repositories repository[,..] ]--slavedb
-repositories-all-the-others | --masterdb-repositories-all-the-others[ -slavedb-repositories repository[,..]]
Specify repositories of which master database and/or slave database have controls. In
the A2b data, the record stored in the database which has control of its repository will
overwrite the other when conflicting.
[--masterdb-overwrites-conflict | --slavedb-overwrites-conflict |
--error-on-conflict]
Specify the behavior when records in the A1a and B2 excluding B2a categories conflict.
The default is the option, --error-on-conflict, which means all conflicts cause errors.
[[--masterdb-overwrites-expsiteholecore-of-expeditions expedition[,..]]
[--slavedb-overwrites-expsiteholecore-of-expeditions expedition[,..]]
[--error-on-expsiteholecore-conflict-of-expeditions-all-the-others]] |
[[--masterdb-overwrites-expsiteholecore-of-expeditions expedition[,..]]
[--slavedb-overwrites-expsiteholecore-of-expeditions-all-the-others]
[--error-on-expsiteholecore-conflict-of-expeditions expedition[,..]]] |
[[--masterdb-overwrites-expsiteholecore-of-expeditions-all-the-others]
[--slavedb-overwrites-expsiteholecore-of-expeditions expedition[,..]]
[--error-on-expsiteholecore-conflict-of-expeditions expedition[,..]]]
Specify which database has controls of what expeditions, for the A2a data. In the A2a
data, the record stored in the database which has control of its expedition will overwrite
the other when conflicting. By default, conflicts of the A2a data belonging to unspecified
expeditions as arguments for the option of the master database and the slave database,
(if no option, belonging to all expeditions) will cause logical errors, as same as specifying
the
option,
--error-on-expsiteholecore-conflict-of-expeditions-all-the-others.
17
Database Synchronizer Administration Manual
--masterdb-connection
password-for-user-jcores@hostname-or-ip-address:port:instance
Specify the information to connect the master database.
--slavedb-connection
password-for-user-jcores@hostname-or-ip-address:port:instance
Specify the information to connect the slave database to be processed.
[--go-live | --dry-run]
Specify the operational mode. For details of operational modes, refer to the chapter 2.1.
The default is the dry-run mode.
--masterdb-loginid-suffix suffix
Specify the Oracle user id suffix of the master database to use when creating users.
Refer to the chapter 2.5 for the details of the suffix.
--password-generator passwd-generator-command-with-option
Specify the password generator with its option. When creating an Oracle user, Database
Synchronizer will call specified command with the specified option and his/her Oracle
user id. The user id is added as the last argument for the command. You specify the
command in an absolute path or in a relative path, or configure the environmental
variable, PATH, as you can call the command from any directories.
For the usage of the password generator of version 1.3, refer to the chapter 5.5.2(2).
[--java java-command]
Specify the java command to use to execute Database Synchronizer. Its version must be
1.7. By default, the java command found on user path will be used.
This option can be specified only as a command line option. Do not include in an option
file.
[--option-file file-name]
Specify a file containing options. By default, Database Synchronizer seeks no option file.
This option can be specified only as a command line option. Do not include in an option
file.
[--help]
Display the usage of the command. When specifying this option, all of the rest options
are ignored.
This option can be specified only as a command line option. Do not include in an option
file.
18
Database Synchronizer Administration Manual
(3) Option List for the split command
The options for the split command are almost same as the merge command, excluding
the options, --expeditions-to-merge, --masterdb-loginid-suffix. Instead of them,
the split command supports options below:
[--expeditions-to-split expedition[,..]]
Specify expeditions to process. In the A1b data, only the records belonging to specified
expeditions will be processed. By default, Database Synchronizer will split no
expedition data categorized A1b.
--slavedb-loginid-suffix suffix
Specify the Oracle user id suffix of the slave database to use when creating users.
5.3.3. Return codes
The merge command and the split command return a code after processing. The
meanings of the codes are below:
Table 5-1 Return codes of the merge / split command
Status
Code
Success
Failed
to
start
java
0
Succeeded. No error has occurred.
1
Can't find jar file for command execution, jar file
command
Some
Situation
was destroyed, or illegal CLASSPATH, etc.
errors
have
11
occurred.
Some runtime errors or logical errors have occurred
while processing.
Can't find java command
20
Specified java command is not found, or installed
directory is not included in PATH, etc.
Command format error
21
Required option was not specified, illegal command
format, or unexpected option was specified, etc.
Can't
lock
the
other
30
users
Another
Can't lock the other users at the master database
or/and the slave database.
Synchronizer
31
might be running
The user for this system, jcores has been logging in
already. To avoid collisions, the synchronizer
doesn't run when another jcores session exists.
Logical error
40
A logical error has occurred.
Runtime error
99
Serious runtime error has occurred.
19
Database Synchronizer Administration Manual
5.3.4. Update statistical information on the database
Database synchronizer executes many insert/update/delete SQL to the merged/spited
database. This may decrease database performance, even if the database synchronizer
has run as dry-run mode. This decrease performance may be invoked by mismatching
between the preliminarily acquired statistical information of the database and current
state of the database. To resolve to this issue and to improve performance again, the
statistical information must update to current state. Updating statistical information
takes while minutes. Here is operation example with SQLplus.
SQL> conn / as sysdba
SQL> BEGIN
2
DBMS_STATS.GATHER_DATABASE_STATS (options=> 'GATHER AUTO');
3
END;
4
/
PL/SQL procedure successfully completed.
5.3.5. Execution samples
These are examples of executing the merge command. (1) is an example of specifying
options both on a command line and in an option file. (2) is an example of specifying
options only on a command line. Executions these examples save the standard output of
the command as a file, merge_315_316_out.txt, and the standard error output as a file,
merge_315_316_err.txt. Both examples are for Bash.
(1) Using an option file
[Executing the command on a command line]
./jcores-dbsync-merge2master --option-file merge_opt.txt \
--java /usr/java/jdk1.7.0_25/bin/java \
--expeditions-to-merge 315,316 \
1>merge_315_316_out.txt 2> merge_315_316_err.txt
[Option file]
--masterdb-repositories-all-the-others
--slavedb-repositories CKY
--slavedb-overwrites-conflict
--masterdb-overwrites-expsiteholecore-of-expeditions-all-the-others
20
Database Synchronizer Administration Manual
--slavedb-overwrites-expsiteholecore-of-expeditions 315,316
--masterdb-connection [email protected]:1521:masterdb
--slavedb-connection [email protected]:1521:slavedb
--masterdb-loginid-suffix #z2
--password-generator "./jcores-dbsync-pgen.sh --suffix aa"
(2) Specifying all options as command line options
[Executing the command on a command line]
./jcores-dbsync-merge2master --java /usr/java/jdk1.7.0_25/bin/java \
--expeditions-to-merge 315,316 \
--masterdb-repositories-all-the-others \
--slavedb-repositories CKY \
--slavedb-overwrites-conflict \
--masterdb-overwrites-expsiteholecore-of-expeditions-all-the-others \
--slavedb-overwrites-expsiteholecore-of-expeditions 315,316 \
--masterdb-connection [email protected]:1521:masterdb \
--slavedb-connection [email protected]:1521:slavedb \
--masterdb-loginid-suffix “#z2” \
--password-generator "./jcores-dbsync-pgen.sh --suffix aa" \
1>merge_315_316_out.txt 2> merge_315_316_err.txt
5.4. Checking messages of the merge/split command
(1) Confirming the option evaluation
Database Synchronizer outputs evaluated options to the standard output. An example
is below:
--expeditions-to-merge 315,316
--masterdb-repositories-all-the-others BCR,ECR,KCC,SHIP,OUT,GCR,WCR
--slavedb-repositories CKY
--slavedb-overwrites-conflict
--masterdb-overwrites-expsiteholecore-of-expeditions-all-the-others
116,117,115,195,194,196,191,190,193,118,119,87,125,126,127,128,121,122,12
3,124,22,23,24,25,26,27,28,29,30,6,31,120,134,135,132,59,133,58,57,56,19,
314,179,18,176,21,131,60,90,187,189,180,182,183,184,185,186,902,901
--slavedb-overwrites-expsiteholecore-of-expeditions 315,316
21
Database Synchronizer Administration Manual
--error-on-expsiteholecore-conflict-of-expeditions (none)
--masterdb-connection dbserver.sample.domain:1521:masterdb
--slavedb-connection dbserver.sample.domain:1521:slavedb
--dry-run
--masterdb-loginid-suffix #z2
--password-generator ./jcores-dbsync-pgen.sh --suffix aa
--java /usr/java/jdk1.7.0_25/bin/java
(2) Checking how data will be updated
Database Synchronizer outputs messages of data differences and inserting, updating
and deleting data to the standard output. But no messages for same data stored in each
database are output. The formats of these messages are below. Variables are
underlined.
[Differences]
DIFFER;table_name,data_cat_label:masterdb;record_data_or_nonexistent
DIFFER;table_name,data_cat_label:slavedb;record_data_or_nonexistent
[Updating, inserting, deleting]
UPDATE;table_name,data_cat_label:master_or_slave_db;record_data
INSERT;table_name,data_cat_label:master_or_slave_db;record_data
DELETE;table_name,data_cat_label:master_or_slave_db;record_data
[The definitions of variables]
table_name; a table name in processing
data_cat_label; a label of the processing data category, listed below.
Table 5-2 Labels of data categories
category
label
A1a
expedition-data-exp-indistinguishable
A1b
expedition-data
A1b1
vcd-selectee-except-basic-and-defunct-in-masterdb
A2a
exp-site-hole-core
A2b
curatorial-material-data
B1
master-data
B2
master-data-editable
B2a
master-data-sequence-current-value
22
Database Synchronizer Administration Manual
category
C
label
stray-data
master_or_slave_db; the database which have updating data originally. For updating
and inserting, the master database when splitting, and the slave database when
merging. For deleting, the slave database when splitting, and the master database
when merging.
record_data_or_nonexistent; record_data or the string, “nonexistent” which means
no record exist. The “nonexistent” would appear only when the processing table
is a child table.
record_data; column values to update with. Column values which are not updated are
not displayed. A null value would be output as the string “null.” A line code in a
column value would begin a new line with a tab code on its head. (Each “\n” and
“\r” leads one new line, excepting a pair of “\r\n”, which leads one new line.)
Database Synchronizer also outputs messages of creating/dropping Oracle users, and
granting/revoking roles. The formats of these messages are below. Variables are
underlined:
[Creating, dropping a user]
date CREATE-ORACLE-USER;data_cat_label:master_or_slave_db;user…result
date DROP-ORACLE-USER;data_cat_label:master_or_slave_db;user…result
[Granting, revoking a role]
date GRANT-ORACLE-ROLE;data_cat_label:master_or_slave_db;user,role…result
date REVOKE-ORACLE-ROLE;data_cat_label:master_or_slave_db;user,role…result
[The definitions of variables]
date; processed date. The format is yyyy-mm-ddThh:mm:ss.sssZ.
data_cat_label; a label of data category. For creating and deleting user, it is the string
“master-data-editable.” For granting and revoking a role, when the role belongs
to an expedition, the string “expedition-data,” when the role does not belong to
an expedition, the string “master-data-editable.”
master_or_slave_db; the database to be updated, the slave database when splitting,
and the master database when merging.
user; an Oracle user id to be processed, with a suffix like “#z2.”
role; an Oracle role to grant or to revoke.
result; a result of processing. In the go-live mode, it is “Succeeded” or “Failed.” In the
23
Database Synchronizer Administration Manual
dry-run mode, “Skipped,” since SQLs to process these tasks are not published.
(3) Recognizing errors
Database Synchronizer outputs messages to the standard error output when an error
occurs. To know what errors occurred and why they occurred, see the error messages.
For the list of errors and their messages, refer to Appendix D. And to solve an error,
editing database may be needed following Appendix D.
5.5. Usages of the related commands
5.5.1. The cleanup command
(1) Basic behavior
The cleanup command processes the following tasks for every specified database as
arguments.
-
Unlock all users excluding the user, jcores.
-
Close existent sessions of the user, jcores.
(2) Synopsis
jcores-dbsync-cleanup [--forcible | --interactive] \
[--java java-command] \
password-for-user-jcores@hostname-or-ip-address:port:instance [...]
(3) Options and arguments
The options can be specified in random order.
[--forcible | --interactive]
The --forcible option closes the sessions of jcores forcibly. The --interactive option
gives a prompt before closing the sessions. The default is -- interactive.
[--java java-command]
Specify the java command to use to execute. Its version must be 1.7. By default, the java
command found on user path will be used.
password-for-user-jcores@hostname-or-ip-address:port:instance [ ...]
Specify the information to connect the databases to be cleaned up. You can specify as
many databases as you want separating by a blank.
24
Database Synchronizer Administration Manual
(4) Return codes and message outputs
The cleanup command returns a code after processing. The meanings of the codes are
below:
Table 5-3 Return codes of the cleanup command
Status
Success
Code
Situation
0
Succeeded cleaning up for all of specified databases.
No error has occurred.
Failed
to
start
java
1
command
Can't find jar file for command execution, jar file
was destroyed, or illegal CLASSPATH, etc.
Failed to clean up
11
Cleaning up for some (or all) databases failed.
Can't find java command
20
Specified java command is not found, or installed
directory is not included in PATH, etc.
Command format error
21
Required parameter was not specified, illegal
command
format,
or
unexpected
option
was
specified, etc.
Runtime error
99
Serious runtime error has occurred.
The cleanup command outputs processing messages to the standard output, and error
messages to the standard error output. To know detailed results of processing, or to
know causes of errors, refer to these messages. For the list of errors and their messages,
refer to Appendix D.
(5) Execution example
jcores-dbsync-cleanup --forcible --java /bin/usr/java \
[email protected]:1521:honban \
[email protected]:1521:ship
5.5.2. A password generator
(1) Basic behavior of a password generator
A password generator is a command which outputs a string to the standard output as a
password of specified user. Database Synchronizer calls this command at beginning to
confirm execution, and calls when creating a user.
A password generator is exchangeable. A generator to be used shall perform as below:
-
A created password shall consist of only alphanumeric characters, “_”, “$”, and
25
Database Synchronizer Administration Manual
“#.”
-
The last argument for the command is an Oracle user id, of which a password is
created.
-
If errors occur, no message is output to the standard output.
-
If errors occur, error messages indicating causes are output to the standard
error output.
-
The command exits with status 0 if a password is created successfully, non-zero
if errors occur.
(2) The password generator of version 1.3
A password generator, jcores-dbsync-pgen.sh is bundled in J-CORES version 1.3. This
generator satisfies the behaviors written in 5.3.1(1).
The password generator outputs a string which connected a specified user name and a
specified suffix, e.g. when a suffix is “_suffix”, a password for a user “uname” is
“uname_suffix.”
[Synopsis]
jcores-dbsync-pgen.sh --suffix passwd-suffix userid
[Option]
The options shall be specified in the fixed order.
--suffix passwd-suffix; Specify a string to be used as a password suffix. The
argument, passwd_suffix shall consist of only alphanumeric characters, “_”, “$”,
and “#.”
userid; Specify a Oracle user id, whose password will be created.
[Errors]
The password generator outputs error messages to the standard error output. For the
list of errors and their messages, refer to Appendix D.
[Execution example]
This is an example to execute from a command line, specifying all arguments.
jcores-dbsync-pgen.sh --suffix _tmp “taro#z2”
This is an example to specify as an argument of the “--password-generator” option.
jcores-dbsync-pgen.sh --suffix _tmp
26
Database Synchronizer Administration Manual
6. Properties
Database Synchronizer employs the property mechanism of Java, same as J-CORES
client applications. You can change some values which influence on behaviors of
Database
Synchronizer
by
editing
its
property
file.
The
property
file
is
“properties/dbsync.properties” in the installed directory of Database Synchronizer.
The editable values are below:
default_password; default password to use when creating an Oracle user. This
password would be used when password generating failed.
retry_time_second; seconds to wait retrying to connect. When the database session
had closed unexpectedly, Database Synchronizer would try to reconnect to unlock
users on the database.
retry_time_count; the maximum counts to retry to connect to the session closed
database.
27
Database Synchronizer Administration Manual
7. Known Bug
Known but unfixed bug is shown below.
bug
Conflicting
how to avoid the bug
records
od21_mscl_calibration
table
on Following steps resolve this bug.
will 1.
Check standard error output and find
become cause of merging / splitting
conflicting records.
failure
Update datetime on overwritten record
even
if 2.
`--masterdb-overwrites-conflict' or
(record
`--slavedb-overwrites-conflict'
`--masterdb-overwrites-conflict').
is
specified.
3.
This bug will be occurred when a 4.
on
slavedb
if
Execute merge / split again.
Update od21_mscl_run records on merged /
MSCL calibration file in both databases
split database which refer to record updated
has same datetime.
on step 2 to refer to another record found on
step 1.
5.
Delete od21_mscl_calibration record on
merged / split database which was updated
on step 2.
6.
Update
to
restore
datetime
of
od21_mscl_calibration record which was
updated step 2, if the updated record is on
the source database (master database when
splitting,
marging).
28
and
slave
database
when