소스 보기

업그레이드 작업

preupgrade_fixups 스크립트 수행

SET ECHO ON;
SET SERVEROUTPUT ON;
@/u01/app/oracle/cfgtoollogs/racdb/preupgrade/preupgrade_fixups.sql

11g 데이터베이스 중지

shutdown immediate

재사용 파일 복제 및 신규 생성

export DB_HOME=$ORACLE_BASE/product/19.0.0/db_1
export ORACLE_HOME=$DB_HOME
cp $ORACLE_BASE/product/11.2.0/db_1/network/admin/*.ora $DB_HOME/network/admin/
cp $ORACLE_BASE/product/11.2.0/db_1/dbs/initracdb1.ora $DB_HOME/dbs/
orapwd file=$ORACLE_HOME/dbs/orapwracdb1 force=y format=12

export DB_HOME=$ORACLE_BASE/product/19.0.0/db_1
export ORACLE_HOME=$DB_HOME
cp $ORACLE_BASE/product/11.2.0/db_1/network/admin/*.ora $DB_HOME/network/admin/
cp $ORACLE_BASE/product/11.2.0/db_1/dbs/initracdb2.ora $DB_HOME/dbs/
orapwd file=$ORACLE_HOME/dbs/orapwracdb2 force=y format=12

싱글 DB로 전환 및 Upgrade 모드 기동

conn / as sysdba
startup nomount
alter system set cluster_database=false scope=spfile;
startup force upgrade

업그레이드 수행

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/db_1
$ORACLE_HOME/bin/dbupgrade

Time Zone 업그레이드 수행

startup upgrade;
select * from V$TIMEZONE_FILE;
select DBMS_DST.GET_LATEST_TIMEZONE_VERSION from dual;

SET SERVEROUTPUT ON;
exec DBMS_DST.BEGIN_PREPARE(DBMS_DST.GET_LATEST_TIMEZONE_VERSION);
exec DBMS_DST.FIND_AFFECTED_TABLES;
select * from SYS.DST$AFFECTED_TABLES;
select * from SYS.DST$ERROR_TABLE;
select * from SYS.DST$TRIGGER_TABLE;
exec DBMS_DST.END_PREPARE;

exec DBMS_DST.BEGIN_UPGRADE(DBMS_DST.GET_LATEST_TIMEZONE_VERSION);
shutdown immediate
startup

SET SERVEROUTPUT ON;
DECLARE
  failed_num PLS_INTEGER;
BEGIN
  DBMS_DST.UPGRADE_DATABASE(failed_num);
  DBMS_OUTPUT.PUT_LINE('DBMS_DST.UPGRADE_DATABASE : ' || failed_num);
END;
/

SET SERVEROUTPUT ON;
DECLARE
  failed_num PLS_INTEGER;
BEGIN
  DBMS_DST.END_UPGRADE(failed_num);
  DBMS_OUTPUT.PUT_LINE('DBMS_DST.END_UPGRADE : ' || failed_num);
END;
/

select PROPERTY_NAME, PROPERTY_VALUE
  from DATABASE_PROPERTIES
 where PROPERTY_NAME like 'DST_%'
 order by PROPERTY_NAME;

Fixed Objects 통계 수집

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

postupgrade_fixups 스크립트 수행

SET ECHO ON;
SET SERVEROUTPUT ON;
@/u01/app/oracle/cfgtoollogs/racdb/preupgrade/postupgrade_fixups.sql

객체 재컴파일

@?/rdbms/admin/utlrp.sql

클러스터 DB로 전환

alter system set cluster_database=true scope=spfile;
shutdown immediate

$DB_HOME/bin/srvctl upgrade database -d racdb -o $DB_HOME
srvctl start database -d racdb

APEX 설치 (옵션)

@?/apex/catapx.sql <APEX 관리자 암호> SYSAUX SYSAUX TEMP /i/ NONE