rac归档模式下文件建至本地存储修复方法（以下为测试，生产环境慎重操作）
模拟误操作
节点1添加数据文件
-
SQL> alter tablespace netdata add datafile 'netdata03.dbf' size 20M;
节点2查询
-
SQL> select file_name from dba_data_files;
-
ERROR:
-
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
-
ORA-01110: data file 7: '/u01/app/oracle/product/11.2.0/db_1/dbs/netdata02.dbf'
将错误添加datafile offline
-
SQL> alter database datafile 7 offline;
-
Database altered.
修改文件路径
-
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/netdata02.dbf' to '+DATA1/DEVDB/DATAFILE/netdata02.dbf';
-
-
-
Database altered.
recovery datafile
-
SQL> recover datafile 7;
-
Media recovery complete.
datafile online
-
SQL> alter database datafile 7 online;
-
-
-
Database altered.
节点2查询已经正常
-
SQL> select file_name from dba_data_files;
-
-
-
FILE_NAME
-
--------------------------------------------------------------------------------
-
+DATA1/devdb/datafile/users.269.937046643
-
+DATA1/devdb/datafile/undotbs1.276.937046643
-
+DATA1/devdb/datafile/sysaux.277.937046643
-
+DATA1/devdb/datafile/system.268.937046635
-
+DATA1/devdb/datafile/undotbs2.267.937047083
-
+DATA1/devdb/datafile/netdata.284.937049537
-
+DATA1/devdb/datafile/netdata02.dbf
-
-
-
7 rows selected.
rac非归档模式下文件建至本地存储修复方法
-
SQL> archive log list;
-
Database log mode No Archive Mode
-
Automatic archival Disabled
-
Archive destination USE_DB_RECOVERY_FILE_DEST
-
Oldest online log sequence 175
-
Current log sequence 177
-
SQL>
-
-
SQL> show parameters cluster;
-
-
-
NAME TYPE VALUE
-
------------------------------------ ----------- ------------------------------
-
cluster_database boolean TRUE
-
cluster_database_instances integer 2
-
cluster_interconnects string
误操作模拟
-
SQL> alter tablespace netdata add datafile 'netdata03.dbf' size 20M;
-
-
Tablespace altered.
-
-
SQL> select file_name from dba_data_files;
-
ERROR:
-
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
-
ORA-01110: data file 8: '/u01/app/oracle/product/11.2.0/db_1/dbs/netdata03.dbf'
尝试offline报错
-
SQL> alter database datafile 8 offline;
-
alter database datafile 8 offline
-
*
-
ERROR at line 1:
-
ORA-01145: offline immediate disallowed unless media recovery enabled
关库
-
[grid@rac1-213-11 ~]$ srvctl stop database -d devdb -o immediate
cp文件至asm
-
[grid@rac1-213-11 ~]$ asmcmd
-
ASMCMD> pwd
-
+DATA1/DEVDB/DATAFILE
-
ASMCMD> cp /u01/app/oracle/product/11.2.0/db_1/dbs/netdata03.dbf .
-
copying /u01/app/oracle/product/11.2.0/db_1/dbs/netdata03.dbf -> +DATA1/DEVDB/DATAFILE/netdata03.dbf
-
ASMCMD> quit
启动到mount
-
[grid@rac1-213-11 ~]$ srvctl start database -d devdb -o mount
-
、
修改文件路径
-
[root@rac1-213-11 ~]# su - oracle
-
[oracle@rac1-213-11 ~]$ sqlplus / as sysdba
-
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 1 22:02:50 2018
-
Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
Connected to:
-
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
-
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
-
Data Mining and Real Application Testing options
-
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/netdata03.dbf' to '+DATA1/DEVDB/DATAFILE/netdata03.dbf';
-
Database altered.
节点2查询 正常
-
SQL> select open_mode from v$database;
-
-
OPEN_MODE
-
--------------------
-
READ WRITE
