Database crashes with ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [], [], [], [], [], []

 Cause

Unpublished Bug: 4483084 says that this particular ORA-600 error can be ignored as it means nothing - there is no error.

This ORA-600 is in itself harmless and indicates that some other activity was taking place in the database at the time of shutdown; it can be safely ignored as there is no problem with the database.

Solution

1. Upgrade to database version 11.1.0.6 when it is available

OR as a work around after seeing the error

2  use addbctl.sh script to start the Database again

OR

3.  After googling in the web, two triggers were suggested to resolve this issue:

CREATE or replace TRIGGER flush_shared_pool

BEFORE SHUTDOWN ON DATABASE

BEGIN

  execute immediate 'ALTER SYSTEM FLUSH SHARED_POOL';

EXCEPTION

  WHEN OTHERS THEN

  RAISE_APPLICATION_ERROR (num => -20000, msg => 'Error flushing pool');

END;

CREATE or replace TRIGGER flush_shared_pool

BEFORE SHUTDOWN ON DATABASE

BEGIN

  execute immediate 'alter TRIGGER SYS.OLAPISTARTUPTRIGGER DISABLE';

  execute immediate 'ALTER TRIGGER SYS.OLAPISHUTDOWNTRIGGER DISABLE';

  execute immediate 'ALTER SYSTEM FLUSH SHARED_POOL';

  execute immediate 'alter TRIGGER SYS.OLAPISTARTUPTRIGGER ENABLE';

  execute immediate 'ALTER TRIGGER SYS.OLAPISHUTDOWNTRIGGER ENABLE';

EXCEPTION

  WHEN OTHERS THEN

  RAISE_APPLICATION_ERROR (num => -20000, msg => 'Error flushing pool');

END;

Alert log

Wed Jan 05 06:10:01 2011
ARCH shutting down
ARC0: Archival stopped
Wed Jan 05 06:10:02 2011
Thread 1 closed at log sequence 12677
Successful close of redo thread 1
Wed Jan 05 06:10:02 2011
Completed: ALTER DATABASE CLOSE NORMAL
Wed Jan 05 06:10:02 2011
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
System State dumped to trace file d:\oracle\product\10.2.0\admin\ORCLPROD\udump\ORCLPROD_ora_828.trc
Wed Jan 05 06:10:06 2011
Errors in file d:\oracle\product\10.2.0\admin\ORCLPROD\udump\ORCLPROD_ora_828.trc:
ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [], [], [], [], [], []

How to find out oracle and OS version is either 32 bit or 64bit

Oracle

SQL> select length(addr)*4 || '-Bits' Word_length_32_or_64 from v$process where rownum=1;

WORD_LENGTH_32_OR_64

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

32-Bits

OS – UNIX

db0211> isainfo -kv

64-bit sparcv9 kernel modules

RMAN-06089: archived log /u01/oradata/MASTERDB/arch/log125552_1.arc not found or out of sync with catalog

Problem:

========

When you use RMAN to backup the archivelogs against your database, you got the following errors:

RMAN> run {

2> allocate channel c1 type disk format ' /u01/oradata/MASTERDB/arch/’;

3> backup  archivelog all;

4> release channel d1;

5>}

RMAN-03026: error recovery releasing channel resources

RMAN-08031: released channel: c1

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

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

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

RMAN-03002: failure during compilation of command

RMAN-03013: command type: backup

RMAN-06089: archived log /u01/oradata/MASTERDB/arch/log125552_1.arc not found or out of sync with catalog

Cause:

=====

The RMAN-06089 error occurs because RMAN is unable to backup archive logs at the archive destination.

There's a mismatch of archive logs between what is in controlfile/recoverY catalog and what is physically on disk. The reason is, someone removed archivelogs on the OS manually.

Solution:

=========

Try the following

Oracle 8i
rman> allocate channel for maintenance type disk;
rman> change archivelog all crosscheck;

Oracle 9i
rman> allocate channel for maintenance type disk;
rman> crosscheck archivelog all;

If catalog Database is there. Please do the following before and after the crosscheck command

rman> resync catalog;

This should change the status of the missing archivelogs to 'EXPIRED', after this RMAN will no longer try to backup these missing files.

Example

======

RMAN> connect catalog rman/manager@rmanDB;

RMAN-06008: connected to recovery catalog database

RMAN> Change Archivelog All Validate;

RMAN-03022: compiling command: change

RMAN-03025: performing implicit partial resync of recovery catalog

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-06158: validation succeeded for archived log

RMAN-08514: archivelog filename=/u01/oradata/MASTERDB/arch/log157079_1.arc recid=156817 stamp=772115838

RMAN-06158: validation succeeded for archived log

RMAN-08514: archivelog filename=/u01/oradata/MASTERDB/arch/log157080_1.arc recid=156815 stamp=772115830

RMAN-06158: validation succeeded for archived log

RMAN-08514: archivelog filename=/u01/oradata/MASTERDB/arch/log157081_1.arc recid=156816 stamp=772115830

RMAN-06158: validation succeeded for archived log

RMAN-08514: archivelog filename=/u01/oradata/MASTERDB/arch/log157082_1.arc recid=156818 stamp=772115843

After backup

RMAN> run {

2> allocate channel c1 type disk format ' /u01/oradata/MASTERDB/arch/’;

3> backup  archivelog all;

4> release channel d1;

5>}

RMAN-03022: compiling command: backup

RMAN-03025: performing implicit partial resync of recovery catalog

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03023: executing command: backup

RMAN-08009: channel c1: starting archivelog backupset

RMAN-08502: set_count=63583 set_stamp=772375289 creation_time=12-JAN-12 12:41:29

RMAN-08014: channel c1: specifying archivelog(s) in backup set

RMAN-08504: input archivelog thread=1 sequence=157079 recid=156817 stamp=772115838

RMAN-08504: input archivelog thread=1 sequence=157080 recid=156815 stamp=7721

.

.

Thanks for reading. Enjoy learning. J

Contact me on sulfidba@gmail.com for further assistance.

Why flush shared pool is required

On systems which use a lot of literal SQL the shared pool is likely to fragment over time such that the degree of concurrency which can be achieved diminishes. Flushing the shared pool will often restore performance for a while as it can cause many small chunks of memory to be coalesced.

After the flush there is likely to be an interim spike in performance as the act of flushing may remove sharable SQL from the shared pool but does nothing to improve shared pool fragmentation. To clear the shared pool, issue the following:

Alter system flush shared_pool;

Real-time scenario

I’ve faced a wired issue during my support for one of the critical customer. Performance of the DB was OK at the beginning of the day and by late of the day performance was totally unacceptable. Investigation showed that application was generating ad hoc SQL without using bind variables. The generation of ad-hoc SQL and the nonexistence of required bind variables were resulting in propagation of non-reusable code fragments in the shared pool. We have asked application team to use more bind variables to resolve such kind of Performance issue for a stable performance.

SGASTAT : Before Flushing the shared pool:

SQL> select * from v$sgastat;
fixed_sga 735504
buffer_cache 3187671040
log_buffer 264192
shared poolerrors 443048
shared poolenqueue 1570544
shared poolKGK heap 33304
shared poolKQR L PO 3083920

SGASTAT : After Flushing the shared pool:

SQL> alter system flush shared_pool;

SQL> select * from v$sgastat;
fixed_sga 735504
buffer_cache 3187671040
log_buffer 264192
shared poolenqueue 1570544
shared poolKGK heap 33304
shared poolKQR L PO 1064000

Note :

Flush shared pool does not clear shared SQL and PL/SQL areas for SQL statements, stored procedures, functions, packages or triggers that are currently being executed or for SQL SELECT statements for which all rows have not yet been fetched.

             As a fix, we have done flushing the shared pool couple of times, resulting that all other query returned again in less than a second. As a permanent fix, we have scheduled a corn job which is firing at the mid night to flush the shared pool.

ORA-01031 insufficient privs error : connect sys/password@SID as sysdba doesn't work

sqlplus '/as sysdba'

ERROR:
ORA-01031: insufficient privileges

sqlplus /nolog

SQL> connect sys/passwd as sysdba
ERROR:
ORA-01031: insufficient privileges

The ORA-01031: “insufficient privileges” error occurs when you attempt to execute a program or function for which you have not been granted the appropriate privileges.   For the DBA, the ORA-01031 can happen if the target OS executables do not have read and execute permissions (e.g. (770) in UNIX/Linux), and ensure that the oracle user is a member of the dba group (e.g. /etc/group). 

There is similar permission in the Windows registry.

The below steps will help you resolve this issue.

Resolution Steps: for DBAs

- Make sure that the user is part of DBA group

-change sys password using orapwd

orapwd file=$ORACLE_HOME/dbs/orapwDB1 password=system entries=10

or

orapwd file ORACLE_HOME\dbs\orapwDB1 password=system entries=10

-remote_login_passwordfile exclusive

-         If not working, just restart the database and listener.

-         If it is non system/sys user, ask DBA to grant necessary privileges to connect the database.

Windows specific steps

1. Checked that the Task Scheduler service in the Services control
panel of NT is logging on as a user that is part of the ORA_DBA or
ORA_<SID>_DBA group.

2. REMOTE_LOGIN_PASSWORDFILE is set to SHARED or EXCLUSIVE in
the Oracle parameter file (init.ora).

3. Verify that SQLNET.AUTHENTICATION_SERVICES=NTS is set in the
SQLNET.ORA file


If you're using Windows NT, do:

1) Open up "My Computer"
2) Open up "Scheduled Tasks"
3) On the toolbar, click on "Advanced"
4) Click on "AT Service Account"
5) Choose "This Account:"
6) Specify an NT account name and password that is part of the ORA_DBA or ORA_<SID>_DBA group


If you're using Windows 2000, do:

1) Open "Control Panel"
2) Click on "Scheduled Tasks"
3) On the toolbar, click on "Advanced"
4) Click on "AT Service Account"
5) Choose "This Account:"
6) Specify a Windows 2000 account name and password that is part of the ORA_DBA or ORA_<SID>_DBA group

SQL*Plus: Release 9.2.0.3.0 - Production on Thu Nov 20 10:44:43 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> connect sys/password1 as sysdba
Connected.