How to find Log switches in oracle 11g Database 


let we discuss how to find Log switches in Database with Details of oracle11g


Note: V$LOG_HISTORY displays log history information from the control file.

Method-1:

I am going to take log switches count till before the month end
(i.e current date is '27-Sep-17' using last_day function 

it consider the date is '30-Sep-2017')



SQL> col c1 for a10 heading "Month"
SQL> col c2 for a25 heading "ArchiveDate"
SQL> col c3 for 999 heading "Switches"
SQL>  SQL> col c1 for a10 heading "Month"
SQL> col c2 for a25 heading "ArchiveDate"
SQL> col c3 for 999 heading "Switches"
SQL>  select to_char(trunc(first_time),'Month') c1,
to_char(trunc(first_time),'DAY:DD-MM-YY') c2,
count(*) c3 from V$log_history 
where first_time > last_day(trunc
(sysdate) - 30)+1 
group by trunc(first_time) order by trunc(first_time);



Month      ArchiveDate        Switches
---------- ----------------   --------
September  THURSDAY :14-09-17     2
September  FRIDAY   :15-09-17     1
September  MONDAY   :18-09-17     1
September  TUESDAY  :19-09-17     2
September  WEDNESDAY:20-09-17     1
September  THURSDAY :21-09-17     2
September  FRIDAY   :22-09-17     4
September  MONDAY   :25-09-17     1
September  TUESDAY  :26-09-17     2
September  WEDNESDAY:27-09-17     2


Method-2:
from the sysdate into last no of days if we want to take long switches count
Let remove the "last_day" function.



SQL> col c1 for a10 heading "Month"
SQL> col c2 for a25 heading "ArchiveDate"
SQL> col c3 for 999 heading "Switches"
SQL>  select to_char(trunc(first_time),'Month') c1,
to_char(trunc(first_time),'DAY:DD-MM-YY') c2,
count(*) c3 from V$log_history 
where first_time > (trunc(sysdate) - 30)  
group by trunc(first_time) 
order by trunc(first_time);



Note: First_time=Lowest system change number (SCN) in the log,

 Method-3:

 For global view of all instances of RAC databases



SQL> select  to_char(first_time,  'dd-mon-yy hh24:mi:ss' )"ArchiveDate",

sequence#, thread# from gv$log_history;
ArchiveDate                 SEQUENCE#    THREAD#
--------------------------- ---------- ----------
14-sep-17 16:21:42                   1          1
14-sep-17 16:21:53                   2          1
15-sep-17 15:08:36                   3          1
18-sep-17 09:42:27                   4          1
19-sep-17 09:40:55                   5          1
19-sep-17 12:36:53                   6          1
20-sep-17 09:43:03                   7          1
21-sep-17 09:44:31                   8          1
21-sep-17 15:31:55                   9          1
22-sep-17 09:44:11                  10          1
22-sep-17 10:41:59                  11          1
22-sep-17 12:59:20                  12          1
22-sep-17 13:29:42                  13          1
25-sep-17 09:00:19                  14          1
26-sep-17 09:43:03                  15          1
26-sep-17 15:44:32                  16          1
27-sep-17 10:24:34                  17          1
27-sep-17 10:29:09                  18          1

18 rows selected.

Popular posts from this blog

Error in starting the service (OracleMTSRecovery)

Image
 While i am installing the oracle 11g database i got the popup message " Error in starting the service.The service OracleMTSRecoveryService was not found " i just ignored the message and continue my installing but the installation was successful with errors, so once again i started the installation this time also i got the same error pop up and  searched on google to resolve this issue by using below steps S-1: S-2: press " Ctrl + r " then type " regedit " you 'll got this screen and  then choose " HKEY_LOCAL_MACHINE "   S-3:  Then choose " SYSTEM "  => " ControlSet001 " => " Services " => " OracleMTSRecoveryService "       S-4:                 After   choosing the " OracleMTSRecovery " we can see the " imagepath " which was mentioned below Double click the image and change the path where we are going to install the software(like be...
Read more

ORA-39000, ORA-39143 ORA- 31619 invalid dump file in datapump

Today i am tired to export  one schema (hr) from the one database  into another database  ( import schema ) so i created one directory which is called "ram" grant read and write access to  the system user ,then i started export the schema (hr) from production  while i did the export of hr schema the job was successful after that i did import schema like below expdp : C:\>expdp system/oracle directory=ram schemas=hr dumpfile=hr.dmp logfile=hr.log ...... ...... Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:   C:\datapump\HR.DMP Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 13:23:40 impdp: C:\>impdp system/oracle directory=ram remap_schema=hr:scott dumpfile=HR.DMP logfile=hr.log it through an error like below . ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31619: invalid dump file "C:\datapump\hr.dmp" ORA-27091: unable to queue I/O ORA-27070: async read/write failed OSD-04006: ReadFile() failu...
Read more

ORA-00439: feature not enabled: Flashback Database

Today we discuss ORA-00439: feature not enabled: Flashback Database ora error This is the test database I was performing flashback on the dummy table to deleted some rows  and tried to do the flashback above-mentioned Error S-1: SQL> flashback table hr.rg to timestamp to_timestamp('2018-04-18 12:57:00','YYYY -MM-DD HH:MI:SS'); flashback table hr.rg to timestamp to_timestamp('2018-04-18 12:57:00','YYYY-MM-D D HH:MI:SS') * ERROR at line 1: ORA-00439: feature not enabled: Flashback Table S-2: To view version of my oracle SQL> select * from V$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production PL/SQL Release 11.2.0.2.0 - Production CORE    11.2.0.2.0      Production TNS for 32-bit Windows: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production S-3: to features of my oracle edition SQL> SELECT Parameter, Valu...
Read more