首页 > 代码库 > Oracle 12c 12.1.0.1.0管理控制文件官方文档翻译说明
Oracle 12c 12.1.0.1.0管理控制文件官方文档翻译说明
Link: http://download.csdn.net/detail/rlhua/7718571
官方Link：
http://docs.oracle.com/database/121/ADMIN/control.htm#ADMIN006
版本：
SYS@db12c>select * from v$version;
BANNER CON_ID
--------------------------------------------------------------------------------------------------------- -------------------
OracleDatabase 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQLRelease 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNSfor Linux: Version 12.1.0.1.0 - Production 0
NLSRTLVersion 12.1.0.1.0 - Production 0
0 ManagingControl Files管理控制文件
This chapter contains the following topics:
这章包含以下内容：
· WhatIs a Control File?
什么是控制文件
· Guidelinesfor Control Files
控制文件指南
· CreatingControl Files
创建控制文件
· TroubleshootingAfter Creating Control Files
创建控制文件后的故障排除
· BackingUp Control Files
备份控制文件
· Recoveringa Control File Using a Current Copy
使用当前副本恢复控制文件
· DroppingControl Files
· 删除控制文件
· ControlFiles Data Dictionary Views
· 控制文件数据字典视图
See Also:
o Oracle Database Concepts for an overview of control files
o Chapter17, "Using Oracle Managed Files" for information about creating control files that are bothcreated and managed by the Oracle Database server
1 What Is a Control File?什么是控制文件
EveryOracle Database has a control file, which is a small binary file thatrecords the physical structure of the database. The control file includes:
每个oracle数据库都有控制文件，一个小的二进制文件记录着数据库的物理结构，控制文件包括以下：
· The database name
数据库名
· Names and locationsof associated data files and redo log files
相关联的数据文件和重做日志文件的名称和位置
· The timestamp of thedatabase creation
数据库创建的时间戳
· The current logsequence number
目前日志的序列
· Checkpointinformation
检查点信息
The control file must be available for writing by the OracleDatabase server whenever the database is open. Without the control file, the databasecannot be mounted and recovery is difficult.
当数据库为打开状态时，oracle数据库服务器对控制文件是可写的，没有控制文件，数据库不能装载，恢复也是一个很难的事情。
The control file of an Oracle Database is created at the sametime as the database. By default, at least one copy of the control file is createdduring database creation. On some operating systems the default is to createmultiple copies. You should create two or more copies of the control fileduring database creation. You can also create control files later, if you losecontrol files or want to change particular settings in the control files.
oracle数据库的控制文件在数据库创建的同一时间创建，默认的，当数据库创建后至少有一个控制文件也会被创建，在一些操作系统中，默认是创建多个副本，你应该在数据库创建时同时创建两个或者更多的控制文件副本，如果你丢失了控制文件或者想改变控制文件的特别的设置，你也可以稍后创建控制文件。
2 Guidelines for Control Files控制文件指南
Thissection describes guidelines you can use to manage the control files for adatabase, and contains the following topics:
这个部分主要说明如何管理数据库的控制文件，包含以下部分：
· ProvideFilenames for the Control Files
提供控制文件的文件名
· MultiplexControl Files on Different Disks
在不同磁盘上的多路复用控制文件
· BackUp Control Files
备份控制文件
· Managethe Size of Control Files
管理控制文件的大小
2.1 Provide Filenames forthe Control Files提供控制文件的文件名
You specify controlfile names using the CONTROL_FILES initializationparameter in the database initialization parameter file (see "CreatingInitial Control Files"). Theinstance recognizes and opens all the listed file during startup, and theinstance writes to and maintains all listed control files during databaseoperation.
你可以在数据库的初始化参数文件里使用CONTROL_FILES初始化参数来指定控制文件的名称，实例在启动的时候确认并且打开列出的文件，并且实例在数据库操作中写入和维护所列出的控制文件。
If you do not specify files for CONTROL_FILES before database creation:
如果你在数据库创建前没有在CONTROL_FILES指定文件：
· If you are not usingOracle Managed Files, then the database creates a control file and uses adefault filename. The default name is operating system specific.
如果你没有使用OMF，那么数据库创建一个控制文件，并使用默认的文件名，默认的名称是操作系统指定的。
· If you are usingOracle Managed Files, then the initialization parameters you set to enable thatfeature determine the name and location of the control files, as described in Chapter17, "Using Oracle Managed Files".
如果你使用OMF，那么初始化参数将启用这个特性来指定控制文件名称和位置。
· If you are usingOracle Automatic Storage Management (Oracle ASM), you can place incompleteOracle ASM filenames in the DB_CREATE_FILE_DEST andDB_RECOVERY_FILE_DEST initialization parameters. Oracle ASMthen automatically creates control files in the appropriate places. See thesections "About Oracle ASM Filenames" and "Creating a DatabaseThat Uses Oracle ASM" in Oracle Automatic Storage Management Administrator‘s Guide for more information.
如果你使用ASM，你可以设置在DB_CREATE_FILE_DEST和DB_RECOVERY_FILE_DEST初始化参数，ASM就可以自动的在合适的位置创建控制文件。
2.2 Multiplex ControlFiles on Different Disks在不同磁盘上的控制文件的多路复用
Every Oracle Database should have at least two control files, each stored on a different physical disk. If a controlfile is damaged due to a disk failure, the associated instance must be shutdown. Once the disk drive is repaired, the damaged control file can be restoredusing the intact copy of the control file from the other disk and the instancecan be restarted. In this case, no media recovery is required.
每个oracle数据库应该有至少两个控制文件，每个存在都不同的物理磁盘上，如果其中一个控制文件由于磁盘损坏而损坏，那么相关联的实例必须被关闭。一旦这个磁盘恢复，损坏的控制文件可以使用另外磁盘上好的控制文件的副本来还原，那么实例就可以重新启动了，在这种情况下，不需要介质恢复。
The behavior of multiplexed control files is this:
控制文件多路复用的行为：
· The database writesto all filenames listed for the initialization parameter CONTROL_FILES in the database initializationparameter file.
在初始化文件参数里的CONTROL_FILES参数列出所有文件名，数据库将数据写入这些文件
· The database readsonly the first file listed in the CONTROL_FILES parameter during database operation.
在数据库进行操作时，数据库仅仅只读CONTROL_FILES参数里列出的第一个文件
· If any of thecontrol files become unavailable during database operation, the instancebecomes inoperable and should be aborted.
如果在数据库操作时，控制文件的任何一个不可用，那么实例将变得不能操作，并且会被强制关闭
Note:
Oracle strongly recommends that your database has a minimum oftwo control files and that they are located on separate physical disks.
Oracle强烈建议，你的数据库最少有两个控制文件，并且它们放置在不同的物理磁盘上。
One way to multiplexcontrol files is to store a control file copy on every disk drive that storesmembers of redo log groups, if the redo log is multiplexed. By storing controlfiles in these locations, you minimize the risk that all control files and allgroups of the redo log will be lost in a single disk failure.
多路复用控制文件的一个方法是在每个磁盘上都存储一个控制文件副本，如果重做日志是多路复用的，那么也会在每个磁盘上存储重做日志组的成员。将控制文件存储在这些地方，可以减少单点故障风险，即可以减少所有的控制文件和所有的重做日志组在一个单一磁盘上丢失的危险。
2.3 Back Up Control Files备份控制文件
It is very important that you back up your control files. Thisis true initially, and every time you change the physical structure of yourdatabase. Such structural changes include:
备份控制文件是很重要的，在数据库最初始状态或者每次你改变了数据库的物理结构时，结构改变的内容包括以下：
· Adding, dropping, orrenaming data files
增加，删除，重命名数据文件
· Adding or dropping atablespace, or altering the read/write state of the tablespace
增加或者删除表空间，或者改变表空间的读/写状态
· Adding or droppingredo log files or groups
增加或删除重做日志或重做日志组
The methods for backing up control files are discussed in "BackingUp Control Files".
备份控制文件的方法在“备份控制文件”中讨论。
2.4 Manage the Size ofControl Files管理控制文件的大小
The maindeterminants of the size of a control file are the values set for the MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY,and MAXINSTANCESparametersin the CREATEDATABASE statementthat created the associated database. Increasing the values of these parametersincreases the size of a control file of the associated database.
在使用CREATE DATABASE语句创建相关的数据库时， MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY，MAXINSTANCES参数决定了控制文件的大小，增加这些参数的值即增加了相关数据库控制文件的大小。
See Also:
· Your operatingsystem specific Oracle documentation contains more information about themaximum control file size.
· Oracle Database SQL Language Reference for a description of the CREATEDATABASE statement
3 Creating Control Files创建控制文件
This section describes ways to create control files, andcontains the following topics:
这章节主要描述创建控制文件的方法，包含以下内容：
· CreatingInitial Control Files
创建初始的控制文件
· CreatingAdditional Copies, Renaming, and Relocating Control Files
创建额外的副本，重命名或者迁移控制文件
· CreatingNew Control Files
创建新的控制文件
3.1 Creating InitialControl Files创建初始控制文件
The initial control files of an Oracle Database are created whenyou issue the CREATEDATABASE statement.The names of the control files are specified by theCONTROL_FILES parameter in the initializationparameter file used during database creation. The filenames specified in CONTROL_FILES should be fully specified and areoperating system specific. The following is an example of a CONTROL_FILES initialization parameter:
当你使用CREATE DATABASE语句创建oracle数据库的初始控制文件，控制文件的名字使用CONTROL_FILES初始化参数在数据库创建时来执行，在该参数中指定文件名字必须指定全路径，以下为该参数的例子：
CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
/u02/oracle/prod/control02.ctl,
/u03/oracle/prod/control03.ctl)
If files with the specified names currently exist at the time ofdatabase creation, you must specify the CONTROLFILEREUSE clause in the CREATEDATABASEstatement, or else an error occurs. Also, if the size ofthe old control file differs from the SIZE parameter of the new one, you cannotuse the REUSE clause.
如果在数据库创建时，指定的文件名已经存在，那么，你在CREATE DATABASE语句时必须指定 CONTROLFILE REUSE子句，否则会出现错误。同样，如果旧的控制文件的大小和之前不一样，则需要为新的控制文件指定SIZE参数，你不能使用REUSE子句。
The size of the control file changes between some releases ofOracle Database, as well as when the number of files specified in the controlfile changes. Configuration parameters such as MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES,and MAXINSTANCES affect control file size.
Oracle数据库的一些发行版本之间的控制文件的大小，即是当控制文件变化时执行的文件数量，配置如下参数MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES，MAXINSTANCES会影响到控制文件的大小。
You can subsequently change the value of the CONTROL_FILES initialization parameter to add morecontrol files or to change the names or locations of existing control files.
你随后可以改变CONTROL_FILES初始化参数的值，添加更多的控制文件或者改变名称或者改变现有存在的控制文件的位置
See Also:
Your operating system specific Oracle documentation containsmore information about specifying control files.
你的操作系统指定oracle文档包含更多指定控制文件的信息
3.2 Creating AdditionalCopies, Renaming, and Relocating Control Files创建新增副本，重命名，迁移控制文件
You can create an additional control file copy for multiplexingby copying an existing control file to a new location and adding the file nameto the list of control files. Similarly, you rename an existing control file bycopying the file to its new name or location, and changing the file name in thecontrol file list. In both cases, to guarantee that control files do not changeduring the procedure, shut down the database before copying the control file.
你拷贝一个已存在的控制文件来创建一个额外的控制文件副本来做多路复用到一个新的位置，并且将文件名添加到控制文件列表中，同样的，你可以通过拷贝文件来重命名一个已存在的控制文件为新的名称或者位置，并且改变控制文件列表的名字。在这样的情况下，保证控制文件在数据库运行中不能改变，在拷贝控制文件之前要关闭数据库。
To add a multiplexed copy of the current control file or torename a control file:
增加一个多路复用的副本或者重命名控制文件按照以下步骤：
1. Shut down thedatabase.
关闭数据库
2. Copy an existingcontrol file to a new location, using operating system commands.
使用操作系统命令拷贝一个已存在的控制文件到一个新路径
3. Edit the CONTROL_FILES parameter in the databaseinitialization parameter file to add the new control file name, or to changethe existing control filename.
编辑数据库里的初始化参数CONTROL_FILES参数来增加一个新的控制文件名称，或者改变已存在的控制文件的文件名
4. Restart thedatabase.
重启数据库
3.2.1 使用静态参数pfile来添加或删除或迁移控制文件
使用spfile创建pfile，操作系统拷贝或删除，使用vi编辑CONTROL_FILES参数，使用pfile启动数据库，创建spfile，再使用spfile重新启动数据库。
通过服务器端初始化参数文件建立静态初始化参数，并修改静态初始化参数文件，增加一个控制文件；（删除同样方法）
1.创建pfile文件，关闭数据库
SYS@db12c>create pfile from spfile;
Filecreated
SYS@db12c>shutdown immediate;
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
2.操作系统命令拷贝（删除类似 rm –rf ）
[oracle@db12c dbs]$ cp/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl/u01/app/oracle/oradata/DB12C/controlfile/control02.ctl
3.vi编辑pfile初始化参数，编辑CONTROL_FILES文件
[oracle@db12cdbs]$ pwd
/u01/app/oracle/product/12.1/db_1/dbs
[oracle@db12cdbs]$ ll
total17580
-rw-rw----1 oracle oinstall 1544 Aug 4 15:49 hc_db12c.dat
-rw-r--r--1 oracle oinstall 1104 Aug 4 15:51 initdb12c.ora
-rw-r--r--1 oracle oinstall 2992 Feb 3 2012 init.ora
-rw-r-----1 oracle oinstall 24 Jul 28 15:20 lkDB12C
-rw-r-----1 oracle oinstall 7680 Jul 28 15:37 orapwdb12c
-rw-r-----1 oracle oinstall 17973248 Jul 28 15:52 snapcf_db12c.f
-rw-r-----1 oracle oinstall 3584 Aug 4 15:02 spfiledb12c.ora
[oracle@db12cdbs]$ viinitdb12c.ora
db12c.__data_transfer_cache_size=0
db12c.__db_cache_size=503316480
db12c.__java_pool_size=16777216
db12c.__large_pool_size=33554432
db12c.__oracle_base=‘/u01/app/oracle‘#ORACLE_BASEset from environment
db12c.__pga_aggregate_target=436207616
db12c.__sga_target=822083584
db12c.__shared_io_pool_size=50331648
db12c.__shared_pool_size=201326592
db12c.__streams_pool_size=0
*.audit_file_dest=‘/u01/app/oracle/admin/db12c/adump‘
*.audit_trail=‘db‘
*.compatible=‘12.1.0.0.0‘
*.control_files=‘/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl‘,‘/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl‘,‘/u01/app/oracle/oradata/DB12C/controlfile/control02.ctl‘
*.db_block_size=8192
*.db_create_file_dest=‘/u01/app/oracle/oradata‘
*.db_domain=‘‘
*.db_name=‘db12c‘
*.db_recovery_file_dest=‘/u01/app/oracle/fast_recovery_area‘
*.db_recovery_file_dest_size=4800m
*.diagnostic_dest=‘/u01/app/oracle‘
*.dispatchers=‘(PROTOCOL=TCP)(SERVICE=db12cXDB)‘
*.enable_pluggable_database=true
*.log_archive_format=‘%t_%s_%r.dbf‘
*.memory_target=1200m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile=‘EXCLUSIVE‘
*.undo_tablespace=‘UNDOTBS1‘
~
"initdb12c.ora"29L, 1162C written
[oracle@db12cdbs]$
4.使用pfile启动数据库，从pfile创建spfile文件，再使用spfile文件启动数据库
SYS@db12c>startuppfile=‘/u01/app/oracle/product/12.1/db_1/dbs/initdb12c.ora‘
ORACLEinstance started.
TotalSystem Global Area 1252663296 bytes
FixedSize 2287864bytes
VariableSize 788530952 bytes
DatabaseBuffers 452984832 bytes
RedoBuffers 8859648 bytes
Databasemounted.
Databaseopened.
SYS@db12c>showparameter control_files
NAME TYPE VALUE
----------------------------------------------- ------------------------------
control_files string /u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl,/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl, /u01/app/oracle/oradata/DB12C/controlfile/control02.ctl
SYS@db12c>
SYS@db12c>create spfile frompfile;
Filecreated.
SYS@db12c>startup force
ORACLEinstance started.
TotalSystem Global Area 1252663296 bytes
FixedSize 2287864bytes
VariableSize 788530952 bytes
DatabaseBuffers 452984832 bytes
RedoBuffers 8859648 bytes
Databasemounted.
Databaseopened.
SYS@db12c>
3.2.2 使用动态参数spfile来添加或删除或迁移控制文件
1.获取控制文件信息
SYS@db12c>select name fromv$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl
/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl
SYS@db12c>
2.更改SPFILE中控制文件信息，增加一个新的控制文件（删除，迁移类似）
SYS@db12c>alter system set control_files=‘/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl‘,‘/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl‘,‘/u01/app/oracle/oradata/DB12C/controlfile/control02.ctl‘scope=spfile;
System altered.
3.关闭数据库
SYS@db12c>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@db12c>
4.在操作系统中，将已有的控制文件进行拷贝操作，修改名称或路径，之后，重新启动数据库
SYS@db12c>!cp/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl/u01/app/oracle/oradata/DB12C/controlfile/control02.ctl
SYS@db12c>startup
ORACLE instance started.
Total System Global Area1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 788530952 bytes
Database Buffers 452984832 bytes
Redo Buffers 8859648 bytes
Database mounted.
Database opened.
5.再来查看控制文件
SYS@db12c>selectname from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl
/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl
/u01/app/oracle/oradata/DB12C/controlfile/control02.ctl
SYS@db12c>
3.3 Creating New ControlFiles创建新的控制文件
This section discusses when and how to create new control files.
这个章节讨论什么时候怎么样去创建新的控制文件
3.3.1 When to Create NewControl Files什么时候创建新的控制文件
It is necessary for you to create new control files in thefollowing situations:
在以下情况是需要你创建新的控制文件：
· All control filesfor the database have been permanently damaged and you do not have a controlfile backup.
数据库的所有的控制文件永久的破坏，并且没有一个控制文件的备份
· You want to changethe database name.
你想要改变数据库名
For example, you would change a database name if it conflictedwith another database name in a distributed environment.
例如，在分布式环境中，如果你想改变一个和另外一个数据库名相冲突的数据库名称。
Note:
You can change the database name and DBID (internal databaseidentifier) using the DBNEWID utility. See Oracle Database Utilities for information about using this utility.
你可以使用DBNEWID工具来改变数据库名和DBID（数据库内部标示）
3.3.2 The CREATE CONTROLFILEStatementCREATE CONTROLFILE语句
You can create a new control file for a database using the CREATECONTROLFILE statement.The following statement creates a new control file for the proddatabase(a database that formerly used a different database name):
你可以使用CREATE CONTROL语句来为一个数据库创建一个新的控制文件，以下语句是为prod数据库创建了一个新的控制文件（该数据库以前使用的另外不一样的数据库名）
CREATE CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 (‘/u01/oracle/prod/redo01_01.log‘,
‘/u01/oracle/prod/redo01_02.log‘),
GROUP 2 (‘/u01/oracle/prod/redo02_01.log‘,
‘/u01/oracle/prod/redo02_02.log‘),
GROUP 3 (‘/u01/oracle/prod/redo03_01.log‘,
‘/u01/oracle/prod/redo03_02.log‘)
RESETLOGS
DATAFILE ‘/u01/oracle/prod/system01.dbf‘ SIZE 3M,
‘/u01/oracle/prod/rbs01.dbs‘ SIZE 5M,
‘/u01/oracle/prod/users01.dbs‘ SIZE 5M,
‘/u01/oracle/prod/temp01.dbs‘ SIZE 5M
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;
Cautions:
· The CREATECONTROLFILE statementcan potentially damage specified data files and redo log files. Omitting afilename can cause loss of the data in that file, or loss of access to theentire database. Use caution when issuing this statement and be sure to followthe instructions in "Stepsfor Creating New Control Files".
CREATE CONTROLFILE语句可能会使指定的数据文件和重做日志文件损坏，省略文件名可能会导致那个文件数据有丢失，或者失去对整个数据库的访问。当你使用这条语句时，注意确定按照“创建新的控制文件的步骤”来执行。
· If the database hadforced logging enabled before creating the new control file, and you want it tocontinue to be enabled, then you must specify the FORCELOGGING clause inthe CREATECONTROLFILE statement.See "SpecifyingFORCE LOGGING Mode".
如果在创建新的控制文件前，数据库的强制日志是可用的，你想让数据库的强制日志继续可用，那么你必须在CREATE CONTROLFILE语句指定FORCE LOGGING子句，详见“指定强制日志模式”。
See Also:
Oracle Database SQL Language Reference describes the complete syntax of the CREATECONTROLFILE statement
保险起见，先rman备份一下
[oracle@db12c ~]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Tue Aug 509:04:21 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB12C (DBID=1308144233)
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/home/oracle/backup/DB12C_%U.bak‘;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/home/oracle/backup/DB12C_%U.bak‘;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO‘/home/oracle/backup/control/DB12C_%F.bak‘;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO‘/home/oracle/backup/control/DB12C_%F.bak‘;
new RMAN configuration parameters are successfully stored
RMAN> backup as compressed backupset database include current controlfileplus archivelog delete all input;
Starting backup at 05-AUG-14
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=1 STAMP=854120380
input archived log thread=1 sequence=13 RECID=2 STAMP=854120472
input archived log thread=1 sequence=14 RECID=3 STAMP=854120643
input archived log thread=1 sequence=15 RECID=4 STAMP=854120654
input archived log thread=1 sequence=16 RECID=5 STAMP=854120677
input archived log thread=1 sequence=17 RECID=6 STAMP=854120722
input archived log thread=1 sequence=18 RECID=7 STAMP=854120800
input archived log thread=1 sequence=19 RECID=8 STAMP=854120885
input archived log thread=1 sequence=20 RECID=9 STAMP=854120953
input archived log thread=1 sequence=21 RECID=10 STAMP=854120981
input archived log thread=1 sequence=22 RECID=11 STAMP=854121063
input archived log thread=1 sequence=23 RECID=12 STAMP=854129531
input archived log thread=1 sequence=24 RECID=13 STAMP=854721139
input archived log thread=1 sequence=25 RECID=14 STAMP=854726096
input archived log thread=1 sequence=26 RECID=15 STAMP=854786604
input archived log thread=1 sequence=27 RECID=16 STAMP=854788141
channel ORA_DISK_1: starting piece 1 at 05-AUG-14
channel ORA_DISK_1: finished piece 1 at 05-AUG-14
piece handle=/home/oracle/backup/DB12C_02pf621d_1_1.baktag=TAG20140805T090901 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: deleting archived log(s)
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_07_28/o1_mf_1_12_9xczktp5_.arcRECID=1 STAMP=854120380
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_07_28/o1_mf_1_13_9xcznqsm_.arcRECID=2 STAMP=854120472
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_07_28/o1_mf_1_14_9xczsz9d_.arcRECID=3 STAMP=854120643
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_07_28/o1_mf_1_15_9xcztfcr_.arcRECID=4 STAMP=854120654
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_07_28/o1_mf_1_16_9xczv2f0_.arcRECID=5 STAMP=854120677
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_07_28/o1_mf_1_17_9xczwjnj_.arcRECID=6 STAMP=854120722
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_07_28/o1_mf_1_18_9xczyyvk_.arcRECID=7 STAMP=854120800
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_07_28/o1_mf_1_19_9xd01lz0_.arcRECID=8 STAMP=854120885
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_07_28/o1_mf_1_20_9xd03r36_.arcRECID=9 STAMP=854120953
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_07_28/o1_mf_1_21_9xd04n81_.arcRECID=10 STAMP=854120981
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_07_28/o1_mf_1_22_9xd07656_.arcRECID=11 STAMP=854121063
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_07_28/o1_mf_1_23_9xd8htkv_.arcRECID=12 STAMP=854129531
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_04/o1_mf_1_24_9xyb7lsr_.arcRECID=13 STAMP=854721139
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_04/o1_mf_1_25_9xyh2hg9_.arcRECID=14 STAMP=854726096
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_05/o1_mf_1_26_9y0b5b97_.arcRECID=15 STAMP=854786604
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_05/o1_mf_1_27_9y0codg9_.arcRECID=16 STAMP=854788141
Finished backup at 05-AUG-14
Starting backup at 05-AUG-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcykmom_.dbf
input datafile file number=00003name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcygnfw_.dbf
input datafile file number=00004name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_9xcynn1n_.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xcynlwd_.dbf
channel ORA_DISK_1: starting piece 1 at 05-AUG-14
channel ORA_DISK_1: finished piece 1 at 05-AUG-14
piece handle=/home/oracle/backup/DB12C_03pf622i_1_1.baktag=TAG20140805T090937 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xczq0ls_.dbf
input datafile file number=00011name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_example_9xczq0m6_.dbf
input datafile file number=00008name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xczq0m9_.dbf
input datafile file number=00010name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xczq0md_.dbf
channel ORA_DISK_1: starting piece 1 at 05-AUG-14
channel ORA_DISK_1: finished piece 1 at 05-AUG-14
piece handle=/home/oracle/backup/DB12C_04pf624t_1_1.baktag=TAG20140805T090937 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcyozo1_.dbf
input datafile file number=00005name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcyozo6_.dbf
channel ORA_DISK_1: starting piece 1 at 05-AUG-14
channel ORA_DISK_1: finished piece 1 at 05-AUG-14
piece handle=/home/oracle/backup/DB12C_05pf626k_1_1.baktag=TAG20140805T090937 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 05-AUG-14
channel ORA_DISK_1: finished piece 1 at 05-AUG-14
piece handle=/home/oracle/backup/DB12C_06pf6281_1_1.baktag=TAG20140805T090937 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-AUG-14
Starting backup at 05-AUG-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=28 RECID=17 STAMP=854788357
channel ORA_DISK_1: starting piece 1 at 05-AUG-14
channel ORA_DISK_1: finished piece 1 at 05-AUG-14
piece handle=/home/oracle/backup/DB12C_07pf6286_1_1.baktag=TAG20140805T091238 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_05/o1_mf_1_28_9y0cw5xo_.arcRECID=17 STAMP=854788357
Finished backup at 05-AUG-14
Starting Control File and SPFILE Autobackup at 05-AUG-14
piece handle=/home/oracle/backup/control/DB12C_c-1308144233-20140805-00.bakcomment=NONE
Finished Control File and SPFILE Autobackup at 05-AUG-14
RMAN>
*****************************************************************************************************************************************************
[oracle@db12c control]$ pwd
/home/oracle/backup/control
[oracle@db12c control]$ ll
total 17632
-rw-r----- 1 oracle oinstall 18055168 Aug 5 09:12DB12C_c-1308144233-20140805-00.bak
[oracle@db12c control]$ cd ..
[oracle@db12c backup]$ pwd
/home/oracle/backup
[oracle@db12c backup]$ ll
total 965048
drwxr-xr-x 2 oracle oinstall 4096 Aug 5 09:12 control
-rw-r----- 1 oracle oinstall 149952000 Aug 5 09:09 DB12C_02pf621d_1_1.bak
-rw-r----- 1 oracle oinstall 383705088 Aug 5 09:10 DB12C_03pf622i_1_1.bak
-rw-r----- 1 oracle oinstall 238657536 Aug 5 09:11 DB12C_04pf624t_1_1.bak
-rw-r----- 1 oracle oinstall 214745088 Aug 5 09:12 DB12C_05pf626k_1_1.bak
-rw-r----- 1 oracle oinstall 1114112 Aug 5 09:12DB12C_06pf6281_1_1.bak
-rw-r----- 1 oracle oinstall 19968 Aug 5 09:12DB12C_07pf6286_1_1.bak
[oracle@db12c backup]$
3.3.3 Steps for Creating NewControl Files创建新的控制文件的步骤
Complete the following steps to create a new control file.
按照以下步骤来完成创建一个新的控制文件
1. Make a list of alldata files and redo log files of the database.
将数据库的所有的数据文件和重做日志文件一个清单列表
If you follow recommendations for control file backups asdiscussed in "BackingUp Control Files" , you will alreadyhave a list of data files and redo log files that reflect the current structureof the database. However, if you have no such list, executing the followingstatements will produce one.
SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE;
SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘control_files‘;
If you have no such lists and your control file has been damagedso that the database cannot be opened, try to locate all of the data files andredo log files that constitute the database. Any files not specified in step 5 are not recoverable once a new control file has been created.Moreover, if you omit any of the files that comprise the SYSTEM tablespace, you might not be able torecover the database.
如果你没有这样的清单，并且你的控制文件已经损坏，那么数据库将不能打开。尝试将所有的数据文件盒重做日志文件迁移到新位置来组成数据库，在步骤5中，一旦创建了一个新的控制文件，任何文件不能被指定和不能恢复，另外，如果你省略了任何文件，包括SYSTEM表空间，你可能不能再恢复数据库。
2. Shut down thedatabase.
关闭数据库
If the database is open, shut down the database normally if possible.Use the IMMEDIATE or ABORT clauses only as a last resort.
如果数据库是打开状态，那么正常关闭数据库，使用IMMEDIATE或者ABORT作为最后的方法。
3. Back up all datafiles and redo log files of the database.
备份数据库中的所有的数据文件和重做日志文件
4. Start up a newinstance, but do not mount or open the database:
启动数据库，但是不要装载或者打开数据库
STARTUP NOMOUNT
5. Create a new controlfile for the database using the CREATECONTROLFILE statement.
使用CREATE CONTROL语句来创建一个新的控制文件
When creating a new control file, specify the RESETLOGS clause if you have lost any redo loggroups in addition to control files. In this case, you will need to recoverfrom the loss of the redo logs (step 8). You must specify the RESETLOGS clause if you have renamed thedatabase. Otherwise,select theNORESETLOGS clause.
当创建一个新的控制文件时，除了控制文件，如果你失去了任何重做日志组须指定RESETLOGS子句，在这种情况下，你需要恢复重做日志，如果你想重命名数据库，则你必须指定RESETLOGS子句，否则，使用NORESETLOGS子句。
6. Store a backup ofthe new control file on an offline storage device. See "BackingUp Control Files" for instructions forcreating a backup.
还原新的控制文件的百备份到离线存储设备上。
7. Edit the CONTROL_FILES initialization parameter for thedatabase to indicate all of the control files now part of your database ascreated in step 5 (not including the backup control file). If you are renaming thedatabase, edit the DB_NAME parameter in your instance parameterfile to specify the new name.
编辑数据库的CONTROL_FILES初始化参数来指明所有的控制文件在第5步创建的部分（不包含备份控制文件），如果你要重命名数据库，在你的实例参数文件里编辑DB_NAME参数指定新的名字。
8. Recover the databaseif necessary. If you are not recovering the database, skip to step 9.
如果需要，请恢复数据库，如果没有恢复数据库，跳至第9步。
If you are creating the control file as part of recovery,recover the database. If the new control file was created using the NORESETLOGS clause (step 5), you can recover the database with complete, closed databaserecovery.
如果创建控制文件是恢复的一部分，那么需要恢复数据库，如果新的控制文件创建时使用NORESETLOGS子句，你可以完全的恢复数据库。
If the new control file was created using the RESETLOGS clause, you must specify USINGBACKUP CONTROL FILE. If you have lost online redo logs, archivedredo log files, or data files, use the procedures for recovering those files.
如果新的控制文件创建时使用RESTLOGS子句，你必须指定USING BACKUP CONTROLFILE.如果你丢失了在线重做日志，归档日志文件或者数据文件，使用程序恢复这些文件。
See Also:
OracleDatabase Backup and Recovery User‘s Guide for information about recovering your database and methods ofrecovering a lost control file
9. Open the databaseusing one of the following methods:
使用以下方法打开数据库
o If you did not perform recovery, or you performed complete,closed database recovery in step 8, open the database normally.
如果你没有执行恢复，或者你在第8步执行完全的数据库恢复，即正常的打开数据库。
ALTER DATABASE OPEN;
o If you specified RESETLOGS when creating the control file, usethe ALTERDATABASE statement,indicating RESETLOGS.
如果你在创建控制文件时指定RESETLOGS，使用ALTER DATABASE 打开数据库时应使用RESETLOGS。
ALTER DATABASE OPEN RESETLOGS;
The database is nowopen and available for use.
3.3.4 Eg.创建一个新的控制文件（NORESETLOGS）
1.将数据库的所有的数据文件和重做日志文件一个清单列表
SYS@db12c>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 31
Next log sequence toarchive 33
Current log sequence 33
SYS@db12c>select member fromv$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_3_9xcyokx3_.log
/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_3_9xcyol09_.log
/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_2_9xcyogfh_.log
/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_2_9xcyogj8_.log
/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_1_9xcyobx8_.log
/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_1_9xcyoc3q_.log
6 rows selected.
SYS@db12c>select name fromv$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcykmom_.dbf
/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcygnfw_.dbf
/u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_9xcynn1n_.dbf
/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcyozo6_.dbf
/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xcynlwd_.dbf
/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcyozo1_.dbf
/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xczq0m9_.dbf
/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xczq0ls_.dbf
/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xczq0md_.dbf
/u01/app/oracle/oradata/DB12C/datafile/o1_mf_example_9xczq0m6_.dbf
10 rows selected.
SYS@db12c>select value fromv$parameter where name=‘control_files‘;
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl,/u01/app/oracle/f
ast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl,/u01/app/oracle/oradata
/DB12C/controlfile/control02.ctl
SYS@db12c>
2.关闭数据库，并删除控制文件
SYS@db12c>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@db12c>
SYS@db12c>! rm -rf/u01/app/oracle/oradata/DB12C/controlfile/control02.ctl
SYS@db12c>! rm -rf/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl
SYS@db12c>! rm -rf/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl
3.启动到nomount
SYS@db12c>startup nomount
ORACLE instance started.
Total System Global Area1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 788530952 bytes
Database Buffers 452984832 bytes
Redo Buffers 8859648 bytes
SYS@db12c>
4.创建控制文件（这个脚本在下面“备份控制文件”文中可以得到）
SYS@db12c>CREATE CONTROLFILEREUSE DATABASE "DB12C"NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 ‘/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_1_9xcyobx8_.log‘,
10 ‘/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_1_9xcyoc3q_.log‘
11 )SIZE 50M BLOCKSIZE 512,
12 GROUP 2 (
13 ‘/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_2_9xcyogfh_.log‘,
14 ‘/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_2_9xcyogj8_.log‘
15 )SIZE 50M BLOCKSIZE 512,
16 GROUP 3 (
17 ‘/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_3_9xcyokx3_.log‘,
18 ‘/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_3_9xcyol09_.log‘
19 )SIZE 50M BLOCKSIZE 512
20 --STANDBY LOGFILE
21 DATAFILE
22 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcykmom_.dbf‘,
23 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcygnfw_.dbf‘,
24 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_9xcynn1n_.dbf‘,
25 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcyozo6_.dbf‘,
26 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xcynlwd_.dbf‘,
27 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcyozo1_.dbf‘,
28 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xczq0m9_.dbf‘,
29 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xczq0ls_.dbf‘,
30 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xczq0md_.dbf‘,
31 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_example_9xczq0m6_.dbf‘
32 CHARACTER SET AL32UTF8
33 ;
Control file created.
SYS@db12c>
5.打开数据库
SYS@db12c>alter databaseopen;
Database altered.
再重新备份一次
3.3.5 Eg.创建一个新的控制文件（RESETLOGS）
1. 关闭数据库
SYS@db12c>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2. 删除控制文件，并将数据库启动到nomount
SYS@db12c>! rm -rf/u01/app/oracle/oradata/DB12C/controlfile/control02.ctl
SYS@db12c>! rm -rf/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl
SYS@db12c>! rm -rf/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl
SYS@db12c>startup nomount
ORACLE instance started.
Total System Global Area1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 788530952 bytes
Database Buffers 452984832 bytes
Redo Buffers 8859648 bytes
3. 创建控制文件
SYS@db12c>CREATE CONTROLFILEREUSE DATABASE "DB12C" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 ‘/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_1_9xcyobx8_.log‘,
10 ‘/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_1_9xcyoc3q_.log‘
11 )SIZE 50M BLOCKSIZE 512,
12 GROUP 2 (
13 ‘/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_2_9xcyogfh_.log‘,
14 ‘/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_2_9xcyogj8_.log‘
15 )SIZE 50M BLOCKSIZE 512,
16 GROUP 3 (
17 ‘/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_3_9xcyokx3_.log‘,
18 ‘/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_3_9xcyol09_.log‘
19 )SIZE 50M BLOCKSIZE 512
20 --STANDBY LOGFILE
21 DATAFILE
22 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcykmom_.dbf‘,
23 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcygnfw_.dbf‘,
24 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_9xcynn1n_.dbf‘,
25 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcyozo6_.dbf‘,
26 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xcynlwd_.dbf‘,
27 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcyozo1_.dbf‘,
28 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xczq0m9_.dbf‘,
29 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xczq0ls_.dbf‘,
30 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xczq0md_.dbf‘,
31 ‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_example_9xczq0m6_.dbf‘
32 CHARACTER SET AL32UTF8
33 ;
Control file created.
4. Resetlogs打开数据库
SYS@db12c>alter database openresetlogs;
Database altered.
SYS@db12c>select name fromv$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl
/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl
SYS@db12c>
Resetlogs打开数据库后，前面的备份集就变为无效，即要重新备份一下数据库
Rman备份数据库
[oracle@db12c backup]$ rmantarget /
Recovery Manager: Release12.1.0.1.0 - Production on Tue Aug 5 20:03:38 2014
Copyright (c) 1982, 2013, Oracleand/or its affiliates. All rightsreserved.
connected to target database:DB12C (DBID=1308144233)
RMAN> backup as compressedbackupset database include current controlfile plus archivelog delete allinput;
Starting backup at 05-AUG-14
current log archived
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38device type=DISK
channel ORA_DISK_1: startingcompressed archived log backup set
channel ORA_DISK_1: specifyingarchived log(s) in backup set
input archived log thread=1sequence=34 RECID=1 STAMP=854826964
input archived log thread=1sequence=35 RECID=2 STAMP=854826964
input archived log thread=1sequence=36 RECID=3 STAMP=854826965
channel ORA_DISK_1: startingpiece 1 at 05-AUG-14
channel ORA_DISK_1: finishedpiece 1 at 05-AUG-14
piecehandle=/u01/app/oracle/fast_recovery_area/DB12C/backupset/2014_08_05/o1_mf_annnn_TAG20140805T200349_9y1l156l_.bkptag=TAG20140805T200349 comment=NONE
channel ORA_DISK_1: backup setcomplete, elapsed time: 00:00:01
channel ORA_DISK_1: deletingarchived log(s)
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_05/o1_mf_1_34_9y1kln8m_.arcRECID=1 STAMP=854826964
archived log file name=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_05/o1_mf_1_35_9y1kln9z_.arcRECID=2 STAMP=854826964
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_05/o1_mf_1_36_9y1klnct_.arcRECID=3 STAMP=854826965
channel ORA_DISK_1: startingcompressed archived log backup set
channel ORA_DISK_1: specifyingarchived log(s) in backup set
input archived log thread=1sequence=1 RECID=4 STAMP=854827428
channel ORA_DISK_1: startingpiece 1 at 05-AUG-14
channel ORA_DISK_1: finishedpiece 1 at 05-AUG-14
piecehandle=/u01/app/oracle/fast_recovery_area/DB12C/backupset/2014_08_05/o1_mf_annnn_TAG20140805T200349_9y1l16p2_.bkptag=TAG20140805T200349 comment=NONE
channel ORA_DISK_1: backup setcomplete, elapsed time: 00:00:01
channel ORA_DISK_1: deletingarchived log(s)
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_05/o1_mf_1_1_9y1l14cv_.arcRECID=4 STAMP=854827428
Finished backup at 05-AUG-14
Starting backup at 05-AUG-14
using channel ORA_DISK_1
channel ORA_DISK_1: startingcompressed full datafile backup set
channel ORA_DISK_1: specifyingdatafile(s) in backup set
input datafile file number=00001name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcykmom_.dbf
input datafile file number=00003name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcygnfw_.dbf
input datafile file number=00004name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_9xcynn1n_.dbf
input datafile file number=00006name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xcynlwd_.dbf
channel ORA_DISK_1: startingpiece 1 at 05-AUG-14
channel ORA_DISK_1: finishedpiece 1 at 05-AUG-14
piecehandle=/u01/app/oracle/fast_recovery_area/DB12C/backupset/2014_08_05/o1_mf_nnndf_TAG20140805T200351_9y1l1815_.bkptag=TAG20140805T200351 comment=NONE
channel ORA_DISK_1: backup setcomplete, elapsed time: 00:01:16
channel ORA_DISK_1: startingcompressed full datafile backup set
channel ORA_DISK_1: specifyingdatafile(s) in backup set
input datafile file number=00009name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xczq0ls_.dbf
input datafile file number=00011name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_example_9xczq0m6_.dbf
input datafile file number=00008name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xczq0m9_.dbf
input datafile file number=00010name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xczq0md_.dbf
channel ORA_DISK_1: startingpiece 1 at 05-AUG-14
channel ORA_DISK_1: finishedpiece 1 at 05-AUG-14
piecehandle=/u01/app/oracle/fast_recovery_area/DB12C/FF3D1512366F42F4E045000000000001/backupset/2014_08_05/o1_mf_nnndf_TAG20140805T200351_9y1l3m69_.bkptag=TAG20140805T200351 comment=NONE
channel ORA_DISK_1: backup setcomplete, elapsed time: 00:00:55
channel ORA_DISK_1: startingcompressed full datafile backup set
channel ORA_DISK_1: specifyingdatafile(s) in backup set
input datafile file number=00007name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcyozo1_.dbf
input datafile file number=00005name=/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcyozo6_.dbf
channel ORA_DISK_1: startingpiece 1 at 05-AUG-14
channel ORA_DISK_1: finishedpiece 1 at 05-AUG-14
piecehandle=/u01/app/oracle/fast_recovery_area/DB12C/FF3CD6C4F94F3FD9E045000000000001/backupset/2014_08_05/o1_mf_nnndf_TAG20140805T200351_9y1l5bbf_.bkptag=TAG20140805T200351 comment=NONE
channel ORA_DISK_1: backup setcomplete, elapsed time: 00:00:45
channel ORA_DISK_1: startingcompressed full datafile backup set
channel ORA_DISK_1: specifyingdatafile(s) in backup set
including current control filein backup set
channel ORA_DISK_1: startingpiece 1 at 05-AUG-14
channel ORA_DISK_1: finishedpiece 1 at 05-AUG-14
piecehandle=/u01/app/oracle/fast_recovery_area/DB12C/backupset/2014_08_05/o1_mf_ncnnf_TAG20140805T200351_9y1l6tn6_.bkptag=TAG20140805T200351 comment=NONE
channel ORA_DISK_1: backup setcomplete, elapsed time: 00:00:01
Finished backup at 05-AUG-14
Starting backup at 05-AUG-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: startingcompressed archived log backup set
channel ORA_DISK_1: specifyingarchived log(s) in backup set
input archived log thread=1sequence=2 RECID=5 STAMP=854827611
channel ORA_DISK_1: starting piece1 at 05-AUG-14
channel ORA_DISK_1: finishedpiece 1 at 05-AUG-14
piecehandle=/u01/app/oracle/fast_recovery_area/DB12C/backupset/2014_08_05/o1_mf_annnn_TAG20140805T200652_9y1l6wb5_.bkptag=TAG20140805T200652 comment=NONE
channel ORA_DISK_1: backup setcomplete, elapsed time: 00:00:01
channel ORA_DISK_1: deletingarchived log(s)
archived log filename=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_05/o1_mf_1_2_9y1l6vpz_.arcRECID=5 STAMP=854827611
Finished backup at 05-AUG-14
Starting Control File and SPFILEAutobackup at 05-AUG-14
piecehandle=/u01/app/oracle/fast_recovery_area/DB12C/autobackup/2014_08_05/o1_mf_s_854827613_9y1l6xx7_.bkpcomment=NONE
Finished Control File and SPFILEAutobackup at 05-AUG-14
RMAN>
4 Troubleshooting After Creating Control Files创建控制文件后的故障排除
After issuing the CREATECONTROLFILE statement,you may encounter some errors. This section describes the most common controlfile errors:
使用CREATE CONTROLFILE语句创建控制文件后，你可能会遇到一些错误。这章节主要描述大多数控制文件的普通问题：
· Checkingfor Missing or Extra Files
· HandlingErrors During CREATE CONTROLFILE
4.1 Checking for Missingor Extra Files检查丢失或多余的文件
After creating a new control file and using it to open thedatabase, check the alert log to see if the database has detectedinconsistencies between the data dictionary and the control file, such as adata file in the data dictionary includes that the control file does not list.
新的控制文件创建之后，并且使用它来打开数据库，如果数据库在数据字典和控制文件之间有检查到不一致，则检查告警日志，比如一个数据文件在数据字典里讯在，而不再控制文件里存在。
Ifa data file exists in the data dictionary but not in the new control file, thedatabase creates a placeholder entry in the control file under the nameMISSINGnnnn, where nnnn is the file number in decimal. MISSINGnnnn isflagged in the control file as being offline and requiring media recovery.
如果一个数据文件存在数据字典但是不存在新的控制文件里，数据库会在控制文件里创建一个占位符条目使用名称为MISSINGnnnn，nnnn代表文件的序号，MISSINGnnnn在控制文件里是被标志为离线和需要介质恢复。
If the actual data file corresponding to MISSINGnnnn isread-only or offline normal, then you can make the data file accessible byrenaming MISSINGnnnn tothe name of the actual data file. If MISSINGnnnn correspondsto a data file that was not read-only or offline normal, then you cannot usethe rename operation to make the data file accessible, because the data filerequires media recovery that is precluded by the results of RESETLOGS.In this case, you must drop the tablespace containing the data file.
如果实际的数据文件标志的MISSINGnnnn是只读或者正常离线的，那么你重命名MISSINGnnnn数据文件为可访问为实际的数据文件，如果MISSINGnnnn相关联的数据文件不是只读或者正常离线，那么你不能重命名，因为数据文件要求介质恢复，在这种情况下，你必须删除包含该数据文件的表空间。
Conversely, if a data file listed in the control file is notpresent in the data dictionary, then the database removes references to it fromthe new control file. In both cases, the database includes an explanatorymessage in the alert log to let you know what was found.
相反的，如果数据文件在控制文件列表中，而不再目前的数据字典中，那么数据库将移除相关的去匹配控制文件，在这些情况下，数据库会在告警日志中包含一个解释信息，让你知道出现了什么问题。
4.2 Handling Errors DuringCREATE CONTROLFILE在CREATE CONTROL创建过程中处理错误
If Oracle Database sends you an error (usually error ORA-01173, ORA-01176, ORA-01177, ORA-01215,or ORA-01216)when you attempt to mount and open the database after creating a new controlfile, the most likely cause is that you omitted a file from the CREATECONTROLFILE statementor included one that should not have been listed. In this case, you shouldrestore the files you backed up in step 3 and repeat the procedure from step 4, using the correct filenames.
当你创建新的控制文件后，试图装载数据库或者打开数据库时，如果数据库给你发送错误（通常错误为ORA-01173, ORA-01176, ORA-01177, ORA-01215,or ORA-01216），最可能的原因是你在使用CREATE CONTROLFILE语句时忽略了文件或者包含了一个没有在列表中的文件，在这样的情况下，你应该还原在第3步备份的文件，使用正确的文件名，重新执行第4步。
5 Backing Up Control Files备份控制文件
Use the ALTERDATABASE BACKUP CONTROLFILE statementto back up your control files. You have two options:
使用ALTER DATABASE BACKUP CONTROLFILE语句来备份你的控制文件，你有两种选项：
· Back up the controlfile to a binary file (duplicate of existing control file) using the followingstatement:
备份控制文件为一个二进制文件（已存在控制文件的副本）使用以下语句：
ALTER DATABASE BACKUP CONTROLFILE TO ‘/oracle/backup/control.bkp‘;
· Produce SQLstatements that can later be used to re-create your control file:
使用SQL语句来备份以便以后可以用来重新创建控制文件
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This command writes a SQL script to a trace file where it can becaptured and edited to reproduce the control file. View the alert log todetermine the name and location of the trace file.
这个命令是一个SQL脚本，生成一个跟踪文件，它能够捕获或者编辑来重新生成控制文件，查看告警日志来确定该跟踪文件的名称和位置。
See Also:
o Oracle Database Backup and Recovery User‘s Guide for more information on backing up your control files
o "Viewingthe Alert Log"
5.1.1 Eg.使用SQL备份控制文件，并从中得到创建控制文件脚本
1.备份控制文件
SYS@db12c>alter databasebackup controlfile to trace;
Databasealtered.
2找出trace文件，两种方法，一是按照时间查看trace文件，二是使用SQL脚本找出trace文件
SYS@db12c>showparameter user_dump_dest
NAME TYPE VALUE
----------------------------------------------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/db12c/db12c/trace
SYS@db12c>
[oracle@db12c~]$ cd/u01/app/oracle/diag/rdbms/db12c/db12c/trace
[oracle@db12ctrace]$ ls -alt
total 1124
-rw-r----- 1 oracle oinstall 105006 Aug 4 14:59 alert_db12c.log
-rw-r----- 1 oracle oinstall 13190 Aug 4 14:59 db12c_ora_2206.trc
-rw-r----- 1 oracle oinstall 276 Aug 4 14:59 db12c_ora_2206.trm
或者使用SQL语句来查找刚生成的跟踪文件
SYS@db12c>SELECT a.VALUE||b.symbol|| c.instance_name|| ‘_ora_‘ ||d.spid|| ‘.trc‘ trace_file
2 FROM(SELECT VALUE FROM v$parameter WHERE name = ‘user_dump_dest‘) a,
3 (SELECT SUBSTR (VALUE, -6, 1)symbol FROM v$parameter WHERE name = ‘user_dump_dest‘) b,
4 (SELECT instance_name FROMv$instance) c,
5 (SELECT spid FROM v$sessions,v$process p,v$mystat m WHERE s.paddr= p.addr AND s.sid = m.sid ANDm.statistic#= 0) d;
TRACE_FILE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db12c/db12c/trace/db12c_ora_2206.trc
SYS@db12c>
3.查看跟踪文件即控制文件内容
SYS@db12c>!cat/u01/app/oracle/diag/rdbms/db12c/db12c/trace/db12c_ora_2206.trc
Trace file/u01/app/oracle/diag/rdbms/db12c/db12c/trace/db12c_ora_2206.trc
Oracle Database12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With thePartitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME =/u01/app/oracle/product/12.1/db_1
Systemname: Linux
Nodename: db12c.localdomain
Release: 3.8.13-16.2.1.el6uek.x86_64
Version: #1 SMP Thu Nov 7 17:01:44 PST 2013
Machine: x86_64
Instance name:db12c
Redo threadmounted by this instance: 1
Oracle processnumber: 7
Unix processpid: 2206, image: oracle@db12c.localdomain (TNS V1-V3)
*** 2014-08-04 14:32:15.494
*** SESSIONID:(1.5) 2014-08-04 14:32:15.494
*** CLIENT ID:()2014-08-04 14:32:15.494
*** SERVICENAME:() 2014-08-04 14:32:15.494
*** MODULENAME:(sqlplus@db12c.localdomain (TNS V1-V3)) 2014-08-04 14:32:15.494
*** ACTIONNAME:() 2014-08-04 14:32:15.494
*** CONTAINERID:(1) 2014-08-04 14:32:15.494
Thread 1checkpoint: logseq 24, block 2, scn 2036670
cache-lowrba: logseq 24, block 16289
on-disk rba: logseq 24, block 16460, scn 2046562
startrecovery at logseq 24, block 16289, scn 0
*** 2014-08-0414:32:15.659
Started writingzeroblks thread 1 seq 24 blocks 16460-16467
*** 2014-08-0414:32:15.818
Completedwriting zeroblks thread 1 seq 24
==== Redo readstatistics for thread 1 ====
Total physicalreads (from disk and memory): 4096Kb
-- Redoread_disk statistics --
Read rate(ASYNC): 85Kb in 0.33s => 0.25 Mb/sec
Longest record:1Kb, moves: 0/275 (0%)
Change moves:3/35 (8%), moved: 0Mb
Longest LWN:9Kb, moves: 0/38 (0%), moved: 0Mb
Last redo scn:0x0000.001f3a61 (2046561)
----------------------------------------------
----- RecoveryHash Table Statistics ---------
Hash tablebuckets = 262144
Longest hashchain = 1
Average hashchain = 46/46 = 1.0
Max compares perlookup = 1
Avg compares perlookup = 434/528 = 0.8
----------------------------------------------
*** 2014-08-0414:32:15.877
KCRA: startrecovery claims for 46 data blocks
*** 2014-08-0414:32:16.056
KCRA: blocksprocessed = 46/46, claimed = 46, eliminated = 0
*** 2014-08-0414:32:16.057
Recovery ofOnline Redo Log: Thread 1 Group 3 Seq 24 Reading mem 0
*** 2014-08-0414:32:16.185
Completed redoapplication of 0.05MB
*** 2014-08-0414:32:17.287
Completedrecovery checkpoint
----- RecoveryHash Table Statistics ---------
Hash tablebuckets = 262144
Longest hashchain = 1
Average hashchain = 46/46 = 1.0
Max compares perlookup = 1
Avg compares perlookup = 383/480 = 0.8
----------------------------------------------
Recovery setsnab of thread 1 seq 24 to 16460 with 8 zeroblks
*** 2014-08-04 14:32:22.214
Count ofofsmtab$: 0 entries
*** 2014-08-0414:32:24.991
08/04/201414:32:23 08/04/2014 14:32:232014-08-04 14:32:24.991: [ OCRMSG]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2014-08-0414:32:24.992: [ OCRMSG]GIPC error [29] msg [gipcretConnectionRefused]
2014-08-0414:32:24.992: [ OCRMSG]prom_connect: error while waiting for connectioncomplete [24]
2014-08-0414:32:24.992: [ OCRMSG]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2014-08-0414:32:24.993: [ OCRMSG]GIPC error [29] msg [gipcretConnectionRefused]
2014-08-0414:32:24.993: [ OCRMSG]prom_connect: error while waiting for connectioncomplete [24]
2014-08-0414:32:24.994: [ OCRMSG]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2014-08-0414:32:24.994: [ OCRMSG]GIPC error [29] msg [gipcretConnectionRefused]
2014-08-0414:32:24.994: [ OCRMSG]prom_connect: error while waiting for connectioncomplete [24]
2014-08-0414:32:24.995: [ OCRMSG]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2014-08-0414:32:24.995: [ OCRMSG]GIPC error [29] msg [gipcretConnectionRefused]
2014-08-0414:32:24.995: [ OCRMSG]prom_connect: error while waiting for connectioncomplete [24]
*** 2014-08-0414:32:25.878
kskpdbnfy: openpdb 2 state 0
*** 2014-08-0414:32:31.106
08/04/2014 14:32:30DD7C8BF397900F9AE04325AAE80A464F
*** 2014-08-0414:32:34.339
Param= 16, AT=4
KZAM_FILE_MAX_SIZEfor 4 is 10000
Param= 16, AT=8
KZAM_FILE_MAX_SIZEfor 8 is 10000
Param= 16, AT=51
KZAM_FILE_MAX_SIZEfor 51 is 10000
Param= 17, AT=4
KZAM_FILE_MAX_AGEfor 4 is 5
Param= 17, AT=8
KZAM_FILE_MAX_AGEfor 8 is 5
Param= 17, AT=51
KZAM_FILE_MAX_AGEfor 51 is 5
Param= 22, AT=1
KZAM_DB_TABLESPACE_LOCfor 1 is SYSAUX
Param= 22, AT=2
KZAM_DB_TABLESPACE_LOCfor 2 is SYSAUX
Param= 22, AT=51
KZAM_DB_TABLESPACE_LOCfor 51 is SYSAUX
Param= 23, AT=1
KZAM_DB_DEL_BATCH_SIZEfor 1 is 10000
Param= 23, AT=2
KZAM_DB_DEL_BATCH_SIZEfor 2 is 10000
Param= 26, AT=4
KZAM_FILE_DEL_BATCH_SIZEfor 15 is 1000
Param= 26, AT=8
KZAM_FILE_DEL_BATCH_SIZEfor 15 is 1000
Param= 33, AT=51
KZAMKZAM_ANG_WRITE_MODE
*** 2014-08-0414:59:24.154
-- The followingare current System-scope REDO Log Archival related
-- parametersand can be included in the database initialization file.
--
--LOG_ARCHIVE_DEST=‘‘
--LOG_ARCHIVE_DUPLEX_DEST=‘‘
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
--DB_UNIQUE_NAME="db12c"
--
--LOG_ARCHIVE_CONFIG=‘SEND, RECEIVE, NODG_CONFIG‘
--LOG_ARCHIVE_MAX_PROCESSES=4
--STANDBY_FILE_MANAGEMENT=MANUAL
--STANDBY_ARCHIVE_DEST=?/dbs/arch
-- 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 twosets of SQL statements, each of which creates a new
-- control fileand uses it to open the database. The first set opens
-- the databasewith the NORESETLOGS option and should be used only if
-- thecurrent versions of all online logs are available. The second
-- set opens thedatabase with the RESETLOGS option and should be used
-- if onlinelogs are unavailable.
-- Theappropriate set of statements can be copied from the trace into
-- a scriptfile, edited as necessary, and executed when there is a
-- need tore-create the control file.
--以下有两种SQL语句，每个可以创建一个新的控制文件，并且使用它来打开数据库，第一个使用NORESETLOGS选项来打开数据--库，应用于当前版本的所有的在线日志可用。
--第二个使用RESETLOGS选项来打开数据库，应用于在线日志不可用，
--适当的语句可以从跟踪文件来生成一个脚本文件，需要时编辑它，并且当有需要时执行重新创建控制文件。
--
-- Set #1. NORESETLOGScase（这条语句在前面已经使用过）
--
--以下命令可以创建一个新的控制文件，并且可使用它来打开数据库。RMAN使用的数据会丢失。
--离线介质恢复可能需要额外的日志
--如果当前版本的所有的在线日志是可用的，可以使用下面语句来创建，装载控制文件后，下面的SQL语句将数据库置为保护模式。
-- The following commands will create a newcontrol file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for mediarecovery of offline
-- Use this only if the current versions of allonline logs are
-- available.
-- After mounting the created controlfile, thefollowing SQL
-- statement will place the database in theappropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TOMAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATECONTROLFILE REUSE DATABASE "DB12C" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
‘/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_1_9xcyobx8_.log‘,
‘/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_1_9xcyoc3q_.log‘
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
‘/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_2_9xcyogfh_.log‘,
‘/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_2_9xcyogj8_.log‘
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
‘/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_3_9xcyokx3_.log‘,
‘/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_3_9xcyol09_.log‘
) SIZE 50M BLOCKSIZE 512
--STANDBY LOGFILE
DATAFILE
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcykmom_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcygnfw_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_9xcynn1n_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcyozo6_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xcynlwd_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcyozo1_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xczq0m9_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xczq0ls_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xczq0md_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_example_9xczq0m6_.dbf‘
CHARACTERSET AL32UTF8
;
---以上为创建控制文件的脚本
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existingfilenames on
-- disk. Any one log file from each branch canbe used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE‘/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_04/o1_mf_1_1_%u_.arc‘;
-- ALTER DATABASE REGISTER LOGFILE‘/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_04/o1_mf_1_1_%u_.arc‘;
-- Recovery is required if any of the datafilesare restored backups,
-- or if the last shutdown was not normal orimmediate.
RECOVER DATABASE
-- All logs need archiving and a log switch isneeded.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Open all the PDBs.
ALTER PLUGGABLE DATABASE ALL OPEN;
-- Commands to add tempfiles to temporarytablespaces.
-- Online tempfiles have complete spaceinformation.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_temp_9xcyovxf_.tmp‘
SIZE 92274688 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
--这里是要注意的地方，重建控制文件的时候，不能写上临时表空间，等控制文件创建完毕之后，在手工的执行SQL加上临时表空间。
ALTER SESSION SET CONTAINER = PDB$SEED;
ALTER TABLESPACE TEMP ADD TEMPFILE‘/u01/app/oracle/oradata/DB12C/datafile/pdbseed_temp01.dbf‘
SIZE 91226112 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = DB12CCD;
ALTER TABLESPACE TEMP ADD TEMPFILE‘/u01/app/oracle/oradata/DB12C/datafile/db12ccd_temp01.dbf‘ REUSE;
ALTER SESSION SET CONTAINER = CDB$ROOT;
-- End of tempfile additions.
--
-- Set #2. RESETLOGScase
--以下命令会创建一个新的控制文件，并使用它来打开数据库
--RMAN使用的数据会丢失
--在线日志的内容会丢失，所有备份会变得无效。
--使用RESETLOGS来创建控制文件仅仅只在在线日志已经损坏的情况下
--控制文件创建之后，装载，使用以下的SQL语句来将数据库置为合适的保护模式
-- The following commands will create a newcontrol file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost andall backups will
-- be invalidated. Use this only if online logsare damaged.
-- After mounting the created controlfile, thefollowing SQL
-- statement will place the database in theappropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TOMAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATECONTROLFILE REUSE DATABASE"DB12C" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
‘/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_1_9xcyobx8_.log‘,
‘/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_1_9xcyoc3q_.log‘
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
‘/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_2_9xcyogfh_.log‘,
‘/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_2_9xcyogj8_.log‘
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
‘/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_3_9xcyokx3_.log‘,
‘/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_3_9xcyol09_.log‘
) SIZE 50M BLOCKSIZE 512
--STANDBY LOGFILE
DATAFILE
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcykmom_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcygnfw_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_9xcynn1n_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xcyozo6_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xcynlwd_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xcyozo1_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9xczq0m9_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9xczq0ls_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9xczq0md_.dbf‘,
‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_example_9xczq0m6_.dbf‘
CHARACTERSET AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existingfilenames on
-- disk. Any one log file from each branch canbe used to
-- re-create incarnation records.
--在磁盘上已经存在的文件名一定会改变。任何一个日志文件都将重新记录
-- ALTER DATABASE REGISTER LOGFILE‘/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_04/o1_mf_1_1_%u_.arc‘;
-- ALTER DATABASE REGISTER LOGFILE‘/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_08_04/o1_mf_1_1_%u_.arc‘;
-- Recovery is required if any of the datafilesare restored backups,
-- or if the last shutdown was not normal orimmediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing theonline logs.
ALTER DATABASE OPEN RESETLOGS;
--如果使用resetlogs打开的数据库，就需要对DB做一次备份。
-- Open all the PDBs.
ALTER PLUGGABLE DATABASE ALL OPEN;
-- Commands to add tempfiles to temporarytablespaces.
-- Online tempfiles have complete spaceinformation.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE‘/u01/app/oracle/oradata/DB12C/datafile/o1_mf_temp_9xcyovxf_.tmp‘
SIZE 92274688 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = PDB$SEED;
ALTER TABLESPACE TEMP ADD TEMPFILE‘/u01/app/oracle/oradata/DB12C/datafile/pdbseed_temp01.dbf‘
SIZE 91226112 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = DB12CCD;
ALTER TABLESPACE TEMP ADD TEMPFILE‘/u01/app/oracle/oradata/DB12C/datafile/db12ccd_temp01.dbf‘ REUSE;
ALTER SESSION SET CONTAINER = CDB$ROOT;
-- End of tempfile additions.
--
SYS@db12c>
6 Recoveringa Control File Using a Current Copy使用当前副本来恢复控制文件
即是使用操作系统命令来进行操作，再在CONTROL_FILES初始化参数文件修改，使用pfile或者spfile都可以
This section presents ways that you can recover your controlfile from a current backup or from a multiplexed copy.
6.1 Recovering fromControl File Corruption Using a Control File Copy使用现有控制文件副本来恢复控制文件
This procedure assumes that one of the control files specifiedin the CONTROL_FILES parameter is corrupted, that the controlfile directory is still accessible, and that you have a multiplexed copy of thecontrol file.
这个假设在CONTROL_FILES参数里的其中一个控制文件损坏，而控制文件的目录还是可以访问的，那么你可以使用多路复用拷贝一个好的控制文件到这个目录
关闭数据库，操作命令拷贝控制文件到损坏但可访问的控制文件的目录，然后启动数据库。
1. With the instanceshut down, use an operating system command to overwrite the bad control filewith a good copy:
% cp /u03/oracle/prod/control03.ctl /u02/oracle/prod/control02.ctl
2. Start SQL*Plus andopen the database:
SQL> STARTUP
6.2 Recovering fromPermanent Media Failure Using a Control File Copy使用控制文件副本来恢复永久的介质错误
This procedure assumes that one of the control files specifiedin the CONTROL_FILES parameter is inaccessible due to apermanent media failure and that you have a multiplexed copy of the controlfile.
假设在CONTROL_FILES参数里的其中一个控制文件由于永久的介质错误而不能访问，且你有多路复用的控制文件的副本。
关闭数据库，使用操作系统拷贝命令进行拷贝到一个新的位置，修改CONTROL_FILES参数，重启数据库。
1. With the instanceshut down, use an operating system command to copy the current copy of thecontrol file to a new, accessible location:
关闭数据库，使用操作系统命令来拷贝当前控制文件副本到一个新的能访问的位置
% cp /u01/oracle/prod/control01.ctl /u04/oracle/prod/control03.ctl
2. Edit the CONTROL_FILES parameter in the initializationparameter file to replace the bad location with the new location:
在初始化参数文件里编辑CONTROL_FILES参数，用新的位置替换坏的位置
CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
/u02/oracle/prod/control02.ctl,
/u04/oracle/prod/control03.ctl)
3. Start SQL*Plus andopen the database:
启动数据库
SQL> STARTUP
If you have multiplexed control files, you can get the databasestarted up quickly by editing the CONTROL_FILES initialization parameter. Remove thebad control file from CONTROL_FILES setting and you can restart thedatabase immediately. Then you can perform the reconstruction of the badcontrol file and at some later time shut down and restart the database afterediting the CONTROL_FILES initialization parameter to includethe recovered control file.
如果你有多路复用控制文件，你可以编辑CONTROL_FILES参数来快速的打开数据库，在CONTROL_FILES设置里移走坏的控制文件，然后就可以重新启动数据库，你可以重建坏的控制文件，然后关闭数据库，编辑CONTROL_FILES初始化参数后重新启动数据库，包括已经恢复的控制文件。
总结来说，CONTROL_FILES参数里的控制文件与OS里面的控制文件保持一致，或者说CONTROL_FILES参数里指定的必须在物理磁盘上有，而物理磁盘上可以没有，即可打开数据库。
7 Dropping Control Files删除控制文件
You want to dropcontrol files from the database, for example, if the location of a control fileis no longer appropriate. Remember that the database should have at least twocontrol files at all times.
如果你想从数据库中删除控制文件，例如，一个控制文件的位置不再合适。记住数据库应该至少始终包含两个控制文件。
1. Shut down thedatabase.
关闭数据库
2. Edit the CONTROL_FILES parameter in the database initializationparameter file to delete the old control file name.
编辑CONTROL_FILES参数来删除旧的控制文件
3. Restart thedatabase.
重新启动数据库
Note:
This operation does not physically delete the unwanted controlfile from the disk. Use operating system commands to delete the unnecessaryfile after you have dropped the control file from the database.
注意，这个操作，在物理磁盘上没有删除那个旧的控制文件，在你在数据库中删除控制文件后需使用操作系统命令来删除不需要的文件。
7.1.1 使用spfile来删除控制文件
1.查看当前控制文件状态
SYS@db12c>select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl
/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl
/u01/app/oracle/oradata/DB12C/controlfile/control02.ctl
2.在spfile中使用alter system set control_files来修改
SYS@db12c>alter system set
control_files=‘/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl‘,‘/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl‘scope=spfile;
System altered.
3.关闭，然后启动数据库,并查看修改后的控制文件
SYS@db12c>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@db12c>startup
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 788530952 bytes
Database Buffers 452984832 bytes
Redo Buffers 8859648 bytes
Database mounted.
Database opened.
SYS@db12c>select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl
/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl
SYS@db12c>
4物理在磁盘上删除(虽然不影响数据库的运行，但建议磁盘上与CONTROL_FILES参数保持一致)
SYS@db12c>! rm -rf/u01/app/oracle/oradata/DB12C/controlfile/control02.ctl
8 ControlFiles Data Dictionary Views控制文件数据字典视图
The following views display information about control files:
以下视图显示控制文件的相关信息
View | Description |
| Displays database information from the control file 从控制文件中显示数据库的信息 |
| Lists the names of control files 控制文件名清单 |
| Displays information about control file record sections 控制文件记录信息 |
| Displays the names of control files as specified in the 显示在CONTROL_FILES初始化参数里指定的控制文件名 |
This example lists the names of the control files.
查看控制文件的名称及位置（两种方法）：
SYS@db12c>descv$controlfile;
Name Null? Type
--------------------------------------------------------------------------------------------------------------------- --------------------------------------------------
STATUS VARCHAR2(7)
NAME VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
BLOCK_SIZE NUMBER
FILE_SIZE_BLKS NUMBER
CON_ID NUMBER
SYS@db12c>colname for a80
SYS@db12c>select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID
--------------------------------------------------------------------------------------- --- ---------- ------------- ----------
/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl NO 16384 1096 0
/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl YES 16384 1096 0
/u01/app/oracle/oradata/DB12C/controlfile/control02.ctl NO 16384 1096 0
SYS@db12c>show parameter control_files
NAME TYPE VALUE
------------------------------------ -----------------------------------------
control_files string /u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9xcyo7gc_.ctl,
/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9xcyo80x_.ctl,/u01/app/oracle/oradata/DB12C/controlfile/control02.ctl
SYS@db12c>
---
查询控制文件中可记载的最大数据文件个数
SYS@db12c>desc V$CONTROLFILE_RECORD_SECTION
Name Null? Type
------------------------------------------------------------- ------------------------------------
TYPE VARCHAR2(28)
RECORD_SIZE NUMBER
RECORDS_TOTAL NUMBER
RECORDS_USED NUMBER
FIRST_INDEX NUMBER
LAST_INDEX NUMBER
LAST_RECID NUMBER
CON_ID NUMBER
SYS@db12c>select * from V$CONTROLFILE_RECORD_SECTION;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEXLAST_RECID CON_ID
---------------------------- ----------- ------------- ----------------------- ---------- ---------- ----------
DATABASE 316 1 1 0 0 0 0
CKPT PROGRESS 8180 11 0 0 0 0 0
REDO THREAD 256 8 1 0 0 0 0
REDO LOG 72 16 3 0 0 15 0
DATAFILE 520 1024 11 0 0 123 0
FILENAME 524 4146 20 0 0 0 0
TABLESPACE 68 1024 13 0 0 9 0
TEMPORARY FILENAME 56 1024 3 0 0 165 0
RMAN CONFIGURATION 1108 50 0 0 0 0 0
LOG HISTORY 56 292 25 1 25 25 0
OFFLINE RANGE 200 1063 8 1 8 8 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEXLAST_RECID CON_ID
---------------------------- ----------- ------------- ----------------------- ---------- ---------- ----------
ARCHIVED LOG 584 28 14 1 14 14 0
BACKUP SET 96 1022 1 1 1 1 0
BACKUP PIECE 780 1006 1 1 1 1 0
BACKUP DATAFILE 200 1063 1 1 1 1 0
BACKUP REDOLOG 76 215 0 0 0 0 0
DATAFILE COPY 736 1000 6 1 6 6 0
BACKUP CORRUPTION 44 1115 0 0 0 0 0
COPY CORRUPTION 40 1227 0 0 0 0 0
DELETED OBJECT 20 818 8 1 8 8 0
PROXY COPY 928 1004 0 0 0 0 0
BACKUP SPFILE 124 131 1 1 1 1 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEXLAST_RECID CON_ID
---------------------------- ----------- ------------- ----------------------- ---------- ---------- ----------
DATABASE INCARNATION 56 292 2 1 2 2 0
FLASHBACK LOG 84 2048 0 0 0 0 0
RECOVERY DESTINATION 180 1 1 0 0 0 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0 0 0
REMOVABLE RECOVERY FILES 32 1000 0 0 0 0 0
RMAN STATUS 116 141 6 1 6 6 0
THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0 0
MTTR 100 8 1 0 0 0 0
DATAFILE HISTORY 568 57 0 0 0 0 0
STANDBY DATABASE MATRIX 400 31 31 0 0 0 0
GUARANTEED RESTORE POINT 212 2048 0 0 0 0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEXLAST_RECID CON_ID
---------------------------- ----------- ------------- ----------------------- ---------- ---------- ----------
RESTORE POINT 212 2083 0 0 0 0 0
DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0 0
ACM OPERATION 104 64 9 0 0 0 0
FOREIGN ARCHIVED LOG 604 1002 0 0 0 0 0
PDB RECORD 684 10 3 0 0 9 0
AUXILIARY DATAFILE COPY 584 128 0 0 0 0 0
MULTI INSTANCE REDO APPLY 556 1 0 0 0 0 0
PDBINC RECORD 144 113 0 0 0 0 0
41 rows selected.
SYS@db12c>