Preface

The below procedure explains how to backup and restore DB from single-instance of Oracle. The steps are not applicable for Oracle RAC. All ST tables should be placed on the same DB.

The expdp and impdp are located in the ORACLE_HOME/bin folder and should be executed with the OS account which runs the Oracle DB. Information on how to find your ORACLE_HOME is available in Oracle's documentation.

Before executing any of the below steps, you MUST stop all SecureTransport services. Failure to do so will result in a corrupted backup.

The following placeholders should be replaced with their appropriate values:

ST_DMPDIR with the name of the folder defined to store the export, for example, ST_DUMP;
ST_DATABASE with the ST DB name;
ST_DATABASE_USER with the DB account which SecureTransport uses to connect to the DB;
PASSWORD with the password of the ST_DATABASE_USER;
SERVICE_ID with the instance ID, for example, orcl

Backup

B1. Configure directory to store the exported information

Login to the OS of the Oracle server and create the directory where the dumps will be exported. Make sure that the OS account which starts the Oracle has write permissions in that folder.

Log in into Oracle DB as SYSDBA and create the ST_DMPDIR directory using the following syntax:

CREATE DIRECTORY ST_DMPDIR AS '/YOUR/DIRECTORY/HERE';

Grant all privileges on the directory to the ST user

GRANT ALL PRIVILEGES ON DIRECTORY ST_DMPDIR TO ST_DATABASE_USER;

Grant create table privileges to the ST user

GRANT CREATE TABLE TO ST_DATABASE_USER;

B2. Dump the schema

expdp ST_DATABASE_USER/PASSWORD@SERVICE_ID schemas=ST_DATABASE directory=ST_DMPDIR dumpfile=ST_DATABASE_USER_schema_only.dmp logfile=expdp_ST_DATABASE_USER_schema_only.log CONTENT=metadata_only

B3. Dump the data

(a) Dump the data excluding the File Tracking and Server Log related tables:

expdp ST_DATABASE_USER/PASSWORD@SERVICE_ID schemas=ST_DATABASE directory=ST_DMPDIR dumpfile=ST_DATABASE_USER_data_excluding.dmp consistent=y  logfile=expdp_ST_DATABASE_USER_data_excluding.log content=DATA_ONLY EXCLUDE=TABLE:\"LIKE \'LOGGING_EVENT%\'\",EXCLUDE=TABLE:\"LIKE \'AUDITLOG%\'\",EXCLUDE=TABLE:\"LIKE \'SUBTRANSMISSIONSTATUS%\'\",EXCLUDE=TABLE:\"LIKE \'TRANSFERDATA%\'\",EXCLUDE=TABLE:\"LIKE \'TRANSFERDETAILS%\'\",EXCLUDE=TABLE:\"LIKE \'TRANSFERPROTOCOLCOMMANDS%\'\",EXCLUDE=TABLE:\"LIKE \'TRANSFERRESUBMITDATA%\'\"

(b) Dump the data with all tables:

expdp ST_DATABASE_USER/PASSWORD@SERVICE_ID schemas=ST_DATABASE directory=ST_DMPDIR dumpfile=ST_DATABASE_USER_all_data.dmp consistent=y  logfile=expdp_ST_DATABASE_USER_all_data.log content=DATA_ONLY

Restore

R1. Drop the current ST schema

Execute the below query with with SYSDBA permissions:

DROP USER ST_DATABASE_USER CASCADE;

R2. Create the new ST Schema with tablespace configurations

Execute the below queries with with SYSDBA permissions:

CREATE USER ST_DATABASE_USER IDENTIFIED BY PASSWORD quota 4G on USERS quota 4G on ST_FILETRACKING quota 4G on ST_SERVERLOG quota 4G on ST_DATA DEFAULT TABLESPACE ST_DATA;

GRANT create session, create table, create procedure, exp_full_database, imp_full_database to ST_DATABASE;

R3. Grant all privileges on the directory to the ST user

GRANT ALL PRIVILEGES ON DIRECTORY ST_DMPDIR TO ST_DATABASE_USER

R4. Import the exported schema from step B2

impdp ST_DATABASE_USER/PASSWORD@SERVICE_ID schemas=ST_DATABASE directory=ST_DMPDIR dumpfile=ST_DATABASE_USER_schema_only.dmp logfile=impdp_ST_DATABASE_USER_schema_only.log

R5. Disable the constraints

The script below should be executed with the ST Account (ST_DATABASE_USER):

begin
    FOR c IN
        (SELECT c.owner, c.table_name, c.constraint_name
        FROM user_constraints c, user_tables t
        WHERE c.table_name = t.table_name
        AND c.status = 'ENABLED'
        AND NOT (t.iot_type IS NOT NULL AND c.constraint_type = 'P')
        ORDER BY c.constraint_type DESC)
    LOOP
        dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name);
    END LOOP;
end;

R6. Import data

(a) Import the dump file which doesn't include File Tracking and Server Log related tables (taken in step B3-a):

impdp ST_DATABASE_USER/PASSWORD@SERVICE_ID schemas=ST_DATABASE directory=ST_DMPDIR dumpfile=ST_DATABASE_USER_data_excluding.dmp logfile=impdp_ST_DATABASE_USER_data_excluding.log

(b) Import the dump file with all the tables (taken in step B3-b):

impdp ST_DATABASE_USER/PASSWORD@SERVICE_ID schemas=ST_DATABASE directory=ST_DMPDIR dumpfile=ST_DATABASE_USER_all_data.dmp logfile=impdp_ST_DATABASE_USER_all_data.log

R7. Enable the constraints

The script below should be executed with the ST Account (ST_DATABASE_USER):

begin
    FOR c IN
        (SELECT c.owner, c.table_name, c.constraint_name
        FROM user_constraints c, user_tables t
        WHERE c.table_name = t.table_name
        AND c.status = 'DISABLED'
        ORDER BY c.constraint_type)
    LOOP
        dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name);
    END LOOP;
end;