"Some people like
my advice so much that they frame it upon the wall instead of
using it." Gordon R. Dickson |
Read
first then play the video:
DBA-VIDEO -RMAN
Managed incomplete database recovery scenario
DBA-VIDEO -RMAN
Managed incomplete database recovery
RMAN-Managed incomplete
database recovery
Introduction
As a DBA, you are
responsible for recovering a table to a point in time due to user
failure. In the pervious hands-on, the scenario was modeled and
performed. Now, in this hands-on, we"ll use the RMAN utility to
perform an incomplete recovery to the point in time before the
dropping of the table. As a DBA, you"ll have to recover the table by
using an incomplete recovery. Your job"s responsibilities dictate
that you should at least be informed of the following basic
fundamental subjects:
Performing an incomplete
database recovery
Performing the database
restore
Performing the recover
procedures until a specified time
Opening a database using
the RESETLOGS option
Checking to see if a table
was recovered
Dropping a table
Commands:
RMAN> CONNECT CATALOG
RMAN> CONNECT TARGET
RMAN> SHUTDOWN
IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> OPEN RESETLOGS
DATABASE;
DROP TABLE
Hands-on
This is the continuation of
the previous hands-on situation. In this exercise we will learn how to
perform an incomplete database recovery using the RMAN tool. You will
use the RMAN tool to recover the BEFOREDROP table. You should have
already noted the time that the BEFOREDROP table was dropped from the
previous Hands-On exercise. Remember that when we do an incomplete
recovery all the information after that time will be lost.
In this Hands-On, we will
learn and discuss how to recover to a specific point in time and we
will see that the AFTERDROP table been lost.
Now, connect to the SCHOOL database as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
Run the RMAN tool.
DOS> RMAN
Then, connect to the RMAN tool using the Recovery Catalog database.
RMAN> CONNECT CATALOG
rman/password@dbs4rman
And connect to the target database.
RMAN> CONNECT TARGET system/manager@school
Perform an incomplete
recovery
Shutdown the database.
RMAN> SHUTDOWN IMMEDIATE;
Startup the database with the MOUNT option.
RMAN> STARTUP MOUNT;
Perform the database restore and recover procedures until you reach
the specified time from pervious Hands-On.
RMAN>
RUN
{
SQL "ALTER SESSION SET NLS_DATE_FORMAT=''DD-MON-YYYY
HH24:MI:SS''";
SET UNTIL TIME '04-AUG-2002 01:04:22';
RESTORE DATABASE;
RECOVER DATABASE;
}
Open a database
Open the database using the RESETLOGS option.
RMAN> OPEN RESETLOGS DATABASE;
Login to SQLPLUS as SYSDBA
SQL> CONNECT system/manager as sysdba
Verify the recovery
Now, check to see if the BEFOREDROP table was recovered. Check to
see that the BEFOREDROP table was recovered.
SQL> SELECT table_name
FROM user_tables
WHERE tablespace_name = 'TOOLS'
/
Now, the BEFOREDROP table should be back. But the AFTERDROP
table should not be anymore there.
List the last 10 records from the BEFOREDROP table.
SQL> SELECT *
FROM beforedrop
WHERE col1 >
(SELECT MAX(col1) - 10 FROM beforedrop)
/
There should be no loss of data, and the recovery should
have been successful!
Drop the BEFOREDROP table.
SQL> DROP TABLE beforedrop
/
You drop the table, so you can perform this hands-on over
if you wish.
"Great ability
develops and reveals itself increasingly with every new
assignment." Baltasar Gracian |
Questions:
Q: What is an incomplete
database recovery?
Q: What are the differences
between an incomplete database recovery and complete database
recovery?
Q: How do you perform an
incomplete database recovery?
Q: How do you perform a
restore?
Q: How do you recover a
datafile until a specified time?
Q: When do you use the
RESETLOGS option?
Q: What do the following
RMAN commands do?
RMAN> SHUTDOWN
IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> RUN
{
SQL "ALTER SESSION SET NLS_DATE_FORMAT=''DD-MON-YYYY
HH24:MI:SS''";
SET UNTIL TIME '04-AUG-2002 01:04:22';
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN> OPEN RESETLOGS
DATABASE;
|