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

This article mainly introduces what to do when oracle rac creates datafile to the local file system resulting in orastar-20170924 errors. The article is very detailed and has certain reference value. Interested friends must finish reading it!

1. Problem description

The database of the oracle rac environment is created to the local file system, resulting in intermittent access exceptions for some applications.

2. Treatment method

Thinking of problem handling

1. Delete the newly added local datafile and add the datafile to the shared storage

2. Migrate the problem datafile to the shared storage datafile

Restrictions on deleting data files:

The following are restrictions on deleting data files and temporary files:

Click (here) to collapse or open

The database must be open.

If the data file is not empty, it cannot be deleted.

If you must delete a data file that is not empty and cannot be empty by deleting a schema object, you must delete the tablespace that contains the data file.

You cannot delete the first or unique data file in the tablespace. This means that DROP DATAFILE cannot be used with bigfile tablespaces.)

You cannot delete a data file in a read-only tablespace.

You cannot delete data files in SYSTEM tablespaces. -pay special attention

If the data file in the local administrative tablespace is offline, it cannot be deleted.

| | data in method 1 (non-system drop), move dba_extents, and delete datafile |

| | non-system tablespace-| |

| | method 2 (non-system rename), datafile offline,cp datafile, rename |

Datafile--- |

| |

| | system tablespace-| method 3 (system rename), stop instance,startup mount,cp datafile to new location,rename,recover datafile,instance open |

3. Method 1 non-system drop experiment

SQL > col file_name for A40

Set line 200

Select file_id,tablespace_name,file_name,bytes/1024/1024 from dba_data_files;SQL > SQL >

FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024

--

4 USERS / dev/rlvusers_1G 5

3 SYSAUX / dev/rlvsysaux_512 360

2 UNDOTBS1 / dev/rlvundotbs_512 35

1 SYSTEM / dev/rlvsystem_512 490

5 TT_TEST / oracle/oradata/test/tt_test01.dbf 100

6 TT_TEST / oracle/oradata/test/tt_test02.dbf 100-Delete the data file

7 SYSTEM / dev/rlvsystem_512_2 100

7 rows selected.

SQL > select owner,segment_name,tablespace_name from dba_extents where file_id=6

No rows selected

SQL > alter tablespace TT_TEST drop datafile 6

Tablespace altered.

Note:

If there is data in the data file, you need to migrate and rebuild the index first.

1. Check whether there is any data: SQL > select owner,segment_name,tablespace_name from dba_extents where file_id=6

2 、 ALTER TABLE table_name MOVE TABLESPACE tablespace_name

3 、 index_rebuild

4 、 alter tablespace TT_TEST drop datafile 6

Select file_id,tablespace_name,file_name,bytes/1024/1024 from dba_data_files;SQL > SQL >

FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024

--

4 USERS / dev/rlvusers_1G 5

3 SYSAUX / dev/rlvsysaux_512 360

2 UNDOTBS1 / dev/rlvundotbs_512 35

1 SYSTEM / dev/rlvsystem_512 490

5 TT_TEST / oracle/oradata/test/tt_test01.dbf 100

7 SYSTEM / dev/rlvsystem_512_2 100

6 rows selected.

SQL >

4. Method 2 non-system drop rename

Description:

SQL > select file_id,tablespace_name,file_name,bytes/1024/1024,ONLINE_STATUS from dba_data_files

FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024 ONLINE_

-

4 USERS / dev/rlvusers_1G 5 ONLINE

3 SYSAUX / dev/rlvsysaux_512 360 ONLINE

2 UNDOTBS1 / dev/rlvundotbs_512 35 ONLINE

1 SYSTEM / dev/rlvsystem_512 490 SYSTEM

5 TT_TEST / oracle/oradata/test2/tt_test01.dbf 100 ONLINE

7 SYSTEM / dev/rlvsystem_512_2 100 SYSTEM

6 rows selected.

SQL > alter database datafile 5 offline

Database altered.

SQL > select file_id,tablespace_name,file_name,bytes/1024/1024,ONLINE_STATUS from dba_data_files

FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024 ONLINE_

-

4 USERS / dev/rlvusers_1G 5 ONLINE

3 SYSAUX / dev/rlvsysaux_512 360 ONLINE

2 UNDOTBS1 / dev/rlvundotbs_512 35 ONLINE

1 SYSTEM / dev/rlvsystem_512 490 SYSTEM

5 TT_TEST / oracle/oradata/test2/tt_test01.dbf RECOVER

7 SYSTEM / dev/rlvsystem_512_2 100 SYSTEM

6 rows selected.

SQL >! cp / oracle/oradata/test2/tt_test01.dbf / oracle/oradata/test/tt_test02.dbf

SQL > alter database rename file'/ oracle/oradata/test2/tt_test01.dbf' to'/ oracle/oradata/test/tt_test02.dbf'

Database altered.

SQL > select file_id,tablespace_name,file_name,bytes/1024/1024,ONLINE_STATUS from dba_data_files

FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024 ONLINE_

-

4 USERS / dev/rlvusers_1G 5 ONLINE

3 SYSAUX / dev/rlvsysaux_512 360 ONLINE

2 UNDOTBS1 / dev/rlvundotbs_512 35 ONLINE

1 SYSTEM / dev/rlvsystem_512 490 SYSTEM

5 TT_TEST / oracle/oradata/test/tt_test02.dbf RECOVER

7 SYSTEM / dev/rlvsystem_512_2 100 SYSTEM

6 rows selected.

SQL > recover datafile 5

Media recovery complete.

SQL > alter database datafile 5 online

Database altered.

SQL > select file_id,tablespace_name,file_name,bytes/1024/1024,ONLINE_STATUS from dba_data_files

FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024 ONLINE_

-

4 USERS / dev/rlvusers_1G 5 ONLINE

3 SYSAUX / dev/rlvsysaux_512 360 ONLINE

2 UNDOTBS1 / dev/rlvundotbs_512 35 ONLINE

1 SYSTEM / dev/rlvsystem_512 490 SYSTEM

5 TT_TEST / oracle/oradata/test/tt_test02.dbf 100 ONLINE

7 SYSTEM / dev/rlvsystem_512_2 100 SYSTEM

6 rows selected.

5. Method 3 system rename

SQL > /

FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024

--

4 USERS / dev/rlvusers_1G 5

3 SYSAUX / dev/rlvsysaux_512 360

2 UNDOTBS1 / dev/rlvundotbs_512 35

1 SYSTEM / dev/rlvsystem_512 490

5 TT_TEST / oracle/oradata/test/tt_test01.dbf 100

6 TT_TEST / oracle/oradata/test/tt_test02.dbf 100

7 SYSTEM / oracle/oradata/test/system02.dbf 100-migrate this file to a bare device

7 rows selected.

SQL > alter system checkpoint

System altered.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size 2084400 bytes

Variable Size 419430864 bytes

Database Buffers 1174405120 bytes

Redo Buffers 14692352 bytes

Database mounted.

SQL > exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

$export ORACLE_SID=test

$rman target /

Recovery Manager: Release 10.2.0.4.0-Production on Sun Sep 24 10:45:59 2017

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

Connected to target database: TEST (DBID=2245579828, not open)

RMAN > copy datafile'/ oracle/oradata/test/system02.dbf' to'/ dev/rlvsystem_512_2'

Starting backup at 24-SEP-17

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid=156 devtype=DISK

Channel ORA_DISK_1: starting datafile copy

Input datafile fno=00007 name=/oracle/oradata/test/system02.dbf

Output filename=/dev/rlvsystem_512_2 tag=TAG20170924T104619 recid=1 stamp=955536380

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 24-SEP-17

$export ORACLE_SID=test

$sqlplus / as sysdba

SQL > alter database rename file'/ oracle/oradata/test/system02.dbf' to'/ dev/rlvsystem_512_2'

Database altered.

SQL > alter database open

Database altered.

SQL > select file_id,tablespace_name,file_name,bytes/1024/1024,ONLINE_STATUS from dba_data_files

FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024 ONLINE_

-

4 USERS / dev/rlvusers_1G 5 ONLINE

3 SYSAUX / dev/rlvsysaux_512 360 ONLINE

2 UNDOTBS1 / dev/rlvundotbs_512 35 ONLINE

1 SYSTEM / dev/rlvsystem_512 490 SYSTEM

5 TT_TEST / oracle/oradata/test/tt_test01.dbf 100 ONLINE

7 SYSTEM / dev/rlvsystem_512_2 100 SYSTEM

6 rows selected.

6. Points for attention

To create an oracle datafile with a bare device, the bare device needs to be larger than oracle datafiles.

The above is all the contents of the article "what to do if oracle rac creates datafile to the local file system and causes orastar-20170924 errors?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.