Solved

point  in time restore

Posted on 2014-11-02
15
202 Views
Last Modified: 2014-12-03
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
Comment
Question by:marrowyung
  • 10
  • 3
  • 2
15 Comments
 
LVL 12

Expert Comment

by:praveencpk
ID: 40418091
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40418779
"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
 
LVL 5

Accepted Solution

by:
Sanjeev Labh earned 300 total points
ID: 40419138
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40436593
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 40436595
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
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 40448448
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40451606
"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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Assisted Solution

by:praveencpk
praveencpk earned 200 total points
ID: 40452770
>>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
 
LVL 1

Author Comment

by:marrowyung
ID: 40461352
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 40473349
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40473353
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 40473355
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
 
LVL 12

Expert Comment

by:praveencpk
ID: 40474953
>>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
 
LVL 1

Author Comment

by:marrowyung
ID: 40475223
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40478018
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now