星期五, 9月 22, 2017

如何Disable Tuning Pack/ Diagnostic Pack

客戶往往在買了10g/ 11g 企業版的Oracle資料庫後,
當資料庫管理員透過DBCA程式  instance建立出來的過程中,
安裝程式也會一併把Enterprise Manager + Tuning Pack + Diagnostic Packenable
如果客戶在沒有買Tuning Pack + Diagnostic Packlicense情況下 是無法合法使用的

以下就開始介紹如何Disable 這兩個套件的方法
1.On Oracle 10g ,
  根據 MOS上面的文章 Disabling and Uninstalling AWR (Doc ID 1909073.1)
  於安裝dbmsnoawr.plb Package, sqlplus下執行 dbms_awr.disable_awr();

2.On Oracle 11g ,
  透過線上修改control_management_pack_access 參數 (default : DIAGNOSTIC+TUNING)
  SQL> ALTER SYSTEM SET control_management_pack_access=NONE;

3.On 12c 以後, 預設只會有Enterprise Manager Express版本, 所以除非有用到Cloud Control Enterprise Manager,
  才會需要考慮license的問題.

ref:
http://www.eygle.com/archives/2012/03/disable_enable_awr.html
https://petesdbablog.wordpress.com/2013/04/06/disable-oracle-diagnostic-pack-tuning-pack/

星期四, 9月 21, 2017

在Windows 已經裝了visio studio 2013 仍無法安裝MySQL 5.7的解決方法

就算已經先安裝了visio studio 2013 , 仍然無法安裝MySQL 5.7的解決方法

 

1.從這下載 Visio Studio Redistributable 2013 版本(更新版)

 

https://support.microsoft.com/en-us/help/3179560/update-for-visual-c-2013-and-visual-c-redistributable-package

 

2.再試著安裝MySQL 5.7

 

Ref:

https://forums.mysql.com/read.php?169,659133,659152#msg-659152

 

星期三, 8月 30, 2017

FW: 如何切換Oracle Linux 改用redhat kernel 進行開機

會用到此方法主要是因為qlogic HBA Card在官網上面可以下載到的driverfor redhat kernel, 而客戶的作業系統又是使用免費的Oracle Linux 7 (目前還號稱免費使用) ...

Ref:
Switching Default UEK kernel to Redhat Kernel on Oracle Linux 7
This article will describe how to Switching Default UEK kernel to Redhat Kernel on Oracle Linux 7 with the following steps :
1. Original Kernel for Oracle Linux 7 will likely as below :
# uname -a
Linux localhost.localdomain 3.8.13-55.1.6.el7uek.x86_64 #2 SMP Wed Feb 11 14:18:22 PST 2015 x86_64 x86_64 x86_64 GNU/Linux
2. Grep the list of the available kernel in Oracle Linux 7. The sequence of entry will start with 0.
a) To display with command 1 :
# egrep ^menuentry /etc/grub2.cfg | cut -f 2 -d \'
Oracle Linux Server 7.1, with Linux 3.10.0-229.el7.x86_64
Oracle Linux Server 7.1, with Unbreakable Enterprise Kernel 3.8.13-55.1.6.el7uek.x86_64
Oracle Linux Server 7.1, with Linux 0-rescue-26ad0b77c2de4840ba8402282bdd9d17
b) To display with command 2 :
# grep '^menuentry' /boot/grub2/grub.cfg | cut -f 2 -d \'
Oracle Linux Server 7.1, with Linux 3.10.0-229.el7.x86_64
Oracle Linux Server 7.1, with Unbreakable Enterprise Kernel 3.8.13-55.1.6.el7uek.x86_64
Oracle Linux Server 7.1, with Linux 0-rescue-26ad0b77c2de4840ba8402282bdd9d17
3. Set entry 0 as the default kernel with this command :
# grub2-set-default 0
# grub2-mkconfig -o /etc/grub2.cfg
Generating grub configuration file ...
Found linux image: /boot/vmlinuz-3.10.0-229.el7.x86_64
Found initrd image: /boot/initramfs-3.10.0-229.el7.x86_64.img
Found linux image: /boot/vmlinuz-3.8.13-55.1.6.el7uek.x86_64
Found initrd image: /boot/initramfs-3.8.13-55.1.6.el7uek.x86_64.img
Found linux image: /boot/vmlinuz-0-rescue-26ad0b77c2de4840ba8402282bdd9d17
Found initrd image: /boot/initramfs-0-rescue-26ad0b77c2de4840ba8402282bdd9d17.img
done
4. Reboot the server :
# init 6
5. After reboot, verify that Red Hat Compatible Kernel will become the default :
# uname -a
Linux localhost.localdomain 3.10.0-229.el7.x86_64 #1 SMP Fri Mar 6 04:05:24 PST 2015 x86_64 x86_64 x86_64 GNU/Linux

