|
User-Managed Tablespace Recovery
Introduction
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:.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
In this
exercise you will learn how to perform the
USERS tablespace recovery.
Connect to the SCHOOL database as the
SYSTEM/MANAGER user.
SQL> CONNECT
system/manager AS SYSDBA
Since we have two databases in a single
machine, we need to verify that we are in
the YOURDBNAME database.
SQL> SELECT name
FROM v$database
/
You should see SCHOOL database.
Tablespace
Recovery
First, query
the data dictionary view to check if the
SYSTEM user has created any tables in the
USERS tablespace.
SQL> SELECT
table_name
FROM user_tables
WHERE tablespace_name = 'USERS'
/
Create a table in the USERS tablespace.
SQL> CREATE TABLE
discovery
(col1 NUMBER,
col2 VARCHAR2(100))
TABLESPACE users
/
Now, check to see if the SYSTEM user has any
tables in the USERS tablespace.
SQL> SELECT
table_name, tablespace_name
FROM user_tables
WHERE tablespace_name = 'USERS'
/
It should.
Write a procedure to insert at least 100
records into the newly created DISCOVERY
table.
SQL> BEGIN
SQL> FOR i IN 1..100 LOOP
SQL> INSERT INTO DISCOVERY
SQL> VALUES(i,'AAAAA' || i*100);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /
List the last 10 records from the DISCOVERY
table.
SQL> SELECT *
FROM DISCOVERY
WHERE col1 >
(SELECT MAX(col1) - 10 FROM DISCOVERY)
/
Let's delete the datafiles of the USERS
tablespace.
SQL> ALTER
TABLESPACE users OFFLINE NORMAL
/
SQL> HOST erase C:.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.
Now, the steps to do a recovery:
Connect to the
SCHOOL database and try again to list the
last 10 records from the DISCOVERY table.
SQL> CONNECT
system/manager AS SYSDBA
SQL> 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.
Restore the datafiles of the USERS
tablespace. Remember, you may have more than
one datafile.
SQL> HOST copy
c:.bk
C:.DBF
Set the USERS tablespace status to ONLINE.
SQL> ALTER
TABLESPACE users ONLINE
/
Now, recover the USRES tablespace.
SQL> RECOVER
TABLESPACE users
/
And now, check to see if the DISCOVERY table
was recovered.
SQL> SELECT
table_name
FROM user_tables
WHERE tablespace_name = 'USERS'
/
Now, the DISCOVERY table should
be back.
List the last 10 records from the DISCOVERY
table.
SQL> SELECT *
FROM DISCOVERY
WHERE col1 >
(SELECT MAX(col1) - 10 FROM DISCOVERY)
/
Note that there is no loss of
data, and the recovery was successful! You
could also do the same for the datafile
recovery.
Drop the DISCOVERY table.
SQL> DROP TABLE discovery
/
Questions:
Q: How do you
store a destroyed datafile when Oracle is
online and running?
Q: How do you
recover a tablespace?
Q: What does
the following SQL statement?
SQL> RECOVER TABLESPACE users
/
Q: You, as a
DBA, are responsible to recover the database
to the point of failure due to a loss of
data and a media failure. Assuming that you
lost your TOOLS�s datafiles, what are the
steps to recover the datafiles to the point
of failure? |
