Create dblinks without user access
When users asks the DBA’s to create a dblink, we would normally get back to the users asking for the dblink’s owner password to create the required db link. The…
Read MoreSteps to find if there are any logical corruptions in the table that has BLOB column
a. Create a temporary dummy table for storing the rowids of the corrupted LOBs. here the dummy table name is “corrupt_lobs”. SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number); Create…
Read MoreSteps to Remove any logical corruptions in the table that has BLOB column
a. Create a temporary dummy table for storing the rowids of the corrupted LOBs. here the dummy table name is “corrupt_lobs”. SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number); Create…
Read MoreExport the table without the corrupted row
If your table has any logical corruptions the expdp of the table might fail with ORA-155 error. If your table has any logical corruption, first identify the rows/rowids that are…
Read MoreReorganizing FND_LOBS table in Oracle EBS R12.1.3
Reorganizing FND_LOBS table in Oracle EBS R12.1.3 FND_LOBS is usually one of the top 10 table in an EBS environment. It stores all the attachments that have been uploaded to Oracle Applications. There is a LOB field within this table called FILE_DATA, the corresponding LOB segment (e.g., APPLSYS.SYS_LOB000******04$$) is where the actual attachment data is stored, and is usually very large. The size of FND_LOBS table and its LOB_SEGMENT SYS_LOB******$$ was around 3GB and 1.5 TB in our environment. There were over 40 lakhs record in the FND_LOBS table each pertaining to its own Application Module. Our Client had recently decided to migrate the EBS environment to OCI (IAAS). The OC team had decided to follow expdp/impdp to migrate the EBS database from on-prem to cloud. When they tried to perform the export the EBS database they had faced an issue with the FND_LOBS table. They had faced the following error, ORA-31693: Table data object “APPLSYS”.”FND_LOBS” failed to load/unload and is being skipped due to error: ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout ORA-01555: snapshot too old: rollback segment number with name “” too small ORA-22924: snapshot too old This error occurred while the expdp is trying to export the FND_LOBS table. This issue is occurring due to corruption in the FND_LOBS table. Reference – SRDC – ORA-22924 or ORA-1555 on LOB data: Checklist of Evidence to Supply (Doc ID 1682707.1) As suspected there were logical corruptions in the FND_LOBS table and 26 rows were identified as corrupted records in FND_LOBS table by the validation scripts in Doc ID 1682707.1. We had opened a SR with Oracle Support to resolve the corruptions, Oracle Support had suggested to follow action plan mentioned in Doc ID 1950896.1 to remove corruptions. As per Doc ID 1950896.1 we had performed the following, Section a – Identifying and removing logical corruptions Create a temporary dummy table for storing the rowids of the corrupted LOBs. here the dummy table name is “corrupt_lobs”. SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number); Create this table as a user who has necessary provilege to scan the target table for corruptions. Execute the following PL/SQL block to identify the corrupted rows. Provide the <lob Column name> and <Table name> with the respective LOB column and table name. SQL> declare error_1578 exception; error_1555 exception; error_22922 exception; pragma exception_init(error_1578,-1578); pragma exception_init(error_1555,-1555); pragma exception_init(error_22922,-22922); num number; begin for cursor_lob in (select rowid r, &&lob_column from &table_owner..&table_with_lob) loop begin num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw (‘889911’)) ;…
Read MoreRecovering OCR using physical backup
Step 1: Identify OCR physical backup using the following command [root@rac1 ~]# ocrconfig -showbackup rac2 2020/04/03 16:57:56 /u01/app/12.1.0/grid/cdata/rac-scan/backup00.ocr 0 rac1 2020/04/01 17:34:19 /u01/app/12.1.0/grid/cdata/rac-scan/backup01.ocr 0 rac1 2020/04/31 13:57:45 /u01/app/12.1.0/grid/cdata/rac-scan/backup02.ocr 0 rac2…
Read MoreENABLE_DDL_LOGGING In Oracle 12c
ENABLE_DDL_LOGGING In Oracle 12c This ENABLE_DDL_LOGGING parameter has been introduced in oracle 12c. Enable the parameter: SQL> show parameter enable_ddl_logging NAME TYPE VALUE ———————————— ———– —————————— enable_ddl_logging boolean FALSE…
Read MoreHow To Drop SQL Baselines In Oracle
How To Drop SQL Baselines In Oracle 1.Get the sql_handle and sql_baseline name of the sql_id: SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE…
Read MoreOrapwd Tool For Password File In Oracle
Orapwd Tool For Password File In Oracle orapwd tool is used to create and manage password files. DEFAULT LOCATION FOR PWD FILE – $ORACLE_HOME/dbs Usage: orapwd file= entries= force=<y/n> asm=<y/n>…
Read MoreHow To Export And Import Statistics In Oracle
How To Export And Import Statistics In Oracle For Table: PROD> exec dbms_stats.export_table_stats(ownname=>’SCOTT’, tabname=>’TEST’, stattab=>’STAT_TEST’, cascade=>true); PL/SQL procedure successfully completed. SQL> set lines 200 SQL> set pagesize 200 SQL>…
Read More