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

Source: Internet
Author: User

 

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;

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.