Oracle 11g，新特性：闪回数据归档（Flashback data archive）
UNDO表空间记录的回滚信息虽然可以提供闪回查询，但是时间久了，这些信息可能会被覆盖掉。
所以，时常会遇到找不到UNDO BLOCK而报出【ORA-1555】的错误。
在11g开始，Oracle引入了Flashback data archive，用于存放数据的所有改变，并且消耗的是磁盘空间，[……]
Category Archives: Oracle Database
SQL，查询正在执行的事务
SQL：
|
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 |
set linesize 600 col event for a18 col sql_text for a29 col sql_fulltext for a29 col username for a9 col machine for a9 col program for a18 col direct_writes for a18 col terminal for a9 SELECT s.sid, s.serial#, s.event, a.sql_text, a.sql_fulltext, s.username, s.status, s.machine, s.terminal, s.program, a.executions, s.sql_id, p.spid, a.direct_writes FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s LEFT JOIN v$sqlarea a ON s.sql_id = a.sql_id INNER JOIN v$process p ON s.paddr = p.addr where sql_text is not null and rownum<2; |
Like This：
|
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 |
SQL> set linesize 600 SQL> col event for a18 SQL> col sql_text for a29 SQL> col sql_fulltext for a29 col username for a9 SQL> SQL> col machine for a9 SQL> col program for a18 SQL> col direct_writes for a18 col terminal for a9 SQL> SQL> SELECT s.sid, s.serial#, 2 3 s.event, 4 a.sql_text, 5 a.sql_fulltext, 6 s.username, 7 s.status, 8 s.machine, 9 s.terminal, 10 s.program, 11 a.executions, 12 s.sql_id, 13 p.spid, 14 a.direct_writes 15 FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s 16 LEFT JOIN v$sqlarea a 17 ON s.sql_id = a.sql_id 18 INNER JOIN v$process p 19 ON s.paddr = p.addr 20 where sql_text is not null and rownum<2; SID SERIAL# EVENT SQL_TEXT SQL_FULLTEXT USERNAME STATUS MACHINE TERMINAL PROGRAM EXECUTIONS SQL_ID SPID DIRECT_WRITES ---------- ---------- ------------------ ----------------------------- ----------------------------- --------- -------- --------- --------- ------------------ ---------- ------------- ------------------------ ------------- 7 29 db file sequential DELETE FROM "SXMSPMAST" WHERE DELETE FROM "SXMSPMAST" WHERE SAPSR3 ACTIVE uxgdc115 dw.sapNXP_DVEBMGS0 16 5zs82knjqrj2r 8716586 ########## read "MANDT"=:A0 AND "MSGGUID"=:A "MANDT"=:A0 AND "MSGGUID"=:A 0@uxgdc115 (TNS V1 1 AND "PID"=:A2 1 AND "PID"=:A2 -V3) SQL> |
——————
Done。
Oracle：ORA-00214: control file xxxx inconsistent with file xxx
该错误发生在MOUNT数据库的时候，具体报错如下：
|
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 |
[oracle@DC15VM-033 vcenter]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 22 12:52:02 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> SQL> startup nomount; ORACLE instance started. Total System Global Area 4108759040 bytes Fixed Size 2259680 bytes Variable Size 1157629216 bytes Database Buffers 2936012800 bytes Redo Buffers 12857344 bytes SQL> alter database mount standby database; alter database mount standby database * ERROR at line 1: ORA-00214: control file '/u01/app/oracle/fast_recovery_area/vcenter/control02.ctl' version 25922642 inconsistent with file '/u01/app/oracle/oradata/vcenter/control01.ctl' version 21054587 SQL> |
错误原因：
两个数据库的版本不一致。
解法：
|
1 2 3 4 5 6 7 8 9 10 |
[oracle@DC15VM-033 vcenter]$ pwd /u01/app/oracle/oradata/vcenter [oracle@DC15VM-033 vcenter]$ [oracle@DC15VM-033 vcenter]$ ls -ltr | grep --color ".ctl" -rw-r----- 1 oracle oinstall 28590080 Aug 22 11:30 control01.ctl [oracle@DC15VM-033 vcenter]$ [oracle@DC15VM-033 vcenter]$ cp /u01/app/oracle/fast_recovery_area/vcenter/control02.ctl /u01/app/oracle/fast_recovery_area/vcenter/control02.ctl_orig_20170822 [oracle@DC15VM-033 vcenter]$ [oracle@DC15VM-033 vcenter]$ cp /u01/app/oracle/oradata/vcenter/control01.ctl /u01/app/oracle/fast_recovery_area/vcenter/control02.ctl [oracle@DC15VM-033 vcenter]$ |
备份某个控制文件的原始版本，用其中一个控制文件[……]
SQL：当前会话
SQL：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SET LINESIZE 400 COLUMN spid FORMAT A10 COLUMN username FORMAT A10 COLUMN program FORMAT A45 col machine for a14 SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program, s.machine FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 'BACKGROUND'; |
Like this：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL> SET LINESIZE 400 SQL> COLUMN spid FORMAT A10 SQL> COLUMN username FORMAT A10 SQL> COLUMN program FORMAT A45 SQL> col machine for a14 SQL> SELECT s.inst_id, 2 s.sid, 3 s.serial#, 4 p.spid, 5 s.username, 6 s.program, 7 s.machine 8 FROM gv$session s 9 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 'BACKGROUND'; 10 INST_ID SID SERIAL# SPID USERNAME PROGRAM MACHINE ---------- ---------- ---------- ---------- ---------- --------------------------------------------- -------------- 1 1156 15 9629 SYS sqlplus@DC15VM-032 (TNS V1-V3) DC******2 1 582 49 10247 VPXADMIN sqlplus@DC15VM-032 (TNS V1-V3) DC******2 SQL> |
————————————————
Done。
Oracle SQL：表空间使用率
SQL：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
set linesize 400 col tablespace_name for a23 col free_rate for a18 SELECT a.tablespace_name, ROUND (a.total_size) "total_size(MB)", ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)", ROUND (b.free_size, 3) "free_size(MB)", ROUND (b.free_size / total_size * 100, 2) || '%' free_rate FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size FROM dba_data_files GROUP BY tablespace_name) a, ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name(+); |
Like this：
|
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 |
SQL> run 1 SELECT a.tablespace_name, 2 ROUND (a.total_size) "total_size(MB)", 3 ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)", 4 ROUND (b.free_size, 3) "free_size(MB)", 5 ROUND (b.free_size / total_size * 100, 2) || '%' free_rate 6 FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size 7 FROM dba_data_files 8 GROUP BY tablespace_name) a, 9 ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size 10 FROM dba_free_space 11 GROUP BY tablespace_name) b 12* WHERE a.tablespace_name = b.tablespace_name(+) TABLESPACE_NAME total_size(MB) used_size(MB) free_size(MB) FREE_RATE ------------------------ -------------- ------------- ------------- ------------------ SYSAUX 24210 13316.875 10893.125 44.99% UNDOTBS1 20480 252.312 20227.688 98.77% CLOUD_INDX 500 1 499 99.8% USERS 8 1.812 6.188 82.5% FLASHBACK_ARCH 2048 1 2047 99.95% SYSTEM 3178 1321.125 1856.875 58.43% VPX 240640 8210.5 232429.5 96.59% CLOUD_DATA 7089 5241.437 1847.563 26.06% 8 rows selected. SQL> |
——————————————
Done。