【OracleDatabase】数据库日志管理
查询日志文件 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
沙市网站制作公司哪家好,找创新互联公司!从网页设计、网站建设、微信开发、APP开发、成都响应式网站建设等网站项目制作,到程序开发,运营维护。创新互联公司自2013年创立以来到现在10年的时间,我们拥有了丰富的建站经验和运维经验,来保证我们的工作的顺利进行。专注于网站建设就选创新互联公司。
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
本文名称:【OracleDatabase】数据库日志管理
网页链接:http://pwwzsj.com/article/jidoej.html