Oracle database backup and restore commands

Oracle database backup and restore commands

Note:

You have to have sufficient permissions, or it will prompt you if the permissions are not enough.

You can connect to the database by using tnsping TEST to see whether the database TEST can be connected.

(See Appendix)

Data output:

1 Export the database TEST completely, and export the username system and password 123456 to D:oracle.dmp

exp system/123456@orcl file=d:oracle.dmp full=y (full table)

2 Export the tables of system users and sys users in the database

exp system/123456@orcl file=d:oracle.dmp owner=(system,sys)

3 Export tables table1 and table2 in the database

exp system/123456@orcl file=d:oracle.dmp tables=(table1,table2)

4 Export the field filed1 in the table table1 in the database with the data starting with "00"

exp system/123456@orcl file=d:oracle.dmp tables=(table1)query=" where filed1 like '00%'"

The above are commonly used exports. I don't care much about compression. Dmp files can be compressed well with winzip.

But add compress=y after the above command.

Import of data

1 Import the data in D:oracle.dmp into the TEST database.

imp system/123456@orcl file=d:oracle.dmp

There may be a problem with the above, because some tables already exist, and then it reports an error and does not import the table.

Just add ignore=y at the end.

2 Import the table table1 in d:oracle.dmp

imp system/123456@orcl file=d:oracle.dmp tables=(table1)

Basically the above import and export are enough. In many cases, I delete the table completely and then import it.

Appendix I:

Add the operation of importing data permissions to users

First, start sql*puls

Second, log in as system/123456

Third, create user username IDENTIFIED BY password (if the user has already been created, this step can be omitted)

Fourth, GRANT CREATE USER, DROP USER, ALTER USER, CREATE ANY VIEW,

DROP ANY VIEW, EXP_FULL_DATABASE, IMP_FULL_DATABASE,

DBA, CONNECT, RESOURCE, CREATE SESSION TO user name

Fifth, run -cmd- to enter the directory where the dmp file is located,

imp userid=system/123456 full=y file=*.dmp

Or imp userid=system/123456 full=y file=filename.dmp

You can use Vinchin Backup & Recovery to protect Oracle databases (Windows Server 2008/2012/2016/2019 versions; Linux RHEL 6 to 8, CentOS 6 to 8, and Oracle Linux 6 to 8 versions) in both virtual machines and physical servers. The software automates the database backup and restore process after simple pre-set configurations, and you can make your own data protection plan with a lot of effective and advanced features. 60-day full-featured free trial is available to download.