92-10g升級與降級_環境變數_PFILE

DBUA及手工升級，DownGrade，環境變數、PFILE及手工建庫指令碼。嘗試過9.2.0.4，升級為10.2.0.4，降級為9.2.0.6,再手工升級為10.2.0.4----記錄一下

[@more@]

安裝資料庫9.2並建立資料庫（後附環境變數及建庫指令碼）

安裝10.2.0.1.0軟體

以DBUA方式升級：

1、在9.2的profile下將主庫以升級方式啟動

SQL> STARTUP UPGRADE;或startup migrate（如果是大版本升級要用這個）

2、cd /u01/app/oracle/product/10.2/bin

./dbua

(在/etc/oratab確認有你要升級的庫的SID及HOME資訊）

提示RA-06553: PLS-213: package STANDARD not accessible

在$oraclehomeRDBMSADMIN下

把catalog.sql catproc.sql dbmsutil.sql 三個指令碼執行一下。

手工升級：

準備工作：

1、拷pfile,PWD,tnslistener,tnsnames到new_ORACLE_HOME,修改listentr.ora裡的home目錄

2、刪除10g裡過期的初始化引數（archive=enable,HASH_JOIN_ENABLE=true)

3、建立初始化引數裡指定的所有目錄，確保許可權

4、PFILE裡如有IFILE，要指定新路徑

開始UPDATEE：

1、將9下的庫關閉

2、將環境變數設為NEWHOME

■ ORACLE_HOME

■ PATH

■ ORA_NLS10

■ LD_LIBRARY_PATH

3、cd $ORACLE_HOME/dbs/admin

sqlplus / as sydba;

SQL>startup upgrade pfile=..

SQL>create tablespace sysaux datafile '/u01/oracle/oradata/sysaux01.dbf' size 200m resuse extent management local segment space management auto online;

SQL> spool upgrade;

SQL>@catupgrd.sql

sql>@utlu102s.sql

SQL>spool off

檢視upgrade.lst是否有異常

at last restart the database;

以下步驟可根據需要執行：

1、This step is only necessary if Oracle Label Security is in your

database.

SQL> @olstrig.sql

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

SQL> @utlrp.sql

3、Verify that all expected packages and classes are valid:

Select count(*) from dba_objects where status=’invalid’;

Select distinct object_name from dba_objects where status=’invalid’;

降級：

最多隻能降到9206，如果你裝的是9204，那要先打上9206的Patch。

以10的環境變數

SQL> STARTUP DOWNGRADE

SQL> SPOOL downgrade.log

SQL> @catdwgrd.sql（10.2.10執行的是這個，而10.1降級用的是d92000.sql，即dold_release.sql）

Sql>spool off

Sql>shutdown immediate

將環境變數設定回92

在92的home裡設定PFILE，PWDFILE等。

SQL> STARTUP MIGRATE

SQL> SPOOL reload.log

SQL> @catrelod.sql

SQL> SPOOL OFF

檢視pool日誌，有需要時你可以再執行N遍這個SQL。下面這個錯可以乎略

ORA-22308: operation not allowed on evolved type errors

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

以下這兩個SQL按需要執行

1. Do this step if the database is configured for Oracle Label Security. Copy the

olstrig.sql script from the 10.2 Oracle Home to the version to which the

database will be downgraded. Run olstrig.sql to re-create DML triggers on

tables with Oracle Label Security policies. (See Oracle Database Enterprise User

Administrator's Guide for more information.)

SQL> @olstrig.sql

2. Run utlrp.sql:

SQL> @utlrp.sql

The utlrp.sql script recompiles all existing PL/SQL modules that were

previously in an INVALID state, such as packages, procedures, types, and so on.

總結：

1、有錯誤上metalink查，官方的說法比goole後排除來得快。

2、官方文件不同版本對於同一個操作，文件可能是不一樣的。這裡的降級，10.1與10.2做法就不一樣。

附：

環境變數設定

[oracle@test ~]$ more .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

# Set Linux Kernel

export LD_ASSUME_KERNEL=2.4.18

# Oracle Environment

export ORACLE_BASE=/u01/oracle

export ORACLE_HOME=/u01/oracle/product/9.2

export ORACLE_SID=orcl

export ORACLE_TERM=xterm

export NLS_LANG="AMERICAN_AMERICA.zhs16gbk"

export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

#Set some variables for Manage

export PFILE=/u01/oracle/admin/orcl/pfile

export BDUMP=/u01/oracle/admin/orcl/bdump

export UDUMP=/u01/oracle/admin/orcl/udump

export TNS=/u01/oracle/product/9.2/network/admin

# Set shell search paths

export PATH=$ORACLE_HOME/bin:/sbin:/usr/sbin:$PATH

# set LANG

export LANG=en_US

export LC_ALL=en_US

# alias

alias sql='sqlplus /nolog'

alias dbasql='sqlplus "/ as sysdba"'

alias rm='rm -i'

alias alert='tail -f $BDUMP/alert*log'

alias alert2='tail -n 200 $BDUMP/alert*log'

alias df='df -h'

unset USERNAME

DISPLAY=192.168.10.107:0.0;export DISPLAY

set -o vi

初始化引數檔案PFILE

[oracle@test ~]$ more $PFILE/initorcl.ora

log_archive_dest_1='LOCATION=/u03/arc/archive'

log_archive_format=%t_%s.arc

log_archive_start=true（在10g裡obsolete）

instance_name=orcl

db_name=orcl

db_block_size=8192

db_cache_size=80m

pga_aggregate_target=50m

large_pool_size=10485760

shared_pool_size=12582912 (10g裡要求設定97431142以上)

background_dump_dest=/u01/oracle/admin/orcl/bdump

core_dump_dest=/u01/oracle/admin/orcl/cdump

user_dump_dest=/u01/oracle/admin/orcl/udump

control_files=("/u01/oracle/oradata/control01.ctl", "/u01/oracle/oradata/control02.ct

l", "/u01/oracle/oradata/control03.ctl")

compatible=9.2.0.0.0

hash_join_enabled=TRUE （在10g裡obsolete）

query_rewrite_enabled=FALSE

star_transformation_enabled=FALSE

timed_statistics=TRUE

remote_login_passwordfile=EXCLUSIVE

processes=500

parallel_max_servers=8

open_cursors=600

db_files=2000

fast_start_mttr_target=300

db_writer_processes=1

job_queue_processes=8

open_links=8

undo_management=AUTO

手工建庫

create database orcl controlfile reuse

maxdatafiles 250

maxinstances 2

maxlogmembers 3

maxlogfiles 18

datafile '/u01/oracle/oradata/orcl/system.dbf' size 600m reuse

autoextend off extent management local

default temporary tablespace temp

tempfile '/u01/oracle/oradata/orcl/tbs_temp_01.dbf' size 500m reuse autoextend off

undo tablespace tbs_undo01

datafile '/u01/oracle/oradata/orcl/tbs_undo1_01.dbf' size 500m reuse autoextend off

character set zhs16gbk

logfile group 1 ('/u01/oracle/oradata/orcl/redo01_01.dbf',

'/u01/oracle/oradata/orcl/redo01_02.dbf') size 10m reuse,

group 2 ('/u01/oracle/oradata/orcl/redo02_01.dbf',

'/u01/oracle/oradata/orcl/redo02_02.dbf') size 10m reuse,

group 3 ('/u01/oracle/oradata/orcl/redo03_01.dbf',

'/u01/oracle/oradata/orcl/redo03_02.dbf') size 10m reuse;