Pakistan's First Oracle Blog

Subscribe to Pakistan's First Oracle Blog feed
Blog By Fahd Mirza Chughtai
Updated: 18 hours 5 min ago

Cloud Vanity: A Weekly Carnival of AWS, Azure, GCP, and More - Edition 5

Thu, 2021-06-10 21:09

 Welcome to the next edition of weekly Cloud Vanity. As usual, this edition casts light on multiple cloud providers and what's happening in their sphere. From the mega players to the small fish on the ocean, it has covered it all. Enjoy!!!

AWS:

Reducing risk is the fundamental reason organizations invest in cybersecurity. The threat landscape grows and evolves, creating the need for a proactive, continual approach to building and protecting your security posture. Even with expanding budgets, the number of organizations reporting serious cyber incidents and data breaches is rising.

Streaming data presents a unique set of design and architectural challenges for developers. By definition, streaming data is not bounded, having no clear beginning or end. It can be generated by millions of separate producers, such as Internet of Things (IoT) devices or mobile applications. Additionally, streaming data applications must frequently process and analyze this data with minimal latency.

This post presents a solution using AWS Systems Manager State Manager that automates the process of keeping RDS instances in a start or stop state.

Over the last few years, Machine Learning (ML) has proven its worth in helping organizations increase efficiency and foster innovation. 

GCP:

In recent years, the grocery industry has had to shift to facilitate a wider variety of checkout journeys for customers. This has meant ensuring a richer transaction mix, including mobile shopping, online shopping, in-store checkout, cashierless checkout or any combination thereof like buy online, pickup in store (BOPIS).  

At Google I/O this year, we introduced Vertex AI to bring together all our ML offerings into a single environment that lets you build and manage the lifecycle of ML projects. 

Dataflow pipelines and Pub/Sub are the perfect services for this. All we need to do is write our components on top of the Apache Beam sdk, and they’ll have the benefit of distributed, resilient and scalable compute.

In a recent Gartner survey of public cloud users, 81% of respondents said they are working with two or more providers. And as well you should! It’s completely reasonable to use the capabilities from multiple cloud providers to achieve your desired business outcomes. 

Azure:

Generators at datacenters, most often powered by petroleum-based diesel, play a key role in delivering reliable backup power. Each of these generators is used for no more than a few hours a year or less at our datacenter sites, most often for routine maintenance or for backup power during a grid outage. 

5 reasons to attend the Azure Hybrid and Multicloud Digital Event

For over three years, I have had the privilege of leading the SAP solutions on Azure business at Microsoft and of partnering with outstanding leaders at SAP and with many of our global partners to ensure that our joint customers run one of their most critical business assets safely and reliably in the cloud. 

There are many factors that can affect critical environment (CE) infrastructure availability—the reliability of the infrastructure building blocks, the controls during the datacenter construction stage, effective health monitoring and event detection schemes, a robust maintenance program, and operational excellence to ensure that every action is taken with careful consideration of related risk implications.

Others:

Anyone who has even a passing interest in cryptocurrency has probably heard the word ‘blockchain’ branded about. And no doubt many of those who know the term also know that blockchain technology is behind Bitcoin and many other cryptocurrencies.

Alibaba Cloud Log Service (SLS) cooperates with RDS to launch the RDS SQL audit function, which delivers RDS SQL audit logs to SLS in real time. SLS provides real-time query, visual analysis, alarm, and other functionalities.

How AI Automation is Making a First-of-its-Kind, Crewless Transoceanic Ship Possible

Enterprise organizations have faced a compendium of challenges, but today it seems like the focus is on three things: speed, speed, and more speed. It is all about time to value and application velocity—getting applications delivered and then staying agile to evolve the application as needs arise.

Like many DevOps principles, shift-left once had specific meaning that has become more generalized over time. Shift-left is commonly associated with application testing – automating application tests and integrating them into earlier phases of the application lifecycle where issues can be identified and remediated earlier (and often more quickly and cheaply).

Categories: DBA Blogs

Cloud Vanity: A Weekly Carnival of AWS, Azure, GCP, and More - Edition 4

Thu, 2021-06-03 19:31

 Welcome to the next edition of weekly Cloud Vanity. Foundation of any cloud matters. Cloud is and always will be a distributed hybrid phenomenon. That is why architecting, developing, and operating a hybrid mix of workload require stable, scalable and reliable cloud technologies. This edition discusses few of them from across different clouds out there.


AWS:

AWS SAM or Serverless Application Model is an open source framework that you can use to develop, build and deploy your serverless applications.

Pluralsight, Inc., the technology workforce development company, today announced that it has entered into a definitive agreement to acquire A Cloud Guru (ACG).

AWS Lambda Extensions are a new way to integrate your favorite operational tools for monitoring, observability, security, and governance with AWS Lambda. Starting today, extensions are generally available with new performance improvements and an expanded set of partners including Imperva, Instana, Sentry, Site24x7, and the AWS Distro for OpenTelemetry.

Amazon SQS is a fully managed message queuing service that enables you to decouple and scale microservices, distributed systems, and serverless applications. 

Let’s say your Python app uses DynamoDB and you need some unit tests to verify the validity of your code, but you aren’t sure how to go about doing this.

Azure:

Personal access tokens (PATs) make it easy to authenticate against Azure Devops to integrate with your tools and services. However, leaked tokens could compromise your Azure DevOps account and data, putting your applications and services at significant risk.

