Author Archives: Rick Miners

About Rick Miners

I got started with Oracle 8i and have my OCP in 9i, 10g, & 11g. My areas of expertise is in RAC, Exadata and GoldenGate, AWS and Oracle Cloud Infrastructure.

Terraform in OCI

What is Terraform

Terraform is an infrastructure as code software by HashiCorp.

It allows users to define a datacenter infrastructure in a high-level configuration language, from which it can create an execution plan to build the infrastructure.

Infrastructure is defined in a HCL Terraform syntax or JSON format.

Can be used with the following cloud providers. (This is not an all inclusive list.)

  • AWS
  • IBM Cloud
  • Google Cloud Platform
  • Microsoft Azure
  • Oracle Cloud Infrastructure

Example Provisioning of Linux Instance in OCI-Terraform

OCI Pre-requisites

  • IAM User
  • IAM API Fingerprint

Terraform Pre-requisites

  • Linux or other supported OS
  • Install Terraform and OCI-Provider

Set Environment

$ cat env-vars
# ####################################################################
# # Public/private keys used on the instance
# ####################################################################
export TF_VAR_ssh_public_key=$(cat /home/user/.ssh/my_opc_key.pub)
export TF_VAR_ssh_private_key=/home/user/.ssh/my_opc_key

# ####################################################################
# # Authentication
# ####################################################################
export TF_VAR_user_ocid="ocid1.user.oc1..xxxxxxxxxxx"
export TF_VAR_fingerprint="xxxxxxxxxxx"
export TF_VAR_private_key_path=/home/user/.ssh/my_api_key

Configure TF Files for Cloud Server Provisioning

In this example, an OL 7.5 VM will be provisioned.

$ ls -1
block.tf
compute.tf
datasources.tf
links.sh
outputs.tf
terraform.tfstate
userdata
variables.tf
vars-compartment.tf
vars-images.tf
vars-provider.tf
vars-region.tf
vars-ssh.tf
vars-subnets.tf
vars-tenancy.tf
vars-user.tf
vars-vcn-cidr.tf

The variables.tf is one the file that has the variable values will need to be set.
The other files contain the structure of the instance, where it is to be provisioned and the mappings for the variables being used.

$ cat variables.tf
# ######################################
# instance details
# ######################################
variable "AD" { default = "2" }
variable "privateIP" { default = "10.10.10.74" }
variable "subnet" { default = "SN2" }

variable "InstanceShape" { default = "VM.Standard2.1" }
variable "InstanceName" { default = "test01phx" }
variable "image_id" { default = "ol75-phoenix" }
# boot volume
variable "bv_size" { default = "100" }

# block volume
variable "BV1Name" { default = "test01phxBV1" }
variable "BV1Size" { default = "50" }

variable "BootStrapFile" { default = "./userdata/bootstrap" }

The variables.tf file above defines the following:

  • OL 7.5 operating system
  • Boot volume of 100G (the defualt is 50G)
  • A block volume of 50G
  • The contents of the bootstrap file will install the required packages to resize the boot volume.

In order to change the image version, subnet or other variable, please reference the vars*.tf files for the appropriate values.

Provisioning the Virtual Machine

Execute “terraform plan” to verify the tf files and the placement of the VM.

$ terraform plan
Refreshing Terraform state in-memory prior to plan...
The refreshed state will be used to calculate this plan, but will not be
persisted to local or remote state storage.

data.oci_identity_availability_domains.ADs: Refreshing state...

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

An execution plan has been generated and is shown below.
Resource actions are indicated with the following symbols:
+ create
~ update in-place
<= read (data resources)

Terraform will perform the following actions:

~ data.oci_core_vnic.InstanceVnic
id: "" => <computed>
availability_domain: "" => <computed>
compartment_id: "" => <computed>
display_name: "" => <computed>
hostname_label: "" => <computed>
is_primary: "" => <computed>
mac_address: "" => <computed>
private_ip_address: "" => <computed>
public_ip_address: "" => <computed>
skip_source_dest_check: "" => <computed>
state: "" => <computed>
subnet_id: "" => <computed>
time_created: "" => <computed>
vnic_id: "" => "${lookup(data.oci_core_vnic_attachments.InstanceVnics.vnic_attachments[0],\"vnic_id\")}"

<= data.oci_core_vnic_attachments.InstanceVnics
id: <computed>
availability_domain: "PHX-AD-2"
compartment_id: "ocid1.compartment.oc1..xxxxxxxxxxx"
instance_id: "${oci_core_instance.TFInstance.id}"
vnic_attachments.#: <computed>

+ oci_core_instance.TFInstance
id: <computed>
availability_domain: "PHX-AD-2"
boot_volume_id: <computed>
compartment_id: "ocid1.compartment.oc1..xxxxxxxxxxx"
create_vnic_details.#: "1"
create_vnic_details.0.assign_public_ip: "false"
create_vnic_details.0.display_name: "primaryvnic"
create_vnic_details.0.hostname_label: "test01phx"
create_vnic_details.0.private_ip: "10.10.10.74"
create_vnic_details.0.skip_source_dest_check: <computed>
create_vnic_details.0.subnet_id: "ocid1.subnet.oc1.phx.xxxxxxxxxxx"
display_name: "test01phx"
image: <computed>
ipxe_script: <computed>
launch_mode: <computed>
launch_options.#: <computed>
metadata.%: "2"
metadata.ssh_authorized_keys: "ssh-rsaxxxxxxxxxxx"
metadata.user_data: "xxxxxxxxxxx"
private_ip: <computed>
public_ip: <computed>
region: <computed>
shape: "VM.Standard2.1"
source_details.#: "1"
source_details.0.boot_volume_size_in_gbs: "100"
source_details.0.source_id: "ocid1.image.oc1.phx.xxxxxxxxxxx"
source_details.0.source_type: "image"
state: <computed>
subnet_id: <computed>
time_created: <computed>

