About Brian Fitzgerald

Database administrator

Launch AMI by name in terraform

By Brian Fitzgerald

Introduction

Using Terraform, you can launch an AWS EC2 instance from any account and any region using a public AMI identified only by image name. Because you do not need to lookup the image id each time, your code can be simplified.

Approach

Red Hat account number

In the AWS console, notice that the Red Hat, Inc. account number is 309956199498.


AMI name

Find the Red Hat AMI image by name. In the console, navigate to EC2 Images AMIs. Filter on Owner: 309956199498, Architecture: 64-bit (x86), Virtualization type: HVM.

For example, “RHEL-7.9_HVM_GA-20200917-x86_64-0-Hourly2-GP2”. You could also search using the AWS command line:

$ aws ec2 describe-images --owners 309956199498 --filters "` cat filters.ami.json `" --region us-east-2

where filters.ami.json contains:

[
    {
        "Name": "architecture",
        "Values": [
             "x86_64"
        ]
    },
    {
        "Name": "virtualization-type",
        "Values": [
             "hvm"
        ]
    }
]

Terraform data source

Define a Terraform aws_ami data source.

ami-rhel.tf

data "aws_ami" "rhel" {
    most_recent = true
    owners = [
        "309956199498"
    ]
    filter {
        name   = "name"
        values = [
            "RHEL-7.9_HVM_GA-20200917-x86_64-0-Hourly2-GP2"
        ]
    }
}

aws instance resource

Use the data source in your Terraform aws_instance resource

provider "aws" {
    region      = var.region
}

