| 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 |
|
| 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 10M,
GROUP 2 '/u02/oradata/redo02.log'SIZE 10M,
GROUP 3 '/u03/oradata/redo03.log'SIZE 10M,
GROUP 4 '/u04/oradata/redo04.log'SIZE 10M |
| Log Files With Redundancy | LOGFILE
GROUP 1 ('/u01/oradata/redo1a.log','/u05/oradata/redo1b.log') SIZE 10M,
GROUP 2 ('/u02/oradata/redo2a.log','/u06/oradata/redo2b.log') SIZE 10M,
GROUP 3 ('/u03/oradata/redo3a.log','/u07/oradata/redo3b.log') SIZE 10M,
GROUP 4 ('/u04/oradata/redo4a.log','/u08/oradata/redo4b.log') SIZE 10M |
| |
| 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 ;
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 ;
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
('', '')
SIZE ; |
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
('') SIZE ; |
ALTER DATABASE ADD LOGFILE GROUP 4
('c:tempnewlog1.log') SIZE 100M; |
| Relocating redo log files | 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 ; |
| ALTER DATABASE DROP LOGFILE GROUP 4; |
| |
| Managing Log File Members |
| Adding log file group members | ALTER DATABASE
ADD LOGFILE MEMBER ''
TO GROUP ; |
ALTER DATABASE
ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo'
TO GROUP 2; |
| Dropping log file group members | ALTER DATABASE DROP LOGFILE MEMBER ''; |
| ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo'; |
| |
| Dumping Log Files |
| Adding log file group members | D |
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;
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/1988','MM/DD/YYYY'))*86400 ssec
FROM dual;
ALTER SYSTEM DUMP LOGFILE 'c:oracleproductoradataorabaseredo01.log' TIME MIN 579354757; |