"He who lives
without folly isn't so wise as he thinks." Francois de La
Rochefoucauld |
Read
first then play the video:
DBA-VIDEO -RMAN
Managed incomplete database recovery scenario
DBA-VIDEO -RMAN
Managed incomplete database recovery
RMAN-Managed incomplete
database recovery scenario
Introduction
As a DBA, you are
responsible for recovering a table to a point in time due to user
failure. In your organization, a user dropped a table. That table
needs to be recovered before the table was dropped. In this hands-on
we"ll created a scenario to demonstrate to you an incomplete
recovery. In the next hands-on we"ll then use the RMAN utility to
perform an incomplete recovery to the time before the table was
dropped. As a DBA, you"ll have to recover the table using an
incomplete recovery. Your job"s responsibilities dictate that you
should at least be informed of the following basic fundamental
subjects:
Using the V$DATABASE view
Using the USER_TABLES view
Using the SET TIME command
Creating a scenario -
before and after an incomplete recovery
Commands:
DROP TABLE
SET TIME ON
SET TIME OFF
Hands-on
In this exercise you will
learn how to perform an incomplete database recovery using the RMAN
tool.
Begin by connecting to the SCHOOL database as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
Since you have two databases in your machine, you need to verify that
you are in the SCHOOL database.
SQL> SELECT name FROM v$database
/
You should have the SCHOOL database.
Scenario
You create two tables and
populate them then you will delete one of them (the same as you had a
crash). Then you will try to recover to the point in time before we
create the second table.
Create a table
Now, create a table in the
TOOLS tablespace and name it BEFOREDROP.
SQL> CREATE TABLE beforedrop
(col1 NUMBER,
col2 VARCHAR2(100))
TABLESPACE TOOLS
/
Now, verify if the SYSTEM user has the BEFOREDROP table in the TOOLS
tablespace.
SQL> SELECT table_name, tablespace_name
FROM user_tables
WHERE tablespace_name = 'TOOLS'
/
It should.
Populate a table
Write a procedure to insert at least 10 records into the
BEFOREDROP table.
SQL> BEGIN
SQL> FOR i IN 1..10 LOOP
SQL> INSERT INTO beforedrop
SQL> VALUES(i,'Before...' || i*100);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /
List the last 5 records from the BEFOREDROP table.
SQL> SELECT *
FROM beforedrop
WHERE col1 >
(SELECT MAX(col1) - 5 FROM beforedrop)
/
Drop a table
Now, drop the table.
SQL> DROP TABLE beforedrop
/
Check the time that the table was dropped. Set the time parameter on
so you can make a note on the time that the table was dropped. Make
sure to write the time some where so you can refer back to it.
SQL> SET TIME ON
Turn the time off.
SQL> SET TIME OFF
Create and populate another
table
Create another table in the TOOLS tablespace and name it AFTERDROP
just as we did with the BEFOREDROP table.
SQL> CREATE TABLE afterdrop
(col1 NUMBER,
col2 VARCHAR2(100))
TABLESPACE TOOLS
/
Now, verify if the SYSTEM user has the AFTERDROP table 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 10 records into the AFTERDROP
table.
SQL> BEGIN
SQL> FOR i IN 1..10 LOOP
SQL> INSERT INTO afterdrop
SQL> VALUES(i,'After...' || i*100);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
/
And list the last 5 records from the AFTERDROP table.
SQL> SELECT *
FROM afterdrop
WHERE col1 >
(SELECT MAX(col1) - 5 FROM afterdrop)
/
Recovery on the next
hands-on
Now, we need to recover the BEFOREDROP table. So we need to do an
incomplete database recovery to a point in time. You should have
already noted the time that the table was dropped. Notice that when we
do an incomplete recovery all the information after that time will be
lost.
In the next Hands-On
activity, we will learn how to recover to the point in time where the
BEFOREDROP table was lost, and we'll see that the AFTERDROP table will
be lost.
" It is very
difficult to live among people you love and hold back from
offering them advice." Anne Tyler (1941 - ), Celestial
Navigation, 1974 |
Questions:
Q: Describe the SET TIME
command in the SQLPLUS tool.
|