點選(此處)摺疊或開啟

?
●
?
◆
※
☆
⊙
------GBK:
=E6=B5=B7=E6=BB=A8 (=E5=8F ---> LHR (=E5=8F
------3DUTF-8:
=E6=B5=B7=E6=BB=A8 (=E5=8F ---> =E5=B0=8F=E9=BA=A6=E8=8B=97 (=E5=8F
---小麥苗
3DUTF-8：=E5=B0=8F=E9=BA=A6=E8=8B=97
3DGBK： =D0=A1=C2=F3=C3=E7
----- editplus 替換空行： ^[ \t]*\n EDIT -> DELETE->DELETE BLANK LINES
----- editplus 替換以#開頭的行，多次執行： ^#[^\n]*\n
windows不支援的檔名：\ / : * ? " < > |
\ 反斜槓、捺斜槓
/ 正斜槓、撇斜槓
製表符 chr(9)
換行符 chr(10)
回車符 chr(13)
1s=1000ms(毫秒)=1000000(微秒)
-------------------------------常用日期
月份全拼簡拼示例
1 January Jan
2 February Feb
3 March Mar
4 April Apr
5 May May
6 June Jun
7 July Jul
8 August Aug
9 September Sep
10 October Oct Sat Aug 13 10:54:45 2016
11 November Nov Tue Nov 29 02:56:59 2016
12 December Dec Tue Dec 06 08:51:57 2016
星期全拼簡拼示例
1 Monday Mon Mon Dec 05 01:04:18 2016
2 Tuesday Tue Tue Dec 01 16:21:37 2016
3 Wednesday Web
4 Thursday Thu Thu Dec 01 08:36:03 2016
5 Friday Fri Fri Dec 02 16:17:17 2016
6 Saturday Sat Sat Dec 10 14:13:34 2016
7 Sunday Sun
---------------------------BBED
1.1 我的編譯程式碼
ls -l $ORACLE_HOME/rdbms/lib/*sbbd* */
ls -l $ORACLE_HOME/rdbms/mesg/bbed* */
chown oracle:dba /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/*sbbd* */
chown oracle:dba /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/bbed* */
--cd $ORACLE_HOME/rdbms/lib
--make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
--make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
1.2 我的使用程式碼
vi /home/oracle/file.txt
set line 9999 pagesize 9999
col name format a80
select file#||' '||name||' '||bytes name from v$datafile;
vi /home/oracle/bbed.par
blocksize=8192
listfile=/home/oracle/file.txt
mode=edit
bbed parfile=/home/oracle/bbed.par
bbed PASSWORD=blockedit mode=edit blocksize=8192 listfile=/home/oracle/file.txt
---------secureCRT中vi 顯示彩色
[root@rhel6_lhr ~]# vi .bashrc
alias vi='vim'
[root@rhel6_lhr ~]# vi /etc/profile
export TERM=xterm-color
secureCRT 中刪除用 shift+delete 鍵或 ctrl + backspace 鍵
ctrl+? 清理當前行命令
---------------------------- root 配置
-------------- AIX
chmod +w /etc/profile
echo "
umask 022
export ORACLE_HOME=/u01/app/11.2.0/grid
export PATH="\$PATH:\$ORACLE_HOME/bin"
export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '
set -o vi
alias l=\""ls -l\""
alias ll=\""ls -l\""
" >> /etc/profile
. /etc/profile
echo "
umask 022
export ORACLE_HOME=/u01/app/11.2.0/grid
export PATH="\$PATH:\$ORACLE_HOME/bin"
export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '
set -o vi
alias l=\""ls -l\""
alias ll=\""ls -l\""
" >> ~/.profile
. ~/.profile
-------------- Linux
chmod +w /etc/profile
echo "
umask 022
export ORACLE_HOME=/u01/app/11.2.0/grid
export PATH="\$PATH:\$ORACLE_HOME/bin"
export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '
export TERM=xterm-color
set -o vi
alias l=\""ls -l\""
alias ll=\""ls -l\""
" >> /etc/profile
. /etc/profile
echo "
umask 022
export ORACLE_HOME=/u01/app/11.2.0/grid
export PATH="\$PATH:\$ORACLE_HOME/bin"
export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '
set -o vi
alias l=\""ls -l\""
alias ll=\""ls -l\""
" >> ~/.bash_profile
. ~/.bash_profile
--export PS1="[\u@\h \W]\$ "
--export PS1='[$LOGNAME@'`hostname`:'$PWD'']# '
echo "export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '" > /etc/profile
export PS1='[$LOGNAME@'`hostname`:'$PWD'']# '
-------------------------------------------------------------------------------- linux
export HISTTIMEFORMAT='%F %T '
export HISTSIZE=100000
export HISTFILESIZE=100000
Ctrl+R 搜尋命令歷史，當找到命令後，通常再按Enter鍵就可以執行該命令。如果想對找到的命令進行調整後再執行，則可以按一下左或右方向鍵。
------------------------------------------------------------------------------------------------- cmd 命令
mstsc 遠端桌面
firewall.cpl 和 wf.msc 防火牆設定
services.msc 服務
cmd
msconfig 開啟啟動項
dxdiag Direct11
----開啟遠端桌面
1、計算機——右鍵——屬性——遠端設定——遠端，選中“允許允許任意版本遠端桌面的計算機連線”
2、關閉防火牆
netsh firewall set opmode disable
netsh advfirewall set publicprofile state off
3、services.msc 開啟服務，然後找到 Remote Desktop 相關的三個服務，確保狀態為“已啟動”
C:\Users\華榮>set /a a=8500*12
102000
C:\Users\華榮>
電腦設定豆沙綠：視窗、活動視窗標題2
色度，飽和度，亮度：85 123 205 80 100 200 100 120 200 76 91 205
RGB:207,232,204
cmd介面：0，128，128
程式碼背景：141，179，226
------- cmd 介面字型和介面背景顏色
背景顏色： 0 128 128
HKEY_CURRENT_USER\Console\%SystemRoot%_system32_cmd.exe 修改%SystemRoot%_system32_cmd.exe下，若無該項可以修改HKEY_CURRENT_USER\Console下，將DWORD型別的CodePage項修改為十進位制值936，將字串型別的FaceName改為Lucida Console ，若沒有該項則可以新建該項
chcp 437
cmd 介面按下F7顯示歷史命令
----cmd 下不換行
host set /p=start...資料庫巡檢服務概要. <nul
host set /p=. <nul
host echo ..end
----- 鎖屏 windows + L 鍵
----------系統環境變數
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment]
"NLS_DATE_FORMAT" "YYYY-MM-DD HH24:mi:ss"
"NLS_LANG AMERICAN_CHINA.ZHS16GBK
"ORACLE10G D:\Program files\app\oracle\product\10.2.0\db_1
"ORACLE11G D:\Program files\app\oracle\product\11.2.0.1\dbhome_1
"ORACLE8I D:\Program files\app\oracle\product\ora8i
"ORACLE9I D:\Program files\app\oracle\product\ora92
"ORACLE_HOME D:\Program files\app\oracle\product\11.2.0.1\dbhome_1
"TNS_ADMIN D:\Program files\app\oracle\product\11.2.0.1\dbhome_1\NETWORK\ADMIN
----------使用者環境變數
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Environment]
"NLS_DATE_FORMAT"="YYYY-MM-DD HH24:MI:SS"
"NLS_LANG"="AMERICAN_CHINA.ZHS16GBK"
---------cmd下設定使用者環境變數
SETX "NLS_DATE_FORMAT" "YYYY-MM-DD HH24:MI:SS"
SETX "NLS_LANG" "AMERICAN_CHINA.ZHS16GBK"
SETX "ORACLE_HOME" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1"
SETX "TNS_ADMIN" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1\NETWORK\ADMIN"
SETX "PATH" "%ORACLE_HOME%\bin"
SETX "VBOX_MSI_INSTALL_PATH" "D:\Program Files\Oracle\VirtualBox"
SETX "ORACLE_HOME" "%cd%\oracle\product\11.2.0.1\dbhome_1"
SETX "ORACLE_HOME" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1"
SETX "TNS_ADMIN" "%%ORACLE_HOME%%\network\admin"
SETX "PATH" "%path%;%%ORACLE_HOME%%\bin"
SETX "NLS_DATE_FORMAT" "YYYY-MM-DD HH24:MI:SS"
SETX "NLS_LANG" "AMERICAN_CHINA.ZHS16GBK"
alter session set nls_language='SIMPLIFIED CHINESE';
alter system set nls_language='AMERICAN' scope=spfile;
export EDITOR=vi
export ORACLE_SID=orclasm
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_ALERT=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_PATH=.:$ORACLE_BASE/dba_scripts/sql:$ORACLE_HOME/rdbms/admin
umask 022
#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" 　　　SELECT userenv('LANGUAGE') db_NLS_LANG FROM DUAL;
#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
export SQLPATH=$ORACLE_HOME/sqlplus/admin
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';
#alias sqlplus='rlwrap sqlplus'
#alias rman='rlwrap rman'
#alias asmcmd='rlwrap asmcmd'
alias alert_log='tail -200f $ORACLE_ALERT/alert_$ORACLE_SID.log'
alias alert_xml='tail -200f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/alert/log.xml'
alias alert_listener='tail -200f $ORACLE_BASE/diag/tnslsnr/rhel6/listener/trace/listener.log'
----- 如何啟用 Administrator（xp、windows7）
net user Administrator /active:yes
單擊“開始→執行”，輸入regedit後回車，開啟登錄檔編輯器，依次展開 "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList" 分支
將右邊的Administrator的值改為1，即可讓Administrator賬戶出現在登入的歡迎螢幕上。（如果沒有Administrator的話就建立一個DWORD(32位)值型別，其它上級專案沒有的話也依次建立）
----- 在PE中修改源系統的登錄檔
啟動登錄檔編輯器，點選HKEY_LOCAL_MACHINE，然後點【檔案】-【載入配置單元】，瀏覽檔案，進入 Win7安裝的分割槽 " c:\Windows\System32\config" 裡面的SYSTEM、DEFAULT、SOFTWARE都可以選擇，選擇後讓你輸入名稱，隨便輸入，如byiu輸入後點確定就會在HKEY_LOCAL_MACHINE下面增加那個分支，然後就可以編輯了。
---新建administrator
點開HKEY_LOCAL_MACHINE,點開SAM，右擊SAM，點選許可權..在組或使用者名稱稱下點選 Administrators,點選完全控制對應的允許核取方塊，點選確定
在登錄檔編輯器視窗按F5重新整理。點開："HKEY_LOCAL_MAICHINE\SAM\SAM\Domains\Account\Users\Names"
匯入如下注冊表，即Administrator使用者：
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SAM\SAM\Domains\Account\Users\Names\Administrator]
@=hex(1f4):
----------------禁用隨身碟啟用隨身碟
----- 啟用usb大容量儲存裝置預設設定為“3”表示手動，“2”是表示自動，“4”是表示停用，一般設定為3
--禁用
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\USBSTOR" /v Start /d 4 /t reg_dword /f
--啟用
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\USBSTOR" /v Start /d 3 /t reg_dword /f
---------------------- 檢視網站是否通
telnet 192.168.59.130 1158
---怎樣從DOS/Linux的telnet中退出
我們經常要用到telnet來測試FTP埠，但是有一個問題估計大家都會遇到，在telnet進入某個埠後無法退出，沒辦法，為了繼續測試只好關掉這個dos視窗，重新開啟一個。這裡教給你一個方法，按住ctrl+]就可以退出到
Welcome to Microsoft Telnet Client
Escape Character is 'CTRL+]'
Microsoft Telnet>
然後再輸入quit就可以完全退出了。
unzip /media/linux.x64_11gR2_/linux.x64_11gR2_database_1of2.zip -d /tmp && unzip /media/linux.x64_11gR2_/linux.x64_11gR2_database_2of2.zip -d /tmp > /dev/null 2>&1
unzip p10404530_112030_Linux-x86-64_1of7.zip -d /tmp && unzip p10404530_112030_Linux-x86-64_2of7.zip -d /tmp && unzip p10404530_112030_Linux-x86-64_3of7.zip -d /tmp > /dev/null 2>&1
mysql -u root -p
----------------------------- 科學計數法
12345678901，結果為 1.23E+10，即 1.23 乘以 10 的 10 次冪
12300000000
4.1E+11
410 000 000 000
----------------------------------------- crontab
每天0點02分 2 0 * * * /home/weblogic/lhr/ods_scripts/new_ods2/main_ods_entity_de_lhr.sh
每天凌晨1點3分 3 1 * * * /home/weblogic/bin/crm_address_intf_1.sh
每週日18點 0 18 * * 0 /home/weblogic/lhr/ods_scripts/new_ods2/main_weekly.sh
每週三18點 0 18 * * 3 /home/weblogic/bin/crm_inf_linkresource_monthly.sh
--root使用者可以檢視其它使用者的crontab
crontab -u zhangsan -l
crontab -u lisi -l
crontab -u wangwu -l
nohup sh rman_backup_full.sh 2>&1 &
MYDATE=`date +'%Y-%m-%d %H:%M:%S'`
sqlplus lhr/lhr@192.168.128.134:1521/orclasm.lhr.com
tnsping 192.168.0.123:1521/dev.us.oracle.com
drop user lhr cascade;
NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280"
NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
NLS_LANG=AMERICAN_AMERICA.UTF8
alias alert_log='tail -200f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
drop tablespace temp including contents and datafiles;
ALTER TABLESPACE temp DROP TEMPFILE '/u01/app/oracle/oradata/orcl/orclasm/tempfile/temp.264.850260283';
ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 20M;
--表空間大小：
set pagesize 9999 line 9999
col TS_Name format a30
WITH wt1 AS
(SELECT ts.TABLESPACE_NAME,
df.all_bytes,
decode(df.TYPE,
'D',
nvl(fs.FREESIZ, 0),
'T',
df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,
df.MAXSIZ,
ts.BLOCK_SIZE,
ts.LOGGING,
ts.FORCE_LOGGING,
ts.CONTENTS,
ts.EXTENT_MANAGEMENT,
ts.SEGMENT_SPACE_MANAGEMENT,
ts.RETENTION,
ts.DEF_TAB_COMPRESSION,
df.ts_df_count
FROM dba_tablespaces ts,
(SELECT 'D' TYPE,
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
FROM dba_data_files d
GROUP BY TABLESPACE_NAME
UNION ALL
SELECT 'T',
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))
FROM dba_temp_files d
GROUP BY TABLESPACE_NAME) df,
(SELECT TABLESPACE_NAME,
SUM(BYTES) FREESIZ
FROM dba_free_space
GROUP BY TABLESPACE_NAME
UNION ALL
SELECT tablespace_name,
SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
FROM gv$sort_usage a,
dba_tablespaces d
WHERE a.tablespace = d.tablespace_name
GROUP BY tablespace_name) fs
WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))
SELECT (SELECT A.TS#
FROM V$TABLESPACE A
WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,
t.TABLESPACE_NAME TS_Name,
round(t.all_bytes / 1024 / 1024) ts_size_M,
round(t.freesiz / 1024 / 1024) Free_Size_M,
round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per,
round(MAXSIZ / 1024 / 1024/1024, 3) MAX_Size_g,
round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 /
MAXSIZ,
3) USED_per_MAX,
round(t.BLOCK_SIZE) BLOCK_SIZE,
t.LOGGING,
t.ts_df_count
FROM wt1 t
UNION ALL
SELECT to_number('') TS#,
'ALL TS:' TS_Name,
round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M,
round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,
round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per,
round(SUM(MAXSIZ) / 1024 / 1024/1024) MAX_Size,
to_number('') "USED,% of MAX Size",
to_number('') BLOCK_SIZE,
'' LOGGING,
to_number('') ts_df_count
FROM wt1 t
order by TS#
;
WITH wt1 AS
(SELECT df.TABLESPACE_NAME,
df.all_bytes,
df.MAXSIZ,
nvl(fs.FREESIZ, 0) FREESIZ
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
FROM dba_data_files d
GROUP BY TABLESPACE_NAME) df,
(SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ
FROM dba_free_space
GROUP BY TABLESPACE_NAME) fs
WHERE fs.TABLESPACE_NAME(+) = df.TABLESPACE_NAME)
SELECT t.TABLESPACE_NAME TS_Name,
round(t.all_bytes / 1024 / 1024, 2) ts_size_m,
round(t.freesiz / 1024 / 1024, 2) Free_Size_m,
round((t.all_bytes - t.FREESIZ) / 1024 / 1024, 2) Used_Size_m,
round(MAXSIZ / 1024 / 1024 / 1024, 2) MAX_Size_g,
round((MAXSIZ - (t.all_bytes - t.FREESIZ)) / 1024 / 1024 / 1024, 2) MAX_Size_free_g,
(t.all_bytes) ts_size,
(t.freesiz) Free_Size,
(t.all_bytes - t.FREESIZ) Used_Size,
(MAXSIZ) MAX_Size,
((MAXSIZ - (t.all_bytes - t.FREESIZ))) MAX_Size_free
FROM wt1 t;
------ 資料檔案情況
SELECT d.FILE_ID,
d.TABLESPACE_NAME,
(SELECT round(SUM(nb.BYTES) / 1024 / 1024, 2)
FROM dba_data_files nb
WHERE nb.TABLESPACE_NAME = d.TABLESPACE_NAME) ts_size_m,
d.FILE_NAME,
round(d.BYTES / 1024 / 1024, 2) file_size_m,
round(d.MAXBYTES / 1024 / 1024 / 1024, 2) file_max_size_G,
d.AUTOEXTENSIBLE,
round(d.INCREMENT_BY * 8 * 1024 / 1024 / 1024, 2) INCREMENT_m,
round(d.BYTES * 100 / decode(d.MAXBYTES, 0, BYTES, d.MAXBYTES), 2) autoextend_ratio,
(SELECT b.CREATION_TIME
FROM sys.v_$datafile b
where b.FILE# = d.FILE_ID) CREATION_TIME,
d.INCREMENT_BY INCREMENT_BY_block,
d.BYTES,
d.blocks,
d.MAXBYTES,
d.MAXBLOCKS,
d.USER_BYTES,
d.USER_BLOCKS
FROM dba_data_files d
UNION ALL
SELECT d.FILE_ID,
d.TABLESPACE_NAME,
(SELECT round(SUM(nb.BYTES) / 1024 / 1024, 2)
FROM v$tempfile nb
WHERE nb.name = d.FILE_NAME) ts_size,
d.FILE_NAME,
round(d.BYTES / 1024 / 1024, 2) file_size_m,
round(d.MAXBYTES / 1024 / 1024 / 1024, 2) file_max_size_G,
d.AUTOEXTENSIBLE,
round(d.INCREMENT_BY * 8 * 1024 / 1024 / 1024, 2) INCREMENT_m,
round(d.BYTES * 100 / decode(d.MAXBYTES, 0, BYTES, d.MAXBYTES), 2) autoextend_ratio,
(SELECT b.CREATION_TIME
FROM sys.v_$datafile b
where b.FILE# = d.FILE_ID) CREATION_TIME,
d.INCREMENT_BY INCREMENT_BY_block,
d.BYTES,
d.blocks,
d.MAXBYTES,
d.MAXBLOCKS,
d.USER_BYTES,
d.USER_BLOCKS
FROM dba_temp_files d
ORDER BY TABLESPACE_NAME, file_id;
-----檢視字符集
select SYS_CONTEXT('USERENV', 'LANGUAGE') from dual;
select userenv('language') from dual;
select * from v$nls_parameters;
---密碼檔案 linux區分$ORACLE_SID大小寫 sysdba select* from v$pwfile_users;
--linux：orapw+$ORACLE_SID
--windows： pwd+$ORACLE_SID.ora
oradim -NEW -sid orcl9i -INTPWD admin -pfile d:\oracle\ora90\database\initstorm.ora;
C:\Users\Administrator> orapwd file="E:\oracle\ora8i\DATABASE\PWDortest.ORA" password=lhr
[oracle@robinson dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle force=y
sc delete OracleOraDb11g_home1TNSListener
sc create OracleOraDb11g_home1TNSListener binpath= "F:\app\oracle\product\BIN\TNSLSNR" start= auto displayname= "OracleOraDb11g_home1TNSListener"
--11g中密碼大小寫敏感
（1） sec_case_sensitive_logon引數可以指定使用者的口令是否區分大小寫，預設為true，表示區分大小寫
（2）口令檔案中的ignorecase 引數僅僅針對建立時是否區分大小寫，簡言之，只能針對SYS使用者，預設為N，表示不忽略大小寫，即區分大小寫
--透過設定EVENTS 28401可以遮蔽密碼延遲驗證：
SQL> ALTER SYSTEM SET EVENT = '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE；
設定該事件後重啟資料庫即可。
------------------------------------------------ selinux
檢視SELinux狀態：
1、/usr/sbin/sestatus -v ##如果SELinux status引數為enabled即為開啟狀態
SELinux status: enabled
2、getenforce ##也可以用這個命令檢查
關閉SELinux：
1、臨時關閉（不用重啟機器）：
setenforce 0 ##設定SELinux 成為permissive模式
##setenforce 1 設定SELinux 成為enforcing模式
2、修改配置檔案需要重啟機器：
修改/etc/selinux/config 檔案
將SELINUX=enforcing改為SELINUX=disabled
重啟機器即可
------------------------------------------------------------ linux
儲存區域網路（Storage Area Network，SAN）
----設定密碼永不過期：
chage -M -1 oracle
chage -l oracle
lsb_release-a
cat /etc/issue
uname -a
cat /proc/version
---linux位數檢視
file /bin/ls
getconf LONG_BIT
arch
---AIX系統檢視
　　顯示AIX系統核心是32位還是64位：
　　bootinfo -K
　　顯示機器硬體是32位還是64位：
　　bootinfo -y
　---　SUN：
　　$isainfo -bv
　　64表示核心是64位的，32表示核心是32位的
　---　HPUX:
　　>getconf KERNEL_BITS
　　64表示核心是64位的，32表示核心是32位的
　　HPUX:
　　>getconf KERNEL_BITS
　　64表示核心是64位的，32表示核心是32位的
------------------ AIX系統使用者解鎖
3.1 AIX使用者賬戶鎖定與解鎖最佳方法
3004-303 There have been too many unsuccessful login attempts; please see
the system administrator.
與之相關的配置引數是/etc/security/login.cfg的以下配置項
logindisable=7 *7次失敗登入後鎖定埠
logininterval=120 *在120秒內7次失敗登入才鎖定埠
1、如果你可以登陸到ROOT賬戶，比較簡單
使用chsec命令即可解鎖，具體如下：
# chsec -f /etc/security/lastlog -a unsuccessful_login_count=0 -s username
透過重置未成功登陸的次數即可解鎖
2、如果是透過設定來鎖定的使用者，可以這樣解鎖
#【smitty user】-->【Lock / Unlock a User's Account】
或是
# chsec -f /etc/security/lastlog -a unsuccessful_login_count=0 -s username
3、命令解鎖
# chuser account_locked=TRUE username 給使用者加鎖
# chuser accout_locked=FALSE username 給使用者解鎖
------------大寫G跳到最後一行，o新插入一行
AIX開啟自動補全：
方法一：
set -o vi
自動補全 esc \
歷史命令 esc -
HJKL
左下上右
A 跳到行末，進入編輯模式
I 跳到行首，進入編輯模式
X 鍵刪除游標前一個字元停留在原來的那個字元
AIX開啟自動補全：
方法一：
set -o vi
自動補全 esc \
歷史命令 esc -
esc j
esc k
i a x 編輯
游標移動： h l
HJKL
左下上右
A 跳到行末，進入編輯模式
I 跳到行首，進入編輯模式
X 鍵刪除游標前一個字元停留在原來的那個字元
方法二：
set -o emacs
自動補全按兩次esc
歷史命令 ctrl-n 或 ctrl-p
more /etc/profile
more /etc/environment
export TMOUT=0;
oslevel -qs
---清磁碟頭
dd if=/dev/zero of=/dev/rhdisk5 bs=1024 count=1024
##檢視PV大小,單位M AIX 查詢磁碟大小硬碟大小
for HDISK in `lspv | grep -v hdisk0 | awk '{print $1}'`;do
bootinfo -s $HDISK
done
for diskname in `lspv|grep -i none|cut -f "1" -d ' '`
do
echo "/dev/r$diskname" `getconf DISK_SIZE /dev/r$diskname`
done
---程式控制程式碼
lsof -p pid
---告警日誌位置
lsof | grep diag
lsof | grep bdump
show parameter background_dump_dest
$ORACLE_BASE/ADMIN/SID/BDUMP/ALERTSID.LOG
--根據實際情況決定是否加upper函式
SELECT VALUE || substr(d.VALUE, -6, 1) || 'alert_' || b.INSTANCE_Name ||'.log' alertname
FROM v$parameter d, v$instance b
WHERE d.NAME = 'background_dump_dest';
--1 埠是否佔用
netstat -apn | grep 1521
netstat -ano|grep 1521
netstat -lnp|grep 1521
----檢查包忽略大小寫
rpm -qa | grep -i AAA
--2 殺死所有程式
kill -9 `ps -ef|grep orcl| grep -v grep | awk '{print $2}'`
ps -ef |grep $ORACLE_SID|grep -v grep|awk '{print $2}' | xargs kill -9
ipcs -m | grep oracle | awk '{print $2}' | xargs ipcrm shm
--程式總數
ps -ef|grep orcl| wc -l
---統計行數
wc - lcw a.txt
　- c 統計位元組數。
　　- l 統計行數。
　　- w 統計字數。
-- 匹配多個程式號
[root@node1 node1]# ps -ef | grep ' 27373 \| 27182 '
oracle 27182 1 0 14:50 ? 00:00:00 ora_pz99_jmrac1
oracle 27373 1 0 14:53 ? 00:00:00 ora_w000_jmrac1
root 27574 9150 0 14:56 pts/1 00:00:00 grep 27373 \| 27182
[oracle@orcltest shm]$ ps -ef | egrep '(12545|12543)' |grep -v grep
oracle 12543 1 0 07:41 ? 00:00:02 ora_pmon_ogg1
oracle 12545 1 0 07:41 ? 00:00:04 ora_psp0_ogg1
[oracle@orcltest shm]$
-- 匹配多個字串
[root@node1 node1]# ps -ef | grep -v grep | grep -E "ohasd.bin|crs|ocssd|evmd|oproc"
root 2372 1 0 09:33 ? 00:00:25 /u01/grid/bin/crsd.bin reboot
root 5051 1 0 05:36 ? 00:01:02 /u01/grid/bin/ohasd.bin reboot
grid 5313 1 0 05:37 ? 00:01:45 /u01/grid/bin/ocssd.bin
grid 5500 1 0 05:37 ? 00:00:13 /u01/grid/bin/evmd.bin
[root@node1 node1]#
--3 關閉防火牆
chkconfig iptables off ---永久
service iptables stop ---臨時
chkconfig iptables --list
/etc/init.d/iptables status ----會得到一系列資訊，說明防火牆開著。
/etc/rc.d/init.d/iptables stop ----------關閉防火牆
setup ----------圖形介面
--將/etc/sysconfig/iptables檔案新增一行，表示允許1521埠訪問：
[root@dcsopen2Node sysconfig]# vi /etc/sysconfig/iptables
# Firewall configuration written by system-config-firewall
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
find / -type f -size +10000000c -exec du -sh {} \; 查詢大於10M的檔案
find . -name '*.phtml' -type f -mmin -30 查詢當前目錄下.phtml檔案中，最近30分鐘內修改過的檔案。
find . -name '*.phtml' -type f -mmin -30 -ls 查詢當前目錄下.phtml檔案中，最近30分鐘內修改過的檔案，的詳細情況。
find . -type f -mtime -1 查詢當前目錄下，最近1天內修改過的常規檔案
find . -type f -mtime +1 查詢當前目錄下，最近1天前（2天內）修改過的常規檔案。
find . -ctime +3 -exec rm -rf {} \; #刪除一個目錄下幾天前的檔案和目錄
find / -type f -size +10000000c -exec du -sh {} \; 2>/dev/null #查詢大於10M的檔案並列出檔案大小
find /home -size +10k #意思是說查詢/home目錄下大小為10k的檔案
find . -type f -mtime 0
find . -type f -mtime +1
find . -name '*.doc' -mtime 0
find / -name access_log 2>/dev/null
find . -name '*.doc' 2>/dev/null
find / -amin -10 # 查詢在系統中最後10分鐘訪問的檔案
find / -atime -2 # 查詢在系統中最後48小時訪問的檔案
find /tmp -size +10000000c -and -mtime +2
find /tmp -size +10000000c -or -mtime +2
find / -empty # 查詢在系統中為空的檔案或者資料夾
find / -group cat # 查詢在系統中屬於 groupcat的檔案
find / -mmin -5 # 查詢在系統中最後5分鐘裡修改過的檔案
find / -mtime -1 #查詢在系統中最後24小時裡修改過的檔案
find / -nouser #查詢在系統中屬於作廢使用者的檔案
find / -user fred #查詢在系統中屬於FRED這個使用者的檔案
find . -type f -mtime 0 -exec ls -lrt {} \; --檢視當天修改過的檔案
find . -type f -mtime 0 #最近24小時內修改過的檔案
find . -type f -mtime 1 #前48~24小時內修改過的檔案，而不是48小時以內修改過的檔案
---近3天內修改過的檔案
find . -type f -mtime 0 -o -mtime 1 -o -mtime 2
find . -type f -mtime 0 -or -mtime 1 -or -mtime 2
目錄大小： du -h --max-depth=1 . 2>&1
du -h --max-depth=0 /tmp/database/
du -sh database/
find . -ctime +3 -exec rm -rf {} \; 刪除一個目錄下幾天前的檔案和目錄
find / -type f -size +10000000c -exec du -sh {} \; 查詢大於10M的檔案
du -s /*|sort -rn 檢視目錄大小
/**/
-------- AIX資料夾大小
du -sg app/11.2.0/grid/* | sort -rn /* */
du -ag app/11.2.0/grid/* | sort -rn /* */
du -g /oracle/app/11.2.0/* | sort -rn | more /* */
系統啟動時間：
date -d "$(awk -F. '{print $1}' /proc/uptime) second ago" +"%Y-%m-%d %H:%M:%S"
create public database link dblink_ogg1
connect to lhr identified by lhr
using '(DESCRIPTION =
　　 (ADDRESS_LIST =
　　 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1521))
　　 )
　　 (CONNECT_DATA =
　　 (SERVICE_NAME = ogg1)
　　 )
　　 )';
create public database link DBLINK_OGG1
connect to LHR identified by lhr
using 'OGG1';
----linux檢視檔案的詳細時間
ls ctlfile*20150212* --full-time|awk '{print $6,$7,$9}'
-----------新增磁碟
fdisk -l
fdisk /dev/sdf
...
mkfs.ext4 /dev/sdf1
[root@rhel6_lhr ~]# mkdir /u03
[root@rhel6_lhr ~]# mount /dev/sdf1 /u03
[root@rhel6_lhr ~]# vi /etc/fstab
/dev/sdf1 /u03 ext4 defaults 0 0
chown oracle:oinstall /u04
linux 修改主機名，永久生效：
vim /etc/sysconfig/network
vim /etc/hosts
aix 修改主機名：
修改主機名暫時生效：
hostname NEW_HOSTNAME
永久生效 smit hostname
或者 smit tcpip - futher configureation - hostname -set the hostname
uname -S hostname
或者直接用命令 chdev -l inet0 -a hostname=NEW_HOSTNAME
||||||||||||||||||||
正確更改IP 地址是用 smit tcpip 進入選單之後，選擇further configuration 再選 Network Interfaces，再選 Network Interface Selection,
再選 Change /show characteristic of a network interface來更改 IP，這樣/etc/hosts就不會新加入一條記錄，只需更改檔案中相應的IP就行了。
-------------------------------------------------------------- 閃回恢復區滿
select * from v$flash_recovery_area_usage;
ALTER SYSTEM SET db_recovery_file_dest_size='2G';
--關閉閃回恢復區
alter system set db_recovery_file_dest='';
--EXPIRED可以理解為失效的備份集，即物理檔案丟失。OBSOLETE可以理解為過期的備份集。
CROSSCHECK ARCHIVELOG ALL;
LIST EXPIRED ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
--錯誤記錄
v_error := SQLCODE || ',' || SQLERRM || chr(13) ||dbms_utility.format_error_backtrace;
--DML行數
v_count :=TO_CHAR(SQL%ROWCOUNT);
-----------塊改變跟蹤
alter system set db_create_file_dest = '/u01/bct/' scope=both sid='*';
alter database enable block change tracking;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+FRA';
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/mydir/rman_change_track.f' REUSE;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
COL STATUS FORMAT A8
COL FILENAME FORMAT A60
SELECT STATUS, FILENAME,BYTES FROM V$BLOCK_CHANGE_TRACKING;
SELECT file#,
AVG(datafile_blocks),
AVG(blocks_read),
AVG(blocks_read / datafile_blocks) * 100 AS PCT_READ_FOR_BACKUP,
AVG(blocks)
FROM v$backup_datafile
WHERE used_change_tracking = 'YES'
AND incremental_level > 0
GROUP BY file#;
-------------------------------------------------------------- 聯機重做日誌
----------清除未歸檔日誌
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
----------清空損壞的日誌檔案成功執行前提：一致性關閉，解決ora-00392錯誤
ALTER DATABASE CLEAR LOGFILE GROUP 2;
----- 新增 redo
alter database add logfile group 4 'c:\oracle\oradata\orcl\redo04.log' size 100m;
alter database add logfile;
alter database add logfile [group n];
alter database add logfile member '<dir>' to group [n] /*add logfile member這個方法僅使用未使用OMF的日誌檔案，對於已經運用了OMF的日誌組，無法使用該功能新增日誌檔案*/
alter database add logfile thread 1 group 4 ('+DATA','+FRA') size 50M;
---rac庫可以在同一個例項下新增
alter database add logfile thread 1 group 7('+DATA_DG/querydb/redo07_1.log','+DATA_DG/querydb/redo07_2.log') size 1024m;
alter database add logfile thread 2 group 8('+DATA_DG/querydb/redo08_1.log','+DATA_DG/querydb/redo08_2.log') size 1024m;
--- 刪除
alter database drop logfile group 4;
alter database drop logfile member '';
--------重新命名redo
SQL> ho cp /u03/app/oracle/oradata/ora1024g/redo03.log /u03/app/oracle/oradata/ora1024g/redo04.log
SQL> alter database rename file '/u03/app/oracle/oradata/ora1024g/redo03.log' to '/u03/app/oracle/oradata/ora1024g/redo04.log';
--------重新命名表空間重新命名錶空間
alter tablespace users rename to users01;
----------------- 一個查詢慢的sql例子
select count(1) from dba_objects a
inner join user_objects b on 1=1
inner join user_objects c on 1=1
;
------------------------------------------------------------------------------------------------------------------------------------------------------------------- 構造大表
select level,level from dual connect by level<=1000;
-----------外部表
CREATE DIRECTORY EXT_LOG AS '/tmp';
DROP TABLE ALERT_LOG_lhr2;
CREATE TABLE ALERT_LOG_lhr2(
TEXT VARCHAR2(4000)
)ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_LOG
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE CHARACTERSET utf8
nobadfile
nodiscardfile
nologfile
FIELDS TERMINATED BY 0X'0D' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
)LOCATION('lhr1.txt')
) reject limit unlimited ;
----------檔案格式
df -hT
--------------ORA-00845: MEMORY_TARGET not supported on this system
辦法：修改/etc/fstab
tmpfs /dev/shm tmpfs defaults,size=1.5G 0 0
[root@FWDB ~]# mount -o remount /dev/shm
簡單來說就是 MEMORY_MAX_TARGET 的設定不能超過 /dev/shm 的大小：
[oracle@FWDB FWDB]$ df -h | grep shm
tmpfs 2.0G 0 2.0G 0% /dev/shm
馬上把它加大：
[root@FWDB ~]# cat /etc/fstab | grep tmpfs
tmpfs /dev/shm tmpfs defaults,size=4G 0 0
現在可以透過重啟使這個配置生效，也可以透過重新掛載來修改其大小：
[root@FWDB ~]# mount -o remount,size=4G /dev/shm
[root@FWDB ~]# df -h | grep shm
tmpfs 4.0G 0 4.0G 0% /dev/shm
再次啟動資料庫，沒有報錯了。
二、修改/dev/shm大小
預設的最大一半記憶體大小在某些場合可能不夠用，並且預設的inode數量很低一般都要調高些，這時可以用mount命令來管理它。
#mount -o size=1500M -o nr_inodes=1000000 -o noatime,nodiratime -o remount /dev/shm
在2G的機器上，將最大容量調到1.5G，並且inode數量調到1000000，這意味著大致可存入最多一百萬個小檔案。
如果需要永久修改/dev/shm的值，需要修改/etc/fstab
tmpfs /dev/shm tmpfs defaults,size=1.5G 0 0
mount -o remount /dev/shm
umount tmpfs
mount -t tmpfs shmfs -o size=3000m /dev/shm
vi /etc/fstab
---red hat重啟網路卡
service network restart
/etc/rc.d/init.d/network restart
---suse重啟網路卡
service network restart
rcnetwork restart
/etc/rc.d/init.d/network restart
----解除安裝網路卡
ifconfig eth0 down
--- 單獨重啟網路卡
ifdown eth0 && ifup eth0
ifconfig eth0 down && ifconfig eth0 up
----------------------------------------------- 固定IP 配置靜態ip地址
ifconfig eth1 192.168.210.108 broadcast 192.168.210.254 netmask 255.255.255.0
ifconfig eth1 192.168.59.130 broadcast 192.168.59.1 netmask 255.255.255.0
ifconfig eth0 192.168.59.130 netmask 255.255.255.0 gw 192.168.129.1
chkconfig NetworkManager off
chkconfig network on
service NetworkManager stop
service network start
---若還有問題，可刪掉網路卡重新新增
vi /etc/sysconfig/network-scripts/ifcfg-eth0
vi /etc/udev/rules.d/70-persistent-net.rules
DEVICE=eth0
IPADDR=192.168.59.130
NETMASK=255.255.255.0
NETWORK=192.168.59.0
BROADCAST=192.168.59.255
GATEWAY=192.168.59.2
ONBOOT=yes
USERCTL=no
BOOTPROTO=static
#HWADDR=00:0c:29:97:f1:5b
TYPE=Ethernet
IPV6INIT=no
DNS1=202.96.209.5
DNS2=8.8.8.8
NAME="System eth0"
----------------- 動態ip地址
DEVICE=eth0
ONBOOT=yes
USERCTL=no
BOOTPROTO=dhcp
HWADDR=00:0c:29:97:f1:5b
TYPE=Ethernet
PEERDNS=yes
IPV6INIT=no
[root@rhel6 ~]# export LANG=C
[root@rhel6 ~]# setup
[root@rhel6 ~]#
------------------------------------------------- 修改主機名
永久生效：
[root@zijuan /]# vim /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=yes
HOSTNAME=zijuan
HOSTNAME=zijuan表示主機設定為zijuan.
注意：修改主機名後，需要重啟系統後生效，或者切換個使用者然後切換回來就OK
檢視/etc/hosts檔案中必須包含a fully qualified name for the server
[root@localhost lhr]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.59.140 dg
[root@localhost lhr]# hostname
localhost.localdomain
[root@localhost lhr]# hostname dg
[root@localhost lhr]# hostname
dg
-----歸檔格式
alter system set log_archive_format = "log_oradg10g_%d_%t_%s_%r.arc" scope=spfile;
---------------------------------------- drop database 刪除資料庫
1、dbca靜默刪庫：dbca -silent -deleteDatabase -sourceDB mydb
2、SQL視窗：
alter database close;
alter system enable restricted session;
drop database;
3、SQL視窗：
sql > startup force mount restrict;
sql > drop database;
注意：強烈推薦第一種辦法，以上2和3的辦法若是rac庫需要設定cluster_database為false後才可以執行drop database，命令為：alter system set cluster_database=false sid='*' scope=spfile;
-------------------------------------- 配置本地yum源
-----------rhel 6.5
mkdir -p /media/lhr/cdrom
mount /dev/sr0 /media/lhr/cdrom/
#設定開機自動掛載系統映象檔案 vi /etc/fstab 新增以下內容
/dev/sr0 /media/cdrom iso9660 defaults,ro,loop 0 0
cd /etc/yum.repos.d/
cp rhel-media.repo rhel-media.repo.bk
vi /etc/yum.repos.d/rhel-media.repo
[rhel-media]
name=Red Hat Enterprise Linux 6.5
baseurl=file:///media/cdrom
enabled=1
gpgcheck=1
gpgkey=file:///media/cdrom/RPM-GPG-KEY-redhat-release
yum install httpd #安裝命令
yum install -y *sz*
rpm -ivh lrzsz-0.12.20-27.1.el6.x86_64.rpm
yum -y remove mysql-libs-5.1.71
yum list | grep mysql
rpm -e --nodeps mysql-libs.x86_64
-----------------命令後rpm包被下載到了什麼地方
每次在執行完yum命令後，系統都會把需要用到的rpm包放在/var/cache/yum/這個目錄下，但下載源的不同還是會放在不同源目錄下。
find /var/cache/yum/ -name kmod-oracleasm*
-------------- rhel5.5
# mkdir /media/cdrom
編輯 /etc/fstab 檔案，在檔案尾部新增如下內容，以便開機自動掛載光碟：
[root@localhost ~]# tail -1 /etc/fstab
/dev/cdrom /media/cdrom iso9660 defaults 0 0
[root@localhost ~]#
[root@localhost ~]# mount -a
mount: block device /dev/sr0 is write-protected, mounting read-only
[root@localhost ~]#
清空並編輯 YUM 源配置檔案
清空 /etc/yum.repos.d/rhel-debuginfo.repo 檔案並新增以下內容：
[root@localhost ~]# cp /etc/yum.repos.d/rhel-debuginfo.repo /etc/yum.repos.d/rhel-debuginfo.repo.bak
[root@localhost ~]# cat /etc/yum.repos.d/rhel-debuginfo.repo
[rhel-debuginfo]
name=Red Hat Enterprise Linux $releasever - $basearch - Debug
baseurl=file:///media/cdrom
enabled=0
gpgcheck=0
# vi /etc/yum.repos.d/my.repo
[Oracle]
name=OEL-$releasever – Media
baseurl=file:///mnt/Server
gpgcheck=0
enabled=1
如果是RHEL或者CentOS，請先將/etc/yum.repos.d下面的檔案刪除或者移動到別的目錄下，RHEL建立方法和OEL一樣，CentOS則baseurl=file:///mnt/ 即可，因為CentOS的repodata目錄就在光碟根下。
------檢視資源的屬性值：
[root@rac2 ~]# crsctl stat res ora.cluster_interconnect.haip -p -init | grep ENABLED
ENABLED=0
[root@rac2 ~]# crsctl stat res ora.asm -p -init | grep START_DEPENDENCIES
START_DEPENDENCIES=hard(ora.cssd,ora.ctssd)pullup(ora.cssd,ora.ctssd)weak(ora.drivers.acfs)
[root@rac2 ~]#
---修改資源的屬性值
crsctl modify resource ora.<diskgroup>.dg -attr AUTO_START=always
---啟動磁碟組
srvctl start diskgroup -g data -n "rac2"
------------- 新增rac資料庫到叢集
srvctl add database -d DGPHY -c RAC -o /oracle/app/oracle/product/11.2.0/db -p '+DATA/TESTDGPHY/PARAMETERFILE/spfiledgphy.ora' -r physical_standby -n TESTDG
srvctl add instance -d DGPHY -i DGPHY1 -n ZFZHLHRDB1
srvctl add instance -d DGPHY -i DGPHY2 -n ZFZHLHRDB2
srvctl status database -d DGPHY
srvctl start database -d TESTDG
srvctl remove database -d DGPHY
--新增監聽到crs 必須是grid新增
[ZFZHLHRDB3:grid]:/home/grid>srvctl add listener -l LISTENER_LHRDG -p 1523 -o $ORACLE_HOME
srvctl config listener -l LISTENER_LHRDG -a
①　rac新增LISTENER資源的時候需要使用grid使用者
②　srvctl config檢視資源的具體配置情況
③　srvctl modify可以修改資源的配置
--新增單例項
srvctl add database -d LHRDGPRI -c SINGLE -o /oracle/app/oracle/product/11.2.0/db -p '/oracle/app/oracle/product/11.2.0/db/dbs/spfileLHRDGPHY1.ora' -r physical_standby -n LHRDGPRI -x ZFZHLHRDB1 -i LHRDGPRI
crsctl status resource ora.asm -f
crsctl modify resource ora.asm -attr "GEN_USR_ORA_INST_NAME@SERVERNAME(zfxdeskdb1)=+ASM1"
crsctl modify resource ora.asm -attr "GEN_USR_ORA_INST_NAME@SERVERNAME(zfxdeskdb2)=+ASM2"
srvctl stop asm -f
-------------- 11g rac 修改歸檔 alter system set log_archive_dest_1='LOCATION=/arch/DGPHY' scope=spfile sid='*';
SQL>alter system set log_archive_dest_1='LOCATION=+FRA/GUITAR/ARC1' scope=spfile sid='guitar1';
Diskgroup altered.
SQL>alter system set log_archive_dest_1='LOCATION=+FRA/GUITAR/ARC2' scope=spfile sid='guitar2';
Diskgroup altered.
乾淨關閉資料庫然後啟動庫到mount，在其中一個例項上執行alter database archivelog 然後開啟資料庫即可。
[root@node1 ~]# srvctl stop database -d jmrac -o immediate
[root@node1 ~]# srvctl start database -d jmrac -o mount
------------------ mgmtdb
srvctl stop mgmtdb
srvctl status mgmtdb
srvctl config mgmtdb
srvctl disable mgmtdb
srvctl disable mgmtlsnr
srvctl add mgmtdb
srvctl config mgmtdb
srvctl disable mgmtdb
srvctl enable mgmtdb
srvctl getenv mgmtdb
srvctl modify mgmtdb
srvctl relocate mgmtdb
srvctl remove mgmtdb
srvctl setenv mgmtdb
srvctl start mgmtdb
srvctl status mgmtdb
srvctl stop mgmtdb
srvctl unsetenv mgmtdb
srvctl add mgmtlsnr
srvctl config mgmtlsnr
srvctl disable mgmtlsnr
srvctl enable mgmtlsnr
srvctl getenv mgmtlsnr
srvctl modify mgmtlsnr
srvctl remove mgmtlsnr
srvctl setenv mgmtlsnr
srvctl start mgmtlsnr
srvctl status mgmtlsnr
srvctl stop mgmtlsnr
srvctl unsetenv mgmtlsnr
[grid@raclhr-12cR1-N1 ~]$ export ORACLE_SID=-MGMTDB
[grid@raclhr-12cR1-N1 ~]$ sqlplus / as sysdba
Database unique name: _mgmtdb
Database name: _mgmtdb
Oracle user: grid
Database instance: -MGMTDB
service_names：_mgmtdb
------------------------------- export display
export DISPLAY=192.168.59.1:0.0
xhost +
在linux系統中用Oracle帳號執行DBCA或其他JAVA圖形介面程式時，報錯：
Xlib: connection to ":0.0" refused by server
Xlib: No protocol specified
Error: Can't open display: :0.0
解決辦法：用root登陸，在#提示符後輸入：
xhost local:oracle
-----------------------------iSCSI target
more /etc/ietd.conf --配置檔案
service iscsi-target start #啟動iSCSI target
cat /proc/net/iet/volume #檢視iSCSI-target共享出的硬碟
cat /proc/net/iet/session #檢視客戶端（initiator端）登陸到target的情況
-----------------------------iSCSI initiator
more /etc/iscsi/initiatorname.iscsi
more /etc/iscsi/iscsid.conf
iscsiadm -m discovery -t sendtargets -p 192.168.59.200:3260
iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.5e423e1e4d90 -p 192.168.59.200:3260 -l
iscsiadm --mode discovery --type sendtargets --portal 192.168.59.200
iscsiadm --mode node --targetname iqn.2006-01.com.openfiler:tsn.5e423e1e4d90 –portal 192.168.59.200:3260 --login
iscsiadm -m session -P 3
service iscsi start #啟動iSCSI initiator
/etc/init.d/iscsi start
service iscsi start
service iscsid start
chkconfig iscsi on
chkconfig iscsid on
chkconfig --level 2345 iscsi on
chkconfig --list|grep iscsi
-------------------------- 多路徑常用命令
rpm -qa|grep multipath
modprobe dm-multipath
modprobe dm-round-robin
lsmod |grep multipath
chkconfig --level 2345 multipathd on
chkconfig --list|grep multipathd
chkconfig --list multipathd
multipath -ll
---------------獲取wwid
--在RHEL 6中，可以透過如下方式獲取磁碟wwid：
for i in `cat /proc/partitions | awk {'print $4'} |grep sd`; do echo "### $i: `scsi_id --whitelist /dev/$i`"; done
-- 在RHEL 5中，可以透過如下方式獲取磁碟wwid：
for i in `cat /proc/partitions | awk {'print $4'} |grep sd`; do echo "### $i: `scsi_id -g -u -s /block/$i`"; done
-------------------------------------------------------------- ASM
---------------------------- oracleasm常用命令
/usr/sbin/oracleasm configure -i
/usr/sbin/oracleasm createdisk DISK1 /dev/sdb1
/etc/init.d/oracleasm scandisks
/etc/init.d/oracleasm listdisks
/etc/init.d/oracleasm enable
/usr/sbin/oracleasm enable
/usr/sbin/oracleasm restart
/usr/sbin/oracleasm createdisk DISKNAME devicename
/usr/sbin/oracleasm deletedisk DISKNAME
/usr/sbin/oracleasm querydisk {DISKNAME | devicename}
/usr/sbin/oracleasm listdisks
/usr/sbin/oracleasm scandisks
------ ASM磁碟
$ORACLE_HOME/bin/kfod disk=asm s=true ds=true c=true
/grid/stage/ext/bin/kfod disk=asm s=true ds=true c=true
create diskgroup DATA external redundancy disk '/dev/raw/raw*';
create diskgroup FRA external redundancy disk '/dev/rhdisk3'; --建立磁碟組FRA
CREATE DISKGROUP ACFSDG external redundancy DISK '/dev/oracleasm/disks/VOL1' ATTRIBUTE 'compatible.asm' = '11.2','compatible.rdbms' = '11.2','compatible.advm'='11.2';
create diskgroup OCR exteRnal redundancy disk 'ORCL:OVDISK' attribute 'compatible.asm'='11.2','compatible.rdbms'='11.2';
---修改磁碟組的相容屬性
ALTER DISKGROUP asm_dg SET ATTRIBUTE 'compatible.asm' = '11.1';
ALTER DISKGROUP asm_dg SET ATTRIBUTE 'compatible.rdbms' = '11.1';
COLUMN name FORMAT A10
COLUMN compatibility FORMAT A20
COLUMN database_compatibility FORMAT A20
SELECT group_number, name, compatibility, database_compatibility FROM v$asm_diskgroup;
set line 9999
set pagesize 9999
col path format a60
SELECT a.group_number, disk_number,mount_status, a.name, path FROM v$asm_disk a order by a.disk_number;
select instance_name,status from v$instance;
set line 999
select name,state,free_mb,required_mirror_free_mb,usable_file_mb,a.group_number, disk_number,mount_status, path from v$asm_diskgroup a;
select a.group_number,name,TYPE,state,TOTAL_MB,free_mb from v$asm_diskgroup a;
select name,state,free_mb,required_mirror_free_mb,usable_file_mb,a.group_number from v$asm_diskgroup a;
alter diskgroup DG1 mount;
---nomount狀態下強制刪除磁碟組
drop diskgroup oradg force including contents;
alter diskgroup DG1 drop disk DG1_VOL5; --刪除磁碟組DG1中的磁碟VOL5
alter system set asm_diskstring='/dev/asm-disk*','/dev/raw/raw*';
alter diskgroup DATA add disk '/dev/raw/raw1';
------------------------ faking asmdisk asm磁碟
---- 新增loop裝置個數
第一種辦法：修改 /etc/modprobe.conf 檔案新增引數：options loop max_loop=20 可以透過 modprobe -v loop 命令立即載入該模組，或重啟
第二種辦法(通用)：mknod -m 0660 /dev/loopX b 7 X
raw -qa
losetup -a
--mknod -m 0660 /dev/loopX b 7 X
mknod -m 0660 /dev/loop9 b 7 9
mkdir /asmdisk
dd if=/dev/zero of=/asmdisk/disk1 bs=1024k count=2000
dd if=/dev/zero of=/asmdisk/disk2 bs=1024k count=2000
dd if=/dev/zero of=/asmdisk/disk3 bs=1024k count=2000
dd if=/dev/zero of=/asmdisk/disk4 bs=1024k count=2000
dd if=/dev/zero of=/asmdisk/disk5 bs=1024k count=2000
/sbin/losetup /dev/loop1 /asmdisk/disk1
/sbin/losetup /dev/loop2 /asmdisk/disk2
/sbin/losetup /dev/loop3 /asmdisk/disk3
/sbin/losetup /dev/loop4 /asmdisk/disk4
/sbin/losetup /dev/loop5 /asmdisk/disk5
raw /dev/raw/raw1 /dev/loop1
raw /dev/raw/raw2 /dev/loop2
raw /dev/raw/raw3 /dev/loop3
raw /dev/raw/raw4 /dev/loop4
raw /dev/raw/raw5 /dev/loop5
chmod 660 /dev/raw/raw1
chmod 660 /dev/raw/raw2
chmod 660 /dev/raw/raw3
chmod 660 /dev/raw/raw4
chmod 660 /dev/raw/raw5
chown oracle:dba /dev/raw/raw1
chown oracle:dba /dev/raw/raw2
chown oracle:dba /dev/raw/raw3
chown oracle:dba /dev/raw/raw4
chown oracle:dba /dev/raw/raw5
------Add the following entries to the file "/etc/rc.local"
/sbin/losetup /dev/loop1 /asmdisk/disk1
/sbin/losetup /dev/loop2 /asmdisk/disk2
/sbin/losetup /dev/loop3 /asmdisk/disk3
/sbin/losetup /dev/loop4 /asmdisk/disk4
/sbin/losetup /dev/loop5 /asmdisk/disk5
raw /dev/raw/raw1 /dev/loop1
raw /dev/raw/raw2 /dev/loop2
raw /dev/raw/raw3 /dev/loop3
raw /dev/raw/raw4 /dev/loop4
raw /dev/raw/raw5 /dev/loop5
chmod 660 /dev/raw/raw1
chmod 660 /dev/raw/raw2
chmod 660 /dev/raw/raw3
chmod 660 /dev/raw/raw4
chmod 660 /dev/raw/raw5
chown oracle:dba /dev/raw/raw1
chown oracle:dba /dev/raw/raw2
chown oracle:dba /dev/raw/raw3
chown oracle:dba /dev/raw/raw4
chown oracle:dba /dev/raw/raw5
------------------------ Simulating Asm by faking hardware
-->Faking Hardware
-->Instaling ASM Lib
-->Configuring the disks
-->Install DB & ASM instance
---Faking Hardware: root 使用者
mkdir /asmdisk
dd if=/dev/zero of=/asmdisk/disk1 bs=1024k count=2000
dd if=/dev/zero of=/asmdisk/disk2 bs=1024k count=2000
/sbin/losetup /dev/loop1 /asmdisk/disk1
/sbin/losetup /dev/loop2 /asmdisk/disk2
raw /dev/raw/raw1 /dev/loop1
raw /dev/raw/raw2 /dev/loop2
chmod 660 /dev/raw/raw1
chmod 660 /dev/raw/raw2
chown grid:asmadmin /dev/raw/raw1
chown grid:asmadmin /dev/raw/raw2
------Add the following entries to the file "/etc/rc.local"
echo "/sbin/losetup /dev/loop1 /asmdisk/disk1" >>/etc/rc.local
echo "/sbin/losetup /dev/loop2 /asmdisk/disk2" >>/etc/rc.local
--------------------------------- oracle 日誌
oracleasm日誌：　tail -f /var/log/oracleasm
oracle agent日誌：　tail -f /u01/app/11.2.0/grid/log/rhel5/agent/ohasd/oraagent_grid/oraagent_grid.log
asm 告警日誌：alert_log='tail -200f $ORACLE_BASE/diag/asm/+asm/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
CRS 啟動日誌： more $ORACLE_HOME/log/$HOSTNAME/crsd/crsd.log
------查詢：find /u01/app/ -name crsd.log
crs日誌地址：/u01/app/11.2.0/grid/log/rac2/crsd/crsd.log
oracle 10g 告警日誌： /u02/app/oracle/admin/ora10g/bdump
oracle 11g 告警日誌： select value from v$diag_info where name='Default Trace File';
System Control Statement 系統控制語句 alter system
------------------------------------------------------------------------------------------- 恢復到new host
set pagesize 200 linesize 200
select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'
from v$datafile a
union all
select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'
from v$tempfile a
union all
SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' ||
a.MEMBER || ''''' ";'
FROM v$logfile a;
RUN
{
# allocate a channel to the tape device
# ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS '...';
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
# rename the data files and online redo logs
SET NEWNAME FOR DATAFILE 1 TO '?/oradata/test/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '?/oradata/test/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '?/oradata/test/cwmlite01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '?/oradata/test/drsys01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '?/oradata/test/example01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '?/oradata/test/indx01.dbf';
SET NEWNAME FOR DATAFILE 7 TO '?/oradata/test/tools01.dbf';
SET NEWNAME FOR DATAFILE 8 TO '?/oradata/test/users01.dbf';
SET NEWNAME FOR TEMPFILE 1 TO '?/oradata/test/temp01.dbf';
SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo01.log''
TO ''?/oradata/test/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo02.log''
TO ''?/oradata/test/redo02.log'' ";
# Do a SET UNTIL to prevent recovery of the online logs
SET UNTIL SCN 123456;
# restore the database and switch the data file names
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
# recover the database
RECOVER DATABASE;
}
------------------------------------------------- 資料庫未掛掉的情況下的恢復
[root@orcltest ~]# ps -ef|grep ora_lgwr_
.oracle 32173 1 0 06:31 ? 00:00:00 ora_lgwr_oratest
root 33247 32901 0 10:19 pts/0 00:00:00 grep ora_lgwr_
[root@orcltest ~]# cd /proc/32173/fd
[root@orcltest fd]# ll | grep deleted
lrwx------ 1 root root 64 May 5 15:10 266 -> /u02/app/oracle/oradata/oratest/temp01.dbf （deleted）
cp 266 /u02/app/oracle/oradata/oratest/temp01.dbf
------------------------------------------------------------------------------------------- asm <=> os
----所有檔案列表資料檔案
set line 9999 pagesize 9999
col FILE_NAME format a60
select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile
union all
select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile
union all
select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile
union all
select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile
;
set line 9999 pagesize 9999
col FILE_NAME format a50
select file#,name FILE_NAME,status,enabled from v$datafile;
--select file#,name FILE_NAME from v$dbfile;
col FILE_NAME format a50
select FILE_NAME,FILE_ID,a.TABLESPACE_NAME, b.status ts_status ,BYTES from dba_data_files a,dba_tablespaces b where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
-------------------------------------------os--->>asm
-- answer 1 set newname 好
run{
crosscheck backup;
sql 'alter tablespace testdg offline immediate';
set newname for datafile 14 to'+DATA';
restore tablespace testdg;
switch datafile 14;
recover tablespace testdg;
sql 'alter tablespace testdg online';
}
-- answer 2 convert 好
rman下：
convert datafile '/home/oracle/testdg.dbf' format '+DATA';
sql 下：
alter tablespace testdg offline ;
alter tablespace testdg rename datafile '/home/oracle/testdg.dbf' to '+DATA/orclasm/datafile/testdg.284.868895277';
recover datafile 14;
alter tablespace testdg online;
-- answer 3 dbms_file_transfer
create directory asmsrc as'+DATA/orclasm/datafile/';
create directory osdesc as '/home/oracle/';
alter tablespace testdg offline;
exec dbms_file_transfer.copy_file('osdesc','testdg.dbf','ASMSRC','testdg.dbf');
alter database rename file'/home/oracle/testdg.dbf' to '+DATA/orclasm/datafile/testdg.dbf';
alter tablespace testdg online ;
-- answer 4 backup as copy
run{
shutdown immediate;
startup mount;
backup as copy datafile 14 format '+DATA';
}
SWITCH TABLESPACE testdg TO COPY;
alter database open;
-- answer 5 cp
alter tablespace testdg offline;
[root@rhel6_lhr ~]# cp /home/oracle/testdg.dbf /home/grid/testdg.dbf
[root@rhel6_lhr ~]# chown grid:oinstall /home/grid/testdg.dbf
[root@rhel6_lhr ~]# su - grid
ASMCMD> cp /home/grid/testdg.dbf +DATA/orclasm/datafile/testdg.dbf
copying /home/grid/testdg.dbf -> +DATA/orclasm/datafile/testdg.dbf
ASMCMD>
alter database rename file'/home/oracle/testdg.dbf' to '+DATA/orclasm/datafile/testdg.dbf';
alter tablespace testdg online ;
---生成standby controlfile
rman：backup device type disk format '/arch/standby_new_lhr_%U.ctl' current controlfile for standby;
sql：alter database create standby controlfile as '/arch/standby_new_lhr_contol.ctl'
--------------------控制檔案轉換
RMAN> catalog controlfilecopy '/home/oracle/rman_back/ctl_orastrac.ctl_bk';
cataloged control file copy
control file copy file name=/home/oracle/rman_back/ctl_orastrac.ctl_bk RECID=7 STAMP=881248289
RMAN> backup as copy controlfilecopy '/home/oracle/rman_back/ctl_orastrac.ctl_bk' format '+DATA';
Starting backup at 01-JUN-2015 15:11:44
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input control file copy name=/home/oracle/rman_back/ctl_orastrac.ctl_bk
output file name=+DATA/orastrac/controlfile/backup.331.881248305 tag=TAG20150601T111610 RECID=8 STAMP=881248307
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 01-JUN-2015 15:11:51
RMAN-06497: WARNING: control file is not current, control file AUTOBACKUP skipped
RMAN> restore controlfile to '+DATA' FROM '+DATA/orastrac/controlfile/backup.331.881248305';
Starting restore at 01-JUN-2015 15:12:49
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
Finished restore at 01-JUN-2015 15:12:50
------------------------------------------------ asm --->> os
-- answer 1 好
rman下：
convert datafile '+DATA/orclasm/datafile/testdg.277.868887219' format '/home/oracle/testdg.dbf';
sql 下：
alter tablespace testdg offline ;
alter tablespace testdg rename datafile '+DATA/orclasm/datafile/testdg.277.868887219' to '/home/oracle/testdg.dbf';
recover datafile 14;
alter tablespace testdg online;
-- answer 2 dbms_file_transfer
create directory asmsrc as'+DATA/orclasm/datafile/';
create directory osdesc as '/home/oracle/';
alter tablespace testdg offline;
exec dbms_file_transfer.copy_file('ASMSRC','testdg.282.868891371','osdesc','testdg.dbf');
alter database rename file'+DATA/orclasm/datafile/testdg.282.868891371' to '/home/oracle/testdg.dbf';
alter tablespace testdg online ;
-- answer 3 mount
run{
shutdown immediate;
startup mount;
set newname for datafile 14 to '/home/oracle/testdg.dbf';
restore datafile 14;
switch datafile 14;
recover datafile 14;
alter database open;
}
-- answer 4 mount
run{
shutdown immediate;
startup mount;
backup as copy datafile 14 format '/home/oracle/testdg.dbf';
}
switch tablespace testdg to copy;
alter database open;
-- answer 5 cp
alter tablespace testdg offline;
[root@rhel6_lhr ~]# su - grid
ASMCMD> cp +DATA/orclasm/datafile/testdg.dbf /home/grid/testdg.dbf
copying +DATA/orclasm/datafile/testdg.dbf -> /home/grid/testdg.dbf
ASMCMD>
[root@rhel6_lhr ~]# cp /home/grid/testdg.dbf /home/oracle/testdg.dbf
[root@rhel6_lhr ~]# chown oracle:oinstall /home/oracle/testdg.dbf
[root@rhel6_lhr ~]#
alter database rename file'+DATA/orclasm/datafile/testdg.dbf' to '/home/oracle/testdg.dbf';
alter tablespace testdg online ;
create bigfile tablespace ts_dbm datafile '/home/oracle/ts_dbm01.dbf' size 10m autoextend on next 10M ;
alter user xxx default tablespace bbb;
------ smallfile tablespaces
單個資料檔案的大小由資料庫block_size的尺寸決定，例如：
block_size =8K 對應單個資料檔案最大為 32G
block_size =16K 對應單個據檔案最大為 64G
block_size =32K 對應單個據檔案最大為 128G
------Bigfile Tablespaces
block_size =8K 對應單個資料檔案最大為 32T
block_size =16K 對應單個據檔案最大為 64T
block_size =32K 對應單個據檔案最大為 128T
----如果刪除表空間之前刪除了表空間檔案，解決辦法:
如果資料庫已經啟動，則需要先執行下面這行：
SQL> shutdown abort
SQL> startup mount
SQL> alter database datafile 'filename' offline drop;
SQL> alter database open;
SQL> drop tablespace tablespace_name including contents;
alter database datafile '/u02/oracle/oradata/user01.dbf' offline drop;
alter tablespace test drop datafile '+DATA/orclasm/datafile/test.274.907173619';
------表空間預設型別
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%TBS%';
SQL> alter database set default bigfile tablespace;
--改回為預設值
SQL> alter database set default smallfile tablespace;
---控制檔案
--alter system set control_files='/u01/app/oracle/oradata/control01.ctl', '/u01/app/oracle/oradata/control02.ctl','/u01/app/oracle/oradata/control03.ctl' scope=spfile;
alter database backup controlfile to trace as '/home/oracle/oracle_bk/coolbak/ctl.sql';
select * from v$controlfile_record_section;
--轉儲控制檔案
alter system set events 'immediate trace name controlf level 12';
---檔案路徑
SELECT d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
p.spid || '.trc' trace_file_name
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = '1'
AND s.sid = m.sid
AND p.addr = s.paddr) p,
(SELECT t.instance
FROM v$thread t, v$parameter v
WHERE v.name = 'thread'
AND (v.value = '0' OR to_char(t.thread#) = v.VALUE)) i,
(SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
oradebug setmypid
SELECT a.SID,
b.SERIAL# ,
c.SPID ospid,
c.pid orapid
FROM v$mystat a,
v$session b ,
v$process c
WHERE a.SID = b.SID
and b.PADDR=c.ADDR
AND rownum = 1;
oradebug dump controlf 12;
16:09:17 SQL> oradebug setmypid
已處理的語句
16:09:55 SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_21437.trc
oradebug event 1555 trace name errorstack level 3
熱備:
alter database backup controlfile to '<dir>'; --熱備份控制檔案 alter database backup controlfile to '/home/oracle/ora_bk/control.bk';
alter database backup controlfile to trace as '<dir>' ;--得到建立控制檔案的指令碼
RMAN:
backup current controlfile format '/home/oracle/oracle_bk/orclasm/ctl_%d_%T_%s_%p.bak';
backup database include current controlfile;
-- 或者設定RMAN 為自動備份
RMAN > configure controlfile autobackup on;
----預設false 忽略一致性檢察隱含引數隱藏
SELECT * FROM gv$parameter a WHERE a.NAME like '\_%' escape '\' ;
SELECT * FROM gv$parameter a WHERE a.NAME like '=_%' escape '=' ;
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
alter system set "_allow_resetlogs_corruption"=false scope=spfile; --預設
alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';
SQL> show parameter _allow_resetlogs_corruption
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean TRUE
set pagesize 9999
set line 9999
col NAME format a40
col KSPPDESC format a50
col KSPPSTVL format a20
SELECT a.INDX,
a.KSPPINM NAME,
a.KSPPDESC,
b.KSPPSTVL
FROM x$ksppi a,
x$ksppcv b
WHERE a.INDX = b.INDX
and lower(a.KSPPINM) like lower('%&parameter%');
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
recover database using backup controlfile until cancel;
alter database open resetlogs;
startup force
alter database open resetlogs;
alter system set "_allow_resetlogs_corruption"=false scope=spfile;
alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';
----------------incarnation
RMAN> list incarnation of database;
RMAN> reset database to incarnation 8;
SELECT * FROM V$DATABASE_INCARNATION;
alter system set log_archive_dest_1='LOCATION=/home/oracle' scope=spfile;
---------- 重建控制檔案
CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS DATAFILE '/u01/app/oracle/oradata/orcltest/system01.dbf';
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/app/oracle/oradata/orcltest/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u02/app/oracle/oradata/orcltest/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u02/app/oracle/oradata/orcltest/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u02/app/oracle/oradata/orcltest/system01.dbf',
'/u02/app/oracle/oradata/orcltest/sysaux01.dbf',
'/u02/app/oracle/oradata/orcltest/undotbs01.dbf',
'/u02/app/oracle/oradata/orcltest/users01.dbf',
'/u02/app/oracle/oradata/orcltest/example01.dbf'
CHARACTER SET ZHS16GBK
;
select THREAD#, SEQUENCE#,FIRST_TIME from v$archived_log d where (( THREAD#=2 and SEQUENCE# between 10050 and 10060) or ( THREAD#=1 and SEQUENCE# between 9720 and 9725)) and d.DELETED!='YES' ORDER BY THREAD#, D.RECID;
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
startup force mount;
sql 'alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:mi:ss"';
set until time = "to_date('2016-07-28 21:04:50','YYYY-MM-DD HH24:mi:ss')";
restore database;
recover database;
release channel c1;
release channel c2;
}
catalog start with '/u03/backup/' noprompt;
--restore Controlfile
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
--restore datafile
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/u02/app/oracle/oradata/orcltest/system01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/u02/app/oracle/oradata/orcltest/sysaux01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/u02/app/oracle/oradata/orcltest/undotbs01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/u02/app/oracle/oradata/orcltest/users01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'/u02/app/oracle/oradata/orcltest/example01.dbf');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
注意：
在multisection backup 的情況下，我們需要考慮所有的backuppiece（也就是所有的section）,使用initmsr函式來restore datafile
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
dbms_backup_restore.RestoreSetDatafile;
dbms_backup_restore.initmsr(1,'/ud1001/PROD/oradata/system01-test.dbf');
dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/ud1001/PROD/oradata/system01-test.dbf');
dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/backup/Oracle-DB-8-7-2014/db_L0_PROD_e6pf7too_1_1.rman', params => null);
dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/backup/Oracle-DB-8-7-2014/db_L0_PROD_e6pf7too_2_1.rman', params => null);
END;
/
--restore archived redolog
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN');
dbms_backup_restore.RestoreSetArchivedLog(destination=>'D:\ORACLE_BASE\achive\');
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>1);
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>2);
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>3);
dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_LOG_BCK0DH1JGND_1_1', params => null);
dbms_backup_restore.DeviceDeallocate;
END;
/
--清除控制檔案中關於v$archived_log的資訊
SQL> execute sys.dbms_backup_restore.resetCfileSection(11);
--再次查詢v$archived_log，資訊已經被清除
SQL> select dest_id,sequence#,name,blocks from v$archived_log;
---------------------------------------------------------------------------------------------------------------- 歸檔丟失
SQL> recover database ;
ORA-00279: change 1549336 generated at 01/15/2015 16:22:07 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_22_865253186.dbf
ORA-00280: change 1549336 for thread 1 is in sequence #22
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/utf8test/system01.dbf'
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 1102344192 bytes
Fixed Size 2227584 bytes
Variable Size 738198144 bytes
Database Buffers 352321536 bytes
Redo Buffers 9596928 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/utf8test/system01.dbf'
---- recover database using backup controlfile;
SQL> recover database until cancel;
ORA-00279: change 1549336 generated at 01/15/2015 16:22:07 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_22_865253186.dbf
ORA-00280: change 1549336 for thread 1 is in sequence #22
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/u01/app/oracle/oradata/utf8test/sysaux01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [1549349], [0],
[1550178], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [1549348], [0],
[1550178], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [1549346], [0],
[1550178], [12583040], [], [], [], [], [], []
Process ID: 7693
Session ID: 237 Serial number: 5
退出，重新登入
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rhel6_lhr utf8test]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 16 10:13:53 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1102344192 bytes
Fixed Size 2227584 bytes
Variable Size 738198144 bytes
Database Buffers 352321536 bytes
Redo Buffers 9596928 bytes
Database mounted.
Database opened.
----------------------------------------------------------------------------------------------------------------
select * from v$fixed_view_definition a WHERE a.VIEW_NAME like 'X_$DIAG%' ;
SELECT * FROM V$FIXED_TABLE A WHERE A.NAME like 'X$DIAG%' ;
------- 十進位制轉十六進位制
select to_char(1985432,'xxxxxxxxxxxxxxx') FROM DUAL;
------- 十六進位制轉十進位制
select to_number('1e4b98','xxxxxxxxxxxxxxx') from dual;
--------------------------- exp和imp grant exp_full_database to lhr;
--EXP-00091的方法 select userenv('language') from dual; ---->>> NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
或加上： STATISTICS=NONE
------ query選項
exp \'/ AS SYSDBA\' tables=test_query_lhr file=/tmp/test_query_lhr_scott.dmp query=\" where owner=\'SCOTT\' \" log=/tmp/test_query_lhr_scott.log
[ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par
query="where owner='SCOTT'"
[ZFZHLHRDB1:oracle]:/oracle> exp \'/ AS SYSDBA\' tables=test_query_lhr file=/tmp/test_query_lhr_scott_01.dmp parfile=/tmp/scottfile.par log=/tmp/test_query_lhr_scott_01.log
------ parfile選項
[ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par
tables=scott.emp,scott.dept
exp \'/ AS SYSDBA\' file=/tmp/test_query_lhr_scott_01.dmp parfile=/tmp/scottfile.par log=/tmp/test_query_lhr_scott_01.log
strace exp n1/n1 tables=scott.emp file=a.dmp
exp cnydm/cnydm@DATAWDB_125 file=d:/oracle_bk/cnydm20150402.dmp log=d:/oracle_bk/cnydm20150402.log buffer=50000000 tables=PRD_CTGRY_D,DSCNT_TP_D,MKT_AND_PRD_CTGRY_D,MKT_CTGRY_D
imp cnydm/cnydm@DATAWDB_125 file=d:/oracle_bk/cnydm20150402.dmp log=d:/oracle_bk/imp_cnydm20150402.log buffer=50000000 full=y
exp system/lhr file=E:\expfull.dmp full=y log=E:\expfull.log
imp system/lhr file=E:\expfull.dmp full=y log=E:\impfull.log
exp lhr/lhr@orclasm tables=xb_log_lhr,xb_a,xb_b file=e:\e1.dmp log=E:\exp_table.log buffer=41943040
imp lhr/lhr@winxp tables=xb_log_lhr file=e:\e1.dmp log=E:\exp_table.log buffer=41943040
imp lhr/lhr@orclasm tables=(emp,dept) file=d:\e1.dmp log=E:\exp_table_.log buffer=41943040
exp system/lhr file=E:\expfull2.dmp log=E:\expfull2.log owner=(lhrexp,lhrimp)
imp system/lhr file=E:\expfull2.dmp full=y log=E:\expfull2.log
imp "sys as sysdba" file=testmv_full.dmp full=y buffer=41943040 feedback=10000 log=testmv_full.log
imp user2/pwd fromuser=user1 touser=user2 file=/tmp/imp_db_pipe1 feedback=10000 buffer=41943040 log=testmv_full.log
生產環境下，oracle 9I下sga大概8G，pga大概6g，需要匯入一個2.7g以上的大表到成產庫中，由於是同事著手運用imp工具的預設buffer=30K，用時大概一個小時還沒有結果，考慮到pga還是很大的，跟同事商議加上buffer=409600000設定buffer大概400M的，15分鐘內imp完成。當然運用impdp然後運用parallel=n效率當然更加理想了！
-------------------------------------------- 匯出ASH檢視的資料 ash資料
--- 方法1：ctas建表匯出有的客戶不讓建表
CREATE TABLE ASH_TEMP_20161117 NOLOGGING AS
SELECT *
FROM DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE D.SAMPLE_TIME BETWEEN
TO_DATE('2016-11-10 02:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_DATE('2016-11-17 06:00:00', 'YYYY-MM-DD HH24:MI:SS')
;
exp \'/ AS SYSDBA\' tables=ASH_TEMP_20161117 file=/tmp/ASH_TEMP_20161117.dmp log=/tmp/ASH_TEMP_20161117.log buffer=41943040
imp lhr/lhr tables=ASH_TEMP_20161117 file=/tmp/ASH_TEMP_20161117.dmp log=/tmp/imp_ASH_TEMP_20161117.log buffer=41943040
--- 方法2：匯出基表的資料
---more /tmp/exp_ash_lhr_01.par
query="WHERE SAMPLE_TIME BETWEEN TO_DATE('2016-12-02 08:30:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2016-12-08 23:38:00', 'YYYY-MM-DD HH24:MI:SS')"
exp \'/ AS SYSDBA\' tables='WRH$_ACTIVE_SESSION_HISTORY' file=/tmp/exp_ash_lhr_01.dmp parfile=/tmp/exp_ash_lhr_01.par log=/tmp/exp_ash_lhr_01.log GRANTS=N CONSTRAINTS=N STATISTICS=NONE
exp \'/ AS SYSDBA\' tables='WRM$_SNAPSHOT','WRH$_EVENT_NAME','WRH$_SQLCOMMAND_NAME','WRH$_PLAN_OPERATION_NAME','WRH$_PLAN_OPTION_NAME','WRH$_TOPLEVELCALL_NAME' file=/tmp/exp_ash_lhr_02.dmp log=/tmp/exp_ash_lhr_02.log GRANTS=N CONSTRAINTS=N STATISTICS=NONE
imp lhr/lhr file=/tmp/exp_ash_lhr_01.dmp tables='WRH$_ACTIVE_SESSION_HISTORY' log=/tmp/imp_ash_lhr_01.log FROMUSER=SYS TOUSER=LHR
imp lhr/lhr file=/tmp/exp_ash_lhr_02.dmp tables='WRM$_SNAPSHOT','WRH$_EVENT_NAME','WRH$_SQLCOMMAND_NAME','WRH$_PLAN_OPERATION_NAME','WRH$_PLAN_OPTION_NAME','WRH$_TOPLEVELCALL_NAME' log=/tmp/imp_ash_lhr_02.log FROMUSER=SYS TOUSER=LHR
DROP TABLE LHR.WRH$_ACTIVE_SESSION_HISTORY PURGE;
DROP TABLE LHR.WRM$_SNAPSHOT PURGE;
DROP TABLE LHR.WRH$_EVENT_NAME PURGE;
DROP TABLE LHR.WRH$_SQLCOMMAND_NAME PURGE;
DROP TABLE LHR.WRH$_PLAN_OPERATION_NAME PURGE;
DROP TABLE LHR.WRH$_PLAN_OPTION_NAME PURGE;
DROP TABLE LHR.WRH$_TOPLEVELCALL_NAME PURGE;
create or replace view dh_ash_11g_lhr
(snap_id, dbid, instance_number, sample_id, sample_time, session_id, session_serial#, session_type, flags, user_id, sql_id, is_sqlid_current, sql_child_number, sql_opcode, sql_opname, force_matching_signature, top_level_sql_id, top_level_sql_opcode, sql_plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options, sql_exec_id, sql_exec_start, plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id, qc_instance_id, qc_session_id, qc_session_serial#, px_flags, event, event_id, seq#, p1text, p1, p2text, p2, p3text, p3, wait_class, wait_class_id, wait_time, session_state, time_waited, blocking_session_status, blocking_session, blocking_session_serial#, blocking_inst_id, blocking_hangchain_info, current_obj#, current_file#, current_block#, current_row#, top_level_call#, top_level_call_name, consumer_group_id, xid, remote_instance#, time_model, in_connection_mgmt, in_parse, in_hard_parse, in_sql_execution, in_plsql_execution, in_plsql_rpc, in_plsql_compilation, in_java_execution, in_bind, in_cursor_close, in_sequence_load, capture_overhead, replay_overhead, is_captured, is_replayed, service_hash, program, module, action, client_id, machine, port, ecid, dbreplay_file_id, dbreplay_call_counter, tm_delta_time, tm_delta_cpu_time, tm_delta_db_time, delta_time, delta_read_io_requests, delta_write_io_requests, delta_read_io_bytes, delta_write_io_bytes, delta_interconnect_io_bytes, pga_allocated, temp_space_allocated)
as
select /* ASH/AWR meta attributes */
ash.snap_id, ash.dbid, ash.instance_number,
ash.sample_id, ash.sample_time,
/* Session/User attributes */
ash.session_id, ash.session_serial#,
decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'),
ash.flags,
ash.user_id,
/* SQL attributes */
ash.sql_id,
decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
ash.sql_child_number, ash.sql_opcode,
(select command_name from WRH$_SQLCOMMAND_NAME
where command_type = ash.sql_opcode
and dbid = ash.dbid) as sql_opname,
ash.force_matching_signature,
decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id),
decode(ash.top_level_sql_id, NULL, ash.sql_opcode,
ash.top_level_sql_opcode),
/* SQL Plan/Execution attributes */
ash.sql_plan_hash_value,
decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id),
(select operation_name from WRH$_PLAN_OPERATION_NAME
where operation_id = ash.sql_plan_operation#
and dbid = ash.dbid) as sql_plan_operation,
(select option_name from WRH$_PLAN_OPTION_NAME
where option_id = ash.sql_plan_options#
and dbid = ash.dbid) as sql_plan_options,
decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id),
ash.sql_exec_start,
/* PL/SQL attributes */
decode(ash.plsql_entry_object_id,0,to_number(NULL),
ash.plsql_entry_object_id),
decode(ash.plsql_entry_object_id,0,to_number(NULL),
ash.plsql_entry_subprogram_id),
decode(ash.plsql_object_id,0,to_number(NULL),
ash.plsql_object_id),
decode(ash.plsql_object_id,0,to_number(NULL),
ash.plsql_subprogram_id),
/* PQ attributes */
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#),
decode(ash.px_flags, 0, to_number(NULL), ash.px_flags),
/* Wait event attributes */
decode(ash.wait_time, 0, evt.event_name, NULL),
decode(ash.wait_time, 0, evt.event_id, NULL),
ash.seq#,
evt.parameter1, ash.p1,
evt.parameter2, ash.p2,
evt.parameter3, ash.p3,
decode(ash.wait_time, 0, evt.wait_class, NULL),
decode(ash.wait_time, 0, evt.wait_class_id, NULL),
ash.wait_time,
decode(ash.wait_time, 0, 'WAITING', 'ON CPU'),
ash.time_waited,
(case when ash.blocking_session = 4294967295
then 'UNKNOWN'
when ash.blocking_session = 4294967294
then 'GLOBAL'
when ash.blocking_session = 4294967293
then 'UNKNOWN'
when ash.blocking_session = 4294967292
then 'NO HOLDER'
when ash.blocking_session = 4294967291
then 'NOT IN WAIT'
else 'VALID'
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else ash.blocking_session
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else ash.blocking_session_serial#
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else ash.blocking_inst_id
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then NULL
else decode(bitand(ash.flags, power(2, 3)), NULL, 'N',
0, 'N', 'Y')
end),
/* Session's working context */
ash.current_obj#, ash.current_file#, ash.current_block#,
ash.current_row#, ash.top_level_call#,
(select top_level_call_name from WRH$_TOPLEVELCALL_NAME
where top_level_call# = ash.top_level_call#
and dbid = ash.dbid) as top_level_call_name,
decode(ash.consumer_group_id, 0, to_number(NULL),
ash.consumer_group_id),
ash.xid,
decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#),
ash.time_model,
decode(bitand(ash.time_model,power(2, 3)),0,'N','Y')
as in_connection_mgmt,
decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse,
decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse,
decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution,
decode(bitand(ash.time_model,power(2,11)),0,'N','Y')
as in_plsql_execution,
decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc,
decode(bitand(ash.time_model,power(2,13)),0,'N','Y')
as in_plsql_compilation,
decode(bitand(ash.time_model,power(2,14)),0,'N','Y')
as in_java_execution,
decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind,
decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close,
decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load,
decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y')
as capture_overhead,
decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' )
as replay_overhead,
decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured,
decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed,
/* Application attributes */
ash.service_hash, ash.program,
ash.module module,
ash.action action,
ash.client_id,
ash.machine, ash.port, ash.ecid,
/* DB Replay info */
ash.dbreplay_file_id, ash.dbreplay_call_counter,
/* stash columns */
ash.tm_delta_time,
ash.tm_delta_cpu_time,
ash.tm_delta_db_time,
ash.delta_time,
ash.delta_read_io_requests,
ash.delta_write_io_requests,
ash.delta_read_io_bytes,
ash.delta_write_io_bytes,
ash.delta_interconnect_io_bytes,
ash.pga_allocated,
ash.temp_space_allocated
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt
where ash.snap_id = sn.snap_id(+)
and ash.dbid = sn.dbid(+)
and ash.instance_number = sn.instance_number(+)
and ash.dbid = evt.dbid
and ash.event_id = evt.event_id;
----以下資料不能匯出
SELECT * FROM sys.Ku_Noexp_View d WHERE d.name LIKE '%WRH%' ;
SELECT * FROM DBA_OBJECTS d WHERE d.ORACLE_MAINTAINED='Y' AND D.object_name LIKE 'WR%';
-------------------預設使用者
SELECT d.username,d.default_tablespace,d.account_status, 'create user '|| d.username|| ' identified by '|| d.username ||' default tablespace '||d.default_tablespace||';' FROM dba_users d WHERE d.username not in ('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','DMSYS','DVSYS','EXFSYS','FLOWS_FILES','HR','IX','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PM','REMOTE_SCHEDULER_AGENT','SCOTT','SH','SI_INFORMATN_SCHEMA','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','XS$NULL','CSMIG');
-------------------------------------expdp和impdp 資料泵
`date +%Y%m%d`
%date:~0,4%%date:~5,2%%date:~8,2%
set CurDate=%date:~0,4%%date:~5,2%%date:~8,2%
set hh=%time:~0,2%
if /i %hh% LSS 10 ( set hh=0%time:~1,1%)
set ms=%time:~3,2%%time:~6,2%
set my_date=%CurDate%%hh%%ms%
grant read,write on directory DATA_PUMP_DIR to LHR;
windows下用：expdp \"/ AS SYSDBA\"
-------------匯出到服務端
expdp scott/tiger@orclasm directory=DATA_PUMP_DIR TABLES=EMP,DEPT dumpfile=expdp_by_lhr_`date +%Y%m%d`.dmp LOGFILE=expdp_by_lhr_`date +%Y%m%d`.log
expdp scott/tiger@orclasm directory=DATA_PUMP_DIR TABLES=EMP,DEPT dumpfile=expdp_by_lhr_%date:~0,4%%date:~5,2%%date:~8,2%.dmp LOGFILE=expdp_by_lhr_%date:~0,4%%date:~5,2%%date:~8,2%.log
--表級別
expdp lhr/lhr@orclasm directory=DATA_PUMP_DIR dumpfile=exptable.dmp TABLES=TEST_TSPITR2,TEST_TSPITR3 LOGFILE=expdp_table.log
expdp scott/tiger@orclasm directory=DATA_PUMP_DIR dumpfile=exptable.dmp TABLES=EMP,DEPT LOGFILE=expdp_table.log
expdp system/lhr@orclasm DIRECTORY=DATA_PUMP_DIR DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN ('EMP', 'DEPT')\"
impdp lhr/lhr@orclxp directory=DATA_PUMP_DIR dumpfile=exptable.dmp LOGFILE=impdp_table.log
--schema級別
expdp lhr/lhr@orclasm directory=DATA_PUMP_DIR dumpfile=HR.dmp SCHEMAS=HR,SCOTT,TESTUSER LOGFILE=HR.log
expdp system/oracle@orcl DIRECTORY=DATA_DUMP_DIR DUMPFILE=test_20140324.DMP SCHEMAS=test logfile=test_expdp_20111014.log status=10 parallel=4 CONTENT=ALL COMPRESSION=ALL
impdp lhr/lhr@orclasm directory=DATA_PUMP_DIR dumpfile=HR.dmp SCHEMAS=HR,SCOTT,TESTUSER parallel=4 LOGFILE=HR_20151125.log
--整個資料庫
expdp SYSTEM/ORACLE@ORCL DIRECTORY=DATA_DUMP_DIR DUMPFILE=TEST_20140324.DMP SCHEMAS=TEST LOGFILE=TEST_EXPDP_20111014.LOG STATUS=10 PARALLEL=1 CONTENT=ALL FLASHBACK_SCN=18341888 COMPRESSION=ALL
expdp \'/ AS SYSDBA\' DIRECTORY=DATA_DUMP_DIR FULL=Y DUMPFILE=FULLEXP.DMP LOGFILE=FULLEXP.LOG PARALLEL=2
impdp \'/ AS SYSDBA\' DIRECTORY=DATA_DUMP_DIR FULL=Y DUMPFILE=FULLEXP.DMP LOGFILE=FULLIMP.LOG PARALLEL=2 TABLE_EXISTS_ACTION=REPLACE EXCLUDE=STATISTICS,SCHEMA,TABLESPACE,ROLE,DIRECTORY,CONTEXT,PROFILE
impdp LHR/LHR@ORCLASM DIRECTORY=DATA_PUMP_DIR DUMPFILE=HR.DMP SCHEMAS=HR,SCOTT,TESTUSER PARALLEL=4 LOGFILE=HR_20151125.LOG
expdp SYSTEM/LHR DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=EXPDP_FULL_20150417.LOG EXCLUDE=STATISTICS
impdp SYSTEM/LHR DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=IMPDP_FULL_20150417.LOG PARALLEL=4 EXCLUDE=STATISTICS:"IN('')"
ORACLE_SID=ORA1024G
impdp \"/ AS SYSDBA\" DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=IMPDP_FULL_20150417.LOG JOB_NAME=IMPDP_LHR EXCLUDE=SCHEMA,TABLESPACE,ROLE,DIRECTORY, CONTEXT,PROFILE PARALLEL=2 TABLE_EXISTS_ACTION=REPLACE
impdp \"/ AS SYSDBA\" DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=IMPDP_FULL_20150417.LOG JOB_NAME=IMPDP_LHR EXCLUDE=TABLESPACE,ROLE,DIRECTORY, CONTEXT,PROFILE,USER,SCHEMA:"\=\'SYS'",SCHEMA:"\=\'IX'" PARALLEL=2 TABLE_EXISTS_ACTION=REPLACE
------ query選項
[ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par
query=SCOTT.EMP:"WHERE DEPTNO=20",SCOTT.DEPT:"WHERE DNAME='SALES'"
[ZFZHLHRDB1:oracle]:/oracle> expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_query_lhr_scott_02.dmp parfile=/tmp/scottfile.par log=test_query_lhr_scott_02.log
----- include
expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_include_lhr_scott_03.dmp logfile=test_include_lhr_scott_03.log job_name=my_job_lhr include=procedure,function,sequence:"like '%TEST%'"
include=procedure,function,sequence:"like '%TEST%'"
include=procedure
include=function
include=sequence:"like '%TEST%'"
expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_include_lhr_scott_05.dmp logfile=test_include_lhr_scott_05.log job_name=my_job_lhr parfile=/tmp/parfile.par
expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile='expdp_by_lhr_%date:~0,4%%date:~5,2%%date:~8,2%.dmp' LOGFILE='expdp_by_lhr_%date:~0,4%%date:~5,2%%date:~8,2%.log' job_name=my_job_lhr parfile=/tmp/parfile.par
-------- trace
expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_query_lhr_scott_02.dmp parfile=/tmp/scottfile.par log=test_query_lhr_scott_02.log trace=4a0300
---------匯出到本地
expdp lhr/lhr@orclxp directory=DATA_PUMP_DIR dumpfile=exptable.dmp TABLES=xb_log_lhr network_link=dblk_orclasm LOGFILE=expdp_table.log
impdp lhr/lhr@orclxp directory=DATA_PUMP_DIR dumpfile=exptable.dmp LOGFILE=impdp_table.log
---- 直接匯入不生成檔案
impdp lhr/lhr@orclxp network_link=dblk_orclasm directory=DATA_PUMP_DIR TABLES=xb_log_lhr PARALLEL=2 LOGFILE=impdp_table.log
---- 生成ddl語句不會匯入資料
--expdp \'/ AS SYSDBA\' tables=lhr.exptest directory=DATA_PUMP_DIR dumpfile=exptest.dmp logfile=exp_exptest.dmp EXCLUDE=STATISTICS
--expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=lhrsql20161215.log content=metadata_only schemas=SCOTT EXCLUDE=STATISTICS
impdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=imp_exptest.log sqlfile=exptest.sql
exp \'/ AS SYSDBA\' tables=scott.emp file=/tmp/exp_ddl_lhr_01.dmp log=/tmp/exp_table.log buffer=41943040 rows=n compress=n
imp \'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y
set pagesize 0
set trimspool ON
SET linesize 10000
set long 90000
set feedback OFF
set feed off;
set echo off
spool schema_scott.sql
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME,U.owner)
FROM DBA_OBJECTS U
WHERE U.OBJECT_TYPE IN ('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION'，'PACKAGE','TRIGGER')
AND U.owner='SCOTT';
spool off;
----只匯出表結構
expdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515.log content=metadata_only schemas=TEST,SQCHECK,DWUSER
impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log sqlfile=lhrsql20150515.sql
impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515_imp.log
impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log TRANSFORM=storage:n TRANSFORM=SEGMENT_CREATION:n
impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log sqlfile=lhrsql20150515.sql TRANSFORM=segment_attributes:n
--transform=segment_attributes|storage|SEGMENT_CREATION|oid|pctspace：Y/N:object_type
----修改物件schema和tablespace
impdp test/test directory=exp_dump dumpfile=test.dmp logfile=test.log remap_schema=test1:test2 remap_tablespace=TBS_DAT_1:TBS_DAT_2,TBS_IDX_1:TBS_IDX_2
impdp test/test directory=exp_dump dumpfile=test.dmp logfile=test.log remap_schema=test1:test2 remap_tablespace=TBS_DAT_1:TBS_DAT_2 remap_tablespace=TBS_IDX_1:TBS_IDX_2
----顯示時間
expdp SCOTT/tiger@orclasm DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT%U.dmp SCHEMAS=SCOTT COMPRESSION=all metrics=y
--dmp檔案重用 reuse_dumpfiles=y
expdp \''sys/"l@h\r/0"'@LHRDB as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=dmp_SCOTT.dmp SCHEMAS=SCOTT reuse_dumpfiles=y
#scp -r root@10.0.24.103:/home2/backup/ /home/mover00/shadow_bak/sites/
複製遠端（10.0.24.103）的/home2/backup/ 到本地的 /home/mover00/shadow_bak/sites/
#scp -r /home2/backup/ root@10.0.24.99:/home/mover00/shadow_bak/sites/
複製本地的/home2/backup/ 到遠端（10.0.24.99）的 /home/mover00/shadow_bak/sites/
set line 9999
col owner_name for a10
col job_name for a25
col operation for a10
col job_mode for a10
col state for a15
col job_mode for a10
col state for a15
col osuser for a10
col "degree|attached|datapump" for a25
col session_info for a20
SELECT s.inst_id,
dj.owner_name,
dj.job_name,
dj.operation,
dj.job_mode,
dj.state,
dj.degree || ',' || dj.attached_sessions || ',' ||
dj.datapump_sessions "degree|attached|datapump",
ds.session_type,
s.osuser,
(SELECT s.SID || ',' || s.SERIAL# || ',' || p.SPID
FROM gv$process p
where s.paddr = p.addr
AND s.inst_id = p.inst_id) session_info
FROM DBA_DATAPUMP_JOBS dj --gv$datapump_job
full outer join dba_datapump_sessions ds --gv$datapump_session
on (dj.job_name = ds.job_name and dj.owner_name = ds.owner_name)
left outer join gv$session s
on (s.saddr = ds.saddr)
ORDER BY dj.owner_name, dj.job_name;
select * from GV$DATAPUMP_SESSION;
select * from GV$datapump_jobs;
select * From dba_datapump_jobs;
impdp \"/ as sysdba\" attach=IMPDP_LHR
------------- parfile
[root@rhel6_lhr dpdump]# more par.f
DUMPFILE=EXPDAT.DMP
DIRECTORY=DATA_PUMP_DIR
TRANSPORT_DATAFILES=
/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.DBF,
/u01/app/oracle/admin/orclasm/dpdump/APP2TBS.DBF,
/u01/app/oracle/admin/orclasm/dpdump/IDXTBS.DBF
LOGFILE=tts_import.log
[root@rhel6_lhr dpdump]#
[oracle@rhel6 ~]$ impdp system/lhr parfile='/u01/app/oracle/admin/orclasm/dpdump/par.f'
----檢視使用者的目錄許可權
column grantee format a10
column grantor format a10
column dir_name format a20
column dir_path format a50
column privilege format a10
break on dir_name
select
d.directory_name dir_name,
d.directory_path dir_path,
p.privilege,
p.grantee,
p.grantor
from
dba_tab_privs p,
dba_directories d
where
p.table_name = d.directory_name and
p.grantee = upper('&user')
order by
d.directory_name,
p.privilege
/
CREATE USER LHRSYS IDENTIFIED BY LHRSYS;
GRANT UPDATE (ENAME,SAL) ON SCOTT.EMP TO LHRSYS;
GRANT UPDATE (ENAME) ON SCOTT.EMP TO LHRSYS;
GRANT SELECT ON SCOTT.EMP TO LHRSYS;
GRANT CONNECT TO LHRSYS;
GRANT CREATE JOB TO LHRSYS;
SELECT * FROM DBA_COL_PRIVS D WHERE D.GRANTEE='LHRSYS';
SELECT * FROM DBA_TAB_PRIVS D WHERE D.GRANTEE='LHRSYS';
SELECT * FROM DBA_SYS_PRIVS D WHERE D.GRANTEE='LHRSYS';
SELECT * FROM DBA_ROLE_PRIVS D WHERE D.GRANTEE='LHRSYS';
--檢視建立表SQL語句：
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME) FROM USER_TABLES U;
--檢視建立索引的SQL語句：
SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DEPT','SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME) FROM USER_INDEXES U;
--檢視建立主鍵的SQL語句：
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK') FROM DUAL;
--檢視建立外來鍵的SQL語句：
SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_FK_DEPT') FROM DUAL;
--檢視建立檢視（VIEW）的SQL語句：
SELECT DBMS_METADATA.GET_DDL('VIEW', 'MY_TABLES','SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('VIEW', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'VIEW';
SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=UPPER('&VIEW_NAME');
--檢視建立儲存過程（PROCEDURE）的SQL語句：
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'PROCEDURE';
--檢視建立觸發器（TRIGGER）的SQL語句：
SELECT DBMS_METADATA.GET_DDL('TRIGGER', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'TRIGGER';
--檢視建立函式（FUNCTION）的SQL語句：
SELECT DBMS_METADATA.GET_DDL('FUNCTION', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'FUNCTION';
--檢視建立包（PACKAGE）的SQL語句：
SELECT DBMS_METADATA.GET_DDL('PACKAGE', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'PACKAGE';
--檢視建立序列（SEQUENCE）的SQL語句：
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'SEQUENCE';
--檢視建立同義詞（SYNONYM）的SQL語句：
SELECT DBMS_METADATA.GET_DDL('SYNONYM', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'SYNONYM';
--檢視建立表空間（TABLESPACE）的SQL語句：
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', U.TABLESPACE_NAME)
FROM USER_TABLESPACES U;
--檢視建立角色（ROLE）的SQL語句：
SELECT DBMS_METADATA.GET_DDL('ROLE', U.ROLE) FROM DBA_ROLES U;
--檢視建立使用者（USER）的SQL語句：
SELECT DBMS_METADATA.GET_DDL('USER','SYS') FROM DUAL;
------------------------- 得到表空間DDL語句
SELECT (SELECT b.NAME FROM v$tablespace b WHERE b.TS# = a.TS#) ts_name,
a.NAME datafilename,
'create tablespace '||(SELECT b.NAME FROM v$tablespace b WHERE b.TS# = a.TS#) || ' datafile ' || a.NAME ||' size ;'
FROM v$datafile a;
SELECT TABLESPACE_NAME,
substr(create_ts, 1, instr(create_ts, 'EXTENT') - 1) || ';'
FROM (SELECT a.TABLESPACE_NAME,
replace(to_char(DBMS_METADATA.GET_DDL('TABLESPACE',
a.tablespace_name)),
chr(10),
'') create_ts
FROM DBA_TABLESPACES a) v
where v.TABLESPACE_NAME not in
('SYSTEM', 'SYSAUX', 'UNDOTBS1', 'TEMP', 'USERS', 'EXAMPLE');
------------------------- 得到使用者及其許可權的DDL語句
SELECT DBMS_METADATA.GET_DDL('USER','LHRSYS') DDL_SQL FROM DUAL
UNION ALL
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHRSYS'))) FROM DUAL
UNION ALL
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHRSYS'))) FROM DUAL
UNION ALL
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHRSYS'))) FROM DUAL;
drop table t_tmp_user_lhr;
create table t_tmp_user_lhr( id number, username varchar2(50), exec_sql varchar2(4000),create_type varchar2(20) );
DROP sequence s_t_tmp_user_lhr;
create sequence s_t_tmp_user_lhr;
begin
for cur in (SELECT d.username,
d.default_tablespace,
d.account_status,
'create user ' || d.username || ' identified by ' ||
d.username || ' default tablespace ' ||
d.default_tablespace || ' TEMPORARY TABLESPACE ' ||
D.temporary_tablespace || ';' CREATE_USER,
replace(to_char(DBMS_METADATA.GET_DDL('USER',
D.username)),
chr(10),
'') create_USER1
FROM dba_users d
WHERE d.username not in ('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','DMSYS','DVSYS','EXFSYS','FLOWS_FILES','HR','IX','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PM','REMOTE_SCHEDULER_AGENT','SCOTT','SH','SI_INFORMATN_SCHEMA','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','XS$NULL','CSMIG')) loop
INSERT INTO t_tmp_user_lhr
(id, username, exec_sql, create_type)
values
(s_t_tmp_user_lhr.nextval, cur.username, cur.CREATE_USER, 'USER');
INSERT INTO t_tmp_user_lhr
(id, username, exec_sql, create_type)
SELECT s_t_tmp_user_lhr.nextval,
cur.username,
CASE
WHEN D.ADMIN_OPTION = 'YES' THEN
'GRANT ' || d.privilege || ' TO ' || d.GRANTEE ||
' WITH GRANT OPTION ;'
ELSE
'GRANT ' || d.privilege || ' TO ' || d.GRANTEE || ';'
END priv,
'DBA_SYS_PRIVS'
FROM dba_sys_privs d
WHERE D.GRANTEE = CUR.USERNAME;
INSERT INTO t_tmp_user_lhr
(id, username, exec_sql, create_type)
SELECT s_t_tmp_user_lhr.nextval,
cur.username,
CASE
WHEN D.ADMIN_OPTION = 'YES' THEN
'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE ||
' WITH GRANT OPTION;'
ELSE
'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE || ';'
END priv,
'DBA_ROLE_PRIVS'
FROM DBA_ROLE_PRIVS d
WHERE D.GRANTEE = CUR.USERNAME;
INSERT INTO t_tmp_user_lhr
(id, username, exec_sql, create_type)
SELECT s_t_tmp_user_lhr.nextval,
cur.username,
CASE
WHEN d.grantable = 'YES' THEN
'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||
d.table_name || ' TO ' || d.GRANTEE ||
' WITH GRANT OPTION ;'
ELSE
'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||
d.table_name || ' TO ' || d.GRANTEE || ';'
END priv,
'DBA_TAB_PRIVS'
FROM DBA_TAB_PRIVS d
WHERE D.GRANTEE = CUR.USERNAME;
end loop;
COMMIT;
end;
/
SELECT * FROM t_tmp_user_lhr;
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
---怎麼批次去除WORD裡表格中的超連結
全選文件。按"Ctrl+shift+F9"斷開連線就行了。
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
----linux下批次查詢/替換文字內容
--一般在本地電腦上批次替換文字有許多工具可以做到，比如sublime text ,但大多伺服器上都是無圖形介面的，為此收集了幾條針對linux命令列實現批次替換文字內容的命令：
--1.批次查詢某個目下檔案的包含的內容，例如：
# grep -rn "要找查詢的文字" ./
[oracle@rhel6_lhr dpdump]$ grep -rn "ALTER SESSION SET EVENTS" ./
./spool_result.sql:2:ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
./spool_result.sql:3:ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
./spool_result.sql:4:ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
./spool_result.sql:5:ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
./spool_result.sql:6:ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
./spool_result.sql:7:ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
--2.批次查詢並替換檔案內容。
# sed -i "s/要找查詢的文字/替換後的文字/g" `grep -rl "要找查詢的文字" ./`
例如替換被病毒修改的一段指令碼：
sed -i "s/<script type=\"text\/javascript\" src='http:\/\/t.cn\/RhyQ1GN'><\/script>//g" `grep -rl "<script type=\"text\/javascript\" src='http:\/\/t.cn\/RhyQ1GN'><\/script>" ./`
----將STORAGE(INITIAL開頭的行整行替換為STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
-- 必須加點和星號和最後的g ，否則不能整行替換
sed 's/^STORAGE(INITIAL.*/STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645/g' a.txt > b.txt
-------------------------------------------------------------------------------------------------------------- 修改日期的顯示格式
execute immediate 'alter session set NLS_DATE_FORMAT=''YYYY-MM-DD DY HH24:MI:SS''';
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
----------------------------------------------------------------------------- sqlplus 設定
--$ORACLE_HOME/sqlplus/admin/glogin.sql
set linesize 9999 pagesize 9999
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
set time on;
set line 9999
set pagesize 9999;
set timing on;
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
set echo on;
set time on;
SET LONG 99999999;
SET LONGCHUNKSIZE 1000000;
set timing on;
set serveroutput on size 1000000;
set sqlblanklines on;
set linesize 800;
set pagesize 50000;
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
host color 02
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
set errorlogging on table lhr.sperrorlog;
set errorlogging on identifier LHR_SESSION
--- alter session set nls_date_language='american' ;
--------------------------------------------------------rman備份-----------------------------------------------------------
backup as backupset database format '/u05/oracle/oracle_bk/orclasm/full_%n_%T_%t_%s_%p.bak' include current controlfile plus archivelog;
backup as compressed backupset format '/arch/oracle_bk/ora2lhr/full_%n_%T_%t_%s.bak' database include current controlfile plus archivelog delete input ;
----------------------------- 歸檔 open 全備
export ORACLE_SID=ora11g
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
MYDATE=`date +'%Y%m%d%H%M%S'`
BACKUP_DIR=/home/oracle/oracle_bk/$ORACLE_SID
mkdir -p $BACKUP_DIR/log
rman target / log $BACKUP_DIR/log/rman_full_$MYDATE.log append <<EOF
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database filesperset 4 format '$BACKUP_DIR/full_%n_%T_%t_%s_%p.bak';
backup spfile tag='bk_spfile_$MYDATE' format='$BACKUP_DIR/spfile_%n_%U_%T.bak';
sql 'alter system archive log current';
backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p.bak' delete input;
backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p.bak';
release channel c1;
release channel c2;
}
EOF
rman target / log $BACKUP_DIR/log/rman_delete_$MYDATE.log append <<EOF
allocate channel for maintenance type disk;
allocate channel for maintenance type sbt_tape;
crosscheck archivelog all;
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired archivelog all;
EOF
----------------------------- 非歸檔 mount 全備
export ORACLE_SID=oralhr
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
MYDATE=`date +'%Y%m%d%H%M%S'`
BACKUP_DIR=/home/oracle/oracle_bk/$ORACLE_SID
mkdir -p $BACKUP_DIR/log
rman target / log $BACKUP_DIR/log/rman_full_$MYDATE.log append <<EOF
run
{
shutdown immediate;
startup mount;
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database filesperset 4 format '$BACKUP_DIR/full_%n_%T_%t_%s_%p.bak';
backup spfile tag='bk_spfile_$MYDATE' format='$BACKUP_DIR/spfile_%n_%U_%T.bak';
backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p.bak' delete input;
backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p.bak';
release channel c1;
release channel c2;
alter database open;
}
EOF
rman target / log $BACKUP_DIR/log/rman_delete_$MYDATE.log append <<EOF
allocate channel for maintenance type disk;
allocate channel for maintenance type sbt_tape;
crosscheck archivelog all;
crosscheck backup;
delete noprompt obsolete;
EOF
--------------------------------------------------------冷備（最適合非歸檔）-----------------------------------------------------------
set feedback off
set heading off
set verify off
set trimspool off
set pagesize 0
set linesize 200
define dir = '/home/oracle/oracle_bk/coolbak'
define script = '/tmp/coolbak.sql'
spool &script
select 'ho cp ' || name || ' &dir' from v$controlfile
union all
select 'ho cp ' || name || ' &dir' from v$datafile
union all
select 'ho cp ' || member || ' &dir' from v$logfile
union all
select 'ho cp ' || name || ' &dir' from v$tempfile
/
create pfile = '&dir/initorcl.ora' from spfile;
ho cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwutf8test &dir
spool off
alter database backup controlfile to trace as '/home/oracle/oracle_bk/coolbak/ctl.sql';
shutdown immediate
start &script
--ho rm &script
startup
---------有壞塊情況下的備份 ORA-19566: exceeded limit of 0 corrupt blocks for file /oracle/app/oracle/oralhr/users01.dbf
run{
set maxcorrupt for datafile 4 to 2;
backup datafile 4 tag='2corruptblock';
}
--------------------------------------------------------熱備(歸檔)-----------------------------------------------------------
--------------------------- 熱備基於database (歸檔)
set feedback off
set heading off
set verify off
set trimspool off
set pagesize 0
set linesize 200
define dir = '/home/oracle/oracle_bk/hotbak'
define script = '/tmp/hotbak.sql'
spool &script
select 'ho cp ' ||name|| ' &dir' from v$datafile;
spool off
alter database begin backup;
start &script
alter database end backup;
alter database backup controlfile to trace as '&dir/controlbak.sql';
alter database backup controlfile to '&dir/controlbak.ctl';
create pfile = '&dir/initorcl.ora' from spfile;
--------------------------熱備基於表空間 (歸檔)
set feedback off
set heading off
set verify off
set trimspool off
set pagesize 0
set linesize 200
define dir = '/home/oracle/oracle_bk/hotbak'
define script = '/tmp/hotbak_tb.sql'
spool &script
select 'alter tablespace '|| tablespace_name ||' begin backup ;' ||
chr(10)||'ho cp ' || file_name || ' &dir ' ||
chr(10)||'alter tablespace '|| tablespace_name || ' end backup;'
from dba_data_files order by tablespace_name;
spool off
alter system switch logfile;
start &script
alter system switch logfile;
alter database backup controlfile to '&dir/controlbak.ctl';
alter database backup controlfile to trace as '&dir/controlbak.sql';
create pfile = '&dir/initorcl.ora' from spfile;
----------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------
-------------------------------------- LogMiner 日誌挖掘
要安裝LogMiner工具，必須首先要執行下面這樣兩個指令碼：
$ORACLE_HOME/rdbms/admin/dbmslm.sql
$ORACLE_HOME/rdbms/admin/dbmslmd.sql
這兩個指令碼必須均以SYS使用者身份執行。其中第一個指令碼用來建立DBMS_LOGMNR包，該包用來分析日誌檔案。第二個指令碼用來建立DBMS_LOGMNR_D包，該包用來建立資料字典檔案。
---設定單獨的表空間
create tablespace ts_LOGMNR datafile '/sda4/u01/app/oracle/oradata/orcllinux/logmnr.dbf' size 1G autoextend on next 2M ;
exec dbms_logmnr_d.set_tablespace('ts_logmnr');
---------------- 附加日誌
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
alter database add supplemental log data(primary key) columns;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_pk FROM V$DATABASE;
---找到需要進行日誌挖掘的歸檔日誌
list archivelog all completed between '2017-01-01 16:20:00' and '2017-01-01 16:25:00';
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';
exec dbms_logmnr.add_logfile('歸檔日誌檔案', Options => dbms_logmnr.new);
exec dbms_logmnr.add_logfile('歸檔日誌檔案', Options => dbms_logmnr.ADDFILE);
--若歸檔日誌不在本地，則需要恢復相應的歸檔日誌到本地目錄。
run {allocate channel ci type disk;
set archivelog destination to '/tmp';
restore archvielog from logseq xxx until logseq xxx;
release channel ci;
};
-------------- 利用平面檔案作為資料字典
alter system set utl_file_dir='/home/oracle/' scope=spfile; --然後重啟庫
exec dbms_logmnr_d.build('log.ora','/home/oracle/',dbms_logmnr_d.store_in_flat_file);
exec dbms_logmnr.add_logfile('+FRA/orclasm/archivelog/2015_01_20/thread_1_seq_952.463.869481079',dbms_logmnr.new);
exec dbms_logmnr.add_logfile('+FRA/orclasm/archivelog/2015_01_20/thread_1_seq_953.462.869481107',dbms_logmnr.addfile);
exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/log.ora');
create table LHR.testlog as select * from v$logmnr_contents a;
EXEC DBMS_LOGMNR.END_LOGMNR;
-------------------------- redo 字典
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
exec dbms_logmnr_d.build(options => dbms_logmnr_d.STORE_IN_REDO_LOGS);
select a.*
from v$archived_log a
WHERE a.name IS NOT NULL
and (a.DICTIONARY_BEGIN = 'YES' or a.DICTIONARY_END = 'YES');
-------------- 利用線上日誌作為資料字典
SELECT ' dbms_logmnr.add_logfile(''' || MEMBER || ''');' FROM v$logfile;
BEGIN
dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_3.263.850260263',dbms_logmnr.new) ;
dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_2.262.850260259',dbms_logmnr.ADDFILE) ;
dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_1.261.850260255',dbms_logmnr.ADDFILE)) ;
dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);
END;
SELECT scn, sql_redo, a.SQL_UNDO, a.*
FROM v$logmnr_contents a
WHERE a.OPERATION = 'INSERT'
and a.TABLE_NAME = 'AABB';
create table testlog as select * from v$logmnr_contents a;
begin
dbms_logmnr.end_logmnr();
end;
EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
-------------------------- online字典
begin
dbms_logmnr.start_logmnr(startScn => 23573690,
endScn => 23632671,
Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG + dbms_logmnr.CONTINUOUS_MINE);
end;
begin
dbms_logmnr.start_logmnr(startScn => 23573690,
endScn => 23632671,
Options => dbms_logmnr.DICT_FROM_REDO_LOGS +
dbms_logmnr.CONTINUOUS_MINE);
end;
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';
list archivelog all completed between '2015-06-03 08:00:00' and '2015-06-03 09:00:00';
exec SYS.DBMS_LOGMNR.ADD_LOGFILE( '/u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2015_06_03/o1_mf_1_505_7w35gdnx_.arc', sys.dbms_logmnr.New);
BEGIN
DBMS_LOGMNR.START_LOGMNR(
STARTTIME => '2015-06-03 11:10:12',
ENDTIME => '2015-06-03 11:13:06',
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
END;
/
---查詢挖掘到的結果
select a.SCN,a.TIMESTAMP,a.SQL_REDO from v$logmnr_contents A where table_name='XXXX' and OPERATION='INSERT' order by a.SCN;
--清除控制檔案中關於v$archived_log的資訊
execute sys.dbms_backup_restore.resetCfileSection(11);
---恢復歸檔
1.恢復全部歸檔日誌檔案
RMAN> restore archivelog all;
2.只恢復5到8這四個歸檔日誌檔案
RMAN> restore archivelog from logseq 5 until logseq 8;
3.恢復從第5個歸檔日誌起
RMAN> restore archivelog from logseq 5;
4.恢復7天內的歸檔日誌
RMAN> restore archivelog from time 'sysdate-7';
5. sequence between 寫法
RMAN> restore archivelog sequence between 1 and 3;
6.恢復到哪個日誌檔案為止
RMAN> restore archivelog until logseq 3;
7.從第五個日誌開始恢復
RMAN> restore archivelog low logseq 5;
8.到第5個日誌為止
RMAN> restore archivelog high logseq 5;
恢復指定的archivelog：restore archivelog sequence 18;
--若歸檔日誌不在本地，則需要恢復相應的歸檔日誌到本地目錄。
run {allocate channel ci type disk;
set archivelog destination to '/tmp';
restore archvielog from logseq xxx until logseq xxx;
release channel ci;
};
------------------------------------------------------------------------------------------ 詳細執行計劃
---------------Session級別：
ALTER SESSION SET STATISTICS_LEVEL=ALL;
----------------- 詫句級別使用HINT
select /*+ gather_plan_statistics*/ ...
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f6cz4n8y72xdc',0,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
-------------------------------------------------------------------------------------------- 其他
SELECT * FROM DBA_STREAMS_UNSUPPORTED;
------------------- 鎖和事務關係
select * from v$lock a where type in ('TM','TX') ;
SELECT * from v$transaction;
select trunc(655385/power(2,16)) XIDUSN from dual;
10
select bitand(655385,to_number('ffff','xxxx'))+0 XIDSLOT from dual;
25
---------------------------------truncate 後的恢復-------------------------- 恢復的過程會多次執行 alter system flush buffer_cache;
------------ tmp目錄空閒空間需要比表的大小大
create table lhr.TRUNTAB as SELECT * FROM dba_objects where rownum<=10;
SELECT COUNT(1) FROM lhr.TRUNTAB;
truncate table lhr.TRUNTAB;
exec fy_recover_data.recover_truncated_table('LHR','TRUNTAB',1);
SELECT * FROM lhr.TRUNTAB$$;
insert into lhr.TRUNTAB SELECT * FROM lhr.TRUNTAB$$;
commit;
drop tablespace FY_REC_DATA including contents and datafiles;
drop tablespace FY_RST_DATA including contents and datafiles;
---閃回
alter table TRUNTAB enable row movement;
flashback table TRUNTAB to timestamp to_date('2014/12/31 17:33:00','YYYY/MM/DD HH24:MI/SS');
--------------長查詢
SELECT * FROM vw_active_session_lhr a where a.USERNAME IS NOT NULL ;
SELECT * FROM vw_longrun_lhr;
SET LINE 9999 PAGESIZE 9999
col username format a10
col session_info format a30
col target format a20
col opname format a35
col message format a80
col sofar_TOTALWORK format a20
col progress format a8
SELECT A.USERNAME,
(SELECT NB.SID || ',' || NB.SERIAL# || ',' || PR.SPID || ',' ||NB.OSUSER|| ',' ||nb.status|| ',' ||nb.EVENT
FROM GV$PROCESS PR, GV$SESSION NB
WHERE NB.PADDR = PR.ADDR
AND NB.SID = A.SID
AND NB.SERIAL# = A.SERIAL#
AND PR.INST_ID = NB.INST_ID) SESSION_INFO,
A.TARGET,
A.OPNAME,
TO_CHAR(A.START_TIME, 'YYYY-MM-DD HH24:MI:SS') START_TIME,
ROUND(A.SOFAR * 100 / A.TOTALWORK, 2) || '%' AS PROGRESS,
(A.SOFAR || ':' || A.TOTALWORK) SOFAR_TOTALWORK,
A.TIME_REMAINING TIME_REMAINING,
A.ELAPSED_SECONDS ELAPSED_SECONDS,
MESSAGE MESSAGE
FROM GV$SESSION_LONGOPS A
WHERE A.TIME_REMAINING <> 0
ORDER BY A.TIME_REMAINING DESC, A.SQL_ID, A.SID;
SELECT * FROM vw_tablespace_datafile_lhr ;
SELECT * FROM xb_audit_ddl_lhr a WHERE a.id >=2373180 ;
----------------------------------------------------------- 歸檔
alter system set log_archive_dest_1='location=D:\arch';
alter system set log_archive_dest='USE_DB_RECOVERY_FILE_DEST';
show parameter DB_RECOVERY_FILE_DEST
如果歸檔日誌為自動歸檔，則切換日誌(alter system switch logfile)會自動歸檔；
如果為手動歸檔模式，則不會歸檔，除非你執行下列命令手動歸檔：
alter system archive log sequence lognumber
alter system archive log all
alter system archive log current
檢視歸檔日誌模式:select log_mode from v$database;
NOARCHIVELOG-- 為不歸檔
ARCHIVELOG -- 為自動歸檔
MANUAL --手動歸檔模式
修改歸檔日誌模式:
alter database noarchivelog;
alter database archivelog;
alter database archivelog manual;
----------------透過移動資料檔案來均衡檔案I/O
col PHYRDS format 999999999
col PHYWRTS format 999999999
col READTIM format 999999999
col WRITETIM format 999999999
col name for a60
set line 9999 pagesize 9999
select name,phyrds,phywrts,readtim,writetim
from v$filestat a,v$datafile b
where a.file#=b.file#
union all
select name,PHYRDS,PHYWRTS,READTIM,WRITETIM from v$tempstat a,v$tempfile b where a.file#=b.file#
order by readtim desc;
/*檢視錶最後一次DML時間*/
select max(ora_rowscn), to_char(scn_to_timestamp(max(ora_rowscn)),'YYYY-MM-DD HH24:MI:SS') from aa;
select a.*,dbms_rowid.rowid_block_number(rowid),to_char(ora_rowscn),to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS') from AA a;
SQL> select ename ,sal,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_relative_fno(rowid) as file# from scott.emp where empno=7839;
ENAME SAL BLOCK# FILE#
---------- ---------- ---------- ----------
KING 5000 32 4
SQL>
------------------------------------------ 日誌切換頻率
select b.SEQUENCE#,
b.FIRST_TIME,
a.SEQUENCE#,
a.FIRST_TIME,
round(((a.FIRST_TIME - b.FIRST_TIME) * 24) * 60, 2) min
from v$log_history a, v$log_history b
where a.SEQUENCE# = b.SEQUENCE# + 1
and b.THREAD# = 1
order by a.SEQUENCE# desc;
select sequence#,
first_time,
nexttime,
round(((first_time - nexttime) * 24) * 60, 2) diff
from (select sequence#,
first_time,
lag(first_time) over(order by sequence#) nexttime
from v$log_history
where thread# = 1)
order by sequence# desc;
select max (first_time) max_first_time,
to_char (first_time, 'yyyy-mm-dd') day,
count (recid) count_number,
count (recid) * 200 size_mb
from v$log_history
group by to_char (first_time, 'yyyy-mm-dd')
order by 1;
--------------------------------------------------------------------------------------------------- 開啟10046事件
10046事件級別：
level 0：禁用SQL_TRACE，等價於SQL_TRACE=FALSE
level 1：啟用標準的sql_trace功能跟蹤SQL語句，包括解析、執行、提取、提交和回滾等，等價於SQL_TRACE=TRUE
level 4：Level 1 +包括變數(bind values)的詳細資訊
level 8：Level 1 + 包括等待事件
level 12：包括繫結變數與等待事件，包含Level 1 + Level 4 + Level 8
alter session set SQL_TRACE=true;
alter session set SQL_TRACE=false;
alter session set events '10046 trace name context forever, level 12';
alter session set events '10046 trace name context off';
alter session set events '10046 trace name context off, LEVEL 12';
--對單個 SQL ID 開啟10046事件跟蹤
ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:&&SQL_ID] BIND=TRUE,WAIT=TRUE';
ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:C7452AGJ0S0T6] WAIT=TRUE,BIND=TRUE,PLAN_STAT=ALL_EXECUTIONS,LEVEL=12';
--關閉單個SQL的跟蹤命令如下所示：
ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:&&SQL_ID] OFF';
exec dbms_session.set_sql_trace(true);
exec dbms_session.set_sql_trace(false);
exec dbms_session.session_trace_enable(waits=>true,binds=>true);
exec dbms_session.session_trace_enable();
-----跟蹤其它會話
SQL> exec dbms_system.set_ev(sid,serial#,10046,12,'');
SQL> exec dbms_system.set_ev(sid,serial#,10046,0,'');
exec dbms_system.set_sql_trace_in_session(9,437,true);
exec dbms_system.set_sql_trace_in_session(9,437,false);
SQL> exec dbms_monitor.session_trace_enable;
SQL> 執行sql
SQL> exec dbms_monitor.session_trace_disable;
跟蹤其他會話：
SQL> exec dbms_monitor.session_trace_enable(session_id=>sid,serial_num=>serial#,waits=>true,binds=>true);
SQL> exec dbms_monitor.session_trace_disable(session_id=>sid,serial_num=>serial#);
--跟蹤當前會話：
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> 執行sql
SQL> oradebug tracefile_name
SQL> oradebug event 10046 trace name context off;
Statement processed.
--跟蹤其他會話：
SQL> select spid,pid2 from v$process
2 where addr in (select paddr from v$session where sid=(select distinct sid from v$mystat));
SPID PID
------------ ----------
1457 313
SQL> oradebug setospid 1457;
Statement processed.
或者
SQL> oradebug setorapid 313;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> oradebug tracefile_name
SQL> oradebug event 10046 trace name context off;
Statement processed.
SELECT a.SID,
b.SERIAL# ,
c.SPID
FROM v$mystat a,
v$session b ,
v$process c
WHERE a.SID = b.SID
and b.PADDR=c.ADDR
AND rownum = 1;
--啟用errorstack的跟蹤來找到出現問題的SQL語句
alter session set events '3001 trace name errorstack level 3';
---跟蹤1438的錯誤
alter system set events='1438 trace name errorstack forever,level 3';
alter system set events='1438 trace name errorstack off';
系統預設沒有安裝dbms_support這個包，可以手動執行$ORACLE_HOME/rdbms/admin/bmssupp.sql指令碼來建立該包跟蹤當前會話：
SQL> exec dbms_support.start_trace
SQL> 執行sql
SQL> exec dbms_support.stop_trace
跟蹤其他會話：等待事件+繫結變數，相當於level 12的10046事件。
SQL> select sid,serial#,username from v$session where ...;
SQL> exec dbms_support.start_trace_in_session(sid=>sid,serial=>serial#,waits=>true,binds=>true);
SQL> exec dbms_support.stop_trace_in_session(sid=>sid,serial=>serial#);
----select value from v$diag_info where name like '%Default%';
--轉儲檔案路徑
col TRACE_FILE_NAME format a100
SELECT d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
p.spid || '.trc' trace_file_name
FROM (SELECT p.spid
FROM v$mystat m,
v$session s,
v$process p
WHERE m.statistic# = '1'
AND s.sid = m.sid
AND p.addr = s.paddr) p,
(SELECT t.instance
FROM v$thread t,
v$parameter v
WHERE v.name = 'thread'
AND (v.value = '0' OR to_char(t.thread#) = v.VALUE)) i,
(SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
CREATE OR REPLACE VIEW VW_TRACEFILE_ALL_LHR AS
SELECT INST_ID,
SID,
SERIAL#,
SPID,
USERNAME,
D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM (SELECT S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.USERNAME
FROM GV$SESSION S, GV$PROCESS P
WHERE P.ADDR = S.PADDR
AND S.INST_ID = P.INST_ID) P,
(SELECT T.INSTANCE
FROM GV$THREAD T, GV$PARAMETER V
WHERE V.NAME = 'thread'
AND (V.VALUE = '0' OR TO_CHAR(T.THREAD#) = V.VALUE)) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;
--建立公共同義詞：
CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACEFILE_ALL_LHR FOR VW_TRACEFILE_ALL_LHR;
在Oracle 11g中可以直接查詢V$PROCESS獲取TRACE檔案：
CREATE OR REPLACE VIEW VW_TRACEFILE_LHR AS
SELECT S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.USERNAME, P.TRACEFILE
FROM GV$SESSION S, GV$PROCESS P
WHERE P.ADDR = S.PADDR
AND S.INST_ID = P.INST_ID ;
--建立公共同義詞：
CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACEFILE_LHR FOR VW_TRACEFILE_LHR;
--------------------------如何生成 systemstate dump systemdump systemstate
###sysdba可登陸時
$sqlplus "/as sysdba"
SQL>oradebug setmypid
SQL>--其中266表示dump的級別，不用調整,即把系統當前各個程式正在執行什麼、正在等待什麼全部抓下來
SQL>oradebug dump systemstate 266 --oradebug dump systemdump 266;
SQL>--等上30秒到1分鐘
SQL>oradebug dump systemstate 266
###sysdba不可登陸時
sqlplus -prelim "/as sysdba"
SQL>oradebug setmypid
SQL>--其中266表示dump的級別，不用調整,即把系統當前各個程式正在執行什麼、正在等待什麼全部抓下來
SQL>oradebug dump systemstate 266
SQL>--等上30秒到1分鐘
SQL>oradebug dump systemstate 266
SQL> oradebug tracefile_name
/u02/app/oracle/diag/rdbms/lhrdb/lhrdb/trace/lhrdb_ora_46679.trc
----------HANGANALYZE 分析
SQL> alter session set events 'immediate trace name HANGANALYZE level 3';
或者：
SQL>ORADEBUG hanganalyze 3 --for 單例項
------for RAC 例項
SQL>ORADEBUG setmypid
SQL>ORADEBUG setinst all
SQL>ORADEBUG -g def hanganalyze 3
The levels are defined as follows:
10 Dump all processes (IGN state)
5 Level 4 + Dump all processes involved in wait chains (NLEAF state)
4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)
3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
1-2 Only HANGANALYZE output, no process dump at all
----------------------------------------------------------------------------------------------------- 層次查詢
SELECT LEVEL,
id,
parentid,
(lpad(' ', 8 * (LEVEL - 1)) || LEVEL || ':' || l.name) names,
substr(SYS_CONNECT_BY_PATH(NAME, '=>'), 3),
connect_by_root(NAME) root,
decode(LEVEL,
2,
NAME,
substr(SYS_CONNECT_BY_PATH(NAME, '=>'),
instr(SYS_CONNECT_BY_PATH(NAME, '=>'), '>', 1, 2) + 1,
(instr(SYS_CONNECT_BY_PATH(NAME, '=>'), '=', 1, 3) -
instr(SYS_CONNECT_BY_PATH(NAME, '=>'), '>', 1, 2) - 1))) root2,
decode(connect_by_isleaf, 1, 'Y', 0, 'N') is_leaf,
decode(connect_by_iscycle, 1, 'Y', 0, 'N') is_leaf
FROM xb_location l
START WITH l.parentid IS NULL
CONNECT BY nocycle PRIOR l.id = l.parentid;
----------------------------------------------------------------------------------------------------- 啟用系統觸發器
alter system set "_system_trig_enabled"=true; --預設
alter system reset "_system_trig_enabled" scope=spfile sid='*';
----------------------------------------------------------------------------------------------------- 閃回
select * from user_recyclebin t where t.original_name LIKE'TMP_AB%';
select * from dba_recyclebin;
--ddl
flashback table TMP_AB to before drop rename to old_t;
flashback table "BIN$B/HqKSpfWrvgU4I7qMATlg==$0" to before drop;
--dml
alter table old_t enable row movement;
flashback table old_t to timestamp to_date('2012/11/13 16:16:40','YYYY/MM/DD HH24:MI/SS');
--insert into xb_port
SELECT *
FROM xb_port AS OF TIMESTAMP TO_TIMESTAMP('2013-04-25 16:53:28', 'YYYY-MM-DD HH24:MI:SS')
WHERE id = 307247374 ;
commit;
SELECT timestamp_to_scn(TO_TIMESTAMP('2012-11-13 16:25:17',
'YYYY-MM-DD HH24:MI:SS')),
to_char(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) AS SCN
FROM dual;
----查詢
SELECT versions_starttime,
versions_startscn,
versions_endtime,
versions_endscn,
versions_xid,
versions_operation,
id,
NAME
FROM xb_location versions BETWEEN TIMESTAMP to_timestamp('2012-11-13 15:10:40', 'yyyy-mm-dd hh24:mi:ss') AND to_timestamp(to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
WHERE versions_xid IS NOT NULL
ORDER BY versions_starttime;
SELECT versions_starttime,
versions_startscn,
versions_endtime,
versions_endscn,
versions_xid,
versions_operation,
id,
NAME
FROM xb_location versions BETWEEN scn minvalue AND maxvalue
WHERE versions_xid IS NOT NULL
ORDER BY versions_starttime;
------------------------------------------------------- undo表空間
--1、系統段 2、非系統段 3、表空間離線後的defered段
SELECT d.segment_type,
COUNT(1)
FROM dba_segments d
GROUP BY d.segment_type;
select * from dba_segments d where d.segment_type in ('TYPE2 UNDO','ROLLBACK') ;
select * from dba_rollback_segs;
select * from dba_undo_extents;
select * from v$transaction;
select * from v$rollstat;
select * from v$rollname;
select * from dba_extents d where d.segment_name='_SYSSMU25_17381587$';
select * from v$undostat; ---mount狀態可查
--ssolderrcnt : snapshot too old error count
select status,count(*) from dba_rollback_segs group by status;
SELECT d.TABLESPACE_NAME,
d.STATUS,
SUM(bytes) / 1024 / 1024
FROM dba_undo_extents d
GROUP BY d.TABLESPACE_NAME,
d.status
ORDER BY d.TABLESPACE_NAME;
SELECT r.tablespace_name,
r.status "Status",
r.segment_name "Name",
s.extents "Extents",
TO_CHAR((s.bytes / 1024 / 1024), '99999990.000') "SizeM",
s.segment_type
FROM dba_rollback_segs r,
dba_segments s
WHERE r.segment_name = s.segment_name
AND s.segment_type IN ('ROLLBACK', 'TYPE2 UNDO')
ORDER BY r.tablespace_name,
5 DESC;
----估算undo需要的大小
SELECT (UR * (UPS * DBS)) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), (SELECT undoblks/((end_time-begin_time)*86400) AS UPS FROM v$undostat WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)), (SELECT block_size AS DBS FROM dba_tablespaces WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
-------------------- 已用大小
set line 9999
select s.sid,
s.serial#,
s.sql_id,
v.usn,
segment_name,
r.status,
v.rssize / 1024 / 1024 mb
From dba_rollback_segs r, v$rollstat v, v$transaction t, v$session s
Where r.segment_id = v.usn
and v.usn = t.xidusn
and t.addr = s.taddr
order by segment_name;
------undo總大小
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
COLUMN status FORMAT a9 HEADING 'Status'
COLUMN name FORMAT a30 HEADING 'Tablespace Name'
COLUMN type FORMAT a15 HEADING 'TS Type'
COLUMN extent_mgt FORMAT a10 HEADING 'Ext. Mgt.'
COLUMN segment_mgt FORMAT a10 HEADING 'Seg. Mgt.'
COLUMN ts_size FORMAT 9,999,999,999,999 HEADING 'Tablespace Size'
COLUMN used FORMAT 9,999,999,999,999 HEADING 'Used (in bytes)'
COLUMN free FORMAT 9,999,999,999,999 HEADING 'Free (in bytes)'
COLUMN pct_used FORMAT 999 HEADING 'Pct. Used'
SELECT
d.status status
, d.tablespace_name name
, d.contents type
, d.extent_management extent_mgt
, d.segment_space_management segment_mgt
, NVL(a.bytes, 0) ts_size
, NVL(a.bytes - NVL(f.bytes, 0), 0) used
, NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used
FROM
sys.dba_tablespaces d
, ( select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name
) a
, ( select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name
) f
WHERE
d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name like '%UNDO%'
ORDER BY
2;
--資料檔案管理引數： db_create_file_dest
CREATE TABLESPACE test DATAFILE SIZE 10m;
alter database datafile 3 resize 5G;
alter database tempfile 1 resize 2G;
alter database datafile 3 autoextend off;
alter database tempfile 1 autoextend off;
-------------- 重建undo表空間
create undo tablespace undotbs2 datafile '+DATA' size 100m reuse autoextend off;
alter system set undo_tablespace=undotbs2;
drop tablespace undotbs1 including contents and datafiles;
create undo tablespace undotbs1 datafile '+DATA' size 100m reuse autoextend off;
alter system set undo_tablespace=undotbs1;
alter database datafile 3 autoextend off;
alter database tempfile 1 autoextend off;
create undo tablespace undotbs2 datafile '+DATA' size 5M;
alter system set undo_tablespace=undotbs2;
alter tablespace undotabs2 retention guarantee;
select t.rowid,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno, t.owner,t.object_name from t ;
alter system dump datafile 4 block 6643;
select * from vw_mysession_lhr;
UBA : undo block address
select * from v$obsolete_parameter;
alter database create datafile 3 as '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf' size 50m;
------- undo
alter system set "_offline_rollback_segments"=true scope=spfile;
alter system set "_offline_rollback_segments"=false scope=spfile; ---預設
alter system reset "_offline_rollback_segments" scope=spfile sid='*';
*._offline_rollback_segments=('_SYSSMU154_3691636531$','_SYSSMU155_3686385895$','_SYSSMU156_3796802683$','_SYSSMU157_2723916652$','_SYSSMU158_1435464080$')
_offline_rollback_segment='_SYSSMU3$'
_newsort_enabled --排序
-------- 如果undo為recover狀態的話還需要加如下引數
alter system set "_corrupted_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$' scope=spfile;
alter system reset "_corrupted_rollback_segments" scope=spfile sid='*';
alter system set "_corrupted_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$','_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$' scope=spfile;
*._corrupted_rollback_segments=('_SYSSMU10_3271578125','_SYSSMU11_125382609','_SYSSMU1_1240252155','_SYSSMU12_2245433549','_SYSSMU13_3242268464','_SYSSMU14_44821983','_SYSSMU15_1872739176','_SYSSMU16_1376564431','_SYSSMU17_1839632768','_SYSSMU18_3088942417','_SYSSMU19_2867910983','_SYSSMU20_948290921','_SYSSMU2_111974964','_SYSSMU3_4004931649','_SYSSMU4_1126976075','_SYSSMU5_2968973961','_SYSSMU6_2060978448','_SYSSMU7_4222772309','_SYSSMU8_3612859353','_SYSSMU9_2370500926')
drop rollback segment "drop rollback segment";
drop rollback segment "_SYSSMU154_3691636531$";
---- 查詢undo段
strings /u01/app/oracle/oradata/ora11g/system01.dbf | grep _SYSSMU | sort -u >/tmp/system.txt
more /tmp/system.txt
注意：透過system01.dbf查出了，正在使用的undo segment，以上按使用時間做了排序，注意只選擇那些排在最前面的（相同回滾段）；預設每個undo tablespace 會應用10個undo segments。
------- 不能建立undo檔案（ORA-01178錯誤），無備份的情況下采用隱含引數啟動資料庫
set line 9999
col name format a100
select file#, name,status,enabled from v$datafile;
select * from v$recover_file;
alter system set undo_management=manual scope=spfile;
alter database datafile 3 offline;
alter system set undo_tablespace=SYSTEM scope=spfile;
alter system set "_offline_rollback_segments"=true scope=spfile;
! strings '/u01/app/oracle/oradata/orcltest/system01.dbf' | grep _SYSSMU | sort -u
alter system set
"_corrupted_rollback_segments"='_SYSSMU3_4004931649$','_SYSSMU3_4160240979$','_SYSSMU34_2573821980$','_SYSSMU35_3476245049$','_SYSSMU36_353473384$','_SYSSMU37_682878819$','_SYSSMU38_2521239011$','_SYSSMU39_1467520375$','_SYSSMU40_753689919$','_SYSSMU4_1126976075$','_SYSSMU4_348804819$','_SYSSMU5_2968973961$','_SYSSMU5_4011504098$','_SYSSMU6_2060978448$','_SYSSMU6_3654194381$','_SYSSMU7_4222772309$','_SYSSMU7_894058185$','_SYSSMU8_3612859353$','_SYSSMU8_87803851$','_SYSSMU9_2370500926$','_SYSSMU9_2370500926$','_SYSSMU9_3945653786$' scope=spfile;
shutdown immediate;
startup mount;
alter database open;
select segment_name,status,tablespace_name from dba_rollback_segs;
drop tablespace UNDOTBS1;
create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/orcltest/undotbs01.dbf' size 50m autoextend on;
alter system set undo_tablespace=UNDOTBS1 scope=spfile;
alter system set undo_management=auto scope=spfile;
alter system reset "_offline_rollback_segments" scope=spfile sid='*';
alter system reset "_corrupted_rollback_segments" scope=spfile sid='*';
shutdown immediate;
startup
---注意回滾段的命名規範：
11g： _SYSSMU1_1189172979$、 _SYSSMU2_1189172979$ 。。。_SYSSMU10_1189172979$
10g、9i：_SYSSMU1$、_SYSSMU2$、_SYSSMU3$ 。。。。_SYSSMU10$
8i 為rollbackspace 即RBS空間：RBS0、RBS1、RBS2 。。。。RBS6
7.3 ：RB1、RB2.。。。。RB6
*.LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))'
*.LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))';
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))';
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))';
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))','(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522)))';
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))','(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523))';
ALTER SYSTEM REGISTER;
------------ 資料檔案自動擴充套件
alter database datafile 5 autoextend on next 5M;
如果是bigfile可以採用： ALTER TABLESPACE TBS2 AUTOEXTEND ON NEXT 20G;
修改表空間資料檔案大小為不限制的語句為：
alter database datafile '/oradata/orcl/demo01.dbf' autoextend on maxsize unlimited;
建立表空間資料檔案大小為不限制的語句為：
create tablespace demo2 datafile '/oradata/orcl/demo201.dbf' size 10M autoextend on maxsize unlimited;
----取消已有資料檔案的自動增長方式
alter database datafile 'i:\oracle\oradata\dmusertbs01.dbf' autoextend off;
------------ 根據檔案號和塊號查詢資料庫物件
SELECT tablespace_name,
segment_type,
owner,
segment_name,
partition_name
FROM dba_extents
WHERE file_id = &file_id
AND &block_id BETWEEN block_id AND block_id + blocks - 1
;
SELECT ROWID,
dbms_rowid.rowid_object(ROWID) object_id,
dbms_rowid.rowid_relative_fno(ROWID) file_id,
dbms_rowid.rowid_block_number(ROWID) block_id,
d.*
FROM scott.SALGRADE d
WHERE dbms_rowid.rowid_block_number(ROWID) = 163
AND dbms_rowid.rowid_relative_fno(ROWID) = 4;
SELECT DBMS_ROWID.ROWID_CREATE(1,
(SELECT DATA_OBJECT_ID
FROM DBA_OBJECTS
WHERE OBJECT_ID = ROW_WAIT_OBJ#),
ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#,
ROW_WAIT_ROW#),
A.ROW_WAIT_OBJ#,
A.ROW_WAIT_FILE#,
A.ROW_WAIT_BLOCK#,
A.ROW_WAIT_ROW#,
(SELECT D.OWNER || '.' || D.OBJECT_NAME
FROM DBA_OBJECTS D
WHERE OBJECT_ID = ROW_WAIT_OBJ#) OBJECT_NAME
FROM V$SESSION A
WHERE A.ROW_WAIT_OBJ# <> -1;
SELECT * FROM SYS.COM$ A WHERE A.ROWID='AAAACJAABAAAARGAAA';
---------------------- 刪除主鍵及主鍵索引
alter table table_name drop primary key cascade drop index;
alter table table_name drop constraint constraint_name cascade drop index;
--------------------------------------------------------- awr
----- 生成awr
select * from table(dbms_workload_repository.awr_report_html(3424884828,1,1161,1165));
@$ORACLE_HOME/rdbms/admin/awrrpt.sql;
----檢視
select * from DBA_HIST_WR_CONTROL;
select * from DBA_HIST_SNAPSHOT;
select * from DBA_HIST_ACTIVE_SESS_HISTORY;
select * from DBA_HIST_ASH_SNAPSHOT;
select * from DBA_HIST_SEG_STAT;
select * from DBA_HIST_SQLBIND;
select * from DBA_HIST_SQLSTAT;
select * from DBA_HIST_SQLTEXT;
select * from DBA_HIST_SQL_BIND_METADATA;
select * from DBA_HIST_SQL_PLAN;
--------- sql部分
select &begin_snap || '~' || &end_snap snap_id_range,
(SELECT round(sum(db_time) / 1000000 / 60, 2) db_time_m
FROM (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time
from dba_hist_sys_time_model a, dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.stat_name = 'DB time'
and a.snap_id between &begin_snap and &end_snap)
where db_time IS NOT NULL) "db_time(m)",
round(nvl((sqt.elap / 1000000), to_number(null)), 2) "Elapsed Time (s)",
round(nvl((sqt.cput / 1000000), to_number(null)), 2) "CPU Time (s)",
round(nvl((sqt.iowait_delta / 1000000), to_number(null)), 2) "User I/O Time (s)",
round(nvl((sqt.buffer_gets_delta), to_number(null)), 2) "Buffer Gets",
round(nvl((sqt.disk_reads_delta), to_number(null)), 2) "Physical Reads",
round(nvl((sqt.rows_processed_delta), to_number(null)), 2) "Rows Processed",
round(nvl((sqt.parse_calls_delta), to_number(null)), 2) "Parse Calls",
sqt.exec executions,
round(decode(sqt.exec,
0,
to_number(null),
(sqt.elap / sqt.exec / 1000000)),
2) "Elapsed Time per Exec (s)",
round(decode(sqt.exec,
0,
to_number(null),
(sqt.cput / sqt.exec / 1000000)),
2) "CPU per Exec (s)",
round(decode(sqt.exec,
0,
to_number(null),
(sqt.iowait_delta / sqt.exec / 1000000)),
2) "UIO per Exec (s)",
round(sqt.cput * 100 / sqt.elap, 2) "%CPU",
round(sqt.iowait_delta * 100 / sqt.elap, 2) "%IO",
round(sqt.elap * 100 /
(SELECT sum(db_time)
FROM (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time
from dba_hist_sys_time_model a, dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.stat_name = 'DB time'
and a.snap_id between &begin_snap and &end_snap)
where db_time IS NOT NULL),
2) "elapsed/dbtime",
sqt.sql_id,
parsing_schema_name,
(decode(sqt.module, null, null, sqt.module)) module,
nvl((select dbms_lob.substr(st.sql_text, 2000, 1)
from dba_hist_sqltext st
WHERE st.sql_id = sqt.sql_id
and st.dbid = sqt.dbid),
(' ** SQL Text Not Available ** ')) sql_text
from (select sql_id,
a.dbid,
a.parsing_schema_name,
max(module || '--' || a.action) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec,
SUM(a.iowait_delta) iowait_delta,
sum(a.buffer_gets_delta) buffer_gets_delta,
sum(a.disk_reads_delta) disk_reads_delta,
sum(a.rows_processed_delta) rows_processed_delta,
sum(a.parse_calls_delta) parse_calls_delta
from dba_hist_sqlstat a
where &begin_snap < snap_id
and snap_id <= &end_snap
group by sql_id, parsing_schema_name, a.dbid) sqt
order by nvl(sqt.elap, -1) desc, sqt.sql_id
;
--------- 資訊
select s.snap_date,
snap_time_range,
t.snap_id + 1 snap_id,
decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
startup_time,
to_char(round(s.seconds / 60, 2)) "elapse(min)",
round(t.db_time / 1000000 / 60, 2) "DB time(min)",
s.redosize redo,
round(s.redosize / s.seconds, 2) "redo/s",
round(s.redosize / s.transactions, 2) "redo/t",
s.logicalreads logical,
round(s.logicalreads / s.seconds, 2) "logical/s",
round(s.logicalreads / s.transactions, 2) "logical/t",
physicalreads physical,
round(s.physicalreads / s.seconds, 2) "phy/s",
round(s.physicalreads / s.transactions, 2) "phy/t",
s.executes execs,
round(s.executes / s.seconds, 2) "execs/s",
round(s.executes / s.transactions, 2) "execs/t",
s.parse,
round(s.parse / s.seconds, 2) "parse/s",
round(s.parse / s.transactions, 2) "parse/t",
s.hardparse,
round(s.hardparse / s.seconds, 2) "hardparse/s",
round(s.hardparse / s.transactions, 2) "hardparse/t",
s.transactions trans,
round(s.transactions / s.seconds, 2) "trans/s"
from (select curr_redo - last_redo redosize,
curr_logicalreads - last_logicalreads logicalreads,
curr_physicalreads - last_physicalreads physicalreads,
curr_executes - last_executes executes,
curr_parse - last_parse parse,
curr_hardparse - last_hardparse hardparse,
curr_transactions - last_transactions transactions,
round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
to_char(currtime, 'yyyy-mm-dd') snap_date,
to_char(currtime, 'hh24:mi') currtime,
to_char(lasttime, 'YYYY-MM-DD HH24:MI') || '~' ||
to_char(currtime, 'YYYY-MM-DD HH24:MI') snap_time_range,
currsnap_id endsnap_id,
to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
from (select a.redo last_redo,
a.logicalreads last_logicalreads,
a.physicalreads last_physicalreads,
a.executes last_executes,
a.parse last_parse,
a.hardparse last_hardparse,
a.transactions last_transactions,
lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
b.end_interval_time lasttime,
lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
b.startup_time
from (select snap_id,
dbid,
instance_number,
sum(decode(stat_name, 'redo size', value, 0)) redo,
sum(decode(stat_name,
'session logical reads',
value,
0)) logicalreads,
sum(decode(stat_name,
'physical reads',
value,
0)) physicalreads,
sum(decode(stat_name, 'execute count', value, 0)) executes,
sum(decode(stat_name,
'parse count (total)',
value,
0)) parse,
sum(decode(stat_name,
'parse count (hard)',
value,
0)) hardparse,
sum(decode(stat_name,
'user rollbacks',
value,
'user commits',
value,
0)) transactions
from dba_hist_sysstat
where stat_name in
('redo size',
'session logical reads',
'physical reads',
'execute count',
'user rollbacks',
'user commits',
'parse count (hard)',
'parse count (total)')
group by snap_id, dbid, instance_number) a,
dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
order by end_interval_time)) s,
(select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id,
b.snap_id
from dba_hist_sys_time_model a, dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.stat_name = 'DB time') t
where s.endsnap_id = t.endsnap_id
order by s.snap_date desc, snap_id desc, time asc;
----主機資訊
SELECT s.snap_id,
DB_NAME,
s.dbid,
INSTANCE_NAME,
s.instance_number,
s.startup_time,
Version Release,
PARALLEL RAC,
HOST_NAME,
di.platform_name,
v.cpus CPUS,
v.cores,
v.sockets,
v.Memory "Memory (GB)"
FROM DBA_HIST_DATABASE_INSTANCE di,
DBA_HIST_SNAPSHOT s,
(SELECT snap_id,
dbid,
instance_number,
SUM(CPUs) CPUs,
SUM(Cores) Cores,
SUM(Sockets) Sockets,
SUM(Memory) Memory
FROM (SELECT o.snap_id,
o.dbid,
o.instance_number,
decode(o.stat_name, 'NUM_CPUS', o.value) CPUs,
decode(o.stat_name, 'NUM_CPU_CORES', o.value) Cores,
decode(o.stat_name, 'NUM_CPU_SOCKETS', o.value) Sockets,
decode(o.stat_name,
'PHYSICAL_MEMORY_BYTES',
trunc(o.value / 1024 / 1024 / 1024, 2)) Memory
FROM dba_hist_osstat o
WHERE o.stat_name IN
('NUM_CPUS',
'NUM_CPU_CORES',
'NUM_CPU_SOCKETS',
'PHYSICAL_MEMORY_BYTES'))
GROUP BY snap_id,
dbid,
instance_number) v
WHERE s.instance_number = di.instance_number
AND s.startup_time = di.startup_time
AND s.dbid = di.dbid
AND s.snap_id = v.snap_id
AND s.dbid = s.dbid
AND s.instance_number = v.instance_number;
------------------------------------------------------------------------------------------ 臨時表
基於事務： create global temporary table lhr.cgtt_temp_sw on commit delete rows as select * from dba_objects;
建立索引：create index ind_cgtt_object_ID on cgtt_temp_sw(object_ID);
基於會話： create global temporary table lhr.cgtt_temp_hh on commit preserve rows as select * from dba_objects;
建立索引：需重開會話： create index ind_cgtt_object_ID2 on cgtt_temp_hh(object_ID);
select * from VW_TEMP_OBJECT_LHR;
Select se.username,
se.sid,
su.extents,
(su.blocks *
to_number((select rtrim(value)
from v$parameter p
WHERE p.NAME = 'db_block_size'))) / 1024 / 1024 as Size_m,
tablespace,
segtype,
(SELECT a.SQL_TEXT
FROM v$sql a
WHERE a.SQL_ID = su.SQL_ID
and rownum = 1) SQL_TEXT
from v$sort_usage su, v$session se
where su.session_addr = se.saddr
order by se.username, se.sid;
-------------------------------------- catalog庫
SQL> create tablespace rman_ts datafile '/lhrdata/u01/app/oracle/oradata/orcllinux/rman.dbf' size 1G;
表空間已建立。
SQL> create user rc identified by lhr default tablespace rman_ts quota unlimited on rman_ts;
使用者已建立。
SQL> grant recovery_catalog_owner to rc;---包含了connect的角色許可權
授權成功。
SQL> grant RESOURCE to rc;
授權成功。
SQL> HOST
[oracle@lhr_linux ~]$ rman catalog rc/lhr
恢復管理器: Release 11.2.0.1.0 - Production on 星期四 4月 10 15:08:22 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
連線到恢復目錄資料庫
RMAN> create catalog tablespace rman_ts;
恢復目錄已建立
RMAN> connect target /
連線到目標資料庫: ORCLLINU (DBID=534927627)
RMAN> register database;
註冊在恢復目錄中的資料庫
正在啟動全部恢復目錄的 resync
完成全部 resync
RMAN> exit
---------- 使用者profile
alter user lhr profile default;
alter system set resource_limit=true;
alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;
alter profile default limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
/oracle/app/oraInventory/ContentsXML/inventory.xml
oracle執行：$ORACLE_HOME/oui/bin/attachHome.sh
-------------------------------------------- 解除安裝軟體 GRID
kill -9 `ps -ef|grep d.bin| grep -v grep | awk '{print $2}'`
解除安裝GRID軟體，grid使用者執行:$ORACLE_HOME/deinstall/deinstall
解除安裝ORACLE軟體，oracle使用者執行:$ORACLE_HOME/deinstall/deinstall
在Linux下手工解除安裝RAC的步驟：
①　rm -rf /etc/ora*
②　rm -rf /var/tmp/.oracle
③　修改/etc/inittab刪除以下三行
h1:2:respawn:/etc/init.evmd run >/dev/null 2>&1 </dev/null
h2:2:respawn:/etc/init.cssd fatal >/dev/null 2>&1 </dev/null
h3:2:respawn:/etc/init.crsd run >/dev/null 2>&1 </dev/null
④　rm -rf /tmp/*
⑤　rm -rf /u01/app/grid/* ---刪除GRID目錄
⑥　rm -rf /u01/app/oracle/* --刪除ORACLE目錄
⑦　rm -rf /u01/app/oraInventory/*
⑧　清除OCR、Voting及資料庫使用過的磁碟
dd if=/dev/zero of=/dev/raw/raw1 bs=104857600 count=1
dd if=/dev/zero of=/dev/raw/raw2 bs=104857600 count=1
最後，重啟2個節點*/
-------重建路徑
mkdir -p /u01/app/oracle
mkdir -p /u01/app/grid
mkdir -p /u01/app/12.1.0/grid
mkdir -p /u01/app/oracle/product/12.1.0/dbhome_1
chown -R grid:oinstall /u01/app/grid
chown -R grid:oinstall /u01/app/12.1.0
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01
mkdir -p /u01/app/oraInventory
chown -R grid:oinstall /u01/app/oraInventory
chmod -R 775 /u01/app/oraInventory
在Windows下手工解除安裝RAC的步驟：
①　開始->設定->控制皮膚->管理工具->服務，或執行services.msc開啟服務，停止所有Oracle服務
②　刪除Oracle和GRID的安裝目錄
③　刪除C:\Program Files\Oracle目錄
④　刪除C:\windows\temp和C:\temp以及C:\Users\Administrator\Oracle下的檔案
⑤　執行regedit，開啟登錄檔編輯器，選擇HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE，刪除該項
⑥　執行regedit，開啟登錄檔編輯器，選擇HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services，滾動這個列表，刪除所有Oracle開頭的項
⑦　執行regedit，開啟登錄檔編輯器，選擇HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application，刪除所有Oracle入口
⑧　開始->設定->控制皮膚->系統->高階->環境變數，刪除環境變數CLASSPATH和PATH中有關Oracle的設定
⑨　從桌面上、STARTUP（啟動）組、程式選單中，刪除所有有關Oracle的組和圖示
⑩　重新啟動計算機，重起後才能完全刪除Oracle所在目錄
若個別檔案不能刪除，則說明該檔案與某個Windows服務相關聯，可以先把相關聯的服務停止後再刪除。
-- 加入常用命令
vi /etc/profile
export GRID_HOME=/u01/app/12.1.0/grid
export PATH=$PATH:$GRID_HOME/bin
---------------- 重新執行root.sh
---kill -9 `ps -ef|grep d.bin| grep -v grep | awk '{print $2}'`
---$ORACLE_HOME 為 GRID_HOME的路徑,執行之前最好先手動把資料庫資源關閉
日誌地址：$ORACLE_HOME/cfgtoollogs/crsconfig/
重置的日誌檔案：hadelete.log
root.sh指令碼日誌：rootcrs_rac2.log
--------------① 指令碼方式
---執行失敗，重新執行root.sh指令碼
$ORACLE_HOME/crs/install/crsconfig_params
$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose
--$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose -lastnode -keepdg
dd if=/dev/zero of=/dev/rhdiskN bs=1024k count=1024
lquerypv -h /dev/rhdisk5
$ORACLE_HOME/root.sh
$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose
--$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose -lastnode -keepdg
$ORACLE_HOME/root.sh
---$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose執行完成之後需要刪除如下的檔案
ls -l $ORACLE_BASE/Clusterware/ckptGridHA* */
find $ORACLE_HOME/gpnp/* -type f */
find $ORACLE_HOME/gpnp/* -type f -exec rm -rf {} \; */
--------------② 介面方式
---------------刪除兩節點crsconfig_params中的DATA1和磁碟介面方式
$ORACLE_HOME/crs/install/crsconfig_params
ASM_DISK_GROUP=DATA1
ASM_DISKS=/dev/rhdisk5
--root
$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose
-- GRID
export DISPLAY=22.188.216.132:0.0
$ORACLE_HOME/crs/config/config.sh
CRS-4124: Oracle High Availability Services startup failed. -- 報錯
CRS-4000: Command Start failed, or completed with errors.
ohasd failed to start: Inappropriate ioctl for device
ohasd failed to start: Inappropriate ioctl for device at /u01/app/11.2.0/grid/crs/install/roothas.pl line 296.
/bin/dd if=/var/tmp/.oracle/npohasd of=/dev/null bs=1024 count=1
/nfs/software/db/install/clone/sh/11g/grid/runcluvfy.sh stage -pre crsinst -n ZFLHRDB1,ZFLHRDB2 -verbose -fixup
$ORACLE_HOME/bin/cluvfy stage -pre crsinst -n all -verbose -fixup
find . -name runcluvfy.sh
---GRID_HOME許可權修復
方法1：11gR2可以deconfig crs的配置，然後重新跑root.sh即可。重新跑root.sh指令碼並不影響資料庫，所以無需擔心（個人推薦的一種方式）.
$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose
$ORACLE_HOME/root.sh
方法2：根據Mos文件提供的建議透過 $GRID_HOME/crs/install/rootcrs.pl -init 或 roothas.pl -init進行解決. rootcrs.pl –init是在PSU>11.2.0.3.6下執行的，如果PSU<11.2.0.3.6可以執行如下兩條命令來實現同樣的效果
<GRID_HOME>/crs/install/rootcrs.pl -unlock
<GRID_HOME>/crs/install/rootcrs.pl -patch
For 11.2:
For clustered Grid Infrastructure, as root user
# cd <GRID_HOME>/crs/install/
# ./rootcrs.pl -init
For Standalone Grid Infrastructure, as root user
# cd <GRID_HOME>/crs/install/
# ./roothas.pl -init
For 12c:
For clustered Grid Infrastructure, as root user
# cd <GRID_HOME>/crs/install/
# ./rootcrs.sh -init
For Standalone Grid Infrastructure, as root user
# cd <GRID_HOME>/crs/install/
# ./roothas.sh -init
---檢視psu
/nfs/software/db/install/chk/chkora.sh
opatch lsinventory -bugs_fixed | grep 'PSU'
opatch lsinv
[ZFCASSDB1:grid]:/home/grid>opatch lspatches
13343438;Database Patch Set Update : 11.2.0.3.1 (13343438)
13348650;Grid Infrastructure Patch Set Update : 11.2.0.3.1 (13348650)
[ZFCASSDB1:grid]:/home/grid>
col action_time for a30
col action for a10
col namespace for a10
col version for a10
col bundle_series for a10
col comments for a30
SELECT to_char(action_time, 'YYYY-MM-DD HH24:MI:SS') action_time,
action,
namespace,
version,
id,
bundle_series,
comments
FROM dba_registry_history D;
select action,comments from registry$history;
---grid和oracle分別回滾
$ORACLE_HOME/OPatch/opatch rollback -local -id 13348650 -oh /oracle/app/oracle/product/11.2.0/db
----------------------------- OCR備份
--邏輯備份恢復
ocrconfig -export /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/export_asm.bak
ocrconfig -import /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/export_asm_lhr.bak
crsctl stop crs
crsctl start crs -excl -nocrs
ocrconfig -import /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/export_asm_lhr.bak
crsctl start crs
--物理備份恢復
ocrconfig -manualbackup
ocrconfig -showbackup
cluvfy comp ocr -n all -verbose
cluvfy comp olr -verbose
crsctl stop crs -f
crsctl start crs -excl -nocrs
crsctl stop resource ora.crsd -init
ocrconfig -restore /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/backup_20160701_152358.ocr
crsctl stop has -f
crsctl start crs
crsctl query css votedisk
--------dd備份恢復OCR 注：11g不推薦使用dd來進行備份恢復，盤頭一般是前4K
--備份表決磁碟：
dd if=/dev/raw/raw3 of=/tmp/votedisk_lhr.bak bs=1024k count=4
--恢復表決磁碟：
dd if=/tmp/votedisk_lhr.bak of=/dev/raw/raw3 bs=1024k count=4
----------kfed修復磁碟頭
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
----md_backup修復磁碟頭
asmcmd md_backup /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/asm_md_backup.bak
asmcmd md_restore /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/asm_md_backup.bak
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 /asm_rhdisk2_dd.bak
ASMCMD [+] > md_backup /rman/asm_md.bak
dd if=/dev/zero of=/dev/rhdisk2 bs=1024 count=4
crsctl stop has -f
crsctl start has
ASMCMD [+] > startup force nomount;
ASMCMD [+] > md_restore /rman/asm_md.bak
-------- OLR的備份恢復
<GI_HOME>/bin/ocrconfig -local -manualbackup
<GI_HOME>/bin/ocrconfig -local -showbackup
ps -ef| grep ohasd.bin
<GI_HOME>/bin/crsctl stop crs -f <========= for GI Cluster
<GI_HOME>/bin/crsctl stop has <========= for GI Standalone
<GI_HOME>/bin/ocrconfig -local -restore <olr-backup>
<GI_HOME>/bin/crsctl start crs <========= for GI Cluster
<GI_HOME>/bin/crsctl start has <========= for GI Standalone, this must be done as grid user.
vi crsstat_lhr.sh
awk 'BEGIN {printf "%-26s %-26s %-10s %-10s %-10s \n","Name ","Type ","Target ","State ","Host "; printf "%-30s %-26s %-10s %-10s %-10s\n","----------------------------------------","--------------------------","----------", "---------","----------";}'
crs_stat | awk 'BEGIN { FS="=| ";state = 0;} $1~/NAME/ {appname = $2; state=1}; state == 0 {next;} $1~/TYPE/ && state == 1 {apptype = $2; state=2;} $1~/TARGET/ && state == 2 {apptarget = $2; state=3;} $1~/STATE/ && state == 3 {appstate = $2; apphost = $4; state=4;} state == 4 {printf "%-40s %-26s %-10s %-10s %-10s\n", appname,apptype,apptarget,appstate,apphost; state=0;}'
-------------------------------------------------------------- OEM
--重建：
emca -config dbcontrol db -repos recreate
emca -config dbcontrol db -repos recreate -cluster
http://192.168.59.130:1158/em/
https://192.168.59.128:1158/em/
日誌：
$ORACLE_HOME/$hostname_$oracle_sid/sysman/log
4.安裝過程中出現問題的時候認真檢視日誌，安裝日誌路徑：$ORACLE_HOME/cfgtoollogs\emca\
5.OEM執行日誌：$ORACLE_HOME/$hostname_$oracle_sid/sysman/log
6.建議重新建立，在oracle使用者下：
單機： emca -config dbcontrol db -repos recreate
叢集： emca -config dbcontrol db -repos recreate -cluster
若是叢集環境，則在建立之前先在grid使用者下執行如下程式碼：
-----sqlplus / as sysasm ASM例項 GRID 使用者下執行
SYS@+ASM1> create user asmsnmp identified by xxx;
SYS@+ASM1> grant sysdba to asmsnmp;
SYS@+ASM1> alter user asmsnmp identified by xxx;
SYS@+ASM1> alter system set remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ZFZHLHRDB-scan)(PORT=1521))))' sid='*';
SYS@+ASM1> alter system register;
7.啟動和關閉維護命令，oracle使用者下：export ORACLE_UNQNAME=$DB_UNIQUE_NAME ,資料庫唯一名，程式：ps -ef| grep em
啟動： emctl stop dbconsole
關閉： emctl stop dbconsole
執行狀態：emctl status dbconsole
-------------------------------------------- dbca 靜默建庫 windows 和 linux 命令一樣
------歸檔
vi $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc
修改為：<archiveLogMode>true</archiveLogMode>
[oracle@rhel6_lhr ~]$ strings $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc | grep -i arch
<archiveLogMode>false</archiveLogMode>
----靜默安裝資料庫日誌路徑：
11g:$ORACLE_BASE/cfgtoollogs/dbca
10g:$ORACLE_HOME/cfgtoollogs/dbca
---dbca -silent整理 \ 後不能包含空格
dbca -silent -deleteDatabase -sourceDB mydb
dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-gdbname mydb -sid mydb \
-sysPassword oracle -systemPassword oracle \
-datafileDestination 'DATA/' -recoveryAreaDestination 'DATA/' \
-redoLogFileSize 50 \
-storageType ASM -asmsnmpPassword oracle -diskGroupName 'DATA' \
-characterset AL32UTF8 -nationalCharacterSet AL16UTF16 \
-sampleSchema true \
-automaticMemoryManagement true -totalMemory 2048 \
-databaseType OLTP \
-emConfiguration NONE \
-nodeinfo ZFZHLHRDB1,ZFZHLHRDB2
dbca -silent -deleteDatabase -sourceDB mydb
dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-gdbname mydb -sid mydb \
-sysPassword oracle -systemPassword oracle \
-datafileDestination '/u05/app/oracle' -recoveryAreaDestination '/u05/app/oracle' \
-storageType FS \
-characterset AL32UTF8 -nationalCharacterSet AL16UTF16 \
-sampleSchema false \
-memoryPercentage 10 \
-databaseType OLTP \
-emConfiguration NONE
---10g
dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-gdbname mydg -sid mydg \
-sysPassword lhr -systemPassword lhr \
-datafileDestination '/cds/oradata' -recoveryAreaDestination '/cds/oradata' \
-storageType FS \
-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
-sampleSchema true \
-memoryPercentage 20 \
-databaseType OLTP \
-emConfiguration NONE
--12C
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname oradb.example.com -sid oradb -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration NONE
dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-gdbname lhrdb -sid lhrdb \
-createAsContainerDatabase false \
-sysPassword lhr -systemPassword lhr -serviceUserPassword lhr \
-datafileDestination '/u01/app/oracle' -recoveryAreaDestination '/u01/app/oracle' \
-storageType FS \
-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
-sampleSchema true \
-memoryPercentage 30 \
-databaseType OLTP \
-emConfiguration NONE
--12C rac
dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-gdbname lhrrac -sid lhrrac \
-createAsContainerDatabase false \
-sysPassword lhr -systemPassword lhr -serviceUserPassword lhr \
-datafileDestination 'DATA/' -recoveryAreaDestination 'FRA/' \
-storageType ASM -asmsnmpPassword oracle -diskGroupName 'DATA' \
-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
-sampleSchema true \
-memoryPercentage 30 \
-databaseType OLTP \
-emConfiguration NONE \
-nodeinfo raclhr-12cR1-N1,raclhr-12cR1-N2
--11g
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -redoLogFileSize 50 -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -characterSet AL32UTF8
----ASM 儲存單例項
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination 'TEST/' -storageType ASM -asmsnmpPassword lhr -diskGroupName 'DATA' -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true
---預設
--- 閃回恢復區建立
--storageType FS
--sampleSchema 預設建立
--em 預設不建立
--10g
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -databaseType OLTP -emConfiguration NONE
----------------根據模板檔案進行安裝
dbca -silent -responseFile $ORACLE_HOME/assistants/dbca/dbca.rsp
dbca -silent -cloneTemplate -responseFile $ORACLE_HOME/assistants/dbca/dbca.rsp -gdbName orcltest -sid orcltest -datafileDestination /u01/app/oracle/oradata
-------------------- 根據資料庫生成不帶資料檔案的模板
dbca -silent -createTemplateFromDB -sourceDB 192.168.59.130:1521:ora10g -templateName dbtemplate_ora10g2_lhr -sysDBAUserName sys -sysDBAPassword lhr
-------------------- 根據資料庫生成帶資料檔案的模板
dbca -silent -createCloneTemplate -sourceDB orcltest -sysDBAUserName lhr -sysDBAPassword lhr -templateName dbtemplate_orcltest_lhr -datafileJarLocation
-------------------- 利用帶資料檔案的模板生成克隆資料庫
dbca -silent -createDatabase -templateName dbtemplate_orcltest_lhr.dbc -gdbname orcl22 -sid orcl22 -sysPassword lhr -systemPassword lhr -datafileJarLocation $ORACLE_HOME/assistants/dbca/templates -datafileDestination /u01/app/oracle/oradata -responseFile NO_VALUE -characterset ZHS16GBK
-------------------- 利用不帶資料檔案的模板生成新的資料庫慢，不推薦
----dbca -silent -createDatabase -templateName New_Database.dbt -gdbname test33 -sid test33 -datafileDestination /u01/app/oracle/oradata -responseFile NO_VALUE -characterset ZHS16GBK
---------------- 刪除資料庫
dbca -silent -deleteDatabase -sourceDB orclbb -sysDBAUserName sys -sysDBAPassword lhr
------------- linux、AIX下 rac 資料庫的建立
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname jmrac -sid jmrac -sysPassword lhr -systemPassword lhr -datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination 'ARCH/' -storageType ASM -asmsnmpPassword lhr -diskGroupName 'DATA' -responseFile NO_VALUE -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true -totalMemory 500 -nodeinfo node1,node2
----windows下建立rac庫，注意引數 diskGroupName 為 DATA ，不能帶有引號
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname winrac -sid winrac -sysPassword lhr -systemPassword lhr -datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination 'FRA/' -storageType ASM -asmsnmpPassword lhr -diskGroupName DATA -responseFile NO_VALUE -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true -totalMemory 300 -nodeinfo rac1,rac2
----- 或者：
[oracle@node1 dbca]$ more $ORACLE_HOME/assistants/dbca/dbca_rac.rsp
[GENERAL]
RESPONSEFILE_VERSION = "11.2.0"
OPERATION_TYPE = "createDatabase"
[CREATEDATABASE]
GDBNAME = "myrac"
SID = "myrac"
NODELIST=node1,node2
TEMPLATENAME = "General_Purpose.dbc"
SYSPASSWORD = "lhr"
SYSTEMPASSWORD = "lhr"
SYSMANPASSWORD = "lhr"
DBSNMPPASSWORD = "lhr"
STORAGETYPE=ASM
DISKGROUPNAME=DATA
ASMSNMP_PASSWORD="lhr"
RECOVERYGROUPNAME=ARCH
CHARACTERSET = "ZHS16GBK"
NATIONALCHARACTERSET= "UTF8"
[oracle@node1 dbca]$ dbca -silent -responseFile $ORACLE_HOME/assistants/dbca/dbca_rac.rsp
set line 9999
col HOST_NAME format a10　
select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,ACTIVE_STATE,INSTANCE_ROLE,DATABASE_STATUS from gv$INSTANCE;
select INST_ID,name , open_mode, log_mode,force_logging from gv$database;
------------------------------------------------------------------- 靜默安裝
---------- 單例項資料庫安裝
vi /tmp/database/response/db_install.rsp
ORACLE_HOSTNAME=192.168.59.129
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u02/app/oracle/oraInventory
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1
ORACLE_BASE=/u02/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.EEOptionsSelection=false
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper
oracle.install.db.isRACOneInstall=false
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
oracle.installer.autoupdates.option=SKIP_UPDATES
----軟體安裝
./runInstaller -silent -noconfig -responseFile /tmp/database/response/db_install.rsp -ignoreSysPrereqs -ignorePrereq
----靜默建立asm例項
/u01/app/11.2.0/grid/bin/asmca -silent -configureASM -sysAsmPassword lhr -asmsnmpPassword lhr -diskGroupName OCR -diskList /dev/rhdisk20 -redundancy EXTERNAL
----監聽配置
$ORACLE_HOME/bin/netca /silent /responsefile /u01/database/netca.rsp
netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp
$ORACLE_HOME/bin/netca /silent /responsefile /u01/database/netca.rsp
netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp
netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp -instype custom -listener LISTENER_LHR
crsctl delete resource ora.LISTENER.lsnr -f
rm $ORACLE_HOME/network/admin/listener.ora
---------------------------------- 單例項grid安裝
/softtmp/grid/runInstaller -silent -force -noconfig -IgnoreSysPreReqs -ignorePrereq -showProgress \
ORACLE_HOSTNAME=ZFFR4CB2101 \
INVENTORY_LOCATION=/u01/app/oraInventory \
SELECTED_LANGUAGES=en \
oracle.install.option=CRS_SWONLY \
ORACLE_BASE=/u01/app/grid \
ORACLE_HOME=/u01/app/11.2.0/grid \
oracle.install.asm.OSDBA=asmdba \
oracle.install.asm.OSOPER=asmoper \
oracle.install.asm.OSASM=asmadmin \
oracle.install.crs.config.storageOption=ASM_STORAGE \
oracle.install.crs.config.sharedFileSystemStorage.votingDiskRedundancy=EXTERNAL \
oracle.install.crs.config.sharedFileSystemStorage.ocrRedundancy=EXTERNAL \
oracle.install.crs.config.useIPMI=false \
oracle.install.asm.SYSASMPassword=lhr \
oracle.install.asm.diskGroup.name=OCR \
oracle.install.asm.diskGroup.redundancy=EXTERNAL \
oracle.install.asm.diskGroup.disks=/dev/rhdisk20 \
oracle.install.asm.monitorPassword=lhr \
oracle.installer.autoupdates.option=SKIP_UPDATES
---------------------------------- 單例項db安裝
/softtmp/database/runInstaller -silent -force -noconfig -IgnoreSysPreReqs -ignorePrereq -showProgress \
oracle.install.option=INSTALL_DB_SWONLY \
DECLINE_SECURITY_UPDATES=true \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oraInventory \
SELECTED_LANGUAGES=en \
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 \
ORACLE_BASE=/u01/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=false \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=dba \
oracle.install.db.isRACOneInstall=false \
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
oracle.installer.autoupdates.option=SKIP_UPDATES
---------------------------------- rac grid安裝
./runInstaller -silent -force -noconfig -IgnoreSysPreReqs -ignorePrereq -showProgress \
INVENTORY_LOCATION=/u01/app/oraInventory \
SELECTED_LANGUAGES=en \
ORACLE_BASE=/u01/app/grid \
ORACLE_HOME=/u01/app/11.2.0/grid \
oracle.install.asm.OSDBA=asmdba \
oracle.install.asm.OSOPER=asmoper \
oracle.install.asm.OSASM=asmadmin \
oracle.install.crs.config.storageOption=ASM_STORAGE \
oracle.install.crs.config.sharedFileSystemStorage.votingDiskRedundancy=EXTERNAL \
oracle.install.crs.config.sharedFileSystemStorage.ocrRedundancy=EXTERNAL \
oracle.install.crs.config.useIPMI=false \
oracle.install.asm.diskGroup.name=OCR \
oracle.install.asm.diskGroup.redundancy=EXTERNAL \
oracle.installer.autoupdates.option=SKIP_UPDATES \
oracle.install.crs.config.gpnp.scanPort=1521 \
oracle.install.crs.config.gpnp.configureGNS=false \
oracle.install.option=CRS_CONFIG \
oracle.install.asm.SYSASMPassword=lhr \
oracle.install.asm.monitorPassword=lhr \
oracle.install.asm.diskGroup.diskDiscoveryString=/dev/rhdisk* \
oracle.install.asm.diskGroup.disks=/dev/rhdisk10 \
oracle.install.crs.config.gpnp.scanName=ZFFR4CB2101-scan \
oracle.install.crs.config.clusterName=ZFFR4CB-cluster \
oracle.install.crs.config.autoConfigureClusterNodeVIP=false \
oracle.install.crs.config.clusterNodes=ZFFR4CB2101:ZFFR4CB2101-vip,ZFFR4CB1101:ZFFR4CB1101-vip \
oracle.install.crs.config.networkInterfaceList=en0:22.188.187.0:1,en1:222.188.187.0:2 \
ORACLE_HOSTNAME=ZFFR4CB2101
---------------------------------- rac db安裝
./runInstaller -silent -force -noconfig -IgnoreSysPreReqs -ignorePrereq -showProgress \
oracle.install.option=INSTALL_DB_SWONLY \
DECLINE_SECURITY_UPDATES=true \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oraInventory \
SELECTED_LANGUAGES=en \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=false \
oracle.install.db.EEOptionsSelection=false \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=asmoper \
oracle.install.db.isRACOneInstall=false \
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
oracle.installer.autoupdates.option=SKIP_UPDATES \
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 \
ORACLE_BASE=/u01/app/oracle \
ORACLE_HOSTNAME=ZFFR4CB2101 \
oracle.install.db.CLUSTER_NODES=zffr4cb2101,zffr4cb1101 \
oracle.install.db.isRACOneInstall=false
-------自動啟動 The Oracle system identifier(SID) "orcltest" already exists. Specify another SID.
vi /etc/oratab
---------啟動
1) lsnrctl start (啟動監聽) -------監聽停止: lsnrctl stop，lsnrctl是listener-control 監聽器的縮寫，檢視監聽的狀態(lsnrctl status)
2) net start OracleServiceORCL (COCL 我的SID，可以小寫，這是開啟資料庫例項)
或者用 oradim –startup –sid orcl -----net stop OracleServiceORACLE
/********************************************************************************
* SQL Scripts Name:
* SQL Scripts Desc:
* Author:
* Date:
* Inputs:
* Outputs:
* Return:
* History:
********************************************************************************/
--------------- 修改dbid和dbname
原： orcltest
修改後：DBID: 4270446895 Database Name: ORA11G
create pfile from spfile;
shutdown immediate;
startup mount;
nid target=sys/lhr dbname=ORA11G
cp initorcltest.ora initORA11G.ora
shutdown immediate;
startup open read only;
@chang_dbid_lhr.sql
create pfile from spfile;
shutdown immediate;
------------AUTHID CURRENT_USER
CREATE OR REPLACE PROCEDURE PRO_XXXX_LHR(p_flag in NUMBER DEFAULT 1,
p_result out varchar2)
AUTHID CURRENT_USER AS
begin
end ;
--------- oracle 使用者解壓縮
gunzip -c 10201_database_linux_x86_64.cpio.gz > /tmp/10201_database_linux_x86_64.cpio
cpio -idmv < 10201_database_linux_x86_64.cpio
unzip p6810189_10204_Linux-x86-64.zip -d /tmp
壓縮當前的資料夾 zip -r ./xahot.zip ./* -r表示遞迴
zip [引數] [打包後的檔名] [打包的目錄路徑]*/
Linux下*.tar.gz檔案解壓縮命令
1.壓縮命令：
　　命令格式：tar -zcvf 壓縮檔名.tar.gz 被壓縮檔名
可先切換到當前目錄下。壓縮檔名和被壓縮檔名都可加入路徑。
2.解壓縮命令：
　　命令格式：tar -zxvf 壓縮檔名.tar.gz
　　解壓縮後的檔案只能放在當前的目錄。
Aix下*.tar.gz檔案解壓縮命令
gunzip -c gdul3.5.0.1.tar.gz | tar -xvf -
---------- 重建scott使用者
sqlplus / as sysdba
SQL>@$ORACLE_HOME/rdbms/admin/utlsampl.sql
-------------SQLNET跟蹤tnsping過程 sqlnet.ora中配置
Trace_level_client=16
Trace_directory_client=D:\Program files\app\oracle\product\11.2.0.1\dbhome_1\NETWORK\ADMIN
Trace_unique_client=on
Trace_timestamp_client=on
Diag_adr_enabled=off
tnsping.trace_directory=D:\Program files\app\oracle\product\11.2.0.1\dbhome_1\NETWORK\ADMIN
tnsping.trace_level=support
----------後設資料獲取
SELECT to_char(DBMS_METADATA.GET_DDL('TABLESPACE', a.tablespace_name))
FROM DBA_TABLESPACES a
where a.TABLESPACE_NAME = 'TS_LHR';
SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')) DDL_SQL FROM DUAL;
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHRSYS'))) FROM DUAL
UNION ALL
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHRSYS'))) FROM DUAL
UNION ALL
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHRSYS'))) FROM DUAL;
--------------linux 跟蹤sqlplus程式
strace -o /tmp/output.txt -T -tt -e trace=all sqlplus / as sysdba
[root@rhel6_lhr ~]# strace -t -p 4545
------------- Unix 跟蹤sqlplus程式
truss -dfaie -o /tmp/sched_trace.out.02271 sqlplus '/as sysdba'
-----aix 修改系統時間
linux下用date -s "20131215 09:02:25"把時間設為2013年12月15日9點2分25秒。
而aix呢？它不認-s這個引數：
date -n mmddHHMMYY，mm表示月分，dd表示日期，HH表示小時，MM表示分鐘，YY表示年份。
如：date -n 1215090213表示把當前時間設為2013年12月15日9點2分，秒數無法修改。
-----自動同步linux時間
1、下載ntpdate
注：有些版本是沒有自帶ntpdate，因此需要下載
# yum install -y ntpdate
2、調整時區為上海，也就是北京時間+8區
注：想改其他時區也可以去看看/usr/share/zoneinfo目錄
# cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
# yes | cp -f /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
3、使用NTP來同步時間
# ntpdate us.pool.ntp.org
4、定時同步時間（每隔10分鐘同步時鐘）
# crontab -l >/tmp/crontab.bak
# echo "*/10 * * * * /usr/sbin/ntpdate us.pool.ntp.org | logger -t NTP" >> /tmp/crontab.bak
# crontab /tmp/crontab.bak
-----筆記本開啟：wlan
netsh wlan set hostednetwork mode=allow ssid=lhr-minPC key=lihuarong
netsh wlan start hostednetwork
netsh wlan show hostednetwork
------------ 福昕pdf
--右轉
crtl+shift++
------ Quote (q) 語法
select q''
from employees;
select q'\\'
from employees;
select q'\ \'
from employees;
-------------------------------------- OS 型別
-----臨時修改語言環境：
AIX：
export LANG=en_US
export LANG=zh_CN
Linux：
export LANG=en_US.UTF-8
export LANG=zh_CN.UTF-8
可用語言環境：
locale -a | grep zh_CN
--------- Linux
cpu : cat /proc/cpuinfo|grep name|cut -f2 -d:|uniq -c
memory :cat /proc/meminfo
os version : lsb_release -a
os hostname hostname
------aix
-------------- AIX 檢視CPU個數
1. smtctl
2. bindprocessor -q
3. prtconf
4.lsdev
5.vmstat
racle@DNSCDBS05:/home/oracle>hostname
DNSCDBS05
oracle@DNSCDBS05:/home/oracle>oslevel -r
7100-01
oracle@DNSCDBS05:/home/oracle>uname -vr
1 7
oracle@DNSCDBS05:/home/oracle>uname -s
AIX
oracle@DNSCDBS05:/home/oracle>uname -a
AIX DNSCDBS05 1 7 00F813B44C00
oracle@DNSCDBS05:/home/oracle>uname -v
7
oracle@DNSCDBS05:/home/oracle>pmcycles -m
CPU 0 runs at 3024 MHz
CPU 1 runs at 3024 MHz
CPU 2 runs at 3024 MHz
CPU 3 runs at 3024 MHz
CPU 4 runs at 3024 MHz
CPU 5 runs at 3024 MHz
CPU 6 runs at 3024 MHz
CPU 7 runs at 3024 MHz
oracle@DNSCDBS05:/home/oracle>prtconf|grep Processors
Number Of Processors: 2
prtconf
prtdiag
select userenv('LANGUAGE') from dual;
archive log list;
select name from v$datafile;
------------------------------- 表空間歷史增長量
select a.name, b.*
from v$tablespace a,
(select tablespace_id ts#,
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime,
round(max(tablespace_usedsize * 8 / 1024),2) ts_used_size_M,
round(max(v.tablespace_size * 8 / 1024),2) ts_size_MB,
round(max(tablespace_maxsize * 8 / 1024/1024)) ts_maxsize_G
from dba_hist_tbspc_space_usage v
where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) >=
trunc(sysdate - 10)
group by tablespace_id,
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))
order by tablespace_id,
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b
where a.ts# = b.ts#
ORDER BY b.TS#,b.datetime;
--------------------------------------------- BIOS設定之UEFI BIOS 切換為 Legacy BIOS
1、OS Optimized Defaults系統預設最佳化設定配置為DISABLE
2、CSM相容模組設定配置為ENABLE
3、Boot Mode啟動方式選擇配置為：Legacy only
4、Secure boot 配置為：DISABLE
---------------------------------- nmon
nmon -f -t -r nmon_lhr -s 10 -c 60
nmon -f -t -r nmon_lhr -s 30 -c 10
nmon -s10 -c60 -f -m /home/
上面命令的含義是：
-f ：按標準格式輸出檔名稱：<hostname>_YYYYMMDD_HHMM.nmon
-t ：輸出最耗資源的程式
-r : nmon生成的標題，監控記錄的標題
-s ：每隔n秒抽樣一次，這裡為30秒
-c ：取出多少個抽樣數量，這裡為10，即監控=10*30/60=5分鐘
-m ：生成的資料檔案的存放目錄。
-----------------自動按天採集資料：
在 crontab 中增加一條記錄：
0 0 * * * root nmon -s300 -c288 -f -m /home/ > /dev/null 2>&1
300*288=86400 秒，正好是一天的資料。
------------------ 根據相對位置建立快捷方式
%SystemRoot%\explorer.exe "一個絕對路徑或相對路徑"
如：
%SystemRoot%\explorer.exe "..\TEST\TEST\TEST.txt"
注意：得把起始位置清空
----------------- OGG
------- source端準備
SQL> col current_scn format 999999999999999
SQL> Select current_scn from v$database;
CURRENT_SCN
----------------
12242466771468
SQL>
expdp XPADB/XPADB directory=DMP dumpfile=xpadb_20160125_01.dmp LOGFILE=xpadb_20160125.log TABLES=BASE_ACTIONPOWER,BASE_BANK,BASE_BANKMERGE FLASHBACK_SCN=12242466771468
---------- target 備份
expdp xpadrpt/xpadrpt directory=OGGD dumpfile=xpadb_20160125_02.dmp LOGFILE=xpadb_20160125_2.log TABLES=BASE_ACTIONPOWER,BASE_BANK,BASE_BANKMERGE
impdp XPADRPT/xpadrpt DIRECTORY=OGGD DUMPFILE=xpadb_20160125_01.dmp LOGFILE=impdp.xpadb_20160125_01.log REMAP_SCHEMA=xpadb:xpadrpt REMAP_TABLESPACE=xpaddat:xpaddata table_exists_action=replace
start replicat ggsrep , aftercsn 12242466771468
------------- VirtualBox 修改磁碟大小 51200 為5G
vboxmanage list hdds
vboxmanage modifyhd "E:\My Virtual Machines\VirtualBox VMs\VirtualBox_XP\Windows XP.vdi" --resize 51200
--------------WPS 去掉文件漫遊
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Kingsoft\Office\6.0\plugins\officespace]
"roaminghomepageguidedtag"="9.1.0.4715"
檔案另存為.reg格式。在wps關閉下，雙擊該檔案，按”是“和”確定“匯入登錄檔。開啟wps，沒有文件漫遊了。如果希望再次顯示”文件漫遊“，將該給檔案中的"9.1.0.4715"改為""儲存，執行就可了。
------------------------- Linux 許可權
755表示該檔案所有者對該檔案具有讀、寫、執行許可權，該檔案所有者所在組使用者及其他使用者對該檔案具有讀和執行許可權。
linux檔案許可權一般都以8進製表示，格式為abc的形式，其中a,b,c各為一個數字，分別表示User、Group、及Other對該檔案的操作許可權；
如果檔案許可權用二進位制表示那麼是9位bit，從左至右，1-3位數字代表檔案所有者的許可權，4-6位數字代表同組使用者的許可權，7-9數字代表其他使用者的許可權；
而具體的許可權是由數字來表示的，讀取的許可權等於4，用r表示；寫入的許可權等於2，用w表示；執行的許可權等於1，用x表示；
透過4、2、1的組合，得到以下幾種許可權：0（沒有許可權）；4（讀取許可權）；5（4+1 | 讀取+執行）；6（4+2 | 讀取+寫入）；7（4+2+1 | 讀取+寫入+執行）
常用的linux檔案許可權如下：
444 r--r--r--
600 rw-------
644 rw-r--r--
666 rw-rw-rw-
700 rwx------
744 rwxr--r--
755 rwxr-xr-x
777 rwxrwxrwx
這裡以755為例：
1-3位7等於4+2+1，rwx，所有者具有讀取、寫入、執行許可權；
4-6位5等於4+1+0，r-x，同組使用者具有讀取、執行許可權但沒有寫入許可權；
7-9位5，同上，也是r-x，其他使用者具有讀取、執行許可權但沒有寫入許可權。
rwxr-xr-x：當前檔案對所屬使用者為可讀可寫可執行，對所屬組為可讀可執行，對其他使用者為可讀可執行
Linux 中對於檔案的許可權分為可讀(r),可寫(w),可執行(x),其對應的 8進位制程式碼是
可讀(r):4,可寫(w):2,可執行(x):1 ,如果有多個許可權，那麼對應的8進位制數字就是各個單獨許可權數字相加，3個數字中，第一個代表檔案所屬使用者，第二個代表檔案所屬組，第三個代表其他。那麼對於所屬使用者為可讀可寫可執行就是 4+2+1 = 7, 對於所屬組為可讀可執行就是 4+1 = 5 ,對於其他使用者為可讀可執行就是 4+1 = 5, 合起來就是 755 的許可權
-------------- linux下檢視所有使用者
cat /etc/passwd |cut -f 1 -d :
---------- AIX檢視所有使用者及其id
lsuser ALL |cut -d ' ' -f 1
lsuser ALL |cut -d ' ' -f 2
lsuser ALL |cut -d ' ' -f 1-2
---------- AIX檢視所有使用者組及其id
lsgroup ALL |cut -d ' ' -f 1
lsgroup ALL |cut -d ' ' -f 2
lsgroup ALL |cut -d ' ' -f 1-2
---------- linux和AIX檢視所有使用者及其id
cat /etc/passwd |cut -d : -f 1
cat /etc/passwd |cut -d : -f 3
cat /etc/passwd |cut -d : -f 1,3
cat /etc/passwd | awk 'BEGIN{FS=":"} {printf "%-15s %-10s %-20s \n",$1,$3,$6}' | sort -n -k2
cat /etc/passwd | awk 'BEGIN{FS=":"} $3>=100 {printf "%-15s %-10s %-20s \n",$1,$3,$6}' | sort -n -k2
---------- linux和AIX檢視所有使用者組及其id
cat /etc/group |cut -d : -f 1
cat /etc/group |cut -d : -f 3
cat /etc/group |cut -d : -f 1,3
cat /etc/group | awk 'BEGIN{FS=":"} {printf "%-15s %-10s \n",$1,$3}' | sort -n -k2
cat /etc/group | awk 'BEGIN{FS=":"} $3>=100 {printf "%-15s %-10s \n",$1,$3}' | sort -n -k2
[root@redhat4 ~]# cat /etc/group | awk 'BEGIN{FS=":"} {printf "%-10s %-5s \n",$1,$3}'
root 0
bin 1
daemon 2
sys 3
adm 4
tty 5
xfs 43
ntp 38
gdm 42
stapdev 101
stapusr 102
pegasus 65
htt 103
oinstall 500
dba 501
---------- linux和AIX檢視所有使用者組及其id，且id>=100
[root@redhat4 ~]# cat /etc/group | awk 'BEGIN{FS=":"} $3>=100 {printf "%-10s %-5s \n",$1,$3}'
users 100
nfsnobody 4294967294
stapdev 101
stapusr 102
htt 103
oinstall 500
dba 501
[root@redhat4 ~]#
-------------------------------------------------------------------------------- 虛擬機器系列 --------------------------------------------------------------------------------
------------------------------------------- 最新的XP虛擬機器 Windows XP.vdi
虛擬機器安裝：無論是綠色還是安裝都需要有管理員的許可權
虛擬機器中安裝windows7：需要進入PE系統後再安裝
c:
cd C:\Program Files (x86)\VMware\VMware Workstation\
----檔案合併
vmware-vdiskmanager -r "E:\My Virtual Machines\Windows XP Professional\Windows XP Professional.vmdk" -t 0 "E:\My Virtual Machines\Windows XP Professional\XP_LHR.vmdk"
---增大虛擬機器檔案 -x後的引數為磁碟擴充套件後的總大小
vmware-vdiskmanager -x 15Gb "G:\VMware Space\Red Hat Enterprise Linux 5.vmdk"
---壓縮磁碟空間，但是磁碟的最大值不變
vmware-vdiskmanager -k "G:\My Virtual Machines\RHEL6.5_LHRDB\RHEL6.5_LHRDB_DATA.vmdk"
{} 必須有
[] 可選項
| 選擇
<> 註釋或引數值
()
-------------------------------------------------------------------------------- CHM --------------------------------------------------------------------------------
chm檔案是由Windows目錄下的hh.exe這個檔案開啟的，但是它的搜尋功能卻不是這個檔案能辦到的，所以， chm能開啟卻無法搜尋，或者是MSDN能開啟卻無法搜尋，即使重灌也無濟於事，有的人遇到這個問題還從其他人的機器上重新複製了hh.exe這個檔案，發現仍然不能解決這個問題。其實解決這個問題，很簡單，在執行裡面執行以下命令：
regsvr32 hhctrl.ocx
regsvr32 itss.dll
regsvr32 itircl.dll //這個很重要，是關於全文搜尋的。
問題就可以解決了。最後一個命令特別重要，以前我找到的解決方法只有前面兩個，沒有最後一個，還是解決不了問題的。
如果chm格式檔案出現“網頁不能瀏覽”的錯誤，在該文件上點選滑鼠右鍵，解除鎖定即可。
------------------------------------ shell中的引號
單引號和雙引號的區別。單引號告訴shell忽略所有特殊字元，而雙引號忽略大多數，但不包括$、\、`。
-------------------------------------------
Sub setpicsize() '批次選中圖片
Dim j '計數圖片個數
Application.ScreenUpdating = False
For j = 1 To ActiveDocument.InlineShapes.Count '檔案中圖片總個數，圖片型別為inlineshapes
'ActiveDocument.InlineShapes(j).Height = ActiveDocument.InlineShapes(j).Height '設定高度
'ActiveDocument.InlineShapes(j).Width = ActiveDocument.InlineShapes(j).Width '設定寬度
ActiveDocument.InlineShapes(j).Range.Editors.Add wdEditorEveryone
Next j
ActiveDocument.SelectAllEditableRanges (wdEditorEveryone)
ActiveDocument.DeleteAllEditableRanges (wdEditorEveryone)
Application.ScreenUpdating = True
End Sub
'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# ||',@'||A.INST_ID||' IMMEDIATE ;' kill_session,
'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# ||''' IMMEDIATE ;' kill_session,
ALTER SYSTEM KILL SESSION '1228,42549,@1';
SELECT 'ALTER SYSTEM DISCONNECT SESSION ''' || V.SID || ',' || V.SERIAL# || ',@' ||
V.INST_ID || ''' IMMEDIATE',
'ALTER SYSTEM DISCONNECT SESSION ''' || V.SID || ',' || V.SERIAL# ||
''' IMMEDIATE',
V.*
FROM GV$SESSION V;
-----清理killed的會話
-----方法1
select spid, program from v$process
where program!= 'PSEUDO'
and addr not in (select paddr from v$session)
and addr not in (select paddr from v$bgprocess)
and addr not in (select paddr from v$shared_server);
select INST_ID, spid, program,'kill -9 '|| spid kill9
from gv$process a
where program != 'PSEUDO'
and (INST_ID, addr) not in (select INST_ID, paddr from gv$session)
and (INST_ID, addr) not in (select INST_ID, paddr from gv$bgprocess)
and (INST_ID, addr) not in (select INST_ID, paddr from gv$shared_server)
and a.PNAME is null;
-----方法2
set line 9999
col sessionid format a20
col sessionid_killed format a20
col kill_session format a60
SELECT a.INST_ID,
a.SID || ',' || a.SERIAL# || ',' ||
(select spid
from gv$process b
where b.INST_ID = a.INST_ID
and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR
) sessionid,
a.PADDR,
a.STATUS,
a.PROGRAM,
'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session
FROM gv$session a
WHERE a.USERNAME = 'SYS'
and a.STATUS = 'KILLED';
-----方法3
SELECT a.SID || ',' || a.SERIAL# || ',' ||
(select spid
from gv$process b
where b.INST_ID = a.INST_ID
and A.pid = b.pid) sessionid,
'alter system kill session ''' || sid || ',' || serial# || ''';' kill_session
FROM gV$DETACHED_SESSION a;
-----方法4
SELECT INST_ID, spid, program, 'kill -9 ' || spid kill9
FROM gv$process a
WHERE (a.INST_ID, a.addr) in (select INST_ID, p.addr
from gv$process p
where pid <> 1
minus
select INST_ID, s.paddr
from gv$session s)
and a.PNAME is null;
---------------- rac 配置SSH互信
sshUserSetup.sh在GI安裝介質解壓縮後的sshsetup目錄下。下面兩條命令在一個節點上執行即可，在root使用者下執行：
./sshUserSetup.sh -user grid -hosts "raclhr-12cR1-N2 raclhr-12cR1-N1" -advanced exverify –confirm
./sshUserSetup.sh -user oracle -hosts "raclhr-12cR1-N2 raclhr-12cR1-N1" -advanced exverify -confirm
-------- 指令碼配置
grep "^LoginGraceTime 0" /etc/ssh/sshd_config
[ $? -ne 0 ] && { cp -p /etc/ssh/sshd_config /etc/ssh/sshd_config.org; echo "LoginGraceTime 0" >>/etc/ssh/sshd_config; }
export hn=`hostname`
export oth=RACDB2
export p_pwd='/nfs/software/db/install/inst/sh/11g'
su - grid -c "$p_pwd/sshUserSetup.sh -user grid -hosts $oth -noPromptPassphrase"
su - grid -c "ssh $hn hostname"
su - grid -c "ssh $oth hostname"
su - oracle -c "$p_pwd/sshUserSetup.sh -user oracle -hosts $oth -noPromptPassphrase"
su - oracle -c "ssh $hn hostname"
su - oracle -c "ssh $oth hostname"
------分別配置grid和oracle使用者的ssh
-------- 手工配置
----------------------------------------------------------------------------------
[root@ZFLHRDB1 : /]# su - oracle
[oracle@ZFLHRDB1 ~]$ mkdir -p ~/.ssh
[oracle@ZFLHRDB1 ~]$ chmod 700 ~/.ssh
[oracle@ZFLHRDB1 ~]$ ssh-keygen -t rsa ->回車->回車->回車
[oracle@ZFLHRDB1 ~]$ ssh-keygen -t dsa ->回車->回車->回車
-----------------------------------------------------------------------------------
[root@ZFLHRDB2 : /]# su - oracle
[oracle@ZFLHRDB2 ~]$ mkdir ~/.ssh
[oracle@ZFLHRDB2 ~]$ chmod 700 ~/.ssh
[oracle@ZFLHRDB2 ~]$ ssh-keygen -t rsa ->回車->回車->回車
[oracle@ZFLHRDB2 ~]$ ssh-keygen -t dsa ->回車->回車->回車
-----------------------------------------------------------------------------------
[oracle@ZFLHRDB1 ~]$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
[oracle@ZFLHRDB1 ~]$ cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
[oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys ->輸入ZFLHRDB2密碼
[oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys ->輸入ZFLHRDB2密碼
[oracle@ZFLHRDB1 ~]$ scp ~/.ssh/authorized_keys ZFLHRDB2:~/.ssh/authorized_keys ->輸入ZFLHRDB2密碼
-----------------------------------------------------------------------------------
測試兩節點連通性：
[oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB1 date
[oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB2 date
[oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB1-priv date
[oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB2-priv date
[oracle@ZFLHRDB2 ~]$ ssh ZFLHRDB1 date
[oracle@ZFLHRDB2 ~]$ ssh ZFLHRDB2 date
[oracle@ZFLHRDB2 ~]$ ssh ZFLHRDB1-priv date
[oracle@ZFLHRDB2 ~]$ ssh ZFLHRDB2-priv date
-----如何清除Shared Pool中某條SQL語句？
如果資料庫版本為Oracle 10g以前，那麼只能清空整個Shared Pool，命令為：“ALTER SYSTEM FLUSH SHARED_POOL;”。在Oracle 10g中提供了一個包DBMS_SHARED_POOL，該包可以實現該功能。若該包沒有安裝，則可以透過$ORACLE_HOME/rdbms/admin/dbmspool.sql進行安裝。在Oracle 10.2.0.4中有BUG（MOS為：751876.1），需要透過設定事件來規避該問題，命令為：“ ALTER SESSION SET EVENTS '5614566 TRACE NAME CONTEXT FOREVER';”
使用這種方法，就可以精確的將一個SQL從共享池中刪除，從而使得Oracle為這個SQL重新生成執行計劃。這種方法只針對單個SQL語句，使得解決問題的同時不會造成任何的誤傷。
SYS@lhrdb> SELECT ADDRESS,HASH_VALUE FROM V$SQLAREA WHERE ROWNUM<=1;
ADDRESS HASH_VALUE
---------------- ----------
0000000092D263D0 3231842444
SYS@lhrdb> EXEC DBMS_SHARED_POOL.PURGE('0000000092D263D0,3231842444','C');
PL/SQL procedure successfull
--------------------------------------------- Linux 邏輯卷管理
pvcreate /dev/sda4
pvdisplay
pvs
vgcreate vg_orasoft /dev/sda4
vgdisplay
vgs
lvcreate -n lv_orasoft_u01 -L 1G vg_orasoft
lvdisplay
lvs
mkfs.ext4 /dev/vg_orasoft/lv_orasoft_u01
mkdir /u11
mount /dev/vg_orasoft/lv_orasoft_u01 /u11
--mv /u01/* /u11/
--擴充套件邏輯卷
vgextend vg_orasoft /dev/sdb3
--lvextend -L +9G /dev/vg_orasoft/lv_orasoft_u01
lvextend -L 20G /dev/vg_orasoft/lv_orasoft_u01
resize2fs /dev/vg_orasoft/lv_orasoft_u01 #更新檔案系統
--收縮邏輯卷
lvreduce -L -4G /dev/vg_orasoft/lv_orasoft_u01
--重新命名邏輯卷
lvrename /dev/vg_orasoft/lv_ora_soft_u01 /dev/vg_orasoft/lv_orasoft_u01
--- /etc/fstab
/dev/vg_orasoft/lv_orasoft_u01 /u01 ext4 defaults 0 0
/dev/vg_orasoft/lv_orasoft_u02 /u02 ext4 defaults 0 0
/dev/vg_orasoft/lv_orasoft_u03 /u03 ext4 defaults 0 0
/dev/vg_orasoft/lv_oradata_u04 /u04 ext4 defaults 0 0
---找邏輯卷
lvmdiskscan
vgchange -ay
--------------------------------------------- 查詢錶的歷史統計資訊
SELECT D.OWNER,
D.TABLE_NAME,
TO_CHAR(D.STATS_UPDATE_TIME, 'YYYY-MM-DD HH24:MI:SS') STATS_UPDATE_TIME
FROM DBA_TAB_STATS_HISTORY D
WHERE D.TABLE_NAME IN ('TPCCBOKBAL', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN')
ORDER BY D.owner,D.table_name, D.stats_update_time;
SELECT B.OWNER,
B.OBJECT_NAME TABLE_NAME,
TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
D.ROWCNT
FROM SYS.WRI$_OPTSTAT_TAB_HISTORY D, DBA_OBJECTS B
WHERE D.OBJ# = B.OBJECT_ID
AND B.OBJECT_NAME IN
('TEST_STAT', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN', 'PK_TPCCBOKBAL')
ORDER BY D.OBJ#, D.SAVTIME;
----------------------- 查詢索引的歷史統計資訊
SELECT B.OWNER,
B.OBJECT_NAME INDEX_NAME,
TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
D.ROWCNT,
D.BLEVEL,
D.LEAFCNT,
D.DISTKEY,
D.CLUFAC
FROM SYS.WRI$_OPTSTAT_IND_HISTORY D, DBA_OBJECTS B
WHERE D.OBJ# = B.OBJECT_ID
AND B.OBJECT_NAME IN ('IND_TEST

小麥苗的常用程式碼

相關文章