Oracle GoldenGate From On-Premises to OCI DBCS – Part 1: Configuring the Source Environment

 

Introduction:

This is Part 1 of a four-part blog series that demonstrates how to replicate data from an on-premises Oracle Database to a database running on Oracle Cloud Infrastructure (OCI) using Oracle GoldenGate.

In this blog, we focus on configuring Oracle GoldenGate in the on-premises source environment, including database prerequisites, user setup, and preparing the source database for change data capture.

Related blogs in this series:

• Part 2: Configuring the Target Environment on OCI
• Part 3: Connecting On-Premises GoldenGate to Source and OCI GoldenGate to Target Database
• Part 4: Connecting On-Premises GoldenGate to OCI using NGINX and Creating Extract & Replicat

OCI Bastion RDP Failure: Troubleshooting Error 0x708 with VNC Console Access by creating local console connection

 

Introduction:

Oracle Cloud Infrastructure (OCI) provides Bastion as a secure and convenient way to access private resources without exposing them directly to the internet. Recently, while trying to connect to a Windows Server 2012 instance via RDP through a Bastion host, I encountered connection issues. Despite setting up SSH port forwarding, the RDP session would not establish. This experience highlighted an important consideration: while Bastion is ideal for many scenarios, there are situations where it may not work, and OCI’s Console Connection (VNC) can be a reliable alternative.

In this blog, I’ll walk you through the root cause of the Bastion failure, explain why it occurs, and guide you on how to use Console connections to access Windows instances.

Fixing ORA-30012: Unable to Convert Physical Standby to Snapshot Standby

 

Introduction 

I recently encountered the frustrating "ORA-30012: undo tablespace 'UNDOTBS1' does not exist or is of wrong type" while attempting to convert my Data Guard Physical Standby into a writable Snapshot Standby for an important testing cycle. In my setup, the Primary database was a RAC running on Exadata Database Service on Dedicated Infrastructure, while the Standby was a single-instance deployment on DBCS. This mixed environment introduced subtle configuration differences, and one of those differences caused the ORA-30012 failure during the snapshot conversion. In this post, I’ll walk through how I investigated the issue, identified the undo-related mismatch on the standby, and applied the correct fix to enable the Snapshot Standby conversion successfully.

Troubleshooting ORA-29771 on a DBCS Data Guard Standby

Introduction

Recently, I encountered an issue on a DBCS Data Guard standby database where the alert log reported the error ORA-29771. After seeing this error, I checked the Data Guard status and noticed that the standby had developed a significant apply lag, even though redo apply was still running.

In this blog, I’ll explain the symptoms I observed, how I investigated the cause of the ORA-29771 error, and the steps I took to restore normal standby performance.

Fixing a DBCS Startup Failure Caused by ORA-07445

 

Introduction:

Running Oracle databases on Oracle Database Cloud Service (DBCS) usually provides a stable and well-automated environment, but unexpected platform-level issues can still occur—especially when critical OS-level components are modified or removed. Recently, I encountered a serious problem where a DBCS compute node failed to start the database, and the stack repeatedly threw an ORA-07445 error during the startup process.

At first glance, the issue looked like a typical software or patch conflict, but after deeper investigation, it turned out to be something far more fundamental: the swap mount point and related configuration had been cleared or removed on the compute instance. Without an active swap device, several Oracle background processes failed during initialization, resulting in the ORA-07445 crash.

In this blog, I’ll walk you through the symptoms, diagnostic steps, and the exact solution that restored the instance.

Exploring Bigfile Tablespaces with Default Settings in oracle 23ai

BIGFILE Tablespace

A bigfile tablespace is a tablespace with a single, but large datafile. Traditional small file tablespaces, in contrast, typically contain multiple datafiles, but the files cannot be as large. Making SYSUAX, SYSTEM, and USER tablespaces bigfile by default will benefit large databases by reducing the number of datafiles, thereby simplifying datafile, tablespace, and overall global database management for users.

Oracle 23ai: SQL Productivity Power-Up – Streamlined SELECT Syntax

 

Introduction

Oracle 23ai clearly shows that Oracle is putting developers first when it comes to SQL. For a long time, writing Oracle SQL meant putting up with extra clutter—things like always using FROM DUAL or repeating the same expressions again and again in GROUP BY clauses. It worked, but it often made simple queries feel unnecessarily complicated.

