1、查看Oracle归档日志模式
SQL> archive log list;
Database log mode Archive Mode --归档模式
Automatic archival Enabled
Archive destination /u01/oracle/archive_log
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
2、修改Oracle归档模式
1,关闭数据库:shutdown immediate;
2,启动数据库实例到mount状态:startup mount;
3,切换数据库日志模式:
alter database archivelog;(设置数据库为归档日志模式)
alter database noarchivelog;(设置数据库为非归档日志模式)
4,打开数据库:alter database open;
5,确认数据库处于归档模式:archive log list;
3、归档存放地点 Archive destination /u01/oracle/archive_log
(默认是USE_DB_RECOVERY_FILE_DEST,即$ORACLE_BASE/flash_recovery_area)
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 10G
默认情况下,归档日志会存放至闪回恢复区( /u01/oracle/fast_recovery_area)中,如果使用超过db_recovery_file_dest_size限制,归档日志无法继续归档,数据库HANG,
解决:增加闪回恢复区SIZE.
SQL> alter system set db_recovery_file_dest_size=3G;
4、变更归档存放地点
SQL> alter system set log_archive_dest_1='location=D:\arch';
验证:通过切换日志,判断路径下是否已经有归档日志产生 alter system switch logfile;
5、查看flash_recovery_area使用情况
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .13 0 1
ONLINELOG 2.93 0 3
ARCHIVELOG 96.62 0 141
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6、计算flash_recovery_area已经占用的空间
SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
2.9904
7、清理归档日志(删除归档日志后,必须使用RMAN维护控制文件,否则空间显示仍然不释放)
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
validation succeeded for archived log
archived log file name=/u01/oracle/archive_log1_1_977338902.dbf RECID=1 STAMP=978108565
validation succeeded for archived log
archived log file name=/u01/oracle/archive_log1_2_977338902.dbf RECID=2 STAMP=979034798
Crosschecked 2 objects
delete expired archivelog all;
delete archivelog until time 'sysdate-1' ;