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

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

至此，控制文件恢复完毕