ORA-20104 ORA-00942 error in APEX when click a todo item of team development

able to create todo item but not able to view todo item due to below error in APEX 5.1.4

ORA-20104: create_collection_from_queryb2
Error:ORA-20104: create_collection_from_query
ParseErr:ORA-00942: table or view does not exist

Googled and noticed below solution works.

To re-create the table, it should be sufficient to do the following (in Application Builder):
1) As a workspace admin, go to Administration -> Manage Service -> Set Workspace Preferences
2) Check if “Enable File Repository” in “Team Development” is set to Yes.
   Set it to No, if file upload isn’t needed or keep the Yes.
3) Click the Apply Changes button to re-create the apex$team_dev_files table in the workspace schema.

--checked no table before apply the solution
SQL> conn / as sysdba
Connected.
SQL> select * from dba_objects where object_name =upper('apex$team_dev_files');

no rows selected


Spent some and finally find the setting in 5.1.4 as shown below.



--checked again but I don't see the table is created.

SQL> l
  1* select * from dba_objects where object_name =upper('apex$team_dev_files')
SQL> /

no rows selected

SQL> exit


Never mind, my problem solved.

Insert Chinese character into Oracle database

My database character is AL32UT8. Below works for me.

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
> sqlplus / as sysdba



SQL> select value10 from tab1 where config_name like '%SMS_TEMP%' AND VALUE1='132';

VALUE10
--------------------------------------------------------------------------------
亲爱的顾客

How to identify "bad" index after analyze table validate structure cascade - from rdba or seg/obj.

Do this.

SELECT dbms_utility.data_block_address_file(to_number(trim(leading '0' from replace('&&rdba','0x','')),'XXXXXXXX')) AS rfile#,dbms_utility.data_block_address_block(
to_number(trim(leading '0' from replace('&&rdba','0x','')),'XXXXXXXX')) AS block# FROM dual;
    RFILE#     BLOCK#
---------- ----------
        20      17036

select segment_name, owner from dba_extents where file_id=20 and 17036 between block_id and block_id+blocks-1;


or

SQL> analyze table test validate structure cascade;
analyze table test validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file 

The associated trace file contains:

Table/Index row count mismatch
table 6559 : index 10000, 0
Index root = tsn: 6 rdba: 0x01400091

It means: A table scan returned 6559 rows and an index scan returned 10000 rows.

"Index root" is the segment header information for the index:

rdba: 0x01400091 is the Index segment header relative data block address. It is decimal 20971665 which is Rfile#=5 Block#=145 :

SQL> select dbms_utility.data_block_address_file(20971665)  "Rfile#"  
2          ,dbms_utility.data_block_address_block(20971665) "Block#"  
3 from dual;  

Rfile#     Block#  
---------- ----------  
5          145  

Running the next query can identify the associated index:

QUERY 1: 

SQL> select owner, segment_name, segment_type 
2    from  dba_segments 
3    where header_file = 5 
4      and header_block = 145; 

OWNER    SEGMENT_NAME    SEGMENT_TYPE 
-------- --------------- ------------------ 
SCOTT    I_TEST          INDEX

ora-00600 related docs for block corruption

Mark some doc ID here for recent incident. A  Happy New Year ahead to all!

ORA-600 [6006] ORA-600 [6856] During Startup Instance, Followed by Termination by SMON (Doc ID 549000.1)

Identify the Corruption Extension for Block Corruption, Table/Index Inconsistency, Data Dictionary and Lost Writes (Doc ID 836658.1)

RMAN - Identify Datafile Block Corruptions

• To identify both Physical and Logical Block Corruptions use the "CHECK LOGICAL" option. The next command checks the complete database for both corruptions without actually doing a backup:

$ rman target /
RMAN>  backup check logical validate database;

The next command checks the complete database for both corruptions in a backup:

$ rman target /
RMAN> backup check logical database

• Check the view V$DATABASE_BLOCK_CORRUPTION to identify the block corruptions detected by RMAN. 
• Use Doc ID 472231.1 (section "Step 2: Identify the corrupt segments") to identify all the Corrupted Objects in the Database reported by RMAN.
• The above command can use PARALLELISM using multiple channels to make the validation faster.  Reference Doc ID 472231.1 for examples of PARALLELISM.
• By Default RMAN backups, without the CHECK LOGICAL option, only detect Physical Block Corruptions.

Identify corruption caused by LOST WRITES

A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage.  The result is that the block in the database is a stale/old copy which is not logical or physical corrupt; block internal structures are correct. Reference Doc ID 840978.1 for more information about Physical and Logical block corruption.

A block with lost changes may produce several errors when compared with another context like ORA-600 [kdsgrp1] (table/index inconsistency or invalid chained row pointer), ORA-8103 (old object id), etc; or the next errors during media recovery (like in a physical standby): ORA-600 [3020], ORA-752 (if db_lost_write_protect is enabled).

• DBV/RMAN are not intended to identify inconsistency caused by LOST Write:

Identifying the corruption extension by lost IO is not straight forward as dbverify/rman run intra-block checks (blocks are not compared with another context). The block itself is healthy as structures are valid (not garbage). However, in very rare cases a block can be indirectly exposed to logical corruption especially in the space management area if there is a lost write. Example is that the block was being marked as full in the metadata but that change was lost. Subsequent inserts may logically corrupt the block.

• Media RECOVERY / Physical Standby

The best option is to have a media recovery in place like a standby database or restore/recover the database in another system.  Media recovery performs checks to identify if the block content is the one expected as the redo structure keeps track of block previous version (expected scn) and compare it with the current block scn.  If there is a mismatch then ORA-600 [3020] or ORA-752 are produced.

• EXPORT

Running export may help to identify if a chained row has an invalid pointer which may cause ORA-00600 [25027] or ORA-00600 [kdsgrp1] but export does not identify an old block version or if there is a table/index mismatch.  In UNIX systems it can be done to /dev/null:

exp system/manager full=y log=exp_validation.log file=/dev/null volsize=0

How to identify all the Corrupted Objects in the Database with RMAN (Doc ID 472231.1)


To make it faster,  RMAN can be configured to use PARALLELISM with multiple channels:

RMAN> configure device type disk parallelism 4;
RMAN> backup validate check logical database;

OR

RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;
}

Output
V$DATABASE_BLOCK_CORRUPTION is updated with the corrupt blocks.


In 12c the NOLOGGING blocks identified by rman validate are in new view v$nonlogged_block:

 RMAN keeps corruption information in the control file (v$database_block_corruption, v$backup_corruption)

1) validate all database files and archived redo log files for physical and logical corruption:

   BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

Physical and Logical Block Corruptions. All you wanted to know about it. (Doc ID 840978.1)

  
 ORA-1499. Table/Index row count mismatch (Doc ID 563070.1)

Trial Recovery - Recover database Test (Doc ID 283262.1)

How to use TEST option for any RECOVER command ?

For example, you can start SQL*Plus and then issue any of the following commands:

RECOVER DATABASE TEST
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL TEST
RECOVER TABLESPACE TEST
RECOVER DATABASE UNTIL CANCEL TEST

 

By default, trial recovery always attempts to corrupt blocks in memory if this action allows trial recovery to proceed. In other words, trial recovery by default can corrupt an unlimited number of data blocks. You can specify the ALLOW n CORRUPTION clause on the RECOVER ... TEST statement to limit the number of data blocks trial recovery can corrupt in memory. For an example

SQL> RECOVER DATABASE TEST ALLOW n CORRUPTION;


-- where n is the number of blocks.

 

 

 RMAN : Block-Level Media Recovery - Concept & Example (Doc ID 144911.1)

 

RMAN> run {BACKUP VALIDATE DATABASE;}

 

SQL> select * from V$backup_corruption;

 

  Alternatively, you can use Data Recovery Advisor (DRA):

RMAN> list failure;

RMAN> repair failure preview;

RMAN> repair failure noprompt;

  

 

1) validate all database files and archived redo log files for physical and logical corruption:

   BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

2) to check individual data blocks, as shown in the following example:

   VALIDATE DATAFILE 4 BLOCK 10 TO 13;

