Source DB version- 11.2.0.4.0
Target DB version - 12.1.0.2.0
Steps involve:
1) Take Full RMAN backup with archivelog & current controlfile from source db.
2) Copy pfile and password file from source db to target db
3) Restore RMAN backup on Target DB
4) Upgrade Database
5) Recompile all invalid objects
Below article illustrates oracle 11g to 12c upgradation.
At 11G server::
[oracle@centos ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 11 11:40:38 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1219260416 bytes
Fixed Size 2252744 bytes
Variable Size 788529208 bytes
Database Buffers 419430400 bytes
Redo Buffers 9048064 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
INFOTECH ARCHIVELOG
SQL> !
[oracle@centos ~]$
[oracle@centos ~]$
[oracle@centos ~]$
[oracle@centos ~]$ echo $ORACLE_SID
infotech
[oracle@centos ~]$
[oracle@centos ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 11 11:42:37 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: INFOTECH (DBID=2795125410)
RMAN> backup database plus archivelog;
Starting backup at 11-OCT-18
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=48 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=34 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=980347021
input archived log thread=1 sequence=3 RECID=2 STAMP=980605846
input archived log thread=1 sequence=4 RECID=3 STAMP=981892692
input archived log thread=1 sequence=5 RECID=4 STAMP=981892692
input archived log thread=1 sequence=6 RECID=5 STAMP=981892694
channel ORA_DISK_1: starting piece 1 at 11-OCT-18
channel ORA_DISK_2: starting archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=6 STAMP=981892694
input archived log thread=1 sequence=8 RECID=7 STAMP=985457005
input archived log thread=1 sequence=9 RECID=8 STAMP=986401810
input archived log thread=1 sequence=10 RECID=9 STAMP=987086418
input archived log thread=1 sequence=11 RECID=10 STAMP=987189035
channel ORA_DISK_2: starting piece 1 at 11-OCT-18
channel ORA_DISK_3: starting archived log backup set
channel ORA_DISK_3: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=11 STAMP=987610731
input archived log thread=1 sequence=13 RECID=12 STAMP=988560278
input archived log thread=1 sequence=14 RECID=13 STAMP=989235792
channel ORA_DISK_3: starting piece 1 at 11-OCT-18
channel ORA_DISK_1: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_annnn_TAG20181011T114312_fvxths6z_.bkp tag=TAG20181011T114312 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_2: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_annnn_TAG20181011T114312_fvxthsob_.bkp tag=TAG20181011T114312 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_3: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_annnn_TAG20181011T114312_fvxtht28_.bkp tag=TAG20181011T114312 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:07
Finished backup at 11-OCT-18
Starting backup at 11-OCT-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/infotech/infotech/system01.dbf
input datafile file number=00004 name=/u01/infotech/infotech/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-OCT-18
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/infotech/infotech/sysaux01.dbf
input datafile file number=00003 name=/u01/infotech/infotech/undotbs01.dbf
channel ORA_DISK_2: starting piece 1 at 11-OCT-18
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_3: starting piece 1 at 11-OCT-18
channel ORA_DISK_3: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_ncnnf_TAG20181011T114320_fvxtj8fm_.bkp tag=TAG20181011T114320 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_3: starting piece 1 at 11-OCT-18
channel ORA_DISK_3: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_nnsnf_TAG20181011T114320_fvxtjbjl_.bkp tag=TAG20181011T114320 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_2: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_nnndf_TAG20181011T114320_fvxtj1p6_.bkp tag=TAG20181011T114320 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:04
channel ORA_DISK_1: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_nnndf_TAG20181011T114320_fvxtj1xo_.bkp tag=TAG20181011T114320 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:24
Finished backup at 11-OCT-18
Starting backup at 11-OCT-18
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=14 STAMP=989235885
channel ORA_DISK_1: starting piece 1 at 11-OCT-18
channel ORA_DISK_1: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_annnn_TAG20181011T114447_fvxtlr3n_.bkp tag=TAG20181011T114447 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-OCT-18
RMAN>
RMAN> backup current controlfile;
Starting backup at 11-OCT-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 11-OCT-18
channel ORA_DISK_1: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_ncnnf_TAG20181011T114628_fvxtox78_.bkp tag=TAG20181011T114628 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-OCT-18
RMAN> exit
Recovery Manager complete.
[oracle@centos rman]$
[oracle@centos rman]$ cd /u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/
[oracle@centos 2018_10_11]$ ls -ltr
total 1412592
-rw-r-----. 1 oracle oinstall 92031488 Oct 11 11:43 o1_mf_annnn_TAG20181011T114312_fvxths6z_.bkp
-rw-r-----. 1 oracle oinstall 99141632 Oct 11 11:43 o1_mf_annnn_TAG20181011T114312_fvxtht28_.bkp
-rw-r-----. 1 oracle oinstall 153950208 Oct 11 11:43 o1_mf_annnn_TAG20181011T114312_fvxthsob_.bkp
-rw-r-----. 1 oracle oinstall 9797632 Oct 11 11:43 o1_mf_ncnnf_TAG20181011T114320_fvxtj8fm_.bkp
-rw-r-----. 1 oracle oinstall 98304 Oct 11 11:43 o1_mf_nnsnf_TAG20181011T114320_fvxtjbjl_.bkp
-rw-r-----. 1 oracle oinstall 404103168 Oct 11 11:44 o1_mf_nnndf_TAG20181011T114320_fvxtj1p6_.bkp
-rw-r-----. 1 oracle oinstall 677560320 Oct 11 11:44 o1_mf_nnndf_TAG20181011T114320_fvxtj1xo_.bkp
-rw-r-----. 1 oracle oinstall 6144 Oct 11 11:44 o1_mf_annnn_TAG20181011T114447_fvxtlr3n_.bkp
-rw-r-----. 1 oracle oinstall 9797632 Oct 11 11:46 o1_mf_ncnnf_TAG20181011T114628_fvxtox78_.bkp
[oracle@centos 2018_10_11]$ cd ..
[oracle@centos backupset]$ ll
total 4
drwxr-x---. 2 oracle oinstall 4096 Oct 11 11:46 2018_10_11
[oracle@centos backupset]$ du -sh *
1.4G 2018_10_11
[oracle@centos backupset]$ scp * 10.20.30.10:/u01/app/oracle/fast_recovery_area/
[oracle@centos backupset]$ exit
exit
SQL>
SQL> create pfile from spfile;
File created.
SQL> !
cd $ORACLE_HOME
[oracle@centos ~]$
[oracle@centos ~]$ cd $ORACLE_HOME
[oracle@centos db_1]$
[oracle@centos db_1]$ cd dbs
[oracle@centos dbs]$
[oracle@centos dbs]$ ls -ltr
total 9552
-rw-r-----. 1 oracle oinstall 1536 Jun 28 15:48 orapwinfotech
-rw-r--r--. 1 oracle oinstall 1103 Oct 11 11:50 initinfotech.ora
[oracle@centos dbs]$
[oracle@centos dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@centos dbs]$ scp * 10.20.30.10:/u01/app/oracle/product/12.1.0/db_1/dbs
At 12c Server::
[oracle@alpha dbs]$ pwd
/u01/app/oracle/product/12.1.0/db_1/dbs
[oracle@alpha dbs]$
[oracle@alpha dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 11 12:07:24 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initinfotech.ora';
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 788529872 bytes
Database Buffers 419430400 bytes
Redo Buffers 13852672 bytes
SQL>
SQL>
SQL> create spfile from pfile;
File created.
SQL> !
[oracle@alpha dbs]$
[oracle@alpha dbs]$ cd /u01/app/oracle/fast_recovery_area
[oracle@alpha fast_recovery_area]$
[oracle@alpha fast_recovery_area]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Oct 11 12:12:10 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: INFOTECH (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/o1_mf_ncnnf_TAG20181011T114628_fvxtox78_.bkp';
Starting restore at 11-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/infotech/infotech/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/infotech/control02.ctl
Finished restore at 11-OCT-18
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
RMAN> catalog start with '/u01/app/oracle/fast_recovery_area/';
Starting implicit crosscheck backup at 11-OCT-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=24 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=25 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 11-OCT-18
Starting implicit crosscheck copy at 11-OCT-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
Finished implicit crosscheck copy at 11-OCT-18
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/app/oracle/fast_recovery_area/
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114312_fvxths6z_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114312_fvxthsob_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114312_fvxtht28_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114447_fvxtlr3n_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_ncnnf_TAG20181011T114320_fvxtj8fm_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_ncnnf_TAG20181011T114628_fvxtox78_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1p6_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1xo_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnsnf_TAG20181011T114320_fvxtjbjl_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114312_fvxths6z_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114312_fvxthsob_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114312_fvxtht28_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114447_fvxtlr3n_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_ncnnf_TAG20181011T114320_fvxtj8fm_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_ncnnf_TAG20181011T114628_fvxtox78_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1p6_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1xo_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnsnf_TAG20181011T114320_fvxtjbjl_.bkp
RMAN> restore database;
Starting restore at 11-OCT-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/infotech/infotech/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/infotech/infotech/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1p6_.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /u01/infotech/infotech/system01.dbf
channel ORA_DISK_2: restoring datafile 00004 to /u01/infotech/infotech/users01.dbf
channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1xo_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1p6_.bkp tag=TAG20181011T114320
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_DISK_2: piece handle=/u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1xo_.bkp tag=TAG20181011T114320
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:07
Finished restore at 11-OCT-18
RMAN>
RMAN> run{
2> set until sequence=16;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 11-OCT-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 11-OCT-18
RMAN> exit
Recovery Manager complete.
[oracle@alpha fast_recovery_area]$
[oracle@alpha fast_recovery_area]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 11 12:17:24 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier
Process ID: 1838
Session ID: 25 Serial number: 14007
SQL> SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@alpha fast_recovery_area]$
[oracle@alpha fast_recovery_area]$
[oracle@alpha fast_recovery_area]$
[oracle@alpha fast_recovery_area]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 11 12:18:08 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 788529872 bytes
Database Buffers 419430400 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> !
[oracle@alpha bin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@alpha admin]$
[oracle@alpha admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
Argument list for [catctl.pl]
SQL Process Count n = 4
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/db_1/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrd_catcon_1968.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus = 1
SQL Process Count = 4
------------------------------------------------------
Phases [0-73]
Serial Phase #: 0 Files: 1
Time: 1129s
Serial Phase #: 1 Files: 5 Time: 679s
Restart Phase #: 2 Files: 1 Time: 0s
Parallel Phase #: 3 Files: 18 Time: 159s
Restart Phase #: 4 Files: 1 Time: 0s
.
.
.
.
Serial Phase #:71 Files: 1 Time: 2s
Serial Phase #:72 Files: 1 Time: 0s
Serial Phase #:73 Files: 1 Time: 232s
Grand Total Time: 12579s
LOG FILES: (catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/infotech/upgrade/upg_summary.log
Grand Total Upgrade Time: [0d:3h:29m:39s]
[oracle@alpha admin]$
[oracle@alpha admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 11 16:03:13 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 922747600 bytes
Database Buffers 285212672 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL> SQL>
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2018-10-11 16:04:23
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_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2018-10-11 16:20:54
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# "116" 16:22:22
...Compiled 0 out of 3014 objects considered, 0 failed compilation 16:22:22
...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 16:22:22
...Completed key object existence check 16:22:22
...Setting DBMS Registry 16:22:22
...Setting DBMS Registry Complete 16:22:23
...Exiting validate 16:22:23
PL/SQL procedure successfully completed.
SQL> SQL> SQL> SQL>
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select comp_name,version from dba_registry where status = 'VALID';
COMP_NAME VERSION
---------------------------------------- ------------------------------
Oracle Application Express 4.2.5.00.08
Spatial 12.1.0.2.0
Oracle Multimedia 12.1.0.2.0
Oracle XML Database 12.1.0.2.0
Oracle Text 12.1.0.2.0
Oracle Workspace Manager 12.1.0.2.0
Oracle Database Catalog Views 12.1.0.2.0
Oracle Database Packages and Types 12.1.0.2.0
JServer JAVA Virtual Machine 12.1.0.2.0
Oracle XDK 12.1.0.2.0
Oracle Database Java Packages 12.1.0.2.0
OLAP Analytic Workspace 12.1.0.2.0
Oracle OLAP API 12.1.0.2.0
14 rows selected.
SQL>
SQL> select name,open_mode,log_mode, version from v$database,v$instance;
NAME OPEN_MODE LOG_MODE VERSION
--------- ---------------- ------------ -------------------
INFOTECH READ WRITE ARCHIVELOG 12.1.0.2.0
Target DB version - 12.1.0.2.0
Steps involve:
1) Take Full RMAN backup with archivelog & current controlfile from source db.
2) Copy pfile and password file from source db to target db
3) Restore RMAN backup on Target DB
4) Upgrade Database
5) Recompile all invalid objects
Below article illustrates oracle 11g to 12c upgradation.
At 11G server::
[oracle@centos ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 11 11:40:38 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1219260416 bytes
Fixed Size 2252744 bytes
Variable Size 788529208 bytes
Database Buffers 419430400 bytes
Redo Buffers 9048064 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
INFOTECH ARCHIVELOG
SQL> !
[oracle@centos ~]$
[oracle@centos ~]$
[oracle@centos ~]$
[oracle@centos ~]$ echo $ORACLE_SID
infotech
[oracle@centos ~]$
[oracle@centos ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 11 11:42:37 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: INFOTECH (DBID=2795125410)
RMAN> backup database plus archivelog;
Starting backup at 11-OCT-18
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=48 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=34 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=980347021
input archived log thread=1 sequence=3 RECID=2 STAMP=980605846
input archived log thread=1 sequence=4 RECID=3 STAMP=981892692
input archived log thread=1 sequence=5 RECID=4 STAMP=981892692
input archived log thread=1 sequence=6 RECID=5 STAMP=981892694
channel ORA_DISK_1: starting piece 1 at 11-OCT-18
channel ORA_DISK_2: starting archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=6 STAMP=981892694
input archived log thread=1 sequence=8 RECID=7 STAMP=985457005
input archived log thread=1 sequence=9 RECID=8 STAMP=986401810
input archived log thread=1 sequence=10 RECID=9 STAMP=987086418
input archived log thread=1 sequence=11 RECID=10 STAMP=987189035
channel ORA_DISK_2: starting piece 1 at 11-OCT-18
channel ORA_DISK_3: starting archived log backup set
channel ORA_DISK_3: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=11 STAMP=987610731
input archived log thread=1 sequence=13 RECID=12 STAMP=988560278
input archived log thread=1 sequence=14 RECID=13 STAMP=989235792
channel ORA_DISK_3: starting piece 1 at 11-OCT-18
channel ORA_DISK_1: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_annnn_TAG20181011T114312_fvxths6z_.bkp tag=TAG20181011T114312 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_2: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_annnn_TAG20181011T114312_fvxthsob_.bkp tag=TAG20181011T114312 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_3: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_annnn_TAG20181011T114312_fvxtht28_.bkp tag=TAG20181011T114312 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:07
Finished backup at 11-OCT-18
Starting backup at 11-OCT-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/infotech/infotech/system01.dbf
input datafile file number=00004 name=/u01/infotech/infotech/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-OCT-18
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/infotech/infotech/sysaux01.dbf
input datafile file number=00003 name=/u01/infotech/infotech/undotbs01.dbf
channel ORA_DISK_2: starting piece 1 at 11-OCT-18
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_3: starting piece 1 at 11-OCT-18
channel ORA_DISK_3: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_ncnnf_TAG20181011T114320_fvxtj8fm_.bkp tag=TAG20181011T114320 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_3: starting piece 1 at 11-OCT-18
channel ORA_DISK_3: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_nnsnf_TAG20181011T114320_fvxtjbjl_.bkp tag=TAG20181011T114320 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_2: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_nnndf_TAG20181011T114320_fvxtj1p6_.bkp tag=TAG20181011T114320 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:04
channel ORA_DISK_1: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_nnndf_TAG20181011T114320_fvxtj1xo_.bkp tag=TAG20181011T114320 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:24
Finished backup at 11-OCT-18
Starting backup at 11-OCT-18
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=14 STAMP=989235885
channel ORA_DISK_1: starting piece 1 at 11-OCT-18
channel ORA_DISK_1: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_annnn_TAG20181011T114447_fvxtlr3n_.bkp tag=TAG20181011T114447 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-OCT-18
RMAN>
RMAN> backup current controlfile;
Starting backup at 11-OCT-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 11-OCT-18
channel ORA_DISK_1: finished piece 1 at 11-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/o1_mf_ncnnf_TAG20181011T114628_fvxtox78_.bkp tag=TAG20181011T114628 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-OCT-18
RMAN> exit
Recovery Manager complete.
[oracle@centos rman]$
[oracle@centos rman]$ cd /u01/app/oracle/fast_recovery_area/INFOTECH/backupset/2018_10_11/
[oracle@centos 2018_10_11]$ ls -ltr
total 1412592
-rw-r-----. 1 oracle oinstall 92031488 Oct 11 11:43 o1_mf_annnn_TAG20181011T114312_fvxths6z_.bkp
-rw-r-----. 1 oracle oinstall 99141632 Oct 11 11:43 o1_mf_annnn_TAG20181011T114312_fvxtht28_.bkp
-rw-r-----. 1 oracle oinstall 153950208 Oct 11 11:43 o1_mf_annnn_TAG20181011T114312_fvxthsob_.bkp
-rw-r-----. 1 oracle oinstall 9797632 Oct 11 11:43 o1_mf_ncnnf_TAG20181011T114320_fvxtj8fm_.bkp
-rw-r-----. 1 oracle oinstall 98304 Oct 11 11:43 o1_mf_nnsnf_TAG20181011T114320_fvxtjbjl_.bkp
-rw-r-----. 1 oracle oinstall 404103168 Oct 11 11:44 o1_mf_nnndf_TAG20181011T114320_fvxtj1p6_.bkp
-rw-r-----. 1 oracle oinstall 677560320 Oct 11 11:44 o1_mf_nnndf_TAG20181011T114320_fvxtj1xo_.bkp
-rw-r-----. 1 oracle oinstall 6144 Oct 11 11:44 o1_mf_annnn_TAG20181011T114447_fvxtlr3n_.bkp
-rw-r-----. 1 oracle oinstall 9797632 Oct 11 11:46 o1_mf_ncnnf_TAG20181011T114628_fvxtox78_.bkp
[oracle@centos 2018_10_11]$ cd ..
[oracle@centos backupset]$ ll
total 4
drwxr-x---. 2 oracle oinstall 4096 Oct 11 11:46 2018_10_11
[oracle@centos backupset]$ du -sh *
1.4G 2018_10_11
[oracle@centos backupset]$ scp * 10.20.30.10:/u01/app/oracle/fast_recovery_area/
[oracle@centos backupset]$ exit
exit
SQL>
SQL> create pfile from spfile;
File created.
SQL> !
cd $ORACLE_HOME
[oracle@centos ~]$
[oracle@centos ~]$ cd $ORACLE_HOME
[oracle@centos db_1]$
[oracle@centos db_1]$ cd dbs
[oracle@centos dbs]$
[oracle@centos dbs]$ ls -ltr
total 9552
-rw-r-----. 1 oracle oinstall 1536 Jun 28 15:48 orapwinfotech
-rw-r--r--. 1 oracle oinstall 1103 Oct 11 11:50 initinfotech.ora
[oracle@centos dbs]$
[oracle@centos dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@centos dbs]$ scp * 10.20.30.10:/u01/app/oracle/product/12.1.0/db_1/dbs
At 12c Server::
[oracle@alpha dbs]$ pwd
/u01/app/oracle/product/12.1.0/db_1/dbs
[oracle@alpha dbs]$
[oracle@alpha dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 11 12:07:24 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initinfotech.ora';
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 788529872 bytes
Database Buffers 419430400 bytes
Redo Buffers 13852672 bytes
SQL>
SQL>
SQL> create spfile from pfile;
File created.
SQL> !
[oracle@alpha dbs]$
[oracle@alpha dbs]$ cd /u01/app/oracle/fast_recovery_area
[oracle@alpha fast_recovery_area]$
[oracle@alpha fast_recovery_area]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Oct 11 12:12:10 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: INFOTECH (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/o1_mf_ncnnf_TAG20181011T114628_fvxtox78_.bkp';
Starting restore at 11-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/infotech/infotech/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/infotech/control02.ctl
Finished restore at 11-OCT-18
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
RMAN> catalog start with '/u01/app/oracle/fast_recovery_area/';
Starting implicit crosscheck backup at 11-OCT-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=24 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=25 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 11-OCT-18
Starting implicit crosscheck copy at 11-OCT-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
Finished implicit crosscheck copy at 11-OCT-18
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/app/oracle/fast_recovery_area/
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114312_fvxths6z_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114312_fvxthsob_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114312_fvxtht28_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114447_fvxtlr3n_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_ncnnf_TAG20181011T114320_fvxtj8fm_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_ncnnf_TAG20181011T114628_fvxtox78_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1p6_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1xo_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnsnf_TAG20181011T114320_fvxtjbjl_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114312_fvxths6z_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114312_fvxthsob_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114312_fvxtht28_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_annnn_TAG20181011T114447_fvxtlr3n_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_ncnnf_TAG20181011T114320_fvxtj8fm_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_ncnnf_TAG20181011T114628_fvxtox78_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1p6_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1xo_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnsnf_TAG20181011T114320_fvxtjbjl_.bkp
RMAN> restore database;
Starting restore at 11-OCT-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/infotech/infotech/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/infotech/infotech/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1p6_.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /u01/infotech/infotech/system01.dbf
channel ORA_DISK_2: restoring datafile 00004 to /u01/infotech/infotech/users01.dbf
channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1xo_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1p6_.bkp tag=TAG20181011T114320
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_DISK_2: piece handle=/u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20181011T114320_fvxtj1xo_.bkp tag=TAG20181011T114320
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:07
Finished restore at 11-OCT-18
RMAN>
RMAN> run{
2> set until sequence=16;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 11-OCT-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 11-OCT-18
RMAN> exit
Recovery Manager complete.
[oracle@alpha fast_recovery_area]$
[oracle@alpha fast_recovery_area]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 11 12:17:24 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier
Process ID: 1838
Session ID: 25 Serial number: 14007
SQL> SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@alpha fast_recovery_area]$
[oracle@alpha fast_recovery_area]$
[oracle@alpha fast_recovery_area]$
[oracle@alpha fast_recovery_area]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 11 12:18:08 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 788529872 bytes
Database Buffers 419430400 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> !
[oracle@alpha bin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@alpha admin]$
[oracle@alpha admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
Argument list for [catctl.pl]
SQL Process Count n = 4
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/db_1/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrd_catcon_1968.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus = 1
SQL Process Count = 4
------------------------------------------------------
Phases [0-73]
Serial Phase #: 0 Files: 1
Time: 1129s
Serial Phase #: 1 Files: 5 Time: 679s
Restart Phase #: 2 Files: 1 Time: 0s
Parallel Phase #: 3 Files: 18 Time: 159s
Restart Phase #: 4 Files: 1 Time: 0s
.
.
.
.
Serial Phase #:71 Files: 1 Time: 2s
Serial Phase #:72 Files: 1 Time: 0s
Serial Phase #:73 Files: 1 Time: 232s
Grand Total Time: 12579s
LOG FILES: (catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/infotech/upgrade/upg_summary.log
Grand Total Upgrade Time: [0d:3h:29m:39s]
[oracle@alpha admin]$
[oracle@alpha admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 11 16:03:13 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 922747600 bytes
Database Buffers 285212672 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL> SQL>
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2018-10-11 16:04:23
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_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2018-10-11 16:20:54
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# "116" 16:22:22
...Compiled 0 out of 3014 objects considered, 0 failed compilation 16:22:22
...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 16:22:22
...Completed key object existence check 16:22:22
...Setting DBMS Registry 16:22:22
...Setting DBMS Registry Complete 16:22:23
...Exiting validate 16:22:23
PL/SQL procedure successfully completed.
SQL> SQL> SQL> SQL>
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select comp_name,version from dba_registry where status = 'VALID';
COMP_NAME VERSION
---------------------------------------- ------------------------------
Oracle Application Express 4.2.5.00.08
Spatial 12.1.0.2.0
Oracle Multimedia 12.1.0.2.0
Oracle XML Database 12.1.0.2.0
Oracle Text 12.1.0.2.0
Oracle Workspace Manager 12.1.0.2.0
Oracle Database Catalog Views 12.1.0.2.0
Oracle Database Packages and Types 12.1.0.2.0
JServer JAVA Virtual Machine 12.1.0.2.0
Oracle XDK 12.1.0.2.0
Oracle Database Java Packages 12.1.0.2.0
OLAP Analytic Workspace 12.1.0.2.0
Oracle OLAP API 12.1.0.2.0
14 rows selected.
SQL>
SQL> select name,open_mode,log_mode, version from v$database,v$instance;
NAME OPEN_MODE LOG_MODE VERSION
--------- ---------------- ------------ -------------------
INFOTECH READ WRITE ARCHIVELOG 12.1.0.2.0
No comments:
Post a Comment