Oracle Apps DBA, Oracle DBA: March 2017

Monday, March 27, 2017

Steps to create 2 node rac instance in cloud

How to create RAC in cloud?

Hello DBA's,

Please find the steps to create RAC instance in oracle cloud. I have covered most of the steps with screen shots. So you must be able to create service in cloud without any issue.

Make sure you have paid or trial account to the oracle cloud in order to make services. Please log in to cloud portal and navigate to dashboard. I logged in with the link below and you can use the link sent in the email by oracle.

URL : https://myaccount.cloud.oracle.com/mycloud/faces/dashboard.jspx

Click Oracle database cloud service

Below page will display what are all services are attached to your account. To create new service, click create service button.

Before we create service we need private and public keys for SSH access which we need to supply while creating service. Please download and install putty key generator.

Click generate button and drag mouse in the given space to generate public and private keys.

Supply the password and save private key in *.ppk format. Please make sure to remember the password, we need this password to access the servers once they are created.

To save public key dont use save public key button as we did for private key since it wont work in cloud. Service creation will result into failed status. Copy the key as highlighted below, Open a notepad and paste it there. Save it into *.pub format. Now, we have created our public and private keys. We are good to create service now.

- Select Enterprise edition - Extreme performance in order to create RAC. Or else you cant see RAC option from the drop down menu.
- Select Database clustering with RAC in db type.

- Provide service name
- Supply the password (This password needed to access sys schema)
- Since i had trial account i can create only 2 node cluster. If you have paid account, create node type as per your requirement.
- I have selected backup container to none since i dint want to take backup. If you want to schedule backup you need to create container before you create service.

At ssh public key section click edit and upload the public key which we created.

- Review the configuration and click create to create service

Once you create the service oracle may take 2-3 hours to create service and compute nodes. You can monitor the status in activity tab.

Once service is created click the service name to find the ip addresses of the nodes.

To access the servers please do the below. Open putty, click ssh --> Auth and click browse. Select the private key which you created through putty generator. To start listener, click icon in the actions menu and enable to listener. I have highlighted the same in the picture below.

In order to connect to db you need to start the listeners from console. Please click the icon below and select access rules. start all the highlighted listeners below.

You can access the below users in the nodes.

- OPC
- ORACLE
- ROOT
- GRID

Excpet opc user you cant log into the nodes. Putty will throw an error if you try to do so. In order to log in as oracle, root or grid user. Initially log in as opc user then sudo to any of the user (oracle, root or grid).

Ex : Sudo su - grid
sudo su - root
sudo su - oracle

Thanks!
Hope it helps. You are encouraged to point out if i have anything provided incorrectly and feel free to add more info.

Tuesday, March 21, 2017

How to deinstall or deregister application oracle home in EBS R12.2.4?

** Please double check whether the fix applied to your environment before applying it**

Hello Everyone,

Below procedure can be followed if you want to deregister application oracle home from your global inventory.

Error snap:

/d02/DEV/appltest/fs2/inst/apps/dev_ahqtest/appl/admin/dev_ahqtest.xml -stage /d02/DEV/appltest/fs2/EBSapps/comn/clone -showProgress -nopromptmsg
Log file located at /d02/DEV/appltest/fs2/inst/apps/dev_ahqtest/admin/log/clone/ApplyAppsTier_03201502.log
| 0% completed
ERROR while running Apply...
Mon Mar 20 15:02:31 2017

ERROR: Failed to execute /d02/DEV/appltest/fs2/EBSapps/comn/clone/bin/adclone.pl

Error logged as below :

#############################################################

Started ApplyAppsTier at Mon Mar 20 15:02:31 AST 2017

Version:

ApplyAppsTier.java : 120.11.12020000.6

#############################################################

Below Oracle Homes are already registered in the global inventory:

/d02/DEV/appltest/fs2/FMW_Home/Oracle_EBS-app1

/d02/DEV/appltest/fs2/FMW_Home/webtier

/d02/DEV/appltest/fs2/FMW_Home/oracle_common

Exiting Cloning...

Ensure that the above Oracle Homes are unregistered from the global inventory "/var/opt/oracle/oraInventory/ContentsXML/inventory.xml" and re-run adcfgclone.pl script

You can view the registered oracle home from the following file. Dont attemp to update the file by hand.
/var/opt/oracle/oraInventory/ContentsXML/inventory.xml

I had to apply the below fix since i have multiple instances attached to this inventory. If you have single instance in your server and if you get the same error while do clone, simply rename the file and try adcfgclone once before applying this fix.

Fix :

Go to 10.1.2 oracle home where you can find runinstaller.

bash-3.2$ cd /d02/DEV/appltest/fs2/EBSapps/10.1.2/oui/bin/

1.
bash-3.2$ ./runInstaller -silent -deinstall -invPtrLoc /var/opt/oracle/oraInst.loc REMOVE_HOMES={"/d02/DEV/appltest/fs2/FMW_Home/Oracle_EBS-app1"}
Starting Oracle Universal Installer...
No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-03-20_03-56-58PM. Please wait ...bash-3.2$ Oracle Universal Installer, Version 10.1.0.6.0 Production
Copyright (C) 1999, 2009, Oracle. All rights reserved.

Starting deinstall

Deinstall in progress (Monday, March 20, 2017 3:57:02 PM AST)
...............................................................100% Done.

End of install phases.(Monday, March 20, 2017 3:57:02 PM AST)
End of deinstallations
Please check /var/opt/oracle/oraInventory/logs/silentInstall2017-03-20_03-56-58PM.log for more details.

bash-3.2$ ./runInstaller -silent -deinstall -invPtrLoc /var/opt/oracle/oraInst.loc REMOVE_HOMES={"/d02/DEV/appltest/fs2/FMW_Home/webtier"}

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-03-20_03-59-30PM. Please wait ...bash-3.2$ Oracle Universal Installer, Version 10.1.0.6.0 Production

Starting deinstall

Deinstall in progress (Monday, March 20, 2017 3:59:34 PM AST)

...............................................................100% Done.

End of install phases.(Monday, March 20, 2017 3:59:34 PM AST)

End of deinstallations

Please check /var/opt/oracle/oraInventory/logs/silentInstall2017-03-20_03-59-30PM.log for more details.

3.
bash-3.2$ ./runInstaller -silent -deinstall -invPtrLoc /var/opt/oracle/oraInst.loc REMOVE_HOMES={"/d02/DEV/appltest/fs2/FMW_Home/oracle_common"}

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-03-20_04-00-00PM. Please wait ...bash-3.2$ Oracle Universal Installer, Version 10.1.0.6.0 Production

Starting deinstall

Deinstall in progress (Monday, March 20, 2017 4:00:04 PM AST)

...............................................................100% Done.

End of install phases.(Monday, March 20, 2017 4:00:04 PM AST)

End of deinstallations

Please check /var/opt/oracle/oraInventory/logs/silentInstall2017-03-20_04-00-00PM.log for more details.

Thats it. You can verify the fix by opening contents.xml to see whether oracle homes have been deinstalled.

Monday, March 20, 2017

PLS-00201: identifier 'FND_CONC_CLONE.SETUP_CLEAN' must be declared

I was getting the below error after DB refresh.

SQL> exec fnd_conc_clone.setup_clean;
BEGIN fnd_conc_clone.setup_clean; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'FND_CONC_CLONE.SETUP_CLEAN' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Reason :

This package should be executed from APPS user whereas i was executing from sys user.

connect as APPS user and execute the command.

