标签云
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,597)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (17)
- PostgreSQL恢复 (5)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- 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$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
月归档:十一月 2014
记录一次rm -rf 删除数据文件异常恢复
因为人员离职闹得不愉快,系统工程师离职后,由于公司未及时关闭其vpn,数据库服务器(Linux 6.5 Oracle 11.2.0.1)帐号未及时被修改,最后直接上去rm ORACLE_BASE给干掉,悲剧的是ORADATA目录也在里面,更加悲剧的是所有数据文件都在里面.也就是说数据库彻底被删除,而且没有任何备份.朋友咨询了我,让我给予支持.最后比较幸运,文件没有被覆盖,inode都还在,通过extundelete顺利恢复所有数据文件,控制文件,redo文件(extundelete恢复Linux被删除文件),数据库顺利打开,实现0丢失,算是一次完美的恢复
[root@DB1 tmp]# tar xvf extundelete-0.2.4.tar extundelete-0.2.4/ extundelete-0.2.4/acinclude.m4 extundelete-0.2.4/missing extundelete-0.2.4/autogen.sh extundelete-0.2.4/aclocal.m4 extundelete-0.2.4/configure extundelete-0.2.4/LICENSE extundelete-0.2.4/README extundelete-0.2.4/install-sh extundelete-0.2.4/config.h.in extundelete-0.2.4/src/ extundelete-0.2.4/src/extundelete.cc extundelete-0.2.4/src/block.h extundelete-0.2.4/src/kernel-jbd.h extundelete-0.2.4/src/insertionops.cc extundelete-0.2.4/src/block.c extundelete-0.2.4/src/cli.cc extundelete-0.2.4/src/extundelete-priv.h extundelete-0.2.4/src/extundelete.h extundelete-0.2.4/src/jfs_compat.h extundelete-0.2.4/src/Makefile.in extundelete-0.2.4/src/Makefile.am extundelete-0.2.4/configure.ac extundelete-0.2.4/depcomp extundelete-0.2.4/Makefile.in extundelete-0.2.4/Makefile.am [root@DB1 tmp]# cd extundelete-0.2.4 [root@DB1 extundelete-0.2.4]# ./configure Configuring extundelete 0.2.4 Writing generated files to disk [root@DB1 extundelete-0.2.4]# make && make install make -s all-recursive Making all in src Making install in src /usr/bin/install -c extundelete '/usr/local/bin' [root@DB1 extundelete-0.2.4]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda3 244G 11G 221G 5% / tmpfs 16G 72K 16G 1% /dev/shm /dev/sda1 190M 62M 119M 35% /boot /dev/sdb1 2.0T 71M 1.9T 1% /home [root@DB1 extundelete-0.2.4]# umount /dev/sdb1 umount: /home: device is busy. (In some cases useful info about processes that use the device is found by lsof(8) or fuser(1)) [root@DB1 extundelete-0.2.4]# fuser -m -u /home /home: 3914c(oracle) 8372c(oracle) [root@DB1 extundelete-0.2.4]# kill -9 3914 [root@DB1 extundelete-0.2.4]# fuser -m -u /home /home: 8372c(oracle) [root@DB1 extundelete-0.2.4]# kill -9 8372 [root@DB1 extundelete-0.2.4]# fuser -m -u /home [root@DB1 extundelete-0.2.4]# umount /dev/sdb1 [root@DB1 extundelete-0.2.4]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda3 244G 11G 221G 5% / tmpfs 16G 72K 16G 1% /dev/shm /dev/sda1 190M 62M 119M 35% /boot [root@DB1 extundelete-0.2.4]# extundelete /dev/sdb1 --restore-all NOTICE: Extended attributes are not restored. Loading filesystem metadata ... 16384 groups loaded. Loading journal descriptors ... 26542 descriptors loaded. Searching for recoverable inodes in directory / ... 18896 recoverable inodes found. Looking through the directory structure for deleted files ... 2 recoverable inodes still lost. Unable to restore inode 43778050 (file.43778050): Space has been reallocated. [root@DB1 extundelete-0.2.4]# ls acinclude.m4 autogen.sh config.h.in config.status configure.ac install-sh Makefile Makefile.in aclocal.m4 config.h config.log configure depcomp LICENSE Makefile.am missing [root@DB1 extundelete-0.2.4]# cd RECOVERED_FILES/ [root@DB1 RECOVERED_FILES]# ls app file.43778051 oracle oraInventory [root@DB1 RECOVERED_FILES]# cd app [root@DB1 app]# ls admin cfgtoollogs diag oracle oradata orcl ORCL [root@DB1 app]# cd oradata [root@DB1 oradata]# ls orcl [root@DB1 oradata]# cd orcl [root@DB1 orcl]# ls control01.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf [root@DB1 orcl]# ls -ltr total 2908776 -rw-r--r--. 1 root root 734011392 Nov 18 02:06 system01.dbf -rw-r--r--. 1 root root 1069555712 Nov 18 02:06 sysaux01.dbf -rw-r--r--. 1 root root 120594432 Nov 18 02:06 undotbs01.dbf -rw-r--r--. 1 root root 887365632 Nov 18 02:06 users01.dbf -rw-r--r--. 1 root root 9748480 Nov 18 02:06 control01.ctl -rw-r--r--. 1 root root 52429312 Nov 18 02:06 redo01.log -rw-r--r--. 1 root root 52429312 Nov 18 02:06 redo02.log -rw-r--r--. 1 root root 52429312 Nov 18 02:06 redo03.log [root@DB1 orcl]#
再次提醒各位:数据库备份重于一切,防天灾的同时还要防人灾,也希望圈子里面以后不要听到类似故障.
In-Memory整体汇总
本问是对于Oracle 12C中的In-Memory Column Store一个整体的汇总,具体细节知识在以后章节中展开
IM可以针对如下级别进行操作
Column
Table
Materialized view
Tablespace
Partition
可以指定In-Memory操作语句
CREATE TABLE
ALTER TABLE
CREATE TABLESPACE
ALTER TABLESPACE
CREATE MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
压缩级别
优先级
对象级别操作IM
CREATE TABLE t_xifenfei ( id NUMBER(5) PRIMARY KEY, test_col VARCHAR2(15)) INMEMORY; ALTER TABLE t_xifenfei INMEMORY; ALTER TABLE t_xifenfei INMEMORY MEMCOMPRESS FOR CAPACITY LOW; ALTER TABLE t_xifenfei INMEMORY PRIORITY HIGH; ALTER TABLE t_xifenfei INMEMORY MEMCOMPRESS FOR CAPACITY HIGH PRIORITY LOW; ALTER TABLE t_xifenfei INMEMORY MEMCOMPRESS FOR QUERY ( product_id, product_name, category_id, supplier_id, min_price) INMEMORY MEMCOMPRESS FOR CAPACITY HIGH ( product_description, warranty_period, product_status, list_price) NO INMEMORY ( weight_class, catalog_url); ALTER TABLE t_xifenfei NO INMEMORY;
补充说明:列级别设置的优先级无效,优先级是表(物化视图)或者分区表级别
表空间级别操作IM
CREATE TABLESPACE xifenfie_im DATAFILE '/u02/xifenfei.dbf' SIZE 40M ONLINE DEFAULT INMEMORY; ALTER TABLESPACE xifenfie_im DEFAULT INMEMORY MEMCOMPRESS FOR CAPACITY HIGH PRIORITY LOW;
物化视图级别
CREATE MATERIALIZED VIEW oe.prod_info_mv INMEMORY AS SELECT * FROM t_xifenfei; ALTER MATERIALIZED VIEW oe.prod_info_mv INMEMORY PRIORITY HIGH;
适合使用IN-Memory操作
A query that scans a large number of rows and applies filters that use operators such as the following: =, <, >, and IN
A query that selects a small number of columns from a table or materialized view with a large number of columns,
such as a query that selects five columns from a table with 100 columns
A query that joins a small table to a large table
A query that aggregates data
不适合使用IN-Memory操作
Queries with complex predicates
Queries that select a large number of columns
Queries that return a large number of rows
Queries with multiple large table joins
IM控制参数
INMEMORY_SIZE 指定IM分配内存大小,默认值为0,如果启动该值最小为100M;如果在CDB环境中使用,CDB级别设置为整个库级别限制,PDB默认继承CDB设置,但是在实际使用中PDB中总数不能超过CDB限制
INMEMORY_FORCE 指定是否允许数据库中对象使用IM,默认是DEFAULT,即可以实现在对象级别定义INMEMORY or NO INMEMORY,如果设置为OFF 即表示表或者物化视图无法使用IM
INMEMORY_CLAUSE_DEFAULT 默认为空,和NO INMEMORY意义相同,表示创建新对象默认不启用IM,如果配置为INMEMORY,表示新创建对象默认启用IM
INMEMORY_QUERY 默认为TRUE,表示查询是否使用IM特性,设置为FALSE表示查询不使用IM特性
INMEMORY_MAX_POPULATE_SERVERS 默认和系统core一致,用途是把你的表中数据写入到IM中
INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT 控制IM中对象数据的重新载入的进程数,该值为INMEMORY_MAX_POPULATE_SERVERS参数的百分比
OPTIMIZER_INMEMORY_AWARE 该参数是控制优化器成本计算时是否考虑IM,默认为TRUE
impdp 操作IM
TRANSFORM=INMEMORY:y 继承IM导出对象属性
TRANSFORM=INMEMORY:n 不继承IM导出对象属性
TRANSFORM=INMEMORY_CLAUSE:string 修改IM导出对象关于IM的属性
参考文档:https://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN14257
如何估算表In-Memory需要内存大小
对于12.1.0.2的In-Memory特性很多朋友都已经知晓,现在可能有这个困惑我一张表启用In-Memory大概需要多少内存呢?该如何估算这个值呢?这里我告诉你通过dbms_compression可以完成你想做的事情
启用In-Memory功能
[oracle@www.xifenfei.com u02]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 7 17:50:47 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 0 inmemory_query string ENABLE inmemory_size big integer 0 inmemory_trickle_repopulate_servers_ integer 1 optimizer_inmemory_aware boolean TRUE SQL> alter system set inmemory_size=400M; alter system set inmemory_size=400M * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-02095: specified initialization parameter cannot be modified SQL> alter system set inmemory_size=400M scope=spfile; System altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO SQL> alter session set container=pdb1; Session altered. SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 0 inmemory_query string ENABLE inmemory_size big integer 0 inmemory_trickle_repopulate_servers_ integer 1 optimizer_inmemory_aware boolean TRUE SQL> alter system set inmemory_size=200M; alter system set inmemory_size=200M * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-02095: specified initialization parameter cannot be modified SQL> alter system set inmemory_size=200M scope=spfile; alter system set inmemory_size=200M scope=spfile * ERROR at line 1: ORA-02096: specified initialization parameter is not modifiable with this option SQL> !oerr ora 2096 02096, 00000, "specified initialization parameter is not modifiable with this option" // *Cause: Though the initialization parameter is modifiable, it cannot be // modified using the specified command. // *Action: Check the DBA guide for information about under what scope // the parameter may be modified SQL> select 200*1024*1024 from dual; 200*1024*1024 ------------- 209715200 SQL> alter system set inmemory_size=209715200; alter system set inmemory_size=209715200 * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-02095: specified initialization parameter cannot be modified SQL> shutdown immediate; Pluggable Database closed. SQL> conn / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 838860800 bytes Fixed Size 2929936 bytes Variable Size 360712944 bytes Database Buffers 50331648 bytes Redo Buffers 5455872 bytes In-Memory Area 419430400 bytes Database mounted. Database opened. SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 1 inmemory_query string ENABLE inmemory_size big integer 400M inmemory_trickle_repopulate_servers_ integer 1 optimizer_inmemory_aware boolean TRUE SQL> alter session set container=pdb1; Session altered. SQL> alter database open; Database altered. SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 1 inmemory_query string ENABLE inmemory_size big integer 400M inmemory_trickle_repopulate_servers_ integer 1 optimizer_inmemory_aware boolean TRUE SQL> alter system set inmemory_size=100M; System altered. SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 1 inmemory_query string ENABLE inmemory_size big integer 100M inmemory_trickle_repopulate_servers_ integer 1 optimizer_inmemory_aware boolean TRUE
这里可以发现inmemory_query默认为true,但是inmemory_size默认为0,也就是说In-Memory Option默认是关闭的,如果启用直接设置inmemory_size值即可(该值最小值为100M),但是需要注意在cdb中需要重启后生效,pdb需要待cdb生效后才能够设置
估算表设置In-Memory默认需要内存空间
SQL> create user chf identified by xifenfei; User created. SQL> grant dba to chf; Grant succeeded. SQL> create table chf.t_xifenfei as select * from dba_objects; Table created. SQL> select sum(bytes), sum(blocks) from DBA_segments where segment_name = 'T_XIFENFEI'; SUM(BYTES) SUM(BLOCKS) ---------- ----------- 13631488 1664 SQL> set serveroutput on DECLARE l_blkcnt_cmp binary_integer; l_blkcnt_uncmp binary_integer; l_row_cmp binary_integer; l_row_uncmp binary_integer; l_cmp_ratio number; SQL> 2 3 4 5 6 7 l_comptype_str varchar2(100); 8 BEGIN 9 dbms_compression.get_compression_ratio( 10 scratchtbsname => upper('&ScratchTBS'), 11 ownname => upper('&ownername'), 12 objname => upper('&TableName'), 13 subobjname => NULL, 14 comptype => DBMS_COMPRESSION.COMP_INMEMORY_QUERY_LOW, 15 blkcnt_cmp => l_blkcnt_cmp, 16 blkcnt_uncmp => l_blkcnt_uncmp, 17 row_cmp => l_row_cmp, 18 row_uncmp => l_row_uncmp, 19 cmp_ratio => l_cmp_ratio, 20 comptype_str => l_comptype_str 21 ); 22 dbms_output.put_line('.'); 23 dbms_output.put_line('OUTPUT: '); 24 dbms_output.put_line('LINEORDER '||l_comptype_str||' ratio: '||to_char(l_cmp_ratio,'99.999')); 25 end; 26 / Enter value for scratchtbs: USERS old 10: scratchtbsname => upper('&ScratchTBS'), new 10: scratchtbsname => upper('USERS'), Enter value for ownername: CHF old 11: ownname => upper('&ownername'), new 11: ownname => upper('CHF'), Enter value for tablename: T_XIFENFEI old 12: objname => upper('&TableName'), new 12: objname => upper('T_XIFENFEI'), . OUTPUT: LINEORDER "In-memory Memcompress Query Low" ratio: 2.800 PL/SQL procedure successfully completed. SQL> SELECT 13631488/2.800 FROM DUAL; 13631488/2.800 -------------- 4868388.57 SQL> alter table CHF.T_XIFENFEI inmemory; Table altered. SQL> select COUNT(*) FROM CHF.T_XIFENFEI; COUNT(*) ---------- 90923 SQL> select inmemory_size from v$im_segments where segment_name = 'T_XIFENFEI'; INMEMORY_SIZE ------------- 4325376 SQL> select (4868388.57-4325376)/4325376 from dual; (4868388.57-4325376)/4325376 ---------------------------- .125541125
这里使用大家在ehcc中熟悉的dbms_compression.get_compression_ratio来估算In-memory需要的大概空间,例如本测试中,创建T_XIFENFEI表占用磁盘空间为13631488byte,使用dbms_compression估算在In-memory默认的压缩比例(Query Low)情况下,大概压缩比例为2.8,也就是通过合理估算,表启用In-memory cache之后,大概需要空间为13631488/2.800=4868388.57byte,最终通过实际测试需要空间为4325376byte,整体误差为(4868388.57-4325376)/4325376=12.5%左右
因此我们在使用In-memory cache一个表之时,如果不确定其需要内存大小,可以通过dbms_compression包来估算.另外In-memory还可以配置不同的压缩级别实现不同的压缩比例,其他压缩比例请见下图
详细link请见:https://docs.oracle.com/database/121/ARPLS/d_compress.htm#ARPLS65599