Tag: Database

Database, Linux, RAC | Data Guard

PRCR-1079 CRS-2674 CRS-5017 ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

 , , ,

# Problem

myserver:/home/oracle>srvctl start database -d mydb
PRCR-1079 : Failed to start resource ora.mydb.db
CRS-5017: The resource action "ora.mydb.db start" encountered the following error:
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
. For details refer to "(:CLSN00107:)" in "/grid/product/11.2.0/log/myserver/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-2674: Start of 'ora.mydb.db' on 'myserver' failed
CRS-5017: The resource action "ora.mydb.db start" encountered the following error:
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
. For details refer to "(:CLSN00107:)" in "/grid/product/11.2.0/log/myserver2/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-2674: Start of 'ora.mydb.db' on 'myserver2' failed
CRS-2632: There are no more servers to try to place resource 'ora.mydb.db' on that would satisfy its placement policy
myserver:/home/oracle>

# Solution

On /etc/sysctl.conf ajust as below and then reload sysctl (“sysctl -p” as root):

#Old
#kernel.shmall = 24641536
#New
kernel.shmall = 4294967296

Matheus.

Database, Errors and Bugs

11.2.0.1: ORA-00600: internal error code, arguments: [7005], [0], [], [], [], [], [], [], [], [], [], []

 , ,

# Error

Errors in file /oracle/diag/rdbms/mydb/mydb_1/trace/mydb_1_ora_972.trc (incident=195818):
ORA-00600: internal error code, arguments: [7005], [0], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/mydb/mydb_1/incident/incdir_195818/mydb_1_ora_972_i195818.trc

#Cause
The query causing this error uses a CONTAINS clause on alphanumerical column using bind variables. This is a perfect match with note ORA-0600 [7005] on a Select Query Using Contains Clause (Doc ID 1176276.1), referencing the unpublished Bug 8770557 ORA-600 [7005] While Running Text Queries.
The symptoms includes this two key factors:
– presence of CONTAINS clause
– use of bind variables

# Solution
Apply 11.2.0.2 patchset or higher, where this issue is fixed or Apply one off Patch 8770557 if available for your version / platform.

See ya!
Matheus.

Backup and Restore, Database, Errors and Bugs, Tools

EXP Missing Tables on 11.2

 , ,

Made an exp and some table is missing, right? The database is 11.2+? The tables missing have no rows in source dabase, right? Bingo!
This happen because Oracle implemented a space saving feature on 11.2 called Deffered Segment Creation.

This feature basically makes that the first segment of a table is only allocated when the first row is inserted. It was implemented because Oracle realized is not rare to find databases with lots of tables that haven’t ever had a row.

The situation occurs because the EXP client uses dab_segments as index to exporting, and, this feature makes that no segment be allocated. For Oracle, it’s not a problem, considering the use of Datapump (EXPDP/IMPDP).

But (there always exist a “but”), let’s suppose you have to export the file to a different location not accessible by directory nor has local space, or either, your table has a long column (yes, it’s deprecated, I know… but let’s suppose this is a legacy system…). Then, you can do:

1) For all tables that has no rows, allocate an extent:
alter table owner.tabela allocate extent;

To generate, the script:

select 'alter table '||owner||'.'||table_name||' allocate extent;' from all_tables where num_rows=0;

2) Export using clausule VERSION=11.1 or lower on EXP. 🙂

More about Deffered Segment Creation: https://oracle-base.com/articles/11g/segment-creation-on-demand-11gr2

Hope It helped.
See ya!
Matheus.

Database, Errors and Bugs, SQL | PLSQL

ORA-04091: Table is Mutating, Trigger/Function may not see it

 , , ,

No!
This is not a super-table nor a x-table (X-Men joke, this was awfull, I know… I’m sorry).

ORA-04091: Table "TABLE NAME" is Mutating, Trigger/Function may not see it
ORA-06512: em "TRC_INSERT_TABLE", line 14
ORA-04088: error during execution of trigger 'TRC_INSERT_TABLE'

Very interesting. But not hard to understand. The cause is that the trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

In other words, your trying to read a data the you are modifying. The obviously cause an inconsistency, the reason to this error. The data is “mutant”. But the error could be less annoying, right? Oracle and his jokes…

The solution is to rewrite the trigger to not use the table, or, in some situation, you can use an autonomous transaction, to turn it independent. It can be done using the clause PRAGMA AUTONOMOUS_TRANSACTION.

This FAQ can be useful to you: http://www.orafaq.com/wiki/Autonomous_transaction

Matheus.

Database, Errors and Bugs

ORA-00600: internal error code, arguments: [ktecgetsh-inc], [2]

 , ,

Alert showing:

Errors in file /oracle/diag/rdbms/mydb/mydb/trace/mydb_smon_6024.trc (incident=9666):
ORA-00600: internal error code, arguments: [ktecgetsh-inc], [2], [], [], [], [], [], [], [], [], [], []

This is a non-fatal internal error happenned while SMON is doing a temporary segment drop. My SMON encountered 9 out of maximum 100 non-fatal internal errors.

So,

alter system set event="10061 trace name context forever, level 10" scope=spfile;

Restart Database and:

alter system reset event scope=both sid='*';

See ya!
Matheus.

Database, SQL | PLSQL

Purge SYSAUX Tablespace

 , , ,

Your SYSAUX is bigger than the rest of database?
It’s not uncommon to “old” databases, usually bad administrated. Some databases configuration must cause this situation.

The general indication is to review stats and reports retention of objects and database.

