ALTER DATABASE ADD LOGFILE GROUP [nr] ('[file]','[file]') size [size]M;
ALTER DATABASE ADD LOGFILE ('[file]') size [size]M;
remove group (group must by inactive - check v$log):
ALTER DATABASE DROP LOGFILE GROUP [group_nr];
add file to group:
ALTER DATABASE ADD LOGFILE MEMBER '[file]' TO GROUP [goup_nr];
remove file from group:
ALTER DATABASE DROP LOGFILE MEMBER '[file]';
turn on/off archivelog (mount mode):
ALTER DATABASE ARCHIVELOG|NOARCHIVELOG;
force archivie and switch redolog:
ALTER SYSTEM ARCHIVE LOG CURRENT;
(mount mode)
ALTER SYSTEM ARCHIVE LOG GROUP [group_nr];
archivelog info:
ARCHIVE LOG LIST;
switch redolog:
ALTER SYSTEM SWITCH LOGFILE;
force writing DIRTY BLOCKS from buffers (buffer cache) to datafile:
ALTER SYSTEM CHECKPOINT;
also for:
- change redologs(?)
- achieve LOG_CHECKPOINT_INTERVAL
- finish LOG_CHECKPOINT_TIMEOUT
- set ts of database in BACKUP mode
(SCN will be change only in header file of backup tablespace. During backup, in the other header files, SCN will be change normally without feedback with SCN in backup tablespace.
Tablespace after BACKUP mode adjust SCN to other files)
natomiast o ile zmiana redologów powoduje checkpoint to brak takiej zależności w drugą stronę
checkpoint (write blocks from cache to files):
ALTER SYSTEM CHECKPOINT;
1a. SCN in logfiles not change
1b. SCN in header datafiles change
check files checkpoint and SCN:
SELECT file#,checkpoint_change;checkpoint_time FROM v$datafile_header;
to show SCN must be set SQLPLUS param:
set numwidth 18
check current SCN (change every 3 sec.):
SELECT current_scn FROM v$database;
SELECT dbms_flashback.get_system_change_number FROM dual;
set rolling grop of redologs:
ARCHIVE_LAG_TARGET=1800 (default 0)redolog is writing again only when data from it war written to datafiles and to archivelogs
switch redolog process:
ALTER SYSTEM SWITCH LOGFILE;
1a. new redolog group get status CURRENT witch current SCN,
1b. present redolog group get status ACTIVE it means that it is necessary to recover,
2a. oracle wait for finish writing data to datafile
2b. SCN in header files is changed(SELECT checkpoint_change# FROM v$datafile_header;) is equal SCN of current redolog group (SELECT first_change# FROM v$log;)
2c. present redolog group change status from ACTIVE to INACTIVE (SELECT * FROM v$log;)
check redolog load:
SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM
x$kcccp cp,
x$kccle le
WHERE
le.leseq =cp.cpodr_seq
and bitand(le.leflg,24)=8;
x$kcccp - kernel cache, controlfile checkpoint progress
x$kccle - kernel cache, controlfile logfile entry
| VIEWS |
|---|
| V$LOG |
| V$LOGFILE |
| V$LOG_HISTORY |
| V$ARCHIVE_DEST |
| V$ARCHIVED_LOG |