All language subtitles for [SubtitleTools.com] RMAN Incomplete Recovery - Learning Oracle 12c [Video]

In this lesson, we're going to
do an RMAN incomplete recovery.

Now, you might ask,
why in the world

would we want to do an
incomplete recovery?

Well, there are
certain circumstances.

Of course, a complete
recovery occurs

when we restore from
backup and recover up

until the last
committed transaction.

And that's what we would need
in the case of media failures

and those types of problems.

But there are situations that
call for incomplete recoveries.

And in an incomplete
recovery, what we will do

is restore the
backup and recover up

until a point in the past.

So why would we need to do this?

Well, a simple example would
be unfortunately something

that happens all too often.

Let's say that a
user accidentally

had the privileges to
drop a production table.

And so they drop a table
in production on a Monday.

On Tuesday morning,
we discover this.

And then a decision
has to be made.

Is the table important enough--

assuming that there's no
other way to recover the data,

is it important
enough that we lose

all of the data that
occurred after the drop,

because what we're talking about
doing is restoring the backup

and recovering the database
right up until before the table

drop occurred.

Now, there can be some
difficulty in exactly

finding out when that occurred.

But assuming we can find a
mark to hit, if you will,

then we can do an
incomplete recovery.

So keep in mind that
an incomplete recovery

is one of the most stressful
things that a DBA does.

It's quite difficult and
there are steps involved.

But what we're going to
do here is simulate it

on a very small scale, sort
of a laboratory approach.

But these are the steps
that we would go through.

So in learning this, this is
a simple incomplete recovery.

But it is completely
valid for our purposes.

So we've got a little
bit of setup here.

So I'm connected
to the Scott user.

And Scott is going to create
a table for us to recover.

So we've created a
table called help_me.

Now we're going to insert a
value into it, You did it!

Hopefully that's what we'll see.

And then it's very
important that we

do a commit so that that row
is committed to the database.

What I want to do here is kind
of show the passage of time.

All right.

So we're going to simulate
some time passage here

just by switching the archive
log a couple of times.

All right.

So those changes that we
made are now in the archive

logs or switched from the
redo logs to the archive logs.

And we want to get a hard date.

So this is going to be the point
that we want to recover to.

Hang onto that.

Now we are ready
to drop our table.

So if we were to select
star from help_me to list,

that table is gone.

And I'm going to go
back here and switch

the log file one more time.

OK.

So at this point, we're told
we need to recover that table.

So we need to get the
database into a state

where it can do recoveries,
which is the mount state.

So we're going to shut
down the database.

Let me emphasize here that
the most important thing

about this entire process is
that you have a good backup.

If you attempt to do
this without a backup,

you cannot do a recovery.

startup mount.

target time.

So now we're going to
exit out of SQL*Plus.

We could have done the shutdown
and start up in either SQL*Plus

or RMAN.

But we'll need to do
the recovery in RMAN,

so we'll type in
RMAN connect target.

And then we're going
to use an alter session

command that's going to set
the time and date format.

By default, the time
and date format actually

only has the date.

But we need to be more specific.

We need to get the
entire date and the time.

So we're going to have
to actually change

the default format for
date and time information

that RMAN uses.

So we're going to
put that format

in exactly the
same format that we

have here and our target time.

We say, alter session set
nls_date_format equal to--

and inside of quotes we'll put
the same format, 2-digit day,

3-digit month, 4-digit
year, 24-hour time, minutes,

and seconds.

So now when we address RMAN with
our time and date information,

it will know what we mean.

All right.

So let's begin the recovery.

We'll do this in a run block.

And so what's going to
set this recovery apart

from other recoveries
or a complete recovery

is this command, set until time.

And here, I'm going to
copy/paste the exact time

that we want to recover
the database up to.

We say restore database.

It'll need to restore
the entire database.

And then recover database up to
the point that we've specified

here.

Now, this is going to
replace all of the data files

with data files from
the backup, and then

roll those changes forward
up until this date and time.

So this will take a little time.

All right.

So our restore and our
recovery have completed.

So if we were to go back and
look at this a little bit,

here's our command.

Notice that the first
step is to restore.

So it restores these data
files that it mentions here.

And it reads them
from backup pieces.

And then it starts
media recovery,

and it has a list
of archive logs

that it's going to draw from,
and then finishes the recovery.

So now the database is in
a state that exact time

that we specified.

Now, are we finished?

No, because we're still
in the mount state

and we need to
open the database.

However, we can't open
the database normally

in this situation,
because all of the redo

logs that we were using before
are now invalid, because we've

recovered up to a
point in the past,

and redo logs have the most
recent change information.

So what we need to do
is open the database

in a way that automatically
recreates the redo logs.

And that is done, alter
database open reset logs.

That's going to reset
the redo log files.

Now, this is actually
rebuilding the redo logs,

so this can take a little time.

The database is
open for business.

So let's exit out of
here, see how we did.

Connect to Scott.

select star from help_me.

Now, if we did
everything correctly,

we'll see the line
that we inserted.

So we've done it.

We've managed to do
an incomplete recovery

to recover the database
to a point in the past.