Oracle编写定时同步数据库表数据的shell脚本

1.docker安装oracle12c

参考连接:
https://www.cnblogs.com/Dev0ps/p/10676930.html

2.测试环境准备

• 2.1 登录oracle容器

    docker exec -it ef7e5bc1c20e /bin/bash (ef7e5bc1c20e自己容器的ID)
  

• 2.2 sqlplus登录oracle，创建账号并授权

  su oracle #需切换用户
  
  cd /u01/app/oracle-product/12.1.0/xe/bin #oracle命令目录
  
  ./sqlplus / as sysdba #登录
  
  create tablespace testspace datafile '/u01/app/oracle/oradata/xe/testspace.dbf' size 3000m; #创建表空间
  
  create user test1 identified by 123456 default tablespace testspace;
  create user test2 identified by 123456 default tablespace testspace;
  grant dba, connect to test1;
  grant dba, connect to test2;
  

• 2.3退出使用test1用户登录

 ./sqlplus test1/123456

create table test1.UOS_JOB
(
  job_id      NUMBER not null,
  org_id      NUMBER,
  post_id     NUMBER,
  job_name    VARCHAR2(255),
  state       VARCHAR2(255) default (1),
  comments    VARCHAR2(255),
  ldap_id     VARCHAR2(255),
  update_date DATE,
  email       VARCHAR2(255)
)
tablespace testspace
create table test2.UOS_JOB
(
  job_id      NUMBER not null,
  org_id      NUMBER,
  post_id     NUMBER,
  job_name    VARCHAR2(255),
  state       VARCHAR2(255) default (1),
  comments    VARCHAR2(255),
  ldap_id     VARCHAR2(255),
  update_date DATE,
  email       VARCHAR2(255)
)
tablespace testspace

insert into uos_job(job_id,org_id,post_id,job_name) select 1,2,3,'sss' from dual;
insert into uos_job(job_id,org_id,post_id,job_name) select 22,23,32,'sssccc' from dual;
insert into uos_job(job_id,org_id,post_id,job_name) select 33,23,32,'ssswww' from dual;

现在test1用户的uos_job表有数据，test2用户的uos_job表没有数据。后面用shell脚本通过sqlplus进行定时同步.

• 2.4更改当前oracle的服务名

vi …/network/admin/tnsnames.ora

test =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST =172.17.0.2)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME=test )

    )

  )

添加如上内容，ip为容器ip。

./tnsping test #输出为ok即为成功

3.编写shell同步脚本

vi …/test.sh

#!/bin/bash
./sqlplus /NOLOG<<EOF>> /u01/app/oracle-product/12.1.0/xe/test.log

CONNECT test1/123456

SET ARRAYSIZE 5000;
SET COPYCOMMIT 20;
SET AUTOCOMMIT OFF;

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;


COPY FROM test1/123456@test TO test2/123456@test -
INSERT test2.uos_job (job_id,org_id,post_id,job_name,state,comments,ldap_id,update_date,email) -
USING SELECT job_id,org_id,post_id,job_name,state,comments,ldap_id,update_date,email -
FROM test1.uos_job WHERE 1=1;

EXIT COMMIT
EOF

/nolog 是不进行数据库登录。

4.定时执行

crontab -e # 添加如下内容

0 2 * * * /u01/app/oracle-product/12.1.0/xe/test.sh

5. 手动执行看下日志

chmod +x test.sh

./test.sh #日志如下

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 7 10:47:03 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> SQL> Connected.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> > > >
Array fetch/bind size is 5000. (arraysize is 5000)
Will commit after every 20 array binds. (copycommit is 20)
Maximum long size is 80. (long is 80)
   3 rows selected from test1@test.
   3 rows inserted into TEST2.UOS_JOB.
   3 rows committed into TEST2.UOS_JOB at test2@test.

SQL> SQL> Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production