"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';
|