Feed aggregator

SSMA (SQL Server Migration Assistant) handles the whole Oracle-to-SQL Server move: it reads the source data dictionary, converts the schema, then generates a SELECT per table to pull the rows across. That last step is where this story goes wrong.

Migrating ~5,600 Oracle tables to SQL Server with SSMA. Most load fine; ~100 tables fail Migrate Data with the same error:

ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904:
"SYS_C00004_21081414:28:22$": invalid identifier

The named column exists in no DDL anyone wrote. The [Ora] prefix says Oracle itself is rejecting the query: SSMA built an extraction SELECT naming a column Oracle refuses to resolve. Tellingly, SELECT * and COUNT(*) run fine against the same table, whatever this column is, Oracle is happy to ignore it, but not to be asked for it by name.

The name is the giveaway: SYS_C00004_21081414:28:22$ is what Oracle calls a column after ALTER TABLE … SET UNUSED COLUMN.

Oracle offers two ways to get rid of a column: a logical delete and a physical one. The physical delete (ALTER TABLE … DROP COLUMN) is the honest one, but on a large table it is very time- and resource-consuming. That’s why people reach for the logical delete instead:

ALTER TABLE table_name SET UNUSED (column_name);

That statement is metadata-only and instant. The column immediately stops being visible to users, and the physical removal is deferred to whenever there is time for it (see Oracle Documentation):

ALTER TABLE table_name DROP UNUSED COLUMNS;
-- on large tables, cap undo growth by checkpointing every N rows:
ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;

To free the original name for reuse, Oracle renames the column to SYS_C<internal column number>_<YYMMDDHH24:MI:SS>$, sets USER_GENERATED to NO, HIDDEN_COLUMN to YES and releases its COLUMN_ID. So the timestamp is not when the column was added, it is the second someone ran SET UNUSED. Ours says 14 August 2021, 14:28:22.

That also explains the error pattern. The operation is one-way and the column is unreadable by design, so naming it gets you ORA-00904 «invalid identifier». SELECT * and COUNT(*) keep working because the column no longer has a COLUMN_ID and is simply excluded from the star. SSMA, however, lists it and builds an explicit column list Oracle then refuses.

Not to be confused with SYS_NC…$. Those are a different animal: virtual columns backing a function-based index or extended statistics.

Bottom line: returning NULL costs you nothing. This is a column its owner already decided to delete, holding data Oracle itself will no longer hand out. There is no information left to lose.

• SSMA setting Ignore hidden system columns = Yes was not making any effect on this use case
• Dropping the column on SQL Server resolves nothing because the error is on the source SELECT, unaffected.
• Dropping it on Oracle could not be done in our scenario because the source is frozen; DDL not allowed.
• Custom select, column removed or bare NULL: SSMA still expects the name in its mapping and fails with “key not present” or “does not match up” before the query ever reaches Oracle.

Before editing anything, get the full list. Discovering the affected tables one failed migration at a time is a waste of an afternoon because the data dictionary already knows.

The reason the columns are findable at all is an asymmetry between two views: an unused column is gone from ALL_TAB_COLUMNS, but still listed in ALL_TAB_COLS with HIDDEN_COLUMN = 'YES'. That second view is what you query:

SELECT owner,
table_name,
column_name,
data_type,
internal_column_id,
TO_DATE(REGEXP_SUBSTR(column_name, '\d{8}:\d{2}:\d{2}'),
'YYMMDDHH24:MI:SS') AS set_unused_at
FROM dba_tab_cols
WHERE hidden_column = 'YES'
AND user_generated = 'NO'
AND REGEXP_LIKE(column_name, '^SYS_C\d+_\d{8}:\d{2}:\d{2}\$$')
-- AND owner = '<SCHEMA_NAME>'
ORDER BY owner, table_name, internal_column_id;

The regex is deliberately strict: it matches only the SET UNUSED naming pattern, so virtual columns and other system-generated names stay out of the result.

One more view is worth a look, as a cross-check:

SELECT owner, table_name, count AS unused_columns
FROM   dba_unused_col_tabs
--WHERE owner = '<SCHEMA_NAME>'
ORDER  BY count DESC, table_name;

DBA_UNUSED_COL_TABS gives the number of unused columns per table. Sorting by that number puts the dangerous tables first: those with two or three hidden columns are the ones where you’ll forget a line in the custom select and be back at square one.

Keep the hidden column’s name as an alias, but return a literal NULL instead of reading it. SSMA’s mapping finds the name (no “key not present”); Oracle never resolves the real column (no ORA-00904).

• Tools → Project Settings → General → Migration → enable Extended data migration options.

• Data Migration Settings tab → tick Use custom select → replace each hidden-column line with:

 SELECT ...   
    TO_CHAR("<COLUMN_NAME>", 'TM', 'NLS_NUMERIC_CHARACTERS = ''.,''') as "<COLUMN_NAME>",
    NULL as "SYS_C00004_21081414:28:22$"
from <OWNER>.<TABLE_NAME> t

• Migrate Data → 100%. Drop the NULL-filled column(s) on SQL Server in post-migration cleanup.

SYS_C…$ is not an exotic Oracle feature, it’s an ordinary column someone deleted years ago, logically. Oracle keeps the name on file; SSMA finds it, insists on naming it, and Oracle refuses to hand it over. Aliasing a NULL satisfies both, then you drop the column on the target. No source DDL, no external tooling, everything inside SSMA, behind a project setting that’s hidden by default.

L’article Fixing ORA-00904 on Oracle hidden columns during SSMA data migration est apparu en premier sur dbi Blog.

Posted by Pete On 19/08/26 At 12:42 PM

Posted by Pete On 17/08/26 At 12:51 PM

Posted by Pete On 05/08/26 At 09:35 AM

Posted by Pete On 03/08/26 At 01:59 PM

Posted by Pete On 29/07/26 At 02:20 PM

Posted by Pete On 28/07/26 At 08:41 AM

Posted by Pete On 23/07/26 At 12:01 PM

Posted by Pete On 13/07/26 At 09:11 AM

In a previous blog, I presented how to set up a distribution path between two GoldenGate deployments both secured with NGINX. The method I used there was purely through the Web UI. But GoldenGate also exposes a full REST API, and everything you can do in the UI can be done through the API as well, which is useful for automation, scripting, or when the UI is not reachable.

This blog covers the exact same setup, using the REST API instead. I will show two ways of doing it :

• Using oggrestapi.py, the GoldenGate REST client I released in another blog.
• Using the requests library to call the REST API directly.

The prerequisites are the same as in the previous blog :

• Two GoldenGate Microservices deployments, ogg_test_01 (source) on oggvm1 and ogg_test_02 (target) on oggvm2. I will use the latest 26ai version.
• Both OGG setups secured with NGINX acting as a reverse proxy, so everything goes through port 443.
• A running extract on the source, writing to a trail (aa in my case).

Just like in the Web UI, there are three steps to get a working distribution path :

• Create a path connection on the source, to authenticate against the target.
• Register the target’s CA certificate on the source Service Manager.
• Create and start the distribution path.

A quick note on URLs before we start. Behind an NGINX reverse proxy, each service has its own path prefix :

• Administration Service : /services/<deployment>/adminsrvr/v2/...
• Distribution Service : /services/<deployment>/distsrvr/v2/...
• Service Manager : /services/ServiceManager/v2/...

The oggrestapi.py client builds these for you as soon as you pass reverse_proxy=True and the deployment name, so let’s connect once and reuse the client. If you don’t provide the password argument, you will be prompted for it.

from oggrestapi import OGGRestAPI

ogg_source = OGGRestAPI(
    url="https://oggvm1",
    username="ogg",
    deployment="ogg_test_01",
    reverse_proxy=True,
)

As explained in Creating Path Connections with GoldenGate REST API, a path connection is simply an alias in the Network domain. It stores the credentials of a user that exists on the target deployment, and its alias is only known on the source side.

With the client, just call the create_alias method :

ogg_source.create_alias(
    alias="ogg_target",
    domain="Network",
    data={
        "userid": "ogg_user_on_target",
        "password": "***",
    },
)

As mentioned in the introduction, here is the same call with requests, calling the Administration Service of oggvm1 through NGINX :

import requests

auth = ("ogg", "ogg_password")

