Hands-On 10 (User-Managed Tablespace Recovery)

You, as a DBA, are responsible to recover the database to the point of failure due to a loss of data and media failure. Your job responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Performing a tablespace recovery

Using the V$DATABASE view

Checking if a user has created any object

Using DBA_TABLESPACE view

Writing a procedure

HOST erase C:\ORACLE\ORA90\SCHOOL\USERS01.DBF

Permanently damaging datafiles

Restoring a datafiles of a USERS tablespace

Setting a tablespace status

Recovering a tablespace

Using the USER_TABLES view

Commands:

ALTER TABLESPACE OFFLINE NORMAL

HOST COPY

ALTER TABLESPACE ONLINE

RECOVER TABLESPACE

DROP TABLE

-- Hands-On 10 (User-Managed Tablespace Recovery)
-- Preparation
SET ECHO ON
connect system/manager as sysdba
HOST ERASE c:\userhotbkup\*.bk

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 the USERS
-- tablespace recovery.

-- Connect to the SCHOOL database as the SYSTEM/MANAGER user.

pause


CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- Since we have two databases in a single 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.

pause

CLEAR SCR
-- First, query the data dictionary view to check if the 
-- SYSTEM user has created any tables in the USERS tablespace.

pause


SELECT table_name
FROM user_tables
WHERE tablespace_name = 'USERS'
/

pause

CLEAR SCR
-- Create a table in the USERS tablespace.

pause


CREATE TABLE discovery
(col1 NUMBER,
col2 VARCHAR2(100))
TABLESPACE users
/

pause

CLEAR SCR
-- Now, check to see if the SYSTEM user has any tables 
-- in the USERS tablespace.

pause

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

-- Yes, it does.

pause


CLEAR SCR
-- Write a procedure to insert at least 100 records
-- into the newly created DISCOVERY table.

pause

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

pause

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

pause

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

pause

CLEAR SCR
-- Let's delete the datafiles of the USERS tablespace.

pause


ALTER TABLESPACE users OFFLINE NORMAL
/

HOST erase C:\ORACLE\ORA90\SCHOOL\USERS01.DBF

-- This models a system crash and the USERS tablespace has been 
-- permanently damaged. It is your job to recover and restore the
-- damaged tablespace.

pause

CLEAR SCR
-- Connect to the SCHOOL database and try again to 
-- list the last 10 records from the DISCOVERY table.

pause

CONNECT system/manager AS SYSDBA

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

-- As you can see, the table in the USERS tablespace can't
-- be accessed.

pause


CLEAR SCR
-- Restore the datafiles of the USERS tablespace.
-- Remember, you may have more than one datafile.

pause


HOST copy c:\userhotbkup\USERS01.bk C:\ORACLE\ORA90\SCHOOL\USERS01.DBF

pause

CLEAR SCR
-- Set the USERS tablespace status to ONLINE.

pause

ALTER TABLESPACE users ONLINE
/ 

pause

CLEAR SCR
-- Now, recover the USRES tablespace.

pause

RECOVER TABLESPACE users
/

pause

CLEAR SCR
-- And now, check to see if the DISCOVERY table was recovered.

pause

SELECT table_name
FROM user_tables
WHERE tablespace_name = 'USERS'
/

-- Yes, the DISCOVERY table is back. Congratulations!

pause


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

pause

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

-- No loss of data, and the recovery was successful!
-- You could also do the same for the datafile recovery.

pause

CLEAR SCR
-- Drop the DISCOVERY table.

pause

DROP TABLE discovery
/

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