-----------------------

Script to Collect Data Guard Primary Site Diagnostic Information [ID 241374.1] -------------------------------------------------------------------------------- Modified 20-APR-2011 Type SCRIPT Status PUBLISHED Overview -------- This script is intended to provide an easy method to provide information necessary to troubleshoot Data Guard issues. Script Notes ------------- This script is intended to be run via sqlplus as the SYS or Internal user. Script ------- - - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - - -- NAME: dg_prim_diag.sql (Run on PRIMARY with a LOGICAL or PHYSICAL STANDBY) -- ------------------------------------------------------------------------ -- Copyright 2002, Oracle Corporation -- LAST UPDATED: 2/23/04 -- -- Usage: @dg_prim_diag -- ------------------------------------------------------------------------ -- PURPOSE: -- This script is to be used to assist in collection inform

Script to Collect Data Guard Physical Standby Diagnostic Information [ID 241438.1] -------------------------------------------------------------------------------- Modified 20-APR-2011 Type SCRIPT Status PUBLISHED Overview -------- This script is intended to provide an easy method to provide information necessary to troubleshoot Data Guard issues. Script Notes ------------- This script is intended to be run via sqlplus as the SYS or Internal user. Script ------- - - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - - -- NAME: DG_phy_stby_diag.sql -- ------------------------------------------------------------------------ -- AUTHOR: -- Michael Smith - Oracle Support Services - DataServer Group -- Copyright 2002, Oracle Corporation -- ------------------------------------------------------------------------ -- PURPOSE: -- This script is to be used to assist in collection information to help -

Mount the database whose name is to be changed SQL> startup pfile=inithcc.ora nomount ORACLE instance started. Total System Global Area 100663296 bytes Fixed Size 1217884 bytes Variable Size 88083108 bytes Database Buffers 8388608 bytes Redo Buffers 2973696 bytes SQL> alter database mount; Database altered. SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- ---------- HCC MOUNTED Use nid Utility to change the Database Name(from 9i or higher) $ nid target=sys/as sysdba dbname=test This may give the following Output DBNEWID: Release 10.2.0.1.0 - Production on Thu May 19 19:33:31 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Password: Connected to database HCC (DBID=1702020770) Connected to server version 10.2.0 Control Files in database: /home/hcctest/oracle/oradata/test/control/control01.ctl /home/hcctest/oracle/oradata/test/control/cont

Step by step document to create Standby Database Step by step document to create Standby Database Standby database are very critical for disaster recovery. This article takes you step by step to setup Oracle Standby Database. 1 - Data Guard Operational Prerequisites • Same Oracle software release must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same. • The Primary Database must run in ARCHIVELOG mode. • The hardware and Operating system architecture on primary and standby location must be same. • Each primary and standby database must have its own control file. • If primary and standby databases are placed on the same system, initialization parameters must be adjusted correctly. • Primary database must be FORCE LOGGING mode. 2 - Preparing Primary Database for Standby Database creation 2.1 Ensure the primary database in ARCHIVELOG mode Ensure

Problem: ======== During the creation of a new database the CREATE DATABASE command fails with the following errors: ORA-01501: CREATE DATABASE failed ORA-01519: error while processing file '?/rdbms/admin/sql.bsq' near line 406 ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 2192 bytes of shared memory ("shared pool", "unknown object","KQLS heap","KQLS MEM BLOCK") Solution: ========= 1. Increase the shared_pool_size parameter in the init .ora file. 2. Shutdown abort your database. 3. Startup nomount your database. 4. Execute the CREATE DATABASE command again. Explanation: ============ If your shared_pool_size is too small, the CREATE DATABASE command cannot finish successfully. This happens typically on small test instances when you just want to test anything on an empty instance and do not want to waste too much memory on a server. In this case you often edit in

utlrp script + What is UTLRP.SQL? This script recompiles invalid PLSQL modules. + What this script does? This script recompiles all existing invalid PL/SQL modules in a database. This is a fairly general script that can be used at any time to recompile all existing invalid PL/SQL modules in a database If run as one of the last steps during migration/upgrade/downgrade this script will validate all PL/SQL modules (i.e. procedures, functions, packages, triggers, types, views, libraries) during the migration step itself. + Any other script called within UTLRP.SQL? Yes. It invokes utlrcmp.sql Where are these SCRIPTS located? ============================ The scripts are found in the standard admin directory of the Oracle Home. $ORACLE_HOME/rdbms/admin Which script is better? ================= -> UTLRP.SQL - Validates all invalid objects and can be run anytime. (this in-turn is called by utlirp.sql) What is invalid ? you can find out by doing this In sqlplus : 15:03

SQL> select bgp.name,p.spid,s.sid 2 from v$bgprocess bgp,v$process p,v$session s 3 where bgp.paddr=p.addr 4 and p.addr=s.paddr; NAME SPID SID ----- ------------ ---------- PMON 29891 1 DBW0 29893 2 DBW1 29895 3 DBW2 29897 4 LGWR 29899 5 CKPT 29901 6 SMON 29903 7 RECO 29905 8 CJQ0 29907 9 QMN0 29909 10 10 rows selected. SQL> !ps -ef|grep 29893 orclhcc 29893 1 0 13:49 ? 00:00:00 ora_dbw0_HCC orclhcc 30347 14908 0 13:52 pts/5 00:00:00 /bin/bash -c ps -ef|grep 29893 orclhcc 30349 30347 0 13:52 pts/5 00:00:00 grep 29893 SQL> !kill -9 29893 SQL> !ps -ef|grep 29893 orclhcc 30378 14908 0 13:52 pts/5 00:00:00 /bin/bash -c ps -ef|grep 29893 orclhcc 30380 30378 0 13:52 pts/5 00:00:00 grep 29893 SQL> select 1 from dual; select 1 from

A script to kill all Oracle processes This is a common Unix script used by Oracle DBAs when a database is locked up, and Server Manager cannot be used to stop the database in more “gentle“ fashion. To begin, the Unix kill command is used to kill a process. The basic format of the kill command is as follows: Kill –9 PID1 PID2 PID3 …PIDn The trick is to be able to identify and kill only the Oracle processes. That’s done by stringing several commands together. The resulting one-line script looks like this: ps –ef|grep “ora_”|grep –v grep|grep $ORACLE_SID| awk ‘{print $2}’|xargs kill –9 We ‘ll walk through the process of building the command. To begin, we want to get a list of active processes on the server. We can do that using the following command: ps –ef If we execute ps –ef on our server, we’ ll see a long list of processes - both for Oracle and for many other things. However, we want to limit your output to only those processes that are related to the Oracle Data

Summary: What duties, if any, are different for an Apps DBA versus those of an Oracle 8/9i DBA? Full Article: Disclaimer: Contents are not reviewed for correctness and are not endorsed or recommended by ITtoolbox or any vendor. Popular Q&A contents include summarized information from Oracle-DB-L discussion unless otherwise noted. 1) Adapted from response by Harshal Dikshit on Tuesday, November 26, 2002 Oracle APPS DBA's duties are almost same as of Oracle DBA but your work will be more specific to the Architecture (or I should say Environment) and OFA of Oracle Application. Apart from that there are few more things you should know like applying patches or adadmin utility and some basic functionality of SYSADMIN responsibility of APPS. 2) Adapted from response by Winston Douglas on Tuesday, November 26, 2002 An APPs DBA knows all that a regular DBA knows plus the following: - Application fundamentals such as concepts and architecture - Application Installation and

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc. The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands: SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp; SQL> ALTER USER scott TEMPORARY TABLESPACE temp; Note that a temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up. What are TEMPFILES? Unlike normal data files, TEMPFILEs are not