2014年Oracle技术控 Orcl-Con活动报名

Create your free online surveys with SurveyMonkey , the world’s leading questionnaire tool.

Oracle 12.1.0.2新特性 Approximate Count Distinct

[oracle@PD009 ~]$ grep -i approx_count oracle.str
settings for approx_count_distinct optimizations
qkaGBPushdown:  estimated memory without GPD = groupSize (%.2f) * aclsum (%u) = %.2f; estimated memory with GPD = optHllEntry (%u) * # of approx_count_distinct (%d) * parallelDegree (%.0f) = %.2f
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT
 
 
_approx_cnt_distinct_gby_pushdown = choose
_approx_cnt_distinct_optimization = 0




 alter system flush shared_pool;
  alter session set events '10053 trace name context forever ,level 1';
 select count( distinct prod_id) from sales_history where  amount_sold>1;
 

 select approx_count_distinct(prod_id) from sales_history where  amount_sold>1;

  oradebug setmypid;
  oradebug tracefile_name;
  /s01/diag/rdbms/pdprod/PDPROD/trace/PDPROD_ora_4086.trc
  
  
  
  sql= select count( distinct prod_id) from sales_history where  amount_sold>1
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id  | Operation             | Name         | Rows  | Bytes | Cost  | Time      |
---------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT      |              |       |       |  4912 |           |
| 1   |  SORT AGGREGATE       |              |     1 |    13 |       |           |
| 2   |   VIEW                | VW_DAG_0     |    72 |   936 |  4912 |  00:00:59 |
| 3   |    HASH GROUP BY      |              |    72 |   648 |  4912 |  00:00:59 |
| 4   |     TABLE ACCESS FULL | SALES_HISTORY| 3589K |   32M |  4820 |  00:00:58 |
---------------------------------------------+-----------------------------------+













sql= select approx_count_distinct(prod_id) from sales_history where  amount_sold>1
----- Explain Plan Dump -----
----- Plan Table -----
 
============
Plan Table
============
----------------------------------------------+-----------------------------------+
| Id  | Operation              | Name         | Rows  | Bytes | Cost  | Time      |
----------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |              |       |       |  4820 |           |
| 1   |  SORT AGGREGATE APPROX |              |     1 |     9 |       |           |
| 2   |   TABLE ACCESS FULL    | SALES_HISTORY| 3589K |   32M |  4820 |  00:00:58 |
----------------------------------------------+-----------------------------------+
Predicate Information: 


about db_lost_write_protect


SQL> create table lost_write(t1 int) tablespace users; 

Table created.

SQL> 
SQL> insert into lost_write values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.


select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from lost_write;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                                 222                                    6

								 
								 

	alter system set db_lost_write_protect=typical;
	
	
	
	
SQL> select name from v$datafile where file#=6;

NAME
--------------------------------------------------------------------------------
/s01/oradata/PDPROD/datafile/o1_mf_users_b2wgb20l_.dbf


update lost_write set t1=9999;

alter system flush buffer_cache;



dd if=/s01/oradata/PDPROD/datafile/o1_mf_users_b2wgb20l_.dbf skip=222 bs=8192 count=1  of=222_block

dd if=222_block of=/s01/oradata/PDPROD/datafile/o1_mf_users_b2wgb20l_.dbf  seek=222 bs=8192 count=1 conv=notrunc

11g以后的space preallocation特性和SMCO/W00N

11g以后oracle引入了智能空间预分配space preallocation的新特性,该新特性涉及3个领域:

  • 表空间的预分配和扩展
  • 数据段segment的预分配和扩展
  • LOB chunk的预分配和扩展

 

以下是一个tablespace 预分配和扩展的例子,可以看到某个表空间对应的FILE#=3的数据文件,由于在一段时间内的空间使用情况预估,所以在几个小时内扩展了不少的空间:

 

Sat Oct 04 06:07:46 2014
Resize operation completed for file# 3, old size 706560K, new size 716800K
Sat Oct 04 08:00:03 2014
www.askmaclean.com
Thread 1 advanced to log sequence 60 (LGWR switch)
  Current log# 2 seq# 60 mem# 0: /s01/oradata/PDPROD/onlinelog/o1_mf_2_b2wgc3rf_.log
  Current log# 2 seq# 60 mem# 1: /s01/fast_recovery_area/PDPROD/onlinelog/o1_mf_2_b2wgc4mf_.log
Sat Oct 04 08:00:05 2014
TT00: Standby redo logfile selected for thread 1 sequence 60 for destination LOG_ARCHIVE_DEST_2
Sat Oct 04 08:00:06 2014
Archived Log entry 79 added for thread 1 sequence 59 ID 0xe5f08f5 dest 1:
Sat Oct 04 08:08:00 2014
www.askmaclean.com
Resize operation completed for file# 3, old size 716800K, new size 727040K
Sat Oct 04 10:08:37 2014
Thread 1 advanced to log sequence 61 (LGWR switch)
  Current log# 3 seq# 61 mem# 0: /s01/oradata/PDPROD/onlinelog/o1_mf_3_b2wgc6ol_.log
  Current log# 3 seq# 61 mem# 1: /s01/fast_recovery_area/PDPROD/onlinelog/o1_mf_3_b2wgc70g_.log
Sat Oct 04 10:08:37 2014
TT00: Standby redo logfile selected for thread 1 sequence 61 for destination LOG_ARCHIVE_DEST_2
Sat Oct 04 10:08:40 2014
Archived Log entry 81 added for thread 1 sequence 60 ID 0xe5f08f5 dest 1:
Sat Oct 04 13:08:34 2014
Resize operation completed for file# 3, old size 727040K, new size 737280K
Sat Oct 04 14:04:39 2014


Resize operation completed for file# 3, old size 747520K, new size 757760K

 

以上这种扩展受到Oracle内部预定义的参数TBS_EXTENSION_MAX_STEP_SIZE(64MB)的限制,即最大一次扩展是64MB,可以从上述日志看到大多数扩展在这里是一次10MB。

 

对于表空间预分配, space preallocation特性会基于过去6个小时内的表空间使用情况,每隔10分钟分析一次,基于以下的数据分析:

  • Database id – Database identifier
  • Tablespace id – Tablespace identifier number
  • Creation SCN (wrap, base) [NEW] – SCN when the tablespace is created. This is for tablespace sanity check in case that the tablespace is dropped or recreated after the statistics is collected.
  • Allocated space – Space allocated to the tablespace
  • Used space – Space currently used in the tablespace. For permanent tablespace, it is represented by the used extents. For temporary tablespace, this should be the space used by active sorts, hash joins and other transient objects.
  • Max size – Maximum size of the tablespace
  • Flag – Tablespace flag (e.g. perm/temp/undo)
  • Stat-collection Timestamp [NEW] – The time when the statistics is collected

 

对于数据段segment预分配,数据段的统计信息会每半个小时flush到AWR中(WRH$_SEG_STAT),Segment growth trend数据段的增长趋势也会每半个小时完成一次, 基于以下的数据分析:

  • Tablespace id
  • Tablespace creation SCN (wrap, base) [NEW]
  • Segment obj#
  • Segment dataobj#
  • Number of allocated blocks
  • Number of used blocks
  • Stat-collect Timestamp [NEW] – The time when the statistics is collected
  • Last-analysis timestamp [NEW] – The time when the segment is analyzed the last time
  • Number of forecasted used blocks [NEW]

 

LOB chunk的使用情况 会每10分钟记录在内存中,也会定期flush到AWR中,基于以下的数据分析:

 

  • Instance id
  • Tablespace id
  • Tablespace creation SCN (wrap, base)
  • Segment obj#
  • Segment dataobj#
  • Number of estimated optimal allocation for each chunk size (Ne) – One occurrence of an allocation for a chunk size refers to a planned
  • allocation of the optimal chunk size that is calculated by the space layer when receiving a chunk allocation request from the data layer.
  • Number of allocations for each chunk size (Na)
  • Number of deallocation for each chunk size (Nd)
  • Number of under-allocation for each chunk size (Nu)
  • Number of split for each chunk size (Ns)
  • Number of projected allocations for each chunk size (Np)
  • Timestamp – The time when the statistics is collected

 

这些预分配和扩展任务主要由SMCO(Space Management Coordinator Process)和其小工进程W00n(slave )一起完成。 SMCO和W00n是基于任务task驱动的后台任务Framework。这个后台任务体系是基于实例的,而非基于数据库的。每一个实例instance有其自己的后台任务服务体系,运行和处理在本实例发起的任务。RAC的不同实例之间不会交互来负责此种任务的负载。

 

Task coordinator ( SMCO ) ,SMCO充当调度进程以便管理任务队列和slave进程池。其主要任务是在几个task queue之间移动任务,清理过期任务,基于任务需要来动态分配新的slave(W00n)进程,并监控slave进程。 一个实例只有一个SMCO进程,且SMCO进程不是fatal进程,kill了一般也没事。

 

部分12c新特性 Policy Driven Data Movement and ILM(Information Lifecycle Management) Project 数据生命周期管理也依赖于SMCO后台进程。

 

 

 

SMON,SMON是老牌后台进程 已经负责了一系列任务。其现在也负载动态启动SMCO进程。SMON现在会定期检查SMCO是否启动着,SMON也会当有任务提交时启动SMCO。

W00n等一组Slave Process,Slave进程总是实际干活的人。Salve process由SMCO这个调度器动态分配。一旦启动后,slave 进程将自主工作,其自动从ready-task队列中找寻任务并执行。如果空闲了过长时间,那么W00n也会自行终止。

 

其大致的工作流程如下:

 

  • The foreground system/user session submits a task through ktsjCreateTask.
  • The task is created. If the task is planned to start right away, it is inserted into one of the ready-task queue directly, otherwise, it isinserted into the not-ready-task queue.
  • If SMCO does not exist, post SMON to start SMCO .
  • If SMCO is available, post SMCO that a new task is available.
  • SMCO knows that there is a task that needs to be run in the near future. It starts a slave process.
  • SMCO moves the task from the not-ready queue to the ready queue and posts any waiting slave.
  • The slave process picks up the task from the ready queue and invokes the task’s callback function.
  • The task is done. The slave process executes the completion call back of the task.
  • The slave process post SMCO that a task is finished. askmaclean.com
  • SMCO updates task execution statistics and may choose to free the task if there is no enough memory for task cache.
  • The slave process periodically checks if there is any task in the ready-task queue.

 

与该11g以后的space preallocation特性相关的参数如下:

_enable_space_background_task/_enable_spacebg – This parameter specifies whether the background task support is enabled.

_max_ smco _slaves – This parameter specifies the maximum number of active slave process that can be spawned at a time. Ideally, the number
of slave processes should be decided by the system workload and the availability of system resources.
_max_ smco _tasks – This parameter specifies the maximum number of tasks that can be stored in memory. Ideally, the number of tasks should be
decided by the availability of SGA memory and the efficiency of task maintenance. Too many cached tasks will simply increase the burden of
task maintenance.

 

_enable_space_background_task
Parameter Name: _enable_space_background_task
Parameter Type: boolean
Allowable Values: TRUE to enable the feature, and FALSE to disable the feature
Default Value: TRUE. The space management background task support will be enabled by default.
Description: This parameter allows the user to choose whether to enable the space management background task support feature.
Dynamic: The parameter is dynamic and the scope is ALTER SYSTEM.
Example: alter system set “_enable_space_background_task ” = TRUE

 

 

_enable_space_preallocation
Parameter Name: _enable_space_preallocation
Parameter Type: integer
Allowable Values:
0x00: disable all levels of space preallocation
0x01: enable tablespace extension ahead of time
0x02: enable segment growth ahead of time
0x04: enable chunk allocation ahead of time
Combination of any of the above 3 levels of space preallocation Default Value: 0x07. The space preallocation at all levels will be enabled by default. However, whether the space preallocation will be done and how much space will be allocated relies on the system monitor analysis result.

Description: This parameter allows the user to enable different levels of space preallocation.
Dynamic: The parameter is dynamic and the scope is ALTER SYSTEM.
Example: alter system set “_enable_space_prealloaction ” = 1
_max_spacebg_slaves
the parameter that allows user to set maximum number of space BG slaves

_minmax_spacebg_slaves
the parameter that allows user to set min-max number of space BG slaves

_min_spacebg_slaves

the parameter that allows user to set minimum number of space BG slaves

_max_spacebg_tasks

the parameter that specifies the maximum number of space BG tasks
_max_spacebg_msgs_percentage

the parameter that specifies percentage of _messages (ksaxxm) before KTSJ
throttling occurs. Default level is 50%. When throttle level is reached,
interrupt messages are not sent (thus avoiding _messages queue).

_enable_space_preallocation 3 enable space pre-allocation
_enable_spacebg TRUE enable space management background task
_max_spacebg_slaves 1024 maximum space management background slaves
_minmax_spacebg_slaves 8 min-max space management background slaves
_min_spacebg_slaves 2 minimum space management background slaves
_max_spacebg_tasks 8192 maximum space management background tasks
_max_spacebg_msgs_percentage 50 maximum space management interrupt message throttling

对于由于SMCO 或W00n在完成space preallocation过程中遇到的问题时,可以考虑禁用该特性,具体方法为:

 

 

SQL> alter system set "_enable_spacebg"=false;

System altered.

SQL> alter system set "_enable_space_preallocation"=0;

System altered.




 

SMCO/Wnnn

 

Short Description: The space management coordinator (SMCO) process coordinates the execution of
various space management related tasks, such as proactive space allocation and space reclamation. It
dynamically spawns slave processes (Wnnn) to implement the task.
Detailed Description: The space management coordinator (SMCO) and slave (Wnnn) processes work
cooperatively on various background space management tasks in a database instance. The coordinator
is responsible for maintaining the tasks and dispatching tasks. It dynamically spawns new slaves based
on task needs. The slave process performs the actual space management task, including space preallocation
and space reclamation. Once started, the slave process acts as an autonomous agent. After
it finishes execution of the task, it automatically pick up another one from the task queue. It terminates
itself after being idle for a long time. SMCO and Wnnn are optional non-fatal background processes.
There is at most one SMCO process per instance. There can be several Wnnn processes at a time. The
failure of these processes does not cause instance to fail.
Properties: Optional, non-fatal, database instance only

 

12c RMAN新特性restore/recover from service远程恢复

12c中提供了基于网络的RMAN Restore和recover功能:

 

About Restoring Files Over the Network

RMAN restores database files, over the network, from a physical standby database by using the FROM SERVICE clause of the RESTORE command. The FROM SERVICE clause provides the service name of the physical standby database from which the files must be restored. During the restore operation, RMAN creates backup sets, on the physical standby database, of the files that need to be restored and then transfers these backup sets to the target database over the network.

Use the SECTION SIZE clause of the RESTORE command to perform a multisection restore operation. To encrypt the backup sets created on the physical standby database, use the SET ENCRYPTION command before the RESTORE command to specify the encryption algorithm used.

To transfer files from the physical standby database as compressed backup sets, use the USING COMPRESSED BACKUPSET clause in the RESTORE command. By default, RMAN compresses backup sets using the algorithm that is set in the RMAN configuration. You can override the default and set a different algorithm by using the SET COMPRESSION ALGORITHM command before the RESTORE statement.

About Recovering Files Over the Network

RMAN can perform recovery by fetching an incremental backup, over the network, from a primary database and then applying this incremental backup to the physical standby database. RMAN is connected as TARGET to the physical standby database. The recovery process is optimized by restoring only the used data blocks in a data file. Use the FROM SERVICE clause to specify the service name of the primary database from which the incremental backup must be fetched.

To use multisection backup sets during the recovery process, specify the SECTION SIZE clause in the RECOVER command. To transfer the required files from the primary database as encrypted backup sets, use the SET ENCRYPTION command before the RESTORE command to specify the encryption algorithm used to create the backup sets.

To compress backup sets that are used to recover files over the network, use the USING COMPRESSED BACKUPSET. RMAN compresses backup sets when it creates them on the primary database and then transfers these backup sets to the target

 

 

 

restore from service

 

可以通过restore .. from service指定的对象类型:

  • database
  • datafile
  • tablespace
  • 控制文件
  • SPFILE

 

restore datafile from service

当在主库Primary丢失/或损坏FILE#=6的user01.dbf数据文件时,可以直接使用restore datafile from service来从standby(其实并不要求一定是DataGuard,只需要是合适的备用库即可)上获得数据文件,例如:

 

select * from v$version;

BANNER                                                                                         CON_ID
------------------------------------------------------------------------------------------ ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production                        0
PL/SQL Release 12.1.0.2.0 - Production                                                              0
CORE    12.1.0.2.0      Production                                                                          0
TNS for Linux: Version 12.1.0.2.0 - Production                                                      0
NLSRTL Version 12.1.0.2.0 - Production                                                           askmaclean.com


RMAN> select name from v$datafile where file#=6;



NAME                                                                            
--------------------------------------------------------------------------------

/s01/oradata/PDPROD/datafile/o1_mf_users_b2wgb20l_.dbf
 

RMAN> alter database datafile 6 offline;

Statement processed




RMAN> restore datafile 6 from service pdstby;

Starting restore at 04-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service pdstby
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /s01/oradata/PDPROD/datafile/o1_mf_users_b2wgb20l_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 04-OCT-14




RMAN> recover datafile 6 from service pdstby;

Starting recover at 04-OCT-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service pdstby
destination for restore of datafile 00006: /s01/oradata/PDPROD/datafile/o1_mf_users_b2wgb20l_.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/04/2014 02:57:09

ORA-19845: error in backupDatafile while communicating with remote database server
ORA-17628: Oracle error 19648 returned by remote Oracle server
ORA-19648: datafile : incremental-start SCN equals checkpoint SCN
ORA-19660: some files in the backup set could not be verified
ORA-19661: datafile 6 could not be verified
ORA-19845: error in backupDatafile while communicating with remote database server
ORA-17628: Oracle error 19648 returned by remote Oracle server
ORA-19648: datafile : incremental-start SCN equals checkpoint SCN



之后recover 并online datafile 6即可

 

具体的几种用法:

 

  • 数据库级别: restore database from service <服务别名>
  • 表空间: restore tablespace from service <服务别名>
  • 控制文件: restore controlfile to ‘指定的位置’ from service <服务别名>
  • SPFILE: restore spfile from service <服务别名>

 

 

通过recover .. from service命令可以通过网络将service指定的数据库的增量备份拉过来在本地做recover从而让本地数据库跟上远程数据库的SCN。

CONNECT TARGET “sys/<password>@standby as sysdba” RECOVER DATABASE FROM SERVICE primary;

recover database from service

 

 

此外上述增量备份还可以是基于压缩备份的:

 

SET COMPRESSION ALGORITHM ‘BASIC';

SET COMPRESSION ALGORITHM ‘LOW';

SET COMPRESSION ALGORITHM ‘MEDIUM';

SET COMPRESSION ALGORITHM ‘HIGH';

 

CONNECT TARGET “sys/<password>@standby as sysdba”

SET COMPRESSION ALGORITHM ‘BASIC';

RECOVER DATABASE FROM SERVICE primary

USING COMPRESSED BACKUPSET;

为12.1 DataGuard配置DGMGRL遇到ORA-16698

为12.1.0.2 DataGuard配置DGMGRL时遇到了ORA-16698错误:

 

 



BANNER                                                                                         CON_ID
------------------------------------------------------------------------------------------ ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production                        0
PL/SQL Release 12.1.0.2.0 - Production                                                              0
CORE    12.1.0.2.0      Production                                                                          0
TNS for Linux: Version 12.1.0.2.0 - Production                                                      0
NLSRTL Version 12.1.0.2.0 - Production                                                              0


DGMGRL> DGMGRL> [oracle@PD009 ~]$ oerr ora 16698
16698, 0000, "LOG_ARCHIVE_DEST_n parameter set for object to be added"
// *Cause:  One or more LOG_ARCHIVE_DEST_n initialization parameters that
//          contain a SERVICE attribute but not the NOREGISTER attribute were
//          set when attempting to create a configuration or add a standby or
//          far sync instance to the configuration.  askmaclean.com
// *Action: If creating a configuration, clear all LOG_ARCHIVE_DEST_n
//          initialization parameters that contain a SERVICE attribute but not
//          the NOREGISTER attribute. If adding a standby database or far sync
//          instance, clear the LOG_ARCHIVE_DEST_n initialization parameter
//          that specifies the database or far sync instance to be added.


这个错误可以通过在Primary 和 Standby上取消log_archive_dest_n参数来解决,实际这一块的参数应当是交给DG broker 来管理了,不再需要人为介入设置。

 



primary:


SQL>  alter system reset log_archive_dest_2 scope=spfile sid='*';

System altered.



SQL> alter system reset log_archive_dest_1 scope=spfile sid='*';

System altered.


SQL> startup force;
ORACLE instance started.

standby:


SQL>  alter system reset log_archive_dest_2 scope=spfile sid='*';

System altered.



SQL> alter system reset log_archive_dest_1 scope=spfile sid='*';

System altered.


重启primary和standby实例

[oracle@PD009 ~]$  dgmgrl sys/oracle@PDPROD
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> create CONFIGURATION PDPROD as primary database is PDPROD CONNECT IDENTIFIER IS  PDPROD;
Configuration "pdprod" created with primary database "pdprod"

add database PDSTBY as CONNECT IDENTIFIER IS PDSTBY MAINTAINED AS PHYSICAL;

Database "pdstby" added

Connected as SYSDBA.
DGMGRL> enable configuration;

Enabled.
DGMGRL> DGMGRL> show configuration;

Configuration - pdprod

  Protection Mode: MaxPerformance
  Members:
  pdprod - Primary database
    pdstby - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 5 seconds ago)

 

PRM-DUL成功案例:为某电信运营商恢复了误truncate的一百多张表

PRM-DUL成功案例:为某电信运营商恢复了误删truncate的一百多张表。东南某电信运营商生产系统数据库部分数据表被误删除的问题,现场工作内容:采用PRM-DUL工具将所需要的共121张表恢复出来。

整个数据库大小在25T左右,由于还原的新环境存储空间有限,还原整个数据库不够现实。初步决定还原方案为先还原SYSTEM和data表空间,然后使用PRM-DUL直接读取数据文件,将数据导出。

实际这个case用户在带库里是由完整备份和归档的,但是实际情况所制约没有那么多空间和时间去从备份里恢复数据了,如果真的那么做,可能至少需要几天时间,而实际恢复业务要求在1天内。所幸的是业务对这些表的完整性要求不高,而且从后期来看在truncate后插入数据的量很少,所以这个case在协商后使用了PRM-DUL成功scan database字典模式下恢复truncate功能来恢复了。

 

最新版PRM-DUL下载地址: http://parnassusdata.com/sites/default/files/ParnassusData_PRMForOracle_3206.zip

免费的PRM-DUL License :http://www.parnassusdata.com/zh-hans/node/122 

PRM-DUL成功案例:恢复了700GB损坏严重的Oracle数据库

PRM-DUL成功案例:恢复了700GB损坏严重的Oracle数据库。 某中原企业存储断电重启后发现其700GB大小的数据库,存在十几万个坏块,数据库无法正常打开使用。

 

用户自行尝试采用ORACLE DUL做恢复,但是由于ORACLE DUL对于该超过500万个col$记录的数据字典出现DC_COLUMNS过大导致的coredump segmentfault,导致ORACLE DUL无法正常使用。

 

该场景中数据块的损坏模式主要是fracture断裂。诗檀软件工程师Biot.wang 通过修改checksum+ tailchk , tailchk=低2位的bas_kcbh+type_kcbh+seq_kcbh,伪装了大部分数据块为可用。此场景中之后exp可以导出大部分数据,但是由于数据字典严重损毁,所以可能出现表上字段混乱或者缺失数据等问题, 大部分情况可以用PRM-DUL的非字典模式(NON-DICT)来解决。

 

在这个案例中由于用户的表和索引过多,导致数据字典异常庞大,ORACLE原厂的DUL在加载数据字典时由于其内存分配方式直接导致出现了coredump segmentfault,而COL$.DAT又过大了,很难处理分片。

 

在这个问题上PRM-DUL由于采用了内置一个derby数据库,所以即便数据字典再大也不会有问题,而且内置数据库中的数据也做了索引,这保证了PRM-DUL能迅速处理字典操作。 另由于此例子中需要恢复的数据表实在太多,达到了几十万张,所以充分利用了PRM-DUL的schema-level Databridge功能。 仅仅花了2天时间就基本处理完这个case了。

 

最新版PRM-DUL下载地址: http://parnassusdata.com/sites/default/files/ParnassusData_PRMForOracle_3206.zip

免费的PRM-DUL License :http://www.parnassusdata.com/zh-hans/node/122 

诗檀软件成功为某西南大型企业从不完整的备份中恢复了数据

诗檀软件成功为某西南大型国企从不完整的备份中恢复了数据; 某西南大型企业数据库的ASM因为加盘意外损坏,用户自行重建ASM DISKGROUP并restore了热备份,由于丢失了归档,数据库无法打开。

诗檀软件工程师biot.wang 通过特殊恢复方案打开了该并不一致的数据库备份,大致经过为:手动修改bootstrap对象 ,commit事务;修改datafile header绕过已经不存在的archivelog,之后打开数据库exp导出数据并重建数据库。但是由于丢失部分redo,导致部分表exp时出现了ORA-600错误,这部分表通过PRM-DUL工具的DataBridge特性来特殊恢复了。

 

最新版PRM-DUL下载地址: http://parnassusdata.com/sites/default/files/ParnassusData_PRMForOracle_3206.zip

免费的PRM-DUL License :http://www.parnassusdata.com/zh-hans/node/122