--- Archive Redo Logs see Oracle Server Administrator's Guide (Chap. Archiving Redo Information) for more information
To switch the database to noarchivelog :
shutdown;
startup mount;
alter database noarchivelog;
alter database open;
To switch the database to archive log mode:
shutdown;
startup mount;
alter database archivelog;
alter database open;
To enable automatic archiving of redo logs set log_archive_start=true in init.ora file.
To enable automatic archiving after instance startup:
alter system archive log start;
To switch the logs:
alter system switch logfile;
Destination of archive logs:
log_archive_dest=/dir/
To change the destination of archive files shutdown the instance, change the parm in init.ora and then restart the instance
Format of archive file names:
log_archive_format=arch%t_%s.arc
%s is the sequence number
%t is the thread number
-- Redo's see Oracle Server Administrator's Guide (Chap. Managing the Online Redo Log)
-- How should you size the redo logs:
-- Considerations
-- If logs are to large:
- Then you MAY loose the active log and all its transactions, in case of media failure.
- How much data can you afford to loose in case of recovery? (If you use mirroring of the logs this risk is minimized)
-- If logs are to small:
- Each time oracle switches between logs Oracle does a lot of things (Checkpoint of the database is one of them).
This may cause performance degradation if it is to frequent. Another thing is that a log is not done archiving
when oracle wants to write transactions to it again, oracle waits (no transactions) until its freed.
A good rule of thumb is to size the redo logs so that Oracle archives about once each hour.
-- If the instance cannot be shutdown because there are pending transactions and the archiver is not archiving
-- for any reason you can add a redo log to finish the tx's and then shutdown.
alter database add logfile group 4
('/db02/oradata/DBSTAR/DBSTAR_log04a.dbf','/db03/oradata/DBSTAR/DBSTAR_log04b.dbf') size 20M;
-- Data Dictionary queries:
Archiving information: select * from v$database;
Logs to be archived: select group#, archived from v$log;
select * from v$logfile;
Show archiving information: archive log list;
SQL> select * from V$ARCHIVE_DEST;
ARCMODE STATUS DESTINATION
------------ -------- ----------------------------------------------------------------------------------------------
MUST SUCCEED NORMAL /db01/app/oracle/admin/DBSTAR/arch/a
SQL> select * from v$archive;
GROUP# THREAD# SEQUENCE# CUR FIRST_CHANGE#
---------- ---------- ---------- --- -------------
1 1 19327 NO 110712590
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 19327 20971520 2 NO INACTIVE 110712590 2000-11-01:02:35:20
2 1 19328 20971520 2 NO CURRENT 110715338 2000-11-01:07:46:21
3 1 19326 20971520 2 YES INACTIVE 110712555 2000-11-01:02:15:11
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
SQL> select SEQUENCE# ,FIRST_TIME, NEXT_TIME from v$archived_log order by 2;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ----------------- -----------------
6188 20191028 07:46:27 20191028 07:57:01
6189 20191028 07:57:01 20191028 07:58:50
6190 20191028 07:58:50 20191028 08:15:31
6191 20191028 08:15:31 20191028 08:54:29
6192 20191028 08:54:29 20191028 11:33:17
6193 20191028 11:33:17 20191028 17:34:50
6194 20191028 17:34:50 20191028 22:50:27
6195 20191028 22:50:27 20191029 02:00:10
6196 20191029 02:00:10 20191029 04:00:16
6197 20191029 04:00:16 20191029 04:00:37
6198 20191029 04:00:37 20191029 04:45:47
|