Year: 2014

connecting using ‘SYS’ user from client machine

arcsdegeo1 Comment

in order to connect using ‘SYS’ account from your local machine to the Oracle database, you need to follow the following steps:

1. create a password file, using the command:

orapwd file=orapwsid  password=xxxxx entries=Z

**IMPORTANT REMARKS**:

this command should be executed under the directory: $ORACLE_HOME/dbs where your init.ora is located

orapwsid: where you need to append the database SID in the name, which is a standrd conversion naming for the password file that must be used.

Z: represents the number of oracle users that needs to be granted the SYSDBA privilege

just type the following command and it will list for you the parameters in details: orapwd

2. edit the init.ora file under $ORACLE_HOME/dbs

change the parameter “remote_login_passwordfile” to “exclusive”

SQL> show parameter password

NAME TYPE VALUE
—————————– ———– ———-
remote_login_passwordfile string EXCLUSIVE

3. grant ‘sysdba’ privilege to the oracle account you are seeking.

 

for example,

SQL> grant sysdba to db_user;

 

ORA-39070: Unable to open the log file ORA-06512: at “SYS.UTL_FILE”, line 536

arcsdegeoLeave a comment

if you receive the following error message:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning option

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-06512: at “SYS.UTL_FILE”, line 536

ORA-29283: invalid file operation

**** Resolution:

Create a database directory that has been grante privilege access to the user who is performing “expdp” operation.

SQL> create or replace directory DB_EXPORT as ‘/oracle/export/db/;

Directory created.

SQL> grant read, write on directory DB_EXPORT to expdp_user;

Grant succeeded.

 

Where expdp_user could be ‘system’ user, or any user that has ‘DBA’ role privilege.

Example of expdp command:

expdp expdp_user /XXXX directory=DB_EXPORT logfile=db_test.log dumpfile=EXDP_TEST.dmp schemas=HR

Oracle Database XBD instillation,de-instillation, verification

arcsdegeoLeave a comment

***The following is being referenced by Oracle Doc ID 1292089.1 ******

//========================================================================//

** XDB Removal and Reinstall **

XDB Removal

The catnoqm.sql script drops XDB.

spool xdb_removal.log
set echo on;
connect / as sysdba
shutdown immediate;
startup
@?/rdbms/admin/catnoqm.sql
spool off;

Some XDB related objects in the SYS schema are not dropped during the removal of XDB. Also, the SYS.KU$_% views will become invalid. Please see the following document for cleaning up these objects:

(Doc ID 1269470.1) XDB Deinstallation script catnoqm.sql leads to Invalid SYS Objects

** XDB Installation **

The catqm.sql script requires the following parameters be passed to it when run:

A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSAUX, SYSTEM, UNDO and TEMP can be specified.
The specified tablespace must already exist prior to running the script.
Sample tablespace creation:
create tablespace xdb datafile ‘<$ORACLE_BASE>/oradata/<SID>/xdb01.dbf’ size 350M autoextend on segment space management auto; )
C. XDB user temporary tablespace
D. YES or NO
(If YES is specified, the XDB repository will use SecureFile storage.
If NO is specified, LOBS will be used.
To use SecureFiles, compatibility must be set to 11.2.
The tablespace specified for the XDB repository must be using
Automatic Segment Space Management (ASSM) for SecureFiles to be used.)

Therefore the syntax to run catqm.sql is the following:
SQL> catqm.sql A B C D

For Example:
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP YES

## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##

spool xdb_install.log
set echo on;
connect / as sysdba
shutdown immediate;
startup;
@?/rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs> <XDB temporary tbs> <YES or NO> — substitute the parameters with appropriate values
@?/rdbms/admin/utlrp.sql
spool off

 —————————————————————————————————————-

** XDB in Oracle 12c is Mandatory **

Oracle XML DB is now a mandatory component of Oracle Database. You cannot uninstall it, and if Oracle XML DB is not already installed in your database prior to an upgrade to Oracle Database 12c Release 1 (12.1.0.1) or later, then it is automatically installed in tablespace SYSAUX during the upgrade. If Oracle XML DB has thus been automatically installed, and if you want to use Oracle XML DB, then, after the upgrade operation, you must set the database compatibility to at least 12.1.0.1. If the compatibility is less than 12.1.0.1 then an error is raised when you try to use Oracle XML DB.

 

Verify XDB Installation

spool xdb_status.txt

set echo on;
connect / as sysdba
set pagesize 1000
col comp_name format a36
col version format a12
col status format a8
col owner format a12
col object_name format a35
col name format a25

— Check status of XDB

select comp_name, version, status
from dba_registry
where comp_id = ‘XDB’;

— Check for invalid objects

select owner, object_name, object_type, status
from dba_objects
where status = ‘INVALID’
and owner in (‘SYS’, ‘XDB’);

spool off;

//=========================================================================//

 

Oracle Spatial General Information

arcsdegeoLeave a comment

The following is a “general” information about Oracle Spatial to give the reader a brief overview, and links to useful reading resources:

1. the difference between Oracle Locator and Oracle Spatial:

Oracle Locator: a feature of Oracle Database (Standard and Enterprise Editions), provides core location functionality needed by most customer applications and partner solutions. (Locator is not a solution for complex GIS applications .)  Developers can extend existing Oracle-based applications, since with Locator they can easily incorporate location information directly in their applications and services. This is possible because location data is fully integrated in the Oracle server itself. Geographic and location data are manipulated using the same semantics applied to the CHAR, DATE or INTEGER types that are familiar to all users of SQL.

 

Oracle Spatial: an option to Oracle Database Enterprise Edition, augments Locator, and provides a robust foundation for complex GIS applications which require more spatial analysis and processing in Oracle Database. It includes spatial functions (including area, buffer, centroid calculations), advanced coordinate systems support, linear referencing system, and aggregate functions. Significant new capabilities in this release address the challenging business-critical requirements from the public sector, defense, logistics, energy exploration, and business geographics domains.

 

2. to check if oracle spatial component is “installed” and “valid” in your database use the following query: SELECT COMP_NAME,VERSION,STATUS FROM DBA_REGISTRY;

 

3. when you have spatial data stored in your database, you can view it using “Oracle Fusion Middleware MapViewer“:http://www.oracle.com/technetwork/middleware/mapviewer/overview/index.html

 

OR

you can integrate your Oracle Spatial data with ESRI ArcGIS software and view the data in their ArcGIS Desktop , this requires registering the oracle spatial data with ArcGIS Geodatabase, this paper is very good paper to read: http://download.oracle.com/otndocs/products/spatial/pdf/spatial_wp09_bestprac.pdf

 

 

shrinking (resizing) undo tablespace in oracle

arcsdegeo1 Comment

To shrink (re-size) undo tablespace in Oracle DBMS, please follow the following steps:

1. Create a temporary tablespace for swapping

CREATE undo TABLESPACE undotbs_2 DATAFILE
‘/orad001/data_files/undotbs2_02.dbf’ size 10000M;

2. Use it as default UNDO tablespace

ALTER SYSTEM SET undo_tablespace=undotbs_2;

3. Drop the old UNDO tablespace

DROP TABLESPACE UNDOTBS1 including contents;

****** if you face an error that you can’t drop the undo tablespace then you need to check the sessions that requires (commit or rollback).

you may face the error “Undo Tablespace X moved to Pending Switch-Out state”

To Fix this use the following query to check the session,oracleuser, program that requires either a commit transaction or a rollback.

**********************
SELECT
a.usn,
a.name,
b.status,
c.tablespace_name,
d.addr,
e.sid,
e.serial#,
e.username,
e.program,
e.machine,
e.osuser
FROM
v$rollname a,
v$rollstat b,
dba_rollback_segs c,
v$transaction d,
v$session e
WHERE
a.usn=b.usn AND
a.name=c.segment_name AND
a.usn=d.xidusn AND
d.addr=e.taddr AND
b.status=’PENDING OFFLINE’;
**************************

4. Create back the original UNDO tablespace

CREATE undo TABLESPACE UNDOTBS1 DATAFILE
‘/orad001/data_files/undo01.dbf’ size 5000M REUSE,
‘/orad001/data_files/undo02.dbf’ size 5000M REUSE;

5. Set the default UNDO tablespace back to the original one

ALTER SYSTEM SET undo_tablespace=UNDOTBS1;

6. Drop the temporary one

DROP TABLESPACE undotbs2 including contents and datafiles;

7. do verification using this command:

SQL> show parameter undo_tablespace

 

 

the case of st_geometry in Enterprise Geodatabase design

arcsdegeoLeave a comment

st_geometry is one of ESRI Geodatabase storage format for Oracle database environment.

This geometry has the following highlights:

  • st_geometry is the “default” geometry storage for creating an enterprise geodatabase in Oracle DB.

 

  • If you migrated your storage from sdelob to stgeometry you’ll need to recreate your multiversion view because the structure is different.

 

  • Drop the users using the ST_GEOMETRY, before dropping the SDE user, not the other way around.Otherwise you will face ORA-21700: object does not exist or is marked for delete and as a consequence your Oracle instance is corrupted.

 

  • User defined data types such as (st_geometry) has issues with export/import database dump process in oracle.

Remark: some of these highlights are my “own” technical observation, and some are referenced by ESRI technical documentation.