控制文件用于记录和维护数据库。当恢复数据库时,服务器进程和后台进程需要从控制文件中读取各种备份相关的信息。如果控制文件损坏,则会导致这些备份信息的丢失。尽管使用多元化控制文件可以防止控制文件损坏,但因为控制文件的重要性,应该定期备份控制文件。当数据库配置发生改变时,一定要备份控制文件。涉及到数据库配置改变的命令:
alter database [add|drop] logfile
alter database [add|drop] logfile member
alter database [add|drop] logfile group
alter database [noarchivelog|archivelog]
alter database rename file
create tablespace
alter tablespace [add|rename] datafile
alter tablespace [read write|read only]
drop tablespace
控制文件的备份,三种方式
1)使用OS命令进行拷贝
2)open状态下,使用alter database命令生成控制文件副本
3)open状态下,使用alter database backup controlfile to trace命令将控制文件备份到跟踪文件
控制文件的恢复,两种方式
1)mount状态下,将1,2)中备份的控制文件copy到指定位置,然后使用RECOVER DATABASE USING BACKUP CONTROLFILE(如果当前的重做日志没有损坏,最后需要恢复当前的重做日志)。
2)mount状态下,使用3)中的跟踪文件构造脚本并进行恢复。
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 8月 1 21:40:03 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 528482304 bytes
Fixed Size 1220360 bytes
Variable Size 176161016 bytes
Database Buffers 343932928 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
--open状态下生成控制文件副本
SQL> alter database backup controlfile to
2 '/oracle/10g/oracle/bakup/database/oralife.ctl';
alter database backup controlfile to
*
ERROR at line 1:
ORA-01580: error creating control backup file
/oracle/10g/oracle/bakup/database/oralife.ctl
ORA-27038: created file already exists
Additional information: 1
SQL> alter database backup controlfile to
2 '/oracle/10g/oracle/bakup/database/oralife.ctl' reuse; --reuse用于覆盖原有控制文件副本
Database altered.
--手动删除所有控制文件模拟文件丢失
SQL> ho rm /oracle/10g/oracle/product/10.2.0/oradata/oralife/*.ctl;
--使用evan登录,并添加数据
SQL> conn evan/evan
Connected.
SQL> select * from t_evan;
TEXT
--------------------------------------------------------------------------------
oracle
java
spring
hibernate
hibernate
SQL> insert into t_evan values('added');
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
--alter_oralife.log出现这样的信息:
Mon Aug 1 23:13:51 2011
ORA-00202: control file: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
--拷贝控制文件到目标路径
SQL>ho cp /oracle/10g/oracle/bakup/database/oralife.ctl /oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl
SQL> alter system set control_files='/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl' scope = spfile; --修改control_files参数,指定可用的控制文件
System altered.
SQL> startup force mount
ORACLE instance started.
Total System Global Area 528482304 bytes
Fixed Size 1220360 bytes
Variable Size 138412280 bytes
Database Buffers 381681664 bytes
Redo Buffers 7168000 bytes
Database mounted.
--生成trace文件
SQL> alter database backup controlfile to trace noresetlogs;
Database altered.
SELECT c.VALUE || '/' || d.instance_name || '_ora_' || a.spid || '.trc' TRACE
FROM v$process a, v$session b, v$parameter c, v$instance d
WHERE a.addr = b.paddr
AND b.audsid = USERENV ('sessionid')
AND c.NAME = 'user_dump_dest';
TRACE
--------------------------------------------------------------------------------
/oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_4558.trc
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
--打开trace文件,去掉注释,在shutdown状态下执行脚本,创建控制文件
--用evan登录验证数据
SQL> conn evan/evan
Connected.
SQL> select * from t_evan;
TEXT
--------------------------------------------------------------------------------
oracle
java
spring
hibernate
hibernate
added
6 rows selected.
可见数据没有丢失。
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl
这时 应该重建多个控制文件。
如何做?
--设置control_files参数,前提是使用spfile启动数据库
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/10g/oracle/product/10.
2 ,'/oracle/10g/oracle/product/10.2.0/oradata/oralife/control02.ctl'
3 ,'/oracle/10g/oracle/product/10.2.0/oradata/oralife/control03.ctl'
4 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--拷贝控制文件到不同的磁盘
SQL> host
[oracle@localhost oralife]$ cp control01.ctl control02.ctl
[oracle@localhost oralife]$ cp control01.ctl control03.ctl
[oracle@localhost ~]$ rlsqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 8月 2 21:02:57 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 528482304 bytes
Fixed Size 1220360 bytes
Variable Size 142606584 bytes
Database Buffers 377487360 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl
/oracle/10g/oracle/product/10.2.0/oradata/oralife/control02.ctl
/oracle/10g/oracle/product/10.2.0/oradata/oralife/control03.ctl
已生效。
alter database [add|drop] logfile
alter database [add|drop] logfile member
alter database [add|drop] logfile group
alter database [noarchivelog|archivelog]
alter database rename file
create tablespace
alter tablespace [add|rename] datafile
alter tablespace [read write|read only]
drop tablespace
控制文件的备份,三种方式
1)使用OS命令进行拷贝
2)open状态下,使用alter database命令生成控制文件副本
3)open状态下,使用alter database backup controlfile to trace命令将控制文件备份到跟踪文件
控制文件的恢复,两种方式
1)mount状态下,将1,2)中备份的控制文件copy到指定位置,然后使用RECOVER DATABASE USING BACKUP CONTROLFILE(如果当前的重做日志没有损坏,最后需要恢复当前的重做日志)。
2)mount状态下,使用3)中的跟踪文件构造脚本并进行恢复。
23--2示例:
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 8月 1 21:40:03 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 528482304 bytes
Fixed Size 1220360 bytes
Variable Size 176161016 bytes
Database Buffers 343932928 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
--open状态下生成控制文件副本
SQL> alter database backup controlfile to
2 '/oracle/10g/oracle/bakup/database/oralife.ctl';
alter database backup controlfile to
*
ERROR at line 1:
ORA-01580: error creating control backup file
/oracle/10g/oracle/bakup/database/oralife.ctl
ORA-27038: created file already exists
Additional information: 1
SQL> alter database backup controlfile to
2 '/oracle/10g/oracle/bakup/database/oralife.ctl' reuse; --reuse用于覆盖原有控制文件副本
Database altered.
--手动删除所有控制文件模拟文件丢失
SQL> ho rm /oracle/10g/oracle/product/10.2.0/oradata/oralife/*.ctl;
--使用evan登录,并添加数据
SQL> conn evan/evan
Connected.
SQL> select * from t_evan;
TEXT
--------------------------------------------------------------------------------
oracle
java
spring
hibernate
hibernate
SQL> insert into t_evan values('added');
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
--alter_oralife.log出现这样的信息:
Mon Aug 1 23:13:51 2011
ORA-00202: control file: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
--拷贝控制文件到目标路径
SQL>ho cp /oracle/10g/oracle/bakup/database/oralife.ctl /oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl
SQL> alter system set control_files='/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl' scope = spfile; --修改control_files参数,指定可用的控制文件
System altered.
SQL> startup force mount
ORACLE instance started.
Total System Global Area 528482304 bytes
Fixed Size 1220360 bytes
Variable Size 138412280 bytes
Database Buffers 381681664 bytes
Redo Buffers 7168000 bytes
Database mounted.
--生成trace文件
SQL> alter database backup controlfile to trace noresetlogs;
Database altered.
SELECT c.VALUE || '/' || d.instance_name || '_ora_' || a.spid || '.trc' TRACE
FROM v$process a, v$session b, v$parameter c, v$instance d
WHERE a.addr = b.paddr
AND b.audsid = USERENV ('sessionid')
AND c.NAME = 'user_dump_dest';
TRACE
--------------------------------------------------------------------------------
/oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_4558.trc
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
--打开trace文件,去掉注释,在shutdown状态下执行脚本,创建控制文件
--用evan登录验证数据
SQL> conn evan/evan
Connected.
SQL> select * from t_evan;
TEXT
--------------------------------------------------------------------------------
oracle
java
spring
hibernate
hibernate
added
6 rows selected.
可见数据没有丢失。
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl
这时 应该重建多个控制文件。
如何做?
--设置control_files参数,前提是使用spfile启动数据库
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/10g/oracle/product/10.
2.0/db_1/dbs/spfileoralife.ora
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl
2 ,'/oracle/10g/oracle/product/10.2.0/oradata/oralife/control02.ctl'
3 ,'/oracle/10g/oracle/product/10.2.0/oradata/oralife/control03.ctl'
4 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--拷贝控制文件到不同的磁盘
SQL> host
[oracle@localhost oralife]$ cp control01.ctl control02.ctl
[oracle@localhost oralife]$ cp control01.ctl control03.ctl
[oracle@localhost ~]$ rlsqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 8月 2 21:02:57 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 528482304 bytes
Fixed Size 1220360 bytes
Variable Size 142606584 bytes
Database Buffers 377487360 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl
/oracle/10g/oracle/product/10.2.0/oradata/oralife/control02.ctl
/oracle/10g/oracle/product/10.2.0/oradata/oralife/control03.ctl
已生效。