response = requests.post(
    "https://oggvm1/services/ogg_test_01/adminsrvr/v2/credentials/Network/ogg_target",
    auth=auth,
    json={
        "userid": "ogg_user_on_target",
        "password": "***",
    },
)

After refreshing the source Web UI, the new path connection is visible under the Path Connections tab :

But of course, you can also view the new path connection by calling the REST API:

# Since path connections are aliases of the Network domain, we use the get_alias method to retrieve them
>>> ogg_source.get_alias('Network', 'ogg_target')
{'$schema': 'ogg:credentials', 'userid': 'ogg_user_on_target', 'type': 'PASSWORD'}

Because the deployments are secured with NGINX, the source has to trust the certificate authority that signed the target’s certificate. This is done on the source Service Manager, by registering the target’s root CA certificate.

With the client, use create_deployment_certificate against the source deployment. The certificate type to use is truststore, and the certificate content goes under trustpointBundle.trustpointPem:

target_ca = open("rootCA_ogg_test_02.pem").read()

ogg_source.create_deployment_certificate(
    deployment="ogg_test_01",
    type="truststore",
    certificate="rootCA_ogg_test_02",
    data={
        "trustpointBundle": {
            "trustpointPem": target_ca,
        }
    },
)

The same call with requests, this time on the Service Manager prefix :

target_ca = open("rootCA_ogg_test_02.pem").read()

response = requests.post(
    "https://oggvm1/services/ServiceManager/v2/deployments/ogg_test_01/certificates/truststore/rootCA_ogg_test_02",
    auth=auth,
    json={
        "trustpointBundle": {
            "trustpointPem": target_ca,
        }
    },
)

Registering under the specific deployment (ogg_test_01) is the equivalent of the Local option in the Web UI. To get the Shared behavior instead, register the same certificate under the ServiceManager deployment name, so it becomes available to every deployment on that node.

If the certificate file contains a chain of certificates, you must register each certificate individually, since GoldenGate does not accept them in one go. I described that issue in detail in a blog about the OGG-30007 error.

We can now create the distribution path itself. It has a source endpoint (the local trail) and a target endpoint (the target’s Receiver Service, reached over wss through NGINX). Because the target is NGINX-secured, the target URI :

• uses the wss protocol on port 443,
• points at the Receiver Service path prefix, recvsrvr, not distsrvr (that prefix is only for the Distribution Service on the source side),
• does not carry the path connection alias itself. The alias goes in a separate authenticationMethod key.

With the client :

ogg_source.create_distribution_path(
    distpath="path12",
    name="path12",
    source={
        "uri": "trail://localhost/services/v2/sources?trail=PDB1/aa",
    },
    target={
        "uri": "wss://oggvm2/services/ogg_test_02/recvsrvr/v2/targets?trail=PDB1/bb",
        "authenticationMethod": {
            "domain": "Network",
            "alias": "ogg_target",
        },
    },
    begin="now",
    status="running",
)

And the equivalent requests call, on the Distribution Service prefix (/services/ogg_test_01/distsrvr/):

response = requests.post(
    "https://oggvm1/services/ogg_test_01/distsrvr/v2/sources/path12",
    auth=auth,
    json={
        "name": "path12",
        "source": {
            "uri": "trail://localhost/services/v2/sources?trail=PDB1/aa",
        },
        "target": {
            "uri": "wss://oggvm2/services/ogg_test_02/recvsrvr/v2/targets?trail=PDB1/bb",
            "authenticationMethod": {
                "domain": "Network",
                "alias": "ogg_target",
            },
        },
        "begin": "now",
        "status": "running",
    },
)

The trail value in both URIs also has to match the path the extract actually registers, EXTTRAIL PDB1/aa on the source becomes trail=PDB1/aa in the source URI, and the same logic applies to the target’s bb trail. A bare trail=aa without the PDB path segment matches neither what the extract writes nor what the target’s own directory layout expects.

Once the path is created with status: "running", the trail files start flowing. You can confirm it on the target :

oracle@oggvm2:~/ ll $OGG_DEPLOYMENT_HOME/var/lib/data/PDB1
total 0
-rw-r-----. 1 oracle oinstall 0 Mar 22 07:34 bb000000000

If your distribution path doesn’t start and generates a “certificate that failed validation” error, it means that you incorrectly registered your certificates. Make sure that the target deployment’s CA certificate is registered on the source Service Manager, and not the other way around.

And that’s it. With three REST calls, through oggrestapi.py or using the requests module, you get the exact same NGINX-secured distribution path as the Web UI method, but in a form you can script and repeat.

L’article NGINX Secured Distribution Path with GoldenGate REST API est apparu en premier sur dbi Blog.

A few weeks ago Laurent Schneider messaged me to say Oracle Database 26ai (23.26.2) was supported on Oracle Linux 10 (OL10). That started the usual series of test builds. Basic Installations I already had an installation article for 26ai on OL10, but I updated it, including using the new preinstall package which is now in … Continue reading "Oracle AI Database 26ai (23.26.2) Supported on Oracle Linux 10 (OL10)"

I already covered out-of-place patching from the web UI, but patching tasks should be automated, and clicking through the same screens for every deployment can get repetitive. Let’s do the exact same out-of-place patch of a GoldenGate Microservices Architecture deployment, this time entirely with the REST API.

Every step below shows two ways to make the same call:

• A standard requests call, the default Python module to handle REST APIs.
• The equivalent call using oggrestapi.py, the OGGRestAPI Python client I presented in another blog, which handles everything for you.

This part does not change: the REST API cannot install software on the server, so you still need to unzip the patched installation to a new OGG_HOME and run runInstaller in silent mode, as described in the web UI blog.

As with the web UI, the Service Manager has to be patched first. Assume the following setup:

• sm_url: https://vmogg:7809
• new_ogg_home: /u01/app/ogg/product/23.26.2.0.1
• username / password: an administrator on the Service Manager

Updating OGG_HOME is a PATCH call on the ServiceManager deployment:

import requests

sm_url = "https://vmogg:7809"
auth = ("oggadmin", "password")

requests.patch(
    f"{sm_url}/services/v2/deployments/ServiceManager",
    json={"oggHome": "/u01/app/ogg/product/23.26.2.0.1"},
    auth=auth,
)

With oggrestapi.py:

from oggrestapi import OGGRestAPI

client = OGGRestAPI(url="https://vmogg:7809", username="oggadmin", password="password")
client.update_deployment(deployment="ServiceManager", ogg_home="/u01/app/ogg/product/23.26.2.0.1")

Already, you can see that the REST API client simplifies the patching a lot.

Restarting the Service Manager is the same endpoint, this time setting status:

requests.patch(
    f"{sm_url}/services/v2/deployments/ServiceManager",
    json={"status": "restart"},
    auth=auth,
)

client.restart_deployment(deployment="ServiceManager")

restart_deployment is a dedicated method in oggrestapi.py, following the same pattern already used for restart_service, restart_extract and restart_replicat. It makes it easier to use the API, instead of building the {"status": "restart"} payload yourself. It also takes an optional only_if_running argument, so a deployment that was already stopped before the patch is left alone rather than being started by the restart call.

As with the web UI, all your deployment processes are still running on the old OGG_HOME at this point. The AIService, introduced in 26ai, does not pick up the new home automatically either. You should then list the services attached to the Service Manager and restart the ones that are not ServiceManager itself:

services = requests.get(
    f"{sm_url}/services/v2/deployments/ServiceManager/services",
    auth=auth,
).json()["response"]["items"]

for service in services:
    if service["name"] != "ServiceManager":
        requests.patch(
            f"{sm_url}/services/v2/deployments/ServiceManager/services/{service['name']}",
            json={"status": "restart"},
            auth=auth,
        )

for service in client.list_services("ServiceManager"):
    if service.get("name") != "ServiceManager":
        client.restart_service(deployment="ServiceManager", service=service.get("name"))

Once the Service Manager runs on the new home, repeat the same update, then restart sequence for each deployment (oggHome, then status: restart):

deployment = "ogg_test_01"

requests.patch(
    f"{sm_url}/services/v2/deployments/{deployment}",
    json={"oggHome": "/u01/app/ogg/product/23.26.2.0.1"},
    auth=auth,
)

requests.patch(
    f"{sm_url}/services/v2/deployments/{deployment}",
    json={"status": "restart"},
    auth=auth,
)

client.update_deployment(deployment="ogg_test_01", ogg_home="/u01/app/ogg/product/23.26.2.0.1")
client.restart_deployment(deployment="ogg_test_01")

Once the deployment is back up, restart its extracts and replicats. Since these processes are not accessible through the Service Manager port, you need to change the URL. If you use a reverse proxy setup, or auto_discovery=True (see below), this is also easier with the Python client.

admin_url = "https://vmogg:7810"

extracts = requests.get(f"{admin_url}/services/v2/extracts", auth=auth).json()["response"]["items"]
for extract in extracts:
    requests.patch(f"{admin_url}/services/v2/extracts/{extract['name']}", json={"status": "stopped"}, auth=auth)
    requests.patch(f"{admin_url}/services/v2/extracts/{extract['name']}", json={"status": "running"}, auth=auth)

replicats = requests.get(f"{admin_url}/services/v2/replicats", auth=auth).json()["response"]["items"]
for replicat in replicats:
    requests.patch(f"{admin_url}/services/v2/replicats/{replicat['name']}", json={"status": "stopped"}, auth=auth)
    requests.patch(f"{admin_url}/services/v2/replicats/{replicat['name']}", json={"status": "running"}, auth=auth)

With oggrestapi.py, restart_all_extracts and restart_all_replicats do the same thing on an OGGRestAPI client already pointed at the deployment (either connected directly to its Administration Service, or through an NGINX reverse proxy with deployment= set):

admin_client = OGGRestAPI(url="https://vmogg:7810", username="oggadmin", password="password")
admin_client.restart_all_extracts(only_if_running=True)
admin_client.restart_all_replicats(only_if_running=True)

The steps listed above (update home, restart deployment, restart processes for every deployment) is exactly what patch_deployment (a single deployment) and patch_deployments (all of them) already do in oggrestapi.py, internally calling restart_deployment for the restart step. They also handle the ServiceManager special case (patch and restart the deployment and its services, but never restart extracts and replicats on it) and the wait_until_deployment_status polling in between:

client = OGGRestAPI(url="https://vmogg:7809", username="oggadmin", password="password", reverse_proxy=True)
client.patch_deployments(new_home="/u01/app/ogg/product/23.26.2.0.1", ask_credentials=False)

Both methods take restart_after_patch and restart_processes_after_patch (both default to True) if you need to skip either step, for example to patch every home first and restart everything in a separate maintenance window:

client.patch_deployments(
    new_home="/u01/app/ogg/product/23.26.2.0.1",
    restart_after_patch=False,
    restart_processes_after_patch=False,
    ask_credentials=False,
)

restart_processes_after_patch=True needs per-deployment routing: restarting extracts and replicats on ogg_test_01 is a different call than on ogg_test_02, and a plain connection to the Service Manager’s own port has no way to reach either one. oggrestapi.py gives you two ways to get that routing from a single client:

• reverse_proxy=True, shown above, if you already run NGINX in front of your deployments.
• auto_discovery=True, with no reverse proxy at all. The client looks up each deployment’s real Administration/Distribution/Performance Metrics Service port through the Service Manager itself (the same GET .../deployments/{deployment}/services/{service} call), the first time each one is actually needed, and reuses that lookup for the rest of the run:

client = OGGRestAPI(url="https://vmogg:7809", username="oggadmin", password="password", auto_discovery=True)
client.patch_deployments(new_home="/u01/app/ogg/product/23.26.2.0.1", ask_credentials=False)

Without either flag, patch with restart_processes_after_patch=False and restart each deployment’s processes yourself through a separate client pointed at that deployment’s own admin URL.

Same as with the web UI: a restart call on a deployment returns as soon as the Administration Service (adminsrvr) is back up. The Receiver Service (recvsrvr) or the Distribution Service (distsrvr) can take a bit longer to restart. If, after polling for a few minutes, a service is still reporting the old home, restart it individually with the same PATCH .../services/{service} call shown above for the AIService, or with the restart_service method.

If you change the name of your home at every release, remember to update OGG_HOME in every script and environment that references it, for example:

• DMK environment files.
• systemd service files, which might hardcode the OGG_HOME variable.

L’article GoldenGate 26ai out-of-place patching with Python est apparu en premier sur dbi Blog.