Troubleshooting Oracle Rman Backup Error (iv) continue to purge zombie sessions in the database

With the previous effort, all the jobs in the database are back to normal, there are no long-running sessions that hold locks, and there are no long-running transactions in the transaction view:

Sql> SELECT instance_name from v$instance;

Instance_name

----------------

Tradedb1

1 rows have been selected.

Sql> SELECT SID, TYPE, ID1, ID2, Lmode, REQUEST, CTIME, block

2 from V$lock

3 WHERE CTIME > 86400

4 and CTIME < 864000;

No rows selected

Sql> SELECT ADDR, start_date

2 from V$transaction

3 WHERE start_date < TRUNC (sysdate);

No rows selected

Sql> SELECT SID, JOB, Last_date, this_date

2 from Dba_jobs_running;

No rows selected

Check another instance:

Sql> SELECT instance_name from v$instance;

Instance_name

----------------

Tradedb2

Sql> SELECT SID, TYPE, ID1, ID2, Lmode, REQUEST, CTIME, block

2 from V$lock

3 WHERE CTIME > 86400

4 and CTIME < 864000;

No rows selected

Sql> SELECT ADDR, start_date

2 from V$transaction

3 WHERE start_date < TRUNC (sysdate);

No rows selected

Sql> SELECT SID, JOB, Last_date, this_date

2 from Dba_jobs_running;

No rows selected

Although it appears that the database status is normal, the start Rman connection database still complains:

bash-3.00$ Rman Target/

Recovery manager: Release10.2.0.3.0-production on Tuesday May 26 16:54:55 2009

Copyright (c) 1982, +, Oracle. All rights reserved.

RMAN-06900: Warning: Unable to generate v$rman_status or v$rman_output rows

RMAN-06901: Warning: Disable update of v$rman_status and V$rman_output rows

Oracle error from Target database:

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR message STACK follows ===============

RMAN-00571: ===========================================================

RMAN-00554: Internal Recovery Manager Package initialization failed

RMAN-06003: Oracle Error from Target database:

ORA-03114: Not connected to Oralce

Although there is no complete solution to the problem, but the previous operation is still valid, then the job has been rerun, and the smooth end, since the rman needs of the resources are still locked, indicating that there are still some zombie session has not been cleared, check the session, according to the time to find a suspicious problem session, And based on the actual waiting session to determine whether the need to clear in the background:

Sql> SELECT SID, USERNAME, program, Service_Name, Logon_time

2 from V$session

3 WHERE logon_time BETWEEN to_date (' 2009-5-23 ', ' yyyy-mm-dd ')

4 and To_date (' 2009-5-25 ', ' yyyy-mm-dd ')

5 order by 5;

SID USERNAME program Service_Name logon_time

---- -------------- ----------------------------- --------------------- -------------------

299 Zhejiang sys$users 2009-05-23 17:01:01

Zhejiang sys$users 2009-05-23 18:27:00

145 Zhejiang sys$users 2009-05-23 18:27:00