Hands-On 07 (RMAN-Managed incomplete database recovery scenario)

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 omplete 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 07 (RMAN-Managed incomplete database recovery scenario)
-- Preparation
SET ECHO ON
connect system/manager as sysdba
DROP TABLE afterdrop
/
SET linesize 1000 pagesize 55
COL name FORMAT a60
col description format a30
col tablespace_name format a15
col file_name format a45
pause

--Start

CLEAR SCR
-- 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.

pause


CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- Since we have two databases in this machine, we need to 
-- verify that we are in the SCHOOL database.

pause


SELECT name FROM v$database
/

-- It looks like we are in the right database.
the right database.

pause

CLEAR SCR
-- Now, create a table in the TOOLS tablespace and name it
-- BEFOREDROP.

pause


CREATE TABLE beforedrop
(col1 NUMBER,
col2 VARCHAR2(100))
TABLESPACE TOOLS
/

pause

CLEAR SCR
-- Now, verify if the SYSTEM user has the BEFOREDROP table 
-- in the TOOLS tablespace.

pause

SELECT table_name, tablespace_name
FROM user_tables
WHERE tablespace_name = 'TOOLS'
/

-- Yes, it does.

pause


CLEAR SCR
-- Write a procedure to insert at least 10 records
-- into the BEFOREDROP table.

pause

BEGIN
FOR i IN 1..10 LOOP
INSERT INTO beforedrop
VALUES(i,'Before...' || i*100);
END LOOP;
COMMIT;
END;
/

pause

CLEAR SCR
-- List the last 5 records from the BEFOREDROP table.

pause

SELECT * 
FROM beforedrop
WHERE col1 > 
(SELECT MAX(col1) - 5 FROM beforedrop)
/ 

pause

CLEAR SCR
-- Now, drop the table.

pause


DROP TABLE beforedrop
/

pause

CLEAR SCR
-- Check the time that the table was dropped.
e time that the table was dropped.

pause


SET TIME ON

pause

CLEAR SCR
-- turn the time off and note the time.

pause

SET TIME OFF

pause

--

CLEAR SCR
-- Create another table in the TOOLS tablespace and 
-- name it AFTERDROP just as we did with the BEFOREDROP
-- table.

pause


CREATE TABLE afterdrop
(col1 NUMBER,
col2 VARCHAR2(100))
TABLESPACE TOOLS
/

pause

CLEAR SCR
-- Now, verify if the SYSTEM user has the AFTERDROP table 
-- in the TOOLS tablespace.

pause

SELECT table_name, tablespace_name
FROM user_tables
WHERE tablespace_name = 'TOOLS'
/

-- Yes, it does.

pause


CLEAR SCR
-- Write a procedure to insert at least 10 records
-- into the AFTERDROP table.

pause

BEGIN
FOR i IN 1..10 LOOP
INSERT INTO afterdrop
VALUES(i,'After...' || i*100);
(i,'After...' || i*100);
END LOOP;
COMMIT;
END;
/

pause

CLEAR SCR
-- And list the last 5 records from the AFTERDROP table.

pause

SELECT * 
FROM afterdrop
WHERE col1 > 
(SELECT MAX(col1) - 5 FROM afterdrop)
/ 

pause

CLEAR SCR
-- 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.
--
pause
pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause