11g ocm考纲复习:配置Oracle数据库备份

 

在完成这个课程之后,你应该能够完成下列事情:

  • 使用EM去配置备份参数
  • 开启自动参数文件备份
  • 给备份分配通道
  • 进行备份优化的相关配置

 

2.2使用RMAN进行备份

当用RMAN创建一个备份的时候,你可以指定:

  • 类型:完整或增量
  • 文件备份:整个数据库,数据文件,控制文件,参数文件和归档日志文件。
  • 备份类型:镜像拷贝 或备份集

代理选项:传递给媒体库来进行文件的拷贝。

 

一个数据库的备份可以用来重建数据。使用RMAN的备份,可以选择镜像拷贝或者备份集的方式来进行备份。

当用RMAN进行备份的时候,你可以指定:

  • 备份执行的类型:你可以在进行备份的时候指定是进行整库的备份,包含所有文件中使用的数据块(全备),还是进行增量的备份(增量)

如果CONFIGURE CONTROLFILE AUTOBACKUP是启用的,RMAN在进行BACKUP命令的时候会自动备份控制文件和当前的参数文件。

  • 如何备份:对于数据库备份有一些有效的值:DATABASE, DATAFILE, TABLESPACE,

ARCHIVELOG, CURRENT CONTROLFILE, 和 SPFILE。RMAN有另外的命令可以将备份文件移动到磁带。

备份类型:创建镜像拷贝(作为副本),或者备份集(最为备份集)。

-每个备份片的名称格式和路径(format)。

-哪些数据文件或归档应备份(skip)。

-那些在成功完成备份集之后输入的文件应该被删除(delete input)

  • 代理选项:代理选项指定MML如何执行文件拷贝。这个在backup命令中的proxy选项提供了一种RMAN调用MML但是不用知道MML是如何工作的。这个会在后面的课程” 使用RMAN创建备份”中详细介绍。

2.3 备份地点

备份可以写入到:

  • 磁盘目录
  • 媒体管理库

-通常用于灾难恢复,在磁盘备份丢失的时候

-oracle 提供了一个安全的备份

  • 闪回恢复区

-这个是为备份和回复以及闪回数据库所留出的磁盘空间

-定义位置和大小

-文件被OMF自动命名

-文件按需要自动的保留和删除

 

备份可以被写入到指定的磁盘目录,一个媒体管理库,或者闪回恢复区中。指定一个磁盘目录或闪回恢复区,意味着备份以磁盘介质存储。通常,会定期的将这些文件通过媒体管理通道移动到磁带上,来保证磁盘空间的可用性。任意一个存在的磁盘目录,都可以作为一个备份的路径。

如果你配置了一个闪回恢复区,很多备份和恢复的任务对你来说就很简单了。oracle 数据库服务自动为文件命名,并且当空间不足的时候自动删除过期的文件。

注意:更多关于 oracle安全备份的信息,可以参考Oracle Secure Backup

Administrator’s Guide

 

 

2.4 为RMAN进行固定设置

  • RMAN 有一些默认的参数配置。
  • 使用configure 命令来配置:

-配置自动通道

-指定备份策略

-指定创建备份副本的数目。

-设置默认的备份类型(备份集或者镜像)

-限制备份片的大小

-在备份的时候排除指定的表空间

-开启或禁用备份优化

-设置自动备份控制文件

-定义归档保留策略

-为一个设备设置并行度

-在备份的时候设置加密和压缩参数

 

为了简化RMAN的备份和恢复,你可以在RMAN中为每个目标数据库,设置一些国定的配置。这些参数在很多方面控制RMAN的行为。你可以保存固定配置信息例如:通道参数,并行度,和默认设备类型到RMAN资源库中。这些配置经常存放在数据库的控制文件和恢复目录中(如果有的话)。

这些设置有默认的值,这样就允许你可以立马使用RMAN 而不用配置。但是,如果你想设计一个更好的备份和恢复策略,你需要为实现这个策略而改变这些参数的值。你可以是用configure命令来配置RMAN中备份、修复、辅助和维护任务中的固定参数。这些参数在所有的RMAN会话中生效。直到配置被清除或改变。

注意:在RMAN任务(或会话中)只能用set 命令来改变这段周期中的参数配置。

2.5 使用EM 来配置RMAN的参数

你可以使用oracle 企业管理器来为实例配置备份参数。在数据首页,导航栏可用性>备份设置。

备份设置的页面上包含下面3个选项:

  • 设备:用来设置磁盘和磁带的配置参数,包括媒体管理库的参数。
  • 备份设置(在图片中):用来指定备份的设置和主机的认证。
  • 策略:用来在你开始备份之前,设置各种备份和保留策略,例如自动备份控制文件和参数文件。这个策略页面也运行你配置块变化跟踪,这是种加快增量备份的特性。

