Automatic Maintaining SQL Tuning Advisor.

Automatic Maintaining SQL Tuning Advisor.

      It is always enabled by default, but the SQL Profiles are enabled unless that is ACCEPT_SQL_PROFILES modify the parameter.

- We can see the report and change the parameter through ACCEPT_SQL_PROFILES DBMS_AUTO_SQLTUNE package

- Reviewed the report

VARIABLE my_rept CLOB;

BEGIN

  :my_rept :=DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(

    begin_exec   => NULL,

    end_exec     => NULL,

    type         => 'TEXT',

    level        => 'TYPICAL',

    section      => 'ALL',

    object_id    => NULL,

    result_limit => NULL);

END;

/

PRINT :my_rept

- Enable  SQL Profiles automatically

- Before implementation in production you have to insure about Pro.. and Cons...

- Beware of.. doing it on production

BEGIN

  DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER

(parameter => 'ACCEPT_SQL_PROFILES',

value => 'TRUE'

);

END;

/

- For disable it,  use below command

BEGIN

  DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER

(parameter => 'ACCEPT_SQL_PROFILES',

 value => 'FALSE'

);

END;

/

Change ADDM/AWR Snapshot interval to 2 minute

Change ADDM/AWR Snapshot interval to 2 minute

sqlplus -s /NOLOG

set echo on

connect / as sysdba

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

grant dba to SH;

rem -- event to allow setting very short Flushing interval

alter session set events '13508 trace name context forever, level 1';

rem -- change INTERVAL setting to 2 minutes

rem -- change RETENTION setting to 6 hours (total of 180 snapshots)

execute dbms_workload_repository.modify_snapshot_settings(interval => 2,retention => 360);

rem -- play with ADDM sensitiveness

exec dbms_advisor.set_default_task_parameter('ADDM','DB_ACTIVITY_MIN',30);

alter user sh account unlock;

alter user sh identified by sh;

Thread 1 cannot allocate new log Sequence and Checkpoint not complete

Thread 1 cannot allocate new log Sequence
 and Checkpoint not complete

                                                  

When you will see these messages, like Oracle wants to reuse the redo log file, but checkpoint position is still in the log, Oracle must wait until the checkpoint completes.

CAUSE:

In this situation either DBWR writes slowly or log switch happens before the log is completely full or log file is small.

Thread 1 advanced to log sequence 38379 (LGWR switch)

  Current log# 3 seq# 38379 mem# 0: +OCMDB/OCMDB/onlinelog/group_3.263.853784771

Thread 1 advanced to log sequence 38380 (LGWR switch)

  Current log# 1 seq# 38380 mem# 0: +OCMDB/OCMDB/onlinelog/group_1.261.853784763

Mon Dec 29 05:17:19 2014

Thread 1 cannot allocate new log, sequence 38381

Checkpoint not complete

  Current log# 1 seq# 38380 mem# 0: +OCMDB/OCMDB/onlinelog/group_1.261.853784763

Thread 1 advanced to log sequence 38381 (LGWR switch)

  Current log# 2 seq# 38381 mem# 0: +OCMDB/OCMDB/onlinelog/group_2.262.853784767

Mon Dec 29 05:17:40 2014

Thread 1 cannot allocate new log, sequence 38382

Checkpoint not complete

This occurred when large number of DML (updates) in the system, and required you might need more redo groups in your running instances.

By adding more redo group in your running database its can help you to get rid into this.

ADDING REDO LOGS

SQL> ALTER DATABASE ADD LOGFILE GROUP …. ;

If you have smaller redo log and if you see many log switches then increasing the redo size might help.

Step1: Switching logfile to make group 1 ‘INACTIVE’

SQL> Alter system switch logfile;
SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 ACTIVE
3 CURRENT 

Step2:- Drop and recreate redo with size greater than earlier.

SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1 <…………..>   size 100M reuse;

Repeat step 1 and 2 until you drop and recreate all redo logs with bigger size.

It is a recommended to have 4-5 log switches per hour. You can use below Script to find the log switches on hourly basis.

SCRIPTS FOR CHECKING ARCHIVELOG GENERATION

SQL> set lines 200 pages 2000

SQL> SELECT to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
GROUP by to_char(first_time,'YYYY-MON-DD'); 

Archivelog generation on a daily basis:

SQL> select trunc(COMPLETION_TIME,'DD') Day,

thread#,

round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,

round((sum(BLOCKS*BLOCK_SIZE)/1048576)/1024

) GB,

count(*) Archives_Generated from v$archived_log

group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Archive log generation on an hourly basis:

SQL> select trunc(COMPLETION_TIME,'HH') Hour,

thread# ,

round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,

round(sum(BLOCKS*BLOCK_SIZE)/1048576)/1024 GB,

count(*) Archives

from  v$archived_log

group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

Manage Distributed Materialized Views

Manage Distributed Materialized Views

1.  From Oracle Documentation : Basic Materialized Views

2.  This objective requires us to know how to create a materialized in a distributed environment view, that is, create a separate MVIEW

# Create the DB LINK DB in TEST Database

# The first is to add the entry to the tnsnames.ora file

vi $ORACLE_HOME/network/admin/tnsnames.ora

# Add these lines

ORCL=

(DESCRIPTION=

  (ADDRESS=(PROTOCOL=tcp)(HOST=amit.example.com)(PORT=1521))

  (CONNECT_DATA=

     (SERVICE_NAME=ORCL)))

# Check that we have connectivity

tnsping ORCL

- Create the MVIEW LOG on the EMPLOYEES table

CREATE MATERIALIZED VIEW LOG ON EMPLOYEES;

- Create the DB LINK

CREATE PUBLIC DATABASE LINK ORCL CONNECT TO HR IDENTIFIED BY "hr" USING 'ORCL';

--- Tried connectivity

SELECT COUNT(*) FROM EMPLOYEES@ORCL;

- Create the EMP MVIEW pointing at table EMPLOYEES

CREATE MATERIALIZED VIEW EMP_MV REFRESH FAST AS

  SELECT * FROM EMPLOYEES@ORCL;

- Tried the Fast Refresh

                             

EXEC DBMS_MVIEW.REFRESH('EMP_MV','F');

3.       Materialized View Refresh Group: Create a group of materialized views and to make the data consistent between them.

- Create a MVIEW LOG in the DEPARTMENTS table (DATABASE ORCL)

CREATE MATERIALIZED VIEW LOG ON DEPARTMENTS;

- Create a second materialized view in the database of ORCL

CREATE MATERIALIZED VIEW DEP_MV REFRESH FAST AS

  SELECT * FROM DEPARTMENTS@ORCL;

Create the refresh group

- Information can be found in these two views
--   · DBA_REFRESH

--   · DBA_REFRESH_CHILDREN

BEGIN

DBMS_REFRESH.MAKE (

  NAME => 'REFRESH_GROUP_TEST',

  LIST => 'EMP_MV,DEP_MV',

  NEXT_DATE => SYSDATE,

  INTERVAL => 'SYSDATE+1/1440',

  IMPLICIT_DESTROY => TRUE);

END;

/

- Cleaning environment

EXEC DBMS_REFRESH.DESTROY('REFRESH_GROUP_TEST');

DROP MATERIALIZED VIEW EMP_MV;

DROP MATERIALIZED VIEW DEP_MV;

DROP PUBLIC DATABASE LINK ORCL;

-- Deleting the MVIEW LOG

DROP MATERIALIZED VIEW LOG ON EMPLOYEES;

DROP MATERIALIZED VIEW LOG ON DEPARTMENTS;

GoldenGate - Overview

GoldenGate - Overview

GoldenGate enables us to extract and replicate data across a variety of topologies as shown the diagram below as well as the exchange and manipulation of data at the transactional level between a variety of database platforms like Oracle, MySQL, SQL Server, Teradata, UDB, DB2 etc.

It can support a number of different business requirements like:

§  Business Continuity and High Availability

§  Data migrations and upgrades

§  Decision Support Systems and Data Warehousing

§  Data integration and consolidation

Manager

The Manager process must be running on both the source as well as target systems before the Extract or Replicat process can be started and performs a number of functions including monitoring and starting other GoldenGate processes, managing the trail files and also reporting.

Extract

The Extract process runs on the source system and is the data capture mechanism of GoldenGate. It can be configured both for initial loading of the source data as well as to synchronize the changed data on the source with the target. This can be configured to also propagate any DDL changes on those databases where DDL change support is available.

Replicat

The Replicat process runs on the target system and reads transactional data changes as well as DDL changes and replicates then to the target database. Like the Extract process, the Replicat process can also be configured for Initial Load as well as Change Synchronization.

Collector

The Collector is a background process which runs on the target system and is started automatically by the Manager (Dynamic Collector) or it can be configured to stsrt manually (Static Collector). It receives extracted data changes that are sent via TCP/IP and writes then to the trail files from where they are processed by the Replicat process.

Trails