But if you need to clean it now, how to do?
1) PURGE_STATS. It’s recommended to execute in smaller steps. Otherwise the RBS tablespace will be blown up.
2) Oracle is sometimes building new extents for SYSAUX stats table in other tablespaces. They will be moved back to the SYSAUX tablespace.
3) The Index rebuild will decrease the size of the indexes. They are mostly larger as the raw data.
4) The Indexes are partly function bases. Therefore it is imported in which order the index rebuild will be done. Otherwise you have to reexecute this steps again and again.

Going practical, I used the follow:

exec DBMS_STATS.PURGE_STATS(SYSDATE-180);
exec DBMS_STATS.PURGE_STATS(SYSDATE-160);
exec DBMS_STATS.PURGE_STATS(SYSDATE-140);
exec DBMS_STATS.PURGE_STATS(SYSDATE-120);
exec DBMS_STATS.PURGE_STATS(SYSDATE-100);
exec DBMS_STATS.PURGE_STATS(SYSDATE-80);
exec DBMS_STATS.PURGE_STATS(SYSDATE-60);
exec DBMS_STATS.PURGE_STATS(SYSDATE-40);
exec DBMS_STATS.PURGE_STATS(SYSDATE-20);
exec DBMS_STATS.PURGE_STATS(SYSDATE-7);
alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_IND_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_OPR move tablespace sysaux;
alter table WRH$_OPTIMIZER_ENV move tablespace sysaux;
Alter index SYS.I_WRI$_OPTSTAT_IND_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_HH_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_TAB_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_OPR_STIME rebuild TABLESPACE SYSAUX;

Matheus.

Database, Errors and Bugs, SQL | PLSQL

CREATE MVIEW: ORA-12014: table does not contain a primary key constraint

 , , , , ,

Ok, you are trying to create a materialized view involving a database link and found a ORA-12014, right?

CREATE MATERIALIZED VIEW &OWNER..MVW_NAME
REFRESH FORCE ON DEMAND
AS SELECT COL1, COL2, COL3 FROM TABLE@REMOTE_DB;
 *
ERROR at line 1:
ORA-12014: table 'TABLE' does not contain a primary key constraint
SQL>

It blowed me sometime ago. But it’s not complicated to workaround it, just try to:

CREATE MATERIALIZED VIEW &OWNER..MVW_NAME
REFRESH FORCE ON DEMAND AS
select * from (SELECT COL1, COL2, COL3 FROM TABLE@REMOTE_DB);

An alternative is to use MV log + WITH ROWID on REMOTE_DB side:

CREATE MATERIALIZED VIEW LOG MVW_LOG_NAME
ON TABLE WITH ROWID;

And

CREATE MATERIALIZED VIEW &OWNER..MVW_NAME
REFRESH FORCE ON DEMAND WITH ROWID
AS SELECT COL1, COL2, COL3 FROM MVW_LOG_NAME@REMOTE_DB;

PS: Make sure username used in remote_db database link has select privileges on MV log. On source db issue:

SELECT LOG_TABLE FROM DBA_MVIEW_LOGS WHERE LOG_OWNER='OWNER' AND MASTER = 'TABLE';

This will give you MV log table name. On target side issue:

SELECT * FROM MVW_LOG_NAME@remote_db;

See ya!
Matheus.

Errors and Bugs, GoldenGate

OGG-0352: Invalid character for character set UTF-8 was found while performing character validation of source column

 , ,

Almost a month without post!
My bad, december is always a crazy time to DBAs, right?

This post’s title error happens because the charset is different between databases used on replication thought GoldenGate and occurs only with alphanumerical columns (CHAR, VARCHAR, VARCHAR2), because, even the char length be the same, the data length will be different (like I explained here). Take a look in this example:

sourcedb> select table_name,column_name,data_length,char_length from dba_tab_cols where column_name=’NAME' order by 1,2,3;
 
TABLE_NAME    COLUMN_NAME     DATA_LENGTH CHAR_LENGTH
------------- --------------- ----------- -----------
TABLE_EXAMPLE NAME            25          25
destdb> select table_name,column_name,data_length,char_length from dba_tab_cols where column_name=’NAME' order by 1,2,3;

TABLE_NAME  COLUMN_NAME     DATA_LENGTH CHAR_LENGTH
------------- --------------- ----------- -----------
TABLE_EXAMPLE NAME            100         25

 

There is basically two solutions:
1) Change one of those charsets.
2) Add “SOURCECHARSET PASSTHRU” clause on the replicat file.

I usually prefer the second option, just because it’s less intrusive than number 1.

 

See ya!
Matheus.

Database, Errors and Bugs, Linux, RAC | Data Guard

VKTM Hang – High CPU Usage

 , , ,

Today a database (RHEL 6, single instance, 11.2.0.4) suddently started to “explode” CPU on VKTM process (100% CPU).
After some minutes lost (completely) in support.oracle.com (there was just a few notes about binary permissions on Solaris), I decided to make a McGayver by myself. 🙂

By Oracle words: “VKTM acts as a time publisher for an Oracle instance. VKTM publishes two sets of time: a wall clock time using a seconds interval and a higher resolution time (which is not wall clock time) for interval measurements. The VKTM timer service centralizes time tracking and offloads multiple timer calls from other clients.”

This way, my solution:

SQL> alter system set "_high_priority_processes"='LMS*' scope=spfile;
System altered.

And restart the database, of course.
So, VKTM is no more a “priority” process. The problem was solved. 🙂

Another possibility is to disable VKTM (undocumented parameter “_disable_vktm” – boolean). But I wanted to keep it running, changing less as possible of database configuration, just reducing priority.

KB:
Master Note: Troubleshooting Oracle Background Processes (Doc ID 1509616.1)
Great post about hidden parameters: http://oracleinaction.com/undocumented-params-11g/
Oficial one: http://www.orafaq.com/parms/index.htm

Hugs!
Matheus.