Home » RDBMS Server » Server Administration » system ORACLE ID all objects were removed (RDBMS 12C Linux)
system ORACLE ID all objects were removed [message #687551] Sat, 01 April 2023 09:14 Go to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
Hello all,
I have a huge problem, my UNIX admin logged into the database as system and run some command that removed all tables under the system ID. I do have a backup, but is datapump if I try to restore the data from the backup I get these msg

ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06512: at "SYS.KUPV$FT", line 1140
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 924

I have run counteless times and the registry still invalid
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql

in the alert.log i just see these msg

ORA-942 encountered when generating server alert SMG-4120
ORA-942 encountered when generating server alert SMG-4121

any help would be appreciated
Re: system ORACLE ID all objects were removed [message #687552 is a reply to message #687551] Sat, 01 April 2023 11:47 Go to previous messageGo to next message
John Watson
Messages: 8916
Registered: January 2010
Location: Global Village
Senior Member
I've just had a look at the system schema in a 19.17 EE database, it has 131 tables (I'll upload the list). Do you actually have a problem? Most of the tables seem to be to do with the Log Miner, Logical Standby (which is basically the Log Miner), and (I think) materialized view replication. Do you need those facilities? THere are also some for Advanced Queues, which could be more important, and for stored outlines which are deprecated.

Are your applications failing? If so, you need to raise a P1 TAR with Support. Otherwise, no hurry to fix it: P2 will do.
Re: system ORACLE ID all objects were removed [message #687553 is a reply to message #687552] Sat, 01 April 2023 13:23 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
i cant restore or backup any schema using datapump, and all registry is invalid, users /applications can still login and perform queries, I thought in recreating the instance but I do have 30 or more schemas
in other oracle 12C under the oracle System id
SQL> select count(*) from user_objects;

COUNT(*)
----------
457

the problem one
SQL> select count(*) from user_objects;

COUNT(*)
----------
8

SQL>
Re: system ORACLE ID all objects were removed [message #687554 is a reply to message #687553] Sat, 01 April 2023 13:32 Go to previous messageGo to next message
John Watson
Messages: 8916
Registered: January 2010
Location: Global Village
Senior Member

Quote:
users /applications can still login and perform queries
So, no immediate problem. As I said, you need to raise a TAR with Oracle Support.

I can certainly believe that Data Pump won't work, because it uses Advanced Queues. Support should be able to tell you how to recreate the system schema. You are paying for it, so use it.
Re: system ORACLE ID all objects were removed [message #687555 is a reply to message #687554] Sat, 01 April 2023 13:41 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
Yes I do have a ticket already open with oracle, unfortunately they are extremely slow this is going on for 1 week now, and still nothing very frustrating
Re: system ORACLE ID all objects were removed [message #687556 is a reply to message #687555] Sun, 02 April 2023 07:30 Go to previous messageGo to next message
John Watson
Messages: 8916
Registered: January 2010
Location: Global Village
Senior Member
You could try using the old exp/imp utilities to extract the system objects from a working database and import them into the broken one. It might not work, but you do not have a lot to lose at this point.
Re: system ORACLE ID all objects were removed [message #687557 is a reply to message #687556] Sun, 02 April 2023 13:09 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
yea i tried that and the same problem persists
Re: system ORACLE ID all objects were removed [message #687609 is a reply to message #687557] Thu, 13 April 2023 06:35 Go to previous message
Andrey_R
Messages: 439
Registered: January 2012
Location: Israel
Senior Member
You can create a copy of the server, Install Oracle from scratch on it ( same folder structure ) , migrate the data and rebuild the jobs and processes from the current server.

Off course, after coordinating and scheduling downtime for maintenance with all parties involved.
Previous Topic: PROBLEAM OPEN REDOLOG FILE
Next Topic: No SCN in controlfile after BACKUP TO TRACE
Goto Forum:
  


Current Time: Mon Feb 26 04:26:18 CST 2024