Found a nice script to be used for rolling upgrades on Oracle Support. Please check it out here Oracle11g Data Guard: Database Rolling Upgrade Shell Script
Advertisements
Category Archives: Shell script
Virtualbox automation scripts
As I’m trying to speed up things while I’m creating my ORACLE environments I started to build up some scripts to help me on doing it faster.
1. Adapt the network and the hosts file
Note: I’m running this script from my local server and not from VM, like this:
# root>ssh root@192.168.1.130 < $script_location/adapt_network.sh
================================== START =========================================
#!/bin/bash
################################################
# Adapting the /etc/sysconfig/network-scripts
################################################
# Regenerate the IP rules
#mv /etc/udev/rules.d/70-persistent-net.rules ~/70-persistent-net.rules.bck
#/sbin/udevadm trigger --type=devices --action=add
### adapt the IPADDR
cd /etc/sysconfig/network-scripts
grep -q '^IPADDR' ifcfg-eth1 && sed -i 's/^IPADDR.*/'IPADDR=192.168.100.10'/' ifcfg-eth1 || echo 'IPADDR=192.168.100.10' >> ifcfg-eth1
grep -q '^IPADDR' ifcfg-eth2 && sed -i 's/^IPADDR.*/'IPADDR=192.168.101.11'/' ifcfg-eth2 || echo 'IPADDR=192.168.101.10' >> ifcfg-eth2
### adapt the Name
grep -q '^NAME' ifcfg-eth1 && sed -i 's/^NAME.*/NAME="eth1"/' ifcfg-eth1 || echo 'NAME="eth1"' >> ifcfg-eth1
grep -q '^NAME' ifcfg-eth2 && sed -i 's/^NAME.*/NAME="eth2"/' ifcfg-eth2 || echo 'NAME="eth2"' >> ifcfg-eth2
### adapt the BOOTPROTO and remove the UUID
for f in ifcfg-eth{1..2}; do
grep -q '^BOOTPROTO' $f && sed -i 's/^BOOTPROTO.*/BOOTPROTO=none/' $f || echo 'BOOTPROTO=none' >> $f
sed -i '/UUID/d' $f
done
==================================== END =========================================
Note: I’m still searching for a way to use variables instead the “fixed” IP addresses; didn’t find a way yet.
Looking forwards for your comments.
Grab stats for partitions
Useful scripts to grab stats for partitions:
set serveroutput on
DECLARE
LV_SQL VARCHAR2(1000);
CURSOR C1 IS
SELECT TABLE_NAME T , PARTITION_NAME P , LAST_ANALYZED L FROM USER_TAB_PARTITIONS WHERE LAST_ANALYZED is null AND
TABLE_NAME = '&TABLE_NAME' and partition_name like ('&PART_NAME');
BEGIN
FOR I IN C1 LOOP
dbms_output.put_line('Gathering stats for :'||I.P);
LV_SQL:= 'BEGIN ';
LV_SQL:= LV_SQL ||'dbms_stats.gather_table_stats (''OWNER'','||':1'||',partname=>'||':2'||',granularity=>''partition'',estimate_percent => 1);' ;
LV_SQL:= LV_SQL ||' END ;';
EXECUTE IMMEDIATE LV_SQL USING I.T,I.P;
END LOOP;
END;
/
Or we can put it in a shell script:
#!/bin/bash
export ORACLE_SID=DB_NAME
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3
export PATH=$PATH:$ORACLE_HOME/bin
cd /u01/app/oracle/admin/
sqlplus -S /nolog << EOF > gather_part_stats.log 2>> gather_part_stats.log
conn user/passwd
set serveroutput on
DECLARE
LV_SQL VARCHAR2(1000);
CURSOR C1 IS
SELECT TABLE_NAME T , PARTITION_NAME P , LAST_ANALYZED L FROM USER_TAB_PARTITIONS WHERE (LAST_ANALYZED is null or LAST_ANALYZED < sysdate -7) AND
order by LAST_ANALYZED;
BEGIN
FOR I IN C1 LOOP
dbms_output.put_line('Gathering stats for :'||I.P);
BEGIN
LV_SQL:= 'BEGIN ';
LV_SQL:= LV_SQL ||'dbms_stats.gather_table_stats (''OWNER'','||':1'||',partname=>'||':2'||',granularity=>''partition'',estimate_percent => 1,CASCADE => TRUE, method_opt => ''FOR ALL COLUMNS SIZE 1'');' ;
LV_SQL:= LV_SQL ||' END ;';
EXECUTE IMMEDIATE LV_SQL USING I.T,I.P;
END;
END LOOP;
END;
/
exit
EOF