bash-3.2$ sqlplus apps/******

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 20 11:14:59 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec fnd_conc_clone.setup_clean;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

We should run autoconfig in both apps and db tier once we execute cleaned fnd_nodes table.

RMAN Active database duplication

Hello Everyone,

Below is tested in my environment and i have done refresh successfully using this method. Following will be Active database duplication. Here i have done the refresh for oracle e-business suite.

DB CLONING – RMAN Active Duplication

	Source	Target
Host name	ORAPROD	ORACLONE
Instance name	PROD	CLONE

Preclone in Prod dbTier :

Go to /u01/oracle/PROD/11.2.0/appsutil/scripts/PROD_oraprod

Perl adpreclone.pl dbTier

Please make sure stage created successfully.

Create directory for clone oracle home.

Mkdir –p /a02/oraclone/CLONE/11.2.0

Mkdir –p /a02/oraclone/CLONE/data

Copy oracle home from prod to clone server.

(Note : Copy only oracle home, don’t copy any db files since we will duplicating the db through RMAN.)

Adcfgclone in CLONE server :

Once oracle home copied to clone server, Execute the below.

Go to $ORACLE_HOME/clone/bin

Perl adcfgclone.pl dbTechStack

dbTechSTack – It will configure the clone oracle home and will make necessary directories and configuration files for clone instance.

Once adcfgclone.pl completes successfully, we can get the env file clone instance under $ORACLE_HOME. We can use the populated env file for clone instance.

TNSNAMES.ORA :

Make sure both servers able to ping with each other. Add the both instance tns entries in both tnsnames.ora

Prod :

PROD=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=oraprod.ebs.com)(PORT=1521))

(CONNECT_DATA=

(SERVICE_NAME=PROD)

(INSTANCE_NAME=PROD)

)

CLONE = (DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=oraclone)(PORT=1542))

(CONNECT_DATA=(SID=CLONE))

)

Clone :

CLONE = (DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=oraclone)(PORT=1542))

(CONNECT_DATA=(SID=CLONE))

)

PROD=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=oraprod.ebs.com)(PORT=1521))

(CONNECT_DATA=

(SERVICE_NAME=PROD)

(INSTANCE_NAME=PROD)

)

Orapwd creation :

Please make sure both oracle homes have password file with same password.

orapwd file=$ORACLE_HOME/dbs/orapwPROD password=sys123 entries=4

orapwd file=$ORACLE_HOME/dbs/orapwCLONE password=sys123 entries=4

Please read my blog once before you do the above. It may be helpful to create password file.

http://sikkyappsdba.blogspot.com/2016/10/ora-01994-grant-failed-password-file.html

Pfile creation :

If prod is running with spfile create a pfile from spfile and scp it to clone server.

$sqlplus / as sysdba

SQL > create pfile from spfile;

Scp pfile to $ORACLE_HOME/dbs to clone server.

Edit the pfile and update the below parameters.

db_name = CLONE

control_files = /a02/oraclone/CLONE/data/cntrl01.dbf,/a02/oraclone/CLONE/data/cntrl02.dbf,/a02/oraclone/CLONE/data/cntrl03.dbf

db_file_name_convert='/u01/oracle/PROD/data','/a02/oraclone/CLONE/data'

log_file_name_convert='/u01/oracle/PROD/data','/a02/oraclone/CLONE/data'

Clone instance :

Once pfile is created start the instance in nomount.

sqlplus / as sysdba

startup nomount pfile=initCLONE.ora

RMAN Connectivity :

Check whether you are able to connect to both database through RMAN.

Rman> connect target sys/sys123@PROD

RMAN> connect auxiliary sys/sys123@CLONE

Rman command to trigger active duplication :

bash-3.2$ nohup rman cmdfile=active_dup.rcv log=active_duplication.log &

Script :

bash-3.2$ cat active_dup.rcv

connect target sys/change_on_install@PROD

connect auxiliary sys/change_on_install@DEV

RUN

{

duplicate target database to 'oradev' FROM ACTIVE DATABASE;

}

RMAN will do the refresh and will open the database. It is recommended to Create spfile once and bounce the database.

Oracle Apps DBA, Oracle DBA

Search This Blog