Scenario where, changed primary keys for table / dropped supplemental logging & re-enabled supplemental logging with new keycolumns on table. Extract Abended with "ERROR OGG-00730 "& report as follows 2016-03-21 08:52:37 INFO OGG-01517 Position of first record processed Sequence 15315, RBA 187571728, SCN 3378.225080410, Mar 21, 2016 6:37:42 AM. Source Context : SourceModule : [er.redo.ora.out] SourceID : [/scratch/aime1/adestore/views/aime1_staxj04/oggcore/OpenSys/src/app/er/re do/oracle/redooraout.c] SourceFunction : [get_next_complete_record(mempool_t *, memtran_t *, ora_xid_t *, int32_t, log_context_t *, chkpt_context_t *, BOOL, BOOL *, tran_item_hdr_t *, file_def **, row_info_t *, row_in fo_t *, row_info_t *, BOOL *, BOOL *, BOOL *, char *)] SourceLine : [2916] 2016-03-21 08:54:21 ERROR OGG-00730 No minimum supplemental logging is enabled. This may cause extract process to handle key update incorrectly if key column is not in first row piece. RootCause: checked for Minimal supplemental logging for database. But in my scenario, where Minimal Supplemental Logging is enabled for database.If it is NO, please Enable Minimal supplemental logging for database & start extract with Begin now :). Sync table after .. SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEMENTAL_LOG_DATA_MIN
YES
From METALINK I FOUND FOLLOWING INFORMATION ( Doc ID 1571665.1 )
This is due to a regression bug( handled via Bug 16857778)
The ogg v11.2.1.0 4 and lower could process the same archive log without any issues.
This bug is fixed after v11.2.1.0.9, currently I am using OGG Version v11.2.1.0.6
Use the undocumented parameter "TRANLOGOPTIONS DISABLESUPPLOGCHECK". Once it bypass the problematic archive log file remove the same.
Note:
We have seen the extract hanging in one case when using DISABLESUPPLOGCHECK and so please use it with caution.
Resolution: Depend on your scenario, work around 1) alter extract with begin now (or) 2) Add parameter ""TRANLOGOPTIONS DISABLESUPPLOGCHECK" to extract, restart the Extract :) --Nikhil Tatineni-- --GoldenGate 12c--
Problem Summary & Problem Description Developers started to perform DML operation on Huge Volume tables, Oracle Goldengate Extract Abended with Error OGG-00665. Errors in Report File as follows 2016-03-21 09:44:31 ERROR OGG-00665 OCI Error describe for query (status = 4031-ORA-04031: unable to allocate 4000 bytes of shared memory ("shared pool","SELECT banner FROM v$version","sga heap(1,0) ","kglsim heap")), SQL<Not available>. 2016-03-21 09:44:31 ERROR OGG-01668 PROCESS ABENDING. Resolution: we increased memory structure "SGA" for database, issue is resolved --Nikhil Tatineni-- --Oracle GoldenGate--
In order to change the plan we have flush the current cursor in the library cache.
We can find existing cursor information with the help of SQL_ID :)
Steps as follows Find out sql_id of the existing sql in memory using v$sqlarea;
Using v$sqlarea we can find out "sql_id" as follows select sql_id, force_matching_signature, sql_text from v$sqlarea where SQL_TEXT like '%Select max%';
using DBMS_XPLAN.DISPLAY_CURSOR we can find "sql_id" stored in memory
SQL>select * from TABLE(dbms_xplan.display_cursor('SQL_ID'));
Flush the cache from memory and using dbms_shared_pool.purge
Scenario: Bouncing of database failed with following errors [admind@tnc63 goldengate]$ . oraenv
ORACLE_SID = [admin] ? admin
The Oracle base remains unchanged with value /u01/app/admind
[admind@tnc63 goldengate]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 21 21:33:01 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'tnc63.sfdc.sbc.com:1521'
SQL> !
In order to start Database & to fix this issue
1) commented out local_listener parameter in pfile i.e ORACLE_HOME/dbs location 2) rename server parameter file (spfile) 3) start database using pfile from step 1
[admind@tnc63 dbs]$ vi initadmin.ora
[admind@tnc63 dbs]$ cat initadmin.ora
admin.__data_transfer_cache_size=0
admin.__db_cache_size=134217728
admin.__java_pool_size=16777216
admin.__large_pool_size=33554432
admin.__oracle_base='/u01/app/admind'#ORACLE_BASE set from environment
Replicat Abended With following Error 2016-02-17 08:09:55 INFO OGG-03035 Operating system character set identified as ISO-8859-1. Locale: en_US, LC_ALL:. 2016-02-17 08:09:55 INFO OGG-02696 NON-ANSI SQL parameter syntax is used for parameter parsing. replicat rotr131 sourcedefs dirdef/nsp-rwpk.def Source Context : SourceModule : [er.init] SourceID : [/scratch/aime1/adestore/views/aime1_staxj04/oggcore/OpenSys/src/app/er/init.cpp] SourceFunction : [get_infile_params(time_elt_def *, time_elt_def *, char **)] SourceLine : [1596] 2016-02-17 08:09:55 ERROR OGG-00303 Problem at line 103. Bad column definition: PREV_VDSL_UP_RATE 134 11 684 3 0 1 0 8 8ED 192 19 26 0 0 1 0 19 19 19 0 5 0 0 1 0 0 0. 2016-02-17 08:09:55 ERROR OGG-01668 PROCESS ABENDING. Root Cause:Definition file which is used by replicat is corrupted Fix:Rename the existing definition file in ogghome/dirdef directory and re-create New defintion file from Source database using defgen utility & place it in GoldenGateHome/dirdef directory Regards, Nikhil Tatineni
Assign Newly Created Temporary Tablespace to Database as follows
SQL> alter database default temporary tablespace temptbs;
Drop Old tablespace Temporary tablespace on database # Use following Queries to monitor the size of temporary tablespace Adding additional space to temporary tablespace on database #
SQL> alter tablespace temptbs add tempfile '+NSP' size 100m;
