Oracle提供兩種方式備份控制檔案:
1.生成可以重建控制檔案的指令碼
2.備份二進位制的控制檔案
我們看一下如何獲得可以重建控制檔案的指令碼.
Oracle提供如下命令:
alter database backup controlfile to trace;
實際操作:
[oracle@standby tools]$ sqlplus "/ as sysdba"
SQL> alter database backup controlfile to trace;
Database altered.
SQL> select d.value||b.bias||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from ( select p.spid from sys.v$mystat m,sys.v$session s,sys.v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, ( select t.instance from sys.v$thread t,sys.v$parameter v where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, ( select value from sys.v$parameter where name = 'user_dump_dest') d,(select DECODE(count(BANNER),0,'/','\') bias from v$version where upper(banner) like '%WINDOWS%') b;
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: (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;
|