分类：
2008-11-26 13:36:14
控制文件对于数据库来说是非常重要的数据结构，在进行数据恢复时通常是必不可少的.
Oracle提供两种方式备份控制文件:
1.生成可以重建控制文件的脚本
2.备份二进制的控制文件
备份控制文件到/export/home/oracle目录下面
SQL> alter database backup controlfile to trace as '/export/home/oracle/ctl.sql';
SQL> !more /export/home/oracle/ctl.sql
# 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 "DBTEST" NORESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/dev/rdsk/c1t10d0s1' SIZE 50M,
GROUP 2 '/dev/rdsk/c1t10d0s3' SIZE 50M,
GROUP 3 '/dev/rdsk/c1t10d0s4' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/export/home/oracle/oradata/dbtest/system01.dbf',
'/export/home/oracle/oradata/dbtest/undotbs01.dbf',
'/export/home/oracle/oradata/dbtest/drsys01.dbf',
'/export/home/oracle/oradata/dbtest/indx01.dbf',
'/export/home/oracle/oradata/dbtest/tools01.dbf',
'/export/home/oracle/oradata/dbtest/users01.dbf',
'/export/home/oracle/oradata/dbtest/xdb01.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
# 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 '/export/home/oracle/oradata/dbtest/temp01.dbf' REUSE;
# 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 "DBTEST" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/dev/rdsk/c1t10d0s1' SIZE 50M,
GROUP 2 '/dev/rdsk/c1t10d0s3' SIZE 50M,
GROUP 3 '/dev/rdsk/c1t10d0s4' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/export/home/oracle/oradata/dbtest/system01.dbf',
'/export/home/oracle/oradata/dbtest/undotbs01.dbf',
'/export/home/oracle/oradata/dbtest/drsys01.dbf',
'/export/home/oracle/oradata/dbtest/indx01.dbf',
'/export/home/oracle/oradata/dbtest/tools01.dbf',
'/export/home/oracle/oradata/dbtest/users01.dbf',
'/export/home/oracle/oradata/dbtest/xdb01.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 '/export/home/oracle/oradata/dbtest/temp01.dbf' REUSE;
# End of tempfile additions.
#
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 320308312 bytes
Fixed Size 730200 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 811008 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "DBTEST" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 5
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 1
6 MAXLOGHISTORY 226
7 LOGFILE
8 GROUP 1 '/dev/rdsk/c1t11d0s1' SIZE 50M,
9 GROUP 2 '/dev/rdsk/c1t11d0s3' SIZE 50M,
10 GROUP 3 '/dev/rdsk/c1t11d0s4' SIZE 50M
11 DATAFILE
12 '/export/home/oracle/oradata/dbtest/system01.dbf',
13 '/export/home/oracle/oradata/dbtest/undotbs01.dbf',
14 '/export/home/oracle/oradata/dbtest/drsys01.dbf',
15 '/export/home/oracle/oradata/dbtest/indx01.dbf',
16 '/export/home/oracle/oradata/dbtest/tools01.dbf',
17 '/export/home/oracle/oradata/dbtest/users01.dbf',
18 '/export/home/oracle/oradata/dbtest/xdb01.dbf'
19 CHARACTER SET ZHS16GBK
20 ;
Control file created.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> arhive log list;
SP2-0734: unknown command beginning "arhive log..." - rest of line ignored.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /export/home/oracle/product/9.2.0/dbs/arch
Oldest online log sequence 0
Current log sequence 1
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
3 ONLINE
/dev/rdsk/c1t11d0s4
2 ONLINE
/dev/rdsk/c1t11d0s3
1 ONLINE
/dev/rdsk/c1t11d0s1
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
1 1 0 52428800 1 YES UNUSED
0
2 1 0 52428800 1 YES UNUSED
0
3 1 1 52428800 1 NO CURRENT
185991 29-4? -08
