Looking for something? Try here..

Wednesday, December 30, 2015

Uninstalling GoldenGate software

GoldenGate software can be uninstalled using the deinstall script available in deinstall directory under GoldenGate Home. The following explains the steps on the same.

[oracle@12r1-rac1 12121]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug  7 2014 10:21:34
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.



GGSCI (12r1-rac1.selvapc.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           
EXTRACT     ABENDED     DEMO1E      00:00:02      154:53:03   

GGSCI (12r1-rac1.selvapc.com) 2>

The process is already abended as I did some changes (out of scope for this article). If the process is running, please stop the process by stop command.
STOP EXTRACT DEMO1E

One can stop all the extract and replicat process at once issueing following command
STOP ER *

Stop the manager process if it is running. In our case it is already stopped hence skipping the step. Also the following delete extract step is optional as GoldenGate deinstall removes everything by itself.

GGSCI (12r1-rac1.selvapc.com) 2> delete ext *
Are you sure you want to delete all groups? y
Deleted EXTRACT DEMO1E.


GGSCI (12r1-rac1.selvapc.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           


GGSCI (12r1-rac1.selvapc.com) 4> exit

Run the following scripts to disable and remove DDL replication objects and marker objects.

[oracle@12r1-rac1 12121]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Dec 31 12:17:03 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> @ddl_disable    

Trigger altered.

SQL> @ddl_remove

DDL replication removal script.
WARNING: this script removes all DDL replication objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:ggs_dba
Working, please wait ...
Spooling to file ddl_remove_spool.txt

Script complete.
SQL> @remove_marker
SP2-0310: unable to open file "remove_marker.sql"
SQL> @marker_remove

Marker removal script.
WARNING: this script removes all marker objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:ggs_dba

PL/SQL procedure successfully completed.


Sequence dropped.


Table dropped.


Script complete.
-- The following revoke is optional as I'm going to drop the user ggs_dba once goldengate is uninstalled. 
-- For target database the privilege_type is 'apply' or '*' is both capture and apply is granted.
SQL> exec dbms_goldengate_auth.revoke_admin_privilege(grantee => 'ggs_dba', privilege_type => 'capture');

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

Change to deinstall directory under GoldenGate Home and run the deinstall script.

[oracle@12r1-rac1 12121]$ cd deinstall/
[oracle@12r1-rac1 deinstall]$ ./deinstall.sh

ALERT: Ensure all the processes running from the current Oracle Home are shutdown prior to running this software uninstallation script.

Proceed with removing Oracle GoldenGate home: /u02/gg_home/12121 (yes/no)? [no] 
yes
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 3068 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-12-31_12-22-16PM. Please wait ...Oracle Universal Installer, Version 11.2.0.3.0 Production
Copyright (C) 1999, 2011, Oracle. All rights reserved.

Starting deinstall


Deinstall in progress (Thursday, December 31, 2015 12:22:36 PM IST)
............................................................... 100% Done.

Deinstall successful


End of install phases.(Thursday, December 31, 2015 12:23:02 PM IST)
End of deinstallations
Please check '/oracle/oraInventory/logs/silentInstall2015-12-31_12-22-16PM.log' for more details.
[oracle@12r1-rac1 deinstall]$ 
[oracle@12r1-rac1 deinstall]$ cd ..
cd: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
[oracle@12r1-rac1 ..]$ pwd
/u02/gg_home/12121/deinstall/..
[oracle@12r1-rac1 ..]$ cd /u02/gg_home/
[oracle@12r1-rac1 gg_home]$ ls -lrt
total 0

Once the GoldenGate software is uninstalled, one can drop the GoldenGate schema.

[oracle@12r1-rac1 gg_home]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Dec 31 12:24:20 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> drop user ggs_dba cascade;

User dropped.

SQL> 

Everything's done!
Happy deinstall!!

Friday, December 4, 2015

OGG-01423 No valid default archive log destination directory found for thread 1

Today while I was trying to set up GoldenGate sync between Oracle database 11.2.0.1 running on Windows 10 with Oracle database 12.1.0.2 running on openSUSE (this is a lab set up), I received the following error when attempting to start the real time extract.

ggsci > ADD EXTRACT DEMO1E, TRANLOG, BEGIN NOW
ggsci > ADD EXTTRAIL E:\oracle\ogg112101\dirdat\ea, EXTRACT DEMO1E, Megabytes 200
ggsci > start extract DEMO1E
INFO all command showed the process as ABENDED.
The report file DEMO1E.rpt shows the below.

...
...
...

2015-12-04 02:48:50  WARNING OGG-01423  No valid default archive log destination directory found for thread 1.

2015-12-04 02:48:50  INFO    OGG-01515  Positioning to begin time Dec 4, 2015 1:59:16 AM.

Source Context :
  SourceModule            : [er.common]
  SourceID                : [er/common.cpp]
  SourceFunction          : [extract_start_point]
  SourceLine              : [2090]

2015-12-04 02:49:10  ERROR   OGG-00446  Error 5 (Access is denied.) opening log file E:\ORACLE\ORADATA\ORCL\REDO03.LOG for sequence 57. Not able to establish initial position for begin time 2015-12-04 01:59:16.

2015-12-04 02:49:10  ERROR   OGG-01668  PROCESS ABENDING.
A similar issue may arise if the source is a RAC clustered database under different scenario, but this is not a RAC database but a standalone.

So why is this error reported? (see highlighted error line)
If you look at the archive log destination in the database, we have defined it to use the FRA.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     55
Next log sequence to archive   57
Current log sequence           57

SQL> sho parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      

Also, there is no value defined in the default archive location ( the database is created using DBCA while installing the Oracle software using default values)

Hence, I tried to give a location to the archive log so that GoldenGate can check in the location specified. I did the below.

SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST' scope=both;

SQL> sho parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------------
log_archive_dest_1                   string      location=USE_DB_RECOVERY_FILE_DEST

Now, after setting the location, I started the extract to which the response is positive with the following contents in the report file.

ggsci > start extract DEMO1E
The report file contents...

...
...
...
             
2015-12-04 02:57:23  WARNING OGG-01423  No valid default archive log destination directory found for thread 1.

2015-12-04 02:57:24  INFO    OGG-01515  Positioning to begin time Dec 4, 2015 2:56:48 AM.

2015-12-04 02:57:24  INFO    OGG-01052  No recovery is required for target file E:\oracle\ogg112101\dirdat\ea000000, at RBA 0 (file not opened).

2015-12-04 02:57:24  INFO    OGG-01478  Output file E:\oracle\ogg112101\dirdat\ea is using format RELEASE 11.2.

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************


2015-12-04 02:57:25  INFO    OGG-01515  Positioning to begin time Dec 4, 2015 2:56:48 AM.

2015-12-04 02:57:25  INFO    OGG-01516  Positioned to Sequence 57, RBA 3447824, SCN 0.0, Dec 4, 2015 2:56:48 AM.

2015-12-04 02:57:25  INFO    OGG-01517  Position of first record processed Sequence 57, RBA 3447824, SCN 0.2086081, Dec 4, 2015 2:57:15 AM.

Now, we have the same warning message, but the extract process started to process the first record. (I'm still wondering why does it throw Access is denied for online redo log when all the permission required is available ;-) )

Happy Troubleshooting!!