Hi Experts
How to recover seq$ system table?
Regards
Printable View
Hi Experts
How to recover seq$ system table?
Regards
Why? How did u arrive at this idea of recovering a system table? Do you get any errors? If so, post them verbatim.
HTHU.
Run this as sys as sysdba.
Code:CREATE TABLE SEQ$
(
OBJ# NUMBER NOT NULL,
INCREMENT$ NUMBER NOT NULL,
MINVALUE NUMBER,
MAXVALUE NUMBER,
CYCLE# NUMBER NOT NULL,
ORDER$ NUMBER NOT NULL,
CACHE NUMBER NOT NULL,
HIGHWATER NUMBER NOT NULL,
AUDIT$ VARCHAR2(38) NOT NULL,
FLAGS NUMBER
)
TABLESPACE SYSTEM
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
CREATE UNIQUE INDEX I_SEQ1 ON SEQ$
(OBJ#)
LOGGING
TABLESPACE SYSTEM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
This script is for 9iR2 databases.
will that work? doubt it really
Hi
I am getting the following error. I shutdown my database as 'shutdown immediate';
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
my alter log file
=================
Mon Apr 21 15:35:30 2003
Errors in file d:\oracle\admin\buchku\udump\buchku_ora_1576.trc:
ORA-00942: table or view does not exist
Error 942 happened during db open, shutting down database
USER: terminating instance due to error 942
Instance terminated by USER, pid = 1576
ORA-1092 signalled during: ALTER DATABASE OPEN...
I know it is seq$ table, what got deleted
Regards
Hi Adweri
My database is Oracle 9i and release 2, but I can not startup my database presently. So how to execute your stuff?
Pls need help.
Regards
you have to do some recovery, it´s not that easy to recover a data dictionary table, we wish huh
Hi Pando,
I tried the same now.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> recover automatic database;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Please help me to come out from this problem. I highly appreciate your time.
Regards
What is the contents of the above file? BTW, how are u so sure it is seq$ table? Hope u did not delete it yourself?Quote:
Originally posted by sumit
Errors in file d:\oracle\admin\buchku\udump\buchku_ora_1576.trc
did you restore a backup?
do you have archived logs?
if NO to all then byebye or call support
Hi,
I was just doing some R&D and deleted that table. But before deleting the same I created one clone of that table in my database. But now the problem is I could not start up my database.
Here is the content of "d:\oracle\admin\buchku\udump\buchku_ora_1576.trc" ...
Dump file d:\oracle\admin\buchku\udump\buchku_ora_1576.trc
Mon Apr 21 15:35:30 2003
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 3, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 3, CPU type 586
Instance name: buchku
Redo thread mounted by this instance: 1
Oracle process number: 15
Windows thread id: 1576, image: ORACLE.EXE
*** SESSION ID:(11.3) 2003-04-21 15:35:30.000
ORA-00942: table or view does not exist
(assumption) As it is an unimportant database (used for R&D), you could as well do a fresh install and create another database without much sweat!Quote:
Originally posted by sumit
I was just doing some R&D and deleted that table.
Hi Pando,
I do not have backup. But I have archive log
Can I restore my database?
omg
how can you apply log if you dont have a backup, please use some common sense
Pndo u r right.
What I wanted to ask u that, is point in time recovery will work here?
Or what is the solution, if any system table will be deleted?
regards
if you have a valid backup and archived log then why it would not work....?
oracle records everything in the logs including data dictionary changes
Expect one more retardation from Pando. Pando clearly says that without any sort of backup how can u implement any type of recovery. How can u do R&D on System tables.God only has to help u.
Yes it works.Quote:
Originally posted by pando
will that work? doubt it really
Don't try this on any prod databases.Code:[email protected]D> startup
ORACLE instance started.
Total System Global Area 303111628 bytes
Fixed Size 454092 bytes
Variable Size 226492416 bytes
Database Buffers 75497472 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
[email protected]D>
[email protected]D>
[email protected]D>
[email protected]D> create table seq_backup as select * from seq$;
Table created.
[email protected]D> drop table seq$;
Table dropped.
[email protected]D> CREATE TABLE SEQ$
2 (
3 OBJ# NUMBER NOT NULL,
4 INCREMENT$ NUMBER NOT NULL,
5 MINVALUE NUMBER,
6 MAXVALUE NUMBER,
7 CYCLE# NUMBER NOT NULL,
8 ORDER$ NUMBER NOT NULL,
9 CACHE NUMBER NOT NULL,
10 HIGHWATER NUMBER NOT NULL,
11 AUDIT$ VARCHAR2(38) NOT NULL,
12 FLAGS NUMBER
13 )
14 TABLESPACE SYSTEM
15 PCTUSED 40
16 PCTFREE 10
17 INITRANS 1
18 MAXTRANS 255
19 STORAGE (
20 INITIAL 64K
21 MINEXTENTS 1
22 MAXEXTENTS 2147483645
23 PCTINCREASE 0
24 FREELISTS 1
25 FREELIST GROUPS 1
26 BUFFER_POOL DEFAULT
27 )
28 LOGGING
29 NOCACHE
30 NOPARALLEL;
Table created.
[email protected]D>
[email protected]D>
[email protected]D> CREATE UNIQUE INDEX I_SEQ1 ON SEQ$
2 (OBJ#)
3 LOGGING
4 TABLESPACE SYSTEM
5 PCTFREE 10
6 INITRANS 2
7 MAXTRANS 255
8 STORAGE (
9 INITIAL 64K
10 MINEXTENTS 1
11 MAXEXTENTS 2147483645
12 PCTINCREASE 0
13 FREELISTS 1
14 FREELIST GROUPS 1
15 BUFFER_POOL DEFAULT
16 )
17 NOPARALLEL;
Index created.
[email protected]D> insert into seq$ select * from seq_backup;
167 rows created.
[email protected]D> commit;
Commit complete.
[email protected]D> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[email protected]D> startup
ORACLE instance started.
Total System Global Area 303111628 bytes
Fixed Size 454092 bytes
Variable Size 226492416 bytes
Database Buffers 75497472 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
[email protected]D>
Though it may not be solution for sumits question, where i earlier assumed that the database was in open state.
And won't work even if it was open as sumit would not have any backup of the seq$ table :)
You don't have a backup, so how can you do PIT recovery?Quote:
Originally posted by sumit
Pndo u r right.
What I wanted to ask u that, is point in time recovery will work here?
Or what is the solution, if any system table will be deleted?
regards
Here i would say showing my grand-motherly affectionQuote:
Originally posted by marist89
You don't have a backup, so how can you do PIT recovery?
that this is a classic case for "sumit RTFM" http://craniumabuse.com/chat/emote/56.gif
Sumit:
Try incomplete recovery until time.Hope that works.
Quote:
Originally posted by jomon_s
Sumit:
Try incomplete recovery until time.Hope that works.
another one who needs to RTFM
gez christ how can you recover until time without a backup?
adewri, it works but what happens if obj$ references sequences in seq$ :-?
I hoped you would never asked that :) i know my test DB is ruined now. No sequence would ever work in it.Quote:
Originally posted by pando
another one who needs to RTFM
gez christ how can you recover until time without a backup?
adewri, it works but what happens if obj$ references sequences in seq$ :-?
Doing some test now but will need to recreate the DB anyway.
Hey pando guess what it worked :D
i just ran utlrp and presto...
its working because i did take a backup of seq$ table before dropping it, other wise yes i could have kissed my DB goodbye.
NOTE: Pando (and other moderators) if you feel that this information can be dangerous as some chap may get funny ideas and play around with his prod DB, then please feel free to delete this thread.Quote:
CAUTION:
Never try this on you production databases.
Code:SQL*Plus: Release 9.2.0.3.0 - Production on Mon Apr 21 20:48:32 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
[email protected]D> startup
ORACLE instance started.
Total System Global Area 101784796 bytes
Fixed Size 453852 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
[email protected]D>
[email protected]D>
[email protected]D>
[email protected]D> create table seq_back as select * from seq$;
Table created.
[email protected]D> drop table seq$;
Table dropped.
[email protected]D>
[email protected]D> CREATE TABLE SEQ$
2 (
3 OBJ# NUMBER NOT NULL,
4 INCREMENT$ NUMBER NOT NULL,
5 MINVALUE NUMBER,
6 MAXVALUE NUMBER,
7 CYCLE# NUMBER NOT NULL,
8 ORDER$ NUMBER NOT NULL,
9 CACHE NUMBER NOT NULL,
10 HIGHWATER NUMBER NOT NULL,
11 AUDIT$ VARCHAR2(38) NOT NULL,
12 FLAGS NUMBER
13 )
14 TABLESPACE SYSTEM
15 PCTUSED 40
16 PCTFREE 10
17 INITRANS 1
18 MAXTRANS 255
19 STORAGE (
20 INITIAL 64K
21 MINEXTENTS 1
22 MAXEXTENTS 2147483645
23 PCTINCREASE 0
24 FREELISTS 1
25 FREELIST GROUPS 1
26 BUFFER_POOL DEFAULT
27 )
28 LOGGING
29 NOCACHE
30 NOPARALLEL;
Table created.
[email protected]D>
[email protected]D>
[email protected]D> CREATE UNIQUE INDEX I_SEQ1 ON SEQ$
2 (OBJ#)
3 LOGGING
4 TABLESPACE SYSTEM
5 PCTFREE 10
6 INITRANS 2
7 MAXTRANS 255
8 STORAGE (
9 INITIAL 64K
10 MINEXTENTS 1
11 MAXEXTENTS 2147483645
12 PCTINCREASE 0
13 FREELISTS 1
14 FREELIST GROUPS 1
15 BUFFER_POOL DEFAULT
16 )
17 NOPARALLEL;
Index created.
[email protected]D>
[email protected]D>
[email protected]D> insert into seq$ select * from seq_back;
87 rows created.
[email protected]D> commit;
Commit complete.
[email protected]D> select object_name,status from all_objects where status='INVALID';
OBJECT_NAME STATUS
------------------------------ -------
ALL_SEQUENCES INVALID
DBA_LOGSTDBY_NOT_UNIQUE INVALID
DBA_OBJ_AUDIT_OPTS INVALID
DBA_SEQUENCES INVALID
DBMSOBJG INVALID
DBMS_METADATA_UTIL INVALID
EXU7SEQ INVALID
EXU7SEQU INVALID
EXU8SEQ INVALID
EXU8SEQU INVALID
KU$_AUDIT_OBJ_BASE_VIEW INVALID
KU$_AUDIT_OBJ_VIEW INVALID
KU$_SEQUENCE_VIEW INVALID
LTUTIL INVALID
SM$AUDIT_CONFIG INVALID
USER_OBJ_AUDIT_OPTS INVALID
USER_SEQUENCES INVALID
17 rows selected.
[email protected]D> @?/rdbms/admin/utlrp
PL/SQL procedure successfully completed.
Table created.
Table created.
Table created.
Index created.
Table created.
Table created.
View created.
View created.
Package created.
No errors.
Package body created.
No errors.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
[email protected]D> select object_name, status from dba_objects where status='INVALID';
no rows selected
[email protected]D> CREATE SEQUENCE emp_sequence
2 INCREMENT BY 1
3 START WITH 1
4 NOMAXVALUE
5 NOCYCLE
6 CACHE 10;
Sequence created.
[email protected]D> select emp_sequence.nextval from dual;
NEXTVAL
----------
1
[email protected]D> select emp_sequence.nextval from dual;
NEXTVAL
----------
2
[email protected]D> create table t (x int);
Table created.
[email protected]D> insert into t values (emp_sequence.nextval);
1 row created.
[email protected]D> insert into t values (emp_sequence.nextval);
1 row created.
[email protected]D> insert into t values (emp_sequence.nextval);
1 row created.
[email protected]D> insert into t values (emp_sequence.nextval);
1 row created.
[email protected]D> commit;
Commit complete.
[email protected]D> select * from t;
X
----------
3
4
5
6
[email protected]D> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[email protected]D> startup
ORACLE instance started.
Total System Global Area 101784796 bytes
Fixed Size 453852 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
[email protected]D> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
SCOTT
OUTLN
WMSYS
ORDSYS
ORDPLUGINS
MDSYS
LBACSYS
HR
OE
QS_ADM
QS
QS_WS
QS_ES
QS_OS
QS_CBADM
QS_CB
QS_CS
20 rows selected.
[email protected]D> select object_name,object_type from all_objects where owner='SCOTT';
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
BONUS TABLE
DEPT TABLE
EMP TABLE
PK_DEPT INDEX
PK_EMP INDEX
SALGRADE TABLE
6 rows selected.
If someone is stupid enough to try to re-create their system tables instead of recovering from backup, they deserve to corrupt their DB.Quote:
Originally posted by adewri
on you production databases.
NOTE: Pando (and other moderators) if you feel that this information can be dangerous as some chap may get funny ideas and play around with his prod DB, then you are free to delete this thread.
If it is a production database then Amar has to find him a job in his company, if it is a test database then is a good lesson for sumit.
He is one of those chaps who get funny ideas...Quote:
Originally posted by akhadar
If it is a production database then Amar has to find him a job in his company, if it is a test database then is a good lesson for sumit.
Akhadar i never said that i tested this on the prod DB, this was a test(on test DB) to see if we can create system table or not using the script that i had posted earlier on this thread.
you should RTFT(Thread) before saying anything.
Amar,
I think u got him(akhadar) wrong.
As far i see the statement
HIM,i think, he is reffereing to Summit.Quote:
If it is a production database then Amar has to find him a job in his company, if it is a test database then is a good lesson for sumit.
now i think u shud LEL ;)
Abhay.
http://craniumabuse.com/chat/emote/33.gifhmmm... could have been more clearhttp://craniumabuse.com/chat/emote/22.gif
Hai Amar iam not referring to u in my reply.It's only to sumit why there is any need to do R&d on system tables.
My apologies, i seem to loose patience easily these days...Quote:
Originally posted by akhadar
Hai Amar iam not referring to u in my reply.It's only to sumit why there is any need to do R&d on system tables.
That's how people learn and I love that.
Thanks
May be New Delhi is too hot now a days or one more day of bulk load.Quote:
Originally posted by adewri
My apologies, i seem to loose patience easily these days...