3) validate backup sets:

   VALIDATE BACKUPSET 3;

The following RMAN command recovers the corrupted blocks:


1) recover all corrupted blocks reported in v$database_block_corruption

   RMAN> RECOVER CORRUPTION LIST;

2) recover individual blocks, see eg:

   RMAN> RECOVER DATAFILE 1 BLOCK 233, 235 DATAFILE 2 BLOCK 100 TO 200;
 

ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution (Doc ID 794505.1)

 The
 "VALIDATE" RMAN command is used to identify NOLOGGING blocks and 
populates the view v$database_block_corruption (versions lower than 12c)
 and v$nonlogged_block (12c and greater). 

In version 12.2 the new command "validate .. nonlogged block" is available to validate NOLOGGING Blocks.  In the next example datafiles 5 and 6 have nologged blocks:

RMAN> validate database nonlogged block;

Monitoring NOLOGGING Operations

The RMAN command "REPORT UNRECOVERABLE" reports when a data file has been changed by a NOLOGGING operation and the datafile has not been backed up since then. Example:

RMAN> report unrecoverable;

In 12c there is the option to use the RMAN command: RECOVER NONLOGGED BLOCK with DATAFILE,TABLESPACE,DATABASE granularity.  An example for DATABASE is:

RMAN> RECOVER DATABASE NONLOGGED BLOCK;

To avoid the problem from being introduced, force logging in the PRIMARY database with:

alter database force logging;

 

 

 DBMS_REPAIR SCRIPT (Doc ID 556733.1)

ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors (Doc ID 293515.1)

Apply APEX 5.1.4 to 5.1.3 in PDB

1. download p25341386_511_Generic.zip

- I was wrong to use the full set copy for upgrade.

2. 

SQL> alter session set container=pdb1 ;

Session altered.

SQL> select VERSION from dba_registry where comp_id='APEX';

VERSION
------------------------------
5.1.3.00.05

3. SQL> @apxpatch.sql

PL/SQL procedure successfully completed.

...Validating Application Express
...(22:36:14) Starting validate_apex for APEX_050100
...(22:36:17) Checking missing sys privileges
...(22:36:18) Key object existence check
...(22:36:18) Setting DBMS Registry for APEX to valid
...(22:36:18) Exiting validate_apex

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

timing for: Complete Patch
Elapsed: 00:05:41.47


4. load images


SQL> !pwd
/home/oracle/Downloads/APEX/p26795231_514_Generic/patch

SQL> !ls -ld images
drwxr-xr-x. 35 oracle oracle 32768 Dec 14 18:59 images

SQL> @apxldimg.sql /home/oracle/Downloads/APEX/p26795231_514_Generic/patch

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.





1 row selected.





1 row selected.





1 row selected.


PL/SQL procedure successfully completed.





1 row selected.


PL/SQL procedure successfully completed.

. Loading images directory: /home/oracle/Downloads/APEX/p26795231_514_Generic/patch/images

Directory created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.


Directory dropped.

timing for: Load Images
Elapsed: 00:06:19.75



SQL> select VERSION from dba_registry where comp_id='APEX';

VERSION
------------------------------
5.1.4.00.08

1 row selected.

No more below error any more !

References:

http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-514-patch-set-notes-4124555.html#GUID-68F6035F-90E8-4059-8902-AC6ADE3DAB3E

Zabbix 3.4 for Postgresql 9.6 installation in Centos 7

This installation extended to my home hour, since both Postgresql and Zabbix are new to me.

Followed few articles without much difficulty.

https://www.zabbix.com/documentation/3.4/manual/installation/install_from_packages/rhel_centos

follow above, but replace 'mysql' with 'pgsql' 

yum install zabbix-server-pgsql

yum install zabbix-proxy-pgsql

yum install zabbix-web-pgsql 

below is my final packages started with zabbix

[oracle@hmc-P55A-UD3 zabbix]$ rpm -qa |grep zabbix
zabbix-get-3.4.4-2.el7.x86_64
zabbix-web-3.4.4-2.el7.noarch
zabbix-release-3.4-2.el7.noarch
zabbix-proxy-pgsql-3.4.4-2.el7.x86_64
zabbix-web-pgsql-3.4.4-2.el7.noarch
zabbix-server-pgsql-3.4.4-2.el7.x86_64
zabbix-agent-3.4.4-2.el7.x86_64
 

Zabbix 3 Install on CEntOS 7 with PostgreSQL 9.5

database creation

postgres createuser --pwprompt zabbix

postgres createdb -O zabbix zabbix

logon as postgres

 zcat /usr/share/doc/zabbix-proxy-pgsql*/schema.sql.gz |  psql -U zabbix -d zabbix

-- /etc/httpd/conf.d/zabbix.conf. 

set php_value date.timezone accordingly 

Problem starts when I started in web page installation step.

Problem  1  
"system error occurred. please contact zabbix administrator"


Then I realized the agent was not installed and started, but this didn't solve the problem.

Problem  2
 
From the agent logfile /var/log/zabbix/zabbix_agentd.log, it says zabbix-agent failed to connect to 10051


Then I realized the

problem 3  - the zabbix-server failed to start

although the systemctl start zabbix-server.service return 0 , and didn't show any errror on screen.

Errors in var/log/zabbix/zabbix_serverd.log are :

7676:20171206:223117.081 Starting Zabbix Server. Zabbix 3.4.4 (revision 74338).
7676:20171206:223117.081 ****** Enabled features ******
7676:20171206:223117.081 SNMP monitoring: YES
7676:20171206:223117.081 IPMI monitoring: YES
7676:20171206:223117.081 Web monitoring: YES
7676:20171206:223117.081 VMware monitoring: YES
7676:20171206:223117.081 SMTP authentication: YES
7676:20171206:223117.081 Jabber notifications: YES
7676:20171206:223117.081 Ez Texting notifications: YES
7676:20171206:223117.081 ODBC: YES
7676:20171206:223117.081 SSH2 support: YES
7676:20171206:223117.081 IPv6 support: YES
7676:20171206:223117.081 TLS support: YES
7676:20171206:223117.081 ******************************
7676:20171206:223117.081 using configuration file: /etc/zabbix/zabbix_server.conf
7676:20171206:223117.090 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: relation "users" does not exist
LINE 1: select userid from users limit 1
^
[select userid from users limit 1]
7676:20171206:223117.090 cannot use database "zabbix": database is not a Zabbix database
zabbix_server [7686]: cannot open log: cannot create semaphore set: [28] No space left on device
zabbix_server [7691]: cannot open log: cannot create semaphore set: [28] No space left on device


Problem 4 -  PGRES_FATAL_ERROR:ERROR: relation "users" does not exist

Googled this crucial article to my final success,  which tell me should leave default (blank) for DBSchema inside the /etc/zabbix/zabbix_server.conf


Next I attempted to solve  "cannot create semaphore set", but even the zabbix-server is stopped, the tail of logfile still moved on. I guess something not clean with IPC, to safe time , I rebooted my PC, which worked!

The zabbix-server is up without error.


Next, I almost faced no challenges with web page installation steps, as shown below.

Again , there Database schema is important, leave as blank. both public and named as zabbix not working. 

The default login ID is Admin (case sensitive) and password is zabbix

empty dashboard at first logon

enable local serer for moniting

Finally see something.

key settings for jmeter3.3 JDBC connection to Oracle database (11.2.0.4)

Tested both URL works:
jdbc:oracle:thin:@//localhost:1523/ora11g
jdbc:oracle:thin:@localhost:1523/ora11g

JDBC URL takes forms:

• jdbc:oracle:thin:@host:port /databaseName
• jdbc:oracle:thin:@host:port :serviceName

Note that the "validation query" in my case should choose "select 1 from dual", otherwise I was getting "Cannot create PoolableConnectionFactory (ORA-00923: FROM
keyword not found where expected"

ojdbc8.jar is the jdbc driver downloaded from oracle.com
 

For SQL query , note that
1. There is no semi-colon (;) at the end of the sql statment, otherwise there is ora error code saying "ORA-00911: invalid character"



Above are key things I encountered when first time playing jmeter & oracle.

Also tested okay for 12.2c PDB.