Lost my seq$ system table

Quote:

---

Originally posted by marist89
You don't have a backup, so how can you do PIT recovery?

---

Here i would say showing my grand-motherly affection
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$ :-?

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$ :-?

---

I hoped you would never asked that :) i know my test DB is ruined now. No sequence would ever work in it.

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.

Quote:

---

CAUTION:
Never try this 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 please feel free to delete this thread.

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.

---

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 someone is stupid enough to try to re-create their system tables instead of recovering from backup, they deserve to corrupt their DB.

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.

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.

---

He is one of those chaps who get funny ideas...

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

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.

---

HIM,i think, he is reffereing to Summit.

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