alter database backup controlfile to trace创建控制文件脚本，重建控制文件

创建控制文件脚本，重建控制文件

alter database backup controlfile to trace

1、alter session set tracefile_identifier='bak_control';【为session加上标记，方便查找trace文件】

2、alter database backup controlfile to trace;【将当前controlfile文件的创建语句放到了trace文件中】

3、select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));【获得当前trace文件生成路径】

cd /u02/app/oracle/diag/rdbms/orcl/orcl1/trace/

ls -lt *bak_control*

4、cat orcl1_ora_28738_bak_control.trc【查看一下生成的trace文件】

[oracle@rac1 trace]$ cat orcl1_ora_28738_bak_control.trc

Trace file /u02/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_28738_bak_control.trc

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

ORACLE_HOME = /u02/app/oracle/product/11.2.0/db_home

System name: Linux

Node name: rac1

Release: 2.6.32-431.el6.x86_64

Version: #1 SMP Sun Nov 10 22:19:54 EST 2013

Machine: x86_64

Instance name: orcl1

Redo thread mounted by this instance: 1

Oracle process number: 41

Unix process pid: 28738, image: oracle@rac1 (TNS V1-V3)

*** 2016-10-25 09:34:16.954

*** SESSION ID:(1168.259) 2016-10-25 09:34:16.954

*** CLIENT ID:() 2016-10-25 09:34:16.954

*** SERVICE NAME:(SYS$USERS) 2016-10-25 09:34:16.954

*** MODULE NAME:(sqlplus@rac1 (TNS V1-V3)) 2016-10-25 09:34:16.954

*** ACTION NAME:() 2016-10-25 09:34:16.954

-- 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="orcl"

--

-- 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=+FRA'

-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'

-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_1='REGISTER 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 "ORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 32

MAXLOGHISTORY 292

*** 2016-10-25 09:34:17.101

LOGFILE

GROUP 1 (

'+DATA/orcl/onlinelog/group_1.262.925580767',

'+DATA/orcl/onlinelog/group_1.263.925580823',

'+FRA/orcl/onlinelog/group_1.257.925580877'

) SIZE 256M BLOCKSIZE 512,

GROUP 2 (

'+DATA/orcl/onlinelog/group_2.264.925580933',

'+DATA/orcl/onlinelog/group_2.265.925580987',

'+FRA/orcl/onlinelog/group_2.258.925581043'

) SIZE 256M BLOCKSIZE 512,

GROUP 3 (

'+DATA/orcl/onlinelog/group_3.270.925582185',

'+DATA/orcl/onlinelog/group_3.271.925582261',

'+FRA/orcl/onlinelog/group_3.260.925582315'

) SIZE 256M BLOCKSIZE 512,

GROUP 4 (

'+DATA/orcl/onlinelog/group_4.272.925582373',

'+DATA/orcl/onlinelog/group_4.273.925582429',

'+FRA/orcl/onlinelog/group_4.261.925582487'

) SIZE 256M BLOCKSIZE 512,

GROUP 5 (

'+DATA/orcl/onlinelog/group_5.266.925581101',

'+DATA/orcl/onlinelog/group_5.267.925581157',

'+FRA/orcl/onlinelog/group_5.259.925581211'

) SIZE 256M BLOCKSIZE 512,

GROUP 6 (

'+DATA/orcl/onlinelog/group_6.274.925582545',

'+DATA/orcl/onlinelog/group_6.275.925582599',

'+FRA/orcl/onlinelog/group_6.262.925582659'

) SIZE 256M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'+DATA/orcl/datafile/system.256.925580473',

'+DATA/orcl/datafile/sysaux.257.925580475',

'+DATA/orcl/datafile/undotbs1.258.925580475',

'+DATA/orcl/datafile/users.259.925580475',

'+DATA/orcl/datafile/undotbs2.269.925581587',

'+DATA/orcl/datafile/test1.dbf'

CHARACTER SET ZHS16GBK

;

-- Configure snapshot controlfile filename

EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('+DATA/snapcf_ls.f');

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('SNAPSHOT CONTROLFILE NAME','TO ''+DATA/snapcf_ls.f''');

-- 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 '+FRA';

-- ALTER DATABASE REGISTER LOGFILE '+FRA';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- Set Database Guard and/or Supplemental Logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- 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 '+DATA/orcl/tempfile/temp.268.925581299'

SIZE 35651584 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 "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 32

MAXLOGHISTORY 292

LOGFILE

GROUP 1 (

'+DATA/orcl/onlinelog/group_1.262.925580767',

'+DATA/orcl/onlinelog/group_1.263.925580823',

'+FRA/orcl/onlinelog/group_1.257.925580877'

) SIZE 256M BLOCKSIZE 512,

GROUP 2 (

'+DATA/orcl/onlinelog/group_2.264.925580933',

'+DATA/orcl/onlinelog/group_2.265.925580987',

'+FRA/orcl/onlinelog/group_2.258.925581043'

) SIZE 256M BLOCKSIZE 512,

GROUP 5 (

'+DATA/orcl/onlinelog/group_5.266.925581101',

'+DATA/orcl/onlinelog/group_5.267.925581157',

'+FRA/orcl/onlinelog/group_5.259.925581211'

) SIZE 256M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'+DATA/orcl/datafile/system.256.925580473',

'+DATA/orcl/datafile/sysaux.257.925580475',

'+DATA/orcl/datafile/undotbs1.258.925580475',

'+DATA/orcl/datafile/users.259.925580475',

'+DATA/orcl/datafile/undotbs2.269.925581587',

'+DATA/orcl/datafile/test1.dbf'

CHARACTER SET ZHS16GBK

;

-- Configure snapshot controlfile filename

EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('+DATA/snapcf_ls.f');

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('SNAPSHOT CONTROLFILE NAME','TO ''+DATA/snapcf_ls.f''');

-- 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 '+FRA';

-- ALTER DATABASE REGISTER LOGFILE '+FRA';

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

-- Set Database Guard and/or Supplemental Logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Create log files for threads other than thread one.

ALTER DATABASE ADD LOGFILE THREAD 2

GROUP 3 (

'+DATA/orcl/onlinelog/group_3.270.925582185',

'+DATA/orcl/onlinelog/group_3.271.925582261',

'+FRA/orcl/onlinelog/group_3.260.925582315'

) SIZE 256M BLOCKSIZE 512 REUSE,

GROUP 4 (

'+DATA/orcl/onlinelog/group_4.272.925582373',

'+DATA/orcl/onlinelog/group_4.273.925582429',

'+FRA/orcl/onlinelog/group_4.261.925582487'

) SIZE 256M BLOCKSIZE 512 REUSE,

GROUP 6 (

'+DATA/orcl/onlinelog/group_6.274.925582545',

'+DATA/orcl/onlinelog/group_6.275.925582599',

'+FRA/orcl/onlinelog/group_6.262.925582659'

) SIZE 256M BLOCKSIZE 512 REUSE;

-- 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 '+DATA/orcl/tempfile/temp.268.925581299'

SIZE 35651584 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- End of tempfile additions.

--

5、NORESETLOGS VS RESETLOGS

从trace文件可以看出，提供了两个创建控制文件的脚本，分别针对两种不同的应用场景：NORESETLOGS适用于当前redo log可用，而RESETLOGS适用于当前redo log不可用。

6.1NORESETLOGS重建步骤

1.执行脚本，重建控制文件（CREATE CONTROLFILE REUSE DATABASE "HOEGH"  NORESETLOGS  NOARCHIVELOG......）

2.执行介质恢复 RECOVER DATABASE

3.打开数据库 ALTER DATABASE OPEN;

4.添加临时表空间

6.2RESETLOGS重建步骤

1.执行脚本，重建控制文件（CREATE CONTROLFILE REUSE DATABASE "HOEGH"  RESETLOGS  NOARCHIVELOG......）

2.执行介质恢复 RECOVER DATABASE USING BACKUP CONTROLFILE

3.打开数据库ALTER DATABASE OPEN RESETLOGS;

4.添加临时表空间

下面测试来自网络 ：

[oracle@standby tools]$ cat createctlf.sql STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "PRIMARY" NORESETLOGS ARCHIVELOG-- SET STANDBY TO MAXIMIZE PERFORMANCE MAXLOGFILES 5 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226LOGFILE GROUP 1 '/opt/oracle/oradata/primary/redo01.log' SIZE 10M, GROUP 2 '/opt/oracle/oradata/primary/redo02.log' SIZE 10M, GROUP 3 '/opt/oracle/oradata/primary/redo03.log' SIZE 10M-- STANDBY LOGFILEDATAFILE '/opt/oracle/oradata/primary/system01.dbf', '/opt/oracle/oradata/primary/undotbs01.dbf', '/opt/oracle/oradata/primary/users01.dbf'CHARACTER SET ZHS16GBK;RECOVER DATABASEALTER SYSTEM ARCHIVE LOG ALL;ALTER DATABASE OPEN;ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/primary/temp01.dbf' SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

运行这个脚本即可重建控制文件:

[oracle@standby tools]$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Sat Oct 16 09:20:24 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.SQL> set echo onSQL> @createctlfSQL> STARTUP NOMOUNTORACLE instance started.Total System Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesSQL> CREATE CONTROLFILE REUSE DATABASE "PRIMARY" NORESETLOGS ARCHIVELOG 2 -- SET STANDBY TO MAXIMIZE PERFORMANCE 3 MAXLOGFILES 5 4 MAXLOGMEMBERS 3 5 MAXDATAFILES 100 6 MAXINSTANCES 1 7 MAXLOGHISTORY 226 8 LOGFILE 9 GROUP 1 '/opt/oracle/oradata/primary/redo01.log' SIZE 10M, 10 GROUP 2 '/opt/oracle/oradata/primary/redo02.log' SIZE 10M, 11 GROUP 3 '/opt/oracle/oradata/primary/redo03.log' SIZE 10M 12 -- STANDBY LOGFILE 13 DATAFILE 14 '/opt/oracle/oradata/primary/system01.dbf', 15 '/opt/oracle/oradata/primary/undotbs01.dbf', 16 '/opt/oracle/oradata/primary/users01.dbf' 17 CHARACTER SET ZHS16GBK 18 ;Control file created.SQL> RECOVER DATABASEORA-00283: recovery session canceled due to errorsORA-00264: no recovery requiredSQL> ALTER SYSTEM ARCHIVE LOG ALL;System altered.SQL> ALTER DATABASE OPEN;Database altered.SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/primary/temp01.dbf' 2 SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;Tablespace altered.SQL>

以上给出生成创建控制文件脚本并重建控制文件的方法，但是具体恢复中遇到的问题可能需要具体对待.

这种方法通常是在没有控制文件(二进制文件)备份的情况下所采用的，如果存在备份应该使用备份的控制文件尝试恢复