12c OCP upgrade done and dusted!
About 80 questions, about 50 questions new version and about 30 questions are general, all versions related.
There are just few confusing questions, most of remaining are quite clear.
Another number to the ever growing list of versions. OCP 12c 11g 10g 9i
Let’s have a look at what part of oracle upgrade process takes most of the time. This is test environment with very simple database created from the oltp template.
Notice that Oracle server takes 22 minutes to upgrade and APEX part takes 54.59 minutes! We don’t want to waste almost 1h of production downtime window.
It would be nice to switch this part completely off, the most fastest way to do something is to not to do it at all. However I didn’t find a supported way to skip this part yet.
The workaround is to install the newest version of APEX beforehand.
After instaling the APEX from here: http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html, the upgrade time looks much better. The APEX part completely been skipped.
Starting from 12c, the upgrade script catctl.pl runs in parallel. We can use -n option to adjust the number of threads of the process. It looks like that by default it takes the number of cpu available.
<br />Analyzing file catupgrd.sql Log files in /u01/app/oracle/product/12.1.0.2/rdbms/admin catcon: ALL catcon-related output will be written to catupgrd_catcon_4747.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
Setting high value could produce more contention that benefit. This is something we need to test before production. To set the number of threads, -n option is used. In this example I use 1 thread only.
<br />$ORACLE_HOME/perl/bin/perl catctl.pl -n 1 catupgrd.sql Argument list for [catctl.pl] SQL Process Count n = 1 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/rdbms/admin catcon: ALL catcon-related output will be written to catupgrd_catcon_6258.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 = 1
I am testing the 12c upgrades for a half a year now and I have done a lot of 10g to 11g upgrades in the past. How much better the upgrade process is comparing with older versions?
My general impression is the whole process got much better.
<br />Analyzing file catupgrd.sql Log files in /u01/app/oracle/product/12.1.0.2/rdbms/admin catcon: ALL catcon-related output will be written to catupgrd_catcon_4747.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
<br />====>> PRE-UPGRADE RESULTS for UPGR <<==== ACTIONS REQUIRED: 1. Review results of the pre-upgrade checks: /u01/app/oracle/cfgtoollogs/UPGR/preupgrade/preupgrade.log 2. Execute in the SOURCE environment BEFORE upgrade: /u01/app/oracle/cfgtoollogs/UPGR/preupgrade/preupgrade_fixups.sql 3. Execute in the NEW environment AFTER upgrade: /u01/app/oracle/cfgtoollogs/UPGR/preupgrade/postupgrade_fixups.sql
<br />------------------------------------------------------ Phases [0-73] Start Time:[2016_10_30 16:36:05] ------------------------------------------------------ Serial Phase #: 0 Files: 1 Time: 252s Serial Phase #: 1 Files: 5 Time: 139s Restart Phase #: 2 Files: 1 Time: 0s Parallel Phase #: 3 Files: 18 Time: 29s Restart Phase #: 4 Files: 1 Time: 1s Serial Phase #: 5 Files: 5 Time: 46s
DST_prepare.sql DST_adjust.sql
The great option for those who want to get into 12c upgrade as fast as possible is to look at Oracle provided virtual box environment with pre-configured linux os and 11g and 12c installations.
The virtual machine could be obtained from
http://www.oracle.com/technetwork/community/developer-vm/vts-hol-2415742.html
There are 8 zip files with total size of 15GB required to be downloaded from the internet.
You must have 30GB free space to store and unzip them, and about 75GB of storage for virtual machine disks.
The 4 tasks could be performed here:
Thanks to Roy Swonger and Mike Dietrich from Oracle for a great work!
Installing the oracle database software could bring you the following error:
./runInstaller: /u01/insta/database/install/.oui: /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory
Which indicates that 32bit version of libs are missing. At least two possible reasons for that, some 32 bit libs are really missing and we could start digging into the cause or ask your self a question, what we are trying to achieve here? Are we trying to install 32bit software on 64bit linux by mistake?
The 64bit software distribution will have 64 in the name of the zip, like:
p10404530_112030_Linux-x86-64_3of7
If there is NO 64 in the naming, then most likely you are installing 32 bit on 64 bit linux.
Even if you want just HR schema then you need to download examples distribution about 800MB. Then you have to unzip it on the server and install using GUI to the oracle home into demo directory , this will consume some space and will contain bunch of other schemas too. Only after that its possible to deploy HR schema.
This process is cumbersome and time consuming if only HR schema is required. For convenience, I zipped just the HR scripts (17KB) and this could be obtained from here. To install run hr_main as usual.
note: by downloading you accept the oracle examples terms and conditions, scripts obtained this way are not supported, proceed on you own risk
The common misconception I observe is how the block change tracking file (BCTF) works. One of oracle published books on backup and recovery says:
“The Block Change Tracking File . By default, when doing an incremental backup, any datafile that has changed in any way will be backed up. This can make incremental backups take longer and will make them larger. RMAN offers the ability to just back up changed database blocks. This can make your incremental database backups much smaller and shorter. ” (Freeman, Hart – Oracle RMAN 11g Backup and Recovery)
This stating that the whole datafile will be backed up, which is not very correct. The incremental backup works on block level disregarding BCTF.
Lets do a test:
The environment is one big table called T1 with 6.5 million of records (6 499 987) and about 7GB of space.
1. Lets update fraction of records and do incremental backup without change tracking
~~~~
SELECT STATUS, FILENAME
FROM V$BLOCK_CHANGE_TRACKING;
STATUS FILENAME
———- ——————————————
DISABLED
update t1 set t='TEST1' where c>100000 and c<101000;
12987 rows updated.
commit;
Commit complete.
~~~~
2. Lets update fraction of records again and do incremental backup with change tracking
~~~~
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/oracle/oradata/fast_recovery_area/ED12/chtf01.dbf' REUSE;
Database altered.
SQL> update t1 set t='TEST1' where c>100000 and c commit;
Commit complete.
~~~~
RMAN> backup incremental level 0 database;
Starting backup at 24-SEP-16
The typical way of fixing it is to set the NLS_DATE_FORMAT=’YY/MM/DD HH24:MI’
However it doesn’t work in RMAN, even version 12c:
RMAN> SQL ‘ALTER SESSION SET NLS_DATE_FORMAT=”DD.MM.YYYY HH24:MI:SS”‘;
sql statement: ALTER SESSION SET NLS_DATE_FORMAT=”DD.MM.YYYY HH24:MI:SS”
RMAN> backup incremental level 0 database;
Starting backup at 24-SEP-16
The workaround is:
1. Set the NLS_DATE_FORMAT variable for the os shell
export NLS_DATE_FORMAT=’YY/MM/DD HH24:MI’
rman target /
RMAN>
Starting backup at 16/09/24 16:39
2. Or in the scripts, use host command to produce the timestamp
RMAN> run {
2> host ‘date’;
3> backup incremental level 0 database;
4> host ‘date’;
5> }
Sat Sep 24 16:09:57 BST 2016
host command complete
Starting backup at 24-SEP-16
[oracle@single1 ~]$ srvctl add -h
The SRVCTL add command adds the configuration and the Oracle Restart application to the OCR for the cluster database, named instances, named services, or for the named nodes.
Usage: srvctl add database -db -oraclehome [-domain ] [-spfile ] [-pwfile ] [-role {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY | FAR_SYNC}] [-startoption ] [-stopoption ] [-dbname ] [-instance ] [-policy {AUTOMATIC | MANUAL | NORESTART}] [-diskgroup “”]
srvctl add database -db ED12 -oraclehome /u01/oracle/product/12.1.0/dbhome_1 -spfile ‘/u01/oracle/product/12.1.0/dbhome_1/dbs/spfileED12.ora’ -policy AUTOMATIC -startoption OPEN -stopoption IMMEDIATE
[oracle@single1 ~]$ srvctl status database -d ED12
Database is not running.
[oracle@single1 ~]$ srvctl start database -d ED12
[oracle@single1 ~]$ ps -ef | grep pmon
oracle 2737 1 0 12:10 ? 00:00:00 asm_pmon_+ASM
oracle 3140 1 0 12:27 ? 00:00:00 ora_pmon_ED12
oracle 3351 2906 0 12:27 pts/0 00:00:00 grep pmon
[oracle@single1 ~]$ srvctl stop database -d ED12
[oracle@single1 ~]$ srvctl remove database -d Ed12 -f
[oracle@single1 ~]$ srvctl status database -d Ed12
PRCD-1120 : The resource for database Ed12 could not be found.
PRCR-1001 : Resource ora.ed12.db does not exist
[oracle@single1 ~]$ srvctl add database -d ED12 -o /u01/oracle/product/12.1.0/dbhome_1 -p ‘/u01/oracle/product/12.1.0/dbhome_1/dbs/spfileED12.ora’ -y AUTOMATIC -s OPEN -t IMMEDIATE
[oracle@single1 ~]$ srvctl start database -d ED12
[oracle@single1 ~]$ ps -ef | grep pmon
oracle 2711 1 0 12:31 ? 00:00:00 asm_pmon_+ASM
oracle 3360 1 0 12:36 ? 00:00:00 ora_pmon_ED12
oracle 3773 2558 0 12:44 pts/0 00:00:00 grep pmon
erudans.info 