Posts

Showing posts from 2024

ORA-00257:archiver error, connect internal only until freed

  Issue: Application log shows below error and users are unable to connect to the database. 0RA-00257:archiver error, connect internal only until freed (OR) 0RA-00257:archiver error, connect internal only until freed ORA-16020: less destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST Fix: 1) Check if there are any errors for the archive destination(s). Make sure that the number of VALID archive destinations is greater than or equal to the value specified by LOG_ARCHIVE_MIN_SUCCEED_DEST initialization parameter. set pages 1000 col dest_name for a30 col destination for a60 SELECT dest_id, dest_name, binding, status, destination, error FROM v$archive_dest; SHOW PARAMETER log_archive_min_succeed_dest 2) If space is full in one or more of the archive destinations (or if destination is not available), take any of the following steps: 2.a) Manually move the archives to another location and delete them from archive destination.   (OR) 2.b) Change the archive destination to an

How to set up the Oracle Wallets in Oracle Database 19C

  The Oracle Wallet is a container or repository that stores authentication and credentials such as certificates, certificate requests, and private keys., By using this we can connect to database without providing the schema name & password, Password will be connected by using the TNS ALIES name & hence the schema are encrypted & stored in the oracle wallets. Let us configure the schema password by using the wallets. Create a directory to store the wallets. Create a directory to store the password $ mkdir -p /u01/app/wallets Create a wallet & provide the wallet password. $ mkstore -wrl /u01/app/wallets/ -create Enter password: ********* Enter password again: ******** $ ll total 8 -rw-------. 1 oracle oinstall 194 Nov 1 14:38 cwallet.sso -rw-------. 1 oracle oinstall 0 Nov 1 14:38 cwallet.sso.lck -rw-------. 1 oracle oinstall 149 Nov 1 14:38 ewallet.p12 -rw-------. 1 oracle oinstall 0 Nov 1 14:38 ewallet.p12.lck Check the status of listener $lsnrctl status listener_name;

How to write to a CSV file using Oracle SQL*Plus

  SPOOL command  The  SPOOL  command is  unavailable  in the browser-based SQL*Plus version,  iSQL*Plus . To generate files while using iSQL*Plus, change the necessary preference settings to directly output to a file. This is accomplished using the  SPOOL  statement. While  SPOOL  is  active , SQL*PLus will store the output of any query to the specified file. Therefore, the next command to enter is  spool : spool filepath Skipping ahead slightly,  after  your query is inserted, you also need to halt  spool  so the file output is closed by using the  spool off  command: spool off Insert the query  The last step after the settings are modified and  spool  is running is to insert your query. For our simple example, we’re outputting all books from our  books  table. SELECT   title,   author FROM   authors; Don’t forget the semi-colon to close out your query statement, then enter the aforementioned  spool off  command. That’s it, you’ve generated a new text file with the results of your qu

How To Use Patch Wizard Without Setting Up Internet Connection

Image
 Below  document describes the process for using Patch Wizard without setting up an Internet connection Follow the steps below to run Patch Wizard without an internet connection to generate the latest EBS Patch Recommendation Analysis or to analyze any EBS patch(es) against your current EBS Application. The infobundle is updated daily and should be refreshed/downloaded each time/day you wish to analyze patches on your EBS Environment using Patch Wizard. If you want to get the latest Oracle E-Business Suite patch recommendations: Download the Patch Information Bundle (InfoBundle) on to a system that has Internet access. The InfoBundle is generated daily, and can be downloaded from the following locations: https://updates.oracle.com/download/InfoBundleR12.zip  (for R12) or https://updates.oracle.com/download/InfoBundle11i.zip  (for 11i) From  Patch Wizard > Preferences , set up a staging directory if not already done so. Make sure the Patch Wizard can read from and write to the stagin

CPU Patch Analysis

 Oracle CPU patch analysis Identifying the Latest Critical Patch Update for Oracle E-Business Suite Release 12.2 (Doc ID 2484000.1) Database Quick-Link If you only care about the database patches, this MOS note steps you through really easily. Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patch sets and Base Releases (Doc ID 2118136.2) https://www.oracle.com/security-alerts/ Critical Patch Updates Critical Patch Updates provide security patches for supported Oracle on-premises products. They are available to customers with valid support contracts. Starting in April 2022, Critical Patch Updates are released on the third Tuesday of January, April, July, and October (They were previously published on the Tuesday closest to the 17th day of January, April, July, and October). The next four dates are: 16 April 2024 16 July 2024 15 October 2024 21 January 2025 A pre-release announcement will be published on the Thursday preceding each Cr

How To Manage Space of The FRA in the Oracle DB

  Delete Old files connect with the target database using RMAN RMAN target / Delete the old files RMAN> delete obsolete; Remove Restore Points SQL> SELECT name, storage_size FROM gv$restore_point; Drop the restore point SQL> DROP restore point RP; Delete the archive logs RMAN> DELETE archivelog ALL Increase the Space SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 20G scope=both; Disable the Flashback ALTER DATABASE flashback off;

How to autopopulate user_guid in fnd_user table ( EBS Blank Page)

  Issue:  EBS Local Login url is working fine. When accessing sso url, users are getting error as below appended to the url. And login page will be blank. One of the reason could be : OAM Authentication is fine but during authorization process, OAM is unable to map guid from oid to user_guid in fnd_user. This can be due to user_guid being null in fnd_user table. As per the process, when user logins for the first time, user_guid need to be populated automatically (from oid) Query guid from oid and update fnd_user table with that. ldapsearch -h oidhost.appsolworld.com -p 3060 -D "cn=orcladmin" -w orcladmpwd -b "cn=Users,dc=dc,dc=online,dc=org" -s sub "(uid=$1)" orclsamaccountname krbprincipalname mail orcluserprincipalname orclguid Pass userid to this command which gives guid along with other details. Update fnd_user table with above guid : update fnd_user  set user_guid=' ' where user_name like ' '; Solution:  As per the process,  when user

How to Collect Standard Diagnostic Information Using SQLT for SQL Issues

  Gather Diagnostics with SQLT In order to gather SQLT output for the query, you will need to download and install SQLT. Download SQLT To do this, download and run the SQLT tool. You can  download SQLT here .  Install SQLT Install SQL by executing the installation script sqlt/install/sqcreate.sql connected as SYS: # cd sqlt/install # sqlplus / as sysdba SQL> START sqcreate.sql During the installation you will be asked to enter values for these parameters: Optional Connect Identifier  - In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key. SQLTXPLAIN password  - Case sensitive in most systems. SQLTXPLAIN Default Tablespace  - Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space. SQLTXPLAIN Temporary Tablespace  - Select from a list of available tempor