Error import_update_catalog Using emcli

Today I get the next error when I was importing an offline repository to the OMS (Version 12c);

 
Error: The command name "import_update_catalog" is not a recognized command.
Run the "help" command for a list of recognized commands.
You may also need to run the "sync" command to synchronize with the current OMS.

The command was;

 
./emcli import_update_catalog -file=/oracle/anr_donwloads/p9348486_112000_Generic.zip -omslocal

Like the error message show us, you need to execute a synchronize, but you need first log, as show below;

 
oms> ./emcli login -username='SYSMAN'
Enter password : 

Login successful 

oms> ./emcli sync
Synchronized successfully

### Reexecute the import command 

HTH – Antonio NAVARRO

Advertisements

ORA-00600 kcratr_nab_less_than_odr Starting Database

When a database are performing crash recovery abort with ora-00600 at openning. The error is the next;

 

 
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [2120], [54473], [54480], [], [], [], [], [], [], []

The problem is a logical corruption in the controlfile, you have perform the following steps to solve it. Please, is a good idea to take a backup.

 
SQL> startup nomount;

SQL> select a.member, a.group#, b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT' ;

MEMBER                                GROUP#  STATUS
------------------------------------- ------- ---------
/dsk45/rman/rdo1/RMAN/redo01.rdo      2       CURRENT  
/dsk45/rman/rdo2/RMAN/redo02.rdo      2       CURRENT

SQL> shutdown abort;

SQL> startup mount;
ORACLE instance started.

Total System Global Area 5010685952 bytes
Fixed Size                  2236968 bytes
Variable Size             956304856 bytes
Database Buffers         4043309056 bytes
Redo Buffers                8835072 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel ;
ORA-00279: change 35290801 generated at 07/18/2016 04:01:28 needed for thread 1
ORA-00289: suggestion :
/dsk10/rman/fra/RMAN/archivelog/2016_07_22/o1_mf_1_2120_%u_.arc
ORA-00280: change 35290801 for thread 1 is in sequence #2120

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dsk45/rman/rdo1/RMAN/redo01.rdo
Log applied.
Media recovery complete.
SQL>  Alter database open resetlogs ;

Database altered.

SQL>

HTH – Antonio NAVARRO

 

ORA-19566 In Sysaux Tablespace

The backup team report the next error;

 

 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/26/2016 04:03:17
ORA-19566: exceeded limit of 0 corrupt blocks for file /dsk45/rman/data_12/sysaux_01.dbf

When I execute a dbv command for this file get the next output;

 
$ dbv file=/dsk4/rmandb/prod/datos/RMAN/sysaux01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Tue Jul 26 11:01:47 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /dsk45/rman/data_12/sysaux_01.dbf
Page 92161 is marked corrupt
Corrupt block relative dba: 0x00816801 (file 2, block 92161)
Bad header found during dbv:
Data in bad block:
 type: 245 format: 4 rdba: 0x000003e7
 last change scn: 0x0000.00000001 seq: 0x0 flg: 0x00
 spare1: 0x5 spare2: 0xa5 spare3: 0x0
 consistency value in tail: 0x496d0601
 check value in block header: 0x0
 block checksum disabled

Page 92177 is marked corrupt
Corrupt block relative dba: 0x00816811 (file 2, block 92177)
Bad header found during dbv:
Data in bad block:
 type: 164 format: 1 rdba: 0x00000001
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x0
 block checksum disabled

Page 92178 is marked corrupt
Corrupt block relative dba: 0x00816812 (file 2, block 92178)
Completely zero block found during dbv:

.... 

In this case, I think in general, the file number 2 is sysaux tablespace. The natural solution is restore the lost information and after recover, but I think I can have archives corrupts on disk, (the problem start because of a SAN crash, affecting to many disk). In my case this database is the rman’s catalog, I verify the data for rman schema and datafiles (by using dbv), I performanced the following actions;

i) export rman schema
ii) Recreate the database (create database command, spfile, locations for datafiles, redo, controlfile is the same)
iii) import rman schema into the new database.

It works fine. Of course, this is no the best way.

HTH – Antonio NAVARRO

 

How To Discard Hints

Many times when you try testcase of execution plans for a sql statement you can need ignoring the hints used in the statements. Here I propose a way of turn off all hints, at level session, for perform the testcases. We goint to use the _optimizer_ignore_hints parameter, of course, it is a hidden parameter, please be carefull.
i) Create a table for the testcase with an index;

