数据库

img topbright

Oracle数据库的转移与升级

发表于2004/11/15 8:51:00  2529人阅读

分类: Oracle

公司最近要上shop floor系统,需将数据库从东莞转移过来,以下就是转移的经过

东莞系统环境:

OS:Windows 2000 Advanced Server+SP4

Oracle 9i standard Edition Version: 9.0.1.1.1

吴江系统环境:

  HP ML570 CPU:Intel Xeon MP CPU 3.0G RAM:2GB

  OS:Windows 2000 Advanced Server+SP4

  Oracle 9i Standard Editon Version: 9.2.0.1.0.

步骤:

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

2.        创建相关目录:d:/oracle/admin/wjsfms/bdump

d:/oracle/admin/wjsfms/cdump

                           d:/oracle/admin/wjsfms/udump

d:/oracle/admin/wjsfms/create

d:/oracle/admin/wjsfms/pfile

3.        Copy数据文件到E:/Oradata/WJSFMS目录下,临时文件可不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:/ControlFile/WJSFMS,H:/ControlFile/WJSFMS,I:/ControlFile/WJSFMS

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

6.        新建实例,SID必须跟原来的SID一样

C:/Oradim –NEW –SID WJSFMS –STARTMODE m

7.        创建密码文件

C:/orapwd file=d:/oracle/ora92/database/pwdwjsfms.ora password=password entries=5

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

…….

control_files=("G:/ControlFile/WJSFMS/CONTROL01.CTL", "H:/ControlFile/WJSFMS/CONTROL02.CTL", "I:/ControlFile/WJSFMS/CONTROL03.CTL")

……………………..

background_dump_dest=d:/oracle/admin/WJSFMS/bdump

core_dump_dest=d:/oracle/admin/WJSFMS/cdump

timed_statistics=TRUE

user_dump_dest=d:/oracle/admin/WJSFMS/udump

……………………….

log_archive_dest=f:/oracle/wjsfms/archivelog

 

如果是根据oracle自带的参数文件进行修改的,则还需修改db_name,instance_name

9.        激活oracle

c:/sqlplus /nolog

sql>conn / as sysdba

sql>startup pfile=d:/oracle/ora92/database/initwjsfms.ora

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

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

ORA-00202: controlfile: 'G:/ControlFile/WJSFMS/CONTROL01.CTL'

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

sql>shutdown immediate

sql> startup pfile=d:/oracle/ora92/database/initwjsfms.ora

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

ORA-01110: data file 1: 'D:/ORACLE/ORADATA/WJSFMS/SYSTEM01.DBF'

ORA-27041: unable to open file

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

NAME

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

D:/ORACLE/ORADATA/WJSFMS/SYSTEM01.DBF

D:/ORACLE/ORADATA/WJSFMS/UNDOTBS01.DBF                                     

D:/ORACLE/ORADATA/WJSFMS/CWMLITE01.DBF                                        

D:/ORACLE/ORADATA/WJSFMS/DRSYS01.DBF                                            

D:/ORACLE/ORADATA/WJSFMS/EXAMPLE01.DBF                                         

D:/ORACLE/ORADATA/WJSFMS/INDX01.DBF                                            

D:/ORACLE/ORADATA/WJSFMS/TOOLS01.DBF                                           

D:/ORACLE/ORADATA/WJSFMS/USERS01.DBF                                            

D:/ORACLE/ORADATA/WJSFMS/CSFIS01.ORA                                            

D:/ORACLE/ORADATA/WJSFMS/RSFIS01.ORA                                            

D:/ORACLE/ORADATA/WJSFMS/RSFIS02.ORA                                          

D:/ORACLE/ORADATA/WJSFMS/HSFIS01.ORA                                           

D:/ORACLE/ORADATA/WJSFMS/CINDX01.ORA                                            

D:/ORACLE/ORADATA/WJSFMS/RINDX01.ORA                                           

D:/ORACLE/ORADATA/WJSFMS/RINDX02.ORA                                         

D:/ORACLE/ORADATA/WJSFMS/HINDX01.ORA                                           

D:/ORACLE/ORADATA/WJSFMS/UNDOTBS2.ORA

执行alter database  rename  file

Sql> alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/SYSTEM01.DBF' to 'E:/Oradata/WJSFMS/SYSTEM01.DBF';                                      

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/UNDOTBS01.DBF' to 'E:/Oradata/WJSFMS/UNDOTBS01.DBF';                                         

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/CWMLITE01.DBF' to 'E:/Oradata/WJSFMS/CWMLITE01.DBF';                                          

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/DRSYS01.DBF' to 'E:/Oradata/WJSFMS/DRSYS01.DBF';                                           

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/EXAMPLE01.DBF' to 'E:/Oradata/WJSFMS/EXAMPLE01.DBF';                                         

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/INDX01.DBF' to 'E:/Oradata/WJSFMS/INDX01.DBF';                                            

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/TOOLS01.DBF' to 'E:/Oradata/WJSFMS/TOOLS01.DBF';                                           

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/USERS01.DBF' to 'E:/Oradata/WJSFMS/USERS01.DBF';                                           

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/CSFIS01.ORA' to 'E:/Oradata/WJSFMS/CSFIS01.ORA';                                           

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/RSFIS01.ORA' to 'E:/Oradata/WJSFMS/RSFIS01.ORA';                                            

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/RSFIS02.ORA' to 'E:/Oradata/WJSFMS/RSFIS02.ORA';                                           

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/HSFIS01.ORA' to 'E:/Oradata/WJSFMS/HSFIS01.ORA';                                           

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/CINDX01.ORA' to 'E:/Oradata/WJSFMS/CINDX01.ORA';                                           

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/RINDX01.ORA' to 'E:/Oradata/WJSFMS/RINDX01.ORA';                                           

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/RINDX02.ORA' to 'E:/Oradata/WJSFMS/RINDX02.ORA';                                            

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/HINDX01.ORA' to 'E:/Oradata/WJSFMS/HINDX01.ORA';                                           

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/UNDOTBS2.ORA' to 'E:/Oradata/WJSFMS/UNDOTBS2.ORA';

 

sql>alter 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:/ORACLE/ORADATA/ZISCO12/REDO01.LOG'

 

再次执行alter database  rename  file

sql> alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/REDO01.LOG' to 'G:/RedoLog/WJSFMS/REDO01.LOG';   

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/REDO02.LOG' to 'H:/RedoLog/WJSFMS/REDO02.LOG';

alter database  rename  file  'D:/ORACLE/ORADATA/WJSFMS/REDO03.LOG' to 'I:/RedoLog/WJSFMS/REDO03.LOG';                                       

 

此时执行alter database open

sql>alter database open;

此时提示:

Errors in file d:/oracle/admin/wjsfms/udump/wjsfms_ora_2516.trc:

ORA-10827: database must be opened with MIGRATE option

然后instance自动shutdown

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

10.     数据库升级:

查看oracle online document,决定手工升级数据库

sql>startup migrate pfile=d:/oracle/ora92/database/initwjsfms.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 come with the new 9.2 release, which create the system catalog views and all the necessary packages for using PL/SQL.

 

The following components are upgraded by running the uold_release.sql script:

 

Oracle9i Catalog Views

Oracle9i Packages and Types

 

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

SQL> SELECT comp_name, version, status

         FROM dba_registry;

 

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

 

COMP_NAME                      VERSION          STATUS

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

Oracle9i Catalog Views         9.2.0.1.0        VALID

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

Oracle Text                    9.0.1            LOADED

Oracle Workspace Manager       9.0.1.0.0        LOADED

Oracle interMedia              9.0.0.0.0        LOADED

Oracle Spatial                 9.0.0.0.0 BETA   LOADED

Ultrasearch                    9.0.1.0.0        LOADED

OLAP Catalog                   9.0.1.0.0        LOADED

 

11 rows selected.

 

 

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

SQL> @cmpdbmig.sql

 

The following components 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 component registry to determine which components were upgraded:

SQL> SELECT comp_name, version, status

         FROM dba_registry;

 

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

 

COMP_NAME                      VERSION         STATUS

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

Oracle9i Catalog Views         9.2.0.1.0       VALID

Oracle9i Packages and Types    9.2.0.1.0       VALID

JServer JAVA Virtual Machine   9.2.0.1.0       VALID

Oracle9i Java Packages         9.2.0.1.0       VALID

Oracle XDK for Java            9.2.0.2.0       UPGRADED

Oracle Text                    9.0.1           LOADED

Oracle Workspace Manager       9.2.0.1.0       VALID

Oracle interMedia              9.0.0.0.0       LOADED

Oracle Spatial                 9.0.0.0.0 BETA  LOADED

Ultrasearch                    9.0.1.0.0       LOADED

OLAP Catalog                   9.2.0.1.0       VALID

OLAP Analytic Workspace        9.2.0.1.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 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.

 

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

 

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 components that existed in the previous database. See "Upgrading Specific Components".

 

The following components require separate upgrade steps:

 

Oracle Text

 Oracle Ultra Search

 Oracle Spatial

 Oracle interMedia

 Oracle Visual Information Retrieval

 

Run utlrp.sql to recompile 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 components are valid and have been upgraded to release 9.2:

SQL> SELECT comp_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.     创建SPFILE

sql>create spfile from pfile;

12.     查看报警日志文件,发现temp file找不到:

           Errors in file d:/oracle/admin/wjsfms/bdump/wjsfms_dbw0_2200.trc:

          ORA-01186: file 201 failed verification tests

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

         ORA-01110: data file 201: 'D:/ORACLE/ORADATA/WJSFMS/TEMP01.DBF'

 

         Wed Nov 10 10:29:22 2004

        File 201 not verified due to error ORA-01157

       Wed Nov 10 10:29:22 2004

       Errors in file d:/oracle/admin/wjsfms/bdump/wjsfms_dbw0_2200.trc:

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

      ORA-01110: data file 202: 'D:/ORACLE/ORADATA/WJSFMS/TEMP2.ORA'

      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:/ORACLE/ORADATA/WJSFMS/TEMP01.DBF                                                   

                                                                                
TEMP2                        2            D: /ORACLE/ORADATA/WJSFMS/TEMP02.DBF   

SQL>alter database tempfile 1 drop including datafiles;

SQL>alter database tempfile 2 drop including datafiles;

SQL>alter tablespace temp

add tempfile 'e:/oradata/wjsfms/temp01.dbf' size 100m autoextend on next 1024k maxsize 1024m;

SQL>alter tablespace temp2

add tempfile 'e:/oradata/wjsfms/temp02.dbf' size 100m autoextend on next 1024k maxsize 1024m;

至此数据库转移完毕

阅读全文
0 0

相关文章推荐

img
取 消
img