How to force log switch on every XXXX minutes (ARCHIVE_LAG_TARGET)

For performance reason It is preferred that redo switches should not be less than 15 minutes .So we must resize our redo logs optimally (for deatil look Redo logs sizing advisory article


When we plan our redo logs for peak hours.while off peak-hours the redo switch does not occur for hours .This sitiuation has some risk

1. In a case of catastrophic hardware failure, we may lose our current redo log having a enormous long redo data.There is no way to recover transactions that are sitting in the current redo log .


2. Standby and Primary database may not be close synchronous If you useMAXIMUM AVAILABILITY

so It will be better to switch log for each specified interval.ARCHIVE_LAG_TARGET parameter simply forces a log switch at a specified interval, and that can be very useful.

In order to set 15 minutes (900 seconds )

ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 900 SCOPE=BOTH;

ORA-02064: Distributed Operation not Supported

ORA-02064 may occur may occur when A commit(also rollback) or autocommit(like DDL) operation is executed within a procedure or function called remotely via database link.Examples are follows

Example-1 : commit issued on remote database

On database A:
create table serdar.bbb ( n number);

create or replace FUNCTION serdar.func_distributed_commit RETURN NUMBER IS
BEGIN
insert into bbb values (1);
commit;
RETURN 1;
END;
/

grant execute on serdar.func_distributed_commit to linkuser ;

Then, we call it from Database B:

DECLARE
rc NUMBER;
BEGIN
rc := serdar.func_distributed_commit@link.databaseA;
END;
/

ORA-02064: distributed operation not supported
ORA-06512: at "SERDAR.FUNC_DISTRIBUTED_COMMIT", line 4
ORA-06512: at line 4


Example-2 : DML issued on remote database

On database A:
CREATE OR REPLACE FUNCTION serdar.func_distributed RETURN NUMBER IS
BEGIN
EXECUTE IMMEDIATE 'create table serdar.bbb as select * from dba_tables';
RETURN 1;
END;
/

grant execute on serdar.func_distributed to linkuser;


Then, we call it from Database B:

DECLARE
rc NUMBER;
BEGIN
rc := serdar.func_distributed@link.databaseA;
END;
/
ORA-02064: distributed operation not supported
ORA-06512: at "SERDAR.FUNC_DISTRIBUTED", line 3
ORA-06512: at line 4


Other causes and reference note
Other causes:
ORA-02064:distributed operation not supported
Cause:
1.One of the following unsupported operations was attempted:1. array execute of a remote update with a subquery that references a dblink, or
2. an update of a long column with bind variable and an update of a second column with a subquery that both references a dblink and a bind variable, or
3. a commit is issued in a coordinated session from an RPC procedure call with OUT parameters or function call.
Action:simplify remote update statement

Workaround:
As a workaround Pragma AUTONOMOUS_TRANSACTION can be used in the called function or procedure in the remote site package.

REDO LOGS SIZING ADVISORY

Why redo log size is important

• The size of the redo log files can influence performance, Too small, and the frequent log switches tie-up the LGWR, ARCH and DBWR background processes.Undersized log files increase checkpoint activity and increase CPU usage.
  

How to Tune Checkpoint

• Checkpoint frequency is affected by several factors, including log file size and the setting of the FAST_START_MTTR_TARGET initialization parameter.There is an detail articale about tunning checkpoint (http://serdarturgut.blogspot.com/2007/09/tune-checkpoint.html)
  


• you can check it
  show parameter fast_start_mttr_target

How to check redo size and redo switch

• Prior to 10g, it was not possible to provide a specific size recommendation for redo log files. Generally it is recommended to size your redo log file in a way that Oracle performs a log switch every 15 to 30 minutes.
  


• You can check log switches from v$log_history in a day or per hour etc.
  select * from v$log_history where first_time >trunc(sysdate);
  


• You can check the log size from v$log
  
  select GROUP# , BYTES,archived,status from v$log;
  GROUP# BYTES ARC STATUS
  --------- ------------------------ --- ----------------
  1 10,485,760 YES ACTIVE
  2 10,485,760 NO CURRENT
  3 10,485,760 YES ACTIVE
  4 10,485,760 YES ACTIVE


• Note that ARCHIVE_LAG_TARGET can also be used to force a log switch after the specified amount of time elapses.
  show parameter archive_lag_target
  

How to set and Check the reso size advisory

• The redo logfile sizing advisory is a new feature in 10g where in the redo logfile size can tuned as per recommendation specified by column optimal_logfile_size of v$instance_recovery.This feature require setting the parameter "fast_start_mttr_target" for the advisory to take effect and populate the column optimal_logfile_size.
  select ACTUAL_REDO_BLKS ,TARGET_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR, OPTIMAL_LOGFILE_SIZE,CKPT_BLOCK_WRITES
  from v$instance_recovery;
  ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR OPTIMAL_LOGFILE_SIZE CKPT_BLOCK_WRITES
  ---------------- ---------------- ----------- -------------- -------------------- -----------------
  35190 55260 31 24 140 4144049
  


• The value for optimal_logfile_size is expressed in megabytes (For this example optimal size is 140m ) and it changes frequently, based on the DML load on your database.
  

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