Tips and wisdom from 15 years of DBA experience

Wednesday, February 10, 2010

Clear Space in the Flash Recovery Areas

A common problem related to the Flash Recovery Area is "ORA-19809: limit exceeded for recovery files~ORA-19804: cannot reclaim 44040192 bytes disk space from 429496729600 limit". If you can confirm that that error is coming from the flash recovery area, you can simply issue the following:

SQL> show parameter db_recovery_file_dest_size

NAME TYPE VALUE
------------------------------ ------------- -------
db_recovery_file_dest_size big integer 400G

SQL> alter system set db_recovery_file_dest_size=200G

SQL> alter system set db_recovery_file_dest_size=400G

Simply setting the recovery dest file size to a smaller value temporarily will remove the oldest files...of course you will lose some of the history you can flashback to, so be aware of this.

Wednesday, February 3, 2010

Streams vs. Golden Gate

In late 2009, Oracle purchased Golden Gate software whose technology is now the stated platform that Oracle will build upon for all new replication features. Streams will still be supported, but the new goodies and major focus will be on Golden Gate. My initial experiences with Golden Gate have been very positive, and for those of you who have ever used streams, you will be excited to learn that there is a single command line interface to do *everything*, and transformations are far, far simpler. Here is the breakdown:

Setup

Streams:
Set up database links, hope that your global names are set properly, use dbms_streams_adm.maintain_schemas(), or if you are really a masochist, build your capture & apply queues, and set up propagation, then spend a couple hours digging through V$ views to find out why things aren't working.

Golden Gate:
untar/unzip the software on both source and target to a NON-nfs mounted volume - I had problems using gg when the connection to the nfs server was slow, & gg does not support this config. Who installs binaries on nfs mounted volumes anyway? Duh.

type ./ggsci
> info all -- (gives you everything you need to know about what is running)

I found the easiest config was to walk through the steps in the install guide, then use the first example to create a reporting system in the admin guide. It's so simple and straightforward.

Transformations

Streams:
Create dml handlers or apply handlers, and have fun debugging them looking for errors in v$applyblahblah, v$captureblahblah...

Golden Gate:
Add a line like this to your source to only pull rows where col1 is greater than 1000:
TABLE schema1.table1, FILTER(col1 > 1000);
Done.
Other transformations are equally simple. Exclude a column?
TABLE schema1.table1, EXCCOL (col2);

...send data from one schema to another?
MAP schema1.*, TARGET schema2.*;

...map a column from one table to another?
MAP schema1.tab1, TARGET schema1.tab2, COLMAP (usedefaults, tab1_col=tab2_col)

My experience with GG proved that GG is faster than STREAMS even when using real-time downstream capture...oh, that's another thing - ever struggled setting up redo transport services for real time downstream capture in streams, yeah...none of that with GG. It does not use any Oracle-specific mechanism to transport the data. There is a manager process on each server involved in the replication that listens on a user defined port (default 7809). The extract process reads from the online redo log or archive log if necessary and sends what are called trail files over to the target. Those trail files are then read by the replicat process and applied on the target. Overall, GG looks very promising.

For more information on Golden Gate, see the following:
http://cglendenningoracle.blogspot.com/2010/11/transform-data-in-oracle-golden-gate.html

Followers