Strage behavior on renaming an IOT table - oracle 11g - 11.2.0.2.0 "truncating (as requested) before column"

I have created a heap table,
then an IOT with CTAS from above table
and then altered the IOT to rename it.

as soon as i ran a select on dba_tables, i noticed the Truncating commands and was little scared.
Since the issue is not reproducible, didn't open any SR with Oracle and still wonder why that strange output was shown. If anybody noticed the similar behavior and got the reason, please let me know.


19:56:51 SQL> conn raj/***
Connected.
19:56:54 SQL> CREATE TABLE test123
19:56:55   2     ( asd    NUMBER(6)
19:56:55   3     , first_name     VARCHAR2(20)
19:56:55   4     , CONSTRAINT     test123_pk
19:56:55   5                      PRIMARY KEY (asd)
19:56:55   6     );

Table created.

Elapsed: 00:00:00.60

19:57:09 SQL> create table test321 (asd, first_name,
19:57:09   2  constraint new_pk
19:57:09   3  primary key (asd))
19:57:09   4  organization index
19:57:09   5  as select * from test123;

Table created.

Elapsed: 00:00:00.03
19:57:49 SQL>
19:57:54 SQL> alter table test321 rename to testraj;

Table altered.

Elapsed: 00:00:00.96
19:59:31 SQL> select * from dba_tables where table_name ='TESTRAJ';
truncating (as requested) before column MAX_TRANS

truncating (as requested) before column INITIAL_EXTENT

truncating (as requested) before column NEXT_EXTENT

truncating (as requested) before column MIN_EXTENTS

truncating (as requested) before column MAX_EXTENTS

truncating (as requested) before column PCT_INCREASE

truncating (as requested) before column FREELISTS

truncating (as requested) before column FREELIST_GROUPS

truncating (as requested) before column NUM_ROWS

truncating (as requested) before column BLOCKS

truncating (as requested) before column EMPTY_BLOCKS

truncating (as requested) before column AVG_SPACE

truncating (as requested) before column CHAIN_CNT

truncating (as requested) before column AVG_ROW_LEN

truncating (as requested) before column AVG_SPACE_FREELIST_BLOCKS

truncating (as requested) before column NUM_FREELIST_BLOCKS

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated


OWNER      TABLE_NAME                     TABLESPACE_NAME CLUSTER_NAME                   IOT_NAME                       STATUS               PCT_FREE             PCT_USED            INI_TRANS LOG B D
---------- ------------------------------ --------------- ------------------------------ ------------------------------ -------- -------------------- -------------------- -------------------- --- - -
RAJ        TESTRAJ                                                                                                      VALID                       0                    0                    0     N

Elapsed: 00:00:00.36
20:00:25 SQL>

ORA-00600 when using EXPDP & IMPDP on a RAC Database.

When exporting a schema (with no data) using expdp, no issues were identified, but when using impdp with sqlfile, identified below ora 600 issue. Oracle called it out a bug and no patch yet. Hence, be little careful while using impdp on your prod systems. Btw, there was no impact to database because of the ora 600, but obviously the impdp failed. You can notice that when you start expdp or impdp, database does "alter system set services" introducing a sys related service "SYS$SYS.KUPC$Cxxxx.DBNAME"  as you can see in the below log file.

work around is to exec dbms_stats.gather_fixed_objects_stats();
but it doesn't help as the issue is reproducible.

used commands were:
expdp / directory = DATA_PUMP_DIR dumpfile="$i"_raj.dmp logfile="$i"_raj.log schemas="$i" content=metadata_only

impdp / irectory=DATA_PUMP_DIR dumpfile="$i"_raj.dmp sqlfile="$i"_raj.sql logfile="$i"_raj.log"

Error from Alert log file is:


streams_pool_size defaulting to 2867MB. Trying to get it from Buffer Cache for process 45416666.
2013-08-26 10:53:23.851000 -07:00
ALTER SYSTEM SET service_names='SRV_JOB','SRV_ORCL','ORCL','SYS$SYS.KUPC$C_4_20130826105315.ORCL' SCOPE=MEMORY SID='ORCL_4';
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_4_20130826105315.ORCL','SRV_JOB','SRV_ORCL','ORCL','SYS$SYS.KUPC$S_4_20130826105315.ORCL' SCOPE=MEMORY SID='ORCL_4';
2013-08-26 10:53:25.284000 -07:00
DM00 started with pid=969, OS id=16842836, job OPS$ORACLE.SYS_EXPORT_SCHEMA_01
2013-08-26 10:53:30.374000 -07:00
DW00 started with pid=890, OS id=13370008, wid=1, job OPS$ORACLE.SYS_EXPORT_SCHEMA_01


Dumping diagnostic data in directory=[cdmp_20130826110944], requested by (instance=4, osid=61210982 (DM00)), summary=[incident=1207772].
2013-08-26 11:09:46.331000 -07:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.


Errors in file /u01/home/oracle/diag/rdbms/orcl_live1/ORCL_4/trace/ORCL_4_dm00_61210982.trc  (incident=1207772):
ORA-00600: internal error code, arguments: [15851], [4], [4], [1], [3], [], [], [], [], [], [], []
Incident details in: /u01/home/oracle/diag/rdbms/orcl_live1/ORCL_4/incident/incdir_1207772/ORCL_4_dm00_61210982_i1207772.trc
2013-08-26 11:02:07.749000 -07:00
Non critical error ORA-48913 caught while writing to trace file "/u01/home/oracle/diag/rdbms/orcl_live1/ORCL_4/incident/incdir_1207772/ORCL_4_dm00_61210982_i1207772.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached
Writing to the above trace file is disabled for now on...

Dumping diagnostic data in directory=[cdmp_20130826110944], requested by (instance=4, osid=61210982 (DM00)), summary=[incident=1207772].
2013-08-26 11:09:46.331000 -07:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sweep [inc][1207772]: completed
Sweep [inc2][1207772]: completed
2013-08-26 11:09:48.495000 -07:00
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_4_20130826105656.ORCL','SRV_JOB','SRV_ORCL','ORCL' SCOPE=MEMORY SID='ORCL_4';
ALTER SYSTEM SET service_names='SRV_JOB','SRV_ORCL','ORCL' SCOPE=MEMORY SID='ORCL_4';
Errors in file /u01/home/oracle/diag/rdbms/orcl_live1/ORCL_4/trace/ORCL_4_dm00_61210982.trc  (incident=1207773):
ORA-00600: internal error code, arguments: [15851], [4], [4], [1], [3], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPM$MCP", line 1040
ORA-06512: at line 2
Incident details in: /u01/home/oracle/diag/rdbms/orcl_live1/ORCL_4/incident/incdir_1207773/ORCL_4_dm00_61210982_i1207773.trc
2013-08-26 11:09:52.970000 -07:00
Non critical error ORA-48913 caught while writing to trace file "/u01/home/oracle/diag/rdbms/orcl_live1/ORCL_4/incident/incdir_1207773/ORCL_4_dm00_61210982_i1207773.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached
Writing to the above trace file is disabled for now on...
2013-08-26 11:09:54.553000 -07:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
opidrv aborting process DM00 ospid (61210982) as a result of ORA-447
2013-08-26 11:10:10.618000 -07:00

defgen

Definition of a table using defgen Utility (oracle golden gate) - simple script to automatically get your table definitions if you are using UserExits or if you are relying on the definition file rather than the replicat parameter file.. Do not forget to update your table definitions when the source table has been modified.

Btw, edit the home, golden gate locations accordingly to suit your environment..

script: def_gen.sh

Dir=/u01/orcl

gguser=/u01/orcl/goldengate/111

. /u01/orcl/.profile

cd $Dir

cd $Dir/gg

mv *.obey logs/

Obey_file=$Dir/gg/defgen_`date +"%m-%d-%y-%H-%M-%S"`.obey;

Definition_file=$Dir/gg/defgen_`date +"%m-%d-%y-%H-%M-%S"`.def

sqlplus -s / as sysdba << EOF 

set echo off head off trim on feed off

spool $Obey_file

prompt --DEFSFILE

prompt DEFSFILE $Definition_file,purge

prompt USERID ggs, PASSWORD ******

select 'TABLE '||OWNER||'.'||TABLE_NAME||';' FROM DBA_TABLES WHERE OWNER IN

(

'SCOTT',

'HR'

) ;

--AND TABLE_NAME= (based on your options)

spool off

EOF

cd $gguser

pwd

#echo "obey $Obey_file"|./defgen

./defgen PARAMFILE $Obey_file

ls -ltr $Dir/gg

== run the script and you should be able to get your definition file

output will be :

raj@localhost > cat defgen_05-18-13-02-24-59.def
*
* Definitions created/modified  2013-05-18 02:25
*
*  Field descriptions for each column entry:
*
*     1    Name
*     2    Data Type
*     3    External Length
*     4    Fetch Offset
*     5    Scale
*     6    Level
*     7    Null
*     8    Bump if Odd
*     9    Internal Length
*    10    Binary Length
*    11    Table Length
*    12    Most Significant DT
*    13    Least Significant DT
*    14    High Precision
*    15    Low Precision
*    16    Elementary Item
*    17    Occurs
*    18    Key Column
*    19    Sub Data Type
*
*
Definition for table SCOTT.VALUES_TAB
Record length: 430
Syskey: 0
Columns: 5
ID       64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
NAME     64    200       56  0  0 1 0    200    200      0 0 0 0 0 1    0 0 0
VALUE1   64     50      262  0  0 1 0     50     50     50 0 0 0 0 1    0 0 2
VALUE2   64     50      318  0  0 1 0     50     50     50 0 0 0 0 1    0 0 2
VALUE3   64     50      374  0  0 1 0     50     50     50 0 0 0 0 1    0 0 2
End of definition

Oracle Golden gate - Long running transactions while stopping an extract ?

Below is how you can find it out and kill if needed.


GGSCI (ggnode1) 7>send extract EXRAJ, showtrans duration 20 MIN

Sending showtrans request to EXTRACT EXRAJ ...

Oldest redo log files necessary to restart Extract are:

Redo Thread 1, Redo Log Sequence Number 74058, SCN 1586.3900894393 (6815719025849), RBA 104066064
Redo Thread 2, Redo Log Sequence Number 79546, SCN 1586.3894494112 (6815712625568), RBA 1458358288

------------------------------------------------------------
XID:                  1630.17.201166
Items:                1      
Extract:              EXRAJ  
Redo Thread:          1    
Start Time:           2013-05-10:21:43:50
SCN:                  1586.3900894393 (6815719025849)
Redo Seq:             74058
Redo RBA:             104066064        
Status:               Running          


------------------------------------------------------------
XID:                  9068.29.296116
Items:                1      
Extract:              EXRAJ  
Redo Thread:          2    
Start Time:           2013-05-10:21:13:50
SCN:                  1586.3894494112 (6815712625568)
Redo Seq:             79546
Redo RBA:             1458358288        
Status:               Running          


------------------------------------------------------------
XID:                  8805.6.296139
Items:                1      
Extract:              EXRAJ  
Redo Thread:          2    
Start Time:           2013-05-10:21:24:33
SCN:                  1586.3896755063 (6815714886519)
Redo Seq:             79547
Redo RBA:             791874576        
Status:               Running          



22:09:08 SQL> select * from gv$transaction where xidusn=8805;

   INST_ID ADDR                 XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS           START_TIME           START_SCNB START_SCNW START_UEXT START_UBAFIL
---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------
START_UBABLK START_UBASQN START_UBAREC SES_ADDR               FLAG SPA REC NOU PTX NAME                                                                   PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN
------------ ------------ ------------ ---------------- ---------- --- --- --- --- ---------------------------------------------------------------------- ---------- ---------- ---------- ----------
PTX_XIDSLT PTX_XIDSQN     DSCN-B     DSCN-W  USED_UBLK  USED_UREC     LOG_IO     PHY_IO     CR_GET  CR_CHANGE START_DATE       DSCN_BASE  DSCN_WRAP  START_SCN DEPENDENT_SCN XID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ---------- ---------- ---------- ------------- ----------------
PRV_XID          PTX_XID
---------------- ----------------
         2 0000001BFF3DEBD8       8805          6     296139        856     125452      27952          5 ACTIVE           05/10/13 21:24:33    3896755063       1586          4          856
      125452        27952            5 0000001C84587F40       7683 NO  NO  NO  NO                                                                                  0          0          0          0
         0          0          0          0          1          1          5          0          0          0 10-MAY-13                0          0 6.8157E+12             0 22650006000484CB
0000000000000000 0000000000000000



22:09:38 SQL> select sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action from gv$session where taddr='0000001BFF3DEBD8';

  SID    SERIAL# EVENT                          MACHINE         SQL_ID        SECONDS_IN_WAIT PREV_SQL_ID   MODULE         PROGRAM
----- ---------- ------------------------------ --------------- ------------- --------------- ------------- -------------- ------------------------------------------------
ACTION
----------------------------------------------------------------
 9871        167 SQL*Net message from client    client07                               2710 7zwu0n8myp5vn occ-raj-jar occworker@client07 (TNS V1-V3)
                                                                                                         
20f669b057722


22:09:43 SQL> select
hash_value, address,
executions,buffer_gets, disk_reads,
round(buffer_gets/decode(executions, 0, 1, executions), 1) avg_gets,
round(disk_reads/decode(executions, 0, 1, executions), 1) avg_disk,
last_load_time,
module,
sql_fulltext
from v$sqlarea
where sql_id='&sql_id';

Enter value for sql_id: 7zwu0n8myp5vn

HASH_VALUE ADDRESS          EXECUTIONS BUFFER_GETS DISK_READS   AVG_GETS   AVG_DISK LAST_LOAD_TIME      MODULE
---------- ---------------- ---------- ----------- ---------- ---------- ---------- ------------------- -------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
 669685620 0000001A4E4E8840   16774570    43243144       5728        2.6          0 10-MAY-13           occ-raj-jarers
INSERT INTO user_info (uid, part_key, t_id, uname) VALUES (:uid, :part_key, :t_id) /*Truncated the sql*/


you will see the following error message in ggserr.log file

2013-05-10 22:15:15  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle):  send EXRAJ showtrans duration 45m.
2013-05-10 22:15:16  INFO    OGG-01021  Oracle GoldenGate Capture for Oracle, EXRAJ.prm:  Command received from GGSCI: showtrans duration 45m.
Long Running Transaction: XID 8805.6.296139, Items 1, Extract EXRAJ, Redo Thread 2, SCN 1586.3896755063 (6815714886519), Redo Seq #79547, Redo RBA 791874576.
2013-05-10 22:15:20  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, EXRAJ.prm:  Long Running Transaction: XID 8805.6.296139, Items 1, Extract EXRAJ, Redo Thread 2, SCN 1586.3896755063 (6815714886519), Redo Seq #79547, Redo RBA 791874576.
2013-05-10 22:15:26  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): send EXRAJ showtrans duration 90m.
2013-05-10 22:15:28  INFO    OGG-01021  Oracle GoldenGate Capture for Oracle, EXRAJ.prm:  Command received from GGSCI: showtrans duration 90m.


select logon_time,status,LAST_CALL_ET from gv$session where sid=9871 and inst_id=2;

LOGON_TIME          STATUS   LAST_CALL_ET
------------------- -------- ------------
05/10/2013 19:26:36 INACTIVE         3226

or you can use:


set echo on
set timing on
col sid format 999999
col serial# format 999999
alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';
select  t.start_time,t.status TSTATUS, s.status SSTATUS,s.sid, s.serial# ,s.machine , s.sql_id,s.prev_sql_id,s.process,t.XIDUSN||'.'||t.XIDSLOT||'.'||t.XIDSQN XID from gv$transaction t, gv$session s  where t.addr=s.taddr and t.inst_id=s.inst_id and t.start_date < (sysdate-1/142) order  by t.start_time;


-- if needed, you can go ahead and kill the sql (if it is not of a major impact)

alter system kill session '9871,167';

Enabling Index Monitoring to find if the indexes are being used:


::::::::::::::
1.sql
::::::::::::::
select table_name, index_name, MONITORING, USED from v$object_usage where index_name in
(
'PUSER_IDX_3',
'PUSER_IDX_4',
'PUSER_IDX_6',
'PUSER_IDX_3',
'PUSER_IDX_4',
'USER_IDX'
)
/
::::::::::::::
2.sql
::::::::::::::
-- note not altering the session as i am identifying the index with schema name

--spool enable_monitoring.log
alter index  RAJ.PUSER_IDX_3 monitoring usage;
alter index  RAJ.PUSER_IDX_4 monitoring usage;
alter index  RAJ.PUSER_IDX_6 monitoring usage;
alter index  RAJ.PUSER_IDX_3 monitoring usage;
alter index  RAJ.PUSER_IDX_4 monitoring usage;
alter index  ORACLE.USER_IDX monitoring usage;
::::::::::::::
3.sql
::::::::::::::
select table_name, index_name, MONITORING, USED from v$object_usage where index_name in
(
'PUSER_IDX_3',
'PUSER_IDX_4',
'PUSER_IDX_6',
'PUSER_IDX_3',
'PUSER_IDX_4',
'USER_IDX'
)
/


