Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

point in time restore

Dear all,

how can I implement the point in time restore in Oracle ? if I restore the backup using RMAN and I still need to restore latest archive log if that database is totally deleted, right? and then followed by the archive log roll forward operation to minimize data lost, right?

any logic and step by step procedure ?
0
marrowyung
Asked:
marrowyung
  • 10
  • 3
  • 2
2 Solutions
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
The Database point in time recovery is also called has incomplete recovery it can be done through RMAN or FLASHBACK database to a specific SCN, time or log sequence number using set until command in RMAN.

here is the doc which explains about DBPITR

http://docs.oracle.com/cd/E36909_01/backup.1111/e10642/rcmflash.htm#BRADV80055

http://www.oracleracexpert.com/2009/11/how-to-restore-database-to-point-in.html
0
 
marrowyungAuthor Commented:
"The Database point in time recovery is also called has incomplete recovery "

so this means for sure that this kind of point in time restore can't help us too ? it only depends on how frequent we backup the log and we can only restore up to that point ?

so what should be your suggestion on this ?

so this mean we have to restore the database file from backup as well as the latest archive log for sure?

what if the archive log get deleted too ? the Oracle should be keep running and what we do is just restore the latest archive log ? but it should not work as Oracle should be about to the recreate the archive log itself ?

oh.. should we consider the guaranteed restore points ?
0
 
Sanjeev LabhCommented:
For PITR important thing is what is the scenario under which you are trying to do it and kind of information or data is available to you.

Point in time basically translates to the last applied SCN at the time stated in PITR operation. The essential things that you would need in this case is a full level 0 backup for doing RMAN restore of that particular point in time. However if your full backup was of a time earlier than the point in time that you would like to restore then you will need the addtional log files so as to bring the database consistency to upto that point in time. If you have incremental backups till the time specified then RMAN should be easily perform all actions. However, if not then after RMAN restore you will need to apply the archive logs required till the point in time as desired. If you happen to lose these archival logs as well then probably it would be quite difficult to move ahead in time, data loss would occur then.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
marrowyungAuthor Commented:
"For PITR important thing is what is the scenario under which you are trying to do it and kind of information or data is available to you."

for banking, they will say they can't lost anything! what can you do with it ?

What I hope to say is to find script to find out from Oracle the latest time we can restore OR what time of data provide the best data we can restore.

so this mean to me that:

1) Full backup of DB.
2) incremental backup of DB (MS SQL only has differential)
3) plus the restore of archive log

can do the job ?

it seems to me that as long as archive log STILL THERE there it will ok ? DBA/root can delete this kind of file any time and this files is not locked for use ?

BTW, what is level 0 backup ?
0
 
marrowyungAuthor Commented:
from one of the link you show me:

Step 2: Determine the SCN, time or log sequence that you want to recovery the
SCN – you can get the SCN from alert.log file
Sequence – v$log_history


how to ACCURATLY delete the SCN?
0
 
Sanjeev LabhCommented:
The RMAN that you would be having will have its log. That log will tell you what is the max PIT or SCN upto which it can be restored.

If someone has deleted your archive log then you will lose your data for whatever period the gap is. That is why we design high availability architetture to take care of any kind of loss or failure. If your system has not been designed is such a why then you will have to live with data loss. But if you say that it is a banking database I am sure then high availability would surely be taken care of.

I got confused with your statement about incremental backup where you state about MS SQL. In your question you have mentioned about Oracle database so I do not understand how MS SQL comes into picture.

If you know about incremental backup then you should know about level 0 backup also. It is almost the same as full backup.

I think it is not really getting clear as to what is your real requirement. Are you asking a question about a situation you are in or do you want to design a system cater to recovery needs. That would make things easier to understand your real requirement.
0
 
marrowyungAuthor Commented:
"If someone has deleted your archive log then you will lose your data for whatever period the gap is."

you mean thye hack me ? so they del my files?

"I got confused with your statement about incremental backup where you state about MS SQL. In your question you have mentioned about Oracle database so I do not understand how MS SQL comes into picture."

I am just comparing concept !


"If you know about incremental backup then you should know about level 0 backup also. It is almost the same as full backup.
"

almost the same, what is the diff in high level concept ?

"Are you asking a question about a situation you are in or do you want to design a system cater to recovery needs."

it is about a sistutaion I am in. trying to setup a better method of backup !
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
>>how to ACCURATLY delete the SCN?<<
i didn't understand your question

>>what is the diff in high level concept ?<<
The level 0 backup is recorded as an incremental backup in the RMAN repository, so it can be used as the parent for a level 1 backup.A full backup can never be part incremental backup strategy i.e it cannot be the parent for a subsequent incremental backup .

Check this oracle doc on incremental backup
https://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmcncpt.htm#BRADV89499

>>you mean thye hack me ? so they del my files? <<
Not really, your disk in which archive log are saved may be corrupted or mistakenly your Unix Admin deleted the file in archive log directory..
0
 
marrowyungAuthor Commented:
"Not really, your disk in which archive log are saved may be corrupted or mistakenly your Unix Admin deleted the file in archive log directory.. "

this is a good point as even Oracle teacher cant' explain why oracle file can be delete when operating !

it should be locked for the use of Oracle only, rihgt? even administrator shouldn't able to delete it !
0
 
marrowyungAuthor Commented:
praveencpk,

from this link : http://www.oracleracexpert.com/2009/11/how-to-restore-database-to-point-in.html

"Step 2: Determine the SCN, time or log sequence that you want to recovery the
SCN – you can get the SCN from alert.log file
Sequence – v$log_history"

so the alert.log will record the latest SCN number we can use ?so just record down the last one we can use ?

we can find the sequence number by select * from v$log_history ?

">>how to ACCURATLY delete the SCN?<<
 i didn't understand your question
"

I mean I don't know how to find the SCN to let me restore the DB to the point I want.
0
 
marrowyungAuthor Commented:
"RUN
{
SET UNTIL TIME 'Aug 10 2009 11:00:00';
# SET UNTIL SCN 100; # alternatively, specify SCN
# SET UNTIL SEQUENCE 123; # alternatively, specify log seq
RESTORE DATABASE;
RECOVER DATABASE;
}"

so we can do either:
!) SET UNTIL TIME
2) SET UNTIL SCN
or
3) SET UNTIL SEQUENCE  

?

but just one of them, right?

# is a comment for Oracle ?
0
 
marrowyungAuthor Commented:
once we open the dB by:

4. Open database with resetlogs
SQL> ALTER DATABASE OPEN RESETLOGS;

then oracle can serve custom and we do nothing after that?

RESTLOGS means force oracle to follow the log SCN right now and ignore the log number before failure  ?
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
>>I mean I don't know how to find the SCN to let me restore the DB to the point I want.<<
SQL>select dbms_flashback.get_system_change_number from dual;
                                or
SQL>select CURRENT_SCN from v$database;

the above SQL will give you SCN details.

>>but just one of them, right?<<
Yes any one of them you can use for restoring your database as per your requirement.

>>RESTLOGS means force oracle to follow the log SCN right now and ignore the log number before failure  ?<<
yes
0
 
marrowyungAuthor Commented:
praveencpk,

"SQL>select dbms_flashback.get_system_change_number from dual;
                                 or
 SQL>select CURRENT_SCN from v$database;

 the above SQL will give you SCN details."


you very helpful. but when I need to do this, the redolog and/OR archieve log probably dead and oracle stop operating, right? then how can I run this command ?

">>RESTLOGS means force oracle to follow the log SCN right now and ignore the log number before failure  ?<<
 yes "

tks so much !

by the way, did your oralce fail like this before? I cant' see oracle on linux/unix have the design like this! the root or users has root level permission can delete the redo log and archieve log online !?

My option is, for this kind of important Oralce file, why Oracle don't lock that DURING the operation ! I can't understandt this at all!

MS SQL, administrator can't delete any file IN USE even that guys is the local/domain administrator. they all locked.
0
 
marrowyungAuthor Commented:
praveencpk,

"select CURRENT_SCN from v$database;"

that one only return the current SCN number, what if we try to check of the past SCN number ?
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 10
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now