Source database providers for DMS Schema Conversion - DMS SCT

Source database  providers for DMS Schema Conversion - DMS SCT

=> DMS Schema Conversion supports the following data providers as sources for your migration projects.

• Microsoft SQL Server version 2008 R2, 2012, 2014, 2016, 2017, and 2019
• Oracle version 10.2 and higher, 11g and up to 12.2, 18c, and 19c, and Oracle Data Warehouse
• PostgreSQL version 9.2 and higher
• MySQL version 5.5 and higher

How to check Postgres Schema size using psql query ?

 

Check Postgres Schema size using psql query

Query1 :

SELECT     pg_catalog.pg_namespace.nspname,

           pg_size_pretty(SUM(pg_relation_size(pg_catalog.pg_class.oid))::BIGINT)

FROM       pg_catalog.pg_class

           INNER JOIN pg_catalog.pg_namespace

           ON         relnamespace = pg_catalog.pg_namespace.oid

GROUP BY   pg_catalog.pg_namespace.nspname;

Query 2 : 

with SchemaSize as

(

select ps.nspname as schema_name,

sum(pg_relation_size(pc.oid)) as total_size

from pg_class pc

join pg_catalog.pg_namespace ps

on ps.oid = pc.relnamespace

group by ps.nspname

)

select ss.schema_name,

pg_size_pretty(ss.total_size)

from SchemaSize ss

order by ss.total_size desc

limit 20;

How to generate an SSH Key Pair in Linux ?

 

Generate an SSH Key Pair in Linux 

[aaf@ip-10.0.0.0 ~]$

[aaf@ip-10.0.0.0 ~]$

[aaf@ip-10.0.0.0 ~]$

[aaf@ip-10.0.0.0 ~]$ ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/home/aaf/.ssh/id_rsa):

Created directory '/home/aaf/.ssh'.

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /home/aaf/.ssh/id_rsa

Your public key has been saved in /home/aaf/.ssh/id_rsa.pub

The key fingerprint is:

SHA256:mWZkHs4sTs3rue1E+9dQJXMN+imORLrDlh/IbJxNq4I aaf@ip-10.0.0.0.us-west-1.compute.internal

The key's randomart image is:

+---[RSA 3072]----+

|              ...|

|             .o +|

|        + . .  +.|

|       X *   . ..|

|      o S + . o. |

|     o O @ = ..  |

|     .. # B .  o |

|    E .+ B o  . .|

|       .=o+ ..   |

+----[SHA256]-----+

[aaf@ip-10.0.0.0 ~]$

[aaf@ip-10.0.0.0 ~]$

[aaf@ip-10.0.0.0 ~]$

[aaf@ip-10.0.0.0 ~]$ cat .ssh/id_rsa.pub

ssh-rsa AAAAB3NzaC1yc4ttt546regehtrhrty2EAAAADAQrgregfdvfdhfgjfjfgABAAABgQCoqxYxIvbLOHN0bkqFMe+pFxVyWk4NvD0qQ0JPUADWCujJdzwZWZls7rYwUCVOdfdsfdz3pVaqqzubHQdDVvOMB3drUcyodgfu9D6aYL+oLIHWtbZPjYsadsadada243243249Mf14ewcCOxF0bA2MaPEcZzjXdfdsya0jVJGCg85gfhgfhgfh4VKEJoOqNX82jxTLo+HbHhmwc6xmFRQqpekSLWbdsfdsfds707aZ7kp/rghghgKkDg+p+9asfsdfdsIhGcbIKc8dfdsfds554HN3V6glirulUVbtD2d0ON/DasdasdafghgghodtRzurn1Wkl972vbA79APgfhgfhgfhhWdSwyjnNqIZ8uGDH5fc6CVqnASB7gT7idWccghghhghghgghgha8d8NCzu9d3PIjSfdgfdVaBKGYEgfhgfhrgfduCLny3KdRJcjEqq24IX96prIXF7Zwfgrfrgretgrdw2323r4343tefdtregdfgffdgghghgggghghghghY01eUbxxqqUDRQtFR9KLOx73TfDRBZ8PC8YydFyFbdEpTQyJixu5nu5dtGcDVtqBVMY+f01NzdfgfdgfdgfdwYwnAH9/8Zd7Jwu+f6KE0tAR6FDm01iuQQXvAvk75rNl2szN1c3+82xgabO79yQE2S/Tdnze6OoLpyVMPb5c/ujX+dBc= aaf@ip-10.0.0.0.us-west-1.compute.internal

