HOW TO

How To Take an Oracle Database Backup when requested by Esri Technical Support

Last Published: January 14, 2025

Summary

While working with Esri Technical Support, there may be times when an Oracle Database backup is requested by the Support Analyst. The Oracle Database backup may be needed for troubleshooting the issue in your Support case.

Oracle Database backups can be taken in various ways. When they are requested by Esri Technical Support, the Oracle Data Pump Export (EXPDP) utility is used to create a dump file (.DMP).

Cause

Below in the Solution is the information that must be provided and the workflow in which these tasks can be completed. 

Procedure

Provide the following information regarding the Oracle Database server machine

  • Operating System and version of the server machine the Oracle Database is installed on:
    • For example: Windows 10 Enterprise 64-bit, Red Hat Enterprise Linux Server 8
  • Oracle Database version:
SELECT * FROM V$VERSION;
Note:
You may run this query in SQL*Plus when connected to the Oracle database as SYS to determine this version.
  • Enterprise Geodatabase version:
SELECT * FROM SDE.VERSION;
Note:
You may run this query in SQL*Plus when connected to the Oracle database as SYS or SDE to determine this version.
  • Are you using ST_Geometry? Based on the Operating system below, provide the version of the st_shapelib.dll or libst_shapefil.so library.
    • Windows OS
      • st_shapelib.dll:
    • Linux or Unix OS
      • libst_shapefil.so:

Execute the Get Oracle Information script and provide the resulting text file

The Get Oracle Information script (get-orainfo.sql), gathers the necessary information, (Users, Roles, Privileges, Data Owners, Tablespaces and Datafiles), to prepare for restoring an Oracle database within a different environment. This script writes the output results to C:\TEMP\ORA_Info_Results.txt. Once, the file has been generated, please provide the ORA_Info_Results.txt file to your Esri Support Services Analyst.

  1. Copy the below script and save it locally on your machine as: get-ora-backup-info.sql
  2. In SQL*Plus, connected as the SYS user, execute the get-ora-backup-info.sql script.
Note:
To execute a script file in SQL*Plus, type @ and then the file name.  See the following examples below for a variety of input options:
Example 1: If the file was called script.sql, and located in the current directory, you would type at the SQL prompt:
@script.sql

Example 2: If the file is located in a different directory, add the path prior to the file name.
@/oracle/scripts/script.sql
  1. Provide this output ORA_Info_Results.txt file to the Esri Support Services analyst.
clear screen

PROMPT
PROMPT

PROMPT ********************************************************************************
PROMPT * Script to gather information about an Oracle instance for backup and restore *
PROMPT ********************************************************************************
PROMPT
PROMPT

sho user

PROMPT
PROMPT

PROMPT This script needs to be executed as the SYS or SYSTEM user, or with a user that has been assigned the DBA role!!
PROMPT
PROMPT
PAUSE  Press CTRL+C to quit, or press any key to continue ...
PROMPT

CLEAR BREAKS COMPUTES COLUMNS

host mkdir C:\TEMP

spool C:\TEMP\ORA_Backup_Info_Results.txt replace

set linesize 130 pages 500

col member for a80
col "Filesize in MBs" for 9,999,999.99

PROMPT
PROMPT
PROMPT


PROMPT *********************************************
PROMPT * INFORMATION GATHERED WILL BE USED BY ESRI *
PROMPT *  SUPPORT TO RESTORE THIS ORACLE DATABASE  *   
PROMPT *********************************************   


PROMPT
PROMPT
PROMPT


PROMPT ***********************************
PROMPT * SID, ORA VERSION, HOSTNAME INFO *   
PROMPT ***********************************   
   
col "HOST NAME" for a20
select instance_name, version "ORA VERSION", substr(host_name,1,30) "HOST NAME", active_state "STATE", archiver "ARCHIVE" 
from v$instance;
   

PROMPT
PROMPT
PROMPT

PROMPT *******************
PROMPT * TABLESPACE INFO *
PROMPT *******************

COL c1 heading "Tablespace Name" FORMAT A25
COL c2 heading "Used MB"    	 FORMAT 99,999,999
COL c3 heading "Free MB"    	 FORMAT 99,999,999
COL c4 heading "Total MB"   	 FORMAT 99,999,999 

break on report

compute sum of c2 on report
compute sum of c3 on report
compute sum of c4 on report

SELECT
   fs.tablespace_name              c1,
   (df.totalspace - fs.freespace)  c2,
   fs.freespace                    c3,
   df.totalspace                   c4,
   round(100 * (fs.freespace / df.totalspace)) "% Free"
FROM (select distinct tablespace_name, round(sum(bytes) / 1048576) TotalSpace
     from dba_data_files group by tablespace_name) df,
     (select tablespace_name, round(sum(bytes) / 1048576) FreeSpace
     from dba_free_space group by tablespace_name) fs     
WHERE df.tablespace_name = fs.tablespace_name
ORDER BY 1; 

PROMPT
PROMPT
PROMPT


/* PROMPT **************************
PROMPT * TABLESPACE EXTENT INFO *
PROMPT **************************


select tablespace_name, block_size, segment_space_management, initial_extent "INITIAL_EXT", next_extent "NEXT_EXT",      extent_management "EXT_MGMT", allocation_type "ALLO_TYPE"
from dba_tablespaces 
order by 1;


PROMPT
PROMPT
PROMPT */


PROMPT *************************************************
PROMPT * Non ORACLE GENERATED TABLESPACE METADATA INFO *
PROMPT *************************************************

set long 9999999

select dbms_metadata.get_ddl('TABLESPACE',tbsp.tablespace_name) 
from dba_tablespaces tbsp
where tablespace_name not in('SYSTEM','SYSAUX','UNDOTBS1','TEMP','USERS');

PROMPT
PROMPT
PROMPT


/* PROMPT **********************
PROMPT * USER METADATA INFO *
PROMPT **********************
PROMPT
PROMPT

select dbms_metadata.get_ddl('USER',usr.username) 
from dba_users usr;


PROMPT
PROMPT
PROMPT */

PROMPT *************
PROMPT * USER INFO *
PROMPT *************

PROMPT
PROMPT
PROMPT

PROMPT
PROMPT All non-Oracle maintained users in the database
PROMPT ===============================================

col username for a30
col default_tablespace for a20
col temp_tablespace for a20
col profile for a20
col account_status for a20

select username,substr(default_tablespace,1,30) default_tablespace, substr(temporary_tablespace,1,20) temp_tablespace, substr(profile,1,20) profile, substr(account_status,1,20) account_status 
from dba_users 
where oracle_maintained = 'N'
order by 1;


PROMPT
PROMPT
PROMPT Users that own data in SDE
PROMPT ==========================
PROMPT
PROMPT
PROMPT SDE Layers
PROMPT -----------

col owner for a20
select owner,count(*) from sde.layers group by owner order by 1;

PROMPT
PROMPT
PROMPT SDE Tables
PROMPT -----------

col owner for a20
select owner,count(*) from sde.table_registry group by owner order by 1;

PROMPT
PROMPT
PROMPT ST_Geometry Layers
PROMPT -------------------

col owner for a20
select owner,count(*) from sde.st_geometry_columns group by owner order by 1;


PROMPT
PROMPT
PROMPT

PROMPT Number of Columns in SDE Tables
PROMPT --------------------------------

col tablename for a60

select owner||'.'||table_name tablename, count(*) NumColumns from sde.column_registry group by owner||'.'||table_name order by 2 desc ;


PROMPT
PROMPT
PROMPT


PROMPT ************************
PROMPT * USER PRIVILEGES INFO *
PROMPT ************************

PROMPT
PROMPT Privileges assigned to users that own data in ArcSDE
PROMPT ====================================================

col username for a20
col PRIVILEGES_ASSIGNED for a30

break on username skip 1

select grantee username, granted_role PRIVILEGES_ASSIGNED
from dba_role_privs
where grantee in (select distinct owner from sde.table_registry
                  union
                  select distinct owner from sde.st_geometry_columns
                  union
                  select distinct owner from sde.layers)
union
select grantee "USERNAME", privilege "PRIVILEGES ASSIGNED"
from dba_sys_privs
where grantee in (select distinct owner from sde.table_registry
                  union
                  select distinct owner from sde.st_geometry_columns
                  union
                  select distinct owner from sde.layers)
order by 1;

PROMPT
PROMPT
PROMPT

PROMPT
PROMPT ********************
PROMPT * SDE VERSION INFO *
PROMPT ********************


select major,minor,bugfix,description  from sde.version;
 

PROMPT
PROMPT
PROMPT

spool off

host notepad C:\TEMP\ORA_Backup_Info_Results.txt

    Use the Data Pump Export (EXPDP) utility to create a dump file

    1. As the SYS user, using SQL*Plus, connect to the Oracle instance to generate Oracle export file. Below is an example of how the format is used to connect in SQL*Plus.
    sqlplus sys/sys@server/sid as sysdba
    1. Verify if a directory exists to write the Oracle dump file to. The directory name by default is ‘DATA_PUMP_DIR’. This directory is where the Oracle dump file(s) are written to. The directory name is ‘DATA_PUMP_DIR’. You may use the following query to determine the directory path.
    select * from dba_directories where directory_name = ‘DATA_PUMP_DIR’;

Example: C:\app\oracle\admin\db_unique_name\dpump\
    1. If a directory does not already exist, create one.  Use the following example as a guide, updating the name and path for your environment.  This example creates a directory called DATA_PUMP_DIR.  The Oracle dump file(s) will be written to subdirectories within Oracle's Admin Directory, ORACLE_BASE\admin\DB_UNIQUE_NAME.
    create directory ‘DATA_PUMP_DIR’ as 'C:\app\oracle\admin\DB_UNIQUE_NAME\dpump\';
    1. Use the following command to exit SQL*Plus.
    quit;
    1. Open Command Prompt on this machine and generate a full Oracle dump file using the following command with the Data Pump Export (EXPDP) utility.
    expdp user_name FULL=y DUMPFILE=expdat.dmp DIRECTORY=data_pump_dir LOGFILE=export.log
    Note (Optional):
To export individual schemas (sde.dmp, gis.dmp, etc...) use the syntax below.
    expdp user_name DUMPFILE=expdat.dmp DIRECTORY=data_pump_dir LOGFILE=export.log schemas=SDE

    Provide the Oracle Dump File(s) (.DMP) file to your Esri Technical Support Analyst.

    • Now that the Oracle Dump File(s) have been generated, upload these along with your log files to the secure location provided by your Esri Technical Support Analyst. If you have not, received this File Transfer Site, reach out to the Esri Technical Support Analyst you are working with on your case.
    • To ensure that there are no issues with data corruption upon upload, please check the size of the data uploaded to the File Transfer Site to the size that you have locally on your machine.
    Note:
Consult with your Organization's Database Administrator or IT and refer to Oracle documentation for assistance with the Oracle Data Pump Export (EXPDP) utility.

    Article ID: 000026410

    Get support with AI

    Resolve your issue quickly with the Esri Support AI Chatbot.

    Start chatting now

    Discover more on this topic

    Esri Community

    Search for related information

    Training

    Find training related to this topic

    ArcGIS Ideas

    Explore ideas and give feedback

    Get help from ArcGIS experts

    Contact technical support

    Start chatting now

    Go to download options