无备份重建控制文件

目录

1    控制文件模板

2    无备份重建控制文件示例

2.1    查看控制文件信息

2.2    删除控制文件

2.3    删除控制文件可以切换日志和做检查点更新

2.4    关闭和重启数据库报错

2.5    重建控制文件（丢失所有控制文件，且无备份）

2.5.1    获取DBNAME

2.5.2    RESETLOGS和NORESETLOGS选择

2.5.3    是否归档

2.5.4    数据文件路径

2.5.5    获取字符集

2.5.6    重建控制文件

2.6    手工注册归档日志

2.7    恢复数据库

2.8    非当前日志归档

2.9    打开数据库

2.10    创建临时文件

---

1    控制文件模板

SQL> alter database backup controlfile to trace as '/tmp/control_bak.ctl';

Database altered.

[oracle@dblab tmp]$more /tmp/control_bak.ctl 
-- 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="epmdss"
--
-- 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=/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch'
-- 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 "EPMDSS" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/epmdss/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/epmdss/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/epmdss/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/epmdss/system01.dbf',
  '/u01/app/oracle/oradata/epmdss/sysaux01.dbf',
  '/u01/app/oracle/oradata/epmdss/undotbs01.dbf',
  '/u01/app/oracle/oradata/epmdss/users01.dbf',
  '/u01/app/oracle/oradata/epmdss/dssdat01.dbf',
  '/u01/app/oracle/oradata/epmdss/dssdat02.dbf',
  '/u01/app/oracle/oradata/epmdss/dssdat03.dbf',
  '/u01/app/oracle/oradata/epmdss/dssidx01.dbf',
  '/u01/app/oracle/oradata/epmdss/dssidx02.dbf',
  '/u01/app/oracle/oradata/epmdss/dssidx03.dbf'
CHARACTER SET ZHS16GBK
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');
-- 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/app/oracle/product/11.2.0.4/db_1/dbs/arch1_1_1035249887.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_1_1035386059.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- 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/app/oracle/oradata/epmdss/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- 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 "EPMDSS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/epmdss/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/epmdss/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/epmdss/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/epmdss/system01.dbf',
  '/u01/app/oracle/oradata/epmdss/sysaux01.dbf',
  '/u01/app/oracle/oradata/epmdss/undotbs01.dbf',
  '/u01/app/oracle/oradata/epmdss/users01.dbf',
  '/u01/app/oracle/oradata/epmdss/dssdat01.dbf',
  '/u01/app/oracle/oradata/epmdss/dssdat02.dbf',
  '/u01/app/oracle/oradata/epmdss/dssdat03.dbf',
  '/u01/app/oracle/oradata/epmdss/dssidx01.dbf',
  '/u01/app/oracle/oradata/epmdss/dssidx02.dbf',
  '/u01/app/oracle/oradata/epmdss/dssidx03.dbf'
CHARACTER SET ZHS16GBK
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');
-- 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/app/oracle/product/11.2.0.4/db_1/dbs/arch1_1_1035249887.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_1_1035386059.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;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/epmdss/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--

2    无备份重建控制文件示例

2.1    查看控制文件信息

SQL> select BANNER 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

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/epmdss
                                                 /control01.ctl, /u01/app/oracl
                                                 e/oradata/epmdss/control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING

2.2    删除控制文件

$ cd /u01/app/oracle/oradata/epmdss
$ rm -f control0*

2.3    删除控制文件可以切换日志和做检查点更新

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

SQL> alter system checkpoint;

System altered.

2.4    关闭和重启数据库报错

SQL> shutdown immediate
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/epmdss/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> startup force
ORACLE instance started.

Total System Global Area 2421825536 bytes
Fixed Size                  2255632 bytes
Variable Size             889193712 bytes
Database Buffers         1526726656 bytes
Redo Buffers                3649536 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> select status from v$instance;

STATUS
------------
STARTED

2.5    重建控制文件（丢失所有控制文件，且无备份）

SQL> 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/CDB18/system01.dbf'

SQL> recover database;
Media recovery complete.
SQL> alter database open;     

Database altered.

2.5.1    获取DBNAME

2.5.1.1    从初始化参数文件中获取

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      epmdss

2.5.1.2    从数据文件头中获取

BBED> set filename '/u01/app/oracle/oradata/epmdss/system01.dbf';
        FILENAME        /u01/app/oracle/oradata/epmdss/system01.dbf

BBED> map
 File: /u01/app/oracle/oradata/epmdss/system01.dbf (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 860 bytes                    @0       

 ub4 tailchk                                @8188    

BBED> p kcvfh
struct kcvfh, 860 bytes                     @0       
   struct kcvfhbfh, 20 bytes                @0       
      ub1 type_kcbh                         @0        0x0b
      ub1 frmt_kcbh                         @1        0xa2
      ub1 spare1_kcbh                       @2        0x00
      ub1 spare2_kcbh                       @3        0x00
      ub4 rdba_kcbh                         @4        0x00400001
      ub4 bas_kcbh                          @8        0x00000000
      ub2 wrp_kcbh                          @12       0x0000
      ub1 seq_kcbh                          @14       0x01
      ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
      ub2 chkval_kcbh                       @16       0x5818
      ub2 spare3_kcbh                       @18       0x0000
   struct kcvfhhdr, 76 bytes                @20      
      ub4 kccfhswv                          @20       0x00000000
      ub4 kccfhcvn                          @24       0x0b200400
      ub4 kccfhdbi                          @28       0x04ebb29f
      text kccfhdbn[0]                      @32      E
      text kccfhdbn[1]                      @33      P
      text kccfhdbn[2]                      @34      M
      text kccfhdbn[3]                      @35      D
      text kccfhdbn[4]                      @36      S
      text kccfhdbn[5]                      @37      S
      text kccfhdbn[6]                      @38       
      text kccfhdbn[7]                      @39

      
2.5.2    RESETLOGS和NORESETLOGS选择

在线重做日志丢失则用RESETLOGS，全部未丢失用NORESETLOGS。

2.5.3    是否归档

可以查看log_archive_dest_1参数设置，到路径下查看是否有归档日志。
可能存放归档日志路径：$ORACLE_HOME/dbs

2.5.4    数据文件路径

到相关路径查看数据文件，如果漏掉了某些数据文件，open数据库后只能使用非常规手段恢复使数据文件online。（最好先做个全备）

2.5.5    获取字符集

props$表中存在字符集信息，11g中是在1号文件801号块。12c在1号文件1321号块。可以通过下面语句获取（当前数据库是nomount状态查看不了）
select distinct dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from props$;
使用dd获取字符集
$ dd if=system01.dbf of=/tmp/dbzfj bs=8192 skip=801 count=1
$ strings /tmp/dbzfj |more
NLS_CHARACTERSET
ZHS16GBK
Character set

2.5.6    重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "EPMDSS" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/oradata/epmdss/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oracle/oradata/epmdss/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oracle/oradata/epmdss/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oracle/oradata/epmdss/system01.dbf',
 14    '/u01/app/oracle/oradata/epmdss/sysaux01.dbf',
 15    '/u01/app/oracle/oradata/epmdss/undotbs01.dbf',
 16    '/u01/app/oracle/oradata/epmdss/users01.dbf',
 17    '/u01/app/oracle/oradata/epmdss/dssdat01.dbf',
 18    '/u01/app/oracle/oradata/epmdss/dssdat02.dbf',
 19    '/u01/app/oracle/oradata/epmdss/dssdat03.dbf',
 20    '/u01/app/oracle/oradata/epmdss/dssidx01.dbf',
 21    '/u01/app/oracle/oradata/epmdss/dssidx02.dbf',
 22    '/u01/app/oracle/oradata/epmdss/dssidx03.dbf'
 23  CHARACTER SET ZHS16GBK
 24  ;

Control file created.

2.6    手工注册归档日志

2.6.1.1    少量日志注册

生成批量注册脚本
ls -lrt |awk '{print "alter database register physical logfile  \047/oracle/oradata/"$9"\047; "}'>regsql.sql
SQL> select * from v$archived_log;

no rows selected

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_1_1035386059.dbf';

Database altered.
SQL> select sequence#,applied,name from v$archived_log;

 SEQUENCE# APPLIED
---------- ---------
NAME
--------------------------------------------------------------------------------
         1 NO
/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_1_1035386059.dbf

ALTER DATABASE REGISTER PHYSICAL LOGFILE  '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_1_1035386059.dbf'; 
ALTER DATABASE REGISTER PHYSICAL LOGFILE  '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_2_1035386059.dbf'; 
ALTER DATABASE REGISTER PHYSICAL LOGFILE  '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_3_1035386059.dbf'; 
ALTER DATABASE REGISTER PHYSICAL LOGFILE  '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_4_1035386059.dbf'; 
ALTER DATABASE REGISTER PHYSICAL LOGFILE  '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_5_1035386059.dbf'; 
ALTER DATABASE REGISTER PHYSICAL LOGFILE  '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_6_1035386059.dbf'; 
ALTER DATABASE REGISTER PHYSICAL LOGFILE  '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_7_1035386059.dbf'; 
ALTER DATABASE REGISTER PHYSICAL LOGFILE  '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_8_1035386059.dbf'; 
ALTER DATABASE REGISTER PHYSICAL LOGFILE  '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_9_1035386059.dbf'; 
ALTER DATABASE REGISTER PHYSICAL LOGFILE  '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_10_1035386059.dbf';

2.6.1.2    大量日志注册

rman> catalog start with '/u01/archlog/';

2.7    恢复数据库

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

2.8    非当前日志归档

SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.

2.9    打开数据库

SQL> alter database open;
Database altered.

2.10    创建临时文件

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/epmdss/temp01.dbf'
            SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.