apps 11.5.10 software

download from on demand for linux  on edelivery.  32 bit  for 11.5.10  ( later you can upgrade to 11.5.10.2)
apps is only 32bit
db can be 64bit .

hide password entering on unix in desktop sharing

stty -echo
export var1=??
stty echo

echo $var1 --

how to check if goldengate trail file is corrupt

open the trail file and  do a count
if it says
Bad record found

then that means the trail file is corrupt.

unix one liner

 for i in `ls *.dmp`; do mv $i $i.`date +%m%d` ; done
 for i in `ls *.log`; do mv $i $i.`date +%m%d` ; done

toad connection not open in windows 7 64bit

install toad in something like c:\temp  instead of  c:\program file(x86)

datapump export from multiple schemas

DUMPFILE=bck_dir:t1.dmp
logfile=bck_dir:t1.log
schemas='scott','order'
include=TABLE:"IN('BLT_CTL','BRPT_LOG','ATLAS')"

in above case if a table is in both schema then it will export both the table from both schemas.

utl_recomp hanging

check the shared pool size

add sqlnet.outbound_connect_timeout=10 ( if single task message wait coming)
flush shared pool

oraenv in 11.2.0.3 on hp-ux itanium issue sh: @: Parameter not set.

$ . oraenv
sh: @: Parameter not set.

https://forums.oracle.com/forums/thread.jspa?threadID=2139644

Our unix administratos have set user profiles with:

set -u

which will exit any script if you try to use an uninitialised variable. 

DBMS_NETWORK_ACL_ADMIN to public

begin
 DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'DPB.xml',
    description  => ' ACL functionality',
    principal => 'DPB',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

   DBMS_NETWORK_ACL_ADMIN.assign_acl (
      acl         => 'DPB.xml',
      host        => '*',
      lower_port  => 1,
    upper_port  => 9999);


  DBMS_NETWORK_ACL_ADMIN.assign_acl (
     acl         => 'DPB.xml',
     host        => '*.dpbrep.com',
     lower_port  => null,
     upper_port  => null);

 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('/sys/acls/DPB.xml','DPB', TRUE, 'connect');
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('/sys/acls/DPB.xml','DPB', TRUE, 'resolve');
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('/sys/acls/DPB.xml','PUBLIC', TRUE, 'resolve');
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('/sys/acls/DPB.xml','PUBLIC', TRUE, 'connect');

commit;
end;

intermittent ORA-12545

make sure the client can make  telent on port  with all vips, all scan ips .

goldengate add trandata

when it does add trandata on a table it create a supplemental log group for this table
which can be seen on dba_log_groups

LD_LIBRARY_PATH - is it required

sysresv wont work without it . But not a necessity to be set everytime.

crsctl activeversion less then softwareversion

rootupgrade.sh dint run properly

so manually set the activeversion

integrating 10g RAC database to 11gR2 RAC

1- update 11.2.0.3 latest psu to oracle home
2- use srvctl from 10g db home to manage the 10g database
3- create a FAN callout  in 11g home to automate the scan service startup when the db start up.

trace the replicat in goldengate

place the below in the parameter file and it will display all the sql that replicat execute

TRACE

OGG-01921

I had to comment out the resolveconflict clause  in replicat and then let the transaction go thru  the current trail and after that enabled .

demantra- login hangs for dm but other user can login see the worksheet

check with how many work list and portal_task for user dm . try purging them and bounce the oas.

sending mail with attachment

uuencode awrrpt_1_4569_4570.html awrrpt_1_4569_4570.html | mailx -m -s "test"  em@email.com

PRVF-7617: TCP connectivity check failed for subnet

make sure firewall is turned off in all the nodes in RAC  or suitable rules are given

dba_data_files bytes is larger then maxbytes

encountered few .  seems like someone resize the datafile to a bigger size then maxbytes.

tnsping Ok but sqlplus hangs

one of the many reason

doing strace . it was showing as below in infinite loop


times(NULL)                             = -2093807697
times(NULL)                             = -2093807697
times(NULL)                             = -2093807697
times(NULL)                             = -2093807697
times(NULL)                             = -2093807697
times(NULL)                             = -2093807697
times(NULL)                             = -2093807697
               = -2093807696
[oracle@b6jasmine-prbs ~]$

the server was up for 200 days . so i had to do a reboot after that it worked fine ...

goldengate vip getting connection refused

goldengate bidirectional replication on site A,site B,site C

the goldengate vip is configured on site A .  but this morning when i came the vip was running on node B  on site B

and  the pump process running on site B and site C  that send to site A  were failing with

WARNING OGG-01223  Oracle GoldenGate Capture for Oracle, dpsiteA.prm:  TCP/IP error 111 (Connection refused), endpoint: :7809.

and trying a telnet connect from site B to site A    telnet 7809  was returning

telnet 7809

Trying ...

telnet: connect to address : Connection refused

telnet: Unable to connect to remote host: Connection refused

so on relocating the vip from node B to node A on site A   the issue was resolved.  

Goldengate replication on RAC and timezone change

I was troubleshooting a goldengate issue in a 2node RAC on RHEL8   i ran into a issue
where extract was not writing to trail log .

We recently changed the timezone of the server to GMT  from PST to match with other target server residing on different continent  while setting up the environment.   and we did our regular approach of bringging down the cluster and database using crsctl/srvctl way.  however i was surprised to see that the redo log timestamp was still on PST . Which means the golden gate extract begin now was not finding any latest change from the redo log threads .   no matter how many times I brought up brought down the database using srvctl didnt change the online redo log timestamp.  so i did  a manual restart of database instances  after that the redo started showing the timestamp on GMT . and bouncing the manager resolved the extract issue .

ORA-609 on the alert log with TNS lost contact on the client

I ran into this issue on a  2 node RAC 11.2.0.3 cluster   while accessing from the client PC.  here the scan listener was using a port 1590 while node listener was running on 1521 .   checking with the network team regarding firewall port opening for 1590 was verified. The information from network team regarding port 1521 was not clear  while troubleshooing over the weekend.. First of all looking the ORA-609  on the alert log was making the issue looks very confusing . I was seeng the request coming to the server on scan listener log .   It took a lot of hours  with no success .  so thought of giving a try to convert the node listener port  to also using 1590 port. which  did the trick .

SP2-0027: Input is too long (> 2499 characters) - line ignored

RUNNING  a very long insert with xml data to put into a clob column gave this issue on sqlplus.

sqldeveloper helped in this case...

ERROR: PST-initiated MANDATORY DISMOUNT of group DATA

It means ASM  is not able to offline the disks having issues so it dismount the entire diskgroup -


https://twiki.cern.ch/twiki/bin/view/PSSGroup/ASM_Internals

• PST - Partner Status Table. Maintains info on disk-to-diskgroup membership.
• COD - Continuing Operation Directory. The COD structure maintains the state of active ASM operations or changes, such as disk or datafile drop/add. The COD log record is either committed or rolled back based on the success of the operation. (source Oracle whitepaper)
• ACD - Active Change Directory. The ACD is analogous to a redo log, where changes to the metadata are logged. The ACD log record is used to determine point of recovery in the case of ASM operation failures or instance failures. (source Oracle whitepaper)
• OSM Oracle Storage Manager, legacy name, synonymous of ASM
• CSS Cluster Synchronization Services. Part of Oracle clusterware, mandatory with ASM even in single instance. CSS is used to heartbeat the health of the ASM instances.
• RBAL - Oracle backgroud process. In an ASM instance coordinated rebalancing operations. In a DB instance, opens and mount diskgroups from the local ASM instance.
• ARBx - Oracle backgroud processes. In an ASM instance, a slave for rebalancing operations
• PSPx - Oracle backgroud processes. In an ASM instance, Process Spawners
• GMON - Oracle backgroud processes. In an ASM instance, diskgroup monitor.
• ASMB - Oracle backgroud process. In an DB instance, keeps a (bequeath) persistent DB connection to the local ASM instance. Provides hearthbeat and ASM statistics. During a diskgroup rebalancing operation ASM communicates to the DB AU changes via this connection.
• O00x - Oracle backgroud processes. Slaves used to connected from the DB to the ASM instance for 'short operations'.

ALTER DATABASE RECOVER TO LOGICAL STANDBY struck

738643.1

one of the many reason - the

DBMS_LOGSTDBY.BUILD;

didnt finish yet.

orapwd and alter user for sys

alter user for sys updates the orapwd file .

but changing the password thru orapwd do not change the data dictionary (sys password) - again this is my assumption on my observation ( check out?)

physical standby on realtime apply

SQL> recover managed standby database using current logfile disconnect from session;

ORA-15110: no diskgroups mounted

check if you are on spfile or not .

rman duplicate from active database

1- TNS entry on both side

2- listener configuration is done

3- password for target and auxiliary should be same ( my personal observation)

4- use TNS(@ for both target and auxiliary

5- check the service name(dbms_service/listener)

ORA-16211: unsupported record found in the archived redo log

getting

ORA-16211: unsupported record found in the archived redo log

on the logical standby database can be due to having force logging not enabled.

asm dropping disk

for

ORA-01948: identifier's name length (31) exceeds maximum (30)

use the name from

select group_number, name, TOTAL_MB, FREE_MB

from V$asm_disk_stat;

in

alter diskgroup tan1_1 drop disk ' TAN1_2_0008';

ORA-1031 while connecting sys as sysasm

v$pwfile_users

grant sysasm to sys

oracle restart - asm status

somehow it doesn't show online after adding into crs stack

srvctl add asm -l LISTENER

but after bouncing the asm it shows properly

crsctl start resource ora.asm - forces a bounce.

alter system switch logfile hung

most of the time

ALTER SYSTEM ARCHIVE LOG CURRENT clears it

unmounting asm diskgroup do not release the file descriptor

issuing

select * from v$asm_disk in 11gR2 after the unmount/drop disk command. or apply the one off patch in 10g clears the file descriptor.

can be verified lsof /dev/rdisk/disk44 ( or if there is LVM lsof -d /dev/dgsap01 of lsof /dev/dgsap01/rasm* )

metalink Note - 402526.1

ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]

in 11gR2

SQL> create spfile from pfile;

create spfile from pfile

*

ERROR at line 1:

ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]

lsts[0]]

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl stat res

NAME=ora.LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.cssd

TYPE=ora.cssd.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.diskmon

TYPE=ora.diskmon.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.evmd

TYPE=ora.evm.type

TARGET=ONLINE

STATE=ONLINE on m3p131

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: srvctl add asm -l LISTENER

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl stat res

NAME=ora.LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.asm

TYPE=ora.asm.type

TARGET=OFFLINE

STATE=OFFLINE

NAME=ora.cssd

TYPE=ora.cssd.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.diskmon

TYPE=ora.diskmon.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.evmd

TYPE=ora.evm.type

TARGET=ONLINE

STATE=ONLINE on m3p131

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs:

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: sqlplus '/as sysasm'

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 6 09:43:53 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Automatic Storage Management option

SQL> create spfile from memory;

File created.

ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]

in 11gR2

SQL> create spfile from pfile;

create spfile from pfile

*

ERROR at line 1:

ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]

lsts[0]]

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl stat res

NAME=ora.LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.cssd

TYPE=ora.cssd.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.diskmon

TYPE=ora.diskmon.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.evmd

TYPE=ora.evm.type

TARGET=ONLINE

STATE=ONLINE on m3p131

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: srvctl add asm -l LISTENER

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl stat res

NAME=ora.LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.asm

TYPE=ora.asm.type

TARGET=OFFLINE

STATE=OFFLINE

NAME=ora.cssd

TYPE=ora.cssd.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.diskmon

TYPE=ora.diskmon.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.evmd

TYPE=ora.evm.type

TARGET=ONLINE

STATE=ONLINE on m3p131

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs:

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: sqlplus '/as sysasm'

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 6 09:43:53 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Automatic Storage Management option

SQL> create spfile from memory;

File created.

RMAN-06024 during duplicate database for standby

RMAN-06024 during duplicate database for standby

RMAN-06024: no backup or copy of the control file found to restore

alter system switch logfile on target should fix this

RMAN Duplicate For Standby Fails with Rman-06024: No Backup Or Copy Of The Control File Found To Restore [ID 466321.1]

ORA-00600: internal error code, arguments: [kfmrgReg01]

while creating a diskgroup on a 10.2.0.4 ASM we encountered

ORA-00600: internal error code, arguments: [kfmrgReg01], [24287664],

[84759367], [], [], [], [], []

tried dd,rebuilding volume group but eventually shutting down the asm removed this issue. asm gone crazy

ASM -renamedg KFNDG-00305: file not found

make sure you are issuing the renamedg from dba owned directory . as the renamedg create a file called rename_dg

and also make sure the diskgroup is dismounted

ORA-17628: Oracle error 19505 returned by remote Oracle server

precreate the contolfile directory in asm diskgroup then retry the duplicate from active database

rman duplicate from active database -PARAMETER_VALUE_CONVERT

incorrect/incomplete PARAMETER_VALUE_CONVERT can crash your target database . my target database crashed as i missed

it from rman duplicate while just trying to create the database with init.ora having only db_name entry

my controlfile/datafile/online log file in asm so i had to do as below

DUPLICATE TARGET DATABASE

TO t11gdact

FROM ACTIVE DATABASE

DB_FILE_NAME_CONVERT '+DATA/t11gdb/','+DATA/t11gdact/'

SPFILE

PARAMETER_VALUE_CONVERT '/u03/arch/t11gdb/','/u03/arch/t11gdact/','+DATA/t11gdb/','+DATA/t11gdact/'

SET SGA_MAX_SIZE '300M'

