| oracle

Oracle

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
# oracle log 
select log_mode,force_logging from v$database;
alter database force  logging;     
insert /*+append parallel(32)*/ into tab nologging 
alter database {add|drop} supplemental log data;
alter table test drop supplemental log data(all,primary key,unique,foreign key) columns;
# archive log 
select * from v$flash_recovery_area_usage;
DELETE  noprompt ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3';
# 文件存在 但是数据库中没有
CATALOG RECOVERY AREA NOPROMPT;
# 数据库有，但是物理文件失效等
CROSSCHECK ARCHIVELOG ALL;
LIST EXPIRED ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
LIST ARCHIVELOG ALL;
# 物理删除
asmcmd
rm xxx
# 迁移表空间
alter tablespace test offline;
cp datafile  newdatafile;
alter tablespace test rename datafile datafile to newdatafile
alter tablespace test online;
ora-01113 文件10 需要介质修复，输入语句：recover datafile 10;
# oracle rac 
##  profile 生效
aix：. /home/grid/.profile  
linux：source /home/grid/.bash_profile
## 操作系统日志
aix：errpt -a
linux：dmesg -l err
       sudo journalctl -p err
## rac 集群 ohas、cssd、crsd、evm
ps -ef |grep -E "has|css|crs|evm"
crsctl check|start|stop cluster -all
crsctl check|start|stop cluster -n node1
crsctl stats res -t 
crsctl stats|start|stop res xxxx -n node1
crsctl get log crs all
## node节点状态 n编号 s状态
olsnodes -n -s 
## srvctl 
srvctl status|start|stop database -d dbname -o immediate
srvctl status|start|stop instance -d dbname -i instance_name
srvctl status|start|stop asm 
srvctl status|start|stop asm -n node_name
srvctl status listener
srvctl status vip -n node1 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 常用sql
select level as xh  from dual connect by level <= 8;
##lag 忽略空
select a.*, nvl(test, lag(a.test ignore nulls  ) over(order by xh))  from cs12 a order by a.xh;
## 替换中文
select regexp_replace('1测试S01','[' || unistr('\0391') || '-' || unistr('\9fa5') || ']','') from  dual;
## expdp
nohup expdp  \"/ as sysdba\"  PARALLEL=16 cluster=no COMPRESSION=ALL DUMPFILE=CS_%U.dmp  DIRECTORY=NEW_DIR logfile=cs.log schemas=cs_user  \ query= \"where  lrrq\>\=to_date\(\'2022-01-01 00:00:00\'\,\'yyyy-mm-dd hh24:mi:ss\'\) or  \(xgrq\>\=to_date\(\'2022-01-01 00:00:00\'\,\'yyyy-mm-dd hh24:mi:ss\'\) \)\" >  cs.out 2>&1 &;
# stats 
EXEC dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TABLENAME',cascade => TRUE,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',degree=>16 );
#查看某条SQL执行计划
select a.SQL_TEXT, a.SQL_FULLTEXT, a.ADDRESS || ',' || a.HASH_VALUE
  from v$sqlarea a
 where sql_fulltext like '%and t.cs = :1%';
select * from v$sql_plan where sql_id = '2n2yxg7h0chdt';
# 清除旧的执行计划
exec sys.dbms_shared_pool.purge('0700000B4EBA9AC8,3758506425', 'c');
ESC