tag:blogger.com,1999:blog-180318512024-03-06T11:34:59.539+08:00MiDBABuild an Oracle DBA on a firm foundation of sound practices and technical knowledge!MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comBlogger566125tag:blogger.com,1999:blog-18031851.post-76151401580572758392019-12-15T22:07:00.003+08:002019-12-15T22:07:50.215+08:00apt update public key issueWhen running "apt update" in raspberry pi, facing below warning message<br />
<br /><i>W: An error occurred during the signature verification. The repository is not updated and the previous index files will be used. GPG error: https://packagecloud.io/headmelted/codebuilds/debian stretch InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 0CC3FD642696BFC8</i><br />Following the solution from <br />refer https://askubuntu.com/questions/13065/how-do-i-fix-the-gpg-error-no-pubkey , which worked for me. Cheers.<br />
<br />sudo apt-get install debian-keyring<br /><br />- below command took more than 1 minutes <br /><br />pi@raspberrypi:~ $ sudo gpg --keyserver pgp.mit.edu --recv-keys 0CC3FD642696BFC8<br />gpg: directory '/root/.gnupg' created<br />gpg: keybox '/root/.gnupg/pubring.kbx' created<br />gpg: /root/.gnupg/trustdb.gpg: trustdb created<br />gpg: key 8D9940AE59B46979: public key "https://packagecloud.io/headmelted/code-oss (https://packagecloud.io/docs#gpg_signing) <support packagecloud.io="">" imported<br />gpg: Total number processed: 1<br />gpg: imported: 1<br /><br /><br />pi@raspberrypi:~ $ sudo gpg --armor --export 0CC3FD642696BFC8 | apt-key add -<br />E: This command can only be used by root.<br /><br />pi@raspberrypi:~ $ sudo su -<br />root@raspberrypi:~# id<br />uid=0(root) gid=0(root) groups=0(root)<br />root@raspberrypi:~# gpg --armor --export 0CC3FD642696BFC8 | apt-key add -<br />OK<br /><br />Verified running "apt update" without any warning message now. </support><br />
<br />MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-26217268942031948142019-10-31T22:52:00.000+08:002019-10-31T22:52:14.426+08:00About RAC SCAN IPA SCAN (Single Client Access Name) is a special case of VIP. The SCAN should also be defined in DNS, and <em>not</em>
assigned to any host or interface. There should be three IPs
associated with the SCAN name in DNS, and the DNS entry should be
defined so that one of the three IPs is returned each time DNS is
queried, in a round robin fashion.<br />
<br />
At clusterware startup time, each of the three VIPs that make up the
SCAN will be assigned to a different node in the cluster. (Except in
the special case of a two node cluster, one of the nodes will have a 2
SCAN VIPs assigned to it.) The point of the SCAN, is that no matter how
many nodes are added to or removed from the cluster, all the Net
Service Name definitions in your tnsnames.ora (or LDAP equivalent) will
not need to ever change, because they all refer to the SCAN, which
doesn't change, regardless of how many node additions or drops are made
to the cluster. <br />
<br />
Additionally, you may have the SCAN defined as:
<code>rac-scan</code> with three IPs, <code>10.1.1.7, 10.1.1.8, 10.1.1.9</code>. Again, the DNS definition would be defined so those IPs are served up in a round robin order.MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-83839223429225239962019-10-29T22:04:00.001+08:002019-10-29T22:04:27.345+08:00CRS-5019:All OCR locations are on ASM disk groups [DATA], and none of these disk groups are mounted. In my case , from ASM alert.log , actually it is due to ASM failed to start because of ORA-00600 as shown below. <br />
<br />
<br />
ORA-00600: internal error code, arguments: [kfcema35], [0], [742], [0], [4368], [], [], [], [], [], [], []CRS-2674: Start of 'ora.asm' on 'rac1' failed<br />
<br />
<br />
However, I don't have solution to overcome this ora-00600. In the end, I decided to rebuild the cluster. MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-54993145708650080592019-10-29T21:54:00.000+08:002019-10-29T21:54:19.402+08:00about data guard FAL setting<span style="color: #333333; font-family: "Helvetica Neue", "Segoe UI", Helvetica, Arial, sans-serif; font-size: 12px;">FAL_SERVER
= Specifies one or more Oracle Net service names for the databases from
which this standby database can fetch (request) missing archived redo
log files. <br /><br />FAL_CLIENT = This parameter is no longer required. If
it is not set, the fetch archive log (FAL) server will obtain the
client's network address from the LOG_ARCHIVE_DEST_n parameter that
corresponds to the client's DB_UNIQUE_NAME.</span><br />
<br />
<br />
<span style="color: #333333; font-family: "Helvetica Neue", "Segoe UI", Helvetica, Arial, sans-serif; font-size: 12px;">With broker configured</span><br />
<br />
<span style="color: #333333; font-family: "Helvetica Neue", "Segoe UI", Helvetica, Arial, sans-serif; font-size: 12px;">Yes the broker handles it. FAL_CLIENT would not be set at all but
FAL_SERVER would default itself to the primary db_unique_name. That´s
the beauty of the Broker - it really sorts these things out for you, so
in my experience the best setup with Broker is to not set anything
before hand, but let the Broker sort all those parameters for you. And
if you need to change something, always do it from the dgmgrl interface -
not sql*plus - as that would cause inconsistencies with the Broker
configuration. </span>MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-45469332844986023242019-10-03T23:21:00.001+08:002019-10-03T23:21:24.595+08:00Key things leared from VNC Server setup1.Commands: to setup different desktop. Note that the desktops used by VNC server shall not be the same as that for direct access' desktop. e.g. I use KDE for local desktop, then my remote desktop is gnome.<br />
<br />
-- for gnome desktop<br />
<br />
xsetroot -solid grey<br />vncconfig -iconic &<br /><span style="background-color: yellow;">dbus-launch --exit-with-session gnome-session &</span><br />
<br />
<span style="background-color: yellow;"></span><br />
<span style="background-color: yellow;"><span style="background-color: white;">-- for xfce4</span></span><br />
<br />
<span style="background-color: yellow;"><span style="background-color: white;"> startxfce4 &</span></span><br />-- for kde<br />
startkde &<br />
<br />
2. commands to start/stop vncserver. Note that avoid to use sudo, otherwise the vncserver will be started as root instead of logon user. In the end, it causes file permission issue of .Xauthority<br />
<br />
start : vncserver<br />stop : vncserver -kill :1<br />
<br />
<br />
Ref: https://www.teknotut.com/en/install-vnc-server-with-gnome-display-on-ubuntu-18-04/<br />
<br />MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-49965386189856849922018-01-28T21:40:00.000+08:002018-01-28T21:40:27.454+08:00config HTTPS for APEX 5.1 running on SE2In my case, self-signed certification is used.<br />
<br />
<br />
<br />
High level steps are;<br />
<br />
1. Relink SE Oracle binary in order to support TCPS. Skip this step for EE<br />
<br />
2. Create wallet to store self-signed cert<br />
<br />
3. Create self-signed cert<br />
<br />
4. Verify TCPS connection over SQL*NET<br />
<br />
5. Configure http2 for APEX Embedded web server<br />
<br />
<br />
<br />
<br />
<br />
Below are details.<br />
<br />
<br />
<br />
Step 1 - Enable SE TCPS<br />
<br />
<br />
<br />
Follow Oracle doc - How To Enable TCPS Support For Oracle Standard Edition (Doc ID 1457854.1)<br />
<br />
<br />
-- current lib file<br />
<br />
/software/oracle/product/12.2.0/lib> ls -l libntcps*<br />
<br />
-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a<br />
<br />
<br />
<br />
--installer jar files<br />
<br />
:/software/oracle/admin/INSTALLER/database/stage/Components/oracle.network.rsf/12.2.0.1.0/1/DataFiles> ls -l<br />
<br />
total 4540<br />
<br />
-rwxr-xr-x 1 oracle oracle 722400 Jan 26 2017 filegroup1.jar<br />
<br />
-rwxr-xr-x 1 oracle oracle 3708288 Jan 26 2017 filegroup2.jar<br />
<br />
-rwxr-xr-x 1 oracle oracle 212871 Jan 26 2017 filegroup3.jar<br />
<br />
<br />
<br />
<br />
<br />
> pwd<br />
<br />
/software/oracle/admin/INSTALLER/database/stage/Components/oracle.network.rsf/12.2.0.1.0/1/DataFiles<br />
<br />
> $ORACLE_HOME/jdk/bin/jar tvf filegroup2.jar |grep libntcps<br />
<br />
356034 Thu Dec 15 07:31:24 SGT 2016 lib/libntcps12_ee.a.dbl<br />
<br />
<br />
<br />
> $ORACLE_HOME/jdk/bin/jar xvf ~/filegroup2.jar lib/libntcps12_ee.a.dbl<br />
<br />
inflated: lib/libntcps12_ee.a.dbl<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
> ls -la libntcps*<br />
<br />
-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a<br />
<br />
-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl<br />
<br />
> cp -p libntcps12.a libntcps12.a.orig<br />
<br />
<br />
<br />
-- relink<br />
<br />
<br />
<br />
> sqlplus / as sysdba<br />
<br />
<br />
<br />
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 24 14:39:17 2018<br />
<br />
<br />
<br />
Copyright (c) 1982, 2016, Oracle. All rights reserved.<br />
<br />
<br />
<br />
<br />
<br />
Connected to:<br />
<br />
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production<br />
<br />
<br />
<br />
SQL> shutdown immediate<br />
<br />
Database closed.<br />
<br />
Database dismounted.<br />
<br />
ORACLE instance shut down.<br />
<br />
SQL> exit<br />
<br />
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production<br />
<br />
> ps -f |grep tns<br />
<br />
oracle 20494 20272 0 14:39 pts/0 00:00:00 grep --color=auto tns<br />
<br />
> ps -fu oracle<br />
<br />
UID PID PPID C STIME TTY TIME CMD<br />
<br />
oracle 14048 14046 0 11:20 ? 00:00:01 sshd: oracle@pts/1<br />
<br />
oracle 14049 14048 0 11:20 pts/1 00:00:00 -ksh<br />
<br />
oracle 19604 1 0 14:04 ? 00:00:00 /software/oracle/product/12.2.0/bin/tnslsnr LISTENER_ORCL -inherit<br />
<br />
oracle 20271 20269 0 14:28 ? 00:00:00 sshd: oracle@pts/0<br />
<br />
oracle 20272 20271 0 14:28 pts/0 00:00:00 -ksh<br />
<br />
oracle 20500 20272 0 14:40 pts/0 00:00:00 ps -fu oracle<br />
<br />
> lsnrctl stop LISTENER_ORCL<br />
<br />
<br />
<br />
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-JAN-2018 14:40:10<br />
<br />
<br />
<br />
Copyright (c) 1991, 2016, Oracle. All rights reserved.<br />
<br />
<br />
<br />
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1588)))<br />
<br />
The command completed successfully<br />
<br />
<br />
<br />
<br />
<br />
> relink all<br />
<br />
writing relink log to: /software/oracle/product/12.2.0/install/relink.log<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
> ls -la libntcps*<br />
<br />
-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a<br />
<br />
-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a.orig<br />
<br />
-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl<br />
<br />
<br />
<br />
<br />
<br />
> ls -l /software/oracle/product/12.2.0/install/relink.log<br />
<br />
-rw-r--r-- 1 oracle oracle 169637 Jan 24 14:41 /software/oracle/product/12.2.0/install/relink.log<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
#highlighted are updated files<br />
<br />
ls -lrt<br />
<br />
<br />
<br />
<br />
<br />
...<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
-rw-r--r-- 1 oracle oracle 22215298 Jan 24 12:01 libcommon12.a<br />
<br />
-rw-r--r-- 1 oracle oracle 36700774 Jan 24 12:01 libgeneric12.a<br />
<br />
-rwxr-xr-x 1 oracle oracle 2032800 Jan 24 12:01 libasmclntsh12.so<br />
<br />
-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl<br />
<br />
-rw-r--r-- 1 oracle oracle 14683 Jan 24 14:40 libskgxn2.so<br />
<br />
-rw------- 1 oracle oracle 137 Jan 24 14:40 ldflagsO<br />
<br />
-rw-r--r-- 1 oracle oracle 137 Jan 24 14:40 ldflags<br />
<br />
-rw-r--r-- 1 oracle oracle 2872 Jan 24 14:40 ntcontab.o<br />
<br />
-rw-r--r-- 1 oracle oracle 1968 Jan 24 14:40 nnfgt.o<br />
<br />
-rw-r--r-- 1 oracle oracle 9619442 Jan 24 14:40 libn12.a<br />
<br />
-rwxr-xr-x 1 oracle oracle 8033736 Jan 24 14:40 libclntshcore.so.12.1<br />
<br />
-rw-r--r-- 1 oracle oracle 1687689 Jan 24 14:40 clntshcore.map<br />
<br />
lrwxrwxrwx 1 oracle oracle 21 Jan 24 14:40 libclntshcore.so -> libclntshcore.so.12.1<br />
<br />
-rwxr-xr-x 1 oracle oracle 71653096 Jan 24 14:40 libclntsh.so.12.1<br />
<br />
-rw-r--r-- 1 oracle oracle 5080271 Jan 24 14:40 clntsh.map<br />
<br />
lrwxrwxrwx 1 oracle oracle 17 Jan 24 14:40 libclntsh.so -> libclntsh.so.12.1<br />
<br />
lrwxrwxrwx 1 oracle oracle 12 Jan 24 14:40 libclntsh.so.11.1 -> libclntsh.so<br />
<br />
lrwxrwxrwx 1 oracle oracle 12 Jan 24 14:40 libclntsh.so.10.1 -> libclntsh.so<br />
<br />
-rwxr-xr-x 1 oracle oracle 2222528 Jan 24 14:40 libocci.so.12.1<br />
<br />
lrwxrwxrwx 1 oracle oracle 15 Jan 24 14:40 libocci.so -> libocci.so.12.1<br />
<br />
-rwxr-xr-x 1 oracle oracle 2553264 Jan 24 14:40 libagtsh.so.1.0<br />
<br />
lrwxrwxrwx 1 oracle oracle 15 Jan 24 14:40 libagtsh.so -> libagtsh.so.1.0<br />
<br />
-rwxr-xr-x 1 oracle oracle 194936 Jan 24 14:40 libsrvmm2.so<br />
<br />
-rw-r--r-- 1 oracle oracle 1192447 Jan 24 14:40 libskgxp12.so<br />
<br />
<br />
<br />
<br />
<br />
Step 2 - create wallet<br />
<br />
<br />
<br />
Use oracle wallet manager - owm to create an empty wallet.<br />
<br />
<br />
<br />
Step 3 - create self-signed cert<br />
<br />
<br />
<br />
ORCL:localhost:/software/oracle/admin/ORCL/wallet> orapki wallet add -wallet ./ -pwd Oracle123$ \<br />
> -dn "CN=`hostname`, OU=DBA, O=mm, L=SG, ST=SG, C=SG" \<br />
> -keysize 1024 -self_signed -validity 3650<br />
Oracle PKI Tool : Version 12.2.0.1.0<br />
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.<br />
<br />
Operation is successfully completed.<br />
ORCL:localhost:/software/oracle/admin/ORCL/wallet> orapki wallet display -wallet ./ -pwd Oracle123$<br />
Oracle PKI Tool : Version 12.2.0.1.0<br />
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.<br />
<br />
Requested Certificates:<br />
Subject: C=SG,ST=SG,L=SG,O=mm,OU=dba,CN=dbmate-cert<br />
User Certificates:<br />
Subject: CN=localhost,OU=DBA,O=mm,L=SG,ST=SG,C=SG<br />
Trusted Certificates:<br />
Subject: CN=localhost,OU=DBA,O=mm,L=SG,ST=SG,C=SG<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Step 4 - verify TCPS<br />
<br />
<br />
<br />
-- config sqlnet<br />
<br />
ORCL:localhost:/software/oracle/product/12.2.0/network/admin> cat sqlnet.ora<br />
<br />
# sqlnet.ora Network Configuration File: /software/oracle/product/12.2.0/network/admin/sqlnet.ora<br />
<br />
# Generated by Oracle configuration tools.<br />
<br />
<br />
<br />
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)<br />
<br />
<br />
<br />
SSL_CLIENT_AUTHENTICATION = FALSE<br />
<br />
<br />
<br />
WALLET_LOCATION =<br />
<br />
(SOURCE =<br />
<br />
(METHOD = FILE)<br />
<br />
(METHOD_DATA =<br />
<br />
(DIRECTORY = /software/oracle/admin/ORCL/wallet )<br />
<br />
)<br />
<br />
)<br />
<br />
<br />
<br />
ORCL:localhost:/software/oracle/product/12.2.0/network/admin> cat listener.ora<br />
<br />
# listener.ora Network Configuration File: /software/oracle/product/12.2.0/network/admin/listener.ora<br />
<br />
# Generated by Oracle configuration tools.<br />
<br />
<br />
<br />
LISTENER_ORCL =<br />
<br />
(DESCRIPTION_LIST =<br />
<br />
(DESCRIPTION =<br />
<br />
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1588))<br />
<br />
(ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589))<br />
<br />
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))<br />
<br />
)<br />
<br />
)<br />
<br />
<br />
<br />
<br />
<br />
SSL_CLIENT_AUTHENTICATION = FALSE<br />
<br />
<br />
<br />
WALLET_LOCATION =<br />
<br />
(SOURCE =<br />
<br />
(METHOD = FILE)<br />
<br />
(METHOD_DATA =<br />
<br />
(DIRECTORY = /software/oracle/admin/ORCL/wallet )<br />
<br />
)<br />
<br />
)<br />
<br />
<br />
<br />
--tnsnames.ora<br />
<br />
<br />
<br />
ORCL_SSL =<br />
<br />
(DESCRIPTION =<br />
<br />
(ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589))<br />
<br />
(CONNECT_DATA =<br />
<br />
(SERVER = DEDICATED)<br />
<br />
(SERVICE_NAME = ORCL)<br />
<br />
)<br />
<br />
)<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
ORCL:localhost:/software/oracle/product/12.2.0/network/admin> sqlplus perfstat@ORCL_SSL<br />
<br />
<br />
<br />
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 26 11:00:54 2018<br />
<br />
<br />
<br />
Copyright (c) 1982, 2016, Oracle. All rights reserved.<br />
<br />
<br />
<br />
Enter password:<br />
<br />
Last Successful login time: Fri Jan 26 2018 11:00:14 +08:00<br />
<br />
<br />
<br />
Connected to:<br />
<br />
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production<br />
<br />
<br />
<br />
SQL> select sys_context('userenv','network_protocol') from dual;<br />
<br />
<br />
<br />
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')<br />
<br />
--------------------------------------------------------------------------------<br />
<br />
tcps<br />
<br />
<br />
<br />
SQL> exit<br />
<br />
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production<br />
<br />
ORCL:localhost:/software/oracle/product/12.2.0/network/admin> tnsping ORCL_SSL<br />
<br />
<br />
<br />
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 26-JAN-2018 11:01:17<br />
<br />
<br />
<br />
Copyright (c) 1997, 2016, Oracle. All rights reserved.<br />
<br />
<br />
<br />
Used parameter files:<br />
<br />
/software/oracle/product/12.2.0/network/admin/sqlnet.ora<br />
<br />
<br />
<br />
<br />
<br />
Used TNSNAMES adapter to resolve the alias<br />
<br />
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))<br />
<br />
OK (10 msec)<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Step 5. Configure http2 for APEX Embedded web server<br />
<br />
<br />
<br />
Set http2-port and http2-protocol in the XDB configuration:<br />
See Note 942945.1<br />
<br />
<br />
<br />
5.1 Set dispatcher for TCPS in database parameter file<br />
<br />
<br />
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)','(PROTOCOL=TCPS) (SERVICE=ORCLXDB)'<br />
<br />
<br />
<br />
<br />
5.2 set apex /Manage Instance/Requires HTTPS = always.<br />
<br />
<br />
<br />
<br />
<br />
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.<br />
<br />
<br />
SQL> select dbms_xdb_config.gethttpport() from dual;<br />
<br />
DBMS_XDB_CONFIG.GETHTTPPORT()<br />
-----------------------------<br />
8888<br />
<br />
SQL> exec dbms_xdb_config.sethttpport(8080);<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> select dbms_xdb_config.gethttpport() from dual;<br />
<br />
DBMS_XDB_CONFIG.GETHTTPPORT()<br />
-----------------------------<br />
8080<br />
<br />
SQL> call DBMS_XDB_CONFIG.SETLISTENERENDPOINT(2, null, 8888,2);<br />
<br />
Call completed.<br />
<br />
SQL> select dbms_xdb_config.gethttpport() from dual;<br />
<br />
DBMS_XDB_CONFIG.GETHTTPPORT()<br />
-----------------------------<br />
8080<br />
<br />
<br />
<br />
<br />
select extractValue(value(x),'/httpconfig/http2-protocol', 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') "Protocol"<br />
2 , extractValue(value(x),'/httpconfig/http2-port', 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') "Port#"<br />
3 from table(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(),'/xdbconfig/sysconfig/protocolconfig/httpconfig'))) x<br />
4 /<br />
<br />
Protocol<br />
--------------------------------------------------------------------------------<br />
Port#<br />
--------------------------------------------------------------------------------<br />
tcps<br />
8888<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Cheers!<br />
<br />
<br />
<br />
<br />
<br />MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-20757223645868769672018-01-25T20:43:00.000+08:002018-01-25T20:43:01.690+08:00bash commands for QNAPThis is useful.<br />
<br />
<pre>find /share/Multimedia/ –name *.m4a | while read foo; do rm “$foo” ;done</pre>
<pre> </pre>
<pre> </pre>
<pre>https://www.ripcaster.co.uk/node/772 </pre>
MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-16431273315569233902018-01-16T00:13:00.001+08:002018-01-16T00:13:13.610+08:00ORA-20104 ORA-00942 error in APEX when click a todo item of team developmentable to create todo item but not able to view todo item due to below error in APEX 5.1.4<br />
<br />
ORA-20104: create_collection_from_queryb2<br />
Error:ORA-20104: create_collection_from_query<br />
ParseErr:ORA-00942: table or view does not exist<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbKehUapYzuTYFLRDmhqNL3h4i0T17y2gpzpQywqpHwsYQgSzY-ZCOYTMBWINfNsf1Xt7Gwv_gZR3NwDKQauutB9Wq0h98v7mcS4KjR_vaxdXGuK5gcPoRHE933lSGTbCW5GzFZA/s1600/2018-01-15+17_55_28-To+Do.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="384" data-original-width="939" height="261" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbKehUapYzuTYFLRDmhqNL3h4i0T17y2gpzpQywqpHwsYQgSzY-ZCOYTMBWINfNsf1Xt7Gwv_gZR3NwDKQauutB9Wq0h98v7mcS4KjR_vaxdXGuK5gcPoRHE933lSGTbCW5GzFZA/s640/2018-01-15+17_55_28-To+Do.png" width="640" /></a></div>
<br />
<br />
Googled and noticed below solution works.<br />
<br />
To re-create the table, it should be sufficient to do the following (in Application Builder):<br />
1) As a workspace admin, go to Administration -> Manage Service -> Set Workspace Preferences<br />
2) Check if “Enable File Repository” in “Team Development” is set to Yes.<br />
Set it to No, if file upload isn’t needed or keep the Yes.<br />
3) Click the Apply Changes button to re-create the apex$team_dev_files table in the workspace schema.<br />
<br />
--checked no table before apply the solution<br />
SQL> conn / as sysdba<br />
Connected.<br />
SQL> select * from dba_objects where object_name =upper('apex$team_dev_files');<br />
<br />
no rows selected<br />
<br />
<br />
Spent some and finally find the setting in 5.1.4 as shown below.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWS7UtEeXcVX4qRz782KEpQMzFAfP-0tOI5DOzy9r1bVvrIJwJALji6x16rRaZG-aySFOQ4vOI8BZDqzLfmZG9qc7EuWHvvSTF8_jueoQhdqyaoGVpC2FlCj8RoiGni1mvT1civA/s1600/2018-01-15+18_06_39-Feature+Configuration.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="280" data-original-width="1396" height="128" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWS7UtEeXcVX4qRz782KEpQMzFAfP-0tOI5DOzy9r1bVvrIJwJALji6x16rRaZG-aySFOQ4vOI8BZDqzLfmZG9qc7EuWHvvSTF8_jueoQhdqyaoGVpC2FlCj8RoiGni1mvT1civA/s640/2018-01-15+18_06_39-Feature+Configuration.png" width="640" /></a><br />
<br />
--checked again but I don't see the table is created.<br />
<br />
SQL> l<br />
1* select * from dba_objects where object_name =upper('apex$team_dev_files')<br />
SQL> /<br />
<br />
no rows selected<br />
<br />
SQL> exit<br />
<br />
<br />
Never mind, my problem solved.<br />
<div>
<br /></div>
MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-86168009759378706582018-01-08T23:46:00.004+08:002018-01-08T23:46:45.168+08:00Insert Chinese character into Oracle databaseMy database character is AL32UT8. Below works for me.<br />
<br />
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8<br />
> sqlplus / as sysdba<br />
<br />
<br />
<br />
SQL> select value10 from tab1 where config_name like '%SMS_TEMP%' AND VALUE1='132';<br />
<br />
VALUE10<br />
--------------------------------------------------------------------------------<br />
亲爱的顾客<br />
<br />
<br />
<div>
<br /></div>
MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-66339054963232058162018-01-01T12:14:00.000+08:002018-01-01T12:17:13.168+08:00How to identify "bad" index after analyze table validate structure cascade - from rdba or seg/obj.Do this.<br />
<div style="min-height: 8pt; padding: 0px;">
<br /></div>
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(<br />
to_number(trim(leading '0' from replace('&&rdba','0x','')),'XXXXXXXX')) AS block# FROM dual;<br />
RFILE# BLOCK#<br />
---------- ----------<br />
20 17036<br />
<br />
select segment_name, owner from dba_extents where file_id=20 and 17036 between block_id and block_id+blocks-1;<br />
<br />
<br />
or<br />
<br />
<br />
<br />
<div class="kmcodeblock" style="width: 95%;">
SQL> analyze table test validate structure cascade;<br />analyze table test validate structure cascade<br />*<br />ERROR at line 1:<br />ORA-01499: table/index cross reference failure - see trace file </div>
<br />
The associated trace file contains:<br />
<div class="kmcodeblock" style="width: 95%;">
Table/Index row count mismatch <br />table 6559 : index 10000, 0 <br />Index root = tsn: 6 rdba: 0x01400091</div>
It means: A table scan returned 6559 rows and an index scan returned 10000 rows. <br /> <br />"Index root" is the segment header information for the index: <br /><br />rdba: 0x01400091 is the Index segment header relative data block address. It is decimal 20971665 which is Rfile#=5 Block#=145 :<br />
<div class="kmcodeblock" style="width: 95%;">
SQL> select dbms_utility.data_block_address_file(20971665) "Rfile#" <br />2 ,dbms_utility.data_block_address_block(20971665) "Block#" <br />3 from dual; <br /><br />Rfile# Block# <br />---------- ---------- <br />5 145 </div>
<br />
<br />
<br />
Running the next query can identify the associated index:<br />
<div class="kmcodeblock" style="width: 95%;">
QUERY 1: <br /><br />SQL> select owner, segment_name, segment_type <br />2 from dba_segments <br />3 where header_file = 5 <br />4 and header_block = 145; <br /><br />OWNER SEGMENT_NAME SEGMENT_TYPE <br />-------- --------------- ------------------ <br />SCOTT I_TEST INDEX </div>
MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-85262352042983692522018-01-01T12:11:00.001+08:002018-01-01T12:11:16.880+08:00ora-00600 related docs for block corruption Mark some doc ID here for recent incident. A Happy New Year ahead to all!<br />
<br />
<span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;">ORA-600 [6006] ORA-600 [6856] During Startup Instance, Followed by Termination by SMON (Doc ID 549000.1)</span><br />
<br />
<span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;">Identify
the Corruption Extension for Block Corruption, Table/Index
Inconsistency, Data Dictionary and Lost Writes (Doc ID 836658.1)</span></span><br />
<br />
<blockquote>
<h4 class="km">
RMAN - Identify Datafile Block Corruptions</h4>
</blockquote>
<ul>
<li>To identify both <strong>Physical and Logical Block Corruptions</strong> use the "CHECK LOGICAL" option. The next command checks the complete database for both corruptions <span style="text-decoration: underline;">without actually doing a backup</span>:</li>
</ul>
<blockquote>
<blockquote>
<div class="kmcodeblock" style="width: 95%;">
<strong>$ rman target /</strong><br /> <strong>RMAN> backup check logical validate database;</strong></div>
</blockquote>
The next command checks the complete database for both corruptions <strong><span style="text-decoration: underline;">in a backup</span></strong>:<br />
<blockquote>
<div class="kmcodeblock" style="width: 95%;">
<strong>$ rman target /<br /> RMAN> backup check logical database</strong></div>
</blockquote>
</blockquote>
<ul>
<li>Check the view V$DATABASE_BLOCK_CORRUPTION to identify the block corruptions detected by RMAN. </li>
<li>Use <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=836658.1&id=472231.1">Doc ID 472231.1</a> (section "Step 2: Identify the corrupt segments") to identify all the Corrupted Objects in the Database reported by RMAN.</li>
<li>The above command can use <strong>PARALLELISM </strong>using multiple channels to make the validation faster. Reference <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=836658.1&id=472231.1">Doc ID 472231.1</a> for examples of PARALLELISM.</li>
<li>By Default RMAN backups, without the CHECK LOGICAL option, only detect Physical Block Corruptions.</li>
</ul>
<br />
<span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"></span><br />
<h3 class="km">
<span style="text-decoration: underline;">Identify corruption caused by LOST WRITES</span></h3>
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 <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=836658.1&id=840978.1">Doc ID 840978.1</a> for more information about Physical and Logical block corruption. <br /><br />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).<br /><br />
<ul>
<li>DBV/RMAN are not intended to identify inconsistency caused by LOST Write:</li>
</ul>
<div style="margin-left: 30px;">
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.<br /></div>
<ul>
<li>Media RECOVERY / Physical Standby</li>
</ul>
<div style="margin-left: 30px;">
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.</div>
<div style="margin-left: 30px;">
<br /></div>
<div style="margin-left: 30px;">
<br /></div>
<div style="margin-left: 30px;">
<br /></div>
<br />
<ul>
<li>EXPORT</li>
</ul>
<div style="margin-left: 30px;">
Running export may help to identify if a <span style="background-color: yellow;">
chained row</span> has an invalid pointer which may cause ORA-00600 [25027] or
ORA-00600 [kdsgrp1] but export <span style="background-color: yellow;">does not identify an old block version or
if there is a table/index mismatch.</span> In UNIX systems it can be done to
/dev/null:</div>
<div class="kmcodeblock" style="margin-left: 30px;">
exp system/manager full=y log=exp_validation.log file=/dev/null volsize=0</div>
<span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"></span><br />
<div style="margin-left: 30px;">
</div>
<br /><br />
<span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;">How to identify all the Corrupted Objects in the Database with RMAN (Doc ID 472231.1)</span><br />
<br />
<br />
<span style="font-size: x-small;">To make it faster, RMAN can be configured to use PARALLELISM with multiple channels:</span><br />
<br />
<div class="kmcodeblock" style="width: 95%;">
<span style="font-size: x-small;">RMAN> configure device type disk parallelism 4;</span><br /><span style="font-size: x-small;">RMAN> <strong>backup validate check logical database;</strong></span><br /><br /><span style="font-size: x-small;">OR </span><br /><br /><span style="font-size: x-small;">RMAN> run {</span><br /><span style="font-size: x-small;">allocate channel d1 type disk;</span><br /><span style="font-size: x-small;">allocate channel d2 type disk;</span><br /><span style="font-size: x-small;">allocate channel d3 type disk;</span><br /><span style="font-size: x-small;">allocate channel d4 type disk;</span><br /><span style="font-size: x-small;"><strong>backup validate check logical database;</strong></span><br /><span style="font-size: x-small;">}</span></div>
<span style="font-size: x-small;"><strong><span style="text-decoration: underline;">Output</span></strong></span><br />
<span style="font-size: x-small;">V$DATABASE_BLOCK_CORRUPTION is updated with the corrupt blocks.</span><br />
<br />
<br />
<span style="font-size: x-small;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><span style="font-size: x-small;">In 12c the NOLOGGING blocks identified by rman validate are in new view v$nonlogged_block:</span></span></span><br />
<br />
<span style="font-size: x-small;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><span style="font-size: x-small;"> </span></span></span><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;">RMAN keeps corruption information in the control file (v$database_block_corruption, v$backup_corruption)</span><br />
<br />
<span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"></span></span><br />
<pre>1) validate all database files and archived redo log files for physical and logical corruption:
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;</pre>
<br /><br />
<br />
<span style="font-size: x-small;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><span style="font-size: x-small;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;">Physical and Logical Block Corruptions. All you wanted to know about it. (Doc ID 840978.1)</span></span></span></span><br />
<br />
<span style="font-size: x-small;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><span style="font-size: x-small;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"> </span> </span></span> </span><br />
<span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"> </span><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;">ORA-1499. Table/Index row count mismatch (Doc ID 563070.1)</span> <br />
<br />
<span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;">Trial Recovery - Recover database Test (Doc ID 283262.1)</span></span></span><br />
<br />
<span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"></span></span></span></span><br />
<h3 class="km">
How to use TEST option for any RECOVER command ?</h3>
<span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><span style="font-weight: normal;">For example, you can start SQL*Plus and then issue any of the following commands:</span><br />
<span style="font-weight: normal;">
</span><div class="kmcodeblock" style="width: 95%;">
<span style="font-weight: normal;">RECOVER DATABASE TEST <br />RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL TEST <br />RECOVER TABLESPACE TEST <br />RECOVER DATABASE UNTIL CANCEL TEST</span></div>
<div class="kmcodeblock" style="width: 95%;">
<span style="font-weight: normal;"> </span></div>
<div class="kmcodeblock" style="width: 95%;">
<span style="font-weight: normal;">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</span><br />
<span style="font-weight: normal;">
</span><div class="kmcodeblock" style="width: 95%;">
<span style="font-weight: normal;">SQL> RECOVER DATABASE TEST ALLOW n CORRUPTION;<br /><br /><br />-- where n is the number of blocks.</span></div>
<div class="kmcodeblock" style="width: 95%;">
<span style="font-weight: normal;"> </span></div>
<div class="kmcodeblock" style="width: 95%;">
<span style="font-weight: normal;"> </span></div>
<div class="kmcodeblock" style="width: 95%;">
<span style="font-weight: normal;"> </span><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;">RMAN : Block-Level Media Recovery - Concept & Example (Doc ID 144911.1)</span></div>
<div class="kmcodeblock" style="width: 95%;">
<span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"> </span></div>
<div class="kmcodeblock" style="width: 95%;">
<span style="font-weight: normal;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;">RMAN> run {BACKUP VALIDATE DATABASE;}</span></span></span></div>
<div class="kmcodeblock" style="width: 95%;">
<span style="font-weight: normal;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"> </span></span></span></div>
<div class="kmcodeblock" style="width: 95%;">
<span style="font-weight: normal;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;">SQL> select * from V$backup_corruption;</span></span></span></span></div>
<div class="kmcodeblock" style="width: 95%;">
<span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"> </span></span></span></div>
<div class="kmcodeblock" style="width: 95%;">
<span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"> </span> </span></span><span style="font-family: Courier New,Courier,mono;"><span style="font-family: Courier New,Courier,mono;">Alternatively, you can use Data Recovery Advisor (DRA):</span></span><br />
<div class="kmcodeblock">
<strong>RMAN> list failure;</strong></div>
<div class="kmcodeblock">
<strong><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><strong>RMAN> repair failure preview;</strong></span></strong></div>
<div class="kmcodeblock">
<strong><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><strong><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><strong>RMAN> repair failure noprompt;</strong></span></strong></span></strong></div>
<div class="kmcodeblock">
<strong><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><strong><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><strong> </strong></span> </strong></span> </strong></div>
</div>
</div>
</span></span></span></span><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><pre>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;</pre>
<pre> </pre>
<pre> </pre>
<pre><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><pre>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;</pre>
</span> </pre>
<pre>
</pre>
<pre><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;">ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution (Doc ID 794505.1)</span></pre>
<pre><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"> </span></pre>
<pre><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"> </span></pre>
<pre><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;"> </span><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><span style="font-size: x-small;">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). </span></span></pre>
<pre><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><span style="font-size: x-small;">
</span></span></pre>
<pre>
</pre>
</span><span style="font-size: x-small;">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:</span><br />
<div class="kmcodeblock">
<span style="font-size: x-small;">RMAN> validate database nonlogged block;</span><br />
<br />
<br />
<br />
<h3 class="km">
<span style="font-size: x-small;"><strong>Monitoring NOLOGGING Operations</strong></span></h3>
<div class="km">
<br /></div>
<div class="km">
<span style="font-size: x-small;">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:</span></div>
<div class="kmcodeblock">
<span style="font-size: x-small;">RMAN> report unrecoverable;</span></div>
<br />
<span style="font-size: x-small;"><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"></span></span><br />
<div style="margin-left: 30px;">
<span style="font-size: x-small;">In 12c there is the option to use the RMAN command: <a href="http://docs.oracle.com/database/122/SBYDB/examples-of-using-oracle-data-guard.htm#SBYDB4895">RECOVER NONLOGGED BLOCK</a> with DATAFILE,TABLESPACE,DATABASE granularity. An example for DATABASE is:</span></div>
<div class="kmcodeblock" style="margin-left: 60px;">
<span style="font-size: x-small;">RMAN> RECOVER DATABASE NONLOGGED BLOCK;</span></div>
<div style="margin-left: 30px;">
<span style="font-size: x-small;">To avoid the problem from being introduced, force logging in the PRIMARY database with:</span></div>
<div class="kmcodeblock" style="margin-left: 60px;">
<span style="font-size: x-small;">alter database force logging;</span></div>
<div class="kmcodeblock" style="margin-left: 60px;">
<span style="font-size: x-small;"> </span></div>
<div class="kmcodeblock" style="margin-left: 60px;">
<span style="font-size: x-small;"> </span></div>
<br /><br />
<span style="font-size: x-small;"> </span><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;">DBMS_REPAIR SCRIPT (Doc ID 556733.1)</span><br />
<br />
<span style="font-size: x-small;"><span class="xq" id="kmPgTpl:r1:0:ol22" style="color: black; font-size: 130%; font-weight: bold;">ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors (Doc ID 293515.1)</span> </span></div>
<span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><pre><span class="kmContent" id="kmPgTpl:r1:ot71" style="color: black; font-size: x-small;"><span style="font-size: x-small;"></span></span></pre>
</span>MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-46964207984121557342017-12-29T22:50:00.002+08:002017-12-29T22:53:39.641+08:00Apply APEX 5.1.4 to 5.1.3 in PDB1. download p25341386_511_Generic.zip<br />
<br />
- I was wrong to use the full set copy for upgrade. <br />
<br />
2. <br />
<br />
SQL> alter session set container=pdb1 ;<br />
<br />
Session altered.<br />
<br />
SQL> select VERSION from dba_registry where comp_id='APEX';<br />
<br />
VERSION<br />
------------------------------<br />
5.1.3.00.05<br />
<br />
3. SQL> @apxpatch.sql<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
...Validating Application Express<br />
...(22:36:14) Starting validate_apex for APEX_050100<br />
...(22:36:17) Checking missing sys privileges<br />
...(22:36:18) Key object existence check<br />
...(22:36:18) Setting DBMS Registry for APEX to valid<br />
...(22:36:18) Exiting validate_apex<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
timing for: Complete Patch<br />
Elapsed: 00:05:41.47<br />
<br />
<br />
4. load images<br />
<br />
<br />
SQL> !pwd<br />
/home/oracle/Downloads/APEX/p26795231_514_Generic/patch<br />
<br />
SQL> !ls -ld images<br />
drwxr-xr-x. 35 oracle oracle 32768 Dec 14 18:59 images<br />
<br />
SQL> @apxldimg.sql /home/oracle/Downloads/APEX/p26795231_514_Generic/patch<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
<br />
<br />
<br />
<br />
1 row selected.<br />
<br />
<br />
<br />
<br />
<br />
1 row selected.<br />
<br />
<br />
<br />
<br />
<br />
1 row selected.<br />
<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
<br />
<br />
<br />
<br />
1 row selected.<br />
<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
. Loading images directory: /home/oracle/Downloads/APEX/p26795231_514_Generic/patch/images<br />
<br />
Directory created.<br />
<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
<br />
Commit complete.<br />
<br />
<br />
Directory dropped.<br />
<br />
timing for: Load Images<br />
Elapsed: 00:06:19.75<br />
<br />
<br />
<br />
SQL> select VERSION from dba_registry where comp_id='APEX';<br />
<br />
VERSION<br />
------------------------------<br />
5.1.4.00.08<br />
<br />
1 row selected.<br />
<br />
No more below error any more !<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcYzz6xRaLtQ2hUpEDdtNg2xLpmowOPRYuTK6VKktF-BisxJ0X4wBmuBeU0ubcicR7f4LarO8UBL_bN1NW4PA6QklZFhahleUXM23UIXAptdHfSKRZ1jbtwaZrJjxHTNAHVxfFPg/s1600/snapshot49.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="228" data-original-width="1255" height="115" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcYzz6xRaLtQ2hUpEDdtNg2xLpmowOPRYuTK6VKktF-BisxJ0X4wBmuBeU0ubcicR7f4LarO8UBL_bN1NW4PA6QklZFhahleUXM23UIXAptdHfSKRZ1jbtwaZrJjxHTNAHVxfFPg/s640/snapshot49.png" width="640" /></a></div>
<br />
<br />
References:<br />
<br />
http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-514-patch-set-notes-4124555.html#GUID-68F6035F-90E8-4059-8902-AC6ADE3DAB3E<br />
<br />
<br />MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-16823796377897698052017-12-06T23:53:00.001+08:002017-12-07T00:15:33.337+08:00Zabbix 3.4 for Postgresql 9.6 installation in Centos 7This installation extended to my home hour, since both Postgresql and Zabbix are new to me.<br />
<br />
Followed few articles without much difficulty.<br />
<br />
<pre class="code">https://www.zabbix.com/documentation/3.4/manual/installation/install_from_packages/rhel_centos</pre>
<pre class="code"> </pre>
<pre class="code">follow above, but replace 'mysql' with 'pgsql' </pre>
<pre class="code"> </pre>
<pre class="code">yum install zabbix-server-pgsql</pre>
<pre class="code">yum install zabbix-proxy-pgsql</pre>
<pre class="code">yum install zabbix-web-pgsql </pre>
<pre class="code"> </pre>
<pre class="code"> </pre>
<pre class="code">below is my final packages started with zabbix</pre>
<pre class="code"> </pre>
<pre class="code">[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
</pre>
<pre class="code"> </pre>
<h1>
<a href="https://gist.github.com/sebastianwebber/5f7dd76d0b7eabb1d388fbfdcbafebda" target="_blank"><span style="font-size: small;"><span style="font-weight: normal;">Zabbix 3 Install on CEntOS 7 with PostgreSQL 9.5</span></span></a></h1>
<h3 class="sectionedit1" id="database_creation">
</h3>
<h3 class="sectionedit1" id="database_creation">
database creation</h3>
<pre class="code">postgres createuser --pwprompt zabbix</pre>
<pre class="code"> </pre>
<pre class="code">postgres createdb -O zabbix zabbix</pre>
<pre class="code"> </pre>
<pre class="code"> </pre>
<pre class="code">logon as postgres</pre>
<pre class="code"> </pre>
<pre class="code"> zcat /usr/share/doc/zabbix-proxy-pgsql*/schema.sql.gz | psql -U zabbix -d zabbix</pre>
<pre class="code"> </pre>
<pre class="code"> </pre>
<pre class="code">-- /etc/httpd/conf.d/zabbix.conf. </pre>
<pre class="code">set php_value date.timezone accordingly </pre>
<pre class="code"> </pre>
<br />
<br />
Problem starts when I started in web page installation step.<br />
<br />
<u>Problem 1 </u><br />
"system error occurred. please contact zabbix administrator"<br />
<br />
<br />
Then I realized the agent was not installed and started, but this didn't solve the problem.<br />
<br />
<u>Problem 2</u><br />
<u> </u><br />
From the agent logfile /var/log/zabbix/zabbix_agentd.log, it says zabbix-agent failed to connect to 10051<br />
<br />
<br />
Then I realized the<br />
<br />
<u>problem 3</u> - the zabbix-server failed to start<br />
<br />
although the systemctl start zabbix-server.service return 0 , and didn't show any errror on screen.<br />
<br />
Errors in var/log/zabbix/zabbix_serverd.log are :<br />
<br />
<i>7676:20171206:223117.081 Starting Zabbix Server. Zabbix 3.4.4 (revision 74338).<br /> 7676:20171206:223117.081 ****** Enabled features ******<br /> 7676:20171206:223117.081 SNMP monitoring: YES<br /> 7676:20171206:223117.081 IPMI monitoring: YES<br /> 7676:20171206:223117.081 Web monitoring: YES<br /> 7676:20171206:223117.081 VMware monitoring: YES<br /> 7676:20171206:223117.081 SMTP authentication: YES<br /> 7676:20171206:223117.081 Jabber notifications: YES<br /> 7676:20171206:223117.081 Ez Texting notifications: YES<br /> 7676:20171206:223117.081 ODBC: YES<br /> 7676:20171206:223117.081 SSH2 support: YES<br /> 7676:20171206:223117.081 IPv6 support: YES<br /> 7676:20171206:223117.081 TLS support: YES<br /> 7676:20171206:223117.081 ******************************<br /> 7676:20171206:223117.081 using configuration file: /etc/zabbix/zabbix_server.conf<br /> 7676:20171206:223117.090 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: relation "users" does not exist<br />LINE 1: select userid from users limit 1<br /> ^<br /> [select userid from users limit 1]<br /> 7676:20171206:223117.090 cannot use database "zabbix": database is not a Zabbix database<br />zabbix_server [7686]: cannot open log: cannot create semaphore set: [28] No space left on device<br />zabbix_server [7691]: cannot open log: cannot create semaphore set: [28] No space left on device</i><br />
<br />
<br />
Problem 4 - <i></i><i>PGRES_FATAL_ERROR:ERROR: relation "users" does not exist</i><br />
<br />
Googled this crucial <a href="https://www.zabbix.com/forum/showthread.php?t=53682&page=2" target="_blank">article</a> to my final success<i>, </i>which tell me should leave default (blank) for DBSchema inside the /etc/zabbix/zabbix_server.conf<br />
<br />
<br />
Next I attempted to solve "<i>cannot create semaphore set</i>", 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! <br />
<br />
The zabbix-server is up without error.<br />
<br />
<br />
Next, I almost faced no challenges with web page installation steps, as shown below.<br />
<br />
Again , there Database schema is important, leave as blank. both public and named as zabbix not working. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiootALC21xrvmQb50ecdwA8sc-mmpRtzRfjXeWT_B4t3vpj5zLOM4GmRAEAa0RZhjX3WFLpLUF1vknVNCSpVv-DZDounzEi2o8WPpxUwS6Ke01J2kDb2l69R2ND76hPrqdFGgzg/s1600/snapshot37.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="506" data-original-width="849" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiootALC21xrvmQb50ecdwA8sc-mmpRtzRfjXeWT_B4t3vpj5zLOM4GmRAEAa0RZhjX3WFLpLUF1vknVNCSpVv-DZDounzEi2o8WPpxUwS6Ke01J2kDb2l69R2ND76hPrqdFGgzg/s640/snapshot37.png" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgst1djnh4uNKLFIFwp6tnyHY9IsJjRoKZ7oEgLfVc5538IL-gzOvwFse8SD8YxoSvVtwC9wPEIr5H7286Q4JTrgP9IXAdV4pKdBBEg_5G1QocUOMHzcPBYpqlKt3ZPfAXPBdMmqg/s1600/snapshot38.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="505" data-original-width="839" height="384" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgst1djnh4uNKLFIFwp6tnyHY9IsJjRoKZ7oEgLfVc5538IL-gzOvwFse8SD8YxoSvVtwC9wPEIr5H7286Q4JTrgP9IXAdV4pKdBBEg_5G1QocUOMHzcPBYpqlKt3ZPfAXPBdMmqg/s640/snapshot38.png" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZX3WqVw3n2Jx9ic8QybOQwFOJlRPcewedcTWjz3U28G2O9Bhe-UP1dsvaeiSXg67-QEKFrHdOpHcIES84n-XSfNs07vikpY_-lBdlBveVZfsg6zeogQQNhCphksijwHrfyhgYig/s1600/snapshot39.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="534" data-original-width="847" height="402" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZX3WqVw3n2Jx9ic8QybOQwFOJlRPcewedcTWjz3U28G2O9Bhe-UP1dsvaeiSXg67-QEKFrHdOpHcIES84n-XSfNs07vikpY_-lBdlBveVZfsg6zeogQQNhCphksijwHrfyhgYig/s640/snapshot39.png" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjm-C_KN0s01q3BsQhzEIvJvkfiTrkv4E8prEuwmVdQZMsLJJvOKDOhh51nvRc48vWcfcGJRrrh9qxC0CPa1aao6oZbqBBtzFRkyj8br61fMOeewOpzv5ut7ybTc9muc87qJUX_Q/s1600/snapshot40.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="501" data-original-width="851" height="376" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjm-C_KN0s01q3BsQhzEIvJvkfiTrkv4E8prEuwmVdQZMsLJJvOKDOhh51nvRc48vWcfcGJRrrh9qxC0CPa1aao6oZbqBBtzFRkyj8br61fMOeewOpzv5ut7ybTc9muc87qJUX_Q/s640/snapshot40.png" width="640" /></a></div>
<br />
The default login ID is Admin (case sensitive) and password is zabbix<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmefsamET0C4TAZrytnJoN-7BT8yKImPaKDpiPjzpEPawPIiA7X9DZzhpoN-UI_Bhi61-iiwrDgQEAQbs3UiUciyKf9GeUlZM9CuiaBvOfNJTtP5mO8oZtvm2fQik5FcbboiWaAQ/s1600/snapshot44.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="470" data-original-width="393" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmefsamET0C4TAZrytnJoN-7BT8yKImPaKDpiPjzpEPawPIiA7X9DZzhpoN-UI_Bhi61-iiwrDgQEAQbs3UiUciyKf9GeUlZM9CuiaBvOfNJTtP5mO8oZtvm2fQik5FcbboiWaAQ/s400/snapshot44.png" width="333" /></a></div>
<br />
empty dashboard at first logon<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyNRrhP01wRCwyF2VccBUkTnUpLQtTBWvV0JAin8qumDTyYlUAm6ACJXs3AArTjdwmPpFqkWpPopY6PP-2jmE0ptgMpN93-o5vfcgy1mGebfPIdP4tLnu1SHiilBCwEJ2cS7lVOA/s1600/snapshot41.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="762" data-original-width="1600" height="304" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyNRrhP01wRCwyF2VccBUkTnUpLQtTBWvV0JAin8qumDTyYlUAm6ACJXs3AArTjdwmPpFqkWpPopY6PP-2jmE0ptgMpN93-o5vfcgy1mGebfPIdP4tLnu1SHiilBCwEJ2cS7lVOA/s640/snapshot41.png" width="640" /></a></div>
<br />
enable local serer for moniting <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDjnuP2u0tNALiTIysiOCtXeLfdoKLQWoGmb_l246Vq948gzsIs_V1sL__wJ6Vsy0O4UISop5uMWCdg38IwfOkAC8JMopWIq7TE3-u8tDTXWRRfXmlBszslWoHIW-pxGP2xBicFA/s1600/snapshot42.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="306" data-original-width="1600" height="122" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDjnuP2u0tNALiTIysiOCtXeLfdoKLQWoGmb_l246Vq948gzsIs_V1sL__wJ6Vsy0O4UISop5uMWCdg38IwfOkAC8JMopWIq7TE3-u8tDTXWRRfXmlBszslWoHIW-pxGP2xBicFA/s640/snapshot42.png" width="640" /></a></div>
<br />
Finally see something.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhGtkiuSwkCQ7ODTRi7xs1B8TLryancvlaMvPKLhXAwRFxGgwlmAxGQlJLgd-AXNPOFF2Ie1Fqn-6p3KN413dvyyi2MucwSk1c9zFM7jURWTaev2SPewG_tiJn9S8wqdrfn2nzzQ/s1600/snapshot43.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="674" data-original-width="1600" height="268" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhGtkiuSwkCQ7ODTRi7xs1B8TLryancvlaMvPKLhXAwRFxGgwlmAxGQlJLgd-AXNPOFF2Ie1Fqn-6p3KN413dvyyi2MucwSk1c9zFM7jURWTaev2SPewG_tiJn9S8wqdrfn2nzzQ/s640/snapshot43.png" width="640" /></a></div>
<br />MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-4897092547353597502017-11-28T23:32:00.002+08:002017-11-28T23:42:18.746+08:00key settings for jmeter3.3 JDBC connection to Oracle database (11.2.0.4)<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDFBiWmZ-Trgyz1Our4Zbtu2IScSrzqDWssBnkIcA1p1wkPRqggqkXKeXRncOSFQ0YvFpxDsWY24eM4W1f7Y9vNPuhZJbAuMMqzbbrMC3XXMOBhk8DHR5i5HDlCAPRlijxLyPO_w/s1600/snapshot30.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="548" data-original-width="857" height="408" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDFBiWmZ-Trgyz1Our4Zbtu2IScSrzqDWssBnkIcA1p1wkPRqggqkXKeXRncOSFQ0YvFpxDsWY24eM4W1f7Y9vNPuhZJbAuMMqzbbrMC3XXMOBhk8DHR5i5HDlCAPRlijxLyPO_w/s640/snapshot30.png" width="640" /></a></div>
Tested both URL works:<br />
jdbc:oracle:thin:@//localhost:1523/ora11g<br />
jdbc:oracle:thin:@localhost:1523/ora11g<br />
<br />
JDBC URL takes forms:
<br />
<ul>
<li>jdbc:oracle:thin:@host:port <strong>/databaseName</strong></li>
<li>jdbc:oracle:thin:@host:port <strong>:serviceName </strong></li>
</ul>
Note that the "validation query" in my case should choose "select 1 from dual", otherwise I was getting "Cannot create PoolableConnectionFactory (ORA-00923: FROM<br />
keyword not found where expected"<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgnIxSNlOF3dn0OPT6GWmcy9aL1u83W3rA6ymruvBcCyxhvpML90gLT5YTjI2a31W_OqO3_YGNxmb1qWcdYG9M3aqr1g1Aqyt5ZjjwdfJIzOWzGWqkCcFI3gVrcJ9-lpLPEJPc67g/s1600/snapshot32.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="750" data-original-width="1019" height="470" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgnIxSNlOF3dn0OPT6GWmcy9aL1u83W3rA6ymruvBcCyxhvpML90gLT5YTjI2a31W_OqO3_YGNxmb1qWcdYG9M3aqr1g1Aqyt5ZjjwdfJIzOWzGWqkCcFI3gVrcJ9-lpLPEJPc67g/s640/snapshot32.png" width="640" /></a></div>
<br />
ojdbc8.jar is the jdbc driver downloaded from oracle.com<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdISuDIk5s6YIMGqy7bq5YunBrA5jOHtyrzojs6E6u0UbFcuu1gxplBy9uLliJGTuKKWiwNrsaKcKxPDcfAMyHp4Cliq6Q90jA6bF4409SDSVL9bCZpgqzEMI2fzNIlGBOE60xvA/s1600/snapshot33.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="221" data-original-width="635" height="222" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdISuDIk5s6YIMGqy7bq5YunBrA5jOHtyrzojs6E6u0UbFcuu1gxplBy9uLliJGTuKKWiwNrsaKcKxPDcfAMyHp4Cliq6Q90jA6bF4409SDSVL9bCZpgqzEMI2fzNIlGBOE60xvA/s640/snapshot33.png" width="640" /></a></div>
<br />
For SQL query , note that<br />
1. There is no semi-colon (;) at the end of the sql statment, otherwise there is ora error code saying "ORA-00911: invalid character"<br />
<br />
<br />
<br />
Above are key things I encountered when first time playing jmeter & oracle.<br />
<br />
Also tested okay for 12.2c PDB.<br />
<br />MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-90855361923824510592017-09-01T09:46:00.003+08:002017-09-01T09:46:31.666+08:00apply APEX patch 5.1.2 for 5.1.1alter session set container=pdb1;<br /><br /><br />Session altered.<br /><br />SQL> @apxpatch.sql<br />...<br /><br />PL/SQL procedure successfully completed.<br /><br />...Validating Application Express<br />...(12:25:48) Starting validate_apex for APEX_050100<br />...(12:25:51) Checking missing sys privileges<br />...(12:25:51) Recompiling<br />...(12:25:53) Checking for objects that are still invalid<br />...(12:25:53) Key object existence check<br />...(12:25:53) Setting DBMS Registry for APEX to valid<br />...(12:25:53) Exiting validate_apex<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br />timing for: Complete Patch<br />Elapsed: 00:04:48.58<br /><br /><br />SQL> !ls apxldimg.sql<br />apxldimg.sql<br /><br />SQL> @apxldimg.sql /home/oracle/Downloads/patch<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br /><br /><br />1 row selected.<br /><br /><br /><br />1 row selected.<br /><br /><br /><br /><br />1 row selected.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br /><br /><br /><br />1 row selected.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br />. Loading images directory: /home/oracle/Downloads/patch/images<br /><br />Directory created.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br />Commit complete.<br /><br /><br />Directory dropped.<br /><br />timing for: Load Images<br />Elapsed: 00:05:12.92<br />
SQL> select VERSION from dba_registry where comp_id='APEX';<br /><br />VERSION<br />------------------------------<br />5.1.2.00.09<br /><br />1 row selected.<br />MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-83095728242640106512017-08-23T22:32:00.000+08:002017-08-23T22:34:18.600+08:00some links for APEX 5.0 upgrade to 5.1<br />
<br />
<h1 class="entry-title">
<a href="https://svenweller.wordpress.com/2017/02/01/how-to-upgrade-from-apex-5-0-to-5-1/" target="_blank">How to upgrade from Apex 5.0 to 5.1</a></h1>
<h1 class="entry-title">
</h1>
<h1 itemprop="headline">
<a href="https://www.foxinfotech.in/2017/03/apply-these-5-steps-to-upgrade-oracle-apex-from-5-0-to-5-1.html" target="_blank">Apply These 5 Steps to Upgrade Oracle Apex from 5.0 to 5.1</a></h1>
<h1 itemprop="headline">
</h1>
<h1 class="entry-title">
<a href="http://farzadsoltani.com/2017/03/01/upgrade-oracle-apex-5-1-grant-issues/" target="_blank">Upgrade Oracle APEX 5.1 – Grant Issues</a></h1>
<h1 class="entry-title">
</h1>
<h2 class="m-SubHeader-title">
<a href="http://www.oracle.com/technetwork/developer-tools/apex/application-express/upgrade-apex-for-xe-154969.html" target="_blank">Upgrading Oracle Application Express within Oracle Database 11g Express Edition (XE)</a></h2>
<h1 class="entry-title">
</h1>
<h1 class="entry-title">
</h1>
<h1 itemprop="headline">
</h1>
<h1 itemprop="headline">
</h1>
<h1 class="entry-title">
</h1>
<h1 class="entry-title">
</h1>
<h1 class="entry-title">
</h1>
MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-74506550437625445212017-08-23T00:47:00.001+08:002017-08-23T00:47:12.629+08:00something new related AWR setting in 12c multitenant DB<br />
<br />
<h3 class="post-title entry-title">
<a href="http://dba-blogs.blogspot.sg/2013/09/doc-explains-12c-automatic-workload.html">12c Consolidated AWR report with PDBs</a></h3>
<h3 class="post-title entry-title" itemprop="name">
<a href="https://asanga-pradeep.blogspot.sg/2017/06/enabling-automatic-awr-snapshots-on-pdb.html" target="_blank">Enabling Automatic AWR Snapshots on PDB </a></h3>
<h3 class="post-title entry-title" itemprop="name">
</h3>
<h3 class="post-title entry-title" itemprop="name">
below is not unique to 12c, but advanced ... </h3>
<a href="http://vijaydbacorner.blogspot.sg/2013/04/modifying-awr-automatic-snapshot" target="_blank"><span style="font-weight: normal;">To see AWR settings query DBA_HIST_WR_CONTROL</span></a><br />
<h3 class="post-title entry-title" itemprop="name">
</h3>
<h3 class="post-title entry-title" itemprop="name">
</h3>
<h3 class="post-title entry-title" itemprop="name">
</h3>
<h3 class="post-title entry-title" itemprop="name">
</h3>
<h3 class="post-title entry-title">
</h3>
MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-71557390023758904482017-08-23T00:33:00.002+08:002017-08-23T00:55:04.505+08:00Create stacked bar in APEX 5.1It seems no "stacked bar" chart in APEX 5.1.<br />
<br />
Here is my key steps after few tries.<br />
<br />
1. add two series, with same test SQL query.<br />
<br />
select * from "EVENT_RT"<br />
<br />
2. here is the key setting. Inside Chart > Attribute > choose YES for Stacked. Default is NO, which means two bars side by side displayed.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLDFwBEzITQAtKq4SSpoPS_B9WBsU0JHiXg1Hx_S5CSsmZr96ToIm321td7mvNCLHg0CVG2cwehKfyTLwZ1p1g5F_h6pwY2DHwYv5WQm_l31PsGfUU7fgZGMttSURS8agZEjsH6Q/s1600/snapshot9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="441" data-original-width="319" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLDFwBEzITQAtKq4SSpoPS_B9WBsU0JHiXg1Hx_S5CSsmZr96ToIm321td7mvNCLHg0CVG2cwehKfyTLwZ1p1g5F_h6pwY2DHwYv5WQm_l31PsGfUU7fgZGMttSURS8agZEjsH6Q/s400/snapshot9.png" width="288" /></a></div>
<br />
<br />
The other method (should be prior to 5.1) uses one serie data only together with below sample query formed by UNION ALL. I guess it may still work in 5.1<br />
<br />
<i>select id,<br /> project as label,<br /> NVL((select sum(t.budget) from eba_demo_chart_tasks t where t.project = p.id and t.budget > t.cost),0) as value,<br /> 'under budget' as series ,<br /> 'green' as color<br /> from eba_demo_chart_projects p<br />union all<br />select id,<br /> project as label,<br /> NVL((select sum(t.budget) from eba_demo_chart_tasks t where t.project = p.id and t.budget <= t.cost),0) as value,<br /> 'over budget' as series ,<br /> 'red' as color<br /> from eba_demo_chart_projects p</i><br />
MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-44038673306730844382017-08-20T00:22:00.000+08:002017-08-20T00:22:06.224+08:00install sql server 2017 RC2 in CentOS 7.3Here is the <a href="https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat" target="_blank">link</a> as of writing time, and follow it without difficulty.<br />
<br />
<br />
<br />
[oracle@hmc-P55A-UD3 bin]$ sudo su -<br />[sudo] password for oracle: <br />Last login: Sat Aug 19 10:48:19 EDT 2017 on pts/1<br />[root@hmc-P55A-UD3 ~]# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server.repo<br /> % Total % Received % Xferd Average Speed Time Time Time Current<br /> Dload Upload Total Spent Left Speed<br />100 220 100 220 0 0 445 0 --:--:-- --:--:-- --:--:-- 446<br />[root@hmc-P55A-UD3 ~]# ls -lrt /etc/yum.repos.d/<br />total 52<br />-rw-r--r--. 1 root root 535 Oct 14 2015 CentOS-OpenStack-kilo.repo<br />-rw-r--r--. 1 root root 2893 Nov 29 2016 CentOS-Vault.repo<br />-rw-r--r--. 1 root root 1331 Nov 29 2016 CentOS-Sources.repo<br />-rw-r--r--. 1 root root 630 Nov 29 2016 CentOS-Media.repo<br />-rw-r--r--. 1 root root 314 Nov 29 2016 CentOS-fasttrack.repo<br />-rw-r--r--. 1 root root 649 Nov 29 2016 CentOS-Debuginfo.repo<br />-rw-r--r--. 1 root root 1309 Nov 29 2016 CentOS-CR.repo<br />-rw-r--r--. 1 root root 1664 Nov 29 2016 CentOS-Base.repo<br />-rw-r--r--. 1 root root 1050 Jun 24 11:04 epel-testing.repo<br />-rw-r--r--. 1 root root 951 Jun 24 11:04 epel.repo<br />-rw-r--r--. 1 root root 2142 Jul 23 15:07 elrepo.repo<br />-rw-r--r--. 1 root root 193 Aug 19 10:49 msprod.repo<br /><span style="background-color: orange;">-rw-r--r--. 1 root root 220 Aug 19 12:07 mssql-server.repo</span><br />[root@hmc-P55A-UD3 ~]# yum update<br />Loaded plugins: fastestmirror, langpacks<br />packages-microsoft-com-mssql-server | 2.9 kB 00:00 <br />packages-microsoft-com-mssql-server/primary_db | 12 kB 00:00 <br />Loading mirror speeds from cached hostfile<br /> * base: mirror.qoxy.com<br /> * elrepo: mirrors.thzhost.com<br /> * epel: 0xa028cd70.xtremenitro.org<br /> * extras: mirror.qoxy.com<br /> * updates: mirror.qoxy.com<br />No packages marked for update<br />[root@hmc-P55A-UD3 ~]# yum install -y mssql-server<br />Loaded plugins: fastestmirror, langpacks<br />Loading mirror speeds from cached hostfile<br /> * base: mirror.qoxy.com<br /> * elrepo: elrepo.mirror.angkasa.id<br /> * epel: epel.mirror.angkasa.id<br /> * extras: mirror.qoxy.com<br /> * updates: mirror.qoxy.com<br />Resolving Dependencies<br />--> Running transaction check<br />---> Package mssql-server.x86_64 0:14.0.900.75-1 will be installed<br />--> Finished Dependency Resolution<br /><br />Dependencies Resolved<br /><br />================================================================================<br /> Package Arch Version Repository Size<br />================================================================================<br />Installing:<br /> mssql-server x86_64 14.0.900.75-1 packages-microsoft-com-mssql-server 165 M<br /><br />Transaction Summary<br />================================================================================<br />Install 1 Package<br /><br />Total download size: 165 M<br />Installed size: 165 M<br />Downloading packages:<br />mssql-server-14.0.900.75-1.x86_64.rpm | 165 MB 00:10 <br />Running transaction check<br />Running transaction test<br />Transaction test succeeded<br />Running transaction<br /> Installing : mssql-server-14.0.900.75-1.x86_64 1/1 <br /><br />+--------------------------------------------------------------+<br />Please run 'sudo /opt/mssql/bin/mssql-conf setup'<br />to complete the setup of Microsoft SQL Server<br />+--------------------------------------------------------------+<br /><br /> Verifying : mssql-server-14.0.900.75-1.x86_64 1/1 <br /><br />Installed:<br /> mssql-server.x86_64 0:14.0.900.75-1 <br /><br />Complete!<br />[root@hmc-P55A-UD3 ~]# /opt/mssql/bin/mssql-conf setup<br />The license terms for this product can be found in<br />/usr/share/doc/mssql-server or downloaded from:<br />https://go.microsoft.com/fwlink/?LinkId=852741&clcid=0x409<br /><br />The privacy statement can be viewed at:<br />https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409<br /><br />Do you accept the license terms? [Yes/No]:Yes<br /><br />Choose an edition of SQL Server:<br /> 1) Evaluation (free, no production use rights, 180-day limit)<br /> 2) Developer (free, no production use rights)<br /> 3) Express (free)<br /> 4) Web (PAID)<br /> 5) Standard (PAID)<br /> 6) Enterprise (PAID)<br /> 7) I bought a license through a retail sales channel and have a product key to enter.<br /><br />Details about editions can be found at<br />https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409<br /><br />Use of PAID editions of this software requires separate licensing through a<br />Microsoft Volume Licensing program.<br />By choosing a PAID edition, you are verifying that you have the appropriate<br />number of licenses in place to install and run this software.<br /><br />Enter your edition(1-7): 2<br />Enter the SQL Server system administrator password: <br />The specified password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters<br />Enter the SQL Server system administrator password: <br />Confirm the SQL Server system administrator password: <br />Configuring SQL Server...<br /><br />This is an evaluation version. There are [157] days left in the evaluation period.<br />Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.<br />Setup has completed successfully. SQL Server is now starting.<br />
<br />
<br />
As I have disabled the firewalld in my desktop and installed command line tools in previous blog, I can directly connect locally.<br />
<br />
[oracle@hmc-P55A-UD3 bin]$ sqlcmd -S localhost -U sa<br />Password: <br />1> create database testdb;<br />2> go<br />1> select name from sys.databases;<br />2> go<br />name <br />--------------------------------------------------------------------------------------------------------------------------------<br />master <br />tempdb <br />model <br />msdb <br />testdb <br /><br />(5 rows affected)<br /><br />
<br />
<br />
<br />MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-6016158911496645692017-08-20T00:03:00.002+08:002017-08-20T00:06:13.182+08:00Install sqlcmd and bcp the SQL Server command-line tools on Linux (centos 7.3)Followed <br />
<h1 id="install-sqlcmd-and-bcp-the-sql-server-command-line-tools-on-linux">
<a href="https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools" target="_blank">Install sqlcmd and bcp the SQL Server command-line tools on Linux</a></h1>
<h1 id="install-sqlcmd-and-bcp-the-sql-server-command-line-tools-on-linux">
</h1>
<h1 id="install-sqlcmd-and-bcp-the-sql-server-command-line-tools-on-linux">
<span style="font-size: small;">tested okay from centos7.3 to sql server express 2014 running on windows 10.</span></h1>
<h1 id="install-sqlcmd-and-bcp-the-sql-server-command-line-tools-on-linux">
<span style="font-size: small;">[oracle@hmc-P55A-UD3 bin]$ ls -l<br />total 752<br />-rwxr-xr-x. 1 root root 239320 May 9 15:09 bcp<br />-rwxr-xr-x. 1 root root 525856 May 9 15:09 sqlcmd<br />[oracle@hmc-P55A-UD3 bin]$ pwd<br />/opt/mssql-tools/bin<br /></span></h1>
<h1 id="install-sqlcmd-and-bcp-the-sql-server-command-line-tools-on-linux">
<span style="font-size: small;">[oracle@hmc-P55A-UD3 ~]$ sqlcmd -U perfstat -S 192.168.0.181,1433<br />Password: <br />1> select count(*) from sys.sql_logins;<br />2> go;<br />3> go<br />Msg 102, Level 15, State 1, Server MP04ISLLIQY1\SQLEXPRESS, Line 2<br />Incorrect syntax near 'go'.<br />1> select count(*) from sys.sql_logins;<br />2> go<br /> <br />-----------<br /> 4<br /><br />(1 rows affected)</span> </h1>
MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-52564127319661829612017-08-18T20:18:00.002+08:002017-08-18T20:18:43.413+08:0030 Things to Do After Minimal RHEL/CentOS 7 Installation<h1 class="post-title">
<a href="https://www.tecmint.com/things-to-do-after-minimal-rhel-centos-7-installation/" target="_blank">30 Things to Do After Minimal RHEL/CentOS 7 Installatio</a>n</h1>
<h1 class="post-title">
</h1>
<h1 class="post-title">
</h1>
MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-73492862365297385042017-08-18T19:25:00.002+08:002017-08-18T19:25:40.921+08:00auto dbstart stop in centos 7 (systemd)a nice <a href="https://www.realdbamagic.com/automatic-db-startup-linux-part-oel-6-7-2/" target="_blank">article</a>, followed and works perfectly.<br />
<br />
tested on 12.2c on centos 7.3 <br />
<br />
Only place to replaced is the ORACLE_BASE AND ORACLE_HOME according to my env. <br />
<br />
<br />
<br />
[root@hmc-P55A-UD3 system]# ps -ef |grep tns<br />root 28 2 0 06:53 ? 00:00:00 [netns]<br />root 5336 4752 0 07:20 pts/0 00:00:00 grep --color=auto tns<br />[root@hmc-P55A-UD3 system]# ps -ef |grep pmon<br />root 5435 4752 0 07:20 pts/0 00:00:00 grep --color=auto pmon<br /><br />[root@hmc-P55A-UD3 system]# systemctl start oracle_network.service<br />[root@hmc-P55A-UD3 system]# ps -ef |grep tns<br />root 28 2 0 06:53 ? 00:00:00 [netns]<br />oracle 5449 1 0 07:20 ? 00:00:00 /app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit<br />root 5456 4752 0 07:20 pts/0 00:00:00 grep --color=auto tns<br /><br />[root@hmc-P55A-UD3 system]# systemctl start oracledb<br /><br />[root@hmc-P55A-UD3 system]# systemctl status oracledb<br />● oracledb.service - Oracle databases service<br /> Loaded: loaded (/usr/lib/systemd/system/oracledb.service; enabled; vendor preset: disabled)<br /> Active: active (running) since Fri 2017-08-18 07:20:49 EDT; 16s ago<br /> Process: 5463 ExecStart=/app/oracle/product/12.2.0/dbhome_1/bin/dbstart /app/oracle/product/12.2.0/dbhome_1 (code=exited, status=0/SUCCESS)<br /> CGroup: /system.slice/oracledb.service<br /> ├─5564 ora_pmon_orcl<br /> ├─5566 ora_clmn_orcl<br /> ├─5568 ora_psp0_orcl<br /> ├─5570 ora_vktm_orcl<br /> ├─5574 ora_gen0_orcl<br /> ├─5576 ora_mman_orcl<br /> ├─5580 ora_gen1_orcl<br /> ├─5584 ora_diag_orcl<br /> ├─5586 ora_ofsd_orcl<br /> ├─5590 ora_dbrm_orcl<br /> ├─5592 ora_vkrm_orcl<br /> ├─5594 ora_svcb_orcl<br /> ├─5596 ora_pman_orcl<br /> ├─5598 ora_dia0_orcl<br /> ├─5600 ora_dbw0_orcl<br /> ├─5602 ora_lgwr_orcl<br /> ├─5604 ora_ckpt_orcl<br /> ├─5606 ora_lg00_orcl<br /> ├─5608 ora_smon_orcl<br /> ├─5610 ora_lg01_orcl<br /> ├─5612 ora_smco_orcl<br /> ├─5615 ora_reco_orcl<br /> ├─5617 ora_w000_orcl<br /> ├─5619 ora_lreg_orcl<br /> ├─5621 ora_w001_orcl<br /> ├─5623 ora_pxmn_orcl<br /> ├─5627 ora_mmon_orcl<br /> ├─5629 ora_mmnl_orcl<br /> ├─5631 ora_d000_orcl<br /> ├─5633 ora_s000_orcl<br /> ├─5635 ora_tmon_orcl<br /> ├─5645 ora_tt00_orcl<br /> ├─5647 ora_tt01_orcl<br /> ├─5649 ora_tt02_orcl<br /> ├─5653 ora_aqpc_orcl<br /> ├─5657 ora_p000_orcl<br /> ├─5659 ora_p001_orcl<br /> ├─5661 ora_p002_orcl<br /> ├─5663 ora_p003_orcl<br /> ├─5665 ora_p004_orcl<br /> ├─5667 ora_p005_orcl<br /> ├─5669 ora_p006_orcl<br /> ├─5671 ora_p007_orcl<br /> ├─5673 ora_p008_orcl<br /> ├─5675 ora_p009_orcl<br /> ├─5677 ora_p00a_orcl<br /> ├─5679 ora_p00b_orcl<br /> ├─5681 ora_p00c_orcl<br /> ├─5683 ora_p00d_orcl<br /> ├─5685 ora_p00e_orcl<br /> ├─5687 ora_p00f_orcl<br /> ├─5841 ora_cjq0_orcl<br /> ├─5962 ora_j000_orcl<br /> ├─5964 ora_j001_orcl<br /> ├─5966 ora_s001_orcl<br /> ├─5968 ora_qm02_orcl<br /> ├─5970 ora_qm03_orcl<br /> ├─5972 ora_q002_orcl<br /> ├─5974 ora_q003_orcl<br /> └─5976 ora_q004_orcl<br /><br />Aug 18 07:20:38 hmc-P55A-UD3 systemd[1]: Starting Oracle databases service...<br />Aug 18 07:20:38 hmc-P55A-UD3 dbstart[5463]: Processing Database instance "orcl": log file /app/oracle/product/12.2.0/dbhome_1/startup.log<br />Aug 18 07:20:49 hmc-P55A-UD3 systemd[1]: Started Oracle databases service.<br />[root@hmc-P55A-UD3 system]# systemctl stop oracledb<br /><br />[oracle@hmc-P55A-UD3 bin]$ ps -ef |grep pmon<br />oracle 6147 4949 0 07:22 pts/1 00:00:00 grep --color=auto pmon<br />[oracle@hmc-P55A-UD3 bin]$ ps -ef |grep tns<br />root 28 2 0 06:53 ? 00:00:00 [netns]<br />oracle 6152 4949 0 07:22 pts/1 00:00:00 grep --color=auto tns<br />MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-39109360064596541702017-08-08T23:51:00.000+08:002017-08-08T23:51:11.659+08:00SQL server audit related linksCollected some related to SQL server audit setup, writing to windows event log.<br />
<br />
<br />
<br /><a href="https://www.mssqltips.com/sqlservertip/1735/auditing-failed-logins-in-sql-server/">Auditing Failed Logins in SQL Server</a><br /><br /><br /><a href="https://www.tecklyfe.com/how-to-write-sql-server-audit-events-to-windows-security-log/">How-To Write SQL Server Audit Events To Windows Security Log</a><br /><br /><span style="color: #747474;">After following these steps, you should successfully start seeing your </span><a href="http://www.microsoftstore.com/">Microsoft</a><span style="color: #747474;"> SQL Server Audit Logs in the Windows Security Log. They’re usually listed as Event Code 33205, so you can filter your Security Logs to only show those events.</span><br /><br /><br /><a href="https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine">SQL Server Audit (Database Engine)</a><br /><br />All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions<br /><br /><br /><span style="color: #8a6d3b;">Any authenticated user can read and write to the Windows Application event log. The Application event log requires lower permissions than the Windows Security event log and is less secure than the Windows Security event log.</span><br /><br /><br />
<h3>
Attaching a Database with an Audit Defined</h3>
<br />Attaching a database that has an audit specification and specifies a GUID that does not exist on the server will cause an orphaned audit specification. Because an audit with a matching GUID does not exist on the server instance, no audit events will be recorded. To correct this situation, use the ALTER DATABASE AUDIT SPECIFICATION command to connect the orphaned audit specification to an existing server audit. Or, use the CREATE SERVER AUDIT command to create a new server audit with the specified GUID.<br />You can attach a database that has an audit specification defined on it to another edition of SQL Server that does not support SQL Server audit, such as SQL Server Express but it will not record audit events.<br /><br /><br /><br />
<h3>
Auditing Administrators</h3>
<br />Members of the sysadmin fixed server role are identified as the dbo user in each database. To audit actions of the administrators, audit the actions of the dbo user.<br /><br /><br /><a href="https://technet.microsoft.com/en-us/library/cc280500(v=sql.105).aspx">Creating and Managing Audits with SQL Server Management Studio</a><br /><br />Audit-related nodes are located as follows in the Object Explorer hierarchy in Management Studio.<br />SQL Server Instance<br />---- Security<br />-------- Audits<br />-------- Server Audit Specifications<br />SQL Server Instance<br />---- Databases<br />-------- Database Name<br />------------ Security<br />---------------- Database Audit Specifications<br /><br /><br /><a href="http://create/">Create a Server Audit and Server Audit Specification</a><br /><br /><a href="https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification">Create a Server Audit and Database Audit Specification</a><br /><br /><br /><br /><br /><a href="https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions">SQL Server Audit Action Groups and Actions</a><br /><br /><br /><a href="https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/write-sql-server-audit-events-to-the-security-log">Write SQL Server Audit Events to the Security Log</a><br /><br /><br /><br /><a href="https://www.logbinder.com/Blog/?p=d1f5efd3-d0a8-4c3e-82a5-6c8fc7e7f672">SQL Server Audit Support in Different Editions and Versions</a> ( info for version prior to sql 2016)<br />Sat, 23 Nov 2013 14:02:05 GMT<br /><br /><br />Here is a summary of the SQL Server Audit support in the different editions:<br /><br />
<table class="table"><colgroup><col></col><col></col><col></col></colgroup><tbody>
<tr><th>click me</th><th>click me</th><th>click me</th></tr>
<tr><td> Edition</td><td> SQL Server 2008 and 2008 R2</td><td> SQL Server 2012 and 2014
</td></tr>
<tr><td> Enterprise
</td><td> Server- and database-level</td><td> Server- and database-level</td></tr>
<tr><td> Evaluation</td><td> Server- and database-level</td><td> Server- and database-level</td></tr>
<tr><td> Developer</td><td> Server- and database-level</td><td> Server- and database-level</td></tr>
<tr><td> Datacenter</td><td> Server- and database-level</td><td> N/A</td></tr>
<tr><td> Business Intelligence</td><td> None</td><td> Server-level</td></tr>
<tr><td> Standard</td><td> None</td><td> Server-level</td></tr>
<tr><td> Web</td><td> None</td><td> Server-level</td></tr>
<tr><td> Express</td><td> None</td><td> Server-level</td></tr>
</tbody></table>
<br /><br /><br /><a href="https://stackoverflow.com/questions/2503234/sql-server-ports-445-and-1433">sql server ports 445 and 1433</a><br /><br /><br />445 is not a SQL port, is a SMB port. It is involved in SQL Server only if you use named pipes protocol, as named pipes are over SMB and this in turn uses 445 for '<a href="http://support.microsoft.com/kb/204279">SMB over IP</a>', aka. as SMB 'NETBIOSless' as opposed to the old NetBIOS based SMB, which uses 137-139.<br /><br /><br />To configure the server to listen on specific protocols, use <a href="http://technet.microsoft.com/en-us/library/ms174212.aspx">SQL Server configuration Manager</a>. To configure the client allowed protocols, see <a href="http://msdn.microsoft.com/en-us/library/ms190425.aspx">Configuring Client Network Protocols</a>.MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-17674098357910220632017-08-05T17:02:00.002+08:002017-08-05T17:02:44.697+08:00SQL commands for getting bind variable value $ cat chk_bind_value.sql<br />
Prompt Enter SQL_ID<br />
<br />
<br />
select SQL_ID,name,VALUE_STRING,VALUE_ANYDATA,POSITION,DUP_POSITION,last_captured<br />
from V$SQL_BIND_CAPTURE i<br />
where SQL_ID = '&1'<br />
<br />
<br />
<br />
$ cat chk_hist_bind_value.sql<br />
set pages 1000<br />
select SQL_ID,name,VALUE_STRING,VALUE_ANYDATA,POSITION,DUP_POSITION,last_captured from dba_hist_sqlbind<br />
where SQL_ID='&1' order by snap_id<br />
/<br />
<div>
<br /></div>
MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.comtag:blogger.com,1999:blog-18031851.post-71590970374082549342017-08-05T17:01:00.001+08:002017-08-05T17:01:24.099+08:00Using bind variables in SQL*Plusmark two links for quick reference<br />
<br />
<h1 itemprop="name" style="background-color: white; border: 0px; color: #242729; font-family: Arial, "Helvetica Neue", Helvetica, sans-serif; font-size: 22px; font-stretch: inherit; font-variant-numeric: inherit; font-weight: inherit; line-height: 1.3; margin: 0px 0px 0.5em; padding: 0px; vertical-align: baseline;">
<a href="https://stackoverflow.com/questions/11737849/declare-bind-variables-in-sqlplus" target="_blank">Declare bind variables in SQL*Plus</a></h1>
<div>
<br /></div>
<div>
<h1 style="background-color: #eeeecc; color: #4274c2; font-family: monospace; font-size: 22px; margin-left: 0px; margin-top: 25px;">
<a href="http://www.adp-gmbh.ch/ora/sqlplus/use_vars.html" target="_blank">Using bind variables in SQL*Plus</a></h1>
</div>
<div>
<br /></div>
<br />
<br />
You can declare a bind variable in SQL*Plus though, and select into that:<br />
<br />
var l_test_quote varchar2(80); -- or whatever type/size you need<br />
var l_test_id varchar2(80);<br />
<br />
declare<br />
l_id varchar2(80) :='test123';<br />
begin<br />
select test_quote, test_id<br />
into :l_test_quote, :l_test_id<br />
from order_link<br />
where id = l_id;<br />
end;<br />
/<br />
<br />
print l_test_quote<br />
print l_test_id<br />
Note the : before the references to the variables defined outside the block, indicating they are bind variables. l_id is declared inside the block so it does not have a preceding :.<br />
<br />
In this case you could also define l_id outside the block, and avoid PL/SQL while still using a bind variable for that:<br />
<br />
var l_id varchar2(80);<br />
<br />
exec :l_id := 'test123';<br />
<br />
select test_quote, test_id<br />
from order_link<br />
where id = :l_id;<br />
Because the main query isn't PL/SQL any more (although the exec is; that's just a shorthand for a one-line anonymous block), you don't need to select ... into so you don't need to declare those variables.<br />
<br />
<br />MiDBAhttp://www.blogger.com/profile/09538210178301381014noreply@blogger.com