Oracle GoldenGate - Installation on Windows - Part 3

Setting up a Database User

We are in the process of performing a GoldenGate installation on Windows Vista Machine, running Oracle 11g R1 database. We have complete the steps of configuring the ORACLE_SID and ORACLE_HOME variables so far. We have also installed the VC++ runtime libraries, and copied the GoldenGate libraries.

6) The next step is to create a database user and grant the required privileges, which is inturn used by GoldenGate. Connect to the database as a DBA user and execute the following steps:-



    a) Create a tablespace which will be used by the GoldenGate user.

        create tablespace ggate
        datafile 'C:\app\<path>\ggate.dbf' size 100m

    b) Create a user in Oracle.

        create user ggate identified by ggate
        default tablespace ggate
        temporary tablespace temp

From here, we have to grant the required privileges for the user. The required privileges are documented in Oracle reference. They are listed in step 7.
7) The privileges to be granted are listed in the table below


SQL> grant create session, alter session to ggate
  2  /

Grant succeeded.

SQL> grant connect,resource to ggate
  2  /

Grant succeeded.

SQL> grant select any dictionary to ggate
  2  /

Grant succeeded.

SQL> grant flashback any table to ggate
  2  /

Grant succeeded.

SQL> grant select any table to ggate
  2  /

Grant succeeded.

SQL> grant create table to ggate
  2  /

Grant succeeded.

SQL> grant execute on dbms_flashback to ggate
  2  /

Grant succeeded.

SQL> grant execute on utl_file to ggate
  2  /

Grant succeeded.

SQL>
 

8) The next step is to confirm if we can connect to the database using the GGSCI for the user we created just now.


C:\GoldenGate>GGSCI

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Windows (optimized), Oracle 10 on Sep 18 2009 15:54:55

Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.



GGSCI (Home) 1> DBLOGIN USERID ggate,PASSWORD ggate
Successfully logged into database. => confirms that GGSCI is able to connect to the database :)

GGSCI (Home) 2>


9) For Oracle 9i and later, it is also required to enable minimal supplemental logging at the database level. To do so, power up SQLPLUS as SYSDBA user and execute the following command


SQL> alter database add supplemental log data (ALL) columns
  2  /

Database altered.

SQL>


If you are successful till this point, you may move on to the next step of the installation. Setting up GoldenGate Manager Process.

4 comments:

  1. Hi,

    I'm getting an error when trying this..any clues?

    The procedure entry point ocixmldbfreexmlctx could not be located in the dynamic link librabry oci.dll

    ReplyDelete
  2. Hi,

    While connecting i am getting the following error.


    GGSCI (Salih-lap) 6> DBLOGIN USERID gg, PASSWORD gg
    ERROR: Failed to open data source for user GG.


    Please help on this.

    Thanks,
    Salih

    ReplyDelete
  3. I used as follows and it is working.

    DBLOGIN USERID gg@conn_prod



    Salih KM

    ReplyDelete