Thinking Out Loud

May 9, 2017

DBFS and XAG for Goldengate P3

Filed under: 12c,GoldenGate — mdinh @ 12:47 am

Start Pump Extract at source failed as shown below.

INFO OGG-00993 Oracle GoldenGate Capture for Oracle, p_test.prm: EXTRACT P_TEST started.
ERROR OGG-01224 Oracle GoldenGate Capture for Oracle, p_test.prm: TCP/IP error 111 (Connection refused), endpoint: ggvip_hawk.local:7809.
ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, p_test.prm: PROCESS ABENDING.

Hypothesis is ports are not opened from source to target and let’s verify configuration.

VIP name and address from DNS.

Name:    ggvip_hawk.local
Address: 10.10.10.201

Recall this is how goldengate instance was created.

# $GRID_HOME/bin/agctl add goldengate gg_xx \
--instance_type target \
--oracle_home /u01/app/oracle/product/12.1.0/db_1 \
--nodes hawk1,hawk2 \
--network 1 --ip 10.10.10.201 \
--user ggsuser --group dba \
--filesystems dbfs_mount \
--gg_home /u03/gg/12.2.0 

From Goldengate instance gg_xx,
Application VIP (gg_xx-vip) is created
using address (10.10.10.201).

Check for xag resource.

ggsuser@hawk1 ~ $ $GRID_HOME/bin/crsctl stat res -t|grep -A2 xag
xag.gg_xx-vip.vip
      1        ONLINE  ONLINE       hawk1                STABLE
xag.gg_xx.goldengate
      1        ONLINE  ONLINE       hawk1                STABLE

Verify Application VIP address assigned.

ggsuser@hawk1 /u03/app/gg/12.2.0 
$ $GRID_HOME/bin/crsctl stat res xag.gg_xx-vip.vip -p|grep USR_ORA_VIP
GEN_USR_ORA_VIP=
USR_ORA_VIP=10.10.10.201
ggsuser@hawk1 /u03/app/gg/12.2.0 
$ nslookup 10.10.10.201
Server:		10.80.107.101
Address:	10.80.107.101#53

name = ggvip_hawk.local
ggsuser@hawk1 /u03/app/gg/12.2.0 $ 
$GRID_HOME/bin/crsctl stat res xag.gg_xx-vip.vip -p
NAME=xag.gg_xx-vip.vip
TYPE=app.appvipx.type
ACL=owner:root:rwx,pgrp:root:r-x,other::r--,group:dba:r-x,user:ggsuser:r-x
ACTIONS=
ACTION_SCRIPT=
ACTION_TIMEOUT=60
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/orarootagent%CRS_EXE_SUFFIX%
APPSVIP_FAILBACK=0
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=1
CHECK_TIMEOUT=0
CLEAN_TIMEOUT=60
DEGREE=1
DELETE_TIMEOUT=60
DESCRIPTION=Application VIP
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
GEN_USR_ORA_STATIC_VIP=
GEN_USR_ORA_VIP=
HOSTING_MEMBERS=
INSTANCE_FAILOVER=1
INTERMEDIATE_TIMEOUT=0
LOAD=1
LOGGING_LEVEL=1
MODIFY_TIMEOUT=60
NLS_LANG=
OFFLINE_CHECK_INTERVAL=0
PLACEMENT=balanced
RELOCATE_BY_DEPENDENCY=1
RESTART_ATTEMPTS=0
SCRIPT_TIMEOUT=60
SERVER_CATEGORY=
SERVER_POOLS=*
START_CONCURRENCY=0
START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network)
START_TIMEOUT=0
STOP_CONCURRENCY=0
STOP_DEPENDENCIES=hard(ora.net1.network)
STOP_TIMEOUT=0
TYPE_VERSION=1.1
UPTIME_THRESHOLD=7d
USER_WORKLOAD=no
USE_STICKINESS=0
USR_ORA_ENV=
USR_ORA_VIP=10.10.10.201
VERSION=12.1.0.1.0

May 7, 2017

free.sql

Filed under: oracle — mdinh @ 11:02 am

