|
实验：基于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> 至此，控制文件恢复完毕
| 
发表于 2014-10-10 15:05:14
