DBA Blogs

Materialized view refresh in case of DDL on base table, like truncate, drop and recreate

Tom Kyte - Wed, 2018-03-28 01:46
Hi Tom, I was trying to prepare materialized view against some table which are periodically dropped and recreated. I did not find an option to automatically refresh the materialized view in this case. Refresh FAST ON COMMIT is not working, and REF...
Categories: DBA Blogs

Query transformation - Remote database

Tom Kyte - Wed, 2018-03-28 01:46
Hi, I have a query that fetches data from a remote database and this data is not what i was expecting (less data or no data). I did try to understand what is causing the issue and noticed the query was being transformed while run on the remote db....
Categories: DBA Blogs

Richard Foote Consulting Seminars At Your Workplace !!

Richard Foote - Wed, 2018-03-28 00:04
If you have 6 or more people in you organisation who would be interested in attending a seminar, please contact me at richard@richardfooteconsulting.com. I currently run my highly acclaimed “Oracle Indexing Internals and Best Practices” seminar as public events, which has now been run in some 18 countries. I’m also at work on developing an […]
Categories: DBA Blogs

Automating procedure testing which returns cursors

Tom Kyte - Tue, 2018-03-27 07:26
Hi Team, In my project we have testing suite built for PL/SQL and it tests basic test cases like input/output comparision. So in the tool if I configure procedure P1 (p_in IN number, P_out OUT number) with input and and expected output, the too...
Categories: DBA Blogs

Extracting 500 Million Rows from Oracle DB -- Pro*C OR PL/SQL UTL_FILE? Deep Dive into WHY?

Tom Kyte - Tue, 2018-03-27 07:26
Hi Tom, The question is simple. I need 500 Mil rows to offload from oracle, what should I choose to to develop an application with ? PRO*C or PL/SQL UTL_FILE? and why ? I have gone through the debated topics on which approach between PRO*C o...
Categories: DBA Blogs

Extracting execution plan for PL/SQL block

Tom Kyte - Tue, 2018-03-27 07:26
<code>Hi Tom, Need help extracting execution plan for any anonymous PL/SQL block . I am able to extract execution plan for SQL using explain plan but it doesn't work for pl/sql block EXPLAIN PLAN FOR select * from dual; select *...
Categories: DBA Blogs

Difference in parallel hints.

Tom Kyte - Tue, 2018-03-27 07:26
Hi Tom, What is difference in following two scenarios? I am using parallel hints like - 1. select /*+ parallel(e,4) parallel(d,4)*/ e.eid,d.did from emp e, dept d where e.did = d.did; 2. select /*+ parallel(4)*/ e.eid,d.did from e...
Categories: DBA Blogs

customizing the SQLprompt

Tom Kyte - Tue, 2018-03-27 07:26
Hello Tom, I notice in the Oracle Magazine that you have your SQL prompt displaying "user@instance". Please tell me how you accomplish this. I can get it to display the username OR the instance name but not both. Thanks! -Lisa
Categories: DBA Blogs

Oracle Function returning multiple values

Tom Kyte - Mon, 2018-03-26 13:06
Can Oracle function return multiple values..? if yes, could you give an example for it?
Categories: DBA Blogs

How to identify database export files

Tom Kyte - Mon, 2018-03-26 13:06
Any tools/mechanisms to identify database export files taken using exp and expdp, so that i can do a scan on the desktops in my organisation to find who all have kept the export files in their desktops.
Categories: DBA Blogs

inconsistent behavior of DBMS_AQ.REGISTER process

Tom Kyte - Mon, 2018-03-26 13:06
Hi Chris/Connors, I'm facing one problem with inconsistent behavior of DBMS_AQ.REGISTER process. Followed below steps: 1) Created a queue table (multi consumer) 2) Created & Started the Queue (provided all grants) 3) Crated a call b...
Categories: DBA Blogs

The Blog Post that has had more than 55K PageViews

Hemant K Chitale - Mon, 2018-03-26 00:25
This Blog Post from 2009 has had more than 55thousand PageViews to date.

Bringing ONLINE a Datafile that is in RECOVER mode because it was OFFLINE

.
.
.
  
Categories: DBA Blogs

generate file from clob in plsql

Tom Kyte - Sun, 2018-03-25 18:46
Hi, Tom. Estoy dise?ando un peque?o pkg para encapsular el codigo de generacion de archivos .csv de interfaces de aplicacion, pero cuando se trata de un set de datos muy grande la escritura del archivo falla, dando un error ORA-29285 Mi pregunta ...
Categories: DBA Blogs

Restrict user session for each osuser

Tom Kyte - Sun, 2018-03-25 18:46
We have a database consists of 14 users and approx 60 people are using it through PLSQL developer. I would like to restrict 5 session each OSuser in PLSQL developer so that weblogic application can have sufficient surplus session. Please guide how ca...
Categories: DBA Blogs

Adding constraint with REGEXP_REPLACE fails

Tom Kyte - Sun, 2018-03-25 18:46
Hi Tom, Why is this script failing? There is no errors on running this in Oracle 12.01 and previous versions!
Categories: DBA Blogs

Is safe to use row_number() over (order by null) as a rownum alternative?

Tom Kyte - Sun, 2018-03-25 18:46
Hi, I did some testing and it seems that row_number() over (order by null) could be used as an alternative to oracle's rownum pseudocolumn. Is this behavior by design or is just a coincidence? I'm trying this because some queries are very slow...
Categories: DBA Blogs

Create a unique alpha numeric reference for a record

Tom Kyte - Sun, 2018-03-25 18:46
Hi, I am developing my first application in Oracle Apex 4.1. I am developing an application that will hold contract information. Each contract has an ID that is a primary key and I am using a sequence and trigger to create this. The end us...
Categories: DBA Blogs

How to write to SYSLOG in OGG 12.3 Classic and Microservices Architectures?

DBASolved - Sun, 2018-03-25 17:25

Oracle GoldenGate 12.3 Microservices introduced a lot of changes into how replication is to be managed moving forward. The Microservices Architecture (MA), provides a lot of opportunity for people to learn a lot about microservices and how they can be leveraged to to bring real-time replication to their organizations.

One feature that was used in the Classic Architecture (CA) was having the GGSERR.log file write items to the messages file at the system level. Starting in Oracle GoldenGate 12.3, both MA and CA, this can still be done; however, how it is done is different.

When you first look at the release notes for Oracle GoldenGate 12.3, you will notice that the SYSLOG parameter is listed under the section 2.3.2 Deprecated Features (here). It clearly states that the SYSLOG parameter has been removed and should not be used for the GLOBALS or the MGR.prm files. So, how do we enable writing to the messages file then?

After digging around a bit, you will notice that this is covered in the docs under 17.6 Using the Error Log in the Administration Guide (here). There is a lot of information here for how to enable the SYSLOG functionality and tells you what file to look at (ogg-ggserr.xml).

Now, that you know what file controls the SYSLOG option, where is this file located?

In CA, the file is located in a new directory called “diretc” and under another directroy called “logging”.

$OGG_HOME/diretc/loggin

In MA, the file is buried in the $OGG_HOME a bit, but easy to find.

$OGG_HOME/lib/utl/logging

Once you are in the file, you will notice that the file is configured by default to write to the GGSERR.log and rollover after every 10Mb in size. Just below that, you will notice that there are three commented out items. Each one of these control what you can do with the GGSERR.log. The first option will write to the GGSERR.log without rolling over. The second will allow you to write GGSERR.log info to the SYSLOG! The third will turn off all event logging. Since you will want to write to the SYSLOG, you will go with the second option. Uncomment that section (remove <!— && ! —> ).

This section will look like this:

!- The ggserr.Syslog adapter will write all messages to the local
!- system log.
!-
<appender name=”ggserr.Syslog” class=”SyslogAppender”>
<layout class=”PatternLayout”>
<param name=”Pattern” value=”%-19.19d{%Y-%m-%d %H:%M:%S} %m%n”/>
</layout>
</appender>

After uncommiting the section for writing to SYSLOG, you will need to restart the ServiceManager by using the Action button in the Deployment section of the ServiceManager HTML5 page. After the ServiceManager restarts, you will notice Oracle GoldenGate commands being written to the /var/lib/messages file.

Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-01892 Distribution path network is configured as
Mar 20 11:12:17 ogg123 ServiceManager: OS DEFAULT SPECIFIED ACTUAL VALUE
Mar 20 11:12:17 ogg123 ServiceManager: APP_FLUSHBYTES 27985 N/A 27985
Mar 20 11:12:17 ogg123 ServiceManager: APP_FLUSHSECS 1 N/A 1
Mar 20 11:12:17 ogg123 ServiceManager: IP_DSCP DEFAULT N/A DEFAULT
Mar 20 11:12:17 ogg123 ServiceManager: IP_TOS DEFAULT N/A DEFAULT
Mar 20 11:12:17 ogg123 ServiceManager: TCP_NODELAY 0 N/A 0
Mar 20 11:12:17 ogg123 ServiceManager: TCP_QUICKACK 1 1 1
Mar 20 11:12:17 ogg123 ServiceManager: TCP_CORK 0 N/A 0
Mar 20 11:12:17 ogg123 ServiceManager: SO_SNDBUF 16384 N/A 16384
Mar 20 11:12:17 ogg123 ServiceManager: TCP_CORK 0 N/A 0
Mar 20 11:12:17 ogg123 ServiceManager: SO_SNDBUF 16384 N/A 16384
Mar 20 11:12:17 ogg123 ServiceManager: SO_RCVBUF 87380 N/A 87380.
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-01052 No recovery is required for target file CB000000000, at RBA 0 (file not opened).
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-01669 Opening /opt/app/oracle/gg_deployments/pdb2/var/lib/data/CB000000000 (byte -1, current EOF 0).
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-02243 Opened trail file /opt/app/oracle/gg_deployments/pdb1/var/lib/data/CA000000 at 2018-03-20 11:12:17.126288.
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-01478 Output file CB is using format RELEASE 12.3.
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-02756 The definition for table PDB1.TEST_USER.LRG_TABLE is obtained from the trail file.

Enjoy!!!

Categories: DBA Blogs

database performance by dblink

Tom Kyte - Fri, 2018-03-23 11:46
Hi Team , we are facing issue with sql query , which is using dblink in it , most of wait event observed is sql*net message from dblink , from remote database we have figured out the plan , and it seems to good , but we are not able to get that ...
Categories: DBA Blogs

Oracle and MS SQL server data transfers

Tom Kyte - Fri, 2018-03-23 11:46
Hi Tom, Currently our company is Implementing an ERP system based on Oracle (12c - 12.2.0.1). We already have a product testing system that has data in a SQL Server database (MS SQL Server 2008 R2 64 bit - 10.50.2500.0). We need to Establish a com...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs