Monday, June 18, 2012

An example of logon trigger

By user and by Module:
CREATE OR REPLACE TRIGGER SET_OPTIMIZER_ONLOGIN AFTER LOGON ON DATABASE
DECLARE
uname VARCHAR2(30);
app VARCHAR2(30);
cmmd VARCHAR2(64);
BEGIN
cmmd:='ALTER SESSION SET optimizer_index_cost_adj=20';
uname:=SYS_CONTEXT('USERENV','SESSION_USER');
app := upper(SYS_CONTEXT('USERENV','MODULE'));
IF uname = '*****' and app like 'SQLPLUS.EXE' then
   EXECUTE IMMEDIATE cmmd;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

By module only:
CREATE OR REPLACE TRIGGER SET_OPTIMIZER_ONLOGIN AFTER LOGON ON DATABASE
DECLARE
app VARCHAR2(30);
cmmd VARCHAR2(64);
BEGIN
cmmd:='ALTER SESSION SET optimizer_index_cost_adj=20';
app := upper(SYS_CONTEXT('USERENV','MODULE'));
IF app like 'RUNBATCH.EXE' then
   EXECUTE IMMEDIATE cmmd;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

Wednesday, May 30, 2012

Regular Expression in Ultraedit to replace Non ASCII characters

Find Non ASCII characters:
[~^t^r^n -~]+