SET SGA_TARGET '250M'

SET LOG_FILE_NAME_CONVERT '+DATA/t11gdb/',

'+DATA/t11gdact/';

HPUX_SCHED_NOAGE

a must for oracle in hp-ux

ORA-29701: unable to connect to Cluster Manager on

checking the grep cssd I saw it was running as root user

+ASM:/home/oracle $ ps -ef | grep css

root 60799 20 0 10:14:39 ? 0:00 /bin/sh /sbin/init.d/init.cssd run

then asking the SA to do

localconfig delete

localconfig add

under OH fixed the issue by starting css the right way

oracle 35487 20 0 14:10:33 ? 0:00 /apps/oracle/product/10.2.0.3/bin/ocssd.bin

on 11gR2

mp012:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl start resource ora.cssd

CRS-2672: Attempting to start 'ora.cssd' on 'mp012'

CRS-2672: Attempting to start 'ora.diskmon' on 'mp012'

CRS-2676: Start of 'ora.diskmon' on 'mp012' succeeded

CRS-2676: Start of 'ora.cssd' on 'mp012' succeeded

mp012:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl modify resource "ora.cssd" -attr "AUTO_START=1"

mp012:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl modify resource "ora.diskmon" -attr "AUTO_START=1"

ORA-15096: lost disk write detected

one of the asm diskgroup after dropping it was still showing in v$asm_disk with error state. bringing down the asm didnt help either. so had to do dd to clean up the disk properly and then was able to recreate it.

ORA-15037:disk '/dev/asmvg02/rasmdisk02' is smaller than mimimum of 4 MBs

while creating a asm diskgroup I encountered this issue .

SQL> create diskgroup DFDG external redundancy disk

'/dev/asmvg02/rasmdisk02'; 2

create diskgroup DFDG external redundancy disk

*

ERROR at line 1:

ORA-15018: diskgroup cannot be created

ORA-15031: disk specification '/dev/asmvg02/rasmdisk02' matches no disks

ORA-15037: disk '/dev/asmvg02/rasmdisk02' is smaller than mimimum of 4 MBs

asking the SA he verified

vgdisplay -v /dev/asmvg02

diskinfo /dev/rdisk/disk11

and compared with the other working diskgroup disks.

but it didnt give any indication why i was hitting this error for this new diskgroup i am trying to create . So i gave a shot at dd and ifter that i was able to create diskgroup

+ASM:/dev/asmvg02: dd if=/dev/zero of=/dev/asmvg02/rasmdisk02 bs=4096 count=12800

12800+0 records in

12800+0 records out

+ASM:/dev/asmvg02: sqlplus '/as sysasm'

SQL> create diskgroup DFDG external redundancy disk

'/dev/asmvg02/rasmdisk02'; 2

Diskgroup created.

ORA-15063: ASM discovered an insufficient number of disks for diskgroup

We encountered this issue after the san copy is done.

ORA-15032: not all alterations performed

ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DFDATA_2"

ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DFDATA_1"

Looking into asm_diskstring dont seem to be any issue . however seems like the device mapping file was changed so the /dev/asmvg01 directory was not showing the proper asm disk group name .

once the SA fixed the device mapping file it recognized the asm disks and the system back online

kill -9 doesn't work always

well it didnt for me on a RHEL 5.3 for a process accessing NFS filesystem.

check - fuser -k

ORA-12853 / ORA-4031

ORA-12801: error signaled in parallel query server P002, instance xt0p-2:stpa2 (

2) ORA-12853: insufficient memory for PX buffers: current 68544K, max needed 15360K

ORA-04031: unable to allocate 131120 bytes of shared memory (""shared pool"",""unknown object"",""sga heap(2,0)"",""PX msg pool"")

3 node rac . parallel query process was spawning to node -2 . increasing the shared pool size fixed the query . and also changed the parallel instance groups.

ORA-14266: data type or length of an index subpartitioning column may

I was getting this error while trying to alter the data type for a column. as per the error . but as per the cause /action for this error there were no indexes/partitions on this column .

was not sure what is going on so i just dropped all the indexes( few normal index and partioned bitmap indexes) for this table and then when i try to do the alter it went fine .

move tablespace

select 'alter table mtsuser.'||table_name || ' move tablespace mtsuser_tab_data ;'

from dba_tables

where owner='MTSUSER'

select 'alter index mtsuser.'||index_name || ' rebuild tablespace mtsuser_IND_DATA ;'

from dba_indexes

where table_owner='MTSUSER'

set linesize 500

SELECT 'ALTER TABLE mtsuser.'||table_name ||' move lob('||column_name||') store as (tablespace mtsuser_tab_data );' FROM DBA_LOBS

WHERE owner='MTSUSER'

select 'alter table mtsuser.'||TABLE_NAME ||' move partition '||PARTITION_NAME||' tablespace mtsuser_data;' from dba_tab_partitions

where table_owner='MTSUSER'

select 'ALTER INDEX mtsuser.'||index_name ||' rebuild partition ' || partition_name ||' tablespace tnarchive_index ;'

from dba_ind_partitions

where index_owner='MTSUSER'

select 'alter table mtsuser.'||table_name ||' move partition '||partition_name||' tablespace mtsuser_data lob('||column_name ||') store as

(tablespace mtsuser_lob_data);'

from dba_lob_partitions

where table_owner='MTSUSER'

remote_listener -

It is recommended to set it up in RAC enviornment . but setting it up in regular standby databse gives rise to grid control alert ( observe the service update on both the network log files ) at times the grid control alert are misleading .

a good reference

http://oracledocaccess.blogspot.com/2009/04/remote-listener.html

UNDO in 9i and 10g

it is advisable to have AUTEXTEND OFF or have a suitable MAXSIZE in UNDO datafiles.

ref-

http://www.orafaq.com/node/61

best practice

This following list of recommendations will help you manage your undo space to best advantage.

• You need not set a value for the UNDO_RETENTION parameter unless your system has flashback or LOB retention requirements.

• Allow 10 to 20% extra space in your undo tablespace to provide for some fluctuation in your workload.

• Set the warning and critical alert thresholds for the undo tablespace alert properly. Please refer to "Managing Space in Tablespaces " for information on how to set alert thresholds for the undo tablespace.

• To tune SQL queries or to check on runaway queries, use the value of the SQLID column provided in the long query or in the V$UNDOSTAT or WRH$_UNDOSTAT views to retrieve SQL text and other details on the SQL fromV$SQL view.

• Always use indexes for Flashback Version Query.

  
  

  
  

  ref oracle docs

  Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger

  In the following example, a system wide AFTER SUSPEND trigger is created and registered as user SYS at the database level. Whenever a resumable statement is suspended in any session, this trigger can have either of two effects:

  • If an undo segment has reached its space limit, then a message is sent to the DBA and the statement is aborted.

  • If any other recoverable error has occurred, the timeout interval is reset to 8 hours.

  Here are the statements for this example:

  CREATE OR REPLACE TRIGGER resumable_default AFTER SUSPEND ON DATABASE DECLARE    /* declare transaction in this trigger is autonomous */    /* this is not required because transactions within a trigger       are always autonomous */    PRAGMA AUTONOMOUS_TRANSACTION;    cur_sid           NUMBER;    cur_inst          NUMBER;    errno             NUMBER;    err_type          VARCHAR2;    object_owner      VARCHAR2;    object_type       VARCHAR2;    table_space_name  VARCHAR2;    object_name       VARCHAR2;    sub_object_name   VARCHAR2;    error_txt         VARCHAR2;    msg_body          VARCHAR2;    ret_value         BOOLEAN;    mail_conn         UTL_SMTP.CONNECTION; BEGIN    -- Get session ID    SELECT DISTINCT(SID) INTO cur_SID FROM V$MYSTAT;     -- Get instance number    cur_inst := userenv('instance');     -- Get space error information    ret_value :=     DBMS_RESUMABLE.SPACE_ERROR_INFO(err_type,object_type,object_owner,         table_space_name,object_name, sub_object_name);    /*    -- If the error is related to undo segments, log error, send email    -- to DBA, and abort the statement. Otherwise, set timeout to 8 hours.    --     -- sys.rbs_error is a table which is to be    -- created by a DBA manually and defined as    -- (sql_text VARCHAR2(1000), error_msg VARCHAR2(4000),    -- suspend_time DATE)    */     IF OBJECT_TYPE = 'UNDO SEGMENT' THEN        /* LOG ERROR */        INSERT INTO sys.rbs_error (            SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME            FROM DBMS_RESUMABLE            WHERE SESSION_ID = cur_sid AND INSTANCE_ID = cur_inst         );        SELECT ERROR_MSG INTO error_txt FROM DBMS_RESUMABLE             WHERE SESSION_ID = cur_sid and INSTANCE_ID = cur_inst;          -- Send email to receipient via UTL_SMTP package         msg_body:='Subject: Space Error Occurred                     Space limit reached for undo segment ' || object_name ||                     on ' || TO_CHAR(SYSDATE, 'Month dd, YYYY, HH:MIam') ||                    '. Error message was ' || error_txt;          mail_conn := UTL_SMTP.OPEN_CONNECTION('localhost', 25);         UTL_SMTP.HELO(mail_conn, 'localhost');         UTL_SMTP.MAIL(mail_conn, 'sender@localhost');         UTL_SMTP.RCPT(mail_conn, 'recipient@localhost');         UTL_SMTP.DATA(mail_conn, msg_body);         UTL_SMTP.QUIT(mail_conn);          -- Abort the statement         DBMS_RESUMABLE.ABORT(cur_sid);     ELSE         -- Set timeout to 8 hours         DBMS_RESUMABLE.SET_TIMEOUT(28800);     END IF;      /* commit autonomous transaction */     COMMIT;    END;

pga_aggregate_target

v$pga_target_advice - estd_pga_cache_hit_percentage

v$pgastat - maximum PGA allocated

dba_hist_pgastat

from doc

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater

init parameter

pga_aggregate_target

hidden parameters

_pga_max_size

_smm_px_max_size

Tuning - check the hit , firstpass and multipass

--eliminate overalloc and maximize cache hit

SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024/1024) target_in_GB,

ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,

ESTD_OVERALLOC_COUNT

FROM v$pga_target_advice

SELECT    program,    pga_used_mem "PGA Used",    pga_alloc_mem "PGA Alloc",    pga_max_mem "PGA Max"   FROM v$process;

SELECT    low_optimal_size/1024 "Low (K)",    (high_optimal_size + 1)/1024 "High (K)",    optimal_executions "Optimal",    onepass_executions "1-Pass",    multipasses_executions ">1 Pass"   FROM v$sql_workarea_histogram  WHERE total_executions <> 0;

check multipass
SELECT    optimal_count "Optimal",    round(optimal_count * 100 / total,2) "Optimal %",    onepass_count "OnePass",    round(onepass_count * 100 / total,2) "Onepass %",    multipass_count "MultiPass",    round(multipass_count * 100 / total,2) "Multipass %"   FROM (       SELECT          DECODE (SUM(total_executions), 0, 1, SUM(total_executions)) total,          SUM(optimal_executions) optimal_count,          SUM(onepass_executions) onepass_count,          SUM(multipasses_executions) multipass_count         FROM v$sql_workarea_histogram -- Limits consideration of queries with LOW_OPTIMAL_SIZE limit <64k> 64*1024)

SELECT *   FROM (SELECT             workarea_address,             operation_type,             policy,             estimated_optimal_size           FROM v$sql_workarea       ORDER BY estimated_optimal_size)  WHERE ROWNUM <= 20;

ref-

http://www.dba-oracle.com/tips_pga_aggregate_target.htm

it is important to remember that automatic PGA memory management via PGA_AGGREGATE_TARGET only applies to dedicated server connections. Memory allocation forshared server connections is still managed by SORT_AREA_SIZE, HASH_AREA_SIZE, and so forth

http://www.databasejournal.com/features/oracle/article.php/10893_2109921_1/Activating-Monitoring-and-Tuning-Automatic-PGA-Memory-Management-via-PGAAGGREGATETARGET-Under-Oracle-9i.htm

tasklist -v in windows

works same as ps -ef ................

dbca inside dmz server

doing the regular display setup do not work when you are inside dmz server .

so I had to do this .

keep your exceed open. ssh using my loginid first with X11 tunnelling on .

as user ou1234>

find the display by env . it should be something like
DISPLAY=localhost:11.0

then you should see a .Xauthority file in user ou1234's home directory .

do a chmod on this file . better put this inside user ou1234's .profile

$ cat .profile
chmod 660 .Xauthority

and then su as the oracle user

su - oracle

and then export DISPLAY to the display as you found inside ou1234's env variable
As oracle user
$ export DISPLAY=localhost:11.0
$ export XAUTHORITY=/home/nrusip01/.Xauthority
$ xclock

now you are ready to do any gui actvity.

recovery scenario where too many archived log

check
v$log_history,

v$archived_log

V$RECOVERY_LOG -
[

Lists only the archived redo logs that Oracle needs to perform media recovery. It also includes the probable names of the files, using LOG_ARCHIVE_FORMAT.

Note: This view is only populated when recovery is required for a datafile. Hence, this view is not useful in the case of a planned recovery such as a user error.

]

If sufficient space is not available at the location indicated by the archiving destination initialization parameter, restore some or all of the required archived redo log files to an alternate location. Specify the location before or during media recovery using the LOGSOURCE parameter of the SET statement in SQL*Plus or the RECOVER ... FROM parameter of the ALTER DATABASE statement in SQL. For example, enter:

SET LOGSOURCE /disk2/temp   # set location using SET statement
DATABASE RECOVER FROM '/disk2/temp';  # set location in RECOVER statement itself

in rman
SET ARCHIVELOG DESTINATION TO  command

for temporary change
ARCHIVE LOG START '';

xming

a free alternative for exceed.

cygwin installation

mirrors.kernel.org(http) . make sure vim is checked.

recovery using online redo logs for a norarchivelog database

after the platform services did the san migration somehow one of the datafile became corrupted and while i try to open the database it complains. this database is a norachivelog database . so I just felt lucky on how it worked . the platform services had taken a o/s backup while the database was running the around 1am before they did the san migration.

Database mounted.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01201: file 7 header failed to write correctly

so at this point I asked them to restore the datafile 7 that they took from last night . even though this waas a production database but there were no activity during the night on this weekend .

so once they restored the file . I mounted

SQL> recover database using backup controlfile;
ORA-00279: change 2213499829905 generated at 02/21/2009 08:43:13 needed for
thread 1
ORA-00289: suggestion :
/rgw/ora_bkup/flash_recovery_area/RGWP20/archivelog/2009_02_22/o1_mf_1_10088_%u_
.arc
ORA-00280: change 2213499829905 for thread 1 is in sequence #10088


Specify log: {=suggested | filename | AUTO | CANCEL}
/rgw/u2/oradata/rgwp20/redo02.log
ORA-00279: change 2213502398486 generated at 02/21/2009 22:07:33 needed for
thread 1
ORA-00289: suggestion :
/rgw/ora_bkup/flash_recovery_area/RGWP20/archivelog/2009_02_22/o1_mf_1_10089_%u_
.arc
ORA-00280: change 2213502398486 for thread 1 is in sequence #10089
ORA-00278: log file '/rgw/u2/oradata/rgwp20/redo02.log' no longer needed for
this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/rgw/u2/oradata/rgwp20/redo03.log
Log applied.
Media recovery complete.

--what a relief it was for me . because if it would have any issue further then probably i would have to go through a export restore . then I did

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open



SQL> select * from v$recover_file;

no rows selected

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10087
Current log sequence 10089
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
0

SQL> alter database open resetlogs;

Database altered.

the database is back up and i am a happy man.

**** alert log .
alter database open resetlogs
Sun Feb 22 13:02:00 2009
RESETLOGS after complete recovery through change 2213530385545
Resetting resetlogs activation ID 1283279895 (0x4c7d4c17)
Sun Feb 22 13:02:05 2009
Setting recovery target incarnation to 2
Sun Feb 22 13:02:05 2009
Assigning activation ID 1331518248 (0x4f5d5b28)

***

but even though this production database was running in noarchivelog but still this database is a very highly visible database so I just did few homeowrk to make sure that everything is fine .

1-
SQL> begin
for r in( select owner,table_name from dba_tables where owner='RGWP20') loop
dbms_output.put_line( r.table_name );
dbms_stats.gather_table_stats( r.owner, r.table_name, estimate_percent => 5,
degree => 16, granularity => 'ALL', cascade => true,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1' );
end loop;
end; 2 3 4 5 6 7 8
9 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


2#took a export backup and compare the the backup before and now for
the tables /records

my lesson- a production database should always be in archivelog if you want life to be less stressful. but it is hard here to convince the application manager that we should convert it to archivelog. but will try again.

controlfile recovery for a db having few readonly tablespaces

here is the situation- The platform services took a cold backup of a UAT database before 3 weeks. and now the application group want that backup to be restored to the UAT . so I asked the platform services to go ahead and overwrite the existing UAT db related filesystems.

after they restored the cold backup I try to open the database and it complains . so I had to list out all the datafiles(including the readonly datafiles( though I dont know which datafiles are readonly from these 250+ datafiles )) . and recreate the controlfile . then i did a recover database using backup controlfile . and somehow it asks for the log file so I had to do a recovery using online redo logs . and then i opened the database with resetlogs . after that still confused about the read only tablespaces I did a status check in dba_tablespaces and I found that the data dictionary had all the readonly ts information and even though the controlfile was recreated but still the database was able to indentify properly .

-not that of a big deal but it was a good learning for me .
lesson- never keep the controlfile outside the datafile filesystem. becuase it is a lot of headache when you took a cold backup and then keep scratching your head that why you are not able to use the backup to restore it . always make sure that a cold backup backs up all the files .

The readonly tablespaces hit me back . when the user reported this error.

ORA-00376: file 71 cannot be read at this time

ORA-01111: name for data file 71 is unknown - rename to correct file

ORA-01110: data file 71: '/u1/app/oracle/product/9.2.0/dbs/MISSING00071'

so I had to to just do trial and error

select file_id,file_name from dba_data_files
where file_name like '%MISS%'

select * from v$datafile where status='OFFLINE'

SQL> alter tablespace PDB_IX_DS1 online;
alter tablespace PDB_IX_DS1 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 246 - see DBWR trace file
ORA-01111: name for data file 246 is unknown - rename to correct file
ORA-01110: data file 246: '/u1/app/oracle/product/9.2.0/dbs/MISSING00246'

doing a alter datbase backup controlfile to trace reported these errors

ALTER DATABASE RENAME FILE 'MISSING00227'
# WARNING! This command can not be completed because the file
# needs to be renamed to a real file name.

anyway I guess I was just lucky . somehow able to patch up the missing links. but I will make sure to take a alter database backup controfile to trace for any database specifically with readonly tablespace before I do a cold backup.

-

it seems oradebug dump helps to identify the tablespace/datafile relationship . thanks to Rijaj in oracle list . have to try.

vmware windows xp guest disk expansion

step -1 in host o/s
C:\ "C:\Program Files\VMware\VMware Workstation\v
mware-vdiskmanager" -x 10GB "C:\Virtual Machines\Windows XP Professional\Windows
XP Professional.vmdk"
Disk expansion completed successfully.

WARNING: If the virtual disk is partitioned, you must use a third-party
utility in the virtual machine to expand the size of the
partitions. For more information, see:
http://www.vmware.com/support/kb/enduser/std_adp.php?p_faqid=1647

step 2 - in guest o/s

used the home edition to resize the drive

http://www.partition-tool.com/easeus-partition-manager/help/resizing-and-moving-partition.htm

Dataguard switchover on 10g

After doing a dataguard switchover (not using dgmgrl ) these were few common cases you might come across.

1- switch_over status in primary was NOT ALLOWED

I found that the log shipping was stopped to the new standby and
select database_role,switchover_status from v$database;
PRIMARY , NOT ALLOWED .

It did not put any alerts in alert.log or any message on v$dataguard_status . I was totally clueless about what is going on here. then I found that the log_archive_dest_state_2 which was originally set as defer was still defer after the switchover . as soon as I turn it into enable at the new primary it started sending the logs.

2- temp files were recreated on the new primary.

Errors in file /u1/app/oracle/admin/dtrt/bdump/dtrt_dbw0_8307.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/rgwt/u6/oradata/dtrt/temp01.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
File 201 not verified due to error ORA-01157
Tue Dec 2 14:31:12 2008
Dictionary check complete
Tue Dec 2 14:31:12 2008
SMON: enabling tx recovery
Tue Dec 2 14:31:12 2008
Re-creating tempfile /rgwt/u6/oradata/dtrt/temp01.dbf

3-standby redo log on the new standby.

if you have configured the standby redo log on the old standby and then you did not configured on the old primary then you might as well create it

startup nomount;
alter database mount standby database;
alter database add standby logfile group 4 ('/rgwt/u2/oradata/dtrt/sredo41.rlog') size 50M;
alter database recover managed standby database disconnect from session;

then verify v$standby_log ,v$logfile

4- client tns .

i had to cut paste the old standby address to top .

dtrt_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = xt33db002)
(Port = 2483)
)
(ADDRESS =
(PROTOCOL = TCP)
(Host = xt33db003)
(Port = 1521)
)
)
(CONNECT_DATA = (SERVICE_NAME = dtrt)
)
)

