Wednesday, June 12, 2013

Why my new Disk Group is not getting mounted automatically when ASM instance starts?

Today I faced the issue that a new ASM Disk Group created in instance +ASM1 is not getting automatically mounted in instance +ASM2.

The first step was to check DB dependencies:

[oracle@ol5-112-rac2 trace]$ srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/RAC/spfileRAC.ora
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RAC
Database instances: RAC1,RAC2
Disk Groups: DATA
Services: reports
Database is administrator managed

You can see that only dependent Disk Groups are ‘DATA’.

Let us add ‘FRA’ also as dependent Disk Group:

[oracle@ol5-112-rac2 trace]$ srvctl modify database -d RAC -a 'DATA,FRA'
[oracle@ol5-112-rac2 trace]$ srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/RAC/spfileRAC.ora
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RAC
Database instances: RAC1,RAC2
Disk Groups: DATA,FRA
Services: reports
Database is administrator managed

The next step is to add the Disk Groups to ASM_DISKGROUPS parameter:

SQL> ALTER SYSTEM SET ASM_DISKGROUPS='DATA','FRA' scope=both sid='+ASM1';

System altered.

SQL> ALTER SYSTEM SET ASM_DISKGROUPS='DATA','FRA' scope=both sid='+ASM2';

System altered.

After restarting +ASM2 instance we can see that FRA Disk Group is mounted automatically:

[oracle@ol5-112-rac2 trace]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 12 15:44:44 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> set lines 1000
SQL> select * from V$ASM_DISKGROUP;

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY                                                DATABASE_COMPATIBILITY                                       V
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
           1 DATA                                   512       4096              1048576 MOUNTED     EXTERN      15342      12616           0         2726                       0          12616     0 11.2.0.0.0                                                   10.1.0.0.0                                                   N
           2 FRA                                    512       4096              1048576 MOUNTED     EXTERN       6133       5962           0          171                       0           5962     0 11.2.0.0.0                                                   10.1.0.0.0                                                   N

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Real Application Clusters and Automatic Storage Management options

Thanks,

Alfredo

ORA-15097: cannot SHUTDOWN ASM instance with connected client

In 11g R2 Clusterware configurations the OCR is located in ASM disk, that’s why ASM can’t be shutdown while the cluster is running.  You have to shutdown your cluster in order to shutdown ASM instance.

Oracle support note 984663.1

Here’s a good blog about this:

Example, how to shutdown ASM instance on node ol5-112-rac1:

[oracle@ol5-112-rac1 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.FRA.dg
               OFFLINE OFFLINE      ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.LISTENER.lsnr
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.asm
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2             Started
ora.eons
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.gsd
               OFFLINE OFFLINE      ol5-112-rac1
               OFFLINE OFFLINE      ol5-112-rac2
ora.net1.network
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.ons
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       ol5-112-rac1
ora.oc4j
      1        OFFLINE OFFLINE
ora.ol5-112-rac1.vip
      1        ONLINE  ONLINE       ol5-112-rac1
ora.ol5-112-rac2.vip
      1        ONLINE  ONLINE       ol5-112-rac2
ora.rac.db
      1        ONLINE  ONLINE       ol5-112-rac2             Open
      2        ONLINE  ONLINE       ol5-112-rac1
ora.rac.reports.svc
      1        ONLINE  ONLINE       ol5-112-rac1
ora.scan1.vip
      1        ONLINE  ONLINE       ol5-112-rac1
[oracle@ol5-112-rac1 ~]$

Shutdown RAC1 instance first:

[oracle@ol5-112-rac1 ~]$ srvctl stop instance –d RAC –I RAC1 –o immediate

Then proceed to shutdown cluster (root required):

[oracle@ol5-112-rac1 ~]$ su -
[root@ol5-112-rac1 bin]#
[root@ol5-112-rac1 bin]# ./crsctl stop cluster
CRS-2673: Attempting to stop 'ora.crsd' on 'ol5-112-rac1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'ol5-112-rac1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'ol5-112-rac1'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'ol5-112-rac1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'ol5-112-rac1' succeeded
CRS-2673: Attempting to stop 'ora.ol5-112-rac1.vip' on 'ol5-112-rac1'
CRS-2677: Stop of 'ora.ol5-112-rac1.vip' on 'ol5-112-rac1' succeeded
CRS-2672: Attempting to start 'ora.ol5-112-rac1.vip' on 'ol5-112-rac2'
CRS-2676: Start of 'ora.ol5-112-rac1.vip' on 'ol5-112-rac2' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'ol5-112-rac1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'ol5-112-rac1'
CRS-2677: Stop of 'ora.asm' on 'ol5-112-rac1' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'ol5-112-rac1'
CRS-2673: Attempting to stop 'ora.eons' on 'ol5-112-rac1'
CRS-2677: Stop of 'ora.ons' on 'ol5-112-rac1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'ol5-112-rac1'
CRS-2677: Stop of 'ora.net1.network' on 'ol5-112-rac1' succeeded
CRS-2677: Stop of 'ora.eons' on 'ol5-112-rac1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'ol5-112-rac1' has completed
CRS-2677: Stop of 'ora.crsd' on 'ol5-112-rac1' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'ol5-112-rac1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'ol5-112-rac1'
CRS-2673: Attempting to stop 'ora.evmd' on 'ol5-112-rac1'
CRS-2673: Attempting to stop 'ora.asm' on 'ol5-112-rac1'
CRS-2677: Stop of 'ora.cssdmonitor' on 'ol5-112-rac1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'ol5-112-rac1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'ol5-112-rac1' succeeded
CRS-2677: Stop of 'ora.asm' on 'ol5-112-rac1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'ol5-112-rac1'
CRS-2677: Stop of 'ora.cssd' on 'ol5-112-rac1' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'ol5-112-rac1'
CRS-2677: Stop of 'ora.diskmon' on 'ol5-112-rac1' succeeded
[root@ol5-112-rac1 bin]# ps -ef|grep pmon
root      7072  5722  0 14:45 pts/2    00:00:00 grep pmon
[root@ol5-112-rac1 bin]# ps -ef|grep +ASM1
root      7074  5722  0 14:46 pts/2    00:00:00 grep +ASM1
[root@ol5-112-rac1 bin]#

At this point ASM & Cluster Ready Services managed resources are down.

In order to start them again just need to issue:

[root@ol5-112-rac1 bin]# ./crsctl start cluster
[root@ol5-112-rac1 bin]# exit
[oracle@ol5-112-rac1 ~]$ srvctl start instance –d RAC –I RAC1


Thanks,
Alfredo

Sunday, June 9, 2013

Hands on practice ASM

Today I want to share with you an ASM exercise which includes how to add an additional disk to our VM RAC nodes, prepare the disk, add the disk to ASM configuration and create a new diskgroup using this disk.

The very first step is to have our VM machines turned off. Then go into your VM host server's terminal and get into ASM shared disks location.


alfredo@alfredo-N56VM:~$ cd /u04

alfredo@alfredo-N56VM:/u04$ ls

VirtualBox

alfredo@alfredo-N56VM:/u04$ cd VirtualBox/

alfredo@alfredo-N56VM:/u04/VirtualBox$ ls

ol5-112-rac

alfredo@alfredo-N56VM:/u04/VirtualBox$ cd ol5-112-rac/

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ ls

asm1.vdi  asm2.vdi  asm3.vdi  asm4.vdi


Then create the new VM shared disk and assign it to your VM machines.


alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage createhd --filename asm5.vdi --size 1024 --format VDI --variant Fixed

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Disk image created. UUID: f317a4fb-d132-49fa-8572-76ca7a509e04

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage storageattach ol5-112-rac1 --storagectl "SATA" --port 5 --device 0 --type hdd --medium asm5.vdi --mtype shareable

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage modifyhd asm5.vdi --type shareable

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage storageattach ol5-112-rac2 --storagectl "SATA" --port 5 --device 0 --type hdd --medium asm5.vdi --mtype shareable

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage modifyhd asm5.vdi --type shareable


Now turn your on your node 1, open a terminal as root and format the disk.


[oracle@ol5-112-rac1 ~]$ su -
Password: 
[root@ol5-112-rac1 ~]# cd /dev
[root@ol5-112-rac1 dev]# ls sd*
sda  sda1  sda2  sdb  sdb1  sdc  sdc1  sdd  sdd1  sde  sde1  sdf  sdf1  sdg  sdg1  sdh
[root@ol5-112-rac1 dev]# fdisk /dev/sdh
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1): 
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-130, default 130): 
Using default value 130

Command (m for help): p

Disk /dev/sdh: 1073 MB, 1073741824 bytes
255 heads, 63 sectors/track, 130 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdh1               1         130     1044193+  83  Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.


Turn on the second VM node.
The next step is to add the newly partitioned disk to ASM.


[root@ol5-112-rac1 dev]# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
[root@ol5-112-rac1 dev]# /usr/sbin/oracleasm createdisk DISK5 /dev/sdh1
Writing disk header: done
Instantiating disk: done
[root@ol5-112-rac1 dev]# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
[root@ol5-112-rac1 dev]# exit



At this point the new disk is already added to ASM configuration; let's login to ASM instance and confirm that new disk is present.


[oracle@ol5-112-rac2 ~]$ . oraenv
ORACLE_SID = [RAC2] ? +ASM2
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle
[oracle@ol5-112-rac2 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
ora.LISTENER.lsnr
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
ora.asm
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2             Started             
ora.eons
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
ora.gsd
               OFFLINE OFFLINE      ol5-112-rac1                                 
               OFFLINE OFFLINE      ol5-112-rac2                                 
ora.net1.network
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
ora.ons
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       ol5-112-rac1                                 
ora.oc4j
      1        OFFLINE OFFLINE                                                   
ora.ol5-112-rac1.vip
      1        ONLINE  ONLINE       ol5-112-rac1                                 
ora.ol5-112-rac2.vip
      1        ONLINE  ONLINE       ol5-112-rac2                                 
ora.rac.db
      1        ONLINE  ONLINE       ol5-112-rac2             Open                
      2        ONLINE  ONLINE       ol5-112-rac1                                 
ora.rac.reports.svc
      1        ONLINE  ONLINE       ol5-112-rac1                                 
ora.scan1.vip
      1        ONLINE  ONLINE       ol5-112-rac1                                 
[oracle@ol5-112-rac2 ~]$ sqlplus / as sysasm


SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 9 16:06:27 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> set lines 1000
SQL> column path format a50
SQL> SELECT PATH,NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,OS_MB,TOTAL_MB,FREE_MB FROM V$ASM_DISK;

PATH                                               NAME                           GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    REDUNDA      OS_MB   TOTAL_MB    FREE_MB
-------------------------------------------------- ------------------------------ ------------ ----------- ------- ------------ ------- -------- ------- ---------- ---------- ----------
/dev/oracleasm/disks/DISK5                                                                   0           0 CLOSED  PROVISIONED  ONLINE  NORMAL   UNKNOWN       1019          0          0
/dev/oracleasm/disks/DISK4                         DATA_0003                                 1           3 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4439
/dev/oracleasm/disks/DISK3                         DATA_0002                                 1           2 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4439
/dev/oracleasm/disks/DISK2                         DATA_0001                                 1           1 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4444
/dev/oracleasm/disks/DISK1                         DATA_0000                                 1           0 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4406



You can see the DISK5 present in the ASM system view, the HEADER_STATUS is 'PROVISIONED'. The PROVISIONED status implies that an additional platform-specific action has been taken by an administrator to make the disk available for ASM.

Now, let's drop DISK4 from DATA diskgroup, this will free DISK4 for our new configuration.


SQL> select * from V$ASM_DISKGROUP;

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY            DATABASE_COMPATIBILITY                                        V
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
           1 DATA                                   512       4096 1048576 MOUNTED     EXTERN      20456      17728           0         2728 0          17728             0 11.2.0.0.0               10.1.0.0.0 N



SQL> ALTER DISKGROUP DATA DROP DISK DATA_0003;

Diskgroup altered.

SQL> SELECT PATH,NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,OS_MB,TOTAL_MB,FREE_MB FROM V$ASM_DISK;

PATH                                               NAME                           GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    REDUNDA      OS_MB   TOTAL_MB    FREE_MB
-------------------------------------------------- ------------------------------ ------------ ----------- ------- ------------ ------- -------- ------- ---------- ---------- ----------
/dev/oracleasm/disks/DISK5                                                                   0           0 CLOSED  PROVISIONED  ONLINE  NORMAL   UNKNOWN       1019          0          0
/dev/oracleasm/disks/DISK4                         DATA_0003                                 1           3 CACHED  MEMBER       ONLINE  DROPPING UNKNOWN       5114       5114       4546
/dev/oracleasm/disks/DISK3                         DATA_0002                                 1           2 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4404
/dev/oracleasm/disks/DISK2                         DATA_0001                                 1           1 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4409
/dev/oracleasm/disks/DISK1                         DATA_0000 1           0 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114 5114       4369


You can see the STATE of DISK4 is DROPPING, in this stage ASM will rebalance all the data from DISK4 into the disk of diskgroup DATA. In order to speed this operation we are going to modify the rebalance power of the diskgroup.


SQL> ALTER DISKGROUP DATA REBALANCE POWER 5;

Diskgroup altered.

SQL> SELECT PATH,NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,OS_MB,TOTAL_MB,FREE_MB FROM V$ASM_DISK;


PATH                                               NAME                           GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    REDUNDA      OS_MB   TOTAL_MB    FREE_MB
-------------------------------------------------- ------------------------------ ------------ ----------- ------- ------------ ------- -------- ------- ---------- ---------- ----------
/dev/oracleasm/disks/DISK5                                                                   0           0 CLOSED  PROVISIONED  ONLINE  NORMAL   UNKNOWN       1019          0          0
/dev/oracleasm/disks/DISK4                                                                   0           1 CLOSED  FORMER       ONLINE  NORMAL   UNKNOWN       5114          0          0
/dev/oracleasm/disks/DISK3                         DATA_0002                                 1           2 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4213
/dev/oracleasm/disks/DISK2                         DATA_0001                                 1           1 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4218
/dev/oracleasm/disks/DISK1                         DATA_0000                                 1           0 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4185

SQL> show parameter power

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_power_limit                      integer     1
SQL> ALTER DISKGROUP DATA REBALANCE POWER 1;

Diskgroup altered.


After modifying the rebalance power to 5 ASM will provide more resources to the rebalance process, this results in faster rebalance times. You can see that DISK4's header status is now 'FORMER' this means a disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.

Be sure to modify back the power of the disk group DATA to 1.

Now let's create a new disk group named FRA using DISK4 & DISK5.


SQL> CREATE DISKGROUP FRA EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/DISK4' NAME FRA_0000, '/dev/oracleasm/disks/DISK5' NAME FRA_0001;

Diskgroup created.

SQL> SELECT PATH,NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,OS_MB,TOTAL_MB,FREE_MB FROM V$ASM_DISK;

PATH                                               NAME                           GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    REDUNDA      OS_MB   TOTAL_MB    FREE_MB
-------------------------------------------------- ------------------------------ ------------ ----------- ------- ------------ ------- -------- ------- ---------- ---------- ----------
/dev/oracleasm/disks/DISK4                         FRA_0000                                  2           0 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       5072
/dev/oracleasm/disks/DISK3                         DATA_0002                                 1           2 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4215
/dev/oracleasm/disks/DISK2                         DATA_0001                                 1           1 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4218
/dev/oracleasm/disks/DISK1                         DATA_0000                                 1           0 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       5114       5114       4183
/dev/oracleasm/disks/DISK5                         FRA_0001                                  2           1 CACHED  MEMBER       ONLINE  NORMAL   UNKNOWN       1019       1019       1009

SQL> select * from V$ASM_DISKGROUP;

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY            DATABASE_COMPATIBILITY                                        V
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
           1 DATA                                   512       4096              1048576 MOUNTED     EXTERN      15342      12616           0         2726                       0          12616             0 11.2.0.0.0               10.1.0.0.0                                                    N
           2 FRA                                    512       4096              1048576 MOUNTED     EXTERN       6133       6081           0           52                       0           6081             0 10.1.0.0.0               10.1.0.0.0                                                    N

SQL> 

ALTER DISKGROUP FRA SET ATTRIBUTE 'compatible.asm'='11.2.0.0.0';

SQL> ALTER DISKGROUP FRA SET ATTRIBUTE 'compatible.asm'='11.2.0.0.0';

Diskgroup altered.

SQL> select * from V$ASM_DISKGROUP;

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY            DATABASE_COMPATIBILITY                                        V
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
           1 DATA                                   512       4096              1048576 MOUNTED     EXTERN      15342      12616           0         2726                       0          12616             0 11.2.0.0.0               10.1.0.0.0                                                    N
           2 FRA                                    512       4096              1048576 MOUNTED     EXTERN       6133       6079           0           54                       0           6079             0 11.2.0.0.0               10.1.0.0.0                                                    N

SQL> 



FRA disk group is ready to host our flash recovery area for archivelogs and backups.

This is a small but useful example of the ASM functionality.

Thanks,
Alfredo





Saturday, June 8, 2013

GoldenGate 11G Install on Linux x86

Today I will show you how to install GoldenGate under Linux 5.5 following the recipe of my teammate Gleb.

Prerequisites:

·          Oracle Linux 5.5 installed in 2 VM using VirtualBox, I have used one pre-built VM from Oracle (http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html)
·          Download Oracle GoldenGate software from (http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html)
·          Start both VM machines and change the host name to source_host & target_host respectively
·          Test ping connectivity from source_host to target_host

Installation

·          Create /01 directory and change the owner to oracle:dba on source_host:

[oracle@source_host Desktop]$ su -
Password:
[root@source_host ~]# cd /
[root@source_host /]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/hda1              11G  2.7G  6.9G  28% /
/dev/hdb1              12G   11G  683M  94% /home
tmpfs                1014M  264M  750M  27% /dev/shm
[root@source_host /]# mkdir /u01
[root@source_host /]# chown oracle:dba /u01
[root@source_host /]# exit
Logout

·          Copy GoldenGate binaries to /u01 and unzip the file, then create goldengate directory under /u01 and untar the file:
[oracle@source_host Desktop]$ mv ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip /u01/

[oracle@source_host Desktop]$ cd /u01
[oracle@source_host u01]$ ls
ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
[oracle@source_host u01]$ unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
Archive:  ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
  inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar 
  inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf 
  inflating: Oracle GoldenGate 11.2.1.0.1 README.txt 
  inflating: Oracle GoldenGate 11.2.1.0.1 README.doc 
[oracle@source_host u01]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar
ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
Oracle GoldenGate 11.2.1.0.1 README.doc
Oracle GoldenGate 11.2.1.0.1 README.txt

[oracle@source_host u01]$ mkdir goldengate
[oracle@source_host u01]$ mv fbo_ggs_Linux_x86_ora11g_32bit.tar goldengate/

[oracle@source_host u01]$ cd goldengate/
[oracle@source_host goldengate]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar
[oracle@source_host goldengate]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

UserExitExamples/
UserExitExamples/ExitDemo_passthru/
UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX
UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c
UserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HP_OSS
UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj
UserExitExamples/ExitDemo_passthru/readme.txt
UserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARIS
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX
UserExitExamples/ExitDemo_lobs/
UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcproj
UserExitExamples/ExitDemo_lobs/Makefile_lob.HPUX
UserExitExamples/ExitDemo_lobs/exitdemo_lob.c
UserExitExamples/ExitDemo_lobs/Makefile_lob.AIX
UserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARIS
UserExitExamples/ExitDemo_lobs/Makefile_lob.LINUX
UserExitExamples/ExitDemo_lobs/readme.txt
UserExitExamples/ExitDemo/
UserExitExamples/ExitDemo/exitdemo.vcproj
UserExitExamples/ExitDemo/Makefile_exit_demo.SOLARIS
UserExitExamples/ExitDemo/exitdemo_utf16.c
UserExitExamples/ExitDemo/readme.txt
UserExitExamples/ExitDemo/exitdemo.c
UserExitExamples/ExitDemo/Makefile_exit_demo.HPUX
UserExitExamples/ExitDemo/Makefile_exit_demo.AIX
UserExitExamples/ExitDemo/Makefile_exit_demo.LINUX
UserExitExamples/ExitDemo/Makefile_exit_demo.HP_OSS
UserExitExamples/ExitDemo_pk_befores/
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARIS
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcproj
UserExitExamples/ExitDemo_pk_befores/readme.txt
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIX
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUX
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX
UserExitExamples/ExitDemo_more_recs/readme.txt
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS
bcpfmt.tpl
bcrypt.txt
cfg/
cfg/ProfileConfig.xml
cfg/jps-config-jse.xml
cfg/password.properties
cfg/MPMetadataSchema.xsd
cfg/mpmetadata.xml
cfg/Config.properties
chkpt_ora_create.sql
cobgen
convchk
db2cntl.tpl
ddl_cleartrace.sql
ddl_ddl2file.sql
ddl_disable.sql
ddl_enable.sql
ddl_filter.sql
ddl_nopurgeRecyclebin.sql
ddl_ora10.sql
ddl_ora10upCommon.sql
ddl_ora11.sql
ddl_ora9.sql
ddl_pin.sql
ddl_purgeRecyclebin.sql
ddl_remove.sql
ddl_session.sql
ddl_session1.sql
ddl_setup.sql
ddl_status.sql
ddl_staymetadata_off.sql
ddl_staymetadata_on.sql
ddl_trace_off.sql
ddl_trace_on.sql
ddl_tracelevel.sql
ddlcob
defgen
demo_more_ora_create.sql
demo_more_ora_insert.sql
demo_ora_create.sql
demo_ora_insert.sql
demo_ora_lob_create.sql
demo_ora_misc.sql
demo_ora_pk_befores_create.sql
demo_ora_pk_befores_insert.sql
demo_ora_pk_befores_updates.sql
dirjar/
dirjar/xmlparserv2.jar
dirjar/spring-security-cas-client-3.0.1.RELEASE.jar
dirjar/spring-security-taglibs-3.0.1.RELEASE.jar
dirjar/org.springframework.transaction-3.0.0.RELEASE.jar
dirjar/identityutils.jar
dirjar/xpp3_min-1.1.4c.jar
dirjar/jps-api.jar
dirjar/jacc-spi.jar
dirjar/org.springframework.jdbc-3.0.0.RELEASE.jar
dirjar/org.springframework.aspects-3.0.0.RELEASE.jar
dirjar/identitystore.jar
dirjar/jps-mbeans.jar
dirjar/fmw_audit.jar
dirjar/commons-codec-1.3.jar
dirjar/jmxremote_optional-1.0-b02.jar
dirjar/spring-security-core-3.0.1.RELEASE.jar
dirjar/log4j-1.2.15.jar
dirjar/commons-logging-1.0.4.jar
dirjar/org.springframework.context-3.0.0.RELEASE.jar
dirjar/org.springframework.asm-3.0.0.RELEASE.jar
dirjar/org.springframework.expression-3.0.0.RELEASE.jar
dirjar/org.springframework.instrument-3.0.0.RELEASE.jar
dirjar/jps-wls.jar
dirjar/jps-upgrade.jar
dirjar/jdmkrt-1.0-b02.jar
dirjar/jps-ee.jar
dirjar/jps-common.jar
dirjar/org.springframework.beans-3.0.0.RELEASE.jar
dirjar/jps-manifest.jar
dirjar/oraclepki.jar
dirjar/spring-security-acl-3.0.1.RELEASE.jar
dirjar/jagent.jar
dirjar/osdt_xmlsec.jar
dirjar/jps-patching.jar
dirjar/org.springframework.context.support-3.0.0.RELEASE.jar
dirjar/jps-unsupported-api.jar
dirjar/monitor-common.jar
dirjar/osdt_cert.jar
dirjar/spring-security-web-3.0.1.RELEASE.jar
dirjar/spring-security-config-3.0.1.RELEASE.jar
dirjar/org.springframework.aop-3.0.0.RELEASE.jar
dirjar/xstream-1.3.jar
dirjar/jps-internal.jar
dirjar/osdt_core.jar
dirjar/org.springframework.core-3.0.0.RELEASE.jar
dirjar/slf4j-api-1.4.3.jar
dirjar/ldapjclnt11.jar
dirjar/org.springframework.test-3.0.0.RELEASE.jar
dirjar/org.springframework.orm-3.0.0.RELEASE.jar
dirjar/org.springframework.web-3.0.0.RELEASE.jar
dirjar/slf4j-log4j12-1.4.3.jar
dirjar/jsr250-api-1.0.jar
dirprm/
dirprm/jagent.prm
emsclnt
extract
freeBSD.txt
ggMessage.dat
ggcmd
ggsci
help.txt
jagent.sh
keygen
libantlr3c.so
libdb-5.2.so
libgglog.so
libggrepo.so
libicudata.so.38
libicui18n.so.38
libicuuc.so.38
libxerces-c.so.28
libxml2.txt
logdump
marker_remove.sql
marker_setup.sql
marker_status.sql
mgr
notices.txt
oggerr
params.sql
prvtclkm.plb
pw_agent_util.sh
remove_seq.sql
replicat
retrace
reverse
role_setup.sql
sequence.sql
server
sqlldr.tpl
tcperrs
ucharset.h
ulg.sql
usrdecs.h
zlib.txt
[oracle@source_host goldengate]$

·          Start GG command line utility and create necessary working directories for GoldenGate:

[oracle@source_host goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

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



GGSCI (source_host.localdomain) 1> create subdirs

Creating subdirectories under current directory /u01/goldengate

Parameter files                /u01/goldengate/dirprm: already exists
Report files                   /u01/goldengate/dirrpt: created
Checkpoint files               /u01/goldengate/dirchk: created
Process status files           /u01/goldengate/dirpcs: created
SQL script files               /u01/goldengate/dirsql: created
Database definitions files     /u01/goldengate/dirdef: created
Extract data files             /u01/goldengate/dirdat: created
Temporary files                /u01/goldengate/dirtmp: created
Stdout files                   /u01/goldengate/dirout: created


GGSCI (source_host.localdomain) 2>
GGSCI (source_host.localdomain) 2> exit
[oracle@source_host goldengate]$ mkdir discard
[oracle@source_host goldengate]$

·          Connect to Source DB and run required configuration and scripts:

[oracle@source_host goldengate]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jun 8 11:55:56 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system set recyclebin=off scope=spfile;

System altered.

SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to ggate;

Grant succeeded.

SQL> grant execute on utl_file to ggate;

Grant succeeded.

SQL>

SQL> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggate


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.
SQL>

SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggate

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.


Using GGATE as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE

CLEAR_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

CREATE_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

TRACE_PUT_LINE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

INITIAL_SETUP STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED

STAYMETADATA IN TRIGGER
------------------------------------------------------------------------------------------------------------------------
OFF

DDL TRIGGER SQL TRACING
------------------------------------------------------------------------------------------------------------------------
0

DDL TRIGGER TRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SQL>

SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggate
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.
SQL>

SQL> grant GGS_GGSUSER_ROLE to ggate;

Grant succeeded.

SQL> @ddl_enable.sql

Trigger altered.

SQL>

SQL> create user sender identified by qwerty default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to sender;

Grant succeeded.

SQL>


·          The installation is done in the Source host, now do the same on the Target host

·          Connect to the Target DB and create a receiver user which will be synced from Source DB


SQL> create user receiver identified by qwerty default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to receiver;

Grant succeeded.

SQL>

·          In the Source host configure the MANAGER and the Extract processes

[oracle@source_host goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

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



GGSCI (source_host.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                          


GGSCI (source_host.localdomain) 2> edit params mgr

--Add this line--
PORT 7809

GGSCI (source_host.localdomain) 3> start manager

Manager started.


GGSCI (source_host.localdomain) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          


GGSCI (source_host.localdomain) 8> add extract ext1, tranlog, begin now
EXTRACT added.


GGSCI (source_host.localdomain) 9> add exttrail /u01/goldengate/dirdat/lt, extract ext1
EXTTRAIL added.


GGSCI (source_host.localdomain) 10> edit params ext1

--Add these lines--
--extract group--
extract ext1
--connection to database--
userid ggate, password ggate
--hostname and port for trail--
rmthost target_host.localdomain, mgrport 7809
--path and name for trail--
rmttrail /u01/goldengate/dirdat/lt
--DDL support
ddl include mapped objname sender.*;
--DML
table sender.*;

GGSCI (source_host.localdomain) 12>

·          Now configure the MANAGER and Replicat processes in Target host

[oracle@target_host goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

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



GGSCI (target_host.localdomain) 1> edit params ./GLOBAL


GGSCI (target_host.localdomain) 4> dblogin userid ggate
Password:
Successfully logged into database.

GGSCI (target_host.localdomain) 5> add checkpointtable ggate.checkpoint

Successfully created checkpoint table ggate.checkpoint.

GGSCI (target_host.localdomain) 6> add replicat rep1, exttrail /u01/goldengate/dirdat/lt,checkpointtable ggate.checkpoint
REPLICAT added.


GGSCI (target_host.localdomain) 7> edit params rep1

--Add these lines--
--Replicat group --
replicat rep1
--source and target definitions
ASSUMiETARGETDEFS
--target database login --
userid ggate, password ggate
--file for dicarded transaction --
discardfile /u01/goldengate/discard/rep1_discard.txt, append, megabytes 10
--ddl support
DDL
--Specify table mapping ---
map sender.*, target receiver.*;



GGSCI (target_host.localdomain) 11> edit params mgr

--Add this line--
PORT 7809

GGSCI (target_host.localdomain) 12> start manager

Manager started.


GGSCI (target_host.localdomain) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    STOPPED     REP1        00:00:00      00:03:31   


·          Now on the Source host start the Extract process


GGSCI (source_host.localdomain) 3> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI (source_host.localdomain) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT1        00:00:00      00:22:34   


GGSCI (source_host.localdomain) 5>

·          Now on the Target host start the Replicat process

GGSCI (target_host.localdomain) 14> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (target_host.localdomain) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP1        00:00:00      00:00:05  

·          At this point GoldenGate is installed and configured in both Source & Target hosts, Extract/Replicat processes are running, now the funny part begins. Let’s test our replication environment!

·          In the Source DB run the below command:

SQL> create table sender.test_tab_1 (id number,rnd_str varchar2(12));
SQL> insert into sender.test_tab_1 values (1,'test_1');
SQL> commit;

·                     ·          Now check the Target DB:

SQL> select * from receiver.test_tab_1;

ID         RND_STR
---------- ------------
1          test_1


Our GoldenGate DDL and DML replication is now working. The table was created on the Target DB and data were replicated.

Thanks,
Alfredo