一线运维 dba 五年经验常用 sql 大全（二） -m6米乐安卓版下载

col file_name for a55
select tablespace_name,file_name,bytes/1024/1024/1024 gb,autoextensible 
from dba_data_files where tablespace_name='am_data';
alter tablespace am_data add datafile ' data' size 30g;

su - grid 
sqlplus / as sysasm
select group_number,name,total_mb/1024 total_gb,free_mb/1024 free_gb,type 
from v$asm_diskgroup;
---调整asm磁盘均衡级别
alter diskgroup data rebalance power 10; 
---查看asm磁盘均衡时间：
select *  from  v$asm_operation;

set lin 1000 pagesize 999
col path for a33
col name for a15
col failgroup for a15
select group_number,disk_number,total_mb/1024,free_mb/1024,name,failgroup,path,failgroup_type 
from v$asm_disk where group_number='1';

select a.tablespace_name,round(total/1024/1024/1024) "total g",
round(free/1024/1024/1024) "free g",round((total-free)/total,4)*100 "used%"
from (select tablespace_name,sum(bytes) free from
dba_free_space
group by tablespace_name ) a,
(select tablespace_name,sum(bytes) total from dba_data_files
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by 4;

col table_name for a30
set pagesize 200
set linesize 200
col table_name for a30
set linesize 200
select * from (select tablespace_name,owner,segment_name "table_name",to_number(decode(substr(bytes/1024/1024,1,1),'.','0'||bytes/1024/1024,bytes/1024/1024)) total_mb
from dba_segments where tablespace_name ='&tablespacename' and segment_type='table' order by total_mb desc ) where rownum<=5;

select * from (select owner,segment_name, sum(bytes) / 1024 / 1024 mb from dba_segments where tablespace_name = 'system' group by segment_name,owner  order by 3 desc) where rownum <=5;
owner                          segment_name                                                                              mb
------------------------------ --------------------------------------------------------------------------------- ----------
sys                            xx_recname_rb_ix                                                                        11136
sys                            xxxxx_originnote_ix                                                                     9152
sys                            xxxxx_abstract_ix                                                                       6388
sys                            xxx_paydate_nu_nc                                                                       5490

select owner,segment_name ,segment_type from dba_segments where  segment_name in ('payments_recname_rb_ix','xxxxx_originnote_ix','xxxxx_abstract_ix','xxx_paydate_nu_nc');

owner                          segment_name                                                                      segment_type
------------------------------ --------------------------------------------------------------------------------- ------------------
sys                            xxx_paydate_nu_nc                                                                 index
sys                            xxxxx_originnote_ix                                                               index
sys                            xxxxx_abstract_ix                                                                 index
sys                            xxxments_recname_rb_ix                                                            index

select sum(bytes)/1024/1024/1024 from v$sgastat where pool='shared pool';
--查看空闲的：
select * from v$sgastat where name = 'free memory' and pool = 'shared pool';
--统计活动的undo
select sum(bytes (1024*1024*1024)) from dba_undo_extents where status='active';

select sql_text ,sharable_mem from v$sql where sharable_mem > '100000' order by sharable_mem;

select userenv('language') from dual;
select * from nls_database_parameters;

select tablespace_name,file_name from dba_temp_files;
col file_name for a55
select tablespace_name,file_name,bytes/1024/1024/1024 total_gb,user_bytes/1021/1024/1024 gb from dba_temp_files;

create temporary tablespace temp1  tempfile '/rhzxdata/tempdata/pbc/temp01.dbf' size 20g autoextend on maxsize 30g

oracle查询 temp 表空间的使用率
select tablespace_name,round(free_space/1024/1024/1024,2) "free(gb)",round(tablespace_size/1024/1024/1024,2) "total(gb)",round(nvl(free_space,0)*100/tablespace_size,3) "free percent"
from dba_temp_free_space;

注意：primary 端涉及到的临时表空间创建维护、临时文件创建的操作是不会传导到 standby 端的。

set line 150   
col action_time for a30    
col action for a8    
col namespace for a8    
col version for a10    
col bundle_series for a5    
col comments for a20    
select * from dba_registry_history; 

jieked2:/app/product/11.2.0/db/opatch$opatch lsinventory

select l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
from v$locked_object l, all_objects o, v$session s
where l.object_id = o.object_id
and l.session_id = s.sid
order by sid, s.serial# ;

查出锁定表的 session 的 sid, serial#,os_user_name, machine name, terminal 和执行的语句：

 select l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
 l.os_user_name,s.machine, o.object_name,s.terminal,a.sql_text, a.action
 from v$sqlarea a,v$session s, v$locked_object l,all_objects o
 where l.session_id = s.sid
 and s.prev_sql_addr = a.address
 order by sid, s.serial#;

select * from dba_dependencies where name='视图名' and type='view';

alter system kill session 'sid,serial#';

select tablespace_name,owner,segment_name,sum(bytes)/1024/1024 total_mb 
from dba_segments where tablespace_name='newcar02' 
group by tablespace_name,owner,segment_name;

 select name,compatibility,database_compatibility from  v$asm_diskgroup;

prtconf|more
lparstat -i
ortconf
aix 磁盘扫描命令
root 执行
cfgmgr -v


--查看那个进程占用磁盘空间
fuser -dv app


aix 查看错误日志并输出到 messages
jiekexuy1:/#errpt -dh
identifier timestamp t c resource_name description
de3b8540 1012214419 p h hdisk18 path has failed
4b436a3d 1012214119 t h fscsi0 link error
4b436a3d 1012214119 t h fscsi0 link error
4b436a3d 1012214119 t h fscsi0 link error

/bin/errpt -a > messages.out 

aix查看lunid信息
lscfg -vpl hdisk187 输出结果里的 serial number就是存储要的lunid
# lscfg -vpl hdisk11
  hdisk11          u9080.mme.680a6e8-v7-c2-t1-w500507680c25adba-lb000000000000  mpio fc 2145
        manufacturer................ibm     
        machine type and model......2145            
        ros level and id............0000
        device specific.(z0)........0000063268181002
        device specific.(z1)........0203202
        serial number...............600507680c808570080000000000042b
  platform specific

  name:  disk
    node:  disk
    device type:  block  

--- 检查磁盘大小（单位m）
bootinfo -s hdisk12
--- 查看磁盘的详细信息
lsattr -el hdisk12
hp：machinfo
（diskinfo /dev/rdisk/disk*  查看磁盘大小）
win：msinfo32
suse：cat /proc/cpuinfo  （model name ）
---查看资源使用情况：
hp：glance/top
aix：nmon/topas

--aix查看版本号
oslevel 
jiekexur2:/app/product/11.2.0/grid/network/log$oslevel
6.1.0.0
---查看内存大小：
hp：/usr/contrib/bin/machinfo | grep -i memory
aix: usr/sbin/lsattr -e -l sys0 -a realmem
---查看swap分区：
hp：/usr/sbin/swapinfo -a
aix：/usr/sbin/lsps -s

select  username,default_tablespace,temporary_tablespace from dba_users;
create user agriproduct identified by product default tablespace users;
grant conncet,resource,create view to product;
--解锁用户
set linesize 500
col username for a15
col account_status for a15
select username,account_status,lock_date,expiry_date,default_tablespace from dba_users where username ='sqlmon';
alter user sqlmon identified by "sqlmon" account unlock;
alter user sqlmon account unlock;
alter user sqlmon identified by "sqlmon";
conn sqlmon/sqlmon
#查看用户具有的权限
1、查询用户有哪些角色：
select *  from dba_role_privs where grantee='&username';
2、查询角色包含哪些权限：
select *  from role_sys_privs where  role='&role';
3、查询用户系统权限：
select  *  from dba_sys_privs where  grantee='&username';
select  *  from dba_tab_privs where  grantee='&username';
--创建 dblink 权限
grant create database link to user ;
grant create public database link to user ;

删除用户：drop user xxx cascade;
解锁用户
alter user muar_rb account unlock identified by &password;

查看表空间下的用户
select distinct s.owner from dba_segments s where s.tablespace_name ='&tbsname'

select sum(bytes)/1024/1024/1024 gb from dba_segments;

select group#,members,bytes/1024/1024,status from v$log; 

 cd &dir
find ./ -ctime  3 |xargs rm
查看大于1024m的文件
find / -type f -size  1024m -print0 | xargs -0 du -h | sort -nr

--- 检查磁盘大小（单位m）
bootinfo -s hdisk0

--- 查看磁盘的详细信息
lsattr -el hdisk0

jiekexu:/dev#lsattr -el dev/hdisk322
lsattr: 0514-519 the following device was not found in the customized
        device configuration database:
        dev/hdisk322    
jiekexu:/dev#su - grid 
jiekexu:/home/grid$kfed read dev/rhdisk322 
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: kfbtyp_diskhead
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483953 ; 0x008: disk=305
kfbh.check:                  3956950460 ; 0x00c: 0xebda45bc
kfbh.fcn.base:                     2632 ; 0x010: 0x00000a48
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000

--- 检查权限 hdisk是块设备，而rhdisk是字符设备。
ls -l dev/hdisk*
【排序查看ls -ltr dev |grep rhdisk】

运行命令lsattr -e -a rw_timeout -f value -l name，查看rw_timeout的值。
jiekey1:/app/grid/diag/asm/ asm/ asm1/trace$lsattr -e -a rw_timeout -f value -l hdisk16
60
--- 检查pvid
lspv | grep hdiskn
--- 检查保留策略
lsattr -e -l hdisk5 | grep reserve_policy  
--- 查看磁盘是否为共享磁盘
lsattr -el hdisk0 
比对两个主机对应的磁盘号是否一致：unique_id
--- 查看磁盘是否可用
lspv
看pvid是否为none，若为none则数据库可用，再查看数据库当前有没有使用，若没有则可用来扩容asm磁盘组

svmon -u grid -w |more                       
svmon -u oracle -w | more 

svmon -po unit=gb |grep aioserver |wc
svmon -po unit=gb |grep oracle |wc

lsvg |lsvg -i -p
lsdev -c disk

lscfg -vpl hdisk40

-----某个节点的 awr
@?/rdbms/admin/awrrpti.sql
-----awr报告
@?/rdbms/admin/awrrpt.sql
-- awr 两个时间段的比较报告
@?/rdbms/admin/awrddrpt.sql
-----ash报告
@$oracle_home/rdbms/admin/ashrpt.sql
-----细粒度的 ash （enter value for slot_width:1）slot 槽为 1 的 ash
@$oracle_home/rdbms/admin/ashrpti.sql

关于physical reads ，db block gets 和 consistent gets 这三个参数之间有一个换算公式：
数据缓冲区的使用命中率=1 - ( physical reads  (db block gets   consistent gets) )。
用以下语句可以查看数据缓冲区的命中率：
select name, value from v$sysstat where name in ('db block gets', 'consistent gets','physical reads');

查询出来的结果 buffer cache 的命中率应该在 90％ 以上，否则需要增加数据缓冲区的大小。

set lines 500 pages 2000
col g_name format a10
col g_n format 99
col d_n format 999
col m_status format a7
col mo_status format a7
col h_status format a11
col name format a20
col path format a20
col failgroup format a15
select g.group_number g_n,
g.disk_number d_n,
g.name name,
g.failgroup,
g.mount_status m_status,
g.header_status h_status,
g.mode_status mo_status,
g.path ,
to_char(g.mount_date, 'yyyy/mm/dd hh24:mi:ss') m_date
from v$asm_disk g
order by g_n, d_n

select * from dba_role_privs where grantee='jkx_new_qry ';

select * from dba_sys_privs where grantee='skdata';
select * from dba_tab_privs where grantee='skdata';

开机自启动：
tmpfs      /dev/shm    tmpfs    defaults,size=20g   0 0
立即生效：
mount -t tmpfs shmfs -o size=20g dev/shm 

$ ps aux |sort -rnk3 |more
侦听器进程的pstack  truss  strace  tusc 注意110888.1如何跟踪unix系统调用
pstack <pid_of_listener> 

sun：truss -aefddo tmp/truss-lsnr.log -p <pid_of_listener> 
linux：strace -frt -o tmp/strace-lsnr.log -p <pid_of_listener> 
hp：tusc -aef -o tmp/tusc-lsnr.log -t“％h：％m：％s” -p <pid_of_listener> 

find ./ -name '*.dmp' -mtime  10 -exec rm -f {}

su - ddsdt 用户，vshmt -c 可以看到同步情况
/ddssoftware/ddt/ddsdt/bin/dds_pput -x
dds同步软件 类似于ogg的同步软件
10.10.19x.xxx  localhost
10.10.19x.xx     jiekeadg
jiekeadg:/app/oracle/diag/rdbms/picccash/picccash/trace$
hostname:/#su - ddsdt
jiekeadg:/ddssoftware/ddt/ddsdt/dt$vshmt -c
shm:
  shm-ver        : ver2.13.009
  type           : t
  multi_source   : 1
  size           : 34675744
  systemstat     : normal
  oracleloginstr : ddsdt/ddsdt
  db-charset     : 852
  db-n-charset   : 2000
  conv-charset   : 0
  authstr        : ddsdt/ddsdt
  dds_data       : /ddssoftware/ddt/ddsdt/dt
  audit          : no
  queuecount     : 2
  comm_param     : tcp:10.10.19x.xx:7915
  source         : tcp:10.10.19x.xxx:7910
  sourcewebport  : 8303
  sizelogfile    : 104857600
  reservetablecnt: 50
  put_retry_cnt  : 3
  alarm_wait_dtf : 300
  has_split_rids : 0
  large_table_blo: 1000
  lob_piece_len  : 8388608
  rm_blk_count   : 128
  putdata        : inqueue: 0:0 putok: 12510654:945115120084
  hisdata        : inqueue: 0:0 putok: 8893:87198847301
  chkdata        : inqueue: 0:0 putok: 0:0
  auddata        : inqueue: 0:0 putok: 0:0
  dbfinasm       : 1
  delay          : 0
  dml_mode       : rowid
  packbak        : 5
  modules        : 0x87
  dds_rb_hours   : 0
  machinetime    : 20190904182630
  inode-log      : 294
  inode-err      : 281
  disk-remain(mb): 512
  restart_mem(mb): 256
  dds_lobdir     : 
  sem_count      : 121
  sem_pput       : 8
  sem_pput_h     : 40
  sem_pput_v     : 72
  sem_pput_cmt   : 89
  max_num_pput   : 32
  max_num_pput_h : 32
  max_num_pput_v : 16
  max_num_pput_a : 1
  adjust_op      : 0
  index_retry_cou: 0
  index_retry_del: 60
  synctime0      : 2018/07/26 00:35:05
  scn-put00      : scn-0x83d659c71      sub:0   time:2019/09/04 10:29:57
  scn-put01      : scn-0x83d659c71      sub:0   time:2019/09/04 10:29:57
  stopputincremen: normal, load increments
  stopcreateindex: normal, load indexes
  total          : irp-160361545 urp-1100746241 drp-130759 ddl-7 txn-593096960
  delaytime      : min-28591 max-1353324 avg-229
  put-no-cur     : 136533
  put0           : 6296809/6296809
  put1           : 6296809/6296809
  his0           : 12033/12033 ats001
  waitcommit     : 0-n 1-n 
  src#0          : source       10.10.19x.xxx:7910:8303
jiekeadg:/ddssoftware/ddt/ddsdt/dt$

rman target 
run
{
allocate channel t1 type 'sbt_tape' parms 'env=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
restore archivelog sequence between 63145 and 63149 thread 1;
restore archivelog sequence between 62697 and 62700 thread 2;
release channel t1;
}

jdbc:oracle:thin:@(description=(address_list=(address=(host=10.1xx.xxx.xxx)(protocol=tcp)(port=1521))(address=(host=10.1xx.xxx.xxx)(protocol=tcp)(port=1521)))(load_balance=yes)(failover=yes)(connect_data=(server=dedicated)(service_name=jieker)))

impdp user/password dumpfile=temp.dmp directory=dump remap_tablespace=[old_table_space]:[new_table_space] 
old是指server1机器上的tablespace_name，new是指server2上的  

alter system switch logfile;
alter system switch logfile;
alter system checkpoint;

alter  system  set  memory_max_target=0 scope=spfile;
alter  system  set  memory_target =0 scope=spfile;
alter system set sga_max_size=13g scope=spfile;
alter system set sga_target=13g scope=spfile;
alter  system  set pga_aggregate_target=6g scope=spfile;

停 oem 服务再停数据库
/app/oms/bin/emctl start/stop oms
ps -ef | grep -i local=no | grep -v grep | awk '{print $2}'| xargs kill -9 

su - grid 
crsctl query crs softwareversion  --查看集群版本
asmcmd
lsdg
mount ocr
exit
crsctl start res ora.crsd -init

netstat -anop | grep established | awk '$4 ~/:1521/'

--解压 10g 的 cpio 类型安装软件
cpio -idmvc <*.cpio  ----cpio解压

cd &dir
find ./ -ctime  3 |xargs rm
查看大于20m的文件
find / -type f -size  20m -print0 | xargs -0 du -h | sort -nr

set pages 9999
set lines 200
select process,client_process,sequence#,thread#,status from v$managed_standby;
---mrp0当前正在应用的日志序列
select process,status,sequence# from v$managed_standby;
---查询dg应用情况
set linesize 150; 
set pagesize 20; 
column name format a13; 
column value format a20; 
column unit format a30; 
column time_computed format a30; 
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); 

