How to read the contents of Oracle Archive files (Log miner)

1. Determine which archive files you will read (e.x we will read archives between 395552..395557)


2. execute following command with user sys (Note that first add command called with sys.dbms_logmnr.new parameter and others callled with dbms_logmnr.addfile )
   
   begin
   sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395552_1_571790194.arc',options=>sys.dbms_logmnr.new);
   sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395553_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
   sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395554_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
   sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395555_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
   sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395556_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
   sys.dbms_logmnr.add_logfile (logfilename => '/cbdstarch/arch/arch_395557_1_571790194.arc',options=>sys.dbms_logmnr.addfile);
   sys.DBMS_LOGMNR.START_LOGMNR(options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
   
   end;
   /


3. You can select log content from V$LOGMNR_CONTENTS;
   select * from V$LOGMNR_CONTENTS;


4. Selecting from V$LOGMNR_CONTENTS is very slow and another restriction is You can select the content of V$LOGMNR_CONTENTS only from the session that you execute the dbms_logmnr commands.If you want to to query it multiple times suggest you to insert this data to another local table
   
   create table logminer_table tablespace tools as select * from V$LOGMNR_CONTENTS;
   
   also you can create index on it in order to query time based
   create index logminer_table_ix on logminer_table("TIMESTAMP") tablespace tools ;
   
   Note:Be carefull for the space of logminer_table .Each archive will cover 4-5 bigger than the size of the archive .(for the above example think each archive is about 100m than for 6 archive. logminer_table will cover nealy 6*5*100m =3g )


5. After you finish with log miner, execute following query:
   begin
   sys.dbms_logmnr.end_logmnr;
   end;


6. Then you can select it from table logminer_table
   

GPFS version determination

1. To find out which version of GPFS is running on a particular AIX node, enter:
   lslpp -l gpfs\*
2. To find out which version of GPFS is running on a particular Linux node, enter:
   rpm -qa grep gpfs
3. To find out which version of GPFS is running on a particular Windows node, use the graphical user interface (GUI) and follow these steps:
   
   Click Control Panel–>Add or Remove Programs
   Click IBM® General Parallel File System and choose Click here for support information.

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