Oracle EXPDP备份脚本

楚枫默寒
已于 2024-06-18 11:42:23 修改
阅读量132
点赞数 4
文章标签： oracle 数据库
于 2024-05-13 11:56:45 首次发布
本文链接：https://blog.csdn.net/ffoooxx/article/details/138795525
版权
expbackup.sh
1、自动跟据数据库版本调用exp或expdp
2、备份完成后自动移到备份当天日期目录
3、可配置备份保留次数，自动清理过期备份
4、备份DG配置（如果有）、用户（非系统用户：如SYS，SYSTEM等）、用户权限、字符集信息
5、表空间信息
#!/bin/bash

####################################
# Author:        楚枫默寒
# Create_time:   2022/07/22
# Modify_time:   2024/06/18
####################################

umask 022
export ORACLE_SID=xxxx
export ORACLE_BASE=xxxx
export ORACLE_HOME=xxxx
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export PS1='$ORACLE_SID:$PWD>'

RELS=$(sqlplus -v) 
OS_VER_PRI=`echo ${RELS#*Release} | awk '{print $1}' | cut -f 1 -d '.'`

BACKPATH='/rman'                                  #备份路径
BAKDIR=`date --date "0 days ago" +"%Y%m%d"`_exp   #EXP备份目录
DBTIME=`date +%Y%m%d`                             #备份时间
ALTIME=`date +%Y%m%d_%H%M%S`_`hostname`           #备份时间

if [[ $OS_VER_PRI = "10" ]];then
COMM=`which exp`                         #备份命令

$COMM  \"/ as sysdba\" file=>(gzip>$BACKPATH/FULL_exp_$ALTIME.dmp.gz) log=$BACKPATH/FULL_exp_$ALTIME.log full=y direct=y recordlength=65535

sqlplus -s / as sysdba <<EOF
create pfile='$BACKPATH/pfile_$ALTIME.ora' from spfile;
set line 900
SET newpage none
SET heading off
SET feedback off
SET pagesize 0
SET echo on
SET newp none
SET trimout on
SET trimspool on
SET SERVEROUTPUT ON size 1000000
col name for a20
col value for a50
col display_value for a50
col CMD for a200
col instance_name for a10 
col host_name  for a20 
col online_status for a10 
col TABLESPACE_NAME  for a40 
col status for a10
col Extent for a10 
spool $BACKPATH/DG_$ALTIME.ini
select name,value,display_value from v\$parameter where name='log_archive_config' or (name like 'log_archive_dest_%' and name not like 'log_archive_dest_state_%') and value is not null order by 1;
col parameter for a30
spool $BACKPATH/CHARACTERSET_$ALTIME.conf
select * from nls_database_parameters where parameter like '%CHARACTERSET%' order by 1;

spool $BACKPATH/DB_username_$ALTIME.sql
select 'CREATE USER "'||a.username||'" IDENTIFIED BY VALUES '''||b.spare4||';'||b.password||''' DEFAULT TABLESPACE "'||a.default_tablespace||'" TEMPORARY TABLESPACE "'||a.temporary_tablespace||'";' CMD from dba_users a,sys.user\$ b where a.username not in ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','ZABBIX') and account_status='OPEN' and a.username= b.name ;
spool $BACKPATH/Privs_table_$ALTIME.sql
select 'grant '||PRIVILEGE||' on '||grantor||'.'||TABLE_NAME||' to '||GRANTEE||';' CMD from dba_tab_privs where ( grantee in (select username from dba_users where username not in ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','ZABBIX') and account_status='OPEN')) 
union 
select 'grant '||PRIVILEGE||' on '||grantor||'.'||TABLE_NAME||' to '||GRANTEE||';' CMD from dba_tab_privs where ( owner in (select username from dba_users where username not in ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','ZABBIX') and account_status='OPEN'));
spool $BACKPATH/Privs_user_$ALTIME.sql
DECLARE
  l_privs CLOB;
BEGIN
  FOR rec IN (SELECT PRIVILEGE, ADMIN_OPTION, GRANTEE FROM DBA_SYS_PRIVS where grantee in (select username from dba_users where username not in ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','ZABBIX') and account_status='OPEN')) LOOP
    l_privs := 'GRANT ' || rec.PRIVILEGE || ' TO ' || rec.GRANTEE ||
               CASE WHEN rec.ADMIN_OPTION = 'YES' THEN ' WITH ADMIN OPTION' ELSE '' END || ';';
    DBMS_OUTPUT.PUT_LINE(l_privs);
  END LOOP;
END;
/
SET heading on
SET pagesize 50
spool $BACKPATH/Tablespace_$ALTIME.txt
SELECT I.instance_name,I.host_name,A.status,A.autoextensible Extent,A.TABLESPACE_NAME,ROUND(A.TOTAL_SPACE/1024/1024/1024,0) TOTAL_GB,ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/1024/1024/1024,0) USED_GB
FROM (SELECT status,TABLESPACE_NAME,SUM(BYTES) BYTES_ALLOC,SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) TOTAL_SPACE,autoextensible FROM DBA_DATA_FILES GROUP BY STATUS, TABLESPACE_NAME, autoextensible) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES_FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B,
V\$INSTANCE I WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
union
SELECT I.instance_name,I.host_name,A.status,A.autoextensible Extent,A.TABLESPACE_NAME,ROUND(A.TOTAL_SPACE/1024/1024/1024,0) TOTAL_GB,ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/1024/1024/1024,0) USED_GB
FROM (SELECT STATUS,TABLESPACE_NAME,SUM(BYTES) BYTES_ALLOC,SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) TOTAL_SPACE,autoextensible FROM DBA_temp_FILES GROUP BY STATUS, TABLESPACE_NAME, autoextensible) A,
 (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES_FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B,
V\$INSTANCE I WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) ORDER BY 5;
SET heading off
SET pagesize 0
SELECT dbms_lob.substr(DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name))||';' SQL FROM DBA_TABLESPACES TS;
EOF
fi

if [[ $OS_VER_PRI = "11" ]]||[[ $OS_VER_PRI = "12" ]];then
COMM=`which expdp`                       #备份命令

$COMM \"/ as sysdba\" DIRECTORY=EXPDIR DUMPFILE=FULL_expdp_%U_$ALTIME.dmp logfile=FULL_expdp_00_$ALTIME.log parallel=5 full=y compression=ALL cluster=N

sqlplus -s / as sysdba <<EOF
create pfile='$BACKPATH/pfile_$ALTIME.ora' from spfile;
set line 900
SET newpage none
SET heading off
SET feedback off
SET pagesize 0
SET echo on
SET newp none
SET trimout on
SET trimspool on
SET SERVEROUTPUT ON size 1000000
col name for a20
col value for a50
col display_value for a50
col CMD for a200
col instance_name for a10 
col host_name  for a20 
col online_status for a10 
col TABLESPACE_NAME  for a40 
col status for a10
col Extent for a10 
spool $BACKPATH/DG_$ALTIME.ini
select name,value,display_value from v\$parameter where name='log_archive_config' or (name like 'log_archive_dest_%' and name not like 'log_archive_dest_state_%') and value is not null order by 1;
col parameter for a30
spool $BACKPATH/CHARACTERSET_$ALTIME.conf
select * from nls_database_parameters where parameter like '%CHARACTERSET%' order by 1;
spool $BACKPATH/DB_username_$ALTIME.sql
select 'CREATE USER "'||a.username||'" IDENTIFIED BY VALUES '''||b.spare4||';'||b.password||''' DEFAULT TABLESPACE "'||a.default_tablespace||'" TEMPORARY TABLESPACE "'||a.temporary_tablespace||'";' CMD from dba_users a,sys.user\$ b where a.username not in ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','ZABBIX') and account_status='OPEN' and a.username= b.name ;
spool $BACKPATH/Privs_table_$ALTIME.sql
select 'grant '||PRIVILEGE||' on '||grantor||'.'||TABLE_NAME||' to '||GRANTEE||';' CMD from dba_tab_privs where ( grantee in (select username from dba_users where username not in ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','ZABBIX') and account_status='OPEN')) 
union 
select 'grant '||PRIVILEGE||' on '||grantor||'.'||TABLE_NAME||' to '||GRANTEE||';' CMD from dba_tab_privs where ( owner in (select username from dba_users where username not in ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','ZABBIX') and account_status='OPEN'));
spool $BACKPATH/Privs_user_$ALTIME.sql
DECLARE
  l_privs CLOB;
BEGIN
  FOR rec IN (SELECT PRIVILEGE, ADMIN_OPTION, GRANTEE FROM DBA_SYS_PRIVS where grantee in (select username from dba_users where username not in ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','ZABBIX') and account_status='OPEN')) LOOP
    l_privs := 'GRANT ' || rec.PRIVILEGE || ' TO ' || rec.GRANTEE ||
               CASE WHEN rec.ADMIN_OPTION = 'YES' THEN ' WITH ADMIN OPTION' ELSE '' END || ';';
    DBMS_OUTPUT.PUT_LINE(l_privs);
  END LOOP;
END;
/
SET heading on
SET pagesize 50
spool $BACKPATH/Tablespace_$ALTIME.txt
SELECT I.instance_name,I.host_name,A.status,A.autoextensible Extent,A.TABLESPACE_NAME,ROUND(A.TOTAL_SPACE/1024/1024/1024,0) TOTAL_GB,ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/1024/1024/1024,0) USED_GB
FROM (SELECT status,TABLESPACE_NAME,SUM(BYTES) BYTES_ALLOC,SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) TOTAL_SPACE,autoextensible FROM DBA_DATA_FILES GROUP BY STATUS, TABLESPACE_NAME, autoextensible) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES_FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B,
V\$INSTANCE I WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
union
SELECT I.instance_name,I.host_name,A.status,A.autoextensible Extent,A.TABLESPACE_NAME,ROUND(A.TOTAL_SPACE/1024/1024/1024,0) TOTAL_GB,ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/1024/1024/1024,0) USED_GB
FROM (SELECT STATUS,TABLESPACE_NAME,SUM(BYTES) BYTES_ALLOC,SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) TOTAL_SPACE,autoextensible FROM DBA_temp_FILES GROUP BY STATUS, TABLESPACE_NAME, autoextensible) A,
 (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES_FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B,
V\$INSTANCE I WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) ORDER BY 5;
SET heading off
SET pagesize 0
SELECT dbms_lob.substr(DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name))||';' SQL FROM DBA_TABLESPACES TS;
EOF
fi

filelist=$(ls -l /${BACKPATH}/*${ALTIME}*)
if [[ -d ${BACKPATH}/${BAKDIR} ]];then
    filelist=`ls -l ${BACKPATH}/*${ALTIME}*`
    if [[ -n $filelist ]];then
        mv ${BACKPATH}/*${ALTIME}* ${BACKPATH}/${BAKDIR}/
    fi
else 
    mkdir -p ${BACKPATH}/${BAKDIR};
    if [[ -n $filelist ]];then
        mv ${BACKPATH}/*${ALTIME}* ${BACKPATH}/${BAKDIR}/
    fi
fi

#配置备份保留次数（默认值为2，保留1份）
REDUNDANCY=2
rmdir_no=$(find $BACKPATH -maxdepth 1 -type d -name "*_exp"|sort|tail -n $REDUNDANCY|wc -l)
if [ $rmdir_no -ge 2 ];then
    rmdir=$(find $BACKPATH -maxdepth 1 -type d -name "*_exp"|sort|tail -n $REDUNDANCY|head -n 1);
    if [ $rmdir ];then
	   echo $rmdir
       #rm -rf $rmdir;
    fi
fi