DBA Blogs

How to set all elements of collections

Tom Kyte - Mon, 2016-07-18 01:26
I've three questions 1) Suppose I have a collection of numbers say 300 values & i want to initialise with number 72 what would be the best way to do so ,rather than writing 300 times 2) I've to generate reports in pdf so what would you sugg...
Categories: DBA Blogs

Will open cursor hold up more tablespace when it is not closed?

Tom Kyte - Mon, 2016-07-18 01:26
Hi Tom, I am using oracle 11g and the tablespaces keeps growing, I have recently identified a issue with open cursors which is not closed when the session was closed. Will open cursor eats up all the spaces which leads to consume more temp spa...
Categories: DBA Blogs

ORA-01578: ORACLE data block corrupted (file # , block # )

Tom Kyte - Mon, 2016-07-18 01:26
Hi Tom, I m getting following error in my production data base alert log since last 5 month. Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_j000_26775.trc: ORA-01578: ORACLE data block corrupted (file # , block # ) ORA-01578: O...
Categories: DBA Blogs

ORA - 08103:Object No Longer Exists

Tom Kyte - Mon, 2016-07-18 01:26
Hi, I am getting an error Ora-08103: Object No longer exists when select queries are fired against a partitioned table having local Bit map indexes. But when the same process/job (which fires select query) is restarted the issue does not come up a...
Categories: DBA Blogs

Why the "Row Source Operation" ('reality exec') differs from the "Execution Plan" ('guess exec')

Tom Kyte - Sun, 2016-07-17 07:06
Hi, Team There's a procedure in our Oracle 11.2.0.1 DB which captures data from MS SQL Server through DB Link, since the context is quite long (cursors, pragma autonomous_transaction and loop included), let us take it brief like this: CREATE OR...
Categories: DBA Blogs

XML Update

Tom Kyte - Sun, 2016-07-17 07:06
Hi Tom, I need to update some attribute(XML) in oracle table whose datatype is XML. Is there any package provided by oracle which help me? Or if you can tell me anyway how can I update it.
Categories: DBA Blogs

clone(duplicate active Database)

Tom Kyte - Sun, 2016-07-17 07:06
I Want to clone a database from one computer to another computer, both computer connected with lan. The database which i have to clone is available on computer 2. on computer 1 i have created an instance using oradim utility On computer 2 <code>...
Categories: DBA Blogs

Add_months

Tom Kyte - Sun, 2016-07-17 07:06
Hi Guys, I am just a little bit confused regarding the following : select add_months(to_date('30/01/2016','DD/MM/YYYY'),1) from dual Result is : 29/02/2016, shouldn't it be 28/02/2016 ?? Thanks Mohannad
Categories: DBA Blogs

issue while adding new apps node in oracle EBS r12.2.5

Tom Kyte - Sun, 2016-07-17 07:06
Hi DBA Experts, I am facing following issue while adding new application node please help me to fix this issue. Executing command: perl /u02/applmgr/SR1225/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/adProvisionEBS.pl ebs-create-node -contextfi...
Categories: DBA Blogs

export to CSV

Tom Kyte - Sun, 2016-07-17 07:06
Tom, I need to export data from a table into a .csv file. I need to have my column headres in between "" and data separated by ','. also depending on the column values a row may be printed upto 5 times with data differing in only one field. ...
Categories: DBA Blogs

Links for 2016-07-16 [del.icio.us]

Categories: DBA Blogs

Finding transacted tables,

Tom Kyte - Sat, 2016-07-16 12:46
Hello, Using Oracle data dictionary, how to find out the list of tables that have undergone Insert/Update/Delete by a particular user account in the last 7 days? Also, if possible I want to know the number of transactions happened and the size of...
Categories: DBA Blogs

What is best way to collect GLOBAL STATS of a table with 4 billion records

Tom Kyte - Sat, 2016-07-16 12:46
Hi Tom I need to take global stats collection for a table with 4 billion records. This is a partitioned table but I need to collect STATS globally. This table was last analyzed in Sept 2015. As of now following is Table statistics Actual Number...
Categories: DBA Blogs

DENSE_RANK function

Tom Kyte - Sat, 2016-07-16 12:46
Hi Tom, I have a problem with DENSE_RANK function. Let's see an example: <code>CREATE TABLE test_rank (val number); INSERT INTO test_rank VALUES(1); INSERT INTO test_rank VALUES(2); INSERT INTO test_rank VALUES(3); INSERT INTO test_rank VALUE...
Categories: DBA Blogs

DROP TABLE performance/delay

Tom Kyte - Sat, 2016-07-16 12:46
Hello Tom, We have a new application which has serious performance problems, and a reason for that might be the Oracle DB. Unfortunately, tests could not find the root cause yet. Now I saw a very strange behavior of the DROP TABLE performance: ...
Categories: DBA Blogs

count of distinct on multiple columns does not work

Tom Kyte - Sat, 2016-07-16 12:46
Hi, I am trying to count the number of distinct combinations in a table but the query gives error. For example, create table t(a varchar2(10), b varchar2(10), c varchar2(10)); insert into t values('a','b','c'); insert into t values('d','e'...
Categories: DBA Blogs

Creating and Executing a stored procedure that dynamically builds a table getting ORA-06550: PLS-00103

Tom Kyte - Sat, 2016-07-16 12:46
Receiving ORA-06550: PLS-00103 error when trying to execute a procedure that dynamically creates a table. I have created a pl/sql script to dynamically create a table: declare l_tablename varchar2(30) := 'TEST_3_'||to_char(sysdate, 'YYYYMMDD')...
Categories: DBA Blogs

Average of input two Dates

Tom Kyte - Fri, 2016-07-15 18:26
How Can i create a oracle sql Function which can take two input Dates and Display their Average.For Ex:- input :-28-Aug-2016,4-Sep-2016 output:- 1-Sep-2016
Categories: DBA Blogs

Constraints

Tom Kyte - Fri, 2016-07-15 18:26
<code>Tom, Are the following a full list of all possible user constraint_types P - primary key? C - check? R - foreign key? U - unique ? And what are their full meanings? Are the dab constraint_types the same? Thanks Brian </code>
Categories: DBA Blogs

Truncates and Sequences Replication in Oracle GoldenGate

Pythian Group - Fri, 2016-07-15 11:29

We use the terms DDL and DML in our DBA life with Oracle all the time. The first stands for Data Definition Language and it is about Data Definition changes. The latter is about manipulation with your data itself, and stands for Data Manipulation Language. When we speak about replication of our data by replication tools between Oracle databases, we generally either enable DDL, work only replicating DML, or do it for both together. In general, I would recommend replicating both DML and DDL just in case, to prevent the replication to be broken in case of unexpected structural changes in the replicated schemas. But in some cases you do not want to replicate all DDL or any DDL at all for certain reasons. I will discuss a couple of operations which are handled slightly different from pure DDL/DML changes in GoldenGate.

The first of them is truncate operation. In Oracle it is definitely DDL and you can see that.

orcl&gt; select object_id,data_object_id,last_ddl_time from dba_objects where object_name='EMP_TEST';

       OBJECT_ID   DATA_OBJECT_ID LAST_DDL_TIME
---------------- ---------------- -----------------
	  148769	   148769 06/24/16 16:07:04

orcl&gt; truncate table emp_test;

Table truncated.

orcl&gt; select object_id,data_object_id,last_ddl_time from dba_objects where object_name='EMP_TEST';

       OBJECT_ID   DATA_OBJECT_ID LAST_DDL_TIME
---------------- ---------------- -----------------
	  148769	   148770 06/24/16 16:15:52

orcl&gt;

It is clear that the object gets the new data_object_id and last_ddl_time shows new time.

There is a general assumption that you need to enable a DDL support to replicate truncates. But is this true? In reality you can replicate truncates (with some limitations) without full DDL support, and I want to show you how it can be done. What you need to do is setup a parameter GETTRUNCATES.
Let’s setup it on extract and see how it works.

Here is my extract parameter file:

[oracle@bigdatalite oggora]$ cat dirprm/trext.prm
extract trext
userid ogg, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
--RMTHOSTOPTIONS
RMTHOST bigdatalite, MGRPORT 7849
RMTTRAIL ./dirdat/tr, format release 11.2
GETTRUNCATES
TABLE trsource.*;

We don’t have DDL support and if we try to add a column on the source and put a value to that column our replicat on other side will be abended.

orcl&gt; alter table trsource.emp add col1 varchar2(10) ;

Table altered.

orcl&gt; update trsource.emp set col1='Test1' where empno=7499;

1 row updated.

orcl&gt; commit;

And on the target side:

2016-06-27 13:51:47  INFO    OGG-01021  Oracle GoldenGate Delivery for Oracle, trrep.prm:  Command received from GGSCI: STATS.
2016-06-27 13:57:37  ERROR   OGG-01161  Oracle GoldenGate Delivery for Oracle, trrep.prm:  Bad column index (8) specified for table TRSOURCE.EMP, max columns = 8.
2016-06-27 13:57:37  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, trrep.prm:  PROCESS ABENDING.

You are going to get similar errors for other DDL operations but not for truncates.

orcl&gt; truncate table trsource.emp;

Table truncated.

orcl&gt;
GGSCI (bigdatalite.localdomain) 1&gt; send trext, stats

Sending STATS request to EXTRACT TREXT ...

Start of Statistics at 2016-06-27 14:05:24.

Output to ./dirdat/tr:

Extracting from TRSOURCE.EMP to TRSOURCE.EMP:

*** Total statistics since 2016-06-27 14:05:07 ***
	Total inserts                   	           0.00
	Total updates                   	           0.00
	Total deletes                   	           0.00
	Total truncates                 	           1.00
	Total discards                  	           0.00
	Total operations                	           1.00

You can see that we have captured the truncate by our extract. Even our DDL support is disabled. What we need is to set up the same parameter GETTRUNCATES on replicat side. Why do we need to set it up explicitly? Because the default behaviour and parameter for GoldenGate is “IGNORETRUNCATES” for all processes. As result, the truncates will be applied to the target system.

We are setting our parameter on replicat side and see the result:

[oracle@bigdatalite ogg11ora]$ cat dirprm/trrep.prm
replicat trrep
--trace
DISCARDFILE ./dirdsc/discard.out, append
userid ogg@test, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
assumetargetdefs
--DDL include all
GETTRUNCATES
map trsource.emp, target trdest.emp;
[oracle@bigdatalite ogg11ora]$


GGSCI (bigdatalite.localdomain) 4&gt; send trrep, stats

Sending STATS request to REPLICAT TRREP ...

Start of Statistics at 2016-06-27 14:08:40.

Replicating from TRSOURCE.EMP to TRDEST.EMP:

*** Total statistics since 2016-06-27 14:08:25 ***
	Total inserts                   	           0.00
	Total updates                   	           0.00
	Total deletes                   	           0.00
	Total truncates                 	           1.00
	Total discards                  	           0.00
	Total operations                	           1.00

test&gt; select count(*) from trdest.emp;

	COUNT(*)
----------------
	       0

test&gt;

We don’t need full DDL support if we want to replicate truncates only. Sometimes it may help us when we have workflow including truncates, but we don’t want to replicate all DDL commands for some reasons. Just keep in mind that it works with some limitations. You cannot replicate by using “truncate partition” for Oracle. It will require full DDL support.

The second thing I want to discuss in this topic is support for sequences values replication. Sometimes people assume that it requires DDL support, but this is not true. As matter of fact replicating of sequences values doesn’t require you to enable DDL support for your replication. Of course, you need full DDL replication support to replicate CREATE, ALTER, DROP, RENAME for sequences, but the values are replicated as DML.

To enable the replication of sequences you need to create a special user on source and target databases, add the user to the GGSCHEMA parameter to your .GLOBALS file, and run one script to create all necessary procedures in the newly created schema.
Let’s have a closer look. I have a user OGG I am using for connection and I plan to use the same user for sequence support.

Here is my .GLOBALS file:

[oracle@bigdatalite oggora]$ cat GLOBALS
GGSCHEMA OGG

The same I have on the target side:

[oracle@bigdatalite ogg11ora]$ cat GLOBALS
GGSCHEMA OGG

I ran the script sequence.sql on both sides.

orcl&gt; @sequence.sql
Please enter the name of a schema for the GoldenGate database objects:
ogg
Setting schema name to OGG

UPDATE_SEQUENCE STATUS:

Line/pos				 Error
---------------------------------------- -----------------------------------------------------------------
No errors				 No errors

GETSEQFLUSH

Line/pos				 Error
---------------------------------------- -----------------------------------------------------------------
No errors				 No errors

SEQTRACE

Line/pos				 Error
---------------------------------------- -----------------------------------------------------------------
No errors				 No errors

REPLICATE_SEQUENCE STATUS:

Line/pos				 Error
---------------------------------------- -----------------------------------------------------------------
No errors				 No errors

STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support
orcl&gt;

And on the source side add primary key supplemental logging to the sys.seq$ table:

orcl&gt; ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Table altered.

orcl&gt;

You may have a look to the procedures created by the scripts:
SEQTRACE
GETSEQFLUSH
REPLICATESEQUENCE
UPDATESEQUENCE

These procedures enable interface to flush, update and replicate the sequences.

Now we are creating a sequence on the source and target with the same parameters.

orcl&gt; create sequence trsource.empno_seq start with 8100;

Sequence created.

orcl&gt;

Adding parameter SEQUENCE to our parameter file for extract:

[oracle@bigdatalite oggora]$ cat dirprm/trext.prm
extract trext
userid ogg, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
--RMTHOSTOPTIONS
RMTHOST bigdatalite, MGRPORT 7849
RMTTRAIL ./dirdat/tr, format release 11.2
--DDL include objname trsource.*
GETTRUNCATES
SEQUENCE tsource.*;
TABLE trsource.*;

[oracle@bigdatalite oggora]$

On the target we are creating the same sequence:

test&gt; create sequence trdest.empno_seq start with 8100;

Sequence created.

test&gt;
[oracle@bigdatalite ogg11ora]$ cat dirprm/trrep.prm
replicat trrep
--trace
DISCARDFILE ./dirdsc/discard.out, append
userid ogg@test, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
assumetargetdefs
--DDL include all
GETTRUNCATES
map trsource., target trdest.;

I made our sequences a bit different on purpose. Our source sequence had a slightly bigger current value than target:

orcl&gt; select trsource.empno_seq.currval from dual;

	 CURRVAL
----------------
	    8102

orcl&gt;


test&gt; select trdest.empno_seq.currval from dual;

	 CURRVAL
----------------
	    8100

test&gt;

What we need is to run command FLUSH SEQUENCE on our extract side:

GGSCI (bigdatalite.localdomain) 9&gt; dblogin userid ogg@orcl,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
Successfully logged into database.

GGSCI (bigdatalite.localdomain as ogg@orcl) 3&gt; flush sequence trsource.empno_seq
Successfully flushed 1 sequence(s) trsource.empno_seq.

GGSCI (bigdatalite.localdomain as ogg@orcl) 4&gt;

And on target we can see:

test&gt; select * from dba_sequences where SEQUENCE_NAME='EMPNO_SEQ';

SEQUENCE_OWNER		       SEQUENCE_NAME			     MIN_VALUE	      MAX_VALUE     INCREMENT_BY C O	   CACHE_SIZE	   LAST_NUMBER
------------------------------ ------------------------------ ---------------- ---------------- ---------------- - - ---------------- ----------------
TRDEST			       EMPNO_SEQ				     1 9999999999999999 	       1 N N		   20		  8143

test&gt; select last_number from dba_sequences where SEQUENCE_NAME='EMPNO_SEQ';

     LAST_NUMBER
----------------
	    8143

test&gt;

The last number for the sequence on the target has been increased to 8143 when on the source we have only cache was flushed and we got 8123 as a last number for the sequence:

orcl&gt; select last_number from dba_sequences where SEQUENCE_NAME='EMPNO_SEQ';

     LAST_NUMBER
----------------
	    8123

orcl&gt;

Let’s try to get new values for the sequence.

orcl&gt; select trsource.empno_seq.nextval from dual;

	 NEXTVAL
----------------
	    8104

orcl&gt; select trsource.empno_seq.nextval from dual;

	 NEXTVAL
----------------
	    8105

We continue to increase values on the source, and as soon as we crossed max number for the source (8123) we got new value on the target:

orcl&gt; select trsource.empno_seq.nextval from dual;

	 NEXTVAL
----------------
	    8119

........
	 NEXTVAL
----------------
	    8124

orcl&gt;


test&gt; select last_number from dba_sequences where SEQUENCE_NAME='EMPNO_SEQ';

     LAST_NUMBER
----------------
	    8144

test&gt;

And the statistics on the target will be shown as updates:

GGSCI (bigdatalite.localdomain) 1&gt; send trrep, stats

Sending STATS request to REPLICAT TRREP ...

Start of Statistics at 2016-06-29 13:20:36.

Replicating from TRSOURCE.EMPNO_SEQ to TRDEST.EMPNO_SEQ:

*** Total statistics since 2016-06-29 13:10:52 ***
	Total updates                   	           4.00
	Total discards                  	           0.00
	Total operations                	           4.00


We can see that the two operations are a bit different from all other standard DDL and DML in Oracle GoldenGate. I hope this small piece of information may help you in your implementation, or help to support your GoldenGate environment.

Stay tuned and keep your eyes on Pythian blog.

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs