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 NO11200 rows selected.SQL> |
然后查看下当前的归档记录
|
1
2
3
4
5
6
7
8
|
SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 164Next log sequence to archive 166Current log sequence 166SQL> |
看到归档记录才是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 selectedSQL> |
再次测试，可以查看到日志记录变化了，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 NOSQL> 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 selectedSQL> |
扩展话题，单机实例可以用上，述办法操作，那么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)---------- 623616SQL> #standby库上v$archived_log表记录数：SQL> select count(1) from v$archived_log; COUNT(1)---------- 2226823SQL> |
感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！
原文链接：http://blog.csdn.net/mchdba/article/details/72231432
