Oracle数据库的转移与升级数据库教程 电脑资料

Oracle数据库的转移与升级数据库教程 电脑资料 oracle|数据|数据库 公司最近要上shop floor系统,需将数据库从东莞转移过来,以下就是转移的经过 东莞系统环境: OS:Windows 2000 Advanced Server+SP4 Oracle 9i standard Edition Version: .1 吴江系统环境: HP ML570 CPU:Intel Xeon MP CPU 3.0G RAM:2GB OS:Windows 2000 Advanced Server+SP4 Oracle 9i Standard Editon Version: .0. 步骤: 1. 安装Oracle 9i,选择只安装Software,不创建数据库,安装过程略. 2. 创建相关目录:d:oracleadminwjsfmsbdump d:oracleadminwjsfmscdump d:oracleadminwjsfmsudump d:oracleadminwjsfmscreate d:oracleadminwjsfmspfile 3. Copy数据文件到E:OradataWJSFMS目录下,临时文件可不COPY,文件清单如下: SYSTEM01.DBF NDOTBS01.DBF CWMLITE01.DBF DRSYS01.DBF EXAMPLE01.DBF INDX01.DBF TOOLS01.DBF USERS01.DBF CSFIS01.ORA SFIS01.ORA RSFIS02.ORA HSFIS01.ORACINDX01.ORA RINDX01.ORA RINDX02.ORA HINDX01.ORA UNDOTBS2.ORA 4. COPY控制文件G:ControlFileWJSFMS,H:ControlFileWJSFMS,I:ControlFileWJSFMS 5. COPY Redo文件到G:RedoLogWJSFMS,H: RedoLog WJSFMS,I: RedoLog WJSFMS 6. 新建实例,此SID必须跟原来的SID一样 C:Oradim NEW SID WJSFMS STARTMODE m 7. 创建密码文件 C:orapwd file=d:oracleora92databasepwdwjsfms.ora password=password entries=5 8. 修改初始参数文件INITwjsfms.ora,如没有参数文件,则可用oracle自带的参数文件进行修改,红色部分为修改部分: . control_files=(G:ControlFileWJSFMSCONTROL01.CTL, H:ControlFileWJSFMSCONTROL02.CTL, I:ControlFileWJSFMSCONTROL03.CTL) . background_dump_dest=d:oracleadminWJSFMSbdump core_dump_dest=d:oracleadminWJSFMScdump timed_statistics=TRUE user_dump_dest=d:oracleadminWJSFMSudump . log_archive_dest=f:oraclewjsfmsarchivelog 如果是根据oracle自带的参数文件进行修改的,则还需修改db_name,instance_name 9. 激活oracle c: qlplus /nolog sqlconn / as sysdba sqlstartup pfile=d:oracleora92databaseinitwjsfms.ora ORA-00218: block size 4096 of controlfile does not match DB_BLOCK_SIZE (8192) ORA-00202: controlfile: G:ControlFileWJSFMSCONTROL01.CTL 出现此错误的原因是原来的database的db_block_size跟现在的初始化参数设的不一样,将初始化参数中的db_block_size=8192改成4096即可 sqlshutdown immediate sql startup pfile=d:oracleora92databaseinitwjsfms.ora ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: D:ORACLEORADATAWJSFMS YSTEM01.DBF ORA-27041: unable to open file Sql select from v$datafile a,v$recover_file b where a.file#=b.file#; NAME - D:ORACLEORADATAWJSFMS YSTEM01.DBF D:ORACLEORADATAWJSFMSUNDOTBS01.DBF D:ORACLEORADATAWJSFMSCWMLITE01.DBF D:ORACLEORADATAWJSFMSDRSYS01.DBF D:ORACLEORADATAWJSFMSEXAMPLE01.DBF D:ORACLEORADATAWJSFMSINDX01.DBF D:ORACLEORADATAWJSFMSTOOLS01.DBF D:ORACLEORADATAWJSFMSUSERS01.DBF D:ORACLEORADATAWJSFMSCSFIS01.ORA D:ORACLEORADATAWJSFMSRSFIS01.ORA D:ORACLEORADATAWJSFMSRSFIS02.ORA D:ORACLEORADATAWJSFMSHSFIS01.ORA D:ORACLEORADATAWJSFMSCINDX01.ORA D:ORACLEORADATAWJSFMSRINDX01.ORA D:ORACLEORADATAWJSFMSRINDX02.ORA D:ORACLEORADATAWJSFMSHINDX01.ORA D:ORACLEORADATAWJSFMSUNDOTBS2.ORA 执行alter database rename file Sql alter database rename file D:ORACLEORADATAWJSFMS YSTEM01.DBF to E:OradataWJSFMS YSTEM01.DBF; alter database rename file D:ORACLEORADATAWJSFMSUNDOTBS01.DBF to E:OradataWJSFMSUNDOTBS01.DBF; alter database rename file D:ORACLEORADATAWJSFMSCWMLITE01.DBF to E:OradataWJSFMSCWMLITE01.DBF; alter database rename file D:ORACLEORADATAWJSFMSDRSYS01.DBF to E:OradataWJSFMSDRSYS01.DBF; alter database rename file D:ORACLEORADATAWJSFMSEXAMPLE01.DBF to E:OradataWJSFMSEXAMPLE01.DBF; alter database rename file D:ORACLEORADATAWJSFMSINDX01.DBF to E:OradataWJSFMSINDX01.DBF; alter database rename file D:ORACLEORADATAWJSFMSTOOLS01.DBF to E:OradataWJSFMSTOOLS01.DBF; alter database rename file D:ORACLEORADATAWJSFMSUSERS01.DBF to E:OradataWJSFMSUSERS01.DBF; alter database rename file D:ORACLEORADATAWJSFMSCSFIS01.ORA to E:OradataWJSFMSCSFIS01.ORA; alter database rename file D:ORACLEORADATAWJSFMSRSFIS01.ORA to E:OradataWJSFMSRSFIS01.ORA; alter database rename file D:ORACLEORADATAWJSFMSRSFIS02.ORA to E:OradataWJSFMSRSFIS02.ORA; alter database rename file D:ORACLEORADATAWJSFMSHSFIS01.ORA to E:OradataWJSFMSHSFIS01.ORA; alter database rename file D:ORACLEORADATAWJSFMSCINDX01.ORA to E:OradataWJSFMSCINDX01.ORA; alter database rename file D:ORACLEORADATAWJSFMSRINDX01.ORA to E:OradataWJSFMSRINDX01.ORA; alter database rename file D:ORACLEORADATAWJSFMSRINDX02.ORA to E:OradataWJSFMSRINDX02.ORA; alter database rename file D:ORACLEORADATAWJSFMSHINDX01.ORA to E:OradataWJSFMSHINDX01.ORA; alter database rename file D:ORACLEORADATAWJSFMSUNDOTBS2.ORA to E:OradataWJSFMSUNDOTBS2.ORA; sqlalter database open; alter database open * ERROR at line 1: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: E:ORACLEORADATAZISCO12REDO01.LOG 再次执行alter database rename file sql alter database rename file D:ORACLEORADATAWJSFMSREDO01.LOG to G:RedoLogWJSFMSREDO01.LOG; alter database rename file D:ORACLEORADATAWJSFMSREDO02.LOG to H:RedoLogWJSFMSREDO02.LOG; alter database rename file D:ORACLEORADATAWJSFMSREDO03.LOG to I:RedoLogWJSFMSREDO03.LOG; 此时执行alter database open sqlalter database open; 此时提示: Errors in file d:oracleadminwjsfmsudumpwjsfms_ora_2516.trc: ORA-10827: database must be opened with MIGRATE option 然后instance自动shutdown 出现这个原因是由于数据库版本不一致造成的,原来版本是.1,现在是.0. 10. 数据库升级: 查看oracle online document,决定手工升级数据库 sqlstartup migrate pfile=d:oracleora92databaseinitwjsfms.ora SQL SPOOL upgrade.log Run uold_release.sql, where old_release refers to the release you had installed prior to upgrading. See Table 3-2 to choose the correct script. Each script. provides a direct upgrade from the release specified in the Old Release column. The Old Release is the release from which you are upgrading. To run a script, enter the following: SQL uold_release.sql Table 3-2 Upgrade Scripts Old Release Run Script 7.3.4 u0703040.sql 8.0.6 u0800060.sql 8.1.7 u0801070.sql 9.0.1 u0900010.sql See Also: Determine Your Upgrade Path to the New Release if the old release you had installed prior to upgrading is not listed in Table 3-2 Make sure you follow these guidelines when you run the script.: You must use the version of the script. supplied with the new release 9.2 installation. You must run the script. in the new release 9.2 environment. You only need to run one script, even if your upgrade spans more than one release. For example, if your old release was 8.1.7, then you only need to run u0801070.sql. The script. you run creates and alters certain dictionary tables. It also runs the catalog.sql and catproc.sql scripts that e with the new 9.2 release, which create the system catalog views and all the necessary packages for using PL/SQL. The following ponents are upgraded by running the uold_release.sql script.: Oracle9i Catalog Views Oracle9i Packages and Types Display the contents of the ponent registry to determine which ponents need to be upgraded: SQL SELECT p_name, version, status FROM dba_registry; The following is an example of the output you will see when issuing this query: P_NAME VERSION STATUS - - - Oracle9i Catalog Views .0 VALID Oracle9i Packages and Types .0 VALID JServer JAVA Virtual Machine 9.0.1 LOADED Java Packages 9.0.1 LOADED Oracle XDK for Java 9.0.1 LOADED Oracle Text 9.0.1 LOADED Oracle Workspace Manager .0 LOADED Oracle interMedia .0 LOADED Oracle Spatial .0 BETA LOADED Ultrasearch .0 LOADED OLAP Catalog .0 LOADED 11 rows selected. Run the cmpdbmig.sql script. to upgrade ponents that can be upgraded while connected with SYSDBA privileges: SQL cmpdbmig.sql The following ponents are upgraded by running the cmpdbmig.sql script.: JServer JAVA Virtual Machine Oracle9i Java Packages Oracle XDK for Java Messaging Gateway Oracle9i Real Application Clusters Oracle Workspace Manager Oracle Data Mining OLAP Catalog OLAP Analytic Workspace Oracle Label Security Display the contents of the ponent registry to determine which ponents were upgraded: SQL SELECT p_name, version, status FROM dba_registry; The following is an example of the output you will see when issuing this query: P_NAME VERSION STATUS - - - Oracle9i Catalog Views .0 VALID Oracle9i Packages and Types .0 VALID JServer JAVA Virtual Machine .0 VALID Oracle9i Java Packages .0 VALID Oracle XDK for Java .0 UPGRADED Oracle Text 9.0.1 LOADED Oracle Workspace Manager .0 VALID Oracle interMedia .0 LOADED Oracle Spatial .0 BETA LOADED Ultrasearch .0 LOADED OLAP Catalog .0 VALID OLAP Analytic Workspace .0 LOADED 12 rows selected. Turn off the spooling of script. results to the log file: SQL SPOOL OFF Then, check the spool file and verify that the packages and procedures piled suessfully. You named the spool file in Step 13; the suggested name was upgrade.log. Correct any problems you find in this file and rerun the appropriate upgrade scripts if necessary. You can rerun any of the scripts described in this chapter as many times as necessary. Shut down and restart the instance to reinitialize the system parameters for normal operation. The restart will also perform. release 9.2 initialization for JServer JAVA Virtual Machine and other ponents. SQL SHUTDOWN IMMEDIATE Executing this clean shutdown flushes all caches, clears buffers, and performs other housekeeping activities. These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle9i database. Also, if you encountered a message listing obsolete initialization parameters when you started the database in Step 11, then remove the obsolete initialization parameters from the initialization parameter file now. Upgrade any remaining ponents that existed in the previous database. See Upgrading Specific Components. The following ponents require separate upgrade steps: Oracle Text Oracle Ultra Search Oracle Spatial Oracle interMedia Oracle Visual Information Retrieval Run utlrp.sql to repile any remaining stored PL/SQL and Java code. SQL utlrp.sql Verify that all expected packages and classes are valid: SQL SELECT count(*) FROM dba_objects WHERE status=INVALID; SQL SELECT destinct object_name FROM dba_objects WHERE status=INVALID; Verify that all ponents are valid and have been upgraded to release 9.2: SQL SELECT p_name, version, status FROM dba_registry; Your database is now upgraded to the new 9.2 release. Complete the procedures described in Chapter 4, After Upgrading a Database. 11. 创建SPFI