Provide info if tablespace is BIGFILE and existing increment by.
What I did might and might not be for the better – alter tablespace TBSNAME_XXXX autoextend on next 1g maxsize 250g;

sqlplus / as sysdba @free.sql TBSNAME_XXXX

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 7 05:47:54 2017

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option


BIG TABLESPACE_NAME                      BLKSZ   DFCT    CT_FRAG MB_FREE_FRAG     MB_FREE    MB_TOTAL PCT_USED   MAX_MB_SZ MAX_PCT_USED
--- ----------------------------------- ------ ------ ---------- ------------ ----------- ----------- -------- ----------- ------------
YES *a s TBSNAME_XXXX                      8192      1         24        3,968      79,788     179,199    97.79     256,000        68.45
                                               ------                         -----------                      -----------
sum                                                 1                              79,788                          256,000


   FILE_ID FILE_NAME                                          AUT          GB      INC_GB      MAX_GB
---------- -------------------------------------------------- --- ----------- ----------- -----------
        11 +DATA/xxx/datafile/TBSNAME_XXXX.274.800368305       YES         175           1         250

SQL> exit

free.sql

set line 150 echo off verify off trimspool off tab off
break on report
COMPUTE sum of mb_used on report
COMPUTE sum of mb_free on report
COMPUTE sum of max_mb_sz on report
COMPUTE sum of dfct on report
COLUMN file_name format a50
COLUMN mb_used format 99,999,999
COLUMN mb_free format 99,999,999
COLUMN mb_total format 99,999,999
COLUMN max_mb_sz format 99,999,999
COLUMN mb_free_frag format 99,999,999
COLUMN dfct format 99999
COLUMN blksz format 99999
COLUMN pct_used format 999.99
COLUMN max_pct_used format 999.99
COLUMN gb format 99,999,999
COLUMN inc_gb format 99,999,999
COLUMN max_gb format 99,999,999
SELECT bigfile,
DECODE(extent_management,'LOCAL','*',' ') ||
DECODE(segment_space_management,'AUTO','a ','m ') ||
DECODE(allocation_type,'SYSTEM','s ','u ') ||
fs.tablespace_name tablespace_name, block_size blksz, dfct,
fs.nfrag ct_frag,
fs.mxfrag / 1048576 mb_free_frag,
fs.free_bytes / 1048576 mb_free,
df.avail / 1048576 mb_total,
(df.avail-fs.mxfrag)/df.avail*100 pct_used,
df.max_bytes / 1048576 max_mb_sz,
(df.avail-fs.mxfrag)/df.max_bytes*100 max_pct_used
FROM dba_tablespaces ts,
(SELECT tablespace_name, count(*) dfct,
SUM(decode(maxbytes,0,user_bytes,greatest(maxbytes,user_bytes))) max_bytes,
SUM(user_bytes) avail
FROM dba_data_files
GROUP BY tablespace_name
) df,
(SELECT tablespace_name, nvl(sum(bytes),0) free_bytes, count(bytes) nfrag, nvl(max(bytes),0) mxfrag
FROM dba_free_space
GROUP BY tablespace_name
) fs
WHERE fs.tablespace_name = ts.tablespace_name(+)
AND fs.tablespace_name = df.tablespace_name
AND regexp_like(fs.tablespace_name,'&1','i')
ORDER BY pct_used desc
;
SELECT
file_id,file_name,autoextensible,
bytes/1024/1024/1024 gb,
increment_by*(bytes/blocks)/1024/1024/1024 inc_gb,
maxbytes/1024/1024/1024 max_gb
FROM dba_data_files
WHERE regexp_like(tablespace_name,'&1','i')
ORDER BY 4 asc
;

May 6, 2017

DBFS and XAG for Goldengate P2

Filed under: 12c,GoldenGate — mdinh @ 4:26 pm

In order to use agctl commands, we need to know goldengate instance_name.

Unfortunately, agctl does not work the same way as srvctl where it’s possible to determine what is configured.

ggsuser@hawk1 ~ $ $ORACLE_HOME/bin/srvctl config database
DBFS

