migrate

[root@rac1 ~]# uname -a
Linux rac1 2.6.9-78.ELsmp #1 SMP Wed Jul 9 15:39:47 EDT 2008 i686 i686 i386 GNU/Linux
[root@rac1 ~]# echo $HOSTTYPE
i386

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/db9i/dbs/arch
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
upgrade from 9.2.0.4.0 to 9.2.0.8

Patch Set Notes

Release 2 (9.2.0.8) Patch Set 7 for Linux x86
This patch set includes Oracle Universal Installer release 10.1.0.5. You must use this Oracle Universal Installer to
install this patch set and not Oracle Universal Installer from the 9.2.0.x maintenance release media or Oracle home

1.tar ORACLE_HOME
tar -cvf /base/DONT_TOUCH/ORACLE_9.2.4.tar /db9i
2.take a cold backup
RMAN> run
2> {set backup copies 1;
3> backup database format '/base/DONT_TOUCH/%U_%d';
4> }
3.stop all processes
dbshut--Shut Down Oracle Databases
lsnrctl stop--stop listener
agentctl stop--stop agent
ps -ef|grep ora
4.   ./runInstaller
question1: /OPatch/opatch (Permission denied)
solution:
chmod 740 /OPatch.
5.Postinstallation Tasks
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      ?/dbs/spfile@.ora
SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 83886080
--a.If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
SQL> show parameter java_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 83886080
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;

System altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
[oracle@rac1 logs]$ lsnrctl start

LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 19-JAN-2011 05:12:09

Copyright (c) 1991, 2006, Oracle Corporation.  All rights reserved.

Starting /u01/app/oracle/db9i/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 9.2.0.8.0 - Production
System parameter file is /u01/app/oracle/db9i/network/admin/listener.ora
Log messages written to /u01/app/oracle/db9i/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.8.0 - Production
Start Date                19-JAN-2011 05:12:09
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/db9i/network/admin/listener.ora
Listener Log File         /u01/app/oracle/db9i/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 logs]$ sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Jan 19 05:12:15 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup migrate
ORACLE instance started.

Total System Global Area  319885828 bytes
Fixed Size                   451076 bytes
Variable Size             285212672 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> spool patch.log
SQL> @?/rdbms/admin/catpatch.sql
DOC> BEGIN CATPATCH.SQL */

PL/SQL procedure successfully completed.

Session altered.

....being hung

DOC>================================================================
DOC>   The above query lists the components in the database, along
DOC>   with their current patchset version and status.
DOC>================================================================
DOC>*/
DOC> END CATPATCH.SQL */

solution：
review  alter file
+++++++++++++++++++++++++++++++++++++++++++++
Wed Jan 19 06:13:32 2011
ORACLE Instance orcl - Can not allocate log, archival required
Wed Jan 19 06:13:32 2011
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 14
All online logs needed archiving
  Current log# 3 seq# 13 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
 

++
Looks like archiving has been turned off. You will need to manually archive by logging in with SYSDBA privileges
and executing command ALTER SYSTEM ARCHIVE LOG ALL

cause by
Automatic archival             Disabled
SQL> alter system set log_archive_start=true scope=spfile;

System altered.
shutdown
startup
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled--
Archive destination            /u01/app/oracle/db9i/dbs/arch
Oldest online log sequence     16
Next log sequence to archive   18
Current log sequence           18
+++++++++++++++++++++++++++++++++++++++++++++

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

PL/SQL procedure successfully completed.

Table created.

Table created.

Table created.

Index created.

Table created.

Table created.

View created.

View created.

Package created.

No errors.

Package body created.

No errors.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL>

SQL>  col comp_name for a30
    select COMP_NAME,VERSION,STATUS from dba_registry;
SQL>
COMP_NAME                      VERSION                        STATUS
------------------------------ ------------------------------ -----------
Oracle9i Catalog Views         9.2.0.8.0                      VALID
Oracle9i Packages and Types    9.2.0.8.0                      VALID
Oracle Workspace Manager       9.2.0.1.0                      VALID
JServer JAVA Virtual Machine   9.2.0.8.0                      VALID
Oracle XDK for Java            9.2.0.10.0                     VALID
Oracle9i Java Packages         9.2.0.8.0                      VALID
Oracle interMedia              9.2.0.8.0                      VALID
Spatial                        9.2.0.8.0                      VALID
Oracle Text                    9.2.0.8.0                      VALID
Oracle XML Database            9.2.0.8.0                      INVALID
Oracle Ultra Search            9.2.0.8.0                      VALID

COMP_NAME                      VERSION                        STATUS
------------------------------ ------------------------------ -----------
Oracle Data Mining             9.2.0.8.0                      VALID
OLAP Analytic Workspace        9.2.0.8.0                      UPGRADED
Oracle OLAP API                9.2.0.8.0                      UPGRADED
OLAP Catalog                   9.2.0.8.0                      VALID

15 rows selected.

how to solved the invalid of xml database
solution:
startup migrate
@?/rdbms/admin/xdbrelod.sql

SQL> select COMP_NAME,VERSION,STATUS from dba_registry;

COMP_NAME                      VERSION                        STATUS
------------------------------ ------------------------------ -----------
Oracle9i Catalog Views         9.2.0.8.0                      VALID
Oracle9i Packages and Types    9.2.0.8.0                      VALID
Oracle Workspace Manager       9.2.0.1.0                      VALID
JServer JAVA Virtual Machine   9.2.0.8.0                      VALID
Oracle XDK for Java            9.2.0.10.0                     VALID
Oracle9i Java Packages         9.2.0.8.0                      VALID
Oracle interMedia              9.2.0.8.0                      VALID
Spatial                        9.2.0.8.0                      VALID
Oracle Text                    9.2.0.8.0                      VALID
Oracle XML Database            9.2.0.8.0                      VALID
Oracle Ultra Search            9.2.0.8.0                      VALID

COMP_NAME                      VERSION                        STATUS
------------------------------ ------------------------------ -----------
Oracle Data Mining             9.2.0.8.0                      VALID
OLAP Analytic Workspace        9.2.0.8.0                      UPGRADED
Oracle OLAP API                9.2.0.8.0                      UPGRADED
OLAP Catalog                   9.2.0.8.0                      VALID

15 rows selected.

[oracle@rac1 install]$ ./changePerm.sh

-------------------------------------------------------------------------------
Disclaimer: The purpose of this script. is to relax permissions on some of the
files in the database Oracle Home so that all clients can access them.

Please note that Oracle Corporation recommends using the most restrictive file
permissions as possible for your given implementation.  Running this script
should be done only after considering all security ramifications.
-------------------------------------------------------------------------------

Do you wish to continue (y/n) [n]: \c
y
Finished running the script. successfully

 

------------------------------------------------------------------------------------
Installing the Patch Set Noninteractively
The following procedure describes how to install the patch set noninteractively:

1.Log in as the Oracle software owner (typically oracle).

2.Copy the response file template provided in the response directory where you the patch set archive file.

3.Edit the values for all fields labeled as as described by the comments and examples in the template.

4.To run Oracle Universal Installer, enter a command similar to the following, where response_file is the full path to the response file that you edited:

$ cd patchset_directory/Disk1
$ ./runInstaller -silent -responseFile response_file

5.After the installation, run the $ORACLE_HOME/root.sh script. as the root user. If you are applying the patch set to an Oracle RAC installation, then run the root.sh script. on each node of the cluster.
------------------------------------------------------------------------------------

 

upgrade from 9i to 10g

install oracle10 on other ORACLE_HOME

Pre-Upgrade Validation Checks

