..: December 2021

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

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

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

| 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

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

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

| 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 *