Azure announces general availability of scale-out NVIDIA A100 GPU Clusters: the fastest public cloud supercomputer.

La Liga, the foremost Spanish football league, has expanded its partnership with Microsoft Azure to focus on machine learning (ML), over the top (OTT) services, as well as augmented reality.

A little over a year ago, Microsoft Build 2020 was Microsoft’s first flagship event to become all-digital early in the COVID-19 pandemic.

Generators at datacenters, most often powered by petroleum-based diesel, play a key role in delivering reliable backup power. Each of these generators is used for no more than a few hours a year or less at our datacenter sites, most often for routine maintenance or for backup power during a grid outage. 

GCP:

Having constant access to fresh customer data is a key requirement for PedidosYa to improve and innovate our customer’s experience. Our internal stakeholders also require faster insights to drive agile business decisions. 

5 ways Vertex Vizier hyperparameter tuning improves ML models

Getting started with Kubernetes is often harder than it needs to be. While working with a cluster “from scratch” can be a great learning exercise or a good solution for some highly specialized workloads, often the details of cluster management can be made easier by utilizing a managed service offering. 

Zero-trust managed security for services with Traffic Director

Databases are part of virtually every application you run in your organization and great apps need great databases. This post is focused on one such great database—Cloud Spanner.

Others:

Kubernetes is a robust yet complex infrastructure system for container orchestration, with multiple components that must be adequately protected. 

It is no contradiction to say that being ‘cloud-native’ has not much to do with cloud computing. There is an idea that cloud is a place, a suite of technologies or services that run somewhere in data centres. But the cloud is not a place; it is a way of working.

The most innovative companies of 2021 according to BCG: Alphabet, Amazon, Microsoft all make it.

In this article, the author discusses how cloud computing has changed the traditional approach to operation and maintenance (O&M).

This June, a small marine research non-profit with a huge vision will launch a first-of-its-kind, crewless transoceanic ship that will attempt to cross the Atlantic Ocean without human intervention.

Categories: DBA Blogs

Cloud Vanity: A Weekly Carnival of AWS, Azure, GCP, and More - Edition 3

Thu, 2021-05-27 20:48

 Welcome to the next edition of weekly Cloud Vanity. IBM Cloud is not having a good time out there. It was hit by another outage this week, just five days after a similar incident. The root cause was an unidentified "severity-one" incident impacted multiple services across multiple locations. This once again underlines that you need a high availability and DR plan in the cloud too. 


AWS:

Authorizing functionality of an application based on group membership is a best practice. If you’re building APIs with Amazon API Gateway and you need fine-grained access control for your users, you can use Amazon Cognito

VMware Cloud on AWS allows customers to run VMware vSphere workloads on the AWS global infrastructure. This means you can run vSphere workloads across all of the AWS Regions where VMware Cloud on AWS is available.

CloudFormation Guard, an open source tool that helps validate your AWS CloudFormation templates against a rule set to keep AWS resources in compliance with company guidelines.

AWS Security Hub provides a comprehensive view of the security alerts and security posture in your accounts. Now you can import AWS IoT Device Defender audit findings into Security Hub.

Customers who are running fleets of Amazon Elastic Compute Cloud (Amazon EC2) instances use advanced monitoring techniques to observe their operational performance. Capabilities like aggregated and custom dimensions help customers categorize and customize their metrics across server fleets for fast and efficient decision making. 

Azure:

Microsoft has published a root cause analysis of an outage of its Azure Domain Name System that struck the cloud platform over Easter, causing intermittent failures for customers accessing and managing their Microsoft services globally.

Machine learning is changing the way we interact with each other and improving many aspects of our lives. In recent years, a variety of tools and frameworks have been developed to make it easier to build and deploy machine learning models into user-facing applications.

Function App keys are placed in the azure-webjobs-secrets folder in Blob Container. If this folder is missing, this could mean that the Function App is unable to connect to the storage account referenced by the Function App Application Setting “AzureWebJobsStorage”. This could happen either because of a network misconfiguration or because of an issue on the storage side.

Java is one of the most popular programming languages, used by over seven million developers to create everything from enterprise applications to complex robots. 

Azure at Microsoft Build recap: build amazing things on your terms, anywhere

GCP:

Using AI-powered machine learning models to identify fraudulent unemployment claims

Google has won a deal to provide cloud services to Elon Musk's SpaceX, which has launched a slew of Starlink satellites to provide high-speed internet, it said on Thursday. SpaceX will set up ground stations within Google's data centres that connect to the Starlink satellites

How to leverage global address resolution using Cloud DNS in Google Cloud VMware Engine

Analyze your logs easier with log field analytics

With Datashare, data publishers, aggregators, and consumers can come together to exchange licensed datasets on Google Cloud securely, quickly, and easily.

Others:

CNA Financial, the US insurance conglomerate, has apparently paid $40m to ransomware operators to gets its files back.

Oracle Chairman and CTO Larry Ellison was seemingly omnipresent at the annual Oracle OpenWorld conference last week, providing his unique insights on subjects ranging from why autonomous technologies are so fundamentally important to what keeps him engaged after decades in the business.

Use o to radically accelerate your cloud operations workflow. Spend less time searching the docs and say goodbye to the days of copy-and-pasting those long OCIDs.

TimescaleDB is a time series data plug-in for PostgreSQL. Its version 1.5 enables automatic compression.

Alibaba’s Winning Cloud Formula Is Coming Under Pressure

Categories: DBA Blogs

Cloud Vanity: A Weekly Carnival of AWS, Azure, GCP, and More - Edition 2

Thu, 2021-05-27 20:47

 Welcome to the next edition of weekly Cloud Vanity. With all the hype around Cloud computing, you might think that everyone is already in the cloud but that's not really true. It's still just the beginning and a long way to go. So don't think it's already late to jump on the bandwagon. If you are thinking about shifting to cloud career then do it now. If you are a company thinking about moving workloads to cloud then do it. Because there is no other option if you want to survive.


AWS:

With the launch of AWS Distro for OpenTelemetry, AWS will continue to help drive advances in observability technologies, enhancing innovation and scalability for the entire OpenTelemetry community by contributing 100% of all changes to the upstream.

CloudEndure Migration can move applications from any physical, virtual, or cloud-based infrastructure to AWS at no charge. This complements AWS Server Migration Service (AWS SMS), which is an agentless service for migrating on-premises workloads to AWS. And now we have AWS Application Migration Service for lift and shift migrations.

Monitoring SQL Server is an essential aspect of any relational database management system (RDBMS) when dealing with performance problems. 

A common practice when creating a data model design, especially in the relational database management system (RDMS) world, is to start by creating an entity relationship diagram (ERD). Afterwards, you normalize your data by creating a table for each entity type in your ERD design. 

Even if you don't like AWS IAM, drop by to wish it happy birthday. Yes AWS is celebrating or marking the birthday of AWS Identity and Access Management (IAM). 

Azure:

Infrastructure-as-code tools like ARM templates and Terraform are more and more used to deploy infrastructure solutions. In general, these tools run within the context of a service principal so there needs to be an account with high privileges – at least high enough to deploy a given type of resource in your cloud environment. 

Software available under the partnership includes Oracle WebLogic, Oracle Linux, and Oracle Database, as well as interoperability between Oracle Cloud Infrastructure (OCI) and Azure. 

With the ever-increasing adoption of cloud-based solutions, and the incredibly complex make-up of the application architectures; the ability to effectively manage, orchestrate, and monitor the scenarios for search, security, and operations are becoming very critical for the success of the businesses.

Modern web app design patterns leverage microservices best practices for performance, agility, and extensibility. Azure Static Web Apps is a turnkey service for these modern full-stack web apps with pre-built and pre-rendered static front-ends, and serverless API backends. 

One detail updating today is the Azure “A” icon, which will be rolled out in product experiences and across related sites in the coming weeks. The new Azure icon represents the unity of Azure within the larger Microsoft family of product icons.

GCP:

Today’s healthcare organizations are grappling with mountains of data, increasing regulations, and escalating customer expectations. To add to these, healthcare organizations deal with highly sensitive personal data that needs to be protected.

Since its launch in 2016, the Google Cloud Public Datasets Program has provided a catalog of curated public data assets in optimized formats on BigQuery and Cloud Storage in partnership with a number of data providers.

A data cloud offers a comprehensive and proven approach to cloud and embraces the full data lifecycle, from the systems that run your business, where data is born, to analytics that support decision making, to AI and machine learning (ML) that predict and automate the future. 

VPC Flow Logs is one such enterprise-grade network traffic analysis tool, providing information about TCP and UDP traffic flow to and from VM instances on Google Cloud, including the instances used as Google Kubernetes Engine (GKE) nodes.

Others:

This post covers setting up API Gateway logging, setting up Logging Analytics, setting up Service Connector Hub to send API Gateway logs to Logging Analytics, and creating a Dashboard. Prerequisites include a working knowledge of OCI API Gateway and OCI in general.

Applications and use cases continue to evolve around data and enhanced storage needs in the cloud. Organizations building their own infrastructure and storage solutions to address the variable demands of their applications is more expensive and complex. Oracle makes storage seamless, inexpensive, high-performing, and flexible to support a wide range of use cases without sacrificing enterprise capabilities.

Businesses spend billions of hours a year on work that strips people of time and keeps them from focusing on higher-value things. AI-powered Automation helps people reclaim up to 50% of their time, and that’s something we all need.

The word “automation” often reminds people of assembly lines and manufacturing processes. But in a digital world, automation isn’t about the delivery of goods — it’s about making every interaction, experience and process more intelligent and impactful. This helps companies deliver value to their customers and gain a competitive advantage in their industry.

Financial institutions around the world are dramatically accelerating digital transformation. In the financial services industry, over 36 billion customer records were exposed in Q3 of 2020. The IBM Cloud for Financial Services provides a way for banks and financial institutions to migrate workloads to the cloud platform.

Categories: DBA Blogs

How to Enable SSH Equivalency Between EC2 Instances

Tue, 2021-04-20 01:55

 If you want to login to a Linux instance from other Linux instance without password or without mentioning the key, then ssh equivalency is the solution. 

Normally, in order to generate ssh equivalency between 2 Linux instances, you create both public and private keys, then copy them over to other instance and add it to authorized_keys file etc. 

But in EC2 instance in AWS, you have to create or specify the keys during the launch time of instance. When you launch an EC2 instance, public keys are already present in home directory of the user. For example, for Amazon Linux , the public key would be already present in /home/ec2-user/.ssh/authorized_keys file. That is why, you only need the private key to ssh into that server.