ggsuser@hawk1 ~ $ $GRID_HOME/bin/agctl config goldengate
XAG-212: Instance '' is not yet registered.
ggsuser@hawk1 ~ $ 

How do we find out what the goldengate instance name is? IFF XAG is configured, then grep for it.

ggsuser@hawk1 ~ $ $GRID_HOME/bin/crsctl stat res -t|grep -A2 xag
xag.gg_xx-vip.vip
      1        ONLINE  ONLINE       hawk1                STABLE
xag.gg_xx.goldengate
      1        ONLINE  ONLINE       hawk1                STABLE
--------------------------------------------------------------------------------

ggsuser@hawk1 ~ $ $GRID_HOME/bin/agctl status goldengate gg_xx
Goldengate  instance 'gg_xx' is running on hawk1
ggsuser@hawk1 ~ $ 

Some other useful commands to gather configurations info.

ggsuser@hawk1 ~ $ $GRID_HOME/bin/crsctl stat res|grep xag
NAME=xag.gg_xx-vip.vip
NAME=xag.gg_xx.goldengate
TYPE=xag.goldengate.type
ggsuser@hawk1 ~ $ 

ggsuser@hawk1 ~ $ $GRID_HOME/bin/crsctl stat res|grep -i type|sort -u
TYPE=app.appvipx.type
TYPE=local_resource
TYPE=ora.asm.type
TYPE=ora.cluster_vip_net1.type
TYPE=ora.cvu.type
TYPE=ora.database.type
TYPE=ora.diskgroup.type
TYPE=ora.listener.type
TYPE=ora.mgmtdb.type
TYPE=ora.mgmtlsnr.type
TYPE=ora.network.type
TYPE=ora.oc4j.type
TYPE=ora.ons.type
TYPE=ora.scan_listener.type
TYPE=ora.scan_vip.type
TYPE=xag.goldengate.type

ggsuser@hawk1 ~ $ $GRID_HOME/bin/crsctl stat res -w "TYPE = xag.goldengate.type" -p
NAME=xag.gg_xx.goldengate
TYPE=xag.goldengate.type
ACL=owner:ggsuser:rwx,pgrp:dba:r-x,other::r--
ACTIONS=
ACTION_SCRIPT=%CRS_HOME%/bin/aggoldengateas
ACTION_TIMEOUT=60
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/scriptagent
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=30
CHECK_TIMEOUT=0
CLEAN_TIMEOUT=60
CRITICAL_EXTRACTS=
CRITICAL_REPLICATS=
CRS_ATTRIBUTES=
DATABASES= (No DB dependencies - User Exits)
DATAGUARD_AUTOSTART=no
DB_SERVICES=
DEGREE=1
DELETE_TIMEOUT=60
DESCRIPTION="Oracle GoldenGate Clusterware Resource"
ENABLED=1
ENVIRONMENT_VARS=
FAILOVER_DELAY=0
FAILURE_INTERVAL=600
FAILURE_THRESHOLD=5
FILESYSTEMS=dbfs_mount
GG_HOME=/u03/gg/12.2.0
GG_INSTANCE_TYPE=target
HOSTING_MEMBERS=hawk1 hawk2
INSTANCE_FAILOVER=1
INTERMEDIATE_TIMEOUT=0
JAGENT_AUTOSTART=no
LOAD=1
LOGGING_LEVEL=1
MODIFY_TIMEOUT=60
MONITOR_EXTRACTS=
MONITOR_REPLICATS=
OFFLINE_CHECK_INTERVAL=0
ORACLE_CLIENT_HOME=
ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
PLACEMENT=restricted
RELOCATE_BY_DEPENDENCY=1
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=60
SERVER_CATEGORY=
SERVER_POOLS=
START_CONCURRENCY=0
START_DEPENDENCIES=hard(xag.gg_xx-vip.vip,dbfs_mount) pullup(xag.gg_xx-vip.vip,dbfs_mount)
START_TIMEOUT=300
STOP_CONCURRENCY=0
STOP_DEPENDENCIES=hard(xag.gg_xx-vip.vip,intermediate:dbfs_mount)
STOP_TIMEOUT=300
UPTIME_THRESHOLD=10m
USER_WORKLOAD=no
USE_STICKINESS=0
VERSION=2
VIP_CREATED=1
VIP_NAME=xag.gg_xx-vip.vip
ggsuser@hawk1 ~ $ 

You might be thinking, if there are no dependencies for database, then why is it referencing Database Home?

ggsuser@hawk1 ::/u03/gg/12.2.0
$ ldd ggsci 
	linux-vdso.so.1 =>  (0x00007ffcaa8ff000)
	librt.so.1 => /lib64/librt.so.1 (0x00007f6a02c5b000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007f6a02a56000)
	libgglog.so => /u03/gg/12.2.0/./libgglog.so (0x00007f6a02630000)
	libggrepo.so => /u03/gg/12.2.0/./libggrepo.so (0x00007f6a023ba000)
	libdb-6.1.so => /u03/gg/12.2.0/./libdb-6.1.so (0x00007f6a01fd5000)
	libggperf.so => /u03/gg/12.2.0/./libggperf.so (0x00007f6a01da5000)
	libggparam.so => /u03/gg/12.2.0/./libggparam.so (0x00007f6a00c8d000)
	libicui18n.so.48 => /u03/gg/12.2.0/./libicui18n.so.48 (0x00007f6a0089d000)
	libicuuc.so.48 => /u03/gg/12.2.0/./libicuuc.so.48 (0x00007f6a0051c000)
	libicudata.so.48 => /u03/gg/12.2.0/./libicudata.so.48 (0x00007f69fed57000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f69feb3a000)
	libxerces-c.so.28 => /u03/gg/12.2.0/./libxerces-c.so.28 (0x00007f69fe574000)
	libantlr3c.so => /u03/gg/12.2.0/./libantlr3c.so (0x00007f69fe35b000)
	libnnz12.so => /u01/app/oracle/product/12.1.0/db_1/lib/libnnz12.so (0x00007f69fdc36000)
	libclntsh.so.12.1 => /u01/app/oracle/product/12.1.0/db_1/lib/libclntsh.so.12.1 (0x00007f69fabbf000)
	libons.so => /u01/app/oracle/product/12.1.0/db_1/lib/libons.so (0x00007f69fa97a000)
	libclntshcore.so.12.1 => /u01/app/oracle/product/12.1.0/db_1/lib/libclntshcore.so.12.1 (0x00007f69fa406000)
	libggnnzitp.so => /u03/gg/12.2.0/./libggnnzitp.so (0x00007f69f9922000)
	libm.so.6 => /lib64/libm.so.6 (0x00007f69f9620000)
	libc.so.6 => /lib64/libc.so.6 (0x00007f69f925e000)
	/lib64/ld-linux-x86-64.so.2 (0x00005624a8090000)
	libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f69f8f56000)
	libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f69f8d3f000)
	libmql1.so => /u01/app/oracle/product/12.1.0/db_1/lib/libmql1.so (0x00007f69f8ac8000)
	libipc1.so => /u01/app/oracle/product/12.1.0/db_1/lib/libipc1.so (0x00007f69f8750000)
	libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f69f8537000)
	libaio.so.1 => /lib64/libaio.so.1 (0x00007f69f8335000)

Would’t be much better if Goldengate installation is self contained without having to download and install 2 components!

May 5, 2017

DBFS and XAG for Goldengate P1

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

What’s the difference between the 2 GoldenGate configurations below.

$ $GRID_HOME/bin/agctl config goldengate gg_xx

GoldenGate location is: /u03/gg/12.2.0
GoldenGate instance type is: target
Configured to run on Nodes: arrow1 arrow2
ORACLE_HOME location is: /u01/app/oracle/product/12.1.0/db_1
Databases needed: ora.emu1.db
File System resources needed: dbfs_mount
Extracts to monitor: 
Replicats to monitor: 
Critical extracts: 
Critical replicats: 
Autostart on DataGuard role transition to PRIMARY: no
Autostart JAgent: no

$ $GRID_HOME/bin/agctl config goldengate gg_xx

GoldenGate location is: /u03/gg/12.2.0
GoldenGate instance type is: target
Configured to run on Nodes: hawk1 hawk2
ORACLE_HOME location is: /u01/app/oracle/product/12.1.0/db_1
File System resources needed: dbfs_mount
Extracts to monitor: 
Replicats to monitor: 
Critical extracts: 
Critical replicats: 
Autostart on DataGuard role transition to PRIMARY: no
Autostart JAgent: no

Here are how they are added:

# $GRID_HOME/bin/agctl add goldengate gg_xx \
--instance_type target \
--oracle_home /u01/app/oracle/product/12.1.0/db_1 \
--nodes hawk1,hawk2 \
--network 1 --ip 10.10.10.101 \
--user ggsuser --group dba \
--filesystems dbfs_mount \
--gg_home /u03/gg/12.2.0 \
--databases ora.emu1.db

# $GRID_HOME/bin/agctl add goldengate gg_xx \
--instance_type target \
--oracle_home /u01/app/oracle/product/12.1.0/db_1 \
--nodes arrow1,arrow2 \
--network 1 --ip 10.10.10.201 \
--user ggsuser --group dba \
--filesystems dbfs_mount \
--gg_home /u03/gg/12.2.0 

One is actually source from Database and the other is target User Exits.
But, but, but –instance_type is target for both.
That’s right (meaning your observation is correct) – hehe
Bad implementation – don’t do that.

instance_type – OGG source or OGG target (source, target) (dual is bi-directional)

$XAG_HOME/bin/agctl add goldengate lax_ggate \
--gg_home /acfsmount/ggs112 \
--instance_type dual \
--nodes rac01,rac02 \
--vip_name lax-ggate1-vip \
--filesystems ora.dg_acfs.vg_acfs.acfs \
--databases ora.emu.db \
--oracle_home /u01/app/oracle/product/11.2.0.4/db_1 \
--monitor_extracts ELAX,PLAX_DEN \
--critical_extracts ELAX,PLAX_DEN \
--monitor_replicats RDEN_LAX \
--critical_replicats RDEN_LAX

Why agctl is called from $GRID_HOME in one and $XAG_HOME in another?

$ $GRID_HOME/bin/agctl query releaseversion
The Oracle Grid Infrastructure Agents release version is 3.1.0

$ $GRID_HOME/bin/agctl query deployment
The Oracle Grid Infrastructure Agents deployment is bundled

agctl from GRID_HOME is bundled with install, but older version.
agctl from XAG_HOME is standalone, downloaded and installed from

Oracle Grid Infrastructure Standalone Agents for Oracle Clusterware 11g Rel. 2, 12c Rel. 1 and 12c Rel. 2
http://www.oracle.com/technetwork/database/database-technologies/clusterware/downloads/xag-agents-downloads-3636484.html

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

My Vagrant Journey

Filed under: Vagrant,VirtualBox — mdinh @ 4:09 am

This is probably nothing new.

Wanted to build my own Vagrant box with prerequisite to install GI/DB.

Instead of creating new network, use existing to assign IP.

Shared Folder uses existing location versus having to copy binaries into Vagrant location.

Same Vagrantfile can be used with a little search and replace.

You might ask, why not automate GI/DB install. I like to practice installating and cloning.

Next, install GG and DG.

After, create 2 RAC clusters using the same Vagrant Box?

dinh@CMWPHV1 MINGW64 /f/Vagrant
$ vboxmanage list hostonlyifs
Name:            VirtualBox Host-Only Ethernet Adapter
GUID:            8898fc55-9a80-4d5e-9a82-d2dc776ef00e
DHCP:            Disabled
IPAddress:       192.168.146.1
NetworkMask:     255.255.255.0
IPV6Address:
IPV6NetworkMaskPrefixLength: 0
HardwareAddress: 0a:00:27:00:00:22
MediumType:      Ethernet
Status:          Up
VBoxNetworkName: HostInterfaceNetworking-VirtualBox Host-Only Ethernet Adapter

dinh@CMWPHV1 MINGW64 /f/Vagrant
$ vagrant box list
There are no installed boxes! Use `vagrant box add` to add some.

dinh@CMWPHV1 MINGW64 /f/Vagrant
$ vagrant global-status
id       name   provider state  directory
--------------------------------------------------------------------
There are no active Vagrant environments on this computer! Or,
you haven't destroyed and recreated Vagrant environments that were
started with an older version of Vagrant.

dinh@CMWPHV1 MINGW64 /f/Vagrant
$ cd arrow1/

dinh@CMWPHV1 MINGW64 /f/Vagrant/arrow1
$ vagrant status
Current machine states:

arrow1                    not created (virtualbox)

The environment has not yet been created. Run `vagrant up` to
create the environment. If a machine is not created, only the
default provider will be shown. So if a provider is not listed,
then the machine is not created for that environment.

dinh@CMWPHV1 MINGW64 /f/Vagrant/arrow1
$ vagrant up
Bringing machine 'arrow1' up with 'virtualbox' provider...
==> arrow1: Box 'ol73-min' could not be found. Attempting to find and install...
    arrow1: Box Provider: virtualbox
    arrow1: Box Version: >= 0
==> arrow1: Box file was not detected as metadata. Adding it directly...
==> arrow1: Adding box 'ol73-min' (v0) for provider: virtualbox
    arrow1: Unpacking necessary files from: file:///F:/Vagrant/ol73-min.box
    arrow1:
==> arrow1: Successfully added box 'ol73-min' (v0) for 'virtualbox'!
==> arrow1: Importing base box 'ol73-min'...
==> arrow1: Matching MAC address for NAT networking...
==> arrow1: Setting the name of the VM: arrow1
==> arrow1: Clearing any previously set network interfaces...
==> arrow1: Preparing network interfaces based on configuration...
    arrow1: Adapter 1: nat
    arrow1: Adapter 2: hostonly
==> arrow1: Forwarding ports...
    arrow1: 22 (guest) => 2011 (host) (adapter 1)
==> arrow1: Running 'pre-boot' VM customizations...
==> arrow1: Booting VM...
==> arrow1: Waiting for machine to boot. This may take a few minutes...
    arrow1: SSH address: 127.0.0.1:2011
    arrow1: SSH username: vagrant
    arrow1: SSH auth method: private key
    arrow1: Warning: Remote connection disconnect. Retrying...
==> arrow1: Machine booted and ready!
[arrow1] GuestAdditions 5.1.18 running --- OK.
==> arrow1: Checking for guest additions in VM...
==> arrow1: Setting hostname...
==> arrow1: Configuring and enabling network interfaces...
==> arrow1: Mounting shared folders...
    arrow1: /vagrant => F:/Vagrant/arrow1
    arrow1: /sf_working => C:/dinh/Dropbox/working
    arrow1: /sf_OracleSoftware => F:/OracleSoftware

dinh@CMWPHV1 MINGW64 /f/Vagrant/arrow1
$ cd ../arrow2/

dinh@CMWPHV1 MINGW64 /f/Vagrant/arrow2
$ vagrant up
Bringing machine 'arrow2' up with 'virtualbox' provider...
==> arrow2: Importing base box 'ol73-min'...
==> arrow2: Matching MAC address for NAT networking...
==> arrow2: Setting the name of the VM: arrow2
==> arrow2: Clearing any previously set network interfaces...
==> arrow2: Preparing network interfaces based on configuration...
    arrow2: Adapter 1: nat
    arrow2: Adapter 2: hostonly
==> arrow2: Forwarding ports...
    arrow2: 22 (guest) => 2012 (host) (adapter 1)
==> arrow2: Running 'pre-boot' VM customizations...
==> arrow2: Booting VM...
==> arrow2: Waiting for machine to boot. This may take a few minutes...
    arrow2: SSH address: 127.0.0.1:2012
    arrow2: SSH username: vagrant
    arrow2: SSH auth method: private key
    arrow2: Warning: Remote connection disconnect. Retrying...