注意:备份参数的默认参数是被所有的备份使用。当创建一个备份时,一些参数可以在特定的备份中被覆盖。

2.6自动备份控制文件

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

最佳实践:oracle建议开启自动备份控制文件

为了在丢失所有控制文件副本的时候,能更容易的恢复,你应该配置RMAN自动备份控制文件。自动备份控制文件在任何备份的时候发生,将备份当前控制文件作为备份命令中的一部分。如果你的RMAN没有使用恢复目录,强烈建议你使用自动控制文件备份。否则,如果你丢失了控制文件,你的数据库将不可恢复。

为了配置自动备份控制文件,你可以使用EM或使用下列RMAN命令:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

默认情况下。自动备份控制文件是禁用的。如果你开启了控制文件自动备份,RMAN在下列一种或2种场景会自动的备份控制文件和参数文件(如果是使用动态参数文件打开的数据库):

  • 在RMAN资源库中记录了一次成功的备份
  • 一个数据库结构的改变影响到了控制文件的内容,因此必须被备份。

控制文件自动备份的文件名早所有的设备类型上,使用默认%F的格式,这样RMAN就可以推断它的位置,然后重建它而不需要RMAN的资料库。这个变量格式可以转换为c-IIIIIIIIII-YYYYMMDD-QQ,这里:

  • IIIIIIIIII 表示DBID
  • YYYYMMDD 是备份产生的一个时间戳
  • QQ 是一个十六进制的数,从00开始,最大是FF。

你可以使用CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE type TO ‘string’ 命令来改变默认的格式。这个字符串的值必须包含%F,而且不能包含其他的变量格式。例如:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT

FOR DEVICE TYPE DISK TO ‘/u01/oradata/cf_ORCL_auto_%F’;

除非有其他的指定,默认控制文件自动备份到闪回恢复区。控制文件自动备份后,RMAN可以恢复数据库即使当前的控制文件、恢复目录、参数文件都不可用。因为存储的路径是按固定的格式存在自动备份中的,RMAN可以从自动备份中发现并且修复参数文件或控制文件。

 

2.7管理固定参数

  • 使用show命令来列出当前的参数

RMAN> SHOW CONTROLFILE AUTOBACKUP FORMAT;

RMAN> SHOW EXCLUDE;

RMAN> SHOW ALL;

  • 使用configure中的clear选项来重置那些固定参数到默认值:

RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;

RMAN> CONFIGURE MAXSETSIZE CLEAR;

RMAN> CONFIGURE DEFAULT DEVICE TYPE CLEAR;

使用RMAN show命令,你可以查看RMAN的配置参数。如果在目标数据库上运行show all 命令,那么只有这个节点的配置和数据库配置会显示。

你可以在配置参数的时候使用clear选项,来将参数的值重置为默认值。

2.8 配置备份设备

RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 3;

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY;

你可以在RMAN中使用CONFIGURE DEVICE TYPE配置一个设备。

并行度

并行度是设备在读和写的时候,能够使用的数据流的数量。在设备被RMAN使用的时候,并行度由分配的通道的数据引起。例如,如果一个媒体管理有2个磁带设备可用,那么在用RMAN通过媒体管理进行备份的时候,并行2可以同时被所有磁带设备使用。并行对于磁盘类型也很有用,当你想把备份分割到多个磁盘上时。在配置使用设备的时候指定并行,例如:

CONFIGURE DEVICE TYPE <device> PARALLELISM <n>

n就是并行度的值.

备份类型

备份的输出类型可以是备份集或者是镜像拷贝。使用BACKUP TYPE TO在配置设备类型的时候可以为其设置默认值。为备份集指定BACKUPSERT或者为镜像拷贝指定COPY。

压缩

可以在指定备份设备的时候,在BACKUP TYPE TO 子句中使用COMPRESSED来配置压缩。压缩可以使得备份文件更小。

注意:压缩只能用于备份集。

或者

2.9备份的时候配置和分配通道

  • 使用configure命令配置自动通道:

RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 1;

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;

RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt …

RMAN> BACKUP DATABASE;

  • 使用ALLOCATE CHANNEL命令在run块中手动分配通道:

RMAN> RUN

{

ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;

BACKUP DATABASE PLUS ARCHIVELOG;

}

 

在备份的时候选择下列方式来配置通道:

  • 使用configure命令配置自动通道,然后在RUN块中执行BACKUP命令。
  • 在run 块中使用allocate channel命令手动分配通道,然后执行backup命令。

 

 

2.10 配置备份优化

  • 在备份文件的时候,备份命令会跳过那些已经被备份的文件。
  • 适用于下列条件满足的场景:

-备份优化是启用的。

-BACKUP DATABASE, BACKUP ARCHIVELOG命令中使用ALL或LIKE选项,或者是执行BACKUP BACKUPSET ALL命令。

-只有一个通道类型被分配。

  • 它会被force选项覆盖
  • 总是用于:RECOVERY AREA, DB_RECOVERY_FILE_DEST和RECOVERY FILES BACKUP选项。

CONFIGURE BACKUP OPTIMIZATION ON;

 

如果你开启了自动备份优化,BACKUP命令会跳过那些,已经被指定设备类型,备份过的文件。

如果RMAN认为一个文件是相同的,并且它已经被备份了,那么它是将被跳过的文件之一。但是,RMAN会进一步的检查这个文件是否应当被跳过,因为保留策略和备份双工功能都会影响RMAN,在指定的设备类型上是否有足够的备份的计算。

可以参考Oracle Database Backup and Recovery User’s Guide来获取关于RMAN认为一个文件是相同与否的信息和备份优化的标准。

你可以在EM中备份设置页面上开启备份优化,或者使用CONFIGURE BACKUP OPTIMIZATION ON命令。默认情况下,备份优化是禁用的。

备份优化会自动的为BACKUP RECOVERY AREA 、

DB_RECOVERY_FILE_DEST 和 BACKUP RECOVERY FILES命令开启。

你可以在BACKUP命令中指定FORCE选项来覆盖备份优化,和备份所有的文件而不管是否有无变化。例如:

BACKUP DEVICE TYPE sbt BACKUPSET ALL FORCE;

注意 FORCE选项不能应用于那些在恢复区的文件。你可以使用EM配置禁用自动优化或者使用下列命令:

CONFIGURE BACKUP OPTIMIZATION OFF;

2.11 总结

在这个课程,你可以学习到如何:

  • 使用EM配置备份参数
  • 开启控制文件自动备份
  • 在备份中分配通道
  • 配置备份优化

Oracle OCM考试全球日程表

Oracle OCM考试全球日程表

 

ORA-600 [kcbnew_3] 问题诊断

ORA-00600[kcbnew_3] [a] [b] [c] 是Oracle 9i 9.2之后引入的新的块检测机制。一个存有数据块的cache buffer在被重用的过程中,buffer的状态为state且数据为temp或undo。 oracle一致性检查比较了buffer header的block class和传递给调用者的block class并发现不一致。

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

ORA-00600[kcbnew_3]的三个参数随着版本的不同有几种解释:

oracle 11.1和之后:

[a] 内存循环计数器,实际是新初始的块的数目

[b]代码层面访问该cache的object id

[c] flag定义该buffer使用的特点

 

oracle 10.1 和 10.2中:

[a]  内存循环计数器,实际是新初始的块的数目

[b] buffer class

[c] 代码层面访问该cache的object id

 

Oracle 9.2中:

[a]  buffer class

 

该ORA-600 [kcbnew_3] 属于内核内存buffer管理使用,其影响可能导致进程失败、SQL执行失败,但不会造成2次数据损坏

 

 

NB Bug Fixed Description
12391183 11.2.0.3,
12.1.0.0 ORA-600 [kcbnew_3] with "_db_fast_obj_truncate"=false
11902008 12.1.0.0 SMON may crash with ORA-00600 [kcbgcur_3] or ORA-600
[kcbnew_3] during Transaction recovery
9275027 11.2.0.2,
12.1.0.0 ORA-600 [kcbnew_3] can occur after TRUNCATE / DROP
9456964 11.2.0.2 OERI[kcbnew_3] after shrinking an IOT and reusing the blocks
freed by shrink
6970071 10.2.0.5,
11.2.0.1 ORA-600 [kcbnew_3] when recyclebin is active
6730125
10.2.0.5,
11.1.0.7,
11.2.0.1
OERI[kcbnew_3] during instance recovery
6079038 10.2.0.5,
11.2.0.1
Internal Errors from DML with error logging / batch errors
against partitioned table
* 6017420 10.2.0.4,
11.1.0.6 OERI[kcbo_link_q_1] / crash with fix for bug 5454831 installed
5558244 10.2.0.4,
11.1.0.6 OERI[kcbnew_3] can occur
5303237 11.1.0.6 ORA-600 [kcbgtcr_5] during create queue table
5218905 10.2.0.4,
11.1.0.6 OERI[kcbnew_3] when segment advisor has been used
+ 4430244 10.2.0.4,
11.1.0.6
Segment advisor can load blocks of dropped objects into buffer
cache (KCB OERI errors)
+ 5523799 Various OERI (eg kcbgtcr_12) using ASSM managed segments -
superceded
5718371 10.1.0.2 OERI[kcbgtcr_12] / [kcbnew_3] from concurrent CTAS and
DROP TABLE
* 3785200 9.2.0.6, 10.1.0.2 Corruption possible in automatic space managed segments
3085651 9.2.0.5, 10.1.0.2 Table corruption / OERI after TRUNCATE on ASSM table with
NESTED TABLE cols
2768278 9.2.0.4, 10.1.0.2 OERI[KCBNEW_3] possible after DROP of 8i segment in locally
managed tablespace
2747978 9.2.0.4, 10.1.0.2 OERI[KCBNEW_3] after resize of locally managed tablespace
2406802 9.2.0.2 OERI[kcbgtcr_3] / OERI[kcbcxx_1] after DROP TABLE in locally
managed tablespace
2414972 9.2.0.2 OERI:[kcbnew_3]/OERI:[kcbgtcr_3] after resize in LOCALLY
MANAGED tablespace

Oracle 11g OCM的通过祝贺信

Oracle 11g OCM的通过祝贺信

Congratulations! on the successful completion of the Oracle DBA 11g Certified Master practicum.

You are now a member of an elite group of Oracle professionals. You will receive your Oracle DBA 11g Certified Master fulfillment kit that includes a congratulations letter, OCM certificate, OCM ID card, and denim OCM shirt to the mailing address mentioned in your Oracle DBA 11g OCM Hands-on course requirement Form. 
We request you to please submit your mailing address and the shirt size through our Fulfillment Kit Request form in Pearson Vue website. Kindly note you have to enter the Authentication code ‘********’ to submit your Fulfillment Form. We will process your Oracle DBA 11g OCM success kit once this form is submitted.

Please feel free to contact the OCP team at ocpreq_ww@oracle.com if you have questions.

Kind regards,
Oracle Certification Program

11g OCM 自评

以下为博主yetanotherocm对自己的11g OCM考试的情况的自评, 1代表做的很差,5代表做的不错。他在2012年3月通过了11g ocm,此前有一次考试失败的经历。

 

Based on the 11G OCM Upgrade Exam Objectives, I rate myself as follows (1=poor to 5=good):

考试专题 自评 参考
Database, RMAN, EM and Network Configuration
Configure server-side network 4
Configure client-side network 4
Create and Manage encrypted tablespaces 4
Create and Manage a tablespace that uses NFS mounted file system file 4 14 Set Up DirectNFS for Example Database
Set up ADR file based repository 4
Perform cold database backup 5
Manage user accounts and use case sensitive passwords 4 11G OCM Case Sensitive Passwords
Use OPatch to install a patch 4 11G OCM OPatch 11.1.0.7 on GRIDCTRL
Install and configure EM Agent 4 02 Deploy EM Agent on BANKSTOWN
03 Upgrade to EM Agent 10.2.0.5.0
15 Deploy EM Agent on PENRITH Cluster
Create Base Recovery Catalog 5
Configure RMAN 4
Perform multisection backup of a datafile 4
Create an Archival Backup 4
Data Guard
Create Physical Standby Database with real-time apply. 3
Configure the data guard environment to reduce overheads of fast incremental backups on the primary database 4
Configure the Observer 1
Switchover and switch back 3
Convert the standby to a snapshot standby 1
Configure archivelog deletion policy for the dataguard configuration 4
Data and Data Warehouse Management
Troubleshoot fast materialized views to fast refresh and query rewrite 1
Add a tablespace by using Transportable Tablespace Feature of Data Pump Import (cross platform transportable tablespace) 1
Configure a schema to support a star transformation query 4
Configure and use parallel execution for queries 3
Use and access SecureFile LOBS 1
Create partitioned tables (includes reference and interval partitioning) 4
Configure Flashback Data Archive 4
Use Oracle Streams to capture and propagate changes in a table 1
Performance Management
Set up and configure Resource Manager to control active sessions, number of I/Os, execution time..etc 4
Use Result Cache 4
Use multi column statistics 4
Gather statistics on a specific table without invalidating cursors 4
Use partitioned indexes 4
Use SQL Tuning Advisor 5
Use SQL Access Advisor 5
Configure baseline templates 4
Use SQL Plan Management feature 4
Replay a captured workload 4

现在注册: OCA到OCP升级考试来啦

你是否准备将你之前版本的数据库OCA认证升级到最新版本的OCP认证,但在犹豫需要面对多个版本的产品升级考试呢?如果是这样的话,现在是时候来注册此升级考试来作为你的升级计划了。

Oracle 9i/10g OCA、OCP至Oracle Database 11g OCP升级考试” (1Z0-034)和“Oracle 9i/10g/11g OCA、OCP升级Oracle Database 12c OCP升级考试”(1Z0-067) 的上线测试期已过。此两类考试将很快进入正式阶段。现在预约此考试,你很快就能开始此认证,并在不久确认到此认证的价值。

Oracle数据库12c已经因多租户架构和云计算的加入进行了改变。千万别掉队了哦。好好利用此机会。你已经通过获取OCA认证来证明了你基础数据库技能。现在开始升级你的技能,获取最新的OCP认证吧。由于有2条升级路线。参与者可以选择升级至11g或12c。下面列出了每个路线的需求:

 

升级至11g DBA OCP

从Oracle9i或10g数据库管理员OCA认证升级至Oracle数据库11g管理员专家认证(11g DBA OCP):

  1. 进入以下链接:

https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=653&get_params=p_id:51#tabs-4&intcmp=WWOUCERTBLOGPOST0856011315

完成“Oracle Database 11g Administrator OCP – upgrade from Oracle9i or Oracle Database 10g DBA OCA” -> “View Upgrade Path”查看升级路径中的第二部分(Step 2),即完成其中1门课程即可。

  1. 通过考试” Oracle 9i/10g OCA、OCP至Oracle Database 11g OCP升级考试” (1Z0-034)。

 

升级至12c DBA OCP

从Oracle 9i, 10g或11g OCA认证升级至Oracle数据库12c管理员专家认证(12c DBA OCP)

  1. 进入以下链接:

https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=653&get_params=p_id:249#tabs-3&intcmp=WWOUCERTBLOGPOST0856011315

完成“Oracle Database 12c Administrator OCP – upgrade from Oracle9i, Oracle Database 10g or 11g DBA OCA” -> “View Upgrade Path” 查看升级路径中的第二部分(Step 2),即完成其中1门课程即可。

  1. 通过考试”Oracle 9i/10g/11g OCA、OCP升级Oracle Database 12c OCP升级考试” (1Z0-067)。

大家可以访问pearsonvue.com/oracle并注册1Z0-034或1Z0-067升级考试。你可以从下列Oracle认证网站快速链接部分中获取所有考试准备细节,包括考点,题量,考试时间和认证价格等。

关于OCA到OCP的升级路径

 

快速链接

现在注册

如何准备Oracle数据库11g OCM认证考试

 

自从2天前第三个人问到如何准备OCM考试的时候,我们决定写一篇文章来对公众进行回答。这将使得大家可以通过自己来找到认证的官方路径。并对如何准备考试以及准备多长时间进行一个指导。

 

如何建立一个测试环境

首先你需要建立一个测试环境。许多人会问需要安装什么版本的软件。正确的回答是在Oracle Database 11g Certified Master Exam页面,查看”Exam Environment”部分。我常听到的下一个问题是: 测试环境里应该包含多少服务器?我认为这需要你自己去考虑,我这里仅给你一些提示:

  1. 请查看考试中是否包含有RAC部分。如果有,那你至少需要2台主机。:)
  2. 请查看考试中是否包含有Grid Control相关部分。如果有,那你应该将它装在某个地方,是么?:)

 

考试包含了些什么以及最好的准备方法是什么

你可能不相信,但考试包含的内容真都在Oracle Database 11g Certified Master Exam页上。你仅仅需要浏览每个在”Exam Topics”部分下的分类,然后想一些最最难完成的任务并不断地实践,实践,再实践… 在此之后,更多地实践 🙂

举个例子,考虑“建一个数据库”的任务。并想着此场景如何尽可能地复杂。你可能需要想到有好几个不同的场景被考试用到…

 

需要花多久来准备这场考试

如果你有一个正常工作的话(5天每天工作8小时),建议大家有一个2个月的技术训练计划。在工作日每天花1~2小时,在周末每天花4~5小时来做准备。对于一个有3~4年工作经验的DBA来说,准备这个考试大约需要花个80~130小时时间。当然,如果你是个有15年DBA工作经验的人,我们仍然建议按此时间框架来做。:)

 

哪些信息资源是我们建议你准备的?

对于此认证考试,主要的准备资源来自http://docs.oracle.com/cd/E11882_01/index.htm

 

你是否认为OCM的考试成本太高了?不合理?

不可否认这个认证考试很贵。然而,它取决于你拿它和什么做比较。让我们来看看”Cisco Certified Internetwork Expert”思科专家认证考试。如果你比较过价格,你会发现它和OCM考试费用相当。然而,如果你浏览过其需要满足的要求,你会发现它的需求长得多。我的一些搞网络的朋友需要花更多努力来满足CCIE认证需求。此外你会发现CCIE的通过率只有30%。基于朋友的反馈,一般通过CCIE认证考试需要尝试3次。如果你第二次就过了,那真是太幸运了。如果你比较了OCM和CCIE,OCM看上去就不那么贵了。而且需要告诉大家的是,并不是需要准备所有内容来满足OCM需要,对于考试内容的主次,大家的学习可以分下重点。

 

综上,如果你接受上面的建议的话,那么你很有机会一次通过OCM考试。仅认真地准备2一个月,搭好你的测试环境,够勇敢 🙂 。

data pump expdp/impdp丢失权限角色 grant role的问题

对于使用data pump的 数据迁移,expdp/impdp可能存在丢失权限角色 grant role的问题

对于此类问题 , 可以考虑使用 下面的expdp/impdp脚本仅仅导出希望导出的用户的权限和相关角色

 

JOB_NAME=EXPDP_USERS
DIRECTORY=DTPUMP
FULL=Y
DUMPFILE=users_privs.dmp
LOGFILE=users_expdp.log
INCLUDE=SCHEMA:”IN (SELECT USERNAME FROM dba_users where default_tablespace NOT in(‘SYSAUX’,’SYSTEM’))”,ROLE,USER,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,TABLESPACE_QUOTA

impdp file should look like this:

JOB_NAME=IMPDP_USERS
DIRECTORY=DTPUMP
DUMPFILE=users_privs.dmp
LOGFILE=users_impdp.log
EXCLUDE=TABLE

Oracle INDEX SKIP SCAN

Oracle INDEX SKIP SCAN

 

Oracle 9i Skip Scan
Objectives:
An improvement over index range/full scan when there are non-equality start/stop
keys.
Example(to be walked through):
create index si on emp(deptno, sal);
select count(*) from emp
where deptno > 10 and sal = 35000;
Oracle 9i Skip Scan
Functionality:
Optimizer hints: index_ss(), index_ss_desc() and index_ss_asc().
Index types not supported: single column indexes, reverse indexes,
functional indexes or bitmap indexes.
Predicates not supported: like.
Oracle 9i Skip Scan
Optimizer(ask Hakan Jakobsson for details):
1. kkofmx(): mark index and/or skip- scan -only.
2. kkonxc(): exclude index types and predicates not supported.
3. kkessc(): cost skip and range scan and choose one accordingly.
4. skip scan cost: (est. no. of distinct keys:) * (no. of leaf blocks) /

 

(no. of distinct keys in the index)
Oracle 9i Skip Scan
QKA/Row Source/PQ(ask Chi Ku for details):
1. FROKISSA/FROKISSD: ascending/descending skip scan
access path.
2. RWSTSSL/RWSTSSP: IOT/regular index skip scan row sources.
3. kkfdtbl(): call kkfdtip() to allocate an index skip scan DN.
Oracle 9i Skip Scan
Index layer(ask Dmitry Potapov for details: kdiss.c kdiss.h)
kdiss/KDITSK: index skip scan state and index skip scan type.
Methods: kdiss_init()/kdiss_fetch()/kdiss_end_ scan ().
Start/Stop keys: skip scan uses column key values to qualify index
rows.
Start/Stop Operators: skip scan uses start/stop operator to qualify
index rows.
Oracle 9i Skip Scan
Index Layer(continued):
kdiss_init(): initialize the skip scan state.
kdiss_fetch(): performs binary search with backtrack starting from
root using start/stop keys and start/stop operators to
qualify index rows. The path from root to the current
leaf block are pinned during the scan .

 

kdiss_end_ scan (): releases all pins and resources, as well as, nullify
the scan state.
Oracle 9i Skip Scan
Performance:
Skip scan performance is a function of no. of distinct keys.
Experiments on a Sun Ultra 60: 1504 2K leaf blocks
create table test (a number, b number, c number, d number);
create index test_abcd on test(a, b, c, d);
load test with 100000 rows which as 10 distinct values in a,
100 distinct values in b, 1000 disctinct values in c and 10000
distinct values in d.
Oracle 9i Skip Scan
select count(d) from test where b = 10;
skip scan /f.f. scan : 0.1/0.9 second; cons. reads: 50; bin srch: 924
select count(d) from test where c = 10;
skip scan /f.f. scan : 0.09/0.77 second; cons. reads: 135; bin srch: 1984
select count(d) from test where d = 10;
skip scan /f.f. scan : 1.31/0.78 seconds; cons. reads: 1041;
bin srch: 11702

12.1.0.2 Patch Set – Availability and Known Issues

Known Issues specific to the 12.1.0.2 Patch Set

  • See Note:1565065.1 for Support Status and Alerts affecting 12.1.0 releases.

This note lists known install / alert issues specific to the 12.1.0.2 Patch Set.
It should be read in conjunction with the platform specific release notes.This note includes details of:

  • Known install issues specific to 12.1.0.2, both generic and port specific
  • Bugs introduced in 12.1.0.2 (for features present in earlier releases)
  • Alerted issues which affect 12.1.0.2
  • Availability details for 12.1.0.2 by platform

This note does NOT list ALL issues which affect this release.
To get an indication of other issues known to affect this version you can search My Oracle Support for “Patches” which exist for your platform / release.

12.1.0.2 General Notes

  • 12.1.0.2 is only released as Enterprise Edition at this time
    See Note:1905806.1 “Oracle Database 12c Release 1 Patchset 1 (12.1.0.2) is being released only as Enterprise Edition at this time” for more details.
  • 12.1.0.2 New Features
    In addition to bug fixes and stability enhancements this Patch Set Release includes a number of new features – see Oracle Database 12c Release 1 (12.1.0.2) New Features .
  • Database Security Patching from 12.1.0.1 onwards
    Starting with Oracle Database version 12.1.0.1 , Oracle will only provide Patch Set Update (PSU) patches to meet the CPU program requirements for security patching. See Note:1581950.1 for full details.

Latest Critical Patch Update

For information about Critical Patch Updates see the Oracle Technology Network page “Critical Patch Updates and Security Alerts“. Note that for Database, Critical Patch Updates are a subset of each Patch Set Update. The latest Patch Set Update for this release is listed in the next section. For further information about Patch Set Updates see Note:854428.1.

Document Summary
Note:467881.1 Critical Patch Update Advisory (Jul 2015)
Note:2005667.1 Critical Patch Update Availability for Database Products (Jul 2015)

 

Current Recommended Patches

For information about Recommended Patches for the RDBMS see Note:756671.1 .

Patch Set Updates

Document Description Rolling RAC Patch Download
Note:21150768.8 Combo of 12.1.0.2.4 OJVM PSU and 12.1.0.2.4 DB PSU (Jul 2015) Part Patch:21150768
Note:21068507.8 Oracle JavaVM Component 12.1.0.2.4 Database PSU (Jul 2015) (OJVM PSU) No Patch:21068507
Note:20831110.8 12.1.0.2.4 (Jul 2015) Database Patch Set Update (DB PSU) Yes Patch:20831110

Grid Infrastructure

Document Description Rolling RAC Patch Download
Note:21150782.8 Combo of 12.1.0.2.4 OJVM PSU and 12.1.0.2.4 GI PSU (Jul 2015) Part Patch:21150782
Note:20996835.8 12.1.0.2.4 (Jul 2015) Grid Infrastructure Patch Set Update (GI PSU) Yes Patch:20996835

Oracle Engineered Systems

Document Description Rolling RAC Patch Download
Note:888828.1 Recommended Patch Information for Exadata Database Machine and Exadata Storage Server
Note:21188742.8 12.1.0.2 Bundle Patch 10 for Engineered Systems and DB In-Memory (Jul 2015) Yes Patch:21188742
Note:21150792.8 Combo of 12.1.0.2.4 OJVM PSU and 12.1.0.2.10 DBBP (Jul 2015) Part Patch:21150792

Database In-Memory (DBIM)

Document Description Rolling RAC Patch Download
Note:21188742.8 12.1.0.2 Bundle Patch 10 for Engineered Systems and DB In-Memory (Jul 2015) Yes Patch:21188742
Note:21150792.8 Combo of 12.1.0.2.4 OJVM PSU and 12.1.0.2.10 DBBP (Jul 2015) Part Patch:21150792

12.1.0.2 Alerts / Issues

This section lists alerts and important issues relevant to 12.1.0.2 .

General Alerts / Issues

Bug/Doc Description Updated
20881450+ Wrong results or Assorted dumps and errors querying HCC tables with OLTP blocks 20/Jul/2015
20144308+ ORA-27086 or ORA-1182 RMAN May Overwrite a SOURCE Database File during TTS, TSPITR, etc when OMF is used in SOURCE. ORA-1578 ORA-1122 in SOURCE afterwards 14/Jul/2015
20369110 ORA-600[9999] / Cannot enable more than 8 kernel options (such as uniaud , olap, lbac etc..) 14/Jul/2015
Note 1944645.1* ORA-600 [kdblkcheckerror]..[6266] corruption with self-referenced chained row. ORA-600 [kdsgrp1] / Wrong Results / ORA-8102 14/Jul/2015

Upgrade Issues

Bug/Doc Description Updated
19787643P Windows: SQLLDR.EXE – SQL Loader fails to start – oranfsodm12.dll is missing 19/Nov/2014
19141838 ORA-600 [qksanGetTextStr:1] from SQL Plan Management after Upgrade to 12.1 02/Jun/2015
20123899 Insert in to stat table fails with ORA-932 “inconsistent datatypes” after upgrade from 11g to 12c (part 2) 06/May/2015
15894842 Insert in to stat table fails with ORA-932 “inconsistent datatypes” after upgrade from 11g to 12c (part 1) 06/May/2015
19664340+ ORA-20000 “unable to gather statistics concurrently” during upgrade to 12.1 14/Jul/2015
19536415 DB upgrade to 12.1 fails with ORA-600 [kkaegso: PUBLIC] 14/Apr/2015
19291380 ORA-38802/ORA-38803/ORA-38804 from CREATE EDITION or SET EDITION during upgrade 14/Apr/2015

 

Notable fixes included in 12.1.0.2

This section lists fixes / enhancements in 12.1.0.2 which may cause a notable change in behaviour.

Note:1927261.1C OPS$ users identified with a password (OS_AUTHENT_PREFIX) can connect using OS authentication – correctly raise ORA-1017 in 12.1.0.2 onwards
14675058C A package procedure with zero arguments should not have a row in ALL_ARGUMENTS

 

Issues introduced in 12.1.0.2

This section lists bugs introduced in 12.1.0.2 (if any). Such issues may be either serious or trivial but the aim is to list them all to help customers assess the risk of applying the Patch Set on top of 12.1.0.1

Bug/Doc Description Updated
20881450+ Wrong results or Assorted dumps and errors querying HCC tables with OLTP blocks 20/Jul/2015
21030693 ORA-600 [qerpxMObjVI6] from parallel query on partitioned table 14/Jul/2015
20879889 Open cursor leak from DML on table with a materialized view log 16/Apr/2015
20634449 Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause in 12.1.0.2 14/May/2015
20466322 ASSM Free block space not reused when expected 14/Jul/2015
20406840 Pro*C 12.1.0.2 throws ORA-600 [17998] when precompiling by ‘other’ user (not Oracle install user) 23/Jul/2015
19787643PI Windows: SQLLDR.EXE – SQL Loader fails to start – oranfsodm12.dll is missing 19/Nov/2014
19567916 Wrong results when GROUP BY uses nested queries in 12.1.0.2 24/Sep/2014
20794034 ORA-600 [rworupo.1] from SQL with window function and large columns 24/Jun/2015
18881811 Data pump import of wrapped PLSQL corrupts the body 12/Apr/2015
19703301 ORA-22275 “invalid lob locator specified” if fix for bug 14044260 present 13/Jul/2015
19687159 Excess memory use / ORA-4030 for SQL with many WINDOW operations and large sort keys – superseded 14/Apr/2015
19627012 ORA-7445 [kd9ir2tComputeTreeSErcs] inserting / loading to a compressed table 14/Apr/2015
19018447 ORA-7445 [ksxpmsgcncl] / ORA-600 [15810] from Parallel Query 14/Apr/2015
19134173 ORA-600 [kghfrf:nxt] writing diagnostics for ORA-4021 14/Apr/2015

12.1.0.2 Availability

The following 12.1.0.2 releases are currently available.
Platform specific install / upgrade issues are listed under each heading but also see above for GENERIC issues.For unlisted platforms check Note:742060.1 for the Release Schedule.

In the table clicking on the hyperlinks beginning with “Patch” should take you directly to the relevant patch download page, clicking on the “ReadMe” link should take you directly to the latest version of the ReadMe for the Patch Set, and clicking on “Certification” shows certification information for the platform.

HPUX Itanium 32-bit Client Certification
12.1.0.2 Patch 17694377 released 14-NOV-2014 ReadMe
HPUX Itanium 64-bit Certification
12.1.0.2 Patch 17694377 released 14-NOV-2014 ReadMe
IBM AIX Based Systems 32-bit Client Certification
12.1.0.2 Patch 17694377 released 14-NOV-2014 ReadMe
IBM AIX Based Systems (64-bit) Certification
12.1.0.2 Patch 17694377 released 14-NOV-2014 ReadMe
IBM Linux on System z (32-bit client)
12.1.0.2 Patch 17694377 released 14-NOV-2014 ReadMe
IBM Linux on System z (64-bit) Certification
12.1.0.2 Patch 17694377 released 14-NOV-2014 ReadMe
Linux 32-bit Client Certification
12.1.0.2 Patch 17694377 released 22-JUL-2014 ReadMe
Linux x86-64 Certification
12.1.0.2 Patch 17694377 released 22-JUL-2014 ReadMe
Note:888828.1MP Recommended Patch Information for Exadata Database Machine and Exadata Storage Server
Oracle Solaris on x86 32-bit Client
12.1.0.2 Patch 17694377 released 22-JUL-2014 ReadMe
Oracle Solaris on x86-64 Certification
12.1.0.2 Patch 17694377 released 22-JUL-2014 ReadMe
Oracle Solaris on Sparc 32-bit Client Certification
12.1.0.2 Patch 17694377 released 22-JUL-2014 ReadMe
Oracle Solaris on Sparc 64-bit Certification
12.1.0.2 Patch 17694377 released 22-JUL-2014 ReadMe
Windows 32-bit Certification
12.1.0.2 Patch 17694377 released 26-SEP-2014 ReadMe
19787643P Windows: SQLLDR.EXE – SQL Loader fails to start – oranfsodm12.dll is missing
Windows 64-bit (x86-64) Certification
12.1.0.2 Patch 17694377 released 26-SEP-2014 ReadMe
19787643P Windows: SQLLDR.EXE – SQL Loader fails to start – oranfsodm12.dll is missing

Symbols used in this document

In the lists above

  • ‘*’ indicates that an alert exists for that issue.
  • ‘+’ indicates a particularly notable issue / bug.
  • See Note:1944526.1 for details of other symbols used

 

沪ICP备14014813号-2

沪公网安备 31010802001379号