DBA Blogs

Graceful Stop of Goldengate Extract

Pakistan's First Oracle Blog - Wed, 2018-11-07 18:58
It's always a good idea to stop extracts after checking if there is any long running transaction in the database being captured. Failing to do so might later result in hung or unstable processes.





Use following command to check the extract:

GGSCI (test) 3> send e* status

Sending STATUS request to EXTRACT ext ...


EXTRACT ext (PID 16649)
Current status: In recovery[1]: Processing data with empty data queue

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

testsrv:/u05/ggate> grep -i bound dirrpt/ext.rpt
Bounded Recovery Parameter:
2017-06-07 15:48:18 INFO OGG-01639 BOUNDED RECOVERY: ACTIVE: for object pool 2: p2628_Redo_Thread_2.
2017-06-07 15:48:18 INFO OGG-01640 BOUNDED RECOVERY: recovery start XID: 129.15.2953694.

-- And then Check RBA is moving with following commands
./ggsci
info e*
lag e*

Categories: DBA Blogs

Goldengate ERROR OGG-02037 Failed to retrieve the name of a missing Oracle redo log.

Pakistan's First Oracle Blog - Wed, 2018-11-07 18:37
One extract got abended and wasn't able to start in Oracle Goldengate Version 12.2.0.1.160517 23194417_FBO. The redologs were present but extract was still abended and threw following error in the report file.

Error:

ERROR OGG-02037 Failed to retrieve the name of a missing Oracle redo log.

Solution:




The solution for this error is to unregister, register and then start the extract as follows:


GGSCI (test) 6> unregister extract ext database 2018-11-07 17:07:03 INFO OGG-01750 Successfully unregistered EXTRACT ext from database. GGSCI (test) 7> GGSCI (test) 7> register extract ext database 2018-11-07 17:07:56 INFO OGG-02003 Extract ESTATDEV successfully registered with database at SCN 1373637632014. GGSCI (test) 8> start extract ext

Hope this helps.
Categories: DBA Blogs

Row visibility set in a package procedure EXECUTE IMMEDIATE 'ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE'

Tom Kyte - Wed, 2018-11-07 11:06
Hi Tom, I've got a table DAVE_ELOF set to row archival (alter table dave_elof row archival;). I want that a user can set row visibility via a package procedure setStudyVisibleYN(aStudyId number, aVisible_YN varchar2). Code snipp...
Categories: DBA Blogs

Parallelism in IOT's

Tom Kyte - Wed, 2018-11-07 11:06
I am trying to join (outer join) a number of IOT's all with the same key in Parallel Mode; and am expecting the optimizer to use a Merge Join in order to avoid sorts (as the documentation clearly says it should); but it does not. - I'm tracing the pr...
Categories: DBA Blogs

history of session for particular sql id

Tom Kyte - Wed, 2018-11-07 11:06
I have a query which takes times to execute. When a user connects to the system and I monitor database I also get the SQL statement which takes times. but when user complete his task and after days he talks to me report takes too much time. I analyz...
Categories: DBA Blogs

How to determine the faster of two queries based on the explain plan

Tom Kyte - Wed, 2018-11-07 11:06
Hello I have two SQL scripts that are producing the exact same result (returning approx. 30000 rows) but the first one does it for 30min. and the second one for 2min. The slower query has (by my understatnding) the better explain plan (because it ...
Categories: DBA Blogs

Hierarchy Validation - Incoming hierarchy vs Expected hierarchy

Tom Kyte - Wed, 2018-11-07 11:06
Hi Experts, I have a requirement to validate the hierarchical structure of flat files which are loaded into the database against an expected structure that is known. I have created some sample data to demonstrate what I would like to do: <code>...
Categories: DBA Blogs

Flashback data archive for auditing

Tom Kyte - Wed, 2018-11-07 11:06
Hi Tom, Greetings !! we would like to leverage the 18c Flashback Data archive feature for auditing in our environment. Currently its trigger based with some business logic implemented in it and only 30% of tables being audited and capturing all...
Categories: DBA Blogs

Which is better to use Direct SQL or Bulk Collect

Tom Kyte - Wed, 2018-11-07 11:06
I have a source table of almost 20 million records. I have to load another table from source table after some checks and validation. What would be the better way to transfer, Insert through Direct SQL or Use Bulk Collect. what is difference between...
Categories: DBA Blogs

How to prevent some firewall from terminating SQL*Plus sessions with long-running statements?

Tom Kyte - Wed, 2018-11-07 11:06
Hi Tom, In know that I should talk to the firewall admins to get things fixed but this is - unfortunately - out of scope. So here we go: We have a database running on some Linux server and some client application running on some W2k16 server. T...
Categories: DBA Blogs

Who last updated an Oracle object

Tom Kyte - Wed, 2018-11-07 11:06
Hello, My goal is to produce a list of all objects (procedures, packages, functions, triggers, and tables) that have changed in the database since they were first created. I have a query that compares "created" and last_ddl_time" in the vi...
Categories: DBA Blogs

Counter to indicate rate changes

Tom Kyte - Tue, 2018-11-06 16:46
Hi Tom! Glad to have you back. Oracle: 12.1.0.2 My challenge is as follows: The t_res table contains hotel room reservations that consist of a reservation id (res_id), date of stay (res_date), and a rate card (rate_id) which typically dic...
Categories: DBA Blogs

How to split big union all query

Tom Kyte - Tue, 2018-11-06 16:46
Hi Tom In my application uses SSRS tool and PLSQL .According the requirement I have category as a in parameter like 'AD','SA','SSV' and 'SOA' , so I have written single query with union all operator for all category. Now problem is my query has b...
Categories: DBA Blogs

Data pump import unable to import table that data pump export exported

Tom Kyte - Tue, 2018-11-06 16:46
I'm having trouble importing a table that export exported. The error I'm getting is "ORA-14308: partition bound element must be one of: string, datetime or interval literal, number, or NULL", but according to the documentation "The partit...
Categories: DBA Blogs

Authorization with External User Access in Application Express

Tom Kyte - Mon, 2018-11-05 22:26
I need to be able to have external (business) users to be able to access only a few features of the site, for example, accounting can only see pages A and B while executives can see A, B and C. I need to be able to have multiple groups of people...
Categories: DBA Blogs

Execute package procedures in parallel

Tom Kyte - Mon, 2018-11-05 22:26
Hi all, For our customer, we start a process in the night to generate orders for 400 stores. Each store can regenerate its order during the day so the procedure is executed separately for each store. For each store we start a scheduled job with...
Categories: DBA Blogs

Insert Rows from one table to columns of other table.

Tom Kyte - Mon, 2018-11-05 22:26
Hi, Hope you are doing good and the Open World was exciting. I have got 2 tables, Table1 has the columns and data that has to be inserted as rows into Table2. I have shared an example of 5 columns, however the number is not constant. This can ch...
Categories: DBA Blogs

Upgrade DR to 12.2.0.1

Tom Kyte - Mon, 2018-11-05 22:26
Hi, The project is to upgrade 11gR2 RAC database to 12cR2(12.2.0.1) with minimal downtime. The approach is using standby database. Logical standby database cannot be done. Number of nodes in 11gr2 cluster 2. What i am doing >>> 1> P...
Categories: DBA Blogs

Partner Webcast – Enterprise ready production microservices on Oracle Cloud Infrastructure ...

There is a huge shift in application development where containerisation has become very popular with the move away from monolith application to microservices. Containers provide a level of isolation...

We share our skills to maximize your revenue!
Categories: DBA Blogs

FIRST_ROWS_10 CBO Is Hopeless, It’s Using The Wrong Index !! (Weeping Wall)

Richard Foote - Mon, 2018-11-05 05:45
There’s an organisation I had been dealing with on and off over the years who were having all sorts of issues with their Siebel System and who were totally convinced their performance issues were due directly to being forced to use the FIRST_ROWS_10 optimizer. I’ve attempted on a number of occasions to explain that their […]
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs