oralce 问题几则 ORA-19504 报错
问题1 ，数据库恢复报错 ORA-19504
SYMPTOMS
Restore database to ASM location failing with errors
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/< path>/<db file name>
channel ORA_DISK_1: restoring datafile 00002 to +DATA/< path>/<db file name>
channel ORA_DISK_1: restoring datafile 00003 to +DATA/< path>/<db file name>
channel ORA_DISK_1: restoring datafile 00004 to +DATA/< path>/<db file name>
channel ORA_DISK_1: restoring datafile 00005 to +DATA/<db file name>
channel ORA_DISK_1: restoring datafile 00006 to +DATA/<db file name>
channel ORA_DISK_1: restoring datafile 00007 to +DATA/<db file name>
channel ORA_DISK_1: reading from backup piece /<backup piece path>/<backup piece name>
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /<backup piece path>/<backup piece name>
ORA-19504: failed to create file "+DATA/<db file name>"
ORA-17502: ksfdcre:3 Failed to create file +DATA/<db file name>
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15040: diskgroup is incomplete
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> select name, state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
RECO MOUNTED
DATA MOUNTED
CAUSE
New Envirnoment.
SOLUTION
Issue is related to permission issue
The Grid home is owned by oinstall
ls -lrt /<oracle home>/bin/oracle
-rwsr-s--x 1 oracle oinstall 327811823 Oct 2 16:50 /<oracle home>/bin/oracle
The ASMdisks would be owned by grid:asmadmin so if the database needs to access those disks then the oracle binaries have to be oracle:asmadmin.
Change the group to asmadmin :-
chgrp asmadmin /<oracle home>/bin/oracle
ls -lrt /<oracle home>/bin/oracle
问题2， 程序总是循环发起，消耗了大量redo 资源
日志切换快，是应用发起delete操作删除大量数据，跑批存在问题，delete操作又被回滚
1． 应用跑批发起delete操作
2． 应用跑批存在问题，delete操作被回滚
相关delete操作
4wzufsb9yg7ka
DELETE FROM tabWHERE ClassifyDate='2020/06/29'
以上操作单次删除的数据量为13255454
BEGIN_INTERVAL_TIME END_INTERVAL_TIME SQL_ID EXECUTIONS ROWS_PROCESSED
-------------------------------------- -------------------------------------- -------------------------- ---------------- --------------------
2020-06-30 05:00:24 2020-06-30 05:15:54 4wzufsb9yg7ka 0 0
2020-06-30 05:15:54 2020-06-30 05:30:21 4wzufsb9yg7ka 1 13255454
2020-06-30 05:30:21 2020-06-30 05:45:55 4wzufsb9yg7ka 1 13255454
我们在5点21分观察到delete操作，5点24分没有delete操作的情况下，依然在快速切换日志
获取5点23分和5点24分的归档日志进行分析：
SQL> select OPERATION,count(*) cnt from v$logmnr_contents group by OPERATION order by cnt;
OPERATION CNT
-------------------------------- ----------
UPDATE 81
COMMIT 1183
START 1183
INTERNAL 2944065 <<<<< internal操作为数据库内部操作
INSERT 2945122 <<<<< 存在大量insert操作
SQL> select TABLE_NAME,ROLLBACK,count(*) cnt from v$logmnr_contents where OPERATION = 'INTERNAL' group by TABLE_NAME,ROLLBACK;
ROLLBACK CNT
---------- ----------
1 2944009 <<<<<< internal 操作为回滚产生
0 56
SQL> select TABLE_NAME,ROLLBACK,count(*) cnt from v$logmnr_contents where OPERATION = 'INSERT' group by TABLE_NAME,ROLLBACK;
TABLE_NAME ROLLBACK CNT
-------------------------------- ---------- ----------
OBJ# 87412 0 9
OBJ# 87417 0 12
OBJ# 87434 0 1
OBJ# 87429 0 1
OBJ# 87498 0 24
OBJ# 87460 0 24
OBJ# 87491 0 12
OBJ# 87415 0 9
OBJ# 87720 1 2944010 <<<<<< 大量的insert为回滚产生，与object 87720有关。object 87720经查询为user.CLASSIFY_RECORD，为上述delete操作的表
OBJ# 91051 0 981
OBJ# 87449 0 30
OBJ# 87428 0 9
#############问题3. 为何一张19G 表查询数据很慢，90G 表查询很快
这个是一种错觉，19G 是完全查询完才回表，90G 表是 查询100条左右记录就回表数据，实际上要完整查询90G 的表需要远远多的时间。
测试方法：
--只显示执行计划和统计信息，不显示sql执行结果。
SQL> set autotrace traceonly;
设置Autotrace的命令。
分别在执行sql前设置set autotrace 的不同参数，得到不同的想观察的效果
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
--关闭跟踪执行计划和统计信息功能（默认关闭）。
SQL> set autotrace off;
--执行计划和统计信息都显示
SQL> set autotrace on ;
--只显示执行计划和统计信息，不显示sql执行结果。
SQL> set autotrace traceonly;
--只显示执行计划
SQL> set autotrace on explain;
--只显示统计信息
SQL> set autotrace on statistics;
使用autotrace功能时，oracle启用了两个session。
一个用来执行SQL。另一个用来记录执行计划和输出结果。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25542870/viewspace-2144764/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/25542870/viewspace-2144764/
