Home » RDBMS Server » Server Administration » instance
instance [message #293199] Fri, 11 January 2008 03:29 Go to next message
armaan_dba
Messages: 3
Registered: January 2008
Location: india
Junior Member
hello every one my queation is when we create an instance in 10g what is the default size of that intance? can we maximize our instance size?

[Updated on: Fri, 11 January 2008 03:36]

Report message to a moderator

Re: instance [message #293205 is a reply to message #293199] Fri, 11 January 2008 03:43 Go to previous messageGo to next message
azamkhan
Messages: 548
Registered: August 2005
Senior Member
I m not 100% sure but check out your INIT[sid] parameter file.

Regards,
Azam Khan
Re: instance [message #293280 is a reply to message #293199] Fri, 11 January 2008 09:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good question did you search in documentation?
http://www.oracle.com/pls/db102/portal.portal_db?selected=1

Regards
Michel
Re: instance [message #293303 is a reply to message #293199] Fri, 11 January 2008 11:22 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Default size is what is specified in init.ora file.
Re: instance [message #293312 is a reply to message #293303] Fri, 11 January 2008 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no log file or data file size in init.ora, I think this is the question.
Anyway, what are the default pool sizes if there are not in init.ora, this is another question.

Regards
Michel

[Updated on: Fri, 11 January 2008 12:05]

Report message to a moderator

Re: instance [message #293313 is a reply to message #293199] Fri, 11 January 2008 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
armaan_dba,
>can we maximize our instance size?
Please quantify.
At what size is an Oracle database "maximized"?
Re: instance [message #293396 is a reply to message #293199] Sat, 12 January 2008 02:31 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I think the OP needs to know instance size and not database size.
An instance size is the size of the SGA.
SGA size is the memory parameters defined in init.ora file.
Default SGA size is what you set in your init.ora file when you create database.
You can use DBCA to see and modify your instance size.
Re: instance [message #293397 is a reply to message #293396] Sat, 12 January 2008 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Default SGA size is what you set in your init.ora file when you create database.

This is wrong.

And what if you NEVER set anything in your init.ora?

Regards
Michel

[Updated on: Sat, 12 January 2008 02:56]

Report message to a moderator

Re: instance [message #293401 is a reply to message #293199] Sat, 12 January 2008 03:19 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:

And what if you NEVER set anything in your init.ora?


Is it possible?
How would the instance be started then?
Re: instance [message #293402 is a reply to message #293401] Sat, 12 January 2008 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it is possible.
As far I remember, there is only ONE mandatory parameter: db_name.
You can then create the database with only "create database;".

Regards
Michel
Re: instance [message #293415 is a reply to message #293199] Sat, 12 January 2008 06:45 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
WIth no SGA specified,how would the database be open?
How would this command work?
SQL>startup nomount pfile='.,.../init.ora';

[Updated on: Sat, 12 January 2008 07:08]

Report message to a moderator

Re: instance [message #293424 is a reply to message #293415] Sat, 12 January 2008 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

WIth no SGA specified,how would the database be open?

This is the purpose of default values, isn't it?

Regards
Michel
Re: instance [message #293428 is a reply to message #293199] Sat, 12 January 2008 10:36 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
but you said
Quote:

As far I remember, there is only ONE mandatory parameter: db_name.

Michel, Can you demonstrate what you said?
Re: instance [message #293431 is a reply to message #293428] Sat, 12 January 2008 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No problem.
I'll show you this on Monday, currently I don't have any environment to create a database.

Regards
Michel
Re: instance [message #293466 is a reply to message #293431] Sun, 13 January 2008 01:40 Go to previous messageGo to next message
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
Member
Michel Cadot is correct. The design of Oracle database is such that even if you do not specify/use any any of configuration file such as PFILESPFILE ,listener.ora, tnsnames.ora, sqlnet.ora than also a database can be created, connected to & run smoothly. The credit goes to default values assumed while an instance or listener is started. The rule is simple if values are available in configuration files than use them else silently assume defaults. This is the beauty of Oracle & you do not get this beauty in any other enterprise class software.

If you want to have a taste than go ahead & try for yourself on a test machine, you will be happy to discover this behavior of Oracle. The only basic requirement is you should understand what you are doing. But before you do it just brush up little on oracle fundamentals. In Oracle world Every file which oracle expects has a default location & if file is not there than there is a default behavior in absence of file similarly every parameter is expected in some configuration file is not found there than there is a default value.

Do not ever try this test on a production. If attempting on a already existing database than do not forget to relocate controlfiles , logfiles & data files to expected default locations.

Regards

Manjit Kumar [ mkbhati]

[Updated on: Sun, 13 January 2008 01:42]

Report message to a moderator

Re: instance [message #293554 is a reply to message #293428] Mon, 14 January 2008 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here it is:
[MCA]> sqldba

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jan 14 08:24:18 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

?> !cat /.users/mount/mcadot/temp/initMCA.ora
db_name=MCA

?> startup nomount pfile=/.users/mount/mcadot/temp/initMCA.ora
ORACLE instance started.
Total System Global Area  171966464 bytes
Fixed Size                  2028656 bytes
Variable Size             113249168 bytes
Database Buffers           50331648 bytes
Redo Buffers                6356992 bytes
?> create database;

Database created.

?> select name from v$dbfile;
NAME
-----------------------------------------------------------------
/oracle/product/rdbms/10.2.0.3/dbs/dbs1MCA.dbf
/oracle/product/rdbms/10.2.0.3/dbs/dbx1MCA.dbf

2 rows selected.

?> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
?> startup mount pfile=/.users/mount/mcadot/temp/initMCA.ora
ORACLE instance started.
Total System Global Area  171966464 bytes
Fixed Size                  2028656 bytes
Variable Size             113249168 bytes
Database Buffers           50331648 bytes
Redo Buffers                6356992 bytes
Database mounted.
?> alter system enable restricted session;

System altered.

?> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options
?> exit

It took about one minute to do this.

Regards
Michel
Re: instance [message #293657 is a reply to message #293199] Mon, 14 January 2008 07:49 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I am on 9.2.0.1
Quote:


startup nomount pfile=/.users/mount/mcadot/temp/initMCA.ora
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 2028656 bytes
Variable Size 113249168 bytes
Database Buffers 50331648 bytes
Redo Buffers 6356992 bytes


So these are the default values..
I did the same thing but got the error the following error:
SQL> startup nomount pfile='C:\oracle\admin\testdb\pfile\init_testdb.ora'
LRM-00109: could not open parameter file 'C:\oracle\admin\testdb\pfile\init_testdb.ora'
ORA-01078: failure in processing system parameters
SQL>

As you told,i have only parameter "db_name=testdb" in my init file.
Re: instance [message #293660 is a reply to message #293657] Mon, 14 January 2008 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The error has nothing to do with the file content, it is:
could not open parameter file 'C:\oracle\admin\testdb\pfile\init_testdb.ora'

Check if your file is there and your Oracle instance owner (most often SYSTEM) has privileges on it.

Regards
Michel
Re: instance [message #293677 is a reply to message #293199] Mon, 14 January 2008 09:56 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
With 300 messeges and yet do not know how to create a database Mad

I am getting the same error.
This is what i did.
C:\set oracle_sid=testdb
C:\oradim -new -sid testdb -intpwd test -pfile C:\oracle\admin\testdb\pfile\init_testdb.ora
C:\sqlplus /no log
SQL>conn sys/test as sysdba
SQL>startup nomount pfile='C:\oracle\admin\testdb\pfile\init_testdb.ora'
LRM-00109: could not open parameter file 'C:\oracle\admin\testdb\pfile\init_testdb.ora'
ORA-01078: failure in processing system parameters
Re: instance [message #293680 is a reply to message #293677] Mon, 14 January 2008 10:03 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>SQL>startup nomount >pfile='C:\oracle\admin\testdb\pfile\init_testdb.ora'
>LRM-00109: could not open parameter >file 'C:\oracle\admin\testdb\pfile\init_testdb.ora'
it will not open until you have to correct parameter file becuase it is currently corrupted or unformatted.
Re: instance [message #293687 is a reply to message #293680] Mon, 14 January 2008 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...or does not exist or Oracle instance owner does not have access to it...

Regards
Michel
Re: instance [message #293706 is a reply to message #293199] Mon, 14 January 2008 12:59 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Ok finally done,,,
Quote:
Michel Cadot is correct. The design of .....

ofcourse i know he is correct.All i wanted to do is to try it myself but was unsure.

this might be on behalf of Armaandba.
Where are these default values stored or from where does oracle retrieve these values?

[Updated on: Mon, 14 January 2008 13:01]

Report message to a moderator

Re: instance [message #293709 is a reply to message #293706] Mon, 14 January 2008 13:19 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Where are these default values stored

Inside the code.

Regards
Michel
Previous Topic: ORA-01555 caused by SQL statement below
Next Topic: ORA-12519, Listener refused the connection
Goto Forum:
  


Current Time: Sun Dec 11 08:29:55 CST 2016

Total time taken to generate the page: 0.04763 seconds