eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 家里换了电视 | Blog首页 | SYSOPER身份用户的权限限制 »

重建控制文件恢复丢失表空间一例
modb.pro

作者：eygle | 【转载请注】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：
以下案例用以说明重建控制文件的恢复机制。
案例假设在备份之后，新建表空间，然后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

3.执行备份 
                                                                                  
[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 
这个重建后显然丢失了。

还有一些内容不再列举。
历史上的今天...
    >> 2013-03-25文章:
    >> 2008-03-25文章:
           SOX(萨班斯)法案知多少
    >> 2007-03-25文章:
           eygle的菜谱：金钩玉条
    >> 2006-03-25文章:
           LOB对象与临时段

By eygle on 2005-03-25 16:03 | Comments (1) | Backup&Recovery | 217 |

1 Comment

habeeb | September 19, 2007 12:16 PM

please discuss issues on standby database on Oracle 7 and 8

CopyRight © 2004~2020 云和恩墨，成就未来！, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话：400-600-8755 业务咨询：010-59007017-7040 or 7037 业务合作: marketing@enmotech.com