Forum Stats

  • 3,770,398 Users
  • 2,253,103 Discussions
  • 7,875,439 Comments

Discussions

INITXXX.ORA Location

User_N8412
User_N8412 Member Posts: 3 Green Ribbon
edited Aug 27, 2021 5:35PM in General Database Discussions

Hello,

I need to change NLS_DATE_FORMAT. However, I cannot find where the INITXXXX.ORA file is located. My DBA has told me that it should be in /oracle/app/oracle/dbs, however, I simply do not have those folders. I just upgraded my server to 19C by using NT_193000_client_home.zip on the following web page (https://www.oracle.com/database/technologies/oracle19c-windows-downloads.html) - maybe I was supposed to use the client instead of the client_home? Any help is greatly appreciated. Thanks in advance.

Tagged:

Answers

  • EdStevens
    EdStevens Member Posts: 28,534 Gold Crown
    edited Aug 27, 2021 7:51PM

    IF you have one, it will be located in $ORACLE_HOME/dbs.

    I say "IF" because you very well may not have an init<sid>.ora, but be using exclusively an spfile. The spfile would be located in the same directory. And beware that the spfile is a binary file that WILL be corrupted if you try to edit it manually.

    But all that begs several questions. You said you upgraded your 'server' by using a client installation file. The only thing that would have installed/upgraded is a client installation. And the client doesn't use the init file. What, exactly, were you trying to upgrade? On what machine?

    You said you consulted your DBA to find the init file. So what is your role in all of this? If you are not the DBA, you should not be mucking around with the init file. And if I were your DBA and found you doing so, I'd be reporting you to your supervisor, asking for disciplinary action.

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge

    Hi @User_N8412 ,

    why do you want to change the parameter in the init.ora file? Maybe it also meets your needs to only change it in your session or in your pl/sql code.

    Best regards

    Jan

  • User_N8412
    User_N8412 Member Posts: 3 Green Ribbon

    @EdStevens

    Good morning. I apologize for not explaining my employment situation in detail from the start, even though I didn't think that it was anyone's business. I work for a state government entity where we have remote DBA services for specific servers. However, for the server in question it is up to myself and my team to upgrade and maintain it, thus is why I am 'mucking' around with the init file. I hope that clears up any opsec concerns you may have.

    I am very new to installing Oracle, but I am going to try and include as many details as possible. The server that I upgraded is running on a Windows server 2012 virtual machine in a VM server cluster hosted my VM vSphere. The server formally had Oracle client 12 (OraClient12Home1_32 bit) installed on it, which I then removed and installed 19C by using NT_193000_client_home.zip on the following web page (https://www.oracle.com/database/technologies/oracle19c-windows-downloads.html). After installing and replacing my tnsnames.ora file, my reporting tools such as pl/sql developer, Argos, etc all work great....aside from my NLS_DATE_FORMAT.

    After upgrading, I noticed on my reporting tools (mentioned above) that I was receiving errors for some queries that included date variables (ex. select to_date(sysdate, 'DD-MON-YYYY') from dual). This query now returns 8/20/0021, as opposed to 8/20/2021 as it used to. This clearly is causing issues with my queries, as the year is not correct.

    I then queried (select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT'). The value that is being returned after querying on the upgraded server is DD-MON-RR, whereas when querying the same on an Oracle 12c server, the value being returned is DD-MON-RRRR. I believe this is what is causing the date issue explained above, thus I proceeded to try and find my init file so that I could alter the default NLS_DATE_FORMAT. However, there is not 'dbs' folder as you explained above, Ed. I do not see $ORACLE_HOME/dbs, which is making my wonder if what you stated is my problem - that I may have installed a client installation? I am not versed enough to know the difference, I suppose, which is why I am reaching out on this forum.

    @Jan Gorkow I was able to resolve the date issues for 24 queries that were presenting errors to me after our upgrade by altering the code that we used to format the dates, however I was hoping to simply (or maybe not so simply) change the nls_date_format so that we wouldn't need to change all of our code that we have always used to format dates in the past, going forward. If that makes sense.

    Thank you both again for the information.

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge

    Hi @User_N8412 ,

    ok, first you should check, if your database instance is really using an init.ora file (old school) or the spfile. For this use the following query:

    SELECT VALUE
     FROM v$parameter
     WHERE name = 'spfile';
    

    If the returning value is a file path, the the instance uses the spfile. In this case you're able to set the default date format for the whole instance via:

    ALTER SYSTEM SET nls_date_format = '[Your required format]' SCOPE = SPFILE;
    

    After executing the statement you have to restart the database.

    If your instance is not using the spfile (query above returns null), you should find the init.ora file in the directory database under your oracle home directory of the server software. It is named init[SID of database instance].ora. Here you can add or set the parameter followed by an instance restart to set the default date format.

    Best regards

    Jan

  • JohnWatson2
    JohnWatson2 Member Posts: 4,329 Silver Crown

    Part of your problem is that you have a bug in your code. This,

     select to_date(sysdate, 'DD-MON-YYYY') from dual)
    

    is wrong. SYSDATE is a data type DATE, and you are passing it as an argument to TO_DATE. The TO_DATE function cannot accept a DATE argument, so Oracle is forced to make an implicit data type conversion to VARCHAR2. It does that using whatever NLS_DATE_FORMAT happens to be in effect for your session, which is where your problem is coming from. What you should be doing is probably something like this,

    select to_char(sysdate,'dd-mm-yyyy') from dual;
    

    SQL is a strongly typed language, and you must respect that or you will get all sorts of unexpected results.

  • User_GSQTY
    User_GSQTY Member Posts: 55 Bronze Badge
    edited Aug 30, 2021 3:03PM

    The value for nls_date_format that you see is set at the client level ( session ) not at the database level.

    Very likely your "old" nls_date_format was set in $ORACLE_HOME/sqlplus/admin/glogin.sql in your old oracle client .

    So go to your new oracle home client 19c and modify $ORACLE_HOME/sqlplus/admin/glogin.sql to add

    ( on windows value of "$ORACLE_HOME" is not defined - you need to find it in your registry or by looking at your path : echo %PATH% )

     alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY' ; -- or wahtever value that you want
    
  • EdStevens
    EdStevens Member Posts: 28,534 Gold Crown

    However, there is not 'dbs' folder as you explained above, Ed. I do not see $ORACLE_HOME/dbs, which is making my wonder if what you stated is my problem

    That would have been for a *nix (Linux, Solaris, AIX, HP-UX, etc), which I assumed would be recognized and translated to Windows if need be. For Windows (which I avoid every chance I get) it would be %ORACLE_HOME%\database. (Where %ORACLE_HOME% is the system environment variable that points to the installation location). But as user_GSQTY pointed out, it appears you are inheriting the value of NLS_DATE_FORMAT not from the database but as a session setting on the client. This only serves to point out the futility of relying on inheriting NLS_DATE_FORMAT at all. Personally, I don't even like setting it at the session level. I prefer to make proper use of TO_CHAR and TO_DATE, so that the format is seen explicitly at the level of the actual sql statement, thus avoiding any and all assumptions and resulting confusion.

  • User_N8412
    User_N8412 Member Posts: 3 Green Ribbon

    Thanks everyone.

    If I alter NLS_DATE_FORMAT at the session level, would this have to be re-set every time I create a new query? Or is the change permanent?

  • EdStevens
    EdStevens Member Posts: 28,534 Gold Crown

    ( on windows value of "$ORACLE_HOME" is not defined - you need to find it in your registry or by looking at your path : echo %PATH% )

    Well, actually it is defined - or should be. It's just that the syntax for referencing it is OS dependent. On *nix it is '$ORACLE_HOME', while on Windblows it is '%ORACLE_HOME%'

    While on *nix, it is $ORACLE_HOME:

    oracle:cdb$ echo $ORACLE_HOME
    /u01/app/oracle/product/19.0.0/dbhome_1
    
    oracle:cdb$
    


  • User_GSQTY
    User_GSQTY Member Posts: 55 Bronze Badge

    It's good for the entire session : that means you need to execute it only once at the begining of your session.