EasyReliableDBA: Oracle DBA interview question and answer 2026

What is the primary problem Oracle Database Vault solves?

Answer: It stops privileged account abuse and insider threats. In standard Oracle databases, a SYSDBA or DBA user can view all application tables. Database Vault blocks these powerful administrative accounts from viewing application data while still allowing them to perform operational tasks like backups, patching, and tuning

Q1: What is Oracle Database Vault, and how does it change traditional DBA privileges in Oracle 19c?

Answer: Oracle Database Vault is an embedded security feature that restricts highly privileged database users from accessing sensitive application data.
Key Mechanism: Traditionally, a user with the DBA role or SYSDBA system privilege can see all data in all tables. With Database Vault enabled, it strips the implicit data-viewing power from administrators. The DBA can still maintain the infrastructure (backups, tuning, patching), but they cannot query restricted application data tables.

Q2: What are the primary components used to implement Database Vault restrictions?

Realms: Logical zones created around specific database schemas, tables, or roles. Only explicitly authorized users or application contexts can access objects inside a realm.
Command Rules: Controls placed over SQL commands (e.g., DROP TABLE, ALTER SYSTEM, CREATE USER). They determine under what exact conditions a command can run.
Factors: System contexts or attributes used to establish a trusted path (e.g., Client IP address, Database User, Time of Day, or Client Program).

Q3: Explain "Separation of Duties" under Oracle Database Vault. Which specific accounts are created?

Database Vault removes complete control from a single monolithic DBA account by introducing specialized administrative roles:

