Thinking Out Loud

April 28, 2017

Bug 18411339 – Low performance V$ARCHIVE_GAP (11.2.0.4) fix 12.2.0.1

Filed under: 11g,12c,Dataguard — mdinh @ 12:31 am

Just came across bug from 11.2.0.4 not fixed until 12.2 base release. Seriously Oracle?
In the test case below, it looks to have only affected 11.2.0.4 – 64bit for AIX Version 7.1 since I recall this was not an issues for Linux.

11.2.0.4.0
select * from v$archive_gap;
Elapsed: 00:01:48.93

12.1.0.2.0
select * from v$archive_gap;
Elapsed: 00:00:06.60

Work Around

select USERENV('Instance'), high.thread#, low.lsq, high.hsq
 from
  (select a.thread#, rcvsq, min(a.sequence#)-1 hsq
   from v$archived_log a,
        (select lh.thread#, lh.resetlogs_change#, max(lh.sequence#) rcvsq
           from v$log_history lh, v$database_incarnation di
          where lh.resetlogs_time = di.resetlogs_time
            and lh.resetlogs_change# = di.resetlogs_change#
            and di.status = 'CURRENT'
            and lh.thread# is not null
            and lh.resetlogs_change# is not null
            and lh.resetlogs_time is not null
         group by lh.thread#, lh.resetlogs_change#
        ) b
   where a.thread# = b.thread#
     and a.resetlogs_change# = b.resetlogs_change#
     and a.sequence# > rcvsq
   group by a.thread#, rcvsq) high,
 (select srl_lsq.thread#, nvl(lh_lsq.lsq, srl_lsq.lsq) lsq
   from
     (select thread#, min(sequence#)+1 lsq
      from
        v$log_history lh, x$kccfe fe, v$database_incarnation di
      where to_number(fe.fecps) <= lh.next_change# and to_number(fe.fecps) >= lh.first_change#
        and fe.fedup!=0 and bitand(fe.festa, 12) = 12
        and di.resetlogs_time = lh.resetlogs_time
        and lh.resetlogs_change# = di.resetlogs_change#
        and di.status = 'CURRENT'
      group by thread#) lh_lsq,
     (select thread#, max(sequence#)+1 lsq
      from
        v$log_history
      where (select min( to_number(fe.fecps))
             from x$kccfe fe
             where fe.fedup!=0 and bitand(fe.festa, 12) = 12)
      >= next_change#
      group by thread#) srl_lsq
   where srl_lsq.thread# = lh_lsq.thread#(+)
  ) low
 where low.thread# = high.thread#
 and lsq < = hsq and hsq > rcvsq;

April 16, 2017

Be Prepared When Using DBFS

Filed under: 12c,GoldenGate,oracle — mdinh @ 9:18 pm

Goldegate 12c implementation for 2-node RAC with XAG from Grid Home using DBFS.

We are facing situation where DBFS has 41G free while DBFS_TS has 1.5G free and having to resize BIGFILE tablespace.

Tablespace and DBFS size is 45G.

Basically, 45G tablespace is used to maintain 4G Goldengate files.

Based on steps followed from NOTE:1453350.1 – How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized?

Next step is to Shrink a SECUREFILE LOB using Online Redefinition (DBMS_REDEFINITION)? (Document  1394613.1)

At this point, does not look pretty.

Here are all the research I have gone through.

NOTE:1150157.1 - List of Critical Patches Required For Oracle 11.2 DBFS and DBFS Client
BUG:13824914 - DBFS FREE SPACE IS NOT RECLAIMED AFTER DELETIONS.
BUG:12662040 - SECUREFILE LOB SEGMENT KEEPS GROWING IN CASE OF PLENTY OF FREE SPACE

How DBFS Reclaims Free Space After Files Are Deleted (Doc ID 1438356.1) 
NOTE:162345.1 - LOBS - Storage, Read-consistency and Rollback
NOTE:386341.1 - How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM
--------------------------------------------------------------------------------
NOTE:1453350.1 - How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized?
3) Determine if the LOBSEGMENT is a candidate for shrink / reorganization

The following query will show the extents allocated for the LOBSEGMENT

SELECT BYTES, COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_NAME = ''  GROUP BY BYTES ORDER BY 2;

If NON Data Bytes is  one or more of the extent sizes from this query ...
     then this segment is a candiate for a shrink / reorganization as this process will likely be able to reduce the size of the LOBSEGMENT by at least one extent

4) If #3 is true ... then the method in the following note may be used to shrink / reorganize the lob segment

How to Shrink a SECUREFILE LOB using Online Redefinition (DBMS_REDEFINITION)? (Document  1394613.1)
--------------------------------------------------------------------------------
NOTE:66431.1 - LOBS - Storage, Redo and Performance Issues

April 1, 2017

Goldengate 12c Troubleshooting XAGENABLE

Filed under: 12c,GoldenGate — mdinh @ 2:52 pm

Environment:
Oracle RAC with DBFS running as oracle
Goldengate12c running as ggsuser using DBFS and XAGENABLE
Monitoring is running as monitor

ERROR: Transparent Integration with XAG is Enabled but CRS/XAG is not Available (Doc ID 2240440.1)

Modify GLOBALS per Doc ID 2240440.1

$ cat GLOBALS

setenv (GRID_HOME='/u01/app/12.1.0/grid')
XAGENABLE

$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.160823 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_160805.1058_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug  5 2016 23:35:08
Operating system character set identified as UTF-8.

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



2017-03-31 11:24:06  INFO    OGG-02095  Successfully set environment variable GRID_HOME=/u01/app/12.1.0/grid.

*** Could not open error log ggserr.log (error 13,Permission denied) ***

2017-03-31 11:24:06  INFO    OGG-02095  Successfully set environment variable GRID_HOME=/u01/app/12.1.0/grid.
ERROR: Transparent Integration with XAG is enabled but CRS/XAG is not available.

chmod 775 ggserr.log and set umask 002 in .bash_profile

$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.160823 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_160805.1058_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug  5 2016 23:35:08
Operating system character set identified as UTF-8.

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



2017-03-31 11:27:58  INFO    OGG-02095  Successfully set environment variable GRID_HOME=/u01/app/12.1.0/grid.

2017-03-31 11:27:58  INFO    OGG-02095  Successfully set environment variable GRID_HOME=/u01/app/12.1.0/grid.
ERROR: Transparent Integration with XAG is enabled but CRS/XAG is not available.

$ export GRID_HOME=/u01/app/12.1.0/grid
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.160823 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_160805.1058_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug  5 2016 23:35:08
Operating system character set identified as UTF-8.

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



2017-03-31 11:29:40  INFO    OGG-02095  Successfully set environment variable GRID_HOME=/u01/app/12.1.0/grid.

2017-03-31 11:29:40  INFO    OGG-02095  Successfully set environment variable GRID_HOME=/u01/app/12.1.0/grid.

GGSCI 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt   XAG                           


MANAGER     RUNNING                                                  MANAGED/xag.ggs.goldengate  


GGSCI 2> exit

See anything wrong so far? Extracts are running and not shown.

As it turns out from strace of ggsci – no RW permission.
open(“./dirchk/*.cpe”, O_RDWR) = -1 EACCES (Permission denied)

Remembered my post about XAG many moons ago about role separation and what a PITA it was when implemented incorrectly?

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 14, 2017

Goldengate 12c runInstaller UNIX_GROUP_NAME | INVENTORY_LOCATION

Filed under: 12c,GoldenGate,oracle — mdinh @ 2:20 pm

I keep finding ways to break things because the environments are not consistent.

UNIX_GROUP_NAME
This parameter is the UNIX group name to be set for the inventory on UNIX platforms.
Note: The UNIX group name is used for first-time installations only.

Details for oraInventory

$ cat /etc/oraInst.loc
inventory_loc=/u01/app/oracle/oraInventory
inst_group=oinstall

Notice the group is dba while /etc/oraInst.loc has oinstall.
Probably because I specified UNIX_GROUP_NAME=dba?
Don’t remembered since this environment is really old.

$ ls -ld /u01/app/oracle/oraInventory
drwxrwx---. 6 oracle dba 4096 Feb 14 04:50 /u01/app/oracle/oraInventory

What happens when INVENTORY_LOCATION is not the same as /etc/oraInst.loc ?

oracle@arrow1:HAWKA:/media/sf_OracleSoftware/GoldenGate_12.2.0.1/fbo_ggs_Linux_x64_shiphome/Disk1
$ ./runInstaller -silent -showProgress -waitforcompletion INSTALL_OPTION=ORA11g SOFTWARE_LOCATION=/u01/app/12.2.0.1/ggs03 UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/u01/app/oraInventory

++++++++++

oracle@arrow1:HAWKA:/home/oracle
$ ls -ld /u01/app/oraInventory
drwxrwx---. 5 oracle oinstall 4096 Feb 14 05:18 /u01/app/oraInventory
oracle@arrow1:HAWKA:/home/oracle
$

++++++++++

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03
$ cd /u01/app/12.2.0.1/ggs03/OPatch/

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ env|grep HOME
GG_HOME=/u01/app/12.2.0.1/ggs01
HOME=/home/oracle

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ export GG_HOME=/u01/app/12.2.0.1/ggs03

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ ./opatch lsinventory -details
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/12.2.0.1/ggs03
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.7
OUI version : 11.2.0.3.0
Log file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/opatch2017-02-14_05-21-32AM.log

List of Homes on this system:

Home name= OraGI12Home1, Location= "/u01/app/12.1.0.2/grid"
Home name= OraDB12Home1, Location= "/u01/app/oracle/product/12.1.0.2/db_1"
Home name= OraDb11g_home1, Location= "/u01/app/oracle/product/11.2.0.4/db_1"
Home name= OraHome1, Location= "/u01/app/12.2.0.1/ggs01"
Home name= OraHome2, Location= "/u01/app/12.2.0.1/ggs02"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
Oracle Home dir. path does not exist in Central Inventory
Oracle Home is a symbolic link
Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$

When INVENTORY_LOCATION is different from /etc/oraInst.loc, use -invPtrLoc.

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ ./opatch lsinventory -details -invPtrLoc /u01/app/oraInventory/oraInst.loc
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/12.2.0.1/ggs03
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oraInventory/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/opatch2017-02-14_05-44-55AM.log

Lsinventory Output file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/lsinv/lsinventory2017-02-14_05-44-55AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle GoldenGate Core                                               12.2.0.0.0
There are 1 products installed in this Oracle Home.


Installed Products (6):

Installer SDK Component                                              11.2.0.3.0
Java Development Kit                                                 1.6.0.65.0
Oracle GoldenGate Core                                               12.2.0.0.0
Oracle GoldenGate for Oracle 11g                                     12.2.0.0.0
Oracle One-Off Patch Installer                                       11.2.0.1.7
Oracle Universal Installer                                           11.2.0.3.0
There are 6 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$

Just out of curiosity, what’s the difference between ORA11g|ORA12c?

oracle@arrow1:HAWKA:/media/sf_OracleSoftware/GoldenGate_12.2.0.1/fbo_ggs_Linux_x64_shiphome/Disk1
$ ./runInstaller -silent -showProgress -waitforcompletion INSTALL_OPTION=ORA12c SOFTWARE_LOCATION=/u01/app/12.2.0.1/ggs03 UNIX_GROUP_NAME=dba INVENTORY_LOCATION=/u01/app/oraInventory2
Starting Oracle Universal Installer...

++++++++++

oracle@arrow1:HAWKA:/home/oracle
$ cd /u01/app/12.2.0.1/ggs03/OPatch/

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ cat /u01/app/oraInventory2/oraInst.loc
inventory_loc=/u01/app/oraInventory2
inst_group=dba

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ ls -ld /u01/app/oraInventory2/
drwxrwx---. 5 oracle dba 4096 Feb 14 06:08 /u01/app/oraInventory2/

++++++++++

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ ./opatch lsinventory -details -invPtrLoc /u01/app/oraInventory2/oraInst.loc
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/12.2.0.1/ggs03
Central Inventory : /u01/app/oraInventory2
   from           : /u01/app/oraInventory2/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/opatch2017-02-14_06-09-42AM.log

Lsinventory Output file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/lsinv/lsinventory2017-02-14_06-09-42AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle GoldenGate Core                                               12.2.0.0.0
There are 1 products installed in this Oracle Home.


Installed Products (6):

Installer SDK Component                                              11.2.0.3.0
Java Development Kit                                                 1.6.0.65.0
Oracle GoldenGate Core                                               12.2.0.0.0
Oracle GoldenGate for Oracle 12c                                     12.2.0.0.0
Oracle One-Off Patch Installer                                       11.2.0.1.7
Oracle Universal Installer                                           11.2.0.3.0
There are 6 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$

February 13, 2017

Goldengate 12c Find log sequence#, rba# for integrated extract (Doc ID 2006932.1)

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

When using integrated extract, info command does not show Seqno, RBA, e.g. Seqno 3292, RBA 79236752.
Command send status will show Seqno, RBA; is not usable when process is stopped.
For Oracle GoldenGate – Version 12.1.2.1.2 and later, there is now debug option to retrieve Seqno, RBA.

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> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E_HAWK      00:00:10      00:00:06
EXTRACT     STOPPED     P_HAWK      00:00:00      00:02:03


GGSCI (arrow1.localdomain) 2> info e*

EXTRACT    E_HAWK    Last Started 2017-02-12 18:35   Status RUNNING
Checkpoint Lag       00:00:10 (updated 00:00:08 ago)
Process ID           1665
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-02-12 18:36:06
                     SCN 0.4928929 (4928929)


GGSCI (arrow1.localdomain) 3> info e* debug

EXTRACT    E_HAWK    Last Started 2017-02-12 18:35   Status RUNNING
Checkpoint Lag       00:00:10 (updated 00:00:02 ago)
Process ID           1665
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-02-12 18:36:16  Seqno 3292, RBA 79236752
                     SCN 0.4928939 (4928939)


GGSCI (arrow1.localdomain) 4> send e* status

Sending STATUS request to EXTRACT E_HAWK ...


EXTRACT E_HAWK (PID 1665)
  Current status: Recovery complete: Processing data

  Current read position:
  Redo thread #: 1
  Sequence #: 3292
  RBA: 79263888
  Timestamp: 2017-02-12 18:36:47.000000
  SCN: 0.4928987 (4928987)
  Current write position:
  Sequence #: 0
  RBA: 1420
  Timestamp: 2017-02-12 18:36:56.251219
  Extract Trail: ./dirdat/aa



GGSCI (arrow1.localdomain) 5> stop e*

Sending STOP request to EXTRACT E_HAWK ...
Request processed.


GGSCI (arrow1.localdomain) 6> send e* status

Sending STATUS request to EXTRACT E_HAWK ...

ERROR: sending message to EXTRACT E_HAWK (Connection reset by peer).


GGSCI (arrow1.localdomain) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     E_HAWK      00:00:07      00:00:13
EXTRACT     STOPPED     P_HAWK      00:00:00      00:03:09


GGSCI (arrow1.localdomain) 8> info e* debug

EXTRACT    E_HAWK    Last Started 2017-02-12 18:35   Status STOPPED
Checkpoint Lag       00:00:07 (updated 00:00:19 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-02-12 18:37:07  Seqno 3292, RBA 79275152
                     SCN 0.4929013 (4929013)


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

February 9, 2017

Steps to Recreate Central Inventory in Real Applications Clusters (Doc ID 413939.1)

Filed under: 12c,RAC — mdinh @ 3:13 am

$ echo $ORACLE_HOME

/u01/app/oracle/product/12.1.0/db_1

$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME

Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2017-02-08_15-56-03PM_1.log

List of Homes on this system:

Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

This happened due to error during install. – oraInventory mismatch.

$ cat /etc/oraInst.loc
inst_group=oinstall
inventory_loc=/u01/app/oraInventory

$ cd /u01/software/database
$ export DISTRIB=`pwd`
$ ./runInstaller -silent -showProgress -waitforcompletion -force -ignorePrereq -responseFile $DISTRIB/response/db_install.rsp \
> oracle.install.option=INSTALL_DB_SWONLY \
> UNIX_GROUP_NAME=oinstall \
> INVENTORY_LOCATION=/u01/app/oracle/oraInventory \

Backup oraInventory for both nodes and attachHome

$ cd $ORACLE_HOME/oui/bin
$ ./runInstaller -silent -ignoreSysPrereqs -attachHome \
ORACLE_HOME="/u02/app/12.1.0/grid" ORACLE_HOME_NAME="OraGI12Home1" \
LOCAL_NODE="node01" CLUSTER_NODES="{node01,node02}" CRS=true
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'AttachHome' was successful.

$ ./runInstaller -silent -ignoreSysPrereqs -attachHome \
ORACLE_HOME="/u01/app/oracle/product/12.1.0/db_1" ORACLE_HOME_NAME="OraDB12Home1" \
LOCAL_NODE="node01" CLUSTER_NODES="{node01,node02}"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'AttachHome' was successful.

February 8, 2017

runcluvfy.sh -pre crsinst NTP failed PRVF-07590 PRVG-01017

Filed under: 12c,RAC — mdinh @ 12:56 pm

12c (12.1.0.2.0) RAC Oracle Linux Server release 7.3
/u01/software/grid/runcluvfy.sh stage -pre crsinst -n node1,node2 -verbose

Starting Clock synchronization checks using Network Time Protocol(NTP)...

Checking existence of NTP configuration file "/etc/ntp.conf" across nodes
  Node Name                             File exists?            
  ------------------------------------  ------------------------
  node02                                yes                     
  node01                                yes                     
The NTP configuration file "/etc/ntp.conf" is available on all nodes
NTP configuration file "/etc/ntp.conf" existence check passed

Checking daemon liveness...

Check: Liveness for "ntpd"
  Node Name                             Running?                
  ------------------------------------  ------------------------
  node02                                no                      
  node01                                yes                     
PRVF-7590 : "ntpd" is not running on node "node02"
PRVG-1017 : NTP configuration file is present on nodes "node02" on which NTP daemon or service was not running
Result: Clock synchronization check using Network Time Protocol(NTP) failed

NTP was indeed running on both nodes.
The issue is /var/run/ntpd.pid does not exist on the failed node.
NTP was started with incorrect options.

GOOD:

# cat /etc/sysconfig/ntpd
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

# systemctl status ntpd.service
ntpd.service - Network Time Service
   Loaded: loaded (/usr/lib/systemd/system/ntpd.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2017-02-03 20:37:18 CST; 3 days ago
 Main PID: 22517 (ntpd)
   CGroup: /system.slice/ntpd.service
           /usr/sbin/ntpd -u ntp:ntp -x -u ntp:ntp -p /var/run/ntpd.pid

# ll /var/run/ntpd.*
-rw-r--r-- 1 root root 5 Feb  3 20:37 /var/run/ntpd.pid

BAD:

# cat /etc/sysconfig/ntpd
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

# systemctl status ntpd.service
ntpd.service - Network Time Service           
   Loaded: loaded (/usr/lib/systemd/system/ntpd.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2017-02-03 18:10:23 CST; 3 days ago
 Main PID: 22403 (ntpd)
   CGroup: /system.slice/ntpd.service
           /usr/sbin/ntpd -u ntp:ntp -g           

# ll /var/run/ntpd.*
ls: cannot access /var/run/ntpd.*: No such file or directory

SOLUTION:

Restart ntpd on failed node.

February 5, 2017

12c Database spfile Parameter alias is not created in ASM Diskgroup (Doc ID 1950769.1)

Filed under: 12c,RAC — mdinh @ 8:41 pm

This is new as of 12.1.0.2.

$ srvctl config database -d hawk
Database unique name: hawk
Database name: hawk
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/HAWK/PARAMETERFILE/spfile.264.934897017
Password file: +DATA/hawk/orapwhawk
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: DATA
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: dba
OSOPER group: 
Database instances: hawk1,hawk2
Configured nodes: hawk01,hawk02
Database is administrator managed

Alias will need to be created manually.

SQL> ALTER DISKGROUP dg1 ADD ALIAS '+DG1/rac12c/spfilerac12c.ora'  FOR  '+dg1/rac12c/parameterfile/spfile.271.860077229'; 

WARNING:
I did not create alias and was curious why alias was not created. Now I know.

What I did: echo “SPFILE=’+DATA/HAWK/PARAMETERFILE/spfile.264.934897017” > $ORACLE_HOME/dbs/init$ORACLE_SID.ora

Setting SPFILE Parameter Values for Oracle RAC
http://docs.oracle.com/database/121/RACAD/admin.htm#RACAD815

Next Page »

Create a free website or blog at WordPress.com.