Hands-On 11 (User-Managed Logical Backup and recovery)

You, as a DBA, are responsible to perform a logical backup using the EXP tool. Notice that if the loss of data since the last time of backup is not significant then a logical backup is a good option to use. Or you may use it to organized the table or relocate data from one database to another. You are also responsible to use the IMP tool to restore data to the same or a different destination. Your job responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Performing an Oracle Logical Backup and recovery

Using the EXP tool

Using the IMP tool

Dropping a table

Using the V$DATABASE view

Creating a table

Performing a logical backup

HOST EXP system/manager FILE=’xx’ TABLES=(xx)

Restoring a logical table

Commands:

DROP TABLE

            HOST EXP

HOST IMP

-- Hands-On 11 (User-Managed Logical Backup and recovery)
-- Preparation
SET ECHO ON
connect system/manager as sysdba
HOST ERASE c:\newfolder\*.dmp

SET linesize 1000 pagesize 55
COL name FORMAT a60
col description format a30
col tablespace_name format a15
col file_name format a45
pause

--Start

CLEAR SCR
-- In this exercise you will learn how to perform the Oracle
-- Logical Backup and recovery.

-- We will use the EXPORT and IMPORT utilities to backup a table,
-- then we'll drop the table to symbolize a table problem or crash
-- and import it into its proper user.

-- Connect to the SCHOOL database as the SYSTEM/MANAGER user.

pause


CONNECT system/manager

pause

CLEAR SCR
-- Since we have two databases in this machine, we need to 
-- verify that we are in the SCHOOL database.

pause


SELECT name FROM v$database
/

-- It looks like we are in the right database.

pause

CLEAR SCR
-- Create a table in the USERS tablespace and name
-- it GUIDEME.

pause


CREATE TABLE guideme
(col1 NUMBER,
col2 VARCHAR2(100))
TABLESPACE users
/

pause

CLEAR SCR
-- Write a procedure to insert at least 100 records
-- into the newly created GUIDEME table.

pause

BEGIN
FOR i IN 1..100 LOOP
INSERT INTO guideme
VALUES(i,'AAAAA' || i*100);
END LOOP;
COMMIT;
END;
/

pause

CLEAR SCR
-- List the last 10 records from the GUIDEME table.

pause

SELECT * 
FROM guideme
WHERE col1 > 
(SELECT MAX(col1) - 10 FROM guideme)
/ 

pause

CLEAR SCR
-- Get the logical backup of the populated table in the GUIDEME.DMP file 
-- and save it in the NEWFOLDER directory.

-- This a COMMAND LINE example.

pause


HOST EXP system/manager FILE=c:\newfolder\guideme.dmp TABLES=(system.guideme)

pause

CLEAR SCR
-- Drop the GUIDEME table.

pause


DROP TABLE guideme
/

pause

CLEAR SCR
-- Try to see if table exists.

pause


SELECT * FROM gudieme
/ 

-- As you can see the GUIDEME table cannot be accessed.

pause


CLEAR SCR
-- Restore the GUIDEME table from the exported dump file.
-- Remember, you named c:\newfolder\GUIDEME.DMP the export dump file. 

-- This time we are using the IMPORT utility interactively.

pause
pause


HOST IMP

pause
pause


CLEAR SCR
-- Now, check to see if the GUIDEME table was recovered, by
-- listing the last 10 records from the GUIDEME table.

pause

SELECT * FROM guideme
WHERE col1 > (SELECT MAX(col1) - 10 FROM guideme)
/ 

-- No loss of data. The logical restore action was successful!
-- Remember that any additional records after the EXPORT action 
-- was taken will be lost.

pause

CLEAR SCR
-- Drop the GUIDEME table.

pause

DROP TABLE guideme
/

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause