RAC添加表空间误将数据文件放本地处理

最新推荐文章于 2024-07-23 16:09:42 发布

qxy0503

最新推荐文章于 2024-07-23 16:09:42 发布

阅读量1.3k

点赞数

文章标签： RAC oracle

本文链接：https://blog.csdn.net/m15217321304/article/details/80563939

版权

RAC添加表空间数据文件的时候，如果误把数据文件放置在本地，另外一个节点访问数据文件上面的数据时就会提示无法锁定该数据文件，导致出现异常，下面介绍两种处理办法。

1、当前数据文件和表空间信息

node1执行

SQL> show parameter instance

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
active_instance_count                integer
cluster_database_instances           integer                           3
instance_groups                      string
instance_name                        string                            devdb1
instance_number                      integer                           1
instance_type                        string                            RDBMS
open_links_per_instance              integer                           4
parallel_instance_group              string
parallel_server_instances            integer                           3
SQL> col tablespace_name for a20
SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- --------------------
+DATA/devdb/datafile/users.259.940551847           USERS
+DATA/devdb/datafile/undotbs1.258.940551847        UNDOTBS1
+DATA/devdb/datafile/sysaux.257.940551847          SYSAUX
+DATA/devdb/datafile/system.256.940551845          SYSTEM
+DATA/devdb/datafile/undotbs2.264.940552089        UNDOTBS2
SQL>

2、在节点1向表空间users添加数据文件

node1执行

SQL>  alter tablespace users add datafile '/u01/app/oracle/user01.dbf' size 50M autoextend off;

Tablespace altered.

SQL>

3、在users表空间创建测试表

node1执行

SQL> 
SQL> create table t (id int) tablespace users;

Table created.

SQL> 
SQL> insert into t select level id from dual connect by level <100000;

99999 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t;


  COUNT(*)
----------
     99999

SQL>

4、在节点2访问测试表T

node2执行

SQL> show parameter instance

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
active_instance_count                integer
cluster_database_instances           integer                           3
instance_groups                      string
instance_name                        string                            devdb2
instance_number                      integer                           2
instance_type                        string                            RDBMS
open_links_per_instance              integer                           4
parallel_instance_group              string
parallel_server_instances            integer                           3
SQL> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/user01.dbf'

SQL>

可以看到因为users表空间对应的数据文件存放在节点1的本地目录，实例2访问测试表T时就会提示无法锁定该文件。

5、把本地数据文件offline

node1执行

SQL> alter database datafile '/u01/app/oracle/user01.dbf' offline;

Database altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/devdb/datafile/users.259.940551847
+DATA/devdb/datafile/undotbs1.258.940551847
+DATA/devdb/datafile/sysaux.257.940551847
+DATA/devdb/datafile/system.256.940551845
+DATA/devdb/datafile/undotbs2.264.940552089
/u01/app/oracle/user01.dbf

6 rows selected.

6、使用rman迁移数据文件

node1执行

[oracle@rac1]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jun 4 18:43:57 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEVDB (DBID=845281939)

RMAN> copy datafile '/u01/app/oracle/user01.dbf' to '+DATA/devdb/datafile/user01.dbf';

Starting backup at 2018/06/04 18:44:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=158 instance=devdb1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/user01.dbf
output file name=+DATA/devdb/datafile/user01.dbf tag=TAG20180604T184453 RECID=3 STAMP=977942703
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
Finished backup at 2018/06/04 18:45:10

RMAN>

7、恢复并online数据文件

node1执行

SQL> alter database rename file '/u01/app/oracle/user01.dbf' to '+DATA/devdb/datafile/user01.dbf';

Database altered.
SQL> recover datafile '+DATA/devdb/datafile/user01.dbf';
Media recovery complete.
SQL> alter database datafile '+DATA/devdb/datafile/user01.dbf' online;

Database altered.

SQL>

8、实例2 访问测试表T

SQL> show parameter instance

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
active_instance_count                integer
cluster_database_instances           integer                           3
instance_groups                      string
instance_name                        string                            devdb2
instance_number                      integer                           2
instance_type                        string                            RDBMS
open_links_per_instance              integer                           4
parallel_instance_group              string
parallel_server_instances            integer                           3
SQL> select count(*) from T;    《========可以正常访问
  COUNT(*)
----------
     99999

SQL>

11g或者以上可以使用asmcmd copy本地文件到磁盘组

前5步一样，从第6步开始使用如下步骤

6、使用asmcmd迁移数据文件

node执行(grid 用户)

ASMCMD> ls
DEVDB/
scan-cluster/
snapcf_devdb1.f
test/
ASMCMD> cd DEVDB
ASMCMD> cd datafile
ASMCMD> pwd
+DATA/DEVDB/datafile
ASMCMD> cp '/u01/app/oracle/user01.dbf' ./
copying /u01/app/oracle/user01.dbf -> +DATA/DEVDB/datafile/user01.dbf
ASMCMD> 
ASMCMD>

7、恢复并online数据文件

SQL> 
SQL> alter database rename file '/u01/app/oracle/user01.dbf' to '+DATA/devdb/datafile/user01.dbf';


Database altered.

SQL> SQL> 
SQL> recover datafile '+DATA/devdb/datafile/user01.dbf';
Media recovery complete.
SQL> alter database datafile '+DATA/devdb/datafile/user01.dbf' online;

Database altered.

SQL>

8、实例2查询测试表T

SQL> show parameter instance

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
active_instance_count                integer
cluster_database_instances           integer                           3
instance_groups                      string
instance_name                        string                            devdb2
instance_number                      integer                           2
instance_type                        string                            RDBMS
open_links_per_instance              integer                           4
parallel_instance_group              string
parallel_server_instances            integer                           3
SQL> select count(*) from T;

  COUNT(*)
----------
     99999