rac中文名檔名新增到本地路徑解決辦法(ORA-01157)-非歸檔

shawnloong發表於2018-07-01
oracle rac中文名新增到本地路徑解決辦法(ORA-01157)


故障模擬(生產環境勿測試)

點選(此處)摺疊或開啟

  1. [grid@rac1-213-11 ~]# su - oracle
  2. [grid@rac1-213-11 ~]$ sqlplus / as sysdba
  3. SQL>alter table netdata add datafile '測試DATA'


在第二個節點上查詢出現錯誤

點選(此處)摺疊或開啟

  1. [oracle@rac2-213-12 trace]$ sqlplus / as sysdba
  2. SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 1 22:07:21 2018
  3. Copyright (c) 1982, 2013, Oracle. All rights reserved.
  4. Connected to:
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  6. With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
  7. Data Mining and Real Application Testing options
  8. SQL> select file_name from dba_data_files;
  9. ERROR:
  10. ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
  11. ORA-01110: data file 9: '/u01/app/oracle/product/11.2.0/db_1/dbs/測試DATA'

  13. SQL> select file_name from dba_data_files;
  14. ERROR:
  15. ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
  16. ORA-01110: data file 9: '/u01/app/oracle/product/11.2.0/db_1/dbs/嫻????ìDATA'
關閉庫，複製檔案

點選(此處)摺疊或開啟

  1. --grd
  2. [grid@rac1-213-11 ~]$ srvctl stop database -d devdb


  5. --oracle
  6. [oracle@rac1-213-11 dbs]$ cp "測試DATA" netdata05.dbf


  9. mount 庫
  10. --grid
  11. [grid@rac1-213-11 ~]$ srvctl start database -d devdb -o mount
  12. --oracle
  13. [oracle@rac1-213-11 ~]$ sqlplus / as sysdba


  16. SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 1 22:39:54 2018
  17. Copyright (c) 1982, 2013, Oracle. All rights reserved.

  19. Connected to:
  20. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  21. With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
  22. Data Mining and Real Application Testing options


  25. SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/測試DATA' to '/u01/app/oracle/product/11.2.0/db_1/dbs/netdata05.dbf';

  27. Database altered.



關閉庫，再次複製檔案至asm

點選(此處)摺疊或開啟

  1. [grid@rac1-213-11 ~]$ srvctl stop database -d devdb
  2. ASMCMD> cp /u01/app/oracle/product/11.2.0/db_1/dbs/netdata05.dbf .
  3. copying /u01/app/oracle/product/11.2.0/db_1/dbs/netdata05.dbf -> +DATA1/DEVDB/DATAFILE/netdata05.dbf
  4. ASMCMD> quit


mount庫

點選(此處)摺疊或開啟

  1. [grid@rac1-213-11 ~]$ srvctl start database -d devdb -o mount



修改檔案路徑

點選(此處)摺疊或開啟

  1. SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/netdata05.dbf' to '+DATA1/DEVDB/DATAFILE/netdata05.dbf';
  2. SQL>Database altered.

關閉庫

點選(此處)摺疊或開啟

  1. [grid@rac1-213-11 ~]$ srvctl stop database -d devdb
  2. 啟動庫
  3. [grid@rac1-213-11 ~]$ srvctl start database -d devdb





資料庫已經正常

點選(此處)摺疊或開啟

  1. [oracle@rac1-213-11 ~]$ sqlplus / as sysdba
  2. SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 1 22:49:49 2018
  3. Copyright (c) 1982, 2013, Oracle. All rights reserved.
  4. Connected to:
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  6. With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
  7. Data Mining and Real Application Testing options
  8. SQL> select open_mode from v$database;
  9. OPEN_MODE
  10. --------------------
  11. READ WRITE



節點2檢視

點選(此處)摺疊或開啟

  1. [oracle@rac2-213-12 trace]$ sqlplus / as sysdba

  3. SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 1 22:07:21 2018

  5. Copyright (c) 1982, 2013, Oracle. All rights reserved

  7. Connected to:
  8. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  9. With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
  10. Data Mining and Real Application Testing options




  15. SQL> /


  18. FILE_NAME
  19. --------------------------------------------------------------------------------
  20. +DATA1/devdb/datafile/users.269.937046643
  21. +DATA1/devdb/datafile/undotbs1.276.937046643
  22. +DATA1/devdb/datafile/sysaux.277.937046643
  23. +DATA1/devdb/datafile/system.268.937046635
  24. +DATA1/devdb/datafile/undotbs2.267.937047083
  25. +DATA1/devdb/datafile/netdata.284.937049537
  26. +DATA1/devdb/datafile/netdata02.dbf
  27. +DATA1/devdb/datafile/netdata03.dbf
  28. +DATA1/devdb/datafile/netdata05.dbf


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24486203/viewspace-2157171/，如需轉載，請註明出處，否則將追究法律責任。

相關文章