oracle数据库修改归档模式
标签(空格分隔): oracle
##查看归档模式
su - oracle
sqlplus / as as sysdba
archive log list;
##查看日志产生频率
select a.recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') begin_time,
b.recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,
round((b.first_time - a.first_time)*24*60,2) minutes
from v$log_history a,v$log_history b
where b.recid = a.recid+1 and to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') >=to_char(sysdate-10,'yyyy-mm-dd hh24:mi:ss') ;
##查看归档目录大小
###ASM磁盘组大小及剩余空间
set lines 150 pages 200
col path for a40
col HEADER_STATUS for a9
col disk_name for a12
col type for a16
col bytes for 999,999,999,999
col name for a52
col name2 for a15
col group_number heading 'Group|_NO' format 99
col file_number heading 'FILE|NO' format 9999
col redundancy format a6 noprint
col striped format a6 noprint
col FAILGROUP for a10
col disk_number heading 'Disk|_NO' format 9999
col MOUNT_STATUS heading 'Mount|_Status'
col FAILGROUP heading 'FAIL|_GROUP'
col TOTAL_MB heading 'Total|_MB' format 99,999,999
select name name2,state,type,free_mb/1024 free_G,total_mb/1024 free_G,
usable_file_mb/1024 usable_file_G from v$asm_diskgroup;
###本地存储大小
linux : df -h
AIX: df -g
unix: bdf
##操作步骤:
1、切换到oracle用户下,并切换到正确实例:
su - oracle
sqlplus / as sysdba
2、备份spfile文件
create pfile='/home/oracle/pfile20181213.ora' from spfile;
3、重启数据库,开启/关闭归档模式(rac两个节点均关闭,使用一个节点修改,修改完成后启动另一个节点)
shutdown immediate;
startup mount;
4、修改归档路径
alter system set log_archive_dest_1 = 'LOCATION=/backup';
5、开启归档模式:
alter database archivelog;
6、启动数据库:
alter database open;
7、检查是否开启成功:
archive log list;
8、切换日志,查看归档目录下产生归档文件
alter system switch logfile;