How to setup Data Guard Broker and enable Fast Start Failover in 11g?

Environment: 11g, initial primary db ‘upup’, standby db ‘upup1′
Here are the major steps:
1.Setup primary db and standby db
2.using spfile instead of pfile for both standby db and primary db.
3.set DG_BROKER_START = true
4.Set parameters of data guard broker configuration files
On the server you want to setup the broker, in my case, it is the primary db server
dg_broker_config_file1 [...]

How to operate lob by using DBMS_LOB in 11g

–Test version: 11.1.0.6
–The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs.
–DBMS_LOB can read and modify BLOBs, CLOBs, and NCLOBs;it provides read-only operations for BFILEs
Temporary LOB
-Stored in temporary tablespaces, deleted at the end of the session by default
Internal LOB
-LOB columns are defined in a table
-Use DML to initialize or populate [...]

Result cache in oracle 11g

DB version: 11.1.0.6
*Result cache:
The result cache stores the results of SQL queries and PL/SQL functions in an area called Result Cache Memory in the shared pool.
Server result cache is detetermined by below 3 parameters:
result_cache_max_result              integer     5
result_cache_max_size                big integer 384K
result_cache_mode string      MANUAL
if result_cache_mode is AUTO, no hint is requred to cache the sql result. It is [...]

Step by step test Sql Performance Analyzer of Real Application Test

–Test version: 11.1.0.6
–
–create an analysis task
–
–Use the following procedures to create a sql tuning set
– populate the tuning set from the cursor cache
DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => ‘my_sts’,
description  => ‘Sql tunning set for SPA’);
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
‘parsing_schema_name <> ”SYS” ‘,
NULL, NULL, NULL, NULL, 1, NULL,
‘ALL’)) P;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => ‘my_sts’,
populate_cursor => cur);
END;
/
–show sql tune set contents
SELECT * FROM [...]

Step by step test Database Replay of Real Application Test

–Test version: 11.1.0.6

–
–1.Capture workload
–
Before capturing a database workload, carefully consider the following options:
*Restarting the Database
–to avoid capture partial transaction
*Defining the Workload Filters
–inclusion filters or exclusion filters, not both
*Setting Up the Capture Directory
–capturing a database workload using APIs, Adding and Removing Workload Filters
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
fname => ‘user_t’,
fattribute => ‘USER’,
fvalue => ‘T’);
END;
/
–this will filte out all sessions beloings [...]

Step by step test Sql Plan Management

–Test Version: 11.1.0.6
–Plan history is maintained only for repeatable SQL statements.A SQL statement is recognized as repeatable when it is parsed or executed again after it has been logged.
–How to turn on Automatic Plan Capture
Alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE
–Manual Plan Loading
–Loading Plans from SQL Tuning Sets
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => ‘tset1′);
END;
/
–you can load plans [...]

Step by step test 11g adaptive cursor

DB version: 11.1.0.6
–
–Adaptive Cursors in 11g
–
–create a test table
T@ORCL11G>alter system flush shared_pool;
System altered.
T@ORCL11G>create table t2
as
select case when rownum = 1 then 1 else 99 end id, a.*
from dba_objects a
/
Table created.
T@ORCL11G>create index t2_id on t2(id);
Index created.
T@ORCL11G> begin
dbms_stats.gather_table_stats
( user, ‘T2′,
estimate_percent => 100,
method_opt=> ‘for all indexed columns’,cascade=>TRUE);
end;
/
PL/SQL procedure successfully completed.
–gather 100% is necessary as the data is skew
–bind [...]

Step by step test flashback data archives, a.k.a. Total Recall

–Flashback Data Archives, a.k.a. Total Recall
–  A Flashback Data Archive (Oracle Total Recall) provides the ability to track and store all transactional changes to a table over its lifetime.
–  Test version: 11.1.0.6
–create a system-wide default flashback archive on an existing tablespace.
conn / as sysdba
SYS@orcl11g>create flashback archive default fb tablespace example retention 1 year;
Flashback archive created.
–create [...]

Step by step test 11g new partition methods

DB version: 11.1.0.6
–new partition methods, ** are tested
**interval partition
**referencial partition
**virtual column partition
**system partition
*other new composite partitions
*Range-Range
*List-Range
*List-Hash
*List-List
*Interval-Range
*Interval-Hash
*Interval-List
*partition advisor, part of the SQL Access Advisor
*partition mode data pump, tables=[schema].[table_name].[partiton_name], PARTITION_OPTIONS = {NONE | DEPARTITION | MERGE}
–
–**interval partition, a kind of range partition
–
–use a sale table as example
create table sales
(prod_id        NUMBER,
cust_id        NUMBER,
time_id        DATE
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, ‘MONTH’)) STORE [...]

Step by step setup ASM using os files in 11g

Environment: Windows
DB version: 11.1.0.6
–Creating the ASM Instance Manually, using dbca is also ok
–create CSS service if it doesn’t exist in services
WINNT> <ORACLE_HOME>\bin\localconfig add
–create 2 1000MB os files as asmdisk
asmtool -create D:\asm\asmdisk1 1000
asmtool -create D:\asm\asmdisk2 1000
–if get an error msg, using commnd “net helpmsg errornumber” to know why
–create spfile
file: <ORACLE_HOME>\database\init+ASM.ora
INSTANCE_TYPE=ASM
DB_UNIQUE_NAME=+ASM
ASM_DISKSTRING=’D:\asm\*’
LARGE_POOL_SIZE=16M
_ASM_ALLOW_ONLY_RAW_DISKS= FALSE
–Note: The undocumented parameter _ASM_ALLOW_ONLY_RAW_DISKS is [...]