Manual Database up gradation from 9.2.0 to 10.1.0 in Same server

Step : 1
 Pre-request in the 9i Database.
SQL> select name from v$database;
 NAME
———
TEST
 SQL> select count(*) from dba_objects;
   COUNT(*)                                                                     
———-                                                                    
     29511                                                                    
 SQL> @C:\oracle\ora92\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> select count(*) from dba_objects;
   COUNT(*)                                                                    
———-                                                                    
     29511                                                                    
 SQL> select count(*),object_name from dba_objects where status=’INVALID            ‘ GROUP BY OBJECT_NAME;
 no rows selected
 Spool the output of the below query and do the modification as mentioned after backing up the DB
 SQL> @E:\oracle\product\10.1.0\db_1\RDBMS\ADMIN\utlu101i.sql
Oracle Database 10.1 Upgrade Information Tool    08-22-2009 21:29:58          
.                                                                              
*************************************************************************     
Database:                                                                     
———                                                                     
–> name: TEST                                                                
–> version: 9.2.0.1.0                                                        
–> compatibility: 9.2.0.0.0                                                  
.                                                                              
*************************************************************************     
Logfiles: [make adjustments in the current environment]                       
—————————————————-                          
– The existing log files are adequate. No changes are required.              
.                                                                             
*************************************************************************     
Tablespaces: [make adjustments in the current environment]                    
———————————————————-                    
–> SYSTEM tablespace is adequate for the upgrade.                             
…. owner: SYS                                                               
…. minimum required size: 577 MB                                            
–> CWMLITE tablespace is adequate for the upgrade.                            
…. owner: OLAPSYS                                                           
…. minimum required size: 9 MB                                              
–> DRSYS tablespace is adequate for the upgrade.                             
…. owner: CTXSYS                                                            
…. minimum required size: 10 MB                                             
–> ODM tablespace is adequate for the upgrade.                               
…. owner: ODM                                                                
…. minimum required size: 9 MB                                              
–> XDB tablespace is adequate for the upgrade.                               
…. owner: XDB                                                                
…. minimum required size: 48 MB                                             
.                                                                             
*************************************************************************     
Options: [present in existing database]                                       
—————————————                                       
–> Partitioning                                                               
–> Spatial                                                                   
–> OLAP                                                                      
–> Oracle Data Mining                                                         
WARNING: Listed option(s) must be installed with Oracle Database 10.1         
.                                                                             
*************************************************************************     
Update Parameters: [Update Oracle Database 10.1 init.ora or spfile]           
——————————————————————-           
WARNING: –> “shared_pool_size” needs to be increased to at least “150944944″ 
–> “pga_aggregate_target” is already at “25165824″ calculated new value is   
“25165824″                                                                    
–> “large_pool_size” is already at “8388608″ calculated new value is “8388608″
WARNING: –> “java_pool_size” needs to be increased to at least “50331648″    
.                                                                             
*************************************************************************     
Deprecated Parameters: [Update Oracle Database 10.1 init.ora or spfile]       
———————————————————————–       
– No deprecated parameters found. No changes are required.                   
.                                                                              
*************************************************************************     
Obsolete Parameters: [Update Oracle Database 10.1 init.ora or spfile]         
———————————————————————         
–> “hash_join_enabled”                                                       
–> “log_archive_start”                                                       
.                                                                             
*************************************************************************     
Components: [The following database components will be upgraded or installed] 
—————————————————————————– 
–> Oracle Catalog Views         [upgrade]  VALID                             
–> Oracle Packages and Types    [upgrade]  VALID                             
–> JServer JAVA Virtual Machine [upgrade]  VALID                             
…The ‘JServer JAVA Virtual Machine’ JAccelerator (NCOMP)                    
…is required to be installed from the 10g Companion CD.                     
…                                                                           
–> Oracle XDK for Java          [upgrade]  VALID                             
–> Oracle Java Packages         [upgrade]  VALID                             
–> Oracle XML Database          [upgrade]  VALID                             
–> Oracle Workspace Manager     [upgrade]  VALID                              
–> Oracle Data Mining           [upgrade]                                    
–> OLAP Analytic Workspace      [upgrade]                                    
–> OLAP Catalog                 [upgrade]                                     
–> Oracle OLAP API              [upgrade]                                    
–> Oracle interMedia            [upgrade]                                    
…The ‘Oracle interMedia Image Accelerator’ is                                
…required to be installed from the 10g Companion CD.                        
…                                                                           
–> Spatial                      [upgrade]                                    
–> Oracle Text                  [upgrade]  VALID                             
–> Oracle Ultra Search          [upgrade]  VALID                             
.                                                                             
*************************************************************************     
.                                                                             
*************************************************************************     
SYSAUX Tablespace: [Create tablespace in Oracle Database 10.1 environment]    
————————————————————————–    
–> New “SYSAUX” tablespace                                                   
…. minimum required size for database upgrade: 500 MB                       
Please create the new SYSAUX Tablespace AFTER the Oracle Database             
10.1 server is started and BEFORE you invoke the upgrade script.              
.                                                                              
*************************************************************************     
Oracle Database 10g: Changes in Default Behavior                             
————————————————                               
This page describes some of the changes in the behavior of Oracle             
Database 10g from that of previous releases. In some cases the                
default values of some parameters have changed.  In other cases               
new behaviors/requirements have been introduced that may affect               
current scripts or applications. More detailed information is in              
the documentation.                                                            
SQL OPTIMIZER                                                                 
The Cost Based Optimizer (CBO) is now enabled by default.                     
* Rule-based optimization is not supported in 10g (setting                    
OPTIMIZER_MODE to RULE or CHOOSE is not supported).  See Chapter              
12, “Introduction to the Optimizer,” in Oracle Database                       
Performance Tuning Guide.                                                     
* Collection of optimizer statistics is now performed by default,             
automatically for all schemas (including SYS), for pre-existing               
databases upgraded to 10g, and for newly created 10g databases.               
Gathering optimizer statistics on stale objects is scheduled by               
default to occur daily during the maintenance window.  See                    
Chapter 15, “Managing Optimizer Statistics” in Oracle Performance             
Tuning Guide.                                                                  
* See the Oracle Database Upgrade Guide for changes in behavior               
for the COMPUTE STATISTICS clause of CREATE INDEX, and for                    
behavior changes in SKIP_UNUSABLE_INDEXES.                                    
UPGRADE/DOWNGRADE                                                            
* After upgrading to 10g, the minimum supported release to                    
downgrade to is Oracle 9i R2 release 9.2.0.3 (or later), and the              
minimum value for COMPATIBLE is 9.2.0.  The only supported                    
downgrade path is for those users who have kept COMPATIBLE=9.2.0              
and have an installed 9i R2 (release 9.2.0.3 or later)                        
executable.  Users upgrading to 10g from prior releases (such as              
Oracle 8, Oracle 8i or 9iR1) cannot downgrade to 9i R2 unless                 
they first install 9i R2. When upgrading to10g, by default the                
database will remain at 9i R2 file format compatibility, so the               
on disk structures that 10g writes are compatible with 9i R2                  
structures; this makes it possible to downgrade to 9i R2. Once                
file format compatibility has been explicitly advanced to 10g                 
(using COMPATIBLE=10.x.x), it is no longer possible to downgrade.             
See the Oracle Database Upgrade Guide.                                        
* A SYSAUX tablespace is created upon upgrade to 10g. The SYSAUX               
tablespace serves as an auxiliary tablespace to the SYSTEM                    
tablespace. Because it is the default tablespace for many Oracle              
features and products that previously required their own                      
tablespaces, it reduces the number of tablespaces required by                 
Oracle that you, as a DBA, must maintain.                                     
MANAGEABILITY                                                                
* Database performance statistics are now collected by the                    
Automatic Workload Repository (AWR) database component,                       
automatically upon upgrade to 10g and also for newly created 10g              
databases.  This data is stored in the SYSAUX tablespace, and is              
used by the database for automatic generation of performance                  
recommendations. See Chapter 5, “Automatic Performance                        
Statistics” in the Oracle Database Performance Tuning Guide.                  
* If you currently use Statspack for performance data gathering,              
see section 1. of the Statspack readme (spdoc.txt in the RDBMS                
ADMIN directory) for directions on using Statspack in 10g to                  
avoid conflict with the AWR.                                                  
MEMORY                                                                       
* Automatic PGA Memory Management is now enabled by default                    
(unless PGA_AGGREGATE_TARGET is explicitly set to 0 or                        
WORKAREA_SIZE_POLICY is explicitly set to MANUAL).                            
PGA_AGGREGATE_TARGET is defaulted to 20% of the SGA size, unless               
explicitly set.  Oracle recommends tuning the value of                        
PGA_AGGREGATE_TARGET after upgrading.  See Chapter 14 of the                  
Oracle Database Performance Tuning Guide.                                     
* Previously, the number of SQL cursors cached by PL/SQL was                  
determined by OPEN_CURSORS.  In 10g, the number of cursors cached             
is determined by SESSION_CACHED_CURSORS.  See the Oracle Database             
Reference manual.                                                              
* SHARED_POOL_SIZE must increase to include the space needed for              
shared pool overhead.                                                         
* The default value of DB_BLOCK_SIZE is operating system                      
specific, but is typically 8KB (was typically 2KB in previous                 
releases).                                                                    
TRANSACTION/SPACE                                                             
* Dropped objects are now moved to the recycle bin, where the                 
space is only reused when it is needed. This allows ‘undropping’              
a table using the FLASHBACK DROP feature.  See Chapter 14 of the              
Oracle Database Administrator’s Guide.                                        
* Auto tuning undo retention is on by default. For more                       
information, see Chapter 10, “Managing the Undo Tablespace,” in                
the Oracle Database Administrator’s Guide.                                    
CREATE DATABASE                                                              
* In addition to the SYSTEM tablespace, a SYSAUX tablespace is                
always created at database creation, and upon upgrade to 10g. The             
SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM             
tablespace. Because it is the default tablespace for many Oracle              
features and products that previously required their own                      
tablespaces, it reduces the number of tablespaces required by                 
Oracle that you, as a DBA, must maintain. See Chapter 2,                      
“Creating a Database,” in the Oracle Database Administrator’s                 
Guide.                                                                        
* In 10g, by default all new databases are created with 10g file              
format compatibility. This means you can immediately use all the              
10g features.  Once a database uses 10g compatible file formats,              
it is not possible to downgrade this database to prior releases.              
Minimum and default logfile sizes are larger. Minimum is now 4                
MB, default is 50MB, unless you are using Oracle Managed Files                
(OMF) when it is 100 MB.                                                      
PL/SQL procedure successfully completed.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\oradata\test\archive
Oldest online log sequence     91
Next log sequence to archive   93
Current log sequence           93
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
  Backup complete database. (Cold backup)
