asked on

Help InitSID.ora problem

I accidently set a parameter to an invalid directory. I shutdown the database before I realized it. When I tried to start the database again I got an error that the parameter in question has an invalid directory. So I opened the spfileSID.ora and changed the directory of the parameter to a valid directory and saved it. But when I went back to startup the database again I got the following error:

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/oracle/product/app/10.2.1/dbs/initJoshua.ora'

So I issue the following command:

SQL> startup pfile='/u01/oracle/product/app/10.2.1/dbs/spfileJoshua.ora';

But I get this error:

LRM-00123: invalid character 7 found in the input file
ORA-01078: failure in processing system parameters

Here is the contents of the file:

^G^B^B^D^D$Gq^KJoshua.__db_cache_size=406847488
Joshua.__java_pool_size=25165824
Joshua.__large_pool_size=4194304
Joshua.__shared_pool_size=167772160
Joshua.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/Joshua/adump'
*.background_dump_dest='/u01/app/oracle/admin/Joshua/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u04/oradata/Joshua/control01.ctl','/u04/oradata/Joshua/control02.ctl','/u04/oradata/Joshua/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/Joshua/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='Joshua'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='/u04/flash_recover_area'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=JoshuaXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u05/archive_log'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=612368384
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/Joshua/udump'

I don't know what shouldn't be there. How do I fix this?

rbrooker

SQL> startup pfile='/u01/oracle/product/app/10.2.1/dbs/spfileJoshua.ora';

should be

SQL> startup spfile='/u01/oracle/product/app/10.2.1/dbs/spfileJoshua.ora';

if you are using an spfile...
(note: it is startup SPFILE=, not startup PFILE= )

give that a go and see what happens.

sikyala

ASKER

I tried and now I get the following error:

SQL> startup spfile=='/u01/oracle/product/app/10.2.1/dbs/spfileJoshua.ora';
SP2-0714: invalid combination of STARTUP options

sikyala

ASKER

I deleted ^G^B^B^D^D$Gq^K from the line where Joshua.__db_cache_size=406847488 is located. Then I typed startup nomount. Then I altered the parameter and assigned a valid directory. Then I mounted and opened the database. I am not sure what to do next to ensure that there is nothing wrong with the database. I tried create pfile from spfile and I got an error:

SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size Additional information: 1

How do I fix this?

SOLUTION

rbrooker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sikyala

ASKER

Well I totally blew it. I don't know how to fix it.

I have a problem. I don't have a pfile. I did copy statement before I got your message when I was trying to fix the problem.

cp $ORACLE_HOME/dbs/spfileJoshua $ORACLE_HOME/initJoshua.ora

It looks like this now in my directory:

-rw-r----- 1 oracle dba 1027 Dec 9 11:20 initJoshua.ora
-rw-r----- 1 oracle dba 1036 Dec 8 16:56 spfileJoshua.ora

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sikyala

ASKER

You are right I found a init.ora file in the pfile directory. Thanks!

slightwv (䄆 Netminder)

If you've heavily modified parameters since you created the instance, it's a little more work but:

open your old spfile in an editor and manually copy the parameters to a new init.ora making sure not to copy any of the binary data. Then you can use that pfile to generate a new spfile as already mentioned.

schwertner

Never edit SPFILE!
Before changing init.ora files do a backup copy!

slightwv (䄆 Netminder)

>>Never edit SPFILE!

Agreed but since we've already passed that point... You can still see the parameters in the SPFILE in text form if opened with a text editor. If they've modified numerous parameters since DB creation I'm only suggesting copying the text from the FUBARed SPFILE and paste them into a new PFILE then use this new PFILE to rebuild the SPFILE.

sikyala

ASKER

I was told that when I created the spfile using the create statement that it would overwrite the current spfile. Is that true? I issued the following statement:

create spfile='$ORACLE_HOME/dbs/spfileJoshua.ora' from pfile='/u01/app/oracle/admin/Joshua/pfile/init.ora.1172006175052';

sikyala

ASKER

I only have 2 parameters to alter. I tried altering the first parameter and got the following error:

SQL> alter system set log_archive_dest_1='/u05/archive_log';
alter system set log_archive_dest_1='/u05/archive_log'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

The directory does exist. I don't know what the problem is.

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sikyala

ASKER

When I typed this alter system set log_archive_dest_1='location=/u05/archive_log' scope=spfile; I still got an error because I had started the database using pfile. I shut down the database and restarted it and was able to issue the command.

Thanks!!!