iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals II

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 | Lesson 27 | Lesson 28 | Lesson 29 |

 

Lesson 09

“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:

   DBA-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:\your-location\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.

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:\your-backup-location\USER01.bk  C:\your-location\USERS01.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?