resource "aws_instance" "your-ec2-rsrc" {
    ami           = data.aws_ami.rhel.image_id

and so on . . .

Launch the ec2 instance

Run “terraform apply”. Terraform determines the AMI ID. For example, ami-0d2bf41df19c4aac7.

Conclusion

By using the Terraform AWS AMI data source, you can launch a public AMI from any account and region. The AMI ID is different for each account and region.

curl on el5 with TLS 1.2

By Brian Fitzgerald

Background

We needed a curl that supportsTLS 1.2 to be working on Red Hat el5 in order to migrate off el5. The default curl does not support TLS 1.2
Solution: download and compile curl on el5

Test case

Download Oracle patch p6880880_200000_Linux-x86-64.zip from MOS.
Steps:
Store the URL, the file name, and your cookies in files url.txt, filename.txt , and cook.txt, respectively. For instructions on getting your browser cookies, please refer to Oracle binaries or patch download from Linux command line.

Symptom

$ uname -r
2.6.18-426.el5
$ which curl
/usr/bin/curl
$ curl -b cook.txt  -o "` cat filename.txt `" -L "` cat url.txt `"
curl: (35) error:14077410:SSL routines:SSL23_GET_SERVER_HELLO:sslv3 alert handshake failure

Prerequisites to compiling curl

perl 

Install Perl v5.10.0 minimum
unset LD_LIBRARY_PATH
cd /u02/sw/perl/src/perl-5.30.2
./Configure -de -Dprefix=/u02/sw
make
make install

openssl

cd /u02/sw/openssl/src/openssl-1.1.1g
export PATH=/u02/sw/bin:/bin:/usr/bin
./config --prefix=/u02/sw enable-egd  
make
make install

openldap

cd /u02/sw/openldap/src/openldap-2.4.56
export PATH=/bin:/usr/bin
export LD_FLAGS=-L/u02/sw/lib
export CPPFLAGS=-I/u02/sw/include
./configure --prefix=/u02/sw --with-ssl=/u02/sw
make
make install

sasl

cd /u02/sw/cyrus-sasl/src/cyrus-sasl-2.1.27
./configure --prefix=/u02/sw --with-openssl=/u02/sw/ssl
make
make install
ln -s libsasl2.so /u02/sw/lib/libsasl2.so.2

Compile curl

cd /u02/sw/curl/src/curl-7.73.0
./configure --prefix=/u02/sw --with-ssl=/u02/sw
make
make install
unset LD_FLAGS

Tests

el5

$ ./test.u02.curl.bash
2.6.18-426.el5
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
100  116M  100  116M    0     0  9357k      0  0:00:12  0:00:12 --:--:-- 17.3M

el6

$ ./test.u02.curl.bash
2.6.32-754.31.1.el6.x86_64
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
100  116M  100  116M    0     0  4132k      0  0:00:28  0:00:28 --:--:-- 4184k

el7

$ ./test.u02.curl.bash
3.10.0-1062.el7.x86_64
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
100  116M  100  116M    0     0  42.3M      0  0:00:02  0:00:02 --:--:-- 51.8M

Actual TLS version

Verbose output shows:
$ curl -v -b cook.txt -o “` cat filename.txt `” -L “` cat url.txt `”
 . .
* SSL connection using TLSv1.3 / TLS_AES_256_GCM_SHA384
* ALPN, server accepted to use http/1.1

Conclusion

A locally-compiled curl runs on Red Hat el5, el6, el7

aws lambda publish-layer-version fails silently

By Brian Fitzgerald

Introduction

aws lambda publish-layer-version fails silently. Out of memory is the root cause. Changing the instance type fixes the problem.

Symptoms

aws lambda publish-layer-version produces no output. The exit status is nonzero.

[root@ip-172-31-62-89 layers]# aws lambda publish-layer-version --layer-name oracle-instant-client-layer --zip-file fileb://oracle-instant-client-layer.zip  --compatible-runtimes python3.7


[root@ip-172-31-62-89 layers]# echo $?
255

Investigation

Investigation using strace reveals an out of memory condition

[root@ip-172-31-62-89 layers]# uname -a
Linux ip-172-31-62-89.ec2.internal 4.14.186-146.268.amzn2.x86_64 #1 SMP Tue Jul 14 18:16:52 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
[root@ip-172-31-62-89 layers]# strace -f -o tr aws lambda publish-layer-version --layer-name oracle-instant-client-layer --zip-file fileb://oracle-instant-client-layer.zip  --compatible-runtimes python3.7
[root@ip-172-31-62-89 layers]# grep ENOMEM tr
3576  mmap(NULL, 272371712, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
3576  mmap(NULL, 272502784, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)

The instance type is t2.micro. Physical memory is 983 MB

[root@ip-172-31-62-89 layers]# curl -X GET http://169.254.169.254/latest/meta-data/instance-type                                                  t2.micro[root@ip-172-31-62-89 layers]#
[root@ip-172-31-62-89 layers]# free -m
              total        used        free      shared  buff/cache   available
Mem:            983          63         742           0         177         784
Swap:             0           0           0

Solution

Change the instance type to t2.small. Re-run the command, The normal json output appears. The exit status is 0.

[root@ip-172-31-62-89 layers]# aws lambda publish-layer-version --layer-name oracle-instant-client-layer --zip-file fileb://oracle-instant-client-layer.zip  --compatible-runtimes python3.7
{
    "LayerVersionArn": "arn:aws:lambda:us-east-1:999999999999:layer:oracle-instant-client-layer:2",
    "Description": "",
    "CreatedDate": "2020-08-02T21:03:38.787+0000",
    "LayerArn": "arn:aws:lambda:us-east-1:999999999999:layer:oracle-instant-client-layer",
    "Content": {
        "CodeSize": 51069060,
        "CodeSha256": "B1DGnA385aL50A8mrKoq1FOsIsEtMerbhdYCwd485YA=",
        "Location": "https://prod-04-2014-layers. etc."
    },
    "Version": 2,
    "CompatibleRuntimes": [
        "python3.7"
    ]
}
[root@ip-172-31-62-89 layers]# echo $?
0

Physical memory us 1991 MB.

[root@ip-172-31-62-89 layers]# curl -X GET http://169.254.169.254/latest/meta-data/instance-type
t2.small
[root@ip-172-31-62-89 layers]# free -m
              total        used        free      shared  buff/cache   available
Mem:           1991          63        1712           0         215        1788
Swap:             0           0           0

Conclusion

An AWS CLI on EC2 produced no output and exited silently. Investigation uncovered an out of memory condition, which was fixed by upgrading the instance type.

EC2 maximum number of volumes

Introduction

The documented AWS EC2 EBS volume attachment limit is 27. The attachment limit affects the maximum number of Oracle ASM disks.

EC2 volume limit

Amazon AWS documents a 27-volume attachment limit. I have found that a t2 type creation attempt with 28 EBS volumes will abort.

error.png

Attempting to create an m5 (Nitro)  with 28 or more volumes will hang.

creating

The attachment limit is 28, including network interfaces, volumes, and instance store volumes. EC2 instances with one network interface can have up to 27 volumes attached.

EC2 instances with more than 27 volumes

I am aware of t2 EC2 systems with as many as 44 volumes attached. I have no information about how this was done, and what the customer’s support expectations are. I would be concerned about how such a system would respond to a change of instance type.

Oracle Database

In planning an Oracle database installation, you may need file system mounts as well. /u01, for example. The underlying volume counts against the maximum attachment count.

In laying out an ASM setup, consider a simple design, with as few ASM disks and ASM disk groups as needed.

Conclusion

Based on the available public information, I recommend limiting the number of EBS volume attachments to 27. Oracle database administrators might want to simplify their ASM implementation.

AWS RDS Oracle parameter values

Introduction

Amazon AWS has set values to certain Oracle Database parameters. You can refer to this blog post if want to know ahead of time what the values will be before you create the instance.

Parameters that have values

Oracle Database parameters that AWS RDS has set to values are listed here. The values come from parameter group default.oracle-ee-19:

ParameterName ParameterValue
archive_lag_target 300
audit_file_dest /rdsdbdata/admin/{dbName}/adump
compatible 19.0.0
control_files /rdsdbdata/db/{dbName}_{DBUniqueNameSuffix}/controlfile/control-01.ctl
db_block_checking MEDIUM
db_create_file_dest /rdsdbdata/db
dbfips_140 FALSE
db_name {dbName}
db_recovery_file_dest_size 1073741824
db_unique_name {dbName}_{DBUniqueNameSuffix}
dg_broker_config_file1 /rdsdbdata/config/dr1{dbName}.dat
dg_broker_config_file2 /rdsdbdata/config/dr2{dbName}.dat
diagnostic_dest /rdsdbdata/log
enable_pluggable_database FALSE
filesystemio_options setall
heat_map OFF
job_queue_processes 50
local_listener (address=(protocol=tcp)(host=)(port={EndPointPort}))
log_archive_dest_1 location=”/rdsdbdata/db/{dbName}_{DBUniqueNameSuffix}/arch/redolog”, valid_for=(ALL_LOGFILES,ALL_ROLES)
log_archive_format -%s-%t-%r.arc
max_string_size STANDARD
memory_max_target IF({DBInstanceClassHugePagesDefault}, 0, {DBInstanceClassMemory*3/4})
memory_target IF({DBInstanceClassHugePagesDefault}, 0, {DBInstanceClassMemory*3/4})
open_cursors 300
pga_aggregate_target IF({DBInstanceClassHugePagesDefault}, {DBInstanceClassMemory*1/8}, 0)
processes LEAST({DBInstanceClassMemory/9868951}, 20000)
recyclebin OFF
sga_target IF({DBInstanceClassHugePagesDefault}, {DBInstanceClassMemory*3/4}, 0)
spfile /rdsdbbin/oracle/dbs/spfile{dbName}.ora
standby_file_management AUTO
undo_tablespace UNDO_T1
use_large_pages {DBInstanceClassHugePagesDefault}

Variables

Notice that some parameter values depend on variables.

The list of variables is:

DBInstanceClassHugePagesDefault

Depends on RDS size. RDS uses hugepages for RAM 16 GB and up.

DBInstanceClassMemory

The amount of RAM, in bytes, in the EC2 instance type that you select.

DBUniqueNameSuffix

Usually “_A”

EndPointPort

1521, for example

dbName

The value that you specify, or ORCL.

Actual memory-related parameter values

Here are some actual, observed memory-related parameter values:

Host memory
db.t3 instance size small medium large xlarge 2xlarge
RAM 2 GB 4 GB 8 GB 16 GB 32 GB
Parameter Name
memory_max_target 1328 MB 2768 MB 5670 MB 0 0
memory_target 1328 MB 2768 MB 5670 MB 0 0
pga_aggregate_target 0 0 0 2 GB 4 GB
processes 187 392 815 1663 3359
sga_target 0 0 0 12 GB 23 GB
use_large_pages FALSE FALSE FALSE ONLY ONLY

Generally, you should not set these parameters to your own values. You should not create parameter groups that set these values. Doing so defeats the simplifying design of RDS.

RDS disables hugepages, and memory management is AMM, for RAM size 8 GB and smallerl however, RDS enforces hugepages, and memory management is ASMM, for RAM size 16 GB and up. If you modify the instance size, the memory-related parameters get changed.

Files

Control files

Control files are not multiplexed. Example:

/rdsdbdata/db/ORCL_A/controlfile/control-01.ctl

Data files

Tablespaces are bigfile. Example datafile:

/rdsdbdata/db/ORCL_A/datafile/o1_mf_users_hbl3j3yn_.dbf

Log files

Logs are not multiplexed. Example logfile:

/rdsdbdata/db/ORCL_A/onlinelog/o1_mf_1_hbl3kbbx_.log

Example tempfile:

/rdsdbdata/db/ORCL_A/datafile/o1_mf_temp_hfdtzrr6_.tmp

Operations

Because parameters db_create_file_dest and db_unique_name are set, tablespace creation is simplified. This command:

create tablespace TSQ;

results in creating a datafile such as:

/rdsdbdata/db/ORCL_A/datafile/o1_mf_tsq_hffymmyr_.dbf

Services

Out of the box, RDS boots with two services, namely db_name and db_unique_name. For example, ORCL and ORCL_A.

There are at least two ways to create additional services. Grid infrastructure tool srvctl is not be available to manage services in RDS.

SID

Your db_name is also your SID, and it is registered with the listener as a service. Check your SID:

select sys_context('userenv','instance_name') from dual;

or

select instance_name from v$instance;
ORCL

db_unique_name

Check your db_unique_name:

show parameter db_unique_name;
NAME TYPE VALUE 
-------------- ------ ------ 
db_unique_name string ORCL_A

You could override the default db_unique_name by creating a parameter group with your new value.

Managing service_names

You could create a new AWS RDS parameter group and set service_names there. If you do that, you may find over time that you have created an unmanageable number of parameter groups.

Running dbms_service

You can create and start a service using dbms_service.

begin
dbms_service.create_service(
        service_name => 'ESB',
        network_name => 'ESB'       
    );
end;
/
begin
dbms_service.start_service(
        service_name => 'ESB'      
    );
end;
/

However, the service state would not survive a restart. To start the service automatically, you could try creating a database trigger.

create or replace trigger
system.start_svc
after startup on database
begin
dbms_service.start_service(
        service_name => 'ESB'      
    );    
exception when others then
    if sqlcode != -44305
    then
        raise;
    end if;
end;
/

However, you will get this error:

ORA-20900: RDS restricted DDL found: CREATE TRIGGER SYS.START_SVC

In case you set db_domain

Suppose, because of a tradition, you decide to set db_domain. You could create a new parameter group and set db_domain there.

pargrpdb

Then you could apply the parameter group to your RDS instance. Now you have two connection options:

host:port/orcl

or

host:port/orcl_a.its.gvu.edu

If you try:

host:port/orcl.its.gvu.edu

you get:

ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor

This means that all your connections need suffix “_a.its.gvu.edu”. If you head down this road, your environment is going to be cluttered with “_a.its.gvu.edu”. “_A” is for “_Appendix”, a vestigial organ that serves no current purpose, and can cause pain at unexpected times. If you don’t want so much clutter, do not set db_domain in RDS.

Services conclusion

Services were useful on on-prem deployments for managing or monitoring workload. Configuring services in AWS RDS is impractical.

I have these recommendations regarding services:

  • Do not set db_domain
  • Do not attempt to manage extra database services
  • Connect using host:port/sid

AWS RDS is not very service-friendly.

Conclusion

Amazon AWS RDS sets certain Oracle Database initialization parameters. Memory and file-related parameters depend on RDS variables, which depend on variables. Memory-related variables are adjusted proportional to the amount of EC2 RAM. RDS sets parameters that control file creation locations. RDS does not support the service functionality that you could be accustomed to with on-prem.

OUI on AIX Power in IBM cloud via SSH tunnel

By Brian Fitzgerald

Introduction

This is a worked example on how to Display Oracle Universal Server back to your Windows PC if you want to connect ssh through an additional server, such as a bastion. The destination operating system is AIX POWER in IBM Cloud. The tunnel host is Linux.

Pattern

The ssh tunnel connection follows this serverfault answer:

How to enable SSH X11 forwarding through additional server?

There are several ways to do this, the one I prefer is to forward the ssh port:

First, connect to machine B and forward [localPort] to C:22 through B

A$ ssh -L [localPort]:C:22 B

Next, connect to C from A through this newly-created tunnel using [localPort], forwarding X11

A$ ssh -X -p [localPort] localhost

Now we can run X11 programs on C and have them display on A

C$ xclock

[localPort] can be any port that you are not already listening to on A, I often use 2222 for simplicity.

Prerequisites

You should have a working ssh setup before beginning.  In this example, the private ssh key is saved on Windows as file “ibm_rsa”. Likewise, you will need a working X server, such a Xming.

In Oracle Cloud, create an AIX Power VM. Create a Linux VM to use as the intermediate host.

Tunnel account setup

On the Linux host:

[root@dal-vm ~]# groupadd tunl
[root@dal-vm ~]# useradd -g tunl tunl
[root@dal-vm ~]# cp -pr  ~/.ssh ~tunl/.ssh
[root@dal-vm ~]# chown -R tunl:tunl ~tunl/.ssh

Oracle account setup

On the AIX POWER host, enable X11 forwarding,

*******************************************************************************
*                                                                             *
*                                                                             *
*  Welcome to AIX Version 7.1!                                                *
*                                                                             *
*                                                                             *
*  Please see the README file in /usr/lpp/bos for information pertinent to    *
*  this release of the AIX Operating System.                                  *
*                                                                             *
*                                                                             *
*******************************************************************************
# bash
bash-4.3# ed /etc/ssh/sshd_config
3233
1,$s/#X11Forwarding no/X11Forwarding yes/g
w
3207
q
bash-4.3# stopsrc -s sshd
0513-044 The sshd Subsystem was requested to stop.
bash-4.3# startsrc -s sshd
0513-059 The sshd Subsystem has been started. Subsystem PID is 13107376.
bash-4.3#

Create the oracle account:

bash-4.3# mkgroup oinstall
bash-4.3# mkgroup dba
bash-4.3# useradd -g oinstall -G dba oracle
bash-4.3# mkdir ~oracle
bash-4.3# cp -pr ~/.ssh ~oracle/.ssh
bash-4.3# chown -R oracle:oinstall ~oracle
bash-4.3#

Create the tunnel

On Windows, connect to the Linux host with the needed tunneling arguments.

C:>ssh -i ibm_rsa -N -L 3333:52.117.58.66:22 tunl@169.61.227.202
The authenticity of host '169.61.227.202 (169.61.227.202)' can't be established.
ECDSA key fingerprint is SHA256:gCHZnnBtodihB75yPqIZ21Cbdq/+IAWbsCr4zRe5MTw.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '169.61.227.202' (ECDSA) to the list of known hosts.

Note that this is not an interactive session. No Linux prompt appears.

Log on to the AIX POWER oracle account

In Windows, set the DISPLAY variable. Log on to AIX as oracle via the local tunnel port:

C:>set DISPLAY=localhost:0.0

C:>ssh -i ibm_rsa -Y -p 3333 oracle@localhost
The authenticity of host '[localhost]:3333 ([::1]:3333)' can't be established.
RSA key fingerprint is SHA256:28Wh/Inx/YBDvPhIYN+VyEZ8b903cXtKzA83KEnv3bU.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '[localhost]:3333' (RSA) to the list of known hosts.
Warning: No xauth data; using fake authentication data for X11 forwarding.
Last login: Tue Nov 19 11:43:19 2019 on ssh from 169.61.227.202
*******************************************************************************
*                                                                             *
*                                                                             *
*  Welcome to AIX Version 7.1!                                                *
*                                                                             *
*                                                                             *
*  Please see the README file in /usr/lpp/bos for information pertinent to    *
*  this release of the AIX Operating System.                                  *
*                                                                             *
Last login: Tue Nov 19 11:43:19 2019 on ssh from 169.61.227.202
*******************************************************************************
*                                                                             *
*                                                                             *
*  Welcome to AIX Version 7.1!                                                *
*                                                                             *
*                                                                             *
*  Please see the README file in /usr/lpp/bos for information pertinent to    *
*  this release of the AIX Operating System.                                  *
*                                                                             *
*                                                                             *
*******************************************************************************
1356-364 /usr/bin/X11/xauth:  creating new authority file /home/oracle/.Xauthority

In AIX,  set LC_ALL. Optionally, test xterm. Start Oracle Universal installer:

-bash-4.3$ export LC_ALL=C
-bash-4.3$ xterm
-bash-4.3$ cd /opt/app/oracle/product/19.3.0/dbhome_1/
-bash-4.3$ unzip -q /opt/app/download/AIX.PPC64_193000_db_home.zip
-bash-4.3$ ./runInstaller

********************************************************************************

Your platform requires the root user to perform certain pre-installation
OS preparation.  The root user should run the shell script 'rootpre.sh' before
you proceed with Oracle installation. The rootpre.sh script can be found at:
/opt/app/oracle/product/19.3.0/dbhome_1/clone/rootpre.sh

Answer 'y' if root has run 'rootpre.sh' so you can proceed with Oracle
installation.
Answer 'n' to abort installation and then ask root to run 'rootpre.sh'.

********************************************************************************

Has 'rootpre.sh' been run by root in this machine? [y/n] (n)
y
Launching Oracle Database Setup Wizard...

oem

Conclusion

This has been a complete, worked example of starting Oracle Universal Installer, an X-Windows client, on an AIX POWER virtual machine in the IBM Cloud. The ssh session was established via a tunnel on a Linux virtual machine.

pagination and python 3.8 assignment expressions in AWS boto3

By Brian Fitzgerald

Introduction

AWS query operations return their results all at once, or in smaller, manageable pages. boto3 provides the programmer with to ways to manage the retrieval of paginated results.

Python version 3.8 introduces assignment expressions, a way to assign to variables within an expression using the notation := expr. PEP-572 explains how assignment expressions are useful in control statements, such as if and while, and in comprehensions. Assignment expressions can be used to improve coding style in pagination code.

describe

To query an Amazon AWS service, one calls an API using a procedure with a name beginning with “describe”, or sometimes “list” or “get”. The result is a dictionary with at least two elements, namely the payload, and ResponseMetadata. If more results are pending, NextToken will appear. The payload is your query result and consists of an array of objects. Examples of payload data include EC2 instance Reservations, EC2 EBS Volumes, and RDS DBInstances.

Using python API boto3, you can write:

import boto3
ec2 = boto3.client('ec2')
resp = { 'NextToken' : '' }
while 'NextToken' in resp:
    resp = ec2.describe_instances(
        NextToken = resp['NextToken']
    )
    for resv in resp['Reservations']:
        for inst in resv['Instances']:
            print ( inst['InstanceId'])

resp is a dict, and appears four times in the above code:

  • initialization with a dummy NextToken
  • returned from describe_instances
  • used to get describe_instances argument NextToken
  • used to get Reservations

The dictionary keys are Reservations, ResponseMetadata, and when needed, NextToken.

python 3.8 assignment expression

In python 3.8, we can assign to a variable, and then use the assignment as an expression. Specifically, we can assign resp, and access element Reservations.

import boto3
ec2 = boto3.client('ec2')
resp = { 'NextToken' : '' }
while 'NextToken' in resp:
    for resv in (resp := ec2.describe_instances(
        NextToken = resp['NextToken']
    ))['Reservations']:
        for inst in resv['Instances']:
            print ( inst['InstanceId'])

There is one fewer line. Some may find the revised code appealing.

paginator

AWS boto3 provides paginators, python iterators, that handle NextToken for you.

import boto3
ec2 = boto3.client('ec2')
pagr = ec2.get_paginator('describe_instances')
for page in pagr.paginate():
    for resv in page['Reservations']:
        for inst in resv['Instances']:
            print( inst['InstanceId'])

There are two fewer lines. NextToken does not appear in the code.

Paginate returns the same results as the prior “describe” type methods. The returned value is a dict, again with keys Reservations, ResponseMetadata, and when needed, NextToken. However, the returned NextToken has no role in the new code.

paginator relationship to API call

A debug trace of describe_instances() shows an underlying call to client.py, line 357, function _api_call().

(Pdb) where
  /usr/lib64/python3.7/bdb.py(585)run()
-> exec(cmd, globals, locals)
  (1)()
  /home/ec2-user/git/aws/insts.desc.3.7.py(8)()
-> NextToken = resp['NextToken']
  /usr/local/lib/python3.7/site-packages/botocore/client.py(357)_api_call()
-> return self._make_api_call(operation_name, kwargs)

Debug trace of paginate approach shows a call to the same point via paginate.py.

(Pdb) where
  /usr/lib64/python3.7/bdb.py(585)run()
-> exec(cmd, globals, locals)
  (1)()
  /home/ec2-user/git/aws/insts.page.3.7.py(6)()
-> for page in pagr.paginate():
  /usr/local/lib/python3.7/site-packages/botocore/paginate.py(255)__iter__()
-> response = self._make_request(current_kwargs)
  /usr/local/lib/python3.7/site-packages/botocore/paginate.py(332)_make_request()
-> return self._method(**current_kwargs)
  /usr/local/lib/python3.7/site-packages/botocore/client.py(357)_api_call()

You can see where paginate is handling NextToken for you. For example, this code:

NextToken = resp['NextToken']

is handled behind the scenes as:

> /usr/local/lib/python3.7/site-packages/botocore/paginate.py(303)__iter__()
-> previous_next_token = next_token
(Pdb) p next_token
{'NextToken': 'eyJ2IjoiMiIsImMiOiJ5eHV0K011N2crQlBBaFhoSWU2SUpad0c3V3VaUFBvKzBPbDRIWFAvaXJSb3poeDFDNks3TkxGMkU5R1UxRjk4UlVnNFViRzNjSUlWWXhMbHk3ejU1Qjd1ZGhERHNBVktCR1g0cW5RZk9FdStZckViM0NjOXljV1p0SWplckhkV2ZISkNvc0NXdjhnMXA4RVBMWDFiVzNkS3k1NW5CdlZmUlhWUEpzeUZNbnhMS3VzdEo4eHFIWHRYNytpcEdWbHJKMFRqTlNLQ3A0Rk9VaEZGckdBMTVOYU44WGhvYkYyZVBBYjRrMVVaYXNFTCIsInMiOiIxIn0='}

The describe and paginate approaches are functionally equivalent. Neither one has a performance advantage. Using paginate leads to cleaner code.

limiting the results (cloud side)

You can limit the number of items returned thus:

...
for page in pagr.paginate(PaginationConfig={'MaxItems': 250}):
...

In that case, the total number of items returned will be at most 250. The limiting is done on the cloud side. The items could be returned across more than one page.

limiting the results (client side)

Suppose you want all the ec2 instance types that have memory less than or equal to 8 GB. Two problems:

  1. AWS pricing has no filter for memory.
  2. The AWS pricing filter has no “less than” operator.

In that case, you need to retrieve your products, and apply additional filtering in client side code.

#!/home/ec2-user/sw/python/3.8/bin/python3.8

from boto3 import client
from pfilt import Pfilt
from json import dumps, loads


class ResultsLimitExceededError(Exception):
    def __init__(self, limit):
        fmt = format('results limit %s exceeded')
        msg = (fmt % limit)
        super(ResultsLimitExceededError, self).__init__(msg)


class Prices:

    @classmethod
    def instancetypes(cls):
        cli = client('pricing')
        pag = cli.get_paginator('get_products')

        max_products_in = 1000
        max_products_out = 100
        num_products_in = 0
        num_products_out = 0
        for page in pag.paginate(
                ServiceCode='AmazonEC2',
                Filters=Pfilt.filters,
                PaginationConfig={'MaxItems': max_products_in}
        ):
            print('page size %s' % len(page['PriceList']))
            for skitem in [loads(itm) for itm in page['PriceList']]:
                num_products_in += 1
                if (filtered_skitem := cls.client_filter(cls.enrich(skitem))) is not None:
                    if (num_products_out := num_products_out + 1) > max_products_out:
                        raise ResultsLimitExceededError(max_products_out)
                    cls.processitm(filtered_skitem)

        print('number of products in %s' % num_products_in)
        print('number of products out %s' % num_products_out)

    @classmethod
    def enrich(cls, skitem):
        eskitem = skitem
        product = skitem['product']
        memGB = float(product['attributes']['memory'].split()[0].replace(',', ''))
        eskitem['memGB'] = memGB
        return eskitem

    @classmethod
    def client_filter(cls, eskitem):
        maxmem = 8
        return eskitem if eskitem['memGB'] <= maxmem else None

    @classmethod
    def processitm(cls, skitem):
        pass


if __name__ == '__main__':
    Prices.instancetypes()

In this case, we set a cloud-side limit of 1000 records, and a client-side limit of 100 records.

Example output:

$ ./prices.pag.py
page size 100
page size 100
page size 39
number of products in 239
number of products out 33

You could manage your client-side memory by reducing the page size:

PaginationConfig={'MaxItems': max_products_in, 'PageSize': 50}

Setting PageSize does not affect the results. The output, then, is:

page size 50
page size 50
page size 50
page size 50
page size 39
number of products in 239
number of products out 33

To demonstrate the exception, we can change:

max_products_out = 20

in that case, we get:

$ ./prices.pag.py
Traceback (most recent call last):
  File "./prices.pag.py", line 62, in 
    Prices.instancetypes()
  File "./prices.pag.py", line 36, in instancetypes
    raise ResultsLimitExceededError(max_products_out)
__main__.ResultsLimitExceededError: results limit 20 exceeded

We used the new Python 3.8 assignment expression in two places:

if (fskitem := cls.client_filter(cls.enrich(skitem))) is not None:
    if (num_products_out := num_products_out + 1) > max_products_out:

We assign fskitem and then test it. Likewise, we assign num_products_out and test it.

pagination availability

Not all AWS operations can be paginated — it depends on the service. If an operation cannot be paginated, it will not return NextToken. If an operation cannot be paginated, you may have issues managing the results. You can use this script to find out if an operation can be paginated.

#!/usr/bin/python

from boto3 import client
from argparse import ArgumentParser


class CanPag:
    args = None

    @classmethod
    def prs(cls):
        ap = ArgumentParser(
            description='Check whether an operation can paginate'
        )
        ap.add_argument(
            '--service', '-s', required=True,
            help='AWS service name (ec2, s3, pricing, etc.)'
        )
        ap.add_argument(
            '--operation', '-o', required=True,
            help='AWS service operation (describe_instances, etc.)'
        )

        cls.args = ap.parse_args()

    @classmethod
    def canpage(cls):
        cli = client(cls.args.service)
        cp = cli.can_paginate(cls.args.operation)
        print(
            '%s %s %s paginate.' % (
                cls.args.service,
                cls.args.operation,
                'can' if cp else 'cannot')
        )


if __name__ == '__main__':
    CanPag.prs()
    CanPag.canpage()

Examples:

$ ./can.paginate.py -s ec2 -o describe_instances
ec2 describe_instances can paginate.
$ ./can.paginate.py -s ec2 -o describe_volumes
ec2 describe_volumes can paginate.
$ ./can.paginate.py -s ec2 -o describe_images
ec2 describe_images cannot paginate.
$ ./can.paginate.py -s pricing -o get_products
pricing get_products can paginate.

Conclusion

I wrote this article for two reasons: To identify a good paginator coding practice, and to try out the new Python 3.8 assignment expression.

AWS queries are often returned in chunks, or pages. You could write your own code to manage the retrieval, but you are better off using the provided paginator. You can configure the paginator as to and MaxItems and PageSize. Use can_paginate() to find out which operations can be paginated.

Python version 3.8 assignment expression was demonstrated. Three cases were presented. In each case, the pattern was “assign and test”. The assigned variables in the examples were resp, filtered_skitem, and num_products_out. The assigned value was needed elsewhere in the routine.

Data Guard broker 19c configuration export

By Brian Fitzgerald

Introduction

Oracle Database 19c Data Guard broker has a new export/import feature. You can use export to backup your Data Guard configuration and you can use import to recreate your configuration.

Export

Issue a command such as:

DGMGRL> export configuration to 'dg.xml';
Succeeded.

Find your configuration file in diagnostic_dest

/u01/app/oracle/diag/rdbms/prim/PRIM/trace/dg.xml

You cannot specify a directory or a relative path.

DGMGRL> export configuration to '/home/oracle/dg.xml';
ORA-16540: invalid argument
ORA-06512: at "SYS.DBMS_DRS", line 1947
ORA-06512: at line 1

DGMGRL> export configuration to '../trace/dg.xml';
ORA-16540: invalid argument
ORA-06512: at "SYS.DBMS_DRS", line 1947
ORA-06512: at line 1

dgmgrl will not overwrite an existing file

DGMGRL> export configuration to 'dg.xml';
ORA-16571: Oracle Data Guard configuration file creation failure

You may export at the standby. The export file appears in the standby trace directory.

/u01/app/oracle/diag/rdbms/stby/STBY/trace/dg.xml

Contents

Refer, for example, to this link dg.xml. The export file contains:

  • Identity and role of members
  • Default, desired, and current state, including error number and timestamp
  • System and member configuration parameters

Some parameters that are at the default value and have never been modified are not exported.

Only the error number is exported. The error text depends on localization.

[oracle@ip-172-31-88-93 ~]$ export NLS_LANG=French
[oracle@ip-172-31-88-93 ~]$ dgmgrl sys/Zystm.22@PRIM
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Ven. Oct. 18 18:49:58 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Bienvenue dans DGMGRL, tapez "help" pour obtenir des informations.
Connecté à "PRIM"
Connecté en tant que SYSDBA.
DGMGRL> show configuration

Configuration - ORCL_CONFIG

  Mode de protection : MaxPerformance
  Membres :
  PRIM - Base de données principale
    Erreur : ORA-16778: erreur du Redo Transport Service pour des membres

    STBY - Base de données de secours physique
      Erreur : ORA-12543: TNS : hôte de destination impossible à atteindre

Fonction Fast-Start Failover :  Disabled

Statut de configuration :
ERROR   (statut mis à jour il y a 34 secondes)

Import

Here is how to use import.

If there is a configuration, disable and remove it. All members must be accessible.

DGMGRL> disable configuration
Disabled.
DGMGRL> remove configuration
Removed configuration

Import and enable the configuration

DGMGRL> import configuration from 'dg.xml';
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.
DGMGRL> enable configuration
Enabled.

Data Guard broker will not import over an existing configuration

DGMGRL> import configuration from 'dg.xml';
ORA-16504: The Oracle Data Guard broker configuration already exists.

Result:

DGMGRL> show configuration

Configuration - ORCL_CONFIG

  Protection Mode: MaxPerformance
  Members:
  PRIM - Primary database
    STBY - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 4 seconds ago)

You can change the export file before reimporting.

Before:

DGMGRL> show database verbose PRIM NetTimeout
  NetTimeout = '30'

File:

tag

Result:

DGMGRL> remove configuration
Removed configuration
DGMGRL> import configuration from 'dg.xml';
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.
DGMGRL> show database verbose PRIM NetTimeout
  NetTimeout = '10'

You cannot split the Data Guard xml file into pieces and import them separately.

DGMGRL> import configuration from 'prim.xml';
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.
DGMGRL> import configuration from 'stby.xml';
ORA-16504: The Oracle Data Guard broker configuration already exists.
DGMGRL> 

You must import to the primary. If you attempt to import to the standby, this message will appear:

DGMGRL> import configuration from 'dg.xml';
ORA-16501: The Oracle Data Guard broker operation failed.

The trace file offers no clue about what could be the problem.

2019-10-18T11:19:37.866+00:00
IMPORT METADATA FROM /u01/app/oracle/diag/rdbms/stby/STBY/trace/dg.xml
ORA-16501: The Data Guard broker failed to import metadata from /u01/app/oracle/diag/rdbms/stby/STBY/trace/dg.xml.
IMPORT METADATA FROM /u01/app/oracle/diag/rdbms/stby/STBY/trace/dg.xml completed with error ORA-16501

Management of an unreachable member

If you remove a Data Guard configuration while a member is down, you will have an issue later if you try to import. The following administrative sequence that could lead up to the trouble:

DGMGRL> export configuration to 'dg.xml';
Succeeded.

Now, suppose the standby is down

DGMGRL> validate database STBY
Error: ORA-1034: ORACLE not available
Error: ORA-16625: cannot reach member "STBY"
DGMGRL> disable configuration
Disabled.
DGMGRL> remove configuration 
Warning: ORA-16620: one or more members could not be reached for a remove operation Removed configuration
DGMGRL> import configuration from 'dg.xml';
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.

Now, if you enable even while the standby is up:

DGMGRL> enable configuration
Enabled.

Error appears

DGMGRL> show configuration

Configuration - ORCL_CONFIG

  Protection Mode: MaxPerformance
  Members:
  PRIM - Primary database
    STBY - Physical standby database (disabled)
      ORA-16904: Member may be a primary database or is receiving redo data from another primary database.

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 28 seconds ago)

Solution: On the standby disable the broker, remove the Data Guard Broker configuration files, and reenable the broker.

SQL> alter system set dg_broker_start=false;

System altered.
[grid@ip-172-31-91-148 ~]$ asmcmd rm +DATA01/STBY/dr1orcl.dat
[grid@ip-172-31-91-148 ~]$ asmcmd rm +RECO01/STBY/dr2orcl.dat
SQL> alter system set dg_broker_start=true;

System altered.

Then, on the primary, disable, remove, import, and enable the configuration.

DGMGRL> disable configuration
Disabled.
DGMGRL> remove configuration
Warning: ORA-16620: one or more members could not be reached for a remove operation

Removed configuration
DGMGRL> import configuration from 'dg.xml';
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - ORCL_CONFIG

  Protection Mode: MaxPerformance
  Members:
  PRIM - Primary database
    STBY - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 34 seconds ago)

The problem scenario described here is not unique to import. The issue can occur any time a configuration is deleted while members are down.

Conclusion

dgmgrl export and import are useful for backing up and restoring the Data Guard configuration.

listener status UNKNOWN or BLOCKED

By Brian Fitzgerald

Introduction

By trial and error, I identify what Oracle Database instance states correspond to listener status READY, UNKNOWN, and BLOCKED.

Details

I have recorded the listener status for various database states. In some cases, the listener was reloaded or restarted.

Database open READ WRITE:

Service "EQTRD" has 2 instance(s).
  Instance "EQTRD", status UNKNOWN, has 1 handler(s) for this service...
  Instance "EQTRD", status READY, has 1 handler(s) for this service...

Shutdown the database

Service "EQTRD" has 1 instance(s).
  Instance "EQTRD", status UNKNOWN, has 1 handler(s) for this service...

Delete the static listener and reload the listener.

[none]

Startup nomount

Service "EQTRD" has 1 instance(s).
  Instance "EQTRD", status BLOCKED, has 1 handler(s) for this service...

Reload

Service "EQTRD" has 1 instance(s).
  Instance "EQTRD", status BLOCKED, has 1 handler(s) for this service...

Stop and start the listener.

Service "EQTRD" has 1 instance(s).
  Instance "EQTRD", status BLOCKED, has 1 handler(s) for this service...

Note that reloading or restarting the listener does not clear a BLOCKED state.

Mount

Service "EQTRD" has 1 instance(s).
  Instance "EQTRD", status READY, has 1 handler(s) for this service...

Startup force nomount

Service "EQTRD" has 1 instance(s).
  Instance "EQTRD", status BLOCKED, has 1 handler(s) for this service...

