AI智能
改变未来

Oracle 手工恢复的案例(2)


案例场景:

数据文件备份- 控制文件备份 – 创建了表空间 -此时控制文件损坏-归档日志模式,日志文件皆可用。
(考点:控制文件备份中不存在新建表空间信息)。

数据文件控制文件备份:
[oracle@service3 prod]$ cp *.dbf /u01/backup/
[oracle@service3 prod]$ cp control01.ctl /u01/backup/

SYS@prod>startup;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 490736720 bytes
Database Buffers 335544320 bytes
Redo Buffers 6565888 bytes
Database mounted.
Database opened.

创建一个表空间:
SYS@prod>create tablespace t1 datafile ‘/u01/app/oracle/oradata/prod/t1.dbf’ size 50M;

SYS@prod>create table t1 tablespace t1 as select * from hr.employees;

[oracle@service3 prod]$ cp /etc/passwd control01.ctl
[oracle@service3 prod]$ cp /etc/passwd /u01/app/oracle/fast_recovery_area/prod/control02.ctl

日志切换数据库崩溃:
SYS@prod>alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2504
Session ID: 125 Serial number: 9

使用备份的控制文件进行恢复:
[oracle@service3 prod]$ cp /u01/backup/control01.ctl .
[oracle@service3 prod]$ cp /u01/backup/control01.ctl /u01/app/oracle/fast_recovery_area/prod/control02.ctl

SYS@prod>startup mount;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 490736720 bytes
Database Buffers 335544320 bytes
Redo Buffers 6565888 bytes
Database mounted.

SYS@prod>alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/prod/system01.dbf’
ORA-01207: file is more recent than control file – old control file

SYS@prod>recover database using backup controlfile;
ORA-00279: change 1198800 generated at 05/12/2020 22:43:32 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PROD/archivelog/2020_05_13/o1_mf_1_1_%u_.arc
ORA-00280: change 1198800 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/prod/redo01.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 7: ‘/u01/app/oracle/oradata/prod/t1.dbf’

创建一个空的文件:
SYS@prod>alter database create datafile 7 as ‘/u01/app/oracle/oradata/prod/t1.dbf’;
Database altered.
必须是数据文件自创建以来所有的日志都是存在的。

SYS@prod>recover database using backup controlfile;
ORA-00279: change 1199161 generated at 05/13/2020 09:05:00 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PROD/archivelog/2020_05_13/o1_mf_1_1_%u_.arc
ORA-00280: change 1199161 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/prod/redo01.log
Log applied.
Media recovery complete.

SYS@prod>alter database open resetlogs;

使用trace重建控制文件。
SYS@prod>alter database backup controlfile to trace as ‘/home/oracle/con2.ctl’;
将备份的控制文件中不存在的数据文件信息,手动添加到trace文件中,进行控制文件重建。
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “PROD” NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/prod/redo01.log’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘/u01/app/oracle/oradata/prod/redo02.log’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘/u01/app/oracle/oradata/prod/redo03.log’ SIZE 50M BLOCKSIZE 512
– STANDBY LOGFILE
DATAFILE
‘/u01/app/oracle/oradata/prod/system01.dbf’,
‘/u01/app/oracle/oradata/prod/sysaux01.dbf’,
‘/u01/app/oracle/oradata/prod/undotbs01.dbf’,
‘/u01/app/oracle/oradata/prod/users01.dbf’,
‘/u01/app/oracle/oradata/prod/example01.dbf’,
’/u01/app/oracle/oradata/prod/t1.dbf’,
‘/u01/app/oracle/oradata/prod/test.dbf’
CHARACTER SET AL32UTF8
;

SYS@prod>shutdown abort;
ORACLE instance shut down.
SYS@prod>@con.sql
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 490736720 bytes
Database Buffers 335544320 bytes
Redo Buffers 6565888 bytes

Control file created.

SYS@prod>alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/prod/system01.dbf’

SYS@prod>recover database;
Media recovery complete.
SYS@prod>alter database open;

Database altered.

SYS@prod>select count(*) from t1;

COUNT(*)
———-

107

1* select tablespace_name from dba_tablespaces where tablespace_name=‘T1’
SYS@prod>/

TABLESPACE_NAME
——————————

T1

赞(0) 打赏
未经允许不得转载:爱站程序员基地 » Oracle 手工恢复的案例(2)