DBA Fundamentals

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:

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.
SQL> CONNECT system/manager@yourhost

Since you have two databases in your server or PC, you need to make sure that we are in the SCHOOL database.

SQL> SELECT name FROM v$database
/
You should see the SCHOOL database name.

Query the data dictionary view to check, if the SYSTEM user has created any tables in the TOOLS tablespace.
SQL> SELECT table_name
FROM user_tables
WHERE tablespace_name = 'TOOLS'
/
It should look like there is no table in the TOOLS tablespace by the SYSTEM user.
 

Create a table to recover later
Now, create a table in the TOOLS tablespace.
SQL> CREATE TABLE rmandiscovery
(col1 NUMBER,
col2 VARCHAR2(100))
TABLESPACE TOOLS
/

Verify if the SYSTEM user has any tables in the TOOLS tablespace.
SQL> SELECT table_name, tablespace_name
FROM user_tables
WHERE tablespace_name = 'TOOLS'
/
It should.

Write a procedure to insert at least 100 records into the newly created RMANDISCOVERY table.
SQL> BEGIN
SQL> FOR i IN 1..100 LOOP
SQL> INSERT INTO rmandiscovery
SQL> VALUES(i,'AAAAA' || i*100);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /

List the last 10 records from the RMANDISCOVERY table.
SQL> SELECT *
FROM rmandiscovery
WHERE col1 >
(SELECT MAX(col1) - 10 FROM rmandiscovery)
/
 

Remove tablespace
Let's delete the datafiles belonging to the TOOLS tablespace.
SQL> ALTER TABLESPACE tools OFFLINE NORMAL
/
SQL> HOST erase C:.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.

Identify the permanent damage

Connect to the SCHOOL database and attempt to list the last 10 records from the RMANDISCOVERY table.
SQL> CONNECT system/manager
SQL> 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.
 

Restore tablespace or datafile using RMAN
Now, restore the datafiles of the TOOLS tablespace using the RMAN tool. Remember, you may have more than one datafile.
First, run the RMAN tool.
DOS> RMAN

Connect to the RMAN tool using the Recovery Catalog database.
RMAN> CONNECT CATALOG RMAN/password@dbs4RMAN

Connect to the target database.
RMAN> CONNECT TARGET system/manager@yourhost

Set the TOOLS01 datafile status to OFFLINE if it is still ONLINE.
RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
 

Restore a datafile
Now, restore the TOOLS01 datafile.
RMAN> RESTORE DATAFILE 7;
 

Recover a datafile
Then, recover the TOOLS01 datafile.
RMAN> RECOVER DATAFILE 7;

Set the TOOLS01 datafile status back to ONLINE.
RMAN> SQL 'ALTER DATABASE DATAFILE 7 ONLINE';

Now, set the TOOLS tablespace status back to ONLINE.
RMAN> SQL 'ALTER TABLESPACE tools ONLINE';

Then exit from RMAN.
RMAN> exit
 

Verify your recovery
Now, check to see if the RMANDISCOVERY table was recovered.
SQL> SELECT table_name
FROM user_tables
WHERE tablespace_name = 'TOOLS'
/
Now, you should recover and have the RMANDISCOVERY table back.

Now, list the last 10 records from the RMANDISCOVERY table.
SQL> SELECT *
FROM rmandiscovery
WHERE col1 >
(SELECT MAX(col1) - 10 FROM rmandiscovery)
/
There should be no loss in data, and the recovery should have been successful.

Drop the RMANDISCOVERY table.
SQL> DROP TABLE rmandiscovery
/
 

You dropped the table so you can repeat this hands-on if you wish.

Q: How do you perform a tablespace recovery using RMAN?

Q: How do you set a datafile status using the RMAN tool?

Q: How do you restore a corrupted datafile?

Q: How do you recover a corrupted datafile?

Q: What do the following RMAN commands do?

RMAN> RECOVER DATAFILE 7;

RMAN> SQL 'ALTER DATABASE DATAFILE 7 ONLINE';

RMAN> SQL 'ALTER TABLESPACE tools ONLINE';