==> arrow2: Machine booted and ready!
[arrow2] GuestAdditions 5.1.18 running --- OK.
==> arrow2: Checking for guest additions in VM...
==> arrow2: Setting hostname...
==> arrow2: Configuring and enabling network interfaces...
==> arrow2: Mounting shared folders...
    arrow2: /vagrant => F:/Vagrant/arrow2
    arrow2: /sf_working => C:/dinh/Dropbox/working
    arrow2: /sf_OracleSoftware => F:/OracleSoftware

dinh@CMWPHV1 MINGW64 /f/Vagrant/arrow2
$ vagrant global-status
id       name   provider   state   directory
-----------------------------------------------------------------------
e779ae1  arrow1 virtualbox running F:/Vagrant/arrow1
7642809  arrow2 virtualbox running F:/Vagrant/arrow2

The above shows information about all known Vagrant environments
on this machine. This data is cached and may not be completely
up-to-date. To interact with any of the machines, you can go to
that directory and run Vagrant, or you can use the ID directly
with Vagrant commands from any directory. For example:
"vagrant destroy 1a2b3c4d"

dinh@CMWPHV1 MINGW64 /f/Vagrant/arrow2
$ vagrant box list
ol73-min (virtualbox, 0)

dinh@CMWPHV1 MINGW64 /f/Vagrant/arrow2
$ vboxmanage list runningvms
"arrow1" {d8e472d1-92c1-4211-ac86-99a8461f7cf5}
"arrow2" {66ad90a6-15ad-4096-a04e-e30c40ad2d70}

dinh@CMWPHV1 MINGW64 /f/Vagrant/arrow2
$ cat Vagrantfile
# -*- mode: ruby -*-
# vi: set ft=ruby :

# vboxmanage list vms
# vagrant package --output ol73-min.box --base ol73-min
# vagrant box add --name arrow1 file:///F:/Vagrant/ol73-min.box
# vboxmanage sharedfolder add "arrow1" --name "OracleSoftware" --hostpath "F:\OracleSoftware" --automount
# vboxmanage modifyvm "ol73-min" --natpf1 "ssh,tcp,,2222,,22"

VAGRANTFILE_API_VERSION = "2"
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.define "arrow2" , primary: true do |config|
    config.vm.box = "ol73-min"
    config.vm.box_url = "file:///F:/Vagrant/ol73-min.box"
    config.vm.network "private_network", ip: "192.168.146.12"
    config.vm.network "forwarded_port", guest: 22, host: 2012, host_ip: "127.0.0.1", id: "ssh"
    config.vm.box_check_update = false
    config.ssh.insert_key = false
    config.vm.host_name = "arrow2"
    config.vm.synced_folder "F:\\OracleSoftware", "/sf_OracleSoftware", type: "nfs"
    config.vm.synced_folder "C:\\dinh\\Dropbox\\working", "/sf_working", type: "nfs"
    config.vm.provider "virtualbox" do |vb|
      #vb.customize ["modifyvm", :id, "--natdnshostresolver1", "on"]
      #vb.customize ["modifyvm", :id, "--natdnsproxy1", "on"]
      vb.memory = "1536"
      vb.name = "arrow2"
    end
  end
end

dinh@CMWPHV1 MINGW64 /f/Vagrant/arrow2
$

April 9, 2017

Oracle Linux Vagrant Boxes

Filed under: oracle,Vagrant,VirtualBox — mdinh @ 5:57 pm

Almost like finding gold.

Oracle publishes Vagrant boxes based on Oracle Linux

April 8, 2017

Download and Install Vagrant Box Locally

Filed under: oracle,Vagrant — mdinh @ 7:20 pm

References:

Vagrant Box from oravirt

GitHub from oravirt

Blog Post from oravirt

Windows binaries of GNU Wget

Copy wget to C:\Users\dinh\AppData\Local\Programs\Git\mingw64\bin (My install location)

You might be thinking, why download box?

  1. Box may get removed.
  2. Box is staged at preferred versus default location.
