how to setup oracle maintenance job

If already provided a feasible solution here
https://www.experts-exchange.com/questions/29200352/Rman-Backup-script.html

TLDR:

I'd put the RMAN scripts into the database as you can now run them within the scheduler as type 'backup script'.
I have 2 of them:
1. Full DB backup (every Sunday at 0:30 am):

backup as COMPRESSED BACKUPSET tag '%U' database;
backup as COMPRESSED BACKUPSET tag '%U' archivelog all not backed up delete all input;
backup as COMPRESSED BACKUPSET tag '%U' current controlfile;
delete noprompt obsolete device type disk;

Select allOpen in new window

2. Archive Logs (every 10 minutes):

backup device type disk tag '%U' archivelog all not backed up delete all input;

Select allOpen in new window

%U

hi what will it be AFTER the command execute ?
how can I specify the target backup path ?

Look at this screenshot from our RMAN config:
https://www.experts-exchange.com/questions/29200352/Rman-Backup-script.html#a43193393                                   

It's this part where you configure the backup destination:
"CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ...."

And yes, wen know that this is not best practice considerung backup strategies ;-)
So, we have weekly fully database backups plus the archived logs; this enables us to restore to the most recent SCN in case of failure...

but full + archive log should already be the best as archive log should finish very fast and not lock the DB for long ?

Why should there be any "locking"?! Could you elaborate more on this? Maybe I don't understand exactly what you mean...

when backing up a DB, data pages is locked for backup until it complete? so this is the locking I meant.

one thing, for the RMAN command above, as we have primary server and DG standby server, I have to run it on primary server, right? 

Taken from https://docs.oracle.com/database/121/SBYDB/rman.htm#SBYDB4856

Oracle's Maximum Availability Architecture (MAA) best practices recommend that backups be taken at both the primary and the standby databases to reduce MTTR, in case of double outages and to avoid introducing new site practices upon switchover and failover.

but in DG, the same DB exists on both server right ? it means backup twice  /

Yes

Scripts
https://marketplace.microfocus.com/itom/content/rpa-database-maintenance 

so this script compatible with oracle 19.3 on Windows server 2019 ?

I'd put the RMAN scripts into the database as you can now run them within the scheduler as type 'backup script'.

As we are using Windows, we just need to setup via task scheduler?

so this script compatible with oracle 19.3 on Windows server 2019 ?

I don't know...You have to check for yourself, I cannot download it. See the release notes for any spec or such...

As we are using Windows, we just need to setup via task scheduler?                

Not necessarily ;-) You may also create appropriate scheduler jobs within the database itself, samples shown here:
https://support.oracle.com/knowledge/Oracle%20Database%20Products/2102623_1.html
https://oracle-base.com/articles/12c/scheduler-enhancements-12cr1#backup-script

https://oracle-base.com/articles/12c/scheduler-enhancements-12cr1#backup-script 

this is on linux/unix right ? we are on window server.

or this :

CONN test/test@pdb1 
-- Create a job with an RMAN script defined in-line, -- including an explicit connect. SET SERVEROUTPUT ON DECLARE  l_job_name VARCHAR2(30);  l_script   VARCHAR2(32767); BEGIN  l_job_name := DBMS_SCHEDULER.generate_job_name;  DBMS_OUTPUT.put_line('JOB_NAME=' || l_job_name);   -- Define the backup script.  l_script := 'connect target / run {
  backup archivelog all delete all input; }';   DBMS_SCHEDULER.create_job(    job_name        => l_job_name,    job_type        => 'BACKUP_SCRIPT',    job_action      => l_script,    credential_name => 'oracle_ol6_121',    enabled         => TRUE  ); END; /
JOB_NAME=JOB$_338 
PL/SQL procedure successfully completed.

Select allOpen in new window

already run inside sqlplus session?

I found an example of the RMAN script:

run
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
backup database format '/RMAN/bandbbckup/TST8/Current/%d_%U_%T_bkp';
backup archivelog all delete input format '/RMAN/bandbbckup/TST8/Current/%d_%U_%T.arcbkp';
backup current controlfile format '/RMAN/bandbbckup/TST8/Current/control_%d_%U_%T.ctl';
}

Select allOpen in new window

As far as I know, if I want to delete archive log which is backed up I do:

RMAN> backup archivelog all delete input;

If we want to take backup to specific drive, we can do

run {
allocate channel t1 type disk;
backup format '/recover/oracle/DB_%d_%t_%s' database;
 backup archivelog all delete input format '/RMAN/bandbbckup/TST8/Current/%d_%U_%T.arcbkp';
backup format '/recover/oracle/arc_%d_%t_%s' archivelog until time 'sysdate-14' delete input;
}

what is channel for ? just like disk path ?

and this one:

 backup format '/recover/oracle/arc_%d_%t_%s' archivelog until time 'sysdate-14' delete input;  

is the backup archive log command in RMAN with the action of deleting any old archive log backup file older than 14 days and this one:

/recover/oracle/ 

backup archivelog all delete input format '/RMAN/bandbbckup/TST8/Current/%d_%U_%T.arcbkp'; 

 delete all old backed up archive log, is that right?
 

so this script

CONN test/test@pdb1 
-- Create a job with an RMAN script defined in-line, -- including an explicit connect. SET SERVEROUTPUT ON DECLARE  l_job_name VARCHAR2(30);  l_script   VARCHAR2(32767); BEGIN  l_job_name := DBMS_SCHEDULER.generate_job_name;  DBMS_OUTPUT.put_line('JOB_NAME=' || l_job_name);   -- Define the backup script.  l_script := 'connect target / run {
  backup archivelog all delete all input; }';   DBMS_SCHEDULER.create_job(    job_name        => l_job_name,    job_type        => 'BACKUP_SCRIPT',    job_action      => l_script,    credential_name => 'oracle_ol6_121',    enabled         => TRUE  ); END; /
JOB_NAME=JOB$_338 
PL/SQL procedure successfully completed. 
SQL> 
-- Check the status of the job. 
COLUMN job_name FORMAT A20 SELECT job_name, status, error# FROM   user_scheduler_job_run_details ORDER BY job_name; 
JOB_NAME             STATUS                             ERROR# -------------------- ------------------------------ ---------- JOB$_338             SUCCEEDED                               0 
SQL>

Select allOpen in new window

can save as a .bat files in Windows and let windows scheduler to run the .bat ?

delete all input

"all delete input" right ?

run {
allocate channel t1 type disk;
backup format '/recover/oracle/DB_%d_%t_%s' database;
 backup archivelog all delete input format '/RMAN/bandbbckup/TST8/Current/%d_%U_%T.arcbkp';
backup format '/recover/oracle/arc_%d_%t_%s' archivelog until time 'sysdate-14' delete input;
}

so the script here already can do the job
1) backup format '/recover/oracle/DB_%d_%t_%s' database;

     RMAN full backup to the path I want, which is:  /recover/oracle/ ,  and also delete any old full backup file more than 14 days old found in this path : /recover/oracle/ ?

2)  backup archivelog all delete input format '/RMAN/bandbbckup/TST8/Current/%d_%U_%T.arcbkp';

   delete all archive log backed up/ processed
3)  backup format '/recover/oracle/arc_%d_%t_%s' archivelog until time 'sysdate-14' delete input;

    backup archive log and retention period is 14 days so for any archive log backed up and saved in /recover/oracle/  with files extension .arcbkp will be removed ?

can save as a .bat files in Windows and let windows scheduler to run the .bat ?

NO!!! You have to read properly!! I'm talking about putting the RMAN stuff WITHIN the database itself as dbms_scheduler jobs (which is possible since 12.1)!!!

Regarding the commands and proper syntaxes, please refer to the official docs, there's all you need to know:
https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/index.html

If you're after RMAN scripting via shell (batch), there are tons of samples out there, like:
https://blogs.oracle.com/oraclemagazine/scripting-oracle-rman-commands
https://oracle-base.com/articles/misc/oracle-shell-scripting
http://oracle-help.com/scripts/schedule-rman-backup-in-windows/

from here:

Document 2102623.1 (oracle.com) 
something like this:

vi sample_bkp.rman
connect target /
RUN
{
backup database plus archivelog;
crosscheck backup;
crosscheck archivelog all;
};

Select allOpen in new window

tells something! it vi a script! which create the file  xxxx.rman, then inside that rman file it run the RMAN command:

connect target /

