Solaris定时导出和导入数据

最新推荐文章于 2024-07-22 15:12:37 发布

columsms27884

最新推荐文章于 2024-07-22 15:12:37 发布

阅读量180

点赞数

文章标签：数据库运维 shell

Solaris 定时导出导入数据

目的

       编写shell脚本，凌晨定时执行导出数据，通过SCP命令拷贝dump文件到其他服务器
       源数据库 Solaris 11 Oracle RAC 11.2.0.4
       目标数据库 Solaris 11 Oracle RAC 11.1.0.7

步骤

      1. 建立SSH等价关系
      2. 导出脚本
      3. 导入脚本
      4. 建立定时任务

建立SSH等价关系

       参考文档http://docs.oracle.com/cd/E11882_01/install.112/e41961/manpreins.htm#CWLIN408
       源数据库节点oltpnode1
       目标数据库racnode1 172.31.178.130
       oracle用户建立等价关系。

       由于oracle用户在安装RAC时，已经生成公钥和私钥，因此只要将其已经生成的公钥拷贝到authorized_keys文件即可。
       源数据库oltpnode1节点的操作如下，将其公钥拷贝至目标数据库节点racnode1。/etc/hosts文件内定义racnode1。

点击(此处)折叠或打开

cd /export/home/oracle/.ssh
scp id_rsa.pub oracle@racnode1:/u01/backup

目标数据库racnode1节点，将其oltpnode1的公钥拷贝至authorized_keys

点击(此处)折叠或打开

cd /export/home/oracle/.ssh
cat /u01/backup/id_rsa.pub >>authorized_keys

目标数据库racnode1节点执行如下操作，将其公钥拷贝到源数据库节点oltpnode1。/etc/hosts文件内定义oltpnode1。

点击(此处)折叠或打开

cd /export/home/oracle/.ssh
scp id_rsa.pub oracle@oltpnode1:/u01/

源数据库oltpnode1执行如下操作

点击(此处)折叠或打开

cd /export/home/oracle/.ssh
cat /u01/backup/id_rsa.pub >>authorized_keys

        两个节点分别执行：
        ssh oltpnode1 date
        ssh racnode1 date

        第一次运行命令，系统提示数据密码，第二次运行之后，应该免密，如下所示。

导出脚本

创建数据库目录，创建脚本，确保脚本具有执行权限。
导出脚本较简单如下所示：

点击(此处)折叠或打开

#!/bin/bash
set -x
. /export/home/oracle/.bash_profile
DUMPFILEPRE=njexpschema
DUMPFILE=`date +%Y%m%d`
FILE=`date +%Y%m%d%H%M`
SHELLLOGFILE=/u02/acfsmounts/expdp/shell_info.log
DUMPDIR=/u02/acfsmounts/expdp
REMOTEHOST="172.31.178.130"
REMOTEDIR=/racbackup
echo "=======================================================================================================" >>$SHELLLOGFILE
echo "= =" >> $SHELLLOGFILE
echo "= backup_day_expdp.sh =" >>$SHELLLOGFILE
echo "= =" >> $SHELLLOGFILE
echo "=======================================================================================================" >>$SHELLLOGFILE
echo "[`date`] [INFO] shell script start............ " >>$SHELLLOGFILE
USERNAME="SDP_PM,SDP_SMECD,SDPNSI,SDPRPI,SDPSDI,sdp_usp,sdp_usm,sdp_miss"
#USERNAME="SDPRPI,SDPNSI"
echo "[`date`] [INFO] expdp start............. " >>$SHELLLOGFILE
expdp \'/ as sysdba\' directory=EXPDP SCHEMAS=$USERNAME dumpfile=$DUMPFILEPRE$DUMPFILE-%U.dump logfile=$DUMPFILEPRE$DUMPFILE.log COMPRESSION=all PARALLEL=4 VERSION=11.1
if [ $? -eq 0 ];then
echo "[`date`] [INFO] expdp success" >>$SHELLLOGFILE
echo "[`date`] [INFO] scp start" >>$SHELLLOGFILE
scp $DUMPDIR/$DUMPFILEPRE$DUMPFILE-*.dump oracle@$REMOTEHOST:$REMOTEDIR >>$SHELLLOGFILE
echo "[`date`] [INFO] scp end" >>$SHELLLOGFILE
echo "[`date`] [INFO] shell script success" >>$SHELLLOGFILE
else
echo "[`date`] [ERROR] expdp failture!!!!!!!!!!!!!!" >>$SHELLLOGFILE
echo "[`date`] [ERROR] shell script failture!!!!!!!!!!!!!!" >>$SHELLLOGFILE
fi
find $DUMPDIR -name "${DUMPFILEPRE}*.dump" -mtime +1 -exec rm {} \;
find $DUMPDIR -name "${DUMPFILEPRE}*.log" -mtime +30 -exec rm {} \;
echo " " >>$SHELLLOGFILE
echo " " >>$SHELLLOGFILE

注意：version参数，由于目标数据库为11.1，低于源数据库，因此必须加此参数，否则导入数据会失败。错误提示如下：

点击(此处)折叠或打开

bash3.00$ impdp ''\''/' as 'sysdba'\''' directory=DATA_IMPDP SCHEMAS=SDPNSI,SDPRPI dumpfile=njexpschema20160718-%U.dump logfile=njexpschema20160718.imp.log PARALLEL=8 cluster=N
Import: Release 11.1.0.7.0 - 64bit Production on Monday, 18 July, 2016 13:49:07
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "/u01/backup/njexpschema20160718-01.dump"

导入脚本

源数据库为RAC，只有一个鉴权系统使用此数据库，因此导入脚本中没有使用kill session命令，而是直接停止实例，断开所有AMSP用户会话。重新启动实例时使用限制模式，避免新的连接。导入完成之后，取消限制模式。
脚本如下：

点击(此处)折叠或打开

#!/bin/bash
. /export/home/oracle/.profile
set -x
echo " "
echo "==========================================================================================================="
echo "= ="
echo "=`date` script run ="
echo "= ="
echo "==========================================================================================================="
FILE=`date +%Y%m%d`
DUMP=/racbackup
#KILLSESSION=/racbackup/impdp/killsql$FILE.sql
SHELLLOGFILE=/racbackup/impdp/shell_info.log
srvctl stop service -d orcl -s amsp
srvctl stop listener -n racnode1
srvctl stop listener -n racnode2
srvctl stop instance -d orcl -i orcl1,orcl2
sqlplus / as sysdba << EOF
STARTUP RESTRICT;
exec dbms_lock.sleep(10);
drop user SDP_PM cascade;
drop user SDP_SMECD cascade;
drop user sdp_usm cascade;
drop user sdp_usp cascade;
drop user sdp_miss cascade;
drop user SDPNSI cascade;
drop user SDPRPI cascade;
drop user SDPSDI cascade;
exit
EOF
impdp \'/ as sysdba\' directory=DATA_IMPDP SCHEMAS=SDP_PM,SDP_SMECD,sdp_usm,sdp_usp,sdp_miss,SDPNSI,SDPRPI,SDPSDI dumpfile=njexpschema$FILE-%U.dump logfile=njexpschema$FILE.log PARALLEL=4 cluster=N
#impdp \'/ as sysdba\' directory=DATA_IMPDP SCHEMAS=SDPNSI,SDPRPI dumpfile=njexpschema$FILE-%U.dump logfile=njexpschema$FILE.imp.log PARALLEL=2 cluster=N
sqlplus / as sysdba << EOF
grant dba to SDP_PM;
grant dba to SDP_SMECD;
grant dba to sdp_usm;
grant dba to sdp_usp;
grant dba to sdp_miss;
grant dba to SDPNSI;
grant dba to SDPRPI;
grant dba to SDPSDI;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
alter system register;
exec dbms_utility.compile_schema('SDP_PM');
exec dbms_utility.compile_schema('SDP_SMECD');
exit
EOF
sqlplus sdp_pm/sdp_pm <<eof< eof</eof<>
@/racbackup/impdp/cr_index.sql
exit
EOF
srvctl start instance -d orcl -i orcl2
srvctl start listener -n racnode1
srvctl start listener -n racnode2
srvctl start service -d orcl -s amsp
#find $DUMP -name "njexpschema*dump" -exec rm {} \;
/u01/backup/cleararchivelog.sh

          要点
          a. 关于dbms_utility.compile_schema，编译无效对象。手工导入时发现，有较多无效对象，故运行此过程来解决。
          b. cr_index.sql脚本。由于11.2导入11.1时，全文索引报错 ORA-39083，ORA-06550，PLS-00306，ORA-06550，故执行此脚本重新创建全文索引。参见 MOS文档 ID 1574526.1
          c. amsp为应用的服务名
          d. 导入时，仅仅只能启动一个实例。否则报ORA-31693和ORA-29913错误如下：

定时任务

      oracle用户在源数据库执行如下命令
      crontab -e
      编辑如下，添加最后一行，每天凌晨1点运行

      oracle用户在目标数据库执行如下命令
      crontab -e
      编辑，添加最后一行。每天凌晨2点30执行

     要点
     a. cron日志保存在/var/cron/log文件中
     b. 用户使用crontab的权限。需要将用户加入到/etc/cron.d/cron.allow中
     c. 定时服务是否启动。svcs | grep cron。未启动，执行svcadm enable svc:/system/cron:default

参考文档

       1. ORA-39083, PLS-00306 while impdp creates Text index 文档 ID 1574526.1
       2. Solaris10 添加定时任务 http://blog.itpub.net/228190/viewspace-497984/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31142205/viewspace-2122364/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31142205/viewspace-2122364/