Oracle 9i -dan Oracle 10g – ye upgrade edilmesi


UPGRADE STEPS
==================================================
==> PRE_UPGRADE
===============================
 1) Oracle 10g profile ile sisteme baglaniriq
 2) cp $ORACLE_HOME/rdbms/admin/utlu102i.sql /tmp/utlu102i.sql
 3) Daha sonra Oracle 9i profile ile sisteme baglaniriq.
                cd /tmp
                sqlplus "/as sysdba"
                SQL> SPOOL info.log
                SQL> @utlu102i.sql
                SQL> SPOOL OFF
 4) /tmp/info.log dosyasi incelenir.Sirasiyla gerekli ishlemler edilir.
4.1 ) Oracle 10g de CONNECT role uchun sadece "CREATE SESSION" privilege movcut. Varolan grantlari dagitmamaq  uchun movcut DB den user larin grantlari alinib,daha sonra script olarak chalishdirilmasi lazim.
SELECT 'grant alter session to '||grantee||';'
  FROM dba_role_privs
 WHERE granted_role = 'CONNECT'
   AND grantee NOT IN
          ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
           'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR',
           'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS',
           'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 'OLAPSYS',
           'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
union all
SELECT 'grant CREATE CLUSTER  to '||grantee||';'
  FROM dba_role_privs
 WHERE granted_role = 'CONNECT'
   AND grantee NOT IN
          ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
           'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR',
           'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS',
           'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 'OLAPSYS',
           'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM') 
union all   
SELECT 'grant CREATE DATABASE LINK to '||grantee||';'
  FROM dba_role_privs
 WHERE granted_role = 'CONNECT'
   AND grantee NOT IN
          ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
           'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR',
           'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS',
           'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 'OLAPSYS',
           'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
union all
SELECT 'grant CREATE SEQUENCE TO to '||grantee||';'
  FROM dba_role_privs
 WHERE granted_role = 'CONNECT'
   AND grantee NOT IN
          ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
           'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR',
           'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS',
           'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 'OLAPSYS',
           'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM') 
union all                 
SELECT 'grant CREATE SESSION to '||grantee||';'
  FROM dba_role_privs
 WHERE granted_role = 'CONNECT'
   AND grantee NOT IN
          ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
           'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR',
           'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS',
           'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 'OLAPSYS',
           'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
union all
SELECT 'grant CREATE SYNONYM to '||grantee||';'
  FROM dba_role_privs
 WHERE granted_role = 'CONNECT'
   AND grantee NOT IN
          ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
           'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR',
           'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS',
           'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 'OLAPSYS',
           'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM') 
union all                 
SELECT 'grant CREATE TABLE to '||grantee||';'
  FROM dba_role_privs
 WHERE granted_role = 'CONNECT'
   AND grantee NOT IN
          ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
           'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR',
           'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS',
           'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 'OLAPSYS',
           'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
union all                 
SELECT 'grant CREATE VIEW to '||grantee||';'
  FROM dba_role_privs
 WHERE granted_role = 'CONNECT'
   AND grantee NOT IN
          ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
           'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR',
           'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS',
           'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 'OLAPSYS',
           'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

4.2 ) DB Link password leri crypto formata cehvrildiyi uchun butun DB linklerin create scriptini yaratmaq lazimdir.
                                SELECT * FROM
(               
                SELECT  u.USERNAME,
                'create database link '
                 || username
                 || '.'
                 || NAME
                 || ' connect to '
                 || userid
                 || ' identified by '
                 || l.PASSWORD
                 || ' using '
                 || ''''
                 || HOST
                 || ''';'
            FROM SYS.link$ l, dba_users u
           WHERE u.user_id = l.owner#
          UNION ALL
          SELECT  'PUBLIC','create public database link '
                 || NAME
                 || ' connect to '
                 || userid
                 || ' identified by '
                 || l.PASSWORD
                 || ' using '
                 || ''''
                 || HOST
                 || ''';'
            FROM SYS.link$ l
           WHERE l.owner# = 1
)
ORDER BY 1;
 5) Oracle 9i profilini istifade ederek sisteme baglaniriq.
                sqlplus "/as sysdba"
                SQL> create pfile='/tmp/x.txt' from spfile;
ORA-19905: log_archive_format must contain %s, %t and %r ( pfile qaydasina qoyarken log_archive_format parametresi gozden kechirilmelidir. )
Daha sonra /tmp/x.txt file da gerekli parametre düzenlemeleri edilir.
**********************************************************************         
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]            
**********************************************************************         
WARNING: --> "streams_pool_size" is not currently defined and needs a value of 
at least 50331648                                                              
WARNING: --> "session_max_open_files" needs to be increased to at least 20     
**********************************************************************         
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]           
**********************************************************************         
-- No renamed parameters found. No changes are required.                       
**********************************************************************         
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************         
--> "hash_join_enabled"                                                        
--> "log_archive_start"                                                        
Bu zaman /tmp/x.txt den hash_join_enabled ve log_archive_start parametreleri chixardilir, session_max_open_files ve streams_pool_size parametreleri  yuxarida gosterilen shekilde bashqalarida elave edilir.                                                                              
Butun her shey bitdikden sonra Oracle 10g profili ile sisteme baglanilir,ashagdaki ishlemler edilir ve daha sonra $ORACLE_HOME/dbs locationunda yeni yaranan spfile kontrol edilir.
                sqlplus "/as sysdba"
                SQL> create spfile from pfile='/tmp/x.txt';
 6) Oracle password file 9i $ORACLE_HOME/dbs altindan 10g $ORACLE_HOME/dbs altina kopyalanir.
    cp -p /export/home0/oracle/ora92/product/9.2.0/dbs/orapwMISTEST /export/home0/oracle/ora102/product/10.2.0/dbs/
                ==> UPGRADE
                =======================================
                Goldengate baglanir..........
 1) Oracle 9i shutdown edilir.
                sqlplus "/as sysdba"
                SQL> shutdown immediate
 2) Oracle 10g profili ile sisteme baglanilir ve "STARTUP UPGRADE" komandasi chalishdirilir
                shell > cd /export/home0/oracle/ora102/product/10.2.0/rdbms/admin/
                shell > sqlplus "/as sysdba"
                SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 25 15:47:28 2010
                Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
                Connected to an idle instance.
                SQL> STARTUP UPGRADE;
 3) Upgrade  bashlamadan once SYSAUX tablespacesi yaradilir.
 CREATE TABLESPACE sysaux DATAFILE '/export/home0/oracle/oradata/MISTEST/sysaux01.dbf'
 SIZE 1000M REUSE
 EXTENT MANAGEMENT LOCAL
 SEGMENT SPACE MANAGEMENT AUTO
 ONLINE;
 4) Daha sonra sirali shekilde ashagdakikomandalar chalishdirilir.
 shell > sqlplus "/as sysdba"
 SQL> SPOOL upgrade.log
 SQL> @catupgrd.sql ( !!!! uzunca bir süre beklenir. )
 5) Upgrade sagliqli shekilde tamamlandiqdan sonra neticeni goremk uchu
 SQL> @utlu102s.sql
 SQL> SPOOL OFF
 SQL> SHUTDOWN IMMEDIATE
 SQL> STARTUP
 6) Butun invalid objeleri compile etmek uchun ashagdaki procedure chalishdirilir ve butunobjelerin ve paketlerin  valid oldugundan emin oluruq.
 SQL> @utlrp.sql
 SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
 SQL> SELECT distinct object_name FROM dba_objects WHERE status='INVALID';
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: