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 的新增欄位

 

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