Month: August 2017

ORA-31617: unable to open dump file “/export/data.dmp” for write

by

This morning a user came to me because they were getting what appeared to be a Permission error

FLASHBACK automatically enabled to preserve database integrity.
Starting "myschema"."SYS_EXPORT_SCHEMA_06":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 252.1 GB
ORA-31693: Table data object "myschema"."mytable" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/nfs/unix/export/myschema01.dmp" for write
ORA-19505: failed to identify file "/nfs/unix/export/myschema01.dmp"
ORA-17503: ksfdopn:4 Failed to open file /nfs/unix/export/msychema01
ORA-17500: ODM err:File does not exist

I checked the directory listed, /nfs/unix/export. It is mounted on both nodes of the cluster.

Checked file permissions, using touch I created a file as the oracle user, and deleted it, no permissions issues there.

It turns out it was the directory object itself! I looked at the job definition, and it was using the directory /export

Export is a link to the /nfs/unix/export directory. The second node had link, but not the first! What threw me off was the directory object itself was only pointing to /export, but the error message was pointing to the full NFS path. Dropping and re-creating the directory object in the database pointing to /nfs/unix/export/ resolve the issue.

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;

 

ORA-02396: exceeded maximum idle time, please connect again

by

I was doing a schema import last night, over a database link. Lack of sleep had set in and I was getting the following error

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX 
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [INDEX:"SCHEMA_OWNER"."TABLE2"] 
ORA-02396: exceeded maximum idle time, please connect again 

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 
ORA-06512: at "SYS.KUPW$WORKER", line 11259 

----- PL/SQL Call Stack ----- 
object line object 
handle number name 
0x2156ad620 27116 package body SYS.KUPW$WORKER 
0x2156ad620 11286 package body SYS.KUPW$WORKER 
0x2156ad620 13515 package body SYS.KUPW$WORKER 
0x2156ad620 3173 package body SYS.KUPW$WORKER 
0x2156ad620 12035 package body SYS.KUPW$WORKER 
0x2156ad620 2081 package body SYS.KUPW$WORKER 
0x20d5f9bb0 2 anonymous block 

KUPP$PROC.CHANGE_USER 
KUPP$PROC.CHANGE_USER 
NONE connected to AAR00287 
DBMS_LOB.TRIM 
DBMS_LOB.TRIM 
DBMS_LOB.TRIM 
DBMS_LOB.TRIM 
DBMS_METADATA.FETCH_XML_CLOB 
DBMS_METADATA.FETCH_XML_CLOB 
In procedure DETERMINE_FATAL_ERROR with ORA-02396: exceeded maximum idle time, please connect again

Now if you aren’t tired this should be a pretty obvious fix, the idle_time value on the remote server was set to 60 and was disconnecting me during the index build section of the import. I altered the profile to give my user/profile unlimited idle_time. Problem solve, import complete.

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