Oracle Database provides advanced features to optimize storage and enhance performance. Starting with Oracle Database 23c, it is now possible to “estimate the space that you can save by enabling advanced LOB deduplication for existing LOBS“, by using the new GET_LOB_DEDUPLICATION_RATIO function. Let’s quickly demonstrate how it works.
The concept of PDB priority has been introduced with Oracle Database 23c. Simply put, it is now possible to define a priority for some or all PDBs inside a CDB, so that the PDBs will open in a specific order, when opening a list of PDBs or all the PDBs of a CDB. More details can be found in the official documentation.
Let’s see how it works!
Continue reading
One of the benefits of using the online method with ZDM for OCI migration is its ability to allow you to prepare everything upfront, and minimize the number of actions needed on the migration day. It also ensures that everything is running smoothly until the migration day. This means that you can, for example, let ZDM run all the preparation steps, and 10 days later, you can perform the migration.
When using the Logical Online migration method with ZDM, Oracle GoldenGate ensures replication between the source and the target database under the hood. But it happens that manual or applicative actions on the source or on the target database unfortunately break the GoldenGate replication …
Read more: ZDM hints and tips: Logical online method – 3 ways to check the status of a GoldenGate replicat processAfter launching a logical online migration using ZDM, a healthy job status would be similar to:
# zdmcli query job -jobid 77
zdm-server.subnet1.labvcn.oraclevcn.com: Audit ID: 2136
Job ID: 77
User: zdmuser
Client: zdm-server
Job Type: "MIGRATE"
[...]
Current status: PAUSED
Current Phase: "ZDM_MONITOR_GG_LAG"
Result file path: "/u01/app/zdmbase214/chkbase/scheduled/job-77-2023-11-22-13:40:21.log"
Metrics file path: "/u01/app/zdmbase214/chkbase/scheduled/job-77-2023-11-22-13:40:21.json"
Excluded objects file path: "/u01/app/zdmbase214/chkbase/scheduled/job-77-filtered-objects-2023-11-22T13:40:32.108.json"
Job execution start time: 2023-11-22 13:40:21
Job execution end time: 2023-11-22 13:53:21
Job execution elapsed time: 11 minutes 28 seconds
ZDM_VALIDATE_TGT ...................... COMPLETED
ZDM_VALIDATE_SRC ...................... COMPLETED
ZDM_SETUP_SRC ......................... COMPLETED
ZDM_PRE_MIGRATION_ADVISOR ............. COMPLETED
ZDM_VALIDATE_GG_HUB ................... COMPLETED
ZDM_VALIDATE_DATAPUMP_SETTINGS_SRC .... COMPLETED
ZDM_VALIDATE_DATAPUMP_SETTINGS_TGT .... COMPLETED
ZDM_PREPARE_DATAPUMP_SRC .............. COMPLETED
ZDM_DATAPUMP_ESTIMATE_SRC ............. COMPLETED
ZDM_PREPARE_GG_HUB .................... COMPLETED
ZDM_ADD_HEARTBEAT_SRC ................. COMPLETED
ZDM_ADD_SCHEMA_TRANDATA_SRC ........... COMPLETED
ZDM_CREATE_GG_EXTRACT_SRC ............. COMPLETED
ZDM_PREPARE_DATAPUMP_TGT .............. COMPLETED
ZDM_DATAPUMP_EXPORT_SRC ............... COMPLETED
ZDM_UPLOAD_DUMPS_SRC .................. COMPLETED
ZDM_DATAPUMP_IMPORT_TGT ............... COMPLETED
ZDM_POST_DATAPUMP_SRC ................. COMPLETED
ZDM_POST_DATAPUMP_TGT ................. COMPLETED
ZDM_ADD_HEARTBEAT_TGT ................. COMPLETED
ZDM_ADD_CHECKPOINT_TGT ................ COMPLETED
ZDM_CREATE_GG_REPLICAT_TGT ............ COMPLETED
ZDM_START_GG_REPLICAT_TGT ............. COMPLETED
ZDM_MONITOR_GG_LAG .................... COMPLETED
ZDM_PREPARE_SWITCHOVER_APP ............ PENDING
ZDM_SWITCHOVER_APP .................... PENDING
ZDM_POST_SWITCHOVER_TGT ............... PENDING
ZDM_RM_GG_EXTRACT_SRC ................. PENDING
ZDM_RM_GG_REPLICAT_TGT ................ PENDING
ZDM_DELETE_SCHEMA_TRANDATA_SRC ........ PENDING
ZDM_RM_HEARTBEAT_SRC .................. PENDING
ZDM_RM_CHECKPOINT_TGT ................. PENDING
ZDM_RM_HEARTBEAT_TGT .................. PENDING
ZDM_CLEAN_GG_HUB ...................... PENDING
ZDM_POST_ACTIONS ...................... PENDING
ZDM_CLEANUP_SRC ....................... PENDING
Pause After Phase: "ZDM_MONITOR_GG_LAG"
Let’s now create a scenario to break things a little bit … There is a table on the source called bands.labels with a primary key constraint on the column id. Let’s insert a new row in this table, but directly on the target database, while GoldenGate replication is running:
SQL> select * from bands.labels;
ID NAME_LONG CREATION_DATE
_____ ____________________ ________________
6 METAL_MUSIC_LABEL 22-NOV-23
SQL> insert into bands.labels (ID,NAME_LONG) values ('7','SLUDGE_METAL_MUSIC_LABEL');
1 row inserted.
SQL> commit ;
Commit complete.
SQL> select * from bands.labels;
ID NAME_LONG CREATION_DATE
_____ ___________________________ ________________
6 METAL_MUSIC_LABEL 22-NOV-23
7 SLUDGE_METAL_MUSIC_LABEL 22-NOV-23
Now, let’s insert the same row on the source database:
SQL> insert into bands.labels (ID,NAME_LONG) values ('7','SLUDGE_METAL_MUSIC_LABEL');
1 row inserted.
SQL> commit ;
Commit complete.
SQL> select * from bands.labels;
ID NAME_LONG CREATION_DATE
_____ ___________________________ ________________
6 METAL_MUSIC_LABEL 22-NOV-23
7 SLUDGE_METAL_MUSIC_LABEL 22-NOV-23
No SQL error until now … Except that the GoldenGate replicat process will try to insert this row again on the target database, and it will, of course, fail, because of the primary key constraint violation. When we resume the ZDM job, we get the following:
# zdmcli resume job -jobid 77 -pauseafter ZDM_PREPARE_SWITCHOVER_APP
zdm-server.subnet1.labvcn.oraclevcn.com: Audit ID: 2141
# zdmcli query job -jobid 77
zdm-server.subnet1.labvcn.oraclevcn.com: Audit ID: 2143
Job ID: 77
User: zdmuser
Client: zdm-server
Job Type: "MIGRATE"
[...]
Current status: FAILED
Result file path: "/u01/app/zdmbase214/chkbase/scheduled/job-77-2023-11-22-13:40:21.log"
Metrics file path: "/u01/app/zdmbase214/chkbase/scheduled/job-77-2023-11-22-13:40:21.json"
Excluded objects file path: "/u01/app/zdmbase214/chkbase/scheduled/job-77-filtered-objects-2023-11-22T13:40:32.108.json"
Job execution start time: 2023-11-22 13:40:21
Job execution end time: 2023-11-22 14:05:53
Job execution elapsed time: 12 minutes 30 seconds
ZDM_VALIDATE_TGT ...................... COMPLETED
ZDM_VALIDATE_SRC ...................... COMPLETED
ZDM_SETUP_SRC ......................... COMPLETED
ZDM_PRE_MIGRATION_ADVISOR ............. COMPLETED
ZDM_VALIDATE_GG_HUB ................... COMPLETED
ZDM_VALIDATE_DATAPUMP_SETTINGS_SRC .... COMPLETED
ZDM_VALIDATE_DATAPUMP_SETTINGS_TGT .... COMPLETED
ZDM_PREPARE_DATAPUMP_SRC .............. COMPLETED
ZDM_DATAPUMP_ESTIMATE_SRC ............. COMPLETED
ZDM_PREPARE_GG_HUB .................... COMPLETED
ZDM_ADD_HEARTBEAT_SRC ................. COMPLETED
ZDM_ADD_SCHEMA_TRANDATA_SRC ........... COMPLETED
ZDM_CREATE_GG_EXTRACT_SRC ............. COMPLETED
ZDM_PREPARE_DATAPUMP_TGT .............. COMPLETED
ZDM_DATAPUMP_EXPORT_SRC ............... COMPLETED
ZDM_UPLOAD_DUMPS_SRC .................. COMPLETED
ZDM_DATAPUMP_IMPORT_TGT ............... COMPLETED
ZDM_POST_DATAPUMP_SRC ................. COMPLETED
ZDM_POST_DATAPUMP_TGT ................. COMPLETED
ZDM_ADD_HEARTBEAT_TGT ................. COMPLETED
ZDM_ADD_CHECKPOINT_TGT ................ COMPLETED
ZDM_CREATE_GG_REPLICAT_TGT ............ COMPLETED
ZDM_START_GG_REPLICAT_TGT ............. COMPLETED
ZDM_MONITOR_GG_LAG .................... COMPLETED
ZDM_PREPARE_SWITCHOVER_APP ............ FAILED
ZDM_SWITCHOVER_APP .................... PENDING
ZDM_POST_SWITCHOVER_TGT ............... PENDING
ZDM_RM_GG_EXTRACT_SRC ................. PENDING
ZDM_RM_GG_REPLICAT_TGT ................ PENDING
ZDM_DELETE_SCHEMA_TRANDATA_SRC ........ PENDING
ZDM_RM_HEARTBEAT_SRC .................. PENDING
ZDM_RM_CHECKPOINT_TGT ................. PENDING
ZDM_RM_HEARTBEAT_TGT .................. PENDING
ZDM_CLEAN_GG_HUB ...................... PENDING
ZDM_POST_ACTIONS ...................... PENDING
ZDM_CLEANUP_SRC ....................... PENDING
Pause After Phase: "ZDM_PREPARE_SWITCHOVER_APP"
The ZDM job log file will immediately show that the replicat process is ABENDED and will show the error log. Actually, it will display the content of the replicat report file, exactly like on the GoldenGate web user interface:
####################################################################
zdm-server: 2023-11-22T14:04:51.750Z : Resuming zero downtime migrate operation ...
[...]
zdm-server: 2023-11-22T14:05:53.688Z : Status of Oracle GoldenGate replicat process "R95UB" : ABENDED
zdm-server: 2023-11-22T14:05:53.688Z : "R95UB" is processing trail file "Vu" with sequence number "0" at offset "17817"
PRGG-1049 : Oracle GoldenGate process "R95UB" has "ABENDED".
[...]
2023-11-22 14:05:05 WARNING OGG-01154 SQL error 1 mapping source table BANDS.LABELS to target table CHINOOK.BANDS.LABELS. Database error: OCI Error ORA-00001: unique constraint (BANDS.LABELS_PK) violated (status = 1), SQL <INSERT INTO "BANDS"."LABELS" ("ID","NAME_LONG","CREATION_DATE") VALUES (:a0,:a1,:a2)>.
[...]
2023-11-22 14:05:07 ERROR OGG-01668 PROCESS ABENDING.
To access this file in a slightly more readable way, connect to the GoldenGate hub web user interface using your favorite browser. You can get the GoldenGate hub URL from your response file:
# grep GOLDENGATEHUB_URL <your_response_file>.rsp
GOLDENGATEHUB_URL=https://<FQDN or IP address of server hosting GoldenGatedeployment>
If you created your GoldenGate hub using OCI Marketplace, the username and password are available on your GoldenGate hub compute instance in the following file:
$ cat /home/opc/ogg-credentials.json
{"username": "<your_username>", "credential": "<your_strong_password>"}
Once connected, identify your faulty replicat process and click Action > Details > Report. You will get something like:
In the same way, you can see the status and understand why the replicat process abended.
And to finish, my favorite way to dig for more information is to use the GoldenGate REST APIs. It is pretty straightforward, thanks to the documentation:
$ curl -k -u oggadmin -X GET https://<your_ogg_hub>/services/v2/replicats | jq
{
"$schema": "api:standardResponse",
[...]
"messages": [],
"response": {
"$schema": "ogg:collection",
"items": [
{
"links": [
{
"rel": "parent",
"href": "https://<your_ogg_hub>/services/v2/replicats",
"mediaType": "application/json"
},
{
"rel": "canonical",
"href": "https://<your_ogg_hub>/services/v2/replicats/R95UB",
"mediaType": "application/json"
}
],
"$schema": "ogg:collectionItem",
"name": "R95UB"
}
]
}
}
$ curl -k -u oggadmin -X GET https://<your_ogg_hub>/services/v2/replicats/R95UB/info/status | jq
{
"$schema": "api:standardResponse",
[...]
"messages": [],
"response": {
"$schema": "ogg:replicatStatus",
"status": "abended",
"lastStarted": "2023-11-22T14:04:54.287Z",
"lag": 603,
"sinceLagReported": 1583,
"position": {
"path": "/u02/trails/",
"name": "Vu",
"sequence": 0,
"offset": 17817
}
}
}
$ curl -k -u oggadmin -X GET https://<your_ogg_hub>/services/v2/replicats/R95UB/info/reports | jq
{
"$schema": "api:standardResponse",
[...]
"messages": [],
"response": {
"$schema": "ogg:collection",
"items": [
{
"links": [
{
"rel": "parent",
"href": "https://<your_ogg_hub>/services/v2/replicats/R95UB/info/reports",
"mediaType": "application/json"
},
{
"rel": "canonical",
"href": "https://<your_ogg_hub>/services/v2/replicats/R95UB/info/reports/R95UB.dsc",
"mediaType": "application/json"
}
],
"$schema": "ogg:collectionItem",
"name": "R95UB.dsc"
},
{
"links": [
{
"rel": "parent",
"href": "https://<your_ogg_hub>/services/v2/replicats/R95UB/info/reports",
"mediaType": "application/json"
},
{
"rel": "canonical",
"href": "https://<your_ogg_hub>/services/v2/replicats/R95UB/info/reports/R95UB.rpt",
"mediaType": "application/json"
}
],
"$schema": "ogg:collectionItem",
"name": "R95UB.rpt"
},
[...]
]
}
}
$ curl -k -u oggadmin -X GET https://<your_ogg_hub>/services/v2/replicats/R95UB/info/reports/R95UB.rpt | jq
{
"$schema": "api:standardResponse",
[...]
"messages": [],
"response": {
"lines": [
"",
"***********************************************************************",
" Oracle GoldenGate Delivery for Oracle",
" Version 21.7.0.0.1 OGGCORE_21.7.0.0.0OGGRU_PLATFORMS_220723.1000_FBO",
" Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 23 2022 15:05:57",
" ",
"Copyright (C) 1995, 2022, Oracle and/or its affiliates. All rights reserved.",
"",
" Starting at 2023-11-22 14:04:53",
"***********************************************************************",
"",
"Operating System Version:",
"Linux",
"Version #2 SMP Mon Jul 18 02:08:52 PDT 2022, Release 5.4.17-2136.309.5.el7uek.x86_64",
"Node: ogg-server",
"Machine: x86_64",
" soft limit hard limit",
"Address Space Size : unlimited unlimited",
"Heap Size : unlimited unlimited",
"File Size : unlimited unlimited",
"CPU Time : unlimited unlimited",
"",
"Process id: 4528",
"",
"Description: ",
"",
"***********************************************************************",
"** Running with the following parameters **",
"***********************************************************************",
"",
"2023-11-22 14:04:53 INFO OGG-03059 Operating system character set identified as UTF-8.",
"",
[...]
"2023-11-22 14:05:05 WARNING OGG-01154 SQL error 1 mapping source table BANDS.LABELS to target tableCHINOOK.BANDS.LABELS. Database error: OCI Error ORA-00001: unique constraint (BANDS.LABELS_PK) violated (status = 1), SQL <INSERT INTO \"BANDS\".\"LABELS\" (\"ID\",\"NAME_LONG\",\"CREATION_DATE\") VALUES (:a0,:a1,:a2)>.",
[...]
],
"$schema": "ogg:report"
}
}
Whether you are examining ZDM job logs, browsing the GoldenGate hub web interface, or using the GoldenGate REST APIs, I hope this article will help you find what suits you best in these different approaches for troubleshooting issues with the replicat process when using ZDM.
As I work more and more with the Oracle Zero Downtime Migration tool, I also encounter more and more specific problems. And if I manage to (sometimes) find solutions, then why not share them in the form of small blog posts? 1
I will not present ZDM on this blog, as the official documentation as well as my Oracle colleagues are doing a fantastic job exploring the multiple aspects of ZDM. You’ll find references at the end of this post2.
Recently, I was performing a logical online migration using Oracle ZDM on the following environments:
Everything went well during the -eval process, but then the phase ZDM_DATAPUMP_IMPORT_TGT failed during the migrate process:
Even with all my good intentions, work and life challenges got in the way, and I have not been able to blog this year yet.
I still wanted to take part in the #JoelKallmanDay to honor Joel’s memory and to show my gratitude to the Oracle community.
In my (not so) new job at Oracle, I specialize in database migrations in the Cloud, with all sorts of databases and various migration strategies.
A quite common strategy is to use the powerful Oracle Data Pump utility. This blog post will not be about Data Pump, but rather about a very specific column in the dictionary views that I love very much, and that I use every minute of each of my work days (ok, I may be exaggerating a little): ORACLE_MAINTAINED
2 years ago, I wrote my latest blog post. These last 2 years have been hectic and challenging for me. But I recently felt the need to go back to blogging, as much as my current lifestyle will allow it. And what better occasion than the #JoelKallmanDay for this.
What a great loss: Joel Kallman was an exceptional contributor and an excellent guide among the Oracle community.
And talking about community, today I am thankful for the Oracle ACE Program, and the hard-working people who have been keeping this community going for years.
I entered the program in 2018 and it brought me more than I could ever imagine: I met incredible people from all over the world, I went out of my comfort zone thanks to supportive mentors, I learned and also shared, I had rich networking opportunities, I always was in good company during lockdowns, and my career took a fantastic and unexpected turn. Today, I’m not part of the Oracle ACE Program anymore … and I somehow owe it to the program: I recently joined Oracle. 4 years ago, I would never have imagined it could be possible.
I will for sure keep on supporting the Oracle ACE Program in all ways possible.
EDIT : This blog post has been modified thanks to Douglas suggestions, in the comment section.
I recently took the plunge to learn and use Ansible. I read many interesting blog posts on how Ansible can simplify DBA life from a lot of people in the community. But I felt I needed a simple use case to finally get started and become familiar with it.
To overly simplify the concept of Ansible, let’s say it allows to SSH from any machine (control node) to a group of servers (managed nodes) to run the same commands, as long as Ansible is installed on the control node. The magic part is that you don’t have to deploy any agent on the the managed nodes. I will not cover the functioning of Ansible in this blog post, the official documentation is a must-read.
I recently had to deploy Autonomous Health Framework on a group of new servers : finally I got the simple use-case I was waiting for.
Continue reading
One of my colleague was trying to find out which application was inserting “wrong” values on a column of a specific table. It took me some time to understand what a “wrong” value can be, but in this case, it was pretty simple : for example, any value greater than 6 on column NUMBER_OF_LEGS of table INSECTS was considered a “wrong” value.
And actually Fine-Grained Auditing makes it possible in a simple way. Here is an example on Oracle Cloud “Always Free” Autonomous Database 19.5 :
Continue reading
The other day, I was obsessing over an alert in Oracle Enterprise Manager Cloud Control 12c Release 4, because the error message was cryptic. In the Incident Manager console, I got the following line for an Oracle Database 12.2 target :
I expected to have some value or an explicit error message. I did not understand why I would get an “ORA-00942: table or view does not exist”. When drilling down on this alert, I noticed it belonged to a Metric Group called “Deferred Transactions” (and of course I had no idea what it was) :
But I still did not understand why I would get such an error (instead of a numeric value for example) and why the Event Type was “Metric Evaluation Error”.
Starting from Oracle Database 12.2, it is now possible with TDE to encrypt and decrypt tablespaces online. Here are 2 quick examples using OMF and non-OMF tablespaces in Oracle Database version 19.3.
Continue reading
Floo Bar 