Oracle 11g R2(11.2.0.1) RAC 数据文件路径错误解决--ORA-01157 ORA-01110: 数据文件
oracle 11g R2(11.2.0.4) rac
--scan ip
select 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_TBS
select 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]$ ll
total 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 Mode
Automatic archival Enabled
Archive destination +FRA/bol/arch
Oldest online log sequence 54
Next log sequence to archive 55
Current 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 32767M
NOLOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK 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 EXAMPLE
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 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 EXTERN
2 FRA CONNECTED EXTERN
3 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 catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 instance=bol2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORORADATABOLEXAMPLE01.DBF
output file name=+DATA/bol/datafile/example01.dbf tag=TAG20181121T081627 RECID=1 STAMP=992765787
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished 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 1
ORA-00289: suggestion : +FRA/bol/arch/110_1_990861401.log
ORA-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 EXAMPLE
SQL> 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 EXAMPLE
SQL> select count(*) from hr.t;--返回正常
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> 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.