添加控制文件

介绍 添加控制文件一个比较简单，稳妥的方法。

[oracle@oradb01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 15 08:25:28 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--1. 查看当前控制文件路径
SQL> SELECT name FROM v$controlfile;
NAME
--------------------------------------------------------------------------------
/opt/ora11g/oradata/orcl/control01.ctl
/opt/ora11g/fast_recovery_area/orcl/control02.ctl

--2.查看spfile路径
SQL> SHOW PARAMETERS SPFILE

NAME             TYPE        VALUE
---------------- ----------- -------------------------------------------------------
spfile           string      /opt/ora11g/product/11.2.3/db_1/dbs/spfileorcl.ora
                                                
--3.根据spfile创建pfile
SQL> CREATE PFILE='/opt/ora11g/product/11.2.3/db_1/dbs/pfiletemp.ora' FROM SPFILE;
File created.

--4.编辑pfile，添加控制文件路径
[oracle@oradb01 dbs]$ vi /opt/ora11g/product/11.2.3/db_1/dbs/pfiletemp.ora
*.control_files='/opt/ora11g/oradata/orcl/control01.ctl','/opt/ora11g/fast_recovery_area/orcl/control02.ctl','/opt/ora11g/oradata/orcl/ctl/control03.ctl'

--5.拷贝旧控制文件到新添加的路径。控制文件路径和名称与pfile中指定的路径和名称保持一致。
[oracle@oradb01 ctl]$ cp /opt/ora11g/oradata/orcl/control01.ctl /opt/ora11g/oradata/orcl/ctl/control03.ctl

-- 6.关闭数据库并重命名spfile
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@oradb01 dbs]$ mv spfileorcl.ora spfileorcl.ora_bak

--7 用新建的pfile启动数据库
SQL> startup PFILE='/opt/ora11g/product/11.2.3/db_1/dbs/pfiletemp.ora';
ORACLE instance started.

Total System Global Area 3273641984 bytes
Fixed Size                  2232800 bytes
Variable Size             721423904 bytes
Database Buffers         2533359616 bytes
Redo Buffers               16625664 bytes
Database mounted.
Database opened.

--8.根据新建的pfile创建spfile
SQL> CREATE SPFILE='/opt/ora11g/product/11.2.3/db_1/dbs/spfileorcl.ora' FROM PFILE='/opt/ora11g/product/11.2.3/db_1/dbs/pfiletemp.ora';
File created.

--9.关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

--10.正常启动数据库
SQL> startup
ORACLE instance started.

Total System Global Area 3273641984 bytes
Fixed Size                  2232800 bytes
Variable Size             721423904 bytes
Database Buffers         2533359616 bytes
Redo Buffers               16625664 bytes
Database mounted.
Database opened.