+ oci_core_volume.TFBlock1
id: <computed>
availability_domain: "PHX-AD-2"
compartment_id: "ocid1.compartment.oc1..xxxxxxxxxxx"
display_name: "test01phxBV1"
is_hydrated: <computed>
size_in_gbs: "50"
size_in_mbs: <computed>
source_details.#: <computed>
state: <computed>
time_created: <computed>
volume_backup_id: <computed>

+ oci_core_volume_attachment.TFBlock1Attach
id: <computed>
attachment_type: "iscsi"
availability_domain: <computed>
chap_secret: <computed>
chap_username: <computed>
compartment_id: "ocid1.compartment.oc1..xxxxxxxxxxx"
display_name: <computed>
instance_id: "${oci_core_instance.TFInstance.id}"
ipv4: <computed>
iqn: <computed>
is_read_only: <computed>
port: <computed>
state: <computed>
time_created: <computed>
use_chap: <computed>
volume_id: "${oci_core_volume.TFBlock1.id}"


Plan: 3 to add, 1 to change, 0 to destroy.

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

Once verified all settings are correct, execute “terraform apply” to provision the VM.
The output will display the progress of the provisioning and it can also be viewed in the OCI console on the Instances page.

References

Terraform: https://www.terraform.io/
Terraform OCI-Provider: https://www.terraform.io/docs/providers/oci/index.html
Terraform/OCI-Provider GitHub: https://github.com/terraform-providers/terraform-provider-oci

Ansible in OCI – Oracle Cloud Infrastructure

Ansible is open source software that automates software provisioning, configuration management, and application deployment. Ansible connects via SSH, remote PowerShell or via other remote APIs.

In this example, I am using it to install and configure an OL7.5 VM after it was provisioned in OCI.

List the Servers to Configure

$ ansible-playbook generic-server-setup.yml --list-hosts
playbook: generic-server-setup.yml
play #1 (dr-others): dr-others TAGS: []
pattern: [u'dr-others']
hosts (1):
10.201.5.74

List the Tasks to Execute

$ ansible-playbook generic-server-setup.yml --list-tasks

playbook: generic-server-setup.yml

play #1 (dr-others): dr-others TAGS: []
tasks:
oci-console-user : create group TAGS: []
oci-console-user : create user TAGS: []
oci-console-user : create directory TAGS: []
oci-console-user : Set authorized key took from file TAGS: []
oci-console-user : common user alias TAGS: []
oci-console-user : set password TAGS: []
linux_rpms : install RPMs TAGS: [pkgs]
configure_sudoers : put entry in /etc/sudoers TAGS: [osuser]
create_linux_user : create group TAGS: [osuser]
create_linux_user : create user TAGS: [osuser]
create_linux_user : create directory TAGS: [osuser]
create_linux_user : Set authorized key took from file TAGS: [osuser]
create_linux_user : common user alias TAGS: [osuser]
create_linux_user : root alias TAGS: [osuser]
credential-rotation : show file TAGS: [osuser]
credential-rotation : display the result TAGS: [osuser]
credential-rotation : copy credential-rotation.sh TAGS: [osuser]
credential-rotation : run credential-rotation.sh TAGS: [osuser]
dns-disable-ipv6 : put entry in /etc/sysconfig/network TAGS: [dns]
dns-update-search-domains : touch /etc/dhcp/dhclient.conf TAGS: [dns]
dns-update-search-domains : put entry in /etc/dhcp/dhclient.conf TAGS: [dns]
dns-update-search-domains : Update search domains for Phoenix TAGS: [dns]
snmp-setup : put entry in /etc/snmp/snmpd.conf TAGS: [snmp]
snmp-setup : comment out entry in /etc/snmp/snmpd.conf TAGS: [snmp]
snmp-setup : comment out entry in /etc/snmp/snmpd.conf TAGS: [snmp]
snmp-setup : comment out entry in /etc/snmp/snmpd.conf TAGS: [snmp]
snmp-setup : Stop service snmp TAGS: [snmp]
snmp-setup : Start service snmp TAGS: [snmp]
snmp-setup : Enable service snmp TAGS: [snmp]
configure_iscsi : copy iscsiadd.sh TAGS: [iscsi]
configure_iscsi : put line in /etc/rc.local TAGS: [iscsi]
configure_iscsi : make /etc/rc.d/rc.local executable TAGS: [iscsi]
configure_iscsi : show lsblk TAGS: [iscsi]
configure_iscsi : display the disks TAGS: [iscsi]
configure_iscsi : attach the iscsi volumes TAGS: [iscsi]
configure_iscsi : show lsblk TAGS: [iscsi]
timezone : set timezone to {{ time_zone }} TAGS: [tz]
enable-display-back : X11DisplayOffset setting TAGS: [sshd]
enable-display-back : X11UseLocalhost setting TAGS: [sshd]
ntp_config : show file TAGS: [ntp]
ntp_config : display the result TAGS: [ntp]
ntp_config : install RPMs TAGS: [ntp]
ntp_config : copy update-ntp.sh TAGS: [ntp]
ntp_config : run update-ntp.sh TAGS: [ntp]
ntp_config : put entry in /etc/ntp.conf TAGS: [ntp]
ntp_config : put entry in /etc/ntp.conf TAGS: [ntp]
ntp_config : put entry in /etc/ntp.conf TAGS: [ntp]
ntp_config : put entry in /etc/ntp.conf TAGS: [ntp]
ntp_config : put entry in /etc/ntp.conf TAGS: [ntp]
ntp_config : start and enable the NTP service on OL6 TAGS: [ntp]
ntp_config : start and enable the NTP service on OL7 TAGS: [ntp]
ntp_config : stop and disable the chronyd service TAGS: [ntp]
ntp_config : display the ntpq output TAGS: [ntp]
ntp_config : display the result TAGS: [ntp]
reboot_vm : reboot VM TAGS: [reboot_vm]
set_fact TAGS: [reboot_vm]
reboot_vm : wait for server to come down TAGS: [reboot_vm]
reboot_vm : wait for server to come back up TAGS: [reboot_vm]

