Oracle: October 2014

Today when doing Initial load my extract got ABENDED with the error below

2014-10-14 19:46:37 ERROR OGG-00049 Trails cannot be used when SOURCEISTABLE/SOURCEISFILE is specified.

2014-10-14 19:46:37 ERROR OGG-01668 PROCESS ABENDING.

GGSCI (oracledev01.com) 1> view params initEXT

EXTRACT initEXT

SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")

SETENV(ORACLE_SID=orcl)

sourceistable

USERID ggs_admin,PASSWORD xxxxx

RMTHOST oracleqa01, mgrport 7809

RMTTRAIL /u01/dev/oracle/input/ggs/remote/cb/DEV_ORCL.dat, purge

TABLE DEV.TAB1;

TABLE DEV.TAB2;

Since here i'm sending the ".dat" files to the target server for the reporting purpose i'm not using Datapump and Replicat process, just sending files directly from my extract process (datapump process is always OPTIONAL)

SOURCEISTABLE: extracts the records from the source tables directly.

RMTFILE: Specifies the location and file name of the extract file that will be written during the load.

SOLUTION: Remove the EXTTRAIL or RMTTRAIL parameter.

When I replaced RMTTRAIL with RMTFILE, then my issue got resolved.

RMTFILE /u01/dev/oracle/input/ggs/remote/cb/DEV_ORCL.dat, purge

GGSCI (oracledev01.domain.com) 3> start initEXT

Sending START request to MANAGER ...

EXTRACT INITEXT starting

GGSCI (oracledev01.domain.com) 4> info initEXT

EXTRACT INITEXT Initialized 2014-10-14 17:40 Status RUNNING

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

Reference:

Goldengate Extract Abends with Error Message: Trails Cannot Be Used When SOURCEISTABLE/SOURCEISFILE Is Specified (Doc ID 1457442.1)

While doing server Housekeeping and found my Listener log files are consuming lot of space and contains logs from past few months.

According to Oracle doc, “Listener log files ('.xml' files) are not purged via the 'automatic' purge”. Therefore in 11g releases, these log files (and trace files) will not get automatically purged and so you need to manually delete them when they are no longer required.

Below are my listener log files on node1 of my 2 node RAC

OS version: RHEL 5.x
Oracle version: 11.2.0.3

[root@oracledev01 alert]# pwd

/u01/app/oracle/diag/tnslsnr/oracledev01/listener/alert

[root@oracledev01 alert]# ls -lrth

total 1.1G

-rw-r----- 1 grid oinstall 11M Aug 13 02:56 log_1.xml

-rw-r----- 1 grid oinstall 11M Aug 13 09:49 log_2.xml

-rw-r----- 1 grid oinstall 11M Aug 13 16:33 log_3.xml

-rw-r----- 1 grid oinstall 11M Aug 13 23:35 log_4.xml

-rw-r----- 1 grid oinstall 11M Aug 14 06:20 log_5.xml

-rw-r----- 1 grid oinstall 11M Aug 14 13:38 log_6.xml

-rw-r----- 1 grid oinstall 11M Aug 14 20:57 log_7.xml

-rw-r----- 1 grid oinstall 11M Aug 15 04:05 log_8.xml

-rw-r----- 1 grid oinstall 11M Aug 15 11:47 log_9.xml

-rw-r----- 1 grid oinstall 11M Aug 15 19:15 log_10.xml

-rw-r----- 1 grid oinstall 11M Aug 16 02:48 log_11.xml

-rw-r----- 1 grid oinstall 11M Aug 16 11:11 log_12.xml

-rw-r----- 1 grid oinstall 11M Aug 16 21:36 log_13.xml

-rw-r----- 1 grid oinstall 11M Aug 17 08:05 log_14.xml

-rw-r----- 1 grid oinstall 11M Aug 17 17:57 log_15.xml

-rw-r----- 1 grid oinstall 11M Aug 18 03:34 log_16.xml

-rw-r----- 1 grid oinstall 11M Aug 18 12:47 log_17.xml

……….

o/p trimmed

……….

-rw-r----- 1 grid oinstall 11M Oct 4 16:30 log_93.xml

-rw-r----- 1 grid oinstall 11M Oct 5 11:11 log_94.xml

-rw-r----- 1 grid oinstall 11M Oct 5 15:28 log_95.xml

-rw-r----- 1 grid oinstall 11M Oct 5 20:27 log_96.xml

-rw-r----- 1 grid oinstall 11M Oct 6 01:28 log_97.xml

-rw-r----- 1 grid oinstall 11M Oct 6 06:44 log_98.xml

-rw-r----- 1 grid oinstall 11M Oct 6 12:09 log_99.xml

-rw-r----- 1 grid oinstall 11M Oct 6 17:33 log_100.xml

-rw-r----- 1 grid oinstall 11M Oct 6 23:33 log_101.xml

-rw-r----- 1 grid oinstall 11M Oct 7 17:04 log_102.xml

-rw-r----- 1 grid oinstall 11M Oct 8 10:42 log_103.xml

-rw-r----- 1 grid oinstall 11M Oct 9 03:27 log_104.xml

