Lyon's Oracle Store

By my understanding the main difference between Sync and Async for VVR is how VVR writes VVR log and ship it to DR side.  查看全文

Lock mode in which the process requests the lock:
¦ 0 - none
¦ 1 - null (NULL)
¦ 2 - row-S (SS)
¦ 3 - row-X (SX)
¦ 4 - Table share (S)
¦ 5 - Table Share and Row-X (SSX)
¦ 6 - Table exclusive (X)

Conclusion in 10gR2.

1> create index az_obj_id. (4-TM)

2>
alter index az_obj_id rebuild online; (2-TM and 4-TM)

3>
alter index az_obj_id rebuild; (4-TM and 3-TM)

4>
alter table az move tablespace test; (6-TM)

5>
alter table az shrink space; (3-TM and 6-TM But the amount of time for TM 6 is far more less than ALTER TABLE MOVE)

6>
alter table az shrink space COMPACT; (3-TM only)
and then alter table az shrink space; (6-TM only)
Please see shrinkSpaceIn10g.doc in the same direcotry to find the mechanism for SHRINK SPACE in Oracle 10g.

7> truncate table az;(DDL) (6-TM)

http://space.itpub.net/756652/viewspace-442206 查看全文

Below is the process of the testing.

Primary Side. O01DMS0
Target Side. O01LEE3

This article is intended to quickly setup a stream environment for playing in which the schema HR in primary side will be in sync with the one in target side. 查看全文

For these examples, assume the following statements have been issued: 查看全文

What is Partition Pruning?

Some tables are so large that a Full Table Scan is unthinkable. If these tables are not partitioned, they should be.

Use the following approach to create tables with constraints and indexes:

http://www.dbspecialists.com/files/presentations/bulk_binds.html

The same configuration as the case one. 查看全文

Advanced Backup & Recovery For Oracle Dataguard based on The Reference Using Recovery Manager with Oracle Data Guard in Oracle Database 10g

In a standby environment, backing up data files and archived redo log files taken on the primary or standby system are usable on either system for recovery. Although some files such as the control file and SPFILE must be backed up on the primary database, the process of backing up data files and archived redo log files can be off-loaded to the standby system, to minimize the effect of backups on the production system.

Only those archived redo log files that were created by the standby instance can be backed up at the standby site. If there were any archived redo log files generated before the standby database was started, they must be backed up on the primary database. For example, if the first log sent from the primary database to the standby is log sequence 100 thread 1, then the backup of archived redo log files whose log sequence is less than 100 must be done on the primary database.

So I initiated this series by the following case hoping that it could give you some help and hints on this. During the next few days, I will update other case to record the process of my experiment.

Case One -- Recover a datafile in primary site from the backup taken in standby site using catalog.

10.248.38.170 RAC01 Primary DB (under MAXIMUM AVAILABILITY protection mode)
10.248.38.171 RAC02 Standby DB

SQL> select name,database_role role,log_mode,
2 protection_mode,protection_level
3 from v$database;

NAME ROLE LOG_MODE PROTECTION_MODE PROTECTION_LEVEL
---------- ------- ------------ -------------------- --------------------
O01DMS0A PRIMARY ARCHIVELOG MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Being aware of that in this case a shared directory (/archbk) is exported across the two node using NFS which makes the backup & recovery a little easier.

The configuration for UFS at Primary site.

[oracle@RAC01 o01dms0arch]$ cat /etc/exports
/sharedisk *(rw,sync,no_wdelay,insecure_locks,no_root_squash)
[oracle@RAC01 o01dms0arch]$ cat /etc/fstab# This file is edited by fstab-sync - see 'man fstab-sync' for details
/dev/VolGroup00/LogVol00 / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
none /dev/pts devpts gid=5,mode=620 0 0
none /dev/shm tmpfs defaults 0 0
none /proc proc defaults 0 0
none /sys sysfs defaults 0 0
/dev/VolGroup00/LogVol01 swap swap defaults 0 0
/dev/hda /media/cdrom auto pamconsole,exec,noauto,managed 0 0
/dev/fd0 /media/floppy auto pamconsole,exec,noauto,managed 0 0
nas1:/sharedisk /archbk nfs rw,bg,hard,nointr,tcp,vers=3,timeo=300,rsize=32768,wsize=32768,actimeo=0 0 0

The configuration of NFS at Standby site.

[oracle@RAC02 archbk]$ cat /etc/fstab
# This file is edited by fstab-sync - see 'man fstab-sync' for details
/dev/VolGroup00/LogVol00 / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
none /dev/pts devpts gid=5,mode=620 0 0
none /dev/shm tmpfs defaults 0 0
none /proc proc defaults 0 0
none /sys sysfs defaults 0 0
/dev/VolGroup00/LogVol01 swap swap defaults 0 0
/dev/hda /media/cdrom auto pamconsole,exec,noauto,managed 0 0
/dev/fd0 /media/floppy auto pamconsole,exec,noauto,managed 0 0
nas1:/sharedisk /archbk nfs rw,bg,hard,nointr,tcp,vers=3,timeo=300,rsize=32768,wsize=32768,actimeo=0 0 0

Configuration for RMAN

[oracle@RAC01 o01dms0arch]$ rman target / catalog rman/cat@catadb

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jun 20 14:45:01 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: O01DMS0A (DBID=231561342)
connected to recovery catalog database

RMAN> show all;

starting full resync of recovery catalog
full resync complete
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/archbk/O01DMS0A_%U_BK.bak';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/archbk/snapcf_O01DMS0A.f';

 查看全文

忙过了上一周上线的日子 终于可以喘口气了 总结一下目前发现的应用问题.

1 还是发现了低效pl/sql

2 还要加强对系统设计人员对于函数索引的培训...

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 770,883.85 18,746.07
Logical reads: 19,073.28 463.82
Block changes: 3,656.68 88.92
Physical reads: 698.60 16.99
Physical writes: 278.54 6.77
User calls: 1,313.17 31.93
Parses: 703.90 17.12
Hard parses: 18.46 0.45
Sorts: 48.68 1.18
Logons: 0.29 0.01
Executes: 1,469.75 35.74
Transactions: 41.12

% Blocks changed per Read: 19.17 Recursive Call %: 57.80
Rollback per transaction %: 0.00 Rows per Sort: 115.45

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.26 Redo NoWait %: 100.00
Buffer Hit %: 96.36 In-memory Sort %: 100.00
Library Hit %: 99.23 Soft Parse %: 97.38
Execute to Parse %: 52.11 Latch Hit %: 99.58
Parse CPU to Parse Elapsd %: 10.57 % Non-Parse CPU: 98.97

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 92.75 92.78
% SQL with executions>1: 1.34 0.84
% Memory for SQL w/exec>1: 1.71 1.31

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 1,145,585 9,031 60.85
buffer busy waits 171,815 1,328 8.95
CPU time 1,323 8.92
global cache null to x 171,035 758 5.11
buffer busy global cache 22,043 504 3.39
-------------------------------------------------------------

如果我们的备份策略是:

周日0级

周一，二 1 differential

周三 1 cumulative

周四,五 1 differential

周六 1 cumulative

我们可以通过查询数据字典试图v$backup_datafile了解到每天数据块修改的情况.

可以被lock manager并行锁的enqueue resources的最大数量由ENQUEUE_RESOURCES初始化参数控制.该参数默认值通常来讲已经足够,如果在应用程序中使用并行DML操作,可以考虑增加ENQUEUE_RESOURCES的值. 查看全文

当处理enqueue等待时,熟记这些要点:

1 Enqueues 是应用到数据库对象的locks.与latch(应用于SGA内存)不同.
2 Enqueues 是由应用程序发起,具有事务性.(具体参见下文)
3 Oracle session 正在等候获取一个特定的enqueue. enqueue的名字和mode被记录在P1参数里.
针对不同类型的enqueue竞争应采用不同的方式解决.
4 一直到Oracle 9i,enqueue wait event 代表所有enqueue waits；从Oracle 10g开始enqueue waits被分类定义(下文详细介绍).