Oracle 11G SQL Plan Management

Oracle 11g New Features SQL plan management (SPM)

You might have noticed that execution plan changed in CBO , specially in following case

• Database Upgrade
• Database / Schema Stats collection
• Change in environment ( LinkUnix to Linux Migration )
• Change in data

Case: You want to upgrade database from 10g to 11g and this change can cause regressions in SQL performance,and fixing them manually can be difficult and time consuming. Sql tuning can be used but, this is a reactive mechanism and cannot guarantee stable performance when drastic changes happen to the system. SQL tuning can only resolve performance issues after they have occurred and are identified. For example, a SQL statement may become a high-load statement due to a plan change, but this cannot be resolved by SQL tuning until after the plan change occurs.

Solution: Oracle 11g new features SQL plan management (SPM) records and evaluates the execution plans of SQL Statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

Note:- Before upgrading database to 11g (11.1.0.6) , i have collect SQL Tuning set (STS) for 7 Day using "capture_cursor_cache_sqlset". By assuming that in 7 day all SQL stas will collected in STS. After that upgraded database to 11g ( from 10g 10.2.0.3) , i have used this STS to compare execution plan from 11g. Please check "SQL Performance Analyzer Part - 2"
for steps to transfer STS one database to other database

1) Capturing SQL Plan Baselines

· Automatic Plan Capture ( 11g Only )

· Manual Plan Capture ( 10g and 11g )

2) Make Changes Upgrade Database / collect stats / Migrate Database to Linux

3) Upload SQL Plan Baseline

4) Enable the use of SQL plan baselines

5) Evolving SQL Plan Baselines

1) Capturing SQL Plan Baselines in oracle 10g before upgrading to 11g 

a) Automatic Plan Capture(11g only)

When OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES set true , then database automatically creates and maintains the plan history for SQL statements using information provided by the optimizer.

>

In init.ora file OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true OR SQL>Alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true;

Note : - Above automatic plan capture only work on 11g , if you want to capture plan in 10g , then check section “Manual Plan Capture”

b) Manual Plan Capture ( 10g and 11g )

Following script will capture sql plan in SQL tuning set for 7 Day ( this script will run for 7 day , you can change time according to your need )

exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL'); exec dbms_sqltune.create_sqlset('STS_RAG_CMUL'); DECLARE sts_cmul VARCHAR2(30) := 'STS_RAG_CMUL'; BEGIN dbms_sqltune.capture_cursor_cache_sqlset(sts_cmul, 604800, 1, 'MERGE', dbms_sqltune.MODE_ACCUMULATE_STATS ); END;

capture_cursor_cache_sqlset : The procedure captures a workload from the cursor cache into a SQL tuning set, polling the cache multiple times over a time period and updating the workload data stored there. It can execute over as long a period as required to capture an entire system workload.

• time_limit : 604800 ( The total amount of time, in seconds, to execute , 7 day = 7 * 24 * 60 * 60 = 604800 )
• repeat_interval : 1 ( The amount of time, in seconds, to pause between sampling )
• capture_option During capture, either insert new statements, update existing ones, or both.
• 'INSERT'
• 'UPDATE',
• 'MERGE'
• capture_mode capture Option (UPDATE and MERGE capture options).
• MODE_REPLACE_OLD_STATS - Replace statistics when the number of executions
  seen is greater than that stored in the SQL tuning set

MODE_ACCUMULATE_STATS - Add new values to current values for SQL we already store. Note that this mode detects if a statement has been aged out, so the final value for a statistics will be the sum of the statistics of all cursors that statement existed under.

The CAPTURE_CURSOR_CACHE_SQLSET function enables the capture of the full system workload by repeatedly polling the cursor cache over a specified interval. This function is a lot more efficient than repeatedly using the SELECT_CURSOR_CACHE and LOAD_SQLSET procedures to capture the cursor cache over an extended period of time. This function effectively captures the entire workload, as SQL Profiles opposed to the AWR—which only captures the workload of high-load SQL statements or the LOAD_SQLSET procedure, which accesses the data source only once.

2) Make Changes e.g. Upgrade Database / collect stats / Migrate Database to Linux Collect stats

exec dbms_stats.set_param('ESTIMATE_PERCENT','100'); exec dbms_stats.set_param('method_opt','FOR ALL COLUMNS SIZE 254'); exec dbms_stats.Gather_Database_Stats;

3) Upload SQL Plan Baseline 
If you have collected stats manually in SQL tuning sets ( STS) , then you need to
upload baseline from STS

-- Upload plan manually using dbms_spm -- variable pls number; exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name=>'STS_RAG_CMUL',- basic_filter=>'parsing_schema_name like ''APPS'' and plan_hash_value!=0', - fixed=>'NO',commit_rows=>1000);

4) Enable the use of SQL plan baselines

To enable the use of SQL plan baselines, set the OPTIMIZER_USE_SQL_PLAN_BASELINES
initialization parameter to TRUE. By default, this parameter is set to TRUE.

--In init.ora file -- OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE OR SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES= true;

5) Evolving SQL Plan Baselines

I have captured 10g plans in STS and after upgrading database to 11g uploaded those plans ,using dbms_spm.load_plans_from_sqlset. After uploading plans manually , I have set

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true.

--Check not accepted Plans -- SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines Where ACCEPTED='NO' and PARSING_SCHEMA_NAME like 'APPS'; SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED --------------------- ------------------------- ------- --------- ------- SYS_SQL_9295397103ae5ebe SYS_SQL_PLAN_74720f16b0fcefa7 YES NO NO SYS_SQL_9295397103ad3eba SYS_SQL_PLAN_03ad3ebaa086802f YES NO NO SYS_SQL_434ef30d9da6a29b SYS_SQL_PLAN_9da6a29b1f6e321d YES NO NO SYS_SQL_13836d6b3da62bbb SYS_SQL_PLAN_3da62bbbe5990995 YES NO NO .......................................... .................................... 40 rows selected.

-- Displaying SQL Plan Baselines -- SQL> select * fromtable(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_13836d6b3da62bbb',format=>'basic')); -------------------------------------------------------------------------------- SQL handle: SYS_SQL_13836d6b3da62bbb SQL text: ( I have removed SQL text and changed table name in below execution plan) -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_3da62bbb91266099 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD -------------------------------------------------------------------------------- Plan hash value: 2877140902 ------------------------------------------------- Id Operation Name  ------------------------------------------------- 0 SELECT STATEMENT  1 SORT ORDER BY  2 HASH JOIN SEMI  3 TABLE ACCESS FULL PROD  4 INDEX FULL SCAN COMP_PROD ------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_3da62bbbe5990995 Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- Plan hash value: 3245492848 ----------------------------------------- Id Operation Name  ----------------------------------------- 0 SELECT STATEMENT  1 SORT ORDER BY  2 HASH JOIN SEMI  3 TABLE ACCESS FULL PROD  4 TABLE ACCESS FULL COMP ----------------------------------------- 42 rows selected. SQL>

Evolving Plans with pls/sql function DBMS_SPM.EVOLVE_SQL_PLAN_BASELIN

--Evolving Plans With DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE -- SQL> DECLARE report clob; BEGIN report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE( sql_handle => 'SYS_SQL_13836d6b3da62bbb'); DBMS_OUTPUT.PUT_LINE(report); END; / ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- SQL_HANDLE =SYS_SQL_13836d6b3da62bbb PLAN_NAME = TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT = YES Plan: SYS_SQL_PLAN_3da62bbbe5990995 ----------------------------------- Plan was verified: Time used .06 seconds. Failed performance criterion: Compound improvement ratio <= .3. Baseline Plan Test Plan Improv. Ratio ------------- --------- ------------- Execution Status: COMPLETE COMPLETE Rows Processed: 1460 1460 Elapsed Time(ms): 10 10 1 CPU Time(ms): 9 9 1 Buffer Gets: 84 286 .29 Disk Reads: 0 0 Direct Writes: 0 0 Fetches: 0 0 Executions: 1 1 ------------------------------------------------------------------------------- Report Summary ------------------------------------------------------------------------------- Number of SQL plan baselines verified: 1. Number of SQL plan baselines evolved: 0. PL/SQL procedure successfully completed. SQL>

JUST FOR DEMO PURPOSE , LETS ACCEPT PLAN 2 ('SYS_SQL_PLAN_3DA62BBBE5990995')

-- Acccept plan 2 ('SYS_SQL_PLAN_3da62bbbe5990995') and Fixed it -- variable cnt number; exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_13836d6b3da62bbb', - plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', - attribute_name => 'ACCEPTED', attribute_value => 'YES'); SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_13836d6b3da62bbb', - plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', - attribute_name => 'ACCEPTED', attribute_value => 'YES'); PL/SQL procedure successfully completed. SQL> -- Same pl/sql function used to fix plans  -- Optimizer always picked FIXED plans SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_13836d6b3da62bbb', - plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', - attribute_name => 'FIXED', attribute_value => 'YES'); PL/SQL procedure successfully completed. SQL> - - Check status of plans-- SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines 2 Where FIXED='YES' and PARSING_SCHEMA_NAME like ''; SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED ----------------------------- ---------------------------- -------------- --------  SYS_SQL_13836d6b3da62bbb SYS_SQL_PLAN_3da62bbbe5990995 YES YES YES SQL> >

>

-- Displaying SQL Plan Baselines after changes  -- SQL> select * fromtable(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_13836d6b3da62bbb',format=>'basic')); -------------------------------------------------------------------------------- SQL handle: SYS_SQL_13836d6b3da62bbb SQL text: ( I have removed SQL text and changed table name in below execution plan) -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_3da62bbb91266099 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD -------------------------------------------------------------------------------- Plan hash value: 2877140902 ------------------------------------------------- Id Operation Name  ------------------------------------------------- 0 SELECT STATEMENT  1 SORT ORDER BY  2 HASH JOIN SEMI  3 TABLE ACCESS FULL PROD  4 INDEX FULL SCAN COMP_PROD ------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_3da62bbbe5990995 Enabled: YES Fixed: YES Accepted: YES Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- Plan hash value: 3245492848 ----------------------------------------- Id Operation Name  ----------------------------------------- 0 SELECT STATEMENT  1 SORT ORDER BY  2 HASH JOIN SEMI  3 TABLE ACCESS FULL PROD  4 TABLE ACCESS FULL COMP ----------------------------------------- 42 rows selected.

Oracle streams

ORACLE STREAMS

 
2 databases
1 src as source database
1 dst as destination database

replication type 1 way of the entire schema STREAMSCHEMA
Step 1. Set all databases in archivelog mode.

Step 2. Change initialization parameters for Streams. The Streams pool size and NLS_DATE_FORMAT require a restart of the instance.

SQL> alter system set global_names=true scope=both;
SQL> alter system set undo_retention=3600 scope=both;
SQL> alter system set job_queue_processes=4 scope=both;
SQL> alter system set streams_pool_size= 20m scope=spfile;
SQL> alter system set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' scope=spfile;
SQL> shutdown immediate;
SQL> startup
Step 3. Create Streams administrators on the src and dst databases, and grant required roles and privileges. Create default tablespaces so that they are not using SYSTEM.

---at the src
SQL> create tablespace streamsdm datafile '/u01/product/oracle/oradata/orcl/strepadm01.dbf' size 100m;

---at the replica:
SQL> create tablespace streamsdm datafile '/u02/oracle/oradata/str10/strepadm01.dbf' size 100m;
---at both sites:
SQL> create user streams_adm
identified by streams_adm
default tablespace strepadm01
temporary tablespace temp;

SQL> grant connect, resource, dba, aq_administrator_role to streams_adm;
SQL> BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE (
grantee => 'streams_adm', grant_privileges => true);
END;
/
Step 4. Configure the tnsnames.ora at each site so that a connection can be made to the other database.
Step 5. With the tnsnames.ora squared away, create a database link for the streams_adm user at both SRC and DST. With the init parameter global_name set to True, the db_link name must be the same as the global_name of the database you are connecting to. Use a SELECT from the table global_name at each site to determine the global name.

SQL> select * from global_name;
SQL> connect streams_adm/streams_adm@SRC
SQL> create database link DST
connect to streams_adm identified by streams_adm using 'DST';
SQL> select sysdate from dual@DST;
SLQ> connect streams_adm/streams_adm@DST
SQL> create database link SRC connect to stream_admin identified by streams_adm using 'SRC';
SQL> select sysdate from dual@SRC;

Step 6. Control what schema shall be replicated

STREAMSCHEMA is the schema to be replicated
Step 7. Add supplemental logging to the STREAMSCHEMA schema on all the
tables?

SQL> Alter table STREAMSCHEMA.tb1 add supplemental log data (ALL) columns;
SQL> alter table STREAMSCHEMA.tb2 add supplemental log data (ALL) columns;

etc...
Step 8. Create Streams queues at the primary and replica database.

---at SRC (primary):

SQL> connect stream_admin/stream_admin@ORCL
SQL> BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'streams_adm.STREAMSCHEMA_src_queue_table',
queue_name => 'streams_adm.STREAMSCHEMA_src__queue');
END;
/
---At DST (replica):
SQL> connect stream_admin/stream_admin@STR10
SQL> BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'stream_admin.STREAMSCHEMA_dst_queue_table',
queue_name => 'stream_admin.STREAMSCHEMA_dst_queue');
END;
/
Step 9. Create the capture process on the source database (SRC).

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name =>'STREAMSCHEMA',
streams_type =>'capture',
streams_name =>'STREAMSCHEMA_src_capture',
queue_name =>'STREAMSCHEMA_src_queue',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database => NULL,
inclusion_rule => true);
END;
/
Step 10. Instantiate the STREAMSCHEMA schema at DST. by doing export
import : Can I use now datapump to do that ?

---AT SRC:
exp system/superman file=STREAMSCHEMA.dmp log=STREAMSCHEMA.log
object_consistent=y owner=STREAMSCHEMA

---AT DST:
---Create STREAMSCHEMA tablespaces and user:
create tablespace STREAMSCHEMA_datafile
'/u02/oracle/oradata/str10/STREAMSCHEMA_01.dbf' size 100G;
create tablespace ws_app_idx datafile
'/u02/oracle/oradata/str10/STREAMSCHEMA_01.dbf' size 100G;
create user STREAMSCHEMA identified by STREAMSCHEMA_
default tablespace STREAMSCHEMA_
temporary tablespace temp;
grant connect, resource to STREAMSCHEMA;

imp system/123db file=STREAMSCHEMA_.dmp log=STREAMSCHEMA.log fromuser=STREAMSCHEMA  touser=STREAMSCHEMA streams_instantiation=y
Step 11. Create a propagation job at the source database (SRC).

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name =>'STREAMSCHEMA',
streams_name =>'STREAMSCHEMA_src_propagation',
source_queue_name =>'stream_admin.STREAMSCHEMA_src_queue',
destination_queue_name=>'stream_admin.STREAMSCHEMA_dst_queue@dst',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database =>'SRC',
inclusion_rule =>true);
END;
/

Step 12. Create an apply process at the destination database (DST).

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name =>'STREAMSCHEMA',
streams_type =>'apply',
streams_name =>'STREAMSCHEMA_Dst_apply',
queue_name =>'STREAMSCHEMA_dst_queue',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database =>'SRC',
inclusion_rule =>true);
END;
/
Step 13. Create substitution key columns for äll the tables that haven't a primary key of the STREAMSCHEMA schema on DST The column combination must provide a unique value for Streams.

SQL> BEGIN
DBMS_APPLY_ADM.SET_KEY_COLUMNS(
object_name =>'STREAMSCHEMA.tb2',
column_list =>'id1,names,toys,vendor');
END;
/

Step 14. Configure conflict resolution at the replication db (DST).

DECLARE
cols DBMS_UTILITY.NAME_ARRAY;
BEGIN
cols(1) := 'id';
cols(2) := 'names';
cols(3) := 'toys';
cols(4) := 'vendor';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name =>'STREAMSCHEMA.tb2',
method_name =>'OVERWRITE',
resolution_column=>'STREAMSCHEMA',
column_list =>cols);
END;
/
Step 15. Enable the capture process on the source database (SRC).

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STREAMSCHEMA_src_capture');
END;
/

Step 16. Enable the apply process on the replication database (DST).

BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'STREAMSCHEMA_DST_apply');
END;
/
Step 17. Test streams propagation of rows from source (src) to replication (DST).

AT ORCL:

insert into STREAMSCHEMA.tb2 values (
31000, 'BAMSE', 'DR', ‘SILICON’);

AT STR10:

connect STREAMSCHEMA/STREAMSCHEMA
select * from STREAMSCHEMA.tb2 where vendor= ‘SILICON’;

Oracle streams

ORACLE STREAMS

 
2 databases
1 src as source database
1 dst as destination database

replication type 1 way of the entire schema STREAMSCHEMA
Step 1. Set all databases in archivelog mode.

Step 2. Change initialization parameters for Streams. The Streams pool size and NLS_DATE_FORMAT require a restart of the instance.

SQL> alter system set global_names=true scope=both;
SQL> alter system set undo_retention=3600 scope=both;
SQL> alter system set job_queue_processes=4 scope=both;
SQL> alter system set streams_pool_size= 20m scope=spfile;
SQL> alter system set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' scope=spfile;
SQL> shutdown immediate;
SQL> startup
Step 3. Create Streams administrators on the src and dst databases, and grant required roles and privileges. Create default tablespaces so that they are not using SYSTEM.

---at the src
SQL> create tablespace streamsdm datafile '/u01/product/oracle/oradata/orcl/strepadm01.dbf' size 100m;

---at the replica:
SQL> create tablespace streamsdm datafile '/u02/oracle/oradata/str10/strepadm01.dbf' size 100m;
---at both sites:
SQL> create user streams_adm
identified by streams_adm
default tablespace strepadm01
temporary tablespace temp;

SQL> grant connect, resource, dba, aq_administrator_role to streams_adm;
SQL> BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE (
grantee => 'streams_adm', grant_privileges => true);
END;
/
Step 4. Configure the tnsnames.ora at each site so that a connection can be made to the other database.
Step 5. With the tnsnames.ora squared away, create a database link for the streams_adm user at both SRC and DST. With the init parameter global_name set to True, the db_link name must be the same as the global_name of the database you are connecting to. Use a SELECT from the table global_name at each site to determine the global name.

SQL> select * from global_name;
SQL> connect streams_adm/streams_adm@SRC
SQL> create database link DST
connect to streams_adm identified by streams_adm using 'DST';
SQL> select sysdate from dual@DST;
SLQ> connect streams_adm/streams_adm@DST
SQL> create database link SRC connect to stream_admin identified by streams_adm using 'SRC';
SQL> select sysdate from dual@SRC;

Step 6. Control what schema shall be replicated

STREAMSCHEMA is the schema to be replicated
Step 7. Add supplemental logging to the STREAMSCHEMA schema on all the
tables?

SQL> Alter table STREAMSCHEMA.tb1 add supplemental log data (ALL) columns;
SQL> alter table STREAMSCHEMA.tb2 add supplemental log data (ALL) columns;

etc...
Step 8. Create Streams queues at the primary and replica database.

---at SRC (primary):

SQL> connect stream_admin/stream_admin@ORCL
SQL> BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'streams_adm.STREAMSCHEMA_src_queue_table',
queue_name => 'streams_adm.STREAMSCHEMA_src__queue');
END;
/
---At DST (replica):
SQL> connect stream_admin/stream_admin@STR10
SQL> BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'stream_admin.STREAMSCHEMA_dst_queue_table',
queue_name => 'stream_admin.STREAMSCHEMA_dst_queue');
END;
/
Step 9. Create the capture process on the source database (SRC).

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name =>'STREAMSCHEMA',
streams_type =>'capture',
streams_name =>'STREAMSCHEMA_src_capture',
queue_name =>'STREAMSCHEMA_src_queue',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database => NULL,
inclusion_rule => true);
END;
/
Step 10. Instantiate the STREAMSCHEMA schema at DST. by doing export
import : Can I use now datapump to do that ?

---AT SRC:
exp system/superman file=STREAMSCHEMA.dmp log=STREAMSCHEMA.log
object_consistent=y owner=STREAMSCHEMA

---AT DST:
---Create STREAMSCHEMA tablespaces and user:
create tablespace STREAMSCHEMA_datafile
'/u02/oracle/oradata/str10/STREAMSCHEMA_01.dbf' size 100G;
create tablespace ws_app_idx datafile
'/u02/oracle/oradata/str10/STREAMSCHEMA_01.dbf' size 100G;
create user STREAMSCHEMA identified by STREAMSCHEMA_
default tablespace STREAMSCHEMA_
temporary tablespace temp;
grant connect, resource to STREAMSCHEMA;

imp system/123db file=STREAMSCHEMA_.dmp log=STREAMSCHEMA.log fromuser=STREAMSCHEMA  touser=STREAMSCHEMA streams_instantiation=y
Step 11. Create a propagation job at the source database (SRC).

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name =>'STREAMSCHEMA',
streams_name =>'STREAMSCHEMA_src_propagation',
source_queue_name =>'stream_admin.STREAMSCHEMA_src_queue',
destination_queue_name=>'stream_admin.STREAMSCHEMA_dst_queue@dst',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database =>'SRC',
inclusion_rule =>true);
END;
/

Step 12. Create an apply process at the destination database (DST).

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name =>'STREAMSCHEMA',
streams_type =>'apply',
streams_name =>'STREAMSCHEMA_Dst_apply',
queue_name =>'STREAMSCHEMA_dst_queue',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database =>'SRC',
inclusion_rule =>true);
END;
/
Step 13. Create substitution key columns for äll the tables that haven't a primary key of the STREAMSCHEMA schema on DST The column combination must provide a unique value for Streams.

SQL> BEGIN
DBMS_APPLY_ADM.SET_KEY_COLUMNS(
object_name =>'STREAMSCHEMA.tb2',
column_list =>'id1,names,toys,vendor');
END;
/

Step 14. Configure conflict resolution at the replication db (DST).

DECLARE
cols DBMS_UTILITY.NAME_ARRAY;
BEGIN
cols(1) := 'id';
cols(2) := 'names';
cols(3) := 'toys';
cols(4) := 'vendor';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name =>'STREAMSCHEMA.tb2',
method_name =>'OVERWRITE',
resolution_column=>'STREAMSCHEMA',
column_list =>cols);
END;
/
Step 15. Enable the capture process on the source database (SRC).

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STREAMSCHEMA_src_capture');
END;
/

Step 16. Enable the apply process on the replication database (DST).

BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'STREAMSCHEMA_DST_apply');
END;
/
Step 17. Test streams propagation of rows from source (src) to replication (DST).

AT ORCL:

insert into STREAMSCHEMA.tb2 values (
31000, 'BAMSE', 'DR', ‘SILICON’);

AT STR10:

connect STREAMSCHEMA/STREAMSCHEMA
select * from STREAMSCHEMA.tb2 where vendor= ‘SILICON’;

AdLeaf

Advertisement:

www.veerabiopower.com

Ultimate choice for Green Energy

Adleaf

Advertisement : 

www.veerabiopower.com

Ultimate choice for Green energy

Oracle DBA

Advertising

Oracle session level, Managing tablespace, Managing

Oracle session level, Managing tablespace, Managing

Datafiles, Logical and physical structure


Session Level

Long Operations:

select sid,username usern,serial#,opname,target,sofar,totalwork tot_work,units,time_remaining remaning,elapsed_seconds lapsed,last_update_time last_time from v&session_longops where sid=73 order by last_update_time desc;

All active sessions
Select * from v&session where status='ACTIVE'and sid in (Sid, Serial #) order by sid;

Find session's sid or process id by it's sid or process id

Select sid, a.serial#, spid, a.username, status, taddr, a.programfrom v&session a, v&process b where a.paddr=b.addr and a.username is not nulland (sid=163 or spid=28179) order by status, sid;

Kill Session

Alter system kill session '&sid,&serial';

Log Management

Status of Logfile Groups

Select * from v&log;

Status of Logfiles

Select * from v&logfile order by group#;

status of the archiver

Select * from v&instance;

Restart the archiver

Alter system archive log start;



Switch online log

Alter system switch logfile;

Alter system set log_archive_max_processes=4;


Add logfile group

Alter database add logfile group 4 ('&logfilename1','&logfilename2') size 64M;

Drop logfile group and all members in it

Alter database drop logfile group &N;

Add logfile member

Alter database add logfile member '&logfilename' reuse to group 4;

Drop logfile member

Alter database drop logfile member '&logfilename';

Checking archivelog mode
Select dbid, name, resetlogs_time, log_mode from v&database;

Archiver destinations

Select * from v&archive_dest;

Altering Archive destination

Alter system set log_archive_dest_1='location=&path';
Alter system set log_archive_dest_state_1='enable';

Archived log info from the control file

Select * from v&archived_log;

The sequence# of last backed up log

Select thread#, max (sequence#) from v&archived_log where BACKUP_COUNT>0 group by thread#;

Redo size (MB) per day, last 30 days
Select trunc (first_time) arc_date, sum (blocks * block_size)/1048576 arc_sizefrom v&archived_log where first_time >= (trunc (sysdate)-30)group by trunc(first_time);

MANAGING TABLESPACES

A tablespace is a logical storage unit. Why we are say logical because a tablespace is not visible in the file system. Oracle store data physically is datafiles. A tablespace consist of one or more datafile.
Types of tablespace?
System Tablespace:• Created with the database • Required in all database • Contain the data dictionary
Non System Tablespace: • Separate undo, temporary, application data and application index segments Control the amount of space allocation to the user’s objects • Enable more flexibility in database administration
How to Create Tablespace?
CREATE TABLESPACE "tablespace name"DATAFILE clause SIZE ……. REUSEMENIMUM EXTENT (This ensure that every used extent size in the tablespace is a multiple of the integer)BLOCKSIZELOGGING NOLOGGING (Logging: By default tablespace have all changes written to redo, Nologging : tablespace do not have all changes written to redo)ONLINE OFFLINE (OFFLINE: tablespace unavailable immediately after creation)PERMANENT TEMPORARY (Permanent: tablespace can used to hold permanent object, temporary: tablespace can used to hold temp object)EXTENT MANAGEMENT clauseExample: CREATE TABLESPACE "USER1"DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10m REUSEBLOCKSIZE 8192
LOGGING
ONLINEPERMANENTEXTENT MANAGEMENT LOCAL

How to manage space in Tablespace?

Tablespace allocate space in extent.






Locally managed tablespace:

The extents are managed with in the tablespace via bitmaps. In locally managed tablespace, all tablespace information store in datafile header and don’t use data dictionary table for store information. Advantage of locally managed tablespace is that no DML generate and reduce contention on data dictionary tables and no undo generated when space allocation or deallocation occurs.

Extent Management [Local Dictionary]
The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid for segments stored in locally managed tablespaces.
To create a locally managed tablespace, you specify LOCAL in the extent management clause of the CREATE TABLESPACE statement. You then have two options. You can have Oracle manage extents for you automatically with the AUTOALLOCATE option, or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE).
If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice.If you do not specify either AUTOALLOCATE or UNIFORM with the LOCAL parameter, then AUTOALLOCATE is the default.

Dictionary Managed tablespace
When we declaring a tablespace as a Dictionary Managed, the data dictionary manages the extents. The Oracle server updates the appropriate tables (sys.fet$ and sys.uet$) in the data dictionary whenever an extent is allocated or deallocated.
How to Create a Locally Managed Tablespace?
The following statement creates a locally managed tablespace named USERS, where AUTOALLOCATE causes Oracle to automatically manage extent size.CREATE TABLESPACE usersDATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50MEXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Alternatively, this tablespace could be created specifying the UNIFORM clause. In this example, a 512K extent size is specified. Each 512K extent (which is equivalent to 64 Oracle blocks of 8K) is represented by a bit in the bitmap for this file.
CREATE TABLESPACE usersDATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;How to Create a Dictionary Managed Tablespace?The following is an example of creating a DICTIONARY managed tablespace in Oracle9i:CREATE TABLESPACE usersDATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50MEXTENT MANAGEMENT DICTIONARYDEFAULT STORAGE (INITIAL 64KNEXT 64KMINEXTENTS 2MAXEXTENTS 121PCTINCREASE 0);
What is Segment Space Management Options?
Two choices for segment-space management, one is manual (the default) and another auto.
Manual: This is default option. This option use free lists for managing free space within segments. What are free lists: Free lists are lists of data blocks that have space available for inserting new rows.
Auto: This option use bitmaps for managing free space within segments. This is typically called automatic segment-space management
Example:CREATE TABLESPACE usersDATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10M REUSEEXTENT MANAGEMENT LOCAL UNIFORM SIZE 512KSEGMENT SPACE MANAGEMENT AUTOPERMANENTONLINE;
How to Convert between LMT and DMT Tablespace?
The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert
between LMT and DMT mode. Look at these examples:
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');PL/SQL procedure successfully completed.
SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');PL/SQL procedure successfully completed

Important Queries Related to Tablespace

How to retrieve tablespace default storage Parameters?
SELECT TABLESPACE_NAME "TABLESPACE",INITIAL_EXTENT "INITIAL_EXT",NEXT_EXTENT "NEXT_EXT",MIN_EXTENTS "MIN_EXT",MAX_EXTENTS "MAX_EXT",PCT_INCREASE FROM DBA_TABLESPACES;

How to retrieve information tablespace and associated datafile?
SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME FROM DBA_DATA_FILES;
How to retrieve Statistics for Free Space (Extents) of Each Tablespace?

SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,COUNT(*) "PIECES",MAX(blocks) "MAXIMUM",MIN(blocks) "MINIMUM",AVG(blocks) "AVERAGE",SUM(blocks) "TOTAL" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME, FILE_ID;

How to drop a datafile from a Tablespace?Important:
Oracle does not provide an interface for dropping datafiles in the same way you would drop a schema object such as a table or a user.Reasons why you want to remove a datafile from a tablespace:• You may have mistakenly added a file to a tablespace. • You may have made the file much larger than intended and now want to remove it. • You may be involved in a recovery scenario and the database won't start because a datafile is missing.

Once the DBA creates a datafile for a tablespace, the datafile cannot be removed. If you want to do any critical operation like dropping datafiles, ensure you have a full backup of the database.
Step: 1

Determining how many datafiles make up a tablespace to determine how many and which datafiles make up a tablespace, you can use the following query:
SELECT file_name, tablespace_name FROM dba_data_files WHEREtablespace_name ='';

