How to create a logon trigger for session tracing

first create log table:

create table dba_user.sessiontrace(LDATE date, MACHINE  VARCHAR2(64), PROGRAM VARCHAR2(48), OSUSER  VARCHAR2(30), MODULE  VARCHAR2(64), SID NUMBER, SERIAL# NUMBER, SPID VARCHAR2(24));

grant necessary permissions to dba_user:

grant alter session to dba_user;

grant select on v_$session to dba_user;

grant select on v_$process to dba_user;

Create procedure to set session trace on:


CREATE OR REPLACE PROCEDURE dba_user.logon_actions IS
CURSOR user_prog IS
SELECT s.machine,s.program,s.osuser,s.module,s.sid, s.serial#,p.spid  FROM v$session s, v$process p where s.audsid=sys_context('USERENV','SESSIONID') and s.paddr=p.addr;
user_rec user_prog%ROWTYPE;
begin
OPEN user_prog;
FETCH user_prog INTO user_rec;
insert into sessiontrace values(sysdate, user_rec.machine,user_rec.program,user_rec.osuser,user_rec.module,user_rec.sid,user_rec.serial#, user_rec.spid);
commit;
execute immediate 'alter session set sql_trace=true';
CLOSE user_prog;
END;
/
 
Finally create login trigger to trace user:

CREATE OR REPLACE TRIGGER dba_user.ON_LOGIN_OF_DBUSER
AFTER LOGON ON DBUSER.SCHEMA
begin
dba_user.logon_actions;
end;
/

An Example of Transportable Tablespaces

(set oracle environment for source DB)
At source DB:
connect / as sysdba
create tablespace data4 datafile
'/path1/data401.dbf' size 10m autoextend on next 10m,
'/path1/data402.dbf' size 10m autoextend on next 10m;
create tablespace indx4 datafile
'/path1/indx401.dbf' size 10m autoextend on next 10m;
create user tc identified by tc default tablespace data4
temporary tablespace temp quota unlimited on data4 quota unlimited on indx4;
grant create session, create table to tc;
create table tc.testtab (nr number, txt varchar2(10)) tablespace data4;
insert into tc.testtab values (1, 'line 1');
commit;
create index tc.i_testtab on tc.testtab(nr) tablespace indx4;
select owner'.'segment_name "OWNER.SEGMENT_NAME", segment_type, tablespace_name from dba_segments where tablespace_name in ('DATA4','INDX4');
alter tablespace data4 read only;
alter tablespace indx4 read only;
execute dbms_tts.transport_set_check('data4,indx4', TRUE);
select * from transport_set_violations;
exit;

At Shell:
exp "'/ as sysdba'" file=exp_tts.dmp log=exp_tts.log transport_tablespace=y tablespaces=data4,indx4
cp /path1/data401.dbf /path2/data401.dbf
cp /path1/data402.dbf /path2/data402.dbf
cp /path1/indx401.dbf /path2/indx401.dbf
cd /path2
chmod g+w *

(set oracle environment for destination DB)
At destination DB:
connect / as sysdba
create user tcnew identified by tcnew default tablespace tools temporary tablespace temp;
grant create session, create table, resource to tcnew;

At Shell:
imp "'/ as sysdba'" file=exp_tts.dmp log=imp_tts.log fromuser=tc touser=tcnew transport_tablespace=y datafiles=/path2/data401.dbf,/path2/data402.dbf,/path2/indx401.dbf

At destination DB:
connect / as sysdba
alter tablespace data4 read write;
alter tablespace indx4 read write;
alter user tcnew quota unlimited on data4;
alter user tcnew quota unlimited on indx4;
revoke resource from tcnew;
select owner'.'segment_name "OWNER.SEGMENT_NAME", segment_type, tablespace_name from dba_segments where tablespace_name in ('DATA4','INDX4');

How to determine full scan sqls?

Here is a sql script to determine full scan sqls:

SELECT /*+ ordered */
DISTINCT s.hash_value, s.buffer_gets, s.disk_reads, s.executions,
s.sql_text,
'SELECT * FROM V$SQL_PLAN WHERE ADDRESS='''
|| s.address
|| ''' AND HASH_VALUE='
|| s.hash_value
FROM v$sql_plan p, v$sqlarea s
WHERE p.options LIKE '%FULL%'
AND s.address = p.address
AND s.hash_value = p.hash_value
AND object_name <> 'DUAL'
-- AND EXECUTIONS > 10
ORDER BY s.buffer_gets DESC

Sql Script to Generate AWR Report

Here is a sql script to generate awr report using sqlplus:

select DBID,INSTANCE_NUMBER,SNAP_ID,END_INTERVAL_TIME, BEGIN_INTERVAL_TIME from dba_hist_snapshot order by BEGIN_INTERVAL_TIME;
undefine DBID
undefine INSID
undefine STARTSNAPID
undefine ENDSNAPID
column report_name new_value report_name;
select 'awr_'||(select NAME from v$database where DBID = &&DBID)||'_'||&&INSID||'_'||
(select to_char(END_INTERVAL_TIME,'yyyymmddhh24mi') from dba_hist_snapshot where SNAP_ID=&&STARTSNAPID)||'_'||
(select to_char(END_INTERVAL_TIME,'yyyymmddhh24mi') from dba_hist_snapshot where SNAP_ID=&&ENDSNAPID)
||'.html'
report_name
from dual;
set termout off
spool &&report_name
select output from table(sys.dbms_workload_repository.awr_report_html(&DBID,&INSID,&STARTSNAPID,&ENDSNAPID));
spool off
set termout on

Setting up Dataguard Configuration for Oracle 10G

I assume that physical standby database is previously created, configured and up-to-date.

Prod configuration
If you use spfile, set scope=both at the end of all alter commands. Other case, dont forget to alter parameters in init.ora file
1-)Be sure logging is enabled at database level:ALTER DATABASE FORCE LOGGING;
2-)Change db_unique_name parameter (in init.ora or spfile) if necessary. Default is ORACLE_SID. You may leave it as it is. Change requires restart to apply.
* alter system set STANDBY_FILE_MANAGEMENT=AUTO;
* alter system set log_archive_dest_state_1=ENABLE;
* alter system set log_archive_dest_state_2=DEFER;
* alter system set log_archive_dest_1='LOCATION={archive_path_prod} MANDATORY REOPEN=60 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME={db_unique_name_prod}';
TNS_stby is the TNS name configured for standby database. I strongly suggest configuring a new TNS listener on a different port reserverd for dataguard configuration. We will set it later at standby database.
* alter system set log_archive_dest_2='SERVICE={TNS_stby} LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME={db_unique_name_stby}';
* alter system set FAL_SERVER={TNS_stby};
* alter system set FAL_CLIENT={TNS_prod}; -- TNS_prod: tns name for primary database.
3-)Be sure that remote_login_passwordfile is set to EXCLUSIVE (show parameter remote_login_passwordfile)
4-)On platforms other than Windows, create a password file, and set the password for the SYS user to the same password used by the SYS user on the primary database. The password for the SYS user on every database in a Data Guard configuration must be identical for redo transmission to succeed. See Oracle Database Administrator's Guide.
6-)Add number of redologgroups+1 standby logfiles to the prod database. These redo logs wont be used unless a swithover is performed.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 20 ('{redo_path}/stbyredo01.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 21 ('{redo_path}/stbyredo02.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 22 ('{redo_path}/stbyredo03.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 23 ('{redo_path}/stbyredo04.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 24 ('{redo_path}/stbyredo05.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 25 ('{redo_path}/stbyredo06.log') SIZE 52428800;

if there is RAC configuration then add logfiles for thread 2:

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 26 ('{redo_path}/stbyredo07.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 27 ('{redo_path}/stbyredo08.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 28 ('{redo_path}/stbyredo09.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 29 ('{redo_path}/stbyredo10.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 30 ('{redo_path}/stbyredo11.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 31 ('{redo_path}/stbyredo12.log') SIZE 52428800;

Standby configuration:
If you use spfile, set scope=both at the end of all alter commands. Other case, dont forget to alter parameters in init.ora file
1-) change db_unique_name parameter (in init.ora or spfile) if necessary. It can not be the same with production. I assume it is {db_unique_name_stby}
* alter system set STANDBY_FILE_MANAGEMENT=AUTO;
* alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
* alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
* alter system set LOG_ARCHIVE_DEST_1='LOCATION={archive_path_stby} VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME={db_unique_name_stby}';
TNS_prod is the TNS name configured for prod database. I strongly suggest configuring a new TNS listener on a different port reserverd for dataguard configuration.
* alter system set LOG_ARCHIVE_DEST_2='SERVICE={TNS_prod} LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME={db_unique_name_prod}';
* alter system set FAL_SERVER={TNS_prod};
* alter system set FAL_CLIENT={TNS_stby};
2-)Be sure that remote_login_passwordfile is set to EXCLUSIVE (show parameter remote_login_passwordfile)
3-)Configure listener at prod site so be sure that "tnsping TNS_prod" is working.
4-)Configure listener at standby site so be sure that "tnsping TNS_stby" is working.
5-)On platforms other than Windows, create a password file, and set the password for the SYS user to the same password used by the SYS user on the primary database. The password for the SYS user on every database in a Data Guard configuration must be identical for redo transmission to succeed. See Oracle Database Administrator's Guide.
6-)Add number of redologgroups+1 standby logfiles to the standby database.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 20 ('{redo_path}/stbyredo01.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 21 ('{redo_path}/stbyredo02.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 22 ('{redo_path}/stbyredo03.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 23 ('{redo_path}/stbyredo04.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 24 ('{redo_path}/stbyredo05.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 25 ('{redo_path}/stbyredo06.log') SIZE 52428800;

if there is RAC configuration then add logfiles for thread 2:

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 20 ('{redo_path}/stbyredo07.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 21 ('{redo_path}/stbyredo08.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 22 ('{redo_path}/stbyredo09.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 23 ('{redo_path}/stbyredo10.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 24 ('{redo_path}/stbyredo11.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 25 ('{redo_path}/stbyredo12.log') SIZE 52428800;


Finally, activating dataguard configuration (prod):
alter systen set log_archive_dest_state_2=ENABLE; -- If you use spfile, set scope=both, Other case, dont forget to update init.ora file

Tuning Network Configuration:
Network Tuning - Oracle Net Session Data Unit (SDU) Size
Prod (tnsnames.ora):
...
( DESCRIPTION =
(SDU=32767)
( ADDRESS = (PROTOCOL = TCP) (Host = ...) (Port = ...) )
( CONNECT_DATA = (SID = ...) )
)

Standby (listener.ora):
SID_LIST_LISTENER_... =
( SID_LIST=
(SID_DESC =
(SDU=32767)
(ORACLE_HOME = ...)
(SID_NAME =...)
)
)

Network Tuning: Setting TCP Socket Buffer Sizes:
BDP= {available bandwidth}*{Network Round Trip Time-RTT}
Socket Buffer Size = 3*BDP

Example for 34Mbit bandwith with 2ms RTT:
= 3* ((34000000*0.02)/8)
=255000

Prod(tnsnames.ora):
... =
( DESCRIPTION =
(SDU=32767)
(SEND_BUF_SIZE=255000)
(RECV_BUF_SIZE=255000)
( ADDRESS = (PROTOCOL = TCP) (Host = ...) (Port = ...) )
( CONNECT_DATA = (SID = ...) )
)

standby(listener.ora):
LISTENER_...=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= ...))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =...)(PORT = 1521) (SEND_BUF_SIZE=255000) (RECV_BUF_SIZE=255000))
)
)
)

Starting and stopping Managed Recovery (Standby):
start
STARTUP MOUNT;
alter database recover managed standby database using current logfile disconnect from session; -- Applies not only completed redo logs, but also redo log currently being copied
or
alter database recover managed standby database disconnect from session; -- Applies completed redo logs

stop
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SHUTDOWN IMMEDIATE;

Monitoring:
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

A Script to Add Supplemental Logging at Table Level

Here is a script which may be used to generate script which enables supplemental logging at table level for all tables.

declare
out varchar2(2000);
cflag number;
begin
-- You may change this line, i am choosing tables with only 50 to 250 MBs and owner OWNR
for l in ( select segment_name from dba_segments where bytes/(1024*1024) between 50 and 250 and owner in ('OWNR') and SEGMENT_TYPE = 'TABLE' and segment_name not like 'BIN%' order by segment_name)
loop
cflag := 0;
out :='alter table OWNR.'||l.segment_name||' ADD SUPPLEMENTAL LOG GROUP "'||substr(l.segment_name,1,25)||'_SLOG" (';


-- If there is a PK then add its columns
for k in (select COLUMN_NAME from DBA_IND_COLUMNS where INDEX_NAME in (select index_name from dba_constraints where CONSTRAINT_TYPE = 'P' and table_name=l.segment_name) order by COLUMN_POSITION)

loop
if (cflag = 0) then out:=out||k.column_name;
else out:=out||','||k.column_name;
end if;
cflag := 1;
end loop;

-- If there is no PK but there is a UI, then add its columns.
-- Normally there should not be more then one UI on any table. If there is, then is a problem. You may check it with this sql:
-- select TABLE_NAME,count(*) from dba_indexes where INDEX_TYPE='NORMAL' and UNIQUENESS='UNIQUE' and owner='OWNR' and
-- TABLE_NAME not in (select TABLE_NAME from dba_constraints where CONSTRAINT_TYPE = 'P') group by TABLE_NAME having count(*) > 1;

if (cflag = 0) then
for m in (select COLUMN_NAME from DBA_IND_COLUMNS where INDEX_NAME in (select index_name from dba_indexes where INDEX_TYPE='NORMAL' and UNIQUENESS='UNIQUE' and owner='OWNR' and table_name=l.segment_name) order by COLUMN_POSITION)

loop
if (cflag = 0) then out:=out||m.column_name;
else out:=out||','||m.column_name;
end if;
cflag := 1;
end loop;
end if;
-- If there is no PK and UI, then add all columns to supplemental logging
if (cflag = 0) then
for n in (select COLUMN_NAME from dba_tab_columns where table_name=l.segment_name order by COLUMN_ID)
loop
if (cflag = 0) then out:=out||n.column_name;
else out:=out||','||n.column_name;
end if;
cflag := 1;
end loop;
end if;

out := out||') ALWAYS;';
dbms_output.put_line(out);
end loop;
END;
/