Monday, September 26, 2016

Oracle Apex report cell background color based on value

1. Create a report and set the report static ID: DD
2. Create a after refresh DA, put following javascript:

$("#DD tbody tr td[headers='STATUS']").each(function(){
     if ($(this).text() == 'Completed')
        $(this).css("background-color","#31f904").css('color','white');
     else if ($(this).text() == 'WIP')
        $(this).css("background-color","#22e5be").css('color','white');
     else if ($(this).text() == 'Pending')
        $(this).css("background-color","#ff0800").css('color','white');
});

Here I am using the STATUS col of the report, you can change that to your col and adjust the value/color based on your requirement

Monday, September 19, 2016

Apex plugin - international phone item

Apex international phone plugin


I have an app where it will need customer to type in their cell phone numbers, Since the customer can be anywhere in the planet, it's a little harder to validate the phone # folks typed in.

Luckily I found this wonderful jquery plugin(intltelinput, you can find the plugin here https://github.com/jackocnr/intl-tel-input). 

I played with it and decided to write a Apex Plugin so that I can reuse it in my app:

1. it will decide your default country based on your laptop's IP address.
2. You can choose a country from the list
3. The plugin will return both the country code and the cell phone # so the item itself will have the format  like +country_code+cell phone number, although you just need to type in your cell phone #
4. It will validate the number you typed in based on the country you are in.

Here is the plugin:

Tuesday, September 6, 2016

Setup Oracle Database Active Data Guard and Broker automatically

Setup Oracle Database Active Data Guard and Broker automatically

Environment: Database: Oracle 12c(12.1.0). 
                         Server: Primary Database Server--P_server/Standby Database Server-S_server
                                       Center server: CC_server: this can auto ssh P_server/S_server without 
                                       login;
                         OS account:  oracle-- DB owner/oralist -- Listener owner
                         Primary Database SID/DB name: m12c1
                         OS: OEL 5
                         Filesystem Layout:  /u01 --> OH
                                                         /u02 ../u04: DB datafile/controlfile/online logfile
                                                         /orabackup:  backup fir
                                                        /oraarchive: arch file
                         
Goal:
   1. Setup DB m12c1 Active Data Guard between P_server and S_server
   2. Setup Data Guard Broker.

Setup:
    1. In CC server, create a dir called dataguard with following subdir:
              template ---> all templates
              tnsnames ---> all listener/tns related templates
              scripts     ---> all scripts:
   2. put followings into template subdir:
       a. primary.tmp
#!/bin/bash
. ~/.bash_profile
export ORACLE_SID=p_sid
export ORAENV_ASK=NO
. oraenv
export TNS_ADMIN=/u01/app/oracle/product/12clistener/network/admin
sqlplus -S / as sysdba<<EOF
spool /tmp/pp.archive
set heading off feedback off
select log_mode FROM v\$database;
spool off
EOF
archive=`cat /tmp/pp.archive | xargs echo`
sqlplus -S / as sysdba<<EOF
spool /tmp/pp.log
set heading off feedback off
select force_logging from v\$database;
spool off
EOF
LOG=`cat /tmp/pp.log | xargs echo`
sqlplus -S / as sysdba <<EOF
set heading off feedback off
spool /tmp/pp.redosize
select max(bytes/(1024*1024)) bytes
from v\$log;
spool off
EOF
test -f /tmp/initp_sid.ora
if [ $? == 0 ]; then
   rm /tmp/initp_sid.ora
fi
test -f /tmp/p_sid.ctl
if [ $? == 0 ]; then
 rm /tmp/p_sid.ctl
fi
TT=`cat /tmp/tnsnames.s_server|xargs echo`
grep "$TT" $TNS_ADMIN/tnsnames.ora
if [ $? != 0 ]; then
   grep s_short_p_sid $TNS_ADMIN/tnsnames.ora
   if [ $? == 0 ]; then
     cat $TNS_ADMIN/tnsnames.ora|sed -e "s/s_short_p_sid/xxxxx/g" >/tmp/tnsnames
     echo "$TT" >>/tmp/tnsnames
     mv /tmp/tnsnames  $TNS_ADMIN/tnsnames.ora
   fi
   echo "$TT" >>  $TNS_ADMIN/tnsnames.ora
fi
TT=`cat /tmp/tnsnames.p_server|xargs echo`
grep "$TT" $TNS_ADMIN/tnsnames.ora
if [ $? != 0 ]; then
   grep p_short_p_sid $TNS_ADMIN/tnsnames.ora
   if [ $? == 0 ]; then
     cat $TNS_ADMIN/tnsnames.ora|sed -e "s/p_short_p_sid/xxxxx/g" >/tmp/tnsnames
     echo "$TT" >>/tmp/tnsnames
     mv /tmp/tnsnames  $TNS_ADMIN/tnsnames.ora
   else
     echo "$TT" >>  $TNS_ADMIN/tnsnames.ora
   fi
fi
grep "listener12c =" $TNS_ADMIN/tnsnames.ora
if [ $? != 0 ]; then
   cat "/tmp/tt.p_server" >>$TNS_ADMIN/tnsnames.ora
fi
redo_size=`cat /tmp/pp.redosize | xargs echo`
a_size=`df /oraarchive|grep /oraarchive|awk '{print $(NF-2)}'`
#a_size=`df -k|grep oraarchive|awk '{print $2 }'`
arch_size1=$((${a_size}*7/1024))
arch_size=`printf "%.0f\n" $arch_size1`
if [ $archive != 'ARCHIVELOG' ];then
sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
EOF
fi
if [ $LOG == 'NO' ];then
sqlplus / as sysdba <<EOF
ALTER DATABASE FORCE LOGGING;
EOF
fi
cd $ORACLE_HOME/dbs
test -f orapwp_sid
if [ $? == 0 ]; then
   rm orapwp_sid
fi
orapwd file=orapwp_sid password=sbbbb# entries=20
sqlplus / as sysdba <<EOF
alter system set local_listener='listener12c' scope=both;
alter system register;
alter user sys identified by sbbbb#;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(p_sid,p_sid_stdy)' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=s_short_p_sid NOAFFIRM ASYNC COMPRESSION=ENABLE VALID_FOR=(ONLINE_
LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=p_sid_stdy' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;
ALTER SYSTEM SET FAL_SERVER=s_short_p_sid scope=both;
alter system set fal_client=p_short_p_sid scope=both;
alter system set archive_lag_target=1800 scope=both;
ALTER DATABASE FLASHBACK ON;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=20 scope=both;
create user dga identified by saaaa2#;
grant connect,sysdg to dga;
shutdown abort;
startup;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/p_sid/standby_redo01.log') SIZE ${redo_size}M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/p_sid/standby_redo02.log') SIZE ${redo_size}M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u04/oradata/p_sid/standby_redo03.log') SIZE ${redo_size}M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u04/oradata/p_sid/standby_redo04.log') SIZE ${redo_size}M;
alter system set db_recovery_file_dest_size=${arch_size}M scope=both;
ALTER SYSTEM SET dg_broker_start=true scope=both;
CREATE PFILE='/tmp/initp_sid.ora' from spfile;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/p_sid.ctl';


execute dbms_service.delete_service('p_sidp');
execute dbms_service.create_service(service_name => 'p_sidp',network_name => 'p_sidp',failover_method => 'BASIC', failover_type => 'SELECT',failover_retries => 180,failover_delay => 1);
create or replace trigger manage_service after startup on database
declare  role varchar2(30);
begin
  select database_role into role from v$database;
     if role = 'PRIMARY' THEN
          DBMS_SERVICE.START_SERVICE('p_sidp');
     else
          DBMS_SERVICE.STOP_SERVICE('p_sidp');
     end if;
end;
exec dbms_service.start_service('p_sidp');
EOF
grep ^p_sid: /etc/oratab >/tmp/oratabp_sid.tmp
### Modify listener.ora to prepare for dataguard broker config
cp /u01/app/oracle/product/12clistener/network/admin/listener.ora /tmp/listener.tmp
grep "(SID_NAME = p_sid)" /tmp/listener.tmp >/dev/null
if [ $? != 0 ]; then
start_line=`grep -n "(SID_LIST =" /u01/app/oracle/product/12clistener/network/admin/listener.ora|awk -F: '{prin
t $1}'`
let start_line=$start_line+1
let start_line_2=$start_line+1
let start_line_3=$start_line_2+1
let start_line_4=$start_line_3+1
sed -i "${start_line} i\    (SID_DESC =" /tmp/listener.tmp>/dev/null
sed -i "${start_line_2} i\       (SID_NAME = p_sid)" /tmp/listener.tmp>/dev/null
sed -i "${start_line_3} i\       (ORACLE_HOME = $ORACLE_HOME)" /tmp/listener.tmp>/dev/null
sed -i "${start_line_4} i\     )" /tmp/listener.tmp>/dev/null
fi
grep "(GLOBAL_DBNAME = p_sid_DG)" /tmp/listener.tmp>/dev/null >/dev/null
if [ $? != 0 ]; then
start_line=`grep -n "(SID_LIST =" /tmp/listener.tmp|awk -F: '{print $1}'`
let start_line=$start_line+1
let start_line_2=$start_line+1
let start_line_3=$start_line_2+1
let start_line_4=$start_line_3+1
let start_line_5=$start_line_4+1
sed -i "${start_line} i\    (SID_DESC =" /tmp/listener.tmp>/dev/null
sed -i "${start_line_2} i\       (GLOBAL_DBNAME = p_sid_DG)" /tmp/listener.tmp>/dev/null
sed -i "${start_line_3} i\       (SID_NAME = p_sid)" /tmp/listener.tmp>/dev/null
sed -i "${start_line_4} i\       (ORACLE_HOME = $ORACLE_HOME)" /tmp/listener.tmp>/dev/null
sed -i "${start_line_5} i\     )" /tmp/listener.tmp>/dev/null
fi
cp /tmp/listener.tmp $TNS_ADMIN/listener.ora
ssh -o StrictHostKeyChecking=no -q -l oralist p_server ". ~/.bash_profile;lsnrctl reload listener12c"

b. primary1.tmp:

#!/bin/bash
. ~/.bash_profile
export ORACLE_SID=p_sid
export ORAENV_ASK=NO
. oraenv
sqlplus / as sysdba <<EOF
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_u
nique_name=p_sid' scope=both;
alter system set log_archive_dest_2='' scope=both;
alter system set log_archive_min_succeed_dest=1 scope=both;
EOF

c. standby.tmp:

 standby.tmp
#!/bin/bash
. ~/.bash_profile
export TNS_ADMIN=/u01/app/oracle/product/12clistener/network/admin
grep ^p_sid: /etc/oratab
if [ $? != 0 ]; then
   cat /tmp/oratabp_sid.tmp >>/etc/oratab
fi
export ORACLE_SID=p_sid
export ORAENV_ASK=NO
. oraenv
mkdir -p /u02/oradata/p_sid /u03/oradata/p_sid /u04/oradata/p_sid
mkdir -p /orabackup/flash_recovery_area/p_sid
mkdir -p /u01/app/oracle/admin/p_sid
mkdir -p /u01/app/oracle/admin/p_sid/adump /u01/app/oracle/admin/p_sid/bdump /u01/app/oracle/admin/p_sid/dpdump
 /u01/app/oracle/admin/p_sid/cdump /u01/app/oracle/admin/p_sid/udump /u01/app/oracle/admin/p_sid/pfile /u01/app
/oracle/admin/p_sid/scripts
mkdir -p $ORACLE_BASE/diag/rdbms/p_sid/trace
mkdir -p $ORACLE_BASE/diag/rdbms/p_sid/cdump
cp $TNS_ADMIN/listener.ora /tmp/listener.tmp
grep "(SID_NAME = p_sid)" /u01/app/oracle/product/12clistener/network/admin/listener.ora >/dev/null
if [ $? != 0 ]; then
start_line=`grep -n "(SID_LIST =" /u01/app/oracle/product/12clistener/network/admin/listener.ora|awk -F: '{prin
t $1}'`
let start_line=$start_line+1
let start_line_2=$start_line+1
let start_line_3=$start_line_2+1
let start_line_4=$start_line_3+1
sed -i "${start_line} i\    (SID_DESC =" /tmp/listener.tmp>/dev/null
sed -i "${start_line_2} i\       (SID_NAME = p_sid)" /tmp/listener.tmp>/dev/null
sed -i "${start_line_3} i\       (ORACLE_HOME = $ORACLE_HOME)" /tmp/listener.tmp>/dev/null
sed -i "${start_line_4} i\     )" /tmp/listener.tmp>/dev/null
fi
grep "(GLOBAL_DBNAME = p_sid_sty_DG)" /tmp/listener.tmp>/dev/null >/dev/null
if [ $? != 0 ]; then
start_line=`grep -n "(SID_LIST =" /tmp/listener.tmp|awk -F: '{print $1}'`
let start_line=$start_line+1
let start_line_2=$start_line+1
let start_line_3=$start_line_2+1
let start_line_4=$start_line_3+1
let start_line_5=$start_line_4+1
sed -i "${start_line} i\    (SID_DESC =" /tmp/listener.tmp>/dev/null
sed -i "${start_line_2} i\       (GLOBAL_DBNAME = p_sid_sty_DG)" /tmp/listener.tmp>/dev/null
sed -i "${start_line_3} i\       (SID_NAME = p_sid)" /tmp/listener.tmp>/dev/null
sed -i "${start_line_4} i\       (ORACLE_HOME = $ORACLE_HOME)" /tmp/listener.tmp>/dev/null
sed -i "${start_line_5} i\     )" /tmp/listener.tmp>/dev/null
fi
cp /tmp/listener.tmp $TNS_ADMIN/listener.ora
grep "listener12c =" $TNS_ADMIN/tnsnames.ora
if [ $? != 0 ]; then
   cat "/tmp/tt.s_server" >>$TNS_ADMIN/tnsnames.ora
fi
TT=`cat /tmp/tnsnames.s_server`
grep "$TT" $TNS_ADMIN/tnsnames.ora
if [ $? != 0 ]; then
   grep s_short_p_sid $TNS_ADMIN/tnsnames.ora
   if [ $? == 0 ]; then
     cat $TNS_ADMIN/tnsnames.ora|sed -e "s/s_short_p_sid/xxxxx/g" >/tmp/tnsnames
     echo "$TT" >>/tmp/tnsnames
     mv /tmp/tnsnames  $TNS_ADMIN/tnsnames.ora
   else
     echo "$TT" >>  $TNS_ADMIN/tnsnames.ora
   fi
fi
TT=`cat /tmp/tnsnames.p_server`
grep "$TT" $TNS_ADMIN/tnsnames.ora
if [ $? != 0 ]; then
   grep p_short_p_sid $TNS_ADMIN/tnsnames.ora
   if [ $? == 0 ]; then
     cat $TNS_ADMIN/tnsnames.ora|sed -e "s/p_short_p_sid/xxxxx/g" >/tmp/tnsnames
     echo "$TT" >>/tmp/tnsnames
     mv /tmp/tnsnames  $TNS_ADMIN/tnsnames.ora
   fi
   echo "$TT" >>  $TNS_ADMIN/tnsnames.ora
fi
ctl1=`grep control_files= /tmp/initp_sid.ora|awk -F= '{print $2}'|sed "s/'//g"|awk -F, '{print $1}'|xargs echo`
cp  /tmp/p_sid.ctl $ctl1
ctl2=`grep control_files= /tmp/initp_sid.ora|awk -F= '{print $2}'|sed "s/'//g"|awk -F, '{print $2}'|xargs echo`
cp  /tmp/p_sid.ctl $ctl2
ctl3=`grep control_files= /tmp/initp_sid.ora|awk -F= '{print $2}'|sed "s/'//g"|awk -F, '{print $3}'|xargs echo`
cp  /tmp/p_sid.ctl $ctl3

d. standby.tmp2:

#!/bin/bash
. ~/.bash_profile
export TNS_ADMIN=/u01/app/oracle/product/12clistener/network/admin
export ORACLE_SID=p_sid
export ORAENV_ASK=NO
. oraenv
sqlplus / as sysdba <<EOF
shutdown abort;
STARTUP NOMOUNT PFILE='/tmp/initp_sid.ora';
EOF
rman TARGET sys/sbbbb#@p_short_p_sid AUXILIARY sys/sbbbb#@s_short_p_sid<<EOF
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='p_sid_stdy' COMMENT 'Is standby'
    SET LOG_ARCHIVE_DEST_2='SERVICE=p_shortr_p_sid ASYNC COMPRESSION=ENABLE VALID_FOR=(ONLINE_LOGFILES,PRIMARY_
ROLE) DB_UNIQUE_NAME=p_sid'
    SET FAL_SERVER='p_short_p_sid' COMMENT 'Is primary'
  NOFILENAMECHECK;
exit;
EOF
sqlplus / as sysdba<<EOF
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_u
nique_name=p_sid_stdy' scope=both;
alter system set log_archive_dest_2='' scope=both;
alter system set log_archive_min_succeed_dest=1 scope=both;
ALTER DATABASE FLASHBACK ON;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240 scope=both;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
EOF

e. listener.tmp

#!/bin/bash
. ~/.bash_profile
export ORACLE_SID=p_sid
export ORAENV_ASK=NO
. oraenv
export TNS_ADMIN=/u01/app/oracle/product/12clistener/network/admin
grep  'TCP.NODELAY=YES' $TNS_ADMIN/sqlnet.ora
if [ $? != 0 ]; then
 echo 'TCP.NODELAY=YES'>>$TNS_ADMIN/sqlnet.ora
fi
grep "DEFAULT_SDU_SIZ=32767" $TNS_ADMIN/sqlnet.ora
if [ $? != 0 ]; then
 echo 'DEFAULT_SDU_SIZ=32767'>>$TNS_ADMIN/sqlnet.ora
fi

f.  dg_check_status.tmp

#!/bin/bash
. ~/.bash_profile
export ORACLE_SID=p_sid
export ORAENV_ASK=NO
. oraenv
dgmgrl <<EOF | grep "Ready for Switchover:  Yes"
connect /
validate database p_sid
EOF
if [ $? == 0 ]; then
dgmgrl <<EOF | grep "Ready for Switchover:  Yes"
connect /
validate database p_sid_stdy
EOF
if [ $? != 0 ]; then
   exit 1
fi
else
  exit 1
fi
exit 0

3. Put followings file into tnsnames subdif:

a. tnsnames.tmp:

TNS=(DESCRIPTION=(SDU = 32767)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=DBSERVER)(PORT=1528)(SEND_BUF_SIZE=SSI
ZE)(RECV_BUF_SIZE=RSIZE)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=p_sid)))

b. tt.tmp:

listener12c =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = V_HOST )(PORT = 1525))
      (ADDRESS = (PROTOCOL = TCP)(HOST = V_HOST )(PORT = 1526))
      (ADDRESS = (PROTOCOL = TCP)(HOST = V_HOST )(PORT = 1527))
      (ADDRESS = (PROTOCOL = TCP)(HOST = V_HOST )(PORT = 1528))
      (ADDRESS = (PROTOCOL = TCP)(HOST = V_HOST )(PORT = 1529))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1530))
    )
  )

4. Put following  into scripts sub dir:

a. dg_setup.sh

#!/local/bash
beep()
{
  echo '\a'
}
red ()
{
   echo "$(tput setaf 1)$1$(tput sgr 0)"
}
green ()
{
   echo "$(tput setaf 2)$1$(tput sgr 0)"
}
warning()
{
   if [ $# != 1 ]; then
      beep;
   fi
   echo "\033[41m\033[37m" $1 "\033[33;00m"
}

if [ $# != 3 ]; then
   warning "Usage: setup_dg.sh primary_server standby_server DB_SID"
   exit;
fi
p_server=$1
s_server=$2
p_sid=$3
green "1. Checking environment ..."
echo $1 |grep aaaa.com>/dev/null
if [ $? != 0 ];then
   warning "$1 need to be full name"
   exit
fi
echo $2|grep aaaa.com>/dev/null
if [ $? != 0 ];then
   warning "$2 need to be full name"
   exit
fi
ssh -o StrictHostKeyChecking=no -q -l oracle $p_server 'hostname' >/dev/null
if [ $? != 0 ]; then
   warning "Can't auto ssh(passwordless) to oracle of $p_server from ccadmin of cc, please set up!"
   exit
fi
ssh -o StrictHostKeyChecking=no -q -l oracle $s_server 'hostname' >/dev/null
if [ $? != 0 ]; then
   warning "Can't auto ssh(passwordless) to oracle of $s_server from ccadmin of cc, please set up!"
   exit
fi
ssh -o StrictHostKeyChecking=no -q -l oralist $p_server 'hostname' >/dev/null
if [ $? != 0 ]; then
   warning "Can't auto ssh(passwordless) to oralist of $p_server from ccadmin of cc, please set up!"
   exit
fi
ssh -o StrictHostKeyChecking=no -q -l oralist $s_server 'hostname' >/dev/null
if [ $? != 0 ]; then
   warning "Can't auto ssh(passwordless) to oralist of $s_server from ccadmin of cc, please set up!"
   exit
fi
p_short=`echo $1|awk -F. '{print $1}'`
s_short=`echo $2|awk -F. '{print $1}'`

ping $p_server >/dev/null
if [ $p_server == $s_server ]; then
   warning "Primary Server can't be the same as Standy Server!"
   exit;
fi
if [ $? != 0 ]; then
   warning "Can't ping server $p_server, please check!"
   exit;
fi
ping $s_server >/dev/null
if [ $? != 0 ]; then
   warning "Can't ping server $p_server, please check!"
   exit;
fi
ssh -o StrictHostKeyChecking=no -q -l oracle $p_server "ps -ef|grep pmon|grep $p_sid$|egrep -v grep" >/dev/nul
l
if [ $? != 0 ]; then
   warning "Can't find primary database $p_sid in server $p_server, please check!"
   exit;
fi
ssh -o StrictHostKeyChecking=no -q -l oracle $s_server "ps -ef|grep pmon|grep $p_sid$|egrep -v grep" >/dev/nul
l
if [ $? == 0 ]; then
   warning "Find standby Database $p_sid in server $s_server up and running, please check!"
   exit;
fi
green " .... Passed!"
green "2. Prepare Active Data Guard Setup Between Primary DB($p_sid on $p_server) and Standby DB($p_sid on $s_s
erver ..."
ssh -o StrictHostKeyChecking=no -q -l oracle $p_server "ping -c 5 -s 64000 $s_server|grep 64008 >/tmp/ss" >/de
v/null
TNS_ADMIN='/u01/app/oracle/product/12clistener/network/admin'
LATENCY=`ssh -o StrictHostKeyChecking=no -q oracle@${p_server}  "cat /tmp/ss" |awk '{print $8}' |awk
-F= '{print $2}'|awk '{ total += $1; count++ } END { print total/count }'`
BDU=`echo "2500 * $LATENCY * 3 * 1000 / 8" | bc`
cat ../template/primary.tmp|sed -e "s/p_server/$p_server/g" -e "s/p_sid/$p_sid/g" -e "s/s_server/$s_server/g" -
e "s/p_short/$p_short/g" -e "s/s_short/$s_short/g" > ../template/$p_server.sh
cat ../template/primary1.tmp|sed -e "s/p_server/$p_server/g" -e "s/p_sid/$p_sid/g" -e "s/s_server/$s_server/g"
-e "s/p_short/$p_short/g" -e "s/s_short/$s_short/g" > ../template/${p_server}1.sh
cat ../template/standby.tmp|sed -e "s/p_server/$p_server/g" -e "s/p_sid/$p_sid/g" -e "s/s_server/$s_server/g" -
e "s/p_short/$p_short/g" -e "s/s_short/$s_short/g"  > ../template/$s_server.sh
cat ../template/standby.tmp2|sed -e "s/p_server/$p_server/g" -e "s/p_sid/$p_sid/g" -e "s/s_server/$s_server/g"
-e "s/p_short/$p_short/g" -e "s/s_short/$s_short/g"  > ../template/${s_server}2.sh
cat ../tnsnames/tnsnames.tmp|sed -e "s/SSIZE/$BDU/g" -e "s/RSIZE/$BDU/g" -e "s/p_sid/$p_sid/g" -e "s/TNS/${p_sh
ort}_${p_sid}/g" -e "s/DBSERVER/$p_server/g" >../tnsnames/tnsnames.$p_server
cat ../tnsnames/tnsnames.tmp|sed -e "s/SSIZE/$BDU/g" -e "s/RSIZE/$BDU/g" -e "s/p_sid/$p_sid/g" -e "s/TNS/${s_sh
ort}_${p_sid}/g" -e "s/DBSERVER/$s_server/g" >../tnsnames/tnsnames.$s_server
cat ../tnsnames/tt.tmp| sed -e "s/V_HOST/$p_server/g" >../tnsnames/tt.$p_server
cat ../tnsnames/tt.tmp| sed -e "s/V_HOST/$s_server/g" >../tnsnames/tt.$s_server
cat ../template/dgmgrl.tmp |sed -e "s/p_sid/$p_sid/g" -e "s/p_short/$p_short/g" -e "s/s_short/$s_short/g" >../t
emplate/dgmgrl.$p_short
cat ../template/listener.tmp |sed -e "s/p_sid/$p_sid/g" -e "s/p_short/$p_short/g" -e "s/s_short/$s_short/g" >..
/template/listener.$p_short
cat ../template/dg_check_status.tmp |sed -e "s/p_sid/$p_sid/g" -e "s/p_short/$p_short/g" -e "s/s_short/$s_short
/g" >dg_check_status.sh_$p_sid
scp -o StrictHostKeyChecking=no -q ../tnsnames/tt.$p_server oracle@$p_server:/tmp
scp -o StrictHostKeyChecking=no -q ../tnsnames/tt.$s_server oracle@$s_server:/tmp
###scp -o StrictHostKeyChecking=no -q ../template/$p_server.sh oracle@$p_server:/tmp
###scp -o StrictHostKeyChecking=no -q ../template/$s_server.sh oracle@$s_server:/tmp
scp -o StrictHostKeyChecking=no -q ../tnsnames/tnsnames.$p_server oracle@$p_server:/tmp
scp -o StrictHostKeyChecking=no -q ../tnsnames/tnsnames.$s_server oracle@$p_server:/tmp
scp -o StrictHostKeyChecking=no -q ../tnsnames/tnsnames.$p_server oracle@$s_server:/tmp
scp -o StrictHostKeyChecking=no -q ../tnsnames/tnsnames.$s_server oracle@$s_server:/tmp
#ssh -o StrictHostKeyChecking=no -q -l oracle $p_server ". ~/.bash_profile;ksh /tmp/$p_server.sh"
ssh -o StrictHostKeyChecking=no -q -l oralist $p_server 'bash -s'</template/listener.$p_short >/dev/null
ssh -o StrictHostKeyChecking=no -q -l oralist $s_server 'bash -s'</template/listener.$p_short >/dev/null
green "3. Prepare Primary Database for Data Guard...."
ssh -o StrictHostKeyChecking=no -q -l oracle $p_server 'bash -s' <../template/$p_server.sh >/dev/null
scp -o StrictHostKeyChecking=no -q oracle@$p_server:/tmp/init${p_sid}.ora ../template
scp -o StrictHostKeyChecking=no -q oracle@$p_server:/tmp/oratab${p_sid}.tmp ../template
scp -o StrictHostKeyChecking=no -q ../template/oratab${p_sid}.tmp oracle@$s_server:/tmp
PP=`echo $p_short|tr '[:lower:]' '[:upper:]'`
SS=`echo $s_short|tr '[:lower:]' '[:upper:]'`
SSID=`echo $p_sid|tr '[:lower:]' '[:upper:]'`
cat ../template/init$p_sid.ora | sed -e "s/${s_short}_${p_sid}/${p_short}_${p_sid}/g" -e "s/${p_sid}_stdy/$p_si
d/g" -e "s/$SS/$PP/g" >../template/init${p_sid}.tmp
cat ../template/init${p_sid}.tmp|grep -v fal_client>../template/init${p_sid}.tmp1
echo "*.fal_client='${SS}_${SSID}'">>../template/init${p_sid}.tmp1
cp ../template/init${p_sid}.tmp1 ../template/init${p_sid}.tmp
rm ../template/init${p_sid}.tmp1
scp -o StrictHostKeyChecking=no -q ../template/init${p_sid}.tmp oracle@$s_server:/tmp/init${p_sid}.ora
scp -o StrictHostKeyChecking=no -q oracle@$p_server:/tmp/$p_sid.ctl /tmp
scp -o StrictHostKeyChecking=no -q /tmp/$p_sid.ctl oracle@$s_server:/tmp
scp -o StrictHostKeyChecking=no -q oracle@$p_server:/u01/app/oracle/product/12.1.0/dbs/orapw$p_sid /tmp
scp -o StrictHostKeyChecking=no -q /tmp/orapw$p_sid oracle@$s_server:/u01/app/oracle/product/12.1.0/dbs
###scp -o StrictHostKeyChecking=no -q ../template/dgmgrl.$p_short oracle@$p_server:/tmp
#ssh -o StrictHostKeyChecking=no -q -l oracle $s_server ". ~/.bash_profile;ksh /tmp/$s_server.sh"
green "4. Create Standby Database of $p_sid on $s_server through RMAN Duplicate ...."
ssh -o StrictHostKeyChecking=no -q -l oracle $s_server 'bash -s' <../template/$s_server.sh >/tmp/${s_short}.lo
g
grep "Finished Duplicate Db" /tmp/${s_short}.log
if [ $? != 0 ]; then
   red "Issues when setup Standby DB using RMAN Duplicate command, please check detail at /tmp/${s_short}.log"
   exit;
fi
ssh -o StrictHostKeyChecking=no -q -l oralist $s_server '. /usr/users/oralist/.bash_profile;lsnrctl status list
ener12c|grep TNS-12541'
if [ $? == 0 ]; then
  ssh -o StrictHostKeyChecking=no -l oralist $s_server '. /usr/users/oralist/.bash_profile;lsnrctl start listen
er12c' >/dev/null
else
  ssh -o StrictHostKeyChecking=no -l oralist $s_server '. /usr/users/oralist/.bash_profile;lsnrctl reload liste
ner12c' >/dev/null
fi
green "5. Setup Data Guard Broker ..."
ssh -o StrictHostKeyChecking=no -q -l oracle $s_server 'bash -s' <../template/${s_server}2.sh >/tmp/${p_short}
.dg.log
ssh -o StrictHostKeyChecking=no -q -l oracle $p_server 'bash -s' <../template/${p_server}1.sh >>/tmp/${p_short
}.dg.log
ssh -o StrictHostKeyChecking=no -q -l oracle $p_server 'bash -s' <../template/dgmgrl.$p_short >>/tmp/${p_short
}.dg.log
ssh -q oracle@${p_server} 'bash -s'< dg_check_status.sh_${p_server} >>/tmp/${p_short}.dg.log
ff=`cat /tmp/${p_short}.dg.log |grep "Switchover:  Yes"|wc -l`
if [ $ff -lt 2 ]; then
   red "There are some issues while setup the DataGuard Broker, please check for details at /tmp/${p_short}.dg.
log"
   exit
fi
green "Congratulations!! You have successfully setup the Active Dataguard between $p_server($p_sid) and $s_serv
er($p_sid)!"

Usage:

From CC center:

cd dataguard/scripts

ksh P_server S_server m12c1

tnsnames.ora in the client side:

m12c1p=
(DESCRIPTION_LIST=
 (LOAD_BALANCE=off)
 (FAILOVER=on)
 (DESCRIPTION=
   (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
     (ADDRESS_LIST=
       (LOAD_BALANCE=on)
       (ADDRESS=(PROTOCOL=TCP)(HOST=P_server)(PORT=1526)))
       (CONNECT_DATA=(SERVICE_NAME=m12c1p)))
 (DESCRIPTION=
   (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
     (ADDRESS_LIST=
       (LOAD_BALANCE=on)
       (ADDRESS=(PROTOCOL=TCP)(HOST=S_server)(PORT=1526)))
       (CONNECT_DATA=(SERVICE_NAME=m12c1p))))


Note:

1. You can use your own setting to replace the $ORACLE_HOME/$TNS_ADMIN..
2. The sys and dga password in the scrips is dummy ones, please replace them with your own password.