On AWS, would you use RDS or EC2 for Oracle deployments?
Answer:
Use Amazon RDS for Oracle when you want managed operations (automated backups, patching, Multi‑AZ HA, monitoring) and can live within RDS feature boundaries (no OS access, no custom ASM, no RAC, limited filesystem tweaks, specific versions/patch cadence). Choose EC2 when you need full control—custom ASM layout, Oracle RAC, Data Guard with bespoke protection modes, custom backup flows (e.g., RMAN + S3 lifecycle), OS/kernel tuning, specialized agents, or third‑party storage. For BYOL and Oracle feature parity (including TDE, partitioning, diagnostics), both are viable, but EC2 is preferred for complex enterprise estates and RDS for standardized, fast-to-operate workloads. A common pattern: Prod on EC2 for control, Dev/Test on RDS for velocity.
How do you manage high availability and failover on AWS?
Answer:
- RDS for Oracle: Enable Multi‑AZ (single‑AZ primary with synchronous standby in another AZ, automatic failover); for read scaling use read replicas (if your edition supports it). Combine with RDS Blue/Green for low-risk cutovers during major changes.
- EC2 for Oracle: Use Oracle Data Guard (Maximum Availability/Performance as appropriate). For automatic failover, enable Fast‑Start Failover (FSFO) with Data Guard Broker; place primary/standby across distinct AZs with independent failure domains and separate EBS volumes. Optionally add observer outside the primary AZ (or outside the Region if using cross‑Region DR). If you require RAC, deploy across multiple subnets/AZs with shared storage (e.g., FSx for ONTAP/Lustre or third‑party); RAC handles node failures while Data Guard handles site/AZ failures. Integrate Route 53/app‑side connection string failover and srvctl/SCAN (for RAC).
What’s your method for automated backup and restore on AWS?
Answer:
- RDS: Turn on automated backups (retention policy + PITR) and scheduled snapshots; export snapshots to S3 (for long‑term retention/archival). Validate restores via automated runbooks (EventBridge → Lambda → create temp instance, run smoke tests, destroy).
- EC2: Use RMAN to S3 via the AWS Backint Agent for Oracle or RMAN to EBS snapshots + S3 lifecycle for archives. Maintain catalog (separate repository DB).
- Automate via AWS Systems Manager (SSM) Automation documents scheduled by EventBridge; record backup metadata to DynamoDB for inventory. Test restores monthly with in-place (to alternate SID) and out-of-place (to a new host/Region) workflows; verify PITR, redo apply, and TDE wallet recovery. Apply S3 lifecycle (Standard → IA → Glacier) to control costs.
How do you control costs while running Oracle on AWS?
Answer:
- Right-size instances (compute/IO) with AWR/ASH evidence; move to gp3 EBS with tuned IOPS/throughput; choose io2 only where latency-sensitive.
- Use Savings Plans/Reserved Instances for steady workloads; Stop non-prod nightly with SSM automation.
- Optimize licensing: consolidate cores with high clock/low vCPU where feasible; use BYOL only on approved hypervisors; for RDS consider License Included where cost-effective.
- Archive RMAN backups aggressively to Glacier; dedupe w/ block change tracking.
- Reduce cross‑Region replication only to required datasets; compress Data Guard redo.
- Implement tagging + Cost Explorer + budgets and alerts per environment/team.
How do you secure Oracle on AWS using network and IAM controls?
Answer:
- Network: Place DBs in private subnets; restrict ingress with Security Groups (only app subnets/hosts), lock down NACLs, and use VPC endpoints for S3/KMS. For cross‑VPC access, prefer Transit Gateway or PrivateLink; avoid public IPs.
- Encryption: Enable TDE for Oracle; manage keys in AWS KMS (CMKs with rotation). Enforce in-transit TLS; RDS supports SSL out of the box; for EC2, configure TCPS/SQL*Net with wallets.
- Secrets: Store DB creds in AWS Secrets Manager with automatic rotation (via Lambda) and IAM-scoped retrieval.
- Access control: Use IAM roles for EC2/RDS features (S3 access for backups, CloudWatch, SSM). Implement least privilege and ABAC via tags.
- Audit: Turn on Oracle Unified Auditing to CloudWatch Logs/S3, integrate with SIEM (Splunk), and monitor CloudTrail for control-plane actions.
How do you build an SLOB-generated workload to test performance?
Answer:
- Prep: Create a dedicated tablespace, users, and configure redo/undo/temp appropriately; set filesystem/ASM striping; pre‑warm EBS volumes.
- Install: Provision SLOB on a bastion/driver host; configure slob.conf (think time, readers/writers mix, scale of IOPS).
- Load: Run setup.sh to create and load tables (multiple schemas for parallelism).
- Execute: Launch runit.sh with different settings (pure read, mixed 80/20, write‑heavy) while capturing AWR baselines before/after, OS metrics (iostat, vmstat, sar), and CloudWatch (EBS volume metrics).
- Analyze: Compare db file sequential read, log file sync, cell single block read (if Exa), PGA/SGA usage, IOPS/latency ceilings, queue depths; iterate on EBS IOPS, ASM striping, and Oracle init.ora.
What’s your process for patch testing in production-like environments?
Answer:
- Clone prod to a masking-compliant staging env (Data Guard snapshot standby for EC2; RDS Blue/Green or snapshot restore for RDS).
- Apply PSU/RU patches with opatchauto/Fleet Patching and Provisioning (FPP) on EC2; for RDS, use the maintenance window or Blue/Green.
- Run functional and performance suites (AWR diff reports, SLOB/replay).
- Validate backup/restore, Data Guard sync, FSFO, TDE wallets, and app connectivity.
- Approve via CAB, then rolling apply (Data Guard switchover or Blue/Green) to minimize downtime.
How do you automate health‑check reports across hundreds of DBs?
Answer:
- Maintain an inventory (DynamoDB/Parameter Store).
- Use SSM Run Command/State Manager to execute standardized SQL scripts across hosts (or RDS Automation with EventBridge).
- Collect AWR Top Events, wait class summaries, redo rates, FRA usage, backup freshness, invalid objects, tablespace headroom, and Data Guard lag into S3 (CSV/JSON).
- Transform with Athena/Glue; push daily rollups and exceptions to CloudWatch dashboards and SNS alerts.
- Optionally, publish to Splunk via HEC for dashboards and anomaly detection.
Describe your experience integrating Oracle with external tools (e.g., Splunk).
Answer:
- Logs & Audit: Stream Oracle alert/audit logs to CloudWatch Logs (EC2 via CloudWatch Agent; RDS via built-in exports), then forward to Splunk (Kinesis Firehose → Splunk HEC or Splunk add-on for CloudWatch).
- Metrics: Export AWR summaries and custom v$ views via scheduled jobs; push to CloudWatch/SNS and Splunk for trending.
- DB Connect: For query-based monitoring, use Splunk DB Connect with read-only accounts and resource limits (profiles).
- Security: Ensure pseudonymization/masking for PII before export; segregate indexes and enforce least privilege on read access.
How do you handle cross‑platform migrations (Windows ⟷ Linux)?
Answer:
- For minimal downtime: Oracle GoldenGate bi‑directional or uni‑directional replication until cutover.
- For bulk move: Data Pump (expdp/impdp) + RMAN Cross‑Platform Transportable Tablespaces (TTS) with endian conversion; validate constraints, statistics, and grants.
- Test character set compatibility, line endings (for scripts), scheduler jobs, external tables, and directory objects.
- Rehearse cutover with pre‑validation scripts, log apply lag metrics, and rollback plan.
How do you assess database security risks in a merger or acquisition?
Answer:
- Discovery: Inventory all Oracle estates, versions, patches, TDE status, IAM/roles, network exposure, and data classifications (PII/PHI/export controls).
- Controls review: Evaluate Unified Auditing, least privilege, password policies, wallet management, and backup encryption.
- Data flows: Map cross‑border replication paths; identify shadow IT, 3rd‑party integrations, and on‑prem bridges.
- Gap analysis: Compare to corporate baseline (CIS, NIST, ISO, MAA); quantify risk with likelihood/impact; plan remediations (patches, config hardening, key rotation, segmentation).
How do you ensure compliance during cross‑border data replication?
Answer:
- Select compliant Regions and enable region-level controls (deny policies).
- Replicate only minimum necessary datasets; apply field-level masking/tokenization for PII/PCI.
- Enforce encryption in transit and at rest with customer‑managed KMS keys; maintain key residency per jurisdiction.
- Keep processing records, DPIAs, and lawful transfer mechanisms (SCCs/BCRs).
- Monitor access with CloudTrail, CloudWatch, and audit logs; retain immutable copies (Object Lock/WORM) where required.
How do you maintain audit trails and data lineage in Oracle?
Answer:
- Enable Unified Auditing with fine‑grained policies; ship to S3/CloudWatch with Object Lock for immutability.
- Track ETL lineage with Oracle Data Integrator/AWS Glue Data Catalog (annotate source‑to‑target mappings).
- Use DBMS_FGA for column‑level sensitive access; integrate with SIEM for correlation and alerts.
- Version DDL via migration tools (Liquibase/Flyway) and retain AWR/ASH baselines for forensic context.
What is your approach to GDPR‑compliant encryption practices?
Answer:
- At rest: TDE (tablespace or column-level) with KMS CMKs; enforce key rotation, split duties, and wallet hardening.
- In transit: Mandatory TLS for all client connections and replication channels.
- Data minimization: Mask non‑prod; limit replication scope; implement pseudonymization.
- Access governance: Role-based access, JIT approvals, rigorous auditing, and breach notification runbooks.
- Documentation: Maintain RoPA, DPIAs, and test data subject rights processes against backups and replicas.
How do you plan and execute a zero‑downtime major version upgrade?
Answer:
- Path: Prefer Oracle GoldenGate rolling upgrade (dual writes → cutover → decommission). Alternatives: Logical Standby rolling, Edition‑Based Redefinition (EBR) for app changes, or RDS Blue/Green for RDS.
- Steps: Build target (N+1) side, validate schema and compatibility, backfill data, run dual‑run window for reconciliation, switch traffic (connection strings/DNS), freeze old writers, and finalize.
- Validation: AWR diffs, consistency checks, and business KPIs; rollback is DNS revert + fall back to old primary.
Data Guard Focus
What is the role of primary and standby databases?
Answer:
The primary serves read/write production. Standby (physical or logical) continuously applies changes from redo. Standby provides HA/DR, read‑only offload (depending on type), and enables rolling maintenance via switchover.
How do you configure Data Guard Broker?
Answer:
- Ensure force logging, archivelog, flashback (recommended).
- Configure standby redo logs sized to match online redo.
- Create broker configuration with DGMGRL:
CREATE CONFIGURATION ...,ADD DATABASE ...,ENABLE CONFIGURATION. - Set properties:
RedouTransportSettings,ObserverConfigFile,ProtectionMode,FastStartFailoverTarget,ObserverConnectIdentifier. - Validate status:
SHOW CONFIGURATION,SHOW DATABASE. Automate observer with a separate host (or external VPC).
What are the differences between physical and logical standbys?
Answer:
- Physical Standby: Block‑for‑block redo apply (MRP), near‑perfect fidelity; supports Active Data Guard read‑only queries; simplest and most common; no data type translation issues.
- Logical Standby: SQL apply; allows rolling upgrades and selective replication; supports read/write for non‑replicated objects; may not support all data types/features; more maintenance overhead.
How does Fast‑Start Failover (FSFO) work?
Answer:
With Broker enabled, an observer monitors primary/standby. If conditions meet policy (connectivity loss, threshold timeouts, zero/acceptable data loss based on Protection Mode), the broker automatically promotes the standby. When the old primary returns, it is reinstated using Flashback Database if enabled; otherwise, manual re‑creation is needed. Configure FastStartFailoverThreshold, ObserverReconnect, and DataLoss limits to match RPO/RTO.
How do you test a switchover and failover?
Answer:
- Switchover (planned): Confirm
SYNCstate and zero apply lag;VALIDATE DATABASEin DGMGRL; runSWITCHOVER TO <standby>; verify roles, services, listener/SCAN endpoints, app connection strings, and redo routes; run smoke tests and AWR capture. - Failover (unplanned): Simulate primary outage (network cut or instance stop); confirm observer triggers FSFO (or run manual
FAILOVER TO <standby>); validate data consistency; reinstate former primary using flashback; document timings and any data loss relative to protection mode. Schedule quarterly DR tests.
No comments:
Post a Comment