Topics: RMAN-Managed Tablespace
Recovery
|
More Resources by
Google: |
|
|
|
|
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
|