Find Non ASCII characters following with space and ASCII characters:
[~^t^r^n -~]+ [-,."^?0-9a-z]

To replace the space with CR/LF:
search ^([~^t^r^n -~]+^) ^([-,."^?0-9a-z]+^)
and replace to ^1^p^2

Here ^1 = ^([~^t^r^n -~]+^)
     ^2 = ^([-,."^?0-9a-z]+^)
     space is going to be replaced by ^p(CR/LF)

Thursday, May 17, 2012

How to Resume a Failed RMAN Duplicate Due to Missing Backups or ArchiveLogs

It is relatively easy to resume the rman restore and recovery process if it has failed due to missing backups or archivelogs.  You most often see the following error messages complaining that something is missing. RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/24/2010 12:52:44
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 6253 lowscn 1663211060 found to restore

You may have forgotten to copy them over to the same local disk location and/or  failed to restore due to tape issues. After you are sure that you have everything you need in the location RMAN expects them,  shutdown the database that is being duplicated (this is assumed that the controlfile has been restored and the database mounted), start it backup in nomount mode and just rerun the same commands.

RMAN automatically (tested using Oracle RDBMS Enterprise Edition 10.2.0.4) runs through the commands and continues the restore and recovery process from where it left off.

For example: There was a gap in the archive logs in the flash recovery area. I have an alternate archive destination that contains the missing log.
After the first duplicate command failed, I used RMAN to catalog the existence of the  missing log:
CATALOG ARCHIVELOG '/u01/arc/ORACLE_SID/1_6253_DBID.arc';
see MOS Document: How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies [ID 470463.1]  Then shutdown, startup nomount the database being duplicated. Reran the exact same duplicate command:
archive log thread 1 sequence 6251 is already on disk as file /backup/flash_recovery_area/ORACLE_SID/archivelog/2010_05_22/o1_mf_1_6251_5zk173bs_.arc
archive log thread 1 sequence 6252 is already on disk as file /backup/flash_recovery_area/ORACLE_SID/archivelog/2010_05_22/o1_mf_1_6252_5zkhfw99_.arc
archive log thread 1 sequence 6253 is already on disk as file /u01/arc/ORACLE_SID/1_6253_DBID.arc
archive log thread 1 sequence 6254 is already on disk as file /backup/flash_recovery_area/ORACLE_SID/archivelog/2010_05_23/o1_mf_1_6254_5zl68htc_.arc
As you can see from the above output…that it applies the archive logs including the single cataloged piece in the alternate location.  This may save someone else time and trouble when they have the same issue.

 

Monday, May 7, 2012

TDPO for TSM setup on AIX

1. make sure oracle id has full access to tsm installation folder and files.
2. vi /usr/tivoli/tsm/client/ba/bin64/dsm.sys:
    SErvername your TSM server name, for example, TSM_ORA
    COMMmethod  TCPIP
    TCPPort     1500
    TcpServerAddress tsm server IP
    NODENAME  client_node registered on your TSM server(for example, client_ora) 
3. vi /usr/tivoli/tsm/client/ba/bin64/dsm.opt:
    SErvername  your TSM server name, for example, TSM_ORA 
4. su - oracle
5. ln -s /usr/lib/libobk64.a $ORACLE_HOME/lib/libobk.a
6. vi /usr/tivoli/tsm/client/oracle/bin64/tdpo.opt:
    DSMI_ORC_CONFIG    (location of dsm.opt, for example, /usr/tivoli/tsm/client/oracle/bin64/dsm.opt)
    DSMI_DIR           (location for dsm.sys,for example, /usr/tivoli/tsm/client/oracle/bin64)
    TDPO_FS            (Space name for backup on TSM, for example, adsmorc
    TDPO_NODE      (Nodename for client, usually the servername of client)
    TDPO_OWNER   (TDPO owner, for example, oracle)
    TDPO_PSWDPATH      (tdpoconf password file location)
    DSMI_LOG          (Log file location, for example, /home/oracle)
7. tdpoconf password -tdpo_optfile=<full path of tdpo.opt file>
    It will create a password file TDPO.nodename
    You should get the password from the TSM admin
8. ln -s /usr/tivoli/tsm/client/lang/EN_US /usr/tivoli/tsm/client/oracle/bin64/EN_US
9. make sure user oracle has full access to all files under /usr/tivoli/tsm/client/oracle/bin64
10. test: $ORACLE_HOME/bin/sbttest test
    if you see:
    The sbt function pointers are loaded from libobk.a(shr.o) library.
    -- sbtinit succeeded

    Means the tdpo for oracle are configured successfully.

Wednesday, May 2, 2012

ORA-20005: object statistics are locked (stattype = ALL) after database clone

After database clone or datapump import, you may get the error: "ORA-20005: object statistics are locked (stattype = ALL)":
SQL> exec dbms_stats.gather_table_stats(ownname=>'cisadm',tabname=>'c0_installation',ESTIMATE_PERCENT => 30);
BEGIN dbms_stats.gather_table_stats(ownname=>'cisadm',tabname=>'c0_installation',ESTIMATE_PERCENT => 30); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1

Tuesday, May 1, 2012

Check the value of ESTIMATE_PERCENT:

The default value of estimate is AUTO_SAMPLE_SIZE.

10g

SQL> select
DBMS_STATS.GET_PARAM ('AUTOSTATS_TARGET'),
DBMS_STATS.GET_PARAM ('CASCADE'),
DBMS_STATS.GET_PARAM ('DEGREE'),
DBMS_STATS.GET_PARAM ('ESTIMATE_PERCENT'),
DBMS_STATS.GET_PARAM ('METHOD_OPT'),
DBMS_STATS.GET_PARAM ('NO_INVALIDATE'),
DBMS_STATS.GET_PARAM ('GRANULARITY')
from dual;

11g

SQL> select
DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET'),
DBMS_STATS.GET_PREFS ('CASCADE'),
DBMS_STATS.GET_PREFS ('DEGREE'),
DBMS_STATS.GET_PREFS ('ESTIMATE_PERCENT'),
DBMS_STATS.GET_PREFS ('METHOD_OPT'),
DBMS_STATS.GET_PREFS ('NO_INVALIDATE'),
DBMS_STATS.GET_PREFS ('GRANULARITY'),
DBMS_STATS.GET_PREFS ('PUBLISH'),
DBMS_STATS.GET_PREFS ('INCREMENTAL'),
DBMS_STATS.GET_PREFS ('STALE_PERCENT')
from dual;

GATHER_STATS_JOB

To check the job status in 10g:
select * from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
select * from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB';
select * from dba_scheduler_job_log where job_name = 'GATHER_STATS_JOB';

To Check the scheduler for this job:
select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from dba_scheduler_jobs where job_name='GATHER_STATS_JOB';
SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS; 
If the state is not "Scheduled":
EXEC DBMS_SCHEDULER.ENABLE ('GATHER_STATS_JOB');
if the window is not enabled:
EXEC DBMS_SCHEDULER.ENABLE ('SYS.WINDOWNAME');

You can also create the job on different user and execute it:
BEGIN
dbms_scheduler.create_Job (job_name=>'Gather_Stats_Job',
job_type=>'PLSQL_BLOCK',
job_action=>'BEGIN Dbms_Stats.gather_database_stats(cascade=>true); end;',
start_date => sysdate,
repeat_interval => 'freq=daily',
enabled=>true);
end;
/

BEGIN
DBMS_SCHEDULER.RUN_JOB(
job_name => 'GATHER_STATS_JOB'
);
END;
/

To disable gather_stats_job in 10g:
BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/
To drop the job, login as creator and run:
EXEC DBMS_SCHEDULER.DROP_JOB (JOB_NAME=>'GATHER_STATS_JOB', force => true);