Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the correction method when the data file is created in the local disk by mistake in RAC". In the operation of the actual case, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Test purpose: to verify the repair method in RAC when the data file is created on the local disk by mistake.

Environment description:

Two-node RAC, database named kdedi version 11.2.0.4

ASM is used as the shared storage solution.

One environment preparation

1) create a tablespace test1 on the node, and put the data files on the local site instead of ASM. You can view the tablespace and create tables on it:

19:26:00 sys@XXX > create tablespace test1 datafile'/ home/oracle/test1.dbf' size 10m

Tablespace created.

19:28:25 sys@XXX > select name,status from v$datafile

NAME

- -

STATUS

.

/ home/oracle/test1.dbf

ONLINE

30 rows selected.

19:28:32 sys@XXX > create table test1 (id int) tablespace test1

Table created.

19:29:01 sys@XXX > create table test2 tablespace test1 as select * from dba_tables

Table created.

19:29:09 sys@XXX >

2) the tablespace test1 can be found on node 2, but an error was reported when creating the table:

19:24:33 sys@XXX > select name, status from v$datafile

NAME

- -

STATUS

-

/ home/oracle/test1.dbf

ONLINE

30 rows selected.

19:29:24 sys@XXX > create table test1 (id int) tablespace test1

Create table test1 (id int) tablespace test1*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 30-see DBWR trace file

ORA-01110: data file 30:'/ home/oracle/test1.dbf'

3) but the database can still be restarted.

[grid@qs-oradb1] $srvctl stop database-d kdedi

[grid@qs-oradb1] $srvctl start database-d kdedi

[grid@qs-oradb1 ~] $

2 treatment process

20:56:25 sys@XXX > alter tablespace test1 offline

Tablespace altered.

20:56:59 sys@XXX > exec dbms_file_transfer.copy_file ('TEST1','test1.dbf','TEST2','test1.dbf')

PL/SQL procedure successfully completed.

20:57:09 sys@XXX > alter database rename file'/ home/oracle/test1.dbf' to'+ XXX_DATA/XXX/datafile/test1.dbf'

Database altered.

20:57:39 sys@XXX > alter tablespace test1 online

Tablespace altered.

3. Remarks

The above data files are migrated using dbms_file_transfer.copy_file, or you can use RMAN to do so:

SQL > select tablespace_name,file_name,status,online_status from

Dba_data_files

Need to OFFLINE the tablespace

Log in to RMAN

RMAN > sql "alter tablespace test1 offline"

RMAN > copy datafile'/ home/oracle/test1.dbf' to

'+ DG/rac10g/datafile/test1.dbf'

SQL > alter database rename file'/ home/oracle/test1.dbf' to

'+ DG/rac10g/datafile/test1.dbf'

SQL > alter tablespace test1 online

Attachment: delete alias of data file:

[grid@qs-oradb1 ~] $asmcmd

ASMCMD > ls-l

State Type Rebal Name

MOUNTED EXTERN N ARCH/

MOUNTED EXTERN N DG_GRID/

MOUNTED EXTERN N FBFJ_DATA/

MOUNTED EXTERN N KDEDI_DATA/

Cut to the corresponding directory:

ASMCMD > cd + KDEDI_DATA/kdedi/datafile

ASMCMD > rm test1.dbf

This is the end of the content of "what is the correction method for mistakenly creating data files on the local disk in RAC". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.