Let's say you have another EC2 instance which is Linux based and you want to establish ssh equivalency between these two instances. Let's suppose both are using the same key-pair. It means that both would already have public key present in their /home/ec2-user/.ssh/authorized_keys file. In that case all you need to do is following on both servers to establish ssh equivalency:


1- Login to Instance 1

2- Go to /home/ec2-user/.ssh/ directory

3- Touch a new file

touch id_rsa

chmod 700 id_rsa

4- Copy the content of your pem key and paste it into this id_rsa file

Now you should be able to ssh to the other server, which has the same keypair.

Repeat above steps on other server if you want to enable reverse ssh equivalency.

Categories: DBA Blogs

Where to Put PostgreSQL in AWS

Thu, 2021-04-15 22:44

When it comes to putting PostgreSQL database in AWS, you are spoiled for choice. There are 3 ways to do that:



1) Install and configure PostgreSQL on EC2 instance.

2) Amazon RDS for PostgreSQL

3) Amazon Aurora for PostgreSQL

You can watch the whole video here.

Categories: DBA Blogs

One Reason to Run Oracle on Google Cloud Platform

Wed, 2021-03-17 02:29

There is one reason to run Oracle on Google Cloud Platform, one solid and compelling reason. It has nothing to do with cost, and it has nothing to do with performance.

In all fairness, you can get cost savings (or not) with any of cloud provider in terms of software and hardware. But if you are or have to run Oracle, then probably cost is not your issue. For me, one differentiating reason is presence of Google Big Query in GCP. 

A serverless, fastest, easiest and very powerful data warehouse GCP BQ is an attraction of its own if you compare it to other competing cloud offerings. I am observing more and more companies drawing to GCP just to use BQ as unified warehouse of their data. Companies are using ETL, ELT tools and flows to push data into BQ from all sorts of databases and data stores on AWS, OCI and Azure. 

So if you have a choice, then why not put your Oracle database on GCP VM using their bare metal? If you even mention that to your GCP sales rep, very strong chances are that he will get a very good discount for you. Be sure to mention that you intend to integrate other GCP services with that Oracle database in the future and you might get bare metal for free. That's my guess, but there is no harm in trying.


Categories: DBA Blogs

Compartments in OCI

Sat, 2021-03-13 21:23

 One of my favorite concepts in Oracle Cloud Infrastructure (OCI) is compartments. If you have worked in AWS, at first they may seem redundant and cumbersome, but contrary to that, they are quite useful and make things less cluttered. 

I think if AWS would get a chance to reorganize their cloud governance model, they might also introduce something like that but then they don't like to copy thing. 

Compartment is used to organize your cloud resources like compute instances, buckets, etc. Compartments are a global concept and they span multiple regions. You can connect your resources across your regions within the same compartment.

The OCI account is called as Tenancy. When you create a tenancy, you also get a default compartment which is called as 'root compartment'. Of course, you can also create many other compartments too.

One of the biggest advantage of OCI compartment is that they enable you to do cost control of your cloud resources. You can assign budgets, quotas, and cost tags to the compartment and its resources. You can attach policies to them and that enable you to control the access in a unified and centralized way. All you have to do is to design the layout of the resources.

Categories: DBA Blogs

Solution of Nuget Provider Issue with PowerShell and AWS Tools

Wed, 2021-02-24 20:08

 On a AWS EC2 Windows 2012 server, my goal was to write some data to S3 bucket. I was using a small Powershell Script to copy the file to the S3 bucket. For that I needed to Install AWS Tools for Powershell and I used following command at Powershell prompt running as administrator:

Windows PowerShell

Copyright (C) 2016 Microsoft Corporation. All rights reserved.


PS C:\Users\SRV> Install-Module -Scope CurrentUser -Name AWSPowerShell.NetCore -Force

and it failed with following error:

NuGet provider is required to continue

PowerShellGet requires NuGet provider version '2.8.5.201' or newer to interact with NuGet-based repositories. The NuGet

 provider must be available in 'C:\Program Files\PackageManagement\ProviderAssemblies' or

'C:\Users\SRV\AppData\Local\PackageManagement\ProviderAssemblies'. You can also install the NuGet provider

by running 'Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force'. Do you want PowerShellGet to install

 and import the NuGet provider now?

[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): y

WARNING: Unable to download from URI 'https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409' to ''.

WARNING: Unable to download the list of available providers. Check your internet connection.

PackageManagement\Install-PackageProvider : No match was found for the specified search criteria for the provider

'NuGet'. The package provider requires 'PackageManagement' and 'Provider' tags. Please check if the specified package

has the tags.

Solution:

The solution is to enable TLS 1.2 on this Windows host, which you can do by running Powershell in administrator mode:


Set-ItemProperty -Path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\.NetFramework\v4.0.30319' -Name 'SchUseStrongCrypto' -Value '1' -Type DWord


Close your Powershell window, and reopen as administrator and check if TLS protocol is present by typing following command on PS prompt:

[Net.ServicePointManager]::SecurityProtocol

If the above shows Tls12 in the output, then we are all good and now you should be able to install AWS Tools.

I hope that helps.




Categories: DBA Blogs

Boto3 Dynamodb TypeError: Float types are not supported. Use Decimal types instead

