oracle之 利用 controlfile trace檔案重建控制檔案

標籤：ada   users   his   over   tab   auto   and   complete   versions   

一、 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檔案重建控制檔案