Resetlogs noresetlogs, resetlogs

Resetlogs noresetlogs, resetlogs
SQL> select * from v $ version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

SQL>! Cat/etc/issue
Enterprise Linux Server release 5.5 (Carthage)
Kernel \ r on an \ m

When we re-create the control file, in which way do we resetlogs or noresetlogs?
For how to create a control file, refer to the ghost Control File Script in the previous article. It also describes the reconstruction process in detail and describes the resetlogs and noresetlogs methods, the detailed rules provide the data file for adding the TEMP tablespace.
SQL> alter database backup controlfile to trace;


The database has been changed.


SQL> select value from v $ diag_info where name = 'default Trace file ';


VALUE
--------------------------------------------------------------------------------
/U01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_22671.trc

Open the backup trace file of the Creation statement of the control file:
* ** 12:17:18. 453
* ** Session id: (125.7) 12:17:18. 454
* ** Client id :() 12:17:18. 454
* ** Service name :( SYS $ USERS) 12:17:18. 454
* Module name :( sqlplus@localhost.localdomain (TNS V1-V3) 12:17:18. 454
* ** Action name :() 12:17:18. 454
 
-- The following are current System-scope REDO Log Archival related
-- Parameters and can be encoded in the database initialization file.
--
-- LOG_ARCHIVE_DEST =''
-- LOG_ARCHIVE_DUPLEX_DEST =''
--
-- LOG_ARCHIVE_FORMAT = % t _ % s _ % r. dbf
--
-- DB_UNIQUE_NAME = "orcl3939"
--
-- LOG_ARCHIVE_CONFIG = 'send, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES = 4
-- STANDBY_FILE_MANAGEMENT = MANUAL
-- STANDBY_ARCHIVE_DEST =? /Dbs/arch
-- FAL_CLIENT =''
-- FAL_SERVER =''
--
-- LOG_ARCHIVE_DEST_1 = 'location = USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1 = 'mandatory NOREOPEN nodelay'
-- LOG_ARCHIVE_DEST_1 = 'arch noaffrem expedite noverify sync'
-- LOG_ARCHIVE_DEST_1 = 'noregister noalternate nodependency'
-- LOG_ARCHIVE_DEST_1 = 'nomax _ FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1 = 'valid _ FOR = (PRIMARY_ROLE, ONLINE_LOGFILES )'
-- LOG_ARCHIVE_DEST_STATE_1 = ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- Control file and uses it to open the database. The first set opens
-- The database with the NORESETLOGS option and shoshould be used only if
-- The current versions of all online logs are available. The second
-- Set opens the database with the RESETLOGS option and shocould be used
-- If online logs are unavailable.
-- The appropriate set of statements can be copied from the trace
-- A script file, edited as necessary, and executed when there is
-- Need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- To open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- Available.
-- After mounting the created controlfile, the following SQL
-- Statement will place the database in the appropriate
-- Protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
Create controlfile reuse database "ORCL3939" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
# MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl3939/redo01.log' SIZE 50 m blocksize 512,
GROUP 2 '/u01/app/oracle/oradata/orcl3939/redo02.log' SIZE 50 m blocksize 512,
GROUP 3 '/u01/app/oracle/oradata/orcl3939/redo03.log' SIZE 50 m blocksize 512
-- STANDBY LOGFILE
DATAFILE
'/U01/app/oracle/oradata/orcl3939/system01.dbf ',
'/U01/app/oracle/oradata/orcl3939/sysaux01.dbf ',
'/U01/app/oracle/oradata/orcl3939/undotbs01.dbf ',
'/U01/app/oracle/oradata/orcl3939/user01.dbf ',
'/U01/app/oracle/oradata/orcl3939/example01.dbf ',
'/U01/app/oracle/oradata/orcl3939/chao. dbf ',
'/U01/app/oracle/oradata/orcl3939/big_file ',
'/U01/app/oracle/oradata/orcl3939/undo_mongodbf ',
'/U01/app/oracle/oradata/orcl3939/a. dbf ',
'/U01/app/oracle/oradata/orcl3939/v. dbf'
Character set AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- Disk. Any one log file from each branch can be used
-- Re-create incarnation records.
-- Alter database register logfile '/u01/app/oracle/flash_recovery_area/ORCL3939/archivelog/2015_06_17/o1_mf_1_1 _ % u _. arc ';
-- Alter database register logfile '/u01/app/oracle/flash_recovery_area/ORCL3939/archivelog/2015_06_17/o1_mf_1_1 _ % u _. arc ';
-- Recovery is required if any of the datafiles are restored backups,
-- Or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Database can now be opened normally.
Alter database open;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
Alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl3939/temp01.dbf'
SIZE 2526 m reuse autoextend on next 655360 MAXSIZE 32767 M;
Alter tablespace TEMP_WANG add tempfile '/u01/app/oracle/oradata/orcl3939/temp_wang.dbf'
SIZE 31457280 reuse autoextend off;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- To open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- Be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- Statement will place the database in the appropriate
-- Protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
Create controlfile reuse database "ORCL3939" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
# MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl3939/redo01.log' SIZE 50 m blocksize 512,
GROUP 2 '/u01/app/oracle/oradata/orcl3939/redo02.log' SIZE 50 m blocksize 512,
GROUP 3 '/u01/app/oracle/oradata/orcl3939/redo03.log' SIZE 50 m blocksize 512
-- STANDBY LOGFILE
DATAFILE
'/U01/app/oracle/oradata/orcl3939/system01.dbf ',
'/U01/app/oracle/oradata/orcl3939/sysaux01.dbf ',
'/U01/app/oracle/oradata/orcl3939/undotbs01.dbf ',
'/U01/app/oracle/oradata/orcl3939/user01.dbf ',
'/U01/app/oracle/oradata/orcl3939/example01.dbf ',
'/U01/app/oracle/oradata/orcl3939/chao. dbf ',
'/U01/app/oracle/oradata/orcl3939/big_file ',
'/U01/app/oracle/oradata/orcl3939/undo_mongodbf ',
'/U01/app/oracle/oradata/orcl3939/a. dbf ',
'/U01/app/oracle/oradata/orcl3939/v. dbf'
Character set AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- Disk. Any one log file from each branch can be used
-- Re-create incarnation records.
-- Alter database register logfile '/u01/app/oracle/flash_recovery_area/ORCL3939/archivelog/2015_06_17/o1_mf_1_1 _ % u _. arc ';
-- Alter database register logfile '/u01/app/oracle/flash_recovery_area/ORCL3939/archivelog/2015_06_17/o1_mf_1_1 _ % u _. arc ';
-- Recovery is required if any of the datafiles are restored backups,
-- Or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
Alter database open resetlogs;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
Alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl3939/temp01.dbf'
SIZE 2526 m reuse autoextend on next 655360 MAXSIZE 32767 M;
Alter tablespace TEMP_WANG add tempfile '/u01/app/oracle/oradata/orcl3939/temp_wang.dbf'
SIZE 31457280 reuse autoextend off;
-- End of tempfile additions.
--

The red font above shows when to create logs with resetlogs and noresetlogs. If the redo log of the current database is available, you can use the noresetlogs parameter to recreate the control file. In this case, oracle can read the redo information from the LOG file and record it to the control file, because the information recorded in redo is sufficient to repeat all committed transactions, all transactions can be completely restored and the database is opened successfully. In this case, the database is restored after the power is cut off, there is no loss of data, and the redo log can be written forward.
However, oracle considers that the current log file has been lost in the resetlogs mode, which means that oracle may lose the data submitted successfully. Restoration will be an incomplete media restoration, resetlogs forcibly clears or recreates online log files.
After the restoration, you need to use the resetlogs method to open the database:
1. After Incomplete recovery is performed
2. After the backup control file is used for recovery
3. After the control file created with the resetlogs option is restored
It is a small knowledge point and I hope to share it with you!