【Oracle Database】数据库日志管理
发表于：2025-02-23 作者：千家信息网编辑
千家信息网最后更新 2025年02月23日，查询日志文件SQL> col member for a50SQL> select group#,status,type,member from v$logfile order by group#;
千家信息网最后更新 2025年02月23日【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安全错误
数据库的锁怎样保障安全
炉石传说连接不了服务器
新科技下互联网经济
网络安全法宣传目的
苹果手机服务器无信号
广州小程序软件开发平台
海陵区多功能网络技术大概费用
阿里发布自研arm服务器芯片
国家安全网络安全绘画
洗浴管理软件数据库失败
22和100在数据库中比较大小
工业网络技术实训摘要
地平线4微软服务器太差了吧
浙江智能软件开发配件
绍兴云软件开发
网信办网络安全领导小组
镇江康乐网络技术游戏
深圳爱达普网络技术有限公司
软件开发企业项目管理制度
农村网络安全宣传照片
odps连接数据库
手机代理服务器apk
网络技术面试技巧
清除代理服务器
无锡联想服务器维修费用
小米路由迷你cups服务器
旧机器服务器
虹口区通用软件开发服务商密度
数据库中的系统模型分几种
趣动互联网科技
软件开发时期包含哪些阶段
