Sop

 Standard Operating Procedure (SOP) for Oracle 19c TDE Wallet Validation and Backup Automation


Document Version


Version: 1.4


Date: YYYY-MM-DD


Author: <Your Name>


Reviewed By: <Reviewer Name>


Last Updated: YYYY-MM-DD




---


1. Objective


This SOP provides a structured approach to automate Transparent Data Encryption (TDE) wallet validation and backup for all running Oracle databases (both 12c and 19c) on a given host.


Key Features of the Automation:


Detects all running Oracle databases except ASM and APX.


Determines the database version dynamically (12c or 19c) and executes version-specific queries.


Automatically sources ORACLE_SID using oraenv.


Retrieves wallet passwords securely from Cloakware (if configured).


Validates the TDE wallet status and its location.


Ensures the wallet location contains the corresponding database name.


Performs structured backups of the wallet if it exists.


Checks for encrypted tablespaces and validates wallet password correctness.


Logs outputs systematically for audit and review.


Fully automated—no manual user input required.




---


2. Scope


This procedure applies to all Oracle databases (12c and 19c) running on a given host that have TDE enabled. It is intended for database administrators responsible for encryption wallet management and compliance.



---


3. Prerequisites


3.1 Access & Permissions


Must execute as the oracle user.


Ensure sqlplus, mkstore, and Cloakware utilities are available.


Oracle environment variables should be correctly set.


/etc/oratab must be updated with all database instances.



3.2 Required Software & Tools


Oracle 12c and 19c installed and configured.


sqlplus for database queries.


mkstore for wallet operations.


Cloakware (pwEcho.exe) for secure password retrieval.


Bash shell (bash) for script execution.



3.3 Directory Structure & Naming Conventions



---


4. Execution Steps


Step 1: Prepare the Environment


1. Ensure the Oracle environment is configured correctly:


export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH



2. Confirm that required utilities are available:


which sqlplus mkstore



3. Verify that running databases can be identified:


ps -ef | grep pmon | grep -v grep | grep -Ev "(\+ASM|APX)"





---


Step 2: Execute the TDE Wallet Validation Script


1. Deploy the script on the database server


Ensure it is located in a directory accessible by the Oracle user, e.g., /home/oracle/tde_wallet_validation.sh.




2. Grant execute permissions


chmod +x /home/oracle/tde_wallet_validation.sh



3. Run the script


/home/oracle/tde_wallet_validation.sh





---


Step 3: Script Execution Details


The script follows these key steps:


1. Identify Running Databases


Uses ps -ef | grep pmon to detect running Oracle instances.


Filters out ASM and APX instances.




2. Source ORACLE_SID and Set Environment


Dynamically retrieves the list of databases.


Sources the environment using oraenv without manual input.




3. Determine Oracle Database Version


The script connects to each database and retrieves the version:


SELECT version FROM v$instance;


If the database is 12c, it executes queries specific to 12c.


If the database is 19c, it executes queries specific to 19c.




4. Retrieve Wallet Password Securely


Uses Cloakware (pwEcho.exe) to fetch the correct wallet password.


Falls back to a standard password if Cloakware is unavailable.




5. Validate Wallet Status and Location


Queries V$ENCRYPTION_WALLET for wallet status:


SELECT status, wallet_type, wallet_location FROM v$encryption_wallet;


Extracts wallet location and ensures it contains the database name.


If the wallet location is missing or incorrect, logs an alert.




6. Backup TDE Wallet (if location exists and is correct)


Checks if the wallet directory exists:


if [ -d "$WALLET_LOCATION" ]; then


Verifies that the wallet directory contains the database name:


if [[ "$WALLET_LOCATION" == *"$ORACLE_SID"* ]]; then


Creates a structured backup directory.


Copies wallet files securely:


