Apache Related Common Issues in R12 and 11i.

Apache Related Common Issues in R12 and 11i.


Apache Common Issues
General Information to Troubleshoot Apache Issues.
1)If you mistype any directive in Apache (httpd.conf or any conf file), Apache will not start & it will not write even in error_log. In these case it will report any error message to file
$COMMON_TOP/admin/log/$CONTEXT_NAME/adapcctl.txt

So first check in the above file

2)Found *.pid file in Apache_Top/Apache/logs/ even after bounce of an Apache web server (When Apache starts it creates .pid file in logs directory and when you shutdown apache it cleans this .pid file). So if this file is already there then Apache will not start.
Fix: Simply move this file





3)Could not bind port (Port already in Use) or Sometimes apache and concurrent Manager will interchange their port no.’s while starting results error and both of them will not up
Fix: To overcome this up the services in this manner
 

1)Listener
2)Apache web server
3)Concurrent manager
4)adstrtal.sh

4)Error 500 – Internal server error
Fix: 1) Clear Cache (Application Server Cache)and start the services
5)If there are no error messages in error_log or error_log_pls
then there might be issues in starting Jserv component of web server (which caters Java requests by mod_jserv)

6)If you don’t know port number of your web server you can check it in file $IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf with directive Listen & Port

7)Once you are able to access http://hostname.domainname:port and if you are getting any error message that means your web server (Core Apache) is working but there are various components called as modules and there may be issues with those modules which we will check these modules . few modules of our interest are
-mod_jserv
-mod_pls
-mod_oprocmgr
-mod_ossl (If you have ssl enabled on web server)
-mod_osso (If single sign on server is enabled on web server)

8)Once in a while adapcctl.sh start will not work then ultimate you have to go and invoke apachectl script file in Apache top/apache/bin to start Apache webserver.
09)Error : Your session is no longer valid while trying to loging to applications.
The issue is resolved by changing the profile option session_cookie_domain to null from domain.com

10)HTTP-403 forbidden message after entering the username and password in the “Apps Logon Links” page. [Not authorised to view this page]
Check for Apps password in $APACHE_TOP/modplsql/cfg/wdbsvr.app ,Comment out custom_auth entry in $APACHE_TOP/modplsql/cfg/wdbsvr.app and bounce apache.

11)Error message: You have insufficient privileges for the current operation.
Verify and update SESSION_COOKIE_DOMAIN in ICX_parameters if it is wrong.
Example :
update icx_parameters set SESSION_COOKIE_DOMAIN=’NEW_VALUE’ where SESSION_COOKIE_DOMAIN=’OLD_VALUE’
12)AppsLocalLogin?.jsp page error outs….

• Edit the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties
• Set wrapper.bin.parameters=-DLONG_RUNNING_JVM=false
• add the line wrapper.bin.parameters=-DCACHEMODE=LOCAL
• Restarted Apache for these changes to take effect and retested the issue

13) Login to the applications URL fails with
500 Internal server Error
java.lang.NoClassDefFoundError at
oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect.java:126) at
oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect.java:170) at com.evermind[Oracle
Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.loadServlet(HttpApplication.java:2231) at com.evermind[Oracle
Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.findServlet(HttpApplication.java:4617) at com.evermind[Oracle
Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.findServlet(HttpApplication.java:4541) at com.evermind[Oracle
Containers for J2EE 10g (10.1.3.0.0)
Performed the below steps, but it did not resolved the issue
1) Bounced Apache server, cleared cache and validated ports
2) Bounced all Middle tier services and killed defunct processes
3) Bounced Unix Server

Solution
=======
Java object Caching Configuration parameter was Enabled in oc4j.properties
$INST_TOP/ora/10.1.3/j2ee/oacore/config
Disabled LONG_RUNNING_JVM=false from True
Bounced Apache server and could login to the applications URL without issues
                           ISSUES AND SOLUTIONS
ISSUE 1:
Description
When Apache script adapcctl.sh is issued.It shows that Apache is started.On checking the status Apache is stopped
Cause
The error log file in the folder
cd $APACHE_TOP/Apache/logs
Showed
fopen: No such file or directory httpd: could not log pid to file /var/opt/_/Apache/logs/httpd_pls.pid
SOLUTION 1
Created the missing directories /_/Apache/logs under /var/opt folder
Bounced Apache
**********************************************************************
ISSUE 2:
Description
oacore was not comming up in the configurator node.
opmnctl status
ias-component process-type pid status
OC4J oacore N/A Down
OC4J oacore N/A Down
OC4J oacore N/A Down
OC4J oacore N/A Down
Cause
Check the oacore error log.
Example:
Below Log file gives root cause. //mtlog/_/logs/ora/10.1.3/opmn/oacore_default_group_2/oacorestd.err
14:11:56 Error initializing server: //inst/apps/_/ora/10.1.3/j2ee/oacore/config/server.xml,
Fatal error at line 70 offset 1 in file://inst/apps/_/ora/10.1.3/j2ee/oacore/config/server.xml: .:
XML-20100: (Fatal Error) Expected ‘EOF’.
SOLUTION 2
Edit the /sid/inst/apps/SID_hostname/ora/10.1.3/j2ee/oacore/configserver.xml file and remove the extra line in the file. Restart and check oacore will be starting. This sudden change in server.xml seems to be because of a bug.
Bug – 6702510
************************************************************************
ISSUE 3
AppsLocalLogin?.jsp page was displaying errors
Cause
Extract from the Jserv log file =========================== [27/02/2008 16:57:14:447 CST] weboamLocal/oracle.apps.fnd.oam. servlet.ui.OAMServlet:
Sucessfully initialize oaosu.OAMServlet [27/02/2008 16:57:14:447 CST] Servlet Zone rootauohstrzt11 initia lization complete [27/02/2008 16:57:15:101 CST]
Creating noun for: oracle.jsp.JspServlet [27/02/2008 16:57:15:102 CST] Creating noun for: AppsLocalLogin?.jsp
[27/02/2008 16:57:15:120 CST] oracle.jsp.JspServlet: init [27/02/2008 16:57:28:066 CST] Creating noun for: oracle.jsp.JspServlet
[27/02/2008 16:57:28:067 CST] Creating noun for: AppsLocalLogin?.jsp [27/02/2008 16:57:28:078 CST] oracle.jsp.JspServlet: init
[27/02/2008 16:57:29:753 CST] JspServlet?: unable to dispatch to requested page: Exception:java.lang.NoClassDefFoundError
SOLUTION 3:
Edited the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties
a) Set wrapper.bin.parameters=-DLONG_RUNNING_JVM=false
b) Add the line wrapper.bin.parameters=-DCACHEMODE=LOCAL
c) Restarted Apache for these changes to take effect and retested the issue
********************************************************************
ISSUE 4
New Message when starting Apache using adapcctl.sh start
Cause
It takes 7/10 minutes to clear this cache directory if present
apache Web Server Listener is not running.
Starting Apache Web Server Listener (dedicated HTTP) …
Removing gantt cache directory
/ppomwi/applmgr/r12/apps/apps_st/comn/webapps/oacore/html/cabo/images/cache
Successfully removed the gantt cache directory:/tcge1i/applmgr/common/html/cabo/images/cache/gantt
Apache Web Server Listener (PLSQL) is not running.
Starting Apache Web Server Listener (dedicated PLSQL) …
SOLUTION 4
$COMMON_TOP/webapps/oacore/html/cabo/images/cache
Wait for atleast 7 to 10 minutes and this cache directory will be cleared and Apache will be started successfully
***********************************************************************
ISSUE 5
Apache server is up and running.
But adapcctl.sh status shows
adapcctl.sh version 115.54
Apache Web Server Listener is not running. Apache Web Server Listener (PLSQL) is not running.
adapcctl.sh: exiting with status 1

Cause
Incorrect Lock file path given in httpd.conf
LockFile? $APACHE_TOP/Apache/log/PPOMWI_httpd.lock
and Incorrect path given in adapcctl.sh script.
SOLUTION 5:
Modified the path of lock file in $APACHE_TOP/Apache/conf/httpd.conf file.
LockFile? /var/opt/SID_/httpd.lock
and Modified the path of adapcctl.sh file by commenting the wrong path
************************************************************************
ISSUE 6

After selecting the E-Business home page got internal server error Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator, and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.

Cause
Found that all the properties files as below
jserv.properties jserv_restrict.properties zone.properties zone_restrict.properties
were pointing to a dbc file name _.dbc
which was not physically present at the path
/sid/applmgr/11510/fnd/11.5.0/secure/_
$ cd //applmgr/11510/fnd/11.5.0/secure/_
$ ls .dbc
SOLUTION 6:
copied the file PROD.dbc
which was physically present at path
//applmgr/11510/fnd/11.5.0/secure/_
to the dbc file name which was being pointed by all the properties files
This resolved the issue.
**********************************************************************
ISSUE 7
Instance is very slow / inaccessible with a warning on login page:
Warning
Low-level logging is currently enabled. Your application will not perform as well while Low-level logging is on.
Cause
Logging was enabled through profile values:
FND: Debug Log Enabled — Yes
FND: Debug Log Level — Statement
FND: Debug Log Module — %
SLA: Enable Diagnostics — Yes
FND Validation Level — Error
SOLUTION 7
1. Change these values to:
FND: Debug Log Enabled — No
FND: Debug Log Level — ( Blank )
FND: Debug Log Module — ( Blank )
SLA: Enable Diagnostics — No
FND Validation Level — None
2. Bounce Apache on all the MTs and clear Apache cache.
*********************************************************************
ISSUE 8
Login to the applications URL fails with
500 Internal Server Error java.lang.NoClassDefFoundError at oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect?.java:126) at oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect?.java:170) at com.evermind[Oracle Containers for J2EE? 10g (10.1.3.0.0) ].server.http.HttpApplication.loadServlet(HttpApplication?.java:2231) at com.evermind
Performed the below steps, but it did not resolved the issue
1) Bounced Apache server, cleared cache and validated ports
2) Bounced all Middle tier services and killed defunct processes
3) Bounced Unix Server

Cause
No Cause Entered
SOLUTION 8
Java object Caching Configuration parameter was Enabled in oc4j.properties $INST_TOP/ora/10.1.3/j2ee/oacore/config
Disabled LONG_RUNNING_JVM=false from True
Bounced Apache server and could login to the applications URL without issues
Java Caching is a mechanism used to improve performance by storing data in the Middle Tier JVM memory, instead of retrieving the data from the database every time it is requested.
***********************************************************************
ISSUE 9
When trying to access the URL (Either Big IP or the node level), it hangs for a long time and getting redirected to a different URL and gives a blank page. For example, When try to access the ipayments URL which is hosted on node2, it hangs for a long time and its getting redirected towards the Sales URL which is hosted on node3, and displays a blank page.

Cause
All the host reference and the URL directives are pointing towards node3 and sales URL respectively, in the below files. Hence every attempt tries to launch the sales URL and get fails as the apache configuration files and parameters are pointing towards the correct one.
jserv.conf forms.properties zone.properties jserv.properties
SOLUTION 9
Stop Apache. Edit the files jserv.conf, forms.properties, zone.properties, jserv.properties, jserv.conf to replace the host reference and the URL directives to the correct one (In our case, its node3 . Clear the cache and restart the Apache.
ISSUE 10
Apache login issues occurs in multi MT environment. Unable to login to oracle applications.

Cause
Issue due to distributed cache.
SOLUTION 10
Set is_distributed to false in javacache.xml Bounce Apache.
ISSUE 11
Apache server is down. Apache logfile did not provide useful information.
Started the Apache server, but the Apache Web listener did not started.
1) Verified no log files (Apache,Jserv) crossed 2GB limit. error_log, access.log, mod_jserv
2) Veified that the port is listening (netstat -an |grep ) Apache port(httpd.conf), pls listener(httpd_pls.conf)
3) Verified that the Apache and Jserv configuration files are not changed recently httpd.conf & Jserv.conf
4) Shutdown all the Middle tier services and check for ipcs (semaphores) did not resolve the outage.
$ ipcs on application middle tier as Application user ap

Cause
No Cause Entered
SOLUTION 11
Rebooted the server and started the services. All services came back normal.
************************************************************************
ISSUE 12
Unable to login to the home page URL. Login fails with the error:
Error Page You have encountered an unexpected error. Please contact the system administrator for assistance.
Click here for exception details
Checks:
Upon clicking the link, can observe the error:
oracle.apps.fnd.framework.OAException: Application: FND, Message Name: SQL_PL SQL_ERROR. Tokens: ROUTINE = createSession(int)(userId=6,sessionMode=’null’,’2D8 FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412?’); REASON = java.s ql.SQLException: ORA-01422: exact fetch returns more than requested number of ro ws ORA-06512: at “APPS.FND_SESSION_MANAGEMENT”, line 295 ORA-06512: at “APPS.FND_SESSION_MANAGEMENT”, line 455 ORA-06512: at line 1 ; ERRNO = 1422;
Cause
FND_NODES table was updated with the same server_id for all Nodes.
SOLUTION 12
Update the table FND_NODES with correct server_ids for all nodes as in $FND_TOP/secure/sid_host/sid.dbf
Bounced apache on all apache nodes.
and Run select node_name, server_id from fnd_nodes;
to confirm the values.
Below is the example for the solution:
Found three hosts using same server_id:
SQL> select node_name, server_id from fnd_nodes; NODE_NAME SERVER_ID
AUOHSACPS06 2D8FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412
AUOHSACPS03 2D8FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412
AUOHSACPS02 2D8FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412
reset server_id with original server_id from each hosts .dbc file @ $FND_TOP/secure:
SQL> select node_name, server_id from fnd_nodes; NODE_NAME SERVER_ID
AUOHSACPS06 2D8FF21843830F82E040558CDBC6195C27690802237516122831445560236127
AUOHSACPS03 2D90005D5A13C774E040558CDBC61D9436546148971863330582284123371818
AUOHSACPS02 2D8FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412
************************************************************************
ISSUE 13
Users getting ‘404 page not found errors’ when tyring to access the URL
(OR)
Getting Exception in thread “main” java.lang.ClassFormatError: Truncated class file
(OR)
adstrtal.sh or adstpall.sh is throwing below error message
You are running adstrtal.sh version 115.16
Exception in thread “main” java.lang.ClassFormatError: oracle/apps/ad/context/AppsContext (Truncated class file) at java.lang.ClassLoader.defineClass0(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader?.java:539) at java.security.SecureClassLoader.defineClass(SecureClassLoader?.java:123) at java.net.URLClassLoader.defineClass(URLClassLoader?.java:251) at java.net.URLClassLoader.access$100(URLClassLoader?.java:55) at java.net.URLClassLoader$1.run(URLClassLoader?.java:194) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader?.java:187) at java.lang.ClassLoader.loadClass(ClassLoader?.java:289) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:274) at java.lang.ClassLoader.loadClass(ClassLoader?.java:235) at java.lang.ClassLoader.loadClassInternal(ClassLoader?.java:302) at oracle.apps.ad.autoconfig.ServiceControl.(ServiceControl?.java:319) at oracle.apps.ad.autoconfig.ServiceControl.main(ServiceControl?.java:662) Check logfile //applmgr/common/admin/log/_/08061104.log for details
Exiting with status 1
(OR)
[06/08/2007 11:03:12:181] (ERROR) an error returned handling request via protocol “ajpv12” [06/08/2007 11:03:12:181] (EMERGENCY) ajp12: can not connect to host 140.85.188.36:20021 [06/08/2007 11:03:13:184] (EMERGENCY) ajp12: can not connect to host 140.85.188.36:20021 [06/08/2007 11:03:15:038] (EMERGENCY) ajp12: auth did not receive challenge size [06/08/2007 11:03:15:038] (EMERGENCY) ajp12: auth fail
Cause
Extract from the JVM log file: //product/iAS/Apache/Jserv/logs/jvm/OACoreGroup.0.stderr
Exception in thread “main” java.lang.ClassFormatError: Truncated class file at org.apache.jserv.JServServletManager.load_init(JServServletManager?.java:765) at org.apache.jserv.JServServletManager.loadServlet(JServServletManager?.java:659) at org.apache.jserv.JServServletManager.loadStartupServlets(JServServletManager?.java:789) at org.apache.jserv.JServServletManager.init(JServServletManager?.java:447) at org.apache.jserv.JServ.start(JServ.java:625)
Verified that there are no log files that exceed 2GB limit (OS limit).
Clearing the Apache and Jserv cache did not resolve the issue. Unmount / mount product specific file system did not resolve
SOLUTION 13:
Try each of these solutions until the problem is resolved, in order to have the least impact on the system.
1. If you can determine the specific class file from the log files, use the ‘touch’ command.
e.g. touch classfile
If necessary, restart Apache and clear server cache.
2. Shut down all MT services, exit the Apps owner and ask the System Admin to unmount and remount the /sid/applmgr file system (mount point above $JAVA_TOP). Ask the System Admin to kill any processes that may be holding the file system open. Then log in to the Apps owner and restart MT services.
3. Shutdown all the MT services and ask System Admin to reboot the server if the actions above don’t resolve the outage.
************************************************************************
ISSUE 14:
Got login error – Internal Server Error after bounced all services on . find following message from Jserv log file:
$ tail -8 mod_jserv.log
[07/05/2007 18:41:06:915] (ERROR) ajp12: Servlet Error: java.lang.NoClassDefFoundError: null
[07/05/2007 18:41:06:916] (ERROR) an error returned handling request via protocol “ajpv12”
[07/05/2007 18:41:06:916] (ERROR) balance: 11906 internal servlet error in server auohsnops03.oracleoutsourcing.com:20720
[07/05/2007 18:41:06:916] (ERROR) an error returned handling request via protocol “balance”
[07/05/2007 20:07:52:732] (ERROR) ajp12: Servlet Error: java.lang.NoClassDefFoundError: null
No runaway, no lock on port 20720.

