Setup and configure DBFS, GoldenGate and XAG on RAC
articles:
Recently, I must have configured one system, in which using flex-asm, dbfs (database file system), dns in according to be an Golden Gate Hub & roll-forward/back-forward leaf application on flex cluster.
Usually, I configured ACFS for this purpose but only 2 nodes in RAC's environment, however, DBFS's often invoking line.
In this article, I share only DBFS content in RAC, of course, they're not different to Flex system.
I. Setup & Configuration DBFS on RAC using CRS Management (non Exa Data)
1. Install fuse in both of nodes
[root@atm-db01 ~] # yum install fuse fuse-libs kernel-devel [/code ] Then we need to add our oracle user to that group on both nodes [code] [root@atm-db01 ~] # usermod -a -G fuse oracle
2. Add user_allow_other into /etc/fuse.conf file, in both of nodes
[root@atm-db01 ~] # echo user_allow_other > /etc/fuse.conf [root@atm-db01 ~] # chmod 644 /etc/fuse.conf [root@atm-db01 ~] # cat /etc/fuse.conf [root@atm-db01 ~] # ls -l /etc/fuse.conf
3. Create the DBFS mount point on both nodes
[root@atm-db01 ~] # mkdir -p /mnt/dbfs_direct [root@atm-db01 ~] # chown oracle:dba /mnt/dbfs_direct [root@atm-db01 ~] # ls -ld /mnt/dbfs_direct [root@atm-db01 ~] # su - oracle [oracle@atm-db01 ~]$ cd /mnt/dbfs_direct [oracle@atm-db01 ~]$ pwd [oracle@atm-db01 ~]$ exit
4) Create the “DBFS database user” and the “DBFS database repository” from Node #1 as follows:
4.1) Connect to the database instance located in the first node ( e.g. DBFSDAT1) “/as sysdba” and executed the next commands (only from the first node):
[oracle@atm-db01 ~]$ sqlplus "/as sysdba" SQL> create bigfile tablespace dbfs_ts datafile '+DATA' size 24G autoextend on next 100M maxsize 31G NOLOGGING <<-- This is very important step, if none of maxsize clause issue, then DBFS will allocate all "+DATA" diskgroup EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; SQL> create user dbfs_user identified by dbfs_user default tablespace dbfs_ts quota unlimited on dbfs_ts; SQL> grant create session, create table, create view, create procedure, dbfs_role to dbfs_user;
4.2) Then execute the following commands through the database instance (e.g. DBFSDAT1) as the “dbfs_user” database user (from Node #1 only):
[oracle@atm-db01 ~]$ sqlplus dbfs_user/dbfs_user
SQL>@?/rdbms/admin/dbfs_create_filesystem dbfs_ts FS
No errors.
--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_FS', tbl_name => 'T_FS', tbl_tbs =>
'dbfs_ts', lob_tbs => 'dbfs_ts', do_partition => false, partition_key => 1, do_compress => false,
compression => '', do_dedup => false, do_encrypt => false); end;
--------
REGISTER STORE:
begin dbms_dbfs_content.registerStore(store_name=> 'FS_FS', provider_name => 'sample1',
provider_package => 'dbms_dbfs_sfs'); end;
--------
MOUNT STORE:
begin dbms_dbfs_content.mountStore(store_name=>'FS_FS', store_mount=>'FS'); end;
--------
CHMOD STORE:
declare m integer; begin m := dbms_fuse.fs_chmod('/FS', 16895); end;
No errors.
SQL>exit;
4.3) Set the NOCACHE storage option to the LOB segment used by DBFS, connect to the database instance (e.g. DBFSDAT1) “/as sysdba”
(from Node #1 only) and execute the following commands as follows:
[oracle@atm-db01 ~]$ sqlplus "/as sysdba" SQL> ALTER TABLE DBFS_USER.T_FS1 MODIFY LOB (FILEDATA) (NOCACHE LOGGING); SQL> SELECT owner , table_name , segment_name , logging , cache FROM dba_lobs WHERE tablespace_name='DBFS_TS'; OWNER TABLE_NAME SEGMENT_NAME LOGGING ------------------------------ ------------------------------ ------------------------------ ------- CACHE ---------- DBFS_USER T_FS LOB_SFS$_FST_1 YES NO 1 row selected.
5) Then configure “fuse” library links on all the nodes (connected as “root” OS user), we will use oracle environment to get $ORACLE_HOME path:
[root@atm-db01 ~] # . oraenv ORACLE_SID = [root] ? DBFSDAT1 The Oracle base has been set to /u01/app/oracle [root@atm-db01 ~] # echo "/usr/local/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf cd /usr/local/lib ln -s $ORACLE_HOME/lib/libclntsh.so.11.1 ln -s $ORACLE_HOME/lib/libnnz11.so locate libfuse.so ln -s /lib64/libfuse.so.2 libfuse.so ldconfig ln -s $ORACLE_HOME/bin/dbfs_client /sbin/mount.dbfs
6) As a following step, please download the latest version of the “mount-dbfs.sh” script from the following document:
Configuring DBFS on Oracle Database Machine [ID 1054431.1]
Note: We will use mount-dbfs.sh of mine script instead of exist in 105443.1
And we edit those parameter in mount-dbfs.sh
# DBNAME= << -- ORACLE_UNQNAME, example, devmkt
# MOUNT_POINT= << -- shared_storage, example in this document: /mnt/dbfs_direct
# DBFS_USER= << -- Oracle's database DBFS_USER created on above
# ORACLE_HOME= << -- ORACLE_HOME path
# DBFS_PASSWD= << -- Oracle's database DBFS_USER password on above
# ------------------------------------------------ mount-dbfs.sh -----------------------------------------------------------------------
#!/bin/bash
### This script is from Note 1054431.1, ensure you have the latest version
### Note 1054431.1 provides information about the setup required to use this script
### updated 26-JUL-2012
###########################################
### Everyone must set these values
###########################################
### Database name for the DBFS repository as used in "srvctl status database -d $DBNAME"
DBNAME=atmdev
### Mount point where DBFS should be mounted
MOUNT_POINT=/mnt/dbfs_direct
### Username of the DBFS repository owner in database $DBNAME
DBFS_USER=dbfs_user
### RDBMS ORACLE_HOME directory path
ORACLE_HOME=/dbaapp/app/oracle/product/11.2.0/dbatmdev2023
### Syslog facility name (default user)
### Changed default from local3 to user for Solaris default support on 17-FEB-2012
### This will allow us to log messages to the syslog
### (/var/log/messages on Linux, /var/adm/messages on Solaris)
LOGGER_FACILITY=user
### mount options for dbfs_client; these are used for both wallet and non-wallet mounting
MOUNT_OPTIONS=allow_other,direct_io,failover,nolock
### if tracing is required, maybe consider parameter like the example below
### following example is commented out, only uncomment if directed by Oracle Support
### fix_control=32 added per bug 13340960 to allow async statfs response
#MOUNT_OPTIONS=allow_other,direct_io,fix_control=32,trace_level=1,trace_file=/tmp/dbfs_client_trace.$$.log,trace_size=100
### PERL_ALARM_TIMEOUT is number of seconds to wait for response from status command.
### After this, if no respnose, the script will run clean.
### NOTE: If this is longer than the clusterware check interval, bad things may happen.
### Adjust the CHECK_INTERVAL to ensure it is at least 2x as long as PERL_ALARM_TIMEOUT.
### Example:
### $ crsctl status res dbfs_mount -p|grep ^CHECK
### CHECK_INTERVAL=30
### $ crsctl modify res dbfs_mount -attr "CHECK_INTERVAL=32"
### $ crsctl status res dbfs_mount -p|grep ^CHECK
### CHECK_INTERVAL=32
PERL_ALARM_TIMEOUT=14
###########################################
### If using password-based authentication, set these
###########################################
### This is the plain text password for the DBFS_USER user
DBFS_PASSWD=devmkt
### The file used to temporarily store the DBFS_PASSWD so dbfs_client can read it
### This file is removed immediately after it is read by dbfs_client
### The actual filename used will have the PID appended to the name for uniqueness
### This variable should be a full pathname including a directory and the first part of a filename.
DBFS_PWDFILE_BASE=/tmp/.dbfs-passwd.txt
###########################################
### If using wallet-based authentication, modify these
###########################################
### WALLET should be true if using a wallet, otherwise, false
WALLET=false
### TNS_ADMIN is the directory containing tnsnames.ora and sqlnet.ora used by DBFS
TNS_ADMIN=/dbaapp/app/oracle/product/11.2.0/atmdev2023/network/admin
### TNS alias used for mounting with wallets
# DBFS_LOCAL_TNSALIAS=fsdb.local
###########################################
### No editing is required below this point
###########################################
### determine platform
UNAME_S=`uname -s`
if [ $UNAME_S = 'Linux' ]; then LINUX=1; SOLARIS=0;
elif [ $UNAME_S = 'SunOS' ]; then LINUX=0; SOLARIS=1;
fi
GREP=/bin/grep
AWK=/bin/awk
ECHO=/bin/echo
LOGGER="/bin/logger -t DBFS_${MOUNT_POINT}"
RMF='/bin/rm -f'
TOUCH=/bin/touch
CHMOD=/bin/chmod
PS=/bin/ps
SLEEP=/bin/sleep
KILL=/bin/kill
BASENAME=/bin/basename
STAT=/usr/bin/stat
ID=/usr/bin/id
WC=/usr/bin/wc
SRVCTL=$ORACLE_HOME/bin/srvctl
DBFS_CLIENT=$ORACLE_HOME/bin/dbfs_client
HN=/bin/hostname
PERL=/usr/bin/perl
MOUNT=/bin/mount
### ensure messages are displayed in English for pattern matching
LANG=en_US.UTF-8
NLS_LANG=American_America.US7ASCII
if [ -z "$STATUS_TIMEOUT" ]; then STATUS_TIMEOUT=0; fi
if [ $LINUX -eq 1 ]; then
MOUNT=/bin/mount
XARGS='/usr/bin/xargs -r'
FUSERMOUNT=/bin/fusermount
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64
elif [ $SOLARIS -eq 1 ]; then
MOUNT=/sbin/mount
XARGS=/usr/bin/xargs
UMOUNT=/usr/sbin/umount
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/usr/lib:/lib
fi
DBFS_PWDFILE=$DBFS_PWDFILE_BASE.$$
export ORACLE_HOME LD_LIBRARY_PATH TNS_ADMIN
export STAT MOUNT_POINT PERL_ALARM_TIMEOUT SOLARIS LINUX
export PATH=$ORACLE_HOME/bin:$PATH
export STATUS_TIMEOUT
export LANG NLS_LANG
logit () {
### type: info, error, debug
type=$1
msg=$2
if [ "$type" = "info" ]; then
$ECHO $msg
$LOGGER -p ${LOGGER_FACILITY}.info "$msg"
elif [ "$type" = "error" ]; then
$ECHO $msg
$LOGGER -p ${LOGGER_FACILITY}.error "$msg"
elif [ "$type" = "debug" ]; then
$ECHO $msg
$LOGGER -p ${LOGGER_FACILITY}.debug "$msg"
fi
}
### must not be root
if [ `$ID -u` -eq 0 ]; then
logit error "Run this as the Oracle software owner, not root"
exit 1
fi
### determine how we were called, derive location
SCRIPTPATH=$0
SCRIPTNAME=`$BASENAME $SCRIPTPATH`
echo $SCRIPTPATH | grep ^/ > /dev/null 2>&1
if [ $? -ne 0 ]; then
MYDIR=`pwd`
SCRIPTPATH=${MYDIR}/${SCRIPTPATH}
fi
### must cd to a directory where the oracle owner can get CWD
cd /tmp
case "$1" in
'start')
logit info "$SCRIPTNAME mounting DBFS at $MOUNT_POINT from database $DBNAME"
### check to see if it is already mounted
$SCRIPTPATH status > /dev/null 2>&1
if [ $? -eq 0 ]; then
logit error "$MOUNT_POINT already mounted, use $SCRIPTNAME stop before attempting to start"
$SCRIPTPATH status
exit 1
fi
### set the ORACLE_SID dynamically based on OCR info, if it is running
export ORACLE_SID=$($SRVCTL status instance -d $DBNAME -n `$HN` | \
$GREP 'is running' | $AWK '{print $2}' )
logit info "ORACLE_SID is $ORACLE_SID"
### if there's no SID defined locally or it isn't running, stop
if [ -z "$ORACLE_SID" -a "$WALLET" = 'false' ]; then
logit error "No running ORACLE_SID available on this host, exiting"
exit 2
fi
### if using password-based startup, use this
if [ "$WALLET" = 'false' -a -n "$DBFS_PASSWD" ]; then
$RMF $DBFS_PWDFILE
if [ -f $DBFS_PWDFILE ]; then
logit error "please remove $DBFS_PWDFILE and try again"
exit 1
fi
$TOUCH $DBFS_PWDFILE
$CHMOD 600 $DBFS_PWDFILE
$ECHO $DBFS_PASSWD > $DBFS_PWDFILE
logit info "spawning dbfs_client command using SID $ORACLE_SID"
(nohup $DBFS_CLIENT ${DBFS_USER}@ -o $MOUNT_OPTIONS \
$MOUNT_POINT < $DBFS_PWDFILE | $LOGGER -p ${LOGGER_FACILITY}.info 2>&1 & ) &
$RMF $DBFS_PWDFILE
elif [ "$WALLET" = true ]; then
logit info "doing mount $MOUNT_POINT using SID $ORACLE_SID with wallet now"
(nohup $DBFS_CLIENT /@${DBFS_LOCAL_TNSALIAS} -o $MOUNT_OPTIONS,wallet \
$MOUNT_POINT | $LOGGER -p ${LOGGER_FACILITY}.info 2>&1 & ) &
fi
### allow time for the mount table update before checking it
$SLEEP 5
### set return code based on success of mounting
$SCRIPTPATH status > /dev/null 2>&1
if [ $? -eq 0 ]; then
logit info "Start -- ONLINE"
exit 0
else
logit info "Start -- OFFLINE"
exit 1
fi
;;
'stop')
$SCRIPTPATH status > /dev/null
if [ $? -eq 0 ]; then
logit info "unmounting DBFS from $MOUNT_POINT"
if [ $LINUX -eq 1 ]; then
logit info "umounting the filesystem using '$FUSERMOUNT -u $MOUNT_POINT'"
$FUSERMOUNT -u $MOUNT_POINT
elif [ $SOLARIS -eq 1 ]; then
logit info "umounting the filesystem using '$UMOUNT $MOUNT_POINT'"
$UMOUNT $MOUNT_POINT > /dev/null 2>&1
fi
$SCRIPTPATH status > /dev/null
if [ $? -eq 0 ]; then
logit error "Stop - stopped, but still mounted, error"
exit 1
else
logit info "Stop - stopped, now not mounted"
exit 0
fi
else
logit error "filesystem $MOUNT_POINT not currently mounted, no need to stop"
fi
;;
'check'|'status')
### check to see if it is mounted
### fire off a short process in perl to do the check (need the alarm builtin)
logit debug "Checking status now"
$PERL <<'TOT'
$timeout = $ENV{'PERL_ALARM_TIMEOUT'};
$SIG{ALRM} = sub {
### we have a problem and need to cleanup
exit 3;
die "timeout" ;
};
alarm $timeout;
eval {
$STATUSOUT=`$ENV{'STAT'} -f -c "%T" $ENV{'MOUNT_POINT'} 2>&1 `;
chomp($STATUSOUT);
if ( ( $ENV{'SOLARIS'} == 1 && $STATUSOUT eq 'uvfs' ) ||
( $ENV{'LINUX'} == 1 && $STATUSOUT eq 'fuseblk' ) ) {
### status is okay
exit 0;
} elsif ( $STATUSOUT =~ /Transport endpoint is not connected/ ) {
### we have a problem, need to clean up
exit 2;
} else {
### filesystem is offline
exit 1;
}
};
TOT
RC=$?
### process return codes from the perl block
if [ $RC -eq 3 ]; then
STATUS_TIMEOUT=$(( $STATUS_TIMEOUT + 1 ))
logit error "Found timeout while checking status, cleaning mount automatically"
$SCRIPTPATH clean
logit debug "Check -- OFFLINE"
exit 1
elif [ $RC -eq 2 ]; then
STATUS_TIMEOUT=$(( $STATUS_TIMEOUT + 1 ))
logit error "Found error while checking status, cleaning mount automatically"
$SCRIPTPATH clean
logit debug "Check -- OFFLINE"
exit 1
elif [ $RC -eq 1 ]; then
logit debug "Check -- OFFLINE"
exit 1
elif [ $RC -eq 0 ]; then
logit debug "Check -- ONLINE"
exit 0
fi
;;
'restart')
logit info "restarting DBFS"
$SCRIPTPATH stop
$SLEEP 2
$SCRIPTPATH start
;;
'clean'|'abort')
logit info "cleaning up DBFS nicely using (fusermount -u|umount)"
if [ $LINUX -eq 1 ]; then
$FUSERMOUNT -u $MOUNT_POINT
elif [ $SOLARIS -eq 1 ]; then
$UMOUNT $MOUNT_POINT > /dev/null 2>&1
fi
$SLEEP 1
FORCE_CLEANUP=0
if [ $STATUS_TIMEOUT -gt 1 ]; then
FORCE_CLEANUP=1
else
$SCRIPTPATH status > /dev/null
if [ $? -eq 0 ]; then FORCE_CLEANUP=1; fi
fi
if [ $FORCE_CLEANUP -eq 1 ]; then
logit error "tried (fusermount -u|umount), still mounted, now cleaning with (fusermount -u -z|umount -f) and kill"
if [ $LINUX -eq 1 ]; then
$FUSERMOUNT -u -z $MOUNT_POINT
elif [ $SOLARIS -eq 1 ]; then
echo "running umount -f now"
$UMOUNT -f $MOUNT_POINT > /dev/null 2>&1
fi
if [ $LINUX -eq 1 ]; then
PIDS=`$PS -ef | $GREP -w "$MOUNT_POINT" | $GREP dbfs_client| $GREP -v grep | \
$AWK '{print $2}'`
if [ -n "$PIDS" ]; then $KILL -9 $PIDS; fi
PIDS=`$PS -ef | $GREP -w "$MOUNT_POINT" | $GREP mount.dbfs | $GREP -v grep | \
$AWK '{print $2}'`
if [ -n "$PIDS" ]; then $KILL -9 $PIDS; fi
elif [ $SOLARIS -eq 1 ]; then
PIDS=`$PS -ef | $GREP dbfs_client| $GREP -v grep | $AWK '{print $2}'`
REALPIDS=' '
for pid in $PIDS
do
ARGS=`pargs $pid`
echo $ARGS | grep "$MOUNT_POINT$" > /dev/null
RET=$?
if [ $RET -eq 0 ]; then REALPIDS="$REALPIDS $pid"; fi
done
if [ -n "$REALPIDS" ]; then $KILL -9 $REALPIDS; fi
### do it a 2nd time to clean up others
if [ -n "$REALPIDS" ]; then $KILL -9 $REALPIDS; fi
PIDS=`$PS -ef | $GREP dbfs_client| $GREP -v grep | $AWK '{print $2}'`
REALPIDS=' '
for pid in $PIDS
do
ARGS=`pargs $pid`
echo $ARGS | grep "$MOUNT_POINT$" > /dev/null
RET=$?
if [ $RET -eq 0 ]; then REALPIDS="$REALPIDS $pid"; fi
done
if [ -n "$REALPIDS" ]; then $KILL -9 $REALPIDS; fi
fi
exit 1
fi
;;
*)
$ECHO "Usage: $SCRIPTNAME { start | stop | check | status | restart | clean | abort }"
;;
esac
# ------------------------------------------------ mount-dbfs.sh END of file -----------------------------------------------------------------------
7. Place the new “mount-dbfs.sh” script at the “
[root@atm-db01 ~]# . oraenv
ORACLE_SID = [root] ? +ASM1
The Oracle base has been set to /dbaapp/app/grid
[root@atm-db01 ~]#
[root@atm-db01 ~]# cd $ORACLE_HOME/crs/script
[root@atm-db01 script]# pwd
/dbaapp/app/11.2.0/grid/crs/script
[root@atm-db01 script]# ls -llrt
total 12
-rwxr-x--- 1 oracle dba 11628 Oct 17 18:10 mount-dbfs.sh
[root@atm-db01 script]#
8. Set the correct permissions and ownership as follows (on all the nodes):
[root@atm-db01 ~] # cd $ORACLE_HOME/crs/script [root@atm-db01 ~] # chown oracle:dba mount-dbfs.sh [root@atm-db01 ~] # chmod 750 mount-dbfs.sh [root@atm-db01 ~] # dos2unix mount-dbfs.sh dos2unix: converting file mount-dbfs.sh to UNIX format ...
9. Then manually run the (“/dbaapp/app/grid/crs/script/mount-dbfs.sh start”) script (as “oracle” OS user or the RDBMS Oracle Home owner)
on all the nodes to mount the DBFS filesystem for the first time:
[oracle@atm-db01 ~]$ cd /dbaapp/app/grid/crs/script/ [oracle@atm-db01 ~]$ mount-dbfs.sh start mount-dbfs.sh mounting DBFS at /mnt/dbfs_direct from database atmdev ORACLE_SID is atmdev1 spawning dbfs_client command using SID atmdev1 Start -- ONLINE [oracle@atm-db01 script]$ mount-dbfs.sh status Checking status now Check -- ONLINE [oracle@atm-db01 script]$ [oracle@atm-db02 ~]$ cd /dbaapp/app/grid/crs/script/ [oracle@atm-db02 ~]$ mount-dbfs.sh start mount-dbfs.sh mounting DBFS at /mnt/dbfs_direct from database atmdev ORACLE_SID is atmdev2 spawning dbfs_client command using SID atmdev2 Start -- ONLINE [oracle@atm-db02 script]$ mount-dbfs.sh status Checking status now Check -- ONLINE [oracle@atm-db02 script]$
10. Verify and validate that the DBFS filesystem was successfully mounted on all the nodes
[oracle@atm-db01 script]$ df -k /mnt/dbfs_direct/ Filesystem 1K-blocks Used Available Use% Mounted on dbfs-dbfs_user@:/ 3144704 120 3144584 1% /mnt/dbfs_direct [oracle@atm-db01 script]$ [oracle@atm-db02 ~]$ df -k /mnt/dbfs_direct/ Filesystem 1K-blocks Used Available Use% Mounted on dbfs-dbfs_user@:/ 3144704 120 3144584 1% /mnt/dbfs_direct [oracle@atm-db02 ~]$
11. Testing: On node 1, login into oracle user, create a test file in /mnt/dbfs_direct/FS, then show it from node 2
[oracle@atm-db01 ~]$ cd /mnt/dbfs_direct/FS [oracle@atm-db01 FS]$ touch a.lst [oracle@atm-db02 ~]$ cd /mnt/dbfs_direct/FS [oracle@atm-db02 FS]$ ls -llrt a.lst
11. Then proceed to mount the same DBFS filesystem (now as a service/ resource) through the CRS as follows:
11.1. Then add the DBFS service/resource from node #1 only Using oracle to create add-dbfs-resource shell script, put in into $ORACLE_PATH
[oracle@atm-db01 ~]$ cd $ORACLE_PATH [oracle@atm-db01 ~]$ vi add-dbfs-resource.sh
Pay attention to edit those parameters:
# ACTION_SCRIPT = $GRID_HOME/crs/script (GRID_HOME=grid's ORACLE_HOME) # RESNAME << -- The resource name will be managed by CRS # DBNAME << -- ORACLE_UNQNAME #------------------------------------------------- add-dbfs-resource.sh ------------------------------------------------------------------------- ##### start script add-dbfs-resource.sh #!/bin/bash ACTION_SCRIPT=/dbaapp/app/11.2.0/grid/crs/script/mount-dbfs.sh RESNAME=dbfs_mount DBNAME=atmdev DBNAMEL=`echo $DBNAME | tr A-Z a-z` ORACLE_HOME=/dbaapp/app/11.2.0/grid PATH=$ORACLE_HOME/bin:$PATH export PATH ORACLE_HOME crsctl add resource $RESNAME \ -type local_resource \ -attr "ACTION_SCRIPT=$ACTION_SCRIPT, \ CHECK_INTERVAL=30,RESTART_ATTEMPTS=10, \ START_DEPENDENCIES='hard(ora.$DBNAMEL.db)pullup(ora.$DBNAMEL.db)',\ STOP_DEPENDENCIES='hard(ora.$DBNAMEL.db)',\ SCRIPT_TIMEOUT=300" ##### end script add-dbfs-resource.sh #------------------------------------------------- add-dbfs-resource.sh End of file --------------------------------------------------------------
[oracle@atm-db01 ~]$ chmod +x add-dbfs-resource.sh
11.2 Start the add-dbfs-resource.sh
[oracle@atm-db01 ~]$ ./add-dbfs-resource.sh
11.3 Using crsctl to start dbfs_mount resource in node 2, verify its status from node 2
[oracle@atm-db01 ~]$ . oraenv ORACLE_SID = [atmdev1] ? +ASM1 The Oracle base has been changed from /dbaapp/app/oracle to /dbaapp/app/grid [oracle@atm-db01 ~]$ crsctl start res dbfs_mount [oracle@atm-db02 ~]$ crsctl status res dbfs_mount NAME=dbfs_mount TYPE=local_resource TARGET=ONLINE , ONLINE STATE=ONLINE on atm-db01, ONLINE on atm-db02 [oracle@atm-db02 ~]$
12. Add virtual IP, using root in node 1
[root@atm-db01 ~] # appvipcfg create -network=1 -ip=10.164.88.76 -vipname=atmdev-gg-vip -user=root [root@atm-db01 ~] # crsctl setperm resource atmdev-gg-vip -u user:oracle:r-x [oracle@atm-db01 ~]$ crsctl start res atmdev-gg-vip -n 'hostname -s'
13. XAG setup & configuration
13.1 Download XAG and install in node 1, using grid user
[grid@atm-db01 ~]$ cd xagbundle [grid@atm-db01 ~]$ ./xagsetup.sh --install --directory /dbaapp/app/grid/xag --all_nodes
13.2. Download & install Goldengate software (21) from Oracle
Note:
- Using oracle user to install Goldengate, separately between 2 nodes but same as local directories.
in my system, I located them in to $ORACLE_BASE/gg (/dbaapp/app/oracle/product/gg)
- After installation Goldengate, we upgrade .bash_profile for oracle's environment, by add & editing something likes :
.bash_profile
.........
GGATE = $ORACLE_BASE/gg; export GGATE
XAG_HOME = $GRID_BASE/xag (/dbaapp/app/grid/xag); export XAG_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GGATE/lib; export LD_LIBRARY_PATH
13.3. Perparation tnsnames.ora. (Not show for my private system)
13.4 -- Using agctl only in node 1, add goldengate service
[oracle@atm-db01 ~]$ agctl add goldengate gg_atmdev --gg_home /dbaapp/app/oracle/product/gg --instance_type source --nodes atm-db01,atm-db02 --filesystems dbfs_mount --databases ora.atmdev.db --oracle_home /dbaapp/app/oracle/product/11.2.0/atmdev2023 [oracle@atm-db01 ~]$ crsctl modify res dbfs_mount -attr "AUTO_START=never" [oracle@atm-db01 ~]$ agctl start goldengate gg_atmdev --node atm-db01
14. Create symbol link, on both of nodes
-- Only create dirchk, dirprm, dirdat on shared_storage using DBFS, but not dirtmp because of DataGuard builing later
-- dirchk
Node 1:
[oracle@atm-db01 ~]$ cd $GGATE [oracle@atm-db01 ~]$ rmdir dirchk [oracle@atm-db01 ~]$ mkdir /mnt/dbfs_direct/FS/dirchk [oracle@atm-db01 ~]$ chown oracle:oinstall /mnt/dbfs_direct/FS/dirchk [oracle@atm-db01 ~]$ chmod 750 /mnt/dbfs_direct/FS/dirchk [oracle@atm-db01 ~]$ ln -s /mnt/dbfs_direct/FS/dirchk dirchk [oracle@atm-db01 ~]$ ls -ld dirchk
-- dirdat
[oracle@atm-db01 ~]$ cd $GGATE [oracle@atm-db01 ~]$ rmdir dirdat [oracle@atm-db01 ~]$ mkdir /mnt/dbfs_direct/FS/dirdat [oracle@atm-db01 ~]$ chown oracle:oinstall /mnt/dbfs_direct/FS/dirdat [oracle@atm-db01 ~]$ chmod 750 /mnt/dbfs_direct/FS/dirdat [oracle@atm-db01 ~]$ ln -s /mnt/dbfs_direct/FS/dirdat dirdat [oracle@atm-db01 ~]$ ls -ld dirdat
-- dirprm
[oracle@atm-db01 ~]$ cd $GGATE [oracle@atm-db01 ~]$ mkdir /mnt/dbfs_direct/FS/dirprm [oracle@atm-db01 ~]$ chown oracle:oinstall /mnt/dbfs_direct/FS/dirprm [oracle@atm-db01 ~]$ chmod 750 /mnt/dbfs_direct/FS/dirprm [oracle@atm-db01 ~]$ cp -r dirprm $ORACLE_PATH <<--- Important, backup mrp into another directory [oracle@atm-db01 ~]$ mv dirprm/* /mnt/dbfs_direct/FS/dirprm [oracle@atm-db01 ~]$ rmdir dirprm [oracle@atm-db01 ~]$ ln -s /mnt/dbfs_direct/FS/dirprm dirprm [oracle@atm-db01 ~]$ ls -ld dirprm
Node 2:
[oracle@atm-db02 ~]$ cd $GGATE [oracle@atm-db02 ~]$ rmdir dirchk [oracle@atm-db02 ~]$ ln -s /mnt/dbfs_direct/FS/dirchk dirchk [oracle@atm-db02 ~]$ ls -ld dirchk
-- dirdat
[oracle@atm-db02 ~]$ cd $GGATE [oracle@atm-db02 ~]$ rmdir dirdat [oracle@atm-db02 ~]$ ln -s /mnt/dbfs_direct/FS/dirdat dirdat [oracle@atm-db02 ~]$ ls -ld dirdat
-- dirprm
[oracle@atm-db02 ~]$ cd $GGATE [oracle@atm-db02 ~]$ cp -r dirprm $ORACLE_PATH <<--- Important, backup mrp into another directory [oracle@atm-db02 ~]$ mv dirprm/* /mnt/dbfs_direct/FS/dirprm [oracle@atm-db02 ~]$ rmdir dirprm [oracle@atm-db02 ~]$ ln -s /mnt/dbfs_direct/FS/dirprm dirprm [oracle@atm-db02 ~]$ ls -ld dirprm
15. Testing relocate GoldenGate
[oracle@atm-db01 ~]$ agctl status goldengate Goldengate instance 'gg_atmdev' is running on atm-db01 [oracle@atm-db01 ~]$ agctl relocate goldengate gg_atmdev --node atm-db02 [oracle@atm-db01 ~]$ agctl status goldengate Goldengate instance 'gg_atmdev' is running on atm-db02 [oracle@atm-db01 ~]$
II. Configuration Goldengate Replication
1. Force logging & add supplemental log
[oracle@atm-db01 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 19 10:29:30 2023 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options sys@atmdev> select force_logging from v$database; FOR --- NO sys@atmdev> alter database force logging; Database altered. sys@atmdev> select force_logging from v$database; FOR --- YES sys@atmdev> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. sys@atmdev> SELECT supplemental_log_data_min FROM v$database; SUPPLEME -------- YES
2. Perparing Oracle Database configuration for GoldenGate
2.1. Enable parameters
sys@atmdev> alter system set enable_goldengate_replication=TRUE scope=spfile sid='*'; sys@atmdev> alter system set recyclebin=OFF scope=spfile sid='*';
2.2 Restart Database
[grid@atm-db01 ~]$ srvctl stop database -d atmdev PRCR-1133 : Failed to stop database atmdev and its running services PRCR-1132 : Failed to stop resources using a filter CRS-2529: Unable to act on 'ora.atmdev.db' because that would require stopping or relocating 'dbfs_mount', but the force option was not specified CRS-2529: Unable to act on 'ora.atmdev.db' because that would require stopping or relocating 'xag.gg_atmdev.goldengate', but the force option was not specified CRS-2529: Unable to act on 'ora.atmdev.db' because that would require stopping or relocating 'dbfs_mount', but the force option was not specified CRS-2529: Unable to act on 'ora.atmdev.db' because that would require stopping or relocating 'xag.gg_atmdev.goldengate', but the force option was not specified [grid@atm-db01 ~]$ exit
Because we start GoldenGate XAG and DBFS resource, so, we must shutdown them, before shutdown Database
[oracle@atm-db01 ~]$ . oraenv ORACLE_SID = [atmdev1] ? +ASM1 The Oracle base has been changed from /dbaapp/app/oracle to /dbaapp/app/grid [oracle@atm-db01 grid]$ cd $ORACLE_HOME/crs/script [oracle@atm-db01 script]$ mount-dbfs.sh stop unmounting DBFS from /mnt/dbfs_direct umounting the filesystem using '/bin/fusermount -u /mnt/dbfs_direct' Stop - stopped, now not mounted
Node 2:
[oracle@atm-db02 admin]$ cd $HOME [oracle@atm-db02 ~]$ . oraenv ORACLE_SID = [atmdev2] ? +ASM2 The Oracle base has been changed from /dbaapp/app/oracle to /dbaapp/app/grid [oracle@atm-db02 grid]$ cd $ORACLE_HOME/crs/script [oracle@atm-db02 script]$ mount-dbfs.sh stop unmounting DBFS from /mnt/dbfs_direct umounting the filesystem using '/bin/fusermount -u /mnt/dbfs_direct' Stop - stopped, now not mounted
And we will be start Database againg, restart DBFS_MOUNT resource & XAG service
Node 1:
[oracle@atm-db01 script]$ mount-dbfs.sh start mount-dbfs.sh mounting DBFS at /mnt/dbfs_direct from database atmdev ORACLE_SID is atmdev1 spawning dbfs_client command using SID atmdev1 nohup: redirecting stderr to stdout Start -- ONLINE
Node 2:
[root@atm-db02 ~]# su - oracle Last login: Thu Oct 19 10:58:22 +07 2023 on pts/3 [oracle@atm-db02 ~]$ . oraenv ORACLE_SID = [atmdev2] ? +ASM2 The Oracle base has been changed from /dbaapp/app/oracle to /dbaapp/app/grid [oracle@atm-db02 ~]$ cd $ORACLE_HOME/crs/script [oracle@atm-db02 script]$ mount-dbfs.sh start mount-dbfs.sh mounting DBFS at /mnt/dbfs_direct from database atmdev ORACLE_SID is atmdev2 spawning dbfs_client command using SID atmdev2 nohup: redirecting stderr to stdout Start -- ONLINE [oracle@atm-db02 script]$
Node 1:
[oracle@atm-db01 script]$ crsctl start resource dbfs_mount CRS-2672: Attempting to start 'dbfs_mount' on 'atm-db01' CRS-2672: Attempting to start 'dbfs_mount' on 'atm-db02' CRS-2676: Start of 'dbfs_mount' on 'atm-db01' succeeded CRS-2676: Start of 'dbfs_mount' on 'atm-db02' succeeded [oracle@atm-db01 script]$ crsctl stat res dbfs_mount NAME=dbfs_mount TYPE=local_resource TARGET=ONLINE , ONLINE STATE=ONLINE on atm-db01, ONLINE on atm-db02
2.3 Restart XAG & testing relocate again
[oracle@atm-db01 ~]$ agctl start goldengate gg_atmdev [oracle@atm-db01 ~]$ agctl status goldengate gg_atmdev Goldengate instance 'gg_atmdev' is running on atm-db01 [oracle@atm-db01 ~]$ agctl relocate goldengate gg_atmdev --node atm-db02 [oracle@atm-db01 ~]$ agctl status goldengate gg_atmdev Goldengate instance 'gg_atmdev' is running on atm-db02 [oracle@atm-db01 ~]$ agctl relocate goldengate gg_atmdev --node atm-db01 [oracle@atm-db01 ~]$ agctl status goldengate gg_atmdev Goldengate instance 'gg_atmdev' is running on atm-db01 [oracle@atm-db01 ~]$
3. Building marker, role, ddl for OGG schema user
3.1. Create user schema OGG and grant priviledges to
sys@atmdev> create user ogg identified by atmdev default tablespace tbs_ogg temporary tablespace temp quota unlimited on tbs_ogg; sys@atmdev> grant connect, resource, create session, dba, alter system to ogg; sys@atmdev> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ogg', privilege_type=>'CAPTURE', grant_optional_privileges=>'*');
3.2. Building the marker, role & ddl setup. Before 18, 19 & 21c GoldenGate software, the bundle scripts include are marker_setup.sql, role_setup.sql
and ddl_setup.sql. However, with 18 onward, Oracle replace them by sequences.sql. We just will execute sequences.sql & role_setup into OGG's user schema
ogg@atmdev> @$GGATE/sequence Please enter the name of a schema for the GoldenGate database objects: ogg Setting schema name to OGG UPDATE_SEQUENCE STATUS: Line/pos ---------------------------------------------------------------------------------------------------- Error ----------------------------------------------------------------- No errors No errors GETSEQFLUSH Line/pos ---------------------------------------------------------------------------------------------------- Error ----------------------------------------------------------------- No errors No errors SEQTRACE Line/pos ---------------------------------------------------------------------------------------------------- Error ----------------------------------------------------------------- No errors No errors MOVETARGETHWM Line/pos ---------------------------------------------------------------------------------------------------- Error ----------------------------------------------------------------- No errors No errors REPLICATE_SEQUENCE STATUS: Line/pos ---------------------------------------------------------------------------------------------------- Error ----------------------------------------------------------------- No errors No errors STATUS OF SEQUENCE SUPPORT ---------------------------------------------------------------------------------------------------- SUCCESSFUL installation of Oracle Sequence Replication support
4. Configuration within ggsci in both of source & target. Edit something apperciable to each target & source
Source:
[oracle@atm-db01 gg]$ ggsci ggsci> edit param mgr PORT 7809 DYNAMICPORTLIST 7810-7820 userid ogg@report, password atmdev ACCESSRULE, PROG REPLICAT, IPADDR *, ALLOW ggsci> edit param ./GLOBALS GGSCHEMA ogg CHECKPOINTTABLE ogg.checkpointtable GGSCI (atm-db01) 2> dblogin userid ogg@bank, password atmdev Successfully logged into database. GGSCI (atm-db01 as ogg@atmdev1) 3> ADD CHECKPOINTTABLE ogg.checkpointtable Successfully created checkpoint table ogg.checkpointtable.
5. Bug on GoldenGate 21 running on Oracle 11.2.0.4 Database Target
But, there an error in $GGATE/ggserr.log:
2023-10-19T16:45:03.032+0700 ERROR OGG-02912 Oracle GoldenGate Capture for Oracle,
exbrp.prm: Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later.
Fixing:
Fixing:
[oracle@atm-db04]$ cd $GGATE [oracle@atm-db04 gg]$ ls -llrt *.plb -rwxr-xr-x 1 oracle oinstall 1272 Dec 28 2010 prvtclkm.plb* -rwxr-xr-x 1 oracle oinstall 9487 May 27 2015 prvtlmpg.plb* [oracle@atm-db04 gg]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 19 16:48:30 2023 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options sys@atmdev> @prvtlmpg.plb Oracle GoldenGate Workaround prvtlmpg This script provides a temporary workaround for bug 17030189. It is strongly recommended that you apply the official Oracle Patch for bug 17030189 from My Oracle Support instead of using this workaround. This script must be executed in the mining database of Integrated Capture. You will be prompted for the username of the mining user. Use a double quoted identifier if the username is case sensitive or contains special characters. In a CDB environment, this script must be executed from the CDB$ROOT container and the mining user must be a common user. =========================== WARNING ========================== You MUST stop all Integrated Captures that belong to this mining user before proceeding! ================================================================ Enter Integrated Capture mining user: ogg Installing workaround... No errors. No errors. No errors. Installation completed. sys@atmdev> exit
End.
»
- trantuananh24hg's blog
- Log in to post comments