I've created a standby DB (Oracle 8.1.7) from my primary DB and everything seems to work fine.
Now I want to do a recovery every midnight and then open the standby DB in read only mode again. (so my users always see the data untill yesterday)
A shutdown immediate without any open connections works fine, but when I have a connection open (only one sqlplus) it hangs there for more then 30 min, and I have to use shutdown abort to close my standby DB.
Does anybody know what the problem is ? I can't find anything about it in my log files.
I also tried alter database close normal, but then I get the message that there are still open connections.
Which sessions do I have to kill in v$session (and what happen when I kill background processes) ??
Or is it safe to do a shutdown abort, startup again and shutdown immediate right after that ? (I really want to avoid this method).
If anyone knows a solution, please let me know.
Kill the Process associated to the User. Then you should be able to shutdown. And however, it is not good to use Shutdown Abort. In this case there will be no gaurantee that Database will start next.
befor you go killing process, look into them carfully, look to see if you are the only one connected. in v$session you can see the bd user, the terminal name and the os user, so no probelm there. if you have transaction running you may end up waitnig for it to finish befor you can shutdown. accourding to what you say, it sound's imposible to me, that yopu have something that should prevent the db from normaly shutiong down. do you have a dblink? You are running a multi-master replication environment or an updatable-snapshot environment ? something? shutdown immediate has to have a resone to hang there.
Thanks for your help.
Yes, I first created a multimaster replication (only on scott.emp) but that didn't work for my situation
(I couldn't replicate the table structure with it, only data).
I could remove the replication on my primary DB, but not on my standby DB because the system.dbf is in read only mode.
Any ideas how to solve that ?
first, i think the resone way your shutdown connact is hanging has to do with delay_seconds parameter value. you can check it in You knew V$LOCK, the snp process still has a TX lock to the DEF$_DESTINATION table. Since this lock is NOT released, the SHUTDOWN IMMEDIATE hangs. for more reading look at do id: Note:1018421.102 on the metalink. in this doc it is writen that
The delay_seconds parameter causes the SNP process to sleep when there are no more transactions to push or purge. When a shutdown immediate is executed, it will have to wait for the SNP process tofinish sleeping. Issuing a shutdown abort will kill all processes, including the SNP process.
acualy the workaround is simply a shutdown abort.
regarding your replication problem, i can not help you bcouse i do not have the experiance needed. what i can tell you is to look at the metalink and then, i no answer is found call oracle suopprt. from my experiance, better look in the metalink befor you call oracle support.