As we discussed some undo-related aspects in my previous blog post , I’ll continue the conversation here with more details about undo tablespaces and the common challenges you might encounter with them in Oracle 19c. Understanding and optimizing undo management is critical to ensuring data consistency, supporting recovery operations, and maintaining peak database performance.
Sharing knowledge is the ultimate key to gaining knowledge..
The only two things that stay with you for life
are you & your knowledge !
Live while you can!
Teach & inspire while you could &
Smile while you have the teeth.. 😉
-
ASM
- Creating ASM Instance and diskgroups manually without DBCA
- 20 ASM Realtime | Interview Questions
- How to make a cold backup for database running on ASM ( using RMAN )
- Moving a Single Datafile from File System to ASM System (METHOD 1)
- Moving a Single Datafile from File System to ASM System (METHOD 2)
- Things to consider while adding new disk/ Rebalancing existing Disk Group
-
Dataguard
- Dataguard Modes
- Dataguard Modes - II
- Oracle DataGuard Setup
- invalid archived log deletion policy
- recover from the Loss of a Datafile on a Standby Database ?
- MRP terminated with ORA-00600: internal error code, arguments
- Recover archive gaps in standby database - using 2 methodss
- Gap Resolution in Dataguard
- Convert Physical Standby to Snapshot Standby and Vice Versa
- Re-create Redo-logs on Standby/ Primary
- Pre-check for DataGuard switchover Process
- ORA-19815: WARNING: db_recovery_file_dest_size
- How do you purge old archivelogs which are applied on Standby DB
- Pre-check For SWITCHOVER using DG Broker
- https://oracle-dba-help.blogspot.com/2016/03/data-guard-modes.html
- ORA-01274 : RENAME THE UNKNOW FILE IN THE STANDBY DATABASE
-
Core DBA
- Database Creation in Easy 6 Steps
- Oracle Networking
- Tablespace management
- Housekeep : Quick Tips for DBA
- Health check for DB
- ORA-12537 While Connecting To Database Via Listener
- How to identify the applied patche-set of your Oracle Home
- Accessing a schema without knowing the password
- ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
- Session Related Scripts
- How to check ORA Error on DB Server – Beginner Tip
- Working with Redo Logs
- Decommission of Oracle Database
- Roles in Oracle - Managing Users
- Oracle Release Number Format
- Troubleshooting Handy-Guide
- Understanding the Upgradation Process
- Query to find the session creating more redo
- Audit in Oracle
- Troubleshooting Internal Errors and Error-Look-up Tool on MOS
- Troubleshooting Issues with Undo Tablespace
- User Management
- User Management - II
- Data Pump Gets Better
- MEMORY_TARGET Parameter
-
Must Read
- Oracle 21c: INCLUDE and EXCLUDE in Data Pump
- Behind the Scenes of COMMIT
- Data Guard Redo Transport Encryption
- Oracle 19C with ADG DML Redirection
- 19c PDB Refreshable Clones
- Solving the Mysterious Database Connection Timeouts
- How to identify the applied patche-set of your Oracle Home
- Topic need to get clear to become expert in Oracle DBA
- Working with Redo Logs
- Oracle Database Block corruption
- What happens during Instance Recovery
- Oracle Datapump Scenarios
- Troubleshooting Issues with Undo Tablespace
- General Tips to Prepare for an Oracle DBA Job Interview
- RMAN Questions
- How To..
-
Postgres Admin
- Installing PostgreSQL Server on CentOS, RHEL 6 / 7 Systems
- Creating a database in Postgres
- Initialize multiple Postgres instance on the same RHEL server
- How to stop starting of clusters while starting up services
- Setup a streaming replication
- Switchover- master server and warm standby (with streaming replication)
- All About Physical Replication and Log shipping
- PostgreSQL Upgrade and troubleshooting
- Timeline Switch issue
- Query to identify the lag in replication
- Drop table : ERROR: must be owner of relation table_name
- PgBadger
- PgCenter
- PgTune
- Internals of Toast Table
- Influencing Query Execution Plan
- Create Extension ERROR
- Find Uptime in PostgreSQL
- Set DB Timezone
- Clear RAM Cache, Buffer
- Read-Only User in Postgres
- Everything you need to know about Streaming Replication
- Ora2Pg Migrate Oracle to Postgres
- Total Page Cost
- PostgreSQL 13 - Everything you need to know
- Startup Failure - .conf file
- All About Postgres Upgrade
- MySQL Admin
-
Oracle Tuning
- Basic Guide
- Session Related Scripts
- AWR :: Beginners Guide
- All About ASH - Active Session History
- Wait Events : checkpoint busy waits or archiver busy waits
- Find sessions performing sort operations : TEMP Tablespace occupants
- Generate ADDM task and generate its report for Tuning
- Database Performance Tuning Scripts -Keep Handy
- Buffer Busy Wait
- Simulating the Deadlock
- Latches
-
RMAN
- Steps to Schedule any RMAN Jobs In Windows Server
- ORA-01194: file 1 needs more recovery to be consistent
- Loss of Current Online Redo-Log
- RMAN "baby Steps"
- Recovery of ControlFiles
- Loss of Datafile
- Recovery from complete loss of all online redo log files
- Block Change Tracking
- RMAN Questions
- Instance Recovery
- Redundancy and Recovery window in RMAN
- Cold Backup
- Database Cloning
- Contact
- Drop Database using RMAN
- PITR to older Incarnation
- GoldenGate
- About Me
Monday, 31 March 2025
Sunday, 16 March 2025
A Deep Dive into Oracle Key Vault Features
In the world of database and application security, managing encryption keys and other sensitive credentials is paramount. Enter Oracle Key Vault (OKV)—a robust solution designed to securely store and manage encryption keys, Oracle Wallets, Java KeyStores, SSH Key pairs, and other critical secrets. Whether deployed in the Oracle Cloud Infrastructure (OCI), Azure, AWS, or on-premises, OKV offers a scalable and fault-tolerant solution for key management across various environments.
Monday, 3 March 2025
Elevate Your Oracle Database Security with Oracle Data Safe
Ensuring robust database security and compliance is a critical concern for organizations, especially when dealing with sensitive data and regulatory requirements. Oracle Data Safe provides a unified control center designed to streamline security management and compliance tasks across various environments—whether on-premises, in Oracle Cloud Infrastructure (OCI), at Cloud@Customer, or on other cloud platforms.
Saturday, 1 March 2025
Building a Kind and Effective Leadership Style: 10 Essential Traits
Monday, 24 February 2025
Guide to Managing Database During Rapid Growth: Key Strategies for Scalability and Efficiency
As your database expands, ensuring peak performance becomes increasingly complex. Whether you’re dealing with a sudden surge in transactions or expanding workloads, proactive measures are essential to maintain efficiency. Below are some key strategies to help you optimize performance and scale smoothly during rapid growth.
Sunday, 16 February 2025
From Setup to Purging: Managing Oracle's Unified Audit Trail with Ease
In the realm of Oracle database management, maintaining an effective audit trail is crucial for ensuring data integrity and security. Oracle's approach to auditing has evolved, with unified audit records now taking center stage. Here’s an in-depth look at how Oracle handles audit trails and how you can manage them effectively.
Understanding Unified Audit Records
Oracle databases generate audit records during or after the execution of audited SQL statements. These records are written to the internal relational table within the AUDSYS schema. Unlike previous releases, where audit records were stored in SecureFile LOBs, the current system utilizes a partitioned table. This partitioned table uses the EVENT_TIMESTAMP as a partition key, with a default partition interval set to one day. If the database version lacks partitioning support, the audit records are stored in a regular, non-partitioned table.
Unified audit records require significantly more disk space—about 50% more—compared to traditional audit records. It’s essential to plan for this increased storage requirement to avoid potential issues.
Real-Time and Historical Audit Policies
Unified audit policies in Oracle take effect immediately when enabled, applying to ongoing user sessions. Conversely, disabling these policies also takes immediate effect. However, changes to existing unified audit policies will only apply to new sessions, not retroactively to sessions that were already in progress.
Handling Audit Trail Failures
In scenarios where the database is unable to write audit records to the database (such as when it is read-only, when the tablespace is full or offline, or if the audit tablespace is offline), Oracle writes audit records to operating system spillover files in .bin format. These spillover files continue to receive audit records until the operating system’s disk space is exhausted. Once the disk is full, user transactions that generate audit records will fail with an ORA-02002 error.
SYSLOG Integration
Oracle provides the AUDIT_SYSLOG_LEVEL parameter to facilitate writing SYS and standard OS audit records to the system audit log using the SYSLOG utility. When AUDIT_SYSLOG_LEVEL is set and SYS auditing is enabled (AUDIT_SYS_OPERATIONS = TRUE), SYS audit records are directed to the system audit log. If standard audit records are being sent to the operating system (AUDIT_TRAIL=os), these records are also written to the system audit log. However, be aware that most SYSLOG configurations have a limitation of 1024 bytes, which means only a subset of key fields can be captured. For a complete record, refer to the UNIFIED_AUDIT_TRAIL.
Purging Unified Audit Trail
Managing disk space and keeping the audit trail efficient involves regular purging of old records. Follow these steps to purge the unified audit trail:
1. Set Last Archive Timestamp:
EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
LAST_ARCHIVE_TIME => <TimeStampValue> + INTERVAL '30' DAY );
Replace <TimeStampValue> with the timestamp of the last archived record.
2. Check Last Archive Timestamp:
SELECT AUDIT_TRAIL, LAST_ARCHIVE_TS
FROM DBA_AUDIT_MGMT_LAST_ARCH_TS
WHERE AUDIT_TRAIL = 'UNIFIED AUDIT TRAIL';
Use the LAST_ARCHIVE_TS value from this query for the new <TimeStampValue> in the next step.
3. Clean the Audit Trail:
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
USE_LAST_ARCH_TIMESTAMP => TRUE );
By understanding and effectively managing these aspects of Oracle’s audit trail, you can ensure that your database remains secure, compliant, and performant. Regular monitoring and maintenance of the audit trail will help prevent potential issues and ensure that your auditing processes run smoothly.
Saturday, 15 February 2025
10 Common Oracle Performance Mistakes and How to Avoid Them
Sunday, 2 February 2025
Oracle TDE Demystified: Safeguarding Sensitive Data in Your Database
Sunday, 26 January 2025
A Step-by-Step Guide to Identifying and Resolving Performance Bottlenecks
Sunday, 19 January 2025
Navigating Human Struggles: A Tale of Empathy in the Workplace
In the bustling world of office dynamics and performance evaluations, there lies a crucial juncture where empathy meets professionalism. Recently, I found myself at such a crossroads during a candid 1:1 performance review with one of my employees. What transpired during this encounter not only reinforced the essence of our organizational culture but also underscored the importance of extending compassion beyond the confines of the workplace.
Sunday, 5 January 2025
Mastering SQL Performance with SQL Plan Management
In my previous blogpost, we delved into the complete details on loading a SQL Plan with its Plan Hash Value. SQL Plan Management (SPM) is a powerful feature designed to ensure consistent and optimized SQL performance by managing execution plans proactively. By leveraging SQL Plan Baselines, SPM helps maintain query efficiency and reliability, making it an essential tool for database administrators. Here's a closer look at how SPM works, its benefits, and its role in maintaining SQL performance.