I cannot connect to my database as admin, even using connect descriptor neither via RMAN. I am getting TNS-12514 error.
I consider to install new instance.
Questions:
1) Can I restore/recover PLSQL code from "dead" oracle database?
2) Where is the PLSQL stored?
EDIT: My situation is described here: ORA-01033 "ORACLE initialization or shutdown in progress" caused by ORA-10567 "REDO is inconsistent with data block"
Best Answer
You need to restore/recover your database. If you can't then yes the code is buried in the system table spaces. But it won't be easy looking through the data file with a hex editor trying to find the code. You should either try to restore the database or just rewrite the code. Do you have a backup of your database? Do you have all of the data files, control files, online redo files? If you don't have a backup and your database is in an inconsistent state, then you can probably alter database recover until cancel and then point Oracle to your online redo logs to see if it can find what it needs.
Otherwise you might be out of luck. What is in your alert log for your database?