| 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 a single log
file group member |
ALTER DATABASE
ADD LOGFILE MEMBER '<log_member_path_and_name>'
TO GROUP <group_number>; |
| ALTER DATABASE
ADD LOGFILE MEMBER '/oracle/dbs/log2b.log'
TO GROUP 2; |
| Add a log group containing two
members |
ALTER DATABASE ADD LOGFILE GROUP
<integer>
(<logfile_path_and_name>, <logfile_path_and_name>)
SIZE <integer><K | M>; |
ALTER DATABASE
ADD LOGFILE
GROUP 4
('/u01/logs/redo4a.log', '/u02/logs/redo4b.log')
SIZE 50M; |
Drop Log File Group |
ALTER DATABASE DROP [STANDBY] LOGFILE
GROUP <integer>; |
SELECT group#, status
FROM gv$log;
ALTER DATABASE
DROP LOGFILE GROUP
2; |
Dropping log file group member |
ALTER DATABASE DROP [STANDBY] LOGFILE MEMBER <logfile_member_path_and_name>; |
SELECT *
FROM gv$logfile
WHERE group# IN (
SELECT group#
FROM gv$log
WHERE status = 'INACTIVE');
ALTER DATABASE
DROP LOGFILE MEMBER '/oracle/dbs/log3c.log'; |
| Drop Log File by Descriptor |
ALTER DATABASE DROP [STANDBY] LOGFILE
<file_name>; |
| ALTER DATABASE
DROP LOGFILE
'/oracle/dbs/log3c.log'; |
| |
| Managing Log File
Threads |
Add a redo log containing two members to a thread |
ALTER DATABASE ADD LOGFILE THREAD <integer>
GROUP <integer>
(<logfile_path_and_name>, <logfile_path_and_name>); |
ALTER DATABASE
ADD LOGFILE THREAD
5 GROUP 3
('/u03/oradabase/redo315.log', '/home/oracle/orabase/redo325.log'); |
| |
| 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;
|
