oracle控制文件丢失了怎么办?

最新推荐文章于 2024-09-13 07:22:04 发布

原创最新推荐文章于 2024-09-13 07:22:04 发布 · 5.5k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #database #数据库 #file #disk #sql

ORACLE 专栏收录该内容

106 篇文章

订阅专栏

本文介绍了三种Oracle数据库控制文件的恢复方法：通过RMAN备份自动恢复控制文件；使用老的可打开的控制文件生成trace脚本，再重建控制文件；手动编写脚本恢复控制文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

三大方法：1.利用rman备份来还原

恢复控制文件自动备份
如果未使用恢复目录，则应该配置控制文件自动备份，从而可以在需要时快速还原控制文件。无论是否使用快速恢复区，用于还原控制文件的

命令都相同。但是，如果使用快速恢复区，则 RMAN 会隐式交叉检验控制文件中列出的备份和影像副本，并对处于快速恢复区但已还原控制文

件中未记录的所有文件进行编录，从而提高已还原控制文件在还原数据库的其余部分时的作用。
注：还原控制文件后不自动交叉检验磁带备份。如果使用磁带备份，则在还原控制文件并装载数据库后，必须交叉检验磁带上的备份。
要从自动备份还原控制文件，数据库必须处于 NOMOUNT 状态。然后，使用 RESTORE
CONTROLFILE FROM AUTOBACKUP 命令：
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

恢复控制文件自动备份（续）
RMAN 将搜索控制文件自动备份。如果找到一个自动备份，则 RMAN 会将控制文件从该备份还原到 CONTROL_FILES 初始化参数中列出的所有控制文件位置。
如果有恢复目录，则不必设置 DBID 或使用控制文件自动备份来还原控制文件。可以使用 RESTORE CONTROLFILE 命令，且没有任何参数：
RMAN> RESTORE CONTROLFILE;
执行此操作时，实例必须处于 NOMOUNT 状态，且 RMAN 必须连接到恢复目录。还原的控制文件将写入 CONTROL_FILES 初始化参数中列出的所有位置。
如果同时丢失了数据库的 SPFILE 且需要从自动备份进行还原，则过程与从自动备份还原控制文件类似。必须首先设置数据库的 DBID，然后才能使用 RESTORE SPFILE FROM AUTOBACKUP 命令。
在使用已还原服务器参数文件启动实例后，RMAN 可以从自动备份还原控制文件。在还原并装载控制文件后，将拥有还原和恢复数据库所需的备份信息。
从备份还原数据库的控制文件后，必须执行完全介质恢复，然后才能使用 RESETLOGS 选项打开数据库。

[oracle@yysf 2009_09_30]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Sep 30 17:08:44 2009

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 285212672 bytes

Fixed Size                     1218992 bytes
Variable Size                109053520 bytes
Database Buffers             171966464 bytes
Redo Buffers                   2973696 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 30-SEP-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /oracle/oradata
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oracle/oradata/ORCL/autobackup/2009_09_30/o1_mf_s_698950847_5d67z0ww_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oracle/oradata/orcl/control01.ctl
output filename=/oracle/oradata/orcl/control02.ctl
output filename=/oracle/oradata/orcl/control03.ctl
Finished restore at 30-SEP-09

RMAN> alter databaset mount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "clone, database"
RMAN-01008: the bad identifier was: databaset
RMAN-01007: at line 1 column 7 file: standard input

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 30-SEP-09
Starting implicit crosscheck backup at 30-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 30-SEP-09

Starting implicit crosscheck copy at 30-SEP-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 30-SEP-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oracle/oradata/ORCL/autobackup/2009_09_30/o1_mf_s_698950847_5d67z0ww_.bkp

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 30 is already on disk as file /oracle/oradata/orcl/redo03.log
archive log filename=/oracle/oradata/orcl/redo03.log thread=1 sequence=30
media recovery complete, elapsed time: 00:00:03
Finished recover at 30-SEP-09

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/30/2009 17:10:12
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

database opened

RMAN>

2.使用老的可以打开的控制文件生成trace脚本，再重建控制文件
1.制定pfile,添加老的控制文件作为源 old.ctl

2.利用老的控制文件把数据库置于mount状态,
SQL> startup mount pfile='initorcl.ora';
SQL> select status from v$instance;

STATUS
------------
MOUNTED

3.把数据库的ctl文件生成脚本录一下：
SQL>alter database backup controlfile to trace;

需要数据库在挂载的情况下,
生成一个trc文件导出到 udump文件夹中去,
这个trc大小是7K左右.

4.创建pfile，制定我欲恢复的控制文件的位置
[oracle@yysf dbs]$ vi initorcl.ora

orcl.__db_cache_size=79691776
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=109051904
orcl.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/orcl/adump'
*.audit_trail='NONE'
*.background_dump_dest='/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle/new.ctl'
*.core_dump_dest='/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_cache_size=79691776
*.db_create_file_dest='/oracle/oradata'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.java_pool_size=4194304
*.job_queue_processes=10
*.large_pool_size=4194304
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=66060288
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=201326592
*.shared_pool_size=109051904
*.streams_pool_size=0
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/orcl/udump'
~
5.察看trc 文件，把建立控制文件的那一部分拷贝出来:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
    '/oracle/oradata/ORCL/onlinelog/o1_mf_1_5bbblfr4_.log',
    '/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_5bbblgwf_.log',
    '/home/oracle/damao.dbf'
) SIZE 50M,
GROUP 2 (
    '/oracle/oradata/ORCL/onlinelog/o1_mf_2_5bbbljjw_.log',
    '/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_5bbblkdc_.log'
) SIZE 50M,
GROUP 3 (
    '/oracle/oradata/ORCL/onlinelog/o1_mf_3_5bbbllln_.log',
    '/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5bbblmq0_.log'
) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/ORCL/datafile/o1_mf_system_5bbbjkn1_.dbf',
'/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5bbbjkv1_.dbf',
'/oracle/oradata/ORCL/datafile/o1_mf_sysaux_5bbbjko0_.dbf',
'/oracle/oradata/ORCL/datafile/o1_mf_users_5bbbjkw8_.dbf',
'/oracle/oradata/ORCL/datafile/o1_mf_example_5bbbmdyq_.dbf'
CHARACTER SET AL32UTF8
;

做成文件 recover.sql

5.利用这个脚本生成新的控制文件
startup nomount;
SQL>@recover.sql

[oracle@yysf ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 13 21:47:46 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown abort;
ORACLE instance shut down.

---用建立的pfile把数据库置于 nomount状态，---------
SQL> startup nomount pfile='/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
ORACLE instance started.

Total System Global Area 201326592 bytes
Fixed Size                  1218508 bytes
Variable Size            117442612 bytes
Database Buffers          79691776 bytes
Redo Buffers                2973696 bytes

----用建立的sql脚本重建控制文件---------
SQL> @/home/oracle/recover.sql
ORA-01081: cannot start already-running ORACLE - shut it down first

Control file created.

6.恢复数据库，并已resetlogs 方式打开数据库。
[oracle@yysf ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 13 21:50:31 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> recover database using backup controlfile;
ORA-00279: change 813732 generated at 09/13/2009 21:21:13 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2009_09_13/o1_mf_1_26_%u_.arc
ORA-00280: change 813732 for thread 1 is in sequence #26

Specify log: { <RET>=suggested | filename | AUTO | CANCEL}
/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_5bbblkdc_.log
Log applied.
Media recovery complete

SQL> alter database open resetlogs;

Database altered.

3.手动写脚本，根据datafile的情况，编写ctl file生成脚本，剩余步骤和2一样。