Rebuild control file(重建控制文件)

可通过重建控制文件的方式进行数据库物理迁移

这个过程需要在源库上生成重建控制文件的脚本,根据目标库设置,对脚本进行修改后可copy至目标库并重建控制文件,整个过程非常快。但是如果升级数据库的话要执行多个脚本,catupgrd.sql脚本执行时间尤其长

下面是生成

SQL> Alter database backup controlfile to trace;

数据库已更改。

 

SQL> @gettrcname
 
TRACE_FILE_NAME 
-------------------------------------------------------------------------------- 
h:\app\marshall\diag\rdbms\orcl\orcl\trace/orcl_ora_5692.trc

 

下面是trace文件内容:

 

Trace file h:\app\marshall\diag\rdbms\orcl\orcl\trace\orcl_ora_5692.trc 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
Windows NT Version V6.1 
CPU : 2 - type 586, 2 Physical Cores 
Process Affinity : 0x0x00000000 
Memory (Avail/Total): Ph:567M/2046M, Ph+PgF:1048M/4092M, VA:824M/2047M 
Instance name: orcl 
Redo thread mounted by this instance: 1 
Oracle process number: 25 
Windows thread id: 5692, image: ORACLE.EXE (SHAD)
 
*** 2011-05-03 12:16:06.677 
*** SESSION ID:(145.40) 2011-05-03 12:16:06.677 
*** CLIENT ID:() 2011-05-03 12:16:06.677 
*** SERVICE NAME:(SYS$USERS) 2011-05-03 12:16:06.677 
*** MODULE NAME:(sqlplus.exe) 2011-05-03 12:16:06.677 
*** ACTION NAME:() 2011-05-03 12:16:06.677
 
-- 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=ARC%S_%R.%T 
-- 
-- DB_UNIQUE_NAME="orcl" 
-- 
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG' 
-- LOG_ARCHIVE_MAX_PROCESSES=4 
-- STANDBY_FILE_MANAGEMENT=MANUAL 
-- STANDBY_ARCHIVE_DEST=%ORACLE_HOME%\RDBMS 
-- FAL_CLIENT='' 
-- FAL_SERVER='' 
-- 
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' 
-- 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 "ORCL" NORESETLOGS NOARCHIVELOG 
MAXLOGFILES 16 
MAXLOGMEMBERS 3 
MAXDATAFILES 100 
MAXINSTANCES 8 
MAXLOGHISTORY 292 
LOGFILE 
GROUP 1 'H:\APP\MARSHALL\ORADATA\ORCL\REDO01.LOG' SIZE 50M BLOCKSIZE 512, 
GROUP 2 'H:\APP\MARSHALL\ORADATA\ORCL\REDO02.LOG' SIZE 50M BLOCKSIZE 512, 
GROUP 3 'H:\APP\MARSHALL\ORADATA\ORCL\REDO03.LOG' SIZE 50M BLOCKSIZE 512 
-- STANDBY LOGFILE 
DATAFILE 
'H:\APP\MARSHALL\ORADATA\ORCL\SYSTEM01.DBF', 
'H:\APP\MARSHALL\ORADATA\ORCL\SYSAUX01.DBF', 
'H:\APP\MARSHALL\ORADATA\ORCL\UNDOTBS01.DBF', 
'H:\APP\MARSHALL\ORADATA\ORCL\USERS01.DBF', 
'H:\APP\MARSHALL\ORADATA\ORCL\EXAMPLE01.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 'H:\APP\MARSHALL\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2011_05_03\O1_MF_1_1_%U_.ARC'; 
-- ALTER DATABASE REGISTER LOGFILE 'H:\APP\MARSHALL\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2011_05_03\O1_MF_1_1_%U_.ARC'; 
-- 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 'H:\APP\MARSHALL\ORADATA\ORCL\TEMP01.DBF' 
SIZE 36700160 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 NOARCHIVELOG 
MAXLOGFILES 16 
MAXLOGMEMBERS 3 
MAXDATAFILES 100 
MAXINSTANCES 8 
MAXLOGHISTORY 292 
LOGFILE 
GROUP 1 'H:\APP\MARSHALL\ORADATA\ORCL\REDO01.LOG' SIZE 50M BLOCKSIZE 512, 
GROUP 2 'H:\APP\MARSHALL\ORADATA\ORCL\REDO02.LOG' SIZE 50M BLOCKSIZE 512, 
GROUP 3 'H:\APP\MARSHALL\ORADATA\ORCL\REDO03.LOG' SIZE 50M BLOCKSIZE 512 
-- STANDBY LOGFILE 
DATAFILE 
'H:\APP\MARSHALL\ORADATA\ORCL\SYSTEM01.DBF', 
'H:\APP\MARSHALL\ORADATA\ORCL\SYSAUX01.DBF', 
'H:\APP\MARSHALL\ORADATA\ORCL\UNDOTBS01.DBF', 
'H:\APP\MARSHALL\ORADATA\ORCL\USERS01.DBF', 
'H:\APP\MARSHALL\ORADATA\ORCL\EXAMPLE01.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 'H:\APP\MARSHALL\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2011_05_03\O1_MF_1_1_%U_.ARC'; 
-- ALTER DATABASE REGISTER LOGFILE 'H:\APP\MARSHALL\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2011_05_03\O1_MF_1_1_%U_.ARC'; 
-- 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 'H:\APP\MARSHALL\ORADATA\ORCL\TEMP01.DBF' 
SIZE 36700160 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; 
-- End of tempfile additions.

上面涉及到RESETLOGS和NORESETLOGS两种方式,可通过这两种方式重建控制文件。我在迁移过程中使用的是NORESETLOGS方式,REDO的sequence不会清零,这跟incarnation有关,我得学习下。

参考资料:如何获得创建控制文件的脚本并重建控制文件 http://www.eygle.com/archives/2004/10/backup_and_recreate_controlfile.html Eygle(盖国强)大师

  1. 不论是生成重建控制文件的脚本也好还是导出控制文件也罢,如果没有gettraname脚本,就找不到这些个trace文件了么?
    上面两篇文章生成的trace脚本都指向同一个位置:
    h:\app\marshall\diag\rdbms\orcl\orcl\trace/

    SQL> show parameter dump

    NAME TYPE VALUE
    ———————————— ———– ——————————
    background_core_dump string partial
    background_dump_dest string h:\app\marshall\diag\rdbms\orc
    l\orcl\trace
    core_dump_dest string h:\app\marshall\diag\rdbms\orc
    l\orcl\cdump
    max_dump_file_size string unlimited
    shadow_core_dump string none
    user_dump_dest string h:\app\marshall\diag\rdbms\orc
    l\orcl\trace
    应该是user_dump_dest或是background_dump_dest之一,记录以之,再研究

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress