Home » RDBMS Server » Backup & Recovery » ORA-01422: exact fetch returns more than requested number of (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0, Microsoft Windows IA (32-bit))
ORA-01422: exact fetch returns more than requested number of [message #520759] Wed, 24 August 2011 10:16 Go to next message
kytemanaic
Messages: 55
Registered: February 2009
Member
Hi,

I have perform a failover and encounters the following problem.

ORA-01422: exact fetch returns more than requested number of rows

initially primary db: chicago
physical standby db: boston

after failover

primary db:boston
physical standby: NA


after recreating physical standby

primary db:boston
physical standby: chicago

over at chicago (new standby)

log_archive_dest_3=E:\chicago_arc\

over at boston (new primary)
log_archive_dest_3=E:\boston_arc\

over at boston when I connect to both boston as target db and recovery catalog


RMAN> list archivelog like 'E:\CHICAGO_ARC\%';

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
252690  1    10      A 22-AUG-11 E:\CHICAGO_ARC\10_759842424_1.ARC


over at chicago when I connect to both chicago as target db and recovery catalog
RMAN> list archivelog like 'E:\CHICAGO_ARC\%';

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
252690  1    10      A 22-AUG-11 E:\CHICAGO_ARC\10_759842424_1.ARC


however at E:\chicago_arc\ when I do a os check

C:\Documents and Settings\Administrator>dir /w E:\chicago_arc\
 Volume in drive E is New Volume
 Volume Serial Number is C874-DD6D

 Directory of E:\chicago_arc

[.]                   [..]                  100_759410562_1.ARC
101_759410562_1.ARC   102_759410562_1.ARC   103_759410562_1.ARC
10_759842424_1.ARC    11_759842424_1.ARC    12_759842424_1.ARC
13_759842424_1.ARC    14_759842424_1.ARC    15_759842424_1.ARC
16_759842424_1.ARC    17_759842424_1.ARC    18_759842424_1.ARC
19_759842424_1.ARC    20_759842424_1.ARC    21_759842424_1.ARC
22_759842424_1.ARC    23_759842424_1.ARC    24_759842424_1.ARC
94_759410562_1.ARC    95_759410562_1.ARC    96_759410562_1.ARC
97_759410562_1.ARC    98_759410562_1.ARC    99_759410562_1.ARC
listener.ora
              26 File(s)      9,077,989 bytes
               2 Dir(s)   2,650,337,280 bytes free



there's more files than indicated by rman

also at both chicago and boston

I have the following error:

RMAN> debug on;

RMAN-03036: Debugging set to level=9, types=ALL

RMAN> report obsolete;

RMAN-06524: RMAN retention policy will be applied to the command
RMAN-06511: RMAN retention policy is set to redundancy 1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of report command at 08/24/2011 23:12:15
RMAN-06004: ORACLE error from recovery catalog database: ORA-01422: exact fetch
returns more than requested number of rows

RMAN> debug off;

Debugging turned off




here's the trace files I logged


DBGRCVMAN: EXITING listBackup with exception: ORA-01422: exact fetch returns more than requested number of rows
DBGMISC:     krmicomp: error 6004 signalled during compilation [23:12:15.358]
DBGMISC:     ENTERED krmkmrsr [23:12:15.358]

DBGSQL:       EXEC SQL AT TARGET select decode(open_mode,'MOUNTED',0,'READ WRITE',1,'READ ONLY',1,0) into :b1  from v$database  [23:12:15.374]
DBGSQL:          sqlcode=0 [23:12:15.389]
DBGSQL:             :b1 = 0

DBGSQL:       EXEC SQL AT TARGET select decode(status,'OPEN',1,0) ,decode(archiver,'FAILED',1,0) into :b1,:b2  from v$instance  [23:12:15.405]
DBGSQL:          sqlcode=0 [23:12:15.405]
DBGSQL:             :b1 = 0
DBGSQL:             :b2 = 0

DBGSQL:       EXEC SQL AT TARGET select value into :b1:b2  from v$parameter where name='compatible' [23:12:15.436]
DBGSQL:          sqlcode=0 [23:12:15.452]
DBGSQL:             :b1 = "10.2.0.3.0"

DBGSQL:       EXEC SQL AT TARGET declare dot1st number ; dot2nd number ; dot3rd number ; comptxt varchar2 ( 255 ) := :vcomp_txt ; begin dot1st := instr ( comptxt , '.' , 1 , 1 ) ; dot2nd := instr ( comptxt , '.' , 1 , 2 ) ; if instr ( comptxt , '.' , 1 , 3 ) = 0 then dot3rd := length ( comptxt ) + 1 ; else dot3rd := instr ( comptxt , '.' , 1 , 3 ) ; end if ; comptxt := lpad ( substr ( comptxt , 1 , dot1st -1 ) , 2 , '0' ) || lpad ( substr ( comptxt , dot1st + 1 , dot2nd -dot1st -1 ) , 2 , '0' ) || lpad ( substr ( comptxt , dot2nd + 1 , dot3rd -dot2nd -1 ) , 2 , '0' ) ; :vcomp_ub4 := to_number ( comptxt ) ; end ; [23:12:15.468]
DBGSQL:          sqlcode=0 [23:12:15.483]
DBGSQL:             :b1 = "10.2.0.3.0"
DBGSQL:             :b2 = 100200
DBGMISC:      krmkpdbs(): vcomp_txt:10.2.0.3.0 vcomp_ub4:100200 flags:0 [23:12:15.499]

DBGSQL:       EXEC SQL AT TARGET select  /*+  rule  +*/ round(sum(MBYTES_PROCESSED)) ,round(sum(INPUT_BYTES)) ,round(sum(OUTPUT_BYTES))  from V$RMAN_STATUS  start with (RECID=:b1 and STAMP=:b2)  connect by prior RECID=parent_recid             [23:12:15.514]
DBGSQL:          sqlcode=0 [23:12:15.530]
DBGSQL:             :b1 = 1450
DBGSQL:             :b2 = 760057924

DBGSQL:       EXEC SQL at TARGET FETCH/CLOSE of prior statement [23:12:15.546]
DBGSQL:          sqlcode=0 [23:12:15.593]
DBGSQL:             :b1 = 0
DBGSQL:             :b2 = 0
DBGSQL:             :b3 = 0

DBGSQL:       EXEC SQL at TARGET FETCH/CLOSE of prior statement [23:12:15.624]
DBGSQL:          sqlcode=0 [23:12:15.639]



How should I resolve this issue? thanks a lot!
Re: ORA-01422: exact fetch returns more than requested number of [message #520762 is a reply to message #520759] Wed, 24 August 2011 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Make sure that lines of code do not exceed 80 characters when you format.

Regards
Michel
Re: ORA-01422: exact fetch returns more than requested number of [message #520767 is a reply to message #520759] Wed, 24 August 2011 11:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Seems you hit a gray spot with mismatch between Oracle versions.
Why am I seeing 10.2.0.3 is those debug info and you claim version to be 10.2.0,4?

I suppose your
Primary/Secondary databases are in 10.2.0.4
and
Recovery catalog in 10.2.0.3.
Either you upgraded only database and not the catalog or the other case.
Metalink is full of this and a very particular bug is been reported with these versions.

[Updated on: Wed, 24 August 2011 11:07]

Report message to a moderator

Re: ORA-01422: exact fetch returns more than requested number of [message #520772 is a reply to message #520759] Wed, 24 August 2011 11:47 Go to previous messageGo to next message
kytemanaic
Messages: 55
Registered: February 2009
Member
Nope all of them are the same version?

10.2.0.4

could be be some thing to do with resyncing the wrong control file?

I also encountered the following error when I try to backup at primary, boston

RMAN> backup validate check logical database plus archivelog;

Starting backup at 25-AUG-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 08/25/2011 01:07:49
RMAN-06059: expected archived log not found, lost of archived log compromises re
coverability
ORA-19625: error identifying file D:\ORA102\CHICAGO\CHICAGO_RECOVER\CHICAGO\ARCH
IVELOG\2011_08_22\O1_MF_1_18_7542SJD6_.ARC
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.


I also verified that the files does exist
SYS@boston>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Pr
oduction
With the OLAP, Data Mining and Real Application Testing options

C:\Documents and Settings\Administrator>dir/w D:\ORA102\CHICAGO\CHICAGO_RECOVER\
CHICAGO\ARCHIVELOG\2011_08_22\O1_MF_1_18_7542SJD6_.ARC
 Volume in drive D is New Volume
 Volume Serial Number is 10B0-3354

 Directory of D:\ORA102\CHICAGO\CHICAGO_RECOVER\CHICAGO\ARCHIVELOG\2011_08_22

O1_MF_1_18_7542SJD6_.ARC
               1 File(s)        108,032 bytes
               0 Dir(s)  10,528,411,648 bytes free



I have also execute the command

change archivelog all crosscheck according to http://www.shutdownabort.com/errors/RMAN-06059.php;

May I know how should I approach this issue?

thanks

[Updated on: Wed, 24 August 2011 12:11]

Report message to a moderator

Re: ORA-01422: exact fetch returns more than requested number of [message #520779 is a reply to message #520772] Wed, 24 August 2011 12:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
If that is the case,
I would consider looking into metalink note titled

Bug 5882242: CHANGE TO DB_UNIQUE_NAME CAUSES ORA-1422 DURING RMAN CATALOG RESYNC

and see if it applies( looks so. Dataguard implemented with roles switching causing a similar issue).


[Updated on: Wed, 24 August 2011 12:11]

Report message to a moderator

Re: ORA-01422: exact fetch returns more than requested number of [message #521100 is a reply to message #520759] Fri, 26 August 2011 05:51 Go to previous message
Markwillium
Messages: 13
Registered: December 2010
Location: India
Junior Member
Possible cause: The number specified in exact fetch is less than the rows returned.
Resolution: Rewrite your query or change the requested number of rows.
Previous Topic: New backup on networkdrive - cant read from new host
Next Topic: error set in sbterror by MM, does not get printed on the console?
Goto Forum:
  


Current Time: Thu Mar 28 15:50:46 CDT 2024