Hands-On 05 (RMAN-Managed Tablespace Recovery)

You, as a DBA, are responsible to recover a tablespace due to a loss of data from a media system crash. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Performing a tablespace recovery

Using the in-bottom: 0">Using the V$DATABASE view

Using the USER_TABLES

Writing a procedure

Modeling a media disk crash

Setting a datafile status using the RMAN tool

Restoring a datafile

Commands:

ALTER TABLESPACE

HOST ERASE

DOS> RMAN

RMAN> CONNECT CATALOG

RMAN> CONNECT TARGET

RMAN> SQL 'ALTER DATABASE DATAFILE OFFLINE';

RMAN> RESTORE DATAFILE

RMAN> RECOVER DATAFILE

RMAN> SQL 'ALTER DATABASE DATAFILE ONLINE';

RMAN> SQL 'ALTER TABLESPACE ONLINE';

RMAN> exit

DROP TABLE

-- Hands-On 05 (RMAN-Managed Tablespace Recovery)
-- Preparation
SET ECHO ON
connect system/manager as sysdba

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 TOOLS
-- tablespace recovery by using the RMAN tool.

-- Now, connect to the SCHOOL database as the SYSTEM/MANAGER user.

pause


CONNECT system/manager@school

pause

CLEAR SCR
-- Since we have two databases in this machine, we need to 
-- make sure 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 
-- TOOLS tablespace.

pause


SELECT table_name
FROM user_tables
WHERE tablespace_name = 'TOOLS'
/

-- It looks like there is no table in the TOOLS tablespace
-- by the SYSTEM user.

pause

CLEAR SCR
-- Now, create a table in the TOOLS tablespace.

pause


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

pause

CLEAR SCR
-- Verify if the SYSTEM user has any tables 
-- 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 100 records
-- into the newly created RMANDISCOVERY table.

pause

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

pause

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

pause

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

pause

CLEAR SCR
-- Let's delete the datafiles belonging to the TOOLS tablespace.

pause


ALTER TABLESPACE tools OFFLINE NORMAL
/

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

-- This is the equivalent to a media disk crash and the TOOLS tablespace
-- has been permanently damaged. It is your job to restore the
-- tablespace.

pause

CLEAR SCR
-- Connect to the SCHOOL database and attempt to 
-- list the last 10 records from the RMANDISCOVERY table.

pause

CONNECT system/manager

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

-- As you can see, the table in the TOOLS tablespace cannot
-- be accessed, as a result of the media artificial crash.

pause


CLEAR SCR
-- Now, restore the datafiles of the TOOLS tablespace using
-- the RMAN tool.

-- Remember, you may have more than one datafile.

pause
pause

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

pause
pause

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

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


CLEAR SCR
-- Set the TOOLS01 datafile status to OFFLINE if it is still ONLINE.
--\/-- RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
pause
pause

-- Now, restore the TOOLS01 datafile.
--\/-- RMAN> RESTORE DATAFILE 7;
pause
pause

-- Then, recover the TOOLS01 datafile.
--\/-- RMAN> RECOVER DATAFILE 7;
pause
pause


CLEAR SCR
-- Set the TOOLS01 datafile status back to ONLINE.
--\/-- RMAN> SQL 'ALTER DATABASE DATAFILE 7 ONLINE';
pause
pause

-- Now, set the TOOLS tablespace status back to ONLINE.
--\/-- RMAN> SQL 'ALTER TABLESPACE tools ONLINE';
pause
pause
-- Then exit from RMAN.
--\/-- RMAN> exit
pause
pause

CLEAR SCR
-- Now, check to see if the RMANDISCOVERY table was recovered.

pause

SELECT table_name
FROM user_tables
WHERE tablespace_name = 'TOOLS'
/

-- Yes, the RMANDISCOVERY table is back.

pause


CLEAR SCR
-- Now, list the last 10 records from the RMANDISCOVERY table.

pause

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

-- There has been no loss in data, and the recovery was successful.
-- Congratulations!

pause

CLEAR SCR
-- Drop the RMANDISCOVERY table.

pause

DROP TABLE rmandiscovery
/

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