How To Check Oracle EBS Version
Somehow we should know what version we running in. There’s two ways to check it out :
1. On the application just click : Help -> About Oracle Application
2. Run this simple query : “SELECT release_name FROM app.fnd_product_groups”
Cheers 😀
Check Status RMAN Backup
Some how, we need to check status our RMAN backup, so these the script :
- To check full and incremental RMAN backup with archivelog :
col status format a9
col hrs format 999.99
SELECT
session_key,input_type, status,
to_char(start_time,’mm/dd/yy hh24:mi’) start_time,
to_char(end_time,’mm/dd/yy hh24:mi’) end_time,
elapsed_seconds/3600 hrs
FROM v$rman_backup_job_details ORDER BY session_key;
- Tp check full and incremental backup, not archivelog backup
col status format a9
col hrs format 999.99
SELECT
session_key, input_type, status,
to_char(start_time,’mm/dd/yy hh24:mi’) start_time,
to_char(end_time,’mm/dd/yy hh24:mi’) end_time,
elapsed_seconds/3600 hrs
FROM v$rman_backup_job_details
WHERE input_type=’DB INCR’ ORDER BY session_key;
Resuming Screen
I usually using screen command for my work on machine. It works when I lost my connection to my machine, my job could be continued. But some case, my screen not detached. This is my solution when the screen still attached. Fist of all, check your screen status with command “screen -ls”
- Use screen -D. Just type “sceen -d (ppt/tty number)”
- If number 1 failed. So you need to kill the process. What process number?
“ps -ef | grep screen”
noted the number where the pty/tty is ” screen -r 123.tty.DEVMRCAP”
“ps -ef | grep (process number)
noted the process number
“kill -9 (process number)
Believe me, it works 😀
Troubleshoot : Can’t Access DB From Some Node/TOAD
Symptom :
Yesterday I had case, my developer can’t access db with TOAD but he can access it on machine.
Solutin :
Check : $TNS_ADMIN/sqlnet.ora
There are lines :
tcp.validate_checking = yes
tcp.invited_nodes = ….
First line is sett db should check node who can access db. yes means db machine would check who access the db.
Second line is define nodes who accepted to access db.
Both of lines is addition, after install db, the lines is not exist, we should add those lines for security issue.
How To Change Sysadmin Password
Sometime we lost sysadmin password. So this article i will define steps of “how we can change sysadmin user password” .For to can change sysadmin user password, we have 2 options:
1. We can use E-Business suite screens (if we know old password)
2. We can use FNDPASS utulity(whether we have old password or not)
Please follow below steps for can change sysadmin user password:
1. If we want to change sysadmin user password with E-Business suite
Login to EBS than:
System Administrator -> User -> Query the desired USERNAME and in password tab enter the new password.
To test,relogin into the applications as the SYSADMIN user and thenew password.
Verify that a concurrent program such as Active Users runs.
2. If we want to change sysadmin user password with FNDPASS utulity
Keep all services running.
Login OS level by way of the applmgr user and source(run)n your environment scripts
cd $APPL_TOP
run APPSORA.env
cd $FND_TOP/bin
Run FNDCPASS to change the SYSADMIN password
FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN <New Password>
Example:
FNDCPASS apps/apps 0 Y system/manager USER sysadmin/sysadmin123
Restart the Apache, Forms, Reports, 8.0.6 listener and concurrent manager services. << This step not mandotary,but recommended
To test, login to EBS as the SYSADMIN user with new password.
Verify that a concurrent program such as Active Users runs.
Reference:
How To Change The Password For The SYSADMIN User [ID 423274.1]
How to Change Applications R12 Passwords using Applications Schema Password Change Utility (FNDCPASS)? [ID 437260.1]
Report Error to Excel/PDF
Today I get one case from my team who implement EBS in PGN (Perusahaan Gas Negara).
Issue :
When create report and try to open in excel, he got error message : “REP-3000: Internal error starting Oracle Toolkit”
These how step by step I solved the issue :
- Log in to apps server as root.
- Check inittab file : more /etc/inittab, see the id mode, if mode 5 by looking this line “id:5:initdefault”.
- I found inittab mode was 5, so :
-
#export DISPLAY=:0.0
-
#xhost +
- Modify the context file.
-
#vi /oracle/DEV/devappl/admin/PROD_devmrcap.xml (*vary depend your installation)
- Find DISPLAY and change to : “<DISPLAY >:0.0</DISPLAY>” *something like that.
- Log in with application user.
- Run environment.
- Shutdown application by :
#cd $COMMON_TOP/admin/scripts/
#sh adstpall.sh apps/apps
10. Make sure all process were stop by : #ps -ef | grep FNDLIBR ,they should be one process.
11. Run autoconfig : #sh adautocfg.sh apps/apps.
12. Startup application by : #sh adstral.sh apps/apps.
Tuning Oracle Database
There many ways to tuning oracle database, one of them by using third party application. The name is Toad . I use Toad version 9.7.0.51.
Toad has many features, we need these three feature at minimum.
- ADDM/ADDR Reports
- Location : Database – Monitor – ADDM/AWR Reports
- Function : It will tell us if any slow query appears. So we need to check the query then.
- Oracle Tuning Advisor
- Location : Database – Optimize – Oracle Tuning Advisor
- Function : It will tess how to tune query. We neet do know query before and write on it. Then OTA will explain tune posibilities for query. For example : create index, remove sub-query, etc.
- Session Browser
- Location : Database – Monitor – Session Browse
- Function : It help when we need watch running query online. We need execute program who slow performance then we need to see every query on it.
There many ways to tune database, I will write it later.
Preparing OEL 5 for Oracle EBS r12
Hi, I do preparation for installing oracle EBS on OEL 5. My environment are in Virtual Box. After installing OEL 5, what I do is :
- Setup reproforge configuration following these steps :
# wget http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.2-2.el5.rf.x86_64.rpm # rpm -Uvh rpmforge-release-0.5.2-2.el5.rf.x86_64.rpm source : https://forums.oracle.com/forums/thread.jspa?threadID=2332788
- Setup yum configuration :
# cd /etc/yum.repos.d # wget http://public-yum.oracle.com/public-yum-el5.repoopen with editor (vi) : # vi /etc/yum.repos.d/public-yum-el5.repo change : enable=0 to enable=1 (all of repo) #yum list (for updating repository to public yum) source : http://public-yum.oracle.com
I need to read my external hardisk which formatted by ntfs, so I need to install fuse and ntfs-3g packages :
#yum install fuse
#yum install ntfs-3g
These two steps for easy step to install anything from repository.
After that I extracted file Staging R12.
#cd Source
#tar xfvz StageR12.tar
(to be continued) 😀
View Previous Backup On SQL Server
The case is I try to restore db through full backup on differential backup. I failed when restore diffential backup. With this query I found last full backup of db.
SELECT
CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN ‘D’ THEN ‘Database’
WHEN ‘L’ THEN ‘Log’
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() – 7) AND database_name=’FMS_JASARAHARJA’
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
Voila, u can find the location of your last backup.
Install Oracle Enterprise Linux 5.1 x64
INSTALL ORACLE ENTERPRISE LINUX 5.1 x64
Edit /etc/hosts. The file should contain text similar to the following:
127.0.0.1 localhost.localdomain localhost 10.10.10.180 otm6.jatis.com otm6
Create the Oracle Groups and User Account
Next, create the Linux groups and user account that will be used to install and maintain the Oracle Database 11g Release 1 software. The user account will be called oracle, and the groups will be oinstall and dba. Execute the following commands as root:
/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/useradd -m -g oinstall -G dba oracle
Set the password on the oracle account:
passwd oracle Ex: # passwd oracle Changing password for user oracle. New password: Retype new password: passwd: all authentication tokens updated successfully.
Prerequisite 64-bit (x86_64) Installations
Required kernel version: 2.6.18 This kernel, or any of the kernels supplied in updates, works with Oracle Database 11g Release 1 .
Check your kernel version by running the following command:
uname -rm Ex: # uname -rm 2.6.18-8.el5 x86_64
Required package versions (or later):
- binutils-2.17.50.0.6
- compat-libstdc++-33-3.2.3
- compat-libstdc++-33-3.2.3 (32 bit)
- elfutils-libelf-0.125
- elfutils-libelf-devel-0.125
- gcc-4.1.1
- gcc-c++-4.1.1
- glibc-2.5-12
- glibc-2.5-12 (32 bit)
- glibc-common-2.5
- glibc-devel-2.5
- glibc-devel-2.5-12 (32 bit)
- libaio-0.3.106
- libaio-0.3.106 (32 bit)
- libaio-devel-0.3.106
- libgcc-4.1.1
- libgcc-4.1.1 (32 bit)
- libstdc++-4.1.1
- libstdc++-4.1.1 (32 bit)
- libstdc++-devel 4.1.1
- make-3.81
- sysstat-7.0.0
Note that there are a number of packages where both the 64-bit and 32-bit RPMs must be installed. Fortunately, both are provided on the 64-bit Linux installation media.
If you’ve performed a “default RPMs” install as suggested by Oracle, there are still some required packages that must be installed:
- compat-libstdc++-33-3.2.3
- compat-libstdc++-33-3.2.3 (32 bit)
- elfutils-libelf-devel-0.125
- gcc-4.1.1
- gcc-c++-4.1.1
- glibc-devel-2.5
- glibc-devel-2.5-12 (32 bit)
- libaio-devel-0.3.106
- libstdc++-devel 4.1.1
- sysstat-7.0.0
To install the remaining packages, mount the Oracle Enterprise Linux media and move to the directory containing the RPMs.
Some required packages are dependent upon other packages, so the dependant packages must be installed as well. Login as root and run the following commands to install the remaining required packages and their dependent packages:
Mount CD 2
mount -t iso9660 -r /dev/cdrom /media
cd /media/Server
rpm -ivh elfutils-libelf-devel*.x86_64.rpm glibc-devel-2* gcc-4*.x86_64.rpm gcc-c++-4*.x86_64.rpm libstdc++-devel-4*.x86_64.rpm glibc-headers*.x86_64.rpm libgomp*.x86_64.rpm
Ex:
# rpm -ivh elfutils-libelf-devel*.x86_64.rpm glibc-devel-2* gcc-4*.x86_64.rpm gcc-c++-4*.x86_64.rpm libstdc++-devel-4*.x86_64.rpm glibc-headers*.x86_64.rpm libgomp*.x86_64.rpm
warning: elfutils-libelf-devel-0.125-3.el5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing… ########################################### [100%]
1:glibc-headers ########################################### [ 11%]
2:glibc-devel ########################################### [ 22%]
3:libgomp ########################################### [ 33%]
4:gcc ########################################### [ 44%]
5:libstdc++-devel ########################################### [ 56%]
6:gcc-c++ ########################################### [ 67%]
7:glibc-devel ########################################### [ 78%]
8:elfutils-libelf-devel-s########################################### [ 89%]
9:elfutils-libelf-devel ########################################### [100%]
cd /
eject
Mount CD 3
mount -t iso9660 -r /dev/cdrom /media
cd /media/Server
rpm -ivh compat-libstdc++-33* libaio-devel*.x86_64.rpm
Ex:
# rpm -ivh compat-libstdc++-33* libaio-devel*.x86_64.rpm
warning: compat-libstdc++-33-3.2.3-61.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing… ########################################### [100%]
1:libaio-devel ########################################### [ 33%]
2:compat-libstdc++-33 ########################################### [ 67%]
3:compat-libstdc++-33 ########################################### [100%]
cd /
eject
Mount CD 4
mount -t iso9660 -r /dev/cdrom /media
cd /media/Server
rpm -ivh sysstat*
Ex:
# rpm -ivh sysstat*
warning: sysstat-7.0.0-3.el5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing… ########################################### [100%]
1:sysstat ########################################### [100%]
To verify that the required packages have been installed on your system, run the following command:
rpm -q –queryformat “%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n” binutils \
compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc \
glibc-common glibc-devel-2.5 libaio libaio-devel libgcc libstdc++ libstdc++-devel \
make sysstat
Ex:
# rpm -q --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" binutils \
> compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc \
> glibc-common glibc-devel-2.5 libaio libaio-devel libgcc libstdc++ libstdc++-devel \
> make sysstat
binutils-2.17.50.0.6-2.el5 (x86_64)
compat-libstdc++-33-3.2.3-61 (i386)
compat-libstdc++-33-3.2.3-61 (x86_64)
elfutils-libelf-0.125-3.el5 (x86_64)
elfutils-libelf-devel-0.125-3.el5 (x86_64)
gcc-4.1.1-52.el5 (x86_64)
gcc-c++-4.1.1-52.el5 (x86_64)
glibc-2.5-12 (i686)
glibc-2.5-12 (x86_64)
glibc-common-2.5-12 (x86_64)
glibc-devel-2.5-12 (x86_64)
glibc-devel-2.5-12 (i386)
libaio-0.3.106-3.2 (x86_64)
libaio-0.3.106-3.2 (i386)
libaio-devel-0.3.106-3.2 (x86_64)
libgcc-4.1.1-52.el5 (x86_64)
libgcc-4.1.1-52.el5 (i386)
libstdc++-4.1.1-52.el5 (x86_64)
libstdc++-4.1.1-52.el5 (i386)
libstdc++-devel-4.1.1-52.el5 (x86_64)
make-3.81-1.1 (x86_64)
sysstat-7.0.0-3.el5 (x86_64)
To check that all installed rpm -qa
Cara Menghapus di LINUX
Configuring the Linux Kernel Parameters
Oracle Database 11g Release 1 requires the kernel parameter settings shown below. The values given are minimums, so if your system uses a larger value, don’t change it. Linux allows modification of most kernel parameters while the system is up and running, so there’s no need to reboot the system after making kernel parameter changes.
kernel.shmall = 2097152 kernel.shmmax = 536870912 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 65536 net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default=4194304 net.core.wmem_default=262144 net.core.rmem_max=4194304 net.core.wmem_max=262144
Note that OEL 5 already has values defined for kernel.shmall and kernel.shmmax. Use the default values if they are the same or larger than the required values.
If you’re following along and have just installed Linux, the kernel parameters will all be at their default values and you can just cut and paste the following commands while logged in as root.
cat >> /etc/sysctl.conf <<EOF kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 65536 net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default=4194304 net.core.wmem_default=262144 net.core.rmem_max=4194304 net.core.wmem_max=262144 EOF /sbin/sysctl -p Ex: # cat >> /etc/sysctl.conf <<EOF net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default=262144 > kernel.shmmni = 4096 > kernel.sem = 250 32000 100 128 > fs.file-max = 65536 > net.ipv4.ip_local_port_range = 1024 65000 > net.core.rmem_default=4194304 > net.core.wmem_default=262144 > net.core.rmem_max=4194304 > net.core.wmem_max=262144 > EOF # /sbin/sysctl -p net.ipv4.ip_forward = 0 net.ipv4.conf.default.rp_filter = 1 net.ipv4.conf.default.accept_source_route = 0 kernel.sysrq = 0 kernel.core_uses_pid = 1 net.ipv4.tcp_syncookies = 1 kernel.msgmnb = 65536 kernel.msgmax = 65536 kernel.shmmax = 4294967295 kernel.shmall = 268435456 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 65536 net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default = 4194304 net.core.wmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_max = 262144
Run the following commands as root to verify your settings:
/sbin/sysctl -a | grep shm /sbin/sysctl -a | grep sem /sbin/sysctl -a | grep file-max /sbin/sysctl -a | grep ip_local_port_range/sbin/sysctl -a | grep rmem_default/sbin/sysctl -a | grep rmem_max/sbin/sysctl -a | grep wmem_default/sbin/sysctl -a | grep wmem_maxEx: # /sbin/sysctl -a | grep shm kernel.shmmni = 4096 kernel.shmall = 268435456 kernel.shmmax = 4294967295 kernel.shm-use-bigpages = 0 # /sbin/sysctl -a | grep sem kernel.sem = 250 32000 100 128 # /sbin/sysctl -a | grep file-max fs.file-max = 65536 # /sbin/sysctl -a | grep ip_local_port_range net.ipv4.ip_local_port_range = 1024 65000 # /sbin/sysctl -a | grep rmem_default net.core.rmem_default = 4194304 # /sbin/sysctl -a | grep rmem_max net.core.rmem_max = 4194304 # /sbin/sysctl -a | grep wmem_default net.core.wmem_default = 262144 # /sbin/sysctl -a | grep wmem_max net.core.wmem_max = 262144
Setting shell limits for the oracle user
Oracle recommends setting limits on the number of processes and open files each Linux account may use. To make these changes, cut and paste the following commands as root:
cat >> /etc/security/limits.conf <<EOF oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536 EOF cat >> /etc/pam.d/login <<EOF session required pam_limits.so EOF
Change the default profile for bash and ksh as well as the default login script for cshell.
cat >> /etc/profile <<EOF if [ \$USER = "oracle" ]; then if [ \$SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi umask 022 fi EOF cat >> /etc/csh.login <<EOF if ( \$USER == "oracle" ) then limit maxproc 16384 limit descriptors 65536 umask 022 endif EOF
Edit /home/oracle/.bash_profile
Add these lines:
umask 022 ORACLE_BASE=/u01/app/oracle ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1 ORACLE_SID=orcl PATH=$ORACLE_HOME/bin:$PATH export PATH export ORACLE_BASE ORACLE_HOME ORACLE_SID
Using RPMforge
This will automatically install the configuration and GPG keys that are for safely installing RPMforge packages.
Please select the correct command from the following list:
Supported
- Red Hat Enterprise Linux 5 / i386:
- Red Hat Enterprise Linux 5 / x86_64:
- Red Hat Enterprise Linux 4 / i386:
- Red Hat Enterprise Linux 4 / x86_64:
- Red Hat Enterprise Linux 3 / i386:
- Red Hat Enterprise Linux 3 / x86_64:
- Red Hat Enterprise Linux 2 / i386:
- Red Hat Linux 9 / i386:
- Red Hat Linux 7.3 / i386:
If you’ve done that, the rest is simple. Upgrade your system by doing:
yum update
You can add new software by typing:
yum install <name of package>
Or update installed software:
yum update <name of package>
Or search for software in the local repository meta-data:
yum search <keyword>
Or simply list all available software:
yum list available
From time to time you may want to save some diskspace:
yum clean
Install ORACLE Database 11g
Log in using the oracle account.
Change directory to the directory containing the Oracle Database 11g Release 1 software.
Ex: $ cd $HOME/11gR1_db/database
Start the Oracle Universal Installer.
$ ./runInstaller
- Select Installation Method
- Select Basic Installation
- Oracle Base Location: /u01/app/oracle
- Oracle Home Location: /u01/app/oracle/product/11.1.0/db_1
- Installation Type: Enterprise Edition (3.3GB)
- UNIX DBA Group: dba
- Make sure Create Starter Database is checked
- Global Database Name: demo1
- Enter the Database Password and Confirm Password
- Click on Next
- Specify Inventory Directory and Credentials
- Inventory Directory: /u01/app/oraInventory
- Operating System group name: oinstall
- Click on Next
- Product-specific Prerequisite Checks
- If you’ve been following the steps in this guide, all the checks should pass without difficulty. You may receive a warning related to swap file size if the default partition layout was used during OEL5 installation. This may be safely ignored . If one or more checks fail, correct the problem before proceeding.
- Click on Next
- Oracle Configuration Manager Registration
- Leave this disabled for the demo and click on Next
- Summary
- A summary of the products being installed is presented.
- Click on Install.
- Install
- Installation progress is displayed
- Execute Configuration Scripts
- At the end of the installation, a pop up window will appear indicating scripts that need to be run as root. Login as root and run the indicated scripts.
- Click on OK when finished.
- End of Installation
- Make note of the URLs presented in the summary, and click on Exit when ready.
- Congratulations! Your new Oracle Database 11g Release 1 database is up and ready for use.
INSTALLATION p6890831_111070_Linux-x86-64
CREATE DATABASE 11g
Improve file system performance by mounting them with the “noatime” parameter in /etc/fstab
O7_DICTIONARY_ACCESSIBILITY is a static parameter. So if you use spfile then after setting it by
SQL>ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=TRUE SCOPE=SPFILE; you need to restart your database in order to take affect.
SQL> alter session set nls_length_semantics=char;
alter system set open_cursors=1000 (any higher value) scope=both sid=’*’;
or
If you want to use initi.ora file, then you can create one using spfile or if it already exists you can modify it
to create pfile
Login as sysdba
sql> create pfile=’/location/init.ora’ from spfile;
File Created;
Cd to location and edit init.ora file
*.open_cursrors=1000 # higher value
sql> shutdown immediate
sql> starutp mount pfile=’/location/init.ora’;
sql> alter database open;
sql> show parameter open_cursors;
ALTER SYSTEM SET open_cursors =1000 SCOPE=BOTH;
Journal DBA 