지역 사회 배우다 도구 라이브러리 여가
찾다
한국어
집 > 데이터 베이스 > MySQL 튜토리얼 > 본문

OracleStudy之案例--重建数据库控制文件

WBOY
풀어 주다： 2016-06-07 14:55:45
원래의
1058명이 탐색했습니다.

Oracle Study之案例--重建数据库控制文件 系统环境： 操作系统： Linux RH6 数据库： Oracle 11gR2 案例分析： 数据库中所有的控制文件被意外破坏，非归档的库，在有trace备份的情况下，重建控制文件。 1、控制文件trace脚本 [oracle@rh6~]$catcrctr.sqlCREA

Oracle Study之案例--重建数据库控制文件

系统环境：

操作系统： Linux RH6

数据库：   Oracle 11gR2

   案例分析：

          数据库中所有的控制文件被意外破坏，非归档的库，在有trace备份的情况下，重建控制文件。

1、控制文件trace脚本

[oracle@rh6 ~]$ cat crctr.sql 
CREATE CONTROLFILE REUSE DATABASE "TEST3" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 10
    MAXLOGMEMBERS 5
    MAXDATAFILES 300
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/test3/redo01a.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/test3/redo02a.log'  SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/test3/system01.dbf',
  '/u01/app/oracle/oradata/test3/sysaux01.dbf',
  '/u01/app/oracle/oradata/test3/undotbs01.dbf',
  '/u01/app/oracle/oradata/test3/users01.dbf'
CHARACTER SET ZHS16GBK；
로그인 후 복사

2、启动Instance到nomount，重建controlfile

10:59:05 SYS@ test3 >startup nomount;
ORACLE instance started.
Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             213912664 bytes
Database Buffers           92274688 bytes
Redo Buffers                6336512 bytes

10:59:41 SYS@ test3 >@/home/oracle/crctr.sql
Control file created.
로그인 후 복사

3、告警日志

......
CREATE CONTROLFILE REUSE DATABASE "TEST3" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 10
    MAXLOGMEMBERS 5
    MAXDATAFILES 300
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/test3/redo01a.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/test3/redo02a.log'  SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/test3/system01.dbf',
  '/u01/app/oracle/oradata/test3/sysaux01.dbf',
  '/u01/app/oracle/oradata/test3/undotbs01.dbf',
  '/u01/app/oracle/oradata/test3/users01.dbf'
CHARACTER SET ZHS16GBK
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Wed Jan 07 11:00:02 2015
Successful mount of redo thread 1, with mount id 991126251

Completed: CREATE CONTROLFILE REUSE DATABASE "TEST3" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 10
    MAXLOGMEMBERS 5
    MAXDATAFILES 300
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/test3/redo01a.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/test3/redo02a.log'  SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/test3/system01.dbf',
  '/u01/app/oracle/oradata/test3/sysaux01.dbf',
  '/u01/app/oracle/oradata/test3/undotbs01.dbf',
  '/u01/app/oracle/oradata/test3/users01.dbf'
CHARACTER SET ZHS16GBK
Wed Jan 07 11:00:59 2015
......
로그인 후 복사

3、查看数据库状态

11:00:03 SYS@ test3 >select status from v$instance;
STATUS
------------
MOUNTED

11:00:27 SYS@ test3 >select file#,name ,checkpoint_change# from v$datafile;
     FILE# NAME                                               CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
         1 /u01/app/oracle/oradata/test3/system01.dbf                     333365
         2 /u01/app/oracle/oradata/test3/sysaux01.dbf                     333365
         3 /u01/app/oracle/oradata/test3/undotbs01.dbf                    333365
         4 /u01/app/oracle/oradata/test3/users01.dbf                      333365

11:00:46 SYS@ test3 >select file#,name ,checkpoint_change# from v$datafile_header;
     FILE# NAME                                               CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
         1 /u01/app/oracle/oradata/test3/system01.dbf                     333365
         2 /u01/app/oracle/oradata/test3/sysaux01.dbf                     333365
         3 /u01/app/oracle/oradata/test3/undotbs01.dbf                    333365
         4 /u01/app/oracle/oradata/test3/users01.dbf                      333365
로그인 후 복사

4、打开数据库

11:00:54 SYS@ test3 >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/test3/system01.dbf'

---打开数据库报错，需要做“media recovery”

执行介质恢复：
由于本库为非归档模式，只能通过current redolog来恢复

查看当前日志组：
[oracle@rh6 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 7 11:02:12 2015
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

11:02:12 SYS@ test3 >select member from v$logfile;

MEMBER
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/test3/redo01a.log
/u01/app/oracle/oradata/test3/redo02a.log

11:02:22 SYS@ test3 >select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         2         12 INACTIVE
         1         13 CURRENT


11:00:59 SYS@ test3 >recover database until cancel;
ORA-00279: change 333365 generated at 01/07/2015 10:30:26 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_13_868275293.dbf
ORA-00280: change 333365 for thread 1 is in sequence #13
11:01:42 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/test3/redo01a.log
Log applied.
Media recovery complete.
---恢复完成！

11:02:46 SYS@ test3 >alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Elapsed: 00:00:00.01

11:02:52 SYS@ test3 >alter database open resetlogs;
Database altered.

---Database open成功！
로그인 후 복사

查看告警日志：

alter database open
Errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_ora_3294.trc:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/test3/system01.dbf'
ORA-1113 signalled during: alter database open...
Wed Jan 07 11:01:40 2015
ALTER DATABASE RECOVER  database until cancel  
Media Recovery Start
Serial Media Recovery started
ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel  ...
Wed Jan 07 11:02:44 2015
ALTER DATABASE RECOVER    LOGFILE '/u01/app/oracle/oradata/test3/redo01a.log'  
Media Recovery Log /u01/app/oracle/oradata/test3/redo01a.log
Incomplete recovery applied all redo ever generated.
Recovery completed through change 334001 time 01/07/2015 10:51:13
Media Recovery Complete (test3)
Completed: ALTER DATABASE RECOVER    LOGFILE '/u01/app/oracle/oradata/test3/redo01a.log'  
alter database open
Errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_ora_3294.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: alter database open...
Wed Jan 07 11:03:04 2015
alter database open resetlogs
RESETLOGS after complete recovery through change 334001
Resetting resetlogs activation ID 990996637 (0x3b11689d)
Errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_ora_3294.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test3/redo01a.log'
Wed Jan 07 11:03:05 2015
Errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_m000_3336.trc:
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test3/redo01a.log'
Errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_ora_3294.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/test3/redo02a.log'
Errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_m000_3336.trc:
ORA-00316: log 2 of thread 1, type 0 in header is not log file
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/test3/redo02a.log'
Wed Jan 07 11:03:18 2015
Setting recovery target incarnation to 2
Wed Jan 07 11:03:20 2015
Checker run found 4 new persistent data failures
Wed Jan 07 11:03:21 2015
Assigning activation ID 991126251 (0x3b1362eb)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/test3/redo01a.log
Successful open of redo thread 1
Wed Jan 07 11:03:22 2015
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jan 07 11:03:23 2015
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Tablespace 'TEMPTS1' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMPTS1
*********************************************************************
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Jan 07 11:03:27 2015
QMNC started with pid=19, OS id=3341 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open resetlogs
Wed Jan 07 11:13:27 2015
Starting background process SMCO
Wed Jan 07 11:13:27 2015
SMCO started with pid=22, OS id=3382
로그인 후 복사

---至此，通过trace脚本，重建控制文件成功！






관련 라벨：
or 控制 数据库 文件 案例 재건
원천：php.cn
이전 기사：符合通用准则（commoncriteriacompliance） 다음 기사：利用DAC(Data-tierApplication)实现数据库结构迁移
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
저자별 최신 기사
최신 이슈
function_exists()는 사용자 정의 함수를 결정할 수 없습니다. 기능 테스트() {                                                                                ...
에서 2024-04-29 11:01:01
0
2
1910
Chrome 모바일 버전을 표시하는 방법 안녕하세요 선생님, Chrome을 모바일 버전으로 어떻게 변경하나요?
에서 2024-04-23 00:22:19
0
10
2081
자식 창이 부모 창을 작동하지만 출력이 응답하지 않습니다. 처음 두 문장은 실행 가능하지만 마지막 문장은 구현할 수 없습니다.
에서 2024-04-19 15:37:47
0
1
1754
상위 창에 출력이 없습니다. document.onclick = function(){ window.opener.document.write('나는 자식 창의 출력입니다.');
에서 2024-04-18 23:52:34
0
1
1653
CSS 마인드맵 코스웨어는 어디에 있나요? 코스웨어
에서 2024-04-16 10:10:18
0
0
1661
관련 주제
더>
인기 추천
인기 튜토리얼
더>
관련 튜토리얼
인기 추천
최신 강좌
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