Re-enable the static listener

Service "EQTRD" has 2 instance(s).
  Instance "EQTRD", status UNKNOWN, has 1 handler(s) for this service...
  Instance "EQTRD", status BLOCKED, has 1 handler(s) for this service...

Mount

Service "EQTRD" has 2 instance(s).
  Instance "EQTRD", status UNKNOWN, has 1 handler(s) for this service...
  Instance "EQTRD", status READY, has 1 handler(s) for this service...

Restricted session

Service "XIENT" has 1 instance(s).
  Instance "XIENT", status RESTRICTED, has 1 handler(s) for this service...

Summary

Here is a summary of listener status and interpretation.

status interpretation
BLOCKED nomount
UNKNOWN static listener
READY mounted or open. Dymamically registered
RESTRICTED restricted session

 

Data Guard broker 19c edit database set parameter feature is unstable

By Brian Fitzgerald

Introduction

New in Oracle Database 19c, you can set a database initialization parameter directly from dgmgrl, the Data Guard broker command-line interface. The documentation is not clear or the feature is unstable.

Edit database set parameter

The basic functionality works as documented.

DGMGRL> edit database PRIM set parameter pga_aggregate_target='752m';
Parameter "pga_aggregate_target" updated

However:

DGMGRL> edit database PRIM set parameter pga_aggregate_target='753m' sid='PRIM';
edit database PRIM set parameter pga_aggregate_target='753m' sid='PRIM';
                                                                ^
