Understanding Nested Lists Dictionaries of JSON in Python and AWS CLI


After lots of hair pulling, bouts of frustration, I was able to grasp this nested list and dictionary thingie in JSON output of AWS cli commands such as describe-db-instances and others. If you run the describe-db-instances for rds or describe-instances for ec2, you get a huge pile of JSON mumbo-jumpo with all those curly and square brackets studded with colons and commas. The output is heavily nested.


For example, if you do :

aws rds (more...)

AWS CloudFormation Linter Installation and Quick Introduction

AWS Cloudformation is an evolving managed service which facilitates infrastructure as a code in the cloud. What it means is that you can create AWS resources like EC2 instances, S3 buckets and many more just by writing code instead of using GUI console.



For instances, if you want to create 100 EC2 instances for production, and then later you have to create same for development and for testing and then may be later on, you (more...)

Step by Step Troubleshooting and Validation of AWS CloudFormation Yaml template

CloudFormation (CFN) is infrastructure as a code service of AWS. You just tell CFN your desired state of resources and it creates them in order while resolving dependencies. You mention these resources in a file called as template which can be written in YAML or JSON. YAML being bit more user readable, is widely used now. YAML is great but you have to be aware of its indentation.


Following is a step by step troubleshooting (more...)

When AWS SCT Unable to Connect to MySQL in RDS or EC2

AWS Schema Conversion Tool (SCT) is one of the must tool for a successful migration of databases to AWS RDS.

If you are trying to connect to MySQL hosted on EC2 instance or RDS and unable to make the connection despite of setting the security groups correctly and making sure that port, hostname, username and password are right, then first check the log of SCT.




2019-03-22 19:40:16.866 [   1]     GENERAL INFO    global_settings:
(more...)

Monitoring Database in AWS Aurora After Migrating from Oracle to PostgreSQL

Suppose you have an Oracle database on-premise, which you have now moved over to AWS Cloud in AWS Aurora PostgreSQL. 
For your Oracle database, you have been using v$ views to monitor your runtime performance of instance, long running operations, top SQLs from ASH, blocking etc. How do you continue doing that when you migrate your database to cloud especially in AWS Aurora based PostgreSQL?

Well, PostgreSQL provides statistics collection views, which is a (more...)

AWS Support Knowledge Center

In addition to AWS documentation and blogs, one of the best resource is AWS Knowledge Center. It contains frequently asked questions from AWS customers so this resource contains real world problems with their solution.


AWS Knowledge Center contains FAQ from almost all of the AWS services. For example, I was researching about if there was a way to restore or recover a terminated EC2 instance in case I didn't have any backups or AMIs, and (more...)

Updating Oracle Opatch with AWS Systems Manager Run Command at Scale

AWS Systems Manager (SSM) is a managed service by AWS, which is used to configure and manage EC2 instances, other AWS resources, and on-premise VMs/servers at scale. SSM frees you up from having ssh or bastion host access to the remote resources.


Pre-requisites of SSM:

The managed instances need to have SSM agent running.
The managed instances need to be assigned an IAM role with policy AmazonEC2RoleforSSM.
The managed insatnces need to have a meaningful (more...)

awk -F option

I often need to extract subset of data from one command so that it could be used in another during the DBA tasks performed through shell scripts.

One such requirement is that when you need to feed the name of ASM disks to the querydisks command.


For example, if you want to see the mapping of ASM disks to the devices at OS level, you need to run following command:

oracleasm querydisk -d

Now in (more...)

Step by Step Installation of PostgreSQL 11 on Oracle Linux 7

This is quick spit-out of commands, I used on my test virtual machine to install PostgreSQL 11 on Oracle Enterprise Linux 7.



[root@docker ~]# curl -O https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-oraclelinux11-11-2.noarch.rpm
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  4952  100  4952    (more...)

Quickly Download Oracle Software From My Oracle Support

In order to quickly download Oracle software from My Oracle Support (MOS) to your server or local host, you must have curl or wget installed.

Check these by using:

[oracle@test working]$ which curl
/usr/bin/curl
[oracle@test working]$ which wget
/usr/bin/wget




Now use following command from the operating system prompt to download orachk, opatch, any Oracle patch or any other software by using your MOS credentials.

Right click on the software link and copy the link e. (more...)

Goldengate ERROR OGG-02037 Failed to retrieve the name of a missing Oracle redo log.

