oracle 控制文件的重建

不到最后时刻，如三个控制文件都已损坏，又没有控制文件的备份。还是不要重建控制文件，处理不好就会有数据丢失。

NORESETLOGS

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 "KYEUPDB" NORESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 4096

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167'  SIZE 200M BLOCKSIZE 512,

  GROUP 2 '+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169'  SIZE 200M BLOCKSIZE 512,

  GROUP 3 '+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173'  SIZE 200M BLOCKSIZE 512,

  GROUP 4 '+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183'  SIZE 200M BLOCKSIZE 512,

  GROUP 5 '+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193'  SIZE 200M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '+DGSYSTEM/kyeupdb/datafile/system.271.978829205',

  '+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265',

  '+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323',

  '+DGSYSTEM/kyeupdb/datafile/users.275.978829391',

  '+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421',

  '+DGSYSTEM/kyeupdb/datafile/kye_tbs02.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 '/oracle/app/oracle/product/11.2.0/dbs/arch1_1_978829161.dbf';

-- 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;

-- Files in normal offline tablespaces are now named.

ALTER DATABASE RENAME FILE 'MISSING00006'

  TO '+DGSYSTEM/kyeupdb/datafile/kye_tbs01.dbf';

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379'

     SIZE 1024M REUSE AUTOEXTEND OFF;

-- End of tempfile additions.

操作：

[BEGIN] 2018/6/22 21:28:55

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3858223104 bytes

Fixed Size		    2259080 bytes

Variable Size		  788531064 bytes

Database Buffers	 3053453312 bytes

Redo Buffers		   13979648 bytes

CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" NORESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 4096

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167'  SIZE 200M BLOCKSIZE 512,

  GROUP 2 '+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169'  SIZE 200M BLOCKSIZE 512,

  GROUP 3 '+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173'  SIZE 200M BLOCKSIZE 512,

  GROUP 4 '+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183'  SIZE 200M BLOCKSIZE 512,

  GROUP 5 '+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193'  SIZE 200M BLOCKSIZE 512

DATAFILE

  '+DGSYSTEM/kyeupdb/datafile/system.271.978829205',

  '+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265',

  '+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323',

  '+DGSYSTEM/kyeupdb/datafile/users.275.978829391',

  '+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421',

  '+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf'

CHARACTER SET ZHS16GBK

 21  ;

Control file created.

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-00264: no recovery required

SQL> alter database open;

Database altered.

SQL> select * from dba_temp_files;

no rows selected

ALTER TABLESPACE TEMP ADD TEMPFILE '+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379'

  2       SIZE 1024M REUSE AUTOEXTEND OFF;

Tablespace altered.

SQL> select * from dba_temp_files;

FILE_NAME

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

   FILE_ID TABLESPACE_NAME		       BYTES	 BLOCKS STATUS	RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

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

+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379

	 1 TEMP 			  1073741824	 131072 ONLINE		   1 NO 	       0		  0 1072693248	    130944

[END] 2018/6/22 21:49:09

RESETLOGS

--     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 "KYEUPDB" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 4096

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167'  SIZE 200M BLOCKSIZE 512,

  GROUP 2 '+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169'  SIZE 200M BLOCKSIZE 512,

  GROUP 3 '+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173'  SIZE 200M BLOCKSIZE 512,

  GROUP 4 '+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183'  SIZE 200M BLOCKSIZE 512,

  GROUP 5 '+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193'  SIZE 200M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '+DGSYSTEM/kyeupdb/datafile/system.271.978829205',

  '+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265',

  '+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323',

  '+DGSYSTEM/kyeupdb/datafile/users.275.978829391',

  '+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421',

  '+DGSYSTEM/kyeupdb/datafile/kye_tbs02.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 '/oracle/app/oracle/product/11.2.0/dbs/arch1_1_978829161.dbf';

-- 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;

-- Files in normal offline tablespaces are now named.

ALTER DATABASE RENAME FILE 'MISSING00006'

  TO '+DGSYSTEM/kyeupdb/datafile/kye_tbs01.dbf';

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379'

     SIZE 1024M REUSE AUTOEXTEND OFF;

-- End of tempfile additions.

操作如下：

[BEGIN] 2018/6/22 21:58:29

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

[oracle@kyeup01 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 22 21:58:41 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> archive log list;

Database log mode	       No Archive Mode

Automatic archival	       Disabled

Archive destination	       /oracle/app/oracle/product/11.2.0/dbs/arch

Oldest online log sequence     10

Current log sequence	       14

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 4096

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167'  SIZE 200M BLOCKSIZE 512,

  GROUP 2 '+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169'  SIZE 200M BLOCKSIZE 512,

  GROUP 3 '+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173'  SIZE 200M BLOCKSIZE 512,

  GROUP 4 '+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183'  SIZE 200M BLOCKSIZE 512,

  GROUP 5 '+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193'  SIZE 200M BLOCKSIZE 512

DATAFILE

  '+DGSYSTEM/kyeupdb/datafile/system.271.978829205',

  '+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265',

  '+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323',

  '+DGSYSTEM/kyeupdb/datafile/users.275.978829391',

  '+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421',

  '+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf'

CHARACTER SET ZHS16GBK

 21  ;

CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS  NOARCHIVELOG

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 75347

Session ID: 386 Serial number: 5

SQL>

CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS  NOARCHIVELOG

SIZE 512,

  GROUP 2 '+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169'  SIZE 200M BLOCKSIZE 512,

  GROUP 3 '+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173'  SIZE 200M BLOCKSIZE 512,

  GROUP 4 '+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183'  SIZE 200M BLOCKSIZE 512,

  GROUP 5 '+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193'  SIZE 200M BLOCKSIZE 512

DATAFILE

  '+DGSYSTEM/kyeupdb/datafile/system.271.978829205',

  '+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265',

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 4096

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167'  SIZE 200M BLOCKSIZE 512,

  GROUP 2 '+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169'  SIZE 200M BLOCKSIZE 512,

  GROUP 3 '+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173'  SIZE 200M BLOCKSIZE 512,

  GROUP 4 '+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183'  SIZE 200M BLOCKSIZE 512,

  GROUP 5 '+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193'  SIZE 200M BLOCKSIZE 512

DATAFILE

  '+DGSYSTEM/kyeupdb/datafile/system.271.978829205',

  '+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265',

  '+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323',

  '+DGSYSTEM/kyeupdb/datafile/users.275.978829391',

  '+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421',

  '+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf'

CHARACTER SET ZHS16GBK

 21  ;

CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS  NOARCHIVELOG

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 75347

Session ID: 386 Serial number: 5

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3858223104 bytes

Fixed Size		    2259080 bytes

Variable Size		  788531064 bytes

Database Buffers	 3053453312 bytes

Redo Buffers		   13979648 bytes

CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

0M BLOCKSIZE 512,

  GROUP 5 '+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193'  SIZE 200M BLOCKSIZE 512

DATAFILE

  '+DGSYSTEM/kyeupdb/datafile/system.271.978829205',

  '+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265',

  '+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323',

  '+DGSYSTEM/kyeupdb/datafile/users.275.978829391',

  '+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421',

  '+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf'

CHARACTER SET ZHS16GBK

    MAXLOGMEMBERS 3

    MAXDATAFILES 4096

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167'  SIZE 200M BLOCKSIZE 512,

  GROUP 2 '+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169'  SIZE 200M BLOCKSIZE 512,

  GROUP 3 '+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173'  SIZE 200M BLOCKSIZE 512,

  GROUP 4 '+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183'  SIZE 200M BLOCKSIZE 512,

  GROUP 5 '+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193'  SIZE 200M BLOCKSIZE 512

DATAFILE

  '+DGSYSTEM/kyeupdb/datafile/system.271.978829205',

  '+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265',

  '+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323',

  '+DGSYSTEM/kyeupdb/datafile/users.275.978829391',

  '+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421',

  '+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf'

CHARACTER SET ZHS16GBK

 21  ;

Control file created.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

ALTER TABLESPACE TEMP ADD TEMPFILE '+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379'

  2       SIZE 1024M REUSE AUTOEXTEND OFF;

Tablespace altered.

SQL> select * from dba_temp_files;

FILE_NAME

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

   FILE_ID TABLESPACE_NAME		       BYTES	 BLOCKS STATUS

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

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

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

+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379

	 1 TEMP 			  1073741824	 131072 ONLINE

	   1 NO 	  0	     0		  0 1072693248	    130944

SQL> archive log list;

Database log mode	       No Archive Mode

Automatic archival	       Disabled

Archive destination	       /oracle/app/oracle/product/11.2.0/dbs/arch

Oldest online log sequence     1

Current log sequence	       1

SQL> 

[END] 2018/6/22 22:10:06

如果使用 resetlogs 打开的数据库，就需要对 DB 做一次备份。

resetlogs 命令表示一个数据库逻辑生存期的结束和另一个数据库逻辑生存期的开始， 每次使用 resetlogs 命令的时候，SCN 不会被重置，不过 oracle 会重置日志序列号， 而且会重置联机重做日志内容.

这样做是为了防止不完全恢复后日志序列会发生冲突（因为现有日志和数据文件间有 了时间差）。

在上面RESETLOGS重建控制文件的时候，归档是关闭的，如果是开启的，在开启RESETLOG之前，需要执行RECOVER DATABASE USING BACKUP CONTROLFILE

最新文章

  1. C++随笔：.NET CoreCLR之GC探索（4）
  2. （转载）SQL去除回车符，换行符，空格和水平制表符
  3. 排序陷阱 List.Sort Linq.OrderBy
  4. HDU5127 神坑题---vector 、 list 、 deque 的用法区别
  5. Fix “Windows cannot access the specified device path or file” Error
  6. qt 屏幕旋转
  7. VC++时间函数总结
  8. 更改layout的布局
  9. jQuery中$(function() {});问题详解
  10. Log4net 自定义字段到数据库（二）
  11. （二十）unity4.6得知Ugui中国文献-------另外-InputModules
  12. JAVA学习：异常
  13. vue.js使用props在父子组件之间传参
  14. Build to win
  15. 用Sklearn画一颗决策树
  16. 采用xtrabackup部署主从同步
  17. python3文件的读写操作
  18. Mysql误删表中数据与误删表的恢复方法
  19. ELKstack5.6.5
  20. AJPFX讲解外汇保证金交易的货币符号和外汇的报价方式

热门文章

  1. feign容断忽略某些异常
  2. ECShop怎么首页调用文章列表
  3. ABAP跳转屏幕
  4. <转>Java 高并发综合
  5. IT部门域事件与业务分析
  6. LR脚本中常用函数使用介绍
  7. Windows Experience Index
  8. CentOS7-samba文件共享服务
  9. java基础—java制作证书的工具keytool
  10. Windows平台下MySQL常用操作与命令