Manzoor's Blog - Stuffs on Oracle Database Administration: 2014

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

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

Manzoor's Blog - Stuffs on Oracle Database Administration