Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

editing .ora files

Omar Alvi_1
Super Advisor

editing .ora files

Hi,

I'm totally new to oracle. In fact I'm installing a product called visionael, which requires oracle, and the manuals assume that you know Oracle.

neway, my question at the moment is, how do you edit .ora files, like spfiledb.ora, tnsnames.ora. In Windows it gives part rubbish when opening it.

Any help greatly appreciated.

A pointer to a good basics guide would be extremely helpful as well

Regards,

-Alvi
20 REPLIES
Sanjay_6
Honored Contributor

Re: editing .ora files

Hi,

Have you tried the oracle docs website.

http://otn.oracle.com/documentation/index.html

I don't know about spfiledb.ora but tnsnames.ora is a text file and any editor should be able to open it without showing the content as junk. I would hope the other file would be text too. Check your editor.

Hope this helps.

Regds
Tom Geudens
Honored Contributor

Re: editing .ora files

Hi,
As was stated you should be able to edit those files with a "normal" texteditor (preferably do not try Word :-). Try "vi" on the Unix host itself. If that works (and does not show "rubbish") there's probably a conversion problem between your Windows Workstation an the Unix host.

Regards,
Tom Geudens
A life ? Cool ! Where can I download one of those from ?
Tom Geudens
Honored Contributor

Re: editing .ora files

Hi,
Correction !!!
I checked up on this spfiledb.ora (which did not sound familiar, I'm an ex-DBA) and found the following link : http://databasejournal.com/features/oracle/article.php/2212711
This would suggest that spfiledb.ora is a binary file that you do NOT edit with a texteditor.

tnsnames.ora is definitly a textfile however ...

Regards,
Tom Geudens
A life ? Cool ! Where can I download one of those from ?
Omar Alvi_1
Super Advisor

Re: editing .ora files

Thanks Sanjay and Tom,

especially for following up on the answer.

This step by step installation guide leaves a lot to be desired, assuming the reader to be an oracle and sql expert :(

Thanks again for your help. There should be some tool to edit some of these files.

Is there some good forum for Oracle and SQL, similar in value to this forum. Now i'm getting some sql error

ORA-12913: Cannot create dictionary managed tablespace

Sanjay, the docs you suggested seem to require previleges

Regards,

Omar Alvi
Tom Geudens
Honored Contributor

Re: editing .ora files

Hi again,
http://metalink.oracle.com/ is the oracle-equivalent of ITRC. I don't know if registration is "free" though (I'm registered because we have an Oracle support contract).

I'm not familiar with the software you're trying to install (so take my suggestions for what they are, suggestions !), but are you running the oracle-installation with the correct user ? Databases should be created with the "oracle" user (or a user in the primary group of "oracle" - usually "dba") ...

Regards,
Tom Geudens
A life ? Cool ! Where can I download one of those from ?
Omar Alvi_1
Super Advisor

Re: editing .ora files

Hello Tom

The producti'm installaing is visionael network audit , similar to HP NNM.

The metalik site requires some serial number stuff for registration.

I'm installing Oracle on windows, using ITRC only because of the wealth of experience the members have.

I recall adding the dba group and user and setting a host of variables when I installed Oracle on Unix, but I did nothing of the kind in windows. All I have is the Administrator user.

Maybe I blundered because of the approaching deadline :) So, do you think I need to reinstall? Is there a summarised guide for the oracle installation?

Thanks a lot

-Alvi
Tom Geudens
Honored Contributor

Re: editing .ora files

Hi,
I have - alas - no experience with Oracle installation on Windows servers. However, I believe your can register for free on the Oracle Technology Network.
http://otn.oracle.com/documentation/index.html
You'll find all the manuals you need there !

Regards,
Tom Geudens
A life ? Cool ! Where can I download one of those from ?
Michael Schulte zur Sur
Honored Contributor

Re: editing .ora files

Hi,

editing the spfiledb.ora is not good. As someone stated before, it is binary. You can create a pure text file by create pfile from spfile. This pfile you can edit and then you use create spfile from pfile to change the spfile. For this, the database must not be open. The alter system command are usually written into the spfile. You also can use the enterprise manager do configure existing databases.

greetings,

Michael
Omar Alvi_1
Super Advisor

Re: editing .ora files

Thanks Tom, for persisting, and Michael for the solution.

Now, with the pfile, sfile issue solved ... i'm having a problem with sql as follows, just in case you can help.

SQL> create tablespace visionael_tempspace
2 datafile 'c:\oracle\oradata\orak\orak_tmp01.dbf' size 10 M
3 autoextend on next 10M
4 default storage (
5 initial 50K
6 next 50K
7 minextents 1
8 maxextents 121
9 pctincrease 10
10 ) temporary online;
create tablespace visionael_tempspace
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

Regards

-Alvi
twang
Honored Contributor

Re: editing .ora files

Hi,
How about your SYSTEM tablespace?
If your system tablespace is created as locally managed, you can't create any dictionary managed tablespace.
Using the following SQL to see the details:
select * from dba_tablespaces where tablespace_name='SYSTEM';

regards
twang
Omar Alvi_1
Super Advisor

Re: editing .ora files

Hi,

I'm new to SQL as well.

How can I modify the SQL statements to conform to my system

I actually have 8 examples I need to follow. 4 to create tablespaces, a few others to create users, report role, and granting report role priviledges.


Thanks

-Alvi
Omar Alvi_1
Super Advisor

Re: editing .ora files

... and the following sql statements yield

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
TOOLS
USERS
XDB

11 rows selected.

and another not so readable

SQL> select * from dba_tablespaces where tablespace_name='SYSTEM';

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- --- ----------
ALLOCATIO PLU SEGMEN
--------- --- ------
SYSTEM 8192 65536 1
2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL
SYSTEM NO MANUAL

Regards,

-Alvi
Michael Schulte zur Sur
Honored Contributor

Re: editing .ora files

Hi,

ORA-12913 Cannot create dictionary managed tablespace

Cause: An attempt was made to create a dictionary managed tablespace in a database whose SYSTEM tablespace is locally managed.

Action: Create a locally managed tablespace.

When creating tablespace you have to use extent management local.

greetings,

Michael
Omar Alvi_1
Super Advisor

Re: editing .ora files

Hi,

Following are three different cases of the statements.

SQL> create tablespace visionael_tempspace
2 extent management local
3 datafile 'c:\oracle\oradata\orak\orak_tmp01.dbf' size 10 M
4 autoextend on next 10M
5 default storage (
6 initial 50K
7 next 50K
8 minextents 1
9 maxextents 121
10 pctincrease 10
11 ) temporary online;
create tablespace visionael_tempspace
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace


SQL> create tablespace visionael_tempspace
2 extent management local
3 datafile 'c:\oracle\oradata\orak\orak_tmp01.dbf' size 10 M;

Tablespace created.


SQL> create tablespace visionael2_tempspace
2 datafile 'c:\oracle\oradata\orak\orak2_tmp01.dbf' size 10 M
3 ;

Tablespace created.

So twice the tablespaces have been created without using the rest of the options

I do not mean to use your help and cooperation to entirely solve my problems, but how do I proceed now?

-I don't know what's causing the error
-I have created two tablespaces whoich I either have to delete or incorporate within them the other options

-Alvi
Michael Schulte zur Sur
Honored Contributor

Re: editing .ora files

Hi,

I am not sure, what problem you still have.
The first statement failed, because the default is a dictionary managed extent management, which conflicts with a locally managed system tablespace. In example two and three you used local extent management. The rest of the options was replaced by their default values. You can go on with the instructions, the software manual gives you.

greetings,

Michael
twang
Honored Contributor

Re: editing .ora files

SQL> select * from dba_tablespaces where tablespace_name='SYSTEM';

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- --- ----------
ALLOCATIO PLU SEGMEN
--------- --- ------
SYSTEM 8192 65536 1
2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL
SYSTEM NO MANUAL

As your reply above, you cannot create new DICTIONARY managed tablespaces, and you cannot migrate a LOCALLY managed SYSTEM tablespace back to DICTIONARY.
Syntax to create LOCALLY managed tablespace:
CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/usr01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

Hope this can help you.
twang
Honored Contributor

Re: editing .ora files

Why fail, for locally managed tablespaces, the LOCAL option of the EXTENT MANAGEMENT clause specifies that a tablespace is to be locally managed. By default a tablespace is locally managed.
extent_management_clause for Locally-managed tablespace:==
[ EXTENT MANAGENT
[ LOCAL
[ AUTOALLOCATE | UNIFORM [SIZE integer [K|M]]]]]

And UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes. Default size is 1 megabyte.
Good luck.
Omar Alvi_1
Super Advisor

Re: editing .ora files

Thanks a lot Michael, Twang, and all else who helped, persisted, and followed up. Exactly the reason why for ANY rpoblem I refer to ITRC

The products support team pointed me to an Oracle Repository Builder script, which eventually solved the problem.

I have though learnt a lot from your posts, and they might motivate me to learn more DBA stuff.

Thanks a lot

and Regards,

-Alvi
Michael Schulte zur Sur
Honored Contributor

Re: editing .ora files

Hi Omar,

learning DBA Stuff is a good idea. It will not only make your own work more fun and more satisfactory, when you know, what other people/documents are talking about but also you may give other people just the right hint to solve their own problem. ;-))))

So, learn and share,

Michael
Sanjay Kumar Suri
Honored Contributor

Re: editing .ora files

Dear All

Quite a long & interesting problem solving.

Alvi & others can refer to me Oracle related queries on Windows/HP-UX.

Thanks.
Sanjay
OCA/OCP
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.