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 18

“Training is everything. The peach was once a bitter almond; cauliflower is nothing but cabbage with a college education.” Mark Twain (1835 - 1910), Pudd'nhead Wilson (1894)

 

Read first then play the video:

   DBA-VIDEO -RMAN Managed Tablespace Recovery

   

RMAN-Managed Tablespace Recovery

Introduction

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:

 

Performing a tablespace recovery

Using the V$DATABASE view

Using the USER_TABLES

Writing a procedure

Modeling a media disk crash

Setting a datafile status using the RMAN tool

Restoring a datafile

Commands:

ALTER TABLESPACE

HOST ERASE

DOS> RMAN

RMAN> CONNECT CATALOG

RMAN> CONNECT TARGET

RMAN> SQL 'ALTER DATABASE DATAFILE OFFLINE';

RMAN> RESTORE DATAFILE

RMAN> RECOVER DATAFILE

RMAN> SQL 'ALTER DATABASE DATAFILE ONLINE';

RMAN> SQL 'ALTER TABLESPACE ONLINE';

RMAN> exit

DROP TABLE

 

Hands-on

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@school

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:\your-location\data-file-name.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@school

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.

 

“We must learn to live together as brothers or perish together as fools.” Martin Luther King Jr.

 

Questions:

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> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';

RMAN> RESTORE DATAFILE 7;

RMAN> RECOVER DATAFILE 7;
RMAN> SQL 'ALTER DATABASE DATAFILE 7 ONLINE';
RMAN> SQL 'ALTER TABLESPACE tools ONLINE';