11.1.17

How to find the date when a role was created?

The view DBA_ROLES doesn’t have the created date column to display when the role was created, but the information is stored in sys.user$ the underlying table. So using the following query one can find the date the role was created.

You need to SYSDBA role to get the information.
SQL> select name, to_char(ctime, ‘DD-MON-YYYY HH24:MI:SS’) from sys.user$ where name = ‘RESOURCE’;
NAME TO_CHAR(CTIME,’DD-MON-YYYYHH24:MI:SS’)
——– —————————————–
RESOURCE 11-JAN-2017 00:42:50
on No comments:

30.12.16

Installation guide of Oracle Database 11g Release 2 on Linux and Windows PART 1

In this I am going to explain in detail about installation of DATABASE on LINUX
As we all know Database installation is easy task on Windows. On other hand Linux is little complex as we need to do many  step before installation.

Step1:- 

Download the DB setup from  https://www.oracle.com/downloads

Step2:- 
After downloading the setup. We need to prepare our OS (windows and linux)

1) Make an entry in "/etc/hosts" for linux and "C:\Windows\System32\drivers\etc\hosts" for windows like below :-
     127.0.0.1       localhost.localdomain  localhost
     XXX.XXX.XXX.XXX         test.domain            test

 Where XXX.XXX.XXX.XXX is IP of your machine.

2) No Need to do anything for windows After this. On linux you need to check the packages. Below are the packages that should be there in the Linux server.

 For Linux : You can create all the packages by using oracle-validated. For this follow instruction at http://public-yum.oracle.com .
               e.g. # yum install oracle-validated
  
This will create everything for you. Your users and evrything.

If you are not going to install it by oracle-validated then below list is for importatnt packages that is needed and below changes in some important files.
  binutils-2.*
  compat-libstdc++-33*
  compat-libstdc++-33*.i386.rpm
  elfutils-libelf*
  gcc-4.*
  gcc-c++-4.*
  glibc-2.*
  glibc-common-2.*
  glibc-devel-2.*
  glibc-headers-2.*
  ksh*
  libaio-0.*
  libaio-devel-0.*
  libgomp-4.*
  libgcc-4.*
  libstdc++-4.*
  libstdc++-devel-4.*
  make-3.*
  sysstat-7.*
  unixODBC-2.*
  unixODBC-devel-2.*
  numactl-devel-*

Note:- for windows no need to anything. 

Step3:- 

Go to /etc/sysctl.conf and amend the changes in /etc/sysctl.conf file.

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

After setting the changes you need to run "/sbin/sysctl -p"


Add the following lines to the "/etc/security/limits.conf" file.

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  4096
oracle              hard    nofile  65536
oracle              soft    stack   10240

Step4:-  

If you havn't run ORACLE_VALIDATE then Create the new groups and users.
click here
Check

Also for linux one more change is needed.Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=permissive
Note:- for windows please turn down the Firewall of the system and make sure directories having good permisons for read and write.


Step5 :- Log into the OS and set DISPLAY for LINUX.


Step 6:- Now go to the setup and unzip the setup and for windows simply double click on runInstaller.
and for linux just go to the directory by using CD and run ./runInstaller.

I will update second blog with images for complete installation.
on No comments:

29.12.16

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor


How to solve ORA-12505, Listener does not currently know of SID  


This is very common error and really it will suck your blood if you are not right path. I will try to explain you about this error.

As you can see this error is showing itself that listener is up and running but not serving  the SID that it is meant for. And yes 1st you need to check where you are facing this error. Is it on client side or on server side.

So what is the SID this listener looking for?

SID is name for your database or you can say service identifier, That is help to to identify the the service like we use PROD for production UAT for testing server. SID of Database should be identical to avoid the confusion. It's length is 8 character only that means you can not extend it more then 8 character.

Now how listener works ?

As we all know its like a car, That drop us from airport to hotel, hahah sorry I am not good in examples. So let me explain technically, whenever you try to make connection from a client machine to the DB server rather than connecting directly oracle have this utility called listener. As you can understand by name it self, yes yes it is going to listen your reqst and then pass that to DB. wow great right.?


Now what if you are taking a taxi to some hotel and that hotel does not exist? Same happens with this error. Taxi driver don't know where you are going so it will through you the error :-

"ORA-12505, TNS:Listener does not currently know of SID given in connect descriptor"


How to solve this "ORA-12505, TNS:listener does not currently" ?



It's very easy. Tell the taxi driver name of hotel that really exist. Fare enough right?

OK Tips for troubleshooting.

We all know that listner.ora and tnsnames.ora  are two files that are involved in this case. and location of these files are $ORACLE_HOME\network\admin

Now check your tnsnames.ora that SID is mentioned is correct or not. Like for my TEST DB is should be like below :-
TEST_TNS = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=*******)(PORT=0000)))(CONNECT_DATA=(SERVICE_NAME=TEST)(INSTANCE_NAME=TEST)))


You can change the format also but i like this way. and yes PORT can't be 0000. ;)

Now you know SID service name TEST is listening at 0000 port number.

If I have set accurate entries than you shouldn't face any issue. So Please check these entries. Also check listner.ora file too. If there is mismatch in entries. Than you going to face same issue

That will solve your problem.

Keep sharing. Keep smile ;)
on

Oracle 10 days rule and how it works

Oracle's “Ten Days Rule” ?? I know it's very confusing and hard to understand " Oracle Ten Days Rule”. I will try to explain these "Ten Days Rule”. Please check my post on oracle auditing risks too. Oracle's “Ten Days Rule” is very confusing not even for us but sometimes for oracle's sales representatives too. You have to pay a lot of money means a lot if you follow them wrongly. We all may think that by these "Oracle 10 day rules" we can run our DR(disaster recovery) server for ten days without purchasing any license. Let me tell you, This is totally a myth.


I was working for one of our clients in INDIA and They were so confident about these Ten-day rules that they are under these rules. After discussing with their IT mgr, We come to the point that yes they were totally confused with this "Oracle 10 days rule".


So What is the Oracle 10 day rule?

 
These "Ten Days Rule” are actually very simple. It can apply to fail-over situations and yes it's a very important part. These "Ten Days Rule” can apply to active/passive hardware where oracle is installed on both of your nodes or you are sharing the storage. We know what cluster services we are using like Failsafe for Microsoft (similar to RAC), OCRS ( Oracle Cluster Ready Services), Oracle RAC, etc. . Like for example our primary server got fails and we have a fail-over server that means our services will get less impacted because of any crash. The good part of the oracle is, They will allow you to install binaries on fail-over sites without any licensing cost for that node for only 10 days.


But according to this 10 days rule, you need to buy the licenses for your fail-over site/server if and only if it is up and running for more than 10 days in a given calendar year. That means if you have used all ten days in the starting year then you cannot use this for the rest of the year.


Also, the interesting part is if your primary server got failed at 11:30 pm Saturday and you moved to fail-over at 11:35 pm and after the help of your DBA's and your Team you manage to get back to the primary site by 02:00 am i.e. Sunday. So this will count for two days. haha, interesting right? So by using just approx one and a half hours, We have just used our two days out of ten days. Really crazy,
So now we know that Oracle 10 days rule will work on calendar days, not on time.


The ten days rule is also confused with Oracle’s license policy for testing backups. In this case, we can install Oracle on a different server and check backup that is reliable or not. But after checking the backup we need to shut down the servers until you need to test the backup again. Also, the interesting part in this is, you cannot keep checking your database backup, again and again, every other month. There is one restriction and that is, you can only start and check that server to check your backup four times, and you cannot test the scenario for more than 2 days regularly, yeah of course in any given calendar of the year.


I hope it will help you to avoid the trouble, As we already paying much to oracle and don't want to pay them more ;). Please comment on this post if you find anything wrong.

Points added from comments:- 

 xavier :- well, you can not use this rule in site failover. This rule is only applicable to clusters with shared logical storage localized in a single data center.


Keep sharing, keep the smile.
on

Oracle character AL32UTF8

The character set determines what languages can be represented in the database.


Oracle recommends using Unicode (AL32UTF8) as the database character set. AL32UTF8 is Oracle's name for the UTF-8 encoding of the Unicode standard. The Unicode standard is the universal character set that supports most of the currently spoken languages of the world. The use of the Unicode standard is indispensable for any multilingual technology, including database processing.
Changing the database character set is a time consuming and complex project. Therefore, it is very important to select the right character set at installation time.

If the language is American English or a Western European language, then the default character set is WE8MSWIN1252. Each Microsoft Windows ANSI Code Page can store data from only one language or a limited group of languages, such as only Western European, or only Eastern European, or only Japanese.

AL32UTF8 is a multibyte character set, database operations on character data may be slightly slower when compared to single-byte database character sets, such as WE8MSWIN1252. Storage space requirements for text in most languages that use characters outside of the ASCII repertoire are higher in AL32UTF8 compared to legacy character sets supporting the language. Note that the increase in storage space concerns only character data and only data that is not in English. The universality and flexibility of Unicode usually outweighs these additional costs.
on No comments:

ORACLE AUDIT SAFETY STEPS

HOW To GET PREPARED FOR ORACLE AUDIT


Oracle Audit is like earthquake. We all have faced Oracle audit in our career. I want to share below steps those can be very helpful when you faced Audit. I will try to elaborate all points in simple and easy way.

I am not saying that after reading this you will easily clear the audit but by using following steps but you may minimize the risks and time of the AUDIT. I am working from last 5 years as ORACLE APPS DBA and I know its not long time :).

We need to work proactively in case of oracle AUDIT. One secret is, always keep AUDIT in your mind before implementing anything new like H/W changes. As we all know ORACLE is not having very friendly reputation e.g. you will never get a perfect solution for licencing .Many cases are there in market where sales guys sold meaning less software to costumers (ORACLE). OK that is a different story.

Let's get back to the topic.

Prepare your self

1. Be careful about the HARDWARE up gradation.


This is very important and you need to take more care about H/W. For example If we upgrade our DB server with One more CORE,We all know that this is going to cost us in DB licensing. Also Oracle will always want to know what your new platform is or what have you changed after licence agreement, This will be Oracle 's first check to you. In this case either you need to give them all information proactively or an audit is coming. So be aware of hardware up-gradation.

2. virtual software in your Oracle data centers also can cause 


Check Your data center of virtual software. If you are now using any virtualization technologies that were not previously in your Oracle environment, it will certainly lead to additional licensing needs. And Yes its sad but you need to pay to oracle. So be careful about the virtualization in ORACLE DC.

3. Oracle 10 days rule is trap in some cases 


We all believe in Oracle 10 days rule right? sometimes that work like trap. Many cases reported that Oracle charge a lot of money to some costumers because they took 10 days rule wrongly.Also,make sure you know your environment before Oracle looks into this.You need to clear the doubts against Oracle 10 days rule.

I will explain Oracle 10 days rule in different post.

4.Keep checking your license agreement metrics. 

In the gap of three or six months keep eye on your license agreement metrics and your support renewals as this will play a major role in auditing. It will be ease for you and for oracle as well.

5. we careful about your production clones and test/development environment


Its very normal scenario, We all keep multiple sets of production like for an example we use to keep backup server and DR server which we think we are safe with (myth) and always try to hide (most of the cases failed) and also on last UAT and DEV servers. If you are using mentioned scenarios then I suggest you to read licence agreement very carefully. I know time is valuable asset you have and its worth more then money but you need spend some of your valuable time to save real money. To be honest that money is real big.

6. Keep eye on your third party vendors 


I know we all choose our vendors very carefully because a lot of money involved and also sometime we don't want extra pressure of DB on our shoulders so we hire a third party vendor. That is itself a challenge now a days, because Oracle licence policy its self very complex. If your vendor is changing anything on DB side and that is impacting the licensing you need to be aware and keep asking your vendor about this.

