Moving and adding an instance of a control file

The control file is a very important file in the whole database, must ensure the security of the control file, in principle, multiple control files should be placed on separate disks. However, it is very dangerous to have all the control files on the same disk in the same directory as the default installation of Oracle, which is very risky and must move multiple control files on the existing Oracle system to a separate physical disk for secure backup. The steps are as follows:

First, using the data dictionary to obtain the name and absolute path of the existing control file

Select Name,status from V$controlfile;

If you are using SPFile, because you cannot use a text editor to change the Control_files parameter in the SPFile, you must use the ALTER SYSTEM command in SQL to modify the SPFile so that it can point to the newly added control file.

To determine whether the current Oracle system is using Pfile or spfile can use the command;

Show parameter pfile;

Third, use the ALTER system command to modify the Control_files parameter in SPFile to add the newly moved control file path to the Control_files parameter when determining the use of SPFile

Alter system set control_files=

' D:\app\Administrator\oradata\orclyg\control1\CONTROL01. CTL ',

' D:\app\Administrator\oradata\orclyg\control2\CONTROL02. CTL ',

' D:\app\Administrator\oradata\orclyg\control3\CONTROL03. CTL ' Scope=spfile;

Four, close the database shutdown, copy the original control file to the specified newly added directory path.

Note: You must close the database before you can copy the control file because the control file may be modified by the Oracle server at any time without shutting down the database, and if you do not close the database at this point, the corresponding control file is copied to the specified directory. Then in the subsequent user process changes to the database structure is recorded in the original control file, so that if the restart of the database load the new control file will cause the original modification is overwritten, resulting in data loss and incomplete recovery. Therefore, you must close the database after you copy the control file to the specified directory.

V. Start the database startup view the path to the control file

Select Name,status from V$controlfile;

Displays the absolute path of the currently loaded control file.

Six, because the control file is an extremely important file, in addition to the above mentioned a plurality of control files exist on several different disks, after the structure of the database changes should be immediately back up the control files.

The command to back up the control file is as follows:

ALTER DATABASE backup Controlfile to ' D:\backup\control.bak ';

Note: When you create a backup file, the path must already exist ' D:\backup directory must be found, otherwise it cannot be created successfully.

In addition, the command that creates the control file is backed up to a trace file that contains all the SQL statements needed to rebuild the control file

ALTER DATABASE backup Controlfile to trace;

The trace file finds the storage directory of the user trace file in the directory of user files by following sql:

Select Name,value from v$parameter where name like '%_dest ';

Where the query result set parameter Background_dump_dest represents the alarm log file storage path

The User_dump_dest parameter represents the path to the user process trace file storage.

Sort the user process according to the time to trace the file under the directory, the last one is just the new control file tracking file, copy the file to another directory, and rename it to a meaningful name.