Exp/imp three modes-complete, user, table

Exp/imp three modes-full, user, and table ORACLE databases have two backup methods. The first type is physical backup. This method enables complete database recovery, but the database must run in the recovery mode (the business database runs in the non-recovery mode ), this method requires a large number of external storage devices, such as tape libraries. The second type of backup mode is logical backup, which is used by the business database. This method does not require the database to run in the back-to-back mode, which is not only simple, in addition, external storage devices are not required. Database logical backup methods ORACLE database logical backup is divided into three modes: Table backup, user backup, and full backup. Basic syntax and example: 1. EXP: There are three main methods (FULL, user, table) 1. FULL: exp system/manager buffer = 64000 FILE = C: \ FULL. dmp full = Y back up the complete database. Business databases do not adopt this backup method. Backup command: exp icdmain/icd rows = y indexes = n compress = n buffer = 65536 feedback = 100000 full = y file = exp _. dmp log = exp. to export logs completely, you must have special permissions! Otherwise the error: EXP-00023: must be a DBA to do Full Database or Tablespace export 2, user mode: EXPSONIC/sonic buffer = 64000 FILE = C: \ SONIC. dmp owner = SONIC so that all the objects of the user SONIC are output to the file. Back up all objects in a user mode. Business databases usually adopt this backup method. If you back up data to a local file, run the following command: exp icdmain/icd owner = icdmain rows = y indexes = n compress = n buffer = 65536 feedback = 100000 file = exp. dmp log = exp. if the log is backed up directly to the tape device, run the following command: exp icdmain/icd owner = icdmain rows = y indexes = n compress = n buffer = 65536 feedback = 100000 volsize = 0 file =/dev/rmt0 log = exp. log Note: If the disk has space, we recommend that you back up the disk and copy it to the tape. If the database data volume is small, backup can be performed in this way. 3. Table mode: exp sonic/sonic buffer = 64000 FILE = C: \ SONIC. dmp owner = sonic tables = (SONIC) so that the user's SONIC table SONIC is exported to back up the specified object (table) in a user mode ). Business databases usually adopt this backup method. If you back up data to a local file, run the following command: exp icdmain/icd rows = y indexes = n compress = n buffer = 65536 feedback = 100000 volsize = 0 file = exp. dmp log = exp. if log tables = tab1, tab2, and tab3 are directly backed up to the tape device, run the following command: exp icdmain/icd rows = y indexes = n compress = n buffer = 65536 feedback = 100000 volsize = 0 file =/dev/rmt0 log = exp. log tables = tab1, tab2, tab3 Note: If the disk space is allowed, back up the data to the local server and then copy the data to the tape. For speed considerations, do not back up data directly to tape devices. 2. IMP: three modes are available (FULL, user, table). 1. FULL: imp system/manager buffer = 64000 FILE = C: \ FULL. dmp full = Y if the backup mode is FULL, use the following restoration methods: imp system/manager rows = y indexes = n commit = y buffer = 65536 feedback = 100000 ignore = y volsize = 0 full = y file = exp. dmp log = imp. log 2. User Mode: imp sonic/sonic buffer = 64000 FILE = C: \ SONIC. dmp fromuser = sonic touser = SONIC so that all the objects of the user's SONIC are imported into the file. You must specify the FROMUSER and TOUSER parameters to import data. This method will be restored Based on the Data backed up in user mode. 2.1. to restore all the backup data from a local file, run the following command: imp icdmain/icd fromuser = icdmain touser = icdmain rows = y indexes = n commit = y buffer = 65536 feedback = 100000 ignore = n file = exp. dmp log = imp. if the log is restored from the tape device, run the following command: imp icdmain/icd fromuser = icdmain touser = icdmain rows = y indexes = n commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file =/dev/rmt0 log = imp. log 2.2. if the specified table in the backup data is recovered from the local file, run the following command: imp icdmain/icd fromuser = icdmain t Ouser = icdmain rows = y indexes = n commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file = exp. dmp log = imp. log tables = t1, t2, t3; 3. Table mode: imp sonic/sonic buffer = 64000 FILE = C: \ SONIC. dmp owner = sonic tables = (SONIC) so that the SONIC table is imported. This method restores data backed up in Table mode. 3.1 restore all contents of the Backup Data imp icdmain/icd fromuser = icdmain touser = icdmain rows = y indexes = n commit = y buffer = 65536 feedback = 100000 ignore = n file = exp. dmp log = imp. if the log is restored from the tape device, run the following command: imp icdmain/icd fromuser = icdmain touser = icdmain rows = y indexes = n commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file =/dev/rmt0 log = imp. log 3.2 restores the specified table in the backup data. If the table is recovered from the local file, run the following command: imp icdmain/icd fromuser = icdmain touser = icdmain row. S = y indexes = n commit = y buffer = 65536 feedback = 100000 ignore = n file = exp. dmp log = imp. if log tables = t1, t2, and t3 are recovered from the tape device, run the following command: imp icdmain/icd fromuser = icdmain touser = icdmain rows = y indexes = n commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file =/dev/rmt0 log = imp. log tables = t1, t2, t3 3. Character Set Description: export end: the character set of the client should be set to view the character set of the database like the character set of the database: SQL> select userenv ('language ') from dual; USERENV ('language ')----------- Export AMERICAN_AMERICA.AL32UTF8 view the character set of the client (operating system): echo $ NLS_LANG modify the character set of the Client: Export NLS_LANG = AMERICAN_AMERICA.AL32UTF8 export the conversion process in the Export process, if the source database character set is inconsistent with the Export user session Character Set, Character Set conversion occurs, and the Export user session Character Set ID is stored in several bytes in the header of the exported file. Data may be lost during this conversion process. For example, if the source database uses ZHS16GBK and the Export user session Character Set uses US7ASCII, because ZHS16GBK is a 16-bit Character Set and US7ASCII is a 7-bit character set, chinese characters cannot find equivalent characters in US7ASCII, so all Chinese characters will be lost and become "?" In this way, the generated Dmp file has been lost. Therefore, if you want to Export the source database data correctly, the user session character set during the Export process should be equal to the source database character set or the superset import End of the source database character set: 1. character Set of the elasticsearch server; 2. character Set of oracle client; 3. the character set of the dmp file. During data import, the three character sets must be consistent before the data can be correctly imported. Import conversion process (1) determine the character set environment of the exported database. by reading the exported file header, you can obtain the character set settings of the exported file (2) determine the character set of the imported session, that is, the NLS_LANG environment variable used by the import Session (3) IMP reads the exported file and reads the exported file Character Set ID, and compares it with the NLS_LANG of the import process (4) if the exported file character set is the same as the imported Session character set, the conversion is not required in this step. If the conversion is different, the data needs to be converted to the character set used by the imported Session. It can be seen that two character set conversions occur during the process of importing data to the database for the first time: the conversion between the character set of the imported file and the character set used by the imported Session. If this conversion process cannot be completed correctly, the Import process from Import to the target database cannot be completed. Second: import the conversion between the Session Character Set and the database character set.