小麦苗的常用代码--常用命令（仅限自己使用））--上
下：https://blog.csdn.net/lihuarongaini/article/details/100743572
囗
■
☑
●
•
·
◆
※
☆
★
⊙
√
→
innobackupex --help
-?
-h
help=y
systeminfo | find "系统类型"
----- editplus 替换空行： ^[ \t]*\n EDIT -> DELETE->DELETE BLANK LINES
----- editplus 替换以#开头的行，多次执行： ^#[^\n]*\n
windows不支持的文件名：\ / : * ? " < > |
\ 反斜杠、捺斜杠
/ 正斜杠、撇斜杠
制表符 chr(9)
换行符 chr(10)
回车符 chr(13)
1s=1000ms(毫秒)=1000000(微秒)
------sudo
vi /etc/sudoers
oracle ALL=(ALL) NOPASSWD: ALL
输入wq!
-------------------------------常用日期
月份 全拼 简拼 示例
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
---------------------- 关闭休眠后，Hiberfil.sys自动释放
powercfg -h off
休眠文件Hiberfil.sys释放C盘
---------------------------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
bbed password=blockedit blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/ora11g/users01.dbf'
---------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'']# '
export PS1="[\u@\h-\`echo \$ORACLE_SID\` \W]$ "
-------------------------------------------------------------------------------- linux
export HISTTIMEFORMAT='%F %T '
export HISTSIZE=100000
export HISTFILESIZE=100000
Ctrl+R 搜索命令历史 ，当找到命令后，通常再按回车键就可以执行该命令。如果想对找到的命令进行调整后再执行，则可以按一下左或右方向键。ctrl+r (继续反向搜索)
------------------------------------------------------------------------------------------------- cmd 命令
mstsc 远程桌面
firewall.cpl 和 wf.msc 防火墙设置
services.msc 服务
cmd
msconfig 开启启动项
dxdiag Direct11
---查找文件内容
findstr /S /I "普通用户" *.sql
----开启远程桌面
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
[HKEY_CURRENT_USER\Control Panel\Colors] windows项 ：202 234 206
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Themes\DefaultColors\Standard] --16进制：caeace
豆沙：207 232 204
最好：209 231 212 D2E7D5
cmd界面背景：0，128，128 ，16进制值：#008080
代码背景：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显示历史命令
chcp 65001 就是换成UTF-8代码页
chcp 936 可以换回默认的GBK
chcp 437 是美国英语
dir /s /b 显示当前目录及其目录的所有文件
----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_AMERICA.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_AMERICA.ZHS16GBK"
--环境变量，如果报错“ora-12705:cannot access NLS data files or invalid environment specified”，那么请将以下环境变量加入到系统的环境变量中，注意：在cmd中设置也不起作用的，必须手动加入环境变量中。
---------cmd下设置用户环境变量
SETX "NLS_DATE_FORMAT" "YYYY-MM-DD HH24:MI:SS"
SETX "NLS_LANG" "AMERICAN_AMERICA.ZHS16GBK"
SETX "ORACLE10G" "D:\Program_files\u01\app\oracle\product\10.2.0\db_1"
SETX "ORACLE11G" "D:\Program_files\u01\app\oracle\product\11.2.0.1\dbhome_1"
SETX "ORACLE8I" "D:\Program_files\u01\app\oracle\product\ora8i"
SETX "ORACLE9I" "D:\Program_files\u01\app\oracle\product\ora92"
SETX "ORACLE_HOME" "D:\Program_files\u01\app\oracle\product\11.2.0.1\dbhome_1"
SETX "TNS_ADMIN" "%ORACLE_HOME%\NETWORK\ADMIN"
SETX "Path" "%ORACLE_HOME%\bin"
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_AMERICA.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'
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias asmcmd='rlwrap asmcmd'
alias dgmgrl='rlwrap dgmgrl'
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'
NLS_DATE_FORMAT=YYYY-MM-DD HH24:mi:ss
NLS_LANG=AMERICAN_AMERICA.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
PATH=%ORACLE_HOME%\bin
TNS_ADMIN=%ORACLE_HOME%\NETWORK\ADMIN
---------cmd下设置用户环境变量
SETX "NLS_DATE_FORMAT" "YYYY-MM-DD HH24:MI:SS"
SETX "NLS_LANG" "AMERICAN_AMERICA.ZHS16GBK"
SETX "ORACLE10G" "D:\Program files\app\oracle\product\10.2.0\db_1"
SETX "ORACLE11G" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1"
SETX "ORACLE8I" "D:\Program files\app\oracle\product\ora8i"
SETX "ORACLE9I" "D:\Program files\app\oracle\product\ora92"
SETX "ORACLE_HOME" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1"
SETX "TNS_ADMIN" "%ORACLE_HOME%\NETWORK\ADMIN"
SETX "Path" "%ORACLE_HOME%\bin"
SETX "VBOX_MSI_INSTALL_PATH" "D:\Program Files\Oracle\VirtualBox"
alter session set nls_language='SIMPLIFIED CHINESE';
alter system set nls_language='AMERICAN' scope=spfile;
------------------------------------ sqlnet.ora----------------------------------------------
---------- windows
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)
---------- linux 建议注释掉SQLNET.AUTHENTICATION_SERVICES的值
# SQLNET.AUTHENTICATION_SERVICES= (ALL)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)
--------------------------------------------------------------------- oracle
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export PATH=$PATH:/sbin/:$HOME/bin
# for oracle user
unset USERNAME
export GLOBAL_DB_UNIQUE_NAME=orclasm
export ORACLE_HOSTNAME=192.168.128.134
export ORACLE_UNQNAME=orclasm
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'
stty erase ^h
ORACLE_HOME=D:\Program files\app\oracle\product\11.2.0.1\dbhome_1
PATH=%ORACLE_HOME%\bin
TNS_ADMIN=%ORACLE_HOME%\NETWORK\ADMIN
##NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
#ORACLE_SCRIPT=%ORACLE_HOME%\RDBMS\ADMIN
SQLPATH=.;%ORACLE_BASE%\dba_scripts\sql;%ORACLE_HOME%\rdbms\admin
NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280"
NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
NLS_LANG=AMERICAN_AMERICA.UTF8
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_LANG=AMERICAN_CHINA.ZHS16GBK
NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss'
--------------------------------------------------------------------- grid
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export PATH=$PATH:$HOME/bin
# for grid user
export ORACLE_SID=+ASM
export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=/u01/app/grid/11.2.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
umask 022
alias sqlplus='rlwrap sqlplus'
alias asmcmd='rlwrap asmcmd'
alias alert_log='tail -200f $ORACLE_BASE/diag/asm/+asm/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias crsd_log='tail -200f $ORACLE_HOME/log/$HOSTNAME/crsd/crsd.log'
在linux和unix上有时不知道该使用LD_LIBRARY_PATH还是LIBPATH，经过搜索和实际使用，实际的情况如下
OS Environment Variable Name
Linux LD_LIBRARY_PATH
Solaris 32-bit ruby LD_LIBRARY_PATH_32 or LD_LIBRARY_PATH
Solaris 64-bit ruby LD_LIBRARY_PATH_64 or LD_LIBRARY_PATH
HP-UX PA-RISC 32-bit ruby SHLIB_PATH
HP-UX PA-RISC 64-bit ruby LD_LIBRARY_PATH
HP-UX IA64 LD_LIBRARY_PATH
Mac OS X DYLD_LIBRARY_PATH
AIX LIBPATH
-----------------------------------------------------------------------------------------------
------------------------------------listener.ora--------------------------------------------
--动态监听： alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.157)(PORT = 1521))';
-----------------------------------------------------------------------------------------------
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl.lhr.com)
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME = rman.lhr.com)
(SID_NAME = rman)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
LSNR2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1526))
)
)
)
SID_LIST_LSNR2 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
------------windows 下
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\oracle\product\11.2.0.1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\oracle\product\11.2.0.1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = orcl.lhr.com)
(SID_NAME = orcl)
(ORACLE_HOME = D:\app\oracle\product\11.2.0.1)
)
)
-----------------------/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.133)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.lhr.com)
)
)
#### 动态监听非默认端口
LSNR_ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1526))
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
-------------------------------------------------------------------- rac
--- TAF BASIC方式
racdb_vip =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.131)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jmrac)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
--- TAF preconnect方式
racdb_vip1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.131)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jmrac)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(BACKUP = preconnect)
)
)
)
racdb_vip2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jmrac)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(BACKUP = preconnect)
)
)
)
----- 如何启用 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就可以完全退出了。
--退出exp或imp交互窗口：
imp lhr/lhr
Windows下用ctrl+c，Linux下用Ctrl+d
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
----------------------------- 科学计数法
12345678901，结果为 1.23E+10，即 1.23 乘以 10 的 10 次幂
12300000000
4.1E+11
410 000 000 000
------------------- 显示行号
cat -n rsyslog.conf
如果要显示所有行号，使用 :set nu
如果要显示当前行号，使用 :nu
如果要跳转到指定行，使用 :行号
例如，跳转到第10行，使用 :10
----------------------------------------- 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'`
echo `date +'%Y-%m-%d %H:%M:%S'` >> /var/log/oraclelog
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
col PDBNAME format a15
col TS_NAME format a20
col LOGGING format a10
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;
----- 12C表空间：
set pagesize 9999 line 9999
col TS_Name format a30
col PDBNAME format a15
col TS_NAME format a20
col LOGGING format a10
SELECT CON_ID,
PDBNAME,
TS#,
TS_NAME,
TS_SIZE_M,
FREE_SIZE_M,
USED_SIZE_M,
USED_PER,
MAX_SIZE_G,
USED_PER_MAX,
BLOCK_SIZE,
LOGGING,
TS_DF_COUNT
FROM (WITH wt1 AS (SELECT ts.CON_ID,
(SELECT np.NAME
FROM V$CONTAINERS np
WHERE np.CON_ID = tS.con_id) PDBNAME,
(SELECT A.TS#
FROM V$TABLESPACE A
WHERE A.NAME = UPPER(tS.TABLESPACE_NAME)
AND a.CON_ID = tS.con_id) TS#,
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 cdb_tablespaces ts,
(SELECT d.CON_ID,
'D' TYPE,
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
FROM cdb_data_files d
GROUP BY d.CON_ID,
TABLESPACE_NAME
UNION ALL
SELECT d.CON_ID,
'T',
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))
FROM cdb_temp_files d
GROUP BY d.CON_ID,
TABLESPACE_NAME) df,
(SELECT d.CON_ID,
TABLESPACE_NAME,
SUM(BYTES) FREESIZ
FROM cdb_free_space d
GROUP BY d.CON_ID,
TABLESPACE_NAME
UNION ALL
SELECT d.CON_ID,
tablespace_name,
SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
FROM gv$sort_usage a,
cdb_tablespaces d
WHERE a.tablespace = d.tablespace_name
AND a.CON_ID = d.CON_ID
GROUP BY d.CON_ID,
tablespace_name) fs
WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
AND ts.CON_ID = df.CON_ID
AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+)
AND ts.CON_ID = fs.CON_ID(+))
SELECT T.CON_ID,
(CASE
WHEN T.PDBNAME = LAG(T.PDBNAME, 1)
OVER(PARTITION BY T.PDBNAME ORDER BY TS#) THEN
NULL
ELSE
T.PDBNAME
END) PDBNAME,
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 DISTINCT T.CON_ID,
'' PDBNAME,
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
GROUP BY rollup(CON_ID,PDBNAME)
)
ORDER BY CON_ID,TS# ;
------ 数据文件情况
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;
----12c的数据文件
SELECT FILE_ID,
CON_ID,
(CASE
WHEN T.PDBNAME = LAG(T.PDBNAME, 1)
OVER(PARTITION BY T.PDBNAME ORDER BY TS#) THEN
NULL
ELSE
T.PDBNAME
END) PDBNAME,
TS#,
TABLESPACE_NAME,
TS_SIZE_M,
FILE_NAME,
FILE_SIZE_M,
FILE_MAX_SIZE_G,
AUTOEXTENSIBLE,
INCREMENT_M,
AUTOEXTEND_RATIO,
CREATION_TIME,
INCREMENT_BY_BLOCK,
BYTES,
BLOCKS,
MAXBYTES,
MAXBLOCKS,
USER_BYTES,
USER_BLOCKS
FROM (SELECT D.FILE_ID,
D.CON_ID,
(SELECT NP.NAME
FROM V$CONTAINERS NP
WHERE NP.CON_ID = D.CON_ID) PDBNAME,
(SELECT A.TS#
FROM V$TABLESPACE A
WHERE A.NAME = UPPER(D.TABLESPACE_NAME)
AND A.CON_ID = D.CON_ID) TS#,
D.TABLESPACE_NAME,
(SELECT ROUND(SUM(NB.BYTES) / 1024 / 1024, 2)
FROM CDB_DATA_FILES NB
WHERE NB.TABLESPACE_NAME = D.TABLESPACE_NAME
AND NB.CON_ID = D.CON_ID) 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
AND B.CON_ID = D.CON_ID) CREATION_TIME,
D.INCREMENT_BY INCREMENT_BY_BLOCK,
D.BYTES,
D.BLOCKS,
D.MAXBYTES,
D.MAXBLOCKS,
D.USER_BYTES,
D.USER_BLOCKS
FROM CDB_DATA_FILES D
UNION ALL
SELECT D.FILE_ID,
D.CON_ID,
(SELECT NP.NAME
FROM V$CONTAINERS NP
WHERE NP.CON_ID = D.CON_ID) PDBNAME,
(SELECT A.TS#
FROM V$TABLESPACE A
WHERE A.NAME = UPPER(D.TABLESPACE_NAME)
AND A.CON_ID = D.CON_ID) TS#,
D.TABLESPACE_NAME,
(SELECT ROUND(SUM(NB.BYTES) / 1024 / 1024, 2)
FROM V$TEMPFILE NB
WHERE NB.NAME = D.FILE_NAME
AND NB.CON_ID = D.CON_ID) 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
AND B.CON_ID = D.CON_ID) CREATION_TIME,
D.INCREMENT_BY INCREMENT_BY_BLOCK,
D.BYTES,
D.BLOCKS,
D.MAXBYTES,
D.MAXBLOCKS,
D.USER_BYTES,
D.USER_BLOCKS
FROM CDB_TEMP_FILES D) T
ORDER BY CON_ID,
TS#,
FILE_ID;
------------含LOB字段的用户大小
SELECT
trunc(((SELECT SUM(S.BYTES) -- The Table Segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = UPPER('HTSEC_ADMIN')) +
(SELECT SUM(S.BYTES) -- The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = UPPER('HTSEC_ADMIN') AND
(L.SEGMENT_NAME = S.SEGMENT_NAME AND L.OWNER = UPPER('HTSEC_ADMIN'))) +
(SELECT SUM(S.BYTES) -- The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = UPPER('HTSEC_ADMIN') AND
(I.INDEX_NAME = S.SEGMENT_NAME AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('HTSEC_ADMIN'))))/1024/1024/1024,2)
"TOTAL TABLE SIZE (G)"
FROM DUAL;
ACCEPT SCHEMA PROMPT 'Table Owner: '
ACCEPT TABNAME PROMPT 'Table Name: '
SELECT (SELECT SUM(S.BYTES) -- The Table Segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = UPPER('&SCHEMA')
AND (S.SEGMENT_NAME = UPPER('&TABNAME'))) +
(SELECT SUM(S.BYTES) -- The Lob Segment Size
FROM DBA_SEGMENTS S,
DBA_LOBS L
WHERE S.OWNER = UPPER('&SCHEMA')
AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND
L.TABLE_NAME = UPPER('&TABNAME') AND
L.OWNER = UPPER('&SCHEMA'))) +
(SELECT SUM(S.BYTES) -- The Lob Index size
FROM DBA_SEGMENTS S,
DBA_INDEXES I
WHERE S.OWNER = UPPER('&SCHEMA')
AND (I.INDEX_NAME = S.SEGMENT_NAME AND
I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND
I.OWNER = UPPER('&SCHEMA'))) "TOTAL TABLE SIZE"
FROM DUAL;
-----查看字符集
0001 US7ASCII
0369 AL32UTF8
0354 ZHS16GBK
SELECT NLS_CHARSET_NAME(TO_NUMBER('0001', 'XXXX')) US7ASCII,
NLS_CHARSET_NAME(TO_NUMBER('0354', 'XXXX')) ZHS16GBK,
NLS_CHARSET_NAME(TO_NUMBER('0369', 'XXXX')) AL32UTF8,
TO_CHAR(NLS_CHARSET_ID('US7ASCII'), 'XXXX') US7ASCII_ID,
TO_CHAR(NLS_CHARSET_ID('ZHS16GBK'), 'XXXX') ZHS16GBK_ID,
TO_CHAR(NLS_CHARSET_ID('AL32UTF8'), 'XXXX') AL32UTF8_ID
FROM DUAL;
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
orapwd file='+data/lhrracphy/PASSWORD/pwdlhrracphy' force=y dbuniquename=lhrracphy format=12 password=lhr
orapwd file='+data' force=y dbuniquename=lhrracphy format=12 password=lhr
orapwd file='+data/HTZXDBPRI/password/orapwhtzxdbpri' force=y dbuniquename=htzxdbpri format=12 sys=oracle sysbackup=oracle sysdg=oracle syskm=oracle
orapwd input_file='+data/HTZXDBPRI/password/orapwhtzxdbpri' file='+ocr/asm/password/orapwASM' asm=y force=y
srvctl config db -d htzxdbpri -a
srvctl modify db -d htzxdbpri -pwfile '+data/HTZXDBPRI/password/orapwhtzxdbpri'
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
--卸载Windows下服务 卸载服务
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; --设置该事件后重启数据库即可。
ALTER SYSTEM SET EVENTS '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1';--屏蔽密码延迟验证
------------------------------------------------ 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 _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
--windows
netstat -ano | findstr "1521"
tasklist | findstr "3572"
----检查包忽略大小写
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
ipcs
ipcs -m
ipcrm -m 1048583
sysresv
--进程总数
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';
--ORA-00600: internal error code, arguments: [kzdlk_zt2 err],
create public database link DBLINK_OGG1
connect to LHR identified by values '72979A94BAD2AF80'
using 'OGG1';
CREATE DATABASE LINK DBLINK_OGG1
CONNECT TO CURRENT_USER
USING 'remote';
CREATE DATABASE LINK DBLINK_OGG1
USING 'remote';
----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
---- RHEL 7 CentOS 7 修改主机名
hostnamectl set-hostname raclhr-19c-n1
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='';
col name format a6
SELECT NAME,
TRUNC(SPACE_LIMIT/1024/1024/1024, 3) LIMIT_GB,
TRUNC(SPACE_USED/1024/1024/1024, 3) USED_GB,
TRUNC(SPACE_USED / SPACE_LIMIT, 3) "USED%",
TRUNC(SPACE_RECLAIMABLE, 3) RECLAIM,
NUMBER_OF_FILES
FROM V$RECOVERY_FILE_DEST V
WHERE V.SPACE_LIMIT <> 0;
SELECT NVL(FRAU.FILE_TYPE, 'Total:') FILE_TYPE,
SUM(ROUND(FRAU.PERCENT_SPACE_USED / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) USED_GB,
SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED,
SUM(FRAU.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE,
SUM(ROUND(FRAU.PERCENT_SPACE_RECLAIMABLE / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) RECLAIM_GB,
SUM(FRAU.NUMBER_OF_FILES) NUMBER_OF_FILES
FROM V$FLASH_RECOVERY_AREA_USAGE FRAU, V$RECOVERY_FILE_DEST RFD
GROUP BY ROLLUP(FILE_TYPE);
--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;
--alter database add standby logfile thread 1 group 5 ('+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 TABLE T_YH_20170705_LHR NOLOGGING
AS
SELECT 137 || (LPAD(ROWNUM, 8,'0')) x
FROM DUAL
CONNECT BY LEVEL <= 99999999;
DROP TABLE T_YH_20170705_LHR;
CREATE TABLE T_YH_20170705_LHR NOLOGGING AS
SELECT 137 || (LPAD(ROWNUM, 8,'0')) x
from xmltable('1 to 99999999');
-----------外部表
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=4G 0 0
[root@FWDB ~]# mount -o remount,size=4G /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
若有的时候不能卸载该目录，报错：“umount: /dev/shm: device is busy.”，则可以用fuser处理：
# fuser -km /dev/shm
# umount /dev/shm
# mount /dev/shm
---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/lhr/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/lhr/cdrom
enabled=1
gpgcheck=1
gpgkey=file:///media/lhr/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 erase 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*
--extundelete工具恢复rm -rf 删除的目录(ext4)
-------------- rhel5.5
# mkdir /media/cdromlhr
编辑 /etc/fstab 文件，在文件尾部添加如下内容，以便开机自动挂载光盘：
[root@localhost ~]# tail -1 /etc/fstab
/dev/hdc /media/cdromlhr 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/cdromlhr/Server
enabled=1
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
--数据库开机启动
crsctl modify resource ora.rac18c.db -attr AUTO_START=never -unsupported
---启动磁盘组
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 -ivh device-mapper-multipath-libs-0.4.9-72.el6.x86_64.rpm
rpm -ivh device-mapper-multipath-0.4.9-72.el6.x86_64.rpm
[root@raclhr-12cR1-N1 Packages]# rpm -qa|grep device-mapper
device-mapper-multipath-0.4.9-72.el6.x86_64
device-mapper-persistent-data-0.2.8-2.el6.x86_64
device-mapper-1.02.79-8.el6.x86_64
device-mapper-event-libs-1.02.79-8.el6.x86_64
device-mapper-event-1.02.79-8.el6.x86_64
device-mapper-multipath-libs-0.4.9-72.el6.x86_64
device-mapper-libs-1.02.79-8.el6.x86_64
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
/sbin/mpathconf
service multipathd restart
#/etc/init.d/multipathd restart
ps -ef|grep multipathd
/sbin/mpathconf --enable --find_multipaths y --with_module y --with_chkconfig y
ll /etc/multipath.conf
multipath -F
multipath -v2
more /etc/multipath/wwids
multipath -ll
dmsetup ls|sort
ll /dev/dm-*
multipath -v3 -ll
multipathd -k
service multipathd reload
---------------获取wwid SCSI ID
--/etc/udev/rules.d/99-oracle-asmdevices.rules
--在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
--配置udev规则，脚本如下所示：
for i in f g h i j k l m ;
do
echo "KERNEL==\"dm-*\", BUS==\"block\", PROGRAM==\"/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/\$name\",RESULT==\"`scsi_id --whitelisted --replace-whitespace --device=/dev/sd$i`\",NAME=\"asm-disk$i\",OWNER=\"grid\",GROUP=\"asmadmin\",MODE=\"0660\"" >> /etc/udev/rules.d/99-oracleasm.rules
done
编辑/etc/multipath.conf
for i in f g h i j k l m ;
do
echo "KERNEL==\"sd*\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id --whitelisted --device=/dev/\$name\",RESULT==\"`scsi_id --whitelisted --device=/dev/sd$i`\",NAME=\"asm-disk$i\",OWNER=\"grid\",GROUP=\"asmadmin\",MODE=\"0660\""
done
---------------------------------再次重新加载UDEV规则
--- # 业务运行期间，禁止通过执行/sbin/start_udev使规则生效！
/sbin/udevadm control --reload-rules
/sbin/udevadm trigger --type=devices --action=change
--在线添加ASM盘
udevadm trigger --subsystem-match=block --action=add
udevadm control --reload-rules && udevadm trigger
alter diskgroup DATA add disk '/dev/rhdiskpower38' rebalance power 10;
alter diskgroup DATA rebalance power 11;
col ERROR_CODE for a20
select * from v$asm_operation;
-------------------------------------------------------------- ASM
---------------------------- oracleasm常用命令 asmlib oracleasm日志： tail -f /var/log/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
/etc/init.d/oracleasm restart
/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
ls -l /dev/oracleasm/disks
--配置文件
cat /etc/sysconfig/oracleasm
------ 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
# 18c
kfod disks=asm ds=true cluster=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';
CREATE DISKGROUP dgroup1 NORMAL REDUNDANCY disk '/dev/raw/raw6', '/dev/raw/raw7';
CREATE DISKGROUP dgroup1 HIGH REDUNDANCY disk '/dev/raw/raw6', '/dev/raw/raw7', '/dev/raw/raw8';
create diskgroup DG1 external redundancy
failgroup FG1 disk '/dev/raw/raw6' name DG2_FG1_VOL1
failgroup FG2 disk '/dev/raw/raw7' name DG2_FG2_VOL2;
create diskgroup DG2 normal redundancy
failgroup FG1 disk '/dev/raw/raw6' name DG2_FG1_VOL1
failgroup FG2 disk '/dev/raw/raw7' name DG2_FG2_VOL2;
create diskgroup DG2 normal redundancy
failgroup FG1 disk '/dev/raw/raw6','/dev/raw/raw7' name DG2_FG1_VOL1
failgroup FG2 disk '/dev/raw/raw8','/dev/raw/raw9' name DG2_FG2_VOL2;
---修改磁盘组的兼容属性
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='','ORCL:*','/dev/raw/raw*','/dev/oracleasm/disks/VOL*';
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
------ 将以下内容添加到文件/etc/rc.local文件中
------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';
在Oracle 12c中，集群的告警日志位置发生了变化，变到了$GRID_HOME/diag/crs中：/u01/app/oracle/diag/crs
tailf /u01/app/oracle/diag/rdbms/rac18cpri/rac18c1/trace/alert_rac18c1.log
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 ;
------------------------------------------------ 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 set newname for datafile
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 switch tablespace
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 ;
---生成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
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
--唤醒PMON进程
oradebug wakeup 2
alter session set events '10246 trace name context forever,level 4';
alter session set events '10246 trace name context off';
热备:
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 '=' ;
recover database using backup controlfile until cancel;
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;
Linux: exp scott/tiger tables=emp query=\"where job=\'SALESMAN\' and sal\<1600\"
Windows exp userid=tkyte/tkyte tables=t query="""where object_id < 5000"""
在windows中，需要在WHERE语句的两端使用三个双引号
--EXP-00091: Exporting questionable statistics 的方法 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;
grant ALL 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_PUMP_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_PUMP_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_PUMP_DIR FULL=Y DUMPFILE=FULLEXP.DMP LOGFILE=FULLEXP.LOG PARALLEL=2
impdp \'/ AS SYSDBA\' DIRECTORY=DATA_PUMP_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
----全库导出时的排除信息
more /tmp/exclude_schema.par
EXCLUDE=STATISTICS,SCHEMA:"in ('SYSTEM','MDSYS','DBSNMP','SCOTT','OUTLN','MGMT_VIEW','FLOWS_FILES','ORDSYS','EXFSYS','WMSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','ORACLE_OCM','XS$NULL','BI','PM','MDDATA','IX','SH','DIP','OE','APEX_PUBLIC_USER','HR','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR')"
------ 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
--DATABASE_EXPORT_OBJECTS
--SCHEMA_EXPORT_OBJECTS
--TABLE_EXPORT_OBJECTS
--SELECT * FROM DBA_EXPORT_OBJECTS D WHERE D.OBJECT_PATH LIKE '%DB_LINK%' ;
-----导出job
expdp system/lhr dumpfile=dmplhr_JOB.dmp directory=data_pump_dir full=y include=JOB
impdp system/lhr dumpfile=dmplhr_JOB.dmp directory=data_pump_dir full=y include=JOB SQLFILE=expddl_lhr.sql
---- 导出dblink
--所有dblink
expdp system/lhr dumpfile=dmplhr_dblink.dmp directory=data_pump_dir full=y include=db_link
--私有dblink
expdp system/lhr dumpfile=dmplhr_dblink.dmp directory=data_pump_dir schemas=SYS,LHR include=db_link
--公共dblink
expdp system/lhr dumpfile=dmplhr_dblink.dmp directory=data_pump_dir full=y include=db_link:\"IN \(SELECT DB_LINK FROM DBA_DB_LINKS WHERE OWNER = \'PUBLIC\'\)\"
impdp system/lhr dumpfile=dmplhr_dblink.dmp directory=data_pump_dir full=y include=db_link SQLFILE=expddl_lhr.sql
---------------filesize参数
exp userid=scott/tiger file=/tmp/test1,/tmp/test2,/tmp/test3,/tmp/test4,/tmp/test5 filesize=1024000 log=test.log
imp userid=lhr/lhr FILE=/tmp/test1,/tmp/test2,/tmp/test3,/tmp/test4,/tmp/test5 LOG=imp_emp.log FROMUSER=scott TOUSER=lhr TABLES=emp
--filesize数据泵示例
expdp system/lhr SCHEMAS=scott DIRECTORY=DATA_PUMP_DIR DUMPFILE=DATA_PUMP_DIR:expdp_20190416_%U.dmp FILESIZE=1024000
impdp lhr/lhr SCHEMAS=scott DIRECTORY=DATA_PUMP_DIR DUMPFILE=DATA_PUMP_DIR:expdp_20190416_%U.dmp
expdp system/lhr SCHEMAS=scott DIRECTORY=DATA_PUMP_DIR DUMPFILE=DATA_PUMP_DIR:expdp_20190416_%U.dmp FILESIZE=10g
impdp lhr/lhr SCHEMAS=scott DIRECTORY=DATA_PUMP_DIR DUMPFILE=DATA_PUMP_DIR:expdp_20190416_%U.dmp
---------------- 导出和导入物化视图
--不同步数据 TEST_MV是基表 TEST_MV_LHR是物化视图
expdp system/lhr dumpfile=mview4.dmp schemas=lhr include=TABLE:\"IN \(\'TEST_MV_LHR\'\)\",materialized_view:\"IN \(\'TEST_MV_LHR\'\)\"
impdp system/lhr dumpfile=mview4.dmp
--TEST_MV是基表 同步数据
expdp system/lhr dumpfile=mview5.dmp schemas=lhr include=TABLE:\"IN \(\'TEST_MV_LHR\',\'TEST_MV\'\)\",materialized_view:\"IN \(\'TEST_MV_LHR\'\)\"
impdp system/lhr dumpfile=mview4.dmp
exec dbms_mview.refresh('TEST_MV_LHR','C');
---导出HR用户下定义的公共同义词 单引号、双引号、小括号 都需要进行转义
expdp system/lhr dumpfile=dmplhr_syn.dmp directory=data_pump_dir full=y include=DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM:\"IN \(SELECT synonym_name FROM DBA_SYNONYMS WHERE OWNER=\'PUBLIC\' AND TABLE_OWNER=\'HR\'\)\"
---------导出到本地
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语句 不会导入数据 metadata_only表示不导出表中数据，但是其它元数据会导出的，包括存储过程、函数定义等
--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 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/
---------------------------如何彻底停止expdp进程？
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 OSUSER FOR A10
COL "DEGREE|ATTACHED|DATAPUMP" FOR A25
COL SESSION_INFO FOR A20
SELECT DS.INST_ID,
DJ.OWNER_NAME,
DJ.JOB_NAME,
TRIM(DJ.OPERATION) OPERATION,
TRIM(DJ.JOB_MODE) 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 AND DS.INST_ID = S.INST_ID)
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';
1、给表加注释
COMMENT ON TABLE TABLENAME IS '用户表';
2、查看表的COMMENT
SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME='TABLENAME';
3、给字段加注释
COMMENT ON COLUMN TABLENAME.COLNAME IS 'OOXX';
4、查看字段的COMMENT
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME='TABLENAME';
--查看创建表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
sqlplus中的清屏命令： clear scr
--修改提示符
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 99999999;
SET CHUNKSIZE 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
About Me
| ........................................................................................................................ ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub、博客园、CSDN和个人微 信公众号（ xiaomaimiaolhr）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文博客园地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群号： 230161599 、618766405 ● 微 信群：可加我微 信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友 （ 646634621 ），注明添加缘由 ● 于 2019-09-01 06:00 ~ 2019-09-31 24:00 在西安完成 ● 最新修改时间：2019-09-01 06:00 ~ 2019-09-31 24:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ........................................................................................................................ ● 小麦苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麦苗腾讯课堂主页： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客户端扫描下面的二维码来关注小麦苗的微 信公众号（ xiaomaimiaolhr）及QQ群（DBA宝典）、添加小麦苗微 信， 学习最实用的数据库技术。
........................................................................................................................ |
扫一扫
288
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
