how to uninstall symantec Endpoint Protection from windows 7 laptop

I am having issues uninstalling Symantec Endpoint Protection Client. Each time I tried to uninstall it, it just rolled back.
Finally Here is what helped me: youhave to uncheck Protect Symantec security software from being tampered with or shutdown: After this, go to control panel->programs and features and click symantec to uninstall. then restart.

To allow Symantec Endpoint Protection services to be configured

1. Right-click the Symantec Endpoint Protection icon in the lower right corner of the screen, and click Open Symantec Endpoint Protection.
2. In the left pane, click Change Settings.
3. In the right pane, click Client Management > Configure Settings.
4. On the Tamper Protection tab, uncheck Protect Symantec security software from being tampered with or shut down.
5. Click OK.
6. Close Symantec Endpoint Protection.

snapshot controlfile in Oracle RAC must be on shared location on versions >=11.2.0.2

source: 

https://dbatrain.wordpress.com/2011/09/

On a recent Oracle Certified Masters (OCM) prep workshop, a question arose concerning the purpose and use of the RMAN Snapshot control file. This in turn led to some interesting exploration of a change in Oracle 11g R2 beginning in 11.2.0.2.

To understand this change it is first a good idea to review the snapshot controlfile which in turn requires an understanding of RMANResync operations.

RMAN Resync Operations
RMAN performs Resync operations when the RMAN Client is connected to a target and an RMAN catalog. There are also two ways forResync to be invoked:

• Manual Resync – using the RESYNC CATALOG command;
  
• Automatic Resync – this may be partial or full as explained below.

There are two types of Resync:

• Full Resync
• Partial Resync

To understand when these are done, consider that the controlfile contains certain records that are able to be overwritten based on theCONTROLFILE_RECORD_KEEP_TIME parameter. These are records in the controlfile, that contain details of archivelogs produced and which may be overwritten once those details are copied to the catalogue. But many of the other controlfile record types are not overwritten as they represent persistent configuration details. These records include but are not restricted to things such as:

• Database Record
• Redo Thread Records
• Log File Records
• Checkpoint Progress Records
• Tablespace Records
• Filename Records
• RMAN Persistent Configuration Records

Details of all the record types, including the number of records per type is available in the V$CONTROLFILE_RECORD_SECTION view.

When changes occur only to the records that may be overwritten, then a Partial Resync is performed automatically the next time that theRMAN client connects to the target and the catalogue and does any of the following:

• Backup
• Restore
• Recover

A Full Resync occurs in the following cases:

• When changes occur to any non-overwriteable section of the controlfile and any of the three commands listed above is issued
• When a database is first registered in the catalogue using the REGISTER DATABASE command
• When a RESYNC command is issued from the RMAN Prompt

Snapshot Controlfile Locations

Prior to Oracle 11.2.0.2, The Snapshot Controlfile was used only during a Full Resync to reduce the duration of the CF ENQUEUE held exclusively on the controlfile. The enqueue prevents updates to any of the control file records, because there are dependencies between records in some sections and others. For example, there are tablespace records and file records, and during a Full Resync, the consistency between these two record types must be maintained until all the controlfile records are resync’ed to the catalogue. This prevents Oracle processes making changes to anything in the controlfile that might cause the records to become inconsistent.

But the controlfile is frequently updated in a busy system, so instead of holding the CF ENQUEUE for the duration of the resync, the controlfile is essentially copied to the “Snapshot” controlfile location in one go whilst holding the CF ENQUEUE, and then it is dequeued. The catalogue is then resync’ed from the “Snapshot”.

By default, the “Snapshot” is written to the $ORACLE_HOME/dbs directory which may be seen by doing an RMAN SHOW ALLcommand. But this may be configured to another location if required.

In RAC for example, the directory for the snapshots, must be a valid path name on any node in the cluster which has an instance of the database, to guarantee that the snapshot may be created regardless of which instance is serving the RMAN client. But here is where the change occurs.

Prior to 11.2.0.2, the Snapshot Controlfile location did NOT require shared storage, only that the path was valid from all nodes as stated above. From 11.2.0.2, it must be in shared storage when using RAC. If not then one might see the following:

RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: =========================================================== 
RMAN-03009: failure of Control File and SPFILE Autobackup command on 
ORA_DISK_1 channel at 31/08/2011 13:34:07
ORA-00245: control file backup operation failed

What is going on?  The answer is that the Snapshot Controlfile is being used for ordinary controlfile backups from 11.2.0.2 for:

• Control file backups taken manually from SQL*PLUS
• Control file auto backups taken by the Oracle server when configured from the RMAN client

For example here is what happens if a control file backup is attempted in RAC in 11.2.0.2 when not using shared storage:

SQL> alter database backup controlfile to ‘/u01/app/oracle/product/11.2.0.2/dbhome_i/dbs/ctlbackup.ctl’ ;
ERROR at line 1:
ORA-00245: control file backup operation failed

The $ORACLE_HOME/dbs directory is normally local in RAC, since the entire ORACLE_HOME is on local storage. This facilitates rolling patches, but from 11.2.0.2 the dbs directory is no longer useful for the snapshots.

The reason for this change is that control file backups traditionally used the CF ENQUEUE to lock the controlfile for the duration of the backup but in some high workload systems this was a performance bottleneck. The solution, was to take the controlfile backup from the snapshot and avoid holding the CF ENQUEUE whilst doing it. But that required maintaining the snapshot as changes occur to the controlfile. Since changes could occur from any instance of the database, the snapshot must be on shared storage, just like the proper control file is.

Look at the OERR output for the error:

> oerr ora 00245

00245, 00000, “control file backup failed; target is likely on a local file system”
// *Cause: Failed to create a control file backup because some process
// signaled an error during backup creation. This is likely caused
// by the backup target being on a local file system so it could not
// be accessed by other instances. It can also be caused by other
// I/O errors to the backup target. Any process of any instance that
// starts a read/write control file transaction must have access
// to the backup control file during backup creation.
// *Action: Check alert files of all instances for further information.

When upgrading to 11.2.0.2 under RAC, make sure to avoid this problem by using RMAN to configure the Snapshot Controlfile to a shared location:

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/snapcf_?.f';

dump_table_to_csv procedure

 exec DUMP_TABLE_TO_CSV('ULS.MARKET','DATA_PUMP_DIR','market.csv');


CREATE OR REPLACE procedure SYS.DUMP_TABLE_TO_CSV( p_tname in varchar2,
                                                   p_dir   in varchar2,
                                                  p_filename in varchar2 )
   is
        l_output        utl_file.file_type;
        l_theCursor     integer default dbms_sql.open_cursor;
        l_columnValue   varchar2(4000);
        l_status        integer;
        l_query         varchar2(1000)
                       default 'select * from ' || p_tname;
       l_colCnt        number := 0;
       l_separator     varchar2(1);
       l_descTbl       dbms_sql.desc_tab;
   begin
       l_output := utl_file.fopen( p_dir, p_filename, 'w' );
       execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''
';
  
       dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
       dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 
       for i in 1 .. l_colCnt loop
           utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"'
);
           dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
           l_separator := ',';
       end loop;
       utl_file.new_line( l_output );
  
       l_status := dbms_sql.execute(l_theCursor);
  
       while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i, l_columnValue );
               utl_file.put( l_output, l_separator || l_columnValue );
               l_separator := ',';
           end loop;
           utl_file.new_line( l_output );
       end loop;
       dbms_sql.close_cursor(l_theCursor);
       utl_file.fclose( l_output );
  
       execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
   exception
       when others then
           execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
           raise;
   end;
/

sybase logon trigger to prevent unauthorized user connections

USE sybsystemprocs
go
IF OBJECT_ID('dbo.sp_login_trigger') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp_login_trigger
    IF OBJECT_ID('dbo.sp_login_trigger') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_login_trigger >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_login_trigger >>>'
END
go
create procedure dbo.sp_login_trigger
as
declare
        @loginname varchar(32),
        @hostname varchar(32),
        @ipaddress varchar(32)


--This is an example to simply restrict user bsun from connecting to the server via a specific IP address
--To configure a login-specific login trigger:
--sp_modifylogin my_login, 'login script',sp_login_trigger
--To drop the login trigger
--sp_modifylogin my_login, 'login script',NULL

-- drop the global login trigger
--sp_logintrigger 'drop'

-- change the global login trigger
--sp_logintrigger 'your_db.dbo.a_different_proc'

--To change the login trigger
--sp_modifylogin my_login,'login script',a_dirrerent_sp
--TO CONFIGURE a GLOBAL LOGIN TRIGGER
--sp_logintrigger 'sybsystemprocs.dbo.sp_login_trigger'
--or: sp_modifylogin NMULL,'login script','sybsystemprocs.dbo.sp_login_trigger'


select @loginname=suser_name(suid), @hostname=rtrim(clienthostname), @ipaddress=rtrim(ipaddr)
from master.dbo.sysprocesses where suid=suser_id()
--print "BEFORE IF"
--print @hostname
print @loginname
print @ipaddress

--if (@hostname='P019323' and @loginname='bsun') or (@ipaddress='192.168.23.74' and @loginname='bsun')
if @ipaddress='165.135.23.74' and @loginname='bsun'

    begin
        print 'Restricted User %1! using %2! blocked out', @loginname, @hostname
        print 'User %1! is not allowed to connect to %2! from %3!', @loginname,@@servername,@ipaddress
            /* Log an entry in an audit table */
            --insert into APP_ACCESS_VIOLATION values (@loginname, @appname, getdate(), 'Access Violation')
        select syb_quit()
    end
go
EXEC sp_procxmode 'dbo.sp_login_trigger', 'unchained'
go
IF OBJECT_ID('dbo.sp_login_trigger') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.sp_login_trigger >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_login_trigger >>>'
go
REVOKE EXECUTE ON dbo.sp_login_trigger FROM public
go
GRANT EXECUTE ON dbo.sp_login_trigger TO public
go

dba_2pc_pending distributed transaction cleanup delete

Hi,

You Can try the following to rollback your transactions:
(It appears that you are seeing distributed transactions as Prepared instead of collecting.

Try running this:

SQL> select LOCAL_TRAN_ID,STATE from DBA_2PC_PENDING;


Then based on your results issue the following:

SQL> rollback force '';


SQL> commit;

SQL> alter system set "_smu_debug_mode" = 4;

SQL> exec dbms_transaction.purge_lost_db_entry('');

SQL> exit


You should repeat this for each entry that you get back from the first query.

Good luck.

Stacy

sybase interface file convert from TLI to TCP

SERVERNAME

      master tli tcp /dev/tcp \x000213880a8d6b360000000000000000

      query tli tcp /dev/tcp \x000213880a8d6b360000000000000000

Indicates that this is a "tli address" family. This is always the start of the start of a TLI address. TCP/IP is family 2, depending on network vendor and byte order of the machine, the most common is 0002, or 0200 depending whether the machine is 'big endian' or 'little endian'. Check your current interfaces file.	This is the hexadecimal equivalent of the port number	This is the first part of the IP address.	This is the second part of the IP address.	This is the third part of the IP address.	This is the fourth part of the IP address.	This is a 'padding' value, and is platform dependant, on Solaris it is 16 zeros. It is mandatory
\x0002	1388	0a	14	1e	28	0000000000000000

Resetting forgotten Oracle Linux root password

http://frogtec.blogspot.com/2009/04/oracle-enterprise-linux-forgot-root.html

get oracle job DDLs with dbms_metadata.get_ddl

Trick:
is you query dba_objects for your job objects, the objecttype is "JOB". However, if you use this type to extract your DDL definition, you get the following:

SQL> select dbms_metadata.get_ddl('JOB','COORD_INC_BACKUP','DBA_ADMIN') from dual;
ERROR:
ORA-31604: invalid NAME parameter "NAME" for object type JOB in function
SET_FILTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4676
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8552
ORA-06512: at "SYS.DBMS_METADATA", line 2881
ORA-06512: at "SYS.DBMS_METADATA", line 2747
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

no rows selected

Infact, the object type of your job object is not JOB, but PROCJOB

SQL> set long 2000000

SQL> set pagesize 0

SQL> select dbms_metadata.get_ddl('PROCOBJ','COORD_INC_BACKUP','DBA_ADMIN') from dual;

BEGIN

dbms_scheduler.create_job('"COORD_INC_BACKUP"',

job_type=>'EXECUTABLE', job_action=>

'/u01/app/oracle/scripts/rman_coord_inc.sh'

, number_of_arguments=>0,

start_date=>NULL, repeat_interval=>

NULL

, end_date=>NULL,

job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=>

'coordinated dump (incremental 1) using rman'

);

COMMIT;

END;

SQL> 

Optimizer statistics History Information

The following is used to get the optimizer statistics' history retention value and the timestamps for which statistics are available to be restored to the oracle database:
DECLARE
r number
a timestamp;
BEGIN
a:=dbms_stats.get_stats_history_availability;
r:=dbms_stats.get_stats_history_retention;
dbms_output.put_line(a||'--'||r);
END;

password file number of entries

1. At the time of creating a password file you specify the maximum number of distinct DBA and OPERs. But say already this step had been taken care and now I want to see in the exsisting password file the max. no of entries specified. Which data dictionary or anyother place where we can get this info. take the size of the password file, subtract 1024 and divide by 128. Eg: $ ls -lag xxx yyy zzz -rwSr----- 1 tkyte other 3584 Dec 27 11:25 xxx -rwSr----- 1 tkyte other 6144 Dec 27 11:25 yyy 3584 -> 2560 -> 20, xxx has 20 entries 6144 -> 5120 -> 40, yyy has 40 entries

profile_hints.sql

----------------------------------------------------------------------------------------
--
-- File name:   profile_hints.sql
--
-- Purpose:     Show hints associated with a SQL Profile.
-
-- Author:      Kerry Osborne
--
-- Usage:       This scripts prompts for one value.
--
--              profile_name: the name of the profile to be modified
--
-- Description: This script pulls the hints associated with a SQL Profile.
--
-- Mods:        Modified to check for 10g or 11g as the hint structure changed.
--              Modified to join on category as well as signature.
--
--              See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
--
set sqlblanklines on
set feedback off
accept profile_name -
       prompt 'Enter value for profile_name: ' -
       default 'X0X0X0X0'

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
version varchar2(3);
l_category varchar2(30);
l_force_matching varchar2(3);
b_force_matching boolean;
begin
 select regexp_replace(version,'\..*') into version from v$instance;

if version = '10' then

-- dbms_output.put_line('version: '||version);
   execute immediate -- to avoid 942 error 
   'select attr_val as outline_hints '||
   'from dba_sql_profiles p, sqlprof$attr h '||
   'where p.signature = h.signature '||
   'and p.category = h.category  '||
   'and name like (''&&profile_name'') '||
   'order by attr#'
   bulk collect 
   into ar_profile_hints;

elsif version = '11' then

-- dbms_output.put_line('version: '||version);
   execute immediate -- to avoid 942 error 
   'select hint as outline_hints '||
   'from (select p.name, p.signature, p.category, row_number() '||
   '      over (partition by sd.signature, sd.category order by sd.signature) row_num, '||
   '      extractValue(value(t), ''/hint'') hint '||
   'from sqlobj$data sd, dba_sql_profiles p, '||
   '     table(xmlsequence(extract(xmltype(sd.comp_data), '||
   '                               ''/outline_data/hint''))) t '||
   'where sd.obj_type = 1 '||
   'and p.signature = sd.signature '||
   'and p.category = sd.category '||
   'and p.name like (''&&profile_name'')) '||
   'order by row_num'
   bulk collect 
   into ar_profile_hints;

end if;

  dbms_output.put_line(' ');
  dbms_output.put_line('HINT');
  dbms_output.put_line('------------------------------------------------------------------------------------------------------------------------------------------------------');
  for i in 1..ar_profile_hints.count loop
    dbms_output.put_line(ar_profile_hints(i));
  end loop;
  dbms_output.put_line(' ');
  dbms_output.put_line(ar_profile_hints.count||' rows selected.');
  dbms_output.put_line(' ');

end;
/
undef profile_name
set feedback on

create_sql_profile_awr.sqlPost title

----------------------------------------------------------------------------------------
--
-- File name:   create_sql_profile_awr.sql
--
-- Purpose:     Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.
--
-- Author:      Kerry Osborne
--
-- Usage:       This scripts prompts for five values.
--
--              sql_id: the sql_id of the statement to attach the profile to 
--              (must be in the shared pool and in AWR history)
--
--              plan_hash_value: the plan_hash_value of the statement in AWR history
--
--              profile_name: the name of the profile to be generated
--
--              category: the name of the category for the profile
--
--              force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description: 
--
--              Based on a script by Randolf Giest.
--
-- Mods:        This is the 2nd version of this script which removes dependency on rg_sqlprof2.sql.
--
--              See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
--

-- @rg_sqlprof1 '&&sql_id' &&child_no '&&category' '&force_matching'

set feedback off
set sqlblanklines on

accept sql_id -
       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'
accept plan_hash_value -
       prompt 'Enter value for plan_hash_value: ' 
accept profile_name -
       prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -
       default 'X0X0X0X0'
accept category -
       prompt 'Enter value for category (DEFAULT): ' -
       default 'DEFAULT'
accept force_matching -
       prompt 'Enter value for force_matching (FALSE): ' -
       default 'false'

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
l_profile_name varchar2(30);
begin
select
extractvalue(value(d), '/hint') as outline_hints
bulk collect
into
ar_profile_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id = '&&sql_id'
and plan_hash_value = &&plan_hash_value
and other_xml is not null
)
) d;

select
sql_text,
decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||'&&plan_hash_value','&&profile_name')
into
cl_sql_text, l_profile_name
from
dba_hist_sqltext
where
sql_id = '&&sql_id';

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
category => '&&category',
name => l_profile_name,
force_match => &&force_matching
-- replace => true
);

  dbms_output.put_line(' ');
  dbms_output.put_line('SQL Profile '||l_profile_name||' created.');
  dbms_output.put_line(' ');

exception
when NO_DATA_FOUND then
  dbms_output.put_line(' ');
  dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Plan: '||'&&plan_hash_value'||' not found in AWR.');
  dbms_output.put_line(' ');

end;
/

undef sql_id
undef plan_hash_value
undef profile_name
undef category
undef force_matching

set sqlblanklines off
set feedback on

create_sql_profile.sql

--THIS IS FROM uses cursor from the shared pool

----------------------------------------------------------------------------------------
--
-- File name:   create_sql_profile.sql
--
-- Purpose:     Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.
--
-- Author:      Kerry Osborne
--
-- Usage:       This scripts prompts for four values.
--
--              sql_id: the sql_id of the statement to attach the profile to (must be in the shared pool)
--
--              child_no: the child_no of the statement from v$sql
--
--              profile_name: the name of the profile to be generated
--
--              category: the name of the category for the profile
--
--              force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description: 
--
--              Based on a script by Randolf Giest.
--
-- Mods:        This is the 2nd version of this script which removes dependency on rg_sqlprof1.sql.
--
--              See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
--

-- @rg_sqlprof1 '&&sql_id' &&child_no '&&category' '&force_matching'

set feedback off
set sqlblanklines on

accept sql_id -
       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'
accept child_no -
       prompt 'Enter value for child_no (0): ' -
       default '0'
accept profile_name -
       prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -
       default 'X0X0X0X0'
accept category -
       prompt 'Enter value for category (DEFAULT): ' -
       default 'DEFAULT'
accept force_matching -
       prompt 'Enter value for force_matching (FALSE): ' -
       default 'false'

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
l_profile_name varchar2(30);
begin
select
extractvalue(value(d), '/hint') as outline_hints
bulk collect
into
ar_profile_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
sql_id = '&&sql_id'
and child_number = &&child_no
and other_xml is not null
)
) d;

select
sql_fulltext, 
decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
into
cl_sql_text, l_profile_name
from
v$sql
where
sql_id = '&&sql_id'
and child_number = &&child_no;

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
category => '&&category',
name => l_profile_name,
force_match => &&force_matching
-- replace => true
);

  dbms_output.put_line(' ');
  dbms_output.put_line('SQL Profile '||l_profile_name||' created.');
  dbms_output.put_line(' ');

exception
when NO_DATA_FOUND then
  dbms_output.put_line(' ');
  dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
  dbms_output.put_line(' ');

end;
/

undef sql_id
undef child_no
undef profile_name
undef category
undef force_matching

set sqlblanklines off
set feedback on

select level from dual connect by level<=10

SQL> select level from dual connect by level<=10;

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL>

column group statistics aka extended statistics in 11g

Using extended statistics in Oracle 11g

when queries have a list of predicates, it may be beneficial to collect statistics for these columns listed in the predicates. Luckily this could be down by oracle automatically once it is setup:

1. Observe workload that use those tables for 300 seconds by simply run explain plan for the long running query

Exec dbms_stats.seed_col_usage(null,null,300)

2. review the column usage information recorded in DBMS_STATS.REPORT_COL_USAGE

select dbms_stats.report_col_usaage(schema_owner,’table_name’) from dual;

3. Create the column groups

Exec dbms_stats.create_extended_stats(schema_owner,’table_name’);

4. review extended statistics collection that will be maintained by oracle

select dbms_stats.create_extended_stats(schema_owner,’table_name’) from dual;

5. Re-gather Statistics so that extended statistics for the column groups will be collected

Exec dbms_stats.gather_table_stats(schema_owner,’table_name’);

6. confirm what new statistics have been collected

select column_name,num_distinct, histogram from user_tab_col_statistics where table_name=’table_name’ order by 1;

endian format

To determin the endian format for a platform:

select a.endian_format, a.platform_id
from v$transportable_platform a, v$database b
where a.platform_name=b.platform_name;

DBMS_SQLTUNE

Step 1: Creating Tuning Task DECLARE my_task_name VARCHAR2 (30); my_sqltext CLOB; BEGIN my_sqltext := 'SELECT e.last_name, d.department_name, d.department_id FROM employees e, departments d WHERE e.department_id = d.department_id AND d.department_id = :bnd'; my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext, bind_list => sql_binds (anydata.convertnumber (9)), user_name => 'HR', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'vega_tuning_task', description => 'Tuning Task' ); END; / Create_tuning_task functions returns name of the task created. Step 2: Executing SQL Tuning Task BEGIN dbms_sqltune.execute_tuning_task (task_name => 'vega_tuning_task'); END; / Step 3: Checking Status of SQL Tuning Task SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'vega_tuning_task'; Step 4: Retrieving results of SQL tuning task After task is executed results can be obtained by calling REPORT_TUNING_TASK function SET LONG 1000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'vega_sql_tuning_task') FROM DUAL;

Block-change tracking: How effective it is

Oracle block-change tracking let's us do incremental backups for only the blocks that have been changed. To check the effectiveness, use the following query:

SELECT file#, avg(datafile_blocks), avg(blocks_read), avg(blocks_read/datafile_blocks)*100 AS PCT_READ_FOR_BACKUP, avg(blocks)
FROM V$BACKUP_DATAFILE
WHERE used_change_tracking='YES' and incremental_level>0
GROUP BY file#

Ideally we wanted to see the PCT_READ_FOR_BACKUP with a small value. If this value is very large, we can increase the frequency of incremental backups.