Select allOpen in new window

then

RUN
{
backup database plus archivelog;
crosscheck backup;
crosscheck archivelog all;
};

Select allOpen in new window

already telling something like what I am saying.

'm talking about putting the RMAN stuff WITHIN the database itself as dbms_scheduler jobs 

it seems I should login using SQL developers and create a schedule job contain statement like that, am I right ?

I read this :

ALLOCATE CHANNEL (oracle.com) 

one statement draw my attention:

  Multiple Channels
You can allocate up to 255 channels; each channel can read up to 64 files in parallel. You can control the degree of parallelism within a job by the number of channels that you allocate. Allocating multiple channels simultaneously allows a single job to read or write multiple backup sets or disk copies in parallel, with each channel operating on a separate backup set or copy.

so from my script above:

allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;

so it just make the SAME task :

backup format '/recover/oracle/DB_%d_%t_%s' database;
 backup archivelog all delete input format '/RMAN/bandbbckup/TST8/Current/%d_%U_%T.arcbkp';
backup format '/recover/oracle/arc_%d_%t_%s' archivelog until time 'sysdate-14' delete input;

much faster by creating/allocating 4 x disk data channel from the DB RMAN backup from and all disk channel server AT THE SAME time so the backup jobs can run in parallel ?

hi,

today we tried this one and this works partially;

Run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
backup database format 'DBBak%d_%U_%T_bkp';
backup archivelog all not backed up 1 times format 'DBBak%d_%U_%T.arcbkp';
backup current controlfile format 'DBBakcontrol_%d_%U_%T.ctl';
}

Select allOpen in new window

but this one is not working as RMAN do not like the path:

D:\Backuptest

Select allOpen in new window

Run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
backup database format 'D:\Backuptest\DBBak%d_%U_%T_bkp';
backup archivelog all not backed up 1 times format 'D:\Backuptest\DBBak%d_%U_%T.arcbkp';
backup current controlfile format 'D:\Backuptest\DBBakcontrol_%d_%U_%T.ctl';
report obsolete;
delete noprompt obsolete;
}

Select allOpen in new window

as by default it point to the <oracle home> so the path can become \\<oracle home>\D:\Backuptest\DBBak%d_%U_%T_bkp, which surely doens't work. the path is not going to exist!

1) how can we change to absolute path, to the one we want, like a networked path.
2)if we specify network path here, but the network path need different username and password to access, how can we do it ?
3) we set "CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;' in RMAN already and we know the command:

delete noprompt obsolete;

Select allOpen in new window

will delete any backup files older than 14 days.
but how can we Delete old archive logs which is backed up?

hi,

tks.

how about the absolute path :

backup database format 'D:\Backuptest\DBBak%d_%U_%T_bkp';

which is not working ! it still refer to ANY path INSIDE the oracle home, anyway to fix it ?

The same goes for network shares, like "\\some_server\some_share\wherever2go\sub_folder"...

if I type this network share, that path RMAN still START to look for it under oracle home, this is part of the trouble make my second script not work as expected!

configure device type disk parallelism 4;

inside the RMAN run script? like

run {
configure device type disk parallelism 4;

..
.}

?

Regarding the archivelogs, there are tons of resources out there giving you various samples of its usage for deletion:

so my script do not delete any backed archive log at all?

I see this one:

DELETE ARCHIVELOG ALL

it is used to deletes archived logs according to the log deletion policy set by the CONFIGURE ARCHIVELOG DELETION POLICY command. If the archived logs are in a flash recovery area, then they are automatically deleted when more open disk space is required. Therefore, you only need to use this command if you explicitly want to delete logs each day.  

so we have to do this first  in RMAN:

CONFIGURE ARCHIVELOG DELETION POLICY ;

then in RMAN we do:
run {
.
.
DELETE ARCHIVELOG ALL }

it will automatically delete backed up archive log OR none backed up archive log ?

Your RMAN script with the absolute path should work??!! Is the RMAN script executed on the DB server and does the path/folder exist?
Can you post the RMAN setup ("show all;")?

it will automatically delete backed up archive log OR none backed up archive log ? 

You should really take your time and read the official docs properly, like
https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/DELETE.html#GUID-FB4EAC69-4978-42F7-8B09-77C6736188B3

