《Oracle跨平台迁移-ORACLE OCP认证考试 (2).docx》由会员分享,可在线阅读,更多相关《Oracle跨平台迁移-ORACLE OCP认证考试 (2).docx(21页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、一、迁移windows数据库至linux上1、windows上的控制文件、日志文件与linux上的不一样,所以无需拷贝2、数据文件头不一样,需要修改,转换3、查看大小码 select * from v$transportable_platform4、迁移数据文件,大码可以互转,小码可以互转,大小码之间不能互转5、迁移表空间,大小码之间也可以迁移,但是字符集不一样不能迁移旦西图民|扬后。融演查询构建器select * from v$pdbs;select * from v?transportable_platform查询结果x才昌跑段SQL提取的所有行:21,用时0.006秒|z| PIATFO
2、EMJD |z| PIATFOEM NAME山 ENDIAFOKMAT |z| CON.ID11 Solaristm 0E (32-bit)Big022 Solaristm 0E (64-bit)Big037 Microsoft Windows IA (32-bit)Little0410 Linux IA (32-bit)Little056 AIX-Based Systems (64-bit)Big063 HP-UX (64-bit)Big075 HP Tru64 UNIXLittle084HP-UX IA (64-bit)Big0911 Linux IA (64-bit)Little0101
3、5 HP Open VMSLittle0118 Microsoft Windows IA (64-bit)Little0129 IBM zSeries Based LinuxBig01313 Linux x86 64-bitLittle01416 Apple Mac OSBig01512 Microsoft Windows x86 64-bitLittle01617 Solaris Operating System (x86)Little01718 IBM Power Based LinuxBig01819 HP IA Open VMSLittle01920 Solaris Operating
4、 System (x86-64)Little02021 Apple Mac OS (X86-64)Little02122 Linux OS (S64)Big06、经过查看,windows迁移到linux是可以进行的7、需要正常关库Host specifies the host name or IP address of the database server computer.Port specifies the listening port on the database server. specifies the service name of the database you want
5、to access.Example: Connect to database using Easy Connect and the Service name is ORCL.sqlpl us myusername/mypasswordHost/ORCLThe /noLOG option starts SQL%Pl us without connecting to a database.The edition specifies the value for session Edition. is: |. .Runs the specified sqlpIus script from a web
6、server (URL) or the local file system (filename.ext) with specified parameters that will be assigned to substitution variables in the script.when sqlpIus starts, and after connect commands, the site profile (e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile (e. g. login, sql in the wo
7、rking di rectory) are run. The files may contain SQL肯Pl us commands.Refer to the SQLPlus users Guide and Reference for more information. oracledb dbs$ sqlplus / as sysdbacopyright (c) 1982, 2019, oracle. All rights reserved, connected to an idle instance.SQL startup; oracle instance started.Total sy
8、stem GlobalArea2566913256Fixed Size8899816Variable Size553648128Database Buffers1996488704Redo Buffers7876608ORA-00205 : 7777,77A 77777bbbbb 7 7ytytytytyts s s s seeeOJeSQL现在需要对WINDOWS上拷过来的数据文件头进行处理,使用rman target /convert datafile 7stage/win/DATA_D-ORCL19_l-376305118_TS-SYSTEM_FNO-l_01015T7l from pl
9、atform Microsoft Windows IA (64-bit) format7u01/a p p/o ra cl e/o ra data/orcl 2/syste mOl.dbf;目的地转换的命令RMAN convert datafile * /stn/DATA_0-ORCL19.I -3 763 0 5118_ts-$ystem_fno-1-0101 5t7i frow platform Microsoft windows xa (64-bit) format * /u01/app/oracl/oradata/orcl2/5y5te01. dbf ,-:Starting conve
10、rsion at target ax H-mav-23using target database control file Instead of recovery catalogallocated channel: oa_discJ.channel ooauXSkU.: SID-24 5 device type-DXSKrman-oo5/I:WWM-005W: ERROR HESSAGE STACK FOLLOWS RMAN-OOSTl:RMAN-03002: failure of conversion at target cotmiand at 05/11/2023 18:04:02wan-
11、06576: plaxforo Linux IA (64-bit)* (11) found 1n header of datafile /$xaQe/w1 n/DATA_0-OCL19J-J76305118-TS-SYSTEM.FNO-1.01015T7I does nor match specified plaxforw name Microsoft windows M (64-b1(8)RMAN这里报错了,说明已经转换过了,那再转换成linux格式,注意,这几条语句不能直 接粘贴,从edit内粘贴,格式不一样convert datafile 7stage/win/DATA_D-ORCL19
12、_l-376305118_TS-SYSTEM_FNO- l_01015T7l from platform Linux IA (64-bit)l format /uOl/app/oracle/oradata/orc/systemOl.dbf;convert datafile 7stage/win/DATA_D-ORCL19_l-376305118_TS-SYSAUX_FNO- 3_02015T8E from platform Linux IA (64-bit), format 7u01/app/oracle/oradata/orcl2/sysaux01.dbf;convert datafile
13、7stage/win/DATA D-ORCL19 1-376305118 TS-UNDOTBS1 FNO- 4_03015T8T from platform Linux IA (64-bit), format 7u01/app/oracle/oradata/orcl2/undotbsl.dbf;convert datafile 7stage/win/DATA_D-ORCL19_l-376305118_TS-USERS_FNO- 7_04015T91 from platform Linux IA (64-bit), format 7u01/app/oracle/oradata/orcl2/use
14、rs01.dbf;RMANRMAN-OO571: RMAN-005W: - ERROR MESSAGE STACK FOLLOWS PXAN-00571:RRAN-OOSSS: error encountered Mile parsing input commands pman-01006: error sianaled during parseRman-O2CX)3: unrecognized character:RMANWANwan cocwecT daxaflle V$tAge/v1n/DATKj)-oT8Sl.FNO-4-03015T8T from platform Lnux xa (
15、M-bH)* format */uOl/app/oracl/or1a/or12/undoxb$01,dbf*:Starting conversion zrgtc 11-may-23 using channelchannel OA_DISKJL: starting datafile conversion Input file name./stage/wln/DATAwD-OftCL19U-376305U8.TS-UHOOTBSl_FNO-4-03015T8T converted darafi1e/uOl/app/oracle/oradata/orcl2/und aaaaWAN-00571: -.
16、RMAN-OO5e9: ERROR MCSSGC STACK FOLLOWS PM AN-005 71:.rman-00558: error erKountered while parsing Input comand5 pman-01006: tcror $1gniled ngRMAN-02003: unrecognized character:hanRMANPMAN /uOl/app/oracl/oradat A/orclau01. dbf ,;wan-00571:.-.WAN-00569: ERROR MCSSAGC STACK FOLLOWS rman-00571pman-00558:
17、 error encountered 61 Ie parsing Input conwundsRMAN-01009: syntax error; found single-quoted-string: expecting one of: advise, allocate, alter, analyze, associate statistics, audit, backup, begn. call, catalog, change, coswent. conm5t, configu re. connect. convert. copy, create, create catalog, crea
18、te global, create or replace global. create or replace script, create script, crosscheck, decUre. delete, delete from, describe, describe catalog, disassociate statistics, drop, drop catalog, drop database, duplicate, exit, explain plan, flashback. flashback table, grant, gram catalog, grant registe
19、r, host. Import, insert, list, lock, irerge. moun t, noaudH, open, print. purge, quit. recover. rgHttr, release Rman-01007: at line 1 column 1 file: standard inputRman coo/ert datafile ,/stage/w1n/DATA_D-oCL19.X-37630S118-TS-uNCOTBSl-FNO-4-0301ST8T from platform Linux ia (64-bit) format ,/u01/app/or
20、acle/oracUta/orcl2/undotbsl.dbf;Starting conversion 八 t9K at 11-may-23 using channel ora-dis- channel o*AJXSKU.: starting datafile conversion Input file name-/$tage/w1n/DATA-D-OK:L19j-376305H8_TS-UH0OTBSl_FNO-4_03015T8T converted daxif51-/uOl/4pp/oracl/oractetVorISKj.: datafile conversion cowplete,
21、elapsed time: 00:00:01 Finished conversion at target at n-may-23RMAN convert datafile /stage/wnn/DATK_o-RCL19.I-37650S118.TS-uSERS_FNO-7.0401ST91 from platforn Linux IA (64-bit) format */u01/app/oracle/oradata/orcl2/user$01,dbf*;Smarting conversion at target at 11-may-23 using channel OfUCDISK-l cha
22、nnel OftA_piSK_A: starting datafile conversion Input file nie-/srage/w1n/0ATCD-*CL19U-376305118.TS-u$ER$NO-7-04O15T91 converted dataf11e-/u01/app/ora1Activate xlie *eb console *5th: systemctl enable now cockpit.socketLast login: Thu May 11 17:25:18 2023Irootftdo * su - oracle orac106db 5 cd /uOl/pp/
23、 grid/oracle/ ordinventory/oracleOdb $ cd /uOl/aporacle/ admn/audit/cfqtoollogs/checkpoints/forac i&Odbcd /u01/app/oracl e/product/lEoracloOdb$ cd /u01/app/oracl/orad*ta/crcl2/oracleQdb orul25 Is sysauxOl.dbf svstnOl. dbf undotbsOl.dbf orAcleOdb orclilS r -rf undotbsOl.dbf oracleOdb orcl2:S oraclpOd
24、b orcl2.$ oraclcOdb orcl2J$ Is sysauxOl.dbf systenOl.cfcf undotbsl.dbf oracleMb orcD$ 1$ sysAUxOL.dbf syslenOl. dof undotbsl.dbf usersOl.dbf oracleCdb orclijS pwd /u01/app/oracl/oradata/orcl2 oracleQdb orcl2$ersdata/fast_rcovory_iira/ oradat4/product/接下来需要做控制文件,退出rman,使用脚本CREATE CONTROLFILE REUSE SE
25、T DATABASE OCC119 RESETLOGS ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEgroup 1 /uOl/app/oracle/oradata/orcl2/redoOl GROUP 2 /uOl/app/oracle/oradata/orcl2/redo02 GROUP 3 /uOl/app/oracle/oradata/orcl2/redo03DATAFILE/uOl/app/oracle/oradata/orcl2/sysaux0
26、1.dbf, /u01/app/oracle/oradata/orcl2/systemOl.dbf, /uOl/app/oracle/oradata/orcl2/undotbs01.dbf /uOl/app/oracle/oradata/orcl2/users01.dbfCHARACTER SET ZHS16GBKSIZE 5OOMSIZE 5OOMSIZE 5OOMBLOCKSIZEBLOCKSIZEBLOCKSIZE2 2 21115 5 51,46Allcontrol.sql dos 17L, 653C现在只能先叫orcll9,在nomount状态下执行脚本CREATE CONTROLF
27、ILE REUSE SET DATABASE orcll9 RESETLOGSARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1BLOCKSIZE 512,GROUP 2BLOCKSIZE 512,GROUP 3BLOCKSIZE 5127u01/app/oracle/oradata/orcl2/redo01.logSIZE500M7u01/app/oracle/oradata/orcl2/redo02.logSIZE500M7u01/app/o
28、racle/oradata/orcl2/redo03.logSIZE500MDATAFILE7u01/app/oracle/oradata/orcl2/sysaux01.dbf, /uOl/app/oracle/oradata/orc/systemOl.dbf, 7u01/app/oracle/oradata/orcl2/undotbsl.dbf, 7u01/app/oracle/oradata/orcl2/users01.dbfCHARACTER SET ZHS16GBK redo文件生产环境至少5个,每个2Gcrs css ctx cv data dbjava dbsoracledbdbe
29、nv.orahas hs install instant cl lent inventory javavmnome_2$ cd dbmd mgw network nls odbc ol ap OPatchoui ow perl Plsql precomp QOpatch Rroot. sh. old. 3 runinstaller schagent.conf sdkslaxsqldeveloperusm irtiwwg xdkdbjava/ dbs/oracledb dbhome_2$ cd dbs/oracledb dbs$ Ishc_orcl2.dat init.ora 1nitorcl2
30、.ora lkORCL19 ;oracledb dbs$ vi 1nitorcl2.ora ;oracledb dbs$ Isorapworcl2 spfileorcl2.orahc_orcl2.dat init.ora ;oracledb dbs$ cd ;oracledb $ Is control.sql Desktopinitorcl2.ora lkORCL19orapworcl2 spfileorcl2.oraDownloads PicturesMusic Publicdb.rspDocunents;oracledbvi control.sql;oracledbsqlplus / as
31、 sysdbaTemplates videosThu May 11 18:20:04 2023Copyright (c) 1982, 2019, oracle. All rights reserved.777 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionSQL control.sqlSQL此时数据库已经处于mount状态,但是无法openoracledb dbs$ Ishc_orcl2.dat init.ora 1nitorcl2.ora lkORCL19 orapworcl2 spfileorcl
32、2.ora ;oracledb dbs$ vi 1nitorcl2.ora oracledb dbs$ Ishc_orcl2.dat init.ora 1nitorcl2.ora lkORCL19 orapworcl2 spfileorcl2.ora ;oracledb dbs$ cd;oracledb $ Iscontrol.sql DesktopDownloads Pictures Templatesdb.rspDocunents MusicPublicVideos;oracledb vi control.sql;oracledb $ sqlplus / as sysdbaSQLPlus:
33、 Release 19.0.0.0.0 - Production on Thu May 11 18:20:04 2023copyright (c) 1982, 2019, oracle. All rights reserved.SQL control.sqlSQL alter session set nls_language=engl1sh; session altered.SQL select open_mode from v$database;OPEN_MODEMOUNTEDSQLcontrol.sqlDesktopDowTToadsPicturesTempTates db.rspDocu
34、nents Music Public videosoracledb $ vi control.sql oracledb $ sqlplus / as sysdbacopyright (c) 1982, 2019, oracle. All rights reserved. SQL control.sqlsql alter session set nls_language=engl1sh;session altered.SQL select open_mode from v$database;OPEN_MODEMOUNTEDSQL alter database open; alter databa
35、se open * error at line 1: ora-01589: must use resetlogs or noresetlogs option for database openSQL处理 resetlogssql control.sqlSQL alter session set nls_language=engl1sh;Session altered.ect open_mode trom v$databaseOPEN_MODEMOUNTEDSQL alter database open; alter database open *ERROR at line 1:ora-0158
36、9: must use resetlogs or noresetlogs option for database openSQL alter database open resetlogs;Database altered.SQL 2select Acopen_modefrom4人HSQLselectopen_modefrom$acSQLselectopen_modefromv$database;OPEN_MODEREAD WRITESQL这里看到已经处于读写状态了,但是数据库名仍然为。919,与我们希望的。92不一致二、修改数据库名Session altered.SQL seiect ope
37、n_mode from v$database;OPEN_MODEMOUNTEDSQL alter database open;alter database openerror at line 1:ORA-01589: must use RESETLOGS or NORESETLOGS option for database openSQL alter database open resetlogs;Database altered.SQL 2select ACopen_modefrom4人HSQLselectopen_modefrom$acSQLselectopen_modefromv$dat
38、abase;OPEN_MODEREAD WRITESQL SELECT DBID,NAME FROM V$DATABASE;DBID NAME376305118 ORCL19SQL这里的数据库名字为0RCL19可以使用脚本进行更改,参考脚本【新提醒】Bos Oracle Station1、先关闭数据库2、修改控制文件和参数文件shutdown immediate;control.sql DesktopDown Ioads Pictures Templatesdb.rspDocunents MusicPublicvideos;oracledb $ vi control.sql;oracledb;
39、oracledb cat control. sqlCREATE CONTROLFILE REUSE SET DATABASE mydb RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE2 2 21115 5 5GROUP 1 /uOl/app/oracle/oradata/orcl2/redoOl.1og SIZE 5OOM BLOCKSIZEGROUP 2 /uOl/app/oracle/oradata/orcl2/redo0
40、2.log SIZE 5OOM BLOCKSIZEgroup 3 /uOl/app/oracle/oradata/orcl2/redo03.log size 5OOM blocksize DATAFILE/u01/app/oracle/oradata/orcl 2/sysauxOl.dbf,/uOl/app/oracle/oradata/orcl2/systemOl.dbf,/u01/app/oracle/oradata/orcl2/undotbsl.dbf,/uOl/app/oracle/oradata/orcl2/users01.dbfCHARACTER SET ZHS16GBKoracl
41、edb $control_files= ,/u01/app/oracle/oradata/orcl2/control01.ctl,/uOl/app/oracle/oradata/orcl2/control02.ctl,/uOl/app/oracle/oradata/orclZ/controlOS. ctlaudit_fi1e_dest= /uOl/app/oracle/admin/orcl2/adumpdb_name= mydb# Please review the values of the following parameters:# oracle_base= c:usersAppleDo
42、wnloadsremote_login_passwordf11e= * exclusivedispatchers= (protocol=tcp) (service=ofc119xdb)h# The values of the following parameters are from source database:processes=300nls_language=simplifiedChinesenls_territory=chinasga_target=2566914048db_block_size=8192compatible=19.0.0undo_tablespace=undotbs
43、I4、5、生成spfile文件6、启动数据库到nomount状态下7、 alter session set nls_language=English8、执行脚本control.sqlListener Log Fi 1 e/u01/app/oracle/diag/tnslsnr/db/11stener/alert/1og.xmlListening Endpoints summary.(DESCRiPTiON=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0, 71)(PORT=1522)(DESCRIPTiON=(ADDRESS=(PROTOCOL=tcps)(HOST=)(PORT=5500)Xsecurity=(my _wal1et_directory=/u01/app/oracle/product/19.3.0/dbhomeJ./adm1n/cdb2/xdb_wal1et)j(Pre sentation=HTTP)(session=RAW)(description=(address=(protocolscps)(HOST=)(port=5501)(security=(my _wal1et_directory=/u01/app/oracle/a
限制150内