Dblink backs up the production database data to the Test Database (proc, jobs) every day)

 

Principle:

(1) Use database link to establish a connection between the two databases.

(2) create a stored procedure: delete all data tables, and insert the data table content of the production database to the test database. (Commit)

(3) Use Jobs to regularly execute stored procedures.

1. Create a dblink in the test database:

Createdatabaselink to_shengchan (dblink name)

Connectto unique identifiedby visu

Using '(DESCRIPTION =

(ADDRESS_LIST =

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

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.138.10.198) (PORT = 1521) -- ip address of the production database cluster

)

(CONNECT_DATA =

(SERVICE_NAME = yyjcrac) -- Instance name

)

)'

 

2. Create a stored procedure:

Createorreplaceprocedure proc_shengchan_bakis

Begin

 

------------ The following non-real-time table data -----------------

 

Deletefromz_visu_data_chk;

Commit;

Insertinto z_visu_data_chk

Select * from z_visu_data_chk @ to_shengchan t;

Commit;

Deletefrom z_visu_data_dish;

Commit;

Insertinto z_pai_data_dish

Select * from z_visu_data_dish @ to_shengchan t;

Commit;

Deletefromz_pai_data_fin;

Commit;

Insertinto z_pai_data_fin

Select * from z_visu_data_fin @ to_shengchan t;

Commit;

Deletefromz_visu_data_hr;

Commit;

Insertinto z_pai_data_hr

Select * from z_visu_data_hr @ to_shengchan t;

Commit;

End;

When creating a stored procedure, you must execute F8 To Make It compiled successfully.

 

3. Execute the Stored Procedure

3.1 manual execution

Call proc_shengchan_bak ()

Or exec proc_shengchan_bak

3.2 run Jobs (create job)

 

DECLARE

X number;

BEGIN

SYS. DBMS_JOB.SUBMIT

(Job => X

, What => 'testproc ;'

, Next_date => to_date ('02-07-2008 01:00:00 ', 'dd/mm/yyyyhh24: mi: ss ')

, Interval => 'trunc (sysdate) + 1 + 9/(24) '-- run at every morning

, No_parse => FALSE

);

SYS. DBMS_OUTPUT.PUT_LINE ('job Number is: '| to_char (x ));

COMMIT;

END;