As till 5 years of experience, I have seen many clients, they really don't care about licencing, Some of them using DB on production without licences.Yes it is true. Oracle is going to catch them soon that i know.

Anyways this all i know . If you guys want any changes then please drop message. I will consider or your point on this post. So it will be easy for others too.

Keep sharing ;) keep troubleshooting  :)
on No comments:

How to set Oracle password to never expire

ORA-28002: the password will expire within x days


Many times we face issue of user password expiry in the DB. For this problem you can assign UNLIMITED limit to the user. This is not exactly for the user, but it’s for the profile in which your DB user exists. 

You can easily change the profile of users in oracle DB. By default the profile is DEFAULT that is having unlimited limit for Password i.e  PASSWORD_LIFE_TIME  .

Let’s use this in practical 

For example, we have new developer joined our company and he/she needs a DB user or you can say fresher. So what happened is, he/she most of the cases she tried to login with wrong password and got DB account locked.

For this case follow the following steps :-

You need to connect with the sysdba privileges.

SQL> conn sys/**** as sysdba

SQL> alter user test identified by test account unlock;

Now the 2nd case, we need to change the limit of our PROFILE that we have created to avoid the AUDIT issues. E.g. TEST is the default profile.


ERROR:
ORA-28002: the password will expire within x days

First check the status and profile of the USER by below sql :-

SQL> select username, account_status, PROFILE from dba_users where username='TEST';

Now you know that TEST is user in TEST profile.

Now check, what the limit of this profile by below sql :- 

Select PROFILE, RESOURCE_NAME, LIMIT from dba_profiles where PROFILE like 'TEST';

PROFILE                        RESOURCE_NAME                    LIMIT
---------                            -----------------------                        -------
TEST                        PASSWORD_LIFE_TIME               180

This indicates that password will expire in 180 days. So you need to change this limit. Please follow below steps:-

SQL> alter profile TEST limit PASSWORD_LIFE_TIME  unlimited;

All this you need to do why SYSDBA privileges.

Now check the status the status again :-

 Select PROFILE, RESOURCE_NAME, LIMIT from dba_profiles where PROFILE like 'TEST';

PROFILE                        RESOURCE_NAME                    LIMIT
-----------                              ----------------------                        -----------------
TEST                        PASSWORD_LIFE_TIME               UNLIMITED

Now this user will never expire.
Stay happy, keep troubleshooting ;)




on

links to find cloning steps

on No comments:

How to create a cluster in weblogic server

In this, I will explain you that how we can configure our weblogic server to work on the cluster. As you all know why we need cluster, Yes you are right we do this only for high availability and scalability.

 Installation of weblogic with the cluster in Multi-node architecture

Note:- we have to have two machines . And please follow the following steps.


Simple steps that we needed :--
 1. Install WebLogic on both physical machines
 2. Create the WebLogic Domain on one physical machine
 3. Configure the domain by adding Managed Servers and Machines on one physical machine
 4. Copy the domain file structure to the other physical machine and enroll it to the WebLogic Domain
 5. Ensure that both WebLogic Machines are operational
 6. Create and configure the Cluster
 7. Ensure that the Cluster is operational


Install WebLogic on both physical machines :--

We have to install weblogic on both machine with same middleware home . So that we can copy our domain easily. Here we are doing our installation on linux machine .We must have some changes to directories but if you want to go with default then its not a problem.
First ever step to cahnge your “hosts” file.

The following actions should be performed by the "root" user.
Make sure the "/etc/hosts" file contains correct entries for both the "localhost" and real host names.
127.0.0.1      localhost localhost.localdomain localhost4 localhost4.localdomain4
192.xxx.xx.xxx  machine_name.com  machine_name
After that Create a new group and user on OS level .
groupadd -g 1000 oinstall
useradd -u 1100 -g oinstall oracle
passwd oracle


Create the directories in which the Oracle software will be installed.If you don't want too create the  directories than it's ok. BEA will take care of this and automatically create the directories in your home directory i.e. /home/username
But oracle recommend to create directories rather then your home directory. So create directories as below:--
mkdir -p /u1/app/oracle/product/fmw11g
mkdir -p /u1/app/oracle/config/domains
mkdir -p /u1/app/oracle/config/applications
chown -R oracle:oinstall /u1
chmod -R 775 /u1/


Now after creating the directories make a entries  into the "/home/username/.bash_profile" file.So, we dont need to set our path again and again.
export MW_HOME=/u1/app/oracle/product/fmw11g
export WLS_HOME=$MW_HOME/wlserver_10.3
export WL_HOME=$WLS_HOME
# Set to the appropriate JAVA_HOME.
#export JAVA_HOME=/usr/java/jdk1.6.0_33
export JAVA_HOME=/u1/app/oracle/jrockit-jdk1.6.0_45-R28.2.7-4.1.0
#export JAVA_HOME=/u1/app/oracle/jdk1.7.0_17
export PATH=$JAVA_HOME/bin:$PATH


Install the JDK. Instructions for installing JRockit can be found here. This article will assume JRockit is being used.
If you are confuse with Jrockit and JDK then let me explain this for you. In simple words both works in same way. But both using different mechanism to do the same thing. If you want more detail on this please go here .


If you want to use HotSpot for JDK6 or JDK7 you might install them like this.
# # Java 6 as root
# rpm -Uvh jdk-6u33-linux-amd64.rpm
Or
$ # Java 7 as Oracle
$ cd /u1/app/oracle/
$ tar -xvzf /tmp/jdk-7u17-linux-x64.tar.gz


You can download the software from oracle and to start Installation of weblogic please run this command where your setup of weblogic is located.

$JAVA_HOME/bin/java -Xmx1024m -jar wls1036_generic.jar


After that provide the desired inputs to the system. I am not displaying everything.
Please visit oracle-base for see the one by one step of installation.
Note** we have done this on single machine. Please follow the same steps to install on another machine.

 2. Create the WebLogic Domain on one physical machine

We have installed weblogic server  on both machine with the same Middleware home. Now we have to create the domain on single machine.
For this we have to run the config.sh from loaction $ORACLE_HOME/wlserver_10.3/common.
Then go with defaults and provide the username and password for weblogic server.we will configure by admin console.


We have to make some change before starting the weblogic server .
You can change these parameters insetDomain.sh


This is optional You can change the memory size by changing the parameters Xmx
the most important parameters are :
-Xms4096m -Xmx4096m -XX:MaxPermSize=1028m
Xmx - is the max size of the heap.
Xms - is the initial size of the heap.( give it the same as Xmx )
XX:MaxPermSize - is is used to hold reflective of the VM itself such as class objects and method objects ( it's independent from the heap size,, give it the 1/3 to 1/4 of the Xms size depend in your classes size)


NOTE** For one domain having admin server and two managed server we have to use given formula :--
3GB for OS and other + 3GB for Admin Server + 6Gb for two managed servers

Now after configuration we have to start our weblogic server from the location   :--
                                 user_projects/domains/domain_name/bin by running startWeblogic.sh
After providing the username and password wait and see the logs that everything is going smooth.
We can configure our weblogic to start without asking username and password.
 I will explain that in next post i.e about nodemanger.

2.Configure the domain by adding Managed Servers and Machines in one physical machine

Note** we are doing this on one machine
Now its time to configuration the machine and manage servers for final step .
Access you weblogic from  http://localhost:7001/console and ready for the main task. Login with your weblogic username and password .
I am not putting every screen shot here because I know you all aware of the things.
Now we have to create manged server in our domain.


Follow the following steps  :--


 1) Expand your Environment then go to the server link after clicking on this you will find the summary of servers and you will find the configuration table there. There click on the ”new“ button to create managed server.

 2) Now this will ask you to put the name of the managed server and and listing address. As you know weblogic by default work on the 7001 port and if you want to change this port you can easily change this port from the admin console. After providing the input click to create manage server.
Now for e.g. we have our manage server with name of “test1” and port is 7010.

 3) Now after creating successfully you have this test1 server in your summary table where you will find its status shutdown

 4) Now you have to follow the same steps to create the same managed server on the other machine with same port.

 5) Now we have to create the machine to assign to the servers .you will find this in Environment . After clicking new it will ask you for the name of the machine  and machine OS , if you re using Linux then you can go for unix or other wise you can go with default and Now after saving this you can click on newly created machine and check the configuration it will run on default port 5556 if you want to change this port , you can change it.

 6) We have to set this machine to our manage server, for this go to the Environment and server then click on the managed server. You have to put your environment on the “lock and edit” . Now you can see in configuration tab that in machine you have options like “none” and one is machine name. Select the name of your machine and then save & active the changes. You will find this in servers summary table.

 7) Now we have to follow the same step for our second machine that is “test2”

 8)  Now with the WebLogic Machines created and configured we can start the node manager on the machine. Remember that we are still working on the same machine where we run the Configuration Wizard in the first place. It can be started by running the startNodeManager.sh script in the wlserver_10.3/server/bin directory under the Middleware Home. Before starting it, go over the start-up parameters specified in the nodemanager.properties configuration file in the wlserver_10.3/common/nodemanager directory and verify that it is not configured for SSL by ensuring that the SecureListener parameter is set to false. Once started the terminal window should indicate that the node manager is  running and listening to the port configured.
on No comments:

HOW TO START AND STOP ORACLE APPS r12

Hi, It is very easy to start or stop the oracle apps r12, but as a fresher you face many issues with starting and stopping the oracle apps. So in this I will tell you the simple steps to start the oracle apps.
What we must remember some rules first about starting and stopping the oracle apps.

SOME RULES:--

To start oracle apps
          when you start the oracle apps you must remember that start your database tier first and then start your listener and after that start your apps tier.
To Stop oracle apps
          When you want to stop oracle apps you have reverse  the starting process . First shutdown your app tier and then shutdown the listener and then at last shutdown the  database.


You must keep these steps in mind that you must follow the rules.


How to start the oracle apps:-

I will tell you the step that I use  to perform to start the oracle apps on LINUX.


Step 1:- Login to putty or any other software that you used to connect to your server.
Step 2:- Login with user that own the permission of that directory where you have installed oracle db.
Step 3:- Now you have to run the .env file located in ORACLE_HOME.
Step 4:- Now, on terminal run this command  Sqlplus '/as sysdba'
Step 5:- Now you will  have sql prompt like this sql> , type startup command to start the database
Step 6:- Now start the listener by typing " lsnrctl start " , this will start our listener.
Step 7:- We have our database up and running. now it's time to start application.
Step 8:- Now open a new terminal with user that own the permission of that directory where you have installed apps.
Step 9:- Now run the .env file from $HOME/apps/apps_st/appl .
Step 10 :- Now, change directory using cd  $ADMIN_SCRIPT_HOME
Step 11 :- Now you will see many .sh file in this directory. run following command on the terminal
                               sh adstartall.sh apps/password
Step 12:- Check  your app on http://hostname:8000/em




How to stop the oracle apps:-

As I have told you that we need to reverse the process of startup to shutdown the oracle apps.Means we have shutdown our application services first and then your database .


Step 1:- Now open a new terminal with user that own the permission of that directory where you have installed apps.
Step 2:- Now run the .env file from $HOME/apps/apps_st/appl .
Step 3 :- Now, change directory using cd  $ADMIN_SCRIPT_HOME
Step 4 :- Now you will see many .sh file in this directory. run following command on the terminal
                               sh adstpall.sh apps/password
Step 5:- Now we have to stop our listener of database.
Step 6:- Login with user that own the permission of that directory where you have installed oracle db.
Step 7:- Now you have to run the .env file located in ORACLE_HOME.
Step 8:- Now stop the listener by typing " lsnrctl stop " , this will stop our listener.
Step 9:- Now, on the same terminal run this command  Sqlplus '/as sysdba'
Step 10:- Now you will  have sql prompt like this sql> , type shutdown  immediate command to stop the database
Step 11 :- Now you have everything down.

Please let me know if you have any quires regarding this :)






on No comments:

ERRORS & SOLUTION in ORACLE APPS

We face many errors in oracle application in day to day activity. I am sharing some of them as may be this will help you to save time.


ERROR 1
ORA-00020: maximum number of processes (200) exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.

 Reason :-- we are exceeded from the defined range of the sessions. So please find the below solution for this problem.


Solution :--  It is very common error that we face day to day. For this please  Increase the processes & Session in  DB
e.g. alter system set processes=XXX scope=spfile;
     alter system set sessions=XXX scope=spfile;
And bounce the Database properly.


ERROR 2

frm-92050 failed to connect to the server hostname:hostname:9000


Solution :--

Have a look at the following notes (especially the troubleshooting note):

Note: 252113.1 - Login Issue Failed To Connect To The Server Frm-92050 After Specfic Number Of Connections
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=252113.1

Note: 299187.1 - FRM-92050 Errors on Linux Oracle Applications 11i installs
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=299187.1

Note: 365529.1 - Troubleshooting FRM-92XXX Errors in Oracle Applications
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=365529.1

ERROR #

ISSUE :- ORA-01555 : snapshot too : old

Cause :-  Running big transection

Solution :- For this error just follow the following steps:-

1)     First check the size of UNDOTBS1 tablespace. Increase the size if it is  full .

2)     Check the undo_retention parameter. This is dynamic parameter and you can change it whenever you want. So  I found that this parameter is set to 900 i.e. in seconds. So I have increased this values to 2700. 
on No comments:

Detail of Patches in Oracle apps

Detail of Patches in Oracle apps



As you all know, we have to apply patches very frequently. This may be on your database or for oracle application ERP. We use OPATCH to apply patches on the database and ADPATCH to apply patches on oracle apps.

Here I am discussing only ADPATCH. As till now, I have worked in many organizations and applied a number of patches in different environments. So I am sharing my knowledge with you all. And if you find any mistake please feel free to write. 

What is a patch?

In simple words, patches are used to fix a bug. A bug is a defect in the code or functionality. Oracle provides a solution for that particular bug known as a patch.  

Types of patches in oracle apps?

Standalone/Oneoff patches:-- 

This is used to define a patch created to fix single/particular problem.

Mini Pack:--This is a group of one-off patches for a particular product like INV, GL, AP and named like 11i.GL.E ( means this group of patches contains fix for 11i GL product (General Ledger till time E is released ) This is commutative which means it will include 11i.GL.A, 11i.GL.B ....11iGL.D till 11i.GL.E earlier in 10.7 it used to call as patchset.


Family Pack:-- Group of mini-packs in one family bundled together is called a family pack. they are usually named as 11i_PF. A few examples of families are SCM ( 11i.SCM_PF.G ), ATG ( 11i.ATG_PF.H ) _PF indicate Product Family Pack


Maintenance Pack:-- Group of the family pack together is called a maintenance pack. So if you say your Verison is 11.5.10 then its maintenance pack 10 ( 3rd digit is maintenance pack )

cXXXXXX.drv The copy driver. Similar to patch.drv except it does not generate any files. dXXXXXX.drv Database driver. Functions the same as the R10 database driver.
gXXXXXXdrv Generate driver. Generates forms, reports, libraries, and message files.
uXXXXXX.drv it is a combination of all patches. means it contains c,d,g patchwork.

What is adpatch?

It is a utility that is used to apply patches. 

How to apply the patch?

You can find this here.



on No comments:

How to clone oracle apps using simple step by step

Cloning Oracle apps R12

I am Sunny Thakur working as Oracle apps DBA from last 3 years. I worked on many oracle product like  weblogic , OBIEE, Oracle apps and oracle database, ORACLE RMA etc. but I like Oracle apps most. I love to work on oracle apps.

You can also see my previous blog about patching oracle apps here .

The truth about oracle apps is it was a hot technology, it is hot and will remain hot. So for fresher's I will highly recommend oracle apps to take as primary skill :). 

So today I am sharing my knowledge with you guys about how to clone a oracle apps .
Cloning is not very easy to perform. You have to have full knowledge of oracle apps and database also. I will also discuss with you about the common errors that we face while cloning.

Please follow the following steps carefully to clone your system .

STEP  1 :-- Stay calm !!!

Yes, while cloning don't panic at all. I know when you clone your application for first time there is some fear in mind So Please be relax and stay calm.

Now Come to business, Cloning is very simple task . So first question is in mind is :-

Why we need to clone or when to clone oracle apps.
Answer is very simple, We do cloning to replicate our existing system to the UAT or DEV for our developers for testing purpose or if you migrating to new H/W.

STEP 2:-- Prepare your self :)

Yes, Before perform the cloning prepare your self, Do study and if you find this blog I don't think you have to go anywhere :).


NOTE:-- We have oracle apps R12. on Linux and having enough space to take backup. So please first of all make sure you have enough space in your source system(which server you want to clone) . Main objective is to save the time. I am assuming that we have system where we create the UAT for first time.

Preparations :--

If you search for cloning you will find following steps:-

1. PREREQUISITE TASKS
2. PREPARE SOURCE SYSTEM
3. COPY SOURCE TO TARGET
4. CONFIGURE TARGET
5. FINISHING TASKS

PREREQUISITE TASKS :--

Run Autoconfig on both APPLICATION & DB

It is main and very important part of cloning. I am assuming that we have enough space in our source system. First we have to run Auto-config on both application and database servers. For running autoconfig on apps tier we have to shutdown the application, only application not database.
Steps to run Autoconfig on apps tier are as follow:--
1.   Run the environment of oracle apps.
2. Go to $ADMIN_SCRIPTS_HOME then run adstpall.sh to stop the application.
3. In the same directory you will find the script adautocfg.sh .Run this script, it will prompt fro the password. And will take some time to complete.  
4. After successfully completion of the adautocfg.sh on application tier, We have to run the same on the database tier.
5.   Go to the database tier and run .env file from $ORACLE_HOME.
6.  Go to  $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME and run adautocgf.sh .
7. Now we have successfully run the autoconfig on both tiers.

You can restore your applied autoconfig .
1. Go to $APPL_TOP>/admin//out/MMDDhhmm
2. Run  restore.sh

Run Preclone  on both APPLICATION & DB

On the DB server:

1. go to  cd $ORACLE_HOME/appsutil/scripts/
2 . run perl adpreclone.pl dbTier

On the APPS server:

 1. cd $ADMIN_SCRIPTS_HOME
 2 perl adpreclone.pl appsTier

NOTE:- NOW SHUTDOWN THE DATABASE AND TAKE BACKUP OF BOTH DB & APPS

COPY SOURCE TO TARGET

Copy all the directory to new target system e.g. we have /u01 for database tier and /u02 for apps tier.
then you can use "tar " command to create a zip and send this to target system.
After sending the all the drives to target system and unzip by creating the same directory structure.  

CONFIGURE TARGET 


We first clone the database but first check the permission on the directories . We have to give full permission to user.

On the target Database system

1. Go to $ORACLE_HOME/appsutil/clone/bin
2. Run perl adcfgclone.pl dbTier 
  
Give the inputs that are required. You can change the name, port number, and location of your db tier.

After successful clone of db please check the lsnrctl that everything is running fine.

On the target apps system

1. Go to $COMMON_TOP/clone/bin
2. Run perl adcfgclone.pl appsTier
It will ask you for many inputs. you can change the port also from here.


After completing the cloning process. just check your concurrent manger. and If needed then run cmclean.sql to clean your node.   


FINISHING TASKS

Happy cloning :)


Please update with doubts. I will update this blog. Thanks for your time.












on No comments:
Subscribe to: Posts (Atom)