Oracle running on AWS EC2 and AWS RDS 2026 interview question and anwer

 Question : 15 tb oracle 19c database migration on aws ec2 provision  and calculate IOPS with precheck vpc,ebs and consideration with example and test case

Migrating a 15 TB Oracle 19c database to AWS EC2 requires precise sizing, IOPS calculation, and network preparation. Utilize AWS Database Migration Service (DMS) or Oracle Data Pump to replicate data. For a seamless cutover, establish robust pre-migration and testing phases. 

1. IOPS Calculation & EC2 Provisioning

For an active 15 TB transactional database, storage performance is critical. You need to calculate both IOPS and Throughput based on your current peak workloads.

Calculate IOPS & Throughput Requirements

Use your current AWR (Automatic Workload Repository) reports to identify your Peak IOPS:

• Formula: Total IOPS = Read IOPS + Write IOPS
• Formula: Throughput (MB/s) = (Read IOPS * Avg Read Size) + (Write IOPS *Avg Write Size)

For a 15 TB OLTP workload, you typically need to provision high baseline IOPS. If your peak requires 20,000 IOPS and 500 MB/s throughput:

• Data Files: Provision an Amazon EBS io2 Block Express volume, or use multiple gp3 volumes striped via LVM to reach the required IOPS.
• Redo Logs: Provision a dedicated io2 Block Express volume (e.g., 100 GB with 10,000 IOPS) to avoid bottlenecks during commit operations. 

EC2 Instance Selection

Select an EBS-optimized instance to ensure sufficient dedicated bandwidth between the EC2 instance and EBS volumes.

• Recommendation: Use an instance family like r6i.16xlarge (64 vCPU, 512 GB RAM) or x2iedn.16xlarge (64 vCPU, 2048 GB RAM), which provide 100 Gbps network bandwidth and up to 80,000 IOPS (depending on the instance)

VPC & Networking Configuration

Isolate your database resources by implementing these network structures:

• VPC: Create a dedicated VPC with isolated subnets for your Database and Bastion Host.
• Subnets: Deploy private subnets spanning at least two Availability Zones (AZs) for high availability.
• Security Groups: Restrict inbound traffic to port 1521 solely from your application subnets and on-premises IP ranges.
• Routing: Attach a NAT Gateway to your public subnet to allow target EC2 instances to fetch OS updates. 

or

1. Pre-Check & Requirements

Before initializing the migration, ensure the following prerequisites are met on both the on-premises source and the AWS target:

• OS & Endianness: Ensure target OS (e.g., Oracle Linux 8) matches the source platform's endianness to avoid complex, heterogeneous migrations.
• Time Zone Files: Verify the target database uses the same or a newer Oracle Time Zone (TZ) version as the source to prevent ORA-39580.
• Network Connectivity: Configure an AWS Direct Connect or Site-to-Site VPN to securely stream data.
• Database Settings: The source database must be in ARCHIVELOG mode and have FORCE LOGGING enabled. 

---

2. VPC & Networking Configuration

Isolate your database resources by implementing these network structures:

• VPC: Create a dedicated VPC with isolated subnets for your Database and Bastion Host.
• Subnets: Deploy private subnets spanning at least two Availability Zones (AZs) for high availability.
• Security Groups: Restrict inbound traffic to port 1521 solely from your application subnets and on-premises IP ranges.
• Routing: Attach a NAT Gateway to your public subnet to allow target EC2 instances to fetch OS updates. 

---

3. EBS IOPS and Throughput Calculation

Oracle databases are I/O intensive. To size your io2 or gp3 Amazon EBS volumes, calculate your requirements based on your peak AWR reports:

\(\text{Required\ IOPS}=\frac{(\text{Total\ Read\ IOPS}+\text{Total\ Write\ IOPS})\times \text{Peak\ Multiplier\ (e.g.,\ 1.5)}}{\text{Number\ of\ Volumes\ in\ ASM}}\)

Calculation Example:
Assume your peak AWR shows 20,000 Read IOPS and 5,000 Write IOPS, and you want a 50% buffer to account for performance spikes.

• Adjusted IOPS demand = (20,000 + 5,000) × 1.5 = 37,500 IOPS.
• If you stripe 4 EBS volumes using Oracle ASM, each volume needs to deliver 9,375 IOPS.
• Provisioned EBS: You will need 4 io2 volumes provisioned at 10,000 IOPS each (or gp3 provisioned at 12,000 IOPS and 1,000 MB/s throughput to meet the peak data load).

Provisioning Profile (Example for a 15 TB DB):

• Redo Logs: 3 Groups, 10 GB each (Use io2 with max IOPS/throughput to avoid log file sync waits).
• Data & Temp: 16 TB Provisioned in total across 4 x 4 TB io2 or gp3 volumes with ASM striping.
• Instance Size: Use an EC2 instance like the r6i.4xlarge or larger, which supports adequate network bandwidth and EBS-optimized throughput. 

or

Migrating a 15 TB Oracle 19c database to AWS EC2 requires precise infrastructure sizing, strict pre-migration checks, and an optimized storage layout. The most effective method is using RMAN Backup/Restore over AWS Direct Connect, combined with Oracle Data Pump or AWS DMS for incremental syncs, achieving minimal downtime. 

Pre-Check & Requirements

Before initializing the migration, ensure the following prerequisites are met on both the on-premises source and the AWS target:

• OS & Endianness: Ensure target OS (e.g., Oracle Linux 8) matches the source platform's endianness to avoid complex, heterogeneous migrations.
• Time Zone Files: Verify the target database uses the same or a newer Oracle Time Zone (TZ) version as the source to prevent ORA-39580.
• Network Connectivity: Configure an AWS Direct Connect or Site-to-Site VPN to securely stream data.
• Database Settings: The source database must be in ARCHIVELOG mode and have FORCE LOGGING enabled

1. Pre-Check & Considerations

Database Pre-Checks:

• Endianness: Verify if the source is little-endian (e.g., Linux/Windows) to match the AWS EC2 target.
• Character Set: Ensure the destination database has the same character set (or is a valid superset).
• Invalid Objects: Run @utlrp.sql on the source to compile all objects. 

Considerations:

• Architecture: Deploy a Multi-AZ architecture with Oracle Data Guard for High Availability (RPO = 0).
• ASM Configuration: Utilize Oracle ASM (Automatic Storage Management) to stripe across multiple EBS volumes. 

2. Storage Attachment & Provisioning (EC2)

For a 15 TB database, use Amazon EBS io2 Block Express volumes or a striped gp3 volume group to achieve the required IOPS and throughput. 

• Target Instance: r5.24xlarge (96 vCPU, 768 GB RAM, up to 100,000 IOPS and 7,500 MB/s throughput) or r6i.24xlarge.
• Storage Layout:
  • Redo Logs: 3 separate io2 volumes of 100 GB each (mounted across different mount points), provisioned at 10,000 IOPS each.
  • Datafiles: 10 x gp3 volumes of 2 TB each (striped using ASM).
  • Temp Tablespace: 2 x gp3 volumes of 1 TB each.
  • FRA (Flash Recovery Area): st1 (Throughput Optimized HDD) of 5 TB for backups

 

3. IOPS Calculation

To calculate the required IOPS, analyze your peak workload using AWR (Automatic Workload Repository) reports from your on-premises system.

Total IOPS=Read OPS +Write IOPS

Formula & Example:

• Read IOPS: 12,000 IOPS
• Write IOPS: 8,000 IOPS
• Calculated Peak IOPS: 12,000 + 8,000 = 20,000 IOPS
• Total Throughput: (12,000 Read IOPS × 8KB) + (8,000 Write IOPS × 8KB) = 160 MB/s

Buffer/Growth Margin: Add a 20-30% buffer. So, target 26,000 IOPS and 200 MB/s throughput.

• If using gp3, you provision this baseline statically. Each gp3 volume provides a baseline of 3,000 IOPS and 125 MB/s. Using an 8-volume ASM stripe will yield 3,000 × 8 = 24,000 IOPS and 125 × 8 = 1,000 MB/s, which easily satisfies the work

EBS Volume Selection:

• Redo Logs (Fastest): Use Amazon EBS io2 Block Express for extremely low latency, providing up to 256,000 IOPS and 4,000 MB/s throughput per volume. 
• Data / Temp Files: Use Amazon EBS gp3 volumes. You can provision 15,000 IOPS (the maximum baseline for gp3) and 1,000 MB/s throughput independently of your volume size, which is highly cost-effective. 
• Sizing: A 15 TB database requires a striped disk array (e.g., ASM with LUNs distributed over several gp3 volumes) to hit higher IOPS and aggregate throughput.

2. AWS Pre-Checks: VPC & EBS

  

• VPC & Networking: Ensure your Amazon VPC has private subnets routing through a NAT Gateway for patch downloads. Establish dedicated AWS Direct Connect or a highly reliable Site-to-Site VPN for the initial seed/migration. 
• CIDR Sizing: Ensure your VPC and subnets have enough IP addresses to support database traffic, listener, and AWS Direct Connect endpoints.
• Security Groups: Whitelist the Oracle listener port (1521) and SSH port (22) tightly between your on-premises data center and the AWS VPC.
• EBS Limits: Ensure your selected EC2 Instance (e.g., r6i.4xlarge) features dedicated EBS bandwidth. If your database needs 30,000 IOPS, the EC2 instance must support at least this rate, or the volume performance will be throttled. 

3. Considerations for 15 TB Oracle DB

• Initial Seeding: Transferring 15 TB of data over the network can take days. For large migrations, use AWS Snowball Edge for the initial backup, followed by incremental syncs.
• Database Parameters: Use Oracle Automatic Storage Management (ASM) for striping across multiple EBS volumes to balance I/O loads and prevent hot spots. 
• Instance Sizing: Choose Memory-Optimized EC2 instances (e.g., R family) as they have the CPU and RAM proportions ideally suited for Oracle Database System Global Area (SGA) caching. 

4. Migration Strategy and Example Test Case

To migrate a 15 TB database with minimal downtime, utilize a hybrid RMAN + AWS DMS (Change Data Capture) methodology. 

Example Test Case: Cutover Verification

• Objective: Validate data consistency and measure switchover downtime.

1. Initial Load: Take a full Oracle RMAN backup and restore it to AWS EC2 via Snowball or network copying.
2. Setup CDC: Use AWS Database Migration Service (AWS DMS) to continuously replicate transactions in real-time from the on-premises source to the AWS target.
3. Validation Test Run: Execute the following query on both on-prem and AWS to ensure the sequence numbers match and no data loss occurred during replication:
   sql

   SELECT name, value FROM v$sysstat WHERE name IN ('redo size', 'user commits');
   

   Switchover: Stop the application, allow DMS to catch up, finalize the cutover, and point the application to the new EC2 endpoint

Question :10 x gp3 volumes of 2 TB each (striped using ASM). for 20 tb oracle database size with example and test case

Using 10 separate 2 TB gp3 volumes striped via Oracle Automatic Storage Management (ASM) is an excellent, architecture-compliant way to host a 20 TB Oracle database. This setup maximizes AWS Elastic Block Store (EBS) performance by distributing I/O across multiple volume channels and storage backends. 

Identify Storage Architecture

ASM maps the 10 detached physical AWS EBS paths into a unified ASM Disk Group. 

