config HTTPS for APEX 5.1 running on SE2

In my case, self-signed certification is used.



High level steps  are;

1. Relink SE Oracle binary in order to support TCPS. Skip this step for EE

2. Create wallet to store self-signed cert

3. Create self-signed cert

4. Verify TCPS connection over SQL*NET

5. Configure http2 for APEX Embedded web server





Below are details.



Step 1 - Enable SE TCPS



Follow Oracle doc  -  How To Enable TCPS Support For Oracle Standard Edition (Doc ID 1457854.1)


-- current lib file

/software/oracle/product/12.2.0/lib> ls -l libntcps*

-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a



--installer jar files

:/software/oracle/admin/INSTALLER/database/stage/Components/oracle.network.rsf/12.2.0.1.0/1/DataFiles> ls -l

total 4540

-rwxr-xr-x 1 oracle oracle 722400 Jan 26 2017 filegroup1.jar

-rwxr-xr-x 1 oracle oracle 3708288 Jan 26 2017 filegroup2.jar

-rwxr-xr-x 1 oracle oracle 212871 Jan 26 2017 filegroup3.jar





> pwd

/software/oracle/admin/INSTALLER/database/stage/Components/oracle.network.rsf/12.2.0.1.0/1/DataFiles

> $ORACLE_HOME/jdk/bin/jar tvf filegroup2.jar |grep libntcps

356034 Thu Dec 15 07:31:24 SGT 2016 lib/libntcps12_ee.a.dbl



> $ORACLE_HOME/jdk/bin/jar xvf ~/filegroup2.jar lib/libntcps12_ee.a.dbl

inflated: lib/libntcps12_ee.a.dbl









> ls -la libntcps*

-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a

-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl

> cp -p libntcps12.a libntcps12.a.orig



-- relink



> sqlplus / as sysdba



SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 24 14:39:17 2018



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





Connected to:

Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production



SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

> ps -f |grep tns

oracle 20494 20272 0 14:39 pts/0 00:00:00 grep --color=auto tns

> ps -fu oracle

UID PID PPID C STIME TTY TIME CMD

oracle 14048 14046 0 11:20 ? 00:00:01 sshd: oracle@pts/1

oracle 14049 14048 0 11:20 pts/1 00:00:00 -ksh

oracle 19604 1 0 14:04 ? 00:00:00 /software/oracle/product/12.2.0/bin/tnslsnr LISTENER_ORCL -inherit

oracle 20271 20269 0 14:28 ? 00:00:00 sshd: oracle@pts/0

oracle 20272 20271 0 14:28 pts/0 00:00:00 -ksh

oracle 20500 20272 0 14:40 pts/0 00:00:00 ps -fu oracle

> lsnrctl stop LISTENER_ORCL



LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-JAN-2018 14:40:10



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



Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1588)))

The command completed successfully





> relink all

writing relink log to: /software/oracle/product/12.2.0/install/relink.log







> ls -la libntcps*

-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a

-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a.orig

-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl





> ls -l /software/oracle/product/12.2.0/install/relink.log

-rw-r--r-- 1 oracle oracle 169637 Jan 24 14:41 /software/oracle/product/12.2.0/install/relink.log









#highlighted are updated files

ls -lrt





...







-rw-r--r-- 1 oracle oracle 22215298 Jan 24 12:01 libcommon12.a

-rw-r--r-- 1 oracle oracle 36700774 Jan 24 12:01 libgeneric12.a

-rwxr-xr-x 1 oracle oracle 2032800 Jan 24 12:01 libasmclntsh12.so

-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl

-rw-r--r-- 1 oracle oracle 14683 Jan 24 14:40 libskgxn2.so

-rw------- 1 oracle oracle 137 Jan 24 14:40 ldflagsO

-rw-r--r-- 1 oracle oracle 137 Jan 24 14:40 ldflags

-rw-r--r-- 1 oracle oracle 2872 Jan 24 14:40 ntcontab.o

-rw-r--r-- 1 oracle oracle 1968 Jan 24 14:40 nnfgt.o

-rw-r--r-- 1 oracle oracle 9619442 Jan 24 14:40 libn12.a

-rwxr-xr-x 1 oracle oracle 8033736 Jan 24 14:40 libclntshcore.so.12.1

-rw-r--r-- 1 oracle oracle 1687689 Jan 24 14:40 clntshcore.map

lrwxrwxrwx 1 oracle oracle 21 Jan 24 14:40 libclntshcore.so -> libclntshcore.so.12.1

-rwxr-xr-x 1 oracle oracle 71653096 Jan 24 14:40 libclntsh.so.12.1

-rw-r--r-- 1 oracle oracle 5080271 Jan 24 14:40 clntsh.map

lrwxrwxrwx 1 oracle oracle 17 Jan 24 14:40 libclntsh.so -> libclntsh.so.12.1

lrwxrwxrwx 1 oracle oracle 12 Jan 24 14:40 libclntsh.so.11.1 -> libclntsh.so

lrwxrwxrwx 1 oracle oracle 12 Jan 24 14:40 libclntsh.so.10.1 -> libclntsh.so

-rwxr-xr-x 1 oracle oracle 2222528 Jan 24 14:40 libocci.so.12.1

lrwxrwxrwx 1 oracle oracle 15 Jan 24 14:40 libocci.so -> libocci.so.12.1

-rwxr-xr-x 1 oracle oracle 2553264 Jan 24 14:40 libagtsh.so.1.0

lrwxrwxrwx 1 oracle oracle 15 Jan 24 14:40 libagtsh.so -> libagtsh.so.1.0

-rwxr-xr-x 1 oracle oracle 194936 Jan 24 14:40 libsrvmm2.so

-rw-r--r-- 1 oracle oracle 1192447 Jan 24 14:40 libskgxp12.so





Step 2 - create wallet



Use oracle wallet manager  - owm to create an empty wallet.



Step 3 - create self-signed cert



ORCL:localhost:/software/oracle/admin/ORCL/wallet> orapki wallet add -wallet ./ -pwd Oracle123$ \
> -dn "CN=`hostname`, OU=DBA, O=mm, L=SG, ST=SG, C=SG" \
> -keysize 1024 -self_signed -validity 3650
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.
ORCL:localhost:/software/oracle/admin/ORCL/wallet> orapki wallet display -wallet ./ -pwd Oracle123$
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
Subject: C=SG,ST=SG,L=SG,O=mm,OU=dba,CN=dbmate-cert
User Certificates:
Subject: CN=localhost,OU=DBA,O=mm,L=SG,ST=SG,C=SG
Trusted Certificates:
Subject: CN=localhost,OU=DBA,O=mm,L=SG,ST=SG,C=SG









Step 4 - verify TCPS



-- config sqlnet

ORCL:localhost:/software/oracle/product/12.2.0/network/admin> cat sqlnet.ora

# sqlnet.ora Network Configuration File: /software/oracle/product/12.2.0/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.



NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)



SSL_CLIENT_AUTHENTICATION = FALSE



WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /software/oracle/admin/ORCL/wallet )

)

)



ORCL:localhost:/software/oracle/product/12.2.0/network/admin> cat listener.ora

# listener.ora Network Configuration File: /software/oracle/product/12.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.



LISTENER_ORCL =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1588))

(ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)





SSL_CLIENT_AUTHENTICATION = FALSE



WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /software/oracle/admin/ORCL/wallet )

)

)



--tnsnames.ora



ORCL_SSL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL)

)

)













ORCL:localhost:/software/oracle/product/12.2.0/network/admin> sqlplus perfstat@ORCL_SSL



SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 26 11:00:54 2018



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



Enter password:

Last Successful login time: Fri Jan 26 2018 11:00:14 +08:00



Connected to:

Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production



SQL> select sys_context('userenv','network_protocol') from dual;



SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')

--------------------------------------------------------------------------------

tcps



SQL> exit

Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

ORCL:localhost:/software/oracle/product/12.2.0/network/admin> tnsping ORCL_SSL



TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 26-JAN-2018 11:01:17



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



Used parameter files:

/software/oracle/product/12.2.0/network/admin/sqlnet.ora





Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))

OK (10 msec)







Step 5. Configure http2 for APEX Embedded web server



Set http2-port and http2-protocol in the XDB configuration:
See Note 942945.1



5.1  Set dispatcher for TCPS in database parameter file


*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)','(PROTOCOL=TCPS) (SERVICE=ORCLXDB)'




5.2  set apex /Manage Instance/Requires HTTPS = always.





 My database is 12.2c , the APEX http port was 8888,  I am using same port for https , as no more HTTP will be supported.


  SQL> select dbms_xdb_config.gethttpport() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
8888

SQL> exec dbms_xdb_config.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL> select dbms_xdb_config.gethttpport() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
8080

SQL> call DBMS_XDB_CONFIG.SETLISTENERENDPOINT(2, null, 8888,2);

Call completed.

SQL> select dbms_xdb_config.gethttpport() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
8080




select extractValue(value(x),'/httpconfig/http2-protocol', 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') "Protocol"
2 , extractValue(value(x),'/httpconfig/http2-port', 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') "Port#"
3 from table(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(),'/xdbconfig/sysconfig/protocolconfig/httpconfig'))) x
4 /

Protocol
--------------------------------------------------------------------------------
Port#
--------------------------------------------------------------------------------
tcps
8888







Cheers!

bash commands for QNAP

This is useful.

find /share/Multimedia/ –name *.m4a | while read foo; do rm “$foo” ;done

https://www.ripcaster.co.uk/node/772 

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)