星期五, 8月 25, 2017

[轉錄] 如何在MySQL Community版本安裝audit plugin.

主透過安裝mariadb audit plugin, MySQL可以記錄各種SQL 動作 : Connect, Query, Table

ref:

1.Download the latest plugin from the links given above and you should see the download directory as below. Choose the latest version. We used server_audit-1.4.0.tar.gz. in centOS. We can use the wget command that is:
wget https://downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin/server_audit-1.4.0.tar.gz
2.Extract the tar file by using the command
tar -xvzf <file name>
3. Login into MySQL and locate the Plugin Directory of MY SQL using the command below
SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
4.Copy the plugin to plugin directory in MySQL based on your linux server (64 bit/32 bit).
&ltcp server_audit-1.4.0/linux-x86-64/server_audit.so /usr/lib64/mysql/plugin/
&lcchown -R mysql.mysql /usr/lib64/mysql/plugin/server_audit.so

5. Install the MariaDB Audit Plugin into the MySQL Server by this command inside MySQL
&ltINSTALL PLUGIN 'plugin name' SONAME 'filename.so';
6. Once Installation is complete, we'll start the daemon with the following command in the command line:
sudo systemctl start mariadb
7.  The command systemctl doesn't display the outcome of all service management commands, so to be sure we succeed, we'll use the following command:
sudo systemctl status mariadb
If MariaDB has successfully started, the output should contain "Active: active (running)"
&lNext, let's take a moment to ensure that MariaDB starts at boot, using the systemctl enable command, which will create the necessary symlinks: sudo systemctl enable mariadb
8.Next, we'll turn our attention in configuring the syslog FormatSet the Type of Action that will be log (within MySQL)
Connect: connecting and disconnecting to/from the server will be added to the log. An unsuccessful connect will be logged as a failed connect including the error code.
Query: full statement including the values will be logged
Table: Any operation on a table triggered by query will result in an event the MariaDB Audit Plugin can catch to log it directly
SET GLOBAL server_audit_events='CONNECT, QUERY,TABLE';
You need to have root privilege to be able to change the Audit Plugin variables.  With this changed we are ready to enable the auditing, which we now will do by using the following command within MySQL:
SET GLOBAL server_audit_logging=ON;
The full set of variables is found on this page: https://mariadb.com/kb/en/mariadb/server_audit-system-variables/
To make the changes to the configuration of the MariaDB Audit Plugin permanent, we now need to add these settings to my.cnf. This ensures that the same configuration will be used after server restart.
Under [mysqld] in my.cnf, add in
server_audit_events=CONNECT, QUERY, TABLE
server_audit_logging=On


星期五, 8月 18, 2017

用CTE (Temp table)來改寫原SQL的加速方法(支援Oracle/ MSSQL)

使用CTE (Temp table)來改寫原SQL的加速方法

 

CTE 產生出來的暫存資料,生命週期只存在當次的 SQL

 

for example:

 

CTE WITH 的語法

 

WITH cte_name (Column1, Column2, Column3)

AS

(

    SELECT Column1, Column2, Column3

    FROM SomeTable

)

 

SELECT * FROM cte

 

ref:

http://sqlbisam.blogspot.tw/2014/01/cte-common-table-expression.html

https://oolamaru.wordpress.com/2017/03/29/oracle-tunning%E7%B4%80%E9%8C%84%E4%B8%80%E6%AC%A1%E4%BD%BF%E7%94%A8cte%E6%94%B9%E5%96%84sql%E6%95%88%E8%83%BD%E6%A1%88%E4%BE%8B/

https://demonchang.blogspot.com/2015/09/database-common-table-expression-cte.html

星期二, 8月 01, 2017

Oracle 12cR1/12cR2 too many trace files was generted (自動產出大量的trc files)

