Home » Oracle » 8, control files, log files

8, control files, log files

8, control files, log files, there is a friend in need can refer to.

Control file

The control file is a small binary file, record the basic information about the structure of the database, the database at home, the Oracle instance will read the contents of the file control. Control file is missing or damaged, the database will not be able to load and it is difficult to restore.

The main contents of the control file are as follows:

1, the database name database, name, and identifies SID

2, database creation timestamp.

3, tablespace name

4, the data file, the name and location of the redo log file,

5, the current redo log file serial number

6, check point information

7, the start and end of undo segment,

8 redo log archive information

9, backup information

Permanent parameter:

Maxinstances: is used to specify the maximum number of routines to access the database at the same time (a database can be loaded and opened by multiple instances).

Maxdatafiles: the maximum number of data files used to specify the Oracle database..

Maxlogfiles: is used to specify the maximum number of log groups for the Oracle database.

Maxlogmembers: is used to specify the maximum number of log members per log group.

Maxloghistory: is used to specify the maximum number of log histories that can be recorded by the control file.

When using the RMAN backup, the RMAN records the backup information in the control file.

1.1 compound control file

Because of the importance of the control file, we need to control the composite file, is actually save multiple control files (respectively for different types of hard disk). When one of the files (hard disk) lost or damaged. If you modify the control file initialization parameter control_files, remove the control file is damaged, and then restart.

Control_files modification (instance using SPFILE startup)

Alter, system, set, control_files=', full path + name',


'full path + name',

Full path + name'


Close the database and copy the original control file into multiple copies (in accordance with the contents of the control_files settings) and put them in different locations.

Shutdown immediate

Copy files...

Then restart.


Select, name, from, v$controlfile -- see all the control files.

For the pfile file, his control_files parameter is modified in this pfile file, and opens the pfile file editor:

Control_files= (path + name),

"Path + name",

Path + name)

OK. The rest of the process is consistent with SPFILE,.

Note the file name and extension name,.

1.2 create control file

Creating a control file needs to start the state of not loading the database..

Startup nomount

Create controlfile

Reuse database, "orcl",


Group 1,'d:appjangleoradataorclREDO01.LOG',

Group 2... REDO02.LOG',

Group 3... REDO03.LOG




Maxlogfiles 50

Maxlogmembers 3

Maxinstances 6

Maxdatafiles 200



From this, the control file has been created.

Noresetlogs: is used to specify the use of the original redo log. If you do not want to use the redo log, you specify reserlogs. (which feels somewhat reversed, to be validated).

Logfile: specifies the original group database redo log, log size and members of the.
Datafile: specifies the original data file for the database (data files correspond to the table space)

Modify the initialization parameter control_files
after you have created it
Alter system set control_files=



Alter database open; - and then open the database.

1.3 backup and restore control file

Alter, database, backup, controlfile, to,'d:backupfilecontrol_16-04-14.bkp'-- this backup is backed up as binary.

Alter, database, backup, controlfile, to, trace - - backup query text file, sql.
that can be used to create control files

Two, redo log files (Redo, Log, File)

Oracle, the changes made to the database to redo records stored in the redo log buffer, when a transaction commits, the LGWR process will be in the cache associated with the transaction redo records all the redo log file, then the transaction is considered successfully submitted br>.
Oracle database requires at least 2 to log iwenj, when the first redo log file is filled after LGWR to begin writing a redo log file; when all log files are written back to write the first, so the cycle of.

Select * from v$log - see redo log group information.

Select * from v$logfile - - view log file information.

Alter database add logfile group 4 ('d:... Redo04.log','e:... Redo04.log') size 10m; - New redo log group, specified as fourth groups, which contains 2 Composite log files (numbering should be made up in sequence)

Alter, database, add, logfile, member,'e:...... to group 1; add member.
to the log group in it
Alter, database, drop, logfile, member,'e:,...... - delete the member.
in the log group
Alter database drop logfile group 4; - delete redo log group.

Alter database clear logfile group 3; - empty 3 redo log group.

For security reasons, planning the redo log, should be the same on different content members can distribute to different disks, all members to prevent the disk damage caused loss. So we need to increase the disk, mobile members: < br>
1, shutdown immediate

2, move the log member to the new disk (new location) at the operating system layer.

3, startup, mount;

4, alter, database, rename, file,

'd:... ',



'e:... ',

'e:... ';

5, alter, database, open;

6, backup the control file (this is a change that should be performed every time for the database).

Three, manage archived logs

The redo log, it is the write cycle, that is to say, when a cycle ends, the first log it will be covered by writing new content before the redo log is unable to view. The concept of archive is, each redo logs are retained, which can check to modify the whole course for data recovery. This is very advantageous for.

NOARCHIVELOG non archiving mode:

1, when the checkpoint is completed, LGWR sequentially covers the redo log file.

2, if the redo log database after the backup was covered, then a data file is damaged can return to full backup point in the past (+ database backup backup to the redo log file database content = current) Note: the redo log is a set of modified.

Archivelog Archive Mode:

1, if disk media corruption occurs, you can use database backup + archive redo logs to restore the transactions that have been committed to ensure that no data loss occurs,.

2, the use of archived log files, you can use the database to open the state created by backup files for database recovery.

3, if you build a backup database for the current database, you can ensure consistency between the source and backup databases by consistently applying archived redo logs to the backup database.

3.1 archiving / non archiving mode switching

Select log_mode from v$database -- see the current archive mode.

Shutdown immediate;

Startup mount - modify.
when loading does not open the database
Alter database archivelog; - switch database to archive mode.

Alter database open; - Open Database

Archive log list; see if it is already in archive mode

Alter system set log_archive_max_processes=5; - sets the number of ARCn processes and defaults to 2.

Set the target file by setting the log_archive_dest_n parameter (1<=n<=10), the default location for use_db_recovery_file_dest, namely the directory database flash_recovery_area flashback directory. By setting the log_archive_dest_1 can modify the default directory.

Alter, system, set, log_archive_dest_1='location=d:...... - - sets the local system directory

Alter system set log_archive) dest_2='service=DBY1'--DBY1 is a remote standby database server named

Select *, from, v$archive_dest -- view all archived object information.

The P278 configuration archive format, configuration archive, file location, and other detailed configuration content.