星期五, 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.

 

如果設定以下三個參數,

l   如果沒有寫正確, 此檔案有多餘的ASII 空白、無法被listener正常的解析 , DB主機上面的listener 就無法正常啟動.

l   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:

https://blog.dbi-services.com/oracle-12cr2-dataguard-and-tcp-valid_node_checking/

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

星期三, 7月 26, 2017

安裝Oracle 12c Client 與Pro*C Precompilers 的方法

安裝Oracle 12c Client Pro*C Precompilers 的方法

安裝12c client 記得選擇[Custom Install] , 只要勾選 [program]跟[oci]就好

只要看的到 $ORACLE_HOME/precomp/demo/proc 資料夾應該就是完成了pro*c的安裝

ref:
http://mirrors.iyunwei.com/oracle/docs/12.1-E16655-01/server.121/e10638/prcmp_cll_int.htm#UNXAR006

另外設定好 bash shellPATH 環境參數便可

ref:
Configuring Pro*C/C++
https://docs.oracle.com/database/121/LACLI/E55097-10.pdf

其他注意事項
Aix環境如何編譯pro*c 程式
 http://www.cnblogs.com/logicbaby/archive/2011/02/25/1964798.html
 Pro*C文件pc_test中。

 pcscfg.cfg :
 #第一行要include instant client 的套件(其實pro*c程式可以搭配Instance Client 的套件即可)
 sys_include=(/u2/chenlong/instantclient_11_1/sdk/include,/usr/include)
  ref:
  https://docs.oracle.com/cd/B28359_01/appdev.111/b28427/pc_10opt.htm#g30455

 Oracle 11g Release 2 Client对 Aix 作業系统的最低要求为:
 AIX 5L V5.3 TL 09 SP1 ("5300-09-01"), 64 bit kernel
 AIX 6.1 TL 02 SP 1 ("6100-02-01), 64-bit kernel
 AIX 7.1 TL 0 SP 1 ("7100-00-01"), 64-bit kernel
 AIX 7.2 TL 0 SP1 ("7200-00-01"), 64-bit kernel
 https://docs.oracle.com/cd/E11882_01/install.112/e24335/toc.htm

 Compiler Requirements
 IBM XL C/C++ Enterprise Edition for AIX, V9.0 April 2008 PTF:

 Even if you do not install the IBM XL C/C++ compiler, you require the compiler for AIX Runtime Environment Component. 
 For AIX 5.3 and AIX 6.1:
 IBM XL C/C++ for AIX, V10.1 Runtime Environment
 For AIX 7.1:
 September 2010 Runtime for XL C/C++ for AIX, V11.1
 For AIX 7.2:
 November 2015 Runtime for XL C/C++ for AIX, V13.1

 Patch Requirement
  有相當多的OS patch要上, 詳情請參照
  https://docs.oracle.com/cd/E11882_01/install.112/e24335/toc.htm#CHDIGJJB

---------------------------------------------------------------------------
 Oracle 11g Release 2 Client对 Solaris 作業系统的最低要求为:
 Compiler Requirements
  Oracle Solaris Studio 12

 Operating System Requirements:
  Oracle Solaris 10 U6 (5.10-2008.10)
  Oracle Solaris 11 11/11 X86

 Package Requirements
  Oracle Solaris 10
  SUNWarc
  SUNWbtool
  SUNWhea
  SUNWlibC
  SUNWlibm
  SUNWlibms
  SUNWsprot
  SUNWtoo
  SUNWi1of
  SUNWi1cs (ISO8859-1)
  SUNWi15cs (ISO8859-15)
  SUNWxwfnt
  SUNWcsl

  Oracle Solaris 11
  pkg://solaris/developer/build/make
  pkg://solaris/developer/assembler

  Patches for Oracle Solaris 10:
  120754-06: SunOS 5.10_x86 libmtsk
  119961-05: SunOS 5.10_x86: Assembler
  119964-14: SunOS 5.10_x86 Shared library patch for C++_x86
  137104-02
  139575-03
  139556-08
  141415-04
  141445-09
 https://docs.oracle.com/cd/E11882_01/install.112/e24351/toc.htm


以上是在網路上找到相關的文件, 有空在找個時間來驗證一下囉~

星期五, 6月 16, 2017

Data Guard RMAN delete Archive log policy 注意事項

Data Guard RMAN delete Archive log policy 注意事項

For 11g版本以後 :
1.記得在Primary/ Standby db 加上 CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY; 否則archive log 在standby db : ASM DISKGROUP RECO 會滿,
無法以rman delete expired archivelog all; 指令進行清除

2.記得在Primary/ Standby db 加上_log_deletion_policy='ALL';  第一點才會正常的運作work.
   SQL>  alter system set "_log_deletion_policy"='ALL' scope=spfile;

因為Primary DB / Standby DB都有角色切換的機會, 所以都要進行設定.

ref:
https://justiono.wordpress.com/2010/04/25/archivelog-files-deletion-policy-in-data-guard-environment/
http://jaychu649.blogspot.tw/2012/01/oracle-11g-active-dataguard-config_18.html
http://jaychu649.blogspot.tw/2011/11/archive-log.html

星期四, 6月 15, 2017

[轉載] ASM Disk Header 備份與還原

Ref:

--
1. dd備份恢復

備份voting disk:
dd if=/dev/raw/raw3 of=/tmp/votedisk_lhr.bak bs=1024k count=4
恢復voting disk
dd if=/tmp/votedisk_lhr.bak of=/dev/raw/raw3 bs=1024k count=4

註:Oracle  11g 不推薦使用dd來進行ASM Header備份恢復,而改採用MD_Backup/ MD_restore指令進行 ;  ASM Disk Header一般是前4K

2. kfed恢復ASM Disk Header

dd if=/dev/rhdisk2 of=/asm_rhdisk2_dd.bak bs=1024 count=4
dd if=/dev/zero of=/dev/rhdisk2 bs=1024 count=4
kfed repair /dev/rhdisk2
關於kfedkfodamdu的更多內容可以參考:http://blog.itpub.net/26736162/viewspace-1694198/

3. md_backupmd_restore ASM Disk Header (11g 以後可以透過md_backup方式來備份ASM disk header)


asmcmd md_backup /oracle/app/11.2.0/tmp/asm_md_backup.bak

#坊間謠傳ASM Disk Header4K , 請在您的test environment實際測試過
dd if=/dev/rhdisk2 of=/asm_rhdisk2_dd.bak bs=1024k count=4
dd if=/dev/zero of=/dev/rhdisk2 bs=1024k count=4
crsctl stop has -f
crsctl start has
ASMCMD [+] > startup force nomount;
ASMCMD [+] > md_restore /oracle/app/11.2.0/tmp/asm_md_backup.bak

4.如果原本ASM DiskGroup是NORMAL Redunancy, 那麼單一disk corruption或壞掉, 可以透過以下方式修回:

@db1, db2
crsctl stop crs -f

@db1
crsctl start crs -excl -nocrs
dd if=/dev/zero of=/dev/mapper/votingdisk bs=1m count=10240
sqlplus / as sysasm
SQL> alter diskgroup VOTING add disk '/dev/mapper/votingdisk' force;
crsctl stop crs -f

@db1, db2
crsctl start crs


關於md_backupmd_restore更多測試內容可以參考:http://blog.itpub.net/26736162/viewspace-2121309/

[轉載] Mount ASM diskgroups with new ASM instance

Mount ASM diskgroups with new ASM instance

Ref:

ASM Disks 透過Storage Snapshot mirror 到異機去, 而異機上面沒有任何duplicated OS環境,
會需要透過一些特殊手法讓新安裝的Grid Infrastructure 認到ASM LUN Header, 進而掛載並讀取裡面的資料檔案

#Prerequisites 
Install Grid Infrastructure required RPM, ASMLIB RPM

# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
# oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
FRA1

Install "Oracle Grid Infrastructure software only" option

After installation finished run noted perl script as root to configure Grid Infrastructure for a Stand-Alone server.
For my configuration script looks like this:
To configure Grid Infrastructure for a Stand-Alone Server run the following command as the root user:
/u01/app/11.2.0.1/grid/perl/bin/perl -I/u01/app/11.2.0.1/grid/perl/lib -I/u01/app/11.2.0.1/grid/crs/install /u01/app/11.2.0.1/grid/crs/install/roothas.pl

Start cssd if it's not running.

# ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE
ora.diskmon
      1        OFFLINE OFFLINE
# ./crs_start ora.cssd
Attempting to start `ora.cssd` on member `asterix`
Attempting to stop `ora.diskmon` on member `asterix`
Stop of `ora.diskmon` on member `asterix` succeeded.
Attempting to start `ora.diskmon` on member `asterix`
Start of `ora.diskmon` on member `asterix` succeeded.
Start of `ora.cssd` on member `asterix` succeeded.

手動再 $GRID_HOME/dbs 下編輯init+ASM.ora
*.asm_diskstring='/dev/oracleasm/disks'
*.asm_power_limit=1
*.diagnostic_dest='/u01/app/grid'
*.instance_type='asm'
*.large_pool_size=12M
*.remote_login_passwordfile='EXCLUSIVE'

Register and start ASM instance.

$ export ORACLE_SID=+ASM
$ export ORACLE_HOME=/u01/app/11.2.0.1/grid
$ srvctl add asm -p $ORACLE_HOME/dbs/init+ASM.ora  *註1
$ srvctl start asm
$ srvctl status asm
ASM is running on asterix

./asmca

Click "Mount all" to mount them all.


Install Oracle database software and create parameter file in "$ORACLE_HOME/dbs" to start database.
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
$ export ORACLE_SID=ora11gr2

$ cd $ORACLE_HOME/dbs
$ cat initora11gr2.ora
*.spfile='+DATA1/ora11gr2/spfileora11gr2.ora'

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 29 14:29:37 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  668082176 bytes
Fixed Size                  2216344 bytes
Variable Size             222301800 bytes
Database Buffers          436207616 bytes
Redo Buffers                7356416 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA1/ora11gr2/datafile/system.297.844627929
+DATA1/ora11gr2/datafile/sysaux.265.844627967
+DATA1/ora11gr2/datafile/undotbs1.266.844627991
+DATA1/ora11gr2/datafile/users.267.844628031
+DATA2/ora11gr2/datafile/marko.261.859213577

Database is successfully opened and you can register instance using SRVCTL command.
$ srvctl add database -d $ORACLE_SID -o $ORACLE_HOME -p $ORACLE_HOME/dbs/initora11gr2.ora
$ srvctl start database -d $ORACLE_SID

Final status.
$ ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA1.dg
               ONLINE  ONLINE       asterix
ora.DATA2.dg
               ONLINE  ONLINE       asterix
ora.FRA1.dg
               ONLINE  ONLINE       asterix
ora.asm
               ONLINE  ONLINE       asterix                  Started
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       asterix
ora.diskmon
      1        ONLINE  ONLINE       asterix
ora.ora11gr2.db
      1        ONLINE  ONLINE       asterix                  Open




*註1
在12c Grid的環境要手動註冊ASM instance, 需指定ASM password file如下:

srvctl add asm -spfile +ORCL_DATA/asm/asmparameterfile/registry.253.917720447 -pwfile $ORACLE_HOME/dbs/orapw+ASM -diskstring '/dev/asmdisk/*'
srvctl add listener -l listener_ASM -o $ORACLE_HOME
srvctl start listener -l listener_ASM
srvctl config asm

ref:

https://vikasdba-bi.blogspot.tw/2017/06/upgrade-grid-infrastructure-11204-to.html

星期四, 6月 08, 2017

OEM 13c Cloud Control 如何刪掉已經連不上的主機

Ref:
OEM 13c : Remove Target with EMCLI Terminal session
https://dbafaq.wordpress.com/2016/03/15/oem-13c-remove-target-with-emcli-terminal-session/

主要是因為DB資料庫設備搬遷以後, 舊的IP已經連接不到主機了
所以需要透過指令方式刪掉該台主機

[oracle@oem_server bin]$ /u01/app/oracle/product/oms/bin/emcli login -username=sysman
Enter password

Login successful
[oracle@oem_server bin]$ ./emcli get_targets |grep 10.
-bash: ./emcli: No such file or directory
[oracle@oem_server bin]$ /u01/app/oracle/product/oms/bin/emcli get_targets |grep 192.168
4       Agent Unreachab  host                  192.168.0.x                                           
4       Agent Unreachab  oracle_emd            192.168.0.x:3872                 
1       Up               oracle_emd            

[oracle@oem_server bin]$ /u01/app/oracle/product/oms/bin/emcli delete_target -name="192.168.0.x" -type="host" -delete_monitored_targets
Host target cannot be deleted via this command. Host target can only be removed
when decommissioning an Agent from Enterprise Manager.

Follow these steps to decommission the Agent, which removes all targets,
including host monitored by it:
1. Navigate to the Agents home page.
2. Decommission the Agent via clicking on Agent -> Target Setup -> Agent
Decommission.
[oracle@oem_server bin]$ /u01/app/oracle/product/oms/bin/emcli delete_target -name="192.168.0.x:3872" -type="oracle_emd" -delete_monito
red_targets
Target "192.168.0.x:3872:oracle_emd" deleted successfully
[oracle@oem_server bin]$


最後再回到OEM 13c畫面, 從[Targets] ->[All Targets]去搜尋該台主機
發現已經成功拿掉了.

[Oracle VM 3.4] 如何Reset OVM Manager 與 OVS Server

[Oracle VM 3.4] 如何Reset OVM Manager OVS Server (清除所有VM內部的組態 : dbpool, dbpool_VIP, VM repository, Network...etc)

 

1.備份OVMM 上面的組態

[root@ovmm ~]# cat /u01/app/oracle/ovm-manager-3/.config

DBTYPE=MySQL

DBHOST=localhost

SID=ovs

LSNR=49500

OVSSCHEMA=ovs

APEX=8080

WLSADMIN=weblogic

OVSADMIN=admin

COREPORT=54321

UUID=0004fb0000010000b6da2e8330b1856b

BUILDID=3.4.2.1384

 

[root@ovmm sysconfig]# cat /etc/sysconfig/ovmm

JVM_MEMORY_MAX=4096m

JVM_MAX_PERM=512m

RUN_OVMM=YES

DBBACKUP=/u01/app/oracle/mysql/dbbackup

DBBACKUP_CMD=/opt/mysql/meb-3.12/bin/mysqlbackup

UUID=0004fb0000010000b6da2e8330b1856b

 

2.備份OVMM上面的MySQL Database

[root@ovmm sysconfig]# /u01/app/oracle/ovm-manager-3/ovm_tools/bin/BackupDatabase -w

Enter your OVM Manager username: admin

Enter your OVM Manager password:

 

INFO:  Backup job starting with destination:

        /u01/app/oracle/mysql/dbbackup/ManualBackup-20170606_211402

 

        Job Id   = 'Start Backup to: ManualBackup(1496754841989)

Uri: https://localhost:7002/ovm/core/wsapi/rest/Job/14967548419

89'

        Job Name = 'Start Backup to: ManualBackup'

 

INFO:  Backup job finished

 

[root@ovmm sysconfig]#

 

 

(optional) 如果要回復OVMM DB , 方法如下

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

Restore :

20170606_211402

su - oracle

bash /u01/app/oracle/ovm-manager-3/ovm_tools/bin/RestoreDatabase.sh ManualBackup-20170606_211402

 

 

#打包備份檔案用, 如果要migration OVMM DB, 需要Copy這個資料夾到其他機器

#tar -zcvf ovmmdb_bak.tar.gz /u01/app/oracle/mysql/dbbackup/ManualBackup-20170606_211402/*

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

 

service ovmm stop

service ovmcli stop

 

 

 

[root@ovmm ~]# sh /u01/app/oracle/ovm-manager-3/bin/ovm_upgrade.sh --deletedb --dbsid=ovs --dbpass=Welcome1 --dbport=49500

 

COMMAND: /u01/app/oracle/ovm-manager-3/ovm_upgrade/bin/ovm_upgrade.sh --deletedb --dbsid=ovs --dbpass=Welcome1 --dbport=49500

 

Copying deleted classes files to patch path location

2017-06-06 21:29:19,581  INFO Oracle OVM Manager Upgrade Processor

2017-06-06 21:29:19,582  INFO

Jun 06, 2017 9:29:20 PM oracle.security.jps.JpsStartup start

INFO: Jps initializing.

Jun 06, 2017 9:29:21 PM oracle.security.jps.JpsStartup start

INFO: Jps started.

2017-06-06 21:29:21,415  INFO Upgrade Initialization Starting

2017-06-06 21:29:21,508  INFO Oracle Distributed Object Fabric (ODOF): Copyright (C) 2007, 2016 Oracle.  All rights reserved.

2017-06-06 21:29:21,514  INFO ODOF Version: 1.3.1.4

2017-06-06 21:29:21,514  INFO Initializing...

2017-06-06 21:29:22,384  INFO Initialization Complete

2017-06-06 21:29:22,385  INFO Upgrade Initialization Complete

2017-06-06 21:29:22,385  INFO Database Wipe Starting

2017-06-06 21:29:22,386  INFO Wiping Exchange

2017-06-06 21:29:22,410  INFO Initializing / Clearing Database Tables

2017-06-06 21:29:23,583  INFO Wiping Complete!

2017-06-06 21:29:23,584  INFO Database Wipe Complete

 

service ovmcli start

service ovmm stop

 

#生成新的憑證 (admin/ Welcome1 for OVM console : https://ovmm:7002/ovm/console )

sh /u01/app/oracle/ovm-manager-3/bin/configure_client_cert_login.sh

service ovmm start

 

至此, OVMM 的組態已經清空

 

3.Reset OVS db

已實際在Oracle VM 3.4 上面實際測試成功

 

ref: http://ask.chinaunix.net/question/785390

 

Oracle VM server 如何刪除vm manager的管理

登入OVS作業系統上,停止ovs-agent

# service ovs-agent stop

刪除ovs-agent-db

# mv /etc/ovs-agent/db /etc/ovs-agent/db.bak

啟動ovs-agent

# service ovs-agent start

 

接著登入到OVMM Console , OVMM [Servers and VMs] Tab discovery OVS主機即可

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...