OraclExpertise

Sunday, February 12, 2017

RMAN recovery using until SCN

Recovering the DB after the clone.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/app/database/dev/data01/system01.dbf'
SQL> recover database using backup controlfile;
ORA-00279: change 461885697981 generated at 02/03/2017 15:39:21 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_113_934902581.dbf
ORA-00280: change 461885697981 for thread 1 is in sequence #113

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/app/database/dev/archive_logs/1_113_934902581.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log
'/app/database/dev/archive_logs/1_113_934902581.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Check whether we have the required archives in the backup.

RMAN> list backuppiece '/app/database/dev/RMAN_BKUP/fullbackup_arch_PROD_hlrrm59a_1_1_196149.rman';

List of Backup Pieces
BP Key BS Key Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
74      74      1   1   AVAILABLE   DISK        /app/database/dev/RMAN_BKUP/fullbackup_arch_PROD_hlrrm59a_1_1_196149.rman

RMAN> list backupset 74;

List of Backup Sets
===================

BS Key Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
74      2.95M      DISK        00:00:01     03-FEB-17
        BP Key: 74   Status: AVAILABLE Compressed: YES Tag: TAG20170203T201850
        Piece Name: /app/database/dev/RMAN_BKUP/fullbackup_arch_PROD_hlrrm59a_1_1_196149.rman

List of Archived Logs in backup set 74
Thrd Seq     Low SCN    Low Time Next SCN   Next Time
---- ------- ---------- --------- ---------- ---------
1    114     461885718790 03-FEB-17 461885734590 03-FEB-17

We don't have the file requried i.e. 113. Get it from the tapes and apply.

SQL> recover database using backup controlfile;
ORA-00279: change 461885697981 generated at 02/03/2017 15:39:21 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_113_934902581.dbf
ORA-00280: change 461885697981 for thread 1 is in sequence #113

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 461885718790 generated at 02/03/2017 17:00:22 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_114_934902581.dbf
ORA-00280: change 461885718790 for thread 1 is in sequence #114
ORA-00278: log file '/app/database/dev/archive_logs/1_113_934902581.dbf'
no longer needed for this recovery

ORA-00279: change 461885734590 generated at 02/03/2017 18:00:20 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_115_934902581.dbf
ORA-00280: change 461885734590 for thread 1 is in sequence #115
ORA-00278: log file '/app/database/dev/archive_logs/1_114_934902581.dbf'
no longer needed for this recovery

ORA-00279: change 461885734598 generated at 02/03/2017 18:00:20 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_116_934902581.dbf
ORA-00280: change 461885734598 for thread 1 is in sequence #116
ORA-00278: log file '/app/database/dev/archive_logs/1_115_934902581.dbf'
no longer needed for this recovery

ORA-00279: change 461885734606 generated at 02/03/2017 18:00:23 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_117_934902581.dbf
ORA-00280: change 461885734606 for thread 1 is in sequence #117
ORA-00278: log file '/app/database/dev/archive_logs/1_116_934902581.dbf'
no longer needed for this recovery

ORA-00279: change 461885734614 generated at 02/03/2017 18:00:23 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_118_934902581.dbf
ORA-00280: change 461885734614 for thread 1 is in sequence #118
ORA-00278: log file '/app/database/dev/archive_logs/1_117_934902581.dbf'
no longer needed for this recovery

ORA-00279: change 461885767294 generated at 02/03/2017 20:18:45 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_119_934902581.dbf
ORA-00280: change 461885767294 for thread 1 is in sequence #119
ORA-00278: log file '/app/database/dev/archive_logs/1_118_934902581.dbf'
no longer needed for this recovery

ORA-00279: change 461885767302 generated at 02/03/2017 20:18:45 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_120_934902581.dbf
ORA-00280: change 461885767302 for thread 1 is in sequence #120
ORA-00278: log file '/app/database/dev/archive_logs/1_119_934902581.dbf'
no longer needed for this recovery

ORA-00279: change 461885767310 generated at 02/03/2017 20:18:47 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_121_934902581.dbf
ORA-00280: change 461885767310 for thread 1 is in sequence #121
ORA-00278: log file '/app/database/dev/archive_logs/1_120_934902581.dbf'
no longer needed for this recovery

ORA-00279: change 461885767325 generated at 02/03/2017 20:18:48 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_122_934902581.dbf
ORA-00280: change 461885767325 for thread 1 is in sequence #122
ORA-00278: log file '/app/database/dev/archive_logs/1_121_934902581.dbf'
no longer needed for this recovery

ORA-00279: change 461885767339 generated at 02/03/2017 20:18:50 needed for
thread 1
ORA-00289: suggestion :
/app/database/dev/archive_logs/1_123_934902581.dbf
ORA-00280: change 461885767339 for thread 1 is in sequence #123
ORA-00278: log file '/app/database/dev/archive_logs/1_122_934902581.dbf'
no longer needed for this recovery

ORA-00308: cannot open archived log
'/app/database/dev/archive_logs/1_123_934902581.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/app/database/dev/data01/system01.dbf'

You still need archive log file with sequence 123, as we don't have it. We are going with incomplete recovery by using, until SCN. This SCN we can get from the last archive backup we have.

RMAN> list backuppiece '/app/database/dev/RMAN_BKUP/fullbackup_arch_PROD_hprrm59b_1_1_196153.rman';

List of Backup Pieces
BP Key BS Key Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
73      73      1   1   AVAILABLE   DISK        /app/database/dev/RMAN_BKUP/fullbackup_arch_PROD_hprrm59b_1_1_196153.rman

RMAN> list backupset 73;

List of Backup Sets
===================

BS Key Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
73      2.50K      DISK        00:00:00     03-FEB-17
        BP Key: 73   Status: AVAILABLE Compressed: YES Tag: TAG20170203T201850
        Piece Name: /app/database/dev/RMAN_BKUP/fullbackup_arch_PROD_hprrm59b_1_1_196153.rman

List of Archived Logs in backup set 73
Thrd Seq     Low SCN    Low Time Next SCN   Next Time
---- ------- ---------- --------- ---------- ---------
1    121     461885767310 03-FEB-17 461885767325 03-FEB-17
1    122     461885767325 03-FEB-17 461885767339 03-FEB-17

RMAN> recover database until scn=461885767339;

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DEV      READ WRITE

SQL>

Wednesday, January 4, 2017

APEX 5.0 Installation using ORDS on WebLogic Server

it's a two tier architecture where the DB that hosts the APEX schema is on one server and the Application Listener is on a different server.

We will be carrying out whole installation setup from the Application server itself, we don't have to do anything on the DB server other than making sure the password file is present and configured so that we can connect as with sysdba privilege from Application server.

APEX Installation:

1) Download the s/w from the Oracle site.
http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

2) Unzip Downloaded file.
$unzip apex_5.0.zip

3) Connect to the DB as SYS specifying the sysdba privilege.
$cd apex

SQL> sho user
USER is "SYS"
SQL>@apexins.sql APEX APEX TEMP3 /i/
.
.
.
.
.
Thank you for installing Oracle Application Express 5.0.4.00.12

Oracle Application Express is installed in the APEX_050000 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex/apex_admin     (Oracle REST Data Services)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex     (Oracle REST Data Services)

PL/SQL procedure successfully completed.

1 row selected.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

4) Creating Instance Administration Account
SQL> @apxchpwd.sql
Usage: SET SERVEROUTPUT { ON | OFF } [SIZE n]
             [ FOR[MAT] { WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED] } ]
================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN]
Enter ADMIN's email [ADMIN]
Enter ADMIN's password []

SQL>

5) Configuring the APEX_PUBLIC_USER Account

SQL>ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

6) Configuring RESTful Services

SQL> @apex_rest_config.sql

Enter a password for the APEX_LISTENER user              []
Enter a password for the APEX_REST_PUBLIC_USER user              []
...create APEX_LISTENER user
...create APEX_REST_PUBLIC_USER user
SQL>

7) DB Users created as part of APEX installation.

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
APEX_050000                    OPEN
APEX_PUBLIC_USER               OPEN
FLOWS_FILES                    OPEN
APEX_LISTENER                  OPEN
APEX_REST_PUBLIC_USER          OPEN

Oracle REST Data Services Installation:

1) Downloading Oracle REST Data Services.
http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html

2) Unzip the downloaded zip file into a directory(ORDS).
$mkdir config

$unzip ords.3.0.9.348.07.16.zip

$ls -1;
ls
config
docs
examples
logs
ords.3.0.9.348.07.16.zip
ords.war
params
readme.html

3) Set parameters before the installation.

~/ORDS/params/ords_params.properties

db.hostname=glerpdvdb05.devglobalpay.com
db.port=1521
db.servicename=DEV
db.sid=DEV
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
rest.services.apex.add=true
rest.services.ords.add=true
schema.tablespace.default=APEX
schema.tablespace.temp=TEMP3
standalone.http.port=8180
standalone.static.images=
user.tablespace.default=APEX
user.tablespace.temp=TEMP3

4) Set config dir
$java -jar ords.war configdir ~/ORDS/config

5) Install ORDS.
$java -jar ords.war install advanced
Enter the name of the database server [dev.oracle.com]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
Enter the database SID [xe]:DEV
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:1
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.

Enter the username with SYSDBA privileges to verify the installation [SYS]:
Enter the database password for SYS:
Confirm password:
Passwords do not match, try again
Enter the database password for SYS:
Confirm password:
Enter the default tablespace for ORDS_METADATA [APEX]:
Enter the temporary tablespace for ORDS_METADATA [TEMP3]:
Enter the default tablespace for ORDS_PUBLIC_USER [APEX]:
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP3]:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Jan 03, 2017 7:06:16 AM
INFO: Updated configurations: defaults, apex, apex_pu, apex_al, apex_rt
Installing Oracle REST Data Services version 3.0.9.348.07.16
... Log file written to ~/ORDS/logs/ords_install_core_2017-01-03_070616_00897.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to ~/ORDS/logs/ords_install_datamodel_2017-01-03_070709_00868.log
Completed installation for Oracle REST Data Services version 3.0.9.348.07.16. Elapsed time: 00:00:55.858

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2

6) Configuring Oracle Application Express Images.
$java -jar ords.war static ~/APEX_5.0/apex/images
WAR Generation complete
WAR location     : ~/ORDS/i.war
Context path     : /i
Static resources : ~/APEX_5.0/apex/images
Ensure the static resources are available at path: ~/APEX_5.0/apex/images
on the server where the WAR is deployed.

$ls -1
config
docs
examples
i.war
logs
ords.3.0.9.348.07.16.zip
ords.war
params
readme.html

Additional Steps:
1) Disabling Oracle XML DB Protocol Server.
SQL> sho user
USER is "SYS"
SQL> EXEC DBMS_XDB.SETHTTPPORT(0);

Deploying to Oracle WebLogic Server:

We are assuming that WebLogic Server is installed and are deploying the *.war files on to a managed server.

Launch the WebLogic Administration Console by typing the following URL in your web browser:
http://<host>:<port>/console

1) Deploying on to the Managed Server.

