To be a Oracle architect. RUN~!!

SQL> select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;  

COMP_NAME     STATUS     VERSION---------------------------------------- ---------- ----------------------------------------JServer JAVA Virtual Machine   VALID     10.2.0.4.0OLAP Analytic Workspace    VALID     10.2.0.4.0OLAP Catalog     VALID     10.2.0.4.0Oracle Data Mining    VALID     10.2.0.4.0Oracle Database Catalog Views   VALID     10.2.0.4.0Oracle Database Java Packages   VALID     10.2.0.4.0Oracle Database Packages and Types  VALID     10.2.0.4.0Oracle Enterprise Manager   VALID     10.2.0.4.0Oracle Expression Filter   VALID     10.2.0.4.0Oracle OLAP API     VALID     10.2.0.4.0Oracle Rules Manager    VALID     10.2.0.4.0Oracle Text     VALID     10.2.0.4.0Oracle Workspace Manager   VALID     10.2.0.4.3Oracle XDK     VALID     10.2.0.4.0Oracle XML Database    VALID     10.2.0.4.0Oracle interMedia    VALID     10.2.0.4.0Spatial      VALID     10.2.0.4.0

17 rows selected.

SQL> select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects

         where status='INVALID' order by owner,object_type;

no rows selected

SQL> select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects

         where status='INVALID' order by owner,object_type;

no rows selected

invalid object가 있다면

$ cd $ORACLE_HOME/rdbms/admin$ sqlplus "/ as sysdba"SQL> @utlrp.sql  

-- (NLS_NCHAR_CHARACTERSET) 이  UTF8,  AL16UTF16 인지 확인

SQL> select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

VALUE--------------AL16UTF16

SQL> select * from v$timezone_file; FILENAME      VERSION------------------------ ----------timezlrg.dat     4  

timestamp 데이터타입을 사용하고 있다면 DB 업그레이드후에 다음패키지로 data를 upgrade 해줘야 한다.

• DBMS_DST.FIND_AFFECTED_TABLES• DBMS_DST.BEGIN_UPGRADE• DBMS_DST.UPGRADE_DATABASE• DBMS_DST.END_UPGRADE 

version 11이상이라면 업그레이드전 패치 필요(메타링크)

Database vault 를 사용중이라면 다음 명령어로 DISALBE 시켜준다

$ dvca -action disable 

10.1, 9.2 버전에서 11g로 업그레이드는 connect role에 대해 create session 권한만 갖게 되므로 user 체크후 다음과 같은 권한을 따로 부여해주던지 role 을 만들어 부여해줘야 하겠다.

9.2.x , 10.1.x CONNECT role 의 권한

SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE ='CONNECT';

GRANTEE PRIVILEGE------- ----------------------CONNECT CREATE VIEWCONNECT CREATE TABLECONNECT ALTER SESSIONCONNECT CREATE CLUSTERCONNECT CREATE SESSIONCONNECT CREATE SYNONYMCONNECT CREATE SEQUENCECONNECT CREATE DATABASE LINK 

10.2는 오직 create session 뿐이므로 조치 필요없음

혹시나 connect role에 다른 권한을 부여하였다면 업그레이드시 초기화 되므로 다른 role에 지정해줘야 한다.

다음 쿼리로 connect role을 가진 user 조회

SQL> SELECT grantee FROM dba_role_privsWHERE granted_role = 'CONNECT' andgrantee NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP','LOGSTDBY_ADMINISTRATOR', 'ORDSYS','ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY','WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS','WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA','SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

$ sqlplus '/ as sysdba' SQL> spool upgrade_info.logSQL> @utlu112i.sqlSQL> spool off

1)On Linux or UNIX platforms, enter the following command at a system prompt in the Oracle Database 11g Release 21 (11.2) environment:

$ dbua

Note: The dbua executable is usually located in the ORACLE_HOME/bin directory

2) On Windows operating systems, select

Start >Programs >Oracle - HOME_NAME >Configuration and Migration Tools >Database Upgrade Assistant.

The DBUA Welcome screen appears.