Oracle OpenWorld 2008

First day at the Oracle Openworld .  

Mr Alex Gorbachov's  Clusterware presentation and  Mr Chintala's  presentation on  Dell IT  was very informative.

oracle 10g database characterset change

ORACLE DB version 10.2.0.3

step 1- run csminst.sql it will create a user to hold csscan result. which the csalter will use to convert

step 2-Use csscan to scan the database . make sure that in the output file csscan creates you dont see any of your application user table. if you see then you must have to export and truncate those tables.

csscan \"sys/@10GDB3 as sysdba \" FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=UTF8 LOG=WE8check_jul10

step 3 - run the csalter.plb to change the database characterset in startup restrict mode. needless to say you must have taken a full backup before you run csalter.plb

SQL> @?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validility...
begin converting system objects
12722 rows in table SYS.WRH$_SQL_PLAN are converted
80 rows in table SYS.METASTYLESHEET are converted
53 rows in table SYS.WRI$_ADV_ACTIONS are converted
79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
169 rows in table SYS.WRI$_ADV_OBJECTS are converted
900 rows in table SYS.WRH$_SQLTEXT are converted
38 rows in table SYS.WRI$_ADV_RATIONALE are converted
17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
4 rows in table SYS.RULE$ are converted
21 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted

PL/SQL procedure successfully completed.

Alter the database character set...
CSALTER operation completed, please restart database

PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.



step 4- do this
SQL> alter database national character set UTF8;

Database altered.

step 5- check output 1 select * from nls_database_parameters
2* where parameter like '%CHARACTERSET%'

SQL> /

PARAMETER VALUE
------------------------------ -----------------------------------
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET UTF8

it shows that we changed the database characterset as well as the national characterset to UTF8.

I had a nice lesson while doing this process. I first took the export dump of all the user tables having nclob data reported by csscan . and then i truncated the tables. However I again got into problem while doing csalter . it gave me the below message

Checking data validility...
Exceptional data found in scanner result

PL/SQL procedure successfully completed.

Checking or Converting phrase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.

again doing the csscan i found 1 more user tables. and while doing export again with the same dump file adding this table I miss I got a dump file which has 0 records for all the other table . not only that I overwrite the dumpfile name . I lost the data but luckily it is a test application otherwise it would have been very painful.

Lesson learned- make sure to take a full export . and never keep the same dump file name while doing a export in the situation as this.

Free Software Heroes.

I came across a interesting article . Here is the link

http://www.freesoftwaremagazine.com/columns/free_software_heroes_stallman_google_list_inspiring_individuals_who_made_everything_

3 node RAC on Enterprise Linux

3 node RAC on Enterprise Linux

ocr, voting disk, spfile on raw devices
and the db files on the block device

storage on MSA1000 ( modular smart array) with 2 fc and gigabit ethernet card .
public eth0 , private eth1 , vip eth0:1

-set up user equivalence.
-checking the configuration of hangcheck timer module
-check the rawdevice mapping exists. for ocr,voting and asm spfile e.g raw -qa
-check that the ASM disk are configured for you -these are block device so you upgrade oracle software from 10.2.0.1 to 10.2.0.3 - (right? )

- install 10.2.0.1 clusterware
-patch oracle clusterware to 10.2.0.3 (rolling clusterware patch)

-install db software in one home to be used by both asm and db.
-patch to 10.2.0.3

-Create cluster database with dbca
- choose RAC database
- choose create database
- select all nodes in the cluster
- select general purpose db
- choose a db name prod
- uncheck configure the db for enterprise manager
- specify sys as password for all account
- choose "Automatic storage management(ASM)" as the storage mechanism
- Configuring ASM - since ASM has not yet been configured dbca allows you to do this at this point of database creation
-specify sys as the asm SYS password
-specify our ASM spfile raw device as the spfile for ASM.
-click the create new button to create a new ASM diskgroup DATA
-create another ASM diskgroup FRA
- choose oracle managed file and the +DATA diskgroup
- select speficy flashrecovery area and +FRA diskgroup
- enable archivelog if you want

- on the database services screen create 2 services
"oltp" preferred node1,node2 available node3
"batch" preferred node3 , available node1,node2

- continue clicking default till it finish .

and now " my second RAC install is finished "

my First RAC experience

This week was amazing for me . on monday I did a 2 node RAC installation on solaris and on Tuesday I did a 3 node RAC on enterprise Linux .

2 node RAC10g on Solaris 10.

two v240 server with EMC clarion storage.
some routine work - user equivalence check , soft link to /usr/local/bin for ssh and scp . make sure your storage lun is outlined. then the public bge0 ,private bge1 ,vip bge0:1 address. (vip is configured by vipca at the end of clusterware. ..)
1- clusterware installation

2- ASM installation on a asm home
2.1 - As part of asm installation on the select configuration option - choose configure Automatic Storage management(ASM)
that leads you to define diskgroup . selected the candidate raw partitions . and the rest follows

3- database installation on a separate db home
3.1 - on the select configuration option choose only the Install database software only .

