Posts Tagged ‘How to Enable Archive Log’

Database Log Switch Intervals

December 4th, 2017, posted in Oracle Queries
Share

Oracle Database Log Switch Intervals :

 

Hourly Log Switch breakdown :

 

SELECT * FROM (
SELECT * FROM (
SELECT   TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '99') "00:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '99') "01:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '99') "02:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '99') "03:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '99') "04:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '99') "05:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '99') "06:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '99') "07:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '99') "08:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '99') "09:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '99') "10:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '99') "11:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '99') "12:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '99') "13:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '99') "14:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '99') "15:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '99') "16:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '99') "17:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '99') "18:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '99') "19:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '99') "20:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '99') "21:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '99') "22:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '99') "23:00"
    FROM V$LOG_HISTORY
    WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM < 30
/

Archive, Archive Log, Archive Log Mode, Check Oracle Archived, Check Oracle Archived Redo Log, Check Oracle Archiving, Check Oracle Redo Log, How to disable Archive Log Mode, How to Enable Archive Log, Log Mode, Oracle, Oracle Archived Redo, Oracle Archiving, Oracle Archiving Log, Oracle Database 10g, Oracle Database 11g, Oracle DBA, Oracle DBA Task, Redo Lob, Redo Log, Steps To Configure Archive Log Mode in Oracle Database 10g and 11g, Steps To Configure Archive Log Mode in Oracle Database 11g
Share
read more no comments

Oracle Database-Archive Log Sizes Spooled

November 25th, 2017, posted in Oracle Queries
Share

Database – Archive Log Sizes Spooled :Oracle : Size Of Database,Oracle,Size Of Database,Database,data files, redo log files, control files, temporary files,Oracle data files,Oracle redo log files,Oracle control files,Oracle temporary files,Enabling And Checking the Status of Flashback On Database,Oracle Database,Oracle DBA,Enabling Flashback On Database,Checking the Status of Flashback On Database, Status of Flashback On Database, Enable Flashback On Database, Enabling Flashback On Database,Enable Flashback On Oracle Database, Enabling Flashback On Oracle Database,

 

1) Info on archived logs spooled/deleted during the past 24 hours

2) Info on archived logs spooled/deleted during the previous 7 days

3) Info on archived logs spooled/deleted during the previous 31 days

 

1) Info on archived logs spooled/deleted during the past 24 hours:



SELECT SUM_ARCH.DAY,
 SUM_ARCH.GENERATED_MB,
 SUM_ARCH_DEL.DELETED_MB,
 SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
 FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 GENERATED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES'
 AND COMPLETION_TIME >SYSDATE-1
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
 ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 DELETED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
 WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
 ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');


 




 


2) Info on archived logs spooled/deleted during the previous 7 days:


SELECT SUM_ARCH.DAY,
 SUM_ARCH.GENERATED_MB,
 SUM_ARCH_DEL.DELETED_MB,
 SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
 FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 GENERATED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES'
 AND COMPLETION_TIME >SYSDATE-7
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
 ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 DELETED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
 WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
 ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');




 


3) Info on archived logs spooled/deleted during the past 31 days:


SELECT SUM_ARCH.DAY,
 SUM_ARCH.GENERATED_MB,
 SUM_ARCH_DEL.DELETED_MB,
 SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
 FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 GENERATED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES'
 AND COMPLETION_TIME >SYSDATE-31
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
 ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
 SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
 DELETED_MB
 FROM V$ARCHIVED_LOG
 WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
 GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
 WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
 ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');




Share


				

					read more
					no comments				

			




					

				
Steps To Configure Archive Log Mode in Oracle Database 11g

				April 24th, 2016, posted in Oracle Queries
				
Share
Mode of Logging


There are two types of logging modes in Oracle Database :


ARCHIVELOG : 

In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don’t have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.


NOARCHIVELOG :

In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data.


How to check log mode in Oracle Database 10g/11g :


[cognos@rac1 u02]$ sqlplus
 
 SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 25 23:03:44 2012
 
 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 Enter user-name: /as sysdba
 
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
 With the Partitioning, Automatic Storage Management, OLAP, Data Mining
 and Real Application Testing options
 
 SQL> archive log list
 Database log mode              No Archive Mode
 Automatic archival             Disabled
 Archive destination            /backup/orcl/
 Oldest online log sequence     1
 Current log sequence           1



 SQL> select name,log_mode from v$database;
 NAME      LOG_MODE
 --------- -    -----------
 ORCL      NOARCHIVELOG
 
 Currently the ORCL database is in NOARCHIVELOG mode


To change the Oracle database in ARCHIVELOG mode. Below mentioned steps :


1. If needed set the archive log destination where you want to save your archive logs whether to a single location or to multiple location. If this is not set then Oracle save archive log files in DB_RECOVERY_FILE_DEST location if set. If you have not set your DB_RECOVERY_FILE_DEST location then you have to set your archive location before changing your database to ARCHIVELOG mode.


SQL> alter system set log_archive_dest_1='LOCATION=/u02/archive' scope=spfile;
System altered.
 
Note -- To change this parameter while database is open, your database has to run with SPFILE, if running through PFILE then shut down your database and make changes in your PFILE and then start the database in MOUNT mode using that changed PFILE




2. After this you need to shut down your database and start again in MOUNT mode






SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.


 


Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.


SQL> alter database archivelog;
Database altered.


SQL> alter database open;


Database altered.


SQL> archive log list




Database log mode              Archive Mode
 Automatic archival             Enabled
 Archive destination            /u02/archive
 Oldest online log sequence     1
 Next log sequence to archive   1
 Current log sequence           1


SQL> select name,log_mode from v$database;

NAME      LOG_MODE
---------      ------------
ORCL      ARCHIVELOG


Database changed to ARCHIVELOG mode.


 


 


 Note :- After you changed your database to ARCHIVELOG mode, take a backup of your database immediately because in recovery scenarios you can recover your database from the last backup taken in this mode.  #############################################################################


 To change the Oracle database in NOARCHIVELOG mode. Below mentioned steps:


 


 1.Shutdown your running database.



SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.






2. Start your database in MOUNT mode.




SQL> startup mount
ORACLE instance started.




Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.




SQL> alter database noarchivelog;
Database altered.




SQL> alter database open;
Database altered.




SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/archive
Oldest online log sequence     1
Current log sequence           1




SQL> select name,log_mode from v$database;


NAME      LOG_MODE
---------      ------------
ORCL      NOARCHIVELOG






Database changed to NOARCHIVELOG mode !!




Share


				

					read more
					no comments