Oracle logical Backup Export usage tips

There are three standard ways to back up an Oracle database: Export (export), offline backup, and online backup. The export method is a logical backup of the database, and the other two backup methods are physical file backups.
Logical backup

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

Backup Methods

(1) Find a Windows machine to install Oracle (if your Oracle is installed on Windows, there is no need to install another, this step saves). If you only have Oracle clients, it doesn't seem to automatically install the Exp tool, So you have to load the database server.
(2) Create a batch file for Windows Task Scheduler Execution (in Control Panel-Task Scheduler).
(3) Specify some parameters for the user and exp to export in the batch file. Bat:
Exp admin/admin@ora12   file=f:/every_week_backup/server12/ s12_evryweek.dmp    Owner=linmarkdev816,linmarkhk,linmarkinstall,linmarkuta816,nbdemo,saksdev, SAKSOWNER1227,SAKSOWNER2.6NP,CSPUAT2 consistent=y Log=f:/every_week_backup/server12/s12_evryweek_exp.log
(4) In the example above owner= is the user you want to export, you can enter according to your needs. Admin/amin@ora12 is a user with DBA authority (you can replace it with a system/password to enable it to export multiple user. Ora12 is the instance name of the database server that I want to export. This may be a server on another machine that you can set up in the client's $oraclehom/network/admin/tnsnames.ora to enable your client to connect to Oracle or directly via Oracle's network Configuration tool net Configuration Assistant to be connected) file= is your export file, that is, DMP file storage path.
(5) After editing a batch file, you can execute the file in the Windows Task plan (you can set the cycle execution, for example: if you want to back up every two days, the time is at 0-1, which you can open the task plan to set). Of course, you have to make sure that the scheduled computer does not shut down when the task is executed. In this way, each time the export task is started, Oracle override the DMP file, so you must also copy the previous DMP file and store it separately. The
Export backup with exp has three ways: full, user, and table.

Export Utility

Use of logical backups
First open the Start menu, select the Run command, pop the "Run" dialog box shown in Figure 1, enter CMD in the Open text box, and click OK.
　　
Figure 2 The Run dialog box
A DOS window pops up, under which you can use the EXP/IMP command to perform logical backup and recovery of the database in three ways:
* Interactive is after the direct input command, according to the system prompts step-by-step, as if in the same conversation with the system. No more examples are provided here.
* Command line mode is followed by some parameters and parameter values. An example is provided:

Figure 3 Command line mode export
    command line mode is a convenient way to operate if users are familiar with IMP and exp command parameters. It is best to use absolute paths for file names when making logical backups. If only the file name is given, the backup file will be saved in the current directory, making it more difficult to control and manage. The
   * parameter mode    is followed by the name of a parameter file in the command that holds some of the parameters required for the execution process to be invoked. The parameter pattern is actually to write the parameters that follow the command in a parameter file, and then use the command to bring a parameter that calls the file followed by parfile=. We can use the normal text file editor and create this file, and for obvious reasons, name the parameter file. The suffix of the parfile. The following is the contents of a parameter file:
        userid=scott/tiger@arjdb
         full=n
        buffer=10000
         file=dept. The DMP
        tables=dept
   execution process is as follows:
   
   Figure 4 Exporting in parametric mode
   the several modes and methods mentioned above also apply to IMP commands.
4. Application instance
   below, combine examples to specify how to import and export usage. In order to avoid data loss due to operational errors, first of all, the Scott user login system to create two tables for logical backup, Emp_dump table and Dept_dump table respectively.

Figure 5 Creating a logical backup table
After confirming that the table was successfully created, start the DOS command Line window and export the logical backup table that you just created, as shown in the figure. It is best to use absolute paths for file names when making logical backups, which can reduce the difficulty of control and management.

Figure 6 Exporting a logical backup table
To verify that the import work is correct, we use DML statements to promote all employees in the company as CEO, as follows:
　　
Figure 7 Updating the data in the table
You can use the SELECT statement to verify that the modification was successful. As shown in the figure.
　　
Figure 8 The updated table data
As can be seen from the display, all the employees in the company have been promoted to the CEO and the data has been updated successfully. Delete the two created tables after the confirmation is successful.
　　
Figure 9 Deleting a logical backup table
After using the SELECT statement to confirm that the two tables no longer exist, enter the import command in the DOS window as shown in the figure.
　　
Figure 10 Restoring a logical backup table
Returns the Scott user, using the SELECT statement to verify that the logical recovery has been successful.
　　
　　

The options and meanings of exp are as follows:

(1) Userid
The user name/password of the exported account is executed, and if this is the first argument after the exp command, the keyword userid is not specified.
(2) Buffer
Used to get the buffer dimensions of the data row, the default value varies with the system, and is typically set to a high value (greater than 64000).
(3) File
The name of the exported dump file.
(4) Filesize
The maximum size of an export dump file. If more than one file is listed in a file entry, the files are exported according to the FileSize set value.
(5) Compress
A y/n flag that specifies whether the export should compress fragment segments into a single area. This flag affects the storage clause that will be stored in the export file.
(6) Grants
A y/n flag that specifies whether permissions for database objects are exported.
(7) Indexes
A y/n flag that indicates whether the index on the table is exported.
(8) Rows
A y/n flag that indicates whether the row is exported. If set to N, only the DDL for database objects will be created in the export file.
(9) Constraints
A y/n flag that indicates whether constraints on the table are exported.
(Ten) Full
If set to Y, perform full database export.
(one) Owner
Export a list of database accounts that can perform user exports of these accounts.
(a) Tables
Export a list of tables to perform table export of these tables.
(RecordLength)
The length, in bytes, of the exported dump file record. The default value is used unless you are converting the exported file between different operating systems.
(Inctype)
The type of export to perform (the allowed values are complete (default), cumulative, and incremental).
(a) Direct
A y/n flag that indicates whether direct export is performed. Direct export bypasses buffers during export, which greatly increases the efficiency of export processing.
(a) record
For incremental export, this y/n flag indicates whether a record is stored in the data dictionary table that is exported by the record.
(Parfile)
A parameter file name passed to export that can contain all the parameter entries required by exp.
(km) Statistics
This parameter indicates whether the Analyze command for the exported object should be written to the export dump file. The valid values are compute, estimate (default) and N. In earlier versions of Oracle, this parameter was called analyze.
(consistent)
A y/n flag that indicates whether read-consistent versions of all exported objects should be preserved. This flag is required during export processing when the related tables are modified by the user.
(km) LOG
A file name to write the export log.
(Feedback)
The number of rows to display progress when the table is exported. The default value is 0, so no feedback is displayed until a table is exported.
(Point_in_time_recover)
A y/n flag to indicate to Oracle whether metadata is being exported for tablespace point-in-time recovery.
(recover_tablespaces)
Tablespace in which metadata should be exported during a tablespace point-in-time recovery.
(in) Query
The WHERE clause used for each table when exporting.
(tablespaces)
The tablespace of its metadata should be exported when a table space 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 extents, the COMPRESS = y option modifies the initial parameters of the S t o r a G e clause. Therefore, the total allocation space of the segment should be compressed into one area. Use this parameter to note two points: