I am trying to create a job in my Oracle database to refresh a materialized view every five minutes. The following is the code I have made:
BEGIN
SYS.DBMS_SCHEDULER.create_job(
job_name => 'refresh_cop_union',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_SNAPSHOT.REFRESH(''COP_UNION'',''C''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely;interval=5',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'Refresh COP_UNION matview'
);
END;
/
When I executed it I got the following error:
Error report —
ORA-27477: «MASTER_DB».»REFRESH_COP_UNION» already exists
ORA-06512: at «SYS.DBMS_ISCHED», line 175
ORA-06512: at «SYS.DBMS_SCHEDULER», line 288
ORA-06512: at line 2
27477. 00000 — «\»%s\».\»%s\» already exists»
*Cause: An attempt was made to create an object with a name that has
already been used by another object in the same schema.
*Action: Reissue the command using a different name or schema.
So it seems I have another object in my database with the same name, probably another job. Therefore I tried to execute the following code to delete it and replace it with my new version:
BEGIN
sys.dbms_scheduler.drop_job(job_name => 'refresh_cop_union');
END;
/
But it failed with the following error:
Error report —
ORA-27475: unknown job «MASTER_DB».»REFRESH_COP_UNION»
ORA-06512: at «SYS.DBMS_ISCHED», line 274
ORA-06512: at «SYS.DBMS_SCHEDULER», line 753
ORA-06512: at line 2
27475. 00000 — «unknown %s \»%s\».\»%s\»»
*Cause: The specified object did not exist, privileges were not granted,
or the object was of the wrong type.
*Action: Specify an object of the correct type on which you have privileges.
Which basically says that the object I am trying to delete doesn’t exist. How is this possible? How can I find this object, see what it is and eventually delete it?
I also went through all the objects that may have a similar name in my database: other tables, views, triggers but I found nothing.
I tried to list all the jobs I have in order to find mine with:
SELECT * FROM USER_JOBS
WHERE WHAT LIKE '%COP_UNION%';
But nothing showed up. Any idea?
datapatch failed with ORA-27477: «SYS».»LOAD_OPATCH_INVENTORY_X» already exists
We have received the
below error while working on the 6-node cluster patching. The patch has been
applied on software GRID and RDBMS on all the node but the datapatch execution
is failed with ORA-27477.
rac06:dbp01:/u01/app/oracle/product/12.1.0.2/database/OPatch$
./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Wed Oct 25 12:55:45 2017
Copyright (c) 2016, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12298_2017_10_25_12_55_45/sqlpatch_invocation.log
Connecting to database…OK
Bootstrapping registry and package to current versions…done
Queryable inventory could not determine the current opatch status.
Execute ‘select dbms_sqlpatch.verify_queryable_inventory from
dual’
and/or check the invocation
log /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12298_2017_10_25_12_55_45/sqlpatch_invocation.log for
the complete error.
Prereq
check failed, exiting without installing any patches.
Solution: The above error occurs when
datapatch trying to read the inventory across all the nodes using predefined
set of jobs. If the predefined jobs are removed from any of the node in RAC.
You will see the above error.
Hence, drop the jobs from all
the cluster nodes and run the datapatch to fix the issue.
1.) Lets validate, the
inventory is queryable or not.
SQL> select
dbms_sqlpatch.verify_queryable_inventory from dual;
VERIFY_QUERYABLE_INVENTORY
——————————————————————————–
ORA-27477: “SYS”.”LOAD_OPATCH_INVENTORY_1″ already exists
2.) Check the predefined job status on all the nodes.
SQL> select job_name,state,
start_date from dba_scheduler_jobs where job_name like ‘LOAD_OPATCH%’;
JOB_NAME STATE START_DATE
————————— ————— ————————————
LOAD_OPATCH_INVENTORY DISABLED 25-OCT-17 06.00.50.791058 AM +00:00
LOAD_OPATCH_INVENTORY_1 STOPPED 25-OCT-17 07.11.49.606570 AM
+00:00
LOAD_OPATCH_INVENTORY_2 DISABLED 25-OCT-17 07.11.50.543851 AM
+00:00
3.)
Clear all existing job entries
SQL> exec
DBMS_SCHEDULER.DROP_JOB(‘LOAD_OPATCH_INVENTORY’);
SQL> exec DBMS_SCHEDULER.DROP_JOB(‘LOAD_OPATCH_INVENTORY_1’);
SQL> exec DBMS_SCHEDULER.DROP_JOB(‘LOAD_OPATCH_INVENTORY_2’);
4.) Validate, there should not be any jobs.
SQL> select job_name,state, start_date from dba_scheduler_jobs where job_name like ‘LOAD_OPATCH%’;
4.) Execute the datapatch
again. This time, the datapatch should be executed successfully.
$ ./datapatch -verbose
Note:- Test, Test and Test before executing the above solutions in your
environment or consult the oracle support.
datapatch is failing with ORA-27477, due to existing job_name in dba_scheduler_jobs
db02:orcl01:/u01/product/12.1.0.2/database/OPatch>./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Tue Oct 25 12:55:45 2016
Copyright (c) 2016, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_3997884_2016_10_25_12_55_45/sqlpatch_invocation.log
Connecting to database…OK
Bootstrapping registry and package to current versions…done
Queryable inventory could not determine the current opatch status.
Execute ‘select dbms_sqlpatch.verify_queryable_inventory from dual’
and/or check the invocation log
/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_3997884_2016_10_25_12_55_45/sqlpatch_invocation.log
for the complete error.
Prereq check failed, exiting without installing any patches.
SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;
VERIFY_QUERYABLE_INVENTORY
——————————————————————————–
ORA-27477: “SYS”.”LOAD_OPATCH_INVENTORY_1″ already exists
SQL> select job_name,state, start_date from dba_scheduler_jobs where job_name like ‘LOAD_OPATCH%’;
JOB_NAME STATE START_DATE
————————— ————— ————————————
LOAD_OPATCH_INVENTORY DISABLED 25-OCT-16 06.00.50.791058 AM +00:00
LOAD_OPATCH_INVENTORY_1 STOPPED 25-OCT-16 07.11.49.606570 AM +00:00
LOAD_OPATCH_INVENTORY_2 DISABLED 25-OCT-16 07.11.50.543851 AM +00:00
Clear all existing job entries
SQL> exec DBMS_SCHEDULER.DROP_JOB(‘LOAD_OPATCH_INVENTORY’);
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.DROP_JOB(‘LOAD_OPATCH_INVENTORY_1’);
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.DROP_JOB(‘LOAD_OPATCH_INVENTORY_2’);
PL/SQL procedure successfully completed.
SQL> select job_name,state, start_date from dba_scheduler_jobs where job_name like ‘LOAD_OPATCH%’;
no rows selected
Re-run datapatch
db02:orcl01:/u01/product/12.1.0.2/database/OPatch>./datapatch -verbose
Database: 12c Release 1
Error code: ORA-27477
Description: «string».»string» already exists
Cause: An attempt was made to create an object with a name that has already been used by another object in the same schema.
Action: Reissue the command using a different name or schema.
Database: 10g Release 1
Error code: ORA-27477
Description: «string.string» already exists
Cause: An attempt was made to create an object with a name that has already been used by another object in the same schema.
Action: Reissue the command using a different name or schema.
Database: 10g Release 2
Error code: ORA-27477
Description: «string.string» already exists
Cause: An attempt was made to create an object with a name that has already been used by another object in the same schema.
Action: Reissue the command using a different name or schema.
Database: 11g Release 1
Error code: ORA-27477
Description: «string.string» already exists
Cause: An attempt was made to create an object with a name that has already been used by another object in the same schema.
Action: Reissue the command using a different name or schema.
Database: 11g Release 2
Error code: ORA-27477
Description: «string.string» already exists
Cause: An attempt was made to create an object with a name that has already been used by another object in the same schema.
Action: Reissue the command using a different name or schema.
Database: 12c Release 2
Error code: ORA-27477
Description: «string».»string» already exists
Cause: An attempt was made to create an object with a name that has already been used by another object in the same schema.
Action: Reissue the command using a different name or schema.
Database: 18c Release 1
Error code: ORA-27477
Description: «string».»string» already exists
Cause: An attempt was made to create an object with a name that has already been used by another object in the same schema.
Action: Reissue the command using a different name or schema.
Database: 19c Release 1
Error code: ORA-27477
Description: «string».»string» already exists
Cause: An attempt was made to create an object with a name that has already been used by another object in the same schema.
Action: Reissue the command using a different name or schema.
This post is following a question I found on LinkedIn. A DBA pasted a strange test case in 11.2.0.4 and I managed to reproduce it in 12.1.0.2 (non-multitenant) and 12.2 (multitenant). But that’s not where the story ends, I wanted to understand what’s going on, so I did some research about it and the result is this post.
Testcase
First, let’s describe the problem. When using the scheduler, the job_name is like an object in the schema and it cannot have the same name as an existing object. The test case is to create a procedure, drop it, then try to create a job with the same name. We expect it to succeed, as the procedure was dropped, but it fails:
SQL> CREATE OR REPLACE PROCEDURE testobj AS BEGIN NULL; END; / Procedure created. SQL> DROP PROCEDURE testobj; Procedure dropped. SQL> BEGIN DBMS_SCHEDULER.create_job ( job_name => 'TESTOBJ', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN null; END;', start_date => systimestamp, repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0;', enabled => TRUE); END; / BEGIN * ERROR at line 1: ORA-27477: "DEF"."TESTOBJ" already exists ORA-06512: at "SYS.DBMS_ISCHED", line 175 ORA-06512: at "SYS.DBMS_SCHEDULER", line 288 ORA-06512: at line 2
However, if we now create a table with the same name after we dropped the procedure and then remove it, the job can be created.
Research
So now it’s time for some research. I checked DBA_OBJECTS but as expected I couldn’t find the dropped procedure. I knew that the object is there somewhere, I just needed to find it. It wasn’t in the recycle bin either (duh, procedures don’t go to the recycle bin). The next thing was to check the low level table OBJ$, and voila, it’s there.
Since it wasn’t in DBA_OBJECTS but it was in OBJ$, I needed to understand what happened to it. I opened the DBA_OBJECTS view to check the code (it queries the view _CURRENT_EDITION_OBJ which then queries OBJ$, but that’s not really relevant for this issue). In the source SQL I saw all the different object type numbers (OBJ$.TYPE# represents the object type, so 2 is table, 4 is view, 7 is procedure and so on). Then I checked the TYPE# of my dropped object and it was 10. The strange thing is that 10 is not in the DBA_OBJECTS view. Now it was time for some google search. Took me some time and some digging but I managed to find Jonathan Lewis‘s post about non-existent objects. In this post he explains that dropped objects are changed to type# 10 instead of being dropped for optimization purposes. Oracle should be able to reuse the name if we create a new object with the same name before restarting the database.
Back to our case. When we drop the procedure it can still be found in OBJ$ with type=10. If we create a table after the procedure is removed, the table is created and it reuses the procedure name. But if we don’t create a table, we have the dropped procedure with type 10, and it seems that DBMS_SCHEDULER cannot reuse the object name. For me, this seems like a bug specific to DBMS_SCHEDULER mechanism. So far I couldn’t find a known bug about it, will update here if I find something.
Update
I wanted to thanks Paul Sammy that found the bug for me (Bug 25979086). The bug states that it happens in 12.2.0.1 and above (which we know is not true) and is fixed in 12.2.0.2 (which doesn’t exists, but I hope it is fixed in 18c).