测试Oracle 11gr2 RAC 非归档模式下，offline drop数据文件后的数据库的停止与启动测试全过程

浏览数：26 / 时间：2015年06月12日

测试Oracle 11gr2 RAC 非归档模式下，offline drop数据文件后的数据库的停止与启动测试全过程

最近系统出现问题，由于数据库产生的日志量太大无法开启归档模式，导致offline的数据文件无法online！

数据库在启动的时候不检查offline的数据文件！

下面进行测试

数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

1.新建一个test表空间，add 10个数据文件，每个数据文件1M

SQL> create tablespace tb_test datafile ‘+data/db11/datafile/test01.dbf‘ size 1M autoextend off;

Tablespace created.

alter tablespace tb_test add datafile ‘+data/db11/datafile/test02.dbf‘ size 1M autoextend off;
alter tablespace tb_test add datafile ‘+data/db11/datafile/test03.dbf‘ size 1M autoextend off;
alter tablespace tb_test add datafile ‘+data/db11/datafile/test04.dbf‘ size 1M autoextend off;
alter tablespace tb_test add datafile ‘+data/db11/datafile/test05.dbf‘ size 1M autoextend off;
alter tablespace tb_test add datafile ‘+data/db11/datafile/test06.dbf‘ size 1M autoextend off;
alter tablespace tb_test add datafile ‘+data/db11/datafile/test07.dbf‘ size 1M autoextend off;
alter tablespace tb_test add datafile ‘+data/db11/datafile/test08.dbf‘ size 1M autoextend off;
alter tablespace tb_test add datafile ‘+data/db11/datafile/test09.dbf‘ size 1M autoextend off;
alter tablespace tb_test add datafile ‘+data/db11/datafile/test10.dbf‘ size 1M autoextend off;
alter tablespace tb_test add datafile ‘+data/db11/datafile/test11.dbf‘ size 1M autoextend off;
alter tablespace tb_test add datafile ‘+data/db11/datafile/test12.dbf‘ size 1M autoextend off;
alter tablespace tb_test add datafile ‘+data/db11/datafile/test13.dbf‘ size 100M autoextend off;

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES from dba_data_files where TABLESPACE_NAME=‘TB_TEST‘;

FILE_NAME                                             FILE_ID TABLESPACE_NAME                     BYTES
-------------------------------------------------- ---------- ------------------------------ ----------
+DATA/db11/datafile/test01.dbf                              6 TB_TEST                           1048576
+DATA/db11/datafile/test02.dbf                              7 TB_TEST                           1048576
+DATA/db11/datafile/test03.dbf                              8 TB_TEST                           1048576
+DATA/db11/datafile/test04.dbf                              9 TB_TEST                           1048576
+DATA/db11/datafile/test05.dbf                             10 TB_TEST                           1048576
+DATA/db11/datafile/test06.dbf                             11 TB_TEST                           1048576
+DATA/db11/datafile/test07.dbf                             12 TB_TEST                           1048576
+DATA/db11/datafile/test08.dbf                             13 TB_TEST                           1048576
+DATA/db11/datafile/test09.dbf                             14 TB_TEST                           1048576
+DATA/db11/datafile/test10.dbf                             15 TB_TEST                           1048576
+DATA/db11/datafile/test11.dbf                             16 TB_TEST                           1048576
+DATA/db11/datafile/test12.dbf                             17 TB_TEST                           1048576
+DATA/db11/datafile/test13.dbf                             18 TB_TEST                         104857600

13 rows selected.

2.创建用户，默认表空间为test

SQL> create user test identified by test default tablespace tb_test;

User created.

SQL> grant dba to test;

Grant succeeded.


SQL> select username,DEFAULT_TABLESPACE from dba_users where username=‘TEST‘;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST                           TB_TEST

查看表空间大小
TS_NAME                            PIECES    TS_SIZE  LARGESTPC  TOTALFREE   PCT_FREE  WHATSUSED   PCT_USED PROBL
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----
SYSTEM                                  2        720          9       9.69       1.35     710.31      98.65 +++++
UNDOTBS1                                1         95          4          4       4.21         91      95.79 +++
SYSAUX                                  1        580      33.31      33.31       5.74     546.69      94.26 +++
UNDOTBS2                                2         25          7       7.13      28.52      17.87      71.48
USERS                                   1          5       3.69       3.69       73.8       1.31       26.2
TB_TEST                                13        112         99     110.25      98.44       1.75       1.56

3.创建表

SQL> conn test/test
Connected.
SQL> create table test as select * from dba_objects;

Table created.

SQL> insert into test select * from dba_objects;

74555 rows created.

SQL> /      

74555 rows created.

SQL> /

74555 rows created.

SQL> commit;

Commit complete.

SQL> select distinct FILE_ID,RELATIVE_FNO,OWNER,SEGMENT_NAME,SEGMENT_TYPE from dba_extents where SEGMENT_NAME=‘TEST‘ and OWNER=‘TEST‘;

   FILE_ID RELATIVE_FNO OWNER                          SEGMENT_NAME                   SEGMENT_TYPE
---------- ------------ ------------------------------ ------------------------------ ------------------
        10           10 TEST                           TEST                           TABLE
        18           18 TEST                           TEST                           TABLE
         9            9 TEST                           TEST                           TABLE

4.数据文件offline

SQL> select count(*) from test;

  COUNT(*)
----------
    820105

SQL> alter database datafile 9 offline;
alter database datafile 9 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


SQL> alter database datafile 9 offline drop;

Database altered.

SQL> select count(*) from test;
select count(*) from test
                     *
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: ‘+DATA/db11/datafile/test04.dbf‘


SQL> alter database datafile 10 offline drop;

Database altered.

SQL> alter system switch logfile;     ---切换日志，让日志覆盖

System altered.

SQL> /
/
/
/

System altered.

SQL> /
System altered.

SQL> 


System altered.

SQL> 

System altered.

SQL> select  FILE#,name,STATUS from v$datafile;

     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 +DATA/db11/datafile/system.256.869601781           SYSTEM
         2 +DATA/db11/datafile/sysaux.257.869601783           ONLINE
         3 +DATA/db11/datafile/undotbs1.258.869601783         ONLINE
         4 +DATA/db11/datafile/users.259.869601783            ONLINE
         5 +DATA/db11/datafile/undotbs2.264.869602055         ONLINE
         6 +DATA/db11/datafile/test01.dbf                     ONLINE
         7 +DATA/db11/datafile/test02.dbf                     ONLINE
         8 +DATA/db11/datafile/test03.dbf                     ONLINE
         9 +DATA/db11/datafile/test04.dbf                     RECOVER
        10 +DATA/db11/datafile/test05.dbf                     RECOVER
        11 +DATA/db11/datafile/test06.dbf                     ONLINE
        12 +DATA/db11/datafile/test07.dbf                     ONLINE
        13 +DATA/db11/datafile/test08.dbf                     ONLINE
        14 +DATA/db11/datafile/test09.dbf                     ONLINE
        15 +DATA/db11/datafile/test10.dbf                     ONLINE
        16 +DATA/db11/datafile/test11.dbf                     ONLINE
        17 +DATA/db11/datafile/test12.dbf                     ONLINE
        18 +DATA/db11/datafile/test13.dbf                     ONLINE

尝试online
SQL> alter database datafile 9 online;
alter database datafile 9 online
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: ‘+DATA/db11/datafile/test04.dbf‘ 


SQL> recovery datafile 9;
SP2-0734: unknown command beginning "recovery d..." - rest of line ignored.
SQL> recover datafile 9
ORA-00279: change 1098981 generated at 01/21/2015 21:23:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/dbs/arch1_5_869601908.dbf   
ORA-00280: change 1098981 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log ‘/u01/app/oracle/product/11.2.0/dbs/arch1_5_869601908.dbf‘     -----日志已覆盖，不能恢复
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

由于是非归档模式，日志被覆盖，不可恢复。

5.重启数据库

db111@testdb11a  /u01/app/oracle/datafile$ srvctl stop database -d db11 -o immediate
db111@testdb11a  /u01/app/oracle/datafile$ srvctl status database -d db11
Instance db111 is not running on node testdb11a
Instance db112 is not running on node testdb11b
db111@testdb11a  /u01/app/oracle/datafile$ srvctl start database -d db11 -o open
db111@testdb11a  /u01/app/oracle/datafile$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 21 22:02:27 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DB11      READ WRITE

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
db111            OPEN
db112            OPEN              ----------成功

6.操作系统删除offline的数据文件后重启数据库

ASMCMD> ls
SYSAUX.257.869601783
SYSTEM.256.869601781
TB_TEST.268.869606509
TB_TEST.269.869606563
TB_TEST.270.869606635
TB_TEST.271.869606635
TB_TEST.272.869606635
TB_TEST.273.869606635
TB_TEST.274.869606635
TB_TEST.275.869606635
TB_TEST.276.869606637
TB_TEST.277.869606637
TB_TEST.278.869607073
TB_TEST.279.869607073
TB_TEST.280.869607275
UNDOTBS1.258.869601783
UNDOTBS2.264.869602055
USERS.259.869601783
test01.dbf
test02.dbf
test03.dbf
test04.dbf
test05.dbf
test06.dbf
test07.dbf
test08.dbf
test09.dbf
test10.dbf
test11.dbf
test12.dbf
test13.dbf
ASMCMD> 
ASMCMD> 
ASMCMD> rm test04.dbf
ASMCMD> rm test05.dbf
ASMCMD> ls
SYSAUX.257.869601783
SYSTEM.256.869601781
TB_TEST.268.869606509
TB_TEST.269.869606563
TB_TEST.270.869606635
TB_TEST.273.869606635
TB_TEST.274.869606635
TB_TEST.275.869606635
TB_TEST.276.869606637
TB_TEST.277.869606637
TB_TEST.278.869607073
TB_TEST.279.869607073
TB_TEST.280.869607275
UNDOTBS1.258.869601783
UNDOTBS2.264.869602055
USERS.259.869601783
test01.dbf
test02.dbf
test03.dbf
test06.dbf
test07.dbf
test08.dbf
test09.dbf
test10.dbf
test11.dbf
test12.dbf
test13.dbf

db111@testdb11a  /u01/app/oracle/datafile$ srvctl stop database -d db11 -o immediate
db111@testdb11a  /u01/app/oracle/datafile$ srvctl status database -d db11
Instance db111 is not running on node testdb11a
Instance db112 is not running on node testdb11b
db111@testdb11a  /u01/app/oracle/datafile$ srvctl start database -d db11 -o open
db111@testdb11a  /u01/app/oracle/datafile$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 21 22:10:12 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
db111            OPEN
db112            OPEN