Monday, 1 June 2026

Interview Question And Answer for autonomous database in 2026

 Question : what is troubleshoot issue in autonomous database





Database troubleshooting involves diagnosing and resolving performance bottlenecks, network/connectivity blocks, or configuration errors to restore normal operations. 
Since an Autonomous Database (such as Oracle Autonomous Database) is largely self-patching, self-tuning, and self-repairing, you don't typically manage routine infrastructure failures. Instead, troubleshooting focuses on the following primary areas: 
1. Common Issues & Solutions
  • Connection Failures: Applications may fail to connect due to misconfigured SSL certificates, incorrect Wallet files (credentials), or blocked network ports (e.g., Port 1522). Solution: Download the latest Client Credentials (Wallet) from your database console, ensure it is unzipped properly, and check your firewall rules. 
  • Performance Bottlenecks: Queries running slowly due to missing indexes, unoptimized logic, or resource contention. Solution: Use the Oracle Cloud Infrastructure (OCI) Performance Hub to identify wait events, monitor real-time SQL, and generate Automatic Workload Repository (AWR) reports to tune your queries. 
  • Resource Throttling: If you run heavy analytics when your database is scaled to lower limits, it may experience throttling. Solution: Scale up your ECPUs temporarily or configure Auto Scaling to automatically add up to 3x more CPU resources during peak loads. 
    • 2. Built-In Troubleshooting Tools
    Rather than digging through raw operating system logs, you can utilize the autonomous features built into the cloud console:
    • Service Metrics: Real-time quantitative data tracking CPU utilization, storage capacity, and active transactions. You can set threshold alarms (e.g., when CPU exceeds 90%). 
    • Database Actions (SQL Developer Web): Provides a built-in UI for monitoring active sessions, killing runaway queries, and running SQL tuning advisors. 
    • Oracle Autonomous Health Framework: Detects, identifies, and notifies you about non-responsive instances or configuration issues

    For More Details


Question : what is daily task in autonomous database


In an Autonomous Database, daily tasks are fully automated using machine learning and AI. Routine maintenance is handled without human intervention, which drastically changes the administrator's role. 
Instead of performing traditional DBA routines, daily operations focus on application-level management and oversight: 
1. What the Database Does Automatically (Self-Driving) 
  • Routine Backups: Automated daily backups with standard retention periods.
  • Patching & Upgrades: Security patches and version updates are applied online with zero downtime.
  • Performance Tuning: Continuous, real-time workload monitoring, automatic indexing, and SQL tuning.
  • Scaling: Instant, automatic adjustment of compute and storage resources based on traffic demands.
  • Self-Healing & Security: Automatic detection and resolution of hardware failures, alongside out-of-the-box data encryption. 
    • 2. What You Do Daily (Application & User Management) 
    • User & Access Management: Creating new database users, setting roles, and managing credentials.
    • Data Loading & Integration: Overseeing automated data loads, external tables, and linkages to cloud object storage.
    • Application Monitoring: Monitoring query performance at the application level to ensure business logic executes efficiently.
    • Security & Auditing: Managing network access controls and reviewing system audit logs. 
    To explore and manage these daily activities, you can utilize the Oracle Autonomous Database console or use the built-in Database Actions / SQL Developer Web interface


Question : How will you optimzed cost in autonomous database


