HOW TO
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).
Below in the Solution is the information that must be provided and the workflow in which these tasks can be completed.
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.
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.
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.
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
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
sqlplus sys/sys@server/sid as sysdba
select * from dba_directories where directory_name = ‘DATA_PUMP_DIR’; Example: C:\app\oracle\admin\db_unique_name\dpump\
create directory ‘DATA_PUMP_DIR’ as 'C:\app\oracle\admin\DB_UNIQUE_NAME\dpump\';
quit;
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
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.
Get help from ArcGIS experts
Download the Esri Support App