以下案例用以说明重建控制文件的恢复机制。
案例假设在备份之后,新建表空间,然后crash丢失所有控制文件和数据文件。
拥有所有日志及归档文件进行恢复。
1.数据库状态
[oracle@standby oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Mar 25 13:56:54 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/primary/system01.dbf
/opt/oracle/oradata/primary/undotbs01.dbf
/opt/oracle/oradata/primary/users01.dbf
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/oradata/primary/archive
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
2.热备脚本
[oracle@standby oradata]$ vi bak.sh sqlplus -S "/ as sysdba" < < ! alter tablespace SYSTEM begin backup; alter tablespace UNDOTBS1 begin backup; alter tablespace USERS begin backup; host cp /opt/oracle/oradata/primary/* /opt/oracle/oradata/primarybk alter tablespace SYSTEM end backup; alter tablespace UNDOTBS1 end backup; alter tablespace USERS end backup; exit; ! ~ ~~ ~ ~ ~ ~ ~ "bak.sh" 10L, 329C written |
[oracle@standby oradata]$ ./bak.sh Tablespace altered. Tablespace altered. Tablespace altered. Tablespace altered. Tablespace altered. Tablespace altered. |
4.更改数据并归档
[oracle@standby oradata]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Fri Mar 25 14:16:12 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning option JServer Release 9.2.0.4.0 - Production SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/oradata/primary/archive Oldest online log sequence 0 Next log sequence to archive 1 Current log sequence 1 SQL> alter database backup controlfile to trace; Database altered. SQL> create table t as select * from dba_users; create table t as select * from dba_users * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> drop table t; Table dropped. SQL> alter system switch logfile; System altered. |
5.创建新的表空间
SQL> create tablespace eygle 2 datafile '/opt/oracle/oradata/primary/eygle01.dbf' size 10M; Tablespace created. SQL> create table t tablespace eygle as select * from dba_users; Table created. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> |
此时记录一次控制文件的内容。 alter session set events 'immediate trace name CONTROLF level 10' ;
5.从此开始恢复
删除所有数据文件和控制文件,并从备份中恢复数据文件.
从跟踪文件中找到重建控制文件的脚本:
[oracle@standby udump]$ vi primary_ora_17369.trc *** SESSION ID:(9.11) 2005-03-25 14:16:28.109 *** 2005-03-25 14:16:28.109 # The following are current System-scope REDO Log Archival related # parameters and can be included in the database initialization file. # # LOG_ARCHIVE_DEST='' # LOG_ARCHIVE_DUPLEX_DEST='' # # LOG_ARCHIVE_FORMAT=%t_%s.dbf # REMOTE_ARCHIVE_ENABLE=TRUE # LOG_ARCHIVE_START=TRUE # LOG_ARCHIVE_MAX_PROCESSES=2 # STANDBY_FILE_MANAGEMENT=MANUAL # STANDBY_ARCHIVE_DEST=?/dbs/arch # FAL_CLIENT='' # FAL_SERVER='' # # LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/oradata/primary/archive' # LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY' # LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM SYNC' # LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY' # LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED' # LOG_ARCHIVE_DEST_STATE_1=ENABLE # # Below are two sets of SQL statements, each of which creates a new # control file and uses it to open the database. The first set opens # the database with the NORESETLOGS option and should be used only if # the current versions of all online logs are available. The second # set opens the database with the RESETLOGS option and should be used # if online logs are unavailable. # The appropriate set of statements can be copied from the trace into # a script file, edited as necessary, and executed when there is a # need to re-create the control file. # # Set #1. NORESETLOGS case # # The following commands will create a new control file and use it # to open the database. # Data used by the recovery manager will be lost. Additional logs may # be required for media recovery of offline data files. Use this # only if the current version of all online logs are available. STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "PRIMARY" NORESETLOGS ARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE MAXLOGFILES 5 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE GROUP 1 '/opt/oracle/oradata/primary/redo01.log' SIZE 10M, GROUP 2 '/opt/oracle/oradata/primary/redo02.log' SIZE 10M, GROUP 3 '/opt/oracle/oradata/primary/redo03.log' SIZE 10M -- STANDBY LOGFILE DATAFILE '/opt/oracle/oradata/primary/system01.dbf', '/opt/oracle/oradata/primary/undotbs01.dbf', '/opt/oracle/oradata/primary/users01.dbf' CHARACTER SET ZHS16CGB231280 ; # Configure RMAN configuration record 1 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON'); # Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal or immediate. RECOVER DATABASE # All logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG ALL; # Database can now be opened normally. ALTER DATABASE OPEN; # No tempfile entries found to add. # # Set #2. RESETLOGS case [oracle@standby oradata]$ vi ctl.sql STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "PRIMARY" NORESETLOGS ARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE MAXLOGFILES 5 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE GROUP 1 '/opt/oracle/oradata/primary/redo01.log' SIZE 10M, GROUP 2 '/opt/oracle/oradata/primary/redo02.log' SIZE 10M, GROUP 3 '/opt/oracle/oradata/primary/redo03.log' SIZE 10M -- STANDBY LOGFILE DATAFILE '/opt/oracle/oradata/primary/system01.dbf', '/opt/oracle/oradata/primary/undotbs01.dbf', '/opt/oracle/oradata/primary/users01.dbf' CHARACTER SET ZHS16CGB231280 ; ~ ~ ~ "ctl.sql" [New] 19L, 613C written |
重建控制文件
[oracle@standby oradata]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Fri Mar 25 14:23:48 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> @ctl.sql ORACLE instance started. Total System Global Area 135337420 bytes Fixed Size 452044 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Control file created. |
记录此控制文件内容
SQL> alter session set events 'immediate trace name CONTROLF level 10' 2 / Session altered. SQL> recover database; ORA-00279: change 7150162 generated at 03/25/2005 14:08:16 needed for thread 1 ORA-00289: suggestion : /opt/oracle/oradata/primary/archive1_1.dbf ORA-00280: change 7150162 for thread 1 is in sequence #1 Specify log: { =suggested | filename | AUTO | CANCEL} auto ORA-00283: recovery session canceled due to errors ORA-01244: unnamed datafile(s) added to controlfile by media recovery ORA-01110: data file 4: '/opt/oracle/oradata/primary/eygle01.dbf' ORA-01112: media recovery not started SQL> recover database ; ORA-00283: recovery session canceled due to errors ORA-01111: name for data file 4 is unknown - rename to correct file ORA-01110: data file 4: '/opt/oracle/product/9.2.0/dbs/UNNAMED00004' ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01111: name for data file 4 is unknown - rename to correct file ORA-01110: data file 4: '/opt/oracle/product/9.2.0/dbs/UNNAMED00004' SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/primary/system01.dbf /opt/oracle/oradata/primary/undotbs01.dbf /opt/oracle/oradata/primary/users01.dbf /opt/oracle/product/9.2.0/dbs/UNNAMED00004 SQL> alter database create datafile '/opt/oracle/product/9.2.0/dbs/UNNAMED00004' as '/opt/oracle/oradata/primary/eygle01.dbf' reuse; Database altered. SQL> recover database; Media recovery complete. |
记录恢复后的控制文件状态
SQL> alter session set events 'immediate trace name CONTROLF level 10' 2 / Session altered. SQL> alter database open; Database altered. |
记录Open后的控制文件状态
SQL> alter session set events 'immediate trace name CONTROLF level 10'; Session altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/primary/system01.dbf /opt/oracle/oradata/primary/undotbs01.dbf /opt/oracle/oradata/primary/users01.dbf /opt/oracle/oradata/primary/eygle01.dbf SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/oradata/primary/archive Oldest online log sequence 3 Next log sequence to archive 5 Current log sequence 5 SQL> |
7.我们来比较一下几个控制文件的不同
a)最显著的
*************************************************************************** DATA FILE RECORDS *************************************************************************** (blkno = 0x6, size = 180, max = 100, in-use = 3, last-recid= 0) 在重建的控制文件中,只能找到3个数据文件,因为丢失了一个数据文件. 原控制文件中,包含四个数据文件. *************************************************************************** DATA FILE RECORDS *************************************************************************** (blkno = 0x6, size = 180, max = 100, in-use = 4, last-recid= 1) DATA FILE #4: (name #7) /opt/oracle/oradata/primary/eygle01.dbf creation size=1280 block size=8192 status=0xe head=7 tail=7 dup=1 tablespace 4, index=5 krfil=4 prev_file=0 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:5 scn: 0x0000.006d1b6c 03/25/2005 14:18:15 Stop scn: 0x0000.006d1b6c 03/25/2005 14:18:15 Creation Checkpointed at scn: 0x0000.006d1b33 03/25/2005 14:17:33 thread:1 rba:(0x2.d.10) enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Offline scn: 0x0000.00000000 prev_range: 0 Online Checkpointed at scn: 0x0000.00000000 thread:0 rba:(0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Hot Backup end marker scn: 0x0000.00000000 aux_file is NOT DEFINED b)重建的控制文件中不包含检查点进程记录信息 *************************************************************************** CHECKPOINT PROGRESS RECORDS *************************************************************************** (blkno = 0x4, size = 104, max = 1, in-use = 1, last-recid= 0) THREAD #1 - status:0x0 flags:0x0 dirty:0 low cache rba:(0x0.0.0) on disk rba:(0x0.0.0) on disk scn: 0x0000.00000000 01/01/1988 00:00:00 resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00 heartbeat: 553849474 mount id: 1391347512 MTTR statistics status: 1 Init time: Avg: 0, Times measured: 0 File open time: Avg: 100000, Times measured: 1 Log block read time: Avg: 20, Times measured: 1 Data block handling time: Avg: 558, Times measured: 1 而原控制文件中,这是重要信息: *************************************************************************** CHECKPOINT PROGRESS RECORDS *************************************************************************** (blkno = 0x4, size = 104, max = 1, in-use = 1, last-recid= 0) THREAD #1 - status:0x1 flags:0x0 dirty:0 low cache rba:(0xffffffff.ffffffff.ffff) on disk rba:(0x4.3.0) on disk scn: 0x0000.006d1b62 03/25/2005 14:18:11 resetlogs scn: 0x0000.0066197e 03/09/2005 10:44:55 heartbeat: 553872664 mount id: 1391378642 MTTR statistics status: 1 Init time: Avg: 5540356, Times measured: 3 File open time: Avg: 1888, Times measured: 11 Log block read time: Avg: 20, Times measured: 1 Data block handling time: Avg: 558, Times measured: 1 c)重建的控制文件,从数据文件中获得文件状态及检查点信息 DATA FILE #1: (name #6) /opt/oracle/oradata/primary/system01.dbf creation size=0 block size=8192 status=0x12 head=6 tail=6 dup=1 tablespace 0, index=1 krfil=1 prev_file=0 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:181 scn: 0x0000.006d1b6c 01/01/1988 00:00:00 Stop scn: 0x0000.006d1b6c 03/25/2005 14:23:53 Creation Checkpointed at scn: 0x0000.00000005 06/30/2004 15:03:10 thread:0 rba:(0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Offline scn: 0x0000.00000000 prev_range: 0 Online Checkpointed at scn: 0x0000.00000000 thread:0 rba:(0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Hot Backup end marker scn: 0x0000.00000000 aux_file is NOT DEFINED 而原控制文件中包含完整信息 DATA FILE #1: (name #6) /opt/oracle/oradata/primary/system01.dbf creation size=0 block size=8192 status=0xe head=6 tail=6 dup=1 tablespace 0, index=1 krfil=1 prev_file=0 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:185 scn: 0x0000.006d1b6c 03/25/2005 14:18:15 Stop scn: 0x0000.006d1b6c 03/25/2005 14:18:15 Creation Checkpointed at scn: 0x0000.00000005 06/30/2004 15:03:10 thread:0 rba:(0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Offline scn: 0x0000.0066197d prev_range: 0 Online Checkpointed at scn: 0x0000.0066197e 03/09/2005 10:44:55 thread:1 rba:(0x1.2.0) enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Hot Backup end marker scn: 0x0000.00000000 aux_file is NOT DEFINED 但是重建控制文件中的SCN是一致的,恢复时需要根据这个SCN向前推移。 d)当然控制文件中的归档、备份信息在重建后都丢失了 e)原控制文件中还包含自动控制文件备份信息 *************************************************************************** EXTENDED DATABASE ENTRY *************************************************************************** (blkno = 0x71, size = 276, max = 1, in-use = 1, last-recid= 0) Control AutoBackup date(dd/mm/yyyy)=25/ 3/2005 Next AutoBackup sequence= 1 这个重建后显然丢失了。 |