Home » RDBMS Server » Server Administration » Multiple DB Instances on single DB server/host (Oracle 12g)
Multiple DB Instances on single DB server/host [message #653771] Mon, 18 July 2016 10:50 Go to next message
TurtleTank112
Messages: 5
Registered: July 2016
Junior Member
(Complete DB/Ora noob alert)

Hello, i'm trying to achieve the following.

Currently I have one DB being consumed by 5 testers. I'd like to replicate that DB 5 times with a unique SID and have 5 instances of the same DB running on the same DB Server.

I've looked at rman some and some of the other options but I get confused when it comes to data files etc. ie; in the above example, would there be separate datafiles created for each DB instance? My root, or base DB has appx 12GB data files. (Although I think that means its allocated and not necessarily used) does that mean if I want 5 instances on a DB server, Id need 5x12GB storage capacity as example? Does the data for the DB live in those data files? like Flat File DB kind of?


Is a simpler way to do this to just make a template of the 'root' db and then use that template to create the additional DB instances (SIDS). Is there any consideration on how to tweak storage? We are using "File System" - Can I just change that to ASM (Needed for any reason? I donno, seemed a more dynamic/grow option maybe).

Re: Multiple DB Instances on single DB server/host [message #653772 is a reply to message #653771] Mon, 18 July 2016 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You cannot have 5 same SID in the same server (well you can but it is opening the gates of hell).
Why don't you create 5 schemes, one for each tester, in your current database instead?

Re: Multiple DB Instances on single DB server/host [message #653773 is a reply to message #653771] Mon, 18 July 2016 11:05 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Does the system have sufficient disk & RAM to support 5 databases??

Have you considered just establishing 5 Virtual Machines instead?
Re: Multiple DB Instances on single DB server/host [message #653774 is a reply to message #653773] Mon, 18 July 2016 11:13 Go to previous messageGo to next message
TurtleTank112
Messages: 5
Registered: July 2016
Junior Member
BlackSwan wrote on Mon, 18 July 2016 11:05
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Does the system have sufficient disk & RAM to support 5 databases??

Have you considered just establishing 5 Virtual Machines instead?


Thanks, I hope to be in compliance with the rules, I've searched but almost getting TOO much information. Wink

Good question on hardware support, I believe we'll be okay. It's needed aswell for functional testing without regard for performance at this point. And I can't go the VM route cause - politics. Wink

Re: Multiple DB Instances on single DB server/host [message #653775 is a reply to message #653772] Mon, 18 July 2016 11:16 Go to previous messageGo to next message
TurtleTank112
Messages: 5
Registered: July 2016
Junior Member
Michel Cadot wrote on Mon, 18 July 2016 11:05

You cannot have 5 same SID in the same server (well you can but it is opening the gates of hell).
Why don't you create 5 schemes, one for each tester, in your current database instead?



My apologies, I meant unique SID's. Smile I'll research more on schemas. How do they compare to using a template? Does using a schema still require me to make the DB's prior to using the schema to populate?

Sorry, I know i'm butchering terminology. I hope this jargen makes sense, and I thank you for your time responding.
Re: Multiple DB Instances on single DB server/host [message #653776 is a reply to message #653775] Mon, 18 July 2016 11:29 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
https://docs.oracle.com/database/121/CNCPT/toc.htm
Re: Multiple DB Instances on single DB server/host [message #653777 is a reply to message #653775] Mon, 18 July 2016 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Does using a schema still require me to make the DB's prior to using the schema to populate?


No, you can use the current one.
Assume you have a database with current schema SCHEMA.
Then you create the 5 tester schemes as (assuming you have enough space on your disk to support 5 more schemes):
expdp system/manager schema=SCHEMA DUMPFILE=schema.dmp
impdp system/manager fromschema=SCHEMA toschema=TESTER1 DUMPFILE=schema.dmp
impdp system/manager fromschema=SCHEMA toschema=TESTER2 DUMPFILE=schema.dmp
impdp system/manager fromschema=SCHEMA toschema=TESTER3 DUMPFILE=schema.dmp
impdp system/manager fromschema=SCHEMA toschema=TESTER4 DUMPFILE=schema.dmp
impdp system/manager fromschema=SCHEMA toschema=TESTER5 DUMPFILE=schema.dmp
You then have to connect as SYSTEM with SQL*Plus to set a password for each tester schema.
That's all.

[Updated on: Mon, 18 July 2016 11:30]

Report message to a moderator

Re: Multiple DB Instances on single DB server/host [message #653778 is a reply to message #653777] Mon, 18 July 2016 11:37 Go to previous messageGo to next message
TurtleTank112
Messages: 5
Registered: July 2016
Junior Member
That is really slick, let me start playing with that and get back to you. How do data files work in this configuration? Will there be a set of them created for each schema?

Thanks Michel. And Love from the US in your countries hard times of late. <3

Re: Multiple DB Instances on single DB server/host [message #653779 is a reply to message #653778] Mon, 18 July 2016 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As it all accounts will use the same data files (tablespaces).
If you don't want this you can create several other tablespaces for each schema.
Assuming SCHEMA use only one tablespace: SCHEMA_TBS.
You can create 5 other tablespaces: TESTER1_TBS... using CREATE TABLESPACE command.
Then the import commands above become:
impdp system/manager fromschema=SCHEMA toschema=TESTER1 DUMPFILE=schema.dmp remap_tablespace=SCHEMA_TBS:TESTER1_TBS
...
Re: Multiple DB Instances on single DB server/host [message #653780 is a reply to message #653771] Mon, 18 July 2016 11:55 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
What you are describing is a perfect usage case for the Multitenant option. Do you happen to be licensed to use Multitenant?
Re: Multiple DB Instances on single DB server/host [message #653781 is a reply to message #653780] Mon, 18 July 2016 12:53 Go to previous messageGo to next message
TurtleTank112
Messages: 5
Registered: July 2016
Junior Member
Hey there, it turns out, yes, we are licensed for multitenent. I'll start looking into it - I assume what you refer will be obvious. Wink
Re: Multiple DB Instances on single DB server/host [message #653782 is a reply to message #653781] Mon, 18 July 2016 13:09 Go to previous message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
That is fan-bloody-tastic, man. I have to say that as a soi-disant "noob", you may need a bit of help. Here's a simple tutorial on the architecture I recorded the couple of weeks back, there is another one that will be up soon on creating your PDBs:
http://www.skillbuilders.com/Oracle-Database-Free-Tutorials?id=140&w=What-is-Oracle-12c-Multitenant-CDB-and-PDB-Architecture
Previous Topic: What is the INSTANT_RESTORE parameter used for?
Next Topic: manual creation of database
Goto Forum:
  


Current Time: Thu Feb 22 07:36:41 CST 2018

Total time taken to generate the page: 0.03084 seconds