Mon, 2021-02-22 01:26

 I was trying to ram data into AWS dynamodb via Boto3 and the streaming failed due to following error:


  File "C:\Program Files\Python37\lib\site-packages\boto3\dynamodb\types.py", line 102, in serialize

    dynamodb_type = self._get_dynamodb_type(value)

  File "C:\Program Files\Python37\lib\site-packages\boto3\dynamodb\types.py", line 115, in _get_dynamodb_type

    elif self._is_number(value):

  File "C:\Program Files\Python37\lib\site-packages\boto3\dynamodb\types.py", line 160, in _is_number

    'Float types are not supported. Use Decimal types instead.')

TypeError: Float types are not supported. Use Decimal types instead.



I was actually getting some raw data points from cloudwatch for later analytics. These datapoints were in float format which are not supported by Dynamodb. Now instead of importing some decimal libraries or doing JSON manipulation, you can solve above with simple Python format expression like this:

"{0:.2f}".format(datapoint['Average'])

It worked like a charm afterwards. I hope that helps.
Categories: DBA Blogs

Main SQL Window Functions for Data Engineers in Cloud

Fri, 2021-02-19 22:36

 To become a data engineer in cloud requires to have a good grasp of SQL among various other things. SQL is the premier tool for interacting with data sets. At first it seems daunting to see all those SQL analytics functions, but if you start with a tiny dataset like in the examples below and understand how these functions work, then it all becomes very easy for large datasets of any volume.

Once you know the basic structure of SQL, understand the basic clauses, then its time to jump into the main analytics functions. Below I have used SQL's With clause to generate a tiny dataset in Oracle. You don't have to create a table, load it with sample data and play with it. Just use with clause with the accompanying select statements which demonstrate you the common SQL Window functions.


1- In this example, sum and row_number functions works on each row of whole window.

   

With x as ( 

   SELECT 'tom' as name, 1 AS t from dual

   UNION ALL

   SELECT 'harry' as name,2 AS t  from dual

   UNION ALL

   SELECT 'jade' as name,2 AS t  from dual

   UNION ALL

   SELECT 'ponzi' as name,3 AS t  from dual

)

select name,t,sum(t) over () as SumEachRow, row_number() over (order by t) as RN from x;


2- In this example, sum and row_number functions works on each row of each partition of whole window. This window is partitioned on column t.


With x as ( 

   SELECT 'tom' as name, 1 AS t from dual

   UNION ALL

   SELECT 'harry' as name,2 AS t  from dual

   UNION ALL

   SELECT 'jade' as name,2 AS t  from dual

   UNION ALL

   SELECT 'ponzi' as name,3 AS t  from dual

)

select name,t,sum(t) over (partition by t) as SumEachRow, row_number() over (partition by t order by t) as RN from x;


3- In following example, we have divided the window into 2 partitions by using case statement within partition clause. One partition is when t=1, and other partition is composed of rest of rows.


With x as ( 

   SELECT 'tom' as name, 1 AS t from dual

   UNION ALL

   SELECT 'harry' as name,2 AS t  from dual

   UNION ALL

   SELECT 'jade' as name,2 AS t  from dual

   UNION ALL

   SELECT 'ponzi' as name,3 AS t  from dual

)

select name,t,sum(t) over (partition by CASE WHEN t = 1 THEN t ELSE NULL END) as SumEachRow, row_number() over (partition by CASE WHEN t = 1 THEN t ELSE NULL END order by t) as RN from x;


4- Below example is variant of example 3. In this the window function row_number is working on whole window instead of partition whereas the window function sum is working on partitions.


With x as ( 

   SELECT 'tom' as name, 1 AS t from dual

   UNION ALL

   SELECT 'harry' as name,2 AS t  from dual

   UNION ALL

   SELECT 'jade' as name,2 AS t  from dual

   UNION ALL

   SELECT 'ponzi' as name,3 AS t  from dual

)

select name,t,sum(t) over (partition by CASE WHEN t = 1 THEN t ELSE NULL END) as SumEachRow, row_number() over (order by t) as RN from x;


5- This example uses lag function to return previous value of window function. For lag function, the value for first row is always null as there is no previous value.


With x as ( 

   SELECT 'tom' as name, 1 AS t from dual

   UNION ALL

   SELECT 'harry' as name,2 AS t  from dual

   UNION ALL

   SELECT 'jade' as name,2 AS t  from dual

   UNION ALL

   SELECT 'ponzi' as name,3 AS t  from dual

)

select name,t,lag(t) over (order by t) as Previous_t from x;


6- This example uses lead function to return next value of window function. For lead function, the value of last row is always null as there is no next value.


With x as ( 

   SELECT 'tom' as name, 1 AS t from dual

   UNION ALL

   SELECT 'harry' as name,2 AS t  from dual

   UNION ALL

   SELECT 'jade' as name,2 AS t  from dual

   UNION ALL

   SELECT 'ponzi' as name,3 AS t  from dual

)

select name,t,lead(t) over (order by t) as Next_t from x;


7- This example shows that First_value function returns first value in window for each row.


With x as ( 

   SELECT 'tom' as name, 1 AS t from dual

   UNION ALL

   SELECT 'harry' as name,2 AS t  from dual

   UNION ALL

   SELECT 'jade' as name,2 AS t  from dual

   UNION ALL

   SELECT 'ponzi' as name,3 AS t  from dual

)

select name,t,first_value(t) over (order by t) as First_t from x;


8- This example shows that First_value function returns first value in each partition of window for each row.

