DBA Fundamentals

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:

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.
SQL> CONNECT system/manager

Since you have two databases in your machine, you need to verify that we are in the YOURDBNAME database.
SQL> SELECT name FROM v$database
/
You should see the SCHOOL database.
 

A logical backup is one of a database recovery steps that is performed based on a database logical layout.

Create a table in the USERS tablespace and name it GUIDEME.
SQL> CREATE TABLE guideme
(col1 NUMBER,
col2 VARCHAR2(100))
TABLESPACE users
/

Write a procedure to insert at least 100 records into the newly created GUIDEME table.
SQL> BEGIN
SQL> FOR i IN 1..100 LOOP
SQL> INSERT INTO guideme
SQL> VALUES(i,'AAAAA' || i*100);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /

List the last 10 records from the GUIDEME table.
SQL> SELECT *
FROM guideme
WHERE col1 >
(SELECT MAX(col1) - 10 FROM guideme)
/

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

FILE=c:.dmp TABLES=(system.guideme)
OR

FILE=c:.dmp TABLES=(system.guideme)

Drop the GUIDEME table.
SQL> DROP TABLE guideme
/

Check to see if the table exists.
SQL> SELECT * FROM gudieme
/
as you can see the GUIDEME table cannot be accessed.
 

Restore Logical backup
Restore the GUIDEME table from the exported dump file. Remember, you named c:.DMP the export dump file. This time we are using the IMPORT utility interactively.
SQL> HOST IMP
OR

The IMPORT utility, will ask you to enter your USERID and PASSWORD. Type SYSTEM/MANAGER. Make sure that you are in the right database. Use the SET command to see the default database value (ORACLE_HOME). The ORACLE_HOME value should be SCHOOL. If it is a different value then it means that we are in a different database.

In the �Import file: EXPDAT.DMP>� prompt, type �C:.DMP.�

In the �Enter insert buffer size (minimum is 8192) 30720>� prompt, press enter key. The default value is 30720.

In the �List contents of import file only (yes/no): no >� prompt, press enter key. The default value is no. You will use the �Yes� option if you only want to see the contents of your exported file.

In the �Ignore create error due to object existence (yes/no): no >� prompt, press enter key. The default value is no. Use only YES if user users have truncated tables and you want to ignore when they get the error messages for objects exist.

In the �Import grants (yes/no): yes >� prompt, press enter key to use the default �yes.� This option will import all grants with an imported table.

In the �Import table data (yes/no): yes >� prompt, press enter key to use the default �yes.� This option will import all records in an imported table.

In the �Import entire export file (yes/no): no >� prompt, press enter key to use the default �no.� Use the �Yes� option, if you want to import the entire export file. In this hands-on, you are going to import only one table.

In the �Username� prompt, type the user name that you wish to import its table. In the �Enter table (T) or partition (T: P) name. Null list means all tables for user

Enter table (T) or partition (T: P) name or . if done:� prompt, you can enter table name one after the other and once it is done then type period (.). If you the press enter key, you will import all the objects for that user. In the hands-on, type GUDEME to import only that table; and then period to terminate the import process.

At the end, you get a message to indicate that your import was successful or not.

Verify restore process
Now, check to see if the GUIDEME table was recovered, by listing the last 10 records from the GUIDEME table.
SQL> SELECT * FROM guideme
WHERE col1 > (SELECT MAX(col1) - 10 FROM guideme)
/
Note this time that you have no loss of data. The logical restore action was successful! Remember that any additional records after the EXPORT action was taken will be lost.

Drop the GUIDEME table.
SQL> DROP TABLE guideme
/
You drop the table so you can repeat this hands-on, if you wish.

The following are some examples of different way you can use the exp and imp utilities:

... where export.txt contains:

BUFFER=100000

FILE=account.dmp

FULL=n

OWNER=scott

GRANTS=y

COMPRESS=y

You can let Export prompt you for parameters by entering the EXP

command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed

by various arguments. To specify parameters, you use keywords:

Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default) Keyword Description (Default)

--------------------------------------------------------------------------

USERID username/password FULL export entire file (N)

BUFFER size of data buffer OWNER list of owner usernames

FILE output files (EXPDAT.DMP) TABLES list of table names

COMPRESS import into one extent (Y) RECORDLENGTH length of IO record

GRANTS export grants (Y) INCTYPE incremental export type

INDEXES export indexes (Y) RECORD track incr. export (Y)

DIRECT direct path (N) TRIGGERS export triggers (Y)

LOG log file of screen output STATISTICS analyze objects (ESTIMATE)

ROWS export data rows (Y) PARFILE parameter filename

CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)

OBJECT_CONSISTENT transaction set to read only during object export (N)

FEEDBACK display progress every x rows (0)

FILESIZE maximum size of each dump file

FLASHBACK_SCN SCN used to set session snapshot back to

FLASHBACK_TIME time used to get the SCN closest to the specified time

QUERY select clause used to export a subset of a table

RESUMABLE suspend when a space related error is encountered(N)

RESUMABLE_NAME text string used to identify resumable statement

RESUMABLE_TIMEOUT wait time for RESUMABLE

TTS_FULL_CHECK perform full or partial dependency check for TTS

VOLSIZE number of bytes to write to each tape volume

TABLESPACES list of tablespaces to export

TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

TEMPLATE template name which invokes iAS mode export

Export terminated successfully without warnings.

assetcnt: /u01/app/oracle $

You can let Import prompt you for parameters by entering the IMP

command followed by your username/password:

Example: IMP SCOTT/TIGER

Or, you can control how Import runs by entering the IMP command followed

by various arguments. To specify parameters, you use keywords:

Format: IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT)

FULL=N or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default) Keyword Description (Default)

--------------------------------------------------------------------------

USERID username/password FULL import entire file (N)

BUFFER size of data buffer FROMUSER list of owner usernames

FILE input files (EXPDAT.DMP) TOUSER list of usernames

SHOW just list file contents (N) TABLES list of table names

IGNORE ignore create errors (N) RECORDLENGTH length of IO record

GRANTS import grants (Y) INCTYPE incremental import type

INDEXES import indexes (Y) COMMIT commit array insert (N)

ROWS import data rows (Y) PARFILE parameter filename

LOG log file of screen output CONSTRAINTS import constraints (Y)

DESTROY overwrite tablespace data file (N)

INDEXFILE write table/index info to specified file

SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N)

FEEDBACK display progress every x rows(0)

TOID_NOVALIDATE skip validation of specified type ids

FILESIZE maximum size of each dump file

STATISTICS import precomputed statistics (always)

RESUMABLE suspend when a space related error is encountered(N)

RESUMABLE_NAME text string used to identify resumable statement

RESUMABLE_TIMEOUT wait time for RESUMABLE

COMPILE compile procedures, packages, and functions (Y)

STREAMS_CONFIGURATION import streams general metadata (Y)

STREAMS_INSTANITATION import streams instantiation metadata (N)

VOLSIZE number of bytes in file on each volume of a file on tape

The following keywords only apply to transportable tablespaces

TRANSPORT_TABLESPACE import transportable tablespace metadata (N)

TABLESPACES tablespaces to be transported into database

DATAFILES datafiles to be transported into database

TTS_OWNERS users that own data in the transportable tablespace set

Import terminated successfully without warnings.

/u01/app/oracle $

Questions:

Q: What is a physical backup?

Q: What is a logical backup?

Q: How do you perform a logical backup?

Q: How do you perform a logical restore?

Q: 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. Scott lost its EMP table and you have been tasked to restore it using the IMP utility.