iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals II

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 | Lesson 27 | Lesson 28 | Lesson 29 |

 

Lesson 21

“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;