DBA Fundamentals

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:

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@yourhost
 

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

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.

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> STARTUP MOUNT;

RMAN> RUN
{
SQL "ALTER SESSION

RMAN> OPEN RESETLOGS DATABASE;