How to synchronize two databases in Oracle (using the materialized view) (Oracle snapshot instance)

How to synchronize two databases in Oracle (using the materialized view) (Oracle snapshot instance)

How to synchronize two databases in Oracle (using the materialized view) (Oracle snapshot instance)

I. Technical Implementation Details
Unless otherwise stated, the following SQL commands are run under the SYSETM user of the database ora_db2.
Assume that you want to copy (or synchronize) all user db1 tables in database ora_db1 on the other server.

1. Create a database connection (dblink) used to connect to database 1 (ora_db1)

SQL> CREATE PUBLIC DATABASE LINK testLK CONNECT TO db1 identified by db1
Using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521 ))
)
(CONNECT_DATA =
(Service_name = ora_db1)
)
)';

** For security reasons, you can use a private data connection.

2. Create a tablespace named Snapshot_ts to store snapshots, and create a user named db2 associated with the tablespace.

SQL> CREATE TABLESPACE snapshot_ts DATAFILE
'D: \ db \ snapshot_ts.dbf 'size 30 M
Default storage (INITIAL 30 K
NEXT 15 K
MINEXTENTS 1
MAXEXTENTS 100
PCTINCREASE 0)
ONLINE
PERMANENT;

SQL> CREATE USER db2
Identified by db2
Default tablespace snapshot_ts;

SQL> GRANT CONNECT, RESOURCE TO db2;

You can roughly estimate the size of the tablespace snapshot_ts by using the following SQL statement in the ora_db1 database as the db1 user.

SQL> SELECT SUM (bytes)
FROM USER_SEGMENTS
WHERE SEGMENT_NAME IN
(Select table_name from user_tables );

3. Run the following script to generate a snapshot script for creating the db1 user code table on the ora_db1 database:

Note: run the following select statement in db1 and run the create_snapshot. SQL script in db2.

SQL> spool d: \ snap \ create_snapshot. SQL

Note that the script used to generate the required table snapshot has some limitations. If the table to generate the snapshot contains a long column, 'select * 'will not work here, the preceding SQL script cannot automatically create a script to generate the required snapshot. You must create a table snapshot by explicitly adding a long column name to the select list. The following is an example. If the note type of a column in table 1 on which we want to create a snapshot is long, we need to write the following snapshot creation script separately: