Regularly back up databases using the oracle Task Plan

The previous tasks involving oracle regular backup were completed at the operating system level. The following uses oracle task scheduler to regularly perform oracle backup. For detailed steps, see:

1. the backup statement/oracle/bak/fullbk.txt is as follows:

[Oracle @ dest ~] $ Cat/oracle/bak/fullbk.txt

Run {

Delete noprompt obsolete; # delete a backup in the obsolete state without prompting. Check whether the backup is related to the backup retention policy)

Crosscheck backup; # Check whether the backup recorded in the rman database exists on the disk or physical device. If not, change the status of the backup record in the rman database to expired.

Delete noprompt expired backup; # delete all records marked as expired in the rman database without prompting

Crosscheck archivelog all; # verify all archive records in the rman database. If the archive does not exist on the physical device, update the archive records marked as expired in the rman database.

Delete noprompt expired archivelog all; # delete all the archive records marked as expired in the rman database without prompting

Backup incremental level = 0 database format'/backup/crm/full-% T-% U. bak '; # Make a full backup of a zero-level database

Backup archivelog all format'/backup/crm/arch-% T-% U. bak '; # Back Up all archives

Backup current controlfile format '/backup/crm/ctl-% T-% U. bak'; # back up the current control file

Backup spfile format '/backup/crm/spf-% T-% U. bak'; # backup parameter file

Delete noprompt archivelog all completed before 'sysdate-7'; # delete all archives created seven days ago without prompting

}

Note that the above script is only for testing. It cannot be completely restored if a part of the archive is lost from the last full backup to the current state of the database, or if the archive is damaged.

2. the backup script/oracle/bak/rman. sh is as follows:

[Oracle @ dest ~] $ Cat/oracle/bak/rman. sh

#! /Bin/bash

Export today = 'date + % Y % m % d % H % m'

Export ORACLE_HOME =/oracle/app/db1

Export ORACLE_SID = CRM

$ ORACLE_HOME/bin/rman target sys/DHHZDHHZ log =/oracle/bak/crm. log partition file =/oracle/bak/fullbk.txt

Mv/oracle/bak/crm. log "/oracle/bak/crm $ {today}. log"

This script allows rman to call the backup statement in the/oracle/bak/fullbk.txt text, record the current backup process in the log file, and change the generated log file name to the current date.

3. Create a program in oracle

Begin

Dbms_scheduler.create_program (

Program_name => 'oracle _ bk_program ',

Program_action => '/oracle/bak/rman. Sh ',

Program_type => 'executable ',

Enabled => true,

Comments => 'oracle. Sh ');

End;

/

Query the created program as follows:

Select program_name, program_type, program_action, number_of_arguments, enabled from user_scheduler_programs where program_name = 'oracle _ BK_PROGRAM ';

PROGRAM_NAME PROGRAM_TYPE PROGRAM_ACTION NUMBER_OF_ARGUMENTS ENABL

---------------------------------------------------------------------------------

ORACLE_BK_PROGRAM EXECUTABLE/oracle/bak/rman. sh 0 TRUE

Note: If you want to modify the program, the following statement can be used for job or shceduler:

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (

Name => '',

Attribute => '',

Value => '');

END;

/

As follows:

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (

Name => 'oracle _ bk_schedule ',

Attribute => 'Repeat _ interval ',

Value => 'freq = DAILY; BYHOUR = 15, 16, 17 ');

END;

/

4. Create a scheduler

Begin

Dbms_scheduler.create_schedule (

Schedule_name => 'oracle _ bk_schedule ',

Repeat_interval => 'freq = DAILY; BYHOUR = 15,16, 17 ',

Comments => 'backup start 3pm ');

End;

/

Check the created scheduler as follows:

SQL> select schedule_name, repeat_interval from user_scheduler_schedules where schedule_name = 'oracle _ bk_schedule ';

SCHEDULE_NAME REPEAT_INTERVAL

------------------------------------------------------------------------------------------

ORACLE_BK_SCHEDULE FREQ = DAILY; BYHOUR = 17

5. Create a job and reference the created program and scheduler in the job.

Note that After you create a job and enable it, the Scheduler automatautomatruns the job according to its schedule or when the specified event is detected

Begin

Dbms_scheduler.create_job (

Job_name => 'oracle _ bk ',

Program_name => 'oracle _ bk_program ',

Schedule_name => 'oracle _ bk_schedule ',

Enabled => true );

End;

/

Query the job we just created as follows:

SQL> select job_name, job_type, job_action, REPEAT_INTERVAL, enabled, state from user_scheduler_jobs where job_name = 'oracle _ BK ';

JOB_NAME JOB_TYPE JOB_ACTION REPEAT_INTERVAL ENABL STATE

-------------------------------------------------------------------------------

ORACLE_BK TRUE SCHEDULED

SQL>

6. query the running status of the created job as follows:

Select log_id, log_date, status, additional_info from user_scheduler_job_run_details where job_name = 'oracle _ BK ';

LOG_ID LOG_DATE STATUS ADDITIONAL_INFO

-------------------------------------------------------------------------------------

511 22-JAN-14 03.54.43.20.305 PM + SUCCEEDED

514 22-JAN-14 03.58.51.953108 PM + 08:00 SUCCEEDED

519 22-JAN-14 04.58.10.062466 PM + 08:00 SUCCEEDED

522 22-JAN-14 05.57.50.288474 PM + SUCCEEDED

7. query the generated backup log file records as follows:

[Oracle @ dest bak] $ ls-lt

Total 304

-Rw-r -- 1 oracle oinstall 9680 Jan 22 crm201401221756.log

-Rw-r -- 1 oracle oinstall 10595 Jan 22 crm201401221656.log

-Rw-r -- 1 oracle oinstall 13155 Jan 22 crm201401221556.log

-Rw-r -- 1 oracle oinstall 12681 Jan 22 crm201401221551.log

Note 1 before deleting program and schedle, delete the jobs that call program and schedule.

2. The rman. sh script was missing #! /Bin/bash and rman: the full path is not entered. As a result, the task plan cannot be executed and the script cannot be executed.

3. For more information about oracle scheduler, see the official documentation.

This article is from the "myblog" blog, please be sure to keep this source http://jiujian.blog.51cto.com/444665/1354083