ARCHIVE LOG

-

For sizing of archive log destination, 
1.it should be twice the size of maximum archive generated per day.
2.compressed backup of archive logs comes to 30 % of actual archive log size.
==============================================================


Simple query to find the archive generated
==============================================================
select trunc(completion_time),count(1),round(count(1)*200/1024) from v$archived_log group by  trunc(completion_time) order by  trunc(completion_time);



Query to give size of archive generated perday in GB
============================================================

SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024/1024) Daily_Avg_GB
FROM
(
SELECT
To_Char(First_Time,'YYYY-MM-DD') DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B;


Query to give archive generation on an hourly basis
==================================================================

set pagesize 120;
set linesize 200;
col day for a8;
select
  to_char(first_time,'YY-MM-DD') day,
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
  COUNT(*)
from v$log_history
group by to_char(first_time,'YY-MM-DD')
order by day ;

==================
Check Archive Gap:

select arch.thread# "Thread",arch.sequence# "Last sequence received", appl.sequence# "Last sequence applied",(arch.sequence#-appl.sequence#) "Difference" from (select thread#,sequence# from v$archived_log where (thread#,first_time) IN (select thread#,max(first_time) from v$archived_log group by thread#)) arch, (select thread#,sequence# from v$log_history where (thread#,first_time) IN (select thread#, max(first_time) from v$log_history group by thread#)) appl where arch.thread#=appl.thread# order by 1;





SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
; 



SQL> select al.thread#,  max(al.sequence#) "Last Recd", max(lh.sequence#) "Last Applied" from   (select thread#, sequence# from v$archived_log) al, (select thread#, sequence# from v$archived_log  where applied='YES' and registrar='RFS') lh where al.thread#=lh.thread# group by al.thread#  order by al.thread#; 
==============
Cross check Archive (RMAN):


CROSSCHECK ARCHIVELOG ALL;

delete noprompt expired archivelog all;

DELETE EXPIRED ARCHIVELOG ALL;





Comments

Post a Comment

Popular posts from this blog

Profile password verify function issue - From root

-
alter profile user limit password_verify_function verify_fun; Here we altered profile password_verify_function to limit verify_fun, but it has not reflected in profile limit. We got below output. Select * from dba_profiles where resource_name=’PASSWORD_VERIFY_FUNCTION’; PROFILE              RESOURCE_NAME             RESOURCE        LIMIT           COM  --------             -------------------------  -------------- ---------------- ----  DEFAULT              PASSWORD_VERIFY_FUNCTION   PASSWORD        NULL            NO  USERS                PASSWORD_VERIFY_FUNCTION   PASSWORD         FROM ROOT       YES  select * from profname$;  PROFILE#   NAME                     FLAGS  ---------- ------------------------- ----------  0         DEFAULT                   0  1         USERS                      1  Update flag values to 0. update profname$ set flags=0;   commit; select * from profname$;  PROFILE#   NAME                      FLAGS  ---------- ------------------------ ----------  0      
Read more

Change character set of Oracle database and using CSSAN and DMU

-
Change character set of Oracle database: shutdown the db: 1.shutdown immediate database start in restrict mode 2.startup restrict change the character set internal use 3.Alter database character set internal_use AL32UTF8; change the character set 4.Alter database character set AL32UTF8; bounce the database 5.shutdown immediate; 6. startup;  SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET'; ===================== Change character set from WE8MSWIN1252 to AL32UTF8: SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET'; VALUE$ --------------------------------------------------------------------------- WE8MSWIN1252 SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_NCHAR_CHARACTERSET'; VALUE$ --------------------------------------------------------------------------- AL16UTF16 SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and
Read more

Upload patch to OEM (12c/13c)

-
Image
EM 12c, EM 13c: How to upload a patch manually (Doc ID 1931686.1): Manually uploaded patches are shown in the Enterprise >> Provisioning and Patching >> Saved Patches page. This is also where you upload patches (from Desktop) First download the patch either from   Offline  - via a computer with a browser connected to the internet via  https://support.oracle.com  , search for the patch and then download to this computer locally. If this computer does not have access to the OMS then files needs to be transfered to a computer that does has access. or Online  - via the Enterprise Manager Cloud Console.  Go to Enterprise >> Provisioning and Patching >> Patches and Updates, search for the patch and download desktop (locally) OR download to Software Library The patch file, the format is p<patch_number>_<version>_<platform>.zip The patch metadata file, the format is p<patch_number>_<version>.American There is also the option to download the
Read more