Amazon QLDB and the Missing Command Line Client

Amazon Quantum Ledger Database is is a fully managed ledger database which tracks all changes of user data and maintains a verifiable history of changes over time. It was announced at AWS re:Invent 2018 and now available in five AWS regions: US East (N. Virginia), US East (Ohio), US West (Oregon), Europe (Ireland), and Asia Pacific (Tokyo).

You may ask why you would like to use QLDB (a ledger database) instead of using your traditional (more...)

Sample AWS Lambda Function to Monitor Oracle Database

I wrote a very simple AWS Lambda function to demonstrate how to connect an Oracle database, gather the tablespace usage information, and send these metrics to CloudWatch. First, I wrote this lambda function in Python and then I had to re-write it in Java. As you may know, you need to use cx_oracle module to connect Oracle Databases with Python. This extension module requires some libraries which are shipped by Oracle Database Client (oh God! (more...)

Costs in Multi-Cloud

Over the last couple of years we have seen growing references to multi-cloud. That is to say, people are recognizing that organisations, particularly larger ones are ending up with cloud services for many different vendors. This at-least in part has come from where departments within an organization can by meaningful resources within their local budgets.

Whilst there is a competitive benefit of the recent partnership agreement between Microsoft and Oracle given the market margin AWS (more...)

DevOps for Oracle DBA

DevOps is natural evolution for Oracle database administrators or sysadmins of any kind. The key to remain relevant in the industry is to embrace DevOps these days and in near future.

The good news is that if you are an Oracle DBA, you already have the solid foundation. You have worked with the enterprise, world class database system and are aware of high availability, disaster recovery, performance optimization, and troubleshooting. Having said that, there is (more...)

S3 – fatal error: An error occurred (404) when calling the HeadObject operation

Make sure to use –recursive parameter.

[desktop: test]:${PWD}> aws s3 cp s3://demo-beta/dw/user/dt=2019-07-30/ /tmp/dw/
fatal error: An error occurred (404) when calling the HeadObject operation: Key "dw/user/dt=2019-07-30/" does not exist

[desktop: test]:${PWD}> aws s3 cp s3://demo-beta/dw/user/dt=2019-07-30/ /tmp/dw/ --recursive
download: s3://demo-beta/dw/user/dt=2019-07-30/part-00002-fd866c-238-489-a44-739f1d04-c000.snappy.parquet to ../../../tmp/dw/part-00002-fd866c-238-489-a44-739f1d04-c000.snappy.parquet

From Documentation –

–recursive (boolean) Command is performed on all files or objects under the specified directory or prefix.

Expanding array to multiple rows – Athena

A single row in Athena table is stored as —

select id, course, date from demo.course_tab where id='1234567892'

 	id	course	                                                                 date
 1234567892	[95c3c1bc5873, 2e345b2eb678, 027b02599f4a, 8695a580520b, 5d453355d415, cdcc7682070b]	2019-06-13

The datatype for course column is array(string). Now, how can you get the output in below format –

 	id	        course          date

1	1234567892	95c3c1bc5873	2019-06-13
2	1234567892	2e345b2eb678	2019-06-13
3	1234567892	027b02599f4a	2019-06-13
4	1234567892	8695a580520b	2019-06-13
5	1234567892	5d453355d415	2019-06-13
6	1234567892	cdcc7682070b	2019-06-13

(more...)

Identifying PostgreSQL Bloat and fixing it without downtime

Original Post can be viewed at Identifying PostgreSQL Bloat and fixing it without downtime

MVCC (Multi-Version Concurrency Control) feature allows databases to provide concurrent access to data. This allows each SQL statement to see a snapshot of data as it was some time ago, regardless of the current state of the underlying data. This prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same…

AskDba.org Weblog

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...)

S3 bucket size using aws cli

To get the total objects count and total size for folder within S3 bucket recursively –


aws s3 ls --summarize --human-readable --recursive s3://content-dev/mywork/dumb | grep 'Total'

Total Objects: 1922
   Total Size: 297.4 GiB

AWS: Accessing S3 buckets from the internet and from ec2 instances (2)

After accessing a S3 bucket from an ec2 instance by adding an IAM role to my EC2 instance, i tried to get the access from an ec2 instance by using a AWS endpoint.

First step: Launch an ec2 instance within your VPC and try to access your S3 bucket:

[ec2-user@ip-172-31-30-93 ~]$ wget https://s3-eu-west-1.amazonaws.com/my.webtest/website.json
--2019-02-02 18:29:28--  https://s3-eu-west-1.amazonaws.com/my.webtest/website.json
Auflösen des Hostnamen »s3-eu-west-1.amazonaws.com (s3-eu-west-1.amazonaws.com)«... 52.218. (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...)

AWS: overview of all postings

After nearly an half year of looking into AWS, here a summary of all postings related to that topic:
ECS & S3 (Computing & Storage):

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...)

Saving AWS RDS and Redshift Billing Costs using Reserved Instances

Original Post can be viewed at Saving AWS RDS and Redshift Billing Costs using Reserved Instances

In this post, we would understand what are reserved instances, when do you need them and various ways to monitor them in your environment. As more enterprises are embracing AWS cloud technologies, we need to understand AWS’s breadth of services and pricing options to right size our compute resources and run efficient infrastructure. Cost optimization…

AskDba.org Weblog

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...)

AWS: Accessing S3 buckets from the internet and from ec2 instances

After reading about endpoints for AWS S3 i was wondering how i can use this feature.

First step was to create a bucket and just tried to access this bucket over the internet:

$ aws s3api create-bucket --bucket my.webtest --region eu-west-1 --create-bucket-configuration LocationConstraint=eu-west-1
{
    "Location": "http://my.webtest.s3.amazonaws.com/"
}
And the i put this location into my browser:


This is like expected, because i did not allow public access for this (more...)

AWS: IAM & security – Best practices: Using a non-root user

After my successful solutions architect practice i knew that i had to take care of my shortcomings in security.
So i decided to visit the IAM (Identity and Access Managemen) of AWS:

  • Delete your root access keys
    I created them for using aws cli (installation or creating a static webpage)
  • Activate MFA on your root account
    No - i will not use multi factor authentication for my playground.
  • Create individual IAM users
    I (more...)

AWS: What services are free of charge? How to control your costs…(part 3)

After looking into my bill (see post 1 and post 2) i was keen, how fine granular the cost statistics are inside the aws web console. So i moved to the billing dashboard and to the cost explorer:
This provides you with the following dashboard:
You can play around with the settings to get for example something like this:




AWS Billing: Set an alarm to a cost threshold

Knowing about some details about the costs inside aws and some of the services (especially, that Docker, VPNs and Kubernetes are not included in the free trials)

i set an alarm on my account, to get informed about new costs:

So move to "Cloudwatch" and there you have to choose "Alarms":

 Then "Create Alarm":
 And click on "select (more...)