Pl/sql remote Backup and recovery Oracle database _oracle

Files that are remotely backed up on the client are saved on the host where the database resides and are not copied directly to the client.
——————————————————————————————————————————

First, whether your Oracle server is Linux or the Windows operating system, Oracle's backup and restore operations use Dbms_dump to implement import (backup) and export (Restore). First you have to install good pl/sql, use Pl/sql to execute the job that I provide below can realize.

First, Oracle Export (Backup)

1, connect the Oracle database server with PLSQL, use the user you need to export to connect the Oracle database server, perform the following statement backup:

declare H1 number;
   L1 VARCHAR2 (20); Begin H1: =dbms_datapump.open (Operation => ' EXPORT ', Job_mode => ' SCHEMA ', job_name => ' job_exp1 ', version =&G t; '
   Compatible ');
   Dbms_datapump.set_parallel (handle=> H1, Degree => 1); Dbms_datapump.add_file (handle=> h1, filename => ' Expdat.
   LOG ', directory => ' Data_pump_dir ', filetype => 3);
   Dbms_datapump.set_parameter (handle=> H1, name => ' Keep_master ', value => 0);
   Dbms_datapump.metadata_filter (handle=> H1, name => ' schema_expr ', Value => ' in (' ' chy ') '); Dbms_datapump.add_file (handle=> h1, filename => ' chydb.
   DMP ', directory => ' Data_pump_dir ', filetype => 1);
   Dbms_datapump.set_parameter (handle=> H1, name => ' Include_metadata ', value => 1);
   Dbms_datapump.set_parameter (handle=> H1, name => ' Data_access_method ', value => ' AUTOMATIC ');
   Dbms_datapump.set_parameter (handle=> H1, name => ' estimate ', value => ' BLOCKS '); Dbms_datapump.start_joB (handle=> H1, skip_current => 0, abort_step => 0);
   Dbms_datapump.wait_for_job (handle=> H1, job_state => L1);
  Dbms_datapump.detach (handle=> H1);


 End

2. Note:

A, ' in (' Chy ') ' Chy for Pl\sql login username, the user to be backed up, note in uppercase.
B, filename=> ' Chydb. DMP ' Chydb is the name of the DMP file specified for the backup, note in uppercase.

3, to be completed in Plsql, from the following query to obtain the path of backup files, and DMP files can be copied out

Copy Code code as follows:

Selectdirectory_path from Dba_directories wheredirectory_name= ' Data_pump_dir ';

4. Oracle's Export (Backup) is complete.

II. Oracle Import (Restore)
1, landing or remote to the Oracle server, the Oracle backup files (DMP files) copied to the specified path, the path to obtain the following SQL statement:

Copy Code code as follows:

SELECT directory_path fromdba_directories WHERE directory_name= ' Data_pump_dir ';

2, the Oracle server to create users, general use Oracle tool EM to create users

A, Oracle does not have the concept of a database entity, is "user", database users are equivalent to SQL Server database entities.
B, login Oracle's EM, use the SYS user login, SYS's role is SYSDBA.
C, the creation of users, "SA" as an example, the default table space generally choose "users", temporary table space generally choose temp (if you want to create the table space yourself, you can choose the table space you create).
D, Role page check, be sure to grant the user DBA role, select the DBA option in the Edit list to move to the right.
E, System Permissions page check, in the Edit list use "Move All", grant the user all permissions.
F, the remaining tab does not need to make any settings, click OK to prompt the user to create success.

3, with Pl\sql to connect Oracle Server, note the use of the 2nd step to create a user login Pl\sql connect Oracle Server

4, in the new Sqlwindow execute the following statement to import (restore)

 declare H1 number;
   L1 VARCHAR2 (20); Begin H1: =dbms_datapump.open (Operation => ' IMPORT ', Job_mode => ' SCHEMA ', job_name => ' job_exp1 ', version =& gt; '
    Compatible ');
    Dbms_datapump.set_parallel (Handle => h1,degree => 1); Dbms_datapump.add_file (Handle => H1, filename=> ' IMPORT.
    LOG ', directory => ' Data_pump_dir ', filetype =>3);
    Dbms_datapump.set_parameter (Handle => H1, name=> ' Keep_master ', value => 0); Dbms_datapump.add_file (Handle => H1, filename=> ' Chydb.
    DMP ', directory => ' Data_pump_dir ', filetype => 1);
    Dbms_datapump.metadata_filter (handle=> H1, name => ' schema_expr ', Value => ' in (' ' chy ') ');
    Dbms_datapump.set_parameter (handle=> H1, name => ' Include_metadata ', value => 1);
    Dbms_datapump.set_parameter (handle=> H1, name => ' Data_access_method ', value => ' AUTOMATIC ');
    Dbms_datapump.set_parameter (handle=> H1, name => ' skip_unusable_indexes ', value => 0); Dbms_Datapump.metadata_remap (handle=> h1,name => ' Remap_schema ', Old_value => ' chy ', value => ' SA '); Dbms_datapump.metadata_remap (handle=> h1,name => ' remap_tablespace ', Old_value => ' users ', Value => ' users '
    );
    Dbms_datapump.metadata_transform (handle=> h1,name => ' OID ', value => 0);
    Dbms_datapump.start_job (handle=> H1, skip_current => 0, abort_step => 0);
    Dbms_datapump.wait_for_job (handle=> H1, job_state => L1);
   Dbms_datapump.detach (handle=> H1);

 End

5, note: (All of the following will be capitalized)
A, the first red mark (' Chydb. DMP '), the name of the DMP file to copy to the Oracle server
B, second red flag (chy), DMP file user, so in the import (backup) Oracle user is required to note the username and the name of the table space
C, third red mark (chy), user of DMP file
D, fourth Red Flag (SA), the user you just created
E, fifth red mark (USERS), the name of the table space for the DMP file, which refers to the need to remember in B
F, sixth red Flag (users), the name of the default table space associated with the user you just created

6, to ensure that the above steps are correct, the implementation of import (recovery) statements, the successful implementation of Oracle Import (recovery).

7, in the process of doing the import, the 1th step mentioned in the path has an import log, after the import is completed to see what the error

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.