ORACLE10G 10.2.0.1升級到10.2.0.5

檢視磁碟空間使用情況

[root@dongyang ~]# df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda2              18G   11G  6.2G  64% /

/dev/sda1             988M   42M  896M   5% /boot

tmpfs                1002M     0 1002M   0% /dev/shm

/dev/sdb1              12G  2.3G  9.0G  21% /u01

[root@dongyang ~]#

檢視作業系統版本

[root@dongyang ~]# cat /proc/version

Linux version 2.6.32-200.13.1.el5uek (mockbuild@ca-build9.us.oracle.com) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-50)) #1 SMP Wed Jul 27 21:02:33 EDT 2011

 [root@dongyang ~]# uname -r

2.6.32-200.13.1.el5uek

[root@dongyang ~]#

檢視當前資料庫版本

SQL> select * from v$version;

select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL>

檢視oracle使用者環境變數

[oracle@dongyang ~]$ cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

# User specific environment and startup programs

alias  uni="uniread sqlplus"

export  PATH

unset  USERNAME

export   ORACLE_BASE=/u01/app/oracle

export  ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export   PATH=$ORACLE_HOME/bin:$PATH

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

export  ORACLE_SID=fengzi

export  NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export  NLS_DATA_FORMAT="YYYY-MM-DD HH24:MI:SS"

export  NLS_DATA_LANGUAGE="AMERICAN"

export DISPLAY=192.168.56.1:0.0

備份資料庫軟體（磁碟空間足夠）

[root@dongyang ~]# df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda2              18G   11G  6.2G  64% /

/dev/sda1             988M   42M  896M   5% /boot

tmpfs                1002M     0 1002M   0% /dev/shm

/dev/sdb1              12G  2.3G  9.0G  21% /u01

[root@dongyang ~]#

[root@dongyang ~]# mkdir /u02

[root@dongyang ~]# cd /u01

[root@dongyang u01]# cp -r * /u02

關閉當前資料庫

[oracle@dongyang ~]$ echo $ORACLE_SID

fengzi

[oracle@dongyang ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 5 13:46:04 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

登入RMAN 啟動資料庫到mount狀態

[oracle@dongyang ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 5 13:48:42 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN>startup mount

database is already started

database mounted

備份當前控制檔案

RMAN>  backup current controlfile;

Starting backup at 05-JUN-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=322 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

channel ORA_DISK_1: starting piece 1 at 05-JUN-14

channel ORA_DISK_1: finished piece 1 at 05-JUN-14

piece handle=+DATA/fengzi/backupset/2014_06_05/ncnnf0_tag20140605t140242_0.380.849448965 tag=TAG20140605T140242 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05

Finished backup at 05-JUN-14

Starting Control File and SPFILE Autobackup at 05-JUN-14

piece handle=+DATA/fengzi/autobackup/2014_06_05/s_849447979.381.849448969 comment=NONE

Finished Control File and SPFILE Autobackup at 05-JUN-14

備份資料庫到 /u01/backup/目錄下

RMAN> backup database format '/u01/backup/2014_06_01_%U';

Starting backup at 05-JUN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=+DATA/fengzi/datafile/system.277.842187103

channel ORA_DISK_1: starting piece 1 at 05-JUN-14

channel ORA_DISK_1: finished piece 1 at 05-JUN-14

piece handle=/u01/backup/2014_06_01_27pa3431_1_1 tag=TAG20140605T140417 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00007 name=/u01/app/oracle/oradata/FENGZI/datafile/o1_mf_wer_9ns1wd2n_.dbf

input datafile fno=00008 name=/u01/app/oracle/oradata/yyyy12.dbf

input datafile fno=00009 name=/u01/app/oracle/oradata/tt.dbf

input datafile fno=00004 name=+DATA/fengzi/datafile/users2.dbf

input datafile fno=00005 name=+DATA/fengzi/datafile/undotbs.dbf

channel ORA_DISK_1: starting piece 1 at 05-JUN-14

channel ORA_DISK_1: finished piece 1 at 05-JUN-14

piece handle=/u01/backup/2014_06_01_28pa345d_1_1 tag=TAG20140605T140417 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=+DATA/fengzi/datafile/sysaux.279.842187235

channel ORA_DISK_1: starting piece 1 at 05-JUN-14

channel ORA_DISK_1: finished piece 1 at 05-JUN-14

piece handle=/u01/backup/2014_06_01_29pa3466_1_1 tag=TAG20140605T140417 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00002 name=+DATA/fengzi/datafile/undotbs1.dbf

channel ORA_DISK_1: starting piece 1 at 05-JUN-14

channel ORA_DISK_1: finished piece 1 at 05-JUN-14

piece handle=/u01/backup/2014_06_01_2apa347j_1_1 tag=TAG20140605T140417 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00006 name=+DATA/fengzi/datafile/users1.dbf

channel ORA_DISK_1: starting piece 1 at 05-JUN-14

channel ORA_DISK_1: finished piece 1 at 05-JUN-14

piece handle=/u01/backup/2014_06_01_2bpa348d_1_1 tag=TAG20140605T140417 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

Finished backup at 05-JUN-14

Starting Control File and SPFILE Autobackup at 05-JUN-14

piece handle=+DATA/fengzi/autobackup/2014_06_05/s_849447979.382.849449239 comment=NONE

Finished Control File and SPFILE Autobackup at 05-JUN-14

RMAN>

[oracle@dongyang ~]$ emctl stop dbconsole

TZ set to PRC

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 

Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.

http://dongyang:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 10g Database Control ...

 ...  Stopped.

[oracle@dongyang ~]$ isqlplusctl stop

iSQL*Plus 10.2.0.1.0

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

iSQL*Plus instance on port 5560 is not running ...

[oracle@dongyang ~]$

[oracle@dongyang ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-JUN-2014 13:43:16

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.10)(PORT=1521)))

The command completed successfully

[oracle@dongyang ~]$

[oracle@dongyang ~]$ echo $ORACLE_SID

fengzi

[oracle@dongyang ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 5 13:46:04 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> [oracle@dongyang ~]$ echo $ORACLE_SID

+ASM

[oracle@dongyang ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 5 13:46:30 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate;

ASM diskgroups dismounted

ASM instance shutdown

SQL>exit      ------sqlplus 要退出不然安裝軟體時會報錯

[root@dongyang oracle]# cd /oinstall

[root@dongyang oinstall]# unzip p8202632_10205_Linux-x86-64.zip

[root@dongyang oinstall]# ls

Disk1  README.htm  p8202632_10205_Linux-x86-64.zip

[root@dongyang oinstall]#

[root@dongyang oinstall]# chown -R oracle:oinstall /oinstall

[root@dongyang oinstall]# ls -l                             

total 1221940

drwxr-xr-x 5 oracle oinstall       4096 Jun  5 14:46 Disk1

-rwxr-xr-x 1 oracle oinstall     171131 Jun  5 14:46 README.htm

-rw-r--r-- 1 oracle oinstall 1249857866 Jun  5 14:48 p8202632_10205_Linux-x86-64.zip

[root@dongyang oinstall]#

[root@dongyang oinstall]# xhost +

xhost:  unable to open display "192.168.56.1:0.0"

[root@dongyang oinstall]# su - oracle

[oracle@dongyang ~]$ export DISPLAY=192.168.56.1:0.0

[oracle@dongyang ~]$ cd /oinstall

[oracle@dongyang oinstall]$ cd Disk1/

[oracle@dongyang Disk1]$ ls

install  patch_note.htm  response  runInstaller  stage

[oracle@dongyang Disk1]$ ./runInstaller

這裡環境變數沒問題直接下一步就可以

這裡需要提過一個電子郵件地址：可以下一步

這裡檢查未通過：我直接忽略了（因為是測試的）因為swap空間沒有達到需求，因為我的盤空間也不是很大就沒有增加swap

出現錯誤：指明ocssd 服務沒有關閉

[root@dongyang ~]# cd /u01/app/oracle/product/10.2.0/db_1/bin

 [root@dongyang bin]# ./crsctl stop crs

Stopping CSSD.

Shutting down CSS daemon.

Shutdown request successfully issued.

[root@dongyang bin]#

開始安裝

以root使用者執行指令碼

[root@dongyang bin]# /u01/app/oracle/product/10.2.0/db_1/root.sh

Running Oracle 10g root.sh script...

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /u01/app/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

   Copying dbhome to /usr/local/bin ...

   Copying oraenv to /usr/local/bin ...

   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'root'..

Operation successful.

Configuration for local CSS has been initialized

Adding to inittab

Startup will be queued to init within 30 seconds.

Checking the status of new Oracle init process...

Expecting the CRS daemons to be up within 600 seconds.

CSS is active on these nodes.

        dongyang

CSS is active on all nodes.

Oracle CSS service is installed and running under init(1M)

[root@dongyang bin]#

這時軟體安裝成功

啟動ASM

[oracle@dongyang ~]$ echo $ORACLE_SID

+ASM

[oracle@dongyang ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jun 5 16:05:38 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup

ASM instance started

Total System Global Area  130023424 bytes

Fixed Size                  2094544 bytes

Variable Size             102763056 bytes

ASM Cache                  25165824 bytes

ASM diskgroups mounted

SQL>

以升級模式啟動資料庫

[oracle@dongyang ~]$ echo $ORACLE_SID

fengzi

[oracle@dongyang ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jun 5 16:06:42 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 1124073472 bytes

Fixed Size                  2095736 bytes

Variable Size             704644488 bytes

Database Buffers          402653184 bytes

Redo Buffers               14680064 bytes

Database mounted.

Database opened.

SQL>

查詢資料庫狀態

SQL>  select status from v$instance;

STATUS

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

OPEN MIGRATE

SQL>

升級資料庫字典

SQL> @?/rdbms/admin/catupgrd.sql

過程 略

重啟資料庫編譯無效物件。執行指令碼utlrp.sql

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1124073472 bytes

Fixed Size                  2095736 bytes

Variable Size             754976136 bytes

Database Buffers          352321536 bytes

Redo Buffers               14680064 bytes

Database mounted.

Database opened.

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN  2014-06-05 17:13:45

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC>   objects in the database. Recompilation time is proportional to the

DOC>   number of invalid objects in the database, so this command may take

DOC>   a long time to execute on a database with a large number of invalid

DOC>   objects.

DOC>

DOC>   Use the following queries to track recompilation progress:

DOC>

DOC>   1. Query returning the number of invalid objects remaining. This

DOC>      number should decrease with time.

DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC>   2. Query returning the number of objects compiled so far. This number

DOC>      should increase with time.

DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC>   This script automatically chooses serial or parallel recompilation

DOC>   based on the number of CPUs available (parameter cpu_count) multiplied

DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC>   On RAC, this number is added across all RAC nodes.

DOC>

DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC>   recompilation. Jobs are created without instance affinity so that they

DOC>   can migrate across RAC nodes. Use the following queries to verify

DOC>   whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC>   1. Query showing jobs created by UTL_RECOMP

DOC>         SELECT job_name FROM dba_scheduler_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC>   2. Query showing UTL_RECOMP jobs that are running

DOC>         SELECT job_name FROM dba_scheduler_running_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END  2014-06-05 17:17:11

DOC> The following query reports the number of objects that have compiled

DOC> with errors (objects that compile with errors have status set to 3 in

DOC> obj$). If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

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

                  0

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

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

                          0

SQL>

重啟資料庫檢視檢視v$version

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1124073472 bytes

Fixed Size                  2095736 bytes

Variable Size             754976136 bytes

Database Buffers          352321536 bytes

Redo Buffers               14680064 bytes

Database mounted.

Database opened.

SQL>

SQL>  select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE    10.2.0.5.0      Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

SQL>