首页 » ORACLE 9i-23ai » loss of all controlfile,database is automatic shutdown abort?(丢失所有控制文件，数据库会关闭？)

loss of all controlfile,database is automatic shutdown abort?(丢失所有控制文件，数据库会关闭？)

19.
Your database is configured in NOARCHIVELOG mode. All the control files have been lost due to a hard disk failure but the data files are not lost. You have the closed whole database backup available to you. Which two statements are true in this scenario? (Choose two.)
A) The instance aborts.
B) The database cannot be recovered.
C) The database can be recovered by restoring the control files from the backup.
D) The database remains opened and you have to shut it down with the ABORT option.
E) The database can be restored till the point of the last closed whole database backup.

noarchivelog mode 下测试,open database前做了rman 的整库备份

idle> conn / as sysdba
Connected.
idle> alter database open;

Database altered.

idle> conn anbob/anbob
Connected.
anbob@ANBOB> create table testdelctl(id int);

Table created.

anbob@ANBOB> insert into testdelctl values(999);

1 row created.

anbob@ANBOB> commit;

Commit complete.

anbob@ANBOB> conn system/oracle
Connected.
system@ANBOB> select * from v$controlfile;

STATUS
-------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------

/oracle/oradata/anbob/control01.ctl
NO       16384            430


/oracle/oradata/anbob/control02.ctl
NO       16384            430


/oracle/oradata/anbob/control03.ctl
NO       16384            430


system@ANBOB> !rm /oracle/oradata/anbob/control*.ctl

system@ANBOB> !ls /oracle/oradata/anbob/
redo01.log  redo03.log    system01.dbf  undotbs01.dbf  users01.dbf
redo02.log  sysaux01.dbf  temp01.dbf    undotbs2.dbf
 
system@ANBOB> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/oradata/anbob/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


system@ANBOB> select status from v$instance;

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

sys@ANBOB> conn anbob/anbob
Connected.
anbob@ANBOB> select * from testdelctl;

        ID
----------
       999

anbob@ANBOB> insert into testdelctl values(0);

1 row created.

anbob@ANBOB> commit;

Commit complete.

anbob@ANBOB> conn / as sysdba
Connected.
sys@ANBOB> alter systme checkpoint;
alter systme checkpoint
      *
ERROR at line 1:
ORA-00940: invalid ALTER command


sys@ANBOB> alter system checkpoint;

System altered.

sys@ANBOB> select status from v$instance;

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

sys@ANBOB> select * from v$version where rownum=1
  2  ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

sys@ANBOB> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/oradata/anbob/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
sys@ANBOB> shutdown abort
ORACLE instance shut down.
sys@ANBOB> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              96471284 bytes
Database Buffers           67108864 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info


sys@ANBOB> select status from v$instance;

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

[oracle@aix ~]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 14 22:32:12 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: anbob (not mounted)

RMAN> restore controlfile;

Starting restore at 14-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/14/2011 22:32:27
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> restore controlfile from autobackup;

Starting restore at 14-JUL-11
using channel ORA_DISK_1

recovery area destination: /oracle/flash_recovery_area
database name (or database unique name) used for search: ANBOB
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oracle/flash_recovery_area/ANBOB/autobackup/2011_07_14/o1_mf_s_756511643_71xy4xjm_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oracle/oradata/anbob/control01.ctl
output filename=/oracle/oradata/anbob/control02.ctl
output filename=/oracle/oradata/anbob/control03.ctl
Finished restore at 14-JUL-11

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/14/2011 22:32:52
ORA-01507: database not mounted

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/14/2011 22:33:12
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/14/2011 22:33:19
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/oradata/anbob/system01.dbf'

RMAN> recover database;

Starting recover at 14-JUL-11
Starting implicit crosscheck backup at 14-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 14-JUL-11

Starting implicit crosscheck copy at 14-JUL-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-JUL-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oracle/flash_recovery_area/ANBOB/autobackup/2011_07_14/o1_mf_s_756511643_71xy4xjm_.bkp

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 19 is already on disk as file /oracle/oradata/anbob/redo03.log
archive log thread 1 sequence 20 is already on disk as file /oracle/oradata/anbob/redo01.log
archive log filename=/oracle/oradata/anbob/redo03.log thread=1 sequence=19
archive log filename=/oracle/oradata/anbob/redo01.log thread=1 sequence=20
media recovery complete, elapsed time: 00:00:03
Finished recover at 14-JUL-11

RMAN> alter database open resetlogs;

database opened

RMAN> exit


Recovery Manager complete.
o[oracle@aix ~]$ ora

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 14 22:34:23 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


sys@ANBOB> !ls /oracle/oradata/anbob/
control01.ctl  control03.ctl  redo02.log  sysaux01.dbf  temp01.dbf     undotbs2.dbf
control02.ctl  redo01.log     redo03.log  system01.dbf  undotbs01.dbf  users01.dbfconn anbob
sys@ANBOB> conn anbob
Enter password: 
Connected.
anbob@ANBOB> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TESTCHAR                       TABLE
TT                             TABLE
TESTTRUN                       TABLE
TESTADDM                       TABLE
TEST                           TABLE
TESTFUNIDX                     TABLE
TEAM                           TABLE
MEMB                           TABLE
TESTENC                        TABLE
TESTREC                        TABLE
BIN$p/SuvHy7sn7gQKjA9QF/dQ==$0 TABLE
DETP                           TABLE
TESTDELCTL                     TABLE
DEPT                           TABLE
TESTFLS                        TABLE

15 rows selected.

anbob@ANBOB> select * from testdelctl;

        ID
----------
       999
         0

这个问题困惑我很久，一直以为这是个颠覆性的实验，后来在oracle官方论坛才得到了有效的解释，还有感谢网友老农

原话如下
The files, which are in use by a process, are not actually deleted when one issues rm command. Its only when the process terminates that the locks are released and file is actually deleted at operating system level.

This behavior is *NIX only.
Windows explicitly prevents open files from being deleted.

Because in the Unix environments, the oracle process won’t actually close the file and would keep it still opened. That’s why you are not seeing that the database is getting aborted. Restart the db and you would see that the error would be reported.

经与老农再三确认，意思是这样的，oracle database 一直在打开状态，实际后台进程一直在打开controlfile，但在linux/unix下，文件上并未上一种锁，这点与windows不同，所以在unix操作系统级做的删除，实际文件并未释放，有后台进程一直在读写，所以在数据库关闭，文件才彻底释放删除。

打赏

,

目前这篇文章有3条评论(Rss)评论关闭。

  1. Arnette Gosden | #1
    2011-12-21 at 07:21

    I got what you mean , appreciate it for putting up.

  2. Sandra Moseley | #2
    2011-11-01 at 22:06

    Great post. Thanks. Pleased I got chance to read it and find out more.

  3. Bucy | #3
    2011-11-01 at 09:25

    Just great list! For a more recent weblog like mine, totally free is the best option right now!If I each and every help to make any kind of decent money, I will consider which market samurai software program. It appears pretty cool!

上一篇：

下一篇：