【Oracle Database】数据库日志管理
发表于：2026-10-31 作者：千家信息网编辑
千家信息网最后更新 2026年10月31日，查询日志文件SQL> col member for a50SQL> select group#,status,type,member from v$logfile order by group#;
千家信息网最后更新 2026年10月31日【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安全错误
数据库的锁怎样保障安全
科技互联网政务服务
没上市的科技互联网公司
致骋互联网科技
广州紫鲸互联网科技
互联网科技的未来发展
长亮科技 互联网金融
互联网金融和金融科技
互联网金融科技委员会
互联网科技股午盘股票涨跌
山东吉时兔互联网科技有限公司
互联网科技公司需要的职位
佳云科技和互联网广告
武汉健翔互联网络科技有限公司
晋城互联网科技
长沙茂晟互联网科技有限公司
科技赋能视角下互联网保险发展
上海科技产业互联网平台
广州富喜互联网络科技
互联网界的黑科技
福仕德教育科技互联网
互联网科技产业
苏州找大状互联网科技有限公司
互联网金融监管科技数据
麻工互联网科技
唐信互联网科技
互联网科技股午盘股票涨跌
开启互联网科技时代小说
互联网5g黑科技有哪些
畅思互联网科技
版纳互联网科技选哪家
