Oracle Notes

Sharing Oracle technology knowledge and experiences.

Archive for the ‘Uncategorized’ Category

Log Buffer #198, A Carnival of the Vanities for DBAs

Posted by sdefilip on August 6, 2010

Welcome to Log Buffer, the weekly roundup of database industry news.  I’m guest hosting this week’s edition, Log Buffer #198[S D1] [S2] , normally published by Pythian.  If you want to host your own version of Log Buffer, reach out to the Log Buffer coordinator.

Lots of good “stuff” out there on the technical blogs this week so here are some interesting ones:

In “Kerry Osborne’s Oracle Blog”, Kerry discusses the use of SQL Profiles

which contain sets of hints.

The memory_target parameter, how it behaves and how it relates to other memory structures, is the topic of Steve Karam’s on his “Oracle Alchemist” blog.

On “Mark Rittman’s Oracle Weblog”, Mark Rittman informs us how we can now add incremental patches to an RPD within OBIEE 11GgR1, through the use of XML patch files.

Scalability and the Oracle conflicts of interest that can be brought about by managing it are illustrated by

Jonathan Lewis on Oracle Scratchpad.

Tanel Poder offers his article on Troubleshooting Exadata V2 Smart Scan Performance in his “Core IT for Geeks and Pros” blog.

In a series of posts on the topic of the SQL Server 2008 Optimiser: Constructing a Plan, Part 1 & Part 2, on the “SQL Server Blogspot on the Web”, Paul White provides detail on how to get SQL code and queries to perform at their best.

Simon Sabin discusses Creating a Unique Constraint on a Column That Allows Nulls on “Simon’s SQL Blog”.

Distributed Transactions and Two-Phase Commit in PostgreSQL is the subject of Joshua Tolley’s informative post on “The Endpoint” blog.

Dimitri Fontaine talks about Querying the Catalog to plan an upgrade, on Planet PostgreSQL.  In more news, PostgreSQL 9.0 beta 4 is out and ready for testing.

On the “MySQL Performance Blog”,  Baron Shwartz cautions Why You Can’t Rely on a Replica for Disaster Recovery.


[S D1]http://wp.me/pYWgn-1o

Posted in Tips, Uncategorized | 1 Comment »

Oracle GoldenGate – Positioning a Read of Extract/Replicat Trail File or Oracle Redo Log

Posted by sdefilip on July 26, 2010

Positioning in Extract / Replicat Trail and Log

In the event that there is ever a need to position an extract in the
Transaction Log (aka redo log), extract trail file or replicat trail file,
the following options can be used:

INFO EXTRACT <groupname>,  DETAIL

This will name your current redo log along with the RBA and sequence number and your extract trail name along with the RBA. RBA is the relative byte address of the record in the trail file at which the checkpoint was made

INFO EXTRACT, SHOWCH

Will show you your read checkpoint in the data source and write
checkpoint in the trail files.

Log Read Checkpoint File /orarac/oradata/racq/redo01.log  ß- Oracle redo
2006-06-09 14:16:45 Thread 1, Seqno 47, RBA 68748800          info
Log Read Checkpoint File /orarac/oradata/racq/redo04.log
2006-06-09 14:16:19 Thread 2, Seqno 24, RBA 65657408
Current Checkpoint Detail:
Read Checkpoint #1
Oracle RAC Redo Log
Startup Checkpoint (starting position in data source):
Sequence #: 47
RBA: 68548112     ß– RBA offset of entry in redo log
Timestamp: 2006-06-09 13:37:51.000000
SCN: 0.8439720
Redo File: /orarac/oradata/racq/redo01.log
Recovery Checkpoint (position of oldest unprocessed transaction in
data source):
Sequence #: 47
RBA: 68748304
Timestamp: 2006-06-09 14:16:45.000000
SCN: 0.8440969
Redo File: /orarac/oradata/racq/redo01.log
Current Checkpoint (position of last record read in
the data source)

Write Checkpoint #1
GGS Log Trail       ß– start of GG Trail Information
Current Checkpoint (current write position):
Sequence #: 2
RBA: 2142224       ß–  RBA offset of entry in Trail file
Timestamp: 2006-06-09 14:16:50.567638
Extract Trail: ./dirdat/eh
Header:
Version = 2
Record Source = A
Type = 6
# Input Checkpoints = 2
# Output Checkpoints = 1

Once you have your checkpoint RBAs, you can use a few ggsci
commands to help you debug:

ADD EXTRACT <groupname>

Allows creation of an extract from a specific position in a trail file or
transaction log.
Some useful options are:

EXTTRAILSOURCE <trail name>
Specifies a trail as the data source. For <trail name>, specify the
fully qualified path name  of the trail, for example c:\ggs\dirdat\aa.

BEGIN <begin spec>
Specifies a timestamp in the data source at which to begin
processing.
<begin spec>  value is either:
□  NOW    –  the time at which the ADD EXTRACT command
is issued.
□   A date and time in the format of:
yyyy-mm-dd [hh:mi:[ss[.cccccc]]]

*** if you have a 4-node RAC cluster environment, use the
“THREADS 4”  option to any command to which it applies.

EXTRBA <relative byte address>
Specifies an RBA at which to start extracting.  This can be used to
skip over a bad entry in a trail file.

ALTER EXTRACT <group name>

Allows changing the attributes of an extract file created by the ADD EXTRACT
command and allows the incrementing of an extract to the  next file in the sequence.

***Always  “STOP EXTRACT <group name>”  before using this command.

You can use this command to make any changes using any of the options to the ADD EXTRACT command (above).  So, for example, you can ALTER EXTRACT an extract file to begin at a specific RBA for skipping over an entry in the extract file.
Ex.:   ALTER EXTRACT finance, EXTSEQNO 26, EXTRBA 338

You can change any of the attributes specified with the ADD EXTRACT command, except for the following:
□ Altering an Extract specified with the EXTTRAILSOURCE
option.
□ Altering the number of RAC threads specified with the THREADS
option.

SEND EXTRACT

Allows you to send commands to a running extract process.
Some useful options are:

GETLAG shows lag time between the extract and the data source

LOGEND shows whether or not extract has processed all record in
the data source

ROLLOVER makes extract increment to the next file in the trail upon
startup

SHOWTRANS shows information about current transactions:
□   Process Checkpoint
□   Transaction ID
□    Extract Group Name
□    Redo Thread Number
□    Timestamp of first transaction of extract
□     System Change Number  (SCN)
□     RBA and Redo Log Number
□     STATUS ( commit after it has been forced by FORCETRANS
(Pending Commit) or “running”)

Posted in CDC, Data Integration, Data Warehouse, Golden Gate, Oracle GoldenGate, Replication, Tips, Uncategorized | 2 Comments »

Posted by sdefilip on July 19, 2010

Follow my post from Technorati .

ZEUTUR46YJR3

Posted in Uncategorized | Leave a Comment »

SQL Injection ?

Posted by sdefilip on December 30, 2009

Ever heard of SQL Injection? Sounds like something medical but unfortunately not as benign as that.

Apparently it is a method used by hackers to redirect a website and mess with our data. I just read an interesting article about this on the eWeek Security Watch site and thought I’d share. Here’s a link to the article:

http://securitywatch.eweek.com/sql_injection/an_unpleasant_anniversary_eleven_years_of_sql_injection.html?kc=EWKNLDAT12242009STR2

Posted in Uncategorized | 1 Comment »