oracle重新创建datafile,oracle-添加 controlfile 以及重建spfile

添加controlfile,重新创建spfile

1.编辑init.ora文件

指定需要添加的控制文件的路径

control_files=("/oracle/product/10.1.0/oradata/orcl/control01.ctl",

"/oracle/product/10.1.0/oradata/orcl/control02.ctl",

"/oracle/product/10.1.0/oradata/orcl/control03.ctl",

"/oracle/product/10.1.0/oradata/orcl/control04.ctl")

--添加control04.ctl的路径

2.创建control04.ctl文件

/oracle/product/10.1.0/admin/orcl/init.ora --添加controlfile

SQL> shutdown--切换状态

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> cp /oracle/product/10.1.0/oradata/orcl/control01.ctl /oracle/product/10.1.0/oradata/orcl/control04.ctl

已复制         1 个文件。

3.使用pflie文件引导oracle启动

--系统首选启动文件/oracle/product/10.1.0/Db_1/database/spfileorcl.ora

--管理员无法直接更改spfileorcl.ora文件，只能重新创建

SQL> startup pfile='/oracle/product/10.1.0/admin/orcl/pfile/init.ora.7112011153835';

ORACLE instance started.

Total System Global Area  171966464 bytes

Fixed Size                   787988 bytes

Variable Size             145488364 bytes

Database Buffers           25165824 bytes

Redo Buffers                 524288 bytes

Database mounted.

Database opened.

--controlfile已经添加成功

SQL> select name from v$controlfile;

NAME

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

/oracle/product/10.1.0/oradata/orcl/CONTROL01.CTL

/oracle/product/10.1.0/oradata/orcl/CONTROL02.CTL

/oracle/product/10.1.0/oradata/orcl/CONTROL03.CTL

/oracle/product/10.1.0/oradata/orcl/CONTROL04.CTL

SQL>

4.重新创建spfile

--(1)oracle使用pfile登录 open状态创建spfile

SQL> create spfile from pfile='/oracle/product/10.1.0/admin/orcl/pfile/init.ora.7112011153835';

File created.

--(2)nomount 状态无法创建spflie

--oracle使用pfile登录nomount状态可以创建spfile

SQL> startup nomount

ORACLE instance started.

Total System Global Area  171966464 bytes

Fixed Size                   787988 bytes

Variable Size             145488364 bytes

Database Buffers           25165824 bytes

Redo Buffers                 524288 bytes

SQL> create spfile from pfile='/oracle/product/10.1.0/admin/orcl/pfile/init.ora.9112013153465';

create spfile from pfile='/oracle/product/10.1.0/admin/orcl/pfile/init.ora.9112013153465'

*

ERROR at line 1:

ORA-32002: cannot create SPFILE already being used by the instance

SQL>

--(3)oracle关闭状态创建spfile

SQL> shutdown

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> create spfile from pfile='/oracle/product/10.1.0/admin/orcl/pfile/init.ora.9112013153465';

File created.

SQL>