Step :2
Check the space needed and stop the listner and delete the sid.
 C:\Documents and Settings\Administrator>set oracle_sid=test
 C:\Documents and Settings\Administrator>sqlplus /nolog
 SQL*Plus: Release 9.2.0.1.0 – Production on Sat Aug 22 21:36:52 2009
 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
 Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> desc sm$ts_avail
 Name                                      Null?    Type
 —————————————– ——– —————————-
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 SQL> select * from sm$ts_avail;
 TABLESPACE_NAME                     BYTES
—————————— ———-
CWMLITE                          20971520
DRSYS                            20971520
EXAMPLE                         155975680
INDX                             26214400
ODM                              20971520
SYSTEM                          419430400
TOOLS                            10485760
UNDOTBS1                        209715200
USERS                            26214400
XDB                              39976960
 10 rows selected.
 SQL> select * from sm$ts_used;
 TABLESPACE_NAME                     BYTES
—————————— ———-
CWMLITE                           9764864
DRSYS                            10092544
EXAMPLE                         155779072
ODM                               9699328
SYSTEM                          414908416
TOOLS                             6291456
UNDOTBS1                          9814016
XDB                              39714816
 8 rows selected.
 SQL> select * from sm$ts_free;
 TABLESPACE_NAME                     BYTES
—————————— ———-
CWMLITE                          11141120
DRSYS                            10813440
EXAMPLE                            131072
INDX                             26148864
ODM                              11206656
SYSTEM                            4456448
TOOLS                             4128768
UNDOTBS1                        199753728
USERS                            26148864
XDB                                196608
 10 rows selected.
 SQL> ho LSNRCTL
LSNRCTL> start
Starting tnslsnr: please wait…
 Failed to open service , error 1060.
TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 – Production
System parameter file is C:\oracle\ora92\network\admin\listener.ora
Log messages written to C:\oracle\ora92\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dee-6e78e526295)(PORT=1521)))
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dee-6e78e526295)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 – Production
Start Date                22-AUG-2009 22:00:00
Uptime                    0 days 0 hr. 0 min. 16 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   C:\oracle\ora92\network\admin\listener.ora
Listener Log File         C:\oracle\ora92\network\log\listener.log
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dee-6e78e526295)(PORT=1521)))
Services Summary…
Service “TEST” has 1 instance(s).
  Instance “TEST”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dee-6e78e526295)(PORT=1521)))
The command completed successfully
LSNRCTL> start
Starting tnslsnr: please wait…
 TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 – Production
System parameter file is C:\oracle\ora92\network\admin\listener.ora
Log messages written to C:\oracle\ora92\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dee-6e78e526295)(PORT=1521)))
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dee-6e78e526295)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 – Production
Start Date                22-AUG-2009 22:00:48
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   C:\oracle\ora92\network\admin\listener.ora
Listener Log File         C:\oracle\ora92\network\log\listener.log
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dee-6e78e526295)(PORT=1521)))
Services Summary…
Service “TEST” has 1 instance(s).
  Instance “TEST”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL> exit
 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production
C:\Documents and Settings\Administrator>lsnrctl stop
 LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 – Production on 22-AUG-2009 22:03:14
 copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dee-6e78e526295)(PORT=1521)))
The command completed successfully
C:\Documents and Settings\Administrator>oradim -delete -sid test
Step: 3
 Install ORACLE 10g Software in different Home.
Starting the DB with 10g instance and upgradation Process.
 SQL> startup pfile=’E:\oracle\product\10.1.0\admin\test\pfile\init.ora.73200934649′ nomount
ORACLE instance started.
 Total System Global Area  239075328 bytes                                     
Fixed Size                   788308 bytes                                     
Variable Size             212859052 bytes                                     
Database Buffers           25165824 bytes                                     
Redo Buffers                 262144 bytes                                     
 SQL> create spfile from pfile=’E:\oracle\product\10.1.0\admin\test\pfile\init.ora.73200934649′;
 File created.
 SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
 Total System Global Area 239075328 bytes                                     
Fixed Size                   788308 bytes                                      
Variable Size             212859052 bytes                                     
Database Buffers           25165824 bytes                                     
Redo Buffers                 262144 bytes                                      
ORA-01990: error opening password file (create password file)
SQL> conn /as sysdba
Connected.
SQL> @”C:\Documents and Settings\Administrator\Desktop\sys.sql.txt”
 (Sys.sql.txt contains sysaux tablespace script as shown below)
 create tablespace SYSAUX datafile ‘sysaux01.dbf’
size 70M reuse
extent management local
segment space management auto
online;
 Tablespace created.
 SQL> @E:\oracle\product\10.1.0\db_1\RDBMS\ADMIN\u0902000.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an “ORA-01722: invalid number”
DOC> error if the database server version is not correct for this script.
DOC> Shutdown ABORT and use a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
 no rows selected
 DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an “ORA-01722: invalid number”
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform a “SHUTDOWN ABORT”  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
 no rows selected
 DOC>#######################################################################
DOC>#######################################################################
DOC> The following statements will cause an “ORA-01722: invalid number”
DOC> error if the SYSAUX tablespace does not exist or is not
DOC> ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
DOC> SEGMENT SPACE MANAGEMENT AUTO.
DOC>
DOC> The SYSAUX tablespace is used in 10.1 to consolidate data from
DOC> a number of tablespaces that were separate in prior releases.
DOC> Consult the Oracle Database Upgrade Guide for sizing estimates.
DOC>
DOC> Create the SYSAUX tablespace, for example,
DOC>
DOC> create tablespace SYSAUX datafile ‘sysaux01.dbf’
DOC>      size 70M reuse
DOC>      extent management local
DOC>      segment space management auto
DOC>      online;
DOC>
DOC> Then rerun the u0902000.sql script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
 no rows selected
 no rows selected
 no rows selected
 no rows selected
 no rows selected
 Session altered.
Session altered.
 The script will run according to the size of the database…
All packages,scripts,synonyms will be upgraded
 At last it will show the message as follows
 TIMESTAMP
——————————————————————————–
  1 row selected.
  PL/SQL procedure successfully completed.
 COMP_ID    COMP_NAME                           STATUS      VERSION
———- ———————————– ———– ———-
CATALOG    Oracle Database Catalog Views       VALID       10.1.0.2.0
CATPROC    Oracle Database Packages and Types  VALID       10.1.0.2.0
JAVAVM     JServer JAVA Virtual Machine        VALID       10.1.0.2.0
XML        Oracle XDK                          VALID       10.1.0.2.0
CATJAVA    Oracle Database Java Packages       VALID       10.1.0.2.0
XDB        Oracle XML Database                 VALID       10.1.0.2.0
OWM        Oracle Workspace Manager            VALID       10.1.0.2.0
ODM        Oracle Data Mining                  VALID       10.1.0.2.0
APS        OLAP Analytic Workspace             VALID       10.1.0.2.0
AMD        OLAP Catalog                        VALID       10.1.0.2.0
XOQ        Oracle OLAP API                     VALID       10.1.0.2.0
ORDIM      Oracle interMedia                   VALID       10.1.0.2.0
SDO        Spatial                             VALID       10.1.0.2.0
CONTEXT    Oracle Text                         VALID       10.1.0.2.0
WK         Oracle Ultra Search                 VALID       10.1.0.2.0
15 rows selected.
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above query lists the SERVER components in the upgraded
DOC>   database, along with their current version and status.
DOC>
DOC>   Please review the status and version columns and look for
DOC>   any errors in the spool log file.  If there are errors in the spool
DOC>   file, or any components are not VALID or not the current version,
DOC>   consult the Oracle Database Upgrade Guide for troubleshooting
DOC>   recommendations.
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
PL/SQL procedure successfully completed.
COMP_ID    COMP_NAME                           STATUS      VERSION
———- ———————————– ———– ———-
CATALOG    Oracle Database Catalog Views       VALID       10.1.0.2.0
CATPROC    Oracle Database Packages and Types  VALID       10.1.0.2.0
JAVAVM     JServer JAVA Virtual Machine        VALID       10.1.0.2.0
XML        Oracle XDK                          VALID       10.1.0.2.0
CATJAVA    Oracle Database Java Packages       VALID       10.1.0.2.0
XDB        Oracle XML Database                 VALID       10.1.0.2.0
OWM        Oracle Workspace Manager            VALID       10.1.0.2.0
ODM        Oracle Data Mining                  VALID       10.1.0.2.0
APS        OLAP Analytic Workspace             VALID       10.1.0.2.0
AMD        OLAP Catalog                        VALID       10.1.0.2.0
XOQ        Oracle OLAP API                     VALID       10.1.0.2.0
ORDIM      Oracle interMedia                   VALID       10.1.0.2.0
SDO        Spatial                             VALID       10.1.0.2.0
CONTEXT    Oracle Text                         VALID       10.1.0.2.0
WK         Oracle Ultra Search                 VALID       10.1.0.2.0
15 rows selected.
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above query lists the SERVER components in the upgraded
DOC>   database, along with their current version and status.
DOC>
DOC>   Please review the status and version columns and look for
DOC>   any errors in the spool log file.  If there are errors in the spool
DOC>   file, or any components are not VALID or not the current version,
DOC>   consult the Oracle Database Upgrade Guide for troubleshooting
DOC>   recommendations.
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP DBUPG_END  2009-08-22 22:59:09
1 row selected.
 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  239075328 bytes
Fixed Size                   788308 bytes
Variable Size             212859052 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
Database opened.
SQL> select count(*) from dba_objects where status=’INVALID’;
   COUNT(*)
———-
       776
 1 row selected.
 SQL> @E:\oracle\product\10.1.0\db_1\RDBMS\ADMIN\utlu101s.sql
 PL/SQL procedure successfully completed.
 Oracle Database 10.1 Upgrade Status Tool 22-AUG-2009 11:18:36
–> Oracle Database Catalog Views       Normal successful completion
–> Oracle Database Packages and Types  Normal successful completion
–> JServer JAVA Virtual Machine        Normal successful completion
–> Oracle XDK                          Normal successful completion
–> Oracle Database Java Packages       Normal successful completion
–> Oracle XML Database                 Normal successful completion
–> Oracle Workspace Manager            Normal successful completion
–> Oracle Data Mining                  Normal successful completion
–> OLAP Analytic Workspace             Normal successful completion
–> OLAP Catalog                        Normal successful completion
–> Oracle OLAP API                     Normal successful completion
–> Oracle interMedia                   Normal successful completion
–> Spatial                             Normal successful completion
–> Oracle Text                         Normal successful completion
–> Oracle Ultra Search                 Normal successful completion
No problems detected during upgrade
 PL/SQL procedure successfully completed.
 SQL> @E:\oracle\product\10.1.0\db_1\RDBMS\ADMIN\utlrp.sql
 TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_BGN  2009-08-22 23:19:07
 1 row selected.
 PL/SQL procedure successfully completed.
 TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_END  2009-08-22 23:20:13
 1 row selected.
 PL/SQL procedure successfully completed.
 PL/SQL procedure successfully completed.
 SQL> select count(*) from dba_objects where status=’INVALID’;
   COUNT(*)
———-
         0
 1 row selected.
 SQL> select * from V$version;
 BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Prod
PL/SQL Release 10.1.0.2.0 – Production
CORE  10.1.0.2.0        Production
TNS for 32-bit Windows: Version 10.1.0.2.0 – Production
NLSRTL Version 10.1.0.2.0 – Production
 5 rows selected.
 Check the Database that everything is working fine.

Schema refresh in oracle 9i

Now we are going to refresh SH schema.
  Steps for schema refresh – before exporting
 Spool the output of roles and privileges assigned to the user .use the query below to view the role s and privileges and spool the out as .sql file.

1. SELECT object_type,count(*) from dba_objects where owner=’SHTEST’ group by object_type;
2. Verify total no of objects from above query.
3. write a dynamic query as below
4. select ‘grant ‘ || privilege ||’ to sh;’ from session_privs;
5. select ‘grant ‘ || role ||’ to sh;’ from session_roles;
6. query the default tablespace and size
7. select tablespace_name,sum(bytes/1024/1024) from dba_segments where owner=’SH’ group by tablespace_name;

  Export the ‘sh’ schema
 exp ‘usernmae/password file=’/location/sh_bkp.dmp’ log=’/location/sh_exp.log’ owner=’SH’ direct=y
steps to drrop and recreate schema
Drop the SH schema

1. Create the SH schema with the default tablespace and allocate quota on that tablespace.
2. Now run the roles and privileges spooled scripts.
3. Connect the SH and verify the tablespace, roles and privileges.
4. then start importing

  Importing The ‘SH’ schema
 Imp ‘username/password’ file=’/location/sh_bkp.dmp’ log=’/location/sh_imp.log’
Fromuser=’SH’ touser=’SH’
 SQL> SELECT object_type,count(*) from dba_objects where owner=’SHTEST’ group by object_type; 
 Compiling and analyzing SH Schema
exec dbms_utility.compile_schema(‘SH’);
execdbms_utility.analyze_schema(‘SH’,'ESTIMATE’,ESTIMATE_PERCENT=>20);
 Now connect the SH user and check for the import data.
 Schema refresh by dropping objects and truncating objects
 Export the ‘sh’ schema
 Take the schema full export as show above
 Drop all the objects in ‘SH’ schema
To drop the all the objects in the Schema
 Connect the schema
Spool the output
 SQL>set head off
SQL>spool drop_tables.sql
SQL>select ‘drop table ‘||table_name||’ cascade constraints purge;’ from user_tables;
SQL>spool off
 SQL>set head off
SQL>spool drop_other_objects.sql
SQL>select ‘drop ‘||object_type||’ ‘||object_name||’;’ from user_objects;
SQL>spool off
Now run the script all the objects will be dropped,
 Importing THE ‘SH’ schema
Imp ‘usernmae/password’ file=’/location/sh_bkp.dmp’ log=’/location/sh_imp.log’
Fromuser=’SH’ touser=’SH’
SQL> SELECT object_type,count(*) from dba_objects where owner=’SHTEST’ group by object_type;
 Compiling and analyzing SH Schema
exec dbms_utility.compile_schema(‘SH’);
execdbms_utility.analyze_schema(‘SH’,'ESTIMATE’,ESTIMATE_PERCENT=>20);
 Now connect the SH user and check for the import data.
To enable constraints use the query below
SELECT ‘ALTER TABLE ‘||TABLE_NAME||’ENABLE CONSTRAINT ‘||CONSTRAINT_NAME||’;'FROM USER_CONSTRAINTS
WHERE STATUS=’DISABLED’;
 Truncate all the objects in ‘SH’ schema
 To truncate the all the objects in the Schema
 Connect the schema
Spool the output
 SQL>set head off 
SQL>spool truncate_tables.sql
SQL>select ‘truncate table ‘||table_name from user_tables;
SQL>spool off
 SQL>set head off 
SQL>spool truncate_other_objects.sql
SQL>select ‘truncate ‘||object_type||’ ‘||object_name||’;’ from user_objects;
SQL>spool off
Now run the script all the objects will be truncated.
Disabiling the reference constraints
 If there is any constraint violation while truncating use the below query to find reference key constraints and disable them. Spool the output of below query and run the script.
 Select constraint_name,constraint_type,table_name FROM ALL_CONSTRAINTS 
 where constraint_type=’R’
 and r_constraint_name in (select constraint_name from all_constraints
 where  table_name=’TABLE_NAME’)
 Importing THE ‘SH’ schema
Imp ‘usernmae/password’ file=’/location/sh_bkp.dmp’ log=’/location/sh_imp.log’
Fromuser=’SH’ touser=’SH’
SQL> SELECT object_type,count(*) from dba_objects where owner=’SHTEST’ group by object_type;
Compiling and analyzing SH Schema
exec dbms_utility.compile_schema(‘SH’);
exec dbms_utility.analyze_schema(‘SH’,'ESTIMATE’,ESTIMATE_PERCENT=>20);
Now connect the SH user and check for the import data.
 Schema refresh in oracle 10g
Here we can use Datapump
 Exporting the SH schema through Datapump
 expdp ‘username/password’ dumpfile=sh_exp.dmp directory=data_pump_dir schemas=sh
Dropping  the ‘SH’ user
  Query the default tablespace and verify the space in the tablespace and drop  the user.
 SQL>Drop user SH cascade;
Importing the SH schema through datapump
impdp ‘username/password’ dumpfile=sh_exp.dmp directory=data_pump_dir schemas=sh
 If you are importing to different schema use remap_schema option.
 Check for the imported objects and compile the invalid objects.