上篇中，结尾处并没有将数据库open起来，由于控制文件太老，所以只能手工重建控制文件，然后前滚数据库才能open。下面一步一步来实施。
手工重建控制文件，必须知道数据库的结构（就是数据文件和联机日志文件的信息），所以从老的控制文件dump一个出来，然后添加example03和04的两个数据文件即可。
SQL> alter database backup controlfile to trace as '/tmp/controlfile.txt';
Database altered.
SQL>
看看dump文件：
[oracle@db1 denver]$ more /tmp/controlfile.txt
-- 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_%r.dbf
--
-- DB_UNIQUE_NAME="denver"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- 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 Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DENVER" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/denver/redo01.rdo' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/denver/redo02.rdo' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/denver/redo03.rdo' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/oradata/denver/system01.dbf',
'/u01/oradata/denver/sysaux01.dbf',
'/u01/oradata/denver/undotbs01.dbf',
'/u01/oradata/denver/users01.dbf',
'/u01/oradata/denver/example01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/flash_recovery_area/DENVER/archivelog/2015_10_04/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/flash_recovery_area/DENVER/archivelog/2015_10_04/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/denver/temp01.dbf' REUSE;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DENVER" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/denver/redo01.rdo' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/denver/redo02.rdo' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/denver/redo03.rdo' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/oradata/denver/system01.dbf',
'/u01/oradata/denver/sysaux01.dbf',
'/u01/oradata/denver/undotbs01.dbf',
'/u01/oradata/denver/users01.dbf',
'/u01/oradata/denver/example01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/flash_recovery_area/DENVER/archivelog/2015_10_04/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/flash_recovery_area/DENVER/archivelog/2015_10_04/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/denver/temp01.dbf' REUSE;
-- End of tempfile additions.
--
[oracle@db1 denver]$
根据noresetlogs的步骤，先abort数据库：
SQL> shutdown abort
ORACLE instance shut down.
SQL>
从前面剪出并修改为如下：
CREATE CONTROLFILE REUSE DATABASE "DENVER" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/denver/redo01.rdo' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/denver/redo02.rdo' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/denver/redo03.rdo' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/oradata/denver/system01.dbf',
'/u01/oradata/denver/sysaux01.dbf',
'/u01/oradata/denver/undotbs01.dbf',
'/u01/oradata/denver/users01.dbf',
'/u01/oradata/denver/example01.dbf',
'/u01/oradata/denver/example02.dbf',
'/u01/oradata/denver/example03.dbf',
'/u01/oradata/denver/example04.dbf'
CHARACTER SET ZHS16GBK;
去掉空行（这个一定要），然后添加红色部分，保存为/tmp/recreatectl.sql再执行：
SQL> @/tmp/recreatectl
Control file created.
SQL>
控制文件成功再造。
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
SQL> alter database open;
Database altered.
SQL>
数据库成功打开，此时并没有提示实例恢复等，是因为数据库只有ctl文件是老的，其他部分是一致的。
那么如果，我们不知道到添加的文件呢？毕竟实际环境是这样的：
干净关闭数据库，再将之前那个老的控制文件恢复为数据库的控制文件：
[oracle@db1 denver]$ cp /tmp/control01.ctl /u01/oradata/denver/control01.ctl;cp /tmp/control01.ctl /u01/flash_recovery_area/denver/control02.ctl
然后打开数据库：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/oradata/denver/system01.dbf'
ORA-01207: file is more recent than control file - old control file
SQL>
这次我们依然使用手工创建控制文件的方式来恢复：
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 595593248 bytes
Database Buffers 230686720 bytes
Redo Buffers 6606848 bytes
SQL>
[oracle@db1 ~]$ more /tmp/recreatectl.sql
CREATE CONTROLFILE REUSE DATABASE "DENVER" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/denver/redo01.rdo' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/denver/redo02.rdo' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/denver/redo03.rdo' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/oradata/denver/system01.dbf',
'/u01/oradata/denver/sysaux01.dbf',
'/u01/oradata/denver/undotbs01.dbf',
'/u01/oradata/denver/users01.dbf',
'/u01/oradata/denver/example01.dbf'
CHARACTER SET ZHS16GBK;
[oracle@db1 ~]$
注意此处并没有添加example的02、03和04数据文件。
SQL> @/tmp/recreatectl
Control file created.
SQL>
我们尝试打开数据库：
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
Database altered.
SQL>
我去，数据库居然打开了，为什么不报错，这里硬要报错的呀。
看看alert日志：
Sun Oct 04 22:40:38 2015
alter database open
Sun Oct 04 22:40:38 2015
Thread 1 advanced to log sequence 31 (thread open)
Thread 1 opened at log sequence 31
Current log# 1 seq# 31 mem# 0: /u01/oradata/denver/redo01.rdo
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Oct 04 22:40:38 2015
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #6 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00006' in the controlfile.
File #7 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00007' in the controlfile.
File #8 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00008' in the 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: TEMP
*********************************************************************
Database Characterset is ZHS16GBK
红色部分显示，数据库在打开的时候，检查了数据字典和控制文件的一致性，很明显，6/7/8号文件没有在控制文件里，所以被offline了。v$datafile也看穿了这一切：
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/denver/system01.dbf
/u01/oradata/denver/sysaux01.dbf
/u01/oradata/denver/undotbs01.dbf
/u01/oradata/denver/users01.dbf
/u01/oradata/denver/example01.dbf
/u01/app/oracle/dbs/MISSING00006
/u01/app/oracle/dbs/MISSING00007
/u01/app/oracle/dbs/MISSING00008
8 rows selected.
SQL>
哎呀，在open之前，是不是应该要前滚数据库，让控制文件赶上来？
那就再来一次：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
[oracle@db1 denver]$ cp /tmp/control01.ctl /u01/oradata/denver/control01.ctl;cp /tmp/control01.ctl /u01/flash_recovery_area/denver/control02.ctl
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 595593248 bytes
Database Buffers 230686720 bytes
Redo Buffers 6606848 bytes
SQL> @/tmp/recreatectl
Control file created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/denver/system01.dbf
/u01/oradata/denver/sysaux01.dbf
/u01/oradata/denver/undotbs01.dbf
/u01/oradata/denver/users01.dbf
/u01/oradata/denver/example01.dbf
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>
数据库很明显需要前滚，因为控制文件并不知道example表空间所发生的一切。关键是怎么前滚呢？
尝试一下实例恢复，recover database：
SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1371726 generated at 10/04/2015 22:46:34 needed for thread 1
ORA-00289: suggestion :
/u01/flash_recovery_area/DENVER/archivelog/2015_10_04/o1_mf_1_31_%u_.arc
ORA-00280: change 1371726 for thread 1 is in sequence #31
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/u01/flash_recovery_area/DENVER/archivelog/2015_10_04/o1_mf_1_31_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/u01/flash_recovery_area/DENVER/archivelog/2015_10_04/o1_mf_1_31_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>
洗白，由于数据库未在归档模式，所以卵儿了。
尝试打开数据库：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL>
SQL> alter database open NORESETLOGS ;
alter database open NORESETLOGS
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open
SQL>
需要resetlogs了，没办法，重建控制文件之后，再执行了recover操作，必须resetlogs才能打开数据库。
还是回到刚刚直接open数据库那里，open后，数据文件678都被offline了：
File #6 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00006' in the controlfile.
File #7 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00007' in the controlfile.
File #8 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00008' in the controlfile.
由于我们678号数据文件是刚刚添加的，所以很明显只能删除这些数据文件：
SQL> alter tablespace example drop datafile 6;
alter tablespace example drop datafile 6
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace
SQL>
额，fuck，本地管理的表空间里的offline数据文件不能被删除。那怎么办？
速度在mount模式下试试：
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 595593248 bytes
Database Buffers 230686720 bytes
Redo Buffers 6606848 bytes
Database mounted.
SQL> alter tablespace example drop datafile 6;
alter tablespace example drop datafile 6
*
ERROR at line 1:
ORA-01109: database not open
SQL>
很明显不行，因为数据根本没有open，此时实例还无法遍历到数据文件，也就是实例还没有将数据文件给atouch。
那没有办法了，只能resetlogs打开数据库了：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 595593248 bytes
Database Buffers 230686720 bytes
Redo Buffers 6606848 bytes
SQL> @/tmp/recreatectl
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover database ;
Media recovery complete.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select file#,status,name from v$datafile;
FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 SYSTEM /u01/oradata/denver/system01.dbf
2 ONLINE /u01/oradata/denver/sysaux01.dbf
3 ONLINE /u01/oradata/denver/undotbs01.dbf
4 ONLINE /u01/oradata/denver/users01.dbf
5 ONLINE /u01/oradata/denver/example01.dbf
6 ONLINE /u01/oradata/denver/example02.dbf
7 ONLINE /u01/oradata/denver/example03.dbf
8 ONLINE /u01/oradata/denver/example04.dbf
8 rows selected.
SQL>
数据库成功打开，并没有resetlogs。ok。
146
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
