f 2021 ~ 迪貝之家

Microsoft SQL Server Log Shipping

SQL 7就有的技術,簡單的說就是資料庫複本資料同步的技術,而這份複本可以開放讀取的功能以分散營業資料庫的負載,也可純粹當成備援資料庫,不管是在本地還是異地。怎麼達成? 透過SQL Agent自動化排程進行備份、傳檔及復原等作業。

Oracle GolGateden

機房搬遷跨Site能即時同步資料庫利器

Nagios 資料庫維運自動化

一開始設計這架構時,就排除使用remote agent的想法因為在專業分工的組織下,要求安裝新軟體於既有系統是一件不太可行的方案,既然身為DBA就只能把資料庫instance當作是一個最大的agent 想辦法在資料庫內做到我想做的事情

This is default featured post 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured post 5 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

神奇的Mongodb $elemMatch Operator

 

這是個神奇的東西
既可做查詢條件
也可做為Projection的設定
一開始我其實搞不太清楚它與field.field.field....格式間的差異
看了資料結構後
我才恍然大悟
field.field.field.... 是給只有單一document
elemMatch 是用來找array的內容是document
舉個例子來看,就清楚了
這是relationships欄位的資料結構
它是document array























db.companies.find({ "relationships":
                      { "$elemMatch": { "is_past": true,
                                        "person.first_name": "Mark" } } },
                  { "name": 1 }).pretty()

我們接著來看elemMatch怎麼當作Projection
先瞧瞧scores的資料結構
教材裡的設定
是只顯示超過85分的document
而不是給你所有的scores的資料
db.grades.find({ "class_id": 431 }, { "scores": { "$elemMatch": { "score": { "$gt": 85 } } } }).pretty()

其實Mongodb都把他們的教學影片公開在youtube
我只要註明出處
就不會違反著作權了
更何況我又沒拿來營利....
出處:
https://youtu.be/Vo3SntUloY8
https://youtu.be/8FDWzlkB-oo

碰到Oracle Lock Wait

 

用了10g以上的版本後
最常用的工具就是awr 及addm
以前常用的script都丟掉了
只是最近又幹起了Programmer的工作
你就不太可能像以前幹dba一樣
知道詳細的資料庫連接資料
碰到都是人家已經在工具UI已經弄妥的設定
你只能直接查internal view來解決問題
昨天要refresh整個schema,然後重匯資料
結果有lock,無法清空
臨時間又懶得去翻找以前的東西
感謝Internet
資料隨手可得阿
以前的老dba都雪藏秘珍這種東西啦~~
所以....這些人現在都活不下去了
昨天對我有幫助的是下列這段語法
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
砍掉該session,就能繼續做事了
出處 :
http://www.dba-oracle.com/t_find_oracle_locked_objects.htm

Mongodb Logic Operator : 到底是我英文爛,還是它描述的糟糕阿...

 

我先寫一下出處
省得被告侵犯著作權
不過
我想它應該也懶得花大錢
就為了這點小事來台灣告我
美國找律師粉貴阿
告我又拿不到什麼錢
這是mongodb的lab問題

出處:
https://university.mongodb.com/mercury/M001/2021_March_16/chapter/Chapter_4_Advanced_CRUD_Operations/lesson/5f36f52104e9ffa9293e896es/problem
沒辦法,上禮拜聽過板橋智財法院的法官的演講
我寫的Query :
db.companies.find({"$and" : [{"founded_year":2005},
{"$or" :[ {category_code:"web"},
                {"founded_month":10}, 
                {category_code:"web"}
             ]
},
{"$or" :[ {category_code:"web"},
                {category_code:"web"}
              ]
}                                               ]
                               }.count()
這是mongodb 給出來的答案
db.companies.find({ "$and": [{ "$or": [
                                                                    { "founded_year": 2004 },
                                                                    { "founded_month": 10 }
                                                                    ]
                                                    },
                                                   { "$or": [
                                                                   { "category_code": "web" },
                                                                   { "category_code": "social" }
                                                                  ]
                                                   }
                                                 ]
                                   }).count()
看了答案後,我傻眼了

MongoDB 的新增欄位

 

RDBMS是用alter table 指令
mongodb因為schema是動態的
在update資料時
如果沒有這個欄位的話
就是動態新增進去
我們來看zips的資料結構
db.zips.find({}).pretty()
我要在capital底下新增一個capital_2欄位
db.zips.updateMany({},{ $set : { "capital_2" : "false" }})
指令異動了29470筆資料
我們來看異動後的結構

MongoDB $push operator for array element

 db.grades.find({ "student_id" :  250, "class_id" : 339 }).pretty()





db.grades.update({ "student_id" :  250, "class_id" : 339 },{ $push : {
                                                                     "scores" :{ 
    "type" : "History", 
           "score" : 95.000
                             } 
              }
       }       
                            )




 db.grades.find({ "student_id" :  250, "class_id" : 339 }).pretty()



Oracle DBMS_METADATA.GET_DDL Generate Index Create Script

 set pagesize 0

set long 90000

set feedback off

set lines 100

set linesize 200

set echo off

col ddl format a1000

set head off

BEGIN

     DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', false);

     DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', false);

     DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false);

     DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);

 


