Oracle 11g R2(11.2.0.1) RAC 数据文件路径错误解决--ORA-01157 ORA-01110: 数据文件
oracle 11g R2(11.2.0.4) rac
--scan ipselect file_name,tablespace_name from dba_data_files;+DATA/bol/datafile/system.259.990861405 SYSTEM+DATA/bol/datafile/sysaux.260.990861407 SYSAUX+DATA/bol/datafile/undotbs1.261.990861409 UNDOTBS1+DATA/bol/datafile/undotbs2.263.990861413 UNDOTBS2+DATA/bol/datafile/users.264.990861413 USERS+DATA/bol/datafile/test01.dbf TEST+DATA/bol/datafile/sde_tbs.dbf SDE_TBS+DATA/bol/datafile/sde_tbs02.dbf SDE_TBSselect name,file#,bytes/1024/1024 MB,status from v$datafile;
+DATA/bol/datafile/system.259.990861405 1 770 SYSTEM+DATA/bol/datafile/sysaux.260.990861407 2 850 ONLINE+DATA/bol/datafile/undotbs1.261.990861409 3 1125 ONLINE+DATA/bol/datafile/undotbs2.263.990861413 4 200 ONLINE+DATA/bol/datafile/users.264.990861413 5 500 ONLINE+DATA/bol/datafile/test01.dbf 6 50 ONLINE+DATA/bol/datafile/sde_tbs.dbf 7 500 ONLINE+DATA/bol/datafile/sde_tbs02.dbf 8 200 ONLINE[root@rac2 ~]# su - oracle
[oracle@rac2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/[oracle@rac2 dbs]$ lltotal 16-rw-rw---- 1 oracle asmadmin 1544 Nov 21 07:39 hc_bol2.dat-rw-r----- 1 oracle oinstall 33 Oct 30 07:36 initbol2.ora-rw-r--r-- 1 oracle oinstall 2851 Oct 30 07:01 init.ora-rw-r----- 1 oracle oinstall 1536 Oct 30 07:40 orapwbol2创建错误的表空间，用户，权限以及插入数据
通过scan_ip连接，是rac2节点，归档模式SQL> archive log list;
Database log mode Archive ModeAutomatic archival EnabledArchive destination +FRA/bol/archOldest online log sequence 54Next log sequence to archive 55Current log sequence 55 SQL> CREATE TABLESPACE EXAMPLE DATAFILE '/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORORADATABOLEXAMPLE01.DBF' SIZE 100M AUTOEXTEND ON NEXT 640K MAXSIZE 32767MNOLOGGINGONLINEEXTENT MANAGEMENT LOCAL AUTOALLOCATEBLOCKSIZE 8KSEGMENT SPACE MANAGEMENT AUTOFLASHBACK ON;SQL> CREATE USER hr
IDENTIFIED BY "hr" DEFAULT TABLESPACE EXAMPLE TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; GRANT RESOURCE TO HR; ALTER USER HR DEFAULT ROLE ALL; -- 7 System Privileges for HR GRANT ALTER SESSION TO HR; GRANT CREATE DATABASE LINK TO HR; GRANT CREATE SEQUENCE TO HR; GRANT CREATE SESSION TO HR; GRANT CREATE SYNONYM TO HR; GRANT CREATE VIEW TO HR; GRANT UNLIMITED TABLESPACE TO HR;SQL> create table t (id int);
SQL> insert into t select level id from dual connect by level <100000;SQL> commit;SQL> select count(*) from t;
SQL> select count(*) from hr.t;再次查看表空间，数据文件等SQL> select file_name,tablespace_name from dba_data_files;/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORORADATABOLEXAMPLE01.DBF EXAMPLESQL> select name,file#,bytes/1024/1024 MB,status from v$datafile;/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORORADATABOLEXAMPLE01.DBF 9 100 ONLINE登录rac1节点查看SQL> select file_name,tablespace_name from dba_data_files;--直接报错ORA-01157: 无法标识/锁定数据文件 9 - 请参阅 DBWR 跟踪文件ORA-01110: 数据文件 9: '/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORORADATABOLEXAMPLE01.DBF'SQL> select group_number , name , state, type from v$asm_diskgroup; --查看asm磁盘组信息，返回正常
1 DATA CONNECTED EXTERN2 FRA CONNECTED EXTERN3 OCR MOUNTED EXTERN登录rac2节点
SQL> alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORORADATABOLEXAMPLE01.DBF' offline;SQL> select name,file#,bytes/1024/1024 MB,status from v$datafile;/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORORADATABOLEXAMPLE01.DBF 9 100 RECOVER使用rman在rac2节点上登录[oracle@rac2 dbs]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 21 08:14:47 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: BOL (DBID=4213574617)
RMAN> copy datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORORADATABOLEXAMPLE01.DBF' to '+DATA/bol/datafile/EXAMPLE01.DBF';
Starting backup at 21-NOV-18
using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=32 instance=bol2 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00009 name=/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORORADATABOLEXAMPLE01.DBFoutput file name=+DATA/bol/datafile/example01.dbf tag=TAG20181121T081627 RECID=1 STAMP=992765787channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 21-NOV-18[oracle@rac2 dbs]$ sqlplus / as sysdba
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORORADATABOLEXAMPLE01.DBF' to '+DATA/bol/datafile/EXAMPLE01.DBF';Database altered.
SQL> recover datafile '+DATA/bol/datafile/EXAMPLE01.DBF';
ORA-00279: change 2845533 generated at 11/21/2018 07:39:35 needed for thread 1ORA-00289: suggestion : +FRA/bol/arch/110_1_990861401.logORA-00280: change 2845533 for thread 1 is in sequence #110 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}Log applied.
Media recovery complete.SQL> alter database datafile '+DATA/bol/datafile/EXAMPLE01.DBF' online;Database altered.
在节点2上查询SQL> select file_name,tablespace_name from dba_data_files; --显示路径正常+DATA/bol/datafile/example01.dbf EXAMPLESQL> select name,file#,bytes/1024/1024 MB,status from v$datafile;+DATA/bol/datafile/example01.dbf 9 100 ONLINE在节点1上进行查询，也正常，并查询hr.t的数据SQL> select file_name,tablespace_name from dba_data_files;+DATA/bol/datafile/example01.dbf EXAMPLESQL> select count(*) from hr.t;--返回正常[root@rac1 ~]# su - oracle[oracle@rac1 ~]$ sqlplus / as sysdbaSQL> select count(*) from hr.t;COUNT(*)
---------- 99999---在非归档模式下进行
SQL> alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORORADATABOLEXAMPLE01.DBF' offline;ORA-01145: offline immediate disallowed unless media recovery enabled以上操作需要在归档模式下才可以非归档模式下SQL> alter tablespace EXAMPLE offline;Tablespace altered.SQL> alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORORADATABOLEXAMPLE01.DBF' offline;Database altered.