Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Monday, December 5, 2016

Oracle 10/11 install on Centos 7

For 10.2.0.1:

If you get error
“Error in invoking target 'collector' of makefile '/orasw/app/oracle/product/10.2.0/sysman/lib/ins_emdb.mk'” at 83% can be ignored (Metalink 957982.1)

For 11.2.0.4:


If you get the following error:

PRVF-7532 : Package "pdksh" is missing on node "Hostname"
You can install ksh package (yum install ksh)then safely ignore it and  continue.


When getting error “Error in invoking target 'agent nmhs' of makefile '/orasw/app/oracle/product/11.2.0/sysman/lib/ins_emagent.mk'.”
vi $ORACLE_HOME/sysman/lib/ins_emagent.mk
Search for the line
$(MK_EMAGENT_NMECTL)
Change it to:
$(MK_EMAGENT_NMECTL) -lnnz11
And retry.



Thursday, August 25, 2016

ORA-03114 and ORA-03137

Oracle Version: 11.2.0.1
Client sessions disconnected due to error "ORA-03114: Not Connected to Oracle".
In Alert log ORA-03137 is detected: "ORA-03137: TTC protocol internal error : [12333] [8] [57] [48] [] [] [] [] ".

Friday, October 9, 2015

optimizer_index_cost_adj hint

Sometimes you want to use alter session set optimizer_index_cost_adj=<value> to change the parameter, but you can also use hint for the purpose, for example:
select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .
  
And optimizer_mode hint:
select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .  

Wednesday, July 11, 2012


Upgrade - Oracle Database 10.2.0.4 to 11.2.0.3

Oracle Database software uses 2 files (1 and 2) of the 11.2.0.3 patchset.
SQL> select name from v$database;
NAME
---------

TEST11G
SQL> select * from v$version;
BANNER

----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE    10.2.0.4.0    Production
TNS for Linux: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production


Tuesday, June 19, 2012

What are cvutrace.log.* files

 I was looking to clear some space recently and ran across a 21G directory (<grid_infrastructure_home>/cv/log). Inside there were a lot of files like “cvutrace.log.0_20100720015347″. These files are log files related to a RAC installation. They can safely be deleted.

To stop the files from being generated is add the following at the top of the cluvfy script in the <grid_infrastructure_home>/bin directory.



Windows:
set SRVM_TRACE=false

AIX:
export  SRVM_TRACE=false

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;
/

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);

Tuesday, April 17, 2012

gather_system_stats

SQL> exec dbms_stats.gather_system_stats (gathering_mode => 'noworkload');

PL/SQL procedure successfully completed.

SQL> select sname, pname, pval1 from sys.aux_stats$;
Resize undo table space

1. define a new temporary undo tablespace
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/ccbprod/oradata/ccbprod/UNDOTBS2.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M ;

Monday, April 16, 2012

Typical production init file (10g)

#####################################################################
# Common parms
#####################################################################
ifile = ?/dbs/orabase-dg.ora
#####################################################################
# Structural Parms
#####################################################################
db_domain = psoug
db_block_size = 8192
db_writer_processes = 8
Thread 1 cannot allocate new log

Sometimes, you can see in your alert.log file, the following corresponding
messages:


  Thread 1 advanced to log sequence 248
    Current log# 2 seq# 248 mem# 0: /prod1/oradata/logs/redologs02.log

  Thread 1 cannot allocate new log, sequence 249
  Checkpoint not complete

Resize Redo log

If the redo logs need to be resized, the following steps can be taken. 
1.  Create new log group members with the following command:

ALTER DATABASE ADD LOGFILE
GROUP 4 (    '/ccbprod/oradata/ccbprod/REDO04A.LOG',
        '/ccbprod/oradata/ccbprod/REDO04B.LOG') SIZE 1280M reuse,
GROUP 5 (    '/ccbprod/oradata/ccbprod/REDO05A.LOG',
        '/ccbprod/oradata/ccbprod/REDO05B.LOG') SIZE 1280M reuse,
GROUP 6 (    '/ccbprod/oradata/ccbprod/REDO06A.LOG',
        '/ccbprod/oradata/ccbprod/REDO06B.LOG') SIZE 1280M reuse;
Configure for oracle Single Instance install with ASM on centos

Create VM:




Oracle Checkpoint 

A checkpoint performs the following three operations:
Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk.
It's the DBWR that writes all modified databaseblocks back to the datafiles.

To change DBIO_EXPECTED for EM

 SELECT parameter_value,is_default FROM dba_advisor_def_parameters WHERE advisor_name = 'ADDM' AND parameter_name ='DBIO_EXPECTED';

EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 20000);

select * from aux_stats$;


EXECUTE IMMEDIATE option for Dynamic SQL and PL/SQL


EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created and executed SQL statements are performance overhead, EXECUTE IMMEDIATE aims at reducing the overhead and give better performance. It is also easier to code as compared to earlier means. The error messages generated when using this feature are more user friendly. Though DBMS_SQL is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package.