点击(此处)折叠或打开

?
●
?
◆
※
☆
⊙
------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 搜索命令历史，当找到命令后，通常再按回车键就可以执行该命令。如果想对找到的命令进行调整后再执行，则可以按一下左或右方向键。
------------------------------------------------------------------------------------------------- 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):
----------------禁用U盘启用U盘
----- 启用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

ITPub博客

小麦苗的常用代码

最新文章