Tuesday 28 December 2021

Upgrade AHF to latest Version (Log4j Vulnerability )

Please find the below steps to upgrade AHF to latest version 

Download latest AHF version and copy to db host  

Unzip software:

[root@racnode01 ahf_stg]# unzip -oq  AHF-LINUX_v21.3.4.zip


run setup command to upgrade:

[root@racnode01 ahf_stg]# ./ahf_setup 


AHF Installer for Platform Linux Architecture x86_64


AHF Installation Log : /tmp/ahf_install_213400_50508_2021_12_28-20_47_27.log


Starting Autonomous Health Framework (AHF) Installation


AHF Version: 21.3.4 Build Date: 202112151432


AHF is already installed at /opt/oracle.ahf


Installed AHF Version: 20.2.2 Build Date: 202008260643


Do you want to upgrade AHF [Y]|N : Y


Upgrading /opt/oracle.ahf


Shutting down AHF Services

Stopped OSWatcher

Nothing to do !

Shutting down TFA

Removed symlink /etc/systemd/system/graphical.target.wants/oracle-tfa.service.

Removed symlink /etc/systemd/system/multi-user.target.wants/oracle-tfa.service.

. . . . . 

. . . 

Successfully shutdown TFA..


Starting AHF Services

Starting TFA..

Created symlink from /etc/systemd/system/multi-user.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.

Created symlink from /etc/systemd/system/graphical.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.

Waiting up to 100 seconds for TFA to be started..

. . . . . 

Successfully started TFA Process..

. . . . . 

TFA Started and listening for commands

No new directories were added to TFA

Directory /app/grid/crsdata/racnode01/trace/chad was already added to TFA Directories.



INFO: Starting orachk scheduler in background. Details for the process can be found at /app/grid/oracle.ahf/data/racnode01/diag/orachk/compliance_start_281221_205029.log



Do you want AHF to store your My Oracle Support Credentials for Automatic Upload ? Y|[N] : N


AHF is successfully upgraded to latest version


.---------------------------------------------------------------------.

| Host          | TFA Version | TFA Build ID         | Upgrade Status |

+---------------+-------------+----------------------+----------------+

| racnode01 |  21.3.4.0.0 | 21340020211215143236 | UPGRADED       |

'---------------+-------------+----------------------+----------------'


Moving /tmp/ahf_install_213400_50508_2021_12_28-20_47_27.log to /app/grid/oracle.ahf/data/racnode01/diag/ahf/


[root@racnode01 ahf_stg]# tfactl status


.-----------------------------------------------------------------------------------------------------.

| Host          | Status of TFA | PID   | Port | Version    | Build ID             | Inventory Status |

+---------------+---------------+-------+------+------------+----------------------+------------------+

| racnode01 | RUNNING       | 58382 | 5000 | 21.3.4.0.0 | 21340020211215143236 | COMPLETE         |

'---------------+---------------+-------+------+------------+----------------------+------------------'

Uninstalling Oracle Autonomous Health Framework

As a root user

#ahfctl uninstall -deleterepo -silent

Starting AHF Uninstall

NOTE : Uninstalling will delete the repository as well since Install type is GI

AHF will be uninstalled on: 

racnode02


Stopping AHF service on local node racnode02...

Stopping TFA Support Tools...


Removed symlink /etc/systemd/system/multi-user.target.wants/oracle-tfa.service.

Removed symlink /etc/systemd/system/graphical.target.wants/oracle-tfa.service.


Removing AHF setup on racnode02:

Removing /etc/rc.d/rc0.d/K17init.tfa

Removing /etc/rc.d/rc1.d/K17init.tfa

Removing /etc/rc.d/rc2.d/K17init.tfa

Removing /etc/rc.d/rc4.d/K17init.tfa

Removing /etc/rc.d/rc6.d/K17init.tfa

Removing /etc/init.d/init.tfa...

Removing /etc/systemd/system/oracle-tfa.service...

Removing /u01/app/19.8.0.0/grid/bin/tfactl...

Removing /opt/oracle.ahf/rpms

Removing /opt/oracle.ahf/jre

Removing /opt/oracle.ahf/common

Removing /opt/oracle.ahf/bin

Removing /opt/oracle.ahf/python

Removing /opt/oracle.ahf/analyzer

Removing /opt/oracle.ahf/tfa

Removing /opt/oracle.ahf/orachk

Removing /opt/oracle.ahf/ahf

Removing /app/grid/oracle.ahf/data/racnode02

Removing /opt/oracle.ahf/install.properties

Removing /app/grid/oracle.ahf/data/repository

Removing /app/grid/oracle.ahf/data

Removing /app/grid/oracle.ahf

Removing AHF Home : /opt/oracle.ahf

Removing /sys/fs/cgroup/cpu/oratfagroup/

Friday 10 December 2021

Dynamic Sampling in Oracle

OPTIMIZER_DYNAMIC_SAMPLING is an Oracle database initialization parameter that controls the level of dynamic sampling used by the query optimizer when generating execution plans. The query optimizer is responsible for generating the most efficient execution plan for a SQL query by choosing the best execution path based on available statistics, indexes, and other factors.

Dynamic sampling is a feature that allows the optimizer to gather additional statistics on tables or indexes at runtime if it determines that the existing statistics are insufficient for generating an optimal execution plan. The OPTIMIZER_DYNAMIC_SAMPLING parameter specifies the level of dynamic sampling to be used, with valid values ranging from 0 to 10.

 0: Disable dynamic sampling
 1: Enable basic dynamic sampling
 2: Enable moderate dynamic sampling (default)
 3-10: Enable higher levels of dynamic sampling

It's important to note that increasing the level of dynamic sampling can increase the amount of time required to generate execution plans, so it's generally recommended to use the default value unless you have a specific need for more aggressive sampling. Additionally, dynamic sampling can be disabled for individual queries using the NO_DYNAMIC_SAMPLING hint.
 
Disable dynamic sampling at session level:

ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 0;

Disable dynamic sampling in db level:
ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING=0 scope=both sid='*';

 

Terraform script to create a PostgreSQL database on AWS

This script will create a PostgreSQL database instance using the aws_db_instance resource. The aws_security_group resource is used to allow inbound traffic on port 5432, which is the default port used by PostgreSQL. The output block will display the endpoint of the database instance created.

You will need to set the appropriate values for the region, identifier, engine_version, instance_class, allocated_storage, username, and password parameters to match your specific requirements. You can also customize the security group rules and tags as needed.

Once you have saved this script to a .tf file, you can run terraform init, terraform plan, and terraform apply commands to provision the PostgreSQL database instance on AWS.

 

Terraform scripts:

provider "aws" {
  region = "us-west-2"
}

