set pages 200 lines 200
set lines 130;
set pages 999;
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
FROM v$log_history
GROUP BY TO_CHAR (first_time, 'YYYY-MON-DD')
ORDER BY 1;
Tuesday, February 27, 2018
GoldenGate Extract, Pump and Replicate process
1. create extract process
2. start extract process
3. create pump process
4. start pump process
5. take expdp backup from source
6. restore through impdp on target
7. Create xml table to tabuler format table at the target
8. Add extract on Target
9. Add replicat on Target
10. Start Extract on Target
11. Start Replicat on Target
12. create replicate process for xml table on Target
13. start replicate proces for xml table on Target
=========================================
Implement
Table Name: FBNK_PR_H_INSTR_ISSUED
fbnk_pr_h_instr_issued
Source >>
Extract Process:
1) EPPHII01
Pump Process:
2) PPPHII01
Target >>
Extract Process:
1) ESPHII01
Replicate:
2) RSPHII01
3) RUPHII01
alter user nblt24 identified by ggN24#12 account unlock;
select recid, ins_pay_to from FBNK_PR_H_INSTR_ISSUED_TBL where recid='PO.1999001766023-6860951';
update FBNK_PR_H_INSTR_ISSUED_TBL tbl
set tbl.ins_pay_to='BANGLADESH CHEMICAL INDUSTRIES CORPORATION'
where recid='PO.1999001766023-6860951';
Work at Source
===============
ggsci> dblogin useridalias SOURCEDB domain admin
--- Extract
ggsci> edit param EPPHII01
ggsci> register extract EPPHII01 database
ggsci> add extract EPPHII01, integrated tranlog, begin now
ggsci> add exttrail ./dirdat/t6, extract EPPHII01, megabytes 5
--- Pump
ggsci> edit param PPPHII01
ggsci> add extract PPPHII01, exttrailsource ./dirdat/t6 begin now
ggsci> add rmttrail ./dirdat/p6, extract PPPHII01, megabytes 5
--- Start Extract and Pump
ggsci> start EPPHII01
ggsci> start PPPHII01
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 29 17:24:29 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> col current_scn for 999999999999999999999999999999999
SQL> select current_scn from v$database;
CURRENT_SCN
----------------------------------
42177587523
SQL>
oracle@dcpdb2 [/home/oracle]$ cd /goldengate/dump_dir
oracle@dcpdb2 [/home/oracle]$ cd /goldengate/dump_dir
oracle@dcpdb2 [/goldengate/dump_dir]$ ls
oracle@dcpdb2 [/goldengate/dump_dir]$ nohup.out
sh: nohup.out: Execute permission denied.
oracle@dcpdb2 [/goldengate/dump_dir]$ > nohup.out
oracle@dcpdb2 [/goldengate/dump_dir]$
oracle@dcpdb2 [/goldengate/dump_dir]$ nohup ./export_fbnk_pr_h_instr_issued.sh &
[1] 3548
oracle@dcpdb2 [/goldengate/dump_dir]$ Sending output to nohup.out
oracle@dcpdb2 [/goldengate/dump_dir]$
oracle@dcpdb2 [/goldengate/dump_dir]$ tail -f nohup.out
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" tables=NBLT24.FBNK_PR_H_INSTR_ISSUED dumpfile=expFBNK_PR_H_INSTR_ISSUED.dmp logfile=expFBNK_PR_H_INSTR_ISSUED.log directory=gg_dump flashback_scn=42177587523
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 613.0 MB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "NBLT24"."FBNK_PR_H_INSTR_ISSUED" 527.7 MB 781793 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/goldengate/dump_dir/expFBNK_PR_H_INSTR_ISSUED.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Nov 29 17:44:51 2017 elapsed 0 00:05:27
[1] + Done(1) nohup ./export_fbnk_pr_h_instr_issued.sh &
oracle@dcpdb2 [/goldengate/dump_dir]$
oracle@dcpdb2 [/goldengate/dump_dir]$ scp expFBNK_PR_H_INSTR_ISSUED.dmp 172.31.10.19:/fra/dump_dir/
oracle@172.31.10.19's password:
expFBNK_PR_H_INSTR_ISSUED.dmp 100% 539MB 67.4MB/s 71.0MB/s 00:08
oracle@dcpdb2 [/goldengate/dump_dir]$
--====Target work==
[oracle@ggprod ~]$ cd /fra/dump_dir
[oracle@ggprod dump_dir]$ ls
expFBNK_ACCOUNT.dmp expFBNK_ACCOUNT.dmp.log expFBNK_PR_H_INSTR_ISSUED.dmp
[oracle@ggprod dump_dir]$
[oracle@ggprod dump_dir]$
impdp userid="'NBLT24/ggN24#12'" dumpfile=expFBNK_PR_H_INSTR_ISSUED.dmp logfile=expFBNK_PR_H_INSTR_ISSUED.log directory=GG_DUMP
[oracle@ggprod ~]$ cd /fra/dump_dir
[oracle@ggprod dump_dir]$ ls
expFBNK_ACCOUNT.dmp expFBNK_ACCOUNT.dmp.log expFBNK_PR_H_INSTR_ISSUED.dmp
[oracle@ggprod dump_dir]$
[oracle@ggprod dump_dir]$
[oracle@ggprod dump_dir]$ impdp userid="'NBLT24/ggN24#12'" dumpfile=expFBNK_PR_H_INSTR_ISSUED.dmp logfile=expFBNK_PR_H_INSTR_ISSUED.log directory=GG_DUMP
Import: Release 11.2.0.4.0 - Production on Wed Nov 29 18:07:48 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "NBLT24"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "NBLT24"."SYS_IMPORT_FULL_01": userid="NBLT24/********" dumpfile=expFBNK_PR_H_INSTR_ISSUED.dmp logfile=expFBNK_PR_H_INSTR_ISSUED.log directory=GG_DUMP
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "NBLT24"."FBNK_PR_H_INSTR_ISSUED" 527.7 MB 781793 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "NBLT24"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 29 18:14:40 2017 elapsed 0 00:06:51
[oracle@ggprod dump_dir]$
[oracle@ggprod dump_dir]$ cd
[oracle@ggprod ~]$ cd /scripts
[oracle@ggprod scripts]$ ls
add_data_file.sh create_fbnk_customer.sql create_fbnk_stmt_index.sh.bk import nohup.out.2 Opatch.lst
create_fbnk_account.sh create_fbnk_phii.sh delete_archive_log.sh initggprod.130817.hidden.ora nohup.out.bk tbs_create.sh
create_fbnk_account.sql create_fbnk_phii.sql F_HOLD_CONTROL_10FEB.sh initggprod.130817.ora nohup.out.fa tbs_stmt_index_create.sh
create_fbnk_customer.sh create_fbnk_stmt_index.sh F_HOLD_CONTROL_10FEB.sql nohup.out nohup.out.phii
[oracle@ggprod scripts]$
[oracle@ggprod scripts]$
[oracle@ggprod scripts]$ > nohup.out
[oracle@ggprod scripts]$
[oracle@ggprod scripts]$ nohup ./create_fbnk_phii.sh &
[1] 22267
[oracle@ggprod scripts]$ nohup: ignoring input and appending output to ‘nohup.out’
[oracle@ggprod scripts]$ tail -f nohup.out
Table created.
Elapsed: 00:00:19.50
Table altered.
Elapsed: 00:00:00.10
Table altered.
Elapsed: 00:00:11.63
^C[1]+ Done nohup ./create_fbnk_phii.sh
[oracle@ggprod scripts]$
--===
cat dirprm/esfaxx01.prm
--==========
--- Add Extract and Replicate
$ gg
ggsci> edit param ESPHII01
ggsci> dblogin USERIDALIAS GG_SOURCE DOMAIN admin
ggsci> register extract ESPHII01 database
ggsci> add extract ESPHII01, integrated tranlog, begin now
ggsci> add exttrail ./dirdat_S/s2, extract ESPHII01, megabytes 5
$ gg
ggsci> edit param RSPHII01
ggsci> dblogin useridalias GG_REPL domain admin
ggsci> add replicat RSPHII01, integrated, exttrail ./dirdat_S/s2
--- Start Extract and Replicate
ggsci> start ESPHII01
ggsci> start RSPHII01
ggsci> edit param RUPHII01
ggsci> dblogin USERIDALIAS TARGETDB DOMAIN admin
ggsci> add replicat RUPHII01, integrated, exttrail ./dirdat/p6
--- Start Replicate
ggsci> start RUPHII01
select count(1) from NBLT24.FBNK_PR_H_INSTR_ISSUED;
select count(1) from NBLT24.FBNK_PR_H_INSTR_ISSUED_TBL;
select username,expiry_date from dba_users where username='RMANBKP';
==========================*******************==============
=================Start New One=======================
F_HOLD_CONTROL_10FEB
EPFHCF01 -- Extract Process
PPFHCF01 -- Pump Process
RUFHCF01 -- Replicate Process
--===GoldenGate Extract Process === SOURCEDB==
F_HOLD_CONTROL_10FEB
EPFHCF01 -- Extract Process
oracle@dcpdb2 [/home/oracle]$ cd /goldengate/gg_home/12.2.0.1
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
HP/UX, IA64, 64bit (optimized), Oracle 11g on Dec 15 2015 16:01:27
Operating system character set identified as hp-roman8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (dcpdb2) 4>
GGSCI (dcpdb2) 4>
GGSCI (dcpdb2) 4>
--==First Step ===
dblogin useridalias SOURCEDB domain admin
add trandata NBLT24.F_HOLD_CONTROL_10FEB
--=============
GGSCI (dcpdb2) 4> dblogin useridalias SOURCEDB domain admin
Successfully logged into database.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 5>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 5> add trandata NBLT24.F_HOLD_CONTROL_10FEB
Logging of supplemental redo data enabled for table NBLT24.F_HOLD_CONTROL_10FEB.
TRANDATA for scheduling columns has been added on table 'NBLT24.F_HOLD_CONTROL_10FEB'.
TRANDATA for instantiation CSN has been added on table 'NBLT24.F_HOLD_CONTROL_10FEB'.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 6>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 6>
--=====Second Step=====
edit param EPFHCF01
EXTRACT EPFHCF01
USERIDALIAS SOURCEDB domain admin
EXTTRAIL ./dirdat/t7
TABLE NBLT24.F_HOLD_CONTROL_10FEB;
--==============
GGSCI (dcpdb2 as ggadmin@dcpdb1) 7> edit param EPFHCF01
EXTRACT EPFHCF01
USERIDALIAS SOURCEDB domain admin
EXTTRAIL ./dirdat/t7
TABLE NBLT24.F_HOLD_CONTROL_10FEB;
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"/goldengate/gg_home/12.2.0.1/dirprm/epfhcf01.prm" 5 lines, 108 characters
--====Third Step ==
dblogin useridalias SOURCEDB domain admin
register extract EPFHCF01 database
add extract EPFHCF01, integrated tranlog, begin now
add exttrail ./dirdat/t7, extract EPFHCF01, megabytes 5
start EPFHCF01
--===================
GGSCI (dcpdb2 as ggadmin@dcpdb1) 16> dblogin useridalias SOURCEDB domain admin
Successfully logged into database.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 17>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 17> register extract EPFHCF01 database
2017-10-03 11:43:01 INFO OGG-02003 Extract EPFHCF01 successfully registered with database at SCN 38807247714.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 18>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 18>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 19> add extract EPFHCF01, integrated tranlog, begin now
EXTRACT (Integrated) added.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 20>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 21> add exttrail ./dirdat/t7, extract EPFHCF01, megabytes 5
EXTTRAIL added.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 22>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 22> start EPFHCF01
Sending START request to MANAGER ...
EXTRACT EPFHCF01 starting
GGSCI (dcpdb2 as ggadmin@dcpdb1) 23>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 23> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EPFAXX01 00:00:02 00:00:04
EXTRACT RUNNING EPFCXX01 00:00:03 00:00:02
EXTRACT RUNNING EPFFTX01 00:00:03 00:00:04
EXTRACT RUNNING EPFHCF01 00:00:03 00:00:00
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$ ls -lrt dirdat/t7*
-rw-r----- 1 oracle oinstall 1348 Oct 3 11:46 dirdat/t7000000000
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$
--===GoldenGate Pump Process === SOURCEDB====
F_HOLD_CONTROL_10FEB
PPFHCF01 -- Pump Process
oracle@dcpdb2 [/home/oracle]$
oracle@dcpdb2 [/home/oracle]$ cd /goldengate/gg_home/12.2.0.1
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
HP/UX, IA64, 64bit (optimized), Oracle 11g on Dec 15 2015 16:01:27
Operating system character set identified as hp-roman8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (dcpdb2) 1>
--===Fourth Step====
dblogin useridalias SOURCEDB domain admin
edit param PPFHCF01
EXTRACT PPFHCF01
USERIDALIAS SOURCEDB domain admin
RMTHOST 172.31.10.19, MGRPORT 7809
RMTTRAIL ./dirdat/p7
TABLE NBLT24.F_HOLD_CONTROL_10FEB;
--======
GGSCI (dcpdb2) 1> dblogin useridalias SOURCEDB domain admin
Successfully logged into database.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 2>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 3> edit param PPFHCF01
EXTRACT PPFHCF01
USERIDALIAS SOURCEDB domain admin
RMTHOST 172.31.10.19, MGRPORT 7809
RMTTRAIL ./dirdat/p7
TABLE NBLT24.F_HOLD_CONTROL_10FEB;
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"/goldengate/gg_home/12.2.0.1/dirprm/ppfhcf01.prm" 5 lines, 142 characters
GGSCI (dcpdb2 as ggadmin@dcpdb1) 12>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 12>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 12> view param PPFHCF01
EXTRACT PPFHCF01
USERIDALIAS SOURCEDB domain admin
RMTHOST 172.31.10.19, MGRPORT 7809
RMTTRAIL ./dirdat/p7
TABLE NBLT24.F_HOLD_CONTROL_10FEB;
GGSCI (dcpdb2 as ggadmin@dcpdb1) 13>
--===Fifth Step====
add extract PPFHCF01, exttrailsource ./dirdat/t7 begin now
add rmttrail ./dirdat/p7, extract PPFHCF01, megabytes 5
start PPFHCF01
--========================
GGSCI (dcpdb2 as ggadmin@dcpdb1) 13> add extract PPFHCF01, exttrailsource ./dirdat/t7 begin now
EXTRACT added.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 14>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 14> add rmttrail ./dirdat/p7, extract PPFHCF01, megabytes 5
RMTTRAIL added.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 15>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 15>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 16> start PPFHCF01
Sending START request to MANAGER ...
EXTRACT PPFHCF01 starting
GGSCI (dcpdb2 as ggadmin@dcpdb1) 17>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 17>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 17>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EPFAXX01 00:00:02 00:00:00
EXTRACT RUNNING EPFCXX01 00:00:02 00:00:01
EXTRACT RUNNING EPFFTX01 00:00:02 00:00:02
EXTRACT RUNNING EPFHCF01 00:00:02 00:00:04
EXTRACT RUNNING EPFSEX01 00:00:02 00:00:09
EXTRACT RUNNING EPFTXX01 00:00:02 00:00:00
EXTRACT RUNNING EPPHII01 00:00:02 00:00:02
EXTRACT RUNNING PPFAXX01 00:00:00 00:00:01
EXTRACT RUNNING PPFCXX01 00:00:00 00:00:09
EXTRACT RUNNING PPFFTX01 00:00:00 00:00:01
EXTRACT RUNNING PPFHCF01 00:00:00 00:00:09
--===pump process trail logfile (p7) will generate in TARGET DB===
oracle@ggprod ~]$
[oracle@ggprod ~]$ cd /u02/gg_home/12.2.0.1
[oracle@ggprod 12.2.0.1]$
[oracle@ggprod 12.2.0.1]$ ls -lrt dirdat/p7*
-rw-r----- 1 oracle oinstall 0 Oct 3 12:47 dirdat/p7000000000
[oracle@ggprod 12.2.0.1]$
[oracle@ggprod 12.2.0.1]$
--===GoldenGate Replication Process === TARGETDB====
F_HOLD_CONTROL_10FEB
RUFHCF01 -- Replicate Process
--==6th step ==Check pump process log generating on TRAGET DB==
[oracle@ggprod ~]$
[oracle@ggprod ~]$ cd /u02/gg_home/12.2.0.1
[oracle@ggprod 12.2.0.1]$
[oracle@ggprod 12.2.0.1]$ ls -lrt dirdat/p7*
-rw-r----- 1 oracle oinstall 0 Oct 3 12:47 dirdat/p7000000000
[oracle@ggprod 12.2.0.1]$
[oracle@ggprod 12.2.0.1]$
--===7th Step==
dblogin useridalias TARGETDB domain admin
edit param RUFHCF01
REPLICAT RUFHCF01
USERIDALIAS TARGETDB DOMAIN admin
DISCARDFILE ./dirrpt/F_HOLD_CONTROL_10FEB.dsc, PURGE
ASSUMETARGETDEFS
MAP NBLT24.F_HOLD_CONTROL_10FEB, TARGET NBLT24.F_HOLD_CONTROL_10FEB;
=====
GGSCI (ggprod.nblbd.com) 6> dblogin useridalias TARGETDB domain admin
Successfully logged into database.
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 7>
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 7>
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 7> edit param RUFHCF01
REPLICAT RUFHCF01
USERIDALIAS TARGETDB DOMAIN admin
DISCARDFILE ./dirrpt/F_HOLD_CONTROL_10FEB.dsc, PURGE
ASSUMETARGETDEFS
MAP NBLT24.F_HOLD_CONTROL_10FEB, TARGET NBLT24.F_HOLD_CONTROL_10FEB;
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"/u02/gg_home/12.2.0.1/dirprm/rufhcf01.prm" 5L, 191C
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 9> view param RUFHCF01
REPLICAT RUFHCF01
USERIDALIAS TARGETDB DOMAIN admin
DISCARDFILE ./dirrpt/F_HOLD_CONTROL_10FEB.dsc, PURGE
ASSUMETARGETDEFS
MAP NBLT24.F_HOLD_CONTROL_10FEB, TARGET NBLT24.F_HOLD_CONTROL_10FEB;
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 10>
--===8th step ===
dblogin useridalias TARGETDB domain admin
add replicat RUFHCF01, integrated, exttrail ./dirdat/p7
--===================
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 12> dblogin useridalias TARGETDB domain admin
Successfully logged into database.
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 13>
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 13> add replicat RUFHCF01, integrated, exttrail ./dirdat/p7
REPLICAT (Integrated) added.
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 14>
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ESFAXX01 00:26:15 349:09:00
REPLICAT RUNNING RSFAXX01 00:26:03 349:09:13
REPLICAT RUNNING RUFAXX01 00:00:08 00:00:07
REPLICAT RUNNING RUFCXX01 00:00:11 00:01:12
REPLICAT RUNNING RUFFTX01 00:00:10 00:00:03
REPLICAT STOPPED RUFHCF01 00:00:00 00:00:17
REPLICAT RUNNING RUFSEX01 00:00:09 00:00:01
REPLICAT RUNNING RUFTXX01 00:00:12 00:00:00
REPLICAT RUNNING RUPHII01 00:00:30 00:00:49
--==
Check the scn from the database.
sql> col current_scn for 999999999999999999999999999999999
sql> select current_scn from v$database
38814763486
expdp userid="' / as sysdba'" tables=NBLT24.F_HOLD_CONTROL_10FEB dumpfile=expF_HOLD_CONTROL_10FEB.dmp logfile=expF_HOLD_CONTROL_10FEB.log directory=GG_DUMP flashback_scn=38814763486
impdp userid="'NBLT24/ggN24#12'" dumpfile=expF_HOLD_CONTROL_10FEB.dmp logfile=F_HOLD_CONTROL_10FEB.log directory=GG_DUMP
start replicat RUFHCF01, aftercsn 38814763486
--==
oracle@dcpdb2 [/home/oracle]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 3 13:25:46 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
SQL> col current_scn for 999999999999999999999999999999999
SQL> select current_scn from v$database
2
SQL> select current_scn from v$database;
CURRENT_SCN
----------------------------------
38814763486
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
oracle@dcpdb2 [/home/oracle]$
oracle@dcpdb2 [/home/oracle]$
oracle@dcpdb2 [/home/oracle]$
oracle@dcpdb2 [/home/oracle]$ _HOLD_CONTROL_10FEB.log directory=GG_DUMP flashback_scn=38814763486 <
Export: Release 11.2.0.4.0 - Production on Tue Oct 3 13:27:16 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01": userid="/******** AS SYSDBA" tables=NBLT24.F_HOLD_CONTROL_10FEB dumpfile=expF_HOLD_CONTROL_10FEB.dmp logfile=expF_HOLD_CONTROL_10FEB.log directory=GG_DUMP flashback_scn=38814763486
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 120.1 MB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "NBLT24"."F_HOLD_CONTROL_10FEB" 192.0 MB 557852 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/goldengate/dump_dir/expF_HOLD_CONTROL_10FEB.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Oct 3 13:33:20 2017 elapsed 0 00:06:02
oracle@dcpdb2 [/home/oracle]$
oracle@dcpdb2 [/home/oracle]$ cd /goldengate
oracle@dcpdb2 [/goldengate]$ ll
total 4
drwxr-xr-x 2 oracle oinstall 1024 Oct 3 13:27 dump_dir
drwxr-xr-x 3 oracle oinstall 96 Jul 20 14:04 gg_home
drwxr-xr-x 3 oracle oinstall 1024 Jul 20 14:02 soft
oracle@dcpdb2 [/goldengate]$ cd dump_dir
oracle@dcpdb2 [/goldengate/dump_dir]$ ll
total 18781658
-rw-r----- 1 oracle asmadmin 517177344 Sep 17 15:44 expFBNK_PR_H_INSTR_ISSUED.dmp
-rw-r--r-- 1 oracle asmadmin 1782 Sep 17 15:44 expFBNK_PR_H_INSTR_ISSUED.log
-rw-r--r-- 1 oracle asmadmin 1247 Aug 8 12:57 expFBNK_STMT_ENTRY.log
-rw-r----- 1 oracle asmadmin 8885822071 Aug 8 12:57 expFBNK_STMT_ENTRY_01.dmp.gz
-rw-r----- 1 oracle asmadmin 213172224 Oct 3 13:33 expF_HOLD_CONTROL_10FEB.dmp
-rw-r--r-- 1 oracle asmadmin 1608 Oct 3 13:33 expF_HOLD_CONTROL_10FEB.log
-rwxr--r-- 1 oracle oinstall 443 Aug 7 16:57 export_4tables.sh
-rwxr--r-- 1 oracle oinstall 375 Jul 31 14:26 export_fbnk_account.sh
-rwxr--r-- 1 oracle oinstall 378 Jul 31 17:38 export_fbnk_customer.sh
-rwxr--r-- 1 oracle oinstall 417 Aug 8 11:42 export_fbnk_stmt_entry.sh
-rw------- 1 oracle oinstall 1240 Aug 8 12:57 nohup.out
oracle@dcpdb2 [/goldengate/dump_dir]$ scp expF_HOLD_CONTROL_10FEB.dmp oracle@172.31.100.66:/fra/dump_dir/
ssh: connect to host 172.31.100.66 port 22: Connection timed out
lost connection
oracle@dcpdb2 [/goldengate/dump_dir]$ scp expF_HOLD_CONTROL_10FEB.dmp oracle@172.31.10.19:/fra/dump_dir/
oracle@172.31.10.19's password:
expF_HOLD_CONTROL_10FEB.dmp 100% 203MB 50.8MB/s 66.5MB/s 00:04
oracle@dcpdb2 [/goldengate/dump_dir]$
--===
[oracle@ggprod dump_dir]$ impdp userid="'NBLT24/ggN24#12'" dumpfile=expF_HOLD_CONTROL_10FEB.dmp logfile=F_HOLD_CONTROL_10FEB.log directory=GG_DUMP
Import: Release 11.2.0.4.0 - Production on Tue Oct 3 14:08:28 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "NBLT24"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "NBLT24"."SYS_IMPORT_FULL_01": userid="NBLT24/********" dumpfile=expF_HOLD_CONTROL_10FEB.dmp logfile=F_HOLD_CONTROL_10FEB.log directory=GG_DUMP
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "NBLT24"."F_HOLD_CONTROL_10FEB" 192.0 MB 557852 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "NBLT24"."SYS_IMPORT_FULL_01" successfully completed at Tue Oct 3 14:10:08 2017 elapsed 0 00:01:38
[oracle@ggprod dump_dir]$
--====
[oracle@ggprod ~]$ cd /u02/gg_home/12.2.0.1
[oracle@ggprod 12.2.0.1]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (ggprod.nblbd.com) 1>
GGSCI (ggprod.nblbd.com) 2> start replicat RUFHCF01, aftercsn 38814763486
Sending START request to MANAGER ...
REPLICAT RUFHCF01 starting
GGSCI (ggprod.nblbd.com) 3>
GGSCI (ggprod.nblbd.com) 3>
select count(1) from NBLT24.F_HOLD_CONTROL_10FEB;
create undo tablespace UNDOTBS_NEW datafile 'C:\APP\TAREK\ORADATA\VIVA\UNDOTBS_NEW01.dbf' size 500M;
2. start extract process
3. create pump process
4. start pump process
5. take expdp backup from source
6. restore through impdp on target
7. Create xml table to tabuler format table at the target
8. Add extract on Target
9. Add replicat on Target
10. Start Extract on Target
11. Start Replicat on Target
12. create replicate process for xml table on Target
13. start replicate proces for xml table on Target
=========================================
Implement
Table Name: FBNK_PR_H_INSTR_ISSUED
fbnk_pr_h_instr_issued
Source >>
Extract Process:
1) EPPHII01
Pump Process:
2) PPPHII01
Target >>
Extract Process:
1) ESPHII01
Replicate:
2) RSPHII01
3) RUPHII01
alter user nblt24 identified by ggN24#12 account unlock;
select recid, ins_pay_to from FBNK_PR_H_INSTR_ISSUED_TBL where recid='PO.1999001766023-6860951';
update FBNK_PR_H_INSTR_ISSUED_TBL tbl
set tbl.ins_pay_to='BANGLADESH CHEMICAL INDUSTRIES CORPORATION'
where recid='PO.1999001766023-6860951';
Work at Source
===============
ggsci> dblogin useridalias SOURCEDB domain admin
--- Extract
ggsci> edit param EPPHII01
ggsci> register extract EPPHII01 database
ggsci> add extract EPPHII01, integrated tranlog, begin now
ggsci> add exttrail ./dirdat/t6, extract EPPHII01, megabytes 5
--- Pump
ggsci> edit param PPPHII01
ggsci> add extract PPPHII01, exttrailsource ./dirdat/t6 begin now
ggsci> add rmttrail ./dirdat/p6, extract PPPHII01, megabytes 5
--- Start Extract and Pump
ggsci> start EPPHII01
ggsci> start PPPHII01
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 29 17:24:29 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> col current_scn for 999999999999999999999999999999999
SQL> select current_scn from v$database;
CURRENT_SCN
----------------------------------
42177587523
SQL>
oracle@dcpdb2 [/home/oracle]$ cd /goldengate/dump_dir
oracle@dcpdb2 [/home/oracle]$ cd /goldengate/dump_dir
oracle@dcpdb2 [/goldengate/dump_dir]$ ls
oracle@dcpdb2 [/goldengate/dump_dir]$ nohup.out
sh: nohup.out: Execute permission denied.
oracle@dcpdb2 [/goldengate/dump_dir]$ > nohup.out
oracle@dcpdb2 [/goldengate/dump_dir]$
oracle@dcpdb2 [/goldengate/dump_dir]$ nohup ./export_fbnk_pr_h_instr_issued.sh &
[1] 3548
oracle@dcpdb2 [/goldengate/dump_dir]$ Sending output to nohup.out
oracle@dcpdb2 [/goldengate/dump_dir]$
oracle@dcpdb2 [/goldengate/dump_dir]$ tail -f nohup.out
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" tables=NBLT24.FBNK_PR_H_INSTR_ISSUED dumpfile=expFBNK_PR_H_INSTR_ISSUED.dmp logfile=expFBNK_PR_H_INSTR_ISSUED.log directory=gg_dump flashback_scn=42177587523
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 613.0 MB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "NBLT24"."FBNK_PR_H_INSTR_ISSUED" 527.7 MB 781793 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/goldengate/dump_dir/expFBNK_PR_H_INSTR_ISSUED.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Nov 29 17:44:51 2017 elapsed 0 00:05:27
[1] + Done(1) nohup ./export_fbnk_pr_h_instr_issued.sh &
oracle@dcpdb2 [/goldengate/dump_dir]$
oracle@dcpdb2 [/goldengate/dump_dir]$ scp expFBNK_PR_H_INSTR_ISSUED.dmp 172.31.10.19:/fra/dump_dir/
oracle@172.31.10.19's password:
expFBNK_PR_H_INSTR_ISSUED.dmp 100% 539MB 67.4MB/s 71.0MB/s 00:08
oracle@dcpdb2 [/goldengate/dump_dir]$
--====Target work==
[oracle@ggprod ~]$ cd /fra/dump_dir
[oracle@ggprod dump_dir]$ ls
expFBNK_ACCOUNT.dmp expFBNK_ACCOUNT.dmp.log expFBNK_PR_H_INSTR_ISSUED.dmp
[oracle@ggprod dump_dir]$
[oracle@ggprod dump_dir]$
impdp userid="'NBLT24/ggN24#12'" dumpfile=expFBNK_PR_H_INSTR_ISSUED.dmp logfile=expFBNK_PR_H_INSTR_ISSUED.log directory=GG_DUMP
[oracle@ggprod ~]$ cd /fra/dump_dir
[oracle@ggprod dump_dir]$ ls
expFBNK_ACCOUNT.dmp expFBNK_ACCOUNT.dmp.log expFBNK_PR_H_INSTR_ISSUED.dmp
[oracle@ggprod dump_dir]$
[oracle@ggprod dump_dir]$
[oracle@ggprod dump_dir]$ impdp userid="'NBLT24/ggN24#12'" dumpfile=expFBNK_PR_H_INSTR_ISSUED.dmp logfile=expFBNK_PR_H_INSTR_ISSUED.log directory=GG_DUMP
Import: Release 11.2.0.4.0 - Production on Wed Nov 29 18:07:48 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "NBLT24"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "NBLT24"."SYS_IMPORT_FULL_01": userid="NBLT24/********" dumpfile=expFBNK_PR_H_INSTR_ISSUED.dmp logfile=expFBNK_PR_H_INSTR_ISSUED.log directory=GG_DUMP
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "NBLT24"."FBNK_PR_H_INSTR_ISSUED" 527.7 MB 781793 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "NBLT24"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 29 18:14:40 2017 elapsed 0 00:06:51
[oracle@ggprod dump_dir]$
[oracle@ggprod dump_dir]$ cd
[oracle@ggprod ~]$ cd /scripts
[oracle@ggprod scripts]$ ls
add_data_file.sh create_fbnk_customer.sql create_fbnk_stmt_index.sh.bk import nohup.out.2 Opatch.lst
create_fbnk_account.sh create_fbnk_phii.sh delete_archive_log.sh initggprod.130817.hidden.ora nohup.out.bk tbs_create.sh
create_fbnk_account.sql create_fbnk_phii.sql F_HOLD_CONTROL_10FEB.sh initggprod.130817.ora nohup.out.fa tbs_stmt_index_create.sh
create_fbnk_customer.sh create_fbnk_stmt_index.sh F_HOLD_CONTROL_10FEB.sql nohup.out nohup.out.phii
[oracle@ggprod scripts]$
[oracle@ggprod scripts]$
[oracle@ggprod scripts]$ > nohup.out
[oracle@ggprod scripts]$
[oracle@ggprod scripts]$ nohup ./create_fbnk_phii.sh &
[1] 22267
[oracle@ggprod scripts]$ nohup: ignoring input and appending output to ‘nohup.out’
[oracle@ggprod scripts]$ tail -f nohup.out
Table created.
Elapsed: 00:00:19.50
Table altered.
Elapsed: 00:00:00.10
Table altered.
Elapsed: 00:00:11.63
^C[1]+ Done nohup ./create_fbnk_phii.sh
[oracle@ggprod scripts]$
--===
cat dirprm/esfaxx01.prm
--==========
--- Add Extract and Replicate
$ gg
ggsci> edit param ESPHII01
ggsci> dblogin USERIDALIAS GG_SOURCE DOMAIN admin
ggsci> register extract ESPHII01 database
ggsci> add extract ESPHII01, integrated tranlog, begin now
ggsci> add exttrail ./dirdat_S/s2, extract ESPHII01, megabytes 5
$ gg
ggsci> edit param RSPHII01
ggsci> dblogin useridalias GG_REPL domain admin
ggsci> add replicat RSPHII01, integrated, exttrail ./dirdat_S/s2
--- Start Extract and Replicate
ggsci> start ESPHII01
ggsci> start RSPHII01
ggsci> edit param RUPHII01
ggsci> dblogin USERIDALIAS TARGETDB DOMAIN admin
ggsci> add replicat RUPHII01, integrated, exttrail ./dirdat/p6
--- Start Replicate
ggsci> start RUPHII01
select count(1) from NBLT24.FBNK_PR_H_INSTR_ISSUED;
select count(1) from NBLT24.FBNK_PR_H_INSTR_ISSUED_TBL;
select username,expiry_date from dba_users where username='RMANBKP';
==========================*******************==============
=================Start New One=======================
F_HOLD_CONTROL_10FEB
EPFHCF01 -- Extract Process
PPFHCF01 -- Pump Process
RUFHCF01 -- Replicate Process
--===GoldenGate Extract Process === SOURCEDB==
F_HOLD_CONTROL_10FEB
EPFHCF01 -- Extract Process
oracle@dcpdb2 [/home/oracle]$ cd /goldengate/gg_home/12.2.0.1
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
HP/UX, IA64, 64bit (optimized), Oracle 11g on Dec 15 2015 16:01:27
Operating system character set identified as hp-roman8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (dcpdb2) 4>
GGSCI (dcpdb2) 4>
GGSCI (dcpdb2) 4>
--==First Step ===
dblogin useridalias SOURCEDB domain admin
add trandata NBLT24.F_HOLD_CONTROL_10FEB
--=============
GGSCI (dcpdb2) 4> dblogin useridalias SOURCEDB domain admin
Successfully logged into database.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 5>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 5> add trandata NBLT24.F_HOLD_CONTROL_10FEB
Logging of supplemental redo data enabled for table NBLT24.F_HOLD_CONTROL_10FEB.
TRANDATA for scheduling columns has been added on table 'NBLT24.F_HOLD_CONTROL_10FEB'.
TRANDATA for instantiation CSN has been added on table 'NBLT24.F_HOLD_CONTROL_10FEB'.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 6>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 6>
--=====Second Step=====
edit param EPFHCF01
EXTRACT EPFHCF01
USERIDALIAS SOURCEDB domain admin
EXTTRAIL ./dirdat/t7
TABLE NBLT24.F_HOLD_CONTROL_10FEB;
--==============
GGSCI (dcpdb2 as ggadmin@dcpdb1) 7> edit param EPFHCF01
EXTRACT EPFHCF01
USERIDALIAS SOURCEDB domain admin
EXTTRAIL ./dirdat/t7
TABLE NBLT24.F_HOLD_CONTROL_10FEB;
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"/goldengate/gg_home/12.2.0.1/dirprm/epfhcf01.prm" 5 lines, 108 characters
--====Third Step ==
dblogin useridalias SOURCEDB domain admin
register extract EPFHCF01 database
add extract EPFHCF01, integrated tranlog, begin now
add exttrail ./dirdat/t7, extract EPFHCF01, megabytes 5
start EPFHCF01
--===================
GGSCI (dcpdb2 as ggadmin@dcpdb1) 16> dblogin useridalias SOURCEDB domain admin
Successfully logged into database.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 17>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 17> register extract EPFHCF01 database
2017-10-03 11:43:01 INFO OGG-02003 Extract EPFHCF01 successfully registered with database at SCN 38807247714.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 18>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 18>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 19> add extract EPFHCF01, integrated tranlog, begin now
EXTRACT (Integrated) added.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 20>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 21> add exttrail ./dirdat/t7, extract EPFHCF01, megabytes 5
EXTTRAIL added.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 22>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 22> start EPFHCF01
Sending START request to MANAGER ...
EXTRACT EPFHCF01 starting
GGSCI (dcpdb2 as ggadmin@dcpdb1) 23>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 23> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EPFAXX01 00:00:02 00:00:04
EXTRACT RUNNING EPFCXX01 00:00:03 00:00:02
EXTRACT RUNNING EPFFTX01 00:00:03 00:00:04
EXTRACT RUNNING EPFHCF01 00:00:03 00:00:00
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$ ls -lrt dirdat/t7*
-rw-r----- 1 oracle oinstall 1348 Oct 3 11:46 dirdat/t7000000000
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$
--===GoldenGate Pump Process === SOURCEDB====
F_HOLD_CONTROL_10FEB
PPFHCF01 -- Pump Process
oracle@dcpdb2 [/home/oracle]$
oracle@dcpdb2 [/home/oracle]$ cd /goldengate/gg_home/12.2.0.1
oracle@dcpdb2 [/goldengate/gg_home/12.2.0.1]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
HP/UX, IA64, 64bit (optimized), Oracle 11g on Dec 15 2015 16:01:27
Operating system character set identified as hp-roman8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (dcpdb2) 1>
--===Fourth Step====
dblogin useridalias SOURCEDB domain admin
edit param PPFHCF01
EXTRACT PPFHCF01
USERIDALIAS SOURCEDB domain admin
RMTHOST 172.31.10.19, MGRPORT 7809
RMTTRAIL ./dirdat/p7
TABLE NBLT24.F_HOLD_CONTROL_10FEB;
--======
GGSCI (dcpdb2) 1> dblogin useridalias SOURCEDB domain admin
Successfully logged into database.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 2>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 3> edit param PPFHCF01
EXTRACT PPFHCF01
USERIDALIAS SOURCEDB domain admin
RMTHOST 172.31.10.19, MGRPORT 7809
RMTTRAIL ./dirdat/p7
TABLE NBLT24.F_HOLD_CONTROL_10FEB;
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"/goldengate/gg_home/12.2.0.1/dirprm/ppfhcf01.prm" 5 lines, 142 characters
GGSCI (dcpdb2 as ggadmin@dcpdb1) 12>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 12>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 12> view param PPFHCF01
EXTRACT PPFHCF01
USERIDALIAS SOURCEDB domain admin
RMTHOST 172.31.10.19, MGRPORT 7809
RMTTRAIL ./dirdat/p7
TABLE NBLT24.F_HOLD_CONTROL_10FEB;
GGSCI (dcpdb2 as ggadmin@dcpdb1) 13>
--===Fifth Step====
add extract PPFHCF01, exttrailsource ./dirdat/t7 begin now
add rmttrail ./dirdat/p7, extract PPFHCF01, megabytes 5
start PPFHCF01
--========================
GGSCI (dcpdb2 as ggadmin@dcpdb1) 13> add extract PPFHCF01, exttrailsource ./dirdat/t7 begin now
EXTRACT added.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 14>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 14> add rmttrail ./dirdat/p7, extract PPFHCF01, megabytes 5
RMTTRAIL added.
GGSCI (dcpdb2 as ggadmin@dcpdb1) 15>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 15>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 16> start PPFHCF01
Sending START request to MANAGER ...
EXTRACT PPFHCF01 starting
GGSCI (dcpdb2 as ggadmin@dcpdb1) 17>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 17>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 17>
GGSCI (dcpdb2 as ggadmin@dcpdb1) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EPFAXX01 00:00:02 00:00:00
EXTRACT RUNNING EPFCXX01 00:00:02 00:00:01
EXTRACT RUNNING EPFFTX01 00:00:02 00:00:02
EXTRACT RUNNING EPFHCF01 00:00:02 00:00:04
EXTRACT RUNNING EPFSEX01 00:00:02 00:00:09
EXTRACT RUNNING EPFTXX01 00:00:02 00:00:00
EXTRACT RUNNING EPPHII01 00:00:02 00:00:02
EXTRACT RUNNING PPFAXX01 00:00:00 00:00:01
EXTRACT RUNNING PPFCXX01 00:00:00 00:00:09
EXTRACT RUNNING PPFFTX01 00:00:00 00:00:01
EXTRACT RUNNING PPFHCF01 00:00:00 00:00:09
--===pump process trail logfile (p7) will generate in TARGET DB===
oracle@ggprod ~]$
[oracle@ggprod ~]$ cd /u02/gg_home/12.2.0.1
[oracle@ggprod 12.2.0.1]$
[oracle@ggprod 12.2.0.1]$ ls -lrt dirdat/p7*
-rw-r----- 1 oracle oinstall 0 Oct 3 12:47 dirdat/p7000000000
[oracle@ggprod 12.2.0.1]$
[oracle@ggprod 12.2.0.1]$
--===GoldenGate Replication Process === TARGETDB====
F_HOLD_CONTROL_10FEB
RUFHCF01 -- Replicate Process
--==6th step ==Check pump process log generating on TRAGET DB==
[oracle@ggprod ~]$
[oracle@ggprod ~]$ cd /u02/gg_home/12.2.0.1
[oracle@ggprod 12.2.0.1]$
[oracle@ggprod 12.2.0.1]$ ls -lrt dirdat/p7*
-rw-r----- 1 oracle oinstall 0 Oct 3 12:47 dirdat/p7000000000
[oracle@ggprod 12.2.0.1]$
[oracle@ggprod 12.2.0.1]$
--===7th Step==
dblogin useridalias TARGETDB domain admin
edit param RUFHCF01
REPLICAT RUFHCF01
USERIDALIAS TARGETDB DOMAIN admin
DISCARDFILE ./dirrpt/F_HOLD_CONTROL_10FEB.dsc, PURGE
ASSUMETARGETDEFS
MAP NBLT24.F_HOLD_CONTROL_10FEB, TARGET NBLT24.F_HOLD_CONTROL_10FEB;
=====
GGSCI (ggprod.nblbd.com) 6> dblogin useridalias TARGETDB domain admin
Successfully logged into database.
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 7>
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 7>
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 7> edit param RUFHCF01
REPLICAT RUFHCF01
USERIDALIAS TARGETDB DOMAIN admin
DISCARDFILE ./dirrpt/F_HOLD_CONTROL_10FEB.dsc, PURGE
ASSUMETARGETDEFS
MAP NBLT24.F_HOLD_CONTROL_10FEB, TARGET NBLT24.F_HOLD_CONTROL_10FEB;
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"/u02/gg_home/12.2.0.1/dirprm/rufhcf01.prm" 5L, 191C
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 9> view param RUFHCF01
REPLICAT RUFHCF01
USERIDALIAS TARGETDB DOMAIN admin
DISCARDFILE ./dirrpt/F_HOLD_CONTROL_10FEB.dsc, PURGE
ASSUMETARGETDEFS
MAP NBLT24.F_HOLD_CONTROL_10FEB, TARGET NBLT24.F_HOLD_CONTROL_10FEB;
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 10>
--===8th step ===
dblogin useridalias TARGETDB domain admin
add replicat RUFHCF01, integrated, exttrail ./dirdat/p7
--===================
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 12> dblogin useridalias TARGETDB domain admin
Successfully logged into database.
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 13>
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 13> add replicat RUFHCF01, integrated, exttrail ./dirdat/p7
REPLICAT (Integrated) added.
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 14>
GGSCI (ggprod.nblbd.com as ggadmin@ggprod) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ESFAXX01 00:26:15 349:09:00
REPLICAT RUNNING RSFAXX01 00:26:03 349:09:13
REPLICAT RUNNING RUFAXX01 00:00:08 00:00:07
REPLICAT RUNNING RUFCXX01 00:00:11 00:01:12
REPLICAT RUNNING RUFFTX01 00:00:10 00:00:03
REPLICAT STOPPED RUFHCF01 00:00:00 00:00:17
REPLICAT RUNNING RUFSEX01 00:00:09 00:00:01
REPLICAT RUNNING RUFTXX01 00:00:12 00:00:00
REPLICAT RUNNING RUPHII01 00:00:30 00:00:49
--==
Check the scn from the database.
sql> col current_scn for 999999999999999999999999999999999
sql> select current_scn from v$database
38814763486
expdp userid="' / as sysdba'" tables=NBLT24.F_HOLD_CONTROL_10FEB dumpfile=expF_HOLD_CONTROL_10FEB.dmp logfile=expF_HOLD_CONTROL_10FEB.log directory=GG_DUMP flashback_scn=38814763486
impdp userid="'NBLT24/ggN24#12'" dumpfile=expF_HOLD_CONTROL_10FEB.dmp logfile=F_HOLD_CONTROL_10FEB.log directory=GG_DUMP
start replicat RUFHCF01, aftercsn 38814763486
--==
oracle@dcpdb2 [/home/oracle]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 3 13:25:46 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
SQL> col current_scn for 999999999999999999999999999999999
SQL> select current_scn from v$database
2
SQL> select current_scn from v$database;
CURRENT_SCN
----------------------------------
38814763486
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
oracle@dcpdb2 [/home/oracle]$
oracle@dcpdb2 [/home/oracle]$
oracle@dcpdb2 [/home/oracle]$
oracle@dcpdb2 [/home/oracle]$ _HOLD_CONTROL_10FEB.log directory=GG_DUMP flashback_scn=38814763486 <
Export: Release 11.2.0.4.0 - Production on Tue Oct 3 13:27:16 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01": userid="/******** AS SYSDBA" tables=NBLT24.F_HOLD_CONTROL_10FEB dumpfile=expF_HOLD_CONTROL_10FEB.dmp logfile=expF_HOLD_CONTROL_10FEB.log directory=GG_DUMP flashback_scn=38814763486
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 120.1 MB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "NBLT24"."F_HOLD_CONTROL_10FEB" 192.0 MB 557852 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/goldengate/dump_dir/expF_HOLD_CONTROL_10FEB.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Oct 3 13:33:20 2017 elapsed 0 00:06:02
oracle@dcpdb2 [/home/oracle]$
oracle@dcpdb2 [/home/oracle]$ cd /goldengate
oracle@dcpdb2 [/goldengate]$ ll
total 4
drwxr-xr-x 2 oracle oinstall 1024 Oct 3 13:27 dump_dir
drwxr-xr-x 3 oracle oinstall 96 Jul 20 14:04 gg_home
drwxr-xr-x 3 oracle oinstall 1024 Jul 20 14:02 soft
oracle@dcpdb2 [/goldengate]$ cd dump_dir
oracle@dcpdb2 [/goldengate/dump_dir]$ ll
total 18781658
-rw-r----- 1 oracle asmadmin 517177344 Sep 17 15:44 expFBNK_PR_H_INSTR_ISSUED.dmp
-rw-r--r-- 1 oracle asmadmin 1782 Sep 17 15:44 expFBNK_PR_H_INSTR_ISSUED.log
-rw-r--r-- 1 oracle asmadmin 1247 Aug 8 12:57 expFBNK_STMT_ENTRY.log
-rw-r----- 1 oracle asmadmin 8885822071 Aug 8 12:57 expFBNK_STMT_ENTRY_01.dmp.gz
-rw-r----- 1 oracle asmadmin 213172224 Oct 3 13:33 expF_HOLD_CONTROL_10FEB.dmp
-rw-r--r-- 1 oracle asmadmin 1608 Oct 3 13:33 expF_HOLD_CONTROL_10FEB.log
-rwxr--r-- 1 oracle oinstall 443 Aug 7 16:57 export_4tables.sh
-rwxr--r-- 1 oracle oinstall 375 Jul 31 14:26 export_fbnk_account.sh
-rwxr--r-- 1 oracle oinstall 378 Jul 31 17:38 export_fbnk_customer.sh
-rwxr--r-- 1 oracle oinstall 417 Aug 8 11:42 export_fbnk_stmt_entry.sh
-rw------- 1 oracle oinstall 1240 Aug 8 12:57 nohup.out
oracle@dcpdb2 [/goldengate/dump_dir]$ scp expF_HOLD_CONTROL_10FEB.dmp oracle@172.31.100.66:/fra/dump_dir/
ssh: connect to host 172.31.100.66 port 22: Connection timed out
lost connection
oracle@dcpdb2 [/goldengate/dump_dir]$ scp expF_HOLD_CONTROL_10FEB.dmp oracle@172.31.10.19:/fra/dump_dir/
oracle@172.31.10.19's password:
expF_HOLD_CONTROL_10FEB.dmp 100% 203MB 50.8MB/s 66.5MB/s 00:04
oracle@dcpdb2 [/goldengate/dump_dir]$
--===
[oracle@ggprod dump_dir]$ impdp userid="'NBLT24/ggN24#12'" dumpfile=expF_HOLD_CONTROL_10FEB.dmp logfile=F_HOLD_CONTROL_10FEB.log directory=GG_DUMP
Import: Release 11.2.0.4.0 - Production on Tue Oct 3 14:08:28 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "NBLT24"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "NBLT24"."SYS_IMPORT_FULL_01": userid="NBLT24/********" dumpfile=expF_HOLD_CONTROL_10FEB.dmp logfile=F_HOLD_CONTROL_10FEB.log directory=GG_DUMP
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "NBLT24"."F_HOLD_CONTROL_10FEB" 192.0 MB 557852 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "NBLT24"."SYS_IMPORT_FULL_01" successfully completed at Tue Oct 3 14:10:08 2017 elapsed 0 00:01:38
[oracle@ggprod dump_dir]$
--====
[oracle@ggprod ~]$ cd /u02/gg_home/12.2.0.1
[oracle@ggprod 12.2.0.1]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (ggprod.nblbd.com) 1>
GGSCI (ggprod.nblbd.com) 2> start replicat RUFHCF01, aftercsn 38814763486
Sending START request to MANAGER ...
REPLICAT RUFHCF01 starting
GGSCI (ggprod.nblbd.com) 3>
GGSCI (ggprod.nblbd.com) 3>
select count(1) from NBLT24.F_HOLD_CONTROL_10FEB;
create undo tablespace UNDOTBS_NEW datafile 'C:\APP\TAREK\ORADATA\VIVA\UNDOTBS_NEW01.dbf' size 500M;
EXPDP with excluding STATISTICS,INDEX
expdp t24backup/**** DIRECTORY=dir_n1 dumpfile=$vdate%U.dmp logfile=$vdate.log schemas=testt24 parallel=15 EXCLUDE=STATISTICS,INDEX:\"IN \(\'IX_FBNK_STMT_ENTRY_C1\'\,\'IX_FBNK_STMT_ENTRY_C25\'\,\'IX_FBNK_STMT_ENTRY_C23\'\,\'IX_FBNK_CATEG_ENTRY\'\)\" CLUSTER=N metrics=y
expdp t24backup/*** DIRECTORY=dump_dir dumpfile=$vdate%U.dmp logfile=$vdate.log schemas=testt24 parallel=15 EXCLUDE=STATISTICS,INDEX:\"IN \(\'IX_FBNK_STMT_ENTRY_C1\'\,\'IX_FBNK_STMT_ENTRY_C25\'\,\'IX_FBNK_STMT_ENTRY_C23\'\,\'IX_FBNK_CATEG_ENTRY\'\)\" CLUSTER=N metrics=y
expdp t24backup/*** DIRECTORY=dump_dir dumpfile=$vdate%U.dmp logfile=$vdate.log schemas=testt24 parallel=15 EXCLUDE=STATISTICS,INDEX:\"IN \(\'IX_FBNK_STMT_ENTRY_C1\'\,\'IX_FBNK_STMT_ENTRY_C25\'\,\'IX_FBNK_STMT_ENTRY_C23\'\,\'IX_FBNK_CATEG_ENTRY\'\)\" CLUSTER=N metrics=y
RMAN Backup Job Session Kill
select b.sid, b.serial#, a.spid, b.client_info
from v$process a, v$session b
where a.addr=b.paddr and client_info like 'rman%';
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;
alter system kill session 'sid,serial#' immediate;
alter system kill session '1918,58173' immediate;
alter system kill session '3803,34441' immediate;
alter system kill session '6603,32737' immediate;
alter system kill session '29,50741' immediate;
from v$process a, v$session b
where a.addr=b.paddr and client_info like 'rman%';
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;
alter system kill session 'sid,serial#' immediate;
alter system kill session '1918,58173' immediate;
alter system kill session '3803,34441' immediate;
alter system kill session '6603,32737' immediate;
alter system kill session '29,50741' immediate;
Database server IP changes
1) Changes ip in tnsnames.ora file.
2) C:\Users\Administrator>sqlplus as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 20 13:49:54 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1670221824 bytes
Fixed Size 2176328 bytes
Variable Size 1275071160 bytes
Database Buffers 385875968 bytes
Redo Buffers 7098368 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 3728
Session ID: 5 Serial number: 5
SQL> startup mount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 20 13:52:26 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1670221824 bytes
Fixed Size 2176328 bytes
Variable Size 1275071160 bytes
Database Buffers 385875968 bytes
Redo Buffers 7098368 bytes
Database mounted.
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database open;
Database altered.
SQL>
2) C:\Users\Administrator>sqlplus as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 20 13:49:54 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1670221824 bytes
Fixed Size 2176328 bytes
Variable Size 1275071160 bytes
Database Buffers 385875968 bytes
Redo Buffers 7098368 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 3728
Session ID: 5 Serial number: 5
SQL> startup mount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 20 13:52:26 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1670221824 bytes
Fixed Size 2176328 bytes
Variable Size 1275071160 bytes
Database Buffers 385875968 bytes
Redo Buffers 7098368 bytes
Database mounted.
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database open;
Database altered.
SQL>
Monday, February 26, 2018
Wallet
create wallet
add credentialstore
alter credentialstore add user ggadmin@SOURCEDB alias SOURCEDB domain admin
alter credentialstore add user ggadmin@TARGETDB alias TARGETDB domain admin
info credentialstore domain admin
add credentialstore
alter credentialstore add user ggadmin@SOURCEDB alias SOURCEDB domain admin
alter credentialstore add user ggadmin@TARGETDB alias TARGETDB domain admin
info credentialstore domain admin
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
alter system set local_listener = '(address_list=(address=(protocol=tcp)(host=drp4db-vip.nblbd.com)(port=1521)))';
As soon as I set the value of local_listener, PMON will know the port number on which listener is running and it will dynamically register the instance with listener.
As soon as I set the value of local_listener, PMON will know the port number on which listener is running and it will dynamically register the instance with listener.
Dataguard SYNC Check
Primary:
SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PhyStdby:
SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PhyStdby:
SQL>select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;
PhyStdby:
SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" , almax-lhmax "Sequence Difference"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) lh
where al.thrd = lh.thrd;
SQL> select sequence#,thread#,applied from v$archived_log where applied in ('IN-MEMORY','NO');
SQL> SELECT PROCESS, THREAD#,SEQUENCE# STATUS FROM V$MANAGED_STANDBY;
SQL> select name, value from v$dataguard_stats where name like '%lag%';
SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PhyStdby:
SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PhyStdby:
SQL>select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;
PhyStdby:
SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" , almax-lhmax "Sequence Difference"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) lh
where al.thrd = lh.thrd;
SQL> select sequence#,thread#,applied from v$archived_log where applied in ('IN-MEMORY','NO');
SQL> SELECT PROCESS, THREAD#,SEQUENCE# STATUS FROM V$MANAGED_STANDBY;
SQL> select name, value from v$dataguard_stats where name like '%lag%';
Set DISPLAY environment variable
$ export DISPLAY=172.31.1.67:0.0
>> 172.31.1.67 : This is client PC IP.
>> 172.31.1.67 : This is client PC IP.
Saturday, February 17, 2018
Audit files delete manually
Linux
find . -maxdepth 1 -name "*.aud" -mtime +3 -type f -exec rm -v {} \;
find . -maxdepth 1 -name "*.xml" -mtime +3 -type f -exec rm -v {} \;
HP-UX
find . -name "*.aud" -mtime +3 -type f -exec rm -rf {} \;
find . -name "*.xml" -mtime +3 -type f -exec rm -rf {} \;
find . -maxdepth 1 -name "*.aud" -mtime +3 -type f -exec rm -v {} \;
find . -maxdepth 1 -name "*.xml" -mtime +3 -type f -exec rm -v {} \;
HP-UX
find . -name "*.aud" -mtime +3 -type f -exec rm -rf {} \;
find . -name "*.xml" -mtime +3 -type f -exec rm -rf {} \;
Subscribe to:
Posts (Atom)