Oracle Migration UPGRADE 11.2.0.4 TO 12.1.0.2 Manual(FIlesystem)

 

Oracle Migration UPGRADE 11.2.0.4 TO 12.1.0.2 Manual(FIlesystem)

 

	AS-IS	TO-BE
VM	Hyper-V	Hyper-V
OS	Oracle Linux 7.8	Oracle Linux 7.8
DB	11.2.0.4	12.1.0.2
DISK	FIlesystem	FIlesystem
Oracle SID	orcl	Orcl
Hostname	11g	12c

 

#테스트 진행

# TO-BE 오라클 12.1.0.2 엔진 설치 (사전 구성 완료) l  AS-IS , TO-BE 동일한 UID GID , 오라클 설치 경로 ,  오라클 SID 및 환경 변수를 동일하게 구성합니다. # Source 11.2.0.3 # Source DB Down # Oracle File SCP -> TB-BE Oracle 12c (오라클 관련 파일 복사) # Oracle 12C UPGRADE(DBUA) Manual # Oracle 12C OPEN

 

#AS-IS(11.2.0.4) 기본정보

$ id uid=1001(oracle) gid=5001(oinstall) groups=5001(oinstall),5000(dba),5002(oper)

$ORACLE_BASE : /u01/app/oracle

$ORACLE_HOME : /u01/app/oracle/product/11.2.0/db_1/

 

#TO-BE(12.1.0.2) 기본정보

$ id uid=1001(oracle) gid=5001(oinstall) groups=5001(oinstall),5000(dba),5002(oper)

$ORACLE_BASE : /u01/app/oracle

$ORACLE_HOME : /u01/app/oracle/product/12.1.0.2/db_1

 

#AS-IS(11.2.0.4) DB Down

SQL> shutdown immediate

 

#TO-BE 디렉토리 생성

mkdir -p /u01/app/oracle/fast_recovery_area/orcl/

 

#AS-IS(11.2.0.4) TO #TO-BE(12.1.0.2) SCP

AS-IS 오라클 데이터 파일, 파라미터 파일, 패스워드 파일, 리스너 파일 TO-BE 동일한 경로 복사합니다.

#Parameter File Password SCP

scp -rp /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora oracle@192.168.56.102:/u01/app/oracle/product/12.1.0.2/db_1/dbs scp -rp /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora oracle@192.168.56.102:/u01/app/oracle/product/12.1.0.2/db_1/dbs scp -rp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl  oracle@192.168.56.102:/u01/app/oracle/product/12.1.0.2/db_1/dbs

#Controlfile SCP

scp -rp /u01/app/oracle/fast_recovery_area/orcl/control02.ctl oracle@192.168.56.102:/u01/app/oracle/fast_recovery_area/orcl/control02.ctl /u01/app/oracle/oradata/orcl/control01.ctl  /u01/app/oracle/fast_recovery_area/orcl/control02.ctl

#Oracle Datafile SCP

scp -rp /u01/app/oracle/oradata/orcl/*.dbf oracle@192.168.56.102:/u01/app/oracle/oradata/orcl/

scp -rp /u01/app/oracle/oradata/orcl/redo03.log oracle@192.168.56.102:/u01/app/oracle/oradata/orcl/ scp -rp /u01/app/oracle/oradata/orcl/redo02.log oracle@192.168.56.102:/u01/app/oracle/oradata/orcl/ scp -rp /u01/app/oracle/oradata/orcl/redo01.log oracle@192.168.56.102:/u01/app/oracle/oradata/orcl/

#Oracle Archive File SCP

scp -rp /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_08_09 oracle@192.168.56.102:/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_08_09

 

#TO-BE(12.1.0.2) STARTUP UPGRADE

SQL> startup upgrade

 

#TO-BE(12.1.0.2) catctl.pl catupgrd.sql

[oracle@12c admin]$ /u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl catctl.pl catupgrd.sql

Argument list for [catctl.pl] SQL Process Count     n = 0 SQL PDB Process Count N = 0 Input Directory       d = 0 Phase Logging Table   t = 0 Log Dir               l = 0 Script                s = 0 Serial Run            S = 0 Upgrade Mode active   M = 0 Start Phase           p = 0 End Phase             P = 0 Log Id                i = 0 Run in                c = 0 Do not run in         C = 0 Echo OFF              e = 1 No Post Upgrade       x = 0 Reverse Order         r = 0 Open Mode Normal      o = 0 Debug catcon.pm       z = 0 Debug catctl.pl       Z = 0 Display Phases        y = 0 Child Process         I = 0   catctl.pl version: 12.1.0.2.0 Oracle Base           = /u01/app/oracle   Analyzing file catupgrd.sql Log files in /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin catcon: ALL catcon-related output will be written to catupgrd_catcon_22783.lst catcon: See catupgrd*.log files for output generated by scripts catcon: See catupgrd_*.lst files for spool files, if any Number of Cpus        = 4 SQL Process Count     = 0 New SQL Process Count = 4 ------------------------------------------------------ Phases [0-73] Serial   Phase #: 0 Files: 1     Time: 225s  Serial   Phase #: 1 Files: 5     Time: 63s   Restart  Phase #: 2 Files: 1     Time: 0s    Parallel Phase #: 3 Files: 18    Time: 14s   Restart  Phase #: 4 Files: 1     Time: 1s    Serial   Phase #: 5 Files: 5     Time: 27s   Serial   Phase #: 6 Files: 1     Time: 18s   Serial   Phase #: 7 Files: 4     Time: 13s   Restart  Phase #: 8 Files: 1     Time: 0s    Parallel Phase #: 9 Files: 62    Time: 73s   Restart  Phase #:10 Files: 1     Time: 0s    Serial   Phase #:11 Files: 1     Time: 20s   Restart  Phase #:12 Files: 1     Time: 0s    Parallel Phase #:13 Files: 91    Time: 12s   Restart  Phase #:14 Files: 1     Time: 0s    Parallel Phase #:15 Files: 111   Time: 26s   Restart  Phase #:16 Files: 1     Time: 0s    Serial   Phase #:17 Files: 3     Time: 1s    Restart  Phase #:18 Files: 1     Time: 0s    Parallel Phase #:19 Files: 32    Time: 32s   Restart  Phase #:20 Files: 1     Time: 0s    Serial   Phase #:21 Files: 3     Time: 9s    Restart  Phase #:22 Files: 1     Time: 0s    Parallel Phase #:23 Files: 23    Time: 139s  Restart  Phase #:24 Files: 1     Time: 0s    Parallel Phase #:25 Files: 11    Time: 70s   Restart  Phase #:26 Files: 1     Time: 0s    Serial   Phase #:27 Files: 1     Time: 1s    Restart  Phase #:28 Files: 1     Time: 0s    Serial   Phase #:30 Files: 1     Time: 0s    Serial   Phase #:31 Files: 257   Time: 25s   Serial   Phase #:32 Files: 1     Time: 0s    Restart  Phase #:33 Files: 1     Time: 0s    Serial   Phase #:34 Files: 1     Time: 7s    Restart  Phase #:35 Files: 1     Time: 0s    Restart  Phase #:36 Files: 1     Time: 0s    Serial   Phase #:37 Files: 4     Time: 83s   Restart  Phase #:38 Files: 1     Time: 0s    Parallel Phase #:39 Files: 13    Time: 90s   Restart  Phase #:40 Files: 1     Time: 1s    Parallel Phase #:41 Files: 10    Time: 12s   Restart  Phase #:42 Files: 1     Time: 0s    Serial   Phase #:43 Files: 1     Time: 10s   Restart  Phase #:44 Files: 1     Time: 0s    Serial   Phase #:45 Files: 1     Time: 7s    Serial   Phase #:46 Files: 1     Time: 1s    Restart  Phase #:47 Files: 1     Time: 0s    Serial   Phase #:48 Files: 1     Time: 147s  Restart  Phase #:49 Files: 1     Time: 0s    Serial   Phase #:50 Files: 1     Time: 68s   Restart  Phase #:51 Files: 1     Time: 0s    Serial   Phase #:52 Files: 1     Time: 1s    Restart  Phase #:53 Files: 1     Time: 0s    Serial   Phase #:54 Files: 1     Time: 715s  Restart  Phase #:55 Files: 1     Time: 0s    Serial   Phase #:56 Files: 1     Time: 99s   Restart  Phase #:57 Files: 1     Time: 0s    Serial   Phase #:58 Files: 1     Time: 907s  Restart  Phase #:59 Files: 1     Time: 0s    Serial   Phase #:60 Files: 1     Time: 1s    Restart  Phase #:61 Files: 1     Time: 0s    Serial   Phase #:62 Files: 1     Time: 2016s Restart  Phase #:63 Files: 1     Time: 1s    Serial   Phase #:64 Files: 1     Time: 1s    Serial   Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > catupgrd_datapatch_upgrade.log 2> catupgrd_datapatch_upgrade.err returned from sqlpatch     Time: 52s   Serial   Phase #:66 Files: 1     Time: 160s  Serial   Phase #:68 Files: 1     Time: 0s    Serial   Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > catupgrd_datapatch_normal.log 2> catupgrd_datapatch_normal.err returned from sqlpatch     Time: 37s   Serial   Phase #:70 Files: 1     Time: 292s  Serial   Phase #:71 Files: 1     Time: 0s    Serial   Phase #:72 Files: 1     Time: 0s    Serial   Phase #:73 Files: 1     Time: 22s    Grand Total Time: 5548s LOG FILES: (catupgrd*.log) Upgrade Summary Report Located in: /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/orcl/upgrade/upg_summary.log Grand Total Upgrade Time:    [0d:1h:32m:28s]

SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 9 20:02:59 2020   Copyright (c) 1982, 2014, Oracle.  All rights reserved.   Enter user-name: / as sysdba Connected to an idle instance.

SQL> startup upgrade

SQL> @utlrp.sql

TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN  2020-08-09 20:04:03   DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC>   objects in the database. Recompilation time is proportional to the DOC>   number of invalid objects in the database, so this command may take DOC>   a long time to execute on a database with a large number of invalid DOC>   objects. DOC> DOC>   Use the following queries to track recompilation progress: DOC> DOC>   1. Query returning the number of invalid objects remaining. This DOC>     number should decrease with time. DOC>        SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC>   2. Query returning the number of objects compiled so far. This number DOC>     should increase with time. DOC>        SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC>   This script automatically chooses serial or parallel recompilation DOC>   based on the number of CPUs available (parameter cpu_count) multiplied DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC>   On RAC, this number is added across all RAC nodes. DOC> DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC>   recompilation. Jobs are created without instance affinity so that they DOC>   can migrate across RAC nodes. Use the following queries to verify DOC>   whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC>   1. Query showing jobs created by UTL_RECOMP DOC>        SELECT job_name FROM dba_scheduler_jobs DOC>              WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC>   2. Query showing UTL_RECOMP jobs that are running DOC>        SELECT job_name FROM dba_scheduler_running_jobs DOC>              WHERE job_name like 'UTL_RECOMP_SLAVE_%';n PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END  2020-08-09 20:16:07 DOC> The following query reports the number of objects that have compiled DOC> with errors. DOC> DOC> If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS -------------------                         0 DOC> The following query reports the number of errors caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC>#   ERRORS DURING RECOMPILATION ---------------------------                                    0 Function created. PL/SQL procedure successfully completed. Function dropped. ...Database user "SYS", database schema "APEX_040200", user# "114" 20:16:15 ...Compiled 0 out of 3014 objects considered, 0 failed compilation 20:16:15 ...271 packages ...263 package bodies ...452 tables ...11 functions ...16 procedures ...3 sequences ...457 triggers ...1320 indexes ...211 views ...0 libraries ...6 types ...0 type bodies ...0 operators ...0 index types ...Begin key object existence check 20:16:15 ...Completed key object existence check 20:16:16 ...Setting DBMS Registry 20:16:16 ...Setting DBMS Registry Complete 20:16:16 ...Exiting validate 20:16:16 PL/SQL procedure successfully completed.

SQL> @utlu121s.sql

SQL> @catuppst.sql

SQL> @utlu121s.sql

 

SET LINE 999; select comp_name, status, substr(version, 1,10) as version from dba_registry; col comp_name for a60;

COMP_NAME                                                STATUS         VERSION ------------------------------------------------------------ ----------- ------------------- Oracle Application Express                                      VALID          4.2.5.00.0 OWB                                                                 VALID          11.2.0.4.0 OLAP Catalog                                                      OPTION OFF   11.2.0.4.0 Spatial                                                               OPTION OFF   11.2.0.4.0 Oracle Multimedia                                                VALID          12.1.0.2.0 Oracle XML Database                                            VALID          12.1.0.2.0 Oracle Text                                                          VALID          12.1.0.2.0 Oracle Workspace Manager                                     VALID          12.1.0.2.0 Oracle Database Catalog Views                                VALID          12.1.0.2.0 Oracle Database Packages and Types                         VALID          12.1.0.2.0 JServer JAVA Virtual Machine                                 VALID          12.1.0.2.0 Oracle XDK                                                         VALID          12.1.0.2.0 Oracle Database Java Packages                                VALID           12.1.0.2.0 OLAP Analytic Workspace                                    OPTION OFF  11.2.0.4.0 Oracle OLAP API                                                  OPTION OFF  11.2.0.4.0

 

#Compatible 변경

SQL> show parameter compatible NAME                                         TYPE        VALUE ------------------------------------ ----------- ------------------------------ compatible                                   string        11.2.0.0.0 noncdb_compatible                         boolean     FALSE

SQL> alter system set compatible='12.1.0.1.0.0'  scope=spfile;

SQL> select comp_name , version, status from dba_registry

SQL> shutdown immediate

 

#CLEAN

vi /etc/oratab

orcl:/u01/app/oracle/product/12.1.0.2/db_1:N

 

#TO-BE(12.1.0.2) STARTUP

SQL> startup

SQL> select instance_name , status from v$instance;