oracle增加control文件,oracle之 利用 controlfile trace文件重建控制文件

一、 11g RAC 重建控制文件

1、 --"create controlfile"命令生成到追踪文件中：

alter database backup controlfile to trace;

2、 --确认追踪文件的路径：

SQL> select value from v$diag_info where name=‘Default Trace File‘;

3、 -- 截取脚本 在追踪文件中找到并执行NORESETLOGS版本的"create controlfile"命令 至 End of tempfile additions.

如下：

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

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 2560

MAXINSTANCES 32

MAXLOGHISTORY 292

LOGFILE

GROUP 1 (

‘+DATA01/yndss/onlinelog/group_1.257.954170283‘,

‘+FRA/yndss/onlinelog/group_1.257.954170289‘

) SIZE 4096M BLOCKSIZE 512,

GROUP 2 (

‘+DATA01/yndss/onlinelog/group_2.258.954170295‘,

‘+FRA/yndss/onlinelog/group_2.258.954170301‘

) SIZE 4096M BLOCKSIZE 512,

GROUP 3 (

‘+DATA01/yndss/onlinelog/group_3.259.954170307‘,

‘+FRA/yndss/onlinelog/group_3.259.954170313‘

) SIZE 4096M BLOCKSIZE 512,

GROUP 4 (

‘+DATA01/yndss/onlinelog/group_4.260.954170317‘,

‘+FRA/yndss/onlinelog/group_4.260.954170323‘

) SIZE 4096M BLOCKSIZE 512,

GROUP 5 (

‘+DATA01/yndss/onlinelog/group_5.267.954172379‘,

‘+FRA/yndss/onlinelog/group_5.261.954172383‘

) SIZE 4096M BLOCKSIZE 512,

GROUP 6 (

‘+DATA01/yndss/onlinelog/group_6.268.954172389‘,

‘+FRA/yndss/onlinelog/group_6.262.954172395‘

) SIZE 4096M BLOCKSIZE 512,

GROUP 7 (

‘+DATA01/yndss/onlinelog/group_7.269.954172399‘,

‘+FRA/yndss/onlinelog/group_7.263.954172405‘

) SIZE 4096M BLOCKSIZE 512,

GROUP 8 (

‘+DATA01/yndss/onlinelog/group_8.270.954172411‘,

‘+FRA/yndss/onlinelog/group_8.264.954172417‘

) SIZE 4096M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

‘+DATA01/yndss/datafile/system.261.954170329‘,

‘+DATA01/yndss/datafile/sysaux.262.954170337‘,

‘+DATA01/yndss/datafile/undotbs1.263.954170349‘,

‘+DATA01/yndss/datafile/undotbs2.265.954170389‘,

‘+DATA01/yndss/datafile/users.266.954170413‘,

‘+DATA01/yndss/datafile/etl001.dbf‘,

‘+DATA01/yndss/datafile/src001.dbf‘,

‘+DATA01/yndss/datafile/cdr001.dbf‘,

‘+DATA01/yndss/datafile/day001.dbf‘,

‘+DATA01/yndss/datafile/mon001.dbf‘,

‘+DATA01/yndss/datafile/detail001.dbf‘,

‘+DATA01/yndss/datafile/code001.dbf‘,

‘+DATA01/yndss/datafile/dss001.dbf‘,

‘+DATA01/yndss/datafile/dm001.dbf‘,

‘+DATA01/yndss/datafile/rpt001.dbf‘,

‘+DATA01/yndss/datafile/undotbs1.292.954498057‘,

‘+DATA01/yndss/datafile/undotbs2.293.954498139‘,

‘+DATA01/yndss/datafile/day002‘,

‘+DATA01/yndss/datafile/day004‘,

‘+DATA01/yndss/datafile/day005‘,

‘+DATA01/yndss/datafile/day006‘,

‘+DATA01/yndss/datafile/day007‘,

‘+DATA01/yndss/datafile/day008‘,

‘+DATA01/yndss/datafile/day009‘,

‘+DATA01/yndss/datafile/day010‘,

‘+DATA01/yndss/datafile/day011‘,

‘+DATA01/yndss/datafile/day012‘,

‘+DATA01/yndss/datafile/day013‘,

‘+DATA01/yndss/datafile/day014‘,

‘+DATA01/yndss/datafile/day015‘,

‘+DATA01/yndss/datafile/day016‘,

