All posts by mrculp

ExaData HealthCheck function

#------------------------------------------------------------------
# HealthCheck for Exadata environment
# Check if RMAN Running & Generate recent RMAN logfiles
# Copy alert logs to common location for review
# Get load average for all hosts
# Get filesystem space in /u01 directory for all hosts
# Get session info for card & deposits, and check if High Number of Active Sessions
# Check FRA space
#-------------------------------------------------------------------

set_parms()
# Set parameters
{ mkdir -p /ora01/tmp/exa_check/logs/rman/proj01
  mkdir -p /ora01/tmp/exa_check/logs/rman/proj02
  mkdir -p /ora01/tmp/exa_check/logs/rman/proj03
  mkdir -p /ora01/tmp/exa_check/logs/alert_logs
  mkdir -p /ora01/tmp/exa_check/logs/fs_space
  export fsdir=/ora01/tmp/exa_check/logs/fs_space
  export aldir=/ora01/tmp/exa_check/logs/alert_logs
  export ldir=/ora01/tmp/exa_check/logs
  export sldir=/ora01/tmp/exa_check/logs/ses_info
  export rldir=/ora01/tmp/exa_check/logs/rman
  export tff=`date +%m%d%y_%H:%M:%S` }


Oracle GoldenGate Conflict Detection and Resolution (CDR)

Active-Active replication allows databases actively receive transactions when their data are synchronized by the data replication. The implementation enables you to provide [3]:

  • Active-Active High Availability 
  • Distributed Data Management
  • Optimized Workload and Balanced Use of the Compute Resources 

Implementing an effective active-active replication configuration is not easy. It requires thorough considerations. One of the considerations is defining the Conflict Detection and Resolution (CDR) rules. The CDR rule decides what to do when active transactions introduce data conflicts. In this blog, let me use some examples to explain the CDR concept, and show you how to configure CDR using Oracle GoldenGate. 

Information: Oracle GoldenGate starts to provides built-in conflict detection and resolution routines in 11.2 to support bi-directional and multi-master configurations. (Source: Oracle GoldenGate 11.2 Release Notes)

1. What is Conflict Resolution and Detection (CDR)? 
Let’s use the following example from the Oracle GoldenGate Administration Guide [1] [2] to explain what CDR is:  

MAP fin.src, TARGET fin.tgt,
​ COMPARECOLS (ON UPDATE ALLEXCLUDING (comment)),
 RESOLVECONFLICT (    UPDATEROWEXISTS, (delta_res_method, USEDELTA, COLS (salary, balance)), (max_res_method, USEMAX (last_mod_time), COLS (address, last_mod_time)),    (DEFAULT, IGNORE));

We can divide the CDR configuration into two parts: the conflict detection and the conflict resolution.

1.1 Conflict Detection
Oracle GoldenGate uses key columns to identity the records and then detects conflicts by comparing the data before and after the transaction updates. You need the following configurations for the conflict detection: 

  • Replicating the full before-image of each record: you need to use ADD TRANDATA to include the information in the redo log and use LOGALLSUPCOLS in the extract to include the data in the GoldenGate trail files. 
  • Use COMPARECOLS to specify the SQL operation (UPDATE | DELETE) and key columns (ALL | KEY | KEYINCLUDING | ALLEXCLUDING) to detect conflicts. 

​In the example, Oracle GoldenGate checks the conflicts of all the UPDATE operations on all but the comment column on the fin.src table.  Why do I get the ” WARNING OGG-02180  Table xxx.xxx.xxx will use legacy trail format to support parameter UPDATEBEFORES.” message? 
In Oracle GoldenGate 12.3, you would use LOGALLSUPCOLS instead of GETBEFOREUPDATES. You might get the following error message for all of your tables: “WARNING OGG-02180  Table xxx.xxx.xxx will use legacy trail format to support parameter UPDATEBEFORES. ”

1.2. Conflict Resolution
Oracle GoldenGate provides REOLVECONFLICT to defines the resolutions for each conflict.  The following is the list of conflicts: 

  • INSERTROWEXISTS: Insert to an existing record.
  • UPDATEROWMISSING: Update a non-existing record. 
  • UPDATEROWEXISTS: Update a record with different value (the before image is different).
  • DELETEROWMISSING: Delete a non-existing record.
  • DELETEROWEXISTS: Delete a record with different value (the before image is different).

The following is the list of resolution functions: 

  • USEMAX and USEMAXEQ
  • USEMIN and USEMINEQ
  • ​USEDELTA 
  • ​DISCARD 
  • ​OVERWRITE
  • IGNORE

You can refer to the Oracle GoldenGate reference guide for the detailed definitions.

Oracle GoldenGate Conflict Resolution Parameters  

In the example, when updating a record with different value on the target database: 

  • the salary and balance columns will be updated with the delta between the before and after image in the trail. 
  • the address and the last_mod_time columns will be updated with the max value of last_mod_time. 
  • the updates will be ignored for all other columns. 

Best Practices: Avoid Conflict First and Make Resolution Rule as Simple as Possible 
Conflict detection and Resolution operates on each rows of your data. Therefore, it’s a significant overhead especially when having millions of row to process. Consequently, you would avoid conflict in the first place by controlling transaction operations in your applications such as using application segregation or using different primary keys in different DBs. Then, you would use the simplest resolution rule such as using a transaction timestamp or relying on trusted sources to overwrite inconsistent data. [3]

​2. CDR Design Strategy 
n summary, there are two typical strategies when implementing CDR: 

  • Master-Slave with overwrites: there is a master-slave relationship between the databases. You always use the value on the master to overwrite the value on the slave. 
  • Master-Master with rules: databases are treated equally.  You then use the built-in or CDR functions in Oracle GoldenGate to resolve the conflicts. You might need some additional column(s) such as the data committed timestamp to resolve the conflicts.

3. Example using Oracle GoldenGate Hub for Active-Active Replication
Let’s explain how CDR works with the following example.

Oracle GoldenGate provides detailed reports of all the CDR operations. You can find them from the GGCSI command, report file or column-conversion functions. 

Edition-Based Redefinition 18c

Edition-based redefinition (EBR) enables online application upgrade with uninterrupted availability of the application. When the installation of an upgrade is complete, the pre-upgrade application and the post-upgrade application can be used at the same time. Therefore, an existing session can continue to use the pre-upgrade application until its user decides to end it; and all new sessions can use the post-upgrade application. When there are no longer any sessions using the pre-upgrade application, it can be retired. In this way, EBR allows hot rollover from from the pre-upgrade version to the post-upgrade version, with zero downtime.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/editions.html#GUID-C213A6AA-5427-4426-80B5-D5301386253E

https://www.oracle.com/database/technologies/high-availability/ebr.html

assign_dba.ksh


#!/bin/ksh
# This script assigns the user (CFGADM) that will be used in oracle to access tables in APEX
# M. Culp
# 11/06/2010
# 01/04/2010 Made changes to reflect the LS commands
# 02/03/2010
# This should give access to the user in APEX that this is going against
#  
#######################################################################################################

sqlplus -s "/ as sysdba" <<EOF

grant exempt access policy to cfgadm;
grant sysdba to cfgadm;
grant select_catalog_role to cfgadm;
grant create session to cfgadm;
grant select on DBA_HIST_DATABASE_INSTANCE to cfgadm;
grant select on DBA_HIST_SNAPSHOT to cfgadm;
grant select on DBA_HIST_SNAP_ERROR to cfgadm;
grant select on DBA_HIST_BASELINE to cfgadm;
grant select on DBA_HIST_WR_CONTROL to cfgadm;
grant select on DBA_HIST_DATAFILE to cfgadm;
grant select on DBA_HIST_FILESTATXS to cfgadm;
grant select on DBA_HIST_TEMPFILE to cfgadm;
grant select on DBA_HIST_TEMPSTATXS to cfgadm;
grant select on DBA_HIST_SQLSTAT to cfgadm;
grant select on DBA_HIST_SQLTEXT to cfgadm;
grant select on DBA_HIST_SQL_SUMMARY to cfgadm;
grant select on DBA_HIST_SQL_PLAN to cfgadm;
grant select on DBA_HIST_SQLBIND to cfgadm;
grant select on DBA_HIST_OPTIMIZER_ENV to cfgadm;
grant select on DBA_HIST_EVENT_NAME to cfgadm;
grant select on DBA_HIST_SYSTEM_EVENT to cfgadm;
grant select on DBA_HIST_BG_EVENT_SUMMARY to cfgadm;
grant select on DBA_HIST_WAITSTAT to cfgadm;
grant select on DBA_HIST_ENQUEUE_STAT to cfgadm;
grant select on DBA_HIST_LATCH_NAME to cfgadm;
grant select on DBA_HIST_LATCH to cfgadm;
grant select on DBA_HIST_LATCH_CHILDREN to cfgadm;
grant select on DBA_HIST_LATCH_PARENT to cfgadm;
grant select on DBA_HIST_LATCH_MISSES_SUMMARY to cfgadm;
grant select on DBA_HIST_LIBRARYCACHE to cfgadm;
grant select on DBA_HIST_DB_CACHE_ADVICE to cfgadm;
grant select on DBA_HIST_BUFFER_POOL_STAT to cfgadm;
grant select on DBA_HIST_ROWCACHE_SUMMARY to cfgadm;
grant select on DBA_HIST_SGA to cfgadm;
grant select on DBA_HIST_SGASTAT to cfgadm;
grant select on DBA_HIST_PGASTAT to cfgadm;
grant select on DBA_HIST_RESOURCE_LIMIT to cfgadm;
grant select on DBA_HIST_SHARED_POOL_ADVICE to cfgadm;
grant select on DBA_HIST_SQL_WORKAREA_HSTGRM to cfgadm;
grant select on DBA_HIST_PGA_TARGET_ADVICE to cfgadm;
grant select on DBA_HIST_INSTANCE_RECOVERY to cfgadm;
grant select on DBA_HIST_JAVA_POOL_ADVICE to cfgadm;
grant select on DBA_HIST_THREAD to cfgadm;
grant select on DBA_HIST_STAT_NAME to cfgadm;
grant select on DBA_HIST_SYSSTAT to cfgadm;
grant select on DBA_HIST_SYS_TIME_MODEL to cfgadm;
grant select on DBA_HIST_OSSTAT_NAME to cfgadm;
grant select on DBA_HIST_OSSTAT to cfgadm;
grant select on DBA_HIST_PARAMETER_NAME to cfgadm;
grant select on DBA_HIST_PARAMETER to cfgadm;
grant select on DBA_HIST_WAITCLASSMET_HISTORY to cfgadm;
grant select on DBA_HIST_DLM_MISC to cfgadm;
grant select on DBA_HIST_CR_BLOCK_SERVER to cfgadm;
grant select on DBA_HIST_CURRENT_BLOCK_SERVER to cfgadm;
grant select on DBA_HIST_ACTIVE_SESS_HISTORY to cfgadm;
grant select on DBA_HIST_TABLESPACE_STAT to cfgadm;
grant select on DBA_HIST_LOG to cfgadm;
grant select on DBA_HIST_MTTR_TARGET_ADVICE to cfgadm;
grant select on DBA_HIST_TBSPC_SPACE_USAGE to cfgadm;
grant select on DBA_HIST_SERVICE_NAME to cfgadm;
grant select on DBA_HIST_SERVICE_STAT to cfgadm;
grant select on DBA_HIST_SERVICE_WAIT_CLASS to cfgadm;
grant select on DBA_HIST_SNAPSHOT to cfgadm;
grant select on DBA_SEGMENTS to cfgadm;
grant select on DBA_OUTSTANDING_ALERTS to cfgadm;
grant select on DICTIONARY to cfgadm;
grant select on DBA_REGISTRY to cfgadm;
grant select on DBA_HIST_COMP_IOSTAT to cfgadm;
grant select on DBA_HIST_UNDOSTAT to cfgadm;

grant select on DBA_VIEWS to cfgadm;
grant select on DBA_DB_LINKS to cfgadm;

grant select on DBA_TABLES to cfgadm;

grant select on v_\$session to cfgadm;
grant select on v_\$sqlarea to cfgadm;
grant select on v_\$sql_plan to cfgadm;
grant select on v_\$database to cfgadm;
grant select on v_\$instance to cfgadm;
grant select on v_\$datafile_copy to cfgadm;
grant select on v_\$parameter to cfgadm;
grant select on v_\$spparameter to cfgadm;
grant select on v_\$asm_disk to cfgadm;
grant select on v_\$asm_diskgroup to cfgadm;
grant select on v_\$system_fix_control to cfgadm;
grant select on gv_\$parameter to cfgadm;
grant select on gv_\$event_histogram to cfgadm;
grant select on sysman.BHV_SERVER_DETAILS to cfgadm;
grant select on sysman.MGMT_TARGETS to cfgadm;
grant select on sysman.MGMT_TABLE_SIZES to cfgadm;
grant select on sysman.MGMT_TARGET_CREDENTIALS to cfgadm;

EOF

tar_common.ksh


. /oracle/admin/scripts/tar_common.ksh

tar_create /oracle/admin/scripts/apexcommon_10092018.tar /oracle/admin/scripts/apex


. /oracle/admin/scripts/tar_common.ksh

tar_create /oracle/admin/scripts/clle_dbfscommon_10092018.tar /oracle/admin/scripts/clle_dbfscommon