SOLUTION 14:
Shutdown all services on MT. Removed all log file (standard error…) from Jserv/jvm. Started All services with successful login.
************************************************************************
ISSUE 15
Customer reported Not able to login with “Internal Server Error” on .
We could not start some of services during bounced All Services with following error message: adapcctl.sh version 115.47
Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.
Apache Web Server Listener is not running (dedicated HTTP) …
Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.
Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.
Apache Web Server Listener is already stopped.
Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.
adapcctl.sh: exiting with status 2
Unable to login the application URL, facing the error while login:
Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator, ap@domain.com and inform them of the time the error occurred, and anything you might have done that may have caused the error.
Cause:
SQL> select END_DATE from fnd_user where user_name=’GUEST’;
END_DATE
———
05-OCT-10
SOLUTION 15
We found out the apps and applsys were not in sync.
Just manual change the apps password from sqlplus.
Restarted all services and login successfully.
or
Update the FND_USER table with END_DATE=null and bounced apache
SQL> update fnd_user set END_DATE=null where user_name=’GUEST’;
1 row updated.
SQL> commit;
Commit complete.
SQL> select END_DATE from fnd_user where user_name=’GUEST’;
END_DATE
———–
************************************************************************



ISSUE 16
Unable to login to oracle applications:
error message: You have insufficient privileges for the current operation.
Checks
Unable to login to oracle applications:
error message: You have insufficient privileges for the current operation.
SOLUTION 16
Verify and update SESSION_COOKIE_DOMAIN in ICX_parameters if it is wrong.
Example :
update icx_parameters set SESSION_COOKIE_DOMAIN=’NEW_VALUE’ where SESSION_COOKIE_DOMAIN=’OLD_VALUE’;
************************************************************************
ISSUE 17
Apache home page is not coming up even after bounced
Found in jvm log asking for java version
SOLUTION 17
//product/iAS/Apache/Apache/bin/java.sh: /usr/java/jdk1.3.1_15/bin/java: not found
Check the version in /usr/java and update that version in java.sh file and Bounced Apache.
************************************************************************
ISSUE 18
Unable to access applications
cause
All applications are up and running.
“Solaris Error: 12: Not enough space” errors are encounterd in database listener logfile.
SOLUTION 18:
Check if the OS is SUN Solaris.
Check if /tmp filesystem is 100% full.
Check Swap space utilization.
Check the application process that is hagging resources. (Check if any sql sessions are hagging resources)
Clear the process in issue after getting required approval.
NOTE:
The above is applicable only for environments on Solaris OS.
************************************************************************
ISSUE 19
Not able to login to APPS. JSP Error:
Unable to create an user session or ORA-01403 : no data found has been detected in FND.SIGNON.AUDIT_USER
Checks
Check number of rows in dual table.
Select * from dual;
DUAL table should always contain only one row.
The table named DUAL is a small table in the data dictionary that Oracle and user-written programs can reference to guarantee a known result.
This table has one column called DUMMY and one row containing the value X.
SOLUTION 19
DUAL table should always contain only one row and one column.
Insert a row with value X into dual table if the dual table contains 0 rows. If the dual table contains more than one row then delete the rows by keeping one row.
The rows in dual table not equal to zero.
************************************************************************
ISSUE 20
Signature for “https URL redirecting to http URL”
Issue:
https URL redirecting to http URL and the page cannot be displayed error occurs.
SOLUTION 20
Check the httpd.conf and url_fw.conf, in url_fw.conf the Rewrite rule was
RewriteRule ^/$/OA_HTML/AppsLocalLogin.jsp.
Changed into
RewriteRule https://: /OA_HTML/AppsLocalLogin.jsp.

Clone a Database Using a Hot Backup RMAN

Clone a Database Using a Hot Backup RMAN

Clone a Database Using a Hot Backup

    A) Take a hot backup of the SOURCE Instance

    To quickly take a hot backup , Refer to : Script-to-create-Script-to-take-Hot-Backup-of-Database

    B) Prepare the TARGET

    SHUTDOWN the target DATABASE and LISTENER
    Take backup and Remove the TARGET ORACLE_HOME and database

    C) Use tar to copy the ORACLE_HOME and the database (from the hot backup) to the TARGET system to replace the target system.

    On the Source : Create the tar files

    tar -cvf /tmp/ohome.tar <ORACLE_HOME location>
    tar -cvf /tmp/dbbkp.tar <Database Hot backup location>


    Copy or scp the tar files

    scp /tmp/ohome.tar oracle@192.168.2.131:/tmp/ohome.tar
    scp /tmp/dbbkp.tar oracle@192.168.2.131:/tmp/dbbkp.tar


    On the Target Intance :

    create the directories for the ORACLE_HOME and datafiles if it does not exist
    Extract the tar files

    cd <ORACLE_HOME location>
    tar -xvf /tmp/ohome.tar

    cd <datafile location>
    tar -xvf /tmp/dbbkp.tar



ON the TARGET

1 Login as target user user and Verify .profile and *.env file under $ORACLE_HOME
Make sure that the environment is set for the Target System.

cd $ORACLE_HOME
pwd

2 Check all oracle related file systems for user and group ownership

cd <TARGET file systems>
ls -l

Check user and group ownership : It should be proper


3 Modify the entries in the tnsnames.ora and listener.ora to have the Listner for the target instance

eg : My listener.ora - where target is dev
==================================

dev =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCdev))
(ADDRESS= (PROTOCOL= TCP)(Host= dbalounge)(Port= 1525))
)

SID_LIST_dev =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u03/oracle/devdb/10.2.0)
(SID_NAME = dev)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u03/oracle/devdb/10.2.0)
(PROGRAM = extproc)
)
)

STARTUP_WAIT_TIME_dev = 0
CONNECT_TIMEOUT_dev = 10
TRACE_LEVEL_dev = OFF

LOG_DIRECTORY_dev = /u03/oracle/devdb/10.2.0/network/admin
LOG_FILE_dev = dev
TRACE_DIRECTORY_dev = /u03/oracle/devdb/10.2.0/network/admin
TRACE_FILE_dev = dev
ADMIN_RESTRICTIONS_dev = OFF


eg : My TNS entry - where target is dev
==================================

dev=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=DBALOUNGE.com)(PORT=1 525))
(CONNECT_DATA=
(SERVICE_NAME=dev)
(INSTANCE_NAME=dev)
)
)


4 In config.c, make sure the entries are pointing to target .

cd $ORACLE_HOME/rdbms/lib
ls -ltr config*
vi config.c
Take care of Group. It should be the group the target user belongs to :
#define SS_DBA_GRP "dbTARGET"
#define SS_OPER_GRP "dbTARGET"



5 Move config.o

cd $ORACLE_HOME/rdbms/lib
mv config.o config.o.old_18jul10


6 If this is RAC to NON-RAC Refresh, relink Oracle with rac_off

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk rac_off

If this step did not fail with fatal errors, proceed with :
make -f ins_rdbms.mk ioracle


7 Relink all the executables.

cd $ORACLE_HOME/bin
./relink all

8 Verify the Timestamp
ls -l $ORACLE_HOME/bin/oracle



9 Verify that you can run sqlplus “/ as sysdba” without prompting for any password.

sqlplus "/ as sysdba"
connected to idle instance


10 Set the init.ora paramters and create the destination directory location
To see the directories to be created and corresponding parameters to be modified, Refer to :
Create-a-Database-Manually-using-Create-Database-Script

11 Prepare the Control file script from the trace bacup taken on production.

vi TARGET_control.sql

a) ? Consider the resetlog portion of this script . Delete the noresetlogs section of the script.

b)? Old Entry - CREATE CONTROLFILE REUSE DATABASE "SOURCE" NORESETLOGS ARCHIVELOG FORCE LOGGING

? New Entry - CREATE CONTROLFILE REUSE SET DATABASE "SOURCE" RESETLOGS ARCHIVELOG

c) Change the location of Datafiles to point to the Target,
Replace all occurances of : SOURCE replaced by TARGET
Replace all occurances of : SOURCE replaced by TARGET

d) Remove the TEMP file entries ( keep a safe copy, for later use).
Keep a safe backup of Temporary creation part and redo logfile creation statements.
After this , you can remove all lines after characterset and semicolon.

e) Inspect the control file script
The number of Datafiles should match that in the source.
select count(*) from v$datafile at the source.

Similarily, number of copied Datafiles at the Target should match the definition in the script.

Example – of what you will be left with in TARGET_control.sql
STARTUP NOMOUNT;
CREATE CONTROLFILE REUSE SET DATABASE "TARGET" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 1361
LOGFILE
GROUP 1 (
'/TARGET/..../log01a.dbf',
'/TARGET/..../log01b.dbf'
) SIZE 200M,
GROUP 2 (
'/TARGET/..../log02a.dbf',
'/TARGET/..../log02b.dbf'
) SIZE 200M,
GROUP 3 (
'/TARGET/..../log03a.dbf',
'/TARGET/..../log03b.dbf'
) SIZE 200M,
….
DATAFILE
'/TARGET/..../system01.dbf',
'/TARGET/..../system02.dbf',
……….
……….. (representing other datafiles)

CHARACTER SET UTF8
;


12 Create the Controlfile

$sqlplus "/ as sysdba"
@TARGET_control.sql

13 See archive log mode

SQL>ARCHIVE LOG LIST
Should show automatic archiving “Enabled” and “Archivelog” Mode

14 Recover the database

SQL> recover database using backup controlfile until cancel;

Enter the archive file location, when prompted.

Use the following query on SOURCE to get the list of archives needed to be recovered -
select THREAD#, SEQUENCE# from v$log_history where &1 between FIRST_CHANGE# and NEXT_CHANGE#;

Here – For the value for $1 to be given will come from the sequence returned by the command – “recover database using backup controlfile until cancel;” which you have given on TARGET


15 Open the DB with resetlogs
SQL> alter database open resetlogs;

When the DB opens, check for any missing files or files needed for recovery

SQL> select * from v$recover_file;
SQL> select * from v$datafile where name like '%MISS%';
Should not return any rows for these queries.

16 Shutdown the Database

17 Check for the Default Temporary file –

select * from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

Confirm that this TEMP.
If the default is not TEMP,
SQL>alter database default temporary tablespace TEMP

18. Create the Temp datafiles , using the commands taken from the control file.

ALTER TABLESPACE TEMP ADD TEMPFILE __________

Example - The commands will be similar to below
ALTER TABLESPACE TEMP ADD TEMPFILE '/TARGET/..../temp20.dbf'
SIZE 134217728 REUSE AUTOEXTEND ON NEXT 52428800 MAXSIZE 1800M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/TARGET/..../temp19.dbf'
SIZE 134217728 REUSE AUTOEXTEND ON NEXT 52428800 MAXSIZE 1800M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/TARGET/..../temp18.dbf'
SIZE 134217728 REUSE AUTOEXTEND ON NEXT 52428800 MAXSIZE 1800M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/TARGET/..../temp17.dbf'
SIZE 134217728 REUSE AUTOEXTEND ON NEXT 52428800 MAXSIZE 1800M;

Verify dba_temp_files, for all the temp file entries added to TEMP

SQL> select file_name from dba_temp_files;

19 Update global_name

select * from global_name;
update global_name set global_name='TARGET.ORACLEOUTSOURCING.COM';
commit;

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
TARGET


Different Approach As Per Oracle Documentation.

    Assumptions:

    Hot backups of the production server are there, including binary backup of controlfiles.

    Production Database Name: orcl
    Cloned Database Name: pub

    --Create pfile from the production servers spfile.

    SQL> create pfile='/u01/initpub.ora' from spfile;

    -- Make appropriate folder structures.

    [oracle@canada ~]$ mkdir -p /u01/app/oracle/admin/pub/adump
    [oracle@canada ~]$ mkdir -p /u01/app/oracle/oradata/pub
    [oracle@canada ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/pub
    [oracle@canada ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/PUB/archivelog


    -- Edit the initpub.ora file and change the location of all the files for
    -- the "pub" database.

    Also add the following parameters to the pfile:

    DB_UNIQUE_NAME=pub

    -- above parameter is necessary if database is created on the same machine.

    DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/orcl/,/u01/app/oracle/oradata/pub/)

    -- above parameter is necessary if database is created on the same machine,
    -- or if the directory structure is different.

    LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/orcl/,/u01/app/oracle/oradata/pub/)

    -- above parameter is mandatory.

    LOG_ARCHIVE_DEST_1 = 'LOCATION=/u01/app/oracle/flash_recovery_area/PUB/archivelog/'

    Sample Pfile


    pub.__db_cache_size=62914560
    pub.__java_pool_size=4194304
    pub.__large_pool_size=4194304
    pub.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    pub.__pga_aggregate_target=113246208
    pub.__sga_target=150994944
    pub.__shared_io_pool_size=0
    pub.__shared_pool_size=75497472
    pub.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/pub/adump'
    *.audit_trail='db'
    *.compatible='11.2.0.0.0'
    *.control_files='/u01/app/oracle/oradata/pub/control01.ctl',
    '/u01/app/oracle/flash_recovery_area/pub/control02.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='orcl'
    *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
    *.db_recovery_file_dest_size=4039114752
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=pubXDB)'
    *.log_archive_format='%t_%s_%r.dbf'
    *.memory_target=262144000
    *.open_cursors=300
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.undo_tablespace='UNDOTBS1'
    DB_UNIQUE_NAME=pub
    DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/orcl/,/u01/app/oracle/oradata/pub/)
    LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/orcl/,/u01/app/oracle/oradata/pub/)
    LOG_ARCHIVE_DEST_1 = 'LOCATION=/u01/app/oracle/flash_recovery_area/PUB/archivelog/'

    --Copy the backup datafiles, archived log files, and control files to folders created
    --earlier.


    [oracle@canada ~]$ export ORACLE_SID=pub
    [oracle@canada ~]$ sqlplus

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 16 19:02:27 2012

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

    Enter user-name: / as sysdba
    Connected to an idle instance.

    SQL> startup nomount pfile='/u01/initpub.ora';
    ORACLE instance started.

    Total System Global Area 263639040 bytes
    Fixed Size 1335892 bytes
    Variable Size 197135788 bytes
    Database Buffers 62914560 bytes
    Redo Buffers 2252800 bytes

    SQL> alter database mount clone database;

    Database altered.

    SQL> set pagesize 100
    SQL> set linesize 130
    SQL> select name,file#,status from v$datafile;

    NAME FILE# STATUS
    ------------------------------ ---------- -------
    /u01/app/oracle/oradata/pub/sy 1 SYSOFF
    stem01.dbf

    /u01/app/oracle/oradata/pub/sy 2 OFFLINE
    saux01.dbf

    /u01/app/oracle/oradata/pub/un 3 OFFLINE
    dotbs01.dbf

    /u01/app/oracle/oradata/pub/us 4 OFFLINE
    ers01.dbf


    SQL> alter database datafile 1 online;

    Database altered.

    SQL> alter database datafile 2 online;

    Database altered.

    SQL> alter database datafile 3 online;

    Database altered.

    SQL> alter database datafile 4 online;

    Database altered.


    SQL> select name,file#,status from v$datafile;

    NAME FILE# STATUS
    -------------------------------------------------- ---------- -------
    /u01/app/oracle/oradata/pub/system01.dbf 1 SYSTEM
    /u01/app/oracle/oradata/pub/sysaux01.dbf 2 ONLINE
    /u01/app/oracle/oradata/pub/undotbs01.dbf 3 ONLINE
    /u01/app/oracle/oradata/pub/users01.dbf 4 ONLINE


    SQL>recover database until cancel using backup controlfile;

    --manually apply all the suggested archived log files and finish
    --the incomplete recovery.

    SQL>alter database open resetlogs;

"FRM-92095; Oracle Jinitiator Version too Low. Please install version 1.1.8.2 or higher"

"FRM-92095; Oracle Jinitiator Version too Low. Please install version 1.1.8.2 or higher"

"FRM-92095; Oracle Jinitiator Version too Low. Please install version 1.1.8.2 or higher"
When I login from my Windows/Xp client machine to my Linux Server running R12.1.1. I get the message below:

Solution:-

Oracle EBS R12 comes with JRE so you are not supposed to download any Jinitator version.
 If you already have one installed, please uninstall it
(via Control Panel > Add/Remove Software) and check then. 

or.

#.Change Browser ...

or.

Check under $ORACLE_HOME/forms60/server and $ORACLE_HOME/forms60/admin/server directories.

Where is that file (formsweb.cfg) located?

Replace
#jpi_classid=clsid:CAFEEFAC-0014-0002-0006-ABCDEFFEDCBA
#jpi_codebase=http://java.sun.com/products/plugin/autodl/jinstall-1_4_2-windows-i586.
cab#Version=1,4,2,06

with
jpi_classid=clsid:CAFEEFAC-0016-0000-0022-ABCDEFFEDCBA
jpi_codebase=http://java.sun.com/update/1.6.0/jinstall-6u22-windows-i586.
cab#Version=1,6,0,22

Clone an Oracle database using RMAN duplicate (same server)..

Clone an Oracle database using RMAN duplicate (same server)..

This procedure will clone a database onto the same server using RMAN duplicate.

• 1. Backup the source database.
  To use RMAN duplicate an RMAN backup of the source database is required. If there is already one available, skip to step 2. If not, here is a quick example of how to produce an RMAN backup. This example assumes that there is no recovery catalog available:

rman target sys@<source database> nocatalogbackup database plus archivelog format '/u01/ora_backup/rman/%d_%u_%s';

 This will backup the database and archive logs. The format string defines the location of the backup files. Alter it to a suitable location.

• 2. Produce a pfile for the new database
  This step assumes that the source database is using a spfile. If that is not the case, simply make a copy the existing pfile.
  
  Connect to the source database as sysdba and run the following:

create pfile='init<new database sid>.ora' from spfile;

This will create a new pfile in the $ORACLE_HOME/dbs directory.

The new pfile will need to be edited immediately. If the cloned database is to have a different name to the source, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary.

Because in this example the cloned database will reside on the same machine as the source, Oracle must be told how convert the filenames during the RMAN duplicate operation. This is achieved by adding the following lines to the newly created pfile:

db_file_name_convert=(<source_db_path>,<target_db_path>)

log_file_name_convert=(<source_db_path>,<target_db_path>)

Here is an example where the source database scr9 is being cloned to dg9a. Note the trailing slashes and lack of quotes:

db_file_name_convert=(/u01/oradata/scr9/,/u03/oradata/dg9a/)

log_file_name_convert=(/u01/oradata/scr9/,/u03/oradata/dg9a/)

• 3. Create bdump, udump & cdump directories
  Create bdump, udump & cdump directories as specified in the pfile from the previous step.
• 4. Add a new entry to oratab, and source the environment
  Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
  
  Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:

echo $ORACLE_SID

If this doesn't output the new database sid go back and investigate why not.

• 5. Create a password file
  Use the following command to create a password file (add an appropriate password to the end of it):

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=<your password>

• 6. Duplicate the database
  From sqlplus, start the instance up in nomount mode:

startup nomount

Exit sqlplus, start RMAN and duplicate the database. As in step 1, it is assumed that no recovery catalog is available. If one is available, simply amend the RMAN command to include it.

rman target sys@<source_database> nocatalog auxiliary /



duplicate target database to <clone database name>;

This will restore the database and apply some archive logs. It can appear to hang at the end sometimes. Just give it time - I think it is because RMAN does a 'shutdown normal'.

If you see the following error, it is probably due to the file_name_convert settings being wrong. Return to step 2 and double check the settings.

RMAN-05001: auxiliary filename '%s' conflicts with a file used by the target database

Once the duplicate has finished RMAN will display a message similar to this:

database opened

Finished Duplicate Db at 20-SEP-14



RMAN> exit RMAN.

• 7. Create an spfile
  From sqlplus:

create spfile from pfile;



shutdown immediate

startup

Now that the clone is built, we no longer need the file_name_convert settings:

alter system reset db_file_name_convert scope=spfile sid='*'

/



alter system reset log_file_name_convert scope=spfile sid='*'

/

• 8. Optionally take the clone database out of archive log mode
  RMAN will leave the cloned database in archive log mode. If archive log mode isn't required, run the following commands from sqlplus:

shutdown immediate

startup mount

alter database noarchivelog;

alter database open;

• 9. Configure TNS
  Add entries for new database in the listener.ora and tnsnames.ora as necessary.

Restore/Recovery using RMAN with different scenarios

Restore/Recovery using RMAN with different scenarios

POINT IN TIME PRODUCTION RESTORE/RECOVERY USING RMAN BACKUPS

ASSUMPTION:

*This restore recover situation is pertaining to Restoring database from 15th Sep 09 backup and recovering point in time upto 16th Sep 09 12:00 Noon. (This situation raised as a user made logical error in the database).

*Took a backup of current database using RMAN and also kept a cold backup after shutting down the database. This is usefull in case of reverting back the database to original.

*Remove all Controlfiles/Logfile/Database files including undo,temp from the original location.

*Make sure RMAN catalog database tnsentry is available in Restoring production server.

Connect to target database and recovery catalog database using following command.

rman catalog username/password@catalogdb target sys/password@targetdb

Get the backup piece information with List backup command in RMAN.

RMAN> List backup;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

4173    Full    3M         DISK        00:00:01     15-SEP-09

        BP Key: 4173   Status: AVAILABLE   Tag: TAG20090915T182528

        Piece Name: F:\BACKUPS\RMAN\SID\SID_20090915_697659929_4273_1.CTL

  Controlfile Included: Ckp SCN: 136760496    Ckp time: 15-SEP-09

Get the Controlfile name and Tag name of 15 Sep 09 controlfile backup.

Backup piece name              : SID_20090915_697659929_4273_1.CTL

Tag name                               : TAG20090915T182528

Restore controlfile from Tag name.

run{

allocate channel ch1 type DISK;

restore controlfile from tag 'TAG20090915T182528';

release channel ch1;

}

Mount the database.

RMAN> alter database mount;

database mounted

IMP NOTE:

At this point, Restore database command alone will restore the latest backup pieces from auto backup since this is from catalog. taken on 16th Sep 09. Hence Find out the appropriate tag for database files on 15th Sep 09 and use it for restoration.

RMAN> list backup summary completed after 'sysdate -2';

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Tag

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

235823  B  0  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T010133

235824  B  0  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T010133

235825  B  0  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T010133

235826  B  0  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T010133

235827  B  F  A SBT_TAPE    15-SEP-09       1       1

235828  B  A  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T012854

235829  B  A  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T012854

235830  B  A  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T012854

235831  B  A  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T012854

235832  B  A  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T012854

235833  B  F  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T012929

235834  B  F  A SBT_TAPE    15-SEP-09       1       1

235835  B  A  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T110015

235836  B  A  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T110015

235837  B  A  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T110015

235838  B  A  A SBT_TAPE    15-SEP-09       1       1       TAG20090915T110015

235839  B  F  A SBT_TAPE    15-SEP-09       1       1

235894  B  F  A DISK        15-SEP-09       1       1       TAG20090915T182007

235895  B  F  A DISK        15-SEP-09       1       1       TAG20090915T182007

235915  B  F  A DISK        15-SEP-09       1       1       TAG20090915T182528

235922  B  A  A DISK        15-SEP-09       1       1       TAG20090915T182532

235923  B  A  A DISK        15-SEP-09       1       1       TAG20090915T182532

235924  B  A  A DISK        15-SEP-09       1       1       TAG20090915T182532

235936  B  F  A DISK        15-SEP-09       1       1

236271  B  F  A DISK        16-SEP-09       1       1       TAG20090916T172304

236272  B  F  A DISK        16-SEP-09       1       1       TAG20090916T172304

236292  B  F  A DISK        16-SEP-09       1       1       TAG20090916T172837

236299  B  A  A DISK        16-SEP-09       1       1       TAG20090916T172851

236303  B  F  A DISK        16-SEP-09       1       1

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

235894  Full    6G         DISK        00:03:56     15-SEP-09

        BP Key: 235896   Status: AVAILABLE   Tag: TAG20090915T182007

        Piece Name: F:\BACKUPS\RMAN\SID\SID_20090915_697659608_4271_1.DBF

  List of Datafiles in backup set 235894

  File LV Type Ckp SCN    Ckp Time  Name

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

  1       Full 136760211  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_SYSTEM01.DBF

  2       Full 136760211  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_FWPDS02.DBF

  5       Full 136760211  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_FWDATAMART01.DB

  6       Full 136760211  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_FWINDEX01.DBF

  8       Full 136760211  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_FWLOB01.DBF

  14      Full 136760211  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_BUSOBJS01.DBF

  15      Full 136760211  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_IVIEWS01.DBF

  17      Full 136760211  15-SEP-09 E:\ORACLE\ORADATA\SID\SID_UNDOTBS1.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

235895  Full    8G         DISK        00:05:12     15-SEP-09

        BP Key: 235897   Status: AVAILABLE   Tag: TAG20090915T182007

        Piece Name: F:\BACKUPS\RMAN\SID\SID_20090915_697659609_4272_1.DBF

  List of Datafiles in backup set 235895

  File LV Type Ckp SCN    Ckp Time  Name

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

  3       Full 136760212  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_CWMLITE01.DBF

  4       Full 136760212  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_DRSYS01.DBF

  7       Full 136760212  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_FWIVINDEX01.DBF

  9       Full 136760212  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_FWPDS01.DBF

  10      Full 136760212  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_ODM01.DBF

  11      Full 136760212  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_TOOLS01.DBF

  12      Full 136760212  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_USERS01.DBF

  13      Full 136760212  15-SEP-09 D:\ORACLE\ORADATA\SID\SID_XDB01.DBF

Run the below command to restore the database from tag

run{

allocate channel ch1 type DISK;

allocate channel ch2 type DISK;

allocate channel ch3 type DISK;

allocate channel ch4 type DISK;

RESTORE DATABASE from tag 'TAG20090915T182007';

release channel ch1;

release channel ch2;

release channel ch3;

release channel ch4;

}

Recover database until the required time of 16th Sep 09 12:00PM noon.

*Check the availability of Archivelog files its asking for. If Archivelog files already available in the default location, no need to restore the archive log files.

SQL> RECOVER DATABASE UNTIL TIME '2009-09-16:12:00:00' USING BACKUP CONTROLFILE;

ORA-00279: change 136760211 generated at 09/15/2009 18:20:09 needed for thread

1

ORA-00289: suggestion : E:\ORACLE\ORADATA\SID\ARCHIVE\ARC01202001

ORA-00280: change 136760211 for thread 1 is in sequence #1202

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 136760501 generated at 09/15/2009 18:25:31 needed for thread

1

ORA-00289: suggestion : E:\ORACLE\ORADATA\SID\ARCHIVE\ARC01203001

ORA-00280: change 136760501 for thread 1 is in sequence #1203

ORA-00278: log file 'E:\ORACLE\ORADATA\SID\ARCHIVE\ARC01202001' no longer

needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.

Media recovery complete.

Verify the checkpoint time for all the datafiles. It should show the same restore time.

SQL> Select distinct to_char(checkpoint_time,’DD-MON-YYYY HH24:MI:SS’) from v$datafile;

Once all archive log files are applied and after the status is Media recover complete, Use alter database open resetlogs to open the database.

SQL> alter database open resetlogs;

Database altered.

Add temp files as required.

SQL> alter tablespace temp add tempfile 'E:\ORACLE\ORADATA\SID\SID_TEMP01.DBF' size 200m;

Tablespace altered.

Perform a basic health check as required.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

SID   READ WRITE

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            E:\Oracle\oradata\SID\archive

Oldest online log sequence     1

Next log sequence to archive   3

Current log sequence           3

Verify database connectivity from local laptop or any other hosts.

IMPNOTE:

As this is a new database incarnation all further Rman backups will fail as this new incarnation is not registered in the catalog database. Issue the following command to register this database incarnation to the catalog.

RMAN> reset database;

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN> list incarnation;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            CUR Reset SCN  Reset Time

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

1       2       SID  3580177852       NO  1          04-JUN-07

1       144370  SID  3580177852       NO  97271318   28-NOV-08

1       242850  SID  3580177852       YES 136846506  16-SEP-09

Finally take a backup of production database immediately after restore recovery.