To install the deployment:
    Go to the WebLogic Server Home Page. Below Domain Configuration, select Deployments.
    The Summary of Deployments is displayed.
    Click Install.
    Specify the location of the ords.war file and click Next.
    Select Install this deployment as an application and click Next.
    Select the servers and/or clusters to which you want to deploy the application or module and click Next.
    In the Optional Settings, specify the following:
        Name - Enter:
        ords
        Security - Select the following:
        Custom Roles: Use roles that are defined in the Administration Console; use policies that are defined in the deployment descriptor
        Source accessibility - Select:
        Use the defaults defined by the deployment's targets
    Click Next.
    A summary page is displayed.
    Click Finish.

    Repeat the previous steps to deploy the i.war file.
    In the optional settings, specify the following:
        Name - Enter:
        i
        Security - Select:
        Custom Roles: Use roles that are defined in the Administration Console; use policies that are defined in the deployment descriptor
        Source Accessibility - Select:
        Use the defaults defined by the deployment's targets.

2) Configuring WebLogic to Handle HTTP Basic Challenges Correctly.
Add the <enforce-valid-basic-auth-credentials> element to $DOMAIN_HOME/config/config.xml within the<security-configuration> element.

...
<enforce-valid-basic-auth-credentials>false</enforce-valid-basic-auth-credentials>
</security-configuration>
...

3) Login into the APEX

http://<host>:<managed server port>/ords

workspace : internal
user : admin
password :

Monday, January 2, 2017

WebLogic R12c: nodemanager failed to start

The configuration that is set in the Admin Console"Environment, Machines, Configuration, Node Manager" conflicts with the configuration in $DOMAIN_HOME/nodemanager/nodemanager.properties.

Error: "For Server $ServerName, the Node Manager Associated With Machine $MachineName is Not Reachable" Attempting To Start A Managed Server Within The Administration Console (Doc ID 2054244.1)

=>/app01/Oracle_Home/user_projects/domains/ORDS/bin
=>./startNodeManager.sh
NODEMGR_HOME is already set to /app01/Oracle_Home/user_projects/domains/ORDS/nodemanager
/app01/Oracle_Home/wlserver/server/bin/startNodeManager.sh: !: not found
CLASSPATH=/app01/Java/jdk1.7.0_79/lib/tools.jar:/app01/Oracle_Home/wlserver/server/lib/weblogic_sp.jar:/app01/Oracle_Home/wlserver/server/lib/weblogic.jar:/app01/Oracle_Home/oracle_common/modules/net.sf.antcontrib_1.1.0.0_1-0b3/lib/ant-contrib.jar:/app01/Oracle_Home/wlserver/modules/features/oracle.wls.common.nodemanager_2.0.0.0.jar::/app01/Oracle_Home
+ /app01/Java/jdk1.7.0_79/bin/java -server -d64 -Xms32m -Xmx200m -XX:MaxPermSize=128m -Dcoherence.home=/app01/Oracle_Home/coherence -Dbea.home=/app01/Oracle_Home -Dweblogic.RootDirectory=/app01/Oracle_Home/user_projects/domains/ORDS -Xverify:none -Djava.endorsed.dirs=/app01/Java/jdk1.7.0_79/jre/lib/endorsed:/app01/Oracle_Home/oracle_common/modules/endorsed -Djava.security.policy=/app01/Oracle_Home/wlserver/server/lib/weblogic.policy -Dweblogic.nodemanager.JavaHome=/app01/Java/jdk1.7.0_79 weblogic.NodeManager -v
<Jan 2, 2017 3:38:46 AM EST> <INFO> <Loading domains file: /app01/Oracle_Home/user_projects/domains/ORDS/nodemanager/nodemanager.domains>
<Jan 2, 2017 3:38:49 AM EST> <INFO> <Loaded NodeManager configuration properties from '/app01/Oracle_Home/user_projects/domains/ORDS/nodemanager/nodemanager.properties'>
Node manager v12.1.3

Configuration settings:

DomainsFile=/app01/Oracle_Home/user_projects/domains/ORDS/nodemanager/nodemanager.domains
LogLimit=0
DomainsDirRemoteSharingEnabled=false
AuthenticationEnabled=true
LogLevel=INFO
DomainsFileEnabled=true
ListenAddress=localhost
NativeVersionEnabled=true
ProcessDestroyTimeout=20000
ListenPort=8156
LogToStderr=true
weblogic.StartScriptName=startWebLogic.sh
SecureListener=true
LogCount=1
LogAppend=true
weblogic.StopScriptEnabled=false
StateCheckInterval=500
CrashRecoveryEnabled=false
weblogic.StartScriptEnabled=true
LogFile=/app01/Oracle_Home/user_projects/domains/ORDS/nodemanager/nodemanager.log
LogFormatter=weblogic.nodemanager.server.LogFormatter
coherence.StartScriptEnabled=false
ListenBacklog=50
NodeManagerHome=/app01/Oracle_Home/user_projects/domains/ORDS/nodemanager
weblogic.startup.JavaHome=/app01/Java/jdk1.7.0_79
weblogic.startup.MW_Home=
coherence.startup.JavaHome=/app01/Java/jdk1.7.0_79
coherence.startup.MW_Home=

Domain name mappings:

ORDS -> /app01/Oracle_Home/user_projects/domains/ORDS

<Jan 2, 2017 3:38:49 AM EST> <INFO> <WebLogic Server 12.1.3.0.0 Wed May 21 18:53:34 PDT 2014 1604337 >
<Jan 2, 2017 3:38:50 AM EST> <INFO> <Secure socket listener started on port 8156, host localhost>
^C

Solution:
Changed ListenAddress in "$DOMAIN_HOME/nodemanager/nodemanager.properties"

before:
ListenAddress=localhost
SecureListener=false

after:
ListenAddress=dev.oracle.com
SecureListener=true

