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 20

“He who lives without folly isn't so wise as he thinks.” Francois de La Rochefoucauld

 

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 scenario

 

Introduction

As a DBA, you are responsible for recovering a table to a point in time due to user failure. In your organization, a user dropped a table. That table needs to be recovered before the table was dropped. In this hands-on we’ll created a scenario to demonstrate to you an incomplete recovery. In the next hands-on we’ll then use the RMAN utility to perform an incomplete recovery to the time before the table was dropped. As a DBA, you’ll have to recover the table using an incomplete recovery. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

 

Using the V$DATABASE view

Using the USER_TABLES view

Using the SET TIME command

Creating a scenario - before and after an incomplete recovery

Commands:

DROP TABLE

SET TIME ON

SET TIME OFF

 

Hands-on

In this exercise you will learn how to perform an incomplete database recovery using the RMAN tool.

Begin by connecting to the SCHOOL database as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA

Since you have two databases in your machine, you need to verify that you are in the SCHOOL database.
SQL> SELECT name FROM v$database
/
You should have the SCHOOL database.


Scenario

You create two tables and populate them then you will delete one of them (the same as you had a crash). Then you will try to recover to the point in time before we create the second table.

 

Create a table

Now, create a table in the TOOLS tablespace and name it BEFOREDROP.
SQL> CREATE TABLE beforedrop
               (col1 NUMBER,
               col2 VARCHAR2(100))
               TABLESPACE TOOLS
/

Now, verify if the SYSTEM user has the BEFOREDROP table in the TOOLS tablespace.
SQL> SELECT table_name, tablespace_name
               FROM user_tables
               WHERE tablespace_name = 'TOOLS'
/
It should.


Populate a table
Write a procedure to insert at least 10 records into the BEFOREDROP table.
SQL> BEGIN
SQL>      FOR i IN 1..10 LOOP
SQL>           INSERT INTO beforedrop
SQL>           VALUES(i,'Before...' || i*100);
SQL>      END LOOP;
SQL>      COMMIT;
SQL> END;
SQL> /

List the last 5 records from the BEFOREDROP table.
SQL> SELECT *
               FROM beforedrop
               WHERE col1 > (SELECT MAX(col1) - 5 FROM beforedrop)
/


Drop a table
Now, drop the table.
SQL> DROP TABLE beforedrop
/

Check the time that the table was dropped. Set the time parameter on so you can make a note on the time that the table was dropped. Make sure to write the time some where so you can refer back to it.
SQL> SET TIME ON

Turn the time off.
SQL> SET TIME OFF


Create and populate another table
Create another table in the TOOLS tablespace and name it AFTERDROP just as we did with the BEFOREDROP table.
SQL> CREATE TABLE afterdrop
               (col1 NUMBER,
               col2 VARCHAR2(100))
               TABLESPACE TOOLS
/

Now, verify if the SYSTEM user has the AFTERDROP table in the TOOLS tablespace.
SQL> SELECT table_name, tablespace_name
               FROM user_tables
               WHERE tablespace_name = 'TOOLS'
/
It should.


Write a procedure to insert at least 10 records into the AFTERDROP table.
SQL> BEGIN
SQL>      FOR i IN 1..10 LOOP
SQL>           INSERT INTO afterdrop
SQL>           VALUES(i,'After...' || i*100);
SQL>      END LOOP;
SQL>      COMMIT;
SQL> END;
/

And list the last 5 records from the AFTERDROP table.
SQL> SELECT *
               FROM afterdrop
               WHERE col1 > (SELECT MAX(col1) - 5 FROM afterdrop)
/


Recovery on the next hands-on
Now, we need to recover the BEFOREDROP table. So we need to do an incomplete database recovery to a point in time. You should have already noted the time that the table was dropped. Notice that when we do an incomplete recovery all the information after that time will be lost.

In the next Hands-On activity, we will learn how to recover to the point in time where the BEFOREDROP table was lost, and we'll see that the AFTERDROP table will be lost.

 

” It is very difficult to live among people you love and hold back from offering them advice.” Anne Tyler (1941 - ), Celestial Navigation, 1974

 

Questions:

Q: Describe the SET TIME command in the SQLPLUS tool.