How To: Backup and Restore Ginesys database (Oracle) manually using Data Pump Export and Import

The Database Backup utility in Ginesys HO helps users to take regular backup of Ginesys database. The utility uses the EXPDP command which is an in-built utility of Oracle database. In Ginesys Database Backup utility it takes export of selective schemas which are essential for standard functionality of Ginesys.

This article is for advanced usage to backup database without using the Ginesys Database Backup utility. The purpose is like to take a complete database backup during planned or emergency migration, preparing backup script for scheduled backup etc. This article describes how we can take backup of Ginesys database without using the Ginesys Database Backup utility. Additionally the guide also describes the process for import the database on different database.

One can read the original article from Oracle on EXPDP and IMPDP commands for detailed understanding on Oracle commands. The article can be found here at Oracle Data Pump

Pre-requisites

1. Oracle database must be of Oracle 10g or above. Steps are tested with Oracle 10g (10.2.0.4.0) and Oracle 12c (12.1.0.2.0).

2. A directory on Database server. In this article the backup destination will be created on Database server only. The directory name convention varies for Windows-based and Linux-based database server.

3. Oracle login credential for SYSTEM user.

4. Privilege to set security of OS directory.

Step-by-step guide

Exporting the database:

1. Create a folder on local storage of Database server for expdp dump and give appropriate permission. Ensure Oracle database have access to directory to generate backup file.

   Example:

   On Windows server:
   E:\HO_Backup
   
   On Linux server:
   /oracle/ho_backup

   Directory path convension

   Windows-based Server

   In Windows we create directories within logical volume and we use backslash (\) for mentioning a directory path. For an example we create a directory for Ginesys Database backup is E:\HO_Backup

   Linux-based Server

   In Linux we use forward slash (/) for mentioning a directory path. For an example we create a directory for Ginesys Database backup is /oracle/ho_backup

2. Open a command-prompt or terminal and connect database using SQLPLUS with SYS user. Ensure correct version of SQLPLUS from from correct Oracle home based on the database version is running. We assume the database instance name as GINESYS and the password of SYS as SYSPWD

   Command

   SQPLUS SYS/SYSPWD@GINESYS as SYSDBA

3. Then execute following statements sequentially. Replace placeholders (<>) as required
   
   <BACKUP_PATH>  - The directory path where the backup file will be created

   
   

   1. Statement 1: Create database directory
      
      

      Oracle Command

      create or replace directory GSLBAK as '<BACKUP_PATH>';

      
      Example:

      On Windows server:
      create or replace directory GSLBAK as 'E:\HO_Backup';

      On Linux server:
      create or replace directory GSLBAK as '/oracle/ho_backup';
      
      

   2. Statement 2: Grant permission
      
      

      Oracle Command

      grant read,write on directory GSLBAK to system;

   3. Statement 3 : Backup command

      
      In this step we use SYSTEM user login to connect the database, we assume the password for SYSTEM is SYSPWD
      Oracle Command

      host expdp system/SYSPWD DIRECTORY=GSLBAK DUMPFILE=GINESYS_DP.DMP LOGFILE=GINESYS_EXPDP.LOG EXCLUDE=STATISTICS REUSE_DUMPFILES=YES FULL=Y;

4. Wait to complete process and Oracle to generate the dump file. A confirmation line can be seen in command window like, Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at ...
   Dump file GINESYS_DP.DMP should be generated in the location specified in above steps (mentioned in step 1). Also export log found there, named GINESYS_EXPDP.log
   
   
5. Now the file is ready to be moved. File size will be be reduced significantly if file compression is used.

Importing the database:

1. Place the dump in a folder on local storage of destination Database server.
   

   Example:

   On Windows server:
   D:\HO_Restore
   
   On Linux server:
   /oracle/ho_restore
   

   
   

2. Open a command-prompt or terminal and connect database using SQLPLUS with SYS user. Ensure correct version of SQLPLUS from from correct Oracle home based on the database version is running. We assume the destination database instance name as GINESYS and the password of SYS as SYSPWD

   Command

   SQPLUS SYS/SYSPWD@GINESYS as SYSDBA

3. Then execute following statements sequentially. Replace placeholders (<>) as required
   
   <RESTORE_PATH>  - The directory path where the backup file placed for restore.
   
   

   1. Statement:1
      
      

      Oracle Command

      create or replace directory GSLBAK as '<RESTORE_PATH>';

      
      Example:

      On Windows server:
      create or replace directory GSLBAK as 'D:\HO_Restore';

      On Linux server:
      create or replace directory GSLBAK as '/oracle/ho_restore';
      
      

   2. Statement:2
      
      

      Oracle Command

      grant read,write on directory GSLBAK to system;

   3. Statement:3 
      
      

      <USER_SCHEMA> - The Oracle user schema used for Ginesys data. Example: MNHO
      
      

      Oracle Command

      host impdp system/SYSPWD DIRECTORY=GSLBAK DUMPFILE=GINESYS_DP.DMP LOGFILE=GINESYS_IMPDP.LOG SCHEMAS=<USER_SCHEMA>,GATEWAY,GINVIEW,GINSSOT,GINAPPS,GINBASE,MISRETAIL,GINARCHIVE

      Example:

      
      host impdp system/SYSPWD DIRECTORY=GSLBAK DUMPFILE=GINESYS_DP.DMP LOGFILE=GINESYS_IMPDP.LOG SCHEMAS=MNHO,GATEWAY,GINVIEW,GINSSOT,GINAPPS,GINBASE,MISRETAIL,GINARCHIVE
      
      
4. Wait to complete the import. Then execute following statements. (Replace schema name <USER_SCHEMA> appropriately)
   
   GRANT SELECT ON SYS.V_$SESSION TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.V_$MYSTAT TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_SEQUENCES TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_TABLES TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_TAB_COLUMNS TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_CONSTRAINTS TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_CONS_COLUMNS TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_INDEXES TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_DATA_FILES TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_USERS TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_IND_COLUMNS TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_COL_COMMENTS TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_TRIGGERS TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_VIEWS TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_SOURCE TO <USER_SCHEMA>;
   GRANT SELECT ON SYS.DBA_JOBS TO <USER_SCHEMA>;
   Grant execute on dbms_crypto to public;
   Grant execute on dbms_sqlhash to public;
   ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
   alter system set "_optimizer_enable_extended_stats" = false;
   ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
   
   

5. Done. Import log GINESYS_IMPDP.log file can be found at specified location.
   
   

   There are some post-restore configuration required to start Ginesys with this restored database. Please get in touch with GInesys Care to take assistance with post-restore configuration.

Reference:

Oracle Data Pump