RMAN

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

by

The other night on of my databases started throwing the following error.

RMAN> delete noprompt archivelog all backed up 1 times to 'sbt'; 
RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: =========================================================== 
RMAN-03002: failure of delete command at 05/03/2019 08:53:40 
RMAN-03014: implicit resync of recovery catalog failed 
RMAN-03009: failure of partial resync command on default channel at 05/03/2019 08:53:40 
ORA-01422: exact fetch returns more than requested number of rows

From the RMAN trace

DBGMISC: Trimming message: ORA-01422: exact fetch returns more than requested number of rows [09:01:19.157] (krmstrim) 
DBGMISC: ORA-06512: at "RMAN_NB_TP0LAWS.DBMS_RCVCAT", line 13092 (krmstrim) 
DBGMISC: ORA-06512: at line 3995 (krmstrim) 
DBGMISC: (146) (krmstrim)

The solution here is actually quite simple, the rout table, which stores previous rman output from previous runs so that it can displayed in OEM had excessive rows in it. Since we don’t really use OEM to track our rman backups, we can safely purge them.

SQL> truncate table rout ; 
SQL> truncate table rsr ;

This resolved our issue. I was able to resync the catalog with out any further errors.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

by

On a non-production database I was getting the following error when trying to delete archive logs with RMAN.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+RECO/DBP1/ARCHIVELOG/2017_05_07/thread_1_seq_32041.11548.943352263 thread=1 sequence=32041

This database does not have any golden gate processes associated with it, so why doesn’t Oracle want to delete the  archive log?

The database was rebuilt with a copy of a production database that did have golden gate running on it.

We can see the name of the extract that is still registered with the database.

 SYS dbp01> select CAPTURE_NAME from dba_capture;

CAPTURE_NAME
--------------------------------------------------------------------------------------------------------------------------------
OGG$CAP_EXTDBP01

Elapsed: 00:00:00.35

Since the database was a copy of our production database that did have golden gate running, it till thinks there is a valid extract. Connecting to the database and unregistered the extract allows the archive log to be deleted.

GGSCI (dclxoradbd01) 1> dblogin userid ggs, password xxxx
Successfully logged into database.

GGSCI (dclxoradbd01 as ggs@dp5fact1) 9> UNREGISTER EXTRACT EXTFWBP database
Successfully unregistered EXTRACT EXTFWBP from database.

UPDATE 11/13/2017:

One additional helpful command if you don’t have goldengate installed on the host where you are getting this error, you can remove the extract directly from the database

SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE('OGG$CAP_EXTDBP01')

PL/SQL procedure successfully completed.

Update 08/10/2018

I usually just generate the code I need to run like this since I usually want to drop all the captures

select 'exec DBMS_CAPTURE_ADM.DROP_CAPTURE('''||CAPTURE_NAME||''');' from dba_capture;

 

RMAN-20036: invalid record order

by

Trying to register a database with my rman catalog I was getting the following error

$ rman target sys/xxx@DB01 catalog=RMAN_DB01/xxx@rmancat

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 7 11:42:37 2017

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

connected to target database: DB01 (DBID=114156133)
connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

RMAN> register database;

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 08/07/2017 11:42:44
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 08/07/2017 11:42:44
RMAN-20036: invalid record order

Poking around metalink I found note 113325.1

Basicly it says there is a mismatch in the number of standby redo logs, but this database does not have a standby. However it was cloned from a database that had a standby, and I apparently forgot to remove the standby redo logs after I cloned it.

SQL> select INST_ID,GROUP#,TYPE from gv$logfile;
         1          1         ONLINE
         1          2         ONLINE
         1          3         ONLINE
         1          4 INVALID STANDBY
         1          5         STANDBY
         2          6         ONLINE
         2          7         ONLINE
         2          8         ONLINE
         2          9 INVALID STANDBY
         2         10         STANDBY

So cleanup the standby logfiles

SQL> alter database drop standby logfile group 1 thread 4;
		 
Database altered.

SQL> alter database drop standby logfile group 1 thread 5;
		 
Database altered.

SQL> alter database drop standby logfile group 2 thread 9;
		 
Database altered.

SQL> alter database drop standby logfile group 2 thread 9;
		 
Database altered.

I dropped and re-created the catalog

RMAN> create catalog;

recovery catalog created

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

 

Rushing = Stupid Mistakes

by

I am currently being asked to refresh a database. The end users are pissed because they asked for the database to be refreshed at noon yesterday, this was the first they had asked for it, and they were doing “really important things” and by 3pm were pissed that I hadn’t responded to them, let alone completed the work. The thing was, I took a vacation. I took a whole two days off to spend some time with my girlfriend and wasn’t checking my email. Never mind that we have a whole team of DBAs that can refresh a database, they had emailed me. What they should have done was open a proper ticket, and someone would have helped out even though they hadn’t planned and were now in a panic.

So After escalating it to their boss because I wasn’t providing them with “good service”, who in turn talked to my boss, who informed him how they had dropped the ball, and I would deal with it today, that they were being completely unprofessional. (My boss is a good guy, and sticks up for me).

So here is the technical part of this post. Ever DBA has cloned a database, thousands and thousands of databases, but when you are rushing you can make stupid errors. I just fought this error for 20 minutes

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/08/2016 14:23:04
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-27191: sbtinfo2 returned error
Additional information: 3480

This should have been a no brainier, but in my rush I wasn’t fully paying attention to the error.

I was just issuing the command

duplicate target database to testdb from active database

My defaults for RMAN are using sbt ie tape, so for some reason the tape channels were being loaded. This causes an error. The solution is simple and should have been fairly immediate, just allocate disk channels.

run
{
ALLOCATE CHANNEL d1 TYPE DISK ;
ALLOCATE CHANNEL d2 TYPE DISK ;
ALLOCATE CHANNEL d3 TYPE DISK ;
ALLOCATE AUXILIARY CHANNEL a1 TYPE DISK ;
ALLOCATE AUXILIARY CHANNEL a2 TYPE DISK ;
ALLOCATE AUXILIARY CHANNEL a3 TYPE DISK ;
duplicate target database to testdb from active database;
}

Even simple things become very hard when you are rushing.