【Oracle Database】数据库日志管理-白红宇

【Oracle Database】数据库日志管理

阅读量：6278 次

发布时间：2019-06-22

本文共 12716 字，大约阅读时间需要 42 分钟。

查询日志文件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.log

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

转载于:https://blog.51cto.com/13598811/2132015

你可能感兴趣的文章

DELL EqualLogic PS存储数据恢复全过程整理

查看>>

《Node.js入门经典》一2.3　安装模块

查看>>

《Java 开发从入门到精通》—— 2.5 技术解惑

查看>>

Linux 性能诊断 perf使用指南

查看>>

实操分享：看看小白我如何第一次搭建阿里云windows服务器(Tomcat+Mysql)

查看>>