How can you export and import Oracle database contains aSISt scheme to another server?

To export the database:

1. After starting the sql client, e.g.:

  • <CMD> sqlplus /nolog
  • <SQL> connect system/pass as sysdba;

2. Create a new directory in the Oracle database to which the export will be performed and grant it the required permissions

  • <SQL> CREATE DIRECTORY asistdmpdir AS ‘C:\asistdmpdir’;
  • SQL> grant read, write on directory asistdmpdir to asist5;

3. Check if the directory for the ORA engine has been created correctly:

  • <SQL> SELECT directory_name, directory_path FROM dba_directories;

4. Perform database export:

  • expdp asist5/hasło_użytkownika DIRECTORY=asistdmpdir DUMPFILE=asist.dmp  LOGFILE=asist_export.log

To import the database:

1. Create a new PDB database, to which the schema will be imported:

  • SQL> create pluggable database asistpdb admin user asistdba identified by asist file_name_convert=(‘ D:\app\oracle\oradata\orcl\pdbseed’,’D:\app\oracle\oradata\orcl\asistpdb’);
  • SQL> alter pluggable database asistpdb open read write;
  • SQL> alter session set container=asistpdb;
  • SQL> alter pluggable database asistpdb save state instances=all;

2. Create a new directory for import and grant it the required permissions:

  • CREATE DIRECTORY asistdmpdir AS ‘C:\asistdmpdir’; 
  • SQL> grant read, write on directory asistdmpdir to system;

3. Create a user and tablespace:

  • SQL> create tablespace users datafile ‘D:\app\oracle\oradata\orcl\asistpdb/asist01.dbf’ size 1G autoextend on;
  • SQL> create user asist5 identified by asist5 default tablespace users;
  • SQL> grant connect, resource, create any view, unlimited tablespace to asist5;

4. In the tnsnames.ora file, add an entry about the plug-in database:

ASISTPDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = serwer)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = asistpdb)

    )

  )

An example file location is: D:\app\oracle\product\12.2.0\dbhome_1\network\admin

5. Perform an import from the system user:

  • impdp system/system@asistpdb directory=asistdmpdir logfile=imp.log dumpfile=asist.dmp

Additional parameters for the import command:

remap_schema – to use when changing the target schema, syntax: oldUser:newUser np. remap_schema=asist5:asis

remap_tablespace – to use when changing target user space, syntax: oldTablespace_newTablespace np. remap_tablespace=users:asistdata

Link to a helpful article (import syntax a bit simpler):

http://orafaq.com/wiki/Datapump

For the aSISt application to connect to the new Oracle database, change the database address in the db.properties file by entering:
  • db.url=jdbc\:oracle\:thin\:@serwer\:1521/asistpdb