How To Drop, Create And Recreate DB Control In A 10g Database

1. Drop the DB Control Repository Objects manualy
   Drop AQ related objects in the SYSMAN schema connect with SYSMAN (sqlplus sysman)
   
   exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'MGMT_NOTIFY_QTABLE',force=>TRUE);
   
   Drop the DB Control Repository Objects
   SQL> SHUTDOWN IMMEDIATE;
   SQL> STARTUP RESTRICT;
   SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
   SQL> EXEC sysman.setEMUserContext('',5);
   SQL> REVOKE dba FROM sysman;
   SQL>
   DECLARE
   CURSOR c1 IS
   SELECT owner, synonym_name name FROM dba_synonyms
   WHERE table_owner = 'SYSMAN';
   BEGIN
   FOR r1 IN c1 LOOP
   IF r1.owner = 'PUBLIC' THEN
   EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ||' r1.name;
   ELSE
   EXECUTE IMMEDIATE 'DROP SYNONYM ' ||r1.owner ||'.' ||r1.name;
   END IF;
   END LOOP;
   END;
   /
   SQL> DROP USER mgmt_view CASCADE;
   SQL> DROP ROLE mgmt_user;
   SQL> DROP USER sysman CASCADE;
   SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;


2. Remove configration files and reposotistoy by emca
   emca -deconfig dbcontrol db -repos drop
   


3. Create the DB Control Repository Objects and Configuration Files
   emca -config dbcontrol db -repos create
   


4. Recreate the DB Control Configuration Files and Repository
   emca -config dbcontrol db -repos recreate


5. Create dbconsole on RAC system
   1-ORACLE_HOME and ORACLE_SID must be set
   2-ORACLE_HOME and ORACLE_HOME/bin are set in the environment variable $PATH
   3-Note the followings
   Database unique name (Database unique name)
   Listener port
   password of SYS,SYSMAN,DBSNMP (SYSMAN will be set)
   CLUSTER_NAME($CRS_HOME/bin/cemutlo -n)
   4-Check space in SYSAUX tablespace
   5-emca -config dbcontrol db -repos create -cluster
   6-emctl stat dbconsole
   
   

How to resize and/or add redo logs

The only way to resize log files is add new log groups and remove the old ones.
Suppose that we have 3 log group(50 m ) and we will resize then to 200m

1. Check the existing redo groups and files
   SELECT a.group#, b.member, a.status, a.bytes
   FROM v$log a, v$logfile b
   WHERE a.group#=b.group#;
   GROUP# MEMBER STATUS BYTES
   --------- ------------------- ---------------- ------------
   1 /redo01/redo01a.log CURRENT 52,428,800 1 /redo02/redo01b.log CURRENT 52,428,800 2 /redo01/redo02a.log INACTIVE 52,428,800 2 /redo02/redo02b.log INACTIVE 52,428,800 3 /redo01/redo03a.log INACTIVE 52,428,800 3 /redo02/redo03b.log INACTIVE 52,428,800
   
2. Add new groups (e.x group 4,5,6) and check them
   
   ALTER DATABASE ADD LOGFILE group 4
   ('/redo01/redo04a.log', '/redo02/redo04b.log') SIZE 200m;
   ALTER DATABASE ADD LOGFILE group 5
   ('/redo01/redo05a.log', '/redo02/redo05b.log') SIZE 200m;
   ALTER DATABASE ADD LOGFILE group 6
   ('/redo01/redo06a.log', '/redo02/redo06b.log') SIZE 200m;
   
   SELECT a.group#, b.member, a.status, a.bytes
   FROM v$log a, v$logfile b
   WHERE a.group#=b.group#;
   
   GROUP# MEMBER STATUS BYTES
   --------- ------------------- ---------------- ------------1 /redo01/redo01a.log ACTIVE 52,428,800 1 /redo02/redo01b.log ACTIVE 52,428,800 2 /redo01/redo02a.log INACTIVE 52,428,800 2 /redo02/redo02b.log INACTIVE 52,428,800 3 /redo01/redo03a.log CURRENT 52,428,800 3 /redo02/redo03b.log CURRENT 52,428,800 4 /redo01/redo04a.log UNUSED 209,715,200 4 /redo02/redo04b.log UNUSED 209,715,200 5 /redo01/redo05a.log UNUSED 209,715,200 5 /redo02/redo05b.log UNUSED 209,715,200 6 /redo01/redo06a.log UNUSED 209,715,200 6 /redo02/redo06b.log UNUSED 209,715,200
   
   
3. Drop the online redo log groups that are not needed (Group 3,4,5).
4. First check the status of the logs you can not drop if status in ACTIVE (Is archiving now ) or CURRENT (This is the current ).You can drop it if status ='INACTIVE'.
   
   SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
   GROUP# ARC STATUS
   --------- --- ----------------
   1 YES ACTIVE ----This archived by arc process
   2 YES INACTIVE
   3 NO CURRENT ---This is written by log writer process
   4 YES UNUSED
   5 YES UNUSED
   6 YES UNUSED
5. If you want to drop current log then switch logfile (alter system switch logfile) or if you want to drop active log then wait until it is archived and status become INACTIVE
6. Then drop 3 log group
   
   ALTER DATABASE DROP LOGFILE GROUP 2;
   alter system switch logfile; --We want to drop current log
   ALTER DATABASE DROP LOGFILE GROUP 3;
   ALTER DATABASE DROP LOGFILE GROUP 1;
7. Note that An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.)
8. Use operating system command to delete the dropped online redo log files (Be sure that the files belongs to dropped groups )
   
   rm /redo01/redo01a.log
   rm /redo02/redo01b.log
   rm /redo01/redo02a.log
   rm /redo02/redo02b.log
   rm /redo01/redo03a.log
   rm /redo02/redo03b.log
   
   
   

sqlplus / as sysdba, ORA-01031: insufficient privileges

One of the reason of this error is OS user must be belong to "osdba" group defined in group as defined in the "$ORACLE_HOME/rdbms/lib/config.s" or "$ORACLE_HOME/rdbms/lib/config.c". Typically this is set to "dba".

1. Check your OS user group e.x my group is dbap (not dba)
   %id
   uid=1030(oracle) gid=1030(dbap)
2. Check if this group is in /etc/group
   cat /etc/groupgrep dbap
3. Check config.s or config
   cd $ORACLE_HOME/rdbms/lib
   ls -lrt config.*
   cat config.s #It can be also config.c
4. correct group name in config.s file (example Change both ocurrences of dba to dbap )
   
   Sun SPARC Solaris
   .ascii "dbap\0"
   
   IBM AIX/Intel Solaris:
   .string "dbap"
5. To effect any changes to the groups and to be sure you are using the groups defined in this file relink the Oracle executable.Be sure to shutdown all databases before relinking
   
   mv config.o config.o.orig
   make -f ins_rdbms.mk ioracle

Incorrectly stored dates in a database

What is incorrect date

• You might sometimes find a DATE column in a database with incorrectly stored data like '00-000-0000'
• It can be unexpected errors like ORA-01841
• Or the result is correct but if you try to use it in where clause the result is incorrect

How can these dates end up in the database

• This issue is about your client side code and database provider
• if your client application uses OCI or Pro*C, then you can use the DATE external data type in OCI and Pro*C to insert illegal values into the database

Detection of these incorrect values

Use dump function on this data example (there are to rows looks similiar but their dump is not the same CIl_NCZ_VERILIS_TARIHI is a date cloumn an values kept as dd/mm/yyyy )

select CIl_NCZ_VERILIS_TARIHI,dump(a.CIl_NCZ_VERILIS_TARIHI,16) from aaa a;

30/11/2001 Typ=12 Len=7: 50,63,b,1e,1,1,1

30/11/2001 Typ=12 Len=7: 78,65,b,1e,1,1,1

How to find bad rows in a date column?

To find the rowid's of a date column giving ORA-01841 you can use a approach like:

DECLARE
CURSOR c1 IS SELECT rowid, DUMP (incorrectdate) as DUMP FROM incorrecttable;
v_incorrectdate incorrecttable.incorrectdate%TYPE;
date_error_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(date_error_detected,-1841);
BEGIN
FOR row_rec IN c1
LOOP
dbms_output.put('ROWID: 'row_rec.rowid' DUMP:'row_rec.dump);
BEGIN
SELECT incorrectdate INTO v_incorrectdate FROM incorrecttable WHERE rowid=row_rec.rowid;
dbms_output.put_line(' DATE:'v_incorrectdate);
EXCEPTION
WHEN date_error_detected THEN dbms_output.put_line(' DATE: Got ORA-01841 on this date');
END;
END LOOP;
END;
/

Relink with RAC OFF

If a RAC installed Oracle Home is copied to a Non rac standby so You must relink it.

1. Shutdown the database completely.
2. Relink with RAC OFF :
   cd $ORACLE_HOME/rdbms/lib
   make -f ins_rdbms.mk rac_off
   make -f ins_rdbms.mk ioracle
3. Startup the database

Space Management In Sysaux

The SYSAUX tablespace can grow larger than expected very quickly if Automatic Workload Repository (AWR) is taking too much space

1-Check the occupants

Select * from v$sysaux_occupants ;

2-Check the interval and retention of snaphshot generation

select * from DBA_HIST_WR_CONTROL ;

3-You can modify the interval and snaphshot generation e.x
interval =60 minutes
retention=4 x 24 x 60 = 5760 minutes default is 7 days
EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=> 60, retention => 5760 );

cache buffers chains

If "cache buffer chains" lacth is intensive then

1-Check if "cache buffer chains" exists
--If it is top in top latches


select substr(name,1,40),gets,misses*100/decode(gets,0,1,gets) misses,
spin_gets*100/decode(misses,0,1,misses) spins, immediate_gets igets,
immediate_misses*100/decode(immediate_gets,0,1,immediate_gets) imisses
from v$latch order by gets + immediate_gets
/

2-Check latch holders


set numwidth 5
select distinct lh.inst_id, s.sid, s.username, p.username os_user, lh.name
from gv$latchholder lh, gv$session s, gv$process p
where (lh.sid = s.sid and lh.inst_id = s.inst_id)
and (s.inst_id = p.inst_id and s.paddr = p.addr)
order by lh.inst_id, s.sid
/

3-List the latches and note the latch# number of most latch

SELECT latch#, substr(name,1,40), gets, misses, sleeps
FROM v$latch WHERE sleeps>0
ORDER BY sleeps ;

4-Find the latch child and note the addr of most used child

SELECT addr, latch#, gets, misses, sleeps
FROM v$latch_children
WHERE sleeps>100 and latch# = &LATCH_NUMBER_WANTED
ORDER BY sleeps ;
5-Find the file# number and block number of hot block

SELECT File# , dbablk, class, state ,tch
FROM x$bh WHERE hladdr='&ADDR_OF_CHILD_LATCH' order by tch;

6-Find the hot block

SELECT distinct owner, segment_name, segment_type
FROM dba_extents
WHERE file_id= &FILE_ID
and &BLOCK_NUMBER between block_id and block_id+blocks-1
/