Control File topic

Control file contains the following content:

• Database Name
• Time stamp of database creation
• Synchronization information (checkpoint and log sequence information) needed for recovery
• Names and locations of datafiles and redo log files
• Archiving mode of the database
• Current Log sequence number
• Recovery Manager Backup meta data (RMAN)

RMAN information is automatically updated by the Oracle system.

To open the database, datafile, redo log, and control file must be synchronized.

The precondition for enabling RMAN is that the database is in the mount or open state. If the control file is corrupted, the database cannot be mounted or open, so RMAN cannot be used. therefore, the control file is corrupted and can only be handled manually as follows.

Resetlogs

The resetlogs option is always required after Incomplete media recovery or recovery using a backup control file.

• Archives the current online redo logs (if they are accessible) and then erases the contents of the online redo logs and resets the log sequence number to 1.
• Creates the online redo log files if they do not currently exist.
• Reinitializes the control file metadata about online redo logs and redo threads.
• Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new resetlogs SCN and time stamp.

The following content is reprinted and summarized.

It is very difficult to restore and reconstruct control files..

Normally, when the current control file is lost or the current control file is inconsistent with the control file to be restored, we need to re-create a control file or use unsing
Backup controlfile to restore the control file. To put it simply, as long as the backup control file is inconsistent with the current control file to restore the database, you need to use the unsing
Backup controlfile method. Once this method is used, you must use the resetlgos option to open the database.

Therefore, it is very troublesome to control files. There are only two methods to control file recovery:

1. Use the control file that has been backed up before to restore it. The using backup controlfile

2. Recreate the control file, alter Database Backup controlfile to trace as '/u01/backup_controlfile/recreate_control.txt'

Before operating the control file, you must perform a full backup for the database.

1. Restore control file, using backup controlfile

First, set the database to the Mount status.

Ls-hltr/u01/oradata/ora10g/query the control time.

After the database is mounted, the time of the control file is updated to the latest time.

View instance status, select instance_name, status, database_status from V $ instance;

Confirm the current controlfile status: You can see that the current controlfile status is current and open_resetlogs is not allow.

In this case, if the control file is lost, we use the previous backup controlfile to restore

Recover database using backup controlfile; -- note that the backup controlfile path is not required here.

After entering the preceding command, the following prompt is displayed:

It is recommended that we use/u01/oradata/ora10g/archivelog/arch_201717_831209795.arc to restore the file. However, we actually go to this directory to check the file and do not see it. in this case, we enter cancel, and the recover ..., Then we can view the status of the control file.

You can see that the status of the control file has changed from current to backup, and open_resetlogs has changed from not allowd to required.

At this point, we try to open the database and alter database open; Be sure to use alter database open resetlogs;

In this case, we can view the prompt in the above RECOVER:

Suggested: This file does not work. We checked the directory of this file, and there is no suggest file in it.

Cancel: No. We have performed experiments and cannot open the database.

Auto: I have never tried it. I guess it's okay.

Filename: The last redo log file. You can specify the redo log file. First, query the redo log file currently executed,

We can see that redo01.log is currently being executed, and we will use this file as filename to run recover database using backup controlfile again

Note: Enter/u01/oradata/ora10g/redo01.log directly -- no quotation marks

The system displays log applied and media recovery complete.

At this time, we try open database. Alter database open resetlogs;

At this point, we can see that the database is open successfully, and the controlfile status changes to current. At this time, if you view the time of the control file on the system, it is synchronized to the current time.

Check the archivelog list. No, all the numbers are changed to 1. This is an incarnation. The following describes the specific incarnation.

Summary (reproduced)

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.

2. Recreate the control file

In some cases, you must recreate the control file, such as database name change.

First, you need to obtain the script to recreate the control file.

1. Alter Database Backup controlfile to trace as '/u01/user_controlfile/recreate_control.txt; obtain the reconstruction controlfile script.

2. Sort out the script

When we back up the control file to the trace file, we can see that it contains two reconstruction statements: resetlogs and noresetlogs.

Using noresetlogs is only available for all current online logs. That is to say, if online redo logs is unavailable, you must use resetlogs.

Using resetlogs will lead to the loss of content in online redo logs, and all backups will fail. The resetlogs mode is used only when the online redo logs is damaged.