Optimizing cost in an autonomous database (like Oracle Autonomous Database) requires adjusting compute and storage resources to match your actual workload. 
Implement these actionable strategies to minimize your monthly bill:
  • Enable Compute Auto-Scaling: Enable auto-scaling so your database automatically adds up to 3x more compute cores during peak loads, then scales down when demand drops. You only pay for the extra cores when they are actively used. 
  • Utilize Elastic Pools: Consolidate multiple distinct databases into an Elastic Pool. Instead of provisioning and paying for peak capacity on every single database, you share a pool of ECPUs (Elastic Compute Processing Units) across databases with staggered peak workloads. 
  • Schedule Automatic Shutdowns: Use OCI Events and Functions (or third-party schedulers) to automatically stop your development, testing, and UAT databases during off-hours and weekends. You only pay for storage when the database is stopped. 
  • Leverage Storage Auto-Scaling: Enable auto-scaling for storage. The database provisions minimal storage to start and scales up only when required, preventing you from over-provisioning and paying for unused disk space upfront. 
  • Deploy ADB App Store Analytics: Deploy the Cost & Usage Analytics application via the ADB App Store to track exactly which instances, queries, or workloads are consuming your budget. 
  • Choose the Right Workload Type: Ensure your database is correctly provisioned for its primary use case (e.g., Data Warehouse, Transaction Processing, JSON, or APEX) as pricing tiers and resource allocations can differ. 
    • For more specific recommendations based on your infrastructure, use the Oracle Cloud Cost Estimator to model your usage before your billing cycle



Question : How will you check Connection Failures in autonomous database


Checking for connection failures in an Oracle Autonomous Database involves looking at three key areas: database error logs, network/firewall settings, and client-side wallet configurations. 
1. Database-Side Diagnostics
Query the V$CLIENT_ERRORS view to see recent connection tracing and alert log errors (e.g., ORA-4021, ORA-12514):
sql
SELECT * FROM V$CLIENT_ERRORS ORDER BY timestamp DESC;
Use code with caution.
For historical performance and connection refusal trends, use the Performance Hub available in your Oracle Cloud Infrastructure (OCI) console under the Database Actions dashboard. 

. Network & Security Settings
Most connection failures stem from network blocks. Verify the following: 
  • Access Control Lists (ACL): If using Public Endpoints, ensure your client's IP address is explicitly whitelisted in the Autonomous Database's ACL settings. 
  • VCN/Subnet Rules: If using a Private Endpoint, verify your VCN security lists or Network Security Groups (NSGs) allow outbound traffic on port 1522 (the standard Oracle port) to the database's private IP. 
  • Database State: Check the OCI console to ensure the database lifecycle state is Available and not stopped or undergoing maintenance. 
3. Client & Connection String Issues
If you are seeing immediate network timeouts, verify your client configuration:
  • Connection Wallets: Autonomous databases typically require TLS authentication. Ensure you are using the correct wallet.zip credentials in your client (like SQL Developer, DBeaver, or Visual Studio Code)

  • tnsnames.ora: Double-check your connection string. Your service names determine your workload/performance tier (e.g., _high, _medium, or _low). 
  • Test the connection: Use the "Test Connection" feature in your driver or database tool to validate credentials and connectivity before troubleshooting further. 
For detailed official guidelines on auditing logs and resolving configuration conflicts, refer to the Oracle Cloud Documentation on Autonomous Database Metrics and Database Tools Connection Troubleshooting.

For more details



Question : How will you check Performance Bottlenecks in autonomous database


To identify performance bottlenecks in an Autonomous Database (such as Oracle Autonomous Database), you should use built-in, cloud-native observability tools rather than traditional OS-level scripts. 
The standard approach relies on evaluating wait events, analyzing heavy queries, and checking real-time metrics. 
1. Evaluate Average Active Sessions (AAS)
In an Autonomous Database, performance is typically measured using the AAS metric compared to the number of allocated CPU cores. 
  • CPU Bound: If AAS consistently exceeds your allocated CPU count, the database is waiting for processing power. Consider scaling up your CPU limits. 
  • Wait Based: If AAS spikes are tied to specific wait classes (e.g., User I/O, Concurrency), you need to investigate those specific operations.
2. Use Performance Hub
The Performance Hub is the primary visual diagnostic tool: 
  • Time Period: Select a 60-minute interval when the bottleneck was most severe.
  • Activity Chart: Use this view to see Average Active Sessions grouped by Wait Class.
  • Top SQL: Identify which specific SQL_ID is consuming the most database time. 
    3. Review ADDM and ASH Reports