One extract got abended and wasn't able to start in Oracle Goldengate Version 12.2.0.1.160517 23194417_FBO. The redologs were present but extract was still abended and threw following error in the report file.

Error:

ERROR OGG-02037 Failed to retrieve the name of a missing Oracle redo log.

Solution:




The solution for this error is to unregister, register and then start the extract as follows:


GGSCI (test) 6> unregister extract ext database 2018-11-07 17:07:03 (more...)

Graceful Stop of Goldengate Extract

It's always a good idea to stop extracts after checking if there is any long running transaction in the database being captured. Failing to do so might later result in hung or unstable processes.





Use following command to check the extract:

GGSCI (test) 3> send e* status

Sending STATUS request to EXTRACT ext ...


EXTRACT ext (PID 16649)
Current status: In recovery[1]: Processing data with empty data queue

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

testsrv:/u05/ggate> grep -i bound dirrpt/ext.rpt
(more...)

Step by Step: Ansible Role To Setup Oracle ACFS On Multiple Nodes

This post contains step by step instructions for creating an Ansible role acfssetup to setup Oracle ASM Cluster Filesystem (ACFS) on multiple nodes of a cluster. This assumes that Grid Infrastructure 12.1.0.2.0 is already installed on the nodes, and ASM is working fine. This also assumes that there already is Ansible installed on some controller host with ssh equivalency setup between root and Oracle users.




Step 1: Create directory structure for (more...)

OPS$Oracle user after Wallet Creation in Oracle 12c


----- In Oracle 12.1.0.2, created the wallet by using below commands:

TEST$ orapki wallet create -wallet "/u01/app/oracle/admin/TEST/wallet" -pwd ****  -auto_login_local
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.


TEST$ mkstore -wrl "/u01/app/oracle/admin/TEST/wallet" -createCredential TEST2 sys ********
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
(more...)

Network Slowness Caused Database Contention That Caused Goldengate Lag

I got paged for a goldengate extract lagging behind. Checked the extract configuration and it was normal extract and it seemed stuck without giving any error in the ggserr.log or anywhere else. It wasn't abended either and was in running state.


Tried stopping and restating it, but still it remained in running state while doing nothing and lag was increasing. So the issue was clearly outside of goldengate. Checked the database by starting from (more...)

Log Buffer #546: A Carnival Of The Vanities For DBAs

This Log Buffer Edition covers Cloud, Oracle, and PostgreSQL.
Cloud:
Google Maps platform now integrated with the GCP Console
Getting more value from your Stackdriver logs with structured data
Improving application availability with Alias IPs, now with hot standby
Performing a large-scale principal component analysis faster using Amazon SageMaker
Optimized TensorFlow 1.8 now available in the AWS: deep learning AMIs to accelerate training on Amazon EC2 C5 and P3 instances


Oracle:

How to find the UUID of a device in Linux for Oracle ASM

UUID stands for Universally Unique Identifier. I use UUID for my disk device, when I need to create and add disks for Oracle ASM, as UUID is independet of device name or mountpoint. So its always a good idea to include UUID of device in the fstab file in Linux.

So here is how to find the UUID of a device in Linux for Oracle ASM:




[root@bastion ~]$ ls -l /dev/disk/by-uuid
lrwxrwxrwx 1 root root (more...)

Relocate Goldengate Processes to Other Node with agctl

Oracle Grid Infrastructure Agents can be used to manage Oracle Goldengate through Oracle GI. agctl is the utility to add, modify and relocate the goldengate. These Oracle GI agents can also be used with other products like weblogic, mysql etc. 


Frits has a good article about installation and general commands regarding GI agents for a non-clustered environment.

Following is the command to relocate Goldengate processes to other node with agctl. 


[gi@hostname ~]$ agctl (more...)

Oracle DBAs and GDPR

The General Data Protection Regulation (GDPR) (Regulation (EU) 2016/679) is a regulation by which the European Parliament, the Council of the European Union and the European Commission intend to strengthen and unify data protection for all individuals within the European Union (EU).


To bring Oracle database to align with GDPR directive, we have to encrypt all the databases and files on disk, aka encryption at rest (when data is stored). We also have to encrypt (more...)

AWS Pricing Made Easy By Simple Monthly Calculator

With ever changing pricing model and services, its hard to keep track of AWS costing.





If you want to check how much would it cost to have a certain AWS service, tailored to your requirement then use the following Simply Monthly Calculator from AWS.

AWS Price Calculator.