DMS mapping JSON ( one schema (DB1) to other schema (DB2)) - AWS

 DMS mapping task JSON - AWS 

{

    "rules": [

        {

            "rule-type": "transformation",

            "rule-id": "828748134",

            "rule-name": "828748134",

            "rule-target": "schema",

            "object-locator": {

                "schema-name": "dbo"

            },

            "parallel-load": null,

            "rule-action": "rename",

            "value": "pra_mns_pss",

            "old-value": null

        },

        {

            "rule-type": "transformation",

            "rule-id": "828155163",

            "rule-name": "828155163",

            "rule-target": "table",

            "object-locator": {

                "schema-name": "dbo",

                "table-name": "demo2"

            },

            "parallel-load": null,

            "rule-action": "rename",

            "value": "demo2",

            "old-value": null

        },

        {

            "rule-type": "selection",

            "rule-id": "828120150",

            "rule-name": "828120150",

            "object-locator": {

                "schema-name": "dbo",

                "table-name": "demo2"

            },

            "rule-action": "include",

            "filters": [],

            "parallel-load": null,

            "isAutoSegmentationChecked": false

        },

        {

            "rule-type": "selection",

            "rule-id": "820918688",

            "rule-name": "820918688",

            "object-locator": {

                "schema-name": "dbo",

                "table-name": "demo1"

            },

            "rule-action": "include",

            "filters": [],

            "parallel-load": null,

            "isAutoSegmentationChecked": false

        },

        {

            "rule-type": "transformation",

            "rule-id": "1",

            "rule-name": "1",

            "rule-target": "table",

            "object-locator": {

                "schema-name": "dbo",

                "table-name": "demo1"

            },

            "rule-action": "rename",

            "value": "demo1",

            "old-value": null

        }

    ]

}

Test Endpoint failed: Application-Status: 1020912, Application-Message: Cannot connect to ODBC provider ODBC general error., Application-Detailed-Message: RetCode: SQL_ERROR SqlState: HY000 NativeError: 1130 Message: [MySQL][ODBC 8.0(w) Driver]Host '10.1.1.12' is not allowed to connect to this MySQL server

 Test Endpoint failed: Application-Status: 1020912, Application-Message: Cannot connect to ODBC provider ODBC general error., Application-Detailed-Message: RetCode: SQL_ERROR SqlState: HY000 NativeError: 1130 Message: [MySQL][ODBC 8.0(w) Driver]Host '10.1.1.12' is not allowed to connect to this MySQL server

Solution :

You have to allow database port in Source security group and test the port using telnet 

How to Create / launch AWS EC2 instance using Terraform ?

MyFirst-EC2.tf

####Define credential to connect with providers

provider "aws" {

    region = "us-east-1"

    access_key = "Put-Your-Access_Key"

    secret_key = "Put-Your-Secret_Key"

  

}

#### Create Resource i.e. EC2 

resource "aws_instance" "MyEc2" {

 ami = "ami-05548f9cecf47b442"  

 instance_type = "t2.micro"

 

}

Terraform commands 

terraform init

terraform plan

terraform apply

AWS Oracle Classic RDS Features & Limitataion

Edition and licensing => License included (SE2) or BYOL (SE2/EE)

Multitenant PDB in CDB (licence & Maint cost reduction)  => 1 PDB

No of Database => 1 DB per RDS instance

