How to handle RMAN DUPLICATE: Errors in krbm_getDupCopy during duplicate

I recently cloned a 11.2.0.4 database using tape backups as source.

The clone failed right before "open resetlogs". In other words, the database was restored and recovered, but DUPLICATE didn't succeed in opening it. So I did it manually, by recreating the controlfile using a template from the source database, and adjusted for the file layout on the auxiliary. The database opened nicely.

However, later during the day, the application team found that a new clone was needed due to some synch issue caused by a third-party replication tool. In other words, the entire database had to be copied from the latest version of our incremental level 1 backups and restored all over again.

No problem.

As usual, I started by saving away the spfile, password file and the block change tracking file located in $ORACLE_HOME/dbs. Following that, I mounted and dropped the database. All the files were automatically removed from disk by Oracle, and the instance terminated, as expected.

As I started to clone, I noticed some new messages in the alert log of the auxiliary database:

RMAN DUPLICATE: Errors in krbm_getDupCopy
Errors in file /u01/oracle/product/11204/admin/testdb01/diag/rdbms/testdb01/testdb01/trace/testdb01_ora_22544486.trc:
ORA-19625: error identifying file /u02/oradata/testdb01/system01.dbf
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

This error was repeated for every data file in the database and appended to the same trace file as in the message above.

I found the errors worth checking.

Fortunately, these errors can be ignored. MOS Doc ID 1476641.1 "RMAN DUPLICATE: Errors In Krbm_getDupCopy found in alert.log" explains

"This happens after a previous failed duplicate trial, if the files copied to auxiliary destination have been deleted."

and


"The messages in alert.log indicate that rman cannot use a previous datafile copy for those files and that the files must be copied again to auxiliary destination. Duplicate checks if there are datafile copies already created by a duplicate to avoid restoring the files again.

So this is a part of Oracle restore optimization concept:

"...from 11.2 RMAN always checks if the file copy exists at destination hosts to avoid copying it again"


Great, but how does RMAN do that?

"If duplicate fails between the first restore/copy and the zeroing of the dbid, a second duplicate will find the _rm_dup_@.dat file and the clone default channel will read it into memory, once determined the name of the datafilecopy to be created by the second duplicate it is compared with the existing datafilecopy from the previous duplicate. If the datafilecopy still exists and matches the vital information of the datafile (file number, database id, creation scn, database name) and its checkpoint scn is behind the until scn then the datafilecopy can be used by this new duplicate and restore/copy isnot necessary"


The solution is simple:

"As files have already been deleted from auxiliary destination, ignore those messages."

I could also have followed the following advice:

"If you don't want to see those messages in alert.log but datafiles have already been deleted, on Auxiliary host, delete the file $ORACLE_HOME/dbs/_rm_dup_.dat where dup_db is the name of the clone instance."

How to drop a redo logfile group and redo logfile group members - syntax

alter database drop logfile group 4;

The drop statement will be confirmed by an entry in the database's alert log.

Don't forget to physically remove the file from disk afterwards.

You can also drop individual Group members:

alter database drop logfile member '/u03/oradata/proddb01/red04a.log';

Pay attention to the restrictions when dropping redo log groups and redo log members!

A good Query for redo log file information can be found here

Query to find basic redo log information

COL MEMBER FORMAT A50
SET LINES 200
SELECT A.GROUP#,A.MEMBER, B.BYTES/1024/1024 "MB", B.ARCHIVED,B.STATUS,B.SEQUENCE#
FROM V$LOGFILE A INNER JOIN  V$LOG B ON A.GROUP# = B.GROUP#
ORDER BY GROUP# ASC;

Result:

GROUP#	MEMBER	MB	ARCHIVED	STATUS	SEQUENCE#
1	/u04/oradata/proddb01/log1a.ora	1024	YES	INACTIVE	90510
1	/u04/oradata/proddb01/log1b.ora	1024	YES	INACTIVE	90510
2	/u04/oradata/proddb01/log2b.ora	1024	YES	INACTIVE	90511
2	/u04/oradata/proddb01/log2a.ora	1024	YES	INACTIVE	90511
3	/u04/oradata/proddb01/log3b.ora	1024	NO	CURRENT	90508
3	/u04/oradata/proddb01/log3a.ora	1024	NO	CURRENT	90508

How to add a comment on a column

Use this syntax:

COMMENT ON COLUMN SCOTT.EMP.DOB IS 'Date of Birth';

How to find the default tablespace type (smallfile vs bigfile)

SQL> select property_value 
from database_properties 
where property_name = 'DEFAULT_TBS_TYPE';

PROPERTY_VALUE
---------------
SMALLFILE

Change like this:

alter database set default bigfile tablespace;

The change will of course only take effect for future tablespaces, not existing ones.


Link to Oracle documentation

How to append text at beginning and at end of each line in vi (unix)

Go to top of line with :0

Append text to the end of each line, globally:

:%s/$/text/g

Add text to the beginning of each line, globally:

:%s/^/text/g

How to install and deinstall Oracle Spatial and Oracle Multimedia

The following advice is an excerpt from a Service Request with Oracle support services, on how to administer Spatial and Multimedia:

To remove Spatial:

 connect / as sysdba 
 drop user MDSYS cascade; 

To remove Multimedia:

 connect / as sysdba 
 @?/rdbms/admin/catcmprm.sql ORDIM 

You can reinstall both of these components later if needed by running the following scripts. Note, Multimedia must be installed first.

To Install Multimedia:

 connect / as sysdba 
 @?/ord/admin/ordinst.sql SYSAUX SYSAUX 
 @?/ord/im/admin/catim.sql 
 execute sys.validate_ordim; 

To install Spatial:

 connect / as sysdba 
 @?/md/admin/mdinst.sql 

How to check if spatial and multimedia is installed in the database

The following advice was given to me by Oracle Support services, to determine if either of these components are being used in the database:

 connect / as sysdba 

 set pagesize 10000 
 col owner format a12 
 col table_name format a35 
 col column_name format a25 
 col index_name format a25 

 -- Is Spatial being used? 

 select owner, index_name 
 from dba_indexes 
 where ityp_name = 'SPATIAL_INDEX'; 

 select owner, table_name, column_name 
 from dba_tab_columns 
 where data_type= 'SDO_GEOMETRY' 
 and owner != 'MDSYS'; 

If both of these queries return no rows, Spatial is not being used.

 -- Is Multimedia being used? 

 set serveroutput on; 
 @?/ord/im/admin/imremchk.sql 

If you're not using this component, you will get the message returned "Oracle Multimedia is not being used".

How to check if the database is in restricted mode

To check what kind of logins that are allowed in your instance, query the LOGINS field of the v$instance view:

select logins from v$instance;

Output here will be either ALLOWED or RESTRICTED

Join with v$database for more information:

select a.INSTANCE_NAME, a.ACTIVE_STATE,a.logins, b.open_mode 
from v$instance a inner join v$database b 
on UPPER(a.instance_name) = b.name;

Which will show the following output if your database is in restricted mode, and in this example, mounted:

INSTANCE_NAME    ACTIVE_ST LOGINS     OPEN_MODE
---------------- --------- ---------- --------------------
mydb01           NORMAL    RESTRICTED MOUNTED

and for normal mode:

INSTANCE_NAME    ACTIVE_ST LOGINS     OPEN_MODE
---------------- --------- ---------- --------------------
mydb01           NORMAL    ALLOWED    READ WRITE

ORA-01722 when running ausy1120.sql during preparation of new target database for EBS

If you are following note Doc ID 741818.1 "Export/import process for 12.0 or 12.1 using 11gR1 or 11gR2" and run into the follwing problem when running the ausy1120.sql script:

sqlplus system/*** @ausy1120.sql

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 2 14:19:31 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option


PL/SQL procedure successfully completed.

declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 5

the solution is to modify the script slightly and correct a typo.
Open the ausy1120.sql file in an editor and change

select
  to_number(substr(version,1,instr(version,'.')))
  into :dbver
  from v$instance
  where rownum=1;

to

select
  to_number(substr(version,1,instr(version,'.')-1))
  into :dbver
  from v$instance
  where rownum=1;

Then rerun the script. It should finish almost immediately and the output should be similar to:

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

--------------------------------------------------------------------------------
--- ausy1120 started at 02-NOV-2015 14:40:04 ---


'---AUSY1120COMPLETEDAT'||TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')||'----'
--------------------------------------------------------------------------------
--- ausy1120 completed at 02-NOV-2015 14:40:04 ----

Commit complete.

The same error seems to occur when running the aujv1120.sql and the aumsc1120.sql as well. The solution is the same for all three.