How can I avoid resetlogs from opening the database after the control file is restored?

How can I avoid resetlogs from opening the database after the control file is restored?

After the control file is restored, the database is opened using resetlogs, and the sequence of online logs is reset.

SYS @ practice> select group #, sequence #, archived, status, first_change #, next_change # from v $ log;

GROUP # SEQUENCE # arc status FIRST_CHANGE # NEXT_CHANGE #
----------------------------------------------------------------
1 1 no current 1014415 2.8147E + 14
2 0 yes unused 0 0
3 0 yes unused 0 0

If resetlog is not truncated, the serial number of the current log should be 7, 8, 9
SYS @ practice> select recid, sequence #, first_change #, next_change # from v $ log_history;

Recid sequence # FIRST_CHANGE # NEXT_CHANGE #
---------------------------------------------
1 925702 955284
2 2 955284 955847
3 3 955847 971151
4 971151 987800
5 987800 997957
6 6 997957 1010981
7 1 1010981 1013463
8 2 1013463 1013471
9 3 1013471 1014415

If you have recovered the control file, you cannot open the database without using resetlogs.

We can open the database by manually creating control files to keep the sequence # continuity of online logs. The following experiment demonstrates the specific operation process.

Before the experiment, manually switch the logs three times so that the serial number of the online logs changes. After the restoration is completed, it is used as a reference.
SYS @ practice> alter system archive log current;

SYS @ practice> alter system archive log current;

SYS @ practice> alter system archive log current;

After switching, the current connection log status is as follows:
SYS @ practice> select group #, sequence #, archived, status, first_change #, next_change # from v $ log;

GROUP # SEQUENCE # arc status FIRST_CHANGE # NEXT_CHANGE #
----------------------------------------------------------------
1 4 no current 1030130 2.8147E + 14
2 2 yesinactive 1030109 1030117
3 3 yes active 1030117 1030130

1. Restore the control file
RMAN> startup force nomount;

Oracle instance started

Total System Global Area 580395008 bytes

Fixed Size 2255392 bytes
Variable Size 402654688 bytes
Database Buffers 171966464 bytes
Redo Buffers 3518464 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 06--14 14
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 19 device type = DISK

Recovery area destination:/u01/fast_recovery_area
Database name (or database unique name) used for search: PRACTICE
Channel ORA_DISK_1: AUTOBACKUP/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251717_b35s05gm _. bkp found in the recovery area
Channel ORA_DISK_1: looking for AUTOBACKUP on day: 20141006
Channel ORA_DISK_1: restoring control file from AUTOBACKUP/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_8602517_b35s05gm _. bkp
Channel ORA_DISK_1: control file restore from AUTOBACKUP complete
Output file name =/u01/oradata/practice/control01.ctl
Output file name =/u01/fast_recovery_area/practice/control02.ctl
Finished restore at 06-OCT-14

2. Start the database to the mount status.
RMAN> mount database;

Database mounted
Released channel: ORA_DISK_1

3. Restore the database
RMAN> recover database;

Starting recover at 06--14 14
Starting implicit crosscheck backup at 06--14 14
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 20 device type = DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 06-OCT-14

Starting implicit crosscheck copy at 06--14 14
Using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-OCT-14

Searching for all files in the recovery area
Cataloging files...
Cataloging done

List of Cataloged Files
======================================
File Name:/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr _. arc
File Name:/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83 _. arc
File Name:/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b362l34q _. arc
File Name:/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_8602517_b35s05gm _. bkp
File Name:/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251162_b35rgt84 _. bkp
File Name:/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251158_b35rgpms _. bkp
File Name:/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55 _. bkp

Using channel ORA_DISK_1

Starting media recovery

Archived log for thread 1 with sequence 1 is already on disk as file/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr _. arc
Archived log for thread 1 with sequence 2 is already on disk as file/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b362l34q _. arc
Archived log for thread 1 with sequence 3 is already on disk as file/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83 _. arc
Archived log for thread 1 with sequence 4 is already on disk as file/u01/oradata/practice/redo01.log
Archived log file name =/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr _. arc thread = 1 sequence = 1
Archived log file name =/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b362l34q _. arc thread = 1 sequence = 2
Archived log file name =/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83 _. arc thread = 1 sequence = 3
Archived log file name =/u01/oradata/practice/redo01.log thread = 1 sequence = 4
Media recovery complete, elapsed time: 00:00:00
Finished recover at 06-OCT-14