Applications require access to underlying OS =>  N/A

Applications require access to underlying DB Env =>  N/A

sysdba privilege access  => N/A

Huge Pages (better Performance) => N/A

Local Undo => N/A

Data Guard Max Availability => with Multi AZ (no access on secondary) 

RDS database Patching =>  AWS Managed

Create a Multi-AZ deployment => Yes

Change CPU cores and threads per core on DB instance class => Yes

Turn on storage autoscaling => Yes

Automatic minor version upgrade => Yes

Kerberos authentication. => Yes

Performance Insights => Yes

TDE Encryption => Yes

Billing can be stopped => Yes

Scaling AWS          => Yes

High availability =>  AWS Managed

Database backups =>  AWS  Managed

Database software install => AWS Managed

OS patching =>  AWS Managed

OS installation => AWS Managed

Storage Limit => 64TB

Migrate Oracle Database to Any Cloud (AWS,Azure,Google,IBM etc ) Using Native Tool - Datagaurd

Oracle Database Migration to Any Cloud (AWS,Azure etc )

 Pre-communication (No-Downtime)

• Ensure and agreed with plan & cutover window 
• Ensure all stakeholder should be on same page 
• Ensure everyone copid in mail and meetings
• Ensure all issue should be documented database & application wise during iteration/testing
• Dicussed and aggred who is from available during cutover winodw (application owner,database lead,infa lead etc)   

Pre-Migration Pahse (No-Downtime) 

On Source Database 

• Delete/Remove all backup tables /unused objects from database with help of application team 
• Do username & password backup  
• Get read only access to database server
• Ensure db_unique_name is the same as the db_name
• Set and crosscheck all parameter related to dataguard configuration 
• check remote connectivity is working fine and port should be open at firewall level 
• check and validate database corruption 
• prepare & test  shared mout point  (NFS/EFS/S3FS)  
• Take the database backup including archivelogs and standby control file to create standby database
• check and backup networkfiles (tnsnames.ora,listiner.ora)
• check if wallet is configured
• check if any encryption (TDE )is enables 

Prepare Target envirnoment (No-Downtime) 

• Install oracle binrary software & Build the target database server 
• Install Grid software if required 
• Install Oracle database binary 
• Configure & enable SSH & SCP (with help of root account - Infra team)
• Configure and validate all  mount points (database/aplication)
• Configure and validate same shared  mount points (NFS/EFS/S3FS) in the target server/s.
• Restore the database backup and configure standby database 
• Incase ASM add spfile to grid software using srvctl 
• Validate remote database connctivity  
• Setup dataguard replication  from primary (source) to standby (target) database 

Migration pahse (Downtime start)

• check and validate primary and standby database in sync and no GAP 
• Stop Application associated database 
• create test user/test tablespace and test table on primary database 
• Do 4-5 archive log switch on primary (source) database .
• Again check  and validate primary and standby database in sync and no GAP
• Check active database sessions in primary (source)  database. if still acitve after stopping the application ,  kill those users  
• Create a guaranteed flashback restore point - for this enough FRA space should be available 
• Multiple way to do 
• Using manual swithover 
• Using DGMGRL ( need to configure DGMGRL )
• Using manual Failover (No rollback in this case) 
• Validate the migated database
• Setup & validate connection with application  
• Configure & update on premise DNS with new IP address (target) and create load balancer rules (if using)
• Handover to application team for validation 

Post Migration phase (No-downtime) 

• Take golden database backup after successful migration 
• Configure & schedule  database jobs 
• Configure & setup database monitoring

Rollback Phase 

• Stop the application on target database 
• Rollback the database using restore point (source database)
• Update the DNS with old entry for rollback (if required)
• Start and validate the database 
• Connect with database & start the application 

 

Amazon RDS Custom for Oracle 19c database with sysdba login

 Amazon RDS Custom for Oracle

Login as sysdba to oracle database:

-bash-4.2$

-bash-4.2$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 26 20:07:44 2023

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL>

SQL>

SQL> select name,open_mode,cdb from v$database;

NAME      OPEN_MODE            CDB

--------- -------------------- ---

DEV       READ WRITE           NO

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC     CON_ID

---------- ------------------------------ --- --- --- --- ----------

         0 SYSTEM                         YES YES YES              0

         1 SYSAUX                         YES YES YES              0

         2 UNDO_T1                        YES YES YES              0

         4 USERS                          YES YES YES              0

         5 RDSADMIN                       YES YES YES              0

         3 TEMP                           NO  YES YES              0

6 rows selected.

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/rdsdbdata/db/DEV_A/datafile/o1_mf_rdsadmin_l71tz3fk_.dbf

/rdsdbdata/db/DEV_A/datafile/o1_mf_users_l71t0bxh_.dbf

/rdsdbdata/db/DEV_A/datafile/o1_mf_undo_t1_l71t0bof_.dbf

/rdsdbdata/db/DEV_A/datafile/o1_mf_sysaux_l71t09xk_.dbf

/rdsdbdata/db/DEV_A/datafile/o1_mf_system_l71t08j1_.dbf

SQL>

How to create CEV manifest file for oracle 19c AWS RDS custom ?

 CEV for Oracle Database 19c

{

 "mediaImportTemplateVersion":"2020-08-14",

 "databaseInstallationFileNames":[

   "V982063-01.zip"

 ],

 "installationParameters":{

   "oracleHome":"/app/oracle/oracle19c",

   "oracleBase":"/app/oracle",

   "unixUid":10011,

   "unixUname":"oracle",

   "unixGroupId":10012,

   "unixGroupName":"dba"

 }

}

AWS Doc Link : https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-cev.preparing.html#oracle-cev-manifest-19c

How to get metadata details for AWS EC2 instance using curl command

 Get metadata details for AWS EC2 instance using curl command 

[root@]# curl http://169.254.169.254/2018-09-24/meta-data

ami-id

ami-launch-index

ami-manifest-path

block-device-mapping/

events/

hibernation/

hostname

identity-credentials/

instance-action

instance-id

instance-type

local-hostname

local-ipv4

mac

metrics/

network/

placement/

profile

public-hostname

public-ipv4

public-keys/

reservation-id

security-groups

services

Example: 

[root@]# curl http://169.254.169.254/2018-09-24/meta-data/security-groups

kpitdb-demo-db-sg

How to get public IP from AWS EC2 instance ?

 

Use Below command 

login as root 

curl http://checkip.amazonaws.com

[root@ip-173-11-11-11 ~]#

[root@ip-173-11-11-11~]# curl http://checkip.amazonaws.com

55.2232.1123.123341

[root@ip-173-11-11-11 ~]#

Download Terraform binary software

 

Use below link to download the Terraform 

https://www.terraform.io/downloads

User unable to login /connect with database after upgrade AWS RDS to 19c Database : Error - ORA-01017: invalid username/password

 

Users unable to login / connect with database after upgrade AWS RDS to 19c : Error - ORA-01017: invalid username/password

App users were getting below error after upgrade the AWS Oracle RDS database version 12c to 19c .

ORA-01017: invalid username/password;

Solution :

Need to update below parameter in parameter group :

 

sqlnetora.sqlnet.allowed_logon_version_server  => 8

Also check password version : 

SELECT USERNAME,ACCOUNT_STATUS,PASSWORD_VERSIONS FROM DBA_USERS;

AWS Certified Database Specialty

 

Migrate Oracle Database workload to Cloud : Re-host (Lift and Shift),Re-platform, Refactor

Migrate database workload to Cloud 

When Migrating database and application to cloud ,it is very important to keep in mind the business goals ,application and database capabilities and cost of migration .

There are different strategies for different application and database migrations goals .

• Re-host (Lift and Shift) - Oracle on EC2

    Same administration experience as on-premise   

    Full Control over the environment 

    All feature is available 

    All version is supported 

• Re-platform - Oracle on RDS

    Optimized Architecture

    Database Install and Maintenance

    Automated Patching & Upgrade 

    Automated Backup 

    High Availability 

    OS Patching & Maintenance 

    Scaling

• Refactor (Rearchitect) - Adopt Cloud Native Services

            Amazon Aurora

            Amazon Redshift

            PostgreSQL

            Other database engine 

            Eliminate Oracle Licensing Cost 

Cloud Migration Lifecycle

Cloud Database Migration Lifecycle 

Migration to cloud have two major phase :- 

• Staging 
• Cutover 

Migration Lifecycle 

========================

• Migration Assessment (3 to 5 Weeks )

Migration Readiness Assessment 

        Understand Business acceptation

        Note down current know issue, pain area & challenge

        Total Cost Of ownership (TCO)   

        Application Dependency Mapping

        Compliance & Risk  

• Migration Planning  (6-8 Weeks)

Application Portfolio Assessment

Target Architecture

Operating Model

        Build Cloud Landing Zone

Migration Plan 

Migration Wave Sequencing

• Migration (9++ Weeks)

Migrate Workload

Validate Migrated Workload

Cutover 

Decommission 

• Optimization 

Cost Optimization 

Performance Optimization 

Recommended Best Practice 

        Backup setup + Etc.

        Documentation & Knowledge Sharing 

High Level Migration Timeline 

=================================

• Migration Assessment (3 to 5 Weeks )
• Migration Planning  (6 to 8 Weeks)
• Migration + Optimization  (9++ Weeks)

*Timeline will be change and depends upon inventory and complexity 

Oracle Database Migration Tools & Techniques

Oracle Database Migration Tools & Techniques 

Oracle Native Migrations Tools 

====================================

• Data Pump

• Transportable Tablespaces

• Full Transportable Export/Import

• Data Guard

• Incremental Backups

• Oracle GoldenGate

• RMAN 

AWS Native Migration Tools 

=================================

• Database Migration Service (DMS)

• CloudEndure

• AWS Application Migration Service 

AWS Application Migration Service Error : Failed Installing the AWS Replication Agent

AWS Application Migration Service Error : Failed Installing the AWS Replication Agent

[root@ip ~]# python3 aws-replication-installer-init.py

The installation of the AWS Replication Agent has started.

AWS Region Name: XXXXXXXXXXXXXXX

AWS Access Key ID: XXXXXXXXXXXXXXX

AWS Secret Access Key:

Identifying volumes for replication.

Choose the disks you want to replicate. Your disks are: /dev/xvda

To replicate some of the disks, type the path of the disks, separated with a comma (for example, /dev/sda,/dev/sdb). To replicate all disks, press Enter:

Identified volume for replication: /dev/xvda of size 30 GiB

All volumes for replication were successfully identified.

Downloading the AWS Replication Agent onto the source server... Finished.

Installing the AWS Replication Agent onto the source server...

Error: Failed Installing the AWS Replication Agent

Installation failed.

Learn more about installation issues in our documentation at https://docs.aws.amazon.com/mgn/latest/ug/Error-Installtion-Failed.html

[root@ip ~]# 

Solutions: Install package elfutils.* ,elfutils-libelf-devel 

[root@ip ~]# python3 aws-replication-installer-init.py

The installation of the AWS Replication Agent has started.

AWS Region Name: XXXXXXXXXXXXXXX

AWS Access Key ID: XXXXXXXXXXXXXXX

AWS Secret Access Key:

Identifying volumes for replication.

Choose the disks you want to replicate. Your disks are: /dev/xvda

To replicate some of the disks, type the path of the disks, separated with a comma (for example, /dev/sda,/dev/sdb). To replicate all disks, press Enter:

Identified volume for replication: /dev/xvda of size 30 GiB

All volumes for replication were successfully identified.

