Friday, August 8, 2014

Using 11gr2 Clusterware for Providing HA for Single Instance Database

We can use the Oracle cluster ware for providing the High availability instead of using the
third party cluster-ware.

In this practical session we can see how we are use the 11gr2 cluster ware can be used
to to provide the HA for a single instance database.

OS    - OEL 6.5
Cluster-ware - 11.2.0.3

Below is the status of the fresh installed clusterware.


[oracle@oelrac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADG.dg
               ONLINE  ONLINE       oelrac1
               ONLINE  ONLINE       oelrac2
ora.FRA.dg
               ONLINE  ONLINE       oelrac1
               ONLINE  ONLINE       oelrac2
ora.LISTENER.lsnr
               ONLINE  ONLINE       oelrac1
               ONLINE  ONLINE       oelrac2
ora.OCR_MIRROR.dg
               ONLINE  ONLINE       oelrac1
               ONLINE  ONLINE       oelrac2
ora.OCR_VOTING.dg
               ONLINE  ONLINE       oelrac1
               ONLINE  ONLINE       oelrac2
ora.asm
               ONLINE  ONLINE       oelrac1                  Started
               ONLINE  ONLINE       oelrac2                  Started
ora.gsd
               OFFLINE OFFLINE      oelrac1
               OFFLINE OFFLINE      oelrac2
ora.net1.network
               ONLINE  ONLINE       oelrac1
               ONLINE  ONLINE       oelrac2
ora.ons
               ONLINE  ONLINE       oelrac1
               ONLINE  ONLINE       oelrac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       oelrac2
ora.cvu
      1        ONLINE  ONLINE       oelrac2
ora.oc4j
      1        ONLINE  ONLINE       oelrac2
ora.oelrac1.vip
      1        ONLINE  ONLINE       oelrac1
ora.oelrac2.vip
      1        ONLINE  ONLINE       oelrac2
ora.scan1.vip
      1        ONLINE  ONLINE       oelrac2



1) Install Standalone oracle home on both the nodes.
2) Create the database using dbca named testdb

--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------

ora.testdb.db
      1        ONLINE  ONLINE       oelrac1                  Open


3) Take the configuration properties of the database and remove it from the cluster resource.

[oracle@oelrac1 trace]$ crsctl stat res ora.testdb.db -p > /home/oracle/test_db_properties.txt


[oracle@oelrac1 trace]$ cat /home/oracle/test_db_properties.txt
NAME=ora.testdb.db
TYPE=ora.database.type
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=1
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=1
CHECK_TIMEOUT=30
CLUSTER_DATABASE=false
DATABASE_TYPE=SINGLE
DB_UNIQUE_NAME=testdb
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=database) PROPERTY(DB_UNIQUE_NAME= CONCAT(PARSE(%NAME%, ., 2), %USR_ORA_DOMAIN%, .)) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%) ELEMENT(DATABASE_TYPE= %DATABASE_TYPE%)
DEGREE=1
DESCRIPTION=Oracle Database resource
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=60
FAILURE_THRESHOLD=1
GEN_AUDIT_FILE_DEST=/u01/app/oracle/admin/testdb/adump
GEN_START_OPTIONS=
GEN_START_OPTIONS@SERVERNAME(oelrac1)=open
GEN_USR_ORA_INST_NAME=
GEN_USR_ORA_INST_NAME@SERVERNAME(oelrac1)=testdb
HOSTING_MEMBERS=
INSTANCE_FAILOVER=1
LOAD=1
LOGGING_LEVEL=1
MANAGEMENT_POLICY=AUTOMATIC
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
ONLINE_RELOCATION_TIMEOUT=0
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
ORACLE_HOME_OLD=
PLACEMENT=restricted
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=2
ROLE=PRIMARY
SCRIPT_TIMEOUT=60
SERVER_POOLS=ora.testdb
SPFILE=+DATADG/testdb/spfiletestdb.ora
START_DEPENDENCIES=hard(ora.DATADG.dg) weak(type:ora.listener.type,uniform:ora.ons) pullup(ora.DATADG.dg)
START_TIMEOUT=600
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATADG.dg)
STOP_TIMEOUT=600
TYPE_VERSION=3.2
UPTIME_THRESHOLD=1h
USR_ORA_DB_NAME=testdb
USR_ORA_DOMAIN=
USR_ORA_ENV=
USR_ORA_FLAGS=
USR_ORA_INST_NAME=testdb
USR_ORA_OPEN_MODE=open
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.3.0


[oracle@oelrac1 dbs]$ srvctl status database -d testdb
Instance testdb is running on node oelrac1

[oracle@oelrac1 dbs]$ srvctl stop database -d testdb

[oracle@oelrac1 dbs]$ srvctl remove database -d testdb
Remove the database testdb? (y/[n]) y

Now the db resource will not get listed in crs resource. (crsctl stat res -t).


4) Copy the pfile and password file to the 2nd node. Create adump directory in 2nd node.

[oracle@oelrac1 dbs]$ scp inittestdb.ora oracle@oelrac2:/u01/app/oracle/product/11.2.0/db/dbs/
inittestdb.ora                                                                                                                        100%   41     0.0KB/s   00:00

[oracle@oelrac1 dbs]$ scp orapwtestdb oracle@oelrac2:/u01/app/oracle/product/11.2.0/db/dbs/
orapwtestdb  


[oracle@oelrac2 ~]$ mkdir -p /u01/app/oracle/admin/testdb/adump



5) Test the database startup in node 2.


[oracle@oelrac2 ~]$ export ORACLE_SID=testdb
[oracle@oelrac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 10:35:59 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2230952 bytes
Variable Size             381683032 bytes
Database Buffers          239075328 bytes
Redo Buffers                3338240 bytes
Database mounted.
Database opened.


-- Database has opened in node 2 without issues.

Register the database with local listeners, since the local listeners are listening on the
vip there is no need to setup a seperate vip for this database. We can use the existing
vip and scan vips for this setup.


[oracle@oelrac2 ~]$ srvctl config scan
SCAN name: oelrac-scan.manzoor.com, Network: 1/192.168.0.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /oelrac-scan.manzoor.com/192.168.0.44

[oracle@oelrac2 ~]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

[oracle@oelrac2 ~]$ srvctl config vip -n oelrac1
VIP exists: /oelrac1-vip/192.168.0.42/192.168.0.0/255.255.255.0/eth0, hosting node oelrac1

[oracle@oelrac2 ~]$ srvctl config vip -n oelrac2
VIP exists: /oelrac2-vip/192.168.0.43/192.168.0.0/255.255.255.0/eth0, hosting node oelrac2


Enter the below on the tnsnames.ora file

On Node 1:-

TESTDB =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oelrac-scan.manzoor.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
 )


LISTENER_TESTDB =
        (DESCRIPTION=
        (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.42)(PORT=1521))))


On Node 2:-


TESTDB =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oelrac-scan.manzoor.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
 )


LISTENER_TESTDB =
        (DESCRIPTION=
        (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.43)(PORT=1521))))




5) Set the local_listener parameter in the db to listener on the above configuration.


SQL> alter system set local_listener = 'LISTENER_TESTDB' scope=both ;

System altered.

SQL> alter system set remote_listener = 'oelrac-scan:1521' scope=both;

System altered.


6) Check the dummy connection.

[oracle@oelrac2 admin]$ sqlplus -L a/a@testdb

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 10:51:24 2014

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

ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus


-- Dummy connection working fine.

-- Now shutdown the node 2 and start the db in node 1 and check the connections.


[oracle@oelrac2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 10:52:51 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit


[oracle@oelrac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 10:54:46 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2230952 bytes
Variable Size             381683032 bytes
Database Buffers          239075328 bytes
Redo Buffers                3338240 bytes
Database mounted.
Database opened.


[oracle@oelrac1 ~]$ sqlplus -L a/a@testdb

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 10:56:21 2014

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

ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus



-- So far manual cold failover has been succeeded with out any issues. Now lets create an action script
to perform this action by clusterware.

11gr2 clusterware action script requires 4 inputs (start, stop, check & clean). Lets create the action
script.


Create the below directories in both the nodes and change the directory owner to oracle.


[root@oelrac1 11.2.0]# mkdir ha_scripts
[root@oelrac1 11.2.0]# chown oracle:oinstall ha_scripts


[root@oelrac2 11.2.0]# mkdir ha_scripts
[root@oelrac2 11.2.0]# chown oracle:oinstall ha_scripts

Create the below scripts as oracle user.

vi testdb_action_script.sh



#!/bin/sh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
export ORACLE_SID=testdb



case $1 in
'start')
$ORACLE_HOME/bin/sqlplus /nolog <conn / as sysdba
startup;
exit;
EOF
RET=0
;;

'stop')
$ORACLE_HOME/bin/sqlplus /nolog <conn / as sysdba
shut immediate;
exit;
EOF
RET=0
;;

'clean')
$ORACLE_HOME/bin/sqlplus /nolog <conn / as sysdba
shut abort;
exit;
EOF
RET=0
;;

'check')
os=`ps -ef | grep smon | grep $ORACLE_SID | wc -l`
if [ $os -eq 0 ]; then
        RET=1
else
        RET=0
fi
;;

'*')
RET=0
;;
esac


if [ $RET -eq 0 ]; then
        echo "Return value is zero"
        exit 0
        else
        echo "Return value is one"
        exit 1
fi



Test the script manually.


[oracle@oelrac1 ha_scripts]$ ./testdb_action_script.sh start

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 11:36:12 2014

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

SQL> Connected to an idle instance.
SQL> ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2230952 bytes
Variable Size             381683032 bytes
Database Buffers          239075328 bytes
Redo Buffers                3338240 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options



[oracle@oelrac1 ha_scripts]$ ./testdb_action_script.sh stop

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 11:38:05 2014

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

SQL> Connected.
SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


--- Script is working fine. Copy the script to the other node and test.


[oracle@oelrac2 ha_scripts]$ ./testdb_action_script.sh start

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 11:44:16 2014

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

SQL> Connected to an idle instance.
SQL> ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2230952 bytes
Variable Size             381683032 bytes
Database Buffers          239075328 bytes
Redo Buffers                3338240 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Return value is zero
[oracle@oelrac2 ha_scripts]$ ./testdb_action_script.sh check
Return value is zero
[oracle@oelrac2 ha_scripts]$ ./testdb_action_script.sh stop

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 11:46:24 2014

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

SQL> Connected.
SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Return value is zero

-- Now we need a new cluster resource which will manage the failover automatically, Since we
are creating as cluster_resource type we can remove all the properties which are related to
database resource. Below is the modified properties.



NAME=TESTDB.db
TYPE=cluster_resource
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
ACTION_SCRIPT=/u01/grid/11.2.0/ha_scripts/testdb_action_script.sh
ACTIVE_PLACEMENT=0
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=10
DEGREE=1
DESCRIPTION=Oracle Database resource
ENABLED=1
LOGGING_LEVEL=1
PLACEMENT=restricted
RESTART_ATTEMPTS=1
SCRIPT_TIMEOUT=60
START_DEPENDENCIES=hard(ora.DATADG.dg) weak(type:ora.listener.type,uniform:ora.ons) pullup(ora.DATADG.dg)
START_TIMEOUT=600
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATADG.dg)
STOP_TIMEOUT=600
UPTIME_THRESHOLD=1h
HOSTING_MEMBERS=oelrac1 oelrac2



copy the contents to one text file eg. myresource.txt and add the cluster resource.


[oracle@oelrac2 ha_scripts]$ crsctl add res TESTDB.db -type cluster_resource -file myresource.txt


[oracle@oelrac2 ha_scripts]$ crsctl stat res TESTDB.db
NAME=TESTDB.db
TYPE=cluster_resource
TARGET=OFFLINE
STATE=OFFLINE



[oracle@oelrac2 ha_scripts]$ crsctl start res TESTDB.db
CRS-2672: Attempting to start 'TESTDB.db' on 'oelrac1'
CRS-2676: Start of 'TESTDB.db' on 'oelrac1' succeeded



Cluster Resources
--------------------------------------------------------------------------------
TESTDB.db
      1        ONLINE  ONLINE       oelrac1



[oracle@oelrac1 ~]$ crsctl stop res TESTDB.db
CRS-2673: Attempting to stop 'TESTDB.db' on 'oelrac1'
CRS-2677: Stop of 'TESTDB.db' on 'oelrac1' succeeded

[oracle@oelrac1 ~]$ crsctl start res TESTDB.db
CRS-2672: Attempting to start 'TESTDB.db' on 'oelrac1'
CRS-2676: Start of 'TESTDB.db' on 'oelrac1' succeeded


-- Now relocate the res to another server.



[oracle@oelrac1 ~]$ crsctl relocate res TESTDB.db -s oelrac1 -n oelrac2 -f
CRS-2673: Attempting to stop 'TESTDB.db' on 'oelrac1'
CRS-2677: Stop of 'TESTDB.db' on 'oelrac1' succeeded
CRS-2672: Attempting to start 'TESTDB.db' on 'oelrac2'
CRS-2676: Start of 'TESTDB.db' on 'oelrac2' succeeded


-- Now lets check the failover test...


Cluster Resources
--------------------------------------------------------------------------------
TESTDB.db
      1        ONLINE  ONLINE       oelrac2




[oracle@oelrac2 ha_scripts]$ ps -ef | grep pmon
oracle    3529     1  0 08:50 ?        00:00:02 asm_pmon_+ASM2
oracle   13194     1  0 12:15 ?        00:00:00 ora_pmon_testdb


[oracle@oelrac2 ha_scripts]$ kill -9 13194


[oracle@oelrac2 ha_scripts]$ ps -ef | grep pmon
oracle    3529     1  0 08:50 ?        00:00:02 asm_pmon_+ASM2
oracle   13434 12098  0 12:17 pts/0    00:00:00 grep pmon



[oracle@oelrac2 ha_scripts]$ ps -ef | grep pmon
oracle    3529     1  0 08:50 ?        00:00:02 asm_pmon_+ASM2
oracle   13498     1  0 12:17 ?        00:00:00 ora_pmon_testdb
oracle   13768 12098  0 12:18 pts/0    00:00:00 grep pmon


-- Could see that the database automatically comes up.


-- Let shutdown the node 2.


--- Monitor the cluster res in node 1



--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
TESTDB.db
      1        ONLINE  ONLINE       oelrac2                  STOPPING





--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
TESTDB.db
      1        ONLINE  OFFLINE                               STARTING



--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
TESTDB.db
      1        ONLINE  ONLINE       oelrac1



Advantageous:-

1) No need to rely on third party clustware for managing the db resources and cost saving on acquiring those license.
2) Easy maintenance (using crsctl commands).
3) Automatic fail over.
4) Can be useful for application which will not support RAC.


















































Sunday, July 20, 2014

OCR / VOTING disk adminstration in 11gr2

Current OCR disks
-----------------

#/etc/init.d/oracleasm querydisk /dev/sd*

Device "/dev/sdb1" is marked an ASM disk with the label "NEW_OCR_1"
Device "/dev/sdc" is not marked as an ASM disk
Device "/dev/sdc1" is marked an ASM disk with the label "NEW_OCR_2"
Device "/dev/sdd" is not marked as an ASM disk
Device "/dev/sdd1" is marked an ASM disk with the label "NEW_OCR_3"
Device "/dev/sdm1" is marked an ASM disk with the label "OCR_VOTING_1"
Device "/dev/sdn" is not marked as an ASM disk
Device "/dev/sdn1" is marked an ASM disk with the label "OCR_VOTING_2"
Device "/dev/sdo" is not marked as an ASM disk
Device "/dev/sdo1" is marked an ASM disk with the label "OCR_VOTING_3"



[root@oelrac1 bin]# ./ocrconfig -showbackup

oelrac1     2014/05/10 01:26:20     /u01/grid/11.2.0/cdata/oelrac/backup00.ocr

oelrac1     2014/05/09 21:26:10     /u01/grid/11.2.0/cdata/oelrac/backup01.ocr

oelrac2     2014/03/22 01:06:56     /u01/grid/11.2.0/cdata/oelrac/backup02.ocr

oelrac1     2014/05/09 21:26:10     /u01/grid/11.2.0/cdata/oelrac/day.ocr

oelrac1     2014/05/09 21:26:10     /u01/grid/11.2.0/cdata/oelrac/week.ocr


Preparing the disks for fresh use
---------------------------------


1) Delete all the ocr disks.

/etc/init.d/oracleasm deletedisk NEW_OCR_1
/etc/init.d/oracleasm deletedisk NEW_OCR_2
/etc/init.d/oracleasm deletedisk NEW_OCR_3
/etc/init.d/oracleasm deletedisk OCR_VOTING_1
/etc/init.d/oracleasm deletedisk OCR_VOTING_2
/etc/init.d/oracleasm deletedisk OCR_VOTING_3


2) Format the /dev/sdb1 /dev/sdc1 /dev/sdd1 /dev/sdm1 /dev/sdn1 /dev/sdo1

[root@oelrac1 bin]# dd if=/dev/zero of=/dev/sdb1
dd: writing to `/dev/sdb1': No space left on device
2088388+0 records in
2088387+0 records out
1069254144 bytes (1.1 GB) copied, 13.4004 s, 79.8 MB/s

-- Format all the mentioned devices using above method.


3) Check whether the header been removed.

[root@oelrac1 bin]# for i in /dev/sdb1 /dev/sdc1 /dev/sdd1 /dev/sdm1 /dev/sdn1 /dev/sdo1
> do
> echo "$i" >> /tmp/disk_header.log
> /u01/grid/11.2.0/bin/kfed read $i | grep "kfbh.type" >> /tmp/disk_header.log
> done


[root@oelrac1 bin]# cat /tmp/disk_header.log
/dev/sdb1
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
/dev/sdc1
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
/dev/sdd1
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
/dev/sdm1
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
/dev/sdn1
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
/dev/sdo1
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID


-- All the disk has been formatted.


[root@oelrac1 bin]# /etc/init.d/oracleasm createdisk OCR_VOTE_1 /dev/sdb1
Marking disk "OCR_VOTE_1" as an ASM disk:                  [  OK  ]
[root@oelrac1 bin]# /etc/init.d/oracleasm createdisk OCR_VOTE_2 /dev/sdc1
Marking disk "OCR_VOTE_2" as an ASM disk:                  [  OK  ]
[root@oelrac1 bin]# /etc/init.d/oracleasm createdisk OCR_VOTE_3 /dev/sdd1
Marking disk "OCR_VOTE_3" as an ASM disk:                  [  OK  ]
[root@oelrac1 bin]# /etc/init.d/oracleasm createdisk OCR_MIRR_1 /dev/sdm1
Marking disk "OCR_MIRR_1" as an ASM disk:                  [  OK  ]
[root@oelrac1 bin]# /etc/init.d/oracleasm createdisk OCR_MIRR_2 /dev/sdn1
Marking disk "OCR_MIRR_2" as an ASM disk:                  [  OK  ]
[root@oelrac1 bin]# /etc/init.d/oracleasm createdisk OCR_MIRR_3 /dev/sdo1
Marking disk "OCR_MIRR_3" as an ASM disk:                  [  OK  ]


-- OCR disk are ready now.

=============================================================================================================


I) Scenario 1 ( Restore the OCR from the backup)
================================================

a) Backup file of ocr is below.

[root@oelrac1 bin]# ls -lrt /u01/grid/11.2.0/cdata/oelrac/backup00.ocr
-rw------- 1 root root 7176192 May 18 15:44 /u01/grid/11.2.0/cdata/oelrac/backup00.ocr


b) Start the crs in exclusive mode.

[root@oelrac1 bin]# ./crsctl start crs -excl -nocrs
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.mdnsd' on 'oelrac1'
CRS-2676: Start of 'ora.mdnsd' on 'oelrac1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'oelrac1'
CRS-2676: Start of 'ora.gpnpd' on 'oelrac1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'oelrac1'
CRS-2672: Attempting to start 'ora.gipcd' on 'oelrac1'
CRS-2676: Start of 'ora.cssdmonitor' on 'oelrac1' succeeded
CRS-2676: Start of 'ora.gipcd' on 'oelrac1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'oelrac1'
CRS-2672: Attempting to start 'ora.diskmon' on 'oelrac1'
CRS-2676: Start of 'ora.diskmon' on 'oelrac1' succeeded
CRS-2676: Start of 'ora.cssd' on 'oelrac1' succeeded
CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'oelrac1'
CRS-2672: Attempting to start 'ora.ctssd' on 'oelrac1'
CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'oelrac1' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'oelrac1'
CRS-2676: Start of 'ora.ctssd' on 'oelrac1' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'oelrac1' succeeded
CRS-2679: Attempting to clean 'ora.asm' on 'oelrac1'
CRS-2681: Clean of 'ora.asm' on 'oelrac1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'oelrac1'
CRS-2676: Start of 'ora.asm' on 'oelrac1' succeeded


c) Switch as grid owner and login to asm instance.

SQL> select name, state from V$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
FRA                            DISMOUNTED
DATADG                         DISMOUNTED

SQL>  select path, header_status from V$asm_disk;

PATH                                               HEADER_STATU
-------------------------------------------------- ------------
/dev/oracleasm/disks/OCR_MIRR_3                    PROVISIONED
/dev/oracleasm/disks/OCR_MIRR_2                    PROVISIONED
/dev/oracleasm/disks/OCR_MIRR_1                    PROVISIONED
/dev/oracleasm/disks/OCR_VOTE_3                    PROVISIONED
/dev/oracleasm/disks/OCR_VOTE_2                    PROVISIONED
/dev/oracleasm/disks/OCR_VOTE_1                    PROVISIONED
/dev/oracleasm/disks/DISK_NEW_1                    MEMBER
/dev/oracleasm/disks/DATA_DISK_3                   MEMBER
/dev/oracleasm/disks/DATA_DISK_2                   MEMBER
/dev/oracleasm/disks/DATA_DISK_1                   MEMBER
/dev/oracleasm/disks/DISK_NEW_4                    MEMBER
/dev/oracleasm/disks/DISK_NEW_3                    MEMBER
/dev/oracleasm/disks/DISK_NEW_2                    MEMBER
/dev/oracleasm/disks/DATA_DISK_4                   MEMBER

14 rows selected.



SQL> ! cat /etc/oracle/ocr.loc
ocrconfig_loc=+OCR_VOTING
local_only=false

d) Create the Diskgroup with the same name.

SQL> create diskgroup OCR_VOTING NORMAL REDUNDANCY DISK '/dev/oracleasm/disks/OCR_VOTE_1','/dev/oracleasm/disks/OCR_VOTE_2','/dev/oracleasm/disks/OCR_VOTE_3'
     attribute 'compatible.asm' = '11.2', 'au_size' = '4M';

Diskgroup created.


SQL> exit;

e) Now restore the OCR from backup.

[root@oelrac1 bin]# ./ocrconfig -restore /u01/grid/11.2.0/cdata/oelrac/backup00.ocr
[root@oelrac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2828
         Available space (kbytes) :     259292
         ID                       :  516257634
         Device/File Name         : +OCR_VOTING
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded


f) Now re-start the crs in exclusive mode


# crsctl stop crs -f
# crsctl start crs -excl -nocrs

[root@oelrac1 trace]# /u01/grid/11.2.0/bin/crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       oelrac1                  Started
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       oelrac1
ora.crf
      1        OFFLINE OFFLINE
ora.crsd
      1        OFFLINE OFFLINE
ora.cssd
      1        ONLINE  ONLINE       oelrac1
ora.cssdmonitor
      1        ONLINE  ONLINE       oelrac1
ora.ctssd
      1        ONLINE  ONLINE       oelrac1                  ACTIVE:0
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        OFFLINE OFFLINE
ora.gipcd
      1        ONLINE  ONLINE       oelrac1
ora.gpnpd
      1        ONLINE  ONLINE       oelrac1
ora.mdnsd
      1        ONLINE  ONLINE       oelrac1
[root@oelrac1 trace]# /u01/grid/11.2.0/bin/crsctl query css votedisk
Located 0 voting disk(s).

[root@oelrac1 trace]# /u01/grid/11.2.0/bin/crsctl replace votedisk +OCR_VOTING
Successful addition of voting disk 3eca999b99794f83bf37adb42833f263.
Successful addition of voting disk 2ed8677861f74f9abf25c98a802d95fe.
Successful addition of voting disk 15f88f5c74ce4f77bf1fd2a3c10e4597.
Successfully replaced voting disk group with +OCR_VOTING.
CRS-4266: Voting file(s) successfully replaced

-- Now restart the cluster fully.

[root@oelrac1 bin]# ./crsctl start crs
CRS-4123: Oracle High Availability Services has been started.


[root@oelrac1 bin]# ./crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   3eca999b99794f83bf37adb42833f263 (/dev/oracleasm/disks/OCR_VOTE_1) [OCR_VOTING]
 2. ONLINE   2ed8677861f74f9abf25c98a802d95fe (/dev/oracleasm/disks/OCR_VOTE_2) [OCR_VOTING]
 3. ONLINE   15f88f5c74ce4f77bf1fd2a3c10e4597 (/dev/oracleasm/disks/OCR_VOTE_3) [OCR_VOTING]
Located 3 voting disk(s).


[root@oelrac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2828
         Available space (kbytes) :     259292
         ID                       :  516257634
         Device/File Name         : +OCR_VOTING
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded



[root@oelrac1 bin]# ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADG.dg
               ONLINE  ONLINE       oelrac1
ora.LISTENER.lsnr
               ONLINE  ONLINE       oelrac1
ora.OCR_VOTING.dg
               ONLINE  ONLINE       oelrac1
ora.asm
               ONLINE  ONLINE       oelrac1                  Started
ora.gsd
               OFFLINE OFFLINE      oelrac1
ora.net1.network
               ONLINE  ONLINE       oelrac1
ora.ons
               ONLINE  ONLINE       oelrac1
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       oelrac1
ora.cvu
      1        ONLINE  ONLINE       oelrac1
ora.firstdb.db
      1        ONLINE  OFFLINE                               Instance Shutdown
      2        ONLINE  OFFLINE
ora.oc4j
      1        ONLINE  ONLINE       oelrac1
ora.oelrac1.vip
      1        ONLINE  ONLINE       oelrac1
ora.oelrac2.vip
      1        ONLINE  INTERMEDIATE oelrac1                  FAILED OVER
ora.scan1.vip
      1        ONLINE  ONLINE       oelrac1


================================================================================================================

II) Add one more OCR Mirror Location (upto 4 ocrmirrors can be added)

SQL> set lines 200 pages 200;
SQL> col path for a40;
SQL> select path, header_status from V$asm_disk;

PATH                                     HEADER_STATU
---------------------------------------- ------------
/dev/oracleasm/disks/OCR_MIRR_3          PROVISIONED
/dev/oracleasm/disks/OCR_MIRR_2          PROVISIONED
/dev/oracleasm/disks/OCR_MIRR_1          PROVISIONED

SQL> create diskgroup OCR_MIRROR normal redundancy disk '/dev/oracleasm/disks/OCR_MIRR_1','/dev/oracleasm/disks/OCR_MIRR_2','/dev/oracleasm/disks/OCR_MIRR_3'
    attribute
    'compatible.asm' = '11.2', 'au_size'='4M'
    /



[root@oelrac1 bin]# ./ocrconfig -add +OCR_MIRROR

[root@oelrac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2840
         Available space (kbytes) :     259280
         ID                       :  516257634
         Device/File Name         : +OCR_VOTING
                                    Device/File integrity check succeeded
         Device/File Name         : +OCR_MIRROR
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded



--When votedisk is on ASM diskgroup, no add option available.
The number of votedisk is determined by the diskgroup redundancy. If more copies of votedisks are desired, one can move votedisk to a diskgroup
with higher redundancy.

-- As for the voting disk concerned when the voting disk is placed in asm diskgroup below is the no. of vote
disk base on the redundancy type of the diskgroup.

EXTERNAL - 1 vote disk
NORMAL   - 3 vote disk
HIGH     - 5 vote disk

so there is no add command and only replace can be used for votedisk.

[root@oelrac1 bin]# ./crsctl replace votedisk +OCR_MIRROR
Successful addition of voting disk 5f7d9d544a7b4fa7bf2b2df10f4cd61f.
Successful addition of voting disk 1ca712cdf06a4f5dbf1843f0d98770d1.
Successful addition of voting disk 5afe0a1d5cbe4fc6bf1a8e04c27be20c.
Successful deletion of voting disk 3eca999b99794f83bf37adb42833f263.
Successful deletion of voting disk 2ed8677861f74f9abf25c98a802d95fe.
Successful deletion of voting disk 15f88f5c74ce4f77bf1fd2a3c10e4597.
Successfully replaced voting disk group with +OCR_MIRROR.
CRS-4266: Voting file(s) successfully replaced


[root@oelrac1 bin]# ./crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   5f7d9d544a7b4fa7bf2b2df10f4cd61f (/dev/oracleasm/disks/OCR_MIRR_1) [OCR_MIRROR]
 2. ONLINE   1ca712cdf06a4f5dbf1843f0d98770d1 (/dev/oracleasm/disks/OCR_MIRR_2) [OCR_MIRROR]
 3. ONLINE   5afe0a1d5cbe4fc6bf1a8e04c27be20c (/dev/oracleasm/disks/OCR_MIRR_3) [OCR_MIRROR]
Located 3 voting disk(s).

-- the disks are deleted from the old diskgroup and added to the new diskgroup.

-- Now lets see how the mirror is usefull.


SQL> alter diskgroup ocr_voting dismount force;

Diskgroup altered.




[root@oelrac1 bin]# ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADG.dg
               ONLINE  ONLINE       oelrac1
ora.LISTENER.lsnr
               ONLINE  ONLINE       oelrac1
ora.OCR_MIRROR.dg
               ONLINE  ONLINE       oelrac1
ora.OCR_VOTING.dg
               OFFLINE OFFLINE      oelrac1
ora.asm
               ONLINE  ONLINE       oelrac1                  Started
ora.gsd
               OFFLINE OFFLINE      oelrac1
ora.net1.network
               ONLINE  ONLINE       oelrac1
ora.ons
               ONLINE  ONLINE       oelrac1
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       oelrac1
ora.cvu
      1        ONLINE  ONLINE       oelrac1
ora.firstdb.db
      1        ONLINE  OFFLINE                               Instance Shutdown
      2        ONLINE  OFFLINE
ora.oc4j
      1        ONLINE  ONLINE       oelrac1
ora.oelrac1.vip
      1        ONLINE  ONLINE       oelrac1
ora.oelrac2.vip
      1        ONLINE  INTERMEDIATE oelrac1                  FAILED OVER
ora.scan1.vip
      1        ONLINE  ONLINE       oelrac1


-- The OCR_VOTING diskgroup has been dismounted but still the clusterware is online.

--Lets dismount the mirror diskgroup as well.


SQL> alter diskgroup ocr_mirror dismount force;

Diskgroup altered.




[root@oelrac1 bin]# ./crsctl stat res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.


-- Now the crs is not functional.


SQL> alter diskgroup ocr_mirror mount;

Diskgroup altered.

SQL> alter diskgroup ocr_voting mount;

Diskgroup altered.


[root@oelrac1 bin]# ./crsctl start res ora.crsd -init
CRS-2672: Attempting to start 'ora.crsd' on 'oelrac1'
CRS-2676: Start of 'ora.crsd' on 'oelrac1' succeeded

[root@oelrac1 bin]# ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADG.dg
               ONLINE  ONLINE       oelrac1
ora.LISTENER.lsnr
               ONLINE  ONLINE       oelrac1
ora.OCR_MIRROR.dg
               ONLINE  ONLINE       oelrac1
ora.OCR_VOTING.dg
               ONLINE  ONLINE       oelrac1
ora.asm
               ONLINE  ONLINE       oelrac1                  Started
ora.gsd
               OFFLINE OFFLINE      oelrac1
ora.net1.network
               ONLINE  ONLINE       oelrac1
ora.ons
               ONLINE  ONLINE       oelrac1
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       oelrac1
ora.cvu
      1        ONLINE  ONLINE       oelrac1
ora.firstdb.db
      1        ONLINE  OFFLINE                               Instance Shutdown,S
                                                             TARTING
      2        ONLINE  OFFLINE
ora.oc4j
      1        ONLINE  ONLINE       oelrac1
ora.oelrac1.vip
      1        ONLINE  ONLINE       oelrac1
ora.oelrac2.vip
      1        ONLINE  INTERMEDIATE oelrac1                  FAILED OVER
ora.scan1.vip
      1        ONLINE  ONLINE       oelrac1



====================================================================================



III) Remove an OCR Device.
--------------------------

[root@oelrac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2836
         Available space (kbytes) :     259284
         ID                       :  516257634
         Device/File Name         : +OCR_VOTING
                                    Device/File integrity check succeeded
         Device/File Name         : +OCR_MIRROR
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded


-- Lets remove the primary device.


[root@oelrac1 bin]# ./ocrconfig -delete +OCR_VOTING


[root@oelrac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2836
         Available space (kbytes) :     259284
         ID                       :  516257634
         Device/File Name         : +OCR_MIRROR
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded



[root@oelrac1 bin]# cat /etc/oracle/ocr.loc
#Device/file +OCR_VOTING getting replaced by device +OCR_MIRROR
ocrconfig_loc=+OCR_MIRROR
local_only=false


-- Could see that the primary has been replaced with mirror one.

-- Lets add the diskgroup back.

[root@oelrac1 bin]# ./ocrconfig -add +OCR_VOTING
[root@oelrac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2836
         Available space (kbytes) :     259284
         ID                       :  516257634
         Device/File Name         : +OCR_MIRROR
                                    Device/File integrity check succeeded
         Device/File Name         : +OCR_VOTING
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded



[root@oelrac1 bin]# cat /etc/oracle/ocr.loc
#Device/file  getting replaced by device +OCR_VOTING
ocrconfig_loc=+OCR_MIRROR
ocrmirrorconfig_loc=+OCR_VOTING
local_only=false


III) Replace or move Existing OCR location to New Location.


Note.
1. An ocrmirror must be in place before trying to replace the OCR device. The ocrconfig will fail
with PROT-16, if there is no ocrmirror exists.
2. If an OCR device is replaced with a device of a different size, the size of the new device will
not be reflected until the clusterware is restarted.

-- ocrconfig -replace +OCR_VOTING -replacement +NEW_OCR_VOTING




-- Voting Disk adminstration.

What happens if I lose my voting disk(s)?

If you lose 1/2 or more of all of your voting disks, then nodes get evicted from the cluster, or nodes kick
themselves out of the cluster. It doesn't threaten database corruption. Alternatively you can use external
redundancy which means you are providing redundancy at the storage level using RAID.
For this reason when using Oracle for the redundancy of your voting disks, Oracle recommends that customers
use 3 or more voting disks in Oracle RAC 10g Release 2. Note: For best availability, the 3 voting files should
be physically separate disks. It is recommended to use an odd number as 4 disks will not be any more highly
available than 3 disks, 1/2 of 3 is 1.5...rounded to 2, 1/2 of 4 is 2, once we lose 2 disks, our cluster will fail
with both 4 voting disks or 3 voting disks.

Restoring corrupted voting disks is easy since there isn't any significant persistent data stored in the voting disk.
See the Oracle Clusterware Admin and Deployment Guide for information on backup and restore of voting disks.

For 11.2+, it is no longer required to back up the voting disk. The voting disk data is automatically backed up in OCR
as part of any configuration change. The voting disk files are backed up automatically by Oracle Clusterware if the
contents of the files have changed in the following ways:

Configuration parameters, for example misscount, have been added or modified
After performing voting disk add or delete operations


Add - When votedisk is on ASM diskgroup, no add option available. The number of votedisk is determined by the
      diskgroup redundancy. If more copies of votedisks are desired, one can move votedisk to a diskgroup
      with higher redundancy.

delete - When votedisk is on ASM, no delete option available, one can only replace the existing votedisk group with another ASM diskgroup.

Move the voting disk- as per below.


[root@oelrac1 bin]# ./crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   5f7d9d544a7b4fa7bf2b2df10f4cd61f (/dev/oracleasm/disks/OCR_MIRR_1) [OCR_MIRROR]
 2. ONLINE   1ca712cdf06a4f5dbf1843f0d98770d1 (/dev/oracleasm/disks/OCR_MIRR_2) [OCR_MIRROR]
 3. ONLINE   5afe0a1d5cbe4fc6bf1a8e04c27be20c (/dev/oracleasm/disks/OCR_MIRR_3) [OCR_MIRROR]
Located 3 voting disk(s).

-- Current location id ocr_mirror diskgroup , lets move it to OCR_VOTING diskgroup, it can be done online.


[root@oelrac1 bin]# ./crsctl replace votedisk +OCR_VOTING
Successful addition of voting disk 85ae96c5a26e4f0cbf35f6dd50cb8765.
Successful addition of voting disk 761064c6565c4f54bfedc332938412f8.
Successful addition of voting disk 8484395588014f8abf7274198aa21b1a.
Successful deletion of voting disk 5f7d9d544a7b4fa7bf2b2df10f4cd61f.
Successful deletion of voting disk 1ca712cdf06a4f5dbf1843f0d98770d1.
Successful deletion of voting disk 5afe0a1d5cbe4fc6bf1a8e04c27be20c.
Successfully replaced voting disk group with +OCR_VOTING.
CRS-4266: Voting file(s) successfully replaced


Related-
How to fix the "DiscoveryString in profile.xml" or "asm_diskstring in ASM" if set wrongly (Doc ID 1077094.1)
NOTE:1376225.1 - How to Modify an Existing ASM Spfile in a RAC Environment
http://www.oracle.com/technetwork/database/clusterware/overview/grid-infra-thirdvoteonnfs-131158.pdf


Saturday, July 19, 2014

How spfile is discovered in 11gr2 Grid

How spfile is discovered in 11gr2 Grid
--------------------------------------


As we all aware in 11gr2 grid infrastructure the asm spfile will be placed
under the asm diskgroup.



[oracle@oelrac1 ~]$ gpnptool get
Warning: some command line parameters were defaulted. Resulting command line:
         /u01/grid/11.2.0/bin/gpnptool.bin get -o-

Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
GPnP service is not running on localhost. Found locally cached profile...
micVNAZ3LnolkupldzDRgpM/sEw=HgZp5wx5t55NW7mWiEHSQsevlOd4jpzkDDoyNXYaVUf3fOYK+FLueGTLRr9WZLYmRedUuGVORGECF7v/apacc6dC+nLIa6XxutkHAu6rYuSbjcKoqpB8aCdSlG1gV1v7mzuSRQqGizs3ZW74Np/HZHXpwKsUGvbr6TLzLOwBT2Y=
Success.

--In the above output we could see that the asm_diskstring is mentioned as '/dev/oracleasm/disk*' which is
used to identify the spfile and the voting disks.



[oracle@oelrac1 ~]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   8f20abfc981c4f0dbf4707016284a667 (/dev/oracleasm/disks/OCR_VOTING_1) [OCR_VOTING]
 2. ONLINE   3434c752d9654f2dbf5225b202432ce3 (/dev/oracleasm/disks/OCR_VOTING_2) [OCR_VOTING]
 3. ONLINE   20a19fe75c3f4f94bf4f3e95866128ba (/dev/oracleasm/disks/OCR_VOTING_3) [OCR_VOTING]
Located 3 voting disk(s).



SQL> show parameter asm_disk;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string      DATADG
asm_diskstring                       string      /dev/oracleasm/disks/*
SQL> ! ls -lrt /dev/oracleasm/disks/*
brw-rw---- 1 oracle dba 8, 113 Mar 15 09:27 /dev/oracleasm/disks/DATA_DISK_4
brw-rw---- 1 oracle dba 8,  81 Mar 15 09:28 /dev/oracleasm/disks/DATA_DISK_2
brw-rw---- 1 oracle dba 8,  49 Mar 15 09:28 /dev/oracleasm/disks/OCR_VOTING_3
brw-rw---- 1 oracle dba 8,  33 Mar 15 09:28 /dev/oracleasm/disks/OCR_VOTING_2
brw-rw---- 1 oracle dba 8,  17 Mar 15 09:28 /dev/oracleasm/disks/OCR_VOTING_1
brw-rw---- 1 oracle dba 8,  97 Mar 15 09:28 /dev/oracleasm/disks/DATA_DISK_3
brw-rw---- 1 oracle dba 8,  65 Mar 15 09:28 /dev/oracleasm/disks/DATA_DISK_1

SQL> alter system set asm_diskstring = '/dev/oracleasm/disks/DATA*' scope=spfile;

System altered.


[root@oelrac1 ~]# cd /u01/grid/11.2.0/bin/
[root@oelrac1 bin]# ./crsctl stop crs
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oelrac1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[root@oelrac1 bin]# ./crsctl start crs
CRS-4123: Oracle High Availability Services has been started.


[root@oelrac1 bin]# ./crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  OFFLINE                               Instance Shutdown
ora.cluster_interconnect.haip
      1        ONLINE  OFFLINE
ora.crf
      1        ONLINE  ONLINE       oelrac1
ora.crsd
      1        ONLINE  OFFLINE
ora.cssd
      1        ONLINE  OFFLINE                               STARTING
ora.cssdmonitor
      1        ONLINE  ONLINE       oelrac1
ora.ctssd
      1        ONLINE  OFFLINE
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  OFFLINE
ora.gipcd
      1        ONLINE  ONLINE       oelrac1
ora.gpnpd
      1        ONLINE  ONLINE       oelrac1
ora.mdnsd
      1        ONLINE  ONLINE       oelrac1



-- Now the CSSD process is not coming up.. this is expected.

Some snaps from occsd.log


2014-03-14 17:38:23.393: [    GPNP][3455813376]clsgpnp_profileCallUrlInt: [at clsgpnp.c:2234] Result: (0) CLSGPNP_OK. Successful get-profile CALL to remote "ipc://GPNPD
_oelrac1" disco ""
2014-03-14 17:38:23.393: [    CSSD][3455813376]clssscGetParameterProfile: buffer passed for parameter ASM discovery (3) is too short, required 27, passed 20
2014-03-14 17:38:23.393: [    CSSD][3455813376]clssnmReadDiscoveryProfile: voting file discovery string(/dev/oracleasm/disks/DATA*)
2014-03-14 17:38:23.393: [    CSSD][3455813376]clssnmvDDiscThread: using discovery string /dev/oracleasm/disks/DATA* for initial discovery
2014-03-14 17:38:23.393: [   SKGFD][3455813376]Discovery with str:/dev/oracleasm/disks/DATA*:

2014-03-14 17:38:23.393: [   SKGFD][3455813376]UFS discovery with :/dev/oracleasm/disks/DATA*:

2014-03-14 17:38:23.398: [   SKGFD][3455813376]Fetching UFS disk :/dev/oracleasm/disks/DATA_DISK_1:

2014-03-14 17:38:23.398: [   SKGFD][3455813376]Fetching UFS disk :/dev/oracleasm/disks/DATA_DISK_2:

2014-03-14 17:38:23.398: [   SKGFD][3455813376]Fetching UFS disk :/dev/oracleasm/disks/DATA_DISK_3:

2014-03-14 17:38:23.398: [   SKGFD][3455813376]Fetching UFS disk :/dev/oracleasm/disks/DATA_DISK_4:

2014-03-14 17:38:23.398: [   SKGFD][3455813376]OSS discovery with :/dev/oracleasm/disks/DATA*:

2014-03-14 17:38:23.398: [   SKGFD][3455813376]Handle 0x7fd5b80930f0 from lib :UFS:: for disk :/dev/oracleasm/disks/DATA_DISK_1:

2014-03-14 17:38:23.398: [   SKGFD][3455813376]Handle 0x7fd5b8137d80 from lib :UFS:: for disk :/dev/oracleasm/disks/DATA_DISK_2:

2014-03-14 17:38:23.398: [   SKGFD][3455813376]Handle 0x7fd5b8125730 from lib :UFS:: for disk :/dev/oracleasm/disks/DATA_DISK_3:

2014-03-14 17:38:23.399: [   SKGFD][3455813376]Handle 0x7fd5b8136cf0 from lib :UFS:: for disk :/dev/oracleasm/disks/DATA_DISK_4:

2014-03-14 17:38:23.399: [   SKGFD][3455813376]Lib :UFS:: closing handle 0x7fd5b80930f0 for disk :/dev/oracleasm/disks/DATA_DISK_1:

2014-03-14 17:38:23.399: [   SKGFD][3455813376]Lib :UFS:: closing handle 0x7fd5b8137d80 for disk :/dev/oracleasm/disks/DATA_DISK_2:

2014-03-14 17:38:23.399: [   SKGFD][3455813376]Lib :UFS:: closing handle 0x7fd5b8125730 for disk :/dev/oracleasm/disks/DATA_DISK_3:

2014-03-14 17:38:23.399: [   SKGFD][3455813376]Lib :UFS:: closing handle 0x7fd5b8136cf0 for disk :/dev/oracleasm/disks/DATA_DISK_4:

2014-03-14 17:38:23.399: [    CSSD][3455813376]clssnmvDiskVerify: Successful discovery of 0 disks
2014-03-14 17:38:23.399: [    CSSD][3455813376]clssnmCompleteInitVFDiscovery: Completing initial voting file discovery
2014-03-14 17:38:23.399: [    CSSD][3455813376]clssnmvFindInitialConfigs: No voting files found




--- Its is not able to find the voting disks.


[oracle@oelrac1 ~]$ cd /u01/grid/11.2.0/gpnp/oelrac1/profiles/peer/
[oracle@oelrac1 peer]$ cat profile.xml
xmlns:gpnp="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:orcl="http://www.oracle.com/gpnp/2005/11/gpnp-profile"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.grid-pnp.org/2005/11/gpnp-profile
gpnp-profile.xsd" ProfileSequence="5" ClusterUId="5785d8b1a7aa5ff5ff072fe8b2bf44d1" ClusterName="oelrac"
PALocation="">Adapter="eth0" Use="public"/>DiscoveryString="+asm" LeaseDuration="400"/> 7jxgRZIBJookyQCAC5wBSF4s31I=iwoqZDP0rKW2D+uUI/NJZ5ckxgsHW49XQqzabeYi/9ebvXPb/bN2p0z9nuIrvRqj9a3E1DwEfP7JAKfD1ApHCRrK+v1vQ6VfypVXR2dToMrSwQk0tG6sEKl3wL3Pcm6DYEY3eVjRc+qA6bB0Xr/ENcZ0Hs9apAmYY6/19i0xi80=
[oracle@oelrac1 peer]$



-- From the bove we could see DiscoveryString="/dev/oracleasm/disks/DATA*"

[oracle@oelrac1 peer]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager


[oracle@oelrac1 peer]$ cp profile.xml profile.xml.bkp


[oracle@oelrac1 peer]$ gpnptool unsign -p=profile.xml.bkp
Warning: some command line parameters were defaulted. Resulting command line:
         /u01/grid/11.2.0/bin/gpnptool.bin unsign -p=profile.xml.bkp -o-


Success.


[oracle@oelrac1 peer]$ gpnptool edit -asm_dis='/dev/oracleasm/disks/*' -p=profile.xml.bkp -o=profile.xml.bkp -ovr
Resulting profile written to "profile.xml.bkp".
Success.

[oracle@oelrac1 peer]$ gpnptool sign -p=profile.xml.bkp -w=/u01/grid/11.2.0/gpnp/oelrac1/wallets/peer/ -o=profile.new
Resulting profile written to "profile.new".
Success.


Restart the cluster.

[root@oelrac1 bin]# ./crsctl stop crs -f
CRS-4133: Oracle High Availability Services has been stopped.


[root@oelrac1 bin]# ./crsctl start crs
CRS-4123: Oracle High Availability Services has been started.


[root@oelrac1 bin]# ./crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       oelrac1                  Started
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       oelrac1
ora.crf
      1        ONLINE  ONLINE       oelrac1
ora.crsd
      1        ONLINE  INTERMEDIATE oelrac1
ora.cssd
      1        ONLINE  ONLINE       oelrac1
ora.cssdmonitor
      1        ONLINE  ONLINE       oelrac1
ora.ctssd
      1        ONLINE  ONLINE       oelrac1                  ACTIVE:0
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  INTERMEDIATE oelrac1
ora.gipcd
      1        ONLINE  ONLINE       oelrac1
ora.gpnpd
      1        ONLINE  ONLINE       oelrac1
ora.mdnsd
      1        ONLINE  ONLINE       oelrac1



[oracle@oelrac1 ~]$ gpnptool get
Warning: some command line parameters were defaulted. Resulting command line:
         /u01/grid/11.2.0/bin/gpnptool.bin get -o-

bOe6eT67erYiu4fT4ZwLIyKW9EA=KfwbH0soTVN3yVuwMA7ln57LUlvRgIPWGdxdRQlkghZZisg31NaZYXg4AHeXDo9xEs5LsYf5wR+Ay4ez84JWSCZ31G0k7o4FS43hB9CvrBW6q1+54JsC8iQGPZT61uGjqAVkTw8K9E0Kp5TIwTg0Tm54KwnxSu+UGo/O+w22ppY=
Success.


Now the - DiscoveryString="/dev/oracleasm/disks/*"

and also the crs started without issues.


















Monday, January 20, 2014

OGG - Experiments

Installation:-
==============

1. GG Installation.


  a) GG home - eg /db/gg_trace/golden_gate --> untar the tar file ( tar -xvf software_file_name)

  b) ggsci

> create subdirs


2. Connect to the db and create the golden gate admin user.



SQL> create tablespace golden_gate datafile '/db/oracle/oradata/NSBLSIT12/golden_gate01.dbf' size 2g;

Tablespace created.

SQL> create user ggate identified by Gate_123 default tablespace golden_gate;

User created.

grant connect, resource to ggate;
grant dba to ggate;
grant select any dictionary to ggate;
grant insert any table, select any table, delete any table, update any table to ggate;
grant flashback any table to ggate;
grant execute on dbms_flashback to ggate;
grant execute on utl_file to ggate;
grant create any table to ggate;
grant create sequence to ggate;
grant drop any table to ggate;


SQL> alter database add supplemental log data (all) columns;

Database altered.



SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup

SQL> grant GGS_GGSUSER_ROLE to ggate;

Grant succeeded.

SQL> @ddl_enable

SQL> @ddl_pin ggate



Solution: Grant these privieleges to ggate
=======================================================
1. Run package to grant Oracle Streams admin privilege.
exec dbms_streams_auth.grant_admin_privilege('ggate');
exec dbms_goldengate_auth.grant_admin_privilege('ggate');
2. Grant INSERT into logmnr_restart_ckpt$.
grant insert on system.logmnr_restart_ckpt$ to ggate;
3. Grant UPDATE on streams$_capture_process.
grant update on sys.streams$_capture_process to ggate;
4. Grant the 'become user' privilege.
grant become user to ggate;
=======================================================

Examples:-
---------


Server: 10.41.66.187 - Source
Server: 10.14.24.152 - Target

Source database : NPTLPVT
target database : NTESTSRV

GG owner - GGATE


-- Initial Load
--> Direct load -- Extract sends data directly to Replicat to apply using SQL
  (During the load, the records are applied to the target database one record at a time, so this
  method is considerably slower than any of the other initial load methods)

  Extract  Parameters (RMTHOST, RMTTASK REPLICAT, GROUP) / ADD EXTRACT GRP SOURCEISTABLE
  Replicat Parameters ADD REPLICAT GRP SPECIALRUN



--> Direct bulk load -- Replicat uses the Oracle SQL*Loader API.
 
  Extract  Parameters (RMTHOST, RMTTASK REPLICAT, GROUP) / ADD EXTRACT GRP SOURCEISTABLE
  Replicat Parameters (BULKLOAD / ADD REPLICAT GRP SPECIALRUN)



--> File to Replicat -- Extract writes to a file (Trail File) that Replicat applies using SQL.

  Extract  Parameters (RMTHOST, RMTFILE) / ADD EXTRACT GRP SOURCEISTABLE
  Replicat Parameters ADD REPLICAT GRP SPECIALRUN

--> File to database utility -- Extract writes to a file formatted for a DB bulk load utility.

  Extract parameters (RMTFILE, FORMATASCII SQLLOADER / ADD EXTRACT GRP SOURCEISTABLE)
  Replicat Parameters (GENLOADFILES) tO generate controlfile and runfile



Seting up the environment on source
-----------------------------------

--Setup the checkpoint table for whole instance.

GGSCI (S66CJ187) 2> dblogin userid ggate password Gate_123
Successfully logged into database.


GGSCI (S66CJ187) 9> ADD CHECKPOINTTABLE
ERROR: Missing checkpoint table specification.

GGSCI (S66CJ187) 10> EDIT PARAMS ./GLOBALS

GGSCHEMA GGATE
CHECKPOINTTABLE GGATE.CHKPTBL


GGSCI (S66CJ187) 2> ADD CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (GGATE.CHKPTBL)...

Successfully created checkpoint table GGATE.CHKPTBL.



--Setup the MGR Process

GGSCI (S66CJ187) 12> EDIT PARAMS MGR

PORT 7810
DYNAMICPORTLIST 7900-7950


GGSCI (S66CJ187) 13> START MGR

Manager started.


GGSCI (S66CJ187) 14> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


I) Load using initital load where the source and target table are having mismtach in columns.


SQL> desc GG_SOURCE.GG_SYNC_TBL1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER
 ENAME                                              VARCHAR2(30)
 DETAILS                                            VARCHAR2(30)


-- Target table

SQL> desc GG_TARGET.GG_SYNC_TBL1_TGT;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER
 ENAME                                              VARCHAR2(30)




-- As we can see the source table GG_SYNC_TBL_1 has 3 columns and the target table GG_SYNC_TBL1_TGT
is having 2 columns, we can use the defgen (definition generator) utility to map this.




Create a parameter file first.

GGSCI (S66CJ187) 4> edit params defgen


DEFSFILE ./dirsql/GG_SYNC_TBL1.sql
USERID GGATE password Gate_123
TABLE  GG_SOURCE.GG_SYNC_TBL1;




$ ./defgen PARAMFILE ./dirprm/defgen.prm REPORTFILE ./dirrpt/defgen.rpt


A GG_SYNC_TBL1.sql is generated under dirsql directory which looks like

*+- Defgen version 2.0, Encoding hp-roman8
*
* Definitions created/modified  2014-01-15 16:31
*
*  Field descriptions for each column entry:
*
*     1    Name
*     2    Data Type
*     3    External Length
*     4    Fetch Offset
*     5    Scale
*     6    Level
*     7    Null
*     8    Bump if Odd
*     9    Internal Length
*    10    Binary Length
*    11    Table Length
*    12    Most Significant DT
*    13    Least Significant DT
*    14    High Precision
*    15    Low Precision
*    16    Elementary Item
*    17    Occurs
*    18    Key Column
*    19    Sub Data Type
*
Database type: ORACLE
Character set ID: UTF-8
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
*
Definition for table GG_SOURCE.GG_SYNC_TBL1
Record length: 128
Syskey: 0
Columns: 3
EMPNO     64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
ENAME     64     30       56  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
DETAILS   64     30       92  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
End of definition


ftp/scp the GG_SYNC_TBL1.sql to the target server under the dirsql directory of GG software home.



Extract on the source side
--------------------------

GGSCI (S66CJ187) 20>

GGSCI (S66CJ187) 20> EDIT PARAMS EXT1


EXTRACT DIR_1
USERID GGATE, PASSWORD Gate_123
RMTHOST 10.14.24.152, MGRPORT 7810
RMTTASK REPLICAT, GROUP DIR_R1
TABLE GG_SOURCE.GG_SYNC_TBL2;


GGSCI (S66CJ187) 20> ADD EXTRACT EXT1, SOURCEISTABLE




Replicat on the target side
---------------------------

-- Setup the environment (CHECKPOINTABLE and MGR Process as per above).


GGSCI (S24BF152) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (S24BF152) 2> EDIT PARAMS REP1


REPLICAT REP1
USERID GGATE, PASSWORD Gate_123
SOURCEDEFS ./dirsql/GG_SYNC_TBL1.sql
MAP GG_SOURCE.GG_SYNC_TBL1, TARGET GG_TARGET.GG_SYNC_TBL1_TGT, COLMAP (EMPNO=EMPNO ENAME=ENAME);


GGSCI (S24BF152) 3> ADD REPLICAT REP1, SPECIALRUN



Start the Loading
-----------------

On the source side start the Extract prcoess , since it is a initial load the replicat
will be started automatically on the target side.

GGSCI (S66CJ187) 2> START EXTRACT EXT1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (S66CJ187) 9> INFO EXT1

EXTRACT    EXT1      Last Started 2014-01-16 08:10   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table GG_SOURCE.GG_SYNC_TBL1
                     2014-01-16 08:10:45  Record 1
Task                 SOURCEISTABLE


GGSCI (S66CJ187) 10> !
INFO EXT1

EXTRACT    EXT1      Last Started 2014-01-16 08:10   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table GG_SOURCE.GG_SYNC_TBL1
                     2014-01-16 08:10:46  Record 1000
Task                 SOURCEISTABLE

GGSCI (S66CJ187) 11> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


--as above 1000 records been loaded and then the extract process is stopped.

--Check the count on the target side..

SQL> select count(*) from gg_target.gg_sync_tbl1_tgt;

  COUNT(*)
----------
      1000



II) Change Data Capture.
------------------------

Source Extract Process
----------------------

GGSCI (S66CJ187) 4> EDIT PARAMS EXT1

EXTRACT EXT1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID GGATE@NPTLPVT,  PASSWORD GATE_123
EXTTRAIL ./dirdat/XO
DDL INCLUDE MAPPED EXCLUDE OBJTYPE TRIGGER
--TRANLOGOPTIONS DBLOGREADER # IF LOG FILES ARE STORED IN ASM (Supported 10.2.0.5 and higher and 11.2.0.2 & Higher Not 11gr1)
--TRANLOGOPTIONS ASMUSER SYS@, ASMPASSWORD # IF LOG FILES ARE STORED IN ASM (10g)
TABLE GG_SOURCE.GG_SYNC_TBL1;


GGSCI (S66CJ187) 5> ADD TRANDATA GG_SOURCE.GG_SYNC_TBL1

Logging of supplemental redo log data is already enabled for table GG_SOURCE.GG_SYNC_TBL1.


GGSCI (S66CJ187) 6> ADD EXTRACT EXT1, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (S66CJ187) 8> ADD EXTTRAIL ./dirdat/XO, EXTRACT EXT1, MEGABYTES 20
EXTTRAIL added.

GGSCI (S66CJ187) 10> START EXTRACT EXT1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (S66CJ187) 19> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:01


Source Pump Process
-------------------

GGSCI (S66CJ187) 20> EDIT PARAMS PMP1


EXTRACT PMP1
RMTHOST 10.14.24.152, MGRPORT 7810
RMTTRAIL ./dirdat/XO
PASSTHRU
TABLE GG_SOURCE.GG_SYNC_TBL1;


GGSCI (S66CJ187) 23> ADD EXTRACT PMP1 , EXTTRAILSOURCE ./dirdat/XO
EXTRACT added.

GGSCI (S66CJ187) 25> ADD RMTTRAIL ./dirdat/XO , EXTRACT PMP1, MEGABYTES 20
RMTTRAIL added.

GGSCI (S66CJ187) 26> START EXTRACT PMP1

Sending START request to MANAGER ...
EXTRACT PMP1 starting


GGSCI (S66CJ187) 41> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:04
EXTRACT     RUNNING     PMP1        00:00:00      00:00:22



-- Check whether the file is transfered to the target server.

/app/oracle/golden_gate/dirdat> ls -lrt
total 0
-rw-rw-rw-   1 oracle     oinstall         0 Jan 16 09:38 XO000000


-- Add some records in the source table.

SQL> begin
  2     for i in 1001..2000 loop
  3             insert into gg_sync_tbl1 values (i, dbms_random.string('U',30), DBMS_RANDOM.STRING('U',30));
  4     end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from gg_source.gg_sync_tbl1;

  COUNT(*)
----------
      2000


-- Register the extract so that rman will not delete the archived log files if the data is not captured by
the extract process.

GGSCI (S66CJ187) 7> REGISTER EXTRACT EXT1 LOGRETENTION

2014-01-16 10:59:48  ERROR   OGG-01754  Cannot register or unregister EXTRACT EXT1 because the Extract is currently running. Stop the Extract and retry the command.


GGSCI (S66CJ187) 8> STOP EXTRACT EXT1

Sending STOP request to EXTRACT EXT1 ...
Request processed.


GGSCI (S66CJ187) 9> REGISTER EXTRACT EXT1 LOGRETENTION

2014-01-16 11:00:07  INFO    OGG-01749  Successfully registered EXTRACT EXT1 to start managing log retention at SCN 59178692.

GGSCI (S66CJ187) 11> START EXT1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


Target Replicat process
-----------------------


GGSCI (S24BF152) 7> DELETE REPLICAT REP1
Deleted REPLICAT REP1.


GGSCI (S24BF152) 2> EDIT PARAMS REP1


REPLICAT REP1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID GGATE, PASSWORD Gate_123
SOURCEDEFS ./dirsql/GG_SYNC_TBL1.sql
DISCARDFILE ./dirrpt/REP1.dsc , append
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
MAP GG_SOURCE.GG_SYNC_TBL1, TARGET GG_TARGET.GG_SYNC_TBL1_TGT, COLMAP (EMPNO=EMPNO ENAME=ENAME);


GGSCI (S24BF152) 8> ADD REPLICAT REP1 , EXTTRAIL ./dirdat/XO
REPLICAT added.


GGSCI (S24BF152) 9> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     REP1        00:00:00      00:00:06


GGSCI (S24BF152) 10> START REPLICAT REP1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (S24BF152) 16> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:17:03      00:00:06


GGSCI (S24BF152) 17> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:04


-- Now there is no lag.. let check the count in the target.


SQL> select count(*) from gg_target.gg_sync_tbl1_tgt;

  COUNT(*)
----------
      2000




III) Initial Load using Direct Bulk load
------------------------------------------------

Source Table :- GG_SYNC_TBL2
Target Table :- GG_SYNC_TBL2


Source
------

SQL> DESC GG_SYNC_TBL2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER
 ENAME                                              VARCHAR2(30)
 DETAILS                                            VARCHAR2(30)



SQL> insert into GG_SYNC_TBL2 select * from GG_SYNC_TBL1;

2000 rows created.

SQL> commit;

Commit complete.


SQL> select count(*) from GG_SYNC_TBL2;

  COUNT(*)
----------
      2000



GGSCI (S66CJ187) 3> dblogin userid ggate password Gate_123
Successfully logged into database.



GGSCI (S66CJ187) 22> EDIT PARAMS DIR_1


EXTRACT DIR_1
USERID GGATE, PASSWORD Gate_123
RMTHOST 10.14.24.152, MGRPORT 7810
RMTTASK REPLICAT, GROUP DIR_R1
TABLE GG_SOURCE.GG_SYNC_TBL2;


GGSCI (S66CJ187) 4> ADD EXTRACT DIR_1, SOURCEISTABLE
EXTRACT added.





Replicat Side
-------------


SQL> DESC GG_SYNC_TBL2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER
 ENAME                                              VARCHAR2(30)
 DETAILS                                            VARCHAR2(30)




GGSCI (S24BF152) 3> EDIT PARAMS DIR_R1


REPLICAT DIR_R1
USERID GGATE , PASSWORD Gate_123
ASSUMETARGETDEFS
BULKLOAD
MAP GG_SOURCE.GG_SYNC_TBL2, TARGET GG_TARGET.GG_SYNC_TBL2;



GGSCI (S24BF152) 8> ADD REPLICAT DIR_R1 SPECIALRUN
REPLICAT added.

-------

Start the extract from the Source side.


GGSCI (S66CJ187) 4> START EXTRACT DIR_1

Sending START request to MANAGER ...
EXTRACT DIR_1 starting


GGSCI (S66CJ187) 8> INFO DIR_1

EXTRACT    DIR_1     Last Started 2014-01-17 10:24   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table GG_SOURCE.GG_SYNC_TBL2
                     2014-01-17 10:24:54  Record 2000
Task                 SOURCEISTABLE



Report file - of Replicat process.

==================================================================

Report at 2014-01-17 10:25:00 (activity since 2014-01-17 10:24:54)

From Table GG_SOURCE.GG_SYNC_TBL2 to GG_TARGET.GG_SYNC_TBL2:
       #                   inserts:      2000
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0


-- Lets check the count.

SQL> select count(*) from gg_target.gg_sync_tbl2;

  COUNT(*)
----------
      2000



IV) Usinig Logdump Utility.
---------------------------

Desc     Command Eg
------------------------------------------------------------------------------------------
Open a Trail File -  open Logdump 1 > open ./dirdat/XO000000
To view Trail file header -  fileheader on Logdump 2 > fileheader on
To view Record header -  ghdr on Logdump 3 > ghdr on
To view Column information -  detail on Logdump 4 > detail on
To View column values -  detail data Logdump 5 > detail data
To control the length of value  -  reclen Logdump 6 > reclen 300
To move to first records -  pos Logdump 7 > pos 0
To move to next record -  next or n Logdump 8 > next (or just n)
To move to the starting point
or good header -  scanforheader (sfh)  Logdump 9 > sfh
To move to previous good header -  sfh prev Logdump 10> sfh prev


Record header - (TransInd   :     .  (x00)

00 - Begining of the transaction.
01 - Middle   of the transaction.
02 - Last     of the transaction.
03 - Whole    of the transaction.


Count records in trail - count Logdump 61 > count



Using Filter Options:-
----------------------

Eg.

SQL> update gg_sync_tbl1 set ename = 'MANZOOR' where empno = 1678;

1 row updated.

SQL> commit

Commit complete.

SQL> begin
  2     for i in 2001..2500 loop
  3             insert into  gg_sync_tbl1 values (i,dbms_random.string('U',30), dbms_random.string('U',30));
  4             commit;
  5     end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> delete from gg_sync_tbl1 where ENAME = 'MANZOOR';

1 row deleted.

SQL> commit;

Commit complete.


-- Lets find out this delete tractions using filter option in trail file.


Logdump 175 >open ./dirdat/XO000000
Current LogTrail is /db/oracle/golden_gate/dirdat/XO000000
Logdump 176 >GHDR ON
Logdump 177 >DETAIL ON
Logdump 178 >DETAIL DATA
Logdump 179 >FILEHEADER ON
Logdump 180 >FILTER INCLUDE FILENAME GG_SOURCE.GG_SYNC_TBL1 # Filter the table name
Logdump 181 >FILTER INCLUDE RECTYPE DELETE # Filter the delete operation
Logdump 182 >SHOW FILTER # Display the current filter condition.

Data filters are ENABLED

Include  Match ANY
 Rectypes   : Delete
 Filename-0 : GG_SOURCE.GG_SYNC_TBL1

Exclude  Match ANY

Logdump 183 >FILTER MATCH ALL # Instruciting to find the records which matches all the condition can use FILTER MATCH ANY for any of the one condition.
Logdump 184 >N # To start search (next)

Filtering suppressed   1001 records
Logdump 185 >NT # The record is not present in this trail file, to continue searching in next trail enter NT (next trail)
LogTrail /db/oracle/golden_gate/dirdat/XO000000 closed
Current LogTrail is /db/oracle/golden_gate/dirdat/XO000001
Logdump 186 >N
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)
RecLength  :    12  (x000c)   IO Time    : 2014/01/18 08:36:30.000.000
IOType     :     3  (x03)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 193305728
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/18 08:36:30.000.000 Delete               Len    12 RBA 108695
Name: GG_SOURCE.GG_SYNC_TBL1
Before Image:                                             Partition 4   G  s
 0000 0008 0000 0004 3136 3738                     | ........1678
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3136 3738                               | ....1678


Filtering suppressed    503 records




## Now we have found the delete operation.


-------------------------------------------------------------------------------

Logging lodump session LOG TO LOG TO MYSESSIONLOG.TXT
Stop logging   session LOG STOP LOG STOP




Reverse Utitlity (Reversal of Transaction)
----------------------------------------------------------

1. Used to extract the before data and backout the changes.

2. It can use either trailfile or transaction logs (archive logs).

Restrictions
------------

1. Long and LOB columns are not reversed if before values are not stored in db.
2. XML TYPE tables are not supported.


Lets backout the happend from the below update statment... around (500 Transactions).


update gg_sync_tbl1 set ename = 'MANZOOR' where empno = 1678;


1) Get the time from and to backout the transaction.


Logdump 194 >open ./dirdat/XO000000
Current LogTrail is /db/oracle/golden_gate/dirdat/XO000000
Logdump 195 >GHDR ON
Logdump 196 >DETAIL ON
Logdump 197 >DETAIL DATA
Logdump 198 >FILEHEADER ON
Logdump 199 >FILTER INCLUDE RECTYPE 15; FILTER INCLUDE FILENAME GG_SOURCE.GG_SYNC_TBL1; FILTER MATCH ALL
Logdump 200 >SHOW FILTER

Data filters are ENABLED

Include  Match ALL
 Rectypes   : FieldComp
 Filename-0 : GG_SOURCE.GG_SYNC_TBL1

Exclude  Match ANY

Logdump 201 >
Logdump 201 >n

Filtering suppressed   1001 records
Logdump 202 >nt
LogTrail /db/oracle/golden_gate/dirdat/XO000000 closed
Current LogTrail is /db/oracle/golden_gate/dirdat/XO000001
Logdump 203 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    65  (x0041)   IO Time    : 2014/01/17 12:27:51.000.000
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 60374144
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/17 12:27:51.000.000 FieldComp            Len    65 RBA 1012
Name: GG_SOURCE.GG_SYNC_TBL1
After  Image:                                             Partition 4   G  s
 0000 0008 0000 0004 3136 3738 0001 000b 0000 0007 | ........1678........
 4d41 4e5a 4f4f 5200 0200 2200 0000 1e52 4141 544c | MANZOOR..."....RAATL
 4647 564f 4258 534b 5044 5447 4842 4643 564d 4e44 | FGVOBXSKPDTGHBFCVMND
 4b56 5656 52                                      | KVVVR
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3136 3738                               | ....1678
Column     1 (x0001), Len    11 (x000b)
 0000 0007 4d41 4e5a 4f4f 52                       | ....MANZOOR
Column     2 (x0002), Len    34 (x0022)
 0000 001e 5241 4154 4c46 4756 4f42 5853 4b50 4454 | ....RAATLFGVOBXSKPDT
 4748 4246 4356 4d4e 444b 5656 5652                | GHBFCVMNDKVVVR




The Time of the  transaction is 2014/01/17 12:27:51.000.000
The time of last transaction is 2014/01/18 08:36:30.000.000

Lets backuout all the transaction happend between these time.



Process
-------

a) Create a parameter file for extract process.
b) Run the reverse utility.
c) Run replicat process.


Parameter file (Pre OGG 11.2 )


SPECIALRUN, TRANLOG
USERID GGATE Password Gate_123
BEGIN 2014-01-17 12:27:51
END   2014-01-18 08:36:30
GETUPDATEBEFORES
NOCOMPRESSDELETES
--RMTHOST 10.41.66.187  MGRPORT 7810  # IF the file has to be generated on remote system
--RMTFILE ./dirdat/REV.dat      # IF the file has to be generated on remote system
EXTFILE ./dirdat/REV.dat
TABLE GG_SOURCE.GG_SYNC_TBL1;


Run from os shell  ( ./extract paramfile ./dirprm/ext_1.prm)






Pameter file (From OGG 11.2)

GGSCI (S66CJ187) 9> EDIT PARAMS EXT_1


EXTRACT EXT_1
USERID GGATE Password Gate_123
END   2014-01-18 08:36:30
GETUPDATEBEFORES
NOCOMPRESSDELETES
--RMTHOST 10.14.24.152  MGRPORT 7810 # IF the trail file has to be generated on remote system
--RMTTRAIL ./dirdat/RE     # IF the trail file has to be generated on remote system
EXTTRAIL ./dirdat/RE
TABLE GG_SOURCE.GG_SYNC_TBL1;



GGSCI (S66CJ187) 10> ADD EXTRACT EXT_1, TRANLOG, BEGIN 2014-01-17 12:27:00
EXTRACT added.

GGSCI (S66CJ187) 13> ADD EXTTRAIL ./dirdat/RE , extract EXT_1
EXTTRAIL added.


GGSCI (S66CJ187) 14> START EXTRACT EXT_1

Sending START request to MANAGER ...
EXTRACT EXT_1 starting

GGSCI (S66CJ187) 27> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:01
EXTRACT     RUNNING     EXT_1       43:53:36      00:07:40
EXTRACT     RUNNING     PMP1        00:00:00      00:00:06


GGSCI (S66CJ187) 28> INFO EXT_1

EXTRACT    EXT_1     Last Started 2014-01-19 08:29   Status RUNNING
Checkpoint Lag       44:01:18 (updated 00:00:01 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2014-01-17 12:27:51  Seqno 180, RBA 60378112
                     SCN 0.0 (0)


GGSCI (S66CJ187) 30> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:10
EXTRACT     STOPPED     EXT_1       23:52:44      00:00:12
EXTRACT     RUNNING     PMP1        00:00:00      00:00:05


Once the extraction is completed the extract process is stopped.


--Below is the snap from the REPORT FILE.

From Table GG_SOURCE.GG_SYNC_TBL1:
       #                   inserts:       500
       #                   updates:         1
       #                   befores:         1
       #                   deletes:         1
       #                  discards:         0



--Lets review the trail file to find the first record, as per above the first record should be
update.

$ ls -lrt RE*
total 10816
-rw-rw-rw-   1 oracle     oinstall    109018 Jan 19 08:46 RE000000

Logdump 314 >open RE000000
Current LogTrail is /db/oracle/golden_gate/dirdat/RE000000
Logdump 315 >GHDR ON
Logdump 316 >DETAIL ON
Logdump 317 >DETAIL DATA
Logdump 318 >FILEHEADER ON
Logdump 319 >POS 0
Reading forward from RBA 0
Logdump 320 >N
Logdump 321 >N
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)
RecLength  :    88  (x0058)   IO Time    : 2014/01/17 12:27:50.910.232
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x00)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 60374144
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/17 12:27:50.910.232 FieldComp            Len    88 RBA 954
Name: GG_SOURCE.GG_SYNC_TBL1
Before Image:                                             Partition 4   G  b
 0000 0008 0000 0004 3136 3738 0001 0022 0000 001e | ........1678..."....
 4b59 4857 5055 5255 4d43 4551 5a53 544b 5954 5947 | KYHWPURUMCEQZSTKYTYG
 5a50 4e5a 4158 4e5a 424d 0002 0022 0000 001e 5241 | ZPNZAXNZBM..."....RA
 4154 4c46 4756 4f42 5853 4b50 4454 4748 4246 4356 | ATLFGVOBXSKPDTGHBFCV
 4d4e 444b 5656 5652                               | MNDKVVVR
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3136 3738                               | ....1678
Column     1 (x0001), Len    34 (x0022)
 0000 001e 4b59 4857 5055 5255 4d43 4551 5a53 544b | ....KYHWPURUMCEQZSTK
 5954 5947 5a50 4e5a 4158 4e5a 424d                | YTYGZPNZAXNZBM
Column     2 (x0002), Len    34 (x0022)
 0000 001e 5241 4154 4c46 4756 4f42 5853 4b50 4454 | ....RAATLFGVOBXSKPDT
 4748 4246 4356 4d4e 444b 5656 5652                | GHBFCVMNDKVVVR




--- We could see this is the BEFORE image of the update statement we required to rollback the update.

---Check the last statment in the trail..


Logdump 322 >POS LAST
Reading forward from RBA 109018
Logdump 323 >n
Logdump 324 >pos rev
Reading in reverse from RBA 109018
Logdump 325 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)
RecLength  :    65  (x0041)   IO Time    : 2014/01/18 08:36:30.019.919
IOType     :     3  (x03)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 193305728
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/18 08:36:30.019.919 Delete               Len    65 RBA 108826
Name: GG_SOURCE.GG_SYNC_TBL1
Before Image:                                             Partition 4   G  s
 0000 0008 0000 0004 3136 3738 0001 000b 0000 0007 | ........1678........
 4d41 4e5a 4f4f 5200 0200 2200 0000 1e52 4141 544c | MANZOOR..."....RAATL
 4647 564f 4258 534b 5044 5447 4842 4643 564d 4e44 | FGVOBXSKPDTGHBFCVMND
 4b56 5656 52                                      | KVVVR
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3136 3738                               | ....1678
Column     1 (x0001), Len    11 (x000b)
 0000 0007 4d41 4e5a 4f4f 52                       | ....MANZOOR
Column     2 (x0002), Len    34 (x0022)
 0000 001e 5241 4154 4c46 4756 4f42 5853 4b50 4454 | ....RAATLFGVOBXSKPDT
 4748 4246 4356 4d4e 444b 5656 5652                | GHBFCVMNDKVVVR




--- The statment is delete ...



-- Now the extract process extracted the transactions happend during the mentioned time period.
Now we need to reverse the operation to rollback, i.e. insert becomes deletes, delete become inserert,
update become update with before image. This can be done using the REVERSE utility.


Execute the below command to reverse the operation using reverse utility.


$ pwd
/db/oracle/golden_gate/dirdat

$ ls -lrt
total 10816
-rw-rw-rw-   1 oracle     oinstall    189979 Jan 16 11:00 XO000000
-rw-rw-rw-   1 oracle     oinstall   5103031 Jan 17 10:22 XT000000
-rw-rw-rw-   1 oracle     oinstall    108834 Jan 19 08:05 XO000001
-rw-rw-rw-   1 oracle     oinstall      1012 Jan 19 08:05 XO000002
-rw-rw-rw-   1 oracle     oinstall    109018 Jan 19 08:46 RE000000  <--- extracted="" file.="" p="" the="" this="">
$ cd ..

$ ./reverse ./dirdat/RE* ./dirdat/RV*

Oracle GoldenGate Dynamic Rollback
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
HP/UX, IA64, 64bit (optimized) on Apr 23 2012 23:15:02

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


*Warning* Source file contained Deletes which maybe compressed
Reversed ./dirdat/RE000000 to /db/oracle/golden_gate/dirdat/RV000000
Total Data Bytes             44218
  Avg Bytes/Record              87
Delete                           1
Insert                         500
FieldComp                        2
Before Images                    2
After Images                   501


$ cd dirdat

$ ls -lrt
total 11040
-rw-rw-rw-   1 oracle     oinstall    189979 Jan 16 11:00 XO000000
-rw-rw-rw-   1 oracle     oinstall   5103031 Jan 17 10:22 XT000000
-rw-rw-rw-   1 oracle     oinstall    108834 Jan 19 08:05 XO000001
-rw-rw-rw-   1 oracle     oinstall      1012 Jan 19 08:05 XO000002
-rw-rw-rw-   1 oracle     oinstall    109018 Jan 19 08:58 RE000000
-rw-r--r--   1 oracle     oinstall    109018 Jan 19 09:15 RV000000




-- Lets view the content of the trail file..


-- First Record..


Logdump 333 >N
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    65  (x0041)   IO Time    : 2014/01/18 08:36:30.000.000
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 193305728
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/18 08:36:30.000.000 Insert               Len    65 RBA 954
Name: GG_SOURCE.GG_SYNC_TBL1
After  Image:                                             Partition 4   G  s
 0000 0008 0000 0004 3136 3738 0001 000b 0000 0007 | ........1678........
 4d41 4e5a 4f4f 5200 0200 2200 0000 1e52 4141 544c | MANZOOR..."....RAATL
 4647 564f 4258 534b 5044 5447 4842 4643 564d 4e44 | FGVOBXSKPDTGHBFCVMND
 4b56 5656 52                                      | KVVVR
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3136 3738                               | ....1678
Column     1 (x0001), Len    11 (x000b)
 0000 0007 4d41 4e5a 4f4f 52                       | ....MANZOOR
Column     2 (x0002), Len    34 (x0022)
 0000 001e 5241 4154 4c46 4756 4f42 5853 4b50 4454 | ....RAATLFGVOBXSKPDT
 4748 4246 4356 4d4e 444b 5656 5652                | GHBFCVMNDKVVVR



-- As we can see the delete reversed to insert.

-- Next record

Logdump 349 >N
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)
RecLength  :    88  (x0058)   IO Time    : 2014/01/18 08:35:51.000.000
IOType     :     3  (x03)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 193298448
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/18 08:35:51.000.000 Delete               Len    88 RBA 1146
Name: GG_SOURCE.GG_SYNC_TBL1
Before Image:                                             Partition 4   G  s
 0000 0008 0000 0004 3235 3030 0001 0022 0000 001e | ........2500..."....
 4146 4a42 5558 524e 5052 424c 4d5a 4e49 5551 5345 | AFJBUXRNPRBLMZNIUQSE
 5149 4656 5056 594c 5943 0002 0022 0000 001e 5747 | QIFVPVYLYC..."....WG
 4e56 4a4c 4146 4a55 5347 5548 5954 4751 4e52 4248 | NVJLAFJUSGUHYTGQNRBH
 565a 5351 4148 4e54                               | VZSQAHNT
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3235 3030                               | ....2500
Column     1 (x0001), Len    34 (x0022)
 0000 001e 4146 4a42 5558 524e 5052 424c 4d5a 4e49 | ....AFJBUXRNPRBLMZNI
 5551 5345 5149 4656 5056 594c 5943                | UQSEQIFVPVYLYC
Column     2 (x0002), Len    34 (x0022)
 0000 001e 5747 4e56 4a4c 4146 4a55 5347 5548 5954 | ....WGNVJLAFJUSGUHYT
 4751 4e52 4248 565a 5351 4148 4e54                | GQNRBHVZSQAHNT



-- Insert reversed to delete.



--Last record.

Logdump 352 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    88  (x0058)   IO Time    : 2014/01/17 12:27:51.000.000
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x02)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        180       AuditPos   : 60374144
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/01/17 12:27:51.000.000 FieldComp            Len    88 RBA 108802
Name: GG_SOURCE.GG_SYNC_TBL1
After  Image:                                             Partition 4   G  e
 0000 0008 0000 0004 3136 3738 0001 0022 0000 001e | ........1678..."....
 4b59 4857 5055 5255 4d43 4551 5a53 544b 5954 5947 | KYHWPURUMCEQZSTKYTYG
 5a50 4e5a 4158 4e5a 424d 0002 0022 0000 001e 5241 | ZPNZAXNZBM..."....RA
 4154 4c46 4756 4f42 5853 4b50 4454 4748 4246 4356 | ATLFGVOBXSKPDTGHBFCV
 4d4e 444b 5656 5652                               | MNDKVVVR
Column     0 (x0000), Len     8 (x0008)
 0000 0004 3136 3738                               | ....1678
Column     1 (x0001), Len    34 (x0022)
 0000 001e 4b59 4857 5055 5255 4d43 4551 5a53 544b | ....KYHWPURUMCEQZSTK
 5954 5947 5a50 4e5a 4158 4e5a 424d                | YTYGZPNZAXNZBM
Column     2 (x0002), Len    34 (x0022)
 0000 001e 5241 4154 4c46 4756 4f42 5853 4b50 4454 | ....RAATLFGVOBXSKPDT
 4748 4246 4356 4d4e 444b 5656 5652                | GHBFCVMNDKVVVR



-- Update has been updated with old value.



-- Setup a replicat process to replicat these changes.


GGSCI (S66CJ187) 4> EDIT PARAMS REP_1


REPLICAT REP_1
END RUNTIME
USERID GGATE PASSWORD Gate_123
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rep_1.dsc, append
MAP GG_SOURCE.GG_SYNC_TBL1, TARGET GG_SOURCE.GG_SYNC_TBL1;


GGSCI (S66CJ187) 5> ADD REPLICAT REP_1, EXTTRAIL ./dirdat/RV
REPLICAT added.



GGSCI (S66CJ187) 6> START REPLICAT REP_1

Sending START request to MANAGER ...
REPLICAT REP_1 starting


GGSCI (S66CJ187) 10> INFO REP_1

REPLICAT   REP_1     Last Started 2014-01-19 09:27   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:36 ago)
Log Read Checkpoint  File ./dirdat/RV000000
                     2014-01-17 12:27:51.000000  RBA 109018




--- Extract from report file.


=============================================================
From Table GG_SOURCE.GG_SYNC_TBL1 to GG_SOURCE.GG_SYNC_TBL1:
       #                   inserts:         1
       #                   updates:         1
       #                   deletes:       500
       #                  discards:         0
=============================================================

As we can see from the snap above,

-- 500 insert becomes deletes
-- 1 delete becomes   insert
-- 1 update become update with before image.

-- Lets look at the db.


SQL> select count(*) from gg_source.gg_sync_tbl1;

  COUNT(*)
----------
      2000


- TRANLOG specifies the transaction log as the data source.
- GETUPDATEBEFORES is used to include before images of update records, which contain record details before an update (as opposed to after images).
- NOCOMPRESSDELETES causes Extract to send all column data to the output, instead of sending only the primary key. It enables deletes to be converted back to inserts.
- END RUNTIME causes the Extract or Replicat to terminate when it reaches process startup time.


======================================================================================================================================================================



V) Extract Process Defaults:-

a. Commited data only
b. Full images of inserts.
c. Only primary key and changed columns for update.
d. Only primary key for delete.
e. Only after image of update.

VI) Extract Parameter catagoreis

a) General
b) Processing Methods - When to start and end the process.
c) Database login
d) Selecting and Mapping data.
e) Routing Data
f) Formatting Data
g) Custom Processing  - Whether to invoke a user exit routine or a macro
h) Reporting
i) Error Handling - Contains records that cannot be processed and error handling for DDL extraction
j) Tuning  - Controls how long data is buffered before writing to a trail, and memory allocations.
k) Maintenance
l) Security


VII) Options on TRANSLOGOPTION.

a) TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT log_%t_%s_%r.arc   --> To specify differet format for archivelog.
b) TRANLOGOPTIONS ALTARCHIVELOGDEST /oradata/archive/log2 --> To specify differnet archive log location.
c) TRANLOGOPTIONS ARCHIVEDLOGONLY --> To specify gg to read only from the archive log (ALO)
d) TRANLOGOPTIONS EXCLUDEUSER ggsrep --> To execlude particular user from extract.
e) TRANLOGOPTIONS EXCLUDETRANS "ggs_repl" --> To specify the transaction name of the Replicat database user so that those transactions are not captured by Extract.


VIII) DBOPTIONS. (Database options).

a) SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERS  - For Oracle databases to turn off triggers on the target database.
b) DEFERREFCONST -  Defer deferrable constraints.
c) FETCHBATCHSIZE

======================================================================================================================================================================


IX) Data Manipulation and Mapping
---------------------------------

a) Done by TABLE and MAP parameters.


Parameter

TABLE / MAP | Selects
WHERE | Table, Row,
FILTER |  Row operation, Range.


TABLE COLS | Columns
COLSEXCEPT |



Table selection -> MAP gg_source.gg_sync_tbl1, TARGET gg_target.gg_sync_tbl1_tgt;
Row   Selection -> WHERE (EMPNAME = "MANZOOR");
Operation Selection -> FILTER (ON UPDATE, ON DELETE, amount > 0);
Column Selection -> TABLE gg_source.gg_sync_tbl1 COLSEXCEPT (ENAME);


WHERE CLUASE
------------
-- Can appear on TABLE or MAP
-- Cannot use for arithmetic operation and refer to trail header and user token values.
-- Can perform evaluation as below.

Element Description Defined by Example
------------------- ------------------------ ---------------------
Columns COL_NAME ENAME
Comparision operator >, <, >=, <=, =, <> WHERE (ENAME = "MANZOOR")
Numeric values 38383.00 WHERE (EMPNO = 2)
Literal String "NAME", "CAR" WHERE (ENAME = "MANZOOR")
Field tests @NULL, @PRESENT, @ABSENT WHERE (ENAME = @NULL)  / WHERE ( EMPNO @PRESENT AND EMPNO <> @NULL)
Logical operator AND, OR WHERE ( EMPNO @PRESENT AND EMPNO <> @NULL)


FILTER CLAUSE
-------------
-- Used for complex include / exclude data selection.
-- Can deploy oracle GG built in function.
-- Can use multiple filter in one statement, if one filter fails the whole filter fails.
-- Include multiple option clause eg (ON INSERT / ON DELETE)
-- Raise a user defined error for error handling and processing. (FILTER RAISEERROR)

Eg.

FILTER ((PRODUCT_PRICE * PRODUCT_AMOUNT) > 10000); -- The following example includes rows where the price multiplied by the amount exceeds 10,000.
FILTER (@STRFIND(NAME, "JOE") > 0); -- The following example includes rows containing the string “JOE”:
FILTER (ON UPDATE, ON DELETE, @COMPUTE (PRODUCT_PRICE * PRODUCT_AMOUNT) > 10000); -- Executes the FILTER clause for both updates and deletes, but not inserts.



RANGE CLAUSE
------------
-- Used to divide the workload into mutiple randomly distributed data.
-- Same row will always processed by the same process group , it uses hash against the primary key or user defined columns.

Eg.

Replicat #1

MAP SALES.ACCOUNT,
TARGET SALES.ACCOUNT, FILTER (@RANGE (1,3));

Replicat #2
MAP SALES.ACCOUNT,
TARGET SALES.ACCOUNT, FILTER (@RANGE (2,3));

Replicat #3
MAP SALES.ACCOUNT,
TARGET SALES.ACCOUNT, FILTER (@RANGE (3,3));

Three Replicat processes, with each Replicat group processing one-third of the data in the GoldenGate trail based on the primary key.



TABLE SALES.ACCOUNT, FILTER (@RANGE (1,3,REP_ID));
TABLE SALES.ACCOUNT, FILTER (@RANGE (2,3,REP_ID));
TABLE SALES.ACCOUNT, FILTER (@RANGE (3,3,REP_ID));


Related by REP_ID, require three Replicats to handle the
transaction volumes.
By hashing the REP_ID column, related rows will always be processed to the same Replicat.



Building History
----------------

This is to record all the changes happend on the table, all the changes will be applied as an new insert using
INSERTALLRECORDS parameter.




This example uses special values to build a history of operations data:

INSERTALLRECORDS
MAP SALES.ACCOUNT, TARGET REPORT.ACCTHISTORY,
COLMAP (USEDEFAULTS,
TRAN_TIME = @GETENV("GGHEADER","COMMITTIMESTAMP"),
OP_TYPE = @GETENV("GGHEADER", "OPTYPE"),
BEFORE_AFTER_IND =
@GETENV("GGHEADER", "BEFOREAFTERINDICATOR"),
);



Oralce GG builtin Functions:-
---------------------------

FUNCTION  DEFINITION Example
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CASE   - Allows user to select a value depending on a series of value tests
EVAL - Allows a user to select a value depending on a series of independent tests
IF - Conditional checking AMT_COL = @IF (AMT > 0, AMT, 0)
COLSTAT - Tests whether a column value is missing, NULL, or invalid
COLTEST - Tests whether a column value is present, missing, NULL, or invalid
VALONEOF - STATE_COL = @IF (@VALONEOF ("CHENNAI","MADURAI"),"TN","OTHERS")
DATE - Returns date in variety of formats.
DATEDIFF - Returns diffenet between two date and times.
DATENOW - Returns current date.
COMPUTE - Returns the result of an arithmetic expression
NUMBIN - Convert binary string to number.
NUMSTR - Convert string to number.
STRCAT - Concatenates two or more strings. FULL_NAME = @STRCAT (LAST_NAME,",",FIRST_NAME);
STRCMP - Compares two strings to determine whether they are equal, or whether the first is less or greater than the second.
STREQ - Tests to see whether two strings are equal. Returns 1 for equal and 0 if not equal.
STREXT - Extracts selected characters from a string COUNTRY_CODE = @STREXT (PHONE, 1,2);
STRFIND - Finds the occurrence of a string within a string
STRLEN - Returns the length of a string.
STRLTRIM - Trims leading spaces in a column
STRNCAT - Concatenates one or more strings up to a specified number of characters per string
STRNCMP - Compares two strings up to a certain number of characters
STRNUM - Converts a number into a string, with justification and zerofill options
STRRTRIM - Trims trailing spaces in a column
STRSUB - Substitutes one string for another within a column
STRTRIM - Trims both leading and trailing spaces in a column
STRUP - Changes a string to uppercase
BINARY - Keeps source data in its original binary format in the target when source column is defined as character
BINTOHEX - Converts a binary string to a hexadecimal string
GETENV - Returns information on the GoldenGate environment, trail file header, trail record header, last replicated operation
 and lag. Can retrieve the commit timestamp in local time or GMT.
GETVAL - Extracts parameters from a stored procedure as input to a FILTER or COLMAP clause
HEXTOBIN - Converts a hexadecimal string to a binary string
HIGHVAL,LOWVAL  - Emulate COBOL functions that allow you to set a numeric limit on string or binary data types
RANGE - Divides a workload into multiple groups of data, while ensuring the same row will always be sent to the same process.
 RANGE uses a hash against primary key or user-defined columns.
TOKEN - Maps environmental values that are stored in the user token area to the target column.




SQLEXEC
-------

The SQLEXEC parameter extends GoldenGate capabilities by enabling Extract and Replicat to communicate with the
database through SQL queries or run stored procedures.
SQLEXEC also extends data integration beyond what can be done with GoldenGate functions.

A) Execute a stored procedure or SQL by query using the SQLEXEC clause of the TABLE or MAP parameter.
B) Use SQLEXEC at the root level (without input/output parameters) to call a stored procedure, run a SQL query, or issue a database command.

EG.

CREATE OR REPLACE PROCEDURE LOOKUP (CODE_PARAM IN VARCHAR2, DESC_PARAM OUT VARCHAR2) AS
BEGIN
SELECT DESC_COL INTO DESC_PARAM FROM LOOKUP_TABLE WHERE CODE_COL = CODE_PARAM;
END;

SYNTAX FOR SQLEXEC WITH STORED PRODCEDURE

SQLEXEC (SPNAME ,
[ID {PARAMS | NOPARAMS})


---------------------------------------------------------------------------------------


MAP HR.ACCOUNT, TARGET HR.NEWACCT, &
SQLEXEC (SPNAME lookup, PARAMS (code_param = account_code)), &
COLMAP (USEDEFAULTS, newacct_id = account_id, newacct_val = @GETVAL(lookup.desc_param));

---------------------------------------------------------------------------------------

Maps data from the ACCOUNT table to the NEWACCT table
When processing any rows from ACCOUNT, Extract  performs the LOOKUP stored procedure prior to executing the column map
Maps values returned in desc_param to the newacct_val column using the @GETVAL function.


the above result can also be achived by directly executing the query instead of stored procedure.

SYNTAX for SQLEXEC FOR EXECUTING THE QUERY.

SQLEXEC (ID , QUERY " ",
{PARAMS | NOPARAMS})

---------------------------------------------------------------------------------------
MAP HR.ACCOUNT, TARGET HR.NEWACCT, &
SQLEXEC (id lookup, &
query "select desc_param from lookup_table
where code_col = :code_param", &
PARAMS (code_param = account_code)), &
COLMAP (USEDEFAULTS, newacct_id = account_id,
newacct_val = @GETVAL(lookup.desc_param));
---------------------------------------------------------------------------------------


* When SQLEXEC is used as a stand-alone parameter statement in the Extract or Replicat parameter file, it can
execute a stored procedure, query, or database command.
* For these situations, SQLEXEC does not need to be tied to a specific table and can be used to perform general SQL operations.

SQLEXEC "EXEC SPNAME()"
SQLEXEC ""
SQLEXEC ""


Lets create an example using above method.


-- Source Tbl :-   SALES_MASTER
-- Target Tbls :-  SALES_INDIA, SALES_MALAYSIA,TOTAL_SALES_INDIA, TOTAL_SALES_MALAYSIA, EMP_DETAILS


Here we have one source table and it will get replicated to four target tables.


Source Table
------------

SQL> DESC GG_SOURCE.SALES_MASTER;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SOLD_AMNT                                          NUMBER
 COUNTRY                                            VARCHAR2(30)
 SALE_DATE                                          DATE





Target Tables:-
--------------


SQL> DESC GG_TARGET.SALES_INDIA;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SOLD_AMT                                           NUMBER
 SALE_DATE                                          DATE

SQL> DESC GG_TARGET.SALES_MALAYSIA;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SOLD_AMT                                           NUMBER
 SALE_DATE                                          DATE

SQL> DESC GG_TARGET.TOTAL_SALES_INDIA;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SALES_PROFIT                                       NUMBER
 SALES_DATE                                         DATE


SQL> DESC GG_TARGET.TOTAL_SALES_MALAYSIA;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SALES_PROFIT                                       NUMBER
 SALES_DATE                                         DATE

SQL> DESC GG_TARGET.EMP_DETAILS ;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 EMP_NAME                                           VARCHAR2(64)
 SALES_COMMISION                                    NUMBER
 SALES_DATE                                         DATE



Setup the Extract Process.
--------------------------

GGSCI (S66CJ187) 8> EDIT PARAMS EXT_SALE


EXTRACT EXT_SALE
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID GGATE PASSWORD Gate_123
EXTTRAIL ./dirdat/SA
TABLE GG_SOURCE.SALES_MASTER;


GGSCI (S66CJ187) 11> ADD EXTRACT EXT_SALE , TRANLOG , BEGIN NOW
EXTRACT added.


GGSCI (S66CJ187) 12> ADD EXTTRAIL ./dirdat/SA, EXTRACT EXT_SALE
EXTTRAIL added.


GGSCI (S66CJ187) 13> START EXTRACT EXT_SALE

Sending START request to MANAGER ...
EXTRACT EXT_SALE starting

GGSCI (S66CJ187) 58> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:05
EXTRACT     RUNNING     EXT_SALE    00:00:00      00:00:04
EXTRACT     RUNNING     PMP1        00:00:00      00:00:08



Extract pump process
---------------------

GGSCI (S66CJ187) 71> EDIT PARAMS PMP_SALE


EXTRACT PMP_SALE
RMTHOST 10.14.24.152, MGRPORT 7810
RMTTRAIL ./dirdat/SA
PASSTHRU
TABLE GG_SOURCE.SALES_MASTER;


GGSCI (S66CJ187) 60> ADD EXTRACT PMP_SALE, EXTTRAILSOURCE ./dirdat/SA
EXTRACT added.


GGSCI (S66CJ187) 61> ADD RMTTRAIL ./dirdat/SA, EXTRACT PMP_SALE
RMTTRAIL added.

GGSCI (S66CJ187) 63> START EXTRACT PMP_SALE

Sending START request to MANAGER ...
EXTRACT PMP_SALE starting


Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:02
EXTRACT     RUNNING     EXT_SALE    00:00:00      00:00:10
EXTRACT     RUNNING     PMP1        00:00:00      00:00:07
EXTRACT     RUNNING     PMP_SALE    00:00:00      00:02:32



Replicat Process.
-----------------

-- Source Tbl :-   SALES_MASTER
-- Target Tbls :-  SALES_INDIA, SALES_MALAYSIA,TOTAL_SALES_INDIA, TOTAL_SALES_MALAYSIA, EMP_DETAILS


Lets create 5 Replicate process.

Process 1 :- RSAL_IND
---------------------

Source -

SQL> DESC GG_SOURCE.SALES_MASTER;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SOLD_AMNT                                          NUMBER
 COUNTRY                                            VARCHAR2(30)
 SALE_DATE                                          DATE





Target Tables:-
--------------


SQL> DESC GG_TARGET.SALES_INDIA;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 SOLD_PRODUCT                                       VARCHAR2(30)
 SOLD_PRODUCT_ID                                    NUMBER
 SOLD_AMT                                           NUMBER
 SALE_DATE                                          DATE


a) Both the source and target columns are not same, lets use the defgen utility to create a source defintion.


GGSCI (S66CJ187) 1> EDIT PARAMS DEFGEN

DEFSFILE ./dirsql/sales_master.sql
USERID GGATE@NPTLPVT password Gate_123
TABLE  GG_SOURCE.SALES_MASTER;


$ defgen PARAMFILE ./dirprm/defgen.prm REPORTFILE ./dirrpt/defgen.rpt

$ cat sales_master.sql
*+- Defgen version 2.0, Encoding hp-roman8
*
* Definitions created/modified  2014-01-20 15:13
*
*  Field descriptions for each column entry:
*
*     1    Name
*     2    Data Type
*     3    External Length
*     4    Fetch Offset
*     5    Scale
*     6    Level
*     7    Null
*     8    Bump if Odd
*     9    Internal Length
*    10    Binary Length
*    11    Table Length
*    12    Most Significant DT
*    13    Least Significant DT
*    14    High Precision
*    15    Low Precision
*    16    Elementary Item
*    17    Occurs
*    18    Key Column
*    19    Sub Data Type
*
Database type: ORACLE
Character set ID: UTF-8
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
*
Definition for table GG_SOURCE.SALES_MASTER
Record length: 262
Syskey: 0
Columns: 6
EMP_ID            64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
SOLD_PRODUCT      64     30       56  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
SOLD_PRODUCT_ID   64     50       92  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
SOLD_AMNT         64     50      148  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
COUNTRY           64     30      204  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
SALE_DATE        192     19      240  0  0 1 0     19     19     19 0 5 0 0 1    0 1 0
End of definition


-- scp the file to the target server.


SQL> DESC GG_TARGET.EMP_DETAILS ;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             NUMBER
 EMP_NAME                                           VARCHAR2(64)
 SALES_COMMISION                                    NUMBER
 SALES_DATE                                         DATE



SQL> create or replace procedure get_emp_name ( v_emp_id in number, v_emp_fir_name out varchar2, v_emp_sec_name out varchar2) as
    begin
       select EMP_FIRST_NAME, EMP_LAST_NAME into v_emp_fir_name , v_emp_sec_name from gg_target.emp_names where emp_id = v_emp_id;
    end;
    /


> EDIT PRAMS RSAL_IND

REPLICAT  RSAL_IND
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID GGATE Password Gate_123
SOURCEDEFS ./dirsql/sales_master.sql
DISCARDFILE ./dirrpt/RSAL_IND.dsc, append
MAP GG_SOURCE.SALES_MASTER, TARGET GG_TARGET.SALES_INDIA, &
    COLMAP (USEDEFAULTS, SOLD_AMT = SOLD_AMNT) , &
    FILTER  ( @STRCMP(@STRUP(COUNTRY), "INDIA") = 0);
MAP GG_SOURCE.SALES_MASTER, TARGET GG_TARGET.SALES_MALAYSIA, &
    COLMAP  (USEDEFAULTS, SOLD_AMT = SOLD_AMNT), &
    FILTER  ( @STRCMP(@STRUP(COUNTRY), "MALAYSIA") = 0);
MAP GG_SOURCE.SALES_MASTER, TARGET GG_TARGET.TOTAL_SALES_INDIA, &
    COLMAP  (USEDEFAULTS, SALES_DATE = SALE_DATE, SALES_PROFIT = @COMPUTE (SOLD_AMNT * 0.1)), &
    FILTER  (@STRCMP(@STRUP(COUNTRY), "INDIA") = 0);
MAP GG_SOURCE.SALES_MASTER, TARGET GG_TARGET.TOTAL_SALES_MALAYSIA, &
    COLMAP  (USEDEFAULTS, SALES_DATE = SALE_DATE, SALES_PROFIT = @COMPUTE (SOLD_AMNT * 0.1)), &
    FILTER  (@STRCMP(@STRUP(COUNTRY), "MALAYSIA") = 0);
MAP GG_SOURCE.SALES_MASTER, TARGET GG_TARGET.EMP_DETAILS, &
    SQLEXEC  (SPNAME GET_EMP_NAME, PARAMS ( V_EMP_ID = EMP_ID)), &
    COLMAP   (USEDEFAULTS, SALES_DATE = SALE_DATE, EMP_NAME = @STRCAT (@GETVAL(GET_EMP_NAME.V_EMP_FIR_NAME), "," , @GETVAL (GET_EMP_NAME.V_EMP_SEC_NAME)), &
              SALES_COMMISION = @COMPUTE (SOLD_AMNT * 0.02));



> ADD REPLICAT RSAL_IND, EXTTRAIL ./dirdat/SA

> START REPLICAT RSAL_IND



-- Lets test this.. insert 3 records as below in source..



SQL> insert into sales_master values (1001,'SONY PC','2001',2000,'INDIA',SYSDATE);

1 row created.

SQL> insert into sales_master values (1002,'SONY TV',2002,1800,'MALAYSIA',SYSDATE);


1 row created.

SQL> SQL> insert into sales_master values (1003,'SONY IPAD',2003,1000,'india',SYSDATE);

1 row created.

SQL>  insert into sales_master values (1004,'SONY XBOX',2004,1500,'malaysia',sysdate);

1 row created.

SQL> commit;

Commit complete.


-- Below is the output on the five replicated tables.



SQL> select * from gg_target.emp_details;

    EMP_ID EMP_NAME                                                         SALES_COMMISION SALES_DAT
---------- ---------------------------------------------------------------- --------------- ---------
      1001 Manzoor,Ahamed                                                                40 20-JAN-14
      1002 Bhaskar,Suthradhar                                                            36 20-JAN-14
      1003 Ramesh,Ramasamy                                                               20 20-JAN-14
      1004 Jameel,Ahamed                                                                 30 20-JAN-14

SQL> select * from gg_target.sales_india;

    EMP_ID SOLD_PRODUCT                   SOLD_PRODUCT_ID   SOLD_AMT SALE_DATE
---------- ------------------------------ --------------- ---------- ---------
      1001 SONY PC                                   2001       2000 20-JAN-14
      1003 SONY IPAD                                 2003       1000 20-JAN-14

SQL> select * from gg_target.sales_malaysia;

    EMP_ID SOLD_PRODUCT                   SOLD_PRODUCT_ID   SOLD_AMT SALE_DATE
---------- ------------------------------ --------------- ---------- ---------
      1002 SONY TV                                   2002       1800 20-JAN-14
      1004 SONY XBOX                                 2004       1500 20-JAN-14

SQL> select * from gg_target.total_sales_india;

SOLD_PRODUCT                   SOLD_PRODUCT_ID SALES_PROFIT SALES_DAT
------------------------------ --------------- ------------ ---------
SONY PC                                   2001          200 20-JAN-14
SONY IPAD                                 2003          100 20-JAN-14

SQL> select * from gg_target.total_sales_malaysia;

SOLD_PRODUCT                   SOLD_PRODUCT_ID SALES_PROFIT SALES_DAT
------------------------------ --------------- ------------ ---------
SONY TV                                   2002          180 20-JAN-14
SONY XBOX                                 2004          150 20-JAN-14




============================================================================================================================