实验：基于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
MAXDATAFILE本文来源gaodai$ma#com搞$代*码6网S 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>
至此，控制文件恢复完毕
转载请注明原文链接：丢失了所有控制文件副本后进行恢复以trace文件恢复
