Install Oracle Spatial in Oracle 11g
-------------------------------------------------------
Install Oracle Spatial in Oracle 11gCreate the (just the basic without any bells and whistles) database first.
To install Oracle Spatial, you need to have these below said database components/products already installed in the database:-
1) Java
2) XML database
3) Oracle Multimedia (In Pre-11g versions, its called interMedia)
Let us check if its already there:-
> select comp_id,version,status from dba_registry
where comp_id in ('JAVAVM','ORDIM','XDB');
no rows selected
It shows that these components are not installed.
1) Installing Java:-
Requirements for Java install: Check space availability for SYSTEM UNDO (minimum 100MB) and SYSTEM (minimum 70MB) tablespace, and memory settings for SHARED POOL (minimum 96MB) and JAVA POOL (minimum 50MB).
Run "initjvm.sql" to verify these requirements.
> col COMPONENT for a15
>select COMPONENT,CURRENT_SIZE/1024/1024 "CURRENT_MB",MIN_SIZE/1024/1024 "MIN_MB",
MAX_SIZE/1024/1024 "MAX_MB" from V$MEMORY_DYNAMIC_COMPONENTS where upper(COMPONENT) like '%POOL';
COMPONENT CURRENT_MB MIN_MB MAX_MB
--------------- ---------- ---------- ----------
shared pool 156 156 156
large pool 4 0 4
java pool 4 4 4
streams pool 0 0 0
Shared IO Pool 0 0 0
> ALTER SYSTEM SET JAVA_POOL_SIZE=50M;
System altered.
> select COMPONENT,CURRENT_SIZE/1024/1024 "CURRENT_MB",MIN_SIZE/1024/1024 "MIN_MB",
2 MAX_SIZE/1024/1024 "MAX_MB" from V$MEMORY_DYNAMIC_COMPONENTS where upper(COMPONENT) like '%POOL';
COMPONENT CURRENT_MB MIN_MB MAX_MB
--------------- ---------- ---------- ----------
shared pool 156 156 156
large pool 4 0 4
java pool 52 4 52
streams pool 0 0 0
Shared IO Pool 0 0 0
>SELECT TABLESPACE_NAME,BYTES/1024/1024 "BYTES_MB",MAXBYTES/1024/1024 "MAX_BYTES_MB"
FROM DBA_DATA_FILES WHERE TABLESPACE_NAME IN ('SYSTEM','UNDOTBS1');
TABLESPACE_NAME BYTES_MB MAX_BYTES_MB
------------------------------ ---------- ------------
SYSTEM 325 32767.9844
UNDOTBS1 200 32767.9844
> spool C:\full_jvminst.log;
>set echo on
>connect sys/*** as sysdba
>startup mount
>alter system set "_system_trig_enabled" = false scope=memory;
>alter database open;
>ALTER SYSTEM SET JAVA_POOL_SIZE=50M;
>@C:\oracle\ora11\javavm\install\initjvm.sql
>@C:\oracle\ora11\xdk\admin\initxml.sql
>@C:\oracle\ora11\xdk\admin\xmlja.sql
>@C:\oracle\ora11\RDBMS\ADMIN\catjava.sql
>@C:\oracle\ora11\RDBMS\ADMIN\catexf.sql
> select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
COUNT(*) OBJECT_TYPE
---------- ---------------
317 JAVA DATA
763 JAVA RESOURCE
19974 JAVA CLASS
>shutdown immediate
>set echo off
>spool off
>exit
2) Installing XML DB:-
Create a user and tablespace to store the XML DB components:-
> connect sys/*** as sysdba
> Shutdown Immediate;
> Startup;
> create tablespace XDB
datafile 'C:\ORACLE\ORADATA\ORA11\XDB1.DBF' size 50m autoextend on next 1m maxsize 4096m
extent management local
uniform size 1m
segment space management auto;
> create user XDB identified by XDB
default tablespace XDB
quota unlimited on XDB
temporary tablespace TEMP;
> @C:\oracle\ora11\RDBMS\ADMIN\catqm.sql XDB XDB TEMP
> @C:\Babu\4Project\GTO_PROJECT\Oracle_Spatial\Oracle_11g\xdbusagecheck.sql
> SELECT TABLESPACE_NAME,BYTES/1024/1024 "BYTES_MB",MAXBYTES/1024/1024 "MAX_BYTES_MB"
FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'XDB';
TABLESPACE_NAME BYTES_MB MAX_BYTES_MB
------------------------------ ---------- ------------
XDB 830 4096
@C:\oracle\ora11\RDBMS\ADMIN\utlrp.sql
> COL COMP_NAME FOR A30
> select comp_name, status, version from DBA_REGISTRY where comp_name= 'Oracle XML Database';
COMP_NAME STATUS VERSION
------------------------------ ----------- ------------------------------
Oracle XML Database VALID 11.1.0.6.0
3) Installing Oracle Multimedia:-
Create a tablespace to store the Oracle Spatial components (or SYSAUX default tablespace can be used) :-
> select version, status from dba_registry where comp_id='JAVAVM';
VERSION STATUS
------------------------------ -----------
11.1.0.6.0 VALID
> create tablespace ORA_SPATIAL
datafile 'C:\ORACLE\ORADATA\ORA11\ORA_SPATIAL1.DBF' size 50m autoextend on next 1m maxsize 4096m
extent management local
uniform size 1m
segment space management auto;
> @C:\oracle\ora11\ord\admin\ordinst.sql SYSAUX ORA_SPATIAL
> COL USERNAME FOR A20
> COL ACCOUNT_STATUS FOR A20
> COL DEFAULT_TABLESPACE FOR A20
> SELECT USERNAME,TO_CHAR(CREATED,'DD-MON-YY HH24:MI:SS') CREATED,ACCOUNT_STATUS,DEFAULT_TABLESPACE
FROM DBA_USERS ORDER BY 2;
USERNAME CREATED ACCOUNT_STATUS DEFAULT_TABLESPACE
-------------------- ------------------ -------------------- -------------------
SYS 03-JAN-08 10:08:44 OPEN SYSTEM
SYSTEM 03-JAN-08 10:08:44 OPEN SYSTEM
OUTLN 03-JAN-08 10:08:46 OPEN SYSTEM
DIP 03-JAN-08 10:11:02 EXPIRED & LOCKED USERS
TSMSYS 03-JAN-08 10:11:06 EXPIRED & LOCKED USERS
ORACLE_OCM 03-JAN-08 10:12:40 EXPIRED & LOCKED USERS
BABU 11-AUG-08 09:31:08 OPEN BABU
DBSNMP 23-JAN-08 13:30:18 EXPIRED & LOCKED SYSAUX
EXFSYS 29-OCT-09 13:26:45 EXPIRED & LOCKED SYSAUX
XS$NULL 29-OCT-09 13:51:16 OPEN USERS
ANONYMOUS 29-OCT-09 14:24:51 LOCKED XDB
XDB 29-OCT-09 14:40:29 OPEN XDB
ORDSYS 29-OCT-09 15:03:24 EXPIRED & LOCKED SYSAUX
ORDPLUGINS 29-OCT-09 15:03:24 EXPIRED & LOCKED SYSAUX
SI_INFORMTN_SCHEMA 29-OCT-09 15:03:24 EXPIRED & LOCKED SYSAUX
MDSYS 29-OCT-09 15:03:24 EXPIRED & LOCKED ORA_SPATIAL
16 rows selected.
> @C:\oracle\ora11\ord\im\admin\catim.sql
> execute validate_ordim;
> select version, status from dba_registry where comp_id='ORDIM';
VERSION STATUS
------------------------------ -----------
11.1.0.6.0 VALID
1 row selected.
4) Verify Install Oracle Spatial in Oracle 11g
> connect sys/*** as sysdba
> select comp_id,version,status from dba_registry
where comp_id in ('JAVAVM','ORDIM','XDB');
COMP_ID VERSION STATUS
---------- ---------- -----------
ORDIM 11.1.0.6.0 VALID
XDB 11.1.0.6.0 VALID
JAVAVM 11.1.0.6.0 VALID
3 rows selected.
> @C:\oracle\ora11\md\admin\mdprivs.sql
> @C:\oracle\ora11\md\admin\mdinst.sql
> set serveroutput on
> execute validate_sdo;
> col COMP_ID for a10
> col COMP_NAME for a10
> col CONTROL for a10
> col SCHEMA for a10
> col VERSION for a10
> select comp_id, control, schema, version, status, comp_name from dba_registry
where comp_id='SDO';
COMP_ID CONTROL SCHEMA VERSION STATUS COMP_NAME
---------- ---------- ---------- ---------- ----------- ----------
SDO SYS MDSYS 11.1.0.6.0 VALID Spatial
> select object_name, object_type, status from dba_objects
where owner='MDSYS' and status <> 'VALID'
order by object_name;
no rows selected
> select comp_id,version,status from dba_registry
where comp_id in ('JAVAVM','ORDIM','XDB','SDO');
COMP_ID VERSION STATUS
---------- ---------- -----------
SDO 11.1.0.6.0 VALID
ORDIM 11.1.0.6.0 VALID
XDB 11.1.0.6.0 VALID
JAVAVM 11.1.0.6.0 VALID
4 rows selected
