合 Oracle rac环境将datafile创建到本地文件系统的处理步骤
👉 本文共约400个字，系统预计阅读时间或需2分钟。
简介
oracle rac环境将datafile创建到本地文件系统，会造成间歇性访问异常。
若数据文件在节点1，而客户端访问连接的节点1，那么可以正常操作；而若数据文件在节点1，而客户端访问连接的节点2，那么此时就会报错：
1 2 | ORA-01157: cannot identify/lock data file 8 - see DBWR trace file ORA-01110: data file 8: '/home/oracle/a.dbf' |
实验
1 2 3 4 5 6 7 | sql "alter database datafile 8 offline"; recover datafile 8; run { copy datafile '/home/oracle/a.dbf' to '+data'; } run { switch datafile '/home/oracle/a.dbf' to datafilecopy '+DATA/gbk/datafile/test.302.1120761841'; } sql "alter database datafile 8 online"; |
详细过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 | [oracle@xtrac1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \ > -gdbname gbk -sid gbk \ > -sysPassword oracle -systemPassword oracle \ > -datafileDestination '+DATA' -recoveryAreaDestination 'DATA/' \ > -storageType ASM -asmsnmpPassword oracle -diskGroupName 'DATA' \ > -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \ > -redoLogFileSize 50 \ > -sampleSchema true \ > -memoryPercentage 5 \ > -databaseType OLTP \ > -emConfiguration NONE \ > -nodeinfo xtrac1,xtrac2 Copying database files 1% complete 3% complete 9% complete 15% complete 21% complete 30% complete Creating and starting Oracle instance 32% complete 36% complete 40% complete 44% complete 45% complete 48% complete 50% complete Creating cluster database views 52% complete 70% complete Completing Database Creation 73% complete 76% complete 77% complete 78% complete 79% complete 88% complete 97% complete 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/gbk/gbk.log" for further details. [oracle@xtrac1 ~]$ crsctl stat res -t -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.DATA.dg ONLINE ONLINE xtrac1 ONLINE ONLINE xtrac2 ora.LISTENER.lsnr ONLINE ONLINE xtrac1 ONLINE ONLINE xtrac2 ora.OCR.dg ONLINE ONLINE xtrac1 ONLINE ONLINE xtrac2 ora.asm ONLINE ONLINE xtrac1 Started ONLINE ONLINE xtrac2 Started ora.gsd OFFLINE OFFLINE xtrac1 OFFLINE OFFLINE xtrac2 ora.net1.network ONLINE ONLINE xtrac1 ONLINE ONLINE xtrac2 ora.ons ONLINE ONLINE xtrac1 ONLINE ONLINE xtrac2 -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE xtrac2 ora.cvu 1 ONLINE ONLINE xtrac1 ora.xtorcl.db 1 ONLINE ONLINE xtrac1 Open 2 ONLINE ONLINE xtrac2 Open ora.xtrac1.vip 1 ONLINE ONLINE xtrac1 ora.xtrac2.vip 1 ONLINE ONLINE xtrac2 ora.gbk.db 1 ONLINE ONLINE xtrac1 Open 2 ONLINE ONLINE xtrac2 Open ora.oc4j 1 ONLINE ONLINE xtrac1 ora.scan1.vip 1 ONLINE ONLINE xtrac2 [oracle@xtrac1 ~]$ ORACLE_SID=gbk1 [oracle@xtrac1 ~]$ sas SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 17:43:09 2022 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> select * from v$tablespace; TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- --- 0 SYSTEM YES NO YES 1 SYSAUX YES NO YES 2 UNDOTBS1 YES NO YES 4 USERS YES NO YES 3 TEMP NO NO YES 6 EXAMPLE YES NO YES 5 UNDOTBS2 YES NO YES 7 rows selected. SQL> SQL> col name format a80 SQL> set pagesize 9999 SQL> set line 1000 SQL> SQL> select ts#,file#,name,status from v$datafile d; TS# FILE# NAME STATUS ---------- ---------- -------------------------------------------------------------------------------- ------- 0 1 +DATA/gbk/datafile/system.306.1120757563 SYSTEM 1 2 +DATA/gbk/datafile/sysaux.303.1120757563 ONLINE 2 3 +DATA/gbk/datafile/undotbs1.299.1120757563 ONLINE 4 4 +DATA/gbk/datafile/users.297.1120757563 ONLINE 6 5 +DATA/gbk/datafile/example.318.1120757617 ONLINE 5 6 +DATA/gbk/datafile/undotbs2.316.1120757677 ONLINE 6 rows selected. SQL> create tablespace test ; Tablespace created. SQL> select ts#,file#,name,status from v$datafile d; TS# FILE# NAME STATUS ---------- ---------- -------------------------------------------------------------------------------- ------- 0 1 +DATA/gbk/datafile/system.306.1120757563 SYSTEM 1 2 +DATA/gbk/datafile/sysaux.303.1120757563 ONLINE 2 3 +DATA/gbk/datafile/undotbs1.299.1120757563 ONLINE 4 4 +DATA/gbk/datafile/users.297.1120757563 ONLINE 6 5 +DATA/gbk/datafile/example.318.1120757617 ONLINE 5 6 +DATA/gbk/datafile/undotbs2.316.1120757677 ONLINE 7 7 +DATA/gbk/datafile/test.307.1120758253 ONLINE 7 rows selected. SQL> alter tablespace test add datafile '/home/oracle/a.dbf' size 10m; Tablespace altered. SQL> create table t1 tablespace test as select * from dba_objects; Table created. SQL> select ts#,file#,name,status from v$datafile d; TS# FILE# NAME STATUS ---------- ---------- -------------------------------------------------------------------------------- ------- 0 1 +DATA/gbk/datafile/system.306.1120757563 SYSTEM 1 2 +DATA/gbk/datafile/sysaux.303.1120757563 ONLINE 2 3 +DATA/gbk/datafile/undotbs1.299.1120757563 ONLINE 4 4 +DATA/gbk/datafile/users.297.1120757563 ONLINE 6 5 +DATA/gbk/datafile/example.318.1120757617 ONLINE 5 6 +DATA/gbk/datafile/undotbs2.316.1120757677 ONLINE 7 7 +DATA/gbk/datafile/test.307.1120758253 ONLINE 7 8 /home/oracle/a.dbf ONLINE 8 rows selected. SQL> select owner,segment_name,tablespace_name from dba_extents where file_id=8; OWNER SEGMENT_NAME TABLESPACE_NAME ------------------------------ --------------------------------------------------------------------------------- ------------------------------ SYS T1 TEST SYS T1 TEST SYS T1 TEST SYS T1 TEST SYS T1 TEST SQL> select count(*) from t1; COUNT(*) ---------- 87039 SQL> exit Disconnected from 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 -- 节点2查询 [oracle@xtrac2 ~]$ ORACLE_SID=gbk2 [oracle@xtrac2 ~]$ sas SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 18:32:50 2022 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> select count(*) from t1; select count(*) from t1 * ERROR at line 1: ORA-01157: cannot identify/lock data file 8 - see DBWR trace file ORA-01110: data file 8: '/home/oracle/a.dbf' SQL> exit Disconnected from 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 |
接下来做恢复操作：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 | RMAN> sql "alter database datafile 8 offline"; sql statement: alter database datafile 8 offline RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of sql command on default channel at 11/14/2022 18:37:35 RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 8 offline ORA-01145: offline immediate disallowed unless media recovery enabled RMAN> exit Recovery Manager complete. -- 2个节点都关闭，然后开启归档模式 [oracle@xtrac1 ~]$ sas SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 18:38:09 2022 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> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 2605551616 bytes Fixed Size 2256072 bytes Variable Size 721421112 bytes Database Buffers 1862270976 bytes Redo Buffers 19603456 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> exit Disconnected from 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 -- 开始恢复 [oracle@xtrac1 ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 14 18:41:12 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: GBK (DBID=2254511533) RMAN> sql "alter database datafile 8 offline"; using target database control file instead of recovery catalog sql statement: alter database datafile 8 offline RMAN> recover datafile 8; Starting recover at 14-NOV-22 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=68 instance=gbk1 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 14-NOV-22 RMAN> report schema; Report of database schema for database with db_unique_name GBK List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 760 SYSTEM *** +DATA/gbk/datafile/system.306.1120757563 2 520 SYSAUX *** +DATA/gbk/datafile/sysaux.303.1120757563 3 105 UNDOTBS1 *** +DATA/gbk/datafile/undotbs1.299.1120757563 4 5 USERS *** +DATA/gbk/datafile/users.297.1120757563 5 313 EXAMPLE *** +DATA/gbk/datafile/example.318.1120757617 6 25 UNDOTBS2 *** +DATA/gbk/datafile/undotbs2.316.1120757677 7 100 TEST *** +DATA/gbk/datafile/test.307.1120758253 8 10 TEST *** /home/oracle/a.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 56 TEMP 32767 +DATA/gbk/tempfile/temp.321.1120757617 RMAN> RMAN> RMAN> run { copy datafile '/home/oracle/a.dbf' to '+data'; } Starting backup at 14-NOV-22 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=/home/oracle/a.dbf output file name=+DATA/gbk/datafile/test.302.1120761841 tag=TAG20221114T184401 RECID=3 STAMP=1120761841 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 14-NOV-22 RMAN> run { switch datafile '/home/oracle/a.dbf' to datafilecopy '+DATA/gbk/datafile/test.302.1120761841'; } datafile 8 switched to datafile copy input datafile copy RECID=3 STAMP=1120761841 file name=+DATA/gbk/datafile/test.302.1120761841 RMAN> exit Recovery Manager complete. [oracle@xtrac1 ~]$ sas SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 18:45:34 2022 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> set line 1000 SQL> select ts#,file#,name,status from v$datafile d; TS# FILE# NAME STATUS ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------- 0 1 +DATA/gbk/datafile/system.306.1120757563 SYSTEM 1 2 +DATA/gbk/datafile/sysaux.303.1120757563 ONLINE 2 3 +DATA/gbk/datafile/undotbs1.299.1120757563 ONLINE 4 4 +DATA/gbk/datafile/users.297.1120757563 ONLINE 6 5 +DATA/gbk/datafile/example.318.1120757617 ONLINE 5 6 +DATA/gbk/datafile/undotbs2.316.1120757677 ONLINE 7 7 +DATA/gbk/datafile/test.307.1120758253 ONLINE 7 8 +DATA/gbk/datafile/test.302.1120761841 OFFLINE 8 rows selected. SQL> alter database datafile 8 online; Database altered. SQL> SQL> select count(*) from t1; COUNT(*) ---------- 87039 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
