Upgrade to Oracle 12.1.0.2 problems

I was recently tasked with upgrading  an Oracle RDBMS 11.2.0.4 to 12.1.0.2. The platform is Windows 2012 R2. I know about not being able to move the listener to the new 12c home, which prevents me from using the upgrade utility DBUA.  However, this time there was a nasty surprise in store for me. I ran all the pre-upgrade steps and done all the pre-upgrade fixes and it was time to run catctl.pl. Here is what happened:

C:\app\oracle\product\12.1.0\dbhome_1\RDBMS\ADMIN>%ORACLE_HOME%\perl\bin\perl catctl.pl catupgrd.sql
Undefined subroutine &Tie::Hash::NamedCapture::flags called at C:\app\oracle\age
nt12c\core\12.1.0.5.0\perl\lib/Tie/Hash/NamedCapture.pm line 9.
Compilation failed in require at C:\app\oracle\agent12c\core\12.1.0.5.0\perl\lib/English.pm line 148.
BEGIN failed--compilation aborted at C:\app\oracle\agent12c\core\12.1.0.5.0\perl
\lib/English.pm line 148.
Compilation failed in require at catctl.pl line 322.
BEGIN failed--compilation aborted at catctl.pl line 322.

The line 322 of catctl.pl is “use English;”, a normal and very frequently used Perl directive. Fortunately, I am not completely unfamiliar with Perl, so I was able to figure out the problem. The problem was in OEM:

C:\app\oracle\product\11.2.0\db_1\NETWORK\ADMIN>echo %PERL5LIB%
C:\app\oracle\agent12c\core\12.1.0.5.0\perl\lib;C:\app\oracle\agent12c\core\12.1
.0.5.0\perl\site\lib;C:\app\oracle\agent12c\core\12.1.0.5.0\sysman\admin\scripts

The variable PERL5LIB tells the Perl interpreter where to look for modules. This variable is obviously set on the system-wide basis and is set to OEM 12c directories.  Apparently, there is something in the OEM version of the English.pm module that Perl that comes in Oracle 12c home didn’t like. As soon as I’ve unset this in the CMD windown, by executing set PERL5LIB=<CR>, the upgrade script started working as advertised:
C:\app\oracle\product\12.1.0\dbhome_1\RDBMS\ADMIN>%ORACLE_HOME%\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           = c:\app\oracle

Analyzing file catupgrd.sql
Log files in C:/app/oracle/product/12.1.0/dbhome_1/RDBMS/ADMIN
catcon: ALL catcon-related output will be written to catupgrd_catcon_4296.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus        = 8
SQL Process Count     = 0
New SQL Process Count = 4

------------------------------------------------------
Phases [0-73]         Start Time:[2018_03_30 21:25:39]
------------------------------------------------------
Serial   Phase #: 0      Files: 1     Time: 258s
Serial   Phase #: 1      Files: 5     Time: 75s
Restart  Phase #: 2      Files: 1     Time: 1s
Parallel Phase #: 3      Files: 18    Time: 20s
Restart  Phase #: 4      Files: 1     Time: 0s
Serial   Phase #: 5      Files: 5     Time: 31s
Serial   Phase #: 6      Files: 1     Time: 24s
Serial   Phase #: 7      Files: 4     Time: 15s
Restart  Phase #: 8      Files: 1     Time: 1s
Parallel Phase #: 9      Files: 62    Time: 56s
Restart  Phase #:10      Files: 1     Time: 0s
Serial   Phase #:11      Files: 1     Time: 27s
Restart  Phase #:12      Files: 1     Time: 0s
Parallel Phase #:13      Files: 91    Time: 32s
Restart  Phase #:14      Files: 1     Time: 0s
Parallel Phase #:15      Files: 111   Time: 44s
Restart  Phase #:16      Files: 1     Time: 1s
Serial   Phase #:17      Files: 3     Time: 3s
Restart  Phase #:18      Files: 1     Time: 0s
Parallel Phase #:19      Files: 32    Time: 31s
Restart  Phase #:20      Files: 1     Time: 0s
Serial   Phase #:21      Files: 3     Time: 17s
Restart  Phase #:22      Files: 1     Time: 0s
Parallel Phase #:23      Files: 23    Time: 125s
Restart  Phase #:24      Files: 1     Time: 1s
Parallel Phase #:25      Files: 11    Time: 49s
Restart  Phase #:26      Files: 1     Time: 0s
Serial   Phase #:27      Files: 1     Time: 3s
Restart  Phase #:28      Files: 1     Time: 0s
Serial   Phase #:30      Files: 1     Time: 0s
Serial   Phase #:31      Files: 257   Time: 47s
Serial   Phase #:32      Files: 1     Time: 0s
Restart  Phase #:33      Files: 1     Time: 1s
Serial   Phase #:34      Files: 1     Time: 10s
Restart  Phase #:35      Files: 1     Time: 0s
Restart  Phase #:36      Files: 1     Time: 1s
Serial   Phase #:37      Files: 4

The upgrade was completed successfully and the database is up and running as a 12c database. This, however, was the first time I ran into an intereference from OEM 12c agent. Apparently, the OEM 12c Perl modules are incompatible with the version delivered in Oracle 12.1.0.2. Go figure.

 

Advertisements

SCOTT/TIGER for SAP Hana

SAP Hana is becoming increasingly popular these days. It comes with its own demo schema, STUDENT. However, being an old Oracle hack, I ported the SCOTT schema from Oracle to SAP Hana. Every Oracle DBA has a bunch of queries against EMP and DEPT tables which he or she may test on the SAP Hana database. In case you’re interested, the export file is available here:

http://mgogala.byethost5.com/scott_hana.tgz

 

Finding the trace files

In Oracle versions before 12c, it was necessary to resort to complex magic to find out the location of the process trace file. One of the most famous recipes is probably the one invented by René Nyffenegger and available here:

http://www.adp-gmbh.ch/ora/misc/find_trace_file.html

Another trick included utilizing ORADEBUG command “tracefile_name” which would return trace file name.

However, in Oracle 12c the things have changed. Trace file is now available as a column in the V$PROCESS view:

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> desc v$process
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 ADDR RAW(8)
 PID NUMBER
 SOSID VARCHAR2(24)
 SPID VARCHAR2(24)
 STID VARCHAR2(24)
 EXECUTION_TYPE VARCHAR2(10)
 PNAME VARCHAR2(5)
 USERNAME VARCHAR2(15)
 SERIAL# NUMBER
 TERMINAL VARCHAR2(16)
 PROGRAM VARCHAR2(64)
 TRACEID VARCHAR2(255)
 TRACEFILE VARCHAR2(513)
 BACKGROUND VARCHAR2(1)
 LATCHWAIT VARCHAR2(16)
 LATCHSPIN VARCHAR2(16)
 PGA_USED_MEM NUMBER
 PGA_ALLOC_MEM NUMBER
 PGA_FREEABLE_MEM NUMBER
 PGA_MAX_MEM NUMBER
 CON_ID NUMBER

It is also documented in the documentation. Getting trace file is now a breeze:

SQL> select username,sid from v$session where username='SYSTEM';
USERNAME      SID
----------------------------- ----------
SYSTEM        362
Elapsed: 00:00:00.13
SQL> select p.tracefile from v$process p join v$session s  
2  on (p.addr=s.paddr) where s.sid=362;
TRACEFILE
--------------------------------------------------------------------------------
C:\APP\ORACLE\diag\rdbms\oraprod\oraprod\trace\oraprod_ora_11104.trc
Elapsed: 00:00:00.15

That makes the life of a DBA significantly easier.

ACFS 12.2 Quirks with defragmentation

I have a 12.2 RAC cluster in my lab, on two virtual machines. Checking out what acfsutil can do, I figured out that it can supposedly do defragmentation:

[grid@rac1 ~]$ acfsutil defrag -help

Usage: acfsutil [-h] command ... # (Version 12.2.0.1.0)

-h - help

Command Subcmd Arguments
--------------- --------- ------------------------------------------------------
 defrag dir [-r] <path>
 defrag dir Defrag all files in a specific directory 
 defrag dir [-r] recursively traverse directories
 defrag dir <path> path to a specific dir
 defrag file [-i] <path> [<path> ...]
 defrag file Defrag specific files 
 defrag file [-i] Estimate reduction in number of extents possible
 defrag file <path> path to a specific file

 

So, let’s try it out:

[grid@rac1 ~]$ sudo su - oracle
Last login: Mon Aug 28 18:52:05 EDT 2017 from 192.168.1.100 on pts/0
[oracle@rac1 ~]$ acfsutil defrag -r /oradata
acfsutil INVALID: Version 12.2.0.1.0

I switched to “oracle” because /oradata mount point is owned by that user.

[oracle@rac1 ~]$ ls -ld /oradata
drwxrwxr-x 7 oracle grid 32768 Aug 28 01:47 /oradata

I tried with the user root, but no cigar. The error message is exactly the same. So, it’s time to take a closer look at the error message. The error message tells me that the version 12.2.0.1.0 is invalid. OK, time to look at ASM:

 

[grid@rac1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 31 21:40:28 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> column database_compatibility format a20
column compatibility format a20
column name format a15
select name,compatibility,database_compatibility from v$asm_diskgroup;
SQL> SQL> SQL> 
NAME COMPATIBILITY DATABASE_COMPATIBILI
--------------- -------------------- --------------------
ACFS 12.2.0.1.0 10.1.0.0.0
MGMT 12.2.0.1.0 10.1.0.0.0
OCR 12.2.0.1.0 10.1.0.0.0

The compatibility of all disk groups is correctly set to the software release, which is 12.2.0.1.0, as is clearly visible from the sqlplus header.  The database compatibility doesn’t matter, it simply marks the lowest release of the database that can be created on the ASM group. Time to check out the ACFS file system itself:

[grid@rac1 ~]$ acfsutil compat get /oradata
/oradata
 compatible.advm: 12.2.0.0.0
 ACFS compatibility: 12.2.0.0.0

So, ACFS compatibility is set to 12.2.0.0.0, which is an invalid version, as correctly noted by the error message. However, the only way to change the compatibility is to run “mkfs” with the correct options or, in common vernacular, to “reformat the file system”. I will not do that because that would destroy my database. I consider this to be a serious bug. Oracle should not set the default file system compatibility to an invalid version. Here are the arguments for the mkfs.acfs:

[grid@rac1 ~]$ mkfs.acfs
mkfs.acfs: Create an ACFS file system.
mkfs.acfs: Usage: [-h] [-v] [-f] [-i <metasize>] [-n <name>] [-a <volume>] [-c <release>] <volume> [<size>]
mkfs.acfs: [-h] - Prints out this usage message
mkfs.acfs: [-v] - Verbose mode
mkfs.acfs: [-f] - Force file system format, use to overwrite an existing file system
mkfs.acfs: [-i <metasize>] - Metadata block size
mkfs.acfs: [-n <name>] - Name of file system to be created
mkfs.acfs: [-a <volume>] - ACFS accelerator volume pathname
mkfs.acfs: [-c <release>] - Create ACFS file system compatible with specified release (For example, 12.2.0.0.0)
mkfs.acfs: <volume> - ACFS primary volume pathname
mkfs.acfs: [<size>] - Desired size of the file system in bytes or in units of K|M|G|T|P
mkfs.acfs: Version 12.2.0.1.0

The incorrect argument is “-c”. The funniest thing is that the invalid version in question is even listed at the help screen.  It looks like the defrag utility will stay out of range for now.

RAC 12.2, part 2

Well, today was an interesting day. My ASM, configured with so much effort, stopped working:

root@rac1 grid]# . ~grid/.bashrc
[root@rac1 grid]# crsctl start ohasd
CRS-4640: Oracle High Availability Services is already active
CRS-4000: Command Start failed, or completed with errors.
[root@rac1 grid]# crsctl start cluster
CRS-2672: Attempting to start 'ora.crf' on 'rac1'
CRS-2672: Attempting to start 'ora.storage' on 'rac1'
CRS-2676: Start of 'ora.crf' on 'rac1' succeeded
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: OperatCRS-2674: Start of 'ora.storage' on 'rac1' failed
CRS-2679: Attempting to clean 'ora.storage' on 'rac1'
CRS-2681: Clean of 'ora.storage' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'rac1'
CRS-2677: Stop of 'ora.crf' on 'rac1' succeeded
CRS-4000: Command Start failed, or completed with errors.
[root@rac1 grid]#

After digging through the log files, I found the following message:

ORA-27302: failure occurred at: sskgpreset1
 2017-08-27 18:26:16.056 : OCRRAW:1181941824: -- trace dump on error exit --
 2017-08-27 18:26:16.056 : OCRRAW:1181941824: Error [kgfoAl06] in [kgfokge] at kgfo.c:3083
 2017-08-27 18:26:16.056 : OCRRAW:1181941824: ORA-27140: attach to post/wait facility failed
 ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
 ORA-27301: OS failure message: Operat
 2017-08-27 18:26:16.056 : OCRRAW:1181941824: Category: 7
 2017-08-27 18:26:16.056 : OCRRAW:1181941824: DepInfo: 27140
 2017-08-27 18:26:16.057 : OCRRAW:1181941824: -- trace dump end --
 2017-08-27 18:26:16.057 : OCRASM:1181941824: proprasmo: kgfoCheckMount returned [7]
 2017-08-27 18:26:16.057 : OCRASM:1181941824: proprasmo: The ASM instance is down

There is a problem with connecting to ASM instance, which is up and running:

[grid@rac1 ~]$ ORACLE_SID="+ASM1"
[grid@rac1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Sun Aug 27 18:29:48 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.




Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name,state from v$asm_diskgroup;

NAME STATE
------------------------------ -----------
ACFS MOUNTED
MGMT MOUNTED
OCR MOUNTED

Now, I was at a loss as to why CRS could not connect to the ASM instance. The user “grid” has all necessary groups:

[grid@rac1 ~]$ id
uid=1000(grid) gid=1000(grid) groups=1000(grid),10(wheel),984(vboxsf),54321(oinstall),54331(asmadmin),54332(asmdba)

The content of the $GRID_HOME/rdbms/lib/config.c looks like this:

/*
 * Assembler will not parse a file past the .end directive
 */
#endif

#define SS_DBA_GRP "asmdba"
#define SS_OPER_GRP ""
#define SS_ASM_GRP "asmadmin"
#define SS_BKP_GRP "asmadmin"
#define SS_DGD_GRP "asmadmin"
#define SS_KMT_GRP "asmadmin"
#define SS_RAC_GRP "asmadmin"

User “grid” is the member of all the groups listed above. So, let’s see what group is the ASM instance running as?

[root@rac1 lib]# ps -G asmdba
 PID TTY TIME CMD
[root@rac1 lib]# ps -G asmadmin
 PID TTY TIME CMD
[root@rac1 lib]# ps -G grid
 PID TTY TIME CMD
 2401 ? 00:00:00 sshd
 2402 pts/0 00:00:00 bash
 2576 ? 00:00:04 oraagent.bin
 2595 ? 00:00:01 mdnsd.bin
 2597 ? 00:00:05 evmd.bin
 2646 ? 00:00:02 gpnpd.bin
 2682 ? 00:00:01 evmlogger.bin
 2695 ? 00:00:04 gipcd.bin
 2940 ? 00:00:06 ocssd.bin
 3121 ? 00:00:00 asm_pmon_+asm1
 3127 ? 00:00:00 asm_clmn_+asm1
 3130 ? 00:00:00 asm_psp0_+asm1
 3133 ? 00:00:00 asm_vktm_+asm1
 3137 ? 00:00:01 asm_gen0_+asm1
 3141 ? 00:00:00 asm_mman_+asm1
 3143 ? 00:00:00 asm_scmn_+asm1
 3147 ? 00:00:01 asm_diag_+asm1
 3149 ? 00:00:00 asm_ping_+asm1
 3151 ? 00:00:00 asm_pman_+asm1
 3153 ? 00:00:02 asm_dia0_+asm1
 3155 ? 00:00:01 asm_lmon_+asm1
 3157 ? 00:00:01 asm_lmd0_+asm1
 3159 ? 00:00:03 asm_lms0_+asm1
 3163 ? 00:00:01 asm_lmhb_+asm1
 3165 ? 00:00:00 asm_lck1_+asm1
 3167 ? 00:00:00 asm_dbw0_+asm1
 3169 ? 00:00:00 asm_lgwr_+asm1
 3171 ? 00:00:00 asm_ckpt_+asm1
 3173 ? 00:00:00 asm_smon_+asm1
 3175 ? 00:00:00 asm_lreg_+asm1
 3177 ? 00:00:00 asm_pxmn_+asm1
 3179 ? 00:00:00 asm_rbal_+asm1
 3181 ? 00:00:00 asm_gmon_+asm1
 3183 ? 00:00:00 asm_mmon_+asm1
 3185 ? 00:00:00 asm_mmnl_+asm1
 3187 ? 00:00:02 asm_imr0_+asm1
 3189 ? 00:00:00 asm_lck0_+asm1
 3195 ? 00:00:02 asm_gcr0_+asm1
 6972 ? 00:00:00 sshd
 6973 pts/1 00:00:00 bash
 9317 pts/1 00:00:00 adrci
 9907 ? 00:00:00 sshd
 9908 pts/2 00:00:00 bash
10610 pts/2 00:00:00 perl
10646 ? 00:00:00 oracle_10646_+a
[root@rac1 lib]#

So, the ASM instance was running as a group “grid”, but the SYSASM group was asmadmin. I did make a configuration mistake by leaving the default group “grid” as the primary  group for user “grid”, so the database was started as “grid,grid”.  The first possible solution was to change the primary group for the user “grid” and hope for the best. If that fails, I would have to re-install.  The trick that got everything back up was to allow group access to the dba group:

SQL> show parameter allow_group

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean TRUE
SQL>

After setting that in the SPFILE for a single ASM instance, all was well:

[grid@rac1 ~]$ srvctl status filesystem
ACFS file system /oradata is mounted on nodes rac1,rac2
[grid@rac1 ~]$

Installing Oracle RDBMS after that was a breeze. This parameter is new in Oracle 12.2 and is set to “FALSE” by default. It increases security, so much that the clusterware doesn’t start, which prevents the SCOTT tables from falling into the enemy hands. Of course,  I should have heeded the warning duly shown by cluvfy, but I was lazy. However, I learned a lot.

RAC 12.2

I am in the process of installing RAC 12.2. The installation procedure has undergone a significant change, for the better. It’s now much easier to install it. However, memory and disk requirements have skyrocketed. I needed to allocate 10GB of memory for each of my nodes, 12 GB for OCR and 64GB for the management database. My computer will be barely large enough to run both Commserve and those two nodes together:

I am running Oracle Linux 7.4, the latest and the greatest. The installation procedure is now named gridSetup.sh. The procedure is fairly resource intensive and it is taking more than 30 minutes. Here is the monitor from my host machine:

As you can see from the monitor, it’s a 32 GB machine with quad core CPU and using flash disk to boot. So, if anyone wants to install RAC 12.2 in the home lab, be aware that this is a huge monstrosity which requires a lot of resources. I will have to buy a new machine, with 64 GB RAM to comfortably run both Commvault and 12.2 RAC. Installation process itself is convoluted and complex, using Java and Perl (expected), as well as gdb (GNU debugger) which is totally unexpected. The process also runs depmod, which means that new kernel modules are installed under the hood. This is probably for ACFS, Oracle’s cluster file system. Each of the nodes has 2 virtual CPU’s, the maximum I can afford. This is just the grid infrastructure. I still need the database and the July patchset. The installation is easier than before and I got the most important thing done:

grid@rac1 12.2.0]$ srvctl -version
srvctl version: 12.2.0.1.0
[grid@rac1 12.2.0]$ srvctl status asm
ASM is running on rac1,rac2

I will add the database tomorrow.

Rename Pluggable Database

I stumbled upon a convenient way to rename a pluggable database:

SQL> alter system enable restricted session;

System altered.

SQL> alter database rename global_name to test12.home.com;

Database altered.

SQL> alter session set container=cdb$root;

Session altered.

SQL> select name from v$pdbs;

NAME
——————————
PDB$SEED
TEST12

In other words, to rename a pluggable database, one only needs to rename its global name. Cute!