--Create the credential as in step 1
We make it separate so that you can understand and run it manually. run { exec dbms_scheduler.create_credential( credential_name => 'Windowuser', username => 'Eoracle', password => 'Password1'); -- Prepare an rman script at Harddisk location of your OS SELECT credential_name,username, windows_domain run {
column job_name format a20
backup current controlfile; following command at the RMAN prompt to update RMAN metadata: It is always better to use RMAN to delete logs than to use an operating system utility. backup incremental level 0 database; ,job_action => 'C:\script\rmanbackup.rman'
enabled => TRUE, Credential package is used Operating sytem username and password. /.
l_job_name VARCHAR2(30); ); you can directly use this in one script as follows: -name "*.trc" -depth -mtime +60 -exec rm {} \; find /path/to/files* -mtime +60 -exec rm {} \; find /path/to/files* -type f -mtime +60 -print0 | xargs -r rm -rf, ls -lrt *.arc|awk '{print "compress "$9}' >ARC_LIST.txt, Oracle DBA Interview Questions and Answers - Architecture. }; -- Scheduled the script by calling this from OS location
,comments => 'Full DB BACKUP' BACKUP BACKUPSET COMPLETED BEFORE 'SYSDATE-7' DELETE INPUT; BACKUP BACKUPSET ALL FILESPERSET 10 DELETE INPUT; Delete BACKUPSET backed up to tape/disk (PROD Environment), -----------------------------------------. RUN
,job_type => 'backup_script' job_type => 'BACKUP_SCRIPT', ,start_date => SYSDATE +1/24/59 start_date => SYSDATE +1/24/59,
crosscheck archivelog all; 5. credential_name => Windowuser in this enter your Credential which you created in step 1. backup incremental level 0 database; DECLARE
4. run { } in run block use your rman command as you want
It is the same script which having additional parameter for scheduling also but not tested one. You can check the credential for the database created in step 1. -- Define the backup script.
Check the job is created in DBMS SCHEDULER l_script VARCHAR2(32767); }'; DBMS_SCHEDULER.create_job( job_action => l_script, 1. l_job_name := Backup_database; Define the job name you can edit as you want Manually run the job for checking its free from error. Scheduling the RMAN command with DBMS Scheduler placing script in Operating system find .
EXEC dbms_scheduler.run_job(job_name => 'BACKUP_DATABASE');
exec dbms_scheduler.create_credential( credential_name => 'Windowuser', username => 'EORACLE', password => 'Password1', windows_domain => 'ORCL' ); 2. job_name => l_job_name, BACKUP DEVICE TYPE sbt ARCHIVELOG ALL DELETE ALL INPUT; ls -lrt | grep 'Apr 24' | awk -F' ' '{print$9}', rm -rf `ls -lrt | grep 'Apr 24' | awk -F' ' '{print$9}'`, ls -lrt | grep 'Apr' | awk -F' ' '{print$9}'. ); crosscheck backup; END; --Example for direct run the RMAN commands with DBMS_SCHEDULER: credential_name => 'Windowuser',
Following are the steps for scheduling RMAN script without saving password in SCRIPT at Operating system: 1. After Editing above parameter in following script you can run it and it will create a DBMS_SCHEDULER JOB. exec dbms_scheduler.drop_job(job_name => 'BACKUP_DATABASE'); 5. COLUMN credential_name FORMAT A25
,job_action => 'BEGIN dbms_scheduler.run_job(job_name => ''BACKUP_DATABASE'') END;'
/. 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. Create an OS Level credential from SYS USER. 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. dbms_scheduler.create_job(
DELETE BACKUPSET COMPLETED BEFORE 'SYSDATE-4'; Delete archivelogs backed up to tape/disk (PROD Environment). l_script VARCHAR2(32767); BEGIN
--Its run Daily by 10 PM
BACKUP ARCHIVELOG ALL FILESPERSET 10 DELETE INPUT; Backup archivelogs to disk (specified location,if there is no space to take bakup at archive default location), --------------------------------------------------------------------------------------------------------------.
DECLARE ,enabled => TRUE backup archivelog from sequence 100 until sequence 110 thread 1 delete input; backup archivelog from logseq 100 until logseq 110 thread 1 delete input; DELETE OBSOLETE RECOVERY WINDOW OF 7 DAYS; list archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-3'; delete noprompt archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-3'; If you know that the logs are unavailable because you deleted them by using an operating system utility, then run the. --For Domain user Check the DBMS scheduler job status.
,job_type => 'PLSQL_BLOCK'
select * from V$FLASH_RECOVERY_AREA_USAGE; (see what kind of files are available in the Flash Recovery Area). 3. --For Normal user ls -lrt *.trc|grep 'Apr'|xargs rm -rf {}\; rm -rf `ls -lrt | grep 'Apr' | awk -F' ' '{print$9}'`.
{ job_action => l_script,
,enabled => TRUE l_script := 'spool log to C:\script\rman.txt crosscheck backup; backup database plus archivelog; }'; DBMS_SCHEDULER.create_job(
l_job_name VARCHAR2(30); 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.
DELETE NOPROMPT BACKUPSET COMPLETED BEFORE 'SYSDATE-14' DEVICE TYPE DISK; +FRA filled with Bacupsets (NON-PROD Environment). -- Generator is used to generate job name automatic The easiest method to remove.
END;
Backup & Delete - Archivelogs and RMAN backups.
DELETE archivelog all BACKED UP 1 TIMES TO DEVICE TYPE DISK; delete archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7'; backup archivelog [all] [until time 'sysdate']| [sequence between 100 to 110 thread 1] as filesperset 5 delete.
,repeat_interval => 'FREQ=DAILY; BYHOUR=22;'
connect target / BEGIN Schedule the created job in DBMS SCHEDULER. unwanted logs is to specify the DELETE INPUT option when backing up archived logs. DBMS_OUTPUT.put_line('JOB_NAME=' || l_job_name); -- For Dropping the job ,repeat_interval => 'FREQ=DAILY; BYHOUR=22; BYMINUTE=00;'
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.
Upper part is tested one. ,comments => 'Backup job'); BEGIN delete noprompt obsolete; backup current controlfile; -- l_job_name := DBMS_SCHEDULER.generate_job_name; COLUMN windows_domain FORMAT A20 Backup archivelog all;
,start_date => SYSDATE +1/24/59
notepad rmanbackup.rman
l_job_name := 'Backup_database'; 6.
to_char(space_limit, '999,999,999,999') as space_limit. exec dbms_scheduler.create_credential( credential_name => 'Windowuser', username => 'EORACLE', password => 'Password1'); -name "*.trc" -depth -mtime +60 -exec ls -l {} \; find .
job_name => 'Backup_database' DBMS_OUTPUT.put_line('JOB_NAME=' || l_job_name); DELETE archivelog UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DEVICE TYPE DISK; DELETE archivelog UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DEVICE TYPE SBT_TAPE; Delete archivelogs backed up to tape/disk (NON-PROD Environment). END; crosscheck archivelog all; 2. l_script := spool log to C:\script\rman.txt Define the location of logs file /, For any work, queries and help. job_type => 'BACKUP_SCRIPT', SET SERVEROUTPUT ON repeat_interval => 'FREQ=DAILY; BYHOUR=22; BYMINUTE=00;' to_char(space_limit - space_used + space_reclaimable, round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full, NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES, ---- ----------- ---------- ----------------- ---------------, if SPACE_USED is full, then increase the db_recovery_file_dest_size. Backup and Delete - Archivelogs and RMAN backups: ===================================================, NAME TYPE VALUE, ---- ---- -----, db_recovery_file_dest string +FLASHDG, db_recovery_file_dest_size big integer 150G.
delete archivelog until time ''sysdate-90''; BEGIN
connect target sys/Password1 credential_name => '',
crosscheck archivelog all;
l_job_name := 'Backup_database'; /. SQL>alter system set db_recovery_file_dest_size=200G scope=both; LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1'; ------List all archivelog backups for the past 24 hours. ,credential_name => 'Windowuser' l_script := 'spool log to C:\script\rman.txt connect target sys/Password1 list backup of archivelog all completed before 'sysdate -1'; list backup of archivelog all tag 'TAGXXX'; list archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7'; delete expired backupset device type disk; nohup rman target / cmdfile=archivebackup.cmd log=archivebackup_dbname_DDMONYY.log &. END; So, verify OS user before creating OS credentials in Oracle database. FROM all_credentials ORDER BY credential_name; Directly schedule the job in same package with same script as in STEP2
SET SERVEROUTPUT ON You need to modified the script as you need:
COLUMN username FORMAT A20 crosscheck backup; dbms_scheduler.create_job( enabled => TRUE BACKUP ARCHIVELOG ALL format '/location/arch_%d_%p_%s.rman' FILESPERSET 10 DELETE INPUT; Backup archivelogs to tape (PROD Environment), +FRA filled with Bacupsets (PROD Environment), Move older backupset to tape (to keep more recent backups on disk and older backups only on tape), -------------------------------------------------------------------------------------------------.
--If any error, you need to correct by dropping and re-creating it by executing step 2 job_name => 'Backup_database'
3. connect target sys/Password1 Use your username and password Backup archivelog all; );
job_name => l_job_name, -- Define the backup script.
Please contact us at contactus@smarttechways.com, Scheduled RMAN Backup without password in Script in DBMSScheduler, Run RMAN without saving password in script, Follow Smart way of Technology on WordPress.com, Create & grant permission to directory in Oracle, Check the Size of Oracle Database and PDB database, Exclude/Include option in EXPDP and IMPDP Datapump, Find the temp usage by sessions in Oracle, Check the Patch Applied to the Oracle Database, Check the Undo tablespace Usage in Oracle, Stop the EXPDP/IMPDP Datapump Job in Oracle, Check status, enable and disable the Audit in Oracle, Check and Change Default Tablespace for User in Oracle.