Oracle automatic backup and recovery

Source: Internet
Author: User

1. Automatic Backup

You can use the exp method (provided that you have the export permission) and write a script for the operating system. If you have a Windows system, you can write a BAT file and use exp to export it, then, add bat to the scheduled task.
Backup. bat

--CodeAs follows:
Echo off
Exp system/Sa @ orcl file = D: \ autobackup \ ies % date %. DMP log = D: \ autobackup \ ies % date % _ exp. log owner = (ies) buffer = 655000 compress = y



2. Automatic Recovery

Backup is performed on a regular basis, and recovery generally occurs only when recovery is required. Therefore, recovery is not added to the scheduled task,

However, for ease of operation, you can also create a. BAT file.

Two steps are generally required for recovery: 1. Start sqlplus, create tablespace and user, and launch sqlplus; 2. Import the backup. dmp file.

Recover. bat

-- The Code is as follows:

Sqlplusw system/Oracle @ sqlplus_cuser. SQL
Echo off
IMP system/Oracle @ Oracle fromuser = (ies) touser = (ies) buffer = 655000 ignore = y commit = y file = D: \ autorecover \ ies2010-11-23.dmp log = D: \ autorecover \ ies2010-11-23_imp.log



1. you need to manually perform the backup operation. the DMP file is placed in the specified directory (D: \ autorecover), and the file name is changed to the ies2010-11-23.dmp (or you can not change the file name, But recover. bat open it with notepad, change the ies2010-11-23.dmp to the corresponding file name)

2. sqlplus_cuser. SQL is used to create tablespaces and users.

-- The Code is as follows:

Spool cuser. Log
Connect system/Oracle @ ORACLE;
-- Delete a user --
Drop user ies cascade;
-- Delete a tablespace --
Drop tablespace iests including contents;
-- Create a tablespace --
Create tablespace iests datafile 'C: \ oracle \ product \ 10.2.0 \ oradata \ oracle \ iests. dbf' size 200 m reuse autoextend on next 50 m;
-- Create a user and authorize --
Create user ies identified by ies2010 default tablespace iests;

Grant resource, connect to IEs;
Grant select any dictionary to IEs;
Grant select any sequence to IEs;
Grant create any table, alter any table, drop any table to IEs;
Grant select any table, insert any table, update any table, delete any table to IEs;
Grant create any trigger, alter any trigger, drop any trigger to IEs;
Grant create any procedure, alter any procedure, drop any procedure, execute any procedure to IEs;
Grant create any view, drop any view to IEs;
Grant create any synonym to IEs;
Grant create any snapshot to IEs;

Spool off;


3. When the database is restored (imported), you must stop the web service. The database is not connected. Otherwise, an error is returned and cannot be deleted.
if the user is not deleted, only the tablespace is deleted, and the objects under the user cannot be deleted (the objects include function, procedure, synonym, package, javasource, javaclass, and so on ), likewise, objects cannot be imported.
function, procedure, synonym, package, javasource, javaclass, and type are all stored under SYS. Source $
the table is "dba_source" and "Source $.
If the Web service has been stopped, you must kill the process if the system prompts "the user is currently being connected and cannot be deleted" When deleting the user,
alter system kill session 'sid, serid # ';

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.