In addition, when the database is opened

Use resetlogs after Incomplete recovery (when the entire redo stream was not applied), resetlogs will initialize the logs, reset your log sequence number, and start a new "Incarnation" of the database. resetlogs initializes the log sequence number and creates a new incarnation.

Supplement: Incarnation:

Incarnation is an embodiment. in Oracle, it can be called an entity of a database. It refers to a database scenario after the SCN is reset. A database was created at the beginning. The SCN number is 1. As the database runs, the SCN keeps increasing monotonically. Oracle describes the whole development process of the database based on the SCN. It can be said that the SCN is the timeline of the database, when the database runs normally or performs full recovery, the SCN increments monotonically until the latest SCN, so that all the data in the database changes in time order, however, if a human error occurs in the database, Incomplete recovery is required. In this case, all the data files previously backed up will be used to return the data version to the previous one, and then the log will be applied from that start point, until the moment before the human fault occurs, but at this moment, the SCN does not reach the latest SCN, but gets a previous SCN. At this moment, the human fault has not yet occurred. After completing recover .. until .. after the operation, all data files are unified through application logs, but the database cannot be opened normally yet, because the control file records the latest SCN, it is inconsistent with the data file after the application log, so you cannot directly open the database to return to the original state. You must use resetlogs to forcibly control the file, redo the log file, and the SCN of the data file, at this time, the first SCN in the newly opened database is equal to the SCN number + 1 of the last log from the application log (the resetlogs after Incomplete recovery until change 145936 information can be seen in the alarm Log File, after the database is opened, the SCN value is 145937 ). After the database resetlogs, the SCN and log serial numbers are reset. Therefore, each resetlogs generates a new incarnation, And the Incarnation information is stored in the control file, in rman, you can view entity information through list incarnation.

You can use RMAN to restore the database to an incarnation.

Now that the difference between resetlogs and noresetlogs is reached, perform the test separately:

1. Test noresetlogs:

Script: (Linux Command)

Remove blank lines and comment lines: grep-V ^-/u01/user_controlfile/re_noresetlogs.txt | grep-V ^ $>/u01/user_controlfile/re_noresetlogs.txt

This is the compiled script. You can use this script directly.

First log on to sqlplus/nolog, Conn/As sysdba

@/U01/user_controlfile/no.txt

Note: Because the database contains offline and read only tablespace, this is to be specially processed in the control file creation. From the dba_tablespaces and dba_data_files views, we can see that:

2. Test resetlogs:

Similarly, organize the Script: (Linux Command)

Remove blank lines and comment lines: grep-V ^-/u01/user_controlfile/re_noresetlogs.txt | grep-V ^ $>/u01/user_controlfile/re_noresetlogs.txt

These two scripts are basically the same, but the above 2nd-line command is changed to resetlogs. In addition, pay attention to the key points.

Previous script imperativeRecover Database

Subsequent Script CommandsRecover database using backup controlfile

@/U01/user_controlfile/yes.txt

The following content is displayed:

First, check that the arch_1_2_842279302.arc file does not exist, so you cannot use this suggest file to restore it.

If you use a redo log file, it will be restored successfully.

First, add set echo on in the first line of the script to confirm the results of each content of the script.

The control file created is displayed. At this time, there is no problem.

A problem occurs in the recover database using backup controlfile. note that this step is the basis for subsequent operations in the script. If this step fails, the subsequent operation will fail. however, currently, the control file has been created.

As before, when the preceding recover database using backup controlfile is used, a redo log file can be successfully used.

In addition, if both controlfile and online redo log file are lost, and the latest online redo log file is not archived or has no multiple channels, the database can only be completely recovered.

The recovery method is as follows:

First, back up all the current database files.

Then copy the original full backup file to the current database directory (assuming that the previous full backup control file is also lost)

In this case, you can use the create control file script to recreate the controlfile (note that the resetlogs mode is used here)

After creating a new controlfile, recover the database using backup controlfile;

At this time, you will be prompted. At this time, you will find how useful it is if you have saved all the previous archivelog, and restore it according to the prompted archivelog. in this way, the system can restore all your archivelog archive content, but the latest redo log content will be lost and users need to redo it.

Solution:

1. You have a Multiplexing Control file, which is basically equivalent to no loss.

2. the create controlfile command creates a new control file, which requires you to know all the file details about the database. (alter Database Backup controlfile to trace as '/u01/user_backup/ctl.txt', save it to this file format for us to view .)

3. Use the control file of the previous backup to recover the backup. Alter Database Backup controlfile to '/u01/user_backup/controlfile_back.bak ';

Question 1: Version inconsistency

(If the version of control01 is higher than the other two, refer to the following method :)

This is only based on the premise of multiplexing, the control file is not lost, but the control file versions between multiple channels are different, as long as you replace the lower version with the higher version,Simple

Method: CP/u01/Oracle/oradata/ora10g/control01.ctl CP/u01/Oracle/oradata/ora10g/control02.ctl

CP/u01/Oracle/oradata/ora10g/control01.ctl CP/u01/Oracle/oradata/ora10g/control03.ctl

Problem 2: Some control files are lost.

This is also based on the premise of multiplexing, a small number of reusable control files are lost, but only need to copy another control file and paste it to the corresponding path, and change it to the correct file name.Simple

Copy the existing control file to the target path and change the control file name to the correct control file name.

Problem 3: in non-archive mode, all control files are lost and the control file is rebuilt.

Consider the following:

• Identify the size and location of each log file
• Find the location of each data file
• Set the correct Character Set

The reprinted above contains details about rebuilding the control file.

The control file must be careful. The problem is very serious because the control file is dynamically changed. If it is not an automatic backup, when all the control files are lost, incomplete recovery is required, because if you use the previous control file, the content in the control file, such as SCN, must be different from the present content, therefore, we still need to set automatic backup control file and multiplexing, so we must keep the control file.

Incomplete recovery

Reasons for discovery Ming Incomplete recovery: (Cause of Incomplete recovery)

• Complete recovery fails because an archived log is lost.
• All control files are lost. (wow, if all the control files are lost, they can only be completely recovered)
• All Unarchived redo log files and a datafile are lost.
• User error
  • An important table was dropped.
  • Invalid Data was committed in a table.

Missing archive: a complete recovery operation fails because of a bad or missing archived log. recovery can only be completed to a time in the past, prior to applying the archived log.

Loss of control files: You did not mirror your control file and you do not know the structure of your database, but you have a backup of an old binary copy.

Loss of redo logs: redo logs were not mirrored and you lost a redo log before it was archived, along with a datafile. recovery cannot continue past the lost redo log.

User error: A User drops the wrong table, commits data updated with an incorrect where clause, and so forth.

Incomplete recovery of the Three Types

-Time-based recovery: Restore to a previous time point.

-Cancel-based recovery: Related to redo log sequence. See the blue example below for examples.

-Change-based recovery: restore to the previous SCN.

In addition, if you need to use the old control file for Incomplete recovery, you need to use the recovery using a backup control file. (This situation is generally used for all control files are lost, and only the control file backed up earlier is left.

Precautions for executing Incomplete recovery:

1. before Incomplete recovery, save all database files to other directories, including redo log and archive log. (prevent irreversible damage to the database during operations. If you cannot back up all data, back up the control file and archive log file at least)

2. Do not allow users to access the database before confirming that all recovery is successful.

3. Back up (and later remove) Archived logs from the system to prevent mixing archives from different database incarnations.

For example:

-A database at log seq 144 has archived logs from arch_120.rdo to arch_143.rdo.

-After parameter Ming Incomplete recovery, a new database incarnation is created, setting the Database Log seq to 0.

-Archived log arch_120.rdo to arch_143.rdo are now part of the old database incarnation.

-After 120 log switches, the archived log arch_120.rdo will be overwritten, and is backed up with all other archives (including the old archived logs arch_121.rdo to arch_143.rdo ). the archivelog of the two databases is mixed with mixing.

-At a later stage, if recovery requires arch_124.rdo, you need to make sure that the archived log restored from the backup is for the correct database incarnation, otherwise an error will result.

4. Always confirm Alert Log File before backup and recovery, because all the process information will be stored in Alert Log File.

Perform Incomplete recovery steps:

1. Shutdown and backup the database.

Example: shutdown immediate

2. Restore all datafiles. Do not restore the control file, redo logs, password file, or parameter file.

The purpose of this step is to back up all the datafile and archivelog files to copy the data files and archivelog files in the current database directory to other places.

3. Mount the database.

Startup Mount;

4. Recovery the datafiles to a point before the time of failure.

Recover database until cancel

Recover database until time '2017-03-14: 14: 22: 22'

Recover database until time '2017-03-14: 14: 22: 22 'using backup controlfile -- use the control file backed up earlier

Recover [automatic] database <option>, where:

Automatically apply archived and redo log files.

Option: until time 'yyyy-MM-DD: hh: MI: ss ';

Until cancel;

Until SCN <integer>;

Using backup control file;

5. Open the database with resetlogs.

Alter database open resetlogs;

Archive log list; -- you can see the current log sequence number. For example, the previous seq number has reached 144. You may see that the seq number is 0 after recovery, the recovery is successful.

6. Perform a closed database backup.

Shutdown immediate;

Copy all the files and back them up (full backup). Note that after the full backup, The archivelog file will no longer be used. As mentioned above, if you continue to use the archivelog file, a hybrid mixing will appear, so after the full backup, delete all the archivelog files in the specified directory of the database. If you want to delete the archivelog files before restoration, consider whether to delete them. This does not affect the current database (stored in another directory)

Note: Because the database is restored to a previous time point, the correct transaction after this time point needs to be performed again.

Example:

Cancel-based recovery example (related to log files)

Hypothesis:

It is now, employees table was dropped while someone was trying to fix Bad blocks, log files are stored on this disk at the same time, this table was deleted around am, now meeting: meeting discovery:

Redo log is not multiplexing. In other words, the lost redo log does not have an image. 1 redo log file is missing. The lost log file is not archived. (log2a. rdo is lost)

Some redo logs contain information at. 26 minutes of data loss. You can see this time through V $ logfile.

Query v $ log_history to see the missing log seq number. Here is 48.

The recovery steps are basically the same as those before, but the recovery command is recover database until cancel. note that the preceding seq 47 content has been fully restored (recover the database until log seq 48). After the restoration, you can view the employees, the content can be found.

Using backup control file recovery:

Hypothesis:

It is now, tablespace containing the Employees table has been dropped. The error occurred around 11: 45, employee Employee records were updated this morning, but not since. And a backup is made every night.

Event Sequence:

1. Someone, drop tablespace emp_ts including contents;

2. immediately tell all users to log out. You can set the database to restircted, restrict user logon, and alter system enable restricted session;

3. After analysis, you found the control file backed up last night. Because the current control file will be overwritten, you must first confirm the information in the current control file.

Select * from V $ log;

Select tablespace_name, file_name from dba_data_files where tablespace_name = 'emp_ts'

Through the above confirmation, We can find

Emp_ts tablespace has one datafile.

The current log sequence number is 61.

You confirm that the tablespace was dropped at 11:44:54 (the operation time can be confirmed through the time stamp in alert_log file)

Datafile number 4 is offline.

4. shutdown the database, backup control files. Then, move the control file and datafile that previously backed up contain the tablespace information to the directory of the current database file.

5. Make sure that all offline datafiles are online. Because any offline files may be unrecoverable after recovery:

Select * from V $ recover_file; if you see offline, you can view alter database datafile 4 online;

6. Recover database until time '2017-03-09: 11: 44: 00' using backup controlfile;

7. Alter database open resetlogs;

8. Check whether the employees table exists.

9. Full backup

10. Notify the user to redo the operations after.

Loss of current redo log files lost the currently used redo log file

1. If you want to open the database, the current log file is lost.

2. You need to rediscover the logs. First, check the log sequence number, select * from V $ log. If the current log seq No. is 61

3. Copy the original backup datafile to the specified path of the database. Then, recover until cancel. This operation will restore the database to log seq to 60.

4. Alter database open resetlogs

5. The database shocould now be operational, because any missing log files will be re-created.

NOTE: If log files need to be re-created on another disk due to media failure, use the alter database drop log group and alter database add log group commands to create the log files manually.

6. Full backup

Control File topic