数据泵导入 impdp 自动脚本 linux

陈尚荣

1474

3页

5次

2020-06-02

25墨值下载

#/bin/sh

#创建脚本目录

mkdir -pv /home/oracle/expdp_script

CMDS="

export ORACLE_HOME=$ORACLE_HOME

export ORACLE_SID=$ORACLE_SID

echo

echo ----- SUBSHELL ENV VARIABLES -----

echo

echo

$ENV_COMMANDS

echo ----- STARTING SQL EXECUTION -----

echo

sqlplus / as sysdba"

#创建备份目录

echo ====== 1. create OS DIR and expdp directory ======

CREATE_DIR=" <<\EOF

create or replace directory NEW_BACKUP as '/home/oracle/new_backup';

grant read,write on directory NEW_BACKUP to system;

exit

EOF

#创建用于备份的 OS 目录

mkdir -pv /home/oracle/new_backup

#执行创建目录语句

/bin/sh -c "$CMDS $CREATE_DIR"

echo ====== 1. create OS DIR and expdp directory done ======

echo ====== 2. create profile ======

EXEC_PRO=" <<\EOF

@/home/oracle/expdp_script/7.exec_profile.sql

exit

EOF

#执行创建 profile 脚本 /home/oracle/expdp_script/7.exec_profile.sql

/bin/sh -c "$CMDS $EXEC_PRO"

echo ====== 2. create profile done ======

#设置 USER 以及 TEMP 表空间自动扩展

echo ====== 3. extent tablespace USER and TEMP ======

#获取语句写入 /home/oracle/expdp_script/extent_tablespace.sql

cat > /home/oracle/expdp_script/extent_tablespace.sql << \EOF

set linesize 200;

set pagesize 100;

spool /home/oracle/expdp_script/extent_SQL.sql

select 'alter database datafile '||file_id||' autoextend on;' as "SQL"

from dba_data_files where tablespace_name='USERS';

select 'alter database tempfile '||file_id||' autoextend on;' as "SQL"

from dba_temp_files where tablespace_name='TEMP';

spool off

EOF

EXT_TBS=" <<\EOF

set long 300000;

set echo off;

set feedback off;

set heading off;

set pagesize 0;

set linesize 30000;

set trimout on;

set trimspool on;

set termout off;

@/home/oracle/expdp_script/extent_tablespace.sql

exit

EOF

EXT_TBS_SQL=" <<\EOF

@/home/oracle/expdp_script/extent_SQL.sql

exit

EOF

#执行脚本 /home/oracle/expdp_script/extent_tablespace.sql

#结果写入 /home/oracle/expdp_script/extent_SQL.sql

/bin/sh -c "$CMDS $EXT_TBS"

#更改 /home/oracle/expdp_script/extent_SQL.sql 脚本格式

#sed -i '/^SQL/d' /home/oracle/expdp_script/extent_SQL.sql

#sed -i '/----------/d' /home/oracle/expdp_script/extent_SQL.sql

#sed -i '/^ *$/d' /home/oracle/expdp_script/extent_SQL.sql

echo ====== exec extent SQL ======

#执行脚本 /home/oracle/expdp_script/extent_SQL.sql

/bin/sh -c "$CMDS $EXT_TBS_SQL"

echo ====== 3. extent tablespace USER and TEMP done ======

#创建表空间和数据文件

echo ====== 4. create tablespace and datafile ======

CREATE_TABLESPACE=" <<\EOF

@/home/oracle/expdp_script/2.exec_tablespace_SQL.sql

exit

EOF

#执行创建表空间以及数据文件脚本 home/oracle/expdp_script/2.exec_tablespace_SQL.sql

/bin/sh -c "$CMDS $CREATE_TABLESPACE"

echo ====== 4. create tablespace and datafile done ======

echo ====== 5. create user ======

of 3

25墨值下载

oracle 升级迁移

关注

评论