-- Sql to check the index monitoring

  select du.username, io.name index_name, t.name table_name,
         decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitor,
         decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
         ou.start_monitoring,
         ou.end_monitoring
  from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, dba_users du
  where io.owner# = du.USER_ID
    and i.obj# = ou.obj#
    and io.obj# = ou.obj#
   and t.obj# = i.bo#
   and io.name in (
 'PUSER_IDX_3',
 'PUSER_IDX_4',
 'PUSER_IDX_6',
 'PUSER_IDX_3',
 'PUSER_IDX_4',
 'USER_IDX'
 )
/


USERNAME   INDEX_NAME                     TABLE_NAME                     MONI USED START_MONITORING    END_MONITORING
---------- ------------------------------ ------------------------------ ---- ---- ------------------- -------------------
RAJ PUSER_IDX_3       USER_INFO             YES  NO   02/21/2013 16:09:28
RAJ PUSER_IDX_4       USER_INFO             YES  NO   02/21/2013 16:09:28
RAJ PUSER_IDX_3     P_USER_INFO          YES  NO   02/21/2013 16:47:55
RAJ PUSER_IDX_4   P_USER_INFO          YES  NO   02/21/2013 16:10:52
RAJ PUSER_IDX_6   P_USER_INFO          YES  NO   02/21/2013 16:09:28
ORACLE     USER_IDX     USER_INFO             YES  NO   02/21/2013 16:09:28

6 rows selected.

Logdump: finding RBA using SCN, Timestamp, finding the records

Topics:
Finding RBA Using Timestamp
Finding RBA Using SCN
Finding records in a table using Filter


get either SCN or Timestamp from database and save it

22:18:53 SQL> select scn_to_timestamp(current_scn), current_scn from v$database;

SCN_TO_TIMESTAMP(CURRENT_SCN)                                                        CURRENT_SCN
--------------------------------------------------------------------------- --------------------
03-MAY-13 10.18.50.000000000 PM                                                    6808061561351

1. Using sfts or scanfortimestamp

now: start logdump

Oracle GoldenGate Log File Dump Utility
Version 11.1.1.1.2_01 OGGCORE_11.1.1.1.3_PLATFORMS_111209.1035

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.

usertoken on
ggstoken on
ghdr on
detail on

Logdump 5 >open /goldengate/traildata/ORCL/zb001058
Current LogTrail is /goldengate/traildata/ORCL/zb001058
Logdump 6 >sfts 2013/05/03 10:18:50
Scan for timestamp >= 2013/05/03 17:18:50.000.000 GMT

2013/05/03 11:35:25.208.987 FileHeader           Len  1284 RBA 0
Name: *FileHeader*
 3000 01b5 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...
 0002 3200 0004 2000 0000 3300 0008 02f2 0208 1205 | ..2... ...3.........
 f59b 3400 001f 001d 7572 693a 736c 6373 6264 6231 | ..4.....uri:hostnode1
 343a 3a67 6f6c 6465 6e67 6174 653a 3131 3136 0000 | 4::goldengate:1116..
 2d00 2b2f 676f 6c64 656e 6761 7465 2f74 7261 696c | -.+/goldengate/trail
 6461 7461 2f44 522f 434f 4e46 3243 544f 432f 7a62 | data/ORCL/zb
 3030 3130 3538 3700 0001 0138 0000 0400 0004 2239 | 0010587....8......"9