4. Back up the control file to the trace
SYS @ practice> select open_mode from v $ database;

OPEN_MODE
--------------------
MOUNTED

SYS @ practice> alter database backup controlfile to trace;

SYS @ practice> select value from v $ diag_info where name = 'default Trace file ';

VALUE
--------------------------------------------------------------------------------
/U01/diag/rdbms/practice/trace/practice_ora_1185.trc

5. Restart the instance to the nomount status.
RMAN> startup force nomount;

Oracle instance started

Total System Global Area 580395008 bytes

Fixed Size 2255392 bytes
Variable Size 402654688 bytes
Database Buffers 171966464 bytes
Redo Buffers 3518464 bytes

6. Run the rebuild control file command to enter the mount status.
Vi/home/oracle/create_controlfile. SQL

STARTUP NOMOUNT
Create controlfile reuse database "PRACTICE" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
# MAXLOGHISTORY 292
LOGFILE
GROUP 1'/u01/oradata/practice/redo01.log 'SIZE 50 m blocksize 512,
GROUP 2'/u01/oradata/practice/redo02.log 'SIZE 50 m blocksize 512,
GROUP 3 '/u01/oradata/practice/redo03.log' SIZE 50 m blocksize 512
DATAFILE
'/U01/oradata/practice/system01.dbf ',
'/U01/oradata/practice/sysaux01.dbf ',
'/U01/oradata/practice/undotbs01.dbf ',
'/U01/oradata/practice/users01.dbf ',
'/U01/oradata/practice/example01.dbf'
Character set AL32UTF8
;
Variable recno number;
EXECUTE: RECNO: = SYS. DBMS_BACKUP_RESTORE.SETCONFIG ('controlfile AUTOBACKUP ', 'on ');
RECOVER DATABASE
Alter system archive log all;
Alter database open;
Alter tablespace temp add tempfile '/u01/oradata/practice/temp01.dbf' REUSE;

Run the create Control File Script in sqlplus. The database starts to open and adds temporary tablespace files.

SYS @ practice> @ create_controlfile. SQL

Control file created.

PL/SQL procedure successfully completed.

Media recovery complete.

System altered.

Database altered.

Tablespace altered.

At this time, the database is in the read write state, that is, the open state.
SYS @ practice> select open_mode from v $ database;

OPEN_MODE
--------------------
READ WRITE

Check the serial number of the current log, which is not reset.
SYS @ practice> select group #, sequence #, archived, status, first_change #, next_change # from v $ log;

GROUP # SEQUENCE # arc status FIRST_CHANGE # NEXT_CHANGE #
----------------------------------------------------------------
4 yes inactive 1030130 1050296
2 5 no current 1050296 2.8147E + 14
3 3 yesinactive 1030117 1030130

The temporary table data file is also created.
SYS @ practice> select name from v $ tempfile;

NAME
--------------------------------------------------------------------------------
/U01/oradata/practice/temp01.dbf

7. Re-Identify the backup information and configuration information of the Control File

At this time, the control file does not have the information of the control file backed up before.
RMAN> list backup of controlfile;

Using target database control file instead of recovery catalog
Specification does not match any backup in the repository

Re-register backup information from the flash recovery area
RMAN> catalog db_recovery_file_dest;

Register backup information from the address that has performed full database backup
RMAN> catalog start with '/home/oracle /';

List the backup control files again, and you can see that all registration is successful.
RMAN> list backup of controlfile;

List of Backup Sets
==============================

BS Key Type LV Size Device Type Elapsed Time Completion Time
-------------------------------------------------------------
1 Full 9.67 m disk 00:00:00 06--14 14
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20141006T175610
Piece Name:/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860262970_b363zt2x _. bkp
Control File Included: Ckp SCN: 1051644 Ckp time: 06--14 14

BS Key Type LV Size Device Type Elapsed Time Completion Time
-------------------------------------------------------------
3 Full 9.33 m disk 00:00:00 06--14 14
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20141006T132827
Piece Name:/u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39 _. bkp
Control File Included: Ckp SCN: 1005439 Ckp time: 06--14 14

BS Key Type LV Size Device Type Elapsed Time Completion Time
-------------------------------------------------------------
7 Full 9.33 m disk 00:00:00 06-OCT-14
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20141006T143909
Piece Name:/home/oracle/full_PRACTICE_9_20141006_1.bak
Control File Included: Ckp SCN: 1013438 Ckp time: 06--14 14

This experiment ends now.