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

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

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 40M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

利用trace重建控制檔案

相關文章