Oracle Log Files 11g

General
Data Dictionary Objects	all_log_groups user_log_groups v_$log_history dba_log_groups v_$instance_log_group v_$thread v_$log      – redo log file information from the control file v_$logfile – redo log groups and members and their member status v_$loghist  – log history
Status Privileges	Status Description active The online redo log is active and required for instance recovery, but is not the log to which the database is currently writing. It may be in use for block recovery, and may or may not be archived. clearing The log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, then the status changes to UNUSED. clearing_current The current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header. current The online redo log is active, that is, needed for instance recovery, and it is the log to which the database is currently writing. The redo log can be open or closed. inactive The log is no longer needed for instance recovery. It may be in use for media recovery, and may or may not be archived. unused The online redo log has never been written to.
Related Privileges	alter database alter system
init File Parameters	log_checkpoint_timeout … set to 0
Log Files Without Redundancy	LOGFILE   GROUP 1 ‘/u01/oradata/redo01.log’SIZE 50M,   GROUP 2 ‘/u02/oradata/redo02.log’SIZE 50M,   GROUP 3 ‘/u03/oradata/redo03.log’SIZE 50M,   GROUP 4 ‘/u04/oradata/redo04.log’SIZE 50M
Log Files With Redundancy (Group with multiple members)	LOGFILE  GROUP 1 (‘/u01/oradata/redo1a.log’,'/u05/oradata/redo1b.log’) SIZE 50M,  GROUP 2 (‘/u02/oradata/redo2a.log’,'/u06/oradata/redo2b.log’) SIZE 50M,  GROUP 3 (‘/u03/oradata/redo3a.log’,'/u07/oradata/redo3b.log’) SIZE 50M,  GROUP 4 (‘/u04/oradata/redo4a.log’,'/u08/oradata/redo4b.log’) SIZE 50M
Related Queries
View information on log files	SELECT * FROM gv$log;
View information on log file history	SELECT thread#, first_change#, TO_CHAR(first_time,’MM-DD-YY HH12:MIPM’), next_change# FROM gv$log_history;
Forcing log file switches	ALTER SYSTEM SWITCH LOGFILE; or ALTER SYSTEM CHECKPOINT;
Clear A Log File If It Has Become Corrupt	ALTER DATABASE CLEAR LOGFILE GROUP <group_number>; This statement overcomes two situations where dropping redo logs is not possible: If there are only two log groups The corrupt redo log file belongs to the current group.
	ALTER DATABASE CLEAR LOGFILE GROUP 4;
Clear A Log File If It Has Become Corrupt And Avoid Archiving	ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group_number>; Use this version of clearing a log file if the corrupt log file has not been archived.
	ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
Managing Log File Groups
Adding a redo log file group	ALTER DATABASE ADD LOGFILE (‘<log_member_path_and_name>’, ‘<log_member_path_and_name>’) SIZE <integer> <K|M>;
	ALTER DATABASE ADD LOGFILE (‘/oracle/dbs/log1c.rdo’, ‘/oracle/dbs/log2c.rdo’) SIZE 500K;
Adding a redo log file group and specifying the group number	ALTER DATABASE ADD LOGFILE GROUP <group_number> (‘<log_member_path_and_name>’) SIZE <integer> <K|M>;
	ALTER DATABASE ADD LOGFILE GROUP 4 (‘c:\temp\newlog1.log’) SIZE 100M;
Relocating redo log files	ALTER DATABASE RENAME FILE ‘<existing_path_and_file_name>’ TO ‘<new_path_and_file_name>’;
	conn / as sysdba SELECT member FROM v_$logfile; SHUTDOWN; host $ cp /u03/logs/log1a.log /u04/logs/log1a.log $ cp /u03/logs/log1b.log /u05/logs/log1b.log $ exit startup mount ALTER DATABASE RENAME FILE ‘/u03/logs/log1a.log’ TO ‘/u04/oradata/log1a.log’; ALTER DATABASE RENAME FILE ‘/u04/logs/log1b.log’ TO ‘/u05/oradata/log1b.log’; ALTER DATABASE OPEN host $ rm /u03/logs/log1a.log $ rm /u03/logs/log1b.log $ exit SELECT member FROM v_$logfile;
Drop a redo log file group	ALTER DATABASE DROP LOGFILE GROUP <group_number>;
	ALTER DATABASE DROP LOGFILE GROUP 4;
Managing Log File Members
Adding log file group members	ALTER DATABASE ADD LOGFILE MEMBER ‘<log_member_path_and_name>’ TO GROUP <group_number>;
	ALTER DATABASE ADD LOGFILE MEMBER ‘/oracle/dbs/log2b.rdo’ TO GROUP 2;
