Backup Oracle Physical Standby through DBMS_SCHEDULER job

Question

Friends! We have a lot of primary databases with their physical standby databases in Data Guard configurations on servers. Each primary database on single server and each physical standby on single server. In EM12c we've configured scheduler jobs for backup our primary databases. Unfortunately, when server is really busy, Agent suspends backup execution and we haven't backup according to out schedule.

So, we disabled our backup jobs from EM12c and want to perform backups on Physical standby using procedure DBMS_SCHEDULER.CREATE_JOB.

As Physical Standby is read only database and per-block copy of Primary Database, I have to create schedule job on Primary and it applied to standby.

So, the question is: Is it possible? And if, yes - how to realize this in script??

Something like this:

check database_role 
if role='PHYSICAL STANDBY' 
then execute backup script 
else nothing to do..

If it's not possible, which solution is the best for resolve this task? Is there a way to solve this problem without create cron task with single script on each server? Is it possible to use one global script from recovery catalog database?

Kris said, that I can't run scheduled jobs from physical standby database. So, I'll schedule my linux script with crontab. My linux script is:

#! /usr/bin/bash

LOG_PATH=/home/oracle/scripts/logs; export LOG_PATH
TASK_NAME=backup_database_inc0; export TASK_NAME
CUR_DATE=`date +%Y.%m.%d-%H:%M`; export CUR_DATE
LOGFILE=$LOG_PATH/$TASK_NAME.$CUR_DATE.log; export LOGFILE

    rman target / catalog rmancat/<pswd>@rmancat script 'backup_database' log $LOGFILE

    if [ $? -eq 0 ]
    then
      mail -s "$ORACLE_UNQNAME Backup Status: SUCCESS" [email protected]< $LOGFILE
      exit 0
    else
      mail -s "$ORACLE_UNQNAME Backup Status: FAILED" [email protected]< $LOGFILE
      exit 1

I don't want to create linux file on each host to call backup global script from my recovery catalog. Is it possible to configure centralized backups execution schedule on all hosts? Can i configure ssh from one host to all database hosts and execute my linux script for backup?

Thanks in advance for your answers.

Physical standby databases do not run any scheduler jobs. RMAN scripts are usually scheduled from the OS scheduler anyways... however, you can store and call global scripts from the recovery catalog... refer to the docs on the use of stored scripts: docs.oracle.com/database/121/BRADV/rcmcatdb.htm#BRADV89669 — Kris Johnston
Hi, Kris. Thanks for your reply. I understand that I can't run scheduled jobs from physical standby database. I'll schedule my linux script with crontab, and I have a new question..) I'll write it in question body.. — Sergey

Daniel Daniel · Accepted Answer · 2017-03-17T10:48:21

I highly recommend using Enterprise Manager to run backup jobs. EM integrates nicely with rman catalog and each instance so you can have setup just the rman execute global script command. Everything else is done by EM.

I have the job scheduled to run only on standbys through EM job scheduler without having to change anything during a switchover.

I just have to cascade the jobs. So I have one step checking if the target is standby and if that step is successful, then I am running a backup. If it's not, the next step doesn't run.

In this way, the monitoring is also integrated with the global database monitoring. You don't have to setup error catch inside a shell script at OS level.

Backup Oracle Physical Standby through DBMS_SCHEDULER job

1 Answers