=>./startNodeManager.sh
NODEMGR_HOME is already set to /app01/Oracle_Home/user_projects/domains/ORDS/nodemanager
/app01/Oracle_Home/wlserver/server/bin/startNodeManager.sh: !: not found
CLASSPATH=/app01/Java/jdk1.7.0_79/lib/tools.jar:/app01/Oracle_Home/wlserver/server/lib/weblogic_sp.jar:/app01/Oracle_Home/wlserver/server/lib/weblogic.jar:/app01/Oracle_Home/oracle_common/modules/net.sf.antcontrib_1.1.0.0_1-0b3/lib/ant-contrib.jar:/app01/Oracle_Home/wlserver/modules/features/oracle.wls.common.nodemanager_2.0.0.0.jar::/app01/Oracle_Home
+ /app01/Java/jdk1.7.0_79/bin/java -server -d64 -Xms32m -Xmx200m -XX:MaxPermSize=128m -Dcoherence.home=/app01/Oracle_Home/coherence -Dbea.home=/app01/Oracle_Home -Dweblogic.RootDirectory=/app01/Oracle_Home/user_projects/domains/ORDS -Xverify:none -Djava.endorsed.dirs=/app01/Java/jdk1.7.0_79/jre/lib/endorsed:/app01/Oracle_Home/oracle_common/modules/endorsed -Djava.security.policy=/app01/Oracle_Home/wlserver/server/lib/weblogic.policy -Dweblogic.nodemanager.JavaHome=/app01/Java/jdk1.7.0_79 weblogic.NodeManager -v
<Jan 2, 2017 3:48:36 AM EST> <INFO> <Loading domains file: /app01/Oracle_Home/user_projects/domains/ORDS/nodemanager/nodemanager.domains>
<Jan 2, 2017 3:48:39 AM EST> <INFO> <Loaded NodeManager configuration properties from '/app01/Oracle_Home/user_projects/domains/ORDS/nodemanager/nodemanager.properties'>
Node manager v12.1.3

Configuration settings:

DomainsFile=/app01/Oracle_Home/user_projects/domains/ORDS/nodemanager/nodemanager.domains
LogLimit=0
DomainsDirRemoteSharingEnabled=false
AuthenticationEnabled=true
LogLevel=INFO
DomainsFileEnabled=true
ListenAddress=dev.oracle.com
NativeVersionEnabled=true
ProcessDestroyTimeout=20000
ListenPort=8156
LogToStderr=true
weblogic.StartScriptName=startWebLogic.sh
SecureListener=false
LogCount=1
LogAppend=true
weblogic.StopScriptEnabled=false
StateCheckInterval=500
CrashRecoveryEnabled=false
weblogic.StartScriptEnabled=true
LogFile=/app01/Oracle_Home/user_projects/domains/ORDS/nodemanager/nodemanager.log
LogFormatter=weblogic.nodemanager.server.LogFormatter
coherence.StartScriptEnabled=false
ListenBacklog=50
NodeManagerHome=/app01/Oracle_Home/user_projects/domains/ORDS/nodemanager
weblogic.startup.JavaHome=/app01/Java/jdk1.7.0_79
weblogic.startup.MW_Home=
coherence.startup.JavaHome=/app01/Java/jdk1.7.0_79
coherence.startup.MW_Home=

Domain name mappings:

ORDS -> /app01/Oracle_Home/user_projects/domains/ORDS

<Jan 2, 2017 3:48:39 AM EST> <INFO> <WebLogic Server 12.1.3.0.0 Wed May 21 18:53:34 PDT 2014 1604337 >
<Jan 2, 2017 3:48:39 AM EST> <INFO> <Plain socket listener started on port 8156, host dev.oracle.com>

Thursday, December 29, 2016

WegLogic 12c silent installation on SUN Solaris

WebLogic 12c(12.1.3) on Solaris SPARC 64 bit fails with the error below:

java -jar fmw_12.1.3.0.0_wls.jar -silent -responseFile wls.rsp
Launcher log file is /var/tmp/OraInstall2016-12-29_10-33-15AM/launcher2016-12-29_10-33-15AM.log.
Extracting files...............................
Starting Oracle Universal Installer

Checking if CPU speed is above 300 MHz.   Actual 1415 MHz    Passed
Checking swap space: must be greater than 512 MB.   Actual 17934 MB    Passed
Checking if this platform requires a 64-bit JVM.   Actual 64    Passed
Checking temp space: must be greater than 300 MB.   Actual 56261 MB    Passed

Preparing to launch the Oracle Universal Installer from /var/tmp/OraInstall2016-12-29_10-33-15AM
Log: /var/tmp/OraInstall2016-12-29_10-33-15AM/install2016-12-29_10-33-15AM.log
Copyright (c) 1996, 2014, Oracle and/or its affiliates. All rights reserved.
Reading response file..
Starting check : CertifiedVersions
Expected result: One of 5.10,5.11
Actual Result: 5.10
Check complete. The overall result of this check is: Passed
CertifiedVersions Check: Success.
Starting check : CheckJDKVersion
Expected result: 1.7.0_15
Actual Result: 1.7.0_79
Check complete. The overall result of this check is: Passed
CheckJDKVersion Check: Success.
Validations are enabled for this session.
Verifying data......
[VALIDATION] [ERROR]:INST-07546: Unable to find install type Fusion Middleware Infrastructure
[VALIDATION] [SUGGESTION]:Provide a valid install type
installation Failed. Exiting installation due to data validation failure.

Solution:

Changed "INSTALL_TYPE" parameter in response file.
< INSTALL_TYPE=Fusion Middleware Infrastructure
---
> INSTALL_TYPE=WebLogic Server

Launcher log file is /var/tmp/OraInstall2016-12-29_11-14-20AM/launcher2016-12-29_11-14-20AM.log.
Extracting files...............................
Starting Oracle Universal Installer

Checking if CPU speed is above 300 MHz.   Actual 1415 MHz    Passed
Checking swap space: must be greater than 512 MB.   Actual 17923 MB    Passed
Checking if this platform requires a 64-bit JVM.   Actual 64    Passed
Checking temp space: must be greater than 300 MB.   Actual 55245 MB    Passed

Preparing to launch the Oracle Universal Installer from /var/tmp/OraInstall2016-12-29_11-14-20AM
Log: /var/tmp/OraInstall2016-12-29_11-14-20AM/install2016-12-29_11-14-20AM.log
Copyright (c) 1996, 2014, Oracle and/or its affiliates. All rights reserved.
Reading response file..
Starting check : CertifiedVersions
Expected result: One of 5.10,5.11
Actual Result: 5.10
Check complete. The overall result of this check is: Passed
CertifiedVersions Check: Success.
Starting check : CheckJDKVersion
Expected result: 1.7.0_15
Actual Result: 1.7.0_79
Check complete. The overall result of this check is: Passed
CheckJDKVersion Check: Success.
Validations are enabled for this session.
Verifying data......
Copying Files...
You can find the log of this install session at:
/var/tmp/OraInstall2016-12-29_11-14-20AM/install2016-12-29_11-14-20AM.log
-----------20%----------40%----------60%----------80%--------100%

The installation of Oracle Fusion Middleware 12c WebLogic Server and Coherence 12.1.3.0.0 completed successfully.
Logs successfully copied to /var/opt/oracle/oraInventory/logs.

source:
http://www.kernelbytes.com/silent-mode-installation-weblogic12c/
https://docs.oracle.com/middleware/1213/core/OUIRF/toc.htm

Wednesday, December 28, 2016

RMAN Technical Reference

RMAN backup, delete, reporting, charting, compression, encryption, authentication, such as crosschek control that I created a technical reference for quick access. I want to share with you. I will be updating this document periodically. I tried to classify the commands in RMAN to recover under the general headings. Hope to be useful.
Backup Reporting Operations
Backup need report:

RMAN>report need backup;

Obsolete backup report:

RMAN>report obsolete;

Back-up Operations
Backing Up whole database:

RMAN>backup database;
or
RMAN> backup incremental level 0 database;

Backing Up whole database with archive log files:

RMAN>backup database plus archivelog;

Backing Up Controlfile :

RMAN>backup as copy current controlfıle;

Backing Up whole database with archive log files and deleting arhived log files:

RMAN>backup database plus archivelog delete input;

Backing Up a Tablespace:

RMAN>backup as backupset
format '/backup/df_%d_%s.bck'
tablespace users;

Backing Up a datafile:

RMAN>backup datafile 5;

Backing Up changed blocks (Differential Incremental Backup):

RMAN> backup incremental level 1 database;

Backing Up all blocks after last full backup (Cumulative Incremental Backup):

RMAN>backup incremental level 1 cumulative database;

Image Copy Backup:

RMAN>backup as copy database;

Backup Deletion Operations
Deleting a backup by sequence number:

RMAN>delete backuppiece 101;

Deleting a backup by file name:

RMAN>DELETE CONTROLFILECOPY '/tmp/control01.ctl';

Deleting arhive log files:

RMAN>DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE = 300;
RMAN>DELETE NOPROMPT ARCHIVELOG ALL;

Note:NOPROMPT, During the backup deletion , it will not ask the Yes / No question confirmation
Deleting a backup by tag name:

RMAN>DELETE BACKUP TAG='before_upgrade';

Deleting all backups:

RMAN>DELETE BACKUP;

Deleting a backup on the disk which backed up to tape:
RMAN>DELETE ARCHIVELOG ALL BACKED UP 3 TIMES TO sbt;
Deleting Image copy backup:

RMAN>DELETE COPY;

Deleting Obsolete Backuıp
RMAN marks backups as obsolete are not needed for the recovery process. (if you set redundancy or recovery window )
Deleting obsolete backups:

RMAN>delete obsolete;

Deleting obsolete backups older than 3 days:

RMAN> delete obsolete recovery window of 3 days;

Deleting expired backup

RMAN>delete expired backup;

Crosscheck Control
Crosscheck command checks the backup in the RMAN repository whether it exists physically on the disk or tape . If the file not exists physically on the disk or tape (can be deleted through the operating system) it marks the backup as “EXPIRED” in the RMAN Repository. Then, you can delete from RMAN Repository with “DELETE EXPIRED” command.
Checking Archive log files with crosscheck:
RMAN>crosscheck archivelog all;
Checking whole backup with crosscheck:

RMAN>crosscheck backup;

Checking image copy backup with crosscheck:

RMAN>crosscheck copy;

Checking tagged backup with crosscheck:

RMAN>crosscheck backuppiece tag = 'nightly_backup';

Showing Backup List

RMAN>list backupset of database;

RMAN>list backup;
Showing Backup Preview (In which objects are listed in):

RMAN> restore database preview;

RMAN> restore tablespace users preview;

Configuration Operations
See the configuration settings:

RMAN>show all;

Return to the default configuration: At the end of statement iswritten “clear” expression.

RMAN>configure backup optimization clear;

To see a configuration that has been set: At the begining of statement is written “set” expression

RMAN>show controlfile autobackup format;

To configure a setting: At the begining of statement is written “configure” expression

RMAN>configure device type disk backup type to copy;

RMAN>configure retention policy to recovery window of 7 days;

RMAN>configure retention policy to redundancy 3;

Verification Operations (validate)
Verification whole backup:

RMAN>backup validate;

Verification archive log files backup:

RMAN>backup validate database archivelog all;

Verification whole database restore:

RMAN>restore database validate;

Verification control file restore:

RMAN>restore controlfile validate;

Verification spfile restore:

RMAN>restore spfile validate;

Verification a tablespace restore:

RMAN>restore tablespace users validate;

Backup Compression Operations (After 11g)
Compression a backupset:

RMAN>backup as compressed backupset database;

Backup Encryption Operations
Setting the Encryption :