set pages 9999
set lines 200
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
 select *
  from ( select a.instance_number,
               a.snap_id,
               b.begin_interval_time   0 begin_time,
               b.end_interval_time   0 end_time,
               round(value - lag( value, 1 , '0')
                     over(order by a.instance_number, a.snap_id)) "db time"
          from (select b.snap_id,
                       instance_number,
                       sum(value )  1000000  60 value
                  from dba_hist_sys_time_model b
                 where b.dbid = (select dbid from v$database)
                   and upper (b.stat_name) in upper(('db time' ))
                 group by b.snap_id, instance_number) a,
               dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and b.dbid = (select dbid from v$database)
           and b.instance_number = a.instance_number)
 where to_char(begin_time, 'yyyy-mm-dd') = to_char(sysdate , 'yyyy-mm-dd')
 order by begin_time; 

show parameter process
--查看最大连接数
select value from v$parameter where name ='processes'; 
--查两个节点连接数
select inst_id,count(*) from gv$session group by inst_id;
--查看起库以来最大连接数
select resource_name,max_utilization,limit_value from v$resource_limit where resource_name in ('processes','sessions');
--查看并发连接数
select inst_id,count(*) from gv$session where status='active' group by inst_id;
--查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;

col wait_class for a20
set lines 200 pages 200
col event for a60
select event,count(*),wait_class from v$session_wait group by event,wait_class order by 3;

select dest_name,error from v$archive_dest;

select to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') 启动时间,
trunc(sysdate-(startup_time))||'天'||trunc(24*((sysdate-startup_time)-trunc(sysdate-startup_time)))
||'小时'||mod(trunc(1440*((sysdate-startup_time)-
trunc(sysdate-startup_time))),60)
||'分'||mod(trunc(86400*((sysdate-startup_time)-
trunc(sysdate-startup_time))),60)
||'秒' 运行时间
from gv$instance;

select ceil((sysdate-next_time)*24*60) "m" 
from v$archived_log 
where applied='yes' 
and sequence#=
(select max(sequence#) from v$archived_log where applied='yes');

select a.name,to_char(a.created,'yyyy.mm.dd'),b.instance_name 
from gv$database a,gv$instance b 
where a.inst_id=b.inst_id;

select b.username,h.machine,to_char(h.sample_time,'yyyymmdd hh24:mi:ss') time,count(*)
 from dba_hist_active_sess_history h ,dba_users b
where h.user_id=b.user_id and b.username in('system','sys') and h.machine not in (select host_name from gv$instance)
and h.sample_time>to_date('20210208','yyyymmdd')
group by b.username,h.machine,to_char(h.sample_time,'yyyymmdd hh24:mi:ss');

select trunc(first_time,'dd'),count(1)
from v$log_history
where trunc(first_time,'dd')>sysdate-10
group by trunc(first_time,'dd')
order by 1;

--按天计算
select trunc(first_time,'dd') time,sum(blocks*block_size)/1024/1024/1024 total_gb from v$archived_log 
group by trunc(first_time,'dd') order by 1;    
--按小时计算
select trunc(first_time,'hh') time,sum(blocks*block_size)/1024/1024/1024 total_gb from v$archived_log 
group by trunc(first_time,'hh') order by 1;

select a.recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') begin_time,
b.recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,
round((b.first_time-a.first_time)*24*60,2) minutes
from v$log_history a,v$log_history b
where b.recid = a.recid 1 and to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') >=to_char(sysdate-10,'yyyy-mm-dd hh24:mi:ss') ;
desc dba_hist_active_sess_history; ----可以查询执行过的sql的主机

ps -ef | grep defunct

ps -ef | grep local=no | grep -v grep | awk '{print $2}' |xargs kill -9

errpt | head -20

rman target 
list backup of archivelog all;

set line 200 pages 1000
col message for a60
col target for a20
select sid,serial#,target,start_time,elapsed_seconds,time_remaining,message,round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where 1=1 --and sid=2983 and serial#=5
and time_remaining>0 ;

select t.username, t.account_status, t.created, t.default_tablespace from dba_users t 
where  username= 'abase' order by t.created desc;

run {
  allocate channel c1 type disk ;
  allocate channel c2 type disk ;
  allocate channel c3 type disk ;
  allocate channel c4 type disk ;
  backup  as compressed backupset database format '/backup/%d_%i_%s_%p.bak';
  backup  as compressed backupset archivelog all format '/backup/%d_%i_%s_%p.arc';
  backup current controlfile for standby format '/backup/%d_%i_%s_%p.ctl';
  release channel c1;
  release channel c2;
  release channel c3;
  release channel c4;
}

--压缩全库备份
backup database format='/u01/app/backup/rman/eweaverstb_%s.bak' filesperset 4;
run{
backup as compressed backupset database format='/u01/app/backup/ewverstb_%s.bak' plus archivelog delete input skip inaccessible;
}

--expdp导入导出
select * from dba_directories where directory_name ='data_pump_dir';
如果目录过小或者不存在,则
create directory expdp_dir as '/u01/app/backup/expdp_dir';
grant read,write on directory expdp_dir  to public;
expdp \'/ as sysdba\' directory=expdp_dir dumpfile=full_bak.dmp full=y;

impdp \'/ as sysdba\' directory=expdp_dir dumpfile=full_bak.dmp full=y;

impdb \'/ as sysdba\' directory=dump_dir dumpfile=full.dmp full=y;

--使用 par 文件全库导出
more full_expdp_query.par
directory=exp_dir1
logfile=full_new_exclude.log
dumpfile=full_new_exclude_%u.dmp 
exclude=table:"in('log2017','log20210115')"
query=go.policyattachments:"where createtime>=to_date('20200901','yyyymmdd')"
exclude=statistics
full=y 
parallel=8
cluster=n

nohup expdp \"/ as sysdba\" parfile=full_expdp_query.par & 

--导入
more full_impdp_query.par
directory=imp_dir1
logfile=impdp_full_query.log
dumpfile=full_expdp_query_%u.dmp 
cluster=n
parallel=8 


nohup impdp \"/ as sysdba\" parfile=full_impdp_query.par & 
expdp \'/ as sysdba\' directory=expdp_dir dumpfile=tbs_bak.dmp tables=(xxx,xxx)
expdp \'/ as sysdba\' directory=expdp_dir dumpfile=tbs_bak.dmp tables=\(prod.su_ualcompemresponinfo,prod.su_basicinfo,prod.su_sendxml,prod.su_sendxmlbatch\) fromuser=xxx touser=xxx log=tab.log
expdp \'/ as sysdba\' directory=tif_dp  dumpfile=tss_20190808.dmp logfile=tss_20190809.log  fromuser=tss touser=ts
expdp \'/ as sysdba\' directory=expdp_dir  dumpfile=bbruser.dmp logfile=bbruser.log  fromuser=bbruser touser=bbinfo 


--只导出全库索引
expdp \' / as sysdba \' directory=exp_dir dumpfile=index.dmp logfile=out_index.log cluster=n include=index full=y
export: release 11.2.0.3.0 - production on wed feb 3 14:45:50 2021


米乐app官网下载 copyright (c) 1982, 2011, oracle and/or its affiliates.  all rights reserved.
;;; 
connected to: oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production
with the partitioning, real application clusters, automatic storage management, olap,
data mining and real application testing options
flashback automatically enabled to preserve database integrity.
starting "sys"."sys_export_full_01":  "/******** as sysdba" directory=exp_dir dumpfile=index.dmp logfile=out_index.log cluster=n include=index full=y 
estimate in progress using blocks method...
total estimation using blocks method: 0 kb
processing object type database_export/schema/table/index/index
processing object type database_export/schema/table/index/functional_index/index
processing object type database_export/schema/table/index/statistics/index_statistics
processing object type database_export/schema/table/index/statistics/functional_index/index_statistics
processing object type database_export/schema/table/index/bitmap_index/index
processing object type database_export/schema/table/index/statistics/bitmap_index/index_statistics
master table "sys"."sys_export_full_01" successfully loaded/unloaded
******************************************************************************
dump file set for sys.sys_export_full_01 is:
  /oracle/soft/index.dmp
job "sys"."sys_export_full_01" successfully completed at 14:47:59


sqlfile参数
该参数可以用于impdp,主要作用是未真实在目标端执行导入的情况下,生成sql文件包含该dmp文件的所有ddl语句,使用语法为
sqlfile=[directory_object:]file_name
注意事项:
1.directory_object可以不和impdp的directory参数不一致,如果是一样,directory_object可以省略
2.sqlfile文件必须写入到磁盘之上,不能写入到asm中
3.sqlfile和query参数冲突,不能同时使用


--获取创建索引的 sql 
impdp \' / as sysdba \' directory=exp_dir sqlfile=cre_index.sql  dumpfile=index.dmp logfile=in_index.log cluster=n include=index
import: release 11.2.0.3.0 - production on wed feb 3 15:06:30 2021


米乐app官网下载 copyright (c) 1982, 2011, oracle and/or its affiliates.  all rights reserved.
;;; 
connected to: oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production
with the partitioning, real application clusters, automatic storage management, olap,
data mining and real application testing options
master table "sys"."sys_sql_file_full_01" successfully loaded/unloaded
starting "sys"."sys_sql_file_full_01":  "/******** as sysdba" directory=exp_dir dumpfile=index.dmp logfile=index.log cluster=n sqlfile=cre_index.sql include=index
processing object type database_export/schema/table/index/index
processing object type database_export/schema/table/index/functional_index/index
processing object type database_export/schema/table/index/statistics/index_statistics
processing object type database_export/schema/table/index/statistics/functional_index/index_statistics
processing object type database_export/schema/table/index/bitmap_index/index
processing object type database_export/schema/table/index/statistics/bitmap_index/index_statistics
job "sys"."sys_sql_file_full_01" successfully completed at 15:07:22


--获取创建索引的 sql 语句
set pages 9999
set line 9999
set long 9999
select dbms_lob.substr(dbms_metadata.get_ddl('index',index_name,'scott'))||';'
from dba_indexes
where owner='scott';

--导出序列
expdp \' / as sysdba \' directory=exp_dir dumpfile=sequence.dmp logfile=out_sequence.log cluster=n include=sequence full=y


--删除序列在导入
set line 120 pages 9999
spool drop_sequence.sql
select 'drop sequence '||sequence_owner||'.'||sequence_name||';' from dba_sequences where sequence_owner='scott';
spool off;
@drop_sequnece.sql
impdp \' / as sysdba \'  network_link=db_seq include=sequence


--按条件导出
expdp \'/ as sysdba\' directory=exp_dir1 logfile=full_query2021.log dumpfile=full_query_polic_%u.dmp tables=ecargo.policyattachments query=\"where createtime \>= to_date\(\'20200901\',\'yyyymmdd\'\)\" 


impdp bbrinfo/8t4v~p5=y directory=expdp_dir dumpfile=bbruser.dmp   remap_schema=bbruser:bbrinfo
import: release 10.2.0.1.0 - 64bit production on friday, 09 august, 2019 14:43:03
米乐app官网下载 copyright (c) 2003, 2005, oracle.? all rights reserved.
connected to: oracle database 10g enterprise edition release 10.2.0.1.0 - 64bit production
with the partitioning, olap and data mining options
ora-39001: invalid argument value
ora-39000: bad dump file specification
ora-39142: incompatible version number 3.1 in dump file "/home/oracle/piccatssix_20190808.dmp"


expdp \'/ as sysdba\' directory=exp_dir1 dumpfile=full_query2021_%u.dmp full=y logfile=full2021.log qurey= \"where  policyattachments.createtime>= to_date('20200901','yyyymmdd')\"


impdp scott/tiger ?network_link=test1? tables=scott.a? directory=backup? remap_table=a:a1 query="'where hiredate<= to_date(''31-05-1981'', ''dd-mm-yyyy'') and hiredate>to_date(''31-01-1981'', ''dd-mm-yyyy'') '"? logfile=test.log ;  


query=bi_dw.dw_mid_contract:"where data_date in(to_date('2013-11-13','yyyy-mm-dd'),to_date('2013-11-14','yyyy-mm-dd'),to_date('2013-8-13','yyyy-mm-dd'),to_date('2013-8-14','yyyy-mm-dd'))"


expdp \'/ as sysdba\' directory=exp_dir1 logfile=full_query2021.log dumpfile=full_query_%u.dmp tables=go.policyattachme query=\"where createtime>= to_date('20200901','yyyymmdd') \" parallel=8 cluster=n


源端数据库版本11.2.0.4.0,目标端数据库版本10.2.0.4.0，源端数据库版本高于目标端数据库版本，源端导出数据时加上目标端version：
set lines 300
col owner_name for a10
col operation for a15
col job_mode for a20
col state for a15
select * from dba_datapump_jobs; 


expdp  wxlun/wxlun123 tables=wxlun.wxlun_tab01,wxlun.wxlun_tab02 directory=expdp dumpfile=wxlun1212.dmp logfile=wxlun1212.log version=10.2.0.4.0
impdp  scott/tiger  directory=expdp  dumpfile=wxlun1212.dmp logfile=wxlun1212.log remap_schema=wxlun:scott  remap_tablespace=wxlun:users
--cp冷备数据文件 控制文件 参数文件 v$datafile,v$controlfile;

set line 200
col start_time for a30
col end_time for a30
col status for a10
select session_key,
input_type,
status,
to_char(start_time, 'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time, 'yyyy-mm-dd hh24:mi') end_time,
elapsed_seconds / 3600
from v$rman_backup_job_details
where to_char(start_time, 'yyyy-mm-dd hh24:mi') > to_char(sysdate-12,'yyyy-mm-dd hh24:mi')
order by session_key;

select sum(tablespace_usedsize)*8/1024/1024,substr(rtime,0,10)
from dba_hist_tbspc_space_usage
where snap_id in (select max(snap_id) from dba_hist_tbspc_space_usage group by substr(rtime,0,10) ) --and tablespace_id=7
group by substr(rtime,0,10) order by 1 ;
--清空shared_pool缓冲
alter system flush shared_pool ;
--删除已备份归档
rman target /
delete archivelog all backed up 1 times to device type 'sbt_tape';
关于密码错误验证延迟特性：
密码错误验证延迟，可以通过设置event="28401 trace name context forever, level 1" 
alter system set events='28401 trace name context forever, level 1';   ---不需要重启

su - oracle
sqlplus / as sysdba
archive log list;


1、归档路径
①如果没有归档存放存储，需要操作系统挂载存储再继续操作
②如果归档存储存在，但并非所想存放的实际路径，可在相关存储下创建目录
mkdir -p /app/oracle/arch
修改归档目录权限
chown -r oracle:oinstall  /app/oracle/arch


③如果归档存储存在，且路径正确，权限正确则可以直接使用


2、备份spfile文件
create pfile from spfile;
3、修改归档路径到想存放的位置，如查询出的归档路径已经是正确的位置，则不需要修改。
su - oracle
sqlplus / as sysdba
alter system set log_archive_dest_1='location=/app/oracle/arch'  scope=spfile sid='*';
4、重启数据库，开启/关闭归档模式（rac两个节点均关闭，使用一个节点修改，修改完成后启动另一个节点）
shutdown immediate;
startup mount;
5、归档模式修改：
开启归档模式：
alter database archivelog;
关闭归档模式：
alter database noarchivelog;
alter database open;
如果为集群：需要停止两个节点，在一个节点操作完成后，拉起另一个节点，操作步骤如上，
rac集群启动另一个节点：startup


6、查看数据库归档路径
su - oracle
sqlplus / as sysdba
archive log list;
切换日志，查看归档目录下产生归档文件
alter system switch logfile；
7、关闭归档
shutdown immediate;
startup mount;
--开启归档模式：
--alter database archivelog;
关闭归档模式：
alter database noarchivelog;

①rman全备脚本部署
vi /home/oracle/full_backup.sh
oracle_base=/app/oracle
oracle_home=/app/product/11.2.0/db
oracle_sid=jiekexu1
db_name=jiekexu
path=$path:$oracle_home/bin/


v_date=`date  %y%m%d`
[ -d /backup/$db_name/$v_date ] || mkdir -p /app/backup/$db_name/$v_date


rman target /< /app/backup/$db_name/$v_date/full_$v_date.log
run{
allocate channel c1 device type disk;
backup database format '/app/backup/%d/%t/full_%d_%t_%s_%p.bak';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all delete input format '/app/backup/%d/%t/full_arch_%d_%t_%s_%p.bak';
backup current controlfile format '/app/backup/%d/%t/full_controlfile_%u';
backup spfile format '/app/backup/%d/%t/full_spfile_%d_%t_%s_%p.bak';
release channel c1;
}
eof


②赋权
chmod  x full_backup.sh

以下地址均可找到我：

————————————————————————————
公众号：jiekexu之路
墨天轮：https://www.modb.pro/u/4347
csdn ：https://blog.csdn.net/jiekexu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————