Database Vault Owner (DV_OWNER): Manages the Database Vault configurations, defines realms, and grants access to those realms.
Database Vault Account Manager (DV_ACCT_MGR): Manages database user accounts and profiles. Crucially, a standard DBA can no longer create or drop database users once Database Vault is active. [
Database Administrator (DBA): Retains purely operational duties like performance management, storage management, and database upgrades without access to the business data.

Scalability Strategy: Managing 100+ Databases

Q4: How do you implement and maintain Database Vault efficiently across an estate of 100 databases?

Managing 100 separate databases requires centralization, automation, and standardization. The enterprise strategy involves:

Centralized Policy Management: Use Oracle Enterprise Manager (OEM) Cloud Control to centrally design, package, and monitor policies. Policies can be defined as a master blueprint and pushed across the database fleet.
Infrastructure as Code (IaC) & Automation: Use the provided Oracle PL/SQL APIs (such as DBMS_MACADM) to wrap realm configurations, command rules, and factors into standardized scripts. These scripts are deployed through automation pipelines (like Ansible, Terraform, or Oracle Fleet Patching and Provisioning).
Multitenant Architecture Integration: In an Oracle 19c Container Database (CDB) environment housing multiple Pluggable Databases (PDBs), Database Vault can be configured at the PDB level. This stops a central cloud/CDB administrator from spying on data held within isolated tenant PDBs.
Central Auditing via AVDF: Couple the fleet deployment with Oracle Audit Vault and Database Firewall (AVDF). This aggregates security violations and Database Vault audit trails from all 100 databases into a single dashboards for real-time monitoring.

Scenario-Based & Technical Questions

Q5: A DBA needs to perform an urgent application schema change inside a protected Realm. How is this allowed under Database Vault?

Answer: The Database Vault Owner (DV_OWNER) must grant temporary authorization to the DBA or deployment account using the DBMS_MACADM.AUTHORIZE_PDB_DDL or DBMS_MACADM.AUTHORIZE_REALM APIs.
Best Practice: Tie this authorization to a rigid Command Rule that mandates a "trusted path". For example, the modification is only permitted if it originates from a specific deployment server IP address and occurs during an approved maintenance window.

Q6: How do you verify if Database Vault is properly running on a specific database instance?

Answer: You can check the current system registration by querying the database options view. Run the following SQL query:

sql

SELECT PARAMETER, VALUE FROM V$OPTIONS WHERE PARAMETER = 'Oracle Database Vault';

Result: If it returns TRUE, the option is enabled. You should also check DBA_DV_STATUS to verify if the security enforcement is actively working for the runtime environment.

Q7: Does Oracle Database Vault encrypt data stored on disk?

Answer: No, Oracle Database Vault does not encrypt data. It provides access and operational controls.
Complementary Tech: To protect data on physical storage or backups from being compromised outside the database engine, you must use Transparent Data Encryption (TDE). Database Vault and TDE are complementary features that represent the foundation of Oracle's Defense-in-Depth security framework.

Q3: What is a Realm and what are its components?

Answer: A Realm is a logical security zone or "firewall" wrapped around database schemas, specific tables, or roles. It consists of:
- Secured Objects: The schemas or tables you want to protect (e.g., HR.EMPLOYEES).
- Authorized Users: The specific application accounts explicitly allowed to access those secured objects. Unauthorised users (including SYS or DBA) are instantly blocked.

Q4: What are Factors and Command Rules?

Answer:
- Factors: System attributes that establish a user's context, such as Client IP Address, Session User, Program Name, or Time of Day.
- Command Rules: Context-aware security policies attached to SQL commands (e.g., DROP TABLE, ALTER SYSTEM). They validate defined Factors before allowing the command to execute. [

Q5: How do you verify if Oracle Database Vault is enabled in 19c?

Answer: Query the V$OPTIONS view. If enabled, the parameter returns TRUE:

sql

SELECT parameter, value FROM v$options WHERE parameter = 'Oracle Database Vault';

Part 2: Protecting Data Across 100 Databases

To secure 100 databases efficiently without managing 100 individual security strategies, you must leverage Oracle 19c Multitenant Architecture alongside centralized automation.

Architectural Blueprint

Consolidation Strategy: Group your databases into Multitenant Container Databases (CDBs) hosting multiple Pluggable Databases (PDBs). For instance, 4 production CDBs holding 25 PDBs each.
Isolation Control: Enable Database Vault locally at the PDB level. This prevents a Common CDB DBA from peeking into individual PDB customer application data.
Automation Workflow:
1. Write your Database Vault configuration policies as unified PL/SQL scripts.
2. Use Oracle Enterprise Manager (OEM) Cloud Control or Ansible playbooks to deploy the scripts across all 100 PDB endpoints simultaneously.
3. Route all security logs to a centralized target via Oracle Audit Vault and Database Firewall (AVDF) for centralized auditing and compliance.

Part 3: Implementation Example (Protecting HR Data)

This script creates a custom security realm to isolate human resources data from database administrators.

Execute this configuration as the DV_OWNER account:

sql

-- Step 1: Create a secure protection zone (Realm)
BEGIN
  DBMS_MACADM.CREATE_REALM(
    realm_name    => 'HR_Data_Protection_Realm',
    description   => 'Shields sensitive HR records from administrative access',
    enabled       => DBMS_MACUTL.G_YES,
    audit_options => DBMS_MACUTL.G_AUDIT_ON_FAILURE
  );
END;
/

-- Step 2: Bind the sensitive HR schema to the Realm
BEGIN
  DBMS_MACADM.ADD_OBJECT_TO_REALM(
    realm_name  => 'HR_Data_Protection_Realm',
    object_owner=> 'HR',
    object_name => '%', -- Protects all tables inside the HR schema
    object_type => 'TABLE'
  );
END;
/

-- Step 3: Explicitly authorize only the trusted Application Server account
BEGIN
  DBMS_MACADM.ADD_AUTH_TO_REALM(
    realm_name  => 'HR_Data_Protection_Realm',
    grantee     => 'HR_APP_USER', -- The functional runtime software user
    rule_set_name=> NULL
  );
END;
/

Part 4: Testing & Verification Case

This validation test proves that standard administrative users cannot bypass Database Vault restrictions.

Test Setup

Ensure you have three distinct runtime sessions ready:

HR_APP_USER: The authorized software account.
SYSTEM or SYS: The powerful infrastructure DBA account.

Execution Steps & Expected Results

Scenario A: The App Server accesses data
sql
-- Connect as the authorized Application Server Account CONNECT hr_app_user/password@pdb_name; SELECT COUNT(*) FROM hr.employees;
- Expected Result: Success. The query executes normally and returns a count because this specific account is explicitly permitted inside the realm.
Scenario B: The DBA attempts to access data
sql
-- Connect as the powerful Infrastructure Database Administrator CONNECT system/password@pdb_name; SELECT COUNT(*) FROM hr.employees;
- Expected Result: ORA-47400: Command Blackout / Realm Violation. Database Vault intercepts the transaction and completely denies the SYSTEM admin access, even though they hold global DBA privileges.
Scenario C: Checking the Audit Trails
sql
-- Connect as the Security Auditor or DV_OWNER to check breaches SELECT username, action_name, return_code FROM unified_audit_trail WHERE return_code = 47400;
- Expected Result: A system record identifying that SYSTEM attempted an unauthorized read on HR.EMPLOYEES, logging the exact time, terminal, and database command used

Question : How to app patch OMS/OEM

To patch an OMS (Oracle Management Service) or OEM (Oracle Enterprise Manager) Agent, you must verify your utility versions, run prerequisite analyses to check for conflicts, perform rolling or downtime patching using omspatcher or agentPatcher, and validate the inventory.

1. Interview Questions, Answers & Examples

Q: What is your approach to applying a Release Update (RU) or patch in the OMS environment?

Answer: I follow a structured, phased approach recommended by Oracle. I first take full backups of the OMS and Repository, download the latest OMSPatcher and OPatch utilities, and review the patch README for specific prerequisites. Next, I run the analyze command, shut down the OMS processes, apply the patch, and run post-patch verification.

Example: "In our last patching cycle for OEM 13.5, I downloaded the latest Release Update and one-off patches. Before applying anything to production, I simulated the installation using the analyze command to ensure there were zero conflicts. After a successful simulation, I stopped the OMS services with ./omstop and applied the patch using omspatcher apply."

Q: How do you handle patch conflicts between OMS and Plugins?

Answer: Patch conflicts occur when a one-off patch alters a file that has already been modified or patched by a prior Release Update. I check the OMSPatcher analyze log to identify the conflicting sub-patches. Then, I check My Oracle Support (MOS) for merge patches, or contact Oracle Support for a holistic patch that resolves the conflict.

Example: "During an RU upgrade last quarter, omspatcher apply -analyze threw a prerequisite error due to a pre-existing plugin patch. I viewed the conflict details, reviewed the error in the omspatcher log, and searched MOS for a merged patch to resolve the overlap before proceeding."

Q: What are the primary commands you use for OMS and Agent patching?

Answer:

omspatcher apply -analyze (Validates conflicts and system requirements without altering the system)
omspatcher apply (Applies the system patch to the OMS platform home)
agentPatcher apply (Applies the required fixes to the Management Agent home)
omspatcher lspatches / agentPatcher lspatches (Verifies the patches were successfully registered)

2. Example Test Cases (Pre & Post-Patch Verification)

These test cases demonstrate how you validate the integrity of your OEM environment before and after patching.

Pre-Patching Test Case: Analyze and Environment Check

Objective: Ensure the environment is prepared and conflict-free before applying patches.
Steps:

Export the correct ORACLE_HOME path for the OMS.
Ensure OPatch and OMSPatcher are upgraded to the minimum version specified in the patch README.
Run the command: omspatcher apply -analyze <PATCH_LOCATION>
Review the generated logs in $ORACLE_HOME/cfgtoollogs/omspatcher/ to confirm there are no prerequisite failures or conflict alerts.

Post-Patching Test Case: OMS Verification

Objective: Verify that the OMS software has been successfully updated and services are healthy.
Steps:

Execute the verification command: omspatcher lspatches
Verify the output displays the correct patch ID (e.g., Release Update number) and that the status reads Success.
Restart OMS services using emctl start oms.
Validate the OMS status using emctl status oms -details.
Log in to the OEM Console and verify that the core components show as Up in the Target Homepage.

Post-Patching Test Case: Agent Synchronization

Objective: Ensure the patched Management Agent successfully uploads its payload to the OMS.
Steps:

Navigate to the agent home directory and execute: agentPatcher lspatches
Ensure the patch version corresponds with the OMS patch.
Start the agent: emctl start agent
Run a secure upload to the OMS: emctl upload agent
Check upload status: emctl status agent and ensure there are no queued payloads.

Question : How to configure resource manger in 19c Database.

Managing resources via the Oracle Database Resource Manager (DBRM) in an Oracle 19c database involves grouping database sessions into Consumer Groups, creating a Resource Plan, and establishing Plan Directives to enforce specific resource allocations.

To manage Input/Output (I/O), Oracle 19c offers parameter-based limits (MAX_IOPS and MAX_MBPS) at the Pluggable Database (PDB) level, as well as threshold-driven actions (SWITCH_IO_REQS and SWITCH_IO_MEGABYTES) at the session/consumer group level.

1. Conceptual Framework & Core Q&A

Q1: What are the main components required to set up DBRM?

Pending Area: A scratchpad workspace used to create, modify, and validate resource manager objects before committing them to the data dictionary.
Consumer Groups: Logical buckets that group user sessions based on their resource demands (e.g., OLTP_GROUP, REPORT_GROUP).
Resource Plan: The overarching container configuration that defines how CPU, I/O, and parallel execution limits are distributed.
Plan Directives: The specific rules mapping a Consumer Group to a Resource Plan, determining exact resource throttles or switch behaviors.

Q2: How does Oracle 19c manage database I/O using Resource Manager?

PDB Level (Hard Limits): Using the initialization parameters MAX_IOPS (Input/Output Operations Per Second) and MAX_MBPS (Megabytes Per Second throughput) inside a Pluggable Database. These act as physical limits that throttle the entire PDB regardless of concurrent demand.
Session/Consumer Group Level (Runaway Control): Using parameters such as SWITCH_IO_REQS and SWITCH_IO_MEGABYTES. When a specific database session crosses these I/O thresholds, DBRM automatically downgrades it to a low-priority group or cancels the statement entirely.

2. Practical Configuration: End-to-End Example

The following practical exercise sets up a resource plan named IO_MANAGEMENT_PLAN to isolate online transaction processing sessions (OLTP_CG) from heavy reporting sessions (REPORT_CG). It implements an automatic downgrade rule for reporting sessions that exceed 1000 I/O requests.

Step 1: Create the Pending Area

All configurations must start by initializing the workspace.

sql

EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

Step 2: Define Consumer Groups

Create distinct categories for different transactional workloads.

sql

EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(consumer_group => 'OLTP_CG', comment => 'OLTP Transactions');
EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(consumer_group => 'REPORT_CG', comment => 'Reporting Workloads');

Step 3: Define the Resource Plan

Establish the root container plan.

sql

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN(plan => 'IO_MANAGEMENT_PLAN', comment => 'Plan to limit runaway I/O workloads');

Step 4: Map Directives with I/O Management Controls

Create rules allocating CPU shares and setting explicit session-level I/O switch limits.

OLTP Group: Assigned high CPU priority (80%).
Reporting Group: Assigned lower CPU priority (20%). If any single query exceeds 1,000 I/O requests, it automatically switches to OTHER_GROUPS (low priority) for the remainder of that call.

sql

-- Directive for OLTP_CG
EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( -
    plan => 'IO_MANAGEMENT_PLAN', -
    group_or_subplan => 'OLTP_CG', -
    comment => 'OLTP Priority Directives', -
    mgmt_p1 => 80);

-- Directive for REPORT_CG with runaway I/O management
EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( -
    plan => 'IO_MANAGEMENT_PLAN', -
    group_or_subplan => 'REPORT_CG', -
    comment => 'Report Directives with I/O limits', -
    mgmt_p1 => 20, -
    switch_group => 'OTHER_GROUPS', -
    switch_io_reqs => 1000, -
    switch_for_call => TRUE);

-- Directive for default system tracking (Mandatory)
EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( -
    plan => 'IO_MANAGEMENT_PLAN', -
    group_or_subplan => 'OTHER_GROUPS', -
    comment => 'Catch-all group', -
    mgmt_p2 => 100);

Step 5: Validate and Submit Configuration

Submit the structural definitions to activate them permanently in the database.

sql

EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

Step 6: Map Users to Groups and Enable Plan

Assign target schemas to their respective workload buckets and activate the system plan.

sql

-- Map schema 'BI_USER' to the Reporting Consumer Group
EXEC DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( -
    attribute => 'ORACLE_USER', -
    value => 'BI_USER', -
    consumer_group => 'REPORT_CG');

-- Enable the plan instance-wide
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'IO_MANAGEMENT_PLAN' SCOPE=MEMORY;

3. Alternative: Multi-Tenant PDB-Level I/O Throttling

If operating inside an Oracle 19c Multitenant architecture (CDB/PDB), you do not need a complex PL/SQL pending area to limit absolute infrastructure consumption. You can throttle maximum throughput by running an ALTER SYSTEM command directly inside the specific PDB:

sql

-- Connect to the target PDB (e.g., HRPDB)
ALTER SESSION SET CONTAINER = hrpdb;

-- Enforce hard cap limits on physical disk I/O metrics
ALTER SYSTEM SET MAX_IOPS = 500 SCOPE=BOTH;
ALTER SYSTEM SET MAX_MBPS = 50 SCOPE=BOTH;

4. Test Cases and Verification

Test Case 1: Verifying active plan status

Action: Check if the plan is loaded and running in the memory structure.
SQL Query:
sql
SELECT name, is_top_plan FROM v$rsrc_plan;
Expected Result: IO_MANAGEMENT_PLAN should appear with IS_TOP_PLAN marked as TRUE.

Test Case 2: Mapping Validation

Action: Connect to the database as BI_USER and view active session routing.

SQL Query:

sql

SELECT username, resource_consumer_group FROM v$session WHERE username = 'BI_USER';

Expected Result: The session's RESOURCE_CONSUMER_GROUP must show REPORT_CG.

Test Case 3: Simulating and Verifying an I/O Runaway Switch

Action: Execute an unindexed, heavy Full Table Scan query as BI_USER that requires more than 1,000 disk blocks.
SQL Query:
sql
SELECT COUNT(*) FROM bi_user.massive_historical_data;
Validation Query (Run from a DBA session while the long query runs):
sql
SELECT username, resource_consumer_group, current_consumer_group FROM v$session WHERE username = 'BI_USER';
Expected Result: RESOURCE_CONSUMER_GROUP (initial group) will remain REPORT_CG, but CURRENT_CONSUMER_GROUP will dynamically switch to OTHER_GROUPS once the 1,000 I/O request limit is exceeded.

5. Summary Check

Resource Manager Enabled & I/O Managed

text

The Oracle 19c database will now prioritize low-overhead transactions, isolate reporting pools, and automatically throttle runaway sessions or PDB volumes based on predefined resource metrics.

Question : How to manage and configure observer in data guard in 19c database question and answer with example and test cases

Managing and configuring the Data Guard Observer in Oracle 19c is essential for enabling Fast-Start Failover (FSFO), which automates database failover when the primary site goes down. The Observer acts as a continuous tracking client process that runs outside the primary and standby database nodes.

Part 1: Conceptual Questions & Answers

Q1: What is the primary purpose of the Oracle Data Guard Observer?

A: The Observer continuously monitors the availability and health of a Data Guard configuration. It works alongside the Data Guard Broker to safely initiate an automatic, zero-data-loss failover to a pre-designated standby target when contact with the primary database is unexpectedly lost.

Q2: Where should the Observer process be physically installed and hosted?

A: It must be deployed on a third separate machine (Host 3). It should never share hardware infrastructure with the primary or standby databases. This prevents a single site or network crash from taking down both a database node and the observer simultaneously.

Q3: What new Oracle 19c features enhance Observer reliability?

A: Oracle 19c allows you to deploy multiple observers per configuration (one designated as Master and up to two others as Backups). It also introduces Observe-Only Mode, letting you evaluate if a failover would have occurred without actually changing database roles.

Part 2: Step-by-Step Configuration Example

This setup assumes an existing Oracle 19c Data Guard Broker configuration consisting of primary database PROD and standby database STBY.

Primary Database Node  (Host 1): PROD
Standby Database Node  (Host 2): STBY
Observer Machine Node  (Host 3): OBS_NODE

Step 1: Install and Configure Network Connectivity on Host 3

You only need a lightweight Oracle Database Client Administrator installation or an Instant Client on OBS_NODE to access the DGMGRL command-line utility.

Configure your local $ORACLE_HOME/network/admin/tnsnames.ora on OBS_NODE to point to both systems:

text

PROD =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=host1-ip)(PORT=1521))
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PROD)))

