How to lookup parameter definitions in Golden Gate 12.2

A potentially very useful feature when working with Golden Gate configurations is the ability to lookup parameter definition usage, with the new command

GGSCI (myserver.mydomain.com as ggadmin@testdb01) 31> info param tranlogoptions.INTEGRATEDPARAMS

param name  : tranlogoptions.integratedparams
description : (Oracle) Valid for Extract in integrated capture mode (Oracle Standard or Enterprise Edition 11.2.0.3 or later) 
Passes parameters and values to the Oracle database logmining server when Extract is in integrated capture mode.
argument    : no argument
options     : ANNOTATEDDLSIZE, CAPTURE_IDKEY_OBJECTS
              CAPTURE_SEQUENCE_NEXTVAL, DISABLE_ON_LIMIT
              DOWNSTREAM_REAL_TIME_MINE, ENABLE_PROCEDURAL_REPLICATION
              IGNORE_TRANSACTION, IGNORE_UNSUPPORTED_TABLE, INCLUDE_OBJECTS
              INLINE_LOB_OPTIMIZATION, INLINE_SFLOB_OPTIMIZATION, MAXIMUM_SCN
              MAX_SGA_SIZE, MERGE_THRESHOLD, MESSAGE_LIMIT
              MESSAGE_TRACKING_FREQUENCY, PARALLELISM
              SKIP_AUTOFILTERED_TABLE_DDL, SPLIT_THRESHOLD, STARTUP_SECONDS
              TIME_LIMIT, TRACE_LEVEL, USE_RAC_SERVICE, WRITE_ALERT_LOG
              XOUT_CLIENT_EXISTS
component(s): EXTRACT
mode(s)     : Integrated Extract
platform(s) : AIX
              HPUX-IT
              HPUX-PA
              Linux
              Solaris SPARC
              Solaris x86
              Windows x64
versions    :
    max ver : 12.3.0.1.85
database(s) : all supported databases (on the supported platforms).
status      : current
mandatory   : false
dynamic     : false
relations   : none

Source: http://www.oracle-scn.com/oracle-goldengate-12-2-new-feature-info-param/

How to avoid OGG-10470 during and "add trandata" operation

When configuring a table for an Oracle GoldenGate replication, you need to enable supplemental logging for the table.

If you see the following message at the end of ADD TRANDATA command in ggsci:

GGSCI (myserver.mydomain.com as gg@testdb01) 8> ADD TRANDATA SCOTT.EMP

2018-10-25 11:59:35  INFO    OGG-15132  Logging of supplemental redo data enabled for table SCOTT.EMP.

2018-10-25 11:59:35  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table SCOTT.EMP.

2018-10-25 11:59:35  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table SCOTT.EMP.

2018-10-25 11:59:35  ERROR   OGG-10470  Error encountered during gathering support information on table SCOTT.EMP.
ERROR: OCI Error ORA (status = 942-ORA-00942: table or view does not exist)

You need to grant access to the dictionary for your golden gate databaser user. In my case:

GRANT SELECT ANY DICTIONARY TO GG; 

From now on, any attempt to add supplemental logging for tables will end without errors, like this:

2018-10-26 13:01:41  INFO    OGG-10471  ***** Oracle Goldengate support information on table SCOTT.EMP. *****
Oracle Goldengate support native capture on table SCOTT.EMP.
Oracle Goldengate marked following column as key columns on table SCOTT.EMP: EMP_ID.

How to add supplemental logging on a table

alter table scott.emp 
add supplemental log group gg_feed (feed_id) always;

To reverse:

alter table scott.emp
drop supplemental log group gg_feed;

What is the meaning of the "10G 11G 12C" value in DBA_USERS.PASSWORD_VERSIONS?

Since I wrote my post What is the meaning of the "10G 11G" value in DBA_USERS.PASSWORD_VERSIONS?, Oracle 12c added yet another value to this column.

It's now common to see the string

PASSWORD_VERSIONS
10G 11G 12C

when you query the password_versions column of dba_users view.

What does it mean?

It's a list of password versions that was generated at the time the account was created.
This list will look a little different depending on your setting of the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER in your $TNS_ADMIN/sqlnet.ora at the time of account creation.

Oracle explains:

"The PASSWORD_VERSIONS column shows the list of password versions that exist for the account. 10G refers to the earlier case-insensitive Oracle password DES-based version, 11G refers to the SHA-1 version, and 12C refers to the SHA-2-based SHA-512 version."


In my 12.2 database, I have set the following parameter in my $TNS_ADMIN/sqlnet.ora file:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

to allow older clients to connect.

When I then create a user with the CREATE USER statement, it will automatically generate all three password versions. Since my SQLNET.ALLOWED_LOGON_VERSION_SERVER was set to 11 at the time of creation, my password will indeed by case-sensitive, since case sensitive password was introduced in version 11.1 of the Oracle software.

If I adjust the parameter sqlnet.ora parameter:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=12

and drop/recreate the user, my password version will have changed:

PASSWORD_VERSIONS
11G 12C

The setting of SQLNET.ALLOWED_LOGON_VERSION have the following effects:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 will keep generating 10G, 11G and 12c password versions
SQLNET.ALLOWED_LOGON_VERSION_SERVER=12 will generate both 11G and 12C password versions, and also remove the 10G password version.
SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a will generate only 12c password versions

Oracle call these three settings Greatest level of compatibility, Medium level of security, and Highest level of security, respectivly.

If you for some reason want the old-school case-insensitive password versions to apply, set your SQLNET.ALLOWED_LOGON_VERSION_SERVER to 10 or lower, and make sure the parameter sec_case_sensitive_logon is set to FALSE.

Any user created after setting SQLNET.ALLOWED_LOGON_VERSION_SERVER to 10 or lower, will be able to logon using case-insensitive passwords.

How to list all schemas and their sizes

If you want a list of schema sizes, join dba_segments with dba_users to limit the extract to users that are non-oracle maintained:

set lines 200
col owner  format a30
col "GB" format 999999
SELECT s.owner,sum(s.bytes/1024/1024/1024) "GB"
FROM dba_segments s join dba_users u
on (s.owner = u.username)
where u.oracle_maintained = 'N'
group by owner
ORDER BY 2 desc;

Example output

OWNER                               GB
------------------------------ -------
USER1                            19577
USER2                             6144
USER3                             2306

How to work around ORA-38338: incorrect ILM policy scope for row-level ADO policies

When adding an ADO policy on a table, like this:

CREATE TABLE TEST_TABLE1
(
  col1 NUMBER(38),
  col2 NUMBER(38),
  col3 DATE
)
NOCOMPRESS 
TABLESPACE USERS
ILM ADD POLICY ROW STORE COMPRESS ADVANCED 
ROW AFTER 1 DAY OF NO ACCESS;

oracle returned

ORA-38338: incorrect ILM policy scope

This policy is not valid for row-level ADO policy. The only valid option is

ILM ADD POLICY ROW STORE COMPRESS ADVANCED 
ROW AFTER X DAY OF NO MODIFICATION;

How to deinstall an old oracle installation using oui

You can use the following command to remove an obsolete oracle software installation from the central inventory:

$ORACLE_HOME/oui/bin/runInstaller -silent -deinstall REMOVE_HOMES={"/u01/oracle/product/11204"}

Remember to clean out the physical files with an appropriate operating system command.

The above method is an alternative to the method outlined in this post.