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.

Oracle DBA – Beginner’s essential Guide

Preface:

   This article is mainly aiming for giving a solid idea about Oracle for the beginners and the Job seekers.

Role of DBA:

   In order to become a fully fledged DBA, you need to have an understanding of the basic concepts about oracle architecture and DBA responsibilities. This article aims to provide the fundamentals of what a DBA is and what a DBA does in their daily role.   DBAs are highly paid, due to the nature of their responsibilities, risks and technical knowledge. The Database Administrator (DBA) takes care of all of the security, manage, data management and efficient running of an organizations database. Backup and recovery are also one of the essential parts of the role. The DBA must frequently make recommendations for improvement and future strategy. The DBA must liaise frequently with co-workers and other departments who rely on the database for information. During out of office hours also DBA has to support their critical database with extra vigilance in order to keep the organizations database operational. DBA always would be more careful to maintain the data integrity. . Developers rely on the database for their working platform. Therefore it not only affects the users but developers of products and applications. Another task performed by the DBA is the evaluation and comparison of DBMS’s, so that the correct product can be selected to meet the database and customer specification.

Why Oracle

I was always wondering why people are still behind Oracle. I’ve done a basic analysis and found out some of the following. Hope the below will clarify all your doubts.

Disclaimer and Update: I'm now working on Oracle Platform. So keep that in mind when you read these posts. The topic I posted here is really based on my knowledge. Even I know, there are some advantages for SQL as well. Please rectify me if you find anything wrong which I’ve posted here. I’m happy to remove any point which seems to be incorrect.

Oracle is available on multiple platforms such as Windows, all flavors of Unix from vendors such as IBM, Sun, Digital, HP, Sequent, etc. and VAX-VMS, as well as MVS. The multi-platform nature of Oracle makes it a true enterprise solution. Oracle and Microsoft SQL Server are competitors in the enterprise database market space. Oracle database software is the market leader with a 44.3 percent market share, while Microsoft SQL has an 18.5 percent market share. Oracle is an order of magnitude more flexible and robust that SQL Server. However, market share does not tell the whole story--Microsoft has regularly introduced new features to ensure it remains competitive.

Ø    DBAs can perform typical administrative functions in 41 percent less time when using Oracle Database 11g compared to Microsoft SQL Server 2008.

Ø    Oracle have more sophisticated partitioning including multiple partition types (range, list, hash) and subpartitioning. SQL Server is limited to range partitioning and no subpartitioning

Ø    Oracle Database 11g requires 43 percent fewer steps for the same set of standard RDBMS tasks than Microsoft SQL Server 2008 using Edison’s metric for complexity assessment.

Ø    Oracle Database provides support for developing, storing, and deploying Java applications. Bringing Oracle and Java together in our development environment gives us an exceptionally flexible and robust application development and data-handling infrastructure that fronts an excellent data management system and is easily extended in enterprise resource planning. On top of that, it’s extremely Internet-friendly, an increasingly important consideration. SQL server doesn’t have no Java database engine as in Oracle.

Ø      In SQL Server there is no "dead connection detection". Clients who lose their connection may still hold locks until a DBA is notified to kill their server side processes.

Ø    Benefiting from increased DBA productivity due to lower complexity and higher efficiency cited above, businesses could save up to $33,520.47 per year per DBA by using Oracle Database 11g rather than Microsoft SQL Server 2008.

Why Oracle Wins

Oracle is the first successful commercial RDBMS, they have helped set the standard.

It runs on any OS you can think of, including UNIX, Windows and Mac OSX

You can run it for free. If you consider it’s supported on Linux, then it gets even cheaper. Just find some decent hardware, and you’re good to go.

You can create, populate, query, manage,  backup, & destroy a database all on the command-line. No tools necessary.

Even Oracle support GUI.

You can control it over the web, if that’s your kind of thing.

There’s no shortage of jobs that require Oracle expertise. If you know Oracle, you should be able to find a job. I don’t see this changing anytime soon.

Oracle jobs generally come with a very good salary.

Advantages of Oracle over SQL Server 

In SQL Server, the DBA has no "real" control over sorting and cache memory allocation. The memory allocation is decided only globally in the server properties memory folder, and that applies for ALL memory and not CACHING, SORTING, etc.

No range portioning of large tables and indexes. In Oracle, a large 100 GB table can be seamlessly partitioned at the database level into range partitions. For example, an invoice table can be partitioned into monthly partitions. Such partitioned tables and partitioned indexes give performance and maintenance benefits and are transparent to the application.

There are no function-based indexes in SQL Server.

There is no partitioning in SQL Server.

There are no reverse key indexes in SQL Server.

All pages (blocks) are always 8k and all extents are always 8 pages (64k). This means you have no way to specify larger extents to ensure contiguous space for large objects.

There are no bitmap indexes in SQL Server.

There is no star query optimization in SQL Server.

 PL/SQL is better in error exception handling, and there is no such thing as exceptions in T-SQL (T-SQL uses @@error -- not elegant!).

In SQL Server there is no such thing as SQL*NET aliases/service names! This means applications have to hard code the actual server name into their apps, making it difficult to move databases later to load balance across servers.

No ability to read/write from external files from a stored procedure.

PL/SQL has many DBMS system packages, where T-SQL relies only on a limited number of extended and system stored procedures in the master database.

Stored procedures in SQL Server are not compiled until executed (which means overhead, late binding and finding errors at execution time only!).

We cannot declare public or private synonyms.

No "before" event triggers (only "after" event triggers) and no row triggers (only statement).

There is no such thing as independent sequence objects.

There are no packages; i.e., collections of procedures and functions.