dinh@CMWPHV1 MINGW64 /f/Vagrant
$ git clone --recursive https://github.com/oravirt/vagrant-vbox-si-fs.git
Cloning into 'vagrant-vbox-si-fs'...
remote: Counting objects: 59, done.
remote: Compressing objects: 100% (3/3), done.
remote: Total 59 (delta 0), reused 0 (delta 0), pack-reused 56
Unpacking objects: 100% (59/59), done.
Submodule 'ansible-oracle' (http://github.com/oravirt/ansible-oracle) registered for path 'ansible-oracle'
Cloning into 'F:/Vagrant/vagrant-vbox-si-fs/ansible-oracle'...
warning: redirecting to https://github.com/oravirt/ansible-oracle/
remote: Counting objects: 2169, done.
remote: Compressing objects: 100% (110/110), done.
remote: Total 2169 (delta 56), reused 0 (delta 0), pack-reused 2052
Receiving objects: 100% (2169/2169), 542.42 KiB | 0 bytes/s, done.
Resolving deltas: 100% (1009/1009), done.
Submodule path 'ansible-oracle': checked out 'd311447608e28a6df7816aeb05aa38d610254931'


dinh@CMWPHV1 MINGW64 /f/Vagrant
$ cd vagrant-vbox-si-fs/


dinh@CMWPHV1 MINGW64 /f/Vagrant/vagrant-vbox-si-fs (master)
$ wget -q https://atlas.hashicorp.com/oravirt/boxes/ol73/versions/20170110/providers/virtualbox.box


dinh@CMWPHV1 MINGW64 /f/Vagrant/vagrant-vbox-si-fs (master)
$ ls
ansible.cfg  ansible-oracle/  hosts.yml  README.md  swrepo/  Vagrantfile  virtualbox.box


dinh@CMWPHV1 MINGW64 /f/Vagrant/vagrant-vbox-si-fs (master)
$ vagrant box add oravirt/ol73 file:///F:/Vagrant/vagrant-vbox-si-fs/virtualbox.box
==> box: Box file was not detected as metadata. Adding it directly...
==> box: Adding box 'oravirt/ol73' (v0) for provider:
    box: Unpacking necessary files from: file:///F:/Vagrant/vagrant-vbox-si-fs/virtualbox.box
    box:
==> box: Successfully added box 'oravirt/ol73' (v0) for 'virtualbox'!


dinh@CMWPHV1 MINGW64 /f/Vagrant/vagrant-vbox-si-fs (master)
$ vagrant up
Bringing machine 'db1-fs' up with 'virtualbox' provider...
==> db1-fs: Importing base box 'oravirt/ol73'...
==> db1-fs: Matching MAC address for NAT networking...


dinh@CMWPHV1 MINGW64 /f/Vagrant/vagrant-vbox-si-fs (master)
$ vagrant ssh

----------------------------------------
Welcome to db1-fs
OracleLinux 7.3 x86_64

FQDN: db1-fs
IP:   10.0.2.15

Processor: Intel(R) Core(TM) i7-2640M CPU @ 2.80GHz
#CPU's:    1
Memory:    2749 MB
Kernel:    4.1.12-61.1.18.el7uek.x86_64

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

[vagrant@db1-fs ~]$ ifconfig -a
enp0s3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.0.2.15  netmask 255.255.255.0  broadcast 10.0.2.255
        inet6 fe80::a00:27ff:fe4f:4c27  prefixlen 64  scopeid 0x20
        ether 08:00:27:4f:4c:27  txqueuelen 1000  (Ethernet)
        RX packets 1125  bytes 118982 (116.1 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 814  bytes 127567 (124.5 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

enp0s8: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.9.61  netmask 255.255.255.0  broadcast 192.168.9.255
        inet6 fe80::a00:27ff:fe84:e732  prefixlen 64  scopeid 0x20
        ether 08:00:27:84:e7:32  txqueuelen 1000  (Ethernet)
        RX packets 76  bytes 8068 (7.8 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 25  bytes 1826 (1.7 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 0  (Local Loopback)
        RX packets 96  bytes 8148 (7.9 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 96  bytes 8148 (7.9 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[vagrant@db1-fs ~]$

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?

« Previous PageNext Page »

Create a free website or blog at WordPress.com.