With x as ( 

   SELECT 'tom' as name, 1 AS t from dual

   UNION ALL

   SELECT 'harry' as name,2 AS t  from dual

   UNION ALL

   SELECT 'jade' as name,2 AS t  from dual

   UNION ALL

   SELECT 'ponzi' as name,3 AS t  from dual

)

select name,t,first_value(t) over (partition by t order by t) as First_t from x;


9- This example shows that last_value function returns last value in window for each row.


With x as ( 

   SELECT 'tom' as name, 1 AS t from dual

   UNION ALL

   SELECT 'harry' as name,2 AS t  from dual

   UNION ALL

   SELECT 'jade' as name,2 AS t  from dual

   UNION ALL

   SELECT 'ponzi' as name,3 AS t  from dual

)

select name,t,last_value(t) over (order by t ROWS BETWEEN

           UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as Last_t from x;


10- This example shows that Last_value function returns last value in each partition of window for each row.

With x as ( 

   SELECT 'tom' as name, 1 AS t from dual

   UNION ALL

   SELECT 'harry' as name,2 AS t  from dual

   UNION ALL

   SELECT 'jade' as name,2 AS t  from dual

   UNION ALL

   SELECT 'ponzi' as name,3 AS t  from dual

)

select name,t,last_value(t) over (partition by t order by t ROWS BETWEEN

           UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as Last_t from x;


For explanation of rows between unbounded clause, see this 

11- This example shows the rank() function which is useful for Top N, or Bottom N sort of queries. Following is for whole window. The main idea is that rank starts from 1 from first row and then rank remains same for rows with same value within window. When value changes, the rank increments as per number of lines from top. 

With x as ( 

   SELECT 'tom' as name, 1 AS t from dual

   UNION ALL

   SELECT 'harry' as name,2 AS t  from dual

   UNION ALL

   SELECT 'jade' as name,2 AS t  from dual

   UNION ALL

   SELECT 'ponzi' as name,3 AS t  from dual

)

select name,t,rank(t) over (order by t) as Rank from x;


12- This example shows the rank() function which is useful for Top N, or Bottom N sort of queries. Following is for each partition of window.


With x as ( 

   SELECT 'tom' as name, 1 AS t from dual

   UNION ALL

   SELECT 'harry' as name,2 AS t  from dual

   UNION ALL

   SELECT 'jade' as name,2 AS t  from dual

   UNION ALL

   SELECT 'ponzi' as name,3 AS t  from dual

)

select name,t,rank() over (partition by t order by t) as Rank from x;


PS. Yes I know formatting of code chunks is not good enough but this is limitation of blogger platform it seems and another note to self that I need to move to a better one.

Categories: DBA Blogs

Docker Behind Proxy on CentOS - Solution to Many Issues

Thu, 2021-01-28 22:50

If you running docker behind proxy on CentOS and receiving timeout or network errors, then use below steps to configure proxy settings on your CentOS box where docker is installed and you are trying to build docker image:

Login as the user which is going to build image


Create directory with sudo

    Sudo mkdir -p /etc/systemd/system/docker.service.d


Create file for http proxy setting

    /etc/systemd/system/docker.service.d/http-proxy.conf

    and insert following content into it:

    [Service]

    Environment="HTTP_PROXY=http://yourproxy.com:80/"


Create file for https proxy setting

    /etc/systemd/system/docker.service.d/https-proxy.conf

    and insert following content into it:

    [Service]

    Environment="HTTPS_PROXY=https://yourproxy.com:80/"


Restart the systemctl daemon

systemctl daemon-reload


Restart the docker:

service docker restart


Also if you are trying to install Yarn or NPM within your dockerfile , then within your docker file define following environment variables

ENV http_proxy=http://yourproxy.com

ENV https_proxy=http://yourproxy.com

ENV HTTP_PROXY=http://yourproxy.com

ENV HTTPS_PROXY=http://yourproxy.com


Notice that only specify http protocol both for https and http proxy. 

I hope that helps.

Restart docker again.


Categories: DBA Blogs

Most Underappreciated AWS Service and Why

Tue, 2021-01-05 17:11

Who wants to mention in their resume that one of their operation task is to tag the cloud resources? Well I did and mentioned that one of the tools I used for that purpose was Tag Editor. Interviewer was surprised to learn that there was such a thing in AWS which allowed tagging multiple resource at once. I got the job due to this most under-appreciated and largely unknown service.

Tagging is boring but essential. As cloud matures, tagging is fast becoming an integral part of it. In the environments I manage, most of tagging management is automated but there is still a requirement at times for manual bulk tagging and that's where Tag Editor comes very handy. Besides of bulk tagging Tag Editor enables you to search for the resources that you want to tag, and then manage tags for the resources in your search results.

There are various other tools available from AWS to ensure tag compliance and management but the reason why I like Tag Editor most is its ease of use and a single pane of window to search resources by tag keys, tag values, region or resource types. It's not as glamorous as AWS Monitron, AWS Proton or AWS Fargate but as useful as any other service is.

In our environment, if its not tagged then its not allowed in the cloud. Tag Editor addresses the basics of being in cloud. Get it right, and you are well on your way to well-architected cloud infrastructure.

Categories: DBA Blogs

From DBA to DBI

Mon, 2020-10-19 18:48

Recently Pradeep Parmer at AWS had a blog post about transitioning from DBA to DBI or in other words from database administrator to database innovator. I wonder what exactly is the difference here as any DBA worth his or her salt is an innovator in itself.

Administering a database is not about sleepily issuing backup commands or in terms of Cloud managed databases clicking here and there. Database administration has evolved over time just like other IT roles and is totally different what it was few years back. 

Regardless of the database engine you use, you have to have a breadth of knowledge about operating systems, networking, automation, scripting, on top of database concepts. With managed database services in cloud like AWS RDS or GCP Cloud SQL or Big Query many of the skills have become outdated but new ones have sprung up. That has always  been the case with DBA field. 

Taking the example of Oracle; what we were doing in Oracle 8i became obsolete in Oracle 11g and Oracle 19c  is a totally different beast. Oracle Exadata, RAC, various types of DR services, fusion middleware are in itself a new ballgame with every version. 

Even with managed database services, the role of DBA has become more involved in terms of migrations and then optimizing what's running within the databases from stopping the database costs going through the roof.

So the point here is that DBAs have always been innovators. They have always been trying to find out new ways to automate the management and healing of their databases. They always are under the pressure to eke out last possible optimization out of their system and that's still the case even if those databases are supposedly managed by cloud providers. 

With purpose built databases which are addressed different use case for different database technology the role of DBA has only become more relevant as they have to evolve to address all this graph, in-memory, and other cool nifty types of databases.

We have always been innovators my friend. 

Categories: DBA Blogs

What is Purpose Built Database

Mon, 2020-10-05 17:30

 In simple words, a general Database Engine is a big clunky piece of software with features for all the use cases, and its up to you to choose which features to use. Whereas in a purpose built database, you get a lean, specific database which is only suitable for the feature you want.

For instance, AWS offers 15 purpose-built database engines including relational, key-value, document, in-memory, graph, time series, and ledger databases. GCP also provides multiple databases types like Spanner, BigQuery etc. 

But the thing is that the one-size-fits-all monolithic databases aren't going anywhere. They are here to stay. A medium to large organization has way too many requirements and features to be used and having one database for every use case increases the footprint and cost. For every production database, there is a dev, test, and QA database so the foot print keeps increasing.

So the thing is that though having purpose built database notion is great it's not going to throw monilithic database out of the window. It just provides another option for the organization and they could just have a managed service for purpose built database for a specialized use case but for a general database requirement for OLTP and data warehouse, monilithic is still the way.

Categories: DBA Blogs

5 Important Steps Before Upgrading Oracle on AWS RDS

Sat, 2020-09-26 23:03

 Even though AWS RDS (relational database service) is a managed service which means that you won't have to worry about upgrades, patches and other tidbits, you still have the option of manually triggering the upgrade at time of your choice.

Upgrading an Oracle database is quite critical not only for the database itself but more importantly for the dependent applications. It's very important to try out any upgrade on RDS on a test representative system before hand to iron out any wrinkles and check the timings and any other potential issues. 

There are 5 important steps before upgrading Oracle on AWS RDS you can take to make this process more risk-free, speedy, and reliable:

  1. Check Invalid objects such as procedures, functions, packages etc in your database.
  2. Make a list of the objects which are still invalid and if possible delete them to remove clutter.
  3. Disable and remove audit logs if they are stored in database
  4. Convert dbms_jobs Jobs and other stuff to dbms_scheduler
  5. Take Snapshot of your production database right before you upgrade to speed up the upgrade process as then during upgrade only delta snapshot will be taken
I hope that helps.

Categories: DBA Blogs

Choice State in AWS Step Functions

Thu, 2020-09-17 02:47

Richly asynchronous server-less applications can be built by using AWS step functions. Choice State in AWS Step Functions is the newest feature which was long awaited.

In simply words, we define steps and their transitions and call it State Machine as a whole. In order to define this state machine, we use Amazon States Language (ASL). ASL is a JSON-based structured language that defines state machines and collections of states that can perform work (Task states), determines which state to transition to next (Choice state), and stops execution on error (Fail state). 

So if the requirement is to add a branching logic like if-then-else or case statement in our state transition, then Choice state comes handy. The choice state introduces various new operators into the ASL and the sky is now limit with the possibilities. Operators for choice state include comparison operators like Is Null, IsString etc, Existence operators like Ispresent, glob wildcards where you match some string and also variable string comparison.

Choice State enables developers to simplify existing definitions or add dynamic behavior within state machine definitions. This makes it easier to orchestrate multiple AWS services to accomplish tasks. Modelling complex workflows with extended logic is now possible with this new feature.

Now one hopes that AWS introduces doing it all graphically instead of dabbling into ASL.

Categories: DBA Blogs

CloudFormation Template for IAM Role with Inline Poicy

Tue, 2020-08-18 21:10
I struggled with this a bit to create a cloudformation template for IAM role with inline policy with IAM user as principal. So here it is as a quick reference:


    AWSTemplateFormatVersion: 2010-09-09
Parameters:
vTableName:
Type: String
Description: the tablename
Default: arn:aws:dynamodb:ap-southeast-2:1234567:table/test-table
vUserName:
Type: String
Description: New account username
Default: mytestuser
Resources:
DynamoRoleForTest:
Type: 'AWS::IAM::Role'
Properties:
AssumeRolePolicyDocument:
Statement:
- Effect: Allow
Principal:
AWS:
- !Sub 'arn:aws:iam::${AWS::AccountId}:user/${vUserName}'
Action:
- sts:AssumeRole
Path: /
Policies:
- PolicyName: DynamoPolicy
PolicyDocument:
Version: 2012-10-17
Statement:
- Effect: Allow
Action:
- dynamodb:BatchGet*
- dynamodb:DescribeStream
- dynamodb:DescribeTable
- dynamodb:Get*
- dynamodb:Query
- dynamodb:Scan
Resource: !Ref vTableName
I hope that helps. Thanks.
Categories: DBA Blogs

How to Read Docker Inspect Output

Fri, 2020-08-14 21:52

Here is quick easy set of instructions as how to read docker inspect output:

First you run the command:

docker inspect <image id> or <container id>

and then it outputs in JSON format. Your normally are interested in what exactly is in this docker image which you have just pulled from web or inherited in your new job. 

Now copy this JSON output and put it in VSCode or any of online JSON editor of your choice. For a quick glance, look at the node "ContainerConfig." This node tells you what exactly was run within the temporary container which was used to build this image such as CMD, EntryPoint etc. 

In addition to the above, following is the description of all the important bits of information found in Inspect command output:

  • ID: It's unique identifier of the image.
  • Parent: A link to the identifier of the parent image of this image. 
  • Container: The temporary container created when the image was built.
  • ContainerConfig: Contains what happened in that temporary container.
  • DockerVersion: Version of Docker used to create the image

Virtual Size: Image size in bytes.

I hope that helps.

Categories: DBA Blogs

Installing Docker on Amazon Linux 2

Thu, 2020-08-13 00:50
Installing docker on Amazon Linux 2 is full of surprises which are not easy to deal with. I just wanted to test something within a container environment, so spun up a new EC2 instance from the following AMI:

Amazon Linux 2 AMI (HVM), SSD Volume Type - ami-0ded330691a314693 (64-bit x86) / ami-0c3a4ad3dbe082a72 (64-bit Arm)

After this Linux instance came up, I just did yum update to get all the latest stuff:

 sudo yum update

All good so far.
Then I installed/checked yum-utils and grabbed the docker repo, and all good there:

[ec2-user@testf ~]$ sudo yum install -y yum-utils
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
Package yum-utils-1.1.31-46.amzn2.0.1.noarch already installed and latest version
Nothing to do

[ec2-user@testf ~]$ sudo yum-config-manager \
>     --add-repo \
>     https://download.docker.com/linux/centos/docker-ce.repo
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
adding repo from: https://download.docker.com/linux/centos/docker-ce.repo
grabbing file https://download.docker.com/linux/centos/docker-ce.repo to /etc/yum.repos.d/docker-ce.repo
repo saved to /etc/yum.repos.d/docker-ce.repo


Now, it's time to install docker:

[ec2-user@testf ~]$ sudo yum install docker-ce docker-ce-cli containerd.io
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
amzn2-core                                                                                                               | 3.7 kB  00:00:00
docker-ce-stable                                                                                                         | 3.5 kB  00:00:00
(1/2): docker-ce-stable/x86_64/primary_db                                                                                |  45 kB  00:00:00
(2/2): docker-ce-stable/x86_64/updateinfo                                                                                |   55 B  00:00:00
Resolving Dependencies
--> Running transaction check
---> Package containerd.io.x86_64 0:1.2.13-3.2.el7 will be installed
--> Processing Dependency: container-selinux >= 2:2.74 for package: containerd.io-1.2.13-3.2.el7.x86_64
---> Package docker-ce.x86_64 3:19.03.12-3.el7 will be installed
--> Processing Dependency: container-selinux >= 2:2.74 for package: 3:docker-ce-19.03.12-3.el7.x86_64
--> Processing Dependency: libcgroup for package: 3:docker-ce-19.03.12-3.el7.x86_64
---> Package docker-ce-cli.x86_64 1:19.03.12-3.el7 will be installed
--> Running transaction check
---> Package containerd.io.x86_64 0:1.2.13-3.2.el7 will be installed
--> Processing Dependency: container-selinux >= 2:2.74 for package: containerd.io-1.2.13-3.2.el7.x86_64
---> Package docker-ce.x86_64 3:19.03.12-3.el7 will be installed
--> Processing Dependency: container-selinux >= 2:2.74 for package: 3:docker-ce-19.03.12-3.el7.x86_64
---> Package libcgroup.x86_64 0:0.41-21.amzn2 will be installed
--> Finished Dependency Resolution
Error: Package: containerd.io-1.2.13-3.2.el7.x86_64 (docker-ce-stable)
           Requires: container-selinux >= 2:2.74
Error: Package: 3:docker-ce-19.03.12-3.el7.x86_64 (docker-ce-stable)
           Requires: container-selinux >= 2:2.74
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest


and it failed. 

So googled the error Requires: container-selinux and every stackoverflow post and blogs say to download the new rpm from some centos or similar mirror but it simply doesn't work, no matter how hard you try. 

Here is the ultimate best solution which enabled me to get docker installed on Amazon Linux 2 on this EC2 server:

sudo rm /etc/yum.repos.d/docker-ce.repo

sudo amazon-linux-extras install docker

sudo service docker start

[ec2-user@~]$ docker --version

Docker version 19.03.6-ce, build 369ce74


That's it. I hope that helps.
Categories: DBA Blogs

Pages