resource "aws_security_group" "postgres" {
  name_prefix = "postgres"
  ingress {
    from_port   = 5432
    to_port     = 5432
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

resource "aws_db_instance" "postgres" {
  identifier            = "my-postgres-db"
  engine                = "postgres"
  engine_version        = "12.4"
  instance_class        = "db.t2.micro"
  allocated_storage     = 10
  storage_type          = "gp2"
  username              = "postgres"
  password              = "mysecretpassword"
  skip_final_snapshot   = true
  vpc_security_group_ids = [aws_security_group.postgres.id]

  tags = {
    Name = "My PostgreSQL DB"
  }
}

output "database_endpoint" {
  value = aws_db_instance.postgres.endpoint
}


GATHER AUTO in DBMS_STATS

The "Options => 'GATHER AUTO'" parameter setting is used in Oracle's DBMS_STATS package to specify that the statistics gathering operation should use automatic settings for degree of parallelism and sample size.

When "Options => 'GATHER AUTO'" is used, Oracle will automatically determine the optimal settings for collecting statistics based on the size and complexity of the database objects being analyzed. This can help ensure that statistics are collected efficiently without consuming excessive system resources.

Here is an example of how to use "Options => 'GATHER AUTO'" with the DBMS_STATS.GATHER_TABLE_STATS procedure to collect statistics for a table named "EMPLOYEES":


BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => 'HR', tabname => 'EMPLOYEES', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => DBMS_STATS.AUTO_DEGREE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE, options => 'GATHER AUTO'); END; /

In this example, "Options => 'GATHER AUTO'" is included as a parameter to the DBMS_STATS.GATHER_TABLE_STATS procedure, indicating that automatic settings should be used for the statistics gathering operation. This allows Oracle to determine the best settings for the job based on the characteristics of the "EMPLOYEES" table.

Tuesday 7 December 2021

ERROR OGG-01117 Oracle GoldenGate Performance Metrics Server for Oracle: Received signal: Segmentation violation

Pmsrvr receives SIGSEGV and crashes after recreating datastore.

we can see below error on ggserr.log 

ERROR   OGG-01117  Oracle GoldenGate Performance Metrics Server for Oracle:  Received signal: Segmentation violation

 

Cause:

Empty dirbdb directory under GG_HOME existed when recreating datastore.

pmsrvr does not initialize the datastore completely if <GG_HOME>/dirbdb/ exists even if the directory is empty.

 

Solution:

stop jagent

stop *

stop manager

stop pmsrvr


GGSCI 14> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           

JAGENT      STOPPED                                          

PMSRVR      STOPPED                                           

EXTRACT     STOPPED     PUMP1      00:00:00      00:00:53    

EXTRACT     STOPPED     EXT1       00:00:02      00:00:53    

EXTRACT     STOPPED     EXT2       00:00:00      00:00:51    

REPLICAT    STOPPED     REPLG1     00:00:00      00:00:46    



remove or mv dirbdb with backup name

mv <GG_HOME>/dirbdb/ <GG_HOME>/dirbdb_org


Start GG Process:

GGSCI 15> start pmsrvr -initds

PMSRVR started


GGSCI 16> start manager

Manager started.


GGSCI 17> start *

Sending START request to MANAGER ...

EXTRACT PUMP1 starting

Sending START request to MANAGER ...

EXTRACT EXT1 starting

Sending START request to MANAGER ...

EXTRACT EXT2 starting

Sending START request to MANAGER ...

REPLICAT REPLG1 starting


GGSCI 18> start jagent

Sending START request to MANAGER ...

GGCMD JAGENT starting



GGSCI 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

JAGENT      RUNNING                                           

PMSRVR      RUNNING                                           

EXTRACT     RUNNING     PUMP1      00:00:00      00:00:09    

EXTRACT     RUNNING     EXT1      00:00:03      00:00:07    

EXTRACT     RUNNING     EXT2     00:00:02      00:00:07    

REPLICAT    RUNNING     REPLG1     00:00:03      00:00:00   


 

Recreate datastore after delete old one helped to start PMSRVR without errors

Jagent unable to start PMSRVR , Stop all polling tasks. Wait for 9 second(s) and retry again. Due to Connection refused

Unable to start PMSRVR in golden gate 19.1.0.0.4

Got below error;
Fail to get Web Service Port, java.lang.Exception: PM Server is down

[2021-12-06T23:48:36.361-08:00] [JAGENT] [ERROR] [OGGMON-20348] [com.goldengate.monitor.jagent.jmx.ManagerConnectionKeeperThread] [tid: ManagerConnectionKeeper] [ecid: 0000NqJr8243z0GayxaeMG1Xfl69000001,0] Failed to establish a connection to the OGG Core Web Services-Host:HOSTIP Port:9,004. Stop all polling tasks. Wait for 9 second(s) and retry again. Due to Connection refused (Connection refused)


Cause:

There are multiple GoldenGate installations on the same host.


Solution:

cd $OGG_HOME/dirprm

ls -ltr pmsrvr-config.dat

Backup pmsrvr-config.dat, then change port <Port> to a port available.


Check file detais:

/u01/gg/dirprm> head pmsrvr-config.dat

{"config":{"network":{"serviceListeningPort":9004,"ipACL":[{"permission":"allow","address":"ANY"}]},"legacyProtocolEnabled":false,"asynchronousOperationEnabled":true,"defaultSynchronousWait":30,"workerThreadCount":5,"taskManagerEnabled":true,"csrfTokenProtectionEnabled":true,"csrfHeaderProtectionEnabled":false,"authorizationEnabled":false,"authorizationDetails":{"sessionDurationSecs":3600,"useMovingExpirationWindow":false,"movingExpirationWindowSecs":900,"common":{"allow":["Digest","x-Cert"],"customAuthorizationEnabled":true}},"security":false,"securityDetails":{"network":{"common":{"id":"OracleSSL","fipsEnabled":false,"fipsModulePath":"<not-set>"},"inbound":{"role":"server","authMode":"clientOptional_server","wrl":"file:./etc/ssl/server","wrlPassword":"","protocolVersion":"1_2","cipherSuites":"^((?!anon|RC4|NULL|3DES).)*$","crlEnabled":false,"crlStore":"file:./etc/ssl/crls.pem","certACL":[{"permission":"allow","name":"ANY"}],"sessionCachingEnabled":true,"sessionCacheDetails":{"limit":20480,"timeoutSecs":1800}},"outbound":{"role":"client","authMode":"client_server","wrl":"file:./etc/ssl/client","wrlPassword":"","protocolVersion":"1_2_Or_1_1_Or_1_0_Or_3_0","cipherSuites":"^.*$","crlEnabled":false,"crlStore":"file:./etc/ssl/crls.pem","certACL":[{"permission":"allow","name":"ANY"}],"sessionCachingEnabled":false}}},"hstsEnabled":true,"hstsDetails":"max-age=31536000;includeSubDomains","contentUrlRewrite":[{"srcUrl":"/index.html","targetUrl":"/services/v2/content/index.html"},{"srcUrl":"/$","targetUrl":"/services/v2/content/index.html"},{"srcUrl":"/content$","targetUrl":"/services/v2/content/index.html"},{"srcUrl":"/content/$","targetUrl":"/services/v2/content/index.html"},{"srcUrl":"/content/","targetUrl":"/services/v2/content/"},{"srcUrl":"/services/v2/content$","targetUrl":"/services/v2/content/index.html"},{"srcUrl":"/services/v2/content/$","targetUrl":"/services/v2/content/index.html"},{"srcUrl":"/css$","targetUrl":"/services/v2/content/css/"},{"srcUrl":"/css/$","targetUrl":"/services/v2/content/css/"},{"srcUrl":"/css/","targetUrl":"/services/v2/content/css/"},{"srcUrl":"/js$","targetUrl":"/services/v2/content/js/"},{"srcUrl":"/js/$","targetUrl":"/services/v2/content/js/"},{"srcUrl":"/js/","targetUrl":"/services/v2/content/js/"},{"srcUrl":"/favicon.ico$","targetUrl":"/services/v2/content/css/images/favicon.ico"}],"loadExtractHandler":true,"loadReplicatHandler":true,"loadDSHandler":true,"UDPReceiveBufferSize":10485760,"CollectorWaitInitTime":100,"CollectorWaitIncrement":100,"CollectorWaitMax":1500,"CollectorThreadStack":1048576,"CollectorWorkerThreads":5,"CollectorWorkerQueueLimit":10000,"MonitorTaskHBTimeout":10000,"MonitorTaskFrequency":1000,"DataStoreMemoryModel":"PrivateMemory","DataStoreSharedKey":0,"DataStoreType":"BDB","DataStoreMaxAge":0,"DataStoreMaxDBs":5000,"RepoDatastorePath":"","MessageLimit":500,"StatusChangeLimit":500}}[oracle@prodhost] 


/u01/gg/dirprm> vi pmsrvr-config.dat

Modify port from 9004 to 5504



Started jagent, jagent able to start PMSRVR process without any issue after changing port 

GGSCI> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

JAGENT      RUNNING                                           

PMSRVR      RUNNING    

Wednesday 1 December 2021

Install OCI CLI tool on linux machine

Install oci cli using yum 

[ds4docker]# yum install python36-oci-cli

Loaded plugins: langpacks, ulninfo

Resolving Dependencies

--> Running transaction check

---> Package python36-oci-cli.noarch 0:2.26.3-1.el7 will be updated

---> Package python36-oci-cli.noarch 0:3.3.1-1.el7 will be an update

--> Processing Dependency: python36-oci-sdk = 2.51.0 for package: python36-oci-cli-3.3.1-1.el7.noarch

--> Processing Dependency: python36-arrow >= 1.0.0 for package: python36-oci-cli-3.3.1-1.el7.noarch

--> Running transaction check

---> Package python36-arrow.noarch 0:0.17.0-1.0.2.el7 will be updated

---> Package python36-arrow.noarch 0:1.1.1-1.el7 will be an update

--> Processing Dependency: python36-typing-extensions for package: python36-arrow-1.1.1-1.el7.noarch

---> Package python36-oci-sdk.x86_64 0:2.43.0-1.el7 will be updated

---> Package python36-oci-sdk.x86_64 0:2.51.0-1.el7 will be an update

--> Processing Dependency: python36-circuitbreaker < 2 for package: python36-oci-sdk-2.51.0-1.el7.x86_64

--> Processing Dependency: python36-circuitbreaker >= 1.3.1 for package: python36-oci-sdk-2.51.0-1.el7.x86_64

--> Running transaction check

---> Package python36-circuitbreaker.noarch 0:1.3.1-1.el7 will be installed

---> Package python36-typing-extensions.noarch 0:3.7.4.2-1.el7 will be installed

--> Finished Dependency Resolution


Dependencies Resolved

==========================================================================================================

 Package                    Arch      Version     Repository     Size

===========================================================================================================

Updating:

 python36-oci-cli           noarch     3.3.1-1.el7               ol7_oci_included     12 M

Installing for dependencies:

 python36-circuitbreaker    noarch    1.3.1-1.el7               ol7_oci_included     8.1 k

 python36-typing-extensions noarch    3.7.4.2-1.el7              ol7_oci_included    2 k

Updating for dependencies:

 python36-arrow             noarch    1.1.1-1.el7               ol7_oci_included     115 k

 python36-oci-sd            x86_64    2.51.0-1.el7              ol7_oci_included     18 M


Transaction Summary

============================================================================================================

Install             ( 2 Dependent packages)

Upgrade  1 Package  (+2 Dependent packages)

Total download size: 30 M

Is this ok [y/d/N]: y

Downloading packages:

Delta RPMs disabled because /usr/bin/applydeltarpm not installed.

(1/5): python36-circuitbreaker-1.3.1-1.el7.noarch.rpm                                                                                                               | 8.1 kB  00:00:00     

(2/5): python36-arrow-1.1.1-1.el7.noarch.rpm                                                                                                                        | 115 kB  00:00:00     

(3/5): python36-oci-cli-3.3.1-1.el7.noarch.rpm                                                                                                                      |  12 MB  00:00:00     

(4/5): python36-typing-extensions-3.7.4.2-1.el7.noarch.rpm                                                                                                          |  42 kB  00:00:00     

(5/5): python36-oci-sdk-2.51.0-1.el7.x86_64.rpm                                                                                                                     |  18 MB  00:00:00     

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

Total                               57 MB/s |  30 MB  00:00:00     

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

  Installing : python36-circuitbreaker-1.3.1-1.el7.noarch  1/8 

  Updating   : python36-oci-sdk-2.51.0-1.el7.x86_64        2/8 

  Installing : python36-typing-extensions-3.7.4.2-1.el7.noarch 3/8 

  Updating   : python36-arrow-1.1.1-1.el7.noarch       4/8 

  Updating   : python36-oci-cli-3.3.1-1.el7.noarch  5/8 

  Cleanup    : python36-oci-cli-2.26.3-1.el7.noarch  6/8 

  Cleanup    : python36-arrow-0.17.0-1.0.2.el7.noarch 7/8 

  Cleanup    : python36-oci-sdk-2.43.0-1.el7.x86_64 8/8 

  Verifying  : python36-arrow-1.1.1-1.el7.noarch 1/8 

  Verifying  : python36-typing-extensions-3.7.4.2-1.el7.noarch 2/8 

  Verifying  : python36-oci-sdk-2.51.0-1.el7.x86_64  3/8 

  Verifying  : python36-circuitbreaker-1.3.1-1.el7.noarch 4/8 

  Verifying  : python36-oci-cli-3.3.1-1.el7.noarch   5/8 

  Verifying  : python36-arrow-0.17.0-1.0.2.el7.noarch 6/8 

  Verifying  : python36-oci-sdk-2.43.0-1.el7.x86_64  7/8 

  Verifying  : python36-oci-cli-2.26.3-1.el7.noarch  8/8 


Dependency Installed:

  python36-circuitbreaker.noarch 0:1.3.1-1.el7                    python36-typing-extensions.noarch 0:3.7.4.2-1.el7                                             

Updated:

  python36-oci-cli.noarch 0:3.3.1-1.el7                               

Dependency Updated:

  python36-arrow.noarch 0:1.1.1-1.el7                            python36-oci-sdk.x86_64 0:2.51.0-1.el7                                                       

Complete!


Set up oci config :

[root@oracle-zdmconfig .oci]# oci setup config

 This command provides a walkthrough of creating a valid CLI config file.

 The following links explain where to find the information required by this

    script:

    User API Signing Key, OCID and Tenancy OCID:

        https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#Other

    Region:      https://docs.cloud.oracle.com/Content/General/Concepts/regions.htm

General config documentation:  https://docs.cloud.oracle.com/Content/API/Concepts/sdkconfig.htm


Enter a location for your config [/root/.oci/config]: /root/.oci/config

Enter a user OCID: 

Enter a tenancy OCID: 

Enter a region by index or name(e.g.

1: ap-chiyoda-1, 2: ap-chuncheon-1, 3: ap-hyderabad-1, 4: ap-ibaraki-1, 5: ap-melbourne-1,

6: ap-mumbai-1, 7: ap-osaka-1, 8: ap-seoul-1, 9: ap-singapore-1, 10: ap-sydney-1,

11: ap-tokyo-1, 12: ca-montreal-1, 13: ca-toronto-1, 14: eu-amsterdam-1, 15: eu-frankfurt-1,

16: eu-marseille-1, 17: eu-zurich-1, 18: il-jerusalem-1, 19: me-dubai-1, 20: me-jeddah-1,

21: sa-santiago-1, 22: sa-saopaulo-1, 23: sa-vinhedo-1, 24: uk-cardiff-1, 25: uk-gov-cardiff-1,

26: uk-gov-london-1, 27: uk-london-1, 28: us-ashburn-1, 29: us-gov-ashburn-1, 30: us-gov-chicago-1,

31: us-gov-phoenix-1, 32: us-langley-1, 33: us-luke-1, 34: us-phoenix-1, 35: us-sanjose-1): us-sanjose-1

Do you want to generate a new API Signing RSA key pair? (If you decline you will be asked to supply the path to an existing key.) [Y/n]: n

Enter the location of your API Signing private key file: /opt/ds4docker/private_key

Fingerprint: 

Config written to /root/.oci/config


    If you haven't already uploaded your API Signing public key through the

    console, follow the instructions on the page linked below in the section

    'How to upload the public key':


        https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#How2