The database's internal diagnostic engines process workload histories to pinpoint bottlenecks for you: 
  • ADDM (Automatic Database Diagnostic Monitor): Analyzes workload snapshots and provides actionable recommendations to fix identified problems along with the estimated performance benefit. 
  • ASH (Active Session History): Samples active session data every second to give you a granular, real-time look at what the database is struggling with at the exact moment of the bottleneck. 
4. Check Cloud Infrastructure Metrics
Autonomous databases operate within cloud ecosystems (like Oracle Cloud Infrastructure - OCI): 
  • View Service Metrics like CPU Utilization, Storage Capacity, and Network Bandwidth in your Cloud Console.
     
  • Set up custom alarms and logs that will alert you the moment threshold limits are breached. 
5. Run SQL Tracing
If a specific application flow is slow, use SQL Tracing in the Database Actions (SQL Worksheet). Tracing breaks down execution time into specific parse, execution, and fetch phases to help isolate bad query plans.
For Details 



Question : How will you check Resource Throttling in autonomous database



To check for resource throttling in an Oracle Autonomous Database, monitor the database's CPU utilization, concurrency limits, and Automatic Workload Repository (AWR) reports. Throttling occurs when resource demand exceeds the allocated limits, causing queries to queue or execution to slow down. 
Check for throttling by using the following methods:
1. Cloud Console Metrics (OCI)
Monitor real-time and historical resource usage through the Oracle Cloud Infrastructure (OCI) console. 
  • CPU Utilization: Check if the CPU usage consistently hits 100%.
  • CPU Throttling Threshold: Review if the database is actively throttling threads due to compute limits.
  • Queued Statements: Look for spikes in the Running and Queued Statements metric to identify when user requests are delayed because service concurrency limits are reached. 

2. Performance Hub
Access the Performance Hub in the Oracle Cloud Console or via the Database Actions menu to check database workload. 
  • Active Session History (ASH): View wait events to identify time lost on CPU queues or I/O resource limits.
  • SQL Monitoring: Review individual running SQL statements to identify those waiting for resources or experiencing long execution times. 
3. Service Concurrency and Runaway Queries
Examine the predefined Database Resource Manager (DBRM) rules that govern your instance. 
  • Concurrency Limits: Note that different services (e.g., HIGH, MEDIUM, LOW) have different concurrency limits set by default.
  • Runaway Rules: If queries are getting unexpectedly terminated, check the Service Console under Administration > Set Resource Management Rules to see if specific Runaway SQL criteria (e.g., query run time or I/O limits) are throttling the
  • queries. 
    • 4. AWR Reports (Automatic Workload Repository)
    Generate and analyze AWR reports to drill down into historical performance metrics: 
    • Look at the Average Active Sessions (AAS) and DB CPU metrics.
    • Check for high wait classes, specifically related to CPU (e.g., resmgr: cpu quantum) or I/O, which indicate bottlenecking or resource limits. 
    5. Dynamic Performance Views
    For database-level diagnostics, query internal dynamic views in your SQL worksheet:
    • Run queries against v$con_sysmetric_history to evaluate historical CPU usage metrics per second and identify periods of heavy throttling. 
    To resolve frequent throttling, consider scaling up your OCPU allocation or enabling Auto Scaling, which allows the database to temporarily use up to 3x more CPU
    resources during peak workloads

For more Details please visit


Question : How will you check User & Access Management in autonomous database


To check and manage User & Access Management in Oracle Autonomous Database (ADB), you can evaluate database privileges, external identity providers, and localized user properties through built-in database views, graphical actions, or cloud identity mapping. 
1. Check Local Database Users and Privileges 
You can query standard Oracle data dictionary views using any SQL client to see local accounts and their access levels. 
  • Review all database users: Query DBA_USERS to check account status (locked/unlocked) and expiration dates.
  • Check system privileges: Run SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'USERNAME'; to inspect administrative capabilities.
  • Check object privileges: Run SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'USERNAME'; to verify explicit table or view access.
  • Check assigned roles: Run SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USERNAME'; to review bundled permission sets. 
2. Verify External Identity Integration
Autonomous Database supports centralized authentication. You can check which identity provider is active by querying the system parameters: 
sql
SELECaccepts traffic from permitted endpoints. 
  • Check the OCI Console under the database details page to see if network ACLs are configured.
  • Ensure specific IP addresses or CIDR blocks are explicitly defined so that unauthorized networks are blocked completely. [1, 2, 3, 4]
4. Use Oracle Database Actions (GUI) 
If you prefer a visual interface over SQL commands, you can use the built-in browser tools. 
  1. Log in to Oracle Database Actions as the ADMIN user.
  2. Open the main navigation menu and select Database Users under the Administration section.
  3. View the generated cards for an overview of REST enablement, password validity, and active accounts.
  4. Click Edit on a user card to visually modify or check Granted Roles
T value FROM v$parameter WHERE name = 'identity_provider_type';
  • OCI IAM: If active, check mappings between database global users and cloud groups using DBA_GLOBAL_USERS.
  • Microsoft Entra ID / Active Directory: Verify if enterprise directory links are established for centralized token-based logins. 
3. Inspect Network Access Control Lists (ACLs)
Access management in ADB extends to the network layer to ensure the database only


For more details



Question : How will you check Data Loading & Integration in autonomous database


Checking data loading and integration in an autonomous database typically involves monitoring pipeline metrics, verifying materialized views, and reviewing system execution logs.
To ensure your integrations and data loads are healthy and functioning properly, follow these steps:
1. Monitor Pipeline Jobs
Use the autonomous database’s built-in tools (such as Oracle Autonomous Data Warehouse's Data Studio or Cloud Database tools) to check the status of your data load jobs.
  • Metrics: Review dashboards for successful, running, and failed jobs.
  • Autonomous Data Pump: Query the USER_DATAPUMP_JOBS and DBA_DATAPUMP_JOBS views in SQL to track export and import progress.
  • External Tables: If you are querying data directly from cloud storage, inspect the ALL_EXTERNAL_TABLES and ALL_EXTERNAL_LOCATIONS views to ensure the metadata mapping and source files remain correctly linked.

2. Leverage Diagnostic & Alert Logs
Investigate database logs for specific errors during the extract, transform, and load (ETL) phases.
  • Alert Logs: Check the alert logs for asynchronous errors, database events, or resource bottlenecks.
  • DBMS_CLOUD Package: When loading data via DBMS_CLOUD (common in Oracle Autonomous Databases), check the DBMS_CLOUD.GET_OPERATION_STATUS procedure. This allows you to track the exact status and any parsing errors of bulk loads from Object Storage.
  • Error Tables: Set up and query DBMS_CLOUD error tables (e.g., using the rejectlimit parameter) to see exactly which rows failed validation and why
3. Verify Materialized Views and Transformations
Ensure that aggregated or integrated data tables are refreshing correctly after raw data is loaded.
  • Refresh States: Run a query on USER_MVIEWS to check the LAST_REFRESH_DATE and COMPILE_STATE of your materialized views.
  • Pipeline Status: If you use cloud-native integration tools or Autonomous Data Studio, check the active status and run history of scheduled data transformations and pipeline flows. 
4. Test Query Execution
The ultimate test of data integration is verifying that users and applications can successfully query the newly loaded data.
  • Row Counts & Data Profiling: Run baseline queries and row counts against target tables and compare them to your source systems to verify data completeness. 
  • Automated Auditing: Set up scheduled SQL queries to track load frequency and performance, sending automated alerts (e.g., via OCI Notifications) if integration processes exceed your expected timeframes. 

For specific platform documentation, you can refer to the Oracle Autonomous Database Documentation or the equivalent monitoring guides for your specific cloud provider's autonomous service.

For Details

on

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)