Vishal desai’s Oracle Blog

August 5, 2019

Cross Region DR for Oracle RDS

Filed under: AWS, RDS — vishaldesai @ 9:37 pm


There is a fantastic blog entry on how to automate DR on Amazon RDS for Oracle Databases. One of my customer had on-premise database of 1 TB database with transactional activity peak of 50 GB and had RPO and RTO requirements of 2 hours and 4 hours respectively. Before they migrate to RDS they wanted to know if solution can fulfill their RPO and RTO requirement so I carried out few tests.

Demo environment:

RDS-Oracle-DR-1


I built Database orcl in us-east-1 using swingbench. For first/baseline snapshot, it took 1 hour 25 minutes to create it, 50 minutes to copy from us-east-1 to us-west-2 and 19 minutes to restore it. First/Baseline snapshot numbers could be large depending upon the size of your database. I tweaked swingbench such that it generates 50 GB of archive logs in an hour. Incremental snapshot at T2, took 13 minutes to create it, 11 minutes to copy from us-east-1 and us-west-2 and 18 minutes to restore it. I performed number of incremental updates and all the snapshot creation, copy and restore numbers were in same ball park number.

RDS-Oracle-DR-2


Assuming snapshot is scheduled every hour, if there is an event at 1:14 pm (worst case scenario), PIT snapshot available will be from 12:00 pm. In that case data will be lost of 1 hour 14 minutes and snapshot will be restored by 1:45 pm. So RPO was 1 hour 14 minutes and RTO was 31 minutes. Another box demonstrates failure after snapshot completion but before snapshot copy was complete.

Bottom portion of spreadsheet shows RTO and RPO for 30 minutes snapshot schedule.

As you can see from this test that RPO and RTO requirements for this customer are achievable and Oracle RDS will be great choice as a first step to migrate to AWS cloud and hopefully migrate to Aurora later in future.

Dont’s


Copy/Restore times do not extrapolate so carry out testing based on your requirements. Do not assume that a 20 TB database is going to take 20x to copy/restore.


Always perform cross region timing calculations on incremental snapshot.

May 8, 2019

On-Demand Data science JupyterHub environment using AWS ServiceCatalog

Filed under: AWS, Data Science — vishaldesai @ 5:13 pm

Overview

In previous blog, I demonstrated how to create web based Data Science environment using JupyterHub on Elastic container services. There has to minimum of one task running so if environment is not continuously used customer still has to pay for one task per level of authorization that customer needs. There are different ML stages as shown below in diagram and customer wants to use 1 and 3 stage/tool pattern for their user cases. It will not be economical to use GPU based instances on a persistent basis with web based architecture so customer wants hybrid of web based and on demand environment. In this blog, I will demonstrate how to data scientists can request such on-demand environment using Service Catalog.

image

Architecture

image

0a – Create temporary ubuntu 18.04 EC2 instance and install Anaconda, R, python etc. Create AMI image from EC2 and terminate that temporary EC2 instance.

1a – Create IAM role with policy that will allow read access to PII data in S3 data lake. This role will be used by EC2 instances created when on-demand environment is requested by data scientists.

1b – Create cloud formation template using IAM role and AMI image. In this template only CPU based EC2 instances are allowed for data exploratory type of tasks.

1c – In Service Catalog, create Product using cloud formation template.

1d – In Service Catalog, create PII portfolio and add product to this portfolio.

1e – Create cloud formation template using IAM role and AMI image. In this template only GPU based EC2 instances are allowed for data exploratory, create model, train and evaluate model.

1f – In Service Catalog, create Product using cloud formation template.

1g – Add product to existing PII portfolio.

1i – Add IAM users that will work on PII data to PII portfolio.

2a to 2i – Follow similar steps as above with mapping to nonPII IAM role.

1h – Users can launch product using products assigned to them.

1j – Once the product is launched, users can access JupyterHub environment.

Implementation

0a. Create AMI

Launch any t2 EC2 instance using ubuntu 18.04, login to ec2 instance and run following commands. Once the packages are installed, reboot ec2 instance and create AMI image from it. After image is created terminate EC2 instance.

# Ubuntu updates
sudo apt-get update -y
sudo apt-get dist-upgrade -y
sudo apt-get autoremove -y
sudo apt-get autoclean -y 

# Install Anaconda
sudo curl -O https://repo.anaconda.com/archive/Anaconda3-5.2.0-Linux-x86_64.sh
sudo sh ./Anaconda3-5.2.0-Linux-x86_64.sh -b -p /home/ubuntu/anaconda3

# Install NFS client
sudo apt update
sudo apt-get install nfs-common -y

# Install R pre-requisites
sudo apt-get install -y --no-install-recommends \
fonts-dejavu \
unixodbc \
unixodbc-dev \
r-cran-rodbc \
gfortran \
gcc && \
rm -rf /var/lib/apt/lists/*


# Fix for devtools https://github.com/conda-forge/r-devtools-feedstock/issues/4
sudo ln -s /bin/tar /bin/gtar

# Install Conda, Jupyterhub and R packages
sudo su -
export PATH=$PATH:/home/ubuntu/anaconda3/bin
conda update -n base conda -y
conda create --name jupyter python=3.6 -y
source activate jupyter
conda install -c conda-forge jupyterhub -y
conda install -c conda-forge jupyter notebook -y
conda install -c r r-IRkernel -y
conda install -c r rstudio -y
conda install -c r/label/borked rstudio -y
conda install -c r r-devtools -y
conda install -c r r-ggplot2 r-dplyr -y
conda install -c plotly plotly -y
conda install -c plotly/label/test plotly -y
conda update curl -y
conda install -c bioconda bcftools -y
conda install -c bioconda/label/cf201901 bcftools -y
conda install -c anaconda boto3 -y
pip install boto3
R -e "devtools::install_github('IRkernel/IRkernel')"
R -e "IRkernel::installspec(user = FALSE)"

# Install Jupyterhub
#sudo python3 -m pip install jupyterhub

# Create Config file
mkdir /srv/jupyterhub
echo "c = get_config()" >> /srv/jupyterhub/jupyterhub_config.py
echo "c.Spawner.env_keep = ['AWS_DEFAULT_REGION','AWS_EXECUTION_ENV','AWS_REGION','AWS_CONTAINER_CREDENTIALS_RELATIVE_URI','ECS_CONTAINER_METADATA_URI']" >> /srv/jupyterhub/jupyterhub_config.py
echo "c.Spawner.cmd = ['/home/ubuntu/anaconda3/envs/jupyter/bin/jupyterhub-singleuser']" >> /srv/jupyterhub/jupyterhub_config.py
  

1a. Create IAM roles and policies.

aws iam create-role --role-name "jupyterpii" --description "Allows EC2 to call AWS services on your behalf." --assume-role-policy-document '{"Version":"2012-10-17","Statement":[{"Sid":"","Effect":"Allow","Principal":{"Service":["ec2.amazonaws.com"]},"Action":"sts:AssumeRole"}]}' --region us-east-1
aws iam put-role-policy --policy-name "pii" --policy-document '{"Version":"2012-10-17","Statement":[{"Sid":"VisualEditor0","Effect":"Allow","Action":["s3:PutAccountPublicAccessBlock","s3:GetAccountPublicAccessBlock","s3:ListAllMyBuckets","s3:HeadBucket"],"Resource":"*"},{"Sid":"VisualEditor1","Effect":"Allow","Action":"s3:*","Resource":"arn:aws:s3:::vishaldatalake/pii/*"}]}' --role-name "jupyterpii" --region us-east-1
aws iam create-role --role-name "jupyternonpii" --description "Allows EC2 to call AWS services on your behalf." --assume-role-policy-document '{"Version":"2012-10-17","Statement":[{"Sid":"","Effect":"Allow","Principal":{"Service":["ec2.amazonaws.com"]},"Action":"sts:AssumeRole"}]}' --region us-east-1
aws iam put-role-policy --policy-name "nonpii" --policy-document '{"Version":"2012-10-17","Statement":[{"Sid":"VisualEditor0","Effect":"Allow","Action":["s3:PutAccountPublicAccessBlock","s3:GetAccountPublicAccessBlock","s3:ListAllMyBuckets","s3:HeadBucket"],"Resource":"*"},{"Sid":"VisualEditor1","Effect":"Allow","Action":"s3:*","Resource":"arn:aws:s3:::vishaldatalake/nonpii/*"}]}' --role-name "jupyternonpii" --region us-east-1
    
    
  

1b, 1e Create cloud formation templates.

Create EFS mount point and replace template with EFS endpoint. All the notebooks will be stored on shared EFS mount point. Review default parameters and replace it according to your environment.

Templates

1c, 1f, 2c, 2f Create Service Catalog Products

Locate Service Catalog Service, click on upload new product.

image

Click on Next. Enter email contact of product owner and click on next.

image

Choose file, select cloud formation template and click on next. Review details and create products.

Below is the screenshot for all the products.

image

1d, 1g, 2d, 2g Create Service Catalog portfolio and add products.

Click on Create portfolio.

image

Click on Create.

image

Click on one of the portfolios and add PII specific products to PII portfolio and nonPII products to nonPII portfolio.

Below is screenshot of PII portfolio.

image

1i, 2i Create IAM users and give them access to Service Catalog portfolio.

Create IAM users for data scientists.

aws iam create-user --user-name "user1" --path "/" --region us-east-1
aws iam attach-user-policy --policy-arn "arn:aws:iam::aws:policy/AWSServiceCatalogEndUserFullAccess" --user-name "user1" --region us-east-1
aws iam attach-user-policy --policy-arn "arn:aws:iam::aws:policy/IAMUserChangePassword" --user-name "user1" --region us-east-1
aws iam create-login-profile --user-name "user1" --password-reset-required --regiot-1

aws iam create-user --user-name "user2" --path "/" --region us-east-1
aws iam attach-user-policy --policy-arn "arn:aws:iam::aws:policy/AWSServiceCatalogEndUserFullAccess" --user-name "user2" --region us-east-1
aws iam attach-user-policy --policy-arn "arn:aws:iam::aws:policy/IAMUserChangePassword" --user-name "user2" --region us-east-1
aws iam create-login-profile --user-name "user2" --password-reset-required --regiot-1
  

Click on Portfolio and under users, group and role click on Add users, group and role.

image

1h ,2h Login as Data scientist IAM user and launch product.

image

Click on product and launch product.

image

Provide details and click next.

image

Change instance type as per need and click next.

Leave default for Tags and Notifications. Review details and launch product.

image

Once the product is launched it will show JupyterHub url as key value pair.

Launch JupyterHub from browser.

image\

Login using username and password.

1k, 2k Create notebook and test access.

Create notebook and notebook will be stored on EFS mount point.

image

As expected, User can access data from PII folder.

image

User does not have access to nonPII data.

Once the user completes data science or machine learning tasks, product can be terminated by clicking on action and terminate. In future user can launch product and notebooks will be preserved as they are stored on persistent EFS mount.

Additional Considerations

Spot Instances

I have created products using cloud formation that uses on-demand instances. If there is no urgency to complete data exploration, machine learning training, consider creating products that use spot instances which can significantly save cost.

Security

Use certificates and consider launching EC2 products in private subnet for security reasons and access it through bastion.

Active Directory Integration

You can use ldap authenticator to authenticate users through AD.

SageMaker

Product offering can be extended using sage maker and data scientists can have flexibility to use JupyterHub or SageMaker depending upon their requirements.

Cost and Reporting

If users don’t terminate product EC2 will keep incurring additional cost. Lambda can be scheduled to terminate idle tasks or cloudwatch alarm can be created such that if EC2 instances are idle for more than certain period of time then terminate those instances.

As users have control over what type of EC2 instances they can launch, additional reporting can be created using service catalog, EC2 and cost metadata.

April 24, 2019

Multiuser Data Science Environment using JupyterHub on AWS Elastic Container Services

Filed under: AWS, Containers, Data Science, Uncategorized — vishaldesai @ 8:38 pm

Overview

Customer has built multi tenant data lake on S3 and have started ingesting different types of data. Now they want to build data science environment  for data exploration using JupyterHub. Below were the requirements.
  • Environment must be low cost.
  • Environment must scale with number of data scientists.
  • Environment should support authentication and authorization (S3 data lake).
  • Notebooks must be stored in a centralized location and should be sharable.
  • Environment must support installing custom packages and libraries such as R, pandas etc..

Customer’s preference is to use JupyterHub and does not want to use EMR due to additional cost.

Architecture

image

image

1a. Create IAM policies and roles with access to specific S3 folder. For simplicity lets assume S3 Bucket has two keys/folder called PII and Non-PII. Create policy and role with access to PII and Non-PII.

2a. Create two Dockerfile for authorization purpose. Each Dockerfile will have separate users for authentication and later while creating ECS task definition each image will be attached to different role for authorization. Store Dockerfile in CodeCommit.

2b. CodeBuild will trigger on commit

2c. Code Build will build images using Dockerfile.

2c. CodeBuild will push images in Elastic Container Repository.

Web Based Environment

Single Task can shared by multiple users, Task can scale based on scaling policy, Minimum of one task must be running so customer has to pay for at least one task per task group, CPU and memory limits per task are 4 vCPU and 30 GB of memory.

3a. Create ECS cluster

3b. Create one task definition using the role that has access to PII folder in S3 and image that consists of users who needs access to PII data and other task definition for Non-PII.

3c. Create Services for PII and Non-PII.

3d. Create Application load balancer with routing rules to different services.

3f. Create A-record in Route53 using one of the existing domains.

On Demand Environment

EC2 instance can be provisioned using service catalog, One EC2 instance per User, Users could also share EC2, Customer only pay what they use, wide variety of EC2 options available with much higher CPU, memory compared to ECS. Recommended for ad-hoc and very large data processing use cases.

In this blog, I will cover implementation of Web Based Environment and will cover On Demand Environment in part 2.

Walkthrough Dockerfile

Get the base image, update ubuntu and install jupyter, s3contents, awscli. s3contents is required to store Notebooks on S3.

#Base image
FROM jupyterhub/jupyterhub:latest
#USER root

# update Ubuntu
RUN apt-get update


# Install jupyter, awscli and s3contents (for storing notebooks on S3)
RUN pip install jupyter  && \
    pip install s3contents  && \
    pip install awscli --upgrade --user  && \
    mkdir /etc/jupyter
  

Install R and required packages.

# R pre-requisites
RUN apt-get update && \
    apt-get install -y --no-install-recommends \
    fonts-dejavu \
    unixodbc \
    unixodbc-dev \
    r-cran-rodbc \
    gfortran \
    gcc && \
    rm -rf /var/lib/apt/lists/*

# Fix for devtools https://github.com/conda-forge/r-devtools-feedstock/issues/4
RUN ln -s /bin/tar /bin/gtar


# R packages

RUN conda install -c r r-IRkernel && \
    conda install -c r rstudio && \
    conda install -c r/label/borked rstudio && \
    conda install -c r r-devtools  && \
    conda install -c r r-ggplot2 r-dplyr  && \
    conda install -c plotly plotly  && \
    conda install -c plotly/label/test plotly  && \ 
    conda update curl  && \
    conda install -c bioconda bcftools  && \
    conda install -c bioconda/label/cf201901 bcftools  

RUN R -e "devtools::install_github('IRkernel/IRkernel')"  && \
    R -e "IRkernel::installspec()"
  

Install S3ContentsManager to store notebooks in centralized S3 location. Although github says it should work with IAM role but I got some errors so as of now I’m using access_key_id and secret_access_key that has read/write access to S3 bucket.

#S3ContentManager Config
RUN echo 'from s3contents import S3ContentsManager' >> /etc/jupyter/jupyter_notebook_config.py  && \
    echo 'c = get_config()' >> /etc/jupyter/jupyter_notebook_config.py  && \
    echo 'c.NotebookApp.contents_manager_class = S3ContentsManager' >> /etc/jupyter/jupyter_notebook_config.py  && \
    echo 'c.S3ContentsManager.access_key_id = "xxxxxxxx"' >> /etc/jupyter/jupyter_notebook_config.py  && \
    echo 'c.S3ContentsManager.secret_access_key = "xxxxxxxx"' >> /etc/jupyter/jupyter_notebook_config.py  && \
    echo 'c.S3ContentsManager.bucket = "vishaljuypterhub"' >> /etc/jupyter/jupyter_notebook_config.py
  

JupyterHub Configuration File.

#JupyterHub Config
RUN echo "c = get_config()" >> /srv/jupyterhub/jupyterhub_config.py  && \
    echo "c.Spawner.env_keep = ['AWS_DEFAULT_REGION','AWS_EXECUTION_ENV','AWS_REGION','AWS_CONTAINER_CREDENTIALS_RELATIVE_URI','ECS_CONTAINER_METADATA_URI']" >> /srv/jupyterhub/jupyterhub_config.py  && \
    echo "c.Spawner.cmd = ['/opt/conda/bin/jupyterhub-singleuser']" >> /srv/jupyterhub/jupyterhub_config.py
  

Add PAM users

#Add PAM users
RUN useradd --create-home user3  && \
    echo "user3:user3"|chpasswd  && \
    echo "export PATH=/opt/conda/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin" >> /home/user3/.profile  && \
    mkdir -p /home/user3/.local/share/jupyter/kernels/ir  && \
    cp /root/.local/share/jupyter/kernels/ir/* /home/user3/.local/share/jupyter/kernels/ir/  && \
    chown -R user3:user3 /home/user3
  

Start JupyterHub using configuration file created earlier.

## Start jupyterhub using config file
CMD ["jupyterhub","-f","/srv/jupyterhub/jupyterhub_config.py"]
  

Implementation of Web Based Environment

1a. Create IAM Roles and Policies

Create IAM role and policy with access to PII key/folder and non-PII key/folder.

aws iam create-role --role-name "pii" --description "Allows ECS tasks to call AWS services on your behalf." --assume-role-policy-document '{"Version":"2012-10-17","Statement":[{"Sid":"","Effect":"Allow","Principal":{"Service":["ecs-tasks.amazonaws.com"]},"Action":"sts:AssumeRole"}]}' --region us-east-1
aws iam put-role-policy --policy-name "pii" --policy-document '{"Version":"2012-10-17","Statement":[{"Sid":"VisualEditor0","Effect":"Allow","Action":["s3:PutAccountPublicAccessBlock","s3:GetAccountPublicAccessBlock","s3:ListAllMyBuckets","s3:HeadBucket"],"Resource":"*"},{"Sid":"VisualEditor1","Effect":"Allow","Action":"s3:*","Resource":"arn:aws:s3:::vishaldatalake/pii/*"}]}' --role-name "pii" --region us-east-1
aws iam create-role --role-name "nonpii" --description "Allows ECS tasks to call AWS services on your behalf." --assume-role-policy-document '{"Version":"2012-10-17","Statement":[{"Sid":"","Effect":"Allow","Principal":{"Service":["ecs-tasks.amazonaws.com"]},"Action":"sts:AssumeRole"}]}' --region us-east-1
aws iam put-role-policy --policy-name "nonpii" --policy-document '{"Version":"2012-10-17","Statement":[{"Sid":"VisualEditor0","Effect":"Allow","Action":["s3:PutAccountPublicAccessBlock","s3:GetAccountPublicAccessBlock","s3:ListAllMyBuckets","s3:HeadBucket"],"Resource":"*"},{"Sid":"VisualEditor1","Effect":"Allow","Action":"s3:*","Resource":"arn:aws:s3:::vishaldatalake/nonpii/*"}]}' --role-name "nonpii" --region us-east-1

2c, 2d. Build Docker images and push it to ECR

For sake of brevity, I will skip code commit and code build and show what commands codebuild has to run. There will be two images, one will have users that needs access to PII and another one for non-PII. Instead of two repositories you can also create single repository and create two images with different tags.

cd jupyterhub1
aws ecr create-repository --repository-name jupyterhub/test1
aws ecr get-login --no-include-email --region us-east-1
docker tag jupyterhub/test1:latest xxxxxxxxx.dkr.ecr.us-east-1.amazonaws.com/jupyterhub/test1:latest
docker push xxxxxxxxx.dkr.ecr.us-east-1.amazonaws.com/jupyterhub/test1:latest

cd jupyterhub2
aws ecr create-repository --repository-name jupyterhub/test2
aws ecr get-login --no-include-email --region us-east-1
docker tag jupyterhub/test2:latest xxxxxxxxx.dkr.ecr.us-east-1.amazonaws.com/jupyterhub/test2:latest
docker push xxxxxxxxx.dkr.ecr.us-east-1.amazonaws.com/jupyterhub/test2:latest
  

ecsjh1

3a. Create ECS Cluster

Go to ECS service and click on create cluster and choose Networking only. Click on Next Step, provide cluster name and click on create. I have named cluster as jhpoc.

ecsjh2

3b. Create Task Definitions

Click on Task Definition, and Create New Task Definition. Select Launch Type compatibility as Fargate. Click on Next Step. Enter following details.

Task Definition Name: jhpocpii

Task Role: pii

Task Memory: 2GB

Task CPU: 1 vCPU

Click on Add container. Enter following details.

Container Name: jhpocpii

Image*: xxxxxxxx.dkr.ecr.us-east-1.amazonaws.com/jupyterhub/test1:latest

Port Mapping: Add 8000 and 80

Click on Add.

Click on Create.

Follow same steps now and create another task definition for nonPII. Use different role nonpii role and test2 image for container.

ecsjh3

3d. Create Application Load Balancer.

Create Target groups.

aws elbv2 create-target-group --health-check-interval-seconds 30 --health-check-path "/hub/login" --health-check-protocol "HTTP" --health-check-timeout-seconds 5 --healthy-threshold-count 5 --matcher '{"HttpCode":"200"}' --name "jhpocpii" --port 8000 --protocol "HTTP" --target-type "ip" --unhealthy-threshold-count 2 --vpc-id "vpc-0829259f1492b8986" --region us-east-1
aws elbv2 create-target-group --health-check-interval-seconds 30 --health-check-path "/hub/login" --health-check-protocol "HTTP" --health-check-timeout-seconds 5 --healthy-threshold-count 5 --matcher '{"HttpCode":"200"}' --name "jhpocnonpii" --port 8000 --protocol "HTTP" --target-type "ip" --unhealthy-threshold-count 2 --vpc-id "vpc-0829259f1492b8986" --region us-east-1

Create ALB.

aws elbv2 create-load-balancer --name "jhpocalb1" --scheme "internet-facing" --security-groups '["sg-065547ed77ac48d99"]' --subnets '["subnet-0c90f68bfcc784540","subnet-026d9b30457fcb121"]'  --ip-address-type "ipv4" --type "application" --region us-east-1
  

Create Routing Rules as follows:

ecsjh4

3c. Create ECS Services

Click on ECS cluster and on services tab click on create.

Choose Launch Type Fargate.

Choose Task Definition for PII.

Specify Service Name.

Specify Number of Tasks as 1.

Click on Next and uncheck Enable Service Discovery Integration.

Choose VPC, subnets and security group.

For Load Balancer choose ALB.

Choose Load Balancer Name from Drop down.

Choose Container to Load Balancer settings as follows:

ecsjh5

Click on Next Step.

Optionally set Auto Scaling as follows:

ecsjh17

Click on Create Service.

Create Service for PII and non-PII.

After both the services are created, wait for few minutes until there is one task running for each service.

ecsjh6

3f. Create A-records.

Create A-records in Route53.

ecsjh7

Test it

Launch jhpocpii.vishalcloud.club:8000. Login as user1 and notice user1 can only access pii data. Trying to login using user3 or user4 will result into authentication error.

ecsjh8

ecsjh9

ecsjh10

ecsjh11

Launch jhpocnonpii.vishalcloud.club:8000. Login as user4 and notice user4 can only access non-PII data. Trying to login using user1 or user2 will result into authentication error.

ecsjh12

ecsjh13

ecsjh14

ecsjh15

Test R Program.

ecsjh18

Important Additional Considerations

Cost

ECS Tasks can be launched using Fargate or EC2. Below matrix shows cost comparison of similar CPU/memory configuration between Fargate and EC2. To save cost, depending upon the type of usage pattern of environment use EC2 for persistent usage or use Fargate for adhoc usage.

ecsjh16

Security

Use certificates and consider launching ECS tasks in private subnet for security reasons.

Active Directory Integration

You can use ldap authenticator to authenticate users through AD. Create separate images with different AD group to control authorization.

January 30, 2019

ElasticSearch Authentication and Authorization at Indices level on AWS

Filed under: AWS, ElasticSearch, Uncategorized — vishaldesai @ 10:10 pm

 

Customer Requirement:

Customer XYZ is planning to host multi-tenant ElasticSearch domain to provide log analytics service to multiple clients. Customer XYZ will receive logs from different clients into their data lake and selectively push relevant logs to client specific Indices. Clients should be able to login and authenticate to Kibana portal and should only have authorization to client specific indices. There will be separate set of indices created for each client starting with standard prefix such as clientid or clientname.

Solution:

In current solution, I will demonstrate how to integrate ElasticSearch domain with Cognito. Using Cognito, customer can create users for different clients in user pool. Customer can also federate using SAML provided users are available or can be created in hosted/cloud AD. Each user either from user pool or federated will map to one of the groups in Cognito and each group will be associated with IAM role which in turn will provide authorization access to set of client specific indices.

Note: Solution will work only for dev tools in kibana and not for discover, dashboards etc

Prerequisite resources:

KMS key, VPC, subnet ids, security group should be setup prior to implementation steps.

Implementation Steps:

Step 1: Elasticsearch Domain

Create ElasticSearch Domain using CloudFormation template from Appendix A.

1001

Step 2: Sample Indices

Login to Kibana portal and create client id specific indices for testing. Kibana end point can be accessed using ec2 windows in public subnet or via proxy to bastion.


PUT orders/_doc/1
{

"user" : "order",

"post_date" : "2009-11-15T14:12:12",

"message" : "trying out order"

}


PUT customers/_doc/1

{

"user" : "customers",

"post_date" : "2009-11-15T14:12:12",

"message" : "trying out customer"

}

Step 3: Cognito

Create user pool and identity pool using CloudFormation template from Appendix A.

1003

Step 4: Cognito Domain

Create domain for Cognito user pool.


aws cognito-idp list-user-pools --max-results 50 --output text | grep espool | awk '{print $3}'

us-east-1_ldkzTlRck


--Domain name value in below cli must be unique


aws cognito-idp create-user-pool-domain --domain espooldemo --user-pool-id us-east-1_ldkzTlRck

1004.png

Step 5: Update ElasticSearch domain with Cognito configuration.


aws es update-elasticsearch-domain-config --domain-name esdomain  --cognito-options Enabled=true,UserPoolId=us-east-1_ldkzTlRck,IdentityPoolId=us-east-1:fb6e132c-3711-4974-866f-cc4a3db7d6fa,RoleArn=arn:aws:iam::xxxxxxxx:role/service-role/CognitoAccessForAmazonES

Domain status will change to processing. Once the processing is complete status will change to Active and Cognito configuration will be updated for Elasticsearch domain.

1005

Step 6: Users, Roles and policies

Create IAM policies, roles, Cognito user pool users, groups and map groups to IAM roles. Policy documents can be found in Appendix A. IAM policy documents are key on how to control authorization at indice level.


aws iam create-policy --policy-name client1_policy --policy-document file:///Users/xyz/Downloads/client1_policy.json

aws iam create-policy --policy-name client2_policy --policy-document file:///Users/xyz/Downloads/client2_policy.json


aws iam create-role --role-name client1_role --assume-role-policy-document file:///Users/desaivis/Downloads/client_policy_trust.json


aws iam create-role --role-name client2_role --assume-role-policy-document file:///Users/desaivis/Downloads/client_policy_trust.json


aws iam attach-role-policy --policy-arn arn:aws:iam::"xxxxxxxxxxx:policy/client1_policy --role-name client1_role


aws iam attach-role-policy --policy-arn arn:aws:iam::"xxxxxxxxxxx:policy/client2_policy --role-name client2_role


aws cognito-idp create-group --group-name client1_group --user-pool-id us-east-1_ldkzTlRck --role-arn arn:aws:iam::"xxxxxxxxxxx:role/client1_role


aws cognito-idp create-group --group-name client2_group --user-pool-id us-east-1_ldkzTlRck --role-arn arn:aws:iam::"xxxxxxxxxxx:role/client2_role


aws cognito-idp admin-create-user --user-pool-id us-east-1_ldkzTlRck --username client1_user --temporary-password Eskibana1#


aws cognito-idp admin-create-user --user-pool-id us-east-1_ldkzTlRck --username client2_user --temporary-password Eskibana2#


aws cognito-idp admin-add-user-to-group --user-pool-id us-east-1_ldkzTlRck --username client1_user --group-name client1_group


aws cognito-idp admin-add-user-to-group --user-pool-id us-east-1_ldkzTlRck --username client2_user --group-name client2_group

Step 7: Update ElasticSearch domain resource policy.

Find roleid using following commands:


aws iam get-role --role-name client1_role

aws iam get-role --role-name client2_role

Create policy document as per Appendix A.


aws cognito-identity get-identity-pool-roles --identity-pool-id us-east-1:fb6e132c-3711-4974-866f-cc4a3db7d6fa | jq '.Roles.authenticated'

"arn:aws:iam::xxxxxx:role/cognito-CognitoAuthorizedRole-JEUO3KGR2STO"


aws es update-elasticsearch-domain-config --domain-name esdomain  --access-policies file:///Users/xyz/Downloads/espolicy.json

Wait for cluster status to be active.

Step 8: Testing

Login as client1_user in kibana.

1007

1008

1009

Login as client2_user in kibana.

1010

1011

SAML integration:

Further, if customer wants to provide federation for AD users, identity provider can be configured using SAML using the xml file obtained from ADFS.

1012

Enable the identity provider in the App client Settings.

1013

Kibana Login page will now look like as follows:

1014

Federated identities may not map to any groups initially so federated identities can still get access denied message. Cognito pre/post authentication triggers can be configured using lambda to add external identities to Cognito groups depending upon SAML attribute values or can be added after some verification/approval process.

Appendix A:

Download

August 21, 2017

GoldenGate find size of large transaction from trail using logdump

Filed under: Golden Gate, Oracle Database — vishaldesai @ 7:12 pm

There are multiple ways to find size of large transaction but if you don’t have access to source database below is one way to find it using golden gate logdump utility.

 

	1. Using rba find end of transaction
	
	open ./dirdat/DB/GM000000234
	pos 51525307 <= start of large transaction
	usertoken on
	ggstoken detail
	detail on
	detail data
	Sfet
	
	End of Transaction found at RBA 103803427 
	
	2017/05/11 08:31:44.002.060 GGSUnifiedUpdate     Len    60 RBA 103803427 
	Name: OWNER.TABLE_NAME  (TDR Index: 10) 
	After  Image:                                             Partition 12   G  e   
	 0000 001c 0000 000a 0000 0000 0000 000f 8d74 0020 | .................t.   
	 000a ffff 0000 0000 0000 0000 0000 000a 0000 0000 | ....................  
	 0000 000f 8d74 0020 000a 0000 0000 0000 000f 8d74 | .....t. ...........t  
	Before Image          Len    32 (x00000020) 
	BeforeColumnLen     28 (x0000001c) 
	Column     0 (x0000), Len    10 (x000a)  
	 0000 0000 0000 000f 8d74                          | .........t  
	Column    32 (x0020), Len    10 (x000a)  
	 ffff 0000 0000 0000 0000                          | ..........  
	
	After Image           Len    28 (x0000001c) 
	Column     0 (x0000), Len    10 (x000a)  
	 0000 0000 0000 000f 8d74                          | .........t  
	Column    32 (x0020), Len    10 (x000a)  
	 0000 0000 0000 000f 8d74                          | .........t  
	  
	GGS tokens: 
	TokenID x52 'R' ORAROWID         Info x00  Length   20 
	 4141 416c 326c 4141 5441 4143 4650 2f41 4168 0001 | AAAl2lAATAACFP/AAh..  
	
	
	Make note of rba for end of large transaction
	
	2. Find number of rows in large transaction
	
	ghdr on
	ggstoken detail           
	--log to step1.txt           
	open ./dirdat/DB/GM000000234       
	pos 103803427   <= end of large transaction                   
	pos rev                      
	filter inc transind 0    
	n                                                        
	exit
	  
	Logdump 449 >Logdump 450 >Logdump 450 >Scanned     10000 records, RBA  102141625, 2017/05/11 08:31:44.002.030 
	Scanned     20000 records, RBA  100483423, 2017/05/11 08:31:44.004.204 
	Scanned     30000 records, RBA   98807289, 2017/05/11 08:31:44.003.072 
	Scanned     40000 records, RBA   97130025, 2017/05/11 08:31:44.004.990 
	Scanned     50000 records, RBA   95471275, 2017/05/11 08:31:44.012.356 
	Scanned     60000 records, RBA   93831447, 2017/05/11 08:31:44.018.615 
	Scanned     70000 records, RBA   92205107, 2017/05/11 08:31:44.012.011 
	Scanned     80000 records, RBA   90608113, 2017/05/11 08:31:44.012.912 
	Scanned     90000 records, RBA   88991547, 2017/05/11 08:31:44.013.783 
	Scanned    100000 records, RBA   87350643, 2017/05/11 08:31:44.014.443 
	Scanned    110000 records, RBA   85711735, 2017/05/11 08:31:44.011.865 
	Scanned    120000 records, RBA   84033389, 2017/05/11 08:31:44.014.733 
	Scanned    130000 records, RBA   82418681, 2017/05/11 08:31:44.014.494 
	Scanned    140000 records, RBA   80777203, 2017/05/11 08:31:44.014.346 
	Scanned    150000 records, RBA   79254179, 2017/05/11 08:31:44.016.142 
	Scanned    160000 records, RBA   77650931, 2017/05/11 08:31:44.024.699 
	Scanned    170000 records, RBA   76089431, 2017/05/11 08:31:43.999.983 
	Scanned    180000 records, RBA   74537491, 2017/05/11 08:31:44.012.628 
	Scanned    190000 records, RBA   73113183, 2017/05/11 08:31:44.014.765 
	Scanned    200000 records, RBA   71648821, 2017/05/11 08:31:44.014.572 
	Scanned    210000 records, RBA   70097055, 2017/05/11 08:31:44.016.426 
	Scanned    220000 records, RBA   68512477, 2017/05/11 08:31:44.013.233 
	Scanned    230000 records, RBA   66878817, 2017/05/11 08:31:44.011.231 
	Scanned    240000 records, RBA   65284733, 2017/05/11 08:31:44.016.270 
	Scanned    250000 records, RBA   63637763, 2017/05/11 08:31:44.013.952 
	Scanned    260000 records, RBA   62021021, 2017/05/11 08:31:44.011.614 
	Scanned    270000 records, RBA   60335507, 2017/05/11 08:31:44.004.826 
	Scanned    280000 records, RBA   58605025, 2017/05/11 08:31:44.006.868 
	Scanned    290000 records, RBA   56875905, 2017/05/11 08:31:44.004.518 
	Scanned    300000 records, RBA   55180519, 2017/05/11 08:31:44.003.255 
	Scanned    310000 records, RBA   53441671, 2017/05/11 08:31:43.999.957 
	Scanned    320000 records, RBA   51811087, 2017/05/11 08:31:43.999.864 
	___________________________________________________________________ 
	Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)  
	UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
	RecLength  :    60  (x003c)   IO Time    : 2017/05/11 08:31:43.999.864   
	IOType     :   134  (x86)     OrigNode   :   255  (xff) 
	TransInd   :     .  (x00)     FormatType :     R  (x52) 
	SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
	AuditRBA   :      15946       AuditPos   : 12222992 
	Continued  :     N  (x00)     RecCount   :     1  (x01) 
	
	2017/05/11 08:31:43.999.864 GGSUnifiedUpdate     Len    60 RBA 51525307 
	Name: OWNER.TABLE_NAME  (TDR Index: 10) 
	After  Image:                                             Partition 12   G  b   
	 0000 001c 0000 000a 0000 0000 0000 0014 8fa0 0020 | ...................   
	 000a ffff 0000 0000 0000 0000 0000 000a 0000 0000 | ....................  
	 0000 0014 8fa0 0020 000a 0000 0000 0000 0014 8fa0 | ....... ............  
	  
	GGS tokens: 
	TokenID x52 'R' ORAROWID         Info x00  Length   20 
	 4141 416c 326c 4141 4b41 4141 5839 4c41 4141 0001 | AAAl2lAAKAAAX9LAAA..  
	TokenID x4c 'L' LOGCSN           Info x00  Length   13 
	 3932 3834 3133 3937 3230 3334 33                  | 9284139720343  
	TokenID x36 '6' TRANID           Info x00  Length   11 
	 3136 2e34 2e31 3130 3730 35                       | 16.4.110705  
	TokenID x69 'i' ORATHREADID      Info x01  Length    2 
	 0002                                              | ..  
	   
	
	Filtering suppressed 322014 records 
	
	Filtering suppressed 322014 records
	
	3. Were there multiple tables modified in one large transaction?
	
	In step 1, make note of table name
	
	ghdr on
	ggstoken detail           
	open ./dirdat/DB/GM000000234       
	pos 103803427 <= end of large transaction           
	filter exclude filename OWNER.TABLE_NAME  
	pos rev         
	n  
	
	Logdump 457 >Scanned     10000 records, RBA  102141625, 2017/05/11 08:31:44.002.030 
	Scanned     20000 records, RBA  100483423, 2017/05/11 08:31:44.004.204 
	Scanned     30000 records, RBA   98807289, 2017/05/11 08:31:44.003.072 
	Scanned     40000 records, RBA   97130025, 2017/05/11 08:31:44.004.990 
	Scanned     50000 records, RBA   95471275, 2017/05/11 08:31:44.012.356 
	Scanned     60000 records, RBA   93831447, 2017/05/11 08:31:44.018.615 
	Scanned     70000 records, RBA   92205107, 2017/05/11 08:31:44.012.011 
	Scanned     80000 records, RBA   90608113, 2017/05/11 08:31:44.012.912 
	Scanned     90000 records, RBA   88991547, 2017/05/11 08:31:44.013.783 
	Scanned    100000 records, RBA   87350643, 2017/05/11 08:31:44.014.443 
	Scanned    110000 records, RBA   85711735, 2017/05/11 08:31:44.011.865 
	Scanned    120000 records, RBA   84033389, 2017/05/11 08:31:44.014.733 
	Scanned    130000 records, RBA   82418681, 2017/05/11 08:31:44.014.494 
	Scanned    140000 records, RBA   80777203, 2017/05/11 08:31:44.014.346 
	Scanned    150000 records, RBA   79254179, 2017/05/11 08:31:44.016.142 
	Scanned    160000 records, RBA   77650931, 2017/05/11 08:31:44.024.699 
	Scanned    170000 records, RBA   76089431, 2017/05/11 08:31:43.999.983 
	Scanned    180000 records, RBA   74537491, 2017/05/11 08:31:44.012.628 
	Scanned    190000 records, RBA   73113183, 2017/05/11 08:31:44.014.765 
	Scanned    200000 records, RBA   71648821, 2017/05/11 08:31:44.014.572 
	Scanned    210000 records, RBA   70097055, 2017/05/11 08:31:44.016.426 
	Scanned    220000 records, RBA   68512477, 2017/05/11 08:31:44.013.233 
	Scanned    230000 records, RBA   66878817, 2017/05/11 08:31:44.011.231 
	Scanned    240000 records, RBA   65284733, 2017/05/11 08:31:44.016.270 
	Scanned    250000 records, RBA   63637763, 2017/05/11 08:31:44.013.952 
	Scanned    260000 records, RBA   62021021, 2017/05/11 08:31:44.011.614 
	Scanned    270000 records, RBA   60335507, 2017/05/11 08:31:44.004.826 
	Scanned    280000 records, RBA   58605025, 2017/05/11 08:31:44.006.868 
	Scanned    290000 records, RBA   56875905, 2017/05/11 08:31:44.004.518 
	Scanned    300000 records, RBA   55180519, 2017/05/11 08:31:44.003.255 
	Scanned    310000 records, RBA   53441671, 2017/05/11 08:31:43.999.957 
	Scanned    320000 records, RBA   51811087, 2017/05/11 08:31:43.999.864 
	___________________________________________________________________ 
	Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)  
	UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
	RecLength  :   162  (x00a2)   IO Time    : 2017/05/11 08:31:34.999.737   
	IOType     :   134  (x86)     OrigNode   :   255  (xff) 
	TransInd   :     .  (x03)     FormatType :     R  (x52) 
	SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
	AuditRBA   :      15943       AuditPos   : 8348688 
	Continued  :     N  (x00)     RecCount   :     1  (x01) 
	
	2017/05/11 08:31:34.999.737 GGSUnifiedUpdate     Len   162 RBA 51525028 
	Name: GEM2.HEART_BEAT  (TDR Index: 11) 
	After  Image:                                             Partition 12   G  s   
	 0000 004f 0000 0005 0000 0001 3100 0100 1f00 0032 | ...O........1......2  
	 3031 372d 3035 2d31 313a 3130 3a33 303a 3335 2e37 | 017-05-11:10:30:35.7  
	 3235 3030 3330 3030 0002 001f 0000 3230 3137 2d30 | 25003000......2017-0  
	 352d 3131 3a31 303a 3330 3a33 352e 3732 3530 3033 | 5-11:10:30:35.725003  
	 3030 3000 0000 0500 0000 0131 0001 001f 0000 3230 | 000........1......20  
	 3137 2d30 352d 3131 3a31 303a 3331 3a33 352e 3739 | 17-05-11:10:31:35.79  
	 3035 3638 3030 3000 0200 1f00 0032 3031 372d 3035 | 0568000......2017-05  
	  
	GGS tokens: 
	TokenID x52 'R' ORAROWID         Info x00  Length   20 
	 4141 416e 3238 4141 4b41 4141 592b 2f41 4141 0001 | AAAn28AAKAAAY+/AAA..  
	TokenID x4c 'L' LOGCSN           Info x00  Length   13 
	 3932 3834 3133 3937 3230 3235 31                  | 9284139720251  
	TokenID x36 '6' TRANID           Info x00  Length   10 
	 3338 2e37 2e33 3833 3330                          | 38.7.38330  
	TokenID x69 'i' ORATHREADID      Info x01  Length    2 
	 0004                                              | ..  
	   
	
	Filtering suppressed 322015 records 
	 
	
	Rba returned by above should be less than start of large transaction.
	
	Further verify that rba is start of large transaction using rba returned by above step 3.
	
	open ./dirdat/DB/GM000000234
	pos <rba from step 3>
	usertoken on
	ggstoken detail
	detail on
	detail data
	n
	
	
	2017/05/11 08:31:34.999.737 GGSUnifiedUpdate     Len   162 RBA 51525028 
	Name: GEM2.HEART_BEAT  (TDR Index: 11) 
	After  Image:                                             Partition 12   G  s   
	 0000 004f 0000 0005 0000 0001 3100 0100 1f00 0032 | ...O........1......2  
	 3031 372d 3035 2d31 313a 3130 3a33 303a 3335 2e37 | 017-05-11:10:30:35.7  
	 3235 3030 3330 3030 0002 001f 0000 3230 3137 2d30 | 25003000......2017-0  
	 352d 3131 3a31 303a 3330 3a33 352e 3732 3530 3033 | 5-11:10:30:35.725003  
	 3030 3000 0000 0500 0000 0131 0001 001f 0000 3230 | 000........1......20  
	 3137 2d30 352d 3131 3a31 303a 3331 3a33 352e 3739 | 17-05-11:10:31:35.79  
	 3035 3638 3030 3000 0200 1f00 0032 3031 372d 3035 | 0568000......2017-05  
	Before Image          Len    83 (x00000053) 
	BeforeColumnLen     79 (x0000004f) 
	Column     0 (x0000), Len     5 (x0005)  
	 0000 0001 31                                      | ....1  
	Column     1 (x0001), Len    31 (x001f)  
	 0000 3230 3137 2d30 352d 3131 3a31 303a 3330 3a33 | ..2017-05-11:10:30:3  
	 352e 3732 3530 3033 3030 30                       | 5.725003000  
	Column     2 (x0002), Len    31 (x001f)  
	 0000 3230 3137 2d30 352d 3131 3a31 303a 3330 3a33 | ..2017-05-11:10:30:3  
	 352e 3732 3530 3033 3030 30                       | 5.725003000  
	
	After Image           Len    79 (x0000004f) 
	Column     0 (x0000), Len     5 (x0005)  
	 0000 0001 31                                      | ....1  
	Column     1 (x0001), Len    31 (x001f)  
	 0000 3230 3137 2d30 352d 3131 3a31 303a 3331 3a33 | ..2017-05-11:10:31:3  
	 352e 3739 3035 3638 3030 30                       | 5.790568000  
	Column     2 (x0002), Len    31 (x001f)  
	 0000 3230 3137 2d30 352d 3131 3a31 303a 3331 3a33 | ..2017-05-11:10:31:3  
	 352e 3739 3035 3638 3030 30                       | 5.790568000  
	  
	GGS tokens: 
	TokenID x52 'R' ORAROWID         Info x00  Length   20 
	 4141 416e 3238 4141 4b41 4141 592b 2f41 4141 0001 | AAAn28AAKAAAY+/AAA..  
	TokenID x4c 'L' LOGCSN           Info x00  Length   13 
	 3932 3834 3133 3937 3230 3235 31                  | 9284139720251  
	TokenID x36 '6' TRANID           Info x00  Length   10 
	 3338 2e37 2e33 3833 3330                          | 38.7.38330  
	TokenID x69 'i' ORATHREADID      Info x01  Length    2 
	 0004                                              | ..  
	   
	Logdump 465 >n
	
	2017/05/11 08:31:43.999.864 GGSUnifiedUpdate     Len    60 RBA 51525307 
	Name: OWNER.TABLE_NAME  (TDR Index: 10) 
	After  Image:                                             Partition 12   G  b   
	 0000 001c 0000 000a 0000 0000 0000 0014 8fa0 0020 | ...................   
	 000a ffff 0000 0000 0000 0000 0000 000a 0000 0000 | ....................  
	 0000 0014 8fa0 0020 000a 0000 0000 0000 0014 8fa0 | ....... ............  
	Before Image          Len    32 (x00000020) 
	BeforeColumnLen     28 (x0000001c) 
	Column     0 (x0000), Len    10 (x000a)  
	 0000 0000 0000 0014 8fa0                          | ..........  
	Column    32 (x0020), Len    10 (x000a)  
	 ffff 0000 0000 0000 0000                          | ..........  
	
	After Image           Len    28 (x0000001c) 
	Column     0 (x0000), Len    10 (x000a)  
	 0000 0000 0000 0014 8fa0                          | ..........  
	Column    32 (x0020), Len    10 (x000a)  
	 0000 0000 0000 0014 8fa0                          | ..........  
	  
	GGS tokens: 
	TokenID x52 'R' ORAROWID         Info x00  Length   20 
	 4141 416c 326c 4141 4b41 4141 5839 4c41 4141 0001 | AAAl2lAAKAAAX9LAAA..  
	TokenID x4c 'L' LOGCSN           Info x00  Length   13 
	 3932 3834 3133 3937 3230 3334 33                  | 9284139720343  
	TokenID x36 '6' TRANID           Info x00  Length   11 
	 3136 2e34 2e31 3130 3730 35                       | 16.4.110705  
	TokenID x69 'i' ORATHREADID      Info x01  Length    2 
	 0002                                              | ..  

Instance caging

Filed under: Oracle Database, Performance Tuning — vishaldesai @ 1:54 pm

To determine maximum CPU threads utilized we can look at top activity in Grid control, but the top activity view is limited to 24 hours. What if we want to look at weekly or monthly trends (example figure 1 below)? It will be tedious and boring task to view top activity one day at a time and then determine the maximum CPU threads utilized.

clip_image001

Figure 1

“CPU usage per sec” metric from dba_hist_sysmetric_summary can be converted to seconds and joined with other views to get Grid Control type visual.  Figure 2 is from Grid control and Figure 3 is created using maxthreadused alias from attached script.

clip_image002

Figure 2 – Grid control view

clip_image003

Figure 3 – dba_hist_sysmetric_summary view

There are additional columns in script such as average and maximum cpu threads utilized, standard deviation (lower the value meaning higher confidence in average compared to maximum). “Sum of the squared deviations from the mean” is generally used for comparison. I have not added that metric in script but can be easily modified to add that column and used for comparison of two periods if necessary. If you want to look at trends of multiple instance you can quickly add pivot/decode and plot in excel to look at trends of multiple instances.

Script attached: awr_instance_caging_new.sql

August 11, 2017

Roles and Views to restrict sensitive column data

Filed under: Oracle Database, Security — vishaldesai @ 3:07 pm

Application team wanted solution to mask sensitive column data but does not want to use VPD or change application code . Below is a quick demonstration using roles, views and synonym.

 

drop user user1 cascade;
drop user user2 cascade;
drop user nonvpd cascade;
drop role cansee;
drop role cannotsee;
drop function fn_role_enabled;

create user user1 identified by "Abc$$112" default tablespace users temporary tablespace temp;

grant connect, resource to user1;
grant create synonym to user1;

create user user2 identified by "Abc$$112" default tablespace users temporary tablespace temp;

grant connect, resource to user2;
grant create synonym to user2;

create user nonvpd identified by "Abc$$112" default tablespace users temporary tablespace temp;

grant connect, resource, dba to nonvpd;


drop table nonvpd.employees purge;

create table nonvpd.employees(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno)  
);

insert into nonvpd.employees values (300,'USER1','USER1',1,sysdate,1000,500,10);

insert into nonvpd.employees values (400,'USER2','USER2',1,sysdate,2000,500,10);

insert into nonvpd.employees values (500,'USER3','USER3',1,sysdate,3000,600,20);

commit;


create role cansee;
create role cannotsee;

create or replace function fn_role_enabled return number
is
cnumber number;
BEGIN
   IF DBMS_SESSION.IS_ROLE_ENABLED('CANSEE')
   THEN
      cnumber:=1;
   END IF;
   
      IF DBMS_SESSION.IS_ROLE_ENABLED('CANNOTSEE')
   THEN
      cnumber:=0;
   END IF;
   return cnumber;
END;
/

grant execute on fn_role_enabled to nonvpd  with grant option;

grant cansee to user2;
grant cannotsee to user1;

create view nonvpd.employees_view as
select EMPNo,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       decode(sys.fn_role_enabled,0,null,1,SAL) as SAL,
       decode(sys.fn_role_enabled,0,null,1,COMM) as COMM,
       DEPTNO
from nonvpd.employees;
;

grant select on nonvpd.employees_view to user1, user2;

conn user1/"Abc$$112"
create or replace synonym employees for nonvpd.employees_view;

conn user2/"Abc$$112"
create  or replace synonym employees for nonvpd.employees_view;

col sal format a10
col comm format a10

---connect user1
select * from employees;

     EMPNO ENAME      JOB              MGR HIREDATE  SAL                                      COMM                                         DEPTNO
---------- ---------- --------- ---------- --------- ---------------------------------------- ---------------------------------------- ----------
       300 USER1      USER1              1 19-MAY-13                                                                                           10
       400 USER2      USER2              1 19-MAY-13                                                                                           10
       500 USER3      USER3              1 19-MAY-13                                                                                           20

---connect user2
select * from employees;

     EMPNO ENAME      JOB              MGR HIREDATE  SAL                                      COMM                                         DEPTNO
---------- ---------- --------- ---------- --------- ---------------------------------------- ---------------------------------------- ----------
       300 USER1      USER1              1 19-MAY-13 1000                                     500                                              10
       400 USER2      USER2              1 19-MAY-13 2000                                     500                                              10
       500 USER3      USER3              1 19-MAY-13 3000                                     600                                              20

One using sys_context

drop user user1 cascade;
drop user user2 cascade;
drop user nonvpd cascade;
drop role cansee;
drop role cannotsee;


create user user1 identified by "Abc$$112" default tablespace users temporary tablespace temp;

grant connect, resource to user1;
grant create synonym to user1;

create user user2 identified by "Abc$$112" default tablespace users temporary tablespace temp;

grant connect, resource to user2;
grant create synonym to user2;

create user nonvpd identified by "Abc$$112" default tablespace users temporary tablespace temp;

grant connect, resource, dba to nonvpd;


drop table nonvpd.employees purge;

create table nonvpd.employees(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno)  
);

insert into nonvpd.employees values (300,'USER1','USER1',1,sysdate,1000,500,10);

insert into nonvpd.employees values (400,'USER2','USER2',1,sysdate,2000,500,10);

insert into nonvpd.employees values (500,'USER3','USER3',1,sysdate,3000,600,20);

commit;


create role cansee;
create role cannotsee;



grant cansee to user2;
grant cannotsee to user1;

create view nonvpd.employees_view as
select EMPNo,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       decode(SYS_CONTEXT('SYS_SESSION_ROLES', 'CANSEE'),'FALSE',null,'TRUE',SAL) as SAL,
       decode(SYS_CONTEXT('SYS_SESSION_ROLES', 'CANSEE'),'FALSE',null,'TRUE',SAL) as COMM,
       DEPTNO
from nonvpd.employees;
;

grant select on nonvpd.employees_view to user1, user2;

conn user1/"Abc$$112"
create or replace synonym employees for nonvpd.employees_view;

conn user2/"Abc$$112"
create  or replace synonym employees for nonvpd.employees_view;

col sal format a10
col comm format a10

conn user1/"Abc$$112"
select * from employees;

     EMPNO ENAME      JOB              MGR HIREDATE  SAL                                      COMM                                         DEPTNO
---------- ---------- --------- ---------- --------- ---------------------------------------- ---------------------------------------- ----------
       300 USER1      USER1              1 19-MAY-13                                                                                           10
       400 USER2      USER2              1 19-MAY-13                                                                                           10
       500 USER3      USER3              1 19-MAY-13                                                                                           20

conn user2/"Abc$$112"
select * from employees;

     EMPNO ENAME      JOB              MGR HIREDATE  SAL                                      COMM                                         DEPTNO
---------- ---------- --------- ---------- --------- ---------------------------------------- ---------------------------------------- ----------
       300 USER1      USER1              1 19-MAY-13 1000                                     500                                              10
       400 USER2      USER2              1 19-MAY-13 2000                                     500                                              10
       500 USER3      USER3              1 19-MAY-13 3000                                     600                                              20

VPD and roles to restrict sensitive column data

Filed under: Oracle Database, Security — vishaldesai @ 2:13 pm

Application team wanted to implement VPD using roles such that if a certain role is granted, user can see column data and if role is not granted user cannot see column data. Below is a quick demonstration.

PS I wrote this back in 2013 and little outdated compared to some 12c features.

drop user user1 cascade;
drop user user2 cascade;
drop user vpd cascade;
drop role cansee;
drop role cannotsee;

create user user1 identified by user1 default tablespace example temporary tablespace temp;

grant connect, resource to user1;

create user user2 identified by user2 default tablespace example temporary tablespace temp;

grant connect, resource to user2;


create user vpd identified by vpd default tablespace example temporary tablespace temp;

grant connect, resource, dba to vpd;


drop table vpd.employees purge;

create table vpd.employees as select * from scott.emp where 1=2;

insert into vpd.employees values (300,'USER1','USER1',1,sysdate,1000,500,10);

insert into vpd.employees values (400,'USER2','USER2',1,sysdate,2000,500,10);

insert into vpd.employees values (500,'USER3','USER3',1,sysdate,3000,600,20);

commit;


grant select on vpd.employees to user1;
grant select on vpd.employees to user2;

create or replace function fn_cannotsee(p_owner in varchar2, p_name in varchar2)
return varchar2
is
BEGIN
	IF DBMS_SESSION.IS_ROLE_ENABLED('CANNOTSEE')
	THEN
		return '1=2';
	ELSE
		return null;
	END IF;
END;
/

begin
dbms_rls.add_policy(object_schema=>'VPD',object_name=>'EMPLOYEES',
					policy_name=>'P_CANNOT_SEE',
					function_schema=>'VPD',
					policy_function=>'FN_CANNOTSEE',
					sec_relevant_cols=>'SAL,COMM',
					sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
end;
/




create role cansee;
create role cannotsee;

grant cansee to user2;
grant cannotsee to user1;


---user1
SQL> select *
from vpd.employees;  

     EMPNO ENAME      JOB              MGR HIREDATE  SAL                                      COMM                                         DEPTNO
---------- ---------- --------- ---------- --------- ---------------------------------------- ---------------------------------------- ----------
       300 USER1      USER1              1 19-MAY-13                                                                                           10
       400 USER2      USER2              1 19-MAY-13                                                                                           10
       500 USER3      USER3              1 19-MAY-13                                                                                           20

---user2
SQL> select *
from vpd.employees;  

     EMPNO ENAME      JOB              MGR HIREDATE  SAL                                      COMM                                         DEPTNO
---------- ---------- --------- ---------- --------- ---------------------------------------- ---------------------------------------- ----------
       300 USER1      USER1              1 19-MAY-13 1000                                     500                                              10
       400 USER2      USER2              1 19-MAY-13 2000                                     500                                              10
       500 USER3      USER3              1 19-MAY-13 3000                                     600                                              20
	   

August 26, 2016

Parallel Statement Queuing and parallel_force_local

Filed under: Parallel, Troubleshooting — vishaldesai @ 4:42 pm

We had batch system that starts concurrent sessions all with parallel(8). With every release more concurrent sessions are added to that job and at times few sessions are getting serialized which in turn impacts runtime of batch. Downgraded SQL that used to take few minutes now run for couple of hours and does not complete. Lot of concurrent sessions completes within range of 1-5 minutes so it would be ideal to queue the sessions until it gets all parallel slave processes instead of downgrade and missing SLA. We did not want all the features of parallel automatic tuning so I thought of quickly testing only subset of feature called parallel statement queuing.

Operational warehouse system is on 5 node RAC (non Exadata) and batch is tied to service running on single node and parallel_force_local was set to TRUE for known reasons.

Test 1: There are two instances with total 16 slave processes and parallel_force_local set to TRUE. When we submit 4 concurrent sessions with parallel(4) there was downgrade and no queuing. When we submit 4 concurrent sessions with  parallel(8), one session is queued, two are downgraded and one gets all 8 slaves.

image

 

Test 2: Now I turn off parallel_force_local and we don’t get queuing with 4 concurrent sessions and parallel(4) as there are 16 slaves available across node. When we submit 6 concurrent sessions with parallel(4) we do get queuing and eventually all processes can get requested number of slaves and there were no downgrades.

image

Test 3: If I turn on parallel_force_local (same as Test 1) we get downgrades.

image

 

Test 4: Now I left parallel_force_local turned on but set parallel_min_server, parallel_max_servers and parallel_server_Targets to 0 on second instance. So there are only 8 slaves to serve requests across the cluster. Now I get queuing for 4 concurrent threads with parallel(4) and parallel(6).

image

Test 5: Now I tried parameter instead of hint and got same behavior.

image

image

Based on above observations, I think statement queuing logic does not take into consideration parallel_force_local set to TRUE or always assumes its FALSE. When we submit concurrent requests, Oracle thinks that there are slaves available (across the cluster) but when it actually tries to execute SQL due to parallel_force_local set to TRUE it has to get all slaves from same node and there are not enough slaves so its getting downgraded.

I will check with Oracle support to find out if its bug or design limitation and update this blog.

Update from Oracle support:

Bug 21438369 is fixed in 12.2

If the system is under a high load of PFL queries, then you may see that parallel_servers_target is not conformed by Resource Manager, or may result in downgrades even.

Parallel statement queuing did not conform to parallel_servers_target when a lot of parallel_force_local (PFL) queries are run at the same time. This was happening due to the stale published load value: kxfpinaslv.

It looks like there are some ongoing problems with these settings.

Instead of parallel_force_local = true setting, dev suggests:
If they want to restrict certain jobs to certain nodes, they should do the following:
– Create a service for each job type.
– Enable the service on the nodes where it should run.
– Create a consumer group for each service and map the service to the consumer group.

June 1, 2016

Load Balancing maintenance tasks on RAC nodes using dbms_scheduler

Filed under: Tools — vishaldesai @ 2:04 am

In shared environment, with instance caging turned on, I had to perform maintenance during downtime window to reorganize few tables that are populated by AQ. I wanted to load balance sub tasks, use all available resources and complete task quickly. Here is little snippet of code I wrote to load balance tasks across multiple nodes in RAC cluster.

Create job table and populate job table with sub tasks as shown below. I didn’t had huge variation in table sizes so order was  not that important. But if there is huge outlier table in list, put them first on the list otherwise you may end up with one job at the end that starts towards the end and run for long time.

drop table job_table purge;
create table job_table( task_no number primary key, task varchar2(4000),status char(1)) tablespace users;
--status null - work to do, I - in progress, S - Success, F - Failure

insert into job_table values(1   , 'begin execute immediate ''alter table  schema_name.table_name1  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=1   ; commit; end;','');
insert into job_table values(2   , 'begin execute immediate ''alter table  schema_name.table_name2  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=2   ; commit; end;','');
insert into job_table values(3   , 'begin execute immediate ''alter table  schema_name.table_name3  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=3   ; commit; end;','');
insert into job_table values(4   , 'begin execute immediate ''alter table  schema_name.table_name4  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=4   ; commit; end;','');
insert into job_table values(5   , 'begin execute immediate ''alter table  schema_name.table_name5  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=5   ; commit; end;','');
insert into job_table values(6   , 'begin execute immediate ''alter table  schema_name.table_name6  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=6   ; commit; end;','');
insert into job_table values(7   , 'begin execute immediate ''alter table  schema_name.table_name7  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=7   ; commit; end;','');
insert into job_table values(8   , 'begin execute immediate ''alter table  schema_name.table_name8  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=8   ; commit; end;','');
insert into job_table values(9   , 'begin execute immediate ''alter table  schema_name.table_name9  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=9   ; commit; end;','');
insert into job_table values(10  , 'begin execute immediate ''alter table  schema_name.table_name10 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=10  ; commit; end;','');
insert into job_table values(11  , 'begin execute immediate ''alter table  schema_name.table_name11 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=11  ; commit; end;','');
insert into job_table values(12  , 'begin execute immediate ''alter table  schema_name.table_name12 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=12  ; commit; end;','');
insert into job_table values(13  , 'begin execute immediate ''alter table  schema_name.table_name13 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=13  ; commit; end;','');
insert into job_table values(14  , 'begin execute immediate ''alter table  schema_name.table_name14 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=14  ; commit; end;','');
insert into job_table values(15  , 'begin execute immediate ''alter table  schema_name.table_name15 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=15  ; commit; end;','');
insert into job_table values(16  , 'begin execute immediate ''alter table  schema_name.table_name16 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=16  ; commit; end;','');
insert into job_table values(17  , 'begin execute immediate ''alter table  schema_name.table_name17 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=17  ; commit; end;','');
insert into job_table values(18  , 'begin execute immediate ''alter table  schema_name.table_name18 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=18  ; commit; end;','');
insert into job_table values(19  , 'begin execute immediate ''alter table  schema_name.table_name19 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=19  ; commit; end;','');
insert into job_table values(20  , 'begin execute immediate ''alter table  schema_name.table_name20 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=20  ; commit; end;','');
insert into job_table values(21  , 'begin execute immediate ''alter table  schema_name.table_name21 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=21  ; commit; end;','');
insert into job_table values(22  , 'begin execute immediate ''alter table  schema_name.table_name22 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=22  ; commit; end;','');
insert into job_table values(23  , 'begin execute immediate ''alter table  schema_name.table_name23 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=23  ; commit; end;','');
insert into job_table values(24  , 'begin execute immediate ''alter table  schema_name.table_name24 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=24  ; commit; end;','');

commit;

 

Change number of instances in your environment, number of sub tasks that you want to run simultaneously on each node and sleep interval and execute pl/sql block.

DECLARE
   --how many instances you want to use
   v_instances            NUMBER := 3;
   --jobs on each instance. Each job is a task from job_Table
   v_jobs                 NUMBER := 2;

   v_available_instance   NUMBER := NULL;
   v_available_jobs       NUMBER := NULL;
   v_job_name             varchar2(100);
   c                      number;

   CURSOR c1
   IS
      SELECT *
        FROM job_table
      ;
BEGIN
   FOR v1 IN c1
   LOOP
      --start find available instance

         DECLARE
            CURSOR c2
            IS
               SELECT running_instance, jobs running_jobs
                 FROM (SELECT a.running_instance, a.jobs - b.jobs jobs
                         FROM (    SELECT ROWNUM running_instance, v_jobs jobs
                                     FROM DUAL
                               CONNECT BY LEVEL <= v_instances) a,
                              (  SELECT running_instance,
                                        COUNT (running_instance) jobs
                                   FROM DBA_SCHEDULER_RUNNING_JOBS
                                  WHERE job_name LIKE '%MYJOB%'
                               GROUP BY running_instance) b
                        WHERE a.running_instance = b.running_instance(+))
                WHERE jobs IS NULL OR jobs > 0;

            v_running_instance   number;
            v_running_jobs       number;
         BEGIN
            OPEN c2;
            LOOP
               FETCH c2 INTO v_running_instance, v_running_jobs;

               
                  v_available_instance := v_running_instance;
                  v_available_jobs := v_running_jobs;
                  
                        v_job_name := DBMS_SCHEDULER.generate_job_name ('MYJOB_');
                          DBMS_SCHEDULER.create_job (
                             job_name              => v_job_name,
                             job_type              => 'PLSQL_BLOCK',
                             job_action            => v1.task,
                             comments              =>    'Instance '
                                                      || v_available_instance
                                                      || ' task no '
                                                      || v1.task_no,
                             number_of_arguments   => 0,
                             start_date            => SYSTIMESTAMP,
                             repeat_interval       => NULL,
                             end_date              => NULL,
                             enabled               => FALSE,
                             auto_drop             => TRUE );
                             
                          DBMS_SCHEDULER.set_attribute (
                            name      => v_job_name,
                            attribute => 'instance_id',
                            value     => v_available_instance);   

                         DBMS_SCHEDULER.enable (name => v_job_name);
                      
                          
                          UPDATE job_table
                             SET status = 'I'
                           WHERE task_no = v1.task_no;

                          COMMIT;
                          dbms_lock.sleep(2);
                
                --EXIT WHEN c2%NOTFOUND;
                EXIT;
            END LOOP;

            CLOSE C2;
         END;

        WHILE 1 > 0 loop
            dbms_lock.sleep(5);
            SELECT count(1) into c
                 FROM (SELECT a.running_instance, a.jobs - b.jobs jobs
                         FROM (    SELECT ROWNUM running_instance, v_jobs jobs
                                     FROM DUAL
                               CONNECT BY LEVEL <= v_instances) a,
                              (  SELECT running_instance,
                                        COUNT (running_instance) jobs
                                   FROM DBA_SCHEDULER_RUNNING_JOBS
                                  WHERE job_name LIKE '%MYJOB%'
                               GROUP BY running_instance) b
                        WHERE a.running_instance = b.running_instance(+))
                WHERE jobs IS NULL OR jobs > 0;
                if c > 0 then
                exit;
                end if;
        end loop;
            
        
   END LOOP;
END;
/

 

Now sit back relax and use your favorite monitoring tool and output from following SQL statements until task is complete.

select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name like '%MYJOB%' order by actual_start_date desc;

select * from DBA_SCHEDULER_RUNNING_JOBS where job_name like '%MYJOB%';

select * from dba_scheduler_jobs where job_name like '%MYJOB%';
select * from job_table order by status;
select status,count(1) from job_table group by status;

exec dbms_scheduler.stop_job('MYJOB_11014');
exec dbms_scheduler.drop_job('MYJOB_10997');

 SELECT running_instance, jobs running_jobs
                 FROM (SELECT a.running_instance, a.jobs - b.jobs jobs
                         FROM (    SELECT ROWNUM running_instance, 1 jobs
                                     FROM DUAL
                               CONNECT BY LEVEL <= 3) a,
                              (  SELECT running_instance,
                                        COUNT (running_instance) jobs
                                   FROM DBA_SCHEDULER_RUNNING_JOBS
                                  WHERE job_name LIKE '%MYJOB%'
                               GROUP BY running_instance) b
                        WHERE a.running_instance = b.running_instance(+))
                WHERE jobs IS NULL OR jobs > 0;

I used similar approach for other long running tasks such as index rebuild, gathering statistics on tables and indexes. Other post tasks such as convert table , indexes degree back to 1 etc that does not take long time, were ran from regular sqlplus script.

Older Posts »

Blog at WordPress.com.