042-Your database is having two control files, three redo log file groups with two members in each g

93. Your database is having two control files, three redo log file groups with two members in each group. Failure of which file would cause an instance to shut down?
A) any control file
B) any archive log file
C) one of the redo log members
D) loss of the initialization parameter file
E) any data file belonging to the default permanent tablespace

1,修改control file 數量從預設的3個變為 題中描述的2個。
[oracle@test ~]$ sqlplus / as sysdba

SQL> show parameter db_name; #看下是那個資料庫

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_name string gzy
SQL> create pfile from spfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2，修改pfile 檔案，在第三個control file 前加‘#’遮蔽它。
[root@test gzy]# cd /opt/ora10g/product/dbs/
[root@test dbs]# vi initgzy.ora

gzy.__db_cache_size=134217728
gzy.__java_pool_size=4194304
gzy.__large_pool_size=4194304
gzy.__shared_pool_size=113246208
gzy.__streams_pool_size=0
*.audit_file_dest='/opt/ora10g/admin/gzy/adump'
*.background_dump_dest='/opt/ora10g/admin/gzy/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/ora10g/oradata/gzy/gzy/control01.ctl','/opt/ora10g/oradata/gzy/gzy/control02.ctl',
#'/opt/ora10g/oradata/gzy/gzy/control03.ctl'
*.core_dump_dest='/opt/ora10g/admin/gzy/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='gzy'
*.db_recovery_file_dest='/opt/ora10g/flash_recovery_area'
*.db_recovery_file_dest_size=10737418240
*.dispatchers='(PROTOCOL=TCP) (SERVICE=gzyXDB)'
*.job_queue_processes=10
*.lock_sga=TRUE
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=307232768
*.pre_page_sga=TRUE
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=524288000
*.sga_target=264241152
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/ora10g/admin/gzy/udump'

3，重新啟動資料庫，並確認 control file的數量；
SQL> startup
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 381681888 bytes
Database Buffers 134217728 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string /opt/ora10g/oradata/gzy/gzy/co
ntrol01.ctl, /opt/ora10g/orada
ta/gzy/gzy/control02.ctl
SQL> show parameter pfile #值為空，表明用的是pfile啟動的資料庫。

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string
SQL>

4，在資料庫OPEN的狀態下，將2個control file 中的一個 改名；
[root@test gzy]# pwd
/opt/ora10g/oradata/gzy/gzy
[root@test gzy]# ll
total 1757068
-rw-r----- 1 oracle oinstall 7094272 Apr 1 16:02 control01.ctl
-rw-r----- 1 oracle oinstall 7094272 Apr 1 16:02 control02.ctl
-rw-r----- 1 oracle oinstall 7094272 Apr 1 15:52 control03.ctl
-rw-r----- 1 oracle oinstall 52429312 Apr 1 16:02 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Apr 1 15:53 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Apr 1 15:53 redo03.log
-rw-r----- 1 oracle oinstall 346038272 Apr 1 16:00 sysaux01.dbf
-rw-r----- 1 oracle oinstall 513810432 Apr 1 15:58 system01.dbf
-rw-r----- 1 oracle oinstall 513810432 Feb 8 12:22 system01.dbf.2
-rw-r----- 1 oracle oinstall 20979712 Apr 1 10:26 temp01.dbf
-rw-r----- 1 oracle oinstall 52436992 Apr 1 15:53 test01.dbf
-rw-r----- 1 oracle oinstall 10493952 Apr 1 15:53 test02.dbf
-rw-r----- 1 oracle oinstall 83894272 Apr 1 16:00 undotbs01.dbf
-rw-r----- 1 oracle oinstall 97001472 Apr 1 15:53 users01.dbf


[root@test gzy]# mv control02.ctl control02.ctl.bk
[root@test gzy]#
[root@test gzy]#
[root@test gzy]#
[root@test gzy]# ll
total 1757068
-rw-r----- 1 oracle oinstall 7094272 Apr 1 16:02 control01.ctl
-rw-r----- 1 oracle oinstall 7094272 Apr 1 16:02 control02.ctl.bk
-rw-r----- 1 oracle oinstall 7094272 Apr 1 15:52 control03.ctl
-rw-r----- 1 oracle oinstall 52429312 Apr 1 16:02 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Apr 1 15:53 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Apr 1 15:53 redo03.log
-rw-r----- 1 oracle oinstall 346038272 Apr 1 16:02 sysaux01.dbf
-rw-r----- 1 oracle oinstall 513810432 Apr 1 15:58 system01.dbf
-rw-r----- 1 oracle oinstall 513810432 Feb 8 12:22 system01.dbf.2
-rw-r----- 1 oracle oinstall 20979712 Apr 1 10:26 temp01.dbf
-rw-r----- 1 oracle oinstall 52436992 Apr 1 15:53 test01.dbf
-rw-r----- 1 oracle oinstall 10493952 Apr 1 15:53 test02.dbf
-rw-r----- 1 oracle oinstall 83894272 Apr 1 16:00 undotbs01.dbf
-rw-r----- 1 oracle oinstall 97001472 Apr 1 15:53 users01.dbf

SQL> desc tt; 可以查詢，資料庫正常執行中。
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
ID NUMBER(38)
NAME VARCHAR2(10)


資料庫OPEN狀態下，control丟失是沒有問題的。

再看下，關閉再開啟會發生什麼？
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> statup
SP2-0042: unknown command "statup" - rest of line ignored.
SQL> startup
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 381681888 bytes
Database Buffers 134217728 bytes
Redo Buffers 7168000 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> select status from v$instance;

STATUS
------------------------
STARTED

[root@test gzy]# mv control02.ctl.bk control02.ctl

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------------------
OPEN


將所有的control file 全部改名；
[root@test gzy]# mv control03.ctl control03.ctl.bk
[root@test gzy]# ll
total 1757068
-rw-r----- 1 oracle oinstall 7094272 Apr 1 16:09 control01.ctl.bk
-rw-r----- 1 oracle oinstall 7094272 Apr 1 16:09 control02.ctl.bk
-rw-r----- 1 oracle oinstall 7094272 Apr 1 15:52 control03.ctl.bk
-rw-r----- 1 oracle oinstall 52429312 Apr 1 16:09 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Apr 1 16:08 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Apr 1 16:08 redo03.log
-rw-r----- 1 oracle oinstall 346038272 Apr 1 16:09 sysaux01.dbf
-rw-r----- 1 oracle oinstall 513810432 Apr 1 16:09 system01.dbf
-rw-r----- 1 oracle oinstall 513810432 Feb 8 12:22 system01.dbf.2
-rw-r----- 1 oracle oinstall 20979712 Apr 1 10:26 temp01.dbf
-rw-r----- 1 oracle oinstall 52436992 Apr 1 16:08 test01.dbf
-rw-r----- 1 oracle oinstall 10493952 Apr 1 16:08 test02.dbf
-rw-r----- 1 oracle oinstall 83894272 Apr 1 16:09 undotbs01.dbf
-rw-r----- 1 oracle oinstall 97001472 Apr 1 16:08 users01.dbf

檢視control 的SCN資訊；
SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3471461
2 3471461
3 3471461
4 3471461
5 3471461
6 3471461
7 3472018

SQL> alter system checkpoint;

System altered.


SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3474724
2 3474724
3 3474724
4 3474724
5 3474724
6 3474724
7 3474724

7 rows selected.

control file 的SCN變大了。但是control已經被改名了。
說明ORACLE還是能讀寫 control檔案的。為什麼。
上GOOGLE。

%E4%B8%8B%E5%88%A0%E9%99%A4oracle%E6%8E%A7%E5%88%B6%E6%96%87%E4%BB%B6controlfile%E4%B8%BA%E4%BB%80%E4%B9%88%E5%AE%9E%E4%BE%8Binstance%E6%B2%A1%E6%9C%89%E7%AB%8B%E5%8D%B3%E5%A5%94%E6%BA%83.html

究其根本原因是在Linux/Unix上Read 或 Write一個檔案，程式都會開啟一個與此檔案相關聯的 開啟檔案描述符 Open File Descriptors (a file descriptor (FD) is an abstract indicator for accessing a file. The term is generally used in POSIX operating systems.In POSIX, a file descriptor is an integer, specifically of the C type int.)

而在Linux/Unix上當一個檔案被刪除時，它只是簡單地被”unlink”即刪除一個硬連結hard link； 實際包含 檔案資料的inode 在所有與該檔案關聯的 開啟檔案描述符(Open File Descriptors)被關閉之前 都不會被實際刪除， 已經獲得 開啟檔案描述符的程式 只要不用close函式關閉掉這些描述符， 都可以繼續正常地對檔案進行讀寫。 當所有該檔案 管理的 開啟檔案符都被 關閉時 檔案才真正意義上被刪除。

總結，採用改名來模擬 control file檔案儲存介質損壞是不完善的。
可以採用，將control file 所在的磁碟的資料線直接拔掉來模擬。
請大家期待。。。





[@more@]