文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

【Oracle Database】数据库日志管理

2024-04-02 19:55

关注
查询日志文件
SQL> col member for a50
SQL> 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

删除日志组1
SQL> alter database drop logfile group 1;
Database altered.

SQL> host rm -rf /u01/app/oracle/oradata/wallet/redo01.log

创建日志组1
SQL> alter database add logfile group 1
('/u01/app/oracle/oradata/wallet/redo01a.log','/u01/app/oracle/oradata/wallet/redo01b.log')
size 50M;
Database altered.

删除日志组2
SQL> alter database drop logfile group 2;
Database altered.

SQL> host rm -rf /u01/app/oracle/oradata/wallet/redo02.log

创建日志组2
SQL> 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.

删除日志组3
SQL> alter database drop logfile group 3;
Database altered.

SQL> host rm -rf /u01/app/oracle/oradata/wallet/redo03.log

创建日志组3
SQL> 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.log

SQL> 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.log

SQL>  alter database drop logfile member
'/u01/app/oracle/oradata/wallet/redo02c.log';
Database altered.

SQL> host rm -rf /u01/app/oracle/oradata/wallet/redo02c.log


SQL> 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 immediate
Database 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 sysdba
SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             335545224 bytes
Database Buffers          725614592 bytes
Redo Buffers                5517312 bytes
Database mounted.

SQL> alter database
rename file '/u01/app/oracle/oradata/wallet/redo01b.log'
to '/u02/app/oracle/oradata/wallet/redo01b.log';
Database altered.

SQL> alter database
rename file '/u01/app/oracle/oradata/wallet/redo02b.log'
to '/u02/app/oracle/oradata/wallet/redo02b.log';
Database altered.

SQL> alter database
rename 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.log


SQL> select dbid,name,log_mode from v$database;

      DBID NAME      LOG_MODE
---------- --------- ------------
3215665862 WALLET    NOARCHIVELOG

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            ?/dbs/arch
Oldest online log sequence     28
Current log sequence           29

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             335545224 bytes
Database Buffers          725614592 bytes
Redo Buffers                5517312 bytes
Database 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    ARCHIVELOG

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/app/oracle/archive02/
Oldest online log sequence     28
Next log sequence to archive   29
Current log sequence           29

SQL> 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/ optional
SQL> show parameter log_archive_max
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_max_processes            integer                           4

SQL> show parameter log_archive_min_succeed_dest
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_min_succeed_dest         integer                           1

SQL> show parameter log_archive_format
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_format                   string                            %t_%s_%r.dbf

手动归档日志文件
SQL> alter system archive log current;
System altered.

归档目的地
SQL> set line 200
SQL> col dest_name for a30
SQL> col destination for a30
SQL> 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                    OPTIONAL
LOG_ARCHIVE_DEST_4             INACTIVE                    OPTIONAL
LOG_ARCHIVE_DEST_5             INACTIVE                    OPTIONAL
LOG_ARCHIVE_DEST_6             INACTIVE                    OPTIONAL
LOG_ARCHIVE_DEST_7             INACTIVE                    OPTIONAL
LOG_ARCHIVE_DEST_8             INACTIVE                    OPTIONAL
LOG_ARCHIVE_DEST_9             INACTIVE                    OPTIONAL
LOG_ARCHIVE_DEST_10            INACTIVE                    OPTIONAL
LOG_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 200
SQL> col name for a50
SQL> col status for a10
SQL> 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


阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