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
Comment