Run the Ansible playbook

Here is the beginning of the playbook run.

… and here is the end of the playbook run.

Note the ntp_config has “if” logic for different OS version.

Ansible is a versatile tool and easily allows you to automate the configuration of cloud VMs.

GDS – Global Data Services

Oracle GDS provides the following key capabilities for a set of replicated databases that are globally distributed or located within the same data center.

I implemented this architectural solution for a large financial company to meet their 99.99% SLA database uptime requirements.

Here is a short video showing a Java script using UCP connecting through GDS to two backend Oracle databases.

As an example the video below depicts how we used gdsctl to relocate the Oracle service to the other database and the Java script will also flip over with FAN events that via UCP connections. The end result … connections automatically continue on the surviving database automatically.

Oracle Cloud Infrastructure

Formerly Called Oracle Bare Metal Cloud Services

Are you looking for enterprise cloud solutions to run your Oracle Database?  Take a look at Oracle Cloud.

I spoke about cloud performance at OOW 2016 and it was really impressive with local NVMe storage available to handle large I/O intensive workloads. Download whitepaper Accenture-Oracle-Cloud-Performance-Test-October2016 to learn more.

Besides bare metal machines, you can also launch VMs and and databases.

The Bare Metal Cloud Database Service enables you to provision an Oracle Database System with an elastically scalable (up and down) number of cores (OCPU) in a Virtual Cloud Network (VCN).  You can launch Oracle Database 11.2 or 12.1 in Standard, Enterprise, High Performance, or Extreme Performance editions on your choice of two bare metal compute shapes with Local NVMe Storage: High I/O (4.2TB usable local storage) and Dense I/O (9.6TB usable local storage).

Monitoring Oracle with oratop

Intro

I recently had a situation where I was troubleshooting a system that had fallen victim to some intermittent and very high load averages.  At first, there were no apparent reasons for the high load averages, so that sent me searching for some monitoring tools to help identify the issue.  Besides using OSWBB and dstat, I also used the oratop utility which turns out to be a great way to get a quick overview and monitor active sessions in near real time in Oracle 11g and 12c databases.  It is also RAC and ASM aware.

 

References

Here’s the MOS note for more info and downloads.
oratop – Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

 

Supported Oracle Releases

11gR2 (11.2.0.3, 11.2.0.4)

12cR1

oratop in Action

There is a user guide available for download on the MOS note, but I will show my favorite options.

 

Startup

> ./oratop -f  -d -i 10 / as sysdba
The switches:
f – detailed format option
d – real time wait events ( the default is cumulative )
i – update interval in seconds

The remaining part of the string is the login.  Basically, any string that you use for sqlplus should work with oratop.

oratop_df

 

Output Sections

Here’s an explanation of the sections.  These menus can be found when pressing “h” when running oratop in interactive mode.

 
1 Database
Section 1 - database
        Global Database information

   Version        : Oracle major version
   role           : database_role
   db name        : db_unique_name
   time        [s]: time as of the most recent stats (hh24:mi:ss)
   up          [T]: database uptime
   ins         [N]: total number of instance(s)
   sn        [c,N]: total user sessions (active/inactive)
   us        [c,N]: number of distinct users
   mt        [s,N]: global database memory total (sga+pga)
   fra         [N]: flashback recovery area %used, (red &gt; 90%)
   er          [N]: diag active problem count (faults)
   % db      [s,N]: database time as %(dbtime/cpu) (red if &gt; 99%)

 

2 Instance

Section 2 - instance
        Top 5 Instance(s) Activity
        o Ordered by Database time desc

   ID        [c,N]: inst_id (instance id)
   %CPU      [m,N]: host cpu busy %(busy/busy+idle). (red if &gt; 90%)
   LOAD      [m,N]: current os load. (red if &gt; 2*#cpu &amp; high cpu)
   %DCU      [m,N]: db cpu otusef as %host cpu. (red if &gt; 99% &amp; high AAS)
   AAS       [s,N]: Average Active Sessions. (red if &gt; #cpu)
   ASC       [c,N]: active Sessions on CPU
   ASI       [c,N]: active Sessions waiting on user I/O
   ASW       [c,N]: active Sessions Waiting, non-ASI (red if &gt; ASC+ASI)
   ASP       [m,N]: active parallel sessions (F/G)
   AST       [c,N]: Active user Sessions Total (ASC+ASI+ASW)
   UST       [c,N]: user Sessions Total (ACT/INA)
   MBPS      [m,N]: i/o megabytes per second (throughput)
   IOPS      [m,N]: i/o requests per second
   IORL      [m,T]: avg synchronous single-block read latency. (red &gt; 20ms)
   LOGR      [s,N]: logical reads per sec
   PHYR      [s,N]: physical reads per sec)
   PHYW      [s,N]: physical writes per sec
   %FR       [s,N]: shared pool free %
   PGA       [s,N]: total pga allocated
   TEMP      [s,N]: temp space used
   UTPS      [s,N]: user transactions per sec
   UCPS    [c,m,N]: user calls per sec
   SSRT    [c,m,T]: sql service response time (T/call)
   DCTR      [m,N]: database cpu time ratio
   DWTR      [m,N]: database wait time ratio. (red if &gt; 50 &amp; high ASW)
   %DBT      [s,N]: instance %Database Time (e.g. non-rac shows 100%)

 

3 Wait Events

Section 3 - db wait events
        Top 5 Timed Events
        o Cluster-wide, non-idle
        o Ordered by wait time desc

  EVENT      : wait event name. (red if active)
        (RT) : Real-Time mode
  WAITS      : total waits
  TIME(s)    : total wait time in seconds)
  AVG_MS     : average wait time in milliseconds
  PCT        : percent of wait time (all events)
  WAIT_CLASS : name of the wait class

 

 

4 Processes

Section 4 - process
        o Non-Idle processes
        o Ordered by event wait time desc

   ID          [N]: inst_id. (red if blocking)
   SID         [N]: session identifier. (red if blocking)
   SPID        [N]: server process os id
   USERNAME       : Oracle user name
   PROGRAM        : process program name
   SRV            : SERVER (dedicated, shared, etc.)
   SERVICE        : db service_name
   PGA         [N]: pga_used_mem. (red if continuously growing)
   SQL_ID/BLOCKER : sql_id or the final blocker's (inst:sid, in red)
   OPN            : operation name, e.g. select
   E/T         [T]: session elapsed time (active/inactive)
   STA            : ACTive|INActive|KILled|CAChed|SNIped
   STE            : process state, e.g. on CPU or user I/O or WAIting
   WAIT_CLASS     : wait_class for the named event
   EVENT/*LATCH   : session wait event name. Auto toggle with *latch name.
                    (red if process is hung/spin)
   W/T         [T]: event wait time. (red if &gt; 1s)

 

 

Blocking Sessions

If there are blocking sessions present, the instance and sid will be shown in the SQL ID/BLOCKER column.

oratop_blocking_session

 

Stopping

To quit the program, user may press any of the following keyboard keys:
Character “q” or “Q”, or Esc key
Ctrl+c (to abort).

 

Execution Plans

By pressing “x”, and then giving the sqlid, the explain plan of the sql in question will be displayed.  Although this is a nice and quick feature, it does not show as much detail about the plan as the display_cursor procedure will as shown below.  As far as I can tell, it does show the actual plan and not the estimated ones that are displayed without acttually running the query, but I have not thorougly tested this.
select * from table(dbms_xplan.display_cursor(‘&sql_id’,’&child_no’,’typical’));

 

Space

The “a” option can be used to show info on the disk groups.
The “t” option can be used to show info on the tablespaces.

Oracle Golden Gate – OGG-01028 Incompatible record

Intro

The incompatible record error is one I have seen a couple of times now with DB2 as the source and it seems to hing around something that occurs on weekends when they take the system down for maintenance.  I have yet to pinpoint the cause, but this workaround does get everything back up and running.

 

Symptoms

From the report file:

Switching to next trail file ./dirdat/qt000141 at 2014-10-20 09:14:57 due to EOF, with current RBA 99999985
Opened trail file ./dirdat/qt000141 at 2014-10-20 09:14:57


Source Context :
  SourceModule            : [er.processloop]
  SourceID                : [/scratch/aime1/adestore/views/aime1_adc4150267/oggcore/OpenSys/src/app/er/processloop.cpp]
  SourceFunction          : [process_extract_loop]
  SourceLine              : [874]
  ThreadBacktrace         : [7] elements
                          : [/apps/oracle/ggs/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7fc89734bc3e]]
                          : [/apps/oracle/ggs/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2cc) [0x7fc89734498c]]
                          : [/apps/oracle/ggs/libgglog.so(_MSG_ERR_ER_GENERIC_FAILURE(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x31) [0x7fc8
9732f839]]
                          : [/apps/oracle/ggs/replicat(process_extract_loop()+0x31f1) [0x53e581]]
                          : [/apps/oracle/ggs/replicat(main+0x732) [0x54faa2]]
                          : [/lib64/libc.so.6(__libc_start_main+0xfd) [0x3e5ce1ed1d]]
                          : [/apps/oracle/ggs/replicat(__gxx_personality_v0+0x332) [0x4c233a]]

2014-10-20 09:15:17  ERROR   OGG-01028  Incompatible record (101) in ./dirdat/qt000141, rba 1155 (getting header).

 

Fix

Use the logdump utility to gather the required info from the trail file.

 

&gt; cd $GG_HOME
&gt; ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.7 16934304 OGGCORE_11.2.1.0.7_PLATFORMS_130709.1600.1

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.



Logdump 22 &gt;log to incompat_record.log
--- Session log incompat_record.log opened 2014/10/20 09:31:32.434.370 ---
Logdump 23 &gt;open /apps/oracle/ggs/dirdat/qt000141
Current LogTrail is /apps/oracle/ggs/dirdat/qt000141
Logdump 24 &gt;ghdr on
Logdump 25 &gt;pos 1155
Reading forward from RBA 1155
Logdump 26 &gt;n
Bad record found at RBA 1155, format 5.50 Header token)
 8004 8647                                         | ...G
Logdump 27 &gt;sfh prev
Scanned    1155 bytes and did not find a header
Bad record found at RBA 1155, format 5.50 Header token)
 8004 8647                                         | ...G
Logdump 28 &gt;sfh next
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :   119  (x0077)   IO Time    : 2014/10/18 00:26:10.455.904
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x01)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :          0       AuditPos   : 58745476396002
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/10/18 00:26:10.455.904 FieldComp            Len   119 RBA 1158
Name: schema_name.table_name
After  Image:                                             Partition 4      m
 0000 000a 0000 0000 0000 0335 dba7 0001 000a 0000 | ...........5........
 0000 0000 0000 0001 0021 0034 0000 c4e3 d3f0 f0f1 | .........!.4........
 c2f3 4040 4040 4040 4040 4040 4040 4040 4040 4040 | ..@@@@@@@@@@@@@@@@@@
 4040 4040 4040 4040 4040 4040 4040 4040 4040 4040 | @@@@@@@@@@@@@@@@@@@@
 4040 4040 0022 001f 0000 f2f0 f1f4 60f1 f060 f1f8 | @@@@.&quot;........`..`..
 7af0 f07a f2f6 7af5 f54b f8f7 f0f8 f5f4 f0f0 f0   | z..z..z..K.........

Logdump 29 &gt;exit


Since the issue is with the header of the file, I went to the next record instead of the previous one.  Looks like RBA 1158 is good, and doing a count from here works so the rest of the file is good.  So, let’s alter the replicat to this next good record and start it up.

 

&gt; ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.7 16934304 OGGCORE_11.2.1.0.7_PLATFORMS_130709.1600.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 07:04:28

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI &gt; info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     DB_REP    56:32:07      00:35:28

GGSCI &gt; info *

REPLICAT   DB_REP  Last Started 2014-10-20 10:05   Status ABENDED
Checkpoint Lag       57:39:50 (updated 00:02:38 ago)
Log Read Checkpoint  File ./dirdat/qt000140
                     2014-10-18 00:26:09.793226  RBA 99981632


GGSCI &gt; alter replicat DB2P_REP, extseqno 141, extrba 1158
REPLICAT altered.

GGSCI &gt; start replicat DB_REP

Sending START request to MANAGER ...
REPLICAT DB_REP starting

 

After starting the replicat, normal processing of records started again.

 

References

 

How to Recover from an OGG-01028 Incompatible Record If the Trail Is Not Corrupt (Doc ID 1507462.1)

Using dstat for monitoring CPU, Memory, Disk, etc

Overview

dstat is a good tool for replacing vmstat, netstat, iostat, and ifstat.  It it a very lightweight tool that can consolidate and correlate data  that  you would normally have to capture in different terminals.

 

Examples

 

RMAN Backup Throughput

I’ve used dstat before in the past to monitor backup throughput over interfaces.  Of course, this is possible if the backups are going to disk.

Run ifconfig to get the name of the interface you want to monitor.

Run dstat and watch the receive and send columns.

dstat -cn -N bond0.133 -C total -f 5

----total-cpu-usage---- net/bond0.1
usr sys idl wai hiq siq| recv  send
  6   7  83   4   0   0|   0     0
 11  40  44   5   0   0|1303k 1686k
 14  39  40   7   0   0|5295k 6131k
 11  36  47   6   0   0|1733k 3630k
 10  34  51   5   0   0|2334k   11M
  8  32  54   5   0   0|2189k   14M
  6  10  78   6   0   0|1418k   25M
  6   0  86   7   0   0|1277k 1563k
  8   1  84   8   0   0|1847k 2013k
  8   1  84   7   0   0|1831k 4063k
 13  47  35   5   0   0|1974k 7948k
 12  28  55   5   0   0|1269k   16M
 10  19  64   7   0   0|1308k 2674k
 10  11  73   7   0   0|1474k 2590k
 15  65  18   3   0   0|1566k 4440k

 

CPU, Load, Disk I/O and Memory

Running “dstat -h” will show the options available.  Below is a script that shows some of the options I normally use. 

 cat dstat.sh

#!/bin/sh
echo
dstat -h | egrep &quot;time|load|proc|cpu|disk|io|sys|vm&quot; | egrep -v &quot;cpu0|aio|filesystem|epoch|Usage&quot;
echo

CURRENT_TIME=`date +&quot;%m_%d_%Y_%H_%M&quot;`

#dstat --time --load -p -c --disk --mem --top-cpu --top-bio --top-latency --output /home/oracle/rick/log/dstat_output_${CURRENT_TIME}_.csv 10
#dstat --time --load -p -c --disk --mem --top-cpu --top-bio --top-latency 10
dstat --time --load --proc --cpu --top-cpu --disk --io --sys --vm 60

exit 0

Below that is a sample run on a busy server.

 ./dstat.sh

Versatile tool for generating system resource statistics
Dstat options:
  -c, --cpu              enable cpu stats
  -d, --disk             enable disk stats
  -l, --load             enable load stats
  -p, --proc             enable process stats
  -r, --io               enable io stats (I/O requests completed)
  -t, --time             enable time/date output
  -y, --sys              enable system stats
  --vm                   enable vm stats
  -v, --vmstat           equals -pmgdsc -D total

----system---- ---load-avg--- ---procs--- ----total-cpu-usage---- -most-expensive- -dsk/total- --io/total- ---system-- -----virtual-memory----
  date/time   | 1m   5m  15m |run blk new|usr sys idl wai hiq siq|  cpu process   | read  writ| read  writ| int   csw |majpf minpf alloc  free
15-10 14:17:19|15.9 21.3 24.1|0.0 0.0 150|  6   7  83   4   0   0|ora_as04_LASP0.2|  89M   33M|1141  1095 |  24k   21k|   0    31k   52k   52k
15-10 14:18:19|30.5 24.5 25.0| 13 6.2 465|  8  15  72   5   0   0|oracleORCL  1.0|  32M   21M|1674  1273 |  43k   31k|   0   122k   91k   91k
15-10 14:19:19|22.8 23.0 24.5| 11 4.6 473|  6  12  77   5   0   0|oracleORCL  1.0|  25M   15M|1645  1062 |  40k   30k|   0   115k   82k   84k
15-10 14:20:19|23.6 22.9 24.3| 12 5.2 476|  6  13  76   5   0   0|oracleORCL  1.2|  66M   13M|1716  1127 |  40k   29k|   0   117k   86k   86k
15-10 14:21:19|14.9 20.5 23.4|5.3 5.2 467|  3   6  86   5   0   0|oracleORCL  0.4|  28M   14M|1530  1069 |  32k   26k|   0    99k   57k   57k
15-10 14:22:19|20.5 20.6 23.2|8.8 6.4 466|  5  10  80   5   0   0|oracleORCL  0.8|  70M   20M|1824  1296 |  39k   32k|   0   104k   73k   73k
15-10 14:23:19|15.7 19.2 22.6| 12 7.3 476|  6  12  76   6   0   0|oracleORCL  1.5|  39M   18M|1840  1218 |  41k   33k|   0   117k   89k   89k
15-10 14:24:19|15.8 18.6 22.2| 12 7.5 278|  7  11  75   6   0   0|oracleORCL  1.3| 160M   95M|2142   885 |  37k   30k|   0    64k   77k   77k
15-10 14:25:19|28.2 22.4 23.3| 18 7.5 203|  8  22  65   5   0   0|oracleORCL  1.1|  98M   58M|2608   684 |  42k   31k|   0    70k  110k  110k
15-10 14:26:19|31.6 23.8 23.7| 12 6.8 176|  7  13  75   5   0   0|oracleORCL  1.1|  54M   14M|1944   715 |  30k   25k|   0    39k   56k   57k missed 2 ticks
15-10 14:27:19|32.0 26.4 24.6| 18 4.8 200|  6  20  70   4   0   0|oracleORCL  0.9|  30M   17M| 932  1214 |  37k   27k|   0    72k   98k   98k
15-10 14:28:19|50.7 32.5 26.8| 26 5.3 227| 10  34  52   4   0   0|oracleORCL  1.3|  55M   20M|1200  1088 |  47k   25k|   0   108k  152k  152k missed 2 ticks
15-10 14:29:19|35.4 31.4 26.8| 22 4.5 217|  7  26  64   3   0   0|oracleORCL  0.8|  13M   22M|1071  1113 |  40k   23k|   0    94k  127k  127k
15-10 14:30:19|30.9 30.8 26.8| 20 3.5 212| 11  23  63   3   0   0|oracleORCL  1.5|  70M   16M|1062  1426 |  37k   20k|   0    83k   98k   98k missed 2 ticks
15-10 14:31:19|26.7 28.9 26.4| 10 2.4 182|  7  11  79   3   0   0|oracleORCL  1.0|  38M   16M| 786   892 |  27k   19k|   0    59k   59k   59k
15-10 14:32:19|15.7 25.4 25.4| 18 2.5 208|  6  23  69   2   0   0|oracleORCL  0.7|  53M   16M| 577  1012 |  33k   17k|   0    85k  107k  108k

Changing Timezone Settings on Oracle Database Servers

Twice now in the past month, I have run across some clients that are wanting to change the timezone of their database due to a server relocation. Both cases ended up being a bit similar, one a single instance and the other on Exadata. Since I had to lookup how to do it the seconds time, I thought I would blog about it. Here’s the info on how to view the timezone settings and how to change them.

These settings shown were valid for DB 11.2.0.2 and above on Redhat/Oracle Linux 6.

OS Settings

Look at /etc/localtime to see the timezone setting for the server. The
setting should be set to an entry in the /usr/share/zoneinfo directory/subdirectory.

 
ls -lhF /etc/localtime
lrwxrwxrwx 1 root root 35 May  9 17:54 /etc/localtime -> /usr/share/zoneinfo/America/Chicago

Grid Timezone Setting

Config File in Grid Home

The TZ entry in $GRID_HOME/crs/install/s_crsconfig__env.txt the timezone entry for the Grid Home; hence, the ASM and listener.

 
> grep TZ $GRID_HOME/crs/install/cat s_crsconfig_db1_env.txt 
TZ=America/Chicago

Grid Infrastructure Settings

If TZ is set via in the cluster via srvctl, it will take precedence over the s_crsconfig__env.txt setting.

 
>srvctl getenv listener -l listener
LISTENER:
TZ=America/Chicago

>srvctl getenv database -D DB1
DB1:
TZ=America/Chicago

The TZ setting can be set via the following srvctl command:

srvctl setenv listener -l listener -t 'TZ=America/Chicago'

srvctl setenv database -d DB1 -t 'TZ=America/Chicago'

Database Scheduler

The database scheduler works under the timezone setting at statup time. The timezone can be seen and modified with the following statements.

 
SQL> select dbms_scheduler.stime from dual;
STIME
---------------------------------------------------------------------------
17-SEP-14 11.57.49.394023000 AM EST5EDT

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','US/Central');

SQL> select dbms_scheduler.stime from dual;
STIME
---------------------------------------------------------------------------
17-SEP-14 11.59.49.313001000 AM US/CENTRAL

References

How To Change Timezone for 11gR2 Grid Infrastructure (Doc ID 1209444.1)
Dates & Calendars – Frequently Asked Questions ( Doc ID 227334.1 )
Incorrect SYSDATE shown when connected via Listener in RAC ( Doc ID 1390015.1 )

GoldenGate Initial-Load Trail to Replicat – Part 2

This article will demonstrate how to perform a OGG Initial Load using the Trail to Replicat technique. You can see part 1 here . In this article, we get by the bug since we are on a version higher than 11.2.1.0.1.

Using this method, there is no limit on the amount of data that can be loaded initially. This particular run created 117 2GB trail files on the target to be loaded by the initial load replicat.

Other Initial Load techniques and their advantages and disadvantages can be found here .

Pre-requisites

OGG is installed on source and target systems

Environment

The source database is DB2 on z/OS mainframe with OGG 11.2.1.0.5.

The target database is Oracle:
OGG : 11.2.1.0.7
Database : 11.2.0.3
Linux : OEL 6 ( 64 bit )

Trail to Replicat Steps

1. Configure the initial-load processes

Extract

 
extract ildb2
sourceistable
sourcedb db2t, userid ggcap, password ggcap
rmthost target_server, mgrport 7809, tcpbufsize 1048576, tcpflushbytes 1048576
rmtfile ./dirdat/la, megabytes 2048, maxfiles 999
TABLE user.table1;

Replicat

 
replicat ilrdb2
END RUNTIME
SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1")
SETENV(ORACLE_SID="ORA11G")
SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
UserID ggrep, Password ggrep
SOURCEDEFS ./dirdef/db2t_source_tabs.def
DISCARDFILE ./dirrpt/ilrdb2.dsc, Purge
MAP user.table1, TARGET user.table1;

2. Configure the change-synchronization processes

This is the normal replication methods, so I will not cover them here to keep things short. You can see example of this in some of my other posts.

3. Start the change-synchronization Extract

 
GGSCI> start extract cdb2 

4. Start the initial-load extract

 
GGSCI> add extract ildb2 , sourceistable 
GGSCI> start extract ildb2 

You should observe the following:

* the collector starting on target as seen in the ggserr.log
* the trail files in dirdat directory on target ( $GG_HOME/dirdat/rt* )
* report file in dirrpt directory on source

Monitor the ildb2.rpt report file for any errors.

5. Start the initial-load replicat on the target environment

Since we are using normal trail files here, the replicat can be started w/o having to wait on the initial-load extract to complete. We are also using the checkpoint table here so the process can be restarted if necessary.

GGSCI> DBLogin UserID ggrep, Password ggrep
GGSCI> add replicat ilrdb2, ExtTrail ./dirdat/aa, CheckPointTable ggrep.ggs_checkpoint
GGSCI> start replicat ilrdb2

Monitor the report file for the initial-load replicat and wait until it the intiial-load extract is complete and the replicat is at the EOF with no more records to process. If there are no errors, then you can continue on to the next step.

6. Start the change-synchronization Replicat

GGSCI> start replicat rdb2

7. Turn off HANDLECOLLISIONS

SEND REPLICAT rdb2, NOHANDLECOLLISIONS

Lastly, edit the replicat parameter file and remove HANDLECOLLISIONS so that it is not enabled next time the replicat restarts.

Oracle GoldenGate : DB2 to Oracle

This article will demonstrate heterogeneous replication using OGG from DB2 to Oracle.

1. Pre-requisites

OGG is installed on source and target systems.

2. Environment

DB2 on Mainfram Z/OS
Oracle Database : 11.2.0.3

DB2 to Oracle

3. Configuration on Source Server ( DB2 )

A. Parameter Files

Place all parameter files in the $GG_HOME/dirprm directory.

 

Extract Capture

> cat cdb2.prm

extract cdb2
--trailcharsetascii
sourcedb db2t, userid ggcap, password ggcap
exttrail ./dirdat/lt
transmemory directory(./dirtmp, 450m, 450m),transallsources 450m
table user.table1;

 

Extract Data Pump

> cat pdb2.prm

extract pdb2
rmthost target_server, mgrport 7809
rmttrail ./dirdat/rt
passthru
table user.table1;

 

Extract for Initial Load

> cat ildb2.prm

extract ildb2
sourceistable
sourcedb db2t, userid ggcap, password ggcap
rmthost target_server, mgrport 7809, tcpbufsize 1048576, tcpflushbytes 1048576
rmtfile ./dirdat/la, megabytes 2048, maxfiles 999
TABLE user.table1;

 

B. Add Trandata

 

cd $GG_HOME

> ./ggsci

GGSCI> DBLogin sourcedb db2t, userid ggcap, password ggcap</strong>
GGSCI> add trandata user.table1
GGSCI> exit

 

C. Source Table Definitions

 

> cd $GG_HOME/dirdef

> cat defgen_db2t.prm
DEFSFILE dirdef/db2t_source_tabs.def, PURGE
sourcedb db2t, userid ggcap, password ggcap
TABLE user.table1;

> cd $GG_HOME

>./defgen paramfile </strong><strong>./dirdef/defgen_db2t.prm NOEXTATTR

 

When done, copy the output file (dirdef/db2t_source_tabs.def) over to the target database server and put it in the $GG_HOME/dirdef directory.

 

D. Create and Start the Extract Processes

 

> ./ggsci

GGSCI> DBLogin sourcedb db2t, userid ggcap, password ggcap
GGSCI> add extract cdb2 , tranlog DB2T.BSDS01 , begin now
GGSCI> add exttrail ./dirdat/lt, extract cdb2
GGSCI> add extract pdb2, exttrailsource ./dirdat/lt
GGSCI> add rmttrail ./dirdat/rt, extract pdb2
GGSCI> start extract cdb2
GGSCI> start extract pdb2

 

E. Create and Start the Initial Load Capture Process

 

GGSCI> add extract ildb2 , sourceistable
GGSCI> start extract ildb2

 

3. Configuration on Target Server ( Oracle)

A. Parameter Files

Place all parameter files in the $GG_HOME/dirprm directory.

 

Replicat

> cat rdb2.prm

REPLICAT RDB2
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1")
SETENV (ORACLE_SID="ORA11G")
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") 
USERID ggrep, PASSWORD ggrep 
DiscardRollover at 23:00
ReportRollover at 23:00 
ReportCount Every 5 Minutes, Rate
StatOptions ReportFetch 
DiscardFile ./dirrpt/RDB2.dsc, Append, Megabytes 100M
HandleCollisions
SOURCEDEFS ./dirdef/db2t_source_tabs.def
DbOptions SuppressTriggers
DbOptions DeferRefConst
MAP user.table1, table1 user.table1;

 

Initial load Replicat

> cat ilrdb2.prm

replicat ilrdb2
END RUNTIME
SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1")
SETENV(ORACLE_SID="ORA11G")
SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
UserID ggrep, Password ggrep
SOURCEDEFS ./dirdef/db2t_source_tabs.def
DISCARDFILE ./dirrpt/ilrdb2.dsc, Purge
MAP user.table1, TARGET user.table1;

 

B. Initial load Steps – File to Replicat

After the initial load process from DB2 has completed, then continue with the configuration of the initial load replicat.

Configure and Start the Initial-Load Replicat

> cd $GG_HOME

>./ggsci

GGSCI> DBLogin UserID ggrep, Password ggrep
GGSCI> add replicat ilrdb2, ExtTrail ./dirdat/aa, CheckPointTable ggrep.ggs_checkpoint
GGSCI> start replicat ilrdb2

 

After the initial-load replicat is complete, continue on to the next step.
 

Configure and Start the Change Synchronization Replicat

> cd $GG_HOME

>./ggsci

GGSCI> add replicat rdb2, ExtTrail ./dirdat/rt, CheckPointTable ggrep.ggs_checkpoint
GGSCI> start extract rdb2

 

Monitor the replicat for any errors and the current time and position of the records it is applying.

Next, verify that the replicat has passed the point where the initial-load replicat stopped. You can compare both time and the RBA by looking in the rdb2.rpt report file and the current position of the change synchronization replication as shown by the “info rdb2” command.

Turn Off Handle Collisions

> cd $GG_HOME

>./ggsci

GGSCI> send replicat rdb2, nohandlecollisions

 

Also, edit the parameter file and comment out the HANDLECOLLISIONS line so that the next time the replicat is restarted, this parameter will not take effect.

GGSCI> edit params rdb2

REPLICAT RDB2
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1")
SETENV (ORACLE_SID="ORA11G")
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") 
USERID ggrep, PASSWORD ggrep 
DiscardRollover at 23:00
ReportRollover at 23:00 
ReportCount Every 5 Minutes, Rate
StatOptions ReportFetch 
DiscardFile ./dirrpt/RDB2.dsc, Append, Megabytes 100M
--HandleCollisions
SOURCEDEFS ./dirdef/db2t_source_tabs.def
DbOptions SuppressTriggers
DbOptions DeferRefConst
MAP user.table1, table1 user.table1;