=~=~=~=~=~=~=~=~=~=~=~= PuTTY log 2011.01.19 20:26:48 =~=~=~=~=~=~=~=~=~=~=~=
cat upgrade.log
SQL> @/base/app/oracle/db10g/rdbms/admin/utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility    01-19-2011 07:24:22        
.                                                                              
**********************************************************************         
Database:                                                                      
**********************************************************************         
--&gt name:       ORCL                                                           
--&gt version:    9.2.0.8.0                                                      
--&gt compatible: 9.2.0.0.0                                                      
.                                                                              
**********************************************************************         
Logfiles: [make adjustments in the current environment]                        
**********************************************************************         
--&gt The existing log files are adequate. No changes are required.              
.                                                                              
**********************************************************************         
Tablespaces: [make adjustments in the current environment]                     
**********************************************************************         
--&gt SYSTEM tablespace is adequate for the upgrade.                             
.... minimum required size: 573 MB                                             
.... AUTOEXTEND additional space required: 163 MB                              
--&gt TEMP tablespace is adequate for the upgrade.                               
.... minimum required size: 58 MB                                              
.... AUTOEXTEND additional space required: 18 MB                               
--&gt CWMLITE tablespace is adequate for the upgrade.                            
.... minimum required size: 16 MB                                              
--&gt DRSYS tablespace is adequate for the upgrade.                              
.... minimum required size: 27 MB                                              
.... AUTOEXTEND additional space required: 7 MB                                
--&gt EXAMPLE tablespace is adequate for the upgrade.                            
.... minimum required size: 150 MB                                             
.... AUTOEXTEND additional space required: 1 MB                                
--&gt ODM tablespace is adequate for the upgrade.                                
.... minimum required size: 10 MB                                              
--&gt XDB tablespace is adequate for the upgrade.                                
.... minimum required size: 48 MB                                              
.... AUTOEXTEND additional space required: 3 MB                                
.                                                                              
**********************************************************************         
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]            
**********************************************************************         
WARNING: --&gt "shared_pool_size" needs to be increased to at least 191703040    
WARNING: --&gt "streams_pool_size" is not currently defined and needs a value of 
at least 50331648                                                              
WARNING: --&gt "session_max_open_files" needs to be increased to at least 20     
.                                                                              
**********************************************************************         
Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]        
**********************************************************************         
-- No deprecated parameters found. No changes are required.                    
.                                                                              
**********************************************************************         
Obsolete Parameters: [Update Oracle Database 10.2 init.ora or spfile]          
**********************************************************************         
--&gt "hash_join_enabled"                                                        
--&gt "log_archive_start"                                                        
.                                                                              
**********************************************************************         
Components: [The following database components will be upgraded or installed]  
**********************************************************************         
--&gt Oracle Catalog Views         [upgrade]  VALID                              
--&gt Oracle Packages and Types    [upgrade]  VALID                              
--&gt JServer JAVA Virtual Machine [upgrade]  VALID                              
...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)                     
...is required to be installed from the 10g Companion CD.                      
--&gt Oracle XDK for Java          [upgrade]  VALID                              
--&gt Oracle Java Packages         [upgrade]  VALID                              
--&gt Oracle Text                  [upgrade]  VALID                              
--&gt Oracle XML Database          [upgrade]  INVALID                            
--&gt Oracle Workspace Manager     [upgrade]  VALID                              
--&gt Oracle Data Mining           [upgrade]  VALID                              
--&gt OLAP Analytic Workspace      [upgrade]  UPGRADED                           
--&gt OLAP Catalog                 [upgrade]  VALID                              
--&gt Oracle OLAP API              [upgrade]  UPGRADED                           
--&gt Oracle interMedia            [upgrade]  VALID                              
...The 'Oracle interMedia Image Accelerator' is                                
...required to be installed from the 10g Companion CD.                         
--&gt Spatial                      [upgrade]  VALID                              
--&gt Oracle Ultra Search          [upgrade]  VALID                              
... To successfully upgrade Ultra Search, install it from                      
... the 10g Companion CD.                                                      
.                                                                              
**********************************************************************         
Miscellaneous Warnings                                                         
**********************************************************************         
WARNING: --&gt Deprecated CONNECT role granted to some user/roles.               
.... CONNECT role after upgrade has only CREATE SESSION privilege.             
WARNING: --&gt Database contains stale optimizer statistics.                     
.... Refer to the 10g Upgrade Guide for instructions to update                 
.... statistics prior to upgrading the database.                               
.... Component Schemas with stale statistics:                                  
....   SYS                                                                     
....   XDB                                                                     
....   WMSYS                                                                   
....   ODM                                                                     
....   OLAPSYS                                                                 
....   MDSYS                                                                   
....   WKSYS                                                                   
WARNING: --&gt Database contains INVALID objects prior to upgrade.               
.... USER XDB has 1 INVALID objects.                                           
.                                                                              
**********************************************************************         
SYSAUX Tablespace:                                                             
[Create tablespace in the Oracle Database 10.2 environment]                    
**********************************************************************         
--&gt New "SYSAUX" tablespace                                                    
.... minimum required size for database upgrade: 500 MB                        
.                                                                              

PL/SQL procedure successfully completed.

SQL> exit

1.backup database

RMAN> run
2> {set backup copies 1;
3> backup database format '/base/DONT_TOUCH/%U_%d';
4> }

2.Create a sysaux tablespace

create tablespace sysaux datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf' size 512M extent management local segment space management auto;

3.change parameter
SQL> alter system set shared_pool_size=190M  scope=spfile;

System altered.

SQL> show parameter shared_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 8388608
shared_pool_size                     big integer 167772160
SQL> alter system set session_max_open_files=20 scope=spfile;

System altered.

4. shutdown
5. Copy the spfile (or pfile)   from the existing home to the 10g one.

cp ${ORACLE_HOME}/dbs/*${ORACLE_SID}* /dbs/
6. Edit oratab

Alter /etc/oratab (or /var/opt/oracle/oratab) to point to the10g home.
Once done, rerun oraenv to bring the alteration into effect.

7.edit profile    .bash_profile or export ORACLE_BASE  ORACLE_HOME
#export ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE=/base/app/oracle
#export ORACLE_HOME=$ORACLE_BASE/db9i
export ORACLE_HOME=$ORACLE_BASE/db10g
export ORACLE_SID=orcl
export LD_LIBRABRY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/sbin:$PATH
export TMP=/tmp
export TNS_ADMIN=$ORACLE_HOME/network/admin
8.sqlplus '/as sysdba'
SQL> startup upgrade
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area  339738624 bytes
Fixed Size                  1219304 bytes
Variable Size             301991192 bytes
Database Buffers           33554432 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

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

....beng hung

review alert logfile

Wed Jan 19 09:36:29 2011
MMNL absent for 4142 secs; Foregrounds taking over
Wed Jan 19 09:46:10 2011
MMNL absent for 4629 secs; Foregrounds taking over
Wed Jan 19 09:49:34 2011
MMNL absent for 4926 secs; Foregrounds taking over
Wed Jan 19 09:50:06 2011

 

rollback:
 shutdown immediate
 rman>startup nomount
 rman>restore controlfile from "/base/07m2g2te_1_1_ORCL_740821934";
 rman>catalog backuppiece "/base/07m2g2te_1_1_ORCL_740821934";
 rman>restore database;
 rman>recover database;

....

8. Recompile any invalid objects

@?/rdbms/admin/utlrp.sql
Compare the number of invalid objects with the number noted in step 3. It should hopefully be the same or less.

9. Then check the status of the upgrade

@?/rdbms/admin/utlu102s.sql
10. Alter or remove initialisation parameters

Temporarily creating a pfile is the easiest way.

create pfile from spfile;

shutdown immediate

vi ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
Alter/remove parameters identified in step 9. Set compatible to 10.2.0.0.0
startup

create spfile from pfile;

shutdown immediate

startup

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21993926/viewspace-684330/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21993926/viewspace-684330/