4- Create the cluster database
4.1 - run dbca- choose Oracle Real Application Clusters database -
on the Select the operation that you want to perform page
choose Configure Automatic Storage Management
on the Node select choose select all
enter the ASM sys user password
on the ASM disk groups it will allow you to create another FRA diskgroup if you want
select the raw parttions that you want to assigned to FRA
you should see for all the diskgroups the state shows mounted(num nodes/num nodes )
Click Finish and it ask you "Do you want to perform another operation ?" Choose Yes
Back to the operations screen where you select database .
on the node selection choose select all...
defined a db template- General purpose
on the management options enter the database name and the sid prefix.
then on step 6 of 16 use the same password for all account
On the storage option screen . select Automatic storage management -
select the DATA diskgroup to be used for the db storage . uncheck FRA
On the database files location screen . select oracle-managed file
Select the specify flash recovery area check box and enter FRA diskgroup to be used for fla
select some sample schema if you please
Create database service if you like ( to differentiate based on usage , batch for 1 node,oltp for both
then click , click
on the Creation option page select the create database option .
then click click click and you got a window
Starting cluster database "DEVL" and its instances "DEVL1,DEVL2 " in progress. ...
and now < I am done with my First RAC >

rman - noarchivelog db - recover noredo

I encountered few issues while restoring a rman backup when the db is running in noarchivelog mode (i think the online redolog's availability has some influence on the rman backup restore strategy ) . so to be on a safeside I implemented the backup strategy with incremental backup for noarchivelog db. this one I tested on 10.2.0.2

Here is the backup script

============================
script rman_noarch_backup.rcv # ##########
SHUTDOWN IMMEDIATE;
STARTUP FORCE DBA;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
BACKUP
COPIES 1
INCREMENTAL LEVEL 0
MAXSETSIZE 1G
DATABASE
INCLUDE CURRENT CONTROLFILE
TAG 'BACKUP_1'
;
alter database open;
#########################################

and the restore operation is as below

vmtest:/export/home/oracle$ rman target /
RMAN> SET DBID 1177027667
executing command: SET DBID
RMAN> startup nomount;

RMAN> restore controlfile from '/export/home/oracle/flash_recovery_area/ORCL/backupset/2008_03_13/o1_mf_ncnn0_BACKUP_1_3xmdomv3_.bkp';

RMAN> alter database mount;

RMAN> run{
restore database;
recover database noredo;
}

RMAN> alter database open resetlogs;

database opened

Generate schema creation script using datapump

expdp system/manager directory=tmp_dir dumpfile=a.dmp content=metadata_only schemas=system
impdp system/manager directory=tmp_dir dumpfile=a.dmp sqlfile=p4.sql

moving windows o/s into a ubuntu vmware guest o/s

software needed to move Windows XP
on a dell latitude D600/D620

my laptop came with windows xp . and my objective is to move away from windows XP to ubuntu .
in order to do that . I need to do 2 things
1- install ubuntu in a separate partition
2- using vmware converter take a backup of existing windows xp and then install vmware server 1.0 in ubuntu and open the windows xp .vmx as a guest vm.

I did a defragment on the 80G which freed 50G . and
1- partition magic - shrink your windows o/s partition and then the freed space.
2- vmware converter
3-ubuntu 7.10 - d600 is 32 bit and d620 is 64 bit . so you need appropriate install CD.
while installing careful at partitioning stage . not to destroy the existing windows installation
- manual
freespace - 54g
swap - primary - 1g
/ - primary - 3gb
/usr -logical - 3g
/tmp - 3g
/home - rest free space.
8. vmware server 1.0

Installing perl in solaris 8

As root

install ActivePerl-5.8 -
pkgadd -d ActivePerl-5.8.8.822-sun4-solaris-2.6-cc-280952.pkg

then link perl to /usr/bin

need to install

DBI-1.59.tar
DBD-Oracle-1.17.tar

in order to install DBI-1.59


you have to install gcc,cc,make .

to install gcc you have to use pkg-get , pkgadd ....

then link gcc ,
to install make ./reconfigure and link make to /usr/bin/make

bash-2.05$ gcc -v
Reading specs from /opt/csw/gcc3/lib/gcc/sparc-sun-solaris2.8/3.4.5/specs
Configured with: ../sources/gcc-3.4.5/configure --prefix=/opt/csw/gcc3 --with-local-prefix=/opt/csw --without-gnu-as --with-as=/usr/ccs/bin/as --without-gnu-ld --with-ld=/usr/ccs/bin/ld --enable-threads=posix --enable-shared --enable-multilib --enable-nls --with-included-gettext --with-libiconv-prefix=/opt/csw --with-x --enable-java-awt=xlib --enable-languages=all
Thread model: posix
gcc version 3.4.5
bash-2.05$ make -v
GNU Make 3.81
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE.

This program built for sparc-sun-solaris2.9
as root
set your oracle environments

# ORACLE_BASE=/ofs/app/oracle/product
# export ORACLE_BASE
# ORACLE_HOME=$ORACLE_BASE/10.2.0.1
# export ORACLE_HOME
# ORACLE_SID=testasm
# export ORACLE_SID
# LD_LIBRARY_PATH=$ORACLE_HOME/lib32
# export LD_LIBRARY_PATH

then regenerate the makefile for DBI-1.59
perl Makefile.PL
make

to install DBD-Oracle-1.17
perl Makefile.PL
make
make test
make install

bash-2.05$ perl -v

This is perl, v5.8.8 built for sun4-solaris-thread-multi
(with 11 registered patches, see perl -V for more detail)

rman online backup with exclude undo tablespace

Never try to disaster recover from a backup which was taken online with exclude undo.

the experience was pretty bad while trying to rebuild a test system from production . tried all

_ALLOW_RESETLOGS_CORRUPTION = TRUE
_CORRUPTED_ROLLBACK_SEGMENTS =( )

In UNIX you can issue this command to get the undo segment names:

$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u

recover database using backup controlfile until cancel

alter database open resetlogs

which through the bootstrap error

SMON: enabling cache recovery
Fri Jan 18 12:59:08 2008
Errors in file /ofs/app/oracle/admin/sfods/udump/sfods_ora_29046.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/datastore/sfods03/undotbs01.dbf'
Fri Jan 18 12:59:08 2008
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Fri Jan 18 12:59:08 2008

even tried to adjust_scn till level 7 but no help

ALTER SESSION SET EVENTS '10015 TRACE NAME ADJUST_SCN LEVEL 1';

So a lesson learned . no db full backup from now on using exclude undo.

NO MORE EXCLUDE UNDO -

calculate exp dump size for oracle db

Thanks to Jared-

select sum(bytes)/(1024*1024*1024) bytes
from dba_segments
where segment_type not in (
'CACHE',
'ROLLBACK',
'TYPE2',
'UNDO',
'INDEX'
)

10% variation

df shows 100% on a empty filesystem

In one of my oracle 10gR2 db on sunos8 server even though the archivelog filesystem was empty
but still df shows that it is 100% . so I had to do the below with the help of oracle-l

with Lisa Brandenburg's advice I did a
alter system set log_archive_max_processes=1 . which cleared arch1 process from
/ofs/archlogs01 . but still the arch0 process was active on this . which I could not kill but that brought down the filesystem from 100% to 52%.

I had to use fuser,

then on Tanel Podar's Advice I changed the archivelog moving script to check for fuser

#!/usr/bin/bash


ARCH_DEST=/ofs/archlogs02/oracle/PRRS
BACKUP_DEST=/obackup/oracle/PRRS/archlogs
for i in $ARCH_DEST/*.log ; do
echo "testing fuser" `fuser $i`
if [ -t `fuser $i` ] ; then
mv $i $BACKUP_DEST
echo "File $i is not open"
else
echo "`date` File $i still open..."
fi

done

exit 0

I had to use lsof to check the active process on the server.

opmnctl hangs while trying to start the grid control

after switching the O/S from suse 9 to RH 5 when I try to start the opmnctl it just hanged . and then upon investigation around the log file I found that it do not find few .so files. so just installing those dependent rpms the grid worked fine .

TEMP space error

dealing with ORA-01652 : unable to extend temp segment by 128 in tablespace FCPTEST


alter system set events '1652 trace name errorstack level 1';

ALTER SESSION SET EVENTS '1652 trace name errorstack level 1 ';


or
alter system set events '1652 trace name errorstack level 1 ';
alter system set events '1652 trace name errorstack off ';


To deactivate:

ALTER SESSION SET EVENTS '1652 trace name context off';
ALTER SYSTEM SET EVENTS '1652 trace name context off';


monitoring sort space by statement-
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;


alter database tempfile '/metrix/mtrx01/oracle/mtrx/temp01.dbf' resize 8G;

monitoring sort space by session-
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid,
S.module, S.program,
SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

monitoring temporary segments
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


1. Obtain the Oracle process identifier or the Operating System process
identifier (SPID) from v$process:

> sqlplus /nolog
SQL> connect / as sysdba
SQL> select pid, spid, username from v$process;

PID SPID USERNAME
---- ----- --------
8 25807 oracle


2. Attach to the process using ORADEBUG.

Using the Oracle process identifier:

SQL> oradebug setorapid 8

Unix process pid: 25807, image: oracleV804


- or -


Using the Operating System process identifier:

SQL> oradebug setospid 25807

Oracle pid: 8, Unix process pid: 25807, image: oracleV804


3. Turn on SQL Trace for the session.

SQL> oradebug event 10046 trace name context forever, level 12

Statement processed.


4. Turn off the SQL trace for the session.


SQL> oradebug event 10046 trace name context off


5. Format trace file using TKPROF.

grid control agent refresh

1. Stop the agent
/bin/emctl stop agent

2. Clean start the agent:

2a Delete current upload and state files

rm -r /sysman/emd/state/*
rm -r /sysman/emd/collection/*
rm -r /sysman/emd/upload/*
rm /sysman/emd/lastupld.xml
rm /sysman/emd/agntstmp.txt
rm /sysman/emd/blackouts.xml
rm /sysman/emd/protocol.ini

2b Start the agent

/bin/emctl start agent

2c Issue an agent clearstate from the agent home

/bin/emctl clearstate agent

3. Resecure the agent (if the agent was secured in the first place).

/bin/emctl secure agent

4. Force an upload to the OMS

/bin/emctl upload

5. Wait 2 minutes.


/metrix/app/oracle/product/agent10g/bin/emctl stop agent


rm -r /metrix/app/oracle/product/agent10g/sysman/emd/state/*
rm -r /metrix/app/oracle/product/agent10g/sysman/emd/collection/*
rm -r /metrix/app/oracle/product/agent10g/sysman/emd/upload/*
rm /metrix/app/oracle/product/agent10g/sysman/emd/lastupld.xml
rm /metrix/app/oracle/product/agent10g/sysman/emd/agntstmp.txt
rm /metrix/app/oracle/product/agent10g/sysman/emd/blackouts.xml
rm /metrix/app/oracle/product/agent10g/sysman/emd/protocol.ini


/metrix/app/oracle/product/agent10g/bin/emctl start agent

/metrix/app/oracle/product/agent10g/bin/emctl clearstate agent

/metrix/app/oracle/product/agent10g/bin/emctl secure agent oracle10g

/metrix/app/oracle/product/agent10g/bin/emctl upload

rman to restore archivelog from backupset and synch a standby db

To list the backup sets of the archive logs. Where or which
sets we have the archive logs.

RMAN>
list backup of archivelog logseq 5594;

BP Key: 1292331 Status: AVAILABLE Compressed: NO Tag: MANUAL_HOT_BKP_AL_ON_1213071620
Piece Name: /obackup/oracle/mtrx/hot/q4j3gsbs_1_1

List of Archived Logs in backup set 1292328
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 5594 21533054412 13-DEC-07 21533150295 13-DEC-07

cd /obackup/oracle/mtrx/hot/
ls -l | grep -v mtrx

will show all the files and rsync to the standby server:

for x in `ls -l | grep -v mtrx`
do
rsync -avvzrlpogt --stats --progress --rsync-path=/opt/csw/bin/rsync \
$x -e ssh oracle@asb-con-dbs-001:/obackup/oracle/mtrx/hot/
done

NOW on the standby server: asb-con-dbs-001

RMAN> list backup of archivelog sequence between 5594 and 5884;

Allow Rman to recover the database it will restore the archive
logs in the original place it picked them up.
you can verify by using ls -l /metrix/archlogs01/oracle/

Now to remove the old archive logs from standby db using rman:
DELETE noprompt ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE disk;

rman compressed backupset

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET

configure device type disk clear;

database recovery

Dec 13 after work hour 7pm-
I had this task to move the datafile from one filesystem to another filesystem and change the names.this production db was not so big (only around 75gb+ ) so I started moving the file one by one . and it was getting late so my friend coworker Howard volunteered to move few files from the bottom while I am doing from the top of the list. but while moving he moved one of the file USERS02.sun to USERS01.dbf . so here the unforeseen thing happened and we just lost one of our datafile USERS01.dbf .

and it has been not a while since we moved our backup strategy for this db to rman so as it was supposed to be on once a week hot full db backup and daily archivelog backup so i thought we might get it recovered and so I checked then the latest I could see was the Nov 28 backup which is almost 2 week old . so it was like I felt that we both might loose our job if we could not get this db up and running.

well I start to do a rman restore for the datafile and it took like 2 hour to restore from the compressed backuppiece of Nove 28 backup. and now comes the hard part to recover it . as I found that we need almost 1000+ archive log file to do a complete recover. this db was running on a 100gb SAN. so space also was a critical . so when I started rman recover it verified that all the log files are in disk ( archivelog backuppiece) . and it started restoring the log files from backuppiece which was in another /obackup file system to the 100gb /datastore . so I had to wait and watch as it restore and apply the archivelog and move back into the /obackup. and I also found that there was a 10gb temp file being used so we moved it to /obackup while the restore log process is going on . and it lasted till 3am in the morning and we were done with recovering the db and it was up and running . we both just thanked god and left for home. and thanks to rman which saved our job .

nov 28- rman hot backup

there was a cold backup on dec 4 which we could not find immediatly accessible

till dec 12 - rman log backup set

startup mount

restore datafile 25
recover datafile 25

alter database open .


so in this process here are my learning.

1- before i did the the rearranging of files I should have checked the backup available for the db
2- mv is dangerous it is equally as dangerous as rm.
3- size of a production db doesn't matter