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