Oracle Golden Gate setup in 11gr2 Using ACFS.
=============================================
NOTE:1371067.1 - How To Install/Reinstall Or Deinstall ACFS Modules/Installation Manually?
How To Create A New ACFS Volume & Filesystem And Set The ACFS Filesystem Ownership To A Non-Grid/Oracle OS User? [ID 1322383.1] To BottomTo Bottom
How To Convert A General Purpose ACFS Filesystem To CRS Managed On Release 11.2.0.X? [ID 1517113.1]
1) Create and mount the ACFS file system on both the nodes.
Identify the raw disk which has to be used for creating the ASM disk, we are using oracleasm libraries for
managing the asm devices.
[root@rhel11gr2rac1 ~]# /etc/init.d/oracleasm querydisk /dev/s* | grep "is not marked"
Device "/dev/sda" is not marked as an ASM disk
Device "/dev/sda1" is not marked as an ASM disk
Device "/dev/sda2" is not marked as an ASM disk
Device "/dev/sda3" is not marked as an ASM disk
Device "/dev/sdb" is not marked as an ASM disk
Device "/dev/sdb1" is not marked as an ASM disk
Device "/dev/sdc" is not marked as an ASM disk
Device "/dev/sdd" is not marked as an ASM disk
Device "/dev/sde" is not marked as an ASM disk
Device "/dev/sdf" is not marked as an ASM disk
Device "/dev/sdg" is not marked as an ASM disk
Device "/dev/sdh" is not marked as an ASM disk
Device "/dev/sdi" is not marked as an ASM disk
Device "/dev/sdj" is not marked as an ASM disk
Device "/dev/sdk" is not marked as an ASM disk
Device "/dev/sdk1" is not marked as an ASM disk
Device "/dev/sdl" is not marked as an ASM disk
From the above we need to use the disk /dev/sdk1 for the ACFS file system. Create the asm disk in
node 1.
[root@rhel11gr2rac1 ~]# /etc/init.d/oracleasm createdisk ACFSDISK /dev/sdk1
Marking disk "ACFSDISK" as an ASM disk: [ OK ]
Scan the disk in the node 2, and check whether the created disk is listing there.
[root@rhel11gr2rac2 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks: [ OK ]
[root@rhel11gr2rac2 ~]# /etc/init.d/oracleasm listdisks | grep "ACF*"
ACFSDISK
Create the Diskgroup using this disk.
SQL> select path, header_status , inst_id from GV$asm_disk where header_status = 'PROVISIONED';
PATH HEADER_STATU INST_ID
---------------------------------------- ------------ ----------
ORCL:ACFSDISK PROVISIONED 2
ORCL:ACFSDISK PROVISIONED 1
SQL> CREATE DISKGROUP ACFDG EXTERNAL REDUNDANCY
DISK 'ORCL:ACFSDISK'
ATTRIBUTE 'compatible.asm' = '11.2', 'compatible.advm' = '11.2';
Diskgroup created.
Create the ACFS Volume.
SQL> select name, total_mb, usable_file_mb from V$asm_diskgroup WHERE NAME = 'ACFDG'
NAME TOTAL_MB USABLE_FILE_MB
------------------------------ ---------- --------------
ACFDG 9759 9707
SQL> ALTER DISKGROUP ACFDG ADD VOLUME ACFSVOLUME size 9650M;
Diskgroup altered.
SQL> select name, total_mb, usable_file_mb from V$asm_diskgroup WHERE name = 'ACFDG';
NAME TOTAL_MB USABLE_FILE_MB
------------------------------ ---------- --------------
ACFDG 9759 40
Using asmcmd verify the volume that we have created.
$asmcmd
ASMCMD> volinfo -a
Diskgroup Name: ACFDG
Volume Name: ACFSVOLUME
Volume Device: /dev/asm/acfsvolume-184
State: ENABLED
Size (MB): 9664
Resize Unit (MB): 32
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage:
Mountpath:
Now create a mount pain to mount this acfs volume on both the nodes.
# mkdir /golden_gate
Create the ACFS filesystem on the acfsvolume.
# [root@rhel11gr2rac1 ~]# mkfs -t acfs -b 4k /dev/asm/acfsvolume-184
mkfs.acfs: version = 11.2.0.3.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/acfsvolume-184
mkfs.acfs: volume size = 10133438464
mkfs.acfs: Format complete.
Register the acfs filesystem on the new acfs volume.
[root@rhel11gr2rac1 ~]# acfsutil registry -f -a /dev/asm/acfsvolume-184 /golden_gate
acfsutil registry: mount point /golden_gate successfully added to Oracle Registry
Now mont the file system.
[root@rhel11gr2rac1 ~]# mount.acfs -o all
or
mount -t acfs /dev/asm/acfsvolume-184 /golden_gate
[root@rhel11gr2rac1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 21G 18G 2.0G 90% /
/dev/sda1 289M 17M 258M 6% /boot
tmpfs 816M 563M 253M 69% /dev/shm
/dev/sdb1 20G 173M 19G 1% /emcontrol
/dev/asm/acfsvolume-184
9.5G 56M 9.4G 1% /golden_gate
[root@rhel11gr2rac1 ~]# ls -ld /golden_gate/
drwxrwx--- 4 root dba 4096 Jul 13 17:14 /golden_gate/
[root@rhel11gr2rac1 ~]# chown oracle:oinstall /golden_gate/
[root@rhel11gr2rac1 ~]# ls -ld /golden_gate/
drwxrwx--- 4 oracle oinstall 4096 Jul 13 17:14 /golden_gate/
Now mount the diskgroup on the node2.
ASMCMD> mount acfdg
ORA-15032: not all alterations performed
ORA-15202: cannot create additional ASM internal change segment
ORA-15041: diskgroup "ACFDG" space exhausted (DBD ERROR: OCIStmtExecute)
Unable to mount the diskgroup on the node 2, shows space exhausted, as per oracle support
we need to remove some files in the diskgroup, but here we have used to mount acfs so we
will try the reduce the size to 9g in node 1.
[root@rhel11gr2rac1 ~]# acfsutil size -500m /golden_gate/
acfsutil size: ACFS-03162: Warning: The file system was resized, but an error occurred while resizing the ADVM volume.
File system size now 9184 MB, volume size 9664 MB.
Not able to resize the volume online, so drop and recreate the volumegroup.
SQL> alter diskgroup acfdg drop volume acfsvolume
/
Diskgroup altered.
SQL> ALTER DISKGROUP ACFDG ADD VOLUME ACFSVOLUME size 9100m;
Diskgroup altered.
SQL> select name, total_mb, usable_file_mb from V$asm_diskgroup WHERE NAME = 'ACFDG';
NAME TOTAL_MB USABLE_FILE_MB
------------------------------ ---------- --------------
ACFDG 9759 581
[oracle@rhel11gr2rac1 ~]$ asmcmd
ASMCMD> volinfo -a
Diskgroup Name: ACFDG
Volume Name: ACFSVOLUME
Volume Device: /dev/asm/acfsvolume-184
State: ENABLED
Size (MB): 9120
Resize Unit (MB): 32
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage:
Mountpath:
ASMCMD> exit
[oracle@rhel11gr2rac1 ~]$ su - root
Password:
[root@rhel11gr2rac1 ~]# mkfs -t acfs -b 4k /dev/asm/acfsvolume-184
mkfs.acfs: version = 11.2.0.3.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/acfsvolume-184
mkfs.acfs: volume size = 9563013120
mkfs.acfs: Format complete.
[root@rhel11gr2rac1 ~]# acfsutil registry -f -a /dev/asm/acfsvolume-184 /golden_gate
acfsutil registry: ACFS-03161: Device : /dev/asm/acfsvolume-184 : Mount Point : /golden_gate already exists in the Oracle Registry
[root@rhel11gr2rac1 ~]# mount.acfs -o all
[root@rhel11gr2rac1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 21G 18G 2.4G 88% /
/dev/sda1 289M 17M 258M 6% /boot
tmpfs 816M 563M 253M 69% /dev/shm
/dev/sdb1 20G 173M 19G 1% /emcontrol
/dev/asm/acfsvolume-184
9.0G 83M 8.9G 1% /golden_gate
check in node 2.
ASMCMD> volinfo -a
Diskgroup Name: ACFDG
Volume Name: ACFSVOLUME
Volume Device: /dev/asm/acfsvolume-184
State: ENABLED
Size (MB): 9120
Resize Unit (MB): 32
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage: ACFS
Mountpath: /golden_gate
ASMCMD> exit
[oracle@rhel11gr2rac2 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 21G 15G 5.3G 74% /
/dev/sda1 289M 17M 258M 6% /boot
tmpfs 816M 563M 253M 69% /dev/shm
/dev/asm/acfsvolume-184
9.0G 83M 8.9G 1% /golden_gate
Now it is mounted.
Register this mountpoint in CRS.
Get the information of acfs volumes.
[root@rhel11gr2rac1 ~]# acfsutil registry
Mount Object:
Device: /dev/asm/acfsvolume-184
Mount Point: /golden_gate
Disk Group: ACFDG
Volume: ACFSVOLUME
Options: none
Nodes: all
[root@rhel11gr2rac1 bin]# ./srvctl add filesystem -d /dev/asm/acfsvolume-184 -g ACFDG -v ACFSVOLUME -m /golden_gate -u oracle
PRCA-1023 : Unable to add ACFS file system resource ora.acfdg.acfsvolume.acfs for disk group acfdg and volume acfsvolume
PRCA-1046 : File system for volume device /dev/asm/acfsvolume-184 exists in the ACFS registry
To convert an ACFS mount registry based file system, first delete it from the registry. You don't need to unmount or stop it.
You must be root or asmadmin to perform this operation.
So first delete it from the registry.
This operation will not remove or otherwise disturb the files that might already be stored on that filesystem:
[root@rhel11gr2rac1 bin]# acfsutil registry -d /dev/asm/acfsvolume-184
acfsutil registry: successfully removed ACFS volume /dev/asm/acfsvolume-184 from Oracle Registry
[root@rhel11gr2rac1 bin]# ./srvctl add filesystem -d /dev/asm/acfsvolume-184 -g ACFDG -v ACFSVOLUME -m /golden_gate -u oracle
Now start the filesystem.
[oracle@rhel11gr2rac1 ~]$ srvctl start filesystem -d /dev/asm/acfsvolume-184 -n rhel11gr2rac1
[oracle@rhel11gr2rac1 ~]$ srvctl start filesystem -d /dev/asm/acfsvolume-184 -n rhel11gr2rac2
[oracle@rhel11gr2rac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.acfdg.acfsvolume.acfs
ONLINE ONLINE rhel11gr2rac1 mounted on /golden_
gate
ONLINE ONLINE rhel11gr2rac2 mounted on /golden_
gate
Install the ogg on /golden_gate mount point.
Unzip and untar the software.
[oracle@rhel11gr2rac1 golden_gate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (rhel11gr2rac1.manzoor.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (rhel11gr2rac1.manzoor.com) 2> create subdirs
Creating subdirectories under current directory /golden_gate
Parameter files /golden_gate/dirprm: already exists
Report files /golden_gate/dirrpt: created
Checkpoint files /golden_gate/dirchk: created
Process status files /golden_gate/dirpcs: created
SQL script files /golden_gate/dirsql: created
Database definitions files /golden_gate/dirdef: created
Extract data files /golden_gate/dirdat: created
Temporary files /golden_gate/dirtmp: created
Stdout files /golden_gate/dirout: created
Connect to db
SQL> alter database add supplemental log data (all) columns;
Database altered.
SQL> create tablespace golden_gate datafile '+DATA/layatest/datafile/golden_gate01.dbf' size 500m;
Tablespace created.
SQL> create user ggate identified by Gate_123 default tablespace golden_gate;
User created.
Grant the below privs:-
exec dbms_goldengate_auth.grant_admin_privilege('ggate');
GRANT CONNECT,RESOURCE TO ggate;
GRANT SELECT ANY DICTIONARY, SELECT ANY TABLE TO ggate;
GRANT FLASHBACK ANY TABLE TO ggate;
GRANT CREATE TABLE TO ggate;
grant dba to ggate;
grant insert any table, update any table, delete any table to ggate;
GRANT EXECUTE ON DBMS_FLASHBACK TO ggate;
GRANT EXECUTE ON UTL_FILE TO ggate;
Execute the below sql scripts.
SQL> @/golden_gate/marker_setup.sql
SQL> @/golden_gate/ddl_setup.sql
SQL> @/golden_gate/ddl_enable.sql
SQL> @/golden_gate/role_setup.sql
SQL> @/golden_gate/ddl_pin.sql
Connect to ggsci.
> edit params ./GLOBALS
GGSCHEMA GGATE
CHECKPOINTTABLE GGATE.CHKPTBL
=== MGR process parameters============================
--------------------------------------
----------MGR PROCESS PARAMETERS -----
--------------------------------------
USERID GGATE@NCMSSIT12, PASSWORD Gate_123
PORT 7810
DYNAMICPORTLIST 7900-7950
LAGCRITICALMINUTES 5
=======================================================
> start mgr
GGSCI (rhel11gr2rac1.manzoor.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (rhel11gr2rac1.manzoor.com) 2>
GGSCI (rhel11gr2rac1.manzoor.com) 2> dblogin userid ggate password Gate_123
Successfully logged into database.
GGSCI (rhel11gr2rac1.manzoor.com) 3> add checkpointtable ggate.chkptbl
Successfully created checkpoint table ggate.chkptbl.
Test user
=========
SQL> create tablespace manzoor datafile '+DATA/layatest/datafile/manzoor01.dbf' size 300m;
Tablespace created.
SQL> create user manzoor identified by manzoor default tablespace manzoor;
User created.
SQL> grant connect, resource to manzoor;
Grant succeeded.
SQL> grant dba to manzoor;
Grant succeeded.
SQL> conn manzoor/manzoor
Connected.
SQL> create table dept (dept_id number, department_name varchar2(30));
Table created.
SQL> alter table dept add constraint pk_dept_id primary key (dept_id);
Table altered.
SQL> create table emp (emp_id number primary key, emp_name varchar2(30), dept_id number);
Table created.
SQL> alter table emp add constraint fk_dept_id foreign key (dept_id) references dept (dept_id);
Table altered.
GGSCI (rhel11gr2rac1.manzoor.com) 3> dblogin userid ggate, password Gate_123
Successfully logged into database.
GGSCI (rhel11gr2rac1.manzoor.com) 6> add trandata manzoor.dept
Logging of supplemental redo data enabled for table MANZOOR.DEPT.
GGSCI (rhel11gr2rac1.manzoor.com) 7> add trandata manzoor.emp
Logging of supplemental redo data enabled for table MANZOOR.EMP.
GGSCI (rhel11gr2rac1.manzoor.com) 8> edit params xtbls
EXTRACT XTBLS
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
userid ggate@layatest, password Gate_123
tranlogoptions dblogreader
discardfile ./dirdat/xtbls.dsc, append
exttrail ./dirdat/XT
ddl include mapped
table manzoor.dept;
table manzoor.emp;
GGSCI (rhel11gr2rac1.manzoor.com) 10> ADD EXTRACT XTBLS, TRANLOG, BEGIN NOW, THREADS 2
EXTRACT added.
GGSCI (rhel11gr2rac1.manzoor.com) 16> add exttrail ./dirdat/XT, extract xtbls, megabytes 200
EXTTRAIL added.
GGSCI (rhel11gr2rac1.manzoor.com) 25> REGISTER EXTRACT XTBLS LOGRETENTION
2013-07-13 19:35:05 INFO OGG-01749 Successfully registered EXTRACT XTBLS to start managing log retention at SCN 1868707.
GGSCI (rhel11gr2rac1.manzoor.com) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING XTBLS 00:00:02 00:00:07
If you check the status in node 2 then it will look like as below.
GGSCI (rhel11gr2rac2.manzoor.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT ABENDED XTBLS 00:00:06 00:00:07
sql
SQL> begin
2 for i in 1..10 loop
3 insert into manzoor.dept values (dbms_random.value(1,10),dbms_random.string('U',30));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
Lets check whether it is captured in the extract.
[oracle@rhel11gr2rac2 dirdat]$ pwd
/golden_gate/dirdat
[oracle@rhel11gr2rac2 dirdat]$ ls -lrt
total 8
-rw-rw-rw- 1 oracle oinstall 256 Jul 13 20:04 xtbls.dsc
-rw-rw-rw- 1 oracle oinstall 2842 Jul 13 20:09 XT000000
------- Yet to complete---------------------------------------------------
http://www.oracle11ggotchas.com/articles/TheImportanceofBoundedRecovery.htm
