Search code examples
oracle-databasecentos7sid

Multiple SIDs in oracle

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,