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.