Hands-On 08 (RMAN-Managed incomplete database recovery)

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; o:p>

RMAN> STARTUP MOUNT;

RMAN> OPEN RESETLOGS DATABASE;

DROP TABLE

-- Hands-On 08 (RMAN-Managed incomplete database recovery)
-- Preparation
SET ECHO ON
clear scr
connect system/manager as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a60
col description format a30
col description format a30
col tablespace_name format a15
col file_name format a45
pause

--Start

CLEAR SCR
-- This is the continuation of the Hands-On number 7 situation.
-- In this exercise we will learn how to perform an incomplete
-- database recovery using the RMAN tool.

-- We 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.

pause


CONNECT system/manager AS SYSDBA

pause


CLEAR SCR
-- First, run the RMAN tool.
--\/-- DOS> RMAN

pause
pause

-- Then, connect to the RMAN tool using the Recovery Catalog database.
--\/-- RMAN> CONNECT CATALOG RMAN/password@dbs4RMAN
CATALOG RMAN/password@dbs4RMAN
pause
pause

-- And connect to the target database.
--\/-- RMAN> CONNECT TARGET system/manager@school
pause
pause


CLEAR SCR
-- Shutdown the database.
--\/-- RMAN> SHUTDOWN IMMEDIATE;
pause
pause

-- Startup the database with the MOUNT option.
--\/-- RMAN> STARTUP MOUNT;
pause
pause

-- 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;
--\/-- }

pause
pause


CLEAR SCR
-- Open the database using the RESETLOGS option.
--\/-- RMAN> OPEN RESETLOGS DATABASE;
pause
pause

connect system/manager as sysdba
CLEAR SCR
-- Now, check to see if the BEFOREDROP table was recovered.
e BEFOREDROP table was recovered.

pause

SELECT table_name
FROM user_tables
WHERE tablespace_name = 'TOOLS'
/

-- Yes, the BEFOREDROP table is back.
-- But notice that the AFTERDROP table was lost.

pause


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

pause

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

-- There has been no loss of data, and the recovery was successful!

pause

CLEAR SCR
-- Drop the BEFOREDROP table.

pause

DROP TABLE beforedrop
/

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