【翻譯自mos文章】在rac中,使用asmcmd命令從 檔案系統 move system datafile 到asm磁碟組的方法,asmcmddatafile
在rac中,使用asmcmd命令從 檔案系統 move system datafile 到asm磁碟組的方法。
參考原文:
How to move a SYSTEM datafile from filesystem to the ASM diskgroup using ASMCMD on RAC. (Doc ID 1607292.1)
適用於:
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.1 [Release 10.2]
Information in this document applies to any platform.
癥狀:
由於誤操作,客戶在為system 資料表空間添加datafile時,沒有帶+號,添加datafile的命令執行後,datafile被建立在了一個節點的 $ORACLE_HOME/dbs 下。其他節點看不到這個新添加的datafile,會報ORA-01157: cannot identify/lock data file
需要move 該system表空的datafile 到 asm磁碟組中。
原因:
datafile被建立在了檔案系統中,而不是建立在asm磁碟組中。
[oracle@mbrac2 ~]$ echo $ORACLE_SID
ORCL2
File exists on one node but not on the other:
[oracle@mbrac2 dbs]$ ls -ltr
total 524848
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 1536 Nov 28 16:12 orapwORCL2
-rw-r-----. 1 oracle oinstall 35 Nov 28 16:13 initORCL2.ora
-rw-rw----. 1 oracle dba 1544 Dec 3 15:23 hc_ORCL2.dat
-rw-r-----. 1 oracle dba 536879104 Dec 10 17:56 DATA ==============<<<<HERE
[oracle@mbrac1 dbs]$ ls -ltr
total 32
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 1536 Nov 28 16:12 orapwORCL1
-rw-r-----. 1 oracle oinstall 35 Nov 28 16:13 initORCL1.ora
-rw-rw----. 1 oracle dba 1544 Dec 3 15:23 hc_ORCL1.dat
上面的的顯示可以看出:DATA只在mbrac2上有,在mbrac1上沒有這個檔案。
解決方案:
1.stop db
2.使用asmcmd命令move datafile
3.mount db
4.rename 該datafile
5.open db
6.在其他rac節點上重啟資料庫執行個體,否則會遇到錯誤ORA-01516 or original error ORA-01157: cannot identify/lock data file
======下面是具體的步驟=======
1.stop db
對於system 資料表空間,不得不stop db,否則會報錯:ORA-01541: system tablespace cannot be brought offline
[oracle@mbrac1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
SQL> select TABLESPACE_NAME, file_name, file_id, online_status from dba_data_files where TABLESPACE_NAME='SYSTEM';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID ONLINE_
---------- -------
SYSTEM
+DATA/orcl/datafile/system.256.832695063
1 SYSTEM
SYSTEM
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA
6 SYSTEM
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
No errors in alert.log
2. 使用asmcmd cp move datafile。注意:別名是正常的,因為這不是系統指派別名。
ASMCMD> cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA +DATA/ORCL/DATAFILE/SYSTEM2
copying /u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA -> +DATA/ORCL/DATAFILE/SYSTEM2
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y USERS.259.832695063
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y UNDOTBS2.264.832695177
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y UNDOTBS1.258.832695063
N SYSTEM2 => +DATA/ASM/DATAFILE/SYSTEM2.268.833825159
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y SYSTEM.256.832695063
DATAFILE UNPROT COARSE DEC 10 18:00:00 Y SYSAUX.257.832695063
--->注意 oracle 10g的時候還沒有asmcmd下的cp命令,
--->因此,若是10g,請參考NOTE:390274.1 - How to move a datafile from a file system to ASM
3. mount database
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 805310440 bytes
Database Buffers 436207616 bytes
Redo Buffers 8892416 bytes
Database mounted.
4. rename datafile :
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA' to '+DATA/ORCL/DATAFILE/SYSTEM2';
Database altered.
5.open db:
SQL> alter database open;
Database altered.
6. 在其他rac節點上重啟資料庫執行個體,否則會遇到錯誤ORA-01516 or original error ORA-01157: cannot identify/lock data file
在所有執行個體上對比system資料表空間的datafile,看是否一樣。
[oracle@mbrac1 dbs]$ echo $ORACLE_SID
ORCL1
[oracle@mbrac1 dbs]$
[oracle@mbrac1 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 10 19:00:47 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: / as sysdba
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;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 872419304 bytes
Database Buffers 369098752 bytes
Redo Buffers 8892416 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select TABLESPACE_NAME, file_name, file_id from dba_data_files where TABLESPACE_NAME='SYSTEM';
TABLESPACE_NAME FILE_NAME FILE_ID
--------------------------------------------------------------------------------
SYSTEM
+DATA/orcl/datafile/system.256.832695063
1
SYSTEM
+DATA/orcl/datafile/system2
6
SQL> select INSTANCE_NUMBER, INSTANCE_NAME from v$instance ;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
1 ORCL1
Then on the second node:
SQL> select TABLESPACE_NAME, file_name, file_id, online_status from dba_data_files where TABLESPACE_NAME='SYSTEM';
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_
--------------------------------------------------------------------------------
SYSTEM +DATA/orcl/datafile/system.256.832695063
1 SYSTEM
SYSTEM +DATA/orcl/datafile/system2
6 SYSTEM
SQL> select INSTANCE_NUMBER, INSTANCE_NAME from v$instance ;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
2 ORCL2
linux 中 ASM命令作用
oracle 10R2推出了asmcmd這個工具,可以用來管理asm儲存上的檔案。
1.使用asmcmd命令必須先啟動asm執行個體,不然會有以下報錯:rac2-> asmcmd -p
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory (DBD ERROR: OCISessionBegin)2.使用asmcmd必須先指定ORACLE_HOME和ORACLE_SID,注意此處ORACLE_SID是asm的sid,不然會報錯:rac2-> asmcmd -p
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory (DBD ERROR: OCISessionBegin)3.asmcmd命令帶p參數和不帶p參數的作用:
帶p,使用asmcmd將顯示當前路徑:rac2-> asmcmd -p
ASMCMD [+] > ls
DG1/
RECOVERDEST/
ASMCMD [+] > cd DG1
ASMCMD [+DG1] > ls
DEVDB/
ASMCMD [+DG1] > cd DEVDB
ASMCMD [+DG1/DEVDB] >不帶p,不顯示當前路徑:rac2-> asmcmd
ASMCMD> ls
DG1/
RECOVERDEST/
ASMCMD> cd DG1
ASMCMD> ls
DEVDB/
ASMCMD> cd DEVDB
ASMCMD>4.其他相關參數,可使用help查看ASMCMD [+] > help
asmcmd [-p] [command]
The environment variables ORACLE_HOME and ORACLE_SID determine the
instance to which the program connects, and ASMCMD establishes a
bequeath connection to it, in the same manner as a SQLPLUS / AS
SYSDBA. The user must be a member of the SYSDBA group.
Specifying the -p option allows the current directory to be displayed
in the command prompt, like so:
ASMCMD [+DATAFILE/ORCL/CONTROLFILE] >
[command] specifies one of the following commands, along with its
parameters.
Type "h......餘下全文>>