Logical backup of Oracle10g Database

1. First, I created a user named linuxidc and set the password to "linuxidc123" (SQL statement operated as sys) createuserlinuxi

1. First, I created a user named linuxidc and set the password to "linuxidc123" (SQL statement operated as sys) create user linuxi

1. First, I created a user named "linuxidc" and set the password to "linuxidc123" (an SQL statement operated as sys)

Create user linuxidc identified by linuxidc123;

2. assign some permissions to new users

Grant create session to linuxidc;
Grant create table to linuxidc;

3. Create a directory to store files. (Root operation on the server. And modify the owner and group attributes for this object)

[Root @ ~] # Cd/u01/

[Root @ u01] # mkdir ts

[Root @ u01] # chown-R Oracle: oinstall ts

4. Create a tablespace named "linuxidc. Add the linuxidc user to the IDC tablespace. And allocate a certain amount of space to him (sys identity)

Create tablespace linuxidc
Datafile '/u01/ts/linuxidc001.dbf'
Size 20 m;

Alter user linuxidc default tablespace linuxidc;

Alter user linuxidc quota 20 m on linuxidc;

5. Use the linuxidc account to log on. Create a test table named test1.

Create table test1 (
Ts int, tsname varchar2 (30 ));

6. Create a backup directory and use exp to export the data (executed on the server)

[Oracle @ u01] $ mkdir dump

[Root @ u01] # chown-R oracle: oinstall dump

[Root @ u01] # chmod 775 dump

[Oracle @ dump] $ exp

Export: Release 10.2.0.1.0-Production on Wed May 16 06:44:20 2012

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
Enter array fetch buffer size: 4096>

Export file: expdat. dmp>/u01/dump/emp052209.dmp -- select the target file and storage path for the object to be backed up

(1) E (ntire database), (2) U (sers), or (3) T (ables): (2) U> t -- the backup of the table is suspended here

Export table data (yes/no): yes>

Compress extents (yes/no): yes> -- the backup file is compressed by default.

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
Server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path...
Table (T) or Partition (T: P) to be exported: (RETURN to quit)> scott. emp -- Name of the Table to be exported, specifying the User Name of the Table

Current user changed to SCOTT
.. Exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Table (T) or Partition (T: P) to be exported: (RETURN to quit)>

Export terminated successfully with warnings.

The backup has been completed.

7. Use imp to import backup data

[Oracle @ dump] $ imp

Import: Release 10.2.0.1.0-Production on Wed May 16 07:07:21 2012

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options

Import file: expdat. dmp>/u01/empo52209.dmp -- specify the backup file to be imported and its path

IMP-00002: failed to open/u01/empo52209.dmp for read
Import file: expdat. dmp>/u01/dump/emp052209.dmp

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT: V10.02.01 via conventional path
Import done in US7ASCII character set and AL16UTF16 NCHAR character set
Import server uses AL32UTF8 character set (possible charset conversion)
List contents of import file only (yes/no): no>

Ignore create error due to object existence (yes/no): no> yes -- Ignore error prompt

Import grants (yes/no): yes>

Import table data (yes/no): yes>

Import entire export file (yes/no): no>
Username: scott -- user to which the table belongs

Enter table (T) or partition (T: P) names. Null list means all tables for user
Enter table (T) or partition (T: P) name or. if done:

. Importing SYS's objects into SYS
. Importing SCOTT's objects into SCOTT
.. Importing table "EMP" 14 rows imported
About to enable constraints...
Import terminated successfully with warnings.
[Oracle @ dump] $

This process is logical backup.

For more information about Oracle, see the Oracle topic page? Tid = 12