col file_name for a55select 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 sysasmselect 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 999col PATH for a33col NAME for a15col FAILGROUP for a15select 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 FROMDBA_FREE_SPACEGROUP BY tablespace_name ) a,(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILESGROUP BY tablespace_name) bWHERE a.tablespace_name=b.tablespace_nameORDER BY 4;

col TABLE_NAME for a30set pagesize 200set linesize 200col TABLE_NAME for a30set linesize 200select * 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_MBfrom 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                                                                        11136SYS                            XXXXX_ORIGINNOTE_IX                                                                     9152SYS                            XXXXX_ABSTRACT_IX                                                                       6388SYS                            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                                                                 INDEXSYS                            XXXXX_ORIGINNOTE_IX                                                               INDEXSYS                            XXXXX_ABSTRACT_IX                                                                 INDEXSYS                            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';--统计活动的undoselect 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 a55select 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_timeFROM v$locked_object l, all_objects o, v$session sWHERE l.object_id = o.object_idAND l.session_id = s.sidORDER 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|morelparstat -iortconfAIX 磁盘扫描命令root 执行cfgmgr -v

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

AIX 查看错误日志并输出到 messagesJIekeXuY1:/#errpt -dHIDENTIFIER TIMESTAMP T C RESOURCE_NAME DESCRIPTIONDE3B8540 1012214419 P H hdisk18 PATH HAS FAILED4B436A3D 1012214119 T H fscsi0 LINK ERROR4B436A3D 1012214119 T H fscsi0 LINK ERROR4B436A3D 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 hdisk12HP：machinfo（diskinfo /dev/rdisk/disk*  查看磁盘大小）WIN：msinfo32SUSE：cat /proc/cpuinfo  （model name ）---查看资源使用情况：HP：glance/topAIX：nmon/topas
--AIX查看版本号oslevel JIEKEXuR2:/app/product/11.2.0/grid/network/log$oslevel6.1.0.0---查看内存大小：HP：/usr/contrib/bin/machinfo | grep -i MemoryAIX: /usr/sbin/lsattr -E -l sys0 -a realmem---查看swap分区：HP：/usr/sbin/swapinfo -aAIX：/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 500col USERNAME for a15col ACCOUNT_STATUS for a15select 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 &DIRfind ./ -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/hdisk322lsattr: 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: 0x00kfbh.hard:                          130 ; 0x001: 0x82kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEADkfbh.datfmt:                          1 ; 0x003: 0x01kfbh.block.blk:                       0 ; 0x004: blk=0kfbh.block.obj:              2147483953 ; 0x008: disk=305kfbh.check:                  3956950460 ; 0x00c: 0xebda45bckfbh.fcn.base:                     2632 ; 0x010: 0x00000a48kfbh.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 hdisk1660--- 检查PVIDlspv | 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 |wcsvmon -PO unit=GB |grep oracle |wc
lsvg |lsvg -i -plsdev -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 2000col g_name format a10col g_n format 99col d_n format 999col m_status format a7col mo_status format a7col h_status format a11col name format a20col path format a20col failgroup format a15select 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_datefrom v$asm_disk gorder 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 -xDDS同步软件 类似于OGG的同步软件10.10.19X.XXX  localhost10.10.19X.XX     JiekeADGJiekeADG:/app/oracle/diag/rdbms/picccash/picccash/trace$hostname:/#su - ddsdtJiekeADG:/ddssoftware/ddt/ddsdt/dt$vshmt -cSHM:  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:8303JiekeADG:/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 omsPs -ef | grep -i local=no | grep -v grep | awk '{print $2}'| xargs kill -9

su - grid crsctl query crs softwareversion  --查看集群版本asmcmdlsdgmount ocrexitcrsctl start res ora.crsd -init

netstat -anop | grep ESTABLISHED | awk '$4 ~/:1521/'
--解压 10g 的 cpio 类型安装软件cpio -idmvc <*.cpio  ----cpio解压

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

set pages 9999set lines 200select 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 9999set lines 200alter 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 a20set lines 200 pages 200col event for a60select 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 bwhere 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_historywhere trunc(FIRST_TIME,'dd')>sysdate-10group 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) minutesfrom v$log_history a,v$log_history bwhere 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 1000col MESSAGE for a60col TARGET for a20select sid,SERIAL#,TARGET,START_TIME,ELAPSED_SECONDS,TIME_REMAINING,MESSAGE,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"from v$session_longopswhere 1=1 --and sid=2983 and SERIAL#=5and 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.pardirectory=exp_dir1logfile=full_new_exclude.logdumpfile=full_new_exclude_%U.dmp exclude=table:"in('LOG2017','LOG20210115')"query=GO.POLICYATTACHMENTS:"where createtime>=to_date('20200901','YYYYMMDD')"exclude=statisticsfull=y parallel=8cluster=N
nohup expdp \"/ as sysdba\" parfile=full_expdp_query.par & 
--导入more full_impdp_query.pardirectory=imp_dir1logfile=impdp_full_query.logdumpfile=full_expdp_query_%U.dmp CLUSTER=Nparallel=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.logexpdp \'/ as sysdba\' directory=TIF_DP  dumpfile=TSS_20190808.DMP logfile=TSS_20190809.log  fromuser=TSS touser=TSexpdp \'/ 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=yExport: Release 11.2.0.3.0 - Production on Wed Feb 3 14:45:50 2021
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 ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsFLASHBACK 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 KBProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICSProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICSMaster table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_FULL_01 is:  /oracle/soft/index.dmpJob "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=INDEXImport: Release 11.2.0.3.0 - Production on Wed Feb 3 15:06:30 2021
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 ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsMaster table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloadedStarting "SYS"."SYS_SQL_FILE_FULL_01":  "/******** AS SYSDBA" directory=exp_dir dumpfile=index.dmp logfile=index.log cluster=n sqlfile=cre_index.sql include=INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICSProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICSJob "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 15:07:22
--获取创建索引的 SQL 语句set pages 9999set line 9999set long 9999SELECT dbms_lob.substr(dbms_metadata.get_ddl('INDEX',INDEX_NAME,'SCOTT'))||';'from dba_indexeswhere 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 9999spool drop_sequence.sqlSELECT 'DROP SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||';' FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='SCOTT';spool off;@drop_sequnece.sqlimpdp \' / 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:bbrinfoImport: Release 10.2.0.1.0 - 64bit Production on Friday, 09 August, 2019 14:43:03Copyright (C) 2003, 2005, Oracle.? All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsORA-39001: invalid argument valueORA-39000: bad dump file specificationORA-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 300col OWNER_NAME for a10col OPERATION for a15col JOB_MODE for a20col STATE for a15select * 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.0impdp  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 200col START_TIME for a30col END_TIME for a30col status for a10select 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 / 3600from v$rman_backup_job_detailswhere 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_USAGEwhere snap_id in (select max(snap_id) from DBA_HIST_TBSPC_SPACE_USAGE group by substr(rtime,0,10) ) --and TABLESPACE_ID=7group 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 - oraclesqlplus / as sysdbaarchive log list;
1、归档路径①如果没有归档存放存储，需要操作系统挂载存储再继续操作②如果归档存储存在，但并非所想存放的实际路径，可在相关存储下创建目录mkdir -p /app/oracle/arch修改归档目录权限chown -R oracle:oinstall  /app/oracle/arch
③如果归档存储存在，且路径正确，权限正确则可以直接使用
2、备份spfile文件create pfile from spfile;3、修改归档路径到想存放的位置，如查询出的归档路径已经是正确的位置，则不需要修改。su - oraclesqlplus / as sysdbaalter 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 - oraclesqlplus / as sysdbaarchive log list;切换日志，查看归档目录下产生归档文件alter system switch logfile；7、关闭归档shutdown immediate;startup mount;--开启归档模式：--alter database archivelog;关闭归档模式：alter database noarchivelog;

①RMAN全备脚本部署vi /home/oracle/full_backup.shORACLE_BASE=/app/oracleORACLE_HOME=/app/product/11.2.0/dbORACLE_SID=JiekeXu1db_name=JiekeXuPATH=$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.logrun{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
————————————————————————————

使用数据泵导出时遇到 ORA-27054 错误解决办法

案例|RAC 添加表空间误将数据文件放本地处理办法

11g RAC 在线存储迁移实现 OCR 磁盘组完美替换

震惊：Oracle 11gR2 RAC ADG 并没有高可用

如何通过 Shell 监控异常等待事件和活跃会话 

我的 OCM 之路|书写无悔青春追梦永不止步

Oracle 19c 之多租户 PDB 连接与访问（三）

Oracle 12C 最新补丁下载与安装操作指北

DBA 常用的软件工具有哪些（分享篇）？

深入了解 Oracle Flex ASM 及其优点