Thinking Out Loud

March 23, 2017

PURGEOLDEXTRACTS Not Purging Trail Files

Filed under: GoldenGate — mdinh @ 1:16 am

The best part of playing poker is misreading the hand and won.

Well, today this applied to help troubleshoot a Goldengate issue.
Granted it might not be the root cause but it did solve the issue for the time being.

Given:

1. Goldengate version 10.4

2. mgr process started with uid vs username

$ id -a
uid=19208(ggsuser) gid=1601(dba) groups=1601(dba)

$ ps -ef|grep ./mgr
19208    18576     1  0 16:05 ?        00:00:00 ./mgr 

3. dirdat is symbolic link to /ggs/dirdat

4. mgr.prm contains PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2

5. ggserr.log - trail files not being purged
2017-03-22 16:09:35  GGS INFO        399  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): start mgr.
2017-03-22 16:09:35  GGS WARNING     201  Oracle GoldenGate Manager for Oracle, mgr.prm:  PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2 (MINKEEPFILES option not used.).
2017-03-22 16:09:35  GGS INFO        330  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7949).

6. send mgr, getpurgeoldextracts
GGSCI> send mgr, getpurgeoldextracts

Sending GETPURGEOLDEXTRACTS request to MANAGER ...
PurgeOldExtracts Rules
Fileset                              MinHours MaxHours MinFiles MaxFiles UseCP
/ggs/dirdat/*                              24        0        0        0   Y
OK	
No extract trails

GGSCI> 

Comparison:

1. Goldengate version 12.2

2. mgr process started with username vs uid
ggsuser  73929     1  0 Mar21 ?        00:00:46 ./mgr 

3. dirdat is symbolic link to /ggs/dirdat

4. send mgr, getpurgeoldextracts
GSCI> send mgr, getpurgeoldextracts

Sending GETPURGEOLDEXTRACTS request to MANAGER ...

PurgeOldExtracts Rules
Fileset                              MinHours MaxHours MinFiles MaxFiles UseCP
/ggs/dirdat/*                              48        0        1        0   Y
OK	
Extract Trails
Filename                        Oldest_Chkpt_Seqno  IsTable  IsVamTwoPhaseCommit
/ggs/dirdat/aa                              45

GGSCI>

Research:

1. Did not apply
Goldengate Manager Not Purging Trail Files (Doc ID 1460097.1)

2. Did not apply
Trail Files Not Automatically Purged by 11.1 When PurgeOldExtracts for MGR Has Been Configured (Doc ID 1943702.1)

3. Helped resolve the problem by misreading.
PURGEOLDEXTRACTS Not Purging Trail Files (Doc ID 967934.1)

The use of a UNIX symbolic link to a process can prevent MANAGER from being able to find the appropriate trail files that need to be purged.

There was not symbolic link to a process, that's just insane and who would do such a thing!

Initially read this incorrectly as symbolic link and check dirdat (symbolic link) which lead to the solution.

Solution:

Replace PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1
With    PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2 

2017-03-22 19:49:10  GGS INFO        399  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): start mgr.
2017-03-22 19:49:10  GGS WARNING     201  Oracle GoldenGate Manager for Oracle, mgr.prm:  PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1 (MINKEEPFILES option not used.).
2017-03-22 19:49:10  GGS INFO        330  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7949).

2017-03-22 19:49:10  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000150, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 150.
2017-03-22 19:49:10  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000151, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 151.
.........
2017-03-22 19:49:11  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000183, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 183.
2017-03-22 19:49:11  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000184, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 184.

March 20, 2017

adrci purging

Filed under: oracle — mdinh @ 10:31 pm

I did not know this.

Is there a way to control Auto_Purge Frequency done by the MMON ? (Doc ID 1446242.1)

The automatic purge cycle is designed as follows.
(1) The first actual purge action will be 2 days after instance startup time
(2) The next automatic purge actions following this first purge is done once every 7 days

If you like to purge more often, then it will need to be done manually.

The blog below was every helpful for creating adrci scripts.
https://grepora.com/2016/08/03/adrci-retention-policy-and-ad-hoc-purge-script-for-all-bases/

Here is what I have created.

$ ./adrci_show_control.sh

SHOW CONTROL diag/crs/arrow1/crs:

ADR Home = /u01/app/oracle/diag/crs/arrow1/crs:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1344875867           720                  8760                 2016-11-24 19:05:55.164304 -08:00                                                 2017-02-28 19:56:23.753525 -08:00        1                    2                    82                   1                    2016-11-24 19:05:55.164304 -08:00
1 rows fetched

SHOW CONTROL diag/rdbms/hawka/HAWKA:

ADR Home = /u01/app/oracle/diag/rdbms/hawka/HAWKA:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1630649358           1                    1                    2017-03-04 10:01:39.568251 -08:00        2017-03-18 07:00:21.124556 -07:00        2017-02-28 19:55:26.148874 -08:00        1                    2                    80                   1                    2016-11-27 18:22:12.601136 -08:00
1 rows fetched

SHOW CONTROL diag/rdbms/test/test:

ADR Home = /u01/app/oracle/diag/rdbms/test/test:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
2768052777           720                  8760                 2017-03-04 18:10:18.197875 -08:00                                                                                          1                    2                    80                   1                    2017-03-04 18:10:18.197875 -08:00
1 rows fetched

$ ./adrci_set_control.sh

SET CONTROL diag/crs/arrow1/crs:
SET CONTROL diag/rdbms/hawka/HAWKA:
SET CONTROL diag/rdbms/test/test:

$ ./adrci_purge.sh

PURGE diag/crs/arrow1/crs:

ADR Home = /u01/app/oracle/diag/crs/arrow1/crs:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1344875867           2160                 2880                 2017-03-20 15:02:48.861513 -07:00                                                 2017-03-20 15:03:01.019503 -07:00        1                    2                    82                   1                    2016-11-24 19:05:55.164304 -08:00
1 rows fetched

PURGE diag/rdbms/hawka/HAWKA:

ADR Home = /u01/app/oracle/diag/rdbms/hawka/HAWKA:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1630649358           2160                 2880                 2017-03-20 15:02:48.879455 -07:00        2017-03-18 07:00:21.124556 -07:00        2017-03-20 15:03:01.348572 -07:00        1                    2                    80                   1                    2016-11-27 18:22:12.601136 -08:00
1 rows fetched

PURGE diag/rdbms/test/test:

ADR Home = /u01/app/oracle/diag/rdbms/test/test:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
2768052777           2160                 2880                 2017-03-20 15:02:48.894455 -07:00                                                 2017-03-20 15:03:01.442372 -07:00        1                    2                    80                   1                    2017-03-04 18:10:18.197875 -08:00
1 rows fetched

$ cat adrci_show_control.sh

for f in $( adrci exec="show homes" | grep -v "ADR Homes:" );
do
echo "SHOW CONTROL ${f}:";
adrci exec="set home $f; show control;" ;
done

$ cat adrci_set_control.sh

for f in $( adrci exec="show homes" | grep -v "ADR Homes:" );
do
echo "set control ${f}:";
adrci exec="set home $f; set control \(SHORTP_POLICY=2160, LONGP_POLICY=2880\);" ;
done

$ cat adrci_purge.sh

for f in $( adrci exec="show homes" | grep -v "ADR Homes:" );
do
echo "purge ${f}:";
adrci exec="set home $f; show control; purge" ;
done

ADRCI Retention Policy and Ad-Hoc Purge Script for all Bases

Filed under: oracle — mdinh @ 10:31 pm

|GREP ORA

As you know, since 11g we have a Automatic Diagnostic Repository (ADR). To better manage it, we also have a Command-line Interface, called ADRCI.
ADR contains all diagnostic information for database (logs, traces, incidents, problems, etc).

adr1

ADR Structure

View original post 349 more words

March 19, 2017

Quick Check for Corrupt Block (file #, block #)

Filed under: oracle — mdinh @ 6:21 pm

$ egrep -n '^Corrupt block relative dba| ^Reread of blocknum' alert.log| tail -50

124974:Corrupt block relative dba: 0x04c7d3be (file 12, block 312452)
124983:Reread of blocknum=312452, file=+DATA/db/datafile/db_data03.329.456465464. found valid data

124974|124983 are line number from alert.log

On reread found valid data, there is no need to proceed further since block has been validated. Typically, this occurs when there is a fractured block.

If you have doubts:
RMAN> backup validate check logical datafile 12;
SQL> select * from v$database_block_corruption;

Find corrupted segment.

select owner, segment_name, segment_type, partition_name
from dba_extents
where file_id = 12
and 312452 between block_id and block_id + blocks – 1
and rownum = 1
;

OWNER
------------------------------
SEGMENT_NAME
-----------------------------
SEGMENT_TYPE	   
------------------------------
PARTITION_NAME
------------------------------
MDINH
T1
TABLE PARTITION    
T1_P8

March 10, 2017

Deceptive Numbers

Filed under: Uncategorized — mdinh @ 9:09 pm

My portfolio increased $10,000 today. Original investment is $1,000,000.

I made 50% on my investment today. Original investment is $200.

There is more to than meets the eye.

March 8, 2017

Using Exception Table

Filed under: oracle — mdinh @ 12:44 am

It has been quite a while since I have used Exception table and just a quick demo to share.

oracle@arrow1:HAWKA:/media/sf_working/sql
$ sqlplus demo/demo @exception_demo.sql

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 7 16:36:33 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options

ARROW1:(DEMO@HAWKA):PRIMARY> col OWNER for a20
ARROW1:(DEMO@HAWKA):PRIMARY> col TABLE_NAME for a20
ARROW1:(DEMO@HAWKA):PRIMARY> col R_CONSTRAINT_NAME for a20
ARROW1:(DEMO@HAWKA):PRIMARY> drop table f purge;

Table dropped.

ARROW1:(DEMO@HAWKA):PRIMARY> drop table p purge;

Table dropped.

ARROW1:(DEMO@HAWKA):PRIMARY> drop table exceptions purge;

Table dropped.

ARROW1:(DEMO@HAWKA):PRIMARY> set echo off
Running @?/rdbms/admin/utlexcpt.sql

Table created.

ARROW1:(DEMO@HAWKA):PRIMARY> desc exceptions
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ROW_ID                           ROWID
 OWNER                            VARCHAR2(30)
 TABLE_NAME                       VARCHAR2(30)
 CONSTRAINT                       VARCHAR2(30)

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> create table p
  2  ( id number not null,
  3    CONSTRAINT p_pk PRIMARY KEY (id)
  4  );

Table created.

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> create table f
  2  ( id number not null,
  3    CONSTRAINT  f_fk FOREIGN KEY (id) REFERENCES  p(id)
  4  );

Table created.

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> alter table f DISABLE CONSTRAINT f_fk
  2  ;

Table altered.

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> insert into p values (1);

1 row created.

ARROW1:(DEMO@HAWKA):PRIMARY> insert into f values (2);

1 row created.

ARROW1:(DEMO@HAWKA):PRIMARY> commit;

Commit complete.

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> alter table f ENABLE CONSTRAINT f_fk
  2  EXCEPTIONS INTO exceptions
  3  ;
alter table f ENABLE CONSTRAINT f_fk
                                *
ERROR at line 1:
ORA-02298: cannot validate (DEMO.F_FK) - parent keys not found


ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> select owner,table_name,constraint_type,R_CONSTRAINT_NAME
  2  from dba_constraints
  3  where owner in ('DEMO')
  4  and table_name in ('P','F')
  5  order by owner, table_name,R_CONSTRAINT_NAME asc,constraint_type desc
  6  ;

OWNER                TABLE_NAME           C R_CONSTRAINT_NAME
-------------------- -------------------- - --------------------
DEMO                 F                    R P_PK
DEMO                 F                    C
DEMO                 P                    P
DEMO                 P                    C

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> select owner,table_name,constraint_type,R_CONSTRAINT_NAME
  2  from dba_constraints
  3  where owner in ('DEMO')
  4  and constraint_name in ('P_PK')
  5  order by owner, table_name,R_CONSTRAINT_NAME asc,constraint_type desc
  6  ;

OWNER                TABLE_NAME           C R_CONSTRAINT_NAME
-------------------- -------------------- - --------------------
DEMO                 P                    P

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> select * from exceptions
  2  ;

ROW_ID             OWNER                TABLE_NAME           CONSTRAINT
------------------ -------------------- -------------------- ------------------------------
AAAEv1AAFAAAAerAAA DEMO                 F                    F_FK

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> select f.*
  2  from f, exceptions
  3  where exceptions.constraint='F_FK'
  4  and f.rowid=exceptions.row_id
  5  ;

        ID
----------
         2

ARROW1:(DEMO@HAWKA):PRIMARY>

February 28, 2017

Goldengate 12c OGG-01117 core dumped

Filed under: 12c,GoldenGate — mdinh @ 11:47 pm

This probably applies to other versions as well.

Don’t you love it when there’s a simple solution?

oracle@test1:/opt/oracle/12.2.0/ggs01$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.

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



GGSCI (test1) 1> create subdirs

Creating subdirectories under current directory /oracle/12.2.0/ggs01

Parameter files                /oracle/12.2.0/ggs01/dirprm: created
Report files                   /oracle/12.2.0/ggs01/dirrpt: created
Checkpoint files               /oracle/12.2.0/ggs01/dirchk: created
Process status files           /oracle/12.2.0/ggs01/dirpcs: created
SQL script files               /oracle/12.2.0/ggs01/dirsql: created
Database definitions files     /oracle/12.2.0/ggs01/dirdef: created
Extract data files             /oracle/12.2.0/ggs01/dirdat: created
Temporary files                /oracle/12.2.0/ggs01/dirtmp: created
Credential store files         /oracle/12.2.0/ggs01/dircrd: created
Masterkey wallet files         /oracle/12.2.0/ggs01/dirwlt: created
Dump files                     /oracle/12.2.0/ggs01/dirdmp: created


GGSCI (test1) 2> create wallet

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (test1) 3> add credentialstore

Credential store created in ./dircrd/.

GGSCI (test1) 4> alter credentialstore add user ggs_user alias ggs_user
Password:

Credential store in ./dircrd/ altered.

GGSCI (test1) 5> dblogin useridalias ggs_user

Source Context :
  SourceModule            : [ggapp.util.pcs]
  SourceID                : [/scratch/aime/adestore/views/aime_adc4150330/oggcore/OpenSys/src/gglib/ggapp/pcsutl.c]
  SourceFunction          : [AbendHandler]
  SourceLine              : [1036]
  ThreadBacktrace         : [22] elements
                          : [/oracle/12.2.0/ggs01/libgglog.so(CMessageContext::AddThreadContext()+0x1b) [0x7f746dc0066b]]
                          : [/oracle/12.2.0/ggs01/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x134) [0x7f746dbfa734]]
                          : [/oracle/12.2.0/ggs01/libgglog.so(_MSG_ERR_SIGNAL_RECEIVED(CSourceContext*, int, char const*, CMessageFactory::MessageDisposition)+0x3a) [0x7f746dbde41d]]
                          : [ggsci() [0x52a977]]
                          : [/lib64/libpthread.so.0(+0xf850) [0x7f7469f72850]]
                          : [/lib64/libpthread.so.0(pthread_mutex_lock+0x4) [0x7f7469f6c444]]
                          : [ggsci(ggs::gglib::MultiThreading::Mutex::Lock()+0x9) [0x556bb9]]
                          : [ggsci(CContextItem::operator char const*() const+0x1d) [0x576b03]]
                          : [ggsci(ggs::gglib::ggdbora::OraConnUtil::initConnectionLogon(ggs::gglib::ggdbapi::DBAuthParams const&)+0x3d0) [0x5d13b0]]
                          : [ggsci(ggs::gglib::ggdbora::OraConnUtil::openDataSource(ggs::gglib::ggdbapi::DBAuthParams const&)+0x28) [0x5d1578]]
                          : [ggsci() [0x57c20c]]
                          : [ggsci(gl_db_login(char const*, char const*, ggs::gglib::ggapp::CDBObjName<(DBObjType)11>&, ggs::gglib::ggapp::CDBObjName<(DBObjType)12>&, short, char (&) [2048])+0x61) [0x57c7a1]]
                          : [ggsci() [0x6437a6]]
                          : [ggsci(GGSCIDB_get_command(char const*, char const*, char const*, short, short, char (&) [2048])+0x1a0) [0x63ef00]]
                          : [ggsci(do_cmd(char*, unsigned long, char*, unsigned long)+0xe05) [0x6632a5]]
                          : [ggsci() [0x66746b]]
                          : [ggsci(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x60) [0x5550c0]]
                          : [ggsci(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x14d) [0x555e0d]]
                          : [ggsci(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0xb1) [0x555ef1]]
                          : [ggsci(main+0x3b) [0x667a7b]]
                          : [/lib64/libc.so.6(__libc_start_main+0xe6) [0x7f7465cdfc36]]
                          : [ggsci() [0x4f5b89]]

2017-02-28 20:52:47  ERROR   OGG-01117  Received signal: Segmentation violation (11).

2017-02-28 20:52:47  ERROR   OGG-01668  PROCESS ABENDING.
Segmentation fault (core dumped)

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

Opatch Apply Instructions: Ensure that you set the ORACLE_HOME environment variable to the Oracle GoldenGate home.

I had just completed patching Goldengate and forgot to set ORACLE_HOME back to DB_HOME.

oracle@test1:/opt/oracle/12.2.0/ggs01$ env|grep HOME
GG_HOME=/opt/oracle/12.2.0/ggs01
HOME=/oracle
ORACLE_HOME=/opt/oracle/12.2.0/ggs01
ACS_HOME=/oracle/acsprod

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

oracle@test1:/opt/oracle/12.2.0/ggs01$ source ~/.orcl
The Oracle base remains unchanged with value /opt/oracle
oracle@test1:/opt/oracle/12.2.0/ggs01$ env|grep HOME
GG_HOME=/opt/oracle/12.2.0/ggs01
HOME=/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
ACS_HOME=/oracle/acsprod

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

oracle@test1:/opt/oracle/12.2.0/ggs01$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.

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



GGSCI (test1) 1> dblogin useridalias ggs_user
Successfully logged into database.

GGSCI (test1 as ggs_user@orcl) 2> exit
oracle@test1:/opt/oracle/12.2.0/ggs01$

Goldengate 12c PREPARECSN BUG RESOLUTION

Filed under: 12c,GoldenGate — mdinh @ 2:56 am

It looks like I have encountered BUG which applies to any platform but Oracle only has patch for Solaris Sparc.

Anyone working for Oracle able to assist here please?

ADD SCHEMATRANDATA Throws OGG-01780 Missing/Invalid argument(s) on ADD/INFO/DELETE SCHEMATRANDATA command. (Doc ID 2188988.1)

Oracle GoldenGate – Version 12.2.0.1.0 and later
Information in this document applies to any platform.

Patch 24601324: Patch FOR MLR 24590215: Solaris Sparc: Oracle12c: OGG 12.2.0.1.160517

Only 3 options accepted after ADD SCHEMATRANDATA causing the issue.

The current patch will change the code to accept 5 options after ADD SCHEMATRANDATA

DEMO:

WAIT
Wait for any in-flight transactions and prepare table instantiation.

LOCK
Put a lock on the table (to prepare for table instantiation).

NOWAIT
Default behavior, preparing for instantiation is done immediately.

NONE
No instantiation preparation occurs.

++++++++++++++++++++++++++++++

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$ cat /etc/oracle-release
Oracle Linux Server release 6.6
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

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



GGSCI (arrow1.localdomain) 1> DBLOGIN USERIDALIAS ggs_user
Successfully logged into database.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 2> DELETE SCHEMATRANDATA demo

2017-02-27 18:45:16  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema demo.

2017-02-27 18:45:16  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 3> ADD SCHEMATRANDATA demo PREPARECSN WAIT

2017-02-27 18:45:57  INFO    OGG-01788  SCHEMATRANDATA has been added on schema demo.

2017-02-27 18:45:57  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema demo.

2017-02-27 18:45:57  INFO    OGG-10154  Schema level PREPARECSN set to mode WAIT on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 4> DELETE SCHEMATRANDATA demo

2017-02-27 18:46:00  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema demo.

2017-02-27 18:46:00  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 5> ADD SCHEMATRANDATA demo PREPARECSN NOWAIT

2017-02-27 18:46:10  INFO    OGG-01788  SCHEMATRANDATA has been added on schema demo.

2017-02-27 18:46:10  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema demo.

2017-02-27 18:46:10  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 6> DELETE SCHEMATRANDATA demo

2017-02-27 18:46:18  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema demo.

2017-02-27 18:46:18  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 7> ADD SCHEMATRANDATA demo ALLCOLS PREPARECSN NOWAIT

2017-02-27 18:46:29  ERROR   OGG-01780  Missing/Invalid argument(s) on ADD/INFO/DELETE SCHEMATRANDATA command.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 8> ADD SCHEMATRANDATA demo ALLCOLS PREPARECSN WAIT

2017-02-27 18:46:38  ERROR   OGG-01780  Missing/Invalid argument(s) on ADD/INFO/DELETE SCHEMATRANDATA command.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 9> ADD SCHEMATRANDATA demo ALLCOLS PREPARECSN

2017-02-27 18:46:46  INFO    OGG-01788  SCHEMATRANDATA has been added on schema demo.

2017-02-27 18:46:46  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema demo.

2017-02-27 18:46:46  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema demo.

2017-02-27 18:46:46  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 10> info SCHEMATRANDATA demo

2017-02-27 18:46:56  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema DEMO.

2017-02-27 18:46:56  INFO    OGG-01981  Schema level supplemental logging is enabled on schema DEMO for all columns.

2017-02-27 18:46:56  INFO    OGG-10462  Schema DEMO have 2 prepared tables for instantiation.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 11> exit
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$

UPDATE:

Download and Apply following patch for the appropriate DB version:
Patch 25445840: ORACLE GOLDENGATE V12.2.0.1.170221 FOR Oracle 11g

p25445840_12201170221_Linux-x86-64.zip
p6880880_112000_Linux-x86-64.zip

oracle@test1:/opt/oracle/12.2.0/ggs01$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.

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



GGSCI (test1) 1> DBLOGIN USERIDALIAS ggs_user
Successfully logged into database.

GGSCI (test1 as ggs_user@orcl) 2> ADD SCHEMATRANDATA ggs_test ALLCOLS PREPARECSN WAIT

2017-02-28 20:59:06  INFO    OGG-01788  SCHEMATRANDATA has been added on schema ggs_test.

2017-02-28 20:59:06  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema ggs_test.

2017-02-28 20:59:06  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema ggs_test.

2017-02-28 20:59:06  INFO    OGG-10154  Schema level PREPARECSN set to mode WAIT on schema ggs_test.

GGSCI (test1 as ggs_user@orcl) 3> versions
Operating System:
Linux
Version #1 SMP Tue Jun 23 16:02:31 UTC 2015 (4b89d0c), Release 3.0.101-63-default
Node: test1
Machine: x86_64

Database:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

GGSCI (test1 as ggs_user@orcl) 4> exit
oracle@test1:/opt/oracle/12.2.0/ggs01$

February 26, 2017

dbca does hard coding

Filed under: 11g,oracle — mdinh @ 3:12 pm

Creating Additional Data Dictionary Structures

catoctk.sql: Oracle Cryptographic Toolkit package

owminst.plb: Workspace Manager

Workspace Manager is installed by default in the seed database and in all databases created by the Database Configuration Assistant (DBCA).

However, in all other Oracle databases, such as those you create with a customized procedure, you must install Workspace Manager before you can use its features.

I know what you are thinking, “What’s the BIG deal?”.  For the most part, it’s not.

However, when a person uses dbca to create a database with different ORACLE_HOME, SID from one server versus another, it becomes inefficient to search and replace all the hard coded values.

Now I know what you are thinking, “Why are ORACLE_HOME’s different to begin with and why don’t I just use dbca to create another database?”

That’s not the scope for this post :=) but I am sure you can ascertain the answers.

oracle@arrow1:HAWKA:/u01/app/oracle/admin/foo/scripts
$ ls -alrt

total 40
drwxr-x---. 3 oracle oinstall 4096 Feb 23 20:45 ..
-rw-r-----. 1 oracle oinstall 1948 Feb 23 20:45 init.ora
-rwxr-xr-x. 1 oracle oinstall  627 Feb 23 20:45 foo.sh
-rw-r-----. 1 oracle oinstall 1143 Feb 23 20:45 CreateDB.sql
-rw-r-----. 1 oracle oinstall  346 Feb 23 20:45 CreateDBFiles.sql
-rw-r-----. 1 oracle oinstall  768 Feb 23 20:45 CreateDBCatalog.sql
-rw-r-----. 1 oracle oinstall  506 Feb 23 20:45 lockAccount.sql
-rwxr-xr-x. 1 oracle oinstall  656 Feb 23 20:45 foo.sql
drwxr-x---. 2 oracle oinstall 4096 Feb 23 20:45 .
-rw-r-----. 1 oracle oinstall  966 Feb 23 20:45 postDBCreation.sql

oracle@arrow1:HAWKA:/u01/app/oracle/admin/foo/scripts
$ cat foo.sh

#!/bin/sh

OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/foo/adump
mkdir -p /u01/app/oracle/admin/foo/dpdump
mkdir -p /u01/app/oracle/admin/foo/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/foo
mkdir -p /u01/app/oracle/fast_recovery_area
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/oracle/product/11.2.0.4/db_1/dbs
umask ${OLD_UMASK}
ORACLE_SID=foo; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab: foo:/u01/app/oracle/product/11.2.0.4/db_1:Y
/u01/app/oracle/product/11.2.0.4/db_1/bin/sqlplus /nolog @/u01/app/oracle/admin/foo/scripts/foo.sql

oracle@arrow1:HAWKA:/u01/app/oracle/admin/foo/scripts
$ cat foo.sql

set verify off
-- DEFINE sysPassword="hacker"
-- DEFINE systemPassword="hacker"
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
host /u01/app/oracle/product/11.2.0.4/db_1/bin/orapwd file=/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwfoo force=y
@/u01/app/oracle/admin/foo/scripts/CreateDB.sql
@/u01/app/oracle/admin/foo/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/foo/scripts/CreateDBCatalog.sql
host /u01/app/oracle/product/11.2.0.4/db_1/bin/srvctl add database -d foo -o /u01/app/oracle/product/11.2.0.4/db_1 -n foo
@/u01/app/oracle/admin/foo/scripts/lockAccount.sql
@/u01/app/oracle/admin/foo/scripts/postDBCreation.sql

oracle@arrow1:HAWKA:/u01/app/oracle/admin/foo/scripts
$ cat CreateDBCatalog.sql

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/foo/scripts/CreateDBCatalog.log append
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/catalog.sql;
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/catblock.sql;
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/catproc.sql;
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/catoctk.sql;
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/owminst.plb;
connect "SYSTEM"/"&&systemPassword"
@/u01/app/oracle/product/11.2.0.4/db_1/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /u01/app/oracle/admin/foo/scripts/sqlPlusHelp.log append
@/u01/app/oracle/product/11.2.0.4/db_1/sqlplus/admin/help/hlpbld.sql helpus.sql;
spool off
spool off

Here’s one that I have for creating single instance DB.

Not the best as I should have labeled “pro *** Running initxml.sql ***”
with its corresponding COMP_ID or COMP_NAME versus name of the sql script.

NOTE: for production environment, you would not autoextend datafile 128M (really bad for performance).

define sysPassword="hacker"
define systemPassword="hacker"
spool crdb.log
set echo on timing on time on
host echo $ORACLE_SID
host sysresv
create spfile from pfile;
startup force nomount;
CREATE DATABASE
MAXINSTANCES 1
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 100
MAXDATAFILES 400
ARCHIVELOG
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
SET TIME_ZONE='US/Pacific'
USER SYS IDENTIFIED BY "&&sysPassword"
USER SYSTEM IDENTIFIED BY "&&systemPassword"
EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 513M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M
SYSAUX DATAFILE SIZE 257M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M
LOGFILE GROUP 1 SIZE 200M,GROUP 2 SIZE 200M,GROUP 3 SIZE 200M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 8192M
DEFAULT TABLESPACE users DATAFILE SIZE 129M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M
UNDO TABLESPACE undotbs DATAFILE SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 8192M;
spool off
--
spool run_catalog.log
pro *** Running catalog.sql ***
@?/rdbms/admin/catalog.sql
spool off
--
spool run_catproc.log
pro *** Running catproc.sql ***
@?/rdbms/admin/catproc.sql
spool off
/* 
-- BEG Add additional components as required.
spool run_initjvm.log
pro *** Running initjvm.sql ***
@?/javavm/install/initjvm.sql
spool off
--
spool run_initxml.log
pro *** Running initxml.sql ***
@?/xdk/admin/initxml.sql
spool off
--
spool run_xmlja.log
pro *** Running initxml.sql ***
@?/xdk/admin/xmlja.sql
spool off
--
spool run_catjava.log
pro *** Running catjava.sql ***
@?/rdbms/admin/catjava.sql
spool off
--
spool run_catxdbj.log
pro *** Running catcatxdbj.sql ***
@?/rdbms/admin/catxdbj.sql
spool off
-- END Add additional components as required.
*/ 
spool crdb.log append 
exec dbms_scheduler.set_scheduler_attribute(attribute=>'default_timezone',value=>'US/Pacific'); 
connect system/"&&systemPassword" 
@?/sqlplus/admin/pupbld.sql 
exit

February 19, 2017

Data Pump or Data Pain Part04 – The End

Filed under: 11g,DataPump — mdinh @ 7:10 pm

Please take a look at my notes below:
DataPump: Schema/DB Migration Notes

« Previous PageNext Page »

Blog at WordPress.com.