Logdump 7 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    33  (x0021)   IO Time    : 2013/05/03 11:31:15.000.000
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x01)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :       6682       AuditPos   : 1256873432
Continued  :     N  (x00)     RecCount   :     1  (x01)

2013/05/03 11:31:15.000.000 FieldComp            Len    33 RBA 1292
Name: RAJ.USER_INFO
After  Image:                                             Partition 4   GU m
 0000 000b 0000 0007 3238 3133 3537 3100 0300 0e00 | ........2813571.....
 0000 0a31 3336 3736 3035 3834 38                  | ...1367605848
Column     0 (x0000), Len    11 (x000b)
Column     3 (x0003), Len    14 (x000e)

User tokens:   18 bytes
 5343 4e00 3638 3037 3833 3738 3737 3832 3000      | SCN.6807837877820.

GGS tokens:
 5200 0014 4141 434e 4464 4149 3641 4142 6331 6b41 | R...AACNDdAI6AABc1kA
 4244 0001                                         | BD..


2) Using SCN

Logdump 14> filter usertoken scn >=6808061561351
Scan for timestamp >= 2013/05/04 05:18:50.000.000 GMT

2013/05/03 22:20:20.000.000 FieldComp            Len    33 RBA 278541
Name: RAJ.USER_INFO
Before Image:                                             Partition 4   GU b
 0000 000b 0000 0007 3339 3634 3538 3300 0300 0e00 | ........3964583.....
 0000 0a31 3336 3736 3434 3133 33                  | ...1367644133

OR

3) filter using csn

Logdump 18 >filter usertoken csn >=6808061561351

logdump
ghdr on
usertoken on
detail on
ggstoken on

Logdump 19 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)
RecLength  :    33  (x0021)   IO Time    : 2013/05/03 22:20:20.000.000
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x00)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :       6726       AuditPos   : 39170576
Continued  :     N  (x00)     RecCount   :     1  (x01)

2013/05/03 22:20:20.000.000 FieldComp            Len    33 RBA 278541
Name: RAJ.USER_INFO
Before Image:                                             Partition 4   GU b
 0000 000b 0000 0007 3339 3634 3538 3300 0300 0e00 | ........3964583.....
 0000 0a31 3336 3736 3434 3133 33                  | ...1367644133
Column     0 (x0000), Len    11 (x000b)
Column     3 (x0003), Len    14 (x000e)

User tokens:   18 bytes
 5343 4e00 3638 3038 3036 3136 3731 3436 3700      | SCN.6808061671467.

GGS tokens:
 5200 0014 4141 434e 4464 4147 6541 4144 7a39 4141 | R...AACNDdAGeAADz9AA
 4273 0001 4c00 000d 3638 3038 3036 3136 3731 3436 | Bs..L...680806167146
 3736 0000 0c34 3434 342e 3130 2e39 3937 38        | 76...4444.10.9978
 

Filtering suppressed   1455 records



4. Finding Records in table using FILTER

Logdump 172 >open /tmp/yp032733
Current LogTrail is /tmp/yp032733
Logdump 173 >filter include filename RAJ.USER_LIST
Logdump 174 >N
Scanned     10000 records, RBA   19431038, 2013/02/11 17:01:24.010.061
Scanned     20000 records, RBA   39075232, 2013/02/11 17:01:49.005.345
<o/p truncated>
Scanned    240000 records, RBA  463168443, 2013/02/11 17:10:41.005.732
Scanned    250000 records, RBA  483308009, 2013/02/11 17:11:06.000.784
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :   136  (x0088)   IO Time    : 2013/02/11 17:11:15.005.240
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x00)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :      19691       AuditPos   : 2825134096
Continued  :     N  (x00)     RecCount   :     1  (x01)

2013/02/11 17:11:15.005.240 Insert               Len   136 RBA 490378100
Name: RAJ.USER_LIST
After  Image:                                             Partition 4   GU b
 0000 0018 0000 0014 3133 3732 3331 3838 3236 3632 | ........137231882662
 3738 3534 3735 3620 0001 000a 0000 0000 0000 0000 | 7854756 ............
 0000 0002 0004 ffff 0000 0003 000a 0000 0000 0000 | ....................
 0000 0001 0004 0004 ffff 0000 0005 000a 0000 0000 | ....................
 0000 004c 311e 0006 000a 0000 0000 0000 0000 0001 | ...L1...............
 0007 000e 0000 000a 3133 3630 3633 3134 3730 0008 | ........1360631470..
 000e 0000 000a 3133 3630 3633 3134 3730           | ......1360631470
Column     0 (x0000), Len    24 (x0018)
Column     1 (x0001), Len    10 (x000a)
Column     2 (x0002), Len     4 (x0004)
Column     3 (x0003), Len    10 (x000a)
Column     4 (x0004), Len     4 (x0004)
Column     5 (x0005), Len    10 (x000a)
Column     6 (x0006), Len    10 (x000a)
Column     7 (x0007), Len    14 (x000e)
Column     8 (x0008), Len    14 (x000e)

User tokens:   22 bytes
 746b 6e2d 7363 6e00 3637 3338 3530 3039 3832 3734 | tkn-scn.673850098274
 3900                                              | 9.
 
Filtering suppressed 253736 records

Reverse Replication Using Oracle Golden Gate
This is to set up upstream and downstream replication with in the same data base and test it out.



1. Replicat parameter file:

--replicat added by raj
--CHECKPARAMS

REPLICAT rkiran

SETENV (ORACLE_SID = "ORCL")
SETENV (ORACLE_HOME = "/u01/home/product/10.2.0.4")
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID ggs, PASSWORD, ENCRYPTKEY DEFAULT

ASSUMETARGETDEFS
GROUPTRANSOPS 3000
ALLOWNOOPUPDATES
--ALLOWNOOPUPDATES # Dont comment the above parameter.

-- DISCARDFILE LOCATION
DISCARDFILE /u01/goldengate//dirout/rkiran.dsc, MEGABYTES 100,APPEND

STATOPTIONS REPORTFETCH
REPORTCOUNT EVERY 10 MINUTES, RATE

-- use EOFDELAY to reduce the impact to the oracle redo log sync
-- EOFDELAY 2

--Uncomment the followings if there are truncate operations.
--GETTRUNCATES

DYNAMICRESOLUTION

--Uncomment the followings if handlecollisions required.
--HANDLECOLLISIONS
--RESTARTCOLLISIONS

--Table List

--Uncomment out the followings and enter the scn you would like.

MAP RAJ.raj_raj, TARGET test.raj_test;

==========

ggsci
add replicat rkiran, exttrail /u01/goldengate/traildata/local/tc
alter replicat rkiran, extseqno 0 extrba 0
info rkiran
start rkiran
info rkiran

========

-- test extract created by raj
NUMFILES 2000
ALLOCFILES 500

EXTRACT EKIRAN

TRANLOGOPTIONS BUFSIZE 32768

EXTTRAIL /u01/goldengate/traildata/local/tc

SETENV (ORACLE_SID = "ORCL")
SETENV (ORACLE_HOME = "/u01/home/product/10.2.0.4")
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID ggs, PASSWORD <encrypted password>, ENCRYPTKEY DEFAULT
--sqlexec "alter session set events '10046 trace name context forever, level 12'"

FLUSHSECS 60
CHECKPOINTSECS 120

CACHEMGR, CACHESIZE 32GB, CACHEDIRECTORY /tmp/ggtmp/

EOFDELAY 15

REPORTCOUNT EVERY 100 RECORDS

GETTRUNCATES

DYNAMICRESOLUTION
WILDCARDRESOLVE DYNAMIC
WARNLONGTRANS 5 MINUTES, CHECKINTERVAL 1M

TABLE RAJ.RAJ_RAJ, TOKENS ( SCN = @GETENV ("ORATRANSACTION","SCN"));


===

add extract EKIRAN tranlog begin now
add rmttrail /u01/goldengate/traildata/local/tc extract EKIRAN

make sure if the trail files are generated at the local dirdat directory

===============

-- test extract created by raj for reverse replication

NUMFILES 2000
ALLOCFILES 500

EXTRACT EREV

TRANLOGOPTIONS BUFSIZE 32768

EXTTRAIL /u01/goldengate/traildata/local/rev/bc

SETENV (ORACLE_SID = "ORCL")
SETENV (ORACLE_HOME = "/u01/home/product/10.2.0.4")
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID ggs, PASSWORD <encrypted password>, ENCRYPTKEY DEFAULT
--sqlexec "alter session set events '10046 trace name context forever, level 12'"

FLUSHSECS 60
CHECKPOINTSECS 120

CACHEMGR, CACHESIZE 32GB, CACHEDIRECTORY /tmp/ggtmp/

EOFDELAY 15

REPORTCOUNT EVERY 100 RECORDS

GETTRUNCATES

DYNAMICRESOLUTION
WILDCARDRESOLVE DYNAMIC
WARNLONGTRANS 5 MINUTES, CHECKINTERVAL 1M

TABLE TEST.RAJ_TEST, TOKENS ( SCN = @GETENV ("ORATRANSACTION","SCN"));


===

add extract EREV tranlog begin now
add rmttrail /u01/goldengate/traildata/local/rev/bc extract EREV

make sure if the trail files are generated at the local dirdat directory

=================
--replicat added by raj
--CHECKPARAMS

REPLICAT reverse

SETENV (ORACLE_SID = "ORCL")
SETENV (ORACLE_HOME = "/u01/home/product/10.2.0.4")
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID ggs, PASSWORD <encrypted password>, ENCRYPTKEY DEFAULT

ASSUMETARGETDEFS
GROUPTRANSOPS 3000
ALLOWNOOPUPDATES
--ALLOWNOOPUPDATES # Dont comment the above parameter.

-- DISCARDFILE LOCATION
DISCARDFILE /u01/goldengate//dirout/reverse.dsc, MEGABYTES 100,APPEND

STATOPTIONS REPORTFETCH
REPORTCOUNT EVERY 10 MINUTES, RATE

-- use EOFDELAY to reduce the impact to the oracle redo log sync
-- EOFDELAY 2

--Uncomment the followings if there are truncate operations.
--GETTRUNCATES

DYNAMICRESOLUTION

--Uncomment the followings if handlecollisions required.
--HANDLECOLLISIONS
--RESTARTCOLLISIONS

--Table List

--Uncomment out the followings and enter the scn you would like.
MAP test.raj_test, TARGET RAJ.raj_raj;

==


GGSCI (localhost) 1> dblogin userid ggs, password ****
Successfully logged into database.

GGSCI (localhost) 2> add trandata RAJ.raj_raj

Logging of supplemental redo data enabled for table RAJ.RAJ_RAJ.

GGSCI (localhost) 3> add trandata test.raj_test

Logging of supplemental redo data enabled for table TEST.RAJ_TEST.

oracle@localhost > ls /u01/goldengate/traildata/local/
oracle@localhost >
no files existing

GGSCI (localhost) 3> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                      
EXTRACT     STOPPED     EKIRAN      00:00:00      00:00:03

GGSCI (localhost) 4> add rmttrail /u01/goldengate/traildata/local/tc extract EKIRAN
RMTTRAIL added.


GGSCI (localhost) 5> info EKIRAN
info EKIRAN

EXTRACT    EKIRAN    Last Started 2012-11-14 16:46   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:01:45 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2012-11-14 16:45:49  Seqno 31940, RBA 757264

oracle@localhost > ls -ltr /u01/goldengate/traildata/local/
total 16
-rw-rw-rw-   1 oracle   dba          948 Nov 14 16:46 tc000000

====================================
====================================

GGSCI (localhost) 2> add replicat rkiran, exttrail /u01/goldengate/traildata/local/tc
REPLICAT added.


GGSCI (localhost) 3> alter replicat rkiran, extseqno 0 extrba 0
REPLICAT altered.


GGSCI (localhost) 4> info rkiran

REPLICAT   RKIRAN    Initialized   2012-11-14 17:01   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint  File /u01/goldengate/traildata/local/tc000000
                     First Record  RBA 0


GGSCI (localhost) 5> start rkiran

Sending START request to MANAGER ...
REPLICAT RKIRAN starting


GGSCI (localhost) 6> info rkiran

REPLICAT   RKIRAN    Last Started 2012-11-14 17:01   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File /u01/goldengate/traildata/local/tc000000
                     First Record  RBA 948


GGSCI (localhost) 7> !
info rkiran

REPLICAT   RKIRAN    Last Started 2012-11-14 17:01   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint  File /u01/goldengate/traildata/local/tc000000
                     First Record  RBA 948
====================================
====================================


set echo on
set serveroutput on
begin
 for i in 1..300
  loop
    insert into raj_raj values (i,'jra','hyd',i,i);
    dbms_output.put_line('value of i is '||i);
    if (mod(i,200)=0) then
      dbms_output.put_line('committing at the value of i='||i);
      commit;
        dbms_output.put_line('sleeping for 2 seconds');
        dbms_lock.sleep(2);
        DBMS_OUTPUT.put_line ('Time is ' || TO_CHAR (SYSDATE, 'HH:MI:SS'));
    end if;
  end loop;
end;


select count(*) from raj_raj;
select count(*) from raj_test;


INFERENCE: extract get lag = At EOF, no more records to process
       +
     extract logend  = YES
          +
     Replicat status = 0 records in current transaction
          +
     Replicat lag and logend should be EOF, no more records to process and YES respectively.  
===


== Testing reverse replication


         CURRENT_SCN
--------------------
       6669796442862
       6669796442886 - from the logdump
       rba 4580020
       seq# 000021
   
set serveroutput on
begin
 for i in 1000001..1050000
  loop
    insert into raj_test values (i,'jra','hyd',i,i);
    dbms_output.put_line('value of i is '||i);
    if (mod(i,1000)=0) then
      dbms_output.put_line('committing at the value of i='||i);
      commit;
       dbms_output.put_line('sleeping for 2 seconds');
       dbms_lock.sleep(2);
       DBMS_OUTPUT.put_line ('Time is ' || TO_CHAR (SYSDATE, 'HH:MI:SS'));
    end if;
  end loop;
end;
/



add replicat reverse, exttrail /u01/goldengate/traildata/local/rev/bc

GGSCI (localhost) 2> alter replicat reverse, extseqno 000021 extrba 4580020
REPLICAT altered.


GGSCI (localhost) 3> info reverse

REPLICAT   REVERSE   Initialized   2012-11-16 10:50   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File /u01/goldengate/traildata/local/rev/bc000021
                     First Record  RBA 4580020


GGSCI (localhost) 4> start reverse

Sending START request to MANAGER ...
REPLICAT REVERSE starting


GGSCI (localhost) 5> info reverse

REPLICAT   REVERSE   Last Started 2012-11-16 10:50   Status RUNNING
Checkpoint Lag       00:10:26 (updated 00:00:00 ago)
Log Read Checkpoint  File /u01/goldengate/traildata/local/rev/bc000021
                     2012-11-16 10:39:57.000000  RBA 5300143


GGSCI (localhost) 6>  info reverse detail

REPLICAT   REVERSE   Last Started 2012-11-16 10:50   Status RUNNING
Checkpoint Lag       00:10:03 (updated 00:00:00 ago)
Log Read Checkpoint  File /u01/goldengate/traildata/local/rev/bc000021
                     2012-11-16 10:40:27.000000  RBA 7460517

  Extract Source                          Begin             End        

  /u01/goldengate/traildata/local/rev/bc000021  * Initialized *   2012-11-16 10:40
  /u01/goldengate/traildata/local/rev/bc000021  * Initialized *   First Record
  /u01/goldengate/traildata/local/rev/bc000000  * Initialized *   First Record


