Oracle Point, Oracle Life.

Applied to: Oracle Developer Suite 10.1.2.0.2

Oracle Developer Suite As an important database design tool, Oracle Designer is used to implement oracle database design and ER planning. To have Oracle Designer working, it’s required to create a Core Repository before using it.

To create Core Repository, go Oracle Developer Suite – DevSuiteHome –> Designer –> Repository Administration Utility. With successful logging on the target database where you want to create repository, you can start to install new repository via that utility. But, before you start to do it, it’s better to explicitly grant following privileges to repository owner as oracle DBA role:

• EXECUTE ON DBMS_LOCK
• EXECUTE ON DBMS_PIPE
• SELECT ON DBA_ROLLBACK_SEGS
• SELECT ON DBA_SEGMENTS
• SELECT ON V_$NLS_PARAMETERS
• SELECT ON V_$PARAMETER

Ignoring explicit granting might results in the following problem while creating new core repository:

• ORA-04063: package body “SCOTT.JR_WORKAREA” has errors

Even we can do re-compilation after repository is created by granting necessary privileges to repository owner, it’s still strongly recommended to do it before we create repository.

In addition, after core repository is created, it’s very likely that attempt to log on Designer would fail with following error:

• CDR-20043: Non-versioned repository has no workarea or insufficient privileges

Popularity: 46% [?]

Due to limitation of disk capacity, I was asked to resize one of our databases. Our investigation shows that the size of undo tablespace has been set to unnecessarily large, say 8G. Our junior DBA was trying to switch to newly created undo tablespace and have placed the datafile for default undo tablespace offline. After doing that, he got error code Ora-01157 and Ora-01110 when he tried to drop the prior default undo tablespace. To figure out the problem and perform the switch, we followed the following steps to have it done.

Step 1: Query to discover how many rollback segments were corrupted.

SQL> select segment_name, status, tablespace_name from    dba_rollback_segs where status=’NEEDS RECOVERY’;

SEGMENT_NAME    STATUS                  TABLESPACE_NAME
—————————— —————————— ——————————-
_SYSSMU1$          NEEDS RECOVERY UNDOTBS1
_SYSSMU2$          NEEDS RECOVERY UNDOTBS1
_SYSSMU3$          NEEDS RECOVERY UNDOTBS1
_SYSSMU4$          NEEDS RECOVERY UNDOTBS1
_SYSSMU5$          NEEDS RECOVERY UNDOTBS1
_SYSSMU6$          NEEDS RECOVERY UNDOTBS1
_SYSSMU7$          NEEDS RECOVERY UNDOTBS1
_SYSSMU8$          NEEDS RECOVERY UNDOTBS1
_SYSSMU9$          NEEDS RECOVERY UNDOTBS1
_SYSSMU10$          NEEDS RECOVERY UNDOTBS1

Step 2: Add the following line to pfile

_corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU21$')

Step 3: Shutdown database clearly 

$ sqlplus “/ as sysdba”

SQL> connect sys@dbname as sysdba

SQL> shutdown immediate

Step 4: Start database with newly updated pfile

SQL> startup pfile=/oracle/db10g/dbs/initDBNAME.ora

Step 5: Drop all of the corrupted rollback segments

SQL> drop rollback segment “_SYSSMU1”;

……………..

SQL> drop rollback segment “_SYSSMU10”;

Step 6: Now drop bad undo tablespace

SQL> drop tablespace UNDOTBS1;

Done!

Once finishing above steps, we went back to OEM console and confirmed that bad undo tablespace has been removed. The new undo tablespace is the one our Junior DBA created.

Popularity: 51% [?]