Re: I'll do my best to outline everything...

Doug Jones wrote:
> Version of Oracle: 10g
> Version of OS: Windows 2003
> Server Configuration:
> Xeon 2.8 Dual Core
> 5gb of RAM
> 1tb of Storage
>
> (second server is same configuration)
>
>
>
> Scenario:
> In a previous post I was discussing backup and restore to a new host.
> Well having completed that, I logged into the "destination" server.
> Here is the list of the errors I encountered.
>
> After performing a whole database backup and then a restore and
> recovery on the new server, I was unable to log in to the Oracle
> Management Console (OMC) as sysdba. It would give me "Invalid
> password". Logging in as normal would work just fine when using sysman
> for the user. At the dos prompt I issued the oradim command to change
> the password to what I thought it originally was. I was then able to
> log in. QUESTION: Why if I do a complete backup of the whole database
> would a restore not function properly. Unable to log in as sys seems to
> be a not so insignificant issue. THEORY: Password file was not part of
> the back up. Could the solution be that simple? I am going to run
> through backup and restore over and over until the bugs are gone, but
> is there any other possible solution?
>
> After logging in as sys, and checking to ensure the database was up and
> the listener was functioning I went PC side and started my software.
> Attempting to log in resulted in an error. First it stated that my
> password was expired and that I needed to change it. Upon attempting to
> change it I found that at the time I click "submit" I encounter the
> following error...
>
> SET ROLE lq_user IDENTIFIED BY TIGER2
> ORA-04063: package body "WINRLS.LQ_SECURITY_PKG" has errors ORA-06508:
> PL/SQL: could not find program unit being called:
> "WINRLS.LQ_SECURITY_PKG" ORA-06512: at line 1
>
> ...and my program crashes. So, I go into sqlplus and issue the (i
> believe) select command to show all invalid package bodies and it says
> that there are none. I log into the OMC and it says I have 488 invalid
> package bodies. Every invalid body is for the user of SYS. The
> LQ_SECURITY_PKG is also for the user SYS. So again, what would cause
> this to happen if I am restore the database to a new host? When I
> created the host I used the oradim -new -sid SIDNAME -startmode a.
> Restored my SPFILE, CONTROL FILES, and then the whole database.
> Afterward I ran restore database followed by alter database open
> resetlogs;. Fairly simple and straight forward and incounted no
> errors....
>
> Theory: Remember now that I am new with Oracle (like day 21 new). The
> Source server was created 1 week ago. The data was imported from the
> production server and there were alot of errors that needed to be
> corrected. ARCHIVELOG MODE was turned on. Could I have log files from
> within the data import phase that are being used to bring the database
> to a consistent state at the end of the recover/restore process? It was
> just a thought, but I thought that if I had log files from before the
> scripts were run to correct invalid package bodies in the database that
> it could be cause my issues. I know that might be a little crazy of an
> idea, but it was the best I could come up with.
>
>
> Thank you for all your time. I hope this is a more detailed message to
> everyone's liking.
>
>
> Doug Jones

Yes you would need to either copy the existing password file over or recreate a new one on the server you are working from. Was that step not in the article you were working from? If not I would consider checking with the authors about why that is not in there.

You can use utlrp.sql in the rdbms/admin directory to "attempt to" recompile things. I generally want to work schema by schema instead of working with a hammer but it can be useful especially when doing upgrades and oracle maintenance.

Why are they invalid in your recovered database if they were ok in the original? I generally just do a select * from dba_objects where status<>'VALID' to look at everything not just package bodies. For any of my databases I need to have a knowledge of why "anything" in any schema is invalid. Generally in 9.2 there shouldn't be anything at all. 10.2 has some quirks in that area.

You lost me with your explanation on that one sorry.

If you try running utlrp.sql and things still aren't fixed there's something that was wrong in how you proceeded along the way.

If you try running utlrp.sql Received on Thu Oct 05 2006 - 09:01:28 CDT