DBA Tips: 05.17. Executing pg_dump Initial Load [Postgres->Oracle (Database: dbrep1)]

OGG server #1

***************************

- ogg-srv01.oracak.ru

1. Turn off Replicat rrep1a (or ensure that it is stopped)
. ~/.profile.oracle.gg19
./ggsci
dblogin useridalias ggadmin_dbrep1
stop replicat R_REP1A
info all

2. Disable all constraints & triggers on the target (Oracle) database.
[oracle] sqlplus>$

ALTER TRIGGER STOCK.BONDS_SETTING_ARCH_TRG DISABLE;
ALTER TRIGGER STOCK.BONDS_SETTING_TRG DISABLE;
ALTER TRIGGER STOCK.BONDS_TRG DISABLE;
ALTER TRIGGER STOCK.BONDS_ZAJVKA_TRG DISABLE;
ALTER TRIGGER STOCK.CLIENT_ACCOUNT_TRG DISABLE;
ALTER TRIGGER STOCK.DIVIDENTS_TRG DISABLE;
ALTER TRIGGER STOCK.JOB_LOGS_TRG DISABLE;
ALTER TRIGGER STOCK.PORTFOLIO_TRG DISABLE;
ALTER TRIGGER STOCK.QUOTES_ANALYST_15M_TRG DISABLE;
ALTER TRIGGER STOCK.QUOTES_ANALYST_1D_TRG DISABLE;
ALTER TRIGGER STOCK.QUOTES_ANALYST_1H_TRG DISABLE;
ALTER TRIGGER STOCK.QUOTES_INS DISABLE;
ALTER TRIGGER STOCK.QUOTES_VOLUME_ARCH_TRG DISABLE;
ALTER TRIGGER STOCK.QUOTES_VOLUME_TRG DISABLE;
ALTER TRIGGER STOCK.REPORT_BENEFIT_COMPARATOR_TRG DISABLE;
ALTER TRIGGER STOCK.REPORT_BENEFIT_TRG DISABLE;
ALTER TRIGGER STOCK.REPORT_EDU_COMPARATOR_TRG DISABLE;
ALTER TRIGGER STOCK.REPORT_EDU_TRG DISABLE;
ALTER TRIGGER STOCK.SDELKA_INS DISABLE;
ALTER TRIGGER STOCK.SIGNAL_TRG DISABLE;
ALTER TRIGGER STOCK.TRADING_EDU_TRG DISABLE;
ALTER TRIGGER STOCK.TRADING_PROD_TRG DISABLE;

3. Clearing tables before loading (Oracle database).
[oracle] sqlplus>$

truncate table STOCK.QUOTES_ARCH;
truncate table STOCK.QUOTES_ANALYST_1H;
truncate table STOCK.QUOTES_ANALYST_1D;
truncate table STOCK.QUOTES_ANALYST_15M;
truncate table STOCK.REPORT_BENEFIT_COMPARATOR;
truncate table STOCK.DIVIDENTS;
truncate table STOCK.PORTFOLIO_ARCH;
truncate table STOCK.REPORT_EDU;
truncate table STOCK.PORTFOLIO;
truncate table STOCK.SDELKA_ARCH;
truncate table STOCK.BONDS_ZAJVKA_ARCH;
truncate table STOCK.BONDS_ZAJVKA;
truncate table STOCK.QUOTES_VOLUME;
truncate table STOCK.BONDS_SETTING;
truncate table STOCK.SIGNAL;
truncate table STOCK.TRADING_PROD;
truncate table STOCK.REPORT_BENEFIT;
truncate table STOCK.TRADING_PROD_ARCH;
truncate table STOCK.BONDS;
truncate table STOCK.CLIENT_ACCOUNT;
truncate table STOCK.JOB_LOGS;
truncate table STOCK.REPORT_EDU_COMPARATOR;
truncate table STOCK.SDELKA;
truncate table STOCK.QUOTES;
truncate table STOCK.SIGNAL_ARCH;
truncate table STOCK.CLIENT_ACCOUNT_ARCH;
truncate table STOCK.BONDS_SETTING_ARCH;
truncate table STOCK.QUOTES_VOLUME_ARCH;
truncate table STOCK.TRADING_EDU;
truncate table STOCK.BONDS_ARCH;

Postgres cluster #1:
***************************
- db-pgsql01.oracak.com - master

4. Execute the export of tables with the --snapshot option in the insert_into_table format.
4.1. In the first session, open the session, obtain the current LSN, and export the snapshot.
Session 1:
[postgres@db-pgsql01.oracak.com]>$
psql -h db-pgsql01.oracak.com -d dbquik1 -U stock

dbquik1=> BEGIN;
BEGIN
dbquik1=*> SELECT pg_current_wal_lsn(), pg_export_snapshot();
pg_current_wal_lsn | pg_export_snapshot
--------------------+---------------------
37/F304F5D0 | 0000000A-000CB898-1

• Note: Remember the LSN and the snapshot name (this data will be used below)

4. 2. Perform the export with the --snapshot option.
Session 2:
pg_dump -d dbquik1 -U stock -h db-pgsql01.oracak.com -f ./pgsql_to_oracle.sql --column-inserts --data-only \

--snapshot='0000001E-00005145-1' \
--table=stock.BONDS \
--table=stock.BONDS_ARCH \
--table=stock.BONDS_SETTING \
--table=stock.BONDS_SETTING_ARCH \
--table=stock.BONDS_ZAJVKA \
--table=stock.BONDS_ZAJVKA_ARCH \
--table=stock.CLIENT_ACCOUNT \
--table=stock.CLIENT_ACCOUNT_ARCH \
--table=stock.DIVIDENTS \
--table=stock.JOB_LOGS \
--table=stock.PORTFOLIO \
--table=stock.PORTFOLIO_ARCH \
--table=stock.QUOTES \
--table=stock.QUOTES_ANALYST_15M \
--table=stock.QUOTES_ANALYST_1D \
--table=stock.QUOTES_ANALYST_1H \
--table=stock.QUOTES_ARCH \
--table=stock.QUOTES_VOLUME \
--table=stock.QUOTES_VOLUME_ARCH \
--table=stock.REPORT_BENEFIT \
--table=stock.REPORT_BENEFIT_COMPARATOR \
--table=stock.REPORT_EDU \
--table=stock.REPORT_EDU_COMPARATOR \
--table=stock.SDELKA \
--table=stock.SDELKA_ARCH \
--table=stock.SIGNAL \
--table=stock.SIGNAL_ARCH \
--table=stock.TRADING_EDU \
--table=stock.TRADING_PROD\
--table=stock.TRADING_PROD_ARCH \
--table=stock.p202* \
--table=stock.sys*

4.3. Close the first session and complete the export of the snapshot.
Session 1:
dbquik1=*> exit

5. Perform the replacement of the 'stock' schema with 'ibs' in the generated export file.
-- Optionally, if the schemas have different names.
[postgres]>$
sed -i 's/stock./bonds./g' pgsql_to_oracle.sql

6. Копируем полученный файл с экспортом на сервер БД Oracle
dbrep1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-ora01.oracak.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbrep1)))

Server БД Oracle dbrep1
***************************
- db-ora01.oracak.com

7. Perform the Initial Load into the Oracle database.
[oracle@db-ora01.oracak.com]>$
export NLS_LANG=AMERICAN_AMERICA.UTF8
sqlplus stock@dbrep1
> sqlplus

set timing on time on;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
@pgsql_to_oracle.sql
commit;
exit

8. Enable all constraints & triggers on the target (Oracle) database
> sqlplus

ALTER TRIGGER STOCK.BONDS_SETTING_ARCH_TRG ENABLE;
ALTER TRIGGER STOCK.BONDS_SETTING_TRG ENABLE;
ALTER TRIGGER STOCK.BONDS_TRG ENABLE;
ALTER TRIGGER STOCK.BONDS_ZAJVKA_TRG ENABLE;
ALTER TRIGGER STOCK.CLIENT_ACCOUNT_TRG ENABLE;
ALTER TRIGGER STOCK.DIVIDENTS_TRG ENABLE;
ALTER TRIGGER STOCK.JOB_LOGS_TRG ENABLE;
ALTER TRIGGER STOCK.PORTFOLIO_TRG ENABLE;
ALTER TRIGGER STOCK.QUOTES_ANALYST_15M_TRG ENABLE;
ALTER TRIGGER STOCK.QUOTES_ANALYST_1D_TRG ENABLE;
ALTER TRIGGER STOCK.QUOTES_ANALYST_1H_TRG ENABLE;
ALTER TRIGGER STOCK.QUOTES_INS ENABLE;
ALTER TRIGGER STOCK.QUOTES_VOLUME_ARCH_TRG ENABLE;
ALTER TRIGGER STOCK.QUOTES_VOLUME_TRG ENABLE;
ALTER TRIGGER STOCK.REPORT_BENEFIT_COMPARATOR_TRG ENABLE;
ALTER TRIGGER STOCK.REPORT_BENEFIT_TRG ENABLE;
ALTER TRIGGER STOCK.REPORT_EDU_COMPARATOR_TRG ENABLE;
ALTER TRIGGER STOCK.REPORT_EDU_TRG ENABLE;
ALTER TRIGGER STOCK.SDELKA_INS ENABLE;
ALTER TRIGGER STOCK.SIGNAL_TRG ENABLE;
ALTER TRIGGER STOCK.TRADING_EDU_TRG ENABLE;
ALTER TRIGGER STOCK.TRADING_PROD_TRG ENABLE;

OGG server #1
***************************
- ogg-srv01.oracak.ru

9. Once you verify that the intial load completes, start change synchronization REPLICAT on the Target system
[oracle@ogg-srv01.oracak.ru]$>
. ~/.profile.oracle.gg19
./ggsci
ggsci>
start replicat r_rep1a, aftercsn 36/87041C10

10. Monitor the Replicat's progress and wait for the completion of LAG.
[oracle@ogg-srv01.oracak.ru]$>
. ~/.profile.oracle.gg19
./ggsci
ggsci>
info rrep1a
VIEW REPORT rrep1a

Сервер OGG №2
***************************
- ogg-srv02.oracak.ru

11. Start Replicat on the second OGG server.
. ~/.profile.postgres.gg21

./ggsci
ggsci>
start replicat rrep1a

Notes:
--------------------------------------
--1. Disable/enable constraints & triggers.
select
'alter table '||b.owner||'.'||b.table_name||' disable constraint '||b.constraint_name||';' as sql_disable_const,
'alter table '||b.owner||'.'||b.table_name||' enable constraint '||b.constraint_name||';' as sql_enable_const
from dba_constraints a, dba_constraints b, dba_cons_columns c
where a.owner=b.r_owner
and b.owner=c.owner and b.table_name=c.table_name and b.constraint_name=c.constraint_name
and a.constraint_name=b.r_constraint_name and b.constraint_type='R' and a.owner='STOCK' and b.status='ENABLED'
and b.table_name in
('BONDS','BONDS_ARCH','BONDS_SETTING','BONDS_SETTING_ARCH','BONDS_ZAJVKA','BONDS_ZAJVKA_ARCH','CLIENT_ACCOUNT',
'CLIENT_ACCOUNT_ARCH','DIVIDENTS','JOB_LOGS','PORTFOLIO','PORTFOLIO_ARCH','QUOTES','QUOTES_ANALYST_15M','QUOTES_ANALYST_1D',
'QUOTES_ANALYST_1H','QUOTES_ARCH','QUOTES_VOLUME','QUOTES_VOLUME_ARCH','REPORT_BENEFIT','REPORT_BENEFIT_COMPARATOR','REPORT_EDU',
'REPORT_EDU_COMPARATOR','SDELKA','SDELKA_ARCH','SIGNAL','SIGNAL_ARCH','TRADING_EDU','TRADING_PROD','TRADING_PROD_ARCH'
)
union
select
'alter table '||b.owner||'.'||b.table_name||' disable constraint '||b.constraint_name||';' as sql_disable_const,
'alter table '||b.owner||'.'||b.table_name||' enable constraint '||b.constraint_name||';' as sql_enable_const
from dba_constraints a, dba_constraints b, dba_cons_columns c
where a.owner=b.r_owner
and b.owner=c.owner and b.table_name=c.table_name and b.constraint_name=c.constraint_name
and a.constraint_name=b.r_constraint_name and b.constraint_type='R' and a.owner='STOCK' and b.status='ENABLED'
and b.R_CONSTRAINT_NAME in
(select constraint_name from dba_constraints where constraint_type='P' and table_name in
('BONDS','BONDS_ARCH','BONDS_SETTING','BONDS_SETTING_ARCH','BONDS_ZAJVKA','BONDS_ZAJVKA_ARCH','CLIENT_ACCOUNT',
'CLIENT_ACCOUNT_ARCH','DIVIDENTS','JOB_LOGS','PORTFOLIO','PORTFOLIO_ARCH','QUOTES','QUOTES_ANALYST_15M','QUOTES_ANALYST_1D',
'QUOTES_ANALYST_1H','QUOTES_ARCH','QUOTES_VOLUME','QUOTES_VOLUME_ARCH','REPORT_BENEFIT','REPORT_BENEFIT_COMPARATOR','REPORT_EDU',
'REPORT_EDU_COMPARATOR','SDELKA','SDELKA_ARCH','SIGNAL','SIGNAL_ARCH','TRADING_EDU','TRADING_PROD','TRADING_PROD_ARCH'
)
)
union
SELECT
'ALTER TRIGGER STOCK.' || trigger_name || ' DISABLE;' as sql_disable_trigger,
'ALTER TRIGGER STOCK.' || trigger_name || ' ENABLE;' as sql_enable_trigger
FROM dba_triggers t where table_owner='STOCK' and t.STATUS = 'ENABLED'
and table_name in
(
'BONDS','BONDS_ARCH','BONDS_SETTING','BONDS_SETTING_ARCH','BONDS_ZAJVKA','BONDS_ZAJVKA_ARCH','CLIENT_ACCOUNT',
'CLIENT_ACCOUNT_ARCH','DIVIDENTS','JOB_LOGS','PORTFOLIO','PORTFOLIO_ARCH','QUOTES','QUOTES_ANALYST_15M','QUOTES_ANALYST_1D',
'QUOTES_ANALYST_1H','QUOTES_ARCH','QUOTES_VOLUME','QUOTES_VOLUME_ARCH','REPORT_BENEFIT','REPORT_BENEFIT_COMPARATOR','REPORT_EDU',
'REPORT_EDU_COMPARATOR','SDELKA','SDELKA_ARCH','SIGNAL','SIGNAL_ARCH','TRADING_EDU','TRADING_PROD','TRADING_PROD_ARCH'
);

--2. Clearing tables before loading.
select 'truncate table '||owner ||'.'||table_name||';' from dba_tables where table_name in
('BONDS','BONDS_ARCH','BONDS_SETTING','BONDS_SETTING_ARCH','BONDS_ZAJVKA','BONDS_ZAJVKA_ARCH','CLIENT_ACCOUNT',
'CLIENT_ACCOUNT_ARCH','DIVIDENTS','JOB_LOGS','PORTFOLIO','PORTFOLIO_ARCH','QUOTES','QUOTES_ANALYST_15M','QUOTES_ANALYST_1D',
'QUOTES_ANALYST_1H','QUOTES_ARCH','QUOTES_VOLUME','QUOTES_VOLUME_ARCH','REPORT_BENEFIT','REPORT_BENEFIT_COMPARATOR','REPORT_EDU',
'REPORT_EDU_COMPARATOR','SDELKA','SDELKA_ARCH','SIGNAL','SIGNAL_ARCH','TRADING_EDU','TRADING_PROD','TRADING_PROD_ARCH'
);

-- 3. Quantitative comparison of rows.
-- Postgres
select 'BONDS',count(*) from stock.BONDS union
select 'BONDS_ARCH',count(*) from stock.BONDS_ARCH union
select 'BONDS_SETTING',count(*) from stock.BONDS_SETTING union
select 'BONDS_SETTING_ARCH',count(*) from stock.BONDS_SETTING_ARCH union
select 'BONDS_ZAJVKA',count(*) from stock.BONDS_ZAJVKA union
select 'BONDS_ZAJVKA_ARCH',count(*) from stock.BONDS_ZAJVKA_ARCH union
select 'CLIENT_ACCOUNT',count(*) from stock.CLIENT_ACCOUNT union
select 'CLIENT_ACCOUNT_ARCH',count(*) from stock.CLIENT_ACCOUNT_ARCH union
select 'DIVIDENTS',count(*) from stock.DIVIDENTS union
select 'JOB_LOGS',count(*) from stock.JOB_LOGS union
select 'PORTFOLIO',count(*) from stock.PORTFOLIO union
select 'PORTFOLIO_ARCH',count(*) from stock.PORTFOLIO_ARCH union
select 'QUOTES',count(*) from stock.QUOTES union
select 'QUOTES_ANALYST_15M',count(*) from stock.QUOTES_ANALYST_15M union
select 'QUOTES_ANALYST_1D',count(*) from stock.QUOTES_ANALYST_1D union
select 'QUOTES_ANALYST_1H',count(*) from stock.QUOTES_ANALYST_1H union
select 'QUOTES_ARCH',count(*) from stock.QUOTES_ARCH union
select 'QUOTES_VOLUME',count(*) from stock.QUOTES_VOLUME union
select 'QUOTES_VOLUME_ARCH',count(*) from stock.QUOTES_VOLUME_ARCH union
select 'REPORT_BENEFIT',count(*) from stock.REPORT_BENEFIT union
select 'REPORT_BENEFIT_COMPARATOR',count(*) from stock.REPORT_BENEFIT_COMPARATOR union
select 'REPORT_EDU',count(*) from stock.REPORT_EDU union
select 'REPORT_EDU_COMPARATOR',count(*) from stock.REPORT_EDU_COMPARATOR union
select 'SDELKA',count(*) from stock.SDELKA union
select 'SDELKA_ARCH',count(*) from stock.SDELKA_ARCH union
select 'SIGNAL',count(*) from stock.SIGNAL union
select 'SIGNAL_ARCH',count(*) from stock.SIGNAL_ARCH union
select 'TRADING_EDU',count(*) from stock.TRADING_EDU union
select 'TRADING_PROD',count(*) from stock.TRADING_PROD union
select 'TRADING_PROD_ARCH',count(*) from stock.TRADING_PROD_ARCH order by 1;

