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)
How To Determine The Start and End SCN of Restore and Recovery [message #668822] Thu, 15 March 2018 10:03 Go to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Dear all,

my partial controlfile restore looks like the following:

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

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
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

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:

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:

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.

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 Go to previous messageGo to next message
John Watson
Messages: 8962
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 Go to previous messageGo to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Dear John,

from
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?


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 Go to previous messageGo to next message
John Watson
Messages: 8962
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,
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,
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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:
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:
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 Go to previous messageGo to next message
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:

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 Go to previous message
John Watson
Messages: 8962
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 17 20:14:55 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.