Trails are series of files that GoldenGate temporarily stores on disks and these files are written to and read from by the Extract and Replicat processes as the case may be. Depending on the configuration chosen, these trail files can exist on the source as well as on the target systems. If it exists on the local system, it will be known an Extract Trail or as an Remote Trail if it exists on the target system.

Data Pumps

Data Pumps are secondary extract mechanisms which exist in the source configuration. This is optional component and if Data Pump is not used then Extract sends data via TCP/IP to the remote trail on the target. When Data Pump is configured, the Primary Extract process will write to the Local Trail and then this trail is read by the Data Pump and data is sent over the network to Remote Trails on the target system.

In the absence of Data Pump, the data that the Extract process extracts resides in memory alone and there is no storage of this data anywhere on the source system. In case of network of target failures, there could be cases where the primary extract process can abort or abend. Data Pump can also be useful in those cases where we are doing complex filtering and transformation of data as well as when we are consolidating data from many sources to a central target.

Data source

When processing transactional data changes, the Extract process can obtain data directly from the database transaction logs (Oracle, DB2, SQL Server, MySQL etc) or from a GoldenGate Vendor Access Module (VAM) where the database vendor (for example Teradata) will provide the required components that will be used by Extract to extract the data changes.

Groups

To differentiate between the number of different Extract and Replicat groups which can potentially co-exist on a system, we can define processing groups. For instance, if we want to replicate different sets of data in parallel, we can create two Replicat groups.

A processing group consists of a process which could be either a Extract or Replicat process, a corresponding parameter file, checkpoint file or checkpoint table (for Replicat) and other files which could be associated with the process.

GoldenGate - Error ORA-20782 while deleting Golden Gate User

Error ORA-20782 while deleting Golden Gate User

While dropping GoldenGate replicated user, I got an error.  Details are given below.

SQL> select distinct owner

     from dba_segments

     where owner in (select username

                from dba_users

                where default_tablespace not in ('SYSTEM','SYSAUX')

                ) ;

OWNER

------------------------------

GGTEST

OGGSOURCE

OGGTARGET

…

…

SQL> drop user GGTEST cascade;

drop user GGTEST cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 2

ORA-20782: GoldenGate DDL Replication Error: Code :ORA-20782: Cannot DROP

object used in GoldenGate replication while trigger is enabled. Consult

GoldenGate documentation and/or call GoldenGate Technical Support if you wish to do so., error stack: ORA-06512: at line 231

ORA-06512: at line 957

Solution

Since I had run the ddl_setup.sql which created the DDL trigger for Golden Gate user schema.

So before deleting the Golden Gate replicated user, we need to drop the Golden Gate DDL trigger first.

SQL> SELECT * FROM DBA_TRIGGERS

WHERE OWNER='GGTEST';

no rows selected

SQL>  SELECT A.OBJ#, A.SYS_EVTS, B.NAME

FROM TRIGGER$ A,OBJ$ B

WHERE A.SYS_EVTS > 0

AND A.OBJ#=B.OBJ#

AND BASEOBJECT = 0;

      OBJ#   SYS_EVTS NAME

---------- ---------- ------------------------------

     11990     524256 LOGMNRGGC_TRIGGER

     13175       4096 AW_TRUNC_TRG

     13177       8192 AW_REN_TRG

     13179        128 AW_DROP_TRG

     13885       8416 NO_VM_DDL

     13886        128 NO_VM_DROP_A

     55420         64 CDC_ALTER_CTABLE_BEFORE

     55421         32 CDC_CREATE_CTABLE_AFTER

     55422         32 CDC_CREATE_CTABLE_BEFORE

     55423        128 CDC_DROP_CTABLE_BEFORE

     56322         96 EXPFIL_RESTRICT_TYPEEVOLVE

     56323       8256 EXPFIL_ALTEREXPTAB_MAINT

     57549       4224 XDB_PI_TRIG

     56320        128 EXPFIL_DROPOBJ_MAINT

     56321        128 EXPFIL_DROPUSR_MAINT

     58846       4096 RLMGR_TRUNCATE_MAINT

     63703        128 SDO_DROP_USER

     63971         32 SDO_ST_SYN_CREATE

     63807        128 SDO_TOPO_DROP_FTBL

     68065     524256 SDO_GEOR_BDDL_TRIGGER

     68066     524256 SDO_GEOR_ADDL_TRIGGER

     68143        128 SDO_NETWORK_DROP_USER

     71613          1 MGMT_STARTUP

     76915     524256 GGS_DDL_TRIGGER_BEFORE

24 rows selected.

SQL> drop trigger GGS_DDL_TRIGGER_BEFORE;

Trigger dropped.

SQL> drop user GGTEST cascade;

User dropped.