Performing Audit Data Cleanup
Scenario 6 – Performing Audit Data Cleanup In this section, we’ll guide you through the process of cleaning up audited data from AUDSYS tables stored in the SYSAUX tablespace. Manual Cleanup You can perform the cleanup manually using the following steps: – First, check the count of audited records: SQL> SELECT COUNT(*) FROM unified_audit_trail; – Use the following commands to cleanup audit data: SQL> EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, LAST_ARCHIVE_TIME => SYSDATE); SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, USE_LAST_ARCH_TIMESTAMP => TRUE); Scheduled Cleanup You can also schedule the cleanup for regular execution: Set the last archive timestamp: SQL> EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, LAST_ARCHIVE_TIME => SYSDATE); – Create a purge job: SQL> EXEC DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, AUDIT_TRAIL_PURGE_INTERVAL => 1, AUDIT_TRAIL_PURGE_NAME => ‘Audit_Trail_PJ’, USE_LAST_ARCH_TIMESTAMP => TRUE); Viewing Cleanup Job Executions You can view the executions of the cleanup job: SQL> COL JOB_NAME FORMAT A14 SQL> COL STATUS FORMAT A12 SQL> COL ACTUAL_START_DATE FORMAT A40 SQL> SELECT JOB_NAME, STATUS, ACTUAL_START_DATE FROM dba_scheduler_job_run_details WHERE JOB_NAME = ‘AUDIT_TRAIL_PJ’ ORDER BY ACTUAL_START_DATE; Verifying Cleanup Finally, verify if the audit data has been purged: SQL> SELECT COUNT(*) FROM unified_audit_trail; By following these steps, you can effectively perform audit data cleanup in your Oracle database environment.
Disabling and Deleting Audit Policy
Scenario 5 – Disabling and Deleting Audit Policy In this section, we’ll explore how to disable and delete audit policies in your Oracle database environment without dropping them permanently. 1. Displaying Enabled Audit Policies Start by displaying the list of enabled audit policies: SQL> CONNECT / AS SYSDBA SQL> COL POLICY_NAME FORMAT A20 SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES; 2. Disabling an Audit Policy Next, disable the AUD_MIXED_POL audit policy: SQL> NOAUDIT POLICY aud_mixed_pol; SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES; 3. Dropping an Audit Policy Attempt to drop the DP_POL audit policy. Note that an audit policy can only be dropped after being disabled: SQL> DROP AUDIT POLICY dp_pol; Now, disable the policy and then drop it: SQL> NOAUDIT POLICY dp_pol; SQL> DROP AUDIT POLICY dp_pol; SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES; By following these steps, you can effectively disable and delete audit policies as needed in your Oracle database environment.
Creating Mixed Audit Policy
Scenario 4 – Creating Mixed Audit Policy In this section, we’ll explore how to create a mixed audit policy to monitor users’ activities related to specific roles and actions within your Oracle database environment. 1. Creating a Mixed Audit Policy Start by creating a mixed audit policy that will audit users while using the STORAGE_ROLE role or performing any action related to tables: SQL> CREATE ROLE storage_role; SQL> GRANT DROP TABLESPACE TO storage_role; SQL> GRANT storage_role TO dev; SQL> GRANT DROP ANY TABLE TO jim; SQL> CREATE AUDIT POLICY aud_mixed_pol ACTIONS CREATE TABLE, DROP TABLE, TRUNCATE TABLE ROLES storage_role; 2. Enabling the Audit Policy Next, enable the audit policy: SQL> AUDIT POLICY aud_mixed_pol; 3. Verifying Policy Status Verify that the audit policy is enabled: SQL> COL USER_NAME FORMAT A10 SQL> COL POLICY_NAME FORMAT A14 SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME LIKE ‘%MIXED%’; 4. Performing Operations Execute operations that fall under the scope of the audit policy: SQL> CONNECT dev/oracle_4U SQL> DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES; SQL> CONNECT jim/oracle_4U SQL> DROP TABLE hr.t1 PURGE; 5. Viewing Audit Data Access the resulting audit data to review the audited operations: SQL> CONNECT system/oracle_4U SQL> COL ACTION_NAME FORMAT A16 SQL> COL POLICY_NAME FORMAT A18 SQL> COL ROLE FORMAT A10 SQL> COL SYSTEM_PRIVILEGE_USED FORMAT A20 SQL> SELECT UNIFIED_AUDIT_POLICIES, DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED FROM unified_audit_trail WHERE DBUSERNAME IN (‘JIM’,’DEV’) AND UNIFIED_AUDIT_POLICIES LIKE ‘%AUD_MIXED_POL%’ AND ACTION_NAME NOT IN (‘LOGON’, ‘LOGOFF’); 6. Flushing Audit Trail If necessary, flush the unified audit trail to ensure all audit data is available: SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; 7. Viewing Audit Data Again Once the audit data is flushed to disk, review it again: SQL> CONNECT system/oracle_4U SQL> COL ACTION_NAME FORMAT A16 SQL> COL POLICY_NAME FORMAT A18 SQL> COL ROLE FORMAT A10 SQL> COL SYSTEM_PRIVILEGE_USED FORMAT A20 SQL> SELECT UNIFIED_AUDIT_POLICIES, DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED FROM unified_audit_trail WHERE DBUSERNAME IN (‘JIM’,’DEV’) AND UNIFIED_AUDIT_POLICIES LIKE ‘%AUD_MIXED_POL%’ AND ACTION_NAME NOT IN (‘LOGON’, ‘LOGOFF’); By following these steps, you can effectively create and manage mixed audit policies to monitor users’ activities related to specific roles and actions within your Oracle database environment.
Creating Audit Policies
Scenario 3 – Creating Audit Policies Here we will see how to create different audit policies tailored to your organization’s needs, auditing privileges, actions, and roles under specific conditions. Creating Audit Policies: Part 1 – Privilege Audit Policy In this subsection, we’ll walk through the process of creating a privilege audit policy tailored to your organization’s needs. 1. Creating a Privilege Audit Policy Begin by creating an audit policy to audit the user OE for specific system privileges using the SELECT ANY TABLE or CREATE LIBRARY privileges for each statement executed. Grant the SELECT ANY TABLE privilege to the user OE: SQL> CREATE AUDIT POLICY aud_syspriv_pol PRIVILEGES SELECT ANY TABLE, CREATE LIBRARY WHEN SYS_CONTEXT(‘USERENV’,’SESSION_USER’) = ‘OE’ EVALUATE PER STATEMENT; SQL> GRANT SELECT ANY TABLE TO oe; 2. Enabling the Audit Policy Once the audit policy is created, enable it: SQL> AUDIT POLICY aud_syspriv_pol; 3. Viewing Audit Policy Options View the audit policy options to ensure they are correctly set: SQL> COL AUDIT_OPTION FORMAT A20 SQL> COL POLICY_NAME FORMAT A18 SQL> SELECT POLICY_NAME, AUDIT_OPTION, CONDITION_EVAL_OPT FROM AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME = ‘AUD_SYSPRIV_POL’; 4. Verifying Policy Status Confirm that the audit policy is enabled: SQL> COL USER_NAME FORMAT A10 SQL> COL POLICY_NAME FORMAT A18 SQL> SELECT POLICY_NAME, ENABLED_OPT, USER_NAME, SUCCESS, FAILURE FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME = ‘AUD_SYSPRIV_POL’; 5. Performing an Audited Operation Execute audited operations using the user accounts: SQL> CONNECT hr/oracle_4U SQL> SELECT * FROM sh.sales; SQL> CONNECT oe/oracle_4U SQL> SELECT * FROM hr.employees; 6. Viewing Audit Data Access the resulting audit data to review the audited operations: SQL> CONNECT system/oracle_4U SQL> COL ACTION_NAME FORMAT A16 SQL> COL POLICY_NAME FORMAT A18 SQL> COL SYSTEM_PRIVILEGE_USED FORMAT A20 SQL> SELECT DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED FROM unified_audit_trail WHERE DBUSERNAME IN (‘HR’,’OE’); 7. Flushing Audit Trail If necessary, flush the unified audit trail to ensure all audit data is available: SQL> CONN / AS SYSDBA SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; 8. Viewing Audit Data Again Once the audit data is flushed to disk, review it again: SQL> SELECT DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED FROM unified_audit_trail WHERE DBUSERNAME IN (‘HR’,’OE’); By following these steps, you can effectively create and manage privilege audit policies tailored to your organization’s requirements. Creating Audit Policies: Part 2 – Action Audit Policy In this subsection, we’ll guide you through the process of creating an action audit policy to monitor specific user actions within your database environment. 1. Creating an Action Audit Policy Start by creating an audit policy to monitor select, update, and delete operations on the HR.EMPLOYEES table: SQL> CREATE AUDIT POLICY aud_action_pol ACTIONS SELECT, UPDATE, DELETE ON hr.employees; 2. Enabling the Audit Policy Next, enable the audit policy for all users except OE: SQL> AUDIT POLICY aud_action_pol EXCEPT oe; 3. Viewing Audit Policy Options Verify the audit policy options to ensure they are correctly configured: SQL> COL AUDIT_OPTION FORMAT A20 SQL> COL POLICY_NAME FORMAT A18 SQL> COL OBJECT_NAME FORMAT A18 SQL> SELECT POLICY_NAME, AUDIT_OPTION, OBJECT_NAME FROM AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME = ‘AUD_ACTION_POL’; 4. Verifying Policy Status Confirm that the audit policy is enabled: SQL> COL USER_NAME FORMAT A10 SQL> COL POLICY_NAME FORMAT A18 SQL> SELECT POLICY_NAME, ENABLED_OPT, USER_NAME, SUCCESS, FAILURE FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME = ‘AUD_ACTION_POL’; 5. Performing an Audited Operation Execute audited operations by creating a new user (DEV) and granting appropriate privileges: SQL> CREATE USER DEV IDENTIFIED BY oracle_4U; SQL> GRANT CREATE SESSION TO DEV; SQL> CONNECT hr/oracle_4U SQL> GRANT DELETE ON hr.employees TO DEV; SQL> CONNECT dev/oracle_4U SQL> DELETE FROM hr.employees; SQL> CONNECT oe/oracle_4U SQL> SELECT COUNT(*) FROM hr.employees; 6. Viewing Audit Data Access the resulting audit data to review the audited operations: SQL> CONNECT system/oracle_4U SQL> SET PAGES 100 SQL> COL DBUSERNAME FORMAT A8 SQL> COL ACTION_NAME FORMAT A8 SQL> COL “DATE” FORMAT A20 SQL> COL SYSTEM_PRIVILEGE_USED FORMAT A18 SQL> COL UNIFIED_AUDIT_POLICIES FORMAT A22 SQL> SELECT UNIFIED_AUDIT_POLICIES, DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED, TO_CHAR(EVENT_TIMESTAMP,’DD-MON-YY HH:MI’) “DATE” FROM unified_audit_trail WHERE DBUSERNAME IN (‘DEV’,’OE’) AND ACTION_NAME NOT IN (‘LOGON’, ‘LOGOFF’) ORDER BY 4; 7. Flushing Audit Trail If necessary, flush the unified audit trail to ensure all audit data is available: SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; 8. Viewing Audit Data Again Once the audit data is flushed to disk, review it again: SQL> SELECT UNIFIED_AUDIT_POLICIES, DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED, TO_CHAR(EVENT_TIMESTAMP,’DD-MON-YY HH:MI’) “DATE” FROM unified_audit_trail WHERE DBUSERNAME IN (‘DEV’,’OE’) AND ACTION_NAME NOT IN (‘LOGON’, ‘LOGOFF’) ORDER BY 4; By following these steps, you can effectively create and manage action audit policies to monitor specific user actions within your Oracle database environment. Creating Audit Policies: Part 3 – Role Audit Policy In this subsection, we’ll guide you through the process of creating role audit policies to monitor users’ usage of specific roles within your Oracle database environment. 1. Creating a Role Audit Policy Start by creating an audit policy to monitor all users using the MGR_ROLE role: SQL> CREATE USER JIM IDENTIFIED BY oracle_4U; SQL> CREATE ROLE MGR_ROLE; SQL> GRANT CREATE TABLESPACE TO MGR_ROLE; SQL> GRANT MGR_ROLE, CREATE SESSION TO JIM; SQL> CREATE AUDIT POLICY aud_role_pol ROLES mgr_role; 2. Creating another Role Audit Policy Create an audit policy to monitor all users as soon as they use the DBA role. Create a DBA_JUNIOR user granted the DBA role: SQL> CREATE USER DBA_JUNIOR IDENTIFIED BY oracle_4U; SQL> GRANT DBA TO DBA_JUNIOR; SQL> CREATE AUDIT POLICY aud_dba_pol ROLES dba; 3. Enabling the Audit Policies Enable the audit policies whenever the execution is completed successfully only: SQL> AUDIT POLICY AUD_ROLE_POL WHENEVER SUCCESSFUL; SQL> AUDIT POLICY AUD_DBA_POL WHENEVER SUCCESSFUL; 4. Viewing Audit Policy Options Verify the audit policy options to ensure they are correctly configured: SQL> COL AUDIT_OPTION FORMAT A20 SQL> COL POLICY_NAME FORMAT A18 SQL> SELECT POLICY_NAME, AUDIT_OPTION, CONDITION_EVAL_OPT FROM AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME IN (‘AUD_ROLE_POL’,’AUD_DBA_POL’); 5. Verifying Policy Status Confirm that the audit policies are enabled: SQL> COL USER_NAME FORMAT A10 SQL> COL POLICY_NAME FORMAT A18 SQL> SELECT POLICY_NAME, ENABLED_OPT, USER_NAME, SUCCESS, FAILURE FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME IN (‘AUD_ROLE_POL’,’AUD_DBA_POL’); 6. Performing Audited Operations Execute audited operations for both role type audited policies: SQL> CONNECT JIM/oracle_4U SQL> CREATE TABLESPACE test DATAFILE ‘/tmp/test01.dbf’ SIZE 10M; SQL> CONNECT DBA_JUNIOR/oracle_4U
Auditing RMAN and Oracle Data Pump Operations
Scenario 2 – Auditing RMAN and Oracle Data Pump Operations In this scenario, we’ll explore how to audit Oracle Data Pump and Recovery Manager (RMAN) operations within your Oracle database environment. Oracle Data Pump Auditing 1. Create Data Pump Audit Policy: Begin by creating an audit policy specifically for Oracle Data Pump export operations: SQL> CREATE AUDIT POLICY DP_POL ACTIONS COMPONENT=datapump EXPORT; 2. Enable Audit Policy: Once the audit policy is created, enable it: SQL> AUDIT POLICY DP_POL; 3. Verify Policy Status: Confirm that the audit policy is enabled: SQL> COL USER_NAME FORMAT A10 SQL> COL POLICY_NAME FORMAT A10 SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME LIKE ‘%DP%’; 4. Perform Export Operation: Execute an export operation using Oracle Data Pump. This step is typically performed at the operating system level. $ rm /u01/app/oracle/admin/orcl/dpdump/HR_tables.dmp $ expdp system/oracle_4U dumpfile=HR_tables tables=HR.EMPLOYEES 4. View Audit Data: After performing the export operation, access the resulting audit data: SQL> SET PAGES 100 SQL> SELECT DBUSERNAME, DP_TEXT_PARAMETERS1, DP_BOOLEAN_PARAMETERS1 FROM UNIFIED_AUDIT_TRAIL WHERE DP_TEXT_PARAMETERS1 IS NOT NULL; 5. Flush Audit Trail: If the audited data is still in memory, flush the unified audit trail to disk: SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; 6. View Audit Data Again: Once the audit data is flushed to disk, view it again: SQL> SELECT DBUSERNAME, DP_TEXT_PARAMETERS1, DP_BOOLEAN_PARAMETERS1 FROM UNIFIED_AUDIT_TRAIL WHERE DP_TEXT_PARAMETERS1 IS NOT NULL; RMAN Auditing For RMAN backup, restore, and recover operations are audited by default. You don’t need to create a separate audit policy. 1. Perform Backup Operation: Execute a backup operation using RMAN: $ rman target / RMAN> BACKUP TABLESPACE USERS; RMAN> EXIT; 3. Restore and Recover Operation: Remove the USERS tablespace datafile, then restore and recover it using RMAN: $ sqlplus / as sysdba SQL> ALTER TABLESPACE USERS OFFLINE IMMEDIATE; SQL> EXIT; $ rman target / RMAN> RESTORE TABLESPACE USERS; RMAN> RECOVER TABLESPACE USERS; RMAN> EXIT; 4. View Audit Data: Access the resulting audit data for RMAN operations: $ sqlplus / as sysdba SQL> ALTER TABLESPACE USERS ONLINE; SQL> SELECT DBUSERNAME, RMAN_OPERATION FROM UNIFIED_AUDIT_TRAIL WHERE RMAN_OPERATION IS NOT NULL; 5. Flush Audit Trail: If necessary, flush the unified audit trail to disk: SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; 6. View Audit Data Again: Once the audit data is flushed to disk, view it again: SQL> SELECT DBUSERNAME, RMAN_OPERATION FROM UNIFIED_AUDIT_TRAIL WHERE RMAN_OPERATION IS NOT NULL; By following these steps, you can effectively audit Oracle Data Pump and RMAN operations within your Oracle database environment.
Oracle Unified Auditing: Using Mix Mode
[et_pb_section fb_built=”1″ _builder_version=”4.24.2″ _module_preset=”default” custom_padding=”11px|||||” global_colors_info=”{}”][et_pb_row _builder_version=”4.24.2″ _module_preset=”default” global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”4.24.2″ _module_preset=”default” global_colors_info=”{}”][et_pb_text _builder_version=”4.24.2″ _module_preset=”default” global_colors_info=”{}”] In this scenario, we’ll explore how to leverage mix mode auditing in Oracle Unified Auditing. Follow along as we guide you through the process step by step. 1. Verifying Unified Auditing First, let’s verify that unified auditing is not enabled by default. Open your SQL*Plus session as a privileged user: sqlplus / as sysdba Then, execute the following SQL query to check the status of unified auditing: SQL> SELECT parameter, value FROM v$option WHERE parameter = ‘Unified Auditing’; If the output displays a ‘FALSE’ value, it indicates that unified auditing is available but not enabled by default. In this scenario, ‘FALSE’ value will not make any problem. 2. Checking Predefined Audit Policy Next, let’s check the existence of the predefined ORA_SECURECONFIG audit policy SQL> SELECT policy_name, audit_option FROM audit_unified_policiesWHERE policy_name = ‘ORA_SECURECONFIG’ ORDER BY audit_option; This output will confirm whether the ORA_SECURECONFIG audit policy exists in your database. 3. Verifying Policy Status Now, verify that the predefined ORA_SECURECONFIG audit policy is enabled by default SQL> SELECT policy_name FROM audit_unified_enabled_policies WHERE policy_name = ‘ORA_SECURECONFIG’; Ensure that the policy is listed, indicating that it’s enabled. 4. Verifying Audit Parameters Finally, let’s verify all audit parameters at the instance level: SQL> SHOW PARAMETER AUDIT; This command will display all audit-related parameters configured for your database instance. By following these steps, you can ensure that Oracle Unified Auditing is configured and available for use in mix mode within your database environment. [/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]
Oracle Unified Auditing: A Comprehensive Guide
[et_pb_section fb_built=”1″ _builder_version=”4.19.4″ _module_preset=”default” global_colors_info=”{}”][et_pb_row _builder_version=”4.19.4″ _module_preset=”default” global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”4.19.4″ _module_preset=”default” global_colors_info=”{}”][et_pb_text _builder_version=”4.24.2″ _module_preset=”default” global_colors_info=”{}”] Welcome to our tutorial series on Oracle Unified Auditing! In this first installment, we’ll delve into the fundamentals of implementing this powerful feature, designed to streamline and centralize the auditing process for all RDBMS operations. INTRODUCTION Oracle Unified Auditing, introduced in version 12c, revolutionizes the auditing landscape by consolidating audit trails into a single unified audit trail table. This enhancement simplifies audit management and offers a comprehensive view of all activities within the database. Prerequisites Before diving into the implementation of Oracle Unified Auditing, it’s essential to ensure that you have the necessary prerequisites in place. Here’s what you’ll need to get started: 1. Oracle Database 12c or Above Ensure that you have Oracle Database version 12c or above installed on your system. If you haven’t already installed it, you can download and install the latest version from the official Oracle website. 2. Database Startup Before initiating any auditing tasks, ensure that your Oracle database is up and running. You can start the database using the appropriate commands or tools provided by Oracle Database. With these prerequisites in place, you’ll be ready to embark on the journey of implementing Oracle Unified Auditing seamlessly. SCENARIO Throughout this tutorial series, we’ll walk you through a hands-on scenario, illustrating how to leverage Oracle Unified Auditing effectively. Here’s what you can expect to learn. Click on the following topics to explore further details about each scenario. Mixed Auditing Mode: Explore the flexibility of mixed auditing mode, allowing you to combine traditional and unified auditing approaches. Auditing Data Pump and RMAN Operations: Gain insights into auditing critical RMAN and Data Pump operations such as expdp/impdp, backup, restore, and recover, ensuring the integrity and security of your data. Creating Audit Policies: Learn to craft audit policies tailored to your organization’s needs, auditing privileges, actions, and roles under specific conditions. Utilizing Data Dictionary Views: Discover how to utilize data dictionary views to effortlessly view audit policies and audited data, empowering you with valuable insights into your database activity. Disabling Audit Policies: Understand the process of disabling audit policies when necessary, providing flexibility while maintaining control over your auditing environment. Cleaning Up Audit Data: Master the art of efficiently cleaning up audit data, optimizing storage resources without compromising compliance requirements. [/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]
FLASHBACK and BLOCK CHANGE TRACKING
[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”4.16″ global_colors_info=”{}”][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text admin_label=”Text” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}”] Enabling Flashback Database and Block Change Tracking in Oracle database: A Step-by-Step Guide Introduction Oracle provides powerful features like Flashback Database and Block Change Tracking, offering database administrators a robust set of tools for data recovery and performance optimization. In this guide, we’ll walk you through the process of enabling these features step by step. Enabling Flashback Database Step 1: Configure Fast Recovery Area Before enabling Flashback Database, ensure your Oracle database is running in ARCHIVELOG mode and has a configured fast recovery area. — Ensure the database is running in ARCHIVELOG mode firstSQL> ALTER DATABASE ARCHIVELOG; — Set the location and size for the fast recovery areaSQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘/path/to/fast_recovery_area’ SCOPE=BOTH; — Set the maximum size for the fast recovery areaSQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH; Make sure to replace ‘/path/to/fast_recovery_area’ with the actual path where you want to store the fast recovery area files. The size specified in the example is 10GB; you can adjust it based on your storage requirements. Step 2: Check Flashback Database Status Determine if Flashback Database is already enabled by executing the following SQL command: SQL> SELECT FLASHBACK_ON FROM V$DATABASE; Step 3: Set Flashback Window (Optional) Optionally, set the length of the flashback window in minutes by adjusting the DB_FLASHBACK_RETENTION_TARGET parameter. For instance, to specify a 15-day window: SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=21600 scope=both; Step 4: Enable Flashback Database Enable Flashback Database for the entire database with the following SQL command: SQL> ALTER DATABASE FLASHBACK ON; Step 5: Enable Row Movement for the Table (OPTIONAL) For Flashback Database to work effectively, enable row movement for the relevant tables SQL> ALTER TABLE testing_tbl ENABLE ROW MOVEMENT; NOTE: Step-5 is not mandatory for enabling Flashback Database itself. It is an optional step that may be necessary in certain situations. Enabling row movement allows rows in a table to be moved, which can be useful when performing certain operations with Flashback Database. For example, if you want to perform a Flashback Query on a table and the table has a foreign key relationship with another table, enabling row movement might be required. If your database doesn’t involve operations or features that specifically require row movement, you can choose to skip this step. It is recommended to evaluate your database schema and requirements to determine whether enabling row movement is necessary for your use case. Step 6: Increase UNDO Retention Increase UNDO retention to cover the desired flashback window. The value is defined in seconds. For example, to set it to 15 days: SQL> ALTER SYSTEM SET UNDO_RETENTION = 1296000 scope=both; Enable Block Change Tracking Step 1: Create Directory Create a directory where the block change tracking file will be stored: oracle@OLPODA:/home/oracle> mkdir –p /u01/block_change_tracking_file/ Step 2: Enable Block Change Tracking Connect to the database and execute the following SQL commands: SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/u01/block_change_tracking_file/rman_change_track.f’; Conclusion This completes the process of enabling Flashback Database and Block Change Tracking in your Oracle database. These features enhance data protection and performance tuning capabilities. [/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]
Transparent Database Encryption (TDE)
[et_pb_section admin_label=”section”] [et_pb_row admin_label=”row”] [et_pb_column type=”4_4″][et_pb_text admin_label=”Text”] Securing Your Oracle Database with Transparent Data Encryption (TDE) for CDB and PDB Introduction In the ever-evolving landscape of database security, Oracle 12c offers robust solutions to safeguard your data. Transparent Data Encryption (TDE) is a crucial feature that ensures your data remains confidential, whether at rest or in transit. In this guide, we will walk through the steps to implement TDE for both Container Database (CDB) and Pluggable Database (PDB), ensuring a comprehensive security approach. NOTE: Following steps were performed on Oracle 12c environment but they can be used in newer versions. Step 1: Set Keystore Location To kick off the TDE implementation, the first step is to set the keystore location. Edit the sqlnet.ora file and specify the encryption wallet location. ENCRYPTION_WALLET_LOCATION = (SOURCE =(METHOD = FILE)(METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/ORCL1/encryption_keystore/) Next, create the keystore directory using the specified path. $ mkdir -p /u01/app/oracle/admin/ORCL1/encryption_keystore/ Step 2: Create and Open the Keystore Now, let’s create the keystore and open it for use. SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/u01/app/oracle/admin/ORCL1/encryption_keystore/’ IDENTIFIED BY oracle123; Check the keystore file in the specified location. $ ls /u01/app/oracle/admin/ORCL1/encryption_keystore/ ewallet.p12 Open and close the keystore as needed. SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle123 container=all; SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY oracle123 container=all; Step 3: Take Backup of Keystore Ensure the keystore is open before taking a backup. SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle123 container=all; SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle123 WITH BACKUP CONTAINER=ALL; Step 4: View Key and Wallet Information Query information about the master key using the V$ENCRYPTION_KEYS view and information about the keystore using the V$ENCRYPTION_WALLET view. SQL> SELECT con_id, key_id FROM v$encryption_keys; SQL> SELECT * FROM v$encryption_wallet; Step 5: Implement TDE in Pluggable Database (PDB) Connect to the PDB and create a table with an encrypted column. SQL> conn username@pdb_name SQL> CREATE TABLE tde_test (id NUMBER(10), data VARCHAR2(50) ENCRYPT); Step 6: Create Encrypted Tablespaces Create encrypted tablespaces within the PDB. SQL> create tablespace tde_tbs datafile ‘+DATA/ORCL1/TDE_TBS/DATAFILE/tde_tbs_01.dbf’ size 10m autoextend on next 100m ENCRYPTION USING ‘AES256’ 4 DEFAULT STORAGE(ENCRYPT); Step 7: Query Encrypted Data Insert data into the encrypted table and query it to ensure proper encryption. SQL> INSERT INTO tde_test VALUES (1, ‘Encrypt Data’); SQL> SELECT * FROM tde_test; Conclusion Implementing Transparent Data Encryption in Oracle for CDB and PDB adds an extra layer of security to your database. Following these steps ensures your data remains protected, even in the event of a database restart. Stay ahead in data security by leveraging Oracle’s powerful features. Implementing TDE may seem complex, but the enhanced security it provides is worth the effort. By following this guide, you’ve taken a significant step towards safeguarding your Oracle database.[/et_pb_text][/et_pb_column] [/et_pb_row] [/et_pb_section]
Configure Database Wallet
Configuring Database Wallet on Oracle 12c PDB: A Step-by-Step Guide Note: These steps were performed on Oracle 12c environment, but can be followed in newer releases. Introduction Ensuring the security of sensitive data is a paramount concern for any database administrator. One powerful tool to achieve this in Oracle 12C PDB is the Database Wallet. This guide walks you through the process of configuring and utilizing the Oracle Database Wallet seamlessly. Setup HOSTNAME= iadoracletesting01 PDB_NAME= test_user PDB_USER= test_user_ADMIN WALLET LOCATION= /u01/app/oracle/product/12.1.0.2/db_1/wallet Step 1: Create a Directory for Wallet Files Navigate to your Oracle Home and create a dedicated directory for wallet files. $ cd $ORACLE_HOME $ mkdir -p wallet $ cd wallet Step 2: Set SQLNET.ORA Entry on Client Edit the ‘sqlnet.ora’ file to include the wallet location and necessary configurations. WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/product/12.1.0.2/db_1/wallet) ) ) SQLNET.WALLET_OVERRIDE = TRUE SSL_CLIENT_AUTHENTICATION = FALSE SSL_VERSION = 0 Step 3: Create a Wallet to Hold the Credentials Use the orapki tool to create a wallet with a specified location and password. $ orapki wallet create -wallet “/u01/app/oracle/product/12.1.0.2/db_1/wallet” -pwd “oracle123” -auto_login_local Step 4: Create a Credential Associated with a TNS Alias Utilize the mkstore command to create a credential associated with a TNS alias. $ mkstore -wrl “/u01/app/oracle/product/12.1.0.2/db_1/wallet” -createCredential test_user test_user_ADMIN oracle123 Step 5: List External Password Store Verify the credentials stored in the wallet using the mkstore command. $ mkstore -wrl “/u01/app/oracle/product/12.1.0.2/db_1/wallet” -listCredential Step 6: Check Connection Using Wallet Ensure successful connection using SQLPlus. $ sqlplus Enter user-name: /@test_user Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production … Conclusion Congratulations! You’ve successfully configured the Database Wallet on Oracle 12C PDB. This added layer of security ensures that your sensitive data is protected while maintaining convenient access.