APTARE IT Analytics System Administrator Guide

Follow the steps to execute Data Pump Export in a Linux Environment

1. Login to the Linux database server and switch to user Aptare.
2. Ensure that file /opt/aptare/database/tools/expdp_scdb.par is owned by APTARE user and has 755 permissions.
3. Ensure Oracle listener and Oracle services are running.
4. Run following commands:

   su - aptare
   sqlplus / as sysdba
   alter session set container=scdb;

   Note:

   The alter session set container=scdb; command is required for a container database. Please ignore it for a non-cdb environment.

   CREATE OR REPLACE DIRECTORY datapump_dir AS '/tmp';

5. Export the database using following command:

   /opt/aptare/oracle/bin/expdp 
   parfile=/opt/aptare/database/tools/expdp_scdb.par

6. You can also choose to ignore the par file and include parameters in the expdp command directly. In other words, the above command can be replaced by the following command which can also be executed from aptare user.

   /opt/aptare/oracle/bin/expdp system/aptaresoftware@//localhost:1521/scdb 
   FULL=Y directory=datapump_dir dumpfile=aptare_scdb.exp 
   logfile= export_scdb.log CONTENT=ALL flashback_time=systimestamp

   After successful completion, the data pump export file aptare_scdb.exp is saved in /tmp directory of the Linux Database server.

7. This step is required only if the database is exported from an APTARE IT Analytics version of 10.5 or above.. Execute cp /opt/aptare/datarcvrconf/aptare.ks /tmp command to copy the aptare.ks file to /tmp folder

Follow the steps to execute Data Pump Import in a Linux Environment

1. Place the export file aptar_scdb.exp created using data pump export in /tmp directory.
2. Ensure the aptare_scdb.exp file is owned by the aptatre user and has 755 permissions.
3. Ensure that files /opt/aptare/database/tools/unlock_portal_linux.sql and /opt/aptare/database/tools/impdp_scdb.par are owned by aptare user and have 755 permissions.
4. Using root user Stop all Oracle and Aptare services by running following command: /opt/aptare/bin/aptare stop from root user.
5. Using root user start Oracle services by running following command: /opt/aptare/bin/oracle start
6. Ensure Oracle listener is running. Using aptare user check for status of Listener using following command: lsnrctl status
7. Run following commands:

   su - aptare
   sqlplus / as sysdba
   alter session set container=scdb;

   Note:

   The alter session set container=scdb; command is required for a container database. Please ignore it for a non-cdb environment.

   drop user aptare_ro cascade;

   drop user portal cascade;

   CREATE OR REPLACE DIRECTORY datapump_dir AS '/tmp';

8. Run the following command using aptare user

   /opt/aptare/oracle/bin/impdp 
   parfile=/opt/aptare/database/tools/impdp_scdb.par

9. You can also choose to ignore the par file and include parameters in the impdp command directly. In other words, the above command can be replaced by the following command which can also be executed from aptare user./opt/aptare/oracle/bin/impdp system/aptaresoftware@//localhost:1521/scdb schemas=portal,aptare_ro directory=datapump_dir dumpfile=aptare_scdb.exp logfile=import_scdb.log
10. When import completes in a non-CDB environment, remove first command 'alter session set container = scdb;' from the file unlock_portal_linux.sql and run following command from aptare user.

    Note:

    The removal of the 'alter session set container = scdb;' is required only for a non-CDB environment and no change is required if it is a Container database.

    sqlplus / as sysdba

    @/opt/aptare/database/tools/unlock_portal_linux.sql

11. After exiting from sqlplus, execute following command from aptare user

    sqlplus portal/portal@//localhost:1521/scdb

    @/opt/aptare/database/tools/validate_sp.sql

Follow the steps for Windows Data Pump Export

1. Login to the Windows database server.
2. Ensure Oracle TNS listener and Oracle services are running.
3. Ensure Aptare user has access to the file c:\opt\oracle\database\tools\expdp_scdb_win.par

   Run following commands:

   sqlplus system/aptaresoftware@//localhost:1521/scdb

   create or replace directory datapump_dir as 'c:\opt\oracle\logs';

   Exit

4. After exiting out of sqlplus, execute the following command: c:\opt\oracle\bin\expdp parfile=c:\opt\oracle\database\tools\expdp_scdb_win.par
5. You can also choose to ignore the par file and include parameters in the expdp command directly. In other words, the above command can be replaced by the following command: c:\opt\oracle\bin\expdp system/aptaresoftware@//localhost:1521/scdb FULL=Y DIRECTORY=datapump_dir LOGFILE=export_scdb.log DUMPFILE=aptare_scdb.exp CONTENT=ALL FLASHBACK_TIME=systimestamp
6. After successful completion, the data pump export file aptare_scdb.exp is saved in C:\opt\oracle\logs directory of the Windows Database server.

7. Copy file c:\opt\datarcvrconf\aptare.ks to c:\opt\oracle\logs folder.

   Note:

   This step is required only if database is exported from an APTARE IT Analytics version of 10.5 or above.

Follow the steps for Windows Data Pump Import

1. Login to the Windows database server.
2. The Aptare user will already have access to import files c:\opt\oracle\database\tools\unlock_portal_win.sql and c:\opt\oracle\database\tools\impdp_scdb_win.par. In case the Oracle user does not have read and execute privileges on these files, please ensure privileges are granted before starting the import.
3. Place the export file aptare_scdb.exp in c:\opt\oracle\logs directory
4. In case the name of the export file is capitalized, please change it to lower case. For example, change the name 'APTARE_SCDB.EXP' to 'aptare_scdb.exp'
5. Stop all Oracle and Aptare services using stopAllServices from windows services tab.
6. Start OracleServicescdb from windows services tab and ensure Oracle TNS listener is running.

   Run following commands:

   Sqlplus / as sysdba

   Alter session set container = scdb; (note this command is included only for a container database, otherwise switch to container database is not required)

   DROP USER aptare_ro CASCADE;

   DROP USER portal CASCADE;

   CREATE OR REPLACE DIRECTORY datapump_dir AS 'c:\opt\oracle\logs';

   EXIT;

7. After exiting out of sqlplus execute following command:

   c:\opt\oracle\bin\impdp parfile=c:\opt\oracle\database\tools\impdp_scdb_win.par

8. You can also choose to ignore the par file and include parameters in the impdp command directly. In other words, the above command can be replaced by the following command: c:\opt\oracle\bin\impdp "sys/*@//localhost:1521/scdb as sysdba" SCHEMAS=portal,aptare_ro DIRECTORY=datapump_dir LOGFILE=import_scdb.log DUMPFILE=aptare_scdb.exp
9. After import is complete, execute the following command: sqlplus "sys/*@//localhost:1521/scdb as sysdba" @c:\opt\oracle\database\tools\unlock_portal_win.sql
10. After exiting out of sqlplus, execute the following command: sqlplus portal/portal@//localhost:1521/scdb @c:\opt\oracle\database\tools\validate_sp.sql