tag:blogger.com,1999:blog-71488061414355142392024-02-19T12:44:46.224+05:30Sujeet APPS DBA“The road to success is always under construction.”Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.comBlogger1128125tag:blogger.com,1999:blog-7148806141435514239.post-41304545887102773832024-01-08T17:18:00.006+05:302024-01-08T19:44:20.398+05:30ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE<p><b> Error:-</b></p><p>IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1)</p><p><br /></p><p><b>SYMPTOMS:-</b></p><p>You may have noticed that IEX_DELINQUENCIES_ALL is not getting updated with closed delinquencies or new delinquencies on past due invoices when the IEX: </p><p>Scoring Engine Harness or IEX: Delinquency Management processes run. You have confirmed that you are not putting in a value for the Maximum Number of Records parameter on the IEX: Scoring Engine Harness, however no delinquencies are being created.</p><p>On further investigation, even though IEX: Scoring Engine Harness shows successful the following message can be seen in the log -</p><p>ERROR IN CONCUR: IEX_SCORE: storeDelBuffest: UnExpected Error ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE and cannot be instatiated</p><p><br /></p><p><b>CAUSE:-</b></p><p>The IEX_DELINQUENCIES_ALL table has run out of sequence numbers and you can no longer create new records.</p><p>Bug 21812472</p><p><b>SOLUTION:-</b></p><p>To resolve the issue, the following SQL will recreate the sequence IEX_DEL_BUFFERS_S with NO Max Value:</p><p><br /></p><p>set serveroutput on;</p><p>declare</p><p>BEGIN</p><p>execute immediate ('drop sequence IEX.iex_del_buffers_s');</p><p>execute immediate ('create sequence IEX.IEX_DEL_BUFFERS_S START WITH 10000 '</p><p> || 'MINVALUE 1 INCREMENT BY 1 CACHE 100 NOORDER ');</p><p> DBMS_OUTPUT.put_line('New Sequence for IEX_DEL_BUFFERS_S ' || sqlerrm);</p><p>EXCEPTION</p><p> WHEN OTHERS then</p><p> DBMS_OUTPUT.put_line('Sequence creation failed IEX_DEL_BUFFERS_S ' ||</p><p>sqlerrm);</p><p>end;</p><p>commit;</p><p><br /></p><p><b>Output like below:-</b></p><p>New Sequence for IEX_DEL_BUFFERS_S ORA-0000: normal, successful completion</p><p>PL/SQL procedure successfully completed.</p><p>Commit complete.</p><div><br /></div>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-7752213371079765922023-08-05T00:40:00.001+05:302023-08-05T00:40:40.389+05:30-bash: dropdb: command not found in Postgresql<p> In PostgreSQL, the “psql command not found” error or the “psql” is not recognized as an internal or external command arises </p><p>Because of the following reasons:</p><p>- Postgres is not installed on the Machine.</p><p>- The Path for Postgres tools is not set on our system.</p><p>The stated error can be fixed either by installing PostgreSQL or by setting the environment variable for the Postgres tools.</p><p>[UAT@postgres ~]$ dropdb UATDB;</p><p><span style="color: red;">-bash: dropdb: command not found</span></p><div><h5 style="box-sizing: border-box; color: #333333; font-family: "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 14px; font-weight: 500; line-height: 1.1; margin-bottom: 10px; margin-top: 10px;">Maybe Your $PATH variable doesn't contain the default paths? echo $PATH should give something like:</h5></div><div><div># echo $PATH</div><div>/usr/bin:/bin:/opt/post/bin:/opt/post/sbin</div></div><p># locate dropdb</p><p>/usr/local/pgsql/bin/dropdb</p><p><span style="background-color: whitesmoke; color: #333333; font-family: "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 14px;"><b>You may set the PATH variable with this command</b></span></p><p># export PATH=/usr/local/pgsql/bin:$PATH</p><p>[UAT@postgres ~]$ dropdb UATDB;</p><p><br /></p><p>Its working now.</p><p><br /></p><p><br /></p><p><br /></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-48887256334899400742023-03-25T13:20:00.006+05:302023-03-25T13:20:31.080+05:30Oracle 19c Interview Questions and Answers.<p> </p><p><br /></p><p><b>1. What is Oracle 19c?</b></p><p>Oracle 19c is a relational database management system (RDBMS) from Oracle Corporation. </p><p>It is the long-term support release (LTSR) of the Oracle Database 12c Release 2 (12.2) line. </p><p>Oracle 19c is available on-premises and in the cloud.</p><p><br /></p><p><b>2. Are there any specific new features that can be used in the context of a database upgrade to Oracle 19c?</b></p><p>Yes, there are a few new features that can be used when upgrading to Oracle 19c. One is the new Oracle Upgrade Assistant, </p><p>which can help automate the process of upgrading your database. Another is the new Oracle Database Migration Assistant,</p><p> which can help with migrating data to Oracle 19c. Finally, the new Oracle SQL Developer Migration Workbench can help with migrating database </p><p>objects and data.</p><p><br /></p><p><b>3. Is there anything special about upgrading from Oracle 12c to 19c as opposed to other versions?</b></p><p>Yes, there are a few things to keep in mind when upgrading to Oracle 19c. First, the Oracle 19c database is only </p><p>compatible with Oracle Linux 7.3 or higher.</p><p> Second, the Oracle 19c database is only compatible with the Oracle 19c client. Finally, when upgrading from Oracle 12c to 19c, </p><p>you will need to export your data from the 12c database, then import it into the 19c database.</p><p><br /></p><p><b>4. Is it possible to upgrade an existing 11gR2 or 12c non-container database (CDB) to Oracle 19c? If yes, how?</b></p><p>Yes, it is possible to upgrade an existing 11gR2 or 12c non-container database (CDB) to Oracle 19c. </p><p>You can do this by using the Oracle Database Upgrade Assistant (DBUA).</p><p><br /></p><p><b>5. In what situations would you use a container database architecture for your production databases?</b></p><p>Container database architectures are becoming increasingly popular for production databases, as they offer a number of advantages. </p><p>One key advantage is that they allow for much easier management of multiple databases, as all of the databases can be managed from a single console. </p><p>This can save a lot of time and effort, particularly for larger organizations with many databases. Additionally, </p><p>container databases can offer better performance and scalability than traditional database architectures.</p><p><br /></p><p><b>6. What is a pluggable database?</b></p><p>A pluggable database is a database that is contained within another database, known as the container database. </p><p>The pluggable database shares certain resources with the container database, but it can also be configured and administered independently. </p><p>This allows for greater flexibility and scalability when managing multiple databases.</p><p><br /></p><p><b>7. Does Oracle 19c support using multiple PDBs on the same server?</b></p><p>Yes, Oracle 19c does support using multiple PDBs on the same server. </p><p>This allows for greater flexibility and scalability when deploying Oracle databases.</p><p><br /></p><p><b>8. What are some key features of Autonomous Database Cloud Service?</b></p><p>Some key features of Autonomous Database Cloud Service include:</p><p>– The ability to automatically provision and manage Oracle databases in the cloud</p><p>– The ability to elastically scale compute and storage resources as needed</p><p>– The ability to patch and upgrade databases automatically</p><p>– The ability to monitor and manage databases using the Oracle Cloud Infrastructure Console</p><p><br /></p><p><b>9. Can you explain what SQL Plan Management is and why we need it?</b></p><p>SQL Plan Management is a feature in Oracle 19c that allows the database to automatically manage SQL execution plans. </p><p>This can help improve performance by ensuring that the optimizer always has access to the most up-to-date execution plans. </p><p>SQL Plan Management can also help to prevent plan regressions by automatically reverting to a previous plan if a new one is found to be sub-optimal.</p><p><b>10. What are the main benefits provided by Oracle Machine Learning?</b></p><p>Oracle Machine Learning provides a number of benefits, including the ability to automatically generate models from data, </p><p>the ability to deploy models in a production environment, and the ability to monitor and manage models. Additionally, </p><p>Oracle Machine Learning provides a number of tools that make it easier to work with data, including data preparation, </p><p>feature selection, and model evaluation.</p><p><br /></p><p><b>11. What’s the difference between Oracle Advanced Security Option and Oracle Data Redaction?</b></p><p>Oracle Advanced Security Option provides a comprehensive set of tools for securing data in transit and at rest, including encryption, </p><p>data masking, and access control. Oracle Data Redaction, on the other hand, is a feature that allows you to selectively redact sensitive </p><p>data from your database, making it unreadable to unauthorized users.</p><p><br /></p><p><b>12. How do you ensure data security when storing sensitive information in a database like Oracle 19c?</b></p><p>When storing sensitive information in a database like Oracle 19c, you need to ensure that the data is encrypted. You can do this by using a </p><p>tool like Oracle Advanced Security. This will encrypt the data both at rest and in transit, making it much more difficult for unauthorized </p><p>individuals to access it.</p><p><br /></p><p><b>13. What is the best way to secure passwords stored in Oracle 19c?</b></p><p>The best way to secure passwords stored in Oracle 19c is to use the Oracle Password Vault. The Password Vault is a secure, centralized </p><p>repository for storing and managing passwords. It provides a single point of control for managing passwords, and it ensures that all passwords </p><p>are stored in a secure, encrypted format.</p><p><br /></p><p><b>14. How can you prevent unauthorized users from accessing sensitive information in Oracle 19c?</b></p><p>You can prevent unauthorized users from accessing sensitive information in Oracle 19c by using the Data Redaction feature. This feature allows </p><p>you to specify which columns of data should be redacted, and how the data should be redacted. You can also specify which users should have </p><p>access to the redacted data.</p><p><br /></p><p><b>15. What is the importance of the Oracle wallet feature?</b></p><p>The Oracle wallet feature is important because it allows for the secure storage of sensitive information, such as passwords, </p><p>within the database. This means that the information is less likely to be compromised if the database is breached.</p><p><br /></p><p><b>16. What is Oracle Real Application Clusters? Why should we use it?</b></p><p>Oracle Real Application Clusters (RAC) is a software feature that allows Oracle databases to be deployed on a cluster of servers. </p><p>This provides high availability and scalability for Oracle databases. RAC can be used to improve the performance of Oracle databases, </p><p>and it can also be used to provide failover protection in the event of a server failure.</p><p><br /></p><p><b>17. What are the advantages of using Oracle Enterprise Manager?</b></p><p>Oracle Enterprise Manager provides a comprehensive and centralized solution for managing Oracle databases, middleware, and applications. </p><p>It offers a complete set of tools for managing and monitoring all aspects of your IT infrastructure, including:</p><p>– Provisioning and patching</p><p>– Configuration management</p><p>– Performance monitoring</p><p>– Capacity planning</p><p>– Security and compliance</p><p>– Backup and recovery</p><p>Oracle Enterprise Manager also provides a self-service portal for users, so they can request and provision new resources, </p><p>as well as monitor the status of their existing resources.</p><p><b>18. Do you think there will ever be a situation where a business won’t need a data warehouse?</b></p><p>I don’t think so. Data warehouses will always be necessary to store and analyze large amounts of data.</p><p><b>19. What’s your opinion on Oracle Exadata? When should it be used?</b></p><p>Oracle Exadata is a powerful tool that can be used to improve the performance of Oracle databases. It should be used in situations where the </p><p>database is expected to experience high demand or where the database is particularly large.</p><p><br /></p><p><b>20. What are the differences between Oracle Exadata and Amazon Web Services RDS?</b></p><p>Oracle Exadata is a cloud-based platform that provides customers with a complete Oracle database solution, </p><p>while Amazon Web Services RDS is a cloud-based platform that provides customers with a relational database service. </p><p>Oracle Exadata includes all of the software and hardware necessary to run an Oracle database, </p><p>while Amazon Web Services RDS only provides the software necessary to run a relational database. </p><p>Oracle Exadata also provides customers with a complete set of tools for managing and monitoring their Oracle database, </p><p>while Amazon Web Services RDS only provides a limited set of tools for managing and monitoring their relational database.</p><p><b style="background-color: #04ff00;">Top Oracle DBA, RAC, And Performance Tuning Questions</b></p><p><br /></p><p><b>Q #1) Why do we use the materialized view instead of a table or views?</b></p><p>Answer: Materialized view is a database object that holds query results. If materialized views are used instead of tables or views in complex query executions, performance gets enhanced as re-execution is not required for repeated queries.</p><p><br /></p><p><b>Q #2) How is the Clustered Index different from the Non-Clustered Index?</b></p><p>Answer: An index is a schema object, which can search the data efficiently within the table.</p><p>Indexes can be clustered or non-clustered. Differences include:</p><p>In a clustered index, table records are sorted physically and stored in a particular order. Hence, a table can have a single clustered index only.</p><p> While in a non-clustered index, logical sorting happens which does not match the physical order of the records.</p><p>The leaf node of a clustered index holds the data pages while the non-clustered index holds the index rows.</p><p><br /></p><p><b>Q #3) What are the different responsibilities of a DBA?</b></p><p>Answer: DBA is the database administrator who performs all administrative tasks.</p><p>Administrative tasks include:</p><p>User-level administration i.e. creates users, removes existing users, or modifies user permissions.</p><p>Maintains database security.</p><p>Manages database storage & objects.</p><p>Tunes the performance of a database.</p><p>Performs backups & recovery tasks.</p><p><br /></p><p><b>Q #4) What do you mean by Database Normalization and why is it important?</b></p><p>Answer: The normalization technique is a set of rules that are used to organize the relational database to prevent data redundancy and dependency. </p><p>Once initial database objects are identified, normalization helps in identifying the relationships between schema objects.</p><p>Different normalization forms are as follows:</p><p>First Normal Form (1NF)</p><p>Second Normal Form (2NF)</p><p>Third Normal Form (3NF)</p><p>Boyce-Codd Normal Form (BCNF)</p><p>Fourth Normal Form (4NF)</p><p>Fifth Normal Form (5NF)</p><p><br /></p><p><b>Q #5) Can you list down the different components of physical and logical database structure?</b></p><p>Answer: Given below is the list of different components.</p><p>The physical structure includes:</p><p>Data files, which hold all the DB objects like tables, views, indexes, etc.</p><p>Redo Log files, which maintain the records of database changes as a result of user transactions.</p><p>Control files, which maintain the database status and physical structure.</p><p>The logical structure includes:</p><p>Tablespace, which is a logical storage unit where the database object resides.</p><p>Segments are logical storage units only but within a tablespace.</p><p>Extent is the logical unit where various contiguous data blocks and extents together form a segment.</p><p>A data block is the smallest logical storage unit in the database.</p><p><br /></p><p><b>Q #6) What is a SYSTEM tablespace and why do we need it?</b></p><p>Answer: System tablespace is created at the time of database creation. This tablespace holds all the data dictionary tables and hence it is </p><p>used for the creation of various database objects. System tablespace must stay online for the database to function smoothly.</p><p><br /></p><p><b>Q #7) What do you mean by SGA and how is it different from PGA?</b></p><p>Answer: SGA means System Global Area is the memory area that is defined by Oracle during instance startup. </p><p>This area can be shared by the system-level processes and hence it is known as the Shared Global Area as well.</p><p>PGA is Program Global Area is memory specific to a process or session. It is created when the Oracle process gets </p><p>started and each process will have a dedicated PGA.</p><p><br /></p><p><b>Q #8) What is a password file in a database and why is it required when a user can be authenticated using data dictionary tables?</b></p><p>Answer: Database users can be authenticated using data dictionary tables as they store the username & password. </p><p>If the password provided by a user matches the one stored in the database, then the user would be able to log in. However, </p><p>this can happen only if the database is open.</p><p>If the database is in shutdown mode, then these tables cannot be accessed and hence password file will be used by the database </p><p>administrators to log in and open the database.</p><p><br /></p><p><b>Q #9) What are the different types of backups that are available in Oracle?</b></p><p>Answer: On a higher level, there are 2 types of backup that are available in Oracle which are physical & logical.</p><p>During physical backup, copies of physical database files (like data files, control files, redo logs & other executables) </p><p>are created and saved for the future. This can be achieved using either operating system utilities or RMAN.</p><p>In contrast, logical backup allows taking a backup of the database objects like tables, views, indexes, stored procedures, etc. </p><p>individually through the Export/Import utility provided by Oracle.</p><p><br /></p><p><b>Q #10) What do we mean by hot backup & cold backup and how are they different?</b></p><p>Answer: Hot backup is the process of taking database backup while the database is in running mode. Hence, it is also known as Online Backup. </p><p>While cold backup can be taken only when the database is in shutdown mode and hence it is known as Offline Backup as well.</p><p>There are few websites like banking & trading ones, which are 24 hours operational and hence, cannot support bringing the database down. Hence, </p><p>DBAs need to take the backup in online mode only.</p><p><br /></p><p><b>Q #11) What is the difference between restoring a database and recovering a database? </b></p><p>Answer: During the restoration process, backup files are copied from the hard disk, media, or tapes to the restoration location and later make</p><p> the database operational. Recovery has an additional step of updating these data files by applying redo logs so as to recover the changes which </p><p>are not backed up.</p><p>Let us understand this with the help of a scenario.</p><p>Database full backup is taken on Friday at 11 PM</p><p>The database crash happened on Saturday at 7 AM</p><p>We can restore the lost files using the 11 PM full backup which is Restoration. However, the data will be restored up till Friday at</p><p> 11 PM and not till Saturday at 7 AM. In order to do the same, redo logs can be applied which will bring the database to the point of failure.</p><p><br /></p><p><b>Q #12) What do you understand by Redo Log file mirroring?</b></p><p>Answer: Redo log is the most crucial component of database architecture that records all transactions within the database even before it goes to the data file.</p><p>Hence, the mirroring of these files is done to protect them. Redo Log file mirroring allows redo logs to be copied to different disks simultaneously.</p><p> And this can be achieved using Data Guard and other utilities.</p><p><br /></p><p><b>Q #13) How is incremental backup different from differential backup?</b></p><p>Answer: Incremental backup is known for keeping a backup of only the changed data files since the last backup, which might be full or incremental.</p><p> For Example, An incremental/full backup is done at 10 AM on Friday and the next backup is done at 10 AM Saturday. The second incremental backup will</p><p> only have the transactions that occurred after Friday at 10 AM.</p><p>While Differential backup backs up the files that changed during the last full backup.</p><p>If you take a full backup on Friday at 10 AM and then differential back up on Saturday at 10 AM, it will take the backup of the files </p><p>changed since Friday, at 10 AM. Further, if the differential backup is taken on Sunday at 10 AM, it will take the backup of the files changed since </p><p>Friday, at 10 AM.</p><p><br /></p><p><b>Q #14) What is a Flashback Query and when should it be used?</b></p><p>Answer: Oracle has introduced a flashback technology to recover the past states of database objects. It can recover the accidental changes, </p><p>which got committed as well. Recovery depends on the specified value of the UNDO_RETENTION parameter.</p><p>For Example, the UNDO_RETENTION parameter is set to 2 hours, and if a user accidentally deletes the data at 11 AM with a commit performed. </p><p>Then, using FLASHBACK QUERY, he can retrieve these rows until 1 PM only.</p><p><br /></p><p><b>Q #15) How is RMAN better than the user-managed backup recovery process?</b></p><p>Answer: Recovery Manager (RMAN) is an Oracle built-in utility that can automate database backup & recovery processes and administrate </p><p>backup strategies as well. In user-managed backups, the user needs to perform backup manually.</p><p>RMAN backup time will be less when compared to user-managed backups as RMAN maintains all the metadata in the Central Repository and can </p><p>quickly retrieve the same.</p><p>RMAN does incremental backups rather than taking full file backups which are done by user-managed backups, which again saves time.</p><p>RMAN creates backup and recovery scripts that can be re-used and scheduled and does not need manual intervention.</p><p>RMAN can detect corrupted data blocks automatically during the backup process and recover them, whereas it doesn’t happen in user-managed backups.</p><p><br /></p><p><b>Q #16) What is a Recovery Catalog?</b></p><p>Answer: A recovery catalog is a database schema that holds the metadata used by RMAN for restoration and recovery processes.</p><p>It basically stores information on</p><p>Data files & their backup files.</p><p>Archived Redo Logs & their backup sets.</p><p>Stored scripts</p><p>Incarnation</p><p>Backup history</p><p>The catalog gets updated once RMAN takes the backup or switches redo log or changes data file.</p><p><b>Q #17) How do you recover a lost control file?</b></p><p>Answer: If one of the control files is lost or gets corrupted, we can use another copy of the control file maintained at the OS level. </p><p>If all the copies of control files are lost or if a user is maintaining only one copy of the control file which gets lost, then a user can</p><p>Manually create a control file.</p><p>Restore it from the backup control file using the below command.</p><p>ALTER DATABASE BACKUP CONTROL FILE TO TRACE;</p><p>Restore using RMAN backup by using the below commands.</p><p>setdbid XX;</p><p>restorecontrolfile;</p><p><b>Q #18) What is the difference between media recovery & crash recovery?</b></p><p>Answer: Media recovery is the process of recovering the database from the backup whenever a disk failure is there. Physical files like data files, </p><p>control files, or server parameter files get recovered during media recovery. However, crash recovery will be performed whenever a database instance failure occurs.</p><p>Media recovery needs to be performed by DBA while crash recovery is an automated process that is taken care of SMON background process.</p><p><b>Q #19) What is RAC and what are the various benefits of using RAC architecture?</b></p><p>Answer: RAC or Real Application Cluster allows the database to be installed across multiple servers forming a cluster and sharing the storage </p><p>structure at the same time. This prevents the database from a single point of failure as one or the other instance will always stay up even if </p><p>the other fails.</p><p>Using RAC helps in</p><p>Maintaining high availability of the system.</p><p>Managing workload with the least expenses.</p><p>Scalability & agility.</p><p><b>Q #20) How would you differentiate between cluster and grid?</b></p><p>Answer: Clustering is an integral part of grid infrastructure and focuses on a specific objective.</p><p>While grid, which may or may not consist of multiple clusters, possesses a wider framework that enables sharing of storage systems, data resources, </p><p>and remaining others across different geographical locations.</p><p>A cluster will have single ownership but the grid can have multiple ownership based on the number of clusters it holds.</p><p><br /></p><p><b>Q #21) What do you understand from Cache Fusion?</b></p><p>Answer: Cache fusion is the process of transferring data from one instance buffer cache to another at a very high speed within a cluster. </p><p>Instead of fetching data from a physical disk which is a slow process, the data block can be accessed from the cache.</p><p>For Example, Instance A wants to access a data block, owned by instance B. It will send an access request to instance B and hence can access </p><p>the same using the other instance B’s buffer cache.</p><p><br /></p><p><b>Q #22) How can a single instance environment be converted into the RAC environment and how will they be different?</b></p><p>Answer: Single instance can be converted into RAC using one of the below methods:</p><p>Enterprise Manager</p><p>DBCA i.e. Database Configuration Assistant</p><p>RCONFIG Utility</p><p>Single Instance environment Vs RAC Environment</p><p>Parameters</p><p>Single Instance Environment</p><p>RAC Environment</p><p>Instance</p><p>Instance</p><p>Multiple</p><p>Memory<span style="white-space: pre;"> </span>Instance will have dedicated SGA</p><p>Every instance will have separate SGA</p><p>Access to physical files<span style="white-space: pre;"> </span>Only one instance will access data files</p><p> and control files.<span style="white-space: pre;"> </span>Data files and Control Files are shared across all instances.</p><p>Flash Recovery Log</p><p>Accessed by single instance.</p><p>Shared by multiple instances.</p><p>Redo Logs</p><p>Dedicated to single instance.</p><p>Only one instance can write at a time but others can read data during recovery or archiving process.</p><p><b>Q #23) How can we monitor the space allocations in a database?</b></p><p>Answer: We can use the below data dictionary tables to monitor the space allocations:</p><p>DBA_FREE_SPACE</p><p>DBA_SEGMENTS</p><p>DBA_DATA_FILES</p><p><b>Q #24) What do you understand by “Performance Tuning of DB” & what are the different areas where we can perform tuning?</b></p><p>Answer: It is the process of enhancing database performance by making optimal use of the available resources.</p><p>Performance can be enhanced by tuning any of the below areas:</p><p>Database design.</p><p>Memory allocation.</p><p>Disk I/Os.</p><p>Database contention.</p><p>OS level (CPU).</p><p><b>Q #25) What are the different tools that are provided by Oracle to assist performance monitoring?</b></p><p>Answer: Various tools include:</p><p>AWR(Automatic Workload Repository)</p><p>ADDM(Automated Database Diagnostics Monitor)</p><p>TKPROF</p><p>STATSPACK</p><p>OEM(Oracle Enterprise Manager)</p><p><b>Q #26) What are the different optimizers that are used to optimize the database?</b></p><p>Answer: There are two types of optimizers:</p><p>Rule-Based Optimizer (RBO): If the referenced objects don’t maintain any internal statistics, RBO is used.</p><p>Cost-Based Optimizer (CBO): If the referenced objects maintain internal statistics, CBO will check all the possible execution plans and select </p><p>the one with the lowest cost.</p><p><b>Q #27) What is an explained plan and how does it help in optimizing the SQL query?</b></p><p>Answer: An explain plan is a statement that displays the execution plan selected by the Oracle optimizer for SELECT, INSERT, UPDATE & DELETE statements. </p><p>By looking at this plan, one can figure out Oracle’s selection of the right indexes, proper joins & sorts operations, etc.</p><p><br /></p><p><b>Q #28) How can we collect the statistics of different database objects?</b></p><p>Answer: ANALYZE statement can be used to collect the statistics of various database objects like tables, indexes, partitions, clusters,s or object </p><p>references. Using this statement we can also identify migrated as well as chained rows within a table or cluster.</p><p><br /></p><p><b>Q #29) Why do we need to rebuild indexes?</b></p><p>Answer: Rebuilding indexes is required in order to improve the performance of an application. Due to various INSERT & DELETE operations, the index gets </p><p>fragmented & unstructured, thereby making the application slow. To reorganize data within these indexes, rebuilding is performed.</p><p><br /></p><p><b>Q #30) What is TKPROF and how can we use it?</b></p><p>Answer: TKPROF is a tuning utility provided by Oracle which can convert SQL trace files into a readable format.</p><p>Once the trace file is generated using SQL Trace Utility, the TKPROF tool can be run against the trace file and the output can be read. It can also </p><p>generate the execution plan for SQL statements. The executable for TKPROF is located in the ORACLE HOME/bin directory.</p><p><br /></p><p><b>Q #31) How can we tune a SQL query to optimize the performance of a database?</b></p><p>Answer: Enlisted are a few of the best practices for writing SQL queries.</p><p>Column names should be provided instead of * in SELECT statements.</p><p>Joins should be used in the place of sub-queries.</p><p>EXISTS should be used instead of IN to verify the existence of data.</p><p>UNION ALL should be used in the place of UNION.</p><p>HAVING should be used only for filtering the resulting rows from the SQL query.</p><p><b>Q #32) How would you identify the SHARED_POOL_SIZE parameter that needs to be adjusted?</b></p><p>Answer: Below is the indications for the same:</p><p>Getting an ORA-04031 error.</p><p>Degrading the performance even when all the other parameters are already optimized.</p><p>Poor library cache/data dictionary hits.</p><p><b>Q #33) What do you understand by Row Chaining?</b></p><p>Answer: When a row is too large that it cannot fit in a block, then it will end up using consequent blocks which leads to the concept of Row Chaining. </p><p>It can be avoided by updating the storage parameters to an appropriate value.</p><p><br /></p><p><b>Q #34) What is table partitioning and why is it required?</b></p><p>Answer: It is a process of dividing a table into smaller chunks so as to make data retrieval easy and quick. Each piece will be known as a partition </p><p>and can be accessed separately. Apart from tables, indexes can also be partitioned.</p><p><br /></p><p><b>Q #35) How can we identify the resources for which the sessions are waiting?</b></p><p>Answer: We can find it out using v$session_waits and v$ system _waits.</p><p><br /></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-39386957106936851912023-02-02T19:39:00.001+05:302023-02-02T21:07:49.045+05:30 PLS-00201: identifier 'SYS.XDB_MIGRATESCHEMA' must be declared<p> [applmgr@oracle ~]$ sqlplus apps/*****@DB</p><p>SQL > alter package AD_ZD_PREP compile body;</p><p>Warning: Package Body altered with compilation errors.</p><p>SQL> show error;</p><p><span style="color: red;">Errors for PACKAGE BODY AD_ZD_PREP:</span></p><p><span style="color: red;">LINE/COL ERROR</span></p><p>-------- -----------------------------------------------------------------</p><p>467/3 PL/SQL: SQL Statement ignored</p><p>467/19 PL/SQL: ORA-00942: table or view does not exist</p><p>471/5 PL/SQL: SQL Statement ignored</p><p>471/21 PL/SQL: ORA-00942: table or view does not exist</p><p>491/5 PL/SQL: Statement ignored</p><p><span style="color: red;">491/5 PLS-00201: identifier 'SYS.XDB_MIGRATESCHEMA' must be declared</span></p><p>505/5 PL/SQL: SQL Statement ignored</p><p>505/21 PL/SQL: ORA-00942: table or view does not exist</p><p><br /></p><p><b style="background-color: #01ffff;">Solution:</b></p><p>SQL> select owner, object_name from dba_objects where lower(object_name) ='xdb_migrateschema';</p><p>no row select</p><p># oracle@ora admin]$ sqlplus / as sysdba</p><p>SQL> grant READ on DIRECTORY FND_DIAG_DIR to APPS;</p><p>SQL> grant WRITE on DIRECTORY FND_DIAG_DIR to APPS;</p><p>SQL>@$ORACLE_HOME/rdbms/admin/dbmsxdbschmig.sql</p><p>SQL>@$ORACLE_HOME/rdbms/admin/prvtxdbschmig.plb</p><p>SQL> select owner, object_name from dba_objects where lower(object_name) ='xdb_migrateschema';</p><p>OWNER OBJECT_NAME</p><p>SYS XDB_MIGRATESCHEMA</p><p>SYS XDB_MIGRATESCHEMA</p><p>2 rows selected.</p><p><br /></p><p># cd /u01/db/tech_st/12.1.0.2/appsutil/admin</p><p>adgrants.sql version > 120.67.12020000.69 2020/03/20 00:56:38</p><p># sqlplus /nolog</p><p># @/adgrants.sql apps</p><p>[applmgr@oracle ~]$ sqlplus apps/*****@DB</p><p>SQL> alter package AD_ZD_PREP compile body;</p><p>Package body altered.</p><p> SQL > select owner,object_name,object_type from dba_objects where status='INVALID' and object_name like 'AD_ZD%';</p><p> SQL > select object_type,status from dba_objects where object_name='AD_ZD_PREP';</p><p><br /></p><p><br /></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-10570360382727425322023-01-26T02:04:00.000+05:302023-01-26T02:04:04.564+05:30 UNEXPECTED Nodes X are listed in the ADOP_VALID_NODES table but not in the FND_NODES table<p><b><span style="background-color: #01ffff; font-size: medium;">Error : </span></b></p><p>adop phase=FS_CLONE failed with below error.</p><p>Validating system setup.</p><p><span style="color: red;"> UNEXPECTED Nodes X are listed in the ADOP_VALID_NODES table but not in the FND_NODES table.</span></p><p> [UNEXPECTED]To correct this, run AutoConfig on nodes ""</p><p> [UNEXPECTED]Error determining whether this is a multi-node instance</p><p>[STATEMENT] Please run adopscanlog utility, using the command</p><p>"adopscanlog -latest=yes"</p><p>to get the list of the log files along with snippet of the error message corresponding to each log file.</p><p>adop exiting with status = 2 (Fail)</p><p><br /></p><p><b><span style="background-color: #01ffff; font-size: medium;">Solution:</span></b></p><p><br /></p><p>01. Backup the fnd_oam_context_files, fnd_nodes, and adop_valid_nodes tables in the EBS env nodes:</p><p> sqlplus applsys/pwd</p><p><b>sqlplus applsys/****@PDB</b></p><p>create table fnd_oam_context_files_bkp26jan as select * from fnd_oam_context_files;</p><p>create table fnd_nodes_bk26jan23 as select * from fnd_nodes;</p><p>create table adop_valid_nodes_bk26jan23 as select * from adop_valid_nodes;</p><p>02. Truncate the following tables:</p><p>truncate table fnd_oam_context_files;</p><p>truncate table fnd_nodes;</p><p>truncate table adop_valid_nodes;</p><p>03. Run AutoConfig on the DB tier</p><p> Confirm Autoconfig completes successfully</p><p>04. Stop application service.</p><p>05. Run Autoconfig on the run file system.</p><p>echo $FILE_EDITION</p><p>run</p><p>Confirm Autoconfig completes successfully</p><p><b>Before running Autoconfig on the patch file system the ebs_login trigger MUST be disabled</b></p><p><b>After the successful completion of Autoconfig the ebs_login trigger MUST be re-enabled.</b></p><p>06.Logon to sqlplus as system(sqlplus system/****@PDB): alter trigger ebs_logon disable;</p><p>07. Run Autoconfig on the patch file system</p><p>echo $FILE_EDITION</p><p>patch</p><p>08. Logon to sqlplus as system(sqlplus system/****@PDB): alter trigger ebs_logon enable;</p><p>09. After Autoconfig has been run successfully on all nodes, run the following two (2) queries in </p><p>order to verify the tables have been correctly populated:</p><p>SQL></p><p> set pagesize 5</p><p> set linesize 132</p><p> col node_name format a15</p><p> col server_id format a8</p><p> col server_address format a15</p><p> col platform_code format a4</p><p> col webhost format a12</p><p> col domain format a20</p><p> col virtual_ip format a12</p><p>select node_id, platform_code, support_db D, support_cp C, support_admin A, </p><p>support_forms F, support_web W, node_name, server_id, server_address, domain, webhost, virtual_ip, </p><p>status from fnd_nodes order by node_id;</p><p>SQL></p><p> set pagesize 5</p><p> set linesize 132</p><p> col NAME format A20</p><p> col VERSION format A12</p><p> col PATH format A110</p><p> col STATUS format A10</p><p>select NAME,VERSION,PATH, STATUS from FND_OAM_CONTEXT_FILES;</p><p> 10. Start application service.</p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-12711372707636208782023-01-25T09:41:00.005+05:302023-01-25T09:41:34.154+05:30APP-FND-01388: Cannot read value for profile option CONC_KEEP_BLANK_FILES in routine &ROUTINE<p> Application version : R12.2.10</p><p>DB version : 19.17.0</p><p><span style="background-color: #fcff01;">ERROR</span></p><p>APP-FND-01388: Cannot read value for profile option CONC_KEEP_BLANK_FILES in routine &ROUTINE.</p><p><span style="background-color: #fcff01;">CAUSE</span></p><p>The issue is caused by the following defect:</p><p>BUG 30983111 - APP-FND-01388: CANNOT READ VALUE FOR PROFILE OPTION CONC_KEEP_BLANK_FILES</p><p><span style="background-color: #fcff01;">SOLUTION</span></p><p>To implement the solution, please execute the following steps:</p><p>1) Download and review the readme for Patch 30983111</p><p>2) Apply Patch 30983111 in a test environment.</p><p>3.Confirm the following file versions:</p><p>For 12.1: fnd src/process afpprc.lpc 120.10.12010000.10</p><p>For 12.2: fnd src/process afpprc.lpc 120.10.12020000.10</p><p>4) Retest the issue.</p><p>5) If the issue is resolved, migrate the solution as appropriate to other environments.</p><p>Note: Patch 30983111 exists for both 12.1.3 and 12.2 EBS versions.</p><p>The warning will be shown when it completes in warning.</p><p>One may set the profile option "Concurrent: Keep Empty Output Files" to avoid the error.</p><p>Also the error is harmless and technically can be ignored.</p><p><br /></p><p><span style="background-color: #fcff01;">REFERENCES:</span></p><p><span style="background-color: white; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: 14.3px; font-weight: 700; text-align: right;"> (Doc ID 2736293.1)</span></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-5326990868509240702023-01-23T12:22:00.000+05:302023-01-23T12:22:06.598+05:30WebADI Create Document Redirects From HTTP To HTTPS Causing Error ERR_SSL_PROTOCOL_ERROR <p> Error ERR_SSL_PROTOCOL_ERROR<span style="font-size: x-small;"> </span></p><p><br /></p><p><b style="background-color: #01ffff;">SYMPTOMS</b></p><p>On Oracle EBS R12.2.10 the default URL is HTTP:// but when you navigate to Desktop Integrator -> </p><p>Create Document the URL of the webpage redirects to HTTPS://.</p><p><br /></p><p><b style="background-color: #01ffff;">STEPS</b></p><p>1) Using the Desktop Integrator responsibility.</p><p>2) Navigate to create document.</p><p>3) Select the Integrator, Layout and populate needed parameters.</p><p>4) Select Creating document and view the that URL that is returned to create the document is https and not http.</p><p><b>The following error is seen in the browser window when attempting to create the document:</b></p><p>This site can't provide a secure connection</p><p>ERR_SSL_PROTOCOL_ERROR</p><p> Due to this issue, the document is not created.</p><p><br /></p><p><b style="background-color: #01ffff;">SOLUTION</b></p><p>1) View the following line in the XML context file: <sslterminator oa_var=</p><p>2) If the current value is <sslterminator oa_var=”s_enable_sslterminator”>#</sslterminator></p><p>Please change the following line in the XML context file: From <sslterminator oa_var=”s_enable_sslterminator”>#</sslterminator> to <sslterminator oa_var=”s_enable_sslterminator”/></p><p>3) Run the Autoconfig.</p><p>4) Restart the Services and Re-test.</p><p><b style="background-color: #01ffff;">Note:</b> that if the XML context file line value is currently <sslterminator oa_var=”s_enable_sslterminator”/>, modify the line to <sslterminator oa_var=”s_enable_sslterminator”>#</sslterminator></p><p>follow the same steps as above and re-test. </p><p><br /></p><p><b>Take backup of $CONTEXT_FILE</b></p><p>cd $CONTEXT_FILE</p><p>cd /apps/R12.2.10/fs2/inst/apps/test/appl/admin/test_oracle.xml</p><p>Replace sslterminator value in $CONTEXT_FILE</p><p>From</p><p><sslterminator oa_var="s_enable_sslterminator"/></p><p>TO</p><p><sslterminator oa_var="s_enable_sslterminator">#</sslterminator></p><p>save it.</p><p><br /></p><p>[applmgr@oracle~#]cd $ADMIN_SCRIPTS_HOME</p><p>./adautocfg.sh</p><p>Start appslication service</p><p>[applmgr@oracle~#]cd $ADMIN_SCRIPTS_HOME</p><p>./adstrtal.sh apps/*****</p><p>Retest issue.</p><p><b><br /></b></p><p><b>REFERENCES:</b></p><p>(Doc ID 2855758.1)</p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-5748358665685106182023-01-21T15:01:00.000+05:302023-01-21T15:01:12.699+05:30 How to Change Weblogic Password in R12.2.10<p><b>###RESET WEBLOGIC PASSWORD IN R12.2.10####</b></p><p>1. Shutdown the Application Services using $ADMIN_SCRIPTS_HOME/adstpall.sh apps/****</p><p>2. Start the admin server on Primary node using $ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start</p><p>3. Run the below command to create new password</p><p>perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword</p><p>[applmgr@oracle~#] </p><p><b>perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword</b></p><p>Program: txkUpdateEBSDomain.pl started at Fri Jan 20 14:12:54 2023</p><p>AdminServer will be re started after changing WebLogic Admin Password</p><p>All Mid Tier services should be SHUTDOWN before changing WebLogic Admin Password</p><p>Confirm if all Mid Tier services are in SHUTDOWN state. Enter "Yes" to proceed or anything else to exit: <b>Yes</b></p><p>Enter the full path of Applications Context File [DEFAULT - /apps/R12.2.10/fs2/inst/apps/test/appl/admin/test.xml]:</p><p>Enter the WLS Admin Password:</p><p>Enter the new WLS Admin Password:</p><p>Enter the APPS user password:</p><p>4. Start the Application MiddleWare services using $ADMIN_SCRIPTS_HOME/adstrtal.sh apps/***</p><p>Login to the admin console to verify that the new WebLogic user password works.</p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-87687958577222983422023-01-21T13:10:00.000+05:302023-01-21T13:10:05.207+05:30AFPASSWD utility to change apps password R12.2.10<p> R12.2.10: Steps to Change the APPS, APPLSYS, and APPS_NE Password Using FNDCPASS or AFPASSWD </p><p>Whenever using FNDCPASS or AFPASSWD to change the APPS, APPLSYS, and APPS_NE password, one must also perform the following actions:</p><p>Important: These steps must be carried out on the run file system. The FND_USER and FND_ORACLE_USERID tables should be backed up before any passwords are changed. Remove the backups after you have confirmed that the changes are successfully completed. Passwords for all the 3 schemas, APPS, APPLSYS and APPS_NE will be changed together while changing APPLSYS password.</p><p>1. Shut down the application tier services using the below script:</p><p>$INST_TOP/admin/scripts/adstpall.sh apps/****</p><p>###Change the APPLSYS password using.</p><p><br /></p><p><b>###AFPASSWD:</b></p><p>AFPASSWD is an enhanced version of FNDCPASS, and includes the following features:</p><p>• AFPASSWD only prompts for passwords required for the current operation, allowing separation of duties between applications administrators and database administrators. This also improves interoperability with Oracle Database Vault. In contrast, the FNDCPASS utility currently requires specification of the APPS and the SYSTEM usernames and corresponding passwords, preventing separation of duties between applications administrators and database administrators.</p><p>• When changing a password with AFPASSWD, the user is prompted to enter the new password twice to confirm.</p><p>• In Oracle E-Business Suite Release 12.2.3 and higher, you can also use the AFPASSWD utility to migrate Oracle E-Business Suite user passwords to a password hashing scheme.</p><p><b>Syntax for using AFPASSWD:</b></p><p># AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] -s <APPLSYS></p><p>Syntax for using AFPASSWD:</p><p>AFPASSWD -c apps@PDB -s APPLSYS</p><p><br /></p><p><b style="background-color: #04ff00;">NOTE:</b> The <b>steps 3 to 7</b> are mandatory AFPASSWD and are only applicable when changing the APPLSYS password. </p><p>They are not applicable when changing passwords for product schemas like PO, GL etc or the SYSTEM schema.</p><p>In the next prepare phase after the password change, ADOP will invoke EBS Domain Configuration to ensure </p><p>that the WLS datasource on the patch file system will be synchronized with the new APPS password.</p><p> 3. Run autoconfig with the newly changed password.</p><p>4. Start AdminServer using the $INST_TOP/admin/scripts/adadminsrvctl.sh script. </p><p>Do not start any other application tier services.</p><p><b>#####. Change the "apps" password in WLS Datasource as follows:</b></p><p>#stop all apps service</p><p><span style="font-size: x-small;">cd $ADMIN_SCRIPTS_HOME</span></p><p><span style="font-size: x-small;">./adstpall.sh apps/****</span></p><p># start admin server.</p><p><span style="font-size: small;">cd $ADMIN_SCRIPTS_HOME</span></p><p>./adadminsrvctl.sh start</p><p><b> Log in to WLS Administration Console.</b></p><p>http://host_name:7005/console <span style="background-color: #fcff01; font-size: x-small;"><Depend on your FILE_EDITION(RUN/PATCH) WLS port will change></span></p><p>weblogic/*****</p><p>b. Click Lock & Edit in Change Center.</p><p>c. In the Domain Structure tree, expand Services, then select Data Sources.</p><p>d. On the "Summary of JDBC Data Sources" page, select EBSDataSource.</p><p>e. On the "Settings for EBSDataSource" page, select the Connection Pool tab.</p><p>f. Enter the new password in the "Password" field. <<enter new apps password>></p><p>g. Enter the new password in the "Confirm Password" field. <<re-enter new apps password>></p><p>h. Click Save.</p><p>i. Click Activate Changes in Change Center.</p><p>Message icon - Success All changes have been activated. No restarts are necessary.</p><p><b>#Start all the application tier services using the below script</b></p><p><span style="font-size: small;">cd $ADMIN_SCRIPTS_HOME</span></p><p>./adstrtal.sh apps/****</p><p><br /></p><p><b> REFERENCES :</b></p><p>(Doc ID 1674462.1)</p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-49960786488732022252023-01-18T13:47:00.001+05:302023-01-19T01:23:25.329+05:30ADOP utility in R12.2.10<p><b> Overview of ADOP in Oracle EBS R12.2</b></p><p>ADOP – stands for Application DBA Online Patching which is introduced in Oracle EBS R12.2 </p><p>version to patch oracle applications while a system is available for users during Patching.</p><p><b>NOTE:</b> The adop utility always runs from the run edition file system. </p><p>Full application functionality is retained as patch execution proceeds, </p><p>until the cutover phase is reached.</p><p><b>The online patching cycle consists of five of high level phases:</b></p><p>prepare: Prepares the environment for patching.</p><p>apply: Applies the specified patches to the environment.</p><p>finalize: Performs any final steps required to make the system ready for cutover.</p><p>cutover: Shuts down application tier services, makes the patch edition the new run edition, </p><p>and then restarts application tier services. This is the only phase the involves a brief downtime.</p><p>cleanup: Removes obsolete code and data from old editions.</p><p><span style="background-color: white; font-family: "Open Sans"; font-size: 17px;"><b>Important commands related to ADOP</b></span></p><p></p><p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;">FILE EDITION SWAP</span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">[applmgr@oracle~#] echo $FILE_EDITION<o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">run</span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;">RUN to PATCH <nothing
changes in RUN></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=prepare<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=cutover<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=CLEANUP<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop -STATUS</span></b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;"><o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><br /></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;">RUN to PATCH <make changes
in RUN><o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><br /></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=FS_CLONE</span></b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;"><o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=prepare<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=cutover<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=CLEANUP<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop -STATUS<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;">SYN with RUN to PATCH</span></b><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=FS_CLONE<span style="mso-spacerun: yes;">
</span></span></b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><schedule job every second Sunday><span style="background: aqua; mso-highlight: aqua;"><o:p></o:p></span></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><span style="mso-spacerun: yes;"> </span>Or before patch
trigger adop phase=FS_CONE</span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;">ANYTHING fails in adop
cycle before cutover phase<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><br /></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">adop phase=abort<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">adop phase=cleanup cleanup_mode=full<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">adop phase = fs_clone</span></b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;"><o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop -STATUS<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;">OR</span></b><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">adop phase=abort<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">adop phase=cleanup cleanup_mode=full<span style="mso-spacerun: yes;"> </span><span style="background: aqua; mso-highlight: aqua;"><Failed></span><o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">adop phase = fs_clone</span></b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;"><o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop -STATUS<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;">Try below steps:</span></b><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">adop phase=abort<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">adop phase=cleanup cleanup_mode=quick<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">adop phase = fs_clone</span></b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;"><o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop -STATUS</span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Fixed issue and again try below command:<o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=prepare<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=apply patches=(patch_no)<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=FINALIZE<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=cutover<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop phase=CLEANUP<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;">Adop -STATUS<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;">Adop session reach 20
after that run below steps (Max adop session value is 24)</span></b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;"><o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333;">Actualize_All
and Full Cleanup:<o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333;"><o:p> </o:p></span><span face="Tahoma, sans-serif" style="color: #333333;"> </span><span face="Tahoma, sans-serif" style="background-color: #fafafa; color: #333333;">adop phase=prepare</span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333;"><span style="mso-spacerun: yes;"> </span>adop phase=actualize_all<o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333;"><span style="mso-spacerun: yes;"> </span>adop phase=finalize<o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333;"><span style="mso-spacerun: yes;"> </span>adop phase=cutover<o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333;"><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>adop phase=cleanup cleanup_mode=full<o:p></o:p></span></p><p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333;"><br /></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 12pt; mso-highlight: aqua; mso-shading: #FAFAFA;">adop option</span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;">apply a patch in
downtime mode:</span></b><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span><span face="Tahoma, sans-serif" style="background-color: #fafafa; color: #333333; font-size: 9pt;">adop phase=apply patches=32300055 apply_mode=hotpatch</span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;">Restart a apply command</span></b><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face="Tahoma, sans-serif" style="background-color: #fafafa; color: #333333; font-size: 9pt;">adop phase=apply patches=31993886 restart=yes</span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;">Abandon previous apply
command and apply replacement</span></b><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face="Tahoma, sans-serif" style="background-color: #fafafa; color: #333333; font-size: 9pt;">adop phase=apply patches=223456 abandon=yes</span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><b><span face=""Tahoma",sans-serif" style="background: aqua; color: #333333; font-size: 9pt; mso-highlight: aqua; mso-shading: #FAFAFA;">Restart failed fs_clone
from the beginning:</span></b><b><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p></o:p></span></b></p>
<p class="MsoNormal" style="line-height: normal; margin-bottom: 0cm;"><span face=""Tahoma",sans-serif" style="background: rgb(250, 250, 250); color: #333333; font-size: 9pt;"><o:p> </o:p></span><span face="Tahoma, sans-serif" style="color: #333333; font-size: 9pt;"> </span><span face="Tahoma, sans-serif" style="background-color: #fafafa; color: #333333; font-size: 9pt;">adop
phase=fs_clone force=yes</span></p><br /><p></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-49570487540243107862022-12-09T01:47:00.002+05:302022-12-09T01:47:12.144+05:30ORA-06512: at "APPS.FND_CP_FNDSM"<p> After clone Concurrent manager shut down after 3/4 hours.</p><p><b>Internal concurrent manager log found below error</b></p><p><b>logfile=/apps/UAT/inst/apps/UAT_ora/logs/appl/conc/log/UAT_101.mgr</b></p><p class="MsoNormal"><span style="color: red;">Cause: insert_fcp failed due to
ORA-01403: no data found<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: red;">ORA-06512: at
"APPS.FND_CP_FNDSM", line 149<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: red;">ORA-06512: at line 1.<o:p></o:p></span></p><p class="MsoNormal"><span style="color: red;"><br /></span></p><p class="MsoNormal"><span style="background-color: #fcff01; color: red;">Solution :-</span></p><p class="MsoNormal"><span style="background-color: white;"><b style="color: #222222; font-family: Georgia, "Times New Roman", serif; text-align: justify;">stop Concurrent Managers using adcmctl.sh</b></span></p><p class="MsoNormal"><span style="background-color: white;">[applmgr@oracle scripts]$ ./adcmctl.sh stop apps/***</span></p><p class="MsoNormal"><span style="background-color: white;">[applmgr@oracle ~]$ ps -ef|grep FNDLIBR </span></p><p class="MsoNormal">[applmgr@oracle scripts]$ sqlplus apps/*****</p><p class="MsoNormal"></p><p class="MsoNormal">SQL> select CONCURRENT_QUEUE_NAME from
FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p>CONCURRENT_QUEUE_NAME</p><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal">------------------------------<o:p></o:p></p>
<p class="MsoNormal">FNDSM_ORACLE<o:p></o:p></p>
<p class="MsoNormal"><o:p><span style="font-size: x-small;"> </span></o:p></p>
<p class="MsoNormal"><span style="background-color: white; color: #222222; font-family: Georgia, serif; text-align: justify;"><span style="font-size: x-small;">SQL> set lines 200</span></span></p>
<p class="MsoNormal" style="background: white; line-height: normal; margin-bottom: 0cm; text-align: justify;"><span style="font-size: x-small;"><span style="color: #222222; font-family: Georgia, serif;">SQL> column CONTROL_CODE format
A15</span><span style="color: #222222; font-family: Arial, sans-serif;"><o:p></o:p></span></span></p>
<p class="MsoNormal" style="background: white; line-height: normal; margin-bottom: 0cm; text-align: justify;"><span style="font-size: x-small;"><span style="color: #222222; font-family: Arial, sans-serif;"><o:p> </o:p></span><span style="color: #222222; font-family: Georgia, serif;">SQL> select CONCURRENT_QUEUE_NAME,
CONTROL_CODE , TARGET_NODE, NODE_NAME from FND_CONCURRENT_QUEUES where
concurrent_queue_name like 'OAMGCS_%';</span></span></p>
<p class="MsoNormal" style="background: white; line-height: normal; margin-bottom: 0cm; text-align: justify;"><span style="color: #222222; font-family: Arial, sans-serif;"><o:p><span style="font-size: x-small;"> </span></o:p></span></p>
<p class="MsoNormal" style="background: white; line-height: normal; margin-bottom: 0cm; text-align: justify;"><span style="font-size: x-small;"><span style="color: #222222; font-family: Georgia, serif;">CONCURRENT_QUEUE_NAME
CONTROL_CODE TARGET_NODE<span style="mso-spacerun: yes;"> </span>NODE_NAME</span><span style="color: #222222; font-family: Arial, sans-serif;"><o:p></o:p></span></span></p>
<p class="MsoNormal" style="background: white; line-height: normal; margin-bottom: 0cm; text-align: justify;"><span style="font-size: x-small;"><span style="color: #222222; font-family: Georgia, serif;">------------------------------</span><span style="color: #222222; font-family: Arial, sans-serif;"><o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="color: #222222; font-family: Georgia, serif; line-height: 107%;"><span style="font-size: x-small;">OAMGCS_ORACLE<span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span>E<span style="mso-tab-count: 2;"> </span><span style="mso-spacerun: yes;"> </span>NULL<span style="mso-spacerun: yes;"> ORACLE</span></span><o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: #222222; font-family: Georgia, serif; line-height: 107%;"><o:p> </o:p></span></p>
<p class="MsoNormal"><b style="background-color: #fcff01;"><span style="color: #222222; font-family: Georgia, serif; line-height: 107%;"><o:p> </o:p></span><span style="color: #222222; font-family: Georgia, "Times New Roman", serif;">The standard codes used by Oracle Applications are as followings:</span></b></p><p></p><div class="MsoNormal" style="color: #222222; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; line-height: 18.48px;"><span style="font-family: Georgia, "Times New Roman", serif;"><br /><span style="background-color: white;">'A', -> 'Activate concurrent manager'</span><br /><span style="background-color: white;">'D', -> 'Deactivate concurrent manager'</span><br /><b style="background-color: #ffa400;">'E', -> 'Deactivated'</b><br /><span style="background-color: white;">'N', -> 'Target node/queue unavailable'</span><br /><span style="background-color: white;">'R', -> 'Restart concurrent manager'</span><br /><span style="background-color: white;">'T', -> 'Terminate requests and deactivate manager'</span><br /><span style="background-color: white;">'U', -> 'Update concurrent manager env inf.'</span><br /><span style="background-color: white;">'V', -> 'Verify concurrent managers status'</span><br /><span style="background-color: white;">'X', -> 'Terminated'<o:p></o:p></span></span></div><div class="MsoNormal" style="color: #222222; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; line-height: 18.48px;"><span style="background-color: white; font-family: Georgia, "Times New Roman", serif;"><br /></span><div class="MsoNormal" style="background-color: white;"><br /></div><div class="MsoNormal" style="background-color: white;"><span style="font-family: Georgia, "Times New Roman", serif;"><br /></span></div><div class="MsoNormal"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;"><b style="background-color: #fcff01;">STATUS_CODE Column:</b><span style="background-color: white;"><o:p></o:p></span></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="font-family: Georgia, "Times New Roman", serif;"><br /></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">A Waiting<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">B Resuming<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">C Normal<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">D Cancelled<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">E Error<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">F Scheduled<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">G Warning<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">H On Hold<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">I Normal<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">M No Manager<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">Q Standby<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">R Normal<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">S Suspended<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">T Terminating<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">U Disabled<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">W Paused<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">X Terminated<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">Z Waiting<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="font-family: Georgia, "Times New Roman", serif;"><br /></span></div><div class="MsoNormal"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;"><b style="background-color: #fcff01;">PHASE_CODE column</b><span style="background-color: white;"><o:p></o:p></span></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="font-family: Georgia, "Times New Roman", serif;"><br /></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">C Completed<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">I Inactive<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">P Pending<o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;">R Running</span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;"><br /></span></span></div><div class="MsoNormal" style="background-color: white;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="font-family: Georgia, "Times New Roman", serif;"><br /></span></span></div></div><p class="MsoNormal">
</p><p class="MsoNormal">SQL> update FND_CONCURRENT_QUEUES set control_code = null
where concurrent_queue_name = 'OAMGCS_ORACLE';<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p>1 row updated.</p><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p>SQL> commit;</p><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p>Commit complete.</p><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p>SQL> update FND_CONCURRENT_QUEUES set
TARGET_NODE='ORACLE' where
CONCURRENT_QUEUE_NAME='OAMGCS_ORACLE';</p><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p>1 row updated.</p><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p>SQL> commit;</p><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p>Commit complete.</p><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p><span style="background-color: white; color: #222222; font-family: Georgia, serif; text-align: justify;">SQL> set lines 200</span></p>
<p class="MsoNormal" style="background: white; line-height: normal; margin-bottom: 0cm; text-align: justify;"><span style="color: #222222; font-family: Georgia, serif;">SQL> column CONTROL_CODE format
A15</span><span style="color: #222222; font-family: Arial, sans-serif;"><o:p></o:p></span></p>
<p class="MsoNormal" style="background: white; line-height: normal; margin-bottom: 0cm; text-align: justify;"><span style="color: #222222; font-family: Arial, sans-serif;"><o:p> </o:p></span><span style="color: #222222; font-family: Georgia, serif;">SQL> select CONCURRENT_QUEUE_NAME,
CONTROL_CODE , TARGET_NODE, NODE_NAME from FND_CONCURRENT_QUEUES where
concurrent_queue_name like 'OAMGCS_%';</span></p>
<p class="MsoNormal" style="background: white; line-height: normal; margin-bottom: 0cm; text-align: justify;"><span style="color: #222222; font-family: Arial, sans-serif;"><o:p> </o:p></span><span style="color: #222222; font-family: Georgia, serif;">CONCURRENT_QUEUE_NAME----CONTROL_CODE----TARGET_NODE----NODE_NAME</span></p>
<p class="MsoNormal" style="background: white; line-height: normal; margin-bottom: 0cm; text-align: justify;"><span style="color: #222222; font-family: Georgia, serif;">------------------------------</span><span style="color: #222222; font-family: Arial, sans-serif;"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: #222222; font-family: Georgia, serif; line-height: 107%;">OAMGCS_ORACLE---null-- ORACLE-------- <o:p></o:p></span><span style="color: #222222; font-family: Georgia, serif;">ORACLE</span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<span style="background-color: #fcff01; color: #222222; font-family: Georgia, "Times New Roman", serif; text-align: justify;"><b>start Concurrent Managers using adcmctl.sh</b></span><p></p><p class="MsoNormal"><span style="background-color: white;">[applmgr@oracle scripts]$ ./adcmctl.sh start apps/***</span></p><p class="MsoNormal"><span style="background-color: white;">[applmgr@oracle ~]$ ps -ef|grep FNDLIBR </span></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-83214249993315365912022-10-29T17:05:00.004+05:302022-10-29T17:05:38.150+05:30SYSTEM Tablespace is Growing Abnormally in 12.2 Oracle database.<p> <span style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;">My SYSTEM tablespace was growing rapidly. This was happening in my test environment. We observe that we were not doing much on this database.</span></p><p><u style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;">Database version is 12cR2</u></p><p><span style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;">Possibly there are few reasons for the same.</span><br style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;" /><br style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;" /><span style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;">1) Some has assigned default tablespace as SYSTEM</span><br style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;" /><span style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;">2) Auditing is ON and consuming more space.</span><br style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;" /><span style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;">3) SYS_LOB Objects uses lot of disk space</span><br style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;" /><span style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;">4) Data dictionary objects uses lot of disk space</span><br style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;" /><br style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;" /><span style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;">So now, how to find the root cause and what could be the solution?</span><br style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;" /><br style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;" /><b>TABLESPACE Status :- </b></p><p><i style="background-color: #fefdfa; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><br /></i></p><p><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"></i></p><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEihWtMuyYWZD9_ea-BfQa9MpsOUmZJLebBnhajBN_JO_4J7bHOyVoXr18dgXjRaJ_s3J1iAniioQiqINF39mYlKb0_8k2A5zhbli48jA-BAMIFW-O4WUJC8tSsTAPJNPGL1zvLIlgJsD9Jbq9cN9HngSlrywEAAkhp40PtLxSvhT2qwguM4jj1VT3pGUA" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="113" data-original-width="637" src="https://blogger.googleusercontent.com/img/a/AVvXsEihWtMuyYWZD9_ea-BfQa9MpsOUmZJLebBnhajBN_JO_4J7bHOyVoXr18dgXjRaJ_s3J1iAniioQiqINF39mYlKb0_8k2A5zhbli48jA-BAMIFW-O4WUJC8tSsTAPJNPGL1zvLIlgJsD9Jbq9cN9HngSlrywEAAkhp40PtLxSvhT2qwguM4jj1VT3pGUA=s16000" /></a></i></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><br /></i></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><br /></i></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><span style="font-style: normal; text-align: left;">Let’s query to dba_segments table to find the root cause. I came across few scenarios in my different databases. Here I am discussing those scenarios, what could be the problem and solution of it.</span><br style="font-style: normal; text-align: left;" /><br style="font-style: normal; text-align: left;" /><span style="font-style: normal; text-align: left;">Use below query to find out what are those segments which are consuming lot of space in SYSTEM tablespace.</span></i></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><br style="font-style: normal; text-align: left;" /><i style="text-align: left;">Connect with sysdba.</i></i></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><span style="background-color: yellow; color: black; font-family: "Times New Roman"; font-size: medium; font-style: normal; text-align: left;">[oracle@ora ~]$ sqlplus sys/sys@2023@PDB as sysdba</span></i></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><span style="background-color: yellow; color: black; font-family: "Times New Roman"; font-size: medium; font-style: normal; text-align: left;"><br /></span></i></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><i style="text-align: left;">SQL :- select owner,segment_name,segment_type<br /> ,bytes/(1024*1024) size_mb<br /> from dba_segments<br /> where tablespace_name = 'SYSTEM'<br /> order by size_mb desc;</i></i></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><i style="text-align: left;"><br /></i></i></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><i style="text-align: left;"><br /></i></i></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><i style="text-align: left;"><br /></i></i></div><div class="separator" style="clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><i style="text-align: left;"><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhiHBtyhDYgoBBVPYSG119AUmsiHnpHAI0Twi3XQ2bjAK06tcI-B44i_7Yh0nOid2KIlPbI5wazhkNmONAmMj22HihWEaxT0TIbHB-rtFgvSjdrkWtTUqGKD7Z0FixSl5Vp3hDWSE4ChH5GEWM8dXS_aqjz0PEhRAV0ES3f6KtdZMDkBi3gnLpu4tKCLA" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="114" data-original-width="569" src="https://blogger.googleusercontent.com/img/a/AVvXsEhiHBtyhDYgoBBVPYSG119AUmsiHnpHAI0Twi3XQ2bjAK06tcI-B44i_7Yh0nOid2KIlPbI5wazhkNmONAmMj22HihWEaxT0TIbHB-rtFgvSjdrkWtTUqGKD7Z0FixSl5Vp3hDWSE4ChH5GEWM8dXS_aqjz0PEhRAV0ES3f6KtdZMDkBi3gnLpu4tKCLA=s16000" /></a></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><br /></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><br /></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><span style="background-color: #fefdfa; font-style: normal; text-align: left;">Here in above database my AUD$ table is growing rapidly and it is almost 30gb in size.</span><br style="font-style: normal; text-align: left;" /><br style="font-style: normal; text-align: left;" /><b style="background-color: #fefdfa; font-style: normal; text-align: left;">Problem</b><br style="font-style: normal; text-align: left;" /><span style="background-color: #fefdfa; font-style: normal; text-align: left;">Auditing is enabled hence AUD$ table is growing very fast. Organization wants to do auditing and there is no option to disable it.</span><br style="font-style: normal; text-align: left;" /><br style="font-style: normal; text-align: left;" /><b style="background-color: #fff2cc; font-style: normal; text-align: left;">Solution</b><br style="font-style: normal; text-align: left;" /><span style="background-color: #fefdfa; font-style: normal; text-align: left;">Few solutions which Oracle always recommended,</span><br style="font-style: normal; text-align: left;" /><span style="background-color: #fefdfa; font-style: normal; text-align: left;"> ‘- Move AUD$ to different tablespace</span><br style="font-style: normal; text-align: left;" /><span style="background-color: #fefdfa; font-style: normal; text-align: left;">‘- Purge audit data</span><br style="font-style: normal; text-align: left;" /><span style="background-color: #fefdfa; font-style: normal; text-align: left;">‘- Delete/truncate older data</span></div><div class="separator" style="clear: both; text-align: center;"><span style="background-color: #fefdfa; font-style: normal; text-align: left;"><br /></span></div><div class="separator" style="clear: both; text-align: center;"><span style="background-color: #fefdfa; font-style: normal; text-align: left;">Connect with sysdba.</span></div></i></i></div><p></p><p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN" style="background: yellow; mso-ansi-language: EN; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-hansi-font-family: Calibri; mso-highlight: yellow;">[oracle@ora ~]$ sqlplus sys/sys@2023@PDB as sysdba</span><span lang="EN"><o:p></o:p></span></p><div class="separator" style="clear: both; text-align: center;"><i style="text-align: left;"><div class="separator" style="clear: both; text-align: center;">
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN">SQL*Plus: Release 12.2.0.1.0 Production on Thu <b>Feb
13 06:52:45 2020</b><o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN">Copyright (c) 1982, 2016, Oracle. All rights reserved.<o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN">Connected to:<o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN">Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production<o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN"><o:p> </o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN"><b>SQL> CREATE TABLE backup_AUD_13feb20 AS
(SELECT * from AUD$);</b><o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN"><o:p> </o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN">Table created.<o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN"><o:p> </o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN"><b>SQL> truncate table AUD$;</b><o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN"><o:p> </o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN">Table truncated.<o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN"><o:p> </o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN"><b>SQL> commit;</b><o:p></o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN"><o:p> </o:p></span></p>
<p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN">Commit complete.<o:p></o:p></span></p><p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN"><br /></span></p><p class="MsoNormal" style="line-height: 115%; margin-bottom: 10.0pt; mso-layout-grid-align: none; mso-pagination: none; text-autospace: none;"><span lang="EN"><b>Recheck SYSTEM Tablespace status.</b></span></p></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><br /></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><br /></div><br /><br /></i></div><p><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"></i></p><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><br /></i></div><div class="separator" style="background-color: #fefdfa; clear: both; text-align: center;"><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><br /></i></div><i style="color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><br /><br /></i><p></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-60521295787863780162022-09-17T00:31:00.008+05:302022-09-17T00:31:46.366+05:30Maintenance Mode in Oracle EBS R12.1.3<p> </p><p><b>How to Enable/DISABLE Maintenance Mode in Oracle EBS R12.1.3</b></p><p>Login with Application OS user.</p><p>Conn with apps.</p><p>$ sqlplus apps/****</p><p>$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE </p><p>$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE</p><p>sending the parameter 'ENABLE' or 'DISABLE'</p><p><br /></p><p><b>How to verify Enable/DISABLE Maintenance Mode</b></p><p> $ sqlplus apps/****</p><p>SQL> select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual; </p><p> FND_PROFILE.VALUE('APPS_MAINTENANCE_MODE')</p><p> ----------------------------------------------------------------------------</p><p> NORMAL</p><p>The above query will return MAINT (maintenance mode is ENABLE) </p><p>The above query will return NORMAL (maintenance mode is DISABLE) </p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-27245383808498126442022-08-17T19:19:00.005+05:302022-08-17T19:25:00.167+05:30How to Kill Zombie Processes on Linux<b>
###How to Kill Zombie Processes on Linux</b><div><br /></div><div> Defunct processes may also be known as "zombie" processes. They do not use any system resources - CPU, memory etc.
but may be seen as entries in their respective operating system process table. </div><div><br /></div><div> A zombie is already dead, so you cannot kill it. To clean up a zombie, it must be
waited on by its parent, so killing the parent should work to eliminate the
zombie. (After the parent dies, the zombie will be inherited by pid 1, which
will wait on it and clear its entry in the process table.) If your daemon is
spawning children that become zombies, you have a bug. Your daemon should notice
when its children die and wait on them to determine their exit status. </div><div><br /></div><div><br /></div><div><b> ##What Causes Zombie Processes on Linux?</b></div><div><br /></div><div> A poorly written parent process might not call
the wait() function when the child process is created. This means nothing is
watching for state changes in the child process, and the SIGCHLD signal will be
ignored. Or, perhaps another application is affecting the execution of the
parent process, either due to poor programming or malicious intent.</div><div><br /></div><div><br /></div><div> [oracle@oracle ~]$ ps -ef|grep zombie </div><div><br /></div><div>oracle 175489580 0 11:33 pts/0 00:00:00
grep --color=auto zombie
</div>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-53891324762880366522022-08-10T20:13:00.006+05:302022-08-10T20:30:33.391+05:30ORA-00020: No more process state objects available<p> How to increase processes, sessions, and transactions parameters in Oracle 12R1.</p><p>Whenever DB processes are utilized 100% then it’s NOT allowed any new sessions/connections to DB’s instances and we get the below error:</p><p><span style="background-color: #01ffff;">ORA-00020: No more process state objects available</span></p><p><span style="background-color: #01ffff;">ORA-20 errors will not be written to the alert log for</span></p><p>If we are planning to increase “PROCESSES” parameter so, we should also plan to increase the “SESSIONS” and “TRANSACTIONS” parameters. </p><p>A basic formula for determining these parameter values is as follows:</p><p> </p><p> <b> processes=x</b></p><p><b> sessions=x*1.1+5</b></p><p><b> transactions=sessions*1.1</b></p><p><br /></p><p><b>Cause:</b> All process state objects are in use.</p><p><b>Action:</b> Increase the value of the PROCESSES initialization parameter.</p><p>Before starting on the solution, we understand what is processes, sessions & transactions:</p><p><b>PROCESSES:</b> It specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, </p><p>job queue processes, and parallel execution processes.</p><p>The default values of the SESSIONS and TRANSACTIONS parameters are derived from the PROCESSES parameter. Therefore, if we change the value of PROCESSES, </p><p>We should evaluate whether to adjust the values of those derived parameters.</p><p><br /></p><p><b>SESSIONS:</b> It specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. </p><p>We should always set this parameter explicitly to a value equivalent to our estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions. Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead.</p><p><b>TRANSACTIONS:</b> It specifies the maximum number of concurrent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated. </p><p>The default value is greater than SESSIONS (and, in turn, PROCESSES) to allow for recursive transactions.</p><p><br /></p><p><b style="background-color: #ffa400;">Solution:-</b></p><p>[oradb@oracle ~]$ sqlplus / as sysdba</p><p>SQL> show parameter processes</p><p>NAME TYPE VALUE</p><p>------------------------------------ ----------- ------------------------------</p><p>aq_tm_processes integer 1</p><p>db_writer_processes integer 1</p><p>gcs_server_processes integer 0</p><p>global_txn_processes integer 1</p><p>job_queue_processes integer 2</p><p>log_archive_max_processes integer 4</p><p>processes integer 400</p><p>SQL> show parameter sessions</p><p>NAME TYPE VALUE</p><p>------------------------------------ ----------- ------------------------------</p><p>java_max_sessionspace_size integer 0</p><p>java_soft_sessionspace_limit integer 0</p><p>license_max_sessions integer 0</p><p>license_sessions_warning integer 0</p><p>sessions integer 800</p><p>shared_server_sessions integer</p><p>SQL> show parameter transactions</p><p>NAME TYPE VALUE</p><p>------------------------------------ ----------- ------------------------------</p><p>transactions integer 880</p><p>transactions_per_rollback_segment integer 5</p><p>SQL></p><p><br /></p><p>If we are planning to increase “PROCESSES” parameter so, we should also plan to increase the “SESSIONS” and “TRANSACTIONS” parameters. A basic formula for determining these parameter values is as follows:</p><p> <b> processes=x</b></p><p><b> sessions=x*1.1+5</b></p><p><b> transactions=sessions*1.1</b></p><p><b>Before altering parameters we need to take a backup of pfile.</b></p><p>SQL> create pfile from spfile;</p><p>SQL> alter system set processes=500 scope=spfile;</p><p>SQL> alter system set sessions=555 scope=spfile;</p><p>SQL> alter system set transactions=610 scope=spfile;</p><p><b>After resetting the parameters we need to bounce the database-</b></p><p>SQL>shutdown immediate;</p><p>SQL>startup;</p><p>After that will check the settings of parameters-</p><p>SQL> show parameter processes;</p><p>SQL> show parameter session;</p><p>SQL> show parameter transactions;</p><p><br /></p><p><br /></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-58125349293357015052022-08-08T23:34:00.001+05:302022-08-08T23:34:07.060+05:30ORA-39087: directory name DPUMP_DIR is invalid<p> This error may be returned if you are trying to export or import using oracle data pump, </p><p>but invalid directory name has been provided during this process. </p><p><b>Following is an example of this.</b></p><p><b style="background-color: #fcff01;">Error:</b></p><p>[oracle@hostname ~]$ expdp system/manager DIRECTORY=dpump_dir DUMPFILE=system.dmp logfile=system.log schemas=system</p><p>Export: Release 11.2.0.3.0 - Production on Wed Aug 2022 10:07:55 </p><p>Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.</p><p>Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production</p><p>With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options</p><p>ORA-39002: invalid operation</p><p>ORA-39070: Unable to open the log file.</p><p>ORA-39087: directory name DPUMP_DIR is invalid</p><p><b style="background-color: #fcff01;">Solution:</b></p><p>Check the directory is exists or not, If not exist then create the directory with proper path: </p><p>If the directory exists then check grant on the directory, if the grant missing then provide grant </p><p>SQL> CREATE OR REPLACE DIRECTORY DPUMP_DIR AS '/u01/dpump'</p><p><b style="background-color: #fcff01;">NOTE :</b></p><p>If SYS or SYSTEM user will be used for export/import, granting rights on above directory are not needed, </p><p>otherwise grant rights to the user performing export/import</p><p>SQL> GRANT READ, WRITE ON DIRECTORY DPUMP_DIR TO SYSTEM;</p><p><b>Now try to take export again. It should work.</b></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-63402925839530971802022-07-27T00:05:00.000+05:302022-07-27T00:05:02.508+05:30Concurrent Program Output Saving as FNDWRR.txt Using Edge Browser<p><br /></p><p><b style="background-color: #fcff01;">SYMPTOMS</b></p><p>Using Edge browser when concurrent program output is saved the output file name defaulting to FNDWRR.txt.This is happening only for txt format output type files.</p><p>Expected Behaviour:</p><p>---------------------</p><p>When concurrent program output is saved the file name should default with program name.</p><p>For ex: For Active User concurrent program the Output should be Active_Users.txt</p><p><br /></p><p><b style="background-color: #fcff01;">STEPS</b></p><p>-----------------------</p><p>The issue can be reproduced at will with the following steps:</p><p>1.Using Edge Browser Connect to EBS application</p><p>2.Navigate to System administrator responsibility</p><p>3.Submit Active User concurrent Program</p><p>4.Click On view Output</p><p>5.Program output open in edge browser.</p><p>6.Save the output using option Ctrl+S</p><p>This issue is only for output format of type txt and only in Edge browser. Other browsers like the IE, Chrome the concurrent program outputs were getting saved with expected file naming convention.</p><p><span style="background-color: #fcff01;">CAUSE</span></p><p>Edge browser failed to convert the output file name when using existing viewer options setup for txt output type files.</p><p><br /></p><p><b style="background-color: #fcff01;">SOLUTION</b></p><p>To implement the solution test the following setups::</p><p>1.Navigate to System Administrator responsibility</p><p>2.Install -- Viewer option</p><p>3.Add new entry</p><p> File Format :: Text</p><p> mime type :: application/txt</p><p> Description :: Text File</p><p> Select Allow Native encoding checkbox</p><p><br /></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjOlgtCwXlc5GICVWYa5D5b8gUp714I1gZjte4-m_oaL_KySJTL_mzK6Hgy7EmBeoEzaUTe_UZejlIRQWW7-R456uYqr3qRP43K9QyDmcZ3HYJnkfBaDNnw4G6EV6ijmUFGtHklCntf4dwmA_ftF3bgbL-CP3Z2C6EuFaFRvEr3ml1zn5fFpkXwzIhXgg" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="141" data-original-width="525" height="143" src="https://blogger.googleusercontent.com/img/a/AVvXsEjOlgtCwXlc5GICVWYa5D5b8gUp714I1gZjte4-m_oaL_KySJTL_mzK6Hgy7EmBeoEzaUTe_UZejlIRQWW7-R456uYqr3qRP43K9QyDmcZ3HYJnkfBaDNnw4G6EV6ijmUFGtHklCntf4dwmA_ftF3bgbL-CP3Z2C6EuFaFRvEr3ml1zn5fFpkXwzIhXgg=w532-h143" width="532" /></a></div><br /><br /><p></p><p><br /></p><p>4.Using System administrator responsibility Navigate to User -- Profile form</p><p>5.Update profile 'Viewer: Application for Text' value to Text File.</p><p><br /></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiFDP4FVPznxOFomgRUr0FtIdXYRyGiX3Qol8jeSX9sxUdWLAwPNGmi51FSvUdTsw4sZcZMxfCRbLBj74cREprQkZmAPDetZ__nBcDfG4SbX2FiPq7pQSF3ARPVI7-DBtQwIl7vnA7g8Lv6VF_UFYjYLW8jV5Adlq12zxfvmxM5MV4HOQgyxIzg2T317Q" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="623" data-original-width="791" height="352" src="https://blogger.googleusercontent.com/img/a/AVvXsEiFDP4FVPznxOFomgRUr0FtIdXYRyGiX3Qol8jeSX9sxUdWLAwPNGmi51FSvUdTsw4sZcZMxfCRbLBj74cREprQkZmAPDetZ__nBcDfG4SbX2FiPq7pQSF3ARPVI7-DBtQwIl7vnA7g8Lv6VF_UFYjYLW8jV5Adlq12zxfvmxM5MV4HOQgyxIzg2T317Q=w447-h352" width="447" /></a></div><br /><br /><p></p><p><br /></p><p>7.Logout and re-login to EBS application.</p><p>8.Retest the issue.</p><p><br /></p><p><b style="background-color: #fcff01;">REFERENCES</b></p><p><span style="background-color: white;"><b>Meta link id :- </b><b><span style="font-family: Tahoma, sans-serif; font-size: 10.5pt;">(Doc ID 2786457.1)</span></b></span></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-67525345581656891152022-07-08T06:31:00.000+05:302022-07-08T06:31:27.180+05:30ora-39087 directory name data_pump_dir is invalid<p><b>Error :-</b></p><p>ora-39002 invalid operation</p><p>ora-39070 unable to open the log file</p><p>ora-39087 directory name DATAPUMP is invalid</p><p><br /></p><p><b>Solution :-</b></p><p><b>create a new directory object (as a SYS user or as a user with CREATE ANY DIRECTORY privilege granted), grant READ and WRITE privileges on that DIRECTORY, and perform data pump export/import tasks.</b></p><p>Make sure that the /u01/EXPDP_IMPDP directory has 660 permission.</p><p>create or replace your directory and grant the following privileges' to user(system).</p><p><br /></p><p>[oracle@prod u01]$ mkdir EXPDP_IMPDP</p><p>[oracle@prod u01]$ chmod 660 EXPDP_IMPDP</p><p><br /></p><p><b>[oracle@QA u01]$ sqlplus sys/*****@PDB1 as sysdba</b></p><p><br /></p><p>SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 7 11:30:58 2022</p><p>Copyright (c) 1982, 2016, Oracle. All rights reserved.</p><p>Last Successful login time: Sat Jun 25 2022 14:49:08 -04:00</p><p>Connected to:</p><p>Oracle Database 12c EE High Perf Release 12.2.0.1.0 - 64bit Production</p><p><br /></p><p>SQL> create directory EXPDP_IMPDP as '/u01/EXPDP_IMPDP';</p><p>Directory created.</p><p><br /></p><p>SQL> GRANT READ, WRITE ON DIRECTORY EXPDP_IMPDP TO system;</p><p>Grant succeeded.</p><p><br /></p><p>SQL></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-50477303507135738332022-05-10T20:40:00.004+05:302022-05-10T20:40:54.497+05:30 How to change Oracle Applications R12.1.3 Port<p><b> How to change Oracle Applications R12.1.3 Port-8002 to 8050</b></p><p><b>Step:-1</b></p><p>Dowm Application Tier</p><p>[applprod@prd-erp01 ~]$ cd $ADMIN_SCRIPTS_HOME</p><p>[applprod@prd-erp01 scripts]$ ./adstpall.sh apps/apps</p><p><b>Step:-2</b></p><p>Open your Context XML file for edit it. </p><p>Go to Context XML path :- $INST_TOP/appl/admin/</p><p>Take backup of .XML file :- cp Oracle_prd-erp01.xml Oracle_prd-erp01.xml-bkp10MAY22</p><p>Edit Oracle_prd-erp01.xml file with below parameter port value replace from 8002 to 8050.</p><p><b>Change the value from 8002 to be 8050 on the following lines:</b></p><p> <web_port oa_var="s_webport" oa_type="PORT" base="8000" step="1" range="-1" label="Web Listener Port">8050</web_port></p><p> <activewebport oa_var="s_active_webport" oa_type="DUP_PORT" base="8000" step="1" range="-1" label="Active Web Port">8050</activewebport></p><p> <login_page oa_var="s_login_page">http://<host>.<domain>:8050/OA_HTML/AppsLogin</login_page></p><p> <chronosURL oa_var="s_chronosURL">http://<host>.<domain>:8050/oracle_smp_chronos/oracle_smp_chronos_sdk.gif</chronosURL> </p><p> <oa_var="s_endUserMonitoringURL">http://<host>.<domain>:8050/oracle_smp_chronos/oracle_smp_chronos_sdk.gif</EndUserMonitoringURL></p><p> <externURL oa_var="s_external_url">http://<host>.<domain>:8050</externURL></p><p> <httplistenparameter oa_var="s_http_listen_parameter">8050</httplistenparameter> </p><p><br /></p><p><b> Save .XML file>>>>>></b></p><p><b>Step:-3</b></p><p>Run Autoconfig on apps_tier for changes to take place.</p><p>[applprod@Oracle-prd-erp01 scripts]$ ./adautocfg.sh</p><p>Enter the APPS user password: *****</p><div><b>Step:-4</b></div><p>Start Application services.</p><p>[applprod@Oracle-prd-erp01 scripts]$ ./adstrtal.sh apps/apps</p><p><b>Step:-5</b></p><p>Verify new URL.</p><p>SQL> select HOME_URL from icx_parameters;</p><p>HOME_URL</p><p>--------------------------------------------------------------------------------</p><p>http://Oracle-prd-erp01.oracle.net:8050/OA_HTML/AppsLogin</p><p><br /></p><p><br /></p><p><br /></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-6727533132673191022022-05-09T23:43:00.001+05:302022-05-09T23:43:47.784+05:30FDPSTP failed due to ORA-20100: Error: FND_FILE failure<p><strong style="background-color: #fafcff; color: #2a2a2a; font-family: Verdana, Tahoma, Arial, sans-serif; font-size: 10.944px;">I got recently a issue with Concurrent program, All program completed with below error.</strong></p><p><br /></p><p>**Starts**09-MAY-2022 21:07:39</p><p>ORACLE error 20100 in FDPSTP</p><p>Cause: FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, o5819484.tmp in the directory, /usr/tmp.</p><p>You will find more information in the request log.</p><p>ORA-06512: at "APPS.FND_FILE", line 417</p><p>ORA-06</p><p><br /></p><p><b style="background-color: #ffa400;">Solution :-</b></p><p><strong style="color: #2a2a2a; font-family: Verdana, Tahoma, Arial, sans-serif; font-size: 10.944px;">change Directory file permssion to 777. so that others can write a file into it.</strong></p><p style="background-color: #fafcff; color: #2a2a2a; font-family: Verdana, Tahoma, Arial, sans-serif; font-size: 10.944px; line-height: 1.5em; margin: 0px 0px 15px; padding: 0px;"><strong># chmod 777 /usr/tmp</strong></p><p style="background-color: #fafcff; color: #2a2a2a; font-family: Verdana, Tahoma, Arial, sans-serif; font-size: 10.944px; line-height: 1.5em; margin: 0px 0px 15px; padding: 0px;"><strong><br /></strong></p><p style="background-color: #fafcff; color: #2a2a2a; font-family: Verdana, Tahoma, Arial, sans-serif; font-size: 10.944px; line-height: 1.5em; margin: 0px 0px 15px; padding: 0px;"><strong>Retest issue.</strong></p><p style="background-color: #fafcff; color: #2a2a2a; font-family: Verdana, Tahoma, Arial, sans-serif; font-size: 10.944px; line-height: 1.5em; margin: 0px 0px 15px; padding: 0px;"><strong><br /></strong></p><p style="background-color: #fafcff; color: #2a2a2a; font-family: Verdana, Tahoma, Arial, sans-serif; font-size: 10.944px; line-height: 1.5em; margin: 0px 0px 15px; padding: 0px;"><strong><br /></strong></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-29333123912352700042022-04-13T21:11:00.002+05:302022-04-13T21:11:12.304+05:30ORA-30091: secondary translation table not loaded<p> ORA-31693: Table data object "DEV1"."TABLE_CUST" failed to load/unload and is being skipped due to error:</p><p>ORA-30091: secondary translation table not loaded</p><p>Data Pump Import Reports ORA-31693,ORA-30091: Secondary Translation Table Not Loaded (Doc ID 2736570.1)<span style="white-space: pre;"> </span></p><p><br /></p><p><b style="background-color: #ffa400;">SYMPTOMS:</b></p><p>Performing a Data Pump Import the following errors occur:</p><p>ORA-31693: Table data object "USER"."TABLE" failed to load/unload and is being skipped due to error:</p><p>ORA-30091: secondary translation table not loaded</p><p><b style="background-color: #ffa400;">CAUSE:</b></p><p>The source database has a newer timezone version than the target database:</p><p><br /></p><p><b style="background-color: #ffa400;">Target database timezone version:</b></p><p>SQL> select * from v$timezone_file;</p><p>FILENAME VERSION CON_ID</p><p>-------------------- ---------- ----------</p><p>timezlrg_26.dat 26 0</p><p><br /></p><p><b style="background-color: #ffa400;">Source database timezone version :-</b></p><p>SQL> select * from v$timezone_file;</p><p>FILENAME VERSION CON_ID</p><p>-------------------- ---------- ----------</p><p>timezlrg_28.dat 28 0</p><p><br /></p><p><b style="background-color: #ffa400;">SOLUTION :- </b></p><p>Install the newer Timezone Version on the Target Database.</p><p></p><p class="MsoNormal"><b style="mso-bidi-font-weight: normal;">SQL> shut
immediate<o:p></o:p></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;">SQL> startup
upgrade<o:p></o:p></b></p>
<p class="MsoNormal">ORACLE instance started.<o:p></o:p></p>
<p class="MsoNormal">Total System Global Area 2147483648 bytes<o:p></o:p></p>
<p class="MsoNormal">Fixed Size<span style="mso-spacerun: yes;">
</span>2926472 bytes<o:p></o:p></p>
<p class="MsoNormal">Variable Size<span style="mso-spacerun: yes;">
</span>889194616 bytes<o:p></o:p></p>
<p class="MsoNormal">Database Buffers<span style="mso-spacerun: yes;">
</span>1241513984 bytes<o:p></o:p></p>
<p class="MsoNormal">Redo Buffers<span style="mso-spacerun: yes;">
</span>13848576 bytes<o:p></o:p></p>
<p class="MsoNormal">Database mounted.<o:p></o:p></p>
<p class="MsoNormal">Database opened.<o:p></o:p></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;">SQL> EXEC
DBMS_DST.BEGIN_UPGRADE (28);<o:p></o:p></b></p>
<p class="MsoNormal">PL/SQL procedure successfully completed.<o:p></o:p></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;">SQL> shut
immediate<o:p></o:p></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;">SQL> startup<o:p></o:p></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;">SQL> SELECT
version FROM v$timezone_file;<o:p></o:p></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><span style="mso-spacerun: yes;"> </span></b>VERSION<o:p></o:p></p>
<p class="MsoNormal">----------<o:p></o:p></p>
<p class="MsoNormal"><span style="mso-spacerun: yes;"> 2</span>8<o:p></o:p></p><p class="MsoNormal"><br /></p><p class="MsoNormal"><b>Retest issue.</b></p><p class="MsoNormal"><br /></p><p class="MsoNormal"><br /></p><br /><p></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-920010919441769132022-03-14T17:58:00.005+05:302022-03-14T17:58:46.417+05:30ORA-01172: recovery of thread 1 stuck at block 3024 of file 19<p><br /></p><p><b style="background-color: #ffa400;">ERROR :</b></p><p> SQL> startup</p><p>ORACLE instance started.</p><p>Total System Global Area 2.1475E+10 bytes</p><p>Fixed Size 12170960 bytes</p><p>Variable Size 3019901232 bytes</p><p>Database Buffers 1.8388E+10 bytes</p><p>Redo Buffers 54935552 bytes</p><p>Database mounted.</p><p><span style="color: red;">ORA-01172: recovery of thread 1 stuck at block 3024 of file 19</span></p><p><span style="color: red;">ORA-01151: use media recovery to recover block, restore backup if needed</span></p><p><b style="background-color: #ffa400;">Cause: </b></p><p>This error usually means that blocks of file 19 are corrupted, In this case we need to recover file 19.</p><div><div>Crash recovery or instance recovery could not apply a change to a block because it was not the next change. </div><div>This can happen if the block was corrupted and then repaired during recovery.</div></div><p><b style="background-color: #ffa400;">SOLUTION :- </b></p><p>We need to recover all of corrupted block by using data file recovery or Database recovery. </p><p>In my case I have recover datafile 19.</p><p>SQL> recover datafile 19;</p><p>Media recovery complete.</p><p>SQL> alter database open;</p><p>Database altered.</p><p>SQL> select name,OPEN_MODE from v$database;</p><p>NAME OPEN_MODE</p><p>--------- --------------------</p><p>TEST READ WRITE</p><div><br /></div>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-8094853018395058932022-03-03T23:41:00.005+05:302022-03-03T23:41:54.535+05:30 ORA-19563: header validation failed for file<p>Encountered while restoring the server on test database.</p><p><b>Restore log file :-</b></p><p><span style="color: red;">Finished restore at 03-MAR-22</span></p><p><span style="color: red;">released channel: c1</span></p><p><span style="color: red;">released channel: c2</span></p><p><span style="color: red;">RMAN-00571: ===========================================================</span></p><p><span style="color: red;">RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============</span></p><p><span style="color: red;">RMAN-00571: ===========================================================</span></p><p><span style="color: red;">RMAN-03002: failure of switch command at 03/03/2022 22:58:55</span></p><p><span style="color: red;">ORA-19563: header validation failed for file</span></p><p>RMAN></p><p><br /></p><p><b style="background-color: #fcff01;">CAUSE :-</b></p><p>Check alerts log in target and get the file number/name for which throwing this error .</p><p>SQL > SELECT FILE#, NAME FROM V$DATAFILE where NAME like '%system%';</p><p>FILE# NAME</p><p>----------- -------------</p><p> 92 /u01/test/db/apps_st/data/system17.dbf</p><p> 139 /u01/test/db/apps_st/data/system17.dbf</p><p><br /></p><p><b style="background-color: #fcff01;">Solution :-</b></p><p>Rename the file like (system17.dbf to system18.dbf)</p><p>SQL > SELECT FILE#, NAME FROM V$DATAFILE where NAME like '%system%';</p><p>FILE# NAME</p><p>----------- -------------</p><p> 92 /u01/test/db/apps_st/data/system17.dbf</p><p> 139 /u01/test/db/apps_st/data/system18.dbf</p><p><br /></p><p><b>NOW try to restore again.</b></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-16818701533659476872022-02-03T00:16:00.000+05:302022-02-03T00:16:27.677+05:30REP-1804: Unable to open printer definition file HPL.prt<p><span style="color: red;">REP-0069: Internal error</span></p>
<p class="MsoNormal"><span style="color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">REP-57054: In-process job terminated:Terminated with error:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">REP-1804: Unable to open printer definition file
'/apps/apps_st/appl/CUST_TOP/12.0.0/reports/HPL.prt'.<o:p></o:p></span></p><p class="MsoNormal"><span style="color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;"><br /></span></p><p class="MsoNormal"><span style="color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;"><b><span style="font-size: medium;"><span style="background-color: #fcff01;">Solution :-</span> </span></b></span></p><p class="MsoNormal"></p><p class="MsoNormal">Verify HPL.prt file location and copy HPL.prt file to (<span style="color: red;">'/apps/apps_st/appl/CUST_TOP/12.0.0/reports/HPL.prt'</span>) path.</p><p></p><p class="MsoNormal"></p><p class="MsoNormal">[applmgr@oracle ]$ locate HPL.prt<o:p></o:p></p>
<p class="MsoNormal">/apps/apps_st/appl/fnd/12.0.0/reports/HPL.prt<o:p></o:p></p>
<p class="MsoNormal">[applmgr@oracle ]$ cp /apps/apps_st/appl/fnd/12.0.0/reports/HPL.prt /apps/apps_st/appl/CUST_TOP/12.0.0/reports/<o:p></o:p></p><p class="MsoNormal"><br /></p><p class="MsoNormal"><b>Now retest issue.</b></p>
<p class="MsoNormal"><br /></p><span style="color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;"></span><p></p>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0tag:blogger.com,1999:blog-7148806141435514239.post-1662957480713380252021-12-15T01:29:00.000+05:302021-12-15T01:29:00.466+05:30System Hold, Fix Manager before resetting counters<p>We have seen this error couple of times in the Concurrent manager Administer screen in our R12.1.3 QA instances.</p><p><span style="color: red;">OPP Concurrent Manager status : System Hold, Fix Manager before resetting counters</span></p><div><br /></div><div><br /></div><div><div><b style="background-color: #ffa400;">Solution:</b></div><div><br /></div><div><b style="background-color: #fcff01;">1. Shutdown the Apps Tier cleanly make sure there are no sessions: </b></div><div><br /></div><div>[applmgr@oracle ~]$ cd $ADMIN_SCRIPTS_HOME</div><div><br /></div><div>[applmgr@oracle scripts]$ ./adstpall.sh apps/*****</div><div><br /></div><div><br /></div><div>[applmgr@oracle ~]$ ps -ef | grep FNDLIBR</div><div>[applmgr@oracle ~]$ ps -ef | grep FNDSM</div><div>[applmgr@oracle ~]$ ps -ef | grep FNDCRM</div><div>[applmgr@oracle ~]$ ps -ef | grep FNDFS</div><div>[applmgr@oracle ~]$ ps -ef | grep applmgr</div><div><br /></div><div><br /></div></div><div><div><b style="background-color: #fcff01;">2. Stop the database.</b></div><div><b><br /></b></div><div><div>[ora@oracle ~]$ sqlplus / as sysdba</div><div><br /></div><div>SQL*Plus: Release 11.1.0.7.0 - Production on Tue Dec 14 14:28:07 2021</div><div><br /></div><div>Copyright (c) 1982, 2008, Oracle. All rights reserved.</div><div><br /></div><div>Connected to an idle instance.</div><div><b><br /></b></div><div>SQL> shut immediate</div></div><div><b><br /></b></div><div><b style="background-color: #fcff01;">3. Start the database.</b></div></div><div><b><br /></b></div><div><div>[ora@oracle ~]$ sqlplus / as sysdba</div><div><br /></div><div>SQL*Plus: Release 11.1.0.7.0 - Production on Tue Dec 14 14:28:07 2021</div><div><br /></div><div>Copyright (c) 1982, 2008, Oracle. All rights reserved.</div><div><br /></div><div>Connected to an idle instance.</div><div><b><br /></b></div><div>SQL> startup</div></div><div><b><br /></b></div><div><b style="background-color: #fcff01;">4. To the appsTier go to $FND_TOP/bin and run the following relinks.</b></div><div><div><b><br /></b></div><div>[applmgr@oracle ~]$ cd $FND_TOP/bin</div><div><br /></div><div>$ adrelink.sh force=y link_debug=y "fnd FNDLIBR"</div><div>$ adrelink.sh force=y link_debug=y "fnd FNDFS"</div><div>$ adrelink.sh force=y link_debug=y "fnd FNDCRM"</div><div>$ adrelink.sh force=y link_debug=y "fnd FNDSM"</div><div><b><br /></b></div><div><b style="background-color: #fcff01;">5. Run the CMCLEAN.SQL script and commit..</b></div><div><br /></div><div>SQL> @cmclean.sql</div><div><b><br /></b></div><div><b><br /></b></div><div><b style="background-color: #fcff01;">6. Execute the following SQL:</b></div><div><b><br /></b></div><div>SQL> select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';</div><div><br /></div><div>CONCURRENT_QUEUE_NAME</div><div>--------------------------------------------------------------------------------</div><div>FNDSM_ORACLE</div><div><br /></div><div>SQL></div><div><b><br /></b></div><div><b style="background-color: #fcff01;">7. Now start the Apps tier:</b></div><div><b style="background-color: #fcff01;"><br /></b></div><div><div>[applmgr@oracle ~]$ cd $ADMIN_SCRIPTS_HOME</div><div><br /></div><div>[applmgr@oracle scripts]$ ./adstrtal.sh apps/*****</div><div><br /></div><div><br /></div><div><b>Check status of OPP Concurrent manager.</b></div><div><b><br /></b></div><div><b>I hope issue got fix.</b></div><div><b><br /></b></div><div><br /></div><div><br /></div></div></div>Sujeet Farmerhttp://www.blogger.com/profile/00942028623538196466noreply@blogger.com0