RMAN> Set  encryption identified by passwprd;

Setting the Decryption:

RMAN> Set  decryption identified by passwprd;

Enable & Disable Block change tracking
BCT to be active only changed blocks to keep track of:

SQL>alter database enable block change tracking using file '/rman_bkups/change.log';

Disabling Block change tracking:

SQL>alter database disable block change tracking;

To debug and trace RMAN backup

$rman target=/ debug=all trace=rman.trc

Create the backup log file

$export NLS_DATE_FORMAT=’dd.mm.yyyy hh24:mi:ss’;

$rman target=/ log=rman.log

Recovery area size information

select name
,        floor(space_limit / 1024 / 1024) "Size MB"
,        ceil(space_used  / 1024 / 1024) "Used MB"
from   v$recovery_file_dest
order by name
/

RMAN Catalog DB operations

Create a user on catalog db.

  CREATE USER rman IDENTIFIED BY rman
  TEMPORARY TABLESPACE temp
  DEFAULT TABLESPACE users
  QUOTA UNLIMITED ON users;

Grant Recovery Catalog owner to that user

GRANT RECOVERY_CATALOG_OWNER TO rman;

Connect to catalog db and create catalog

rman CATALOG rman/rman@catalog

RMAN> create catalog;

rman TARGET sys/oracle@test10g CATALOG rman/rman@catolog RMAN> register database;
Opening channels in RMAN and distribute objects to channels
Example:

RUN
{
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS="ENV=(BACKUP_SERVER=tape_server1)";
  ALLOCATE CHANNEL c2 DEVICE TYPE sbt PARMS="ENV=(BACKUP_SERVER=tape_server2)";
  ALLOCATE CHANNEL c3 DEVICE TYPE sbt PARMS="ENV=(BACKUP_SERVER=tape_server3)";
  BACKUP
   (DATAFILE 1,2,3
    CHANNEL c1)
   (DATAFILECOPY '/tmp/system01.dbf', '/tmp/tools01.dbf'
     CHANNEL c2)
   (ARCHIVELOG FROM SEQUENCE 100 UNTIL SEQUENCE 102 THREAD 1
     CHANNEL c3);
}

source:

https://taliphakanozturken.wordpress.com/tag/delete-obsolete-backup/

Wednesday, November 9, 2016

ebs: Gather Statistics

In R11i/R12 customers should be using the FND_STATS command.

Do not use the ANALYZE command or DBMS_STATS package directly,

as doing so may cause incomplete statistics to be generated.

Use the following command to gather schema statistics:

   exec fnd_stats.gather_schema_statistics('ONT') < For a specific schema >
   exec fnd_stats.gather_schema_statistics('ALL') < For all schemas >

Use the following command for gathering statistics on a temporary table
(ie: temporary tablename = TEMP_tmp in schema ABC):
   exec fnd_stats.gather_table_stats('ABC','TEMP_tmp');

Source:
Best Practices for Gathering Statistics with Oracle E-Business Suite (Doc ID 1586374.1)

Concurrent Processing - How To Gather Statistics On Oracle Applications Release 11i and/or Release 12 - Concurrent Process,Temp Tables, Manually (Doc ID 419728.1)

Tuesday, November 1, 2016

compiling a form in ebs R12.1.3

1) Remove or Rename the existing .fmb file under "$AU_TOP/forms/US"

2) Remove or Rename the existing .fmx file under corresponding $PROD_TOP/forms/US.

3) Copy the .fmb to "$AU_TOP/forms/US"

4) Compile the form

cd $AU_TOP/forms/US

frmcmp_batch userid=apps/apps module=$AU_TOP/forms/US/APxxxx.fmb output_file=$AP_TOP/forms/US/APxxxx.fmx module_type=form compile_all=special

5) Verify the file created under the $PROD_TOP/forms/US

Thursday, October 20, 2016

Security Best Practices for Release 12

Actually I was looking for ebs R12 - SEEDED APPLICATION USER ACCOUNTS, in the process I stumbled on this blog and which I think answered my queries.

https://blogs.oracle.com/stevenChan/entry/security_best_practices_for_re

Best Practices for Securing Oracle E-Business Suite Release 12 (Metalink Note 403537.1)

Tuesday, September 27, 2016

Creating and Dropping UNDO Tablespace

SHOW PARAMETER UNDO

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '&undofile/undotbs201.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

DROP TABLESPACE APPS_UNDOTS1 INCLUDING CONTENTS AND DATAFILES;

CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE '&undofile/undo01.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M;

ALTER SYSTEM SET UNDO_TABLESPACE=APPS_UNDOTS1 SCOPE=BOTH;

DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

SHOW PARAMETER UNDO

Friday, July 29, 2016

Oracle11g Data Pump EXPDP Query Parameter

SOURCE:

expdp
system/*****
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=emp_jan16.dmp
LOGFILE=emp_jan16.log
TABLES=scott.emp
QUERY=emp:\"WHERE conversion_date between \'01-JAN-2016\' and \'31-JAN-2016\'\"

OR

expdp parfile=emp_jan16.par

DIRECTORY=DATA_PUMP_DIR
DUMPFILE=emp_jan16.dmp
LOGFILE=emp_jan16.log
TABLES=scott.emp
QUERY=emp:"WHERE conversion_date between '01-JUN-2016' and '28-JUN-2016'"

DESTINATION:

impdp
system/*****
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=emp_jan16.dmp
LOGFILE=emp_jan16_import.log
REMAP_SCHEMA=scott:scott
TABLE_EXISTS_ACTION=APPEND

Saturday, June 18, 2016

How to get process id attached with particular port in SunOS

Solution 1:

$ lsof -i :6200

Solution 2:

Usage:

#pcp -p PORT_NUMBER or
#pcp -P PROCESS_ID
-----------------------------------------------------------------------------------------------

#!/usr/bin/ksh
#
# # PCP (PID con Port)
# v1.10 08/10/2010 Sam Nelson sam @ unix.ms
#
# If you have a Solaris 8, 9 or 10 box and you can't
# install lsof, try this. It maps PIDS to ports and vice versa.
# It also shows you which peers are connected on which port.
# Wildcards are accepted for -p and -P options.
#
# Many thanks Daniel Trinkle trinkle @ cs.purdue.edu
# for the help, much appreciated.

#
i=0
while getopts :p:P:a opt
do
case "${opt}" in
p ) port="${OPTARG}";i=3;;
P ) pid="${OPTARG}";i=3;;
a ) all=all;i=2;;
esac
done
if [ $OPTIND != $i ]
then
echo >&2 "usage: $0 [-p PORT] [-P PID] [-a] (Wildcards OK) "
exit 1
fi
shift `expr $OPTIND - 1`
if [ "$port" ]
then
# Enter the port number, get the PID
#
port=${OPTARG}
echo "PID\tProcess Name and Port"
echo "_________________________________________________________"
for proc in `ptree -a | awk '/ptree/ {next} {print $1};'`
do
result=`pfiles $proc 2> /dev/null| egrep "port: $port$"`
if [ ! -z "$result" ]
then
program=`ps -fo comm= -p $proc`
echo "$proc\t$program\t$port\n$result"
echo "_________________________________________________________"
fi
done
elif [ "$pid" ]
then
# Enter the PID, get the port
#
pid=$OPTARG
# Print out the information
echo "PID\tProcess Name and Port"
echo "_________________________________________________________"
for proc in `ptree -a | awk '/ptree/ {next} $1 ~ /^'"$pid"'$/ {print $1};'`
do
result=`pfiles $proc 2> /dev/null| egrep port:`
if [ ! -z "$result" ]
then
program=`ps -fo comm= -p $proc`
echo "$proc\t$program\n$result"
echo "_________________________________________________________"
fi
done
elif [ $all ]
then
# Show all PIDs, Ports and Peers
#
echo "PID\tProcess Name and Port"
echo "_________________________________________________________"
for proc in `ptree -a | sort -n | awk '/ptree/ {next} {print $1};'`
do
out=`pfiles $proc 2>/dev/null| egrep "port:"`
if [ ! -z "$out" ]
then
name=`ps -fo comm= -p $proc`
echo "$proc\t$name\n$out"
echo "_________________________________________________________"
fi
done
fi
exit 0

command: not found (declare: not found)

If you have any of these shebangs

Code:

#!/bin/sh
#!/bin/dash
#!/bin/bash
#!/bin/tcsh
#!/bin/zsh
#!/bin/awesome_shell

but you still call your code with

Code:

sh my_script.sh

the program effectively used will be "sh", and the shebang will be ignored.

You need to use the appropriate shell

Code:

sh   my_script.sh     # If the shebang is #!/bin/sh
dash my_script.sh     # If the shebang is #!/bin/dash
bash my_script.sh     # If the shebang is #!/bin/bash
tcsh my_script.sh     # If the shebang is #!/bin/tcsh
zsh  my_script.sh     # If the shebang is #!/bin/zsh

or better yet, just give the full or relative path of the script

Code:

./my_script.sh

then the shell used, is the same as the shebang.

Monday, April 18, 2016

Script to check and update Profile value in ebs R12

SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value
FROM
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
upper(pro1.user_profile_option_name) like upper('%&profile%')
and pro.profile_option_name = pro1.profile_option_name
and pro.profile_option_id = pov.profile_option_id
--and resp.responsibility_name like '%General%Ledger%' /* comment this line if you need to check profiles for all responsibilities */
and pov.level_value = resp.responsibility_id (+)
and pov.level_value = appl.application_id (+)
and pov.level_value = u.user_id (+)
order by 1,2;

************************************

1. Run this sql query to check the value of the profile option at all levels:

set serveroutput on
set echo on
set timing on
set feedback on
set long 10000
set linesize 120
set pagesize 132
column SHORT_NAME format A30
column NAME format A40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format A60 wrap

select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10007, 'SERVRESP',
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(p.profile_option_name) like '%FND_INIT_SQL%'
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, level_set;

2. One can also run this sql statement if one is sure to have updated the profile option at Site level only:

SELECT val.level_id, val.level_value, val.profile_option_id, val.profile_option_value
FROM fnd_profile_options opt, fnd_profile_option_values val
WHERE opt.profile_option_name = 'FND_INIT_SQL'
AND opt.profile_option_id = val.profile_option_id;

3. Update the profile option to a NULL value.
For instance if one just updated the same at site level one should run (as APPS user):

~~UPDATE fnd_profile_option_values~~
~~SET profile_option_value = NULL~~
~~WHERE profile_option_id = 3157 AND~~
~~level_id = 10001 AND~~
~~level_value = 0;~~

~~COMMIT;~~

Instead of that use this PROCEDURE below:

set serveroutput on;
DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('GUEST_USER_PWD', 'GUEST/ORACLE', 'SITE');
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
commit;
END;

How to Change Profile Option Value Without Forms? (Doc ID 943710.1)

How To Check If a Profile Option Is Set In Oracle Applications? (Doc ID 470102.1)

Monday, February 1, 2016

Clonning R12.1.3 Instance

Cloning Oracle Applications Release 12 with Rapid Clone (Doc ID 406982.1)

Troubleshooting RapidClone issues with Oracle Applications R12.0 & R12.1 (Doc ID 603104.1)

Tuesday, November 17, 2015

ORA-01017: invalid username/password; logon denied on oracle.apps.fnd.odf2.FndXdfCmp