-rw-r----- 1 grid oinstall 11M Oct 9 12:21 log_105.xml

-rw-r----- 1 grid oinstall 11M Oct 9 20:59 log_106.xml

-rw-r----- 1 grid oinstall 11M Oct 10 04:27 log_107.xml

-rw-r----- 1 grid oinstall 11M Oct 10 11:57 log_108.xml

-rw-r----- 1 grid oinstall 6.0M Oct 10 16:19 log.xml

NOTE: log.xml files will be archived into a log_1.xml, log_2.xml and so on... into the same location as soon as it grows to 10MBytes

[root@oracledev01 alert]# . oraenv

ORACLE_SID = [root] ? DBNAME1

The Oracle base has been set to /u01/app/oracle

[root@oracledev01 alert]# adrci

ADRCI: Release 11.2.0.3.0 - Production on Fri Oct 10 16:38:55 2014

ADR base = "/u01/app/oracle"

adrci> show home

ADR Homes:

diag/tnslsnr/oracledev01/listener

adrci> set home diag/tnslsnr/oracledev01/listener

adrci> SHOW CONTROL

ADR Home = /u01/app/oracle/diag/tnslsnr/oracledev01/listener:

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

ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME

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

2146670485 720 8760 2011-04-22 20:50:43.449927 +00:00 1 2 80 1 2011-04-22 20:50:43.449927 +00:00

1 rows fetched

From the above output we see LAST_AUTOPRG_TIME & LAST_MANUPRG_TIME are empty, meaning that they are not being purged automatically and no one purged manually

adrci> purge -age 10080 -type ALERT (purging older than 7 days, 10080 mins)

adrci> exit

Verify:

[root@oracledev01 alert]# ls -lrth

total 197M

-rw-r----- 1 grid oinstall 11M Oct 3 20:41 log_90.xml

-rw-r----- 1 grid oinstall 11M Oct 4 03:01 log_91.xml

-rw-r----- 1 grid oinstall 11M Oct 4 09:44 log_92.xml

-rw-r----- 1 grid oinstall 11M Oct 4 16:30 log_93.xml

-rw-r----- 1 grid oinstall 11M Oct 5 11:11 log_94.xml

-rw-r----- 1 grid oinstall 11M Oct 5 15:28 log_95.xml

-rw-r----- 1 grid oinstall 11M Oct 5 20:27 log_96.xml

-rw-r----- 1 grid oinstall 11M Oct 6 01:28 log_97.xml

-rw-r----- 1 grid oinstall 11M Oct 6 06:44 log_98.xml

-rw-r----- 1 grid oinstall 11M Oct 6 12:09 log_99.xml

-rw-r----- 1 grid oinstall 11M Oct 6 17:33 log_100.xml

-rw-r----- 1 grid oinstall 11M Oct 6 23:33 log_101.xml

-rw-r----- 1 grid oinstall 11M Oct 7 17:04 log_102.xml

-rw-r----- 1 grid oinstall 11M Oct 8 10:42 log_103.xml

-rw-r----- 1 grid oinstall 11M Oct 9 03:27 log_104.xml

-rw-r----- 1 grid oinstall 11M Oct 9 12:21 log_105.xml

-rw-r----- 1 grid oinstall 11M Oct 9 20:59 log_106.xml

-rw-r----- 1 grid oinstall 11M Oct 10 04:27 log_107.xml

-rw-r----- 1 grid oinstall 11M Oct 10 11:57 log_108.xml

-rw-r----- 1 grid oinstall 6.3M Oct 10 16:39 log.xml

$ date

Fri Oct 10 17:36:34 GMT 2014

adrci> SHOW CONTROL

ADR Home = /u01/app/oracle/diag/tnslsnr/oracledev01/listener:

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

ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME

2146670485 720 8760 2011-04-22 20:50:43.449927 +00:00 2014-10-10 16:39:37.644372 +00:00 1 2 80 1 2011-04-22 20:50:43.449927 +00:00

1 rows fetched

I see today’s date now in the LAST_MANUPRG_TIME column

NOTE: Similarly we can purge files in different component using same command

ALERT — files in the ./alert directory

INCIDENT — files in the ./incident/incdir_<incid> directory. Please note that 'purge -age "n" -type INCIDENT' does not remove the *.lck files from the /lck directory in the incident schema

TRACE — files in the ./trace directory

CDUMP — files in the ./cdump directory

UTSCDMP — files in the ./trace/cdmp_<timestamp> directories

STAGE — files in the ./stage directory

SWEEP — files in the ./sweep directory

HM — files in the ./hm directory and metadata in the HM schema

IPS — files in the ./incpkg directory and metadata in the IPS schema

Referrence:

How To Purge Listener Log.Xml File? (Doc ID 816871.1)

Why Are My Listener Logs & Traces Not Purged By The ADR? (Doc ID 1438242.1)

ADR Auto Purge Does Not Work On All RAC Instances, ASM and Standby Databases (Doc ID 1326983.1)

Tuesday, October 14, 2014

OGG-00049 Trails cannot be used when SOURCEISTABLE/SOURCEISFILE is specified

Friday, October 10, 2014

Purge 11g LISTENER Logs using ADRCI