Syntax error before or at "="

DGMGRL> edit database PRIM set parameter sga_target=2256m scope = both;
edit database PRIM set parameter sga_target=2256m scope = both;
                                                        ^
Syntax error before or at "="

Options SID and SCOPE are documented but are not working for me.

Edit database reset parameter

You can use edit database to reset a database initialization parameter. Suppose spfile initially contains:

*.use_large_pages='ONLY'
PRIM.use_large_pages='ONLY'

Now reset.

DGMGRL> edit database PRIM reset parameter use_large_pages;
ORA-16675: database instance restart required for property value modification to take effect

Parameter "use_large_pages" reset
DGMGRL>

This appears in the alert log

2019-10-17T19:43:49.034727+00:00
ALTER SYSTEM RESET use_large_pages SCOPE=SPFILE;

Here is what is remains:

PRIM.use_large_pages='ONLY'

The “sid=’*'” entry is gone. The dgmgrl reset behavior, then, is the same as the alter system syntax with an assumed “sid=’*'”.

The dgmgrl edit database reset parameter command has no sid option.

DGMGRL> edit database PRIM reset parameter use_large_pages sid = 'PRIM';
edit database PRIM reset parameter use_large_pages sid = 'PRIM';
                                                   ^
Syntax error before or at "sid"

Conclusion

The dgmgrl edit database set and reset parameter commands are incomplete or have unexpected behavior today, or I misunderstand them. I will not use them for now.