Management of Oracle 11G R2 control files

Control files

Each database has a control file, which is a binary data file that records the physical structure of the database. Control file database An important parameter file, when writing data, the control file must be a usable state. The control file includes the following information:

1. Record the database name

2. Name and storage location of data files and redo log files

3. Database creation time

4. SN of the current log

5. Checkpoint Information

Control file Management

The following information describes the control file, understanding the control file.

Control File name

You use the parameter Control_files in the initialization parameter file to name the control file. You can use the show parameter control_fils parameter to view it.

Multi-Path control files

In the initialization parameter file, we can set up multiple control files, and the contents of multiple control files are identical. Each database has at least two control files.

The actions of multiple control files are:

n the database writes all file names named in the initialization parameters

N Database read-only the first control file shown in initialization parameters

n when the database is executing and all control files are unavailable, the instance becomes unavailable and exits

Control file naming in initialization parameters

control_files= (' C:\app\administrator\product\11.2.0\Ora_home\database\control01.ctl ', ' D:\app\Administrator\ Control\control02.ctl ')

Backup control files

Backup control files are recommended when you do the following:

1. Add, output, rename data files

2. Add, delete table space, change the State of table space

3. Add, delete redo logs and groups

Recovery control files

To create an initialization control file

When the Oracle database is created, the initialization control files are created together. The name and location of the control file is determined by the initialization parameter control_files.

Control file copying, renaming, and file location modification

Copy of the current control file (after replication, you can see the new control file, which is equivalent to the increase of the control file), rename and control the location of the file to modify the following steps

1) Close the database

2) Copy the existing file to the new storage location

3) Edit the Control_files parameter,

4) Restart the database

Create a new control file

Prerequisites for rebuilding control files

A) The control file has been compromised and cannot be restored

b) Renaming the database

The steps to rebuild the control file are:

? Create a manifest for the database's data files and redo log files, that is, before rebuilding the control file, you need to know the location of the data file and the redo log file!

? Close the database

? Backing up database data files with redo log files

? Start the DB instance, the database is not loaded or opened (Mount vs. Open)

? Creating a control file using the Create Controlfile expression

? Backup of new control files to other storage devices

? Edit control file information for initialization parameters Control_files

? Recover Database (optional)

? Open Database

Rebuilding control File Parameters

1. LogFile Location

2. DataFile Location

3. The correct character set

4. Maxlogfile

5. Maxlogmember

6. Maxloghistory

7. Maxdatafiles

8. Maxinstances

9. ARCHIVELOG

Purge Control files

Clear the control file can refer to delete control files, detailed steps into the following:

1. Close the database

2. Modify the initialization parameter control file name and delete the old control file

3. Deleting physical files

4. Restart the database

Common view of Control files

V$database;

V$controlfile;

V$controlfile_record_section;

V$parameter

Example Test 1. Understand the control file name.

With show parameter control_files, learn the name of the control file and the location of the control file, which is set in the initialization parameter control_files.

Sql> Show Parameter Control_files

NAME TYPE VALUE

--------------------------

Control_files string C:\APP\ADMINISTRATOR\PRODUCT\1

1.2.0\dbhome_1\database\control01. CTL, C:\APP\ADMINISTRATOR\

Product\11.2.0\dbhome_1\database\control02. Ctl

Sql>

2. Add/Remove control files and modify initialization parameters

*******************************************************************************

Add control file steps

1) Copy a control file

Sql> host copy C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL01. CTL C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL03. Ctl

1 file (s) copied.

2) Display control file

Sql> host dir C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\ # display file

Volume in Drive C have no label.

Volume Serial number is 349C-76EF

Directory of C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE

05/09/2017 05:22 PM <DIR>.

05/09/2017 05:22 PM <DIR>.

04/15/2017 11:45 AM <DIR> Archive

05/09/2017 03:56 PM 7,553,024 CONTROL01. Ctl

05/09/2017 03:56 PM 7,553,024 CONTROL02. Ctl

05/09/2017 03:56 PM 7,553,024 CONTROL03. Ctl

04/22/2017 12:17 PM 2,048 hc_new02.dat

04/15/2017 12:01 PM 2,048 hc_newdb.dat

04/17/2017 11:10 PM 3,161 Initnewdb.ora

04/15/2017 11:58 AM 3,058 initnewdb.ora.bak.win-db

05/09/2017 03:56 PM 1,073,750,016 NEWDB01. Dbf

12/22/2005 08:07 PM 31,744 Oradba.exe

.......

05/09/2017 03:56 PM 1,073,750,016 USERS01. Dbf

File (s) 3,857,721,035 bytes

3 Dir (s) 30,806,429,696 bytes free

3) Modify the control file name using the command alter SYSTEM command

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sql> Startup Nomount

ORACLE instance started.

Total System Global area 1286066176 bytes

Fixed Size 2254864 bytes

Variable Size 872417264 bytes

Database buffers 402653184 bytes

Redo buffers 8740864 bytes

Sql> alter system set control_files= ' C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL01. CTL ', ' C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL02. CTL ', ' C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL03. CTL ' Scope=spfile;

System altered.

Sql> ALTER DATABASE Mount;

Database altered.

sql> ALTER DATABASE open;

Database altered.

Sql>

Sql> Show Parameter Control_files

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Control_files string C:\APP\ADMINISTRATOR\PRODUCT\1

1.2.0\dbhome_1\database\control01. Ctl,c:\app\administrator\product\11.2.0\dbhome_1\database\control02. Ctl

#控制文件还没有生效

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sql> Startup

ORACLE instance started.

Total System Global area 1286066176 bytes

Fixed Size 2254864 bytes

Variable Size 872417264 bytes

Database buffers 402653184 bytes

Redo buffers 8740864 bytes

Ora-00214:control file ' C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL01. CTL ' version 1577 inconsistent with file ' C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL03. CTL '

Version 1559

#再次启动, the control file is found to be inconsistent and the database is in Nomount state

Sql> select Open_mode from V$database;

Select Open_mode from V$database

*

ERROR at line 1:

Ora-01507:database not mounted

ERROR at line 1:

Ora-01507:database not mounted

Sql> ALTER DATABASE Mount;

ALTER DATABASE Mount

*

ERROR at line 1:

Ora-00214:control file ' C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL01. CTL ' version 1577 inconsistent with file ' C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL03. CTL '

Version 1559

4) Inconsistent resolution, re-copy once

Sql> host copy C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL01. CTL C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL03. Ctl

1 file (s) copied.

Sql> ALTER DATABASE Mount;

Database altered.

Sql> ALTER DATABASE Open

2;

Database altered.

Sql> Show parameter control_files;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Control_files string C:\APP\ADMINISTRATOR\PRODUCT\1

1.2.0\dbhome_1\database\control01. CTL, C:\APP\ADMINISTRATOR\

Product\11.2.0\dbhome_1\database\control02. CTL, C:\APP\ADMIN

Istrator\product\11.2.0\dbhome_1\database\control03. Ctl

Sql>

Experience: In order to solve the control file consistency problem, you can close the database, then copy the control file, the startup nomout modify the control file, restart the database, you can see the database has been added control files.

*******************************************************************************

Delete a control file

*******************************************************************************

1) Modify initialization parameter file

Sql> alter system set control_files= ' C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL01. CTL ', ' C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL02. CTL ' Scope=spfile;

System altered.

2) Restart the database

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sql> Startup

ORACLE instance started.

Total System Global area 1286066176 bytes

Fixed Size 2254864 bytes

Variable Size 872417264 bytes

Database buffers 402653184 bytes

Redo buffers 8740864 bytes

Database mounted.

Database opened.

Sql> Show Parameter Control_files

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Control_files string C:\APP\ADMINISTRATOR\PRODUCT\1

1.2.0\dbhome_1\database\control01. CTL, C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL02. Ctl

Sql>

3) Delete physical files

Sql> host del C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTROL04. ctl/q

*******************************************************************************

3. Backup control files

1. Using Rman Backup

*******************************************************************************

C:\users\administrator>set oracle_sid=newdb

C:\users\administrator>rman Target/

Recovery manager:release 11.2.0.3.0-production on Sat 13 10:55:49 2017

Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved.

Connected to target database:newdb (dbid=3181730330)

Rman> Show All;

rman> Backup current controlfile;

Starting backup at 13-MAY-17

Allocated Channel:ora_disk_1

Channel ora_disk_1:sid=27 Device Type=disk

Channel ora_disk_1:starting full DataFile backup set

Channel ora_disk_1:specifying DataFile (s) in backup set

Including current control file in backup set

Channel ora_disk_1:starting piece 1 at 13-may-17

Channel ora_disk_1:finished piece 1 at 13-may-17

Piece handle=c:\app\administrator\flash_recovery_area\newdb\backupset\2017_05_13\o1_mf_ncnnf_tag20170513t105702 _dkdxkm1c_. BKP tag=tag20170513t105702 Comment=none

Channel Ora_disk_1:backup set complete, elapsed time:00:00:10

Finished backup at 13-MAY-17

Rman>

*******************************************************************************

Use the command ALTER DATABASE backup Controlfile to command

*******************************************************************************

Binary mode backup

sql> ALTER DATABASE backup Controlfile to ' C:\CONTROLFILE.BKP ';

Database altered.

Backup in SQL statements for easy rebuilding

sql> ALTER DATABASE backup Controlfile to trace as ' c:\01.sql ';

Database altered.

*******************************************************************************

4. Recovery control files

The operation of the recovery control file requires the database to be executed in the non-loaded state, here is a brief introduction

Sql>startup Nomount;

Sql>restore controlfile from ' C:\CONTROLFILE.BKP ';

5. View the control File view

*******************************************************************************

Sql> select * from V$controlfile;

STATUS NAME is_ block_size file_size_blks

------- ------------------------------------------------------------ --- ---------- --------------

C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTRO NO 16384 460

L01. Ctl

C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\CONTRO NO 16384 460

L02. Ctl

Sql>

Management of Oracle 11G R2 control files