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/