Issue:
Invoking Utility FndXdfCmp ...
Class: oracle.apps.fnd.odf2.FndXdfCmp
Method: applyXDF
Arguments: system &systempwd &un_apps &pw_apps &jdbc_protocol &jdbc_db_addr type &fullpath_cz_patch/115/xdf_CZ_MODEL_NODE_OBJ_TYPE.xdf &fullpath_fnd_patch/115/xdf_xsl

Solution:
Patch 17839156 Gives Error ORA-01017: invalid username/password; logon denied on oracle.apps.fnd.odf2.FndXdfCmp (Doc ID 1623924.1)

SQL> alter system set sec_case_sensitive_logon=false;

Tuesday, October 20, 2015

Backup of Weblogic Domain: pack command

/u01/app01/Oracle/Middleware/user_projects/domains/demo_domain[oraapp@weblogic bin]$ pwd
/u01/app01/Oracle/Middleware/wlserver_10.3/common/bin

[oraapp@weblogic bin]$ ./pack.sh
-domain="/u01/app01/Oracle/Middleware/user_projects/domains/demo_domain" -template="/u01/bkp/apps_bkp/17OCT15/demo_domain.jar"
-template_name="WebCenter Content Domain"
-log=/u01/bkp/apps_bkp/17OCT15/demo_domain.log
-log_priority=INFO
<< read domain from "/u01/app01/Oracle/Middleware/user_projects/domains/demo_domain"
>> succeed: read domain from "/u01/app01/Oracle/Middleware/user_projects/domains/demo_domain"
<< write template to "/u01/bkp/apps_bkp/17OCT15/demo_domain.jar"
....................................................................................................
>> succeed: write template to "/u01/bkp/apps_bkp/17OCT15/demo_domain.jar"
<< close template
>> succeed: close template
[oraapp@weblogic bin]$

RMAN: Cold Backup Script

#!/bin/sh
# Set Oracle Environment for DB
#. ~/.bashrc
. ~/.bash_profile

echo
echo "`date` – Started `basename $0`"
echo

export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
rman target / << EOF
spool log to rman_coldbackup.log;
set echo on
run
{
shutdown immediate;
startup mount;
allocate channel ch1 type disk format '/export/dbclone/DEV_RMAN_COLDBKP/full_backup_%d_%s_%p';
allocate channel ch2 type disk format '/export/dbclone/DEV_RMAN_COLDBKP/full_backup_%d_%s_%p';
allocate channel ch3 type disk format '/export/dbclone/DEV_RMAN_COLDBKP/full_backup_%d_%s_%p';
allocate channel ch4 type disk format '/export/dbclone/DEV_RMAN_COLDBKP/full_backup_%d_%s_%p';
allocate channel ch5 type disk format '/export/dbclone/DEV_RMAN_COLDBKP/full_backup_%d_%s_%p';
allocate channel ch6 type disk format '/export/dbclone/DEV_RMAN_COLDBKP/full_backup_%d_%s_%p';

BACKUP CURRENT CONTROLFILE format '/export/dbclone/DEV_RMAN_COLDBKP/ctrl_file_%d_%s_%p';
BACKUP AS COMPRESSED BACKUPSET DATABASE;

release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
release channel ch6;

alter database open;
}
EOF
if [ $? -eq 0 ]
then
echo "====================="
echo "RMAN Backup Completed"
echo "====================="
else
echo "=================="
echo "RMAN Backup Failed"
echo "=================="
exit 1
fi

echo
echo "`date` - Finished `basename $0`"
echo

Monday, October 19, 2015

Enable Tracing in EBS: "Initialization SQL Statement – Custom"

A user event trace is very handy for tracing sql operations to debug various issues. The benefit of a user event trace is that it is linked to a specific user so that only code run by this user is traced. This makes diagnosis easier when compared to similar tracing methods at the database level where all user calls are traced.

Step 1
Login to Oracle Applications and select the System Administrator responsibility.
Choose Profile - System

Step 2
In the find profile field select the user which you wish to trace. In the profile field enter 'Initialization SQL Statement - Custom"
Select find

Step 3
In the find profile results form copy and paste the following into the 'user' field. Do not update the site level field.

BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'USERID' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;

Note: copy and paste the above as one line. Failure to paste the values properly or pasting the values with incorrect syntax will result in this user not being able to login.

Step 4 (Optional)
Save the profile option change. Stop and start the java virtual machine for the change to take effect
11i: use adapcctl.sh
r12: use adoacorectl.sh

Step 5
Login to the application and reproduce the issue. Then quickly log off. Try and avoid any un-necessary keystrokes as this simply makes the log files larger and the issue hard to pinpoint in the logs.

Step 6
login to unix/windows as the oracle user. Navigate to the user dump destination which us usually $ORACLE_HOME/admin/<context>/udump
You will see a series of trace files generated during the time of your issue reproduction. Tkprof all of the generated files and upload both the raw and tkprof files to your service request.

You can also check the user dump destination via the following:
sql> show parameter user_dump_dest;

Notes:
When using this profile, DO NOT turn trace on the menu, as doing so would actually turnoff Event 10046. Turning Event 10046 to any level, turns trace automatically.

Event 10046 level 1 is regular trace, level 4 is tracing with bind variables, level 8 is with database waits and level 12 with both,bind variables and database waits.

alter system set max_dump_file_size=UNLIMITED scope=both;

Sunday, February 12, 2017

Wednesday, January 4, 2017

Monday, January 2, 2017

Thursday, December 29, 2016

Wednesday, December 28, 2016

Wednesday, November 9, 2016

Tuesday, November 1, 2016

Thursday, October 20, 2016

Tuesday, September 27, 2016

Friday, July 29, 2016

Saturday, June 18, 2016

Tuesday, April 26, 2016

Monday, April 18, 2016

Monday, February 1, 2016

Tuesday, November 17, 2015

Tuesday, October 20, 2015

Monday, October 19, 2015