2 Control file丢失的恢复
控制文件没有备份全部丢失
SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> set linesize 1000 SQL> col name for a50 SQL> select * from v$controlfile; STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS ------- -------------------------------------------------- --- ---------- -------------- /u01/app/oracle/oradata/orcl/control01.ctl NO 16384 594 /u01/app/oracle/oradata/orcl/control02.ctl NO 16384 594
[oracle@DSI ~]$ cd /u01/app/oracle/oradata/orcl/ [oracle@DSI orcl]$ ll total 2790764 -rw-r----- 1 oracle oinstall 9748480 Apr 29 15:17 control01.ctl -rw-r----- 1 oracle oinstall 9748480 Apr 29 15:17 control02.ctl
alter database backup controlfile to trace as '/tmp/control.sql'; ##备份一下控制文件，方便后面创建脚本，也可以使用官方的脚本进行，前提只要清楚数据库的一些基本配置，不知道可以去取 SQL> alter database backup controlfile to trace as '/tmp/control.sql'; Database altered.
##删除控制文件
[oracle@DSI orcl]$ pwd /u01/app/oracle/oradata/orcl [oracle@DSI orcl]$ ll total 2790764 -rw-r----- 1 oracle oinstall 9748480 Apr 29 15:59 control01.ctl -rw-r----- 1 oracle oinstall 9748480 Apr 29 15:59 control02.ctl -rw-r----- 1 oracle oinstall 52429312 Apr 29 15:15 redo01.log -rw-r----- 1 oracle oinstall 52429312 Apr 29 15:15 redo02.log -rw-r----- 1 oracle oinstall 52429312 Apr 29 15:57 redo03.log -rw-r----- 1 oracle oinstall 629153792 Apr 29 15:56 sysaux01.dbf -rw-r----- 1 oracle oinstall 786440192 Apr 29 15:56 system01.dbf -rw-r----- 1 oracle oinstall 61874176 Apr 29 15:15 temp01.dbf -rw-r----- 1 oracle oinstall 52436992 Apr 29 15:15 test01.dbf -rw-r----- 1 oracle oinstall 1184899072 Apr 29 15:56 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 Apr 29 15:15 users01.dbf [oracle@DSI orcl]$ rm -rf control0*
##redo 日志切换操作
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> alter system checkpoint; System altered. ##关闭并启动数据库 SQL> shutdown immediate; Database closed. ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> shutdown abort; ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 784998400 bytes Fixed Size 2257352 bytes Variable Size 511708728 bytes Database Buffers 264241152 bytes Redo Buffers 6791168 bytes ORA-00205: error in identifying control file, check alert log for more info
##查看alert文件
[oracle@DSI ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/ [oracle@DSI trace]$ tail -n 100 alert_orcl.log ORACLE_BASE from environment = /u01/app/oracle Mon Apr 29 16:02:06 2019 ALTER DATABASE MOUNT ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control02.ctl' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-205 signalled during: ALTER DATABASE MOUNT... Mon Apr 29 16:02:06 2019 Checker run found 2 new persistent data failures
##控制文件脚本分析
[oracle@DSI trace]$ more /tmp/control.sql STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf', '/u01/app/oracle/oradata/orcl/sysaux01.dbf', '/u01/app/oracle/oradata/orcl/undotbs01.dbf', '/u01/app/oracle/oradata/orcl/users01.dbf', '/u01/app/oracle/oradata/orcl/test01.dbf' CHARACTER SET AL32UTF8 ; ---DATABASE，db_name参数ORCL ---NORESETLOGS ---ARCHIVELOG ---LOGFILE ---DATAFILE ---CHARACTER
##数据库名称查看 --1 查看参数文件 SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl --2 bbed分析 [oracle@DSI ~]$ bbed BBED: Release 2.0.0.0.0 - Limited Production on Mon Apr 29 16:09:04 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set file 1 block 1 FILE# 1 BLOCK# 1 BBED> map /v File: /u01/app/oracle/oradata/orcl/system01.dbf (1) Block: 1 Dba:0x00400001 BBED> p kcvfh text kccfhdbn[0] @32 O text kccfhdbn[1] @33 R text kccfhdbn[2] @34 C text kccfhdbn[3] @35 L text kccfhdbn[4] @36 text kccfhdbn[5] @37 text kccfhdbn[6] @38 ##归档 SQL> show parameter log_archive NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string log_archive_dest string log_archive_dest_1 string log_archive_dest_10 string log_archive_format string %t_%s_%r.dbf ##字符集 SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production --11g的位置 SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from props$; FILE# BLOCK# ---------- ---------- 1 801 [oracle@DSI ~]$ dd if=/u01/app/oracle/oradata/orcl/system01.dbf of=/tmp/props bs=8192 skip=801 count=1 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 7.9458e-05 s, 103 MB/s [oracle@DSI ~]$ strings /tmp/props |more Calendar system, NLS_CHARACTERSET AL32UTF8 Character set, NLS_NUMERIC_CHARACTERS Numeric characters,
恢复的方法
noresetlogs手工恢复控制文件
resetlogs手工恢复控制文件
建议：生产环境恢复前做好全备，保护现场！！！
noresetlogs手工恢复控制文件
SQL>CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf', '/u01/app/oracle/oradata/orcl/sysaux01.dbf', '/u01/app/oracle/oradata/orcl/undotbs01.dbf', '/u01/app/oracle/oradata/orcl/users01.dbf', '/u01/app/oracle/oradata/orcl/test01.dbf' CHARACTER SET AL32UTF8 ; SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED ##控制文件生成 [oracle@DSI ~]$ cd /u01/app/oracle/oradata/orcl/ [oracle@DSI orcl]$ ll total 2791404 -rw-r----- 1 oracle oinstall 10076160 Apr 29 16:26 control01.ctl -rw-r----- 1 oracle oinstall 10076160 Apr 29 16:26 control02.ctl SQL> select SEQUENCE#,RESETLOGS_ID from v$archived_Log; no rows selected
手工注册归档文件：alter database register physical logfile '/u01/app/oracle/oraarch/1_24_967415830.dbf';
--批量注册归档文件
rman target/
catalog start with '/u01/app/oracle/oraarch/*.dbf'
恢复数据库：RECOVER DATABASE
所有redo日志归档：ALTER SYSTEM ARCHIVE LOG ALL;
打开数据库：alter database open;
增加临时文件
create pfile ='/tmp/pfile20190429.sql' from spfile; [oracle@DSI ~]$ cat /tmp/pfile20190429.sql orcl.__db_cache_size=264241152 orcl.__java_pool_size=4194304 orcl.__large_pool_size=71303168 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=318767104 orcl.__sga_target=469762048 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=117440512 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=786432000 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' SQL> create pfile =/tmp/pfile20190429.sql from spfile; create pfile =/tmp/pfile20190429.sql from spfile * ERROR at line 1: ORA-02236: invalid file name SQL> create pfile ='/tmp/pfile20190429.sql' from spfile; File created. SQL> RECOVER DATABASE; ORA-00283: recovery session canceled due to errors ORA-00264: no recovery required SQL> ALTER SYSTEM ARCHIVE LOG ALL; System altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 88 Next log sequence to archive 90 Current log sequence 90 ##添加临时表空间 SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 61865984 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; Tablespace altered. SQL> alter system switch logfile; System altered. SQL> select SEQUENCE#,RESETLOGS_ID from v$archived_Log; SEQUENCE# RESETLOGS_ID ---------- ------------ 87 1006250831 88 1006250831 89 1006250831 90 1006250831 SQL> select status from v$instance; STATUS ------------ OPEN SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 91 52428800 512 1 NO CURRENT 1124896 29-APR-19 2.8147E+14 2 1 89 52428800 512 1 YES INACTIVE 1123967 29-APR-19 1124411 29-APR-19 3 1 90 52428800 512 1 YES INACTIVE 1124411 29-APR-19 1124896 29-APR-19
----resetlogs手工恢复控制文件
##任何的redo 损坏，需要进行resetlogs开启
##shutdown immediate;
##rm –rf redo*.log
STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf', '/u01/app/oracle/oradata/orcl/sysaux01.dbf', '/u01/app/oracle/oradata/orcl/undotbs01.dbf', '/u01/app/oracle/oradata/orcl/users01.dbf', '/u01/app/oracle/oradata/orcl/test01.dbf' CHARACTER SET AL32UTF8 ; SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; SQL> ALTER DATABASE OPEN RESETLOGS; SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 61865984 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
思考：
shutdown abort;
rm –rf redo*.log
Resetlogs解析 SQL> set linesize 1000 SQL> select * from v$database_incarnation; INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED ------------ ----------------- --------- ----------------------- --------- ------- ------------ ------------------ -------------------------- 1 1 22-APR-19 0 CURRENT 1006250831 0 NO log_archive_format string %t_%s_%r.dbf ##t=thread线程，s=sequence序列号，r=resetlog_id 为什么open要resetlogs，执行后，控制文件不认识之前的归档日志文件，v$database_incarnation;当前控制文件认识的信息 -rw-r----- 1 oracle oinstall 286208 Apr 29 16:31 1_89_1006250831.dbf -rw-r----- 1 oracle oinstall 77824 Apr 29 16:39 1_90_1006250831.dbf ##%t_%s_%r.dbf ##t=thread线程，s=sequence序列号，r=resetlog_id RMAN> list incarnation; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 ORCL 1534031567 CURRENT 1 22-APR-19 SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch' SCOPE=both; System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch Oldest online log sequence 92 Next log sequence to archive 94 Current log sequence 94 SQL> alter system switch logfile; System altered. SQL> / System altered.
数据在启动时候是要做一致性检查的，oracle在open阶段要做两次检查
1. 检查数据文件头的检查点计数（checkpoint cnt）是否和控制文件的检查点计数（checkpoint cnt）一致。
目的是确认数据文件是否来自同一版本，而不是从备份中恢复的。如果这一步检查通过，就进行第二步检查
2. 检查数据文件头的开始scn和控制文件中记录该文件的结束scn是否一致。
如果数据文件头的开始scn和控制文件中该文件的结束scn相等，那说明这个数据文件就不需要恢复，否则就要恢复文件
如果以上两步检查都通过，那就可以正常打开数据库，锁定数据文件，同时将控制文件中每个数据文件的结束scn设置无穷大。
resetlogs的作用
防止陈旧的数据进入数据库（保证数据库的一致性），这也就是为什么在用resetlogs打开数据库，一定要立即对数据库做个全备。
在控制文件，data file header，redo log header里存储”resetlogs data“，当open resetlogs被执行时，可以通过这些内容检查一致性。
RESETLOGS会初始化logs，重置log sequence号，创建一个新的incarnation
resetlogs的原理机制
resetlogs是如何来保证打开数据库是一致的呢？
1）在open resetlogs时，oracle要对比检查控制文件和数据字典file$,如果一个数据文件在file$中存在，但在控制文件中不存在，
那在控制文件中将创建一个这个文件条目（MISSINGnnn ‘nnn’是十进制的file_id）,同时这个文件被标记为离线并需要恢复。如果
实际中这个文件存在的话，可以通过如下sql更改到正确的文件名。
sql> alter database rename file 'MISSINGnnn' to '<filename>';然后数据文件被恢复，online
2）如果一个数据文件存在控制文件中，而不在数据字典file$中，那么直接把控制文件中这个文件的记录条目删除（oracle认为file$文件是正确的，要以它为准）
3）当用旧的备份控制文件恢复的时候，如果有数据文件不在控制文件中注册（会提示控制文件比较旧的错误），那就不得不重建数据文件
，以使数据文件注册到控制文件中，然后系统会自动利用redo/archivelog恢复这个数据文件。
在保证控制文件和file$文件内容一致之后，oracle还有做如下检查才能open resetlogs
4）数据文件的版本要小于当前数据库的版本（counter）
5）offline的数据文件必须被online或者直接drop
6）所有的数据文件不能设置fuzzy bit，所有的数据文件要有相同的检查点（checkpoint SCN）
resetlogs究竟做了哪些工作呢？（重新使用redo log）
1）所有的online logfile 的信息重新被放置在控制文件中。并且还要为有效的thread挑选一个logfile文件作为current logfile
2）log header被更新为log seq#
3）所有的online的数据文件头被新的checkpoint和新的‘resetlogs data’更新，offline的数据文件被标记为需要媒体恢复。
------------
1.哪些场景下需要用alter database open resetlogs打开库？
--不完全恢复的情况下，不管是要什么样的不完全恢复，SCN，TIME，跨越REDO
--用备份的控制文件来恢复
--新创建的控制文件来恢复，redo log有损坏或者丢失
--丢失redo logs或者损坏
2.在删除所有controlfile和redolog日志的情况下shutdown abort异常关库，能用resetlogs打开库吗？为什么？
oralce异常关闭，并删除redo，如果在启动的时候oracle数据库一致性检查是否，那数据库是不允许被open，即open resetlogs不成功，
noresetlogs 重建控制文件时，控制文件中的 datafile checkpoint 来自online logs 中的 current log头。
而resetlogs 重建控制文件时，控制文件中的 datafile checkpoint 来自各数据文件文件头。
3.用dd命令损坏其中一个控制文件的文件头（1号块）,然后尝试用startup mount;命令挂载数据库报错,请用最快的恢复方式恢复控制文件，给出详细操作步骤？
损坏操作如下：
echo "database_name:orcl" > db dd if=db of=control01.ctl bs=16834 seek=1 count=1 conv=notrunc sqlplus / as sysdba shutdown abort; startup mount; ##这里只损坏控制文件01，利用在线镜像进行快速恢复，把控制文件02复制改为01 [oracle@DSI orcl]$ ll total 2791964 -rw-r----- 1 oracle oinstall 10076160 Apr 30 09:40 control01.ctl -rw-r----- 1 oracle oinstall 10076160 Apr 30 09:40 control02.ctl -rw-r----- 1 oracle oinstall 52429312 Apr 30 09:39 redo01.log -rw-r----- 1 oracle oinstall 52429312 Apr 30 09:26 redo02.log -rw-r----- 1 oracle oinstall 52429312 Apr 30 09:26 redo03.log -rw-r----- 1 oracle oinstall 629153792 Apr 30 09:38 sysaux01.dbf -rw-r----- 1 oracle oinstall 786440192 Apr 30 09:39 system01.dbf -rw-r----- 1 oracle oinstall 61874176 Apr 29 22:02 temp01.dbf -rw-r----- 1 oracle oinstall 52436992 Apr 30 09:26 test01.dbf -rw-r----- 1 oracle oinstall 1184899072 Apr 30 09:39 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 Apr 30 09:26 users01.dbf [oracle@DSI orcl]$ pwd /u01/app/oracle/oradata/orcl [oracle@DSI orcl]$ echo "database_name:orcl" > db [oracle@DSI orcl]$ dd if=db of=control01.ctl bs=16834 seek=1 count=1 conv=notrunc 0+1 records in 0+1 records out 21 bytes (21 B) copied, 7.5857e-05 s, 277 kB/s SQL> shutdown immediate; ORA-00227: corrupt block detected in control file: (block 1, # blocks 1) ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl' SQL> shutdown abort; ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 784998400 bytes Fixed Size 2257352 bytes Variable Size 511708728 bytes Database Buffers 264241152 bytes Redo Buffers 6791168 bytes ORA-00227: corrupt block detected in control file: (block 0, # blocks ) [oracle@DSI orcl]$ rm control01.ctl [oracle@DSI orcl]$ cp control02.ctl control01.ctl ##利用在线镜像进行快速恢复 SQL> startup mount; ORA-01081: cannot start already-running ORACLE - shut it down first SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL> set linesize 1000 SQL> col name for a50 SQL> select * from v$controlfile; STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS ------- -------------------------------------------------- --- ---------- -------------- /u01/app/oracle/oradata/orcl/control01.ctl NO 16384 614 /u01/app/oracle/oradata/orcl/control02.ctl NO 16384 614