【Oracle Database】数据库日志管理
发表于：2025-01-20 作者：千家信息网编辑
千家信息网最后更新 2025年01月20日，查询日志文件SQL> col member for a50SQL> select group#,status,type,member from v$logfile order by group#;
千家信息网最后更新 2025年01月20日【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安全错误
数据库的锁怎样保障安全
伦教网络安全找哪家
全民水浒的服务器
扬州专业联想服务器安装
腾讯企业邮箱的服务器地址
it软件开发软件技术
天涯明月刀各区服务器位置
电脑有网络安全模式有什么用
国务院网络安全日
软件开发成功比例
软件开发技术服务进项怎么提高
云赞客网络技术有限公司
女性手游研究数据库
地平线5 无法连接服务器
java 数据库基础
新乡市邦胜网络技术有限公司
贵州鲸潮互联网科技有限公司
考勤表和工资表数据库
高校网络安全规划
高宇网络技术公司
access数据库 分段
读计算机网络技术对口高考
看门狗2怎么不连接服务器
聊城软件开发公司排名
贵阳java软件开发
企业数据库备份架构
服务器空间分区管理
智能变电站数据服务器
安广计算机网络技术专业
警察网络安全教育宣讲
阿里服务器发展