-- Oracle
select 'BONDS',count(*) from IBS.BONDS union
select 'BONDS_ARCH',count(*) from IBS.BONDS_ARCH union
select 'BONDS_SETTING',count(*) from IBS.BONDS_SETTING union
select 'BONDS_SETTING_ARCH',count(*) from IBS.BONDS_SETTING_ARCH union
select 'BONDS_ZAJVKA',count(*) from IBS.BONDS_ZAJVKA union
select 'BONDS_ZAJVKA_ARCH',count(*) from IBS.BONDS_ZAJVKA_ARCH union
select 'CLIENT_ACCOUNT',count(*) from IBS.CLIENT_ACCOUNT union
select 'CLIENT_ACCOUNT_ARCH',count(*) from IBS.CLIENT_ACCOUNT_ARCH union
select 'DIVIDENTS',count(*) from IBS.DIVIDENTS union
select 'JOB_LOGS',count(*) from IBS.JOB_LOGS union
select 'PORTFOLIO',count(*) from IBS.PORTFOLIO union
select 'PORTFOLIO_ARCH',count(*) from IBS.PORTFOLIO_ARCH union
select 'QUOTES',count(*) from IBS.QUOTES union
select 'QUOTES_ANALYST_15M',count(*) from IBS.QUOTES_ANALYST_15M union
select 'QUOTES_ANALYST_1D',count(*) from IBS.QUOTES_ANALYST_1D union
select 'QUOTES_ANALYST_1H',count(*) from IBS.QUOTES_ANALYST_1H union
select 'QUOTES_ARCH',count(*) from IBS.QUOTES_ARCH union
select 'QUOTES_VOLUME',count(*) from IBS.QUOTES_VOLUME union
select 'QUOTES_VOLUME_ARCH',count(*) from IBS.QUOTES_VOLUME_ARCH union
select 'REPORT_BENEFIT',count(*) from IBS.REPORT_BENEFIT union
select 'REPORT_BENEFIT_COMPARATOR',count(*) from IBS.REPORT_BENEFIT_COMPARATOR union
select 'REPORT_EDU',count(*) from IBS.REPORT_EDU union
select 'REPORT_EDU_COMPARATOR',count(*) from IBS.REPORT_EDU_COMPARATOR union
select 'SDELKA',count(*) from IBS.SDELKA union
select 'SDELKA_ARCH',count(*) from IBS.SDELKA_ARCH union
select 'SIGNAL',count(*) from IBS.SIGNAL union
select 'SIGNAL_ARCH',count(*) from IBS.SIGNAL_ARCH union
select 'TRADING_EDU',count(*) from IBS.TRADING_EDU union
select 'TRADING_PROD',count(*) from IBS.TRADING_PROD union
select 'TRADING_PROD_ARCH',count(*) from IBS.TRADING_PROD_ARCH order by 1;

<<< Back Next >>>

Pages

05.17. Executing pg_dump Initial Load [Postgres->Oracle (Database: dbrep1)]