Wednesday, 27 August 2025

Database Credentials in Oracle Wallet

When managing Oracle Databases, storing user credentials in plain text inside scripts is a major security risk. Oracle provides a secure solution with Oracle Wallet, which allows you to store database credentials in an encrypted location. Once configured, applications and utilities like Data Pump or JDBC can connect without requiring a clear-text password.

This blog walks through the step-by-step procedure to store database credentials in Oracle Wallet and use it securely.

Overview

The Oracle Wallet securely stores database credentials, eliminating the need to expose passwords in scripts.

Multiple credentials can be stored in a single wallet.
Supports auto-login, meaning applications don’t need a password to access it.
OS file permissions ensure security.

In this example, a non-Oracle binary owner OS user (bidhan) is used to create the wallet.

Example usage:

Data Pump with Wallet


nohup expdp /@BSA1EP directory=DUMP_DIR dumpfile=scott_%U.dmp logfile=EXP_SCOTT.log schemas=scott parallel=4 &

JDBC with Wallet


Connection conn = DriverManager.getConnection("jdbc:oracle:oci:/@BSA1EP");

Step 1: Create Oracle Wallet Directory

Check wallet status:


SET LINES 180
COL wrl_type FORMAT a12
COL wrl_parameter FORMAT a35
COL status FORMAT a30
SELECT * FROM v$encryption_wallet;

If not present, create directory:


[root@dg1 ~]# mkdir -p /u01/admin/BSA1EP/wallet
[root@dg1 ~]# chown -R bidhan:bidhan /u01/admin/BSA1EP/wallet

Step 2: Create TNS ADMIN Directory (for `bidhan`)


[root@dg1 ~]# mkdir -p /u01/admin/BSA1EP/wallet/TNS_ADMIN
[root@dg1 ~]# chown -R bidhan:bidhan /u01/admin/BSA1EP/wallet/TNS_ADMIN

Step 3: Add TNS Entry (for `oracle`)

Update sqlnet.ora and tnsnames.ora:

sqlnet.ora


SQLNET.WALLET_OVERRIDE = FALSE
WALLET_LOCATION = (SOURCE = (METHOD = FILE)(METHOD_DATA = (DIRECTORY = /u01/admin/BSA1EP/wallet)))

tnsnames.ora


BSA1EP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.bidhan.com)(PORT = 1622))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = BSA1EP)
    )
  )

Step 4: Export Oracle Variables

For OS user bidhan:


export ORACLE_SID=BSA1EP
export ORACLE_HOME=/oracle/1900
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=/u01/admin/BSA1EP/wallet/TNS_ADMIN

Step 5: Create Password-Protected Oracle Wallet


[bidhan@dg1 wallet]$ orapki wallet create -wallet /u01/admin/BSA1EP/wallet -auto_login

After success, check files:


ls -ltr /u01/admin/BSA1EP/wallet
cwallet.sso
ewallet.p12

Step 6: Add Database Credentials to Wallet


[bidhan@dg1 ~]$ mkstore -wrl /u01/admin/BSA1EP/wallet -createCredential BSA1EP test
Enter your secret/Password: *****
Re-enter your secret/Password: *****
Enter wallet password: *****

Step 7: Verify Database Connection


[bidhan@dg1 ~]$ sqlplus /@BSA1EP
SQL> show user
USER is "TEST"

Step 8: List Credentials in Wallet


mkstore -wrl /u01/admin/BSA1EP/wallet -listCredential

Output:


1: BSA1EP test

Step 9: Modify Credentials in Wallet

After DB password change:


mkstore -wrl /u01/admin/BSA1EP/wallet -modifyCredential BSA1EP test newpassword

Step 10: View Wallet Contents


orapki wallet display -wallet /u01/admin/BSA1EP/wallet

Step 11: Change Wallet Password


orapki wallet change_pwd -wallet /u01/admin/BSA1EP/wallet

Step 12: Delete Credentials from Wallet


mkstore -wrl /u01/admin/BSA1EP/wallet -deleteCredential BSA1EP

Step 13: Delete Oracle Wallet

Remove wallet files:


rm -f /u01/admin/BSA1EP/wallet/*wallet*

Key Takeaways

Oracle Wallet eliminates password exposure in scripts.
Supports auto-login for seamless integration.
Credentials can be securely added, updated, listed, or deleted.
Works perfectly with Data Pump, RMAN, JDBC, and batch jobs.

Sunday, 24 August 2025

Oracle 19c Active Data Guard Switchover Procedure

Oracle Active Data Guard provides high availability and disaster recovery by allowing seamless role transitions between the primary and standby databases. A switchover is a planned role reversal where the primary database becomes the standby, and the standby takes over as the new primary without data loss.

This guide covers the step-by-step procedure for switchover in Oracle 19c Active Data Guard.

1. Pre-Checks

Before initiating a switchover, ensure both databases are synchronized and healthy.

On both primary and standby:


SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

Primary should show: PRIMARY, READ WRITE
Standby should show: PHYSICAL STANDBY, READ ONLY WITH APPLY or MOUNTED

Also, confirm there are no active sessions or long-running transactions on the primary.

2. Verify Log Transport and Apply

Ensure redo logs are being shipped and applied correctly.

On Primary:


SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST;

On Standby:


SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY;

ARCH and LGWR processes should be running on primary.
MRP (Managed Recovery Process) should be running on standby.

3. Prepare Standby (Optional)

If standby is running in read-only with apply, cancel recovery before switchover:


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

4. Switchover Command (Primary)

On the Primary Database:


ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

The database is now in standby mode.

5. Activate New Primary (Standby)

On the Standby Database:


ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;

The standby is now promoted to the new primary database.

6. Convert Former Primary to Standby

On the Former Primary (now mounted as standby):


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

This re-establishes redo apply.

7. Verify New Roles

On both databases:


SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

Expected Results:

New Primary → PRIMARY, READ WRITE
New Standby → PHYSICAL STANDBY, MOUNTED or READ ONLY WITH APPLY

8. Post-Checks

Validate the overall Data Guard configuration.

Using Data Guard Broker (DGMGRL):


DGMGRL> SHOW CONFIGURATION;

If Data Guard Broker is configured, switchover is much simpler:


DGMGRL> CONNECT sys/password@primary;
DGMGRL> SWITCHOVER TO 'standby_db_unique_name';
DGMGRL> SHOW CONFIGURATION;

Conclusion

A switchover in Oracle 19c Active Data Guard ensures a zero data loss, planned role reversal between primary and standby databases. This is typically done during planned maintenance, patching, or to test disaster recovery readiness.

Oracle 19c Active Data Guard Configuration on Linux – Step by Step

Oracle Active Data Guard provides high availability, data protection, and disaster recovery by maintaining a synchronized copy of the primary database on a standby system. In this guide, we will configure Oracle 19c Active Data Guard between two Linux servers.

1. Environment Setup

Primary Database: primary.localdomain (192.168.0.187)
Standby Database: standby.localdomain (192.168.0.188)

Update /etc/hosts on both servers:


192.168.0.187 primary.localdomain primary
192.168.0.188 standby.localdomain standby

Disable firewall & SELinux (if required):


systemctl stop firewalld
setenforce 0

2. Enable FORCE LOGGING & ARCHIVELOG Mode on Primary

On the Primary Database:


ALTER DATABASE FORCE LOGGING;
ARCHIVE LOG LIST;

If not in ARCHIVELOG mode:


SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

3. Configure Primary Initialization Parameters

Update the parameters using ALTER SYSTEM:


ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET FAL_SERVER=standby;
ALTER SYSTEM SET FAL_CLIENT=primary;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u02/oradata/standby/','/u02/oradata/primary/';
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u02/oradata/standby/','/u02/oradata/primary/';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

4. Configure `tnsnames.ora` on Both Servers

Edit $ORACLE_HOME/network/admin/tnsnames.ora:


primary =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
   (CONNECT_DATA = (SERVICE_NAME = primary))
 )

standby =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
   (CONNECT_DATA = (SERVICE_NAME = standby))
 )

5. Create Standby Redo Logs on Primary

Add standby redo logs (same size as primary redo logs, +1 group):


ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u02/oradata/primary/standby_redo04.log') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u02/oradata/primary/standby_redo05.log') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u02/oradata/primary/standby_redo06.log') SIZE 500M;

6. Create Password File & Transfer to Standby

On Primary:


orapwd file=$ORACLE_HOME/dbs/orapwprimary password=oracle entries=10 force=y
scp $ORACLE_HOME/dbs/orapwprimary oracle@standby:$ORACLE_HOME/dbs/orapwstandby

7. Take RMAN Backup & Transfer to Standby

On Primary:


rman target /
RUN {
  BACKUP DATABASE PLUS ARCHIVELOG;
  BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/oradumps/standby_ctrl.bkp';
}
scp /oradumps/* oracle@standby:/oradumps/

8. Restore Database on Standby

On Standby:


rman target /
STARTUP NOMOUNT;
RESTORE CONTROLFILE FROM '/oradumps/standby_ctrl.bkp';
ALTER DATABASE MOUNT;
CATALOG START WITH '/oradumps/';
RESTORE DATABASE;
RECOVER DATABASE;

9. Configure Standby Initialization Parameters

Set parameters (reverse role values compared to primary):


ALTER SYSTEM SET DB_UNIQUE_NAME=standby;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET FAL_SERVER=primary;
ALTER SYSTEM SET FAL_CLIENT=standby;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u02/oradata/primary/','/u02/oradata/standby/';
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u02/oradata/primary/','/u02/oradata/standby/';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

10. Start Managed Recovery

On Standby Database:


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

For Active Data Guard (read-only + apply mode):


ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

11. Verify Configuration

Run these checks on both Primary and Standby:


SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;
SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST;

Expected output:

Primary DB → PRIMARY, READ WRITE
Standby DB → PHYSICAL STANDBY, READ ONLY WITH APPLY

Conclusion

With this configuration, your Oracle 19c Active Data Guard environment is now up and running. Always validate in a test environment before production deployment. Active Data Guard ensures business continuity by providing real-time failover, load balancing for queries, and disaster recovery capabilities.

Tuesday, 12 August 2025

Introduction to Oracle Fusion General Ledger (GL)

Oracle Fusion General Ledger is the core financial component of the Oracle Fusion Cloud Financials suite. It delivers a modern, highly configurable, and scalable accounting system designed to meet global business needs. Whether you’re part of a multinational enterprise or a growing organization, Oracle Fusion GL provides the foundation for accurate, real-time financial reporting and decision-making.

Key Features of Oracle Fusion General Ledger

1. Multi-Dimensional Accounting

Oracle Fusion GL introduces a multi-dimensional chart of accounts using segments. Each transaction is recorded with rich context, such as:

Company
Cost Center
Account
Product
Location

This allows flexible and powerful financial analysis.

2. Ledgers and Ledger Sets

Fusion GL supports:

Primary Ledgers for actual accounting
Secondary Ledgers for statutory or management reporting
Ledger Sets for consolidating reporting across multiple ledgers in real-time

3. Accounting Periods and Calendars

You can define:

Custom accounting calendars
Multiple period types (monthly, quarterly, yearly)
Automatic open/close period controls to maintain financial discipline.

4. Journal Processing

Manual and automated journal entries
Journal approval workflows
Reversals, recurring journals, and allocations
Spreadsheet integration for bulk data upload (via ADFdi or FBDI)

5. Real-Time Reporting and Analytics

Fusion GL uses Oracle OTBI and Smart View for Excel for real-time, self-service financial reporting, eliminating delays associated with batch processing.

Benefits of Using Oracle Fusion GL

Global compliance: Handles multiple currencies, languages, and statutory requirements.
Modern UX: Web-based UI with role-based dashboards and guided navigation.
Unified data model: Ensures consistency across finance modules (Payables, Receivables, Assets, etc.).
Audit-ready: Embedded controls and audit trail for every transaction.
Scalable: Supports both centralized and decentralized operations.

Common Use Cases

Closing accounting periods and running trial balances
Generating P&L and balance sheet reports
Performing allocations and consolidations across business units
Setting up intercompany accounting and revaluations
Integrating with EPM tools for planning and forecasting

Who Uses Fusion GL?

Finance Managers: For monitoring KPIs and approving journal entries
Accountants: For daily ledger maintenance and closing books
Auditors: For verifying transactional integrity and controls
IT Teams: For integrating third-party systems and managing roles

Author: Bidhan Mandal
Oracle Fusion Cloud | Financials Expert | EBS & Cloud Migration Specialist
Visit: https://bidhandba.blogspot.com

Friday, 8 August 2025

Configure Oracle Database Vault Realms

Configure Oracle Database Vault Realms to Secure the HR Schema

Oracle Database Vault (DV) is a powerful security feature that enables fine-grained access control by enforcing security policies that protect sensitive data.
One of the most important components in Database Vault is the Realm, which creates a security boundary around database objects to prevent unauthorized access — even by highly privileged users.

In this blog, we’ll walk through the step-by-step process of configuring a Realm to secure the HR schema in an Oracle Database 19c environment.
You’ll learn how to:

Create a Realm
Add objects to it
Restrict access
Enable auditing for security tracking

What is a Realm?

A Realm in Oracle Database Vault defines a logical security boundary around one or more database objects. Once a Realm is in place, no one — not even privileged users like DBAs — can access the protected objects without being explicitly authorized.

Realms are ideal for:

Securing sensitive application data (e.g., HR, Finance)
Enforcing separation of duties in compliance-driven environments
Preventing data breaches caused by insider threats

Step-by-Step Guide to Configuring a Realm

Step 1: Select Data from the HR Schema (Before Realm Setup)

First, try selecting data from the HR schema to see the current level of access.

sql
SELECT * FROM hr.employees;

At this point, access is typically allowed without restriction.

Step 2: Connect as the DV Owner

To create and manage Realms, you need to log in as a Database Vault Owner — often a common user like c##dv_owner_root.

bash
sqlplus c##dv_owner_root@pdb1

This user has privileges to create Realms and manage Database Vault policies.

Step 3: Create the Realm for the HR Schema

Create a Realm to secure all objects in the HR schema.

sql
BEGIN
  DBMS_MACADM.CREATE_REALM(
    realm_name   => 'HR_REALM',
    description  => 'Realm for HR Schema',
    enabled      => DBMS_MACUTL.G_YES,
    audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL +
                     DBMS_MACUTL.G_REALM_AUDIT_SUCCESS
  );
END;
/

Audit Options:
Here, we enable auditing for both failed and successful access attempts. This allows us to track who tried to access the protected data.

Step 4: Add Objects to the Realm

Add all HR schema objects to the Realm. The % wildcard ensures all object names and types are included.

sql
BEGIN
  DBMS_MACADM.ADD_OBJECT_TO_REALM(
    realm_name   => 'HR_REALM',
    object_owner => 'HR',
    object_name  => '%',
    object_type  => '%'
  );
END;
/

Now, all HR schema objects are protected under HR_REALM.

Step 5: Test Realm Enforcement

Try accessing the HR schema again:

sql
SELECT * FROM hr.employees;

If your user or role is not authorized to access HR_REALM, you will receive an access violation error, even if you are a DBA.

Summary of Steps

Step	Action	Result
1	Select data from HR schema	Access allowed
2	Connect as `c##dv_owner_root`	Success
3	Create a Realm on the HR schema	`HR_REALM` created
4	Add objects to the Realm	All HR schema objects protected
5	Select data from HR schema again	Access denied (if unauthorized)

Best Practices for Realms

Enable Auditing – Always enable auditing for Realms to detect unauthorized access attempts.
Granular Protection – Protect only critical objects if full schema protection is unnecessary.
Role-Based Access – Grant Realm access via roles to simplify management.

References

Oracle Database Vault Realms Documentation

Why Use Realms?

Enforce fine-grained access control
Protect sensitive data from privileged users
Achieve compliance with industry regulations
Prevent unauthorized insider access

Realms are a cornerstone of a Zero Trust approach to database security. If you’re working in a regulated or security-conscious environment, they are essential.

Author: Bidhan Mandal
Oracle Apps DBA | Oracle Database Vault Specialist | OCI Architect
Visit: https://bidhandba.blogspot.com

Thursday, 7 August 2025

Configure Oracle Database Vault on Oracle CDB$ROOT

Oracle Database Vault (DV) strengthens the security posture of the Oracle database by enforcing separation of duties and restricting access, even for highly privileged users. While DV is often configured at the PDB level, enterprise environments typically require securing the CDB$ROOT itself to protect the entire multitenant architecture.

This blog walks through the complete process of enabling and verifying Oracle Database Vault on CDB$ROOT in an Oracle 19c environment.

Step 1: Verify DV and OLS Status

Before starting, check if Database Vault and Oracle Label Security (OLS) are installed and verify their status.

sql
COL DESCRIPTION FORMAT A40
SET LINES 900

SELECT * FROM SYS.DBA_DV_STATUS;
SELECT * FROM DBA_OLS_STATUS;

If the components are not installed, install them using DBCA or the Oracle-provided scripts.

Step 2: Create DV Owner and Account Manager Users

You must create common users to manage Database Vault. These accounts should be container-wide.

sql
GRANT CREATE SESSION, SET CONTAINER 
TO c##dv_owner_root IDENTIFIED BY ORacle1234## CONTAINER = ALL;

GRANT CREATE SESSION, SET CONTAINER 
TO c##dv_acctmgr_root IDENTIFIED BY ORacle1234## CONTAINER = ALL;

These will be designated later as the DV Owner and DV Account Manager.

Step 3: Configure Database Vault

Connect as a CDB-level SYSDBA and configure DV.

sql
BEGIN
  CONFIGURE_DV (
    dvowner_uname        => 'c##dv_owner_root',
    dvacctmgr_uname      => 'c##dv_acctmgr_root',
    force_local_dvowner  => FALSE
  );
END;
/

Alternatively:

sql
EXEC CONFIGURE_DV('c##dv_owner_root','c##dv_acctmgr_root');

Step 4: Recompile Invalid Objects

After configuration, recompile invalid objects to ensure all DV packages are valid.

sql
@?/rdbms/admin/utlrp.sql

Step 5: Enable Database Vault

Connect as the DV Owner and enable DV enforcement.

sql
CONNECT c##dv_owner_root@CDB$ROOT
EXEC DBMS_MACADM.ENABLE_DV;

This activates DV, enforcing realms and command rules at the CDB level.

Step 6: Restart the Container Database

Restart the database to finalize configuration.

sql
CONNECT / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP

Step 7: Verify Status After Restart

After restart, confirm DV and OLS are enabled.

sql
SELECT * FROM SYS.DBA_DV_STATUS;
SELECT * FROM DBA_OLS_STATUS;

Both should now show ENABLED.

Conclusion

Configuring Database Vault at the CDB$ROOT level allows DBAs to:

Enforce separation of duties
Protect sensitive metadata
Restrict unauthorized access across all PDBs

This setup is essential in regulated environments where security and compliance are top priorities.

Continue strengthening your security posture by defining realms, command rules, and authorized accounts to match your organization’s requirements.

Author: Bidhan Mandal
Oracle Apps DBA | Oracle Database Vault Specialist | OCI Architect
Visit: https://bidhandba.blogspot.com

Configure Oracle Database Vault for a Pluggable Database (PDB1)

Oracle Database Vault (DV) adds a critical layer of security to Oracle Databases by enabling strong access control and separation of duties. When working in a multitenant environment, it's essential to configure DV specifically for each Pluggable Database (PDB) where protection is required.

This guide outlines the complete steps to enable and verify Database Vault in PDB1, including the assignment of privileged users and schema verification.

Step 1: Connect to the Target PDB

Start by switching your session to the target pluggable database where DV needs to be enabled.

sql
ALTER SESSION SET CONTAINER=pdb1;

Step 2: Verify DV and OLS Installation

Ensure that Database Vault (DV) and Oracle Label Security (OLS) are installed and available in the PDB.

sql
SELECT * FROM SYS.DBA_DV_STATUS;
SELECT * FROM DBA_OLS_STATUS;

These views should return status information such as ENABLED, DISABLED, or NOT CONFIGURED.

Step 3: Load Sample Schema (HR)

If the HR schema is not yet available in the PDB, it can be created using the official Oracle script:

sql
@?/demo/schema/human_resources/hr_main.sql

Once the schema is created, validate access to sample data:

sql
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM hr.employees;

Step 4: Configure Database Vault

Use the built-in CONFIGURE_DV procedure to initialize Database Vault by assigning the DV Owner and DV Account Manager roles.

sql
BEGIN
  CONFIGURE_DV (
    dvowner_uname    => 'c##dv_owner_root',
    dvacctmgr_uname  => 'c##dv_acctmgr_root'
  );
END;
/

This step sets up the security foundation by defining which users will manage DV operations.

Step 5: Enable Database Vault

Connect as the DV Owner user to enable Database Vault enforcement within the PDB.

sql
CONNECT c##dv_owner_root@pdb1

Then execute the enablement procedure:

sql
EXEC DBMS_MACADM.ENABLE_DV;

This activates DV controls and enforces realm protection and command rules.

Step 6: Restart the PDB

To finalize DV configuration, restart the PDB.

sql
ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN;

Step 7: Confirm Configuration

After restarting, confirm that DV and OLS are enabled by rechecking their status:

sql
SELECT * FROM SYS.DBA_DV_STATUS;
SELECT * FROM DBA_OLS_STATUS;

You should now see the status as ENABLED, confirming that Database Vault is active in PDB1.

Conclusion

With Database Vault configured on a PDB, Oracle enforces separation of duties, limits administrative access, and safeguards application data against unauthorized actions—even from powerful users. This configuration is highly recommended for databases requiring strict compliance and security assurance.

For additional control, you can proceed to define realms, command rules, and authorized accounts as per your organizational policies.

Written by Bidhan Mandal
Oracle Apps DBA | EBS Tech Stack Expert | Security & OCI Specialist
Visit: https://bidhandba.blogspot.com

Creating a User in a Database Vault-Protected Oracle PDB

Oracle Database Vault (DV) significantly tightens security by restricting privileged users—even DBA roles—from performing certain operations unless explicitly authorized. One such restriction is creating users in a DV-enabled Pluggable Database (PDB). This article demonstrates how to properly create a new user (SCOTT) in such an environment using the DV Account Manager user.

Background

In a standard Oracle environment, a user with DBA or SYSDBA privileges can easily create users. However, once Database Vault is configured and enabled, these privileges are no longer sufficient unless the user is explicitly granted DV-specific roles, like:

DV_OWNER
DV_ACCTMGR

Let’s walk through a real-world scenario where an attempt to create a user fails due to DV restrictions, and how to fix it using the correct privileged user.

Initial Attempt — Access Denied

First, we switch to the target PDB (pdb1) and try to create the SCOTT user:

sql
ALTER SESSION SET CONTAINER=pdb1;

sql
CREATE USER scott IDENTIFIED BY tiger;

Result:

text
ORA-01031: insufficient privileges

Despite having typical DBA-level access, the operation is blocked—this is Database Vault in action.

Use the Right Role: `DV_ACCTMGR`

To proceed, we must connect using the user who was assigned the DV Account Manager role during Database Vault setup. This account is typically named like c##dv_acctmgr_root.

bash
sqlplus c##dv_acctmgr_root@pdb1

Now, retry the CREATE USER command with a strong password:

sql
CREATE USER scott IDENTIFIED BY ORacle1234##;

Result:

text
User created.

Success! The scott user is now created within the DV-protected PDB.

Why This Matters

Oracle DV enforces separation of duties. Even if you're a DBA, you're not permitted to perform user management or access sensitive data unless your account is assigned specific DV roles. This architecture prevents accidental or malicious privilege misuse and satisfies compliance requirements like GDPR, SOX, and HIPAA.

Bonus: Granting Privileges to the New User

After creation, you can grant necessary roles or privileges to the new user:

sql
GRANT CONNECT, RESOURCE TO scott;
ALTER USER scott QUOTA UNLIMITED ON USERS;

Make sure these actions are also performed by the DV Account Manager or as per the defined security policy.

Summary

Creating users in a DV-enabled Oracle environment requires proper role management. Traditional SYS or DBA accounts can't perform this task unless they're granted DV-specific roles. In this post, we demonstrated:

The error encountered when a non-DV-authorized user attempts to create a user.
The correct process using the c##dv_acctmgr_root user.
Why this restriction is a key part of Oracle's defense-in-depth strategy.

Author: Bidhan Mandal
Oracle EBS | Autonomous Database | Security Expert
More blogs at: https://bidhandba.blogspot.com

Wednesday, 27 August 2025

Database Credentials in Oracle Wallet

Overview

Step 1: Create Oracle Wallet Directory

Step 2: Create TNS ADMIN Directory (for bidhan)

Step 3: Add TNS Entry (for oracle)

Step 4: Export Oracle Variables

Step 5: Create Password-Protected Oracle Wallet

Step 6: Add Database Credentials to Wallet

Step 7: Verify Database Connection

Step 8: List Credentials in Wallet

Step 9: Modify Credentials in Wallet

Step 10: View Wallet Contents

Step 11: Change Wallet Password

Step 12: Delete Credentials from Wallet

Step 13: Delete Oracle Wallet

Key Takeaways

Sunday, 24 August 2025

Oracle 19c Active Data Guard Switchover Procedure

1. Pre-Checks

2. Verify Log Transport and Apply

3. Prepare Standby (Optional)

4. Switchover Command (Primary)

5. Activate New Primary (Standby)

6. Convert Former Primary to Standby

7. Verify New Roles

8. Post-Checks

Conclusion

Oracle 19c Active Data Guard Configuration on Linux – Step by Step

1. Environment Setup

2. Enable FORCE LOGGING & ARCHIVELOG Mode on Primary

3. Configure Primary Initialization Parameters

4. Configure tnsnames.ora on Both Servers

5. Create Standby Redo Logs on Primary

6. Create Password File & Transfer to Standby

7. Take RMAN Backup & Transfer to Standby

8. Restore Database on Standby

9. Configure Standby Initialization Parameters

10. Start Managed Recovery

11. Verify Configuration

Conclusion

Tuesday, 12 August 2025

Introduction to Oracle Fusion General Ledger (GL)

Key Features of Oracle Fusion General Ledger

1. Multi-Dimensional Accounting

2. Ledgers and Ledger Sets

3. Accounting Periods and Calendars

4. Journal Processing

5. Real-Time Reporting and Analytics

Benefits of Using Oracle Fusion GL

Common Use Cases

Who Uses Fusion GL?

Friday, 8 August 2025

Configure Oracle Database Vault Realms to Secure the HR Schema

What is a Realm?

Step-by-Step Guide to Configuring a Realm

Step 1: Select Data from the HR Schema (Before Realm Setup)

Step 2: Connect as the DV Owner

Step 3: Create the Realm for the HR Schema

Step 4: Add Objects to the Realm

Step 5: Test Realm Enforcement

Summary of Steps

Best Practices for Realms

References

Why Use Realms?

Thursday, 7 August 2025

Configure Oracle Database Vault on Oracle CDB$ROOT

Step 1: Verify DV and OLS Status

Step 2: Create DV Owner and Account Manager Users

Step 3: Configure Database Vault

Step 4: Recompile Invalid Objects

Step 5: Enable Database Vault

Step 6: Restart the Container Database

Step 7: Verify Status After Restart

Conclusion

Configure Oracle Database Vault for a Pluggable Database (PDB1)

Step 1: Connect to the Target PDB

Step 2: Verify DV and OLS Installation

Step 3: Load Sample Schema (HR)

Step 4: Configure Database Vault

Step 2: Create TNS ADMIN Directory (for `bidhan`)

Step 3: Add TNS Entry (for `oracle`)

4. Configure `tnsnames.ora` on Both Servers

Use the Right Role: `DV_ACCTMGR`