Downloading the AWS Replication Agent onto the source server... Finished.

Installing the AWS Replication Agent onto the source server... Finished.

Syncing the source server with the Application Migration Service Console... Finished.

The following is the source server ID: XXXXXXXXXXXXXX

The AWS Replication Agent was successfully installed.

[root@ip ~]#

How to extend Linux file system after increasing my EBS volume on my EC2 instance

How to extend Linux file system after increasing my EBS volume on my EC2 instance

[root@anurag ~]#
[root@anurag ~]#
[root@anurag ~]# lsblk
NAME    MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
xvda    202:0    0  10G  0 disk
├─xvda1 202:1    0   1M  0 part
└─xvda2 202:2    0  10G  0 part /
xvdb    202:16   0  20G  0 disk /u01/oracle
xvdc    202:32   0  20G  0 disk /u01/oradata
xvdd    202:48   0   8G  0 disk /u01/FRA
[root@anurag ~]#
[root@anurag ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        1.9G     0  1.9G   0% /dev
tmpfs           1.9G     0  1.9G   0% /dev/shm
tmpfs           1.9G   17M  1.9G   1% /run
tmpfs           1.9G     0  1.9G   0% /sys/fs/cgroup
/dev/xvda2       10G  4.3G  5.7G  44% /
/dev/xvdc       7.9G  5.8G  1.7G  78% /u01/oradata
/dev/xvdd       7.9G  1.5G  6.0G  20% /u01/FRA
/dev/xvdb       7.9G  7.4G     0 100% /u01/oracle
tmpfs           378M     0  378M   0% /run/user/1002
tmpfs           378M     0  378M   0% /run/user/1000
[root@anurag ~]#
[root@anurag ~]#
[root@anurag ~]# resize2fs /dev/xvdb
resize2fs 1.45.4 (23-Sep-2019)
Filesystem at /dev/xvdb is mounted on /u01/oracle; on-line resizing required
old_desc_blocks = 1, new_desc_blocks = 3
The filesystem on /dev/xvdb is now 5242880 (4k) blocks long.

[root@anurag ~]#
[root@anurag ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        1.9G     0  1.9G   0% /dev
tmpfs           1.9G     0  1.9G   0% /dev/shm
tmpfs           1.9G   17M  1.9G   1% /run
tmpfs           1.9G     0  1.9G   0% /sys/fs/cgroup
/dev/xvda2       10G  4.3G  5.7G  44% /
/dev/xvdc       7.9G  5.8G  1.7G  78% /u01/oradata
/dev/xvdd       7.9G  1.5G  6.0G  20% /u01/FRA
/dev/xvdb        20G  7.5G   12G  40% /u01/oracle
tmpfs           378M     0  378M   0% /run/user/1002
tmpfs           378M     0  378M   0% /run/user/1000
[root@anurag ~]#
[root@anurag ~]# resize2fs /dev/xvdc
resize2fs 1.45.4 (23-Sep-2019)
Filesystem at /dev/xvdc is mounted on /u01/oradata; on-line resizing required
old_desc_blocks = 1, new_desc_blocks = 3
The filesystem on /dev/xvdc is now 5242880 (4k) blocks long.

[root@anurag ~]#
[root@anurag ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        1.9G     0  1.9G   0% /dev
tmpfs           1.9G     0  1.9G   0% /dev/shm
tmpfs           1.9G   17M  1.9G   1% /run
tmpfs           1.9G     0  1.9G   0% /sys/fs/cgroup
/dev/xvda2       10G  4.3G  5.7G  44% /
/dev/xvdc        20G  5.8G   14G  31% /u01/oradata
/dev/xvdd       7.9G  1.5G  6.0G  20% /u01/FRA
/dev/xvdb        20G  7.5G   12G  40% /u01/oracle
tmpfs           378M     0  378M   0% /run/user/1002
tmpfs           378M     0  378M   0% /run/user/1000
[root@anurag ~]#
[root@anurag ~]#