What is the correction method when the data file is mistakenly created on the local disk in RAC
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!
