[Oracle] ASM logical file protecting rule

Blog » Oracle ASM logical file protecting rule

Post at 07 Mar 2015 11:38

Oracle ASM can protected logical file from overriding by same database. Let's explore the protecting rule.

Before test the rule, backup both @orcl and @ora2.

rman target / nocatalog
backup compressed backupset database plus archivelog delete input;

Prepare @orcl

SQL> create tablespace demo datafile '+DG1/orcl/demo01.dbf' size 10m autoextend on next 10m maxsize 1g
 
Tablespace created.

Try to overwrite a existing file @orcl

SQL> drop tablespace demo;
 
Tablespace droped;
 
SQL> create tablespace demo datafile '+DG1/orcl/demo01.dbf' size 10m autoextend on next 10m maxsize 1g;
create tablespace demo datafile '+DG1/orcl/demo01.dbf' size 10m autoextend on next 10m maxsize 1g
*
ERROR at line 1:
ORA-01119: error in creating database file '+DG1/orcl/demo01.dbf'
ORA-17502: ksfdcre:4 Failed to create file +DG1/orcl/demo01.dbf
ORA-15005: name "orcl/demo01.dbf" is already used by an existing alias

Overwrite not used file with reuse option

SQL> create tablespace demo datafile '+DG1/orcl/demo01.dbf' reuse
 
Tablespace created.

Try to overwrite a used datafile

SQL> create tablespace demo_t datafile '+DG1/orcl/demo01.dbf' size 10m autoextend on next 10m maxsize 1g
*
ERROR at line 1:
ORA-01537: cannot add file '+DG1/orcl/demo01.dbf' - file already part of
database

From @orcl create a table on tablespace demo

SQL> create table t (id decimal(5,0), name varchar(5)) tablespace demo;
 
SQL> insert into t values (2, 2);
 
SQL> commit;

From @ora2, try to overwrite a file of another db @orcl
With 'reuse' option, we CAN OVERWRITE datafiles of other databases, very dangerous, just as what we can do with file system!

SQL> show parameter db_name

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_name                              string                            ora2

SQL> create tablespace demo datafile '+DG1/orcl/demo01.dbf' size 10m autoextend on next 10m maxsize 1g;
create tablespace demo datafile '+DG1/orcl/demo01.dbf' size 10m autoextend on next 10m maxsize 1g
*
ERROR at line 1:
ORA-01119: error in creating database file '+DG1/orcl/demo01.dbf'
ORA-17502: ksfdcre:4 Failed to create file +DG1/orcl/demo01.dbf
ORA-15005: name "orcl/demo01.dbf" is already used by an existing alias

SQL> create tablespace demo datafile '+DG1/orcl/demo01.dbf' reuse;

Tablespace created.

SQL> create table t (id decimal(5,0), name varchar(5)) tablespace demo;

SQL> insert into t values (2, 2);

SQL> commit;

SQL> alter system switch logfile;

2 instances connected to asm

ASMCMD> lsct
DB_Name   Status        Software_Version  Compatible_version  Instance_Name
ora2      CONNECTED           10.2.0.1.0          10.2.0.1.0  ora2
orcl      CONNECTED           10.2.0.1.0          10.2.0.1.0  orcl

go back to @orcl

[oracle@demo ~]$ export ORACLE_SID=orcl
[oracle@demo ~]$ sqlplus "/ as sysdba"
 
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 7 22:51:25 2015
 
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
 
SQL> select * from demo.t;
select * from demo.t
                   *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '+DG1/orcl/demo01.dbf'
 
SQL> select * from dba_data_files;
 
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
 
   FILE_ID TABLESPACE_NAME                                                                                 BYTES     BLOCKS
---------- ------------------------------------------------------------------------------------------ ---------- ----------
STATUS                      RELATIVE_FNO AUTOEXTEN   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
--------------------------- ------------ --------- ---------- ---------- ------------ ---------- ----------- ---------------------
+DG1/orcl/users01.dbf
         4 USERS                                                                                         5242880        640
AVAILABLE                              4 YES       3.4360E+10    4194302          160    5177344         632 ONLINE
 
+DG1/orcl/sysaux01.dbf
         3 SYSAUX                                                                                      251658240      30720
AVAILABLE                              3 YES       3.4360E+10    4194302         1280  251592704       30712 ONLINE
 
+DG1/orcl/undotbs01.dbf
         2 UNDOTBS1                                                                                     26214400       3200
AVAILABLE                              2 YES       3.4360E+10    4194302          640   26148864        3192 ONLINE
 
+DG1/orcl/system01.dbf
         1 SYSTEM                                                                                      503316480      61440
AVAILABLE                              1 YES       3.4360E+10    4194302         1280  503250944       61432 SYSTEM
 
+DG1/orcl/demo01.dbf
         5 DEMO
AVAILABLE                              5                                                                     RECOVER

Rating

rating: 0+x

Comment

Add a New Comment