如何获得创建控制文件的脚本并重建控制文件
转自 http://www.eygle.com/archives/2004/10/backup_and_recreate_controlfile.html
控制文件对于数据库来说是非常重要的数据结构,在进行数据恢复时通常是必不可少的.
Oracle提供两种方式备份控制文件:
1.生成可以重建控制文件的脚本
2.备份二进制的控制文件
我们看一下如何获得可以重建控制文件的脚本.
Oracle提供如下命令:
alter database backup controlfile to trace;
实际操作:
[oracle@standby tools]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Sat Oct 16 08:56:13 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning option JServer Release 9.2.0.4.0 - Production SQL> alter database backup controlfile to trace; Database altered. SQL> @gettrcname TRACE_FILE_NAME -------------------------------------------------------------------------------- /opt/oracle/admin/primary/udump/primary_ora_2135.trc
trace文件内容:
[oracle@standby tools]$ more /opt/oracle/admin/primary/udump/primary_ora_2135.trc /opt/oracle/admin/primary/udump/primary_ora_2135.trc Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning option JServer Release 9.2.0.4.0 - Production ORACLE_HOME = /opt/oracle/product/9.2.0 System name: Linux Node name: standby Release: 2.4.21-4.EL Version: #1 Fri Oct 3 18:13:58 EDT 2003 Machine: i686 Instance name: primary Redo thread mounted by this instance: 1 Oracle process number: 12 Unix process pid: 2135, image: oracle@standby (TNS V1-V3) *** SESSION ID:(11.6) 2004-10-16 09:00:03.830 *** 2004-10-16 09:00:03.830 # 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.dbf # REMOTE_ARCHIVE_ENABLE=TRUE # LOG_ARCHIVE_START=TRUE # LOG_ARCHIVE_MAX_PROCESSES=2 # STANDBY_FILE_MANAGEMENT=MANUAL # STANDBY_ARCHIVE_DEST=?/dbs/arch # FAL_CLIENT='' # FAL_SERVER='' # # LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/oradata/primary/archive' # LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY' # LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM SYNC' # LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY' # LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED' # 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 the recovery manager will be lost. Additional logs may # be required for media recovery of offline data files. Use this # only if the current version of all online logs are available. STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "PRIMARY" NORESETLOGS ARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE MAXLOGFILES 5 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE 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 LOGFILE DATAFILE '/opt/oracle/oradata/primary/system01.dbf', '/opt/oracle/oradata/primary/undotbs01.dbf', '/opt/oracle/oradata/primary/users01.dbf' CHARACTER SET ZHS16GBK ; # 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 '/opt/oracle/oradata/primary/temp01.dbf' SIZE 41943040 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. # The contents of online logs will be lost and all backups will # be invalidated. Use this only if online logs are damaged. STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "PRIMARY" RESETLOGS ARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE MAXLOGFILES 5 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE 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 LOGFILE DATAFILE '/opt/oracle/oradata/primary/system01.dbf', '/opt/oracle/oradata/primary/undotbs01.dbf', '/opt/oracle/oradata/primary/users01.dbf' CHARACTER SET ZHS16GBK ; # 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 '/opt/oracle/oradata/primary/temp01.dbf' SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; # End of tempfile additions. #
编辑这个trace文件,我们就可以获得创建控制文件的脚本.
根据数据库不同状况,你可以选择是使用RESETLOGS/NORESETLOGS来重建控制文件.
我们获得以下脚本:
[oracle@standby tools]$ cat createctlf.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PRIMARY" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
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 LOGFILE
DATAFILE
'/opt/oracle/oradata/primary/system01.dbf',
'/opt/oracle/oradata/primary/undotbs01.dbf',
'/opt/oracle/oradata/primary/users01.dbf'
CHARACTER SET ZHS16GBK
;
RECOVER DATABASE
ALTER 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 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> set echo on
SQL> @createctlf
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> 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 DATABASE
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> 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>
以上给出生成创建控制文件脚本并重建控制文件的方法,但是具体恢复中遇到的问题可能需要具体对待.
这种方法通常是在没有控制文件(二进制文件)备份的情况下所采用的,如果存在备份应该使用备份的控制文件尝试恢复.
提示:
alter database backup controlfile to trace; 会把脚本生产到当前user的trace文件中.
USER_DUMP_DEST/SID_ORA_PROCESSID.
1. show parameter user_dump_dest; 找到目录。
2. show parameter instance_name;找到SID.
2. 用下面的SQL 找到进程ID
select p.pid,p.spid,p.username from v$process p left outer join v$session s on p.addr = s.paddr where s.sid = (select sid from v$mystat where rownum=1);