END;

 /

select DBMS_METADATA.GET_DDL('INDEX', index_name )||' tablespace sida;' ddl

from user_indexes

where  index_type <> 'LOB'

AND   index_name  not like 'I_MLOG%';

mogodb dump/restore --備出test , restore 回supplies

 

C:\mongodb\bin\mongodump --uri "mongodb+srv://steven:xxxxx@mymongo.xxxx.mongodb.net/test"

2021-03-26T22:35:57.153-0700 WARNING: On some systems, a password provided directly in a connecti
on string or using --uri may be visible to system status programs such as `ps` that may be invoked b
y other users. Consider omitting the password to provide it via stdin, or using the --config option
to specify a configuration file with the password.
2021-03-26T22:36:02.216-0700 writing test.sales to dump\test\sales.bson
2021-03-26T22:36:03.176-0700 [........................] test.sales 0/5000 (0.0%)
2021-03-26T22:36:06.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:09.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:12.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:15.173-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:18.173-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:21.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:24.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:27.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:30.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:33.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:36.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:39.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:42.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:45.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:48.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:51.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:54.173-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:36:57.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:37:00.173-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:37:03.173-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:37:06.173-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:37:09.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:37:12.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:37:15.173-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:37:18.172-0700 [........................] test.sales 101/5000 (2.0%)
2021-03-26T22:37:20.247-0700 [########################] test.sales 5000/5000 (100.0%)
2021-03-26T22:37:20.248-0700 done dumping test.sales (5000 documents)


C:\Users\IEUser\Desktop\dump\test>cd ..
C:\Users\IEUser\Desktop\dump>xcopy /E /I test supplies
test\sales.bson
test\sales.metadata.json
2 File(s) copied

C:\Users\IEUser\Desktop\dump>dir
Volume in drive C is Windows 81
Volume Serial Number is FC92-3303
Directory of C:\Users\IEUser\Desktop\dump
03/26/2021 10:44 PM <DIR> .
03/26/2021 10:44 PM <DIR> ..
03/26/2021 10:44 PM <DIR> supplies
03/26/2021 10:36 PM <DIR> test
0 File(s) 0 bytes
C:\Users\IEUser\Desktop\dump>cd ..
C:\Users\IEUser\Desktop>dir dump
Volume in drive C is Windows 81
Volume Serial Number is FC92-3303
Directory of C:\Users\IEUser\Desktop\dump
03/26/2021 10:44 PM <DIR> .
03/26/2021 10:44 PM <DIR> ..
03/26/2021 10:44 PM <DIR> supplies
03/26/2021 10:36 PM <DIR> test
0 File(s) 0 bytes
4 Dir(s) 42,106,376,192 bytes free

C:\Users\IEUser\Desktop>C:\mongodb\bin\mongorestore --uri "mongodb+srv://steven:xxx@mymongo.xxx.mongodb.net" -d sample_supplies dump\supplies

2021-03-26T22:48:48.676-0700 The --db and --collection flags are deprecated for this use-case; pl
ease use --nsInclude instead, i.e. with --nsInclude=${DATABASE}.${COLLECTION}
2021-03-26T22:48:48.685-0700 building a list of collections to restore from dump\supplies dir
2021-03-26T22:48:48.886-0700 reading metadata for sample_supplies.sales from dump\supplies\sales.
metadata.json
2021-03-26T22:48:49.086-0700 restoring sample_supplies.sales from dump\supplies\sales.bson
2021-03-26T22:48:51.272-0700 [#########...............] sample_supplies.sales 1.66MB/4.13MB (4
0.3%)
2021-03-26T22:48:53.013-0700 [########################] sample_supplies.sales 4.13MB/4.13MB (1
00.0%)
2021-03-26T22:48:53.019-0700 no indexes to restore
2021-03-26T22:48:53.021-0700 finished restoring sample_supplies.sales (5000 documents, 0 failures
)
2021-03-26T22:48:53.025-0700 5000 document(s) restored successfully. 0 document(s) failed to rest
ore.


Oracle like 及 in 的結合 -- regexp_like

 

select substr(address,1,7)
from urtb
where regexp_like(address , '台北市|台北縣')
select substr(address,1,7)
from urtb
where not regexp_like(address , '台北市|台北縣')
參考資料 :
1.Oracle REGEXP_LIKE
2.How to combine “LIKE” and “IN” condition in sql ?
set serveroutput on
declare
string_pattern nvarchar2(200) := '台北市|台北縣|';
begin
string_pattern := rtrim(string_pattern,'|');
dbms_output.put_line(string_pattern);
for i in (
select count(*) cnt from urtb where not regexp_like(address , string_pattern)
)
loop
dbms_output.put_line(i.cnt);
end loop;
end;
/

Oracle 用regrexp_replace來做字串轉換

 

select substr(full_address,1,6),
regexp_replace(substr(full_address,1,6) ,'台北市','臺北市的xxx')
from urtb
where ur_county_name = '台北市'

參考資料 :
Example 4 : REGEXP_REPLACE

Mongodb 的基本認證機制

 

在建立mongo的基本安全連線機制上
其實會碰到一個問題
帳號與基本認證啟用之間
會有一個雞生蛋,蛋生雞的問題
在3T的技術章節Database Administration & Security
這篇MongoDB User Authentication: A Step-by-Step Guide
的這一段話,我覺得最實用
Enable user authentication after creating the user administrator
This straightforward approach requires the following steps:

1.Launch your MongoDB server without authentication.
這就是安裝好的預設狀態,ip 是聽127.0.0.1
帳號認證還沒啟動的時候

2.Create the system user administrator.
直接建一個具root role的user

3.Restart your MongoDB, this time with authentication enabled. You can now connect to your server as that user administrator.
此時當然就是把ip及base security設上
然後重開DB Instance

mongodb

再來就是參照底下這兩篇的介紹
MongoDB Users and Roles Explained – Part 1
MongoDB Users and Roles Explained – Part 2

/crs/install/rootcrs.sh -prepatch 出現CLSRSC-180

 

昨天打了第一台的Patch
想說今天來打node 2
靠.......
怎麼出現類似底下的錯誤
Command execution failed during patching in home:<GRID_HOME>, host: Node 1.
Command failed: <GRID_HOME>/perl/bin/perl -I <GRID_HOME>/perl/lib -I <GRID_HOME>OPatch/auto/dbtmp/bootstrap_Node 1/patchwork/crs/install <GRID_HOME>/OPatch/auto/dbtmp/bootstrap_NODE1/patchwork/crs/install/rootcrs.pl -prepatch
Command failure output:
Using configuration parameter file:<GRID_HOME>/OPatch/auto/dbtmp/bootstrap_Node1/patchwork/crs/install/crsconfig_params
The log of current session can be found at:
<ORACLE_BASE>/oracle/crsdata/Node1/crsconfig/crspatch_Node1_6_2019-07-22_12-52-12AM.log
2019/07/22 12:52:17 CLSRSC-180: An error occurred while executing the command 'cluutil -chkshare -oh <GRID_HOME> -localnode Node 1 -nodelist Node A,Node B'
依照文件2570772.1
我去調整了crs/install/crsconfig_params 這個檔案的內容
不work.......
真是腦袋三條線了
metalink說180是個bug ...
無言了...真有這麼多bug
只好再繼續找文件
找到這一篇2005044.1
它標題就提到,可能是ssh passwordless出了問題
我就去測了ssh 連通本機
還真的不通ㄝ........
怎麼回事啊!!!
也不過才一晚
怪了~~
我就reanme authorized_keys這個檔案後
透過ssh-copy-id 再重建一次
恩.....
還是不work.....
挖哩........搞啥阿....
好吧~~我就把.ssh這個目錄整個rename掉
然後照著300548.1文件內容
想把ssh passwordless 重建一次
key 建完
然後用ssh-copy-id 來產出authorized_keys
還是不行....
我就想說...怎麼可能.....
只好照著文件一步一步來做
但是看到這一段的時候
我才恍然大悟了
我習慣把patch放在/home/grid下
怕用Oracle帳號打DB Patch時出現權限問題
就把/home/grid目錄權限改為775
把它改回755就解決了

Ansible 出現這個錯誤 Could not find the requested service apache2: host

 

這是check syntac 的執行記錄
我昨天測了一下午
ansible-playbook playbook.yml -C
這個劇本就是測不過
去找了Internet
怪了~~ 文不對題
這是output
$ ansible-playbook playbook.yml -C

PLAY [drupal] ************************************************************************************************************************
TASK [Gathering Facts] ***************************************************************************************************************
The authenticity of host '192.168.88.7 (192.168.88.7)' can't be established.
ECDSA key fingerprint is SHA256:cWVg1Hfzq9h5YHIKUWwhE+jz1d+f8IltzWWvB9jbr5Y.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
ok: [192.168.88.7]
TASK [Update apt cache if needed.] ***************************************************************************************************
changed: [192.168.88.7]
TASK [Get software for apt repository management.] ***********************************************************************************
changed: [192.168.88.7]
TASK [Add ondrej repository for later versions of PHP.] ******************************************************************************
changed: [192.168.88.7]
TASK [Install Apache, MySQL, PHP, and other dependencies.] ***************************************************************************
changed: [192.168.88.7]
TASK [Disable the firewall (since this is for local dev only).] **********************************************************************
changed: [192.168.88.7]
TASK [Start Apache, MySQL, and PHP.] *************************************************************************************************
failed: [192.168.88.7] (item=apache2) => {"ansible_loop_var": "item", "changed": false, "item": "apache2", "msg": "Could not find the requested service apache2: host"}
failed: [192.168.88.7] (item=mysql) => {"ansible_loop_var": "item", "changed": false, "item": "mysql", "msg": "Could not find the requested service mysql: host"}
PLAY RECAP ***************************************************************************************************************************
192.168.88.7 : ok=6 changed=5 unreachable=0 failed=1 skipped=0 rescued=0 ignored=0
今天我腦袋瓜應該是清楚了點
我突然想到了
-C 是syntax check
根本不會真的進行安裝的TASK
playbook裏頭有一段是要啟動服務
在沒安裝套件的狀況下
是要怎麼啟動該服務阿...
豬頭.....
這是最簡單的邏輯阿
因此我把-C拿掉後執行
哈哈...一切正常了
PLAY [drupal] ************************************************************************************************************************
TASK [Gathering Facts] ***************************************************************************************************************
ok: [192.168.88.7]
TASK [Update apt cache if needed.] ***************************************************************************************************
ok: [192.168.88.7]
TASK [Get software for apt repository management.] ***********************************************************************************
changed: [192.168.88.7]
TASK [Add ondrej repository for later versions of PHP.] ******************************************************************************
changed: [192.168.88.7]
TASK [Install Apache, MySQL, PHP, and other dependencies.] ***************************************************************************
TASK [Disable the firewall (since this is for local dev only).] **********************************************************************
changed: [192.168.88.7]
TASK [Start Apache, MySQL, and PHP.] *************************************************************************************************
ok: [192.168.88.7] => (item=apache2)
ok: [192.168.88.7] => (item=mysql)
TASK [Enable Apache rewrite module (required for Drupal).] ***************************************************************************
changed: [192.168.88.7]
TASK [Add Apache virtualhost for Drupal.] ********************************************************************************************
changed: [192.168.88.7]
TASK [Enable the Drupal site.] *******************************************************************************************************
changed: [192.168.88.7]
TASK [Disable the default site.] *****************************************************************************************************
changed: [192.168.88.7]
TASK [Adjust OpCache memory setting.] ************************************************************************************************
changed: [192.168.88.7]
TASK [Create a MySQL database for Drupal.] *******************************************************************************************
changed: [192.168.88.7]
TASK [Create a MySQL user for Drupal.] ***********************************************************************************************
TASK [Create a MySQL user for Drupal.] ***********************************************************************************************
ok: [192.168.88.7]
TASK [Download Composer installer.] **************************************************************************************************
changed: [192.168.88.7]
TASK [Run Composer installer.] *******************************************************************************************************
changed: [192.168.88.7]
TASK [Move Composer into globally-accessible location.] ******************************************************************************
changed: [192.168.88.7]
TASK [Ensure Drupal directory exists.] ***********************************************************************************************
changed: [192.168.88.7]
TASK [Check if Drupal project already exists.] ***************************************************************************************
ok: [192.168.88.7]
TASK [Create Drupal project.] ********************************************************************************************************
changed: [192.168.88.7]
TASK [Add drush to the Drupal site with Composer.] ***********************************************************************************
changed: [192.168.88.7]
TASK [Install Drupal.] ***************************************************************************************************************
RUNNING HANDLER [restart apache] *****************************************************************************************************
changed: [192.168.88.7]
PLAY RECAP ***************************************************************************************************************************
192.168.88.7 : ok=24 changed=19 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0