Author: mac

Oracle错误:ORA-39125,ORA-31642,DBMS_CDC_EXPDP.SCHEMA_CALLOUT一例

有网友提问在使用expdp时出现了ORA-39125、ORA-31642错误，具体错误信息如下： Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting “EXPALL”.”SYS_EXPORT_FULL_04″: expall/******** directory=expdp_dir full=y dumpfile=exp_tposdb_full.dmp logfile=exp_tposdb_ full.log Estimate in progress using BLOCKS method… Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB [] ORA-31642: the…

September 30, 2011
Script:收集Flashback Database Log诊断信息

以下脚本可以用于收集10g以后的闪回数据库日志Flashback Database Log的诊断信息: WITH flashback_database_log AS (SELECT ROUND((SYSDATE – oldest_flashback_time) * 24 * 60, 2) oldest_log_minutes, retention_target retention_target_minutes, flashback_size / 1048576 flashback_size_mb, estimated_flashback_size / 1048576 estimated_flashback_size_mb FROM v$flashback_database_log), flashback_database_logfile AS (SELECT COUNT(*) logs, SUM(BYTES / 1048576) size_mb, MIN(first_time) oldest_log, MAX(first_time) latest_log FROM v$flashback_database_logfile), flashback_usage AS (SELECT file_type, ROUND(mb_used, 2) mb_used, ROUND(mb_reclaimable, 2) mb_reclaimable, DECODE(total_mb, 0,…

September 30, 2011
Script:列出Oracle每小时的redo重做日志产生量

以下脚本可以用于列出最近Oracle数据库每小时估算的redo重做日志产生量，因为估算数据来源于archivelog的产生量和大小，所以数据是近似值，可供参考: WITH times AS (SELECT /*+ MATERIALIZE */ hour_end_time FROM (SELECT (TRUNC(SYSDATE, ‘HH’) + (2 / 24)) – (ROWNUM / 24) hour_end_time FROM DUAL CONNECT BY ROWNUM lag_next_time THEN(next_time + (1 / 24) – hour_end_time) * (size_mb / (next_time – lag_next_time)) ELSE 0 END + CASE WHEN hour_end_time < lead_next_time THEN(hour_end_time - next_time) * (lead_size_mb…

September 30, 2011
Script:收集11g Oracle实例IO性能信息

以下脚本可以用于收集Oracle Instance I/O 性能信息： set linesize 80 pagesize 1400; SELECT ios.filetype_name, df.file_name, df.tablespace_name, ios.small_read_megabytes + ios.large_read_megabytes read_megabytes, ios.small_write_megabytes + ios.large_write_megabytes write_megabytes, ios.small_read_reqs + ios.large_read_reqs read_reqs, ios.small_write_reqs + ios.large_write_reqs write_reqs, ios.small_read_servicetime + ios.large_read_servicetime read_servicetime, ios.small_write_servicetime + ios.large_write_servicetime write_servicetime, ios.small_read_reqs, ios.small_read_servicetime FROM v$iostat_file ios, dba_data_files df WHERE ios.filetype_name = ‘Data File’ AND df.file_id = ios.file_no UNION ALL…

September 30, 2011
Script:检查数据库当前是否有备份操作在执行中

以下脚本可以用于检测数据库当前是否有备份操作在执行中: SELECT DECODE(os_backup.backup + rman_backup.backup, 0, ‘FALSE’, ‘TRUE’) backup FROM (SELECT COUNT(*) backup FROM gv$backup WHERE status = ‘ACTIVE’) os_backup, (SELECT COUNT(*) backup FROM gv$session WHERE status = ‘ACTIVE’ AND client_info like ‘%rman%’) rman_backup /

September 30, 2011
检查Oracle数据库软件是Enterprise Edition或Standard Edition的10种方法

我们在接手企业的产品数据库时首先总是要了解一下数据库的版本、版本号等信息。 Oracle数据库软件根据用户不同的需求分成多种版本(Database Edition)，它们分为： Oracle Database Standard Edition One :delivers unprecedented ease of use, power, and performance for workgroup, department-level, and Web applications. From single-server environments for small business to highly distributed branch environments, Oracle Database Standard Edition One includes all the facilities necessary to build business-critical applications. Oracle Database Standard Edition:delivers the unprecedented ease of…

September 27, 2011
Slide:了解真实的Oracle Unbreakable Database Appliance

了解真实的Oracle unbreakable database appliance View more documents from Maclean Liu

September 26, 2011
Script:GoldenGate For Oracle数据库预检查脚本

Script:GoldenGate For Oracle数据库预检查脚本 select * from dba_logstdby_not_unique where owner=’&OWNER’; set null “NULL VALUE” set feedback off set heading off set linesize 132 set pagesize 9999 set echo off set verify off set trimspool on col table_name for a30 col column_name for a30 col data_type for a15 col object_type for a20 col constraint_type_desc…

September 26, 2011
expdp+compression性能测试

以下在11.2.0.3上，2 cores+ext3文件系统，导出schema下10G数据，使用compression+ parallel=2 ，耗时大越10分钟，生成的dump文件总共4.7GB大小 [oracle@mlab1 ~]$ expdp dumpfile=HDUMP:bcm%U.dmp filesize=2048M compression=all schemas=bcm parallel=2 logfile=HDUMP:bcm.log Export: Release 11.2.0.3.0 – Production on Wed Sep 26 11:56:19 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production…

September 26, 2011
Database Appliance并非Mini版的Exadata-还原真实的Oracle Unbreakable Database Appliance

Oracle甲骨文系统有限公司在北京时间9月23日发布了一款Oracle数据库机即Oracle Database Appliance。Oracle Database Appliance是一款面向中小型企业的使用简单、用得起的高可用数据库专用服务器，该数据库机基于Sun Fire服务器、Oracle Enterprise Linux和Oracle Database Server 11g release 2 Enterprise Edition，构成一套双节点的Oracle Real Application Cluster集群以供使用。 Database Appliance的正面图 Database Appliance的后视图 Oracle Unbreakable Database Appliance的硬件采用4-RU的机架。包括以下这些硬件配置：共享磁盘 24 SAS dual ported disk slots 20 x 600GB 12TB RAW, 4 TB usable 4 x 73GB SSD (Flash Disks) for redo logs…

September 26, 2011