Oracle Apps DBA interview questions and Core DBA interview questions
Imp Questions:
Most of them are open questions that I can think of , they have multiple correct answers, we need to choose the best one we can explain,this questions are sourced from multiple interviews are attended and multiple blogs,If anyone needs credit for any information,I will email
2. check the concurrent manager log and out files and delete them if occupying lot of space
to check report based locks:-
you know that conc req id.. by using that
select * from v$session where process in(select os_process_id from fnd_concurrent_requests where request_id='<Req Id>')
from this u will get SID
take that sid and find object_id's from below command
select * from v$locked_objects where session_id='SID'
no take all the object_id's and check any other session is locking those object_ids appart from the above SID...
select * from v$locked_objects where object_id =''
if any session is locking check the status of that session from v$session if that is inactive kill that session ... to free up the resource
________________
Primary Server: Step 1
Oracle Data Guard requires the primary database to be run in FORCE LOGGING mode. This means that statements using the NOLOGGING option will still generate redo information to maintain the Data Guard standby databases.
[oracle@dg1 ~]$ sqlplus / as sysdba
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL>
The standby log files are required to store redo received from the primary database. Standby redo logs must be equal or larger to the largest redo log file in the primary database. The standby redo log must have at least one more redo log group than the redo log on the primary database. It is recommended that the standby redo log is created on the primary database and propagated to the standby for any switch over or fail over operations.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 7 52428800 512 2 YES INACTIVE 828952 15-JAN-12 849105 16-JAN-12
2 1 8 52428800 512 2 NO CURRENT 849105 16-JAN-12 2.8147E+14
3 1 6 52428800 512 2 YES INACTIVE 822304 15-JAN-12 828952 15-JAN-12
SQL> alter database add standby logfile size 50M;
Database altered.
SQL>
SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.
SQL>
SQL> select * from v$logfile;
Next, set the LOG_ARCHIVE_CONFIG parameter. The LOG_ARCHIVE_CONFIG parameter enables or disables the sending of redo streams to the standby sites. The DB_UNIQUE_NAME of the primary database is dg1 and the DB_UNIQUE_NAME of the standby database is dg2. The primary database is configured to ship redo log stream to the standby database. In this example, the standby database service is dg2.
Next, STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are added or dropped from the primary database, these changes are made to the standby databases automatically. The STANDBY_FILE_MANAGEMENT is only applicable to the physical standby databases.
Setting the STANDBY_FILE_MANAGEMENT parameter to AUTO is is recommended when using Oracle Managed Files (OMF) on the primary database. Next, the primary database must be running in ARCHIVELOG mode.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
db_name string dg1
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
db_unique_name string dg1
SQL> alter system set log_archive_config='dg_config=(dg1,dg2)';
System altered.
SQL> alter system set log_archive_dest_2=
2 'service=dg2 async valid_for=(online_logfile,primary_role) db_unique_name=dg2';
System altered.
SQL> alter system set standby_file_management=AUTO;
System altered.
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
SQL> exit;
Ensure the Oracle Net Services aliases for both the primary database and standby database are added to the tnsnames.ora file.
[oracle@dg1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
dg1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg1)
)
)
dg2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg2)
)
)
[oracle@dg1 ~]$
Copy the updated tnsnames.ora file to the standby site (host).
Standby Server: Step 2
The Oracle database binaries have already been installed at this location ($ORACLE_HOME). The new standby database will have dg2 as the SID.
The listener on the standby site must be configured with a static service entry for the standby database. Restart the listener after modification.
[oracle@dg2 admin]$ cat listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=dg2.localdomain)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=dg2)
(SID_NAME=dg2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)
[oracle@dg2 admin]$ lsnrctl start
Create audit directory files under $ORACLE_BASE/admin/$ORACLE_SID/adump. In my example, I will also create the Fast recover area and the oradata directories.
[oracle@dg2 ~]$ mkdir -p $ORACLE_BASE/admin/dg2/adump
[root@dg2 ~]# mkdir -p /opt/oradata
[root@dg2 ~]# mkdir -p /opt/fast_recovery_area
Next, create a simple parameter file on the standby hist with the DB_NAME value.
[oracle@dg2 dbs]$ echo DB_NAME=dg2 > initdg2.ora
[oracle@dg2 dbs]$ cat initdg2.ora
DB_NAME=dg2
[oracle@dg2 dbs]$
The primary database password file must be copied to the standby system for redo authentication.
[oracle@dg2 dbs]$ scp dg1:$ORACLE_HOME/dbs/orapwdg1 orapwdg2
In the next part of the document, we will create the standby database, verity the physical standby database. We will Standby Database Creation
Start the standby database instance in NOMOUNT start up mode:
SQL> startup nomount pfile=initdg2.ora;
Now that the configuration of the standby server is complete, let’s perform the duplication from the primary site.
Primary Server
Use the Recovery Manager (RMAN) to duplicate the primary database to the standby database.
Invoke RMAN; connect to the primary database as the sys user. Make an auxiliary connection to the standby instance:
RMAN> connect target sys
target database Password:
connected to target database: DG1 (DBID=1753913301)
RMAN> connect auxiliary sys@dg2
auxiliary database Password:
connected to auxiliary database: DG2 (not mounted)
RMAN> run{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'dg1','dg2'
set db_unique_name='dg2'
set db_file_name_convert='/dg1/','/dg2/'
set log_file_name_convert='/dg1/','/dg2/'
set control_files='/opt/oradata/dg2/dg2.ctl'
set log_archive_max_processes='5'
set fal_client='dg2'
set fal_server='dg1'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(dg1,dg2)'
set log_archive_dest_2='service=dg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dg1'
;
}
The RMAN duplication process is finished without errors. After the database duplication, we will force the log switch on the primary database to archive the current redo log group.
SQL> alter system switch logfile;
Standby Server
On the standby database, run the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE command to start redo apply.The USING CURRENT LOGFILE means the redo is applied as soon as it is received on the standby.The DISCONNECT option means the redo apply will run in background session.
SQL> alter database recover managed standby database
using current logfile disconnect;
Note that the current log sequence number on the standby is 10.
SQL> select sequence#, first_time, applied
from v$archived_log
order by sequence#;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
9 16-JAN-12 YES
10 16-JAN-12 IN-MEMORY
Primary Server
Let’s perform 3 additional log switches on the primary database. Then we will query the standby database to verify that the log files are applied to the standby.
SQL> alter system switch logfile;
Standby Server
We query the standby database. The logs were successfully transported and applied.
SQL> select sequence#, first_time, applied
from v$archived_log
order by sequence#;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
9 16-JAN-12 YES
10 16-JAN-12 YES
11 16-JAN-12 YES
12 16-JAN-12 YES
13 16-JAN-12 IN-MEMORY
Broker Configuration
s_webentryhost load balancer hostname
s_webentrydomain load balancer domain name
s_active_webport load balancer port
s_login_page load balancer url
s_external_url load balancer url
Section 5: Configuring the E-Business Suite for DMZ Deployments
This section provides the configuration instructions for the deployment models described in this document. Certain common configuration steps must be carried out regardless of which deployment model is used. The details for these common steps are explained from section 5.1 through section 5.4. After completing the common steps, you can proceed to either section 5.5, section 5.6 or section 5.7 depending on which deployment option is chosen.
5.1: Update Hierarchy Type
Several user profile options are used to construct various URLs in an E-Business Suite R12 environment. These user profiles are as follows:
User Profile Name Internal Name
1. Applications Web Agent APPS_WEB_AGENT
2. Applications Servlet Agent APPS_SERVLET_AGENT
3. Applications JSP Agent APPS_JSP_AGENT
4. Applications Framework Agent APPS_FRAMEWORK_AGENT
5. ICX:Forms Launcher ICX_FORMS_LAUNCHER
6. ICX: Oracle Discoverer Launcher ICX_DISCOVERER_LAUNCHER
7. ICX: Oracle Discoverer Viewer Launcher ICX_DISCOVERER_VIEWER_LAUNCHER
8. Applications Help Web Agent HELP_WEB_AGENT
9. Applications Portal APPS_PORTAL
10. BOM:Configurator URL of UI Manager CZ_UIMGR_URL
11. QP: Pricing Engine URL QP_PRICING_ENGINE_URL
12. TCF:HOST TCF:HOST
The default hierarchy type value for the above profile options could be either Security/Server. See diagram below:
The configuration of the E-Business Suite environment for DMZ requires these profile options hierarchy type to be set to SERVER-RESPONSIBILITY TYPE.
1. To change the profile options hierarchy type values to SERVRESP, execute thetxkChangeProfH.sqlSQL script as shown below:
sqlplus apps/apps@/patch/115/sql/txkChangeProfH.sql SERVRESP
2. After thetxkChangeProfH.sqlscript executes successfully, run AutoConfig on all nodes to complete the profile options configuration.
5.2: Update Node Trust Level
Oracle E-Business Suite Release 12 has the capability to restrict access to a predefined set of responsibilities based on the Web server from which the user logs in. This capability is provided by tagging web servers with a trust level indicated by the Node Trust Level (NODE_TRUST_LEVEL) server profile option. The Node Trust Level indicates the level of trust associated with a particular web server. Currently, three trust levels are supported:
Administrative
Servers marked as Administrative are typically those used exclusively by system administrators. These servers are considered secure and provide access to any and all E-Business Suite functions.
Normal
Servers marked as Normal are those used by employees within a company’s firewall. Users logging in from normal servers have access to only a limited set of responsibilities.
External
Servers marked as External are those used by customers or employees outside of a company’s firewall. These servers have access to an even smaller set of responsibilities.
The default value for this profile option for all E-Business Suite middle tiers is Normal. If you wish to learn more about the Node Trust Level profile option, please refer to Oracle Applications System Administrators Guide .
Set the NODE_TRUST_LEVEL profile option value on the external web tier in your Oracle E-business Suite Release 12 environment to External. See diagram below.
To change the value of the Node Trust Level profile option value to External for a particular node, perform the following steps:
⦁ Login to Oracle E-Business Suite as sysadmin user using the internal URL
⦁ Select the System Administrator Responsibility
⦁ Select Profile / System
⦁ From the 'Find system profile option Values' window, select the server that you want to designate as the external web tier
⦁ Query for%NODE%TRUST%. You will see a profile option named 'Node Trust Level'. The value for this profile option at the site level will be Normal. Leave this setting unchanged.
⦁ Set the value of this profile option to External at the server level. The site level value should remain set to Normal
5.3: Update List of Responsibilities
The steps described in this section are required only if you have marked any of the Oracle E-Business Suite Release 12 middle tiers as External as described in section 5.2.
After updating the server-level profile value for Node Trust Level for the external web tier(s) to External, users can no longer see any responsibilities when they login via the external web tier. In order for a responsibility to be available from the external E-Business Suite web tier, set the Responsibility Trust Level profile option value for that responsibility to External at the responsibility level. For information on additional product specific responsibilities that can be made externally accessible from the external E-Business Suite middle tier, please refer to Appendix B1. Oracle E-Business Suite Product Specific Configurations.
To change the value of the Responsibility Trust Level profile option at the responsibility level for a particular responsibility, perform the following steps:
⦁ Login to Oracle E-Business Suite as sysadmin user using the internal URL
⦁ Select System Administrator Responsibility
⦁ Select Profile / System
⦁ From the 'Find system profile option Values' window, select the responsibility that you want to make available to users logging in via the external web tier
⦁ Query for%RESP%TRUST%. You will see a profile option named 'Responsibility trust level'. The value for this profile option at site level will be Normal. Leave this setting unchanged.
⦁ Set the value of this profile option for the chosen responsibility to External at the responsibility level. The site-level value should remain Normal.
⦁ Repeat for all responsibilities that you want to make available from the external web tier.
5.4: Configuration Details for Using Reverse Proxy and an External Web Tier in DMZ
The steps described in this section assume that you have already set up the reverse proxy server of your choice and you are ready to make modifications to the Oracle E-Business Suite Applications Context file on the external web tier. To complete the configuration for this option, follow the steps given below.
Oracle does not certify specific reverse proxy solutions from third-party vendors. The instructions included in this document are generally applicable to third-party reverse proxy solutions, including (but not restricted to) Apache, Microsoft Proxy Server, and other products.
5.4.1: Update Oracle E-Business Suite Applications Context File
On the external Oracle E-Business Suite web node, run the AutoConfig Context Editor as documented in the Oracle MetaLink note 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications Release 12". In the Context Detail screen, set the following configuration values:
⦁ set the webentry point, s_webentryhost, to the reverse proxy server
⦁ set the webentry domain, s_webentrydomain, to the domain name of the reverse proxy server
⦁ set the external URL, s_external_url to the external web node URL.
⦁ set the active webport, s_active_webport, to the port where the reverse proxy server listen for client requests. For example port 80 for HTTP or 443 for HTTPS
⦁ set the webentry protocol, s_webentryurlprotocol, to the protocol value the clients use to access the reverse proxy server
⦁ set the login page, s_login_page, to <webentry protocol>://<webentry host>.<webentry domain>:<active web port>. Replace <webentry protocol>, <webentry host>, <webentry domain>, and <active web port> with their respective values
⦁ set the help web agent s_help_web_agent, to <webentry protocol>://<webentry host>.<webentry domain>:<active web port> . Replace <webentry protocol>, <webentry host>, <webentry domain>, and <active web port> with their respective values.
5.4.2: Run AutoConfig and Restart Oracle Application Server Processes
⦁ Run AutoConfig on each Applications middle tier . Please refer to the Oracle MetaLink note ⦁ 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications R12 " for more information on AutoConfig.
⦁ After AutoConfig completes successfully, restart Oracle Application server processes on the external web tier.
Proceed to the Appendices for any additional Oracle E-Business Suite product specific settings that needs to be done.
5.5: Configuration Details for Using Separate Oracle E-Business Suite Web Tier in DMZ
There are no extra steps needed for this configuration. Proceed to the Appendices for any additional Oracle E-Business Suite product specific settings that needs to be done.
5.6: Configuration Details for Using HTTP Hardware Load Balancers in DMZ
To complete the configuration for this option, follow the steps given below.
5.6.1: Update Oracle Applications Context File
On the internal Applications middle-tier nodes, run the AutoConfig Context Editor as documented in the Oracle MetaLink note 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications R12 ". In the Context Detail screen, set the following configuration values:
⦁ set the webentry point, s_webentryhost, to the load balancer that is used to load balance the internal Applications middle tiers
⦁ set the webentry domain, s_webentrydomain, to the domain name of the load balancer
⦁ set the active webport, s_active_webport, to the value of the load balancer's external port
⦁ set the webentry protocol, s_webentryurlprotocol, to the load balancer's protocol e.g. "http" or "https".
⦁ set the login page, s_login_page, to<webentry protocol>://<webentry host>.<webentry domain>:<active web port>. Replace <webentry protocol>, <webentry host>, <webentry domain>, and <active web port> with their respective values
⦁ set the help web agent, s_help_web_agent, to<webentry protocol>://<webentry host>.<webentry domain>:<active web port> . Replace <webentry protocol>, <webentry host>, <webentry domain>, and <active web port> with their respective values
On the external Applications web tier node, run the AutoConfig Context Editor as documented in the Oracle MetaLink note 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications R12 ". In the Context Detail screen, set the following configuration values:
⦁ set the webentry point, s_webentryhost, to the load balancer that is used to load balance the external Applications middle tiers
⦁ set the webentry domain, s_webentrydomain, to the domain name of the load balancer
⦁ set the external URL, s_external_url to the external web node URL.
⦁ set the active webport, s_active_webport, to the value of the load balancer's external port
⦁ set the webentry protocol, s_webentryurlprotocol, to the load balancer's protocol e.g. "http" or "https"
⦁ set the login page, s_login_page, to <webentry protocol>://<webentry host>.<webentry domain>:<active web port>. Replace <webentry protocol>, <webentry host>, <webentry domain>, and <active web port> with their respective values.
⦁ set the help web agent, s_help_web_agent, to<webentry protocol>://<webentry host>.<webentry domain>:<active web port> . Replace <webentry protocol>, <webentry host>, <webentry domain>, and <active web port> with their respective values
5.6.2: Run AutoConfig and Restart Oracle Applications Processes
⦁ Run AutoConfig on each Applications middle tier to complete the configuration. Please refer to the Oracle MetaLink note ⦁ 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications R12 " for more information on AutoConfig.
⦁ After AutoConfig completes successfully, restart Oracle Applications server processes.
Proceed to the Appendices for any additional Oracle E-Business Suite product specific settings that needs to be done.
5.7: Enable Oracle E-Business Suite Application Server Security
Oracle E-Business Suite Release 12 is deployed in a multi-tier configuration with one Database Server and many possible middle-tier Application Servers. The Application Servers include Apache JSP/Servlet, Forms, Discoverer and also some client programs such as Application Desktop Integrator, Oracle Discoverer Admin Edition. Any program which makes a SQLNET connection to the Oracle E-Business Suite database needs to be trusted at some level. This security feature ensures that such SQLNET connections are coming from trusted machines and/or trusted programs.
The Server Security feature supports authentication of application server machines and code modules in order to access the database. When Server Security is activated, Application Servers are required to supply server IDs (like passwords) and/or code IDs to access a database server. Server IDs identify the machine from which the connection is originating. Code IDs identify the module and patch level from which the connection is originating. Code IDs are included in applications code by development. The database server can be set to allow access only from specific machines and/or by code at a desired patch level.
The application server security feature is activated by default for all E-Business Suite installations. It is recommended that you ensure that the server security feature is enabled by performing the steps given below:
Run the AutoConfig Context Editor as documented in the Oracle MetaLink note 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications R12 ". In the Context Detail screen, review the following configuration values for both internal and external nodes:
⦁ Value of Application Server Security Authentication (s_appserverid_authentication) is set to SECURE .If the value is not set to SECURE, follow the instructions given below:
⦁ Set the value of Application Server Security Authentication (s_appserverid_authentication) to SECURE
⦁ Run AutoConfig on each Applications middle tier to complete the configuration. Please refer to the Oracle MetaLink note ⦁ 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications R12 " for more information on AutoConfig
⦁ After AutoConfig completes successfully, restart Oracle HTTP Server and OC4J processes
5.8: Enable Distributed Oracle Java Object Cache Functionality
Distributed caching functionality has to be enabled in a DMZ environment to avoid data inconsistencies for data such as profiles, menu, responsibilities and product specific data. To complete this configuration, follow the steps given below:
⦁ Identify the highest number of JVMs that serve the oacore JVM group in the internal and external middle tiers. For eg: if there are 3 JVMs in the internal and 2 JVMs configured for the external middle tier, take the number as 3.
⦁ Identify the number of java processes spawned by the concurrent manager tier. For eg: if there are 3 JVMs spawned by the ICM, take the number as 3 . Add this to the number of oacore JVMs . In the example given above, the total number JVMs thus become 6 . So, six ports need to be opened in the firewall. You can use the 'pstree' command to check the number of java processes spawned by the concurrent manager parent process. For eg:pstree -p 26258 where 26258 is the process ID of the FNDSM process.
⦁ Identify the ports to open in the firewall that separates the external middle tier and the internal middle tier . For eg: if the JVM count is 3, you have to open 3 ports on this firewall.
⦁ This range of ports need to be specified as a value for the autoconfig variable ( s_fnd_cache_port_range ) . Please make sure that the value is same in all the applications context files . The value should be specified as a range. For eg: 36500-36505. When AutoConfig completes the configuration, the value specified for this variable in the context file will get updated in the FND_CACHE_PORT_RANGE profile option.
⦁ In addition to the ports specified above, you must ensure that the Java Object Cache Port specified as a value for the autoconfig variable s_java_object_cache_port is also open on the firewall that separate the external and internal middle tiers.
You must run Autoconfig to complete the configuration after editing the applications context file.
Attention
In a multinode installation, the AutoConfig variable s_java_object_cache_port must be set identically on all nodes. Similarly, s_fnd_cache_port_range must be set identically on all nodes. Please note that s_java_object_cache_port must be set to a different value from s_fnd_cache_port_range in the same applications context file to avoid port conflicts.
5.9: Configuration Details for Using reverse proxy with No External Web Tier
This configuration requires your internal application middle tier server to have at least two network interfaces. One network interface is required for the external entry point and another for the internal entry point. These network interfaces must be configured to resolve to two different hostnames in the DNS.
For example:
/etc/hosts of Internal Server 1
130.30.21.1 internal1.company.com internal1
130.30.21.2 external1.company.com external1
5.9.1: Create a new context file for the external Web Entry Point
⦁ To create a context file for the external entry point, execute the commands shown in the table below:
$ perl $COMMON_TOP/clone/bin/adclonectx.pl \
contextfile=$CONTEXT_FILE \
outfile= <name of the output file including location>
For example:
Internal Server Name 1: internal1.company.com
External Server Name 1: external1.company.com
Context file for Internal Entry Point on Internal Server 1 including its location: /d1/applmgr/v
Solution:
· Setup the primary node with 11.5.10.2 ATG RUP6 installed and having CP,Admin,Forms,Web and DB set on it.
· Add and register a new secondary node which has just CP and Admin on it. (You can use shared APPL_TOP for this setup)
· If using Virtual Hostnames, please ensure that the Physical Hostname of the CP node with virtual host is registered from OAM.
· Once both nodes are ready, set the APPLDCP variable to ON in the context file of both nodes.
· Shutdown the application tier services of both the nodes and run AutoConfig on each node. After AutoConfig completes successfully verify that the tnsnames.ora (on both CP nodes) has the FNDFS entries of both the nodes.
· Ensure that the Internal Monitors on both nodes defined properly and have workshifts assigned to them. Also make sure the Internal Monitor manager is activated by going into Concurrent -> Manager -> Adminitrator and activate the manager as they need to be active on the nodes where the ICM can start in case of a failure..
· Define the Primary and Secondary nodes for your Internal Concurrent Manager and Standard Managers (Concurrent Manager Define form)
To ensure that failover of the concurrent managers when one concurrent node fails happens in time,ensure following steps are followed :
· The sqlnet.expire_time in sqlnet.ora is set to 1. This is to be done for the database listeners (not apps listener).
· Ensure that One-off Patch 6495206 and Patch 9074947 have been applied.
⦁ Talk about differences in 11g and 10g
Automatic Memory Tuning - Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was introduced in Oracle 10g. In 11g, all memory can be tuned automatically by setting one parameter. PGA Update: Oracle technology is constantly changing, so don't miss my new notes on updates to Oracle PGA behavior. Also see these important notes on over-riding the Oracle PGA defaults.
Enhanced Password - Pete Finnigan notes some new Oracle 11g security features "[Oracle 11g] will have case sensitive passwords and also the password algorithm has changed to SHA-1 instead of the old DES based hashing used."
Automatic Diagnostic Repository - When critical errors are detected, Oracle automatically creates an “incident” ticket, notifying the DBA instantly.
New table Data Type "simple_integer" - A new 11g datatype dubbedsimple_integer is introduced. The simple_integer data type is always NOT NULL, wraps instead of overflows and is faster than PLS_INTEGER. Source:Lewis Cunningham
RMAN UNDO bypass - RMAN backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. Now RMAN can bypass those types of tablespace. Great for exporting a tablespace from backup. Source: Laurent Schneider
Virtual columns - Oracle 11g virtual table columns are columns that are actually functions ("create table t1 (c1 number, c2 number, c3 as (c1+c2) virtual"), and similarly, virtual indexes that are based on functions.
Oracle 11g RAC parallel upgrades - Oracle 11g promises to have a rolling upgrade features whereby RAC database can be upgraded without any downtime. Ellison first promised this feature i
Disabled state for PL/SQL - Another 11g new feature is a "disabled" state for PL/SQL (as opposed to "enabled" and "invalid" in dba_objects).
The /*+result_cache*/ SQL hint - This suggests that the result data will be cached in the data buffers, and not the intermediate data blocks that were accessed to obtain the query
Automatic Memory Tuning - Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was introduced in Oracle 10g. In 11g, all memory can be tuned automatically by setting one parameter. You literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set. This is controlled by the Oracle 11g memory_target parameter.
⦁ Differences in 11i and r12
⦁ what is request flow process in database and in applications?
⦁ how do you monitor the load on the DB?
⦁ How do you monitor apps? (what all components needs monitoring)
⦁ what does adcfgclone do ? what are important driver files run by autoconfig?
⦁ fndload usages?
⦁ what do you do when a customer tells he is not able to ping the URL of ebs
⦁ what all issues you faced while running autocfg
⦁ What do u know about Workflow
⦁ What is a DMZ why do we need it
⦁ How many types of patches did u work on
⦁ What errors did you face in upgrade process that effected delivery time
⦁ What do u do in case of SEV1 what is step by step procedure you follow
⦁ How do u restore a datafile that was deleted accidentally
⦁ What do u know about ASM
⦁ On how many OS have u worked do u know basics of shell scripting?
⦁ RAC architecture vs Standby vs Goldengate .. what do think will giv higst availability and failover & why
⦁ Describe how you do a general code migration. (don’t forget to tell that u take backup of already existing file)
⦁ Did u generate any business out of your own recommendations to clients?
⦁ What were the sizes of the database you worked on and the amount of activity (tell in amount of log generated per day).
⦁ What very imp features does 12c bring?
⦁ Tell me how online patching is possible in 12c?
⦁ What is the basic element of base configuration of an Oracle database?
⦁ What happens if you don’t give cache size while defining concurrent manager?
Most often when …a request goes "inactive/no manager" and is then processed a short time later, the solution is to either increase the cache size for your Standard manger, or increase the actual number of Standard Manager processes that can run. Cache Size is set in the Concurrent/Manager/Define form. Basically, this regulates how many requests a manager will pick up for each sleep cycle.
Increasing the cache size within reason allows you to decrease the sleep cycle. In turn, your managers will wake up and scroll through the tables less frequently, which reduces the amount of work they have to do as well as the amount of system resources utilized. We see sleep cycles set to 5 seconds at some customer sites and recommend increasing the value, since the managers are waking up and re-reading the same table far too frequently. For reports that for the most part take a minimum of 1 minute to run, the queue wakes up 12 times to check for runnable processes before even one running request completes.
⦁ Why should Apps & Applsys passwords always be the same?
⦁ How do u find versions of various components (apache,disco,forms etc)
SQL> select CONCURRENT_PROGRAM_NAME from fnd_concurrent_programs where QUEUE_CONTROL_FLAG='Y';
If the ICM goes down, you can restart it with 'startmgr'. There is no need to shut down the other managers first.
⦁ After a sys password change on prod my archives are not getting applied on standby , why? What do u do to resolve?
⦁ What do you know about forms palyback?
⦁ Right now a client on 10g asked u to upgrade, which version do u suggest? (always stick with the stable version nd never the new version, 11.2.0.3 in this case)
⦁ Can I run 64 bit application on 32 bit Operating system ?
Topics:
Architecture both DB and Apps and comparisons between old and new versions
RAC and ASM
Standbys
Patching
Cloning DB and Apps
Upgrades DB and Apps ( they may ask about min conf requirement for the verion u have upgraded for OS and also imp patches)
Common issues that you faced and how u resolved them
R12 and 11i Filestructure
R12.2 file structure
⦁ Use of Oracle WebLogic Server instead of AutoConfig for many (but not all) of AutoConfig's previous functions.
⦁ Use of Oracle WebLogic Server instead of Oracle Containers for Java (OC4J).
Edition based redefinition
Adcfgclone
I am covering the Internal Cloning Process which you will not get eaily on the net. Generally you find the steps for cloning like run adpreclone on apps and db tier then copy the file sytem to Target then run adcfgclone on apps tier and db Tier.
What happens when you run adpreclone and adcfgclone. The below steps give you better understanding and enhance your troubleshooting skills.
When you run this commnad adpreclone.pl dbTier . This will run in two steps Techstack and database.
Techstack:
It will create following directories in the ORACLE_HOME/appsutil/clone
Jlib, db, data where “Jlib” relates to libraries “db” will contain the techstack information, “data” will contain the information related to datafiles and required for cloning.
Creates driver files at ORACLE_HOME/appsutil/driver/instconf.drv
Converts inventory from binary to xml, the xml file is located at $ORACLE_HOME/appsutil/clone/context/db/Sid_context.xml
Prepare database for cloning:
This includes creating datbase control file script and datafile location information file at
$ORACLE_HOME/appsutil/template
adcrdbclone.sql, dbfinfo.lst
Generates database creation driver file at ORACLE_HOME/appsutil/clone/data/driver
data.drv
Copy JDBC Libraries at ORACLE_HOME/appsutil/clone/jlib/classes12.jar and appsoui
When Running adpreclone appsTier
This will create stage directory at $COMMON_TOP/clone. This also run in two steps.
Techstack:
Creates template files for
Oracle_iAS_Home/appsutil/template
Oracle_806_Home/appsutil/template
Creates Techstack driver files for
IAS_ORACLE_HOME/appsutil/driver/instconf.drv
806_ORACLE_HOME/appsutil/driver/instconf.drv
APPL_TOP preparation:
-It will create application top driver file$COMMON_TOP/clone/appl/driver/appl.drv
-Copy JDBC libraries$COMMON_TOP/clone/jlib/classes111.zip
Now Shutdown all the services of Application and database for Copy the file System to target location
Configuring the target system
Once it is done . Run as below adcfclone.pl for apps Tier and dbTier.
On database side:
cd $ORACLE_HOME/appsutils/clone/binperl adcfgclone.pl dbTier pwd=apps
This will use the templates and driver files those were created while running adpreclone.pl on source system and has been copied to target system.
Following scripts are run by adcfgclone.pl dbTier for configuring techstack
adchkutl.sh — This will check the system for ld, ar, cc, and make versions.
adclonectx.pl — This will clone the context file. This will ceate a new context file as per the details of this instance.
runInstallConfigDriver — located in $Oracle_Home/appsutil/driver/instconf.drv
Relinking $Oracle_Home/appsutil/install/adlnkoh.sh — This will relink ORACLE_HOME
For data on database side, following scripts are run
Driver file $Oracle_Home/appsutil/clone/context/data/driver/data.drv
Create database adcrdb.zip
Autoconfig is run
Control file creation adcrdbclone.sql
On Application Side:
COMMON_TOP/clone/bin/perl adcfgclone.pl appsTier pwd=apps
Following scripts are run by adcfgclone.pl
Creates context file for target adclonectx.pl
Run driver files
$ORACLE_HOME/appsutil/driver/instconf.drv
$IAS_ORACLE_HOME/appsutil/driver/instconf.drv
Relinking of Oracle Home$ORACLE_HOME/bin/adlnk806.sh$IAS_ORACLE_HOME/bin/adlnkiAS.sh
At the end it will run the driver file $COMMON_TOP/clone/appl/driver/appl.drv and then runs autoconfig.
Background processes
To maximize performance and accommodate many users, a multiprocess Oracle system uses some additional Oracle processes called background processes.
An Oracle instance can have many background processes; not all are always present. There are numerous background processes. See the V$BGPROCESS view for more information on the background processes. The background processes in an Oracle instance can include the following:
Database Writer Process (DBW⦁ n⦁ )
Log Writer Process (LGWR)
Checkpoint Process (CKPT)
System Monitor Process (SMON)
Process Monitor Process (PMON)
Recoverer Process (RECO)
Job Queue Processes
Archiver Processes (ARC⦁ n⦁ )
Queue Monitor Processes (QMN⦁ n⦁ )
Other Background Processes
On many operating systems, background processes are created automatically when an instance is started.
Figure 9-2 illustrates how each background process interacts with the different parts of an Oracle database, and the rest of this section describes each process.
See Also:
Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for more information. Real Application Clusters are not illustrated in ⦁ Figure 9-2
⦁ Your operating system-specific documentation for details on how these processes are created
Figure 9-2 Background Processes of a Multiple-Process Oracle Instance
Description of "Figure 9-2 Background Processes of a Multiple-Process Oracle Instance"
Database Writer Process (DBWn)
The database writer process (DBWn) writes the contents of buffers to datafiles. The DBWn processes are responsible for writing modified (dirty) buffers in the database buffer cache to disk. Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes (DBW1 through DBW9 and DBWa through DBWj) to improve write performance if your system modifies data heavily. These additional DBWn processes are not useful on uniprocessor systems.
When a buffer in the database buffer cache is modified, it is marked dirty. A cold buffer is a buffer that has not been recently used according to the least recently used (LRU) algorithm. The DBWn process writes cold, dirty buffers to disk so that user processes are able to find cold, clean buffers that can be used to read new blocks into the cache. As buffers are dirtied by user processes, the number of free buffers diminishes. If the number of free buffers drops too low, user processes that must read blocks from disk into the cache are not able to find free buffers. DBWn manages the buffer cache so that user processes can always find free buffers.
By writing cold, dirty buffers to disk, DBWn improves the performance of finding free buffers while keeping recently used buffers resident in memory. For example, blocks that are part of frequently accessed small tables or indexes are kept in the cache so that they do not need to be read in again from disk. The LRU algorithm keeps more frequently accessed blocks in the buffer cache so that when a buffer is written to disk, it is unlikely to contain data that will be useful soon.
The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes. The maximum number of DBWn processes is 20. If it is not specified by the user during startup, Oracle determines how to set DB_WRITER_PROCESSES based on the number of CPUs and processor groups.
The DBWn process writes dirty buffers to disk under the following conditions:
⦁ When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers, it signals DBWn to write. DBWn writes dirty buffers to disk asynchronously while performing other processing.
⦁ DBWn periodically writes buffers to advance the checkpoint, which is the position in the redo thread (log) from which instance recovery begins. This log position is determined by the oldest dirty buffer in the buffer cache.
In all cases, DBWn performs batched (multiblock) writes to improve efficiency. The number of blocks written in a multiblock write varies by operating system.
See Also:
"Database Buffer Cache"
Oracle Database Performance Tuning Guide for advice on setting DB_WRITER_PROCESSES and for information about how to monitor and tune the performance of a single DBW0 process or multiple DBWn processes
Oracle Database Backup and Recovery Basics
Log Writer Process (LGWR)
The log writer process (LGWR) is responsible for redo log buffer management—writing the redo log buffer to a redo log file on disk. LGWR writes all redo entries that have been copied into the buffer since the last time it wrote.
The redo log buffer is a circular buffer. When LGWR writes redo entries from the redo log buffer to a redo log file, server processes can then copy new entries over the entries in the redo log buffer that have been written to disk. LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries, even when access to the redo log is heavy.
LGWR writes one contiguous portion of the buffer to disk. LGWR writes:
⦁ A commit record when a user process commits a transaction
⦁ Redo log buffers
⦁ Every three seconds
⦁ When the redo log buffer is one-third full
⦁ When a DBWn process writes modified buffers to disk, if necessary
Note:
Before DBWn can write a modified buffer, all redo records associated with the changes to the buffer must be written to disk (the write-ahead protocol). If DBWn finds that some redo records have not been written, it signals LGWR to write the redo records to disk and waits for LGWR to complete writing the redo log buffer before it can write out the data buffers.
LGWR writes synchronously to the active mirrored group of redo log files. If one of the files in the group is damaged or unavailable, LGWR continues writing to other files in the group and logs an error in the LGWR trace file and in the system alert log. If all files in a group are damaged, or the group is unavailable because it has not been archived, LGWR cannot continue to function.
When a user issues a COMMIT statement, LGWR puts a commit record in the redo log buffer and writes it to disk immediately, along with the transaction's redo entries. The corresponding changes to data blocks are deferred until it is more efficient to write them. This is called a fast commit mechanism. The atomic write of the redo entry containing the transaction's commit record is the single event that determines the transaction has committed. Oracle returns a success code to the committing transaction, although the data buffers have not yet been written to disk.
Note:
Sometimes, if more buffer space is needed, LGWR writes redo log entries before a transaction is committed. These entries become permanent only if the transaction is later committed.
When a user commits a transaction, the transaction is assigned a system change number (SCN), which Oracle records along with the transaction's redo entries in the redo log. SCNs are recorded in the redo log so that recovery operations can be synchronized in Real Application Clusters and distributed databases.
In times of high activity, LGWR can write to the redo log file using group commits. For example, assume that a user commits a transaction. LGWR must write the transaction's redo entries to disk, and as this happens, other users issue COMMIT statements. However, LGWR cannot write to the redo log file to commit these transactions until it has completed its previous write operation. After the first transaction's entries are written to the redo log file, the entire list of redo entries of waiting transactions (not yet committed) can be written to disk in one operation, requiring less I/O than do transaction entries handled individually. Therefore, Oracle minimizes disk I/O and maximizes performance of LGWR. If requests to commit continue at a high rate, then every write (by LGWR) from the redo log buffer can contain multiple commit records.
See Also:
Redo Log Buffer
"Trace Files and the Alert Log"
Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for more information about SCNs and how they are used
Oracle Database Administrator's Guide for more information about SCNs and how they are used
Oracle Database Performance Tuning Guide for information about how to monitor and tune the performance of LGWR
Checkpoint Process (CKPT)
When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process. The CKPT process does not write blocks to disk; DBWn always performs that work.
The statistic DBWR checkpoints displayed by the System_Statistics monitor in Enterprise Manager indicates the number of checkpoint requests completed.
See Also:
Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for information about CKPT with Real Application Clusters
System Monitor Process (SMON)
The system monitor process (SMON) performs recovery, if necessary, at instance startup. SMON is also responsible for cleaning up temporary segments that are no longer in use and for coalescing contiguous free extents within dictionary managed tablespaces. If any terminated transactions were skipped during instance recovery because of file-read or offline errors, SMON recovers them when the tablespace or file is brought back online. SMON checks regularly to see whether it is needed. Other processes can call SMON if they detect a need for it.
With Real Application Clusters, the SMON process of one instance can perform instance recovery for a failed CPU or instance.
See Also:
Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for more information about SMON
Process Monitor Process (PMON)
The process monitor (PMON) performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes.
PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally). PMON also registers information about the instance and dispatcher processes with the network listener.
Like SMON, PMON checks regularly to see whether it is needed and can be called if another process detects the need for it.
Recoverer Process (RECO)
The recoverer process (RECO) is a background process used with the distributed database configuration that automatically resolves failures involving distributed transactions. The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction. When the RECO process reestablishes a connection between involved database servers, it automatically resolves all in-doubt transactions, removing from each database's pending transaction table any rows that correspond to the resolved in-doubt transactions.
If the RECO process fails to connect with a remote server, RECO automatically tries to connect again after a timed interval. However, RECO waits an increasing amount of time (growing exponentially) before it attempts another connection. The RECO process is present only if the instance permits distributed transactions. The number of concurrent distributed transactions is not limited.
See Also:
Oracle Database Administrator's Guide for more information about distributed transaction recovery
Job Queue Processes
Job queue processes are used for batch processing. They run user jobs. They can be viewed as a scheduler service that can be used to schedule jobs as PL/SQL statements or procedures on an Oracle instance. Given a start date and an interval, the job queue processes try to run the job at the next occurrence of the interval.
Job queue processes are managed dynamically. This allows job queue clients to use more job queue processes when required. The resources used by the new processes are released when they are idle.
Dynamic job queue processes can run a large number of jobs concurrently at a given interval. The job queue processes run user jobs as they are assigned by the CJQ process. Here's what happens:
⦁ The coordinator process, named CJQ0, periodically selects jobs that need to be run from the system JOB$ table. New jobs selected are ordered by time.
⦁ The CJQ0 process dynamically spawns job queue slave processes (J000…J999) to run the jobs.
⦁ The job queue process runs one of the jobs that was selected by the CJQ process for execution. The processes run one job at a time.
⦁ After the process finishes execution of a single job, it polls for more jobs. If no jobs are scheduled for execution, then it enters a sleep state, from which it wakes up at periodic intervals and polls for more jobs. If the process does not find any new jobs, then it aborts after a preset interval.
The initialization parameter JOB_QUEUE_PROCESSES represents the maximum number of job queue processes that can concurrently run on an instance. However, clients should not assume that all job queue processes are available for job execution.
Note:
The coordinator process is not started if the initialization parameter JOB_QUEUE_PROCESSES is set to 0.
See Also:
Oracle Database Administrator's Guide for more information about job queues
Archiver Processes (ARCn)
The archiver process (ARCn) copies redo log files to a designated storage device after a log switch has occurred. ARCn processes are present only when the database is in ARCHIVELOG mode, and automatic archiving is enabled.
An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of ARCn processes is insufficient to handle the workload. The alert log keeps a record of when LGWR starts a new ARCn process.
If you anticipate a heavy workload for archiving, such as during bulk loading of data, you can specify multiple archiver processes with the initialization parameter LOG_ARCHIVE_MAX_PROCESSES. The ALTER SYSTEM statement can change the value of this parameter dynamically to increase or decrease the number of ARCn processes. However, you do not need to change this parameter from its default value of 1, because the system determines how many ARCn processes are needed, and LGWR automatically starts up more ARCn processes when the database workload requires more.
Queue Monitor Processes (QMNn)
The queue monitor process is an optional background process for Oracle Streams Advanced Queuing, which monitors the message queues. You can configure up to 10 queue monitor processes. These processes, like the job queue processes, are different from other Oracle background processes in that process failure does not cause the instance to fail.
Cost-Based Optimization
Query optimization is the overall process of choosing the most efficient means of executing a SQL statement. SQL is a nonprocedural language, so the optimizer is free to merge, reorganize, and process in any order.
High Redundancy - In this configuration, for each primary extent, there are two mirrored extents. For Oracle Database Appliance this means, during normal operations there would be three extents (one primary and two secondary) containing the same data, thus providing “high” level of protection. Since ASM distributes the partnering extents in a way that prevents all extents to be unable due to a component failure in the IO path, this configuration can sustain at least two simultaneous disk failures on Oracle Database Appliance (which should be rare but is possible).
Normal Redundancy - In this configuration, for each primary extent, there is one mirrored (secondary) extent. This configuration protects against at least one disk failure. Note that in the event a disk fails in this configuration, although there is typically no outage or data loss, the system operates in a vulnerable state, should a second disk fail while the old failed disk replacement has not completed. Many Oracle Database Appliance customers thus prefer the High Redundancy configuration to mitigate the lack of additional protection during this time.
External Redundancy - In this configuration there are only primary extents and no mirrored extents. This option is typically used in traditional non-appliance environments when the storage sub-system may have existing redundancy such as hardware mirroring or other types of third-party mirroring in place. Oracle Database Appliance does not support External Redundancy.
*ASM redundancy is different from traditional disk mirroring in that ASM mirroring is a logical-physical approach than a pure physical approach. ASM does not mirror entire disks. It mirrors logical storage entities called ‘extents’ that are allocated on physical disks. Thus, all “mirrored” extents of a set of primary extents on a given disk do not need to be on a single mirrored disk but they could be distributed across multiple disks. This approach to mirroring provides significant benefits and flexibility. ASM uses intelligent, Oracle Database Appliance architecture aware, extent placement algorithms to maximize system availability in the event of disk failure(s).
RAC – Cache Fusion
Posted on March 22, 2011by Gopi
In this post let us know something about the main concept of RAC – Cache Fusion.
Now before going into Cache Fusion – first let’s first memorize once again how actually a normal database instance behaves when there is a request for data block.
Let us suppose a user process is requesting a data block, and since this process cannot directly read from the disk, first the requested block must be read(Physical Read) into Buffer Cache of SGA. Once it is read into buffer cache it will remains in the Buffer Cache for further requests. Whenever there is a request for the same data block, since it is already in the buffer, it can be directly read (Buffer Read) from the buffer, thus avoiding another Physical Read. If a data block is found in buffer cache it is called a ‘Cache Hit’ and if it is not found, then it is called a ‘Cache Miss’.
In order to maintain data integrity, when there are concurrent requests for the same data block, Oracle uses Locking Mechanism and multi-version consistency control. A data block can reside in various buffers with different versions, for example a dirtied block, where the previous version of the data block will be maintained in UNDO and the copy of the current version in REDO. Whenever a user request for the block that was already in buffer and dirtied, the UNDO segment provides the required information to construct the read-consistent (CR) image of the data blocks. So, Multi-version data blocks help to achieve read consistency.
The read consistency model guarantees that the data block seen by a statement is consistent with respect to a single point in time and does not change during the statement execution. Readers of data do not wait for other writer’s data or for other readers of the same data. At the same time, writers do not wait for other readers for the same data. Only writers wait for other writers if they attempt to write.
In a single instance the following happens when reading a block
* When a reader reads a recently modified block, it might find an
active transaction in the block.
* The reader will need to read the undo segment header to decide
whether the transaction has been committed or not.
* If the transaction is not committed, the process creates a
consistent read (CR) version of the block in the buffer cache
using the data in the block and the data stored in the undo segment.
* If the undo segment shows the transaction is committed, the process
has to revisit the block and clean out the block and generate the redo
for the changes.
Now let us see how it goes in a RAC environment –
In RAC, there are 2 or more instances accessing same database files that are residing in shared storage area. Each instance has its own SGA and background processes, which means each instance has its own buffer cache (local to each instance). These buffer cache’s act individually at instance level and fuse together at database level to form a single entity (Global Cache) so as to share the data blocks between them. This is what we called ‘Cache Fusion’. Cache Fusion uses a high-speed IPC interconnect to provide cache-to-cache transfers of data blocks between instances in a cluster. This data block shipping eliminates the disk I/O and optimizes read/write concurrency.
Now the question is how the integrity of the data is maintained in a RAC environment, if there are concurrent requests for the same data block – Here too Oracle uses locking and queuing mechanisms to coordinate lock resources, data and inter-instance data requests.
Cache Fusion was implemented by a controlling mechanism called Global Cache Service (GCS), which is responsible for block transfers between instances. The Global Cache Service is implemented by various background processes, such as
Global Cache Service Processes (LMSn)
Global Enqueue Service Daemon (LMD)
[Before going into those processes, let us see how oracle treats the data blocks and how it manages them –
Oracle treats the data blocks as resources. Each of these resources can be held in different modes, which is important mechanism to maintain data integrity. These modes are classified into 3 types depending on whether resource holder intends to modify the data or read the data. They are –
Null (N) mode —Null mode is usually held as a placeholder.
Shared (S) mode — In this mode, data block is not modified by another session, but will allow concurrent shared access.
Exclusive (X) mode — This level grants the holding process exclusive access. Other processes cannot write to the resource. It may have consistent read blocks.
Furthermore, these resources act in one of 2 roles – Local (L) and Global (G).
A resource (data block) is assigned a local role, when a block is first read into the cache and no other instance request for the same data block.
A resource is assigned a Global role, when block is dirtied locally and transmitted to another instance.]
Now let us see what those daemon’s do –
Global Cache Service Daemon (LMSn)
Upon a request from an Instance GCS organizes the block shipping to other instances by retaining block copies in memory. Each such copy is called a past image (PI), which in the event of a node failure, Oracle can reconstruct the current version of a block by using a saved PI. It is also possible to have more than 1 PI of the data block; depending on how many times the block was requested in dirty stage.
New and Changed Features of Oracle Application DBA and Technology Stack (TXK)
Enhanced adop user interface
Category Description Parmameters
Changed UI The UI of the adop utility has been significantly enhanced, to display more selective information on the console. Messages, prompts and other elements have also been extensively refined to increase the ease of use of the various patching commands. Dependent on operation
New adop monitoring and validation features
Category Description Parameters
New Features Progress of an online patching cycle can be followed by running the new Online Patching Monitoring utility (adopmon). This utility can be used to follow the overall progress of a patching cycle, as well as identifying the various individual adop actions being taken. $ adopmon
Before you start a new patching cycle by running the prepare phase, you can optionally check your system's readiness by running adop with the 'validate' option. If you do this while a patching cycle is in progress, validation will take place for the cutover phase. $ adop -validate
Support for new EBS Installation Central Inventory
Category Description Parameters
New Feature Support for an instance-specific EBS Installation Central Inventory has been introduced as an option for the application tier on UNIX platforms. The inventory is identified by <s_base>/oraInventory/oraInst.loc. This feature is useful where multiple Oracle E-Business Suite installations exist on the same host, helping to avoid issues when fs_clone is run simultaneously on different instances. To use the EBS Installation Central Inventory, all application tier Oracle Homes registered in the global inventory for the instance must be migrated to the new inventory. Not applicable
Oracle WebLogic Server performance improvements
Category Desciption Parameters
New Options A new -DserverType=wlx start argument for managed servers reduces their memory footprint, by preventing startup of the Enterprise JavaBeans (EJB), Java EE Connector Architecture (JCA), and Java Message Service (JMS) services. -DserverType=wlx
To reduce oacore startup time, the Portlet Producer libraries are no longer deployed to the EBS domain. A new context variable,s_deploy_portlet, has been introduced to cater for cases where portlet-related configuration is required, such as in instances needing Webcenter integration. s_deploy_portlet
New Mode The default value of s_forms-c4wsstatus is now set to 'Disabled'.Thus, the formsc4-ws servers are no longer started during a 'start all' operation. s_forms-c4wsstatus
New 'dualfs' option in standard cloning
Category Description Parameters
New Option A new 'dualfs' option is available when performing a standard clone, as well as while adding a new node. With the 'dualfs' option, both the run and patch file systems are cloned and configured in a single operation. dualfs
Unlike in the previous releases, a data file migration or renaming in Oracle database 12c R1 no longer requires a number of steps i.e. putting the tablespace in READ ONLY mode, followed by data file offline action.
In 12c R1, a data file can be renamed or moved online simply using the ALTER DATABASE MOVE DATAFILE SQL statement. While the data file is being transferred, the end user can perform queries, DML and DDL tasks. Additionally, data files can be migrated between storages e.g. from non-ASM to ASM and vice versa.
Rename a data file:
SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users01.dbf' TO '/u00/data/users_01.dbf';
Migrate a data file from non-ASM to ASM:
SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '+DG_DATA';
Migrate a data file from one ASM disk group to another:
SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_02';
Overwrite the data file with the same name, if it exists at the new location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE;
Copy the file to a new location whilst retaining the old copy in the old location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;
You can monitor the progress while a data file being moved by querying the v$session_longops dynamic view. Additionally, you can also refer the alert.log of the database where Oracle writes the details about action being taken place.
2. Online migration of table partition or sub-partition
Migration of a table partition or sub-partition to a different tablespace no longer requires a complex procedure in Oracle 12c R1.
In a similar way to how a heap (non-partition) table online migration was achieved in the previous releases, a table partition or sub-partition can be moved to a different tablespace online or offline.
When an ONLINE clause is specified, all DML operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. In contrast, no DML operations are allowed if the partition|sub-partition is moved offline.
Here are some working examples:
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name;
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name UPDATE INDEXES ONLINE;
The first example is used to move a table partition|sub-partition to a new tablespace offline. The second example moves a table partition/sub-partitioning online maintaining any local/global indexes on the table. Additionally, no DML operation will get interrupted when ONLINE clause is mentioned.
Important notes:
⦁ The UPDATE INDEXES clause will avoid any local/global indexes going unusable on the table.
⦁ Table online migration restriction applies here too.
⦁ There will be locking mechanism involved to complete the procedure, also it might leads to performance degradation and can generate huge redo, depending upon the size of the partition, sub-partition.
3. Invisible columns
In Oracle 11g R1, Oracle introduced a couple of good enhancements in the form of invisible indexes and virtual columns. Taking the legacy forward, invisible column concepts has been introduced in Oracle 12c R1. I still remember, in the previous releases, to hide important data –columns from being displayed in the generic queries– we used to create a view hiding the required information or apply some sort of security conditions.
In 12c R1, you can now have an invisible column in a table. When a column is defined as invisible, the column won’t appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. It is pretty easy to add or modify a column to be invisible and vice versa:
SQL> CREATE TABLE emp (eno number(6), ename name varchar2(40), sal number(9) INVISIBLE);
SQL> ALTER TABLE emp MODIFY (sal visible);
You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns. A virtual column or partition column can be defined as invisible too. However, temporary tables, external tables and cluster tables won’t support invisible columns.
4. Multiple indexes on the same column
Pre Oracle 12c, you can’t create multiple indexes either on the same column or set of columns in any form. For example, if you have an index on column {a} or columns {a,b}, you can’t create another index on the same column or set of columns in the same order. In 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different. However, only one type of index is usable/visible at a given time. In order to test the invisible indexes, you need to set the optimizer_use_use_invisible_indexes=true.
Here’s an the example:
SQL> CREATE INDEX emp_ind1 ON EMP(ENO,ENAME); SQL> CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME) INVISIBLE;
5. DDL logging
There was no direction option available to log the DDL action in the previous releases.
In 12cR1, you can now log the DDL action into xml and log files. This will be very useful to know when the drop or create command was executed and by who. The ENABLE_DDL_LOGGING initiation parameter must be configured in order to turn on this feature.
The parameter can be set at the database or session levels. When this parameter is enabled, all DDL commands are logged in an xml and a log file under the $ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. An xml file contains information, such as DDL command, IP address, timestamp etc. This helps to identify when a user or table dropped or when a DDL statement is triggered.
To enable DDL logging
SQL> ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;
The following DDL statements are likely to be recorded in the xml/log file:
⦁ CREATE|ALTER|DROP|TRUNCATE TABLE
⦁ DROP USER
⦁ CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE
Multiple Managed Servers can be grouped into clusters, which enable you to balance loads and provide failover protection for critical applications, while using a single Administration Server simplifies the management of the Managed Server instances.
System administration in WebLogic Server is based on the J2EE Management model, in which each instance of a Web application server resource type is represented by a J2EE Managed Object (JMO). In WebLogic Server, each JMO is a wrapper for a corresponding MBean. You accomplish many WebLogic Server administration tasks by accessing MBeans, either directly using JMX, or through a JMX client like the WebLogic Administration Console or WebLogic Scripting Tool (WLST). For more information, see Monitoring and Managing with the J2EE Management APIs and WebLogic Server MBean Reference.
Run Node Manager on Each Machine that Hosts Managed Servers
To take advantage of Node Manager capabilities, you must run a Node Manager process on each machine that hosts Managed Servers. You can manage multiple Managed Servers on a single machine with one Node Manager process—in Figure 4-1, the two Managed Servers on Machine C can be controlled by a single Node Manager process.
You cannot use Node Manager to start or stop an Administration Server. In a production environment, there is no need to run Node Manager on a machine that runs an Administration Server, unless that machine also runs Managed Servers. In a development environment, you may wish to run Node Manager on a machine that hosts an Administration Server and one or more Managed Servers, because doing so allows you to start the Managed Servers using the Administration Console.
APPLTMP Identifies the directory for Oracle E-Business Suite temporary files. The default is $INST_TOP/appltmp on UNIX.
APPLPTMP Identifies the directory for temporary PL/SQL output files. The possible directory options must be listed in the init.ora parameter utl_file_dir.
Perm -permanent size,metadata of java classes
Heap -programs uses
Eden ->new object created
Gc-eden full
Young generation minor gc
Old generation -major gc
Tune time spent in gc 5% of jvm
Heap size determines how often gc should run
Apply interoperability patches
Install oracle home
Runinstaller(install database software only)
Run root.sh
Install examples
Create nls/data/9idata directory(On the database server node, as the owner of the Oracle RDBMS file system and database instance, run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.)
Apply database patches
Run utlu112i.sql on old db from new oracle home(Checks tablespace,parameters to enable,Database components(OLAP,RAC,OLAP catalog,EM repository,timezone,req to gather stats)
Perform recommendations
Oratab entries
Run DBUA
Gather stats
Workflow sync
Errors
ERROR in setting Environment variable ADJREOPTS
Fix: Copied the jre from the backed up appsutil and then ran autoconfig
⦁ Main upgrade using Main Upgrade Driver for R12.2.0 (merged with pre-installed Oracle E-Business Suite Consolidated Upgrade Patch for R12.2.0)
⦁ Online Patching Enablement
⦁ Applying the latest R12.AD.C.Delta.n and R12.TXK.C.Delta.n
⦁ Applying 12.2.n Release Update Pack (RUP)
ONLINE Patching Enablement
ONLINE Patching Enablement
Initialize the Run File System environment:
source <RUN APPL_TOP>/<Instance SID>_<hostname>.env
Create the online patching log file location and set it as the current directory:
mkdir $LOG_HOME/appl/op
Apply latest patch as per note 'Patching Readiness Report in Oracle E-Business Suite Release 12.2
(Doc ID 1531121.1)' using adpatch options=hotpatch - patch 18824534
cd $LOG_HOME/appl/op
sqlplus system @$AD_TOP/sql/ADZDPAUT.sql
Review report
sqlplus system @$AD_TOP/sql/ADZDPMAN.sql
Review report
sqlplus system @$AD_TOP/sql/ADZDPSUM.sql
Review report
mv adzdpsum.txt adzdpsum_pre_dbprep.txt
mv adzdpman.txt adzdpman_pre_dbprep.txt
mv adzdpaut.txt adzdpaut_pre_dbprep.txt
Run sqlplus apps @$AD_TOP/sql/ADZDDBCC.sql
Review report
mv adzddbcc.lst adzddbcc.lst_pre_dbprep.txt
Run 'sqlplus apps/apps @$AD_TOP/sql/ADZDSHOWAD' and ensure there are no AD_ZD* invalid objects
Review report
Check free tablespace size : perl $AD_TOP/bin/adzdreport.pl apps
Add space to SYSTEM and APPS_TS_SEED tabalespace. Ensure system has 25G free and APPS_TS_SEED has 5Gb free space
Change the max_dump file size parameter to 'unlimited'
Run report: sqlplus apps @$AD_TOP/sql/ADZDEXRPT.sql
mv adzdexrpt.txt adzdexrpt.txt_pre_dbprep.txt
Download and apply the Online Patching Enablement patch: 13543062:R12.AD.C.
Use Autopatch in hotpatch mode to apply the patch. The patch has to be applied on RUN file system.
sqlplus apps @$AD_TOP/sql/ADZDSHOWDDLS.sql
select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;
Connect to sqlplus as 'apps' and run the following:
exec sys.utl_recomp.recomp_parallel
cd $LOG_HOME/appl/op
sqlplus system @$AD_TOP/sql/ADZDPAUT.sql
Review report
sqlplus system @$AD_TOP/sql/ADZDPMAN.sql
Review report
sqlplus system @$AD_TOP/sql/ADZDPSUM.sql
Review report
mv adzdpsum.txt adzdpsum_post_dbprep.txt
mv adzdpman.txt adzdpman_post_dbprep.txt
mv adzdpaut.txt adzdpaut_post_dbprep.txt
Run sqlplus apps @$AD_TOP/sql/ADZDDBCC.sql
Review report
mv adzddbcc.lst adzddbcc.lst_post_dbprep.txt
Run report: sqlplus apps @$AD_TOP/sql/ADZDEXRPT.sql
mv adzdexrpt.txt adzdexrpt.txt_post_dbprep.txt
Take Snapbackup
cd $DOMAIN_HOME/security
$ java weblogic.security.utils.AdminAccount <username> <password> .
Standby_log_management
ADOP Phases
https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531065.htm
Whenever a particular thread keeps running more than the defined threshold, it is classified under stuck threads in weblogic.
In order to find the root cause of the issue, we need to perform the below:
⦁ Verify what operation it does.
⦁ If it is database activity, check which query is running long and tune the query if required
⦁ If it is logon activity ,then check how the authentication is happening and where it actually stuck.
⦁ If it is some other jms or deployment issue, get a thread dump and analyse it using various tools to figure out the actual cause.
Hope this helps!!
https://beginner-sql-tutorial.com/sql-query-tuning.htm
https://developer.rackspace.com/blog/change-the-oracle-apps-and-weblogic-password/
EBS 12.2 login flow
EBS Architecture
When a HTTP request is made for EBS, the request is received by the Oracle HTTP Server (OHS).
When the configuration of OHS is for a resource that needs to be processed by Java, such as logging into EBS, the OHS configuration will redirect the request to the Web Logic Server (WLS) Java process (OACore in this case).
WLS determines the J2EE application that should deal with the request, which is called "oacore".
This J2EE application needs to be deployed and available for processing requests in order for the request to succeed. The J2EE application needs to access a database and does this via a datasource which is configured within WLS.
Login HTTP headers
When the EBS login works OK, the browser will be redirected to various different URLs in order for the login page to be displayed. The page flow below shows the URLs that will be called to display the login page:
/OA_HTML/AppsLogin
EBS Login URL
/OA_HTML/AppsLocalLogin.jsp
Redirects to local login page
/OA_HTML/RF.jsp?function_id=1032925&resp_id=-1&resp_appl_id=-1&security_group_id=0&lang_code=US&oas=3TQG_dtTW1oYy7P5_6r9ag..¶ms=5LEnOA6Dde-bxji7iwlQUg
Renders the login page
The URLs after the user enters username and password, then clicks the "login" button are shown below:
/OA_HTML/OA.jsp?page=/oracle/apps/fnd/sso/login/webui/MainLoginPG&_ri=0&_ti=640290175&language_code=US&requestUrl=&oapc=2&oas=4hoZpUbqVSrv9IE0iJdY1g..
/OA_HTML/OA.jsp?OAFunc=OANEWHOMEPAGE
/OA_HTML/RF.jsp?function_id=MAINMENUREST&security_group_id=0
Renders user home page
Once the users home page is displayed, the logout flow also redirects to several different URL before returning to the login page:
/OA_HTML/OALogout.jsp?menu=Y
Logout icon has been clicked
/OA_HTML/AppsLogout
/OA_HTML/AppsLocalLogin.jsp?langCode=US&_logoutRedirect=y
Redirects to the login page
/OA_HTML/RF.jsp?function_id=1032925&resp_id=-1&resp_appl_id=-1&security_group_id=0&lang_code=US&oas=r6JPtR7-a4n5U2H3--ytEg..¶ms=1JU-PCsoyAO7NMAeJQ.9N6auZoBnO8UYYXjUgSPLHdpzU3015KGHA668whNgEIQ4
Renders login page again
https://techgoeasy.com/login-flow-basic-troubleshooting-r12-2/(Sourced from this blog)
AUG
3
When we login to applications,initially oracle applications connect to public schema, APPLSYSPUB. This schema has sufficient privileges to perform the authentication of an Applications User (FND user), which includes running PL/SQL packages to verify the username/password combination and the privilege to record the success or failure of a login attempt.
The public ORACLE username and password that grants access to the Oracle E-Business Suite initial sign-on form. The default is APPLSYSPUB/PUB.
Once we change the APPLSYSPUB password must propagate the change to application tier configuration files. If the instance is Autoconfig enabled, must edit the CONTEXT file on each tier prior to running Autoconfig.
In the CONTEXT file, locate the autoconfig variable “s_gwyuid_pass” and set it to the new password, then run AutoConfig in each applications nodes.
When Autoconfig is not being used:
If you are not using Autoconfig you must manually edit the following configuration files :
1) FND_TOP/resource/appsweb.cfg
2) OA_HTML/bin/appsweb.cfg
3) FND_TOP/secure/HOSTNAME_DBNAME.dbc
To change password of APPLSYSPUB with FNDCPASS:
$FNDCPASS APPS/[apps_pass] 0 Y SYSTEM/[system_pass] ORACLE APPLSYSPUB [new_passs].
0 & Y are flags for FNDCPASS
0 is request id (request ID 0 is assigned to request ID's which are not submitted via Submit Concurrent Request Form)
'Y' indicates that this method is directly invoked from the command-line and not from the Submit Request Form.
All application tier processes (Apaches) must be restarted following the password change.
Role of GUEST user/schema in Oracle Applications:
GUEST is a dummy schema.
By default it has ORACLE as password.
GUEST/ORACLE password is present in DBC file at $FND_TOP/secure directory as well as at $FND_TOP/secure/SID_hostname directory.
If a user logs in without any role mappings, the user will get the Guest role, which has a default permission of "R".
GUEST user is used by JDBC Drivers and Oracle Self Service Web Applications like istore, irecruitment, iprocurement, ipayables, ireceivables etc to make initial Connection.
Role of APPLSYS & apps user/schema in Oracle Applications:
APPLSYS user is same as other oracle users like AP, AR, GL etc which hold their set of tables, views etc. In the same manner APPLSYS Account holds its set of tables like FND_USER and FND_APPLICATION, AD_APPLIED_PATCHES etc.
Applsys schema has applications technology layer products like FND and AD etc.
Apps is a universal schema, it has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc).
APPS is central Schema which holds synonyms for all other Users Database Objects.
Reason why these contains same password.
Both apps & applsys need to have same password because when you sign on to apps, initially it connects to a public schema called APPLSYSPUB. This validates AOL name and password that we enter (operations/welcome). Once this is verified we select responsibility, this is validated by APPLSYS schema and then it connects to apps schema.
During signon process it uses both applsys and apps, hence this expects both the password to be identical. If the password for applsys & apps are not identical (Different) Try changing apps password to something else and try to login, the validation at the last stage would fail. This would result in failure of application login.
GUEST/ORACLE - is EBS user with no or max limited privileges to execute authorization function.That is why Guest user cannot be end dated.
Now lets jump into the login flow:
1.Webserver receives the user request for the particular port number. Web server Binds the request with the port number.
2.Based on the cookie webserver serves the request by sending login page
3.The application user name and password is entered by the user(operations/welcome)
4.DB connection is established using APPLSYSPUB user. This user “APPLSYSPUB” is having access to fnd_user view and a small set of other public tables needed to establish the initial connection.
5.Once DB connection is established FND_USER table is used to get ENCRYPTED_FOUNDATION_PASSWORD.
6.Using GUEST/ORACLE password combination and ENCRYPTED_FOUNDATION_PASSWORD string obtained from 2 above, we get Apps schema password.
7.Internally it tries to connect to Apps schema with the password retrieved in step 3 above. If the connection fails, then
a)The GUEST username password is incorrect
b)The FNDNAM (APPS) environment variable is set incorrectly.
c)Some other problem prevented a connection
8.Using Apps password obtained in Step 3 and ENCRYPTED_USER_PASSWORD string from FND_USER table, password for Application user (e.g. SYSADMIN) is obtained.
9. The Application user password obtained in Step 5 is compared to the application user password entered by user in login screen. If both passwords match then user is allowed to get into self-service.
10. A list of responsibilities assigned to that user is shown. The user picks one of the responsibilities listed.
11. The responsibility and application name is validated by connecting to Applsys schema(fnd_Responsibility, fnd_application…….)
Note: Here we should remember the difference between applsys and apps schema. Applsys schema contains ad,fnd objects whereas apps schema is a global schema contains all application objects.
12. Once the application is validated , Connecting to apps schema to get access application tables. This is the reason why we should have apps and applsys passwords should be same
13. Once a particular form is selected the .dbc file is accessed to get the information about the GUEST username/pwd
Internal Monitor Processes
The sole job of an Internal Monitor Process is to monitor the Internal Concurrent Manager and to restart that manager should it fail. The first Internal Monitor Process to detect that the Internal Concurrent Manager has failed restarts that manager on its own node.
Only one Internal Monitor Process can be active on a single node. You decide which nodes have an Internal Monitor Process when you configure your system. You can also assign each Internal Monitor Process a primary and a secondary node to ensure fail over protection.
Internal Monitor Processes, like concurrent managers, can have assigned work shifts, and are activated and deactivated by the Internal Concurrent Manager.
FNDFS or the Report Review Agent (RRA) is the default text viewer within Oracle Applications, which allows users to view report output and log files. Report Review Agent is also referred to by the executable FNDFS. The default viewer must be configured correctly before external editors or browsers are used for viewing requests.
FNDSM is the Service manager. FNDSM is executable & core component in GSM ( Generic Service Management Framework ). You start FNDSM services via APPS listener on all Nodes in Application Tier.
There are certain scenario where we can't see the output of Concurrent Requests. It just gives an error.
Then check the fndwrr.exe size in production and compare it with Test Instance.
fndwrr.exe is present in $FND_TOP/bin
If there is any difference then relink the FNDFS executable.It might not be in sync with the binaries.
Command for relinking
adrelink.sh force=y "fnd FNDFS"
So if you have to change the port used by EBS instance, then you have to log into the Weblogic admin console and change port there and then sync context xml file using adSyncContext.pl. This adSyncContext.pl utility will get the current port values from Weblogic console and update the xml with new port values. Once the context xml file syncs, we have to run Autoconfig to sync other config files and database profile values to pickup new webport
Similarly, if you want to change the JVM augments or class path, you have run another utility called adProvisionEBS.pl to make those changes from command line or login to the Weblogic admin console to do those changes. Interestingly, few of the changes done in Weblogic admin console or fusion middleware control are automatically synchronized with context xml file by the adRegisterWLSListeners.pl script that runs in the background all the time. But Apache config file changes were not picked by this script, so Apache changes had to be manually synchronized
The ICM starts up a Service Manager on each node that is enabled for concurrent processing, by instructing the node's Applications Listener (which is dedicated to Concurrent Processing) to spawn a process running the Service Manager executable (FNDSM).
The Applications Listener must be configured to source the Oracle E-Business Suite environment file before FNDSM is spawned.
Following startup, the Service Manager acts as an agent of the ICM to start and stop concurrent managers on that node, according to their defined work shifts.
Apps Listener usually running on All Oracle Applications 11i Nodes with listener alias as APPS_$SID is mainly used for listening requests for services like FNDFS and FNDSM.
If you're looking for Oracle DBA Interview Questions for Experienced or Freshers, you are at right place. There are a lot of opportunities from many reputed companies in the world. According to research, Oracle DBA has a market share of about 0.7%. So, You still have an opportunity to move ahead in your career in Oracle DBA Development. Mindmajix offers Advanced Oracle DBA Interview Questions 2019 that helps you in cracking your interview & acquire dream career as Oracle DBA Developer.
Oracle DBA Vs Oracle Developer
Feature Oracle DBA Oracle Developer
Key Roles Managing Databases Development(Coding)
Type of Tasks Streamlined Wide Range
Work Environment Database maintenance Database development
Area of work Backend database management Front-end development
Few other tasks Backup, recovery, server connectivity, etc Coding, designing UI, etc
Q1. List four possible ways (direct or indirect) to execute an SQL query against an Oracle Database?
⦁ Using the SQL*Plus command line tool. With this tool, you can directly execute SQL commands.
⦁ Using a GUI (Graphical User Interface) tool like SQL Developer. You can directly execute SQL commands with such tools.
⦁ Using Oracle Enterprise Manager. This is an indirect way of executing an SQL query. When you perform certain operations with Oracle Enterprise Manager, they are converted to SQL queries implicitly and these SQL queries are executed against the database.
⦁ Writing your own program. This is not a conventional way of executing your queries but actually it is widely used. Any web or windows program that uses Oracle database at backend, executes SQL queries. These programs are written using a programming language like .NET or JAVA and they use a driver to connect to database.
Q2. What is SQL*Plus? How can one acquire it and what kind of operations can be performed with it?
⦁ SQL*Plus is a command line tool developed by Oracle Corporation.
⦁ It is freely distributed. It is shipped with Oracle client installations or Oracle database installations as a default. So, if Oracle client or Oracle database software is installed on a computer, you can find it under “$ORACLE_HOME/bin/” directory. The name of the executable is “sqlplus” on Linux systems and “sqlplus.exe” on Microsoft Window Systems.
⦁ You can connect to an Oracle database with it. Once connected, you can execute Oracle commands or SQL queries against the connected database. SQL*Plus has also its own commands for formatting the output so that you can display the results in a meat way.
Q3. A user is logged on to a Linux server as root where Oracle database is running. The Oracle is installed at “/uo 1/app/oracle/product/11.2.0.4/dbhome” and the name of the SID is “ORCL”. The user wants to connect to the database locally using operating system authentication with SYSDBA privileges. Show the command that the user has to execute.
⦁ First he needs to switch to “oracle” user:# su – oracle
⦁ Later he needs to set required environment variables:
1
2
3 $ export ORACLE_SID=ORCL
$ export
ORACLE_HOME=/uo1/app/oracle/product/11.2.0.4/dbhome
⦁ Finally he needs to execute the following command to connect to database:
⦁
1 $/uol/app/oracle/product/11.2.0.4/dbhome/bin/sqlplus/ as sysdba
Q4. In our organization, we’re using an Oracle database whose version is 11.2.0.4. Explain what each digit shows.
Ans. “11”: This first digit shows the major database version. Oracle usually publishes a major release once a 4 year. This digit is usually followed by a character describing the nature of the release. For example: 9i (internet), 10g (grid), 11g (grid), 12c (cloud).
“2”: This second digit shows the maintenance release number of the software. Oracle publishes the major release as maintenance release 1 and then usually publishes a second maintenance release during the lifetime of the software. New features are added to database Software with maintenance releases.
“o”: This third digit is Fusion Middleware Number. This will be o for database software.
“4”: This fourth digit is called Component-Specific Release Number and it shows the path set update that was applied to the software. Patch set updates are published 4 times a yearby Oracle and as you apply them to your database software, this fourth digit advances.
Q5. You’re at a client’s office and you are expected to solve a problem in their database. The client is not sure about their database version and you want to find out the version of their existing database. Describe three different methods you can use to find the version of database software.
⦁ You can find the version by connecting to the database with SQL*Plus. SQL*Plus will print the name and the version of the database software once you’re connected to the database. A sample output will look like below:
“Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options”
⦁ You can find the version by querying the “vsversion” view. You can execute the SQL query below to find the version of the database:
1
2
3
4
5
6
7
8 SQL: SELECT * FROM v$version;
A sample output would look like below:
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – Production
PL/SQL Release 11.2.0.4.0 – Production
CORE 11.2.o.4.o Production
TNS for Linux: Version 11.2.0.4.0 – Production
NLSRTLVersion 11.2.0.4.0 – Production
⦁ You can find the version from Enterprise Manager. If you logon to Oracle Enterprise Manager, the version of the database software will be listed at the home page under “General” web part.
Q6. Your client said that he forgot the password for “SYSTEM” user of his database and he no longer could connect. How would you recover this admin password?
Ans.
⦁ If there are other users who have “DBA” privileges, you can connect with those users and change the password for “SYSTEM” user. The users who have DBA privileges have the privileges to change any user’s password. This option is the easiest method but this may not be the case in all scenarios.
⦁ If there are no other users with “DBA” privileges then the only way to connect to the database isto connect using operating system privileges. The oracle software runs under a specific user at operating system. This user is usually named “oracle”. Also, there needs to be a user group that “oracle” user belongs. This user group is usually named “dba”. The operating system users who belong to “dba” group can connect to database with “SYSDBA” privileges. So, you need to ask the system administrator to logon to server as “oracle” user or any user who belongs to this “dba” group. Once logged on to operating system, you can connect to database locally using operating system authentication with SYSDBA privileges. After connecting to the database, you can change the reset the password for this system user.
Q. What is a password file and why is it needed?
Ans. Passwords for database users are stored in the data dictionary of the database. When a user wants to log into the database, the username and password provided by the user is checked against the values stored in the database. If the username and password match, the user is granted access to database. The data dictionary is part of the database and it will be accessible as long as the database is open. The passwords for administrators are stored in the dictionary as well.
When the database is closed, the data dictionary will be inaccessible. There needs to be a mechanism for administrators to logon to database even when it is closed, because it is one of the administrator’s tasks to start up a down database. A password file is a separate operating system file that is stored on disk outside of the database. The username and password for the users who have SYSDBA or SYSOPER privileges are stored in it. Administrators who have those privileges are authenticated using this password file even when the database is down.
Q6. You want to find out how many users are defined in the password file and what privileges those user have. How would you accomplish this?
Ans. You need to query the “v$pwfile_users” view to get information about the existing users in the password file. Execute the SQL query below:
Sql>SELECT * FROM v$pwfile_users;
The query above will return four columns for each user in the password file. The column names are USERNAME, SYSDBA, SYSOPER and SYSASM.
⦁ The USERNAME column shows the username of the user in the password file.
⦁ The SYSDBA column shows whether the user has SYSDBA privileges or not.
⦁ The SYSOPER column shows whether the user has SYSOPER privileges or not.
⦁ The SYSASM column shows whether the user has SYSASM privileges or not.
Q7. What would be the main responsibilities of an Oracle DBA in an organization?
Ans. The main duty of an Oracle DBA isto keep the Oracle Databases of the organization up and running. This may involve installing and configuring a database from scratch.
On a running system, the DBA will be the only privileged person who can shut down and start up the database.
The DBA will create new users and manage the privileges of each user.
He will take regular backups to ensure that data is safe. In case of a disaster, he will be responsible of restoring the database from backups. He will have to do monitor the space usage and do capacity planning for the database.
He will be responsible for enforcing security policies. He will have to monitor database activities. He will have to tune the database so that it works at an acceptable speed.
He is expected to follow the latest patches and apply them when applicable.
Q8. How does an Oracle DBA role differ from an Oracle Developer role in an organization? Are there any similarities between these too?
Ans. An Oracle developer is mainly responsible for developing backend applications. They do data modelling according to business rules. They design tables, create indexes and other types of constraints. They are expected to know SQL and PL/SQL. The develop procedures using this languages. However, the Oracle developers are not expected to administer the database software itself.
On the other side, an Oracle DBA’s main duty is to administer the database which involves tasks like doing maintenance to keep the databases up and running, taking backups, enforcing security policies etc. DBAs are not primarily assigned to develop code. DBAs are supposed to have a good knowledge of SQL and PL/SQL like a developer as these are also required for administering the database.
According to the structure of the organization, DBAs might also be assigned development tasks or at least assist the developers where necessary.
Q9. There are 10 identical servers and you want to install Oracle Database on each of them. What would you use to automate the installation process?
Ans. If you are going to do batch installations, it is best to do it with Oracle Universal Installer in silent mode. For single installations.it is best to start installer in “interactive mode”and set installation options at each window. However in batch installations, this will take long. You need to do the installations in “silent” mode with a “response file”. In silent installation, you start the Oracle Universal Installer from a command prompt and specify the location of the “response file”.
The installation files and the response file can be shared among the servers via NFS so that you won’t have to copy the setup files to each server.
Q10. You want to create a response file to speed up the installation of databases. How would you prepare a response file?
Ans. A response file is a plain text file, where options to create a database are stored. It is possible to create it manually from scratch but that would take long and would be erroneous.
Installation media comes with a template response file. It is rather easier to customize it manually. This file also contains notes about the parameters.
However the easiest and most reliable way to create a response file is using Oracle Universal Installer. If you start the installer in “record” mode, every option you choose at each step is automatically recorded in a response file in correct format. After the installer completes in “record” mode, you’ll have a complete response file with all the options set in it.
Q11. When creating a database with SQL script, what would you specify in the script?
Ans. It is also possible to create a database via an SQL script. In this script I would specify:
⦁ Name of the database
⦁ Password of the SYS user
⦁ Password of the SYSTEM user
⦁ At least three online redolog groups. I would also specify at least two members for each redolog group.
⦁ Character set and the national character set of the database.
⦁ Location and size of the SYSTEM and SYSAUXtablespace. These tablespaces will be used for holding system data.
⦁ I would specify a normal tablespace to use as the default tablespace of the database.
⦁ I would specify a temporary tablespace to use as the default temporary tablespace of the database.
⦁ I would specify an undo tablespace.
Q12. What makes up an Oracle Instance?
Ans. An instance is made up of a shared memory region on RAM called System Global Area (SGA) and background processes.
The system global area is a shared memory, which means it can be accessed by multiple processes. This are holds data which is required by the instance to operate.
The background processes are operating system processes and each process has a specific responsibility in the instance.
The System Global Area and background processes are created when the instance is “started”. When the instance is “shut down”, the processes are killed and the shared memory region is “released” back to operating system.
Q13. What constitutes an Oracle Database?
Ans. An Oracle database is resides on disk and this is permanent. It is composed of files that are stored on disk. These files can be categorized into three types:
⦁ DataFiles: These files hold “user” data or “system” data. Any data that belongs to an application is an example of “user” data. The “data dictionary” of the database is an example of “system” data.
⦁ OnlineRedo Log Files: These files hold the “change” records. Any change, which will be made to a data file, is first written to online redo log files.
⦁ ControlFiles: These files are relatively small but they are essential for a database. They hold information about the physical structure of the database like location of data files, online redo log files etc.
Q14. Which tools can you use to start up an Oracle database?
Ans. You can start up a database with three tools.
⦁ SQL*Plus: This is the most widely used option. You first connect to an idle instance with SQL*Plus and then startup the instance with “startup” command.
⦁ Oracle Enterprise Manager: This is another way of starting up a database. You can logon to Oracle Enterprise Manager even if the database is stopped. OEM will detect the status of the down database and will present you “Startup” button. You can startup the database by clicking this button.
⦁ RMAN: This is rather a less used tool for starting up a database but it is possible to startup a database from Recovery Manager command line.
Q15. During startup of a database, at which order does Oracle software search a parameter file?
Ans. A parameter file holds instance parameters which govern how an instance operates. In order to startup an instance, Oracle needs to locate this file.
The search order is as below: /dbs/spfile.ora – This is an server parameter file and this is the first place that oracle will look for. SID- is the service identifier of the instance.
<$ORACLE_HOME-/dbs/spfile.ora -If Oracle cannot find the file in the first location, it will search this file. This is again a server parameter file.
/dbs/init.ora – This is a parameter file and it is plain text. If Oracle cannot find the two file listed above, it will search for this file. This is the last location to search.
Q16. At what stages does an instance pass while starting up?
Ans. You can startup a database with the modes below:
⦁ NOMOUNT: This is the first stage. At this mode the instance is started.
⦁ MOUNT: This is the second stage. At this mode, the instance is started and the database is mounted. However, the database is not open so you cannot still access data. However you can perform several maintenance tasks at this stage.
⦁ OPEN: This is the final stage. The database is open and all the data is accessible. The default open mode is “read/write” which means you can read data or write to it. However, it is also possible to open itin “read only” mode where you can only read data but cannot change it.
Q17. You want to do maintenance on your database but during the maintenance period, you don’t want any user to be able to connect to the database. How would you accomplish this?
Ans. When a database is open, any user with “CREATE SESSION” privilege can make a connection. However it is possible to open the database in “restricted” mode. When a database is open in restricted mode, only users with “RESTRICTED SESSION” privilege can make a connection to the database. By default, only DBAs have “RESTRICTED SESSION” privilege and it should not be granted to regular users.
Opening a database in “restricted” mode is a good way to prevent regular users from accessing the database during maintenance.
Q18. Your database is open. You don’t want to interrupt currently connected users but you want to temporarily disable further logons. What would you do to achieve this and how would you revert the database back to normal state after that?
Subscribe to our youtube channel to get new updates..!
Ans. I would put the database in “restricted mode”. While in restricted mode, only users with “RESTRICTED SESSION” privilege can make a connection. I would run the below command to put database in restricted mode:
Sql> alter system enable restricted session;
After executing this command regular users won’t be able to logon to the database. Once I want to revert the database to normal, I execute this command:
Sql>alter system disable restricted session;
Q19. What are the types of shutdown modes of an Oracle database?
⦁ Normal: In this mode, no new connections are allowed and the database is closed after all the sessions disconnect themselves.
⦁ Immediate: No new connections are allowed and the existing active transactions are rolled back. Changes made by an active transaction are lost in this option.
⦁ Transactional: No new connections are allowed and Oracle waits until all active transactions are completed.
⦁ Abort: This happens immediately however the database is not shutdown cleanly. Database will have to perform instance recovery next time it is started. This option should not be used in regular activities.
Q20. The data files of your database reside on a storage system. You want to take a snapshot of the storage so that you can use it backup purposes. You also want to ensure that no data is written to data files while the snapshot is being taken. Is it possible to accomplish this while the database is open?
Ans. Yes, it is possible to stop all I/O activity while the database is open. Normally, when a database is open, there will be constant I/O to online redolog files or data files. Even if the database is idle, there is no guarantee that database will not write anything to files during snapshot.
However, if you “suspend” the database, Oracle will halt I/O operations to these datafiles until it is reverted back to normal mode. So, you should “suspend” the database, take the snapshot of the disk and then put the database back in normal mode immediately after that.
Q21. What kind of information can be given while creating a sequence?
⦁ Sequence Name: This is the name of the sequence. It should be unique inside the schema.
⦁ Start With: This is the number that the sequence will start from.
⦁ Increment By: This number shows how much the sequence will increment at each move.
⦁ Nocycle: This determines whether the sequence will start from the beginning once it reaches the end.
⦁ Nocache: This determines how much next sequence number willbe cached in SGA. Nocache means no next sequence will be cached.
Q22. You want your database to start automatically, after a reboot of the server. How would you do that?
Ans. In default configuration, Oracle database will not automatically start after the server reboots. You’ll have to start it manually after each reboot. You’ll usually want it to start automatically. There are two methods to accomplish this:
⦁ Using Oracle Restart: “Oracle Restart” is a feature of Oracle High Availability Service (OHAS). You need to install “Grid Infrastructure” to enable “Oracle Restart” feature. Using “Oracle Restart” is the recommended way.
⦁ Using Your Own Script: It is also possible for you to write your own “bash” script to start the database and place that script in the startup of the operating system.
Q23. Which components of your database environment can be protected by an “Oracle Restart” configuration?
⦁ Database Instances and Automatic Storage Management (ASM): Database instances and ASM instances will be restarted if they crash somehow.
⦁ Oracle NET Listener: Oracle NET Listener will be started if it crashes and stops listening for incoming connection.
⦁ ASM Disk Groups: Oracle Restart will mount ASM Disk groups if they are dismounted.
⦁ Database Services: Non-default database services will be started by Oracle Restart feature.
⦁ Oracle Notification Services (ONS): This is another Oracle component that can be protected by Oracle Restart.
Q24. Explain the difference between “shared server” architecture and “dedicated server” architecture?
Ans. When a user connects to a database, he sends SQL queries to the database to execute. These SQL queries are executed by a “server process” and the result is returned back to the user. In “dedicated server” architecture, the instance will create one server process for each connected user.
That process will be “dedicated” to that user and will only serve that client.
However in “shared server” architecture, a single server process will serve multiple clients. In shared server architecture, the total memory consumption will be less. However, certain operations like DBA activities can only be performed in dedicated server.
Q25. Explain how “shared server” architecture works.
Ans. In shared server architecture, the clients connect to a “dispatcher” process. This dispatcher is responsible for delivering the SQL requests to the “request queue”.
The shared server process monitors the request queue. When they find an incoming request, they execute this SQL query and place the results in the response queue. The request queue and the response queue reside in the system global area.
The dispatcher processes also monitor response queue. When it receives a result, they deliver the result to the relevant client.
In this architecture, there will be multiple shared server processes and dispatcher processes.
Q26. What are the instance parameters that are used for configuring shared server architecture?
⦁ DISPATCHERS: Astring value which is used to configure dispatchers.
⦁ SHARED_SERVERS: Minimum number of shared server processes that will be present in the server. Also, this number of shared servers is created during startup.
⦁ MAX SHARED_SERVERS: This parameter determines the maximum number of shared server processes that can run at the same time.
⦁ SHARED_SERVER SESSIONS: This parameter specifies the maximum number of sessions that can exist at the same time using shared server connection.
⦁ CIRCUITS: This parameter determines the maximum number of virtual circuits that can exist in the system.
Q27. Explain how the “Database Writer” process works.
Ans. There can be multiple database background processes. They are named as “DBWn” at operating system. This process is responsible for writing “dirty” buffers to disk. When a server process wants to update a data block, it reads the block from disk to buffer cache if the block is not already in the cache and then updates the copy in the cache. The modified database block in the buffer cache is called a “dirty” block.
Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!
Oracle Data Structures & SQL
Q28. Why is index used?
Ans. Index is used to increase the performance of retrieval. We can make use of one or more rows in order to make the index. Index can increase the performance of retrieval and slows down the performance of insertion.
Q29. Who is responsible to update the indexes?
Ans. Oracle automatically maintains and uses indexes and when any change is made in the table data Oracle automatically distributes it into relevant indexes.
Q30. What is the definition of table in Oracle?
Ans. Table is the first level of physical unit in database. Oracle uses tables of a database to store data into rows and columns. Table is the first level of physical unit in database.
Q31. What do you mean by view and what are its types?
Ans. View is a type of virtual table and there is a query attached to every view in order to identify specific rows and columns of the table. Views are read-only as well as read-write.
Q32. In Oracle terminology, what do you mean by tablespace?
Ans. Tablespace is a Logical Storage Unit used to group related logical structures together. It is the logical structure where all the objects of database will be grouped.
Q33. When does the SYSTEM tablespace get created?
Ans. In Oracle every database has a tablespace called SYSTEM and it is automatically created when database is created. It also contains the data dictionary table for the whole data.
Q34. What is the relationship between tablespace and datafiles?
Oracle DBA Certification Training!
Explore Curriculum
Ans. Each tablespace is divided into one or more data files and one and more tablespace(s) are created for each database.
Checkout how to revoke user privileges role in Oracle DBA
Q35. How do we use materialized view?
Ans. Materialized views are objects that have reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
Q36. In Oracle terminology, what is Synonym?
Ans. A synonym is an identifier that can be used to reference another database object in a SQL statement. The types of database objects for which a synonym may be created are a table, view, sequence, or another synonym.
Q37. Different types of synonyms are?
Ans. Synonym types are private and public.
Q38. What you understand by public synonym?
Ans. A public synonym does not belong to any schema. In other words, when any database user can access it, it is called public synonym.
Q39. What you understand by private synonym?
Ans. A private synonym is one that does belong to a specific schema. In other words, when only owner can access it, it is called private synonym.
Q40. What are the advantages of synonyms?
Ans. Synonym is used to mask the original name and owner of an object and provides public access to an object.
Q41. What is a sequence?
Ans. A sequence generates a serial list of unique numbers for numerical columns of a database tables. We can use sequence on columns for data where we want to insert data in sequential manner.
Q42. What you understand by private database link?
Ans. Private database link is created for a specific user. It is only used when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner’s views or procedures.
Q43. What you understand by public database link?
Ans. Database link is a schema object in one database to access objects in another database. When you create database link with Public clause it is available for access to all the users.
Q44. What do you mean by row chaining?
Ans. Row Chaining occurs when the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG, LONG RAW, LOB, etc. Row chaining in these cases is unavoidable.
Q45. What is the definition of extent?
More Batches
Ans. An extent is a set of contiguous blocks allocated in a database. In the Oracle database program, the first set of contiguous blocks, set up automatically when a segment is created, is called the initial extent. After the initial extent has been filled, the program allocates more extents automatically. These are known as next extents.
The total number of extents that can be allocated in a database is limited by the amount of storage space available, or in some cases, by the program used.
Q46. Explain the advantages of using view?
Ans. The view helps provide security, presentation of data in a different perspective and store complex queries.
Q47. What do you mean by datafile?
Ans. An Oracle datafile is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.
Datafile is used to store tables and indexes allocated to the database. Every database consists of one or more data files.
Q48. Explain the properties of data files?
Ans. Each data file can only be associated with only one database and once it is created it can not change its size.
Q49. What do you mean by redo log?
Ans. The most crucial structure for recovery operations is the redo log, which consists of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.
Q50. Main function(s) of redo log is?
Ans. Redo log’s main function is to store all changes made to the database as they occur.
Q51. What are the contents of control file?
Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes:
⦁ The database name
⦁ Names and locations of associated datafiles and online redo log files
⦁ The timestamp of the database creation
⦁ The current log sequence number
⦁ Checkpoint information
Checkout how manage redo log in Oracle DBA
Q52. What are the advantages of control file?
The control file must be available for writing by the Oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult. You might also need to create control files, if you want to change particular settings in the control files.
Q53. What is definition of SQL?
SQL is a database computer language designed for managing data in relational database management systems (RDBMS), and originally based upon relational algebra. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
Q54. What is the use of SELECT statement?
SELECT statement is used to select the set of specific values from a table in a database depending on the various conditions specified in a SQL query.
Q55. How can you compare a part of the name rather than the entire name?
In order to compare part we use LIKE operator which acts like a regex engine for database.
Q56. What is the keyword to get distinct records from a table?
SELECT DISTINCT allows the user to select the distinct values from a table in a database.
Q57. In order to get sorted records from a table, what is the keyword?
ORDER BY keyword is used for sorting the results. It returns the sorted results to your program.
Q58. In order to get total records from a table, what is the keyword?
To find the total number of records in a table, COUNT keyword is used.
Q59. What is definition of GROUP BY?
GROUP BY keyword is an aggregate function such as SUM, MULTIPLE, etc and without this function sum for each individual group values can not be calculated.
Q60. Explain the difference among “dropping a table”, “truncating a table” and “deleting all records” from a table.
⦁ Dropping a table means to drops the invalid indexes. It deletes table definition and records both,
⦁ Truncating means to delete invalid data values automatically. Internally the database marks the table as empty and the deleted records are not recorded in the transaction log.
⦁ Deleting all records, deletes all records but records all details in Transaction log file.
Q61. Different types of SQL statements are?
There are five types of SQL statements
⦁ Data Definition Language,
⦁ Data Manipulation Language,
⦁ Transactional Control,
⦁ Session Control and
⦁ System Control.
Q61. What is definition of transaction?
Oracle supports transactions as defined by the SQL standard. A transaction is a sequence of SQL statements that Oracle treats as a single unit of work. As soon as you connect to the database, a transaction begins. Once the transaction begins, every SQL DML (Data Manipulation Language) statement you issue subsequently becomes a part of this transaction. A transaction ends when you disconnect from the database, or when you issue a COMMIT or ROLLBACK command.
Change the WebLogic password
The following sections show the steps for updating the WebLogic password for Txk Delta 7 and later and for versions earlier than Txk Delta 7.
TKX DELTA 7 AND LATER
The following steps apply to WebLogic Txk Delta 7 and later:
⦁ Run the following command to source the run filesystem, shut down all application tier services except the Admin Server, and ensure that no application services (except admin server and node manager) are running on the primary application server:
⦁ adstpall.sh apps/apps_password -mode=allnodes -skipNM -skipAdmin
⦁ Run the following command to change the Weblogic administration password and start the services:
⦁ perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword
The system prompts you for the Apps password, the old WebLogic password and the new WebLogic password.
Following is some sample output from this process:
$ perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword
Program: txkUpdateEBSDomain.pl started at Tue Aug 21 04:30:52 2018
AdminServer is restarted after changing WebLogic Admin Password
All MidTier services should be SHUTDOWN before changing WebLogic Admin Password
Confirm if all Mid-Tier services are in SHUTDOWN state. Enter "Yes" to proceed or anything else to exit:
Enter the full path of Applications Context File [DEFAULT - /apps1/OSID/fs1/inst/apps/OSID_pserver/appl/admin/OSID_pserver.xml]:
Enter the WLS Admin Password:
Enter the new WLS Admin Password:
Enter the APPS user password:
Executing: /apps1/OSID/fs1/FMW_Home/webtier/perl/bin/perl /apps1/OSID/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/adProvisionEBS.pl ebs-get-serverstatus -contextfile=/apps1/OSID/fs1/inst/apps/OSID_pserver/appl/admin/OSID_pserver.xml -servername=AdminServer -promptmsg=hide -logfile=/apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_21_04_30_52_2018/EBSProvisioner.log
Online mode:
Backup Location: /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_21_04_30_52_2018
Logfile Location: /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_21_04_30_52_2018/updateAdminPassword.log
Executing: /apps1/OSID/fs1/EBSapps/comn/util/jdk32/jre/bin/java -classpath :/apps1/OSID/fs1/FMW_Home/wlserver_10.3/server/lib/weblogic.jar:/apps1/OSID/fs1/FMW_Home/Oracle_EBS-app1/oui/jlib/srvm.jar:/apps1/OSID/fs1/EBSapps/comn/java/classes:/apps1/OSID/fs1/EBSapps/comn/shared-libs/ebs-3rdparty/WEB-INF/lib/ebs3rdpartyManifest.jar:/apps1/OSID/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar oracle.apps.ad.util.UpdateEBSDomain updateAdminPassword -contextfile /apps1/OSID/fs1/inst/apps/OSID_pserver/appl/admin/OSID_pserver.xml -promptmsg hide -logdir /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_21_04_30_52_2018 -logfile /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_21_04_30_52_2018/updateAdminPassword.log
Domain updated successfully
Restarting AdminServer with new Admin Password.
You are running adadminsrvctl.sh version 120.10.12020000.10
Stopping WLS Admin Server...
Refer /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/admin/log/adadminsrvctl.txt for details
AdminServer logs are located at /apps1/OSID/fs1/FMW_Home/user_projects/domains/EBS_domain_OSID/servers/AdminServer/logs
adadminsrvctl.sh: exiting with status 0
adadminsrvctl.sh: check the logfile /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/admin/log/adadminsrvctl.txt for more information ...
You are running adnodemgrctl.sh version 120.11.12020000.12
NodeManager log is located at /apps1/OSID/fs1/FMW_Home/wlserver_10.3/common/nodemanager/nmHome1
adnodemgrctl.sh: exiting with status 0
adnodemgrctl.sh: check the logfile /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/admin/log/adnodemgrctl.txt for more information ...
You are running adadminsrvctl.sh version 120.10.12020000.10
Starting WLS Admin Server...
Refer /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/admin/log/adadminsrvctl.txt for details
AdminServer logs are located at /apps1/OSID/fs1/FMW_Home/user_projects/domains/EBS_domain_OSID/servers/AdminServer/logs
adadminsrvctl.sh: exiting with status 0
adadminsrvctl.sh: check the logfile /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/admin/log/adadminsrvctl.txt for more information ...
*************** IMPORTANT ****************
WebLogic Admin Password is changed.
Restart all application tier services using control scripts.
********************************************
Program: txkUpdateEBSDomain.pl completed at Tue Aug 21 04:34:33 2018
⦁ Start all services on all nodes, using the following command.
adstrtal.sh apps/apps_password -mode=allnodes
⦁ Login to the admin console to verify that the new WebLogic user password works.
VERSIONS EARLIER THAN TKX DELTA 7
The following steps apply to WebLogic versions earlier than Txk Delta 7:
⦁ Log in to the WebLogic Administrative console.
⦁ Use the existing WebLogic admin username and password.
⦁ Click Lock & Edit and click Domain.
⦁ Click on the Security tab and click on the Advanced tab.
⦁ Enter the new password that you want for the WebLogic admin user in the Node Manager Password.
⦁ Click Save and verify that the settings were updated successfully. Then click on Activate Changes.
⦁ Click Security Realms and Click Myrealm.
⦁ Select Users and Groups, select Weblogic User, and enter the new password for the WebLogic user.
⦁ Make sure that the settings have been updated successfully.
Note: In the next prepare phase to run after the password change, Oracle Ad Online Patching (adop) invokes EBS Domain Configuration to ensure that the WLS data source on the patch file system is synchronized with the new Apps password
Most of them are open questions that I can think of , they have multiple correct answers, we need to choose the best one we can explain,this questions are sourced from multiple interviews are attended and multiple blogs,If anyone needs credit for any information,I will email
⦁ What if client reports db is slow, what will you do ⦁ what if client reports a conc prog is runnning slow without any changes made
1. check for locks and check for that perticular table locks used by the concurrent program2. check the concurrent manager log and out files and delete them if occupying lot of space
to check report based locks:-
you know that conc req id.. by using that
select * from v$session where process in(select os_process_id from fnd_concurrent_requests where request_id='<Req Id>')
from this u will get SID
take that sid and find object_id's from below command
select * from v$locked_objects where session_id='SID'
no take all the object_id's and check any other session is locking those object_ids appart from the above SID...
select * from v$locked_objects where object_id =''
if any session is locking check the status of that session from v$session if that is inactive kill that session ... to free up the resource
________________
⦁ how do you configure a standby
prepare the primary and the physical Standby EnvironmentsPrimary Server: Step 1
Oracle Data Guard requires the primary database to be run in FORCE LOGGING mode. This means that statements using the NOLOGGING option will still generate redo information to maintain the Data Guard standby databases.
[oracle@dg1 ~]$ sqlplus / as sysdba
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL>
The standby log files are required to store redo received from the primary database. Standby redo logs must be equal or larger to the largest redo log file in the primary database. The standby redo log must have at least one more redo log group than the redo log on the primary database. It is recommended that the standby redo log is created on the primary database and propagated to the standby for any switch over or fail over operations.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 7 52428800 512 2 YES INACTIVE 828952 15-JAN-12 849105 16-JAN-12
2 1 8 52428800 512 2 NO CURRENT 849105 16-JAN-12 2.8147E+14
3 1 6 52428800 512 2 YES INACTIVE 822304 15-JAN-12 828952 15-JAN-12
SQL> alter database add standby logfile size 50M;
Database altered.
SQL>
SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.
SQL>
SQL> select * from v$logfile;
Next, set the LOG_ARCHIVE_CONFIG parameter. The LOG_ARCHIVE_CONFIG parameter enables or disables the sending of redo streams to the standby sites. The DB_UNIQUE_NAME of the primary database is dg1 and the DB_UNIQUE_NAME of the standby database is dg2. The primary database is configured to ship redo log stream to the standby database. In this example, the standby database service is dg2.
Next, STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are added or dropped from the primary database, these changes are made to the standby databases automatically. The STANDBY_FILE_MANAGEMENT is only applicable to the physical standby databases.
Setting the STANDBY_FILE_MANAGEMENT parameter to AUTO is is recommended when using Oracle Managed Files (OMF) on the primary database. Next, the primary database must be running in ARCHIVELOG mode.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
db_name string dg1
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
db_unique_name string dg1
SQL> alter system set log_archive_config='dg_config=(dg1,dg2)';
System altered.
SQL> alter system set log_archive_dest_2=
2 'service=dg2 async valid_for=(online_logfile,primary_role) db_unique_name=dg2';
System altered.
SQL> alter system set standby_file_management=AUTO;
System altered.
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
SQL> exit;
Ensure the Oracle Net Services aliases for both the primary database and standby database are added to the tnsnames.ora file.
[oracle@dg1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
dg1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg1)
)
)
dg2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg2)
)
)
[oracle@dg1 ~]$
Copy the updated tnsnames.ora file to the standby site (host).
Standby Server: Step 2
The Oracle database binaries have already been installed at this location ($ORACLE_HOME). The new standby database will have dg2 as the SID.
The listener on the standby site must be configured with a static service entry for the standby database. Restart the listener after modification.
[oracle@dg2 admin]$ cat listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=dg2.localdomain)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=dg2)
(SID_NAME=dg2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)
[oracle@dg2 admin]$ lsnrctl start
Create audit directory files under $ORACLE_BASE/admin/$ORACLE_SID/adump. In my example, I will also create the Fast recover area and the oradata directories.
[oracle@dg2 ~]$ mkdir -p $ORACLE_BASE/admin/dg2/adump
[root@dg2 ~]# mkdir -p /opt/oradata
[root@dg2 ~]# mkdir -p /opt/fast_recovery_area
Next, create a simple parameter file on the standby hist with the DB_NAME value.
[oracle@dg2 dbs]$ echo DB_NAME=dg2 > initdg2.ora
[oracle@dg2 dbs]$ cat initdg2.ora
DB_NAME=dg2
[oracle@dg2 dbs]$
The primary database password file must be copied to the standby system for redo authentication.
[oracle@dg2 dbs]$ scp dg1:$ORACLE_HOME/dbs/orapwdg1 orapwdg2
In the next part of the document, we will create the standby database, verity the physical standby database. We will Standby Database Creation
Start the standby database instance in NOMOUNT start up mode:
SQL> startup nomount pfile=initdg2.ora;
Now that the configuration of the standby server is complete, let’s perform the duplication from the primary site.
Primary Server
Use the Recovery Manager (RMAN) to duplicate the primary database to the standby database.
Invoke RMAN; connect to the primary database as the sys user. Make an auxiliary connection to the standby instance:
RMAN> connect target sys
target database Password:
connected to target database: DG1 (DBID=1753913301)
RMAN> connect auxiliary sys@dg2
auxiliary database Password:
connected to auxiliary database: DG2 (not mounted)
RMAN> run{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'dg1','dg2'
set db_unique_name='dg2'
set db_file_name_convert='/dg1/','/dg2/'
set log_file_name_convert='/dg1/','/dg2/'
set control_files='/opt/oradata/dg2/dg2.ctl'
set log_archive_max_processes='5'
set fal_client='dg2'
set fal_server='dg1'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(dg1,dg2)'
set log_archive_dest_2='service=dg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dg1'
;
}
The RMAN duplication process is finished without errors. After the database duplication, we will force the log switch on the primary database to archive the current redo log group.
SQL> alter system switch logfile;
Standby Server
On the standby database, run the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE command to start redo apply.The USING CURRENT LOGFILE means the redo is applied as soon as it is received on the standby.The DISCONNECT option means the redo apply will run in background session.
SQL> alter database recover managed standby database
using current logfile disconnect;
Note that the current log sequence number on the standby is 10.
SQL> select sequence#, first_time, applied
from v$archived_log
order by sequence#;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
9 16-JAN-12 YES
10 16-JAN-12 IN-MEMORY
Primary Server
Let’s perform 3 additional log switches on the primary database. Then we will query the standby database to verify that the log files are applied to the standby.
SQL> alter system switch logfile;
Standby Server
We query the standby database. The logs were successfully transported and applied.
SQL> select sequence#, first_time, applied
from v$archived_log
order by sequence#;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
9 16-JAN-12 YES
10 16-JAN-12 YES
11 16-JAN-12 YES
12 16-JAN-12 YES
13 16-JAN-12 IN-MEMORY
Broker Configuration
⦁ explain about load balancer and its configuration
s_webentryurlprotocol http or httpss_webentryhost load balancer hostname
s_webentrydomain load balancer domain name
s_active_webport load balancer port
s_login_page load balancer url
s_external_url load balancer url
Section 5: Configuring the E-Business Suite for DMZ Deployments
This section provides the configuration instructions for the deployment models described in this document. Certain common configuration steps must be carried out regardless of which deployment model is used. The details for these common steps are explained from section 5.1 through section 5.4. After completing the common steps, you can proceed to either section 5.5, section 5.6 or section 5.7 depending on which deployment option is chosen.
5.1: Update Hierarchy Type
Several user profile options are used to construct various URLs in an E-Business Suite R12 environment. These user profiles are as follows:
User Profile Name Internal Name
1. Applications Web Agent APPS_WEB_AGENT
2. Applications Servlet Agent APPS_SERVLET_AGENT
3. Applications JSP Agent APPS_JSP_AGENT
4. Applications Framework Agent APPS_FRAMEWORK_AGENT
5. ICX:Forms Launcher ICX_FORMS_LAUNCHER
6. ICX: Oracle Discoverer Launcher ICX_DISCOVERER_LAUNCHER
7. ICX: Oracle Discoverer Viewer Launcher ICX_DISCOVERER_VIEWER_LAUNCHER
8. Applications Help Web Agent HELP_WEB_AGENT
9. Applications Portal APPS_PORTAL
10. BOM:Configurator URL of UI Manager CZ_UIMGR_URL
11. QP: Pricing Engine URL QP_PRICING_ENGINE_URL
12. TCF:HOST TCF:HOST
The default hierarchy type value for the above profile options could be either Security/Server. See diagram below:
The configuration of the E-Business Suite environment for DMZ requires these profile options hierarchy type to be set to SERVER-RESPONSIBILITY TYPE.
1. To change the profile options hierarchy type values to SERVRESP, execute thetxkChangeProfH.sqlSQL script as shown below:
sqlplus apps/apps@/patch/115/sql/txkChangeProfH.sql SERVRESP
2. After thetxkChangeProfH.sqlscript executes successfully, run AutoConfig on all nodes to complete the profile options configuration.
5.2: Update Node Trust Level
Oracle E-Business Suite Release 12 has the capability to restrict access to a predefined set of responsibilities based on the Web server from which the user logs in. This capability is provided by tagging web servers with a trust level indicated by the Node Trust Level (NODE_TRUST_LEVEL) server profile option. The Node Trust Level indicates the level of trust associated with a particular web server. Currently, three trust levels are supported:
Administrative
Servers marked as Administrative are typically those used exclusively by system administrators. These servers are considered secure and provide access to any and all E-Business Suite functions.
Normal
Servers marked as Normal are those used by employees within a company’s firewall. Users logging in from normal servers have access to only a limited set of responsibilities.
External
Servers marked as External are those used by customers or employees outside of a company’s firewall. These servers have access to an even smaller set of responsibilities.
The default value for this profile option for all E-Business Suite middle tiers is Normal. If you wish to learn more about the Node Trust Level profile option, please refer to Oracle Applications System Administrators Guide .
Set the NODE_TRUST_LEVEL profile option value on the external web tier in your Oracle E-business Suite Release 12 environment to External. See diagram below.
To change the value of the Node Trust Level profile option value to External for a particular node, perform the following steps:
⦁ Login to Oracle E-Business Suite as sysadmin user using the internal URL
⦁ Select the System Administrator Responsibility
⦁ Select Profile / System
⦁ From the 'Find system profile option Values' window, select the server that you want to designate as the external web tier
⦁ Query for%NODE%TRUST%. You will see a profile option named 'Node Trust Level'. The value for this profile option at the site level will be Normal. Leave this setting unchanged.
⦁ Set the value of this profile option to External at the server level. The site level value should remain set to Normal
5.3: Update List of Responsibilities
The steps described in this section are required only if you have marked any of the Oracle E-Business Suite Release 12 middle tiers as External as described in section 5.2.
After updating the server-level profile value for Node Trust Level for the external web tier(s) to External, users can no longer see any responsibilities when they login via the external web tier. In order for a responsibility to be available from the external E-Business Suite web tier, set the Responsibility Trust Level profile option value for that responsibility to External at the responsibility level. For information on additional product specific responsibilities that can be made externally accessible from the external E-Business Suite middle tier, please refer to Appendix B1. Oracle E-Business Suite Product Specific Configurations.
To change the value of the Responsibility Trust Level profile option at the responsibility level for a particular responsibility, perform the following steps:
⦁ Login to Oracle E-Business Suite as sysadmin user using the internal URL
⦁ Select System Administrator Responsibility
⦁ Select Profile / System
⦁ From the 'Find system profile option Values' window, select the responsibility that you want to make available to users logging in via the external web tier
⦁ Query for%RESP%TRUST%. You will see a profile option named 'Responsibility trust level'. The value for this profile option at site level will be Normal. Leave this setting unchanged.
⦁ Set the value of this profile option for the chosen responsibility to External at the responsibility level. The site-level value should remain Normal.
⦁ Repeat for all responsibilities that you want to make available from the external web tier.
5.4: Configuration Details for Using Reverse Proxy and an External Web Tier in DMZ
The steps described in this section assume that you have already set up the reverse proxy server of your choice and you are ready to make modifications to the Oracle E-Business Suite Applications Context file on the external web tier. To complete the configuration for this option, follow the steps given below.
Oracle does not certify specific reverse proxy solutions from third-party vendors. The instructions included in this document are generally applicable to third-party reverse proxy solutions, including (but not restricted to) Apache, Microsoft Proxy Server, and other products.
5.4.1: Update Oracle E-Business Suite Applications Context File
On the external Oracle E-Business Suite web node, run the AutoConfig Context Editor as documented in the Oracle MetaLink note 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications Release 12". In the Context Detail screen, set the following configuration values:
⦁ set the webentry point, s_webentryhost, to the reverse proxy server
⦁ set the webentry domain, s_webentrydomain, to the domain name of the reverse proxy server
⦁ set the external URL, s_external_url to the external web node URL.
⦁ set the active webport, s_active_webport, to the port where the reverse proxy server listen for client requests. For example port 80 for HTTP or 443 for HTTPS
⦁ set the webentry protocol, s_webentryurlprotocol, to the protocol value the clients use to access the reverse proxy server
⦁ set the login page, s_login_page, to <webentry protocol>://<webentry host>.<webentry domain>:<active web port>. Replace <webentry protocol>, <webentry host>, <webentry domain>, and <active web port> with their respective values
⦁ set the help web agent s_help_web_agent, to <webentry protocol>://<webentry host>.<webentry domain>:<active web port> . Replace <webentry protocol>, <webentry host>, <webentry domain>, and <active web port> with their respective values.
5.4.2: Run AutoConfig and Restart Oracle Application Server Processes
⦁ Run AutoConfig on each Applications middle tier . Please refer to the Oracle MetaLink note ⦁ 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications R12 " for more information on AutoConfig.
⦁ After AutoConfig completes successfully, restart Oracle Application server processes on the external web tier.
Proceed to the Appendices for any additional Oracle E-Business Suite product specific settings that needs to be done.
5.5: Configuration Details for Using Separate Oracle E-Business Suite Web Tier in DMZ
There are no extra steps needed for this configuration. Proceed to the Appendices for any additional Oracle E-Business Suite product specific settings that needs to be done.
5.6: Configuration Details for Using HTTP Hardware Load Balancers in DMZ
To complete the configuration for this option, follow the steps given below.
5.6.1: Update Oracle Applications Context File
On the internal Applications middle-tier nodes, run the AutoConfig Context Editor as documented in the Oracle MetaLink note 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications R12 ". In the Context Detail screen, set the following configuration values:
⦁ set the webentry point, s_webentryhost, to the load balancer that is used to load balance the internal Applications middle tiers
⦁ set the webentry domain, s_webentrydomain, to the domain name of the load balancer
⦁ set the active webport, s_active_webport, to the value of the load balancer's external port
⦁ set the webentry protocol, s_webentryurlprotocol, to the load balancer's protocol e.g. "http" or "https".
⦁ set the login page, s_login_page, to<webentry protocol>://<webentry host>.<webentry domain>:<active web port>. Replace <webentry protocol>, <webentry host>, <webentry domain>, and <active web port> with their respective values
⦁ set the help web agent, s_help_web_agent, to<webentry protocol>://<webentry host>.<webentry domain>:<active web port> . Replace <webentry protocol>, <webentry host>, <webentry domain>, and <active web port> with their respective values
On the external Applications web tier node, run the AutoConfig Context Editor as documented in the Oracle MetaLink note 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications R12 ". In the Context Detail screen, set the following configuration values:
⦁ set the webentry point, s_webentryhost, to the load balancer that is used to load balance the external Applications middle tiers
⦁ set the webentry domain, s_webentrydomain, to the domain name of the load balancer
⦁ set the external URL, s_external_url to the external web node URL.
⦁ set the active webport, s_active_webport, to the value of the load balancer's external port
⦁ set the webentry protocol, s_webentryurlprotocol, to the load balancer's protocol e.g. "http" or "https"
⦁ set the login page, s_login_page, to <webentry protocol>://<webentry host>.<webentry domain>:<active web port>. Replace <webentry protocol>, <webentry host>, <webentry domain>, and <active web port> with their respective values.
⦁ set the help web agent, s_help_web_agent, to<webentry protocol>://<webentry host>.<webentry domain>:<active web port> . Replace <webentry protocol>, <webentry host>, <webentry domain>, and <active web port> with their respective values
5.6.2: Run AutoConfig and Restart Oracle Applications Processes
⦁ Run AutoConfig on each Applications middle tier to complete the configuration. Please refer to the Oracle MetaLink note ⦁ 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications R12 " for more information on AutoConfig.
⦁ After AutoConfig completes successfully, restart Oracle Applications server processes.
Proceed to the Appendices for any additional Oracle E-Business Suite product specific settings that needs to be done.
5.7: Enable Oracle E-Business Suite Application Server Security
Oracle E-Business Suite Release 12 is deployed in a multi-tier configuration with one Database Server and many possible middle-tier Application Servers. The Application Servers include Apache JSP/Servlet, Forms, Discoverer and also some client programs such as Application Desktop Integrator, Oracle Discoverer Admin Edition. Any program which makes a SQLNET connection to the Oracle E-Business Suite database needs to be trusted at some level. This security feature ensures that such SQLNET connections are coming from trusted machines and/or trusted programs.
The Server Security feature supports authentication of application server machines and code modules in order to access the database. When Server Security is activated, Application Servers are required to supply server IDs (like passwords) and/or code IDs to access a database server. Server IDs identify the machine from which the connection is originating. Code IDs identify the module and patch level from which the connection is originating. Code IDs are included in applications code by development. The database server can be set to allow access only from specific machines and/or by code at a desired patch level.
The application server security feature is activated by default for all E-Business Suite installations. It is recommended that you ensure that the server security feature is enabled by performing the steps given below:
Run the AutoConfig Context Editor as documented in the Oracle MetaLink note 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications R12 ". In the Context Detail screen, review the following configuration values for both internal and external nodes:
⦁ Value of Application Server Security Authentication (s_appserverid_authentication) is set to SECURE .If the value is not set to SECURE, follow the instructions given below:
⦁ Set the value of Application Server Security Authentication (s_appserverid_authentication) to SECURE
⦁ Run AutoConfig on each Applications middle tier to complete the configuration. Please refer to the Oracle MetaLink note ⦁ 387859.1 "Using AutoConfig to Manage System Configurations with Oracle Applications R12 " for more information on AutoConfig
⦁ After AutoConfig completes successfully, restart Oracle HTTP Server and OC4J processes
5.8: Enable Distributed Oracle Java Object Cache Functionality
Distributed caching functionality has to be enabled in a DMZ environment to avoid data inconsistencies for data such as profiles, menu, responsibilities and product specific data. To complete this configuration, follow the steps given below:
⦁ Identify the highest number of JVMs that serve the oacore JVM group in the internal and external middle tiers. For eg: if there are 3 JVMs in the internal and 2 JVMs configured for the external middle tier, take the number as 3.
⦁ Identify the number of java processes spawned by the concurrent manager tier. For eg: if there are 3 JVMs spawned by the ICM, take the number as 3 . Add this to the number of oacore JVMs . In the example given above, the total number JVMs thus become 6 . So, six ports need to be opened in the firewall. You can use the 'pstree' command to check the number of java processes spawned by the concurrent manager parent process. For eg:pstree -p 26258 where 26258 is the process ID of the FNDSM process.
⦁ Identify the ports to open in the firewall that separates the external middle tier and the internal middle tier . For eg: if the JVM count is 3, you have to open 3 ports on this firewall.
⦁ This range of ports need to be specified as a value for the autoconfig variable ( s_fnd_cache_port_range ) . Please make sure that the value is same in all the applications context files . The value should be specified as a range. For eg: 36500-36505. When AutoConfig completes the configuration, the value specified for this variable in the context file will get updated in the FND_CACHE_PORT_RANGE profile option.
⦁ In addition to the ports specified above, you must ensure that the Java Object Cache Port specified as a value for the autoconfig variable s_java_object_cache_port is also open on the firewall that separate the external and internal middle tiers.
You must run Autoconfig to complete the configuration after editing the applications context file.
Attention
In a multinode installation, the AutoConfig variable s_java_object_cache_port must be set identically on all nodes. Similarly, s_fnd_cache_port_range must be set identically on all nodes. Please note that s_java_object_cache_port must be set to a different value from s_fnd_cache_port_range in the same applications context file to avoid port conflicts.
5.9: Configuration Details for Using reverse proxy with No External Web Tier
This configuration requires your internal application middle tier server to have at least two network interfaces. One network interface is required for the external entry point and another for the internal entry point. These network interfaces must be configured to resolve to two different hostnames in the DNS.
For example:
/etc/hosts of Internal Server 1
130.30.21.1 internal1.company.com internal1
130.30.21.2 external1.company.com external1
5.9.1: Create a new context file for the external Web Entry Point
⦁ To create a context file for the external entry point, execute the commands shown in the table below:
$ perl $COMMON_TOP/clone/bin/adclonectx.pl \
contextfile=$CONTEXT_FILE \
outfile= <name of the output file including location>
For example:
Internal Server Name 1: internal1.company.com
External Server Name 1: external1.company.com
Context file for Internal Entry Point on Internal Server 1 including its location: /d1/applmgr/v
⦁ expalin PCP and failoverProblem: How to Setup and Test Failover of Parallel concurrent Processing PCP
Solution:
· Setup the primary node with 11.5.10.2 ATG RUP6 installed and having CP,Admin,Forms,Web and DB set on it.
· Add and register a new secondary node which has just CP and Admin on it. (You can use shared APPL_TOP for this setup)
· If using Virtual Hostnames, please ensure that the Physical Hostname of the CP node with virtual host is registered from OAM.
· Once both nodes are ready, set the APPLDCP variable to ON in the context file of both nodes.
· Shutdown the application tier services of both the nodes and run AutoConfig on each node. After AutoConfig completes successfully verify that the tnsnames.ora (on both CP nodes) has the FNDFS entries of both the nodes.
· Ensure that the Internal Monitors on both nodes defined properly and have workshifts assigned to them. Also make sure the Internal Monitor manager is activated by going into Concurrent -> Manager -> Adminitrator and activate the manager as they need to be active on the nodes where the ICM can start in case of a failure..
· Define the Primary and Secondary nodes for your Internal Concurrent Manager and Standard Managers (Concurrent Manager Define form)
To ensure that failover of the concurrent managers when one concurrent node fails happens in time,ensure following steps are followed :
· The sqlnet.expire_time in sqlnet.ora is set to 1. This is to be done for the database listeners (not apps listener).
· Ensure that One-off Patch 6495206 and Patch 9074947 have been applied.
⦁ Talk about differences in 11g and 10g
Automatic Memory Tuning - Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was introduced in Oracle 10g. In 11g, all memory can be tuned automatically by setting one parameter. PGA Update: Oracle technology is constantly changing, so don't miss my new notes on updates to Oracle PGA behavior. Also see these important notes on over-riding the Oracle PGA defaults.
Enhanced Password - Pete Finnigan notes some new Oracle 11g security features "[Oracle 11g] will have case sensitive passwords and also the password algorithm has changed to SHA-1 instead of the old DES based hashing used."
Automatic Diagnostic Repository - When critical errors are detected, Oracle automatically creates an “incident” ticket, notifying the DBA instantly.
New table Data Type "simple_integer" - A new 11g datatype dubbedsimple_integer is introduced. The simple_integer data type is always NOT NULL, wraps instead of overflows and is faster than PLS_INTEGER. Source:Lewis Cunningham
RMAN UNDO bypass - RMAN backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. Now RMAN can bypass those types of tablespace. Great for exporting a tablespace from backup. Source: Laurent Schneider
Virtual columns - Oracle 11g virtual table columns are columns that are actually functions ("create table t1 (c1 number, c2 number, c3 as (c1+c2) virtual"), and similarly, virtual indexes that are based on functions.
Oracle 11g RAC parallel upgrades - Oracle 11g promises to have a rolling upgrade features whereby RAC database can be upgraded without any downtime. Ellison first promised this feature i
Disabled state for PL/SQL - Another 11g new feature is a "disabled" state for PL/SQL (as opposed to "enabled" and "invalid" in dba_objects).
The /*+result_cache*/ SQL hint - This suggests that the result data will be cached in the data buffers, and not the intermediate data blocks that were accessed to obtain the query
Automatic Memory Tuning - Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was introduced in Oracle 10g. In 11g, all memory can be tuned automatically by setting one parameter. You literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set. This is controlled by the Oracle 11g memory_target parameter.
⦁ Differences in 11i and r12
⦁ what is request flow process in database and in applications?
⦁ how do you monitor the load on the DB?
⦁ How do you monitor apps? (what all components needs monitoring)
⦁ what does adcfgclone do ? what are important driver files run by autoconfig?
⦁ fndload usages?
⦁ what do you do when a customer tells he is not able to ping the URL of ebs
⦁ what all issues you faced while running autocfg
⦁ What do u know about Workflow
⦁ What is a DMZ why do we need it
⦁ How many types of patches did u work on
⦁ What errors did you face in upgrade process that effected delivery time
⦁ What do u do in case of SEV1 what is step by step procedure you follow
⦁ How do u restore a datafile that was deleted accidentally
⦁ What do u know about ASM
⦁ On how many OS have u worked do u know basics of shell scripting?
⦁ RAC architecture vs Standby vs Goldengate .. what do think will giv higst availability and failover & why
⦁ Describe how you do a general code migration. (don’t forget to tell that u take backup of already existing file)
⦁ Did u generate any business out of your own recommendations to clients?
⦁ What were the sizes of the database you worked on and the amount of activity (tell in amount of log generated per day).
⦁ What very imp features does 12c bring?
⦁ Tell me how online patching is possible in 12c?
⦁ What is the basic element of base configuration of an Oracle database?
⦁ What happens if you don’t give cache size while defining concurrent manager?
Q What happens if you don’t give cache size while defining concurrent manager?
Most often when …a request goes "inactive/no manager" and is then processed a short time later, the solution is to either increase the cache size for your Standard manger, or increase the actual number of Standard Manager processes that can run. Cache Size is set in the Concurrent/Manager/Define form. Basically, this regulates how many requests a manager will pick up for each sleep cycle.
Increasing the cache size within reason allows you to decrease the sleep cycle. In turn, your managers will wake up and scroll through the tables less frequently, which reduces the amount of work they have to do as well as the amount of system resources utilized. We see sleep cycles set to 5 seconds at some customer sites and recommend increasing the value, since the managers are waking up and re-reading the same table far too frequently. For reports that for the most part take a minimum of 1 minute to run, the queue wakes up 12 times to check for runnable processes before even one running request completes.
⦁ Why should Apps & Applsys passwords always be the same?
⦁ How do u find versions of various components (apache,disco,forms etc)
⦁ What happens if the ICM goes down?
If the ICM goes down, requests will continue to run normally, except for “Queue Control” requests. “Queue Control”requests are those programs for which QUEUE_CONTROL_FLAG=Y In table fnd_concurrent_programsSQL> select CONCURRENT_PROGRAM_NAME from fnd_concurrent_programs where QUEUE_CONTROL_FLAG='Y';
If the ICM goes down, you can restart it with 'startmgr'. There is no need to shut down the other managers first.
⦁ After a sys password change on prod my archives are not getting applied on standby , why? What do u do to resolve?
⦁ What do you know about forms palyback?
⦁ Right now a client on 10g asked u to upgrade, which version do u suggest? (always stick with the stable version nd never the new version, 11.2.0.3 in this case)
⦁ Can I run 64 bit application on 32 bit Operating system ?
Topics:
Architecture both DB and Apps and comparisons between old and new versions
RAC and ASM
Standbys
Patching
Cloning DB and Apps
Upgrades DB and Apps ( they may ask about min conf requirement for the verion u have upgraded for OS and also imp patches)
Common issues that you faced and how u resolved them
R12 and 11i Filestructure
R12.2 file structure
⦁ Use of Oracle WebLogic Server instead of AutoConfig for many (but not all) of AutoConfig's previous functions.
⦁ Use of Oracle WebLogic Server instead of Oracle Containers for Java (OC4J).
Edition based redefinition
Adcfgclone
Details of Adpreclone and Adcfgclone
(Interview Ques: What happens internally when you run adpreclone and adcfgclone)
I am covering the Internal Cloning Process which you will not get eaily on the net. Generally you find the steps for cloning like run adpreclone on apps and db tier then copy the file sytem to Target then run adcfgclone on apps tier and db Tier.
What happens when you run adpreclone and adcfgclone. The below steps give you better understanding and enhance your troubleshooting skills.
When you run this commnad adpreclone.pl dbTier . This will run in two steps Techstack and database.
Techstack:
It will create following directories in the ORACLE_HOME/appsutil/clone
Jlib, db, data where “Jlib” relates to libraries “db” will contain the techstack information, “data” will contain the information related to datafiles and required for cloning.
Creates driver files at ORACLE_HOME/appsutil/driver/instconf.drv
Converts inventory from binary to xml, the xml file is located at $ORACLE_HOME/appsutil/clone/context/db/Sid_context.xml
Prepare database for cloning:
This includes creating datbase control file script and datafile location information file at
$ORACLE_HOME/appsutil/template
adcrdbclone.sql, dbfinfo.lst
Generates database creation driver file at ORACLE_HOME/appsutil/clone/data/driver
data.drv
Copy JDBC Libraries at ORACLE_HOME/appsutil/clone/jlib/classes12.jar and appsoui
When Running adpreclone appsTier
This will create stage directory at $COMMON_TOP/clone. This also run in two steps.
Techstack:
Creates template files for
Oracle_iAS_Home/appsutil/template
Oracle_806_Home/appsutil/template
Creates Techstack driver files for
IAS_ORACLE_HOME/appsutil/driver/instconf.drv
806_ORACLE_HOME/appsutil/driver/instconf.drv
APPL_TOP preparation:
-It will create application top driver file$COMMON_TOP/clone/appl/driver/appl.drv
-Copy JDBC libraries$COMMON_TOP/clone/jlib/classes111.zip
Now Shutdown all the services of Application and database for Copy the file System to target location
Configuring the target system
Once it is done . Run as below adcfclone.pl for apps Tier and dbTier.
On database side:
cd $ORACLE_HOME/appsutils/clone/binperl adcfgclone.pl dbTier pwd=apps
This will use the templates and driver files those were created while running adpreclone.pl on source system and has been copied to target system.
Following scripts are run by adcfgclone.pl dbTier for configuring techstack
adchkutl.sh — This will check the system for ld, ar, cc, and make versions.
adclonectx.pl — This will clone the context file. This will ceate a new context file as per the details of this instance.
runInstallConfigDriver — located in $Oracle_Home/appsutil/driver/instconf.drv
Relinking $Oracle_Home/appsutil/install/adlnkoh.sh — This will relink ORACLE_HOME
For data on database side, following scripts are run
Driver file $Oracle_Home/appsutil/clone/context/data/driver/data.drv
Create database adcrdb.zip
Autoconfig is run
Control file creation adcrdbclone.sql
On Application Side:
COMMON_TOP/clone/bin/perl adcfgclone.pl appsTier pwd=apps
Following scripts are run by adcfgclone.pl
Creates context file for target adclonectx.pl
Run driver files
$ORACLE_HOME/appsutil/driver/instconf.drv
$IAS_ORACLE_HOME/appsutil/driver/instconf.drv
Relinking of Oracle Home$ORACLE_HOME/bin/adlnk806.sh$IAS_ORACLE_HOME/bin/adlnkiAS.sh
At the end it will run the driver file $COMMON_TOP/clone/appl/driver/appl.drv and then runs autoconfig.
Background processes
Background Processes
To maximize performance and accommodate many users, a multiprocess Oracle system uses some additional Oracle processes called background processes.An Oracle instance can have many background processes; not all are always present. There are numerous background processes. See the V$BGPROCESS view for more information on the background processes. The background processes in an Oracle instance can include the following:
Database Writer Process (DBW⦁ n⦁ )
Log Writer Process (LGWR)
Checkpoint Process (CKPT)
System Monitor Process (SMON)
Process Monitor Process (PMON)
Recoverer Process (RECO)
Job Queue Processes
Archiver Processes (ARC⦁ n⦁ )
Queue Monitor Processes (QMN⦁ n⦁ )
Other Background Processes
On many operating systems, background processes are created automatically when an instance is started.
Figure 9-2 illustrates how each background process interacts with the different parts of an Oracle database, and the rest of this section describes each process.
See Also:
Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for more information. Real Application Clusters are not illustrated in ⦁ Figure 9-2
⦁ Your operating system-specific documentation for details on how these processes are created
Figure 9-2 Background Processes of a Multiple-Process Oracle Instance
Description of "Figure 9-2 Background Processes of a Multiple-Process Oracle Instance"
Database Writer Process (DBWn)
The database writer process (DBWn) writes the contents of buffers to datafiles. The DBWn processes are responsible for writing modified (dirty) buffers in the database buffer cache to disk. Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes (DBW1 through DBW9 and DBWa through DBWj) to improve write performance if your system modifies data heavily. These additional DBWn processes are not useful on uniprocessor systems.
When a buffer in the database buffer cache is modified, it is marked dirty. A cold buffer is a buffer that has not been recently used according to the least recently used (LRU) algorithm. The DBWn process writes cold, dirty buffers to disk so that user processes are able to find cold, clean buffers that can be used to read new blocks into the cache. As buffers are dirtied by user processes, the number of free buffers diminishes. If the number of free buffers drops too low, user processes that must read blocks from disk into the cache are not able to find free buffers. DBWn manages the buffer cache so that user processes can always find free buffers.
By writing cold, dirty buffers to disk, DBWn improves the performance of finding free buffers while keeping recently used buffers resident in memory. For example, blocks that are part of frequently accessed small tables or indexes are kept in the cache so that they do not need to be read in again from disk. The LRU algorithm keeps more frequently accessed blocks in the buffer cache so that when a buffer is written to disk, it is unlikely to contain data that will be useful soon.
The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes. The maximum number of DBWn processes is 20. If it is not specified by the user during startup, Oracle determines how to set DB_WRITER_PROCESSES based on the number of CPUs and processor groups.
The DBWn process writes dirty buffers to disk under the following conditions:
⦁ When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers, it signals DBWn to write. DBWn writes dirty buffers to disk asynchronously while performing other processing.
⦁ DBWn periodically writes buffers to advance the checkpoint, which is the position in the redo thread (log) from which instance recovery begins. This log position is determined by the oldest dirty buffer in the buffer cache.
In all cases, DBWn performs batched (multiblock) writes to improve efficiency. The number of blocks written in a multiblock write varies by operating system.
See Also:
"Database Buffer Cache"
Oracle Database Performance Tuning Guide for advice on setting DB_WRITER_PROCESSES and for information about how to monitor and tune the performance of a single DBW0 process or multiple DBWn processes
Oracle Database Backup and Recovery Basics
Log Writer Process (LGWR)
The log writer process (LGWR) is responsible for redo log buffer management—writing the redo log buffer to a redo log file on disk. LGWR writes all redo entries that have been copied into the buffer since the last time it wrote.
The redo log buffer is a circular buffer. When LGWR writes redo entries from the redo log buffer to a redo log file, server processes can then copy new entries over the entries in the redo log buffer that have been written to disk. LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries, even when access to the redo log is heavy.
LGWR writes one contiguous portion of the buffer to disk. LGWR writes:
⦁ A commit record when a user process commits a transaction
⦁ Redo log buffers
⦁ Every three seconds
⦁ When the redo log buffer is one-third full
⦁ When a DBWn process writes modified buffers to disk, if necessary
Note:
Before DBWn can write a modified buffer, all redo records associated with the changes to the buffer must be written to disk (the write-ahead protocol). If DBWn finds that some redo records have not been written, it signals LGWR to write the redo records to disk and waits for LGWR to complete writing the redo log buffer before it can write out the data buffers.
LGWR writes synchronously to the active mirrored group of redo log files. If one of the files in the group is damaged or unavailable, LGWR continues writing to other files in the group and logs an error in the LGWR trace file and in the system alert log. If all files in a group are damaged, or the group is unavailable because it has not been archived, LGWR cannot continue to function.
When a user issues a COMMIT statement, LGWR puts a commit record in the redo log buffer and writes it to disk immediately, along with the transaction's redo entries. The corresponding changes to data blocks are deferred until it is more efficient to write them. This is called a fast commit mechanism. The atomic write of the redo entry containing the transaction's commit record is the single event that determines the transaction has committed. Oracle returns a success code to the committing transaction, although the data buffers have not yet been written to disk.
Note:
Sometimes, if more buffer space is needed, LGWR writes redo log entries before a transaction is committed. These entries become permanent only if the transaction is later committed.
When a user commits a transaction, the transaction is assigned a system change number (SCN), which Oracle records along with the transaction's redo entries in the redo log. SCNs are recorded in the redo log so that recovery operations can be synchronized in Real Application Clusters and distributed databases.
In times of high activity, LGWR can write to the redo log file using group commits. For example, assume that a user commits a transaction. LGWR must write the transaction's redo entries to disk, and as this happens, other users issue COMMIT statements. However, LGWR cannot write to the redo log file to commit these transactions until it has completed its previous write operation. After the first transaction's entries are written to the redo log file, the entire list of redo entries of waiting transactions (not yet committed) can be written to disk in one operation, requiring less I/O than do transaction entries handled individually. Therefore, Oracle minimizes disk I/O and maximizes performance of LGWR. If requests to commit continue at a high rate, then every write (by LGWR) from the redo log buffer can contain multiple commit records.
See Also:
Redo Log Buffer
"Trace Files and the Alert Log"
Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for more information about SCNs and how they are used
Oracle Database Administrator's Guide for more information about SCNs and how they are used
Oracle Database Performance Tuning Guide for information about how to monitor and tune the performance of LGWR
Checkpoint Process (CKPT)
When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process. The CKPT process does not write blocks to disk; DBWn always performs that work.
The statistic DBWR checkpoints displayed by the System_Statistics monitor in Enterprise Manager indicates the number of checkpoint requests completed.
See Also:
Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for information about CKPT with Real Application Clusters
System Monitor Process (SMON)
The system monitor process (SMON) performs recovery, if necessary, at instance startup. SMON is also responsible for cleaning up temporary segments that are no longer in use and for coalescing contiguous free extents within dictionary managed tablespaces. If any terminated transactions were skipped during instance recovery because of file-read or offline errors, SMON recovers them when the tablespace or file is brought back online. SMON checks regularly to see whether it is needed. Other processes can call SMON if they detect a need for it.
With Real Application Clusters, the SMON process of one instance can perform instance recovery for a failed CPU or instance.
See Also:
Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for more information about SMON
Process Monitor Process (PMON)
The process monitor (PMON) performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes.
PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally). PMON also registers information about the instance and dispatcher processes with the network listener.
Like SMON, PMON checks regularly to see whether it is needed and can be called if another process detects the need for it.
Recoverer Process (RECO)
The recoverer process (RECO) is a background process used with the distributed database configuration that automatically resolves failures involving distributed transactions. The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction. When the RECO process reestablishes a connection between involved database servers, it automatically resolves all in-doubt transactions, removing from each database's pending transaction table any rows that correspond to the resolved in-doubt transactions.
If the RECO process fails to connect with a remote server, RECO automatically tries to connect again after a timed interval. However, RECO waits an increasing amount of time (growing exponentially) before it attempts another connection. The RECO process is present only if the instance permits distributed transactions. The number of concurrent distributed transactions is not limited.
See Also:
Oracle Database Administrator's Guide for more information about distributed transaction recovery
Job Queue Processes
Job queue processes are used for batch processing. They run user jobs. They can be viewed as a scheduler service that can be used to schedule jobs as PL/SQL statements or procedures on an Oracle instance. Given a start date and an interval, the job queue processes try to run the job at the next occurrence of the interval.
Job queue processes are managed dynamically. This allows job queue clients to use more job queue processes when required. The resources used by the new processes are released when they are idle.
Dynamic job queue processes can run a large number of jobs concurrently at a given interval. The job queue processes run user jobs as they are assigned by the CJQ process. Here's what happens:
⦁ The coordinator process, named CJQ0, periodically selects jobs that need to be run from the system JOB$ table. New jobs selected are ordered by time.
⦁ The CJQ0 process dynamically spawns job queue slave processes (J000…J999) to run the jobs.
⦁ The job queue process runs one of the jobs that was selected by the CJQ process for execution. The processes run one job at a time.
⦁ After the process finishes execution of a single job, it polls for more jobs. If no jobs are scheduled for execution, then it enters a sleep state, from which it wakes up at periodic intervals and polls for more jobs. If the process does not find any new jobs, then it aborts after a preset interval.
The initialization parameter JOB_QUEUE_PROCESSES represents the maximum number of job queue processes that can concurrently run on an instance. However, clients should not assume that all job queue processes are available for job execution.
Note:
The coordinator process is not started if the initialization parameter JOB_QUEUE_PROCESSES is set to 0.
See Also:
Oracle Database Administrator's Guide for more information about job queues
Archiver Processes (ARCn)
The archiver process (ARCn) copies redo log files to a designated storage device after a log switch has occurred. ARCn processes are present only when the database is in ARCHIVELOG mode, and automatic archiving is enabled.
An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of ARCn processes is insufficient to handle the workload. The alert log keeps a record of when LGWR starts a new ARCn process.
If you anticipate a heavy workload for archiving, such as during bulk loading of data, you can specify multiple archiver processes with the initialization parameter LOG_ARCHIVE_MAX_PROCESSES. The ALTER SYSTEM statement can change the value of this parameter dynamically to increase or decrease the number of ARCn processes. However, you do not need to change this parameter from its default value of 1, because the system determines how many ARCn processes are needed, and LGWR automatically starts up more ARCn processes when the database workload requires more.
Queue Monitor Processes (QMNn)
The queue monitor process is an optional background process for Oracle Streams Advanced Queuing, which monitors the message queues. You can configure up to 10 queue monitor processes. These processes, like the job queue processes, are different from other Oracle background processes in that process failure does not cause the instance to fail.
Cost-Based Optimization
Query optimization is the overall process of choosing the most efficient means of executing a SQL statement. SQL is a nonprocedural language, so the optimizer is free to merge, reorganize, and process in any order.
High Redundancy - In this configuration, for each primary extent, there are two mirrored extents. For Oracle Database Appliance this means, during normal operations there would be three extents (one primary and two secondary) containing the same data, thus providing “high” level of protection. Since ASM distributes the partnering extents in a way that prevents all extents to be unable due to a component failure in the IO path, this configuration can sustain at least two simultaneous disk failures on Oracle Database Appliance (which should be rare but is possible).
Normal Redundancy - In this configuration, for each primary extent, there is one mirrored (secondary) extent. This configuration protects against at least one disk failure. Note that in the event a disk fails in this configuration, although there is typically no outage or data loss, the system operates in a vulnerable state, should a second disk fail while the old failed disk replacement has not completed. Many Oracle Database Appliance customers thus prefer the High Redundancy configuration to mitigate the lack of additional protection during this time.
External Redundancy - In this configuration there are only primary extents and no mirrored extents. This option is typically used in traditional non-appliance environments when the storage sub-system may have existing redundancy such as hardware mirroring or other types of third-party mirroring in place. Oracle Database Appliance does not support External Redundancy.
*ASM redundancy is different from traditional disk mirroring in that ASM mirroring is a logical-physical approach than a pure physical approach. ASM does not mirror entire disks. It mirrors logical storage entities called ‘extents’ that are allocated on physical disks. Thus, all “mirrored” extents of a set of primary extents on a given disk do not need to be on a single mirrored disk but they could be distributed across multiple disks. This approach to mirroring provides significant benefits and flexibility. ASM uses intelligent, Oracle Database Appliance architecture aware, extent placement algorithms to maximize system availability in the event of disk failure(s).
RAC – Cache Fusion
Posted on March 22, 2011by Gopi
In this post let us know something about the main concept of RAC – Cache Fusion.
Now before going into Cache Fusion – first let’s first memorize once again how actually a normal database instance behaves when there is a request for data block.
Let us suppose a user process is requesting a data block, and since this process cannot directly read from the disk, first the requested block must be read(Physical Read) into Buffer Cache of SGA. Once it is read into buffer cache it will remains in the Buffer Cache for further requests. Whenever there is a request for the same data block, since it is already in the buffer, it can be directly read (Buffer Read) from the buffer, thus avoiding another Physical Read. If a data block is found in buffer cache it is called a ‘Cache Hit’ and if it is not found, then it is called a ‘Cache Miss’.
In order to maintain data integrity, when there are concurrent requests for the same data block, Oracle uses Locking Mechanism and multi-version consistency control. A data block can reside in various buffers with different versions, for example a dirtied block, where the previous version of the data block will be maintained in UNDO and the copy of the current version in REDO. Whenever a user request for the block that was already in buffer and dirtied, the UNDO segment provides the required information to construct the read-consistent (CR) image of the data blocks. So, Multi-version data blocks help to achieve read consistency.
The read consistency model guarantees that the data block seen by a statement is consistent with respect to a single point in time and does not change during the statement execution. Readers of data do not wait for other writer’s data or for other readers of the same data. At the same time, writers do not wait for other readers for the same data. Only writers wait for other writers if they attempt to write.
In a single instance the following happens when reading a block
* When a reader reads a recently modified block, it might find an
active transaction in the block.
* The reader will need to read the undo segment header to decide
whether the transaction has been committed or not.
* If the transaction is not committed, the process creates a
consistent read (CR) version of the block in the buffer cache
using the data in the block and the data stored in the undo segment.
* If the undo segment shows the transaction is committed, the process
has to revisit the block and clean out the block and generate the redo
for the changes.
Now let us see how it goes in a RAC environment –
In RAC, there are 2 or more instances accessing same database files that are residing in shared storage area. Each instance has its own SGA and background processes, which means each instance has its own buffer cache (local to each instance). These buffer cache’s act individually at instance level and fuse together at database level to form a single entity (Global Cache) so as to share the data blocks between them. This is what we called ‘Cache Fusion’. Cache Fusion uses a high-speed IPC interconnect to provide cache-to-cache transfers of data blocks between instances in a cluster. This data block shipping eliminates the disk I/O and optimizes read/write concurrency.
Now the question is how the integrity of the data is maintained in a RAC environment, if there are concurrent requests for the same data block – Here too Oracle uses locking and queuing mechanisms to coordinate lock resources, data and inter-instance data requests.
Cache Fusion was implemented by a controlling mechanism called Global Cache Service (GCS), which is responsible for block transfers between instances. The Global Cache Service is implemented by various background processes, such as
Global Cache Service Processes (LMSn)
Global Enqueue Service Daemon (LMD)
[Before going into those processes, let us see how oracle treats the data blocks and how it manages them –
Oracle treats the data blocks as resources. Each of these resources can be held in different modes, which is important mechanism to maintain data integrity. These modes are classified into 3 types depending on whether resource holder intends to modify the data or read the data. They are –
Null (N) mode —Null mode is usually held as a placeholder.
Shared (S) mode — In this mode, data block is not modified by another session, but will allow concurrent shared access.
Exclusive (X) mode — This level grants the holding process exclusive access. Other processes cannot write to the resource. It may have consistent read blocks.
Furthermore, these resources act in one of 2 roles – Local (L) and Global (G).
A resource (data block) is assigned a local role, when a block is first read into the cache and no other instance request for the same data block.
A resource is assigned a Global role, when block is dirtied locally and transmitted to another instance.]
Now let us see what those daemon’s do –
Global Cache Service Daemon (LMSn)
Upon a request from an Instance GCS organizes the block shipping to other instances by retaining block copies in memory. Each such copy is called a past image (PI), which in the event of a node failure, Oracle can reconstruct the current version of a block by using a saved PI. It is also possible to have more than 1 PI of the data block; depending on how many times the block was requested in dirty stage.
New and Changed Features of Oracle Application DBA and Technology Stack (TXK)
Enhanced adop user interface
Category Description Parmameters
Changed UI The UI of the adop utility has been significantly enhanced, to display more selective information on the console. Messages, prompts and other elements have also been extensively refined to increase the ease of use of the various patching commands. Dependent on operation
New adop monitoring and validation features
Category Description Parameters
New Features Progress of an online patching cycle can be followed by running the new Online Patching Monitoring utility (adopmon). This utility can be used to follow the overall progress of a patching cycle, as well as identifying the various individual adop actions being taken. $ adopmon
Before you start a new patching cycle by running the prepare phase, you can optionally check your system's readiness by running adop with the 'validate' option. If you do this while a patching cycle is in progress, validation will take place for the cutover phase. $ adop -validate
Support for new EBS Installation Central Inventory
Category Description Parameters
New Feature Support for an instance-specific EBS Installation Central Inventory has been introduced as an option for the application tier on UNIX platforms. The inventory is identified by <s_base>/oraInventory/oraInst.loc. This feature is useful where multiple Oracle E-Business Suite installations exist on the same host, helping to avoid issues when fs_clone is run simultaneously on different instances. To use the EBS Installation Central Inventory, all application tier Oracle Homes registered in the global inventory for the instance must be migrated to the new inventory. Not applicable
Oracle WebLogic Server performance improvements
Category Desciption Parameters
New Options A new -DserverType=wlx start argument for managed servers reduces their memory footprint, by preventing startup of the Enterprise JavaBeans (EJB), Java EE Connector Architecture (JCA), and Java Message Service (JMS) services. -DserverType=wlx
To reduce oacore startup time, the Portlet Producer libraries are no longer deployed to the EBS domain. A new context variable,s_deploy_portlet, has been introduced to cater for cases where portlet-related configuration is required, such as in instances needing Webcenter integration. s_deploy_portlet
New Mode The default value of s_forms-c4wsstatus is now set to 'Disabled'.Thus, the formsc4-ws servers are no longer started during a 'start all' operation. s_forms-c4wsstatus
New 'dualfs' option in standard cloning
Category Description Parameters
New Option A new 'dualfs' option is available when performing a standard clone, as well as while adding a new node. With the 'dualfs' option, both the run and patch file systems are cloned and configured in a single operation. dualfs
New Features in Oracle DBA 12C:
1. Online rename and relocation of an active data file:Unlike in the previous releases, a data file migration or renaming in Oracle database 12c R1 no longer requires a number of steps i.e. putting the tablespace in READ ONLY mode, followed by data file offline action.
In 12c R1, a data file can be renamed or moved online simply using the ALTER DATABASE MOVE DATAFILE SQL statement. While the data file is being transferred, the end user can perform queries, DML and DDL tasks. Additionally, data files can be migrated between storages e.g. from non-ASM to ASM and vice versa.
Rename a data file:
SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users01.dbf' TO '/u00/data/users_01.dbf';
Migrate a data file from non-ASM to ASM:
SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '+DG_DATA';
Migrate a data file from one ASM disk group to another:
SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_02';
Overwrite the data file with the same name, if it exists at the new location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE;
Copy the file to a new location whilst retaining the old copy in the old location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;
You can monitor the progress while a data file being moved by querying the v$session_longops dynamic view. Additionally, you can also refer the alert.log of the database where Oracle writes the details about action being taken place.
2. Online migration of table partition or sub-partition
Migration of a table partition or sub-partition to a different tablespace no longer requires a complex procedure in Oracle 12c R1.
In a similar way to how a heap (non-partition) table online migration was achieved in the previous releases, a table partition or sub-partition can be moved to a different tablespace online or offline.
When an ONLINE clause is specified, all DML operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. In contrast, no DML operations are allowed if the partition|sub-partition is moved offline.
Here are some working examples:
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name;
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name UPDATE INDEXES ONLINE;
The first example is used to move a table partition|sub-partition to a new tablespace offline. The second example moves a table partition/sub-partitioning online maintaining any local/global indexes on the table. Additionally, no DML operation will get interrupted when ONLINE clause is mentioned.
Important notes:
⦁ The UPDATE INDEXES clause will avoid any local/global indexes going unusable on the table.
⦁ Table online migration restriction applies here too.
⦁ There will be locking mechanism involved to complete the procedure, also it might leads to performance degradation and can generate huge redo, depending upon the size of the partition, sub-partition.
3. Invisible columns
In Oracle 11g R1, Oracle introduced a couple of good enhancements in the form of invisible indexes and virtual columns. Taking the legacy forward, invisible column concepts has been introduced in Oracle 12c R1. I still remember, in the previous releases, to hide important data –columns from being displayed in the generic queries– we used to create a view hiding the required information or apply some sort of security conditions.
In 12c R1, you can now have an invisible column in a table. When a column is defined as invisible, the column won’t appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. It is pretty easy to add or modify a column to be invisible and vice versa:
SQL> CREATE TABLE emp (eno number(6), ename name varchar2(40), sal number(9) INVISIBLE);
SQL> ALTER TABLE emp MODIFY (sal visible);
You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns. A virtual column or partition column can be defined as invisible too. However, temporary tables, external tables and cluster tables won’t support invisible columns.
4. Multiple indexes on the same column
Pre Oracle 12c, you can’t create multiple indexes either on the same column or set of columns in any form. For example, if you have an index on column {a} or columns {a,b}, you can’t create another index on the same column or set of columns in the same order. In 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different. However, only one type of index is usable/visible at a given time. In order to test the invisible indexes, you need to set the optimizer_use_use_invisible_indexes=true.
Here’s an the example:
SQL> CREATE INDEX emp_ind1 ON EMP(ENO,ENAME); SQL> CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME) INVISIBLE;
5. DDL logging
There was no direction option available to log the DDL action in the previous releases.
In 12cR1, you can now log the DDL action into xml and log files. This will be very useful to know when the drop or create command was executed and by who. The ENABLE_DDL_LOGGING initiation parameter must be configured in order to turn on this feature.
The parameter can be set at the database or session levels. When this parameter is enabled, all DDL commands are logged in an xml and a log file under the $ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. An xml file contains information, such as DDL command, IP address, timestamp etc. This helps to identify when a user or table dropped or when a DDL statement is triggered.
To enable DDL logging
SQL> ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;
The following DDL statements are likely to be recorded in the xml/log file:
⦁ CREATE|ALTER|DROP|TRUNCATE TABLE
⦁ DROP USER
⦁ CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE
What Is a Domain?
A WebLogic Server administration domain is a logically related group of WebLogic Server resources. Domains include a special WebLogic Server instance called the Administration Server, which is the central point from which you configure and manage all resources in the domain. Usually, you configure a domain to include additional WebLogic Server instances called Managed Servers. You deploy Web applications, EJBs, and other resources onto the Managed Servers and use the Administration Server for configuration and management purposes only.Multiple Managed Servers can be grouped into clusters, which enable you to balance loads and provide failover protection for critical applications, while using a single Administration Server simplifies the management of the Managed Server instances.
System administration in WebLogic Server is based on the J2EE Management model, in which each instance of a Web application server resource type is represented by a J2EE Managed Object (JMO). In WebLogic Server, each JMO is a wrapper for a corresponding MBean. You accomplish many WebLogic Server administration tasks by accessing MBeans, either directly using JMX, or through a JMX client like the WebLogic Administration Console or WebLogic Scripting Tool (WLST). For more information, see Monitoring and Managing with the J2EE Management APIs and WebLogic Server MBean Reference.
Run Node Manager on Each Machine that Hosts Managed Servers
To take advantage of Node Manager capabilities, you must run a Node Manager process on each machine that hosts Managed Servers. You can manage multiple Managed Servers on a single machine with one Node Manager process—in Figure 4-1, the two Managed Servers on Machine C can be controlled by a single Node Manager process.
You cannot use Node Manager to start or stop an Administration Server. In a production environment, there is no need to run Node Manager on a machine that runs an Administration Server, unless that machine also runs Managed Servers. In a development environment, you may wish to run Node Manager on a machine that hosts an Administration Server and one or more Managed Servers, because doing so allows you to start the Managed Servers using the Administration Console.
APPLTMP Identifies the directory for Oracle E-Business Suite temporary files. The default is $INST_TOP/appltmp on UNIX.
APPLPTMP Identifies the directory for temporary PL/SQL output files. The possible directory options must be listed in the init.ora parameter utl_file_dir.
Perm -permanent size,metadata of java classes
Heap -programs uses
Eden ->new object created
Gc-eden full
Young generation minor gc
Old generation -major gc
Tune time spent in gc 5% of jvm
Heap size determines how often gc should run
Database upgrade(High Level)
Take invalid countApply interoperability patches
Install oracle home
Runinstaller(install database software only)
Run root.sh
Install examples
Create nls/data/9idata directory(On the database server node, as the owner of the Oracle RDBMS file system and database instance, run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.)
Apply database patches
Run utlu112i.sql on old db from new oracle home(Checks tablespace,parameters to enable,Database components(OLAP,RAC,OLAP catalog,EM repository,timezone,req to gather stats)
Perform recommendations
Oratab entries
Run DBUA
Gather stats
Workflow sync
Errors
ERROR in setting Environment variable ADJREOPTS
Fix: Copied the jre from the backed up appsutil and then ran autoconfig
Application Upgrade 12.2 high level
⦁ Pre Upgrade preparation steps⦁ Main upgrade using Main Upgrade Driver for R12.2.0 (merged with pre-installed Oracle E-Business Suite Consolidated Upgrade Patch for R12.2.0)
⦁ Online Patching Enablement
⦁ Applying the latest R12.AD.C.Delta.n and R12.TXK.C.Delta.n
⦁ Applying 12.2.n Release Update Pack (RUP)
ONLINE Patching Enablement
ONLINE Patching Enablement
Initialize the Run File System environment:
source <RUN APPL_TOP>/<Instance SID>_<hostname>.env
Create the online patching log file location and set it as the current directory:
mkdir $LOG_HOME/appl/op
Apply latest patch as per note 'Patching Readiness Report in Oracle E-Business Suite Release 12.2
(Doc ID 1531121.1)' using adpatch options=hotpatch - patch 18824534
cd $LOG_HOME/appl/op
sqlplus system @$AD_TOP/sql/ADZDPAUT.sql
Review report
sqlplus system @$AD_TOP/sql/ADZDPMAN.sql
Review report
sqlplus system @$AD_TOP/sql/ADZDPSUM.sql
Review report
mv adzdpsum.txt adzdpsum_pre_dbprep.txt
mv adzdpman.txt adzdpman_pre_dbprep.txt
mv adzdpaut.txt adzdpaut_pre_dbprep.txt
Run sqlplus apps @$AD_TOP/sql/ADZDDBCC.sql
Review report
mv adzddbcc.lst adzddbcc.lst_pre_dbprep.txt
Run 'sqlplus apps/apps @$AD_TOP/sql/ADZDSHOWAD' and ensure there are no AD_ZD* invalid objects
Review report
Check free tablespace size : perl $AD_TOP/bin/adzdreport.pl apps
Add space to SYSTEM and APPS_TS_SEED tabalespace. Ensure system has 25G free and APPS_TS_SEED has 5Gb free space
Change the max_dump file size parameter to 'unlimited'
Run report: sqlplus apps @$AD_TOP/sql/ADZDEXRPT.sql
mv adzdexrpt.txt adzdexrpt.txt_pre_dbprep.txt
Download and apply the Online Patching Enablement patch: 13543062:R12.AD.C.
Use Autopatch in hotpatch mode to apply the patch. The patch has to be applied on RUN file system.
sqlplus apps @$AD_TOP/sql/ADZDSHOWDDLS.sql
select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;
Connect to sqlplus as 'apps' and run the following:
exec sys.utl_recomp.recomp_parallel
cd $LOG_HOME/appl/op
sqlplus system @$AD_TOP/sql/ADZDPAUT.sql
Review report
sqlplus system @$AD_TOP/sql/ADZDPMAN.sql
Review report
sqlplus system @$AD_TOP/sql/ADZDPSUM.sql
Review report
mv adzdpsum.txt adzdpsum_post_dbprep.txt
mv adzdpman.txt adzdpman_post_dbprep.txt
mv adzdpaut.txt adzdpaut_post_dbprep.txt
Run sqlplus apps @$AD_TOP/sql/ADZDDBCC.sql
Review report
mv adzddbcc.lst adzddbcc.lst_post_dbprep.txt
Run report: sqlplus apps @$AD_TOP/sql/ADZDEXRPT.sql
mv adzdexrpt.txt adzdexrpt.txt_post_dbprep.txt
Take Snapbackup
Weblogic password change
cd $DOMAIN_HOME/security
$ java weblogic.security.utils.AdminAccount <username> <password> .
Standby_log_management
ADOP Phases
https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531065.htm
Weblogic Stuck threads
Stuck threads are nothing but long running threads.Whenever a particular thread keeps running more than the defined threshold, it is classified under stuck threads in weblogic.
In order to find the root cause of the issue, we need to perform the below:
⦁ Verify what operation it does.
⦁ If it is database activity, check which query is running long and tune the query if required
⦁ If it is logon activity ,then check how the authentication is happening and where it actually stuck.
⦁ If it is some other jms or deployment issue, get a thread dump and analyse it using various tools to figure out the actual cause.
Hope this helps!!
https://beginner-sql-tutorial.com/sql-query-tuning.htm
https://developer.rackspace.com/blog/change-the-oracle-apps-and-weblogic-password/
EBS 12.2 login flow
EBS Architecture
When a HTTP request is made for EBS, the request is received by the Oracle HTTP Server (OHS).
When the configuration of OHS is for a resource that needs to be processed by Java, such as logging into EBS, the OHS configuration will redirect the request to the Web Logic Server (WLS) Java process (OACore in this case).
WLS determines the J2EE application that should deal with the request, which is called "oacore".
This J2EE application needs to be deployed and available for processing requests in order for the request to succeed. The J2EE application needs to access a database and does this via a datasource which is configured within WLS.
Login HTTP headers
When the EBS login works OK, the browser will be redirected to various different URLs in order for the login page to be displayed. The page flow below shows the URLs that will be called to display the login page:
/OA_HTML/AppsLogin
EBS Login URL
/OA_HTML/AppsLocalLogin.jsp
Redirects to local login page
/OA_HTML/RF.jsp?function_id=1032925&resp_id=-1&resp_appl_id=-1&security_group_id=0&lang_code=US&oas=3TQG_dtTW1oYy7P5_6r9ag..¶ms=5LEnOA6Dde-bxji7iwlQUg
Renders the login page
The URLs after the user enters username and password, then clicks the "login" button are shown below:
/OA_HTML/OA.jsp?page=/oracle/apps/fnd/sso/login/webui/MainLoginPG&_ri=0&_ti=640290175&language_code=US&requestUrl=&oapc=2&oas=4hoZpUbqVSrv9IE0iJdY1g..
/OA_HTML/OA.jsp?OAFunc=OANEWHOMEPAGE
/OA_HTML/RF.jsp?function_id=MAINMENUREST&security_group_id=0
Renders user home page
Once the users home page is displayed, the logout flow also redirects to several different URL before returning to the login page:
/OA_HTML/OALogout.jsp?menu=Y
Logout icon has been clicked
/OA_HTML/AppsLogout
/OA_HTML/AppsLocalLogin.jsp?langCode=US&_logoutRedirect=y
Redirects to the login page
/OA_HTML/RF.jsp?function_id=1032925&resp_id=-1&resp_appl_id=-1&security_group_id=0&lang_code=US&oas=r6JPtR7-a4n5U2H3--ytEg..¶ms=1JU-PCsoyAO7NMAeJQ.9N6auZoBnO8UYYXjUgSPLHdpzU3015KGHA668whNgEIQ4
Renders login page again
https://techgoeasy.com/login-flow-basic-troubleshooting-r12-2/(Sourced from this blog)
AUG
3
EBS login flow
Before jumping into the flow, lets brush up the role of APPLSYSPUB, GUEST, APPLSYS and APPS user.When we login to applications,initially oracle applications connect to public schema, APPLSYSPUB. This schema has sufficient privileges to perform the authentication of an Applications User (FND user), which includes running PL/SQL packages to verify the username/password combination and the privilege to record the success or failure of a login attempt.
The public ORACLE username and password that grants access to the Oracle E-Business Suite initial sign-on form. The default is APPLSYSPUB/PUB.
Once we change the APPLSYSPUB password must propagate the change to application tier configuration files. If the instance is Autoconfig enabled, must edit the CONTEXT file on each tier prior to running Autoconfig.
In the CONTEXT file, locate the autoconfig variable “s_gwyuid_pass” and set it to the new password, then run AutoConfig in each applications nodes.
When Autoconfig is not being used:
If you are not using Autoconfig you must manually edit the following configuration files :
1) FND_TOP/resource/appsweb.cfg
2) OA_HTML/bin/appsweb.cfg
3) FND_TOP/secure/HOSTNAME_DBNAME.dbc
To change password of APPLSYSPUB with FNDCPASS:
$FNDCPASS APPS/[apps_pass] 0 Y SYSTEM/[system_pass] ORACLE APPLSYSPUB [new_passs].
0 & Y are flags for FNDCPASS
0 is request id (request ID 0 is assigned to request ID's which are not submitted via Submit Concurrent Request Form)
'Y' indicates that this method is directly invoked from the command-line and not from the Submit Request Form.
All application tier processes (Apaches) must be restarted following the password change.
Role of GUEST user/schema in Oracle Applications:
GUEST is a dummy schema.
By default it has ORACLE as password.
GUEST/ORACLE password is present in DBC file at $FND_TOP/secure directory as well as at $FND_TOP/secure/SID_hostname directory.
If a user logs in without any role mappings, the user will get the Guest role, which has a default permission of "R".
GUEST user is used by JDBC Drivers and Oracle Self Service Web Applications like istore, irecruitment, iprocurement, ipayables, ireceivables etc to make initial Connection.
Role of APPLSYS & apps user/schema in Oracle Applications:
APPLSYS user is same as other oracle users like AP, AR, GL etc which hold their set of tables, views etc. In the same manner APPLSYS Account holds its set of tables like FND_USER and FND_APPLICATION, AD_APPLIED_PATCHES etc.
Applsys schema has applications technology layer products like FND and AD etc.
Apps is a universal schema, it has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc).
APPS is central Schema which holds synonyms for all other Users Database Objects.
Note: APPLSYS and APPS should have same password.
Reason why these contains same password.
Both apps & applsys need to have same password because when you sign on to apps, initially it connects to a public schema called APPLSYSPUB. This validates AOL name and password that we enter (operations/welcome). Once this is verified we select responsibility, this is validated by APPLSYS schema and then it connects to apps schema.
During signon process it uses both applsys and apps, hence this expects both the password to be identical. If the password for applsys & apps are not identical (Different) Try changing apps password to something else and try to login, the validation at the last stage would fail. This would result in failure of application login.
Difference B/W APPLSYSPUB & GUEST:
APPLSYSPUB/PUB - is DB user which is used by any utility to retrieve APPS schema password for further logins.GUEST/ORACLE - is EBS user with no or max limited privileges to execute authorization function.That is why Guest user cannot be end dated.
Now lets jump into the login flow:
1.Webserver receives the user request for the particular port number. Web server Binds the request with the port number.
2.Based on the cookie webserver serves the request by sending login page
3.The application user name and password is entered by the user(operations/welcome)
4.DB connection is established using APPLSYSPUB user. This user “APPLSYSPUB” is having access to fnd_user view and a small set of other public tables needed to establish the initial connection.
5.Once DB connection is established FND_USER table is used to get ENCRYPTED_FOUNDATION_PASSWORD.
6.Using GUEST/ORACLE password combination and ENCRYPTED_FOUNDATION_PASSWORD string obtained from 2 above, we get Apps schema password.
7.Internally it tries to connect to Apps schema with the password retrieved in step 3 above. If the connection fails, then
a)The GUEST username password is incorrect
b)The FNDNAM (APPS) environment variable is set incorrectly.
c)Some other problem prevented a connection
8.Using Apps password obtained in Step 3 and ENCRYPTED_USER_PASSWORD string from FND_USER table, password for Application user (e.g. SYSADMIN) is obtained.
9. The Application user password obtained in Step 5 is compared to the application user password entered by user in login screen. If both passwords match then user is allowed to get into self-service.
10. A list of responsibilities assigned to that user is shown. The user picks one of the responsibilities listed.
11. The responsibility and application name is validated by connecting to Applsys schema(fnd_Responsibility, fnd_application…….)
Note: Here we should remember the difference between applsys and apps schema. Applsys schema contains ad,fnd objects whereas apps schema is a global schema contains all application objects.
12. Once the application is validated , Connecting to apps schema to get access application tables. This is the reason why we should have apps and applsys passwords should be same
13. Once a particular form is selected the .dbc file is accessed to get the information about the GUEST username/pwd
Internal Concurrent Manager
The Internal Concurrent Manager can run on any node, and can activate and deactivate concurrent managers on the same or other nodes. Since the Internal Concurrent Manager must be active at all times, it needs high fault tolerance. To provide this fault tolerance, parallel concurrent processing uses Internal Monitor Processes.Internal Monitor Processes
The sole job of an Internal Monitor Process is to monitor the Internal Concurrent Manager and to restart that manager should it fail. The first Internal Monitor Process to detect that the Internal Concurrent Manager has failed restarts that manager on its own node.
Only one Internal Monitor Process can be active on a single node. You decide which nodes have an Internal Monitor Process when you configure your system. You can also assign each Internal Monitor Process a primary and a secondary node to ensure fail over protection.
Internal Monitor Processes, like concurrent managers, can have assigned work shifts, and are activated and deactivated by the Internal Concurrent Manager.
FNDFS or the Report Review Agent (RRA) is the default text viewer within Oracle Applications, which allows users to view report output and log files. Report Review Agent is also referred to by the executable FNDFS. The default viewer must be configured correctly before external editors or browsers are used for viewing requests.
FNDSM is the Service manager. FNDSM is executable & core component in GSM ( Generic Service Management Framework ). You start FNDSM services via APPS listener on all Nodes in Application Tier.
There are certain scenario where we can't see the output of Concurrent Requests. It just gives an error.
Then check the fndwrr.exe size in production and compare it with Test Instance.
fndwrr.exe is present in $FND_TOP/bin
If there is any difference then relink the FNDFS executable.It might not be in sync with the binaries.
Command for relinking
adrelink.sh force=y "fnd FNDFS"
Autoconfig
All seasoned Oracle Apps DBAs know that Autoconfig is the master utility that can configure the whole E-Business Suite Instance. In E-Business Suite releases 11i, 12.0 and 12.1 running Autoconfig recreated all the relevant configurations files used by Apache server. If the context file has the correct settings, then configuration files should include the correct setting after running Autoconfig. This is not the case anymore in Oracle E-Business Suite 12.2. Some of the Apache config files are under fusion middleware control now, namely httpd.conf, admin.conf and ssl.conf. All other Apache config files are still under Autoconfig control. But these 3 critical config files include the main config pieces like Webport, SSL port etc.So if you have to change the port used by EBS instance, then you have to log into the Weblogic admin console and change port there and then sync context xml file using adSyncContext.pl. This adSyncContext.pl utility will get the current port values from Weblogic console and update the xml with new port values. Once the context xml file syncs, we have to run Autoconfig to sync other config files and database profile values to pickup new webport
Similarly, if you want to change the JVM augments or class path, you have run another utility called adProvisionEBS.pl to make those changes from command line or login to the Weblogic admin console to do those changes. Interestingly, few of the changes done in Weblogic admin console or fusion middleware control are automatically synchronized with context xml file by the adRegisterWLSListeners.pl script that runs in the background all the time. But Apache config file changes were not picked by this script, so Apache changes had to be manually synchronized
The ICM starts up a Service Manager on each node that is enabled for concurrent processing, by instructing the node's Applications Listener (which is dedicated to Concurrent Processing) to spawn a process running the Service Manager executable (FNDSM).
The Applications Listener must be configured to source the Oracle E-Business Suite environment file before FNDSM is spawned.
Following startup, the Service Manager acts as an agent of the ICM to start and stop concurrent managers on that node, according to their defined work shifts.
Apps Listener usually running on All Oracle Applications 11i Nodes with listener alias as APPS_$SID is mainly used for listening requests for services like FNDFS and FNDSM.
If you're looking for Oracle DBA Interview Questions for Experienced or Freshers, you are at right place. There are a lot of opportunities from many reputed companies in the world. According to research, Oracle DBA has a market share of about 0.7%. So, You still have an opportunity to move ahead in your career in Oracle DBA Development. Mindmajix offers Advanced Oracle DBA Interview Questions 2019 that helps you in cracking your interview & acquire dream career as Oracle DBA Developer.
Oracle DBA Vs Oracle Developer
Feature Oracle DBA Oracle Developer
Key Roles Managing Databases Development(Coding)
Type of Tasks Streamlined Wide Range
Work Environment Database maintenance Database development
Area of work Backend database management Front-end development
Few other tasks Backup, recovery, server connectivity, etc Coding, designing UI, etc
Q1. List four possible ways (direct or indirect) to execute an SQL query against an Oracle Database?
⦁ Using the SQL*Plus command line tool. With this tool, you can directly execute SQL commands.
⦁ Using a GUI (Graphical User Interface) tool like SQL Developer. You can directly execute SQL commands with such tools.
⦁ Using Oracle Enterprise Manager. This is an indirect way of executing an SQL query. When you perform certain operations with Oracle Enterprise Manager, they are converted to SQL queries implicitly and these SQL queries are executed against the database.
⦁ Writing your own program. This is not a conventional way of executing your queries but actually it is widely used. Any web or windows program that uses Oracle database at backend, executes SQL queries. These programs are written using a programming language like .NET or JAVA and they use a driver to connect to database.
Q2. What is SQL*Plus? How can one acquire it and what kind of operations can be performed with it?
⦁ SQL*Plus is a command line tool developed by Oracle Corporation.
⦁ It is freely distributed. It is shipped with Oracle client installations or Oracle database installations as a default. So, if Oracle client or Oracle database software is installed on a computer, you can find it under “$ORACLE_HOME/bin/” directory. The name of the executable is “sqlplus” on Linux systems and “sqlplus.exe” on Microsoft Window Systems.
⦁ You can connect to an Oracle database with it. Once connected, you can execute Oracle commands or SQL queries against the connected database. SQL*Plus has also its own commands for formatting the output so that you can display the results in a meat way.
Q3. A user is logged on to a Linux server as root where Oracle database is running. The Oracle is installed at “/uo 1/app/oracle/product/11.2.0.4/dbhome” and the name of the SID is “ORCL”. The user wants to connect to the database locally using operating system authentication with SYSDBA privileges. Show the command that the user has to execute.
⦁ First he needs to switch to “oracle” user:# su – oracle
⦁ Later he needs to set required environment variables:
1
2
3 $ export ORACLE_SID=ORCL
$ export
ORACLE_HOME=/uo1/app/oracle/product/11.2.0.4/dbhome
⦁ Finally he needs to execute the following command to connect to database:
⦁
1 $/uol/app/oracle/product/11.2.0.4/dbhome/bin/sqlplus/ as sysdba
Q4. In our organization, we’re using an Oracle database whose version is 11.2.0.4. Explain what each digit shows.
Ans. “11”: This first digit shows the major database version. Oracle usually publishes a major release once a 4 year. This digit is usually followed by a character describing the nature of the release. For example: 9i (internet), 10g (grid), 11g (grid), 12c (cloud).
“2”: This second digit shows the maintenance release number of the software. Oracle publishes the major release as maintenance release 1 and then usually publishes a second maintenance release during the lifetime of the software. New features are added to database Software with maintenance releases.
“o”: This third digit is Fusion Middleware Number. This will be o for database software.
“4”: This fourth digit is called Component-Specific Release Number and it shows the path set update that was applied to the software. Patch set updates are published 4 times a yearby Oracle and as you apply them to your database software, this fourth digit advances.
Q5. You’re at a client’s office and you are expected to solve a problem in their database. The client is not sure about their database version and you want to find out the version of their existing database. Describe three different methods you can use to find the version of database software.
⦁ You can find the version by connecting to the database with SQL*Plus. SQL*Plus will print the name and the version of the database software once you’re connected to the database. A sample output will look like below:
“Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options”
⦁ You can find the version by querying the “vsversion” view. You can execute the SQL query below to find the version of the database:
1
2
3
4
5
6
7
8 SQL: SELECT * FROM v$version;
A sample output would look like below:
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – Production
PL/SQL Release 11.2.0.4.0 – Production
CORE 11.2.o.4.o Production
TNS for Linux: Version 11.2.0.4.0 – Production
NLSRTLVersion 11.2.0.4.0 – Production
⦁ You can find the version from Enterprise Manager. If you logon to Oracle Enterprise Manager, the version of the database software will be listed at the home page under “General” web part.
Q6. Your client said that he forgot the password for “SYSTEM” user of his database and he no longer could connect. How would you recover this admin password?
Ans.
⦁ If there are other users who have “DBA” privileges, you can connect with those users and change the password for “SYSTEM” user. The users who have DBA privileges have the privileges to change any user’s password. This option is the easiest method but this may not be the case in all scenarios.
⦁ If there are no other users with “DBA” privileges then the only way to connect to the database isto connect using operating system privileges. The oracle software runs under a specific user at operating system. This user is usually named “oracle”. Also, there needs to be a user group that “oracle” user belongs. This user group is usually named “dba”. The operating system users who belong to “dba” group can connect to database with “SYSDBA” privileges. So, you need to ask the system administrator to logon to server as “oracle” user or any user who belongs to this “dba” group. Once logged on to operating system, you can connect to database locally using operating system authentication with SYSDBA privileges. After connecting to the database, you can change the reset the password for this system user.
Q. What is a password file and why is it needed?
Ans. Passwords for database users are stored in the data dictionary of the database. When a user wants to log into the database, the username and password provided by the user is checked against the values stored in the database. If the username and password match, the user is granted access to database. The data dictionary is part of the database and it will be accessible as long as the database is open. The passwords for administrators are stored in the dictionary as well.
When the database is closed, the data dictionary will be inaccessible. There needs to be a mechanism for administrators to logon to database even when it is closed, because it is one of the administrator’s tasks to start up a down database. A password file is a separate operating system file that is stored on disk outside of the database. The username and password for the users who have SYSDBA or SYSOPER privileges are stored in it. Administrators who have those privileges are authenticated using this password file even when the database is down.
Q6. You want to find out how many users are defined in the password file and what privileges those user have. How would you accomplish this?
Ans. You need to query the “v$pwfile_users” view to get information about the existing users in the password file. Execute the SQL query below:
Sql>SELECT * FROM v$pwfile_users;
The query above will return four columns for each user in the password file. The column names are USERNAME, SYSDBA, SYSOPER and SYSASM.
⦁ The USERNAME column shows the username of the user in the password file.
⦁ The SYSDBA column shows whether the user has SYSDBA privileges or not.
⦁ The SYSOPER column shows whether the user has SYSOPER privileges or not.
⦁ The SYSASM column shows whether the user has SYSASM privileges or not.
Q7. What would be the main responsibilities of an Oracle DBA in an organization?
Ans. The main duty of an Oracle DBA isto keep the Oracle Databases of the organization up and running. This may involve installing and configuring a database from scratch.
On a running system, the DBA will be the only privileged person who can shut down and start up the database.
The DBA will create new users and manage the privileges of each user.
He will take regular backups to ensure that data is safe. In case of a disaster, he will be responsible of restoring the database from backups. He will have to do monitor the space usage and do capacity planning for the database.
He will be responsible for enforcing security policies. He will have to monitor database activities. He will have to tune the database so that it works at an acceptable speed.
He is expected to follow the latest patches and apply them when applicable.
Q8. How does an Oracle DBA role differ from an Oracle Developer role in an organization? Are there any similarities between these too?
Ans. An Oracle developer is mainly responsible for developing backend applications. They do data modelling according to business rules. They design tables, create indexes and other types of constraints. They are expected to know SQL and PL/SQL. The develop procedures using this languages. However, the Oracle developers are not expected to administer the database software itself.
On the other side, an Oracle DBA’s main duty is to administer the database which involves tasks like doing maintenance to keep the databases up and running, taking backups, enforcing security policies etc. DBAs are not primarily assigned to develop code. DBAs are supposed to have a good knowledge of SQL and PL/SQL like a developer as these are also required for administering the database.
According to the structure of the organization, DBAs might also be assigned development tasks or at least assist the developers where necessary.
Q9. There are 10 identical servers and you want to install Oracle Database on each of them. What would you use to automate the installation process?
Ans. If you are going to do batch installations, it is best to do it with Oracle Universal Installer in silent mode. For single installations.it is best to start installer in “interactive mode”and set installation options at each window. However in batch installations, this will take long. You need to do the installations in “silent” mode with a “response file”. In silent installation, you start the Oracle Universal Installer from a command prompt and specify the location of the “response file”.
The installation files and the response file can be shared among the servers via NFS so that you won’t have to copy the setup files to each server.
Q10. You want to create a response file to speed up the installation of databases. How would you prepare a response file?
Ans. A response file is a plain text file, where options to create a database are stored. It is possible to create it manually from scratch but that would take long and would be erroneous.
Installation media comes with a template response file. It is rather easier to customize it manually. This file also contains notes about the parameters.
However the easiest and most reliable way to create a response file is using Oracle Universal Installer. If you start the installer in “record” mode, every option you choose at each step is automatically recorded in a response file in correct format. After the installer completes in “record” mode, you’ll have a complete response file with all the options set in it.
Q11. When creating a database with SQL script, what would you specify in the script?
Ans. It is also possible to create a database via an SQL script. In this script I would specify:
⦁ Name of the database
⦁ Password of the SYS user
⦁ Password of the SYSTEM user
⦁ At least three online redolog groups. I would also specify at least two members for each redolog group.
⦁ Character set and the national character set of the database.
⦁ Location and size of the SYSTEM and SYSAUXtablespace. These tablespaces will be used for holding system data.
⦁ I would specify a normal tablespace to use as the default tablespace of the database.
⦁ I would specify a temporary tablespace to use as the default temporary tablespace of the database.
⦁ I would specify an undo tablespace.
Q12. What makes up an Oracle Instance?
Ans. An instance is made up of a shared memory region on RAM called System Global Area (SGA) and background processes.
The system global area is a shared memory, which means it can be accessed by multiple processes. This are holds data which is required by the instance to operate.
The background processes are operating system processes and each process has a specific responsibility in the instance.
The System Global Area and background processes are created when the instance is “started”. When the instance is “shut down”, the processes are killed and the shared memory region is “released” back to operating system.
Q13. What constitutes an Oracle Database?
Ans. An Oracle database is resides on disk and this is permanent. It is composed of files that are stored on disk. These files can be categorized into three types:
⦁ DataFiles: These files hold “user” data or “system” data. Any data that belongs to an application is an example of “user” data. The “data dictionary” of the database is an example of “system” data.
⦁ OnlineRedo Log Files: These files hold the “change” records. Any change, which will be made to a data file, is first written to online redo log files.
⦁ ControlFiles: These files are relatively small but they are essential for a database. They hold information about the physical structure of the database like location of data files, online redo log files etc.
Q14. Which tools can you use to start up an Oracle database?
Ans. You can start up a database with three tools.
⦁ SQL*Plus: This is the most widely used option. You first connect to an idle instance with SQL*Plus and then startup the instance with “startup” command.
⦁ Oracle Enterprise Manager: This is another way of starting up a database. You can logon to Oracle Enterprise Manager even if the database is stopped. OEM will detect the status of the down database and will present you “Startup” button. You can startup the database by clicking this button.
⦁ RMAN: This is rather a less used tool for starting up a database but it is possible to startup a database from Recovery Manager command line.
Q15. During startup of a database, at which order does Oracle software search a parameter file?
Ans. A parameter file holds instance parameters which govern how an instance operates. In order to startup an instance, Oracle needs to locate this file.
The search order is as below: /dbs/spfile.ora – This is an server parameter file and this is the first place that oracle will look for. SID- is the service identifier of the instance.
<$ORACLE_HOME-/dbs/spfile.ora -If Oracle cannot find the file in the first location, it will search this file. This is again a server parameter file.
/dbs/init.ora – This is a parameter file and it is plain text. If Oracle cannot find the two file listed above, it will search for this file. This is the last location to search.
Q16. At what stages does an instance pass while starting up?
Ans. You can startup a database with the modes below:
⦁ NOMOUNT: This is the first stage. At this mode the instance is started.
⦁ MOUNT: This is the second stage. At this mode, the instance is started and the database is mounted. However, the database is not open so you cannot still access data. However you can perform several maintenance tasks at this stage.
⦁ OPEN: This is the final stage. The database is open and all the data is accessible. The default open mode is “read/write” which means you can read data or write to it. However, it is also possible to open itin “read only” mode where you can only read data but cannot change it.
Q17. You want to do maintenance on your database but during the maintenance period, you don’t want any user to be able to connect to the database. How would you accomplish this?
Ans. When a database is open, any user with “CREATE SESSION” privilege can make a connection. However it is possible to open the database in “restricted” mode. When a database is open in restricted mode, only users with “RESTRICTED SESSION” privilege can make a connection to the database. By default, only DBAs have “RESTRICTED SESSION” privilege and it should not be granted to regular users.
Opening a database in “restricted” mode is a good way to prevent regular users from accessing the database during maintenance.
Q18. Your database is open. You don’t want to interrupt currently connected users but you want to temporarily disable further logons. What would you do to achieve this and how would you revert the database back to normal state after that?
Subscribe to our youtube channel to get new updates..!
Ans. I would put the database in “restricted mode”. While in restricted mode, only users with “RESTRICTED SESSION” privilege can make a connection. I would run the below command to put database in restricted mode:
Sql> alter system enable restricted session;
After executing this command regular users won’t be able to logon to the database. Once I want to revert the database to normal, I execute this command:
Sql>alter system disable restricted session;
Q19. What are the types of shutdown modes of an Oracle database?
⦁ Normal: In this mode, no new connections are allowed and the database is closed after all the sessions disconnect themselves.
⦁ Immediate: No new connections are allowed and the existing active transactions are rolled back. Changes made by an active transaction are lost in this option.
⦁ Transactional: No new connections are allowed and Oracle waits until all active transactions are completed.
⦁ Abort: This happens immediately however the database is not shutdown cleanly. Database will have to perform instance recovery next time it is started. This option should not be used in regular activities.
Q20. The data files of your database reside on a storage system. You want to take a snapshot of the storage so that you can use it backup purposes. You also want to ensure that no data is written to data files while the snapshot is being taken. Is it possible to accomplish this while the database is open?
Ans. Yes, it is possible to stop all I/O activity while the database is open. Normally, when a database is open, there will be constant I/O to online redolog files or data files. Even if the database is idle, there is no guarantee that database will not write anything to files during snapshot.
However, if you “suspend” the database, Oracle will halt I/O operations to these datafiles until it is reverted back to normal mode. So, you should “suspend” the database, take the snapshot of the disk and then put the database back in normal mode immediately after that.
Q21. What kind of information can be given while creating a sequence?
⦁ Sequence Name: This is the name of the sequence. It should be unique inside the schema.
⦁ Start With: This is the number that the sequence will start from.
⦁ Increment By: This number shows how much the sequence will increment at each move.
⦁ Nocycle: This determines whether the sequence will start from the beginning once it reaches the end.
⦁ Nocache: This determines how much next sequence number willbe cached in SGA. Nocache means no next sequence will be cached.
Q22. You want your database to start automatically, after a reboot of the server. How would you do that?
Ans. In default configuration, Oracle database will not automatically start after the server reboots. You’ll have to start it manually after each reboot. You’ll usually want it to start automatically. There are two methods to accomplish this:
⦁ Using Oracle Restart: “Oracle Restart” is a feature of Oracle High Availability Service (OHAS). You need to install “Grid Infrastructure” to enable “Oracle Restart” feature. Using “Oracle Restart” is the recommended way.
⦁ Using Your Own Script: It is also possible for you to write your own “bash” script to start the database and place that script in the startup of the operating system.
Q23. Which components of your database environment can be protected by an “Oracle Restart” configuration?
⦁ Database Instances and Automatic Storage Management (ASM): Database instances and ASM instances will be restarted if they crash somehow.
⦁ Oracle NET Listener: Oracle NET Listener will be started if it crashes and stops listening for incoming connection.
⦁ ASM Disk Groups: Oracle Restart will mount ASM Disk groups if they are dismounted.
⦁ Database Services: Non-default database services will be started by Oracle Restart feature.
⦁ Oracle Notification Services (ONS): This is another Oracle component that can be protected by Oracle Restart.
Q24. Explain the difference between “shared server” architecture and “dedicated server” architecture?
Ans. When a user connects to a database, he sends SQL queries to the database to execute. These SQL queries are executed by a “server process” and the result is returned back to the user. In “dedicated server” architecture, the instance will create one server process for each connected user.
That process will be “dedicated” to that user and will only serve that client.
However in “shared server” architecture, a single server process will serve multiple clients. In shared server architecture, the total memory consumption will be less. However, certain operations like DBA activities can only be performed in dedicated server.
Q25. Explain how “shared server” architecture works.
Ans. In shared server architecture, the clients connect to a “dispatcher” process. This dispatcher is responsible for delivering the SQL requests to the “request queue”.
The shared server process monitors the request queue. When they find an incoming request, they execute this SQL query and place the results in the response queue. The request queue and the response queue reside in the system global area.
The dispatcher processes also monitor response queue. When it receives a result, they deliver the result to the relevant client.
In this architecture, there will be multiple shared server processes and dispatcher processes.
Q26. What are the instance parameters that are used for configuring shared server architecture?
⦁ DISPATCHERS: Astring value which is used to configure dispatchers.
⦁ SHARED_SERVERS: Minimum number of shared server processes that will be present in the server. Also, this number of shared servers is created during startup.
⦁ MAX SHARED_SERVERS: This parameter determines the maximum number of shared server processes that can run at the same time.
⦁ SHARED_SERVER SESSIONS: This parameter specifies the maximum number of sessions that can exist at the same time using shared server connection.
⦁ CIRCUITS: This parameter determines the maximum number of virtual circuits that can exist in the system.
Q27. Explain how the “Database Writer” process works.
Ans. There can be multiple database background processes. They are named as “DBWn” at operating system. This process is responsible for writing “dirty” buffers to disk. When a server process wants to update a data block, it reads the block from disk to buffer cache if the block is not already in the cache and then updates the copy in the cache. The modified database block in the buffer cache is called a “dirty” block.
Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!
Oracle Data Structures & SQL
Q28. Why is index used?
Ans. Index is used to increase the performance of retrieval. We can make use of one or more rows in order to make the index. Index can increase the performance of retrieval and slows down the performance of insertion.
Q29. Who is responsible to update the indexes?
Ans. Oracle automatically maintains and uses indexes and when any change is made in the table data Oracle automatically distributes it into relevant indexes.
Q30. What is the definition of table in Oracle?
Ans. Table is the first level of physical unit in database. Oracle uses tables of a database to store data into rows and columns. Table is the first level of physical unit in database.
Q31. What do you mean by view and what are its types?
Ans. View is a type of virtual table and there is a query attached to every view in order to identify specific rows and columns of the table. Views are read-only as well as read-write.
Q32. In Oracle terminology, what do you mean by tablespace?
Ans. Tablespace is a Logical Storage Unit used to group related logical structures together. It is the logical structure where all the objects of database will be grouped.
Q33. When does the SYSTEM tablespace get created?
Ans. In Oracle every database has a tablespace called SYSTEM and it is automatically created when database is created. It also contains the data dictionary table for the whole data.
Q34. What is the relationship between tablespace and datafiles?
Oracle DBA Certification Training!
Explore Curriculum
Ans. Each tablespace is divided into one or more data files and one and more tablespace(s) are created for each database.
Checkout how to revoke user privileges role in Oracle DBA
Q35. How do we use materialized view?
Ans. Materialized views are objects that have reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
Q36. In Oracle terminology, what is Synonym?
Ans. A synonym is an identifier that can be used to reference another database object in a SQL statement. The types of database objects for which a synonym may be created are a table, view, sequence, or another synonym.
Q37. Different types of synonyms are?
Ans. Synonym types are private and public.
Q38. What you understand by public synonym?
Ans. A public synonym does not belong to any schema. In other words, when any database user can access it, it is called public synonym.
Q39. What you understand by private synonym?
Ans. A private synonym is one that does belong to a specific schema. In other words, when only owner can access it, it is called private synonym.
Q40. What are the advantages of synonyms?
Ans. Synonym is used to mask the original name and owner of an object and provides public access to an object.
Q41. What is a sequence?
Ans. A sequence generates a serial list of unique numbers for numerical columns of a database tables. We can use sequence on columns for data where we want to insert data in sequential manner.
Q42. What you understand by private database link?
Ans. Private database link is created for a specific user. It is only used when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner’s views or procedures.
Q43. What you understand by public database link?
Ans. Database link is a schema object in one database to access objects in another database. When you create database link with Public clause it is available for access to all the users.
Q44. What do you mean by row chaining?
Ans. Row Chaining occurs when the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG, LONG RAW, LOB, etc. Row chaining in these cases is unavoidable.
Q45. What is the definition of extent?
More Batches
Ans. An extent is a set of contiguous blocks allocated in a database. In the Oracle database program, the first set of contiguous blocks, set up automatically when a segment is created, is called the initial extent. After the initial extent has been filled, the program allocates more extents automatically. These are known as next extents.
The total number of extents that can be allocated in a database is limited by the amount of storage space available, or in some cases, by the program used.
Q46. Explain the advantages of using view?
Ans. The view helps provide security, presentation of data in a different perspective and store complex queries.
Q47. What do you mean by datafile?
Ans. An Oracle datafile is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.
Datafile is used to store tables and indexes allocated to the database. Every database consists of one or more data files.
Q48. Explain the properties of data files?
Ans. Each data file can only be associated with only one database and once it is created it can not change its size.
Q49. What do you mean by redo log?
Ans. The most crucial structure for recovery operations is the redo log, which consists of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.
Q50. Main function(s) of redo log is?
Ans. Redo log’s main function is to store all changes made to the database as they occur.
Q51. What are the contents of control file?
Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes:
⦁ The database name
⦁ Names and locations of associated datafiles and online redo log files
⦁ The timestamp of the database creation
⦁ The current log sequence number
⦁ Checkpoint information
Checkout how manage redo log in Oracle DBA
Q52. What are the advantages of control file?
The control file must be available for writing by the Oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult. You might also need to create control files, if you want to change particular settings in the control files.
Q53. What is definition of SQL?
SQL is a database computer language designed for managing data in relational database management systems (RDBMS), and originally based upon relational algebra. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
Q54. What is the use of SELECT statement?
SELECT statement is used to select the set of specific values from a table in a database depending on the various conditions specified in a SQL query.
Q55. How can you compare a part of the name rather than the entire name?
In order to compare part we use LIKE operator which acts like a regex engine for database.
Q56. What is the keyword to get distinct records from a table?
SELECT DISTINCT allows the user to select the distinct values from a table in a database.
Q57. In order to get sorted records from a table, what is the keyword?
ORDER BY keyword is used for sorting the results. It returns the sorted results to your program.
Q58. In order to get total records from a table, what is the keyword?
To find the total number of records in a table, COUNT keyword is used.
Q59. What is definition of GROUP BY?
GROUP BY keyword is an aggregate function such as SUM, MULTIPLE, etc and without this function sum for each individual group values can not be calculated.
Q60. Explain the difference among “dropping a table”, “truncating a table” and “deleting all records” from a table.
⦁ Dropping a table means to drops the invalid indexes. It deletes table definition and records both,
⦁ Truncating means to delete invalid data values automatically. Internally the database marks the table as empty and the deleted records are not recorded in the transaction log.
⦁ Deleting all records, deletes all records but records all details in Transaction log file.
Q61. Different types of SQL statements are?
There are five types of SQL statements
⦁ Data Definition Language,
⦁ Data Manipulation Language,
⦁ Transactional Control,
⦁ Session Control and
⦁ System Control.
Q61. What is definition of transaction?
Oracle supports transactions as defined by the SQL standard. A transaction is a sequence of SQL statements that Oracle treats as a single unit of work. As soon as you connect to the database, a transaction begins. Once the transaction begins, every SQL DML (Data Manipulation Language) statement you issue subsequently becomes a part of this transaction. A transaction ends when you disconnect from the database, or when you issue a COMMIT or ROLLBACK command.
Change the WebLogic password
The following sections show the steps for updating the WebLogic password for Txk Delta 7 and later and for versions earlier than Txk Delta 7.
TKX DELTA 7 AND LATER
The following steps apply to WebLogic Txk Delta 7 and later:
⦁ Run the following command to source the run filesystem, shut down all application tier services except the Admin Server, and ensure that no application services (except admin server and node manager) are running on the primary application server:
⦁ adstpall.sh apps/apps_password -mode=allnodes -skipNM -skipAdmin
⦁ Run the following command to change the Weblogic administration password and start the services:
⦁ perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword
The system prompts you for the Apps password, the old WebLogic password and the new WebLogic password.
Following is some sample output from this process:
$ perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword
Program: txkUpdateEBSDomain.pl started at Tue Aug 21 04:30:52 2018
AdminServer is restarted after changing WebLogic Admin Password
All MidTier services should be SHUTDOWN before changing WebLogic Admin Password
Confirm if all Mid-Tier services are in SHUTDOWN state. Enter "Yes" to proceed or anything else to exit:
Enter the full path of Applications Context File [DEFAULT - /apps1/OSID/fs1/inst/apps/OSID_pserver/appl/admin/OSID_pserver.xml]:
Enter the WLS Admin Password:
Enter the new WLS Admin Password:
Enter the APPS user password:
Executing: /apps1/OSID/fs1/FMW_Home/webtier/perl/bin/perl /apps1/OSID/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/adProvisionEBS.pl ebs-get-serverstatus -contextfile=/apps1/OSID/fs1/inst/apps/OSID_pserver/appl/admin/OSID_pserver.xml -servername=AdminServer -promptmsg=hide -logfile=/apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_21_04_30_52_2018/EBSProvisioner.log
Online mode:
Backup Location: /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_21_04_30_52_2018
Logfile Location: /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_21_04_30_52_2018/updateAdminPassword.log
Executing: /apps1/OSID/fs1/EBSapps/comn/util/jdk32/jre/bin/java -classpath :/apps1/OSID/fs1/FMW_Home/wlserver_10.3/server/lib/weblogic.jar:/apps1/OSID/fs1/FMW_Home/Oracle_EBS-app1/oui/jlib/srvm.jar:/apps1/OSID/fs1/EBSapps/comn/java/classes:/apps1/OSID/fs1/EBSapps/comn/shared-libs/ebs-3rdparty/WEB-INF/lib/ebs3rdpartyManifest.jar:/apps1/OSID/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar oracle.apps.ad.util.UpdateEBSDomain updateAdminPassword -contextfile /apps1/OSID/fs1/inst/apps/OSID_pserver/appl/admin/OSID_pserver.xml -promptmsg hide -logdir /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_21_04_30_52_2018 -logfile /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_21_04_30_52_2018/updateAdminPassword.log
Domain updated successfully
Restarting AdminServer with new Admin Password.
You are running adadminsrvctl.sh version 120.10.12020000.10
Stopping WLS Admin Server...
Refer /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/admin/log/adadminsrvctl.txt for details
AdminServer logs are located at /apps1/OSID/fs1/FMW_Home/user_projects/domains/EBS_domain_OSID/servers/AdminServer/logs
adadminsrvctl.sh: exiting with status 0
adadminsrvctl.sh: check the logfile /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/admin/log/adadminsrvctl.txt for more information ...
You are running adnodemgrctl.sh version 120.11.12020000.12
NodeManager log is located at /apps1/OSID/fs1/FMW_Home/wlserver_10.3/common/nodemanager/nmHome1
adnodemgrctl.sh: exiting with status 0
adnodemgrctl.sh: check the logfile /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/admin/log/adnodemgrctl.txt for more information ...
You are running adadminsrvctl.sh version 120.10.12020000.10
Starting WLS Admin Server...
Refer /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/admin/log/adadminsrvctl.txt for details
AdminServer logs are located at /apps1/OSID/fs1/FMW_Home/user_projects/domains/EBS_domain_OSID/servers/AdminServer/logs
adadminsrvctl.sh: exiting with status 0
adadminsrvctl.sh: check the logfile /apps1/OSID/fs1/inst/apps/OSID_pserver/logs/appl/admin/log/adadminsrvctl.txt for more information ...
*************** IMPORTANT ****************
WebLogic Admin Password is changed.
Restart all application tier services using control scripts.
********************************************
Program: txkUpdateEBSDomain.pl completed at Tue Aug 21 04:34:33 2018
⦁ Start all services on all nodes, using the following command.
adstrtal.sh apps/apps_password -mode=allnodes
⦁ Login to the admin console to verify that the new WebLogic user password works.
VERSIONS EARLIER THAN TKX DELTA 7
The following steps apply to WebLogic versions earlier than Txk Delta 7:
⦁ Log in to the WebLogic Administrative console.
⦁ Use the existing WebLogic admin username and password.
⦁ Click Lock & Edit and click Domain.
⦁ Click on the Security tab and click on the Advanced tab.
⦁ Enter the new password that you want for the WebLogic admin user in the Node Manager Password.
⦁ Click Save and verify that the settings were updated successfully. Then click on Activate Changes.
⦁ Click Security Realms and Click Myrealm.
⦁ Select Users and Groups, select Weblogic User, and enter the new password for the WebLogic user.
⦁ Make sure that the settings have been updated successfully.
Note: In the next prepare phase to run after the password change, Oracle Ad Online Patching (adop) invokes EBS Domain Configuration to ensure that the WLS data source on the patch file system is synchronized with the new Apps password
Comments
Post a Comment