Notes on datapatch

Mike Dietrich is the God father of all Oracle upgrades.

Here are just a few reference and should read

DBUA 12c and “datapatch.pl” – things to know

DBUA misses the post-upgrade datapatch execution in Oracle 12.1.0.2. The solution is to apply the SQL changes manually after DBUA has completed the database upgrade to Oracle Database 12c:

cd $ORACLE_HOME/OPatch
./datapatch -verbose

Does DBCA execute “datapatch” in Oracle 12.2?

My notes: when it doubt check it out.

$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 on Thu Jan 19 10:35:27 2017
Copyright (c) 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_93223_2017_01_19_10_35_27/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 24315824 (Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)):
  Installed in the binary registry only
Bundle series DBBP:
  ID 161018 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    24315824 (Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016))
    24340679 (DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679))

Installing patches...
Patch installation complete.  Total patches installed: 2

Validating logfiles...
Patch 24315824 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24315824/20676808/24315824_apply_DBFS_2017Jan19_10_35_43.log (no errors)
Patch 24340679 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_DBFS_2017Jan19_10_35_47.log (no errors)
SQL Patching tool complete on Thu Jan 19 10:37:16 2017

SQL> set serveroutput on
SQL> exec dbms_qopatch.get_sqlpatch_status;

-- Logfile is provided. How convenient is that?
Patch Id : 24315824
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:14
        Description : Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24315824/20676808/24315824_apply_DBFS_2017Jan19_10_35_43.log
        Status : SUCCESS

Patch Id : 24340679
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:16
        Description : DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_DBFS_2017Jan19_10_35_47.log
        Status : SUCCESS

PL/SQL procedure successfully completed.

SQL>


$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose


SQL> set serveroutput on
SQL> exec dbms_qopatch.get_sqlpatch_status;


Patch Id : 24315824
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:14
        Description : Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24315824/20676808/24315824_apply_DBFS_2017Jan19_10_35_43.log
        Status : SUCCESS

Patch Id : 24340679
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:16
        Description : DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_DBFS_2017Jan19_10_35_47.log
        Status : SUCCESS

PL/SQL procedure successfully completed.

SQL>

Advertisement

12c DataGuard Validate and More

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


GoldenGate 12.2 TROUBLESHOOTING REPLICAT LAG

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> 

12c How to UN Expire Password for CDB Users

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

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!

GoldenGate Extract RBA Not Moving LAG Increasing Appears Hung

OGG Extract RBA Not Moving And LAG Increasing And Appears Hung (Doc ID 964705.1)

Typically, when Goldengate is performing recovery:

In recovery[1] – Extract is recovering to its checkpoint in the transaction log.
In recovery[2] – Extract is recovering from its checkpoint to the end of the trail.
Recovery complete – The recovery is finished, and normal processing will resume.

Example:
Current status: In recovery[1]: Reading from data source

SEND EXTRACT

First time I have seen Processing data with empty data queue and is essentially the same process.

send e* status  
Current status: In recovery[1]: Processing data with empty data queue

send e* status
Current status: Recovery complete: Processing data with empty data queue    

The scary part is current redo vs Goldengate Checkpoint.

To prevent this, make sure there is no lag, no long running transactions before stopping extract.

select thread#,sequence# from v$log where status='CURRENT' order by 1;

THREAD#    SEQUENCE#
---------- ----------
        1     1198566
        2     1291021
        
info e*
Log Read Checkpoint  Oracle Redo Logs
                     2017-06-07 14:44:53  Thread 1, Seqno 1198492, RBA 112396
Log Read Checkpoint  Oracle Redo Logs
                     2017-06-07 14:45:33  Thread 2, Seqno 1290939, RBA 1060244424

What if integrated extract is used? Where’s my Seqno?

info e*
Log Read Checkpoint Oracle Integrated Redo Logs
2017-02-12 18:36:06
SCN 0.4928929 (4928929)

Read more about at Log sequence# and rba# of integrated extract checkpoint