cp -p $WALLET_LOCATION/* $BACKUP_DIR/$ORACLE_SID/




7. Verify Wallet Password


Tests the wallet password using mkstore.


Compares against both Cloakware and standard passwords.




8. Check for Encrypted Tablespaces


If 12c, use:


SELECT tablespace_name, encrypted FROM dba_tablespaces WHERE encrypted='YES';


If 19c, use:


SELECT tablespace_name, encryption_algorithm FROM dba_tablespaces WHERE encrypted='YES';




9. Generate and Store Audit Logs


Logs all validation results in $LOG_DIR/tde_wallet_validation_YYYYMMDD.log.


Creates a structured report in $LOG_DIR/tde_wallet_report_YYYYMMDD.log.






---


Step 4: Post-Execution Validation



---


6. Conclusion


This automation ensures that all Oracle databases (12c and 19c) are:

✅ Properly confi

gured and secured.

✅ Backed up systematically.

✅ Validated for encryption compliance.

✅ Auditable with detailed logging.


This enhances security by verifying wallet locations, ensuring structured backups, and eliminating manual intervention.


Comments

Post a Comment

Popular posts from this blog

Database growth

#!/bin/bash # Log file LOGFILE="db_growth_check.log" echo "Checking yearly database growth on all running Oracle databases..." > $LOGFILE echo "DATABASE_NAME | YEAR | GROWTH_GB" >> $LOGFILE echo "---------------------------------" >> $LOGFILE # Find running Oracle instances (excluding ASM and APX) ps -ef | grep pmon | grep -v grep | grep -Ev "(\+ASM|APX)" | awk -F'_' '{print $3}' | while read -r ORACLE_SID do     if [[ -n "$ORACLE_SID" ]]; then         echo "Processing database: $ORACLE_SID"         # Set Oracle environment         export ORACLE_SID=$ORACLE_SID         export ORAENV_ASK=NO         . oraenv > /dev/null 2>&1         # Run SQL query to check yearly database growth         SQL_OUTPUT=$(sqlplus -s / as sysdba <<EOF SET HEADING OFF; SET FEEDBACK OFF; SELECT '$ORACLE_SID' AS DATABASE_NAME,  ...
Read more

P2

#!/bin/bash # Ensure ORACLE_SID is passed as an argument if [[ -z "$1" ]]; then     echo "Usage: $0 <ORACLE_SID>"     exit 1 fi export ORACLE_SID=$1 export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / as sysdba" # Standard password (static) STANDARD_PWD="xyxyxyxyxytsts" # Fetch cloakware password dynamically NEWPSWD=$(/path/to/pwEcho.exe $ORACLE_SID WALLET) # Function to execute SQL and get database info get_db_info() {     SQL_FILE="/tmp/sql_output.txt"     $SQLPLUS <<EOF > $SQL_FILE SET HEAD OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 500 TRIMSPOOL ON WHENEVER SQLERROR EXIT SQL.SQLCODE SELECT name FROM v\$database; SELECT status FROM v\$encryption_wallet; SELECT NVL((SELECT WRL_PARAMETER FROM gv\$encryption_wallet WHERE inst_id = (SELECT INSTANCE_NUMBER FROM v\$instance)), 'NA') FROM dual; SELECT CASE WHEN COUNT(*) > 0 THEN 'YES' E...
Read more

SQL Loader V3

Create the Configuration File (config.cfg):Create a file named config.cfg with the following content. Adjust the values to match your environment:[database] username=your_username password=your_password database=your_database [files] csv_file=/path/to/your/file.csv log_file=/path/to/your/log_file.log inserted_rows_file=/path/to/your/inserted_rows.log updated_rows_file=/path/to/your/updated_rows.log ********** #!/bin/bash # Load configuration source config.cfg # Variables CSV_FILE=$csv_file USERNAME=$username PASSWORD=$password DATABASE=$database MASTER_TABLE="master_table" CHILD_TABLE="child_table" LOG_FILE=$log_file INSERTED_ROWS_FILE=$inserted_rows_file UPDATED_ROWS_FILE=$updated_rows_file # Logging function log() {   echo "$(date +'%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE } log "Starting data load process." # SQL Loader Control File for child table CONTROL_FILE="control_file.ctl" # Create control file for child table cat ...
Read more