Dropping log file group members	ALTER DATABASE DROP LOGFILE MEMBER ‘<log_member_path_and_name>’;
	ALTER DATABASE DROP LOGFILE MEMBER ‘/oracle/dbs/log3c.rdo’;
Dumping Log Files
Dumping a log file to trace	ALTER SYSTEM DUMP LOGFILE ‘<logfile_path_and_name>’ DBA MIN <file_number> <block_number> DBA MAX <file_number> <block_number>; or ALTER SYSTEM DUMP LOGFILE ‘<logfile_path_and_name>’ TIME MIN <value> TIME MIN <value>
	conn uwclass/uwclass alter session set nls_date_format=’MM/DD/YYYY HH24:MI:SS’; SELECT SYSDATE FROM dual; CREATE TABLE test AS SELECT owner, object_name, object_type FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN ‘A’ AND ‘W’; INSERT INTO test (owner, object_name, object_type) VALUES (‘UWCLASS’, ‘log_dump’, ‘TEST’); COMMIT; conn / as sysdba SELECT ((SYSDATE-1/1440)-TO_DATE(‘01/01/2007′,’MM/DD/YYYY’))*86400 ssec FROM dual; ALTER SYSTEM DUMP LOGFILE ‘c:\oracle\product\oradata\orabase\redo01.log’ TIME MIN 579354757;
Disable Log Archiving
Stop log file archiving	The following is undocumented and unsupported and should be used only with great care and following through tests. One might consider this for loading a data warehouse. Be sure to restart logging as soon as the load is complete or the system will be at extremely high risk. The rest of the database remains unchanged. The buffer cache works in exactly the same way, old buffers get overwritten, old dirty buffers get written to disk. It’s just the process of physically flushing the redo buffer that gets disabled. I used it in a very large test environment where I wanted to perform a massive amount of changes (a process to convert blobs to clobs actually) and it was going to take days to complete. By disabling logging, I completed the task in hours and if anything untoward were to have happened, I was quite happy to restore the test database back from backup. ~ the above paraphrased from a private email from Richard Foote.
	conn / as sysdba SHUTDOWN; STARTUP MOUNT EXCLUSIVE; ALTER DATABASE NOARCHIVELOG; ALTER DATABASE OPEN; ALTER SYSTEM SET “_disable_logging”=TRUE;

Oracle Archive Log Mode 11g

General
Note: Archive logging is essential for production databases where the loss of a transaction might be fatal. It is generally considered unnecessary in development and test environments.
Init.ora Parameters
Configure for multiple archiver processes	log_archive_max_processes=<integer>;
	SELECT value FROM gv$parameter WHERE name = ‘log_archive_max_processes’; ALTER SYSTEM SET log_archive_max_processes=3; SELECT value FROM gv$parameter WHERE name = ‘log_archive_max_processes’;
Startup The Database In Archivelog Mode
Steps Required To Take A Database Not In Archive Log Mode And Alter It To Archive Log Mode	SELECT log_mode FROM gv$database; SHUTDOWN; STARTUP MOUNT EXCLUSIVE; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; SELECT log_mode FROM gv$database;
Startup The Database In NoArchivelog Mode
Steps Required To Take A Database In Archive Log Mode And Alter It To No Archive Log Mode	SELECT log_mode FROM gv$database; SHUTDOWN; STARTUP MOUNT EXCLUSIVE; ALTER DATABASE NOARCHIVELOG; ALTER DATABASE OPEN; SELECT log_mode FROM gv$database;
Restart After Archiving Logging Failure
Archive Logging Restart	SHUTDOWN; STARTUP; ARCHIVE LOG START; ARCHIVE LOG ALL;
Archive Log Related Commands
Start Archive Logging	alter system archive log start;
Stop Archive Logging	alter system archive log stop;
Force archiving of all log files	alter system archive log all;
Force archiving of the current log file	alter system archive log current;
Shell Scripts
Move Archive Logs	export ARCH_DIR=”/tmp/rim” NEW_DIR =”/tmp/rim/new_dir” export FILE_EXT=”arc” export MOVELIST=”/tmp/move.list” export CALF=”/tmp/calc.tmp” export TMPF=”/tmp/workfile.tmp” CMD=”ls -ltr $ARCH_DIR/*.$FILE_EXT | awk {‘print $9′}     | sort -r > $TMPF” export FILE_COUNT=” echo “Number of files foundis $FILE_COUNT” cat $TMPF echo $FILE_COUNT – 1″ > $CALF echo “quit” >> $CALF MOVE =”/usr/bin/bc/ $CALF” echo “Number of files to move is $MOVE” /usr/bin/tail -$MOVE $TMPF > $MOVELIST echo “File to be moved” cat $MOVELIST while read FILE do cho “Moving file $FILE to $NEW_DIR” done < $MOVELIST