Thinking Out Loud

June 16, 2017

12c DataGuard Validate and More

Filed under: oracle,Dataguard,12c — mdinh @ 11:11 pm
12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt) (Doc ID 1582837.1)	

Configuration
db_name=hawk
db_unique_name=hawka (primary)
db_unique_name=hawkb (standby)

Configuration - hawkdg
  Protection Mode: MaxPerformance
  Members:
    hawka - Primary database
      hawkb - Physical standby database 

Validate DataGuard Configurations.

Monitorable (Read-Only) Properties

show configuration verbose

show database verbose hawka
show database verbose hawkb

validate database verbose hawka
validate database verbose hawkb

There is no need to use on database if the instance names are unique across primary and standby environments.

You might ask, why are instance name not the  same on primary and standby?

I don’t know.

show instance verbose hawka1 on database hawka
show instance verbose hawka2 on database hawka

show instance verbose hawkb1 on database hawkb
show instance verbose hawkb2 on database hawkb

The InconsistentProperties monitorable property returns a table that shows all properties whose values contained in the broker configuration file are inconsistent with the values in the corresponding server parameter file or the runtime values.

show database hawka InconsistentProperties
show database hawkb InconsistentProperties

The InconsistentLogXptProps monitorable property returns a table that shows all properties related to redo transport services whose values are inconsistent between the broker configuration file and the runtime value.

show database hawka InconsistentLogXptProps
show database hawkb InconsistentLogXptProps

The LogXptStatus monitorable property returns a table that contains the error status of redo transport services for each of the enabled configuration members. This property pertains to the primary database, a physical standby database that ships redo data, or a far sync instance.

show database hawka LogXptStatus

The SendQEntries monitorable property returns a table that shows all log files on the primary database that were not successfully archived to one or more standby databases. This property pertains to the primary database

show database hawka SendQEntries

The RecvQEntries monitorable property returns a table indicating all log files that were received by the standby database but have not yet been applied. If no rows are returned, it implies all log files received have been applied. This property pertains to a standby database.

show database hawkb RecvQEntries

The TopWaitEvents monitorable property specifies the 5 events with the longest waiting time in the specified instance.

show instance hawkb1 TopWaitEvents

How to edit database properties for all instances.

edit instance * on database hawka set property logarchivetrace=0;

Lastly, all commands and be run from shell script with example below.

echo "***** Checking Data Guard Broker Configuration ...."
dgmgrl -echo << END
connect /
show configuration verbose
show configuration TraceLevel
show database hawklas
show database hawksan
show instance hawklas DGConnectIdentifier
show instance hawksan DGConnectIdentifier
show instance hawklas StaticConnectIdentifier
show instance hawksan StaticConnectIdentifier
show instance hawklas InconsistentProperties
show instance hawksan InconsistentProperties
show instance hawklas LogArchiveMaxProcesses
show instance hawksan LogArchiveMaxProcesses
show instance hawklas DelayMins
show instance hawksan DelayMins
show instance hawklas LogArchiveTrace
show instance hawksan LogArchiveTrace
show instance hawklas statusreport
show instance hawksan statusreport
exit
END
exit

June 11, 2017

GoldenGate 12.2 TROUBLESHOOTING REPLICAT LAG

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

Time Since Chkpt and Lag at Chkpt from replicat keep increasing

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
REPLICAT    RUNNING     R_NEW12C    03:49:45      06:37:47    

This occurs for due to the following reasons:

Delivering a long running transaction
Waiting on a full table scan
Blocked by another sessions
Primary extract lag or pump lag keeps increasing

Is There a Way to Make Long-running Transactions Checkpoint? (Doc ID 969684.1)

The tradeoff with GROUPTRANSOPS is with efficiency. 
The tradeoff with MAXTRANSOPS is efficiency and transaction integrity. 

There are no long running transaction from the extract and is monitored using WARNLONGTRANS 15m, CHECKINTERVAL 3m.

grep "Long Running Transaction" dirrpt/E_OLD10G.rpt

My suspicion is FTS, but how to find out?

SQL> r
  1  select
  2  -- SQL_ID,PLAN_HASH_VALUE,
  3  OBJECT_OWNER,OBJECT_NAME, min(TIMESTAMP) min_ts, max(TIMESTAMP) max_ts, count(*) ct
  4  from DBA_HIST_SQL_PLAN
  5  where operation='TABLE ACCESS'
  6  and options='FULL'
  7  and NOT REGEXP_LIKE(object_owner,'SYS|SYSTEM|DBSNMP')
  8  and TIMESTAMP > TO_DATE('01-JUN-2017','DD-MON-YYYY')
  9  group by
 10  -- SQL_ID,PLAN_HASH_VALUE,
 11  OBJECT_OWNER,OBJECT_NAME
 12  order by count(*), OBJECT_OWNER,OBJECT_NAME
 13*
 
OBJECT_OWNER         OBJECT_NAME                    MIN_TS               MAX_TS                       CT
-------------------- ------------------------------ -------------------- -------------------- ----------
XXX                  THISISAREALLYLONGTABLENAME     01-JUN-2017 00:01:00 11-JUN-2017 02:55:36        114

SQL> select index_name from dba_indexes where table_name='THISISAREALLYLONGTABLENAME';
no rows selected
SQL> 

June 10, 2017

12c How to UN Expire Password for CDB Users

Filed under: 12c,CDB — mdinh @ 8:11 pm

Use dbms_metadata.get_ddl to extract user and replace create with alter.

oracle@arrow ~ $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 10 15:31:13 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select username, expiry_date, account_status from dba_users where username like '%MONITOR%';

USERNAME
--------------------------------------------------------------------------------
EXPIRY_DA ACCOUNT_STATUS
--------- --------------------------------
C##MONITOR
10-JUN-17 EXPIRED

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> set long 1000000 longchunksize 32000 linesize 32000 pages 0 newpage none
SQL> set heading off tab off echo off define off sqlprefix off blockterminator off timing off verify off feedb off
SQL> set sqlblanklines on embedded on trimspool on  
SQL> select dbms_metadata.get_ddl('USER','C##MONITOR') from dual;

   CREATE USER "C##MONITOR" IDENTIFIED BY VALUES 'S:***'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE

SQL> ALTER USER "C##MONITOR" IDENTIFIED BY VALUES 'S:***'
SQL> select username, expiry_date, account_status from dba_users where username like '%MONITOR%';
C##MONITOR                                                                                       07-DEC-17 OPEN
SQL> 

GoldenGate Debugging

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

I was working on automating debug information to submit to Oracle Support and thought I share implementation for what was requested.

OGG_GROUP_NAME is from ggsci info all (case sensitive)
./debug_gg.sh: line 3: 1: —> USAGE: /debug_gg.sh OGG_GROUP_NAME
./debug_gg.sh E_LAX

#!/bin/sh -e
DN=`dirname $0`
BN=`basename $0`
OGG_GROUP_NAME=${1:?"---> USAGE: $DN/$BN "OGG_GROUP_NAME""}
set -x
ps -o pid,uname,cmd `pgrep -f "extract.*${OGG_GROUP_NAME}"`
pstack `pgrep -f "extract.*${OGG_GROUP_NAME}"`
pstack `pgrep -f "extract.*${OGG_GROUP_NAME}"`
pstack `pgrep -f "extract.*${OGG_GROUP_NAME}"`
set +x
$GG_HOME/ggsci << EOF
info ${OGG_GROUP_NAME} detail
send ${OGG_GROUP_NAME} status
sh sleep 2m
send ${OGG_GROUP_NAME} status
sh sleep 2m
send ${OGG_GROUP_NAME} status
send ${OGG_GROUP_NAME} report
exit
EOF
echo " "
ls -alrt $GG_HOME/dirrpt/${OGG_GROUP_NAME}*.rpt|tail -3
exit

Example output from pgrep and pstack

++ pgrep -f 'extract.*E_LAX'
+ ps -o pid,uname,cmd 40882
  PID USER     CMD
40882 ggsuser  /u01/gg/12.2.0/extract PARAMFILE /u01/gg/12.2.0/dirprm/e_lax.prm REPORTFILE /u01/gg/12.2.0/dirrpt/E_LAX.rpt PROCESSID E_LAX USESUBDIRS
++ pgrep -f 'extract.*E_LAX'
+ pstack 40882

Just realized does not scale since extract is hard coded.

Next step, learn how to read pstack output – YIKES!

May 19, 2017

NOLOGGING Bad For Recovery

Filed under: 12c,oracle — mdinh @ 3:41 am

What doesn’t kill you makes you stronger.
I have gone through NOLOGGING hell and not sure if I am out yet.
Disk was lost from server and database was recovered.

Oracle Standard Edition (SE2) for Microsoft Windows x64 (64-bit).

RMAN> restore database validate preview;

  List of Archived Logs in backup set 4098
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    15209   134553557  26-APR-17 134574622  26-APR-17
  1    15218   134681063  26-APR-17 134690751  27-APR-17
RMAN-05119: recovery can not be done to a consistent state.
Media recovery start SCN is 133224369
Recovery must be done beyond SCN 133235710 to clear datafile fuzziness

RMAN> spool log to recover.log
RMAN> recover database until sequence 15209;
RMAN> alter database open resetlogs;
RMAN> exit 

Bug 20315311 – RMAN-5119: recovery can not be done to a consistent state (Doc ID 20315311.8)
The content was last updated on: 17-FEB-2017

RMAN gives the following notification in the 'restore database preview' output even though a successful backup has been performed:
  RMAN-05119: recovery can not be done to a consistent state
 
Workaround
 None

SQL> select * from mytable;

select * from mytable
                      *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 915)
ORA-26040: Data block was loaded using the NOLOGGING option

RMAN> validate tablespace USERS;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     334            263346       848640          134981624

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     295            1225517      3932160         134981561

Finished validate at 18-MAY-17

RMAN> list failure;

Database Role: PRIMARY

no failures found that match specification

RMAN> advise failure;

Database Role: PRIMARY

no failures found that match specification

RMAN> exit

RMAN> backup validate check logical database;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     334            263346       848640          134982083
  File Name: 

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     295            1225517      3932160         134982061
  File Name: 

Finished validate at 18-MAY-17  

Basically, NOLOGGING operation occurred from backup before resetlogs recovery
and there is no datafile/database backup following NOLOGGING operations
since database backup is performed once a day.

The Gains and Pains of Nologging Operations (Doc ID 290161.1)

However, NOLOGGING is intended for configurations in which media recovery 
or the recovery of the corresponding object is not important. 
Thus, if the disk or tape or storage media fails, 
you will not be able to recover your changes from the redo because the changes were never logged.  
SQL> select max(NONLOGGED_END_CHANGE#), max(NONLOGGED_END_TIME), max(RESETLOGS_CHANGE#), max(RESETLOGS_TIME) from v$nonlogged_block;
MAX(NONLOGGED_END_CHANGE#) MAX(NONLOGGED_END_TI MAX(RESETLOGS_CHANGE#) MAX(RESETLOGS_TIME)
-------------------------- -------------------- ---------------------- --------------------
                 134534115

SQL> select count(*) from v$nonlogged_block;
  COUNT(*)
----------
        64

-- NOTICE: this matched what RMAN reported.
SQL> select file#, count(*), sum(blocks) from v$nonlogged_block group by file#;
     FILE#   COUNT(*) SUM(BLOCKS)
---------- ---------- -----------
         5         54         334
         8         10         295

SQL> select resetlogs_change#, resetlogs_time, current_scn from v$database;
RESETLOGS_CHANGE# RESETLOGS_TIME       CURRENT_SCN
----------------- -------------------- -----------
        134553558 15-MAY-2017 18:28:53   135266743

SQL> select file#, block#, NONLOGGED_START_CHANGE# from v$nonlogged_block
  2  where NONLOGGED_START_CHANGE# > (select RESETLOGS_CHANGE# from v$database)
  3  ;

no rows selected

SQL>

Truncating table and reloading data resolve SELECT issues but block corruption (Free Block) still exists.
You can read more about Playing with Oracle Free Block Corruption

SQL> truncate table mytable;

Table truncated.

SQL> select * from mytable;

no rows selected

SQL> r
  1  SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2  , greatest(e.block_id, c.block#) corr_start_block#
  3  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5  - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6  , null description
  7  FROM dba_extents e, v$nonlogged_block c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1 10 AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 13  , greatest(f.block_id, c.block#) corr_start_block#
 14  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 15  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 16  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 17  , 'Free Block' description
 18  FROM dba_free_space f, v$nonlogged_block c
 19  WHERE f.file_id = c.file#
 20  AND f.block_id <= c.block# + c.blocks - 1 21 AND f.block_id + f.blocks - 1 >= c.block#
 22  order by file#, corr_start_block#
 23*

                                                                                      START     END
OWNER      SEGMENT_TYPE         SEGMENT_NAME         PARTITION_NAME        FILE#     BLOCK#  BLOCK# BLOCKS_CORRUPTED DESCRIPTION
---------- -------------------- -------------------- -------------------- ------ ---------- ------- ---------------- ------------------------------
SCHEMA    TABLE                ########                                       5        219     219                1
SCHEMA    TABLE                ########                                       5        227     227                1
SCHEMA    TABLE                ########                                       5        235     235                1
SCHEMA    TABLE                ##################                             5        251     251                1
SCHEMA    TABLE                ##########                                     5        275     275                1
SCHEMA    TABLE                ############                                   5        283     283                1
SCHEMA    TABLE                #############                                  5        459     459                1
SCHEMA    TABLE                ##########                                     5        467     467                1
SCHEMA    TABLE                ################                               5        491     491                1
SCHEMA    TABLE                #############                                  5        499     499                1
SCHEMA    TABLE                ##################                             5        851     851                1
SCHEMA    TABLE                ########                                       5        875     875                1
SCHEMA    TABLE                #############                                  5        883     883                1
SCHEMA    TABLE                ################                               5        891     891                1
SCHEMA    TABLE                ##################                             5        899     903                5
SCHEMA    TABLE                ################                               5        907     907                1
SCHEMA    TABLE                ############                                   5        915     919                5
SCHEMA    TABLE                ############                                   5       1091    1091                1
SCHEMA    TABLE                ##########                                     5       1107    1107                1
SCHEMA    TABLE                ##############                                 5       1323    1327                5
SCHEMA    TABLE                ############                                   5       1331    1331                1
SCHEMA    TABLE                ##############                                 5       1347    1351                5
SCHEMA    TABLE                ############                                   5       1355    1355                1
SCHEMA    TABLE                ###########                                    5       1643    1643                1
SCHEMA    TABLE                ##########                                     5       1651    1651                1
SCHEMA    TABLE                ###########                                    5       2099    2103                5
SCHEMA    TABLE                ###############                                5       2531    2531                1
SCHEMA    TABLE                ##############                                 5       2539    2543                5
SCHEMA    TABLE                ##############                                 5       2547    2547                1
SCHEMA    TABLE                ###############                                5       2707    2707                1
SCHEMA    TABLE                ###########                                    5       2715    2715                1
SCHEMA    TABLE                ###############                                5       2875    2875                1
SCHEMA    TABLE                ##############                                 5       4211    4215                5
                                                                               5       4216    4218                3 Free Block
SCHEMA    TABLE                ###########                                    5       4611    4611                1
SCHEMA    TABLE                ########                                       5       4627    4627                1
                                                                               5       4872    4877                6 Free Block
                                                                               5       4880    4886                7 Free Block
                                                                               5       5064    5071                8 Free Block
                                                                               5       5073    5087               15 Free Block
                                                                               5       5089    5103               15 Free Block
                                                                               5       5105    5119               15 Free Block
                                                                               5       5121    5135               15 Free Block
                                                                               5       5137    5159               23 Free Block
                                                                               5       5161    5175               15 Free Block
                                                                               5       5177    5191               15 Free Block
                                                                               5       5193    5207               15 Free Block
                                                                               5       5209    5223               15 Free Block
                                                                               5       5225    5239               15 Free Block
                                                                               5       5241    5255               15 Free Block
                                                                               5       5257    5263                7 Free Block
                                                                               5       5328    5335                8 Free Block
                                                                               5       5337    5343                7 Free Block
                                                                               5     229506  229550               45 Free Block
                                                                               5     608697  608703                7 Free Block
                                                                               8    1120824 #######                5 Free Block
                                                                               8    1120872 #######                8 Free Block
                                                                               8    1274888 #######                8 Free Block
                                                                               8    1536681 #######               14 Free Block
                                                                               8    1536697 #######               11 Free Block
                                                                               8    1536713 #######                6 Free Block
                                                                               8    1791056 #######                8 Free Block
                                                                               8    2804571 #######               37 Free Block
                                                                               8    3213819 #######                5 Free Block
                                                                               8    3854212 #######              193 Free Block

65 rows selected.

Elapsed: 00:09:22.13
SQL>

How to identify all the Corrupted Objects in the Database with RMAN (Doc ID 472231.1)
In 12c the NOLOGGING blocks identified by rman validate are in new view v$nonlogged_block

set lines 200 pages 1000 tab off trimsp on timing on
col owner for a10
col segment_type for a20
col segment_name for a20
col partition_name for a20
col partition_name for a20
col description for a30
col file# for 99999
col corr_start_block# heading 'CORRUPT|START|BLOCK#' for 999999999
col corr_end_block# heading 'CORRUPT|END|BLOCK#' for 999999999
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$nonlogged_block c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$nonlogged_block c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#
;

May 15, 2017

DBFS and XAG for Goldengate P5

Filed under: 12c,GoldenGate — mdinh @ 11:49 pm
$ $GRID_HOME/crs/script/mount-dbfs.sh version
20160215

$ grep ^VERSION $GRID_HOME/crs/script/mount-dbfs.sh 
VERSION=20160215

The following is an option for customers that have removed the dos2unix tool:
1. vi /tmp/mount-dbfs.sh
2. :set ff=unix
3. :wq
4. repeat for /tmp/mount-dbfs.conf

May 14, 2017

DBFS and XAG for Goldengate P4

Filed under: 12c,GoldenGate — mdinh @ 3:13 pm

I encountered a situation where I was not able to start dbfs_mount to save my life.

2 people spent over 5 hours and was not able to resolve the issue.

1 person suggested to to restart clusterware because it looks like fuse group was added to oracle after oracle clusterware was up.

During configuration:
kernel-devel was missing, /etc/rc.modules did not exists, oracle was not part of fuse group

# rpm -qa |egrep 'fuse|kernel-devel'
# cat /etc/rc.modules
cat: /etc/rc.modules: No such file or directory
# grep fuse /etc/group
fuse:x:993:

Corrections made:

# cat /etc/rc.modules
/sbin/modprobe fuse

# grep fuse /etc/group
fuse:x:993:oracle
$ crsctl start res dbfs_mount
CRS-2672: Attempting to start 'dbfs_mount' on 'hawk1'
CRS-2672: Attempting to start 'dbfs_mount' on 'hawk2'
CRS-2674: Start of 'dbfs_mount' on 'hawk2' failed
CRS-2679: Attempting to clean 'dbfs_mount' on 'hawk2'
CRS-2674: Start of 'dbfs_mount' on 'hawk1' failed
CRS-2679: Attempting to clean 'dbfs_mount' on 'hawk1'
CRS-2681: Clean of 'dbfs_mount' on 'hawk1' succeeded
CRS-2681: Clean of 'dbfs_mount' on 'hawk2' succeeded
CRS-4000: Command Start failed, or completed with errors.

$ crsctl stat res dbfs_mount
NAME=dbfs_mount
TYPE=local_resource
TARGET=ONLINE , ONLINE
STATE=OFFLINE, OFFLINE

After restarting clusterware:

Note: this was started by root which I don't think is a good idea.
# crsctl start resource dbfs_mount
CRS-2672: Attempting to start 'dbfs_mount' on 'hawk1'
CRS-2676: Start of 'dbfs_mount' on 'hawk1' succeeded

Should have been started from grid user
$ crsctl start res dbfs_mount
CRS-2672: Attempting to start 'dbfs_mount' on 'hawk2'
CRS-2672: Attempting to start 'dbfs_mount' on 'hawk1'
CRS-2676: Start of 'dbfs_mount' on 'hawk1' succeeded
CRS-2676: Start of 'dbfs_mount' on 'hawk2' succeeded

$ crsctl stat res dbfs_mount
NAME=dbfs_mount
TYPE=local_resource
TARGET=ONLINE             , ONLINE
STATE=ONLINE on hawk1, ONLINE on hawk2

It really bothered me how this information was missed and where was it documented.

After hours of research, the information is not consistently documented.
Not Found:
White Paper: How To Setup 12c DBFS FileSystem. (Doc ID 1938421.1)

Found:
Configuring DBFS on Oracle Exadata Database Machine (Doc ID 1054431.1)

To pick up the additional group (fuse) membership for the oracle user on Linux or the workaround above on Solaris, Clusterware must be restarted.
For example, to restart Clusterware on all nodes at the same time (non-rolling), you can use the following commands as root:

Hopefully, you won’t have a much fun as I did.

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

Next Page »

Blog at WordPress.com.