iSelfSchooling - Advanced DBA 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:.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?