【轉】一份高階技術支援筆試題（主要是Oracle和Unix，含參考答案）

一、    unix相關問題

1、    你常用的主機和OS是什麼？(以下Unix相關問題儘量基於你常用的Unix環境作答)

    我常用的是Sun主機，作業系統一般是Solaris8或Solaris10。

2、    如何檢視cpu資訊，記憶體、交換區（swap）大小和使用情況

    CPU大小：
        sar -u t n 注意%usr,%sys,%wio,%idle(如果wio列的值很大說明系統的IO存在瓶頸，CPU花費了很大的時間去等待IO的完成; Idle很小說明系統CPU很忙。)
        AIX：lsdev -C|grep Process 或 topas(root登陸)
        Solaris：psrinfo -v 或 mpstat
    記憶體大小：
        vmstat t n 報告關於記憶體的統計資訊
        AIX：lsattr -E -l sys0 -a realmem 或 topas(root登陸) 或 svmon(root登陸)
        HP-UX：grep MemTotal /proc/meminfo 或 top 或 glance
        Linux：dmesg | grep "Physical："
        Solaris：prtconf|grep Mem 或 /usr/platform/sun4u/sbin/prtdiag|grep Mem
        Tru64：/bin/vmstat -P | grep "Total Physical Memory"
    顯示分配的記憶體段：
        ipcs -pmb
    手動移除記憶體段分配：
        ipcrm -m 96 （96是記憶體段id，用ipcs查到）
        有的情況下記憶體例項崩潰，但是Oracle的記憶體段卻沒釋放。注意，這個命令可以使Oracle癱瘓。只有後臺程式異常死掉的時候才考慮用。
    交換區：
        AIX：lsps -a
        HP-UX：swapinfo -tam
        Linux：
        Solaris：swap -s
        Tru64：

