That’s Me

Gavin Soorma
  • Oracle Certified Professional
    • 7.3, 8i, 9i,10g, 11g
  • 11i Apps DBA OCP
    •
  • 10g RAC OCE
    •
  • Certified GoldenGate Implementation Specialist
    •
  • 10g OCM
    •
  • 11g OCM
    •

    Feedback

    1,650,000 hits

    Thanks A MILLION for your support!

    Please send me your valuable feedback and suggestions

    Useful GoldenGate commands - SHELL, OBEY, HELP, HISTORY,VERSIONS

    There are a number of other useful GoldenGate commands which we can run from the GGSCI interface.

    The GoldenGate Reference Guide has information in more detail, but I have tried out a few and found some to be very useful which I am highlighting below.

    HISTORY: To view a list of the most recently issued GGSCI commands since the GGSCI session started

    GGSCI (linux01.oncalldba.com) 4> history

    GGSCI Command History

    1: show all
    2: info all
    3: start extract ext3
    4: history

    ! : Use the ! command to execute a previous GGSCI command without modifications.

    GGSCI (linux01.oncalldba.com) 3> info extract ext3

    EXTRACT EXT3 Last Started 2010-07-21 14:29 Status RUNNING
    Checkpoint Lag 01:07:22 (updated 00:00:02 ago)
    Log Read Checkpoint Oracle Redo Logs

    Continue reading Useful GoldenGate commands – SHELL, OBEY, HELP, HISTORY,VERSIONS

    July 21st, 2010 | Tags: , , , , , | Category: GoldenGate | 5 comments

    Using DBMS_XPLAN to obtain the EXPLAIN PLAN of a SQL Statement

    Very often we run AWR, ASH and ADDM reports which does highlight the Top SQL statements by disk reads, CPU usage and elapsed time. But an important piece of information is missing which is the Explain Plan.

    Using GUI tools like Enterprise Manager will enable us to drill down to the Explain Plan from an individual SQL statement, but how do we do it from the command line?

    The answer is simply using DBMS_XPLAN.DISPLAY_AWR and provide to it as a parameter the SQL_ID in question (which can be picked up from the AWR or ASH report).

    For example in the ASH report we see this section related to the Top SQL

    Top SQL with Top Events DB/Inst: FILESDB/filesdb (Jul 19 13:23 to 13:38)

    Continue reading Using DBMS_XPLAN.DISPLAY_AWR to obtain the EXPLAIN PLAN of a SQL Statement

    July 19th, 2010 | Tags: , , | Category: Performance Tuning | Leave a comment

    GoldenGate DDL Synchronization - some more examples

    In one of my earlier posts we had introduced the GoldenGate synchronization and we had discussed what is required to set this up.

    Configuring DDL Synchronization

    Let us now look at a few more examples of DDL replication or synchronization supported by GoldenGate. We will see how the following operations on the source are replicated to the target database and will also briefly discuss how to handle any errors which may occur in the DDL replication process.

    CREATE USER
    CREATE PROCEDURE
    CREATE TABLESPACE

    This is the extract and replicat file we have used in this case

    EXTRACT ext2
    USERID ggs_owner, PASSWORD ggs_owner
    RMTHOST 192.168.10.194, MGRPORT 7809
    RMTTRAIL /home/oracle/goldengate/dirdat/gg
    DDL INCLUDE ALL
    TABLE ggs_owner.myobjects;

    REPLICAT rep2
    ASSUMETARGETDEFS
    USERID ggs_owner, PASSWORD ggs_owner
    DDL INCLUDE ALL
    DDLERROR DEFAULT IGNORE RETRYOP
    MAP ggs_owner.myobjects, TARGET ggs_owner.myobjects ;

    Let us see what happens when we do not include the DDLERROR clause in the replicat parameter file.

    So if we issue this DROP USER statement on the source, we will see that

    Continue reading GoldenGate DDL Synchronization – some more examples

    July 14th, 2010 | Tags: , , | Category: GoldenGate | 4 comments

    GoldenGate - Online Change Synchronization with the initial data load

    Recently a question was posed to me as to how do we handle changes that are happening to the data while the initial data load extract process is in operation. Sometimes it may not be possible to have an application outage just to peform an initial data load and in most cases we will need to perform the initial data load using GoldenGate while users are connected to the database and changes are being made to the database via the application.

    In my earlier tutorials we have discussed how to perform an initial data load and how to perform subsequent change synchronization to keep the data in sync. In this case we will just combine both those procedures into one.

    So broadly speaking the steps would be :

    Create the initial data load extract process or group
    Create the online change synchronization extract process or group
    Create the initial replicat group
    Create the online online change

    Continue reading GoldenGate – Online Change Synchronization with the initial data load

    July 13th, 2010 | Tags: , , , , | Category: GoldenGate | 2 comments

    GoldenGate Encrypting Data and Passwords

    GoldenGate has some security features which enables us to secure and protect the data that is being processed as well as being transported over the network.

    GoldenGate provides the following encryption options:

    1) The data stored in extract and replicat trail files
    2) Passwords used in the extract and replicat parameter files
    3) Data send over TCP/IP networks

    To encrypt trail or extract files, GoldenGate uses 256-key byte substitution. All records going into those files are encrypted both across any data links and within the files themselves.

    To encrypt the database password or data sent across TCP/IP, GoldenGate uses Blowfish encryption.

    Let us examine some of the steps involved in setting up the encryption with GoldenGate.

    Generate Encryption Keys

    Run the keygen command from the GoldenGate software installation home

    KEYGEN (key length) (n)

    Where:

    (key length) is the encryption key length, up to 128 bits.
    (n) represents the number of keys to generate.

    [oracle@linux01 goldengate]$ ./keygen 128 4

    0x0A0E5C624211E87040B50129726C0371
    0x0D44A10F0A6A05101FCE1E2003F0B405
    0x0F7AE63CD1C2222FFEE63B179373661A
    0xBB5A266A0AFF58158771E5599E5AB84C

    We will then create a text

    Continue reading GoldenGate Encrypting Data and Passwords

    July 6th, 2010 | Tags: , , , | Category: GoldenGate | Leave a comment

    GoldenGate - using FILTER, COMPUTE and SQLEXEC commands

    Some time back I had posted a note on column mapping and data transformation using GoldenGate.

    Here are some more examples of column mapping and manipulating data using keywords like SQLPREDICATE, COMPUTE, FILTER and I will also introduce another powerful GoldenGate command called SQLEXEC – which we will discuss in detail at a later date.

    SQLPREDICATE

    Enables us to provide a WHERE clause to select rows for an initial load. This will be included in the Extract parameter file as part of the TABLE clause as shown below.

    The GoldenGate reference guide has this to say ….

    “SQLPREDICATE is a better selection method for initial loads than the WHERE or FILTER options.It is much faster because it affects the SQL statement directly and does not require GoldenGate to fetch all records before filtering them, like those other options do.”

    TABLE ggs_owner.emp_details, SQLPREDICATE “where ename=’Gavin’”;

    We can also perform the filter on the Replicat side by only

    Continue reading GoldenGate – using FILTER, COMPUTE and SQLEXEC commands

    July 2nd, 2010 | Tags: , , , | Category: GoldenGate | Leave a comment