FIG project queries

##### Service add & LOad Baclancing on Add Service #######

srvctl add service -s wcccdmt.farmersinsurance.com -r wcccdmtx1,wcccdmtx2,wcccdmtx3 -d wcccdmtx -failovermethod BASIC -failovertype SELECT

srvctl start service -s wcccdmt.farmersinsurance.com -d wcccdmtx

srvctl stop service -s wcccdmt.farmersinsurance.com -d wcccdmtx

srvctl status service -s wcccdmt.farmersinsurance.com -d wcccdmtx

srvctl config service -d wcccdmtx

srvctl config service -db wcccdmtx -service wcccdmt.farmersinsurance.com

srvctl modify service -db wcccdmtx -service wcccdmt.farmersinsurance.com -rlbgoal SERVICE_TIME -clbgoal SHORT

srvctl config service -db wcccdmtx -service wcccdmt.farmersinsurance.com

srvctl modify service -db odsprdx -service odsprd.farmersinsurance.com -modifyconfig -preferred odsprdx1,odsprdx2,odsprdx4

https://mikedietrichde.com/2023/05/24/choose-your-desired-time-zone-version-dst-upgrade/

 select value$ "TZ Version" from SYS.PROPS$ where name='DST_PRIMARY_TT_VERSION';

select value$ "TZ Version" from SYS.PROPS$ where name='DST_PRIMARY_TT_VERSION';

select value$ "TZ Version" from SYS.PROPS$ where name='DST_PRIMARY_TT_VERSION';

GG Param files

 mgr.prm

p2can1d7g@mlph638(4102) p2can1d73 /opt/app/p2can1d7/ggs/12.3.0.1.4/dirprm

$ more mgr.prm

PORT 7809

useridalias ggsuser

-- userid ggsuser@p2can1d7.db.att.com, password sh4d0wdb

LAGINFOMINUTES 0

LAGREPORTMINUTES 5

LAGCRITICALSECONDS 30

AUTORESTART EXTRACT E*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

AUTORESTART EXTRACT P*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

AUTORESTART REPLICAT R*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

PURGEOLDEXTRACTS /opt/app/p2can1d7/ggs/12.3.0.1.4/trails/*, USECHECKPOINTS, MINKEEPDAYS 5, FREQUENCYMINUTES 15

PURGEDDLHISTORY MINKEEPDAYS 14, MAXKEEPDAYS 21, FREQUENCYMINUTES 60

PURGEMARKERHISTORY MINKEEPDAYS 14, MAXKEEPDAYS 21, FREQUENCYMINUTES 60

ACCESSRULE,PROG *,IPADDR 135.213.168.192,ALLOW

======

Target side:

p2can1d7g@mlph638(4102) p2can1d73 /opt/app/p2can1d7/ggs/12.3.0.1.4/dirprm

$ more egcpuser.prm

extract EGCPUSER

setenv (ORACLE_SID="p2can1d73")

SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

--SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

useridalias ggsuser

--userid ggsuser@IPAG2PDB_DG2.DB.ATT.COM,  password sh4d0wdb

--userid ggsuser,  password sh4d0wdb

-- userid ggsuser@p2can1d7.db.att.com, password sh4d0wdb

--userid ggsuser@P2CAN1D7.DB.ATT.COM,  password sh4d0wdb

--TRANLOGOPTIONS ASMUSER "x@", ASMPASSWORD x, FORCEASM

--tranlogoptions dblogreader

--tranlogoptions ALTARCHIVEDLOGFORMAT thread_3_seq_%s.%T.%R threadid 4

--tranlogoptions ALTARCHIVEDLOGFORMAT thread_4_seq_%s.1394.1118697501 threadid 3

--tranlogoptions ALTARCHIVEDLOGFORMAT thread_%t_seq_%s.%T.%R threadid 2

--tranlogoptions ALTARCHIVEDLOGFORMAT thread_%t_seq_%s.%T.%R threadid 1

BR BRDIR /opt/app/p2can1d7/ggs/12.3.0.1.4/BR

DISCARDFILE /opt/app/p2can1d7/ggs/12.3.0.1.4/dirrpt/egcpuser.dsc, APPEND, MEGABYTES 100

--FETCHOPTIONS USELATESTVERSION, NOUSESNAPSHOT, MISSINGROW REPORT, SUPPRESSDUPLICATES

--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 10000

--CACHEMGR CACHESIZE 4G

--GETREPLICATES

-- local trail when using data pump

exttrail /opt/app/p2can1d7/ggs/12.3.0.1.4/trails/p2can1d7/lt

--exttrail /orabkup/fs01/extract1/lt

TABLE GCP_USER.GCP_CARD_MV_STAG_TAB;

TABLE GCP_USER.GCP_CIRCUITCIRCUIT_MV_STAG_TAB;

TABLE GCP_USER.GCP_CIRCUITLINK_MV_STAG_TAB;

TABLE GCP_USER.GCP_CIRCUIT_MV_STAG_TAB;

TABLE GCP_USER.GCP_CLOUDOBJECTS_MV_STAG_TAB;

TABLE GCP_USER.GCP_CLOUD_MV_STAG_TAB;

--TABLE GCP_USER.GCP_CPORT_MV_STAG_TAB;

TABLE GCP_USER.GCP_DOCOBJ_MV_STAG_TAB;

TABLE GCP_USER.GCP_DOCUMENT_MV_STAG_TAB;

TABLE GCP_USER.GCP_DS1TIMESLOT_MV_STAG_TAB;

TABLE GCP_USER.GCP_HOMINGHUBBING_MV_STAG_TAB;

TABLE GCP_USER.GCP_LINK_MV_STAG_TAB;

TABLE GCP_USER.GCP_LOCATION_MV_STAG_TAB;

--TABLE GCP_USER.GCP_LPORT_MV_STAG_TAB;

TABLE GCP_USER.GCP_NODECLFI_MV_STAG_TAB;

TABLE GCP_USER.GCP_NODE_MV_STAG_TAB;

TABLE GCP_USER.GCP_NUMBEROBJECTS_MV_STAG_TAB;

TABLE GCP_USER.GCP_NUMBER_MV_STAG_TAB;

TABLE GCP_USER.GCP_SERVICEOBJECTS_MV_STAG_TAB;

--TABLE GCP_USER.GCP_SERVICE_MV_STAG_TAB;

TABLE GCP_USER.GCP_SHELF_MV_STAG_TAB;

TABLE GCP_USER.GCP_SLOT_MV_STAG_TAB;

TABLE GCP_USER.GCP_SUBLOC_MV_STAG_TAB;

--TABLE GCP_USER.GCP_SUBSCRIBER_MV_STAG_TAB;

TABLE GCP_USER.GCP_TOPOBJ_MV_STAG_TAB;

TABLE GCP_USER.GCP_TOPOLOGY_MV_STAG_TAB;

--TABLE GCP_USER.GCP_TPORT_MV_STAG_TAB;

TABLE GCP_USER.GCP_EMS_MV_STAG_TAB;

TABLE GCP_USER.GCP_EMSOBJECT_MV_STAG_TAB;

---MV table added on JUNE292019--

TABLE GCP_USER.GCP_TPORT_SDN_MV_STAG_TAB;

TABLE GCP_USER.GCP_LPORT_SDN_MV_STAG_TAB;

TABLE GCP_USER.GCP_CPORT_SDN_MV_STAG_TAB;

TABLE GCP_USER.GCP_CPORT_MV_STAG_TAB, COLSEXCEPT (ORIGPARENTPORT,PTNIIPORTNAME);

TABLE GCP_USER.GCP_LPORT_MV_STAG_TAB, COLSEXCEPT (ORIGPARENTPORT,PTNIIPORTNAME);

TABLE GCP_USER.GCP_TPORT_MV_STAG_TAB, COLSEXCEPT (ORIGPARENTPORT,PTNIIPORTNAME);

--TABLE GCP_USER.GCP_SERVICE_MV_STAG_TAB, COLSEXCEPT (CUSTOMERUNITYPE);

TABLE GCP_USER.GCP_SUBSCRIBER_MV_STAG_TAB, COLSEXCEPT (CUSID);

TABLE GCP_USER.GCP_SERVICE_MV_STAG_TAB, COLSEXCEPT (CUSTOMERUNITYPE,ORIGCUSTOMERCIRCUITREFERENCE);

Source :

$ more ecramer.prm

-- ext group

extract ecramer

CACHEMGR CACHESIZE 2G

-- DB login (must use TNS string)

getenv (ORACLE_SID)

SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

userid ggsuser@p1bbn1d9.db.att.com, password AACAAAAAAAAAAAJANCYBLDPELJKALCBIRCDDPELASDJETACE encryptkey default

-- ASM login

--TRANLOGOPTIONS ASMUSER "x@", ASMPASSWORD x, FORCEASM

tranlogoptions dblogreader

BR BRDIR /opt/app/p1bbn1d9/ggs/BR

FETCHOPTIONS USELATESTVERSION, NOUSESNAPSHOT, MISSINGROW REPORT, SUPPRESSDUPLICATES, FETCHPKUPDATECOLS

-- trace when needed

-- TLTRACE, DEBUG

-- TRACE, DDLINCLUDE DDLTRACE.log

-- end trace

-- for RAC (in millisec)

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 10000

--THREADOPTIONS PROCESSTHREADS EXCEPT 5

-- warn long trans

WARNLONGTRANS 3H SKIPEMPTYTRANS CHECKINTERVAL 30M

--TRANLOGOPTIONS _CONVERTBADNUMBER ZERO

tranlogoptions _NOREADAHEAD CURRENT

--TRANLOGOPTIONS EXCLUDEUSER PURGE_USER

--TRANLOGOPTIONS _INFINITYTOZERO

--controls how often extract checks for new data after

--it has reached end of the current data in its data source

--can reduce system I/O...default is 1 second.

--EOFDELAY 2

--

-- DDL support -- begin

--DDL INCLUDE mapped objtype 'TABLE'

--EXCLUDE OBJNAME <schema>.<table_name>

DDL INCLUDE MAPPED, EXCLUDE OBJTYPE 'TRIGGER'

--DDL INCLUDE MAPPED EXCLUDE OBJNAME CRAMER.ET$07CB4C760001

DDLERROR SKIPTRIGGERERROR 10

--DDLERROR restartskip

--DDLERROR _SKIPDDL 1

--DDLERROR RESTARTSKIP 1

DDLOPTIONS REPORT

--DDLOPTIONS ADDTRANDATA

DDLOPTIONS ADDTRANDATA RETRYOP MAXRETRIES 10 RETRYDELAY 10

-- DDL support -- end

-- local trail when using data pump

exttrail /opt/app/p1bbn1d9/ggs/trails/lt

-- specify fetch option

--FETCHOPTIONS FETCHPKUPDATECOLS

-- specify tables to be captured

TABLE CRAMER.*;

-- list changed on 2020/02/29 - Vu Nguyen specification

-- specfied table to be excluded

TABLEEXCLUDE CRAMER.AICROLE_MINTROLE_MAPPING;

TABLEEXCLUDE CRAMER.AIC_CARD_BKP;

TABLEEXCLUDE CRAMER.AIC_FEDERATION_LOG;

-- Vu exclude 20200421 rl9783

TABLEEXCLUDE CRAMER.CHANGE_NTI_ORDER;

TABLEEXCLUDE CRAMER.MINTCACHELOG;

TABLEEXCLUDE CRAMER.FAULTYNDCNODE;

Source:

$ more pcramer.prm

-- identify group

extract pcramer

passthru

-- specify source DB login

-- when using passthru, no need to specify user/pass.

-- userid ggsuser@IPAG2PDB.DB.ATT.COM, password goldengate4u

-- specify remote box

RMTHOST mlpd367.sfdc.sbc.com,  mgrport 7809 COMPRESS TCPBUFSIZE 200000, TCPFLUSHBYTES 200000

-- specify remote trail (on target system)

RMTTRAIL  /opt/app/ggs/trails/pt

-- specify tables to be captured

TABLE CRAMER.*;

TABLE MDNE.*;

-- DDL support -- begin

--

-- DDLOPTIONS REPORT

-- DDL support - end

Source:  from this path : /opt/app/p1bbn1d9/ggs/12.3.0.1.4/dirprm

$ more ecramer.prm

-- ext group

extract ecramer

CACHEMGR CACHESIZE 2G

-- DB login (must use TNS string)

getenv (ORACLE_SID)

SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

useridalias ggsuser

-- ASM login

--TRANLOGOPTIONS ASMUSER "x@", ASMPASSWORD x, FORCEASM

--tranlogoptions dblogreader

BR BRDIR /opt/app/p1bbn1d9/ggs/12.3.0.1.4/BR

--FETCHOPTIONS USELATESTVERSION, NOUSESNAPSHOT, MISSINGROW REPORT, SUPPRESSDUPLICATES, FETCHPKUPDATECOLS

-- trace when needed

-- TLTRACE, DEBUG

-- TRACE, DDLINCLUDE DDLTRACE.log

-- end trace

-- for RAC (in millisec)

--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 10000

--THREADOPTIONS PROCESSTHREADS EXCEPT 5

-- warn long trans

WARNLONGTRANS 3H SKIPEMPTYTRANS CHECKINTERVAL 30M

--TRANLOGOPTIONS _CONVERTBADNUMBER ZERO

tranlogoptions _NOREADAHEAD CURRENT

--TRANLOGOPTIONS EXCLUDEUSER PURGE_USER

--TRANLOGOPTIONS _INFINITYTOZERO

--controls how often extract checks for new data after

--it has reached end of the current data in its data source

--can reduce system I/O...default is 1 second.

--EOFDELAY 2

--

-- DDL support -- begin

--DDL INCLUDE mapped objtype 'TABLE'

--EXCLUDE OBJNAME <schema>.<table_name>

DDL INCLUDE MAPPED, EXCLUDE OBJTYPE 'TRIGGER'

--DDL INCLUDE MAPPED EXCLUDE OBJNAME CRAMER.ET$07CB4C760001

DDLERROR SKIPTRIGGERERROR 10

--DDLERROR restartskip

--DDLERROR _SKIPDDL 1

--DDLERROR RESTARTSKIP 1

DDLOPTIONS REPORT

--DDLOPTIONS ADDTRANDATA

DDLOPTIONS ADDTRANDATA RETRYOP MAXRETRIES 10 RETRYDELAY 10

-- DDL support -- end

-- local trail when using data pump

exttrail /opt/app/p1bbn1d9/ggs/trails/le

--exttrail /opt/app/p1bbn1d9/ggs/trails/lt

-- specify fetch option

--FETCHOPTIONS FETCHPKUPDATECOLS

-- specify tables to be captured

TABLE CRAMER.*;

-- list changed on 2020/02/29 - Vu Nguyen specification

-- specfied table to be excluded

TABLEEXCLUDE CRAMER.AICROLE_MINTROLE_MAPPING;

TABLEEXCLUDE CRAMER.AIC_CARD_BKP;

TABLEEXCLUDE CRAMER.AIC_FEDERATION_LOG;

TABLEEXCLUDE CRAMER.AIC_LOCATION_BKP;

TABLEEXCLUDE CRAMER.AIC_MINT_NODE;

TABLEEXCLUDE CRAMER.AIC_NODE_BKP;

Source:   from this path : /opt/app/p1bbn1d9/ggs/12.3.0.1.4/dirprm

$ more pmlph636.prm

-- identify group

extract pmlph636

passthru

-- specify source DB login

-- when using passthru, no need to specify user/pass.

-- userid ggsuser@IPAG2PDB.DB.ATT.COM, password goldengate4u

-- specify remote box

--RMTHOST MLPH636.sfdc.sbc.com,  mgrport 7809 COMPRESS TCPBUFSIZE 200000, TCPFLUSHBYTES 200000

RMTHOST MLPH638.sfdc.sbc.com,  mgrport 7809 COMPRESS TCPBUFSIZE 200000, TCPFLUSHBYTES 200000

-- specify remote trail (on target system)

RMTTRAIL /opt/app/p2can1d7/ggs/12.3.0.1.4/trails/p1bbn1d9/re

--RMTTRAIL /opt/app/p2can1d7/ggs/12.3.0.1.4/trails/p1bbn1d9/rt

-- specify tables to be captured

TABLE CRAMER.*;

TABLE MDNE.*;

-- DDL support -- begin

--

-- DDLOPTIONS REPORT

-- DDL support - end

$ more mgr.prm_upgrade

PORT 7809

-- userid ggsuser@P1BBN1D92.db.att.com, password manager123

userid ggsuser@P1BBN1D92.db.att.com, password AACAAAAAAAAAAAJANCYBLDPELJKALCBIRCDDPELASDJETACE encryptkey default

LAGINFOMINUTES 0

-- specify the interval at which Manager checks for Extract and Replicat lag

--LAGREPORTMINUTES 60

LAGREPORTMINUTES 15

-- specify a lag threshold that is considered critical, and to force a warning

-- message to the error log when the threshold is reached

LAGCRITICALMINUTES 30

-- Control messages being sent to SYSLOG

SYSLOG NONE

---AUTO START OF PROCESSES

--AUTOSTART EXTRACT E*

--AUTOSTART EXTRACT P*

--AUTORESTART EXTRACT E*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

--AUTORESTART EXTRACT P*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

PURGEOLDEXTRACTS /opt/app/p1bbn1d9/ggs/trails/*, USECHECKPOINTS, MINKEEPDAYS 4, FREQUENCYMINUTES 15

PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 14, FREQUENCYMINUTES 30

PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 14, FREQUENCYMINUTES 30

$ more mgr.prm

PORT 7809

useridalias ggsuser

LAGINFOMINUTES 0

-- specify the interval at which Manager checks for Extract and Replicat lag

--LAGREPORTMINUTES 60

LAGREPORTMINUTES 15

-- specify a lag threshold that is considered critical, and to force a warning

-- message to the error log when the threshold is reached

LAGCRITICALMINUTES 30

-- Control messages being sent to SYSLOG

--SYSLOG NONE

---AUTO START OF PROCESSES

AUTOSTART EXTRACT E*

AUTOSTART EXTRACT P*

AUTORESTART EXTRACT E*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

AUTORESTART EXTRACT P*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

PURGEOLDEXTRACTS /opt/app/p1bbn1d9/ggs/trails/*, USECHECKPOINTS, MINKEEPDAYS 4, FREQUENCYMINUTES 15

PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 14, FREQUENCYMINUTES 30

PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 14, FREQUENCYMINUTES 30

$

$

$ more ecramer.prm.20230920

-- ext group

extract ecramer

CACHEMGR CACHESIZE 2G

-- DB login (must use TNS string)

getenv (ORACLE_SID)

SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

useridalias ggsuser

-- ASM login

--TRANLOGOPTIONS ASMUSER "x@", ASMPASSWORD x, FORCEASM

--tranlogoptions dblogreader

BR BRDIR /opt/app/p1bbn1d9/ggs/12.3.0.1.4/BR

--FETCHOPTIONS USELATESTVERSION, NOUSESNAPSHOT, MISSINGROW REPORT, SUPPRESSDUPLICATES, FETCHPKUPDATECOLS

-- trace when needed

-- TLTRACE, DEBUG

-- TRACE, DDLINCLUDE DDLTRACE.log

-- end trace

-- for RAC (in millisec)

--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 10000

--THREADOPTIONS PROCESSTHREADS EXCEPT 5

-- warn long trans

WARNLONGTRANS 3H SKIPEMPTYTRANS CHECKINTERVAL 30M

--TRANLOGOPTIONS _CONVERTBADNUMBER ZERO

tranlogoptions _NOREADAHEAD CURRENT

--TRANLOGOPTIONS EXCLUDEUSER PURGE_USER

--TRANLOGOPTIONS _INFINITYTOZERO

--controls how often extract checks for new data after

--it has reached end of the current data in its data source

--can reduce system I/O...default is 1 second.

--EOFDELAY 2

--

-- DDL support -- begin

--DDL INCLUDE mapped objtype 'TABLE'

--EXCLUDE OBJNAME <schema>.<table_name>

DDL INCLUDE MAPPED, EXCLUDE OBJTYPE 'TRIGGER'

--DDL INCLUDE MAPPED EXCLUDE OBJNAME CRAMER.ET$07CB4C760001

DDLERROR SKIPTRIGGERERROR 10

--DDLERROR restartskip

--DDLERROR _SKIPDDL 1

--DDLERROR RESTARTSKIP 1

DDLOPTIONS REPORT

--DDLOPTIONS ADDTRANDATA

DDLOPTIONS ADDTRANDATA RETRYOP MAXRETRIES 10 RETRYDELAY 10

-- DDL support -- end

-- local trail when using data pump

exttrail /opt/app/p1bbn1d9/ggs/trails/lt

-- specify fetch option

--FETCHOPTIONS FETCHPKUPDATECOLS

-- specify tables to be captured

TABLE CRAMER.*;

-- list changed on 2020/02/29 - Vu Nguyen specification

-- specfied table to be excluded

TABLEEXCLUDE CRAMER.AICROLE_MINTROLE_MAPPING;

TABLEEXCLUDE CRAMER.AIC_CARD_BKP;

TABLEEXCLUDE CRAMER.AIC_FEDERATION_LOG;

TABLEEXCLUDE CRAMER.AIC_LOCATION_BKP;

TABLEEXCLUDE CRAMER.AIC_MINT_NODE;

TABLEEXCLUDE CRAMER.AIC_NODE_BKP;

TABLEEXCLUDE CRAMER.AIC_PORT_BKP;

TABLEEXCLUDE CRAMER.AIC_RACK_BKP;

TABLEEXCLUDE CRAMER.ALLOCATEDLOCK;

$

Target Side:

p2can1d7g@mlph638(4105) p2can1d73 /opt/app/p2can1d7/ggs/12.3.0.1.4/dirprm

$ more rcramer1.prm

-- rep group

replicat  rcramer1

SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

useridalias ggsuser

-- userid ggsuser@p2can1d7.db.att.com,  password sh4d0wdb

assumetargetdefs

--DBOPTIONS DEFERREFCONST

HANDLECOLLISIONS

OVERRIDEDUPS

APPLYNOOPUPDATES

--ALLOWNOOPUPDATES

--BATCHSQL

--GROUPTRANSOPS 1

--MAXTRANSOPS 1

--grouptransops 1000

--maxtransops 1000

--Added by Prashant as suggest Oracle support date 06DEC2018

batchsql BATCHESPERQUEUE 200 OPSPERBATCH 2000

map CRAMER.IDIS_PORT_STAGING, target CRAMER.IDIS_PORT_STAGING, REPERROR (0, DISCARD);

map CRAMER.IDIS_CARD_STAGING, target CRAMER.IDIS_CARD_STAGING, REPERROR (0, DISCARD);

map CRAMER.IDIS_BULKPORTRES_UPDATE, target CRAMER.IDIS_BULKPORTRES_UPDATE, REPERROR (0, DISCARD);

map CRAMER.IDIS_IDF_SAREA_DEVICES, target CRAMER.IDIS_IDF_SAREA_DEVICES, REPERROR (0, DISCARD);

map CRAMER.IDIS_IDF_SAREA_DEVICES, target CRAMER.IDIS_IDF_SAREA_DEVICES;

map CRAMER.FAULTYNDCNODE , target CRAMER.FAULTYNDCNODE , REPERROR (0, DISCARD);

map CRAMER.STATICROUTEPORTMAPPING, target CRAMER.STATICROUTEPORTMAPPING, REPERROR (0, DISCARD);

map CRAMER.IDIS_BULKPORTRES_CHANGEPORT, target CRAMER.IDIS_BULKPORTRES_CHANGEPORT, REPERROR (0, DISCARD);

map CRAMER.EXT_CARD_DPP_EMUX, target CRAMER.EXT_CARD_DPP_EMUX, REPERROR (0, DISCARD);

--map CRAMER.EXT_PORT, target CRAMER.EXT_PORT, REPERROR (0, DISCARD);

--map CRAMER.IDIS_DEVICE_PROPERTIES, target CRAMER.IDIS_DEVICE_PROPERTIES, REPERROR (0, DISCARD);

--discardfile /opt/app/p2can1d7/ggs/11.2.1.0.3/dirrpt/rcramer1.dsc,  append,  megabytes 20000

discardrollover at 01:00

-- map PKs

-- end map PKs

DDLERROR 1452 IGNORE

DDLERROR 957  IGNORE

REPERROR (1400, DISCARD)

REPERROR (1403, DISCARD)

--REPERROR (12899, DISCARD)

--REPERROR (1722, DISCARD)

REPERROR (1, DISCARD)

REPERROR (0, DISCARD)

REPERROR (2291, DISCARD)

REPERROR (2292, DISCARD)

--begin ddl support--

ddloptions report

DDLOPTIONS _USEOWNERFORSESSION

--DDLOPTIONS _USELOGINSCHEMA

--DDL INCLUDE MAPPED , EXCLUDE ALL INSTR 'GRANT'

DDLERROR 1430 IGNORE INCLUDE OPTYPE ALTER OBJTYPE SESSION

DDLERROR 29553 IGNORE

--Ignore By Vikas on July 3 2020 - NODE_TEMP_DEL/LINK_TEMP_DEL_1_IDX

--DDLERROR 1452 IGNORE

--

DDLERROR 4021 IGNORE

DDLERROR 04021 IGNORE

DDLERROR 972 IGNORE

DDLERROR 1722 IGNORE

DDLERROR 2149 IGNORE

DDLERROR 10636 IGNORE

DDLERROR 01407 IGNORE

DDLERROR 4043 IGNORE

DDLERROR 01722 IGNORE

DDLERROR 12003 IGNORE

DDLERROR 4063 IGNORE

DDLERROR 1434 IGNORE

DDLERROR 8181 IGNORE

DDLERROR 29509 IGNORE

DDLERROR 29507 IGNORE

DDLERROR 2430 IGNORE

DDLERROR 903 IGNORE

DDLERROR 1775 IGNORE

DDLERROR 2270 IGNORE

DDLERROR 24344 IGNORE

DDLERROR 1466 IGNORE

DDLERROR 1430 IGNORE

DDLERROR 955 IGNORE

DDLERROR 1765 IGNORE

DDLERROR 30568 IGNORE

DDLERROR 995 IGNORE

DDLERROR 942 IGNORE

DDLERROR 2260 IGNORE

DDLERROR 2275 IGNORE

DDLERROR 29506 IGNORE

DDLERROR 29913 IGNORE

DDLERROR 6545 IGNORE

DDLERROR 2299 IGNORE

DDLERROR 4042 IGNORE

DDLERROR 1917 IGNORE

DDLERROR 1471 IGNORE

DDLERROR 933 IGNORE

DDLERROR 1031 IGNORE

DDLERROR 1418 IGNORE

--DDLERROR 1306 IGNORE

DDLERROR 2431 IGNORE

DDLERROR 2297 IGNORE

DDLERROR 2429 IGNORE

DDLERROR 22930 IGNORE

DDLERROR 2443 IGNORE

DDLERROR 2291 IGNORE

DDLERROR 904 IGNORE

--DDLERROR 29507 IGNORE

--DDL include mapped

DDL INCLUDE MAPPED, EXCLUDE OBJTYPE 'TRIGGER', EXCLUDE OBJTYPE 'PUBLICSYNONYM' , EXCLUDE OPTYPE GRANT , EXCLUDE OBJTYPE 'JAVA'

--DDL INCLUDE MAPPED, EXCLUDE OBJTYPE 'TRIGGER', EXCLUDE INSTRWORDS 'ALTER PACKAGE'

MAPEXCLUDE CRAMER.SCRIPT_LOGS;

MAPEXCLUDE CRAMER.CABS_CIR_USAGE_TABLE;

-- Navin 12/04/2017

MAPEXCLUDE CRAMER.NDC_TEMP_IUCS_CSV;

MAPEXCLUDE CRAMER.ENODED_SIAD_COMPLETE;

MAPEXCLUDE CRAMER.EXT_LINK_FIBER;

MAPEXCLUDE CRAMER.SYNCDIFFDEPENDENCY;

MAPEXCLUDE CRAMER.EXT_NODE_CN8700_SERIES;

--Navin 11-Jun-18

MAPEXCLUDE CRAMER.SBC_PGW_RELATION_LOOKUP;

-- Tejas 10/18/2016

MAPEXCLUDE CRAMER.MV_RESPORT;

--- Tejas Tejas01/152018

MAPEXCLUDE CRAMER.EMS_SVLANINFO;

MAPEXCLUDE CRAMER.DELETIONLOCK;

--end ddl support--

--Vikas Feb262018---As checked CRAMER.AIC_FEDERATION_LOG_LOG table not exist in source side , did the uncomment it Prashant Aug242018

MAPEXCLUDE CRAMER.NDC_D1_NODES_LOG;

MAPEXCLUDE CRAMER.NDC_USP_NODES_BACKUP;

MAPEXCLUDE CRAMER.AIC_FEDERATION_LOG_LOG;

MAPEXCLUDE CRAMER.AIC_FEDERATION_LOG;

--Excluded 20231207

MAPEXCLUDE CRAMER.UNI_EVC_100G;

--Excluded per Vu 20241612

MAPEXCLUDE CRAMER.LAGEXHAUSTREPORT;

--Replicat all CRAMER tables

map CRAMER.IDIS_ABF_DEVICE_EXTRACT, target CRAMER.IDIS_ABF_DEVICE_EXTRACT keycols (EQUIPMENT_CLLI);

map CRAMER.LAGEXHAUSTREPORT, target CRAMER.LAGEXHAUSTREPORT keycols (LAG_NAME,FREE_VLAN_COUNT,USED_PERCENTAGE,LAGREPLACED_FLAG,ALTERNATE_LAG);

map CRAMER.*,  target CRAMER.*;

$ more mgr.prm

PORT 7809

useridalias ggsuser

-- userid ggsuser@p2can1d7.db.att.com, password sh4d0wdb

LAGINFOMINUTES 0

LAGREPORTMINUTES 5

LAGCRITICALSECONDS 30

AUTORESTART EXTRACT E*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

AUTORESTART EXTRACT P*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

AUTORESTART REPLICAT R*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

PURGEOLDEXTRACTS /opt/app/p2can1d7/ggs/12.3.0.1.4/trails/*, USECHECKPOINTS, MINKEEPDAYS 5, FREQUENCYMINUTES 15

PURGEDDLHISTORY MINKEEPDAYS 14, MAXKEEPDAYS 21, FREQUENCYMINUTES 60

PURGEMARKERHISTORY MINKEEPDAYS 14, MAXKEEPDAYS 21, FREQUENCYMINUTES 60

ACCESSRULE,PROG *,IPADDR 135.213.168.192,ALLOW

p2can1d7g@mlph638(4109) p2can1d73 /opt/app/p2can1d7/ggs/12.3.0.1.4/dirprm

$ more rcramer2.prm

-- rep group

replicat  rcramer2

SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

useridalias ggsuser

-- userid ggsuser@p2can1d7.db.att.com,  password sh4d0wdb

assumetargetdefs

--DBOPTIONS DEFERREFCONST

HANDLECOLLISIONS

OVERRIDEDUPS

APPLYNOOPUPDATES

BATCHSQL

--grouptransops 1000

--maxtransops 1000

--discardfile /opt/app/p2can1d7/ggs/11.2.1.0.3/dirrpt/rcramer2.dsc,  append,  megabytes 200

discardrollover at 01:00

-- map PKs

-- end map PKs

REPERROR (1400, DISCARD)

REPERROR (1403, DISCARD)

REPERROR (1, DISCARD)

REPERROR (0, DISCARD)

--begin ddl support--

ddloptions report

DDLOPTIONS _USEOWNERFORSESSION

DDLERROR 4043 IGNORE

DDLERROR 2430 IGNORE

DDLERROR 24344 IGNORE

DDLERROR 1466 IGNORE

DDLERROR 955 IGNORE

DDLERROR 942 IGNORE

DDLERROR 2260 IGNORE

DDLERROR 2275 IGNORE

DDLERROR 29506 IGNORE

DDLERROR 29913 IGNORE

DDLERROR 6545 IGNORE

DDLERROR 2299 IGNORE

DDLERROR 4042 IGNORE

DDLERROR 1917 IGNORE

DDLERROR 1471 IGNORE

DDLERROR 933 IGNORE

--DDLERROR 1031 IGNORE

DDLERROR 1418 IGNORE

--DDLERROR 1306 IGNORE

DDLERROR 2431 IGNORE

DDLERROR 2297 IGNORE

DDLERROR 2429 IGNORE

DDLERROR 22930 IGNORE

DDLERROR 2443 IGNORE

--DDL include mapped

--DDL INCLUDE MAPPED, EXCLUDE OBJTYPE 'TRIGGER', EXCLUDE OPTYPE GRANT

DDL INCLUDE MAPPED, EXCLUDE OBJTYPE 'TRIGGER'

--end ddl support--

--Exclude table

MAPEXCLUDE CRAMER.EXT_NODE_COMMON

--Replicat all CRAMER tables

map CRAMER.CARDINSLOT,  target CRAMER.CARDINSLOT;

--map CRAMER.CIRCUITCIRCUIT,  target CRAMER.CIRCUITCIRCUIT;

map CRAMER.CIRCUITTYPE_M,  target CRAMER.CIRCUITTYPE_M;

map CRAMER.CLOUD,  target CRAMER.CLOUD;

map CRAMER.EXT_CARD_DPC,  target CRAMER.EXT_CARD_DPC;

map CRAMER.EXT_CIRCUIT_FEDERATEDCIRCUIT, target CRAMER.EXT_CIRCUIT_FEDERATEDCIRCUIT;

map CRAMER.EXT_CIRCUIT_TIRKS_BEARER, target CRAMER.EXT_CIRCUIT_TIRKS_BEARER;

map CRAMER.EXT_LSOMAPPING, target CRAMER.EXT_LSOMAPPING;

map CRAMER.EXT_SERVICE_IPAG_UNI, target CRAMER.EXT_SERVICE_IPAG_UNI;

map CRAMER.LINKCIRCUIT, target CRAMER.LINKCIRCUIT;

map CRAMER.LSOMAPPING, target CRAMER.LSOMAPPING;

map CRAMER.NODETYPE_M, target CRAMER.NODETYPE_M;

map CRAMER.SERVICETYPE_M, target CRAMER.SERVICETYPE_M;

map CRAMER.SLOT, target CRAMER.SLOT;

map CRAMER.TOPOLOGY, target CRAMER.TOPOLOGY;

--Mergers of other replicats RALLLCK1 ,RCARD1   ,RCONTNX1 ,RDIMNUF1  ,RDIMNUM1  ,REXTNCOM  ,REXTSRV1  , RGGCPPT1  ,RGGCPUD1   ,RGRNRHT1

--RIDMGCT1 ,RIDORAT1    ,RLFCPRT1 ,RLINK1 ,RLOCTON1 ,RMORDPR1 ,RMRSODR1 ,RMSNSID1 ,ROTRQSS1 ,RNUMOBJ1 ,RPRSTLT1 ,RRESOBJ1

--RROUTE1,RSHELF1 ,RSIADND1,RSLOT1 ,RSUBSCR1 ,RSYNCDF1,RTMSLOT1,RTREMAP1,RCIMCNT1

--map CRAMER.CIM_CONTEXT,  target CRAMER.CIM_CONTEXT;

MAP CRAMER.ALLOCATEDLOCK,  target CRAMER.ALLOCATEDLOCK;

MAP CRAMER.CARD,  target CRAMER.CARD;

MAP CRAMER.CONNECTIONX,  target CRAMER.CONNECTIONX;

--MAP CRAMER.DIMNUMBERFREE,  target CRAMER.DIMNUMBERFREE;

--MAP CRAMER.DIMNUMBER,  target CRAMER.DIMNUMBER;

--MAP CRAMER.EXT_NODE_COMMON,  target CRAMER.EXT_NODE_COMMON; (had distinct columns between source and target)

MAP CRAMER.EXT_SERVICE_SCP_EVC,  target CRAMER.EXT_SERVICE_SCP_EVC;

MAP CRAMER.EXT_SERVICE_EVC,  target CRAMER.EXT_SERVICE_EVC;

MAP CRAMER.GFPGCP_PORT,  target CRAMER.GFPGCP_PORT;

MAP CRAMER.GFPGCP_UPDATE,  target CRAMER.GFPGCP_UPDATE;

MAP CRAMER.GRANITERESPONSEHISTORY,  target CRAMER.GRANITERESPONSEHISTORY;

-- Exclueded on 23rd Oct,2019 as confirmed by Vu on Q Chat

--MAP CRAMER.IDIS_MESSAGING_CONTEXT,  target CRAMER.IDIS_MESSAGING_CONTEXT;

--MAP CRAMER.SYNCDIFF_O,  target CRAMER.SYNCDIFF_O;

MAP CRAMER.IDIS_ORDER_ATTRS,  target CRAMER.IDIS_ORDER_ATTRS;

MAP CRAMER.LFACSPORT,  target CRAMER.LFACSPORT;

MAP CRAMER.LINK,  target CRAMER.LINK;

MAP CRAMER.LOCATION,  target CRAMER.LOCATION;

--MAP CRAMER.MRSORDERPROCESS,  target CRAMER.MRSORDERPROCESS;

--MAP CRAMER.MRSORDER,  target CRAMER.MRSORDER;

MAP CRAMER.MSNSIAD,  target CRAMER.MSNSIAD;

MAP CRAMER.OUTAGE_REQUEST_SUBSYTEM,  target CRAMER.OUTAGE_REQUEST_SUBSYTEM;

MAP CRAMER.NUMBEROBJECT,  target CRAMER.NUMBEROBJECT;

MAP CRAMER.PROJECTSTATUSLISTENER,  target CRAMER.PROJECTSTATUSLISTENER;

MAP CRAMER.RESERVEDOBJECT,  target CRAMER.RESERVEDOBJECT;

--MAP CRAMER.ROUTE,  target CRAMER.ROUTE;

MAP CRAMER.SHELF,  target CRAMER.SHELF;

MAP CRAMER.SIADNODEB,  target CRAMER.SIADNODEB;

MAP CRAMER.SLOT,  target CRAMER.SLOT;

MAP CRAMER.SUBSCRIBER,  target CRAMER.SUBSCRIBER;

MAP CRAMER.TIMESLOT,  target CRAMER.TIMESLOT;

MAP CRAMER.TREMAPPING,  target CRAMER.TREMAPPING;

$ more rcanopi.prm

replicat  rcanopi

--Its related to BPEL/CANOPI database, and merger of three replicats named as RIDAVOS1,RIDSRPT1,RIPAG2D1

--34 tables

SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

useridalias ggsuser

-- userid ggsuser@p2can1d7.db.att.com, password sh4d0wdb

assumetargetdefs

HANDLECOLLISIONS

ALLOWNOOPUPDATES

--DBOPTIONS DEFERREFCONST

--discardfile /opt/app/p2can1d7/ggs/11.2.1.0.3/dirrpt/rcanopi.dsc, append, megabytes 100

REPERROR (2292, DISCARD)

REPERROR (2291, DISCARD)

REPERROR (1400,DISCARD)

-- map PKs

-- end map PKs

--begin ddl support--

ddloptions report

DDLSUBST 'OMG_DAT' with 'CIM_DAT' include all

DDLSUBST 'IPAG_DAT' with 'CIM_DAT' include all

DDLERROR 4043 IGNORE

DDLERROR 1435 IGNORE

DDLERROR 1408 IGNORE

DDLERROR 24344 IGNORE

DDLERROR 1917 IGNORE

DDLERROR 1741 IGNORE

DDLERROR 942 IGNORE

DDLERROR 1418 IGNORE

DDLERROR 903 IGNORE

DDLERROR 1430 IGNORE

DDLERROR 904 IGNORE

--DDLERROR 955 IGNORE

DDL include mapped

--end ddl support--

MAP IDIS_OMG.AI_ABSTRACT_AI, target IDIS_OMG.AI_ABSTRACT_AI;

MAP IDIS_OMG.AI_CLASS_PROPERTIES, target IDIS_OMG.AI_CLASS_PROPERTIES;

MAP IDIS_OMG.AI_IDIS_CONTEXT, target IDIS_OMG.AI_IDIS_CONTEXT;

MAP IDIS_OMG.AI_IDIS_ORDER_AI, target IDIS_OMG.AI_IDIS_ORDER_AI;

MAP IDIS_OMG.CNL_TECHNICAL_ORDER_VERSION, target IDIS_OMG.CNL_TECHNICAL_ORDER_VERSION;

MAP IDIS_OMG.EDW_CACHE, target IDIS_OMG.EDW_CACHE;

MAP IDIS_OMG.EVC_ORDER_TO_UNI_SITE, target IDIS_OMG.EVC_ORDER_TO_UNI_SITE;

MAP IDIS_OMG.EVC_PROJECT_ORDER_VERSION target IDIS_OMG.EVC_PROJECT_ORDER_VERSION;

MAP IDIS_OMG.EVC_TECHNICAL_ORDER_VERSION, target IDIS_OMG.EVC_TECHNICAL_ORDER_VERSION;

MAP IDIS_OMG.EXTERNAL_INTERFACE_HISTORY, target IDIS_OMG.EXTERNAL_INTERFACE_HISTORY;

MAP IDIS_OMG.INL_TECHNICAL_ORDER_VERSION target IDIS_OMG.INL_TECHNICAL_ORDER_VERSION;

MAP IDIS_OMG.INL_PROJECT_ORDER_VERSION target IDIS_OMG.INL_PROJECT_ORDER_VERSION;

MAP IDIS_OMG.LAG_TECHNICAL_ORDER_VERSION target IDIS_OMG.LAG_TECHNICAL_ORDER_VERSION;

MAP IDIS_OMG.LAG_PROJECT_ORDER_VERSION target IDIS_OMG.LAG_PROJECT_ORDER_VERSION;

MAP IDIS_OMG.LINK_DATA, target IDIS_OMG.LINK_DATA;

MAP IDIS_OMG.LINK_ORDER_TO_LINK_DATA, target IDIS_OMG.LINK_ORDER_TO_LINK_DATA;

MAP IDIS_OMG.PROJECT_ORDER, target IDIS_OMG.PROJECT_ORDER;

MAP IDIS_OMG.PROJECT_ORDER_VERSION, target IDIS_OMG.PROJECT_ORDER_VERSION;

MAP IDIS_OMG.PROJECT_TECH_ORDER_VERSION, target IDIS_OMG.PROJECT_TECH_ORDER_VERSION;

MAP IDIS_OMG.TECHNICAL_ORDER, target IDIS_OMG.TECHNICAL_ORDER;

MAP IDIS_OMG.TECHNICAL_ORDER_VERSION, target IDIS_OMG.TECHNICAL_ORDER_VERSION;

MAP IDIS_OMG.UA_HISTORY, target IDIS_OMG.UA_HISTORY;

MAP IDIS_OMG.UA_PREFERENCE, target IDIS_OMG.UA_PREFERENCE;

MAP IDIS_OMG.UA_CONTACT_INFO, target IDIS_OMG.UA_CONTACT_INFO;

MAP IDIS_OMG.UA_ENTITY target, IDIS_OMG.UA_ENTITY;

MAP IDIS_OMG.UA_ENTITY_INFO, target IDIS_OMG.UA_ENTITY_INFO;

MAP IDIS_OMG.UA_GROUPS_TO_PERMISSIONS, target IDIS_OMG.UA_GROUPS_TO_PERMISSIONS;

MAP IDIS_OMG.UA_PERMISSION, target IDIS_OMG.UA_PERMISSION;

MAP IDIS_OMG.UA_USERS_TO_GROUPS, target IDIS_OMG.UA_USERS_TO_GROUPS;

MAP IDIS_OMG.UNI_PROJECT_ORDER_VERSION, target IDIS_OMG.UNI_PROJECT_ORDER_VERSION;

MAP IDIS_OMG.UNI_SITE, target IDIS_OMG.UNI_SITE;

MAP IDIS_OMG.UNI_TECHNICAL_ORDER_VERSION, target IDIS_OMG.UNI_TECHNICAL_ORDER_VERSION, REPERROR (12899, DISCARD);

MAP IDIS_AVOS.AEB4PTBY, target IDIS_AVOS.AEB4PTBY;

MAP IDIS_AVOS.AEB4PTASK, target IDIS_AVOS.AEB4PTASK;

MAP IDIS_RPT.MERGEAVOS, target IDIS_RPT.MERGEAVOS;

MAP IDIS_OMG.OMX_EVC_REQ, target IDIS_OMG.OMX_EVC_REQ;

p2can1d7g@mlph638(4111) p2can1d73 /opt/app/p2can1d7/ggs/12.3.0.1.4/dirprm

OL TARGET : 

$ more mgr.prm

PORT 7809

LAGINFOMINUTES 0

LAGREPORTMINUTES 5

LAGCRITICALSECONDS 30

AUTORESTART EXTRACT E*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

AUTORESTART EXTRACT P*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

AUTORESTART REPLICAT R*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440

PURGEOLDEXTRACTS /opt/app/p2bbn2d4/ggs/12.3.0.1.4/trails/*, USECHECKPOINTS, MINKEEPDAYS 3, FREQUENCYMINUTES 15

$

$ more pggdnpr1.prm

extract pggdnpr1

GETUPDATEBEFORES

GETUPDATEAFTERS

--NOCOMPRESSUPDATES

RMTHOST pz11dti-ggvip2.madc.att.com, MGRPORT 7822, COMPRESS, TCPBUFSIZE 200000, TCPFLUSHBYTES 200000

RMTTRAIL ./trails/bbnms/aa, format release 11.2

userid ggs@p2bbn2d4.db.att.com, password take14team

NOPASSTHRU

TABLE GGS.HEARTBEAT, TOKENS (

PMPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME'),

PMPTIME = @DATENOW() ,

);

PASSTHRU

TABLE VENUS.*;

$

$ more pggdnpr2.prm

extract pggdnpr2

GETUPDATEBEFORES

GETUPDATEAFTERS

--NOCOMPRESSUPDATES

RMTHOST pz22dti-ggvip2.bodc.att.com , MGRPORT 7822 COMPRESS TCPBUFSIZE 200000, TCPFLUSHBYTES 200000

RMTTRAIL ./trails/bbnms/aa,  format release 11.2

userid ggs@p2bbn2d4.db.att.com, password take14team

NOPASSTHRU

TABLE GGS.HEARTBEAT, TOKENS (

PMPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME'),

PMPTIME = @DATENOW() ,

);

PASSTHRU

TABLE VENUS.*;

$

$

$ more pggdnpr3.prm

-- identify group

extract PGGDNPR3

GETUPDATEBEFORES

GETUPDATEAFTERS

--NOCOMPRESSUPDATES

RMTHOST pz33dti-ggvip2.bhdc.att.com , MGRPORT 7822 COMPRESS TCPBUFSIZE 200000, TCPFLUSHBYTES 200000

RMTTRAIL ./trails/bbnms/aa, format release 11.2

userid ggs@p2bbn2d4.db.att.com, password take14team

NOPASSTHRU

TABLE GGS.HEARTBEAT, TOKENS (

PMPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME'),

PMPTIME = @DATENOW() ,

);

PASSTHRU

TABLE VENUS.*;

$

GGSCI (flpd11504) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     EP2BBN2D    00:00:02      00:00:06

EXTRACT     RUNNING     EVENUS      00:00:04      00:00:00

EXTRACT     RUNNING     PP2BBN2D    00:00:05      00:00:09

EXTRACT     RUNNING     PVENUS      00:00:00      00:00:09

p1bbn2d9g@flpd11504@p1bbn2d91 /opt/app/p1bbn2d9/ggs/19.1.0.0.4/dirprm>>more ep2bbn2d.prm

-- ext group

extract ep2bbn2d

-- DB login (must use TNS string)

getenv (ORACLE_SID)

--userid ggs@ljb1pnf.sbc.com, password  take14team

--added credentialstore for storing password on 06th May 2023

useridalias ggs

SETENV (NLS_LANG="AMERICAN_AMERICA.UTF8")

-- ASM login

-- tranlogoptions dblogreader

--TRANLOGOPTIONS ASMUSER "x@", ASMPASSWORD x, FORCEASM

--Suggest by SR 3-25265667661 02/21/2021

--BR BROFF

--tranlogoptions dblogreader

--TRANLOGOPTIONS DBLOGREADERBUFSIZE 4096000

--TRANLOGOPTIONS ASMBUFSIZE 28672

TRANLOGOPTIONS _CONVERTBADNUMBER ZERO

--TRANLOGOPTIONS PURGEORPHANEDTRANSACTIONS

FETCHOPTIONS USELATESTVERSION, NOUSESNAPSHOT, MISSINGROW REPORT, SUPPRESSDUPLICATES

-- trace when needed

-- TLTRACE, DEBUG

-- TRACE, DDLINCLUDE DDLTRACE.log

-- TRACE ./dirrpt/TRACE.trc

--HANDLECOLLISIONS

-- TRACE2 ./dirrpt/TRACE2.trc

-- end trace

-- for RAC (in millisec)

--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 10000

BR BRDIR /opt/app/p1bbn2d9/ggs/19.1.0.0.4/BR

-- THREADOPTIONS PROCESSTHREADS EXCEPT 3

--THREADOPTIONS PROCESSTHREADS EXCEPT 4

-- warn long trans

WARNLONGTRANS 2Hours CHECKINTERVAL 1H SKIPEMPTYTRANS

--WARNLONGTRANS 2Hours CHECKINTERVAL 1D

-- DDL support -- begin

-- DDL INCLUDE ALL objname "NIMBUS.*"

-- DDL INCLUDE OBJNAME NIMBUS.ABSTRACTFLOWABLE  &

-- INCLUDE OBJNAME NIMBUS.WORKFLOW_WORKFLOW  &

-- INCLUDE OBJNAME NIMBUS.gg_heartbeat

-- DDLOPTIONS REPORT

-- DDL support -- end

-- local trail when using data pump

exttrail /opt/app/p1bbn2d9/ggs/19.1.0.0.4/dirdat/lt,format release 12.3

-- specify fetch option

-- FETCHOPTIONS FETCHPKUPDATECOLS

-- FETCHOPTIONS NOFETCH

FETCHOPTIONS USEKEY

-- specify tables to be captured

-- TABLEEXCLUDE NIMBUS.EXTERNALINTERFACEHISTORY

-- TABLE NIMBUS.*

TABLE NIMBUS.ABSTRACTFLOWABLE ;

TABLE NIMBUS.ACCESSORYINFO;

TABLE NIMBUS.ACCESSPOINT ;

TABLE NIMBUS.ACTIVITY ;

TABLE NIMBUS.ADDRESSINFO ;

TABLE NIMBUS.ADDRESSINFO_ADDRESSLINESTYPE;

--added on 14 July 2024

TABLE NIMBUS.SERVICEACCESS;

p1bbn2d9g@flpd11504@p1bbn2d91 /opt/app/p1bbn2d9/ggs/19.1.0.0.4/dirprm>>more pp2bbn2d.prm

-- identify group

extract pp2bbn2d

passthru

-- specify source DB login

-- when using passthru, no need to specify user/pass.

-- userid ggora@ljb2tdb.snt.bst.bls.com, password ggora

-- specify remote box

-- RMTHOST VIP for mlpi453, 455, and 456

--RMTHOST 130.4.165.145, MGRPORT 7809 COMPRESS TCPBUFSIZE 200000, TCPFLUSHBYTES 200000

-- RMTHOST IPs for mlpi453, 455, and 456

--RMTHOST flpi573.ffdc.sbc.com, MGRPORT 7809 COMPRESS TCPBUFSIZE 200000, TCPFLUSHBYTES 200000

RMTHOST flph1167.ffdc.sbc.com, MGRPORT 7809 COMPRESS TCPBUFSIZE 200000, TCPFLUSHBYTES 200000

--DYNAMICPORTLIST 7810-7820

-- specify remote trail (on target system)

RMTTRAIL /opt/app/p2bbn2d4/ggs/12.3.0.1.4/trails/rt,FORMAT RELEASE 12.3

--RMTTRAIL /opt/app/p2bbn2d3/ggs/og11g-12.3.0.1.2/trails/rt

-- specify tables to be captured

TABLEEXCLUDE NIMBUS.EXTERNALINTERFACEHISTORY

TABLE NIMBUS.*;

-- DDL support -- begin

-- DDL INCLUDE ALL objname "NIMBUS.*"

--

-- DDLOPTIONS REPORT

-- DDL support - end

p1bbn2d9g@flpd11504@p1bbn2d91 /opt/app/p1bbn2d9/ggs/19.1.0.0.4/dirprm>>more evenus.prm

-- ext group

extract evenus

-- DB login (must use TNS string)

getenv (ORACLE_SID)

--SETENV(TZ=GMT)

--added credentialstore on 6th May 2023

useridalias ggs

--userid ggs@p1bbn2d6.db.att.com, password  take14team

--SETENV (NLS_LANG="AMERICAN_AMERICA.UTF8")

--TRANLOGOPTIONS SOURCE_OS_TIMEZONE UTC

--tranlogoptions dblogreader

--TRANLOGOPTIONS DBLOGREADERBUFSIZE 4096000

--THREADOPTIONS PROCESSTHREADS EXCEPT 4

--TRANLOGOPTIONS ASMBUFSIZE 28672

TRANLOGOPTIONS _CONVERTBADNUMBER ZERO

--TRANLOGOPTIONS PURGEORPHANEDTRANSACTIONS

FETCHOPTIONS USELATESTVERSION, NOUSESNAPSHOT, MISSINGROW REPORT, SUPPRESSDUPLICATES

BR BRDIR /opt/app/p1bbn2d9/ggs/19.1.0.0.4/BR

WARNLONGTRANS 2Hours CHECKINTERVAL 1H SKIPEMPTYTRANS

exttrail /opt/app/p1bbn2d9/ggs/19.1.0.0.4/dirdat/vt,format release 12.3

TABLE GGS.HEARTBEAT;

GETUPDATEBEFORES

--List of the tables in replication for UPS,GDDN targets

--TABLE VENUS.ACTIVEASSET, TOKENS ( XID_SCN = @GETENV("TRANSACTION","CSN"));

--TABLE VENUS.INACTIVEASSET, TOKENS ( XID_SCN = @GETENV("TRANSACTION","CSN"));

--TABLE VENUS.ACTIVECAPABILITY, TOKENS ( XID_SCN = @GETENV("TRANSACTION","CSN"));

--TABLE VENUS.INACTIVECAPABILITY, TOKENS ( XID_SCN = @GETENV("TRANSACTION","CSN"));

TABLE VENUS.ACTIVEASSET, TOKENS ( tkn-rsn = @GETENV('TRANSACTION','CSN'));

TABLE VENUS.INACTIVEASSET, TOKENS ( tkn-rsn = @GETENV('TRANSACTION','CSN'));

TABLE VENUS.ACTIVECAPABILITY, TOKENS ( tkn-rsn = @GETENV('TRANSACTION','CSN'));

TABLE VENUS.INACTIVECAPABILITY, TOKENS ( tkn-rsn = @GETENV('TRANSACTION','CSN'));

p1bbn2d9g@flpd11504@p1bbn2d91 /opt/app/p1bbn2d9/ggs/19.1.0.0.4/dirprm>>

p1bbn2d9g@flpd11504@p1bbn2d91 /opt/app/p1bbn2d9/ggs/19.1.0.0.4/dirprm>>more pvenus.prm

extract pvenus

passthru

RMTHOST flph1167.ffdc.sbc.com, MGRPORT 7809 COMPRESS TCPBUFSIZE 200000, TCPFLUSHBYTES 200000

--DYNAMICPORTLIST 7810-7820

-- specify remote trail (on target system)

RMTTRAIL /opt/app/p2bbn2d4/ggs/12.3.0.1.4/trails/vt,format release 12.3

--RMTTRAIL /opt/app/p2bbn2d3/ggs/og11g-12.3.0.1.2/trails/vt

-- specify tables to be captured

TABLE GGS.HEARTBEAT;

TABLE VENUS.*;

--

$ more rvenus.prm

-- rep group

replicat RVENUS

getenv (ORACLE_SID)

getenv (ORACLE_HOME)

SETENV (NLS_LANG="AMERICAN_AMERICA.UTF8")

--userid ggs@p2bbn2d4.db.att.com, password take14team

useridalias ggs

assumetargetdefs

HANDLECOLLISIONS

--BATCHSQL

discardfile  /opt/app/p2bbn2d4/ggs/12.3.0.1.4/dirrpt/rvenus.dsc, append, megabytes 100

--DDL suuport begin

--DDLOPTIONS REPORT

--DDL suuport end

-- discard one error (skip trans)

-- DDLERROR 1749 DISCARD

-- DDLERROR 1418 DISCARD

-- DDLERROR 2443 DISCARD

MAP VENUS.ACTIVEASSET,     target VENUS.ACTIVEASSET;

MAP VENUS.INACTIVEASSET,     target VENUS.INACTIVEASSET;

MAP VENUS.ACTIVECAPABILITY,     target VENUS.ACTIVECAPABILITY;

MAP VENUS.INACTIVECAPABILITY,     target VENUS.INACTIVECAPABILITY;

MAP GGS.HEARTBEAT target GGS.HEARTBEAT;

--TABLE VENUS.*;

$ hostname -f

flph1167.ffdc.sbc.com

--

$ more rnimbus1.prm

-- rep group

replicat  RNIMBUS1

getenv (ORACLE_SID)

getenv (ORACLE_HOME)

SETENV (NLS_LANG="AMERICAN_AMERICA.UTF8")

--userid ggs@p2bbn2d4.db.att.com, password take14team

useridalias ggs

assumetargetdefs

 HANDLECOLLISIONS

BATCHSQL

discardfile   /opt/app/p2bbn2d4/ggs/12.3.0.1.4/dirrpt/RNIMBUS1.dsc, append, megabytes 100

-- reperror (1403, discard)

--DDL suuport begin

--DDLOPTIONS REPORT

--DDL suuport end

--discard one error (skip trans)

--DDLERROR 1749 DISCARD

--DDLERROR 1418 DISCARD

--DDLERROR 2443 DISCARD

MAP NIMBUS.ACCESSPOINT, target NIMBUS.ACCESSPOINT;

--MAP NIMBUS.ACTIVITY, target NIMBUS.ACTIVITY;

MAP NIMBUS.ADDRESSINFO_ADDRESSLINESTYPE, target NIMBUS.ADDRESSINFO_ADDRESSLINESTYPE;

MAP NIMBUS.ADDRESSINFO, target NIMBUS.ADDRESSINFO;

GG doc details

 GGSUSER Dictionary Build proc and job.txt

GGSUSER Dictionary Build procedure and job

column first_change# format 999999999999999

SELECT first_change#, to_char(first_time,'yyyy-mm-dd hh24:mi:ss')  FROM v$archived_log WHERE dictionary_begin = 'YES' AND standby_dest = 'NO' AND name IS NOT NULL AND status = 'A';

CREATE OR REPLACE procedure GGSUSER.Dictionary_Build is

cdate date;

begin

dbms_output.enable(10000000);

select sysdate into cdate from dual;

DBMS_LOGMNR_D.BUILD(options=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

select sysdate into cdate from dual;

end;

/

BEGIN

  SYS.DBMS_SCHEDULER.CREATE_JOB

    (

       job_name        => 'GGSUSER.DICTIONARY_BUILD_JOB'

      ,start_date      => TO_TIMESTAMP_TZ('2020/11/20 00:00:00.000000 -07:00','yyyy/mm/dd hh24:mi:ss.ff tzr')

      ,repeat_interval => 'FREQ=DAILY; BYHOUR=06;BYMINUTE=1;'

      ,end_date        => NULL

      ,job_class       => 'DEFAULT_JOB_CLASS'

      ,job_type        => 'STORED_PROCEDURE'

      ,job_action      => 'Dictionary_Build'

      ,comments        => 'OSD GGS Dictionary'

    );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'GGSUSER.DICTIONARY_BUILD_JOB'

     ,attribute => 'RESTARTABLE'

     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'GGSUSER.DICTIONARY_BUILD_JOB'

     ,attribute => 'LOGGING_LEVEL'

     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'GGSUSER.DICTIONARY_BUILD_JOB'

     ,attribute => 'MAX_FAILURES');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'GGSUSER.DICTIONARY_BUILD_JOB'

     ,attribute => 'MAX_RUNS');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'GGSUSER.DICTIONARY_BUILD_JOB'

     ,attribute => 'STOP_ON_WINDOW_CLOSE'

     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'GGSUSER.DICTIONARY_BUILD_JOB'

     ,attribute => 'JOB_PRIORITY'

     ,value     => 3);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'GGSUSER.DICTIONARY_BUILD_JOB'

     ,attribute => 'SCHEDULE_LIMIT');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'GGSUSER.DICTIONARY_BUILD_JOB'

     ,attribute => 'AUTO_DROP'

     ,value     => TRUE);

  SYS.DBMS_SCHEDULER.ENABLE

    (name                  => 'GGSUSER.DICTIONARY_BUILD_JOB');

END;

/

BEGIN

  DBMS_SCHEDULER.RUN_JOB(

    JOB_NAME            => 'GGSUSER.DICTIONARY_BUILD_JOB',

    USE_CURRENT_SESSION => FALSE);

END;

/

----

Goldengate Recovery checkpoint reset.txt

alter evenus,ioextseqno 469690,ioextrba 0,thread 2

alter EP2BBN2D,ioextseqno 469690,ioextrba 0,thread 2

  Current Checkpoint (position of last record read in the data source):

    Thread #: 2

    Sequence #: 469690

    RBA: 1186048512

    Timestamp: 2023-05-06 04:30:38.000000

    SCN: 5925.2439372008 (25450120600808)

    Redo File: +P1BBN2D6_REDO02/p1bbn2d6/onlinelog/group_6.304.889365237

=====

Goldengate_Xid_transaction_showtrans_skiptrans.txt

To recreate the extract without dictionary build you cannot start at point in time that was in past when the dictionary build

is not available.

If you can downgrade to classic extract and perform this step and then upgrade to integrated once you have recovered the data that

is the best possibility. You can also apply the patch in parallel before upgrading to integrated.

Downgrading INTEGRATED Extract to Classic Extract fails on RAC environment ( Doc ID 1476058.1 ) ==> no thread options for non RAC

How To Upgrade From Goldengate Classic Extract To Integrated Extract ( Doc ID 1484313.1 )

"One point worth noting, unlike classic extract you cannot simply point at any historical point in time and expects a new IE to work. 

So if you have a need to create a new IE now to process yesterday's data and all your current IEs was created last year meaning the dictionaries 

are in year old archived logs you are basically stuck. Therefore one way (if you have a need) is to build a dictionary every night if you generally have

 archived logs on disk for > 1 day you can go backwards to find a dictionary for your new IE."

If you have dictionaries built from time to time, say every day. Then you can configure a new extract (with the same parameters) and tag it to this 

daily/yesterday dictionary's scn. Or you can delete/unregister the current extract and create the new extract with the same name.

You cannot skip archived logs for an existing IE even if you are not interested in the data. It has to read through the logs for metadata consistency.

You can build dictionaries at any time using

execute DBMS_LOGMNR_D.BUILD( options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

And subsequently search for the SCN of a data dictionary build using

column first_change# format 999999999999999

SELECT first_change#, to_char(first_time, 'yyyy-mm-dd hh24:mi:ss') FROM v$archived_log WHERE dictionary_begin = 'YES' AND standby_dest = 'NO' AND 

name IS NOT NULL AND status = 'A';

You will see that ggsci register extract has a scn option and you can use this scn where a dictionary exist to register saving another dictionary build.

Note register with no options does 2 things, build a dictionary and instructs RMAN not to delete any archived logs that an extract has not processed. 

See the REGISTER EXTRACT in the reference manual, it also explains the above sql stuff above.

send extract <extname>,showtrans tabular

send extract <extname>,skiptrans <xid>

Troubleshooting OGG with XID Trasnaction

XID:221.26

select * from gv$transaction where xidusn=221 and xidslot=26;

select * from gv$transaction where xidusn=8 and xidslot=11;

transaction.addr=session.taddr;

select status,addr from gv$transaction where xidusn=221 and xidslot=26;

select status,addr from gv$transaction where xidusn=8 and xidslot=11;

Status ADDR

_____________________________

Active 0000000C89CBAEF0

SQL>select sid,serial# from gv$session where taddr='0000000C89CBAEF0';

SID Serial#

________________________

152 44251

select a.sid,a.serial#,b.sql_text from gv$session a,gv$sqlarea b where a.sql_address=b.address and sid='152';

select sid,inst_id,serial#,username,osuser,status,machine,sql_id,sql_hash_value,state from gv$session where sid=152;

select sql_text from gv$sqlarea where sql_id='<get sql_id from above'>;

---

OGG  Upgrade steps on system hosting Goldengate during DB upgrade.docx

OGG Upgrade steps on system hosting Goldengate during DB upgrade

 

1. Stop Extract , pump processes on source

 

2. Wait until there is no lag on replicat , stop replicat process.

 

--4. Stop extract process on source wait until pump process all the trail files , then stop it.

 

3. Once application is down on target, shutdown the DB.

5. Install OGG 19c under different home (Choose appropriate DB versionduring installation , choose for 19c database ) .

 

6. Upgrade database on 19c .

 

7. Copy goldengate process related files from old goldengate to the new 19c goldengate for 19c RDBMS like .prm and .cpe in appropriate directories.

 

NOTE ! If your new goldengate version on target site is lower then source , you should add "format release " into the extract and pump for trail files , otherwise , the OGG lower version cannot read 19c trail file format.

 

8. Start the extract. And do etrollover

 

10 . Alter the pump to read from new generated trail file : Example : alter <pump_name> , extseqno <newtrail file seq> extrba 0

alter <pump_name> , etrollover <<<< It will generate new trail file on site 3 >

 

11. On target, alter replicat to read from new trail file .

 

12 . Start replicat

 

13. After ensuring all goldengate processes are running, start the applications accessing the database.

=========

Troubleshooting_OGG_XID_Transaction.txt

SDBA can u confirm if below archives on thse threads are currently available for server flpd670:

Redo Thread 1, Redo Log Sequence Number 196837

Redo Thread 2, Redo Log Sequence Number 206654

Redo Thread 4, Redo Log Sequence Number 204942

Redo Thread 3, Redo Log Sequence Number 207227

Theread 1:

Recovery SCN

25401121862297

25399292664984

25399692782630

25401118440536

Select * from gv$transaction where xidusn=221 and xidslot=26;

select status,addr from gv$transaction where xidusn=221 and xidslot=26;

SQL> select status,addr from gv$transaction where xidusn=221 and xidslot=26;

STATUS           ADDR

---------------- ----------------

ACTIVE           0000000C89CBAEF0

SQL> select sid,serial# from v$session where taddr='0000000C89CBAEF0';

       SID    SERIAL#

---------- ----------

       152      44251

select sid,inst_id,serial#,username,osuser,status,machine,sql_id,sql_hash_value,state from gv$session where sid ='152';

select sql_text from gv$sqlarea where sql_id ='152';

select a.sid,a.serial#,b.sql_text from gv$session a,gv$sqlarea b where a.sql_address=b.address and sid='152';

268.31.3274012

Select * from gv$transaction where xidusn=268 and xidslot=31;

SQL> select status,addr from gv$transaction where xidusn=268 and xidslot=31;

STATUS           ADDR

---------------- ----------------

ACTIVE           0000000C92285918

SQL> select sid,serial# from gv$session where taddr='0000000C92285918';

       SID    SERIAL#

---------- ----------

      1614      20213

select sid,inst_id,serial#,username,osuser,status,machine,sql_id,sql_hash_value,state,SQL_EXEC_START from gv$session where sid ='1614';

select a.sid,a.serial#,b.sql_text from gv$session a,gv$sqlarea b where a.sql_address=b.address and sid='1614';

195.17.1096475

Select * from gv$transaction where xidusn=195 and xidslot=17;

SQL> select status,addr from gv$transaction where xidusn=195 and xidslot=17;

STATUS           ADDR

---------------- ----------------

ACTIVE           0000000619940E90

SQL> select sid,serial# from gv$session where taddr='0000000619940E90';

       SID    SERIAL#

---------- ----------

       908      21249

select sid,inst_id,serial#,username,osuser,status,machine,sql_id,sql_hash_value,state,SQL_EXEC_START from gv$session where sid ='908';

select a.sid,a.serial#,b.sql_text from gv$session a,gv$sqlarea b where a.sql_address=b.address and sid='908';

=====

Upgrading_Classic_Extract_to_Integrated_Extract.txt

dblogin gguser

1. Stop Extract ext1

2. register extract ext1,database

3. info ext1,upgrade

If u see message ext1 is ready to be upgraded to integrated capture then proceed for below

If u dont see above message then start the extract process so that it passes mentiponed scn and then stop it once u see. U can do info to check current status of extract.

4. alter extract ext1,upgrade integrated tranlog

Extract ext1 successfully upgraded to integrated capture

 Remove the parameters that are specific to Classic capture only

5. Start extract ext1

6. info ext1

7. sql>select capture_name,queue_name,status from dba_capture;

===

LOB Migration.txt

--Verify table and LOB size

SELECT

 (SELECT SUM(S.BYTES)/1024/1024/1024  FROM user_SEGMENTS S where                                                                                           -- The Table Segment size

         (S.SEGMENT_NAME = UPPER('WSMESSAGE'))) +

 (SELECT SUM(S.BYTES)/1024/1024/1024                                                                                                 -- The Lob Segment Size

  FROM user_SEGMENTS S, user_LOBS L

  WHERE 

       (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('WSMESSAGE') )) +

 (SELECT SUM(S.BYTES)/1024/1024/1024                                                                                                 -- The Lob Index size

  FROM user_SEGMENTS S, user_INDEXES I

  WHERE 

       (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('WSMESSAGE') AND INDEX_TYPE = 'LOB' ))

  "TOTAL TABLE SIZE"

FROM DUAL;

--Before 56 GB

select  table_name, column_name, securefile,compression from user_lobs where TABLE_NAME= 'WSMESSAGE';

ALTER TABLE WSMESSAGE MOVE LOB(REQUEST) STORE AS  securefile (TABLESPACE USERS);

ALTER TABLE WSMESSAGE MOVE LOB(RESPONSE) STORE AS  securefile (TABLESPACE USERS);

ALTER TABLE WSMESSAGE MOVE LOB(DEBUG) STORE AS  securefile (TABLESPACE USERS);

select  table_name, column_name, securefile,compression from user_lobs where TABLE_NAME= 'WSMESSAGE';

ALTER TABLE WSMESSAGE MODIFY LOB(REQUEST) (COMPRESS HIGH);

ALTER TABLE WSMESSAGE MODIFY LOB(RESPONSE) (COMPRESS HIGH);

ALTER TABLE WSMESSAGE MODIFY LOB(DEBUG) (COMPRESS HIGH);

select  table_name, column_name, securefile,compression from user_lobs where TABLE_NAME= 'WSMESSAGE';

select segment_type,round(sum(bytes)/1024/1024/1024) "GB"

from user_segments

where segment_name='WSMESSAGE'

group by segment_type

union

-- total size of LOBs for table MYTABLE

SELECT segment_TYPE,round(SUM(BYTES)/1024/1024/1024) "GB LOB"

FROM user_SEGMENTS 

WHERE SEGMENT_NAME IN(

    select distinct lob_name 

    from user_lob_partitions

    where table_name='WSMESSAGE'

)

group by segment_type;

BEGIN

  SYS.DBMS_STATS.GATHER_TABLE_STATS (

     OwnName           => 'IPAM'

    ,TabName           => 'WSMESSAGE_2020_BKUP'

    ,Estimate_Percent  => 10

    ,Method_Opt        => 'FOR ALL COLUMNS SIZE 1'

    ,Degree            => 4

    ,Cascade           => FALSE

    ,No_Invalidate  => FALSE);

END;

/

___________________________________________________________

2nd method

create table IPAM.WSMESSAGE_2020_BKUP_1

(

  TRANSACTION_ID  NUMBER                        NOT NULL,

  INTERFACE_NAME  VARCHAR2(30 BYTE),

  OPERATION       VARCHAR2(100 BYTE)            NOT NULL,

  START_TIME      DATE                          NOT NULL,

  ELAPSED_TIME    NUMBER,

  REQUEST         CLOB                          DEFAULT EMPTY_CLOB(),

  RESPONSE        CLOB                          DEFAULT EMPTY_CLOB(),

  SOURCE          VARCHAR2(128 BYTE),

  STATUS          NUMBER,

  REQUEST_TYPE    VARCHAR2(50 BYTE),

  DEBUG           CLOB                          DEFAULT EMPTY_CLOB()

)

LOB (REQUEST) STORE AS SECUREFILE (

  TABLESPACE  USERS

  enable      STORAGE IN ROW

  CHUNK       8192

  COMPRESS    HIGH

  NOCACHE

  LOGGING

      STORAGE    (

                  INITIAL          104K

                  NEXT             1M

                  MINEXTENTS       1

                  MAXEXTENTS       UNLIMITED

                  PCTINCREASE      0

                  BUFFER_POOL      DEFAULT

                 ))

LOB (RESPONSE) STORE AS SECUREFILE (

  TABLESPACE  USERS

  enable      STORAGE IN ROW

  CHUNK       8192

  COMPRESS    HIGH

  NOCACHE

  LOGGING

      STORAGE    (

                  INITIAL          104K

                  NEXT             1M

                  MINEXTENTS       1

                  MAXEXTENTS       UNLIMITED

                  PCTINCREASE      0

                  BUFFER_POOL      DEFAULT

                 ))

LOB (DEBUG) STORE AS SECUREFILE (

  TABLESPACE  USERS

  enable      STORAGE IN ROW

  CHUNK       8192

  COMPRESS    HIGH

  NOCACHE

  LOGGING

      STORAGE    (

                  INITIAL          104K

                  NEXT             1M

                  MINEXTENTS       1

                  MAXEXTENTS       UNLIMITED

                  PCTINCREASE      0

                  BUFFER_POOL      DEFAULT

                 ))

TABLESPACE USERS

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MAXSIZE          UNLIMITED

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           )

LOGGING 

NOCACHE;

insert into IPAM.WSMESSAGE_2020_BKUP_1 select * from IPAM.WSMESSAGE;

select * from IPAM.WSMESSAGE_2020_BKUP;

commit;

SELECT

 (SELECT SUM(S.BYTES)/1024/1024/1024  FROM user_SEGMENTS S where                                                                                           -- The Table Segment size

         (S.SEGMENT_NAME = UPPER('WSMESSAGE'))) +

 (SELECT SUM(S.BYTES)/1024/1024/1024                                                                                                 -- The Lob Segment Size

  FROM user_SEGMENTS S, user_LOBS L

  WHERE 

       (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('WSMESSAGE') )) +

 (SELECT SUM(S.BYTES)/1024/1024/1024                                                                                                 -- The Lob Index size

  FROM user_SEGMENTS S, user_INDEXES I

  WHERE 

       (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('WSMESSAGE') AND INDEX_TYPE = 'LOB' ))

  "TOTAL TABLE SIZE"

FROM DUAL;

===

GG Upgrade benefits.txt

CIM GG upgrade:

Source 1:

12c DB and OGG 12.3.0.1.4

flpd670.ffdc.sbc.com

1. OGG version currently is 12.3.

2. The extract processes upgraded to integrated extract. Below are the advantages of IE:

a. Because integrated capture is fully integrated with the database, no additional setup is required to work with Oracle RAC, ASM, and TDE. The databaes are on RAC and ASM hence this is very useful featurefor the environment.

b. Performance of IE is faster than Classic. We can notice around 10% improvement in Goldengate performance.

c. Integrated capture enables faster filtering of tables.

d. Integrated capture handles point-in-time recovery and RAC integration more efficiently.

e. Integrated capture features integrated log management. The Oracle Recovery Manager (RMAN) automatically retains the archive logs that are needed by Extract.

f. For a release 11.2.0.4 source database and later (with source compatibility set to 11.2.0.4 or higher), the capture of DDL is performed by the logmining server asynchronously and requires no special triggers, tables, or other database objects to be installed. Oracle GoldenGate upgrades can be performed without stopping user applications. The use of a DDL trigger and supporting objects is required when Extract is in integrated mode with an Oracle 11g source database that is earlier than version 11.2.0.4. In our environment we were facing some performance issues due to these DDL triggers. Now this has been eliminated with introduction of IE.

Target:

Target:  Source for one of our application

11g DB and OGG 11.2.1 ---Is getting upgraded

1. The DB has been upgraded to 12c and GG to 12.3.0.1.4

2. The extract processes upgraded to integrated extract. We are already noticing perfromance improvement.

OL GG Upgrade:

Source:

12c DB and OGG 12.3.0.1.4

flph460.ffdc.sbc.com

1. The extract processes upgraded from Classic to Integrated extract

2. Added credentialstore which introduces more security. The password is not plain text anymore. This brings more security to the environment.

3. Dictioanry build happening everyday ensuring point in time recovery in case of any issues.

Target:  Source for one of our application

flph1167.ffdc.sbc.com

1. The extract processes upgraded from Classic to Integrated extract

2. Added credentialstore which introduces more security. The password is not plain text anymore. This brings more security to the environment.

3. Dictioanry build happening everyday ensuring point in time recovery in case of any issues.

______________________________________________________________

Improvement in GG and DB performance upto 10%

Easier Maintenance anf fixed lot of bugs and internal issues which Classic extract wasnt capable to handle.

Some of the DB performance burdens were reducing upon upgrading GG extract process to integrated capture

Enhancement of DB/GG credential with introduction of latest credentialstore feature which is used to store credentials more securely.

Implementation Dictionary build concept to ensure easier data recovery. 

====

Dictionary_BUILD_Goldengate_DB.txt

 execute DBMS_LOGMNR_D.BUILD( options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

You can build dictionaries at any time using

execute DBMS_LOGMNR_D.BUILD( options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

And subsequently search for the SCN of a data dictionary build using

column first_change# format 999999999999999

SELECT first_change#, to_char(first_time,  'yyyy-mm-dd hh24:mi:ss')  FROM v$archived_log WHERE dictionary_begin = 'YES' AND standby_dest = 'NO' AND name IS NOT NULL AND status = 'A';

Online_redefinition_reorg_steps_HistoryMessage_ST6.docx

 

--Ensure to take backup of table HISTORYMESSAGE in Prod and ST6. In ST6 we used below steps to take export:

 

create or replace directory ST6_Nimbus as '/pac/prod_backup/WF_MILESTONE'

grant read,write on directory ST6_NIMBUS to nimbus;

nohup expdp nimbus/bbnms0l4s2021 DIRECTORY = ST6_Nimbus DUMPFILE = WF_MILESTONE_20221025_%U.dmp filesize=10G TABLES=HISTORYMESSAGE PARALLEL=8 LOGFILE =WF_MILESTONE_20221025.log COMPRESSION=ALL &

 

-- Ensure to copy the table script using toad. In ST6 it was copied and pasted at end of this doc.

 

 

-- Ensure to take Snapshot before starting activity

-- Dependent object count and status

--check grant ans referential integrity

-- Tablespace free and available space

set timing on;

select    tbs.tablespace_name,

tot.bytes/1024/1024/1024 total,

tot.bytes/1024/1024/1024-sum(nvl(fre.bytes,0))/1024/1024/1024 used,

sum(nvl(fre.bytes,0))/1024/1024/1024 free,

(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct

from      dba_free_space fre,

(select  tablespace_name, sum(bytes) bytes

from      dba_data_files

group by tablespace_name) tot,

dba_tablespaces tbs

where   tot.tablespace_name    = tbs.tablespace_name

and        fre.tablespace_name(+) = tbs.tablespace_name

group by tbs.tablespace_name, tot.bytes/1024, tot.bytes

union

select    tsh.tablespace_name,

dtf.bytes/1024/1024/1024 total,

sum(nvl(tsh.bytes_used,0))/1024/1024/1024 used,

sum(nvl(tsh.bytes_free,0))/1024/1024/1024 free,

(1-sum(nvl(tsh.bytes_free,0))/dtf.bytes)*100 pct

from             v$temp_space_header tsh,

(select tablespace_name, sum(bytes) bytes

from dba_temp_files

group by tablespace_name) dtf

where   dtf.tablespace_name     = tsh.tablespace_name(+)

group by tsh.tablespace_name, dtf.bytes/1024/1024/1024, dtf.bytes

order by 1;

 

TABLESPACE_NAME                     TOTAL       USED       FREE        PCT ------------------------------ ---------- ---------- ---------- ---------- GGS_DATA                               30 .000976563 29.9990234 .003255208 JBBOS_ARCHIVE                         255 104.233032 150.766968 40.8756989 JBBOS_DAT                            4770 3678.96765 1091.03235 77.1272044 JBBOS_DAT_1                         752.5 166.051270 586.448730 22.0666139 JBBOS_IDX                         1233.75 685.821899 547.928101 55.5884012 SYSAUX                                 30 20.1910400 9.80895996 67.3034668 SYSTEM                                 30 1.02807617 28.9719238 3.42692057 TEMP                                  120 119.992188   .0078125 99.9934896 TOOLS                                  30 .000976563 29.9990234 .003255208 UNDOTBS1                               60 26.2839966 33.7160034 43.8066610 UNDOTBS2                               60 52.8189087 7.18109131 88.0315145 UNDOTBS3                               60 59.7031860 .296813965 99.5053101 UNDOTBS4                               90 88.0750732 1.92492676 97.8611925 UNDOTBS5                               60 59.5672607 .432739258 99.2787679 UNDOTBS6                               60 39.5926514 20.4073486 65.9877523 USERS                                  30 .000976563 29.9990234 .003255208 VENUS_DAT                              90 71.7305908 18.2694092 79.7006565   17 rows selected. Elapsed: 00:00:21.98

 

--Count of indexes,triggers,synonyms and constraints

set timing on;

 

select index_name from user_indexes where table_name='HISTORYMESSAGE';

select trigger_name from user_triggers where table_name='HISTORYMESSAGE';

select constraint_name from user_constraints where table_name='HISTORYMESSAGE';

select synonym_name from user_synonyms where table_name='HISTORYMESSAGE';

 

INDEX_NAME                                                                      -------------------------------------------------------------------------------- HISTORYMESSAGE_PK                                                               I_HSTRMSG_SONUM                                                                 I_HSTRMSG_TASKID                                                                I_HSTRMSG_SOERRID                                                               I_HSTRMSG_SOVID                                                                 I_HSTRMSG_BANNUMBER                                                             I_HSTRMSG_PRODUCTTYPE                                                             7 rows selected. Elapsed: 00:00:01.26 no rows selected. Elapsed: 00:00:02.32   CONSTRAINT_NAME                                                                 -------------------------------------------------------------------------------- SYS_C00286531                                                                   1 row selected. Elapsed: 00:00:04.42 no rows selected. Elapsed: 00:00:01.50

 

--Count of invalid objects in schema:

 

SELECT OBJECT_NAME, OBJECT_TYPE,status

    FROM USER_OBJECTS where status <>'VALID';

OBJECT_NAME                                                                     -------------------------------------------------------------------------------- OBJECT_TYPE             STATUS ----------------------- ------- IPTVBATCHMIGRATION                                                              PACKAGE BODY            INVALID                                                                                 MULTITHREAD_MIGRATION_UTIL                                                      PACKAGE BODY            INVALID                                                                                 ORDERANALYSIS                                                                   PACKAGE BODY            INVALID                                                                                 PRIORITYORDER                                                                   PACKAGE BODY            INVALID                                                                                 SUBMIGRATION                                                                    PACKAGE BODY            INVALID                                                                                 SUPPORT_UTIL                                                                    PACKAGE BODY            INVALID                                                                                 UV10BATCHMIGRATION                                                                OBJECT_NAME                                                                     -------------------------------------------------------------------------------- OBJECT_TYPE             STATUS ----------------------- ------- PACKAGE BODY            INVALID                                                                                 CHECK_YESTERDAY_REC                                                             PROCEDURE               INVALID                                                                                 CLOB_TO_BLOB                                                                    PROCEDURE               INVALID                                                                                 DM_MIGRATE_SUBTYPE                                                              PROCEDURE               INVALID                                                                                 GRANITE_REPORT_TEST                                                             PROCEDURE               INVALID                                                                                 MIGRATION_NPATTA                                                                PROCEDURE               INVALID                                                                                 MIGRATION_SUBTYPE                                                               PROCEDURE               INVALID   OBJECT_NAME                                                                     -------------------------------------------------------------------------------- OBJECT_TYPE             STATUS ----------------------- -------                                                                                 MIGRATION_WIRECENTER                                                            PROCEDURE               INVALID                                                                                 OUTPUT_BKDN_REPORT_DATA                                                         PROCEDURE               INVALID                                                                                 OUTPUT_BREAK_REPORT_DATA                                                        PROCEDURE               INVALID                                                                                 OUTPUT_EXP_REPORT_DATA                                                          PROCEDURE               INVALID                                                                                 OUTPUT_SPLIT_REPORT_DATA                                                        PROCEDURE               INVALID                                                                                 TEST_GRANITE                                                                    PROCEDURE               INVALID                                                                                   OBJECT_NAME                                                                     -------------------------------------------------------------------------------- OBJECT_TYPE             STATUS ----------------------- ------- TEST_PROC                                                                       PROCEDURE               INVALID                                                                                 BATCHORDER                                                                      PACKAGE BODY            INVALID                                                                                 DBCLEANUP                                                                       PACKAGE BODY            INVALID                                                                                 DB_UTIL                                                                         PACKAGE BODY            INVALID                                                                                 HIBERNATE_MIGRATION                                                             PACKAGE BODY            INVALID                                                                                 DUE_ORDER_SUMMARY                                                               VIEW                    INVALID                                                                                 FAILED_ORDER_SUMMARY                                                              OBJECT_NAME                                                                     -------------------------------------------------------------------------------- OBJECT_TYPE             STATUS ----------------------- ------- VIEW                    INVALID                                                                                 NONFAILED_ORDER_SUMMARY                                                         VIEW                    INVALID                                                                                 ORDER_DUE_BY_TYPE                                                               VIEW                    INVALID                                                                                 ORDER_STATUS_BY_TYPE                                                            VIEW                    INVALID                                                                                 WFMBISMIGRATION                                                                 PROCEDURE               INVALID                                                                                 WORKFLOWMIGRATION                                                               PROCEDURE               INVALID                                                                                   31 rows selected. Elapsed: 00:00:01.05

 

--Index Status before reorg

set timing on;

SELECT OBJECT_NAME, OBJECT_TYPE,status

    FROM USER_OBJECTS

 where object_name in (select index_name from user_indexes where table_name='HISTORYMESSAGE') and status<>'VALID';

 

no rows selected.

--Find ASM diskgroup space:

The Following query can be used to find out the total/free space in ASM Diskgroups: SQL:

set timing on;

 

ASM>select name,total_mb/1024 TOTAL_in_GB,free_mb/1024 FREE_in_GB,state from v$asm_diskgroup;

ASMCMD tool can also be used to find the free space.

 

 

 

--To check Fragmanted space:

 

 

select table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),0) "TOTAL_SIZE",

round((num_rows*avg_row_len/1024/1024),0) "ACTUAL_SIZE",

round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),0) "FRAGMENTED_SPACE" from

user_tables where round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)

> 100 order by 7 desc;

 

 

select

 table_name,round(((blocks*8)/1024/1024),2) "size (gb)" ,

 round(((num_rows*avg_row_len/1024))/1024/1024,2) "actual_data (gb)",

 round((((blocks*8)) - ((num_rows*avg_row_len/1024)))/1024/1024,2) "wasted_space (gb)",

 round(((((blocks*8)-(num_rows*avg_row_len/1024))/(blocks*8))*100 -10),2) "reclaimable space %",

 partitioned

from

 user_tables

where table_name='HISTORYMESSAGE' and

 (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))

order by 4 desc;

 

 

COLUMN OBJECT_NAME FORMAT A40

COLUMN OBJECT_NAME FORMAT A40

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='NIMBUS' and object_name like '%HISTORYMESSAGE%';

 

--Gather table stats:

exec DBMS_STATS.GATHER_TABLE_STATS('NIMBUS', 'HISTORYMESSAGE',  degree=>6, method_opt=> 'for all columns size 1', granularity => 'AUTO', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE=>TRUE);

 

PL/SQL procedure successfully completed.

Elapsed: 00:14:18.73

--Check the Table size and Index size

--Table and Index size output  before REORG :-

 

select segment_name,TABLESPACE_NAME ,segment_type, bytes/1024/1024/1024 size_gb from user_segments where segment_name = 'HISTORYMESSAGE' or

 segment_name in (select index_name from user_indexes where table_name='HISTORYMESSAGE');

HISTORYMESSAGE	JBBOS_DAT	TABLE	226.7764
I_HSTRMSG_PRODUCTTYPE	JBBOS_DAT	INDEX	17.90918
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	1.866638
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	2.965454
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	1.671753
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	2.402893
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	2.152893
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	1.903381
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	1.652893
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	2.340393
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	1.879578
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	2.090393
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	1.652466
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	1.528015
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	2.715515
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	2.248657
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	2.778198
I_HSTRMSG_BANNUMBER	JBBOS_IDX	INDEX PARTITION	1.828979
I_HSTRMSG_BANNUMBER	JBBOS_IDX	INDEX PARTITION	1.823547
I_HSTRMSG_BANNUMBER	JBBOS_IDX	INDEX PARTITION	1.867004
I_HSTRMSG_BANNUMBER	JBBOS_IDX	INDEX PARTITION	3.632019
I_HSTRMSG_BANNUMBER	JBBOS_IDX	INDEX PARTITION	2.139038
I_HSTRMSG_BANNUMBER	JBBOS_IDX	INDEX PARTITION	1.833801
I_HSTRMSG_BANNUMBER	JBBOS_IDX	INDEX PARTITION	5.700745
I_HSTRMSG_BANNUMBER	JBBOS_IDX	INDEX PARTITION	5.493835
I_HSTRMSG_SOVID	JBBOS_IDX	INDEX PARTITION	0.948853
I_HSTRMSG_SOVID	JBBOS_IDX	INDEX PARTITION	1.212463
I_HSTRMSG_SOVID	JBBOS_IDX	INDEX PARTITION	4.927307
I_HSTRMSG_SOVID	JBBOS_IDX	INDEX PARTITION	0.915039
I_HSTRMSG_SOVID	JBBOS_IDX	INDEX PARTITION	3.450012
I_HSTRMSG_SOVID	JBBOS_IDX	INDEX PARTITION	3.112549
I_HSTRMSG_SOVID	JBBOS_IDX	INDEX PARTITION	2.032104
I_HSTRMSG_SOVID	JBBOS_IDX	INDEX PARTITION	2.060242
I_HSTRMSG_SOERRID	JBBOS_IDX	INDEX PARTITION	6.10E-05
I_HSTRMSG_SOERRID	JBBOS_IDX	INDEX PARTITION	6.10E-05
I_HSTRMSG_SOERRID	JBBOS_IDX	INDEX PARTITION	6.10E-05
I_HSTRMSG_SOERRID	JBBOS_IDX	INDEX PARTITION	6.10E-05
I_HSTRMSG_SOERRID	JBBOS_IDX	INDEX PARTITION	6.10E-05
I_HSTRMSG_SOERRID	JBBOS_IDX	INDEX PARTITION	6.10E-05
I_HSTRMSG_SOERRID	JBBOS_IDX	INDEX PARTITION	6.10E-05
I_HSTRMSG_SOERRID	JBBOS_IDX	INDEX PARTITION	6.10E-05
I_HSTRMSG_TASKID	JBBOS_IDX	INDEX PARTITION	6.870972
I_HSTRMSG_TASKID	JBBOS_IDX	INDEX PARTITION	6.882751
I_HSTRMSG_TASKID	JBBOS_IDX	INDEX PARTITION	6.918457
I_HSTRMSG_TASKID	JBBOS_IDX	INDEX PARTITION	6.917358
I_HSTRMSG_TASKID	JBBOS_IDX	INDEX PARTITION	6.920959
I_HSTRMSG_TASKID	JBBOS_IDX	INDEX PARTITION	6.902527
I_HSTRMSG_TASKID	JBBOS_IDX	INDEX PARTITION	6.917542
I_HSTRMSG_TASKID	JBBOS_IDX	INDEX PARTITION	6.936523
I_HSTRMSG_SONUM	JBBOS_IDX	INDEX PARTITION	2.272644
I_HSTRMSG_SONUM	JBBOS_IDX	INDEX PARTITION	2.211853
I_HSTRMSG_SONUM	JBBOS_IDX	INDEX PARTITION	3.20636
I_HSTRMSG_SONUM	JBBOS_IDX	INDEX PARTITION	4.943176
I_HSTRMSG_SONUM	JBBOS_IDX	INDEX PARTITION	2.225281
I_HSTRMSG_SONUM	JBBOS_IDX	INDEX PARTITION	3.766724
I_HSTRMSG_SONUM	JBBOS_IDX	INDEX PARTITION	6.312683
I_HSTRMSG_SONUM	JBBOS_IDX	INDEX PARTITION	2.09314
HISTORYMESSAGE_PK	JBBOS_IDX	INDEX PARTITION	1.90271

 

--Last analyzed

SELECT LAST_ANALYZED FROM user_TABLES WHERE TABLE_NAME IN 'HISTORYMESSAGE';

--High water mark(HWM) check query :

 

 

select a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1) hgwtr

 from user_tables a,user_segments b where a.table_name=b.segment_name

 and a.blocks <> (b.blocks-a.empty_blocks-1)

 and  a.table_name like 'HISTORYMESSAGE'

 order by 1,2;

2nd Method: Redifinition:

grant execute on  DBMS_REDEFINITION to Nimbus;

grant redefine any table to nimbus;

grant CREATE ANY TABLE to NIMBUS;

grant ALTER ANY TABLE to NIMBUS;

grant DROP ANY TABLE to NIMBUS;

grant LOCK ANY TABLE to NIMBUS;

grant SELECT ANY TABLE to NIMBUS;

Grant CREATE ANY TRIGGER to NIMBUS;

Grant CREATE ANY INDEX to NIMBUS;

Grant CREATE VIEW to NIMBUS;

Grant CREATE MATERIALIZED VIEW to NIMBUS;

Grant execute any procedure to nimbus;

Grant select any dictionary to nimbus;

--Verify that the table is a candidate for online redefinition. In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.

--priviledged user is needed

set timing on;

 

1.

BEGIN

  DBMS_REDEFINITION.CAN_REDEF_TABLE('NIMBUS','HISTORYMESSAGE',

      DBMS_REDEFINITION.CONS_USE_PK);

END;

/

 

--If table is candidate then it will return PL/SQL procedure successfully completed otherwise it raises an error.

2,

set timing on;

 

alter session force parallel dml parallel 16;

alter session force parallel query parallel 16;

 

Session altered. Elapsed: 00:00:01.52 Session altered. Elapsed: 00:00:00.40

 

 

3.

set timing on;

 

CREATE TABLE HISTORYMESSAGE_REORG AS SELECT * FROM HISTORYMESSAGE WHERE 1=2 ;

Table created. Elapsed: 00:00:00.52

 

--Not Null constraint should not be created and dropped if created.

 

select constraint_name from user_constraints where table_name ='HISTORYMESSAGE_REORG';

 

alter table historymessage_reorg drop constraint sys_C00294849;

 

 

SELECT COUNT(*) FROM HISTORYMESSAGE_REORG ;

 

  COUNT(*)

 ----------

     0

 

 

 

--START REDEFINITION

set timing on;

 

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('NIMBUS','HISTORYMESSAGE', 'HISTORYMESSAGE_REORG');

 

PL/SQL procedure successfully completed.

Elapsed: 00:12:00.70

--COPY DEPENDENT OBJECTS

The following automatically copy dependent objects like mview, primary key, view, sequence, and triggers.

IGNORE_ERROR is set to TRUE to avoid primary key violation with the COPY_TABLE_DEPENDENTS command.

 

set timing on;

 

DECLARE

      N PLS_INTEGER;

BEGIN

    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('NIMBUS','HISTORYMESSAGE', 'HISTORYMESSAGE_REORG',

    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, N);

END;

/

 

PL/SQL procedure successfully completed. Elapsed: 04:50:19.35

--CHECK FOR ERRORS

set timing on;

COL OBJECT_NAME FOR A25

SET LIN200 PAGES 200

COL DDL_TEXT FOR A60

SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM DBA_REDEFINITION_ERRORS;

No error

 

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

--VALIDATE BOTH TABLES

The following validates the row count on both tables and syncing with the interim table:

set timing on;

SELECT COUNT(*) FROM NIMBUS.HISTORYMESSAGE_REORG ;

 

set timing on;

SELECT COUNT(*) FROM NIMBUS.HISTORYMESSAGE ;

 

COUNT(*) ----------  715618108 1 row selected. Elapsed: 00:01:03.19     COUNT(*) ----------  715621658 1 row selected. Elapsed: 00:02:27.26

 

 

set timing on;

 

EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('NIMBUS','HISTORYMESSAGE','HISTORYMESSAGE_REORG');

 

PL/SQL procedure successfully completed.

Elapsed: 00:02:27.91

--FINISH THE REDEFINITION

The following  finishes the redefinition:

set timing on;

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('NIMBUS','HISTORYMESSAGE','HISTORYMESSAGE_REORG');

 

PL/SQL procedure successfully completed.

Elapsed: 00:02:42.11

SQL>

COLUMN OBJECT_NAME FORMAT A40

COLUMN OBJECT_NAME FORMAT A40

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='NIMBUS' and object_name like '%HISTORYMESSAGE%';

 

 

--High water mark(HWM) check query post upgrade:

 

 

select a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1) hgwtr

 from user_tables a,user_segments b where a.table_name=b.segment_name

 and a.blocks <> (b.blocks-a.empty_blocks-1)

 and  a.table_name like 'HISTORYMESSAGE'

 order by 1,2;

 

 

 

--CHECK ERROR AND RECOMPILE THE SCHEMA

If any invalid objects are there recompile them. Other wise ignore.

 

--EXEC UTL_RECOMP.RECOMP_SERIAL('NIMBUS') ;

 

Gather table stats:

exec DBMS_STATS.GATHER_TABLE_STATS('NIMBUS', 'HISTORYMESSAGE',  degree=>6, method_opt=> 'for all columns size 1', granularity => 'AUTO', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE=>TRUE);

 

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='NIMBUS' and object_name='HISTORYMESSAGE';

 

 

--To check Fragmanted space:

 

select

 table_name,round(((blocks*8)/1024/1024),2) "size (gb)" ,

 round(((num_rows*avg_row_len/1024))/1024/1024,2) "actual_data (gb)",

 round((((blocks*8)) - ((num_rows*avg_row_len/1024)))/1024/1024,2) "wasted_space (gb)",

 round(((((blocks*8)-(num_rows*avg_row_len/1024))/(blocks*8))*100 -10),2) "reclaimable space %",

 partitioned

from

 user_tables

where table_name='HISTORYMESSAGE' and

 (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))

order by 4 desc;

 

--Table and Index size output  after REORG :-

 

select segment_name,TABLESPACE_NAME ,segment_type, bytes/1024/1024/1024 size_gb from user_segments where segment_name = 'HISTORYMESSAGE' or

 segment_name in (select index_name from user_indexes where table_name='HISTORYMESSAGE');

 

HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0693359375 I_HSTRMSG_BANNUMBER          JBBOS_IDX          INDEX PARTITION            1.1875 I_HSTRMSG_BANNUMBER          JBBOS_IDX          INDEX PARTITION            1.1875 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 I_HSTRMSG_SONUM     JBBOS_IDX          INDEX PARTITION            4.0673828125 I_HSTRMSG_SOERRID    JBBOS_IDX          INDEX PARTITION            6.103515625E-5 I_HSTRMSG_SOERRID    JBBOS_IDX          INDEX PARTITION            6.103515625E-5 I_HSTRMSG_TASKID       JBBOS_IDX          INDEX PARTITION            5 I_HSTRMSG_SOVID         JBBOS_IDX          INDEX PARTITION            1.625 I_HSTRMSG_SOVID         JBBOS_IDX          INDEX PARTITION            0.5390625 I_HSTRMSG_SOVID         JBBOS_IDX          INDEX PARTITION            3.125 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 I_HSTRMSG_SOERRID    JBBOS_IDX          INDEX PARTITION            6.103515625E-5 I_HSTRMSG_TASKID       JBBOS_IDX          INDEX PARTITION            4.970703125 I_HSTRMSG_BANNUMBER          JBBOS_IDX          INDEX PARTITION            5.0625 I_HSTRMSG_BANNUMBER          JBBOS_IDX          INDEX PARTITION            1.25 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 I_HSTRMSG_SONUM     JBBOS_IDX          INDEX PARTITION            1.33984375 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.11328125 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 I_HSTRMSG_TASKID       JBBOS_IDX          INDEX PARTITION            4.982421875 I_HSTRMSG_SONUM     JBBOS_IDX          INDEX PARTITION            1.25 I_HSTRMSG_SOVID         JBBOS_IDX          INDEX PARTITION            0.5625 I_HSTRMSG_BANNUMBER          JBBOS_IDX          INDEX PARTITION            2.6875 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 I_HSTRMSG_SONUM     JBBOS_IDX          INDEX PARTITION            1.25 HISTORYMESSAGE           JBBOS_DAT        TABLE    184.997314453125 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 I_HSTRMSG_TASKID       JBBOS_IDX          INDEX PARTITION            5 I_HSTRMSG_SONUM     JBBOS_IDX          INDEX PARTITION            2.375 I_HSTRMSG_SONUM     JBBOS_IDX          INDEX PARTITION            5.314453125 I_HSTRMSG_SONUM     JBBOS_IDX          INDEX PARTITION            1.3125 I_HSTRMSG_BANNUMBER          JBBOS_IDX          INDEX PARTITION            1.375 I_HSTRMSG_SOVID         JBBOS_IDX          INDEX PARTITION            2.1875 I_HSTRMSG_TASKID       JBBOS_IDX          INDEX PARTITION            4.9853515625 I_HSTRMSG_TASKID       JBBOS_IDX          INDEX PARTITION            4.9375 I_HSTRMSG_TASKID       JBBOS_IDX          INDEX PARTITION            5.0029296875 I_HSTRMSG_TASKID       JBBOS_IDX          INDEX PARTITION            4.982421875 I_HSTRMSG_SOVID         JBBOS_IDX          INDEX PARTITION            0.84375 I_HSTRMSG_SOERRID    JBBOS_IDX          INDEX PARTITION            6.103515625E-5 I_HSTRMSG_SOERRID    JBBOS_IDX          INDEX PARTITION            6.103515625E-5 I_HSTRMSG_SOERRID    JBBOS_IDX          INDEX PARTITION            6.103515625E-5 I_HSTRMSG_BANNUMBER          JBBOS_IDX          INDEX PARTITION            4.7685546875 I_HSTRMSG_SOERRID    JBBOS_IDX          INDEX PARTITION            6.103515625E-5 I_HSTRMSG_SONUM     JBBOS_IDX          INDEX PARTITION            2.875 I_HSTRMSG_SOVID         JBBOS_IDX          INDEX PARTITION            1.75 I_HSTRMSG_SOVID         JBBOS_IDX          INDEX PARTITION            4.4375 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 I_HSTRMSG_BANNUMBER          JBBOS_IDX          INDEX PARTITION            1.1875 HISTORYMESSAGE_PK   JBBOS_IDX          INDEX PARTITION            1.0625 I_HSTRMSG_PRODUCTTYPE        JBBOS_DAT        INDEX   13.5869140625 I_HSTRMSG_SOERRID    JBBOS_IDX          INDEX PARTITION            6.103515625E-5

 

--Count of indexes,triggers,synonyms and constraints

 

select index_name from user_indexes where table_name='HISTORYMESSAGE';

select trigger_name from user_triggers where table_name='HISTORYMESSAGE';

select constraint_name from user_constraints where table_name='HISTORYMESSAGE';

select synonym_name from user_synonyms where table_name='HISTORYMESSAGE';

 

 

INDEX_NAME                                                                     

--------------------------------------------------------------------------------

I_HSTRMSG_PRODUCTTYPE                                                          

I_HSTRMSG_BANNUMBER                                                            

I_HSTRMSG_SOVID                                                                

I_HSTRMSG_SOERRID                                                              

I_HSTRMSG_TASKID                                                               

I_HSTRMSG_SONUM                                                                

HISTORYMESSAGE_PK                                                              

 

7 rows selected.

no rows selected.

 

CONSTRAINT_NAME                                                                

--------------------------------------------------------------------------------

SYS_C00286531                                                                  

1 row selected.

no rows selected.

 

 

 

 

SELECT OBJECT_NAME, OBJECT_TYPE,status

    FROM USER_OBJECTS where status <>'VALID';

 

--Index Status before reorg

    SELECT OBJECT_NAME, OBJECT_TYPE,status

    FROM USER_OBJECTS

 where object_name in (select index_name from user_indexes where table_name='HISTORYMESSAGE');

 

--DROP THE INTERIM TABLE

 

DROP TABLE NIMBUS.HISTORYMESSAGE_REORG;

 

 

 

Provide below grant in Prod:

GRANT SELECT ON NIMBUS.HISTORYMESSAGE TO NIMBUS_READ;

 

GRANT DELETE, INSERT, SELECT, UPDATE ON NIMBUS.HISTORYMESSAGE TO NIMBUS_RW;

 

GRANT READ ON NIMBUS.HISTORYMESSAGE TO VS3090;

________________________________________________________________

 

--HISTORYMESSAGE script in ST6

 

DROP TABLE NIMBUS.HISTORYMESSAGE CASCADE CONSTRAINTS;

 

CREATE TABLE NIMBUS.HISTORYMESSAGE

(

  ID                          NUMBER            NOT NULL,

  ACTIVITYNAME                VARCHAR2(255 BYTE),

  DATE0                       TIMESTAMP(6),

  EXECUTEDATETIME             TIMESTAMP(6),

  HOSTNAME                    VARCHAR2(255 BYTE),

  MANUAL                      VARCHAR2(255 BYTE),

  NOOP                        NUMBER,

  RETRYCOUNT                  NUMBER,

  SERVICEORDERNUMBER          VARCHAR2(255 BYTE),

  STATUS                      VARCHAR2(255 BYTE),

  SYSTEMNAME                  VARCHAR2(255 BYTE),

  TASKDEFINITIONID            VARCHAR2(255 BYTE),

  TASKID                      VARCHAR2(255 BYTE),

  TASKNAME                    VARCHAR2(255 BYTE),

  UID0                        VARCHAR2(255 BYTE),

  SERVICEORDERERROR_ID        NUMBER,

  SERVICEORDERVERSION_ID      NUMBER,

  VERSION                     NUMBER,

  BANNUMBER                   VARCHAR2(255 BYTE),

  SUBSCRIPTION_ID             NUMBER,

  CIRCUITID                   VARCHAR2(255 BYTE),

  CREATEDBY                   VARCHAR2(64 BYTE),

  INTERNALUSEONLY             NUMBER,

  STATUSMESSAGE               VARCHAR2(1000 BYTE),

  VERSIONNUMBER               VARCHAR2(64 BYTE),

  ORDERACTIONREFERENCENUMBER  VARCHAR2(64 BYTE),

  EXTERNALORDERNUMBER         VARCHAR2(32 BYTE),

  PRODUCTTYPE                 VARCHAR2(32 BYTE),

  SUPPLEMENTAL LOG GROUP GGS_67874 (ID) ALWAYS,

  SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,

  SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS,

  SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS

)

TABLESPACE JBBOS_DAT

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MAXSIZE          UNLIMITED

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           )

LOGGING

NOCOMPRESS

NOCACHE

MONITORING;

 

 

CREATE UNIQUE INDEX NIMBUS.HISTORYMESSAGE_PK ON NIMBUS.HISTORYMESSAGE

(ID)

  TABLESPACE JBBOS_DAT

  PCTFREE    20

  INITRANS   10

  MAXTRANS   255

  STORAGE    (

              BUFFER_POOL      DEFAULT

             )

GLOBAL PARTITION BY HASH (ID) ( 

  PARTITION ID_01

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_02

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_03

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_04

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_05

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_06

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_07

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_08

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_09

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_10

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_11

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_12

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_13

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_14

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_15

    TABLESPACE JBBOS_IDX, 

  PARTITION ID_16

    TABLESPACE JBBOS_IDX

);

 

CREATE INDEX NIMBUS.I_HSTRMSG_BANNUMBER ON NIMBUS.HISTORYMESSAGE

(BANNUMBER)

  TABLESPACE JBBOS_DAT

  PCTFREE    20

  INITRANS   10

  MAXTRANS   255

  STORAGE    (

              BUFFER_POOL      DEFAULT

             )

GLOBAL PARTITION BY HASH (BANNUMBER) ( 

  PARTITION I_HSTRMSG_BANNUMBER_01

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_BANNUMBER_02

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_BANNUMBER_03

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_BANNUMBER_04

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_BANNUMBER_05

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_BANNUMBER_06

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_BANNUMBER_07

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_BANNUMBER_08

    TABLESPACE JBBOS_IDX

);

 

CREATE INDEX NIMBUS.I_HSTRMSG_PRODUCTTYPE ON NIMBUS.HISTORYMESSAGE

(PRODUCTTYPE)

LOGGING

TABLESPACE JBBOS_DAT

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MAXSIZE          UNLIMITED

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           );

 

CREATE INDEX NIMBUS.I_HSTRMSG_SOERRID ON NIMBUS.HISTORYMESSAGE

(SERVICEORDERERROR_ID)

  TABLESPACE JBBOS_DAT

  PCTFREE    20

  INITRANS   10

  MAXTRANS   255

  STORAGE    (

              BUFFER_POOL      DEFAULT

             )

GLOBAL PARTITION BY HASH (SERVICEORDERERROR_ID) ( 

  PARTITION I_HSTRMSG_SOERRID_01

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOERRID_02

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOERRID_03

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOERRID_04

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOERRID_05

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOERRID_06

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOERRID_07

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOERRID_08

    TABLESPACE JBBOS_IDX

);

 

CREATE INDEX NIMBUS.I_HSTRMSG_SONUM ON NIMBUS.HISTORYMESSAGE

(SERVICEORDERNUMBER)

  TABLESPACE JBBOS_DAT

  PCTFREE    20

  INITRANS   10

  MAXTRANS   255

  STORAGE    (

              BUFFER_POOL      DEFAULT

             )

GLOBAL PARTITION BY HASH (SERVICEORDERNUMBER) ( 

  PARTITION I_HSTRMSG_SONUM_01

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SONUM_02

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SONUM_03

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SONUM_04

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SONUM_05

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SONUM_06

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SONUM_07

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SONUM_08

    TABLESPACE JBBOS_IDX

);

 

CREATE INDEX NIMBUS.I_HSTRMSG_SOVID ON NIMBUS.HISTORYMESSAGE

(SERVICEORDERVERSION_ID)

  TABLESPACE JBBOS_DAT

  PCTFREE    20

  INITRANS   10

  MAXTRANS   255

  STORAGE    (

              BUFFER_POOL      DEFAULT

             )

GLOBAL PARTITION BY HASH (SERVICEORDERVERSION_ID) ( 

  PARTITION I_HSTRMSG_SOVID_01

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOVID_02

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOVID_03

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOVID_04

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOVID_05

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOVID_06

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOVID_07

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_SOVID_08

    TABLESPACE JBBOS_IDX

);

 

CREATE INDEX NIMBUS.I_HSTRMSG_TASKID ON NIMBUS.HISTORYMESSAGE

(TASKID)

  TABLESPACE JBBOS_DAT

  PCTFREE    20

  INITRANS   10

  MAXTRANS   255

  STORAGE    (

              BUFFER_POOL      DEFAULT

             )

GLOBAL PARTITION BY HASH (TASKID) ( 

  PARTITION I_HSTRMSG_TASKID_01

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_TASKID_02

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_TASKID_03

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_TASKID_04

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_TASKID_05

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_TASKID_06

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_TASKID_07

    TABLESPACE JBBOS_IDX, 

  PARTITION I_HSTRMSG_TASKID_08

    TABLESPACE JBBOS_IDX

);

 

GRANT SELECT ON NIMBUS.HISTORYMESSAGE TO APPLICATION_USER;

 

 

select

 table_name,round(((blocks*8)/1024/1024),2) "size (gb)" ,

 round(((num_rows*avg_row_len/1024))/1024/1024,2) "actual_data (gb)",

 round((((blocks*8)) - ((num_rows*avg_row_len/1024)))/1024/1024,2) "wasted_space (gb)",

 round(((((blocks*8)-(num_rows*avg_row_len/1024))/(blocks*8))*100 -10),2) "reclaimable space %",

 partitioned

from

 dba_tables

where table_name='HISTORYMESSAGE' and

 (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))

order by 4 desc;