如果数据库使用ASM管理,如何把ASM的文件拷贝到本地磁盘?
10gR1可以通过rman copy or dbms_file_transfer;10gR2还可以通过本文要说的ftp;11g直接使用asmcmd的cp命令即可。
When ASM was introduced in Oracle 10.1, there was no supported way of viewing or modifying the
contents of the file system. In Oracle 10.2, you can use FTP to access the ASM file system.
In order to use FTP, you must have installed XML DB, which is included in the default DBCA
installation. You must manually configure port numbers for FTP and HTTP/WebDAV. These can be
configured using either the Enterprise Manager XDB Configuration page or SQL*Plus. We recommend
the latter.
To configure the port numbers using SQL*Plus, first assign spare port numbers for each service.
In our example, we will use 7777 for FTP and 8888 for HTTP/WebDAV. Make sure that any ports you
allocate are not in use by any other services. The ports are configured using the script $ORACLE_HOME/
rdbms/admin/catxdbdbca.sql, which should be run by a user with SYSDBA privileges. The script takes
the FTP port as its first parameter and the HTTP/WebDAV port as its second parameter:
SQL> @$ORACLE_HOME/rdbms/admin/catxdbdbca.sql 7777 8888
When the port numbers have been assigned, the FTP service is available for immediate use.
By default, the FTP client will attempt to use Kerberos authentication when connecting to the
FTP server. To suppress this specify the -u option when running ftp:
london1 $ ftp -u
The FTP prompt should be displayed. You can then open the host. For example, if you are running
the FTP session on the same server, you might specify the following host and port number:
ftp> open localhost 7777
You will then be prompted for a username and password. Note that this should be an Oracle
username, rather than an operating system username:
ftp> user system
331 pass required for SYSTEM
Password: oracle
230 SYSTEM logged in
If an ASM instance exists, then the ASM file system will be located under the /sys/asm directory.
Each disk group will have its own subdirectory. You can use most of the standard FTP functionality,
as in the following example:
ftp> cd /sys/asm/DISKGROUP1/RAC/CONTROLFILE
ftp> ls
-rw-r--r-- 1 SYS oracle 7258112 SEP 08 18:30 Current.305.566832105
ftp> bin
ftp> get Current.305.566832105
ftp> bye
The preceding session navigates to the TEST/CONTROLFILE directory in DISKGROUP1 and copies
the current control file to the local working directory. You can also use the put command to write
files back to the ASM file system. If you are transferring binary files using FTP, make sure you first
change the default file type to binary using the bin command. Note that the FTP wildcard commands
mget and mput do not appear to work, so you will need to copy each file individually.
We recommend exercising some caution when using FTP to write files to the ASM file system,
as it is possible to corrupt the database if you overwrite an existing file.
其实很简单,只需sys用户执行 @?/rdbms/admin/catxdbdbca.sql 7070 8080
后面的两个端口可以自己指定,7070是ftp端口,8080是web端口。
SQL> @?/rdbms/admin/catxdbdbca.sql 7070 8080
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> define ftpport = &1
SQL> define httpport = &2
SQL> Rem Create a function to traverse the dom elements.
SQL> CREATE OR REPLACE FUNCTION traverseDom
2 (parnode dbms_xmldom.DOMNode, pathSeg VARCHAR2)
3 RETURN dbms_xmldom.DOMNode IS
4 nodeList dbms_xmldom.DOMNodeList;
5 anElement dbms_xmldom.DOMElement;
6 aNode dbms_xmldom.DOMNode;
7 BEGIN
8 -- Convert the passed in dom node to an element
9 anElement := dbms_xmldom.makeElement(parnode);
10
11 -- Select the path segment requested by the user
12 nodeList := dbms_xmldom.getChildrenByTagName(anElement, pathSeg);
13
14 -- get the first node out of the list
15 aNode := dbms_xmldom.item(nodeList, 0);
16
17 -- return that node (ignore errors here).
18 return aNode;
19 END;
20 /
Function created.
SQL>
SQL> declare
2 configxml sys.xmltype;
3 configdomdoc dbms_xmldom.DOMDocument;
4 textNode dbms_xmldom.DOMNode;
5 aNode dbms_xmldom.DOMNode;
6 protNode dbms_xmldom.DOMNode;
7 anElement dbms_xmldom.DOMElement;
8 listOfNodes dbms_xmldom.DOMNodeList;
9 aString VARCHAR2(100);
10 begin
11
12 -- Select the resource and set it into the config
13 select sys_nc_rowinfo$ into configxml from xdb.xdb$config ;
14
15 -- Create a dom document out of the xmltype
16 configdomdoc := dbms_xmldom.newDOMDocument(configxml);
17
18 -- Get the root Element of the dom
19 anElement := dbms_xmldom.getDocumentElement(configdomdoc);
20
21 -- Convert this to a node
22 aNode := dbms_xmldom.makeNode(anElement);
23
24 -- Traverse One Element Down At A Time.
25 aNode := traverseDom(aNode, 'sysconfig');
26 protNode := traverseDom(aNode, 'protocolconfig');
27
28 -- Set the FTP port by traversing /ftpconfig/ftp-port
29 aNode := traverseDom(protNode, 'ftpconfig');
30 aNode := traverseDom(aNode, 'ftp-port');
31 textNode := dbms_xmldom.getFirstChild(aNode);
32 dbms_xmldom.setNodeValue(textNode, &ftpport);
33
34 -- Set the FTP port by traversing /ftpconfig/ftp-port
35 aNode := traverseDom(protNode, 'httpconfig');
36 aNode := traverseDom(aNode, 'http-port');
37 textNode := dbms_xmldom.getFirstChild(aNode);
38 dbms_xmldom.setNodeValue(textNode, &httpport);
39
40 dbms_xdb.cfg_update(configxml);
41 commit;
42
43 end;
44 /
old 32: dbms_xmldom.setNodeValue(textNode, &ftpport);
new 32: dbms_xmldom.setNodeValue(textNode, 7070);
old 38: dbms_xmldom.setNodeValue(textNode, &httpport);
new 38: dbms_xmldom.setNodeValue(textNode, 8080);
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop function traverseDom;
Function dropped.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[root@xxtdwhtest ~]# netstat -antp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:8080 0.0.0.0:* LISTEN 12143/tnslsnr
tcp 0 0 0.0.0.0:7070 0.0.0.0:* LISTEN 12143/tnslsnr
......
通过上面的网络端口可以看出,实际上是oracle的listener在监听那两个端口。
登陆需要一个非SYS的DBA账号(其实使用sys用户可以登录,但是无法访问asm目录)
1、ftp方式
和普通ftp使用一样,大部分ftp命令都支持。
[root@xxtdwhtest oracle]# ftp
ftp> open 192.168.12.21 7070
Connected to 192.168.12.21.
220- xxtdwhtest
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 xxtdwhtest FTP Server (Oracle XML DB/Oracle Database) ready.
530 Please login with USER and PASS.
530 Please login with USER and PASS.
KERBEROS_V4 rejected as an authentication type
Name (192.168.12.21:root): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
Remote system type is Unix.
ftp> cd sys/asm/data/zhh1
250 CWD Command successful
ftp> ls
227 Entering Passive Mode (192,168,12,21,55,228)
150 ASCII Data Connection
drw-r--r-- 2 SYS oracle 0 FEB 21 07:52 CONTROLFILE
drw-r--r-- 2 SYS oracle 0 FEB 21 07:52 DATAFILE
drw-r--r-- 2 SYS oracle 0 FEB 21 07:52 TEMPFILE
drw-r--r-- 2 SYS oracle 0 FEB 21 07:52 ONLINELOG
226 ASCII Transfer Complete
ftp> cd datafile
250 CWD Command successful
ftp> ls
227 Entering Passive Mode (192,168,12,21,150,234)
150 ASCII Data Connection
-rw-r--r-- 1 SYS oracle 1073750016 FEB 21 07:52 STREAM.263.739890369
-rw-r--r-- 1 SYS oracle 1073750016 FEB 21 07:52 EDUCATION.259.739890465
-rw-r--r-- 1 SYS oracle 2161115136 FEB 21 07:52 UNDOTBS1.260.739890561
-rw-r--r-- 1 SYS oracle 534781952 FEB 21 07:52 SYSTEM.262.739890655
-rw-r--r-- 1 SYS oracle 524296192 FEB 21 07:52 INDX.257.739890711
-rw-r--r-- 1 SYS oracle 399507456 FEB 21 07:52 SYSAUX.261.739890755
-rw-r--r-- 1 SYS oracle 5251072 FEB 21 07:52 USERS.258.739890791
226 ASCII Transfer Complete
ftp> lcd /usr/oracle
Local directory now /usr/oracle
ftp> get USERS.258.739890791
local: USERS.258.739890791 remote: USERS.258.739890791
227 Entering Passive Mode (192,168,12,21,38,96)
150 ASCII Data Connection
550- Error Response
ORA-31198: Mismatch in number of bytes transferred due to non-binary mode
550 End Error Response
270349 bytes received in 0.051 seconds (5.2e+03 Kbytes/s)
ftp> bye
221 QUIT Goodbye.
2、web方式
http://192.168.12.21:8080/
10gR1可以通过rman copy or dbms_file_transfer;10gR2还可以通过本文要说的ftp;11g直接使用asmcmd的cp命令即可。
When ASM was introduced in Oracle 10.1, there was no supported way of viewing or modifying the
contents of the file system. In Oracle 10.2, you can use FTP to access the ASM file system.
In order to use FTP, you must have installed XML DB, which is included in the default DBCA
installation. You must manually configure port numbers for FTP and HTTP/WebDAV. These can be
configured using either the Enterprise Manager XDB Configuration page or SQL*Plus. We recommend
the latter.
To configure the port numbers using SQL*Plus, first assign spare port numbers for each service.
In our example, we will use 7777 for FTP and 8888 for HTTP/WebDAV. Make sure that any ports you
allocate are not in use by any other services. The ports are configured using the script $ORACLE_HOME/
rdbms/admin/catxdbdbca.sql, which should be run by a user with SYSDBA privileges. The script takes
the FTP port as its first parameter and the HTTP/WebDAV port as its second parameter:
SQL> @$ORACLE_HOME/rdbms/admin/catxdbdbca.sql 7777 8888
When the port numbers have been assigned, the FTP service is available for immediate use.
By default, the FTP client will attempt to use Kerberos authentication when connecting to the
FTP server. To suppress this specify the -u option when running ftp:
london1 $ ftp -u
The FTP prompt should be displayed. You can then open the host. For example, if you are running
the FTP session on the same server, you might specify the following host and port number:
ftp> open localhost 7777
You will then be prompted for a username and password. Note that this should be an Oracle
username, rather than an operating system username:
ftp> user system
331 pass required for SYSTEM
Password: oracle
230 SYSTEM logged in
If an ASM instance exists, then the ASM file system will be located under the /sys/asm directory.
Each disk group will have its own subdirectory. You can use most of the standard FTP functionality,
as in the following example:
ftp> cd /sys/asm/DISKGROUP1/RAC/CONTROLFILE
ftp> ls
-rw-r--r-- 1 SYS oracle 7258112 SEP 08 18:30 Current.305.566832105
ftp> bin
ftp> get Current.305.566832105
ftp> bye
The preceding session navigates to the TEST/CONTROLFILE directory in DISKGROUP1 and copies
the current control file to the local working directory. You can also use the put command to write
files back to the ASM file system. If you are transferring binary files using FTP, make sure you first
change the default file type to binary using the bin command. Note that the FTP wildcard commands
mget and mput do not appear to work, so you will need to copy each file individually.
We recommend exercising some caution when using FTP to write files to the ASM file system,
as it is possible to corrupt the database if you overwrite an existing file.
其实很简单,只需sys用户执行 @?/rdbms/admin/catxdbdbca.sql 7070 8080
后面的两个端口可以自己指定,7070是ftp端口,8080是web端口。
SQL> @?/rdbms/admin/catxdbdbca.sql 7070 8080
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> define ftpport = &1
SQL> define httpport = &2
SQL> Rem Create a function to traverse the dom elements.
SQL> CREATE OR REPLACE FUNCTION traverseDom
2 (parnode dbms_xmldom.DOMNode, pathSeg VARCHAR2)
3 RETURN dbms_xmldom.DOMNode IS
4 nodeList dbms_xmldom.DOMNodeList;
5 anElement dbms_xmldom.DOMElement;
6 aNode dbms_xmldom.DOMNode;
7 BEGIN
8 -- Convert the passed in dom node to an element
9 anElement := dbms_xmldom.makeElement(parnode);
10
11 -- Select the path segment requested by the user
12 nodeList := dbms_xmldom.getChildrenByTagName(anElement, pathSeg);
13
14 -- get the first node out of the list
15 aNode := dbms_xmldom.item(nodeList, 0);
16
17 -- return that node (ignore errors here).
18 return aNode;
19 END;
20 /
Function created.
SQL>
SQL> declare
2 configxml sys.xmltype;
3 configdomdoc dbms_xmldom.DOMDocument;
4 textNode dbms_xmldom.DOMNode;
5 aNode dbms_xmldom.DOMNode;
6 protNode dbms_xmldom.DOMNode;
7 anElement dbms_xmldom.DOMElement;
8 listOfNodes dbms_xmldom.DOMNodeList;
9 aString VARCHAR2(100);
10 begin
11
12 -- Select the resource and set it into the config
13 select sys_nc_rowinfo$ into configxml from xdb.xdb$config ;
14
15 -- Create a dom document out of the xmltype
16 configdomdoc := dbms_xmldom.newDOMDocument(configxml);
17
18 -- Get the root Element of the dom
19 anElement := dbms_xmldom.getDocumentElement(configdomdoc);
20
21 -- Convert this to a node
22 aNode := dbms_xmldom.makeNode(anElement);
23
24 -- Traverse One Element Down At A Time.
25 aNode := traverseDom(aNode, 'sysconfig');
26 protNode := traverseDom(aNode, 'protocolconfig');
27
28 -- Set the FTP port by traversing /ftpconfig/ftp-port
29 aNode := traverseDom(protNode, 'ftpconfig');
30 aNode := traverseDom(aNode, 'ftp-port');
31 textNode := dbms_xmldom.getFirstChild(aNode);
32 dbms_xmldom.setNodeValue(textNode, &ftpport);
33
34 -- Set the FTP port by traversing /ftpconfig/ftp-port
35 aNode := traverseDom(protNode, 'httpconfig');
36 aNode := traverseDom(aNode, 'http-port');
37 textNode := dbms_xmldom.getFirstChild(aNode);
38 dbms_xmldom.setNodeValue(textNode, &httpport);
39
40 dbms_xdb.cfg_update(configxml);
41 commit;
42
43 end;
44 /
old 32: dbms_xmldom.setNodeValue(textNode, &ftpport);
new 32: dbms_xmldom.setNodeValue(textNode, 7070);
old 38: dbms_xmldom.setNodeValue(textNode, &httpport);
new 38: dbms_xmldom.setNodeValue(textNode, 8080);
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop function traverseDom;
Function dropped.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[root@xxtdwhtest ~]# netstat -antp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:8080 0.0.0.0:* LISTEN 12143/tnslsnr
tcp 0 0 0.0.0.0:7070 0.0.0.0:* LISTEN 12143/tnslsnr
......
通过上面的网络端口可以看出,实际上是oracle的listener在监听那两个端口。
登陆需要一个非SYS的DBA账号(其实使用sys用户可以登录,但是无法访问asm目录)
1、ftp方式
和普通ftp使用一样,大部分ftp命令都支持。
[root@xxtdwhtest oracle]# ftp
ftp> open 192.168.12.21 7070
Connected to 192.168.12.21.
220- xxtdwhtest
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 xxtdwhtest FTP Server (Oracle XML DB/Oracle Database) ready.
530 Please login with USER and PASS.
530 Please login with USER and PASS.
KERBEROS_V4 rejected as an authentication type
Name (192.168.12.21:root): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
Remote system type is Unix.
ftp> cd sys/asm/data/zhh1
250 CWD Command successful
ftp> ls
227 Entering Passive Mode (192,168,12,21,55,228)
150 ASCII Data Connection
drw-r--r-- 2 SYS oracle 0 FEB 21 07:52 CONTROLFILE
drw-r--r-- 2 SYS oracle 0 FEB 21 07:52 DATAFILE
drw-r--r-- 2 SYS oracle 0 FEB 21 07:52 TEMPFILE
drw-r--r-- 2 SYS oracle 0 FEB 21 07:52 ONLINELOG
226 ASCII Transfer Complete
ftp> cd datafile
250 CWD Command successful
ftp> ls
227 Entering Passive Mode (192,168,12,21,150,234)
150 ASCII Data Connection
-rw-r--r-- 1 SYS oracle 1073750016 FEB 21 07:52 STREAM.263.739890369
-rw-r--r-- 1 SYS oracle 1073750016 FEB 21 07:52 EDUCATION.259.739890465
-rw-r--r-- 1 SYS oracle 2161115136 FEB 21 07:52 UNDOTBS1.260.739890561
-rw-r--r-- 1 SYS oracle 534781952 FEB 21 07:52 SYSTEM.262.739890655
-rw-r--r-- 1 SYS oracle 524296192 FEB 21 07:52 INDX.257.739890711
-rw-r--r-- 1 SYS oracle 399507456 FEB 21 07:52 SYSAUX.261.739890755
-rw-r--r-- 1 SYS oracle 5251072 FEB 21 07:52 USERS.258.739890791
226 ASCII Transfer Complete
ftp> lcd /usr/oracle
Local directory now /usr/oracle
ftp> get USERS.258.739890791
local: USERS.258.739890791 remote: USERS.258.739890791
227 Entering Passive Mode (192,168,12,21,38,96)
150 ASCII Data Connection
550- Error Response
ORA-31198: Mismatch in number of bytes transferred due to non-binary mode
550 End Error Response
270349 bytes received in 0.051 seconds (5.2e+03 Kbytes/s)
ftp> bye
221 QUIT Goodbye.
2、web方式
http://192.168.12.21:8080/