FlimaTech Blog

This is just an example on how to find the record that ABENDED Oracle GoldenGate in the trace file.

Replicat process ABENDED

 

On ggser.log file:

 

2012-03-23 09:41:21  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, R1WV90SP.prm:  OCI Error ORA-00001: unique constraint (SPOE_WV90.SYPK000000000000000000004388) violated (status = 1), SQL <INSERT INTO “SPOE_WV90″.”SYT_SYSYSVERSION” (“IDAPP”,”VERSIONMAJOR”,”VERSIONMINOR”,”DATAWAREHOUSE”,”COPYRIGHTNOTICE”) VALUES (:a0,:a1,:a2,:a3,:a4)>.

2012-03-23 09:41:21  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, R1WV90SP.prm:  Aborted grouped transaction on ‘SPOE_WV90.SYT_SYSYSVERSION’, Database error 1 (OCI Error ORA-00001: unique constraint (SPOE_WV90.SYPK000000000000000000004388) violated (status = 1), SQL <INSERT INTO “SPOE_WV90″.”SYT_SYSYSVERSION” (“IDAPP”,”VERSIONMAJOR”,”VERSIONMINOR”,”DATAWAREHOUSE”,”COPYRIGHTNOTICE”) VALUES (:a0,:a1,:a2,:a3,:a4)>).

2012-03-23 09:41:21  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, R1WV90SP.prm:  Repositioning to rba 695714 in seqno 0.

2012-03-23 09:41:24  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, R1WV90SP.prm:  SQL error 1 mapping WV90.SYT_SYSYSVERSION to SPOE_WV90.SYT_SYSYSVERSION OCI Error ORA-00001: unique constraint (SPOE_WV90.SYPK000000000000000000004388) violated (status = 1), SQL <INSERT INTO “SPOE_WV90″.”SYT_SYSYSVERSION” (“IDAPP”,”VERSIONMAJOR”,”VERSIONMINOR”,”DATAWAREHOUSE”,”COPYRIGHTNOTICE”) VALUES (:a0,:a1,:a2,:a3,:a4)>.

 

Check the Disregard file:

 

Oracle GoldenGate Delivery for Oracle process started, group R1WV90SP discard file opened: 2012-03-23 13:51:29

Current time: 2012-03-23 13:51:33

Discarded record from action ABEND on error 1

 

OCI Error ORA-00001: unique constraint (SPOE_WV90.SYPK000000000000000000004388) violated (status = 1), SQL <INSERT INTO “SPOE_WV90″.”SYT_SYSYSVERSION” (“IDAPP”,”VERSIONMAJOR”,”VERSIONM

INOR”,”DATAWAREHOUSE”,”COPYRIGHTNOTICE”) VALUES (:a0,:a1,:a2,:a3,:a4)>

Aborting transaction on /gg/ORATQA70rep/dirdat/P1WV91SP/p1 beginning at seqno 0 rba 695714

                         error at seqno 0 rba 6268071

Problem replicating WV90.SYT_SYSYSVERSION to SPOE_WV90.SYT_SYSYSVERSION

Mapping problem with insert record (target format)…

*

IDAPP = AppFrameDBManager

VERSIONMAJOR = 2

VERSIONMINOR = 1

DATAWAREHOUSE = 0

COPYRIGHTNOTICE = Copyright 1991-2011 Flimatech Computer Enterprises Ltd.

*

 

Continuing to discard records up to the last discarded record from action ABEND

 

Next Record:

Operation discarded from seqno 0 rba 695714

Aborted insert from WV90.SYT_SYSECGROUP to SPOE_WV90.SYT_SYSECGROUP (target format)…

*

IDRECMAIN = F379F8222AA841BC95FFBA9BD41D0EB6

IDREC = 375EEE31534E4F629CBBCDCB96CA2F47

SECGROUPNAME = TIGHT

CREATEENTITY = NULL

UPDATESECURITYTYP = NULL

UPDATELOCK = NULL

IGNORELOCK = NULL

NOTE = NULL

SYSLOCKMEUI = NULL

SYSLOCKCHILDRENUI = NULL

SYSLOCKME = NULL

SYSLOCKCHILDREN = NULL

SYSLOCKDATE = 2011-09-28 21:29:05

SYSMODDATE = 2011-09-28 21:29:05

SYSMODUSER = aherring

SYSCREATEDATE = 2011-09-28 21:29:05

SYSCREATEUSER = aherring

SYSTAG = NULL

*

 Get the Trace file with the record problem:

 

GGSCI (ls1985p.flimatech.com) 2> info R1WV90SP, detail

 

REPLICAT   R1WV90SP  Last Started 2012-03-23 14:03   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:03 ago)

Log Read Checkpoint  File /gg/ORATQA70rep/dirdat/P1WV91SP/p1000000

2012-03-23 09:34:36.000301  RBA 6268447

 

Extract Source                          Begin             End

 

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  2012-03-23 09:05  2012-03-23 09:34

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  2012-03-23 09:05  2012-03-23 09:34

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  2012-03-23 09:05  2012-03-23 09:34

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  2012-03-23 09:05  2012-03-23 09:34

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  2012-03-23 09:05  2012-03-23 09:34

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  2012-03-23 09:05  2012-03-23 09:34

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  2012-03-23 09:05  2012-03-23 09:34

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  2012-03-23 09:05  2012-03-23 09:34

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  2012-03-23 09:05  2012-03-23 09:34

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  2012-03-23 09:05  2012-03-23 09:34

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  2012-03-22 10:21  2012-03-23 09:34

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  * Initialized *   2012-03-22 10:21

/gg/ORATQA70rep/dirdat/P1WV91SP/p1000000  * Initialized *   First Record

 

 

Current directory    /gg/ggbin/gg111.1.1.1.2_11g

 

Report file          /gg/ggbin/gg111.1.1.1.2_11g/dirrpt/R1WV90SP.rpt

Parameter file       /gg/ggbin/dirprm/R1WV90SP.prm

Checkpoint file      /gg/ggbin/gg111.1.1.1.2_11g/dirchk/R1WV90SP.cpr

Checkpoint table     GG_USER.CHECKPOINT

Process file         /gg/ggbin/gg111.1.1.1.2_11g/dirpcs/R1WV90SP.pcr

Stdout file          /gg/ggbin/gg111.1.1.1.2_11g/dirout/R1WV90SP.out

Error log            /gg/ggbin/gg111.1.1.1.2_11g/ggserr.log

Finding the record on LogDump:

 

./logdump

Logdump 70 >open /gg/ORATQA70rep/dirdat/P1WV91SP/p1000000

Current LogTrail is /gg/ORATQA70rep/dirdat/P1WV91SP/p1000000

Logdump 71 >reclen 1000

Reclen set to 1000

Logdump 72 >pos 6268071  # Put in the RBA position

Reading forward from RBA 6268071

Logdump 73 >fileheader detail

Logdump 74 >ghdr on

Logdump 75 >detail on

Logdump 76 >usertoken detail

Logdump 77 >n

___________________________________________________________________

Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)

UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)

RecLength  :   128  (x0080)   IO Time    : 2012/03/23 09:34:36.000.301

IOType     :     5  (x05)     OrigNode   :   255  (xff)

TransInd   :     .  (x02)     FormatType :     R  (x52)

SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)

AuditRBA   :       1000       AuditPos   : 169558676

Continued  :     N  (x00)     RecCount   :     1  (x01)

 

2012/03/23 09:34:36.000.301 Insert               Len   128 RBA 6268071

Name: WV90.SYT_SYSYSVERSION

After  Image:                                             Partition 4   GU e

0000 0015 0000 0011 4170 7046 7261 6d65 4442 4d61 | ……..AppFrameDBMa

6e61 6765 7200 0100 0a00 0000 0000 0000 0000 0200 | nager……………

0200 0a00 0000 0000 0000 0000 0100 0300 0a00 0000 | ………………..

0000 0000 0000 0000 0400 3900 0000 3543 6f70 7972 | ……….9…5Copyr

6967 6874 2031 3939 312d 3230 3131 2050 656c 6f74 | ight 1991-2011 Flimattech

6f6e 2043 6f6d 7075 7465 7220 456e 7465 7270 7269 | Computer Enterpri

7365 7320 4c74 642e                               | ses Ltd.

Column     0 (x0000), Len    21 (x0015)

Column     1 (x0001), Len    10 (x000a)

Column     2 (x0002), Len    10 (x000a)

Column     3 (x0003), Len    10 (x000a)

Column     4 (x0004), Len    57 (x0039)

 

User tokens:  144 bytes

TKNUSERNAME         : SPELLE99

TKNDBNAME           : ORATQA70

TKNTRANSCSN         : 12413571212722

TKNTRANSID          : 6.24.4025

TKNRSN              : 12413571212718

TKNCTS              : 2012-03-23 09:34:38.593132

 

Logdump 78 >

 

Sample code that shows using BULK COLLECT and performance of using different values for LIMIT.

declare
cursor l_cur is select * from scott.emp;
type emp_tbl is table of l_cur%rowtype index by pls_integer;
l_emp emp_tbl;
limit_in number;
i number;
begin
limit_in := &limit_param;
open l_cur;
loop
fetch l_cur bulk collect into l_emp limit limit_in;
for i in 1..l_emp.count
loop
null;
end loop;
exit when l_emp.count < limit_in;
end loop;
close l_cur;
end;

Limit value 1: 25.76 seconds
Limit value 10: 03.54 seconds
Limit value 100: 01.28 seconds
Limit value 1000: 00.95 seconds
Limit value 2000: 01.00 seconds
Limit value 3000: 01.03 seconds
Limit value 5000: 01.06 seconds
Limit value 10000: 01.20 seconds

As you notice in the case above as one increases the value of LIMIT after 1000, the execution time increases slightly. The following was timed using “set timing on” on SQL*Plus.

Using the following steps one can setup vim/gvim to run on windows.

1) Add the following new variable VIM on your system. Example: VIM=C:\apps\vim\vim73 (Where C:\apps\vim\vim73 is where vim/gvim is installed)
2) Copy the file %VIM%\vimrc_example.vim as _vimrc in %VIM% folder
3) Add the following 2 lines at the end in %VIM%\_vimrc so it sets up vim for windows
source $VIM/mswin.vim
behave mswin

Using “tracefile_identifier” Oracle will add that to the name of the user’s trace filename so the file can easily be identified. For example if one needs to trace multiple users session who have different schema or OS user then using “tracefile_identifier” one can identify the trace file specific to a user.

Example: The following trigger will create a trace file with the user’s OSuser and machine part of the filename for any user who login with “SCOTT”

CREATE OR REPLACE TRIGGER sys.session_trace_on
– to be created by sys user
AFTER LOGON ON database
DECLARE
v_machinename VARCHAR2(64);
v_ora_username VARCHAR2(30) DEFAULT NULL;
v_os_username VARCHAR2(30);
v_sid NUMBER;
v_serial NUMBER;
v_program VARCHAR2(48);
v_numuser NUMBER;
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session
WHERE sid = userenv('sid')
and username = 'SCOTT';
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, v_os_username, v_machinename, v_program;
IF c1%FOUND THEN
— DBMS_SESSION.set_sql_trace (TRUE);
v_machinename := replace(replace(v_machinename, '\', '_'), '/', '_');
v_os_username := replace(replace(v_os_username, '\', '_'), '/', '_');
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''' || trim(v_os_username) || '''';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
CLOSE c1;
END;
/

Trace file created:
ls testdb_ora_5480_CAL-AMIN_Administrator.trc

In “ALTER SYSTEM” one can include a comment which gets recorded in the spfile and also in v$parameter as shown below. It can be used to record why parameter was changed.

Example:
SQL> alter system set open_cursors=1000 comment='07-Jan-2012 Changed AJ needed for application XYZ';

SQL> select value, update_comment from v$parameter where name = 'open_cursors';

VALUE
——————————————————————————–
UPDATE_COMMENT
——————————————————————————–
400

$ strings spfileTESTDB.ORA | grep open_cursors
*.open_cursors=400#07-Jan-2012 Changed AJ needed for application XYZ

The following shows how to add in a table a new column that is NOT NULL when a table already has rows without providing a default value.

SQL> create table scott.testn ( x number not null);

Table created.

SQL> insert into scott.testn values ( 11 );

1 row created.

SQL> commit;

Commit complete.

-- First step add new column without the NULL constraint
SQL> alter table scott.testn add nn number;

Table altered.

-- Then modify the column to be NOT NULL using NOVALIDATE
SQL> alter table scott.testn modify nn not null novalidate;

Table altered.

-- Shows the table doesn't show the NOT NULL constraint on the column NN yet
SQL> desc scott.testn;
Name Null? Type
—————————————– ——– ———————
X NOT NULL NUMBER
NN NUMBER

-- As shown above even though not shown the NOT NULL constraint is there as new values cannot be inserted in the table that are NULL
SQL> insert into scott.testn values (12, null);
insert into scott.testn values (12, null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“SCOTT”.”TESTN”.”NN”)

-- Now we can enable the constraint by updating the rows that have NULL value so we need to find the constraint name
SQL> select column_name, constraint_name from dba_cons_columns where table_name = 'TESTN' and owner = 'SCOTT';

COLUMN_NAME CONSTRAINT_NAME
——————– ——————————
X SYS_C005261
NN SYS_C005262

-- Rows update
SQL> update scott.testn set nn = 1 where nn is null;

1 row updated.

SQL> commit;

Commit complete.

--
SQL> alter table scott.testn modify constraint SYS_C005262 validate;

Table altered.

-- Shows the NOT NULL constraint on the column NN
SQL> desc scott.testn;
Name Null? Type
—————————————– ——– —————————-
X NOT NULL NUMBER
NN NOT NULL NUMBER

-- shows that one can't add a new column not null as table is not empty
SQL> alter table scott.testn add mm number not null;
alter table scott.testn add mm number not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

-- shows it can't add a new column not null with novalidate as table is not empty
SQL> alter table scott.testn add mm number not null novalidate;
alter table scott.testn add mm number not null novalidate
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

PRAGMA EXCEPTION_INIT – allows one to map ORA- error and it can be raised in PL/SQL code. The SQL Error number passed in “EXCEPTION_INIT” is the same as error code except for “NO_DATA_FOUND” ORA-01403 which is 100.
See http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm#BABGIIBI – Summary of Predefined PL/SQL Exceptions

Example:
declare
no_rows_found exception;
pragma exception_init(no_rows_found, 100);
begin
raise no_rows_found;
end;
/
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

declare
too_many_rows exception;
pragma exception_init(too_many_rows, -1422);
begin
raise too_many_rows;
end;
/
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

When logging in SQL*PLus if one receives the following message.

SQL> connect scott/tiger
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM

This can be fixed by executing as SYSTEM
SQL> @?/sqlplus/admin/PUPBLD.SQL
..

And after the script is run reconnect as the user.
SQL> connect scott/tiger
Connected.

One can automate taking of snapshots of STATSPACK by executing DBMS_JOB or by scheduling it through crontab.

Schedule automatic STATSPACK through DBMS_JOB – By executing @?/rdbms/admin/spauto.sql it will schedule to run statspack once every hour which is the default.

Output:
SQL> connect perstat@TESTDB
Password: **********
SQL> @?/rdbms/admin/spauto.sql

PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

JOBNO
———-
2

Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter – the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 1000

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

JOB NEXT_DATE NEXT_SEC
———- ——— ——–
2 27-DEC-11 19:00:00

One can view the job by executing the following SQL.

SQL> select * FROM User_jobs WHERE job = 2;

JOB LOG_USER PRIV_USER
———- —————————— ——————————
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
—————————— ——— ——– ——— ——– ———
NEXT_SEC TOTAL_TIME B
——– ———- -
INTERVAL
——————————————————————————–
FAILURES
———-
WHAT
——————————————————————————–
NLS_ENV
——————————————————————————–
MISC_ENV INSTANCE
—————————————————————- ———-
1 SYS SYS
SYS 27-DEC-11
19:00:00 0 N
trunc(SYSDATE+1/24,'HH')

statspack.snap;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000202000000 1

To change the snap interval one can execute dbms_job.interval in the example below it changes to collect every 1/2 hr
SQL> exec dbms_job.interval(1, 'trunc(SYSDATE+1/48,''HH'')');

PL/SQL procedure successfully completed.

SQL> select * FROM User_jobs;

JOB LOG_USER PRIV_USER
———- —————————— ——————————
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
—————————— ——— ——– ——— ——– ———
NEXT_SEC TOTAL_TIME B
——– ———- -
INTERVAL
——————————————————————————–
FAILURES
———-
WHAT
——————————————————————————–
NLS_ENV
——————————————————————————–
MISC_ENV INSTANCE
—————————————————————- ———-
1 SYS SYS
SYS 27-DEC-11
19:00:00 0 N
trunc(SYSDATE+1/48,'HH')

statspack.snap;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000202000000 1

To schedule it through crontab: One would need to invoke the package “statspack.snap” via SQL*Plus

One can truncate data in STATSPACK or delete range of snaps stored.

To truncate STATSPACK data

Using $ORACLE_HOME/rdbms/admin/sptrunc.sql, one can truncate STATSPACK data. To run the script you will need to connect as PERFSTAT user.

perfstat@TESTDB> @?/rdbms/admin/sptrunc.sql

Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables. You may
wish to export the data before continuing.

About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If would like to exit WITHOUT truncating the tables, enter any text at the
begin_or_exit prompt (e.g. &'exit&'), otherwise if you would like to begin
the truncate operation, press

Enter value for begin_or_exit:

To delete STATSPACK data – This will prompt for starting snap id and end snap id.
One can run the following SQL that will show all the snap ids on the current instance.
select s.snap_id
, to_char(s.snap_time,&' dd Mon YYYY HH24:mi:ss&') snap_date
, s.baseline
, s.snap_level #level#
, di.host_name host_name
, s.ucomment
from perfstat.stats$snapshot s
, perfstat.stats$database_instance di
, v$database v
, v$instance i
where s.dbid = v.dbid
and di.dbid = v.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.startup_time = s.startup_time
order by di.db_name, i.instance_name, s.snap_id
/

SNAP_ID SNAP_DATE B level
———- ——————— – ———-
HOST_NAME
—————————————————————-
UCOMMENT
——————————————————————————–
1 27 Dec 2011 18:30:56 5
MACHINE
2 27 Dec 2011 18:30:58 6
MACHINE