公司最近要上shop floor系統(tǒng),需將數(shù)據(jù)庫(kù)從東莞轉(zhuǎn)移過(guò)來(lái),以下就是轉(zhuǎn)移的經(jīng)過(guò)…
東莞系統(tǒng)環(huán)境:
OS:windows 2000 Advanced Server+SP4
Oracle 9i standard Edition Version: 9.0.1.1.1
吳江系統(tǒng)環(huán)境:
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,不創(chuàng)建數(shù)據(jù)庫(kù),安裝過(guò)程略.
2. 創(chuàng)建相關(guān)目錄: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數(shù)據(jù)文件到E:/Oradata/WJSFMS目錄下,臨時(shí)文件可不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. 新建實(shí)例,此SID必須跟原來(lái)的SID一樣…
C:/Oradim –NEW –SID WJSFMS –STARTMODE m
7. 創(chuàng)建密碼文件
C:/orapwd file=d:/oracle/ora92/database/pwdwjsfms.ora passWord=password entries=5
8. 修改初始參數(shù)文件INITwjsfms.ora,如沒(méi)有參數(shù)文件,則可用oracle自帶的參數(shù)文件進(jìn)行修改,紅色部分為修改部分:
…….
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
假如是根據(jù)oracle自帶的參數(shù)文件進(jìn)行修改的,則還需修改db_name,instance_name
9. 激活oracle
c: qlplus /nolog
sql>conn / as sysdba
sql>startup pfile=d:/oracle/ora92/database/initwjsfms.ora
此時(shí)提示如下信息(由于升級(jí)時(shí)沒(méi)有將相關(guān)信息spool出來(lái),故只能將alert文件中的信息show出來(lái)作為參考):
ORA-00218: block size 4096 of controlfile does not match DB_BLOCK_SIZE (8192)
ORA-00202: controlfile: 'G:/ControlFile/WJSFMS/CONTROL01.CTL'
出現(xiàn)此錯(cuò)誤的原因是原來(lái)的database的db_block_size跟現(xiàn)在的初始化參數(shù)設(shè)的不一樣,將初始化參數(shù)中的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 YSTEM01.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 YSTEM01.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
執(zhí)行alter database rename file
Sql> alter database rename file 'D:/ORACLE/ORADATA/WJSFMS YSTEM01.DBF'
to 'E:/Oradata/WJSFMS YSTEM01.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'
再次執(zhí)行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';
此時(shí)執(zhí)行alter database open
sql>alter database open;
此時(shí)提示:
Errors in file d:/oracle/admin/wjsfms/udump/wjsfms_ora_2516.trc:
ORA-10827: database must be opened with MIGRATE option
然后instance自動(dòng)shutdown
出現(xiàn)這個(gè)原因是由于數(shù)據(jù)庫(kù)版本不一致造成的,原來(lái)版本是9.0.1.1.1,現(xiàn)在是9.2.0.1.0.
10. 數(shù)據(jù)庫(kù)升級(jí):
查看oracle online document,決定手工升級(jí)數(shù)據(jù)庫(kù)
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. 創(chuàng)建SPFILE
sql>create spfile from pfile;
12. 查看報(bào)警日志文件,發(fā)現(xiàn)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;
至此數(shù)據(jù)庫(kù)轉(zhuǎn)移完畢
