说明
对于控制文件我们可以手trace出备份，利用trace文件脚本重新创建控制文件从而恢复数据库。
从trace文件中恢复
1 创建新的表空间、数据文件、用户
|
SQL>create tablespace ttt datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ size 10m; SQL>grant connect,resource to ttt identified by ttt; SQL>alter user ttt default tablespace ttt; SQL>alter user ttt account unlock; |
2 构造数据
|
SQL>conn ttt/ttt; SQL>create table ttt (id int); SQL> insert into ttt values(1); 1 row created. SQL> insert into ttt values(2); 1 row created. SQL> insert into ttt values(3); 1 row created. SQL> commit; Commit complete. SQL>select * from ttt; ID ---- 1 2 3 |
ttt用户默认表空间为ttt,创建表ttt，并插入3数据。
3 切换日志
|
SQL>conn / as sysdba; SQL>alter system switch logfile; |
切换日志，保证数据都写到DBF中。
4 开启控制文件自动备份
|
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/u01/app/oracle/backup/%F';
new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/u01/app/oracle/backup/%F'; new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oracle/u01/app/oracle/backup/%U';
new RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oracle/u01/app/oracle/backup/%U'; new RMAN configuration parameters are successfully stored released channel: ORA_DISK_1 |
5 备份控制文件
|
SQL>alter database backup controlfile to trace as '/home/oracle/control.sql'; 或者 |
6 再插入一些数据
|
SQL>conn ttt/ttt; SQL> insert into ttt values(4); 1 row created. SQL> insert into ttt values(5); 1 row created. SQL> insert into ttt values(6); 1 row created. SQL> commit; Commit complete. SQL>select * from ttt;
|
7 模拟丢失控制文件
|
SQL>conn / as sysdba; SQL>shutdown immediate; [oracle@dev206 ~]$ rm -rf /oracle/u01/app/oracle/oradata/orcl/control0*.ctl SQL> startup ORA-32004: obsolete and/or deprecated parameter(s) specified ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started.
Total System Global Area 1224736768 bytes Fixed Size 2020384 bytes Variable Size 352324576 bytes Database Buffers 855638016 bytes Redo Buffers 14753792 bytes ORA-00205: error in identifying control file, check alert log for more info |
此时在启动数据库时报错找不到控制文件而只能启动到nomount状态。
8 恢复控制文件
编辑刚刚备份产生的control.sql
|
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS FORCE LOGGING ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/oracle/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M, GROUP 2 '/oracle/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M, GROUP 3 '/oracle/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M -- STANDBY LOGFILE
DATAFILE '/oracle/u01/app/oracle/oradata/orcl/system01.dbf', '/oracle/u01/app/oracle/oradata/orcl/undotbs01.dbf', '/oracle/u01/app/oracle/oradata/orcl/sysaux01.dbf', '/oracle/u01/app/oracle/oradata/orcl/users01.dbf', '/oracle/u01/app/oracle/oradata/orcl/admin01.dbf', '/oracle/u01/app/oracle/oradata/orcl/ttt.dbf' CHARACTER SET AL32UTF8 |
这里取noresetlogs段，因为日志文件并没有损坏，不必重做日志文件。
执行完成后数据库自动启动到mount状态，启动数据库到open状态
|
SQL>alter database open; 如果我们之前没有一致性停库，这里我们需要恢复数据库 SQL>recover database; 恢复完成在启动到open状态。 |
如果，在备份数据库控制文件时使用的备份的二进制，则这里需要利用RMAN来恢复
|
RMAN>set dbid=1324770912 RMAN>restore controlfile from ‘/home/oracle/control.ctl’; Starting restore at 08-MAR-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=540 devtype=DISK
channel ORA_DISK_1: copied control file copy output filename=/oracle/u01/app/oracle/oradata/orcl/control01.ctl output filename=/oracle/u01/app/oracle/oradata/orcl/control02.ctl output filename=/oracle/u01/app/oracle/oradata/orcl/control03.ctl Finished restore at 08-MAR-13 RMAN>alter database mount; RMAN> alter database open;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 03/08/2013 17:05:46 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 03/08/2013 17:05:50 ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/oracle/u01/app/oracle/oradata/orcl/system01.dbf'
RMAN> restore database;
Starting restore at 08-MAR-13 Starting implicit crosscheck backup at 08-MAR-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=540 devtype=DISK Finished implicit crosscheck backup at 08-MAR-13
Starting implicit crosscheck copy at 08-MAR-13 using channel ORA_DISK_1 Finished implicit crosscheck copy at 08-MAR-13
searching for all files in the recovery area cataloging files... no files cataloged
using channel ORA_DISK_1
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 03/08/2013 17:06:08 RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 6 found to restore RMAN-06023: no backup or copy of datafile 5 found to restore RMAN-06023: no backup or copy of datafile 4 found to restore RMAN-06023: no backup or copy of datafile 3 found to restore RMAN-06023: no backup or copy of datafile 2 found to restore RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN> recover database;
Starting recover at 08-MAR-13 using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 1 is already on disk as file /oracle/u01/app/oracle/oradata/orcl/redo01.log archive log filename=/oracle/u01/app/oracle/oradata/orcl/redo01.log thread=1 sequence=1 media recovery complete, elapsed time: 00:00:02 Finished recover at 08-MAR-13
RMAN> alter database open;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 03/08/2013 17:06:25 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;
database opened |
9 检查数据
|
SQL> select count(*) from ttt.ttt; ID ---------- 1 2 3 4 5 6 |
数据库恢复完成.
注意：这样利用trace文件脚本恢复完成控制文件后需要手动添加temp表空间的数据文件。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26252014/viewspace-755720/ ，如需转载，请注明出处，否则将追究法律责任。
