The article came online, because the collection is not paying attention to the address, so apologizes the author apologizes ====
Import and export large Oracle databases with split DMP methods
I want to back up some records of a table, can you?
Oracle Data Import Export IMP / EXP
Oracle database has three standard backup methods
===================================================== 分Method Import and Export Large Oracle Database Exp DLYX / DLYX BUFFER = 10000000 File = (Full1.dmp, Full2.dmp, Full3.dmp, Full4.dmp, Full5.dmp) FileSize = 1000m Owner = (DLYX) log = full.logimp DLYX / DLYX BUFFER = 10000000 File = (Full1.dmp, Full2.dmp, Full3.dmp, Full4.dmp, Full5.dmp) fromuser = (DLYX) Touser = DLYX LOG = FULL.LOG ======== ============================================================================================================================================================================================================= == I want to back up some records of a table, can you? For example, the backup release time is: 2004-8-18 data $ express cai / cai @ Pixel file = aa.dmp tables = xxgl query = select * from xxgl where fbtime = 2004-8-18; I wrote this, I would like to ask What should I write? -------------------------------------------------- ------------- $ exp cai / cai @ Pixel file = aa.dmp tables = xxgl query = "" "" "" "" "" "" WINDOWS WHERE statement must Use 3 "" "before and after the statement =================================================== Oracle data import export IMP / eXP
Function: Oracle Data Import Export IMP / EXP is quite with Oracle data restore and backup. Most of the cases can be imported with Oracle data into backups and restores (not lost data). Oracle has a benefit, although your computer is not a server, but you have installed an Oracle client and established a connection (through Net8 Assistant -> service naming to add the correct service naming you can think of a client and server End repair the road, then the data can be pulled out) so you can export data to the local, although it is far from you. You can also import DMP files into the far database server from locally. With this feature that you can build two identical databases, one for testing, one is used to formally use. Execution Environment: You can execute in SQLPlus.exe or DOS (command line), during DOS can be executed in Oracle 8i to install directory / ORA81 / BIN is set to global path, this directory is exp.exe and Imp.exe Documents are used to perform import exports. Oracle is written with Java, I want sqlplus.exe, exp.exe, Imp.exe these two files are packaged after being packaged. SQLPlus.exe calls exp .exe, Imp.exe, the class they parked, complete the import guides. The following describes an example of importing exports. It is basically completed to the imported export to see the instance because the import export is simple. Data Export: 1 Export Database Test Table Export Exp System / Manager @ test file = d: /daochu.dmp Owner = (system, sys) 3 Export table table1, table2 in the database Exp System / Manager @ Test file = d: /daochu.dmp tables = (Table1, Table2) 4 Export Filed1 in the table table1 in the database exports exp system / manager @ test file = d: /daochu.dmp tables = D: /DAOCHU.DMP TABLES = (table1) query = / "Where filed1 like The top of '00% '/ "is a commonly used export. For compression, I don't care, using WinZip to compress the DMP file well. However, after the above command is added, it is possible to import the data in D: /DAOCHU.DMP into the TEST database. IMP System / Manager @ Test file = d: /daochu.dmp The top of the above may be a bit problem, because some tables already exist, then it is wrong, and the table is not imported. In the back, IGNORE = Y is OK. 2 Import the table Table1 in D: /Daochu.dmp into IMP System / Manager @ test file = d: /daochu.dmp tables = (Table1) Basically, the essential imported export is enough. Many case I was completely deleted the table and then imported. Note: You have enough permissions, and the permissions are not enough. It will prompt you. The database can be connected. You can use TNSPING TEST to get the database TEST can be connected.
================================ Oracle database has three standard backup methods, which are exported / imported (Exp / IMP), hot Backup and cold backups. Export spare parts is a logical backup, a cold backup and a hot backup is a physical backup.
First, export / import (export / import)
Using EXPORT to extract data from the database, you can send the extracted data back to the Oracle database using Import.
1. Simple export data (export) and import data (import)
Oracle supports the output of three ways:
(1), the table mode (T method) exports the data of the specified table. (2), user mode (U mode), export all objects and data of the specified user. (3), the full storage method (FULL mode), all objects in the database exported.
The process of data import (import) is the inverse process of the data export, which imports the data file into the database and exports the database data to the data file.
2, incremental export / import
Incremental export is a commonly used data backup method, which can only be implemented throughout the database and must be used as system. When performing such export, the system does not request an answer any questions. Export file name defaults to export.dmp, if you do not want your own output file to be named Export.dmp, you must point out the file name you want to use in the command line.
Incremental export includes three types:
(1), "complete" incremental export (Complete) is backup three databases, such as: exp system / manager incy = completion file = 040731.dmp (2), "incremental" increment export backup last backup Changed data, such as expression system file = 040731.dmp (3), "cumulative" incremental export cumulative export method is to export information that changes from the database after the last "complete" export. For example: Exp System / Manager IncTy = Cumulative File = 040731.dmp database administrator can schedule a backup schedule that is reasonably and efficiently completed by three different ways to export data. For example, the blind task of the database can be arranged as follows:
Monday: Fully Backup (a) Tuesday: Increment Export (B) Wednesday: Increment Export (C) Thursday: Increment Export (D) Friday: Accumulated Export (E) Saturday: Increment Export (f) Sunday: Increase Volume Export (G)
If the database is unexpectedly destroyed on Sunday, the database administrator can follow the steps to reply the database: Step 1: Use the command Create Database to regenerate the database structure; Step 2: Create a big enough rollback. Step 3: Full incremental import A: IMP system / manager incy = rest system / manager incy = restore full = y File = a Step 4: Accumulated incremental Import E: IMP System / Manager IncTy = Restore Full = Y File = E Step 5: Recent increment f: IMP system / manager incy = rest = y file = f
Second, cold backup
The cold backup occurs in the case where the database has been properly closed, and it will be given to us a complete database when properly turned off. A key file is copied to another position when a cold backup is in a cold backup. For backup Oracle information, the fastest and secure method when cold backup. The advantage of the cold backup is:
1. It is a very fast backup method (only copy file) 2, easy to archive (simple copy) 3, easy to recover to a point in time (just copy the file back) 4, can be connected to the archive method Combine, do the recovery of the "best state" of the database. 5, low maintenance, high security.
But the cold backup also has the following deficiencies:
1. When used alone, only the recovery of "a certain point in time" is available. 2. In the full process of the backup, the database must be prepared and cannot do other jobs. That is to say, in the cold backup process, the database must be closed. 3. If the disk space is limited, only the other external storage devices such as tape can be copied, and the speed will be very slow. 4, can not be restored in the form or by user.
If possible (mainly efficient), the information should be backed up to the disk, then start the database (can operate) and copy the backup information to the tape (copy, the database can work). Documents that must be copied in the cold backup include:
1, all data files 2, all control files 3, all online redo log files 4, init.ora file (optional)
It is worth noting that the cold backup must be performed in the case where the database is closed. When the database is open, the execution database file system backup is invalid.
The following is a complete example of a cold backup.
(1) Close the database SQLPLUS / NOLOG SQL> Connect / as Sysdba SQL> Shutdown Normal; (2) Back up all of the time files, redo log files, control files, initialize the parameter file SQL> CP (3) Restart Oracle Database SQL> STARTUP
Third, hot spare
Thermal backup is a method of backing up the database with the ArchiveLog Mode mode with the database operation. So, if you have a cold backup yesterday and today's hot backup documents, you can use this information to restore more information when there is a problem. The hot backup requires the database to operate in the ArchiveLog mode and require a lot of file space. Once the database is running in ArchiveLog, you can make a backup. The command file of the hot spare is consisting of three parts: 1. Data file a table space backup of a table space.
(1) Setting the table space for the backup status (2) Data file (3) reply table space for the backup table space is normal.
2. Backup archive log file
(1) Temporary Stop Archive Process (2) Log under the Archive Rede Log Directory (3) Restart the Archive Process (4) Backup Archive Redo Log File
3. The advantages of backing up the control file hot backup with the Alter Database Bachup ControlFile command are:
1. You can back up in a table space or database file level, and the backup time is short. 2. The database can still be used when backed up. 3. Can reach second level recovery (restored to a point in time). 4. You can recover almost all database entities. Recovery is fast, and in most cases, the database is still restored.
The lack of hot spare is:
1. Can't be wrong, otherwise the consequences are severe 2. If the hot backup is unsuccessful, the resulting results are not available for time point recovery 3. Because it is difficult to maintain, it is necessary to carefully and do not allow "failure to end".
