Dicas ORACLE DBA

Error: oracle@localhost$ /etc/init.d/oracleasm createdisk DISCO1 /dev/sdb1 Marking disk "/dev/sdb1" as an ASM disk: asmtool: Device "/dev/sdb1" is already labeled for ASM disk ""     [FAILED] oracle@localhost$ /etc/init.d/oracleasm deletedisk DISCO1 Removing ASM disk "DISCO1"                                 [FAILED] Solution: oracle@localhost$ dd if=/dev/zero of=/dev/sdb1 bs=1024 count=100 100+0 records in 100+0 records out oracle@localhost$ /etc/init.d/oracleasm createdisk DISCO1 /dev/sdb1 Marking disk "/dev/sdb1" as an ASM disk:                  [ OK ]

$sqlplus / as sysdba SQL> alter system set control_files='+DATA' scope=spfile; SQL> alter system set db_create_file_dest='+DATA' scope=spfile; SQL> alter system set db_recovery_file_dest='+DATA' scope=spfile; [oracle@database ~]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 18 00:06:29 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. connected to target database: ORCL (not mounted) RMAN> restore controlfile from '/u01/app/oracle/oradata/orcl/control01.ctl'; Starting restore at 18-OCT-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=10 device type=DISK channel ORA_DISK_1: copied control file copy output file name=+DATA/orcl/controlfile/current.256.764813197 Finished restore at 18-OCT-11 RMAN> exit Recovery Manager complete. [oracle@database ~]$ sqlplus / as sysdba SQL> alter databas

Qual a diferença entre startup e startup upgrade: ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY; ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY; ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY; ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY; ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY; ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY; ALTER SYSTEM SET recyclebin=‘OFF’ DEFERRED SCOPE=MEMORY;

First Configure public Yum Repository: http://public-yum.oracle.com/ Run oracle-validated: [root@localhost yum.repos.d]# yum install oracle-validated Loading "security" plugin Setting up Install Process Parsing package install arguments Resolving Dependencies --> Running transaction check ---> Package oracle-validated.x86_64 0:1.1.0-14.el5 set to be updated filelists.xml.gz          100% |=========================| 3.1 kB    00:00 filelists.xml.gz          100% |=========================| 2.9 MB    00:10 filelists.xml.gz          100% |=========================| 2.7 MB    00:15 filelists.xml.gz          100% |=========================| 3.2 MB    00:18 filelists.xml.gz          100% |=========================| 2.6 MB    00:14 filelists.xml.gz          100% |=========================| 3.2 MB    00:18 filelists.xml.gz          100% |=========================| 3.0 MB    00:14 filelists.xml.gz          100% |=========================| 2.9 MB    00:14 file

DATAFILE_BLOCKS The number of data blocks in the datafile BLOCKS The number of data blocks written to the backup BLOCK_SIZE The size of the data blocks in bytes So BLOCKS * BLOCK_SIZE will give the number of bytes written for this particular datafile and hence the size of the backup piece may be calculated. SQL> select bs_key, sum(blocks*block_size) from rc_backup_datafile group by bs_key; If you do not use catalog (controlfile): SQL>select bs_key, sum(blocks*block_size) from v$backup_datafile group by bs_key;

BEGIN  DBMS_AUTO_TASK_ADMIN.DISABLE(  client_name => 'auto optimizer stats collection',  operation => NULL,  window_name => NULL);  END;  / to verify: SQL> select client_name,status from Dba_Autotask_Client;  CLIENT_NAME                        STATUS ---------------------------------- -------- auto optimizer stats collection    DISABLED auto space advisor                 ENABLED sql tuning advisor                 ENABLED

1. Using the adctrl utility, shutdown the workers. a. adctrl b. Select option 3 "Tell worker to shutdown/quit" 2. Backup the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema a. sqlplus applsys/ b. create table fnd_Install_processes_back as select * from fnd_Install_processes; c. The 2 tables should have the same number of records. select count(*) from fnd_Install_processes_back; select count(*) from fnd_Install_processes; 3. Backup the AD_DEFERRED_JOBS table. a. sqlplus applsys/ b. create table AD_DEFERRED_JOBS_backas select * from AD_DEFERRED_JOBS; c. The 2 tables should have the same number of records. select count(*) from AD_DEFERRED_JOBS_back; select count(*) from AD_DEFERRED_JOBS; 4. Backup the .rf9 files located in $APPL_TOP/admin//restart directory. At this point, the adpatch session should have ended and the cursor should be back at the Unix prompt. a. cd $APPL_TOP/admin/ b. mv restart restart_back c. mkdir restart 5. Drop the F

To find Symbolic Links find . -type l Show all links, it does nto use the cool flashing RED that BASH can do to show a  broken links. find . -type l | (while read FN ; do test -e "$FN" || ls -ld "$FN"; done)

1) edit context file (vi $CONTEXT_FILE): find and change the variables: s_appldcp to ON s_applcsf to shared file system s_appltmp to valid UTL_FILE_DIR  configured in database 2) Run autoconfig for all nodes 3) Check tnsnames.ora and listener.ora in $TNS_ADMIN, if exist FNDSM and FNDFS entries 4) In System Administrator Responsibility > Concurrent > Manager  > Define , set primary and secundary node for all manager. 5) Set profile option 'Concurrent: PCP Instance Check' to OFF if database instance-sensitive failover is  not  required. By setting it to 'ON', a concurrent manager will fail over to a secondary Application tier node if the database instance to which it is connected becomes unavailable for some reason. *** There are programs (MRP and Transaction Managers for example) that are not natively support RAC Database. Some programs use DBMS_PIPE, was therefore required specific configuration for these programs

#!/bin/bash for i in `/etc/init.d/oracleasm listdisks` do v_asmdisk=`/etc/init.d/oracleasm querydisk -d $i | awk  '{print $2}'` v_minor=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $1}'` v_major=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $2}'` v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'` echo "ASM disk $v_asmdisk based on /dev/$v_device  [$v_minor $v_major]" done

$cat get_free_memory.sh #!/bin/bash MEMFREE=$(cat /proc/meminfo|grep MemFree|awk '{print $2}') LOW=10000 echo em_result=$MEMFREE if [ $MEMFREE -lt $LOW ]; then echo "em_message=Free Memory low" fi $./get_free_memory.sh em_result=983 em_message=Free Memory low

As root: $cd $GRID_HOME/crs/install $perl rootcrs.pl -deconfig -force (Repeat for all nodes) On last node execute: $perl rootcrs.pl -deconfig -force -lastnode The -lastnode remove node cluster with the OCR and voting disks.

set verify off set pagesize 1000 column RN format A40 heading "Responsibility Name" prompt prompt &1 can be run by: SELECT responsibility_name RN FROM fnd_request_groups frg, fnd_request_group_units frgu, fnd_concurrent_programs_vl fcpv, fnd_responsibility_vl frv WHERE frgu.request_unit_type = 'P' AND (UPPER(fcpv.concurrent_program_name) = UPPER('&1') OR UPPER(fcpv.user_concurrent_program_name) = UPPER('&1')) AND frgu.request_group_id = frg.request_group_id AND frgu.request_unit_id = fcpv.concurrent_program_id AND frv.request_group_id = frg.request_group_id ORDER BY responsibility_name / Metalink note: 134036.1

declare l_result boolean; SELECT count(1) a, (CASE WHEN a < 1000 THEN l_result := fnd_concurrent.set_completion_status('NORMAL',Complete normal.'); WHEN a >= 1000 THEN l_result := fnd_concurrent.set_completion_status('WARNING','Complete with warnings.'); ELSE l_result := fnd_concurrent.set_completion_status('ERROR','Complete with error.'); END ) Raise FROM teste; commit; end; / exit;

C:\Users\denilson>java -Xmx1520m -version java version "1.4.2_14" Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_14-b05) Java HotSpot(TM) Client VM (build 1.4.2_14-b05, mixed mode) C:\Users\denilson>java -Xmx1530m -version Error occurred during initialization of VM Could not reserve enough space for object heap Could not create the Java virtual machine.

Change number of days to view in "Find Requests" (FNDRSRUN): alter value from profile: FND: Default Request Days

Query installed package: $rpm -qa glibc glibc-2.5-24 glibc-2.5-24 Query installed package with version and plataform: $rpm -qa --queryformat "|%{NAME}|%{VERSION}|%{RELEASE}|%{ARCH}|\n" glibc |glibc|2.5|24|x86_64| |glibc|2.5|24|i686|

Error: httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory As root create a symbolic link: ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2 Reference: Oracle Application Server 10g (10.1.2) Installation Requirements for Linux OEL 5 and RHEL 5 [ID 564174.1]

APPLICATION 1. as the APPLMGR user source the environment file: $. /u01/visappl/APPSORA.env 2. Create appsutil.zip file $perl $AD_TOP/bin/admkappsutil.pl Starting the generation of appsutil.zip Log file located at /u01/visappl/admin/log/MakeAppsUtil_05072343.log output located at /u01/visappl/admin/out/appsutil.zip MakeAppsUtil completed successfully. 3. Copy the appsutil.zip to Database ORACLE_HOME DATABASE -- Clear nodes EXEC FND_CONC_CLONE.SETUP_CLEAN; COMMIT; EXIT; $export TNS_ADMIN=$ORACLE_HOME/network/admin/ $ hostname rac1 1. Generate your Database Context File $ perl adbldxml.pl appsuser=apps appspass=oracle Starting context file generation for db tier.. Using JVM from /u01/app/oracle/product/11.2.0/db_1/jdk/jre/bin/java to execute java programs.. The log file for this adbldxml session is located at: /u01/app/oracle/product/11.2.0/db_1/appsutil/log/adbldxml_06151633.log Could not Connect to the Database with the above parameters, Please answer the Questions below Enter Hos

BEGIN DBMS_ADVISOR.quick_tune( advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name => 'test_tune', attr1 => 'SELECT SUM(TRANSACTION_QUANTITY) / :B5 * -1 FROM MTL_MATERIAL_TRANSACTIONS MMT, (SELECT TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID FROM MTL_TRANSACTION_TYPES WHERE ATTRIBUTE1 IN (''CONSUMO'')) MTT WHERE MMT.TRANSACTION_QUANTITY < 0 AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID AND MMT.TRANSACTION_ACTION_ID = MTT.TRANSACTION_ACTION_ID AND MMT.TRANSACTION_SOURCE_TYPE_ID = MTT.TRANSACTION_SOURCE_TYPE_ID AND TRUNC(MMT.TRANSACTION_DATE, ''MONTH'') >= :B4 AND TRUNC(MMT.TRANSACTION_DATE, ''MONTH'') <= :B3 AND MMT.ORGANIZATION_ID = :B2 AND MMT.INVENTORY_ITEM_ID = :B1 '); END; / Query output advisor: SET LONG 100000 SET PAGESIZE 50000 SELECT DBMS_ADVISOR.get_task_

R12: http://ebs.company.com:8000/forms/frmservlet 11.5.10 http://ebs.company.com:8000/dev60cgi/f60cgi In R12 version is necessary change the variable " s_appserverid_authentication " value to OFF in Context File and run autoconfig . This value (OFF) is already the default in version 11.5.10 . OFF - Server security is not checked. Any application server machine can access the database. Code IDs are also not checked. Use this option on test systems or if you have full control over the software on all machines which can physically access your database. ON - Some level of trust is required to access the database. Either the application server must be registered with the database or the code must pass a module and version ID known to be trusted. Use this option only if you wish to maintain compatibility with application servers that you cannot yet patch to the code level required for best security. SECURE - Full trust is required for access to the database. Only regi

http://blogs.oracle.com/stevenChan/resource/certifications.html

This example sends 100 concurrents " ACTIVE USERS" for testing. DECLARE G_APPLICATION_ID NUMBER; G_APPLICATION_NAME VARCHAR2(250); G_APPLICATION_SHORT_NAME VARCHAR2(10) := 'FND'; G_RESP_ID NUMBER; G_USER_ID NUMBER; G_USER_NAME VARCHAR2(400); V_REQUEST_ID NUMBER; BEGIN SELECT FA.APPLICATION_ID, FAT.APPLICATION_NAME INTO G_APPLICATION_ID, G_APPLICATION_NAME FROM FND_APPLICATION_TL FAT, FND_APPLICATION FA WHERE FAT.APPLICATION_ID = FA.APPLICATION_ID AND FAT.LANGUAGE = 'US' AND FA.APPLICATION_SHORT_NAME = G_APPLICATION_SHORT_NAME; G_USER_ID := 0; G_USER_NAME := 'SYSADMIN'; BEGIN SELECT RESPONSIBILITY_ID INTO G_RESP_ID FROM FND_RESPONSIBILITY WHERE RESPONSIBILITY_KEY = 'SYSTEM_ADMINISTRATOR'; EXCEPTION WHEN OTHERS THEN G_RESP_ID := -1; END; FND_GLOBAL.APPS_INITIALIZE(G_USER_ID, G_RESP_ID, 1); DBMS_SESSION.SET_NLS('NLS_TERRITORY',

Configure Hardware load-balance EBS R12 Load Balancer Entry Point: ebs.company.com Application Server 1: apps1.company.com Application Server 2: apps2.company.com Application Server 3: apps3.company.com Application Server 4: apps4.company.com Web Entry protocol: https Application Tier Web Protocol: http Application Tier Web Port: 8000 Active Web Port:443 Change the context values in all ebs nodes : Context Variable Name Context Variable Description Value s_webentryurlprotocol Protocol that desktop clients use to communicate with the web entry point server https s_webentryhost Name of the host that receives the first HTTP request from the desktop client ebs s_webentrydomain Domain name of the host that receives the first HTTP request from the desktop client company.com s_active_webport Port on the web server or load balancer that listens for HTTP reques

begin ORASSO.WWSSO_OID_INTEGRATION.reset_passwd('orcladmin',null,'welcome01'); end; / OR $cd $ORACLE_HOME/bin $./oidpasswd connect=yourDBconn unlock_su_acct=true

patchset 10.1.2.3 there are still issues in the 10.1.0.5 DB client causing the crashes. Apply patch: 7047034 https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=7047034 Metalink note: 1306651.1

Check current open cursor in database: select max(a.value) as highest_open_cur, p.value as max_open_cur from gv$sesstat a, gv$statname b, gv$parameter p where a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name = 'open_cursors' group by p.value; Opened cursor by session: select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid = a.sid and b.name = 'opened cursors current' and s.username is not null;

Edit the oraInst.loc file: vi /etc/oraInst.loc Change the inventory_loc to a new location: inventory_loc=/prod/oraInventory_new Create the new directory: mkdir /prod/oraInventory_new Give permissions to the new directory: chmod -R 777 /prod/oraInventory_new -- Add the 10.1.3 Oracle Home to the new created oraInventory: cd $INST_TOP/ora/10.1.3 . ./APP .env Go to the $ORACLE_HOME: cd $ORACLE_HOME Edit the oraInst.loc and point it to the same location ad done in step 1: inventory_loc=/prod/oraInventory_new Add the 10.1.3 Oracle Home to the new oraInventory location: cd $ORACLE_HOME/appsutil/clone ./ouicli.pl Verify if the 10.1.3 is added to the new oraInventory directory: cd /prod/oraInventory_new/ContentsXML cat inventory.xml If it's not added, check the /prod/oraInventory_new/logs file. Verify the oraInventory has the information about the 10.1.3 Oracle Home: export PATH=$ORACLE_HOME/OPatch:$PATH opatch lsinventory -detail -- Add the 10.1.2 Oracle Home to the new created oraInvento

To force Reboot : $echo 1 > /proc/sys/kernel/sysrq $echo b > /proc/sysrq-trigger To force Shutdown: $echo 1 > /proc/sys/kernel/sysrq $echo o > /proc/sysrq-trigger

Here is an example to enable Audit Trail on HZ_PARTIES and HZ_CUST_ACCOUNTS tables: a. (R)System Administrator, (N) Profile -> System Query Profile: 'AuditTrail:Activate'. Click FIND Set it to 'Yes' at Site level. b) Enable Audit Installations (AR) -- Got System Admin: Security:AuditTrail:Install -- Enable Audit Installation for AR c) Define Audit tables and desired columns. -- Got System Admin: Security: Audit Trail: Tables -- Query for user table name 'HZ_PARTIES' and add columns on which you want to enable trail -- do the same for table HZ_CUST_ACCOUNTS d) Define an Audit Group and associated tables -- Got System Admin: Security: Audit Trail: Groups -- Create Audit group for table defined in 'C' e) Run Concurrent program 'AuditTrail Report for Audit Group Validation' with parameter as your Audit Group. f) Define an Industry Template contain Audit Group -- Got System Admin: Security: Audit Trail Reporting: Audit Industry Template g) Run conc

select u.user_name, r.responsibility_name, f.user_function_name, s.function_type, to_char(s.first_connect, 'DD-MON HH24:MI:SS') "First Connect", to_char(s.last_connect, 'DD-MON HH24:MI:SS') "Last Connect", n.node_name from icx_sessions s, fnd_user u, fnd_responsibility_vl r, fnd_form_functions_vl f, fnd_nodes n where u.user_id(+) = s.user_id AND f.function_id(+) = s.function_id AND r.responsibility_id(+) = s.responsibility_id AND n.node_id(+) = s.node_id AND s.disabled_flag != 'Y' and s.pseudo_flag = 'N' and (s.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,s.limit_time, 0,s.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and s.counter < s.limit_connects

http://www.ardentperf.com/2007/09/12/gc-buffer-busy-waits-in-rac-finding-hot-blocks/

$adpatch defaultsfile=$APPL_TOP/admin/ /ATGDEF.txt responder as questőes do adpatch informando senha dos schemas system e apps até solicitar confirmaçăo do diretório de patch. $ CTRL+C SERÁ GERADO O ARQUIVO $APPL_TOP/admin/ /ATGDEF.txt JÁ COM AS SENHAS DO SYSTEM E APPPS CRIPTOGRAFADAS. ---------------------------------------------- CRIAR OS SCRIPTS patchcore e patchlad no diretório $AD_TOP/bin. Exemplo: patchcore: $adpatch options=prereq defaultsfile=$APPL_TOP/admin/ /ATGDEF.txt driver=$1 logfile=$1.log interactive=y patchtop=$PWD workers=16 patchlad: $adpatch options=noprereq defaultsfile=$APPL_TOP/admin/ /ATGDEF.txt driver=$1 logfile=$1.log interactive=y patchtop=$PWD workers=16

-- script to determine the status of the Oracle JVM and required packages connect / as sysdba spool jvm_stats.log set serveroutput on set echo on set pagesize500 set linesize 100 column comp_name format a40 select comp_name, version, status from dba_registry; select owner, status, count(*) from all_objects where object_type like '%JAVA%' group by owner, status; select owner, object_type, count(*) from all_objects where object_type like '%JAVA%' and status <> 'VALID' group by owner, object_type; select owner, status, object_type, object_name from all_objects where object_name like'%DBMS_JAVA%'; select owner, status, object_type, object_name from all_objects where object_name like'%INITJVMAUX%'; select role from dba_roles where role like '%JAVA%'; select * from v$sgastat where POOL = 'java pool' or NAME = 'free memory'; show parameter pool_size show parameter sga select owner, object_type, status, dbms_java.longnam

SET FEEDBACK OFF SET LINESIZE 250 SET SERVEROUTPUT ON DECLARE -- Variables declared P_OTAB DBMS_STATS.OBJECTTAB; MCOUNT NUMBER := 0; P_VERSION VARCHAR2(10); -- Cursor defined CURSOR c1 IS SELECT distinct schema FROM dba_registry ORDER by 1; -- Beginning of the anonymous block BEGIN -- Verifying version from v$instance SELECT version INTO p_version FROM v$instance; DBMS_OUTPUT.PUT_LINE(chr(13)); -- Defining Loop 1 for listing schema which have stale stats FOR x in c1 LOOP DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>x.schema,OPTIONS=>'LIST AUTO',OBJLIST=>p_otab); -- Defining Loop 2 to find number of objects containing stale stats FOR i in 1 .. p_otab.count LOOP IF p_otab(i).objname NOT LIKE 'SYS_%' AND p_otab(i).objname NOT IN ('CLU$','COL_USAGE$','FET$','INDPART$', 'MON_MODS$','TABPART$','HISTGRM$', 'MON_MODS_ALL$', 'HIST_HEAD$','IN $','