HI , we are creating database and it will be on Release 184.108.40.206.0 . We are having below requirement for a scractch new database
--> databsae will have ~30 tables, one schema
--> It will be having ~25G of data considering future growth
What will be my memory configuration -- SGA/PGA/Java pool .. etc size?
what will be optimal the no and size of UNDO space assigned?
what will be the optimal no of 'data files/redologs/Archive logs' and their sizes?
>>What will be my memory configuration -- SGA/PGA/Java pool .. etc size?
In 11g You can use Automatic Memory Management (AMM) you have to only set MEMORY_TARGET and this size you can set on the basis of your OS memory.
You can alter it later on if you need to change
>>what will be optimal the no and size of UNDO space assigned?
You should have one undo TS in database, you can also resize it as per your Requirement, can change the size online
>>what will be the optimal no of 'data files/redologs/Archive logs' and their sizes?
You can set any value, depend on your usage, can be easily manage later on
Better you use DBCA to create the DB it will give you some suggestion/idea during creation or to keep some default values.
want a rough idea to start with, if i will set ~3GB of memory target + 1GB of UNDO + two datafile for user data with each ~10GB pointing to one table space
, will it be ok?
Who knows? The only way to tell is to try it and see. As already said, nothing is cast in stone, everything can be adjusted as needed. What is the cost of trying it and making your own measurements. No matter what configuration you start with, you are going to have to do that anyway. Unless you know going in that your database is going to have some really outlandish requirements (yours doesn't) just go with the defaults as your starting point.
You are showing the early signs of Compulsive Tuning Disorder.
1.What will be my memory configuration -- SGA/PGA/Java pool .. etc size?
To start with, Use only two initialization parameters for memory
i.e. MEMORY_TARGET & MEMORY_MAX_TARGET and allocate a fair amount of memory.
( I would allocate 70% of total RAM, if no other application / software except oracle db is configured in your server )
2. what will be optimal the no and size of UNDO space assigned?
If you create your DB using DBCA, it'll enable automatic extension for Undo tablespace's datafile with maximum size unlimited.
When your database is up and running, you could assess the undo tablespace need using V$UNDOSTAT view.
3.what will be the optimal no of 'data files/redologs/Archive logs' and their sizes?
(a) Data Files :If your hardware supports, go ahead with ASM and configure atleast two faiure groups for each data group.
(b) Redo Logs : Atleast two log members per redo log group, ideally on separate disks.
(c) Archive Logs : Two Destinations : One in the server in a spare disk, and other on to a geographically separated server configured using DataGuard as Physical standby.