【Oracle Database】数据库日志管理
发表于：2025-11-07 作者：千家信息网编辑
千家信息网最后更新 2025年11月07日，查询日志文件SQL> col member for a50SQL> select group#,status,type,member from v$logfile order by group#;
千家信息网最后更新 2025年11月07日【Oracle Database】数据库日志管理
查询日志文件SQL> col member for a50SQL> select group#,status,type,member from v$logfile order by group#; GROUP# STATUS TYPE MEMBER---------- ------- ------- -------------------------------------------------- 1 ONLINE /u01/app/oracle/oradata/wallet/redo01.log 2 ONLINE /u01/app/oracle/oradata/wallet/redo02.log 3 ONLINE /u01/app/oracle/oradata/wallet/redo03.log查询日志组SQL> select group#,sequence#,members,bytes,status,archived from v$log; GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC---------- ---------- ---------- ---------- ---------------- --- 1 25 1 52428800 INACTIVE YES 2 26 1 52428800 INACTIVE YES 3 27 1 52428800 CURRENT NO删除日志组1SQL> alter database drop logfile group 1;Database altered.SQL> host rm -rf /u01/app/oracle/oradata/wallet/redo01.log创建日志组1SQL> alter database add logfile group 1('/u01/app/oracle/oradata/wallet/redo01a.log','/u01/app/oracle/oradata/wallet/redo01b.log')size 50M;Database altered.删除日志组2SQL> alter database drop logfile group 2;Database altered.SQL> host rm -rf /u01/app/oracle/oradata/wallet/redo02.log创建日志组2SQL> alter database add logfile group 2('/u01/app/oracle/oradata/wallet/redo02a.log','/u01/app/oracle/oradata/wallet/redo02b.log')size 50M;Database altered.日志组切换SQL> alter system switch logfile;System altered.执行检查点SQL> alter system checkpoint;System altered.删除日志组3SQL> alter database drop logfile group 3;Database altered.SQL> host rm -rf /u01/app/oracle/oradata/wallet/redo03.log创建日志组3SQL> alter database add logfile group 3('/u01/app/oracle/oradata/wallet/redo03a.log','/u01/app/oracle/oradata/wallet/redo03b.log')size 50M;Database altered.SQL> select group#,status,type,member from v$logfile order by group#; GROUP# STATUS TYPE MEMBER---------- ------- ------- -------------------------------------------------- 1 ONLINE /u01/app/oracle/oradata/wallet/redo01a.log 1 ONLINE /u01/app/oracle/oradata/wallet/redo01b.log 2 ONLINE /u01/app/oracle/oradata/wallet/redo02b.log 2 ONLINE /u01/app/oracle/oradata/wallet/redo02a.log 3 ONLINE /u01/app/oracle/oradata/wallet/redo03b.log 3 ONLINE /u01/app/oracle/oradata/wallet/redo03a.log SQL> select group#,sequence#,members,bytes,status,archived from v$log; GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC---------- ---------- ---------- ---------- ---------------- --- 1 28 2 52428800 CURRENT NO 2 0 2 52428800 UNUSED YES 3 0 2 52428800 UNUSED YES增加日志文件SQL> alter database add logfile member'/u01/app/oracle/oradata/wallet/redo01c.log' to group 1,'/u01/app/oracle/oradata/wallet/redo02c.log' to group 2,'/u01/app/oracle/oradata/wallet/redo03c.log' to group 3;Database altered.SQL> select group#,status,type,member from v$logfile order by group#; GROUP# STATUS TYPE MEMBER---------- ------- ------- -------------------------------------------------- 1 ONLINE /u01/app/oracle/oradata/wallet/redo01b.log 1 INVALID ONLINE /u01/app/oracle/oradata/wallet/redo01c.log 1 ONLINE /u01/app/oracle/oradata/wallet/redo01a.log 2 ONLINE /u01/app/oracle/oradata/wallet/redo02a.log 2 ONLINE /u01/app/oracle/oradata/wallet/redo02b.log 2 INVALID ONLINE /u01/app/oracle/oradata/wallet/redo02c.log 3 ONLINE /u01/app/oracle/oradata/wallet/redo03a.log 3 ONLINE /u01/app/oracle/oradata/wallet/redo03b.log 3 INVALID ONLINE /u01/app/oracle/oradata/wallet/redo03c.logSQL> select group#,sequence#,members,bytes,status,archived from v$log; GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC---------- ---------- ---------- ---------- ---------------- --- 1 28 3 52428800 CURRENT NO 2 0 3 52428800 UNUSED YES 3 0 3 52428800 UNUSED YES 删除日志文件SQL> alter database drop logfile member'/u01/app/oracle/oradata/wallet/redo03c.log';Database altered.SQL> host rm -rf /u01/app/oracle/oradata/wallet/redo03c.logSQL> alter database drop logfile member'/u01/app/oracle/oradata/wallet/redo02c.log';Database altered.SQL> host rm -rf /u01/app/oracle/oradata/wallet/redo02c.logSQL> alter system switch logfile;System altered.SQL> alter system checkpoint;System altered.SQL> alter database drop logfile member'/u01/app/oracle/oradata/wallet/redo01c.log';Database altered.SQL> host rm -rf /u01/app/oracle/oradata/wallet/redo01c.log移动日志文件SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> quit[oracle@wallet01 ~]$ cp /u01/app/oracle/oradata/wallet/redo01b.log /u02/app/oracle/oradata/wallet/redo01b.log[oracle@wallet01 ~]$ cp /u01/app/oracle/oradata/wallet/redo02b.log /u02/app/oracle/oradata/wallet/redo02b.log [oracle@wallet01 ~]$ cp /u01/app/oracle/oradata/wallet/redo03b.log /u02/app/oracle/oradata/wallet/redo03b.log [oracle@wallet01 ~]$ sqlplus / as sysdbaSQL> startup mountORACLE instance started.Total System Global Area 1068937216 bytesFixed Size 2260088 bytesVariable Size 335545224 bytesDatabase Buffers 725614592 bytesRedo Buffers 5517312 bytesDatabase mounted.SQL> alter databaserename file '/u01/app/oracle/oradata/wallet/redo01b.log'to '/u02/app/oracle/oradata/wallet/redo01b.log';Database altered.SQL> alter databaserename file '/u01/app/oracle/oradata/wallet/redo02b.log'to '/u02/app/oracle/oradata/wallet/redo02b.log';Database altered.SQL> alter databaserename file '/u01/app/oracle/oradata/wallet/redo03b.log'to '/u02/app/oracle/oradata/wallet/redo03b.log';Database altered.SQL> alter database open;Database altered.SQL> select group#,status,type,member from v$logfile order by group#; GROUP# STATUS TYPE MEMBER---------- ------- ------- -------------------------------------------------- 1 ONLINE /u01/app/oracle/oradata/wallet/redo01a.log 1 ONLINE /u02/app/oracle/oradata/wallet/redo01b.log 2 ONLINE /u02/app/oracle/oradata/wallet/redo02b.log 2 ONLINE /u01/app/oracle/oradata/wallet/redo02a.log 3 ONLINE /u02/app/oracle/oradata/wallet/redo03b.log 3 ONLINE /u01/app/oracle/oradata/wallet/redo03a.logSQL> select dbid,name,log_mode from v$database; DBID NAME LOG_MODE---------- --------- ------------3215665862 WALLET NOARCHIVELOGSQL> archive log list;Database log mode No Archive ModeAutomatic archival DisabledArchive destination ?/dbs/archOldest online log sequence 28Current log sequence 29SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 1068937216 bytesFixed Size 2260088 bytesVariable Size 335545224 bytesDatabase Buffers 725614592 bytesRedo Buffers 5517312 bytesDatabase mounted.SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archive01/ mandatory';System altered.SQL> alter system set log_archive_dest_2='LOCATION=/u02/app/oracle/archive02/ optional';System altered.SQL> alter database archivelog;Database altered.SQL> alter database open;Database altered.SQL> select dbid,name,log_mode from v$database; DBID NAME LOG_MODE---------- --------- ------------3215665862 WALLET ARCHIVELOGSQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u02/app/oracle/archive02/Oldest online log sequence 28Next log sequence to archive 29Current log sequence 29SQL> show parameter log_archive_dest_1;NAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------log_archive_dest_1 string LOCATION=/u01/app/oracle/archi ve01/ mandatory SQL> show parameter log_archive_dest_2;NAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------log_archive_dest_2 string LOCATION=/u02/app/oracle/archi ve02/ optionalSQL> show parameter log_archive_maxNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------log_archive_max_processes integer 4SQL> show parameter log_archive_min_succeed_destNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------log_archive_min_succeed_dest integer 1SQL> show parameter log_archive_formatNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------log_archive_format string %t_%s_%r.dbf手动归档日志文件SQL> alter system archive log current;System altered.归档目的地SQL> set line 200SQL> col dest_name for a30SQL> col destination for a30SQL> select dest_name,status,binding,destination from v$archive_dest;DEST_NAME STATUS BINDING DESTINATION------------------------------ --------------------------- --------------------------- ------------------------------LOG_ARCHIVE_DEST_1 VALID MANDATORY /u01/app/oracle/archive01/LOG_ARCHIVE_DEST_2 VALID OPTIONAL /u02/app/oracle/archive02/LOG_ARCHIVE_DEST_3 INACTIVE OPTIONALLOG_ARCHIVE_DEST_4 INACTIVE OPTIONALLOG_ARCHIVE_DEST_5 INACTIVE OPTIONALLOG_ARCHIVE_DEST_6 INACTIVE OPTIONALLOG_ARCHIVE_DEST_7 INACTIVE OPTIONALLOG_ARCHIVE_DEST_8 INACTIVE OPTIONALLOG_ARCHIVE_DEST_9 INACTIVE OPTIONALLOG_ARCHIVE_DEST_10 INACTIVE OPTIONALLOG_ARCHIVE_DEST_11 INACTIVE OPTIONAL归档进程SQL> select * from v$archive_processes; PROCESS STATUS LOG_SEQUENCE STATE---------- ------------------------------ ------------ ------------ 0 ACTIVE 0 IDLE 1 ACTIVE 0 IDLE 2 ACTIVE 0 IDLE 3 ACTIVE 0 IDLE 4 STOPPED 0 IDLE 5 STOPPED 0 IDLE 6 STOPPED 0 IDLE 7 STOPPED 0 IDLE 8 STOPPED 0 IDLE 9 STOPPED 0 IDLE 10 STOPPED 0 IDLE 归档日志文件SQL> set line 200SQL> col name for a50SQL> col status for a10SQL> select dest_id,name,sequence#,status from v$archived_log; DEST_ID Tablespace Name SEQUENCE# Status---------- -------------------------------------------------- ---------- ---------- 1 /u01/app/oracle/archive01/1_29_1007721545.dbf 29 A 2 /u02/app/oracle/archive02/1_29_1007721545.dbf 29 A 1 /u01/app/oracle/archive01/1_30_1007721545.dbf 30 A 2 /u02/app/oracle/archive02/1_30_1007721545.dbf 30 A 1 /u01/app/oracle/archive01/1_31_1007721545.dbf 31 A 2 /u02/app/oracle/archive02/1_31_1007721545.dbf 31 A 1 /u01/app/oracle/archive01/1_32_1007721545.dbf 32 A 2 /u02/app/oracle/archive02/1_32_1007721545.dbf 32 A
日志
文件
查询
手动
检查点
目的
目的地
进程
切换
检查
移动
数据
数据库
管理
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
中山中盛互联网科技有限公司
中国软件开发语言排名
高性能服务器如何选择
数据库升级预算
先锋服务器一体机
奉贤区新能源软件开发厂家价格
服务器液冷产品概念股
苏州市鼎鼎旺软件开发
浅谈国产数据库的现状及发展绪论
软件开发时发票是开什么明细
数据库管理系统体系结构
软件开发行业的市场概述
菜刀连接mysql数据库
2008r2数据库如何修复
江苏戴尔服务器免费咨询
嘉定区自动化软件开发诚信服务
永安数据库修复
网络技术服务费会计分录
全国信息网络安全
查找数据库端口
微信苹果协议软件开发
网络安全三化六防落实情况
美文数据库下载
股票进数据库怎么写
西安交大 网络安全
数据库接受
杂质制备数据库
沭阳网络技术价格查询
云南军工卫星授时服务器
全局主题软件开发
