Scheduled RMAN Backup without password in Script in DBMS Scheduler

 RMAN Backup can be scheduled in DBMS SCHEDULER without using password script at Operating system but you have to create credential for Operating system user for that.

Following are the steps for scheduling RMAN script without saving password in SCRIPT at Operating system:

1. Create an OS Level credential from SYS USER.

Credential package is used Operating sytem username and password. So, verify OS user before creating OS credentials in Oracle database.

--For Normal user
exec dbms_scheduler.create_credential( credential_name => 'Windowuser', username => 'EORACLE', password => 'Password1');
--For Domain user
exec dbms_scheduler.create_credential( credential_name => 'Windowuser', username => 'EORACLE', password => 'Password1', windows_domain => 'ORCL' );

2. You will create this job by using WINDOW CREDENTIAL and using SYS or backup user password in the script without saving it on Operating system.

You need to modified the script as you need:

1. l_job_name := ‘Backup_database’; — Define the job name you can edit as you want

2. l_script := ‘spool log to C:scriptrman.txt –Define the location of logs file

3. connect target sys/Password1 — Use your username and password

4. run { } — in run block use your rman command as you want

5. credential_name => ‘Windowuser’ — in this enter your Credential which you created in step 1.

After Editing above parameter in following script you can run it and it will create a DBMS_SCHEDULER JOB

SET SERVEROUTPUT ON
DECLARE
l_job_name VARCHAR2(30);
l_script VARCHAR2(32767);
BEGIN
l_job_name := 'Backup_database';
DBMS_OUTPUT.put_line('JOB_NAME=' || l_job_name);
-- Define the backup script.
l_script := 'spool log to C:\script\rman.txt
connect target sys/Password1
run {
crosscheck backup;
crosscheck archivelog all;
backup incremental level 0 database;
Backup archivelog all;
backup current controlfile;
delete noprompt obsolete;
delete archivelog until time ''sysdate-90'';
}';

DBMS_SCHEDULER.create_job(
job_name => l_job_name,
job_type => 'BACKUP_SCRIPT',
job_action => l_script,
credential_name => 'Windowuser',
enabled => TRUE
);
END;
/

3. Check the job is created in DBMS SCHEDULER

select job_name,start_date,last_start_date,Next_run_Date,failure_count,run_count,job_action from dba_scheduler_jobs where job_name='BACKUP_DATABASE';

4. Manually run the job for checking its free from error.

EXEC dbms_scheduler.run_job(job_name => 'BACKUP_DATABASE');
--If any error, you need to correct by dropping and re-creating it by executing step 2
-- For Dropping the job
exec dbms_scheduler.drop_job(job_name => 'BACKUP_DATABASE');

5. Schedule the created job in DBMS SCHEDULER.

--Its run Daily by 10 PM
BEGIN
dbms_scheduler.create_job(
job_name => 'Backup_database'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN dbms_scheduler.run_job(job_name => ''BACKUP_DATABASE'') END;'
,start_date => SYSDATE +1/24/59
,repeat_interval => 'FREQ=DAILY; BYHOUR=22;'
,enabled => TRUE
,comments => 'Backup job');
END;
/

6. Check the DBMS scheduler job status.

column job_name format a20
select job_name, status, error# from dba_scheduler_job_run_details where job_name='BACKUP_DATABASE';

select job_name,start_date,last_start_date,Next_run_Date,failure_count,run_count,job_action from dba_scheduler_jobs where job_name='BACKUP_DATABASE';

7. You can check the credential for the database created in step 1.

COLUMN credential_name FORMAT A25
COLUMN username FORMAT A20
COLUMN windows_domain FORMAT A20
SELECT credential_name,username, windows_domain
FROM all_credentials ORDER BY credential_name;

Directly schedule the job in same package with same script as in STEP2

It is the same script which having additional parameter for scheduling also but not tested one. Upper part is tested one. We make it separate so that you can understand and run it manually. you can directly use this in one script as follows:

-Example for direct run the RMAN commands with DBMS_SCHEDULER:
SET SERVEROUTPUT ON
DECLARE
l_job_name VARCHAR2(30);
l_script VARCHAR2(32767);
BEGIN
-- Generator is used to generate job name automatic
-- l_job_name := DBMS_SCHEDULER.generate_job_name;
l_job_name := 'Backup_database';
DBMS_OUTPUT.put_line('JOB_NAME=' || l_job_name);
-- Define the backup script.
l_script := 'spool log to C:\script\rman.txt
connect target sys/Password1
run {
crosscheck backup;
crosscheck archivelog all;
backup incremental level 0 database;
Backup archivelog all;
backup current controlfile;
}';

DBMS_SCHEDULER.create_job(
job_name => l_job_name,
job_type => 'BACKUP_SCRIPT',
job_action => l_script,
credential_name => '',
enabled => TRUE,
start_date => SYSDATE +1/24/59,
repeat_interval => 'FREQ=DAILY; BYHOUR=22; BYMINUTE=00;'
);
END;
/

Scheduling the RMAN command with DBMS Scheduler placing script in Operating system

--Create the credential as in step 1
exec dbms_scheduler.create_credential( credential_name => 'Windowuser', username => 'Eoracle', password => 'Password1');

-- Prepare an rman script at Harddisk location of your OS
notepad rmanbackup.rman
connect target /
RUN
{
backup database plus archivelog;
crosscheck backup;
crosscheck archivelog all;
};

-- Scheduled the script by calling this from OS location
BEGIN
dbms_scheduler.create_job(
job_name => 'Backup_database'
,job_type => 'backup_script'
,job_action => 'C:\script\rmanbackup.rman'
,credential_name => 'Windowuser'
,start_date => SYSDATE +1/24/59
,repeat_interval => 'FREQ=DAILY; BYHOUR=22; BYMINUTE=00;'
,enabled => TRUE
,comments => 'Full DB BACKUP'
);
END;
/