Month: February 2014

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;

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

 

Advertisement

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.

Controlling Accessing Oracle Database using TCP.VALIDNODE_CHECKING

arcsdegeoLeave a comment

you can control which client can access your database through the parameter TCP.VALIDNODE_CHECKING

to configure this feature, follow the following steps:

under $ORACLE_HOME/network/admin/sqlnet.ora file add the parameter

TCP.VALIDNODE_CHECKING=ON

you can use the ‘vi’ editor if you are using Unix environment, to add the previous line.

Then, you Either specify the nodes you want them to be allowed OR the nodes that are NOT allowed (excluded):

TCP.INVITED_NODES= {machineA}     

*** here machineA are the only machine allowied to access the database

TCP.EXCLUDED_NODES={machineB}

** here machineB is denied to access the database

you can use them both lines at the same time, but the precedence will be for TCP.INVITED

after adding the required lines under sqlnet.ora either “Reload the listener” OR “stop/start” the listener.

commands used:

lsnrctl reload listenter_db

or

lsnrctl stop listenter_db

then

lsnrctl start listenter_db

******** When an attempt to access the database through sqlplus, the following oracle error is thrown(ORA-12537: TNS:connection closed) for non-authorized client nodes.

Important Remark: valid node can be bypassed through IP spoofing however, you can overcome this by enabling ip spoofing protection at kernel level in your Operating System.