Understanding using backup controlfile

Using backup controlfile is usually used to restore the current control file because the current control file is lost and the original backup control file is changed compared with the current situation. Using backup controlfile
After the recover method is used, common recover database commands cannot be used again, and you must use the resetlogs method to open the database. The following is a detailed demonstration description.

 

1. demonstrate changes related to using backup controlfile

--> View the time information of the database sybo2sz control file SYS @ sybo2sz> Ho LS-hltr/u02/database/sybo2sz/controlf/total 29m-rw-r ----- 1 Oracle oinstall 9.7 m 2012-09-10 cntl3sybo2sz. CTL-RW-r ----- 1 Oracle oinstall 9.7 m 2012-09-10 11: 59 cntl2sybo2sz. CTL-RW-r ----- 1 Oracle oinstall 9.7 m 2012-09-10 11: 59 cntl1sybo2sz. CTL --> View System Time sys @ sybo2sz> Ho datemon Sep 10 12:00:09 CST 2012 --> View the status of database sybo2sz, at this time, the database is shut down. sys @ sybo2sz> Ho PS-Ef | grep pmon_sybo2szoracle 440 32067 442 0 00:00:00 pts/4/bin/bash-c ps-Ef | grep pmon_sybo2szoracle 440 0 01 PTS/4 00:00:00 grep pmon_sybo2szsys @ sybo2sz> startup Mount; oracle instance started. total system global area 599785472 bytesfixed size 2074568 bytesvariable size 381683768 bytesdatabase buffers 209715200 bytesredo buffers 6311936 bytesdatabase mounted. --> after the database is mounted, sys @ sybo2sz> Ho LS-hltr/u02/database/sybo2sz/controlf/total 29m-rw-r ----- 1 Oracle oinstall 9.7 m cntl3sybo2sz. CTL-RW-r ----- 1 Oracle oinstall 9.7 m cntl2sybo2sz. CTL-RW-r ----- 1 Oracle oinstall 9.7 m cntl1sybo2sz. ctlsys @ sybo2sz> select instance_name, status, database_status from V $ instance: 23sys @ sybo2sz> Ho LS-hltr/u02/database/sybo2sz/oradata/sys *-RW-r ----- 1 Oracle oinstall 501 m 2012-09-10/u02/database/sybo2sz/oradata/ syssybo2sz. DBF-RW-r ----- 1 Oracle oinstall 301 m 2012-09-10 :23/u02/database/sybo2sz/oradata/sysauxsybo2sz. dbfsys @ sybo2sz> alter session set nls_date_format = 'yyyymmdd hh24: MI: ss'; --> author: Robinson Cheng --> blog: http://blog.csdn.net/robinson_0612 --> you can also see that controlfile_type is current, open_resetlogs is not allowedsys @ sybo2sz> select controlfile_type, controlfile_sequence #, controlfile_change #, controlfile_time, open_resetlogs 2 from V $ database; control controlfile_sequence # controlfile_change # controlfile_time open_resetl ------- begin ------------------- ----------------- ------------- current 6012 1151639 20120910 04:30:14 not allowed --> use the recover command with using backup controlfile, the specify log prompt --> then open another session to connect to the instance, the following SQL prompt starting with idle is another sessionsys @ sybo2sz> recover database using backup controlfile; ORA-00279: change 1160803 generated at 09/10/2012 11:23:59 needed for thread 1ora-00289: Suggestion:/u02/database/sybo2sz/archive/arch_793474012_1_3.arcORA-00280: Change 1160803 for thread 1 is in sequence #3 specify log: {<RET> = suggested | filename | auto | cancel} cancelmedia recovery canceled. --> session2 operation idle> Conn/As sysdbaconnected. idle> alter session set nls_date_format = 'yyyymmdd hh24: MI: ss'; Session altered. --> after the following query uses recover database using backup controlfile, controlfile_type is backup --> and open_resetlogs is required. The corresponding sequence and change # All change idle> select controlfile_type, controlfile_sequence #, controlfile_change #, controlfile_time, open_resetlogs 2 from V $ database; control controlfile_sequence # controlfile_change # controlfile_time open_resetl ------- begin --------------------- ----------------- backup 6014 1160803 20120910 11:23:59 required --> after the preceding query is complete, enter cancel, message media recovery canceled --> try to open the database in open mode, prompt must use resetlogs or noresetlogs option sys @ sybo2sz> alter database open; Alter database open * error at line 1: ORA-01589: must use resetlogs or noresetlogs option for database open --> use the resetlogs option to open the database. Note that if you use the noresetlogs option, the above prompt --> prompts file1 to restore media sys @ sybo2sz> alter database open resetlogs; Alter database open resetlogs * error at line 1: ORA-01113: File 1 needs media recoveryORA-01110: data File 1: '/u02/database/sybo2sz/oradata/syssybo2sz. dbf' --> View the arch_791_4012_1_3.arc file according to the recover prompt. At this time, the file does not exist sys @ sybo2sz> Ho LS-hltr/u02/database/sybo2sz/archive/arch_791_4012_1_3: /u02/database/sybo2sz/archive/arch_791_4012_1_3.arc: no such file or directory --> try to restore again, and the system still prompts arch_791_4012_1_3.arc to archive logs, view the status information sys @ sybo2sz> recover database using backup controlfile in session2; ORA-00279: Change 1160803 generated at 11:23:59 needed for thread 1ora-00289: Suggestion: /u02/database/sybo2sz/archive/arch_793474012_1_3.arcORA-00280: Change 1160803 for thread 1 is in sequence #3 --> copy the online Log Path of group 3 to the specify log for full recovery specify log: {<RET> = suggested | filename | auto | cancel}/u02/database/sybo2sz/redolog/log3asybo2sz. loglog applied. media recovery complete. --> session2 --> we can see that the sequence of the control file # has changed several times idle> select controlfile_type, controlfile_sequence #, controlfile_change #, controlfile_time, open_resetlogs 2 from V $ database; control controls # controlfile_change # controlfile_time certificate ------- specified parameter certificate ----------- backup 6014 1160803 20120910 11:23:59 requiredidle>/control controlfile_sequence # controlfile_change # controlfile_time certificate ------- specified parameter certificate ----------- backup 6015 1160803 20120910 11:23:59 required --> view the current online log information idle> select * from V $ logfile; group # status type member is _ ---------- ------- ----------------------------------------------------- --- 3 online/u02/database/sybo2sz/redolog/log3asybo2sz. log No 3 online/u02/database/sybo2sz/redolog/log3bsybo2sz. log No 4 online/u02/database/sybo2sz/redolog/log4asybo2sz. log No 4 online/u02/database/sybo2sz/redolog/log4bsybo2sz. log no --> at this time, log group 3 is in the current State. Therefore, copy the online Log Path of group 3 to the specify log for full recovery idle> select * from V $ log; group # thread # sequence # bytes members arc status first_change # first_time ------------ ---------- --- ---------------- ------------- limit 4 1 2 20971520 2 Yes inactive 1124329 20120909 09:00:49 3 3 3 20971520 2 no current 1150957 20120910 04:00:06 --> after the full recovery is successful, using open method still failed sys @ sybo2sz> alter database open; Alter database open * error at line 1: ORA-01589: must use resetlogs or noresetlogs option for database open --> use the open resetlogs method to successfully open the Database SYS @ sybo2sz> alter database open resetlogs; database altered. --> Session 2 --> in session 2, controlfile_type has been set to current, and open_resetlogs is also set to not allowedidle> select controlfile_type, controlfile_sequence #, controlfile_change #, controlfile_time, open_resetlogs 2 from V $ database; Control controlfile_sequence # controlfile_change # controlfile_time open_resetl ------- hour ----------------------- current 6073 1160878 20120910 12:09:58 not allowed --> view the time information of the data file, at this time, sys @ sybo2sz> Ho LS-hltr/u02/database/sybo2sz/oradata/sys *-RW-r ----- 1 Oracle oinstall 501 m/u02 /database/sybo2sz/oradata/syssybo2sz. DBF-RW-r ----- 1 Oracle oinstall 301 m 2012-09-10 12:09/u02/database/sybo2sz/oradata/sysauxsybo2sz. dbfsys @ sybo2sz> Ho datemon Sep 10 12:10:42 CST 2012 --> the corresponding new incarnation has generated the Sys @ sybo2sz> archive log list; database Log mode archive modeautomatic archival enabledarchive destination/u02/database/sybo2sz/archive/oldest online log sequence 1 next log sequence to archive 1 Current Log sequence 1

Ii. Summary
1. Using backup controlfile: the backup control file used to restore the backup is inconsistent with the current control file.
2. Once the using backup controlfile method is used, the type of the control file will be transferred from current to backup, and open_resetlogs is required
3. Once the using backup controlfile method is used, the subsequent use of recover database will become invalid.
4. You must use the resetlogs method to open the database, even if we perform full recovery.
5. Understand the updated time status in the demo. It is actually the instance startup process, namely:
Nomount: Start the background process based on pfile or spfile and assign the SGA
Mount: Open the control file, check the consistency of the Control File status, and associate the database with the instance
Open: the entire database is put in the open state after checking the data file logs recorded in the control file one by one.

 

Iii. More references

For more information about user-managed backup and recovery, see

Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management (describes media recovery and processing in detail)

System tablespace management and Backup Recovery

Sysaux tablespace management and recovery

Oracle backup control file recovery (unsing backup controlfile)

 

For information on RMAN backup recovery and management, see

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use RMAN catalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

RMAN backup path confusion (when using plus archivelog)

 

For the Oracle architecture, see

Oracle tablespace and data files

Oracle Password File

Oracle parameter file

Oracle online redo log file)

Oracle Control File)

Oracle archiving logs

Oracle rollback and undo)

Oracle database instance startup and Shutdown Process

Automated Management of Oracle 10g SGA

Oracle instances and Oracle databases (Oracle Architecture)