Oracle Data Pump (impdp and expdp)

User Crtl + C to suspend a expdp job started from a command line.
to stop, kill, resume, attach, cancel, restart a datapump job
http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/



To enhance performance for large data imports with impdp, consider two things:
One: Using parallellims:
Degree=2 x number of CPUs
Degree <= number of dump files
Degree of parallelims can be adjusted on the fly
Oracle uses worker processes and parallel execution (PX) process. Worker processes are used in inter-segment parallelims using DIRECTPATH while PX processes are used in intra-segment parallelims using EXTERNAL TABLE.
Oracle will automatically choose which method to use in parallellims.
There are times when you can use a hidden parameter to only use DIRECTPATH method
access_method=direct_path
Note: if direct_path cannot be used for a segment and the above parameter is set, data for that segment is not going to be loaded.
Two equally sized segments will be loaded faster with access_method=direct_path

Indexes are created by one at a time by a single worker process using multiple paralle PX processes. Index creation could take a long time.

Two: Setting the following parameters before the load:
1. disk_asynch_io=true
2. noarchivelog (makes big difference)
3. db_block_checking=false
4. db_block_checksum=false
5. disable block change tracking
6. size data files properly before loading, do not use autoextend

Setting EXCLUDE/INCLUDE parameter will also exclude/include dependent objects
To check dependencies:
database_export_objects
schema_export_objects
table_export_objects

To import data into a database with archivelog mode:
1. put database in NOLOGGING mode, or
2. a) create tables with content=metadata_only exclude=indes, constraint, ref_constraint
b) put tables in nologging mode
c) import with table_exists_action=append
4) cretae index with nologging


TRACING:
TRACE=480300 for impdp and expdp
extended log information
not very excessive data output size
(metalink Note: 286496.1)

DATAPUMP NETWORK MODE (Without creating dump file)

STEP 1: grant exp_full_database role to socre shcema [IN REMOTE DB]
conn system/pass;

GRANT EXP_FULL_DATABASE to user_info;

STEP 2: [IN DESTINATION DB] Create destination user and grant necessary roles

conn system/pass;
CREATE USER user_info
IDENTIFIED BY pass
DEFAULT TABLESPACE USER
TEMPORARY TABLESPACE TEMP;

GRANT CONNECT , RESOURCE TO user_info;

STEP 3: [IN DESTINATION DB] grant read/write on dump directory

conn system/pass;

GRANT read,write on DIRECTORY dump_directory to user_info;

Network import does not requer any dump file. This directory is only requer to write the import log file.

STEP 4: [IN DESTINATION DB] create public DB Link

conn system/pass;
CREATE PUBLIC DATABASE LINK SATURN
connect to user_info identified by pass
using '(DESCRIPTION=(
ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remote_db)
(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=orcl.oracle.com)
(server=DEDICATED)))';

STEP 5: [IN DESTINATION DB MACHINE] execute impdp

impdp user_info/pass directory=dump_dir network_link=SATURN logfile=net_import_proddev.log EXCLUDE=GRANT,STATISTICS,SYNONYM,DB_LINK REMAP_SCHEMA=USER_INFO:USER_INFO

Optimizer Statistics Restore

dba_optstat_operations: start and end time for all DBMS_STATS operations
dba_tab_stats_history: history of table statistics modifications for the past 31 days. This means you can use DBMS_STATS.RESTORE to restore optimizer statistics up to 31 days
dba_scheduler_jobs

If you user ANALYZE command to collect statistics, these statistics information will not be stored in the data dictionary.

dbms_stats.restore_table_stats(,,timestamp)
dbms_stats.restore_schema_stats
dbms_stats.restore_database_stats
dbms_stats.restore_dictionary_stats
dbms_stats.restore_fixed_objects_stats
dbms_stats.restore_system_stats
dbms_stats.purge_stats();
dbms_stats.alter_stats_history_retention ();
dbms_stats.get_stats_history_availability