这篇文章主要介绍ASM与File System数据文件转移方法有哪些,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!1、环境介绍实验使用Oracle Linux环境,对应底层Oracle 11g单实例+ASM存储方案。[root@SimpleLinux ~]# uname -r2.6.18-128.el5[root@SimpleLinux ~]# ps -ef | grep pmongrid 3214 1 0 13:53 ? 00:00:00
asm_pmon_+ASMoracle 3294 1 0 13:53 ? 00:00:00
ora_pmon_ora11groot 3376 3107 0 13:53 pts/0 00:00:00 grep pmon数据库使用11gR2版本。SQL> select * from v$version;BANNER————————-Oracle Database 11g En免费主机域名terprise Edition Release 11.2.0.3.0 – ProductionPL/SQL Release 11.2.0.3.0 – ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 – ProductionNLSRTL Version 11.2.0.3.0 – Production建立一个数据文件目录。[oracle@SimpleLinux oracle]$ cd $ORACLE_HOME[oracle@SimpleLinux db_1]$ mkdir oradata[oracle@SimpleLinux db_1]$ ls -l | grep oradatadrwxr-xr-x 2 oracle oinstall 4096 Dec 27 13:56 oradata数据库采用归档模式,注意:下述实验要求在归档模式才能实现。SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 25Next log sequence to archive 27Current log sequence 27创建一个实验文件表空间。SQL> create tablespace test datafile ‘/u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf’ size 100m 2 extent management local uniform size 1m 3 segment space management auto;Tablespace createdSQL> select file_name, tablespace_name from dba_data_files;FILE_NAME TABLESPACE_NAME————————————————– ——————————+DATA/ora11g/datafile/users.259.825944329 USERS+DATA/ora11g/datafile/undotbs1.258.825944329 UNDOTBS1+DATA/ora11g/datafile/sysaux.257.825944327 SYSAUX+DATA/ora11g/datafile/system.256.825944325 SYSTEM+DATA/ora11g/datafile/example.265.825944513 EXAMPLE/u01/app/oracle/product/11.2.0/db_1/oradata/testtb TESTl.dbf
6 rows selected2、ASMCMD命令ASMCMD是Oracle提供的管理ASM的命令行工具。进入11g之后,ASMCMD提供了cp命令,仿照Linux平台的cp命令。ASMCMD是我们事先拷贝的一种选择。我们实验是将文件系统的TEST文件,转移到+DATA ASM DiskGroup中。首先切换一下logfile。SQL> alter system switch logfile;System altered–我们事先online移动,是进行offline之后才行;SQL> alter database datafile ‘/u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf’
offline;Database alteredSQL> select file_name, online_status from dba_data_files where file_id=6;FILE_NAME ONLINE_STATUS————————————————– ————-/u01/app/oracle/product/11.2.0/db_1/oradata/testtb
RECOVERl.dbf进入ASMCMD命令行进行处理。–使用grid用户,注意这个过程是伴随着登录ASM实例过程的。[oracle@SimpleLinux oradata]$ su – gridPassword:[grid@SimpleLinux ~]$ asmcmdASMCMD>
cp /u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf +DATA/ora11g/datafile/testtbl.dbfcopying /u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf -> +DATA/ora11g/datafile/testtbl.dbf在ASM中,查看到文件的确保存在目录了。ASMCMD> ls -lType Redund Striped Time Sys NameDATAFILE UNPROT COARSE DEC 27 13:00:00 Y EXAMPLE.265.825944513DATAFILE UNPROT COARSE DEC 27 13:00:00 Y SYSAUX.257.825944327DATAFILE UNPROT COARSE DEC 27 13:00:00 Y SYSTEM.256.825944325DATAFILE UNPROT COARSE DEC 27 13:00:00 Y UNDOTBS1.258.825944329DATAFILE UNPROT COARSE DEC 27 13:00:00 Y USERS.259.825944329 N testtbl.dbf => +DATA/ASM/DATAFILE/testtbl.dbf.268.835279797另外在Oracle的控制文件体系中,修改文件名称。SQL> alter database rename file ‘/u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf’ to ‘+DATA/ora11g/datafile/testtbl.dbf’;Database alteredSQL> select file_name, file_id from dba_data_files;FILE_NAME FILE_ID————————————————– ———-(篇幅原因,有省略……)+DATA/ora11g/datafile/example.265.825944513 5+DATA/ora11g/datafile/testtbl.dbf 66 rows selected由于进行online操作,对数据文件6进行恢复、上线。SQL> alter database
recover datafile 6;Database alteredSQL> alter database
datafile 6 online;Database alteredSQL> select file_name, file_id, online_status from dba_data_files;FILE_NAME FILE_ID ONLINE_STATUS————————————————– ———- ————-(篇幅原因,有省略……)+DATA/ora11g/datafile/example.265.825944513 5 ONLINE+DATA/ora11g/datafile/testtbl.dbf 6 ONLINE6 rows selected原来的数据文件,删掉就好了。[oracle@SimpleLinux oradata]$ ls -ltotal 102512-rw-r—– 1 oracle asmadmin 104865792 Dec 27 14:02 testtbl.dbf[oracle@SimpleLinux oradata]$ rm testtbl.dbf试验成功。3、DBMS_FILE_TRANSFER包方法Oracle PL/SQL中包dbms_file_transfer也可以实现传输。其中的copy_file过程,可以实现将文件传输跨越ASM和文件系统。使用dbms_file_transfer包前提需要指定文件目录,在Oracle中是通过directory对象实现的。SQL> create directory
souredir
as ‘+DATA/ora11g/datafile/’;Directory createdSQL> create directory
destdir
as ‘/u01/app/oracle/product/11.2.0/db_1/oradata’;Directory created本方法我们将上节中的ASM文件传输到File System中。SQL> select file_name, file_id, online_status from dba_data_files;FILE_NAME FILE_ID ONLINE_STATUS————————————————– ———- ————-(篇幅原因,有省略……)+DATA/ora11g/datafile/example.265.825944513 5 ONLINE+DATA/ora11g/datafile/testtbl.dbf 6 RECOVER –已经被offline状态的数据文件6 rows selectedSQL> begin 2 dbms_file_transfer.copy_file(source_directory_object => ‘souredir’, 3 source_file_name => ‘testtbl.dbf’, 4 destination_directory_object => ‘destdir’,免费主机域名 5 destination_file_name => ‘testtbl.dbf’); 6 end; 7 /PL/SQL procedure successfully completed文件系统中查看到文件testtbl.dbf。[oracle@SimpleLinux oradata]$ ls -ltotal 102512-rw-r—– 1 oracle asmadmin 104865792 Dec 27 14:34 testtbl.dbfSQL> alter database
rename file ‘+DATA/ora11g/datafile/testtbl.dbf’ to ‘/u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf’;Database alteredSQL> alter database recover datafile 6;Database altered对数据文件online操作。SQL> alter database datafile ‘/u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf’ online;Database alteredSQL> select file_name, file_id, online_status from dba_data_files;FILE_NAME FILE_ID ONLINE_STATUS————————————————– ———- ————-(篇幅原因,有省略……)+DATA/ora11g/datafile/example.265.825944513 5 ONLINE/u01/app/oracle/product/11.2.0/db_1/oradata/testtb 6 ONLINEl.dbf 6 rows selected试验成功。4、RMAN方法在没有cp命令之前,RMAN是一种比较常用的方法。我们先将文件offline。SQL> alter database datafile ‘/u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf’ offline;Database altered启动RMAN copy过程。RMAN> connect target /connected to target database: ORA11G (DBID=4222144573)using target database control file instead of recovery catalogRMAN> copy datafile ‘/u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf’ to ‘+RECO’2> ;Starting backup at 27-DEC-13allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=41 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00006 name=/u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbfoutput file name=+RECO/ora11g/datafile/test.282.835281667
tag=TAG20131227T144106 RECID=2 STAMP=835281676channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15Finished backup at 27-DEC-13更改文件名,进行数据文件online操作。SQL> alter database rename file ‘/u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf’ to ‘+RECO/ora11g/datafile/test.282.835281667’;Database alteredSQL> alter database recover datafile 6;Database alteredSQL> alter database datafile 6 online;Database altered试验成功。以上是“ASM与File System数据文件转移方法有哪些”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注云技术行业资讯频道!
这篇文章主要为大家展示了“Mysql索引怎么用”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让小编带领大家一起研究并学习一下“Mysql索引怎么用”这篇文章吧。select …. from table where key_part1=’xxx’…