|
“To repeat what
others have said, requires education; to challenge it, requires
brains.” Mary Pettibone Poole, A Glass Eye at a Keyhole, 1938 |
Read
first then play the video:
DBAx10(VIDEO)-User
Managed Tablespace Recovery
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 SCHOOL 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
/
|
“You always pass
failure on the way to success.” Mickey Rooney (1920) |
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?
|