How to reset the log sequence number in an Oracle database

[oracle@rman-recovery-test archivelog]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 26 16:47:57 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Total System Global Area 2097152000 bytes
Fixed Size                  2097592 bytes
Variable Size            1979715144 bytes
Database Buffers          100663296 bytes
Redo Buffers               14675968 bytes
Database mounted.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>


SQL> select open_mode from v$database;

delete archive log file

SQL> alter system archive log current;

SQL> /

SQL> /

SQL> archvie log list;


手動清除v$ARCHIVED_LOG中不管是DELETED還是available 記錄的方法，注意不要在你的產品資料庫上這樣做：

SQL> select count(*) from v$archived_log;
COUNT(*)
———
553

SQL> execute sys.dbms_backup_restore.resetCfileSection(11);
PL/SQL procedure successfully completed.
SQL> select count(*) from v$archived_log;
COUNT(*)
———
0

========================================================================
PROCEDURE resetCfileSection(record_type IN binary_integer );
— This procedure attempts to reset the circular controlfile section.
—
— Input parameters:
— record_type
— The circular record type whose controlfile section is to be reset.

execute sys.dbms_backup_restore.resetCfileSection(11);  ==> 清理v$ARCHIVED_LOG對應的記錄
execute sys.dbms_backup_restore.resetCfileSection(28);  ==>清理v$rman_status對應的記錄

以上是2個例子，具體的section_id可以這樣獲得：

SQL>  select rownum-1, type from v$controlfile_record_section

ROWNUM-1 TYPE
——— —————————
0 DATABASE
1 CKPT PROGRESS
2 REDO THREAD
3 REDO LOG
4 DATAFILE
5 FILENAME
6 TABLESPACE
7 TEMPORARY FILENAME
8 RMAN CONFIGURATION
9 LOG HISTORY
10 OFFLINE RANGE
11 ARCHIVED LOG
12 BACKUP SET
13 BACKUP PIECE
14 BACKUP DATAFILE
15 BACKUP REDOLOG
16 DATAFILE COPY
17 BACKUP CORRUPTION
18 COPY CORRUPTION
19 DELETED OBJECT
20 PROXY COPY
21 BACKUP SPFILE
22 DATABASE INCARNATION
23 FLASHBACK LOG
24 RECOVERY DESTINATION
25 INSTANCE SPACE RESERVATION
26 REMOVABLE RECOVERY FILES
27 RMAN STATUS
28 THREAD INSTANCE NAME MAPPING
29 MTTR
30 DATAFILE HISTORY
31 STANDBY DATABASE MATRIX
32 GUARANTEED RESTORE POINT
33 RESTORE POINT