Oracle数据库的转移与升级

以下就是转移的经过…

系统环境:

OS:Windows 2000 Advanced Server+SP4

网管u家u.bitsCN.com

Oracle 9i standard Edition Version: 9.0.1.1.1 网管u家u.bitscn@com

吴江系统环境: 网管网www_bitscn_com

  HP ML570 CPU:Intel Xeon MP CPU 3.0G RAM:2GB 网管u家u.bitsCN.com

  OS:Windows 2000 Advanced Server+SP4

  Oracle 9i Standard Editon Version: 9.2.0.1.0. 网管bitscn_com

步骤:

网管论坛bbs_bitsCN_com

1.        安装Oracle 9i,选择只安装Software,不创建数据库,安装过程略. 网管论坛bbs_bitsCN_com

2.        创建相关目录:d:oracleadminwjsfmsbdump 中国网管联盟bitsCN.com

d:oracleadminwjsfmscdump 中国网管论坛bbs.bitsCN.com

                           d:oracleadminwjsfmsudump 网管u家u.bitscn@com

d:oracleadminwjsfmscreate 网管下载dl.bitscn.com

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 网管网www.bitscn.com

4.        COPY控制文件G:ControlFileWJSFMS,H:ControlFileWJSFMS,I:ControlFileWJSFMS 网管u家u.bitscn@com

5.        COPY Redo文件到G:RedoLogWJSFMS,H: RedoLog WJSFMS,I: RedoLog WJSFMS

6.        新建实例,此SID必须跟原来的SID一样… 网管bitscn_com

C:Oradim –NEW –SID WJSFMS –STARTMODE m 网管bitscn_com

7.        创建密码文件

C:orapwd file=d:oracleora92databasepwdwjsfms.ora password=password entries=5

8.        修改初始参数文件INITwjsfms.ora,如没有参数文件,则可用oracle自带的参数文件进行修改,红色部分为修改部分:

……. 网管联盟bitsCN@com

control_files=(\"G:ControlFileWJSFMSCONTROL01.CTL\", \"H:ControlFileWJSFMSCONTROL02.CTL\", \"I:ControlFileWJSFMSCONTROL03.CTL\") 网管联盟bitsCN@com

…………………….. 网管网www.bitscn.com

background_dump_dest=d:oracleadminWJSFMSbdump 网管bitscn_com

core_dump_dest=d:oracleadminWJSFMScdump 网管联盟bitsCN_com

timed_statistics=TRUE 网管论坛bbs_bitsCN_com

user_dump_dest=d:oracleadminWJSFMSudump 网管u家u.bitsCN.com

……………………….

log_archive_dest=f:oraclewjsfmsarchivelog

网管网www.bitscn.com

 

网管下载dl.bitscn.com

如果是根据oracle自带的参数文件进行修改的,则还需修改db_name,instance_name 网管网www.bitscn.com

9.        激活oracle 网管联盟bitsCN_com

c:sqlplus /nolog

网管下载dl.bitscn.com

sql>conn / as sysdba 网管u家u.bitscn@com

sql>startup pfile=d:oracleora92databaseinitwjsfms.ora

中国网管论坛bbs.bitsCN.com

此时提示如下信息(由于升级时没有将相关信息spool出来,故只能将alert文件中的信息show出来作为参考):

ORA-00218: block size 4096 of controlfile does not match DB_BLOCK_SIZE (8192)

ORA-00202: controlfile: 'G:ControlFileWJSFMSCONTROL01.CTL' 网管网www.bitscn.com

出现此错误的原因是原来的database的db_block_size跟现在的初始化参数设的不一样,将初始化参数中的db_block_size=8192改成4096即可

sql>shutdown immediate

sql> startup pfile=d:oracleora92databaseinitwjsfms.ora

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file 网管网www_bitscn_com

ORA-01110: data file 1: 'D:ORACLEORADATAWJSFMSSYSTEM01.DBF'

ORA-27041: unable to open file

网管网www.bitscn.com

Sql> select a.name from v$datafile a,v$recover_file b where a.file#=b.file#; 网管网www.bitscn.com

NAME

-------------------------------------------------------------------------------- 网管论坛bbs_bitsCN_com

D:ORACLEORADATAWJSFMSSYSTEM01.DBF

中国网管联盟bitsCN.com

D:ORACLEORADATAWJSFMSUNDOTBS01.DBF                                     

中国网管联盟bitsCN.com

D:ORACLEORADATAWJSFMSCWMLITE01.DBF                                         网管bitscn_com

D:ORACLEORADATAWJSFMSDRSYS01.DBF                                             网管bitscn_com

D:ORACLEORADATAWJSFMSEXAMPLE01.DBF                                          网管u家u.bitscn@com

D:ORACLEORADATAWJSFMSINDX01.DBF                                            

中国网管联盟bitsCN.com

D:ORACLEORADATAWJSFMSTOOLS01.DBF                                            网管下载dl.bitscn.com

D:ORACLEORADATAWJSFMSUSERS01.DBF                                             网管网www_bitscn_com

D:ORACLEORADATAWJSFMSCSFIS01.ORA                                             网管联盟bitsCN_com

D:ORACLEORADATAWJSFMSRSFIS01.ORA                                           

D:ORACLEORADATAWJSFMSRSFIS02.ORA                                           网管联盟bitsCN@com

D:ORACLEORADATAWJSFMSHSFIS01.ORA                                           

D:ORACLEORADATAWJSFMSCINDX01.ORA                                           

网管bitscn_com

D:ORACLEORADATAWJSFMSRINDX01.ORA                                           

D:ORACLEORADATAWJSFMSRINDX02.ORA                                         

D:ORACLEORADATAWJSFMSHINDX01.ORA                                           网管网www_bitscn_com

D:ORACLEORADATAWJSFMSUNDOTBS2.ORA 网管bitscn_com

执行alter database  rename  file 网管联盟bitsCN_com

Sql> alter database  rename  file  'D:ORACLEORADATAWJSFMSSYSTEM01.DBF' to 'E:OradataWJSFMSSYSTEM01.DBF';                                       网管联盟bitsCN@com

alter database  rename  file  'D:ORACLEORADATAWJSFMSUNDOTBS01.DBF' to 'E:OradataWJSFMSUNDOTBS01.DBF';                                         

alter database  rename  file  'D:ORACLEORADATAWJSFMSCWMLITE01.DBF' to 'E:OradataWJSFMSCWMLITE01.DBF';                                          网管bitscn_com

alter database  rename  file  'D:ORACLEORADATAWJSFMSDRSYS01.DBF' to 'E:OradataWJSFMSDRSYS01.DBF';                                            中国网管论坛bbs.bitsCN.com

alter database  rename  file  'D:ORACLEORADATAWJSFMSEXAMPLE01.DBF' to 'E:OradataWJSFMSEXAMPLE01.DBF';                                         

alter database  rename  file  'D:ORACLEORADATAWJSFMSINDX01.DBF' to 'E:OradataWJSFMSINDX01.DBF';                                             网管网www_bitscn_com

alter database  rename  file  'D:ORACLEORADATAWJSFMSTOOLS01.DBF' to 'E:OradataWJSFMSTOOLS01.DBF';                                           

网管联盟bitsCN_com

alter database  rename  file  'D:ORACLEORADATAWJSFMSUSERS01.DBF' to 'E:OradataWJSFMSUSERS01.DBF';                                            网管联盟bitsCN@com

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';                                           

网管网www.bitscn.com

alter database  rename  file  'D:ORACLEORADATAWJSFMSHSFIS01.ORA' to 'E:OradataWJSFMSHSFIS01.ORA';                                            网管联盟bitsCN_com

alter database  rename  file  'D:ORACLEORADATAWJSFMSCINDX01.ORA' to 'E:OradataWJSFMSCINDX01.ORA';                                           

网管bitscn_com

alter database  rename  file  'D:ORACLEORADATAWJSFMSRINDX01.ORA' to 'E:OradataWJSFMSRINDX01.ORA';                                           

网管u家u.bitscn@com

alter database  rename  file  'D:ORACLEORADATAWJSFMSRINDX02.ORA' to 'E:OradataWJSFMSRINDX02.ORA';                                           

alter database  rename  file  'D:ORACLEORADATAWJSFMSHINDX01.ORA' to 'E:OradataWJSFMSHINDX01.ORA';                                            网管网www.bitscn.com

alter database  rename  file  'D:ORACLEORADATAWJSFMSUNDOTBS2.ORA' to 'E:OradataWJSFMSUNDOTBS2.ORA'; 中国网管论坛bbs.bitsCN.com

  网管u家u.bitscn@com

sql>alter database open;

alter database open 网管网www_bitscn_com

* 网管联盟bitsCN@com

ERROR at line 1: 网管u家u.bitscn@com

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'E:ORACLEORADATAZISCO12REDO01.LOG' 中国网管论坛bbs.bitsCN.com

 

网管网www_bitscn_com

再次执行alter database  rename  file

网管bitscn_com

sql> alter database  rename  file  'D:ORACLEORADATAWJSFMSREDO01.LOG' to 'G:RedoLogWJSFMSREDO01.LOG';   

网管论坛bbs_bitsCN_com

alter database  rename  file  'D:ORACLEORADATAWJSFMSREDO02.LOG' to 'H:RedoLogWJSFMSREDO02.LOG';

alter database  rename  file  'D:ORACLEORADATAWJSFMSREDO03.LOG' to 'I:RedoLogWJSFMSREDO03.LOG';                                       

网管论坛bbs_bitsCN_com

  中国网管联盟bitsCN.com

此时执行alter database open 网管网www_bitscn_com

sql>alter database open; 网管下载dl.bitscn.com

此时提示: 网管网www_bitscn_com

Errors in file d:oracleadminwjsfmsudumpwjsfms_ora_2516.trc:

ORA-10827: database must be opened with MIGRATE option 网管网www.bitscn.com

然后instance自动shutdown

中国网管联盟bitsCN.com

出现这个原因是由于数据库版本不一致造成的,原来版本是9.0.1.1.1,现在是9.2.0.1.0. 网管论坛bbs_bitsCN_com

10.     数据库升级: 网管网www.bitscn.com

查看oracle online document,决定手工升级数据库 网管网www.bitscn.com

sql>startup migrate pfile=d:oracleora92databaseinitwjsfms.ora

SQL> SPOOL upgrade.log 中国网管论坛bbs.bitsCN.com

  网管网www.bitscn.com

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. 网管网www_bitscn_com

  网管联盟bitsCN_com

To run a script, enter the following:

 

网管网www_bitscn_com

SQL> @uold_release.sql

网管u家u.bitscn@com

Table 3-2  Upgrade Scripts

Old Release      Run Script 网管bitscn_com

7.3.4           u0703040.sql

网管u家u.bitsCN.com

8.0.6         u0800060.sql

8.1.7         u0801070.sql 网管u家u.bitsCN.com

9.0.1         u0900010.sql 网管联盟bitsCN_com

 See Also:

网管bitscn_com

\"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 中国网管论坛bbs.bitsCN.com

 

网管联盟bitsCN_com

Make sure you follow these guidelines when you run the script: 网管bitscn_com

  网管论坛bbs_bitsCN_com

You must use the version of the script supplied with the new release 9.2 installation.

网管联盟bitsCN_com

You must run the script in the new release 9.2 environment.

网管u家u.bitsCN.com

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. 网管下载dl.bitscn.com

The script you run creates and alters certain dictionary tables. It also runs the catalog.sql and catproc.sql scripts that come with the new 9.2 release, which create the system catalog views and all the necessary packages for using PL/SQL. 中国网管论坛bbs.bitsCN.com

  中国网管联盟bitsCN.com

The following components are upgraded by running the uold_release.sql script: 网管u家u.bitscn@com

 

中国网管联盟bitsCN.com

Oracle9i Catalog Views 中国网管论坛bbs.bitsCN.com

Oracle9i Packages and Types 网管网www.bitscn.com

  网管u家u.bitsCN.com

Display the contents of the component registry to determine which components need to be upgraded: 网管论坛bbs_bitsCN_com

SQL> SELECT comp_name, version, status

中国网管论坛bbs.bitsCN.com

         FROM dba_registry;

  网管联盟bitsCN_com

The following is an example of the output you will see when issuing this query:

  网管网www_bitscn_com

COMP_NAME                      VERSION          STATUS 网管u家u.bitsCN.com

------------------------------ ---------------- -----------

Oracle9i Catalog Views         9.2.0.1.0        VALID 网管u家u.bitscn@com

Oracle9i Packages and Types    9.2.0.1.0        VALID

JServer JAVA Virtual Machine   9.0.1            LOADED

Java Packages                  9.0.1            LOADED

Oracle XDK for Java            9.0.1            LOADED 中国网管论坛bbs.bitsCN.com

Oracle Text                    9.0.1            LOADED 网管联盟bitsCN_com

Oracle Workspace Manager       9.0.1.0.0        LOADED

网管论坛bbs_bitsCN_com

Oracle interMedia              9.0.0.0.0        LOADED 网管网www_bitscn_com

Oracle Spatial                 9.0.0.0.0 BETA   LOADED 网管bitscn_com

Ultrasearch                    9.0.1.0.0        LOADED

网管下载dl.bitscn.com

OLAP Catalog                   9.0.1.0.0        LOADED

  网管联盟bitsCN_com

11 rows selected.

网管u家u.bitscn@com

 

网管联盟bitsCN@com

 

网管bitscn_com

Run the cmpdbmig.sql script to upgrade components that can be upgraded while connected with SYSDBA privileges:

SQL> @cmpdbmig.sql

  网管bitscn_com

The following components are upgraded by running the cmpdbmig.sql script: 网管下载dl.bitscn.com

  中国网管联盟bitsCN.com

JServer JAVA Virtual Machine 网管联盟bitsCN@com

 Oracle9i Java Packages

网管u家u.bitscn@com

 Oracle XDK for Java 中国网管论坛bbs.bitsCN.com

 Messaging Gateway

网管联盟bitsCN@com

 Oracle9i Real Application Clusters

 Oracle Workspace Manager

网管u家u.bitscn@com

 Oracle Data Mining

 OLAP Catalog 网管bitscn_com

 OLAP Analytic Workspace

网管联盟bitsCN@com

 Oracle Label Security 网管下载dl.bitscn.com

  网管u家u.bitscn@com

Display the contents of the component registry to determine which components were upgraded:

网管下载dl.bitscn.com

SQL> SELECT comp_name, version, status 网管u家u.bitsCN.com

         FROM dba_registry; 网管网www_bitscn_com

  网管u家u.bitscn@com

The following is an example of the output you will see when issuing this query: 网管网www.bitscn.com

 

COMP_NAME                      VERSION         STATUS 中国网管论坛bbs.bitsCN.com

------------------------------ --------------- ----------- 中国网管论坛bbs.bitsCN.com

Oracle9i Catalog Views         9.2.0.1.0       VALID 网管联盟bitsCN_com

Oracle9i Packages and Types    9.2.0.1.0       VALID 网管下载dl.bitscn.com

JServer JAVA Virtual Machine   9.2.0.1.0       VALID 网管u家u.bitsCN.com

Oracle9i Java Packages         9.2.0.1.0       VALID

Oracle XDK for Java            9.2.0.2.0       UPGRADED

网管联盟bitsCN_com

Oracle Text                    9.0.1           LOADED 网管网www_bitscn_com

Oracle Workspace Manager       9.2.0.1.0       VALID

网管u家u.bitscn@com

Oracle interMedia              9.0.0.0.0       LOADED 网管联盟bitsCN@com

Oracle Spatial                 9.0.0.0.0 BETA  LOADED

Ultrasearch                    9.0.1.0.0       LOADED 网管u家u.bitsCN.com

OLAP Catalog                   9.2.0.1.0       VALID 网管论坛bbs_bitsCN_com

OLAP Analytic Workspace        9.2.0.1.0       LOADED 中国网管联盟bitsCN.com

 

12 rows selected. 网管下载dl.bitscn.com

 

网管u家u.bitscn@com

Turn off the spooling of script results to the log file:

网管联盟bitsCN_com

SQL> SPOOL OFF 网管联盟bitsCN@com

 

网管联盟bitsCN@com

Then, check the spool file and verify that the packages and procedures compiled successfully. 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. 网管u家u.bitscn@com

 

网管联盟bitsCN_com

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 components.

SQL> SHUTDOWN IMMEDIATE

网管下载dl.bitscn.com

  中国网管论坛bbs.bitsCN.com

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.

  网管u家u.bitsCN.com

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. 网管联盟bitsCN_com

 

Upgrade any remaining components that existed in the previous database. See \"Upgrading Specific Components\". 网管网www.bitscn.com

  网管联盟bitsCN@com

The following components require separate upgrade steps: 网管网www.bitscn.com

 

网管bitscn_com

Oracle Text 网管论坛bbs_bitsCN_com

 Oracle Ultra Search 网管联盟bitsCN_com

 Oracle Spatial 网管联盟bitsCN@com

 Oracle interMedia 网管网www_bitscn_com

 Oracle Visual Information Retrieval 网管网www_bitscn_com

 

中国网管论坛bbs.bitsCN.com

Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

网管网www.bitscn.com

SQL> @utlrp.sql

  网管下载dl.bitscn.com

Verify that all expected packages and classes are valid: 网管论坛bbs_bitsCN_com

 

网管bitscn_com

SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID'; 网管网www_bitscn_com

SQL> SELECT destinct object_name FROM dba_objects WHERE status='INVALID';

网管联盟bitsCN_com

Verify that all components are valid and have been upgraded to release 9.2: 网管联盟bitsCN@com

SQL> SELECT comp_name, version, status 中国网管联盟bitsCN.com

         FROM dba_registry; 中国网管联盟bitsCN.com

Your database is now upgraded to the new 9.2 release. Complete the procedures described in Chapter 4, \"After Upgrading a Database\". 中国网管联盟bitsCN.com

11.     创建SPFILE 网管网www_bitscn_com

sql>create spfile from pfile; 网管网www.bitscn.com

12.     查看报警日志文件,发现temp file找不到: 中国网管论坛bbs.bitsCN.com

           Errors in file d:oracleadminwjsfmsbdumpwjsfms_dbw0_2200.trc:

          ORA-01186: file 201 failed verification tests

网管论坛bbs_bitsCN_com

          ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

网管u家u.bitsCN.com

         ORA-01110: data file 201: 'D:ORACLEORADATAWJSFMSTEMP01.DBF'

  网管联盟bitsCN@com

         Wed Nov 10 10:29:22 2004 网管u家u.bitsCN.com

        File 201 not verified due to error ORA-01157 中国网管联盟bitsCN.com

       Wed Nov 10 10:29:22 2004

       Errors in file d:oracleadminwjsfmsbdumpwjsfms_dbw0_2200.trc:

网管网www.bitscn.com

       ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

网管联盟bitsCN_com

      ORA-01110: data file 202: 'D:ORACLEORADATAWJSFMSTEMP2.ORA'

网管u家u.bitscn@com

      ORA-27041: unable to open file

Sql>select a.name,b.file#,b.name from ts$ a,v$tempfile b where a.ts#=b.ts#;

NAME                      FILE#           name                         

------------------------------------------------------------------------------------------------------------

TEMP                         1            D:ORACLEORADATAWJSFMSTEMP01.DBF                                                   

网管u家u.bitscn@com

                                                                               
TEMP2                        2            D: ORACLEORADATAWJSFMSTEMP02.DBF    中国网管论坛bbs.bitsCN.com

SQL>alter database tempfile 1 drop including datafiles;

SQL>alter database tempfile 2 drop including datafiles; 网管网www_bitscn_com

SQL>alter tablespace temp

网管网www_bitscn_com

add tempfile 'e:oradatawjsfmstemp01.dbf' size 100m autoextend on next 1024k maxsize 1024m; 网管联盟bitsCN_com

SQL>alter tablespace temp2 网管论坛bbs_bitsCN_com

add tempfile 'e:oradatawjsfmstemp02.dbf' size 100m autoextend on next 1024k maxsize 1024m; 中国网管联盟bitsCN.com

至此数据库转移完毕

网管论坛bbs_bitsCN_com

 

网管论坛bbs_bitsCN_com