export ORACLE_SID=prod
rman target /
rman target / nocatalog
backup database;
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/fra
db_recovery_file_dest_size big integer 10G
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
rman target / nocatalog
show all;
configure controlfile autobackup on;
show all;
configure retention policy to redundancy 2;
configure backup optimization off;
FRA = Flash Recovery Area or Fast Recovery Area
show parameter recover;
alter system set db_recovery_file_dest_size=10G scope=both;
mkdir -p /u01/fra
alter system set db_recovery_file_dest='/u01/fra' scope=both;
BACKUP DATABASE;
BACKUP DATABASE PLUS ARCHIVELOG;
BACKUP ARCHIVELOG ALL;
BACKUP ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
To delete archive log using RMAN
RMAN> DELETE ARCHIVELOG ALL;
RMAN> DELETE ARCHIVELOG <archivelog name>;
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-2';
To delete archive log using SQL
SQL> ALTER SYSTEM ARCHIVE LOG ALL DELETE;
SQL> ALTER SYSTEM ARCHIVE LOG <archivelog name> DELETE;
SQL> DELETE FROM v$archived_log WHERE completion_time < SYSDATE-2;
col name for a40
select recid, name, thread#, sequence#, dest_id, creator, registrar,
standby_dest, status, completion_time
from v$archived_log
order by completion_time;
SQL> DELETE FROM v$archived_log WHERE completion_time < SYSDATE-2;
select sid,recid,status,start_time,end_time from v$rman_status
order by start_time;
declare
f_count number ;
begin
select count(*) into f_count from v$rman_status where STATUS like 'RUNNING%';
if f_count>0 then
dbms_output.put_line('RMAN backup job is running, archive log files cannot be deleted');
return;
end if;
f_count:=0;
for c1 in (
select RECID,STAMP,NAME,THREAD#,SEQUENCE#,
RESETLOGS_CHANGE#,FIRST_CHANGE#,BLOCK_SIZE
from v$archived_log
where STANDBY_DEST='NO' and deleted='NO)
loop
begin
dbms_backup_restore.deleteArchivedLog(c1.RECID,c1.STAMP,c1.NAME,c1.THREAD#,c1.SEQUENCE#,c1.RESETLOGS_CHANGE#,c1.FIRST_CHANGE#,c1.BLOCK_SIZE);
dbms_output.put_line(c1.name||' is deleted.');
f_count:=f_count+1;
exception
when others then
null;
end;
end loop;
if f_count=0 then
dbms_output.put_line('No archive log files deleted.');
end if;
end;
/
The PL/SQL block can be enclosed in database scheduler job definition or Linux/Unix shell script running as crontab job. Here is a simple bash shell example,
#!/bin/bash
# Redirect all STDOUT & STDERR messages to log file
exec &>>/u01/log/backup.log
# Set Oracle environment
export ORAENV_ASK=NO
export ORACLE_SID=<Database SID>
. oraenv 1>/dev/null
# Run PL/SQL block in SQLPLUS
sqlplus -S /nolog <<EOF | grep -v 'PL/SQL procedure successfully completed.'
connect / as sysdba
set serveroutput on
declare
f_count number ;
begin
/* If there is RMAN job running, f_count=0 -> no job running*/
select count(*) into f_count from v\$rman_status where STATUS like 'RUNNING%';
if f_count>0 then
dbms_output.put_line('RMAN job is running, archivelog log files cannot be deleted while RMAN job is running');
return;
end if;
f_count:=0;
for c1 in (
select RECID,STAMP,NAME,THREAD#,SEQUENCE#,RESETLOGS_CHANGE#,FIRST_CHANGE#,BLOCK_SIZE
from v\$archived_log
where STANDBY_DEST='NO' and deleted='NO'
)
loop
begin
dbms_backup_restore.deleteArchivedLog(c1.RECID,c1.STAMP,c1.NAME,c1.THREAD#,c1.SEQUENCE#,c1.RESETLOGS_CHANGE#,c1.FIRST_CHANGE#,c1.BLOCK_SIZE);
dbms_output.put_line(c1.name||' is deleted.');
f_count:=f_count+1;
exception
when others then
null;
end;
end loop;
if f_count=0 then
dbms_output.put_line('No archivelog files deleted.');
end if;
end;
/
EOF
BACKUP DEVICE TYPE sbt
ARCHIVELOG ALL
DELETE ALL INPUT;RMAN Snapshot Control File
The snapshot control file is a temporary file that is created at a certain location specified over the operating system. This file is used by RMAN to synchronize the information with the current control file. This file, except for synchronizing the information with the current control file, does not do anything else and hence, is not included in the recovery catalog. It does not contain any information about the current backup that you have taken, though it does know about the last backups that were done and are not marked yet as obsolete. So this file can be used to recover a scenario where you have lost everything and also were not using the catalog. You can use the last backup information recorded in this file and can recover the database with that backup.
The location of this file depends on the platform and also uses a default naming convention as well. Interestingly, irrespective of the flash recovery area being configured, the snapshot control file does not use it and is set for the location, normally to the ORACLE_HOME/database or ORACLE_HOME/dbs location with the name sncf<sid>.ora.
You can see the current name and location of the file by the show command.
RMAN> show snapshot controlfile name;
RMAN configuration parameters are:
configure snapshot controlfile name to 'E:\ORACLE\product\10.2.0\db_1\database\sncforcl.ora'; # default
In this case, it is set to the default location. You can change it using the configure command.
