OraFAQ Forum: Server Administration » Multiple DB Instances on single DB server/host

Home » RDBMS Server » Server Administration » Multiple DB Instances on single DB server/host (Oracle 12g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Multiple DB Instances on single DB server/host [message #653771]

Mon, 18 July 2016 10:50

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).

Report message to a moderator

Re: Multiple DB Instances on single DB server/host [message #653772 is a reply to message #653771]

Mon, 18 July 2016 11:05

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

Re: Multiple DB Instances on single DB server/host [message #653773 is a reply to message #653771]

Mon, 18 July 2016 11:05

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: Multiple DB Instances on single DB server/host [message #653774 is a reply to message #653773]

Mon, 18 July 2016 11:13

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

Report message to a moderator

Re: Multiple DB Instances on single DB server/host [message #653775 is a reply to message #653772]

Mon, 18 July 2016 11:16

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.

Report message to a moderator

Re: Multiple DB Instances on single DB server/host [message #653776 is a reply to message #653775]

Mon, 18 July 2016 11:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

https://docs.oracle.com/database/121/CNCPT/toc.htm

Report message to a moderator

Re: Multiple DB Instances on single DB server/host [message #653777 is a reply to message #653775]

Mon, 18 July 2016 11:29

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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):

Code: [Select all] [Show/ hide]

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

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

Report message to a moderator

Re: Multiple DB Instances on single DB server/host [message #653779 is a reply to message #653778]

Mon, 18 July 2016 11:44

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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:

Code: [Select all] [Show/ hide]

impdp system/manager fromschema=SCHEMA toschema=TESTER1 DUMPFILE=schema.dmp remap_tablespace=SCHEMA_TBS:TESTER1_TBS
...

Report message to a moderator

Re: Multiple DB Instances on single DB server/host [message #653780 is a reply to message #653771]

Mon, 18 July 2016 11:55

John Watson
Messages: 8976
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?

Report message to a moderator

Re: Multiple DB Instances on single DB server/host [message #653781 is a reply to message #653780]

Mon, 18 July 2016 12:53

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

Report message to a moderator

Re: Multiple DB Instances on single DB server/host [message #653782 is a reply to message #653781]

Mon, 18 July 2016 13:09

John Watson
Messages: 8976
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

Report message to a moderator

Previous Topic:	What is the INSTANT_RESTORE parameter used for?
Next Topic:	manual creation of database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 20:43:25 CDT 2025