Thursday, February 03, 2011

Oracle ASM for Oracle GoldenGate extract



Oracle GoldenGate supports data capture from an Oracle Database using ASM. A few additional setup steps are required in order to use ASM for Oracle redo and/or archive logs:

Extract requires a connection into an ASM instance to be able to read the transaction logs. The connection has to go through the Oracle Database listener and because the ASM instance is only mounted (not open) an entry for the ASM instance must be added to the listener configuration file in order to let incoming connections go through.
See the Oracle GoldenGate for Windows and Unix Administrator Guide as well as Oracle Support note 340277.1 for more details.

The solution is to
1.    Edit the listener.ora on ASM server  
2.    Edit the tnsnames.ora on the client
3.    Setup your remote login password for your ASM instance on the ASM server
4.    Set your SYS password using ORAPWD for the ASM instance
5.    Use the properly formatted connect string to connect with your tool

1) Edit the listener.ora on ASM server
a) Logon to the ASM/Database server
b) Locate your listener.ora (typically located in the $ORACLE_HOME/network/admin)
c) Add a SID_LIST_LISTENER entry for your ASM instance (see example below)

SID_LIST_LISTENER_RAC1 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ASM)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (SID_NAME = +ASM1)
    )
  )

  d) Stop the listener

lsnrctl stop
  
e) restart the listener

lsnrctl start

2) Edit the tnsnames.ora on the client

   a) Logon to the client machine that will be used to connect to the ASM instance
          NOTE: the client machine can also be the ASM/Database server (ie for DBCONTROL)
   b) Locate your tnsnames.ora (typically in the $ORACLE_HOME/network/admin)
   c) Add an entry (tnsalias) for your ASM instance (see example)

EXAMPLE
ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM1)
    )
  )

3) Setup your remote login password for your ASM instance on the ASM server
   a) Logon to the ASM/Database server
   b) Locate the parameter file for your ASM instance (typically
$ORACLE_HOME/dbs/init+ASM.ora [unix] or $ORACLE_HOME/dbs/init+ASM.ora [windows] )
   c) Edit the parameter file and add

remote_login_passwordfile = exclusive ... for stand alone ASM setups
remote_login_passwordfile = shared ... for ASM setups that also use Real Application Cluster (RAC)
 
   d) Save the file

4) Set your SYS password using ORAPWD for the ASM instance

   a) Logon to the ASM/Database server
   b) Locate your orapw file for your ASM instance (typically $ORACLE_HOME/dbs/orapw+ASM)
   c) Rename the file to orapw.old
   d) Run orapwd to reset the password (see example below)

EXAMPLE

mv "orapw+ASM1" "orapw+ASM.old"
orapwd file=orapw+ASM1 password=DBA******

5) Use the properly formatted connect string for your tool
Connnect to the ASM instance using the password (#4 above) and the tnsalias (#2 above)

EXAMPLES

sqlplus "sys/******@asm as sysdba"
sqlplus "sys@asm as sysdba" ... then supply the password when prompted

1 comment:

himanshu singh said...

Hi,
Thanks for the info, I have setup the configuration as described above, but still I am not able to connect from golden gate.
Whereas from database node I am able to connect to ASM. The ggate user has all the privilege. The same issue is coming from sys user as well.

sqlplus ggate@ASM as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 22 21:36:02 2015

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL> exit

Eroor in Golden gate extract process.
OCI Error beginning session (status = 15000-ORA-15000: command disallowed by current
instance type).

Thanks in advance,
Himanshu