Schedulazione IMP a crontab - NETWORK LINK
Copiare ogni martedì alle 9 solo i dati dello schema SAKAMO dal db di prodzune PROD02 a l db di test TEST02.
Creare su TEST02 un network link verso PROD02
CREATE DATABASE LINK DB_PROD02
CONNECT TO SYSTEM
IDENTIFIED BY <PWD>
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL = TCP)(HOST=server06.intra.camera.it)(PORT = 1521)))(CONNECT_DATA=(SERVICE_NAME = PROD02)))';
oppure così
CREATE DATABASE LINK DB_PROD02
CONNECT TO SYSTEM
IDENTIFIED BY <PWD>
USING 'PROD02';
Controllare che PROD02 sia definito nel tnsnames.ora del server di test.
Lo script sh a crontab lancia in sequenza da sqlplus:
- disabilita trigger
- disabilita FK
- import utilizzando un netwok link
- abilita FK
- abilita trigger
[oracle@server06 ~]$ crontab -l
00 09 2 * * /home/oracle/work/schedul_exp.sh 1>>/home/oracle/work/schedul_exp.log 2>&1
[oracle@server06 work]$ cat schedul_exp.sh
#!/bin/bash -x
export ORACLE_HOME=/u01/app/oracle/product/11gR24
export ORACLE_BASE=/u01/app/oracle
export PATH=/bin:/usr/bin:${ORACLE_HOME}/bin:/usr/local/bin:/usr/sbin
export ORACLE_SID=TEST02
unset NLS_LANG
export QBILLDIR=/home/oracle/work
$ORACLE_HOME/bin/sqlplus << EOF
sys/so12mee0 as sysdba
@$QBILLDIR/disable_trigger.sql
@$QBILLDIR/disable_FK.sql
@$QBILLDIR/imp_schema.sql
@$QBILLDIR/enable_FK.sql
@$QBILLDIR/enable_trigger.sql
exit
EOF
[oracle@server06 work]$ cat disable_trigger.sql
set serveroutput on
spool disable_trigger.log
prompt ============================
prompt Start script disable trigger
prompt ============================
BEGIN
FOR cur IN (
select owner, trigger_name
from sys.dba_triggers
where owner in ('SAKAMO')
)
LOOP
begin
DBMS_OUTPUT.put_line ('alter trigger ' || cur.owner || '.'|| cur.trigger_name ||' disable;');
EXECUTE IMMEDIATE 'alter trigger ' || cur.owner || '.'|| cur.trigger_name ||' disable';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('************* trigger non cancellata - '|| cur.trigger_name );
CONTINUE;
end;
END LOOP;
sys.DBMS_LOCK.sleep(20);
END;
/
prompt =================================
prompt End script disable trigger
prompt =================================
spool off
[oracle@server06 work]$ cat disable_FK.sql
set serveroutput on
set lines 200
spool disable_Constraints.log
prompt =================================
prompt Start script disable CONSTRAINTS
prompt =================================
BEGIN
FOR cur IN (
select b.owner owner, b.table_name table_name, b.constraint_name constraint_name
from dba_constraints a, dba_constraints b, dba_cons_columns c
where a.owner=b.r_owner
and b.owner=c.owner
and b.table_name=c.table_name
and b.constraint_name=c.constraint_name
and a.constraint_name=b.r_constraint_name
and a.owner='SAKAMO'
and b.constraint_type='R'
and a.CONSTRAINT_TYPE in ('P','U')
)
LOOP
begin
DBMS_OUTPUT.put_line ('alter table ' || cur.owner ||'.'|| cur.table_name || ' disable constraint ' || cur.constraint_name ||';');
EXECUTE IMMEDIATE 'alter table '||cur.owner||'.'||cur.table_name||' disable constraint '||cur.constraint_name;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('************* constraint non cancellata - '|| cur.sequence_name);
CONTINUE;
end;
END LOOP;
sys.DBMS_LOCK.sleep(20);
END;
/
prompt =================================
prompt End script disable CONSTRAINTS
prompt =================================
spool off
[oracle@server06 work]$ cat imp_schema.sql
spool imp_SAKAMO.log
prompt ============================
prompt Start IMP
prompt ============================
host impdp "'/ as sysdba'" directory=DATA_PUMP_DIR logfile=exp_SAKAMO.log schemas=SAKAMO network_link=DB_PROD02 CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=TRUNCATE flashback_time=systimestamp
prompt ============================
prompt End IMP
prompt ============================
spool off
/
[oracle@server06 work]$ cat enable_FK.sql
set serveroutput on
spool enable_Constraints.log
prompt ==================================
prompt Start script enable CONSTRAINTS
prompt ==================================
BEGIN
FOR cur IN (
select b.owner owner, b.table_name table_name, b.constraint_name constraint_name
from dba_constraints a, dba_constraints b, dba_cons_columns c
where a.owner=b.r_owner
and b.owner=c.owner
and b.table_name=c.table_name
and b.constraint_name=c.constraint_name
and a.constraint_name=b.r_constraint_name
and a.owner='SAKAMO'
and b.constraint_type='R'
and a.CONSTRAINT_TYPE in ('P','U')
)
LOOP
begin
DBMS_OUTPUT.put_line ('alter table ' || cur.owner ||'.'|| cur.table_name || ' enable constraint ' || cur.constraint_name ||';');
EXECUTE IMMEDIATE 'alter table ' || cur.owner ||'.'|| cur.table_name || ' enable constraint ' || cur.constraint_name;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('************* constraint non abilitata - '|| cur.constraint_name );
CONTINUE;
end;
END LOOP;
sys.DBMS_LOCK.sleep(10);
END;
/
prompt =================================
prompt End script enable CONSTRAINTS
prompt =================================
spool off
[oracle@server06 work]$ cat enable_trigger.sql
spool enable_trigger.log
prompt ============================
prompt Start script enable trigger
prompt ============================
BEGIN
FOR cur IN (
select owner, trigger_name
from sys.dba_triggers
where owner in ('SAKAMO')
)
LOOP
beign
DBMS_OUTPUT.put_line ('alter trigger ' || cur.owner || '.'|| cur.trigger_name ||' enable;');
EXECUTE IMMEDIATE 'alter trigger ' || cur.owner || '.'|| cur.trigger_name ||' enable';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('************* trigger non abilitata- '|| cur.trigger_name );
CONTINUE;
end;
END LOOP;
sys.DBMS_LOCK.sleep(10);
END;
/
prompt =================================
prompt End script disable trigger
prompt =================================
spool off
