OraFAQ Forum: Backup & Recovery » RMAN on ORA11gR2 - CONFIGURE RETENTION POLICY TO REDUNDANCY 1

Home » RDBMS Server » Backup & Recovery » RMAN on ORA11gR2 - CONFIGURE RETENTION POLICY TO REDUNDANCY 1

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

RMAN on ORA11gR2 - CONFIGURE RETENTION POLICY TO REDUNDANCY 1 [message #667273]

Tue, 19 December 2017 03:18

Rumak18
Messages: 20
Registered: April 2009
Location: Germany

Junior Member

Hello,
i've got a rman backup configuration for my db :

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'd:\BACKUP\MY_CONTROLFILE_BACKUP_%F.ORA';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\BACKUP\MY_BACKUP_%U' MAXPIECESIZE 2 G;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\BACKUP\MY_SNAPCONTROL.ORA';

My backup script on monday looks like this:
backup AS COMPRESSED BACKUPSET incremental level 1 cumulative database;
delete obsolete;
delete archivelog all;
sql'purge dba_recyclebin';
crosscheck backup;
crosscheck archivelog all;

My backup script on tuesday to friday looks like this:
backup AS COMPRESSED BACKUPSET incremental level 0 cumulative database;
delete obsolete;
delete archivelog all;
sql'purge dba_recyclebin';
crosscheck backup;
crosscheck archivelog all;

Unfortunately , rman does not delete "obsolete" backups on monday after . This happens often in last time and i don't know why.

Report message to a moderator

Re: RMAN on ORA11gR2 - CONFIGURE RETENTION POLICY TO REDUNDANCY 1 [message #667282 is a reply to message #667273]

Tue, 19 December 2017 06:31

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Why are you taking a level 0 (full) backup 6 days a week, and a level 1 only one day a week? Most people do just the opposite.
Why are you not backing up your archivelogs?
Why are you deleting your archivelogs without backing them up?
In a script, your 'delete obsolete' is expecting, and taking input from stdin, so when it prompts for 'yes' or 'no', it gets 'delete archivelog', etc.

Report message to a moderator

Re: RMAN on ORA11gR2 - CONFIGURE RETENTION POLICY TO REDUNDANCY 1 [message #667285 is a reply to message #667282]

Tue, 19 December 2017 08:18

Rumak18
Messages: 20
Registered: April 2009
Location: Germany

Junior Member

Hi,
i've mixed up the days. Sorry. Of Course we're doing the full backup on monday and the "Level 1" backups on other days.
We're also backing up our archivelogs, but this is done in another script which runs every two hours.

Report message to a moderator

Re: RMAN on ORA11gR2 - CONFIGURE RETENTION POLICY TO REDUNDANCY 1 [message #667290 is a reply to message #667285]

Tue, 19 December 2017 17:51

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Rumak18 wrote on Tue, 19 December 2017 08:18

Hi,
i've mixed up the days. Sorry. Of Course we're doing the full backup on monday and the "Level 1" backups on other days.
We're also backing up our archivelogs, but this is done in another script which runs every two hours.

WHat else have you "mixed up"?
The script(s) as shown will not run. I've already pointed out that your DELETE OBSOLETE is going to prompt for input from stdin. And I will now demonstrate that your DELETE ARCHIVELOG ALL will also prompt for input -- AND delete an archive log that has not been backed up.

SQL> select name from v$archived_log;

no rows selected

SQL> alter system switch logfile;

System altered.

SQL> select name from v$archived_log;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/flash_recovery_area/OKLACITY/archivelog/2017_12_19/o1_mf_1_68_f3
m93xc7_.arc


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

oracle:oklacity$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Dec 19 17:45:21 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: OKLACITY (DBID=3427384023)

RMAN> delete archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 device type=DISK
List of Archived Log Copies for database with db_unique_name OKLACITY
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
1       1    68      A 16-DEC-17
        Name: /u01/app/oracle/flash_recovery_area/OKLACITY/archivelog/2017_12_19/o1_mf_1_68_f3m93xc7_.arc


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/flash_recovery_area/OKLACITY/archivelog/2017_12_19/o1_mf_1_68_f3m93xc7_.arc RECID=1 STAMP=963164701
Deleted 1 objects


RMAN> exit


Recovery Manager complete.

oracle:oklacity$ ls -l /u01/app/oracle/flash_recovery_area/OKLACITY/archivelog/2017_12_19/o1_mf_1_68_f3m93xc7_.arc
ls: cannot access /u01/app/oracle/flash_recovery_area/OKLACITY/archivelog/2017_12_19/o1_mf_1_68_f3m93xc7_.arc: No such file or directory

oracle:oklacity$

Report message to a moderator

Re: RMAN on ORA11gR2 - CONFIGURE RETENTION POLICY TO REDUNDANCY 1 [message #667297 is a reply to message #667290]

Wed, 20 December 2017 02:27

Rumak18
Messages: 20
Registered: April 2009
Location: Germany

Junior Member

Hi,
Quote:

WHat else have you "mixed up"?

I hope not that much (-:

Quote:

The script(s) as shown will not run

-> They are invoked by a cmd of course.

As i already wrote...the archive logs are backup with another script that runs every two hours. Here it is:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\myserver-def\BACKUP\ORA_CONTROLFILE_BACKUP_%F.ORA';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '\\myserver-def\BACKUP\ORA_SNAPCONTROL.ORA';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT = '\\myserver-def\BACKUP\ORA_BACKUP_%U' MAXPIECESIZE = 2G;

RUN
{
backup archivelog all delete all input;
sql'purge dba_recyclebin';
}

Report message to a moderator

Re: RMAN on ORA11gR2 - CONFIGURE RETENTION POLICY TO REDUNDANCY 1 [message #667309 is a reply to message #667297]

Wed, 20 December 2017 06:40

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Rumak18 wrote on Wed, 20 December 2017 02:27

Hi,
Quote:
WHat else have you "mixed up"?
I hope not that much (-:

Quote:
The script(s) as shown will not run
-> They are invoked by a cmd of course.

And how does that change the fact that the commands are prompting for input at run time? Do you really execute them manually so that you can respond to those prompts?
Quote:

As i already wrote...the archive logs are backup with another script that runs every two hours. Here it is:

And I have already demonstrated that any archivelogs that happen to get created after the last archivelog backup will get deleted - WITHOUT BEING BACKED UP! - by your database backup script.

And why do you have your rman backup scripts purging the recycle bin?

Report message to a moderator

Re: RMAN on ORA11gR2 - CONFIGURE RETENTION POLICY TO REDUNDANCY 1 [message #667345 is a reply to message #667273]

Thu, 21 December 2017 14:24

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Ok, I've put together an extensive demo.
First, I configured a db with the same rman config as you:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:\demo_backup\MY_CONTROLFILE_BACKUP_%F.ORA';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'E:\demo_backup\MY_BACKUP_%U' MAXPIECESIZE 2 G;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\demo_backup\MY_SNAPCONTROL.ORA';

Next a series of test scripts. Since this is Windows the scripting is more convoluted than if it were *nix
A script to generate some db activity and switch logfiles, thus simulating normal business activity. A sql script and a cmd script to call it:

switch_logfile.sql

conn / as sysdba
insert into scott.demo values (sysdate);
commit;
alter system switch logfile;
exit

switch_logfile.cmd

set ORACLE_BASE=E:\app\oracle
set ORACLE_HOME=E:\app\oracle\product\11.2.0\dbhome_1
set ORACLE_SID=edstest
set PATH=E:\app\oracle\product\11.2.0\dbhome_1\bin;E:\app\oracle\product\11.2.0\dbhome_1\bin;E:\app\oracle\product\12.1.0\dbhome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\
for /f "tokens=2 delims==" %%I in ('wmic os get localdatetime /format:list') do set datetime=%%I
set cdate=%datetime:~0,8%
set ctime=%datetime:~8,6%
set logfile=switch_logfile_%cdate%_%ctime%.log
sqlplus / as sysdba @switch_logfile  > %logfile%
rem pause

A script to report the archivelog files:
rpt_archivelogs.sql

set echo on feedback on verify on trimsp on lines 256 pages 60
col name for a55
conn / as sysdba
archive log list
select name,
       status,
       sequence#,
       first_change# 
from v$archived_log 
where name is not null
order by name;
exit

And cmd file to call it
rpt_archivelogs.cmd

set ORACLE_BASE=E:\app\oracle
set ORACLE_HOME=E:\app\oracle\product\11.2.0\dbhome_1
set ORACLE_SID=edstest
set PATH=E:\app\oracle\product\11.2.0\dbhome_1\bin;E:\app\oracle\product\11.2.0\dbhome_1\bin;E:\app\oracle\product\12.1.0\dbhome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\
for /f "tokens=2 delims==" %%I in ('wmic os get localdatetime /format:list') do set datetime=%%I
set cdate=%datetime:~0,8%
set ctime=%datetime:~8,6%
set logfile=rpt_archlogs_%cdate%_%ctime%.log
sqlplus / as sysdba @rpt_archivelogs  > %logfile%
rem pause

Scripts to implement your backup jobs (I've added a LIST BACKUP as the last step of each)
backup_db_0.rman

backup AS COMPRESSED BACKUPSET incremental level 0 cumulative database;
delete obsolete;
delete archivelog all;
sql'purge dba_recyclebin';
crosscheck backup;
crosscheck archivelog all;
list backup;

backup_db_0.cmd

set ORACLE_BASE=E:\app\oracle
set ORACLE_HOME=E:\app\oracle\product\11.2.0\dbhome_1
set ORACLE_SID=edstest
set PATH=E:\app\oracle\product\11.2.0\dbhome_1\bin;E:\app\oracle\product\11.2.0\dbhome_1\bin;E:\app\oracle\product\12.1.0\dbhome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\
for /f "tokens=2 delims==" %%I in ('wmic os get localdatetime /format:list') do set datetime=%%I
set cdate=%datetime:~0,8%
set ctime=%datetime:~8,6%
set logfile=backup_db_0_%cdate%_%ctime%.log
rman target / @backup_db_0.rman > %logfile%
rem pause

backup_db_1.rman

backup AS COMPRESSED BACKUPSET incremental level 1 cumulative database;
delete obsolete;
delete archivelog all;
sql'purge dba_recyclebin';
crosscheck backup;
crosscheck archivelog all;
list backup;

backup_db_1.cmd

set ORACLE_BASE=E:\app\oracle
set ORACLE_HOME=E:\app\oracle\product\11.2.0\dbhome_1
set ORACLE_SID=edstest
set PATH=E:\app\oracle\product\11.2.0\dbhome_1\bin;E:\app\oracle\product\11.2.0\dbhome_1\bin;E:\app\oracle\product\12.1.0\dbhome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\
for /f "tokens=2 delims==" %%I in ('wmic os get localdatetime /format:list') do set datetime=%%I
set cdate=%datetime:~0,8%
set ctime=%datetime:~8,6%
set logfile=backup_db_1_%cdate%_%ctime%.log
rman target / @backup_db_1.rman > %logfile%
rem pause

backup_archlogs.rman

run
{
backup archivelog all delete all input;
sql'purge dba_recyclebin';
}
list backup;

backup_archlogs.cmd

set ORACLE_BASE=E:\app\oracle
set ORACLE_HOME=E:\app\oracle\product\11.2.0\dbhome_1
set ORACLE_SID=edstest
set PATH=E:\app\oracle\product\11.2.0\dbhome_1\bin;E:\app\oracle\product\11.2.0\dbhome_1\bin;E:\app\oracle\product\12.1.0\dbhome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\
for /f "tokens=2 delims==" %%I in ('wmic os get localdatetime /format:list') do set datetime=%%I
set cdate=%datetime:~0,8%
set ctime=%datetime:~8,6%
set logfile=backup_archlogs_%cdate%_%ctime%.log
rman target / @backup_archlogs.rman > %logfile%
rem pause

And a script to run the test:
doit.cmd

set NLS_DATE_FORMAT=DD-Mon-yyyy hh24:mi:ss
set ORACLE_BASE=E:\app\oracle
set ORACLE_HOME=E:\app\oracle\product\11.2.0\dbhome_1
set ORACLE_SID=edstest
set PATH=E:\app\oracle\product\11.2.0\dbhome_1\bin;E:\app\oracle\product\11.2.0\dbhome_1\bin;E:\app\oracle\product\12.1.0\dbhome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\
cd /d g:\scripts\demo
pause
call backup_db_0.cmd
pause
call rpt_archivelogs
pause
call switch_logfile
pause
call rpt_archivelogs
pause
call backup_archlogs
pause
call rpt_archivelogs
pause
call switch_logfile
pause
call rpt_archivelogs
pause
call backup_db_1.cmd
pause
call rpt_archivelogs
pause
call switch_logfile
pause
call rpt_archivelogs
pause
call backup_archlogs
pause
call rpt_archivelogs
pause
call switch_logfile
pause
call rpt_archivelogs
pause
call backup_db_0.cmd

And the results . . .
The first step, the first level 0 backup ran as expected and listed the two backupsets created at that time. I show just the LIST BACKUP. As you can see, there are no archivelogs yet backed up.

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
73      Incr 0  269.45M    DISK        00:00:26     21-Dec-2017 11:37:02
        BP Key: 73   Status: AVAILABLE  Compressed: YES  Tag: TAG20171221T113636
        Piece Name: E:\DEMO_BACKUP\MY_BACKUP_29SMM1M4_1_1
  List of Datafiles in backup set 73
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1580651    21-Dec-2017 11:36:36 X:\ORADATA\EDSTEST\SYSTEM01.DBF
  2    0  Incr 1580651    21-Dec-2017 11:36:36 X:\ORADATA\EDSTEST\SYSAUX01.DBF
  3    0  Incr 1580651    21-Dec-2017 11:36:36 X:\ORADATA\EDSTEST\UNDOTBS01.DBF
  4    0  Incr 1580651    21-Dec-2017 11:36:36 Y:\ORADATA\EDSTEST\USERS01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
74      Full    9.95M      DISK        00:00:00     21-Dec-2017 11:37:12
        BP Key: 74   Status: AVAILABLE  Compressed: NO  Tag: TAG20171221T113712
        Piece Name: E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-23.ORA
  SPFILE Included: Modification time: 21-Dec-2017 10:15:22
  SPFILE db_unique_name: EDSTEST
  Control File Included: Ckp SCN: 1580671      Ckp time: 21-Dec-2017 11:37:12

Recovery Manager complete.

The next step reported any existing archivelogs. As yet there are none, and that is expected at this point:

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       g:\archivelogs\edstest
Oldest online log sequence     61
Next log sequence to archive   63
Current log sequence	       63
SQL> select name,
  2  	    status,
  3  	    sequence#,
  4  	    first_change#
  5  from v$archived_log
  6  where name is not null
  7  order by name;

no rows selected

The next step was to run 'switch_logfile' to generate activity and force a log switch, thus creating an archivelog. Following that, we again report archivelogs. We have one log, at sequence 63.

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       g:\archivelogs\edstest
Oldest online log sequence     62
Next log sequence to archive   64
Current log sequence	       64
SQL> select name,
  2  	    status,
  3  	    sequence#,
  4  	    first_change#
  5  from v$archived_log
  6  where name is not null
  7  order by name;

NAME							S  SEQUENCE# FIRST_CHANGE#
------------------------------------------------------- - ---------- -------------
G:\ARCHIVELOGS\EDSTEST\ARC0000000063_0959677649.0001	A	  63	   1580554

1 row selected.

Next, we run the backup of archivelogs. The RMAN activity shows it backing up archlog seq 63, and 64. Backing up archivlogs also performs a log switch, which is how we got seq. 64. In the LIST BACKUP, notice backupset 75, containing the backup of archivelog seq 63 and 64.

Starting backup at 21-Dec-2017 11:37:33
<snip unnecesary detail>
input archived log thread=1 sequence=63 RECID=63 STAMP=963315442
input archived log thread=1 sequence=64 RECID=64 STAMP=963315453
<snip unnecesary detail>
channel ORA_DISK_1: deleting archived log(s)
archived log file name=G:\ARCHIVELOGS\EDSTEST\ARC0000000063_0959677649.0001 RECID=63 STAMP=963315442
archived log file name=G:\ARCHIVELOGS\EDSTEST\ARC0000000064_0959677649.0001 RECID=64 STAMP=963315453
Finished backup at 21-Dec-2017 11:37:35
<snip unnecesary detail>

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
73      Incr 0  269.45M    DISK        00:00:26     21-Dec-2017 11:37:02
        BP Key: 73   Status: AVAILABLE  Compressed: YES  Tag: TAG20171221T113636
        Piece Name: E:\DEMO_BACKUP\MY_BACKUP_29SMM1M4_1_1
  List of Datafiles in backup set 73
<snip unnecesary detail>

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
74      Full    9.95M      DISK        00:00:00     21-Dec-2017 11:37:12
        BP Key: 74   Status: AVAILABLE  Compressed: NO  Tag: TAG20171221T113712
        Piece Name: E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-23.ORA
  SPFILE Included: Modification time: 21-Dec-2017 10:15:22
  SPFILE db_unique_name: EDSTEST
  Control File Included: Ckp SCN: 1580671      Ckp time: 21-Dec-2017 11:37:12

BS Key  Size       Device Type Elapsed Time Completion Time     
------- ---------- ----------- ------------ --------------------
75      5.00K      DISK        00:00:00     21-Dec-2017 11:37:34
        BP Key: 75   Status: AVAILABLE  Compressed: NO  Tag: TAG20171221T113734
        Piece Name: E:\DEMO_BACKUP\MY_BACKUP_2BSMM1NU_1_1

  List of Archived Logs in backup set 75
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    63      1580554    21-Dec-2017 11:34:50 1580748    21-Dec-2017 11:37:22
  1    64      1580748    21-Dec-2017 11:37:22 1580761    21-Dec-2017 11:37:33

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
76      Full    9.95M      DISK        00:00:01     21-Dec-2017 11:37:36
        BP Key: 76   Status: AVAILABLE  Compressed: NO  Tag: TAG20171221T113735
        Piece Name: E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-24.ORA
  SPFILE Included: Modification time: 21-Dec-2017 10:15:22
  SPFILE db_unique_name: EDSTEST
  Control File Included: Ckp SCN: 1580775      Ckp time: 21-Dec-2017 11:37:35

Recovery Manager complete.

Next another archivelog report. As expected, there are none because we just finished backing them up with DELETE ALL INPUT.

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       g:\archivelogs\edstest
Oldest online log sequence     63
Next log sequence to archive   65
Current log sequence	       65
SQL> select name,
  2  	    status,
  3  	    sequence#,
  4  	    first_change#
  5  from v$archived_log
  6  where name is not null
  7  order by name;

no rows selected

Next we generate some more activity, log switch, and another archlog report. TAKE SPECIAL NOTE that we have now generated log seq 65.

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       g:\archivelogs\edstest
Oldest online log sequence     64
Next log sequence to archive   66
Current log sequence	       66
SQL> select name,
  2  	    status,
  3  	    sequence#,
  4  	    first_change#
  5  from v$archived_log
  6  where name is not null
  7  order by name;

NAME							S  SEQUENCE# FIRST_CHANGE#
------------------------------------------------------- - ---------- -------------
G:\ARCHIVELOGS\EDSTEST\ARC0000000065_0959677649.0001	A	  65	   1580761

1 row selected.

Next, your level 1 database backup. Remember, this does NOT backup the archlogs, but DOES delete them ...

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 21 11:37:56 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: EDSTEST (DBID=892894544)

RMAN> backup AS COMPRESSED BACKUPSET incremental level 1 cumulative database;
2> delete obsolete;
3> delete archivelog all;
4> sql'purge dba_recyclebin';
5> crosscheck backup;
6> crosscheck archivelog all;
7> list backup;
8> 
<<snip unnecessary detail>>
input datafile file number=00001 name=X:\ORADATA\EDSTEST\SYSTEM01.DBF
input datafile file number=00002 name=X:\ORADATA\EDSTEST\SYSAUX01.DBF
input datafile file number=00003 name=X:\ORADATA\EDSTEST\UNDOTBS01.DBF
input datafile file number=00004 name=Y:\ORADATA\EDSTEST\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 21-Dec-2017 11:37:57
channel ORA_DISK_1: finished piece 1 at 21-Dec-2017 11:38:22
<<snip unnecessary detail>>

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
deleted archived log
archived log file name=G:\ARCHIVELOGS\EDSTEST\ARC0000000065_0959677649.0001 RECID=65 STAMP=963315467
Deleted 1 objects


<<snip unnecessary detail>>
List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
73      Incr 0  269.45M    DISK        00:00:26     21-Dec-2017 11:37:02
        BP Key: 73   Status: AVAILABLE  Compressed: YES  Tag: TAG20171221T113636
        Piece Name: E:\DEMO_BACKUP\MY_BACKUP_29SMM1M4_1_1
<<snip unnecessary detail>>
BS Key  Size       Device Type Elapsed Time Completion Time     
------- ---------- ----------- ------------ --------------------
75      5.00K      DISK        00:00:00     21-Dec-2017 11:37:34
        BP Key: 75   Status: AVAILABLE  Compressed: NO  Tag: TAG20171221T113734
        Piece Name: E:\DEMO_BACKUP\MY_BACKUP_2BSMM1NU_1_1

  List of Archived Logs in backup set 75
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    63      1580554    21-Dec-2017 11:34:50 1580748    21-Dec-2017 11:37:22
  1    64      1580748    21-Dec-2017 11:37:22 1580761    21-Dec-2017 11:37:33

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
77      Incr 1  128.00K    DISK        00:00:17     21-Dec-2017 11:38:14
        BP Key: 77   Status: AVAILABLE  Compressed: YES  Tag: TAG20171221T113757
        Piece Name: E:\DEMO_BACKUP\MY_BACKUP_2DSMM1OL_1_1
  List of Datafiles in backup set 77
<<snip unnecessary detail>>
BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
78      Full    9.95M      DISK        00:00:01     21-Dec-2017 11:38:23
        BP Key: 78   Status: AVAILABLE  Compressed: NO  Tag: TAG20171221T113822
        Piece Name: E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-25.ORA
<<snip unnecessary detail>>
Recovery Manager complete.

And immediately following another archlog report. Where is seq 65?

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       g:\archivelogs\edstest
Oldest online log sequence     64
Next log sequence to archive   66
Current log sequence	       66
SQL> select name,
  2  	    status,
  3  	    sequence#,
  4  	    first_change#
  5  from v$archived_log
  6  where name is not null
  7  order by name;

no rows selected

More activity, another log switch, and report. Now we have log seq 66

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       g:\archivelogs\edstest
Oldest online log sequence     65
Next log sequence to archive   67
Current log sequence	       67
SQL> select name,
  2  	    status,
  3  	    sequence#,
  4  	    first_change#
  5  from v$archived_log
  6  where name is not null
  7  order by name;

NAME							S  SEQUENCE# FIRST_CHANGE#
------------------------------------------------------- - ---------- -------------
G:\ARCHIVELOGS\EDSTEST\ARC0000000066_0959677649.0001	A	  66	   1580807

Next, your archivelog backup. Important to note it is now backing up seq 66 and 67, but still no sign of 65.

RMAN> run
2> {
3> backup archivelog all delete all input;
4> sql'purge dba_recyclebin';
5> }
6> list backup;
7> 
Starting backup at 21-Dec-2017 11:38:44
current log archived
<<snip unnecessary detail>>
input archived log thread=1 sequence=66 RECID=66 STAMP=963315516
input archived log thread=1 sequence=67 RECID=67 STAMP=963315524
channel ORA_DISK_1: starting piece 1 at 21-Dec-2017 11:38:45
channel ORA_DISK_1: finished piece 1 at 21-Dec-2017 11:38:46
piece handle=E:\DEMO_BACKUP\MY_BACKUP_2FSMM1Q5_1_1 tag=TAG20171221T113845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=G:\ARCHIVELOGS\EDSTEST\ARC0000000066_0959677649.0001 RECID=66 STAMP=963315516
archived log file name=G:\ARCHIVELOGS\EDSTEST\ARC0000000067_0959677649.0001 RECID=67 STAMP=963315524
Finished backup at 21-Dec-2017 11:38:46
<<snip unnecessary detail>>
List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
73      Incr 0  269.45M    DISK        00:00:26     21-Dec-2017 11:37:02
        BP Key: 73   Status: AVAILABLE  Compressed: YES  Tag: TAG20171221T113636
        Piece Name: E:\DEMO_BACKUP\MY_BACKUP_29SMM1M4_1_1
  List of Datafiles in backup set 73
<<snip unnecessary detail>>

BS Key  Size       Device Type Elapsed Time Completion Time     
------- ---------- ----------- ------------ --------------------
75      5.00K      DISK        00:00:00     21-Dec-2017 11:37:34
        BP Key: 75   Status: AVAILABLE  Compressed: NO  Tag: TAG20171221T113734
        Piece Name: E:\DEMO_BACKUP\MY_BACKUP_2BSMM1NU_1_1

  List of Archived Logs in backup set 75
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    63      1580554    21-Dec-2017 11:34:50 1580748    21-Dec-2017 11:37:22
  1    64      1580748    21-Dec-2017 11:37:22 1580761    21-Dec-2017 11:37:33

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
77      Incr 1  128.00K    DISK        00:00:17     21-Dec-2017 11:38:14
        BP Key: 77   Status: AVAILABLE  Compressed: YES  Tag: TAG20171221T113757
        Piece Name: E:\DEMO_BACKUP\MY_BACKUP_2DSMM1OL_1_1
  List of Datafiles in backup set 77
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
<<snip unnecessary detail>>

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
78      Full    9.95M      DISK        00:00:01     21-Dec-2017 11:38:23
        BP Key: 78   Status: AVAILABLE  Compressed: NO  Tag: TAG20171221T113822
        Piece Name: E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-25.ORA
  SPFILE Included: Modification time: 21-Dec-2017 10:15:22
  SPFILE db_unique_name: EDSTEST
  Control File Included: Ckp SCN: 1580837      Ckp time: 21-Dec-2017 11:38:22

BS Key  Size       Device Type Elapsed Time Completion Time     
------- ---------- ----------- ------------ --------------------
79      4.50K      DISK        00:00:00     21-Dec-2017 11:38:45
        BP Key: 79   Status: AVAILABLE  Compressed: NO  Tag: TAG20171221T113845
        Piece Name: E:\DEMO_BACKUP\MY_BACKUP_2FSMM1Q5_1_1

  List of Archived Logs in backup set 79
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    66      1580807    21-Dec-2017 11:37:47 1580916    21-Dec-2017 11:38:36
  1    67      1580916    21-Dec-2017 11:38:36 1580929    21-Dec-2017 11:38:44

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
80      Full    9.95M      DISK        00:00:00     21-Dec-2017 11:38:46
        BP Key: 80   Status: AVAILABLE  Compressed: NO  Tag: TAG20171221T113846
        Piece Name: E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-26.ORA
  SPFILE Included: Modification time: 21-Dec-2017 10:15:22
  SPFILE db_unique_name: EDSTEST
  Control File Included: Ckp SCN: 1580943      Ckp time: 21-Dec-2017 11:38:46

Recovery Manager complete.

So at this point you have deleted log seq 65 with no backup.You are now unrecoverable past sequence 64.
========================================================
Now, back to your original question, "rman does not delete "obsolete" backups on monday after". Let's move on past another cycle of generating activity and archlogs and move to the next level 0 backup:

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 21 11:39:08 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: EDSTEST (DBID=892894544)

RMAN> backup AS COMPRESSED BACKUPSET incremental level 0 cumulative database;
2> delete obsolete;
3> delete archivelog all;
4> sql'purge dba_recyclebin';
5> crosscheck backup;
6> crosscheck archivelog all;
7> list backup;
8> 
Starting backup at 21-Dec-2017 11:39:08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=X:\ORADATA\EDSTEST\SYSTEM01.DBF
input datafile file number=00002 name=X:\ORADATA\EDSTEST\SYSAUX01.DBF
input datafile file number=00003 name=X:\ORADATA\EDSTEST\UNDOTBS01.DBF
input datafile file number=00004 name=Y:\ORADATA\EDSTEST\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 21-Dec-2017 11:39:09
channel ORA_DISK_1: finished piece 1 at 21-Dec-2017 11:39:44
piece handle=E:\DEMO_BACKUP\MY_BACKUP_2HSMM1QT_1_1 tag=TAG20171221T113909 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 21-Dec-2017 11:39:44

Starting Control File and SPFILE Autobackup at 21-Dec-2017 11:39:44
piece handle=E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-27.ORA comment=NONE
Finished Control File and SPFILE Autobackup at 21-Dec-2017 11:39:45

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           73     21-Dec-2017 11:37:02
  Backup Piece       73     21-Dec-2017 11:37:02 E:\DEMO_BACKUP\MY_BACKUP_29SMM1M4_1_1
Backup Set           75     21-Dec-2017 11:37:34
  Backup Piece       75     21-Dec-2017 11:37:34 E:\DEMO_BACKUP\MY_BACKUP_2BSMM1NU_1_1
Backup Set           77     21-Dec-2017 11:38:14
  Backup Piece       77     21-Dec-2017 11:38:14 E:\DEMO_BACKUP\MY_BACKUP_2DSMM1OL_1_1
Backup Set           78     21-Dec-2017 11:38:23
  Backup Piece       78     21-Dec-2017 11:38:23 E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-25.ORA
Backup Set           79     21-Dec-2017 11:38:45
  Backup Piece       79     21-Dec-2017 11:38:45 E:\DEMO_BACKUP\MY_BACKUP_2FSMM1Q5_1_1
Archive Log          68     21-Dec-2017 11:38:56 G:\ARCHIVELOGS\EDSTEST\ARC0000000068_0959677649.0001
Backup Set           80     21-Dec-2017 11:38:46
  Backup Piece       80     21-Dec-2017 11:38:46 E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-26.ORA
backup piece handle=E:\DEMO_BACKUP\MY_BACKUP_29SMM1M4_1_1 RECID=73 STAMP=963315396
deleted backup piece
backup piece handle=E:\DEMO_BACKUP\MY_BACKUP_2BSMM1NU_1_1 RECID=75 STAMP=963315454
deleted backup piece
backup piece handle=E:\DEMO_BACKUP\MY_BACKUP_2DSMM1OL_1_1 RECID=77 STAMP=963315477
deleted backup piece
backup piece handle=E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-25.ORA RECID=78 STAMP=963315503
deleted backup piece
backup piece handle=E:\DEMO_BACKUP\MY_BACKUP_2FSMM1Q5_1_1 RECID=79 STAMP=963315525
deleted archived log
archived log file name=G:\ARCHIVELOGS\EDSTEST\ARC0000000068_0959677649.0001 RECID=68 STAMP=963315536
deleted backup piece
backup piece handle=E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-26.ORA RECID=80 STAMP=963315526
Deleted 7 objects

Note above that it IS deleting the previous level 0 backup, BS key 73 and 74, as well as all subsequent backups that are made obsolete by the just completed backup.

Continuing with the same report . . ..

deleted backup piece


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
specification does not match any archived log in the repository

sql statement: purge dba_recyclebin

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=E:\DEMO_BACKUP\MY_BACKUP_2HSMM1QT_1_1 RECID=81 STAMP=963315549
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-27.ORA RECID=82 STAMP=963315585
Crosschecked 2 objects


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
specification does not match any archived log in the repository


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
81      Incr 0  269.48M    DISK        00:00:26     21-Dec-2017 11:39:35
        BP Key: 81   Status: AVAILABLE  Compressed: YES  Tag: TAG20171221T113909
        Piece Name: E:\DEMO_BACKUP\MY_BACKUP_2HSMM1QT_1_1
  List of Datafiles in backup set 81
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1580996    21-Dec-2017 11:39:09 X:\ORADATA\EDSTEST\SYSTEM01.DBF
  2    0  Incr 1580996    21-Dec-2017 11:39:09 X:\ORADATA\EDSTEST\SYSAUX01.DBF
  3    0  Incr 1580996    21-Dec-2017 11:39:09 X:\ORADATA\EDSTEST\UNDOTBS01.DBF
  4    0  Incr 1580996    21-Dec-2017 11:39:09 Y:\ORADATA\EDSTEST\USERS01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
82      Full    9.95M      DISK        00:00:01     21-Dec-2017 11:39:45
        BP Key: 82   Status: AVAILABLE  Compressed: NO  Tag: TAG20171221T113944
        Piece Name: E:\DEMO_BACKUP\MY_CONTROLFILE_BACKUP_C-892894544-20171221-27.ORA
  SPFILE Included: Modification time: 21-Dec-2017 10:15:22
  SPFILE db_unique_name: EDSTEST
  Control File Included: Ckp SCN: 1581014      Ckp time: 21-Dec-2017 11:39:44

Recovery Manager complete.

Notice the only backup now listed is the one we just completed.

Report message to a moderator

Previous Topic:	ORA-19504 - "failed to create file"
Next Topic:	transportable database windows to linux

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 23 00:42:40 CST 2025