--关闭数据库并重启
SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
ORA-00205: error in identifying control file, check alert log for more info
遭遇ora-00205，可能是刚才设置的参数有问题，查看alert log来进一步分析
SQL> !tail -100f /u01/app/oracle/diag/rdbms/zlm11g/zlm11g/trace/alert_zlm11g.log
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Fri Nov 07 01:19:26 2014
Stopping background process VKTM
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Fri Nov 07 01:19:28 2014
Instance shutdown complete
Fri Nov 07 01:19:31 2014
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: zlm
Release: 2.6.32-200.13.1.el5uek
Version: #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine: x86_64
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfilezlm11g.ora
System parameters with non-default values:
processes = 150
memory_target = 800M
control_files = "/u01/app/oracle/oradata/zlm11g/control01.ctl,/u01/oracle/fast_recovery_area/zlm11g/control02.ctl,/u01/control03.ctl,/u01/control04.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_format = "%t_%s_%r.dbf"
db_create_file_dest = "/u01/app/oracle/oradata/zlm11g/"
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 4122M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=zlm11gXDB)"
audit_file_dest = "/u01/app/oracle/admin/zlm11g/adump"
audit_trail = "DB"
db_name = "zlm11g"
open_cursors = 300
diagnostic_dest = "/u01/app/oracle"
Fri Nov 07 01:19:32 2014
PMON started with pid=2, OS id=30640
Fri Nov 07 01:19:32 2014
PSP0 started with pid=3, OS id=30644
Fri Nov 07 01:19:33 2014
VKTM started with pid=4, OS id=30996 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Fri Nov 07 01:19:33 2014
GEN0 started with pid=5, OS id=31003
Fri Nov 07 01:19:33 2014
DIAG started with pid=6, OS id=31007
Fri Nov 07 01:19:33 2014
DBRM started with pid=7, OS id=31011
Fri Nov 07 01:19:33 2014
DIA0 started with pid=8, OS id=31016
Fri Nov 07 01:19:33 2014
MMAN started with pid=9, OS id=31020
Fri Nov 07 01:19:33 2014
DBW0 started with pid=10, OS id=31024
Fri Nov 07 01:19:33 2014
LGWR started with pid=11, OS id=31028
Fri Nov 07 01:19:33 2014
CKPT started with pid=12, OS id=31033
Fri Nov 07 01:19:33 2014
SMON started with pid=13, OS id=31038
Fri Nov 07 01:19:33 2014
RECO started with pid=14, OS id=31042
Fri Nov 07 01:19:33 2014
MMON started with pid=15, OS id=31048
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Nov 07 01:19:33 2014
MMNL started with pid=16, OS id=31052
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Fri Nov 07 01:19:33 2014
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/zlm11g/control01.ctl,/u01/oracle/fast_recovery_area/zlm11g/control02.ctl,/u01/control03.ctl,/u01/control04.ctl' --这里参数后面的引号写错了，应该每个镜像都要单独用引号
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri Nov 07 01:19:34 2014
Checker run found 1 new persistent data failures
--关闭数据库用pfile启动
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup pfile=$ORACLE_HOME/dbs/initzlm11g.ora
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
--创建spfile
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/zlm11g
/control01.ctl, /u01/app/oracl
e/fast_recovery_area/zlm11g/co
ntrol02.ctl, /u01/control03.ct
l
SQL> select table_name from dict where table_name like '%CONTROL%';
TABLE_NAME
------------------------------
DBA_HIST_WR_CONTROL
DBA_REPRESOL_STATS_CONTROL
USER_REPRESOL_STATS_CONTROL
ALL_REPRESOL_STATS_CONTROL
V$SESSION_FIX_CONTROL
V$SYSTEM_FIX_CONTROL
GV$DLM_TRAFFIC_CONTROLLER
GV$SESSION_FIX_CONTROL
GV$SYSTEM_FIX_CONTROL
V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION
V$DIAG_ADR_CONTROL
GV$CONTROLFILE
GV$CONTROLFILE_RECORD_SECTION
V$DLM_TRAFFIC_CONTROLLER
V$BACKUP_CONTROLFILE_DETAILS
V$BACKUP_CONTROLFILE_SUMMARY
17 rows selected.
SQL> desc v$controlfile
Name Null? Type
----------------------------------------- -------- ----------------------------
STATUS VARCHAR2(7)
NAME VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
BLOCK_SIZE NUMBER
FILE_SIZE_BLKS NUMBER
SQL> set lin 130
SQL> select status,name,is_recovery_dest_file,block_size,file_size_blks from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------- --- ---------- --------------
/u01/app/oracle/orad NO 16384 632
ata/zlm11g/control01
.ctl
/u01/app/oracle/fast NO 16384 632
_recovery_area/zlm11
g/control02.ctl
/u01/control03.ctl NO 16384 632
可以查看v$controlfile视图来获得当前控制文件信息，包括位置、名称、大小等
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/zlm11g
/control01.ctl, /u01/app/oracl
e/fast_recovery_area/zlm11g/co
ntrol02.ctl, /u01/control03.ct
l
由于刚才参数格式设置错误，此时control04.ctl还没有被正确修改生效，因此看不到
SQL> alter system set control_files='/u01/app/oracle/oradata/zlm11g/control01.ctl','/u01/app/oracle/fast_recovery_area/zlm11g/control02.ctl','/u01/control03.ctl','/u01/control04.ctl' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
ORA-00214: control file '/u01/app/oracle/oradata/zlm11g/control01.ctl' version 9318 inconsistent with file '/u01/control04.ctl'
version 9283
再次启动数据库发现，用指定路径方式备份控制文件与系统原有的控制文件不一致，版本号不同
猜想可能经过多次打开关闭数据库，现在需要重新创建一次第4个控制文件
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup pfile=$ORACLE_HOME/dbs/initzlm11g.ora
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL> alter database backup controlfile to /u01/control04.ctl;
alter database backup controlfile to /u01/control04.ctl
*
ERROR at line 1:
ORA-02236: invalid file name --是因为没有加引号
SQL> alter database backup controlfile to '/u01/control04.ctl';
alter database backup controlfile to '/u01/control04.ctl'
*
ERROR at line 1:
ORA-01580: error creating control backup file /u01/control04.ctl
ORA-27038: created file already exists --文件已经存在，之前备份的文件并未删除
Additional information: 1