OraFAQ Forum: Backup & Recovery » How To Determine The Start and End SCN of Restore and Recovery

Home » RDBMS Server » Backup & Recovery » How To Determine The Start and End SCN of Restore and Recovery (11.2.0.4 SE Windows 2012 R2 Data Center)

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

How To Determine The Start and End SCN of Restore and Recovery [message #668822]

Thu, 15 March 2018 10:03

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear all,

my partial controlfile restore looks like the following:

Code: [Select all] [Show/ hide]

RMAN-08553: channel ch01: restoring control file from AUTOBACKUP c-2228540098-20180315-01
RMAN-08534: channel ch01: control file restore from AUTOBACKUP complete
RMAN-08505: output file name=D:\APP\ORACLE\ORADATA\kamden\CONTROL01.CTL
RMAN-08505: output file name=D:\APP\ORACLE\FAST_RECOVERY_AREA\kamden\CONTROL02.CTL
RMAN-03091: Finished restore at 2018-03-15 01:00:10

before I begin restore and recovery, I find out the maximum recovery point

Code: [Select all] [Show/ hide]

SYS@rotterdam amsterdam/kamden.eindhoven.com>select sequence#, thread#, first_change#, next_change#
  2  from v$archived_log L, v$database D
  3  where L.resetlogs_change# = D.resetlogs_change# and
  4  sequence# in (:v_sequence);

SEQUENCE# THREAD# FIRST_CHANGE# NEXT_CHANGE#
--------- ------- ------------- ------------
   374833       1   86670157235  86671317955

SYS@rotterdam amsterdam/kamden.eindhoven.com>
SYS@rotterdam amsterdam/kamden.eindhoven.com>
SYS@rotterdam amsterdam/kamden.eindhoven.com>
SYS@rotterdam amsterdam/kamden.eindhoven.com>select sequence#, thread#, first_change#, next_change#
  2  from v$backup_redolog
  3  where sequence# in (:v_sequence);

no rows selected

so of course the expected recovery is up to 374833

but to my surprise.
from my restore log

Code: [Select all] [Show/ hide]

RMAN-03002: failure of recover command at 03/15/2018 08:16:49
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 374878 and starting SCN of 86719434183 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 374877 and starting SCN of 86718256242 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 374876 and starting SCN of 86717065755 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 374875 and starting SCN of 86715873995 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 374874 and starting SCN of 86714674847 found to restore

I only expected an error expecting no backup of archivelog log for thread 1 with sequence 374833 but not that far out 374874

when I look the the rman_output of the last backup job where the controlfile backuppiece is used for my restore

Code: [Select all] [Show/ hide]

input archived log thread=1 sequence=374873 RECID=833739 STAMP=970786266
channel ch00: starting piece 1 at 15-march    -2018 12:38:53 am
channel ch00: finished piece 1 at 15-march    -2018 12:39:08 am
piece handle=archivelog_kamden_374873_s131787_Umbstq7ot_1_1_t970792733 tag=TAG20180314T225107 comment=API Version 2.0,MMS Version
channel ch00: backup set complete, elapsed time: 00:00:15
channel ch00: deleting archived log(s)
archived log file name=R:\APP\ORACLE\ORADATA\kamden\ARC0000374873_0847665026.0001 RECID=833739 STAMP=970786266
Finished backup at 15-march    -2018 12:39:08 am

Starting Control File and SPFILE Autobackup at 15-march    -2018 12:39:08 am
piece handle=c-2228540098-20180315-00 comment=API Version 2.0,MMS Version 5.0.0.0
Finished Control File and SPFILE Autobackup at 15-march    -2018 12:39:24 am

released channel: ch00

allocated channel: ch00
channel ch00: SID=9 device type=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle - Release 7.7.2 (20160111)

sent command to channel: ch00

Starting backup at 15-march    -2018 12:39:30 am
channel ch00: starting full datafile backup set
channel ch00: specifying datafile(s) in backup set
including current control file in backup set
channel ch00: starting piece 1 at 15-march    -2018 12:39:32 am
channel ch00: finished piece 1 at 15-march    -2018 12:39:47 am
piece handle=controlcopy.ctl_kamden_s131789_t970792771 tag=TAG20180315T003931 comment=API Version 2.0,MMS Version 5.0.0.0
channel ch00: backup set complete, elapsed time: 00:00:15
Finished backup at 15-march    -2018 12:39:47 am

Starting Control File and SPFILE Autobackup at 15-march    -2018 12:39:47 am
piece handle=c-2228540098-20180315-01 comment=API Version 2.0,MMS Version 5.0.0.0
Finished Control File and SPFILE Autobackup at 15-march    -2018 12:40:03 am

from the rman_output log, it is quite obvious that the last archivelog backup is 374873, but why when I started restore and recovery, it goes to recover all the way out to 374874

why is this so?

my restore command is as follow:

Code: [Select all] [Show/ hide]

run {
allocate channel ch01 device type sbt;
send 'NB_ORA_CLIENT=rotterdam';
#https://juliandontcheff.wordpress.com/2012/01/11/restoring-the-oracle-database-when-is-it-ready-can-i-start-start-the-application/
sql "alter session set optimizer_mode=RULE";
SET NEWNAME FOR ........
.......
restore database;
SWITCH DATAFILE ALL;
recover database;
 }

g

my customer is backing up directly to tape.

the tape server is accessible to both the production server as well as to my server where I'm doing the restore.

also when I try to find my fuzzines or begin scn I could not find it:

Code: [Select all] [Show/ hide]

select max(absolute_fuzzy_change#) fuzz#, max(checkpoint_change#) chkpnt# from
  2  (select file#, completion_time, checkpoint_change#, absolute_fuzzy_change# from v$backup_datafile
  3  where
  4  incremental_level = 0
  5  --and trunc(completion_time) <= to_date('2017-07-28','YYYY-MM-DD')
  6  AND trunc(completion_time) <= to_date(:v_completion_time_c,'YYYY-MM-DD')
  7  and file# <> 0
  8  order by completion_time desc
  9  	     );

                      FUZZ#                     CHKPNT#
--------------------------- ---------------------------

the only backup I have is file#=0 i.e.

Code: [Select all] [Show/ hide]

SELECT DISTINCT file# FROM v$backup_datafile;

     FILE#
----------
         0

so how do I know the starting recovery scn and end scn.

seems that I'm using the wrong query to find out the starting and end recovery scn.

many thanks in advance!

Report message to a moderator

Re: How To Determine The Start and End SCN of Restore and Recovery [message #668824 is a reply to message #668822]

Thu, 15 March 2018 11:05

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

You are doing a complete recovery: no loss of data, applying all archive logs and the current online log. Is that a problem?

Your first query isn't telling you anything useful, because it is against views populated from a controlfile backup made some time previously, it is way of date.

Report message to a moderator

Re: How To Determine The Start and End SCN of Restore and Recovery [message #668866 is a reply to message #668824]

Mon, 19 March 2018 22:48

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear John,

from

Code: [Select all] [Show/ hide]

RMAN-03002: failure of recover command at 03/15/2018 08:16:49
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 374878 and starting SCN of 86719434183 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 374877 and starting SCN of 86718256242 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 374876 and starting SCN of 86717065755 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 374875 and starting SCN of 86715873995 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 374874 and starting SCN of 86714674847 found to restore

while I understand that I can only perform point in time reocvery, my main concern is that there are 5 archivelog that is not backed up. how can I reduce the number of archivelog that is not backed up?

does it help if archived log is backed up more often?

or is there something wrong with the rman script?

Code: [Select all] [Show/ hide]

RUN {

ALLOCATE CHANNEL ch00

    TYPE 'SBT_TAPE';

SEND 'NB_ORA_CLIENT=rotterdam,NB_ORA_SID=kamden,NB_ORA_SERV=rotterdam

BACKUP

    INCREMENTAL LEVEL=0 PROXY

    FORMAT 'DF_level0_%d_s%s_U%U_t%t'

    DATABASE;


RELEASE CHANNEL ch00;


# Backup Archived Logs

sql 'alter system archive log current';


ALLOCATE CHANNEL ch00

    TYPE 'SBT_TAPE';

SEND 'NB_ORA_CLIENT=rotterdam,NB_ORA_SID=kamden,NB_ORA_SERV=rotterdam

BACKUP

    FORMAT 'archivelog_%d_%e_s%s_U%U_t%t'

    FILESPERSET 1

    ARCHIVELOG

        ALL

DELETE INPUT;


RELEASE CHANNEL ch00;


# Control File Backup


ALLOCATE CHANNEL ch00

    TYPE 'SBT_TAPE';

SEND 'NB_ORA_CLIENT=rotterdam,NB_ORA_SID=kamden,NB_ORA_SERV=rotterdam

BACKUP

    FORMAT 'controlcopy.ctl_%d_s%s_t%t'

    CURRENT CONTROLFILE;

RELEASE CHANNEL ch00;

}

Report message to a moderator

Re: How To Determine The Start and End SCN of Restore and Recovery [message #668870 is a reply to message #668866]

Tue, 20 March 2018 04:07

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

OK, I've looked at it in a bit more detail (while watching an upgrade to 12.2 go through...) and I see what is going on.
THe last archive logfile you backed up was this,

Code: [Select all] [Show/ hide]

input archived log thread=1 sequence=374873 RECID=833739 STAMP=970786266
channel ch00: starting piece 1 at 15-march    -2018 12:38:53 am

but you are restoring your controlfile from this backup,

Code: [Select all] [Show/ hide]

Starting Control File and SPFILE Autobackup at 15-march    -2018 12:39:47 am
piece handle=c-2228540098-20180315-01 comment=API Version 2.0,MMS Version 5.0.0.0

log sequences 374874 - 374878 will have occured after the logfile backup and before the controlfile backup. So the message is correct. You can recover only up 374873.

Report message to a moderator

Re: How To Determine The Start and End SCN of Restore and Recovery [message #668909 is a reply to message #668870]

Wed, 21 March 2018 23:17

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

This is the reason:

Quote:

input archived log thread=1 sequence=374873 RECID=833739 STAMP=970786266
channel ch00: starting piece 1 at 15-march -2018 12:38:53 am <<-- Start a process to backup at 12:38:53
channel ch00: finished piece 1 at 15-march -2018 12:39:08 am <<-- Finish a process backup at 12:39:08

Finished backup at 15-march -2018 12:39:08 am <<-- Finish at 12:39:08am

Starting Control File and SPFILE Autobackup at 15-march -2018 12:39:08 am <<-- Start backup of control-file at 12:39:08
piece handle=c-2228540098-20180315-00 comment=API Version 2.0,MMS Version 5.0.0.0
Finished Control File and SPFILE Autobackup at 15-march -2018 12:39:24 am <<-- Finish backup of control-file at 12:39:24

From 12:38:53 to 12:39:08, there was 5 log generated on your database, and they was not backup in time.
Put
"Set until sequence 374873" to make incomplete restore/recovery or apply 5 archive log when issue "recover database;" command

Report message to a moderator

Re: How To Determine The Start and End SCN of Restore and Recovery [message #669144 is a reply to message #668909]

Thu, 05 April 2018 00:03

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear trantuananh24hg

your set until clause is not correct.

MAN-06025: no backup of archived log for thread 1 with sequence 374874 and starting SCN of 86714674847 found to restore

it should be

Set until sequence 374874

https://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsubcl019.htm#RCMRF160

Quote:

Specifies an SCN as an upper, noninclusive limit.

since it only backup to 374873 then we have to specify 374874

also

if someone could tell me which is the correct way to ensure a complete backupset

assuming controlfile autobackup is turn on;

Method 1:

Code: [Select all] [Show/ hide]

run
{
backup incremental level 0 database;
backup archivelog all delete input;
backup current controlfile;
backup spfile;
}

the current controlfile and spfile backup is to make sure that if case autobackup is turn off they are backed up

Method 2:

Code: [Select all] [Show/ hide]

run
{
backup incremental level 0 database plus archivelog delete input;
backup current controlfile;
backup spfile;
}

however the following will not result in a complete backupset

backup incremental level 0 database;
backup archivelog all delete input;

which method is preferred?

thanks

Report message to a moderator

Re: How To Determine The Start and End SCN of Restore and Recovery [message #669292 is a reply to message #669144]

Thu, 12 April 2018 10:40

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear all,

is there any article in MOS that teaches us how to backup database properly? I believe there is but I just could not find the article

I mean the following is not proper. controlfile autobackup is turned on.

run
{
backup database;
}

run
{
backup archivelog all delete input;
}
In fact the above is actually two different incomplete backupset
it will result in the following when doing the restore:

Code: [Select all] [Show/ hide]

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\APP\ORACLE\ORADATA\KORCL\SYSTEM01.DBF'

however if backup is done the following:

run
{
backup database plus archivelog;
}

It will not result in the above restore error. The above is one complete backupset.

many thanks in advance

Report message to a moderator

Re: How To Determine The Start and End SCN of Restore and Recovery [message #669293 is a reply to message #669292]

Thu, 12 April 2018 10:57

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

All you need to do is read the Backup And Recovery User Guide, it has plenty of examples and tutorials.
There is nothing wrong with your separate commands to backup the database and then the archivelogs, there is no necessity to have them in one: you get separate backupsets anyway. The problem will be in your restore and recover commands.

Report message to a moderator

Previous Topic:	ORA-27072: File I/O error for sysaux.dbf, more logfiles than expected
Next Topic:	Restore datafile after RMAN backup taken

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 04 20:43:00 CDT 2025