源代码_rspcp源代码-CSDN博客

本文链接：https://blog.csdn.net/laochi5/article/details/95735597

Centos安装Oracle11g

#!/bin/bash
#trap 'echo -e "`clear`\e[41;37moracle 11g 安装脚本\n作者：往事汝嫣\e[0m\n\e[1;31m警告：安装被终止\n请运行/root/uninstall.sh文件还原配置\e[0m";exit' 2
trap 'echo -e "\n\033[37;31;5m安装已被中断\033[39;49;0m";read -p "输入任意键继续："' 2
clear
echo -e "\e[1;31m本脚本适用于centOS 6首次安装Oracle 11g\e[0m"
sleep 2
echo -e "\e[1;31m整个安装过程耗时大约30分钟并且要保证网络通畅\e[0m"
sleep 2
echo -e "\e[1;31m安装过程会生成\e[1;42m/root/uninstall.sh\e[0m\e[1;31m用于还原配置和后期卸载Oracle\e[0m"
sleep 2
echo -e "\e[1;31m暂停键：Ctrl+C（测试功能）\e[0m"
sleep 5
wait
for time in {5..1}
do
clear
echo -e "\e[41;37moracle 11g 安装脚本\e[0m"
echo -e "\e[41;37m作者：往事汝嫣\e[0m"
echo -e "\e[1;31m安装将在$time秒后运行...\e[0m"
sleep 1
wait
done
echo -e "\e[1;42m文件校验...\e[0m"
if [ -e /tmp/linux.x64_11gR2_database_1of2.zip ] && [ -e /tmp/linux.x64_11gR2_database_2of2.zip ] ; then
sleep 3
wait
echo -e "\e[1;42m文件校验成功\e[0m"
touch /root/uninstall.sh
chmod 777 /root/uninstall.sh
echo  '#!/bin/bash' >> /root/uninstall.sh
sleep 3
wait
clear
echo -e "\e[41;37moracle 11g 安装脚本\e[0m"
echo -e "\e[41;37m作者：往事汝嫣\e[0m"
cd /tmp
echo -e "\033[37;31;5m文件解压中请稍等...\033[39;49;0m"
echo "`unzip linux.x64_11gR2_database_1of2.zip && unzip linux.x64_11gR2_database_2of2.zip`" &> /dev/null 
echo "rm -rf /tmp/database" >> /root/uninstall.sh
clear
echo -e "\e[41;37moracle 11g 安装脚本\e[0m"
echo -e "\e[41;37m作者：往事汝嫣\e[0m"
echo -e "\e[1;31m解压完成\e[0m"
sleep 3
wait 
clear
echo -e "\e[41;37moracle 11g 安装脚本\e[0m"
echo -e "\e[41;37m作者：往事汝嫣\e[0m"
read -p "请输入计算机名称:" hostname
cp /etc/sysconfig/network /etc/sysconfig/network.bank
sed -i 's/HOSTNAM/#HOSTNAME/g' /etc/sysconfig/network
echo "HOSTNAME=$hostname" >> /etc/sysconfig/network
echo 'mv  /etc/sysconfig/network.bank /etc/sysconfig/network' >> /root/uninstall.sh
cp /etc/hosts /etc/hosts.bank
echo "`ifconfig |grep "inet addr:" |grep -v "127"|cut -d: -f2 |cut -d' ' -f1` $hostname" >> /etc/hosts
echo 'mv /etc/hosts.bank /etc/hosts' >> /root/uninstall.sh
echo -e "\e[1;42m更新计算机名成功\e[0m"
source /etc/sysconfig/network &> /dev/null
source /etc/hosts &> /dev/null
#
cp /etc/selinux/config /etc/selinux/config.bank
sed -i 's/SELINUX=/#SELINUX=/g' /etc/selinux/config
echo "SELINUX=disabled" >> /etc/selinux/config
echo 'mv /etc/selinux/config.bank /etc/selinux/config' >> /root/uninstall.sh
source /etc/selinux/config &> /dev/null
echo -e "\e[1;42mSELINUX更新成功\e[0m"
echo `service iptables stop` &> /dev/null
echo -e "\e[1;42m防火墙关闭成功\e[0m"
sleep 3
wait
#
clear
echo -e "\e[41;37moracle 11g 安装脚本\e[0m"
echo -e "\e[41;37m作者：往事汝嫣\e[0m"
echo -e "\033[37;31;5m依赖包安装中请稍等...\033[39;49;0m"
yum -y install gcc make binutils gcc-c++ compat-libstdc++-33 elfutils-libelf-devel elfutils-libelf-devel-static elfutils-libelf-devel ksh libaio libaio-devel numactl-devel sysstat unixODBC unixODBC-devel pcre-devel libXp* &> /dev/null
sleep 3
wait
clear
echo -e "\e[41;37moracle 11g 安装脚本\e[0m"
echo -e "\e[41;37m作者：往事汝嫣\e[0m"
echo -e "\e[1;31m依赖包安装完成\e[0m"
sleep 3
wait
#
clear
echo -e "\e[41;37moracle 11g 安装脚本\e[0m"
echo -e "\e[41;37m作者：往事汝嫣\e[0m"
cp /etc/sysctl.conf /etc/sysctl.conf.bank
echo "io-max-nr = 1048576" >> /etc/sysctl.conf
echo "fs.file-max = 6815744" >> /etc/sysctl.conf
echo "kernel.shmall = 2097152" >> /etc/sysctl.conf
echo "kernel.shmmax = 1073741824" >> /etc/sysctl.conf
echo "kernel.shmmni = 4096" >> /etc/sysctl.conf
echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf
echo "net.ipv4.ip_local_port_range = 9000 65500" >> /etc/sysctl.conf
echo "net.core.rmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.rmem_max = 4194304" >> /etc/sysctl.conf
echo "net.core.wmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.wmem_max = 1048576" >> /etc/sysctl.conf
echo 'mv /etc/sysctl.conf.bank /etc/sysctl.conf' >> /root/uninstall.sh
sysctl -p &> /dev/null
echo -e "\e[1;42m内核修改成功\e[0m"
#
cp /etc/security/limits.conf /etc/security/limits.conf.bank
echo "oracle           soft    nproc           2047" >> /etc/security/limits.conf
echo "oracle           hard    nproc           16384" >> /etc/security/limits.conf
echo "oracle           soft    nofile          1024" >> /etc/security/limits.conf
echo "oracle           hard    nofile          65536" >> /etc/security/limits.conf
echo "oracle           soft    stack           10240" >> /etc/security/limits.conf
echo 'mv /etc/security/limits.conf.bank /etc/security/limits.conf' >> /root/uninstall.sh
source  /etc/security/limits.conf &> /dev/null
echo -e "\e[1;42m限制文件修改成功\e[0m"
#
cp /etc/pam.d/login /etc/pam.d/login.bank
echo "session  required   /lib64/security/pam_limits.so" >> /etc/pam.d/login
echo "session  required   pam_limits.so" >> /etc/pam.d/login
echo 'mv /etc/pam.d/login.bank /etc/pam.d/login' >> /root/uninstall.sh
echo -e "\e[1;42m登录文件修改成功\e[0m"
#
cp /etc/profile /etc/profile.bank
echo "if [ $USER = "oracle" ]; then" >> /etc/profile
echo "   ulimit -u 16384 -n 65536" >> /etc/profile 
echo "fi" >> /etc/profile
echo 'mv /etc/profile.bank /etc/profile' >> /root/uninstall.sh
source /etc/profile &> /dev/null
echo -e "\e[1;42m环境配置修改完成\e[0m"
#
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
echo -e "\e[1;42m用户创建完成\e[0m"
read -p  "初始化oracle用户的密码：" -s passwd_oracle
echo "$passwd_oracle" | passwd --stdin oracle &> /dev/null
if [ $? -eq 0 ]
then
echo -e "\e[1;42m\n密码更新成功\e[0m"
else
echo -e "\e[1;31m密码更新失败请手动更新密码\e[0m"
fi
echo -e "userdel -rf oracle\ngroupdel oinstall\ngroupdel dba" >> /root/uninstall.sh
#
mkdir -p /u01/app/oracle/product/11.2.0
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/inventory
mkdir -p /u01/app/oracle/fast_recovery_area
chown -R oracle:oinstall /u01/app
chmod -R 775 /u01/app/
echo "rm -rf /u01" >> /root/uninstall.sh
echo -e "\e[1;42m安装路径创建成功\e[0m"
#
echo "export PATH" >> /home/oracle/.bash_profile
echo "ORACLE_BASE=/u01/app/oracle" >> /home/oracle/.bash_profile
echo "ORACLE_HOME=\$ORACLE_BASE/product/11.2.0" >> /home/oracle/.bash_profile
echo "ORACLE_SID=orcl" >> /home/oracle/.bash_profile
echo "PATH=\$PATH:\$ORACLE_HOME/bin" >> /home/oracle/.bash_profile
echo "export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH" >> /home/oracle/.bash_profile
echo -e "\e[1;42m环境变量修改完成\e[0m"
#
cd /tmp/database/response/ 
cp db_install.rsp db_install.rsp.bank
sed -i 's/oracle.install.option=/oracle.install.option=INSTALL_DB_SWONLY/g' db_install.rsp
sed -i 's/ORACLE_HOSTNAME=/ORACLE_HOSTNAME='$hostname'/g' db_install.rsp
sed -i 's/UNIX_GROUP_NAME=/UNIX_GROUP_NAME=oinstall/g' db_install.rsp
sed -i 's/INVENTORY_LOCATION=/INVENTORY_LOCATION=\/u01\/app\/inventory/g' db_install.rsp
sed -i 's/SELECTED_LANGUAGES=/SELECTED_LANGUAGES=en,zh_CN/g' db_install.rsp
sed -i 's/ORACLE_HOME=/ORACLE_HOME=\/u01\/app\/oracle\/product\/11.2.0/g' db_install.rsp
sed -i 's/ORACLE_BASE=/ORACLE_BASE=\/u01\/app\/oracle/g' db_install.rsp
sed -i 's/oracle.install.db.InstallEdition=/oracle.install.db.InstallEdition=EE/g' db_install.rsp
sed -i 's/oracle.install.db.DBA_GROUP=/oracle.install.db.DBA_GROUP=dba/g' db_install.rsp
sed -i 's/oracle.install.db.OPER_GROUP=/oracle.install.db.OPER_GROUP=dba/g' db_install.rsp
sed -i 's/DECLINE_SECURITY_UPDATES=/DECLINE_SECURITY_UPDATES=true/g' db_install.rsp
cp /tmp/database/response/dbca.rsp /tmp/database/response/dbca.rsp.bank
sed -i 's/GDBNAME = "orcl11g.us.oracle.com"/GDBNAME = "orcl"/g' /tmp/database/response/dbca.rsp
sed -i 's/SID = "orcl11g"/SID = "orcl"/g' /tmp/database/response/dbca.rsp
sed -i 's/#SYSPASSWORD = "password"/SYSPASSWORD = "oracle"/g' /tmp/database/response/dbca.rsp
sed -i 's/#SYSTEMPASSWORD = "password"/SYSTEMPASSWORD = "oracle"/g' /tmp/database/response/dbca.rsp
sed -i 's/#SYSMANPASSWORD = "password"/SYSMANPASSWORD = "oracle"/g' /tmp/database/response/dbca.rsp
sed -i 's/#DBSNMPPASSWORD = "password"/DBSNMPPASSWORD = "oracle"/g' /tmp/database/response/dbca.rsp
sed -i 's/#DATAFILEDESTINATION =/DATAFILEDESTINATION =\/u01\/app\/oracle\/oradata/g' /tmp/database/response/dbca.rsp
sed -i 's/#RECOVERYAREADESTINATION=/RECOVERYAREADESTINATION=\/u01\/app\/oracle\/fast_recovery_area/g' /tmp/database/response/dbca.rsp
sed -i 's/#CHARACTERSET = "US7ASCII"/CHARACTERSET = "ZHS16GBK"/g' /tmp/database/response/dbca.rsp
sed -i 's/#TOTALMEMORY = "800"/TOTALMEMORY = "1638"/g' /tmp/database/response/dbca.rsp
echo -e "\e[1;42moracle安装文件配置成功\e[0m"
sleep 3 
wait
clear
echo -e "\e[41;37moracle 11g 安装脚本\e[0m"
echo -e "\e[41;37m作者：往事汝嫣\e[0m"
su - oracle <<EOF
unset DISPLAY;
cd /tmp/database;
./runInstaller -silent -ignorePrereq -ignoreSysPrereqs -responseFile /tmp/database/response/db_install.rsp;
clear;
echo -e "\e[41;37moracle 11g 安装脚本\e[0m";
echo -e "\e[41;37m作者：往事汝嫣\e[0m";
echo -e "\033[37;31;5m请勿操作程序安装中请稍等...\033[39;49;0m";
EOF
echo -e "rm -rf /usr/local/bin/dbhome\nrm -rf /usr/local/bin/oraenv\nrm -rf /usr/local/bin/coraenv\nrm -rf /etc/oratab\nrm -rf /etc/oraInst.loc"  >> /root/uninstall.sh
echo 'echo -e "\033[37;31;5m数据库卸载完成\033[39;49;0m"' >> /root/uninstall.sh
num=0
while [ 0 -eq $num ] 
do
tail -n 1 /u01/app/inventory/logs/installActions*.log |grep "INFO: Shutdown Oracle Database 11g Release 2 Installer" &> /dev/null
if [ $? -eq 0 ]
then
clear
echo -e "\e[41;37moracle 11g 安装脚本\e[0m"
echo -e "\e[41;37m作者：往事汝嫣\e[0m"
sh /u01/app/inventory/orainstRoot.sh 
sh /u01/app/oracle/product/11.2.0/root.sh
clear 
echo -e "\e[41;37moracle 11g 安装脚本\e[0m"
echo -e "\e[41;37m作者：往事汝嫣\e[0m" 
su - oracle <<EOF
export DISPLAY=localhost:0.0;
echo -e "\033[37;31;5m监听配置中...\033[39;49;0m";
netca -silent -responsesFile /tmp/database/response/netca.rsp;
sleep 3;
wait;
clear;
echo -e "\e[41;37moracle 11g 安装脚本\e[0m";
echo -e "\e[41;37m作者：往事汝嫣\e[0m";
echo -e "\e[1;31m监听启动成功\e[0m";
clear;
echo -e "\e[41;37moracle 11g 安装脚本\e[0m";
echo -e "\e[41;37m作者：往事汝嫣\e[0m";
echo -e "\033[37;31;5m数据库实例安装中...\033[39;49;0m";
dbca -silent -responseFile /tmp/database/response/dbca.rsp;
EOF
num=1
fi
sleep 10
done
clear
echo -e "\e[41;37moracle 11g 安装脚本\e[0m"
echo -e "\e[41;37m作者：往事汝嫣\e[0m"
echo -e "\e[41;37moracle 11g 安装完成\e[0m"
cd /root
echo -e "\e[1;31m卸载或恢复配置请运行uninstall.sh\e[0m"
ls
else
echo -e "\e[1;31m文件校验失败\e[0m"
echo -e "\e[1;31m请将oracle11g安装文件上传到/tmp目录下\e[0m"
fi

面试

declare
   cursor cur is select * from t_sql where is_completed = 'N';
   cursor cur1 is select '''||sql_text||''' from t_sql_2;
   v t_sql%rowtype;
   v_sql varchar2(200);
begin
   open cur;
   loop
      fetch cur into v;
      exit when cur%notfound;
      --执行插入操作
      if v.exec_group = 1 then
        insert into t_sql_1
        values (replace(v.sql_text,'?',v.param),v.param,v.exec_group,v.is_completed);
        commit;
      elsif v.exec_group = 2 then
      insert into t_sql_2
        values (replace(v.sql_text,'?',v.param),v.param,v.exec_group,v.is_completed);
        commit;
      end if;  
   end loop;
   --关闭游标cur
   close cur;
   --打开游标cur1
   open cur1;
   loop
      fetch cur1 into v_sql;
      exit when cur1%notfound;
      execute immediate v_sql;
      commit;
      end loop;
   close cur1;
   exception
       when others then
            dbms_output.put_line('发生错误，请联系管理员');
end;

oracle数据库操作

#!/bin/bash
read -p "DB2 username: " dbUserName
read -p "DB2 password: " -s dbPasswd
# db2连接字符串
#ConnectToDB="db2 connect to dbName user $dbUserName using $dbPasswd"

#数据库内拿到查询SQL
sql="SELECT ID,SQL FROM SCOTT.TEST;"
#连接到数据库,将SQL保存到1.sql中
sqlplus -S /nolog >>1.sql <<EOF
conn $dbUserName/$dbPasswd
set heading off
set verify off
set pagesize 5000
set linesize 5000
$sql
exit
EOF

#格式化1.sql文件
sed -i 's/^[ \t]*//g;/^$/d' 1.sql
#如果文件里面有序号取序号之后的SQL语句放入新文件sql.txt中
awk '{for (i=2 ;i<=NF;i++) printf $i " "; printf "\n" }' 1.sql >> sql.txt
awk '{print $1}' 1.sql >> ID.txt
f=./sql.txt #遍历该文件
cat $f |while read line 
do
sqlplus -S /nolog >> $PWD/data.txt << EOF 
conn $dbUserName/$dbPasswd
set heading off
set verify off
set pagesize 5000
set linesize 5000
$line
exit
EOF
done
#格式化sql.txt文件
sed -i 's/^[ \t]*//g;/^$/d' data.txt
#拼接文件
paste ID.txt data.txt >> data.ok
#统计
awk '{for(i=2;i<=NF;i++)a[i]+=$i;print}END{printf "TOTAL:\t";for(j=2;j<=NF;j++)printf a[j]"\t"; print""}' data.ok >> ok.txt
#删除过程文件
rm -rf $PWD/1.sql $PWD/ID.txt $PWD/sql.txt $PWD/data.txt $PWD/data.ok