Case 1
If you have only one datafile in the tablespace and you want to remove it. You can simply drop the entire tablespace using the following:
DROP TABLESPACE INCLUDING CONTENTS;
The above command will remove the tablespace, the datafile, and the tablespace's contents from the data dictionary.Important: Oracle will not drop the physical datafile after the DROP TABLESPACE command. This action needs to be performed at the operating system.
Case 2
If you have more than one datafile in the tablespace, and you want to remove all datafiles and also no need the information contained in that tablespace, then use the same command as above:
DROP TABLESPACE INCLUDING CONTENTS;
Case 3
If you have more than one datafile in the tablespace and you want to remove only one or two (not all) datafile in the tablespace or you want to keep the objects that reside in the other datafile(s) which are part of this tablespace, then you must export all the objects inside the tablespace.
Step: 1 Gather information on the current datafiles within the tablespace by running the following query in SQL*Plus:SELECT file_name, tablespace_name FROM dba_data_files WHEREtablespace_name ='';
Step: 2 you now need to identify which objects are inside the tablespace for the purpose of running an export. To do this, run the following query:SELECT owner, segment_name, segment_type FROM dba_segments WHERE tablespace_name=''
Step: 3 now, export all the objects that you wish to keep.
Step: 4 Once the export is done, issue the

DROP TABLESPACE INCLUDING CONTENTS.
Step: 5 delete the datafiles belonging to this tablespace using the operating system.

Step: 6 Recreate the tablespace with the datafile(s) desired, and then import the objects into that tablespace.
Case: 4
If you do not want to follow any of these procedures, there are other things that can be done besides dropping the tablespace.
• If the reason you wanted to drop the file is because you mistakenly created the file of the wrong size, then consider using the RESIZE command.
• If you really added the datafile by mistake, and Oracle has not yet allocated any space within this datafile, then you can use ALTER DATABASE DATAFILE RESIZE;
Command to make the file smaller than 5 Oracle blocks. If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be rebuilt to exclude the incorrect datafile.
Important: The ALTER DATABASE DATAFILE OFFLINE DROP command is not meant to allow you to remove a datafile. What the command really means is that you are off lining the datafile with the intention of dropping the tablespace.

If you are running in archivelog mode, you can also use:

ALTER DATABASE DATAFILE OFFLINE;

Instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup.

(This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile.

MANAGING DATAFILES

What is datafile?

Datafiles are physical files of the OS that store the data of all logical structures in the database. Datafile must be created for each tablespace.
How to determine the number of datafiles?
At least one datafile is required for the SYSTEM tablespace. We can create separate datafile for other tablespace. When we create DATABASE, MAXDATAFILES may be or not specify in create database statement clause. Oracle assassin db_files default value to 200. We can also specify the number of datafiles in init file.
When we start the oracle instance, the DB_FILES initialization parameter reserve for datafile information and the maximum number of datafile in SGA. We can change the value of DB_FILES (by changing the initialization parameter setting), but the new value does not take effect until you shut down and restart the instance. Important: If the value of DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit. Example: if init parameter db_files set to 2 then you can not add more then 2 in your database.
If the value of DB_FILES is too high, memory is unnecessarily consumed. When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size. However, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

Note:
If you add new datafiles to a tablespace and do not fully specify the filenames, the database creates the datafiles in the default database directory. Oracle recommends you always specify a fully qualified name for a datafile. Unless you want to reuse existing files, make sure the new filenames do not conflict with other files. Old files that have been previously dropped will be overwritten.
How to add datafile in existing tablespace?
Alter tablespace add datafile ‘/............../......./file01.dbf’ size 10m autoextend on; How to resize the datafile? Alter database datafile '/............../......./file01.dbf' resize 100M; How to bring datafile online and offline?Alter database datafile '/............../......./file01.dbf' online;alter database datafile '/............../......./file01.dbf' offline; How to renaming the datafile in a single tablesapce?Step: 1 Take the tablespace that contains the datafiles offline. The database must be open. Alter tablespace offline normal; Step: 2 Rename the datafiles using the operating system. Step: 3 Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

Alter tablespace rename datafile '/...../..../..../user.dbf' to '/..../..../.../users1.dbf';
Step 4: Back up the database. After making any structural changes to a database, always perform an immediate and complete backup. How to relocate datafile in a single tablesapce?Step: 1 Use following query to know the specific file name or size.
Select file_name, bytes from dba_data_files where tablespace_name=''; Step: 2 Take the tablespace containing the datafiles offline: alter tablespace offline normal;
Step: 3 Copy the datafiles to their new locations and rename them using the operating system. Step: 4 Rename the datafiles within the database.
ALTER TABLESPACE RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf' TO '/u03/oracle/rbdb1/users01.dbf','/u04/oracle/rbdb1/users02.dbf';
Step:5 Back up the database. After making any structural changes to a database, always perform an immediate and complete backup. How to Renaming and Relocating Datafiles in Multiple Tablespaces?

Step: 1 Ensure that the database is mounted but closed. Step: 2 Copy the datafiles to be renamed to their new locations and new names, using the operating system. Step: 3 Use ALTER DATABASE to rename the file pointers in the database control file. ALTER DATABASE RENAME FILE
'/u02/oracle/rbdb1/sort01.dbf','/u02/oracle/rbdb1/user3.dbf'TO '/u02/oracle/rbdb1/temp01.dbf',
'/u02/oracle/rbdb1/users03.dbf;

Step: 4 Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.


Overview of Logical Structure of a Database

This section discusses logical storage structures: data blocks, extents, segments, and tablespaces. These logical storage structures enable Oracle Database to have fine-grained control of disk space use.This section includes the following topic

Oracle Database Data Blocks Extents Segments Tablespaces

Oracle Database Data BlocksAt the finest level of granularity, Oracle Database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify up to four other block sizes. A database uses and allocates free database space in Oracle Database data blocks.
ExtentsThe next level of logical database space is an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.
SegmentsAbove extents, the level of logical database storage is a segment. A segment is a set of extents allocated for a table, index, rollback segment, or for temporary use by a session, transaction, or SQL parser. In relation to physical database structures, all extents belonging to a segment exist in the same tablespace, but they may be in different data files.When the extents of a segment are full, Oracle Database dynamically allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.

TablespacesA database is divided into logical storage units called tablespaces, which group related data blocks, extents, and segments. For example, tablespaces commonly group together all application objects to simplify some administrative operations.
Each database is logically divided into two or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

Every Oracle database contains a SYSTEM tablespace and a SYSAUX tablespace. Oracle Database creates them automatically when the database is created. The system default is to create a small file tablespace, which is the traditional type of Oracle tablespace. The SYSTEM and SYSAUX tablespaces are created as small file tablespaces.
Oracle Database also lets you create bigfile tablespaces, which are made up of single large file rather than numerous smaller ones. Bigfile tablespaces let Oracle Database utilize the ability of 64-bit systems to create and manage ultralarge files. As a result, Oracle Database can scale up to 8 exabytes in size. With Oracle-Managed Files, bigfile tablespaces make datafiles completely transparent for users. In other words, you can perform operations on tablespaces, rather than the underlying datafile.

Overview of Physical Structure of a Database

The following sections explain the physical database structures of an Oracle database, including datafiles, control files, redo log files, archive log files, parameter files, alert and trace log files, and backup files.This section includes the following topics

Datafiles
Control Files Online Redo Log Files Archived Redo Log Files Parameter Files Alert and Trace Log Files Backup Files
Datafiles
Every Oracle database has one or more physical datafiles, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.

Datafiles have the characteristics:A datafile can be associated with only one database. Datafiles can be defined to extend automatically when they are full. One or more datafiles form a logical unit of database storage called a tablespace.
Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle Database. For example, if a user wants to access
some data in a table of a database, and if the requested information is not already in the memory cache for the database, then it is read from the appropriate datafiles and stored in memory.
Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and to increase performance, data is pooled in memory and written to the appropriate datafiles all at once, as determined by the background process database writer process (DBWn).
Datafiles that are stored in temporary tablespaces are called tempfiles. Tempfiles are subject to some restrictions.Control FilesEvery Oracle database has a control file. A control file contains entries that specify the physical structure of the database, including the following information:Database name

Names and locations of datafiles and redo log files Timestamp of database creation Oracle Database can multiplex the control file, that is, simultaneously maintain a number of identical control file copies, to protect against a failure involving the control file.
Every time an instance of an Oracle database is started, its control file identifies the datafiles, tempfiles, and redo log files that must be opened for database operation to proceed. If the physical makeup of the database is altered (for example, if a new datafile or redo log file is created), then the control file is automatically modified by Oracle Database to reflect the change. A control file is also used in database recovery.
Online Redo Log File

Every Oracle Database has a set of two or more online redo log files. These online redo log files, together with archived copies of redo log files, are collectively known as the redo log for the database. A redo log is made up of redo entries (also called redo records), which record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.

To protect against a failure involving the redo log itself, Oracle Database lets you create a multiplexed redo log so that two or more copies of the redo log can be maintained on different disks.

Archived Redo Log Fileswhen online redo log files are written to disk, they become archived redo log files. Oracle recommends that you enable automatic archiving of the redo log. Oracle Database automatically archives redo log files when the database is in ARCHIVELOG mode.

Parameter FilesParameter files contains a list of configuration parameters for that instance and database. Both parameter files (pfiles) and server parameter files (spfiles) let you store and manage your initialization parameters persistently in a server-side disk file. A server parameter file has these additional advantages:The file is concurrently updated when some parameter values are changed in the active instance. The file is centrally located for access by all instance in a Real Application Services database. Oracle recommends that you create a server parameter file as a dynamic means
maintaining initialization parameters.
Alert and Trace Log FilesEach server and background process can write to an associated trace file. When an internal error is detected by a process, the process dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, while other information is for Oracle Support Services. Trace file information is also used to tune applications and instances. The alert file, or alert log, is a special trace file. The alert log of a database is a chronological log of messages and errors.The following features provide automation and assistance in the collection and interpretation of trace and alert file information:

The Automatic Diagnostic Repository (ADR) is a system-managed repository for storing and organizing trace files and other error diagnostic data. ADR provides a comprehensive view of all the critical errors encountered by the database and maintains all relevant data needed for problem diagnosis and eventual resolution. When the same type of incident occurs too frequently, ADR performs flood control to avoid excessive dumping of diagnostic information.
The Incident Packaging Service (IPS) extracts diagnostic and test case data associated with critical errors from the ADR and packages the data for transport to Oracle.
Backup FilesTo restore a file is to replace it with a backup file. Typically, you restore a file when a media failure or user error has damaged or deleted the original file.User-managed backup and recovery requires you to actually restore backup files before you can perform a trial recovery of the backups.

Server-managed backup and recovery manages the backup process, such as scheduling of backups, as well as the recovery process, such as applying the correct backup file when recovery is needed.