ORA-00279: change 240795288 generated at 08/03/2010 00:16:24 needed for thread　1 ORA-00289: suggestion : +ARCHDG ORA-00280: change 240795288 for thread 1 is in sequence #3

老鹰北京有限公司

中心医院

数据库故障处理报告

数据库宕机

作者:                        付培利

创建日期:               2010年08月5日

修改日期:  2010年08月5日

修改记录

日期	作者	版本	修改记录
2010-08-5	付培利	1.0	Initial

分发者

姓名	公司
	北京移动
	中心医院

审阅记录

姓名	职位

相关文档

1.

 TOC \o "1-4" \h \z 1.      文档控制. PAGEREF _Toc263860200 \h ii

修改记录  PAGEREF _Toc263860201 \h ii

分发者  PAGEREF _Toc263860202 \h ii

审阅记录  PAGEREF _Toc263860203 \h ii

相关文档  PAGEREF _Toc263860204 \h ii

2.      目录  PAGEREF _Toc263860205 \h iii

3.      总结  PAGEREF _Toc263860206 \h 4

4.      现场处理. PAGEREF _Toc263860207 \h 5

5.      建议  PAGEREF _Toc263860208 \h 6

3.   总结

4.   现场处理

Sun Aug  1 14:36:41 2010

Thread 1 advanced to log sequence 1330 (LGWR switch)

  Current log# 1 seq# 1330 mem# 0: +ORACLEASMDS3400/hdzxyy/onlinelog/group_1.257.702315479

  Current log# 1 seq# 1330 mem# 1: +ARCHDG/hdzxyy/onlinelog/group_1.257.702315481

Sun Aug  1 21:08:49 2010

Thread 1 advanced to log sequence 1331 (LGWR switch)

  Current log# 2 seq# 1331 mem# 0: +ORACLEASMDS3400/hdzxyy/onlinelog/group_2.258.702315483

  Current log# 2 seq# 1331 mem# 1: +ARCHDG/hdzxyy/onlinelog/group_2.258.702315485

Mon Aug  2 07:48:52 2010

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Interface type 1 eth010.1.0.0 configured from OCR for use as a cluster interconnect

Interface type 1 eth2 192.168.0.0 configured from OCR for use as  a public interface

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on.

LICENSE_MAX_USERS = 0

SYS auditing is disabled

ksdpec: called for event 13740 prior to event group initialization

Starting up ORACLE RDBMS Version:10.2.0.4.0.

System parameters with non-default values:

  processes                = 800

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup nomount pfile = '/tempfs/pfile.ora';

ORACLE instance started.

Total System Global Area 2516582400 bytes

Fixed Size                  2086032 bytes

Variable Size             905972592 bytes

Database Buffers         1593835520 bytes

Redo Buffers               14688256 bytes

SQL> alter database mount;

Database altered.

SQL> recover database until cancel;

ORA-00279: change 240795288 generated at 08/03/2010 00:16:24 needed for thread　1

ORA-00289: suggestion : +ARCHDG

ORA-00280: change 240795288 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '+ORACLEASMDS3400/hdzxyy/datafile/system.260.702315491'

ORA-01112: media recovery not started

startupmountpfile= '/tempfs/pfile.ora';

ALTER SESSION SET EVENTS '10015 TRACE NAME ADJUST_SCN LEVEL 3';

alter database open;

Database altered.

create undo tablespace undotbs2　datafile '+ORACLEASMDS3400/hdzxyy/datafile/undotbs3' size100M;

alter system set undo_tablespace=’ UNDOTBS3’scope = spfile;

shutdown abort;

startup up;

drop tablespace undotbs1;

exp ‘sys/fwqoracle as sysdba’ buffer=20971520 full=y grants=y file=/tempfs/fulldb.dmp log=/tempfs/impfull_100803.log

Dbca

imp sys/fwqoracle buffer=20971520 full=y grants=y ignore=y file=/tempfs/fulldb.dmp log=/tempfs/impfull_100803.log

5.   定期rman全备

RMAN> connect target /

connected to target database: HDZXYY (DBID=562030610)

RMAN> run {

backup full tag ' hdzxyy' database

include current controlfile format '/tempfs/rman/orclfull_%d_%T_%s'

plus archivelog format '/tempfs/rman/arch_%d_%T_%s';

}

2> 3> 4> 5>

Starting backup at 03-AUG-10

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=844 instance=hdzxyy1 devtype=DISK

channel ORA_DISK_1: starting compressed archive log backupset

… …

Starting backup at 03-AUG-10

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=10 recid=2028 stamp=726098052

input archive log thread=2 sequence=3 recid=2027 stamp=726098051

channel ORA_DISK_1: starting piece 1 at 03-AUG-10

channel ORA_DISK_1: finished piece 1 at 03-AUG-10

piece handle=/tempfs/rman/arch_HDZXYY_20100803_73 tag= HDZXYY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04

channel ORA_DISK_1: deleting archive log(s)

archive log filename=+ARCHDG/hdzxyy/archivelog/2010_08_03/thread_1_seq_10.711.726098051 recid=2028 stamp=726098052

archive log filename=+ARCHDG/hdzxyy/archivelog/2010_08_03/thread_2_seq_3.755.726098051 recid=2027 stamp=726098051

Finished backup at 03-AUG-10

Starting Control File Autobackup at 03-AUG-10

piece handle=/tempfs/rman/cf_c-562030610-20100803-00 comment=NONE

Finished Control File Autobackup at 03-AUG-10

run {

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

backup full tag ' hdzxyy' database

include current controlfile format '/tempfs/rman/orclfull_%d_%T_%s'

plus archivelog format '/tempfs/rman/arch_%d_%T_%s';

}

rman

connect garget /

startup mount;

restore database;

--恢复

recover database;

alter database open;

SQL> show parameter

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

active_instance_count                integer

aq_tm_processes                      integer     0

archive_lag_target                   integer     0

asm_diskgroups                       string

asm_diskstring                       string

asm_power_limit                      integer     1

audit_file_dest                      string      /app/oracle/admin/hdzxyy/adump

audit_sys_operations                 boolean     FALSE

audit_syslog_level                   string

audit_trail                          string      NONE

background_core_dump                 string      partial

background_dump_dest                 string      /app/oracle/admin/hdzxyy/bdump

backup_tape_io_slaves                boolean     FALSE

bitmap_merge_area_size               integer     1048576

blank_trimming                       boolean     FALSE

buffer_pool_keep                     string

buffer_pool_recycle                  string

circuits                             integer

cluster_database                     boolean     TRUE

cluster_database_instances           integer     2

cluster_interconnects                string

commit_point_strength                integer     1

commit_write                         string

compatible                           string     10.2.0.3.0

control_file_record_keep_time        integer     7

control_files                        string      +ORACLEASMDS3400/hdzxyy/contro

                                                 lfile/current.272.726101231

core_dump_dest                       string      /app/oracle/admin/hdzxyy/cdump

cpu_count                            integer     8

create_bitmap_area_size              integer     8388608

create_stored_outlines               string

cursor_sharing                       string      EXACT

cursor_space_for_time                boolean     FALSE

db_16k_cache_size                    big integer 0

db_2k_cache_size                     big integer 0

db_32k_cache_size                    big integer 0

db_4k_cache_size                     big integer 0

db_8k_cache_size                     big integer 0

db_block_buffers                     integer     0

db_block_checking                    string      FALSE

db_block_checksum                    string      TRUE

db_block_size                        integer     8192

db_cache_advice                      string      ON

db_cache_size                        big integer 0

db_create_file_dest                  string      +ORACLEASMDS3400

db_create_online_log_dest_1          string

db_create_online_log_dest_2          string

db_create_online_log_dest_3          string

db_create_online_log_dest_4          string

db_create_online_log_dest_5          string

db_domain                            string

db_file_multiblock_read_count        integer     16

db_file_name_convert                 string

db_files                             integer     200

db_flashback_retention_target        integer     1440

db_keep_cache_size                   big integer 0

db_name                              string      hdzxyy

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 0

db_recycle_cache_size                big integer 0

db_unique_name                       string      hdzxyy

db_writer_processes                  integer     1

dbwr_io_slaves                       integer     0

ddl_wait_for_locks                   boolean     FALSE

dg_broker_config_file1               string      /app/oracle/product/10.2.0/dbs

                                                 /dr1hdzxyy.dat

dg_broker_config_file2               string      /app/oracle/product/10.2.0/dbs

                                                 /dr2hdzxyy.dat

dg_broker_start                      boolean     FALSE

disk_asynch_io                       boolean     TRUE

dispatchers                          string

distributed_lock_timeout             integer     60

dml_locks                            integer     3892

drs_start                            boolean     FALSE

event                                string

fal_client                           string

fal_server                           string

fast_start_io_target                 integer     0

fast_start_mttr_target               integer     0

fast_start_parallel_rollback         string      LOW

file_mapping                         boolean     FALSE

fileio_network_adapters              string

filesystemio_options                 string      none

fixed_date                           string

gc_files_to_locks                    string

gcs_server_processes