Monthly Archive: September 2019

OGG-01031 Output file is not in any allowed output directories

There are many new features and enhancements introduced in Oracle GoldenGate 12.2. YES, the current version of OGG is 19c. But I still thought of writing this article as this one of the important one. In this article, I am going to explain you about the parameter ALLOWOUTPITDIR with an example.

The Extract Pump (DataPump) process is abending with the below error,

2019-06-11 21:48:42 ERROR OGG-01031 There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Output file /u02/ogg_data/dirdat/et000000 is not in any allowed output directories.).

2019-06-11 21:48:42 ERROR OGG-01668 PROCESS ABENDING.

In the above error message, do not see the ERROR code, which is a default or generic one. Check the message mentioned inside the braces.

Reply received is Output file /u02/ogg_data/dirdat/et000000 is not in any allowed output directories.

From the above message we can understand that, OGG is not allowing to open / create a trail file in above mentioned location and hence the process is failing with the error as above.

This is a new enhancement with respect to security from OGG 12.2

ALLOWOUTPUTDIR

A new parameter is introduced from Oracle GoldenGate 12.2 version. This parameter should be used in the ./GLOBALS parameter file. Failing to use it will encounter the error in the pump process at the Source as below,

ERROR OGG-01031 There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Output file is not in any allowed output directories.).

To overcome this issue, please follow the below action plan,

1. Stop the Manager, Replicat processes in the target.

2. Edit the ./GLOBALS parameter with the below command

GGSCI> EDIT PARAMS ./GLOBALS

3. Add the parameter ALLOWOUTPUTDIR with path to the output trail file directory and save it.

Example ALLOWOUTPUTDIR [relative_dir_name | absolute_dir_name]

4. Exit from the GGSCI prompt.

5. Enter the GGSCI prompt

6. Perform DBLOGIN

7. Start the Manager and Replicat processes.

Note: Whenever we make any changes to ./GLOBALS file, we need to exit and relogin to the GGSCI. Without this the changes made to ./GLOBALS will not be in effect.

This is case sensitive for Windows and driver letter should match exactly as is, for example:

If your driver letter is in lower case but you had mentioned as below,

ALLOWOUTPUTDIR D:\ogg_12c

You will again hit the error OGG-01031.

So, it should be as below,

ALLOWOUTPUTDIR d:\ogg_12c

Hope you enjoyed the post. Cheers 🙂

Oracle GoldenGate Logdump

We cannot open a Redo or Archive log directly in Oracle Database. We need a utility called Log Miner for that. Similarly, in Oracle GoldenGate, we cannot open a Trail file directly and check the data written to it.

LOGDUMP is the tool or utility which is used to open a Trail File in Oracle GoldenGate.

So, what can we seen in the trail file? What information are there in the trail file?

In the above image, we could IOType and TransInd. What are they?

IOType

For each and every operations like INSERT, DELETE, UPDATE, DDL operations etc, it has their own IDs. Means Insert is specified with IOType 5, DELETE is mentioned as 3, COMMIT is mentioned as 2. We have many IO types as below,

TransInd

It is nothing but, Transaction Indicator. We have four kind of Transaction Indicator. Please check the below image for more details,

Let’s continue to see what other information can be seen in the Logdump.

The trail file information which we had seen above are all prior to Oracle GoldenGate 12.2. From 12.2, more information are captured by the Extract process and written to trail file. This Enhancement of Trail file is called “Self Describing Trail Files”. We will see discuss about this feature in our upcoming post.

Some of the basic commands and it’s uses are below,

Hope you enjoyed the post. Keep supporting. Cheers 🙂

Downgrade the Extract (or) Capture from Integrated mode to Classic mode

There are two types of Extract / Capture processes in Oracle GoldenGate.

1. Classic Extract

2. Integrated Extract

Classic Extract is a normal Extract process. It is a single threaded process. If you want to know more about the Integrated Extract process, click the below link,

In my earlier post, we had seen about how to upgrade the Extract/Capture process from Classic to Integrated. Now, let’s see how to downgrade the Integrated Capture to Classic.

Sometimes, we face issues in Integrated mode of capture and we can downgrade it to the Classic mode to keep moving. But this will not help in all the cases. As I explained in my previous article, before downgrading the Integrated extract to Classic mode, we need check if the existing environment can be supported by Classic mode or not. If you are having RAC environment and if there are XA transactions or if your database is compression enabled, then it is not possible to use Classic Extract process. In this situation it is not advised to downgrade the Extract process from Integrated to Classic.

But in some situations this really helps to move forward and avoid latency in replication. Below are high level steps to downgrade the Extract process from Integrated to Classic.

1. Stop the Extract process

GGSCI> STOP EXTRACT

2. Issue the following command to determine whether the downgrade command can be issued. Transactions that started before the downgrade command is issued must be written to the trail before you can proceed. You may have to issue this command more than once until it returns a message stating that Extract can be downgraded.

GGSCI> INFO EXTRACT group DOWNGRADE

3. Downgrade the Extract process.

GGSCI> ALTER EXTRACT DOWNGRADE INTEGRATED TRANLOG

4. Unregister the Extract process from the Database.

GGSCI> UNREGISTER EXTRACT DATABASE

5. Start the Extract process.

GGSCI> START EXTRACT

Let me explain this with an example so that it makes an easier way of understanding.

I have an Extract process EXT1 which is in Integrated Mode. As I explained in my previous article, you could easily find out if the Extract process is Classic or Integrated using the INFO command output,

In the INFO command output, if the,

Log Read Checkpoint – Oracle Redo Logs – Classic Extract

Log Read Checkpoint – Oracle Integrated Redo Logs – Integrated Extract

If you see the output of the INFO command here, you can see the “Log Read Checkpoint” as “Oracle Integrated Redo Logs” which means, here the Extract EXT1 is an Integrated Extract process.

1. Always perform a DBLOGIN whenever you perform any activity in Oracle GoldenGate.

2. Stop the Extract process.

3. Issue the following command to determine whether the downgrade command can be issued. Transactions that started before the downgrade command is issued must be written to the trail before you can proceed. You may have to issue this command more than once until it returns a message stating that Extract can be downgraded.

4. The Extract process is ready to be downgraded. But, before that, we need to edit parameter files and remove/comment or add the parameters which are necessary for Classic Extract process. For example, in my parameter file, I have the parameter below,

TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 500)

which is not supported by Classic Extract process. So, make necessary changes to the parameter file before downgrading and starting the Extract process.

5. Downgrade the Extract process.

If you see the output of the INFO command, the value of the “Log Read Checkpoint” is now changed to “Oracle Redo Logs”. This means the Extract process has been downgraded from Integrated to Classic.

But still you can see the Logminer Capture which was created when you registered this Extract process EXT1 to the database exists.

This is because, we haven’t unregistered the extract process from the database.

6. Unregister the Extract process from the database.

Now check the DBA_CAPTURE view again. It will return no rows.

7. Start the Extract process.

See you again. Cheers 🙂

Upgrade Extract from Classic mode to Integrated mode

Sometimes, we may face a situation where in we need to upgrade our Extract process from Classic mode to Integrated mode.

This situation may arise due to the below reasons,

1. When some data types which are not supported by Classic Capture.
2. When you are using Classic Capture in a RAC environment. This could be the main reason. I would always recommend to go with Integrated Mode in a RAC environment.
3. When you don’t get satisfied with the performance of the Classic capture as it is a single threaded one.
4. When you face a error or bug in Classic mode, which you may not face in Integrated mode.

Likewise, we can keep on adding points for the above situation.

In this article, we are going to see how to upgrade an Extract / Capture process from Classic mode to Integrated mode. Below are the steps,

1. Stop the Extract process

GGSCI> STOP EXTRACT

2. Register the Extract process

GGSCI> REGISTER EXTRACT DATABASE

3. Issue the following command to determine whether the upgrade command can be issued. Transactions that started before the registration command must be written to the trail before you can proceed with the upgrade. You may have to issue this command more than once until it returns a message stating that Extract can be upgraded.

GGSCI> INFO EXTRACT UPGRADE

4. Once confirmed, Upgrade the Extract process.

GGSCI> ALTER EXTRACT UPGRADE INTEGRATED TRANLOG

5. Before starting the Extract process, edit the parameter file accordingly. You might have used the below parameter in the Extract process parameter file which is not supported by Integrated Extract,

TRANLOGOPTIONS DBLOGREADER

This will make the Integrated Extract process to abend when you start it. Like wise you have many parameters which are not supported by Integrated Mode and used only for Classic mode. Check and remove those parameters before starting the Integrated Extract process after upgrading it.

So, let me explain you with an example so that it would be more easier for you to understand.

I have an Extract/Capture process EXT1 which is running in Classic mode.

From the below INFO output, you could see the “Log Read Checkpoint” as “Oracle Redo Logs”. This means, that the extract process is a Classic Extract.

1. When doing any activity in Oracle GoldenGate, it is always recommended to perform a DBLOGIN. So, perform the DBLOGIN,

2. Stop the Extract process. Check if the extract EXT1 has stopped.

3. Register the Extract process to the database. You might be aware of this command. REGISTER command creates a Logminer Capture in the database.

Once you register the Extract process to the database, you can query the DBA_CAPTURE view to check if a Logminer server has been created or not.

The status is in “DISABLED” as we did not start it yet.

But when issuing the INFO command, you will still the extract process in Classic mode. This is because, we haven’t upgraded the extract process. We had just registered it to the database.

4. Issue the following command to determine whether the upgrade command can be issued. Transactions that started before the registration command must be written to the trail before you can proceed with the upgrade. You may have to issue this command more than once until it returns a message stating that Extract can be upgraded.

5. START and STOP the extract process so that it bypasses the SCN with which it was registered. It has to reach the SCN 1077042 as mentioned in the above message.

6. It bypassed the SCN. Now, stop the Extract process.

7. Check if the Extract process is ready for the upgrade by issuing the below command,

8. Now the Extract process EXT1 is ready for upgrade. Issue the below command to upgrade the capture from Classic mode to Integrated mode.,

9. Before starting the Extract process, edit the parameter file accordingly. You might have used the below parameter in the Extract process parameter file which is not supported by Integrated Extract,

TRANLOGOPTIONS DBLOGREADER

This will make the Integrated Extract process to abend when you start it. Like wise you have many parameters which are not supported by Integrated Mode and used only for Classic mode. Check and remove those parameters before starting the Integrated Extract process after upgrading it.

In my case, I haven’t used any such parameters. So, I had just added one of the parameter which is used by Integrated mode of Extract process. Below is my param file,

10. Now, go ahead and start the Extract process.

11. Check if the extract process has been upgraded from Classic to Integrated. From the below output, you can clearly see the “Log Read Checkpoint” is changed from “Oracle Redo Logs” to “Oracle Integrated Redo Logs”. Now the Extract process is running in INTEGRATED mode.

From the report file, you can see the below lines,

From the DBA_CAPTURE view you can see now the Capture status as “ENABLED”.

Hope you enjoyed the post. Cheers 🙂