控制文件被删除的测试
数据库未开启归档，首先备份一下控制文件
alter database backup controlfile to trace as '/home/oracle/controlfile.bak';
然后删除控制文件，执行shutdown abort;
SQL> shu abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracletest1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 24 14:17:54 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2257152 bytes
Variable Size 499126016 bytes
Database Buffers 255852544 bytes
Redo Buffers 6885376 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCLDBTE" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/oradata/ORCLDBTEST/onlinelog/o1_mf_1_gd027gd1_.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/oradata/ORCLDBTEST/onlinelog/o1_mf_2_gd027hwm_.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/oradata/ORCLDBTEST/onlinelog/o1_mf_3_gd027k4m_.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/oradata/ORCLDBTEST/datafile/o1_mf_system_gd024yoy_.dbf',
14 '/oradata/ORCLDBTEST/datafile/o1_mf_sysaux_gd024yqs_.dbf',
15 '/oradata/ORCLDBTEST/datafile/o1_mf_undotbs1_gd024yr2_.dbf',
16 '/oradata/ORCLDBTEST/datafile/o1_mf_users_gd024yrv_.dbf',
17 '/oradata/ORCLDBTEST/datafile/o1_mf_example_gd027w5s_.dbf'
18 CHARACTER SET ZHS16GBK;
Control file created.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/oradata/ORCLDBTEST/datafile/o1_mf_system_gd024yoy_.dbf'
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 1110441 generated at 06/24/2020 14:07:13 needed for thread 1
ORA-00289: suggestion :
/oradata/flash_recovery_area/ORCLDBTEST/archivelog/2020_06_24/o1_mf_1_18_%u_.arc
ORA-00280: change 1110441 for thread 1 is in sequence #18
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/ORCLDBTEST/onlinelog/o1_mf_1_gd027gd1_.log
ORA-00310: archived log contains sequence 16; sequence 18 required
ORA-00334: archived log: '/oradata/ORCLDBTEST/onlinelog/o1_mf_1_gd027gd1_.log'
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:
'/oradata/ORCLDBTEST/datafile/o1_mf_system_gd024yoy_.dbf'
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 1110441 generated at 06/24/2020 14:07:13 needed for thread 1
ORA-00289: suggestion :
/oradata/flash_recovery_area/ORCLDBTEST/archivelog/2020_06_24/o1_mf_1_18_%u_.arc
ORA-00280: change 1110441 for thread 1 is in sequence #18
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/ORCLDBTEST/onlinelog/o1_mf_2_gd027hwm_.log
ORA-00310: archived log contains sequence 17; sequence 18 required
ORA-00334: archived log: '/oradata/ORCLDBTEST/onlinelog/o1_mf_2_gd027hwm_.log'
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:
'/oradata/ORCLDBTEST/datafile/o1_mf_system_gd024yoy_.dbf'
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 1110441 generated at 06/24/2020 14:07:13 needed for thread 1
ORA-00289: suggestion :
/oradata/flash_recovery_area/ORCLDBTEST/archivelog/2020_06_24/o1_mf_1_18_%u_.arc
ORA-00280: change 1110441 for thread 1 is in sequence #18
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/ORCLDBTEST/onlinelog/o1_mf_3_gd027k4m_.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ORCLDBTEST/datafile/TEMP01.DBF' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
