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 10

“Success isn't permanent, and failure isn't fatal.” Mike Ditka (1939)

 

Read first then play the video:

   DBA-VIDEO -User Managed Logical Backup and Recovery

   

User-Managed Logical Backup and recovery

 

Introduction

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

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 SCHOOL database.
SQL> SELECT name FROM v$database
/
You should see the SCHOOL database.


Perform Logical backup

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.

SQL> HOST EXP system/manager -

                FILE=c:\your-backup-location\your-file.dmp TABLES=(system.guideme)
OR

DOS> EXP system/manager -

                FILE=c:\your-backup-location\your-file.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

MS-DOS> IMP

 

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:

exp scott/tiger file=myexport.dmp log=emp.log tables=emp rows=yes indexes=no

exp scott/tiger file= myexport.dmp tables=(emp,dept)

imp scott/tiger file= myexport.dmp full=yes

imp scott/tiger file= myexport.dmp fromuser=scott touser=scott tables=dept

exp userid=scott/tiger@orcl parfile=export.txt

 

... where export.txt contains:

BUFFER=100000

FILE=account.dmp

FULL=n

OWNER=scott

GRANTS=y

COMPRESS=y

 

$ exp help=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.

 

$ imp help=y

 

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.

 

 

“I am still determined to be cheerful and happy, in whatever situation I may be; for I have also learned from experience that the greater part of our happiness or misery depends upon our dispositions, and not upon our circumstances.” Martha Washington (1732 - 1802)

 

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.