The fact I would even post this suggests that I am not an Oracle DBA. Shamefully, I attended the Oracle Administrator I course a couple years ago but am no better off because I just can't find the time. Anyway, I find myself having to fiddle with Oracle on and off beyond the basic startup, shutdown and writing scripts to replace my DBA. Yes, I must admit, I don't micromanage, I just automate it >:-}
I wrote some scripts some time ago to do various things such as setting Oracle's db_file_name_convert and log_file_name_convert in a larger project that used Oracle's RMAN to automatically duplicate databases. Some time later, we added another database to the cluster. This exploited a "bug" in the code (mine, yes) which set the first parameter to db_file_name_convert empty. So, the bad entry in the spfile looked like 'db_file_name_convert = '', '/path/to/db'. Just to save some time, I will recreate what happened:
For some of us, you're in trouble. Why? Because looking up those error codes reveals very little information. After digging around for a while on Google, there were hints to something foul in the spfile. I opened up the spfile and noticed that erroneous line db_file_name_convert.
From previous experience, you cannot edit the spfile with a tool like vim because it is a binary file and vim somehow fouls this. I thought I'd be clever and do it with sed. But, alas, this did not work, either.
The easiest way to fix this is simply use good ol' strings to make a pfile from the spfile and recreate the spfile. Apparently, besides the binary padding, the spfile is the same as the pfile. Maybe some Oracle DBAs will have something to say about this:
Now, edit this file with your favorite text editor and fix the erroneous line because a pfile is a plain ol' text file. After that, recreate the spfile from this pfile and start Oracle.
I wrote some scripts some time ago to do various things such as setting Oracle's db_file_name_convert and log_file_name_convert in a larger project that used Oracle's RMAN to automatically duplicate databases. Some time later, we added another database to the cluster. This exploited a "bug" in the code (mine, yes) which set the first parameter to db_file_name_convert empty. So, the bad entry in the spfile looked like 'db_file_name_convert = '', '/path/to/db'. Just to save some time, I will recreate what happened:
SQL> alter system set db_file_name_convert = '', '/opt/ora-three/DEVEL3' scope=spfile sid='*'; System altered. SQL> shutdown abort ORACLE instance shut down. SQL> startup ORA-01078: failure in processing system parameters LRM-00117: syntax error at ',' at the start of input SQL>
For some of us, you're in trouble. Why? Because looking up those error codes reveals very little information. After digging around for a while on Google, there were hints to something foul in the spfile. I opened up the spfile and noticed that erroneous line db_file_name_convert.
From previous experience, you cannot edit the spfile with a tool like vim because it is a binary file and vim somehow fouls this. I thought I'd be clever and do it with sed. But, alas, this did not work, either.
ORA-27046: file size is not a multiple of logical block size Additional information: 1
The easiest way to fix this is simply use good ol' strings to make a pfile from the spfile and recreate the spfile. Apparently, besides the binary padding, the spfile is the same as the pfile. Maybe some Oracle DBAs will have something to say about this:
$ strings spfileDEVEL3.ora > pfile.ora
Now, edit this file with your favorite text editor and fix the erroneous line because a pfile is a plain ol' text file. After that, recreate the spfile from this pfile and start Oracle.
SQL> create spfile from pfile='/opt/ora-three/pfile.ora'; File created. SQL> startup nomount ORACLE instance started. Total System Global Area 788529152 bytes Fixed Size 2076008 bytes Variable Size 230687384 bytes Database Buffers 549453824 bytes Redo Buffers 6311936 bytes SQL>
