当前位置:Gxlcms > mysql > 丢失了所有控制文件副本后进行恢复以trace文件恢复

丢失了所有控制文件副本后进行恢复以trace文件恢复

时间:2021-07-01 10:21:17 帮助过:30人阅读

实验:基于trace的控制文件重建及数据库回复(所有控制文件丢失等) 1.测试数据的构造,创建只读表空间 create tablespace tbs_users datafile /u01/app/oracle/oradata/PROD/datafile/tbs_users1.dbf size 5m, /u01/app/oracle/oradata/PROD/datafile/tbs_us

实验:基于trace的控制文件重建及数据库回复(所有控制文件丢失等)

1.测试数据的构造,创建只读表空间
create tablespace tbs_users datafile
'/u01/app/oracle/oradata/PROD/datafile/tbs_users1.dbf' size 5m,
'/u01/app/oracle/oradata/PROD/datafile/tbs_users2.dbf' size 5m;

alter tablesapce tbs_users read only;

2.控制文件的转储
select name from v$controlfile;
--查看控制文件状态

转储:
oradebug setmypid;
alter database backup controlfile to trace;
oradebug tracefile_name;

3.删除控制文件
!rm /u01/app/oracle/oradata/PROD/control*.ctl
--若闪回区也有控制文件副本,一并删除;

alter system checkpoint;
create tablespace ts1 datafile '/u01/app/oracle/oradata/PROD/datafile/ts1.dbf' size 5m;
--触发错误
shutdown immeidate/abort
startup
ORA-00205:error in identifying control file;
check alert log for more info;


4.使用转储的跟踪文件进行控制文件重建
查看跟踪文件内容,确定使用noresetlogs进行重建

相关脚本:
-- Set #1. NORESETLOGS case
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/onlinelog/o1_mf_1_b395d7z0_.log',
'/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_1_b395df1j_.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'/u01/app/oracle/oradata/PROD/onlinelog/o1_mf_2_b395dlrd_.log',
'/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_2_b395dqql_.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'/u01/app/oracle/oradata/PROD/onlinelog/o1_mf_3_b395dwfk_.log',
'/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_3_b395f1j7_.log'
) SIZE 50M BLOCKSIZE 512

DATAFILE
'/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf',
'/u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b393xovt_.dbf',
'/u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b393xq2d_.dbf',
'/u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b393xqpm_.dbf',
'/u01/app/oracle/oradata/PROD/datafile/o1_mf_example_b393xp04_.dbf'
CHARACTER SET AL32UTF8
;

RECOVER DATABASE
--如果数据库非正常关闭,需要恢复数据库;

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;
--如果有创建的测试数据文件需要处理,则可以先跳过(以先打开数据库为准);
alter database datafile file_id offline;

ALTER DATABASE RENAME FILE 'MISSING00006'
TO '/u01/app/oracle/oradata/PROD/datafile/tbs_users1.dbf';
ALTER DATABASE RENAME FILE 'MISSING00007'
TO '/u01/app/oracle/oradata/PROD/datafile/tbs_users2.dbf';

ALTER TABLESPACE "TBS_USERS" ONLINE;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PROD/datafile/o1_mf_temp_b29o0g6r_.tmp'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;


备注:
1.只读状态的表空间,在利用跟踪文件后,其数据文件名称会发生变化,需要rename操作;
2.临时表空间,在利用trace重建控制文件后,其临时文件会丢失,需要重新添加或重建临时表空间;
3.在转储控制文件之后新创建的临时表空间,当我们重建控制文件后,这些表空间的数据文件,也需要进行处理;
4.可以利用rman中的命令来查看当前数据库数据文件和临时文 report schema
5.ODU 数据库恢复工具

具体实验:

5.1. 丢失了所有控制文件副本后进行恢复

注 在利用trace备份恢复控制文件的时候,有以下要特别注意:

- 只读表空间

- 临时表空间

- 离线表空间

1) 准备只读表空间环境

SYS@ORA11GR2>create tablespace ts_users datafile '/u01/app/oracle/oradata/ORA11GR2/ts_users01.dbf' size 5m,'/u01/app/oracle/oradata/ORA11GR2/ts_users02.dbf' size 5m;

Tablespace created.

SYS@ORA11GR2>col tablespace_name for a15

SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

TS_USERS ONLINE

7 rows selected.

SYS@ORA11GR2>

SYS@ORA11GR2>select file_name,status,online_status from dba_data_files;

FILE_NAME STATUS ONLINE_

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

/u01/app/oracle/oradata/ORA11GR2/users01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/system01.dbf AVAILABLE SYSTEM

/u01/app/oracle/oradata/ORA11GR2/example01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/ts_users01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/ts_users02.dbf AVAILABLE ONLINE

7 rows selected.

SYS@ORA11GR2>

SYS@ORA11GR2>alter tablespace ts_users read only;

Tablespace altered.

SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

TS_USERS READ ONLY

7 rows selected.

SYS@ORA11GR2>select file_name,status,online_status from dba_data_files;

FILE_NAME STATUS ONLINE_

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

/u01/app/oracle/oradata/ORA11GR2/users01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/system01.dbf AVAILABLE SYSTEM

/u01/app/oracle/oradata/ORA11GR2/example01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/ts_users01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/ts_users02.dbf AVAILABLE ONLINE

7 rows selected.

SYS@ORA11GR2>

2) 查看控制文件位置

SYS@ORA11GR2>select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/ORA11GR2/control01.ctl

/u01/app/oracle/oradata/ORA11GR2/control02.ctl

SYS@ORA11GR2>

3) 转储控制文件到trace文件中

SYS@ORA11GR2>oradebug setmypid

Statement processed.

SYS@ORA11GR2>alter database backup controlfile to trace;

Database altered.

SYS@ORA11GR2>oradebug tracefile_name

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_30816.trc

SYS@ORA11GR2>

4) 打开转储后文件,以下为部分内容

[oracle@ocmu ~]$

cat /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora

……

-- Set #1. NORESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- Additional logs may be required for media recovery of offline

-- Use this only if the current versions of all online logs are

-- available.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORA11GR2" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/ORA11GR2/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/ORA11GR2/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/ORA11GR2/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/ORA11GR2/system01.dbf',

'/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf',

'/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf',

'/u01/app/oracle/oradata/ORA11GR2/users01.dbf',

'/u01/app/oracle/oradata/ORA11GR2/example01.dbf'

CHARACTER SET AL32UTF8

;

……

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1_694825248.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1_799882343.dbf';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Files in read-only tablespaces are now named.

ALTER DATABASE RENAME FILE 'MISSING00006'

TO '/u01/app/oracle/oradata/ORA11GR2/ts_users01.dbf';

ALTER DATABASE RENAME FILE 'MISSING00007'

TO '/u01/app/oracle/oradata/ORA11GR2/ts_users02.dbf';

-- Online the files in read-only tablespaces.

ALTER TABLESPACE "TS_USERS" ONLINE;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORA11GR2/temp01.dbf'

SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- End of tempfile additions.

……

注 在转储的控制文件中,没有只读表空间的内容

本测试,是先生成转储文件,再恢复的,往往在控制文件丢失之前很少会有人转储控制文件的(当然

了,一般情况都会备份控制文件的),如果没有转储控制文件的话,我们也可以在其他数据库中转储

控制文件,按照目标库的情况进行调整,然后再创建。

5) 手工删除控制文件

[oracle@ocmu ORA11GR2]$ pwd

/u01/app/oracle/oradata/ORA11GR2

[oracle@ocmu ORA11GR2]$ ls *.ctl

control01.ctl control02.ctl

[oracle@ocmu ORA11GR2]$ rm *.ctl

[oracle@ocmu ORA11GR2]$ ls *.ctl

ls: *.ctl: No such file or directory

[oracle@ocmu ORA11GR2]$

6) 控制文件删除以后,数据库是可以正常登陆的

[oracle@ocmu ORA11GR2]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 15 16:09:53 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORA11GR2>

7) 当修改结构信息时,会写控制文件,我们创建一个表空间,立即报错

SYS@ORA11GR2> create tablespace ts_idx datafile '/u01/app/oracle/oradata/

ORA11GR2/ts_idx01.dbf' size 10m;

create tablespace ts_idx datafile '/u01/app/oracle/oradata/ORA11GR2/ts_idx01.dbf' size 10m

*

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/ORA11GR2/control01.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SYS@ORA11GR2>

8) 一致性关闭数据库已经不可能了(控制文件丢失,无法同步SCN),采取强制手段

SYS@ORA11GR2>shutdow immediate;

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/ORA11GR2/control01.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SYS@ORA11GR2>

SYS@ORA11GR2>shutdown abort;

ORACLE instance shut down.

SYS@ORA11GR2>

9) 按照转储文件中的步骤恢复控制文件,nomount启动数据库

SYS@ORA11GR2>startup nomount;

ORACLE instance started.

Total System Global Area 841162752 bytes

Fixed Size 1339768 bytes

Variable Size 494931592 bytes

Database Buffers 339738624 bytes

Redo Buffers 5152768 bytes

SYS@ORA11GR2>

10) 采用NORESETLOGS创建控制文件

SYS@ORA11GR2>CREATE CONTROLFILE REUSE DATABASE "ORA11GR2" NORESETLOGS NOARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 '/u01/app/oracle/oradata/ORA11GR2/redo01.log' SIZE 50M BLOCKSIZE 512,

9 GROUP 2 '/u01/app/oracle/oradata/ORA11GR2/redo02.log' SIZE 50M BLOCKSIZE 512,

10 GROUP 3 '/u01/app/oracle/oradata/ORA11GR2/redo03.log' SIZE 50M BLOCKSIZE 512

11 -- STANDBY LOGFILE

12 DATAFILE

13 '/u01/app/oracle/oradata/ORA11GR2/system01.dbf',

14 '/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf',

15 '/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf',

16 '/u01/app/oracle/oradata/ORA11GR2/users01.dbf',

17 '/u01/app/oracle/oradata/ORA11GR2/example01.dbf'

18 CHARACTER SET AL32UTF8

19 ;

Control file created.

SYS@ORA11GR2>

11) 因为之前shutdown没有采取一致性关闭,所以,需要recover database,recover后,可正常open数据库

SYS@ORA11GR2>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/ORA11GR2/system01.dbf'

SYS@ORA11GR2>recover database;

Media recovery complete.

SYS@ORA11GR2>

SYS@ORA11GR2>alter database open;

Database altered.

SYS@ORA11GR2>!ls *.ctl

control01.ctl control02.ctl

SYS@ORA11GR2>

看似已恢复完成,不过,很清晰的看到,转储的控制文件中,还有几步没有完成,我们继续

12) 再次查看表空间和数据文件状态

我们可以看到,只读表空间的状态依然是read only,不过数据文件的路径、名字及状态都有所变化

SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

TS_USERS READ ONLY

7 rows selected.

SYS@ORA11GR2>col file_name for a52

SYS@ORA11GR2>select file_name,status,online_status from dba_data_files;

FILE_NAME STATUS ONLINE_

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

/u01/app/oracle/oradata/ORA11GR2/example01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/users01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/system01.dbf AVAILABLE SYSTEM

/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00006 AVAILABLE OFFLINE

/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007 AVAILABLE OFFLINE

7 rows selected.

SYS@ORA11GR2>

13) 按照转储的步骤继续rename数据文件

注:如果不rename数据文件的话,数据文件是没法ONLINE的

SYS@ORA11GR2>ALTER DATABASE RENAME FILE 'MISSING00006'

TO '/u01/app/oracle/oradata/ORA11GR2/ts_users01.dbf';

Database altered.

SYS@ORA11GR2>ALTER DATABASE RENAME FILE 'MISSING00007'

TO '/u01/app/oracle/oradata/ORA11GR2/ts_users02.dbf';

Database altered.

SYS@ORA11GR2>

14) 按照转储步骤,修改表空间online

SYS@ORA11GR2>ALTER TABLESPACE "TS_USERS" ONLINE;

Tablespace altered.

SYS@ORA11GR2>

15) 再次查看表空间、数据文件的状态,已经彻底恢复原貌

SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

TS_USERS READ ONLY

7 rows selected.

SYS@ORA11GR2>select file_name,status,online_status from dba_data_files;

FILE_NAME STATUS ONLINE_

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

/u01/app/oracle/oradata/ORA11GR2/example01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/users01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/system01.dbf AVAILABLE SYSTEM

/u01/app/oracle/oradata/ORA11GR2/ts_users01.dbf AVAILABLE ONLINE

/u01/app/oracle/oradata/ORA11GR2/ts_users02.dbf AVAILABLE ONLINE

7 rows selected.

SYS@ORA11GR2>

16) 转储步骤中还涉及到了临时表空间,我们验证一下,重建控制文件后,临时表空间的情况,没有任何数据文件

SYS@ORA11GR2>select file_name,tablespace_name from dba_temp_files;

no rows selected

SYS@ORA11GR2>

17) 按照转储中的语句给临时表空间增加数据文件

SYS@ORA11GR2>ALTER TABLESPACE TEMP

ADD TEMPFILE '/u01/app/oracle/oradata/ORA11GR2/temp01.dbf'

SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

SYS@ORA11GR2>

18) 临时文件已添加成功

SYS@ORA11GR2>select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

FILE_NAME TABLESPACE_NAME

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

/u01/app/oracle/oradata/ORA11GR2/temp01.dbf TEMP

SYS@ORA11GR2>

至此,控制文件恢复完毕

人气教程排行