-- some use ful sql

/*
Conn / as sysdba
Col used format a8
Prompt current_rba will show where redo is being written to

Select  le.leseq  log_sequence#, cp.cpodr_bno * le.lebsz current_rba,
                        le.lesiz * le.lebsz log_size,
            substr(to_char(100 * cp.cpodr_bno / le.lesiz, '999.00'), 2) || '%'  used
    from    sys.x$kcccp  cp,
            sys.x$kccle  le
    where   le.inst_id = userenv('Instance') and
            cp.inst_id = userenv('Instance') and
            le.leseq = cp.cpodr_seq and le.leseq > 0
*/

Mongo DB installation on Mac

requirement is: Command Line Tools for Xcode": http://connect.apple.com
go to apple.com and download Xcode.

Last login: Mon Feb 11 23:00:42 on ttys000

machost_01:~ raj$ brew update

-sh: brew: command not found

machost_01:~ raj$ brew

-sh: brew: command not found

machost_01:~ raj$ cd /usr/local/

machost_01:local raj$ find Cellar

find: Cellar: No such file or directory

machost_01:local raj$ gcc -v

-sh: gcc: command not found

machost_01:local raj$ ruby -v

ruby 1.8.7 (2012-02-08 patchlevel 358) [universal-darwin11.0]

machost_01:local raj$ ruby -e "$(curl -fsSL https://raw.github.com/mxcl/homebrew/go)"

==> This script will install:

/usr/local/bin/brew

/usr/local/Library/...

/usr/local/share/man/man1/brew.1

==> The following directories will be made group writable:

/usr/local/.

==> The following directories will have their group set to admin:

/usr/local/.

Press ENTER to continue or any other key to abort

==> /usr/bin/sudo /bin/chmod g+rwx /usr/local/.

WARNING: Improper use of the sudo command could lead to data loss

or the deletion of important system files. Please double-check your

typing when using sudo. Type "man sudo" for more information.

To proceed, enter your password, or type Ctrl-C to abort.

Password:

Sorry, try again.

Password:

==> /usr/bin/sudo /usr/bin/chgrp admin /usr/local/.

==> Downloading and Installing Homebrew...

Failed during: Error: /usr/bin/xcode-select returned unexpected error. init -q

machost_01:local raj$ gcc -v

-sh: gcc: command not found

machost_01:local raj$ ruby -e "$(curl -fsSL https://raw.github.com/mxcl/homebrew/go)"

==> This script will install:

/usr/local/bin/brew

/usr/local/Library/...

/usr/local/share/man/man1/brew.1

Press ENTER to continue or any other key to abort

==> Downloading and Installing Homebrew...

remote: Counting objects: 101297, done.

remote: Compressing objects: 100% (43460/43460), done.

remote: Total 101297 (delta 71564), reused 83861 (delta 56957)

Receiving objects: 100% (101297/101297), 15.12 MiB | 2.19 MiB/s, done.

Resolving deltas: 100% (71564/71564), done.

From https://github.com/mxcl/homebrew

 * [new branch]      master     -> origin/master

Checking out files: 100% (2612/2612), done.

HEAD is now at 52b8be1 doctor: make brew link more clear

Warning: Install the "Command Line Tools for Xcode": http://connect.apple.com

==> Installation successful!

You should run `brew doctor' *before* you install anything.

Now type: brew help

machost_01:local raj$ brew doctor

Warning: Experimental support for using Xcode without the "Command Line Tools".

You have only installed Xcode. If stuff is not building, try installing the

"Command Line Tools for Xcode" package provided by Apple.

machost_01:local raj$ 

machost_01:local raj$ brew doctor

Warning: Experimental support for using Xcode without the "Command Line Tools".

You have only installed Xcode. If stuff is not building, try installing the

"Command Line Tools for Xcode" package provided by Apple.

machost_01:local raj$ 

machost_01:local raj$ 

machost_01:local raj$ 

machost_01:local raj$ ruby -e "$(curl -fsSL https://raw.github.com/mxcl/homebrew/go)"

It appears Homebrew is already installed. If your intent is to reinstall you

should do the following before running this installer again:

    rm -rf /usr/local/Cellar /usr/local/.git && brew cleanup

machost_01:local raj$ 

machost_01:local raj$ brew update

Already up-to-date.

machost_01:local raj$ 

machost_01:local raj$ 

machost_01:local raj$ pwd

/usr/local

machost_01:local raj$ brew install mongo

==> Downloading http://fastdl.mongodb.org/osx/mongodb-osx-x86_64-2.2.3.tgz

######################################################################## 100.0%

==> Caveats

To have launchd start mongodb at login:

    mkdir -p ~/Library/LaunchAgents

    ln -sfv /usr/local/opt/mongodb/*.plist ~/Library/LaunchAgents

Then to load mongodb now:

    launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mongodb.plist

Or, if you don't want/need launchctl, you can just run:

    mongod

==> Summary

🍺  /usr/local/Cellar/mongodb/2.2.3-x86_64: 20 files, 171M, built in 45 seconds