inside the RMAN run script? like

Yes.

Is the RMAN script executed on the DB server and does the path/folder exist?

yes and yes ! seems can't absolute path ! it will point back to home anyway! so the backup path we want MUST BE inside home path! this the one not perfect for us.

this is the script we are using and working for now:

Run { 
allocate channel ch1 device type disk;   
allocate channel ch2 device type disk; 
allocate channel ch3 device type disk; 
allocate channel ch4 device type disk; 
backup database format 'DBBak%d_%U_%T_bkp';    
backup archivelog all not backed up 1 times format 'DBBak%d_%U_%T.arcbkp';   
backup current controlfile format 'DBBakcontrol_%d_%U_%T.ctl';  
report obsolete; 
delete noprompt obsolete;    
delete archivelog all backed up 1 times to DISK;  
} 

Select allOpen in new window

You should really take your time and read the official docs properly, like 

sure!

and we found out that we need to run this one to change backup path:

configure channel device type disk format '<path>\<file name format>.bkp'; 

Select allOpen in new window

That's one of the reasons I asked about your RMAN config!
Btw: the allocation of 4 channels with the very same destination is irrelevant and won't lead to gain in performance!
Allocating multiple channels helps you to mirror the backup to different devices during backup processing.

Btw: the allocation of 4 channels with the very same destination is irrelevant and won't lead to gain in performance!
Allocating multiple channels helps you to mirror the backup to different devices during backup processing.

so just one is ok if you say in this wa y?

and we found out that we need to run this one to change backup path:

configure channel device type disk format '<path>\<file name format>.bkp'; 

Select allOpen in new window

and I am right on this ?

Btw: the allocation of 4 channels with the very same destination is irrelevant and won't lead to gain in performance!

 but we found that 4 channel will separate backup into pieces instead of a large file, is that right ?

but we found that 4 channel will separate backup into pieces instead of a large file, is that right ?

Use the MAXPIECESIZE option for configuring this!

but we found that 4 channel will separate backup into pieces instead of a large file, is that right ?

taken from: https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/ALLOCATE-CHANNEL.html#GUID-9320BFF7-0728-4B3D-85B9-2184557ECDCE

Example 2-7 Distributing a Backup Across Multiple Disks
When backing up to disk, you can spread the backup across several disk drives. Allocate one DEVICE TYPE DISK channel for each disk drive and specify the format string so that the output files are on different disks.

RUN
{
  ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/disk1/%U';
  ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/disk2/%U';
  BACKUP DATABASE PLUS ARCHIVELOG;
}

Select allOpen in new window

But this has nothing to do with parallelism!

run {
configure device type disk parallelism 4;

..
.}

so our script can be:

Run { 
allocate channel ch1 device type disk;   
allocate channel ch2 device type disk; 
allocate channel ch3 device type disk; 
allocate channel ch4 device type disk; 
configure device type disk parallelism 
backup database format 'DBBak%d_%U_%T_bkp';     
backup archivelog all not backed up 1 times format 'DBBak%d_%U_%T.arcbkp';   
backup current controlfile format 'DBBakcontrol_%d_%U_%T.ctl';   
report obsolete; 
delete noprompt obsolete;    

} 

Select allOpen in new window

?

Sure, whereas allocating 4 identical channels doesn't make any sense in this context!
So, it would be rather

Run { 
configure device type disk parallelism
allocate channel ch1 device type disk;  
backup database format 'DBBak%d_%U_%T_bkp';     
backup archivelog all not backed up 1 times format 'DBBak%d_%U_%T.arcbkp';  
backup current controlfile format 'DBBakcontrol_%d_%U_%T.ctl';  
report obsolete; 
delete noprompt obsolete;    

} 

Select allOpen in new window

tks.

configure device type disk parallelism

has to get a ; at the end ?

so it is :

Run { 
configure device type disk parallelism;
allocate channel ch1 device type disk;  
backup database format 'DBBak%d_%U_%T_bkp';     
backup archivelog all not backed up 1 times format 'DBBak%d_%U_%T.arcbkp';  
backup current controlfile format 'DBBakcontrol_%d_%U_%T.ctl';  
report obsolete; 
delete noprompt obsolete;    

} 

Select allOpen in new window

?

Yes, that was just a typo ;-)

tks