last year i have presented at the ” 9th Saudi National GIS Symposium“. In this conference i talked about GIS Database Security which is a unique topic, as it mixes IT & GIS fields in enterprise systems.
The presentation tackled many topics such as : the architecture of Security layers in enterprise IT GIS system, implementing best security practices in GIS database using Oracle Security products (Network Encryption, Label Security, Access Controls).
In this blog article i am providing overview steps in creating a fresh “NEW” oracle 12c database in Unix environment.
After installing oracle database 12c binaries successfully, proceed with the following steps and there are 2 paths for the database creation you can follow one of them depending on your requirements.
PATH Number 1
cd $ORACLE_HOME/bin
export DISPLAY=PC_machine_name:0.0
/usr/bin/X11/xclock
dbca
The database configuration assistant will be invoked with “Create Database” option.
Click “Next”
Choose and insert the required information such as:
Global Database Name:
Storage Type: “File System” for Non-ASM environment.
Database Files Location:
Character Set (be careful and choose the correct one, once you create the database you can’t change the default character set of the database).
Now, if you want to choose the “Advanced Mode”
PATH Number 2
You will choose a template based on type of applications working against your database.
The General Purpose or Transaction template and the Data Warehouse template create a database with the COMPATIBLE initialization parameter set to 12.1.0.2.0
You can either choose to configure enterprise manager or not.
I choose to have both “SYS” and “SYSTEM” having the same password (you can change system password later on if you want to)
You can enable database vault or label security while creating the database
For memory setup I choose “automatic memory management”
For process I choose the default one (300 processes)
For connection mode I choose “Shared Server Mode” to serve multiple application simultaneously
Choose the “create database” and I have chosen to “generate database creation script” for reference.
Review the database creation summary
Normally when we execute the grep unix command to list oracle processes, you will find all “detailed” oracle process:
ps -ef | grep [o]ra_
this is the normal process architecture in Oracle database hosted on Unix environment prior to 12c release.
In oracle 12c you can switch to Multi-threaded architecture by adding a new parameter in your init.ora file.
go to $ORACLE_HOME/dbs and edit your init.ora (text init file in case you are not configuration SPFILE) and add the parameter:
threaded_execution=TRUE
Then, create a password file per the documentation: https://geodatamaster.wordpress.com/2015/05/25/oracle-12c-database-password-file/
Then bounce the Oracle instance (shutdown/startup) the database.
Please note that the OS authentication won’t’ work anymore after changing the mode to “multi-threading”, and you will receive the below error:
If you face problems connecting to the database, you can connect internally through Bequeath protocol: http://www.dbi-services.com/index.php/blog/entry/multithreaded-12c-and-connect-as-sysdba
After enabling multi-threading, the oracle process on the server will look like this:
Interesting part that in IBM AIX environment the Oracle database process using topas command shows the database is consuming around 97% of the CPU ….. This will requires more investigation from my side 😐
Here i am exploring the concept of unplugging a database in 12c multi-tenant setup architecture.
SQL> alter pluggable database pdb2 unplug into ‘/oracle/DCE/pdb2_unplug.xml’;
If you query:
select * from cdb_pdbs;
database status changed to —–> UNPLUGGED
The database is unplugged but not dropped and you can still see it any many data dictionary views:
To drop pluggable database:
SQL> DROP PLUGGABLE DATABASE PDB2 INCLUDING DATAFILES;
Checking the data dictionary, the pluggable database doesn’t exist anymore:
***** Its important to know that unplugging the database could cause problems while patching/upgrading, so its better to drop your database before applying any patches to avoid problems.
Hope this information is useful.
Geodata Master 