Oracle logical backup Export usage skills

This article will introduce you to the oracle logic backup Export usage skills, and hope you can learn more about Export.

There are three standard backup methods for an ORACLE database: EXPORT (EXPORT), offline backup, and online backup. The export method is logical backup of the database, and the other two backup methods are physical file backup.
Logical backup

Oracle export utility exp is used to read the database (including the data dictionary) and write the output to a binary file called the export dump file. You can export the entire database, specify a user, or specify a table. During the export process, you can choose whether to export table-related data dictionary information, such as permissions, indexes, and related constraints. The file written by exp includes the commands required to completely recreate all the selected objects.

Backup Method

(1) install oracle on a windows machine (If your oracle is installed on windows, you do not have to install it again. This step is saved ). if you only install the oracle client, it seems that the exp tool is not automatically installed, so you have to install the database server.
(2) create a batch file for windows Task Scheduling (in Control Panel-task plan ).
(3) In the batch file. bat, specify the user and exp parameters to be exported:
Exp admin/admin @ ora12 file = F:/Every_week_backup/server12/login owner = LINMARKDEV816, LINMARKHK, LINMARKINSTALL, LINMARKUTA816, NBDEMO, SAKSDEV, SAKSOWNER1227, callback, CSPUAT2 consistent = y log = F:/Every_week_backup/server12/s12_evryweek_exp.log
(4) In the above example, the owner = is followed by the user you want to export. You can enter the user according to your needs. Admin/amin @ ora12 is a user with dba permissions (you can replace it with the system/password so that it can export multiple users. Ora12 is the name of the database server I want to export. This may be the server on another machine. You can first go to the client's $ oraclehom/network/admin/tnsnames. set in ora to enable your client to connect to oracle, or directly configure the connection through the network Configuration tool Net Configuration Assistant of oracle) file = is the storage path of your exported file, that is, the dmp file.
(5) After editing the batch processing file, you can execute this file in the windows Task Plan (you can set the periodical execution. For example, if you want to back up the file every two days, the time is. You can set the task plan ). Of course, make sure that the computer with the task plan is not shut down during task execution. In this way, each time the export task is started, oracle will override the dmp file again, so you have to copy the previous dmp file and store it separately ).
There are three ways to export data using exp: Full, User, and Table.

Export Utility

Logical backup usage
First, open the Start menu and select the run command. The run dialog box shown in 1 is displayed. In the open text box, Enter cmd and click OK.
　　
Figure 2 "run" dialog box
A DOS window will pop up. In this window, you can use the EXP/IMP command to perform logical backup and recovery of the database. There are three methods:
* Interactive mode is performed step by step based on the system prompts after directly entering commands, as if it were in a dialog with the system. Here, we will not give an example.
* The command line mode is followed by some parameters and parameter values. Example:

Figure 3 Export in Command Line Mode
If you are familiar with IMP and EXP command parameters, the command line mode is a convenient operation method. Absolute paths are recommended for file names during logical backup. If only the file name is provided, the backup file will be saved in the current directory, which increases the difficulty of control and management.
* The parameter mode is followed by a parameter file name. This parameter file contains some parameters that need to be called during execution. The parameter mode is to write the parameters included after the command in a parameter file, and then use the command to include a parameter that calls the file. This parameter is PARFILE =. We can use a common text file editor and create this file. For the sake of clarity, name this parameter file as the suffix of. PARFILE. The following is the content of a parameter file:
USERID = SCOTT/TIGER @ ARJDB
FULL = N
Buffer= 10000
FILE = DEPT. DMP
TABLES = DEPT
The execution process is as follows:

Figure 4 Export in parameter Mode
The modes and methods mentioned above also apply to IMP commands.
4. Application Instance
The following describes how to import and export an instance. To avoid data loss due to operation errors, log on to the system as SCOTT and create two tables used for logical backup: emp_dump and dept_dump.

Figure 5 create a logical backup table
After confirming that the table is successfully created, start the doscommand line window and use the Export command to export the created logical backup table ,. Absolute paths are recommended for file names during logical backup, which reduces the difficulty of control and management.

Figure 6 export a logical backup table
To verify whether the import work is correct, we use DML statements to promote all employees in the company to the CEO. The specific operations are as follows:
　　
Figure 7 update table data
You can use the select statement to verify whether the modification is successful ..
　　
Figure 8 updated table data
The results show that all employees in the company have been promoted to the CEO, and the data has been updated successfully. After confirmation, delete the two created tables.
　　
Figure 9 delete a logical backup table
After the select statement is used to confirm that the two tables do not exist, enter the import command in the DOS window ,.
　　
Figure 10 restore a logical backup table
Return to the SCOTT user and use the select statement to verify whether the logical recovery has been successful.
　　
　　

The options and meanings of exp are as follows:

(1) Userid
The username/password for the account to be exported. If this is the first parameter after the exp command, you do not need to specify the keyword userid.
(2) Buffer
Used to obtain the buffer size of data rows. The default value varies with the system. It is usually set to a high value (greater than 64000 ).
(3) File
Name of the exported dump file.
(4) Filesize
The maximum size of an exported dump file. If multiple files are listed in the file entry, these files will be exported Based on The filesize setting value.
(5) Compress
A y/N flag to specify whether to compress the fragment into a single partition. This flag affects the storage clause that will be stored in the exported file.
(6) Grants
A y/N flag that specifies whether the database object permissions are exported.
(7) Indexes
A y/N flag that indicates whether the index on the table is exported.
(8) Rows
A y/N flag to indicate whether the row is exported. If it is set to N, only the DDL of the database object will be created in the export file.
(9) Constraints
A y/N flag that indicates whether the constraints on the table are exported.
(10) Full
If it is set to Y, execute FULL Database Export.
(11) owner
To export the list of database accounts, you can execute USER export for these accounts.
(12) Tables
Export the TABLE list. You can export the tables of these tables.
(13) Recordlength
The length of the exported dump file record, in bytes. The default value is used unless the file is converted and exported between different operating systems.
(14) Inctype
Export type to be executed (allowed values: complete (default), cumulative, and incremental ).
(15) Direct
Indicates whether to execute DIRECT export. DIRECT Export bypasses the buffer during export, which greatly improves the export processing efficiency.
(16) Record
Used for INCREMENTAL export. The Y/N mark indicates whether a record is stored in the data dictionary table exported from the record.
(17) Parfile
A parameter file name passed to EXPORT. This file can contain all the parameter entries required by exp.
(18) Statistics
This parameter indicates whether the export object's ANALYZE command should be written to the export dump file. The valid values are COMPUTE, ESTIMATE (default), and N. In earlier ORACLE versions, this parameter is called ANALYZE.
(19) consistent
A y/N flag to indicate whether to retain the consistent read version of all exported objects. This flag is required when the related table is modified during the EXPORT processing.
(20) Log
The file name of the log to be exported.
(21) Feedback
The number of rows in progress displayed during table export. The default value is 0, so no feedback is displayed before all tables are exported.
(22) point_in_time_recover
A y/N flag is used to indicate to ORACLE whether metadata for tablespace time point recovery is being exported.
(23) recover_tablespaces
The tablespace whose metadata should be exported when the tablespace time point is restored.
(24) Query
The where clause is used for each table during export.
(25) tablespaces
The tablespace whose metadata should be exported when a tablespace is moved.
The default value of the exp parameter in ORACLE8i is shown in table 8.1-1.
Use exp help = Y to display the parameters of the exp command.
1. Compress Parameters
For data segments that contain multiple zones, the COMPRESS = Y option can modify the initial parameter of the s t o r a g e clause. Therefore, the total allocated space of this section should be compressed into a single zone. Note the following when using this parameter: