I have managed to stop the cluster from wasting away memory chunks owing to BUG [Note 1523366.1]. Oracle's solution is to opatch. However, the client cannot do that at this point owing to the country wide dependency on the system. In addition to this, to opatch the system is risky considering the fragile nature of RAC in Windows. I am not 100% confident that an opatch will be successful and I would sooner create a new cluster than to apply a patch to and Oracle RAC system on Windows.
For the original discovery of the bug - see here
For the fragility of the cluster - see here
A recap: the command that is left open every 6 hours during the CVU health check is:
C:\Windows\system32\cmd.exe /K E:\OracleGrid\11.2.0.3\bin\cluvfy comp health -_format
A screenshot can be found here when the orphaned session grows in numbers:
For the memory leak fix, I did the following.
I backup a copy of the bat file: E:\OracleGrid\11.2.0.3\bin\cluvfy.bat
I then open the original cluvfy,bat in notepad and carefully make the following changes
CHANGE 1 FROM: if not (%CRSHOME%)==() ( @set "CV_HOME=%CRSHOME%" ) set CMDPATH=%~dp0
TO: if not (%CRSHOME%)==() ( @set "CV_HOME=%CRSHOME%" ) set EXIT_OPTION= if "%CVU_RESOURCE_OPTIONS%"=="" set EXIT_OPTION=/B set CMDPATH=%~dp0
TO: exit %EXIT_OPTION% %errorlevel% goto done :ERROR exit %EXIT_OPTION% 1
Once the change is made, save and copy the file across to node 2. I can also move the CVU to avoid creating any unwanted issues using while carrying out the above changes
srvctl relocate cvu -n <node name>
However, considering the batch file is executed every 6 hours, the chances are slim.
The idea to carry this out came after looking at the source code of the 11.2.0.4 home - I noted a bug fix by a certain Oracle developer @ Oracle. I diff'd the old and new CVU, evaluated the developers intention, and took only what I needed from his fix to manually patch the CVU myself. I first recreated the problem in the 11.2.0.4 test environment, monitored it for a day, patched it with my fix, and monitored for a few days. Once done satisfied, I took the 'patch' live. This fix will allow the in house DBA and his manager to have a cluster that no longer crashes once a month - forever. The nightmare is over and this solution will suffice until we create a new cluster for 12c. Done fixed it.
Oracle stores data in cells within datafiles referred to as datablocks. By default, those blocks are squared off into 8196 byte partitions. The blocksize usually comes into play on database creation and should be the same as the OS blocksize for improved efficiency. More info on blocks here. On a long enough time line, provided the drives survive long enough, corruption will occur at some point or another within the database. They can be recovered provided corruption is detected shortly after it occurs before it persists the days or weeks of backups. Corruption is usually reported first in the RMAN backup log therefore it is important to view the backup log daily to spot corruption.
To see the block size of an instance I run: SQL> show parameter db_block_size NAME TYPE VALUE --------------------------- ----------- ------- db_block_size integer 8192
Enough about that, moving onto corrupting a datablock.
Create a new tablespace: CREATE TABLESPACE blockbreaker DATAFILE '/home/oracle/app/oracle/oradata/cdb1/orcl/blockbreaker01.dbf' SIZE 10M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/cdb1/orcl/blockbreaker01.dbf' AUTOEXTEND OFF; CREATE TABLE BLOCKMAKER ( VALUE VARCHAR(100) ) TABLESPACE BLOCKBREAKER; Now populate the table with data: begin for i in 1..10000 loop insert into blockmaker values (to_char(sysdate,'DDMMYYYY HH24:MI:SS')||'XXXXXXXXX_'||to_char(i)); commit; end loop; end; / alter system switch logfile; alter system switch logfile; alter system switch logfile; alter system switch logfile; alter system switch logfile;
Once created, take a full system backup - in this example I am performing a warm backup considering my database is in archivelog mode. run { crosscheck backup; delete noprompt obsolete; backup as compressed backupset full database tag WARM_FULL_BACKUP format '/home/oracle/app/oracle/backup/%d_%T_%s_%p_WARM_FULL.bk'; sql 'alter system archive log current'; backup tag ARCHIVELOG_BACKUP format '/home/oracle/app/oracle/backup/%d_%T_%s_%p_ARCHIVE.bk' archivelog all delete all input ; backup tag CONTROL_BACKUP current controlfile format '/home/oracle/app/oracle/backup/%d_%T_%s_%p_CONTROL.bk'; delete noprompt obsolete; }
Now the fun part. I am using a Linux VM for the following, I have not done something like this in Windows before but I am sure that there are commands that perform similar functions in a Windows environment.
I am going to
-corrupt a block in my new table
-verify it is corrupted with DBV
-Dump the block out
-Check the corruption view to show the corruption was registered
-Recover the bad blocks
In sqlplus I run the following: set trimspool on set heading off set lines 1000 set pages 1000 column file_name new_val file_name; column file_id new_val file_id; column block_id new_val block_id; select f.name as file_name, to_char(f.block_id) as block_id, to_char(f.file#) as file_id from ( select * from v$datafile vd, dba_extents de where lower(vd.name) like '%blockbreaker%' and vd.file# = de.file_id order by dbms_random.random ) f where rownum < 2 / select 'dd of=&file_name bs=8192 conv=notrunc seek=&block_id << EOF' as string from dual union all select 'BLOCKBREAKER IS HERE' as string from dual union all select 'EOF' as string from dual union all select ' ' as string from dual union all select 'dbv FILE='''||name||''' blocksize=8192' as string from (select * from v$datafile where lower(name) like '%blockbreaker%' order by file#) where rownum < 2 union all select 'echo ''alter system dump datafile &file_id block &block_id;'' | sqlplus sys/oracle@cdb1 as sysdba' as string from dual union all select ' ' as string from dual union all select 'cd '||value from v$diag_info where name='Diag Trace' union all select 'ls -trl *ora*.trc' from dual; / exit;
The output sould look like this: dd of=/home/oracle/app/oracle/oradata/cdb1/orcl/blockbreaker01.dbf bs=8192 conv=notrunc seek=168 << EOF BLOCKBREAKER IS HERE EOF dbv FILE='/home/oracle/app/oracle/oradata/cdb1/orcl/blockbreaker01.dbf' blocksize=8192 echo 'alter system dump datafile 31 block 168;' | sqlplus sys/oracle@cdb1 as sysdba cd /home/oracle/app/oracle/diag/rdbms/cdb1/cdb1/trace ls -trl *ora*.trc
I copy the output and run in the shell: [oracle@localhost backup]$ dd of=/home/oracle/app/oracle/oradata/cdb1/orcl/blockbreaker01.dbf bs=8192 conv=notrunc seek=168 << EOF > BLOCKBREAKER IS HERE > EOF 0+1 records in 0+1 records out 21 bytes (21 B) copied, 0.000171222 s, 123 kB/s [oracle@localhost backup]$ [oracle@localhost backup]$ dbv FILE='/home/oracle/app/oracle/oradata/cdb1/orcl/blockbreaker01.dbf' blocksize=8192 DBVERIFY: Release 12.1.0.2.0 - Production on Thu Sep 15 09:24:06 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /home/oracle/app/oracle/oradata/cdb1/orcl/blockbreaker01.dbf Page 168 is marked corrupt Corrupt block relative dba: 0x07c000a8 (file 31, block 168) Bad header found during dbv: Data in bad block: type: 66 format: 4 rdba: 0x4552424b last change scn: 0x4920.52454b41 seq: 0x53 flg: 0x20 spare1: 0x4f spare2: 0x43 spare3: 0x4552 consistency value in tail: 0x6ac40603 check value in block header: 0x4548 block checksum disabled DBVERIFY - Verification complete Total Pages Examined : 1280 Total Pages Processed (Data) : 57 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 133 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 1089 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 7566501 (0.7566501) [oracle@localhost backup]$ echo 'alter system dump datafile 31 block 168;' | sqlplus sys/oracle@cdb1 as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 15 09:24:06 2016 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> System altered. SQL> 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@localhost backup]$ [oracle@localhost backup]$ cd /home/oracle/app/oracle/diag/rdbms/cdb1/cdb1/trace [oracle@localhost trace]$ ls -trl *ora*.trc -rw-r-----. 1 oracle oracle 2205 Sep 15 09:24 cdb1_ora_31184.trc [oracle@localhost trace]$
The commands should place the shell in the trace location. The latest TRC file should contain the contents of the newly corrupted block.
I cat the latest trace file out below: [oracle@localhost trace]$ cat cdb1_ora_31184.trc Trace file /home/oracle/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_31184.trc 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_HOME = /home/oracle/app/oracle/product/12.1.0/dbhome_1 System name: Linux Node name: localhost.localdomain Release: 3.8.13-68.1.3.el7uek.x86_64 Version: #2 SMP Wed Apr 22 11:51:54 PDT 2015 Machine: x86_64 Instance name: cdb1 Redo thread mounted by this instance: 1 Oracle process number: 67 Unix process pid: 31184, image: oracle@localhost.localdomain *** 2016-09-15 09:24:06.877 *** SESSION ID:(44.23378) 2016-09-15 09:24:06.877 *** CLIENT ID:() 2016-09-15 09:24:06.877 *** SERVICE NAME:(cdb1) 2016-09-15 09:24:06.877 *** MODULE NAME:(sqlplus@localhost.localdomain (TNS V1-V3)) 2016-09-15 09:24:06.877 *** CLIENT DRIVER:(SQL*PLUS) 2016-09-15 09:24:06.877 *** ACTION NAME:() 2016-09-15 09:24:06.877 *** CONTAINER ID:(1) 2016-09-15 09:24:06.877 Start dump data blocks tsn: 6 file#:31 minblk 168 maxblk 168 Block dump from cache: Dump of buffer cache at level 4 for pdb=1 tsn=6 rdba=130023592 BH (0x78bdb158) file#: 31 rdba: 0x07c000a8 (31/168) class: 1 ba: 0x78884000 set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0 dbwrid: 0 obj: 93452 objn: 93452 tsn: [1/6] afn: 31 hint: f hash: [0x8290d588,0x8290d588] lru: [0x78bdb380,0x78bdb100] ckptq: [NULL] fileq: [NULL] objq: [0x78bdb128,0x78bdd1a8] objaq: [0x78bdd1b8,0x78bdb138] st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x0.736877 tch: 1 flags: block_written_once LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1] Block dump from disk: Encrypted block <6, 130023592> content will not be dumped. Dumping header only. buffer tsn: 6 rdba: 0x4552424b (277/1196619) scn: 0x4920.52454b41 seq: 0x53 flg: 0x20 tail: 0x6ac40603 frmt: 0x04 chkval: 0x4548 type: 0x42=unknown Hex dump of corrupt header 4 = CORRUPT Dump of memory from 0x00007FD7120A1E00 to 0x00007FD7120A1E14 7FD7120A1E00 434F4C42 4552424B 52454B41 20534920 [BLOCKBREAKER IS ] 7FD7120A1E10 45524548 [HERE] End dump data blocks tsn: 6 file#: 31 minblk 168 maxblk 168 [oracle@localhost trace]$
The block should be registered in the dynamic corruption view table once discovered by DBV - if it has not been registered, I can discover it myself: --Flush buffer cache to force the db to retrieve the blocks from diskSQL> alter system flush buffer_cache; System altered. SQL> select * from v$database_block_corruption; no rows selected SQL> select * from blockmaker; VALUE -------------------------------------------------------------------------------- ............................ 15092016 09:16:08XXXXXXXXX_6098 15092016 09:16:08XXXXXXXXX_6099 15092016 09:16:08XXXXXXXXX_6100 15092016 09:16:08XXXXXXXXX_6101 ERROR: ORA-01578: ORACLE data block corrupted (file # 31, block # 168) ORA-01110: data file 31: '/home/oracle/app/oracle/oradata/cdb1/orcl/blockbreaker01.dbf' 6885 rows selected. SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID ---------- ---------- ---------- ------------------ --------- ---------- 31 168 1 0 CORRUPT 0 SQL>
Now I will recover the corrupted block. This involves RMAN unpacking the last backup of the corrupted datafile and applying the logs in the backup as well as the logs generated up until this point. RMAN> blockrecover corruption list; Starting recover at 15-SEP-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=43 device type=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00031 channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/backup/CDB1_20160915_9_1_WARM_FULL.bk channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/backup/CDB1_20160915_9_1_WARM_FULL.bk tag=WARM_FULL_BACKUP channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:03 Finished recover at 15-SEP-16 RMAN>
the corruption flag has been cleared: SQL> select * from v$database_block_corruption; no rows selected
And I am able to now select from my table: .................. VALUE -------------------------------------------------------------------------------- 15092016 09:16:08XXXXXXXXX_10000 10000 rows selected. SQL> l 1* select * from blockmaker SQL>
I completed my undergraduate in management information systems (BSc Informatics) in May.
I received my results and passed my degree with distinction. Yay me. I have been studying part time for ~8 years and disappeared for a weekend to celebrate.
The farmhouses at Beacon Vlei are beautiful, looked after, and on the shore of a dam.
The website for Beacon Vlei can be found here
While installing APEX builder in a duplicated database, I receive the following warnings and errors; . ____ ____ ____ ____ . / \ | \ /\ / | / .| || / / \ | | | .| ||--- ---- | | |-- .| || \ / \ | | | . \____/ | \/ \ \____ |____ \____ . . Application Express Installation (DEV). ......................................... . ... Checking prerequisites (ADD_DEV) . PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. . ... Prerequisite checks passed. . no rows selected PL/SQL procedure successfully completed. .................. .................. Installing SYS view wrappers Create apex_sys_all_synonyms view View created. Create apex_sys_all_objects view View created. Create apex_sys_all_constraints view View created. Create apex_sys_all_dependencies view View created. Installing Team Development objects ...create team development objects create table wwv_flow_news * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired create unique index wwv_flow_news_idx1 on wwv_flow_news(security_group_id, id) * ERROR at line 1: ORA-00942: table or view does not exist create table wwv_flow_links ( * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired create index wwv_flow_links_idx1 on wwv_flow_links (security_group_id) * ERROR at line 1: ORA-00942: table or view does not exist
The installation continues, however, the side effects of the above missing objects are numerous.
Once the pages are loaded and the installer puts me back in the console, I notice a number of invalid objects in the apex schema.
It seems that during the Team Development object installation, a resource busy issue compromises the installation. The solution was not an entire re-installation but rather
a re-running of the problematic subset of the installer - manually - and chasing it up with a recompile.
The script that runs the Team development install is <APEX_SOURCE>\apex\devins.sql
Within the devins.sql script, I extract the following lines of script and rerun them manually as sysdba:
alter session set current_schema = APEX_040200; prompt Installing Team Development objects @./core/team_tab.sql @./core/wwv_flow_team.sql @./core/wwv_flow_team_api.sql @./core/wwv_flow_team_gen_api.sql
I then run a recompile: @?/rdbms/admin/utlrp
The APEX objects are valid again and the builder is accessible.
I managed to get my hands on an Oracle 12C developer image along with Oracle Virtual Box which can be downloaded from here...
It's come a long way since the last time I played with Oracle Virtual Box and I am impressed at how simple the setup is currently. "You know sonny, back in my day, we had to create the VM image from scratch, install the OS, the software, setup the network, prepare the virtual drives and partition space ourselves! Easily a day or two of work!".
Not any more - all I had to do was grab the Oracle 12C .OVA file, load it into Oracle Virtual Box and within 20 minutes I had a polished and running copy of Linux, a copy of Oracle 12C EE and both a CDB and PDB. Lovely.
The best part was that I was able to setup Port Forward via settings panel which meant I can use my SSH client tools from my host. I forward port 22 from host to VM as shown below:
I can now connect to the VM using MobaXterm and work as a client.
It's also been a very long time since I've worked in Linux, I have missed it. I'll be using this new setup to explore the new features of 12C. So long in fact that I forgot all about TWO_TASK and what happens when it is not unset and not unset in .profile :
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 18 13:17:10 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
The same system impacted in April from a lost SAN went down again yesterday. The VM file vanished after the operators performed a routine system restart. We believe something else occurred and the operators did more than a routine reboot. As frustrating as the news was, the server admins rebuilt the OS and I restored Oracle - the system was up and open for business in 4 hours when the call was made to recreate the environment. The db was restored to the point of failure too. I am in the process of restoring the final integration components today.
I used the same notes I put down in the page: Database Fever
Upon reviewing the weekend scheduler sent emails for the system, I discovered the following output for our DR standby email:
DRDB DATABASE ============= SP2-0640: Not connected
The correct output should show the number of hours our DR is trailing the production system. Clearly something was wrong.
I investigate the cluster log E:\OracleGrid\11.2.0.3\log\<NODE_1>\alert<NODE_1>.log: 2016-08-07 00:15:10.005: [ohasd(2040)]CRS-2112:The OLR service started on node <NODE_1>. 2016-08-07 00:15:10.425: [ohasd(2040)]CRS-1301:Oracle High Availability Service started on node <NODE_1>.
It began producing these messages again and again from Sunday morning until Monday morning. Something had occurred at midnight which the cluster-ware did not like.
I looked at the services.msc panel and found the OracleOHService to be down. I manually started it up and watched the log. It produced the aforementioned message again and the server went but to a shutdown state. OHAS was not starting and showed no errors in either log thus far. I then went into the OHASD log folder to see what I could see.
I found a series of 100M dump files output by the failed process as well as the stack dumps.
The dump read: Symbol file E:\OracleGrid\11.2.0.3\bin\orannzsbb11.SYM does not match binary.
This did not look good I thought, I've seen this issue in the past and it had to do with a failed opatch. I had not opatched anything in DR before.
I looked through E:\OracleGrid\11.2.0.3\log\<NODE_1>\ohasd\ohasdOUT.log and found: 08/08/16 08:42:03 ssmain_run_crs: CRS starting with restart argument 08/08/16 08:42:03 ssmain_stateCallback: marking daemon service as active 08/08/16 08:42:03 ssmain_monitor_thread setting the service status to Running 08/08/16 08:42:03 **** caught exception (0xe06d7363) in thread 2756 **** Oracle Database 11g OHAS Release 11.2.0.3.0 Production Copyright 1996, 2009, Oracle. All rights reserved. Main: Parameters WaitForAutoStart: 1 ServiceStartupDelay: 0 MaxAutoStartDelay: 600000 OhasdQueryServiceInfo: failed to get service handle for NisDrv, err(5) OhasdReadService: failed getting information for NisDrv, skipping OhasdQueryServiceInfo: failed to get service handle for NisSrv, err(5) OhasdReadService: failed getting information for NisSrv, skipping OhasdReadService: skipping service Oracle ACFS OhasdReadService: skipping service Oracle ADVM OhasdReadService: skipping service Oracle OKS OhasdReadService: skipping service OracleASMService+ASM OhasdReadService: skipping service OracleOraCrs11g_home1TNSListener OhasdReadService: skipping service OracleOraDb11g_home1ClrAgent OhasdReadService: skipping service OracleService<SID> Main: service list contains 0 Main: completed waiting for 0 startup delay Main: completed all work, exiting
The drives we not starting. This really cannot be good I thought. I read the ohasd.log file and found: E:\OracleGrid\11.2.0.3\log\<NODE_1>\ohasd\ohasd.log 2016-08-08 08:42:03.150: [ default][6944] OHASD Daemon Starting. Command string :restart 2016-08-08 08:42:03.150: [ default][6944] Initializing OLR ......................... 2016-08-08 08:42:03.275: [ CRSPE][2756] {0:0:2} Sent request to write event sequence number 5500000 to repository 2016-08-08 08:42:03.290: [ CRSPE][2756] {0:0:2} Wrote new event sequence to repository 2016-08-08 08:42:03.306: [ CRSPE][2756] {0:0:2} Reading (7) resources 2016-08-08 08:42:03.306: [ CRSPE][2756] {0:0:2} Reading (1) server pools 2016-08-08 08:42:03.306: [ CRSPE][2756] {0:0:2} Reading (13) types 2016-08-08 08:42:03.853: [ CRSPE][2756] {0:0:2} Finished reading configuration. Parsing... 2016-08-08 08:42:03.853: [ CRSPE][2756] {0:0:2} Parsing resource types... 2016-08-08 08:42:03.868: [ CRSSEC][2756] {0:0:2} Exception: OwnerEntry construction failed to retrieve user id by name with ACL string: owner:<DOMAIN>\<OLD_USER>:rwx and error: 1 2016-08-08 08:42:03.868: [ CRSSEC][2756] {0:0:2} Exception: ACL entry creation failed for: owner:<DOMAIN>\<OLD_USER>:rwx
For a very strange reason, the clusterware wanted to validate the owner of each resource, however, the user it was set to was the previous DBA. This is strange considering we only ever use a global administration account across all machines for all Oracle administration purposes. I struggled to think of what file or process may want to use the old credentials. I then executed:
The CRS component along with all the components in the OCR, were registered to the old account. So, if the account was gone, it would explain why the CRS was not coming up.
I searched for the user in active directory via a PROPERTIES>SECURITY>ADVANCED>OWNER>EDIT to see if the user existed. It did not. I could find my user, but not this one.
I then requested that the network administrator contact the domain administrator to reinstate the user. It turned out that the user was deleted over a year ago but the DR server had not been restarted since. Reinstating the account it resolved the issue and cluster started up correctly.
Note 1491367.1 has details on how to handle the issue. However, removing components was not applicable in this situation owing to the fact that the vast majority of components were all owned by this single user. I will look for a way to change the owner of components in time.
I have managed to stop the cluster from wasting away memory chunks owing to BUG [Note 1523366.1]. Oracle's solution is to opatch. However, the client cannot do that at this point owing to the country wide dependency on the system. In addition to this, to opatch the system is risky considering the fragile nature of RAC in Windows. I am not 100% confident that an opatch will be successful and I would sooner create a new cluster than to apply a patch to and Oracle RAC system on Windows.
