Oracle 如何规范清理v$archived_log记录实例详解

时间:2021-11-20 04:52:14

Oracle 如何规范清理v$archived_log记录实例详解

单机实例上面,v$archived_log 很多,有上万条记录了,所以得清理一下,不然每次查询都直接滚屏幕了

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> select sequence#,applied from v$archived_log order by sequence# ;
 
 SEQUENCE# APPLIED
....................
 SEQUENCE# APPLIED
---------- ---------
   9376 NO
   9377 NO
   9377 NO
   9378 NO
   9378 NO
   9379 NO
   9379 NO
   9380 NO
   9380 NO
   9381 NO
   9381 NO
 
 SEQUENCE# APPLIED
---------- ---------
   9382 NO
   9382 NO
 
11200 rows selected.
 
SQL>

然后查看下当前的归档记录

?
1
2
3
4
5
6
7
8
SQL> archive log list;
Database log mode     Archive Mode
Automatic archival     Enabled
Archive destination    USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence   164
Next log sequence to archive  166
Current log sequence      166
SQL>

看到归档记录才是164,和v$archived_log里面上W的记录数不匹配,这是因为这是rman备份恢复遗留下来的记录,所以需要清理一下。

清理记录,采用sys.dbms_backup_restore.resetCfileSection(11);清理:

?
1
2
3
4
5
6
7
8
9
SQL> execute sys.dbms_backup_restore.resetCfileSection(11);
 
PL/SQL procedure successfully completed.
 
SQL> select sequence#,applied from v$archived_log order by sequence# ;
 
no rows selected
 
SQL>

再次测试,可以查看到日志记录变化了,v$archived_log已经是最新的,只有一条记录数存在了:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> alter system switch logfile;
 
System altered.
 
SQL> select sequence#,applied from v$archived_log order by sequence# ;
 
 SEQUENCE# APPLIED
---------- ---------
    166 NO
 
SQL> execute sys.dbms_backup_restore.resetCfileSection(11);
 
PL/SQL procedure successfully completed.
 
SQL> select sequence#,applied from v$archived_log order by sequence# ;
 
no rows selected
 
SQL>

扩展话题,单机实例可以用上,述办法操作,那么Oracle集群比如dg呢,分析master库、standby库

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#master库上v$archived_log表记录数:
SQL> select count(1) from v$archived_log;
 
 COUNT(1)
----------
  623616
 
SQL>
 
#standby库上v$archived_log表记录数:
SQL> select count(1) from v$archived_log;
 
 COUNT(1)
----------
  2226823
 
SQL>

感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!

原文链接:http://blog.csdn.net/mchdba/article/details/72231432