by

expdp fails with ORA-01775: looping chain of synonyms

Today, i had to do table refresh. but it failed as follows.

[oracle@antdb01:INDIA1:/home/oracle]$ expdp scott directory=DP dumpfile=smalltable.dmp logfile=smalltable.log tables=Type

Export: Release 11.2.0.3.0 – Production on Tue Dec 30 19:57:47 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-01775: looping chain of synonyms

[oracle@antdb01:INDIA1:/home/oracle]$

problem is expdp trying to create object named : SYS_EXPORT_TABLE_01 and the same object is already available in DB.

OWNER OBJECT_NAME OBJECT_TYPE STATUS
—————————— ————————- ——————- ——-
PUBLIC SYS_EXPORT_TABLE_01 SYNONYM VALID
PUBLIC SYS_EXPORT_TABLE_02 SYNONYM INVALID
PUBLIC SYS_EXPORT_TABLE_03 SYNONYM VALID
PUBLIC SYS_EXPORT_TABLE_04 SYNONYM INVALID

solution : either drop the synonym and use job_name option provide different name as follows

expdp scott directory=DP dumpfile=smalltable.dmp logfile=smalltable.log tables=Type job_name=smalltable_1

above successfully completed.

by

how to take Database link metadata

some cases, we may need to take metadata of DB links and re-create them with few changes on the same db or other DB. below steps can be followed.

 

expdp directory=<TEST_DIR> dumpfile=expdp.dmp logfile=expdp.log schemas=<OWNER_OF_DB_LINK> include=DB_link

and create an sqlfile with

impdp directory=<TEST_DIR> dumpfile=expdp.dmp logfile=impdp.log schemas=<OWNER_OF_DB_LINK> include=DB_link sqlfile=create_db_link.sql

now sqlfile contain metadata of all db links owned by particular user. change the metadata as per requirement and execute the file. 

or

spool create_db_link.sql
select dbms_metadata.get_ddl (‘DB_LINK’, db_link, owner) from dba_db_links where owner like ‘<OWNER>’ ;

 

above select will return all db link metadata owned by particular user. change the metadata as per requirement  , execute it.

by

GI upgradation Failure and could not start GI from old home

as per our plan , we were in the process of upgrading GI to 11.2.0.3 from 11.2.0.2. but our DBA shut down ASM and ran the root upgrade.sh script and it failed and he took long hours to understand that ASM should be up and running while running the script.

 

so decided to go back to old version since outage time is over. but here we had some surprises. when we tried to start clusterware , CSS process failed . tried manually to start CSS . got the below error

 

root@:bin/>./crsctl <mailto:root@:bin/%3E./crsctl> start resource ora.cssd
CRS-2501: Resource ‘ora.cssd’ is disabled
CRS-4000: Command Start failed, or completed with errors.

 

so opened SR and got the Fix as follows

 

./crsctl modify res ora.diskmon -attr “ENABLED=1”

restarted clusterware now. it came successfully up now from old GI home

==============================================SR Update as follows=============

Name
——–
=== ODM Data Collection ===

2012-12-25 05:31:01.937
[/dbname/oragrid/grid1/bin/cssdagent(9044110)]CRS-5818:Aborted command ‘start for resource: ora.cssd 1 1’ for resource ‘ora.cssd’. Details at (:CRSAGF00113:) {0:0:2} in /dbname/oragrid/grid1/log/RACnode/agent/ohasd/oracssdagent_oragrid/oracssdagent_oragrid.log.
2012-12-25 05:31:01.938
[cssd(6881372)]CRS-1656:The CSS daemon is terminating due to a fatal error; Details at (:CSSSC00012:) in /dbname/oragrid/grid1/log/RACnode/cssd/ocssd.log
2012-12-25 05:31:01.938
[cssd(6881372)]CRS-1603:CSSD on node RACnode shutdown by user.
2012-12-25 05:31:10.654
[cssd(7929998)]CRS-1713:CSSD daemon is started in local-only mode
2012-12-25 05:31:19.248
[cssd(7929998)]CRS-1601:CSSD Reconfiguration complete. Active nodes are RACnode .
2012-12-25 05:41:09.588
[/dbname/oragrid/grid1/bin/cssdagent(10682528)]CRS-5818:Aborted command ‘start for resource: ora.cssd 1 1’ for resource ‘ora.cssd’. Details at (:CRSAGF00113:) {0:0:2} in /dbname/oragrid/grid1/log/RACnode/agent/ohasd/oracssdagent_oragrid/oracssdagent_oragrid.log.
2012-12-25 05:41:09.589
[cssd(7929998)]CRS-1656:The CSS daemon is terminating due to a fatal error; Details at (:CSSSC00012:) in /dbname/oragrid/grid1/log/RACnode/cssd/ocssd.log
2012-12-25 05:41:09.589
[cssd(7929998)]CRS-1603:CSSD on node RACnode shutdown by user.

From the CRS alert log file uploaded.

 

Name
——–
=== ODM Data Collection ===

2012-12-25 05:41:08.582: [ default][2829]kgzf_dskm_conn2: skgznp_connect(default pipe) failed with error 56815

2012-12-25 05:41:08.582: [ default][2829]kgzf_dskm_conn2: error 56815 at location skgznpcon6 | connect() – No such file or directory

2012-12-25 05:41:09.082: [ default][2829]kgzf_dskm_conn2: skgznp_connect(default pipe) failed with error 56815

2012-12-25 05:41:09.082: [ default][2829]kgzf_dskm_conn2: error 56815 at location skgznpcon6 | connect() – No such file or directory

2012-12-25 05:41:09.582: [ default][2829]kgzf_dskm_conn2: skgznp_connect(default pipe) failed with error 56815

2012-12-25 05:41:09.582: [ default][2829]kgzf_dskm_conn2: error 56815 at location skgznpcon6 | connect() – No such file or directory

2012-12-25 05:41:09.589: [ CSSD][1029]clssgmExecuteClientRequest: MAINT recvd from proc 1 (11124d6b0)
2012-12-25 05:41:09.589: [ CSSD][1029]clssgmShutDown: Received abortive shutdown request from client.
2012-12-25 05:41:09.589: [ CSSD][1029]###################################
2012-12-25 05:41:09.589: [ CSSD][1029]clssscExit: CSSD aborting from thread GMClientListener
2012-12-25 05:41:09.589: [ CSSD][1029]###################################
2012-12-25 05:41:09.589: [ CSSD][1029](:CSSSC00012:)clssscExit: A fatal error occurred and the CSS daemon is terminating abnormally
2012-12-25 05:41:09.589: [ CSSD][1029]clssgmUpdateEventValue: CmInfo State val 0, changes 11
2012-12-25 05:41:09.589: [ CSSD][1544]clssgmPeerListener: terminating at incarn(251875879)
2012-12-25 05:41:10.082: [ default][2829]kgzf_dskm_conn2: skgznp_connect(default pipe) failed with error 56815

2012-12-25 05:41:10.083: [ default][2829]kgzf_dskm_conn2: error 56815 at location skgznpcon6 | connect() – No such file or directory

2012-12-25 05:41:10.583: [ default][2829]kgzf_dskm_conn2: skgznp_connect(default pipe) failed with error 56815

2012-12-25 05:41:10.583: [ default][2829]kgzf_dskm_conn2: error 56815 at location skgznpcon6 | connect() – No such file or directory

2012-12-25 05:41:11.083: [ default][2829]kgzf_dskm_conn2: skgznp_connect(default pipe) failed with error 56815

 

 

I reviewed log files from 11.2.0.2 HOME, and the error “kgzf_dskm_conn2: error 56815 at location skgznpcon6 | connect() – No such file or directory” is cause of the cssd.bin start up failure.
This error is because diskmon is not starting up. the diskmon.log is not updated since it was shutdown before the upgrade.

 

Solution :

crsctl modify resource ora.diskmon -attr “ENABLED=1”
crsctl start resource ora.diskmon

now re-start HAS service. CSS will come up

==============End of SR update==========================================

 

by

Job Opening

Dear Friends ,

 

just got to know that , there is a Job OPening for freshers for embedded systems. if any one interested and having knowledge of little bit  

 about embedded systems, please do let me know.

 

Thanks

Aliyar