Oracle 12c 如果發現產出大量的trc files別慌, 想必是因為12c 資料庫會產出關於SQL Cursor trc file,
並在cursor 變為invalid 的時候寫到作業系統中, 相關參數為_kks_obsolete_dump_threshold  (default 1)
只要把他設定為0 即可以關閉並去掉不必要的IO 寫入. 或是apply Patch 22075064

ref1:
Huge Trace Files Created Containing "----- Cursor Obsoletion Dump sql_id=%s -----" (Doc ID 1955319.1)  

When the value is equal to 0, the obsolete cursor dump will be disabled completely:

alter system set "_kks_obsolete_dump_threshold" = 0;

ref2: 
http://www.peasland.net/2015/10/28/cursor-obsoletion-dump/
https://mikedietrichde.com/2015/10/20/where-do-these-large-trace-files-come-from-in-oracle-12c/

It appears this is a known bug in 12.1.0.2. Oracle turned on some diagnostic info and forgot to turn it off. But you can turn it off in your environments with the following:
alter system set "_kks_obsolete_dump_threshold" = 0;


ref3: http://pat98.tistory.com/896
12.2.0.1 Hidden Parameter _kks_obsolete_dump_threshold

12.2 資料庫也有中此BUG

星期五, 7月 28, 2017

Oracle 12c/11g Client 的軟體下載與手冊與Tomcat 環境建議

Oracle 12c R2 Client Download for Linux
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle12c-linux-12201-3608234.html

Oracle 12c R2 Client Download for Aix
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle12c-aix-12201-3677038.html

Oracle 12c R2 Client Download for Solaris
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle12c-sparc-12201-3608235.html

Oracle 11.2.0.4 Client Download Patchset 13390677 (p13390677_112040_platform_4of7.zip)
https://updates.oracle.com/download/13390677.html (需要有Oracle Support帳號)
 Oracle Database Client p13390677_112040_platform_4of7.zip

Oracle Client 安裝方式
http://sajidkhadarabad.blogspot.tw/2015/09/oracle-12c-linux-client-64-bit-step-by.html

*以Oracle所建議的support matrix, 如果要能夠連接12c R2 (12.2) Database, Oracle Client最低版本要求為 11.2.0.3 or 11.2.0.4
ref: Oracle Client 最小要求版本為11.2.0.3 or 11.2.0.4 (11g R2 Client)
Client / Server Interoperability Support Matrix for Different Oracle Versions (Doc ID 207303.1)
--
Tomcat 8 (jdk 1.8) 下載與安裝方式
https://oracle-base.com/articles/linux/apache-tomcat-8-installation-on-linux

Oracle JDBC Driver 連接建議
若從Tomcat 6 / 7 / 8開發程式需要連接新的oracle 資料庫 例如11g/ 12c Oracle DB
只要確認您的openJDK為相容的最低要求版本、並搭配ojdbc6.jar 就可以連接到11g/ 12c Oracle DB

Oracle JDBC Driver 連接注意事項
1.原環境把ojdbc14.jar 替換為ojdbc6.jar 就可以連接

2.在DB 端需要修改$ORACLE_HOME/network/admin/sqlnet.ora 設定 SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
  ref: Oracle 9i/10g Client 透過odbc連12c DB 無法連線 ORA-28040 no matching authentication protocol
       http://jaychu649.blogspot.tw/2017/07/oracle-9i10g-client-odbc12c-db-ora.html
  ref: Copy ojdbc6.jar and ucp.jar into $TOMCAT_HOME/lib directory.
       http://www.oracle.com/technetwork/articles/oem/ucp-jdbc-tomcat-355431.html
  ref: Tomcat 6/7/8 所限制的mininal java 開發環境 (tomcat 7 可支援 openjdk 1.7 、openjdk 1.6、tomcat 8 可支援 openjdk 1.7)
       http://tomcat.apache.org/whichversion.html

Oracle JDBC Driver 8下載連結 (ojdbc8.jar 下載後放置到tomcat資料夾的lib資料夾)
http://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html

Oracle JDBC Driver 7下載連結 (ojdbc7.jar 下載後放置到tomcat資料夾的lib資料夾)
http://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html

Oracle JDBC Driver 6下載連結 (ojdbc6.jar 下載後放置到tomcat資料夾的lib資料夾)
http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html

Instant Client Package - Precompiler (v12.2.0.1.0) download
http://www.oracle.com/technetwork/topics/precomp-112010-084940.html

Oracle® Database Client Installation Guide 12c Release 2 (12.2) for Linux
https://docs.oracle.com/database/122/LACLI/LACLI.pdf

Database Database Client Installation Guide for IBM AIX on POWER Systems (64-Bit) 
https://docs.oracle.com/database/122/AXCLI/AXCLI.pdf

PS. Oracle PRO*C precomplier 通常是跟Oracle Client一起進行安裝, PROC 設定可以參考此連結 :
 安裝Oracle 12c Client 與Pro*C Precompilers 的方法
 http://jaychu649.blogspot.tw/2017/07/oracle-12c-client-proc-precompilers.html

關於開發者可能會需要在Solaris / Oracle Linux 安裝所需的Oracle JAVA JDK套件
Oracle Developer Studio and Oracle Solaris Studio Component Matrix 
http://www.oracle.com/technetwork/server-storage/solarisstudio/training/index-jsp-141991.html

Download Oracle Developer Studio
https://www.oracle.com/tools/developerstudio/index.html

關於Oracle 12.2 的線上文件總連結
https://docs.oracle.com/database/122/

Oracle ASM 單一disk 2TB限制其實在11.2.0.4/ 12c 已經解決

Oracle ASM 單一disk 2TB限制其實在11.2.0.4/ 12c 已經解決
詳情可以參考MOS 文章 ORA-15196 WITH ASM DISKS LARGER THAN 2TB (Doc ID 736891.1)

Ref:
http://www.dbgeek.org/jishu/info/id/45

最近加了一個Oracle DBA line 群組

最近加了一個Oracle DBA line 群組
如果有意願加入此群組的人 請與我聯繫 謝謝!!

[SQL*NET Firewall] 透過sqlnet.ora來限定 可連接Oracle資料庫的主機


Oracle DB 主機 $ORACLE_HONE/network/admin路徑下寫好sqlnet.ora檔案, 透過此檔案來限制可以連接DB的主機清單時.
也有一個較白話的講法SQL*NET Firewall.

如果設定以下三個參數,
如果沒有寫正確, 此檔案有多餘的ASCII 空白、無法被listener正常的解析 , DB主機上面的listener 就無法正常啟動.
Oracle RAC環境下,請把VIPSCAN IP也加到TCP.INVITED_NODES 清單內.


Example for sqlnet.ora :

TCP.VALIDNODE_CHECKING=Y
TCP.INVITED_NODES=(192.168.*, 2001:DB8:200C:433B/32)
TCP.EXCLUDED_NODES=(192.168.2.25, 172.30.*, test.fqdn.net, 2001:DB8:200C:417A/32)


ref1:
Listener Fails to Start Using TCP.VALIDNODE_CHECKING: TNS-12532 TNS:Invalid Argument (Doc ID 287500.1)
*The listener will not start if any of the hosts or ip addresses are note resolvable.  
*The only solution to this issue is to ensure that all the hostnames and ip addresses in the invited nodes list
 are resolvable using ping or nslookup from the host where the listener is starting.

ref2:
http://blog.itpub.net/34596/viewspace-605999/

ref3:
In case you work with SCAN and the GRID Infrastructure GRID_HOME/network/admin/sqlnet.ora file, then don't forget to add your NODE VIP's and SCAN VIP's to your TCP.INVITED_NODES list

Oracle 9i/10g Client 透過odbc連12c DB 無法連線 ORA-28040 no matching authentication protocol

Oracle 9i/10g Client 透過odbc12c DB 無法連線 ORA-28040 no matching authentication protocol

 

 

解決方法

 

@DB Server(Windows)

echo SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 >> %oracle_home%\network\admin\sqlnet.ora

echo SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8 >> %oracle_home%\network\admin\sqlnet.ora

echo SQLNET.ALLOWED_LOGON_VERSION=8 >> %oracle_home%\network\admin\sqlnet.ora

 

@DB Server(Linux)

echo "SQLNET.ALLOWED_LOGON_VERSION_SERVER=8" >> $ORACLE_HOME/network/admin/sqlnet.ora

echo "SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8" >> $ORACLE_HOME/network/admin/sqlnet.ora

echo "SQLNET.ALLOWED_LOGON_VERSION=8" >> $ORACLE_HOME/network/admin/sqlnet.ora

 

 

ref:

https://community.oracle.com/thread/4010944

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...