With the latest releases, Oracle is clearly trying to make SQL easier and more pleasant to use. The focus is on writing cleaner, more natural code with less repetition. In this blog, I will look at four small but powerful improvements that remove a lot of that old hassle and let you focus on the data and logic instead of outdated syntax rules.

SELECT without FROM

Previously, Oracle required a FROM clause for every SELECT statement, forcing developers to use the DUAL to retrieve constants, system dates, or perform calculations. Now, you can simply SELECT what you need. It is cleaner, shorter, and matches the behavior of PostgreSQL and SQL Server.

Example 

In this example, we retrieve the current system date, add a simple label, and calculate a projected value, all without using DUAL.

SELECT 
    SYSDATE AS current_time, 
    'HR_REPORT' AS report_tag, 
    (100 * 1.15) AS projected_value;

VALUES Clause Inside SELECT

The VALUES clause used to be limited to INSERT statements, but Oracle 23ai elevates it into a powerful table constructor. You can now use VALUES directly inside a SELECT, effectively creating a small in-memory table on the fly.

This is especially useful when you need a temporary lookup set without creating a table, using UNION ALL, or relying on temporary tables.

Example: 

In this example, the VALUES clause is used to create a temporary, in-memory table directly inside the query. Think of it as a small dataset that exists only for the duration of this SELECT. Each row in the VALUES list represents an employee ID paired with a performance rating.

The alias v(emp_id, rating) gives names to the two columns created by the VALUES clause. This is important because it allows Oracle to treat the result just like a normal table, with clearly defined column names.

The query then joins this temporary table to the EMPLOYEES table using a standard JOIN. The join condition matches employees.employee_id with v.emp_id, ensuring that only employees listed in the VALUES clause are returned.

As a result, the query displays each employee’s last name alongside the temporary rating assigned in the VALUES list. 

SELECT e.last_name, v.rating
FROM employees e
JOIN (VALUES (100, 'A+'), (101, 'B'), (102, 'A')) AS v(emp_id, rating)
  ON e.employee_id = v.emp_id;
  
---The following is another syntax for it with the same output
  SELECT e.last_name, v.rating
FROM employees e,  (VALUES (100, 'A+'), (101, 'B'), (102, 'A')) AS v(emp_id, rating)
  where e.employee_id = v.emp_id;

GROUP BY Alias

Repeating long expressions in the GROUP BY clause has always been one of Oracle SQL’s biggest annoyances. If you used a CASE expression or formatted value in the SELECT, you had to copy it exactly into the GROUP BY.

Oracle 23ai finally fixes this by allowing you to reference column aliases directly. This makes queries shorter, clearer, and much easier to maintain.

Example 

In this example, employees are grouped into salary bands using a CASE expression. Instead of repeating the entire expression in GROUP BY, I simply reuse the alias salary_level.

SELECT 
    CASE WHEN salary > 10000 THEN 'High' ELSE 'Standard' END AS salary_level,
    COUNT(*) AS total_staff
FROM employees
GROUP BY salary_level;

GROUP BY ALL

Complex reports often require grouping by many columns, which means copying those same column names into the GROUP BY clause. This is tedious and error-prone. GROUP BY ALL solves this by automatically grouping on every non-aggregated column in the SELECT list.

Example

This query calculates the average salary for each combination of department, job, and manager. It uses GROUP BY ALL to automatically group by all the non-aggregated columns in the SELECT list, so you don’t have to list them again manually.

The HAVING clause then filters the grouped results, keeping only those groups where the average salary is greater than 5,000. In addition, this example shows that in Oracle 23ai, you can use a column alias (avg_sal) directly in the HAVING clause, which makes the query shorter and easier to read.

SELECT 
    d.department_name, 
    e.job_id, 
    e.manager_id,
    ROUND(AVG(e.salary), 2) AS avg_sal
FROM employees e , departments d 
 Where  e.department_id = d.department_id
GROUP BY ALL
Having avg_sal >5000;

Conclusion

Oracle 23ai and 26ai make it clear that Oracle is focusing more on developers and their day-to-day work. These updates don’t change the power of Oracle SQL, but they make it much easier and nicer to use. By removing extra syntax, writing SQL becomes simpler, clearer, and less error-prone.

Whether you’re creating reports, running analytics, or just querying data, these improvements will quickly feel natural. After using them, the older ways of writing SQL will seem outdated, and that’s a good thing.