• Total Raw Capacity: 10 × 2 TB = 20 TB.
• Redundancy Level: EXTERNAL (Delegates data durability to AWS EBS infrastructure, which natively mirrors blocks behind the scenes).
• ASM Allocation Unit (AU): 4M (Recommended for modern, large Oracle databases to optimize large sequence reads).
• Aggregate Max Performance Potential: Up to 10 × 16,000 = 160,000 IOPS and 10 × 1,000 MiB/s = 10,000 MiB/s (Subject to the EC2 instance's maximum EBS-optimized bandwidth throughput limits)
• 
  

Key Architectural Benefits

• Throughput Scalability: Each gp3 volume has a baseline throughput of 125 MB/s (upgradable to 1,000 MB/s). Striping across 10 volumes allows you to aggregate this bandwidth. 
• IOPS Scalability: Each gp3 volume has a baseline of 3,000 IOPS (upgradable to 16,000 IOPS). 
• ASM Efficiency: ASM handles the striping at the database layer, removing the need for OS-level Logical Volume Management (LVM) for data striping. 

---

Why This Architecture Works

• Throughput Aggregation: AWS caps a single gp3 volume at 1,000 MB/s. Stripping across 10 volumes theoretically scales your throughput potential up to the EC2 instance limit. 
• IOPS Aggregation: A single gp3 volume caps out at 16,000 IOPS. Ten volumes allow you to provision up to 160,000 IOPS provisioned at the storage tier. 
• ASM Balance: ASM natively stripes data evenly across all 10 disks. It writes "allocation units" (AUs) round-robin to ensure no single disk becomes a hot spot. 

Step-by-Step Implementation Example

1. AWS EBS Configuration

Provision 10 x gp3 volumes. For an intense production workload, you might scale performance beyond the baselines:

• Size: 2,048 GB per volume
• IOPS: 3,000 (Baseline) up to 16,000 per volume
• Throughput: 125 MB/s (Baseline) up to 1,000 MB/s per volume 

2. Linux OS Preparation

Identify the 10 NVMe/EBS devices on your EC2 instance (e.g., /dev/nvme1n1 through /dev/nvme10n1).

Create a single primary partition on each disk: 

bash

for dev in /dev/nvme[1-9]n1 /dev/nvme10n1; do
    parted -s $dev mklabel gpt mkpart primary ext4 2048s 100%
done

Initialize the disks using ASMLib or ASM Filter Driver (ASMFD) to ensure persistent naming (Example using ASMLib): 

bash

# Initialize disks
oracleasm createdisk DATA_VOL1 /dev/nvme1n1p1
oracleasm createdisk DATA_VOL2 /dev/nvme2n1p1
# ... repeat for volumes 3 through 9
oracleasm createdisk DATA_VOL10 /dev/nvme10n1p1

or

Configure Linux Prerequisites

Run these commands as the root user to prepare the attached NVMe or SCSI block storage devices. Ensure your system's multipath or udev rules bind the drives consistently. 

bash

# 1. Verify that all 10 disks are attached (e.g., /dev/nvme1n1 through /dev/nvme10n1)
lsblk

# 2. Change ownership of raw block devices to the oracle/grid user
chown oracle:oinstall /dev/nvme[1-9]n1 /dev/nvme10n1
chmod 660 /dev/nvme[1-9]n1 /dev/nvme10n1

or

Linux OS Preparation

Once attached to your Linux EC2 instance, the volumes appear as devices (e.g., /dev/nvme1n1 through /dev/nvme10n1). You prepare them using Oracle ASMFD (ASM Filter Driver) or UDEV rules to ensure persistent ownership and permissions: 

bash

# Example UDEV rule (/etc/udev/rules.d/99-oracle-asm.rules)
KERNEL=="nvme[0-9]*n1", SUBSYSTEM=="block", OWNER="oracle", GROUP="asmdba", MODE="0660"

Initialize the disks for ASM:

bash

asmcmd afd_label DATA_VOL1 /dev/nvme1n1
asmcmd afd_label DATA_VOL2 /dev/nvme2n1
# ... repeat for all 10 volumes
asmcmd afd_label DATA_VOL10 /dev/nvme10n1

3. Oracle ASM Diskgroup Creation

Log into the ASM instance (+ASM) as SYSASM and create a COARSE striped diskgroup (default 1MB allocation unit) for data files. For Oracle ASM, EXTERNAL REDUNDANCY is ideal here because AWS gp3 volumes already have built-in physical storage replication. 

sql

CREATE DISKGROUP data EXTERNAL REDUNDANCY
  DISK
    '/dev/oracleasm/disks/DATA_VOL1',
    '/dev/oracleasm/disks/DATA_VOL2',
    '/dev/oracleasm/disks/DATA_VOL3',
    '/dev/oracleasm/disks/DATA_VOL4',
    '/dev/oracleasm/disks/DATA_VOL5',
    '/dev/oracleasm/disks/DATA_VOL6',
    '/dev/oracleasm/disks/DATA_VOL7',
    '/dev/oracleasm/disks/DATA_VOL8',
    '/dev/oracleasm/disks/DATA_VOL9',
    '/dev/oracleasm/disks/DATA_VOL10'
  ATTRIBUTE 'au_size'='4M';

(Note: Setting au_size to 4M is highly recommended for large 20 TB databases to optimize sequential scan performance on sequential cloud block storage). 

---

Test Case: Validating Layout and I/O Distribution

To prove that Oracle is evenly striping data across all 10 gp3 volumes, you can run a simple heavy-write test case using a large tablespace.

Phase 1: Create a Test Tablespace

Log into your Oracle Database instance as SYSDBA and create a massive tablespace inside the new ASM diskgroup. 

sql

CREATE TABLESPACE io_test_ts 
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;

Phase 2: Run a Heavy I/O Generation Script

Create a table and populate it with enough data to trigger sustained disk writes.

sql

CREATE TABLE io_heavy_alloc (
    id NUMBER,
    payload padding VARCHAR2(4000)
) TABLESPACE io_test_ts;

-- Insert a massive loop block to force allocations and writes
BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT /*+ APPEND */ INTO io_heavy_alloc 
        VALUES (i, RPAD('X', 3900, 'X'));
        IF MOD(i, 50000) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
END;
/

Phase 3: Verify the Striping via ASM Performance Views

While the script runs (or immediately after), query the V$ASM_DISK_IOSTAT view. If ASM striping is working correctly, you will see a perfectly balanced distribution of reads and writes across all 10 disk paths. 

sql

SELECT 
    disk_number,
    name,
    reads,
    writes,
    read_bytes/1024/1024 AS read_mb,
    write_bytes/1024/1024 AS write_mb
FROM 
    v$asm_disk_stat
WHERE 
    group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name = 'DATA')
ORDER BY 
    disk_number;

Expected Result Output:

The columns writes and write_mb should show nearly identical numeric values across disk_number 0 through 9, validating that ASM is round-robining the 4MB Allocation Units flawlessly across your entire 10-volume gp3 array.

Monitor and Validate the Stripe

While the query is running, open a separate Linux terminal and run iostat to watch the physical disks.

bash

iostat -xmdz 1

Expected Test Results Output:
You should see all 10 underlying NVMe devices showing roughly identical read metrics, confirming the 10-way ASM stripe is functioning perfectly:
text
Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s alloc%  %util
nvme1n1           0.00     0.00  3200.0    0.00   400.00     0.00   0.08  85.20
nvme2n1           0.00     0.00  3201.0    0.00   400.12     0.00   0.08  85.15
nvme3n1           0.00     0.00  3199.0    0.00   399.85     0.00   0.08  85.30
...
nvme10n1          0.00     0.00  3202.0    0.00   400.05     0.00   0.08  85.1

or

Migrating a 20 TB Oracle 19c database to AWS EC2 requires strict sizing of compute and storage resources. You must provision sufficient EBS IOPS to avoid bottlenecking, set up a secure VPC, and use Oracle RMAN or AWS DMS for large dataset transfers. [1, 2, 3, 4, 5]

1. Provisioning & Storage Attachment

For a 20 TB database, you need to use Oracle ASM (Automatic Storage Management) or Linux LVM striping across multiple EBS volumes to achieve optimal throughput and capacity.

• EC2 Instance: Use memory-optimized instances like r5.24xlarge or r6i.32xlarge to support high EBS bandwidth (up to 19 Gbps).
• Storage Attachment (EBS): Use io2 Block Express for consistent, sub-millisecond latency.
  • Data Files: 4 × 2 TB io2 volumes (provisioned at 16,000 IOPS each).
  • Redo Logs: 3 × 100 GB io1 or io2 volumes (placed in their own ASM disk group for maximum throughput).
  • FRA/Backups: 2 × 5 TB gp3 volumes (good baseline performance).

2. Calculating IOPS

For an Oracle OLTP database, IOPS is calculated based on the block size and transaction throughput.

• Formula: \(IOPS = \frac{\text{Throughput (MB/s)}}{\text{Block Size (MB)}}\)
• Example Calculation: If your system processes peak workloads of 5,000 MB/sec, with an Oracle block size of 8 KB, the raw IOPS requirement is calculated as:
  5000 ÷ 0.008 = 625,000 IOPS.
• AWS Strategy: Divide this total across multiple ASM disks. If one io2 Block Express volume caps out at 256,000 IOPS or 4,000 MB/s, you must stripe 4 volumes together in ASM to hit 1,000,000 IOPS and bypass volume limits.

3. Precheck: VPC, EBS, and Considerations

• VPC Setup: Ensure you use private subnets. Attach a NAT Gateway only if your migration servers require temporary external internet access to reach the AWS Management Console. 
• Network Baseline: Ensure you have an AWS Direct Connect (1 Gbps to 10 Gbps) or VPN setup if transferring data from an on-premises data center. A 20 TB transfer takes ~18 hours over 10 Gbps. Consider AWS Snowball Edge if bandwidth is below 1 Gbps.
• Architecture Rules: Ensure EBS-optimized is enabled on the EC2 instances. Do not place OS and Database data on the same volume.

4. Migration Strategy Example & Test Cases

Migration Strategy: Use Oracle Transportable Tablespaces (TTS) or RMAN Incremental Backups (for minimal downtime).

1. Initial Backup: Take a full RMAN backup of the source database.
2. Transfer: Ship backup pieces to an Amazon S3 bucket, then copy them to the target EC2 EBS volumes.
3. Restore: Restore the database onto the AWS EC2 instance.
4. Catch-up: Run RMAN incremental backups to sync the EC2 database with the source on-premise system.

Post-Migration Test Cases:

• Test Case 1: Data Integrity Validation: Run RMAN VALIDATE DATABASE and compare SCN on the source and destination.
• Test Case 2: Performance Benchmark: Execute the Oracle AWR (Automatic Workload Repository) report to identify wait events (e.g., db file sequential read).
• Test Case 3: Application Failover: Reroute read-only connections and test listener connectivity over the new VPC Endpoints.

5. Challenges and Post-Migration Issues

• Endinanness Issue: If migrating from on-premises AIX/Solaris to Linux (x86-64), the platforms have different Endianness. Solution: Use Oracle RMAN CONVERT or AWS DMS for schema and data translation. 
• Performance Degradation due to Throttling: EBS volumes may get throttled (burst credits run out) if IOPS is not properly provisioned. 
• Network Latency: Application servers migrating alongside the database might experience latency if not placed in the same Availability Zone. 

6. Troubleshooting Steps

• Issue 1: Slow Data Transfer/RMAN Restores:
  • Troubleshooting: Run sar -n DEV 1 10 to check network bandwidth saturation. Increase RMAN channels using the command: CONFIGURE DEVICE TYPE DISK PARALLELISM 8;. 
• Issue 2: ASM Disk Group Won't Mount:
  • Troubleshooting: Check permission on block devices (ls -l /dev/sd*). Verify asm_diskstring points to the correct multipath devices in your init.ora file.
• Issue 3: High db file scattered read waits after migration:
• Troubleshooting: Gather fresh system and table statistics using EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; and EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;.

Question : post migration oracle 19c database on aws ec2 and how to automate dba routine task with ansible example and test case

or

Migrating a 20 TB Oracle 19c database to AWS EC2 requires precise infrastructure sizing (using Provisioned IOPS io2 or gp3 volumes) and an optimal hybrid migration strategy (e.g., RMAN cross-platform backup or AWS DMS). Careful storage striping with Oracle ASM is essential to prevent latency or IO bottlenecks. [1]

---

1. EC2 Provisioning & Storage Attachment

For a 20 TB database, you need to provision enterprise-grade instances with high memory and network bandwidth.

• Instance Type: r5.24xlarge or r6i.24xlarge (96 vCPU, 768 GiB RAM) for compute.
• Storage Configuration: Do not use a single massive EBS volume. Use Oracle Automatic Storage Management (ASM) or Linux LVM to stripe across multiple EBS volumes to aggregate IOPS and Throughput limits.
• EBS Type: Provision io2 Block Express for latency-sensitive core systems or gp3 for high-balance cost-performance. 

2. IOPS & Throughput Calculation

Oracle databases are sensitive to disk latency. You must calculate IOPS to meet your peak AWR workload. 

• Example Calculation:
  If your peak workload hits 15,000 Read IOPS and 5,000 Write IOPS, you need 20,000 Provisioned IOPS.
  If using gp3 (which allows up to 16,000 IOPS per volume), you can attach four 4TB gp3 volumes, each capped at 5,000 IOPS, and use Oracle ASM to stripe them. This easily achieves your 20,000 IOPS while maintaining a predictable budget.

3. Prechecks & Considerations

• VPC & Subnet: Ensure a dedicated VPC with private subnets for DB servers, secure Bastion hosts, and VPN or Direct Connect (min 1 Gbps to 10 Gbps) to transfer base backups.
• Source DB Check: Run the DBMS_TTS or RMAN prechecks. For OS/Platform changes (e.g., AIX to Linux), check Endian format compatibility.
• Character Set: The target and source database must have identical character sets or the target must be a superset. 

4. Migration Example & Test Cases

Strategy: Transportable Tablespace (TTS) + RMAN Incremental Backups (Best for near-zero downtime of 20TB). 

Migration Steps:

1. Provision EC2 and configure Oracle ASM.
2. Take an RMAN incremental backup of the source database.
3. Transfer backup pieces via AWS Direct Connect to Amazon S3 or mount directly on EC2.
4. Restore the base backup on target EC2.
5. Apply consecutive RMAN incremental backups to sync the databases.
6. Perform final cutover by stopping source DB activity and applying the final incremental backup.

Test Cases:

• Validation Test: Verify DB_NAME, DBID, and GLOBAL_NAME match perfectly.
• Performance Test: Execute AWR reports on the EC2 target against simulated read/write volumes.
• Application Cutover Test: Execute read-only tests with application teams pointing to the newly synced EC2 target. 

5. Challenges

• Network Bottleneck: Transferring 20 TB over the wire takes significant time. A 1 Gbps connection fully utilized takes ~47 hours; a 10 Gbps line takes ~4.7 hours.
• Endianness: If migrating from Big-Endian (Solaris/AIX) to Little-Endian (Linux), datafiles must be converted using RMAN CONVERT.
• SGA/PGA Alignment: Sizing EC2 memory correctly to avoid memory swapping or latch contention. 

6. Post-Migration Issues & Troubleshooting

1. Issue: High db file scattered read or db file sequential read waits.
   • Troubleshooting: Gather fresh system and table statistics using DBMS_STATS.GATHER_DATABASE_STATS. Check if EBS Throughput limit (e.g., 1000 MB/sec on r6i.24xlarge) is being throttled by running iostat -x 5 at the OS level.
2. Issue: Sequence Cache Contention (Wait Event: sequence cache latch).
   • Troubleshooting: Migrations sometimes trigger sequence cache exhaustion. Check source sequence caches, increase the CACHE values on high-usage sequences (e.g., CACHE 1000). 
3. Issue: Invalid PL/SQL or Views after Cross-Platform Conversion.
• Troubleshooting: Run @?/rdbms/admin/utlrp.sql as SYSDBA to recompile all invalid objects.

Question : post migration oracle 19c database on aws ec2 and how to automate dba routine task with ansible example and test case

Post-Migration Oracle 19c Checklist on AWS EC2

