Home » RDBMS Server » Backup & Recovery » RMAN backup not backing up archivelog after database backup (Oracle 9.2.0.6.0 on Windows)
RMAN backup not backing up archivelog after database backup [message #439000] Wed, 13 January 2010 09:49 Go to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi,

I have 2 queries regarding rman backup and recovery

Please see a test case below after which have mentioned my queries :

After database Creation
are no archivelogs files at log_archive_dest=e:\archive


	conn a/a				conn sys / as sysdba			Result
14:27	create table t(n number,		alter system archivelog current;	ARC00003.001
	tm timestamp)
	insert into t values(1,systimestamp);
	commit;

14:29	insert into t values(2,systimestamp);	alter system archivelog current;	ARC00004.001
	commit;

14:32	insert into t values(3,systimestamp);						
	commit;

run{
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to 'e:\backup\db03ctl%F';
configure channel 1 device type disk format 'e:\backup\%d_%T_%s_%p.bak';
configure retention policy to redundancy 1;
backup database plus archivelog;
delete obsolete;
}


This command created ARC00005.001 backed it up and after backup created ARC00006.001 at log_archive_dest
This backup completed before 14:33

14:34	insert into t values(4,systimestamp);	alter system archivelog current;	ARC00007.001
	commit;

14:36	insert into t values(5,systimestamp);	alter system archivelog current;	ARC00008.001
	commit;

14:38	insert into t values(6,systimestamp);	alter system archivelog current;	ARC00009.001
	commit;




Questions
1) before backup there were 2 archivelog files
ARC00003.001, ARC00004.001
The 'backup database plus archivelog' switched redo created 'ARC00005.001' backed it up
backedup the database
again switched redo and created 'ARC00006.001'

however it did not backed up 'ARC00006.001' contrary to the dcoumentation : extract below

When you specify PLUS ARCHIVELOG, RMAN performs these steps:
1. Runs an ALTER SYSTEM ARCHIVE LOG CURRENT statement.
2. Runs the BACKUP ARCHIVELOG ALL command. Note that if backup optimization is enabled, then RMAN only backs up logs that have not yet been backed up.
3. Backs up the files specified in the BACKUP command.
4. Runs an ALTER SYSTEM ARCHIVE LOG CURRENT statement.
5. Backs up any remaining archived redo logs.

2) at 14:38:XX, after inserrting n=6 in table t, current redo was archived to create 'ARC00009.001' however while recovering
at 14:38:XX, the database expects 'ARC00010.001', What could be the reason? please see recovery session below
In fact immediately after archiving 'ARC00010.001' I shut down the database.

SQL> RECOVER automatic DATABASE  until time '2010-01-13:14:38:10' USING BACKUP CONTROLFILE;
ORA-00279: change 47057 generated at 01/13/2010 14:38:19 needed for thread 1
ORA-00289: suggestion : E:\ARCHIVE\ARC00010.001
ORA-00280: change 47057 for thread 1 is in sequence #10
ORA-00278: log file 'E:\ARCHIVE\ARC00010.001' no longer needed for this
recovery
ORA-00308: cannot open archived log 'E:\ARCHIVE\ARC00010.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> RECOVER automatic DATABASE  until time '2010-01-13:14:38:05' USING BACKUP CONTROLFILE;
ORA-00279: change 47057 generated at 01/13/2010 14:38:19 needed for thread 1
ORA-00289: suggestion : E:\ARCHIVE\ARC00010.001
ORA-00280: change 47057 for thread 1 is in sequence #10
ORA-00278: log file 'E:\ARCHIVE\ARC00010.001' no longer needed for this
recovery
ORA-00308: cannot open archived log 'E:\ARCHIVE\ARC00010.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

C:\Documents and Settings\oradba.DEV>E:\oracle\ora9201\bin\rman target /

Recovery Manager: Release 9.2.0.6.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: DB03 (DBID=1416954423)

RMAN> recover database;

Starting recover at 13-JAN-10
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK

starting media recovery

unable to find archive log
archive log thread=1 sequence=10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/13/2010 14:57:20
RMAN-06054: media recovery requesting unknown log: thread 1 scn 47057

RMAN> exit



Thanks and Regards,
Chetana
Re: RMAN backup not backing up archivelog after database backup [message #439060 is a reply to message #439000] Wed, 13 January 2010 18:56 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Q1.
Not sure where you are obtaining the value for the most recent archivelog value, but you should compare the output of the following and compare the exact timings:

RMAN> list backup of archivelog all;
SQL> select max(sequence#), to_char(max(first_time), 'dd-mon-yyyy hh24:mi:ss')
 from v$archived_log;


Q2.
with a PITR, Oracle will sometimes request an archivelog which could actually indicate a redo log that hasn't been archived yet. Depending on the timing, there could be some changes that reside in the online redo logs that may need to be applied to make the database consistent.
Re: RMAN backup not backing up archivelog after database backup [message #439280 is a reply to message #439000] Fri, 15 January 2010 09:14 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi Ebrian,

sorry for the delayed reply

For your reply on question 1, I checked timing of archivelog file when it was created. I will check it from v$archived_log and will post the results/conculsion

For your reply to Question 2, it is true what you are saying but not sure why?
In my case I missed the 'second' details probably.
Meaning it seems I should have checked archivelog creation time till second details to do PITR e.g. 14:38:07 etc

However, can i perform following 2 options
1) 
RECOVER automatic DATABASE  until time '2010-01-13:14:38:10' USING BACKUP CONTROLFILE;
alter database open read only;
--- confirm till what time database is recovered
---if not satisfied recover till further later point of time
RECOVER automatic DATABASE  until time '2010-01-13:14:39:40' USING BACKUP CONTROLFILE;


2)
RECOVER automatic DATABASE  until time '2010-01-13:14:38:10' USING BACKUP CONTROLFILE;
--if it fails since required archivelogs are not there
-- try another timestamp prior to above
RECOVER automatic DATABASE  until time '2010-01-13:14:34:10' USING BACKUP CONTROLFILE;


Thanks and REgards,
Chetana
Re: RMAN backup not backing up archivelog after database backup [message #439361 is a reply to message #439280] Fri, 15 January 2010 18:17 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Chetana, those are very good questions.

chetanaZ wrote on Fri, 15 January 2010 10:14
1)
RECOVER automatic DATABASE until time '2010-01-13:14:38:10' USING BACKUP CONTROLFILE;
alter database open read only;
--- confirm till what time database is recovered
---if not satisfied recover till further later point of time
RECOVER automatic DATABASE until time '2010-01-13:14:39:40' USING BACKUP CONTROLFILE;

This will work fine, you'll just have to shutdown and then mount the database again prior to doing the second recover command.


chetanaZ wrote on Fri, 15 January 2010 10:14
2)
RECOVER automatic DATABASE until time '2010-01-13:14:38:10' USING BACKUP CONTROLFILE;
--if it fails since required archivelogs are not there
-- try another timestamp prior to above
RECOVER automatic DATABASE until time '2010-01-13:14:34:10' USING BACKUP CONTROLFILE;

For this situation, you'll need to restore the database prior to performing the second recover command.

Remember though, the times you specify in the UNTIL TIME clause will be converted to an SCN prior to doing the actual recovery.

Help this helps.
Re: RMAN backup not backing up archivelog after database backup [message #439738 is a reply to message #439000] Tue, 19 January 2010 05:13 Go to previous message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hello Ebrian,

Thanks for the remark..quite encouraging!

It is very much clear now

Thanks for clarifying my doubts

Thanks and Regards,
Chetana
Previous Topic: alter database enable block change tracking
Next Topic: Need help with RMAN Active Duplication on Windows 32 bit to a 64 bit
Goto Forum:
  


Current Time: Sun Sep 25 00:54:22 CDT 2016

Total time taken to generate the page: 0.12302 seconds