标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,607)
- DB2 (22)
- MySQL (71)
- Oracle (1,471)
- Data Guard (50)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle 23ai (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (534)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- 收集统计信息报ORA-00600 16515问题解决
- rm -rf误删Oracle数据库恢复
- 分布式存储故障导致数据库无法启动故障处理
- read_me_recover_tn勒索恢复
- WINDOWS 下用dg broker搭建ADG(单机to单机)
- 存储故障后oracle报—ORA-01122/ORA-01207故障处理
- Oracle 23ai rm redo*.log恢复
- Oracle 发布计划—包含Oracle 23ai版本
- Oracle 23ai 变化之—-默认数据文件变为bigfile
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
月归档:三月 2012
关于SMON_SCN_TIME若干问题说明
1.SMON_SCN_TIME表基础知识
0)作用
由smon收集scn和time映射关系,用于flashback/查询scn和time对应关系等操作
1)保留条数
官方文档给出说明instance number N * 12 times per hour * 24 hours * 5 days = 1440N rows,因为每次的时间间隔不是非常准确的5分钟,所以在具体的条数在实际生产环境中有一定的出入
2)采集和删除
smon进程没5分钟采集一次插入到SMON_SCN_TIME表中,同时将删除历史数据(超过5天前数据),采用下面语句
delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0),如果有时候index出了问题,导致该语句执行很慢
3)当查询scn对应time,如果scn超过SMON_SCN_TIME表范围,将提示错误;或者查询time对应的scn,如果超过范围也同样报错。
2.当SMON_SCN_TIME表出现问题时,truncate操作语句
--找出CLUSTER Select dbms_metadata.get_ddl('TABLE','SMON_SCN_TIME','SYS') FROM DUAL ; 9i truncate CLUSTER "SYS"."SMON_SCN_TO_TIME"; 10g truncate CLUSTER "SYS"."SMON_SCN_TO_TIME"; 11g truncate CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX";
3.停止/开启smon进程收集scn信息
stop alter system set events '12500 trace name context forever, level 10'; start alter system set events '12500 trace name context off';
4.index异常处理
--找出index Select index_name,index_type,owner from dba_indexes where table_name='SMON_SCN_TIME'; --对应index创建语句 Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') FROM DUAL; --重建index drop index smon_scn_time_scn_idx; drop index smon_scn_time_tim_idx; create unique index smon_scn_time_scn_idx on smon_scn_time(scn); create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp); analyze table smon_scn_time validate structure cascade online; --rebuild index alter index sys.smon_scn_time_scn_idx rebuild online; alter index sys.smon_scn_time_tim_idx rebuild online; analyze table smon_scn_time validate structure cascade online;
补充知识点scn计算方法SCN=(SCN_WRP * 4294967296) + SCN_BAS
授权用户访问数据字典三种方式
在很多时候,希望给用户最小的权限,让其访问系统数据字典,检查数据库的运行状态。这种事情在乙方的工作中非常常见。下面介绍三种方法处理这个问题
0.select any table权限
这里说明select any table不能直接访问数据字典
SQL> conn / as sysdba Connected. SQL> create user xff_any identified by xifenfei; User created. SQL> grant connect,select any table to xff_any; Grant succeeded. SQL> conn xff_any/xifenfei Connected. SQL> select count(*) from dba_users; select count(*) from dba_users * ERROR at line 1: ORA-00942: table or view does not exist SQL> select count(*) from chf.t_xff; COUNT(*) ---------- 2770
select any table默认情况下,只能访问业务的表,但是不能访问数据字典的数据。所以单纯的这个属性不能满足需求。
1.SELECT ANY DICTIONARY权限
SQL> conn / as sysdba Connected. SQL> create user xff_DICTIONARY identified by xifenfei; User created. SQL> grant connect to xff_DICTIONARY; Grant succeeded. SQL> conn xff_DICTIONARY/xifenfei Connected. SQL> select count(*) from dba_users; select count(*) from dba_users * ERROR at line 1: ORA-00942: table or view does not exist SQL> conn / as sysdba Connected. SQL> grant SELECT ANY DICTIONARY to xff_DICTIONARY; Grant succeeded. SQL> conn xff_DICTIONARY/xifenfei Connected. SQL> select count(*) from dba_users; COUNT(*) ---------- 32 SQL> select count(*) from chf.t_xff; select count(*) from chf.t_xff * ERROR at line 1: ORA-00942: table or view does not exist
这里可以看出SELECT ANY DICTIONARY权限只能访问数据字典,不能访问业务的表,访问业务的表需要另外授权
2.SELECT_CATALOG_ROLE角色
SQL> conn / as sysdba Connected. SQL> create user xff_CATALOG identified by xifenfei; User created. SQL> grant connect,SELECT_CATALOG_ROLE to xff_CATALOG; Grant succeeded. SQL> conn xff_CATALOG/xifenfei Connected. SQL> select count(*) from dba_users; COUNT(*) ---------- 33 SQL> select count(*) from chf.t_xff; select count(*) from chf.t_xff * ERROR at line 1: ORA-00942: table or view does not exist
这里可以看出SELECT_CATALOG_ROLE权限只能访问数据字典,不能访问业务的表,访问业务的表需要另外授权
3.O7_DICTIONARY_ACCESSIBILITY参数
SQL> conn / as sysdba Connected. SQL> create user xff_O7 identified by xifenfei; User created. SQL> grant connect to xff_o7; Grant succeeded. SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true; alter system set O7_DICTIONARY_ACCESSIBILITY=true * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 368263168 bytes Fixed Size 1345016 bytes Variable Size 306186760 bytes Database Buffers 54525952 bytes Redo Buffers 6205440 bytes Database mounted. Database opened. SQL> conn xff_o7/xifenfei Connected. SQL> select count(*) from dba_users; COUNT(*) ---------- 34
这里通过O7_DICTIONARY_ACCESSIBILITY和SELECT ANY TABLE权限,实现访问业务数据和数据字典
发表在 Oracle
评论关闭
跟踪ORACLE非当前会话
使用oradebug
session 1 --查询出需要跟踪会话v$process.pid session 2 SQL> oradebug SETORAPID 15 Unix process pid: 14851, image: oracle@xifenfei (TNS V1-V3) SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4 Statement processed. --执行session 1 session 1 SQL> oradebug EVENT 10046 trace name context off Statement processed. SQL> oradebug TRACEFILE_NAME /u01/oracle/admin/xifenfei/udump/xff_ora_14851.trc
使用dbms_system.set_sql_trace_in_session
session 1 --查询出需要跟踪会话的sid,SERIAL# session 2 SQL> exec dbms_system.set_sql_trace_in_session(12,130,true); PL/SQL procedure successfully completed. --session 1执行sql session2关闭跟踪 SQL> exec dbms_system.set_sql_trace_in_session(12,130,false); PL/SQL procedure successfully completed. --通过session 1找出trace文件
发表在 Oracle
评论关闭