Post on 05-Jul-2018
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
1/18
TAREAS PREVIAS A LAS
PRÁCTICAS CON ORACLE
SPATIALOracle 12c y Pre 12c, Single-Tenant y No Container
MTI Francisco Javier Rojas Duránfrancisco.javier.rojas@gmail.com
Descripción breveEl presente documento describe las tareas necesarias para poder llevar a cabo las prácticas con
Oracle Spatial, el documento se desarrolló en idioma Inglés
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
2/18
MTI FRANCISCO JAVIER ROJAS DURÁN 1
Table of ContentsReviews of this document ............................................................................................................... 1
Previous tasks to perform before start practicing with Oracle Spatial Training material .................. 2
1. Change in the .bash_profile * ................................................................................................. 2
2. Creating an after startup trigger (Only if you have a Multi-Tenant Container DB)* .............. 2
Steps ............................................................................................................................................ 3
3. Configuring DB parameters, (this applies to Container and Non-Container DB)* .................. 3
This is the expected output: ............................................................................................................ 4
4. Create additional database tablespaces (applies to Container and Non-Container DB)
Mandatory ...................................................................................................................................... 4
Option A. Using a Single-Tenant instance: .................................................................................. 5
Option B . Using a Non-container Single Instance (Pre-12c): ...................................................... 5
5. Create the Oracle database users and grant appropriate privileges. Mandatory .................. 5
6. Connecting to the instance with student user ........................................................................ 6
7. Configuring Services to allow external connections to the Pluggable DB............................... 7
Steps ............................................................................................................................................ 8
Reviews of this documentVersion Author Date
1.0 MTI Francisco Javier Rojas Durán April 2nd 2016
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
3/18
MTI FRANCISCO JAVIER ROJAS DURÁN 2
Previous tasks to perform before start practicing with Oracle Spatial
Training material
Important:
This guide is intended to be use with either Oracle 12c Multitenant environment or Oracle Pre-12c
Non-Container (also works with 11gR1 – R2).
Most of the tasks are optional, however those ones that are optional are marked with an (*) , the
ones that are mandatory were tagged with the legend “Mandatory”.
Please follow the order of this task, skip the optional ones as needed.
1. Change in the .bash_profile
In order to use SecureShell client we need to introduce the support of DELETE key into the
bash_profile, therefore at the end of the file we must add the following:
This should be bash_profile after this change:
2. Creating an after startup trigger (Only if you have a Multi-Tenant Container DB)
In a CDB environment, the default behavior when a CDB is started is that all user-defined PDBs are
in MOUNTED state i.e. closed. The only way to change this behavior is by using an AFTER STARTUP
trigger on the database.
In this exercise, you will create AFTER STARTUP trigger to open all PDBs of a CDB. The AFTER
stty erase ^H
# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=db.oracledemo.com
export ORACLE_UNQNAME=cdborcl
export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export ORACLE_SID=cdborcl
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibstty erase ^H
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
4/18
MTI FRANCISCO JAVIER ROJAS DURÁN 3
STARTUP trigger is a database event trigger, which means that the user creating the trigger should
have ADMINISTER DATABASE TRIGGER system privilege. The trigger will only fire after the startup
event on the database.
Steps
1.
Connect to the Root container of CDBORCL as a SYSDBA user.
2. Create a trigger in CDBORCL to open all PDBs automatically after starting upCDBORCL.
3.
Restart the database CDBORCL.
4.
Observe that the PDBs are in READ WRITE open mode.
3. Configuring DB parameters, (this applies to Container and Non-Container DB)
1.
Modify the DB_CACHE_SIZE database initialization parameter to be at least 75 MB.Notice that this parameter will apply either to the Container and all the PDBs.
a.
sqlplus system/Manager1;b.
SHOW PARAMETER db_cache_size
2.
If the db_cache size is less than 75M (megabytes) type:
a. ALTER SYSTEM SET db_cache_size=75M scope=spfile;b. shutdown;c.
startup;d. SHOW PARAMETER db_cache_sizee. exit
. oraenv [Enter cdborcl at the prompt]
sqlplus / as SYSDBA
CREATE OR REPLACE TRIGGER AFTER_STARTUP AFTER STARTUP ONDATABASE BEGIN EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALLOPEN'; END AFTER_STARTUP; / SHOW ERRORS;
SHUTDOWNIMMEDIATE;
SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
5/18
MTI FRANCISCO JAVIER ROJAS DURÁN 4
This is the expected output:
4. Create additional database tablespaces (applies to Container and Non-Container
DB) Mandatory
In SQL*Plus, assuming you are connected as system user, run the following statements:
In Multi-Tenant Container instance:
Be sure your current container is PDBORCL
This is the expected output:
ALTER SESSION SET CONTAINER=PDBORCL;
COLUMN NAME FORMAT A8;
SELECT NAME, CON_ID, DBID, CON_UID, GUIDFROM V$CONTAINERS ORDER BY CON_ID;
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
6/18
MTI FRANCISCO JAVIER ROJAS DURÁN 5
Option A. Using a Single-Tenant instance:
Option B . Using a Non-container Single Instance (Pre-12c):
5. Create the Oracle database users and grant appropriate privileges. Mandatory
Assuming you are still logged in as system/Manager1 either in the Non-container single-instance
or into the Pluggable DB (PDBORCL)
In Single-Tenant Container instance:
Be sure your current container is PDBORCL and also it is open:
Execute the following:
create tablespace student datafile'/u01/app/oracle/oradata/cdborcl/pdborcl/student.dbf' size 150M;
create tablespace indx_tblspc datafile'/u01/app/oracle/oradata/cdborcl/pdborcl/indx_tblspc.dbf' size150M;
create tablespace work_indx_tblspc datafile'/u01/app/oracle/oradata/cdborcl/pdborcl/work_indx_tblspc.dbf'size 100M;
create tablespace student datafile 'student.dbf' size 150M;
create tablespace indx_tblspc datafile 'indx_tblspc.dbf' size150M;
create tablespace work_indx_tblspc datafile 'work_indx_tblspc.dbf'size 100M;
ALTER SESSION SET CONTAINER=PDBORCL;
SHOW CON_ID;SHOW CON_NAME;
ALTER PLUGGABLE DATABASE OPEN;
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
7/18
MTI FRANCISCO JAVIER ROJAS DURÁN 6
This is the expected output:
6. Connecting to the instance with student user
After creating the user student , the only way to connect in a multi-tenant environment is through
EZCONNECT, since we haven’t created the SERVICE yet, we are able to connect as follows:
This is the expected output:
create user student identified by student default tablespacestudent;
grant connect, resource to student;
alter user student quota unlimited on student;
$sqlplus student/student@//[HOSTNAME|IP ]:1521/pdborcl
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
8/18
MTI FRANCISCO JAVIER ROJAS DURÁN 7
7. Configuring Services to allow external connections to the Pluggable DB.
Mandatory.
When a PDB is created, a new default service for the PDB is created automatically, and has
the same name as the PDB. You cannot manage this service, and it should only be used for
administrative tasks.
Do not use this default PDB service for applications. Always use user-defined services for
applications because you can customize user-defined services to fit the requirements of your
applications.
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
9/18
MTI FRANCISCO JAVIER ROJAS DURÁN 8
You can create, modify, or remove a service with a PDB property in the following ways:
If the database is not being managed by Oracle Restart or Oracle Clusterware, then use
the DBMS_SERVICE package to create or remove a database service. This is the
method you will follow in this task.
Note: Each database service name must be unique in a CDB, and each database service
name must be unique within the scope of all the CDBs whose instances are reached through a
specific listener.
Steps
1. When you create a service using the DBMS_SERVICE package, the PDB property of the newservice is set to the current container. Therefore, login to the PDB that you want tocreate/modify/delete the user-defined services before running the DBMS_SERVICE procedures.
If you run DBMS_SERVICE.CREATE_SERVICE in the root container then the service is associatedwith the root.
Connect as SYS to PDBORCL with the SYSDBA role.
2. Create the service by the name PORCL using the DBMS_SERVICE.CREATE_SERVICE procedure.
3. Check to see if the newly created PORCL service shows up in V$SERVICES.
At this point this is the expected output:
CONNECT SYS/Manager1@//127.0.0.1:1521/PDBORCL ASSYSDBA;SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;
EXEC DBMS_SERVICE.CREATE_SERVICE (SERVICE_NAME=>'PORCL', NETWORK_NAME=>'PORCL');
SELECT NAME FROM V$SERVICES;
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
10/18
MTI FRANCISCO JAVIER ROJAS DURÁN 9
4. Start the newly created PORCL service using DBMS_SERVICE.START_SERVICE procedure.Check if the newly created service is started using the V$SERVICES view, and also in the listenerservices.
5. Reconnect to PDBORCL using the PORCL service. Verify that you are reconnected back toPDB PDBORCL.
6. Now connect with the student user:
EXEC DBMS_SERVICE.START_SERVICE('PORCL');SELECT NAME, PDB FROM V$SERVICES;
HOST lsnrctl services
CONNECT SYS/oracle@//127.0.0.1:1521/PORCL AS SYSDBA;SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
11/18
MTI FRANCISCO JAVIER ROJAS DURÁN10
Once he have configured the SERVICE , we must configure clients to use this SERVICE.
In our VM we must run NETCA in order to create a SERVICE entry within TNSNAMES.ORA
In a terminal window logged with the user oracle , run the command NETCA.
sqlplus student/student@//127.0.0.1:1521/PORCL;
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
12/18
MTI FRANCISCO JAVIER ROJAS DURÁN11
Select “Local Net Service Name Configuration”
Please proceed with the next steps inside NETCA by choosing the same options as follows:
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
13/18
MTI FRANCISCO JAVIER ROJAS DURÁN12
Pleas provide a SERVICE NAME for PDBORCL.
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
14/18
MTI FRANCISCO JAVIER ROJAS DURÁN13
Please provide your hostname name or IP.
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
15/18
MTI FRANCISCO JAVIER ROJAS DURÁN14
Click on Change Login in order to provide a different user/password.
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
16/18
MTI FRANCISCO JAVIER ROJAS DURÁN15
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
17/18
MTI FRANCISCO JAVIER ROJAS DURÁN16
Please write the short name of the SERVICE_NAME.
8/16/2019 Tareas Previas a La Realización de Las Prácticas Con Oracle Spatial
18/18
MTI FRANCISCO JAVIER ROJAS DURÁN17
Now we should close NETCA window, and connect from SQLPLUS with the user student as shown
below:
From now we can connect to PDBORCL as if we were using a Pre-12c Non-Container DB.