Database Point-in-Time Recovery | RMAN INCREMENTAL Backups | DB PITR


The next activity that we have is
performing point-in-time recovery with incremental backups. The best part about
this activity is most of the environments will have incremental
backups. Right, not many will prefer to go for DB full backups. The activity is same
but let’s take we have level 0 backup. We will create a new user, we will take
L1 back up and then we simulate a failure that we have lost the data files.
Now we want to recover the database before this user was created. We assume
these are wrong transactions and we want to open the database just before this
transaction happened. Right , so what we will do is we’ll restore from the L0
back up because restore can be done only from l0 backup not from l1 back up. And
then we will recover from this l1 back up but it will be a pointing time
recovery. So we will apply l1 back up till this time before this user is
created and then we open the database. We are not performing complete recovery
till here. Now after we open the database we must not be able to see this user. So
this user should not be visible. Let us perform this activity. Okay, let us take a
backup level 0 RMAN target / catalog rman_rc/[email protected] Now we are taking a level zero backup of the database. As I said
before every activity it’s good to take a back up. Ok great, so backup is
completed. We can check the backup tag via this command so this is our l0
backup that we have just now taken, just copy this backup tag for our reference,
let us connect to SQL plus, till first check the date and time because this is
the date and time till which will perform the recovery and just making a
note of it. Now will create a new user ogr1 and then grant the required
permissions to the user. Now connect with the user and create a test table. So we
are assuming these transactions are wrong transactions and we will perform a
point-in-time recovery before this transaction. Now commit just to make sure
transactions are all saved select * from test, so we have this table right.
I’ll connect back as sys DBA. Now will trigger the level 1 backup right now,
exit so RMAN target / we are now taking the backup incremental level one
database plus archive log, enter. Now we are taking both again level one database
plus archive log backup. Okay our l1 backup has also been completed,
let us check the backup tag, so for this l1 backup this is the backup tag, just to
make a note of this backup tag . Now will have to simulate a failure, delete all
the data files from the server. So we’ll exit from RMAN sqlplus / sysdba now
select name from v$datafile; These are all the data files, what I’ll
do is I’ll just remove these data files. Remember our database instance is
running, we are just trying to simulate a failure right . We’ll start the recovery
kill the database instance if it is already running. So we have ps -ef|grep pmon so kill – 9 this is the process ID for
proddb. The Instance has been killed. Once again check whether it is cleared
or not – done. Now start the database instance and take it to mount stage
because we have SP file and control files we can take our database to mount
stage right. Ok whenever you kill the instance it takes some time for database
instance to shutdown . So just wait for some time. Now you can see it has
connected to idle instance. Even though if it is connected like this, still if
you issue any transactions it won’t work but still make sure you see this line
connected to idle instance. Now we can start the database instance and take it
to mode stage, startup mount, great database has been mounted. Now we have to
connect to RMAN and issue below command. First of all because we are
performing point in time recovery, we will set the time, set until time and we
have to give the time in this command. So we have the time before these
transactions were issued, just copy this one and paste it over here. Right next is
restore database, we can restore only from level 0 tag correct.
So RMAN target /yeah, list backup of database summary; This is the l0 tag
correct . So I can restore only from this l0 tag . So just get this tag and put it
over there. Now we can perform the database recovery from l1 tag. This is
the l1 tag by looking at the recovery process, we are applying this l1 tag and
we are telling that only apply till this time before this user was created and
that’s why we have set until time Clause over here on the top.
Even though we are saying recover database from the tag, so this tag or
this l1 will be applied only till this time. So all the transactions after this
time will not be applied to the database. So don’t worry and then alter database
open reset logs, why reset logs because this is an incomplete recovery. So we’ll
run this block. Now with our l0 tags all the data files are being restored to
their original locations great. So now we can see because we don’t have so many
transactions, so many archive logs, so many log switches, we can see that it
has applied some transactions from the archive logs
right. So it might have taken these archive logs and there are few
transactions in those archive logs that is it has applied but as per our
recovery scenario right now we have open our database over here. So we
should not see this new user okay. So don’t worry because we don’t have much
transactions, that’s why RMAN has applied , archives on the disk but we’ll
exit. sqlplus / as sysdba , so select name, open_mode from v$database and now we should not see the newly created user. So connect ogr1 as ogr1 we can see invalid user ID and password , that means we have exactly
recovered our database before these wrong transactions happen inside the
database. So this is how we perform database point in time recovery via
RMAN incremental backups.

Daniel Ostrander

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *