ASM FTP 功能复制 ASM数据库

在Oracle10gR2 之后ASM 文件可以使用FTP来进行访问，但还是要满足两个条件

   1：安装XML DB

   2：使用DBCA 默认创建

 

默认情况下FTP 功能是不打开的，你可以使用 提供脚本

 SQL> @$ORACLE_HOME/rdbms/admin/catxdbdbca.sql 7001 8001

  7001 是命令行ftp 的端口

  8001 是通过http 方式访问的端口号码

  （7001，8001 可以在即设定但不能和其他通用端口冲突）

• 所有ASM 文件 FTP 点/sys/asm

可以通过HTTP web 方式登入来查看ASM 数据文件。

 

Index of /sys/asm/DG_DATA/ORCL/

	Tue, 27 Apr 2010 01:37:51 GMT	-
	Tue, 27 Apr 2010 01:37:51 GMT	-
	Tue, 27 Apr 2010 01:37:51 GMT	-
	Tue, 27 Apr 2010 01:37:51 GMT	-
	Tue, 27 Apr 2010 01:37:51 GMT	-
	Tue, 27 Apr 2010 01:37:51 GMT	2560

• 如果要做 数据库迁移或复制的话，要将ASM 数据库以ReadOnly 方式打开

SQL> startup mount
ORACLE instance started.

 

Total System Global Area  452984832 bytes
Fixed Size                  2073544 bytes
Variable Size             268438584 bytes
Database Buffers          176160768 bytes
Redo Buffers                6311936 bytes

 

Database mounted.

 

SQL> alter database open read only;

 

Database altered.

• 使用FTP 命令行来进行对文件copy
  
  bash-3.2$ ftp test_n1 7001
  Connected to test_n1.
  220- test_n1
  Unauthorised use of this FTPis prohibited and may be subject to civil and criminal prosecution.
  220 test_n1 FTP Server (Oracle XML DB/Oracle Database) ready.
  Name (test_n1:root): system
  331 pass required for SYSTEM    ---必需在数据库打开的情况下才能ftp
  Password:
  230 SYSTEM logged in
  ftp> cd /sys/asm
  250 CWD Command successful
  ftp> ls
  200 PORT Command successful
  150 ASCII Connection
  DG_FLASH
  DG_DATA
  226 ASCII Transfer Complete
  ftp> cd  DG_DATA
  250 CWD Command successful
  ftp> ls -al
  200 PORT Command successful
  150 ASCII Data Connection
  drw-r--r--   2 SYS      oracle         0 APR 27 19:12 .
  drw-r--r--   2 SYS      oracle         0 APR 27 19:12 ..
  drw-r--r--   2 SYS      oracle         0 APR 27 19:12 ORCL
  226 ASCII Transfer Complete
  ftp> cd ORCL
  250 CWD Command successful
  ftp> ls
  200 PORT Command successful
  150 ASCII Data Connection
  DATAFILE
  CONTROLFILE
  ONLINELOG
  TEMPFILE
  PARAMETERFILE
  spfileorcl.ora
  226 ASCII Transfer Complete
  ftp> cd DATAFILE
  250 CWD Command successful
  ftp> ls
  200 PORT Command successful
  150 ASCII Data Connection
  SYSTEM.256.716518143
  SYSAUX.257.716518145
  UNDOTBS1.258.716518149
  USERS.259.716518149
  EXAMPLE.265.716518299
  TESTLV.267.716520581
  226 ASCII Transfer Complete
  ftp>
  

ftp> get SYSTEM.256.716518143   
Illegal operation.  Use the COPY command
local and remote hosts are the same as well as the filenames.

 

---需要先将Copy 模式打开
ftp> copy
Copylocal mode on.

进行文件Copy 时先要将copy 选项置为 on(如果要置为 off 则再输入copy 就可以了)
ftp> copy
Copylocal mode on.

 

ftp> bin
200 Type set to I.

ftp> get SYSTEM.256.716518143
200 PORT Command successful
150 ASCII Data Connection
550- Error Response
ORA-31198: Mismatch in number of bytes transferred due to non-binary mode
550 End Error Response
271609 bytes received in 0.09863 seconds (2689 Kbytes/s)
local: SYSTEM.256.716518143 remote: SYSTEM.256.716518143

 

----将所有的文件名全部get 下来

• 重新登入到ASM 数据库创建 pfile 和 controlfile

 

SQL> create pfile='/tmp/initlisdb.ora' from spfile;

 

File created.

 

SQL> alter database backup controlfile to trace as '/tmp/cnt.ctl';

 

Database altered.

 

• 修改Pfile 中的参数

  修改control file，file_dest  等等

 

__db_cache_size=180355072
__java_pool_size=4194304
__large_pool_size=4194304
__shared_pool_size=255852544
__streams_pool_size=0
*.audit_file_dest='/u02/admin/lisdb/adump'
*.background_dump_dest='/u02/admin/lisdb/bdump'
*.compatible='10.2.0.3.0'
*.control_files='cntlisdb01.ctl','cntlisdb02.ctl'
*.core_dump_dest='/u02/admin/orcl/cdump'
*.db_block_size=8192
*.db_create_file_dest='/u02/lisdb'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='lisdb'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=149946368
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=450887680
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u02/admin/lisdb/udump'
bash-3.2$ PuTTY
bash: PuTTY: command not found
bash-3.2$
bash-3.2$

 

• 启动lisdb 到nomount 状态（使用修改后的pfile）

SQL> startup nomount pfile=initlisdb.ora
ORACLE instance started.

 

Total System Global Area  452984832 bytes
Fixed Size                  2073544 bytes
Variable Size             268438584 bytes
Database Buffers          176160768 bytes
Redo Buffers                6311936 bytes

• 修改 control file:

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
   '+DG_DATA/orcl/onlinelog/group_1.261.716518251',
    '+DG_FLASH/orcl/onlinelog/group_1.257.716518253'
  ) SIZE 50M,
  GROUP 2 (
    '+DG_DATA/orcl/onlinelog/group_2.262.716518255',
    '+DG_FLASH/orcl/onlinelog/group_2.258.716518255'
  ) SIZE 50M,
  GROUP 3 (
    '+DG_DATA/orcl/onlinelog/group_3.263.716518257',
    '+DG_FLASH/orcl/onlinelog/group_3.259.716518259'
  ) SIZE 50M
-- STANDBY LOGFILE

 

DATAFILE
 '+DG_DATA/orcl/datafile/system.256.716518143',
  '+DG_DATA/orcl/datafile/undotbs1.258.716518149',
  '+DG_DATA/orcl/datafile/sysaux.257.716518145',
  '+DG_DATA/orcl/datafile/users.259.716518149',
  '+DG_DATA/orcl/datafile/example.265.716518299',
  '+DG_DATA/orcl/datafile/testlv.267.716520581'
CHARACTER SET WE8ISO8859P1
;

 

STARTUP NOMOUNT
CREATE CONTROLFILEsetDATABASE "LISDB"RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/lisdb/redo1.log' SIZE 50M,
  GROUP 2 '/u02/lisdb/redo2.log' SIZE 50M,
  GROUP 3 '/u02/lisdb/redo3.log' SIZE 50M
DATAFILE
  '/u02/lisdb/SYSTEM.256.716518143',
  '/u02/lisdb/UNDOTBS1.258.716518149',
  '/u02/lisdb/SYSAUX.257.716518145',
  '/u02/lisdb/USERS.259.716518149',
  '/u02/lisdb/EXAMPLE.265.716518299',
  '/u02/lisdb/TESTLV.267.716520581'
CHARACTER SET WE8ISO8859P1
;

• 重建Crontrol file

SQL> CREATE CONTROLFILE set DATABASE "LISDB" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  GROUP 1 '/u02/lisdb/redo1.log' SIZE 50M,
  GROUP 2 '/u02/lisdb/redo2.log' SIZE 50M,
  GROUP 3 '/u02/lisdb/redo3.log' SIZE 50M
  8    9   10   11  DATAFILE
 12    '/u02/lisdb/SYSTEM.256.716518143',
 13    '/u02/lisdb/UNDOTBS1.258.716518149',
 14    '/u02/lisdb/SYSAUX.257.716518145',
 15    '/u02/lisdb/USERS.259.716518149',
 16    '/u02/lisdb/EXAMPLE.265.716518299',
 17    '/u02/lisdb/TESTLV.267.716520581'
 18  CHARACTER SET WE8ISO8859P1
 19  ;

 

• 打开数据库添加temp tablespace
   
  ALTER DATABASE OPEN resetlogs;
   
  添加temp tablepsace
   
  ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/lisdb/temp.264.716518279'
       SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

 

 

• 检查数据库 lisdb 已经被listener 接管请求

 

bash-3.2$ lsnrctl status

 

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production on 27-APR-2010 10:56:54

 

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test_n1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production
Start Date                17-APR-2010 01:27:36
Uptime                    10 days 9 hr. 29 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u02/oracle/product/10.2.0/network/admin/listener.ora
Listener Log File         /u02/oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test_n1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test_n1)(PORT=8001))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test_n1)(PORT=7001))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "lisdb" has 1 instance(s).
  Instance "lisdb", status READY, has 1 handler(s) for this service...
Service "lisdb_XPT" has 1 instance(s).
  Instance "lisdb", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "lisdb", status READY, has 1 handler(s) for this service...
The command completed successfully

 

• 个人感受：

通过ASM FTP 功能还是比较方便的对ASM 数据库进行复制或迁移（包括ASM 数据库到 ASM 数据库，或ASM 数据库到文件系统数据库）。

这种方式也是对RMAN Clone数据库的一个补充，使用起来也简单明了。

不失为一个数据库迁移的好方法。