Working on an Oracle Cost Reduction presentation

http://docs.google.com/EmbedSlideshow?docid="dcvphs7f_0hqt4mwhj

Granting SYSDBA adds an entry to the password file

SQL> grant sysdba to robert;

Grant succeeded.

SQL> connect robert as sysdba
Enter password:
Connected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

[t-dev-oranode-50.cmc.local:dv1]$ diff orapwapex.bak orapwapex
< Z[\]ORACLE Remote Password fileNTERNA232A4CC5194A53EESYSDCB748A5BC5390F2
---
> Z[\]ORACLE Remote Password fileNTERNA232A4CC5194A53EESYSDCB748A5BC5390F2ROBERT6D938DC70DAA0F72

Install Apex on 10.2.0.3

Install 10.2.0.1 binaries
Install Companion CD
Install 10.2.0.3 patchset
Create database using dbca

# Download Apex zip file and install full Apex dev environment

@apexins sysaux sysaux temp /i/

# change admin password

@apxchpwd

@apex_epg_config /opt/oracle/product/10.2.0/apex

alter user anonymous account unlock;

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

EXEC DBMS_XDB.SETHTTPPORT(8080);


When Oracle Application Express installs it creates three new database accounts:

* FLOWS_030100 - The account that owns the Oracle Application Express schema and metadata.
* FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.
* APEX_PUBLIC_USER - The minimally privileged account used for Oracle Application Express configuration with Oracle HTTP Server and mod_plsql.

http://host:port/apex/apex_admin admin/password1

Create Workspace and map to schema

http://host:port/apex system/password admin/password1

REDO is generated on global temporary tables.

In this test redo on global temporary table = 4% of permanent table.

SQL> insert into perm_table select * from dba_source where rownum < 100000;

99999 rows created.


Statistics
----------------------------------------------------------
1822 recursive calls
27577 db block gets
9419 consistent gets
2 physical reads
20110532 redo size
832 bytes sent via SQL*Net to client
765 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
99999 rows processed

SQL> insert into temp_table select * from dba_source where rownum < 100000;

99999 rows created.


Statistics
----------------------------------------------------------
27 recursive calls
16980 db block gets
6319 consistent gets
0 physical reads
814600 redo size
832 bytes sent via SQL*Net to client
765 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
99999 rows processed

ORA-01666: controlfile is for a standby database

# PROBLEM :-
# unable to mount an Oracle 9i standby database

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01666: controlfile is for a standby database

# SOLUTION :-

SQL> alter database mount standby database;

Database altered.

ORA-09968: scumnt: unable to lock file

# PROBLEM :-
# unable to mount Oracle 9i standby database using the same ORACLE_HOME as the primary

alter database mount
Mon Oct 27 18:53:03 2008
scumnt: failed to lock /opt/oracle/product/9.2.0.4/dbs/lkDB1 exclusive
Mon Oct 27 18:53:03 2008
ORA-09968: scumnt: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 32061

# SOLUTION :-
# add the following to init.ora and restart
lock_name_space=db2

Database Vault Example

# Using Database Vault to stop SYS from selecting from a table

1. login to the Database Vault console
2. create a new Realm, and add table robert.test as a Realm Secured Object
3. check who has priveleges on the object

SQL> connect robert/password
Connected.
SQL> grant select on test to robert2;

Grant succeeded.

SQL> connect robert2/password
Connected.
SQL> select * from robert.test;

ID
----------
1

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select * from robert.test;
select * from robert.test
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> connect dvowner
Enter password:
Connected.
SQL> select * from robert.test;

ID
----------
1

Debug CRS

# turn on debug
sudo crsctl debug log res "ora.p01dwh.pl01dwh2.inst:5"
sudo crsctl debug log crs CRSRES:5
sudo crsctl debug log crs CRSAPP:5
sudo crsctl debug log crs CRSEVT:5

# run test
srvctl start instance -d p01dwh -i pl01dwh2

# turn off debug

sudo crsctl debug log res "ora.p01dwh.pl01dwh2.inst:0"
sudo crsctl debug log crs CRSRES:0
sudo crsctl debug log crs CRSAPP:0
sudo crsctl debug log crs CRSEVT:0

Database Vault

# Download database vault for the database ORACLE_HOME version.

unset LANG
./runInstaller

#It install Label Security and will ask which database to install in, and will register itself with the OEM dbconsole.

http://t-dev-oranode-50.cmc.local:1158/dva

SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

select username from dba_users where username = 'DVSYS';

select name from REALM_T$;

# INSTALL DATABASE VAULT (run by runInstaller)
/opt/oracle/product/10.2.0/dv/bin/dvca -action option -oh /opt/oracle/product/10.2.0/dv -s_path /tmp -logfile /opt/oracle/product/10.2.0/dv/cfgtoollogs/dvca_install.log -owner_account dvowner -owner_passwd password -jdbc_str jdbc:oracle:oci:@dv1 -sys_passwd password -lockout -nodecrypt -silent

# ENABLE DATABASE VAULT :-
dvca -action enable -service dv -sys_passwd password -owner_account dvowner -owner_passwd dvowner#1 -logfile dvenable.log -nodecrypt

emctl stop dbconsole

sqlplus "sys / as sysoper"
shutdown immediate;
exit

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_on
cd $ORACLE_HOME/bin
relink oracle

sqlplus "sys / as sysoper"
startup
exit

# DISABLE DATABASE VAULT

emctl stop dbconsole

sqlplus "SYS / AS SYSOPER"
shutdown immediate;
exit

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk dv_off
cd $ORACLE_HOME/bin
relink oracle

sqlplus "SYS / AS SYSOPER"
startup
exit

CONNECT SYS / AS SYSDBA
ALTER TRIGGER DVSYS.DV_BEFORE_DDL_TRG DISABLE;
ALTER TRIGGER DVSYS.DV_AFTER_DDL_TRG DISABLE;

dvca -action disable
-service service_name
-instance Oracle_instance_name
-dbname database_name
-sys_passwd SYS_password
-owner_account DV_owner_account_name
-owner_passwd DV_owner_account_password
[-logfile ./dvca.log]
[-nodecrypt]
[-racnode node]

• -silent is the option to run in command line mode.
  

• -nodecrypt is the option to read plaintext passwords.

• -lockout is the flag to use to disable SYSDBA operating system authentication.

# Recreate password file (note the nosysdba=n)
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mysyspassword force=y nosysdba=n

Logon Trigger to trace a specific user

CREATE OR REPLACE TRIGGER trace_trigger AFTER LOGON ON DATABASE
WHEN (user='APEX_ADMINISTRATOR')
BEGIN
execute immediate 'alter session set sql_trace=true';
END;
/

Check which blocks are used in a table

select distinct DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)
from ETSDYN.ETPPRICE;

OWB reports startup errors after database server move

UPDATE OWBRT_SYS.OWBRTPS
  SET   value=''       
  WHERE key =';

UPDATE OWB10G_RUNTIME.WB_RT_SERVICE_NODES
  SET    host = ''
       , port = 
       , service_name = '
       , server_side_home = '
  WHERE  node_id = 1;

UPDATE OWB10G_RUNTIME.WB_RT_STORE_PARAMETERS
  SET    parameter_value = ''
  WHERE  parameter_name  = 'Service';

UPDATE OWB10G_RUNTIME.WB_RT_STORE_PARAMETERS
  SET    parameter_value = ''
  WHERE  parameter_name  = 'Host';

UPDATE OWB10G_RUNTIME.WB_RT_STORE_PARAMETERS
  SET    parameter_value = ''
  WHERE  parameter_name  = 'Port';

OWB reports ORA-01882 when connecting to Oracle 9i databases

vi $ORACLE_HOME/owb/bin/unix/run_service.sh

# add -Duser.timezone="+00:00" as follows

$JAVAPATH/bin/java -Xmx768M -Duser.timezone="+00:00" -Djava.awt.headless=true -DORACLE_HOME="${RTHOME}" -
DOCM_HOME="${RTHOME}" -DOCM_ORACLE_HOME="${RTHOME}" $CLASSPATH_LAUNCHER oracle.wh.runtime.platform.servic
e.Service ${STARTUP_TYPE} ${NODEID} ${RTUSER} ${HOST} ${PORT} ${SERVICE}

#Restart the OWB services using stop_service.sql/start_service.sql.

Memory leak on RAC ONS process due to port conflict

After CRS is upgraded to 10.2.0.4 the ONS ports are changed in ons.config, but not changed in OCR

Check if the ports match in OCR and ons.config

ocrdump -stdout -keyname DATABASE.ONS_HOSTS
onsctl ping
more $ORACLE_HOME/opmn/conf/ons.config

catalog all archivelogs in a directory

rman
connect target /
catalog start with '+ASM_ORADATA12/P01CFD/ARCHIVELOG/'

DBMS_REPAIR.SEGMENT_FIX_STATUS

If you suspect issues with space allocation in ASSM tablespaces :-

exec dbms_repair.segment_fix_status('ETSDYN','ETPPRICE');

DBMS_SPACE.SPACE_USAGE

# For ASSM tablespaces, check space usage :-

create or replace procedure show_space (a_seg_owner IN VARCHAR2, a_seg_name IN VARCHAR2, a_seg_type IN VARCHAR2) is
unformatted_blocks NUMBER;
unformatted_bytes NUMBER;
fs1_blocks NUMBER;
fs1_bytes NUMBER;
fs2_blocks NUMBER;
fs2_bytes NUMBER;
fs3_blocks NUMBER;
fs3_bytes NUMBER;
fs4_blocks NUMBER;
fs4_bytes NUMBER;
full_blocks NUMBER;
full_bytes NUMBER;
BEGIN
DBMS_SPACE.SPACE_USAGE(
segment_owner => a_seg_owner,
segment_name => a_seg_name,
segment_type => a_seg_type,
unformatted_blocks => unformatted_blocks,
unformatted_bytes => unformatted_bytes,
fs1_blocks => fs1_blocks,
fs1_bytes => fs1_bytes,
fs2_blocks => fs2_blocks,
fs2_bytes => fs2_bytes,
fs3_blocks => fs3_blocks,
fs3_bytes => fs3_bytes,
fs4_blocks => fs4_blocks,
fs4_bytes => fs4_bytes,
full_blocks => full_blocks,
full_bytes => full_bytes,
partition_name=>NULL);

dbms_output.put_line('Space usage report for ' || a_seg_type || ': ' ||
a_seg_owner || '.' || a_seg_name);
dbms_output.put_line('***************************************');
dbms_output.put_line('Unformatted : '||unformatted_blocks||' blocks, '||
unformatted_bytes/1024/1024||' MB');
dbms_output.put_line(' 0% < Free Space < 25% : '||fs1_blocks||' blocks, '||
fs1_bytes/1024/1024||' MB');
dbms_output.put_line('25% < Free Space < 50% : '||fs2_blocks||' blocks, '||
fs2_bytes/1024/1024||' MB');
dbms_output.put_line('50% < Free Space < 75% : '||fs3_blocks||' blocks, '||
fs3_bytes/1024/1024||' MB');
dbms_output.put_line('75% < Free Space < 100% : '||fs4_blocks||' blocks, '||
fs4_bytes/1024/1024||' MB');
dbms_output.put_line('Full blocks : '||full_blocks||' blocks, '||
full_bytes/1024/1024||' MB');
END;
/

SQL> set serveroutput on
SQL> exec show_space ('ETSDYN','ETPPRICE','TABLE');
Space usage report for TABLE: ETSDYN.ETPPRICE
***************************************
Unformatted : 0 blocks, 0 MB
0% < Free Space < 25% : 16 blocks, .125 MB
25% < Free Space < 50% : 3 blocks, .0234375 MB
50% < Free Space < 75% : 4 blocks, .03125 MB
75% < Free Space < 100% : 2004570 blocks, 15660.703125 MB
Full blocks : 0 blocks, 0 MB

PL/SQL procedure successfully completed.

OEM error oracle.sysman.emSDK.emd.comm.OperationException: could not write to targets.xml.bak file

# When changing configuration in OEM :-

oracle.sysman.emSDK.emd.comm.OperationException: could not write to targets.xml.bak file

# In emagent.trc many filesystem errors

emagent.trc.4:2008-10-10 01:11:41,951 Thread-1114 ERROR util.files: ERROR: nmeufos_new: failed in lfiopn on file: /opt/oracle/product/10.2.0/agent10g/sysman/emd/agntstmp.txt.error = 24 (Too many open files)
emagent.trc.4:2008-10-10 01:11:41,953 Thread-1114 ERROR util.files: ERROR: nmeufos_new: failed in lfiopn on file: /opt/oracle/product/10.2.0/agent10g/sysman/emd/agntstmp.txt.error = 24 (Too many open files)

# Caused by filesystem problems. Shutdown all Oracle processes, umount the filesystem, and run an fsck.

Using agentca to recreate the OEM agent targets.xml

emctl start agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ...... failed.
Failed to initialize the Target Manager.

# Found that targets.xml file was empty and recreated using :-
# Single node :-
agentca -d -f

# Cluster :-
agentca -c PDCRS03 -d -f

OEM cannot monitor second RAC instance - ORA-12519

Failed to connect to database instance: ORA-12519: TNS:no appropriate service handler found (DBD ERROR: OCIServerAttach).

Modify the SID definition for the second instance in the "Database Configuration Page":

FROM :-

racsid2

TO :-

racsid2)(INSTANCE_ROLE=ANY

emctl clearstate agent
emctl upload agent
emctl status agent

See the following Metalink Note :-
Note.335046.1 Problem Database Monitoring ORA-12519 Returned When Monitoring Secondary Instance Of Rac Database

Debugging OEM issues with EMDIAG

+ Download the EMDiagkit from the Note 421053.1 EMDiagkit Download and Master Index

Set the ORACLE_HOME environment for OMS on the OEM console server.
mkdir $ORACLE_HOME/emdiag
cp emdiag20080902.zip $ORACLE_HOME/emdiag
cd $ORACLE_HOME/emdiag
unzip emdiag20080902.zip
cd bin
chmod u+x repvfy
./repvfy -usr SYSMAN -pwd password install


+ Install it as per instructions in Note 421499.1 How to Install - Deinstall
+ Set the environment as per Note 421586.1 Environment Variables
+Provide the output of the below test:

./repvfy -details

Using emcli to connect to OEM

export JAVA_HOME=/opt/oracle/product/10.2.0/oms10g/jdk
export PATH=$PATH:$JAVA_HOME/bin

cd /opt/oracle/product/10.2.0/oms10g/sysman/jlib
java -jar emclikit.jar client -install_dir=/home/oradba/scripts

emcli setup -url="https://test-oemnode-10:1159/em -username oradba -dir /home/oracle/scripts -trustall -novalidate

emcli get_targets
emcli get_jobs

Check which Oracle instance you are connected to

select host_name, instance_name from v$instance;

Get oracle client IP address from AUD$

select distinct
terminal||'^'||
substr(substr(comment$text,instr(comment$text,'HOST=')+5,100),1,instr(substr(comment$text,instr(comment$text,'HOST=')+5,100),')')-1)
from sys.aud$;

Kill all Oracle sessions

set pagesize 0
spool kill_all.sql
select 'alter system kill session '''||sess.sid||', '||sess.serial#||''';' from v$session sess where username = 'PL_SCHEMA';
spool off
spool kill_all.log
@kill_all.sql
spool off


ps -ef |grep oraclepl01cdy1 | grep NO | grep -v grep | awk '{print $2}' | xargs kill -9

RMAN restore datafile

rman
connect target /
catalog backuppiece '/bie_oraexp/rman_P01DIS_20081002_702_1';
run{
allocate channel disk1 device type disk;
set newname for datafile 17 to '/sl02dis/oraarch1/SL02DIS/datafile/o1_mf_disdwh_d_4g9yqvjx_.dbf';
restore datafile 17;
}

Enable Block Change Tracking

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+ASM_ORADATA41';

select * from v$block_change_tracking;

Oracle export with query (where clause)

export NLS_LANG=american_america.WE8ISO8859P1
exp user/password file=SUBSCRIPTIONGROUPS.dmp tables=SUBSCRIPTIONGROUPS query=\"where system_profile_id \= 1770\"