工作中的shell
1.定时执行oracle数据库存储过程
1.1.传递参数方式
1.1.1.创建sh脚本
11G:
vi execute_oracle_procedure.sh
source ~/.bash_profile
$ORACLE_HOME/bin/sqlplus -S monkey/monkey << eof >>/Data/scripts/log/${1//./_}.log
set heading off;
select 'start----->'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
begin
$1;
COMMIT;
end;
/
select 'end----->'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
exit;
eof
19C:
-- cdb创建用户并赋权
create user c##monkey identified by monkey;
grant connect to c##monkey;
grant set container,execute any procedure to c##monkey container=all;
vi execute_oracle_procedure.sh
source ~/.bash_profile
$ORACLE_HOME/bin/sqlplus -S c##monkey/monkey << eof >>/Data/scripts/log/${1//./_}.log
alter session set container=pdb1;
set heading off;
select 'start----->'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
begin
$1;
COMMIT;
end;
/
select 'end----->'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
exit;
eof
1.1.2.crontab写入排程
crontab -e
*/5 * * * * sh /Data/scripts/execute_oracle_procedure.sh monkey.test_procedure01
1.2.批量生成SH脚本
1.2.1.使用存储过程生成sh脚本
/* Formatted on 2021/3/23 上午 11:00:43 (QP5 v5.163.1008.3004) */
DECLARE
v_string VARCHAR2 (4000);
v_name VARCHAR2 (200);
v_count NUMBER;
v_filename VARCHAR2 (200);
v_dir VARCHAR2 (200);
-----Note:plase replace v_dir v_string with right string
BEGIN
v_dir := '/data/scripts/';
v_string := 'MONKEY.SP01 ,MONKEY.SP02';
WHILE INSTR (v_string, ',') > 0
LOOP
v_count := INSTR (v_string, ',');
v_name := TRIM (CHR (10) FROM TRIM (SUBSTR (v_string, 1, v_count - 1)));
v_filename := v_dir || REPLACE (v_name, '.', '_') || '.sh';
DBMS_OUTPUT.put_line (
'touch ' || v_dir || REPLACE (v_name, '.', '_') || '.sh');
DBMS_OUTPUT.put_line (
'echo ''' || 'source ~/.bash_profile'' >> ' || v_filename);
DBMS_OUTPUT.put_line ('echo ''' || 'date'' >> ' || v_filename);
DBMS_OUTPUT.put_line (
'echo '''
|| '$ORACLE_HOME/bin/sqlplus -S monkey/monkey. <<EOFarch1'' >> '
|| v_filename);
DBMS_OUTPUT.put_line ('echo ''' || 'begin'' >> ' || v_filename);
DBMS_OUTPUT.put_line ('echo ''' || v_name || ';'' >> ' || v_filename);
DBMS_OUTPUT.put_line ('echo ''' || ' COMMIT;'' >> ' || v_filename);
DBMS_OUTPUT.put_line ('echo ''' || 'end;'' >> ' || v_filename);
DBMS_OUTPUT.put_line ('echo ''' || ' /'' >> ' || v_filename);
DBMS_OUTPUT.put_line ('echo ''' || 'exit;'' >> ' || v_filename);
DBMS_OUTPUT.put_line ('echo ''' || 'EOFarch1'' >> ' || v_filename);
DBMS_OUTPUT.put_line ('echo ''' || 'date'' >> ' || v_filename);
v_string := SUBSTR (v_string, v_count + 1);
END LOOP;
END;
/
1.2.2.执行1.2.1生成的脚本
进入系统，执行1.2.1步生成的sh脚本，就会在对应的目录下生成对应存储过程的sh文件
1.3.shell脚本
#!/bin/bash
export ORACLE_HOME=your_oracle_home
export ORACLE_SID=your_oracle_sid
echo "start-------------->$(date +"%Y-%m-%d %H:%M:%S")";
# sqlplus -S '/as sysdba' <<EOFarch1
$ORACLE_HOME/bin/sqlplus -S monkey/monkey<<EOF
BEGIN
MONKEY.TEST_PROCEDURE;
COMMIT;
END;
/
exit
EOF
echo "stop-------------->$(date +"%Y-%m-%d %H:%M:%S")";
1.2.3.crontab写入排程
2.根据归档目录剩余量删除归档
#!/bin/bash
# name: del_archlog_rman.sh
# author: monkey
# date: 20231016
# describe: Determine whether to delete archived logs based on the remaining size of the directory
echo "#################♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥ Start----$(date) ♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥#################"
# Set Oracle environment variables, including ORACLE_SID and ORACLE_HOME
export ORACLE_SID=your_sid
export ORACLE_HOME=/path/to/oracle_home
# Set storage size limit (in MB)
storage_limit=100000
# Set default retention time (in hours) for old archive logs
retention_hours=6
# Function to get the available space for a directory
get_available_space() {
directory=$1
available_space=$(df -BM $directory | awk 'NR==2{print $4}' | sed 's/M//')
echo "$available_space"
}
# Use sqlplus to execute the archive log list command to get the archive log directory
archive_log_list=$($ORACLE_HOME/bin/sqlplus -S '/as sysdba' <<EOF
archive log list;
EOF
)
archive_log_dir=$(echo "$archive_log_list" | awk -F 'Archive destination' 'NF>1{print $2}' | tr -d ' ')
# Check if archive_log_dir is empty; if not, proceed with the rest of the script; otherwise, print a message and exit
if [ ! -d $archive_log_dir ] || [ -z $archive_log_dir ]; then
echo "Archive log directory not found. Exiting."
exit 1
fi
echo "Archive log directory: $archive_log_dir"
# Get the current available space for the archive log directory
available_space=$(get_available_space $archive_log_dir)
echo "Available space in the file system: ${available_space} MB"
# Define the log directory and create it if it doesn't exist
logdir=$(dirname $0)/log
[ ! -d $logdir ] && $(mkdir $logdir)
# Check if available space is less than the storage limit
if [ "$available_space" -lt "$storage_limit" ]; then
echo "Available space is below the limit. Deleting archive logs..."
# Use rman command to delete old archive logs
# If you want to record the RMAN log, you can replace /dev/null with $logdir/rman_$(date +"%Y-%m-%d_%H-%M-%S").log.
$ORACLE_HOME/bin/rman target / <<EOF >>/dev/null
delete force noprompt archivelog until time "sysdate-$retention_hours/24";
exit;
EOF
echo "Archive logs deleted."
else
echo "Available space is within the limit. No need to delete archive logs."
fi
echo "##################♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥ End----$(date) ♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥##################"
3.自动禁用vcs路径
#!/bin/bash
# name:IOErrorDisablePath.sh
# author:monkey
# date:20230602
# describe:When the path encounters an error, disable this path.
# The path to the monitored log file.
LOG_FILE="/var/adm/vx/dmpevents.log"
# The keyword being monitored.
KEYWORD="I/O error"
tail -F "$LOG_FILE" | while read -r line; do
if echo "$line" | grep -q "$KEYWORD"; then
ErrorPath=$(echo "$line" | grep -oP '(?<=Path )\w+(?=\()')
sleep 2
if vxdmpadm getsubpaths | grep -i "$ErrorPath" | grep -q "DISABLED"; then
echo "Path $ErrorPath is already disabled."
else
echo "Disabling path: $ErrorPath"
pwwn=$(vxdmpadm getportids path=$ErrorPath | tail -1 | awk '{print $4}')
vxdmpadm disable pwwn=$pwwn
fi
fi
done
4.rman备份
4.1.全备
source ~/.bash_profile
export ORACLE_SID=XXXXXX
rman target / catalog xxxx/xxxx@xxx.xxx.xxx.xxx:1521/catalog <<eof
run{
allocate channel c1 device type disk format '/backup/full_%d_%T_%s_%p.bak';
allocate channel c2 device type disk format '/backup/full_%d_%T_%s_%p.bak';
allocate channel c3 device type disk format '/backup/full_%d_%T_%s_%p.bak';
sql 'alter system archive log current';
backup as compressed backupset database plus archivelog;
backup current controlfile;
backup spfile;
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired backup;
}
exit;
eof
4.2.归档备份
source ~/.bash_profile
export ORACLE_SID=XXXXXX
rman target / catalog xxxx/xxxx@XXX.XXX.XXX.XXX:1521/catalog <<eof
run{
allocate channel c1 device type disk format '/backup/arch_%d_%T_%s_%p.bak';
allocate channel c2 device type disk format '/backup/arch_%d_%T_%s_%p.bak';
sql 'alter system archive log current';
backup as compressed backupset archivelog all delete input;
}
exit;
eof
