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
Importing Ginesys Database
The import of the backup used to happen earlier using Ginesys import utility as a part of Ginesys Getting Started. The utility doesn't support IMPDP command and currently the utility is not functional. Moreover, we recommend to take help of Ginesys Care team for restoring (importing) Ginesys database backup on production environment. Users can attempt data import with the help of this article for there internal demonstration or testing environment.
Pre-requisites
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).
- 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.
Oracle login credential for SYSTEM user.
Privilege to set security of OS directory.
Alert
It is always recommended to check available disk space before starting the export utility. Ensure sufficient space in local storage on Database server. You may require at least 80% of the size of Oracle data folder (ORADATA).
Step-by-step guide
Exporting the database:
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_backupDirectory 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
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
- Then execute following statements sequentially. Replace placeholders (<>) as required
<BACKUP_PATH> - The directory path where the backup file will be createdStatement 1: Create database directory
Oracle Commandcreate 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';Statement 2: Grant permission
Oracle Commandgrant read,write on directory GSLBAK to system;
Statement 3 : Backup command
In this step we use SYSTEM user login to connect the database, we assume the password for SYSTEM is SYSPWDOracle Commandhost expdp system/SYSPWD DIRECTORY=GSLBAK DUMPFILE=GINESYS_DP.DMP LOGFILE=GINESYS_EXPDP.LOG EXCLUDE=STATISTICS REUSE_DUMPFILES=YES FULL=Y;
- 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 - Now the file is ready to be moved. File size will be be reduced significantly if file compression is used.
Guide on batch / script creation
For creation a batch file or script to automate the backup, you can mention the command in the batch or script.
Only the main backup command, Statement 3: Backup command under step 3 need to be mentioned in batch / script. All other commands can be run onetime to prepare the backup environment.
Importing the database:
- 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 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
- Then execute following statements sequentially. Replace placeholders (<>) as required
<RESTORE_PATH> - The directory path where the backup file placed for restore.Statement:1
Oracle Commandcreate 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';Statement:2
Oracle Commandgrant read,write on directory GSLBAK to system;
Statement:3
<USER_SCHEMA> - The Oracle user schema used for Ginesys data. Example: MNHO
Oracle Commandhost 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
- 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; 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: