suchen

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

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

WBOY
WBOYOriginal
2016-06-07 16:04:421290Durchsuche

实验：基于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>

至此，控制文件恢复完毕

Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：非superuser管理会话Nächster Artikel：Winform项目调用asp.net数据接口

In Verbindung stehende Artikel

Mehr sehen