Backup and restore of Oracle database (local and remote operations)

Export of data

exp qh/[email protected]  file= ' d:\backup\qh\qh20060526.dmp '   grants=y  full=n  1 Fully export the database test, user Name System Password Manager export to D:\DAOCHU.DMP    exp system/[email protected] File=d:\daochu.dmp Full=y  2 Export the table of system user and SYS user in database    exp system/[email protected] File=d:\daochu.dmp owner= ( System,sys)  3 The table in the database table1, table2 export    exp system/[email protected] File=d:\daochu.dmp tables= ( Table1,table2)    4 the field in the table table1 in the database filed1 the data that starts with "00"    Exp system/[email protected] File =d:\daochu.dmp tables= (table1) query=\ "where filed1 like ' 00% ' \"

An example of a user of a database that guides all the tables of a user to another database

Exp Userid=system/manager owner=username1 file=expfile.dmp imp userid=system/manager fromuser=username1 touser= UserName2 Ignore=y File=expfile.dmp

There are two types of backup methods for Oracle databases. The first class is a physical backup, which implements a full database recovery, but the database must be run in the back-up mode (the business database runs in non-return mode) and requires a great deal of external storage devices such as a tape library, a second type of backup as a logical backup, a business database in this way, This method does not require that the database be run in the back-up mode, it is simple to backup, and can not require an external storage device.

table mode   back up the object (table) specified in a user mode. The business database typically uses this backup method.   If backing up to a local file, use the following command:  exp icdmain/icd rows=y indexes=n compress=n buffer=65536  feedback=100000 volsize=0& nbsp file=exp_icdmain_csd_yyyymmdd.dmp  log=exp_icdmain_csd_yyyymmdd.log  Tables=icdmain.commoninformation, icdmain.serviceinfo,icdmain.dealinfo  If you are backing up directly to a tape device, use the following command:  exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536  feedback=100000 volsize=0  file=/dev/rmt0  log=exp_icdmain_csd_yyyymmdd.log  tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo  Note: In case of disk space permitting, you should back up to the local server before copying to tape. For speed reasons, try not to back up directly to the tape device.

user mode   back up all objects in a user mode. The business database typically uses this backup method.   If backing up to a local file, use the following command:  exp icdmain/icd owner=icdmain rows=y indexes=n compress=n buffer=65536  feedback= 100000 volsize=0  file=exp_icdmain_yyyymmdd.dmp  log=exp_icdmain_yyyymmdd.log  If you are backing up directly to a tape device, use 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_icdmain_yyyymmdd.log  Note: If the disk has space, it is recommended to back up to disk and then copy to tape. If the amount of database data is small, this method can be used for backup.

full mode   Back up the complete database. The business database does not use this backup method. Backup command for:  exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536  feedback=100000 volsize=0 full=y  file= EXP_FULLDB_YYYYMMDD.DMP (tape device is/dev/rmt0)   log=exp_fulldb_yyyymmdd.log  for database backups, an incremental backup is recommended, This is to back up only the data that was changed since the last backup. Incremental backup   Command:  exp icdmain/icd rows=y indexes=n compress=n buffer=65536  feedback=100000 volsize=0 full=y inct ype=incremental  File=exp_fulldb_yyyymmdd.dmp (tape device is/dev/rmt0)   log=exp_fulldb_yyyymmdd.log  Note: For incremental backups, the following conditions must be met:  1. Only valid for full database backups, and the first time you need to full=y parameters, you need to inctype=incremental parameters later.   2. The user must have a exp_full_database system role.   3. Database backup can be used when the traffic volume is small.   4. If the disk has space, it is recommended that you back up to disk, and then back up to tape.   Business Database backup method and period   before backing up with Exp, run the Catexp.sql file under the SYS user (do not execute this script if the file has been previously run).   No special instructions are allowed to perform backup commands on the client.

The backup command refers to the backup command in table mode.   Backup from disk files to tape   If you first back up to a local disk file, you need to dump to the tape device.   1. To view the tape devices configured on the host, use the following command:  lsdev-cc tape  displays the results as shown in the following example:  rmt0 Available 30-58-00-2,0 SCSI 4mm Tape drive  RMT1 Defined 30-58-00-0,0 SCSI 4mm Tape drive  The device marked available is an available tape device.   2. To view the contents of a tape store, use the following command:  tar-tvf/dev/rmt0  displays the results shown in the following example: -rw-r--r--8089600 Jan one 14:33:57 2001 Exp_i cdmain_20010111.dmp  if the display resembles the following, the backup data for that tape store is backed up from the database directly to tape, not the backup file from the local disk to tape, so the operating system is not recognized.   tar:0511-193 An error occurred while reading from the media.  there was an input or output error.  or &nbsp ; tar:0511-169 A Directory checksum error on media; -267331077 equal to 25626.  3. For new tapes or tapes that do not need to retain existing data, use the following command:  tar-cvf/dev/rmt0 exp_icdmain_yyyymmdd.dmp  Note: A. This command will unconditionally overwrite the existing data on the tape.   B. File names do not allow path information, such as:/backup/exp_icdmain_yyyymmdd.dmp.   4. For tapes that need to retain existing data, use the following command:  tar-rvf/dev/rmt0 exp_icdmain_yyyymmdd.dmp  Note: This command exp_icdmain_yyyymmdd.dmp the fileAdded to the end of the tape and does not overwrite the existing data.   Special emphasis: If the backup is backed up directly from the database to tape, no additional files can be copied to the tape or the backup data is invalidated.   5. To copy the backup file dumped to the tape to the local hard disk, use the following command:  A. Copy all files on the tape to the current directory on the local hard disk   tar-xvf/dev/rmt0  B. Copy the specified file on the tape to the current directory on the local hard disk. nbsp Tar-xvf/dev/rmt0 exp_icdmain_yyyymmdd.dmp  Backup Schedule   Due to the large impact on system I/O during backup, it is recommended that you perform backup work after 11 o'clock in the evening.   Business Database Oracle version recovery, recovery scenarios are determined based on the backup scenario. Due to the combination of table backup and user backup in the business database, the recovery of the business database needs to be based on the actual scenario of table recovery and user recovery.

recovery plan   Database logical recovery is divided into table recovery, user recovery, full recovery of three modes.

Table mode   This method will be restored based on data backed up in table mode.   A. Restore the full contents of the backup data   if recovering from a local file, use 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=exp_icdmain_cs  d_yyyymmdd.dmp  log=imp_icdmain_csd_yyyymmdd.log  if recovering from a tape device, use 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_icdmain_csd_ yyyymmdd.log  B. Restoring the specified table in the backup data   if recovering from a local file, use 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=exp_icdmain_cs  d_ yyyymmdd.dmp  log=imp_icdmain_csd_yyyymmdd.log  tables=commoninformation,serviceinfo  If recovering from a tape device, Use 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_icdmain_csd_yyyymmdd.log  Tables=commoninformation,serviceinfo

User mode   This method will be restored based on data backed up in user mode.   A. Restore the full contents of the backup data   if recovering from a local file, use 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=exp_icdmain_yy  yymmdd.dmp  log= imp_icdmain_yyyymmdd.log  if recovering from a tape device, use 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_icdmain_yyyymmdd.log   B. Restore the specified table in the backup data   if recovering from a local file, use 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=exp_icdmain_yy  yymmdd.dmp  log= imp_icdmain_yyyymmdd.log  tables=commoninformation,serviceinfo  if recovering from a tape device, use 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_ICDMAIN_YYYYMMDD.Log  Tables=commoninformation,serviceinfo

full mode   If the backup method is full mode, use the following recovery method:  if recovering from a local file,:  imp system/manager rows=y indexes=n commit=y buffer= using the following command 65536  feedback=100000 ignore=y volsize=0 full=y  file=exp_icdmain_yyyymmdd.dmp  log=imp_icdmain_ yyyymmdd.log  if recovering from a tape device, use the following command:  imp system/manager rows=y indexes=n commit=y buffer=65536  feedback= 100000 ignore=y volsize=0 full=y  file=/dev/rmt0  log=imp_icdmain_yyyymmdd.log  parameter description   1. Ignore parameters   Oracle during the recovery of the data, when a table is restored, the table already exists, depending on the settings of the Ignore parameter to determine what to do.   If Ignore=y,oracle does not execute the CREATE TABLE statement, inserts the data directly into the table, and if the inserted record violates the constraint, such as a primary key constraint, the record of the error is not inserted, but the valid record is added to the table.   If Ignore=n,oracle does not execute the CREATE TABLE statement and does not insert data into the table, it ignores the table error and resumes the next table.   2. Indexes parameter   in the process of recovering data, if indexes=n, the index on the table will not be restored, but the unique index corresponding to the primary key will be restored unconditionally, in order to ensure the integrity of the data.

Character set conversions for single-byte character sets (for example, Us7ascii), when recovering, the database is automatically converted to the session's character set (Nls_lang parameter), and for multibyte character sets (for example, zhs16cgb231280), the character set should be the same as possible (avoid conversions) if you want to convert , the character set of the target database should be a superset of the output database character set.

Recovery method The business database uses a table recovery scheme. Before recovering with IMP, run the Catexp.sql file under the SYS user (if the file was previously run, do not execute the script), and then execute the following command: IMP icdmain/icd file= filename log=log filename rows=y commit= Y buffer=y ignore=y tables= table name Note: The table name to be restored references the table name of the backup. Recovery is the accumulation of data on the basis of the original table. No special instructions, do not allow recovery command on client

Appendix I: To the user to increase the Import data permission operation first, start sql*puls second, to System/manager landing third, create user username identified by password (if you have already created a user, this step can be omitted) IV, GRAN T Create User,drop user,alter USER, create any VIEW, DROP any view,exp_full_database,imp_full_database, dba,conn Ect,resource,create SESSION to User name V, run-cmd-into the directory where the DMP file is located, imp userid=system/manager full=y file=*.dmp or by Imp Userid=system/manager full=y file=filename.dmp

Example execution: F:\work\oracle_data\backup>imp userid=test/test full=y file=inner_notify.dmp

Screen display import:release 8.1.7.0.0-production on Thu February 16:50:05 2006 (c) Copyright: Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition release 8.1.7.0.0-production with the partitioning option Jserver release 8.1.7.0.0-p Roduction

exporting files created by export:v08.01.07 by a regular path has completed the ZHS16GBK character set and the import export server in the ZHS16GBK NCHAR character set uses the UTF8 NCHAR character set (possible Ncharset conversions). Importing Aichannel objects into Aichannel. . Importing Table                   " Inner_notify "          4 lines are imported ready to enable constraints ... The import was terminated successfully, but a warning appears.

  Appendix II:  oracle does not allow direct changes to the table owner, the use of export/import can achieve this purpose.   First set up Import9.par,   Then, use the command as follows: Imp parfile=/filepath/import9.par   Example Import9.par content is as follows:          fromuser=tgpms                  touser=tgpms2     (Note: Change the owner of the table from Fromuser to Touser, Fromuser and Touser users can be different)                      rows=y         indexes=y          grants=y         constraints=y          buffer=409600         file==/backup/ctgpc_20030623.dmp         Log==/backup/import_20030623.log Oracle Services in a Windows environment Note: SID-database identity      home_name-oracle HOME name, such as OraHome92, OraHome81 (1) Oracleservicesid                          database service, this service automatically starts and stops the database. If a database is installed, its default startup type is automatic. Service process is ORACLE.EXE, parameter file initsid.ora, log file SIDALRT.log, console SVRMGRL.EXE, SQLPLUS. Exe.

(2) Oraclehome_nametnslistener Listener service, the service only needs when the database needs remote access (whether through a different host or local through the Sql*net network protocol is remote access), without this service can access the local database, Its default startup type is automatic. Service process is TNSLSNR.EXE, parameter file listener.ora, log file Listener.log, console LSNRCTL.EXE, default port 1521, 1526.

(3) Oraclehome_nameagent OEM Agent service, receiving and responding to task and event requests from OEM consoles, only required when using the OEM Management database, and its default startup type is automatic. The service process is DBSNMP.EXE, the parameter file Snmp_rw.ora, the log file Nmi.log, the console LSNRCTL.EXE, and the default port 1748.

(4) Oraclehome_nameclientcache name Caching service, the service caches the Oracle Names data used to connect to the remote database. Its default startup type is manual. However, there is no need to run this service unless there is an Oracle Names server. The service process is ONRSD.EXE, parameter file names.ora, log file ONRSD.LOG, console NAMESCTL.EXE.

(5) Oraclehome_namecmadmin Connection Management Service, is used to build the Connection Manager server, only the server as Connection Manager needs, its default startup type is Manual. The service process is CMADMIN.EXE, parameter file cman.ora, log file cmadm_pid. TRC, console CMCTL.EXE, default port 1830.

(6) Oraclehome_namecman Connection Gateway Service, is used to build Connection Manager server, only the server as Connection Manager needs, its default startup type is manual. The service process is CMGW.EXE, parameter file cman.ora, log file cman_pid. TRC, console CMCTL.EXE, default port 1630.

(7) Oraclehome_namedatagatherer performance packet data acquisition service, unless you use Oracle capacity Planner and Oracle performance Manager, you do not need to start, its default startup type is manual. The service process is VPPDC.EXE, log file Alert_dg.log, console Vppcntl.exe.

(8) Oraclehome_namehttpserver the Web server provided by Oracle, in general we only use it to access Web pages in the Oracle Apache directory, such as JSP or Modplsql pages. Unless you use it as your HTTP service, you do not need to start it (if you start it will take over the IIS service), its default startup type is manual. The service process is APACHE.EXE, the parameter file is httpd.conf, and the default port is 80.

(9) Oraclehome_namepagingserver by a digital pager or email with a modem (not tried), its default startup type is manual. Service process PAGNTSRV.EXE, log file Paging.log.

Oraclehome_namenames Oracle Names Service, only the server is required as a names server, its default startup type is manual. The service process NAMES.EXE, parameter file names.ora, log file NAMES.LOG, console NAMESCTL.EXE, default port 1575.

(one) Oraclesnmppeermasteragent SNMP Service agent for SNMP-enabled network management software to manage the server, unless you use the Webmaster tools to monitor the situation of the database, or do not need to start, its default startup type is manual. The service process is AGNTSVC.EXE, the parameter file is Master.cfg, and the default port is 161.

Oraclesnmppeerencapsulater SNMP Protocol Encapsulation Service for SNMP protocol conversion, unless you use an incompatible SNMP agent service, it does not need to be started, its default startup type is manual. The service process is ENCSVC.EXE, the parameter file is Encaps.cfg, and the default port is 1161.

Oraclehome_namemanagementserver OEM Management Service, the default startup type is manual when you use OEMs. The service process is OMSNTSVR.EXE, and the log file oms.nohup.

Reprint Address: http://blog.csdn.net/sishuiyunyan/article/details/52460052

Backup and restore of Oracle database (local and remote operations)