STBY =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=host2-ip)(PORT=1521))
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STBY)))

Step 2: Configure Fast-Start Failover (FSFO) Settings

Log into the broker from the primary site or the observer node using DGMGRL and adjust timeout metrics:

sql

dgmgrl sys/your_password@PROD

-- 1. Set the target standby for automatic failover
DGMGRL> EDIT DATABASE PROD SET PROPERTY FastStartFailoverTarget = 'STBY';
DGMGRL> EDIT DATABASE STBY SET PROPERTY FastStartFailoverTarget = 'PROD';

-- 2. Define the ping failure threshold (in seconds) before failover begins
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 30;

-- 3. Enable Flashback on both databases (Mandatory requirement for automated reinstatement)
-- Ensure 'ALTER DATABASE FLASHBACK ON;' has been run at SQL level on both nodes.

Step 3: Launch the Observer on Host 3

Run the observer process in the background using Linux nohup or native DGMGRL background capabilities to keep it running after your shell session disconnects:

bash

# Executing on OBS_NODE (Host 3)
nohup dgmgrl sys/your_password@PROD "START OBSERVER obs_master 
FILE='/u01/app/oracle/fsfo_obs.dat'" -logfile /u01/app/oracle/observer.log &

Note: The FILE parameter tracks the synchronization configuration state on disk. []

Step 4: Turn on Automated Failover Protection

Go back into your broker shell window and toggle active protection status:

sql

DGMGRL> ENABLE FAST_START FAILOVER;
DGMGRL> SHOW CONFIGURATION;

Part 3: Live Verification & Testing Scenarios

Test Case 1: Routine Verification Checks

Objective: Ensure the observer is tracking and reporting live heartbeats.
Execution Command:
sql
DGMGRL> SHOW OBSERVER;
Expected Output:

text

Configuration - dg_config
  Fast-Start Failover: ENABLED
  Primary            : PROD
  Active Target      : STBY

  Observer "obs_master" - Master
    Host Name        : OBS_NODE
    Last Ping to Primary : 0 seconds ago
    Last Ping to Target  : 1 second ago

Test Case 2: Simulating Unplanned Primary Node Outage

Objective: Verify that the standalone observer performs an unassisted failover to the standby node when the primary instance crashes completely.
Execution Step: Abruptly abort the primary database engine instance from Host 1.
bash
# Execute on Host 1 (Primary Node) sqlplus / as sysdba SQL> SHUTDOWN ABORT;
Observer Log Reaction (/u01/app/oracle/observer.log):

text

[W000 2026-06-22] Primary database cannot be reached.
[W000 2026-06-22] Fast-Start Failover threshold has expired.
[W000 2026-06-22] Making a last connection attempt to primary...
[S002 2026-06-22] Initiating Fast-start Failover to database STBY...
[S002 2026-06-22] Failover to database "STBY" succeeded.

Expected Result: Checking SHOW CONFIGURATION; via DGMGRL will now demonstrate that STBY automatically transitioned to the Primary processing role.

Test Case 3: Automatic Reinstatement of Failed Nodes

Objective: Validate that the old crashed primary server is automatically recovered as a fresh synchronized standby database without manual restoration steps.

Execution Step: Start the database back up on Host 1.

bash

# Execute on Host 1 (Old Primary Node)
sqlplus / as sysdba
SQL> STARTUP MOUNT;

Expected Result: The running Observer detects that the node is online. It leverages historical Flashback Database tracking parameters to automatically stitch it back into the architecture. Within minutes, SHOW CONFIGURATION; outputs:
text
Members: STBY - Primary database PROD - Physical standby database

For more Details

Guide to Oracle Data Guard Fast-Start Failover

Question : what is key is managed in oracle and OCI

In enterprise architecture, key vaults securely store and manage cryptographic keys, secrets, and credentials. On-premises systems primarily rely on Oracle Key Vault (OKV), while OCI Vault is used in Oracle Cloud. Both integrate seamlessly with Transparent Data Encryption (TDE) to protect sensitive data at rest.

Part 1: Key Management (On-Premises vs. OCI)

Q: What is the difference between Oracle Key Vault (OKV) and OCI Vault?

Oracle Key Vault (OKV): A software appliance designed for on-premises deployments. It centralizes the management of Oracle Wallets, TDE master encryption keys, and credentials, providing enterprise-wide visibility and compliance reporting on internal hardware.
OCI Vault: A fully managed, multi-tenant or dedicated cloud service on Oracle Cloud Infrastructure (OCI). It replaces the need to deploy software appliances yourself, handling the provisioning of FIPS-validated Hardware Security Modules (HSMs) to manage master keys, encryption keys, and secrets in the cloud.

Q: How does OCI Vault integrate with on-premises data centers?

Answer: Hybrid setups often use OCI FastConnect or a VPN for secure networking. By utilizing a Virtual Cloud Network (VCN) with a Service Gateway, on-premises applications and databases can securely access secrets and keys in OCI Vault over private networks without traversing the public internet.

Part 2: Transparent Data Encryption (TDE)

Q: What is TDE and what is its primary function?

Answer: Transparent Data Encryption (TDE) allows you to encrypt sensitive data (such as credit card numbers or PII) at the column or tablespace level. The process is "transparent" because authorized users and applications can seamlessly read and write data without needing to rewrite existing SQL queries.

Q: How do you store the TDE Master Encryption Keys?

Answer: TDE master keys can be stored outside the database in an external security module. Depending on your architecture, this can be an Oracle Wallet (local file-based), Oracle Key Vault (OKV) (on-premises), or the OCI Key Management Service (KMS).

Q: What is the difference between TDE Column Encryption and Tablespace Encryption?

Answer:
- Column Encryption: Encrypts specific, targeted columns. It requires application modifications and has certain limitations (e.g., cannot index encrypted columns easily).
- Tablespace Encryption: Encrypts an entire tablespace at the block level. All data created within this tablespace is encrypted automatically, requiring no application changes. It is the recommended best practice for modern Oracle databases.

Q: How does TDE handle key management during an Oracle Data Guard (Disaster Recovery) switchover?

Answer: Because the standby database needs to decrypt the incoming redo logs to apply changes, both the primary and standby databases must share the exact same master encryption keys. In an OKV or OCI setup, both endpoints query the same centralized vault/OKV to retrieve the master key, preventing decryption failures during role transitions.

Managing 100 TDE-enabled databases centrally requires separating database operations from key management. Deploy Oracle Key Vault (OKV) on-premises for local databases and utilize OCI Vault (KMS) or OCI External KMS for databases in OCI. This standardizes key rotation, compliance, and backups across your environment.

Key Interview Questions & Answers

Q1: What is the main difference between Oracle Key Vault (OKV) and OCI Vault?

Answer: OCI Vault is a cloud-native, multi-tenant or virtual private HSM service designed specifically for OCI workloads. Oracle Key Vault (OKV) is a full-stack, software-based KMIP (Key Management Interoperability Protocol) appliance that you deploy on-premises or on compute instances. OKV is typically used when you need centralized, unified key storage across a hybrid fleet (both on-premises and cloud).

Q2: How does Transparent Data Encryption (TDE) work with centralized key management?

Answer: TDE uses a two-tier architecture: the Data Encryption Key (DEK) encrypts the actual data, while the Master Encryption Key (MEK) encrypts the DEK. In a centralized architecture, the MEK is generated and securely stored outside the database on OKV or OCI Vault. When a database starts or accesses encrypted data, it connects to the key vault via a secure wallet/certificate to retrieve the MEK.

Q3: How do you handle TDE key management for a large fleet of 100 databases?

Answer:
1. Automation: Deploy OKV endpoints via automation (like Ansible or Chef) to register 100 databases automatically.
2. Centralized Policies: Utilize OKV’s "Virtual Wallets" and grouping features to segment keys by environment (e.g., Prod, Dev).
3. Rotation: Set automatic key rotation schedules in OCI Vault or OKV, which will automatically update the MEK without causing database downtime.

Example: Migrating from Local Wallets to Centralized Key Management

To migrate an on-premises database using a local wallet to an OKV centralized server, use the ADMINISTER KEY MANAGEMENT command:

sql

-- Step 1: Set the keystore configuration on the database server to point to OKV
ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=OKV" SCOPE=BOTH;

-- Step 2: Open the connection to the external key vault using admin credentials
ADMINISTER KEY MANAGEMENT SET KEYSTORE IDENTIFIED BY "local_wallet_password" 
WITH BACKUP USING 'wallet_backup_file';

-- Step 3: Create and set the new TDE Master Encryption Key in the external vault
ADMINISTER KEY MANAGEMENT SET KEY 
USING TAG 'Prod_DB_Migration_Key' 
IDENTIFIED BY "okv_keystore_password" WITH BACKUP;

Test Cases for Managing 100 Databases

When validating your OKV or OCI Vault deployment across 100 databases, verify these test cases:

Test Case 1: Database Startup (Wallet Retrieval)

Objective: Ensure the database can fetch the MEK and open automatically upon restart.
Action: Bounce (shut down and restart) 5 randomly selected databases across your fleet.
Expected Result: The databases open successfully without raising ORA-28365: wallet is not open.

Test Case 2: Master Key Rotation

Objective: Verify seamless key rotation without downtime.
Action: Rotate the TDE Master Encryption Key on 3 active databases using the command: ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "password";
Expected Result: Keys are successfully registered in OKV/OCI Vault. Users can still read and write to the database (zero downtime).

Test Case 3: Offline/Loss of Connectivity Resilience

Objective: Test database behavior if the key vault goes offline.
Action: Temporarily block network traffic/firewall between 1 test database and OKV/OCI Vault. Attempt a SELECT statement on an encrypted table. [
Expected Result: Existing data in memory remains readable (cached DEKs), but new sessions or operations requiring a new key unwrap will fail gracefully. Once connectivity is restored, the database transparently reconnects to the vault.

Test Case 4: Fleet-Wide Audit Trail

Objective: Ensure centralized visibility into which database is accessing which keys.
Action: Review the OKV or OCI Vault logs after running a large batch operation on multiple databases.
Expected Result: The central key management dashboard logs distinct Get_Key or Wrap/Unwrap events corresponding to the specific database endpoints.

Oracle Key Vault (OKV) does not store actual table data; it centrally stores and manages the Transparent Data Encryption (TDE) Master Encryption Keys (Key Encryption Keys or KEKs). The actual table data remains inside the Oracle Database, encrypted by a Data Encryption Key (DEK).

When you rotate keys across 100 databases, OKV generates a new version of the TDE Master Key for each database. This happens completely online and without downtime, as old keys are preserved in OKV to decrypt existing data while the new key encrypts any newly created data.

Deep Dive: How Data is Stored (The Two-Tier Architecture)

Oracle uses a two-tier key management architecture to separate data from the keys that protect it:

+-------------------------------------------------------------+

|                  ORACLE KEY VAULT (OKV)                     |
|  [Master Key / KEK (V1)]  --->  [New Master Key / KEK (V2)] |
+-----------------------------------+-------------------------+
                                    | (Sends / Decrypts)
                                    v
+-------------------------------------------------------------+

|                  ORACLE DATABASE ENDPOINT                   |
|                                                             |
|  +-------------------------------------------------------+  |
|  | Oracle Wallet / PKCS#11 Caching Layer (In-Memory)     |  |
|  +-------------------------------------------------------+  |
|                                                             |
|  +---------------------+        +---------------------+  |
|  | Data Enc. Key (DEK) |------> | Data Tablespaces    |  |
|  | (Encrypted by KEK)  |        | (Encrypted on Disk) |  |
|  +---------------------+        +---------------------+  |
+-------------------------------------------------------------+

Tier 1: Data Encryption Key (DEK): This is a symmetric key (usually AES256) generated by the database engine. It encrypts and decrypts tablespace data blocks. The DEK is stored in an encrypted state inside the database file headers.
Tier 2: Key Encryption Key (KEK) / Master Key: This key encrypts the DEK. Instead of storing this locally in a .sso wallet file on the host operating system, the database connects as an Endpoint client using the standard OASIS KMIP protocol or a PKCS#11 library to pull/store the KEK inside OKV.
Inside OKV: The keys are securely kept within an internal Oracle database inside a hardened, FIPS-compliant Linux appliance appliance. In large setups, OKV is deployed as a Multi-Master Cluster (up to 16 nodes), keeping keys replicated in real time. [

What Happens Internally During a 100-Database Key Rotation?

If you trigger a key rotation across 100 databases (either sequentially via a script or simultaneously), the following internal pipeline triggers:

Zero Database Downtime: No active user transactions are stopped. Database tablespaces do not undergo massive re-encryption.
Request Initiation: Each database endpoint contacts OKV to request a new master key.
New Key Generation: OKV generates a brand new Master Key ID and key material for that specific database.
Re-wrapping the DEK: The database engine decrypts the current DEK using the old Master Key, then immediately re-encrypts (re-wraps) the DEK using the new Master Key. The newly wrapped DEK is written back to the database headers.
Historical Retention: The old Master Key is never deleted from OKV. It is marked as Historical/Retired. This ensures that old database backups, archivelogs, or older tablespace data blocks can still be decrypted when read.
OKV Cluster Sync: If OKV is clustered, the newly generated keys are synchronized across all multi-master nodes instantly.

Step-by-Step Test Case Scenario

Objective

Simulate an automated internal key rotation across 100 Oracle Databases managed by an Oracle Key Vault Cluster without disrupting continuous database operations.

Test Architecture Setup

Key Manager: 1x Oracle Key Vault Multi-Master Cluster.
Endpoints: 100x Oracle Databases (DB_1 to DB_100) using TDE.
Connection Method: Online Master Keys via PKCS#11 direct connection.

Step 1: Pre-Rotation Baseline Audit

Log into the OKV management console or use the okvcli tool to verify your database endpoints and count active keys.

bash

# Check endpoint registration status across your estate
okv admin endpoint list --all

Expected Status: 100 endpoints show status ACTIVE. Each database holds 1 active Master Key Version (Total: 100 keys in use).

Step 2: Continuous Workload Simulation

To prove there is no downtime during the migration, trigger a continuous insert loop simulation on one of the target databases (e.g., DB_45).

sql

-- Connect to Database 45
CONNECT app_user/password@DB_45;

-- Create an encrypted tablespace and test table
CREATE TABLESPACE secure_ts DATAFILE SIZE 100M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
CREATE TABLE test_secure (id NUMBER, secret_data VARCHAR2(100)) TABLESPACE secure_ts;

-- Start a continuous insertion loop block
BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO test_secure VALUES (i, 'Confidential Data Payload ' || i);
    COMMIT;
    DBMS_SESSION.SLEEP(0.1); -- Continuous active simulation
  END LOOP;
END;
/

Step 3: Executing Master Key Rotation

Execute the rotation. For 100 databases, doing this manually in a GUI is inefficient. Use an automated bash script running sqlplus commands sequentially or concurrently.

bash

#!/bin/bash
# Loop through all 100 databases to command a key rotation
for i in {1..100}
do
   echo "Rotating Master Key for DB_${i}..."
   sqlplus -s sys/SysPassword@DB_${i} as sysdba <<EOF
     ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "WalletPassword123";
     EXIT;
EOF
done

Note: For multitenant Container Databases (CDB), execute ALTER SYSTEM SET ENCRYPTION KEY... within the root container (CDB$ROOT) to rotate keys seamlessly across all underlying Pluggable Databases (PDBs).

Step 4: Verification of Internal Status Change

While the loop script from Step 3 executes, review your system logs to see what has altered.

1. On the Database Side (View Key History)

Query the database dictionary to confirm a new key activation timestamp.

sql

SELECT key_id, activation_time, status FROM v$encryption_keys;

Result: You will now observe two rows for the database. The old key version is marked RETIRED, and a new KEY_ID string shows status ACTIVE.

2. On the Oracle Key Vault Side

Verify that OKV generated 100 new keys while successfully preserving the original 100 old keys.

bash

# Total keys in OKV inventory should now be 200 (100 Active + 100 Historical)
okv object cryptographic-key list --all

3. Workload Validation

Check your running insertion script from Step 2. The loop will keep processing records successfully with 0 errors and zero dropped connections, verifying the transparent nature of internal key rotations via Oracle Key Vault

To register an Oracle Database 19c in Oracle Key Vault (OKV), you must complete tasks both inside the OKV management console (as a System/Key Administrator) and on your database server host (as the oracle OS user).

Phase 1: Configuration on the OKV Server Console

You must register the database as an endpoint, provision its virtual wallet, and retrieve its unique authentication token.

Register the Endpoint
- Log into the Oracle Key Vault Management Console as a System Administrator.
- Navigate to Endpoints and click Add.
- Fill in the fields: Endpoint Name (typically your database server hostname or unique target name) and Type (select Oracle Database).
- Click Register to produce a secure Enrollment Token. Copy this token immediately.
Create a Virtual Wallet & Link to Endpoint
- Go to the Keys & Wallets tab and select Wallets.
- Click Create, define a unique wallet name, and hit Save.
- Return to the Endpoints page and select the endpoint you generated in step 1.
- Find the Default Wallet profile block, select Choose Wallet, choose your new virtual wallet, and save.

Phase 2: Configuration on the Oracle 19c Server Host

Now you will use the server shell to deploy the client software downloaded securely using your token.

Download and Install the OKV Client Software
- Navigate to the target database server as the oracle user.
- Go to the WALLET_ROOT directory layout (such as /u01/app/oracle/admin/YOUR_SID/wallet/okv).
- Connect to the OKV Endpoint Enrollment Portal (via your browser at https://<OKV_IP_Address>) without logging into a profile.
- Click Endpoint Enrollment and Software Download at the bottom-right corner.
- Paste your saved Enrollment Token and click Submit Token.
- Download the generated okvclient.jar file and upload it to the /okv directory on your server.
- Unpack and trigger installation by running:
  bash
  java -jar okvclient.jar
  (Provide a local endpoint password when prompted or hit Enter to establish auto-login configuration).
Install the PKCS#11 Library (Single Host Run)
- Move into the expanded directory setup where the software extracted (e.g., /home/oracle/okvutil).
- Run the root library placement script:
  bash
  sudo ./root.sh
  Note: Ensure databases running on this server are temporarily offline when committing root.sh to update system-level TDE libraries correctly.

Phase 3: Update SQLNET parameters for TDE Integration

The final link routes Transparent Data Encryption (TDE) tasks explicitly to OKV rather than flat-file software wallets.

Edit sqlnet.ora File
- Edit your system file (found at $ORACLE_HOME/network/admin/sqlnet.ora) to point your system engine to OKV endpoints:
  ini
  # Example Configuration targeting OKV integration ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = OKV))
Configure Initialization Settings in SQL*Plus
- Log into your 19c engine interface via sysdba:
  sql
  sqlplus / as sysdba
  Tell the instance control parameter block to look for OKV modules:
- sql
  ALTER SYSTEM SET TDE_CONFIGURATION = "KEYSTORE_CONFIGURATION=OKV|FILE" SCOPE = BOTH;
  Reset the storage key to push the instance live:
- sql
  ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "your_okv_endpoint_password";

Your Oracle 19c instance is now registered with Oracle Key Vault, with keys safely decentralized inside the centralized vault

Question:How compute server communicate with cell srver in exadata

Compute servers communicate with Exadata storage cells using the high-speed InfiniBand/RoCE network. The database software utilizes an internal library called libcell, which offloads SQL processing, manages cell disks, and uses Remote Direct Memory Access (RDMA) for ultra-low latency without CPU intervention.

How Compute Servers Communicate with Cell Servers

Communication relies on an optimized, hardware-accelerated software stack:

InfiniBand/RoCE Fabric: The physical network backbone connecting all compute (database) and cell (storage) servers.
libcell Library: An Exadata-specific library linked to the Oracle Database and Grid Infrastructure. It translates standard database requests into specialized iDB (Intelligent Database) protocol messages.
RDMA (Remote Direct Memory Access): Allows compute node processes to directly read/write memory on the cell server without interrupting the cell’s CPU, ensuring microsecond-level latency.
Smart Scan Offloading: Instead of the storage cell sending raw data blocks back to the compute server for the DB to filter, the compute node sends the SQL statement parameters to the cell. The cell processes the query and returns only the filtered, relevant rows to the compute node.

The libcell layer is an Exadata-specific library on compute nodes that translates database I/O into intelligent Exadata protocol (iDB) commands. It enables offloading SQL processing directly to storage servers (Smart Scan) and uses RDMA over high-speed networks to bypass standard OS/network bottlenecks.

Example of libcell in Action: Smart Scan

When you run a SELECT COUNT(*) on a massive table, libcell parses the request. Instead of pulling millions of unneeded rows across the network, it instructs the Exadata Storage Servers (cells) to scan blocks locally, filter the data, and return only the aggregated result (e.g., the final row counts) to the compute node.

Core Test Cases & Verification

You can verify and test the libcell and iDB protocol operations using Oracle tracing, ASM alert logs, and Exadata tools.

1. Verifying iDB Protocol / libcell Usage

Goal: Confirm that your database instance is actively utilizing libcell to talk to Exadata cell servers.
Test Case: Run the following SQL to check the cell connection status and iDB protocol statistics:
sql
SELECT name, value FROM v$mystat JOIN v$statname USING (statistic#) WHERE name LIKE '%cell%iDB%';
Expected Output: A non-zero, continuously incrementing count of cell physical IO bytes eligible for smart scan, indicating libcell successfully passed offloaded queries to the storage layer.

2. Verifying Smart Scan (Offloading) Functionality

Goal: Ensure libcell is offloading processing so your database doesn't retrieve unnecessary data.
Test Case: Execute a full table scan on a large, unindexed table with a filter predicate in SQL*Plus:
sql
SET AUTOTRACE ON TRACEONLY SELECT COUNT(*) FROM hr.employees WHERE department_id = 90;
Expected Output: Look for table access (full) and check the session statistics. You should see cell smart table scan events being registered.

3. Tracing libcell / iDB Events

Goal: Diagnose performance bottlenecks or failures in the libcell layer.
Test Case: Enable cell disk tracing to see the exact iDB commands libcell sends to the cells. Execute the following in your session:
sql
ALTER SESSION SET EVENTS '10447 trace name context level 10'; SELECT /*+ FULL(t) */ COUNT(*) FROM my_large_table t WHERE my_col = 'TEST';
Expected Output: The trace file generated in the BACKGROUND_DUMP_DEST will show exact kcfis (Kernel Cache File I/O Services) and iDB packets mapping to Exadata storage cell IPs.

Exadata Interview Questions & Answers

Q1: What is the primary role of an Exadata Storage Server (Cell)?

Answer: A storage cell stores database files on physical disks but also performs intelligent processing. It is responsible for offloading database operations—such as filtering (Smart Scan), compression, and flash caching—directly at the storage tier, freeing up compute servers from unnecessary workloads.

Q2: What is a Smart Scan and how does it improve performance?

Answer: Smart Scan allows Oracle Database to push SQL processing (like WHERE clauses and column projections) down to the Exadata storage cells. Instead of transferring thousands of entire data blocks to the compute server over the network, the cell filters the data in memory and returns only the specific rows/columns requested by the query. This drastically reduces network traffic and compute server CPU utilization.

Q3: How do Compute and Cell servers communicate at the network level?

Answer: They communicate primarily over a high-speed, low-latency InfiniBand network or RoCE (RDMA over Converged Ethernet). The software uses Remote Direct Memory Access (RDMA) combined with Exadata's proprietary libcell layer to facilitate fast data transfers directly into database memory.

Q4: What is a Cell Disk vs. a Grid Disk?

Answer:

Cell Disks: Logical partitions created on the physical hard drives or flash drives inside the storage cell.
Grid Disks: Sub-divisions of Cell Disks that are presented up to the compute servers. These Grid Disks are grouped together by Automatic Storage Management (ASM) to create ASM disk groups.

Q5: What is the function of the Exadata Smart Flash Cache?

Answer: The Flash Cache is a hardware component in the Exadata storage cell that retains frequently accessed "hot" data in high-speed flash memory rather than spinning hard disks. It avoids slow physical disk I/O, dramatically reducing read and write latency.

Q6: Can Exadata Storage Cells communicate directly with each other?

Answer: Yes, introduced in Exadata Storage Server Software 12c, Cell-to-Cell Data Transfer allows cells to communicate directly without routing data through the compute servers. This cuts internal network traffic and compute server memory requirements in half during ASM rebalances, resilvering, and resynchronization

Compute servers communicate with Exadata storage cells via high-speed InfiniBand or RoCE (RDMA over Converged Ethernet) networks. They use the iDB protocol and a specialized libcell library to offload SQL operations directly to the cells, ensuring ultra-low latency and bypassing traditional network bottlenecks.

How the Communication and Processing Works

Database to Cell Communication: The Oracle Database uses the iDB (Intelligent Database) protocol to send SQL requests and storage commands (like Smart Scan) across the interconnect fabric. [
Storage Processing: Instead of shipping all blocks over to the compute server, the cell processes the data in the storage tier, filtering out irrelevant rows/columns, and returns only the finalized result set to the database memory.

Key Cell Server Background Processes

On the Exadata Storage Server (Cell), a few critical background processes run continuously: [

cellsrv: The primary cell server process. It manages I/O requests, handles Smart Scans, caches data in the Flash Cache, and coordinates disk operations.
ms (Management Server): Handles cell management commands (executed via CellCLI), monitors system hardware health, sends email alerts, and gathers performance metrics.
rs (Restart Server): Monitors the heartbeat of the cellsrv and ms processes. If any process fails, the rs process attempts to automatically restart the services and notifies administrators.

Q: What is IORM and how is it different from DBRM?

A: IORM (I/O Resource Manager) operates at the storage cell level to manage and throttle I/O bandwidth across multiple databases or consumer groups. DBRM (Database Resource Manager) operates at the database instance level and manages CPU and session allocations internal to a single database.

Q: Explain the difference between Cell Disks and Grid Disks.
A: A Cell Disk represents the raw, formatted physical storage presented to the cell software. A Grid Disk is a logical slice or partition created on top of the Cell Disk, which is then presented directly to the compute node to be used by Oracle ASM for creating disk groups.

Q: What happens if an Exadata Storage Server process like cellsrv crashes?

A: The rs (Restart Server) process monitors the heartbeat of the cellsrv process. If cellsrv fails to respond within an allowable period, the rs process restarts the service automatically and logs or sends an alert to the administrator. [

Question : dataguard ,swithover ,failover ,snapshot and perofrmance issue on dataguard and interview question and answer in details with example and test case

Data Guard Architecture

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. It maintains one or more standby databases as transactionally consistent copies of the production (primary) database.

Redo Transport Services

Redo transport services control the transfer of redo data from the primary database to the standby system. [

ASYNC (Asynchronous): The primary database commits transactions without waiting for acknowledgment from the standby database. This minimizes performance impact on the primary but introduces a risk of data loss if the primary fails before redo is transmitted.
SYNC (Synchronous): The primary database waits for confirmation from the standby database that the redo data has been received and written to disk before committing. This guarantees zero data loss but adds network latency to transaction commit times on the primary.

Data Protection Modes

Data Guard offers three protection modes to balance data availability against primary database performance.

Maximum Protection: Guarantees zero data loss. Redo must be written to the standby redo log on at least one standby database before the transaction commits on the primary. If the standby becomes unavailable, the primary database shuts down to prevent unprotected data changes. Requires SYNC transport.
Maximum Availability: Provides the highest level of data protection without risking primary database uptime. Transactions commit on the primary after confirmation from the standby. If the standby becomes unavailable, the primary automatically drops into an asynchronous state to keep processing. It reverts to synchronous operation once the standby reconnects. Requires SYNC transport.
Maximum Performance: This is the default mode. It prioritizes primary database performance over absolute data protection. Transactions commit on the primary as soon as redo is written to the local online redo logs. Redo is sent to the standby asynchronously (ASYNC). There is a minor risk of data loss during an unexpected primary failure.

Role Transitions: Switchover vs. Failover

Role transitions alter the database roles between a primary database and its standby counterparts. [

Switchover (Planned): Primary ──► Standby  and  Standby ──► Primary
Failover (Unplanned): Primary (Failed) ──✖── Standby ──► Primary (New)

Switchover (Planned Event)

A switchover is a planned role reversal designed to minimize downtime during hardware upgrades, OS patches, or routine maintenance. It incurs zero data loss and does not require recreating or flashing back the old primary database.

sql

-- Step 1: Verify primary is ready for switchover
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
-- Expected output: TO STANDBY or SESSIONS ACTIVE

-- Step 2: Initiate switchover on primary (Oracle 12c and later)
SQL> ALTER DATABASE SWITCHOVER TO standby_db_name VERIFY;
SQL> ALTER DATABASE SWITCHOVER TO standby_db_name;

-- Step 3: Open the new primary database (on the old standby site)
SQL> ALTER DATABASE OPEN;

Failover (Unplanned Event)

A failover occurs when the primary database encounters an unrecoverable failure (e.g., hardware crash, natural disaster). The standby database is abruptly promoted to the primary role. Depending on the protection mode, a failover may result in data loss. The original primary must typically be reinstated using Flashback Database or rebuilt.

sql

-- Step 1: Check for redo gaps on the standby
SQL> SELECT GAP_STATUS FROM V$ARCHIVE_GAP;

-- Step 2: Finish applying all received redo on standby
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

-- Step 3: Change role from standby to primary
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
-- Or if using Data Guard Broker:
DGMGRL> FAILOVER TO standby_db_name;

-- Step 4: Open the new primary database
SQL> ALTER DATABASE OPEN;

Snapshot Standby

A Snapshot Standby is a fully writeable standby database. It receives and archives redo data from the primary database but does not apply it. This role allows you to use the standby for application testing, benchmarking, or reporting while preserving disaster recovery protection. [

When you convert it back to a Physical Standby, Oracle uses internal flashback mechanisms to discard all local modifications and then catches up with the primary by applying the accumulated redo logs. [

[Physical Standby] ──► Convert ──► [Snapshot Standby (Read-Write Test)]
                                          │
                                    Convert Back
                                          ▼
[Physical Standby] ◄── Apply Redo ◄──────┘

Test Case: Creating and Reverting a Snapshot Standby

This test case demonstrates how to convert a Physical Standby to a Snapshot Standby for isolated testing and then return it to its original state.

sql

-- Prerequisites: Ensure Flashback Database is enabled on the Standby
SQL> SELECT FLASHBACK_ON FROM V$DATABASE; -- Must be YES

-- Step 1: Stop Redo Apply on the Physical Standby
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Step 2: Convert to Snapshot Standby
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

-- Step 3: Restart and open the database in Read-Write mode
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
-- The database is now writeable. Perform destructive application testing here.

-- Step 4: Revert back to Physical Standby after testing
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

-- Step 5: Restart in mount or read-only mode and restart Redo Apply
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT; -- Or STARTUP REED ONLY (for Active Data Guard)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Data Guard Performance Issues & Troubleshooting

Performance bottlenecks in Data Guard generally fall into two categories: network transport delays or log apply lag on the standby database.

Common Performance Bottlenecks

Network Latency and Throughput: High network round-trip times (RTT) or insufficient bandwidth choke SYNC transport modes, causing transaction stalls on the primary database.
Disk I/O Bottlenecks: Slow storage sub-systems on the standby database cannot write archived logs or standby redo logs fast enough.
Insufficient Standby CPU or Parallelism: Redo apply is a CPU-intensive operation. If the standby database lacks processing power or parallel workers, it falls behind the primary.

Step-by-Step Diagnostic Methodology

1. Identify Lag and Transport Performance

Run this query on the standby database to find the exact transport and apply lag metrics.

sql

SQL> SELECT NAME, VALUE, DATATYPE, TIME_COMPUTED 
     FROM V$DATAGUARD_STATS 
     WHERE NAME IN ('transport lag', 'apply lag');

2. Check Redo Transport Process Status

Check if the log writer (LGWR) or async network helper processes (ASYNC) are waiting on network resources.

sql

SQL> SELECT PROCESS, STATUS, CLIENT_PROCESS, SEQUENCE#, BLOCK# 
     FROM V$MANAGED_STANDBY;

3. Analyze Wait Events on Primary and Standby

Look for high-latency Data Guard wait events in V$SYSTEM_EVENT.

SYNC mode issues on Primary: Look for high wait counts or long times on LGWR-transport wait, Net timeout for standby, or redo transport synchronous write.
ASYNC mode issues on Primary: Look for LNS wait on SENDREQ.
Standby Media Recovery issues: Look for checkpoint completed, free buffer waits, or log file sequential read during redo application.

4. Tuning Redo Transport and Apply

Increase Network Buffers: Adjust the SDU (Session Data Unit) size to 65535 in tnsnames.ora and listener.ora profiles to optimize network packet size.
Optimize Oracle Net Parameters: Increase TCP.NODELAY=YES in sqlnet.ora to force immediate packet transmission.

Enable Parallel Media Recovery: Accelerate redo apply on the standby database by utilizing multiple CPU cores.

sql

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT DEFAULT PARALLEL;
-- Or specify explicit core count:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION PARALLEL 8;

Q1: What is the difference between physical standby, logical standby, and snapshot standby?

Physical Standby: A block-for-block identical copy of the primary database. It uses Redo Apply to maintain parity. It can be opened in read-only mode using Active Data Guard.
Logical Standby: Contains the same logical information as the primary, but the physical data organization and structure can differ. It uses SQL Apply to transform redo data into SQL transactions (INSERT, UPDATE, DELETE) and executes them on the standby. This allows users to create custom indexes and materialized views on the standby for specialized reporting.
Snapshot Standby: A physical standby converted into a temporary, modifiable read-write database. It receives but does not apply redo logs. It can be reverted back to a physical standby at any point by discarding all local changes.

Q2: What are Standby Redo Logs (SRLs), and why are they mandatory for real-time apply?

Answer: Standby Redo Logs are structural logs on the standby database that mirror the online redo logs of the primary database. When the primary writes to its online redo log, it simultaneously streams that redo data to the standby's SRLs over the network.
This enables Real-Time Apply, allowing the standby database to apply changes immediately as they arrive, rather than waiting for an archived log file to fill and switch. SRLs are strictly mandatory for Maximum Protection and Maximum Availability modes.
Sizing Rule: The standby database should have at least one more standby redo log group than the primary database's online redo log groups: (Number of OLRL Groups on Primary + 1) * Number of Threads.

Q3: You execute a switchover command, and it hangs. How do you investigate and resolve the issue?

Answer: A hanging switchover is typically caused by uncommitted active transactions on the primary, unapplied redo logs on the standby, or unacknowledged network connections.
Investigation Steps:
1. Query V$MANAGED_STANDBY on both sites to see if the log transport or apply processes are stuck on a specific sequence number.
2. Check V$SESSION on the primary to identify long-running, active user transactions that prevent the database from closing cleanly.
3. Review the Alert Logs (alert_.log) on both primary and standby databases for error codes like ORA-16014 (sequence gap) or network timeouts.
Resolution: Terminate hanging user sessions on the primary using ALTER SYSTEM KILL SESSION. If a redo gap is found, resolve the network obstruction or manually copy and register the missing archive logs on the standby using ALTER DATABASE REGISTER LOGFILE 'path'.

Q4: How do you configure Data Guard to achieve absolute Zero Data Loss?

Answer: To guarantee zero data loss, configure the environment for Maximum Protection mode using these structural components:
1. Create Standby Redo Logs on the standby database that match or exceed the primary log size.
2. Set the redo transport parameter to SYNC and AFFIRM within the LOG_ARCHIVE_DEST_n string on the primary.
3. Upgrade the protection mode via SQL:
  sql
  SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
  Configure at least two separate standby destinations to ensure that a single infrastructure failure does not shut down the primary database.

Q5: What is Active Data Guard, and what are its licensing implications?

Answer: Active Data Guard is a premium option that allows a physical standby database to remain open for read-only user access (such as queries, reports, or backups) while Real-Time Apply is running. In a standard Data Guard configuration, the standby database cannot apply redo logs while open for read-only queries.
Active Data Guard requires an extra software license beyond the standard Oracle Enterprise Edition license.

Q6: If a primary database suffers sudden corruption, does it replicate instantly to the standby?

Answer: It depends entirely on the type of corruption:
- Physical Corruption (e.g., bad disk sector, media failure): This does not replicate. The primary database fails to read the corrupt block locally, but valid logical transactions continue generating standard redo data. The standby remains healthy.
- Logical Corruption (e.g., an accidental DROP TABLE or a buggy application script corrupting data values): This replicates instantly. The primary treats these as legitimate data modifications, generates corresponding redo logs, and streams them directly to the standby database.
Mitigation: To recover from logical corruption, use Flashback Database on both the primary and standby databases to roll back the entire architecture to a specific timestamp before the corruption occurred.

Oracle GoldenGate Architecture

Oracle GoldenGate functions by decoupling data capture from data delivery via intermediate transaction logs. It consists of Classic and Microservices Architectures.

+---------------------------------------------------------------------------------+

|                               SOURCE SYSTEM                                     |
|  +------------------+      +------------------+      +-----------------------+  |
|  |   Source DB      | ---> | Extract Process  | ---> | Local Trail File      |  |
|  | (Redo/Arch Logs) |      | (Capture Engine) |      | (Intermediate Storage)|  |
|  +------------------+      +------------------+      +-----------------------+  |
|                                                                  |              |
|                                                                  v              |
|                                                      +-----------------------+  |
|                                                      | Data Pump (Extract)   |  |
|                                                      +-----------------------+  |
+------------------------------------------------------------------|--------------+
                                                                   | TCP/IP
                                                                   v
+---------------------------------------------------------------------------------+

|                               TARGET SYSTEM                                     |
|  +------------------+      +------------------+      +-----------------------+  |
|  |   Target DB      | <--- | Replicat Process | <--- | Remote Trail File     |  |
|  |                  |      | (Delivery Engine)|      | (Received Data)       |  |
|  +------------------+      +------------------+      +-----------------------+  |
+---------------------------------------------------------------------------------+
* Note: The Manager Process runs independently on both nodes to monitor and control resources.

Core Components

Manager: The control process that starts, restarts, and monitors other OGG processes, managing disk space and trail files.
Extract: The capture mechanism that scans source database transaction logs (redo/archive logs) to collect committed DML/DDL modifications.
Trail Files: Binary files stored on disk that contain captured database modifications sequentially to ensure network and system crash survival.
Data Pump: A secondary, optional extract process that reads local trail files and routes them across the network via TCP/IP to the target node.
Collector: A background server process on the target node that receives trailing data from the data pump and writes it to remote trail files.
Replicat: The delivery engine on the target machine that reads remote trail files, parses operations, maps schemas, and executes transactions against the destination database.

Online Step-by-Step: Adding a New Table

To safely introduce a new table (HR.BONUS) into an existing, live replication stream without restarting or interrupting active replications:

Step 1: Prep Source Supplemental Logging

sql

-- Lock in transaction visibility by adding logging on the new table
ALTER TABLE HR.BONUS ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Step 2: Update Extract Parameters

Edit the source primary extract parameter file (ext1.prm) and pump parameter file (pmp1.prm): text

TABLE HR.BONUS;

Activate changes gracefully in GGSCI:

text

GGSCI> RELOAD EXTRACT EXT1
GGSCI> RELOAD EXTRACT PMP1

Step 3: Capture the Sync Boundary

sql

-- Retrieve current System Change Number (SCN) on the source database
SELECT CURRENT_SCN FROM V$DATABASE; 
-- Example Output SCN: 1452980

Step 4: Export and Import Data (Initial Load)

Perform an offline data export or online point-in-time pump:

bash

expdp hr/pass directory=DATA_PUMP_DIR tables=HR.BONUS 
flashback_scn=1452980 dumpfile=bonus.dmp
impdp hr/pass directory=DATA_PUMP_DIR dumpfile=bonus.dmp

Step 5: Configure and Start Replicat

Edit the target replicat parameter file (rep1.prm):

text

MAP HR.BONUS, TARGET HR.BONUS, FILTER (@GETENV ('TRANSACTION', 'CSN') > 1452980);

Note: The @GETENV filter prevents GoldenGate from attempting to apply transactions captured prior to the initial load boundary, eliminating constraint conflict exceptions.

Conflict Detection and Resolution (CDR)

Conflicts happen in active-active (bidirectional) architectures when identical rows are altered concurrently across differing nodes.

Detection Mechanics

OGG uses COMPARECOLS to contrast the before image (the record values before an update occurred on the source) with the target's current row values. If a divergence is found, a conflict flag is raised.

Resolution Techniques

OVERWRITE / USEDUR: Dictates that the source state always wins or forces explicit execution rules.
USEMAX / USEMIN: Compares a scalar value (like a timestamp column) and chooses the higher/lower value. [

Example Parameter Configuration

text

MAP HR.EMPLOYEES, TARGET HR.EMPLOYEES,
  COMPARECOLS (ALL),
  RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (LAST_MOD_DATE)));

Managing Heterogeneous Structures (Different Source & Target)

When the source table structural layout differs from the target, GoldenGate requires a source definitions map file (defgen) to understand data layouts.

Technical Scenario Matrix & Solutions

Discrepancy Type	Engineering Fix	Replicat Syntax Implementation
Target has extra fields	Nullify or supply structural defaults	`MAP SRC.DATA, TARGET TGT.DATA, COLMAP (USEDEFAULTS, TGT_ADD_COL = "DEFAULT_VAL");`
Target missing columns	Filter structural drops safely	`MAP SRC.DATA, TARGET TGT.DATA, COLMAP (USEDEFAULTS);` (Drops unmapped source fields)
Different Column Names	Explicit explicit positional mappings	`MAP SRC.DATA, TARGET TGT.DATA, COLMAP (TGT_ID = SRC_ID, TGT_NAME = SRC_NAME);`

How to build a Definition File (DEFGEN)

Create a parameter file named defgen.prm:

text

DEFSFILE ./dirdef/source_definitions.def
USERIDGSB hr, PASSWORD pass
TABLE HR.EMPLOYEES;

Run the defgen utility from the command line:

bash
./defgen paramfile ./dirprm/defgen.prm
Transfer the file to the target machine and reference it in the Replicat parameter file (rep1.prm):

text

SOURCEDEFS ./dirdef/source_definitions.def

Conflict Detection and Resolution (CDR) in Oracle GoldenGate ensures data consistency across active-active (bi-directional) environments when multiple databases update the same row simultaneously.

Key Differences: GoldenGate 12c vs. 19c

Historically, CDR evolved from a manual parameter configuration into a highly integrated database automation tool.

GoldenGate 12c (Early Versions < 12.3): Relied purely on Manual CDR configured inside the Replicat parameter file (.prm). It used parameter combinations like COMPARECOLS and RESOLVECONFLICT. Data modifications required manual tracking columns (e.g., application-level timestamps).
GoldenGate 12c (12.3+) & GoldenGate 19c: Features Automatic CDR (Auto-CDR). Configuration is moved directly into the Oracle Database engine via the DBMS_GOLDENGATE_ADM PL/SQL package. The database automatically creates invisible timestamp columns (CDRTS$ROW) and hidden "tombstone" tables to track deleted logs, eliminating the need to alter application logic or schemas. ]

Core Evolution: GoldenGate 12c vs. 19c

The primary distinction across these versions is how CDR is configured and executed:

Feature / Capability	Oracle GoldenGate 12c (Pre-12.3)	Oracle GoldenGate 12c (12.3+) & 19c
Method Name	Manual CDR	Automatic CDR (Auto-CDR)
Configuration Location	Replicat Parameter File (`.prm`)	Oracle Database PL/SQL level
Parameters / Procedures	`COMPARECOLS` and `RESOLVECONFLICT`	`DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR`
Tracking Mechanism	Requires manual app-facing timestamp columns.	Invisible timestamp columns created by DB.
Deletions Tracking	Difficult to track if row is missing entirely.	Uses internal tombstone tables (`DT$_...`).

Resolution Types and Examples

GoldenGate handles conflicts depending on the operation type:

Conflict Type [	Scenario Description	Common Resolution Strategy
`INSERTROWEXISTS`	A row with the same primary key is inserted on both sites.	`USEMAX` (Keep the row with the newer timestamp) or `OVERWRITE`.
`UPDATEROWEXISTS`	The row exists, but its current values differ from the source's before-image.	`USEMAX` (Winner is based on the latest update time).
`UPDATEROWMISSING`	Site A tries to update a row that Site B has already deleted.	`DISCARD` (Ignore the update) or `OVERWRITE` (Turn the update into an insert).
`DELETEROWMISSING`	Both sites try to delete the exact same row.	`IGNORE` / `DISCARD` (Accept that the row is already gone).

Major Advantages of Auto-CDR

No Schema Alterations: Invisible timestamp columns mean the application doesn't see or manage tracking fields.
Zero Replicat Overhead: Eliminates complex RESOLVECONFLICT mapping logic in parameter files.
Granular Logic (Column Groups): You can group specific columns together so that updates to unrelated parts of the same row do not trigger a false conflict.

Practical Example Setup

Consider a bi-directional replication setup between two pluggable databases: PDB_A and PDB_B. They replicate a shared table named HR.EMPLOYEES.

1. SQL Schema Creation (Executed on both sites)

sql

CREATE TABLE hr.employees (
    emp_id     NUMBER PRIMARY KEY,
    emp_name   VARCHAR2(50),
    salary     NUMBER
);

2. Configure Auto-CDR (Executed via PL/SQL on both sites)

Execute this snippet as your GoldenGate admin user on both PDB_A and PDB_B: []

sql

BEGIN
    DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
        schema_name => 'HR',
        table_name  => 'EMPLOYEES'
    );
END;
/

Note: This operation implicitly generates an invisible timestamp tracking column named CDRTS$ROW on the table. []

3. GoldenGate Replicat Configuration

Because the database handles detection natively, your Replicat parameter files on both servers only need to include the MAPINVISIBLECOLUMNS flag to ensure the internal timestamp propagates safely:

text

REPLICAT rep_b
USERIDGSALIAS ogg_admin
MAPINVISIBLECOLUMNS
MAP hr.employees, TARGET hr.employees;

Test Cases & Scenario Simulations

The three most critical test cases to validate your CDR configuration focus on multi-site data intersection rules.

Test Case 1: UPDATE Conflict (Latest Timestamp Wins)

Objective: Ensure the most recent change overwrites older data when two sites modify the same record.
Initial State: Both databases contain (emp_id: 101, emp_name: 'John', salary: 5000).
Execution:
1. At 10:00:01 AM, execute on PDB_A:
  sql
  UPDATE hr.employees SET salary = 6000 WHERE emp_id = 101; COMMIT;
  At 10:00:02 AM, execute on PDB_B:
2. sql
  UPDATE hr.employees SET salary = 6500 WHERE emp_id = 101; COMMIT;
  Expected Result: Because PDB_B committed its statement with a later internal timestamp, its update overrides the value. Both PDB_A and PDB_B converge on a final salary of 6500.

Test Case 2: INSERT Conflict (Uniqueness / Collision)

Objective: Handle scenarios where the same Primary Key value is generated and inserted concurrently on both sites.
Initial State: Table is completely empty on both databases.
Execution:
1. At 10:05:01 AM, execute on PDB_A:
  sql
  INSERT INTO hr.employees VALUES (202, 'Alice', 7000); COMMIT;
  At 10:05:03 AM, execute on PDB_B:
2. sql
  INSERT INTO hr.employees VALUES (202, 'Bob', 8000); COMMIT;
  Expected Result: Replicat finds that row 202 already exists. It evaluates the internal CDRTS$ROW values. Since PDB_B has a newer timestamp, its record wins. Both databases resolve to 202, 'Bob', 8000.

Test Case 3: DELETE / UPDATE Conflict (Row Missing)

Objective: Handle situations where Site A modifies a row that Site B deleted at nearly the same moment.
Initial State: Both databases contain (emp_id: 303, emp_name: 'Charlie', salary: 4000).
Execution:
1. At 10:10:01 AM, execute on PDB_A (Delete always takes priority by default in classic timestamp rules, or depends on the designated configuration):
  sql
  DELETE FROM hr.employees WHERE emp_id = 303; COMMIT;
  At 10:10:02 AM, execute on PDB_B:
2. sql
  UPDATE hr.employees SET salary = 4500 WHERE emp_id = 303; COMMIT;
Expected Result: When PDB_B's update replicates to PDB_A, the row is missing. Replicat checks the hidden delete tombstone table on PDB_A. Since the delete occurred earlier but represents a removal, the incoming update on the non-existent row is suppressed or discarded. The row remains deleted on both sites.

Troubleshooting Matrix & Performance Tuning

1. Common Operational Roadblocks

Process Abend (ABENDED): Inspect using VIEW REPORT <process_name> to view specific database constraint violations or physical filesystem limitations.
Replicat Lag: Run SEND REPLICat <name>, BATCHSTATUS or LAG REPLICAT <name> to trace database contention.
Missing Supplemental Logging: Causes OGG-00727 errors where updates lack full conditional context.

2. Performance Tuning Strategies

Database Log Performance: Ensure the filesystem storing your transactional database logs is optimized for rapid sequential I/O (like Oracle ASM) to prevent capture-side waits.
Network Tuning: Adjust TCPBUFSIZE and TCPFLUSHBYTES parameters inside the Data Pump to stream larger frame packets across the wire.
Replicat Optimization: Use BATCHSQL inside the replicat parameter file to group individual array statements into array execution operations, speeding up application execution.
Process Splitting: Split a highly active single schema into multiple separate Extract/Replicat processes to achieve parallel processing.

Technical Interview QA & Test Case Simulation

Q1: A Replicat abended with an OGG-01296 Error: Key map mismatch... after columns were added to the source. How do you resolve this without data loss?

Answer: This indicates the source and target table structures are out of sync. If DDL replication is not configured, you must regenerate the definitions file. Run the defgen utility against the source database to update structural changes, ship the resulting definitions file to the target system, place it under ./dirdef/, and restart the Replicat.

Q2: What is the risk of using HANDLECOLLISIONS continuously in production?

Answer: HANDLECOLLISIONS is designed exclusively for initial table synchronizations. It forces GoldenGate to convert target duplicate row insert failures into updates and safely ignore missing-row update/delete errors. Leaving this active in standard operations risks silently masking deep data integrity synchronization issues between databases.

Comprehensive Test Case Simulation

Test Scenario

Validate that a transaction replicates across asymmetrical table schemas (SRC.USER_INFO to TGT.CLIENT_PROFILE) where column names and datatypes differ.

Source Schema: SRC.USER_INFO (USER_ID INT PK, USER_NAME VARCHAR2(50))
Target Schema: TGT.CLIENT_PROFILE (CLIENT_CODE INT PK, FULL_NAME VARCHAR2(100), ACCOUNT_STATUS VARCHAR2(10))

Step 1: Target Replicat Configuration File (rep_test.prm)

text

REPLICAT rep_test
USERID OGG_USER, PASSWORD secret
SOURCEDEFS ./dirdef/test_structures.def

MAP SRC.USER_INFO, TARGET TGT.CLIENT_PROFILE,
  COLMAP (
    CLIENT_CODE = USER_ID,
    FULL_NAME = USER_NAME,
    ACCOUNT_STATUS = "ACTIVE"
  );

Step 2: Run the Execution Test

Execute a single row addition on the source node database:

sql

INSERT INTO SRC.USER_INFO (USER_ID, USER_NAME) VALUES (999, 'Alex Smith');
COMMIT;

Verify delivery by querying the target database or checking statistics in GGSCI:

SELECT CLIENT_CODE, FULL_NAME, ACCOUNT_STATUS FROM TGT.CLIENT_PROFILE WHERE CLIENT_CODE = 999;

Expected Query Result:

text

CLIENT_CODE   FULL_NAME     ACCOUNT_STATUS
-------------------------------------------
999           Alex Smith    ACTIVE

Run STATS in GGSCI to confirm the operation was successfully processed:

text

GGSCI> STATS REPLICAT rep_test
Sending STATS request to REPLICAT REP_TEST ...
Start of Statistics at 2026-06-23 10:52:15.

Replicating from SRC.USER_INFO to TGT.CLIENT_PROFILE:
*** Total inserts: 1 ***
*** Total updates: 0 ***
*** Total deletes: 0 ***

EasyReliableDBA

Monday, 22 June 2026

Oracle DBA interview question and answer 2026

Question : How to app patch OMS/OEM

Question : How to configure resource manger in 19c Database.

Question : How to manage and configure observer in data guard in 19c database question and answer with example and test cases

Question : what is key is managed in oracle and OCI

Question:How compute server communicate with cell srver in exadata

No comments:

Post a Comment

Search This Blog