Issue
Can we have multiple SIDs or instances in Oracle? This can be created from a sqlplus or I need a DB client. I am working with:
Centos 7. Oracle 11g2
I have already created a default SID, but I am looking to import more databases each with its respective Instance.
Solution
welcome to SO!
Oracle Database is not only "a database", it is a "database system". You can have one million databases ingested into your "one oracle database system" consisting of one SID (=instance=process+memory) and one database(name) (=datafiles,controlfiles,online redologfiles).
After Oracle Database software is in place, you can create "a database system" by
- $ORACLE_HOME/bin/sqlplus: "create database ... "
- $ORACLE_HOME/bin/dbca (Database Creation Assistant), GUI or silent.
Ingest
You ingest (external) databases into different schemas.
external database A => schemaA (create user schemaA ...)
external database B => schemaB (create user schemaB ...)
etc.
Security
create user appA ...
grant schemaa_rw to appA; <- user appA can only access objects in schemaA being assigned role 'schemaa_rw'
create user appB ...
grant schemab_rw to appB; <- user appB can only access objects in schemaB being assigned role 'schemab_rw'
The best part!
Now you have ONE database system to patch, to upgrade,
to secure, to configure, to tune - to operate.
The future of Oracle Database
If you need more isolation between your data(bases)/schemas, you can utilize a container database (CDB) from version 12c and ingest each external database into a pluggable database.
Best of luck,
Answered By - Bjarte Brandt Answer Checked By - Senaida (WPSolving Volunteer)