Monday, November 30, 2009
Perform Export and Import Parallely
Scenario 1 :
You were asked to import a schema of your production server into your test server for some purpose. You are expecting that the export will take 5 hours and again the import will take quite more than that. In this case, you can use this option which will save your time in exporting and shipping the file to the other server.By doing this we will be able to export and import parallel on the same box.
1. In your test server, add the production database tnsnames to your tnsnames.ora
2. Create a unix pipe
mknod pipe name -p
ex : mknod ram_pipe -p
3. Start your export :
exp system/password@prod file=ram_pipe owner=erpds statistics=none log=/u01/export.log &
Remember to use & at the end to start the export in back groud and You be able to notice from the logfile whtr the pipe broken due to some errors in the export commands or the export is progressing...!!
4. Now from the same terminal start the import..!!
imp system/XXXXX@testserver file=ram_pipe fromuser=erpds touser=testds log=/u01/import.log &
Simple..!! You will notice one job wil be exporting and other will be importing...!!
Friday, October 23, 2009
Oracle 10g Logminer Example
To find out what Oracle is writing to the redo log files called LogMiner. The redo log files store all the data needed to perform a database recovery and record all changes (INSERT, UPDATE and DELETE) made to the database.
Below are the steps how to setup and mine a archive log :
1. Make sure you get the list of archives generated for the day using the below commnand. From the below output identify the archivelogs you are going to mine using logminer..
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select thread#, sequence#, completion_time from v$archived_log order by completion_time desc;
2. Now Build a Logminer dictionary , make sure you have the follwoing folder available and your unix account have acces to it :
execute dbms_logmnr_d.build('dictionary.ora','/d01/oracle');
3. Copy all the archivelog to a path and register all the logs as show below :
execute dbms_logmnr.add_logfile('/d02/archivelogs/backup/arch/8045.arc',dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile('/d02/archivelogs/backup/arch/8046.arc',dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile('/d02/archivelogs/backup/arch/8047.arc',dbms_logmnr.addfile);
4. Now from the below view , make sure you have all the registered logs available for mining.
select log_id, filename from v$logmnr_logs;
5.Using the below view's find the first scn and high scn to mine from the registered logs.
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select low_time,high_time,low_scn,next_scn from v$logmnr_logs;
6.From the above out gather the details and add it to the below logminer session :
execute dbms_logmnr.start_logmnr(dictfilename =>'/d01/oracle/dictionary.ora',starttime => to_date('2008-04-21 10:00:14', 'yyyy-mm-dd hh24:mi:ss'), endtime => to_date('2008-04-21 12:11:07', 'yyyy-mm-dd hh24:mi:ss'));
7. As v$logmnr_contents is a temporary view, once you disconnect your session , you won't be able to see the content, so make sure you create a table of all the contents of the view.
Note : If you mine a bunch of logs , create table will take more time to get created.
create table logmnr_table_1 as select * from v$logmnr_contents;
Monday, August 31, 2009
10g RMAN Incremental backup
The most important reason for doing incremental backups is associated with data warehouse environments, where many operations are done in NOLOGGING mode and data changes do not go to the archived log files.Considering the massive size of data warehouses today, and the fact that most of the data in them does not change, full backups are neither desirable nor practical. Therefore , doing incremental backups in RMAN is an ideal alternative.
WhyDBAs dis not like incremental backup in 9i ?
Oracle does full scan for finding changes in 9i which generates some performance problem.This is why most DBAs did not like incremental backup in 9i.
What is new with 10g incremental backup ?
Block changes can be tracked in 10g which means that Oracle does not need to do full scan anymore to find out changes.
To enable track changing run the following command.
alter database enable block change tracking using file '/rman_bkups/change.log';
To disable block track change.
alter database disable block change tracking;
Wednesday, August 12, 2009
How to Enable Listener Logging and Tracing
Enabling Logging and Tracing in listener.ora:
1)Way 1: Putting entry in listener.ora
2)Way 2: Using Enterprise Manager.
1)Way 1: Putting entry in listener.ora
a)With an editor software edit the listener.ora file, Like on unix, vi $ORACLE_HOME/network/admin/listener.ora
b)Put an entry of LOGGING_LISTENER for logging and TRACE_LEVEL_LISTENER for tracing.Like,
TRACE_FILE_LISTENER = LISTENERTEST.trc (The destination file for the trace file)TRACE_DIRECTORY_LISTENER = /export/home (The destination directory for the trace file)LOGGING_LISTENER = on
TRACE_LEVEL_LISTENER =SUPPORT
TRACE_LEVEL:It specifies the level of detail the trace facility records for the listener.The trace level value can either be a value within the range of 0 (zero) to 16 (where 0 is no tracing and 16 represents the maximum amount of tracing) or a value of off, admin, user, or support.
i))off (equivalent to 0) provides no tracing.
ii))user (equivalent to 4) traces to identify user-induced error conditions.
iii)admin (equivalent to 6) traces to identify installation-specific problems.
iv)support (equivalent to 16) provides trace information for troubleshooting information for Oracle Support Services.
c)Reload The Listener:
Like, lsnrctl reload
2)Way 2: Using Enterprise Manager.
i)Access the Oracle Net Administration page in Oracle Enterprise Manager.
ii)Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.
iii)Click Go.
The Listeners page appears.
iv)Select a listener, and then click Edit.The Edit Listeners page appears.
v)Click the Logging & Tracing tab.
vi)Specify the settings.
vii)Click OK.
Some Troubleshooting:
Format of the Listener Log Audit Trail:
---------------------------------------------
The audit trail formats text into the following fields:
Timestamp * Connect Data [* Protocol Info] * Event [* SID Service] * Return Code
Properties of the audit trail are as follows:
-Each field is delimited by an asterisk (*).
-Protocol address information and service name or SID information appear only when a connection is attempted.
-A successful connection or command returns a code of zero.
-A failure produces a code that maps to an error message.
With the return code you can be able to see which type of error and when it occurs.
Audit Trail information can be used to view trends and user activity by first storing it in a table and then collating it into a report format.
Use the Trace Assistant to Examine Trace Files
With trcasst examine the trace file, Like
trcasst trace_file_name_here.
For example to see statistics we can use,
trcasst -s /export/home/mytracefile.trc
Wednesday, June 10, 2009
Fake space consumption by ASM database
Almost all of the 10g and above databases usually use ASM. One of the most common requests of development systems is to add another lun to add more space to ASM. This is one of the great features of ASM, adding or removing disk without affecting the current databases. Before adding a lun, however, I always check to see if space is really needed. Sometimes, there will be a database which has not been backed up in weeks and it will have literally thousands of archive logs that are filling up ASM. That remedy is easy, either back up the database and archives or delete the archive logs and run a new full backup.
Then there are the vampire databases, those that suck up disk space, but are not active and not being used. Often, they are just forgotten databases by developers or DBAs that are shut down, removed from oratab and backups and generally forgotten about since no one notices the space they take up in ASM. In dealing with space issues, I needed a quick way to determine what databases were in ASM, whether or not they were actually running. Here is what I came up with:
REM asm_db_size.sql
REM Author - Jay Caviness - Grumpy-dba.com
REM 5 March 2009
REM ------------------------------------------------------------
set pages 999
set heading on
set feedback off
set lines 80
col "Size in MB" for 999,999,999
col "Database" for a25
select database_name "Database",
sum(space)/1024/1024 "Size in MB" FROM (
SELECT
CONNECT_BY_ROOT db_name as database_name, space
FROM
( SELECT
a.parent_index pindex
, a.name db_name
, a.reference_index rindex
, f.bytes bytes
, f.space space
, f.type type
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a
USING (group_number, file_number)
)
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex)
group by database_name
order by database_name
/
Which, when run gives the output:
Database Size in MB
------------------------- ------------
DB_UNKNOWN 10
QA 29,667
QACA20A 9,011
QARA20A 13,258
QCONFIG 17,653
QHC1011 8,874
QHR1011 12,068
QICA11A 17,247
QIRA11A 27,041
TESTC02 8,908
TESTR02 11,791
In this case I know that TESTC02 and TESTR02 are not running and upon checking with the owners of this system, received permission to drive a stake through the heart of. Cheers !remove these databases saving 20G of space.
Friday, April 10, 2009
10g RAC: How to Clean Up After a Failed CRS Install
PURPOSE
-----------
The purpose of this document is to help DBA's and support analysts understand howto clean up a failed CRS (Cluster Ready Services) install for 10g RAC.
SCOPE & APPLICATION
--------------------------
DBA's and Support Analysts
10g RAC: How to Clean Up After a Failed CRS Install
-----------------------------------------------------
Not cleaning up a failed CRS install can cause problems like node reboots.Follow these steps to clean up a failed CRS install:
1. Run the rootdelete.sh script then the rootdeinstall.sh script from the $ORA_CRS_HOME/install directory on any nodes you are removing CRS from. Running these scripts should be sufficent to clean up your CRS install. If you have any problems with these scripts please open a service request.
If for some reason you have to manually remove the install due to problemswith the scripts, continue to step 2:
2. Stop the Nodeapps on all nodes:
srvctl stop nodeapps -n
3. Prevent CRS from starting when the node boots. To do this issue the followingas root:
Sun:
rm /etc/init.d/init.cssd
rm /etc/init.d/init.crs
rm /etc/init.d/init.crsd
rm /etc/init.d/init.evmd
rm /etc/rc3.d/K96init.crs
rm /etc/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
Linux:
rm /etc/oracle/*
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -f /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
HP-UX:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc2.d/K960init.crs
rm /sbin/rc2.d/K001init.crs
rm /sbin/rc3.d/K960init.crs
rm /sbin/rc3.d/S960init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
HP Tru64:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K96init.crs
rm /sbin/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
IBM AIX:
rm /etc/init.cssd
rm /etc/init.crs
rm /etc/init.crsd
rm /etc/init.evmd
rm /etc/rc.d/rc2.d/K96init.crs
rm /etc/rc.d/rc2.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -Rf /etc/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
4. If they are not already down, kill off EVM, CRS, and CSS processes or reboot the node:
ps -ef grep crs
kill
ps -ef grep evm
kill
ps -ef grep css
kill
Do not kill any OS processes, for example icssvr_daemon process !
5. If there is no other Oracle software running (like listeners, DB's, etc...), you can remove the files in /var/tmp/.oracle or /tmp/.oracle. Example:
rm -f /var/tmp/.oracle
or
rm -f /tmp/.oracle
6. Remove the ocr.loc Usually the ocr.loc can be found at /etc/oracle
7. De-install the CRS home in the Oracle Universal Installer
8. Remove the CRS install location:
rm -Rf
9. Clean out the OCR and Voting Files with dd commands. Example:
dd if=/dev/zero of=/dev/rdsk/V1064_vote_01_20m.dbf bs=8192 count=2560
dd if=/dev/zero of=/dev/rdsk/ocrV1064_100m.ora bs=8192 count=12800
If you placed the OCR and voting disk on a shared filesystem, remove them.
If you are removing the RDBMS installation, also clean out any ASM disks if they have already been used.
10.If you would like to re-install CRS, follow the steps in the RAC Installation manual.
Sunday, February 22, 2009
Adding a Node to a 10g RAC Cluster
----------
The purpose of this note is to provide the user with a document that
can be used as a guide to add a cluster node from an Oracle 10g Real
Applications environment.
SCOPE & APPLICATION
--------------------------
This document can be used by DBAs and support analsyts who need to
either add a cluster node or assist another in adding a cluster
node in a 10g Unix Real Applications environment. If you are on
10gR2 (10.2.0.2 or higher), please refer to the documentation for
more updated steps.
ADDING A NODE TO A 10g RAC CLUSTER
--------------------------------------------
The most important steps that need to be followed are;
A. Configure the OS and hardware for the new node.
B. Add the node to the cluster.
C. Add the RAC software to the new node.
D. Reconfigure listeners for new node.
E. Add instances via DBCA.
Here is a breakdown of the above steps.
A. Configure the OS and hardware for the new node.
----------------------------------------------------
Please consult with available OS vendor documentation for this step.
See Note 264847.1 for network requirements. Also verify that the OCR and
voting files are visible from the new node with correct permissions.
B. Add the node to the cluster.
------------------------------
1. If the CRS Home is owned by root and you are on a version < 10.1.0.4, change
the ownership of the CRS Home directories on all nodes to the Oracle user
so that OUI can read and write to these directories.
2. Set the DISPLAY environment variable and run the addNode.sh script from
$ORA_CRS_HOME/oui/bin on one of the existing nodes as the oracle user.
Example:
DISPLAY=ipaddress:0.0; export DISPLAY
cd $ORA_CRS_HOME/oui/bin
./addNode.sh
3. The OUI Welcome screen will appear, click next.
4. On the "Specify Cluster Nodes to Add to Installation" screen, add the
public and private node names (these should exist in /etc/hosts and
should be pingable from each of the cluster nodes), click next.
5. The "Cluster Node Addition Summary" screen will appear, click next.
6. The "Cluster Node Addition Progress" screen will appear. You will
then be prompted to run rootaddnode.sh as the root user. First verify
that the CLSCFG information in the rootaddnode.sh script is correct.
It should contain the new public and private node names and node
numbers. Example:
$CLSCFG -add -nn <node2>,2 -pn <node2-private>,2 -hn <node2>,2
Then run the rootaddnode.sh script on the EXISTING node you ran the
addNode.sh from. Example:
su root
cd $ORA_CRS_HOME
sh -x rootaddnode.sh
Once this is finished, click OK in the dialog box to continue.
7. At this point another dialog box will appear, this time you are
prompted to run $ORA_CRS_HOME/root.sh on all the new nodes.
If you are on version < 10.1.0.4 then
- Locate the highest numbered NEW cluster node using "$ORA_CRS_HOME/bin/olsnodes -n".
- Run the root.sh script on this highest numbered NEW cluster node.
- Run the root.sh script on the rest of the NEW nodes in any order.
For versions 10.1.0.4 and above the root scritps can be run on the NEW
nodes in any order.
Example:
su root
cd $ORA_CRS_HOME
sh -x root.sh
If there are any problems with this step, refer to Note 240001.1
Once this is finished, click OK in the dialog box to continue.
8. After running the CRS root.sh on all new nodes, run
$ORA_CRS_HOME/bin/racgons add_config <newnode1>:4948 <newnode2>:4948...
from any node.
9. Next you will see the "End of Installation" screen. At this point you
may exit the installer.
10. Change the ownership of all CRS Homes back to root.
C. Add the Oracle Database software (with RAC option) to the new node.
-------------------------------------------------------------------------
1. On a pre-existing node, cd to the $ORACLE_HOME/oui/bin directory and
run the addNode.sh script. Example:
DISPLAY=ipaddress:0.0; export DISPLAY
cd $ORACLE_HOME/oui/bin
./addNode.sh
2. The OUI Welcome screen will appear, click next.
3. On the "Specify Cluster Nodes to Add to Installation" screen, specify
the node you want to add, click next.
4. The "Cluster Node Addition Summary" screen will appear, click next.
5. The "Cluster Node Addition Progress" screen will appear. You will
then be prompted to run root.sh as the root user.
su root
cd $ORACLE_HOME
./root.sh
Once this is finished, click OK in the dialog box to continue.
6. Next you will see the "End of Installation" screen. At this point you
may exit the installer.
7. Cd to the $ORACLE_HOME/bin directory and run the vipca tool with the
new nodelist. Example:
su root
DISPLAY=ipaddress:0.0; export DISPLAY
cd $ORACLE_HOME/bin
./vipca -nodelist <node1>,<node2>
8. The VIPCA Welcome Screen will appear, click next.
9. Add the new node's virtual IP information, click next.
10. You will then see the "Summary" screen, click finish.
11. You will now see a progress bar creating and starting the new CRS
resources. Once this is finished, click ok, view the configuration
results, and click on the exit button.
12. Verify that interconnect information is correct with:
oifcfg getif
If it is not correct, change it with:
oifcfg setif <interface-name>/<subnet>:<cluster_interconnectpublic>
For example:
oifcfg setif -global eth1/10.10.10.0:cluster_interconnect
or
oifcfg setif -node <nodename> eth1/10.10.10.0:cluster_interconnect
D. Reconfigure listeners for new node.
--------------------------------------
1. Run NETCA on the NEW node to verify that the listener is configured on
the new node. Example:
DISPLAY=ipaddress:0.0; export DISPLAY
netca
2. Choose "Cluster Configuration", click next.
3. Select all nodes, click next.
4. Choose "Listener configuration", click next.
5. Choose "Reconfigure", click next.
6. Choose the listener you would like to reconfigure, click next.
7. Choose the correct protocol, click next.
8. Choose the correct port, click next.
9. Choose whether or not to configure another listener, click next.
10. You may get an error message saying, "The information provided for this
listener is currently in use by another listener...". Click yes to
continue anyway.
11. The "Listener Configuration Complete" screen will appear, click next.
12. Click "Finish" to exit NETCA.
13. Run crs_stat to verify that the listener CRS resource was created.
Example:
cd $ORA_CRS_HOME/bin
./crs_stat
14. The new listener will likely be offline. Start it by starting the
nodeapps on the new node. Example:
srvctl start nodeapps -n <newnode>
15. Use crs_stat to confirm that all VIP's, GSD's, ONS's, and listeners are
ONLINE.
E. Add instances via DBCA. (for standby databases see section F)
-----------------------------------------------------------------
1. To add new instances, launch DBCA from a pre-existing node. Example:
DISPLAY=ipaddress:0.0; export DISPLAY
dbca
2. On the welcome screen, choose "Oracle Real Application Clusters",
click next.
3. Choose "Instance Management", click next.
4. Choose "Add an Instance", click next.
5. Choose the database you would like to add an instance to and specify
a user with SYSDBA privileges, click next. Click next again...
6. Choose the correct instance name and node, click next.
7. Review the storage screen, click next.
8. Review the summary screen, click OK and wait a few seconds for the
progress bar to start.
9. Allow the progress bar to finish. When asked if you want to perform
another operation, choose "No" to exit DBCA.
10. To verify success, log into one of the instances and query from
gv$instance, you should now see all nodes.
F. Adding Instances to the Standby Database
---------------------------------------------
1. If you are using a RAC primary, make sure the steps from section E have
been performed. If you are using a single instance primary, add the redo
log groups and threads to the primary database via "alter database"
commands. Example commands:
alter database add logfile thread 2
group 3 ('/dev/RAC/redo2_01_100.dbf') size 100M,
group 4 ('/dev/RAC/redo2_02_100.dbf') size 100M;
alter database enable public thread 2;
2. Create a new standby controlfile from the primary database and copy it
to the standby. Example commmand:
alter database create standby controlfile as "/u01/stby.ctl";
3. Shut down the standby database, back up the existing standby
controlfile on the standby database, and copy the new standby
controlfile into place.
4. Adjust any init.ora or spfile parameters such as thread, instance_name,
instance_number, local_listener, undo_tablespace, etc... for any new
instances.
5. Recover the standby database.
Tuesday, February 17, 2009
Post Cloning Setups (After Each Clone)
1. Update wf_notifications
set status = 'CLOSED',mail_status = 'SENT',end_date = sysdate;
2. Update fnd_user
set email_address = 'TestOracle@yourdomain.com ';
3. Update per_people_f
set email_address = 'TestOracle@yourdomain.com ';
4. Update po_vendor_sites_all =
email_address = 'TestOracle@yourdomain.com',remittance_email = 'TestOracle@yourdomain.com';
5. Purge Workflow data :
Run the "Purge Obsolete Workflow Runtime Data" concurrent program
6. If test usernames are implemented, re-enable these accounts with the following code snippet:
UPDATE apps.fnd_user
SET last_update_date = SYSDATE, -- set update date
last_updated_by = -1, -- set to ANONYMOUS
password_accesses_left = NULL,
password_lifespan_accesses = NULL,
password_lifespan_days = NULL,
end_date = NULL -- activate user account
WHERE user_name = 'TEST_APPROVER I'
OR user_name = 'TEST_APPROVER II'
OR user_name = 'TEST_APPROVER III'
OR user_name = 'TEST_BUYER'
OR user_name = 'TEST_REQ'
7. Alerts can be selectively disabled or changed based on your preferences and the type of alert.
Wednesday, February 11, 2009
Control File Autobackups
A quick test. First, ensure you have control file autobackups enabled and select a location on disk. By default the control file backup will be placed in your $ORACLE_HOME/dbs directory unless you specify a location.
oravis@myserver=> rman target / nocatalog
Recovery Manager: Release 10.2.0.2.0 - Production on Wed Jan 16 14:48:49 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: VIS (DBID=767668735)
using target database control file instead of recovery catalog
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bck/vis_backup/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bck/vis_backup/%F';
A quick test to make sure the backup works and is on disk:
oravis@myserver=> rman target / nocatalog
Recovery Manager: Release 10.2.0.2.0 - Production on Wed Jan 16 15:25:24 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: VIS (DBID=767668735)using target database control file instead of recovery catalog
RMAN> backup current controlfile;
Starting backup at 16-JAN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=414 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupset
channel ORA_DISK_1: starting piece 1 at 16-JAN-08
channel ORA_DISK_1: finished piece 1 at 16-JAN-08piece handle=/mnt1/app/visdb/1020_64bit/dbs/01j6adrd_1_1 tag=TAG20080116T152532 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 16-JAN-08
Starting Control File Autobackup at 16-JAN-08
piece handle=/oradata/bck/vis_backup/c-767668735-20080116-00 comment=NONE
Finished Control File Autobackup at 16-JAN-08
RMAN> exit
Recovery Manager complete.
oravis@myserver=> ls -alrt /oradata/bck/vis_backup
total 42036
drwxrwxr-x 28 oravis dba 1024 Jan 16 14:47 ..
-rw-rw---- 1 oravis dba 19398656 Jan 16 15:25 c-767668735-20080116-00
drwxrwxr-x 2 oravis dba 512 Jan 16 15:25 .
Next we need to make a structural change to the database to see if the control file is automatically backed up. For this example i'll add a small datafile to the system tablespace.
SQL> alter tablespace SYSTEM add datafile '/oradata/dbf/visdata/system09.dbf' size 25M;
Tablespace altered.
A directory listing of the backup location should show a second control file backup:
oravis@myserver=> ls -alrt /oradata/bck/vis_backup
total 79972
drwxrwxr-x 28 oravis dba 1024 Jan 16 14:47 ..
-rw-rw---- 1 oravis dba 19398656 Jan 16 15:25 c-767668735-20080116-00
-rw-rw---- 1 oravis dba 19398656 Jan 16 15:26 c-767668735-20080116-01
drwxrwxr-x 2 oravis dba 512 Jan 16 15:26 .
Monday, February 2, 2009
How to shrink temporary tablespace if it goes huge
Your production database has bi-annual or annual MIS reports program which performs huge sorting. Due that your Temp tablespace grows very much and occupies most of the space on file system. Now as the MIS reports process is run probably once or twice a year you don't want to keep a huge tempfile around for the rest of the 364 days and don't want to go out to buy disks.
You have created temp tablespace with datafiles as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid Error: ORA-1652 Text: unable to extend temp segment by %s in tablespace %s
You have tried "alter database datafile .. resize" which always fails with Error: ORA 3297 : file contains
You want to shrink the datafile to utilize the disk space for other tablespaces or any other purpose.
Fix:
Create new temporary tablespace with smaller size.
SQL>
create temporary tablespace TEMP
tempfile '/iaa/oradata/u01/TABSIAA/temp01.dbf' size 100M
extent management local uniform size 128K;
SQL> alter database default temporary tablespace temp2;
SQL> drop tablespace temp including contents and datafiles;
If any users are using the temporary tablespace currently, you would not be able to drop the tablespace. So, let us find out wihich users are using the temporary tablespace right now using bellow query:
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)'M' "SIZE",
a.sid','a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
Then kill or wait until the above users finish their jobs, and then try to drop the temporary tablespace again.
You can also see the usage of temporary tablespace using bellow query:
select TABLESPACE_NAME, BYTES_USED, BYTES_FREE
from V$TEMP_SPACE_HEADER;
Cheers !
Wednesday, January 28, 2009
How to to find unused indexes and delete tham to release space
PURPOSE
To show how to view all indexes being monitored.
SCOPE & APPLICATION
Instructional.
Viewing All Indexes Being Monitored Under Another User's Schema:
=================================================
V$OBJECT_USAGE does not display rows for all indexes in the database whose usage is being monitored.
'ALTER INDEX MONITORING USAGE'
places an entry in V$OBJECT_USAGE for that particular index to help determine if the index is being used or not. The V$OBJECT_USAGE view uses the username logged into database when the 'ALTER INDEX MONITORING USAGE' is issued. This will not enable any user otherthan the user who issued the 'ALTER INDEX MONITORING USAGE' to view if index is being monitored or not.
The view structure may be changed slightly (see below) in order to expand its scope system-wide (see below) so that you may see all indexes being monitored.
For example:
Showing User Scott monitoring his Index on EMP table:
SQL> connect scott/tiger SQL> set LONG 30000
SQL> select text from dba_views where view_name ='V$OBJECT_USAGE';
TEXT
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
SQL> select index_name, table_name, uniqueness, status from user_indexes where table_name = 'EMP';
INDEX_NAME TABLE_NAME UNIQUENES STATUSPK_EMP EMP UNIQUE VALID
SQL> alter index PK_EMP monitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORINGPK_EMP EMP YES NO 10/12/2001 06:42:35
Then connect as another user to view indexes being monitored:
SQL> connect / as sysdba;
Connected.
SQL> select * from v$object_usage;
no rows selected
To be able to view them do the following:
SQL> create or replace view V$ALL_OBJECT_USAGE
(OWNER,INDEX_NAME, TABLE_NAME, MONITORING,
USED, START_MONITORING, END_MONITORING)
as select u.name, io.name, t.name, decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring,
ou.end_monitoring from sys.user$ u, sys.obj$ io, sys.obj$ t,
sys.ind$ i, sys.object_usage ou where i.obj# = ou.obj#
and io.obj# = ou.obj# and t.obj# = i.bo#and u.user# = io.owner#
View created.
SQL> select * from v$all_object_usage;
OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORINGSCOTT PK_EMP EMP YES NO 10/12/2001 06:42:35
Saturday, January 24, 2009
Unix/Linux Administrative Commands For DBA
Some UNIX/Linux commands are particularly useful for administrative purpose.They help us find the Server Statistics including CPU usage,Memory Usage,Virtual Memory statistics,I/O statistics.The commands and their details are given below :
tar - Unix Backup and restore utility
tar -cvf backup-devicepath pathofsourcefiletobe archived- creates a new archive.Hyphen is optional
tar -xvf backupdevicepath archivefile - extracts the archived file.
cpio - Copy input/output command
cpio -o - cpio command with -o option copies files to standard output such as disk or tape
cpio -i - cpio command with -i option extracts file from standard input.
Usage :
cpio -o > standardtape/disk path
cpio - i <>
iostat - displays input output statistics for all the disks on our system
sar - System Activity reporter.Its a very powerful tool that offers a way to analyze how the read/write operations are occuring from disk to buffer cache and from buffer cache to disk.By using various options of sar command we can monitor disk and CPU activity,in addition to buffercache activity.
top - It is a commonly used performance monitoring tool.It shows us a little bit of information on everything such as the top CPU and memory utilization processes,the percentage of CPU time used by the top processes, and memory utilization.
glance - A system-monitoring tool in HP-UNIX.Used for monitoring memory,Disk I/O,CPU Performance
netstat - Monitor network to make sure that there are no serious traffic bottlenecks.
ps - Once tha database or any application is started in a UNIX/Linux system the primary step is to ensure that processes have been invoked. ps -efgrep process-name is a clear way to ensure that processes are running. If the output doesnt give out any ros then processes are down and systems need to be restarted again.
ssh - Secure shell. It is a Protocol used to secure communications between hosts.It encrypts the username and password.It uses private keys to authenticate user communication between hosts without a passowrd.This is useful in automating operational tasks using Shell Script,RAC installation etc.
ftp - File Transfer Protocol. It is used to upload files to a host. It needs username/password authentication.telnet - Terminal that enables us to connect to a server.rlogin - Remote login.Enables us to login remotely into a server.
rcp - Remote copy.Enables us to copy files remotely.exit - Enables us to exit a user session.crontab - Schedule shell programs and automate tasks.
ls - List the files and subdirectory in a directory.Doesnt list the hidden files.
ls -a - Lists the hidden files.some files have name like .cshrc,.loin (prefix with .). They are called as hidden files and are used for security reasons.They are not visible under normal listing.
Various types of shells - sh,ksh,bash,csh,tcsh,zsh
AD Utilities (Applications DBA Utilities)
A brief listing of utilities and their usage is given below:
adadmin - Performs maintenance tasks for Oracle Applications.
adchkdig - Checks the integrity of Oracle Applications patches downloaded from OracleMetaLink.
adutconf.sql - Reports standard information about the installed configuration of Oracle Applications.
adctrl - Manages parallel workers in AD Administration and AutoPatch.
adident - Reports the version and translation level of an OracleApplications file.
adncnv - Converts a file from one character set to another.
admrgpch - Merges multiple patches into a single merged patch.
adrelink.sh - Relinks Oracle Applications executable programs withthe Oracle server product libraries.
adsplice - Adds off-cycle products.
adtimrpt.sql - Reports a summary of the timing for jobs run by parallel workers.
adpatch - Applies patches and other system updates.
admsi.pl - Generates customized installation instructions for a patch.
rapidwiz - Provides a wizard for entering parameters that are specific to a new installation or an upgrade of an Oracle Applications system.
Thursday, January 22, 2009
How to create ASM instance
ASM instance is used to manage database files.If a node hosts multiple databases,single database,files can be managed using ASM.
Two possible ASM deployments are:
1) Seperate oracle home is there are multiple databases on a single node
2) No seperate oracle home if the node hosts a single database.
ASM instance needs to be created and started before the Oracle database instance is started.
css - cluster synchronization service daemon is used to establish connection between an ASM instance and oracle database instance.
To startup ASM instance alone and restrict the connection to Oracle database instance startup ASM instance as follows:
SQL>STARTUP RESTRICT;
Steps to startup an ASM instance are as follows:
1) Check is css is running using
ps -efgrep css
We can also use crsctl command to check if css appears healthy
crsctl check css
If css isn't up and running startup ASM as follows
1) Login as root user
2) export PATH=PATH:$ORACLE_HOME
3) localconfig addThe localconfig command creates /etc/oracle file,startsup css.The $ORACLE_HOME/css/admin has control script for css startup and shutdown.
It adds init.cssd to /etc/inittab file.
If we use DBCA to create an instance css is started automatically.
2) Create a initialization parameter file initasm+.ora with the following parameters set.
Instance_type=+ASM
ASM_POWER_Limit =Value from 1 to 11
ASM_Diskstring = '/dev/sda*',
ASM_DISKGROUPS=dg1,dg2
3) Startup the instance.ASM doesn't have a data dictionary.It is mandatory to login as a user with SYSDBA/SYSOPER privilege.For local authentication use Operating system(OS) authentication.For remote authentication use a password file.
4) Set ORACLE_SID=+ASM (ASM is the default value).The value set for instance_type is mapped internally to db_unique_name.
$sqlplus / nolog
SQL>connect / as sysdba
SQL> startup pfile='/asd/initasm+.ora'
This step creates an ASM instance.ASM cache is the SGA component specific to ASM.It takes as little as 25MB- 30MB space.Total spce needed for an ASM instance is 100MB as it doesnt have any datafiles.
5) Check is ASM instance is up and running using :
ps -efgrep asm
The step above lists background processes like:
asm_pmon_+asm
asm_smon_+asm
asm_lgwr_+asm
asm_ckpt_+asm
asm_dbw0_+asm
asm_mman_+asm
Few special is ASM like
asm_rbal_+asm
asm_psp0_+asm
asm_gmaon_+asm
Migrating Database To ASM Using RMAN
1)user-managd bakups
2) Os utilities and commands - they cant see ASM files as ASM files are created on raw disk and there is no file system for ASM files.
RMAN is used to migrate database to ASM.Follow the steps below.
Consider the case where we have an instance named "neworacle".We have three ASM diskgroups dgnew1 - dtles,dgnew2 - controlfile, dgnew3 - online log files.
Follow the steps given below:
1) Change COROLFILES instance parameter to point towards disk group.
SQL> Alter system set controlfiles='+dgnew2','+dgnew3' scope=spfile;
2) Shutdown the instance.
SQL> shutdown immediate;
3) Start database in nomount mode.
SQL>startup nomount;
4) Launch RMAN(RMAN client),restore controlfile from its original location:
RMAN> restore controlfile from '/originalloc/contrl1.con';
5) Issue the steps from RMAN prompt.We can script the list of steps and execute from RMAN prompt as well.This migrates the datafiles from its original location to ASM.
RMAN>shutdown immediate;
RMAN> startup mount;
RMAN>backup as copy database format '+dgnew1';
RMAN> switch database to copy;
RMAN>alter database open;
6) To migrate redologs, create new members in the diskgroups and drop the old members.
SQL>alter database add logfile member '+dgnew2','+dgnew3' to group 1;
SQL>alter database drop logfile member '/originalloc/log1a.rdo','/originalloc/log1b.rdo';
7) Final step is to move tempfiles.It is not possible to move tempfiles(temporary tablespace).So drop and recreate the temporary tablespace on a disk group.
Wednesday, January 21, 2009
ITIL for DBA's
ITIL stands for Infrastructure Information Library.It is a standard from U.K government that has undergone various revisions.Latest Version is ITILv3.
What is ITILv3 focussed on?
ITIL in general deals with the processes related to service management.This includes issues related to incidents mangement,problem management,configuration management,change management,release management.
What is incident management?
Any unexpected event that has an impact on the existing system is an incident.This includes database shutdown due to various failures like media error,server failure.Processes needs to be formulated and followed to handle incidents.Incidents are unknown and hence proactive measures needs to be taken to prevent them and reactive actions should be taken to adress them.
What is problem management?
Problem is an recurring issue.It is mandatory to analyse the root cause of an issue and come out with a permanent solution.Database,Server,Application logs can be used for this purpose.A good example is Load in database that needs to be tuned,deadlock issue etc.
What is configuration Management?
Any chnages made to the system configuration.This can be changes to server,database,application.
What is change management?
Whenever an incident occurs it is mandatory to raise a ticket and depending on the severity it could be given severity(priority) say SEV1 etc.
What is release management?
It is mandatory to document the patch level,software version level of infrastructure components including servers,databases.This document needs to be available to everyone for quick reference.
So whenever a change happens it has to be documented.
Why does a DBa need ITIL?
Around 40% of billion dollar software companies have started adopting ITIL.This is a standard that integrates business with infrastructure.It has resulted in increased customer satisfaction.DBA's have data which is the sole property(heart) of an organization.
So it is mandatory for them to follow the processes and document their activites for the system to be up and running on a 24X7 basis and address issues if one happens.
Tuesday, January 20, 2009
Controlfile Autobackup and RMAN-06172
In this post i will show how to restore CONTROLFILE & SPFILE in different senario
1. when AUTOBACKUP controlfile option is enable/ON and AUTOBACKUP location is also set.
means we are set following two parameter before perform database backup
RMAN>configure controlfile autobackup ON;
RMAN>configure controlfile autobackup format for device type disk to 'F:\B_%F';
NOTE: 'F:\' is a backup location where AUTOBACKUP controlfile or spfile created and "%F" is format.
RMAN> backup database;
Starting Control File and SPFILE Autobackup at 19-JUL-08
piece handle=F:\B_C-1958649224-20080719-03 comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-08
NOTE: above underline and blue colored number is DBID for the database
Now I deleted ALL CONTROLFILES & SPFILE
C:\>del F:\oracle\product\10.1.0\oradata\test\*.CTL
C:\>del F:\oracle\product\10.1.0\Db_1\database\SPFILETEST.ora
1. Restoring SPFILE
RMAN> set dbid=1958649224
RMAN> startup nomount force;
RMAN> set controlfile autobackup format for device type disk to 'F:\B_%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMATRMAN> restore SPFILE from AUTOBACKUP;
Starting restore at 19-JUL-08
using channel ORA_DISK_1channel ORA_DISK_1: looking for autobackup on day: 20080719
channel ORA_DISK_1: autobackup found: F:\B_c-1958649224-20080719-03
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 19-JUL-08
2. Restoring CONTROLFILE
RMAN> restore controlfile from 'F:\B_C-1958649224-20080719-03';Starting restore at 19-JUL-08
using channel ORA_DISK_1channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL01.CTL
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL02.CTL
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL03.CTL
Finished restore at 19-JUL-08
NOTE: It is always recommended to set CONTROLFILE autobackup ON and also set AUTOBACKUP LOCATION AND FOMRAT MASK "%F".
2. Second condition when controlfile autobackup is ON/ENABLE but AUTOBACKUP location and format mask it not set.
means the following parameter is not set.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
NOTE: flashback_recovery_area is set. so controlfile autobackup is created in flashback_recovery_area.
First enable the autobackup controlfile option
RMAN> configure controlfile autobackup on;
RMAN> backup database;
Starting Control File and SPFILE Autobackup at 19-JUL-08
piece handle=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2008_07_19\O1_MF_S_660509348_483ZQ4MF_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-08
NOTE: This time controlfile autobackup is created in FRA.
Now I deleted all controlfile & spfile
RMAN> restore spfile from autobackup;
Starting restore at 19-JUL-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: ===============
ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/19/2008 18:54:49
RMAN-06495: must explicitly specify DBID with SET DBID command
First disadvantage is we can't get DBID becuase controlfile autobackup option is enable for format we are not defined. In this case if it is unix platform we can get DBID from database backupset through backupset location grep MAXVALUE
but I don't know how to find in windows. but here i also keep DBID.
RMAN> set dbid=1958649224
executing command: SET DBID
RMAN> restore spfile from autobackup;
Starting restore at 19-JUL-08
using channel ORA_DISK_1channel ORA_DISK_1: looking for autobackup on day: 20080719
channel ORA_DISK_1: looking for autobackup on day: 20080718
channel ORA_DISK_1: looking for autobackup on day: 20080717
channel ORA_DISK_1: looking for autobackup on day: 20080716
channel ORA_DISK_1: looking for autobackup on day: 20080715
channel ORA_DISK_1: looking for autobackup on day: 20080714
channel ORA_DISK_1: looking for autobackup on day: 20080713
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569:
===============
ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/19/2008 19:17:17
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
Note: above error occur becuase we are not set autobackup location so we need to first set controlfile or spfile backup location, in this case it is FRA.
RMAN> restore spfile from autobackup db_recovery_file_dest='F:\oracle\product\10.1.0\flash_recovery_area' db_name='TEST';Starting restore at 19-JUL-08using channel ORA_DISK_1recovery area destination: F:\oracle\product\10.1.0\flash_recovery_area
database name (or lock name space) used for search: TEST
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2008_07_19\O1_MF_S_660509348_483ZQ4MF_.BKP
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 19-JUL-08
shutdown the database and startup nomout with restored spfile
RMAN> restore controlfile from autobackup;
Starting restore at 19-JUL-08allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISKrecovery area destination: F:\oracle\product\10.1.0\flash_recovery_area
database name (or lock name space) used for search: TEST
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2008_07_19\O1_MF_S_660509348_483ZQ4MF_.BKP
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL01.CTL
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL02.CTL
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL03.CTL
Finished restore at 19-JUL-08
3. when FRA is not configured or autobackup controlfile location & format is not set.
rman target /
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.connected to target database: TEST (DBID=1958649224)RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;using target database controlfile instead of recovery catalog
RMAN configuration parameters are successfully reset to default value
RMAN> configure controlfile autobackup on;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> backup database;Starting backup at 19-JUL-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSTEM01.DBFinput datafile fno=00003 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSAUX01.DBFinput datafile fno=00002 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\UNDOTBS01.DBF
input datafile fno=00004 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 19-JUL-08
channel ORA_DISK_1: finished piece 1 at 19-JUL-08
piece handle=C:\WINDOWS\SYSTEM32\05JLT74O_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 19-JUL-08Starting Control File and SPFILE Autobackup at 19-JUL-08
piece handle=C:\WINDOWS\SYSTEM32\C-1958649224-20080719-03 comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-08
NOTE: This time backupset and autobackup controlfile is created in default windows location: "c:\windows\system32\", interesting autobackup controlfile is created with DBID.
Tuesday, January 13, 2009
Oracle Metalink Notes for Oracle Apps DBAs
Here are the list of Oracle Metalink Notes, I do refer regularly. Moreover, these are very informative. So, I thought of sharing those note ids here with you.
Note: You have to have an account in Oracle Metalink to access notes.
Installation
Note: 452120.1 - How to locate the log files and troubleshoot RapidWiz for R12
Note: 329985.1 - How to locate the Rapid Wizard Installation log files for Oracle Applications 11.5.8 and higher
Note: 362135.1 - Configuring Oracle Applications Release 11i with Oracle10g Release 2 Real Application Clusters and Automatic Storage Management
Note: 312731.1 - Configuring Oracle Applications Release 11i with 10g RAC and 10g ASM
Note: 216550.1 - Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note: 279956.1 - Oracle E-Business Suite Release 11i with 9i RAC: Installation and Configuration using AutoConfig
Note: 294932.1 - Recommendations to Install Oracle Applications 11i
Note: 403339.1 - Oracle 10gR2 Database Preparation Guidelines for an E-Business Suite Release 12.0.4 Upgrade
Note: 455398.1 - Using Oracle 11g Release 1 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 11i
Note: 402311.1 - Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for Microsoft Windows
Note: 405565.1 - Oracle Applications Release 12 Installation Guidelines
AD Utilities
Note: 178722.1 - How to Generate a Specific Form Through AD utility ADADMIN
Note: 109667.1 - What is AD Administration on APPS 11.0.x ?
Note: 112327.1 - How Does ADADMIN Know Which Forms Files To Regenerate?
Note: 136342.1 - How To Apply a Patch in a Multi-Server Environment
Note: 109666.1 - Release 10.7 to 11.0.3 : What is adpatch ?
Note: 152306.1 - How to Restart Failed AutoInstall Job
Note: 356878.1 - How to relink an Applications Installation of Release 11i and Release 12
Note: 218089.1 - Autoconfig FAQ
Note: 125922.1 - How To Find Oracle Application File Versions
Cloning
Note: 419475.1 - Removing Credentials from a Cloned EBS Production Database
Note: 398619.1 - Clone Oracle Applications 11i using Oracle Application Manager (OAM Clone)
Note: 230672.1 - Cloning Oracle Applications Release 11i with Rapid Clone
Note: 406982.1 - Cloning Oracle Applications Release 12 with Rapid Clone
Note: 364565.1 - Troubleshooting RapidClone issues with Oracle Applications 11i
Note: 603104.1 - Troubleshooting RapidClone issues with Oracle Applications R12
Note: 435550.1 - R12 Login issue on target after cloning
Note: 559518.1 - Cloning Oracle E-Business Suite Release 12 RAC-Enabled Systems with Rapid Clone
Note: 216664.1 - FAQ: Cloning Oracle Applications Release 11i
Patching
Note: 225165.1 - Patching Best Practices and Reducing Downtime
Note: 62418.1 - PATCHING/PATCHSET FREQUENTLY ASKED QUESTIONS
Note: 181665.1 - Release 11i Adpatch Basics
Note: 443761.1 - How to check if a certain Patch was applied to Oracle Applications instance?
Note: 231701.1 - How to Find Patching History (10.7, 11.0, 11i)
Note: 60766.1 - 11.0.x : Patch Installation Frequently Asked Questions
Note: 459156.1 - Oracle Applications Patching FAQ for Release 12
Note: 130608.1 - AdPatch Basics
Note::60766.1 - Patch Installation FAQ (Part 1)
Upgrade
Note: 461709.1 - Oracle E-Business Suite Upgrade Guide - Plan
Note: 293166.1 - Previous Versions of e-Business 11i Upgrade Assistant FAQ
Note: 224875.1 - Installation, Patching & Upgrade Frequently Asked Questions (FAQ's)
Note: 224814.1 - Installation, Patching & Upgrade Current Issues
Note: 225088.1 - Installation, Patching & Upgrade Patches Guide
Note: 225813.1 - Installation, Patching & Upgrade Setup and Usage Guide
Note: 224816.1 - Installation, Patching & Upgrade Troubleshooting Guide
Note: 216550.1 - Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note: 362203.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
Note: 423056.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0.2)
Note: 726982.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0.3)
Note: 452783.1 - Oracle Applications Release 11i with Oracle 11g Release 1 (11.1.0)
Note: 406652.1 - Upgrading Oracle Applications 11i DB to DB 10gR2 with Physical Standby in Place
Note: 316365.1 - Oracle Applications Release 11.5.10.2 Maintenance Pack Installation Instructions
Note: 418161.1 - Best Practices for Upgrading Oracle E-Business Suite
Printer
Note: 297522.1 - How to investigate printing issues and work towards its resolution ?
Note: 110406.1 - Check Printing Frequently Asked Questions
Note: 264118.1 - Pasta Pasta Printing Setup Test
Note: 200359.1 - Oracle Application Object Library Printer Setup Test
Note: 234606.1 - Oracle Application Object Library Printer Initialization String Setup Test
Note: 1014599.102 - Subject: How to Test Printer Initialization Strings in Unix
Performance
Note: 390137.1 - FAQ for Collections Performance
Note: 216205.1 - Database Initialization Parameters for Oracle Applications Release 11i
Note: 169935.1 - Troubleshooting Oracle Applications Performance Issues
Note: 171647.1 - Tracing Oracle Applications using Event 10046
Note: 153507.1 - Oracle Applications and StatsPack
Note: 356501.1 - How to Setup Pasta Quickly and Effectively
Note: 333504.1 - How To Print Concurrent Requests in PDF Format
Note: 356972.1 - 11i How to troubleshoot issues with printers
Others
Note: 189367.1 - Best Practices for Securing the E-Business Suite
Note: 403537.1 - Best Practices For Securing Oracle E-Business Suite Release 12
Note: 454616.1 - Export/Import Process for Oracle E-Business Suite Release 12 using 10gR2
Note: 394692.1 - Oracle Applications Documentation Resources, Release 12
Note: 370274.1 - New Features in Oracle Application 11i
Note: 130183.1 - How to Get Log Files from Various Programs for Oracle Applications
Note: 285267.1 - Oracle E-Business Suite 11i and Database FAQ
Note: 453137.1 - Oracle Workflow Best Practices Release 12 and Release 11i
Note: 398942.1 - FNDCPASS Utility New Feature ALLORACLE
Note: 187735.1 - Workflow FAQ - All Versions
Oracle Applications 11i/12 Online Documentation
http://www.oracle.com/technology/documentation/applications.html
Turn on Listener Logging
In order to know, which listener commands were executing, one should enable the logging by the following:
LSNRCTL> set log_directory …/log
LSNRCTL> set log_file Listener_
LSNRCTL> set log_status on LSNRCTL> save_config
Set the Listener Password :
Setting Listener Password is strongly recommended in order to prevent the distinct attacks. There are two ways to do the same. Either by setting PASSWORDS_listenername parameter in listener.ora file (Password will be in a Clear Text – Not Recommended)Or By using lsnrctl set password command (Highly Recommended).
Sunday, January 11, 2009
BLOCK RECOVER BY RMAN
------------------------------------------
BLOCKRECOVER DATAFILE 2 BLOCK 12, 13;
BLOCKRECOVER DATAFILE 2 BLOCK 12,13 DATAFILE 3 BLOCK 5,98,99 DATAFILE 4 BLOCK 19;
Limit block recovery by restoration type
RUN {
BLOCKRECOVER DATAFILE 3 BLOCK 2,3,4,5 TABLESPACE sales DBA 4194405, 4194409, 4194412
FROM DATAFILECOPY;
}
Limit block recovery by backup tag
-----------------------------------
BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404, 4194405 FROM TAG "weekly_backup";
Limit block recovery by time
-----------------------------
BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404, 4194405 RESTORE UNTIL TIME 'SYSDATE-2';
Repair all corrupt blocks in the database
-----------------------------------------
BACKUP VALIDATE DATABASE;BLOCKRECOVER CORRUPTION LIST;
This command will find, list and then recover all corrupted blocks in the database using the gv$database_block_corruption view.
Oracle 10g CRS upgrade to 11g CRS
To upgrade Oracle 10g CRS to 11g CRS, we have two options -
- Perform a rolling upgrade (this requires that your current CRS version >=10.2.0.3 (or 10.2.0.2 with Bundle patch). This option allows us to upgrade the CRS without a complete unavailabilty of downtime
- Upgrade the CRS on all the nodes at the same time with complete downtime.
On Node 1:
1. Stop the CRS: Either use $CRS_HOME/bin/crsctl stop crs as root or run the following from the staging as root:
/
2. Invoke runInstaller from the staging area:
3. As you notice, the option to specify the Home and the destination directory is disabled since OUI has detected the existence of a Cluster.
4. Select the local node (in my case it is alps01), selecting both the nodes requires the CRS to be down on both the nodes - meaning it would require a complete downtime - and would no more be a rolling upgrade:
5. Not shutting down the CRS on the local node would cause the following error:
6. This information would appear in the install logs - pretty detailed and informative :-)
7. Once you shutdown the CRS on the local node and when re-attempted, it should be fine (no errors or warnings):
8. On clicking next, the summary screen would appear. Note that we are upgrading the CRS only the local node (alps01):
9. Install progress screen:
10. At the end of the upgrade, the Installer would prompted us to run the rootupgrade script as root:
11. Output of the rootupgrade script:
12. Check the status of CRS versions:
On Node 2:
13. Shutdown the CRS on node 2. Note that while performing a rolling upgrade, we have to invoke the OUI from the second node and this can't be done from node 1.
14. Invoke the OUI from node 2 and select only the local node and deselect the remote or first node:
15. Cluster Verification screen....looks good.
17. Prompt to run rootupgrade script after the upgrade on Node 2:
18. Output of rootupgrade script on Node 2:
[root@everest02 upgrade]# /opt/oracle/product/crs/install/rootupgrade
Checking to see if Oracle CRS stack is already up...
WARNING: directory '/opt/oracle/product' is not owned by root
WARNING: directory '/opt/oracle' is not owned by root
Oracle Cluster Registry configuration upgraded successfully
Adding daemons to inittab
Attempting to start Oracle Clusterware stack
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Cluster Synchronization Services daemon has started
Event Manager daemon has started
Cluster Ready Services daemon has started
Oracle CRS stack is running under init(1M)
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node
node 2: everest02 everest02-priv everest02
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
clscfg -upgrade completed successfully
CRS stack on this node, is successfully upgraded to 11.1.0.6.0
Checking the existence of nodeapps on this node
Creating '/opt/oracle/product/crs/install/paramfile.crs' with data used for CRS configuration
Setting CRS configuration values in /opt/oracle/product/crs/install/paramfile.crs
[root@everest02 upgrade]#
19. Check or verify the version of CRS:
Configuring SSH
While logged in as oracle perform the following on each of the nodes of the RAC:
cd $HOME mkdir ~/.ssh chmod 700 ~/.ssh /usr/bin/ssh-keygen -t rsa /usr/bin/ssh-keygen -t dsa
On Node 1:
cd $HOME/.ssh cat id_rsa.pub >> authorized_keys cat id_dsa.pub >> authorized_keys Copy the authorized_keys file to the node 2. scp authorized_keys node2:/opt/oracle/.ssh
On Node 2:
cd $HOME/.ssh cat id_rsa.pub >> authorized_keys cat id_dsa.pub >> authorized_keys scp authorized_keys node1:/opt/oracle/.ssh
Now perform a ssh between all the nodes including the node-priv hostnames as well. Check to make sure that ssh is configured well without prompting for the password (on both the nodes):
Sun Aug 12 08:41:42 CDT 2007
$ ssh pepsi.pinnacle.com date
Sun Aug 12 08:42:23 CDT 2007
$ ssh coke-priv date
Sun Aug 12 08:42:45 CDT 2007
$ ssh pepsi-priv date
Sun Aug 12 08:43:22 CDT 2007