HOW TO MANAGE DATABASE IN ARCHIVE LOG
-- init.ora parameters
set log_checkpoints_to_alert=TRUE
remove log_checkpoint_timeout, log_checkpoint_interval
-- general
set numformat 999999999999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
-- turn on archive log mode
steps
A. configure
1) alter system set db_recovery_file_dest='+ORAFLASH001' scope=both sid='*';
2) alter system set db_recovery_file_dest_size = 20G scope=both sid='*';
B. archivelog mode
1) shutdown database
2) startup database in mount mode
3) alter database archivelog; # as sysdba
4) archive log list # as sysdba
sys > ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 416
Next log sequence to archive 418
Current log sequence 418
5) open database
-- FIX archive log destination to ORAFLASH001 in 10g using ASM, if set incorrectly
steps
# of course db_recovery_file_dest should be set to +ORAFLASH001
1) alter system set log_archive_dest_10='LOCATION=use_db_recovery_file_dest' scope=both sid='*';
2) alter system set log_archive_dest_1='' scope=both sid='*'; # to null out this parameter
3) alter system switch logfile; # to test, check alert log
others
alter system switch logfile;
alter system archive log current;
alter system archive log all;
-- increase archive log destination size. but 1st find out, How much archived
steps
1) select count(*),sum(blocks*block_size) from v$archived_log where backup_count=0 and deleted='NO';
2) increase db_recovery_file_dest_size
ALTER SYSTEM SET db_recovery_file_dest_size = 30G SCOPE=BOTH SID='*';
3) SHOW PARAMETER RECOVERY
-- Check space utilization
steps
1. col name format a20
set numwidth 18
select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------- ----------- ---------- ----------------- ---------------
+ORAFLASH001 2147483648 1271777792 596992 33
2. select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .71 0 1
ONLINELOG 0 0 0
ARCHIVELOG 16.81 .03 24
BACKUPPIECE 41.63 0 8
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
-- list archived files not backed-up
select NAME, ARCHIVED , deleted, status
from v$archived_log
where status='A';
others
SELECT SEQUENCE#, FIRST_TIME, FIRST_CHANGE#, NEXT_TIME, NEXT_CHANGE#
FROM v$archived_log;
SELECT sequence#, thread#, first_change#, next_change#
FROM v$archived_log;
SELECT MAX(sequence#)
FROM v$archived_log;
-- find archive logs to be restored
select completion_time,name,sequence#,first_change#,first_time,next_change#,next_time, archived
from v$archived_log;
select f.member,to_char(v.first_change#)
, v.sequence#
from v$log v
, v$logfile f
where v.group# = f.group#
and v.status='CURRENT';
-- for recovery
archive log list # get current sequence number
-- last redo log file that was in use (last sequence number -1)
select recid
, stamp
, sequence#
, first_change#
, next_change#
from v$log_history
where recid = <current log sequence -1) # from last step
This will show us the NEXT_CHANGE#, i.e.the highest SCN, in the redo
log file. It will also give us the FIRST_CHANGE# SCN in
this redo log file.
-- this gives current redo log file. archive for recovery
select f.member,to_char(v.first_change#)
, v.sequence#
from v$log v
, v$logfile f
where v.group# = f.group#
and v.status='CURRENT';
-- misc
Alter system archive log [start|stop|all|...]
alter system switch logfile;
alter system archive log all;
alter system archive log next;
alter system archive log sequence 104;
alter system archive log current;
alter system archive log stop;
alter system archive log current noswitch;
alter system checkpoint;
alter system flush buffer_cache;
alter system flush buffer_pool;
alter system quiesce restricted;
alter system suspend|resume;
alter system switch logfile;
-- how to get current scn
# in 10g
alter system checkpoint;
select current_scn from v$database ;
or To get the current SCN, use the following:
SET SERVEROUTPUT ON
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE('Current SCN is ' || iscn);
END;
/
# in 9i
To get the time/date for an SCN value in 9i, use the following example:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- (a) Get the current SCN base.
SQL>select max(scn_bas) SCN_BASE from smon_scn_time;
1603342197
--(b) Get the complete SCN and the timestamp.
SQL> alter session set NLS_DATE_FORMAT='DD-MON-YY HH:MI:SS';
SQL> select time_dp TIMESTAMP, scn_wrp*4294967296+SCN_bas SCN from smon_scn_time
where scn_bas='1603342197';
TIMESTAMP SCN
------------------ ----------
28-JUL-06 05:31:08 8252235517813
-- restore archive log using seqeunce
run {
allocate channel demo_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier1)';
restore archivelog from sequence 35347 until sequence 35359;
}
run {
allocate channel demo_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2)';
set archivelog destination to '/oraarc01/oracle/arch/demo/' ;
restore archivelog from logseq=70241 until logseq=70250 ;
}
run {
allocate channel demo_t3 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2)';
set archivelog destination to '/opt/splexmv/tmparclog/' ;
restore archivelog sequence between 70276 and 70376 ;
}
-- restore archive log using time
run {
allocate channel icmt_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2,NB_ORA_CLIENT=rs204-bkup)';
restore archivelog
from time = "to_date('Apr 5 2005 19:20:00','Mon DD YYYY HH24:MI:SS')"
until time = "to_date('Apr 5 2005 20:00:00','Mon DD YYYY HH24:MI:SS')";
}
-- backup archive log
run {
allocate channel ch1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';
set command id to 'rman backup';
backup
maxsetsize = 1G
diskratio = 0
skip inaccessible
tag = 'Archive Logs'
(archivelog all delete input format'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p');
}
-- backup database including archivelog
backup setsize = 62914560
(database)
plus archivelog
skip inaccessible;
backup
full
filesperset = 1024
diskratio = 0
tag = 'Full'
(database format 'SBT_TAPE/RMAN_%d.%t.full.%c.%s.%p')
plus archivelog
skip inaccessible
delete input
format 'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p';
rman target / nocatalog <<EOF
backup as copy archivelog all format '$v_rman_loc/%d_AL_%T_%u_s%s_p%p' delete input;
list backupset;
exit
EOF
rman target / nocatalog <<EOF
run {
allocate channel backup_disk1 type disk format '$v_bdest/%U';
allocate channel backup_disk2 type disk format '$v_bdest/%U';
backup as copy archivelog
sequence between $v_scni1 and $v_scnf1 thread 1;
backup as copy archivelog
sequence between $v_scni2 and $v_scnf2 thread 2;
release channel backup_disk1 ;
release channel backup_disk2 ;
}
exit
EOF
run {
# USE THIS COMMAND FILE FOR ARCHIVELOG Backups
# USE THIS COMMAND FILE FOR DATABASES RUNNING IN ARCHIVELOG MODE ONLY !
#backup FILESPERSET = 4
# skip inaccessible
# ( archivelog all ) ;
BACKUP FILESPERSET = 4 skip inaccessible ARCHIVELOG ALL NOT BACKED UP 1 TIMES delete all input ;
sql "ALTER SYSTEM ARCHIVE LOG CURRENT" ;
backup FILESPERSET = 4
skip inaccessible
( archivelog all NOT BACKED UP 1 TIMES );
}
-- dynamic performance views
Dynamic Performance View Description
V$DATABASE
Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode.
V$ARCHIVED_LOG
Displays historical archived log information from the control file.
If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST
Describes the current instance, all archive destinations, and the current value, mode,
and status of these destinations.
V$ARCHIVE_PROCESSES
Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG
Contains information about any backups of archived logs. If you use a recovery catalog,
the RC_BACKUP_REDOLOG contains similar information.
V$LOG
Displays all online redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY
Contains log history information such as which logs have been archived and the SCN range
for each archived log.
Blogged with the Flock Browser
