Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: ORA-04053: error occurred when validating remote object (oracle12c)
PL/SQL: ORA-04053: error occurred when validating remote object [message #662767] |
Thu, 11 May 2017 13:45 |
shrinika
Messages: 306 Registered: April 2008
|
Senior Member |
|
|
I have shell script that calls PLSQL script. The job connect to standby database
and capture some info and update that captured info in another remote database(READ WRITE).
Here is the plsql script, which is calling from shell script.
set serveroutput on
declare
v_instance varchar2(30);
v_starttime date;
v_instance_status varchar2(30);
v_db_status varchar2(30);
v_instance_role varchar2(30);
v_open_mode varchar2(30);
v_protection_mode varchar2(30);
v_database_role varchar2(30);
v_switchover_status varchar2(30);
v_dataguard_broker varchar2(30);
v_host_name varchar2(100);
v_db_name varchar2(50);
v_force_logging varchar2(10);
begin
select
a.instance_name,
a.startup_time,
a.status ,
a.database_status,
a.instance_role,
a.host_name,
b.name,
b.open_mode,
b.protection_mode,
b.database_role,
b.switchover_status,
b.dataguard_broker,
b.force_logging
into
v_instance,
v_starttime,
v_instance_status ,
v_db_status,
v_instance_role,
v_host_name,
v_db_name,
v_open_mode,
v_protection_mode,
v_database_role,
v_switchover_status,
v_dataguard_broker,
v_force_logging
from
v$instance a,
v$database b;
update dbstatus@crdblink
set startup_time = v_starttime,
instance_status = v_instance_status,
database_status = v_db_status,
instance_role = v_instance_role,
open_mode = v_open_mode,
protection_mode = v_protection_mode,
database_role=v_database_role,
switchover_status = v_switchover_status,
dataguard_broker = v_dataguard_broker,
force_logging=v_force_logging,
last_update_dt = sysdate
where
instance_name=v_instance
and dbname=v_db_name
and hostname=v_host_name;
commit;
end;
/
alter session close database link CRDBLINK;
exit
Here is the shell script which calls the above PLSQL code
#!/usr/bin/ksh
#set -x
. /home/oracle/scripts/conf/set_env.sh
filelist=$(cat $CONF/all_instance.txt | grep -v "#"|sort)
for i in $filelist
do
$ORACLE_HOME/bin/sqlplus -S $DB_CREDENTIAL@$i @$SQL_FILE/dbstatusupdate.sql
done
The shells script connect to one of the standby database
and capture DB info and update on table which is existing on other READ WRITE database.
I am getting below error when i run the shell.
SQL> @dbstatusupdate.sql.orig
update dbstatus@crdblink
*
ERROR at line 47:
ORA-06550: line 47, column 8:
PL/SQL: ORA-04053: error occurred when validating remote object
DBMON.DBSTATUS@CRDBLINK
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access
ORA-06550: line 47, column 1:
PL/SQL: SQL Statement ignored
ERROR:
ORA-02080: database link is in use
But every thing works great when i convert the PLSQL code to stored procedure.
Any one please share any thought why it works with stored procedure. But
does not work for plsql code?
Thank you!
|
|
|
Re: PL/SQL: ORA-04053: error occurred when validating remote object [message #662768 is a reply to message #662767] |
Thu, 11 May 2017 15:11 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
ORA-16000: database or pluggable database open for read-only access
*Cause: The database or pluggable database was opened for read-only access.
Attempts to modify the database using DML or DDL statements
generate this error.
*Action: In order to modify the database or pluggable database, it must
first be shut down and reopened for read/write access.
Quote:But every thing works great when i convert the PLSQL code to stored procedure.
Prove it and show us the actual execution.
And you didn't say if this a local or remote procedure.
[Updated on: Thu, 11 May 2017 15:11] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 27 16:56:26 CDT 2024
|