‘+DATA01/yndss/datafile/day017‘,

‘+DATA01/yndss/datafile/day018‘,

‘+DATA01/yndss/datafile/day019‘,

‘+DATA01/yndss/datafile/day020‘,

‘+DATA01/yndss/datafile/day021‘,

‘+DATA01/yndss/datafile/day022‘,

‘+DATA01/yndss/datafile/day023‘

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

-- 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 ‘+DATA01/yndss/tempfile/temp.264.954170377‘

SIZE 20480M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 131071M;

ALTER TABLESPACE TEMP01 ADD TEMPFILE ‘+DATA01/yndss/tempfile/temp01.272.954431201‘

SIZE 30720M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP01 ADD TEMPFILE ‘+DATA01/yndss/tempfile/temp01.273.954431229‘

SIZE 30720M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP01 ADD TEMPFILE ‘+DATA01/yndss/tempfile/temp01.274.954431245‘

SIZE 30720M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP01 ADD TEMPFILE ‘+DATA01/yndss/tempfile/temp01.275.954431255‘

SIZE 30720M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP01 ADD TEMPFILE ‘+DATA01/yndss/tempfile/temp01.276.954431265‘

SIZE 30720M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP02 ADD TEMPFILE ‘+DATA01/yndss/tempfile/temp02.277.954431307‘

SIZE 30720M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP02 ADD TEMPFILE ‘+DATA01/yndss/tempfile/temp02.278.954431321‘

SIZE 30720M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP02 ADD TEMPFILE ‘+DATA01/yndss/tempfile/temp02.279.954431347‘

SIZE 30720M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP02 ADD TEMPFILE ‘+DATA01/yndss/tempfile/temp02.280.954431371‘

SIZE 30720M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP02 ADD TEMPFILE ‘+DATA01/yndss/tempfile/temp02.281.954431381‘

SIZE 30720M REUSE AUTOEXTEND OFF;

-- End of tempfile additions.

说明：REUSE 可以复用数据库本身已经存在的临时文件

4、-- 正式重建 (重建之前，可以 cp 备份控制文件)

alter system set cluster_database=false scope=spfile sid=‘*‘;

@脚本

alter system set cluster_database=true scope=spfile sid=‘*‘;

说明：如果环境是集群的话，需要调整 cluster_database 参数。

——————————————————————————————

二、 12c 重建控制文件

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ‘/home/oracle/app/oradata/orcl/redo01.log‘ SIZE 50M BLOCKSIZE 512,

GROUP 2 ‘/home/oracle/app/oradata/orcl/redo02.log‘ SIZE 50M BLOCKSIZE 512,

GROUP 3 ‘/home/oracle/app/oradata/orcl/redo03.log‘ SIZE 50M BLOCKSIZE 512

DATAFILE

‘/home/oracle/app/oradata/orcl/system01.dbf‘,

‘/home/oracle/app/oradata/orcl/sysaux01.dbf‘,

‘/home/oracle/app/oradata/orcl/undotbs01.dbf‘,

‘/home/oracle/app/oradata/orcl/pdbseed/system01.dbf‘,

‘/home/oracle/app/oradata/orcl/users01.dbf‘,

‘/home/oracle/app/oradata/orcl/pdbseed/sysaux01.dbf‘,

‘/home/oracle/app/oradata/orcl/pdb/system01.dbf‘,

‘/home/oracle/app/oradata/orcl/pdb/sysaux01.dbf‘,

‘/home/oracle/app/oradata/orcl/pdb/pdb_users01.dbf‘

CHARACTER SET ZHS16GBK

;

RECOVER DATABASE

ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE ALL OPEN;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/home/oracle/app/oradata/orcl/temp01.dbf‘

SIZE 92274688 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

ALTER SESSION SET CONTAINER = PDB$SEED;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/home/oracle/app/oradata/orcl/pdbseed/pdbseed_temp01.dbf‘

SIZE 91226112 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

ALTER SESSION SET CONTAINER = PDB;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/home/oracle/app/oradata/orcl/pdb/temp01.dbf‘

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

ALTER SESSION SET CONTAINER = CDB$ROOT;

——————————————————————————————

补充：

如有需要，利用strings命令找出来数据文件和联机日志路径

oracle之 利用 controlfile trace文件重建控制文件

标签：ada   users   his   over   tab   auto   and   complete   versions

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/andy6/p/7509519.html