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