Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for the ‘ORA- ERRORS relating to ORACLE DATABASE’ Category

ORA- ERRORS relating to ORACLE DATABASE.
This category has Oracle errors encountered in real-time and Solutions / Fix provided.

Thread 1 cannot allocate new log, sequence 36286 Checkpoint not complete while doing import

Posted by Hendry chinnapparaj on June 14, 2012

Problem:- Oracle10g Import is slow and see the warnings in the Oracle Alert Log

Thread 1 advanced to log sequence 36285 (LGWR switch)
Current log# 2 seq# 36285 mem# 0: F:\ORACLE\ORADATA\MZSTWCD1\MZSTWCD1_REDO02A.LOG
Current log# 2 seq# 36285 mem# 1: G:\ORACLE\ORADATA\MZSTWCD1\MZSTWCD1_REDO02B.LOG
Thread 1 cannot allocate new log, sequence 36286
Checkpoint not complete

Cause:- 

The “checkpoint not complete” messages are generated because the logs are switching so fast that the checkpoint associated with the log switch isn’t complete.

During that time, Oracle’s LGWR process has filled up the other redo log groups and is now waiting for the first checkpoint to successfully execute.

Remember, Oracle will stop processing until the checkpoint completes successfully!

Solution:- It’ll take a while to complete. Examine your redologs size, increase the Size so that it can keep up with completing the checkpoint without waiting. Increase the redolog size so that you can have like 4 switches in an hour and add additional one /more redo logs if required.

Shalom

Hendry

Posted in ORA- ERRORS relating to ORACLE DATABASE | Leave a Comment »

streams_pool_size defaulting to 179516211 in oracle alert log

Posted by Hendry chinnapparaj on June 16, 2011

streams_pool_size defaulting to 179516211 in oracle alert log

 

Problem

———-

The streams_pool_size dynamically changed from 0 to 179516211

and the following were recorded in the oracle alert log indicating issues related to data pump.

 

streams_pool_size defaulting to 179516211

kupprdp: master process DM00 started with pid=61, OS id=39190678

to execute – SYS.KUPM$MCP.MAIN(‘SYS_EXPORT_FULL_01’, ‘OPS$ORACLE’, ‘KUPC$C_1_20110516040004’, ‘KUPC$S_1_20110516040004’, 0);

kupprdp: worker process DW01 started with worker id=1, pid=115, OS id=14024736

to execute – SYS.KUPW$WORKER.MAIN(‘SYS_EXPORT_FULL_01’, ‘OPS$ORACLE’);

Mon May 16 04:29:12 2011

streams_pool_size defaulting to 179516211

streams_pool_size        = 184549376

 

alert_zoeprd.log.110514:  streams_pool_size        = 0

alert_zoeprd.log.110521:streams_pool_size defaulting to 179516211

alert_zoeprd.log.110521:  streams_pool_size        = 184549376

 

alert_zoeprd.log.110514:  db_cache_size            = 1291845632

alert_zoeprd.log.110521:  db_cache_size            = 1107296256

 

 

Cause

———

Oracle11g  doc has the following explanation

Setting the Size Of the Buffer Cache In a Streams Environment

Oracle Data Pump uses Streams functionality to communicate between processes. If the SGA_TARGET initialization parameter is set, then the STREAMS_POOL_SIZE initialization parameter is automatically set to a reasonable value.

If the SGA_TARGET initialization parameter is not set and the STREAMS_POOL_SIZE initialization parameter is not defined, then the size of the streams pool automatically defaults to 10% of the size of the shared pool.

When the streams pool is created, the required SGA memory is taken from memory allocated to the buffer cache, reducing the size of the cache to less than what was specified by the DB_CACHE_SIZE initialization parameter. This means that if the buffer cache was configured with only the minimal required SGA, then Data Pump operations may not work properly. A minimum size of 10M is recommended for STREAMS_POOL_SIZE in order to ensure successful Data Pump operations.

Oracle10g has the details

Oracle’s Automatic Shared Memory Management feature manages the size of the Streams pool when the SGA_TARGET initialization parameter is set to a nonzero value. If the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value, then Automatic Shared Memory Management uses this value as a minimum for the Streams pool.

If the STREAMS_POOL_SIZE initialization parameter is set to a nonzero value, and the SGA_TARGET parameter is set to 0 (zero), then the Streams pool size is the value specified by the STREAMS_POOL_SIZE parameter, in bytes.

If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero), then, by default, the first use of Streams in a database transfers an amount of memory equal to 10% of the shared pool from the buffer cache to the Streams pool.

 

Setting the Size Of the Buffer Cache In a Streams Environment

As of Oracle Database 10g release 10.2, if Streams functionality is used, but the STREAMS_POOL_SIZE initialization parameter is not defined, then the size of the streams pool automatically defaults to 10% of size of the shared pool.

When the streams pool is created, the required SGA memory is taken from memory allocated to the buffer cache, reducing the size of the cache to less than what was specified by the DB_CACHE_SIZE initialization parameter. This means that if the buffer cache was configured with only the minimal required SGA, then Data Pump operations may not work properly.

Here it doesn’t mention the minimum requirement of 10m.

Fix

—-

If you are using data pump, then increase the size of streams_pool_size to atleast 10m as recommended by oracle and increase the buffer_cache_size to it’s original size

Alter system set streams_pool_size=10m scope=spfile sid=’*’;

Alter system set db_cache_size=1291845632 scope=spfile sid=’*’;

And bounce the database

Posted in ORA- ERRORS relating to ORACLE DATABASE | Leave a Comment »

ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, line 488

Posted by Hendry chinnapparaj on February 4, 2011

Problem

————–

When doing export form datapump

ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 488

Solution

————–

create the unix directory and the data pump directory in oracle

DROP DIRECTORY DATA_PUMP_DIR;

 

CREATE OR REPLACE DIRECTORY

DATA_PUMP_DIR AS

‘/u01/oracle/admin/msoa1_js/dpdump/’;

 

 

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO dp WITH GRANT OPTION;

 

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO EXP_FULL_DATABASE;

 

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO IMP_FULL_DATABASE;

 

Posted in ORA- ERRORS relating to ORACLE DATABASE | Leave a Comment »

EXP-00056: ORACLE error 1017 encountered ORA-01017: invalid username/password; logon denied

Posted by Hendry chinnapparaj on February 4, 2011

Problem

————–

The OS Authentication fails due to the missing user OPS$ORACLE

Cpsoa1:/u01/oracle> exp /

Export: Release 10.2.0.4.0 – Production on Tue Jan 11 13:52:43 2011

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

EXP-00056: ORACLE error 1017 encountered

ORA-01017: invalid username/password; logon denied

Username:

Password:

Solution

————

create user and grant permission for OS Authentication

CREATE USER OPS$ORACLE

IDENTIFIED EXTERNALLY

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

— 4 Roles for OPS$ORACLE

–GRANT DATAPUMP_EXP_FULL_DATABASE TO OPS$ORACLE;

GRANT IMP_FULL_DATABASE TO OPS$ORACLE;

–GRANT DATAPUMP_IMP_FULL_DATABASE TO OPS$ORACLE;

GRANT EXP_FULL_DATABASE TO OPS$ORACLE;

ALTER USER OPS$ORACLE DEFAULT ROLE ALL;

— 1 Tablespace Quota for OPS$ORACLE

ALTER USER OPS$ORACLE QUOTA UNLIMITED ON USERS;

Grant create session to OPS$ORACLE;

Posted in ORA- ERRORS relating to ORACLE DATABASE | Leave a Comment »

ORA-06512: at “SYS.OLAPIHISTORYRETENTION”, line 1

Posted by Hendry chinnapparaj on January 24, 2011

problem

—————–

SQL> startup pfile=initTEST.ora
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  2083760 bytes
Variable Size             100664400 bytes
Database Buffers          205520896 bytes
Redo Buffers                6303744 bytes
Database mounted.
ORA-03113: end-of-file on communication channel

The alert log has

replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=1220658
Mon Jan 24 16:56:35 2011
Errors in file /u01/oracle/admin/TEST/udump/test_ora_2367508.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
ORA-06512: at “SYS.OLAPIHISTORYRETENTION”, line 1
ORA-06512: at line 15
Mon Jan 24 16:56:35 2011

and the trace file has

Instance name: TEST
Redo thread mounted by this instance: 1
Oracle process number: 14
Unix process pid: 2367508, image: oracle@uatu009 (TNS V1-V3)

*** ACTION NAME:() 2011-01-24 16:56:34.754
*** MODULE NAME:(sqlplus@uatu009 (TNS V1-V3)) 2011-01-24 16:56:34.754
*** SERVICE NAME:(SYS$USERS) 2011-01-24 16:56:34.754
*** SESSION ID:(160.3) 2011-01-24 16:56:34.754
kwqmnich: current time::  8: 56: 34
kwqmnich: instance no 0 check_only flag 1
kwqmnich: initialized job cache structure
Error in executing triggers on database startup
*** 2011-01-24 16:56:35.565
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
ORA-06512: at “SYS.OLAPIHISTORYRETENTION”, line 1
ORA-06512: at line 15

 

Solution

—————-

query for the triggers that fires when the database startsup and disable them

SQL> SELECT TRIGGER_NAME FROM DBA_TRIGGERS WHERE TRIGGERING_EVENT LIKE ‘%STARTUP%’
2  ;

TRIGGER_NAME
——————————
AURORA$SERVER$STARTUP
OLAPISTARTUPTRIGGER

SQL> alter trigger AURORA$SERVER$STARTUP disable;

Trigger altered.

SQL> alter trigger OLAPISTARTUPTRIGGER disable;

Trigger altered.

OLAP Trigger may not be needed in this case for us

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  2083760 bytes
Variable Size             100664400 bytes
Database Buffers          205520896 bytes
Redo Buffers                6303744 bytes
Database mounted.
Database opened.

 

No errors

 

Posted in ORA- ERRORS relating to ORACLE DATABASE | Leave a Comment »

ORA-12705: Cannot access NLS data files or invalid environment specified

Posted by Hendry chinnapparaj on January 7, 2011

PROBLEM

————–

The following error occurred when trying to connect to sql*plus to run a script.

ORA-12705: Cannot access NLS data files or invalid environment specified

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jan 7 10:09:37 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

Solution

————

check the NLS_LANG unix env setting

export NLS_LANG=”AMERICAN_AMERICA.US7ASCII”

In the above case, the “A” was missing – no more errors after adding the”A” for the AMERICAN.

Posted in ORA- ERRORS relating to ORACLE DATABASE | Leave a Comment »