After migrating an Oracle 19c database to AWS EC2, you must perform several validation and optimization steps to ensure stability, performance, and security. 

1. Performance Verification

• Gather fresh dictionary and schema statistics (DBMS_STATS.GATHER_DICTIONARY_STATS).

and Run dbms_stats.gather_database_stats

• Rebuild invalid objects (utlrp.sql).
• Verify CPU, memory, and IOPS allocations match source requirements.
• Check AWS EBS volume initialization status to avoid first-touch performance penalties.
• Verify AWS EBS Volume Types: Ensure Data and Redo logs use gp3 (with optimized IOPS/Throughput) or io2 volumes.
• Enable HugePages: Configure Linux HugePages to match your Oracle SGA to prevent memory paging.
• Gather Dictionary Statistics: Run DBMS_STATS.GATHER_DICTIONARY_STATS so the optimizer has accurate post-migration metadata.
• Check CPU Initialization: Verify that SGA_TARGET and PGA_AGGREGATE_TARGET align with your EC2 instance memory limits. 
•  Row Counts: Validate object and row counts between source and target.
• Registry Check: Verify component statuses in dba_registry.

2. AWS Infrastructure & Backup Validation

• Verify Oracle Archive Log mode is enabled and writing to the correct mount point.
• Configure AWS Backup or RMAN: Set up automated backups to AWS S3 using the AWS Storage Gateway or Oracle Secure Backup Cloud Module.
• Confirm AWS Backup or EBS snapshot lifecycle policies are active.
• Monitoring: Set up Amazon CloudWatch agents to track EC2 OS metrics (CPU, Memory, Disk I/O).. 

3. Security & Networking

• Restrict Oracle Listener ports (default 1521) using AWS Security Groups.
• Verify Transparent Data Encryption (TDE) wallets are open and auto-login is functional.
• Audit user privileges and update passwords for administrative accounts. 

---

Automating DBA Routine Tasks with Ansible 

Ansible allows you to automate repetitive tasks like checking database status, managing tablespaces, and cleaning up old logs across multiple EC2 instances. 

Playbook Example: Oracle 19c Health Check & Maintenance

Below is a production-ready Ansible playbook that checks the database status, verifies tablespace usage, and purges older audit logs.

yaml

---
- name: Oracle 19c Post-Migration Routine Maintenance
  hosts: oracle_ec2_instances
  become: yes
  become_user: oracle
  vars:
    oracle_home: "/u01/app/oracle/product/19.0.0/dbhome_1"
    oracle_sid: "ORCL"
    audit_cleanup_days: 30

  environment:
    ORACLE_HOME: "{{ oracle_home }}"
    ORACLE_SID: "{{ oracle_sid }}"
    PATH: "{{ oracle_home }}/bin:{{ ans_env.PATH | default('/usr/local/bin:/usr/bin:/bin') }}"

  tasks:
    - name: Verify Oracle Database Instance Status
      ansible.builtin.shell: |
        sqlplus -s / as sysdba <<EOF
        SET HEAD OFF FEEDBACK OFF SERVEROUTPUT ON;
        SELECT status FROM v\$instance;
        EXIT;
        EOF
      register: db_status
      changed_when: false

    - name: Fail playbook if Database is not OPEN
      ansible.builtin.fail:
        msg: "Database {{ oracle_sid }} is not in OPEN state! Current state: {{ db_status.stdout | trim }}"
      when: "'OPEN' not in db_status.stdout"

    - name: Check Tablespace Usage (Alert if > 85%)
      ansible.builtin.shell: |
        sqlplus -s / as sysdba <<EOF
        SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF;
        SELECT tablespace_name FROM dba_data_files 
        GROUP BY tablespace_name 
        HAVING (SUM(bytes)/1024/1024) > 85;
        EXIT;
        EOF
      register: high_tablespaces
      changed_when: false

    - name: Report High Tablespace Usage
      ansible.builtin.debug:
        msg: "Warning: Tablespace {{ item }} exceeds 85% utilization!"
      loop: "{{ high_tablespaces.stdout_lines }}"
      when: high_tablespaces.stdout_lines | length > 0

    - name: Purge Operating System Audit Logs older than threshold
      ansible.builtin.find:
        paths: "/u01/app/oracle/admin/{{ oracle_sid }}/adump"
        patterns: "*.aud"
        age: "{{ audit_cleanup_days }}d"
      register: files_to_delete

    - name: Delete old .aud files
      ansible.builtin.file:
        path: "{{ item.path }}"
        state: absent
      loop: "{{ files_to_delete.files }}"
      when: files_to_delete.matched > 0

Test Case Validation

To ensure your automation works safely without modifying production data unexpectedly, implement a strict testing pipeline.

1. Setup the Test Environment

• Spin up an identical staging EC2 instance using an AMIs/EBS snapshot of your production Oracle setup.
• Create a dedicated Ansible inventory file (hosts.test) pointing only to your test instance.

2. Test Execution Steps

Execute the playbook in Check Mode (dry-run) first to see what changes would be made without applying them:

bash

ansible-playbook -i hosts.test oracle_maintenance.yml --check

Execute the playbook normally on your test environment:

bash

ansible-playbook -i hosts.test oracle_maintenance.yml

3. Verification Assertions

Verify the test case succeeded by validating these conditions on the test instance:

• Database Status Check: Intentionally shut down the test database (shutdown immediate) and re-run the playbook. Confirm that Ansible throws the expected failure message: “Database ORCL is not in OPEN state!” 
• Audit File Purge Check: Create a dummy audit file with an older timestamp using touch -d "40 days ago" /u01/app/oracle/admin/ORCL/adump/test_audit.aud. Run the playbook and verify that this specific file is removed, while files newer than 30 days remain untouched.

or

Automating DBA Tasks with Ansible [1]

Ansible automates routine Oracle tasks by executing playbooks against your EC2 instances.

1. Directory Structure

text

oracle-dba-automation/
├── inventory.ini
├── playbook.yml
└── test_query.sql

Use code with caution.

2. Inventory Configuration (inventory.ini)

ini

[oracle_servers]
ec2-oracle-prod ansible_host=192.168.1.50 ansible_user=ec2-user ansible_ssh_private_key_file=~/.ssh/id_rsa

Use code with caution.

3. Ansible Playbook (playbook.yml)

This playbook checks database connectivity, gathers dictionary statistics, and verifies the listener state.

yaml

---
- name: Post-Migration Oracle DBA Routine Automation
  hosts: oracle_servers
  become: yes
  become_user: oracle
  vars:
    oracle_home: "/u01/app/oracle/product/19.0.0/dbhome_1"
    oracle_sid: "ORCL"
    ansible_env_vars:
      ORACLE_HOME: "{{ oracle_home }}"
      ORACLE_SID: "{{ oracle_sid }}"
      PATH: "{{ oracle_home }}/bin:{{ ansible_env_vars.PATH | default('/usr/local/bin:/usr/bin:/bin') }}"

  tasks:
    - name: Verify Oracle Listener status
      ansible.builtin.command: "{{ oracle_home }}/bin/lsnrctl status"
      register: listener_status
      changed_when: false

    - name: Display Listener Status
      ansible.builtin.debug:
        var: listener_status.stdout_lines

    - name: Copy SQL verification script to server
      ansible.builtin.copy:
        src: test_query.sql
        dest: /tmp/test_query.sql
        owner: oracle
        group: oinstall
        mode: '0644'

    - name: Execute Database Health Check and Statistics Gathering
      ansible.builtin.shell: |
        export ORACLE_HOME={{ oracle_home }}
        export ORACLE_SID={{ oracle_sid }}
        export PATH=$ORACLE_HOME/bin:$PATH
        sqlplus -s / as sysdba <<EOF
        SET PAGESIZE 50 FEEDBACK OFF ECHO OFF
        @/tmp/test_query.sql
        EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
        EXIT;
        EOF
      register: sqlplus_output
      changed_when: "'STATS.GATHER_DICTIONARY_STATS' in sqlplus_output.stdout"

    - name: Display Database Health Check Output
      ansible.builtin.debug:
        var: sqlplus_output.stdout_lines

    - name: Clean up temporary SQL script
      ansible.builtin.file:
        path: /tmp/test_query.sql
        state: absent

Use code with caution.

4. Verification Script (test_query.sql)

sql

SELECT instance_name, status, database_status FROM v$instance;
SELECT name, open_mode FROM v$database;

Use code with caution.

---

Test Case & Execution

Execution Command

Run the playbook from your control machine:

bash

ansible-playbook -i inventory.ini playbook.yml

Use code with caution.

Expected Output Test Case

A successful test execution will return the following output blocks in your terminal:

json

TASK [Display Database Health Check Output] ************************************
ok: [ec2-oracle-prod] => {
    "sqlplus_output.stdout_lines": [
        "INSTANCE_NAME    STATUS       DATABASE_STATUS",
        "---------------- ------------ ----------------",
        "ORCL             OPEN         ACTIVE",
        "",
        "NAME      OPEN_MODE",
        "--------- --------------------",
        "ORCL      READ WRITE",
        "",
        "PL/SQL procedure successfully completed."
    ]
}

PLAY RECAP *********************************************************************
ec2-oracle-prod            : ok=6    changed=1    unreachable=0    failed=0

Use code with caution.