DBA Fundamentals

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:

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.
 

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.

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.
 

Questions:

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