TVCASE> create table unodostres as select * from dba_objects;

Tabla creada.

Transcurrido: 00:00:00.69
TVCASE> create index myindex on unodostres (object_id);

Índice creado.

ii) Execute the next query to discover the natural plan, in this case it use the index, it’s correct;

Transcurrido: 00:00:00.23
TVCASE> explain plan for
  2  select * from unodostres where object_id = 5678;

Explicado.

Transcurrido: 00:00:00.07
TVCASE> sta ver_plan

PLAN_TABLE_OUTPUT
___________________________________________________________________________________________________________________
Plan hash value: 3410081772

--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| UNODOSTRES |
|*  2 |   INDEX RANGE SCAN          | MYINDEX    |
--------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=5678)

iii) we force the full scan using a hint;

TVCASE> explain plan for
  2  select /*+ full (unodostres)*/ * from unodostres where object_id = 5678;

Explicado.

Transcurrido: 00:00:00.03
TVCASE> sta ver_plan

PLAN_TABLE_OUTPUT
___________________________________________________________________________________________________________________
Plan hash value: 3083713855

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   762 |   154K|   207   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| UNODOSTRES |   762 |   154K|   207   (1)| 00:00:03 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=5678)

iv) now use the _optimizer_ignore_hints a true and repeat the las query;

TVCASE> alter session set "_optimizer_ignore_hints" = true;

Sesión modificada.

Transcurrido: 00:00:00.04
TVCASE> explain plan for
  2  select /*+ full (unodostres)*/ * from unodostres where object_id = 5678;

Explicado.

Transcurrido: 00:00:00.03
TVCASE> sta ver_plan

PLAN_TABLE_OUTPUT
___________________________________________________________________________________________________________________
Plan hash value: 3410081772

--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| UNODOSTRES |
|*  2 |   INDEX RANGE SCAN          | MYINDEX    |
--------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=5678)

Hint full is ignored.

HTH – Antonio NAVARRO

Nulls First In SQL Server

Today someone ask about how to order a column, she want order nulls at the beginning of the report, she told that can perform it on Oracle. She is correct, or maybe not, many people think NULLS FIRST or NULLS LAST is an impletantion by Oracle but it isn’t true. This syntax is defined by the standard, ISO 9075/ANSI SQL. Many database vendors not implement this feature

In this case the query is on SQL Server, a simple way to resolve it is like show below;

 
SELECT 
S.SESSION_ID,
WT.WAIT_TYPE
FROM SYS.DM_OS_WAITING_TASKS AS WT
RIGHT OUTER JOIN SYS.DM_EXEC_SESSIONS AS S ON WT.SESSION_ID = S.SESSION_ID
WHERE S.IS_USER_PROCESS = 0
ORDER BY ISNULL (WT.WAIT_TYPE, '1')

With this query you get the nulls first, and change the 1 by ZZZ (for example) you get nulls last.

HTH – Antonio NAVARRO

Settings In AWR

I’ll like speak about the minimal configuration for AWR snapshots, it is very important configure it as good as possible to gather all the information after you can use this information to performance proposes and maybe others like planning backups, batch proccess…

Use the next query to see the current settings for AWR. Remember the defaults are 8 days of retention with samples each hour.

SELECT
    EXTRACT( DAY FROM SNAP_INTERVAL) *24*60+
    EXTRACT( HOUR FROM SNAP_INTERVAL) *60+
    EXTRACT( MINUTE FROM SNAP_INTERVAL ) “SNAPSHOT INTERVAL”,
    EXTRACT( DAY FROM RETENTION) *24*60+
    EXTRACT( HOUR FROM RETENTION) *60+
    EXTRACT( MINUTE FROM RETENTION ) “RETENTION INTERVAL”
FROM DBA_HIST_WR_CONTROL;

If you want change the seettings, use the next procedure;

--
-- If you don't specific any parameter it uses the current value.
--
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    RETENTION => 50000,      -- IN MINUTES 
    INTERVAL  => 20);        -- IN MINUTES
END;
/

HTH – Antonio NAVARRO

Delete Vs Truncate

Today I like write some lines about delete command compared with truncate clause, on Oracle (in others RBMS changes somethings), of course, both are compliance with ISO 9075/ANSI SQL. I show below a comparative between delete and truncate;

COMPARTIVE_DELETE_VS_TRUNCATE

The idea is refresh the differences.

HTH – Antonio NAVARRO