3、    如何檢視磁碟大小和使用情況，及其檢視某一個目錄空間各檔案大小資訊

    df -k
    du -sk /opt/testuser/* | sort -n 檢視目錄下各檔案大小並排序
    du -sk /opt/testuser/ 檢視總目錄大小

4、    如何檢視主機名、主機型別，OS版本和IP地址

    主機：uname -n
    IP：ifconfig -a

5、    如何檢視系統程式資訊，你常用的程式管理命令有哪些？

    檢視：prstat -a 或 top
    管理：pldd pid 顯示連結到每個程式的動態連結庫
        pstack pid 顯示十六進位制與符號堆跟蹤資訊
        fuser files
        pwdx pid 檢視程式執行目錄
        ptree pid 檢視程式樹資訊

6、    列舉幾種診斷IO、CPU、效能狀況的方法

    w    顯示高資源會話簡單的資訊
    top 顯示cpu和記憶體資源利用情況
    iostat t n列出系統i/o負載（每t秒顯示一次，共顯示n次，下同）
    當系統存在IO的問題，可以從以下幾個方面解決：
        ＊    聯絡相應的作業系統的技術支援對這方面進行最佳化，比如hp-ux在劃定卷組時的條帶化等方面。
        ＊    查詢Oracle中不合理的sql語句，對其進行最佳化。
        ＊    對Oracle中訪問量頻繁的表除合理建索引外，再就是把這些表分表空間存放以免訪問上產生熱點，再有就是對錶合理分割槽。
    vmstat t n報告關於核心執行緒的統計資訊（IRIX系統中對應osview）
    當發現vmstat中pi列非零，memory中的free列的值很小，說明記憶體方面應調節：
        ＊    劃給Oracle使用的記憶體不要超過系統記憶體的1/2,一般保在系統記憶體的40%為益。
        ＊    為系統增加記憶體
        ＊    如果你的連線特別多，可以使用MTS的方式
        ＊    打全補丁，防止記憶體漏洞
    sar -u t n 觀察CPU 的使用情況
    sar -w t n 觀察交換（swapping）的使用情況
    sar -b t n 顯示buffer的活動情況

7、    csh和ksh在設定環境變數上有什麼不同？

    csh： setenv NAME value1
    ksh： NAME=value1; export NAME

8、    建立組dba，然後建立使用者oracle，其home目錄為/opt/oracle，使用ksh.

    groupadd dba
    useradd -d /opt/oracle -m -s /bin/ksh -g dba oracle

9、    顯示當前登陸使用者、當前系統時間、當前操作目錄的命令

    whoami、who am i
    date
    pwd

10、    如何知道testuser使用者最近都做了哪些事情？

    tail -30 ~testuser/.sh_history

11、    如何得到當前系統的核心引數資訊，這樣的引數影響oracle的安裝。

    AIX：lsattr -El sys0
    HP-UX：kmtune | grep -i shm(after v11)
    Solaris：cat /etc/system | grep sem
    Tru64：sysconfig -q ipc, cat /etc/sysconfigtab | grep sem

12、    寫crontab，讓指令碼/opt/test.sh在每週日晚上8：00執行。

    00 20 * * 0 /opt/test.sh

13、    以下選擇其一做：

    寫一條指令，找尋當前目錄及其所有子目錄下的所有dbf字尾的檔案，並複製到/opt/oracle/oradata目錄下。
    find . -name "*.dbf" -exec cp {} /opt/oracle/oradata \;

    寫一條指令，刪除當前目錄下(不含子目錄)除了abc.txt以外的以.txt結尾的修改時間大於7天的檔案。
    find . -level 0 -name "*.txt” ! -name "abc.txt" -mtime +7 -exec rm {} \;

    寫一條指令，查詢當前目錄及其所有子目錄下含有“ORA-”或“warning”字元內容的所有帶log字尾的檔案。
    find . -name "*.log" -print | xargs egrep -i -e "ORA-|warning"

14、    寫一段shell，要求傳入兩引數，一個是ORACLE_SID，一個是大於100的數字值，如果輸入正確就列印出“Right parameter”，否則輸出"usage：$0 <ORACLE_SID> <NUMBER> (where value is > 100)"，且限制只有oracle使用者可以執行該指令碼。

    #!/bin/ksh
    if [ `whoami` != oracle ]
    then
        echo "Error：you must be oracle to execute."
        exit 99
    fi
    #check if null
    if [ -z "$1" -o -z "$2" ]
    then
        echo "usage：$0 <ORACLE_SID> <NUMBER> (where value is > 100)"
        exit 99
    fi
    tmp=`expr $2` #convert string to number,tmp = `expr $2` is error
    if [ $tmp -lt 101 ]
    then
        echo
        echo "Argument two is less then 100.\
    Aborting script."
        echo
        exit 99
    fi
    echo "Right parameter"

15、    寫一段shell，把test.txt中的資料插入表tab_test,每插入一條資料自動執行/opt/oracle/auto.sql。並將該shell提交到後臺執行，然後想辦法實時檢視當前shell執行日誌。
    資料庫資訊：
    ORACLE_SID=ORCL，可sqlplus testuser/test登陸
    test.txt檔案內容如下：
    123
    456
    789
    …
    tab_test表結構如下：
    create table tab_test (c1 number,c2 date default sysdate);

    #!/bin/ksh
    ORACLE_SID=ORCL;export ORACLE_SID
    # /etc/oratab (AIX & HP-UX) or /var/opt/oracle/oratab in Solaris.
    ORACLE_HOME=`cat /var/opt/oracle/oratab | grep ^$ORACLE_SID： | cut -f2 -d'：'`
    export ORACLE_HOME
    PATH=$ORACLE_HOME/bin：$PATH;export PATH
    for tmp in `cat test.txt`
    do
        $ORACLE_HOME/bin/sqlplus -S testuser/oss<<!
        insert into tab_test (c1) values ($tmp);
        @/opt/oracle/auto.sql
        commit;
        exit
        !
    done

    然後要用chmod 755 runsql.ksh來將此指令碼改成可執行。
    $ nohup ./runsql.ksh > runsql.log 2>&1 & (放入後臺執行)
    $ tail -f runsql.log或tail -f nohup.log (不斷的顯示輸出檔案中的新的行)

二、    oracle相關問題（未作特殊說明，基於unix下oracle9i作答）

1、    資料庫報警日誌檔案alertSID.log、初始化引數檔案和trace檔案分別存放在哪個目錄下？

    alertSID.log：background_dump_dest引數指定的目錄下，預設是在$ORACLE_BASE/admin/$ORACLE_SID/bdump下；
    init檔案：spfile引數指定的目錄下，預設是在$ORACLE_HOME/dbs；
    trace檔案：user_dump_dest引數指定的目錄下，預設是在$ORACLE_BASE/admin/$ORACLE_SID/udump下。

2、    如何最佳化系統？

    系統級別：statspack 15分鐘一次
    應用級別：10046 event + tkprof
    不要碰到資料庫就要最佳化，如果資料庫執行狀況不能滿足客戶需求時，就針對需求去最佳化！當然，資料庫健康檢查總是要做!

3、    寫sql。
    SQL> desc emp;
    Name                           Null?    Type
    ------------------------------ -------- -------------------------------
    EMPNO                          NOT NULL NUMBER(4)
    ENAME                                   VARCHAR2(10)
    JOB                                     VARCHAR2(9)
    MGR                                     NUMBER(4)
    HIREDATE                                DATE
    SAL                                     NUMBER(7,2)
    COMM                                    NUMBER(7,2)
    DEPTNO                                  NUMBER(2)

    1）    查出各部門薪水最高的三個員工的部門、姓名和薪水。
    break on deptno;
    select deptno,ename,sal
    from (
    select deptno,ename,sal,
    row_number() over (partition by deptno order by sal) as rank
        from emp
    )
    where rank <= 3 ;

    2）    檢視各部門的薪水合計，要求有總計資訊。
    break on deptno skip 1;
    select decode(grouping(deptno),1,'總計',to_char(deptno)) deptno,
    sum(sal) sum_sal
    from emp
    group by rollup(deptno);

    3）    檢視各部門和工作職位薪水合計，要求分別有部門和工作職位的總計資訊。
    select decode(grouping(deptno),1,'總計',to_char(deptno)) deptno,
    decode(grouping(job),1,'小計',job) job,
    sum(sal) sum_sal
    from emp
    group by cube(deptno,job)
    order by deptno,job nulls last;

    4）    檢視各部門和工作職位薪水合計，要求按部門有百分比資訊。
    break on deptno skip 1;
    compute sum of sum_sal on deptno;
    compute sum of ratio_pct on deptno;
    select deptno,job,sum(sal) sum_sal,
    ratio_to_report(sum(sal)) over (partition by deptno) ratio_pct
    from emp
    group by deptno,job;

4、    如何設定會話跟蹤？

    alter session set sql_trace=true
    或
    dbms_system.set_sql_trace_in_session(sid,serial,true)

5、    sqlplus一臺DB後，如何得到使用者當前的sid和對應的OS程式號？

    select distinct sid from v$mystat;
    select spid from v$process p,v$session s where p.addr=s.paddr and s.sid = :sid;

6、    如何得到"select * from dual"語句的執行計劃和統計資訊？

    第一種方法：（看執行計劃）
    explain plan for select * from dual;
    @?/rdbms/admin/utlxpls.sql

    第二鍾方法：
    set autotrace traceonly
    select * from dual
    附：set autotrace 選項
    on              顯示查詢結果，執行計劃，統計資料
    on statistics   顯示查詢結果，統計資料，不顯示執行計劃
    on explain      顯示查詢結果，執行計劃，不顯示統計資料
    traceonly       顯示執行計劃和統計結果，但不包括查詢結果
    traceonly statistics 僅顯示統計資料

    第三種方法：藉助toad等第三方工具
    (如果plan_table 表不存在，首先執行@?/rdbms/admin/utlxplan.sql建立該表)

7、    不借助第三方工具，如何得到表和儲存過程（包）的結構資訊？

    表：desc，dbms_metadata.get_ddl
    儲存過程（包）：desc

8、    寫儲存過程把當前schema下資料庫物件個數資訊按物件型別分組輸出來。並寫出在sqlplus得出輸出結果的執行過程。
    輸出結果類似：
    物件型別：xxx1，個數：yyy1
    物件型別：xxx2，個數：yyy2
    物件型別：xxx3，個數：yyy3

    create or replace procedure sp_test
    as
        p_object_type varchar2(30);
        p_object_count number(10);
        cursor cur is
            select object_type,count(*)
            from user_objects
            group by object_type;
    begin
        open cur;
        loop fetch cur into p_object_type, p_object_count;
        exit when cur%notfound;
            dbms_output.put_line('物件型別：'||to_char(p_object_type)||',個數：'||to_char(p_object_count));
        end loop;
        close cur;
    end;
    執行過程：
    set serveroutput on size 100000
    exec sp_test;

9、    一個oracle例項是由SGA和哪幾個後臺程式組成的？SGA有哪些組成部分及簡述它們的作用？PGA是什麼？

    後臺程式：系統監控和程式監控程式（SMON和PMON）、資料庫寫程式（DBWR）、日誌寫程式（LGWR）、歸檔程式（ARCH）、檢查點（CKPT）；
        其它可選程式：排程程式（Dnnn）、恢復程式（RECO）、快照程式（SNPn）、鎖程式（LCKn）、並行查詢程式（Pnnn）。
    資料寫程式(dbwr)：負責將更改的資料從資料庫緩衝區快取記憶體寫入資料檔案；
    日誌寫程式(lgwr)：將重做日誌緩衝區中的更改寫入線上重做日誌檔案；
    系統監控(smon) ：檢查資料庫的一致性如有必要還會在資料庫開啟時啟動資料庫的恢復；
    程式監控(pmon)：負責在一個Oracle 程式失敗時清理資源；
    檢查點程式(chpt)：負責在每當緩衝區快取記憶體中的更改永久地記錄在資料庫中時,更新控制檔案和資料檔案中的資料庫狀態資訊；
    歸檔程式(arcn) ：在每次日誌切換時把已滿的日誌組進行備份或歸檔；
    作業排程器(cjq)：負責將排程與執行系統中已定義好的job,完成一些預定義的工作；
    恢復程式(reco)：保證分散式事務的一致性,在分散式事務中,要麼同時commit,要麼同時rollback;
    SGA包含：db_cache、shared_pool、large_pool、java_pool
    db_cache：資料庫快取（Block Buffer）佔據Oracle資料庫SGA（系統共享記憶體區）的主要部分，Oracle資料庫透過使用LRU演算法，將最近訪問的資料塊存放到快取中，從而最佳化對磁碟資料的訪問；
    shared_pool：共享池中儲存資料字典高速緩衝和完全解析或編譯的的PL/SQL 塊和SQL 語句及控制結構；
    large_pool：使用MTS配置時，因為要在SGA中分配UGA來保持使用者的會話，就是用Large_pool來保持這個會話記憶體；使用RMAN做備份的時候，要使用Large_pool這個記憶體結構來做磁碟I/O快取器；
    java_pool：為java procedure預備的記憶體區域,如果沒有使用java proc,java_pool不是必須的。
    PGA：程式全域性區，是儲存有特定服務程式的資料和控制資訊的記憶體結構，這個記憶體結構是非共享的，只有服務程式本身才能夠訪問它自己的PGA區。每個服務 程式都有它自己的PGA區，各個服務程式PGA區的總和即庫例項的PGA區的大小。一般情況下，PGA區都由私有SQL工作區和會話記憶體區組成。

10、    你常用的資料字典檢視和動態效能檢視有哪些？

    dba_xxx，v$xxx系列

11、    陳述使用過的備份和恢復技術,及其優缺點。

    exp/imp
    rman

12、    簡單描述 table/segment/tablespace/datafile/extent/block 之間的關係。

    一個table至少是一個segment，如果分割槽表，則每個分割槽是一個segment，table可以看成是一個邏輯上的概念，segment可以看成是這個邏輯概念的物理實現；
    segment由一個或多個extents組成，segment不可以跨表空間但可以跨資料檔案；
    extent由多個連續的blocks組成，不可以跨資料檔案；
    block由1-多個os塊組成，是oracle i/o的最小儲存單位。
    tablespace是邏輯上的概念，datafile是物理上的概念；一個tablespace可以由多個datafile組成，一個datafile不能跨越多個tablespace。

13、    你關注的oracle初始化引數有哪些?

    show parameter

三、    其它

1、    使用任何一種你熟悉的指令碼語言，完成從DB中獲取表tab的資料並轉成文字的功能，要求各欄位以逗號分隔，每條記錄佔用一行。
    DB的例項資訊如下：sqlplus scott/tiger@(description=(address=(protocol=tcp)(host=192.168.1.4)(port=1521))(connect_data=(server=dedicated)(service_name=orcl)))

2、    某天現場反映BOSS系統非常之慢，根據經驗這很有可能是某些sql執行效率低下造成的，需要你去分析診斷，詳細陳述診斷流程。
    （包括寫下可能用到的OS命令、DB的資料字典試圖和動態效能檢視）

3、    選擇你負責或參與的電信專案的子系統，簡要介紹該子系統資料流程。

4、    Choose at least one of the topics from the following and write whatever you want.
    ? Business Intelligence, Data Warehouse, CRM
    ? TMF's eTOM/NGOSS, CMCC's NGBOSS, CU's NGBSS, CT's CTG-MBOSS
    ? latest web frameworks
    ? open source products that you have used before or are focusing on
    ? latest web2.0 sites.