加群问题

给出下面SQL的运行结果

此题略微需要一些动手能力



select max(power(rownum*2,2))/666663842782 ans from dual connect by level <=19666666;

 

 

自己写的RMAN备份脚本

一套自己写的rman备份脚本,包含全量、增量和归档日志备份,备份策略为每7天一个全备份,2天一个增量备份和每8小时一次归档备份,并删除一个月前的备份和归档。

 

 



crontab 



0 0,8,16 * * * sh /home/oracle/backup_script/backup-archive.sh >> /home/oracle/backup_script/backup-archive.log
0 0 7,14,21,28 * * sh /home/oracle/backup_script/backup-full.sh >> /home/oracle/backup_script/backup-full.log
0 0 2,4,6,8,10,12,16,18,20,22,24,26 * * sh /home/oracle/backup_script/backup-inc.sh >> /home/oracle/backup_script/backup-inc.log




FULL 

sql 'alter system archive log current';
sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET incremental level 0  database tag ORCL_FULL format '/oracle_bak/oradir/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
backup as compressed backupset tag ORCL_ARCHIVE format '/oracle_bak/oradir/%d_%T_%s_%p_ARCHIVE' archivelog all not backed up delete all input ;
delete backup of archivelog until time='sysdate-30';
backup tag ORCL_CONTROL current controlfile format '/oracle_bak/oradir/%d_%T_%s_%p_CONTROL';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}




INC


sql 'alter system archive log current';
sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET incremental level 1  database tag ORCL_INC1 format '/oracle_bak/oradir/%d_%T_%s_%p_INC1' ;
sql 'alter system archive log current';
backup tag ORCL_CONTROL current controlfile format '/oracle_bak/oradir/%d_%T_%s_%p_CONTROL';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}



ARCHIVE 

sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";
RUN
{
allocate channel c1 type disk MAXOPENFILES=1 parms ' BLKSIZE=1048576 ';
allocate channel c2 type disk MAXOPENFILES=1 parms ' BLKSIZE=1048576 ';
allocate channel c3 type disk MAXOPENFILES=1 parms ' BLKSIZE=1048576 ';
allocate channel c4 type disk MAXOPENFILES=1 parms ' BLKSIZE=1048576 ';
sql 'alter system archive log current';
backup as compressed backupset tag ORCL_ARCHIVE format '/oracle_bak/oradir/%d_%T_%s_%p_ARCHIVE' archivelog all not backed up delete all input ;
delete backup of archivelog until time='sysdate-30';
backup tag ORCL_CONTROL current controlfile format '/oracle_bak/oradir/%d_%T_%s_%p_CONTROL';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

软件产品更新下载

 

DBRECOVER for Oracle恢复工具最新版: https://www.parnassusdata.com/zh-hans/node/1343

DBRECOVER for MySQL恢复工具最新版:https://www.parnassusdata.com/zh-hans/node/1343

DSR For Oracle 恢复工具最新版:https://www.parnassusdata.com/zh-hans/node/1343

 

看了TENET电影做个逆向的Oracle数据库周边

10:00 Oracle instance shutdown complete
10:01 database closed
10:02 shutdown issued by user
10:03 SCN 99999
10:04 SCN 99000
10:05 SQL result from client back to server
10:06 SQL fetch back to disk
10:07 SQL cursor back to parse
10:08 SQL optimize=>semantic parse=>syntax parse
10:09 user issue SQL:  ;pme morf * tceles
10:10+NN database opened
10:10+NN redo scan , rolling forward
10:10+NN database mounted
10:10+NN instance nomount start
10:10+NN user issue in SQLPLUS: ;putrats

 

 

Oracle 各种删除操作对空间返还的说明

Oracle 各种删除操作对空间返还的说明
操作 表空间是否回收空间? 文件系统或ASM是否回收空间? 是否造成表上的碎片? 在本地管理表空间(LMT 9i以后)是否造成表空间碎片? 在字典管理表空间(DMT 9i以前)是否造成表空间碎片?注意现在的oracle版本不太可能用DMT 是否造成索引碎片? 有心理问题,或者为应付领导,一定要对付对付不存在的碎片怎么办?
DELETE SQL 否,空间可以被该表重用。可以称之为高水位,但谈不上碎片 不适用 不适用 对表可以shrink space;对索引可以coalesce操作;对于大表而言IO和redo会很多,耗时也可能长
DROP TABLE 是的;视乎recyclebin参数是否进入回收站;但空间都可以被表空间重用 都没表了 本地管理表空间的extent是统一大小或系统自动分配大小,不存在表空间碎片 可能导致碎片(alter tablespace coalesce适用场景) 索引都没了 不适用
TRUNCATE TABLE 默认是的 本地管理表空间的extent是统一大小或系统自动分配大小,不存在表空间碎片 可能导致碎片(alter tablespace coalesce适用场景) 不适用
注:Oracle除非手动resize datafile,否则一般不会自动返回空间给文件系统或ASM

 

 

 

Oracle 各种删除操作对空间返还的说明

OraGlance一个轻量级Oracle性能监控工具

OraGlance是免费的,你可以安心使用它。

OraGlance is free software , use it as you like.

 

下载地址URL:https://zcdn.askmac.cn/OraGlance2103.zip

 

2021-03-04:  现在可以 针对任意SQL_ID查看SQL Detail了

 

 

 

OraGlance的目标

 

  • 完全免费
  • 一键即运行的图形化Oracle数据库性能监控软件
  • 无需安装、部署,不占用服务器资源
  • 支持从Oracle 11.2.0.1开始的所有版本
  • 支持Oracle RAC
  • 非植入式,对Oracle只读,不在数据库内创建任何对象
  • 资源占用极低,内存使用在50MB左右,CPU占用率极低
  • 可回溯历史性能数据,可以观察到过往时间的性能问题
  • 提供SQL历史运行情况历史
  • 提供SQL优化接口
  • 无需外网访问权限,纯本地程序,不上传任何数据到任何服务器

 

产品比较

 

OraGlance Enterprise Manager 其他第三方监控软件
价格 完全免费 费用包含在db license中 基于license或订阅收费
性能指标 追求精简 12c以后的express版精简,12c以前较为全面 追求全面
性能负载 极低,内存小于50MB,cpu在1%左右 基于java,内存和cpu使用略高 B/S架构情况下普遍负载略高
响应速度 极快 正常情况下较快 正常情况下较快
部署情况 无需部署,一键使用 需要少量部署维护 一般需要单独部署
是否需要AGENT 完全不需要 cloud control需要安装agent 可能需要
是否在库内创建对象和写数据 完全不创建 原生存在部分对象,例如sysman 大部分需要
是否最小权限 只需要几个视图的查询权限 需要比较高的权限 可能需要读写权限
是否往需要访问外网 完全不需要 常规使用下完全不需要 可能需要访问外网获得完整功能
是否可以回溯监控历史 可以 部分可以 可能可以
当数据库hang时是否能监控 只要连接未被中断,除非极端情况,否则一直可监控性能;例如AWR快照已经无法写入的情况,则仍可以收集到丢失的AWR数据 可能完全卡死 可能完全卡死


OraGlance的使用技巧

 

  • 主面板每10秒钟自动刷新一次,可以通过左侧面板的Update够选项,临时关闭面板刷新;关闭面板刷新后仍会每10秒钟更新后台数据
  • “<< ONE Minute”按钮可以让主面板数据回溯到之前的时间,回溯后主面板将停止更新,直到使用”backup to current”回到当前时间
  • 可以在左侧时间栏中指定时间,并按下”go to above time”按钮来回溯到过去某个时间点;如果对应时间点不可用则会报错
  • 支持托盘运行,可以监控桌面上后台运行,不打扰用户
  • 例如18:00用户反应出现大量应用程序等待,则可以回溯到该时间点来观察TOP SQL和阻塞情况。

 

同时支持多开程序,可以在同一台pc上多开监控多个数据库:

 

 

SQL优化接口

 

  • 在主面板上点击View Detail按钮,可以进入对应SQL的优化界面
  • 优化界面中显示了SQL的历史运行情况,包括逻辑读、物理读、运行时间,执行计划HASH等
  • 点击Run Advisor按钮可以对该SQL执行SQL Tuning Task优化作业
  • 之后点击View Result可以查看优化建议结果,以下为可能的几种结果:

–建议重写该SQL

–建议收集对应的统计信息

–给出SQL Profile以改善执行计划

–建议添加对应的索引

–无建议

 

 

 

 

OraGlance是一个轻量级的图形化Oracle性能监控工具。由诗檀软件开发,它致力于提供简单有效的几个指标来监控Oracle数据库,而不提供过多的指标。

它是绿色的,非植入式的;你可以直接运行它,而基本不需要做任何准备工作。

 

 

 

不需要在Oracle数据库内创建任何对象,其只需要以下几个查询权限:

OraGlance is a lightweight oracle performance monitor tool , developed by parnassusdata.com . It will only focus on most important metric .

you can easily run it without any prerequisite. It will ask for below permission:

supports oracle version:  11gR2 12c

grant create session to pd1;
grant select on gv_$active_session_history to pd1;
grant select on gv_$SQL to pd1;
grant select on gv_$SQL_MONITOR to pd1;
grant select on gv_$SQLSTATS to pd1;
grant select on v_$database to pd1;
grant select on gv_$instance to pd1;
grant select on gv_$statname  to pd1;
grant select on gv_$sysstat  to pd1;
grant select on gv_$osstat  to pd1;
grant select on gv_$dlm_misc  to pd1;
grant select on gv_$session_blockers to pd1;
grant select on dba_hist_sqltext to pd1;
grant select on dba_hist_sqlstat to pd1;
grant select on dba_hist_snapshot to pd1;
grant select on dba_advisor_sqlstats to pd1;
grant select on dba_sqlset_statements to pd1;


如果要运行SQL Tuning Advisor 则需要授予dba权限

grant dba to pd1;

update log:

适配了12c,增加了登录界面保存,增加了托盘功能,优化了界面, 为sqlite中的表增加了索引。

 

build 2020-07-01         :  https://zcdn.askmac.cn/OraGlance200701.zip

 

2020-07-08:

  1. 现在oraglance支持最老的版本是11.2.0.1了 ,
  2. 现在支持对top sql的历史执行情况查看了
  3. 可以在查看SQL情况页面 调用sql tuning advisor 优化SQL语句 并给出建议了

build 2020-07-08:              https://zcdn.parnassusdata.com/OraGlance200708.zip

 

2020-07-09:

现在登陆密码会以密文形式存放了

 

Oracle Bulk Insert Tuning Test log

SQL> insert into   abc select * from dba_objects;
insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
86977 rows created.

Elapsed: 00:00:00.33
SQL> 
86977 rows created.

Elapsed: 00:00:00.16
SQL> 

Commit complete.

Elapsed: 00:00:00.01
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
173954 rows created.

Elapsed: 00:00:00.26
SQL> 

Commit complete.

Elapsed: 00:00:00.00
SQL> 
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
347908 rows created.

Elapsed: 00:00:00.40
SQL> 

Commit complete.

Elapsed: 00:00:00.01
SQL> 
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;

695816 rows created.

Elapsed: 00:00:00.87
SQL> 
Commit complete.

Elapsed: 00:00:00.00
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;

1391632 rows created.

Elapsed: 00:00:01.40
SQL> 
Commit complete.

Elapsed: 00:00:00.00
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;

2783264 rows created.

Elapsed: 00:00:02.63
SQL> 
Commit complete.

Elapsed: 00:00:00.00
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;

5566528 rows created.

Elapsed: 00:00:05.40
SQL> 
Commit complete.

Elapsed: 00:00:00.01
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;

11133056 rows created.

Elapsed: 00:00:10.87
SQL> 
Commit complete.

Elapsed: 00:00:00.00
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;

22266112 rows created.

Elapsed: 00:00:19.88
SQL> 
Commit complete.

Elapsed: 00:00:00.02
SQL> 
SQL> 
SQL> 
SQL> desc dba_objects;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER						    VARCHAR2(30)
 OBJECT_NAME					    VARCHAR2(128)
 SUBOBJECT_NAME 				    VARCHAR2(30)
 OBJECT_ID					    NUMBER
 DATA_OBJECT_ID 				    NUMBER
 OBJECT_TYPE					    VARCHAR2(19)
 CREATED					    DATE
 LAST_DDL_TIME					    DATE
 TIMESTAMP					    VARCHAR2(19)
 STATUS 					    VARCHAR2(7)
 TEMPORARY					    VARCHAR2(1)
 GENERATED					    VARCHAR2(1)
 SECONDARY					    VARCHAR2(1)
 NAMESPACE					    NUMBER
 EDITION_NAME					    VARCHAR2(30)






SQL> set linesize 300 pagesize 2000 
SQL> 
SQL> select count(*) from abc;

  COUNT(*)
----------
  44532224

Elapsed: 00:00:04.26

Execution Plan
----------------------------------------------------------
Plan hash value: 1045519631

-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |   172K  (1)| 00:34:34 |
|   1 |  SORT AGGREGATE    |	  |	1 |	       |	  |
|   2 |   TABLE ACCESS FULL| ABC  |    44M|   172K  (1)| 00:34:34 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
     635793  consistent gets
     635777  physical reads
	  0  redo size
	529  bytes sent via SQL*Net to client
	519  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL> select /*+ parallel */ count(*) from abc;

  COUNT(*)
----------
  44532224

Elapsed: 00:00:03.19

Execution Plan
----------------------------------------------------------
Plan hash value: 2285262752

--------------------------------------------------------------------------------------------------------
| Id  | Operation	       | Name	  | Rows  | Cost (%CPU)| Time	  |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |	  |	1 | 31976   (1)| 00:06:24 |	   |	  |	       |
|   1 |  SORT AGGREGATE        |	  |	1 |	       |	  |	   |	  |	       |
|   2 |   PX COORDINATOR       |	  |	  |	       |	  |	   |	  |	       |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |	1 |	       |	  |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |	  |	1 |	       |	  |  Q1,00 | PCWP |	       |
|   5 |      PX BLOCK ITERATOR |	  |    44M| 31976   (1)| 00:06:24 |  Q1,00 | PCWC |	       |
|   6 |       TABLE ACCESS FULL| ABC	  |    44M| 31976   (1)| 00:06:24 |  Q1,00 | PCWP |	       |
--------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


Statistics
----------------------------------------------------------
	 19  recursive calls
	  0  db block gets
     636281  consistent gets
     635777  physical reads
	  0  redo size
	529  bytes sent via SQL*Net to client
	519  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed




22266112 rows inserted in 19.88s. PC server virtual box , about 150MB/s disk space written. physical read 635777 blocks( 4.85g) in 3.19s, 1.52GB/s.

 

 

[oracle@ocp ~]$ cat /proc/cpuinfo 
processor	: 0
vendor_id	: GenuineIntel
cpu family	: 6
model		: 158
model name	: Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz
stepping	: 9
cpu MHz		: 3599.883
cache size	: 8192 KB
physical id	: 0
siblings	: 3
core id		: 0
cpu cores	: 3
apicid		: 0
initial apicid	: 0
fpu		: yes
fpu_exception	: yes
cpuid level	: 22
wp		: yes
flags		: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 movbe popcnt aes xsave avx rdrand lahf_lm abm 3dnowprefetch fsgsbase avx2 invpcid rdseed
bogomips	: 7199.76
clflush size	: 64
cache_alignment	: 64
address sizes	: 39 bits physical, 48 bits virtual
power management:

processor	: 1
vendor_id	: GenuineIntel
cpu family	: 6
model		: 158
model name	: Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz
stepping	: 9
cpu MHz		: 3599.883
cache size	: 8192 KB
physical id	: 0
siblings	: 3
core id		: 1
cpu cores	: 3
apicid		: 1
initial apicid	: 1
fpu		: yes
fpu_exception	: yes
cpuid level	: 22
wp		: yes
flags		: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 movbe popcnt aes xsave avx rdrand lahf_lm abm 3dnowprefetch fsgsbase avx2 invpcid rdseed
bogomips	: 7199.76
clflush size	: 64
cache_alignment	: 64
address sizes	: 39 bits physical, 48 bits virtual
power management:

processor	: 2
vendor_id	: GenuineIntel
cpu family	: 6
model		: 158
model name	: Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz
stepping	: 9
cpu MHz		: 3599.883
cache size	: 8192 KB
physical id	: 0
siblings	: 3
core id		: 2
cpu cores	: 3
apicid		: 2
initial apicid	: 2
fpu		: yes
fpu_exception	: yes
cpuid level	: 22
wp		: yes
flags		: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 movbe popcnt aes xsave avx rdrand lahf_lm abm 3dnowprefetch fsgsbase avx2 invpcid rdseed
bogomips	: 7199.76
clflush size	: 64
cache_alignment	: 64
address sizes	: 39 bits physical, 48 bits virtual
power management:

[oracle@ocp ~]$ cat /proc/meminfo 
MemTotal:       15238052 kB
MemFree:         1185404 kB
Buffers:           36968 kB
Cached:         13501972 kB
SwapCached:            0 kB
Active:          9674364 kB
Inactive:        4176152 kB
Active(anon):    9564472 kB
Inactive(anon):  1782020 kB
Active(file):     109892 kB
Inactive(file):  2394132 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:       4128764 kB
SwapFree:        4128764 kB
Dirty:                 4 kB
Writeback:             0 kB
AnonPages:        311584 kB
Mapped:           439612 kB
Shmem:          11034924 kB
Slab:              67436 kB
SReclaimable:      48416 kB
SUnreclaim:        19020 kB
KernelStack:        2192 kB
PageTables:        28060 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    11747788 kB
Committed_AS:   12092868 kB
VmallocTotal:   34359738367 kB
VmallocUsed:      107616 kB
VmallocChunk:   34359622135 kB
HardwareCorrupted:     0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:       13248 kB
DirectMap2M:    15548416 kB



OraDumpReader 0.1 Release

the software directly reads data from oracle exp & expdp result dump file . even the file is corrupted or damaged by malware/ransomware, the software can still scan the whole file , and find the good part of data.

IMP-00009 abnormal end of export file
IMP-00037 Character set marker unknown
ORA-31619: invalid dump file

OraDumpReader 是oracle导出数据恢复工具。

该软件可以直接读取oracle导出工具(包括exp和expdp)所产生的dump文件中的数据。当这些dmp文件被病毒软件加密破坏后,仍可以通过扫描整个文件,找出剩余的数据。

 

 

下载地址download url: https://zcdn.parnassusdata.com/OraDumpReader200707.zip

 

 

 

 

PRM-DUL 5108 RC16

what’s new

  1. 增加了字段column长度的strict mode
  2. 增加了对Oracle 12c字段名字超过30字节的支持

 

https://zcdn.parnassusdata.com/DUL5108rc16_java.zip

 

 

 

Oracle export import传统导出导入工具与 expdp impdp数据泵必知必会

Oracle export import传统导出导入工具与 expdp impdp数据泵必知必会

 

常见使用export/import expdp/impdp的理解错误:

  1. exp/imp expdp/impdp 以为是跑在sqlplus里的
  2. export/expdp 分不清楚
  3. export和expdp兼容性搞不清楚
  4. 怕用expdp,因为不会建directory
  5. 以为expdp/impdp可以远程运行
  6. 不会用expdp的remap和exclude
  7. exp/imp 有include、exclude需求,无法满足
  8. imp 想转换表空间,但不会
  9. 搞不清楚啥是逻辑备份、物理备份。把export/expdp当成物理备份
  10. 用export/expdp当备份,但产生的dmp文件都放在和数据库一个机器和盘上,经常被一锅端

export/expdp这个 搞残很多纯开发 不懂oracle基础知识的;相反 mysqldump对大部分开发都很友好, 虽然技术上mysqldump很落后;所以技术先进没有用 ,而且容易和具体操作者脱节。

沪ICP备14014813号-2

沪公网安备 31010802001379号